Is database record access sequential after retrieving the indexed page from disk?

In SQL Server, for a regular index (i.e. not columnstore or Hekaton) the storage engine can choose one of two methods to find the start or end point of a search. The first option is a binary search using the offset array at the end of the page. The second option is to use linear interpolation.

After the search start/end point is found, any range-scan portion of the search is essentially sequential. More specifically, the engine saves a 'cookie' to remember the search position.

When the next row is needed by the execution engine, the storage engine validates that the cookie is still valid (i.e. a concurrent change hasn't affected the physical structure) and proceeds to find the next matching record from that point, using the offset table and previous/next page links as necessary, searching either forward or backward. If the cookie is invalid, the engine effectively re-seeks to find the correct current position.

A read-ahead mechanism is used to help ensure pages needed by a range scan are present in memory before the page is needed by the execution engine.


MySQL Daemon failed to start - Can't find file: './mysql/plugin.frm'

The answer maybe in the question. "Can't find file: './mysql/plugin.frm'"

Make sure that file exists and permissions are correct, which you did.
Next, SELinux may interfering (see this post)

You may test the SELinux theory by running the following:

setenforce 0
service mysql start

To permanently turn off SELinux, follow the steps here.


Integration Services Catalogs - Version Description

I assume you're describing this window

What the answer wasn't

Folder's have descriptions, you can set them via the catalog.set_folder_description stored procedure. In my example, my folder is called SSISHackAndSlash and my project is SSISHackAndSlash2012.

I called the above method and looked at the properties of the folder and they match.

I then tried to use the same call but using the Project name but no dice

EXECUTE catalog.set_folder_description
    @project_name
,   @folder_description = 'called from set_folder_description'

  Msg 27104, Level 16, State 1, Procedure set_folder_description, Line
  73 Cannot find the folder 'SSISHackAndSlash2012' because it does not
  exist or you do not have sufficient permissions.

That's the only public proc in the list of all the SSISDB Stored Procedures that mentions description.

The actual deploy proc, catalog.deploy_project doesn't have a parameter for description.

What the answer is

Since the answer wasn't in the public catalog and it lead me to believe it either wasn't an exposed property or it's something in the actual project itself. Occam's razor wins.

Win

If you like futzing with the .ispac directly, in the @Project.manifest, the property is serialized under the <SSIS:Properties> node as <SSIS:Property SSIS:Name="Description"></SSIS:Property>


What is the performance difference between local text index and domain text index in oracle?

Sean, I believe that the local text index is part of the Oracle Text feature of Oracle that is specific to working with documents, such as Web sites, digital libraries, document or warehouses. While the Domain Indexes are indexes that we use in day-to-day.

The following documentation will be helpful:
About Oracle Text:
http://docs.oracle.com/cd/B28359_01/text.111/b28303/overview.htm
(In the third chapter there is information about the indexes)

About the Domain Indexes:
http://docs.oracle.com/cd/B10500_01/appdev.920/a96595/dci07idx.htm


MariaDB Inserts are getting slower and slower (7x tables, ~ 2.8M and 200MB)

I feel embarrassed :/, turned out that there were missing important indexes :(

Run for around night with ~ 1800k/s inserts (for endpoint, from mysql there would be probably 6 inserts + 4 reads for each endpoint insert, so ~ 10k/s of inserts + 8k/s reads).

In total over 150M records were created, > 30GB of data, no slow downs, still going fast.

RAM usage ~ 100% that was set for MySQL (32GB).

Really happy with it now :)

Of course replication will be another though decision, but for anyone else.

Make sure that proper indexes are in place, performance here degrade quickly.


Build dynamic text search on table columns

One way to do this using a set-based approach:

IF OBJECT_ID(N'tempdb..#MyTable', N'U') IS NOT NULL
DROP TABLE #MyTable;
CREATE TABLE #MyTable
(
    Id INT,
    Number INT
);

INSERT #MyTable VALUES (6, 3), (7, 3), (8, 3), (16,15), (20, 15);

DECLARE @cmd nvarchar(max);
SET @cmd = '';

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE ' OR ' END + N'(
    Number = ' + CONVERT(nvarchar(max), mt.Number) + N'
    AND Id IN (' + STUFF((SELECT ', ' + CONVERT(nvarchar(max), mt1.Id) FROM #MyTable mt1 WHERE mt1.Number = mt.Number FOR XML PATH ('')), 1, 2, '') + N')
    )'
FROM #MyTable mt
GROUP BY mt.Number

SET @cmd = N'SELECT Id, Number
FROM #MyTable
WHERE ' + @cmd + ';
';

PRINT @cmd;

The results:

SELECT Id, Number
    FROM #MyTable
    WHERE (
        Number = 3
        AND Id IN (6, 7, 8)
        ) OR (
        Number = 15
        AND Id IN (16, 20)
        );


AlwaysOn availability group and Replication - expected behaviour

Actually according to Microsoft this is expected behaviour:

When the publisher has only two availability replicas (one primary and one secondary) and a failover happens, the original primary replica remains down because the logreader does not move forward until all secondary databases are brought back online or until the failing secondary replicas are removed from the availability group. The logreader, now running against the secondary database, will not proceed forward since Always On cannot harden any changes to any secondary database. To allow the logreader to proceed further and still have disaster recovery capacity, remove the original primary replica from the availability group using ALTER AVAILABITY GROUP  REMOVE REPLICA. Then add a new secondary replica to the availability group.

Quoted from:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability?view=sql-server-2017


SQL Server pivot filter underlying tables by aliases specified in view

Not as it stands, you will have to add an index for it and that will never be able to scan only the 2011 column from the pivoted view - adding index onto that view is not possible and will give an error Msg 10114, Level 16, State 1, Line 17 Cannot create index on view "a.dbo.vw_PivotedTest" because it uses the PIVOT operator. Consider not indexing this view..

Then you need to create an index for the underlying data to get the Pivot operator to run faster. Let's look at if the Optimizer has something to say.

Your sample data is really small so run your query like this.

SELECT ProductID, CountryID, [2011]
 FROM dbo.vw_PivotedTest
 option (OPTIMIZE FOR UNKNOWN);

Which will optimize the query for a larger set, you can also add GO 50 after the insert statement into the dbo.TestData table to run the insert statements 50 times and thereby adding more data and then you can skip the option part.

You will then get the following index suggestion from the optimizer:

CREATE INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TestData] ([TestID])
INCLUDE ([TimeSeries],[Values]);

Which will help but will scan on the ProductID - which makes sense, you can also create that index like this

CREATE NONCLUSTERED INDEX idx2
ON [dbo].[TestData] ([TestID],[TimeSeries],[Values]);

But you will end up with the same queryplan and more expensive inserts so there is little to gain.


BACKUP failed to complete - with spaces, for no reason

Well, it looks like 3041 is the generic "something went boom with the backup" error.

Have you tried just running the backup SQL from SQL Agent without using the wrapping stored procedure?  I'm assuming the stored procedure you're using for the backup lives in the master DB (or at least not in the DB you're backing up).

Also, you could use this query to check your backup status (last 7 days).  There might be something else going on that you don't know about and that's interfering with your backups.

SELECT
 CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
 msdb.dbo.backupset.database_name,
 msdb.dbo.backupset.backup_start_date,
 msdb.dbo.backupset.backup_finish_date,
 msdb.dbo.backupset.expiration_date,
 CASE msdb..backupset.type
   WHEN 'D' THEN 'Database'
   WHEN 'L' THEN 'Log'
 END AS backup_type,
 msdb.dbo.backupset.backup_size,
 msdb.dbo.backupmediafamily.logical_device_name,
 msdb.dbo.backupmediafamily.physical_device_name,
 msdb.dbo.backupset.name AS backupset_name,
 msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
  msdb.dbo.backupset.database_name,
  msdb.dbo.backupset.backup_finish_date


Assuming seconds always are '00.000':

WITH cte AS ( SELECT Id,
                     DATEADD(mi, -DATEPART(mi, TSDest) % 5, TSDest) TSDest,
                     Value,
                     ROW_NUMBER() OVER (PARTITION BY DATEADD(mi, -DATEPART(mi, TSDest) % 5, TSDest)
                                        ORDER BY TSDest ASC) rn
              FROM test )
SELECT Id, TSDest, Value
FROM cte
WHERE rn = 1
ORDER BY TSDest

fiddle

Added by OP

Thank you very much, this was very helpful.
BTW, because I had records with seconds and milliseconds, I had to add cast the time
to "smalldatetime" which did the trick very well,

WITH cte AS ( SELECT Id,
                         cast(DATEADD(mi, -DATEPART(mi, TSDest) % 5, TSDest) as smalldatetime) TSDest,
                         Value,
                         ROW_NUMBER() OVER (PARTITION BY cast(DATEADD(mi, -DATEPART(mi, TSDest) % 5, TSDest) as smalldatetime)
                                            ORDER BY TSDest ASC) rn
                  FROM test )
    SELECT Id, TSDest, Value
    FROM cte
    WHERE rn = 1
    ORDER BY TSDest


Replication can break or misbehave in all sorts of fun and exciting ways.  You need to monitor for three things:

1. Replication is running and has not stopped due to error

To monitor whether or not replication is running is simply a matter of programmatically checking SHOW SLAVE STATUS and looking at the values for Slave_IO_Running and Slave_SQL_Running.  Both should be "yes".  pmp-check-mysql-replication-running from the Percona Monitoring Plugins for Nagios is written for this task.

2. Replication is performing well (slave lag behind master is within an acceptable range)

You need to make sure that the slave has not lagged behind the master by too far.  "Too far" is determined by what your application can tolerate and by how many binary logs you keep on the master server.  Because replication on the slave is single-threaded, slaves can easily get lagged behind.  SHOW SLAVE STATUS has the Seconds_Behind_Master value, but is not a reliable indicator of actual lag, and frequently will jump around.  In order to accurately measure replication lag, you need an external application to insert a timestamp into a table periodically.  You can then measure that value from the slave and compare it against the current time to get actual replication delay.  pt-heartbeat is a daemon that will insert a heartbeat into a table on your server.  You can then alert on that value with pmp-check-mysql-replication-delay to make sure it is within your specified parameters.

3. The data on the servers is in sync.

There are many ways that a master and slave can get out of sync so that the data differs.  You need to detect those differences and correct them periodically because a small difference can, over time, turn into a very large difference, especially with statement-based replication.  This is no small task, and pt-table-checksum is designed to calculate these differences.  Run this weekly.  pmp-check-pt-table-checksum is a Nagios plugin to alert when the slave has data discrepancies relative to the master.  To actually fix the differences, use pt-table-sync.

pt-table-checksum has been recently rewritten and is pretty easy to use.  pt-table-sync has a lot of options and can be confusing.  Read the documentation for these thoroughly as you can really shoot yourself in the foot if you aren't careful.  Here is a webinar about these tools.

  Another thing: Can I run a separate db in my replication db which I just run for testing purposes?

There is nothing preventing you from modifying (or supplementing) the data on the slave, though generally I would recommend against it.  Best practice is to have the slave be read_only=1.  However, real life tends to trump best practices and often slaves are used as reporting servers.  My suggestion would be to make very clear access privileges for those using the slave for data modification and to have all additional tables in a separate schema.


VACUUM ANALYSE not reducing index bloat, only VACUUM FULL

VACUUM typically does not shrink the tables or indexes.
From v12 on, you can use REINDEX CONCURRENTLY for that.
Before, you would create a second index with CREATE INDEX CONCURRENTLY, then drop the old index. But that wouldn't work for a constraint index.
But 50% bloat like in this case is quite normal and healthy for a b-tree index, and I wouldn't worry about it.


You can use a window function for this:

select a,b,c
from (
  select a,b,c,
         dense_rank() over (order by a,b) as rnk
  from dbTable
) t
where rnk = 1;

For the "first" rows, it doesn't matter if you use rank() or dense_rank(). When you e.g. want the "second" ones, the rank() and dense_rank() would return different results in case of ties. Because rank() will have "gaps" in the numbers, but dense_rank() will not.

A possible speedup might be achieved by doing this in two steps and of course having an index on (a,b)

with ranked as (
  select *
  from (
    select a,b,
           dense_rank() over (order by a,b) as rnk
    from dbTable
  ) t
  where r.rnk = 1  -- (or <= for "top-k")
)
select t.a, t.b, t.c
from dbTable t
   join ranked r on r.a = t.a and r.b = t.b;

The idea is to give Postgres the chance to do an index-only scan for the ranking part and then join only the result of that to the base table to get the remaining column(s). The filtering on the rank is done inside the CTE as Postgres doesn't push down conditions from the outer query into the CTE itself (that's why I have the derived table inside the CTE)

I'm not sure if this really improves the performance, but I guess it would be worth trying and have a look a the execution plan with the real tables (and data).


Migrate SQL Server backup to MySQL for a Wordpress site

Good luck.  You'll need to do way more work than just a "restore".

Essentially, you'll need to create a custom pipe to convert the data in the SQL Server database into the table structure used by WordPress.

Even if the table structure was identical, and it isn't, you can't "restore" a .bak file into a MySQL database.  The .bak does not contain SQL statements; it is a binary representation, page by page, of the SQL Server database.

Presuming there aren't a huge number of articles, it is generally considered easier to manually transfer them into WordPress using the interface.  Welcome to the thrill of vendor lock-in.

If you have access to a SQL Server, you might try restoring the .bak file then exporting the desired data as SQL Statements using the "script database" facility.  You might then be able to do a transformation on the SQL Statements to make them "fit" the design of the WordPress database.  I'd be very skeptical that this would be easy, however at a certain point this will take less time than manually recreating the stories in WordPress.


Two-way replication between SQL Server 2005 and SQL Server 2008

From your comment update, it sounds like Merge replication could work well for you, mostly due to the minimal updates. It's a lot simpler to set up and manage than two-way transactional (which is what the scary link I mentioned deals with--I should have left that for later).

In a nutshell: https://blogs.technet.microsoft.com/meamcs/2011/01/06/merge-replication-step-by-step/


SQL Job step run as different sql account

If you are configuring a T-SQL job step go to the Advanced Page and configure the "Run as user" to the login of your choice.

If you are working with other job step types like PowerShell it will require a proxy account to be configured.


For two triggers(before insert and after insert),is it allowed for you to use where in the NEW keyword

You will notice that triggers in MySQL include FOR EACH ROW.  The values in NEW alias always, only, ever refer to the specific row that invoked the trigger. There is no ambiguity.

If multiple rows are inserted in a single query, the trigger fires individually for each row, so in each iteration through the trigger you can test any of the NEW columns.


The solution is to Leave extra room.

When shrinking the MDF files, if I leave some unused space (do not remove ALL unused space) then it finishes considerably faster.

For example: If a 3TB database has only 200MB used, shrinking it to 200MB would take a very long time. However if I shrink it to 300MB then the shrink process is much faster.

I'm guessing SQL must defragment the data to the extreme in order to remove ALL unused space. However by leaving extra room it must only reorganize larger sections of data or otherwise adjust the file pointers to fit.


You should be GRANT-ing to a ROLE, not the individuals.
One exception to this:  one Schema is creating code that reference another schema's table.

Grant to Multiples schemas/roles at once

This is legitimate code:
GRANT INSERT ON SCOTT.EMP TO ADAMS,ALLEN;

If you are on 11g or higher, you can use LISTAGG() to generate the comma separated list

declare
  local_variable  varchar2(2000);
begin
  select listagg( e.ename,',' ) within group (order by e.empno)
    into local_variable
  from scott.emp e
  where e.ename like 'A%';
end;
/

But, you'll need to limit the number of rows.  LISTAGG will error if it is over a certain length (eg 4000 bytes for 11g)

LOOPing

You'll want to LOOP over your table:

begin
  for curr in ( select e.ename from scott.emp e order by e.ename)
  loop
    dbms_output.put_line( 'grant for ' || curr.ename );
  end loop;
end;
/

Master's Technique

OK, ok.  This is my technique.  (maybe others)

Use one of the many freely available template based code generators.
This is probably only useful if you are going to do this on a repetitive cycle as new tables are created.

Again, you should be granting to a ROLE, not individual users.


Structuring change history data in MongoDB / NoSQL

As per @DANIEL WATROUS Blog here The size of documents and the frequency with which they are changed must factor in to the retention of historical data.

  Should I log an entire chart with all the items as a single document,
  every time there is a change? (Although I read there may be a limit on
  document size, so this could be a problem.)

As per your JSON code

{
    {"Id": 1},
    {"Title": "Test Chart"},
    {"Items": [
        {
            "Id": 222,
            "Title": "Test Item",
            "Price": 112.34
        }
    ]
}}

As MongoDB BOLhere The maximum BSON document size is 16 megabytes.

Each document in MongoDB has a specifically allocated size when it is created. Updates that increase the size of the document must allocate a new document large enough to accommodate the updated document on disk and move the document. This can be an expensive operation to perform, especially at high volume.

  Should I log Chart changes as one type of document and Chart Item
  changes as a separate type of document? This would allow me to store
  changes to chart items as a single "line item" document without having
  to save the entire Chart every time there is a change to a single
  chart item.

There are a few options when it comes to dealing with uniquely identifying items data. One is to calculate a unique value at the time an object is placed in history. This could be a combination of the top level object ID and a sequential version number. Another is to generate a hash when the object is loaded. The problem with the second approach is that queries for specific date objects become more complex.

For your further ref here


Cannot Login to Fresh Installation of SQL Server 2012

Yup, that last little comment was it. Even though you can select a domain group for the Instance administrators, it will create this problem. You must add individual user accounts. Thanks for the help and pointers!

In the end I put it into single user mode and ran the following command from an elevated prompt:

sqlcmd -S localhost from the command prompt

EXEC sp_addsrvrolemember
'domain\username', 'sysadmin'; GO

exit

Restarted the SQL services normally and went right in.


How does SQL Server know what index to use?

Answer originally left as a comment by i-one:

Your query references ProductId, VariantId, StoreId, ChainId and LocalInventory columns.

All of them except ChainId are declared in the non-clustered index definition.

ChainId is included in the non-clustered index implicitly as part of the clustered index key.

So, the non-clustered index is covering for this query.

The nonclustered index contains the clustered index keys because SQL Server must be able to locate the base table row from the index entry.

From the SQL Server Index Architecture and Design Guide:

  If the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table. This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. For example, if a table has a clustered index on column C, a nonclustered index on columns B and A will have as its key values columns B, A, and C.


how can be changed hot_standby parameter on postgres 9.6 aws/rds?

Ideally the parameter hot_standby should be on only for slave server and pg_is_in_recovery() should be false for master server.

You can follow below steps to make these changes:

Check postgresql.conf file on master server and if the parameter hot_standby is set to on you can change it to off and then restart the server.
On the master server in the data file location, if there is a file called recovery.conf then just delete that file and restart the server.

Hope this will help.


You would need a table for the individual bills, too, would you not?

Table 1: individual bills with external key to table 2
Table 2: Apartments (are "houses" the same as apartments?) with external key to Table 3
Table 3: City information

Your keys would be whatever unique identifier you have set up for each item.

If houses can hold multiple apartments, then you will need to have another table inbetween Table 2 and Table 3.

and yes, this really are one-to-many relationships between all mentioned tables.
You cannot have a single bill that can go to multiple apartments, and an apartment cannot be in multiple cities (or houses).


Using Oracle client to connect to remote database

There are different ways to connect oracle. One of them is easy connect .

You may use

sqlplus username/password@server/SID

According to comments. You can connect oracle using easy connect but hr (human resources) account does not exists.
Therefore try to use

sqlplus username/password@10.66.1.61:1521/OPTPROD

You should be able to connect your oracle system.


Why is table opened every time a query is executed against it?

Perhaps the caption of that step isn't entirely accurate and what it is doing is "obtaining handle to access tables"? In that case the first call performs the relevant IO to open the table (read its metadata and prepare anything that is needed in memory) before returning the handle(s) so the next step can start accessing the resource, and the second time the routine will see that it already has all that done.

It is common (and generally good design, keeping stages decoupled from each other's detailed logic) for routines not to know when the sub-routine they call actually does some real work or just returns a cached resource. Good development avoid early optimisation (make it work, then make it work fast) so initially the code would go to disk every time but later versions include the caching logic.

Another alternative: maybe it really is redoing everything every time, but the database's page cache and/or the OS disk cache mean it is faster because there is less physical IO?


SQL Server 2005 Easy way to add all users to a database role

Adam Machanic has a blog post on replacing xp_execresultset which in SQL 2000 did what you want: Replacing xp_execresultset in SQL Server 2005


How do I select arrays that are not empty?

The reason seems to be that empty strings in that column have array dimensions [1:0]. Should normally be NULL. See:

SELECT lexemes, array_dims(lexemes) FROM ts_debug('a title');

 lexemes | array_dims
---------+------------
 {}      | [1:0]  -- !!
         |
 {titl}  | [1:1]

Empty arrays normally have NULL as array dimensions.

SELECT '{}'::text[] AS test, array_dims('{}'::text[]);

 test    | array_dims
---------+------------
 {}      | <NULL>

Hence, the comparison lexemes = '{}'::text[] returns FALSE. Looks like a bug to me. I tested versions 8.4 - 10 Beta. It's in all versions.

As a workaround, to exclude all empty arrays including the odd case (and NULL):

SELECT *
FROM   ts_debug('This is a title')
WHERE  cardinality(lexemes) > 0;

Or compare the text representation:

...
AND    lexemes::text <> '{}';

I filed bug report #14826.


How is ‘tempdb’ getting used when Always On Failover happens?

Tempdb doesn't move when an AG failover occurs. Each replica is its own standalone instance. Each has its own tempdb. Queries will break during the failover. Once the application reconnects after the failover is complete and crash recovery is also complete, the queries can then run on the other instance where it has its own tempdb.

A Failover Cluster Instance fails over at the instance level, so tempdb is affected. But not for Availability Groups.


Trying to insert only unique items into one table and then create a many to many relationship in a join table only if that join does not exist

You could use NOT EXISTS as your first insert (or LEFT JOIN / IS NULL) for the second insert to work like the first, insert only when the row doesn't exist. Another way is with EXCEPT:

INSERT INTO B (Name)
SELECT firstname FROM @temptable
EXCEPT
SELECT Name FROM B ;

INSERT INTO AtoB (AId, BId)
SELECT @tableAid, B.Id FROM B JOIN @temptable T ON B.Name = T.firstname
EXCEPT
SELECT Aid, Bid FROM AtoB ;


On my local computer, where is the data stored for a search on a dynamodb database

The search is made on the server host where the database service is installed. And you will only get the result set sent to you.
  It would make no sense your client to make this, as your client is just an interface that allows your to interact with your database.
 This is available for all databases.


Need to make MySQL's LOAD DATA LOCAL INFILE load large data faster

I'll bet that you are currently I/O bound.  This means that nothing can speed it up.  (And Rolando's suggestions may be futile.)

Let's look deeper.  Is this LOAD a recurring task?  If so, how often?  Is everything blocked waiting for table to be reloaded?  Simple solution:  Load into a different table, then do a double RENAME TABLE to swap it in.  Only milliseconds of downtime.

Is the data coming from another machine?  Use the network for the "input" side of the LOAD rather than having the one disk fighting for reads versus writes.

Do you have a lot of indexes?  There are several directions to take this question.  Let's see SHOW CREATE TABLE before barking up these tree(s).

Does the entire load need to be a single transaction?  Multiple transactions may be faster because of not overflowing the log file.  (I've seen 2x.)


Does SQL Server cache calculated values in a query?

The SQL Server query optimizer can combine repeated calculated values into a single Compute Scalar operator. Whether or not it will do this depends on query plan costing and the properties of the calculated value. As expected, it will not do this for calculated values which are nondeterministic, which a few exceptions such as RAND(). It also will not do this for user defined functions.

I will start with a user defined function example. Here is an excellent example of a user defined function:

CREATE OR ALTER FUNCTION dbo.NULL_FUNCTION (@N BIGINT) RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN
RETURN NULL;
END;

I also want to create a table and to put 100 rows into it:

CREATE TABLE X_100 (N BIGINT NOT NULL);

WITH
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO X_100 WITH (TABLOCK)
SELECT n
FROM Nums WHERE n <= 100;

The dbo.NULL_FUNCTION function is determistic. How many times will it be executed for the following query?

SELECT n, dbo.NULL_FUNCTION(n)
FROM X_100;

Based on the query plan this will be executed once for each row, or 100 times:

SQL Server 2016 introduced the sys.dm_exec_function_stats DMV. We can take snapshots of that DMV to see how many times a UDF is executed by a query.

SELECT execution_count
FROM sys.dm_exec_function_stats
WHERE object_id = OBJECT_ID('NULL_FUNCTION');

The result of that is 100, so the function was executed 100 times.

Let's try another simple query:

SELECT n, dbo.NULL_FUNCTION(n), dbo.NULL_FUNCTION(n)
FROM X_100;

The query plan suggests that the function will be executed 200 times:

The results of sys.dm_exec_function_stats suggest that the function was executed 200 times.

Note that you cannot always use the query plan to figure out how many times a compute scalar is executed. The following quote is from "Compute Scalars, Expressions and Execution Plan Performance":

  This leads people to think that Compute Scalar behaves like the majority of other operators: as rows flow through it,  the results of whatever computations the Compute Scalar contains are added to the stream.  This is not generally true.  Despite the name, Compute Scalar does not always compute anything, and does not always contain a single scalar value (it can be a vector, an alias, or even a Boolean predicate, for example).  More often than not, a Compute Scalar simply defines an expression; the actual computation is deferred until something later in the execution plan needs the result.

Let's try another example. For the following query I would hope that the UDF is calculated one time:

WITH NULL_FUNCTION_CTE (NULL_VALUE) AS
(
SELECT DISTINCT dbo.NULL_FUNCTION(0)
)
SELECT n , cte.NULL_VALUE
FROM X_100
CROSS JOIN NULL_FUNCTION_CTE cte;

The query plan suggests that it will be calculated one time:

However, the DMV reveals the truth. The compute scalar is deferred until it is needed, which is in the join operator. It is evaluated 100 times.

You also asked what you can do to encourage the optimizer to avoid recalculating the same expression multiple times. The best thing that you can do is to avoid using scalar UDFs in your code. Those have a number of performance issues outside of this question, including inflating memory grants, forcing the entire query to run with MAXDOP 1, bad cardinality estimates, and leading to additional CPU utilization. If you do need to use a UDF and the value of that UDF is a constant you can calculate it outside of the query and to put it in a local variable.

For queries without UDFs, you can try to avoid writing expressions which return the same result but aren't typed in exactly the same way. For this next example I'm using the publicly available AdventureworksDW2016CTP3 database, but really any database will do. How many times will COUNT(*) be calculated for this query?

SELECT OrderDateKey, COUNT(*)
FROM dbo.FactResellerSales
GROUP BY OrderDateKey
ORDER BY COUNT(*) DESC;

For this query we can figure this out by looking at the Hash Match (aggregate) operator.

The COUNT(*) is computed once for each unique value of OrderDateKey. Including the ORDER BY clause does not cause it to be calculated twice. You can see the execution plan here.

Now, consider a query that will return the exact same results but is written in a different way:

SELECT OrderDateKey, SUM(1)
FROM dbo.FactResellerSales
GROUP BY OrderDateKey
ORDER BY COUNT(*) DESC;

The query optimizer is not smart enough to combine them, so additional work will be done:


Will AlwaysOn work with hundreds of databases in 1 group/multiple groups?

Increase the hardware capacity or look for Cloud Solutions, if possible. This is from official doc:

Maximum number of availability groups and availability databases per computer: The actual number of databases and availability groups you can put on a computer (VM or physical) depends on the hardware and workload, but there is no enforced limit. Microsoft has tested up to 10 AGs and 100 DBs per physical machine, however this is not a binding limit. Depending on the hardware specification on the server and the workload, you can put a higher number of databases and availability groups on an instance of SQL Server. Signs of overloaded systems can include, but are not limited to, worker thread exhaustion, slow response times for availability group system views and DMVs, and/or stalled dispatcher system dumps. Please make sure to thoroughly test your environment with a production-like workload to ensure it can handle peak workload capacity within your application SLAs. When considering SLAs be sure to consider load under failure conditions as well as expected response times.

I had working experience with huge databases (size), not large in numbers( Availability Groups and databases)
From my experience and based on the research, there is no limit to number of Availability Groups and number of databases by SQL instance or AG. If you have sufficient hardware capacity, you can host any number of databases. If there is not enough memory, even in SQL cluster you will face the issue.


How to select subset JSON with PostgresQL?

You can remove the empty values during aggregation. And use an array operator in the final select to get only rows that contain a specific key:

SELECT tmp.*
FROM (
  SELECT id,
         ARRAY(SELECT t.k
               from json_each_text(json_data) as t(k,v)
               where nullif(trim(t.v),'') is not null) AS keys
  FROM json_table
) tmp
WHERE keys && array['foo','bar']
;

If you want the complete JSON values, rather than just the keys, you can use something like this:

SELECT tmp.*
FROM (
  SELECT id,
         (SELECT jsonb_object_agg(t.k, t.v)
               from json_each_text(json_data) as t(k,v)
               where nullif(trim(t.v),'') is not null) AS keys
  FROM json_table
) tmp
WHERE keys ?| array['foo','bar']
;

(Note that I am using jsonb for the result in this case as that is much more flexible to handle. In general it's recommended to prefer jsonb over json these days)

If you need to cleanup the json frequently, you might want to consider writing a function for that:

create function non_empty_values(p_input jsonb)
  returns jsonb
as
$$
  SELECT jsonb_object_agg(t.k, t.v)
  from jsonb_each(jsonb_strip_nulls(p_input)) as t(k,v)
  where trim(t.v::text) <> '';
$$
language sql
immutable;


Why not rebuild indexes with page count <1000?

The guidance concerning the minimum number of pages is somewhat arbitrary. The biggest benefits of reducing fragmentation are:

It can improve read-ahead performance for large range scans; and
It may improve the page density (number of rows per page)

Both of these factors are less important for small indexes, by definition.

The counter-argument to rebuilding small indexes is essentially:

  "Why bother? Don't you have more important things to worry about?".

That said, rebuilding/reorganizing is not free. It may be worth avoiding the extra effort and log generation in some cases (for example, if the log is shipped/copied across a WAN for any number of possible reasons - mirroring, availability groups, replication...). Also, unless (or even if, in some cases) you are rebuilding online, the rebuild may impact other concurrent processes through locking. Finally, for small indexes, rebuilding may not even reduce the fragmentation anyway, due to allocations from mixed extents (unless you are running with trace flag 1118 enabled).

If you still feel happier rebuilding these small indexes, and don't mind the consequences, then by all means change the value of the @PageCountLevel parameter passed to Ola's procedure.

See the PASS TV recording of Paul Randal's presentation on Index Fragmentation for all the details.

You might also like to watch Brent Ozar talk about Why Index Fragmentation doesn't matter in SQL Server.


MySQL constraint to prevent combinations of columns

you can use Triggers for that.

CREATE TABLE files_dir (
  `dir` VARCHAR(5),
  `file` VARCHAR(6)
);

INSERT INTO files_dir
  (`dir`, `file`)
VALUES
  ('dir_A', 'file_0'),
  ('dir_A', 'file_1'),
  ('dir_A', 'file_1'),
  ('dir_B', 'file_2'),
  ('dir_C', 'file_3');

CREATE TRIGGER before_file_insert
    BEFORE INSERT ON files_dir
    FOR EACH ROW
 BEGIN
 IF EXISTS(SELECT 1 FROM files_dir WHERE `file`= NEW.`file` AND `dir` = NEW.`dir`) then
  BEGIN
  END;
 ELSE
    IF EXISTS(SELECT 1 FROM files_dir WHERE `file`= NEW.`file`) THEN
          SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = "file has another directory";
    END IF;
   END IF;
 END;

 INSERT INTO files_dir
  (`dir`, `file`)
VALUES
('dir_A', 'file_1');

✓

 INSERT INTO files_dir
  (`dir`, `file`)
VALUES
('dir_B', 'file_1');

file has another directory

SELECT * FROM files_dir;

dir   | file
:---- | :-----
dir_A | file_0
dir_A | file_1
dir_A | file_1
dir_B | file_2
dir_C | file_3
dir_A | file_1

db<>fiddle here
For an BEFORE UPDATE TRIGGER you can do something eual.
Of course you can always change your insert statement with a join like ypercubeᵀᴹ already said.


Mongodb incremental oplog Dump not working

You are essentially attempting to dump the entire oplog and hence paging in all of it, not just the most recent data.  That is going to cause a lot of IO, especially when your oplog is too large to fit into memory.  On a smaller database it's not a problem because the oplog is relatively small.

The reason why it's dumping the full table is because I believe you are missing some zeroes at the end of your time:

> new Date(1452157469)
ISODate("1970-01-17T19:22:37.469Z")

I assume you wanted this:

> new Date(1452157469000)
ISODate("2016-01-07T09:04:29Z")

The speed this dump happens will depend on a number of factors, but mostly it will depend on how much data has been inserted into the oplog recently and how much of it is still in memory when you start the dump.


Error on import of mysqldump file - Illegal double value found during parsing

There is current open bug report on this one.

The bug report has a suggested work around at the bottom entry [22 Jan 2010 6:46]:

replace 1.79769313486232e+308 \'1.79769313486232e+308\' -- filename

Give it a Try !!!


Is it impossible to iterate table rows in Oracle PL/SQL without using a cursor?

Valid cursor attribute name is one of  %NOTFOUND, %FOUND, %ROWCOUNT, %ISOPEN. There is no %rownum attribute . In your question you use implicit cursors, so it should be
 sql%rowcount.


Non-deterministic functions and row-based binary logging (MySQL)

Your understanding is correct. RBR is safe even with non-deterministic functions.


Getting "ORA-00942: table or view does not exist" while table does exist

You created the table in the SYS schema (which you should never, ever do. Really, never).

When you log in as teacher1 any statement looks for objects in that schema. But there is no TEACHER1.INSTRUCTORS table, because the real name is SYS.INSTRUCTORS (did I mention what a bad idea it is to create objects in the SYS schema?).

You need to run select * from sys.instructors to get access to that table. If you don't want to prefix the table name with the schema, create a synonym in the teacher1 schema:

create synonym teacher1.instructors for sys.instructors;

Then teacher1 can access the table from the SYS schema without fully qualifying it.

Again: stop using the SYS or SYSTEM account for anything that is not DBA stuff. Use a regular account for that.


MySQL Select rows with same id but different multiple values in another colum

You can get it by using GROUP BY and COUNT(*)

create table users (user_id int, interest varchar(20));

insert into users values(1, 'sport');
insert into users values(2, 'it');
insert into users values(3, 'game');
insert into users values(1, 'it');
insert into users values(1, 'game');
insert into users values(3, 'it');
insert into users values(3, 'sport');

SELECT user_id
FROM   users
WHERE  interest IN ('game', 'it', 'sport')
GROUP BY user_id
HAVING count(*) = 3;

| user_id |
| ------: |
|       1 |
|       3 |

dbfiddle here


Is it better to put filters on JOIN clauses instead of WHERE clauses

With any good query planner (and IIRC mysql's is good enough in this regard) for queries where all joins are inner joins (as your's is here, unless otherwise specified joins default to inner joins), it does not matter whether you put the filtering or joining comparisons in an ON clause or in the WHERE.

In the presence of outer joins more complicated queries this is a lot less clear cut as the position of the filtering clause can (but does not always) have a significant effect depending on the input data.

To convince yourself, generate a large data-set and try both to see if there is any difference. As a learning exercise to get a good feel for what the optimisations in your chosen DB engine can and can't do, very little beats some well thought out experimenting, monitoring and benchmarking.


SQL Server 2012 Resource Governor Enhancement

This is a good question, because there is a lot of misleading information out there.

The changes to what memory Resource Governor can manage in SQL Server 2012 roughly coincide with the changes to what memory the Max Server Memory setting can manage. Prior to SQL Server 2012, this was just buffer pool / single-page allocations. In SQL Server 2012 this is extended to multi-page allocations. I lifted this diagram from Memory Manager surface area changes in SQL Server 2012:

And while the statement is a little over-promising IMHO, the SQL OS team also stated in New SQLOS features in SQL Server 2012:

  Resource Governor governs all SQL memory consumption (other than special cases like buffer pool)

When I first started doing research on the changes to this feature, the indication I had was that it could manage all memory allocations except buffer pool and column store cache. Prior to SQL Server 2012, Resource Governor only had control over query grant memory.

There are of course other important enhancements to Resource Governor in SQL Server 2012, that I thought I'd mention for other readers who get here because of your question title. You can now:

have 64 resource pools instead of the previous limit of 20
use real CPU capping (even with no contention) - the previous model relied on concurrency before implementing a cap
implement scheduler / NUMA node affinity (e.g. tie a resource pool to a specific NUMA node)

Also note there are some differences with how memory manager changes have been implemented in 32-bit vs. 64-bit. Some relevant info here.


There's no global GRANT in Teradata, you can only grant on a database level.

That's one of the reasons why there's a superuser (usually named sysdba) who is the root of all databases:

dbc
 |...
 |-SYSDBA
 |...   |-myDB
 |...   |-myOtherDB
        |-anotherDB
        |-userA   |-subDB
                  |-anotherSubDB
                  |-UserB
                  |...

This user is an owner of all databases/users below him in the hierarchy and as owner you can Grant/Revoke any right on any owned object.


Postgres: SELECTing bytea data partially with offset and length

Of course, just use the substring function:

select substring(bytea_contents from offset for length) FROM table;

or get_byte(bytea_contents,offset) to get a single byte as an integer.

See Binary String Functions and Operators in the doc for all functions.


I have a beautiful idea for you on this one...

If you can setup the following:

Circular Replication

Two DB Servers
Each Server is a Master to the Other
Each Server is a Slave to the Other

Use a DBVIP that will point to one DB Server
Confine all Reads/Writes to the DBVIP

You can perform this OPTIMIZE TABLE maintenance on the DB Server that does not have the DBVIP.

First, construct the script on the Slave (the DB Server that does not have the DBVIP) that will run OPTIMIZE TABLE on all your tables. Then, run it on the Slave:

MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} OptimizeTableSQL FROM information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
SQL="${SQL} ORDER BY (data_length+index_length)"
echo "SET sql_log_bin = 0;" > /root/OptimizeAllTables.sql
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> /root/OptimizeAllTables.sql
mysql ${MYSQL_CONN} < /root/OptimizeAllTables.sql

The first line of the script is SET sql_log_bin = 0;. This tells the session not to record OPTIMIZE TABLE in its binary logs. This prevents OPTIMIZE TABLE from running on the Master (the DB Server with the DBVIP). Once a month, you can just take down the DBVIP from one DB Server and bring up the DBVIP on the Other DB Server. When you do that, you must also setup the OPTIMIZE TABLE script to on the Other Machine. The key is to run this on the DB Server that does not have the DBVIP.

Give it a Try !!!


Any SQL where clause performance similar to where 1=0

You are looking to choose rows from one of two tables dynamically. This is generally possible to achieve without dynamic SQL.

To demonstrate, here is a simplified version based on the AdventureWorks sample database:

The idea will be to choose rows from either the TransactionHistory or TransactionHistoryArchive tables for each Product, based on the value of the UseArchive column in a Parameter table:

CREATE TABLE dbo.Parameter
(
    ProductID integer NOT NULL PRIMARY KEY,
    UseArchive bit NOT NULL
);

INSERT dbo.Parameter
    (ProductID, UseArchive)
VALUES
    (1, 0),
    (2, 0),
    (3, 1),
    (4, 0);

This example specifies that rows should come from TransactionHistory for products 1, 2, and 4. For product 3, rows should come from TransactionHistoryArchive.

Solution:

SELECT
    P.Name,
    CA.TransactionID
FROM Production.Product AS P
JOIN dbo.Parameter AS PTR
    ON PTR.ProductID = P.ProductID
CROSS APPLY
(
    SELECT TH.ProductID, TH.TransactionID
    FROM Production.TransactionHistory AS TH
    WHERE
        TH.ProductID = P.ProductID
        AND PTR.UseArchive = 0

    UNION ALL

    SELECT THA.ProductID, THA.TransactionID
    FROM Production.TransactionHistoryArchive AS THA
    WHERE
        THA.ProductID = P.ProductID
        AND PTR.UseArchive = 1
) AS CA;

The execution plan for this query is:

The key here is the outer reference PTR.UseArchive = value inside the apply. This allows the optimizer to construct a plan featuring Filters with Start-Up Expressions. For example, the Filter above the TransactionHistory table Index Seek has:

The Filter Predicate above the other seek is very similar, of course, but tests UseArchive = 1.

Each Filter Predicate is evaluated once per product. It determines whether the seek below it in the plan will be executed on that iteration of the nested loop join.

In this example, the effect is that the TransactionHistory index seek is executed three times; the seek on the TransactionHistoryArchive table is executed just once.

You may be able to implement this pattern for your use case.


Decompose Relation R(ABCDE) in 2nf and 3nf

The following two FD's violate 2NF:

A->C
B->D

We can create new relationships based upon those FD's: (AC;A->C), (BD;B->D), (ABE;AB->E).  This set of relationships now satisfies 2NF (and also 3NF and BCNF).


Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP

I have no idea why you would want to ORDER BY CURRENT_TIMESTAMP. If the table has a column where you stored CURRENT_TIMESTAMP when the row was created, you just need to say:

ORDER BY that_column_name;

Or if you want newest first:

ORDER BY that_column_name DESC;

... in other words, forget how the column was populated.

Now, if the comments are accurate, and you're actually not concerned so much about ordering but rather about filtering, then perhaps what you want is:

-- create a DATE variable based on today:

DECLARE @s DATE = CURRENT_TIMESTAMP;

-- convert it to the first of the month
-- (you can do this in the step above,
-- just separating it for clarity):

SET @s = DATEADD(DAY, 1-DAY(@s), @s);

-- use >= and < in a WHERE clause, and order by that column
-- (however this is just a coincidence, order by and filtering
-- are completely unrelated in this case:

SELECT [?] FROM dbo.[?]
  WHERE [datetime_column] >= @s
  AND [datetime_column] < DATEADD(MONTH, 1, @s)
  ORDER BY [datetime_column];

Here is why you don't want to use BETWEEN, in case anyone sends you that way:

https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common

Here is why you want to use built-in date functions and a native DATE type and not convert to a string to trim time from a datetime variable:

http://www.sqlperformance.com/2012/09/t-sql-queries/what-is-the-most-efficient-way-to-trim-time-from-datetime
http://www.sqlperformance.com/2012/10/t-sql-queries/trim-time

And here is some general information about bad practices in date/range queries:

https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries
https://www.sentryone.com/blog/aaronbertrand/backtobasics-dating-responsibly
https://www.mssqltips.com/sqlservertip/5206/sql-server-datetime-best-practices/


Query cycles between suspended and runnable without ever completing

Explanation of behavior

Some of the causes of the IO_COMPLETION wait type are:

  Writing intermediate sort buffers to disk (these are called ‘Bobs’)
  Reading and writing sort results from/to disk during a sort spill

There are two sorts that could be spilling, which uses tempdb.

A source of the slowness could also be one the unfortunate "many to many merge join" in the middle of the execution plan (which also uses tempdb, although it doesn't cause IO_COMPLETION waits):

The estimated plan shows ~2 GB of data coming out of that merge join - and that amount of data could be even higher if the estimates are off.

You mentioned the problem is intermittent, which could be because of tempdb contention (if other queries are running at the time).

Suggestions for improvement

Temp table rewrite

The best option I can think of would be to break the query up into smaller chunks.  For instance, you could just select the trans_header rows that meet your where clause into a #temp table.  Then use that temp table in the main query instead of trans_header.

This could improve estimates and let the optimizer produce a better plan - potentially avoiding any spills or other tempdb activity.

That, generally, looks like this.  Sorry if there are any slight typos, it's tough not having intellisense

SELECT
    a.[term_id],
    a.[trans_ref_no],
    a.supplier_no,
    a.cust_no,
    a.trans_id,
    a.carrier,
    a.folio_yr,
    a.folio_mo,
    a.folio_no,
    a.transaction_date
INTO #trans_header_temp
FROM [TOPHAT].[trans_header] AS a with (nolock)
WHERE
    a.folio_yr=year(getdate())-1
    AND a.transaction_date <>' 00000';

INSERT INTO [TOPHAT].[terminal_volume]
           ([term_id]
           ,[terminal_name]
           ,[folio_yr]
           ,[folio_mo]
           ,[folio_no]
     ,[folio_year_month]
           ,[product_id]
           ,[prod_name]
           ,[product_id_name]
           ,[supplier_no]
           ,[supplier_name]
           ,[supplier_no_name]
           ,[customer_no]
           ,[customer_name]
     ,[carrier_no]
     ,[carrier_name]
           ,[bbl]
           ,[gallon]
           ,[customer_no_name]
           ,[trans_code]
           ,[transaction_description]
           ,[transaction_code_description]
           ,[transaction_year]
           ,[transaction_month]
           ,[transaction_date]
           ,[getdate])
select a.[term_id],
 tp.name as terminal_name,
 a.folio_yr,
 a.folio_mo,
 a.folio_no,
 cast(a.folio_yr + '-' + a.folio_mo +'-01' as date) as folio_year_month,
 p.prod_id as product_id,
 p.prod_name as prod_name,
 p.prod_id+' '+p.prod_name  as product_id_name,
 s.supplier_no,
 s.supplier_name,
 s.supplier_no+' '+s.supplier_name as supplier_no_name,
 cu.cust_no as customer_no,
 cu.cust_name as customer_name,
 a.carrier as carrier_no,
 ca.name as carrier_name,
 sum((convert(decimal(12,2),b.net)*(case when b.sign is null then 1 else -1 end))/42) as 'bbl',
 sum(convert(decimal(12,2),b.net)*(case when b.sign is null then 1 else -1 end)) as gallon,
 cu.cust_no+' '+ cu.cust_name as customer_no_name,
 tv.trans_code,
 tv.trans_desc as transaction_description,
 tv.trans_code+' '+tv.trans_desc as transaction_code_description,
 '20'+ left(a.transaction_date, 2) as transaction_year,
 substring(a.transaction_date, 3, 2) as transaction_month,
  case when isdate(a.transaction_date) = 0 then null
   else
   cast(a.transaction_date as date) end as transaction_date,
 cast(getdate() as date)  as [getdate]
   from
#trans_header_temp as a with (nolock)
 inner join [TOPHAT].[terminal_profile] as tp  with (nolock)
  on a.term_id = tp.term_id
 inner join [TOPHAT].[trans_products] as b with (nolock)
  on a.[trans_ref_no] = b.[trans_ref_no]
 inner join [TOPHAT].[product] as p with (nolock)
  on p.term_id = a.term_id
  and p.prod_id = b.prod_id
 inner join TOPHAT.supplier as s with (nolock)
  on s.supplier_no = a.supplier_no
 inner join TOPHAT.customer as cu with (nolock)
  on cu.cust_no = a.cust_no
  and cu.supplier_no = a.supplier_no
 inner join TOPHAT.trans_value as tv with (nolock)
  on tv.trans_code = a.trans_id
 inner join [TOPHAT].[carrier] as ca with (nolock)
  on ca.term_id = a.term_id
  and ca.carr_no = a.carrier

group by
a.[term_id],
tp.name ,
a.folio_yr,
a.folio_mo,
a.folio_no,
p.prod_id,
p.prod_name,
p.prod_id+' '+p.prod_name,
s.supplier_no,
s.supplier_name,
s.supplier_no+' '+s.supplier_name,
cu.cust_no,
cu.cust_name,
cu.cust_no+' '+ cu.cust_name,
a.carrier,
ca.name,
tv.trans_code,
tv.trans_desc,
tv.trans_code+' '+tv.trans_desc,
'20'+ left(a.transaction_date, 2),
substring(a.transaction_date, 3, 2) ,
a.transaction_date,
cast(a.folio_yr + '-' + a.folio_mo +'-01' as date)

Fix implicit conversions

Speaking of estimates, you have several implicit conversion warnings.  It's difficult to advise on how to deal with them, since we don't have table and index definitions, but you should review them to see if they can be avoided.  Especially the ones in the WHERE clause:

Join hint

One low-effort approach would be to try and avoid that specific merge join with a join hint.  This might cause performance to get worse, though, because it will force the order of the joins as written, which limits the optimizer can do quite a bit:

 inner HASH join [TOPHAT].[product] as p with (nolock)
  on p.term_id = a.term_id
  and p.prod_id = b.prod_id

Note that this wouldn't likely help with sort spills.


Ambiguity in Auto Create\Update Statistics

No ambiguity there.

If you set 'Auto Update Statistics' to 'False' statistics will not be updated automatically.

If you set 'Auto Update Statistics' to 'True' they will be updated according synchronously or asynchronously according to to the 'Auto Update Statistics Asynchronously' settings.


How to query for something like "last active" per column in Access

I would have....

| Hostname | Timestamp | Status |
|----------|-----------|--------|
| Host1    | 19.02.2017| up     |
| Host2    | 19.02.2017| down   |
| Host3    | 19.01.2017| down   |

Then you could have another table...

| ID | Hostname | Location       |
|----|----------|----------------|
| 1  | Host1    | Living Room    |
| 2  | Host2    | Bedroom        |

When you run queries, it's easy to find all of Host1's activities, or you could easily find all the activities for 19.02.2017. Yes you have a million rows, but that doesn't matter.


Will Postgres handle the Unix Epoch problem?

If you look at https://www.postgresql.org/docs/11/functions-datetime.html in the documentation it displays data about the to_timestamp function that returns a timestamp with time zone value from an epoch value. Complete description is: "Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp"

The input type of the function is double precision.

From https://www.postgresql.org/docs/11/datatype-numeric.html you can see that double precision is defined as 8 bytes with 15 decimal digits precision.
A little down below you can read:

  The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits.

So 1E+308 seconds after 1970-01-01 should carry you far after 2038...

In fact if you look at https://wiki.postgresql.org/wiki/TodoDone84, which lists all features that have been done in PostgreSQL 8.4 you can see:

  Completed item: Extend timezone code to allow 64-bit values so we can represent years beyond 2038


what is difference between uniform extents and mixed extents in Database file storage structure?

There is no difference between what is stored on a page, between uniform and shared extents. It is as simple as that the first 8 pages for an allocation (a table, index, ...) comes from shared extents.

Back in the mid-90s when the "new" architecture was done, MS decided that they didn't want to waste a whopping 64 kB (8 pages) for each allocation. Back then we didn't have partitioning, and if we also ignore LOB data, we can equate allocation to "each index and also the actual data". I.e., imagine a table with 4 indexes (say it is a heap, but if that doesn't mean anything to you, just ignore the nuances). With uniform extents, each of these 5 allocations (one for the data and one each for the index) would use as a minimum 64 kB each. I.e., at least 5 * 64 kB = 320 kB, even if you only have one row.

So, they decided that the first 8 pages comes from mixed extents. Now, each of these 5 allocations only use 1 page (instead of 1 extent), i.e., 40 kB instead of 320 kB for this example.

The years fly by, and as of SQL Server 2016, MS decided that saving these measly kB doesn't buy us that much, so nowadays SQL Server allocate uniform extents from the very start. I.e., no more "first 8 pages comes from shared extents". You can revert to the old behavior using a database option if you want.

Shared extents still exists, though. For instance the IAM pages still comes from shared extents.


Linked Server - login trying to access database that is not default

I would also change the parameters as follows:

@srvproduct = 'sql_server'
@provider = 'SQLNCLI' if you're coming from 2005 (and 'SQLNCLI10' from 2008/8R2, and 'SQLNCLI11' from 2012, etc.)
@provstr can be removed entirely; it's not necessary with this product and provider; your linked server logins will handle that.

Whatever SQL Server login is being used by the linked server probably has an offline/removed default database.  Change the default database for that login to one that exists and that the user has permissions to and try again.

This is a guess, as you didn't post the CREATE USER script that generated the particular username logging in.


The Oracle built in function called LAST_DAY can help you here.

It can be used with dates or timestamps and an example based on your table description is:

SELECT * FROM TABLE1
WHERE EXTRACT(DAY FROM DDATE) = EXTRACT(DAY FROM LAST_DAY(DDATE))
AND EXTRACT (MONTH FROM DDATE) = 1;

There are many ways to work with dates in Oracle but I like using EXTRACT as it works with dates and timestamps and means you don't have to use the TO_CHAR function.


What's the best way to go about searching for short names?

A trick that sometimes is useful...

MATCH is usually the fastest way to search
MATCH has some drawbacks
LIKE and RLIKE are very slow
When using both MATCH and LIKE, MATCH is done first, thereby providing very few rows to recheck with LIKE:

WHERE MATCH(...) AGAINST(...)
  AND ... LIKE '%...%'

(I am not familiar with how NGRAM works, so I can't provide specifics there.)

From the 5.7.23 and 8.0.12 Changelogs:  "The ngram full-text search parser permitted comma and period characters to be tokenized as words, which caused an inconsistency between boolean and natural language mode search results. Comma and period characters are no longer tokenized."  (This implies that 5.7.23 has a fix for it.)


SQL Server job to use external Powershell

As requested, here is an example of the process.  The step should look like this, though

Job Step Command
Set the step to be a cmdexec job and then run powershell through that.  It lets you use the latest version of powershell as well as configure the powershell environment for that user (if you want).  The step command will look like the one below, complete with arguments for the example.  Remember to put quotes around strings that contain spaces.

Powershell.exe -noprofile -executionpolicy bypass -file "C:\Scripts\DailyRestoreScript.ps1" -Arguments -SourceSQLInstance 'localhost\jdf2016' -SourceDatabaseName 'AdventureWorks' -TargetSQLInstance 'localhost\jdf2016' -TargetDatabaseName 'AdventureWorks2'

Complete Job

USE [msdb]
GO

/****** Object:  Job [DailyRestore_ADWorks]    Script Date: 2/16/2017 9:00:51 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2/16/2017 9:00:51 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DailyRestore_ADWorks',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Daily restore AD Works from backup',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name='XXXXX\JFite', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Restore ADWorks]    Script Date: 2/16/2017 9:00:51 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Restore ADWorks',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'CmdExec',
        @command=N'Powershell.exe -noprofile -executionpolicy bypass -file "C:\Scripts\DailyRestoreScript.ps1" -Arguments -SourceSQLInstance ''localhost\jdf2016'' -SourceDatabaseName ''AdventureWorks'' -TargetSQLInstance ''localhost\jdf2016'' -TargetDatabaseName ''AdventureWorks2''',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


How to view contents of table in mysql-workbench

After you create a connection to your database, execute the following two commands:
  USE <DATABASE NAME>;
  SELECT * FROM <TABLE NAME>;

Then MySQL Workbench will show another pane with the results.  This will be the entire contents of the table.


I've had improved performance by using bulkWrite:

db.ig_posts.bulkWrite([
   { deleteMany: { "filter": { /* your query here */ } }
]);


I think it's better that timesheetEntry table should reference to job table as

JobId will be PK of Job table and so associating timesheetEntry with job table will provide better referential integrity of data


Query user session length on a timestamped activity log

Plan A:

Create a table with an AUTO_INCREMENT PK, plus the 2 columns in question.  (It can't be a TEMPORARY table due to an unfortunate limitation.)
Do a "self-join" to pair up adjacent rows.  Put this (id, user, and pair of times) in another table.
Filter -- keep pairs that are more than 1 hour.  (Need to also keep first and last times; this may involve UNION.)
Subtract adjacent id pairs to get session_length.  (This probably involves another table and another AUTO_INCREMENT, but could be done with an @variable.)  (Note:  Be aware of auto_increment_increment; if it is not 1, the subtract will be 'wrong'.)

So, Yes, it can be done in SQL; but do you want to go to this much work?

Plan B:  Somehow use @variables to count the session_length, watch out for change in userid, test for 1 hour, etc.

Plan C:  Migrate to MariaDB 10.2 and use its Windowing Functions.


Mongodump: Unrecognized field 'snapshot'

Add --forceTableScan
Example:
mongodump --forceTableScan -d database_name -o target_directory

http://aug2uag.blogspot.com/2018/08/mongoexport-unrecognized-field-snapshot.html

The Error comes from the mongo snapshot functionality which was introduced in mongo 4.0 (https://docs.mongodb.com/manual/reference/read-concern-snapshot/)
If you use an older mongo client (<4.0) to dump/export a more recent MongoDB you will get the error

Unrecognized field 'snapshot'

Using "forceTableScan" will prevent the snapshot functionality to become active, this you won't get the error


SQL Server 2012 replication permissions with virtual user and agent credentials

IT kind of seems like the Agent account didn't have access to that filepath.  On our network permissions to the Program Files directory are very restricted.  When you created the new folder you probably changed the security on the new folder, whether realizing it or not, and that allowed the account for the replication to reach that folder.


How to properly troubleshoot Merge Replication False Conflicts

I found a solid answer to your question here on Database Administrators by Brandon Williams:
How to troubleshoot SQL Server Merge Replication Conflict?
Brandon said:

You need to inspect the Conflict Viewer and observe the conflict types and the conflicting row values. Something to note is that the Conflict Winner column will display the winning row values which are pulled from the base tables. If you think about it, these values are volatile and can change, so the values you see in the Conflict Winner column may not be the values they were when the conflict occurred, keep this in mind. The Conflict Loser column will display the losing row values which are pulled from the MSmerge_conflict_publication_article table and these values are static.
If you are unable to determine the root cause of the conflicts based on the Conflict Viewer, you may need to implement an auditing scheme. I have provided an example of how to do this in Auditing Changes in Merge Replication.
There is a small chance you are getting false conflicts but auditing should reveal whether or not that is the case.


How to access data inside a "before insert trigger" for every statement

The doc for CREATE TRIGGER in PostgreSQL 10 says:

  Triggers that are specified to fire INSTEAD OF the trigger event must
  be marked FOR EACH ROW, and can only be defined on views

Assuming nvme_ts is a view, you're trying to define a FOR EACH STATEMENT trigger in a case that the doc explicitly disallows.

Besides, the code shown in create_ts_partition() is meant for a single row, not for multiple rows with potentially different years.
You probably simply need for a FOR EACH ROW trigger, if you're set on using a trigger at all to manage the partitions.


Give this a try

IF object_id('tempdb..#temp1') IS NOT NULL
    DROP TABLE #temp1

CREATE TABLE #temp1 (
    [Current Service State] NVARCHAR(200)
    ,[SQL_Server_last_RestartOn] NVARCHAR(200)
    )

INSERT INTO #temp1 ([Current Service State])
EXEC master.sys.xp_servicecontrol N'querystate'
    ,N'MSSQLSERVER'

UPDATE #temp1
SET SQL_Server_last_RestartOn = (
        SELECT min(login_time)
        FROM master..sysprocesses
        )

SELECT *
FROM #temp1


Why does SQL Server Management Studio crash?

To fix the error, you should understand the actual error which is not displayed in the screenshot that you've provided in your question. You can view the actual error by reading the application events within the event viewer when the error occurs.

Start by writing down the timestamp when the error happens.
Open Event Viewer
View the application logs and match the error message with the timestamp when the error occurred.

It's pretty common knowledge that Microsoft displays friendly errors to users when errors occur by default which generally leads the end user to questions that cant be answered, friendly error messages only complicate questions. You need to view the source. Feel free to ask your question again so that a professional can provide you a better professional response from a professionally written question with all the required dependencies.


Will my query pick up where it stopped due to connection timeout?

MySQL is an ACID database. A in this term stands for Atomicity. This basically mean:

  Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes. To the outside world, a committed transaction appears (by its effects on the database) to be indivisible ("atomic"), and an aborted transaction does not happen.

So, you should not worry at all. If your transaction timed out nothing will be written to database. Re-run your transaction


This is an ongoing debate that many face, and experts will give good arguments for either of these.  What your question boils down to is a surrogate vs. natural key (and option two seems to be a half-surrogate half-natural key).

As for your third option, oftentimes I see people do whatever they can to find a unique candidate for a primary key constraint.  Take you for instance:  name is your example key.  That is a notoriously bad candidate for a key, as there is no uniqueness guaranteed with that.  Think "John Smith".  Not only that, but having that as a key will bloat your foreign key relationship, as when you go to reference that primary key in a relationship table then you will have that same data type and column width.  Think nvarchar() as opposed to a narrow data type (i.e. int).

As for your second option, it sounds like you have the ability to create a unique value (by your description, able to fit in an int column).  Where would you plan on doing this?  This would either be logic in the database or in the application.  In my opinion, I like to keep entities like keys isolated to the data tier. This "id" would most likely be used for normalized data.  That should be an abstraction to the application (in my opinion).  So now my reasoning brings me to either a database-generated unique value (your option two) or an identity value (your option one).  Those both have the same result (as far as I can see it), therefore why go through the trouble and the development to generate your own when you can just have an IDENTITY column?

Again, others may and probably will have different and equally objective answers for going with another alternative, but my vote would be for the IDENTITY column for a unique, surrogate key.


Storage of final entry values in read only table

Your queue records depend on values of other tables referenced via foreign keys. The values you lookup in this reference tables is about to change over time.

You want to be able to check on processed records with wich values they were processed, especially which lookup values where used in the processing that were valid at the time of processing.

In that case you must copy the value in your archive/log table, otherwise as you pointed out your processed-table references the current lookup values, not the ones that were valid at the time of processing.

Versioning your lookup values based on start_dat,end_date depends on if you need the history of these tables even when nothing was processed with these values. If you only need the values if there are processed records i would go with the denormalized log table structure. Much simpler.


The main problem most of the time is buffer_pool_size, key_buffer_size , etc in My.cnf
This in depth post from @RolandoMySQLDBA should solve your problem:

Why does MySQL say I'm out of memory?


best way for creating Index in mysql (multi-column/table)?

See if this runs faster:

SELECT  n.ID, n.LATITUDE, n.LONGITUDE,
        wn.WAYID
    FROM
      ( SELECT  ID, LATITUDE, LONGITUDE
            FROM  `node`
            WHERE  LATITUDE  BETWEEN '49.558'  AND '49.568'
              AND  LONGITUDE BETWEEN '11.3395' AND '11.3495'
      ) AS n
    JOIN  `waynode` AS wn  ON n.ID= wn.NODEID
    ORDER BY  wn.WAYID

This might also work 'fast':

SELECT  n.ID, n.LATITUDE, n.LONGITUDE,
      ( SELECT  WAYID
            FROM  waynode
            WHERE  n.ID= NODEID
      ) AS WayID
    FROM  `node` AS n
    WHERE  LATITUDE  BETWEEN  '49.558' AND '49.568'
      AND  LONGITUDE BETWEEN '11.3395' AND '11.3495'
    ORDER BY  WayID

For further discussion, please provide EXPLAIN SELECT ... for any query you want to discuss and SHOW CREATE TABLE ... for the tables in question.


Why is my SQL Server consuming so much memory?

You're looking at total process memory, and one particular consumer of memory.  To drill down from total process memory and get a high-level allocation of SQL Server's memory use use sys.dm_os_memory_clerks, eg:
select type,name, (pages_kb + virtual_memory_committed_kb + awe_allocated_kb) / 1024. committed_mb
from sys.dm_os_memory_clerks
order by committed_mb desc

or the old-school
dbcc memorystatus
If you see large amounts of process memory (large_page_allocations_kb + locked_page_allocations_kb + virtual_address_space_committed_kb) that can't be accounted for by the memory clerks, see if you have any OleDb drivers for linked server loaded in-process.  They allocate memory outside of the SQL Server memory managers.
The two biggest consumers of memory are the the buffer pool and the lock manager.  Both of these will allocate memory and not release it unless the system is under memory pressure.
MEMORYCLERK_SQLBUFFERPOOL Client-Default        9801MB
OBJECTSTORE_LOCK_MANAGER  Lock Manager : Node 0 6929MB

So there's your answer.  Since it's started SQL Server has at some point grown the buffer pool to 9GB and the lock manager memory to 7GB, which accounts for enough of your 21GB to indicate that you don't really have a problem.
See related: memory used by Locks


Cannot drop primary key or add primary key

You can find all FKEY/PKEY/CHECK Constraints in QSYS2.TABLE_CONSTRAINTS to see and/or remove any keys built on the table.


Cannot connect to Aurora RDS using workbench over ssh

This took me forever....

Other than the permissions in the security groups that i set initially, i had to allow port forward on the bastion instance.

connect to the bastion instance via SSH
go to /etc/ssh
sudo nano sshd_config
Set: AllowPortForwarding yes
restart the instance
Connect with workbench


Oracle 11g hunting down the deadlock, maybe foreign key?

Deadlocks in Oracle with logically disjoint transactions usually involve unindexed foreign keys:

  There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.

Locks in Oracle are managed at the row level. Concurrent disjoint transactions should not interfere with one another. Unindexed foreign keys are an exception, since it can result in a complete TABLE LOCK.

You should get the SQL in the trace file of the deadlock and that should help you
 narrow down which table / foreign key is responsible for the lock. Once you know which table is affected by the deadlock, make sure that all foreign key references to this table are properly indexed. E.G in your example T2.t1_id should be indexed if it points to T1.t1_id.

Alternatively you could use Tom Kyte's script from the above link to determine if you have any unindexed foreign key.

If you're locked on inserts, it usually means that you're trying to insert rows that have the same values for a set of columns that are UNIQUE, for example:

CREATE TABLE T1 (col1 NUMBER NOT NULL, col2 NUMBER);
ALTER TABLE T1 ADD CONSTRAINT T1_UNIQUE UNIQUE (col1, col2);

session1> INSERT INTO T1 VALUES (1, NULL);
session2> INSERT INTO T1 VALUES (2, NULL);
session2> INSERT INTO T1 VALUES (1, NULL); -- <= this will wait on session1
session1> INSERT INTO T1 VALUES (2, NULL); -- <= Deadlock !


How to aggregate the number of indexes on a table and columns per index

If you want a stable, reliable, and free script, try sp_BlitzIndex. As a disclaimer, it's part of an open source project I contribute to.

If you run it like so, it will go through all the databases on your server and produce the information you asked for, plus a lot of other nice details.

EXEC sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2;


ORDER from 2 tables to achieve a specific order returned by an API

(copied from the chat)

  MySQL version is 5.6.40

  Max records is 100.

The method to parse JSON string to enumerated table (up to 100 records) applicable to version 5.6.40.

set @JSON='{"imgResults":[{"id":"37967","version":"2"},{"id":"236","version":"1"},{"id":"37967","version":"1"},{"id":"1413","version":"2"}]}'

This is example source JSON string.

It may be transferred to server as query parameter to the above query which assigns the value to a variable or inserted immediately into the query text as string literal.

Also It may be transferred to server as query parameter to the below query which parses it, i.e. intermediate user-defined variable is optional.

Now we parse JSON to a table (emulation of JSON_TABLE function available since 8.0 version):

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(json, '"id":"', num+2), '"id":"', -1), '"', 1) id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(json, '"version":"', num+2), '"version":"', -1), '"', 1) version,
       MIN(num) idx
FROM (SELECT @JSON json) source,            -- source JSON string
( SELECT numbers1.num*10+numbers2.num num   -- create numbers table, 0..99
  FROM ( SELECT 0 num UNION
         SELECT 1 UNION
         SELECT 2 UNION
         SELECT 3 UNION
         SELECT 4 UNION
         SELECT 5 UNION
         SELECT 6 UNION
         SELECT 7 UNION
         SELECT 8 UNION
         SELECT 9 ) numbers1,
       ( SELECT 0 num UNION
         SELECT 1 UNION
         SELECT 2 UNION
         SELECT 3 UNION
         SELECT 4 UNION
         SELECT 5 UNION
         SELECT 6 UNION
         SELECT 7 UNION
         SELECT 8 UNION
         SELECT 9 ) numbers2 ) numbers
 GROUP BY id,version
 -- ORDER BY idx;

Output:

id    | version | idx
:---- | :------ | --:
37967 | 2       |   0
236   | 1       |   1
37967 | 1       |   2
1413  | 2       |   3

You may join this parser into your query as a source data subquery and use its idx field for ordering the final result in the same order in which the records are posessed in source JSON.


How to get the last inserted id using scope identity without using a stored procedure in Mysql?

The LAST_INSERT_ID() function will return what you are looking for. It is a built-in function, does that qualify?

I'm not sure what you mean by excluding "stored procedure"; maybe it's just wrong terminology and you wish to also exclude built-in functions?


This ought to do it.  You'll want the last column (FileName_Only).

SELECT f.physical_name,
    REVERSE(f.physical_name) AS physical_name_Reversed,
    CHARINDEX('\', REVERSE(f.physical_name)) AS First_Backslash_When_Reversed,
    LEFT(REVERSE(f.physical_name), CHARINDEX('\', REVERSE(f.physical_name)) - 1) AS FileName_Only_Reversed,
    REVERSE(LEFT(REVERSE(f.physical_name), CHARINDEX('\', REVERSE(f.physical_name)) - 1)) AS FileName_Only
FROM sys.database_files f

Here's another option. The code is a bit leaner:

SELECT
    f.physical_name,
    REVERSE(f.physical_name) AS physical_name_Reversed,
    CHARINDEX('\', REVERSE(f.physical_name)) AS First_Backslash_When_Reversed,
    RIGHT(f.physical_name, CHARINDEX('\', REVERSE(f.physical_name)) - 1) AS FileName_Only
FROM sys.database_files f


How to get balance of of remaining medicine batch wise

Basically you cannot GROUP BY the type, because you want the  SUM of both types
SELECT itemcode, batchno, eXpirydate,
    SUM(CASE
         WHEN type= 1 THEN qty
         WHEN type= 2 THEN qty*-1
    END) AS balance
FROM strans
GROUP BY itemcode, batchno, EXpirydate


MyISAM : Should I be concerned by these warnings?

Yes, you should be worried, but you can improve your backup process with a few changes.
ANALYSIS OF MESSAGES
Status checked for table fotdb.tabfotlog.
1 client is using or hasn't closed the table properly

MyISAM has a header that contains the number of open file handles on the table. If you make a copy of the table with a nonzero value for the number of open file handles, copying it to another MySQL instance or restoring it to your current MySQL instance will result in the table being marked crashed.
Status checked for table fotdb.tabfotlog.
Size of indexfile is: 11223040        Should be: 11224064
------------------------------------------------------------
Status checked for table fotdb.tabfotlog.
Size of datafile is: 28854024         Should be: 28854668

Running REPAIR TABLE fotdb.tabfotlog; will fix it all of these problems after restoring it, but you may probably lose a few rows in the process.
What accounts the  discrepancy in the size of .MYD and .MYI ?
The MyISAM Storage Engine relies solely on the OS for disk changes. That means the OS is responsible for flushing disk changes to the .MYD and .MYI files. This mechanism becomes much worse if you are talking about using MyISAM in a Windows environment.
SUGGESTION
There was a post made back on March 24, 2014 entitled Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)
I have a post (Shell Scripting included) and so does @BillKarwin (proposes XtraBackup for MyISAM) that you may find helpful on scripting a FLUSH TABLES WITH READ LOCK plus other nuances of MyISAM you need to consider.
EPILOGUE
MyISAM Documentation is actually very easy to find. I keep some links in the myisam tag info. The first link goes to the latest MyISAM Documentation in MySQL 5.6.
UPDATE 2014-05-21 19:07 EDT
It just dawned on me. The posts from @BillKarwin and I were answering you from Mar 24th.


Is it safe to risk multiple Firebird nbackup -L?

Executing nbackup -L when a database is already locked will produce an error (and the executable will return exit code 1 instead of 0):

[
PROBLEM ON "begin backup".
unsuccessful metadata update
-ALTER DATABASE failed
-Database is already in the physical backup mode
SQLCODE:-607
]

In that case, Firebird (or nbackup) won't change anything in the state of the database. So the locked database will remain locked.

Continuing would be fine in theory (as the database is locked), but I don't think it is a good idea to just ignore errors and continue, because it could be a different error than this. So, for proper error checking, you would need to check if the error is Database is already in the physical backup mode and not some other error.

As an aside, you can achieve the same effect with ALTER DATABASE BEGIN BACKUP and ALTER DATABASE END BACKUP, which will also work with SuperServer mode (as the nbackup tool needs direct file access, while SuperServer uses an exclusive lock on the file).


innodb_autoinc_lock_mode = 2, real danger if not using AUTO_INCREMENT columns

As far as my understanding goes, if you're truly not using any auto-increment identity fields, then that isn't a concern and you should be ok to switch to innodb_autoinc_lock_mode = 2.
The warning is purely about the fact that auto-increment fields can't have 100% guarantee of staying in sync across replicas when interleaved is set because it allows multiple INSERT statements to occur at the same time so the order of INSERTs (and auto-generation of the IDs) can occur in different orders between replicas and the source.
This article goes into pretty good derail about the different values for that setting and their recommendations on when to use interleaved mode for performance reasons.


Excel Data Connection to SQL Server using Windows Auth from untrusted machine

Our solution ended up being ShellRunas.

https://technet.microsoft.com/en-us/sysinternals/cc300361.aspx

By creating custom shortcuts to launch Excel using the /netonly switch, we can have them enter their domain credentials and then open the needed spreadsheets.  It's not 100% transparent, but it's good enough.


Mysql unable to store emoji in utf8mb4 collation

Paraphrasing https://stackoverflow.com/a/38363567/1766831 :

Question Marks (regular ones, not black diamonds) (Se?or for Señor):

The bytes to be stored are not encoded as utf8mb4. Fix this.
The column in the database is CHARACTER SET utf8mb4. Fix this.
Also, check that the connection during reading is UTF-8.  (That is, check phpmyadmin for utf8mb4.

Once ???? is stored, the Emoji is lost.  That is, the INSERT side has the problem.  The SELECT side may also have a problem.


I would suggest duplicating the table structure in a different schema (backup) and move the data from the one table to the other. That way it can be managed effectively. But if you know what's orphaned, then why would you keep it? What purpose will it serve?


How to optimize selection of pairs from one column of the table (self-join)?

Postgresql 9.5.19 has windowing functions that prove helpful in such situation. The lead() function give you access to the next row in a "partition".

You could try something like that :

SELECT
  user_name,
  place_name AS r1_place,
  tm AS r1_tm,
  lead(place_name) OVER (PARTITION BY user_name ORDER BY tm) AS r2_place,
  lead(tm) OVER (PARTITION BY user_name ORDER BY tm) AS r2_tm
FROM example
ORDER BY 1, 3;

resulting in :

user_name|r1_place|r1_tm              |r2_place|r2_tm              |
---------|--------|-------------------|--------|-------------------|
user_1   |place_1 |2020-02-25 00:00:19|place_2 |2020-03-25 00:00:19|
user_1   |place_2 |2020-03-25 00:00:19|        |                   |
user_2   |place_1 |2020-02-25 00:00:19|        |                   |
user_3   |place_2 |2020-02-25 00:00:19|place_1 |2020-03-25 00:00:19|
user_3   |place_1 |2020-03-25 00:00:19|        |                   |

Not sure about the performance part however... you should make some tests.

Of course, you can filter out null results:

SELECT * FROM (
  SELECT
    user_name,
    place_name AS r1_place,
    tm AS r1_tm,
    lead(place_name) OVER (PARTITION BY user_name ORDER BY tm) AS r2_place,
    lead(tm) OVER (PARTITION BY user_name ORDER BY tm) AS r2_tm
  FROM example
  ORDER BY 1, 3) req
WHERE r2_place IS NOT null


While your query is valid, there is a lot I would differently.

Don't use CaMeL-case names in Postgres if it can be avoided. Your unnamed entity framework may force this nonsense upon you, but I don't want to deal with the double-quote mess, so I tested with your schema after removing all double-quotes - effectively making all identifiers lower-cased.

Are PostgreSQL column names case-sensitive?

Don't use illegible or illegal column and table aliases (like "wordTexts.WordId"). That's a matter of taste and style (and sanity), but you also omitted the keyword AS where you should not and kept it where you could omit it.

Date column arithmetic in PostgreSQL query

I also formatted some more to make it easier for me to grok the query. That last part is totally optional. But use some consistent formatting style.

Arriving at this:

SELECT *
FROM   PulledTexts
WHERE  Id IN (
   SELECT w.TextIdId
   FROM   WordTexts w  -- AS can be omitted for table alias
   LEFT   JOIN UniqueWords u ON w.WordIdId = u.Id  -- LEFT JOIN might be necessary here
   WHERE  u.WordText = 'automate'
   OR     w.TextIdId IN (
      SELECT w.TextIdId  -- AS and1 -- column alias only documentation here, not visible
      FROM   WordTexts w
      JOIN   UniqueWords u ON w.WordIdId = u.Id  -- LEFT JOIN misleading here
      WHERE  u.WordText = 'audit'

      INTERSECT
      SELECT w.TextIdId  -- AS and2  -- but don't omit AS for column alias
      FROM   WordTexts w
      JOIN   UniqueWords u ON w.WordIdId = u.Id
      WHERE  u.WordText = 'trial'
      )
   );

Which can be simplified to:

SELECT *
FROM  (
   SELECT w.TextIdId AS Id
   FROM   WordTexts   w
   JOIN   UniqueWords u ON w.WordIdId = u.Id  -- now we don't need LEFT any more
   WHERE  u.WordText = 'automate'

   UNION
   SELECT w.TextIdId
   FROM   WordTexts w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'audit'

   INTERSECT
   SELECT w.TextIdId
   FROM   WordTexts w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'trial'
   ) w
JOIN   PulledTexts p USING (Id)

We do not need additional parentheses because, according to the manual:

  INTERSECT binds more tightly than UNION. That is, A UNION B INTERSECT C will be read as A UNION (B INTERSECT C).

But this will be faster when replacing multiple intersected subqueries:

SELECT *
FROM  (
   SELECT w.TextIdId AS Id
   FROM   WordTexts   w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'automate'

   UNION
   SELECT TextIdId
   FROM   WordTexts w1
   JOIN   WordTexts w2 USING (TextIdId)
   WHERE  w1.WordIdId = (SELECT Id FROM UniqueWords WHERE WordText = 'audit')
   AND    w2.WordIdId = (SELECT Id FROM UniqueWords WHERE WordText = 'trial')
   ) w
JOIN   PulledTexts p USING (Id)

The INTERSECT part can be cast as relational division problem. Explanation in this related answer from just yesterday:

Counting with multiple joins

db<>fiddle here

Most important for performance is to have the right indexes. You probably should have a UNIQUE constraint on (WordIdId, TextIdId) in table WordTexts, which implements the currently missing index on those two columns in this order.

Does creating a unique constraint on a Postgres column remove the need to index it?


Deploying dacpac through sqlpackage.exe with NETWORK SERVICE user

In the situation you describe, I can't think of anything to do other than add the username / password as secret variables in the pipeline (as you described).
An approach I've used to avoid the username and password thing is to have the Azure DevOps agent service run as a minimally-privileged Windows service account (rather than NEWTWORK SERVICE).  You just have to add that Windows account as a login on the SQL Server instance, and grant it the appropriate permissions for creating and deploying databases with SSDT.
This lets you use Windows auth to connect and deploy to the remote server - but only if that server also "knows" the Windows user (which is probably not the case, as you mentioned the prod server is outside your network - which I assume means it's not on the same Active Directory domain).


How much RAM is required for 5000 concurrent users MySQL

Your problem is the innodb buffer size - that is a lot more dependant on database patterns than users. If your database is only 20gb there is no sense in a larger buffer.

And the sensible size there can not be determined from user count only.

So, given you need something for the OS and MySql as a server, then 26gb for the connections - and man, I don't do MySql, but in SQL Server world 5000 connections would be crazy and be for like 100.000 users with connection pooling at the same time.

Assuming that is not just crazy bad programming - I would go with 32 to 64gb memory and the best xeon chips I can get my hands on....  RAM is not your problem here, processing 5000 concurrent SQL statements is. As a start - 64gb memory are cheap in such a server. If the database is larger, then 128gb to 256gb memory would be good. But your problem is not memory, if you really have 5000 concurrent users doing things and not just keeping passive users connected, which is bad programming.


If you want 14 rows in the result (6+8), you need to use UNION of the two queries you already have.  Just take care for both to have the same number of columns (and same datatypes in the respective columns.)

It will be something like:

SELECT u.*,                              -- list of wanted columns from the users table
       'Video'     AS type
       v.id        AS item_id,           -- and the videos table
       v.posted_at AS posted_datetime
FROM users AS u
  INNER JOIN videos AS v
    ON u.id = v.user_id
WHERE u.id = ?

UNION ALL

SELECT u.*,
       'Post'      AS type
       p.id        AS item_id,           -- and the posts table
       p.posted_at AS posted_datetime
FROM users AS u
  INNER JOIN posts AS p
    ON u.id = p.user_id
WHERE u.id = ?

ORDER BY posted_datetime                 -- ORDER BY some_column
LIMIT 50 OFFSET 0 ;


changing the value of a column in a table based on the value of another column

here is the syntax :
 update class
 inner join (
    select class_number, count(*) totalSize
    from class
    group by class_number
 ) cc on class.class_number = cc.class_number
 set class.class_size = totalSize


My personal preference is to never use BETWEEN since its not abundantly clear, from the code itself, whether the range includes 2019-01-01 and 2019-12-31.  I realize one can easily check the manual to see how BETWEEN operates, but in my opinion, this is far more easily read, without any chance for confusion:

SELECT LogId
FROM [Master].[dbo].log mlog
WHERE cast(LogDate as Date) >= '2019-01-01'
    AND cast(LogDate as Date) <= '2019-12-31'

Be aware, though, that cast(LogDate as date) is not an optimal way to perform this query.  The far better solution would be:

SELECT LogId
FROM [Master].[dbo].log mlog
WHERE LogDate >= '2019-01-01T00:00:00.000'
    AND LogDate < '2020-01-01T00:00:00.000'

The above statement is SARGable, and will also ensure you get the range desired.  Comparing <= '2019-12-31' actually means before December 31st, 2019, when you probably actually mean up until the end-of-day on December 31st, 2019.  Looking for dates less than the start of the next day ensures you get all the rows between the start of the day on 2019-01-01 and the end of the day on 2019-12-31.

Note, I'm including a T between the date and time components; this indicates to SQL Server to treat the value as an ISO8601 date, which will prevent possible errors that might be introduced under non-US English language installations.


syspolicy_purge_history job failed at step 3

I ran into this error a while back and found this link, hope it resolves your issue. http://www.mssqltips.com/sqlservertip/2041/why-syspolicypurgehistory-job-fails-in-sql-server-2008-failover-cluster-instance/


Operators similar to 'in' that compares two sets

You can just JOIN to real or dynamically derived tables:

DECLARE @var1 INT=2;
DECLARE @var2 INT=15;
DECLARE @var3 INT=-7;

IF EXISTS (SELECT 1 FROM
             (VALUES (1),(2),(3))tableNums(number)
             INNER JOIN (VALUES (@var1), (@var2), (@var3))tableVars(var)
             ON tableNums.number=tableVars.var
       )
    SELECT 1    --or do something else interesting
ELSE SELECT 0   --or do the opposite

If the list was very long, I'd probably create it in a #temp table in advance instead of doing it in the statement, but the JOIN and EXISTS logic would be the same.

Similarly, we could used INTERSECT instead of JOIN:

CASE WHEN EXISTS ( SELECT * FROM (VALUES (1), (2), (3)) AS x(i)
                 INTERSECT
                   SELECT * FROM (VALUES (@var1), (@var2), (@var3)) AS y(i)
                 )
THEN 1 ELSE 0 END

Test at dbfiddle.uk.


Postgres row_to_json precision for numeric type

You can round the values:

select row_to_json(t)
from (
  select id,
         round(value_1, 2) as value_1,
         round(value_2, 2) as value_2
  from test
) t;

If you need that more often and don't want to type it all the time, create a view that returns the rounded values.


Restricting the # of Rows a User can return in a single query

Always just give the grants necessary. If you want to restrict on the row number, you have to create views like

CREATE VIEW view_name AS
SELECT whatever FROM a_table ORDER BY whatever LIMIT 10;

Then just grant select on this view, not the table the view is reading from. Read more about views here. With DEFINER and SQL SECURITY you can also select on things, that the user usually has no right to. Whatever you need.

As a last note, I included ORDER BY in the query, because a LIMIT without ORDER BY usually doesn't make much sense, cause there's no order in a relational database unless you specify it with said ORDER BY.