If you wanted only the last time_end value per (app, env), you could just use GROUP BY:
SELECT
  app,
  env,
  MAX(time_end) AS last_time_end
FROM
  the_table
WHERE
  status = 'finished'
GROUP BY
  app,
  env
;

However, you want in each case the entire row where time_end is last. That is a classic "greatest N per group" problem. One way to solve it is in two steps: first get the last values themselves, then join them back to the original table and get the corresponding complete rows. The query above, therefore, is the first step. Use it as a derived table and join it back to the_table to get the final output:
SELECT
  t.*
FROM
  the_table AS t
  INNER JOIN
  (
    SELECT
      app,
      env,
      MAX(time_end) AS last_time_end
    FROM
      the_table
    WHERE
      status = 'finished'
    GROUP BY
      app,
      env
  ) AS s ON t.app = s.app
        AND t.env = s.env
        AND t.time_end = s.last_time_end
;

Other database products support more advanced SQL syntax – specifically, window functions – that allows you to solve this category of problems without using a join, thus offering better performance. MySQL does not support window functions (yet?) but there is a way to work around that limitation using variables.
Specifically, you would use variables to rank rows within each (app, env) group based on the time_end value and then filter the rows on the ranking value:
SELECT
  derived.*
FROM
  (
    SELECT
      @rank := (app = @app AND env = @env) * @rank + 1 AS rank,
      ID,
      @app := app AS app,
      @env := env AS env,
      time_end
    FROM
      (SELECT @app := '', @env := '', @rank := 0) AS x,
      the_table
    WHERE
      status = 'finished'
    ORDER BY
      app ASC,
      env ASC,
      time_end DESC
  ) AS derived
WHERE
  derived.rank = 1
;

The query works like this:

The rows are sorted in such a way that identical (app, env) rows are arranged in groups, the last time_end row being the first row in each group.

Each row is then assigned a ranking value with the help of variables. Variables @app and @env hold the previous row's values of app and env and take part in calculating the @rank value: if the current row's app and env match the previous row's values, the @rank value is increased by 1, otherwise it is set to 1.
Thus, the first row in each group always gets the value of 1, because the previous row belongs to a different group or, in the case of the very first group, is simply non-existent (@app and @env in that case each hold the initial value, an empty string).

The results are used as a derived table and are filtered on the ranking value: only the rows with the ranking value of 1 are returned (they hold the last time_end value in each group).

My only issue with this method is that it is very MySQL-specific, which, in my opinion, is worth keeping in mind. No other RDBMS supports this syntax (unless that product is a MySQL fork, like MariaDB, for instance).
Both methods shown can be found and played with in this live demo at Rextester.


Passing NULL xml value to a certain CLR stored function crashes it when called in a certain way

Overall, no, you did not do anything wrong with your .NET code. There is a minor technical issue, but we will get to that in a moment.

I was able to reproduce this (both error and non-error scenarios) in SQL Server 2008 R2 RTM. At first, in the "non-error" scenario, I was getting another error for:

  specified cast is invalid

This error was a result of the query passing back an INT and the C# code attempting to cast it to a bool. I changed the query to be:

... then CONVERT(BIT, 1) else CONVERT(BIT, 0) end ...

That forces the datatype of the value to be returned to be something that can be cast into bool. This was the "minor error" that I was referring to, and I consider it "minor" and not really an error because I suspect your code originally attempted to cast into int and returned a SqlInt32 since you claim that passing in an empty string allowed it to return successfully. There is no way this would ever return successfully, even with an empty string instead of NULL for the input value, if your code was attempting to cast to bool and return SqlBoolean. And yes, I could have changed the cast to be (int) and the return type to be SqlInt32, but this was less of a structural change.

I tested several types of changes: removing the execution, removing the @d parameter and hard-coding the null into the query but still having the SqlParameter defined, removing the @d parameter and the SqlParameter, etc. What it finally came down to is this: merely the existence of a SqlParameter of a LOB type (i.e. XML, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) ), even if unused in the query, caused the "severe error" when passing in a NULL. However, if any of those MAX types were changed to be their maximum non-MAX lengths (i.e. 8000, 4000, and 8000 respectively), then there was no error when passing in a NULL. Hence the following reduced code should be enough to reproduce the error when passing in a NULL to the SQLCLR scalar function:

using (var cmd = new SqlCommand(@"SELECT 1;", c))
{
    var p = cmd.Parameters.Add("@d", SqlDbType.NVarChar, (int)SqlMetaData.Max);

    cmd.ExecuteScalar();
}

Now, I was not able to reproduce the error with the original code in SQL Server 2012 SP3 or SQL Server 2014 SP1 :-). Therefore, I suspect that this was an issue with CLR 2.0 and/or the .NET Framework versions associated with CLR 2.0 (i.e. "2.0", "3.0", and "3.5"). OR, another aspect to consider is that it with this failing ( declare @res bit; set @res = testing.TestTest(null); ) while this works ( declare @res bit; set @res = (select testing.TestTest(null)); ) that could point to a problem with the Query Optimizer and not with the CLR version. Or maybe it is a combination of the two. Or perhaps it is Descartes's pesky Evil Demon. Either way. ( ** please see comment at the end )

If you are able to move up to SQL Server 2012 or newer, then the problem should "magically" disappear. BUT, if stuck on SQL Server 2008 / 2008 R2, I was able to work around the issue by declaring the parameter to be a non-MAX type if the input is NULL, which should be fine since there won't be any risk of truncation when passing through a NULL. And, in the case of the input parameter datatype of the T-SQL object being called being XML, it still works to define the parameter datatype in the .NET code as NVARCHAR(4000) as it will implicitly convert that into XML when called. The following code worked for me in SQL Server 2008 R2 when passing in a NULL:

SqlParameter _XmlData;

if (Data.IsNull)
{
    // Parameter can't be a LOB type if input is NULL
    _XmlData = new SqlParameter("@d", SqlDbType.NVarChar, 4000);
    _XmlData.Value = DBNull.Value;
}
else
{
    _XmlData = new SqlParameter("@d", SqlDbType.Xml);
    _XmlData.Value = Data.Value;
}

cmd.Parameters.Add(_XmlData);

** @MartinSmith left this illuminating comment:

  Specifically the error seems to be "Access violation reading location 0x0000000000000000" inside sqlservr.exe!CClrXvarProxy::EnsureBufferInit so looks like a null pointer bug but googling those method names doesn't return any KB article.


Better solution for creating many-to-many table per entity

Does every image belong to one and only one entity (i.e. to one person only, to one car only, etc)? If so, your image table needs a key which will identify to which entity it belongs - instead of having a table for each kind of entity. If several entities can use the same image, consider one table of images and one table whose primary key is composed of the image primary key and an entity identifier.


The archive command starts with test, as is recommended on the documentation page: https://www.postgresql.org/docs/11/continuous-archiving.html

To quote the following sentence from that page "This is an example, not a recommendation"

Most likely the file had been copied, and it had been (unknown to PostgreSQL) persisted to disk, but for some reason the copy command returned an error, or didn't return anything at all, because of a crash or an unclean shutdown, or storage was detached unexpectedly from underneath it.  So as far as PostgreSQL is concerned, the archival was not successful and so needed to be repeated.  But when it tried to repeat it, it finds a file with same name already exists.  You could move the existing file to new name, let the archival proceed, then compare the two files to see if they are identical and if so delete the first one.  There is no way to atomically coordinate the filesystem with PostgreSQL, so these things do happen.  If you used a dedicated product to do the archiving rather than rolling your own script, it might take these steps for you.

If you have two clusters archiving to the same place, you will find the files are not identical in their headers.  You will have to figure out whether the other cluster is still valid, and if so where it is supposed to be archiving, fix it going forward, and move the old version of the file to the correct location.

If you are absolutely certain you can't have two instances trying to archive to the same place, then you can remove the 'test' from the archive_command.  Its only useful function is guard against that eventuality.  And as you see, it complicates the case when a archive operation was unsuccessful (either actually unsuccessful, or was not reported up to PostgreSQL as being successful) but did create the named file before failing.


Max Vernon's suggestion about scripting the logons is spot on. For generic Windows Authentication, the default database tends to be master. Setting a default database for each database instance/logon combination will allow control over what the default database is when launching a new query editor window in SSMS.

If you use master for windows auth and/or administer many SQL Databases, the CTRL+U shortcut from a new query window -> select database -> hit enter to select and return to query editor will become firmly ingrained in motor memory.


I don't think you need to join the C table twice. This would work:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C on C.Id = A.IdIntoC
    left join D on D.Id = C.IdIntoD and (D.Type = 1 OR D.Type = 2) ;

The (D.Type = 1 OR D.Type = 2) can be further simplified to D.Type in (1, 2).

If you do want 2 joins to C (and D), you could alter the syntax in your second to last query to (do an inner join to D) and move the join condition to the end:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C as C1
    inner join D as D1 on D1.Id = C1.IdIntoD and D1.Type = 1
  on C1.Id = A.IdIntoC
left join C as C2
    inner join D as D2 on D2.Id = C2.IdIntoD and D2.Type = 2
  on C2.Id = A.IdIntoC ;

or more simpler, join C once and D twice:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C on C.Id = A.IdIntoC
    left join D as D1 on D1.Id = C.IdIntoD and D1.Type = 1
    left join D as D2 on D2.Id = C.IdIntoD and D2.Type = 2 ;


Conditional compilation of SQL Server stored procedure

I'm not aware of this being possible with SSDT unfortunately.

Depending on how big the project is and how many procedures you intend to enhance with 2012 goodies, it may be manageable with Composite Projects.

  SSDT can combine a database project with one or more referenced
  database projects or dacpacs to describe a single composite database
  schema.   Using a composite project allows a large database to be
  broken down into more manageable chunks, allows different people or
  teams to have responsibility for different parts of the overall
  schema, and enables reuse of database object definitions in multiple
  databases.

The notion would be to have a base project, containing the common object definitions and version specific projects for procedures that used new features. The 2012 project would reference the base project and a compile/build would combine objects from both.

The PITA would be that you can't override an object in the base project with an object in a composite, so you would have to maintain base, 2008 & 2012 projects. When you wanted a 2012 version of a particular procedure, you would have to remove it from base and create a version in both 2008 & 2012 projects.


DNS Alias for SQL Instance - LOTS of Instances - All going to default instance

Below is what i've setup so far, I'm not sure what is causing all of
  the aliases to connect only to the default instance but the normal
  connection (server1\inst1) works fine. It seems like it should work.
  What am i missing?

You're allowing Named Pipes connections to the default instance:

@@Servername=Server1, @@ServiceName=MSSQLSERVER
SQL Auth: Net_Transport=named pipe; Auth_Scheme=SQL

You need to turn off Named Pipes if you want to control connectivity through specific IP addresses and ports.  It's only the default instance that listens to the \\.\pipe\sql\query pipe. Named instances will listen on a pipe called \\.\pipe\MSSQL$SOMEINSTANCE\sql\query.  And all the named pipes are available on all the IP addresses.

So basically in your case, the client is connecting to

\\INST1\pipe\sql\query

Which is the default instance's pipe. When a named pipes client connects to the server without an instance name, it will always connect to the default instance's pipe.


Determine the origin IP address of queries against a table?

Simplest method is to use a trigger and log the IP address from sys.dm_exec_connections. You'll need a logging table; I've guessed at a couple of columns you'll want, but you may want others.

CREATE TABLE dbo.LoggingTable
(
    LoggingID INT NOT NULL
        CONSTRAINT PK_LoggingTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , AppName SYSNAME
    , HostName SYSNAME
    , ClientNetAddress VARCHAR(255)
    , LogDateTime DATETIME
);
GO

CREATE TRIGGER dbo.triggername
ON dbo.tablename /* name of the target table  */
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  INSERT dbo.LoggingTable(AppName, HostName, ClientNetAddress, LogDateTime)
  SELECT app_name(), host_name(), client_net_address, SYSDATETIME()
  FROM sys.dm_exec_connections dec
  WHERE dec.session_id = @@SPID;
END
GO

Note that if the update is coming from a connection on the same machine ("The call is coming from inside the house!"), and using shared memory, you'll get <local machine> as the IP address.

Once you've figured it out, don't forget to drop the trigger.

More information about the columns in sys.dm_exec_connections


MS SQL Server 2016 Oprimal Disk Configuration

Looks fine to me.

Because of the way that SQL Server schedules writes, keeping logs and data on separate volumes doesn't save as save as much performance as many think for many workloads (it does have a large effect on some workloads though, so YMMV), and I expect this will be less still on SSDs (as you don't have the latency of head movements flipping between the log and data files to worry about) so if you expect a lot of tempdb activity you might be better off keeping the logs with the data files and leaving tempdb on its own to keep the IO bandwidth of that array to itself.

Of course this is very dependant on actual application workloads so my gut thought above could be completely wrong for your DB(s), so if possible benchmark before committing to anything in production.

Also Cody's comment regarding spare drives is critical: make sure there are some on-site or very nearby. From a HA standpoint mirroring will protect you to an extent should a whole array fail (without mirroring take "or nearby" from the above and make sure there are definitely spares on-site and ready to go) but I'd still feel safer knowing an array can start rebuilding ASAP after a single drive failure to reduce the chance of a dual drive failure. Also check with your infrastructure provider to make sure the machine supports hot-swap (or if not set your recovery plans accordingly to take into account entirely stopping one mirror temporarily while a replacement is installed).


How do I select items from a table where a single column must contain two (or more) values?

This is the relational division problem and there is a question about it at SO, with a lot of ways to write this query, plus performance analysis for PostgreSQL: How to filter SQL results in a has-many-through relation

Shamelessly copying code form there and removing/changing code for answers that have features lacking from MySQL, like CTEs, EXCEPT, INTERSECT, etc, here are a few ways to do this.

Assumptions:

the table is called factors
there is a UNIQUE constraint on (wordid, docid)
there is a documents and a words table:

Easy to write, medium efficiency:

-- Query 1 -- by Martin
SELECT d.docid, d.docname
FROM   document d
JOIN   factors f USING (docid)
WHERE  f.wordid IN (2, 4, 5)
GROUP  BY d.docid
HAVING COUNT(*) = 3 ;           -- number of words

Easy to write, medium efficiency:

-- Query 2 -- by Erwin
SELECT d.docid, d.docname
FROM   documents d
JOIN   (
   SELECT docid
   FROM   factors
   WHERE  wordid IN (2, 4, 5)
   GROUP  BY docid
   HAVING COUNT(*) = 3
   ) f USING (docid) ;

More complex to write, very good efficiency in Postgres - probably lousy in MySQL:

-- Query 4 -- by Derek
SELECT d.docid, d.docname
FROM   documents d
WHERE  d.docid IN (SELECT docid FROM factors WHERE wordid = 2)
AND    d.docid IN (SELECT docid FROM factors WHERE wordid = 4);
AND    d.docid IN (SELECT docid FROM factors WHERE wordid = 5);

More complex to write, very good efficiency in Postgres - and probably the same in MySQL:

-- Query 5 -- by Erwin
SELECT d.docid, d.docname
FROM   documents d
WHERE  EXISTS (SELECT * FROM factors
               WHERE  docid = d.docid AND wordid = 2)
AND    EXISTS (SELECT * FROM factors
               WHERE  docid = d.docid AND wordid = 4)
AND    EXISTS (SELECT * FROM factors
               WHERE  docid = d.docid AND wordid = 5) ;

More complex to write, very good efficiency in Postgres - and probably the same in MySQL:

-- Query 6 -- by Sean
SELECT d.docid, d.docname
FROM   documents d
JOIN   factors x ON d.docid = x.docid
JOIN   factors y ON d.docid = y.docid
JOIN   factors z ON d.docid = z.docid
WHERE  x.wordid = 2
AND    y.wordid = 4
AND    z.wordid = 5 ;

Easy to write and extend to an arbitrary set of words but not as efficient as the JOIN and EXISTS solutions:

-- Query 7 -- by ypercube
SELECT d.docid, d.docname
FROM   documents d
WHERE  NOT EXISTS (
   SELECT *
   FROM   words AS w
   WHERE  w.wordid IN (2, 4, 5)
   AND    NOT EXISTS (
      SELECT *
      FROM   factors AS f
      WHERE  f.docid = d.docid
      AND    f.wordid = w.wordid
      )
   );

Easy to write, not good efficiency:

-- Query 8 -- by ypercube
SELECT d.docid, d.docname
FROM   documents d
WHERE  NOT EXISTS (
   SELECT *
   FROM  (
      SELECT 2 AS wordid UNION  ALL
      SELECT 4 UNION ALL
      SELECT 5
      ) AS w
   WHERE NOT EXISTS (
      SELECT *
      FROM   factors AS f
      WHERE  f.docid = d.docid
      AND    f.wordid = w.wordid
      )
   );

Enjoy testing them :)


You could use SQL CLR. This allows (more-or-less) any C# code to be executed from within SQL Server as if it were a stored procedure. This includes OS and file system manipulation. The code can accept values from and return values to T-SQL. Permissions can be controlled just like any other stored procedure, sidestepping many of the concerns with xp_cmdshell.


The value could not be converted because of a potential loss of data

Ignoring the error

First of all, you should be able to ignore string truncation by going to your Flat File Source, Error Output and then changing "Fail Component" under "Truncation" to "Ignore Error".

Better solution

The real issue could be that the string length inside the SSIS pipeline is still wrong because it got initialized at some earlier point.

You can determine if that is the case by double-clicking the green arrow from your Flat File Source (or after Derived Column/Data Conversion) and choosing "Meta Data". There you can see the length of the field inside the pipeline.

If have often seen that meta data doesn't match the source component anymore, mostly if creating the Flat File Source AND its corrsponding Connection via the Wizard.

My suggestion would be to delete the connection and the Flat File Source and recreate them as I haven't found a way to re-sychronize pipeline meta data with source components.


SELECT COUNT(*) on a view is orders of magnitude slower than SELECT * on the same view

From the execution plans provided, for the COUNT case the optimizer chooses a local/global aggregation strategy around the final join:

Unfortunately, the optimizer overestimates the effectiveness of the local aggregation. It estimates 136 rows driving the Nested Loops join, whereas 366,115 are encountered at runtime.

The 366,115 index lookups might not be too much of an issue for a local SQL Server instance, but the wait stats included in the plan show the I/O (and possibly memory) limitations of your current Azure SQL Database configuration:

The plan for SELECT 1 shows an exclusively hash and merge join strategy, which produces better results in this case with the very limited memory and/or I/O capabilities.

You might well see better performance for the first query with an OPTION (HASH JOIN, MERGE JOIN) hint, but the fundamental issue is the poor cardinality/data distribution estimate driven by the large number of joins.

Don't be misled by the cost percentages shown against each plan operator - these numbers are currently derived from the optimizer's estimate of cost (using an abstract model). The numbers do not reflect runtime conditions or costs.

Large deviations between estimated and actual row counts can often lead to problems. This is especially true for an underestimate that causes the optimizer to choose a strategy that does not scale up well on a particular hardware setup.


How to find MySQL creating temporary table in memory or disk?

Use the variable Created_tmp_disk_tables either on session or global scope to know when you have created implicit temporary tables on disk:

MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 0     | <--- session starts with 0 tmp tables
+-------------------------+-------+
3 rows in set (0.01 sec)

MariaDB [(none)]> SELECT 1 UNION SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     | <--- one tmp table created, 0 on disk
+-------------------------+-------+
3 rows in set (0.01 sec)

MariaDB [(none)]> SELECT 1 UNION ALL SELECT 1;
+---+
| 1 |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.00 sec)

MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     | <--- no tmp table created
+-------------------------+-------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create table test.test (a blob);
Query OK, 0 rows affected (0.18 sec)

MariaDB [(none)]> SELECT * FROM test.test UNION SELECT * FROM test.test;
Empty set (0.00 sec)

MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     | <--- tmp table created on disk
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

If you enable performance_schema, the default configuration (very very low overhead) provides you a summary of everything you probably need:

MariaDB [(none)]> SELECT * FROM performance_schema.events_statements_summary_by_digest\G
[...]
*************************** 26. row ***************************
                SCHEMA_NAME: test
                     DIGEST: f0aad09130b2e108d8dd1c58d1713678
                DIGEST_TEXT: SELECT * FROM `test` UNION SELECT * FROM `test`
                 COUNT_STAR: 1
             SUM_TIMER_WAIT: 716448000
             MIN_TIMER_WAIT: 716448000
             AVG_TIMER_WAIT: 716448000
             MAX_TIMER_WAIT: 716448000
              SUM_LOCK_TIME: 308000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 1 <---- 1 disk tmp table created
     SUM_CREATED_TMP_TABLES: 1 <---- 1 tmp table created (including the disk one)
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 3
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 1
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-01-06 21:18:18
                  LAST_SEEN: 2018-01-06 21:18:18

Edit: I've just noticed you are still on 5.5- I strongly suggest the upgrade, only P_S is worth it for the time not lost on debugging. For now, the session/global variable should work for you, the profiling will not work as well:

MariaDB [(none)]> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT * FROM test.test UNION SELECT * FROM test.test;
Empty set (0.00 sec)

MariaDB [(none)]> SHOW PROFILES;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00055224 | SELECT * FROM test.test UNION SELECT * FROM test.test |
+----------+------------+-------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000053 |
| checking permissions | 0.000011 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000026 |
| After opening tables | 0.000009 |
| System lock          | 0.000008 |
| Table lock           | 0.000196 |
| optimizing           | 0.000010 |
| statistics           | 0.000015 |
| preparing            | 0.000013 |
| optimizing           | 0.000007 |
| statistics           | 0.000006 |
| preparing            | 0.000009 |
| executing            | 0.000005 |
| Sending data         | 0.000019 |
| executing            | 0.000004 |
| Sending data         | 0.000009 |
| optimizing           | 0.000006 |
| statistics           | 0.000006 |
| preparing            | 0.000005 |
| executing            | 0.000004 |
| Sending data         | 0.000016 |
| removing tmp table   | 0.000057 |
| Sending data         | 0.000006 |
| query end            | 0.000008 |
| closing tables       | 0.000004 |
| Unlocking tables     | 0.000008 |
| freeing items        | 0.000005 |
| updating status      | 0.000009 |
| cleaning up          | 0.000012 |
+----------------------+----------+
30 rows in set (0.00 sec)

Sometimes you will see a step like "copying from heap to myisam", when it bails on on-memory execution, but that will not happen everytime, like shown above. You mention innodb on the tags, but implicit temporary tables only started being InnoDB (vs HEAP + MyISAM) since 5.7.


How can a blocking_session_id be minus 2?

Take a look at this blog post by Bob Dorr: How It Works: Orphan DTC Transaction (Session/SPID = -2)

As Bob says, it's a bit of a misnomer that it's an orphaned transaction by DTC, but that is usually how it represents itself.  It's due to (as per Bob's words) "NO ENLISTED SESSIONS on the SQL Server but the transaction is active yet".  You should take a look at that link in it's entirety.

Oftentimes what happens with DTC transactions is that there is no commit/rollback and it does indeed pose a problem, as MSDTC may not reconnect to clean it up, release locks, etc.

See the BOL reference on sys.dm_tran_locks:

  Distributed transactions that are not associated with a session ID value are orphaned transactions and are assigned the session ID value of -2. For more information, see KILL (Transact-SQL).

Yes, BOL uses the terminology "orphaned transaction", as in my experience that is what it usually ends up being (although you can't make that blanket statement without proper investigation that a distributed transaction coordinator hasn't indeed forgotten about the transaction holding everything up).  In that case, after a proper orphaned determination, it is common to then KILL it based off of the unit of work (UOW).


A few things spring to mind.

The patient_info table should be called patient. The (my) patient table should contain patient_name.
Every table should have a Primary Key called table_name_id and not just id as you have (makes debugging difficult - which "id" is causing the problem + problems of ambiguity of column names).
The medical_history table should hang off patient.
The table you call patients should be called appointment.
You vary your table names between singular and plural. Pick one and stick to it. I recommend singular.
These are just my first impressions.
You could also Google things like "Open source medical/dental practice software" and see how they do things in their schemas. Then you might be able to adapt them to your needs. That's the beauty of Open Source software.

p.s. welcome to the forum and good luck with your project! :-)

[Edit in response to OP's request for clarification]

Delete you patients table and replace it with the appointment table.

Your appointment table will have appointment_id, patient_id, dentist_id, date and time.

As regards your dentist friend, that's great that you can run ideas by them, but check out the Open Source s/ware nonetheless.

Another point, I don't know if MySQL is a requirement or not, but if not, I would change to PostgreSQL. Far better for CHECK CONSRINTS - i.e. no overlapping appointments &c.


High amount of Read Misses and Pages To Be Flushed

What really jumps at me is that 52% miss. It's possible your innodb_buffer_pool_size is just too small.
Keep in mind that the InnoDB Buffer Pool caches data pages and index pages.
How can you get a good size for it? Run this query:
SELECT
    CONCAT(CEILING(ibps/POWER(1024,1)),'K') IBPS_KB,
    CONCAT(CEILING(ibps/POWER(1024,2)),'M') IBPS_MB,
    CONCAT(CEILING(ibps/POWER(1024,3)),'G') IBPS_GB
FROM (SELECT SUM(data_length+index_length) ibps
FROM information_schema.tables WHERE engine='InnoDB') A;

This will give you something like this
mysql> SELECT
    ->     CONCAT(CEILING(ibps/POWER(1024,1)),'K') IBPS_KB,
    ->     CONCAT(CEILING(ibps/POWER(1024,2)),'M') IBPS_MB,
    ->     CONCAT(CEILING(ibps/POWER(1024,3)),'G') IBPS_GB
    -> FROM (SELECT SUM(data_length+index_length) ibps
    -> FROM information_schema.tables WHERE engine='InnoDB') A;
+-----------+---------+---------+
| IBPS_KB   | IBPS_MB | IBPS_GB |
+-----------+---------+---------+
| 30333520K | 29623M  | 29G     |
+-----------+---------+---------+
1 row in set (11.43 sec)

mysql>

If the output gives you settings beyond 80% of the DB Server's Installed RAM, then use 80% of whatever the installed RAM is as the innodb_buffer_pool_size.
Give it a Try !!!
UPDATE 2013-04-15 12:43EDT
Let's look at the definition of Innodb_buffer_pool_wait_free

Normally, writes to the InnoDB buffer pool happen in the background. However, if it is necessary to read or create a page and no clean pages are available, it is also necessary to wait for pages to be flushed first. This counter counts instances of these waits. If the buffer pool size has been set properly, this value should be small.

As stated, if the buffer pool size has been set properly, this value should be small. You may simply have lots of dirty pages in the Buffer Pool that need flushing to disk. You should be monitoring Innodb_buffer_pool_pages_dirty.
There are two things you could do to improve the situation:
IMPROVEMENT #1 : Upgrade to the latest MySQL
I trust MySQL 5.5. I have a client going to MySQL 5.6.10 soon. I trust it as well. These versions of MySQL have the InnoDB Plugin standard. They flush dirty pages much more efficiently.
You can also tune InnoDB. Under MySQL 5.1, there are 4 read IO threads and 4 write IO threads. MySQL 5.5+ allows you to increase these for better read and write InnoDB performance. InnoDB For MySQL 5.5.+ can access multiple CPUs/Core. MySQL 5.1 can do this if using MySQL 5.1.38+ and you install the InnoDB Plugin (IMHO too messy, go with MySQL 5.5/5.6). MySQL 5.1.27 cannot do this.
IMPROVEMENT #2 : Get Dirty Pages to Flush More Frequently
You can do this immediately with
SET GLOBAL innodb_max_dirty_pages_pct = 0;

The default value for innodb_max_dirty_pages_pct in MySQL 5.1 is 90. Drop this to zero(0). Then, start watching Innodb_buffer_pool_pages_dirty. On a busy write server, this should drop to 1% of Innodb_buffer_pool_pages_total.


MySQL - Dumping 1TB database with SED to replace definer

First, dump only the routines, not any of the data.  That way it should be quite fast -- drop a routine, rebuild it.  Rinse, repeat.
As for the regexp, the format should be very regular, so [ ]* (or \s* can probably be simplified to  .
[^*]* -->  .*\*

With some guts, simply
mysqldump ... | set -e '...' | mysql ...

I'm not clear that the regexp does the right thing; please provide a sample.


First problem is: What do you mean by ANSI?

ANSI is a common misnomer for your default Windows character set. It depends on your Windows language. See a list of codepages on National Language Support (NLS) API Reference (column "ANSI codepage")

You can also query your Registry to get it:

C:\>reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage /v ACP

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage
    ACP    REG_SZ    1252

Let's assume it is CP1252, the most common on "Western" PC's

When you run SQL*Plus then it inherits the codepage from your cme.exe. However this is not "ANSI codepage", you get it in column "OEM codepage" or by Registry

C:\>reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage /v OEMCP

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage
    OEMCP    REG_SZ    850

This is typically CP850 (for Western Europe) or CP437 (in US)

You can change and interrogate the code page by command chcp

NLS_LANG parameter tells the database which character set you are using (because the Oracle Database cannot interrogate your settings from Notepad++ or cmd.exe)

So, it order to make it working run following:

C:\>chcp 1252
Active code page: 1252

C:\>set NLS_LANG=.WE8MSWIN1252

C:\>sqlplus user/pwd@DB @xyz.sql


This is the expected behavior for system compatibility views from SQL Server 2012+:

  When referenced in a user database, system tables which were announced
  as deprecated in SQL Server 2000 (such as syslanguages or
  syscacheobjects), are now bound to the back-compatibility view in the
  sys schema. Since the SQL Server 2000 system tables have been
  deprecated for multiple versions, this change is not considered a
  breaking change.

  Example: If a user creates a user-table called syslanguages in a
  user-database, in SQL Server 2008, the statement SELECT * from
  dbo.syslanguages; in that database would return the values from the
  user table. Beginning in SQL Server 2012, this practice will return
  data from the system view sys.syslanguages.

You're probably going to have to fix your database to not use the same names as  deprecated system tables (now system views in the sys schema).


Can't use CAST, Convert with UDT in Sql Server 2014

I agree with the comment about providing a minimal, complete and verifiable example of your problem.  I'm not sure why you're trying to use CAST/CONVERT when attempting to define a UserType.  Simply use the base SystemType.

DROP TABLE IF EXISTS TestUdt;
DROP TYPE IF EXISTS DecimalPrecision5Scale4;
DROP TYPE IF EXISTS DecimalPrecision11Scale2;
GO

CREATE TYPE DecimalPrecision5Scale4
FROM DECIMAL(5, 4) NOT NULL;

CREATE TYPE DecimalPrecision11Scale2
FROM DECIMAL(11, 2) NOT NULL;
GO

CREATE TABLE TestUdt
(
TestCol1 DecimalPrecision5Scale4,
TestCol2 DecimalPrecision11Scale2
);

--Verify precision and scale of UDT

SELECT NAME
    ,precision
    ,scale
FROM sys.columns
WHERE NAME LIKE '%TestCol%';

| NAME     | precision | scale |
|----------|-----------|-------|
| TestCol1 | 5         | 4     |
| TestCol2 | 11        | 2     |

UPDATE - 2018-09-11 - Regarding OP comment

  how can I explicitly reduce precision using udt

You could create a @temp Table Variable (or #temp table) with UDT columns for the precision you want, insert the raw data into the temp table (letting SQL Server do the conversion) and then select from the @temp table

Declare @TestConvert table(Qty udtPrice)
insert into @TestConvert select qty from TestQty
SELECT TOP 1 Qty from @TestConvert

| Qty    |
|--------|
| 2.1235 |


Update row with multiple row in another table Error : ORA-01427: single-row subquery returns more than one row?

Community wiki answer:

Since there are several rows in the result you need to decide which one to use as the new value for trn_status:

Limit your subquery with FETCH FIRST 1 ROW ONLY.
If you can guarantee that all corresponding records are full duplicates, use DISTINCT.
Using min, max, avg aggregate would be other possibilities.


Migrate SQL Server binary datadump to postgresql

What you are seeing is SQL Server data pages in a backup file. Or a SQL Server data file (.MDF). Either way, is is a collection of 8k data pages.

These would be restored or attached to a SQL Server.
They are not conveniently readable without a SQL Server install.

No SQL Server = no access to the data. Quite simple.

If you don't have access to a SQL Server install, why on earth do you have a SQL Server data file/backup? What do you or your source expect you to do with it?

So, your solution is clear: ask your source to export the data to CSV and provide the database design.Then you implement and import into PostgreSQL.


You should be very happy the mysqldump is two-thirds the size of the data. Why?

You should want to move data and indexes without copying unused space at the same time. What aspects would you have to consider by simply importing a mysqldump?

Aspect #1

InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.

Aspect #2

InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:

With innodb_file_per_table disabled, the system tablespace file ibdata1 would steadily grow with 0% chance of reclaiming of disk space. You would have to convert InnoDB storage engine to use innodb_file_per_table.
With innodb_file_per_table enabled, you could go to the individual InnoDB and run     of the the following to physically shrink the InnoDB table mydb.mytable:

OPTIMIZE TABLE mydb.mytable;
ALTER TABLE mydb.mytable ENGINE=InnoDB;

In light of this, you should keep in mind that a mysqldump is a logical representation of the data and indexes. There is no wasted space to be concerned with. You should not have to scp or rsync bloated InnoDB data or logs. Importing a mysqldump into a fresh installation of MySQL should be what you what want.

I have written about this many times:

MySql - Clean ibdata1 (April 19, 2012)
How do you remove fragmentation from InnoDB tables? (April 11, 2012)
Is innodb_file_per_table advisable? (April 01, 2012)
How to Clean a mysql innodb storage engine (October 29, 2010)

Once you have loaded the test database with the mysqldump, you should run CHECKSUM TABLE on random tables of various sizes. If all tables have the same checksum value, you could certify as good. Make a disklevel backup of the data on the prod server as well.

You can then do this in production

shutdown mysql
hose everything (including ibdata1, ib_logfile0, ib_logfile1) in /var/lib/mysql except for /var/lib/mysql/mysql
Set innodb_data_file_path=ibdata1:10M:autoextend in /etc/my.cnf
restart mysql (this recreates ibdata1, ib_logfile0, ib_logfile1)
reload mysqldump

Your InnoDB architecture and data will then be lean and mean.


Designing a platform: one database or multiple databases? is relevant background reading for your question.

You're possibly approaching this from the wrong angle. Is there really going to be a single, all mighty, all encompassing, one true system called "THE Hospital Management System"? Or will there be:

Appointments Management System
Patient Records Systems
Medicines/pharmacology database (highly likely to be something bought in, rather than re-invented)
... ?

I'm sure there are dozens of other applications to add the list. Each of these is a separate application and probably a separate database, all of which need to talk to each other via a defined interface. One big application with one big database is a fast road to chaos.


Guidelines for full-text index maintenance

I wasn't able to find any good resources online, so I did some more hands-on research and thought it would be useful to post the resulting full-text maintenance plan we are implementing based on that research.

Our heuristic to determine when maintenance is needed

Our primary goal is to retain consistent full-text query performance as data evolves in the underlying tables.  However, for various reasons it would be difficult for us to launch a representative suite of full-text queries against each of our databases each night and use the performance of those queries to determine when maintenance is needed.  Therefore, we were looking to create rules of thumb that can be computed very quickly and used as a heuristic to indicate that full-text index maintenance may be warranted.

In the course of this exploration, we found that the system catalog provides a lot of information about how any given full-text index is divided into fragments.  However, there is no official "fragmentation %" computed (as there is for b-tree indexes via sys.dm_db_index_physical_stats).  Based on the full-text fragment information, we decided to compute our own "full-text fragmentation %".  We then used a dev server to repeatedly make random updates of anywhere between 100 and 25,000 rows at a time to a 10 million row copy of production data, record the full-text fragmentation, and perform a benchmark full-text query using CONTAINSTABLE.

The results, as seen in the charts above and below, were very illuminating and showed the the fragmentation measure we had created is very highly correlated to the observed performance.  Since this also ties out with our qualitative observations in production, this is enough that we're comfortable using the fragmentation % as our heuristic for deciding when our full-text indexes need maintenance.

The maintenance plan

We have decided to use the following code to compute a fragmentation % for each full-text index.  Any full-text indexes of non-trivial size with fragmentation of at least 10% will be flagged to be re-built by our over-night maintenance.

-- Compute fragmentation information for all full-text indexes on the database
SELECT c.fulltext_catalog_id, c.name AS fulltext_catalog_name, i.change_tracking_state,
    i.object_id, OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS object_name,
    f.num_fragments, f.fulltext_mb, f.largest_fragment_mb,
    100.0 * (f.fulltext_mb - f.largest_fragment_mb) / NULLIF(f.fulltext_mb, 0) AS fulltext_fragmentation_in_percent
INTO #fulltextFragmentationDetails
FROM sys.fulltext_catalogs c
JOIN sys.fulltext_indexes i
    ON i.fulltext_catalog_id = c.fulltext_catalog_id
JOIN (
    -- Compute fragment data for each table with a full-text index
    SELECT table_id,
        COUNT(*) AS num_fragments,
        CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS fulltext_mb,
        CONVERT(DECIMAL(9,2), MAX(data_size/(1024.*1024.))) AS largest_fragment_mb
    FROM sys.fulltext_index_fragments
    GROUP BY table_id
) f
    ON f.table_id = i.object_id

-- Apply a basic heuristic to determine any full-text indexes that are "too fragmented"
-- We have chosen the 10% threshold based on performance benchmarking on our own data
-- Our over-night maintenance will then drop and re-create any such indexes
SELECT *
FROM #fulltextFragmentationDetails
WHERE fulltext_fragmentation_in_percent >= 10
    AND fulltext_mb >= 1 -- No need to bother with indexes of trivial size

These queries yield results like the following, and in this case rows 1, 6, and 9 would be marked as being too fragmented for optimal performance because the full-text index is over 1MB and at least 10% fragmented.

Maintenance cadence

We already have a nightly maintenance window, and the fragmentation calculation is very cheap to compute.  So we will be running this check each night and then only performing the more expensive operation of actually rebuilding a full-text index when necessary based on the 10% fragmentation threshold.

REBUILD vs. REORGANIZE vs. DROP/CREATE

SQL Server offers REBUILD and REORGANIZE options, but they are available only for a full-text catalog (which may contain any number of full-text indexes) in its entirety.  For legacy reasons, we have a single full-text catalog that contains all of our full-text indexes.  Therefore, we have opted to drop (DROP FULLTEXT INDEX) and then re-create (CREATE FULLTEXT INDEX) on an individual full-text index level instead.

It might be more ideal to break full-text indexes into separate catalogs in a logical way and perform a REBUILD instead, but the drop/create solution will work for us in the meantime.


Differences of MySQL locking for update with WHERE and IN

If we use the performance_schema.data_locks table we get a better idea which locks are different.

Here we can see the supremum, or gap lock is the additional lock occurs with the RANGE query. While the is possible to avoid because because id 3 exists as a primary key, the MySQL developers haven't implemented this as it complicates locking and adds checks in a rather critical and time sensitive execution path.

Note, setting SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; will make locks equivalent as gap locks are only REPEATABLE READ.

Schema (MySQL v8.0)

CREATE TABLE example (
  `id` INTEGER NOT NULL PRIMARY KEY,
  `some_val` VARCHAR(36),
  `created_at` datetime
);

INSERT INTO example
  (`id`, `some_val`, `created_at`)
VALUES
  ('1', '33aa186d-e2de-4c41-aa50-000000000001', '2019-03-17 15:15:21.130'),
  ('2', '33aa186d-e2de-4c41-aa50-000000000002', '2019-03-17 15:15:31.292'),
  ('3', '33aa186d-e2de-4c41-aa50-000000000003', '2019-03-17 15:16:01.396');

Query #1

BEGIN;

Query #2

SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;

| id  | some_val                             | created_at          |
| --- | ------------------------------------ | ------------------- |
| 1   | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21 |
| 2   | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31 |
| 3   | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01 |

Query #3

SELECT * FROM performance_schema.data_locks;

| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
| ------ | -------------- | --------------------- | --------- | -------- | ------------- | ----------- | -------------- | ----------------- | ---------- | --------------------- | --------- | --------- | ----------- | ---------------------- |
| INNODB | 2070:1063      | 2070                  | 48        | 4        | test          | example     |                |                   |            | 140108678336728       | TABLE     | IX        | GRANTED     |                        |
| INNODB | 2070:2:4:2     | 2070                  | 48        | 4        | test          | example     |                |                   | PRIMARY    | 140108678333688       | RECORD    | X         | GRANTED     | 1                      |
| INNODB | 2070:2:4:1     | 2070                  | 48        | 4        | test          | example     |                |                   | PRIMARY    | 140108678334032       | RECORD    | X         | GRANTED     | supremum pseudo-record |
| INNODB | 2070:2:4:3     | 2070                  | 48        | 4        | test          | example     |                |                   | PRIMARY    | 140108678334032       | RECORD    | X         | GRANTED     | 2                      |
| INNODB | 2070:2:4:4     | 2070                  | 48        | 4        | test          | example     |                |                   | PRIMARY    | 140108678334032       | RECORD    | X         | GRANTED     | 3                      |

Query #4

ROLLBACK;

Query #5

BEGIN;

Query #6

SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;

| id  | some_val                             | created_at          |
| --- | ------------------------------------ | ------------------- |
| 1   | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21 |
| 2   | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31 |
| 3   | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01 |

Query #7

SELECT * FROM performance_schema.data_locks;

| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
| ------ | -------------- | --------------------- | --------- | -------- | ------------- | ----------- | -------------- | ----------------- | ---------- | --------------------- | --------- | --------- | ----------- | --------- |
| INNODB | 2071:1063      | 2071                  | 48        | 9        | test          | example     |                |                   |            | 140108678336728       | TABLE     | IX        | GRANTED     |           |
| INNODB | 2071:2:4:2     | 2071                  | 48        | 9        | test          | example     |                |                   | PRIMARY    | 140108678333688       | RECORD    | X         | GRANTED     | 1         |
| INNODB | 2071:2:4:3     | 2071                  | 48        | 9        | test          | example     |                |                   | PRIMARY    | 140108678333688       | RECORD    | X         | GRANTED     | 2         |
| INNODB | 2071:2:4:4     | 2071                  | 48        | 9        | test          | example     |                |                   | PRIMARY    | 140108678333688       | RECORD    | X         | GRANTED     | 3         |

Query #8

ROLLBACK;

View on DB Fiddle


First of all I'm not sure why you are dividing by 3600 .. that would return you 1/3600th of a second.

Second you are over thinking things a bit.  Try this:

CREATE TABLE #NotDope
(
  date1 datetime
)

INSERT INTO #NotDope (date1) Values
('2016-01-04 08:08:47.000'),
('2016-01-04 08:25:59.000'),
('2016-01-04 08:27:47.000'),
('2016-01-04 08:00:00.000'),
('2016-01-04 07:55:00.000')

SELECT date1,
    CASE WHEN CAST(date1 AS Time) > '08:00:00.000' THEN
        datediff(second, '08:00:00', CAST([date1] AS time))
    ELSE 0 END AS SecondsLate
FROM #NotDope

All you need to do is cast your date as a time data type to get rid of the date part.


The COPY FROM will not violate integrity of database.

When a constraint fails, an error is throw, and none of data are copied (it is rolled back).

This behavior is unchangeable. You can do a script in a programming language to help you in a copy.


Generate the statements you need

select 'DELETE FROM '||table_name||' ;' from user_tables;

Even better would be using TRUNCATE instead of DELETE.  That would prevent generating extra redo vectors in the redo log - in the end all you want to do is just get rid of the data. It would also avoid all actions by any DELETE triggers that the tables have.

select 'TRUNCATE TABLE '||table_name||' ;' from user_tables;


Looking at your errors, especially 8996, looks like a hardware problem.

Run hardware diagnostics and correct any problems. Also examine the Microsoft Windows system and application logs and the SQL Server error log to see whether the error occurred as the result of hardware failure. Fix any hardware-related problems that are contained in the logs.

CHECKDB wont be able to repair this error. Best is to restore from a GOOD backup.

Refer here for more details.


I have a trigger that uses a local variable @userId that is set prior to the triggering query.

Local variables are variables defined on a compound-statement level with this compound statement scope using DECLARE variable statement explicitly. Their name's first symbol cannot be @.

User-defined variables are session scope variables defined implicitly (in SET or INSERT INTO statement) by the first 2 symbols of their names (first is @ and second is not).

So you use user-devined, not local, variables.

User-defined variables are ignored by the replication at all.

  Can I use local variables in a trigger if I expect it to replicate on a slave?

The answer is "No". Use custom service table.

  I know from the documentation that session variables will not work, but they do not mention local variables.

Session variables are system variables duplicates with a session scope. They (when exists) override system variables values. They are not local or user-defined, they are a separate type of variables. As for user-defined variables their first two symbols are @ and not @ - it shows that their values may be set by user with a session scope (like user-defined variables).


What is exactly a uniform server in a galera cluster setup?

The word "uniform" is being used to describe the fact that each server should have similar or identical resources, in terms of cpu, memory, disk (seek time, I/O bandwidth), etc.

Since a single node can slow down the entire cluster, the slowest node determines the overall capacity of the cluster... so using servers whose performance capabilities are not "uniform" (closely comparable) will mean that resources on the more powerful machines will go underutilized, while the less powerful machines slow down the cluster.


SQL server on VMWare restart loses five days of data

The problem was never definitively resolved. What was found was that at some point their database was completely truncated by an unknown command. Every time they tried to restore the database and roll forward, they were re-truncating their DB.

They started rolling forward a little bit at a time until they found the moment when it went bad. At that point they cut me out of the troubleshooting loop so I was not able to participate in the post-mortem. My guess is that someone did something really stupid on the database and they didn't want me to dig too deeply.


What's a good way to get SQL Server to release memory to the operating system on a dev machine?

I routinely increase and decrease the max server memory configuration option for the SQL Server 2005-2016 instances running on my laptop (with lock pages in memory enabled). Has never caused me any problems, and memory always seems to be released quickly. I also stop, start, and restart instances as I find I need them (using SQL Server Configuration Manager, which I keep open).

As far as checkpoint is concerned, how you configure that depends on version. Manual checkpoint with a duration target has been available since 2005 (though it is undeniably more effective in later versions).

Running a manual checkpoint <duration> per database before shutdown will help spread the I/O load and reduce recovery time on startup. Managed checkpointing will also reduce I/O needed when a memory shrink is requested because there will be fewer dirty pages to write to persistent storage. There is no particular benefit to dropping clean buffers before reducing the max memory setting that I have noticed.

Not using 'lock pages in memory' may increase the rate at which SQL Server can release memory, but I rely on that to avoid paging SQL Server memory to disk. If you don't need it for that purpose, you might choose to run without it.


If possible, write a Stored Procedure to encapsulate the entire task.  And deal with exceptions.  Then have Drupal call the SP.


Very generally and non-technically, you should consider using the column as part of the index key if you are going to use that column in the WHERE clause (or otherwise filter/seek), and INCLUDE if it is merely being used to avoid a lookup (I like to call this "coming along for the ride"). Of course there are always exceptions:

you can't always include columns in the key due to size;
it may not matter due to cardinality; or,
it might not affect the plan due to where you would have to put it in the key to allow the index to continue satisfying other query semantics.

There are also cases where an INCLUDEd column can satisfy a filter just fine, and plenty of cases where changing the index might help this specific query but it might wreak havoc on the rest of your workload. You should always test index changes against an entire workload representing as much of a full business cycle as possible, rather than rely on the missing indexes DMV or your own analysis which, it seems, is focused on a single query.

I'm sure Paul White will come along with a much more elaborate and technically accurate response.


This is a classical scenario I have seen dozens of times in my DBA career. Before I explain this, I want to share something with you. I learned something a long time ago about positions within a binary log. It actually represents the number of bytes up to a specific place when recording a SQL transaction.
EXAMPLE
When you first startup a mysql instance with binary logging enabled, you get an empty binary log. Each version of MySQL has a different size for an empty binary log (See my Feb 04, 2011 post MySQL master binlog corruption) Let's use MySQL 5.5 for this example. Here is the mysqlbinlog output of a empty binary log for MySQL 5.5 running on my Windows Desktop:
C:\MySQL_5.5.12\data>dir mysql-bin.000001
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\MySQL_5.5.12\data

08/16/2012  02:29 PM               107 mysql-bin.000001
               1 File(s)            107 bytes
               0 Dir(s)  191,225,950,208 bytes free

C:\MySQL_5.5.12\data>mysqlbinlog mysql-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#120816 14:29:55 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.12-log created 120816 14:29:55 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
IzwtUA8BAAAAZwAAAGsAAAABAAQANS41LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAjPC1QEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

The file is exactly 107 bytes and there is header info within it that marks end_log_pos 107. If you were to setup a slave that is newly installed, you would run
CHANGE MASTER TO
MASTER_HOST='IP_of_Master',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

Then, run START SLAVE;. That's it. MySQL Replication takes off from there.
NOW, YOUR SITUATION ...
The only reason I have ever encountered your situation (MySQL Exec_Master_Log_Pos value greater than Read_Master_Log_Pos) is this (Still Writing)...
MySQL Replication functions with two threads

IO Thread : Responsible for Maintaining a Connection Back to the Master. It is used to collect SQL entries recorded in the Master's Binary Logs and storing those entries in the Slave's Relay Logs.
SQL Thread : Responsible for Reading the Entries from the Slave's Local Relay Logs, Executing the SQL, and Rotating the Fully Used Relay Logs.

Your problem has to do with the IO Thread. If there was any intermittency experienced by the IO Thread, it was most likely figuring out the size of the incoming SQL statement from the Master but never had the chance to actually read the SQL statement. I can say with absolute certainty because in the example posted above, the end_log_pos is read in the header of the SQL statement before the statement itself is actually read. This usually happens under these circumstances:

mysqld was restarted on the Master while replicating
Master DB Server crashes while replicating
possible dropped packets during the session between Master and Slave

REMEDY
The simplest remedy when this happens is to skip to the next binary log on the Master.
Suppose you have the following

running MySQL 5.5 on the Master
Read_Master_Log_File in SHOW SLAVE STATUS\G is mysql-bin.012345

The remedy is to run this (skip to the next binary log mysql-bin.012346)
STOP SLAVE;
CHANGE MASTER TO
master_log_file='mysql-bin.012346',
master_log_pos=107;
START SLAVE;

This will pick up from where the Slave last left off. This should be your solution in a low-write environment and if the Master has all the binary logs the Slave needs.
BUT WAIT...
you said it had been months since the Slave was broken. The next binary log on the Master may no longer exist. If you run CHANGE MASTER TO command right now, it will erase any binary logs you have now. What can you do ???
Unfortunately, you must rebuilt the Slave from scratch.
You would have to do the following on the Slave
echo "STOP SLAVE;" > MySQLData.sql
MYSQLDUMP_OPTIONS="-hIP_of_Master -u... -p..."
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases --triggers --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1 --single-transaction"
mysqldump ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
mysqldump -uroot -prootpass < MySQLData.sql

This will stop the slave, reload the data, and start the slave. The option master-data=1 will write the CHANGE MASTER TO command at line 23 of MySQLData.sql.
Give it a Try !!!


Not getting current IP address in my oracle database

SYS_CONTEXT('USERENV','IP_ADDRESS') returns the ip-address of the client currently connected to the server.
In your case your toad is connected to Oracle server using loopback ip address(localhost).
I dont have toad demostrate it but I have tested it in my linux machine.

[oracle@testsrv Desktop]$ sqlplus sys/oracle@192.168.1.50/orcl as sysdba

SQL> select sys_context('USERENV','IP_ADDRESS') from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
192.168.1.50

SQL> exit

[oracle@testsrv Desktop]$ sqlplus sys/oracle@localhost/orcl as sysdba

SQL> select sys_context('USERENV','IP_ADDRESS') from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
127.0.0.1

SQL>


Create a custom db roles that uses a lookup table

Roles need to be explicitly defined. You can't tell the engine to just look up the role membership and/or rights in a table.

You'll need to manually create the roles/permissions with dynamic SQL from the table, and come up with some kind of plan to keep them in sync whenever the data in the table changes (you can use a trigger or, if a delay is possible, a scheduled job).

You mention schemas, and I'm not sure how relevant that is, but obviously an easier way to manage this would be to put all of the relevant users in a role, put all the relevant objects in a schema, and GRANT permissions on the schema to the role. If you have exceptions you can implement DENY.


SQL Server Index: One new table joining to three old tables, how to?

You could possibly just use the first index and "include" the fk_b1 and fk_c1 columns. It will depend on your data so you'll need to test, as mentioned previously.


Why is "select *" faster than "select top 500 *" in SQL Server?

Adding a TOP clause to a query introduces a row goal to the query. The query optimizer will attempt to use the fact that it doesn't need to return all rows to create a more efficient query plan. The row goal can cause the cost of some operators to be scaled down. The row goal optimization can work against the favor of the query tuner due to model limitations or incomplete information in statistics objects. Below I have a demo against a simple view for which adding TOP 500 degrades performance.

First insert only odd integers into a table. Note that I'm gathering statistics in full at the end.

DROP TABLE IF EXISTS dbo.ODD;

CREATE TABLE dbo.ODD (
    ID BIGINT NOT NULL,
    FLUFF VARCHAR(10)
);

INSERT INTO dbo.ODD WITH (TABLOCK)
SELECT TOP (100000)
  -1 + 2 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('FLUFF', 2)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE STATISTICS S ON dbo.ODD (ID) WITH FULLSCAN;

Then insert only even integers into a different table. I'm doing some things with repeated values and row size to make the demo work. I still update statistics in full at the end.

DROP TABLE IF EXISTS dbo.EVEN;

CREATE TABLE dbo.EVEN (
    ID BIGINT NOT NULL,
    FLUFF VARCHAR(3500)
);

INSERT INTO dbo.EVEN WITH (TABLOCK)
SELECT TOP (100000)
  1000 * FLOOR ( ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 500)
, REPLICATE('FLUFF', 700)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2

CREATE STATISTICS S ON dbo.EVEN (ID) WITH FULLSCAN;

Here's the view definition:

CREATE OR ALTER VIEW dbo.TRICKY_VIEW AS
SELECT o.ID
FROM dbo.ODD o
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.EVEN e WHERE o.ID = e.ID
);

Consider the following query:

SELECT TOP 500 *
FROM dbo.TRICKY_VIEW
OPTION (MAXDOP 1);

Here's what the query plan looks like:

A costing limitation causes the full scan of the EVEN table on the inner side of the nested loop join to have a low relative cost. Based on how I constructed the data we know that the optimizer will need to scan 500 * 100000 = 50 million rows from the EVEN table in order to return the first 500 rows to the client. This is indeed what happens, and the query takes about 16 seconds to execute on my machine:

Removing the TOP clause from the query gives a different and more efficient plan:

This query executes in less than half a second on my machine. Only 100000 rows are read from the EVEN table.

For SQL Server 2016 and later versions you can work around this issue without changing the view's definition by adding OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL')) to the query. That hint disables the row goal optimization at the query level. For SQL Server 2012 you can use trace flag 4138 at the query level via OPTION (QUERYTRACEON 4138), but that requires SA.

I can't say anything about your query in particular without seeing the query plans, but hopefully this example illustrates the general point.


stored procedure with multiple statements does not execute all statements when run from job

The answer, it turned out, is something no one in forum land would have been able to guess at, no matter how good with SQL Server they were. It was application-level security that no one here was aware of.

The CRM database which was the source of the data for insert, contains a table of system users managed on the front end of the application. The vendor who developed the software set it up, but as they left no documentation, no one here was aware of the security layer on the back-end. It was confusing, largely, because the security only affects certain views - so all those other jobs that were working were calling for information from tables and views that were not influenced by the security.

When testing logged in as me, the procedure worked because I am set up in the CRM table as an administrator, but the credentials used to run SQL Server agent don't even exist in the CRM user table.

Had nothing to do with SQL Server security, but everything to do with permissions (didn't see that coming)...


How can a field with multiple values be used with SQL Server?

On newer versions of SQL Server, you might consider bringing over the JSON data as-is, storing it in an nvarchar(max) column, and using the built-in JSON related functions for parsing and querying JSON data.  This is available in SQL Server 2016+ (compatibility level 130).  You can read more about it here: JSON data in SQL Server

If you plan to "convert" the MongoDB documents into a more relational pattern, the mapping table you mentioned would be a good way to prevent the "Name" of the signer being repeated in each row.

It looks like what you're modeling in the MongoDB document is a collection of documents that have been signed by a given Person.  So the relational structure would be something like this:

A table for documents:

CREATE TABLE dbo.Docs
(
    ID int IDENTITY PRIMARY KEY NOT NULL,
    [Title] varchar(100) NOT NULL
);

A table for the people signing these documents:

CREATE TABLE dbo.Person
(
    Id int IDENTITY PRIMARY KEY NOT NULL,
    [Name] varchar(100) NOT NULL
);

And then the table that brings together all of the documents a person has signed:

CREATE TABLE dbo.SignedDocs
(
    PersonId int NOT NULL,
    DocId int NOT NULL,

    CONSTRAINT FK_SignedDocs_Person FOREIGN KEY (PersonId)
        REFERENCES dbo.Person (Id),

    CONSTRAINT FK_SignedDocs_Docs FOREIGN KEY (DocId)
        REFERENCES dbo.Docs (Id)
);

You could add more constraints to the table if there are other rules that need to be enforced (a document can only be signed by one person, for example).

Note that performing this sort of "ETL" process to convert NoSQL data to relational data can have a lot of maintenance overhead.  One of the advantages of a document database like MongoDB is that the structure of documents can easily be changed.  This has great potential to break the ETL.  Of course, every environment is different, so it might not be a problem for you.  I just thought it was worth mentioning.


You can't, the query portion of the OPENQUERY must be a string literal, so no variables passed in.  You can make the whole thing dynamic SQL though.

 Declare @salesman varchar(max) = 'Rich'

 DECLARE @Query NVARCHAR(4000) = '

Select * FROM OPENQUERY(XXX.XXX.XXX.X,''Select
        cm.salesman salesmanName
        ,iv.saledate InvoiceDate
        ,CAST(COALESCE(jc.salecost,0) AS DOUBLE PRECISION) As totalsalecost
        ,jc.saleid saleid
FROM jobcost jc
        INNER JOIN arc ac
        ON jc.jcid = ac.jcid
        LEFT OUTER JOIN rama wp
        ON wp.jwid = ac.jwid
        Left join ira iv
        ON jc.saleid = iv.saleid
        LEFT JOIN sales jb
        ON jc.saleid = jb.saleid
        INNER JOIN salesman cm
        ON cm.arid = jb.arid
        WHERE wp.jwid IN (1,3,4,5,6) AND (ac.includeascost)
        AND jc.saleid IN (Select saleid from ira)
               AND cm.salesman IN ('+@salesman+')
               AND iv.saledate BETWEEN ' + QUOTENAME('01/01/2016', '''') + ' AND ' + QUOTENAME('12/31/2016', '''') + ')'

EXEC sp_ExecuteSQL @Query


Try

SELECT * FROM mytable WHERE REGEXP_LIKE(mycolumn,'string',1,'i')

Will work if your Db2 version supports regular expression functions
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061494.html
and if you escape any regex meta-characters in your string


Multiple database servers for performance vs failover

If you are using mySQL, and you are not concerned about possible differences between your two databases, albeit for a short while, you could just set up mySQL Replication.

This is different to the active-active clustering you have asked about.  Active-active clustering implies that 2 or more nodes are actively hosting services, but in a mySQL / SQL Server context, you will not have explicit "synched" data in they way you mean. Instead this could be better achieved by replication or SQL2012 AlwaysOn (if you considered using it).


Generally:

10k records is tiny. I use loops in a script to bulk delete becasue I want to keep transactions smaller, but I do deltes of 64 million rows each loop.

Generally:

Loops of TOP X, outside a transaction, are more effective than deleting all rows if you do not care about the order of deletes. Reason simply is that every delete is then a separate transaction. Particularly in staging environments when you can for whatever reason not use truncate, this can be good to keep the tx log smaller. It also avoids longer locking because, again, smaller transactions.

But 10k rows is tiny to start with.


You didn't upgrade the database. You're likely on a system that permits different versions of PostgreSQL to run concurrently and you likely have two versions of the server running on two different ports. Usually 5432..543x. You'll have to

use pg_dump on the server running with the old port (the one you're connecting to here).
use pg_restore on the server running with the new port (that you probably didn't even know was running)

Try

sudo rgrep -i "port =" /etc/postgresql

Or, if you have Debian/Ubuntu, run pg_lsclusters


Query For All Constraints in a SQL Database Diagram

In SSMS, if you expand the tree of tables for your database, then expand the Columns folder, the icon next to a column will be a silver/gray key if it's part of a foreign key relationship, but unfortunately it won't show you the column to which column it's mapped.

You could also create a new diagram in SSMS by right-clicking the "Database Diagrams" folder underneath your database in the tree and choosing "New Database Diagram." You will get lines between the tables where foreign keys exists.

You could also use third-party tools to reverse engineer a diagram from your DB schema, like Microsoft Visio or Sparx Enterprise Architect.

I'm not sure SSMS Express supports these things, so you might be out of luck with anything fancy/visual.


Reverse connect by prior level value for arbitrarily-deep hierarchy

You can't ever query for ROWNUM = 3; ROWNUM 1 gets filtered out, so the next record is now ROWNUM = 1, and so on.

I suspect being worried about controlling the computational depth is premature optimization.  Unless you have millions of options being returned for a single user, pulling the highest level out of the data set - or even pulling the highest level for each dataset should be pretty simple.  It's not as if you can't fully explore the hierarchy, so the additional work in the SQL engine of prioritizing/ranking each window of rows is pretty light on top of that.

So using aggregate/window functions seems like a natural choice here.

Your SQLFiddle doesn't return the dataset expected, but I've modified the SQL below to show how you can prioritize them, assuming I understand the requirements you're looking for.

Analytics rock.

select menu_level, menu_name, menu_id
from
(
select menu_level, menu_name, menu_id,
       row_number() over (partition by menu_name order by menu_level desc) as menu_priority
from
(
SELECT
    LEVEL AS menu_level,
    t.name AS menu_name,
    t.id AS menu_id
  FROM
    jhs_menu_items t, (
      SELECT
        jmi.id
      FROM
        jhs_users ju
      JOIN jhs_user_role_grants jurg ON
        ju.id = jurg.usr_id
      LEFT OUTER JOIN user_menu_preferences ump ON
        ju.id = ump.jhs_usr_id
      LEFT OUTER JOIN default_menu_preferences dmp ON
        jurg.rle_id = dmp.jhs_rle_id
      JOIN jhs_menu_items jmi ON
        -- Retrieve the user's preferred menu item, failing to the default
        -- if no preference is set.
        jmi.id = coalesce(
          ump.jhs_menu_items_id,
          dmp.jhs_menu_items_id
        )
      WHERE
        ju.username = 'adminname'
--      AND ROWNUM = 1
      ORDER BY
        dmp.role_priority_sort
    ) menu_preference

  -- Derive the menu hierarchy starting at the user's preference, going back to
  -- the root menu item.
  START WITH t.id = menu_preference.id
  CONNECT BY PRIOR t.mim_id = t.id
  )
)
 where menu_priority = 1
 order by menu_name

MENU_LEVEL  MENU_NAME   MENU_ID
        3   MenuTab1    1
        2   MenuTab2    2
        1   MenuTab3    3


Transition to innodb_file_per_table for 30+ databases

I honestly wouldn't bother unless you have a specific problem you are solving.
Its possible to do simpler with:

Shutdown
change setting innodb_file_per_table = 1
start
ALTER TABLE xxx FORCE for each table


How to decrease RAM usage of SQL Server 2012 without restarting Service

App based on this SQL Instance is acting up, being Not Responding or working very slow

You have a performance issue that you need to investigate. Read How to analyse SQL Server performance for a starter.

Do not change random settings, like memory, in hope you fix the problem. Analyze, find the root cause and fix it.

You're probably missing an index or more.

For the record: DBCC FREESYSTEMCACHE('ALL')


You're going to have to physically move the data - there is no getting around that. But I don't know who came up with the idea that doing so requires creating a clustered index - this just exchanges certain steps for others, and isn't going to require any different amounts of temporary space to accomplish than the method I outline below.

Why not create a second, empty heap on the new filegroup:

CREATE TABLE dbo.whatever_copy
(
  ... cols ...
)
ON [second filegroup];

Then script the indexes, constraints etc. from the original table, copy the data over, remove any constraints that need to be removed (e.g. foreign keys pointing to the original table), then drop the original table, and rename the new one. Finally, add the indexes and constraints to the new table.

 -- script out indexes, constraints etc.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

INSERT dbo.whatever_copy(cols) WITH (TABLOCK)
  SELECT cols FROM dbo.whatever;

 -- remove constraints etc.

DROP TABLE dbo.whatever;

EXEC sp_rename N'dbo.whatever_copy', N'whatever', N'OBJECT';

 -- add constraints etc.

COMMIT TRANSACTION;


How to set the first row of CSV data as column name in SQL Server?

Assuming you are importing a standard CSV, you can dynamically create the table as such:

DECLARE @sql NVARCHAR(MAX)
DECLARE @filePath NVARCHAR(MAX) = 'C:/SomeFolder/yourImportFile.csv'
DECLARE @tableName NVARCHAR(MAX) = 'yourTableName'
DECLARE @colString NVARCHAR(MAX)

SET @sql = 'SELECT @res = LEFT(BulkColumn, CHARINDEX(CHAR(10),BulkColumn)) FROM  OPENROWSET(BULK ''' + @filePath + ''', SINGLE_CLOB) AS x'
exec sp_executesql @sql, N'@res NVARCHAR(MAX) output', @colString output;

SELECT @sql = 'DROP TABLE IF EXISTS ' + @tableName + ';  CREATE TABLE [dbo].[' + @tableName + ']( ' + STRING_AGG(name, ', ') + ' ) '
FROM (
    SELECT ' [' + value + '] nvarchar(max) ' as name
    FROM STRING_SPLIT(@colString, ',')
) t

EXECUTE(@sql)

and then bulk insert the data as @scsimon suggested:

BULK INSERT dbo.yourTableName.
FROM 'C:/SomeFolder/yourImportFile.csv'
WITH (
    FORMAT='CSV',
    FIRSTROW = 2,
    ROWTERMINATOR = '\n',
    FIELDQUOTE= '"',
    TABLOCK
)


Execute grant for MySQL stored procedure revoked when stored procedure is altered

This happens because the stored procedure is actually dropped and recreated.  When the procedure is dropped it loses all its permissions.  Therefore, when the 'altered' version is created there are no permissions attached.

Your process to update the procedure needs to include a re-grant of the needed permissions.

I don't know your full environment, but here was a 2013 forum discussion for one MySQL tool set:
http://code.google.com/p/heidisql/issues/detail?id=3300


Add an index on the column photo_id  on both the tables- photos and photo_people.


select
    cp.objtype,
    st.text
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where cp.objtype = 'prepared'

That above query will return all of the prepared plans that are cached, as well as their corresponding SQL text.


Change postgresql data directory location

in Your way, also EXPORT PGDATA=/newdisk/newdata

but also You can move all files to the new disk, and mount this disk to old PGDATA path (if all disk for PGSQL only)

Edit your /etc/fstab:
/dev/disk_name    /usr/local/psql/data    auto   defaults    1   2


IF you just want to be able to restore a few tables and the volume is not to high, why not just make copies of them?

You do need to set up good auditing and security around all your tables to prevent data loss, in the event that users did manual updates on tables that are going to be restored because of a process failure or something similar.

It could be that despite that failure, the user modifications are still needed and valid.
Best would be to prevent any user changes in the period where processes run that could cause the need for a restore.


Trying to understand an RCSI example-The Potential Dangers of the Read Committed Snapshot Isolation Level

Under locking read committed it's very likely (but not guaranteed) that when two sessions run this code
BEGIN TRANSACTION
DECLARE @QtyRequired int, @QtyRemain int
SELECT @QtyRequired = 4
SELECT @QtyRemain = SUM(QTY) FROM Inventory WHERE ItemID = 796 AND LocationID = 1
IF @QtyRemain - @QtyRequired >= 0
BEGIN
    UPDATE Inventory SET Qty = Qty - @QtyRequired
    WHERE ItemID = 796 AND LocationID = 1
    -- Do other stuff in other tables or databases to check out the item
    WAITFOR DELAY '00:00:10'
    SELECT 'Checkout complete'
END
ELSE
    SELECT 'Not enough qty!'
COMMIT TRANSACTION

There will be enough difference in time that the second session runs
SELECT @QtyRemain = SUM(QTY) FROM Inventory WHERE ItemID = 796 AND LocationID = 1

after the first session has run
UPDATE Inventory SET Qty = Qty - @QtyRequired
WHERE ItemID = 796 AND LocationID = 1

And so the second session will be unable to acquire an S lock on the row, and will be blocked until the first session commits.  This isn't a guarantee, however, as it's quite possible that both sessions run
SELECT @QtyRemain = SUM(QTY) FROM Inventory WHERE ItemID = 796 AND LocationID = 1

before either of them runs the update.
With RCSI it's simply more likely that they will both read the same value for @QtyRemain as the SELECT is never blocked by the pending UPDATE on the other session, and just returns the "last-known-good" value for the row from the version store.
But since the locking read committed version has the same problem, albeit in a smaller window, they are both broken, and should be fixed by changing the first query to:
SELECT @QtyRemain = SUM(QTY) FROM Inventory with (UPDLOCK) WHERE ItemID = 796 AND LocationID = 1

Which guarantees that even if two sessions attempt the query at the same time, one will be granted the U lock, and the other will be blocked until the first session commits.


Subqueries' aliases same as main queries' aliases

It's OK for nested subqueries to use the same aliases as used in the parent query, although it might be a bit confusing for someone reading the code.  The name space for aliases on a nested subquery is separate from the name space on the parent.  For example the query below has a nested subquery b that also has an alias b used within it.  This would be potentially confusing to the programmer but fine with the DBMS engine:

   select a.foo
          ,b.bar
          ,b.BarCount
      from (select b.bar
                  ,count (*) as BarCount
              from BarTable b
              join OtherTable o
                on b.OtherTableID = o.OtherTableID
             group by b.bar) b
      join Foobar a
        on a.bar = b.bar

On a correlated subquery you have access to the parent's aliases, so the aliases must be unique across the parent query and correlated subquery.  If we take a correlated subquery such as the one below we have a single, global name space shared between the parent query and the correlated subquery:

select a.foo
      ,b.bar
  from Foobar a
  join Bar b
    on b.FooBarID = a.FooBarID
 where not exists
       (select 1
          from Bar b2
         where b2.BarCategoryID = b.BarCategoryID
           and b2.BarDate > b.BarDate)

The correlated subquery does not have an alias as it does not participate in a join as such1.  The references b and b2 for bar are both available to the subquery as correlated subqueries share their namespace for aliases with the parent.

1 Note that the optimiser may choose to use join operators within the plan behind the scenes, although the actual operation specified is a correlated subquery and not a join against a nested subquery.


Is there a version history for the Postgres upgrades made in the installation?

You're using Redhat, so it's stored under dnf history list. It's a function of the package manager, not the database. You can build your own PostgreSQL and install and that won't show under the package manager.

The database engine itself doesn't track revisions to itself in the database: and, it would be weird if it did. If Redhat issues an upgrade and restarts the server, the server is supposed to know that Redhat issued an upgrade? What if the upgrade was a minor fix that Redhat pushed out ahead of PostgreSQL?


Compute duration of an event based on consecutive values in another column

Here is one idea that you might want to try:
select max(ts) - min(ts)
from (
    select ts, w
       ,   row_number() over (order by ts)
         - row_number() over (partition by w order by ts) as grp
    from test
) as t
where w
group by grp;

The idea is to enumerate ts in two ways, first the complete set, then per w and calculate the difference (called grp).
If the difference (grp) changes it means that w changed. We can, therefore, pick the max(ts) - min(ts) within each grp, for groups where w = true.
The problem you are trying to solve is often referred to as an "island and gap" problem. I modified your fiddle with my query: Modified fiddle
You can try and compare the performance with your function. You probably want to add an index like:
CREATE INDEX x1 on test (ts, w);

EDIT: to calculate the total duration in seconds:
select EXTRACT(EPOCH from sum(dur))
from (
    select max(ts) - min(ts) as dur
    from (
        select ts, w
          ,    row_number() over (order by ts)
            -  row_number() over (partition by w order by ts) as grp
        from test
    ) as t
    where w
    group by grp
) as tt;

EDIT: I played around a bit more, see Updated fiddle, and added a bit more data (~ 70000 rows depending on the number of duplicates ). I can see that the results differ between queries, haven't examined that more closely though. But I'm a bit surprised that you say that the queries perform about the same. Looking at the plan for your query:
Aggregate  (cost=21650.65..21650.66 rows=1 width=4) (actual time=2389.576..2389.576 rows=1 loops=1)
  ->  Seq Scan on test  (cost=0.00..1190.40 rows=81840 width=9) (actual time=0.013..36.243 rows=68709 loops=1)
Planning Time: 0.097 ms
Execution Time: 2389.663 ms

and one with window functions:
Aggregate  (cost=18951.01..18951.02 rows=1 width=8) (actual time=163.179..163.179 rows=1 loops=1)
  ->  HashAggregate  (cost=18946.01..18948.51 rows=200 width=24) (actual time=160.594..162.306 rows=8447 loops=1)
        Group Key: t.grp
        ->  Subquery Scan on t  (cost=16183.91..18639.11 rows=40920 width=16) (actual time=106.327..146.336 rows=59271 loops=1)
              Filter: t.w
              Rows Removed by Filter: 9438
              ->  WindowAgg  (cost=16183.91..17820.71 rows=81840 width=17) (actual time=106.326..140.013 rows=68709 loops=1)
                    ->  Sort  (cost=16183.91..16388.51 rows=81840 width=17) (actual time=106.316..114.565 rows=68709 loops=1)
                          Sort Key: test.ts
                          Sort Method: external merge  Disk: 2296kB
                          ->  WindowAgg  (cost=7868.76..9505.56 rows=81840 width=17) (actual time=44.285..81.613 rows=68709 loops=1)
                                ->  Sort  (cost=7868.76..8073.36 rows=81840 width=9) (actual time=44.261..52.843 rows=68709 loops=1)
                                      Sort Key: test.w, test.ts
                                      Sort Method: external merge  Disk: 1288kB
                                      ->  Seq Scan on test  (cost=0.00..1190.40 rows=81840 width=9) (actual time=0.011..5.775 rows=68709 loops=1)
Planning Time: 0.224 ms
Execution Time: 164.052 ms

indicate that the latter is significantly more efficient
I'll see if I can spin up a container with Postgres and dump the data into a table and examine the different results
The index is probably not that useful since we are scanning the whole table anyhow.
Final edit:
I could repeat the diff with only 50 rows Fiddle, so I added the rows to a Libreoffice Calc sheet. It appears as if the window version and Libreoffice agrees on the result. For some reason, I got a negative number from your function. This may be due to that the first row is a single row. This is the data used in verification, perhaps you can just add a "t" row a couple of seconds before the first one to rule out errors due to a single row:
2020-07-27 15:55:37 t
2020-07-27 16:01:56 f
2020-07-27 16:09:17 t
2020-07-27 16:23:32 t
2020-07-27 16:28:47 t   00:19:30
2020-07-27 18:14:32 f
2020-07-27 18:30:26 t
2020-07-27 18:40:52 t
2020-07-27 19:01:18 t
2020-07-27 19:05:24 t
2020-07-27 19:16:16 t
2020-07-27 19:56:00 t
2020-07-27 20:34:05 t   02:03:39
2020-07-27 21:12:18 f
2020-07-27 21:58:53 t
2020-07-27 23:51:55 t
2020-07-28 00:42:24 t
2020-07-28 00:43:56 t
2020-07-28 01:15:08 t
2020-07-28 03:34:08 t
2020-07-28 06:20:28 t
2020-07-28 06:23:14 t
2020-07-28 06:30:51 t
2020-07-28 06:34:12 t
2020-07-28 06:38:41 t
2020-07-28 06:48:48 t
2020-07-28 07:49:39 t
2020-07-28 08:28:41 t
2020-07-28 09:32:42 t
2020-07-28 09:45:36 t
2020-07-28 09:57:46 t
2020-07-28 10:29:37 t
2020-07-28 11:53:21 t
2020-07-28 11:53:29 t
2020-07-28 11:54:35 t
2020-07-28 12:23:18 t
2020-07-28 13:10:48 t
2020-07-28 13:37:21 t
2020-07-28 14:22:14 t
2020-07-28 14:51:16 t
2020-07-28 14:52:12 t
2020-07-28 15:30:04 t
2020-07-28 16:34:43 t
2020-07-28 17:02:03 t   19:03:10
2020-07-28 18:11:49 f
2020-07-28 18:21:09 t
2020-07-28 18:41:38 t
2020-07-28 19:14:45 t
2020-07-28 19:16:01 t
2020-07-28 19:22:03 t   01:00:54

             Total      22:27:13        80833 seconds

Final edit 2:-)
I had a look at your function, and it appears as if you expect the rows to be ordered according to ts? If so, that is a false assumption since they may come in any order. If you do a simple test like:
SELECT ddur(ts, w) FROM (select ts, w from test order by random()) as t;

and compare with:
SELECT ddur(ts, w) FROM test;

the results are probably different. See Modified original Fiddle


SQL Server 2008 R2 enterprise named instance

Microsoft support Multiple instances on single windows machine so it is fully supported when you want to install another version of SQL Server same machine having other versions. But have you made sure that windows server/machine would perfectly support all four instances. Is the hardware resource optimum for your testing won't you face hardware crunch if all 4 instances are required and they are up and running.

Plus why are you still stuck up with SQL Server 2008 R2 edition. The link you posted in your question is pointing to SQL Server 2008 so are you installing SQL server 2008 or 2008 R2. Both does not falls under category of normal support but yes falls under category of extended support, and that too not for very long and soon will become totally unsupported. I would advise you to do your testing at least on SQL Server 2012 Evaluation edition or SQL Server 2014

  Given that I'm wondering if this should be done same as installing a new named instance or is it an upgrade?

You can do a normal installation but before moving please read carefully hardware and software requirement for installing SQL Server 2008 R2. If you are installing SQL Server 2008 R2 on windows 8 you must read this article before proceeding


How to write join query in sub query method?

I think this question will be fine here, though it could as well have been asked at SO. One nice property of the relational model is that it is closed under relational operations, that is, the result of a query is a new relation. I'll use Common Table Expressions (CTE), but you may as well use a subquery:
WITH type_counts (type, cnt) AS -- type is a bad identifier,
                                -- but I'll ignore that
( SELECT type, count(tag) AS count
  FROM tag_type
  group by type
), company_tag_info AS
( SELECT tag_company.tag, company.name, tag_type.type
  FROM company
  JOIN tag_company
      ON company.id = tag_company.company_id
  JOIN tag_type
      ON tag_company.tag = tag_type.tag
)
SELECT cti.*
FROM type_counts tc
JOIN company_tag_info cti
    ON cti.type = tc.type

If you just want information related to the top type, you can order by cnt and limit to one:
WITH type_counts (type, cnt) AS -- type is a bad identifier,
                                -- but I'll ignore that
( SELECT type, count(tag) AS count
  FROM tag_type
  group by type
), company_tag_info AS
( SELECT tag_company.tag, company.name, tag_type.type
  FROM company
  JOIN tag_company
      ON company.id = tag_company.company_id
  JOIN tag_type
      ON tag_company.tag = tag_type.tag
)
SELECT cti.*
FROM type_counts tc
JOIN company_tag_info cti
    ON cti.type = tc.type
ORDER BY tc.cnt DESC
LIMIT 1;

This can be further simplified, but I believe that the above is the most relevant part for your question.


Sql Server 2016 Cannot drop database (it is really, really, really stuck)

This issue is pretty tricky to reproduce so I can suggest to try two more solutions, but both require SQL Server to be restarted:

Start SQL Server in single user mode (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-in-single-user-mode)
(just add SQL Server service startup parameter -m and restart the service). Then using SQL CMD connect to the SQL instance using local administrator account and try to drop database using DROP DATABASE statement. Perhaps this time you won't get an error related to exclusive access acquisition.
Another way (pretty primitive I know! But it might be effective either!) is to stop SQL service and delete (or change location) of database files. Then when you start SQL Server back this database should come up in 'recovery pending' status and you should be able to remove it.


Does the function inside the Select clause evaluate the passed in columns before the where clause?

The references in the function's WHERE clause are comparing with the identically named columns in its SELECT clause, rather than the argument list. Try changing it as follows (note the addition of ARG_ to the argument names and the predicates in the WHERE clause):

create or replace function        func_get_Open_Days_2
   ( ARG_SUBMITTED_BY_PERSON_ID in number,
  ARG_SUBMISSION_DATE in date,
  ARG_FORM_VERSION_DATE_TIME in timestamp,
  ARG_FORM_ID in number
   )
RETURN number

IS
   n1 number:=0;
   n2 number:=0;

   cursor records is
      SELECT
        SUBMITTED_BY_PERSON_ID,
        SUBMISSION_DATE,
        FORM_ID,
        FORM_VERSION_DATE_TIME,
        AUDIT_DATE_TIME,
        STATUS_DATE,
        SUBMISSION_STATUS_CODE,
        CREATE_USER_ID
      from form_state_audit_trail t
      where
        t.submitted_by_person_id = ARG_SUBMITTED_BY_PERSON_ID and
        t.form_id = ARG_FORM_ID and
        t.submission_date = ARG_SUBMISSION_DATE and
        t.form_version_date_time = ARG_FORM_VERSION_DATE_TIME and
        t.submission_status_code NOT IN (3469, 3462);
      order by t.status_date asc;


View crossing multiple databases, but a query only using one is slow

Never mind,

I just went with sp_executesql

Performance issue are gone, readability gone

SET @myInst = '[db2Name]'
SET @sqlReplaced = REPLACE('myQry','{@myInst}',@myInst)

EXECUTE sp_executesql
@statement                  = @sqlReplaced,
@ParmDefinition             = @ParmDefinition,

@clientNo                   = @clientNo,
@lastTransactionDate                = @lastTransactionDate,
@msInstanceNo               = @msInstanceNo,
@dateFrom                   = @dateFrom ,
@dateUntil                  = @dateUntil


Index re/organize/Rebuild Report using Ola

You can find answer for this question at Ola Hallengren website itself. He has put lot of effort to make DBA's life easier and provided lot of parameters in order to customize and run the script as per user's requirement.

SELECT DatabaseName,
       SchemaName,
       ObjectName,
       CASE WHEN ObjectType = 'U' THEN 'USER_TABLE' WHEN ObjectType = 'V' THEN 'VIEW' END AS ObjectType,
       IndexName,
       CASE WHEN IndexType = 1 THEN 'CLUSTERED' WHEN IndexType = 2 THEN 'NONCLUSTERED' WHEN IndexType = 3 THEN 'XML' WHEN IndexType = 4 THEN 'SPATIAL' END AS IndexType,
       PartitionNumber,
       ExtendedInfo.value('(ExtendedInfo/PageCount)[1]','int') AS [PageCount],
       ExtendedInfo.value('(ExtendedInfo/Fragmentation)[1]','float') AS Fragmentation,
       CommandType,
       Command,
       StartTime,
       EndTime,
       CASE WHEN DATEDIFF(ss,StartTime, EndTime)/(24*3600) > 0 THEN CAST(DATEDIFF(ss,StartTime, EndTime)/(24*3600) AS nvarchar) + '.' ELSE '' END + RIGHT(CONVERT(nvarchar,EndTime - StartTime,121),12) AS Duration,
       ErrorNumber,
       ErrorMessage
FROM dbo.CommandLog
WHERE CommandType = 'ALTER_INDEX'
ORDER BY StartTime ASC


Are page numbers of a book a good analogy for a clustered index?

Unfortunately I don't think there is a proper StackExchange for theoretical questions, but hopefully you gather good enough information here
Typically the "Phonebook" makes for a good analogy of how indexes work, not only because of the pages sorted by Names (i.e. the nodes of the B-Tree) but the sorting of the LastName, FirstName of the individual People within a specific page being representative of the leaves of the B-Tree. Brent Ozar has a good in-depth article on how indexing works that utilizes this analogy.


Is there an actual problem in my storage IO or is the data incorrect?

You need to divide io_stall_read_ms by num_of_reads, and io_stall_write_ms by num_of_writes. This will give you something more sensible as what you are seeing for read and write stalls and sum totals since your instance last stated up.

However, using this DMV in this manner can average out any spikes and hide patters, therefore you need to take samples from it every so often. Also I would recomend putting looking at your page life expectancy in conjunction with read latencies, go off Jonathan Kehayias's formula from SQL Skills.

Note that you need to look at PFE per NUMA node and if your application is OLAP you are unlikely to get a great PFE, your sequential scan rates being the more important metric here.