Can we use old primary to sync with standby after failover in PostgreSQL?

In current version (Pg 9.0-9.3) there is no fast "fail-back" mechanism. You will have to follow official guidelines which state:

  The former standby is now the primary, but the former primary is down and might stay down. To return to normal operation, a standby server must be recreated, either on the former primary system when it comes up, or on a third, possibly new, system.

So your second guess is correct: you need to go for complete rebuild of standby.

This arises from PostgreSQL internals, WAL format and timelines. Every time fail-over is done, it creates a new timeline. You can not safely use WAL files from new server (the one that was promoted) to replay on old server after it's revived.

From version 9.1 on, you have a tool for fast cloning of postgreSQL instances: pg_basebackup. You can also use filesystem snapshots (eg. LVM2 + XFS).

If you have a snapshot of primary postgres cluster from before the crash, and a series of WAL files from that time on, you can revive primary cluster from that snapshot and replay its WAL files. This is covered in the docs.

PS. Thanks for a good question - this is not so clear right out of the docs.


SQL Server max memory, page file, max degree parallelism

Your 3 questions touch max memory, page file and max dop setting.

MAX MEMORY

If the server is dedicated SQL Server only without Analysis services (since  SSAS is another beast), then Glenn's recommended best practices is a good starting point. For additional components, you should refer to Jonathan's blog on how to configure max memory.

I have answered on SQL Server Maximum and Minimum memory configuration. This will be helpful if you have lots of SSIS packages running on your server instance.

Page File

I normally set it away from C:\ drive on the drive which has sufficient disk space. I agree with Remus and Brent's recommendation. It should be PAGE FILE FOR SQL SERVER = 1.5 * RAM. A page file will be useful when you encounter yourself in a situation wherein you have to take a FULL MEMORY DUMP (normally asked by Microsoft CSS) for troubleshooting.

Read How to determine the appropriate page file size for 64-bit versions of Windows KB 889654 thoroughly and Buck Woody talks about page file here.

MAX DOP Setting

For sharepoint its recommended that maxdop = 1 be set instance wide. For a normal sql server, I would suggest you to follow  - What is a good, repeatable way to calculate MAXDOP on SQL Server? I have a script written which will calculate it for you.

The gist is below (KB 2806535 also mentions the same thing):

8 or less processors    ===> 0 to N (where N= no. of processors)
More than 8 processors  ===> 8
NUMA configured         ===> MAXDOP should not exceed no of CPU’s assigned to each
                                 NUMA node with max value capped to 8
Hyper threading Enabled ===> Should not exceed the number of physical processors.

As a side note, I would suggest you to read  - Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads


The "implicit" group you mention seems to be based on the row order.  Unlike a spreadsheet or text file, a relational table is logically an unordered set of rows regardless of whether it is stored as a heap or has a clustered index. It will not be possible to write a query to provide the desired results unless you have another column to facilitate the grouping.

  I was hoping I could create a deterministic row ordering with a query similar to the following:

SELECT      *
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM Table WITH(TABLOCK)
OPTION (MAXDOP 1)

Sorry to be the bearer of bad news, but ordering by a literal will not guarantee deterministic ordering (and even if it did, you'd need an ORDER BY clause there). If it appears to do so, it is only by happenstance. Any incremental column will work, though.

  I am hoping this forces an allocation order scan which would then give me a deterministic row order.

An allocation-ordered scan is no more deterministic than any other implementation; you're just (unsafely) relying on a different observed behaviour.


Cardinality Estimation for >= and > for intra step statistics value

The only difficulty is in deciding how to handle the histogram step(s) partially covered by the query predicate interval. Whole histogram steps covered by the predicate range are trivial as noted in the question.
Legacy Cardinality Estimator
F = fraction (between 0 and 1) of the step range covered by the query predicate.
The basic idea is to use F (linear interpolation) to determine how many of the intra-step distinct values are covered by the predicate. Multiplying this result by the average number of rows per distinct value (assuming uniformity), and adding the step equal rows gives the cardinality estimate:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * F * DISTINCT_RANGE_ROWS)

The same formula is used for > and >= in the legacy CE.
New Cardinality Estimator
The new CE modifies the previous algorithm slightly to differentiate between > and >=.
Taking > first, the formula is:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * (F * (DISTINCT_RANGE_ROWS - 1)))

For >= it is:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * ((F * (DISTINCT_RANGE_ROWS - 1)) + 1))

The + 1 reflects that when the comparison involves equality, a match is assumed (the inclusion assumption).
In the question example, F can be calculated as:
DECLARE
    @Q datetime = '1999-10-13T10:48:38.550',
    @K1 datetime = '1999-10-13T10:47:38.550',
    @K2 datetime = '1999-10-13T10:51:19.317';

DECLARE
    @QR float = DATEDIFF(MILLISECOND, @Q, @K2), -- predicate range
    @SR float = DATEDIFF(MILLISECOND, @K1, @K2) -- whole step range

SELECT
    F = @QR / @SR;

The result is 0.728219019233034. Plugging that into the formula for >= with the other known values:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * ((F * (DISTINCT_RANGE_ROWS - 1)) + 1))
            = 16 + (16.1956 * ((0.728219019233034 * (409 - 1)) + 1))
            = 16 + (16.1956 * ((0.728219019233034 * 408) + 1))
            = 16 + (16.1956 * (297.113359847077872 + 1))
            = 16 + (16.1956 * 298.113359847077872)
            = 16 + 4828.1247307393343837632
            = 4844.1247307393343837632
            = 4844.12473073933 (to float precision)

This result agrees with the estimate of 4844.13 shown in the question.
The same query using the legacy CE (e.g. using trace flag 9481) should produce an estimate of:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * F * DISTINCT_RANGE_ROWS)
            = 16 + (16.1956 * 0.728219019233034 * 409)
            = 16 + 4823.72307468722
            = 4839.72307468722

Note the estimate would be the same for > and >= using the legacy CE.


There is a great discussion of this topic here at ServerFault in which the 3 forms of fragmentation are discussed.  I always have to remind myself to search for the answer before I post a question as it saves me so much time.

Short answer (tl/dr):

It will remove physical fragmentation but not logical or VLF fragmentation within the log file.

Physical Fragmentation:

Backing up and restoring will remove any physical fragmentation.  That is, fragmentation that occurred on the NTFS partition as a result of your database growing incrementally over time and contiguous space not being available as a result.

Other Fragmentation:

You will still have index fragmentation and VLF Fragmentation within the database.  Paul Randall stressed not using Windows Defrag at his SQLSkills Immersion Events, and how it could lead to database corruption so I think you're on the right track.

Background:

Physical fragmentation will operate on the disk where the MDF/NDF/LDF files occupy.  Since SQL Server does not track this information as it would be useless.  We want to be able to restore databases to any hard drive as long as it has space, we're not concerned with where it gets located on the disk so much unless you're short writing...but that's a exception to the rule.


-- get all the unique team ids which have both user1 and user2
SELECT DISTINCT a.teamid
FROM
( SELECT teamid FROM `TEAMUSER` WHERE userid = user1 ) AS a
INNER JOIN
( SELECT teamid FROM `TEAMUSER` WHERE userid = user1 ) AS b
ON a.team_id = b.team_id


Return more columns than were inserted inside a PostgreSQL CTE

According Postgres Docs about 6.4. Returning Data From Modified Rows you can't.

Quoted from docs:

  In an INSERT, the data available to RETURNING is the row as it was inserted. This is not so useful in trivial inserts, since it would just repeat the data provided by the client. But it can be very handy when relying on computed default values. For example, when using a serial column to provide unique identifiers, RETURNING can return the ID assigned to a new row.

Unless this data is updated by a trigger:

  If there are triggers (Chapter 39) on the target table, the data available to RETURNING is the row as modified by the triggers. Thus, inspecting columns computed by triggers is another common use-case for RETURNING.

You can check it by using RETURNING *

CREATE TABLE t1 (id int, foo int);
CREATE TABLE t2 (id int, bar int);

INSERT INTO t2 VALUES (1, 2);

INSERT INTO t1 (id, foo)
SELECT id, bar
FROM   t2
WHERE  id = 1
RETURNING *;

id | foo
-: | --:
 1 |   2

db<>fiddle here

You could get it by using a nested CTE:

WITH x AS
(
    SELECT * FROM t2 WHERE id = 1
), y AS
 (
    INSERT INTO t1 (id, foo)
    SELECT id, bar
    FROM   x
    RETURNING *
 )
 SELECT x.*, y.*
 FROM x, y;

id | bar | id | foo
-: | --: | -: | --:
 1 |   2 |  1 |   2

db<>fiddle here


This may be somewhat surprising, but this error is proof that your replication filter is working.  The statement that has stopped working is an UPDATE to another table, but the update is using data from the table that you have said not to replicate in the SELECT clause.  Notice that the error is that the table norep_cp_banner_tvc_temp_ doesn't exist.  Replication filters only are honored on the tables being affected - it's not just a straight pattern match.

Replication filters are somewhat complicated - I suggest that you read up on How Servers Evaluate Replication Filter Rules and Evaluation of Table-Level Replication Options.

The first question to ask yourself is why am I filtering replication at all?  There is rarely a good reason for this and it usually causes far more problems that it solves.

You can  definitely work around the problem by using Row Based Replication (RBR) instead of Statement Based Replication (SBR).  RBR replicates the changes to the data instead of the statement.

I also suggest that you refactor your UPDATE as a join.  IN (SELECT...) is very optimized poorly in MySQL pre-5.6 and this will be much more performant:

UPDATE `ox_banners` A
JOIN norep_cp_banner_tvc_temp_ temp USING (bannerid)
SET A.`status` = 1;

Another way that you might be able to work around this problem is by updating a dummy field in the norep table.  Something like this:

UPDATE ox_banners A
JOIN norep_cp_banner_tvc_temp_ temp USING (bannerid)
SET A.`status` = 1, temp.dummy = 1;

That might trick MySQL into filtering this statement, but it's a hack to say the least.  RBR is likely your best bet.

UPDATE 2012-07-24

You mentioned that you are doing this to work around some of the problems with temporary tables being used with replication.  Filtering them out entirely is a terrible way to handle that problem.  Consider doing this instead, if you are worried about it.

Create a separate database to hold your "temporary" tables

mysql> create database temptables;
Query OK, 1 row affected (0.08 sec)

mysql> use temptables;
Database changed

Namespace the tables by using your CONNECTION_ID...

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              52 |
+-----------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE 52_my_temp ( foo int ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.20 sec)

Use the table as normal, then
DROP the table

mysql> DROP TABLE IF EXISTS 52_my_temp;
Query OK, 0 rows affected (0.05 sec)

Finally, create a process that cleans the tables up every so often, just incase the DROP TABLE didn't get executed.  Something like this (Ruby Sequel syntax here):

# get a list of all the tables in the temp database
query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TABLE_SCHEMA=?"
db[query, 'temptables'].each do |row|
  # get the connection id
  id = row[:TABLE_NAME].split('_')[0].to_i
  # is the connection still active?
  query = "SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = ?"
  if db[query, id].first[:cnt] < 1
    db["DROP TABLE #{row[:TABLE_NAME]}"]
  end
end


Multiple Instance with different service packs in SQL Server

Yes, you can choose instance to apply service pack.
Instance components along with shared components between side by side instances will be upgraded to higher service pack.

Sql server has two types of component\features.
1. Instance specific features
2. Shared Features

When you upgrade one component with a given instance name to a new version or service pack, you must upgrade all components of that instance.


What happens when a CTE query is interrupted

A CTE isn't any different to any other statement in this regard - it runs within a transaction. If no transaction is already open then one is opened for the statement and committed only at the successful completion of the whole statement.

Thus, if you interrupt the CTE, all effects will roll back and nothing will happen1. If you interrupt it just at the moment it is committing then it might commit and all take effect, but either all or none of the CTE will take effect. Individual CTE sub-terms cannot take effect by themselves.

So ... the delete might run, but if so, its effects will not be committed and will be rolled back when the query is cancelled. Exactly how it's executed does not matter. PostgreSQL could choose to batch it, execute it row-by-row, or execute the whole DELETE and feed all its results into the INSERT. It doesn't matter, because the effects will be the same.

1 Well, strictly a whole lot of table bloat could be created, but there are no effects logically visible to other transactions.


Does the length of the index name have any performance impact?

In short: No.

There will be a very very small difference in parsing time for statements that specifically mention the index or generating output that mention the index, but this is so vanishingly small compared to all the other work that teh database engine it doing that it is simply noise - far too small to even reliably measure.

When the index name is not explicitly used then even that difference is gone: the index will be looked up by the columns it contains data for not by its name. If the engine needs to refer to he index directly it will do so by some internal ID - the name you know is essentially just for us humans.

Baring that in mind, especially the second point, long descriptive names are recommended. There is a caveat there though: if you make the name too long then it becomes unwieldy, and many tools will impose arbitrary limits on the length of identifiers. SQL Server for instance limits all names to 128 characters (if you get close to this limit you have probably gone too far!) but I've seen some tools (that were bad in other ways too...) fall over when they hit anything longer than 50. The mySQL documentation should tell you what the internal name length limits are for your circumstance.


Since only the estimated plan is present, these are going to be some guesses.

This part

WHERE j.Status >= 60 AND (j.Status <= 70 OR (dsp.BatchNumber > 1 AND x.CabinetCount IS NULL))

Creates a filter far in the execution plan:

on this part:

 (j.Status <= 70 OR (dsp.BatchNumber > 1 AND x.CabinetCount IS NULL))

You could try adding a union to split them up, but due to the amount of joins I would try using a temp table to split some logic in two pieces.

I don't know your datatypes so I am going to use a SELECT ... INTO. Consider changing this to a INSERT ... SELECT .

Temp table insert:

  SELECT  j.JobId,
        j.OrderId,
        j.OrderType,
        j.ShopName,
        j.ShipDate,
        j.CompletionDate,
        j.InstallDate,
        j.DayPriority,
        j.ShipDateConfirmed,
        j.IsAddon,
        j.CashOnDelivery,
        j.FinalMeasure,
        ds.ShopLabel,
        ds.DesignSetId,
        --dsp.DesignSetProcessId,
        j.PaintBottleneck,
        j.DoorBottleNeck,
        j.AssemblyBottleNeck,
        --Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --      dsp.DatetimeStarted,
        --dsp.DatetimeComplete,
        --dsp.BatchNumber,
        --p.Name AS ProcessName,
        --p.ProcessId,
        --p.ZoneId,
        --X.CabinetCount,
        j.InstallScheduled,
        j.RedTicket,
        j.InstallDateRange,
        --V.VitalItems,
        --NV.NonVitalItems,
        --Pj.ParentJobInstalled,
        Schedule.WorkdaysByDate(j.ShipDate, j.InstallDate) AS ShipInstallDiff,
        dss.CatalogId AS SpeciesId,
        dsld.CatalogId AS LowerDoorId,
        dsupd.CatalogId AS UpperDoorId,
        dsf.CatalogId AS FrontId,
        dsfs.CatalogId AS FinishId,
        dsg.CatalogId AS GlazeId,
        dsfo.CatalogId AS FinishOptionId,
        dsds.CatalogId AS DistressId,
        dssh.CatalogId AS SheenId,
        dsi.CatalogId AS InteriorId,
        dsh.CatalogId AS HingeId,
        dsd.CatalogId AS DrawerId,
        dssl.CatalogId AS SlideId,
        j.ParentJobId --added for outer apply
INTO #temp
FROM Jobs j
JOIN DesignSets ds ON j.JobId = ds.JobId
JOIN Species dss ON ds.DesignSetId = dss.DesignSetId
JOIN LowerDoors dsld ON ds.DesignSetId = dsld.DesignSetId
JOIN UpperDoors dsupd ON ds.DesignSetId = dsupd.DesignSetId
JOIN Fronts dsf ON ds.DesignSetId = dsf.DesignSetId
JOIN Finishes dsfs ON ds.DesignSetId = dsfs.DesignSetId
JOIN Glazes dsg ON ds.DesignSetId = dsg.DesignSetId
JOIN FinishOptions dsfo ON ds.DesignSetId = dsfo.DesignSetId
JOIN Distresses dsds ON ds.DesignSetId = dsds.DesignSetId
JOIN Sheens dssh ON ds.DesignSetId = dssh.DesignSetId
JOIN Interiors dsi ON ds.DesignSetId = dsi.DesignSetId
JOIN Hinges dsh ON ds.DesignSetId = dsh.DesignSetId
JOIN Drawers dsd ON ds.DesignSetId = dsd.DesignSetId
JOIN Slides dssl ON ds.DesignSetId = dssl.DesignSetId
WHERE j.Status >= 60;

You could then change the query to this

SELECT  temp.JobId,
        temp.OrderId,
        temp.OrderType,
        temp.ShopName,
        temp.ShipDate,
        temp.CompletionDate,
        temp.InstallDate,
        temp.DayPriority,
        temp.ShipDateConfirmed,
        temp.IsAddon,
        temp.CashOnDelivery,
        temp.FinalMeasure,
        temp.ShopLabel,
        temp.DesignSetId,
        dsp.DesignSetProcessId,
        temp.PaintBottleneck,
        temp.DoorBottleNeck,
        temp.AssemblyBottleNeck,
        Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --    dsp.DatetimeStarted,
        dsp.DatetimeComplete,
        dsp.BatchNumber,
        p.Name AS ProcessName,
        p.ProcessId,
        p.ZoneId,
        X.CabinetCount,
        temp.InstallScheduled,
        temp.RedTicket,
        temp.InstallDateRange,
        V.VitalItems,
        NV.NonVitalItems,
        Pj.ParentJobInstalled,
        temp.ShipInstallDiff,
        temp.CatalogId AS SpeciesId,
        temp.CatalogId AS LowerDoorId,
        temp.CatalogId AS UpperDoorId,
        temp.CatalogId AS FrontId,
        temp.CatalogId AS FinishId,
        temp.CatalogId AS GlazeId,
        temp.CatalogId AS FinishOptionId,
        temp.CatalogId AS DistressId,
        temp.CatalogId AS SheenId,
        temp.CatalogId AS InteriorId,
        temp.CatalogId AS HingeId,
        temp.CatalogId AS DrawerId,
        temp.CatalogId AS SlideId
FROM #temp temp
LEFT OUTER JOIN Schedule.DesignSetProcesses dsp ON temp.DesignSetId = dsp.DesignSetId
LEFT OUTER JOIN Schedule.Processes p ON dsp.ProcessId = p.ProcessId
OUTER APPLY(Select SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount From Products pr Where temp.DesignSetId = pr.DesignSetId AND
                                            (dsp.BatchNumber = pr.BatchNumber OR dsp.DesignSetId IS NULL)) AS X
OUTER APPLY(SELECT COUNT(pur.Vital) AS 'VitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 1
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS V
OUTER APPLY(SELECT COUNT(pur.ItemId) AS 'NonVitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 0
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS NV
OUTER APPLY(SELECT Schedule.WorkdaysByDate(job.InstallDate, Schedule.GetUtahDate()) AS 'ParentJobInstalled'
                FROM Jobs job
                WHERE job.JobId = temp.ParentJobId) AS Pj
WHERE  (j.Status <= 70 OR (dsp.BatchNumber > 1 AND x.CabinetCount IS NULL))

Apart from this your functions are also going to hurt, depending on how much data is returned.

You could add this index:

CREATE INDEX IX_Calendar_DateId_filtered
ON Schedule.Calendar(DateId)
INCLUDE(IsWorkDay)
WHERE IsWorkDay = 1;

The rest of the indexing is hard to say with an estimated plan, there are index posbilities such as:

CREATE INDEX IX_Jobs_Status_JobID
ON Jobs(Status,JobId)
INCLUDE(
        OrderId,
        OrderType,
        ShopName,
        ShipDate,
        CompletionDate,
        InstallDate,
        DayPriority,
        ShipDateConfirmed,
        IsAddon,
        CashOnDelivery,
        FinalMeasure,
        PaintBottleneck,
        DoorBottleNeck,
        AssemblyBottleNeck,
        InstallScheduled,
        RedTicket,
        InstallDateRange,
        ParentJobId
        );

CREATE INDEX IX_DesignSets
ON DesignSets(JobId,DesignSetId)
INCLUDE(ShopLabel);

But I think that getting the query to apply predicates earlier and/or splitting up the work will have bigger results at the moment.

EDIT

You could try changing the scalar functions to APPLY's. Since you have many apply's already you could add the first function as two apply's on the temp table insert:

SELECT  j.JobId,
        j.OrderId,
        j.OrderType,
        j.ShopName,
        j.ShipDate,
        j.CompletionDate,
        j.InstallDate,
        j.DayPriority,
        j.ShipDateConfirmed,
        j.IsAddon,
        j.CashOnDelivery,
        j.FinalMeasure,
        ds.ShopLabel,
        ds.DesignSetId,
        --dsp.DesignSetProcessId,
        j.PaintBottleneck,
        j.DoorBottleNeck,
        j.AssemblyBottleNeck,
        --Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --      dsp.DatetimeStarted,
        --dsp.DatetimeComplete,
        --dsp.BatchNumber,
        --p.Name AS ProcessName,
        --p.ProcessId,
        --p.ZoneId,
        --X.CabinetCount,
        j.InstallScheduled,
        j.RedTicket,
        j.InstallDateRange,
        --V.VitalItems,
        --NV.NonVitalItems,
        --Pj.ParentJobInstalled,
        ISNULL([WorkdaysByDate1],[WorkdaysByDate2])
        dss.CatalogId AS SpeciesId,
        dsld.CatalogId AS LowerDoorId,
        dsupd.CatalogId AS UpperDoorId,
        dsf.CatalogId AS FrontId,
        dsfs.CatalogId AS FinishId,
        dsg.CatalogId AS GlazeId,
        dsfo.CatalogId AS FinishOptionId,
        dsds.CatalogId AS DistressId,
        dssh.CatalogId AS SheenId,
        dsi.CatalogId AS InteriorId,
        dsh.CatalogId AS HingeId,
        dsd.CatalogId AS DrawerId,
        dssl.CatalogId AS SlideId,
        j.ParentJobId --added for outer apply
INTO #temp
FROM Jobs j
JOIN DesignSets ds ON j.JobId = ds.JobId
JOIN Species dss ON ds.DesignSetId = dss.DesignSetId
JOIN LowerDoors dsld ON ds.DesignSetId = dsld.DesignSetId
JOIN UpperDoors dsupd ON ds.DesignSetId = dsupd.DesignSetId
JOIN Fronts dsf ON ds.DesignSetId = dsf.DesignSetId
JOIN Finishes dsfs ON ds.DesignSetId = dsfs.DesignSetId
JOIN Glazes dsg ON ds.DesignSetId = dsg.DesignSetId
JOIN FinishOptions dsfo ON ds.DesignSetId = dsfo.DesignSetId
JOIN Distresses dsds ON ds.DesignSetId = dsds.DesignSetId
JOIN Sheens dssh ON ds.DesignSetId = dssh.DesignSetId
JOIN Interiors dsi ON ds.DesignSetId = dsi.DesignSetId
JOIN Hinges dsh ON ds.DesignSetId = dsh.DesignSetId
JOIN Drawers dsd ON ds.DesignSetId = dsd.DesignSetId
JOIN Slides dssl ON ds.DesignSetId = dssl.DesignSetId
OUTER APPLY (SELECT SUM(IsWorkDay) AS Workdays
                    From Schedule.Calendar
                    Where DateId >= j.ShipDate AND DateId <= j.InstallDate) as [WorkdaysByDate1]
OUTER APPLY (Select SUM(IsWorkDay) AS Workdays
                            From Schedule.Calendar
                            Where DateId <= j.ShipDate AND DateId >= j.InstallDate) as [WorkdaysByDate2]
WHERE j.Status >= 60;

EDIT 2

  So, when I remove the scalar functions, on my SELECT INTO query my
  temp table execution time is CPU time = 281 ms, elapsed time = 293 ms.
  and my query execution time is CPU time = 1969 ms, elapsed time = 3694
  ms. On my INSERT INTO query my temp table execution time is CPU time
  282 ms, elapsed time = 442 ms. and my query execution time is CPU time
  1984 ms, elapsed time = 3883 ms.

Most of the execution time is on the second part, what happens when you write the second part of the query like this?

SELECT  temp.JobId,
        temp.OrderId,
        temp.OrderType,
        temp.ShopName,
        temp.ShipDate,
        temp.CompletionDate,
        temp.InstallDate,
        temp.DayPriority,
        temp.ShipDateConfirmed,
        temp.IsAddon,
        temp.CashOnDelivery,
        temp.FinalMeasure,
        temp.ShopLabel,
        temp.DesignSetId,
        dsp.DesignSetProcessId,
        temp.PaintBottleneck,
        temp.DoorBottleNeck,
        temp.AssemblyBottleNeck,
        Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --    dsp.DatetimeStarted,
        dsp.DatetimeComplete,
        dsp.BatchNumber,
        p.Name AS ProcessName,
        p.ProcessId,
        p.ZoneId,
        X.CabinetCount,
        temp.InstallScheduled,
        temp.RedTicket,
        temp.InstallDateRange,
        V.VitalItems,
        NV.NonVitalItems,
        Pj.ParentJobInstalled,
        temp.ShipInstallDiff,
        temp.CatalogId AS SpeciesId,
        temp.CatalogId AS LowerDoorId,
        temp.CatalogId AS UpperDoorId,
        temp.CatalogId AS FrontId,
        temp.CatalogId AS FinishId,
        temp.CatalogId AS GlazeId,
        temp.CatalogId AS FinishOptionId,
        temp.CatalogId AS DistressId,
        temp.CatalogId AS SheenId,
        temp.CatalogId AS InteriorId,
        temp.CatalogId AS HingeId,
        temp.CatalogId AS DrawerId,
        temp.CatalogId AS SlideId
FROM #temp temp
LEFT OUTER JOIN Schedule.DesignSetProcesses dsp ON temp.DesignSetId = dsp.DesignSetId
LEFT OUTER JOIN Schedule.Processes p ON dsp.ProcessId = p.ProcessId
OUTER APPLY(Select SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount From Products pr Where temp.DesignSetId = pr.DesignSetId AND
                                            (dsp.BatchNumber = pr.BatchNumber OR dsp.DesignSetId IS NULL)) AS X
OUTER APPLY(SELECT COUNT(pur.Vital) AS 'VitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 1
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS V
OUTER APPLY(SELECT COUNT(pur.ItemId) AS 'NonVitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 0
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS NV
OUTER APPLY(SELECT Schedule.WorkdaysByDate(job.InstallDate, Schedule.GetUtahDate()) AS 'ParentJobInstalled'
                FROM Jobs job
                WHERE job.JobId = temp.ParentJobId) AS Pj
WHERE (dsp.BatchNumber > 1 AND x.CabinetCount IS NULL))
UNION
SELECT  temp.JobId,
        temp.OrderId,
        temp.OrderType,
        temp.ShopName,
        temp.ShipDate,
        temp.CompletionDate,
        temp.InstallDate,
        temp.DayPriority,
        temp.ShipDateConfirmed,
        temp.IsAddon,
        temp.CashOnDelivery,
        temp.FinalMeasure,
        temp.ShopLabel,
        temp.DesignSetId,
        dsp.DesignSetProcessId,
        temp.PaintBottleneck,
        temp.DoorBottleNeck,
        temp.AssemblyBottleNeck,
        Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --    dsp.DatetimeStarted,
        dsp.DatetimeComplete,
        dsp.BatchNumber,
        p.Name AS ProcessName,
        p.ProcessId,
        p.ZoneId,
        X.CabinetCount,
        temp.InstallScheduled,
        temp.RedTicket,
        temp.InstallDateRange,
        V.VitalItems,
        NV.NonVitalItems,
        Pj.ParentJobInstalled,
        temp.ShipInstallDiff,
        temp.CatalogId AS SpeciesId,
        temp.CatalogId AS LowerDoorId,
        temp.CatalogId AS UpperDoorId,
        temp.CatalogId AS FrontId,
        temp.CatalogId AS FinishId,
        temp.CatalogId AS GlazeId,
        temp.CatalogId AS FinishOptionId,
        temp.CatalogId AS DistressId,
        temp.CatalogId AS SheenId,
        temp.CatalogId AS InteriorId,
        temp.CatalogId AS HingeId,
        temp.CatalogId AS DrawerId,
        temp.CatalogId AS SlideId
FROM #temp temp
LEFT OUTER JOIN Schedule.DesignSetProcesses dsp ON temp.DesignSetId = dsp.DesignSetId
LEFT OUTER JOIN Schedule.Processes p ON dsp.ProcessId = p.ProcessId
OUTER APPLY(Select SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount From Products pr Where temp.DesignSetId = pr.DesignSetId AND
                                            (dsp.BatchNumber = pr.BatchNumber OR dsp.DesignSetId IS NULL)) AS X
OUTER APPLY(SELECT COUNT(pur.Vital) AS 'VitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 1
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS V
OUTER APPLY(SELECT COUNT(pur.ItemId) AS 'NonVitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 0
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS NV
OUTER APPLY(SELECT Schedule.WorkdaysByDate(job.InstallDate, Schedule.GetUtahDate()) AS 'ParentJobInstalled'
                FROM Jobs job
                WHERE job.JobId = temp.ParentJobId) AS Pj

WHERE  (j.Status <= 70)


Error passing variable table type to a stored procedure

Sorry, I haven't noticed about something:

I use a script to erase all the database, to start all from zero, this script is useful to test my stored procedures, so in the stored procdure
SP_NUEVO_BD (in the code below), I drop the stored procedure SP_NUEVO_VAR, I drop the type TVariable, then I create the type, then I create the stored procedure.

Something is happening that I cannot run the full script, but when I run it individually it works:

Here is the script below:

USE foxtrot
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
-- Nueva Base de datos
SET NOCOUNT ON
EXEC SP_NUEVO_DB

-- Script de ejemplo, variables de ejemplo

DECLARE @no_serie as varchar (20),
        @folio_terminal as int,
        @err_cod as int,
        @folio_solicitud as int
        SET @no_serie='282-716-200'

-- Tabla de ejemplos de nuevas variables

-- Nuevo Registro de terminal ùnica

EXEC SP_NUEVO_TERMINAL   @no_serie, @folio_terminal OUT, @err_cod OUT

PRINT 'No de serie:['   +@no_serie+']' PRINT 'Folio terminal:['+CAST(@folio_terminal as varchar(20))+ ']' PRINT '@err_cod:['+CAST(@err_cod as varchar(20))+']'

-- Nuevo Registro de terminal, con sistema operativo, modelo

EXEC SP_NUEVO_REGISTRO_TERMINAL 'T00001','282-716-200','QTG5001','EOS123','VX610', @err_cod OUTPUT

PRINT 'err_cod:['+CAST(@err_cod as varchar(20))+']'

-- Script de nuevas variables

DECLARE @T TVARIABLE
INSERT INTO  @T   VALUES ( '#var1','1')
INSERT INTO  @T   VALUES ( '#var2','2')
INSERT INTO  @T   VALUES ( '#var3','3')
INSERT INTO  @T   VALUES ( '#var4','4')
INSERT INTO  @T   VALUES ( '#var5','5')
INSERT INTO  @T   VALUES ( '#var6','6')
INSERT INTO  @T   VALUES ( '#var7','7')
--SELECT * FROM @T
EXEC SP_NUEVO_VAR   @T_Variable=@T

Yu can see that the implmentation I refer in the first post is at the end of the script, when I executed indivudually it works fine, but when I run the complete script it doesn´t, considering I make all the process of deleting the stored procedure and the type, then I recreate it.


mysql_query() is NOT passing the command to the MySQL server using the C API

User Gerard H Pille gave me the correct tip - see the chat.

I had to remove the MYSQL_OPT_RECONNECT setting and now it works. The program runs now for more than 2 months without any issue1.

It were these 2 lines of code according to the MCVE:

bool autoReconnectMySQL = true;
mysql_options( mysqlConnection, MYSQL_OPT_RECONNECT, &autoReconnectMySQL );

Why the setting is causing this issue I cannot answer.

New test results:
MySQL C API 8.0.11 doesn't have this issue anymore.

1 Now, it runs for more than 1 year without any problem.


Are indexes bloated if using nvarchar field sizes that are much bigger than needed?

Aaron Bertrand's comments copied as an answer:

SQL Server just uses the actual number of bytes for storage, but take a look at your data size estimates - they'll be (half the declared byte size x number of rows) rather than (the average size x number of rows), and this can have a big impact on memory grants. Right-sizing is a good thing. These values can be found in the estimated data size (and any memory grant-related information) in a query plan.


Difference between Sharding And Replication on MongoDB

A Replica-Set means that you have multiple instances of MongoDB which each mirror all the data of each other. A replica-set consists of one Master (also called "Primary") and one or more Slaves (aka Secondary). Read-operations can be served by any slave, so you can increase read-performance by adding more slaves to the replica-set (provided that your client application is capable to actually use different set-members). But write-operations always take place on the master of the replica-set and are then propagated to the slaves, so writes won't get faster when you add more slaves.

Replica-sets also offer fault-tolerance. When one of the members of the replica-set goes down, the others take over. When the master goes down, the slaves will elect a new master. For that reason it is suggested for productive deployment to always use MongoDB as a replica-set of at least three servers, two of them holding data (the third one is a data-less "arbiter" which is required for determining a new master when one of the slaves goes down).

A Sharded Cluster means that each shard of the cluster (which can also be a replica-set) takes care of a part of the data. Each request, both reads and writes, is served by the cluster where the data resides. This means that both read- and write performance can be increased by adding more shards to a cluster. Which document resides on which shard is determined by the shard key of each collection. It should be chosen in a way that the data can be evenly distributed on all clusters and so that it is clear for the most common queries where the shard-key resides (example: when you frequently query by user_name, your shard-key should include the field user_name so each query can be delegated to only the one shard which has that document).

The drawback is that the fault-tolerance suffers. When one shard of the cluster goes down, any data on it is inaccessible. For that reason each member of the cluster should also be a replica-set. This is not required. When you don't care about high-availability, a shard can also be a single mongod instance without replication. But for production-use you should always use replication.

So what does that mean for your example?

                            Sharded Cluster
             /                    |                    \
      Shard A                  Shard B                  Shard C
        / \                      / \                      / \
+-------+ +---------+    +-------+ +---------+    +-------+ +---------+
|Primary| |Secondary|    |Primary| |Secondary|    |Primary| |Secondary|
|  25GB |=| 25GB    |    | 25 GB |=| 25 GB   |    | 25GB  |=| 25GB    |
+-------+ +---------+    +-------+ +---------+    +-------+ +---------+

When you want to split your data of 75GB into 3 shards of 25GB each, you need at least 6 database servers organized in three replica-sets. Each replica-set consists of two servers who have the same 25GB of data.

You also need servers for the arbiters of the three replica-sets as well as the mongos router and the config server for the cluster. The arbiters are very lightweight and are only needed when a replica-set member goes down, so they can usually share the same hardware with something else. But Mongos router and config-server should be redundant and on their own servers.


Unexpected unique constraint violation during update

You need to create a deferrable constraint rather than an index.
CREATE TABLE foo
(
  id uuid NOT NULL primary key
  seq integer NOT NULL,
  CONSTRAINT seq_key UNIQUE (seq)
    deferrable initially immediate --<< this
);

By declaring the constraint as deferrable it will be checked at the end of the UPDATE statement (to be more precise: at the end of the transaction) rather than row-by-row.


How can I get the correct offset between UTC and local times for a date that is before or after DST?

The best way to convert a non-current UTC date into local time, prior to SQL Server 2016, is to use the Microsoft .Net Common Language Runtime, or CLR.
The code itself is easy; the difficult part is usually convincing people that the CLR isn't pure evil or scary...
For one of the many examples, check out Harsh Chawla's blog post on the topic.
Unfortunately, there is nothing built-in prior to SQL Server 2016 that can handle this type of conversion, save for CLR-based solutions.  You could write a T-SQL function which does something like this, but then you'd have to implement the date-change logic yourself, and I'd call that decidedly not easy.


Without splitting you can restore specific database using this command

 mysql -u root -p --one-database database_name < main-db-dump.sql

First create the database. Then apply this command with correct database_name. Follow the steps for each database.


I solved this situation using a simple workaround (very similar to DanGuzman's comment).

Merging partitions is in-blink-of-an-eye operation if and only if both partitions are empty. When the partition function is defined as RANGE RIGHT, then during the merge the leftmost partition will eat the other on the right side. Due to this all records have to be moved between partitions filegroups. The workaround is to rebuild or simply drop and recreate the staging table - the table with exactly the same schema as the source partitioned table. The only difference is the filegroup of the nonpartitioned table and it's indices must match filegroup of the partition that is going to be merged. This reduces both of operations - partition switching and partition merge to be metadata operation only. QED.


Generically get all related records from SQL Server Database based off of Primary Key of one table

If the tables have defined foreign keys, you could query the system tables themselves, especially sys.foreign_keys (Documentation here). Here's a good query to start with, written by Stack Overflow user littlesweetseas, from this post :

SELECT
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
   sys.foreign_keys AS f
INNER JOIN
   sys.foreign_key_columns AS fc
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
   sys.tables t
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE
   OBJECT_NAME (f.referenced_object_id) = 'YourTableName'

Keep in mind that bringing down data regularly could get more difficult, as you will have to remember the order of tables, and export that data in order every time.

I would suggest finding a place to restore the production database in your test environment. The safest way to get production data into a test environment would be a database restore to your test server. You could either use the latest full backup, or take a copy-only backup of production, and restore that either to replace your test environment's database completely or  to a new database. That would give you all the data and data structures from Prod, but it could also wipe out any changes or on-going tests if you replace your test database.


Filling in missing dates in record set from generate_series()

This is some kind of misunderstanding. The query in your question already returns what you are asking for. I only changed minor details:
SELECT text 'Inspections'       AS data_label
     , count(i.close_case_date) AS daily_count
     , d.day                    AS date_column
FROM  (
   SELECT generate_series(timestamp '2013-01-01'
                        , timestamp '2013-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
LEFT   JOIN inspection i ON i.close_case_date = d.day
GROUP  BY d.day
ORDER  BY d.day;

About generating a series of dates:

Generating time series between two dates in PostgreSQL

Minor points

date '2013-01-01' + interval '1 year - 1 day' is better than date '2013-01-01' + 365 to also cover leap years.

Using a cheaper subquery. No need for a CTE.

Why count(i.reporting_id)? To just count rows in i, use count(i.close_case_date), which we already join to, so it cannot be NULL. count() only counts non-null values. If reporting_id can be NULL, you run the risk of not counting those rows.

Alternatively, move the cast, so we can use the set-returning function as table expression directly:
SELECT text 'Inspections'       AS data_label
     , count(i.close_case_date) AS daily_count
     , d.day::date              AS date_column
FROM   generate_series(timestamp '2013-01-01'
                     , timestamp '2013-01-01' + interval '1 year - 1 day'
                     , interval  '1 day') AS d(day)
LEFT   JOIN inspection i ON i.close_case_date = d.day::date
GROUP  BY d.day
ORDER  BY d.day;


dropping a subscription in the subscriber first, how to fix it?

Always drop a subscription in the publisher first to avoid this error.

But if you happen to drop the subscription in the subscriber and get the error in the question above,
the way I sorted it out was to go to the publisher server and publisher database and run the script below, that generates a drop subscription command for each article still pointing to the wrong subscription.

this is the script to generate the script to run:

-- check the server and database
-- these should be the publisher server and publisher database

SELECT @@SERVERNAME
-- CTSTGDB1

SELECT DB_NAME()
-- APIASTG_Repl_Pub

-- go to the publication database
USE APIASTG_Repl_Pub
GO

--check how things are (works for before and after)
sp_helppublication

sp_helpsubscription

--script to generate the drop of all articles in the BANDIT subscription
USE APIASTG_Repl_Pub
GO

select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
, s.dest_db
,SQL_='exec sp_dropsubscription ' + CHAR(13) +
      '     @publication=' + '''' + db_name() + '''' +  CHAR(13) +
      '    ,@article='  + '''' + SA.NAME + '''' +  CHAR(13) +
      '    ,@subscriber=' + '''' + UPPER(srv.srvname) + '''' +  CHAR(13) +
      '    ,@destination_db=' + '''' + s.dest_db + ''''
            +  CHAR(13) + CHAR(10)

from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid
where s.dest_db = 'MY_WRONG_SUBSCRIPTION'

-- get the _SQL generated above and open a new query,
-- on the same server, same database, run it
-- problem should be solved!!!

Below is an example - a partial list of the script generated after running the script above.

exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_Airport'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'
exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_ArrivalCalendar'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'
exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_Transportation'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'
exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_Transportation_Details'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'

The script generated should be run in the publisher (server and database)

this is the picture from the replication monitor:
note on the right hand side before there were 2 lines, one with a red error cross on it, that was the subscription we wanted to delete and now it has been deleted:

there are still errors on this replication monitor, but they are unrelated to this subscriptions as they are on a different server.

I would like to include a link to the question below because it might be useful when you have problems deleting a publication

SQL Agent still attempting replication for deleted publication


Install the tools you need now, and no others.  You can always add more features to the installation later.

If you're not installing reports or PowerPivot into your existing SharePoint installation, don't install them.

When most people say that they want to get "SQL Server" installed, they mean just the database engine.  If that sounds like you, install the Database Engine and Management Tools, and you'll have a basic SQL Server up and running.


Restrictions on the GROUP BY Clause

  This clause is subject to the following restrictions:

  You cannot specify LOB columns, nested tables, or varrays as part of
  expr.
  The expressions can be of any form except scalar subquery expressions.
  If the group_by_clause references any object type columns, then the
  query will not be parallelized.

So here comes a cheap and dirty workaround, and no, I do not recommend using this in production.

drop table t1 purge;
create table t1(c1 SDO_ORDINATE_ARRAY);
insert into t1 values(SDO_ORDINATE_ARRAY(1, 1));
insert into t1 values(SDO_ORDINATE_ARRAY(1, 1));
insert into t1 values(SDO_ORDINATE_ARRAY(1, 2));
commit;

SQL> select c1 from t1;

C1
------------------------
SDO_ORDINATE_ARRAY(1, 1)
SDO_ORDINATE_ARRAY(1, 1)
SDO_ORDINATE_ARRAY(1, 2)

This obviously fails:

select c1, count(*) from t1 group by c1;
select c1, count(*) from t1 group by c1
                                     *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got MDSYS.SDO_ORDINATE_ARRAY

But this works:

select ora_hash(c1), count(*) from t1 group by ora_hash(c1);

ORA_HASH(C1)   COUNT(*)
------------ ----------
   111675277          1
   390137838          2

Using this:

select
  (
    select c1 from t1
    where ora_hash(c1) = group_by_sq.ora_hash_c1
    fetch first 1 row only
  ) as c1,
  group_by_sq.t1_count
from
(
  select ora_hash(c1) as ora_hash_c1, count(*) as t1_count
  from t1  group by ora_hash(c1)
) group_by_sq
;

C1                               T1_COUNT
------------------------------ ----------
SDO_ORDINATE_ARRAY(1, 2)                1
SDO_ORDINATE_ARRAY(1, 1)                2


Storing CREATE EXTERNAL DATA SOURCE in a SSDT project without exposing the DATABASE SCOPED CREDENTIAL or MASTER KEY secret;

After experimenting, it seems like any kind of secret in SSDT only gets used when the object is created, not updated.

Therefore if a sqlproj contains CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password A';, and no master key exists, then the master key will be created with that password. However, if one later executes ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'; on the database, and then redeploys the sqlproj to the database, it will not regenerate the master key.


Delete the extra data out of regular business hours, then shrink the database. Don't overcomplicate things.

Andy Mallon explains how in the post How to shrink a database in 4 easy steps:

First you'll get the file sizes for the databases on your instance, using the sys.database_files system view.
Then using DBCC SHRINKFILE, you'll shrink each file in question.
Finally, you'll rebuild all your indexes to ensure any fragmentation is handled.


SQL2043N Unable to start a child process or thread

Before reporting this as error, the max number of processes (ulimit -u) should be checked and increased for the instance owner.
I'm sure they would ask it anyway.


How do I extract row/column elements from multiple tables with same schema in single query

Simplest way would be to use UNION:

SELECT * FROM A
     UNION
SELECT * FROM B
     UNION
SELECT * FROM C

or use a JOIN:

SELECT *
FROM A
FULL OUTER JOIN B
ON < A.name=B.name>;

Read more about joins

Hope this helps.


Optimizing a query with ORDER BY in a derived table

SELECT consoleId,
       servertime,
       servertime AS servertimeUNIX,
       nocName,
       eventIdx,
       nocStatus  AS status,
       nid,
       site,
       machine,
       clientversion,
       timeExecuted,
       count(*)   AS eventCount
FROM   temptest
WHERE  servertime >= 1367902800
       AND servertime <= 1370581199
       AND nid = 1124
GROUP  BY FROM_UNIXTIME(servertime, '%Y-%m-%d'),
          site,
          machine,
          nid;


Given that:

There are some columns you don't want to pull through for each table
Column names are different for each table
You only have 40 tables
This is a one-time migration (this is safe to assume, correct?)

Then I would just copy and paste the core dataflow and make per-table changes as appropriate. For a non-repeating process that is a small amount of tables (not 200 or more), you don't want to spend too much time creating a repeatable process that will never be repeated ;-). And you are going to have to manage the differences anyway, so you might as well just put your effort into that part.

And it should be possible in SSIS to define a reusable component (script task) to handle some of the "common" manipulations. If not, just create a stored procedure that accepts a table name and handle the common steps there. Then, in SSIS, just call that Stored Procedure for each table and that way you will have a single definition for the common stuff, and if you need to make a slight change in the common changes, then you will only need to make them in one spot :).

(please see UPDATE section below) Another option for handling data migration when you have issues of not pulling all fields over and possibly renaming fields is to use the SqlBulkCopy Class in .NET as it handles those things quite nicely. You can either create a C# Task in SSIS to use this class, or you can use a SQLCLR stored procedure such as DB_BulkCopy which is part of the SQL# SQLCLR library (which I am the author of, but this stored procedure is in the Free version). I describe this in  a little more detail in the following answer:

Import data from one Database to another script

UPDATE

The following requirement was added a day after I posted the suggestions above:

  I have to merge all these tables in one table without duplicates.

In light of this new requirement, I think that using the SqlBulkCopy alternate option is less attractive than sticking with SSIS in the manner that I described first. The only way to de-dupe the data using SqlBulkCopy / DB_BulkCopy would be to run the process from the destination / target server, load the data into a temp table (it would have to be a global temporary table -- meaning, starting with ##) from the source server using SqlBulkCopy or DB_BulkCopy, and then insert into the real destination table from the global temporary table while filtering out the "duplicates" based on whatever rules you have to determine what a duplicate is.


What is the cost of changing the search path in postgres in a schema-per tenant model

I would say your intuition is correct. Changing the search_path causes Postgres to start from scratch with prepared statements. The manual on PREPARE:

  Also, if the value of search_path changes from one use to the next,
  the statement will be re-parsed using the new search_path. (This
  latter behavior is new as of PostgreSQL 9.3.)

And SQL statements inside PL/pgSQL functions are treated like prepared statements.

Especially while working with connection poolers (same connection stays open), it will help performance if you keep switching the search_path to a minimum.

The extra cost is not too high for simple statements, but it may be a noticeable effect for more complex queries.


How do I configure SQL Server 2012 so that it can restore and see files in my user account?

For reference "Denali" is SQL Server 2012. With regards to "end user confusion", I am not all that concerned with whether a end user is confused or not with regards to SSMS. Microsoft did not develop this tool for the normal end user, but for the database administrator and/or a user that had to manage a database. Therefore there will be a learning curve with the tools provided and how they function. The file dialog box has been that way in SSMS since SSMS came out with SQL Server 2005. This is why you will general see most stick with T-SQL statements for backing up, restoring, or attaching a database that have been using it since then.

To configure file system permissions with SQL Server you can follow the instructions from MSDN here.

The way service accounts are handled did not come with or because of SQL Server, it was due to the change on the operating system level. Window Server 2008 R2 put a bit more of a security layer around service accounts. The advantage you have is that the service account can more easily access resources on a domain even if installed with the default settings. This link provides a pretty detailed look at how service account permissions are handled with SQL Server 2012. Excerpt from the link is below on the Virtual Accounts used by default in SQL Server 2012. There is also a link provided in the article that goes into more discussion on the Service Account concept with Windows, here. It is from Window Server 2008 R2 but I believe still holds true on Window Server 2012, and likely Window Server 2012 R2.

  Virtual Accounts

  Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed
  local accounts that provide the following features to simplify service
  administration. The virtual account is auto-managed, and the virtual
  account can access the network in a domain environment. If the default
  value is used for the service accounts during SQL Server setup on
  Windows Server 2008 R2 or Windows 7, a virtual account using the
  instance name as the service name is used, in the format NT
  SERVICE\. Services that run as virtual accounts access
  network resources by using the credentials of the computer account in
  the format \$. When specifying a virtual
  account to start SQL Server, leave the password blank. If the virtual
  account fails to register the Service Principal Name (SPN), register
  the SPN manually. For more information on registering a SPN manually,
  see Register a Service Principal Name for Kerberos Connections.
  Note Note
  Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.

  The following table lists examples of virtual account names.

  Default instance of the Database Engine service: NT SERVICE\MSSQLSERVER
  Named instance of a Database Engine service named PAYROLL: NT SERVICE\MSSQL$PAYROLL
  SQL Server Agent service on the default instance of SQL Server: NT SERVICE\SQLSERVERAGENT
  SQL Server Agent service on an instance of SQL Server named PAYROLL: NT SERVICE\SQLAGENT$PAYROLL


How do dirty reads at both the transaction and table level provide increased performance?

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is at the transaction level or at the session level while NOLOCK is a query hint.

You mentioned that you fully understand dirty reads, so using at the transaction level is what I would recommend.

if you want dirty reads on some tables only, then NOLOCK hint will help you.

SQL server 2005 and up allows you to use SNAPSHOT isolation where readers don't block writers and it uses row versioning with some penalty on tempdb.

essentially SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and NOLOCK are same thing but the use depends on the context of what you are using.


Mongodb - save image as bytes or as files

MongoDB will happily store binary data such as files, using its GridFS system. That way you can keep the images and other user data together in one place, which is an advantage over storing the images on the file server.


Select rows within group before condition is met

A single window function in a subquery should do the trick:

SELECT *
FROM  (
   SELECT *, count(*) FILTER (WHERE height = 0)
                      OVER (PARTITION BY traj_id ORDER BY timestamp) AS ct0
   FROM   trajectory_data
   ) sub
WHERE  ct0 = 0;

This counts occurrences of 0 along the timeline per traj_id, and only keeps rows before the first one pops up. Voilá.

db<>fiddle here

Related:

Return counts for multiple ranges in a single SELECT statement
Select longest continuous sequence


Regardless of EC2,

do what makes sense for the data
don't pre-optimize

Columns are not for similar-data that serve the same purpose. If you're writing entries use rows. If you're describing them use columns. Let aggregates do the voodoo of taking the data from the rows and rolling it up to something useful.

Now for your application, if I was doing GPS stuff, I would not be using separate columns for lat and long but instead I would use

PostGIS and the GEOGRAPHY type,
or, if not possible, the point(long, lat) (at least it supports <@> (distance)) as well as some other useful operators and you can upgrade later


Usage of foreign key when the referenced table data will not change

Even If you want to go with the 3rd Option you can have a Foreign Key.

As in your example you are discussing about the days of the week they will never change.

In a case other then Days of the week where there is a probability of change, in my opinion you must go for a foreign-Key in order to maintain your Database Integrity.


How can I get MySQL to accept both resume and resumé on a primary key?

It seems that MySQL does not support Accent Sensitive collations. (See http://dev.mysql.com/doc/refman/5.6/en/charset-collation-implementations.html)

Therefore your only option is to go with a binary collation, e.g. utf8_bin. However, that would also make the column case sensitive, which is different from your current collation.

See also https://stackoverflow.com/questions/500826/how-to-conduct-an-accent-sensitive-search-in-mysql


Validate Primary Key and Index Selection

I wouldn't get too hung up on estimating the correct indexes ahead of time.  Make the ones you think you'll need then watch the missing index DMV's after you get some data in the table.

In my experience, it's pretty difficult to correctly estimate the indexes that will have the biggest impact until you start actually querying the data, unless you know EXACTLY the queries you will use.  If you DO know exactly the queries you will be using, post them so we can help you.

You will also want to view the estimated and actual execution plans for any queries you do make, looking for table scans with an eye to converting them into index seeks.


Does the Sql Server Agent account need sysadmin permission?

Further to Shawn's answer, the Books Online topic titled Select an Account for the SQL Server Agent Service also says:

The account that the SQL Server Agent service runs as must be a member of the following SQL Server roles:

The account must be a member of the sysadmin fixed server role.

To use multiserver job processing, the account must be a member of the msdb database role TargetServersRole on the master server.


This might give you an idea:

create table Customer (
  c_firstname varchar2(50),
  c_lastname  varchar2(50),
  c_userid    varchar2(50)
);

insert into Customer values ('Micky' , 'Mouse', 'mm');
insert into Customer values ('Donald', 'Duck' , 'dd');
insert into Customer values ('Peter' , 'Pan'  , 'pp');

create or replace function GetCustomer(
  FirstN    varchar2 := null,
  LastN     varchar2 := null,
  CID       varchar2 := null
) return sys_refcursor
as
  stmt varchar2(4000);
  ret sys_refcursor;
begin
  stmt := 'select * from Customer where 1=1';

  if  FirstN is not null then
      stmt := stmt || ' and c_firstname like ''%' || FirstN || '%''';
  end if;

  if  LastN is not null then
      stmt := stmt || ' and c_lastname like ''%' || LastN  || '%''';
  end if;

  if  CID is not null then
      stmt := stmt || ' and c_userid like ''%' || CID || '%''';
  end if;

  dbms_output.put_line(stmt);

  open ret for stmt;
  return ret;
end;
/

Later, in SQL*Plus:

set serveroutput on size 100000 format wrapped

declare
  c sys_refcursor;
  fn Customer.c_firstname%type;
  ln Customer.c_lastname %type;
  id Customer.c_userid   %type;
begin
  c := GetCustomer(LastN => 'u');

  fetch c into fn, ln, id;
  while  c%found loop
      dbms_output.put_line('First Name: ' || fn);
      dbms_output.put_line('Last Name:  ' || ln);
      dbms_output.put_line('user id:    ' || id);

      fetch c into fn, ln, id;
  end loop;

  close c;
end;
/

Edit: The comment is right, and the procedure is subject to SQL injection. So, in order to prevent that, you could go with bind variables such as in this modified procedure:

create or replace function GetCustomer(
  FirstN    varchar2 := null,
  LastN     varchar2 := null,
  CID       varchar2 := null
) return sys_refcursor
as
  stmt varchar2(4000);
  ret  sys_refcursor;

  type parameter_t is table of varchar2(50);
  parameters parameter_t := parameter_t();
begin
  stmt := 'select * from Customer where 1=1';

  if  FirstN is not null then
      parameters.extend;
      parameters(parameters.count) := '%' || FirstN || '%';
      stmt := stmt || ' and c_firstname like :' || parameters.count;
  end if;

  if  LastN is not null then
      parameters.extend;
      parameters(parameters.count) := '%' || LastN || '%';
      stmt := stmt || ' and c_lastname like :' || parameters.count;
  end if;

  if  CID is not null then
      parameters.extend;
      parameters(parameters.count) := '%' || CID || '%';
      stmt := stmt || ' and c_userid like :' || parameters.count;
  end if;

  if    parameters.count = 0 then
        open ret for stmt;
  elsif parameters.count = 1 then
        open ret for stmt using parameters(1);
  elsif parameters.count = 2 then
        open ret for stmt using parameters(1), parameters(2);
  elsif parameters.count = 3 then
        open ret for stmt using parameters(1), parameters(2), parameters(3);
  else  raise_application_error(-20800, 'Too many parameters');
  end   if;

  return ret;
end;
/

Note, that now, whatever the input, the select statement becomes something like select ... from ... where 1=1 and col1 like :1 and col2 :2 ... which is obviously much safer.


Difference between encrypted backup of an encrypted DB and an unencrypted backup of an encrypted DB

Does Rman Re-Encrypt TDE (Tablespace) Encrypted Data? (Doc ID 819167.1)

(above link requires an account associated with a valid support contract)

  When using column encryption, these columns as well as other data in
  the tables are encrypted again during the backup. If you are using
  tablespace encryption these are not further encrypted during backups.

...

  ... for compressed backup on an encrypted tablespace, RMAN must decrypt
  all the blocks, compress them, and encrypt them using RMAN encryption ...


List all articles with their amounts at a certain date+time

I you don't care about the time in the result it's pretty easy:

select article_id, sum(amount)
from stock_history
where amount_at <= '2016-03-01 00:00:00'
group by article_id

If you want time, you need to determine which time you should present. If you want the same time as in the where clause you can just add it since it is a constant:

select article_id, '2016-03-01 00:00:00', sum(amount)
from stock_history
where amount_at <= '2016-03-01 00:00:00'
group by article_id

Assuming it is the last time before this time you can map all times to this time with a derived table and use that:

select a.article_id, b.amount_at, sum(a.amount)
from stock_history a
join ( select article_id, max(amount_at) as amount_at
       from stock_history
       where amount_at <= '2016-03-01 00:00:00'
       group by article_id
) as b
    on a.rticle_id = b.article_id
   and a.amount_at <= b.amount_at
group by a.article_id, b.amount_at;


dropping SYNONYM is blocking in sql server?

No, dropping a synonym requires a Sch-M lock, and also IX locks on sysschobjs/sysobjvalues, at least if it is on the same server (I haven't tested what happens when the synonym uses a 4-part name).

What exactly would it mean if you could change a synonym in the middle of a query or transaction? If you have a query plan operator or a cursor which needs to go back to the table, or you have multiple steps in a transaction that reference the table, it would violate all kinds of principles if the referenced table were different. I think it is much better that you can't change it until a point where nobody is referencing it.


Does the size of the database depend on the amount of data stored in the column or on the type of data stored?

For your specific case the size on disk will be longer if you type more characters. This is because you defined your column as VARCHAR i.e. "varying-length character". Varying-length fields have to indicate the actual number of character present so they have an additional internal filed to show this. Therefore even empty strings consume some space when held in VARCHAR. If the column had been declared as CHAR the number of characters typed would not affect the space used. The remainder would be filled with spaces.

When looking at the bigger picture things get more complicated. Disk space is only ever consumed in chunks, often called "pages." One page is the minimum amount of disk the DB can request from the operating system and is also the minimum amount that can be read from or written to disk. Defining a table with one column of CHAR(1) and a single row will still consume a whole page of disk, typically 16kB.

Further, compression can be applied. Then the amount of space used depends on the length of the source strings and how similar they are to other strings held on that page. To be clear, the page size is still fixed (16kB) but more rows can be held on that one page compared to uncompressed storage. Then a single IO (which is typically the slowest step in query processing) will return more data, fewer IOs will be needed overall and the average query latency will reduce.

When designing tables it is best to declare the smallest size that will satisfy the actual business requirement. Don't declare them too short because those limits are hard limits and data truncation will occur. But definitely don't get lazy and use the maximum size just because it is easy. The declared sizes affect how many rows can fit on a page and so how many IOs are needed to satisfy a query. They can also affect memory usage, potentially reserving space for data which isn't actually present. Overly-generous size declarations can affect performance.


Is there a way to edit the System Database Directory for DB2 to remove a database?

So far how we have gotten around this: We have uncatalogued the database. Then we rename the node locally (ie, C:\DB2, the default in the windows version). Then we need to uncatalogue the node. Then we can rebuild our databases with no problems.


Table design: how to store denormalized Excel data

You will want to add an alternate key/unique constraint upon the combination of Indicator and Year. By virtue of the value being stored at the intersection of Indicator and Year in the spreadsheet, you are only allowed a single value. The new table design would allow you to have as many entries for a given indicator and year unless you explicitly disallow that.

DDL is approximately

CREATE TABLE dbo.MyTable
(
    Id int IDENTITY(1,1)
,   Indicator varchar(10) NOT NULL
,   Year smallint NOT NULL
,   Value int NOT NULL
,   CONSTRAINT PK_MyTable PRIMARY KEY
(
    Id
)
,   CONSTRAINT AK_MyTable_Indicator_Year UNIQUE
(
    Indicator
,   Year
)
);


Fastest way to create local copy of live InnoDB MySQL database?

You can take a backup from the live MySQL with Xtrabackup.

Percona provides deb repository for Ubuntu. To install the repo for Ubuntu 12.04 follow instructions:

Install the key:

# apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Add the source file:

# cat /etc/apt/sources.list.d/percona.list
deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main

Update the local cache:

# apt-get update

And install xtrabackup:

# apt-get install xtrabackup

To take a copy of the database run following:

    # innobackupex .

It will create a directory like "2010-03-13_02-42-44". Copy that directory to a development box, apply redo log:

    # innobackupex --apply-log /data/backups/2010-03-13_02-42-44/

Then the directory is ready to use. Copy it back to MySQL datadir and fix ownership:

    # cp /data/backups/2010-03-13_02-42-44/ /var/lib/mysql
    # chown -R mysql:mysql /var/lib/mysql

Then you may start MySQL.

More details you can find on http://www.percona.com/doc/percona-xtrabackup/2.1/how-tos.html


Joining 4 tables of which one is unrelated

Problem 1:  LIMIT is somewhat meaningless without ORDER BY to indicate which 25.

Problem 2:  Don't say x=null, it won't do what you think.  Instead do x IS NULL.

Problem 3:  Do you really want a "cross join"?  That is, a JOIN without an ON clause, and hence gets all combinations of the two tables?

Problem 4:  Well, I did not check the rest of what you have.

Back to your question...  The pattern is

( SELECT ... ORDER BY ... LIMIT 25 )
UNION ALL  -- or DISTINCT, depending on your understanding of the data
( SELECT ... ORDER BY ... LIMIT 25 )
ORDER BY ... -- Yes, again
LIMIT 25;

If you need an offset of, say 100, then:

( ... LIMIT 125 )
UNION ...
( ... LIMIT 125 )
ORDER BY ...
LIMIT 100, 25;


CHECK Constraint to enforce upper case letter and pattern match

It seems the database and column collations are case-insensitive so the LIKE expression is also case-insensitive.
One way to perform a case-sensitive compare in this scenario is by adding a COLLATE clause, specifying a case-sensitive collation. For example, if your database default collation is a case insensitive collation such as Latin1_General_CI_AS, the example below will override that collation with the case-sensitive version of the collation for the literal and perform the case-sensitive comparison you want:
CREATE TABLE Systems(
    SystemsID NVARCHAR(4),
    Title NVARCHAR(30),
    CONSTRAINT chk_SystemsID CHECK (SystemsID LIKE '[D][0-9][0-9][0-9]' COLLATE Latin1_General_CS_AS)
);

Below are related collation documentation pages for your perusal:

COLLATE
Collation Precedence


You will need to create a foreign table in the Linux server that points to the Postgres running on the Windows server.

Then you can move the rows with an INSERT and a DELETE statement

insert into archive_table (col1, col2, col3)
select c1, c2, c3
from local_table
where created_at_column <= current_date - 42;: -- rows older than 42 days

delete from local_table
where created_at_column <= current_date - 42;

Where archive_table is the foreign table that "points"  to the Windows server.

The DELETE will probably be quite slow (the more rows you archive, the slower it will be), a faster way would be to partition the table e.g. by month and then detach old partitions from the table.


Is it possible to combine 7 sources without a UNION?

Without the full table definition, it is difficult to provide a perfect answer.  However, in an attempt to show the differences in a limited repro, with a very small amount of data, I've created the following testbed:

IF OBJECT_ID('tempdb..#src') IS NOT NULL
DROP TABLE #src;
CREATE TABLE #src
(
    EmployeeCode INT NOT NULL
    , WeekStartDate DATE NOT NULL
    , HoursDay1 INT NOT NULL
    , HoursDay2 INT NOT NULL
    , HoursDay3 INT NOT NULL
    , HoursDay4 INT NOT NULL
    , HoursDay5 INT NOT NULL
    , HoursDay6 INT NOT NULL
    , HoursDay7 INT NOT NULL
    , Widget1Day1 INT NOT NULL
    , Widget1Day2 INT NOT NULL
    , Widget1Day3 INT NOT NULL
    , Widget1Day4 INT NOT NULL
    , Widget1Day5 INT NOT NULL
    , Widget1Day6 INT NOT NULL
    , Widget1Day7 INT NOT NULL
    , Widget2Day1 INT NOT NULL
    , Widget2Day2 INT NOT NULL
    , Widget2Day3 INT NOT NULL
    , Widget2Day4 INT NOT NULL
    , Widget2Day5 INT NOT NULL
    , Widget2Day6 INT NOT NULL
    , Widget2Day7 INT NOT NULL
    , PRIMARY KEY CLUSTERED (WeekStartDate, EmployeeCode)
);
GO
INSERT INTO #src (EmployeeCode, WeekStartDate
    , HoursDay1, HoursDay2, HoursDay3, HoursDay4, HoursDay5, HoursDay6, HoursDay7
    , Widget1Day1, Widget1Day2, Widget1Day3, Widget1Day4, Widget1Day5, Widget1Day6, Widget1Day7
    , Widget2Day1, Widget2Day2, Widget2Day3, Widget2Day4, Widget2Day5, Widget2Day6, Widget2Day7
    )
VALUES (1, '2016-09-18'
    , 0, 8, 8, 8, 8, 8, 0
    , ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID()))
    , ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID()))
    );

Below we are comparing the two queries; the first uses the CROSS APPLY method, detailed by me at SQLServerScience.com, and the second uses the UNION ALL method.

SELECT s.WeekStartDate
    , s.EmployeeCode
    , ItemsByDay.DayOfWeekName
    , ItemsByDay.HoursWorked
    , ItemsByDay.Widget1
    , ItemsByDay.Widget2
FROM #src s
CROSS APPLY (VALUES ('Sunday', HoursDay1, Widget1Day1, Widget2Day1)
    , ('Monday', HoursDay2, Widget1Day2, Widget2Day2)
    , ('Tuesday', HoursDay3, Widget1Day3, Widget2Day3)
    , ('Wednesday', HoursDay4, Widget1Day4, Widget2Day4)
    , ('Thursday', HoursDay5, Widget1Day5, Widget2Day5)
    , ('Friday', HoursDay6, Widget1Day6, Widget2Day6)
    , ('Saturday', HoursDay7, Widget1Day7, Widget2Day7)
    ) ItemsByDay(DayOfWeekName, HoursWorked, Widget1, Widget2);

SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Sunday'
    , s.HoursDay1
    , s.Widget1Day1
    , s.Widget2Day1
FROM #src s
UNION ALL
SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Monday'
    , s.HoursDay2
    , s.Widget1Day2
    , s.Widget2Day2
FROM #src s
UNION ALL
SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Tuesday'
    , s.HoursDay3
    , s.Widget1Day3
    , s.Widget2Day3
FROM #src s
UNION ALL
SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Wednesday'
    , s.HoursDay4
    , s.Widget1Day4
    , s.Widget2Day4
FROM #src s
UNION ALL
SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Thursday'
    , s.HoursDay5
    , s.Widget1Day5
    , s.Widget2Day5
FROM #src s
UNION ALL
SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Friday'
    , s.HoursDay6
    , s.Widget1Day6
    , s.Widget2Day6
FROM #src s
UNION ALL
SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Saturday'
    , s.HoursDay7
    , s.Widget1Day7
    , s.Widget2Day7
FROM #src s;

First thing to note, the CROSS APPLY is easier to look at.  This already makes me happy.

Lets check the execution plans for the two variants:

The UNION ALL variant scans the source table 7 times, whereas the CROSS APPLY uses a single table scan.  By using the cross apply, we're #Winning.

Let's add more data:

/* create a table with 2 years worth of week start dates */
IF OBJECT_ID('tempdb..#Weeks') IS NULL
BEGIN
    CREATE TABLE #Weeks
    (
        WeekStart DATE NOT NULL
        PRIMARY KEY CLUSTERED
    );

    INSERT INTO #Weeks (WeekStart)
    SELECT TOP(104) DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY o1.name) - 1) * 7, '2016-01-03')
    FROM sys.objects o1
        CROSS JOIN sys.objects o2;
END

/* remove the single row from the source table we inserted above */
TRUNCATE TABLE #src;

/* insert a load of rows into the #src table */
INSERT INTO #src (EmployeeCode, WeekStartDate, HoursDay1, HoursDay2, HoursDay3, HoursDay4, HoursDay5, HoursDay6, HoursDay7)
SELECT ABS(CHECKSUM(NEWID()))
    , w.WeekStart
    , 0, 8, 8, 8, 8, 8, 0
FROM #Weeks w
    CROSS JOIN sys.objects o1;

On my system the above code generated around 85,000 rows.  The plans for the two queries are now:

SQL Sentry Plan Explorer shows the following summary information, which is invaluable:

This says the CPU is used more intensively by the CROSS APPLY, however there is 7 times more I/O used by the UNION ALL variant.


Insert the same time, it is possible to happen?

Theoretically you could end up with duplicate usernames.
In a scenario of high incoming requests, a race condition could occur.

If you want to avoid this, just put an UNIQUE index on your column.
You could create a UNIQUE index on username by using this query:

CREATE UNIQUE INDEX IDX_USERNAME ON `user_tbl` (`username`);

Prior to creating this unique index, please verify that there are no duplicate entries in your table, as the index creation would fail.

When you have an unique index on this field, it will be impossible to end up with duplicate usernames.
When a username is inserted, the table is locked during the insertion, this guarantees that a race condition would not occur.


Finding or confirming absence of corrupted data in MongoDB

demux finishing (err:corruption found in archive; I/O error reading length or terminator ( gzip: invalid header ))

This error message refers to processing of the gzip archive rather than the BSON data within. Your test with gzip -t implies that the contents are likely OK and the error may be due to ignorable padding at the end of the archive (see: gzip complains with trailing garbage ignored).

  All but one are getting loaded properly. That one reproducibly hangs (indefinitely)

If the problem is easily reproducible, I would raise a bug report in the MongoDB Jira TOOLS project including more details such as the specific versions of mongodump and mongorestore and command line parameters used as well as how long you waited. It seems reasonable for mongorestore to have some sort of timeout or error handling for this case rather than hanging indefinitely; I also wouldn't expect mongodump to create files that the same version of mongorestore cannot handle.

  Now I find myself with some indication that a file I have loaded may have been corrupt, or at least had a corrupt header. Issuing realistic queries to the collection in question and its indices returns results that look reasonable.
    How can I definitively detect or rule out "corruption" of this type?  What indeed does "corrupt" mean in this context? Can I expect to find it in the database, or only within the zip file?

It's unlikely that you would be able to restore corrupt BSON documents into MongoDB, but there may be some nuances depending on how/where data is corrupted. In most cases invalid data will trigger an exception/assertion when the server attempts to read the BSON, but valid BSON does not necessarily mean that the field contents are as expected.

To be entirely certain you would have to compare the document counts or checksums of the restored data against the original data (assuming you still have a copy other than the gzip archive).


Whenever you create or drop a SYNONYM you are changing the definition of your database landscape.  So, yes I would consider that risky unless you have tight controls on when those steps can run.

If a connection resets the SYNONYM it is changing that synonym for the Server and database not for the connection. This means that a set of "complex work" running in another process could wind up switching from your remote data (for example) to the local data without any warning.  That would leave a mess to clean up.

View this like you would view dropping and recreating tables in a running system.  It may often work without anyone knowing, but it can indeed cause you problems.

Of course, if another connection tries to define a SYNONYM that already exists it will raise an error such as: "There is already an object named 'SY_SUBJECTS' in the database."  This will save you from switching context (but you must deal with the error) until after the synonym is dropped.  (There is no ALTER SYNONYM function, which would apparently be much more dangerous.)

Therefore if you try to change the SYNONYM without dropping it first, it will fail.

If a code path runs DROP SYNONYM it will succeed once it can acquire the needed locks.  From MSDN: "References to synonyms are not schema-bound; therefore, you can drop a synonym at any time."

So, the DROP SYNONYM will not stop a running transaction, but will wait for the transaction to finish.


Efficient manual cleanup of foreign keys in MySQL

Try batching the deletes, so that you are only checking a small set of records at a time, and so are locking a smaller set of records.  You can play around with increase or decreasing the size of @batchSize until you find the "magic number" that performs best for you.  Note: I don't normally work in MySQL so my while loop syntax may not be 100% correct, but the general idea is there.

DECLARE @i int;
DECLARE @batchSize int;
DECLARE @maxKey int;

SET @i = 0;
SET @batchSize = 2000;
SET @maxKey = (select max(id) from t2 where t2.tl_id IS NOT NULL);

WHILE (@i < @maxKey) DO
BEGIN
    DELETE t2
    FROM t2
    LEFT JOIN t1 ON t2.t1_id = t1.id
    WHERE t2.id >= @i and t2.id <= @i + @batchSize
        AND t2.t1_id is not null
        AND t1.ID is null
    ;

    SET @i = @i + @batchSize;
END WHILE


Considering SQL Server will automatically issue checkpoints, log clearing will continue and doesn't appear to be a concern at this point. Before making this change should I monitor data churn or checkpoint occurrences?

If these haven't historically been issues for you, then I think you are fine. It does indeed appear to be purely an RPO/SLA/SLO based issue.

That being said, you didn't mention what precipitated this change, but something to consider from Google's SRE book around SLA/SLOs:

  Using a tighter internal SLO than the SLO advertised to users gives you room to respond to chronic problems before they become visible externally. An SLO buffer also makes it possible to accommodate reimplementations that trade performance for other attributes, such as cost or ease of maintenance, without having to disappoint users.

So if there isn't an active problem necessitating this change, it might be worth keeping the more frequent differential backups in my opinion.


MySQL - INSERT DELAYED <- deprecated What now?

According to the MySQL 5.5. Documentation

INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. For engines that do not support DELAYED, an error occurs.

You could downgrade to MySQL 5.5 (Of course, I am joking). I have three suggestions. Please look into them.
SUGGESTION #1
You could setup MySQL Replication and perform the following

Bulk insert into a table on the Master whose storage engine is BLACKHOLE.
The same table on the Slave would be InnoDB. Perform your SELECTs on the Slave
See my 3.5-year-old post How to do something like UPDATE DELAYED in MySQL

SUGGESTION #2
Tuning the InnoDB Environment to handle faster writes. Please see my 4.5-year-old posts How to use insert delay with the InnoDB engine and use less connection for insert statements? and Mysql load from infile stuck waiting on hard drive
SUGGESTION #3
Customize the bulk insert to use extended inserts that load a fixed number of  rows at a time like a mysqldump does.


Is there a performance hit for querying two different databases?

There is essentially no difference between table and db.table.  Once the tables (for SELECT or for INSERT) is opened, it is just a table, regardless of what database it is in.


Data Consistency Between Two MySQL Databases Running Different Versions

Plan A:  Do it one step at a time.

Plan B:  Set up a long replication chain:  5.5 -> 5.6 -> 5.7

Plan C:  Take some downtime to do it via a dump.

Plan D:  "Update in place".  That is, install the next version on the existing version, keeping the data intact.  Be sure to run mysql_upgrade.  Repeat.

Caution:  The upgrade from 5.7 to 8.0 may be the trickiest.

Caution:  If you are using PHP's mysql_* interface, it would probably be better to switch to mysqli_* first.  mysql_* has been removed from php 7.

  I also want changes to the database on the new server to reflect on the database on the old server

Abandon that goal.  There are too many risks in using Dual Master with different versions -- primarily because the "Slave" needs to be no older than the "Master".

Get a separate machine to practice on.


For many people, the MySQL Achilles' heel is implicit commit.

According to Page 418 Paragraph 3 of the Book

the following commands can and will break a transaction

ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES
SET AUTOCOMMIT = 1
START TRANSACTION

SUGGESTION

When it comes to MySQL, any ContinuousIntegration (CI)/SelfService jobs you construct should always make Transactional jobs and DDL scripts mutually exclusive.

This gives you the opportunity to create paradigms that would

support transactions that are properly isolated with START TRANSACTION/COMMIT blocks
control of DDL by scripting the DDL yourself, running such DDL as either constructor or destructor

Constructor : DDL to make Tables with a New Design
Destructor : DDL to make Tables Revert Back to Previous Design

never combine these operations under one job

WARNING : If you are using MyISAM for any this, you can (un)kindly add MyISAM to the list of things that can break a transaction, maybe not in terms of implicit commit, but definitely in terms of data consistency should a rollback ever be needed.

WHY NOT LVM ?

LVM snapshots are great and restoring whole instances of databases without having to perform heavy SQL processing is ideal. However, when it comes to MySQL, you have to account for two storage engines: InnoDB and MyISAM.

All-InnoDB Database

Look at InnoDB's Architecture (Picture courtesy of Percona CTO Vadim Tkachenko)

InnoDB has many moving parts

System Tablespace

Data Dictionary
Double Write Buffer (support data consistency; used for Crash Recovery)
Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
Rollback Segments
Undo Space (where the most uncontrolled growth can happen)

InnoDB Buffer Pool

Dirty Data Pages
Dirty Index Pages
Changes to NonUnique Indexes

Other Important Memory Caches

Taking an LVM snapshot of an all-InnoDB database with uncommitted changes floating in the Buffer Pool and Memory caches would yield a dataset that would require InnoDB crash recovery once the LUN is restored and mysqld started.

SUGGESTION FOR ALL-InnoDB

If You Can Shutdown MySQL Before Taking Snapshot

Run SET GLOBAL innodb_fast_shutdown = 0;

Run SET GLOBAL innodb_max_dirty_pages_pct = 0;

Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

Repeat Step 3 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible

service mysql stop

Take LVM snapshot

service mysql stop

If You Cannot Shutdown But Take Snapshot With MySQL Live

Run SET GLOBAL innodb_max_dirty_pages_pct = 0;

Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

Repeat Step 2 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible

Take LVM snapshot

Run SET GLOBAL innodb_max_dirty_pages_pct = 75;

All-MyISAM Database or InnoDB/MyISAM Mix

MyISAM, when accessed, maintains a count of open file handles against it. If MySQL crashes, any MyISAM table with an open file handle count > 0 will be marked as crash and in need of repair (even if nothing is wrong with the data).

Taking an LVM snapshot of a database that has MyISAM tables in use will have one or more MyISAM tables in need of repair when the snapshot is restored and mysqld started.

SUGGESTION FOR All-MyISAM or InnoDB/MyISAM Mix

If You Can Shutdown MySQL Before Taking Snapshot

Run SET GLOBAL innodb_fast_shutdown = 0;

Run SET GLOBAL innodb_max_dirty_pages_pct = 0;

Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

Repeat Step 3 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible

service mysql stop

Take LVM snapshot

service mysql stop

If You Cannot Shutdown But Take Snapshot With MySQL Live

You could enforce a flushing of certain InnoDB tables

Run SET GLOBAL innodb_max_dirty_pages_pct = 0;

Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

Repeat Step 2 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible

Run FLUSH TABLES innodb_tbl1,... FOR EXPORT; on critical InnoDB tables

Run FLUSH TABLES WITH READ LOCK;

Take LVM snapshot

Run UNLOCK TABLES;

Run SET GLOBAL innodb_max_dirty_pages_pct = 75;

Could MySQL Replication Help ?

While you could restore one LVM snapshot to two servers and setup MySQL Master/Slave Replication, that becomes an extra source of housecleaning when restoring snapshots.

If you run CI jobs on a Master and those jobs are small, replication could be a timesaver under certain circumstances. You could just run STOP SLAVE; on the Slave, kick off the CI jobs on the Master, and run START SLAVE; on the Slave when the Master's data is certified.

If the CI jobs alert too much data, you could restore LVM snapshot and setup replication from scratch. If you find yourself doing this often, you could probably do with setting up MySQL Replication.

FINAL THOUGHTS

It is best to use multiple DB Servers (3 or more) to perform restores and regression tests.
Convert remaining MyISAM tables to InnoDB if those tables do not need to remain MyISAM.
If your data content is sensitive, you should execute a CI job to scrub the data after restoring a snapshot before initiating any tests. As an alternative, you may want to take snapshots of MySQL with the data already scrubbed.


Error while executing query using send attachment

the solution was to specify the database prefix in the sql query in the @query parameter. After lot of scratch on my head i realized without prefixing DB name+Schema in Exec @query it will not work.

Did not work: @query = 'SELECT fields FROM table'
Works: @query = 'SELECT fields FROM database..table'


When you transferred data, you probably compacted data by an implied index rebuild.

Or the REPAIR_ALLOW_DATA_LOSS lived up to it's name...


DB design for employee working hours constraints

Use RFC 5545 RRules and ExRules to represent recurring events (days/times they can't work)

Use pg_rrule to turn recurring events into actual events. Use a materialized view.


Use string functions and arithmetic calculations in SQL query or by programming language?

If you're going to bring the same amount of rows from the database anyway, then you had better perform your mathematical functions in your application, not in your database. Math calculations in SQL are notoriously slow.

Also note that this takes computation from a single database server into possibly multiple PHP front ends. Sure, you might also spread the load over multiple MySQL replication servers; generally speaking, you will have more PHP front ends than MySQL back ends.


SQL Server Configuration Manager not showing services

Start-->Run and type services.msc and check installed SQL Services.  Do you see the installed SQL Server services?
Please refer below articles.

These may help:

SQL Server configuration manager is empty
Why is SQL Server Configuration Manager Missing Services


SQL command mode - unable to run command

I used double quotes in your setvar commands and removed the (+) signs from your attempted concatenation.  This worked for me

:setvar ScriptPath "C:\Work\"
:setvar SQLFile "Test.sql"

print '$(ScriptPath)$(SQLFile)'    ---- Works
GO
:r $(ScriptPath)$(SQLFile)         ---- Works
GO


Do any of the graph based/aware databases have good mechanisms for maintaining referential integrity?

Only allow creation of a comment if an edge is created to link this comment to a post at the same time.

This is supported by Dgraph Labs' GraphQL implementation out of the box. In your schema, you would add a exclamation mark ! to denote that an edge is required when added and when retrieved.
type Post {
  id: ID
  comments: [Comment]
}
type Comment {
  id: ID
  text: String!
  onPost: Post! @hasInverse(field: comments)
}

This schema above, would mandate that every Comment have a reference onPost edge and would return errors if you send mutations that did not match this requirement.
This would also error if you were to query a comment and request the onPost edge and it did not exist. How might that happen if the Comments cannot be added without the edge? Because the @hasInverse will do the bookkeeping to keep the Post.comments and Comment.onPost edges consistent. This allows you to delete a post node that could delete Comment.onPost edges.

Prevent deletion of a post while there are edges from comments to this post.
Prevent deletion of an edge from a comment to a post without deleting the comment itself

Right now in Dgraph's GraphQL implementation there is no way to enforce or prevent cascade deletes as there is with SQL-like databases. This would have to be managed from a middleware level. Having that said, Dgraph Labs has implemented a way to currently do this with the @custom directive and are currently developing (promised for 20.11 release) a @lambda directive to enable an almost native javascript middleware functionality. Combining either one of these methods with another currently awaiting release directive @generate. The generate directive will allow you to disable the default generated mutations and then you can create your own custom mutations to handle this business logic as needed with DQL upserts. These custom mutations can then be mapped and served via the GraphQL endpoint.


I need to merge two queries with condition

Make your where clause operate conditionally based on your input value like this:

SELECT
URYRR_FORWARDS_REC_H.CB_LINE as "CB_LINE",
URYRR_FORWARDS_REC_H.CB_LINE_DESCRIPTION as "DESCRIPCION",
URYRR_FORWARDS_REC_H.UYU_BALANCE_FW as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.USD_BALANCE_FW as "MONEDA EXTRANJERA",
URYRR_FORWARDS_REC_H.UYU_BALANCE_COA as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.USD_BALANCE_COA as "MONEDA EXTRANJERA",
URYRR_FORWARDS_REC_H.DIFFERENCE_MN as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.DIFFERENCE_ME as " MONEDA EXTRANJERA"
FROM TNLRUY.URYRR_FORWARDS_REC_H
WHERE (:BOOK_DATE IS NULL OR
    (:BOOK_DATE IS NOT NULL AND BOOK_DATE=to_date(:BOOK_DATE,'yyyy-mm-dd'));


Get the unmatched user clockin vs user table

The best solution is something like this:

CREATE TABLE personnel (id INTEGER, name VARCHAR(30));

populate it:

INSERT INTO personnel VALUES (1, 'Joe'), (2, 'Mary'), (3, 'Bill'), (4, 'Fred');

Create and populate the timecard table:

CREATE TABLE timecard (empid INTEGER, clockin_time DATETIME);

INSERT INTO timecard VALUES (1, '2019-08-28 08:00:18');
INSERT INTO timecard VALUES (2, '2019-08-28 08:00:28');
INSERT INTO timecard VALUES (3, '2019-08-28 08:00:28');
INSERT INTO timecard VALUES (1, '2019-08-29 08:00:03');
INSERT INTO timecard VALUES (4, '2019-08-29 08:04:04');

Note that only Joe and Fred have clocked in on the 29th of August - Mary and Joe haven't.

You can find the fiddle here.

So, first run this query to determine who clocked in on the 29th.

SELECT p.id AS the_id -- , p.name, t.empid -- remove -- to see results - shows thought process!
FROM timecard t
LEFT JOIN personnel p
  ON t.empid = p.id
WHERE DATE(t.clockin_time) = '2019-08-29'
ORDER BY p.id, t.clockin_time;

Result:

the_id
     1
     4

So, we see that Joe (id 1) and Fred (id 4) have clocked in.

Now, we need to find out the identities of those who didn't clock in. This is the list of people whose id's are not in the result of the first query. We obtain this by doing the following:

SELECT * FROM personnel pl
WHERE pl.id NOT IN
(
  SELECT p.id AS the_id
  FROM timecard t
  LEFT JOIN personnel p
    ON t.empid = p.id
  WHERE DATE(t.clockin_time) = '2019-08-29'
  ORDER BY p.id, t.clockin_time
);

Result:

id  name
 2  Mary
 3  Bill

Something like this is probably what you require - if not, let me know. This construction allows you to do tallies of how many times person with id x was absent in a given month, day, using BETWEEN and lots more.... fun for all the family! :-)


postgres database user grants to query database owned by other user

Your app user should be created like this.
Create the user:
CREATE ROLE "app" WITH ENCRYPTED PASSWORD '${APP_PASSWORD}' LOGIN;

Run this on mydb database.
Grant Connect to DB:
GRANT CONNECT ON DATABASE mydb to app;

Grant Usage:
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "app";
GRANT USAGE ON ALL TABLES IN SCHEMA public TO "app";

Grant Other privileges:
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON ALL TABLES IN SCHEMA public TO "app";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO "app";


voluntarily produce an error with DBCC CheckDb

Would anyone know how I can make DBCC CheckDB produce an error?

Use the lethal dbcc writepage ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool]) ON a TEST (NON-PROD) fully sandbox environment.

  The purposes of DBCC WRITEPAGE are:

  To allow automated testing of DBCC CHECKDB and repair by the SQL Server team.
  To engineer corruptions for demos and testing.
  To allow for last-ditch disaster recovery by manually editing a live, corrupt database.

Apart from "The Guru" Paul Randal, @SteveStedman has a good series of Database Corruption Challenge (with solution - but refer it later once you try to address it by yourself).


Mysql Slave doesn't find binary log from slave

Master -> A -> B

binlog on A needs to be turned on.

B must use the binlogs from A, not from the Master.

So...

Turn on binlogging on A.
Stop replication from Master -> A.
Dump A
Load dump onto B
CHANGE MASTER to point B to A.
Start replication from Master -> A.  Things should flow all the way to B.