Most Efficient Way to Retrieve Date Ranges

This is a hard problem to solve in general, but there are a couple of things we can do to help the optimizer choose a plan.  This script creates a table with 10,000 rows with a known pseudo-random distribution of rows to illustrate:
CREATE TABLE dbo.SomeDateTable
(
    Id          INTEGER IDENTITY(1, 1) PRIMARY KEY NOT NULL,
    StartDate   DATETIME NOT NULL,
    EndDate     DATETIME NOT NULL
);
GO
SET STATISTICS XML OFF
SET NOCOUNT ON;
DECLARE
    @i  INTEGER = 1,
    @s  FLOAT = RAND(20120104),
    @e  FLOAT = RAND();

WHILE @i <= 10000
BEGIN
    INSERT dbo.SomeDateTable
        (
        StartDate,
        EndDate
        )
    VALUES
        (
        DATEADD(DAY, @s * 365, {d '2009-01-01'}),
        DATEADD(DAY, @s * 365 + @e * 14, {d '2009-01-01'})
        )

    SELECT
        @s = RAND(),
        @e = RAND(),
        @i += 1
END

The first question is how to index this table.  One option is to provide two indexes on the DATETIME columns, so the optimizer can at least choose whether to seek on StartDate or EndDate.
CREATE INDEX nc1 ON dbo.SomeDateTable (StartDate, EndDate)
CREATE INDEX nc2 ON dbo.SomeDateTable (EndDate, StartDate)

Naturally, the inequalities on both StartDate and EndDate mean that only one column in each index can support a seek in the example query, but this is about the best we can do.  We might consider making the second column in each index an INCLUDE rather than a key, but we might have other queries that can perform an equality seek on the leading column and an inequality seek on the second column.  Also, we may get better statistics this way.  Anyway...
DECLARE
    @StartDateBegin DATETIME = {d '2009-08-01'},
    @StartDateEnd DATETIME = {d '2009-10-15'},
    @EndDateBegin DATETIME = {d '2009-08-05'},
    @EndDateEnd DATETIME = {d '2009-10-22'}

SELECT
    COUNT_BIG(*)
FROM dbo.SomeDateTable AS sdt
WHERE
    sdt.StartDate BETWEEN @StartDateBegin AND @StartDateEnd
    AND sdt.EndDate BETWEEN @EndDateBegin AND @EndDateEnd

This query uses variables, so in general the optimizer will guess at selectivity and distribution, resulting in a guessed cardinality estimate of 81 rows.  In fact, the query produces 2076 rows, a discrepancy that might be important in a more complex example.
On SQL Server 2008 SP1 CU5 or later (or R2 RTM CU1) we can take advantage of the Parameter Embedding Optimization to get better estimates, simply by adding OPTION (RECOMPILE) to the SELECT query above.  This causes a compilation just before the batch executes, allowing SQL Server to 'see' the real parameter values and optimize for those.  With this change, the estimate improves to 468 rows (though you do need to check the runtime plan to see this).  This estimate is better than 81 rows, but still not all that close.  The modelling extensions enabled by trace flag 2301 may help in some cases, but not with this query.
The problem is where the rows qualified by the two range searches overlap.  One of the simplifying assumptions made in the optimizer's costing and cardinality estimation component is that predicates are independent (so if both have a selectivity of 50%, the result of applying both is assumed to qualify 50% of 50% = 25% of the rows).  Where this sort of correlation is a problem, we can often work around it with multi-column and/or filtered statistics.  With two ranges with unknown start and end points, this becomes impractical.  This is where we sometimes have to resort to rewriting the query to a form that happens to produce a better estimate:
SELECT COUNT(*) FROM
(
    SELECT
        sdt.Id
    FROM dbo.SomeDateTable AS sdt
    WHERE
        sdt.StartDate BETWEEN @StartDateBegin AND @StartDateEnd
    INTERSECT
    SELECT
        sdt.Id
    FROM dbo.SomeDateTable AS sdt
    WHERE
        sdt.EndDate BETWEEN @EndDateBegin AND @EndDateEnd
) AS intersected (id)
OPTION (RECOMPILE)

This form happens to produce a runtime estimate of 2110 rows (versus 2076 actual).  Unless you have TF 2301 on, in which case the more advanced modelling techniques see through the trick and produce exactly the same estimate as before: 468 rows.
One day SQL Server might gain native support for intervals.  If that comes with good statistical support, developers might dread tuning query plans like this a little less.


mysql RANGE partitioning - how do I find partition boundaries?

I am not 100% sure, but 5.1 documentation says:

  PARTITION_DESCRIPTION: This column is used for RANGE and LIST
  partitions. For a RANGE partition, it contains the value set in the
  partition's VALUES LESS THAN clause, which can be either an integer or
  MAXVALUE. For a LIST partition, this column contains the values
  defined in the partition's VALUES IN clause, which is a
  comma-separated list of integer values.

  For partitions whose PARTITION_METHOD is other than RANGE or LIST,
  this column is always NULL.

In my 5.6 installation I can get:

mysql> SELECT PARTITION_NAME, PARTITION_DESCRIPTION FROM information_schema.partitions WHERE table_schema = database() AND table_name = 'foo';
+----------------+-----------------------+
| PARTITION_NAME | PARTITION_DESCRIPTION |
+----------------+-----------------------+
| p0             | 1                     |
| p1             | MAXVALUE              |
+----------------+-----------------------+
2 rows in set (0.00 sec)

Please note than your original ALTER TABLE syntax had a problem, and it should be written as:

mysql> ALTER TABLE foo PARTITION BY RANGE (is_archive) (
         PARTITION p0 VALUES LESS THAN (1),
         PARTITION p1 VALUES LESS THAN (MAXVALUE)
       );
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0


SQL Server 2005 - 2 sets of backups - 2 maintenance plans?

You probably don't want to put your backups in two places like that alternating.  You (probably) won't be able to do a point-in-time restore if one of your file store locations is lost.  You'll be stuck with the latest full backup of the remaining system.

DMason's suggestion is workable, but personally I would use the scripts from http://ola.hallengren.com/ and back up to a network location.  From there, you can backup up the files to tape or another disk.


Select records that do not have associations outside a certain list

Here is the query

SELECT C.* FROM
(SELECT nid,COUNT(1) fullcount
  FROM node
  INNER JOIN field_data_dir_phys_category   AS category   ON node.nid = category.entity_id
  GROUP BY nid) A INNER JOIN
(SELECT nid,COUNT(1) goodcount
  FROM node
  INNER JOIN field_data_dir_phys_category   AS category   ON node.nid = category.entity_id
  WHERE dir_phys_category_tid IN (498,499) GROUP BY nid) B USING (nid)
INNER JOIN (SELECT DISTINCT nid,title FROM node) C USING (nid)
WHERE fullcount=goodcount;

First, let's make some sample data.
NOTE: this uses a single table.  The OP used two tables - mine is intended to demonstrate the method used to count categories, which is the central part of my answer.

DROP DATABASE IF EXISTS doub1ejack;
CREATE DATABASE doub1ejack;
USE doub1ejack
CREATE TABLE node
(
    id INT NOT NULL AUTO_INCREMENT,
    nid INT NOT NULL,
    title VARCHAR(20),
    dir_phys_category_tid INT NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO node (nid,title,dir_phys_category_tid) VALUES
(1171,'Toby',499),(1172,'Susan',500),(1172,'Susan',499),
(1172,'Susan',498),(1173,'Joseph',499),(1173,'Joseph',498);
SELECT * FROM node;

Let's load it

mysql> DROP DATABASE IF EXISTS doub1ejack;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE doub1ejack;
Query OK, 1 row affected (0.00 sec)

mysql> USE doub1ejack
Database changed
mysql> CREATE TABLE node
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     nid INT NOT NULL,
    ->     title VARCHAR(20),
    ->     dir_phys_category_tid INT NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.23 sec)

mysql> INSERT INTO node (nid,title,dir_phys_category_tid) VALUES
    -> (1171,'Toby',499),(1172,'Susan',500),(1172,'Susan',499),
    -> (1172,'Susan',498),(1173,'Joseph',499),(1173,'Joseph',498);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

Let's look at it

mysql> SELECT * FROM node;
+----+------+--------+-----------------------+
| id | nid  | title  | dir_phys_category_tid |
+----+------+--------+-----------------------+
|  1 | 1171 | Toby   |                   499 |
|  2 | 1172 | Susan  |                   500 |
|  3 | 1172 | Susan  |                   499 |
|  4 | 1172 | Susan  |                   498 |
|  5 | 1173 | Joseph |                   499 |
|  6 | 1173 | Joseph |                   498 |
+----+------+--------+-----------------------+
6 rows in set (0.00 sec)

mysql>

Next, let's run the proposed query

mysql> SELECT C.* FROM
    -> (SELECT nid,COUNT(1) fullcount FROM node GROUP BY nid) A
    -> INNER JOIN
    -> (SELECT nid,COUNT(1) goodcount FROM node
    -> WHERE dir_phys_category_tid IN (498,499)
    -> GROUP BY nid) B USING (nid)
    -> INNER JOIN
    -> (SELECT DISTINCT nid,title FROM node) C USING (nid)
    -> WHERE fullcount=goodcount;
+------+--------+
| nid  | title  |
+------+--------+
| 1171 | Toby   |
| 1173 | Joseph |
+------+--------+
2 rows in set (0.00 sec)

mysql>

OK why does it work ? Let's add the count columns, remove the WHERE and see

mysql> SELECT C.*,fullcount,goodcount FROM
    -> (SELECT nid,COUNT(1) fullcount FROM node GROUP BY nid) A
    -> INNER JOIN
    -> (SELECT nid,COUNT(1) goodcount FROM node
    -> WHERE dir_phys_category_tid IN (498,499)
    -> GROUP BY nid) B USING (nid)
    -> INNER JOIN
    -> (SELECT DISTINCT nid,title FROM node) C USING (nid)
    -> ;
+------+--------+-----------+-----------+
| nid  | title  | fullcount | goodcount |
+------+--------+-----------+-----------+
| 1171 | Toby   |         1 |         1 |
| 1172 | Susan  |         3 |         2 |
| 1173 | Joseph |         2 |         2 |
+------+--------+-----------+-----------+
3 rows in set (0.00 sec)

mysql>

Here is the correlation: When the number of categories (fullcount) is equal to the number of categories which are 498 and/or 499 (goodcount), then that row's nid is acceptable.

Give it a Try !!!


Remove duplicate value when we use rollup


Retain column statistics on paragraph-style columns?

First of all, and perhaps most importantly, albatrosses are not unlucky.  They are in fact considered a good omen.  In the poem, the young sailor shoots it with his crossbow, and it is this reckless act that is considered unlucky.

Second of all, SQL Server uses a technique called trie trees, for string statistics.  These are not well documented but get a brief mention in 'Microsoft SQL Server 2012 Internals' in the 'String Statistics' section.  You cannot delete trie trees or control them.

If you delete the ordinary column statistics associated with a column they will be get recreated next time someone queries that column, unless you explicitly disable them which is inadvisable.  In the same way, the sailor is forced to wear the dead albatross around his neck as punishment : )

I think Sean's suggestion of using Full Text Indexing is a good one.  In summary do not shoot albatrosses, do not disable auto-stats.

Let's do a simple walkthru using 'The Rime of the Ancient Mariner' as an example.  I downloaded the text from Project Gutenberg available here, cleaned it up a bit and loaded it up in to a SQL Server table.  For a simple Like query with wildcards, I get a full clustered index scan with an estimated rowcount of 1.55 and actual rowcount of 7:

Looking at the statistics for the column, we can see a lot of stats that probably wouldn't make that much sense for other queries, which I think was your point:

Despite this, the estimated versus actual rowcount (1.55 versus 7) is pretty reasonable and is in the same order of magnitude, but we will get effectively a  table scan because of the wildcards.

Switching on full-text indexing, we can get more accurate rowcounts and start to interrogate the full-text index for other information it stores about the documents, eg

SELECT *
FROM dbo.documentText
WHERE CONTAINS ( documentText, 'albatross' )
GO

SELECT *
FROM sys.dm_fts_index_keywords( DB_ID(), OBJECT_ID('dbo.documentText') )

SELECT *
FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.documentText') )

SELECT TOP 10 display_term, COUNT(*)
FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.documentText') )
GROUP BY display_term
ORDER BY 2 DESC

We now get index seeks and potentially this could make a large performance difference on a bigger table.

Full walkthru script available here.

PS No albatrosses were harmed in the making of this post.


Change Tracking would be a lighter weight solution to your needs, and more robust than using a time stamp (even if driven from CDC in the way you describe).You can enable tracking at the column level. The versioning number - used to determine what has changed since your last pass - is safe from race conditions. Crucially, it is issued on transaction completion. Homebrew solutions can end up using a value (e.g. timestamp) issued at another point in time, and not of sufficient precision to always determine one transaction from another. This leads to edge cases where the determination of change may not be correct.The following series of MS documents describe how to set up change tracking. https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server(Although lightweight, it is still important to test performance using a volume of change representative of your production environment. There are parameters such as change retention period that can be adjusted to balance performance.)


Count orders 12 months back from a certain month group by that month

I don't have experience with Redshift but

WHERE TO_CHAR(create_date,'yyyymm') BETWEEN DATEADD(month,-12,create__date) AND TO_CHAR(create_date,'yyyymm')

looks wrong to me. You don't want to compare create_date with itself, you want to compare it with current_date, and you want to compare after each date is converted to a month. For instance:

WHERE TO_CHAR(create_date,'yyyymm') BETWEEN TO_CHAR(DATEADD(MONTH,-12,current_date),'yyyymm') AND TO_CHAR(current_date,'yyyymm')


Change drive letter (which contains system dbs)

You should follow the procedure outlined in the MSDN article "Moving System Databases"


You can create a client alias on the application server. You will have to run CLICONFG.EXE on the client, click add on the alias tab, click TCP/IP add the server alias B and set the connection paramters to be FC\SQLSRV after this all connections to server B from that client/application server will be forwarded to FC\SQLSRV If your application is 32bit you will also have to do the same for the 32bit registry and run %systemroot%\syswow64\cliconfg.exe


Postgresql failback without resync the data directory

Fundamentally, you can't without some kind of reset of the old master. Unless the old master is shut down before the new master is promoted and you ensure all WAL is synced from old to new master, the old will usually be slightly "ahead" of the newly promoted replica at the point where the replica is promoted onto a new timeline.

It will refuse to start replaying from the new master because doing so would corrupt the old master. The old master might have assigned transaction ID 1234 already. So what should it do if the new master says "I'm starting transaction 1234". It already has 1234! There are similar issues all over, at the low level crash safety parts of the system.

That's why you usually re-sync the old master from the new master before starting it up. This can be made a lot faster with rsync or one of the parallel variants.

However, you can instead use pg_rewind to "wind back" the old master to the point where its history diverged from the new master, so it can start replaying from the new master. I don't use it myself, so I can't do much more than point you at it. But one of my colleagues wrote a couple of blog posts that could help you:

Back to the Future Pt. 1: Introduction to pg_rewind

Back to the Future Pt. 2: How to use pg_rewind with PostgreSQL 9.5

repmgr can use pg_rewind with repmgr standby switchover. As far as I know it does not do so with auto-failover, but you might want to ask the repmgr team for clarification on that.

Personally I think auto-failover is a terrible idea unless you also trigger it randomly during your normal operations, so you always know it works. ChaosMonkey-style. Otherwise it's just a recipe for doubling your disasters. Prefer manually-triggered automated failover, with something like repmgr standby switchover, and alerting to tell you when something's wrong.


Script/tool to use to check when the server is not being heavily used and configuring backup on Availbility Groups

I will answer your second question.

  If I do my backups in my secondary replica, the log files in my primary replica will continue to grow big since it doesn't get truncated coz the log is not backed up in the primary replica

NO, this is where Availability Groups are so nice. No matter on which secondary replica you take transaction log backup it would work. What happens is when you take backup on secondary replica and after it finishes the secondary replica will give all information related to log backup to primary replica like LSN and VLF's that can be marked as truncated and accordingly primary replica does the changes. Bottom line is you can take log backup on any replica and the changes would be reflected on primary.

I would suggest you read Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups). From the BOL I quote

  A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).

So you can see you can take transaction log backup on any replica no matter whether it is synchronous or asynchronous replica.

  Is it possible to do the full backups on the secondary replica and log backups on the primary replica to avoid the log in the primary replica growing too big? Will it work if we restore the database?

Yes you can take full backup on secondary replica but you have to make sure that they are COPY_ONLY full backups. As already pointed in BOL article shared above.

  Backup Types Supported on Secondary Replicas

  •BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.

  •Differential backups are not supported on secondary replicas.

Please note that the backup history is kept in the msdb database of the replica the backup got executed on. This implies that backup history and chain cannot be retrieved out of one instance only.

Differential backup can only be done on instance which is primary node.

Restore will work as normal but I must tell you cannot just restore database which is part of availability group you need to first evict it out of AG and then perform the restore.

At last I suggest you to read MSDN blog series on Availability Groups backup and restore by SQLGardner for more details

If you are backing up lot of big databases it does create lot of I/O so your idea to configure backup when load is relatively very less is correct. Normally during midnight or during maintenance windows load is relatively very less you can physically look at server to see the load and configure backup at that time.


How to manage a growing MySQL slow_log table?

First of all, I discourage you from using the slow log with TABLE output, in production. The reason is that writes are locking, which limits the concurrency of your workload.

Moreover, CSV stored engine does not support indexes. Any non-trivial query will be very slow, so I don't see any advantages in doing this. Of course you could use MyISAM and add indexes, but then writes will become more expensive.

That said, the correct way to "rotate" the table is copying it and than truncate it. You will probably lose some queries every time: the ones ran after the copy but before the TRUNCATE TABLE statement.


Possibility of fixing corrupt MySQL database

"if there is any way to recover the MySQL data." - short answer to this question - it depends on how badly ibdata1 (and rest of important files) are corrupt.

It's a hardware error, IO system call returns Input/output error, so ibdata1 is definitely corrupt.

At minimum you need to copy ibdata1, *.ibd and *.frm files. If simple cp doesn't succeed copy a files with dd ignoring errors.

# dd if=/var/lib/mysql/ibdata1 of=/some/good/disk/ibdata1  conv=noerror

Once you copy all files try to start MySQL with innodb_force_recovery. Try all values from 1 to 6 until MySQL starts. If you are lucky and it starts, take mysqldump. MySQL may crash in the process. Try to dump individual tables.

for d in `mysql -NBe "SHOW DATABASES"`
do
    for t in `mysql -NBe "SHOW TABLES" $d`
    do
        mysqldump --skip-lock-tables $d $t > $d/$t.sql
        if [ $? -ne 0 ]; then sleep 30; fi
    done
done

If MySQL doesn't start - you can upload MySQL datadir in a zip/tar.gz archive on https://recovery.twindb.com/ (DISCLAIMER: I'm the author), it will try to recover the databases and will show you recovered data. (it will ask for a payment if you want to download a dump).

If you don't feel comfortable uploading your data google undrop-for-innodb (DISCLAIMER: I'm the author) and use https://twindb.com/recover-corrupt-mysql-database/ as a guide.

Percona's data recovery toolkit will also work (https://launchpad.net/percona-data-recovery-tool-for-innodb) (keep in mind stream_parser == page_parser, c_parser == constraints_parser). If you use Percona's toolkit make sure you use the latest revision from the trunk (bzr branch lp:percona-data-recovery-tool-for-innodb). Do not use version 0.5 available from downloads!


db_owner does not have access to Windows folders

I searched for this error before posting and could not find a result. After posting, the answer came up as a related question:

Error while taking backup using SSMS

To quote from that question:

"Here is answer for my own question SSMS uses "xp_fixeddrives" to list the drives in PC. Same is used by SSMS when clicking on button which is used to select backup path or browse directory.

The error is generated because "exec xp_fixeddrives" doesn't return any records when executed by a non-sysadmin account.When you run the same using user having sysadmin permission then "exec xp_fixeddrives" returns the list of drives. So it is the problem with SSMS ,and it is not a problem with any permission to the folder or account under which SQL agent services runs etc.(I have tested the same)"

One thing to add is that this only affects local SQL logins and not Windows Authentication accounts.


postgres promote hot_standby (archive_command = ':') to master (archive_command = '')

It's not normally recommended to promote the hot standby but have you read this article on it?
https://help.theatremanager.com/book/export/html/3679


sp_whoisactive - Do stats include activity for snapshot versioning?

I don't believe so, since none of the common DMVs used to track version store usage are present in the proc code.


Deciding what columns to use for partitioning

Always have an explicit PRIMARY KEY for any InnoDB table.  If practical, use a "natural" key -- a column, or combination of columns, that is unique.  Otherwise, add an AUTO_INCREMENT.

Learn about "composite indexes" -- this is using more than one column in an index.  It is often a good optimization.  It is not the same as having an index on each column.

It is OK for the indexes to take up more space than the data.  A quick survey showed 1/4 of my tables have that.  Having good indexes improves performance far more than the bulkiness hurts performance.

Each Index is updated for each row INSERTed, so having too many indexes hurts INSERTs (some).

You mentioned partitioning; did you mean "PARTITION BY ..."?  Don't do it unless you have a particular purpose.  I know of only 4 use cases; see my blog.

Do not bother to index low-cardinality columns (flags, enums, etc).  They won't be used.  If you have a status flag that is 10/90, then an index on just that column would be used for the 10, but not for the 90.  A composite index starting with that column may be always useful when all the columns (in the index) are in the WHERE clause.

The most important performance setting for InnoDB is to set innodb_buffer_pool_size to about 70% of available RAM.

It is usually a bad idea split DATE and TIME into two fields.  Look at how messy your SELECTs have to be.

How to create good indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Show us some of the SELECTs; we can advise further.


How to get history of mapping for following table data

System Version Tables in MariaDB-10.3 offers a range of features associated with versioning. This provides easy history viewing however its hard to trace changes.

Any table structure that preserves a unique unmodified key as the starting ID will make all required queries easier rather than resorting to CTE, procedures or other required recursive implementations.


How can I rectify an "arithmetic overflow occurred error" when trying to merge these two queries?

FWIW ... and in case you're (still) not sure why the original (combined) query was generating an overflow error ... consider:

missing join clause between the c and r tables (c.cnum = r.cnum)
missing join clause between the c and cf tables (c.cnum = cf.cnum) or r and cf tables (r.cnum = cf.cnum)
missing 'group by' clause

The missing join clauses means your query was generating a cartesian product between the c/r/cf tables (ie, generating a lot more join records than what you wanted).

The missing 'group by' clause means the sum() was being applied across that massive set of (cartesian product) join records; and as Henrico has suggested the sum() thinks it's working with integer datatypes so the sum() result is assumed to be integer; Sybase's integer datatype has a max value of 2B so when the sum() (sans a 'group by'; applied to massive cartesian product) hits above 2B you get the overflow error.

Your latest query includes those pieces (joins, 'group by') missing from the first attempt hence you're no longer generating an overflow error. ("Duh, Mark!" ?)


You're pretty much right but this isn't the best way to conceptualize it, rather you should draw out a Tree since that is the logical data structure typically used to hold the data within an index.
Using your notation, your first example and your third example are a little hard to distinguish between what is meant to be the key / row ID for referencing the actual row and what is the included field (but I understand what you mean, given your schema details).
Here is a good article on indexing and B-Trees which has a good example of how to conceptualize it. (You can ignore the database system specific comments, such as the section on SQLite, as the generals in this article apply to pretty much any modern relational database management system.) Additionally you can play around with this B-Tree simulator to help you visualize how they grow as data is added to an indexed table.


Conflicts are automatically detected when a merge or refresh operation is requested, and they are presented to the user in conflict views. There is one conflict view for each table.Each conflict view has a name in the form table_name_CONF.
To keep the rows that you want, first of all let me show you the syntax of ResolveConflicts procedure.
DBMS_WM.ResolveConflicts(
   workspace     IN VARCHAR2,
   table_name    IN VARCHAR2,
   where_clause  IN VARCHAR2,
   keep          IN VARCHAR2);

You can use 'keep' parameter to decide on which(Parent, Child or Base) to resolve the conflicts.

In your case you have define 'CHILD' for keep parameter. And where_clause(ID=1 in your case) identifies the rows to be refreshed . Your procedure resolves conflicts involving rows in the SOME_TABLE table in W_CHILD where ID is 1, and uses the values in the child workspace to resolve all such conflicts.
For details visit Oracle documentation:DBMS_WM Package: Reference


Redshift: How to fix serializable isolation violation(1023) caused by concurrent MERGE operations?

"Redshift is good at parallelism, not so good at concurrency. It's a feature."

Option 1

Is there more than one table you are loading to?

  One strategy is to enforce no more that one concurrent lambda per destination table.

You can do that with one primary lambda, limit it to 1 concurrent invocation. That primary can then in turn invoke a secondary lambda multiple times, each secondary handling a single destination table by passing on a filtered event message. You only need two lambda functions deployed, and only the secondary one is allowed multiple concurrent invocations. There are variations on that approach which involve queues or pubsub solutions. The performance gain here is in loading multiple tables concurrently rather than a single table.

Option 2

  Just use update rather than a delete then insert.

The challenge is that if two sessions both delete from the sales where saletime > somedate and then both insert records to the same table, then there is no order in which they could run serially which would produce the same result as when run concurrently. It's also ambiguous what you would want to happen when they run concurrently when the delete uses a wide date range like that.

  This could still fail if two sessions both open transactions that try
  to update the same rows.

If that is the case then you are abusing Redshift, effectively trying to use it for write transactions like an app database, doing OLTP in an analytical OLAP system, and you should consider a different datastore that is optimized for writes, perhaps something like Cassandra, or Postgres. You can also use something like Snowflake with separate dedicated write compute cluster and read cluster over the same data. Even with those alternatives it is just like every other OLAP system, optimized for bulk loads not small transactions, you don't get MVCC or fancy transaction isolation modes simultaneously with distributed parallel read performance. If you have budget then there are some hybrid options like MemSQL that allow a bit of both, but have their own compromises. For example there's no large cheap data storage on S3 / Google Buckets / Azure Blob storage, RAM is fast but scaling RAM is not cheap.

Option 3

  Do more specific deletes by filtering to a specific set of primary key values.

If you deleted specific rows e.g. based on a list of primary key values, then the two sessions would be unlikely to clobber each other in the way it does with the date filter.

Most ELT/ETL tools - e.g. stitch, fivetran, hevo, too many others - that load to Redshift will dump to S3, then COPY to a staging table and then uses insert when it is certain they are new rows or update when it is not certain. If you split out the known inserts and do them first then you can minimize the impact of the less performant updates.

When you do updates in Redshift, it will mark the rows for deletion (which will happen later in a vacuum operation) and insert the rows you are updating.

So it is already going to do implicitly, what you are trying to do explicitly. I do like the idea of running the delete then the insert though, because it gets to the point of what is going to happen anyway, it's just that you have now surfaced the serializability challenge with that strategy. In fact I have worked with a system that does exactly that, however it runs on a single EC2 and writes serially to Redshift, and despite the best practice recommendations it is fast, but uses a significant amount of IO and CPU on the Redshift cluster. Bulk loading with COPY is cheaper, especially if you split files on S3 into a sympathetic number of files in ratio to your count of cluster nodes, and use a manifest; the only way to really achieve parallel loads.

If you have multiple sources updating the same destination table then I would consider landing the data in separate tables before combining them.

If you have frequent changes coming from the same source to the same destination, then know that Redshift has a single commit queue and forces everything to be serialized, there is no performance gain to be had from attempting concurrent writes. To get the concurrent sessions to run, you either have to lock the table explicitly, a locking means one of them waits... which means they end up running serially. The other way you can get them to run without the error is organize your explicitly coded transactions to run serially, i.e. just limit it to a single invocation, in the knowledge that there isn't any real write concurrency anyway, so it's not going to lose any performance.

Redshift is just not optimized for that kind of write transaction isolation, the way that Postgres or other OLTP RDBMS are.

For more see How do I resolve the error "ERROR: 1023 DETAIL: Serializable isolation violation on table in Redshift"?


Store table in separate filegroup in SQL Server

Create / recreate the CLUSTERED INDEX. It's leaves are the data.

http://gallery.technet.microsoft.com/scriptcenter/c1da9334-2885-468c-a374-775da60f256f

Shows a script for this.

http://www.mssqltips.com/sqlservertip/2442/move-data-between-sql-server-database-filegroups/

has a step by step guide with Pictures. And more usefull scripts all around filegroups.

Both Show up when you ask "SQL move table filegroup" in Google.


Import (imp) not showing final report stats/results

I imagine the unescaped * character in your password is causing problems in the shell.

Escape it and you should be ok.


SQL Server 2008 R2 - How to export/migrate only tables and their PKs

You can try any of these methods given in How to copy tables from one database to another in SQL Server by Ahmad Yaseen:

  1) Using SELECT INTO Query
  the SELECT INTO SQL statement. This statement will create the tables in the destination database first, then it will copy the data to these tables. If you manage to copy the database objects such as the indexes and constraints, you need to generate script for it individually, after that you need to apply the scripts to the destination database.

  2) Using SQL Server Export / Import wizard

  Another method that can be used to copy tables from the source database to the destination one is the SQL Server Export and Import wizard, which is available in SQL Server Management Studio. You have the choice to export from the source database or import from the destination one in order to transfer the data.

  3) Using Generate Scripts

  SQL Server provides another way to generate script for the SQL Server databases with its objects and data. This script can be used to copy the tables’ schema and data from the source database to the destination one in our case.

  4)Using the ApexSQL Diff and ApexSQL Data Diff combination

  ApexSQL Diff is a useful SQL tool that can be used to find the differences between the databases from schema side and generate synchronization script to create these tables in the destination database in the correct order.

  ApexSQL Data Diff also is another SQL tool that can be used to find the differences between the databases from data side and generate synchronization script to insert the data in the destination database tables, taking into consideration the IDENTITY columns.


How do I debug max_allowed_packet going to -1?

max_allowd_packet= set to the size of the largest packet requesting; Usually it can set it to 32M and increase it according to ur application needs. Specify it in [mysqld] and client sections in my.cnf For example

[mysqld]
max_allowed_packet=128M

[client]
max_allowed_packet=128M


Is it possible to retrieve/get the query hash of a query without searching the DMOs?

If you have the queries you're testing in front of you, you can get either an estimated or actual plan. When the query plan pops up, highlight the select operator and hit F4. You'll probably need to toggle around between plan operators to get properties to show up for the one you want, but when they do you'll see the QueryHash, QueryPlanHash, and SQLHandle of your query.


Estimate transaction log size for single transaction

Is there any way to calculate such an estimation?

No. You cannot predict that. You should always do your updates or deletes in batches.


How to know spid which is filling tempdb from temp table name in sybase

The best is to use the montables. Make sure you have mon tables activated. Once verified you can issue the following select statement:

select SPID, DBName, ObjectName, PartitionSize
from master..monProcessObject
where DBID = tempdb_id(SPID)
order by SPID

for more information please see the official Sybase docs: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00967.1550/html/MigrationGuide/MigrationGuide14.htm


Select and Update MAC Addresses to Same Format

Test select:

select macaddress, upper(replace(macaddress,':','')) as new_macaddress
from Macs;

Update:

update macs
set macaddress = upper(replace(macaddress,':',''));

Fiddle

Test data:

create table macs
(
macaddress varchar(20)
);

insert into macs values('90CCAADD3341');

insert into macs values('90:3f:ff:11:22:33');

insert into macs values('33:44:aa:bb:34:6a');

insert into macs values('8801abcd3231');

Documentation links:

UPPER()

REPLACE()


Please look at the three main queries in the following answer (of mine):

SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type

The first query looks at currently running queries.

The second two queries (labeled "Query #1" and "Query #2") work in combination to find high-cost queries over a period of time.

To get alerts, look into options such as Solarwinds Free WMI Monitor or Spiceworks Network Monitor.

You need monitoring software of some nature. Those were 2 that I found for Windows that were free. Most of the free ones appear to be for Unix, or are at least Unix-based. You can also look at Splunk and Zabbix. You can use the queries I provided in the linked answer with at least some of those products. But they should do the "alert on high CPU for X minutes" thing.

You could also write your own system that polls on a scheduled, is scheduled, stores data in SQL Server, ages out old data, calculates % variance over a configurable time-frame, and takes action upon that condition being met. This is a lot more work as you now have even more code to maintain (but is still a viable option if the above doesn't work out for you).


I use pgAdmin 1.14.2 on Ubuntu, but I imagine this will work anywhere else as well.

In pgAdmin, go to Tools -> Backup..., where you'll get a dialog with a few tabs.  On the first one (File Options) give a filename and choose the 'Plain' format from the dropdown list.  On the next tab (Dump Options #1) tick the 'Use Insert commands' option at the bottom and the 'Only data' option at the top (and don't tick the 'Use Column Inserts' one).  On the Objects tab you can choose whichever tables you need then press the Done button.

Now you'll get a file with the INSERT statements for every row - and some other commands as well.  You can remove the latter with your favourite editor.  Then the list can be sorted - if you want to sort by the first column in the table (that is, more often than not the ID) then you can use a simple sort.  If you need a different sorting then you have to apply some magic in an editor.


To summarize what we hashed out in the comments, yes, you will first need to setup a file share with proper permissions as part of the Prerequisites outlined in the Configure Log Shipping Guide provided by Microsoft.

Once you have the fileshare created, you should be able to connect to it using a UNC path similar to \\servername\sharedFolder


I think what you're looking for is an aggregate on employeeID where the CASE goes inside the SUM() aggregate function.

SELECT employeeid,
       SUM((CASE WHEN [event] = 'Walk' THEN [time] ELSE 0 END)) AS walktime,
       SUM((CASE WHEN [event] = 'RUN' THEN [time] ELSE 0 END)) AS runtime,
       SUM((CASE WHEN [event] = 'Sleep' THEN [time] ELSE 0 END)) AS sleeptime
FROM #Bobsled
GROUP BY employeeID;

This way, you get one result row per employeeID, basically pivoted on event.


Restoring database without worring about partitions

I would suggest automating generation of the restore script. There are a few versions of this out there but Paul Brewer has a T-SQL and PowerShell version available.

I have seen some setups as well that just include generating a restore script when the backup is accomplished.

Which, just a note that a new open source backup solution was released by Sean and Jen McCown called Minion Backup. It includes the functionality of generating your restore scripts and migrating backups to development.


Who has deleted MySQL user from management console?

There is no audit trail, so you are out of luck this time.  For the future:

Plan A:  Build some kind of audit trail.

Plan B:  Look for an audit plugin, such as PAM.


Bi-directional synchronization between local SQL Server 2005 and a SQL Azure using SQL Data Sync

First: synchronize 1 table from Azure to Sql Server. Second: Execute some Store procedures in the SQL Server 2005 instance. And third: Synchronize various tables from Sql Server to Azure sequentially.

You can use the Azure data sync, but its buggy - since I never managed to get it running flawlessly for even a week. It is still in Preview mode with limitations.

Also, Data sync IMHO is suitable for database tables that deal with smaller volumes of changes per sync cycle.

If you dont want real time data sync, then I would suggest you to look into SSIS. Its much more flexible. I still use SSIS to move data from on-premise to Azure.

Alternatively, you can look into Sync Framework - Replicating, Distributing, and Synchronizing Data


Create a temp table to build the myid column for you

DROP DATABASE IF EXISTS neeraj;
CREATE DATABASE neeraj;
USE neeraj
CREATE TABLE mytable
(
    id int not null auto_increment,
    myid int not null default 0,
    name varchar(20),
    primary key (id),
    key (name)
);
INSERT INTO mytable (name) VALUES
('Alpha'),('Alpha'),('Beta'),('Charlie'),('Charlie');
SELECT * from mytable;
CREATE TABLE mytablemyid LIKE mytable;
INSERT INTO mytablemyid (name) SELECT DISTINCT name FROM mytable;
UPDATE mytablemyid A
INNER JOIN mytable B
USING (name) SET B.myid = A.id;
DROP TABLE mytablemyid;
SELECT * from mytable;

Here is the result:

mysql> DROP DATABASE IF EXISTS neeraj;
Query OK, 2 rows affected (0.07 sec)

mysql> CREATE DATABASE neeraj;
Query OK, 1 row affected (0.00 sec)

mysql> USE neeraj
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id int not null auto_increment,
    ->     myid int not null default 0,
    ->     name varchar(20),
    ->     primary key (id),
    ->     key (name)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO mytable (name) VALUES
    -> ('Alpha'),('Alpha'),('Beta'),('Charlie'),('Charlie');
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * from mytable;
+----+------+---------+
| id | myid | name    |
+----+------+---------+
|  1 |    0 | Alpha   |
|  2 |    0 | Alpha   |
|  3 |    0 | Beta    |
|  4 |    0 | Charlie |
|  5 |    0 | Charlie |
+----+------+---------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE mytablemyid LIKE mytable;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO mytablemyid (name) SELECT DISTINCT name FROM mytable;
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE mytablemyid A
    -> INNER JOIN mytable B
    -> USING (name) SET B.myid = A.id;
Query OK, 5 rows affected (0.05 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> DROP TABLE mytablemyid;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * from mytable;
+----+------+---------+
| id | myid | name    |
+----+------+---------+
|  1 |    1 | Alpha   |
|  2 |    1 | Alpha   |
|  3 |    2 | Beta    |
|  4 |    3 | Charlie |
|  5 |    3 | Charlie |
+----+------+---------+
5 rows in set (0.00 sec)

mysql>

Now you have a temporary column at your disposal

You could also build it as a separate table called mytablemyid

mysql> CREATE TABLE mytablemyid LIKE mytable;
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE mytablemyid DROp COLUMN myid;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO mytablemyid (name) SELECT DISTINCT name FROM mytable;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * from mytablemyid;
+----+---------+
| id | name    |
+----+---------+
|  1 | Alpha   |
|  2 | Beta    |
|  3 | Charlie |
+----+---------+
3 rows in set (0.00 sec)

mysql>


With OPTION RECOMPILE vs without OPTION RECOMPILE

With OPTION(RECOMPILE) optimiser knows the value of the @hid variable and essentially generates the plan, as if you wrote:

SELECT *
FROM H h
JOIN D d ON d.idH = h.id
WHERE h.id = 1

The generated plan has to be valid for this specific value of the parameter. It does not have to be valid for any other value of the parameter. Also, optimiser knows statistics of the table and usually can make a better decision. If there is only one value in the table that is equal to 1, most likely it will choose a seek. If there are a lot of values in the table that are equal to 1, it would choose a scan.

Without OPTION(RECOMPILE) optimiser has to generate a plan that is valid (produces correct results) for any possible value of the parameter.

As you have observed, this may lead to different plans.

I would recommend to look at both actual execution plans in the free SQL Sentry Plan Explorer tool. Look at details of each operator in the plan and you should see what is going on. You can do it in SSMS as well, but Plan Explorer is much nicer.


Should I use a separate table to store image file names?

I think it's better to let the images have their own table. The way I've solved it is by having 3 tables.

One for the products, another one for the images
and the third for the connections.

The product table just stores information about the product and nothing about the images.
The image table just stores the image id and file extension (the image id is also the filename).
The "connections"-table stores image id and product id so that you know which image belongs to which product.

Though, if only one image can be connected to one product you only need two tables. The product table and image table. The product table is just like above but the image table now contains a third column, productid. Now you can easily see which image belongs to which product.


Can a trasaction be rolled back if a table is edited outside a transaction?

A SAVEPOINT is not a commit.  It doesn't "save" anything other than the "point" where you are, in the transaction, when you create it... allowing you to roll back to the savepoint, if you need to, without rolling back the entire transaction.

You can't alter a table in another connection while one or more transactions is holding locks against it -- the other connection will be forced to wait for the table metadata lock.  You also can't modify the rows, gaps, or indexes locked by one transaction from within another connection... so either way you try to "edit" the table from outside the transaction, the second requester will have to wait.

This is where deadlocks come in to play. In oversimplified terms... If I do something in a transaction, then you do something in a transaction, then I try to do something that requires me to wait on a lock held by your transaction, then you try to do something that requires you to wait for a lock held by my transaction, we are in a deadlock condition, where we would both literally wait forever, since neither of us can proceed without the lock held by the other.  InnoDB will detect this and force one or the other of us to forfeit our transaction by throwing a deadlock exception.  The "winner" will proceed unaffected, not knowing anything happened to the "loser."


Error 3154 while restoring a backup using WITH REPLACE

You should use WITH REPLACE and in general avoid using the point-and-click thingies in Management Studio - they're inflexible and often have bugs.

This worked for me:

USE [master];
GO

CREATE DATABASE test;
GO

CREATE DATABASE test2;
GO

BACKUP DATABASE test TO DISK = 'c:\temp\test.bak' WITH INIT, COMPRESSION;
GO

RESTORE DATABASE test2
  FROM DISK = 'c:\temp\test.bak'
  WITH REPLACE,
  MOVE 'test' TO 'c:\temp\test2.mdf',
  MOVE 'test_log' TO 'c:\temp\test2.ldf';

Also you should make sure when you backup databases you use WITH INIT and/or don't point the device at a file that already contains a backup (since it might not be the same database you're backing up now - especially if you reuse names like test...).


Select rows where 'A1' is followed by 'A2'

Since you don't mention any specific DBMS I'll assume first that there are no window functions:

select A.Id, A.ColA, B.Id, B.ColA
from T as A
join T as B
    on A.Id + 1 = B.Id
where A.ColA = 'A1'
  and B.ColA = 'A2'

Assuming that window functions do exists:

select Id, ColA, Lead_id, LeadColA
from (
    select Id, ColA
         , Lead(id) over ( order by Id ) as Lead_Id
         , Lead(ColA) over ( order by Id ) as Lead_ColA
    from T
)
where Id + 1 = Lead_id
  and ColA = 'A1'
  and Lead_ColA = 'A2'

All untested, but you should get the idea

An alternative to @Vérace suggestion for a more tabular format is to use a lateral cross join that transpose the result. I.e.:

select X.Id, X.ColA
from T as A
join T as B
    on A.Id + 1 = B.Id
cross join lateral ( values (A.Id, A.ColA)
                          , (B.Id, B.ColA)
                   ) X (id, ColA)
where A.ColA = 'A1'
  and B.ColA = 'A2'

For SQL server I believe cross apply can be used in the same way. DB2, Oracle, Postgres all implement LATERAL, MySQL does not. The same technique can be applied with window functions as well:

select x.Id, x.ColA
from (
    select Id, ColA
         , Lead(id) over ( order by Id ) as Lead_Id
         , Lead(ColA) over ( order by Id ) as Lead_ColA
    from T
) as Y cross join lateral (
               values (y.id, y.cola)
                    , (y.lead_id, y.lead_cola)
) as X (id, cola)
where y.Id + 1 = y.Lead_id
  and y.ColA = 'A1'
  and y.Lead_ColA = 'A2'


What is the special database "postgres" for?

There is nothing special about it. As described in PostgreSQL documentation, Creating a Database, you need to be connected to a database, in order to create another one:

Since you need to be connected to the database server in order to execute the CREATE DATABASE command, the question remains how the first database at any given site can be created. The first database is always created by the initdb command when the data storage area is initialized. (See Section 17.2.) This database is called postgres. So to create the first "ordinary" database you can connect to postgres.

and in Template Databases:

Note: template1 and template0 do not have any special status beyond the fact that the name template1 is the default source database name for CREATE DATABASE. For example, one could drop template1 and recreate it from template0 without any ill effects. This course of action might be advisable if one has carelessly added a bunch of junk in template1. (To delete template1, it must have pg_database.datistemplate = false.)
The postgres database is also created when a database cluster is initialized. This database is meant as a default database for users and applications to connect to. It is simply a copy of template1 and can be dropped and recreated if necessary.


Compare data between 2 different schemas

Although the representation of the data differs between the old and new schema the data itself should be the same.  This means a series of reconciliation queries will provide certainty that the migration was without error.

Say you have an Orders table.  The total number of Orders per year, month or day will be the same in the old and new databases.  Similarly the total value of those Orders for each time period will match.  You can expand this idea for orders-by-product, orders-by-customer etc.

If any data was changed in the migration the corresponding reconciliation queries will not return the same value from old and new DBs.  You will know immediately that the problem was with an order from customer "X" on day "Y" for product "Z" and can focus your debugging on that area.

The same approach can be used for any other table.  Take customers as and example.  You can count customers by country, business area, first letter of name or any other differentiator you choose.


Nested Loop has very low estimates due to skewed data

Normally I would use filtered statistics to improve estimations when I have a table with skewed data. But in this case that doesn't seem to work.

You should find the following filtered statistic helpful:

CREATE STATISTICS [stats id (field=varchar)]
ON dbo.MyTable (id)
WHERE field = 'varchar'
WITH FULLSCAN;

This gives the optimizer information about the distribution of id values that match field = 'varchar', giving a much better selectivity estimate for the join:

The execution plan above shows exactly correct estimates with the filtered statistic, leading the optimizer to choose a hash join (for cost reasons).

This distribution information is much more important than the exact method used by the estimator to match the join histograms (fine or coarse alignment), or even the general assumptions (e.g. simple join, base containment).

If you can't do that, your options are broadly as outlined in answer to your previous question Sort spills to tempdb due to varchar(max). My preference would probably be an intermediate temporary table.


How to design data about a column can belong to the whole table?

Given the tables you have (I'm assuming this structure):

Products
--------
  ID
  Name
  VendorID
  MedicalCode

HelpText
--------
  ID
  Description

I'd suggest adding 2 new tables:

product_help_text
-----------------
  product_id
  help_Text_id

help_text_types
---------------
  id
  description

And changing HelpText:

HelpText
--------
  ID
  Description
  HelpText_type_id

So now, you could have some data like this:

Products
ID | Name      | MedicalCode
-----------------------------
1  | red pills | 445631-XS3
2  | blue pill | 043-PPLE-A

HelpText
ID  |  TypeID  |  Description
--------------------------------------------
1   | 1        | Take 3 times / day w.water
2   | 3        | Verify patient's health card
3   | 2        | check for other drug reactions

helpText_Types
ID  | Description
-----------------
1   | For the patient
2   | For the doctor
3   | for the pharmacist

product_help_text
product_id  |  help_text_id
---------------------------
1           |  1
1           |  3
2           |  1
2           |  2

product_help_text tells you which text goes with which product, and you can use the help text types to control which text gets seen where (for patient, pharmacist, doctor, drug sales rep, etc...).


Is it possible to import DBs information_schema, performance_schema and mysql from mysql-community-server-5.6.x to mariadb-server-5.5.x?

Maybe some of it.

information_schema is not a real set of tables; it is in-memory data that pretends to be tables.  So, a definite "no" for that.

performance_schema is mostly transient, so don't bother.

mysql changes between versions, so it would be risky to do so.  Use mysqldump (or other tool) to dump routines.  Do SHOW GRANTS ... to get grant statements to replay; this is somewhat tedious.  Start fresh for most things in mysql.

Also, Oracle 5.6 to MariaDB 5.5 is a step backward in many aspects.  Can't you go to 10.0, at least?  That version has more of what is in 5.6.


How to represent the following ternary relationship

This is a great question that shows the importance of breaking down n-ary relationships based on the business rules contained in them and is a good example of the application of forth normal form which deals with multi-value dependencies (MVD).

The following business rules exist:

Each employee may be assigned to one or more departments.
Each department may be composed of one or more employees.
Each department may be operating in one or more locations since some point in time.
Each location may be an address of one or more departments since some point in time.
Each employee must be found in one and only one location which is by definition a location that is an address of a department the employee is assigned to.
Each location may be an address of one or more employees.

Following is an ERD in Barker-Ellis notation created using Oracle SQL Developer Data Modeler showing these rules:

The key to the solution is to decompose the ternary relationship into the binary relationships supported by the business rules.  Those were the many to many relationships between employees and departments, and between departments and locations.  Then, implement the one to many relationship between location and employees that shows that, while an employee may be assigned to many departments, they can be found at only one of that department's many locations.

Note that it is not possible to depict the portion of rule 5 above that states "...which is by definition a location that is an address of a department the employee is assigned to."  This is a good example of the limitations of ERDs.  Their strength is the ability to visually depict entities and their relationships, and their weakness is they cannot depict more complex business rules beyond relationships.  This is why it is vital to investigate and discover all the business rules and not just the referential rules governing associations.  Fabian Pascal's paper Business Modeling for Database Design provides excellent detail on all the necessary rules to explore.  Ronald Ross has generated a large body of work on this topic as well.  Chris Date also wrote a short book on business rules.


EXECUTE AS USER = 'dbo' - The user does not have permission to perform this action

No, using EXECUTE AS 'dbo' in the CREATE TRIGGER statement and setting the Database to TRUSTWORTHY ON is not an option. I mean, it would technically work, but it's a completely unnecessary security risk (which is the case 99.9% of the time when wondering if you should enable TRUSTWORTHY).

Yes, you could use a Certificate to sign the Trigger, then copy the Certificate to [master] and create a Login from it and then grant that Certificate-based Login the VIEW SERVER STATE Server-level permission. That would also technically work. However, while I am a huge fan of Module Signing, I prefer simplicity when / where available (as long as it is not a security risk or unmaintainable). Fortunately for this situation (i.e. getting connection info for the current Session / @@SPID) there is a built-in function that has what you need: CONNECTIONPROPERTY:

SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID;
/*
Msg 297, Level 16, State 1, Line XXXXX
The user does not have permission to perform this action.
*/

SELECT CONNECTIONPROPERTY('client_net_address');
-- <local machine>

So, in your Trigger, replace your current DECLARE @source line with the following:

DECLARE @NetAddress VARCHAR(48) =
                     CONVERT(VARCHAR(48), CONNECTIONPROPERTY('client_net_address'));

Please note the change in datatype from what you originally had. The documentation states that the base type is VARCHAR(48), so it's best to go with that.


Select most recent record per day based on 3 columns

One way to accomplish this, is to use ROW_NUMBER
because you need on record  per day per user, I added in the PARTITION: a.UserName, CAST(CreatedDateTime as DATE)
 and for the newest record , I added ORDER BY CAST(CreatedDateTime as DATETIME) DESC
SELECT
    src.ID ,
    src.UserName,
    src.UPN,
    src.AppDisplayName,
    src.AppId,
    src.CreatedDateTime
FROM
(
    SELECT
        ID ,
        UserName,
        UPN,
        AppDisplayName,
        AppId,
        CreatedDateTime,
        ROW_NUMBER()OVER(PARTITION BY a.UserName, CAST(CreatedDateTime as DATE) ORDER BY CAST(CreatedDateTime as DATETIME) DESC)as rn
    FROM dbo.tbl_AppUsage as a
)as src
WHERE
    src.rn = 1

output:
ID          UserName    UPN              AppDisplayName  AppId         CreatedDateTime
----------- ----------- ---------------- --------------- ------------- -------------------
3           aaa         aaa@mail.com     Adobe DC        1234fdr234    07/10/2020 09:39:14
4           aaa         aaa@mail.com     Adobe DC        1234fdr234    07/11/2020 09:39:14
6           bbb         bbb@mail.com     Adobe DC        1234fdr234    07/10/2020 09:39:14
8           bbb         bbb@mail.com     Adobe DC        1234fdr234    07/11/2020 10:51:14

Base on your query, same output
SELECT
       ID
      ,UserName
      ,UPN
      ,AppDisplayName
      ,AppId
      ,CreatedDateTime
  FROM tbl_AppUsage as T1
  WHERE CreatedDateTime=(
      SELECT MAX(CreatedDateTime)
      FROM tbl_AppUsage as T2
      WHERE T2.UserName = T1.UserName
        --AND AppId = T1.AppId
        AND CAST(T1.CreatedDateTime AS DATE) =CAST(T2.CreatedDateTime AS DATE)
    )
  --GROUP BY UserName


How to rollback database change in production after few hours or days

In your scenario, the best OPTION is to use FLASHBACK DATABASE. It is as simple as it gets:
1.Create a restore point flashback database guarantee
create restore point my_save_point guarantee flashback database;

2.You apply your changes in Production, DDLs and DMLs. After some QA tests you realise something went wrong and you want to go to the save point
flashback datatabase to my_save_point ;

That last command will rewind the database to that moment in time, making a rollback of all the changes done from the moment you create the restore point.
I used a lot restore points for roll-outs to Production when they contain substantial changes that are very difficult to rollback using scripts.


Can I cluster PostgreSQL by sharing a data directory over NFS?

Use your PostgreSQL database with a shared disk it's a bad idea at all. Your database probably will crash and you WILL lose data.

If you need HA a solution based on shared disk(Like the RedHat Cluster Suite) do that by your storage device, creating a LUN and mapping it on both servers.

Take a look at this doc from EDB's Website:

http://www.enterprisedb.com/services/packaged-services/high-availability

Provide more details for a better solution.


Read-only database: Normalize or not for best query performace

All things being equal, a normalized database will provide better performance for a wide range of queries.  It is a myth that a normalized database will be slow, especially in an analytic environment or with a large number of rows.  Usually if performance suffers, it is because a database has not been properly normalized.
A normalized database also provides for better data integrity which, depending on your industry and whether or not you are storing data related to payments, may be necessary.
However, if you're just looking to dump data, not perform any sort of transforms, always return the exact type of data, and use the same access path you probably don't need to spend as much time normalizing the data to meet your requirements.
But it would still make sense to use a relational engine that features B-Trees (and preferably clustered indexes/index organized tables) to speed data recovery.  Some NoSQL engines support methods to quickly access single records (Hash maps, occasionally B-Trees), but they won't offer the flexibility of a good RDBMS (not SQLite).
Another thing to consider is staying power/support.  Many database engines have existed in a mature form for 30+ years and have existing tools developed to assist with backups/ETL/maintenance, a knowledge base, developers experienced in their use, and some assurance the worst bugs have been worked out (MongoDb was awful in that regard early on).
From comment:

I am storing payments. Why does a normalized database provides better
data integrity?

So it depends on whether you are handling payments in real time versus storing an archive history, but generally a relational database is preferable because:

Transactions can enforce complex operations are performed in a way that keeps the database consistent.
All operations against the database are logged, so changes made between backups are auditable.
With the right primary key definition, uniqueness is guaranteed and all totals/calculations performed will be consistent.
With the right clustered index and/or partitioning scheme, aggregation is very fast.


automatic soft NUMA results in odd number of cores

We can't answer your question of "Is there a performance problem?" Only you can answer that. Are you getting acceptable performance in production? I can tell you that I'm aware of a production system that gets acceptable performance with four sockets of 10 CPUs each with auto soft-NUMA enabled, so it's not like it's impossible to get acceptable performance need with soft-NUMA nodes having an odd number of schedulers.

If you suspect that there are performance gains to be had by changing your NUMA configuration then you should consider testing that. As far as I know it really all depends on your workload. The three choices that you have are to keep auto soft-NUMA, disable auto soft-NUMA, or to attempt a manual configuration with de-affinitizing CPUs or manual soft-NUMA by changing the registry. I'm not sure if I have all of the details right below but you might find information about what changes within SQL Server as you change your configuration to be helpful.

The number of lazy writers is determined by the number of physical NUMA nodes, so soft-NUMA won't change it. You can have up to four transaction log writers in SQL Server 2016, with one per NUMA node within SQL server. Those transaction log writers won't be spread evenly over your NUMA nodes: they are assigned to CPUs 1, 2, 3, and 4 as needed. You will have one resource monitor per logical NUMA node. I believe that you will also have 1 I/O completion thread per logical NUMA node, but I haven't specifically looked at that and don't know if there's a limit.

Maybe going through a few example configurations will help. Suppose you disable auto soft-NUMA. For that configuration you will have 2 logical NUMA nodes of 10 schedulers in SQL Server, 2 resource monitors, 2 lazy writers, 2 I/O completion threads, and 2 transaction log writers. You would no longer see the sp_blitz finding.

Now suppose you enable auto soft-NUMA. For that configuration you will have 4 logical NUMA nodes of 5 schedulers in SQL Server, 4 resource monitors, 2 lazy writers, 4 I/O completion threads, and 4 transaction log writers.

With manual soft-NUMA you could do something like create 3 soft-NUMA nodes of 2 schedulers, 4 schedulers, and 4 schedulers per socket. That would also avoid the sp_blitz finding. You could also make 2 CPUs per socket not visible to SQL Server in which case you'd have 8 schedulers per socket and auto soft-NUMA would not apply. Both of those sound like rather odd configurations to me.

So which of those configurations is best suited for your workload? Would your workload benefit from having 4 transaction log writers instead of 2? Would your workload be harmed by having 4 resource monitors instead of 2? I have no idea. All you can do is benchmark it. I haven't been able to find any information from Microsoft as to when auto soft-NUMA could be harmful for performance. Their point of view seems to be that it generally improves performance and scalability and to leave it on unless you experience a performance problem. IMO sending MAXDOP 4 queries to 4 soft-NUMA nodes of 5 schedulers doesn't sound bad to me at all.


how exactly code.org lost data with 32-bit index? Suppose they use MySQL database

By index, he actually means key column. The maximum value MySQL can store in a signed INT column is 2147483647 (documentation link), which is stored in 32 bits (4 bytes, 2 to the power of 32).

Their problem would have occurred when the table reached 2147483647 rows (and less if there are gaps) - any subsequent INSERTs would fail, and the data would be lost.

It's easy to reproduce what's happened:

Create a test table with a INT primary key:

mysql> create table incfail
       (
          pk INT PRIMARY KEY AUTO_INCREMENT
       );
Query OK, 0 rows affected (0.01 sec)

Set the primary key value to be nearly the maximum value:

mysql> alter table incfail AUTO_INCREMENT= 2147483646;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

INSERT a few values:

mysql> insert into incfail values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into incfail values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from incfail;
+------------+
| pk         |
+------------+
| 2147483646 |
| 2147483647 |
+------------+
2 rows in set (0.01 sec)

mysql>

The primary key is now at its maximum value, and the next INSERT will fail:

mysql> insert into incfail values (NULL);
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
mysql>

The data was lost:

mysql> select * from incfail;
+------------+
| pk         |
+------------+
| 2147483646 |
| 2147483647 |
+------------+
2 rows in set (0.01 sec)

mysql>


Adding a domain user in SQL Server Management Studio

For me it worked to generate a script with SSMS.

USE [master]
GO
CREATE LOGIN [domain\user] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [role] ADD MEMBER [domian\user]
GO


Postgresql performed a "fast shutdown" by itself. Why? How was it able to do that?

If you have unattended security updates enabled, that would be a reason. A new version of postgreSQL (security update) was released today.


Are physical backups sufficient in PostgreSQL?

Physical backups can only be restored onto the same version of PostgreSQL running on the same OS and type of hardware.  If you are running on obscure hardware or operating system, or ancient versions of PostgreSQL, this can be a problem.  If you are not doing those things, it should not be a problem.


Export/Import Subset of data in postgres

Your problem is that the output (row structure) of your query does not match either of your tables.

A possible solution would be creating a temporary table to put in your randomly generated IDs and copying data from one table at a time, using the necessary joins.

BEGIN;

CREATE TEMPORARY TABLE doc_ids ON COMMIT DROP AS
SELECT 1 + floor(random() * 1040000)::integer AS documentUUID
  FROM generate_series(1, 500) g;

COPY (
      SELECT d.*
        FROM Document d
        JOIN doc_ids ON i d.documentUUID = i.documentUUID
     )
  TO /tmp/document_data.sql;

COPY (
      SELECT c.*
        FROM Citation c
        JOIN Document d ON d.citation_id = c.citationUUID
        JOIN doc_ids ON i d.documentUUID = i.documentUUID
     )
  TO /tmp/citation_data.sql;

COMMIT;

Note that I removed the double quotes as they don't make any difference for the given table and column names.  Also I don't see what is the merit of grouping when generating the random document IDs.  Furthermore, I won't call the IDs ...UUID as UUID is a specific type used very often in very diverse applications.


Are we a good fit for SQL Server Enterprise and table partitioning?

My question is, do you think we should look into upgrading to SQL
  Server 2014 Enterprise so that we can partition our time sheet tables?

No. Absolutely not. On a pocket change server of 8 cores you would be spending ~$50k and be unlikely to see any benefit.

I would suggest trying, in this order:

Increase the memory allocation to SQL Server. You said the server is dedicated but have only half the RAM allocated. 32GB server, allocate 28GB.
Upgrade the server memory to [size of database] + 4GB.
Post the problem queries on dba.se.
Engage an expert.


Which tool to compare an Oracle database with a Postgresql database?

You could try the Schema Comparison Tool from dbsolo. I don't know if you can compare different databases with one another.

You have to keep in mind that Oracle and PostgreSQL have quite different table definition clauses which make it difficult to compare them accurately.


What is the impact of a large number of boolean columns in a table on storage and index performance in PostgreSQL?

I wouldn't bother.

Postgres has a record overhead of at least 23 bytes per record -- and that's not counting a bitmap to keep track of NULLs, or other block overheads which combined probably average another couple of bytes per record. See https://stackoverflow.com/a/26206031/391445
Your bigint takes 8 bytes of storage, versus 17 for the booleans.
Another option is to use bit strings, but these have an overhead of 5 or 8 bytes, so to store your 17 booleans you need 8 bytes anyway.
Your 4 other bigint fields take another 32 bytes of storage.

So expect to save only around 10% -- less if you have more non-boolean columns, but only slightly more as you add more boolean columns -- at the expense of making your code and data much more difficult to understand.

But if you have a lot of data (hundreds of millions of records) I would do some performance tests to compare.


SQL Query not throwing error even if the entered scale value is higher than specified

The biggest number you can fit into numeric(5,2) is 999.99. Therefore 123.556... is fine, but 1234.556... is too big.

You can find the rules for truncation, rounding, and errors during implicit conversions in the documentation:

CAST and CONVERT (Transact-SQL)

I don't know SQL-server that well, so I don’t know if you can configure the behaviour. One idea is to load the data into a table with say numeric(7,4). You can then check which values are truncated by a query similar to:

select t1.<key>
from t1 join t2
    on t1.<key> = t2.<key>
where t2.<col> - t1.<col> <> 0


Tuning Java Pool after getting ORA-04031

With SGA_TARGET set and JAVA_POOL_SIZE=0 it you are at least using Automatic Shared Memory Management, so the Java Pool is already self-tuning. You can specify a JAVA_POOL_SIZE value that will become the minimum pool size and Oracle may choose to tune it higher if necessary. Changing the JAVA_POOL_SIZE will require a restart of the database. You may need to increase the SGA_TARGET value as well, as increasing the default JAVA_POOL_SIZE will take memory away from other SGA pools.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/JAVA_POOL_SIZE.html#GUID-F24C4770-0B7C-42BD-9AAD-0EC1E92A90F6


Is it possible to use TSQL differential backups on a system that uses VSS?

It seems that there are two primary solutions to allow differential SQL backups on a server that does VSS backups.

1) On the full server backup, use COPY_ONLY if that is available.

2) Disable the "SQL Server VSS Writer" service.

Some full system backups using VSS record a backup to get SQL Server to pause long enough for a backup. This pause is recorded as if it were a backup but the file is never created.

In our case, disabling the SQL Server VSS Writer service seems to be the best approach.  Since the database is not quiesced during the system backup, we assume that the database will not be in a valid state after a full system restore and we will have to restore our regular SQL Server backups at that time.

More information can be found at:

How can I fix “Cannot perform a differential backup … a current database backup doesn't exist”?
How can I fix "Cannot perform a differential backup ... a current database backup doesn't exist"?

“Cannot perform a differential backup for database ”myDb“, because a current database backup does not exist.”
https://serverfault.com/questions/147459/

Quiesce operations and SQL backups https://nixx.ca/wiki/doku.php?id=vmware:quiesce_sql

Cannot perform a differential backup because current backup does not exist
https://sqlbackupandftp.com/blog/cannot-perform-a-differential-backup-database-current-database-backup-exist/

SQL differential backups failing after windows server backup https://social.msdn.microsoft.com/Forums/sqlserver/en-US/35080750-ad5d-4841-b2ba-e17ee5a799d5

SQL Server records a backup operation in the backupset history table when you use VSS to back up files on a volume https://support.microsoft.com/en-us/kb/951288

Error message when you try to restore a database by using SQL Server Management Studio in SQL Server 2005 after you use the Backup tool: "Restore failed for Server '' (Microsoft.SqlServer.Smo)" https://support.microsoft.com/en-us/kb/937683


Why does the alias not work in the GROUP BY clause?

Because these simply are the syntax rules. A column alias may be used in the ORDER BY clause but not in the GROUP BY clause.

If you look at the syntax diagrams this becomes obvious:

The order by clause is defined as:

So after the ORDER BY an expression is allowed, a position (order by 1) or a c_alias which stands for "column alias".

The group by is defined as:

As you can see, no c_alias option after it, so you can't use a column alias.

This is nothing special to Oracle though. This is how it was defined in the SQL standard.


What happens to mysql information_schema after switching to innodb_file_per_table?

The information_schema database is an all memory database made up of temporary tables. It is not stored in any of the InnoDB plumbing.

I wrote about this 5 years ago : How is INFORMATION_SCHEMA implemented in MySQL?

If you performed a mysqldump of the infomration_schema, reimporting it will do nothing.

mysqld will correct and protect any outside manual attempts to change it.

For example, suppose you wanted to create a database called mynewdb and your datadir is /var/lib/mysql. if you ran the following commands

cd /var/lib/mysql
mkdir mynewdb
chown mysql:mysql mynewdb

This will create a database in the eyes of mysqld.

mynewdb shows up in INFORMATION_SCHEMA.SCHEMATA

You can verify this by running the following:

mysql> SHOW DATABASES;
mysql> SELECT * FROM information_schema.schemata;

The database mynewdb will be present

As for your actual question, don't worry. You cannot break the INFORMATION_SCHEMA. mysqld won't let you.


Quick and Cheap

The quickest method for query review is indeed the slow query log, combined with a tool called pt-query-digest

An example process is to first set long_query_time=1. Set it to 0 for most accurate results, but it might affect IO depending on load of the system.

Let the slow query log run for a period of time, such as 1 week, to capture a significant number of query samples.

Then run pt-query-digest against the slow log.

This tool is free to use.

Continual process

To use a similar process, but have it behind a GUI to know when queries showed up or have been checked, you can set up Anemometer

An enterprise solution

The previous tools mentioned come without much cost, but only focus on queries.

A tool like VividCortex will provide you a great view of the performance of your system, and help you drill down into the queries in many different ways.

The method VividCortex uses to capture queries is lower impact than enabling the slow query log.


Creating rows in a table based on columns from another

A PIVOT statement, given all its hard-coding, is a glorified CASE statement. It was easier to build out a UNION query with all the counters, and run CASE statements against the values.

You can definitely convert this into dynamic SQL for future reference, but this is a good start.

SELECT [AccountID],
    [SiteID],
    [ThreatName],
    SUM(ISNULL([08/2016], 0)) as [08/2016],
    SUM(ISNULL([09/2016], 0)) as [09/2016],
    SUM(ISNULL([10/2016], 0)) as [10/2016],
    SUM(ISNULL([08/2016], 0) + ISNULL([09/2016], 0) + ISNULL([10/2016], 0)) AS [Quarterly_Total]
     FROM (
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'IRA' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [IRA] IS NOT NULL THEN [IRA] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [IRA] IS NOT NULL THEN [IRA] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [IRA] IS NOT NULL THEN [IRA] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
UNION
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'RFI' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [RFI] IS NOT NULL THEN [RFI] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [RFI] IS NOT NULL THEN [RFI] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [RFI] IS NOT NULL THEN [RFI] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
UNION
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'XSS' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [XSS] IS NOT NULL THEN [XSS] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [XSS] IS NOT NULL THEN [XSS] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [XSS] IS NOT NULL THEN [XSS] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
UNION
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'SQLi' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [SQLi] IS NOT NULL THEN [SQLi] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [SQLi] IS NOT NULL THEN [SQLi] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [SQLi] IS NOT NULL THEN [SQLi] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
UNION
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'BDP' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [BDP] IS NOT NULL THEN [BDP] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [BDP] IS NOT NULL THEN [BDP] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [BDP] IS NOT NULL THEN [BDP] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
) a
GROUP BY [AccountID],
    [SiteID],
    [ThreatName]


How can I send mail from a contained database is SQL Server?

The following solution involves the creation of a certificate to sign a newly created stored procedure in a contained database that emulates the functionality of msdb.dbo.sp_send_mail.  This has worked successfully in my testing and hopefully, it will work for you.

-----------------------------------------------------------------------------
--This example expects an empty directory called C:\ContainedDatabaseExample
--Make sure no files exist in the target directory first
-----------------------------------------------------------------------------

USE [master]
GO
--Cleanup from previous runs of this process
IF EXISTS (SELECT loginname FROM master.dbo.syslogins
    WHERE NAME = 'DatabaseMailFromContainedDatabases')
    DROP LOGIN [DatabaseMailFromContainedDatabases]
GO
--===========================================================================
--Create a new 'contained' database for testing
IF EXISTS (SELECT loginname FROM master.dbo.syslogins
    WHERE NAME = 'DatabaseMailFromContainedDatabases')
    DROP LOGIN [DatabaseMailFromContainedDatabases]
GO
IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Contained')
    DROP DATABASE [Contained]
GO
CREATE DATABASE [Contained]
 CONTAINMENT = PARTIAL
 ON  PRIMARY
(
NAME = N'Contained',
FILENAME = N'C:\ContainedDatabaseExample\Contained.mdf' ,
SIZE = 1024KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
 LOG ON
(
NAME = N'Contained_log',
FILENAME = N'C:\ContainedDatabaseExample\Contained_log.ldf' ,
SIZE = 1024KB ,
MAXSIZE = 1024GB ,
FILEGROWTH = 1024KB
)
GO
ALTER DATABASE [Contained] SET COMPATIBILITY_LEVEL = 130
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Contained].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Contained] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Contained] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Contained] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Contained] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Contained] SET ARITHABORT OFF
GO
ALTER DATABASE [Contained] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Contained] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Contained] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Contained] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Contained] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [Contained] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Contained] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Contained] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Contained] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Contained] SET  DISABLE_BROKER
GO
ALTER DATABASE [Contained] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Contained] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Contained] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Contained] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Contained] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Contained] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Contained] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [Contained] SET RECOVERY FULL
GO
ALTER DATABASE [Contained] SET  MULTI_USER
GO
ALTER DATABASE [Contained] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Contained] SET DB_CHAINING OFF
GO
ALTER DATABASE [Contained] SET DEFAULT_FULLTEXT_LANGUAGE = 1033
GO
ALTER DATABASE [Contained] SET DEFAULT_LANGUAGE = 1033
GO
ALTER DATABASE [Contained] SET NESTED_TRIGGERS = ON
GO
ALTER DATABASE [Contained] SET TRANSFORM_NOISE_WORDS = OFF
GO
ALTER DATABASE [Contained] SET TWO_DIGIT_YEAR_CUTOFF = 2049
GO
ALTER DATABASE [Contained] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [Contained] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [Contained] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [Contained] SET QUERY_STORE = OFF
GO
USE [Contained]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
ALTER DATABASE [Contained] SET  READ_WRITE
GO

--Use the 'contained' database
USE Contained
GO

--Create a new user in the contained database
CREATE USER [ContainedUser] WITH PASSWORD=N'BaconLover1', DEFAULT_SCHEMA=[dbo]
GO

--Create a new role that allows for easy additions of users to
--execute the 'contained' database new email procedure
CREATE ROLE [SendMailFromContainedDatabase]
GO

--Add the new contained database user to the database role
ALTER ROLE [SendMailFromContainedDatabase] ADD MEMBER [ContainedUser]
GO

--Create a new procedure in the Contained database that
--emulates all of the functionality of msdb.dbo.sp_send_dbmail
--My example below doesn't take any parameters and hardcodes
--enough information to send a test email.
--The actual procedure would need all of the normal parameters
--that msdb.dbo.sp_send_dbmail uses

CREATE PROCEDURE ContainedSendMail
    (@ValidEmailAddress varchar(100))
    WITH EXECUTE AS OWNER
AS
BEGIN
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
        ,@recipients = @ValidEmailAddress
        ,@subject = 'From Contained Database'
        ,@importance = 'High';
END
GO

--Create a certificate in the contained database that will be
--used to sign the new email stored procedure
CREATE CERTIFICATE [DatabaseMailFromContainedDatabases]
    ENCRYPTION BY PASSWORD = 'Password#1234'
    WITH SUBJECT = 'DatabaseMailFromContainedDatabases';
GO

--Sign the new email stored procedure with the certification
ADD SIGNATURE TO OBJECT::[ContainedSendMail]
BY CERTIFICATE [DatabaseMailFromContainedDatabases]
WITH PASSWORD = 'Password#1234';
GO

--Remove the PRIVATE KEY because it's not really needed
ALTER CERTIFICATE [DatabaseMailFromContainedDatabases]
REMOVE PRIVATE KEY;
GO

--Grant permissions to the database role to execute the
GRANT EXECUTE ON ContainedSendMail TO [SendMailFromContainedDatabase]
GO
GRANT VIEW DEFINITION ON ContainedSendMail TO [SendMailFromContainedDatabase]
GO

--Backup the newly created certificate from the contained database
BACKUP CERTIFICATE [DatabaseMailFromContainedDatabases]
TO FILE = 'C:\ContainedDatabaseExample\DatabaseMailFromContainedDatabases.CER';
GO

--Now, we need to import the 'contained' certificate into master
USE master
go
IF (select Count(*) from sys.certificates where name = 'DatabaseMailFromContainedDatabases') = 1
    DROP CERTIFICATE DatabaseMailFromContainedDatabases;
create CERTIFICATE [DatabaseMailFromContainedDatabases]
from FILE = 'C:\ContainedDatabaseExample\DatabaseMailFromContainedDatabases.CER';
GO

--We need to associate the new certificate with a server login
--and grant permissions
USE [master]
GO
IF EXISTS (SELECT loginname FROM master.dbo.syslogins
    WHERE NAME = 'DatabaseMailFromContainedDatabases')
    DROP LOGIN [DatabaseMailFromContainedDatabases]
GO
CREATE LOGIN [DatabaseMailFromContainedDatabases]
FROM CERTIFICATE [DatabaseMailFromContainedDatabases];
GRANT AUTHENTICATE SERVER TO [DatabaseMailFromContainedDatabases];
GO

--Add the new login to the MSDB DatabaseMailUserRole
USE [msdb]
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'DatabaseMailFromContainedDatabases')
    DROP USER [DatabaseMailFromContainedDatabases]
CREATE USER [DatabaseMailFromContainedDatabases] FOR LOGIN [DatabaseMailFromContainedDatabases]
ALTER ROLE [DatabaseMailUserRole] ADD MEMBER [DatabaseMailFromContainedDatabases]
GO

At this point, you 'should' be able to connect as the 'Contained' database user id (that was created in the example script above) and run

exec ContainedSendMail @ValidEmailAddress = 'YourEmailAddress'

making sure you provide a valid email address.  After a minute or so, I received the test email.


You need to check your SSL and TLS settings.

Check out this KB 3135244. The settings between the client and server should be somewhat consistent (i.e. if the server only allows 1.2 and the client only supports 1.0 I think you might run into issues). Also read through the known issues on that page

Updating to a build that supports TLS 1.2 might resolve your issue.


Storing more than 8000 bytes in a column

There are two things to consider if you want to store values > 8000 bytes in a single column in SQL Server.

First, the column must be capable of holding values of this length. For strings, this is typically done by giving the column a type of varchar(max) (for single-byte characters) or nvarchar(max) (for Unicode). As an example, the following table variable allows > 8000-byte strings in its column:

DECLARE @Example AS table
(
    LongColumn varchar(max) NULL
);

Second, you must ensure that any value you attempt to store in this column is also typed as e.g. varchar(max). For example, the following results in truncation, because the constructed string is implicitly typed as varchar (not varchar(max)) and therefore limited to 8000 bytes:

DECLARE @Example AS table
(
    LongColumn varchar(max) NULL
);

INSERT @Example
    (LongColumn)
VALUES
    (REPLICATE('x', 9000)); -- 'x' is implicitly varchar

SELECT
    E.LongColumn,
    DATALENGTH(E.LongColumn)
FROM @Example AS E;

The following code works correctly because it constructs a string of type varchar(max):

DECLARE @Example AS table
(
    LongColumn varchar(max) NULL
);

INSERT @Example
    (LongColumn)
VALUES
    (REPLICATE(CONVERT(varchar(max), 'x'), 9000));

SELECT
    E.LongColumn,
    DATALENGTH(E.LongColumn)
FROM @Example AS E;

See char and varchar and nchar and nvarchar in Books Online.


SQL Server 2016 ISNULL Returning 1900-01-01

This will happen with DATETIME as well as DATETIME2:

DECLARE @dummy DATETIME2 = NULL
SELECT @dummy, ISNULL(@dummy, N'')
GO

DECLARE @dummy DATETIME = NULL
SELECT @dummy, ISNULL(@dummy, N'')
GO

Along with DATE and TIME

DECLARE @dummy DATE = NULL
SELECT @dummy, ISNULL(@dummy, N'')
GO

DECLARE @dummy TIME = NULL
SELECT @dummy, ISNULL(@dummy, N'')
GO

I think the easiest way around it is to add a second CONVERT to a string:

DECLARE @dummy DATETIME2 = NULL
SELECT @dummy, ISNULL(CONVERT(NVARCHAR(30), @dummy), N'')
GO

Which returns an empty string as desired.

To answer your question about why, well, let's look at another scenario:

DECLARE @dummy INT = NULL
SELECT @dummy, ISNULL(@dummy, '')

The ISNULL here returns 0. If you're not up on how SQL Server stores and deals with dates, see my Q&A here for more information.

In short, if you run SELECT CONVERT(DATETIME, 0), you get a familiar result. There's no such thing as an empty number, and there's no conversion of an empty string to a numeric expression.

Hope this helps!


Incorrect GrantedQueryMemory(KB) Value in SSMS Dashboard report

Using XE to capture the query being executed by the dashboard, it just pulls the raw value from sys.dm_exec_requests.  Here's the query:

exec sp_executesql @stmt=N'
          SELECT master.dbo.fn_varbintohexstr(sql_handle) AS sql_handle,
          master.dbo.fn_varbintohexstr(plan_handle) AS plan_handle,
          case when LEN(qt.text) < 2048 then qt.text else LEFT(qt.text, 2048) + N''...'' end as query_text,
        r.session_id,
        r.request_id,
        r.start_time,
        r.status,
        r.statement_start_offset,
        r.statement_end_offset,
        r.database_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        r.wait_resource,
        r.last_wait_type,
        r.open_transaction_count,
        r.open_resultset_count,
        r.transaction_id,
        r.cpu_time,
        r.total_elapsed_time,
        r.scheduler_id,
        r.reads,
        r.writes,
        r.logical_reads,
        r.transaction_isolation_level,
        r.granted_query_memory,
        r.executing_managed_code
    FROM sys.dm_exec_requests AS r
        JOIN sys.dm_exec_sessions s on r.session_id = s.session_id
        outer APPLY msdb.sys.dm_exec_sql_text(sql_handle) as qt
    WHERE s.is_user_process = CASE when @include_system_processes > 0 THEN s.is_user_process ELSE 1 END',@params=N'@include_system_processes Bit',@include_system_processes=0

Notice the r.granted_query_memory in the select, with no multiplication or division being performed on it.  Note that this number is actually in pages.  When I run your query I get these different numbers:

sys.dm_exec_requests.granted_query_memory = 1,464 pages (times 8KB per page = 11,712 KB)
sys.dm_exec_query_stats.last_grant_kb = 11,712 kilobytes
Performance Dashboard = 11,993,088 "kilobytes"

This is exactly the number of bytes granted (11,712 * 1,024 = 11,993,088).

So it appears the bug is in the SSMS user interface code here, rather than in the query, as it's getting 1,464 back from the query, and must then be multiplying by 8 (conversion from pages to KB) and then 1,024 (accidental conversion from KB to bytes).  Good call reporting it to Microsoft.

I recently reported the exact same type of calculation problem to Microsoft in a Query Store report, which is documented in this Q&A: What is 'Log Memory' in Query Store 2017.  Seems like a common problem =)


How to avoid large joins when determining which rows a user is authorized for?

Like most things it's all about trade-offs. You can denormalize, maintain materialized views, etc, but all that has its own costs. It really depends on workload, query patterns, response time priorities, maintenance burden you're willing to bear, etc.

In general I'd recommend starting with the properly normalized design and join chain, but doing it via views for common queries. This allows you to later replace the view with a trigger-maintained materialized view, to selectively denormalize the schema, etc, without messing with the app's queries.

Don't try to fix it until you know it's actually a problem.


How to evolve MySQL schema while maintaining integrity

You could probably use the online schema change feature of ALTER TABLE in MySQL 5.6
Please note the MySQL 5.6 Documentation for ALTER TABLE

Specifying ALGORITHM=INPLACE makes the operation use the in-place technique for clauses and storage engines that support it, and fail with an error otherwise, thus avoiding a lengthy table copy if you try altering a table that uses a different storage engine than you expect. See Section 14.11, “InnoDB and Online DDL” for information about online DDL for InnoDB tables.

You would run the ALTER TABLE like this:
ALTER TABLE mytable ALGORITHM-INPLACE ... ;

What this will do is create a shadow copy of the table being changed. All reads and writes can go on with the original table. Additional changes made to the table is placed in the shadow copy. Everything is merged and presented at the end.
CAVEAT #1
The online schema change feature uses the folder mapped in tmpdir. Make sure the copy of the table can fit entirely in tmpdir. For example, if tmpdir is mapped to /tmp and the table you are changing is 7GB, there has to be more than 7GB free in /tmp. If /tmp is not large enough, please consider mounting /tmp on a large volume.
CAVEAT #2
Again, from the MySQL 5.6 Documentation for ALTER TABLE

As of MySQL 5.6.16, ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm because the table must be rebuilt, so specifying ALGORITHM=INPLACE in these cases results in an error. Specify ALGORITHM=COPY if necessary.

Changing Columns with older the DATETIME format will not work. You would have to use other means to make the new table.


Your single quote is in the wrong place in the datafile definition:

DATAFILE
'/opt/oracle/oradata/uat02/system01.dbf' size 400m,
'/opt/oracle/oradata/uat02/sysaux01.dbf' SIZE 300m,


plpy.execute('...') statements not in logs

In situations like this, the auto explain module, with log_nested_statements and log_min_duration set to 0 should show the statements executed inside of functions in PostgreSQL.