Get Row Value by Column Name of Name at runtime

You can use the UNPIVOT function to achieve this result set without having to join to the Field table at all.

This query shows how this works (only showing 3 columns for brevity):

SELECT Import_id,
  p.Field,
  p.Value
FROM
(
  SELECT Import_id,
    CAST(Test1 AS VARCHAR) AS Test1,
    CAST(Test2 AS VARCHAR) AS Test2,
    CAST(Test3 AS VARCHAR) AS Test3
  FROM Import_Table
) src
UNPIVOT
(
  Value FOR Field IN (Test1, Test2, Test3)
) p

You can see this in action here in the 3rd query in that db<>fiddle. For more columns, you simply add them to the inner SELECT statement and to the IN clause of the UNPIVOT statement. Alternatively, you can leverage the Fields table and dynamic SQL to build your UNPIVOT statement like the 4th query in the db<>fiddle.

This example requires SQL 2016 or higher as it uses the STRING_AGG() function, earlier versions will work with this method but require a custom string aggregation function.

DECLARE @Sql NVARCHAR(MAX)

SET @Sql = '
SELECT Import_id,
  p.Field,
  p.Value
FROM
(
  SELECT Import_id,
    '

SELECT @Sql = @Sql +
  STRING_AGG('CAST(' + FieldName + ' AS VARCHAR) AS ' + FieldName, ', ')
FROM Fields

SET @Sql = @Sql + '
  FROM Import_Table
) src
UNPIVOT
(
  Value FOR Field IN ('

SELECT @Sql = @Sql +
  STRING_AGG(FieldName, ', ')
FROM Fields
SET @Sql = @Sql + ')
) p'

EXEC sp_executesql @Sql


How do I drop all constraints from all tables?

You can derive this information easily by joining sys.tables.object_id = sys.objects.parent_object_id for those object types.
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
  ALTER TABLE ' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + N' DROP CONSTRAINT '
  + QUOTENAME(c.name) + ';'
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN ('D','C','F','PK','UQ')
ORDER BY c.[type];

PRINT @sql;
--EXEC sys.sp_executesql @sql;

PRINT is just there for eyeballing, not for copying and pasting the output (if you want to run it, that's what the commented-out EXEC is for) - if you have a lot of constraints, it may not show the entire script because PRINT is limited to 4,000 characters (8kb). In those cases, if you need to validate the entire script, see this tip for other ways to validate the script before running. For example:
SELECT CONVERT(xml, @sql);

Once you are happy with the output, uncomment the EXEC.


Can I grant multiple remote LocalService accounts access to a SQL Server database?

Machine accounts such as LocalService cannot be trusted over the network.

There is essentially no way to authenticate the remote machine without domain authentication of some kind.

Use SQL Server authentication; that's what it is meant for.

Alternately, you could provide a setup procedure that allows your clients to provide a domain account for each service to access the SQL Server database.  You'd need to provide a mechanism where they can enter domain credentials for each service when they are setting up the server end.


SQL Server OPENQUERY vs Regular Query against AS400

When you use openquery the query itself executed on remote server and you receive only results. In case of linked server, you local server does all the job. You may want to check
http://social.msdn.microsoft.com/Forums/eu/transactsql/thread/0e68f512-1e19-4c50-b343-219085d70076


I am using Amazon REDSHIIFT and want to make my table data case insensitive?

AFAIK, there's no way to do that. Redshift doesn't allow you to set specific collation settings. To get the CI behavior, you'd have to add either UPPER() or LOWER() consistently to all your queries.

Technically, you could make those case conversions during loading itself and let the application tier handle display data in the appropriate case. However, I'd recommend you do not attempt that unless you know for certain your DW will never have data sets that has to be case sensitive (i.e. source already Y has a different meaning/value from y) or an application that treats different cases differently. In those situations, you've effectively corrupted your data without knowing it.


Different CREATE_TIME for tables in Master and Slave

The variable innodb_optimize_fulltext_only was ON in master but OFF in slave. As a result, the OPTIMIZE TABLE commands were being skipped in Master server.


mongodb - mongos - query a mix of sharded and unsharded collections / databases?

A MongoDB sharded cluster deployment can contain collections that are either unsharded (the default when created) or sharded (based on your chosen shard key). Sharding a collection is (as at MongoDB 3.4) a two-step process: enable sharding on a database using sh.enableSharding() and then shard specific collections using sh.shardCollection(). Sharded and unsharded collections coexist in the same sharded cluster and your application generally does not have to be aware of whether collections are sharded.

If you have a sharded cluster deployment (irrespective of whether you have enabled sharding on individual collections) you should always make your queries and updates through a mongos instance. The mongos instances handle details of routing queries to the correct shard(s) based on the current configuration and status of backing mongod servers.

The "generally unaware" application design caveat alluded to earlier is that a few operations (for example: $lookup and $graphLookup) may not support using sharded collections as the source or destination. There are also some operational restrictions in a sharded cluster which are worth reviewing.

  I like the idea of sharding all collections in all sixdatabases, because it improves redundancy and allows for horizontal scaling

Sharding alone does not improve redundancy. Sharding is used to partition data across multiple servers so you can have multiple MongoDB instances presented as a single logical deployment. Ideally your shards should be backed by replica sets so you have data redundancy and failover (which both rely on replication). I would strongly recommend upgrading a production deployment to a replica set before moving to a sharded cluster.

If you plan on eventually sharding it would be reasonable to get your sharded cluster infrastructure in place so you can minimise disruptions in future. For example, moving from a replica set to a sharded cluster deployment involves setting up config servers, mongos instances, and changing your connection seed list from replica set members to mongos instances.

  because I am currently terrified of shard keys, I do not want to rush this process. So, I want to shard the collection that I must, and deal with the other collections later.

Is the collection that you must shard stressing your current deployment? Before moving to a larger deployment with more moving parts I would encourage you to try to understand and tune your existing deployment. There may be some easy wins that could improve performance ... or conversely, unfortunate design choices that won't scale well in a sharded environment. Helpful starting points include the MongoDB Production Notes, Operations Checklist, and Security Checklist.

If you have good understanding or estimation of your data model and distribution patterns, I would suggest sharding in a test environment using representative data. There are many helpful tools for generating fake (but probabilistic) data if needed. For an example recipe, see: Stack Overflow: duplicate a collection into itself.

  So, I will have two mongo servers, where one is just a shard of the 'user_posts' collection, and the other is a shard of the 'user_posts' collection but also contains some standalone databases.

Assuming you are describing a sharded cluster with two shards, that is correct. Sharded collections will be distributed across shards while unsharded collections will live on a primary shard for each database.

  Will mongos be able to query all six databases in this scenario?

mongos provides access to all databases in the same sharded cluster.


SQL Server 2012 AlwaysOn Availability Group

You can find here some design pattern for always on solutions
http://blogs.msdn.com/b/sqlcat/archive/2013/11/20/sql-server-2012-alwayson-high-availability-and-disaster-recovery-design-patterns.aspx

just note that for automatic fail-over  you may want to have synchronous commit so you dont loose data.
also to have automatic fail-over you must have node majority to decide to do the fail-over.
this mean you must have extra voter in the cluster. (eg' File Share Witness or another node)

this will prevent the problem known as Split Brain problem
(https://en.wikipedia.org/wiki/Split-brain_(computing))

to summarize, this is very common need to have local automatic fail-over (because local LAN can serve synchronous mode)
and another replica off-site with manual fail-over and asynchronous commit which can cause data loss when main site failed and not all data replicated yet to the another site.

I hope this have been informative for you.
I will be glad to add information if you have any questions about that implementation.


I'm sorry, but IT isn't magic...
If your network has high latency how can you expect data change to be spread without latency ?

But if you don't care about your writing rate on the master, you can slow it down with synchronous replication and you won't have any latency between your master and your standby. Of course you will now face writing lag on your master!

I'm afraid postgreSQL isn't suitable for your need in that case...
Here, you're facing the CAP theorem issue. Maybe, you could try another approach to favor consistency and partition tolerance as MongoDB, BigTable or Redis ?


Probably this will help:

  The Linux operating system normally terminates, or kills, a task by
  sending it a SIGHUP command. This stands for "Signal Hangup" and
  terminates a process on the system. If a programmer has not created a
  program restart command, or if you are having difficulties with an
  unresponsive program and wish to restart it, sending it a SIGHUP
  command will terminate the program and cause it to automatically
  restart.


As ypercube mentioned, you'll want to create your index on all three fields, currentUserId, threadId and timestamp like so: CREATE  INDEX IX_YourIndexName ON message (currentUserId, threadId, timestamp). The reasoning being you should index on all fields in your predicates (JOIN, WHERE, and HAVING clauses) to create a fully covering index (see 1.7 Covering Indices) which would be the currentUserId and threadId in this case. And then also adding the fields from your ORDER BY clause will help sort the index on those fields as well which will reduce the work for sorting when your query runs. For more information on indexing in SQLite.
Note you might even see a slight performance benefit by switching around the order of currentUserId  and threadId in the index definition, assuming the threadId is more unique than the currentUserId in the table. By defining your indexes with the most unique fields first, it improves the selectivity of that index because less values need to be seeked on upfront. So if threadId is more unique of a field, you could try this index definition as well CREATE  INDEX IX_YourIndexName ON message (threadId, currentUserId, timestamp). But unless the difference in uniqueness between threadId and currentUserId is a lot, or you have a lot of data, you probably won't notice much of a difference, so either definition should be equally fine.


Merge 2 pairs of tables from 2 databases. Beginner

how about?

create table DB3.tbl1 as
select * from (
  select
    DB1.tbl1.Col1,
    DB1.tbl1.Col2, ...
    DB1.tbl1.ColZ
  from DB1.tbl1
  union all
  select
    DB2.tbl1.Col1 + n
    DB2.tbl1.Col2, ...
    DB2.tbl1.ColZ
  from DB2.tbl1
)

could also replace the + n by + ( select count(*) from DB1.tbl1 )


Identify columns that allow NULL but don't contain NULL

I adapted the query from the linked answer in my question and have something I think works, but I'd prefer if it returned a table instead of just raising notices. I may improve it later but if I don't and anybody else does later, I'll switch the accepted answer. Ditto for any obvious performance improvements (though I understand this is likely to just be slow).

DO $$
DECLARE
   rec      record;
  _notnull  boolean;
BEGIN
   FOR rec IN
      SELECT format('SELECT COUNT(*) = 0 FROM %s WHERE %I IS NULL;'
                   , c.oid::regclass, a.attname) AS qry_to_run
            ,c.oid::regclass AS tbl
            ,a.attname       AS col
      FROM   pg_namespace n
      JOIN   pg_class     c ON c.relnamespace = n.oid
      JOIN   pg_attribute a ON a.attrelid = c.oid
      WHERE  n.nspname <> 'information_schema'
      AND    n.nspname NOT LIKE 'pg_%'  -- exclude system, temp, toast tbls
      AND    c.relkind = 'r'
      AND    a.attnum > 0
      AND    a.attnotnull = FALSE
      AND    a.attisdropped = FALSE
   LOOP
      EXECUTE rec.qry_to_run INTO _notnull;

      IF _notnull THEN
        RAISE NOTICE 'Table % has no NULLs in the NULLable field %'
                    , rec.tbl,rec.col;
      END IF;
   END LOOP;
END
$$;


How can I check for stored procedure consistency across multiples servers

Kankuru is a great and FREE monitoring tool for SQL Server developed here in France. Aside from its monitoring capabilities, it also has a DB compare tool that is easy and simple to use.

It will not only show you if your stored procedures, tables, indexes, types, etc. exist on two servers, but also what those differences are between those two servers.

Here is another compare screen

It's very easy to use and as mentioned before, won't cost you a dime.

To setup Kankuru

Create an empty database in some SQL Server called Kankuru. The kankuru database just stores configuration information and some history and its very compact.
Start Kankuru and from the Home Menu select Configuration

Configure kankuru to use the server\instance and database that you just created.

There is documentation, but its in French. Google translate will be able to get you through one way or another.


SELECT is using a non-PK Index instead of the PK

It has nothing to do with primary key, except that the primary key is typically clustered.

In your case SQL Server is not using the clustered index because, by definition, the clustered index includes all of the columns in the table. Since you only want Id, it is using a skinnier index that satisfies your query simply because it's less work to do so, and even if your table only has a single column, it's still going to choose the non-clustered index.

If I ask you to get me a beer from the fridge, your choices are to:

wheel over the entire fridge
bring over the case
bring over a single beer

In your case, 1. is using the clustered index, 2. is using some wide index, and 3. is using an index that only contains Id.

A clustered index is simply not always the best choice for an operation, much like a Ferrari is not the car you always want to use for a task (racing someone vs. towing a yacht, for example).


The smallest backup possible ... with SQL Server

First thought based on comments...

Use differential backups every, say, 6 hours, to reduce the size/time of backup + FTP. Then reduce your full backup + FTP to weekends only. This avoids complexity of log shipping, simple to do, and only adds slight complexity to DR

I feel that differential backups are overlooked... I've suggested using them before:

How to back up a small database in SQL Server 2008 R2 Express Edition
Using DIFF backups to solve this

  I'd like to keep everything as simple as possible from a recovery perspective whilst minimising the amount of lost data in the event of a failure

Pros and Cons of SQL Server back up strategies and their appropriate usage scenarios
Migrating large database
Using DIFF backups to speed up a backup/restore server migration

Edit: after jcolebrand's comment I'll attempt to explain more

A differential backup only takes pages that have changed. Outside of any index maintenance (which can affect a lot of the database), only a few % of pages will change during a day. So a differential backup is a lot smaller than a full backup before any compression.

If you have a full backup, say weekly, you can then do daily differentials and ship them off site. A daily full backup with differentials will still require both files off site.

This should solve the problem of getting data from A to B, C and D quickly.

You probably need to restore both the full and latest differential to get the latest data but you can maybe work around this with NORECOVERY and a STANDBY file (I haven't tried it with a diff restore for years since I was last in a pure DBA job).

An added bonus is that diff backups are unrelated to ongoing log backups so you can separate any High Availability/DR requirement from the "get data to the code monkeys" requirement.

I see some issues if you have daily full backups by policy or audit, but the diff restore can be applied before any log restores to shorten recovery time. Unlike backups, diff and log restores do interact.

Hope I've covered most bases...


How quickly can you get the databases restored from backups as compared to failing over to the DR replica? Failing over to the DR replica can be done without data loss, provided the primary replica is still accessible, and can be done in just a few seconds.


Please review my first thought-out attempt at a database schema

The picture was useful.  I think you did well.

I don't think of things as hierarchies, where stuff like Category gets in the way.  I make tables for the nouns (users, activity) and then adjectives become columns (category, class).

You use the word 'class' for 'employee type' or 'employee group'.  In a training system like this, I would have expected the 'class' table to store your 'activities'.  If your table names match the nouns that your end-users are using, it'll be easier in the long run.

My comments:

Give training_activities its own id, and use that from training_tracker.
An employee (a single employee profile) can't move from one company to another nor work for two companies at the same time.


How can I select just the 'Create Table' column of SHOW CREATE TABLE?

You can't use the result-set in MySQL directly, most connectors can give back a "normal" Result Set for SHOW, so you can use it in that language like a result from a select.
you must check out INFORMATION_SCHEMA Tables, there you find almost every information you need.


Read Scale Availability Groups and Listener

Is it possible to use a listener with a read scale availability group?

Yes. Not to put a "The answer is in the blog" answer, but I did write it and it apparently survived the Blog migration fiasco. So, the answer is in this blog...

The requisite excerpt is:

Please Note: When creating the listener, you'll need to use the IP Address of the primary node. Since this does not use WSFC, any failovers will need to have the listener dropped and re-created with the current primary node's IP address to properly read only route.

  I can create the listener but cannot connect to it.

Again, the requisite information from my post:

Then we get to the listener, which I will be using the local node's IP address and default port. The value of this may not even matter if it won't be registered in DNS based on what the use cases are.

Since SQL Server doesn't create DNS records (that's the job of the network name resource in WSFC) you'll need to manually create one and manually update it should you have a failover. The Listener only facilitates the need for read only routing. If you're not using read only routing then there isn't any point to creating a listener.

  I am interested in using the listener to only connect to the primary replica, I am not planning to use read only routing.

Then if you continue to want to use a read-scale AG and not a WSFC based one (or Pacemaker/etc.) then you'll want to have a load balancer in front of the read-scale AG that can do scripted health checks to know which IP is the primary and is healthy. Otherwise, don't use read-scale AGs.


Ideal way to start 'Database Server' in PostgreSQL 11

I would prefer option A.

Using systemctl ensures the service is properly (re)started when the computer is (re)started.

As far as the location of the data directory is concerned: the manual shows some defaults. The actual location of the data directory is essentially driven by the packager that built the RPM (or whatever package the OS uses) and the standards the distribution expects or uses. You could also put it under /databasedata/postgres/pg11/data if you want, or /very/important/data/postgres


What objective factors indicate it's time to implement SQL Server replication?

Replication should be considered during the design phase of the application.  If you have a geographically distributed workforce/userbase, having regional databases and central databases may make sense.  Disconnected databases on laptops can "synchronize" when they finally reconnect with the network (think sales staff on the road).

As for reporting purposes, replication is NOT the answer.  Most of the performance problems in a reporting environment stem from the fact that the reports are being written against a system that is configured for online transaction processing (OLTP).

Replication for reporting purposes is simply moving your OLTP data to another server, but keeping the same report-unfriendly structure.  In essence, you are throwing more hardware at the problem and increasing your maintenance costs, but for marginal gains.

What you should be looking at is how to get the specific data that your reports need, and transform them into a more useful format.  Create a feed that grabs new transactions from your production database and stores it in a reporting database, preferably on a separate server.  Every time the feed runs, it should grab all the data that is newer than the last time it ran, transform that data, and store it.  The reports that you currently are running will give you a good idea of the types of transformations required.

By following this approach, you are going to reap huge performance benefits.


Database Modelling of Type, Subtype Relation

Important Caveat:  You need to look at the link OP provided!  It uses the words type and sub-type as jargon in a way which is similar to, but different than what programmers or even data modellers would do.

The issue is that the rules state that if a card has a sub-type, that sub-type must be valid for the given (super)type.

Since not all types have sub-types in this model, you need to have a many to many association between the list of types and individual cards.  At the same time, you need to have a many to many association between the list of possible sub-types and individual cards.

You are in a bit of a relational model rock and hard place.  It depends on how you want to optimize your model.  You can optimize for storage or you can optimize for retrieval.

Optimizing for Storage: You can eliminate the redundancy by linking types without sub-types directly to cards.  When you have a type that has sub-types, don't link the type, link the sub-type instead.  This avoids redundancy and the potential inconsistency of parents and children contradicting each other.  It sucks for data retrieval, however.

Optimizing for Retrieval: You could instead link all types and sub-types (when applicable) to cards and then you have redundancy to manage - either with triggers or some other kind of application logic.

There is one more option you could try, which is a kind of combination approach and which manages to push the complexity into a more rarely used corner of your application.  In the third way, you collapse type and sub-type into a single table with an involuted relationship (self-reference).  Now when you assign a type or sub-type it automatically implies any parents of that thing.

Given the rules requirement for enforcing membership in the type before allowing membership in a sub-type, I would be inclined to just live with the redundancy.  You need a business rule that says you can't have the sub-type if you don't have the type.  That's the kind of business rule that needs to be in code anyway.


First, I think the link you had supplied doesn't fit for your question because it is about combining overlapped dates, and you need to generate (GAPs) new dates.

I would divide your data in two groups, those corresponding to Primary(s) and those that belong to Acting(s).

SELECT   t1.*,
         ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Employee, StartDate) P1
FROM     tbl t1
WHERE    t1.PosType = 'P'
ORDER BY t1.Employee, t1.StartDate
;
GO

Employee | Position | StartDate  | EndDate    | PosType | P1
-------: | -------: | :----------| :----------| :------ | :-
       1 |       30 | 10/12/2016 | 11/12/2016 | P       | 1
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2
       1 |       30 | 09/12/2017 | 26/07/2018 | P       | 3
       1 |       30 | 27/07/2018 | 27/07/2018 | P       | 4
       1 |       30 | 28/07/2018 | 31/12/9999 | P       | 5

Next Acting rows are subsumed into the second Principal row: 12/12/2016 | 08/12/2017

SELECT   t2.*,
         ROW_NUMBER() OVER (ORDER BY t2.StartDate) P2
FROM     tbl t2
WHERE    t2.PosType = 'A'
         AND t2.StartDate >= '20161212'
         AND t2.StartDate <= '20171208'
ORDER BY t2.Employee, t2.StartDate
;
GO

Employee | Position | StartDate  | EndDate    | PosType | P2
-------: | -------: | :----------| :----------| :------ | :--
       1 |       20 | 31/01/2017 | 02/02/2017 | A       | 1
       1 |       20 | 29/03/2017 | 31/03/2017 | A       | 2
       1 |       20 | 19/04/2017 | 21/04/2017 | A       | 3
       1 |       20 | 22/06/2017 | 23/06/2017 | A       | 4
       1 |       20 | 29/06/2017 | 30/06/2017 | A       | 5
       1 |       20 | 03/10/2017 | 06/10/2017 | A       | 6

Now let me generate a new dataset, for each Primary I'll add the corresponding Acting(s) using an OUTER JOIN, this outer join returns all Primary(s) even if they haven't subsumed Acting(s).

SELECT   t1.*, COALESCE(t3.P2, 0) P2, P, t3.PrevED, t3.SD, t3.ED, NextSD, t3.PT
INTO #mydata
FROM     (SELECT *,
                 ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Employee, StartDate) as P1
          FROM   tbl
          WHERE  PosType = 'P'
          ) t1
OUTER APPLY (SELECT   ROW_NUMBER() OVER (ORDER BY t2.StartDate) as P2,
                      LAG(t2.EndDate) OVER (ORDER BY t2.StartDate) as PrevED,
                      LEAD(t2.StartDate) OVER (ORDER BY t2.StartDate) as NextSD,
                      t2.Position as P, t2.StartDate as SD, t2.EndDate as ED, t2.PosType as PT
             FROM     tbl t2
             WHERE    t2.Employee = t1.Employee
                      AND t2.PosType = 'A'
                      AND t2.StartDate >= t1.StartDate
                      AND t2.StartDate <= t1.EndDate
            ) t3
WHERE    t1.PosType = 'P'
ORDER BY t1.Employee, t1.StartDate, t3.SD
;

CREATE CLUSTERED INDEX PK_Mydata ON #mydata(Employee, P1, P2);

SELECT * FROM #mydata ORDER BY Employee, P1, P2;
GO

  NOTE: I've used a temporary table plus and index, but you could try by using a CTE. You should check performance of both options.

This is the new scenario:

Employee | Position | StartDate  | EndDate    | PosType | P1 | P2 |    P | PrevED     | SD         | ED         | NextSD     | PT
-------: | -------: | :----------| :----------| :------ | :- | :- | ---: | :----------| :----------| :----------| :----------| :---
       1 |       30 | 10/12/2016 | 11/12/2016 | P       | 1  | 0  | null | null       | null       | null       | null       | null
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 1  |   20 | null       | 31/01/2017 | 02/02/2017 | 29/03/2017 | A
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 2  |   20 | 02/02/2017 | 29/03/2017 | 31/03/2017 | 19/04/2017 | A
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 3  |   20 | 31/03/2017 | 19/04/2017 | 21/04/2017 | 22/06/2017 | A
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 4  |   20 | 21/04/2017 | 22/06/2017 | 23/06/2017 | 29/06/2017 | A
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 5  |   20 | 23/06/2017 | 29/06/2017 | 30/06/2017 | 03/10/2017 | A
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 6  |   20 | 30/06/2017 | 03/10/2017 | 06/10/2017 | null       | A
       1 |       30 | 09/12/2017 | 26/07/2018 | P       | 3  | 1  |   20 | null       | 12/02/2018 | 02/03/2018 | 19/07/2018 | A
       1 |       30 | 09/12/2017 | 26/07/2018 | P       | 3  | 2  |   20 | 02/03/2018 | 19/07/2018 | 20/07/2018 | null       | A
       1 |       30 | 27/07/2018 | 27/07/2018 | P       | 4  | 0  | null | null       | null       | null       | null       | null
       1 |       30 | 28/07/2018 | 31/12/9999 | P       | 5  | 1  |   20 | null       | 04/09/2018 | 05/09/2018 | 12/11/2018 | A
       1 |       30 | 28/07/2018 | 31/12/9999 | P       | 5  | 2  |   20 | 05/09/2018 | 12/11/2018 | 13/11/2018 | 19/11/2018 | A
       1 |       30 | 28/07/2018 | 31/12/9999 | P       | 5  | 3  |   20 | 13/11/2018 | 19/11/2018 | 20/11/2018 | null       | A

I've added two partitions P1, P2 and I set up an index on them, these partitions will help me to filter information in this new scenario according to 4 types of rows:

|---------------------------------|   Type 1
|--P--|                               Type 2
       |--A--|                        Type 3
              |--P--|                 Type 2
                     |--A--|          Type 3
                            |--P--|   Type 4

Type 1:

Primary(s) that don't have any subsumed Acting.

P2 = 0

Type 2:

If exists, left gaps between Acting(s) plus between first Acting and Primary start date.

P2 <> 0
Actign start date - 1 > Primary start date/Acting end date

Type 3:

All Acting preriods.

P2 <> 0

Type 4:

If exists, right gaps between last Acting and Primary end date.

P2 <> 0
NextSD IS NULL (Last Acting)
Acting end date < Primary end date

Now the selection:

SELECT *
FROM
    (
    -- Primary(s) without Acting(s)
    SELECT Employee, Position, StartDate, EndDate, PosType
    FROM   #mydata
    WHERE  Employee = 1
           AND P2 = 0
    --
    -- plus all Acting(s)
    --
    UNION
    SELECT Employee, P, SD, ED, PT
    FROM   #mydata
    WHERE  Employee = 1
           AND P2 <> 0
    --
    -- plus all (left) Gaps
    --
    UNION
    SELECT Employee,
           Position,
           IIF(PrevED IS NULL, StartDate, DATEADD(day, 1, PrevED)),
           DATEADD(day, -1, SD),
           PosType
    FROM   #mydata
    WHERE  Employee = 1
           AND P2 <> 0
           AND DATEDIFF(day, IIF(PrevED IS NULL, StartDate, DATEADD(day, 1, PrevED)), SD) > 0
    UNION
    --
    -- plus all (last right) Gap
    --
    SELECT Employee,
           Position,
           DATEADD(day, 1, ED),
           EndDate,
           PosType
    FROM   #mydata
    WHERE  Employee = 1
           AND P2 <> 0
           AND NextSD IS NULL
           AND DATEDIFF(day, ED, EndDate) > 0
    ) T
ORDER BY 1, 3;
GO

This is the final result:

Employee | Position | StartDate  | EndDate    | PosType
-------: | -------: | :----------| :----------| :------
       1 |       30 | 10/12/2016 | 11/12/2016 | P
       1 |       30 | 12/12/2016 | 30/01/2017 | P
       1 |       20 | 31/01/2017 | 02/02/2017 | A
       1 |       30 | 03/02/2017 | 28/03/2017 | P
       1 |       20 | 29/03/2017 | 31/03/2017 | A
       1 |       30 | 01/04/2017 | 18/04/2017 | P
       1 |       20 | 19/04/2017 | 21/04/2017 | A
       1 |       30 | 22/04/2017 | 21/06/2017 | P
       1 |       20 | 22/06/2017 | 23/06/2017 | A
       1 |       30 | 24/06/2017 | 28/06/2017 | P
       1 |       20 | 29/06/2017 | 30/06/2017 | A
       1 |       30 | 01/07/2017 | 02/10/2017 | P
       1 |       20 | 03/10/2017 | 06/10/2017 | A
       1 |       30 | 07/10/2017 | 08/12/2017 | P
       1 |       30 | 09/12/2017 | 11/02/2018 | P
       1 |       20 | 12/02/2018 | 02/03/2018 | A
       1 |       30 | 03/03/2018 | 18/07/2018 | P
       1 |       20 | 19/07/2018 | 20/07/2018 | A
       1 |       30 | 21/07/2018 | 26/07/2018 | P
       1 |       30 | 27/07/2018 | 27/07/2018 | P
       1 |       30 | 28/07/2018 | 03/09/2018 | P
       1 |       20 | 04/09/2018 | 05/09/2018 | A
       1 |       30 | 06/09/2018 | 11/11/2018 | P
       1 |       20 | 12/11/2018 | 13/11/2018 | A
       1 |       30 | 14/11/2018 | 18/11/2018 | P
       1 |       20 | 19/11/2018 | 20/11/2018 | A
       1 |       30 | 21/11/2018 | 31/12/9999 | P

db<>fiddle here


How would i create a report involving multiple many to many relationships

I think it is a sound design, based on what is called an associative entity.  To query:

Select A.FieldList..., B.FieldList..., C.FieldList...
   From A
   Join AB On AB.AID = A.ID
   Join B  On B.ID   = AB.BID
   Join BC On BC.BID = B.ID
   Join C  On BC.CID = C.ID


When was Torn Page Detection and Checksum introduced to SQL Server and what are the upgrade behaviors?

In SQL Server 2000, if you want to identify corrupt pages, then the database option TORN_PAGE_DETECTION should be set to TRUE.
But in SQL 2005 and up, a new setting PAGE_VERIFY replaced the old TORN_PAGE_DETECTION which allows to choose from two different types of page verification :  TORN_PAGE_DETECTION and CHECKSUM.
Now the question comes which one to set  - TORN_PAGE_DETECTION or CHECKSUM ?
TORN_PAGE_DETECTION - writes a bit for every 512 bytes in a page allowing you to detect when a page was not successfully written to disk. The catch is that it wont tell you if the data stored in those 512 byes is actually correct or not due to the fact that couple of bytes may have been written incorrectly.
CHECKSUM - will caluclate a checksum of the page both when a page is written and when a page is read, assuming it has checksum on it.

The SQL Server computes the checksum based on the bit pattern on the page, stores it in the page header and then issues an I/O to write the page.  When the SQL Server reads the page, it re-computes the checksum using the same logic and then compares it with the value available in the page header. If the checksum value matches then it is assumes the page did not get corrupted during the write-read cycle.
Since the cost of computing the checksum is incurred on each page read and write, it can add to the CPU overhead and can possibly impact the throughput of your workload. Another thing to keep in mind is that the checksum is not unique for a specific bit pattern on the page. Two pages can possibly map to the same checksum value. So there is remote possibility that page corruption may go undetected.

Reference : Checksum in SQL2005
To specifically answer your questions :

I believe Checksum was introduced in SQL2005 and that upgrading or restoring a DB from a prior version would maintain it's previous page verify method. i.e. there was no implicit upgrade.

Yes CHECKSUM was introduced in SQL Server 2005 and is the DEFAULT. When you upgrade from 2000 to 2005, you have to explicitly change the database option Page Verify to use CHECKSUM.
If you restore the database already created on sql 2005 to another server running sql 2005, you dont have to set it. It will persist to what ever you have set the Page Verify option to.

I've not succeeded in researching when Torn Page Detection came in

From: http://support.microsoft.com/kb/230785

Versions of SQL Server earlier than 7.0
Versions of SQL Server earlier than 7.0 did not provide log parity or
torn bit detection facilities. In fact, those versions can write the
same log page multiple times until the log records fill the 2-KB log
page. This can expose transactions that have successfully committed.
If the log page is being rewritten during a failure, a sector with the
committed transaction may not get rewritten properly.

Thus, TORN_PAGE_DETECTION has been around since SQL Server 7.0. Even then, the default was that it was not enabled (same link).

Note Torn page detection is not enabled by default in SQL Server 7.0. See sp_dboption for how to enable the detection on your system.

Therefore, if the database was developed against a 7.0 instance and was subsequently upgraded, it would have upgraded the with the extant PAGE VERIFY option of NONE (as @ThomasStringer noted in his answer).

Edit : 09/24/2013  To improve the answer :
Refering to my SQL Server Internal notes from SQLSkills, I found that using a page dump, you can verify if torn bit detection - TORN_PAGE_DETECTION or CHECKSUM was enabled or not :
use database_name -- change here for your database !!
checkpoint
go
dbcc traceon (3604)   -- send output to screen
go
dbcc page (dbaalert, 1,1,0)
dbcc traceoff (3604)  -- turn off the trace flag
go

m_tornBits : This holds either the page checksum or the bits that were displaced by the torn-page protection bits – depending on what form of page protection is turnde on for the database.
Note: I dont have any older sql server versions running. Below is confirmed from sql server 2000 and up. If you have a 7.0 or 6.5 running around, you can confirm it as well :-)


SQL Server DR, mirroring or virtualise and mirror

I would implement Log Shipping, which is just a process of taking transaction log backups at one site, and restoring them at another site. This operates at the database level, and it will inherently handle all the schema changes because backups operate at a level above that.

There's really no other sane solution on Standard Edition because the requirement of having an asynchronous process severely limits the available options. (If you're just getting started in this area, don't even think about any kind of SQL Server replication.)

Note also that as far as I can tell (and I could be wrong about this), Windows Server 2012 storage virtualization (aka Storage Spaces) is meant as an enhanced RAID controller given a set of local disks; it's not for replicating virtual machines across data centers. I believe to do that requires specific storage hardware that supports it, and I don't think you're looking to go down that path right now.

  I dont want to put much more stress onto the SQL server as it is quite busy with both reads and updates.

Quantify this. What kind of options do you have to upgrade the hardware (I'm assuming it's several years old at this point)? Is the bottleneck CPU or I/O?

Regardless, if you're legitimately maxing out the available resources right now, virtualizing is a bad idea, as it adds inherent overhead to the system. Virtualization is great, and it may be appropriate here for other reasons, but definitely not if you're concerned about performance on your current physical hardware.

  I don't want the primary server waiting on the secondary server to update before it commits a write.

I'm assuming this is because there's a slow link between the sites? If that's the case, and you want to protect against hardware failure, consider implementing a synchronous site-local mirror (on a separate physical box using Database Mirroring) for high availability, and then an asynchronous remote site using Log Shipping for disaster recovery.

Any kind of mirroring or replication is going to add some latency or load to the primary server. How much depends on the hardware and the workload. This is the tradeoff you have to make to protect against failures. It isn't completely free, but with proper hardware and design, it can be minimized to the point that it's irrelevant.


You achieve this by following this query

select uthing.id, uthing.uid, thing.title
from uthing left join thing on uthing.tid = thing.id
where uthing.tid in (
        select thing.id
        from thing join uthing on uthing.tid = thing.id
        where uthing.uid = 1
     );


How the first IAM page is found by SQL Server

I can see that GAM was used to find a free extent and then a page from
  this extent is allocated to be used as a data page. Rows 8-12.

This extent allocation seen in your transaction log file is a uniform extent allocation for your allocation unit.

  And then it allocates another page which isn't from the extent used
  before and uses this page as IAM. Row 13-16.

This page allocation seen in your transaction log file is a page that is part of a mixed extent, as you noted this page is used by your IAM page.

  from where SQL Server finds this page and make it as IAM. Why this
  page is not allocated from an extent which is accessed by GAM.

Mixed extents

IAM pages are always part of mixed extents.

Proof of IAM pages always being part of mixed extents by Paul S. Randal on:

Inside the Storage Engine: IAM pages, IAM chains, and allocation units

  A couple more things to note about IAM pages:

  There are themselves single-page allocations from mixed extents and
  are not tracked anywhere

And another article by Paul S. Randal:

Inside the Storage Engine: Anatomy of an extent

  Mixed extents (SQL Server 2016 onward)

  The use of mixed extents is disabled by default, and the only pages
  that are mixed pages are IAM pages, and this cannot be disabled.

How are IAM pages allocated

In Pages and Extents Architecture Guide

  IAM pages are allocated as required for each allocation unit and are
  located randomly in the file. The system view,
  sys.system_internals_allocation_units, points to the first IAM page
  for an allocation unit. All the IAM pages for that allocation unit are
  linked in a chain.

IAM pages are located randomly in the file but are chained together per allocation unit.

Dependent on version and traceflag 1118

With this information at hand and knowing that pre sql server 2016 (without TF 1118) tables could get mixed extents for the first 8 pages allocated, one extent could in fact hold both IAM pages & data pages.

Again proven in the previously mentioned article: Inside the Storage Engine: Anatomy of an extent

  This means a mixed extent may hold a variety of page types
  too, including IAM, data, index, or text pages.

By default starting with SQL Server 2016 (or with TF 1118 enabled) data pages will be part of uniform extents and IAM pages will be part of a different, mixed extent.

This should explain why your IAM page is mapped differently from your data page and it's uniform extent that was allocated.

Testing mixed extent allocation

Creating some test tables & inserting one row per table on a SQL Server 2012 instance without TF 1118.

CREATE TABLE dbo.IAM1(id int)
INSERT INTO dbo.IAM1(id)
VALUES(1); -- IAM2,IAM3,IAM4, ... tables created

We can then use DBCC IND to map these allocated uniform pages, in the 150 - 180 range on my end.

If we then check the SGAM settings for the database that only has one SGAM page allocated, on page id 3:

DBCC TRACEON (3604);
DBCC PAGE ('database', 1, 3, 3);

We get this information for our page ranges

(1:152)      - (1:168)      = NOT ALLOCATED       --> 2 FULL uniform extents
(1:176)      -              =     ALLOCATED
(1:184)      - (1:256)      = NOT ALLOCATED

We see that two uniform extents, from 152 to 168 are full.

We could then check the PFS to see if these are IAM pages or not.

DBCC TRACEON (3604);
GO
DBCC PAGE ('database', 1,1, 3);

This is the result

(1:152)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:153)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:154)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:155)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:156)      - (1:157)      =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:158)      - (1:162)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:163)      -              =     ALLOCATED  50_PCT_FULL                     Mixed Ext
(1:164)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

5 IAM pages, not enough to get a full uniform extent of only IAM pages.
With one data page of one of the tables:

(1:163)      -              =     ALLOCATED  50_PCT_FULL                     Mixed Ext

And 10 System data pages.

You will also see these single page allocations when uniform extents are used by reading the transaction log file:

Operation               AllocUnitName           Description
LOP_FORMAT_PAGE         dbo.IAM3
LOP_BEGIN_XACT          NULL                    AllocFirstPage;0x0105000000000005150000002204bf2bf6b1f23aeef85d6e8f150100
LOP_MODIFY_ROW          dbo.IAM3                Allocated 0001:00000076
LOP_MODIFY_ROW          Unknown Alloc Unit      Allocated 0001:00000077
LOP_FORMAT_PAGE         dbo.IAM3
LOP_HOBT_DELTA          NULL                    Action 0 (HOBT_PAGE_COUNT) on rowset 72057594039173120. Leaf page delta: 0, Reserved page delta: 1, Used page delta: 1
LOP_MODIFY_ROW          dbo.IAM3
LOP_CREATE_ALLOCCHAIN   NULL
LOP_LOCK_XACT           NULL
LOP_COMMIT_XACT         NULL

EDIT

  Thanks for replying, but my question was how does the SQL Server
  decide the page 0001:00000135 for IAM. It should have looked somewhere
  in the GAM or SGAM or even PFS for free space check but it doesn't. I
  created a new database and a new table multiple times. And found out
  that the first IAM page is always the same and also the same data page

IAM / Mixed page allocation

Below quote that explains tempdb contention when using mixed pages should have the same application for user databases and it's IAM page allocation.

  When SQL Server searches for a mixed page to allocate, it always
  starts the scan on the same file and SGAM page. This causes intense
  contention on the SGAM page when several mixed-page allocations are
  underway. This can cause the problems that are documented in the
  "Symptoms" section.

Source

Correct me if im wrong but the mechanism it looks like it uses to allocate IAM pages is to check the SGAM page to get a mixed extent with free pages (SGAM bit = 1) and then uses the PFS page to find the first unallocated mixed extent page to allocate the IAM page.

(If there are mixed extents with free pages, if not, it also has to search the GAM page).

This statement is built upon the understanding that allocating a new IAM page should be the same as allocating new mixed extent data pages.

Managing space used by objects

  For each extent, the SQL Server Database Engine searches the PFS pages
  to see if there is a page that can be used

And  Paul S. Randal Why PFS pages cannot be repaired

  The answer is that the is-this-page-allocated-or-not information is
  not duplicated anywhere else in the database, and it’s impossible to
  reconstruct it in all cases.

Managing Extent Allocations

  To find a mixed extent with free pages, the SQL Server Database Engine
  searches the SGAM for a 1 bit.

  To allocate a mixed extent, the SQL Server Database Engine searches
  the GAM for a 1 bit, sets it to 0, and then also sets the
  corresponding bit in the SGAM to 1.


Restore non-cdb backup into pdb database

Good example shown here
"Creating PDB from a non-CDB"
https://shivanandarao-oracle.com/2015/10/02/creating-pluggable-databases-part-2/#CreatingPDBfromanonCDB


SQL Server 2014 extract value from table with double join in row and column and subtotal

This can be done in a step-by-step fashion.

First: we have the table sales differentiating between the PurchaseDateTime and the PurchaseDate:

CREATE TABLE sales
(
    UserName         varchar(100),
    PurchaseDateTime datetime,
    Currency         character(3),
    Price            numeric(10,2),
    Quantity         numeric(10,2),

    PurchaseDate AS CAST(PurchaseDateTime AS date),  -- Computed column
    PRIMARY KEY (UserName, PurchaseDateTime)
) ;

-- Secondary index, to help some JOINs
CREATE INDEX idx_sales_date ON sales(PurchaseDate, UserName);

This will make our life easier, as we already have the date precomputed.

Next thing you need to do is to unpivot your exchange_rate table:

CREATE TABLE #exchange_rates_EUR
(
    FXDate date,
    Currency char(3),
    ExchangeRate decimal(10,4),
    PRIMARY KEY (Currency, FXDate)
) ;

INSERT INTO #exchange_rates_EUR
SELECT
    FXDate, 'EUR' AS Currency, EUR AS ExchangeRate
FROM
    exchange_rates_pivot
UNION
SELECT
    FXDate, 'USD' AS Currency, USD AS ExchangeRate
FROM
    exchange_rates_pivot
// do the same for all currencies .. omitted here for simplicity

NOTE: I've used the name exchange_rates_pivot for your original pivoted exchange rates table.

You need this intermediate step to be able to do later on the proper JOINs (you can't actually JOIN in row and column. You cannot treat column names as if they were values [at least, not in any easy fashion]).

You can also use the UNPIVOT infrastructure from SQL Server, but this is more SQL Standard, although probably not as efficient.

The content of this table looks like:

SELECT TOP 12 * FROM #exchange_rates_EUR ORDER BY FXDate, Currency ;

FXDate              | Currency | ExchangeRate
:------------------ | :------- | :-----------
19/06/2017 00:00:00 | AUD      | 1.4705
19/06/2017 00:00:00 | BGN      | 1.9558
19/06/2017 00:00:00 | CAD      | 1.4827
19/06/2017 00:00:00 | CHF      | 1.0870
19/06/2017 00:00:00 | EUR      | 1.0000
19/06/2017 00:00:00 | USD      | 1.1199
20/06/2017 00:00:00 | AUD      | 1.4674
20/06/2017 00:00:00 | BGN      | 1.9558
20/06/2017 00:00:00 | CAD      | 1.4812
20/06/2017 00:00:00 | CHF      | 1.0854
20/06/2017 00:00:00 | EUR      | 1.0000
20/06/2017 00:00:00 | USD      | 1.1156

Second thing you need to do is finding the exchange rate for all your sales and put the info together.

For each specific purchase, the exchange rate is found by means of:

SELECT TOP 1
   ExchangeRate
FROM
   #exchange_rates_EUR er
WHERE
   er.Currency = s.Currency AND er.FXDate <= s.PurchaseDate
ORDER BY
   er.FXDate DESC ;

Note that the combination of TOP 1 and ORDER BY will give you the currency exchange for the day, and if not available, for the closest previous date where this info is available; as I've inferred from your example. For the sake of completeness, it should actually be divided by the exchange rate of EUR, in case the exchange data is not based on EUR = 1.00. I haven't done at this point not to overcomplicate things.

This will lead us to the following query:

SELECT
    q1.UserName, q1.PurchaseDate, q1.Currency, q1.Price, q1.Quantity, q1.ExchangeRate,
    CAST(q1.Price * q1.Quantity * q1.ExchangeRate AS decimal(10,2)) AS ConvertedAmountEUR
FROM
    (
    SELECT
        s.UserName, s.PurchaseDate, s.Currency, s.Price, s.Quantity,
        -- Subquery to retrieve Exchange Rate
        CAST (
           (SELECT TOP 1
              ExchangeRate
           FROM
              #exchange_rates_EUR er
           WHERE
              er.Currency = s.Currency AND er.FXDate <= s.PurchaseDate
           ORDER BY
              er.FXDate DESC
           )
           AS decimal(10,4))
        AS ExchangeRate
    FROM
        sales s
    ) AS q1
ORDER BY
    PurchaseDate, UserName ;

... which results in:

UserName | PurchaseDate        | Currency | Price | Quantity | ExchangeRate | ConvertedAmountEUR
:------- | :------------------ | :------- | :---- | :------- | :----------- | :-----------------
John     | 24/06/2017 00:00:00 | EUR      | 5.40  | 3.00     | 1.0000       | 16.20
Jude     | 24/06/2017 00:00:00 | USD      | 4.00  | 2.00     | 1.1173       |  8.94
Jack     | 25/06/2017 00:00:00 | USD      | 6.35  | 2.00     | 1.1173       | 14.19
Jill     | 25/06/2017 00:00:00 | EUR      | 4.15  | 1.00     | 1.0000       |  4.15
Joe      | 26/06/2017 00:00:00 | USD      | 4.50  | 1.00     | 1.1187       |  5.03

Last, we compute also the 'DAILY TOTAL', and perform a UNION ALL with what we already have. A WITH statement comes in handy in this case. Some smallish trickery is needed to ease the ordering (look for the OrderBy column).

This is done with the following statement:

; WITH exchanged_sales AS
(
SELECT
    q1.UserName, q1.PurchaseDate, q1.Currency, q1.Price, q1.Quantity, q1.ExchangeRate,
    CAST(q1.Price * q1.Quantity * q1.ExchangeRate AS decimal(10,2)) AS ConvertedAmountEUR, 0 AS OrderBy
FROM
    (
    SELECT
        s.UserName, s.PurchaseDate, s.Currency, s.Price, s.Quantity,
        -- Subqueries to retrieve Exchange Rate
        CAST (
           (SELECT TOP 1 ExchangeRate
           FROM      #exchange_rates_EUR er
           WHERE     er.Currency = s.Currency AND er.FXDate <= s.PurchaseDate
           ORDER BY  er.FXDate DESC) /
           (SELECT TOP 1 ExchangeRate
           FROM      #exchange_rates_EUR er
           WHERE     er.Currency = 'EUR' AND er.FXDate <= s.PurchaseDate
           ORDER BY  er.FXDate DESC) AS decimal(10,4)) AS ExchangeRate
    FROM
        sales s
    ) AS q1
)
SELECT
    UserName, PurchaseDate, Currency, Price, Quantity, ExchangeRate, ConvertedAmountEUR
FROM
    (-- The purchases date, user
    SELECT   *
    FROM     exchanged_sales
    UNION
    -- The daily totals
    SELECT   'DAILY TOTAL' AS UserName, PurchaseDate, NULL AS Currency, NULL AS Price, NULL AS Quantity, NULL AS ExchangeRate,
        SUM(ConvertedAmountEUR) AS ConvertedAmountEUR, 1 AS OrderBy
    FROM     exchanged_sales
    GROUP BY PurchaseDate
    ) AS q
ORDER BY
    PurchaseDate, OrderBy, UserName ;

... whose result is:

UserName    | PurchaseDate | Currency | Price | Quantity | ExchangeRate | ConvertedAmountEUR
:---------- | :----------- | :------- | :---- | :------- | :----------- | :-----------------
John        |   24/06/2017 | EUR      | 5.40  | 3.00     | 1.0000       | 16.20
Jude        |   24/06/2017 | USD      | 4.00  | 2.00     | 1.1173       |  8.94
DAILY TOTAL |   24/06/2017 |          |       |          |              | 25.14
Jack        |   25/06/2017 | USD      | 6.35  | 2.00     | 1.1173       | 14.19
Jill        |   25/06/2017 | EUR      | 4.15  | 1.00     | 1.0000       |  4.15
DAILY TOTAL |   25/06/2017 |          |       |          |              | 18.34
Joe         |   26/06/2017 | USD      | 4.50  | 1.00     | 1.1187       |  5.03
DAILY TOTAL |   26/06/2017 |          |       |          |              |  5.03

You can check all the step-by-step details at dbfiddle here

If you would need everything in just one statement, you could put the equivalent of #exchange_rate as part of the WITH. This will give you a very large statement, not so easy to read... For the all-in-one version dbfiddle here


SQL Server logshipping: Extend the LS_Alert window

update the relevant row and column (backup_threshold (in minutes)) in the log shipping config table
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/log-shipping-monitor-primary-transact-sql?view=sql-server-2017


Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY?

Because it is missing, I would suppose that your query execution plan shows a full-scan on your raw-data table. If this is the case, then your entire rows will be loaded into memory (including raw columns), which obviously is not ok.

The easiest way to avoid this would be to create an index on the filtering columns, so that the query will scan the index prior, and only if the row is a candidate for your result-set, will reach the table and load it into memory.

From comment discussion:

The query performs fast mostly because of the index (thus the slow performance when removing it) ...but your rows, are still  loaded entirely. As you thought, only some of the rows get into the memory, but this does not mean that only parts of them are loaded. The trick here is that the index points only to those rows that are relevant. You're using the index with a ">" operator, which causes a Range Scan, so it is possible that more rows than you need/get are referenced.

You could try to apply the 18 limit earlier, rather at the end of the query. Or you could make a new table with only the raw column and a foreign key to the first table. Link the result from the first table just at the very end of the query. Remember though, if you want to order the rows, and perform the ORDER BY after you join the two tables, it will still perform the sorting with the raw data loaded into memory.

Try to sort the rows of the main table before joining the raw table. If the main table is leading the join, then you should get the rows sorted after the join too.


Do I need to have two queues for Service Broker?

You do not have to, but is a good practice.

You must be aware that your 'from' service will also get messages in it's queue, even if you never send any. There are system messages, most obvious ones being the EndDialog and Error messages. You have to handle at least those. So your code that services the queue must be able to properly handle the messages sent to the initiator (the 'from' service). With a single queue for both services you will get a mix of messages, some for initiator some for target.

  will have code in a trigger to send a simple empty message

Make sure you do not do Fire and Forget, read How to prevent conversation endpoint leaks to understand why is important.


mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?

I have addressed something like this already

Oct 17, 2014 : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?
Jan 31, 2014 : Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?
Apr 11, 2013 : MySQL upgrade 5.0.88 to latest
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

Leaping two versions is risky if you do not want to run mysql_upgrade twice. My Oct 17, 2014 post mentions running mysql_upgrade twice, the right way.

In your case, you should do this:

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="set group_concat_max_len = 1048576;"
SQL="${SQL} select group_concat(schema_name) from information_schema.schemata"
SQL="${SQL} where schema_name not in"
SQL="${SQL} ('information_schema','mysql','performance_schema')"
DBLIST=`mysql -ANe"${SQL}" | sed 's/,/ /g'`
mysqldump --databases ${DBLIST} > MySQLData.sql
SQL="SELECT CONCAT('SHOW GRANTS FOR ',"
SQL="${SQL} QUOTE(user),'@',QUOTE(host),';') "
SQL="${SQL} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}" > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLGrants.sql
mysql ${MYSQL_CONN} -AN < GetGrants.sql | sed 's/$/;/g' >> MySQLGrants.sql
rm -f GetGrants.sql

Now just load MySQLGrants.sql and MySQLData.sql into the MySQL 5.6 instance.

GIVE IT A TRY !!!


How to pivot a table skipping bad value in Postgres

This is really straight forward:

INSERT INTO new_table
    (label, coord, value)
SELECT label, 'x', x  FROM coords  WHERE x <> 0
UNION ALL
SELECT label, 'y', y  FROM coords  WHERE y <> 0
UNION ALL
SELECT label, 'z', z  FROM coords  WHERE z <> 0 ;

or using LATERAL:

INSERT INTO new_table
    (label, coord, value)
SELECT c.label, v.coord, v.value
FROM coords AS c
    , LATERAL
     ( VALUES ('x', x),
              ('y', y),
              ('z', z)
     ) AS v (coord, value)
WHERE v.value <> 0 ;


How to capture error message from one step in another step of an Agent job?

I think your best bet is going to be querying the msdb.dbo.sysjobhistory table in the job step to get the information you're looking for.

Here's an example query that will get you all runs for a given job (mashed together from these two MSSQLTips articles [ref1], [ref2]).  Obviously, you'll want to adjust this to fit your means, but this should provide you with sufficient direction to get what you want.

SELECT j.NAME AS 'JobName'
    ,s.step_id AS 'Step'
    ,s.step_name AS 'StepName'
    ,msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime'
    ,((run_duration / 10000 * 3600 + (run_duration / 100) % 100 * 60 + run_duration % 100 + 31) / 60) AS 'RunDurationMinutes'
    ,CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
     END AS ExecutionStatus
    ,h.message MessageGenerated
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id
    AND s.step_id = h.step_id
    AND h.step_id <> 0
WHERE j.enabled = 1 --Only Enabled Jobs
    and j.name = 'JobNameHere'
ORDER BY JobName
    ,RunDateTime DESC


Maybe none of these matter, but here are some guesses:

Strange values in the data (in your application) due to 10 minute jump.
Out of order things if you need to back up 10 minutes.  Search your code for ORDER BY.  (Other constructs could be affected, too.)
cron jobs and MySQL Events that fail to run or run twice.  (If you have trouble here, re-code them to protect themselves.)

There are probably more things, but I can't think of any 'dangerous' ones.


Storing time with milliseconds in database

Personally, I'd use integer types for duration

Example: 340,000 milliseconds is 340000 in an unsigned int column.

To me, datetime, time, date etc are for explicit points in time. 340 seconds is meaningless in that context. Having a start date/time allows the duration to be added of course


How best to store complex cost information

After much investigation I think that NoSQL is the best way forward. Having unsearchable JSON fields doesn't appeal and I like the flexibility of schema free design.

I have created an example MongoDB document that covers everything I was trying to do:

{
    Cost_Type : "Actuals",
    Cost_Description : "200mm copper steam valve",
    Cost_UOM : "£K",
    Cost_Value : 1000,
    ECs : 2017,
    Source : {
        URL : ,
        Location_In_Source : "pg 203",
        Document : "The cost of valves over time.pdf",
        }
    Physical_Properties : [
        {
            Property : "Height",
            Property_Value : "200",
            Property_UOM : "mm",
            },
        {
            Property : "Weight",
            Property_Value : "200",
            Property_UOM : "kg"
            }
    ]
    Categories : [
        {
            id: "Valve",
            Path: ",Components,Pumps & Valves,",
            }
        {
            id: "Aircraft",
            Path : ",Industry Sector,Aviation,Aircraft,"
            }
        {
            id: "France"
            Path : ",Europe,France,"
            }
        ]
    Tags : ["Fuel system", "Hot system"],
}

The initial fields cover everything that is required for each cost.

The source field can either be an URL or an actual binary document.

The physical properties field holds an array of key value fields with an additional unit of measurement field. if I didn't need the UOM it would be simpler as I could just use a standard dictionary.

The categories are a reference to a mongodb graph collection which will be defined by an administrator to avoid bloat.

Finally the tags field will allow for the user to add any other information they like.

I think this should cover everything I need but now I just need to actually implement it in a MongoDB prototype.


How to access a fresh install of SQL Server 2012?

Since you have access to the machine itself, you can follow the instructions here to (re)add yourself as a sysadmin: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out

The short version of what that says is:

Stop SQL Server
Restart with the minimal configuration option -m
Log into the server with sqlcmd -S {your machine name or localhost}
Run the following commands:

CREATE LOGIN [Domain\LoginName] FROM WINDOWS;
EXEC sp_addsrvrolemember '[Domain\LoginName]', 'sysadmin';

Stop SQL Server
Restart it with the normal configuration


How to populate central management server registration from a list

I would probably recommend Powershell for this task, but I did find a post that allowed me to automatically generate CMS entries and it worked for me - Using T-SQL to populate a Central Management Server

Excerpting from that post:

Create an instance table with some information about the instances

CREATE TABLE Instance (Instance varchar(255), SQLVersion varchar(10),
    SDLC varchar(20));

INSERT INTO Instance VALUES
    ('(local)\sql2014cs','2014','Production'),
    ('(local)\sql2014ci','2014','Test'),
    ('(local)\sql2012','2012','Development'),
    ('(local)\SQLEXPRESS2012','2012','Production'),
    ('(local)\sql2008R2','2008 R2','Development');

Create CMS grouping

-- Version category
INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups_internal]
    VALUES ('Version','Instance Version',0,1,0);

-- Version subcategories
INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups_internal]
SELECT DISTINCT Instance.SQLVersion, Instance.SQLVersion, 0,
    Parent.server_group_id, 0
FROM Instance
CROSS JOIN msdb.dbo.[sysmanagement_shared_server_groups_internal] Parent
WHERE Parent.name = 'Version';

-- SDLC category
INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups_internal]
    VALUES ('SDLC','Instance SDLC level',0,1,0);

-- SDLC subcategories
INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups_internal]
SELECT DISTINCT Instance.SDLC, Instance.SDLC, 0,
    Parent.server_group_id, 0
FROM Instance
CROSS JOIN msdb.dbo.[sysmanagement_shared_server_groups_internal] Parent
WHERE Parent.name = 'SDLC';
GO

Add the actual instances

-- Instances broken out by Version
INSERT INTO msdb.dbo.[sysmanagement_shared_registered_servers_internal]
SELECT Parent.server_group_id, Instance.Instance, Instance.Instance,
    Instance.Instance + ' - ' + Instance.SDLC, 0
FROM Instance
JOIN msdb.dbo.[sysmanagement_shared_server_groups_internal] Parent
    ON Instance.SQLVersion = Parent.name

-- Instances broken out by SDLC
INSERT INTO msdb.dbo.[sysmanagement_shared_registered_servers_internal]
SELECT Parent.server_group_id, Instance.Instance, Instance.Instance,
    Instance.Instance + ' - ' + Instance.SQLVersion, 0
FROM Instance
JOIN msdb.dbo.[sysmanagement_shared_server_groups_internal] Parent
    ON Instance.SDLC = Parent.name


If the objects you want to move are part of a separate filegroup, then you can do a filegroup backup and restore. This is a little tricky - with SQL Server, you can do a piecemeal restore of the primary filegroup, plus other filegroups, and the objects are then online. However, this won't work if the database wasn't designed for this from the start (by separating objects into filegroups.)

If the objects are relatively small, and if all objects are in a filegroup other than the primary, AND if you were allowed to make changes to the database, then you could create a new filegroup, move the objects into it, and then restore just that filegroup. However, you still have to restore the primary filegroup first, so this only works if there's not much in the primary filegroup.

If those aren't the case, you could set up a new database in the other company's data center, and copy the objects in that you'd like to transfer over. You could do this with simple insert statements, or by setting up replication into the new database.

None of what I've said above is easy - but hopefully it plants a few seeds on other ways you could pull it off.


PRODUCT_ID as primary key rather then the UPC(UNIVERSAL PRODUCT CODE)

Auto-increment IDs are what's called surrogate keys. In most cases, data has some sort of a natural key as well. In your case, it sounds like the natural key is the UPC.

So, if there's a natural key, why create a surrogate key? There are several reasons. Two of the most important to me are size and changeability.

When a natural key is a varchar or nvarchar value, that key is frequently much larger than 4 bytes (the size of an int). For frequently used keys, the space difference involved can be noticeable.

Also, natural keys are most often values that have a meaning outside of the database. It may happen rarely, but occasionally something like a UPC code may change. If it's used as the key to your product table, updating all references to it can take a long time, and require a large number of locks.

In your example, I assume that the product of size 7 that didn't get entered would still have its UPC available. If so, you'd look up the UPC code's product_id, and use that value in your EAV table.

If the UPC is not stored in the database, some sort of identifying information still must be. Use the SKU of one of the related products (for example), or (better yet) add an appropriate overall description to the product record so you can locate it. If there's nothing related in the database, then logically the product ID could be anything, and wouldn't have to be connected to any existing data.


Run lo_export as client through psycopg2

OK simple solution. Use:

conn = psycopg2.connect("db=dbname etc etc")
lo = conn.lobject(12345)
lo.export('/tmp/test.jpg')


How do I change name format in TSQL Query Result?

DECLARE @Name varchar(100) = 'Doe, Jane'

SELECT SUBSTRING(@Name,CHARINDEX(',',@Name)+1,LEN(@Name) - CHARINDEX(',',@Name) )+ ' ' + SUBSTRING(@Name,1,CHARINDEX(',',@Name)-1)


Generate a sequence of timestamps but skip given periods

You can assign any numbers with a derived table. generate_series() is instrumental for a simple solution.

With a table like this:

CREATE TABLE things_to_do AS
SELECT * FROM generate_series (1,6) AS thing_id;

A simple solution for the given example

SELECT *
FROM   things_to_do
JOIN (  -- derived table with prepared timestamps
   SELECT row_number() OVER () AS thing_id, *
   FROM  (
      SELECT *
      FROM   generate_series(timestamp '2016-02-10 19:57'
                           , timestamp '2016-02-10 19:59'
                           , interval  '1 min') estim_date
      UNION ALL
      SELECT *
      FROM   generate_series(timestamp '2016-02-11 09:00'
                           , timestamp '2016-02-11 09:02'
                           , interval  '1 min') estim_date
      ) sub
   ) t USING (thing_id)
ORDER  BY 1;

Just append two sets ..

For a more general solution:

SELECT row_number() OVER (ORDER BY d, ts) AS id
     , ts + interval '1 day' * d AS ts
FROM   generate_series(timestamp '2016-02-10 09:00'
                     , timestamp '2016-02-10 19:59'
                     , interval  '1 min') ts
     , generate_series(0,6) d
ORDER  BY 1;

Produces a table with one row per minute from 09:00 to 19:59 for seven consecutive days starting at 2016-02-10 and an id number starting from 1 ...

Or:

SELECT row_number() OVER (ORDER BY d, ts) AS id
     , d + ts::time AS ts
FROM   generate_series(timestamp '2000-01-01 09:00'
                     , timestamp '2000-01-01 19:59'
                     , interval  '1 min') ts
     , generate_series(timestamp '2016-02-10 0:0'
                     , timestamp '2016-02-17 0:0'
                     , interval  '1 day') d
WHERE  d > '2016-02-10' OR ts > '2000-01-01 19:56'  -- start at '2016-02-10 19:57'
ORDER  BY 1;

Or:

SELECT row_number() OVER (ORDER BY ts) AS id
     , ts
FROM   generate_series(timestamp '2016-02-10 19:57'
                     , timestamp '2016-02-17 19:59'
                     , interval  '1 min') ts
WHERE  ts::time BETWEEN '08:00' AND '19:59'
ORDER  BY 1;


Best practice to store and execute a list of queries to build a Fact table

SSIS is included as part of your SQL Server license. Depending on the size, logical grouping, and dependencies I'd break the statements apart, into their own packages consisting of Execute SQL Tasks. Name these appropriately to what they're doing! It will assist in maintenance and trouble-shooting.

Create a Master Package that uses an Execute Package Task to individually call your T-SQL execution packages. This allows clean way that anyone can see the order, or parallelism, of your process. It also gives you a single package to schedule and view native SSIS reports from.

Additionally, all of these tasks can now be stored in a single Visual Studio (SSDT) project and should ideally be stored in source control. Ensure you download the appropriate version of BI tools for SQL Server Data Tools for your server version... they have backwards compatibility issues when deploying SSIS projects.

There are plenty of resources for master package design and such... this should set you on the right path for additional binging.


What are these Errors in a Previously Successful MySQL Script?

I didn't realize this was a two-part question when I asked it:

What are the errors?
Thanks to @Akina for the comment that answered this part of it... MySQL and/or MySQL workbench is unable to distinguish the delimiters that end individual statements from the delimiters that end the creation of the function. So typically one must set a different delimiter outside the creation of the function, then return the delimiter to normal after. I.e.

DELIMITER $$;
-- Create DB object here, using semi-colon at end of all statements except the last one.
DELIMITER ;

See https://www.mysqltutorial.org/mysql-stored-procedure/mysql-delimiter/ for more detail. As one who has never had to do that, it sounded like an annoying bit of extra work. But...

Why did this always work before?
Previously, I was running my script via JetBrains DataGrip. I believed the interface there to basically be just a terminal into mySQL. However, it turns out that DataGrip was automatically handling the necessary parsing of statements. So the manual delimiter handling was unnecessary within that environment.


Not sure I understand the problem properly but here is a sketch that might get you going:

create table t
( a char(10) not null
, b char(10) not null
,     primary key(a,b));

insert into t (a,b)
values ('home','door')
      ,('table','window')
      ,('door','table')
      ,('window','home')
      ,('home','table')
      ,('table','door');

For simplicity, we define a view so that a < b:

create view v as
    select distinct least(a,b) as a, greatest(a,b) as b from t;

Now we know that we can chain v via a self-join. As ybercube pointed out:

select v1.a, v1.b, v2.b
from v as v1
join v as v2
    on v1.b = v2.a;

won't work if we have the tuples (a,b),(a,c). So we add other connections as:

select distinct v1.a, v1.b, v2.b
from v as v1
join v as v2
  on v1.a in (v2.a, v2.b) or v1.b in (v2.a, v2.b)
where v1.a<v1.b
  and v1.b<v2.b;

Needless to say, this will be very inefficient for large tables.


Amazon RDS low in memory no mysql connections

The memory will be consumed by a number of things, including the MySQL innodb buffer pool, table and definition caches, etc.

RDS will default the innodb_buffer_pool_size to {DBInstanceClassMemory*3/4}, which is a good starting point, but does only leave a 1/4 of the RAM available for other parts of the system.

One thing that really hits the small RDS instances is the performance_schema - this can consume 500Mb RAM as soon as the server starts up, and never releases it.

If you are not using the performance_schema regularly, if at all, turn it off by setting the appropriate parameter within your RDS parameter group.

You will need to reboot the RDS instance if this is the case.

At the end of the day, a t2.medium instance only has 4Gb of RAM available to you (see http://www.ec2instances.info/rds/), and 52Mb is probably more than enough free for your system to continue to work without issue. Most of your data content will be in the buffer pool, so MySQL will respond directly from memory for most of your queries, and feel pretty responsive, even on such a small system.


Create intermediate derived tables of your main and mapping tables and then join these to compare and use a CASE statement to check the matching rows.
CREATE TABLE #MAP_CITY_A (CODE TINYINT, CITY VARCHAR(10), MEANING VARCHAR(10))
INSERT #MAP_CITY_A (CODE,CITY,MEANING)
VALUES (90, 'Paris', 'Active'),(85, 'Paris', 'Not_Active'),(60, 'Sydney', 'Active'),(75, 'Sydney', 'Not_Active')

CREATE TABLE #MAP_CITY_B (CODE VARCHAR(3), CITY VARCHAR(10), MEANING VARCHAR(10))
INSERT #MAP_CITY_B (CODE,CITY,MEANING)
VALUES ('IN', 'PA', 'Active'),('OUT', 'PA', 'Not_Active'),('IN', 'SY', 'Active'),('OUT', 'SY', 'Not_Active')

SELECT a.ID,
       a.CITY,
       CASE
          WHEN a.MEANING = b.MEANING THEN 'MATCH'
          ELSE 'MISMATCH'
       END as RESULT
FROM
(
    SELECT CITY_A.ID, CITY_A.CITY, #MAP_CITY_A.MEANING
    FROM CITY_A
    JOIN #MAP_CITY_A ON CITY_A.CODE = #MAP_CITY_A.CODE
         AND CITY_A.CITY = #MAP_CITY_A.CITY
) a
JOIN
(
    SELECT CITY_B.ID, #MAP_CITY_B.MEANING
    FROM CITY_B
    JOIN #MAP_CITY_B ON CITY_B.CODE = #MAP_CITY_B.CODE
         AND CITY_B.CITY = #MAP_CITY_B.CITY
) b
ON a.ID = b.ID
ORDER BY a.ID


Adding index only on read replica of MySQL RDS instance

If by any chance you are using AWS MySQL RDS as your database infra, you can refer this:

  To further maximize read performance, Amazon RDS for MySQL allows you to add table indexes directly to Read Replicas, without those indexes being present on the master.


You need to declare @acceptedvalues as a table variable.  Similar to the below.

DECLARE @acceptedvalues AS TABLE
(
AValue VARCHAR(50) NOT NULL
)

INSERT INTO @acceptedvalues (AValue)
VALUES ('One'), ('Two'), ('Three')

Then it will work as you have specified in the case statement.

Sorry, you need to query the @AcceptedValues.

WHEN numinput NOT IN (SELECT AValue FROM @AcceptedValues) THEN 'Two' END AS [validnumber]


Try this query:

select * from employee join employee_company on
employee.employee_id=employee_company.employee_id
join company on
company.company_id=employee_company.company_id
join (select
employee_id,
company_id,
min(score) score from score
group by employee_id,
company_id)
score on score.employee_id=employee.employee_id and
score.company_id=company.company_id
join factor_company on
factor_company.company_id=company.company_id
join factor on
factor_company.factor_id=factor.factor_id
where factor.factor_id =:factor_id;


Can not backup multiple (not all) databases Ola Hallengren

If you examine the code for DatabaseBackup.sql, you'll see that the definition for the parameter @Databases is @Databases nvarchar(max) = NULL.  When passing in literals like DB1, DB2, etc., the entire string needs to be surrounded by single quotes - (just like you used for literals for other parameters).

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'DB1, DB2, DB3',
@Directory = N'C:\Temp',
@BackupType = 'FULL',
@Verify = 'N',
--@CleanupTime = 360,
@CheckSum = 'Y',
@LogToTable = 'Y',
@NumberOfFiles = 4


SQL Server: calculating memory requirements for Full db backups

At this point, I think this could explain my observations:

https://support.microsoft.com/en-us/help/4347088/fix-out-of-memory-error-when-there-are-free-pages-in-sql-server


How can this table possibly be converted to a MySql table?

I'd pull this data into two tables: Factor's and STEN Lookup's.

Your Factors table would look like:

Factor_ID(PK) | Factor_Name
--------------|------------
  1           |      A
  2           |      B
  3           |      C
  4           |      D

Your `STEN_Lookup'

Factor_ID(FK) | Score_Value | STEN_Value
--------------|-------------|-------------
  1           |       0     |       1
  1           |       1     |       1
  1           |       2     |       1
  1           |       3     |       2
  1           |       4     |       2
  2           |       0     |       8
  2           |       1     |       9
  2           |       2     |       11
.....

I'd suggest you do some reading on normalization, as it helps massivly when designing your tables. A great Video Resource is MVA, (Video 2 on this hyperlink).


Can a table belong to multiple tablespaces?

A segment can only belong to one tablespace. So an ordinary table (plain heap table with no LOBs, varrays, nested tables or partitions) can only live in one tablespace.

Partitioned tables (or indexes, materialized views, ...) can have different partitions in different tablespaces though (with at least one restriction: all the tablespaces must have the same block size).

For tables with LOBs, LOBs storage can be out-of-line, in which case you can specify a different tablespace for each persistent LOB column. (Varrays can be stored as LOBs depending on size, so same applies for them.)

Index organized tables can have an overflow segment. When that's the case, the overflow segment can also be put on a different tablespace than the "main" part of the IOT.

Nested tables are also a special case (whether they're in heap tables or IOTs), and the main and nested parts don't have to live in the same tablespace.

There might be other specially handled types, but the main idea is: one segment lives in one tablespace. If you have an object with multiple segments (the most usual being partitions and LOB storage or overflow segments), then those segments need not all be in the same tablespace (with some limitations).


SQL Server shrink log after full and log backup

You should be ok, transaction log backup will allow a point in time recovery. You can shrink the tlog file without any issues. Since you already took the tlog backup with truncate, the log space should be empty (unless there's something like replication, log shipping, etc) and you should be able to shrink the tlog.

After that, you can continue with your normal schedule of full backup and tlog backups.

The only situation when tlog shrink is not recommended is when you already know the tlog will grow due the "normal" activity of your database. But from what you said, it should be ok to shrink it.


What is the best practice for storing large data when nvarchar(4000) is sufficient 99% of the time?

as soon as DataValue is over nvarchar(4000), internally, sql will store the data as a blob

This is incorrect. SQL Server will use off-row storage for both max and non-max types. In other words your NVARCHAR(4000) may also be stored off-row. The actual storage location will depend on whether the row fits or not on a page and on the exact combination of sp_tableoption values for the large value types out of row and text in row settings.

Your best bet is to change it to max. SQL will store the value in-row whenever possible, offering fast access. This makes application programing much simpler as you don't have to deal with the actual location (Column) based on size. You should only be concerned if you have an objection on adding the first max type column to a table (eg. it will prevent online rebuild operations), but you are already willing to add one so that should not be an issue.


How to display a number with fixed number of digits before the decimal?

It looks like you are trying to convert a number to a number - try to_char instead:

select to_char(123.56,'0999.99') from dual;

TO_CHAR(123.56,'0999.99')
-------------------------
 0123.56

Note that in contrast, select to_number('0123.56','0999.99') from dual; will succeed because the format specification matches, but will not return the text 0123.56 as you want - the '0999.99' in this case is an input format, not an output format - the number 123.56 will be returned.


Oracle - Apply SQL to all schema in database

DECLARE
v_sql varchar2(4000);
 cursor c1 is
    select o.owner,o.object_name from dba_users u,dba_objects o  WHERE u.account_status = 'OPEN' and u.DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM')
    and u.username=o.owner and o.object_name='ACCOUNTDETAILS' and o.object_type='TABLE';
BEGIN
    for REC in c1 loop
        v_sql := 'select * from '||REC.owner||'.'||REC.object_name;
        EXECUTE IMMEDIATE v_sql;
    end loop;
end;
/


Insert image with relative path to the postgres table

It uses PGDATA by default. We can see this using Process Monitor:

You'll have to explicitly set a path if you require otherwise.


SQL Server on CentOS using CAL licensing

Yes, the wrong SQL Server installation files (for the CAL-based setup) were used. The 20-core-limit version is designed for people upgrading from older versions of SQL Server, as explained by Aaron Bertrand.

Check with the team who got your installation files for SQL Server (ISO, CD, ZIP, etc) to get the core-based installation files instead of CAL-based installation files.

Microsoft's instructions for Linux don't include CentOS, but if you read through the comments on that page, they tell you how to pick your repo. If you're still using the latest CU repo and you're still getting the issue, then contact Microsoft support - they may have deployed the wrong binaries to the repo. (This wouldn't be something an outsider could fix.)


It's very hard to comment unless we know the nature of performance issue you are having which could be because of various factors.

Also waits you have captured are of 2 different means. One collected for the waits witnessed during the time you calculated for specific queries run. While other from Paul Randal is the aggregated one since last restart pointing CXpackets which does not necessarily indicate issue with it. That it collected wait stats for all the process that would have run since last restart. So it depends if you are looking to troubleshoot overall server performance or just those queries whose waits were captured.

Also depending upon the version of your sql server there is better way of interpreting CXpackets waits. Post sql 2016 SP2 you now have cxconsumer as well and hence can distinguish between good and bad CXpacket wait stats. Read  here for more on this by Aaron Bertrand.

Also I see you have MAXDOP 0 which should be tested with best magical number depending upon logical processors to be tweaked. Per my experience and support from MS the best magical number that works for most of our numbers is between 4 to 8 where logical processors is greater than 8. You can also see dbatools to look for a number recommended in your environment.

In addition to MAXDOP don't forget to check on cost threshold of parallelism for the server. 5 the default does not work well.

However you should also look other factors like indexing strategies finding opportunity to tune them thus helping in gaining performance


Looks like we have located the issue, DNS entry for the domain is wrong which is being pushed out via group policy.

Thanks to Pinal Dave for his blog post

https://blog.sqlauthority.com/2018/07/21/sql-server-install-error-validation-for-setting-agtsvcaccount-failed-error-message-the-rpc-server-is-unavailable/

Just in case the link becomes stale:

Against the properties of the Ethernet adapter, IPV4, DNS tab, in the section for 'Append these DNS Suffixes' we have an incorrect entry for domain, this is controlled by group policy (for us) we are resolving this now.


SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;

Table name and column name are two separate parameters to the pg_get_serial_sequence() function.
And you don't need a subselect.
Details:

How to reset postgres' primary key sequence when it falls out of sync?
Reset the sequence of a serial PK with dynamic SQL


Transaction Log file of the secondary database in Log Shipping grows fast and fills up disk space

I have a log shipping setup for DR. The transaction log file of one of
  the secondary databases, which is in restoring mode, is 7 times larger
  than the data file and is filling up the disk space. What are the
  possible causes and fixes?

Possible causes can be a heavy transaction or bulk load ran on the primary database which causes its log file to grow and since these log files are shipped to secondary you see the growth on secondary log file as well.

Fixes: You will need to analyse the growth on the log file at primary (since log file at secondary is in restoring mode), if the growth rate is intended to grow that big or was it some sort of AD-HOC activity which made the log file blow that big on primary that caused the similar sort of growth on secondary.

In case: log file is intended to grow that big and there are task that will continue to perform you may consider the option of going with appropriate disk/storage accordingly, because no matter you shrink the task will occupy the same space back. In case its an AD-HOC task, you can go ahead and shrink the log file at primary ( making sure latest log backup is taken and copied to secondary) which should apply the same at secondary.

  Note that it is the transaction log file of the secondary database in
  the DR site that is growing fast.

Here if you meant that primary is stable with its log file and its only the secondary which is growing then , you need to consider looking at how log shipping is configured. I don't recall such scenario where primary log holds better while secondary grows that big, unless until log shipping is not set up correctly. If that's the case, consider setting up LS again with proper settings.


Incremental encrypted per database backup and Partial restore policy for MySQL 5.7 in replication setup

After digging a bit and also based on this page I have a solution that seems to work fine.
Step 1: Decompress and decrypt backup files
xtrabackup --decompress --decrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
    --target-dir=/data/backups/db_xxx/full --remove-original
xtrabackup --decompress --decrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
    --target-dir=/data/backups/db_xxx/incr_1 --remove-original
xtrabackup --decompress --decrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
    --target-dir=/data/backups/db_xxx/incr_2 --remove-original

Step 2: Prepare the data

As we just want to restore one database and not the full mysql server data we pass --export to generate ready to copy files.
We pass also --apply-log to all backup except the last one (from the docs I linked in the question)

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/db_xxx/full/ --export
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/db_xxx/full/ --incremental-dir=/data/backups/db_xxx/incr_1 --export
xtrabackup --prepare --target-dir=/data/backups/db_xxx/full/ --incremental-dir=/data/backups/db_xxx/incr_2 --export

Step 3: Restore the data
Now we need to replace the table files without deleting the database.
mysql> SELECT CONCAT( 'ALTER TABLE ', a.TABLE_SCHEMA, '.' a.TABLE_NAME, ' DISCARD TABLESPACE;' ) AS 'DISCARD TABLESPACE'
    -> FROM information_schema.tables a WHERE a.TABLE_SCHEMA='foo';
+-----------------------------------------------+
| DISCARD TABLESPACE                            |
+-----------------------------------------------+
| ALTER TABLE foo.t1 DISCARD TABLESPACE;        |
| ALTER TABLE foo.t2 DISCARD TABLESPACE;        |
+-----------------------------------------------+
1 row in set (0.00 sec)

cp -a /data/backups/db_xxx/full/foo/* /var/lib/mysql/foo/
chown -R mysql:mysql /var/lib/mysql/foo/

mysql> SELECT CONCAT( 'ALTER TABLE ', a.TABLE_SCHEMA, '.' a.TABLE_NAME, ' IMPORT TABLESPACE;' )
    -> FROM information_schema.tables a WHERE a.TABLE_SCHEMA='foo';
+-----------------------------------------------+
| IMPORT TABLESPACE                             |
+-----------------------------------------------+
| ALTER TABLE foo.t1 IMPORT TABLESPACE;         |
| ALTER TABLE foo.t2 IMPORT TABLESPACE;         |
+-----------------------------------------------+
1 row in set (0.00 sec)


While @ohlin's answer has a decent workaround, it still requires setting up an entire MySQL instance with the undo logs configured the way you want. Then, you have to failover the application to the new MySQL Instance. That still introduces a few minutes of logical downtime from the perspective of the application because of changing the IP addresses in the code or changing DNS.
Your Original Question

Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?

The answer is definitely NO. Why ? Given the following diagram

Look at the following options for the location of the undo logs
OPTION #1 : Undo Logs Inside System Tablespace
By default, the undo logs reside in the system tablespace file known as ibdata1. Undo logs has been the cause of the greatest amount of uncontrolled growth in the face of many transactions (See my post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?)
To relocate ibdata1, you must configure innodb_data_home_dir and innodb_data_file_path. They are global variables, but they are not dynamic. Since a mysql restart would be required to change these settings, the undo logs buried inside ibdata1 cannot be moved for a live MySQL instance.
OPTION #2 : Undo Logs Outside System Tablespace
There are options to configure the undo logs on different disks:

innodb_undo_logs  : Although you can increase or decrease how many rollback segments are used within a transaction, the number of rollback segments physically present in the system never decreases. Thus you might start with a low value for this parameter and gradually increase it, to avoid allocating rollback segments that are not needed later (MySQL Documentation)
innodb_undo_tablespaces
innodb_undo_directory

Both innodb_undo_tablespaces and innodb_undo_directory are global variables, but they are not dynamic. Since a mysql restart would be required to change these settings, the undo logs outside ibdata1 cannot be moved for a live MySQL instance.
EPILOGUE
When I read this question, it reminded me of a blog from a FaceBook DB Engineer that configured InnoDB on different disks.
I referred to his blog in my post How do I determine how much data is being written per day through insert, update and delete operations?
Even with this idea, it requires configuring the undo logs in a different disk location from the very beginning. Doing this after mysql is running is simply not possible.


Query to remove duplicate calculated values

Converting my comment as answer (SQLFiddle):

;with cte (cityID, label, rowNum, stateAbbrev, stateName)
as (
    SELECT
    TOP 100 City.id AS cityID,
    City.name + ', ' + [State].[State]  as label,
    row_number() over (partition by cast(City.name + ', ' + [State].[State] as varchar(max)) order by City.id desc) AS rowNum, -- if you want the highest cityID then use "desc" else remove the "desc"
    [State].[State] AS stateAbbrev,
    [State].StateFullName AS stateName
  FROM [City]
  LEFT JOIN [Country] ON(City.countyID = Country.id)
  LEFT JOIN [State] ON(Country.stateID = [State].id)
  WHERE City.name + ', ' + [State].[State] LIKE 'Por%'
  order by City.id)  -- I have put in order by
select cityID, label, stateAbbrev, stateName
        from cte
        where rowNum = 1

The above will result :

 (click here to enlarge)

Note: As Aaron commented, you should remove CityID if not required in output list.


Just hearing the question makes me think of two aspects:

ASPECT #1 : Functions are supposed to be DETERMINISTIC

If this so, this implies that a function should present the same return data consistently for a given set of parameters, NO MATTER WHEN YOU CALL THE FUNCTION.

Now, imagine a function that produces a different answer because of gathering data at different times of the day based on static SQL in the function. In a sense, that can still be considered DETERMINISTIC if you query the same set of tables and columns every time, given the same set of parameters.

What if you could change the underlying tables of a function via Dynamic SQL? You be violating the definition of a DETERMINISTIC function.

Notice that MySQL added this option in /etc/my.cnf

log-bin-trust-function-creators

Although this may be an oversimplification to say, this allows functions to be allowed to write data into binary logs without strictly enforcing the DETERMINISTIC property.

ASPECT #2 : Triggers should be able to be rolled back

Could you imagine a trigger with all the same behaviors as a function and then introducing Dynamic SQL into the mix?
Could you imagine trying to apply MVCC (Multiversion Concurrecy Control) against Dynamic SQL after applying MVCC to the base table the trigger was meant for?

You would essentially have data that grows quadratically (even exponentially) just in MVCC alone. The process of managing the rollback of SQL with triggers that can be non-DETERMINISTIC would be ungodly complex, to say the least.

In light of these two aspects, I'm sure MySQL Developers thought of these things and quickly dismissed them by imposing restrictions.

So, why lift the restriction for Procedures? Simply put, there is no concern over DETERMINISTIC properties or Rollback.


Functional dependencies and candidate key example

Ok so I can say on a) there are two solutions. If F-Name is Unique the candidate key is F-Name otherwise F-Name, Team-Name

b) The dependencies are:

F-Name -> F-Nation, Team-Name
Team-Name -> Team-Nation

and my solutions on c) and d) are right?


30min for 1TB is quite normal. That all writing stops is also normal if your tablespace completely run out of space. When writes has nowhere to go they have to wait for the RESIZE operation to complete. If one extends datafile while there is still space in it database I/O does not stop.

Just why you extended for such huge amount? Now those +1TB will add to your RMAN backup. Of course they will compress well but still not 100% and RMAN will need time to read all those blocks.

I would setup autoextend on those tablespaces:

ALTER DATABASE DATAFILE '+DG/sid/datafile/datafile_01.dbf' AUTOEXTEND ON NEXT 10G MAXSIZE 30G

Then you would not get wasted space in tablespace and also even in case your data grows fast and catches up with autoextend operation - there will be only small delay in writes because 10GB will not take a long time to format.

And Oracle will autoextend tablespace proactively. It will not wait for the last blocks to fill up.


Best way to capture historical changes between levels in a dimensional hierarchy

If you want to capture historical changes on the three tables you need change hierarchies tables (i.e. [Area], [Region]) into sub-dimensions of the dimension table (i.e. [Location]) by adding an effective_date and expire_date (in the three tables )and you can track changes in dimension and sub-dimension attributes in order to report historical data using Slowly Changing Dimensions (SCD) techniques.


I don't know what resources you're getting this from. Not just that PgAdmin page given some of what you're saying. The information you're relying on is either outdated or incomplete; all this is pretty much unnecessary.

Make sure that autovacuum is keeping up with the database workload and you're pretty much done. These days you should not generally need to run a manual vacuum or analyze, though it's handy after bulk loads or deletes. Manually reindexing is certainly not required as a routine operation.

See autovacuum in the docs.


On long-running queries, which exact time is current_timestamp?

It does move over the duration of the query. If I toss in some

    print current_timestamp;

they change.

I'm going to work around it like this:

    declare @time datetime
    set @time = curent_timestamp