MERGE with IDENTITY_INSERT ON does not work if Identity is not the primary key

Identity values cannot be updated.  They can be inserted if IDENTITY_INSERT is turned on for that table.

This code shows how that works:

USE tempdb;

CREATE TABLE dbo.TestIdentity
(
    ID INT NOT NULL
        IDENTITY(1,1)
    , SomeData VARCHAR(255) NOT NULL
);

INSERT INTO dbo.TestIdentity (SomeData)
VALUES ('This is a test');

--This works

SET IDENTITY_INSERT dbo.TestIdentity ON;

INSERT INTO dbo.TestIdentity (ID, SomeData)
VALUES (1, 'This is a test');

SET IDENTITY_INSERT dbo.TestIdentity OFF;

/*
    This fails with:

    Msg 8102, Level 16, State 1, Line 15
    Cannot update identity column 'ID'.
*/
SET IDENTITY_INSERT dbo.TestIdentity ON;

UPDATE dbo.TestIdentity
SET ID = 2
WHERE ID = 1;

SET IDENTITY_INSERT dbo.TestIdentity OFF;

You may want to replace the IDENTITY column with a column that uses a manually incremented value.  If you were using SQL Server 2012+ you could use a SEQUENCE to populate the values.  Since you are using SQL Server 2008 R2, you will need to roll your own solution to generate values to replace the identity.  One such way is detailed here.

You could potentially resolve your problem using the OUTPUT clause to simultaneously delete the row, then insert it into the table with a modified ID value.  However, this is predicated on not using the merge construct.

/*
    This works, but cannot be used with MERGE
*/
TRUNCATE TABLE dbo.TestIdentity;

INSERT INTO dbo.TestIdentity (SomeData)
VALUES ('This is a test');

SET IDENTITY_INSERT dbo.TestIdentity ON;

DELETE
FROM dbo.TestIdentity
OUTPUT 2 /* The new identity value */
    , deleted.SomeData
INTO dbo.TestIdentity (ID, SomeData)
WHERE ID = 1 /* the old identity value */;

SET IDENTITY_INSERT dbo.TestIdentity OFF;

SELECT *
FROM dbo.TestIdentity


MySQL Tuner OPTIMIZE and Defragmentation

Once you delete some rows from a table its data file could become fragmented and that table becomes unoptimized in terms of MySQL. If you really do frequently delete rows from those tables, you should not worry because this is the expected behavior. You don't need to do anything else.

OPTIMIZE TABLE is usually used to reclaim the unused space and to defragment the data file. Also you should know that MySQL locks the table during the time OPTIMIZE TABLE is running, which means that no one can run queries against that table and has to wait.


Within a trigger is there a way to tell if an update or insert came from a trigger or not?

There are several ways for determining if the trigger is recursing. Checking the trigger depth (pg_trigger_depth()) is not one of them, because you don't know if the trigger itself is recursing or is being fired from another trigger.

The only direct way to know analysing the execution stack during an exception:

DECLARE
  context text;
BEGIN
  BEGIN
    SELECT 1/0;
  EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS context = PG_EXCEPTION_CONTEXT;
  END;
  -- analyse here the context variable to find if the last item in the stack
  -- is this same function
END;

Exception handling is costly, but so are the other options, which require leaving a session marker for avoiding recursion, either using a temporary table, or a session variable:

BEGIN
  IF to_regclass('pg_temp.' || quote_ident(TG_NAME)) THEN -- it's recursing
    RETURN NEW; -- or NULL, or whatever action is required when recursing
  END IF;
  EXECUTE format('CREATE TEMP TABLE %I (a int) ON COMMIT DROP', TG_NAME);
  -- general body of trigger
  EXECUTE format('DROP TABLE pg_temp.%I', TG_NAME);
  RETURN NEW; -- or whatever appropriate
END;

or using a session variable:

BEGIN
  IF current_setting('x.' || TG_NAME, true) IS DISTINCT FROM '1' THEN
    -- it's recursing
    RETURN NEW; -- or NULL, or whatever action is required when recursing
  END IF;
  PERFORM set_config('x.' || TG_NAME, '1', true);
  -- general body of trigger
  PERFORM set_config('x.' || TG_NAME, '', true);
  RETURN NEW; -- or whatever appropriate
END;

All of the options above have a significant performance impact. A possibly better performing option could involve a table for this purpose:

CREATE TABLE trigger_execs (
  pid int NOT NULL DEFAULT pg_backend_pid(),
  tgname name NOT NULL,
  PRIMARY KEY (pid, tgname)
);

which can be used like:

BEGIN
  IF EXISTS (SELECT 1 FROM trigger_execs
             WHERE pid = pg_backend_pid() AND tgname = TG_NAME) THEN
    -- it's recursing
    RETURN NEW; -- or NULL, or whatever action is required when recursing
  END IF;
  INSERT INTO trigger_execs (tgname) VALUES (TG_NAME);
  -- general body of trigger
  DELETE FROM trigger_execs
  WHERE pid = pg_backend_pid() AND tgname = TG_NAME;
  RETURN NEW; -- or whatever appropriate
END;


Yes, FIPS 140-2 is configured at OS level. You cannot be compliant if the OS itself is not compliant. See KB2733626: Instructions for using SQL Server 2012 in the FIPS 140-2-compliant mode (not sure where is the 2014 equivalent).


Can anyone tell how exactly apply works and how will it effect the performance in very large data

APPLY is a correlated join (called a LATERAL JOIN in some products and newer versions of the SQL Standard). Like any logical construction, it has no direct impact on performance. In principle, we should be able to write a query using any logically equivalent syntax, and the optimizer would transform our input into exactly the same physical execution plan.

Of course, this would require the optimizer to know every possible transformation, and to have the time to consider each one. This process might well take longer than the current age of the universe, so most commercial products do not take this approach. Therefore, query syntax can, and often does, have an impact on final performance, though it is difficult to make general statements about which is better and why.

The specific form of OUTER APPLY ( SELECT TOP ... ) is most likely to result in a correlated nested loops join in current versions of SQL Server, because the optimizer does not contain logic to transform this pattern to an equivalent JOIN. Correlated nested loops join may not perform well if the outer input is large, and the inner input is unindexed, or the pages needed are not already in memory. In addition, specific elements of the optimizer's cost model mean a correlated nested loops join is less likely than a semantically-identical JOIN to produce a parallel execution plan.

  I was able to make same query with single left join and row_number()

This may or may not be better in the general case. You will need to performance test both alternatives with representative data. The LEFT JOIN and ROW_NUMBER certainly has potential to be more efficient, but it depends on the precise query plan shape chosen. The primary factors that affect the efficiency of this approach is the availability of an index to cover the columns needed, and to supply the order needed by the PARTITION BY and ORDER BY clauses. A second factor is the size of the table. An efficient and well-indexed APPLY can out-perform a ROW_NUMBER with optimal indexing if the query touches a relatively small portion of the table concerned. Testing is needed.


Connect to SQL Server with Windows Authentication in a different domain

There is another way, which I now use in preference to the runas /netonly method.
You can add the credentials to your profile in Windows using the Credential Manager found in the Windows control panel.

Open Credential Manager

Click "Add A Windows Credential"

Populate the "internet or network address" field with the name and port number of the SQL instance you wish to store credentials for.
Example: UniServer:1433 (1433 is the default port, you may need a different port, especially if you are connecting to a named instance)

Populate the "User Name" (don't forget to include the domain e.g. MYDOMAIN\MYUSER)

Populate the "Password"

Click OK

If you have the server name, port and login details correct, you should now be able to use Windows Authentication from most client tools, SSMS, Excel, whatever. They will all use the stored credentials.
Tip: Sometimes you need to use the FQN for the server when adding the credentials. e.g. UniServer.UniDomain.org:1433, it all depends on your network specifics.
Here is a quick demo of the method : http://youtu.be/WiVBPsqB9b4
It is a screen grab of me attempting (and failing) to connect to a SQL Server running in a VM from my desktop, then adding the required credentials and trying again - successfully.
Tip: use the "cmdkey /add" command to script creating and updating stored credentials.


Result set with multiple rows based on max date and not NULL

SELECT DISTINCT StaffCode,
       FIRST_VALUE(FirstName)
           OVER (PARTITION BY StaffCode
                 ORDER BY CASE WHEN FirstName IS NOT NULL
                               THEN EffectiveDate
                               END DESC) FirstName,
       FIRST_VALUE(LastName)
           OVER (PARTITION BY StaffCode
                 ORDER BY CASE WHEN LastName IS NOT NULL
                               THEN EffectiveDate
                               END DESC) LastName,
       FIRST_VALUE(MarriageStatus)
           OVER (PARTITION BY StaffCode
                 ORDER BY CASE WHEN MarriageStatus IS NOT NULL
                               THEN EffectiveDate
                               END DESC) MarriageStatus
FROM SourceTable;


Let's step back a little: you're basically switching from a SQL Server that someone else manages, to managing your own server.

Q: What are the security challenges involved with running your own server?

You can get hacked, or you can incorrectly set permissions to let strangers access your data and publish it. Here are some resources to get you started:

Microsoft's SQL Server 2008 Compliance Guide white paper - it's very much still relevant today
The book Securing SQL Server by Denny Cherry

Q: What are the high availability challenges involved?

Amazon RDS for SQL Server uses database mirroring for high availability. In your own EC2 instances, you can choose whether you'd like a similar level of protection with mirroring or Always On Availability Groups, or if you'd like to save money and get less protection.

You didn't ask about patching, but I'd bundle that in here: the burdens of patching will now be on you rather than Amazon.

Q: Can I make my migration restores go faster?

Right now, Amazon RDS only gives you access to full backups. Your backup/restore migration timeline is:

Shut down your app
Back up RDS to S3
Restore the S3 file backup to EC2 SQL Server
Start your app again

That's going to result in long downtimes for a 200GB database. To shorten it, you can use tools like Red Gate's Data Compare or build your own comparison tool to detect what changes have been made to your database. For example, on one project, the developers added LastUpdated timestamp fields to all the tables, and maintained those with triggers. Then, after our restore, we could shut down the apps, copy across the specific records that had been updated, and go live with only a few minutes of downtime.

Q: What time zone should I use for my EC2 servers?

Just as RDS was configured with UTC, so should your SQL Servers. Just set your EC2 boxes to be in UTC time zone regardless of their location, and then you won't have problems when you fail over from one region to another.


Structure for storing table data, lists, text, pictures within PostgreSQL JSON field

If you're looking for alternatives other than JSON, and you just need arrays of text name-value pairs collections, you could simply use PostgreSQL's arrays:

CREATE TYPE mydoc AS (content_type text, names text[], values text[]);
CREATE TABLE documentData(id int primary key, documentName text, docs mydoc[]);
INSERT INTO documentData(id,documentName,docs) VALUES (1, 'DocumentA', '{"(Table,\"{ColA,ColB,ColC}\",\"{val1,val2,val3}\")","(Table,\"{ColA,ColB,ColC}\",\"{val4,val5,val6}\")"}'::mydoc[])

SELECT d.id, d.documentName, dd.*
FROM documentData d
LEFT JOIN LATERAL unnest(d.docs) dd ON (true);

This won't get a significant advantage over JSON, other than portability to older PostgreSQL versions, but unnest and =ANY type of operations are often much simpler than the JSON functions, so it may be easier to query and manipulate, e.g.:

SELECT d.id, d.documentName, dd.content_type, pos, name, dd.values[pos] AS value
FROM documentData d
LEFT JOIN LATERAL unnest(d.docs) dd ON (true)
LEFT JOIN LATERAL unnest(dd.names) WITH ORDINALITY AS y (name, pos) ON (true)
WHERE dd.content_type = 'Table' AND name ~ '^Col[AB]';


Efficient query to get greatest value per group from big table

Index
A plain multicolumn B-tree index should work after all:
CREATE INDEX foo_idx
ON geoposition_records (equipment_id, created_at DESC NULLS LAST);

Why DESC NULLS LAST?

Unused index in range of dates query

It's safe to assume you have an equipment table? Then performance won't be a problem:
Correlated subquery
Based on this equipment table, run a lowly correlated subquery to great effect:
SELECT equipment_id
     ,(SELECT created_at
       FROM   geoposition_records
       WHERE  equipment_id = eq.equipment_id
       ORDER  BY created_at DESC NULLS LAST
       LIMIT  1) AS latest
FROM   equipment eq;

For a small number of rows in the equipment table (57 judging from your EXPLAIN ANALYZE output), that's very fast.
LATERAL join in Postgres 9.3+
SELECT eq.equipment_id, r.latest
FROM   equipment eq
LEFT   JOIN LATERAL (
   SELECT created_at
   FROM   geoposition_records
   WHERE  equipment_id = eq.equipment_id
   ORDER  BY created_at DESC NULLS LAST
   LIMIT  1
   ) r(latest) ON true;

Detailed explanation:

Optimize GROUP BY query to retrieve latest record per user

Performance similar to the correlated subquery.
Function
If you can't talk sense into the query planner (which shouldn't occur), a function looping through the equipment table is certain to do the trick. Looking up one equipment_id at a time uses the index.
CREATE OR REPLACE FUNCTION f_latest_equip()
  RETURNS TABLE (equipment_id int, latest timestamp)
  LANGUAGE plpgsql STABLE AS
$func$
BEGIN
   FOR equipment_id IN
      SELECT e.equipment_id FROM equipment e ORDER BY 1
   LOOP
      SELECT g.created_at
      FROM   geoposition_records g
      WHERE  g.equipment_id = f_latest_equip.equipment_id
                           -- prepend function name to disambiguate
      ORDER  BY g.created_at DESC NULLS LAST
      LIMIT  1
      INTO   latest;

      RETURN NEXT;
   END LOOP;
END
$func$;

Makes for a nice call, too:
SELECT * FROM f_latest_equip();

Performance comparison:
db<>fiddle here
OLD sqlfiddle


Is there any way to track the connections opened and relate it to a connection pool created from database side.

Set the Application Name in your config and you can monitor from the server side in sys.dm_exec_sessions.  You'll get a separate Connection Pool for each Connection String, so if you set the Application Name in the connection strings you can tell what pool each connection belongs to.

  the queries which normally runs faster ,goes slow. Is it expected?

Yes.  Before you start seeing timeouts getting connections, you will have waits of up to 30 seconds waiting for a pooled connection to become available.  So it may be a delay in opening or re-opening a connection, rather than a slow query.

Or it could be an indirect correlation.  Often the code path that leaks connections only happens when you get some other Exception.   For instance blocking or resource exhaustion on the database is causing timeout Exceptions, which, due not using using blocks is causing the leaked connections.  But that's just speculation.

BTW 99.99% of the time the cause is: "Opened connections are not closed, called connection leaking."


Behavior of data in indexes based on fill factor

Fill factor only comes into play when an index is created or rebuilt.  It is the amount of consumption for the index of the leaf level pages that are filled during these operations. (see the note below for more clarification on affected page levels)

When there is a DML command to data (INSERT, UPDATE, and/or DELETE), it will happen to the corresponding affected indexes.  In other words, if you have a page that is 20% filled and you insert data into that page, the page will contain more than 20% of the data (let's say 35% just for example's sake).  Do another insert, now the page is 64% filled.  Rebuild the index, and the leaf level pages will now relatively contain the space percentage that you specify (or implicitly the default value for the server).

(Note, when you don't specify PAD_INDEX to be ON, fill factor is only applied to leaf level pages.  But when you set PAD_INDEX = ON, the fill factor will be taken into account for intermediate-level pages of the index.  The default is OFF)

The reason to adjust fill factor (instead of using the default 100/0) is so that you minimize page splits when inserting or updating data.  But keep in mind, nothing is for free.  The lower the fill factor, the more space data would normally take up.  If you keep an 80% free page space for your indexes, they will consume a relatively larger amount of disk space, which can lead to more reads.

  From my understanding, when the data is inserted there will be roughly 20% of data in the pages. When the data is updated, however, it will expand to more than 20% of the index, upto filling it and generating a page split, right?

When data is inserted it will insert into the appropriate indexes at the appropriate page.  This could and most likely would very well cause the page consumption to be higher than the fill factor.

A page split will happen when new data is added to a full index page.  SQL Server will then split the page and approximately place half of the data from the full page into a new page.  Again, fill factor does not come into play here.

A legitimate reason to lower fill factor would be to minimize page splits, therefore minimizing index page fragmentation.


_ and - are interchangeable in configuration files, however as SQL system variables only _ is supported.

relay-log is a variable. relay-log-slave is just a value string.


What procedure can be used to change system statistics in a production environment

For major database statistics regathers, it is best to forget the old stats and essentially regather statistics freshly on the whole system. I do not advise doing this frequently because it is a significant workload undertaking on your system and there are better methods of controlling statistics over time.

This would likely require a script to go out and gather all the tables that exist in your database and put them into a queue for the DB to analyze. After the DB has completed reanalysis you need to publish the changes for them to take effect.

Once you've forced the system to stage the new statistics, this is how to flush the old statistics out and push the staged statistics into production.

exec dbms_stats.flush_database_monitoring_info;

exec dbms_stats.import_database_stats('< table_name >','< table_value >','< user >',force=>true);

exec dbms_stats.export_schema_stats ('< user >','< table >','ACTUAL','< user >');

exec sys.dbms_stats.delete_system_stats();

exec sys.dbms_stats.gather_system_stats(gathering_mode=>'NOWORKLOAD'); #This is subjective based upon the workload the system was on during analysis

exec dbms_stats.gather_dictionary_stats();

exec dbms_stats.gather_fixed_objects_stats();

EDIT:
I cannot yet respond to the comment asking if there are ever any adverse effects from gathering statistics.

Statistics regathers cannot promise that they won't make one or many individual pieces of the system perform poorly. Sometimes SQL scripts will determine that there is a better path based on your new statistics that is actually a worse path in reality. Sometimes it'll make a quiet script that runs millions of times a day with no issue turn into a problematic script that halts the DB (until you go tell it to take the old path). It also cannot promise general performance increases on your database. It can only promise that the statistics on your tables will become fresh.

The other risk is that statistics gathering and analysis is an intensive process which can bog down the system if done under production stress. It is best to do a full statistics regather when you're guaranteed that the majority of users won't be on the system (overnight, during client downtimes, etc)

Finally, if you are gathering statistics while a user is making massive changes then that can cause some unruly performance changes. It's another reason to gather stats while minimal users are on the system or when no atypical changes are being made.


ALTER TABLE ... ROW_FORMAT=Compressed times out

Please note that I am not a MySQL developer, I use MS SQL Server.  But the behavior in your post suggests the following:

It does not look like a deadlock to me, especially with the error messages:

InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.

A deadlock normally would quickly determine which connection to roll back instead of processing up to 600 seconds before crashing the server.

Likely what is happening is that the table metadata change (the ALTER TABLE) cannot happen while there are other transactions using the table.

http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

The link says, in part: "To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session."

EDIT: Sorry for the misunderstanding regarding deadlocks.

Per this post, https://stackoverflow.com/questions/24860111/warning-a-long-semaphore-wait

Ensure that: Innodb_adaptive_hash_index=0


Change code editor variable highlighting colour

Variable colors share settings with table, object and other names. So you can change the text color using Identifier:

Just note that it will also affect table names, column names etc.

For the highlighted color of identifiers and variables, use Selected Text. The highlights will use a slightly muted version of that color.

And no, these are not my current settings, and I only kept them that way long enough to grab a screen shot...


Would i benefit in this case from the myisam table engine?

No you won't.

  What could i do to further optimize the query or the settings?

Materialize the query.

MySQL does not have built-in means to do that easily (similar to indexed views in SQL Server or materialized views in Oracle) so you'll have to put some effort into it.

Create a table like this:

CREATE TABLE log_customer_date
        (
        customer BIGINT NOT NULL,
        logdate DATE NOT NULL,
        sumbytes BIGINT NOT NULL,
        countrecords BIGINT NOT NULL,
        PRIMARY KEY (customer, logdate)
        )

fill it:

INSERT
INTO    log_customer_date
SELECT  customer, logdate, SUM(bytes) sumbytes, COUNT(*) countrecords
FROM    logdata
GROUP BY
        customer, logdate

and add further records in a trigger:

INSERT
INTO    log_customer_date
VALUES  (NEW.customer, NEW.logdate, NEW.bytes, 1)
ON DUPLICATE KEY
UPDATE  sumbytes = sumbytes + VALUES(sumbytes),
        countrecords = countrecords + VALUES(countrecords)

or in a script every once in a while:

INSERT
INTO    log_customer_date
SELECT  customer, logdate, SUM(bytes) sumbytes, COUNT(*) countrecords
FROM    logdata
WHERE   id > :lastid
GROUP BY
        customer, logdate
ON DUPLICATE KEY
UPDATE  sumbytes = sumbytes + VALUES(sumbytes),
        countrecords = countrecords + VALUES(countrecords)

, recording the highest id by the moment of the insert somewhere.


database design for song position under play-list

Instead of Position field you can use a field as LastSongId where you can add the SongId of the previous song in the play list.

So when you want to change you order you only need to update only three rows.

The first row: Your song with new order

The second row / the third row: The song before and after your your changed position song

Your new schema:

+------+------------+----------+
|SongId| PlaylistId |LastSongId|
+------+------------+----------+
|   1  |     10     |    NULL  |
|   2  |     10     |    1     |
|   3  |     10     |    2     |
|   5  |     10     |    3     |
|   6  |     11     |    NULL  |
+------+------------+----------+

If you change the SongId 3 as position 1, you'll have:

+------+------------+----------+
|SongId| PlaylistId |LastSongId|
+------+------------+----------+
|   1  |     10     |    3     |
|   2  |     10     |    1     |
|   3  |     10     |    NULL  |
|   5  |     10     |    2     |
|   6  |     11     |    NULL  |
+------+------------+----------+


Regularly clone/backup 2 MySQL databases unidirectionally from PC to Laptop both running Windows

You need to set up MySQL replication.
In your case, can use the following documentation for version 4.1

http://ftp.nchu.edu.tw/MySQL/doc/refman/4.1/en/replication-howto.html

An additional recommendation, upgrade those servers to a newer version.


pg_basebackup: could not set compression level -1

I tried the command without a compression option and received this error message:

pg_basebackup: could not create file "/dir/names/base.tar": Permission denied

which revealed that the destination directory was not writable for the user. Changing permissions or ownership solved the problem.

Unfortunately, the original error message was not as informative as it could have been.


Is my mssql 2008 standard suitable for web edition 2014

Web edition looks like Standard edition except "It is only available through an SPLA" going by the specs listed in the article you linked to.

With regard to a 2Gb limit, are you thinking about Express edition (or MSDE as it was called pre 2005)? That has had a per database size limit of 10Gb since SQL2008R2 (it was 4Gb in 2008 and 2005 and 2Gb in 2000 & SQL7). Note that is is per-database and data files only, so you could have several databases each under 10Gb that consume much more than 10Gb in total, and log files don't count towards the total. The key limit on Express is that it won't use more than 1Gb of RAM per instance which will limit performance for large data. You can mitigate this a little by running multiple instances. So running Express in a VM may be suitable for your needs and the least hassle option as you don;t have to worry about differences between SQLServer .

I don't have much experience with Azure SQL so I can't comment in any detail on the relative suitability of that for your needs. There are some differences in what is supported so you'll need to find documentation on that and test any potentially affected parts of your application. Be ware of which resources you use for this research: there have been many changes recently so articles form a couple of years ago may be pretty much irrelevant. See this SO question and other similar recent questions for potentially useful references. As well as feature compatibility you'll need to judge the performance requirements of your app against the "Database Throughput Units" (and therefore the cost) of the account types you are looking at, see this MS page for more details about the performance specs for each service tier.


Query to view failure detail for a given sql agent job step?

You should be able to adjust this query to your criteria - taken from What is the Query to display the failed sql jobs
select j.name
    ,js.step_name
    ,jh.sql_severity
    ,jh.message
    ,jh.run_date
    ,jh.run_time
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
   ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
   ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE jh.run_status = 0


Identifying Execution Plans with Probe Residual

What you want to do is to use nodes() Method (xml Data Type) in a cross apply to shred on RelOp nodes so you then can use value() Method (xml Data Type) to get the property values you are looking for.

You are specifically looking for the RelOp nodes that have a Hash/ProbeResidual node so you should use that in a predicate in the XQuery parameter to the nodes() function.

WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT DEQP.query_plan,
       DEST.text,
       RO.X.value('@PhysicalOp', 'nvarchar(50)') as PhysicalOp,
       RO.X.value('@LogicalOp', 'nvarchar(50)') as LogicalOp,
       RO.X.value('@EstimatedTotalSubtreeCost', 'float') as EstimatedTotalSubtreeCost
FROM sys.dm_exec_cached_plans AS DECP
  CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
  CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) as DEST
  CROSS APPLY DEQP.query_plan.nodes('//RelOp[Hash/ProbeResidual]') as RO(X);


The id antipattern is having a unique ID column in each table without requiring (or often discouraging) the application of alternate unique keys.  The ID is generated for each new record.  Why is this an antipattern?

Numeric surrogate keys are fine, including when they are singleton primary keys.

However, every time I have seen a schema in which EVERY table has this id column, the designer wanted the row to be just like a memory handle.  This avoids the crucial question of primary key design and fosters repeated insertion of contradictory redundant data.  It makes database designers tear their hair out.

In the memory of a running application, memory handles like this are transient.  They are dead when the application exits, and no restart of the application tries to use them again.  This makes pointers and object handles useful in that context.

Similarly, storing a handle/pointer to one object as a piece of another object makes sense.  It imposes ownership of the one object over the other.

These handles have an intrinsic meaning only insofar as some client came in and needed data munged around.  Apart from client action the handle is devoid of meaning.

However, a database stores permanent data with permanent IDs.  You need to have a way of getting back to the data you want three days afterward.  To do that you need to have something meaningful somewhere, not just memory handles made permanent.

Why do database designers care about primary keys and alternate keys?  Because when someone comes back to the database and can't find their data, the database is worthless.  If you need to browse and analyze the data, how will you figure it out if everywhere you look there are columns named 'id' with no other constraints to tell you what they mean?  Which of the 90000 rows with object_label = 'Important Thing' is the one you want?


MySQL Union All Causing date field to lose format

I have fixed this by enclosing the Union in brackets and using it as a derived table in a Select query, didn't need to alter any formats etc.

Is it always best to do this with Union queries? The examples I found online did not show this.

Thanks


Will PostgreSQL server work if I install the service in mounted partition

Yes, you can move your base directory to any location you wish where the postgres service has write access and enough storage available. There are a few things to note

your startup script needs to set it's PGDATA directory correctly to the new location
your clients that connect locally have to set their PGDATA to the new location.
Tablespaces in postgres add a lot to complexity. Better is to try to prevent them being used at all.

Tablespaces are directories that are pointed to by symbolic links in $PGDATA so you can keep them if you like. They will be resolved.


Transferring IBD Files from one MySQL database to another in Windows

No.

A .ibd is not transportable.  There are two alternatives:

Read about "transportable tablespaces".
Use mysqldump (or other) to create INSERT statements to recreate the table.

The .ibd is missing two things... the .frm file that gives the schema, plus some meta info in ibdata1, along with lots of other stuff for the other tables, etc.


stats_column_id and index_column_id do not update with physical order of clustered index is changed

I was having the same issue while trying to reproduce the way others retrieve index information from the sys.dm views in SQL Server. I just couldn't figure out the order of the columns in the index.

Following is a script I created to determine the order of the columns in any given index for a given table:

SELECT s.name                  AS Schema_name,
       o.name                  AS Table_Name,
       i.type_desc             AS Index_Type,
       i.name                  AS Index_Name,
       c.name           AS Table_Column,
       i.fill_factor           AS Indx_Fill_Factor,
       ic.key_ordinal          AS [Key_ordinal (IDX Column_Order)],
       ic.index_column_id      AS Index_column_id,
       stc.stats_column_id     AS Stats_Col_ID,
       -- Additional info for each joined table
       -- comment out what you don't need
       -- 2 lines at a time
       --
       -- '| table object -->', -- column seperator
       -- o.*,
       -- '| schema object-->', -- column seperator
       -- s.*,
       '| index info-->', -- column seperator
       i.*,
       '| sys index info -->', -- column seperator
       si.*,
       '| indx cols info -->', -- column seperator
       ic.*,
       '| tab cols info -->', -- column seperator
       c.*,
       '| idx stats info -->', -- column seperator
       st.*,
       '| idx stats columns info -->', -- column seperator
       stc.*
FROM   sys.objects             AS o
       JOIN sys.schemas        AS s
            ON  s.schema_id = o.schema_id
       JOIN sys.indexes        AS i
            ON  i.object_id = o.object_id
       JOIN sys.sysindexes as si
            ON  si.[id] = i.object_id
            AND si.indid = i.index_id
       JOIN sys.index_columns  AS ic
            ON  ic.object_id = i.object_id
            AND ic.index_id = i.index_id
       JOIN sys.columns        AS c
            ON  c.object_id = ic.object_id
            AND c.column_id = ic.column_id
       JOIN sys.stats          AS st
            ON  st.object_id = i.object_id
            and st.stats_id = i.index_id
      JOIN sys.stats_columns  AS stc
      ON c.column_id = stc.column_id
      AND stc.stats_id = st.stats_id
      AND stc.[object_id] = o.[object_id]
WHERE  1=1
     --and i.type <> 1 -- Exclude Clustered Indexes. 0 = Heap; 1 = Clustered Index, 2 = Non-Clustered Index
       AND s.name != 'sys' -- Exclude sys items
       and o.name = 'BusinessEntityAddress'
       AND i.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
ORDER BY
       o.object_id,
       i.index_id,
       ic.key_ordinal

The column key_ordinal in the sys.index_columns table is the order in which the columns are stored in the index.

There isn't a key_ordinal column for the sys.stats_columns table. The column stats_column_id just replicates the index_column_id column of the object it references.

There is a slight difference in the wording of the article sys.stats_columns (Transact-SQL) for the column stats_column_id:

  1-based ordinal within set of stats columns.

...and in the article sys.index_columns (Transact-SQL) for the key_ordinal column:

  Ordinal (1-based) within set of key-columns.

I reckon that the index_column_id (sys.index_columns) and stats_column_id (sys.stats_columns) are the equivalent of each other and that only the sys.index_columns table has an ordering column, namely key_ordinal.


Oracle: Delete row in a table using triggers while deleting value in another table

You can't select or modify the underlying table of the trigger during trigger processing.  In this case, you are selecting.

Don't perform this work in a trigger.  Manage the delete in PL/SQL application code where you can perform the delete, count for the children, and then delete the parent when there are no children.  Basically what I'm interpreting your logic to be.


I won't be able to answer all your questions, but for what it's worth:

SQL modules seem to be a part of the SQL/PSM 2008 standard. At least they appear in the draft version, that can be found on the interwebs.
Tools will eventually catch up with the server features. Meanwhile, you can use the Oracle-compatible PL/SQL dialect, currently supported by tools, to create PL/SQL packages, which are essentially the same thing.
Yes, people do use module. Many of the DB2 system-provided routines are implemented in modules.
While you can restrict direct invocation of routines outside modules by granting appropriate privileges, you cannot "modularize" anything without "modules".


Different types of users in one table, How to add data off different users

Here is the table structure

CREATE TABLE `users`
( `userid` INT NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(50) NOT NULL,
   user_type int 1,
  `email` VARCHAR(100) NOT NULL,
   PRIMARY KEY (`userid`)
);

CREATE TABLE `user_type`
( `user_typeid` INT NOT NULL AUTO_INCREMENT,
  `user_type` VARCHAR(50),
   PRIMARY KEY (`user_typeid`)
);

CREATE TABLE `tbl_corordinator`
( `coordinator_id` INT NOT NULL AUTO_INCREMENT,
   user_id int 1,
  `field1` VARCHAR(100) NOT NULL,
   PRIMARY KEY (`coordinator_id`)
);

When you will add 'Coordinator' users in users table, you need to add information in  tbl_corordinator & you will add userid (which is the primary key of users table.)

Thus using this foreign key concept, you can achieve this relation.


Security implications of enabling CLR on shared instance

Given that we are dealing with a shared instance, it should be safe to assume that none of the logins for the "customer" DBs has sysadmin privileges or has been granted the CONTROL SERVER permission.

Under such conditions, there should not be any security risk to having SQLCLR enabled, regardless of what many people seem to claim. Having the instance-level "CLR enabled" option enabled merely allows for a login that has the appropriate permission to load assemblies, and for loaded assemblies to be used by anyone granted access to the objects in those assemblies.

If users cannot load assemblies, then there can be no risk of having unknown code in assemblies, because there won't be any assemblies. Then, if one user (such as yourself) is granted the CREATE ASSEMBLY permission (which should be coupled with using Certificates to handle the assembly security), then only you can load assemblies.

Of course, for SQL Server 2005 - 2016, and for SQL Server 2017 and newer that has the "CLR strict security" instance-level configuration option disabled, the database owner (i.e. the dbo user) and any user in the db_owner fixed database role can create SAFE assemblies, but those aren't a security risk. Sure, they might be a performance risk, but the same is true for sloppy data modeling, T-SQL, triggers, etc. If the various vendors are not dbo for their databases, then this is a non-issue. If the vendors are dbo for their respective databases and the desire is to prevent them creating SAFE assemblies, it is easy enough to create an instance-wide DDL trigger for the CREATE ASSEMBLY statement and issue a ROLLBACK if the user is not you and/or it is not executed within your database.

If someone is a member of the sysadmin instance-level role, or granted the CONTROL SERVER instance-level permission, then they can also load assemblies. BUT, and here is what most "security" related recommendations miss: having "CLR enabled" disabled does not protect against someone who is a sysadmin or has CONTROL SERVER permission because they have permission to enable the "CLR enabled" option (and enable xp_cmdshell, etc and do whatever they want)!

There is no inherent risk in having this option enabled because having it enabled does not give anyone the permission to load assemblies that pose a security risk (in SQL Server 2005 - 2016, dbo and users in db_owner role can load SAFE assemblies). And, once loaded, assemblies marked as EXTERNAL_ACCESS or UNSAFE need additional security (i.e. Certificates / strong naming, not TRUSTWORTHY) in order for their code to be executed. And, starting in SQL Server 2017 all assemblies, by default, need that additional security just to be loaded in the first place. And, that additional security (for the operation of the assembly) requires sysadmin level permissions:

In the unfortunate case of someone setting the database to TRUSTWORTHY ON, the login that owns the DB needs to have either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission (for SQL Server 2017+ with "CLR strict security" enabled — the default — it must be UNSAFE ASSEMBLY), which can only be granted by a sysadmin (these permissions are obviously implied if the owning login is a sysadmin). Hopefully vendor DBs would never have TRUSTWORTHY enabled.
When assemblies are signed, a certificate or asymmetric key needs to be created in the [master] DB, then a login needs to be created from that, then that login needs to be granted either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission (for SQL Server 2017+ with "CLR strict security" enabled — the default — it must be UNSAFE ASSEMBLY).

For some examples of handling SQLCLR security, please see:

Stairway to SQLCLR Level 3: Security (General and SAFE Assemblies)
Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies)

That being said, as David Browne noted in a comment on this answer:

  Another important point is about the trust that presumably exists among the stakeholders for the various databases. In a corporate environment, everyone is expected to be trustworthy and cooperative. in a 3rd party hosting environment, you have to assume that the owner of the adjacent workload is a completely untrusted and even malicious actor. Lots of not-perfectly-safe things are safe-enough to do behind a firewall and among friends.


postgresql rename a foreignkey after renaming a table

If you are using PostgreSQL 9.2 or newer, you can use RENAME CONSTRAINT:

ALTER TABLE name RENAME CONSTRAINT "error_test_id_fkey" TO "the_new_name_fkey";


Recommendation for mySQL table structure (no code)

Try this
userMaster
| USERID |   NAME |
-------------------
|      1 |  Peter |
|      2 | George |

machineMaster
| MACHINEID |   MACHINENAME | NOOFSET | IMAGEURL |
--------------------------------------------------
|         1 |     treadmill |      10 | blahblah |
|         2 | leg extension |       5 |  xyz.jpg |
|         3 |           xyz |       6 |      asd |

planMaster
| PLANID | PLANNAME |
---------------------
|      1 |     xyaz |
|      2 |     bbbb |

planDetail
| PLANID | MACHINEID |
----------------------
|      1 |         1 |
|      1 |         3 |
|      2 |         1 |
|      2 |         2 |
|      2 |         3 |

userPlan
| USERID | PLANID |
-------------------
|      1 |      1 |
|      2 |      1 |
|      2 |      2 |

userPlanDetail
| USERID | MACHINEID | FLAG |
-----------------------------
|      1 |         1 |    1 |
|      2 |         2 |    1 |
|      2 |         3 |    1 |

Only 1 concern is that how you will manage if user selects a new plan. so for this you have 2 approaches

Delete the user existing plan from userPlan and userPlanDetail table

If you want to maintain history of user selected plan you can then you can a have a flag field like which I have used in the above table which denotes that this plan or machine is under current use of the user or not.

If you are following 2nd scenario then while inserting records in the userPlan and userPlanDetail table use INSERT ON DUPLICATE KEY UPDATE.
Other tables are quite simple i don't think so it needs any explanation, post if you have any doubts.
Hope this helps...
SQL FIDDLE


Use of IN clause within a DELETE command - MySQL

Yes, you can use IN() in the WHERE clause of a DELETE statement.

There is no "IN clause" -- IN() is just a comparison operator, perhaps slightly disguised as something more sophisticated.

http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#function_in

To illustrate what IN() is really doing, consider these examples in isolation.

mysql> SELECT 1 IN (2,3,4); -- should be false
+--------------+
| 1 IN (2,3,4) |
+--------------+
|            0 | -- is indeed false
+--------------+
1 row in set (0.00 sec)

mysql> SELECT 1 IN (2,1,4); -- should be true
+--------------+
| 1 IN (2,1,4) |
+--------------+
|            1 | -- is indeed true
+--------------+
1 row in set (0.00 sec)

...and, as @ypercube has pointed out, the "where_condition is an expression that evaluates to true for each row to be deleted".


Unusual error message - sql server the row object is inconsistent. please rerun the query

As a first step, I'd suggest running DBCC CHECKDB against all databases on that instance.  Here's a quick script that will run against each database (including system databases).  This could take quite a bit of time (depending on the size of your databases), but you need to let it finish running.

DECLARE @Message VARCHAR(max);
DECLARE @databaseList AS CURSOR;
DECLARE @databaseName AS NVARCHAR(500);
DECLARE @tsql AS NVARCHAR(500);

SET @databaseList = CURSOR  LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
        SELECT QUOTENAME([name])
        FROM sys.databases
        WHERE [state] = 0;
OPEN @databaseList;
FETCH NEXT FROM @databaseList into @databaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Message = 'Starting DBCC for ' + @databaseName + ' -> (' + convert(varchar,getdate(),121) + ')'
    RAISERROR(@Message,0,1) WITH NOWAIT
    SET @tsql = N'DBCC CheckDB(' + @databaseName + ') WITH ALL_ERRORMSGS,NO_INFOMSGS;';
    RAISERROR(@tsql,0,1) WITH NOWAIT
    exec SP_EXECUTESQL @tsql;
    SET @Message = 'Completed DBCC for ' + @databaseName + ' -> (' + convert(varchar,getdate(),121) + ')'
    RAISERROR(@Message,0,1) WITH NOWAIT

    FETCH NEXT FROM @databaseList into @databaseName;
END
CLOSE @databaseList;
DEALLOCATE @databaseList;


What is the equivalent of SQL Server's db_datareader/db_datawriter in PostgreSQL?

There is no builtin role like that in PostgreSQL.  However, you can set up one:

CREATE ROLE db_datawriter;

After this, you have to assign write privileges (usually INSERT, UPDATE, DELETE, maybe TRUNCATE) to it.  To see how to do this for all present and future tables, read my older answer.

Normally, write access also implicates reads, therefore it make sense to add db_datawriter as a member of db_datareader (which was set up similarly, but with SELECT only):

GRANT db_datareader TO db_datawriter;

Then you can grant these two to different login users (or other roles) as necessary:

CREATE USER example_user IN ROLE db_datareader;

Note that CREATE USER implicates being able to log in, unlike CREATE ROLE.


This is a known and expected issue - the way IDENTITY columns are managed by SQL Server has changed in SQL Server 2012 (some background); by default it will cache 1000 values and if you restart SQL Server, reboot the server, fail over, etc. it will have to throw out those 1000 values, because it won't have a reliable way to know how many of them were actually issued. This is documented here. There is a trace flag that changes this behavior such that every IDENTITY assignment is logged*, preventing those specific gaps (but not gaps from rollbacks or deletes); however, it is important to note that this can be quite costly in terms of performance, so I'm not even going to mention the specific trace flag here.

* (Personally, I think this is a technical problem that could be solved differently, but since I don't write the engine, I can't change that.)

To be clear about how IDENTITY and SEQUENCE work:

Neither is guaranteed to be unique (you need to enforce that at the table level, using a primary key or unique constraint)
Neither is guaranteed to be gapless (any rollback or delete, for example, will produce a gap, this specific problem notwithstanding)

Uniqueness is easy to enforce. Avoiding gaps is not. You need to determine how important it is for you to avoid these gaps (in theory, you should not care about gaps at all, since IDENTITY/SEQUENCE values should be meaningless surrogate keys). If it is very important, then you should not be using either implementation, but rather roll your own serializable sequence generator (see some ideas here, here and here) - just note that it will kill concurrency.

Lots of background on this "problem":

SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry
identity increment is jumping in sql server database
Identity column value suddenly jumps to 1001 in sql server


Extracting only numbers from data in mysql query

You can use the TRIM function, for example:

mysql> SELECT TRIM(BOTH '_' FROM '_21_' );
21

So in your case, to remove all _ characters, you can use something like:

 $query = "SELECT Date, TRIM(BOTH '_' FROM Tenext) as Tenext FROM tbdata";


Code required to make column not nullable

Just take care of current NULL values by updating them to a default value.
Then do your ALTER TABLE

Example for an integer

UPDATE [MyTable]
SET [MyColumn] = 0
WHERE [MyColumn] IS NULL;
ALTER TABLE [MyTable] ALTER COLUMN [MyColumn] INTEGER NOT NULL;

Example for a varchar

UPDATE [MyTable]
SET [MyColumn] = ''
WHERE [MyColumn] IS NULL;
ALTER TABLE [MyTable] ALTER COLUMN [MyColumn] VARCHAR(max) NOT NULL;

Altering a column: null to not null

Security Measure

Before, make sure you backup your database in case of you screw it all

To make short

This whole complex scenario made by SSMS is not necessary there is no problem doing a plain ALTER TABLE


Exporting Images from Sql server database into local drive

The bcp native datatype for a SQL image data type column includes a length prefix that won't be recognized by the rendering application. IMHO, bcp is not the right tool for this job and invoking xp_cmdshell in general is a dubious practice from a security perspective.
That being said, one way you could export the binary column contents to a file is with a PowerShell command invoked via xp_cmdshell:
EXEC xp_cmdshell 'PowerShell -Command "$con = New-Object Data.SqlClient.SqlConnection(''Data Source=.;Integrated Security=SSPI'');$con.Open();$cmd = New-Object Data.SqlClient.SqlCommand(''SET TEXTSIZE -1;SELECT column_of_type_image FROM yourdb.dbo.foobar WHERE id = 1;'', $con);[IO.File]::WriteAllBytes(''C:\exportdir\yourfile.pdf'', $cmd.ExecuteScalar());$con.Close()"'


Can i make a relationship between two relationships in the database?

Yes! You can make a new table which has foreign keys to both treatments and patient-records.

But be careful either in your model or in your code that you don't associate patient-records from a different patient with the patient associated with the treatment... using natural keys is one way to solve this:

treatments has a primary or unique key (doctor, patient) -- Doctor and Patient can be surrogate keys -- and both of these are foreign keys to doctor and patient, respectively.
patient-records has a primary or unique key (patient, medical-record) -- similarly, these are both foreign keys.
your new table has a primary or unique key (doctor, patient, medical-record) -- and there are two foreign keys: one on (doctor, patient) to treatments, and the other on (patient,medical-record) to medical-records.


dumping database with "compressed data is corrupt" errors

According to a grep in the sources, this error

  ERROR:  compressed data is corrupt

happens in case of a decompression failure of a LZ-compressed TOAST'ed value.

See http://doxygen.postgresql.org/tuptoaster_8c.html#abcb4cc32d19cd5f89e27aeb7e7369fa8

At the row-level storage, large values are stored as pointers to tables in the pg_toast schema containing the actual data.

How do I find and delete all corrupted rows in my table ?

Assuming you know what table it is (if not, iterate over the tables, starting with these with the largest columns), something that might work is:

COPY (select ctid,* FROM tablename ORDER BY ctid) TO '/path/to/file'

ctid is a pseudo-column indicating the physical location of a row in the form of (page,index in page), so this will dump the contents in their order in data files.

This COPY command should error out when it reaches the offending row, but at this point it should have streamed some output (to be confirmed in practice). The end of this output should indicate the ctid up to which the data is not corrupted.

Starting from this ctid, it should be possible to pinpoint the offending row with a dichotomic approach, running queries such as

SELECT ctid,length(t.*::text) FROM table t WHERE ctid>='(?,?)' ORDER BY ctid LIMIT 10

The point of length(t.*::text) is that it must pull all the decompressed data, as opposed to, e.g. count(*).

Once the corrupted row(s) are found, DELETE FROM table WHERE ctid='(?,?)' can be used to delete them.

Note that if VACUUM is working on this table, if might change the ctid of certain rows. Turning off autovacuum or doing all the work inside a transaction should avoid that potential problem.

EDIT: a web search on the error message reveals a blog post from Robert Berry suggesting a method that seems less fastidious than the above. Essentially, create this function:

create function chk(anyelement)
  returns bool
  language plpgsql as $f$
    declare t text;
    begin t := $1;
      return false;
      exception when others then return true;
    end;
  $f$;

and run all rows through it to find the ctid of the corrupted ones.

select ctid from table where chk(table);


You can use either n UPDATE statements, one for each column:

UPDATE table SET Col1 = NULL WHERE Col1 = '';
UPDATE table SET Col2 = NULL WHERE Col2 = '';
---
UPDATE table SET ColN = NULL WHERE ColN = '';

or one with CASE:

UPDATE table
SET Col1 = CASE WHEN Col1 = '' THEN NULL ELSE Col1 END,
    Col2 = CASE WHEN Col2 = '' THEN NULL ELSE Col2 END,
    ---
    ColN = CASE WHEN ColN = '' THEN NULL ELSE ColN END
WHERE
    Col1 = '' OR Col2 = '' OR ... OR ColN = '' ;      --- optional WHERE clause

The WHERE clause is not needed. But in case you run this again later, when there are very few rows with nulls, it will help to keep the transaction "smaller", updating only those rows and not the whole table.

Another equivalent, just shorter syntax, using the NULLIF() function:

UPDATE table
SET Col1 = NULLIF(Col1, ''),
    Col2 = NULLIF(Col2, ''),
    ---
    ColN = NULLIF(ColN, '')
WHERE
    Col1 = '' OR Col2 = '' OR ... OR ColN = '' ;      --- optional WHERE clause


is keeping idle / unused mysql databases bad?

The only aspects to be concerned about is Diskspace and Fragmentation.
MySQL's two main storage engines are MyISAM and InnoDB.
MyISAM
When accessing a MyISAM table, data are always read from the MyISAM table's .MYD file. When an index is being accessing, the index pages are stored in the MyISAM key cache (sized by key_buffer_size). This being the case, if you never access a MyISAM table, index pages are never loaded into memory.
InnoDB
When accessing an InnoDB table, data and index pages that are read are loaded into the InnoDB Buffer Pool (sized by innodb_buffer_pool_size). In this instance, if you never access an InnoDB table, data and index pages are never loaded into memory.
If you have innodb_file_per_table disabled, then all data and index pages would resides inside the system tablespace file ibdata1. Then, have a large ibdata1 with a lot of unused tables could introduce fragmentation that could potentially slow down access to InnoDB tables. If you are using MySQL 5.5/5.6, this should not be a consideration to worry about.
Summary
There are options and structures that can be used to preload index pages (and data pages for InnoDB). Notwithstanding, as long as you do not access those archive tables, you should be OK in all other aspects. To answer your questions, you cannot take a DB Offline and MySQL is engineered to work with what is accessed.


Yes you are correct, from SQL Server 2005 onwards with arrival of SQLOS( actually it was called UMS in SQl Server 2000) Wiindows OS never directly asks SQL Server to trim down its memory consuption when OS is seeing memory pressure. Instead it raises flags(MEMPHYSICAL_HIGH and MEMPHYSICAL_LOW). These flags are monitored by SQLOS dedicated threads ,that in turn sets a internal High Memory resource flag that tells the various caches they can allocate additional memory, or Low Memory resource flag that tells the various caches to try and reduce their memory allocations.

If memory pressure is severe apart from raising low memory notificaion OS  can page out SQL Server as well. There are certain scenarios where things would work differently regarding paging. No paging would happen if SQL Server service account has LPIM. In case of LPIM VirtualAlloc() function do not do major chunk of memory allocations instaed AWEAPI() does the major memory allocation and this is non pageable memory

The monitoring is continuous this is one of the tasks that SQLOS dedicated threads do. If I say dedicated they are bound to keep looking for any notification. I am not aware about schedule if any but whatever may be the schedule it would be definitely in seconds.The reaction time is quick and moment SQL server sees low memory notification it would immediately start trimming its memory consumption. But sometimes if external memory pressure is grave SQL Server might not get enough time to trim its memory consumption and free its caches and before it can complete it, OS might page out SQL server process.
Yes its expensive in terms that SQL Server has to clear its caches, trim down its memory consumption basically devoiding its processes from memory Or partially catering to there memory requests. The process of consuming and releasing memory involves cost so once SQL server consumes memory it does not gives it back(unless OS flags low memory notification) because it has to go all the way to process of asking memory from OS and the VirtualAlloc() function of OS would find a VAS region and map it to physical memory to actually provide memory to the process.

Edit:

  I'm wondering about the ramifications of not setting Max Server Memory.

Microsoft suggests that if you have DEDICATED SQL Server box solely running SQL Server database engine services leave max server memory to default. SQL Server can manage in better way. If you take my opinion this is not true if you have Windows server 2008 and 2005 having SQL Server 2005-2008 R2 installed on it because of various Bugs in Windows server release trimming SQL Server memory consumption. This holds true if you have latest Windows server 2012 having SQL Server 2012. But again this is as per my experience and scenario is limited.

What I suggest is its better to fix max server memory if you correctly set optimum value. Whatever version you are in please see answer given(By me and others) on This SE Thread and This Thread there is lot of evidence to say you should fix max server memory.

  If SQL Server "knows" the OS is low on memory immediately, and can return memory to the OS in a timely fashion, why not let it perform that task

That is good question but like I wrote before sometimes if memory crunch faced by OS is too severe SQL Server will respond to memory pressure but in that case before SQL Server gets enough time to clear cache it would be paged out by OS(because OS is in desperate need of memory) eventually slowing SQL Server process very much. Cases where OS can face severe memory crunch

Bug in OS
Third party driver installed leaking memory
Max server memory not set correctly and point 1 and 2 are true.
Someone start big file copy and any of the above 3 points are true.

In all above case problem was with OS and applications installed on it and SQL Server became victim.


I ended up figuring this one out.

Shrink a tablespace with DB2 9.5 is much more difficult.
The reduce and resize command exist but will only work if the high water mark is low.
You cannot use reduce or resize below the HWM size.

You need to use DB2DART -LHWM and have the database deactivated while doing so.


Problems with Insert( simple query - can't think something )

Assuming both Tblservicedesk & Tblservicedesk_temp having same columns. You can write update statement to update the three columns (FlagEmailNota, FlagEmailSLA, FlagEmailFechado ).

UPDATE tsd
SET
   tsd.FlagEmailNota = tsdt.FlagEmailNota ,
   tsd.FlagEmailSLA = tsdt.FlagEmailSLA,
   tsd.FlagEmailFechado = tsdt.FlagEmailFechado
FROM
   Tblservicedesk AS tsd
JOIN
  Tblservicedesk_temp AS tsdt ON tsd.ID_SOLICITAÇÃO = tsdt.ID_SOLICITAÇÃO

Since I don't have enough reputation to comment hence i'm posting this as an answer, if this is not what you are looking for please let me know.


Best way to migrate a database involved in bidirectional replication

Is this pretty straight-forward?

Yes and No .. depends on what is your downtime (maintenance window), database size or amount of data you are replicating (whole or subset of articles).

Script out current replication setup (both create and drop).

First make sure that you don't have any pending commands to replicate and no user connections while you are doing a cut-over to new server.

Below are some of the ways you can choose to ease your migration :

Easy and straight forward solution is to simply reset up replication. If the database is too big then you can reinitialize from backup as well followed by a snapshot and everything will be working fine.
Set up log-shipping of current publisher databases that you need to migrate to new server or even you can set up the new server as a subscriber to the old one. Then on the day of cutover, just bring the secondary (new server) database online (restore with recovery)  if log-shipping is used or just point your application to the new server and resetup replication.
A more tedious and possibly error prone solution (if you miss some details) is to backup the publication database and restore it to the new publisher server with the KEEP_REPLICATION flag enabled. Go to the subscriber servers and update the dbo.syssubscription tables with the new publishers information. Update the subscription tables with the new publisher information. Edit all the jobs on the distributor to point to the new publisher. Update the tables in the distributor to point to the new distributor.
Another way to do all of this is to stop SQL on the old server, copy all the system and user databases to the new SQL Server overwriting the system databases, then rename the new SQL Server so that it has the same name and IP as the old server. Then bring SQL on the new server online and everything should be working smooth.

Note:

By default, the generated scripts will have @sync_type = N'automatic' => the subscription will need initializing as a brand new subscription. Instead change it to @sync_type = N'none' or @sync_type = N'replication support only' => does not generate replication procs at subscriber.

Obviously, logins and users with appropriate permissions should be created before hand.

References :

Minimize Downtime When Moving to a New SQL Server Environment
SQL Server 2008 R2 Upgrade Technical Reference Guide


mysql 5.7 json filter and features products

For the first problem, AFAICT, there is no way to use SQL to project certain properties of a JSON object.  I think you will have to do that in the application layer.

For the second problem, how to search for products with a certain feature: I assume you have a table products with one row for each product.  The table contains a column of type JSON which contains the features of the product as you have shown.  If so, I think the following query should find the products where the value of the feature called "display" in English has the value of 5.5":

SELECT * FROM products
WHERE JSON_EXTRACT(feature,
    REPLACE(JSON_UNQUOTE(
        JSON_SEARCH(feature, 'one', 'display', NULL, '$.feature[*].en')),
        '.en', '.value')) = "5.5\"";

JSON_SEARCH will give you the path to the feature called 'display'.  To find the value of the same feature, we replace '.en' in the path with '.value' and use JSON_EXTRACT to look up the value at this path.  (JSON_UNQUOTE is needed to convert the JSON string from JSON_SEARCH to an SQL string to be passed to JSON_EXTRACT).


Update a valid column when another column has changed

UPDATE text2bid_users
SET valid_phone = CASE WHEN phone != '0000' THEN 0 else valid_phone END,
phone = '0000'
WHERE user_id = 1;

http://sqlfiddle.com/#!9/24784/1


Log shipping - Bulk logged recovery mode doesn't log bulk operations, yet it is allowed for Log Shipping config? Why?

"Bulk logged recovery mode doesn't log the bulk operations" - it's a wrong message.
Instead of writing information about adding each row to the log file it writes full pages which were affected by a bulk logged operation to the log backup.

You could read more:
Bulk Changed Map (BCM): The Minimally Logged Advantage


How is the MongoDB backup snapshot transferred from Ops Manager to file system store?

As per MongoDB documentation here Ops Manager creates backups of MongoDB replica sets and sharded clusters. After an initial sync, Ops Manager tails the operation log (oplog) to provide a continuous backup with point-in-time recovery of replica sets and consistent snapshots of sharded clusters.

Why doesn’t the Backup feature support standalone deployments?

After an initial sync of your data, Ops Manager copies data from the oplog to provide a continuous backup with point-in-time recovery. Ops Manager does not support backup of standalone servers, which do not have an oplog. To support backup with a single mongod instance, you can run a one-member replica set.

How can I use Backup if Backup jobs fail to bind?

The most common reason that jobs fail to bind to a Backup Daemon is because no daemon has space for a local copy of the backed up replica set.

To increase capacity so that the backup job can bind, you can:

add an additional backup daemon.
increase the size of the file system that holds the Head
directory.
move the Head database data to a new volume with more space, and
create a symlink or configure the file system mount points so that
the daemon can access the data using the original path.


Insert 4 tinyint values into varbinary(4)

@stringIP = '192.168.0.3'
select
    cast(CAST(PARSENAME(@stringIP, 4) as tinyint) as varbinary(1)) +
    cast(CAST(PARSENAME(@stringIP, 3) as tinyint) as varbinary(1)) +
    cast(CAST(PARSENAME(@stringIP, 2) as tinyint) as varbinary(1)) +
    cast(CAST(PARSENAME(@stringIP, 1) as tinyint) as varbinary(1))


How can the TNS connect identifier passed to SQL*Plus be accessed from the run script for reconnecting?

Sure, pass it as a parameter too.

sqlplus MY_USER/pswd@TNSID @script.sql TNSID par2 par3

Then you can use the below in the script:

connect MY_USER2/pswd@&1


Get records which met SELECT condition over time and range them by period of time when condition was met

No, from what you describe, I think that you'll wind up having to track these using some secondary tables.

It sounds like you want to know how long record X stayed in a given status, whether that's the latest status or not. From your description, I'm not sure if records can wind up in a given status multiple times, or if hitting any status is a one-time event. That would impact how this needs to be set up.

It also sounds like there are statuses that matter, and others that do not matter. Hopefully, if there are a large number of records, most records should wind up in statuses that you're not interested in.

So, you'd want to do something like the following:

Create a table to hold current status info (let's call that StatusDuration_Active), and a parallel table to keep historical info (StatusDuration_History). Let's assume it will have at least the following fields:

a unique ID for each of the above tables (and I'd make this an IDENTITY column). The _History ID will be unconnected to the _Active ID (that is to say, when a record moves to _History, it will get a new ID). Unique IDs are useful in case you need to delete records, among other things - and you will want to delete records from _History periodically.
record_id - this will hold the primary key value from the table you're tracking. It can be made a foreign key, but might be useful without being one. Presumably, you'd want to know about a long duration in a given status, even if the record was deleted afterwards.
status - the status value from the table you're tracking.
first_seen - datetime (or datetime2) column holding the time when the records was seen in this status, when it hadn't been in this status at the previous check.
last_seen - same datatype as previous; the last check when the record was still in this status.
times_seen - integer value showing the number of consecutive times your process saw this record at this status. Useful for a cross-check against the first and last times, in case your process that checks these goes offline at some point (accidentally or on purpose); you could add code to remove or warn if times_seen < (DATEDIFF(minute, first_seen, last_seen) - 10), for instance.

Create a procedure that:

Create a temp table as follows:

CREATE TABLE #StatusDuration
     ( tmp_id INT IDENTITY(1,1) NOT NULL
      ,record_id <datatype>
      ,status <datatype>
      ,first_seen datetime
      ,times_seen INT DEFAULT 0
     );

where record_id will hold the primary key of the table you're tracking, and status will hold the current status from that table.
Selects the current time into a variable (@CurTime);
Selects all records currently in a status you're interested in into #StatusDuration; set first_seen to @CurTime;
Copies all records from StatusDuration_Active where the record ID and status are not in the temp table into StatusDuration_History; these rows should be copied exactly as is;
Updates all records in #StatusDuration that are in StatusDuration_Active with the same record_id and status, updating the temp table's first_seen and times_seen with the values from StatusDuration_Active.
TRUNCATE TABLE StatusDuration_Active
Insert all records in #StatusDuration into StatusDuration_Active; set last_seen to @CurTime, and times_seen to times_seen + 1

Set up a job to run the procedure every minute.

Depending on the number of records returned, you could delete records from StatusDuration_Active that got moved to _History, update records that are already present, and insert new records, instead of the TRUNCATE TABLE method. However, if there are a large number of records to be deleted, this might be the fastest method to use.

You'll need to confirm that all this runs within a minute (or schedule it to run less frequently). I'd expect it to be OK, but I'm not sure how many records you'll have.

Note that the TRUNCATE TABLE on StatusDuration_Active will reset the IDENTITY value back to 1 (or whatever you started with) - one of the reasons the IDs shouldn't track over). An ID column on #StatusDuration may actually be unnecessary; I include them wherever possible, because sometimes they are necessary.

As noted, you'll want to clear the oldest rows (optionally keeping the most interesting ones, cases where the statuses remained at a particular duration for longer than normal) from StatusDuration_History, or it will eat up a lot of space, and will take longer and longer to query (and, depending on how you index it, possibly longer and longer to insert into, which may be more of a concern).

When reporting, you may want to use the WITH (NOLOCK) table hint, as both tables will be updated once a minute, and you don't want to interfere with that. If your queries run in a few seconds, and your procedure does as well, then no major worries. If you do use the table hint, note that it'll be possible to hit a row that's in _Active, and to hit the same row because it's been copied to _History. I'd keep the possibility in mind; you could use UNION instead of UNION ALL to combine the data from the two tables to work around this, but I'd not worry about it unless you see it happening and notably affecting your results.


How to display queries of separate tables in adjacent columns?

You are basically building two tables, which then has to be joined on the date column.  In your query you miss the join condition.

One clear way of solving this is building the two result sets as CTEs (WITH queries):

WITH pc AS (SELECT planning_day, sum(sot_contribution) AS minutes_planned
            FROM planning_constraints
            ...),
      p AS (SELECT planning_day, sum(sot_allowed) AS minutes_allowed
            FROM planning
            ...)
SELECT planning_day,
       COALESCE(minutes_allowed, interval '0 minute') AS allowed,
       COALESCE(minutes_planned, interval '0 minute') AS planned,
       COALESCE(minutes_allowed, interval '0 minute') -
           COALESCE(minutes_planned, interval '0 minute') AS diff
  FROM pc FULL OUTER JOIN p USING (planning_day)
 ORDER BY planning_day;

FULL OUTER JOIN means if there is a day specified in either one of the result sets, there will be a line for that day.  If this is not what you want, figure out which type of join you need.

Notes:

planning_day does not need to be COALESCEd, as the USING clause takes care of this
also, there is no need to use a GROUP BY in the outer query, as planning_day is already the key of both source tables
the ORDER BY can be now removed from both subqueries


SQL Server Encryption with "localhost" certificate

Just for fun, I tried to do this with a wildcard certificate. The fact that anyone can do this with a self-signed certificate, for a local application, being forced to use SSL when shared memory would work much more efficiently, is kind of bogus. This adds nothing to the security of the environment.
Create A New Cert
New-SelfSignedCertificate -Subject "CN=*,DC=Dublin,DC=local" -KeySpec KeyExchange
-KeyFriendlyName "SQLWildcard" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1"
-CertStoreLocation "Cert:\LocalMachine\My"

Note that a FQDN of localhost and a short name of localhost both seemed to work as well. It still doesn't make anything any more secure. If anything, forcing it through the TCP stack adds in more places for filter drivers and other items to inspect and attack.
Give Permissions To The Private Key
Here I am giving the service account access to the private keys. Since most of the time SQL Server Express Edition is running as Local* account you can skip this step if you've put the cert in the local machine's MY location. Put this in for completeness.

Setting The Instance To Use The Certificate For SSL
This won't show up in the certificate picker, because the issued-to and subject don't actually match the server. Plus, if you have 3000+ instances to do this on, chances are you aren't going to do it through the GUI.
The location of the registry key is: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\*InstanceID*\MSSQLServer\SuperSocketNetLib with the value name of Certificate which takes the thumbprint of the certificate.

Double Checking
If everything went well, you'll see that the certificate thumbprint was successfully loaded for SQL Server on the next restart of the service.
The certificate [Cert Hash(sha1) "F830C76FDDAC472807AF46A1C44450BBA56443D7"]
was successfully loaded for encryption.

If something went poorly, you'll see that there was a failure to initialize SSL. It will be followed by a fairly verbose output of the error code and status code which will help you in determining why the failure occurred.
Unable to load user-specified certificate [Cert Hash(sha1) "ED3D2F4360780AF71ABCE83A1C3A6F3BE13E1B96"].
The server will not accept a connection.
You should verify that the certificate is correctly installed.
See "Configuring Certificate for Use by SSL" in Books Online.

Alternate DB technologies that are out-of-process will have the same fate. You can use localhost as I explained above.


Do I need a "downtime" to update a stored procedure? (Due to "definition changed" error)

Yes it is true that you should not change an actively running stored procedure, because SQL Server doesn't like it.

As for taking downtime, well yeah, you shouldn't be making changes to frequently-used stored procedures in a busy production environment during operating hours as a best-practice.


HeidiSQL or DBeaver manage the SSH connection and not ODBC. You'll need to create your SSH connection with a port forward first.
Then test if you can connect a mysql client to the local ssh forwarded port.
Then attempt to create your linked server using ODBC to local end of the ssh forwarded port.


Is this proper way to link parent and child tables with logical data instead of parent and child using primary and foreign key

If you want to use relational principles in database design then certainly not.  In a relational design, each entity type in the business domain from the conceptual model is represented by an R-Table in the logical model. The R-Table is a table which, when following a specific discipline, can acquire the properties of a mathematical relational which enable the R-Table to be operated upon with logic statements of arbitrary complexity in an algebraic fashion with guaranteed results.  Entity types at the conceptual level share a set of properties called attributes which describe them.  One or more of these properties is defined to uniquely identify each entity of the class in order to tell them apart.  These are mapped to the R-Table at the logical level as columns, and each Entity mapped to a row.  Each row then represents a predicate, and when values for the columns are entered the predicate is instantiated to become a proposition.

A key component at the conceptual level are the business rules which define exactly what attribute values constitute a true proposition.  These map to constraints in the logical model, which is a key benefit the relational model provides.  When created, the constraints enable the DBMS, which can only manipulate symbols, to effectively keep the data entered consistent with the truth of the real world the data is meant to represent.

So in a nutshell, if you do not declare the constraints - such as a product price must be for one and only one product - to the DBMS, the DBMS will not be able to ensure the data is consistent with the real world.  All bets are then off as to correct query results.

Relational design is not easy however.  It requires detailed analysis of the business domain to be modeled, a full understanding of the business rules that define consistent data, and a careful mapping to the logical level of R-Tables.  All too often what we see in practice is what I call file based design, where tables in the SQL DBMS are used to represent files whose content is based upon totally ad-hoc design considerations.  A clue that the 2 tables in question - product and product details - were designed using file based design is in the name of the product details table.  A file holds details about something.  A Product Details is not an entity type whose class of entities share common properties.  This is also evidenced by the fact that every one of those columns in that table are defined as NULL.

If you want pro's and con's of a file based design I am not the right person to elaborate, as those pro's and con's will be ad-hoc and totally based upon your specific circumstances - such as the technology and the work load in play.

A great primer on relational design and its power can be found in Fabian Pascal's Practical Database Foundation Series.  Fabian lays out the basics for every day practitioners in easy to understand language.  He covers everything I summerized here with clear explanations plus a whole lot more.  I highly recommend it.


data driven report --send max 2 report when records return instead of sending every 10 min

I would create a table

CREATE TABLE ReportRuns
( RunDate Date, RunCounter Int)

The RunDate will contain the Date Portion only.

Everytime you run the report and there is data returning, you Update/Insert into this Table and increment the counter. If counter is already at 2, do nothing and don't send the report. if a record for the current day doesn't exist or does exist and the counter is 1, then update the counter to 2 and send the report.
Next time it runs, the counter will be at 2 and the report won't be sent.

HTH.


Durability and other limitations in mongoDB?

You seem to be using the 32bit version of mongoDB.

It's a miracle you actually got to 4.05GB, because the 32bit version only supports up to 2GB of data.

Any serious MongoDB deployment should be on a 64bit server with a 64bit operating system running a 64bit build of MongoDB. The 64bit version of MongoDB theoretically allows database of almost unlimited size. In reality, you are of course limited to the storage capactity of your server, but MongoDB can always scale horizontally by creating a shard of multiple servers.

Running 64bit will also allow the database to use journaling (the 32bit version also allows journaling in theory, but it's disabled by default because it further limits the amount of data you can store). With journaling enabled, MongoDB is far less likely to require a repair after a sudden crash.


Switching from 2 large collections to multiple small ones in MongoDB 3.4

Multiple small collections will take more space than few big collections.
Depending on amount of memory, big collections indexes are more effective than large amount of indexes of small collections.


What are the consequences, positive or negative, to having a surrogate primary key for a table which already has a guaranteed unique column?

I agree a lot with comments from @MDCCL:

  You should analyze each particular case —that is, every particular table within its whole context, that is, the entire database structure— to define if it requires the appending of a system-generated surrogate column, as such an artifact is an additional aspect that requires its particular administration. Consistency with the way the rest of the tables look is irrelevant, from the logical and physical points of view

A few pointers from my side (the ones that come to my mind at the moment, and that probably are not all there is to it)

Negative consequences:

You need more space for everything: each row is wider, and you have an extra index to maintain. Every write will take (a little bit) more time, and so will every read, because you use more space to read and write the same real information. Let's say you have a log of the readings of several thermometers, one per second. You have one table thermometers (thermometer_id PK, ...etc...) and one readings (thermometer_id, reading_timestamp, value_read). Your readings table has a natural key: (thermometer_id, reading_timestamp), because you cannot have two different readings at the same time. I would normally make this my PK, and forget about surrogates.
Using surrogate keys tend to obscure the real structure and meaning of your data. For instance, imagine you have a tables persons,  books and books_liked_by_persons. This last table is a m x n table, and most of the times contains only (person_id, book_id) pairs. Adding a third id is in such cases most probably meaningless and noisy.

Positive consequences:

In some very specific cases, you may want to have different tables with share one part of their structure (let's say, for instance, you want some of them to have always a country_code, and xxx_id) so that some application is easier to develop because the same pattern can be used to process different tables. [This tends to be rather uncommon, AFAIK, but is also possible.]
Using a surrogate key, even if there are some other alternative keys might help you save space (and probably, time). Imagine your natural key is a PGP key (note 1). It takes a char(800) (approx.) to store it. Imagine you have several tables that reference this one. It will save (a lot of) space all around to have a surrogate integer key (8 or 16 bytes) on the original table, because the referencing tables will store 8 (or 16) bytes instead of 800. The accounting needs to be done for each specific case.
If you ever have to edit your data "by hand" (and, some day, you probably will), you don't want to look for keys which are really long. You prefer surrogates in that case.
Some databases (not PostgreSQL, at least as of now) will cluster the data of your tables using your primary key as a clustering index (or can do it by default unless you explicitly state the contrary). In such cases, it is normally advisable to have an always incrementing primary key; to avoid fragmentation.

So: every case needs analyzing, weighting and deciding. Sometimes consistency may be confused by flying on autopilot.

Side note: There are several different naming conventions. I prefer the one where id is never used, and a persons table has a person_id column if needed.

Note 1: A PGP key looks more or less like:

mQENBFiBGbcBCADBAe1p70ZPGwM6gmAAFTd13gv68rOUQEqlhddjkJZoiKs8SJuX
m4Hpu29+4msQ91071R2yGZ+iydO2laRqWD1jFmF1qOTWiwYkrxYc288/XLbGi7NY
gz9CPKlaH8N4VJBPC6eoRJgpUawBygERf+YFxBEXipWrZbusFrnHCilGGuOwIDAc
UmYf7XNFZrg4+i+QEkBqWnBtd4Q7tkdTTywFDMZ3H+fwrDp+8M3zUwi10PCxJbQd
JkKzXDFemaOHiDxWKNRg7CFyCBRq5K6mfXZuPeAtVOxFMFRnvyJ75HWv4Ss5n40b
tpL/kmSQ9cHgl0nWHS+LMF55zZsAKToNaW6FABEBAAG0FXNvbWV0aGluZ0Bub3do
ZXJlLmNvbYkBHAQQAQIABgUCWIEZtwAKCRDigzJ94dxj1hujCACFt22wZD5yn5/J
3cGLonszqgDRMNzmkdlJjiZi+2fC51z2mkQcy3rlzrQX9K1Mu3TLxPfwyMMiZkut
trleZI4KyAD1hQRm4hQ4f+xx++xOqnMh5c7KsE6AwnGxeDgLy8hD3IJCmrDFwZ93
4XgZ+iXEzFxMcwmLeJ7xg7re4NTlovpIj1QetXqfEHydHQ1AJCqBjGSs805DBHGO
nKAwBXuPI67Kcry+HIhn4ZaIXmXLGvXStJAz2bX8o/nQaVoC/YOyUTMZadR3jtdf
ka5OeyvudCiKTNL3I1oDbhFFfGEhF2w66wp8acJ4z5bYkaQ5RFpEMI1ISfSn7Ypg
FZln1sTo
=RYbd


Incorrect definition of table mysql.event after migrating database to mariadb

On centos 7:

  mysql  Ver 15.1 Distrib 5.5.50-MariaDB, for Linux (x86_64) using readline 5.1

mysql_upgrade -u root -p

This issue is fixed.


Extract JSON array of numbers from JSON array of objects

You need to first unnest the array elements, and then aggregate back each value:

select id,
       (select jsonb_agg(t -> 'a') from jsonb_array_elements(record) as x(t)) as record
from the_table;

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


A stop and start of the SQL Agent fixed the issue.


SQL Datetime / datetimeoffset(2) comparison & implicit cast wierdness

Looks like the opposit is true: the implicit conversion  takes the offset into the equasion, but the cast/convert functions do not.

DECLARE @dt  DATETIME          = '2014-07-07 02:49:33.000';
DECLARE @dto DATETIMEOFFSET(2) = '2014-07-07 09:49:33.000 +07:00';

PRINT CASE WHEN @dt = @dto THEN 'Equals matches'
           ELSE 'Equals does not match'
      END

PRINT CASE WHEN @dt = CAST(@dto AS DATETIME) THEN 'Cast matches'
           ELSE 'Cast does not match'
      END

PRINT CASE WHEN @dt = CONVERT(DATETIME,@dto) THEN 'Convert matches'
           ELSE 'Convert does not match'
      END

Comparing this (deducted 7 hours from @dt) results in:

Equals matches
Cast does not match
Convert does not match

Did some more investigation, and stumbled on this article.

"When you convert from datetime2 or datetimeoffset to date, there is no rounding and the date part is extracted explicitly.
For any implicit conversion from datetimeoffset to date, time, datetime2, datetime, or smalldatetime, conversion is based on the local date and time value."

So when you want to treat  '2014-07-07 09:49:33.000' and '2014-07-07 09:49:33.000 +07:00' as equal, your only option is to make an explicit conversion via cast or convert.
Since implicit conversions would only work when your server's timezone offset happens to be the same as the offset specified in @dto.


I suppose this graph is coming out of SQL Profiler. Profiler behaves this way. it cannot display multi-victim deadlocks correctly. If you pick the very same deadlock from the system_health extended events session (no extra trace necessary as this one is running by default), you can get the correct visual within Extended Events Viewer.
Also you can use this link to get a free deadlock collector and parser which also gets that information and the graphs: https://sqldeadlockcollector.codeplex.com/

as requested: yes, I am the person who created the deadlock collector which is free for anyone to download&use


Newly created jobs need to be set for failure notifications automatically

I'd suggest checking out this post from Greg Larsen - Automating SQL Server Agent Notification.  You could manually create a SQL Agent job that runs periodically and searches for enabled jobs that don't have any notifications defined.

Here's an example of a dynamic SQL query you could put in a SQL Agent job to find SQL agent jobs without notifications and dynamically update them to have notifications.  Make sure you uncomment the EXEC when you put it in a SQL Agent job.  You will also have to modify the variable @Operator to specify the operator you want notified.

USE [msdb]
GO

SET NOCOUNT ON

DECLARE @ExecStmt VARCHAR(4000)
DECLARE @Operator VARCHAR(50) = 'DBA' -- place your operator name here

SELECT @ExecStmt = STUFF((
            SELECT ';' + 'EXEC sp_update_job @job_name = ''' + j.[name] + ''', @notify_email_operator_name = ''' + @Operator + ''', @notify_level_email = 2' -- 1=On Success, 2=On Faulure,3=always
            FROM dbo.sysjobs j
            WHERE j.enabled = 1
                AND j.notify_level_email <> 1
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
PRINT @ExecStmt
--exec (@ExecStmt)
GO


SQL: How to ask for multiple counts from the same table within a join

MySQL does not have FULL join implemented. You are likely to need only a LEFT join (from stores to tasks):

FROM stores
     LEFT JOIN tasks
     ON tasks.store_id = stores.id

You can't have WHERE or WHEN in a SELECT expression like you try. There is a FILTER feature in standard SQL but that has not been implemented either in MySQL. There are however CASE .. WHEN expressions you can use just fine. Example:

COUNT (
    CASE WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
              AND tasks.deleted_at IS NULL
        THEN 1 ELSE NULL END )
    AS TaskTotal ,

So the query can be written:

SELECT
    stores.caption,
    COUNT (
        CASE WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
                  AND tasks.deleted_at IS NULL
            THEN 1 ELSE NULL END )
        AS TaskTotal ,
    COUNT (
        CASE WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
                  AND tasks.deleted_at IS NULL
                  AND tasks.completed_at IS NULL
            THEN 1 ELSE NULL END )
        AS TaskOutstanding ,
    COUNT (
        CASE WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
                  AND tasks.deleted_at IS NULL
                  AND tasks.completed_at IS NULL
                  AND tasks.deadline < '2019-05-16'
            THEN 1 ELSE NULL END )
        AS TaskOverdue
FROM
    stores
    LEFT JOIN tasks
    ON tasks.store_id = stores.id
GROUP BY
    stores.caption ;

Now, since all 3 expression have a common condition you can simplify by moving that condition to the ON clause - assuming that you don't want any more columns without it.

We can also add table aliases and removed the redundant ELSE NULL from the case expressions:

SELECT
    s.caption,
    COUNT ( t.store_id )
        AS TaskTotal ,
    COUNT (
        CASE WHEN t.completed_at IS NULL
            THEN t.store_id END )
        AS TaskOutstanding ,
    COUNT (
        CASE WHEN t.completed_at IS NULL
                  AND t.deadline < '2019-05-16'
            THEN t.store_id END )
        AS TaskOverdue
FROM
    stores AS s
    LEFT JOIN tasks AS t
    ON t.store_id = s.id
    AND t.created_at BETWEEN '2019-04-01' AND '2019-04-30'
    AND t.deleted_at IS NULL
GROUP BY
    s.caption ;


Try the following:

UPDATE tests AS t JOIN testusers AS tu ON t.TestNumber= tu.TestNumber
SET t.InactiveTestSlotBitwise = (t.InactiveTestSlotBitwise | (1 << tu.UserSlot))
WHERE tu.UserId=25 AND tu.UserSlot >= 0;


MySQL Partition by Substring of a Column

If the columnName is an INT, you can partition it as follows

CREATE TABLE mydata
(
    id INT NOT NULL AUTO_INCREMENT,
    myColumn INT NOT NULL,
    PRIMARY KEY (id,myColumn),
    KEY (myColumn)
)
PARTITION BY RANGE (myColumn) (
    PARTITION p000 VALUES LESS THAN (010),
    PARTITION p010 VALUES LESS THAN (020),
    PARTITION p020 VALUES LESS THAN (030),
    PARTITION p030 VALUES LESS THAN (040),
    PARTITION p040 VALUES LESS THAN (050),
    PARTITION p050 VALUES LESS THAN (060),
    PARTITION p060 VALUES LESS THAN (070),
    PARTITION p070 VALUES LESS THAN (080),
    PARTITION p080 VALUES LESS THAN (090),
    PARTITION p090 VALUES LESS THAN (100),
    PARTITION p100 VALUES LESS THAN (110),
    PARTITION p110 VALUES LESS THAN (120),
    PARTITION p120 VALUES LESS THAN (130),
    PARTITION p130 VALUES LESS THAN (140),
    PARTITION p140 VALUES LESS THAN (150),
    PARTITION p150 VALUES LESS THAN (160),
    PARTITION p160 VALUES LESS THAN (170),
    PARTITION p170 VALUES LESS THAN (180),
    PARTITION p180 VALUES LESS THAN (190),
    PARTITION p190 VALUES LESS THAN (200),
    PARTITION p200 VALUES LESS THAN (210),
    PARTITION p210 VALUES LESS THAN (220),
    PARTITION p220 VALUES LESS THAN (230),
    PARTITION p230 VALUES LESS THAN (240),
    PARTITION p240 VALUES LESS THAN (250),
    PARTITION p250 VALUES LESS THAN (260),
    PARTITION p260 VALUES LESS THAN (270),
    PARTITION p270 VALUES LESS THAN (280),
    PARTITION p280 VALUES LESS THAN (290),
    PARTITION p290 VALUES LESS THAN (300),
    PARTITION p300 VALUES LESS THAN (310),
    PARTITION p310 VALUES LESS THAN (320),
    PARTITION p320 VALUES LESS THAN (330),
    PARTITION p330 VALUES LESS THAN (340),
    PARTITION p340 VALUES LESS THAN (350),
    PARTITION p350 VALUES LESS THAN (360),
    PARTITION p360 VALUES LESS THAN (370),
    PARTITION p370 VALUES LESS THAN (380),
    PARTITION p380 VALUES LESS THAN (390),
    PARTITION p390 VALUES LESS THAN (400),
    PARTITION p400 VALUES LESS THAN (410),
    PARTITION p410 VALUES LESS THAN (420),
    PARTITION p420 VALUES LESS THAN (430),
    PARTITION p430 VALUES LESS THAN (440),
    PARTITION p440 VALUES LESS THAN (450),
    PARTITION p450 VALUES LESS THAN (460),
    PARTITION p460 VALUES LESS THAN (470),
    PARTITION p470 VALUES LESS THAN (480),
    PARTITION p480 VALUES LESS THAN (490),
    PARTITION p490 VALUES LESS THAN (500),
    PARTITION p500 VALUES LESS THAN (510),
    PARTITION p510 VALUES LESS THAN (520),
    PARTITION p520 VALUES LESS THAN (530),
    PARTITION p530 VALUES LESS THAN (540),
    PARTITION p540 VALUES LESS THAN (550),
    PARTITION p550 VALUES LESS THAN (560),
    PARTITION p560 VALUES LESS THAN (570),
    PARTITION p570 VALUES LESS THAN (580),
    PARTITION p580 VALUES LESS THAN (590),
    PARTITION p590 VALUES LESS THAN (600),
    PARTITION p600 VALUES LESS THAN (610),
    PARTITION p610 VALUES LESS THAN (620),
    PARTITION p620 VALUES LESS THAN (630),
    PARTITION p630 VALUES LESS THAN (640),
    PARTITION p640 VALUES LESS THAN (650),
    PARTITION p650 VALUES LESS THAN (660),
    PARTITION p660 VALUES LESS THAN (670),
    PARTITION p670 VALUES LESS THAN (680),
    PARTITION p680 VALUES LESS THAN (690),
    PARTITION p690 VALUES LESS THAN (700),
    PARTITION p700 VALUES LESS THAN (710),
    PARTITION p710 VALUES LESS THAN (720),
    PARTITION p720 VALUES LESS THAN (730),
    PARTITION p730 VALUES LESS THAN (740),
    PARTITION p740 VALUES LESS THAN (750),
    PARTITION p750 VALUES LESS THAN (760),
    PARTITION p760 VALUES LESS THAN (770),
    PARTITION p770 VALUES LESS THAN (780),
    PARTITION p780 VALUES LESS THAN (790),
    PARTITION p790 VALUES LESS THAN (800),
    PARTITION p800 VALUES LESS THAN (810),
    PARTITION p810 VALUES LESS THAN (820),
    PARTITION p820 VALUES LESS THAN (830),
    PARTITION p830 VALUES LESS THAN (840),
    PARTITION p840 VALUES LESS THAN (850),
    PARTITION p850 VALUES LESS THAN (860),
    PARTITION p860 VALUES LESS THAN (870),
    PARTITION p870 VALUES LESS THAN (880),
    PARTITION p880 VALUES LESS THAN (890),
    PARTITION p890 VALUES LESS THAN (900),
    PARTITION p900 VALUES LESS THAN (910),
    PARTITION p910 VALUES LESS THAN (920),
    PARTITION p920 VALUES LESS THAN (930),
    PARTITION p930 VALUES LESS THAN (940),
    PARTITION p940 VALUES LESS THAN (950),
    PARTITION p950 VALUES LESS THAN (960),
    PARTITION p960 VALUES LESS THAN (970),
    PARTITION p970 VALUES LESS THAN (980),
    PARTITION p980 VALUES LESS THAN (990),
    PARTITION p990 VALUES LESS THAN MAXVALUE
);

Why partition it this way ??? If it is an INT, here is how the data is grouped

Partition p200 will hold rows with myColumn 200 - 209
Partition p210 will hold rows with myColumn 210 - 219
Partition p220 will hold rows with myColumn 220 - 229

Partitioning works with DATEs and INTs, not CHARs or VARCHARs


JOIN operations are not as expensive as you might expect. Many times the benefit of normalization will far outweigh the "costs" of joining the tables, specifically if you have "common" coordinates that are used by more than one destination/source.

I would likely model this using two tables, one for coordinates, and one to describe the points (sources and destinations).

For instance:

CREATE TABLE dbo.Coords
(
    CoordinateID INT NOT NULL
        CONSTRAINT PK_Coords
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , x INT NOT NULL
    , y INT NOT NULL
);

CREATE TABLE dbo.Points
(
    PointID INT NOT NULL
        CONSTRAINT PK_Points
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CoordinateID INT NOT NULL
        CONSTRAINT FK_Points_CoordinateID
        FOREIGN KEY
        REFERENCES dbo.Coords(CoordinateID)
    , RelativePointID INT NULL
        CONSTRAINT FK_Points_RelativePointID
        FOREIGN KEY REFERENCES dbo.Points(PointID)
);

INSERT INTO dbo.Coords (x, y)
VALUES (1, 2)
    , (1, 3)
    , (1, 4);

INSERT INTO dbo.Points (CoordinateID, RelativePointID)
VALUES (1, NULL);

INSERT INTO dbo.Points (CoordinateID, RelativePointID)
VALUES (2, 1);

INSERT INTO dbo.Points (CoordinateID, RelativePointID)
VALUES (3, 2);

SELECT SrcX = src_c.x
    , SrcY = src_c.y
    , DestX = dest_c.x
    , DestY = dest_c.y
FROM dbo.Points src
    INNER JOIN dbo.Coords src_c ON src.CoordinateID = src_c.CoordinateID
    LEFT JOIN dbo.Points dest ON src.RelativePointID = dest.PointID
    INNER JOIN dbo.Coords dest_c ON dest.CoordinateID = dest_c.CoordinateID;

Results:

This design allows any point to serve as the destination for any other source point, effectively allowing you to create a map of directions from one point to any other.