What cases benefit from the Reduce, Replicate, and Redistribute join hints?

In general terms we use a join hint is to correct a failure of the optimizer to identify a better query plan. This can happen for many reasons which broadly boil down to optimization being a "good enough" approach constrained by a time budget for finding a plan.
Reduce
"also called a semi-join hint". With a semi-join we are only interested in whether or not any matching row exists in the other table. Indeed, it is often because the query has an EXISTS() predicate. If there is one match or one million the result will be the same - the single row from the left (outer) table will be returned.
As an example, we may want to query all Customers who have ever placed an Order. Let's say Customer is a small table but Order is huge and spread over many nodes. It would be wasteful to copy all that data to wherever Customer is held when all we need is to know if at least one row exists for each Customer. The REDUCE hint provides this. Conceptually it is select distinct CustomerId from Order run on each node holding Order, which is relatively fast.
Replicate
This is the converse of REDUCE. Here a table is copied en masse from its "home" node to all nodes holding data from the table on the right-hand side of the join.
For this example we want the total value of all orders, summed by Customer city. We only have a relatively few customers which comfortably fit on a single node. The Orders table, however, is huge and spans many nodes. To satisfy the query we have to get both Customer and Order data onto the same node. It will be more efficient to copy Customer, since it is smaller. The REPLICATE hint forces that behaviour, making a complete copy of the Customer table on each node holding Order rows.
Redistribute
This is another way of stating how rows should be moved from their "home" node to another node so join predicates can be evaluated. This time we are asking the server to copy the data and distribute that copy according to some new distribution function such that the copied rows will end up on the right node to complete the join.
Say now we have a retail e-commerce site with lots of customers and lots of orders. Customers are distributed by CustomerId and Orders distributed by OrderDate. If we now want to join on Customer.CustomerId = Order.CustomerId the most efficient plan may be to copy Order and re-distribute according to CustomerId, then pass each new partition to the corresponding node. This differs from REPLICATE as there the whole table is copied to every node whereas here a portion of each node's data is copied over to other nodes. The specific way the re-distribution is handled will depend in the current distribution and the cardinalities.


I would use two cursors. The first runs through all databases - except sys dbs. For each database the logins will be fetched and added to the db_owner role.

If you have hundreds of dbs/ logins, I would write this as a db with some transaction handling and/or logging that if something crashes while executing you can continue on the point of crash.

i couldn't test the code below, this should be an approach how to develop your own proc

Use master
GO

DECLARE @dbname VARCHAR(50)
DECLARE @SQL_statement varchar(max)
DECLARE @chng_role NVARCHAR(MAX)

DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
  SELECT name
    FROM MASTER.dbo.sysdatabases
    WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
 BEGIN

    SET @SQL_statement = 'Declare  users_cursor CURSOR FOR SELECT name FROM '+ @dbname +'.sys.database_principals where (type='''S''' or type = '''U''')'
    EXEC sp_executesql @sqlstatement
    OPEN users_cursor
    FETCH NEXT FROM users_cursor INTO @UserName
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @chng_role = 'use [' + @dbname + ']; exec sp_addrolemember @rolename = ''db_owner'', @membername = ''' + @userName + ''''
            EXECUTE sp_executesql @chng_role
            FETCH NEXT FROM users_cursor INTO @UserName
        END
    CLOSE users_cursor
    DEALLOCATE users_cursor

 FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor


Cannot drop materialized view privileges from user

revoke all privileges on all tables in schema public from "example_user";

This drops privileges from objects in the schema named "public".  It does not drop them from objects in other schemas, such as "example-prod".

  revoke all privileges on "example-prod".view_foo from "example_user";

Works for me.  It still fails due to the other 3 MV, but no longer for that specific one.


Mysql giving error while inserting 120 million rows "The total number of locks exceeds the lock table size"

Quickest way to get around such an error is to split it up into multiple queries.

Option 1:

Take the original query and execute it repeatedly changing LIMIT bounds.
... LIMIT 0, 10000000;
... LIMIT 10000000, 10000000;
... LIMIT 20000000, 10000000;
... LIMIT 30000000, 10000000;
and so on...

Without an ORDER BY, the limit is not 100.00000% guaranteed to be consistent; but as long as the query is not overly complicated this is usually safe.

Option 2: Take the original query and execute it repeatedly. Instead of a shifting window from varying LIMIT clauses, devise a set of WHERE conditions that will discretely partition the data. For example: If you know the source is roughly even divided up by several sectionID values, you could have WHEREs based on those values (or non-overlapping ranges of them.)


Transfer all tables from a schema to other schema

Execute this for all of the tables:

alter schema2
transfer schema1.table1;

To programmatically get the statements to execute:

declare
    @SourceSchemaName sysname,
    @DestinationSchemaName sysname,
    @AlterStatements varchar(max);

set @SourceSchemaName = 'NewSchema';
set @DestinationSchemaName = 'HumanResources';
set @AlterStatements = '';

select
    @AlterStatements +=
        'alter schema ' + quotename(@DestinationSchemaName) +
        ' transfer ' + quotename(schema_name(schema_id)) + '.' + quotename(object_name(object_id)) +
        char(13) + char(10)
from sys.tables
where schema_id = schema_id(@SourceSchemaName);

print @AlterStatements;


How can I copy a subset of columns from one InnoDB table to another, without locking?

IF you are not updating or deleting old rows, the way to do it with minimal locking is to do it chunk by chunk.  See here for doing chunking for deleting; it can be adapted for your 'copy'.  When you get to the end of the copying, stop writes long enough to copy the final chunk and rename:

Then, if your real goal was to change to BIGINT, finish with

RENAME TABLE source TO old,
             destination TO source;
DROP TABLE old;


How can I get the last second of the last month as a default parameter value?

They key is the placement of DateValue() which:

  Returns a Date value containing the date information represented by a string, with the time information set to midnight (00:00:00).

The problem with where you are removing seconds is inside the datevalue() function which is dropping the time portion.

It is still important as you want time removed from the initial calculation for getting the last day of the month.

A little shifting around get the desired output.

=DateAdd(DateInterval.Second,-1,DateValue(DateAdd(DateInterval.Day,-(Day(Now)-1),Now)))

or

=DateAdd("s",-1,DateValue(DateAdd("d",-(Day(Now)-1),Now)))


PLS-00049 bad bind variable when compiling a trigger

you should remove fragment: "USER.". Properly is:

CREATE OR REPLACE TRIGGER TBL_ACTION_DEDUP
BEFORE INSERT
ON ERRORTABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF (:OLD.Identifier = :NEW.Identifier) THEN
  SELECT :OLD.tally + 1 INTO :NEW.tally FROM DUAL;
END IF;
END TBL_ACTION_DEDUP;
/


Fast general method to calculate percentiles

I have come up with the following:

select cume, max(var) AS max_var
from (
   select var
        , ntile(5) over (order by var) as cume
   from table
   ) as tmp
group by cume
order by cume;

It selects the maximum of each group that is divided using ntile().


How can I check a version of installed ODBC MariaDB connector?

file won't give much information:

file libmaodbc.so
libmaodbc.so: ELF 64-bit LSB pie executable, x86-64, version 1 (SYSV), dynamically linked, BuildID[sha1]=5a3305eade1036378d57e16dcb09e02d541cc6a9, with debug_info, not stripped

A run of strings and grep commands gave me the version quite easily:

jynus@sangai:~/Downloads$ strings mariadb-connector-odbc-3.0.9-ga-debian-x86_64/lib/libmaodbc.so | grep -A10 ^_client_version
_client_version
_server_host
_pid
_platform
libmariadb
3.0.9
Linux
x86_64
MYSQL_PWD
5.5.5-
compiled_in

However, that doesn't seem to match the release version well:

jynus@sangai:~/Downloads$ strings mariadb-connector-odbc-2.0.18-ga-debian-x86_64/lib/libmaodbc.so | grep -A10 ^_client_version
_client_version
_pid
_platform
_server_host
libmariadb
2.3.6
Linux
x86_64
client
MYSQL_PWD
5.5.5-

I found another string like this, which seems to match the released version:

jynus@sangai:~/Downloads$ strings mariadb-connector-odbc-2.0.18-ga-debian-x86_64/lib/libmaodbc.so | grep ma-
[ma-2.0.18]
jynus@sangai:~/Downloads$ strings mariadb-connector-odbc-3.0.9-ga-debian-x86_64/lib/libmaodbc.so | grep ma-
[ma-3.0.9]
jynus@sangai:~/Downloads$ strings mariadb-connector-odbc-3.1.1-ga-rhel7-x86_64/lib/libmaodbc.so | grep ma-
[ma-3.1.1]

Use that command to find the version. Your mileage may vary, there may be a simpler way (e.g. using it), but this is a quick and dirty option.


What I believe you're talking about is Microsoft Office changing single quotes to Smart Quotes.

It's an Autoformatting setting that you can change through the Options menu in Microsoft Word.

Go to Options -> Proofing -> Autocorrect Options

There are two places to change this:

Under 'Autoformat', untick Replace -> Straight Quotes with Smart Quotes
Under 'Autoformat as you type', untick Replace as you Type -> Straight Quotes with Smart Quotes

Alternatively, stop copying SQL from Microsoft Word.

https://www.simple-talk.com/blogs/2011/11/09/how-to-stop-office-2010-changing-and-to-smart-quotes/


Checking for duplicate entries for Column A - Table A based on user_id from Table B

Do a GROUP BY aggregation and see if the aggregated count is greater than 1

SELECT B.Author_id,A.Skill_id,COUNT(1) SkillEntryCount
FROM tableB B INNER JOIN tableA A USING (Entry_id)
GROUP BY B.Author_id,A.Skill_id
HAVING COUNT(1) > 1;

or collect all entry_id values for each (Author_id,Skill_id) and see it is has commas

SET group_concat_max_len = 1048576;
SELECT * FROM
(
  SELECT B.Author_id,A.Skill_id,GROUP_CONCAT(A.Entry_id) EntryIDs
  FROM tableB B INNER JOIN tableA A USING (Entry_id)
  GROUP BY B.Author_id,A.Skill_id
) AA WHERE LOCATE(',',EntryIDs) > 0;


Substitute DISTINCT ON for query transformation (Postgresql to Redshift)

This is my workaround, that works with Redshift:
select * from (select
id_created, customer_id, call_start, status_prior, status_after,
rank() over (partition by customer_id order by call_start desc) as rank
) t
where rank=1;
It seems to work, however, the processing time is longer, because of the subquery.


It turns out there was a trigger on the samen table that also updates it, that forces an update for the index used. It works near instant with the trigger disabled.


It sounds you have a slow disk, small ram and the main cache is too small.
How much RAM?  What is the value of innodb_buffer_pool_size.  SSD or HDD?
Without the secondary indexes, it had no choice but to read the entire table which was probably about 200MB?  But the buffer pool is only 16MB or 128MB (old defaults), hence "caching" was useless.
Moving to a much smaller secondary index (with 8-byte DATETIME(6) and 4-byte INT) was somewhat faster, but still too big to be cached.
I don't want to advise on the buffer_pool size without first knowing how much (or little) RAM you have and whether there are other processes running.


Impact of miss-ordered fields in a composite, clustered primary key?

The order of columns in a primary key does not affect the insert performance, since the combination of the values in the two columns of the primary key are meant to be unique.

However the order of index impacts the performance of SELECT queries. In the new order you will have a greater and increasing range of values (dates) across which the index is clustered (dates) as compared to clustering around 100 stores.

If most of your queries are focused on a single store with the new ordering your queries will be slower.


How to order some rows one way, and the rest another way?

This returns the top 3 rows according to ORDER BY x DESC and appends the rest sorted by y DESC:
WITH cte AS (TABLE example ORDER BY x DESC LIMIT 3)
TABLE cte
UNION ALL
(  -- parentheses required
SELECT e.*
FROM   example e
LEFT   JOIN cte USING (id)
WHERE  cte.id IS NULL
ORDER  BY e.y DESC
);

TABLE is just shorthand for SELECT * FROM.
UNION ALL makes sure that the two derived tables are just appended without trying to eliminate (non-existent) duplicates and thereby messing with the sort order.
Parentheses are required so that ORDER BY only applies enclosed SELECT, not to the complete set of rows.
See:

Combine multiple SELECT statements

Is order preserved after UNION in PostgreSQL?

Related:

Combining 2 SELECT queries and printing the results in PostgreSQL

Is there a faster way to get UNION ALL behavior in Postgres?

Without UNION
SELECT e.*
FROM   example  e
LEFT   JOIN (
   SELECT id, x FROM example ORDER BY x DESC LIMIT 3
   ) c USING (id)
ORDER  BY c.x DESC NULLS LAST, e.y DESC;

Same result.
The LEFT JOIN results in c.x being NULL except for the top 3 chosen rows. So the first ORDER BY items c.x DESC NULLS LAST only sorts the first 3 rows and leaves the rest unsorted. The second ORDER BY item e.y DESC sorts the rest as desired.
About NULLS LAST:

Sort by column ASC, but NULL values first?

db<>fiddle here
If the table is not trivially small, you should have and index on (x) and another one on (y)


Only some basic types.
You can however use the BLOB type to save whatever you want.
Are you going to do the vector processing outside to SQLite?

https://www.sqlite.org/datatype3.html

NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE
floating point number.
TEXT. The value is a text string, stored using the database encoding
(UTF-8, UTF-16BE or UTF-16LE).
BLOB. The value is a blob of data, stored exactly as it was input.


PL/SQL: Selecting multiple values from the same column

You can reference the same table multiple times in your query with an alias to solve this issue.
For example:
SELECT ref.row_id, f.word as fruit, v.word as vehicle
  FROM Rows ref INNER JOIN Words f ON ref.fruit_id = f.id
                INNER JOIN Words v ON ref.vehicle_id = v.id
 WHERE f.id = :PRODUKTART_ID
   and v.id = :VEHICLE_ID


SUBSTRING_INDEX will repeat the value if the value doesnt not end with the respective delimiter,so just concat a comma at the end of each string

SELECT DATE(attendance_date_time) AS attendance_date, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 1), ',', -1) AS F1_time,
IF(LENGTH(GROUP_CONCAT(TIME(attendance_date_time))) - LENGTH(REPLACE(GROUP_CONCAT(TIME(attendance_date_time)), ',', '')) > 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 2), ',', -1), NULL) AS F2_time,
-- SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 3), ',', -1) AS F3_time,
IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 3), ',', -1), NULL) AS F3_time,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 4), ',', -1) AS F4_time
FROM employee_attendance
GROUP BY DATE(attendance_date_time);

FIDDLE


Error in using sp_describe_first_result_set with Restore

The metadata could not be determined because statement RESTORE
  HEADERONLY .... does not support metadata discovery.

Error message tells you clearly that RESTORE HEADERONLY is unsupported statement, it does not support metadata discovery.

So there is NO WAY to make this work. It is not implemented.

UPDATE

There is a solution in the link you posted, but the question there was NOT how to make work sp_describe_first_result_set with smth that is not supported (that is YOUR question), but how to get back only some columns from RESTORE HEADERONLY resultset, i.e. how to get the result of RESTORE HEADERONLY in "queryable" form.

There is a solution like this:

SELECT BackupStartDate
FROM OPENQUERY(LOCALSERVER,
               'SET FMTONLY OFF;
               EXEC(''
               RESTORE HEADERONLY
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')')

But as you see the Server in question was 2008.

Starting with SQL Server 2012 sp_describe_first_result_set was introduced, and now if you try to execute the query above, another thing is executed (you can see it using Profiler):

exec [sys].sp_describe_first_result_set N'SET FMTONLY OFF;
               EXEC(''
               RESTORE HEADERONLY
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')',NULL,1

It's wrong to think that OPENQUERY/OPENROWSET always use sp_describe_first_result_set, you can prove it to yourself executing simple SELECT queries and monitoring execution using Profiler (even on 2012). But in your particular case the query is transformed as described above and you cannot do nothing with it.


You should choose a pattern for the FK name that respects the relationships between the tables.
My preference is FK_<child entity>_<verb phrase>_<parent entity>.
So fk_product_modified_by_user, fk_category_modified_by_user, etc.


How to migrate Oracle Database to MariaDB?

There is certainly no such service by Oracle as they wouldn't want anyone to migrate away from their own database system.

MariaDB Corporation has a paid migration service to help migrating to MariaDB. This is run by experts in the field.

AWS has a fully automated service that appears to be able to migrate your schema and data, but presumably not much beyond that like stored procedures, functions and triggers. AWS is not your only option, though - other tools for this exist.

There are many articles around the web on how to do it yourself, but this will require a lot of knowledge and skills. See e.g. this article at Several Nines for a starter.


Cannot install SQL Server on a local disk

If I understand this correctly, you have 2 servers configured for in a FCI installation. They use shared disk which is the Y drive. You have a 3rd server that has local disk which is the Y drive.

Node 1 and 2 should be installed first as a FCI instance.
As long as the FCI is working as expected and fail overs are happening as expected between Node 1 and Node 2, Node 3 should be added to the Windows Cluster as an extra node.
Node 3 should have SQL server installed on it as a standalone node.
Enable Always On feature on Node 3 and the FCI instance
Node 3 should not be listed as a possible owner of FCI instance.

You should then be able to have an FCI install and a standalone install on the same windows cluster. You will be able to automatic failover between Node 1 and Node 2, with manual failover to Node 3.

I have this set up in my lab now.


Is it bad practice to launch more than 100 sql statements at once?

It depends on how your queries are organised. Important is that you use transactions. The first step in the transaction would be the creation of the bill and the next step in the transaction would be the update of the billing status.
Both need to succeed or both need to fail. If the second step fails, the first step will also be rolled back.
If this is some kind of a batch process, try to work in sets instead of on single rows. If you can process the work in 1 or 2 calls it will be a lot faster than issuing 100 or 200 calls for the same amount f work. With a lot I really mean a lot. If the code is correct easily a magnitude of 100 faster.


SSISDB user "AllSchemaOwner" is not SQL Server Login

Finally found an answer:
You can create database user, which won't have a SQL Login and won't be able to authenticate, but will have permissions and can be specified in WITH EXECUTE AS clause for stored procedures and functions.

The simple way to create such a user is just to use clause WITHOUT LOGIN during user creation:

Use SSISDB;
GO
CREATE USER 'AllSchemaOwner' WITHOUT LOGIN;
GO


issues updating records in ssis using lookup and oledb command

It seems like this could be a bug in OLE DB for SSIS. Could this solution be the one you're looking for?


Problem with PostgreSQL and the geqo optimizer

I believe the settings you mentioned already ensure that geqo will be used, except that the values for geqo_pool_size and geqo_generations are so low that the results won't be good.

Apart from that, to get different plans, you should play with geqo_seed.

From the doc's section Generating Possible Plans with GEQO:

  As long as geqo_seed and the other GEQO parameters are kept fixed, the
  same plan will be generated for a given query (and other planner
  inputs such as statistics). To experiment with different search paths,
  try changing geqo_seed.

About geqo_seed:

  Controls the initial value of the random number generator used by GEQO
  to select random paths through the join order search space. The value
  can range from zero (the default) to one. Varying the value changes
  the set of join paths explored, and may result in a better or worse
  best path being found.


Improving Backup Performance using SQL native methodology

Resource Governor can control the resource consumption of a backup.

By default, backing up using compression significantly increases CPU
usage, and the additional CPU consumed by the compression process can
adversely impact concurrent operations. Therefore, you might want to
create a low-priority compressed backup in a session whose CPU usage
is limited byResource Governor when CPU contention occurs. This topic
presents a scenario that classifies the sessions of a particular SQL
Server user by mapping them to a Resource Governor workload group that
limits CPU usage in such cases.

Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)


While it is not exactly clear what you need but if you design your tables similar to below then you should be able to retrieve the details that you need.

create table alert
(
  id int,
  aname varchar(10)
);
create table device
(
  id int,
  dname varchar(10)
);
create table alertDetails
(
  alertId int,
  deviceId int,
  alertDate datetime
);

Then when you query, you could use the following:

select *
from alert a
left join alertdetails ad
  on a.Id = ad.alertid
left join device d
  on ad.deviceid = d.id
where a.id = 1

See SQL Fiddle with a Demo


Most entities need a thread_id.

If you want "in reply to", you need a link into the same message/comment/post table as parent_id; use NULL or 0 to indicate start of chain.

Since I don't know your distinction between message and comment and post, I can't judge whether to put them in 1, or 2, or 3 tables; it depends on whether they have enough differences.  But Threads should be a separate table that discusses common info about the set of messages/comments/posts.

safe (and questioned), as you described it, belong in Threads.  But you might want a flag(s) on the other table(s) for censoring specific items, too.

Code that displays a thread (or one item in the thread) would need to check safe and the user_id to decide whether this user can see it.  (Presumably, user_id='admin' can see everything.)


Adding non-nullable NEWSEQUENTIALID() column to existing table

1) there is no need to add a NULL-able column, update, then alter it. It can be done in one pass using the WITH VALUES syntax (see http://sqlfiddle.com/#!3/5e1dea/2):

ALTER TABLE MyTable
ADD MyColumn UNIQUEIDENTIFIER NOT NULL
  DEFAULT NEWSEQUENTIALID()
WITH VALUES;

2) Use of NEWID() vs NEWSEQUENTIALID() is a moot point if you do it in one pass. But for the sake of the argument, lets see what happens. The use of sequential GUIDs is only relevant if you index the column. Because random GUIDs are so random, inserting new values results in frequent page splits. For a clustered index column this can be really problematic. But you are not updating an indexed column, so the point is irrelevant. Had you add an nonclustered index later, the index builder would first sort the column then build the index. Afterwards new inserts would find a spot in the table where the sequential GUIDs would happen to sort between the random GUIDs and the start growing the table from there. This would be basically a tail-page split (even though is not a tail-page) so it would still be OK.

Overall I think the use of WITH VALUES syntax (which is surprisingly little known) renders your question obsolete. I don't think you had to do the ALTER in three steps.


MongoDB covered query needs to fetch and examine documents when exists operator is used

I see similar results when tried on MongoDB v4.2.8 - using the posted info.
In general, the $exists in a query filter uses the index on that field. For example, if there is a field fld1 and you run a query with a filter { fld1: {$exists: true}}, the query uses the index.
In the query plan output, the in-memory sort ("stage" : "SORT") is because - for a compound index the filter must be an equality condition on the index prefix field (dataType) and then only the index will be used in a sort operation using the date field. The FETCH is for the SORT operation to happen in the memory (i.e., the index is not being used). So, the query is not covered.
For example, a query like this will use the index (and it will be covered):
db.test.find({ dataType: "a" }, { date: 1, _id: 0 }).sort({ date: -1 })

It is important to note that the query filter { dataType: "a" } uses an equality condition - and, {dataType: {$exists: true}} does not.
See Sort and Non-prefix Subset of an Index


when scripting SQL server agent jobs, is it possible to use @@servername in the @command settings line

Use the SQL Server Agent tokens when scripting the jobs something like

 /SERVER $(ESCAPE_NONE(SRVR))


When you can identify a row or set of rows already in the table, you can do it like this:

SELECT $PARTITION.[PartitionFunctionName](PartitioningColumn)
  FROM dbo.table
  WHERE <clause to identify rows>;

If you have just a value, and it's not necessarily in the table yet, you can tell what partition it would be in given the current state using $PARTITION independently:

SELECT $PARTITION.[PartitionFunctionName](SomeConstant);

(This is also useful shorthand if you are identifying the row(s) in the table using the same column.)


Large NOT IN Statement vs Creating/Populating Temp Table

With a large set you may want to use an NOT EXISTS clause rather than a NOT IN clause.  It will help if you index the id on your feed table.  Using an index will allow the optimizer to use the index rather than reading the table.  Try a query like:

DELETE
FROM    table
WHERE NOT EXISTS (
    SELECT  feed.id
    FROM    feed
    WHERE   feed.id = table.id )


INET6_ATON returns NULL for input parameter

C:\>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> DROP FUNCTION IF EXISTS `addrToBinary`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER ;;
mysql> CREATE FUNCTION `addrToBinary` (`in_remote_ip` varchar(45)) RETURNS varchar(45)
    -> DETERMINISTIC
    -> BEGIN
    ->     RETURN HEX(INET6_ATON(in_remote_ip));
    -> END;;
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> select `addrToBinary`("::ffff:127.0.0.1");
+------------------------------------+
| `addrToBinary`("::ffff:127.0.0.1") |
+------------------------------------+
| 00000000000000000000FFFF7F000001   |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT
    ->     addrToBinary("::ffff:127.0.0.1"),
    ->     HEX(INET6_ATON("::ffff:127.0.0.1"));
+----------------------------------+-------------------------------------+
| addrToBinary("::ffff:127.0.0.1") | HEX(INET6_ATON("::ffff:127.0.0.1")) |
+----------------------------------+-------------------------------------+
| 00000000000000000000FFFF7F000001 | 00000000000000000000FFFF7F000001    |
+----------------------------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>

Works Fine For Me By Hand in MySQL 5.6.22 for Windows

mysql> select `addrToBinary`(@given_ip);
+----------------------------------+
| `addrToBinary`(@given_ip)        |
+----------------------------------+
| 00000000000000000000FFFF7F000001 |
+----------------------------------+
1 row in set (0.00 sec)

mysql>

It works when passing in a parameter from the MySQL Console

If you are doing this from PHP, please make sure the IP is properly enclosed in quotes


MySQL 5.1.63 - using tuning-primer - Table Cache question

A single "open table" instance means different things in different storage engines, but you can have more "open tables" than you actually have... tables.

Tables that are "open" were used at some point since the server was restarted, yet may not have been used for days or hours ... but there's no point in MySQL going around looking for something to do, closing tables that haven't been used in a while, because it will eventually just have to open them again... unless, of course, we want to proactively avoid unnecessary consumption of system resources... and how aggressively it closes idle tables is the gist of the purpose of table_open_cache.  Too small, and you end up opening and closing the same tables over and over.  Too large, and you demand more file descriptors (and probably small amounts of additional memory) from your operating system than your workload justifies.

What you're seeing here with tuning-primer is partially explained by what we might loosely refer to as the observer effect -- changing what we're measuring, by the act of measuring.

The "tuning-primer" script does things to your server that can alter the subsequent values that the script uses to draw its conclusions... As just one example, it queries information_schema.TABLES while gathering InnoDB stats.  What does that do?  It opens tables.  How many tables does it open?  Well... how many have ya got? :)  (It may not actually open all of them, depending on some under-the-hood details in the implementation_schema, but it at least opens all of your InnoDB tables).

Depending on the number of tables you have and the level of traffic on your server (such as how many tables were open when the script was started), this script can completely fill the open table cache and then incorrectly report that the cache is 100% used.

To see this, flush the tables on your server and then run only the "file" test from tuning-primer.  Better still, flush the tables, wait a while, and then run the "file" test again.

Production Environment Disclaimer: FLUSH TABLES should be a harmless operation -- it "closes" all of the tables as soon as any running queries are done, and they will be immediately re-opened by the next query that needs them, and, if you're primarily InnoDB, this is generally almost instantaneous... but on a busy server or with a lot of MyISAM it could take a few seconds, during which your performance may dip slightly.

mysql> FLUSH TABLES;
shell> ./tuning-primer.sh file

For one of my servers, I get this:

You have 8 open tables.
The table_cache value seems to be fine

An hour later, running it again (without flushing the tables) I get this:

You have 40 open tables.
The table_cache value seems to be fine

But if I do this (with or without a FLUSH TABLES before it):

./tuning-primer.sh innodb

...and then this...

./tuning-primer.sh file

You have 512 open tables.
You should probably increase your table_cache

Not the same answer, and not a meaningful one either, because -- would you believe -- that's exactly the limitation imposed by my table_open_cache variable.

My final thought: the best "tuning strategy" from my experience is to tune as few parameters as you can, as seldom as you can, in MySQL.  Aside from the obvious values like innodb_buffer_pool_size, most parameters are best left alone unless a specific reason can be identified why that particular parameter has a value that's not appropriate for your environment.

The developers of this script obviously put a lot of work into it, so it's not my intention to be critical of it.  But just like any tool that tries to make informed decisions based on observing a complex system, its conclusions should not be taken as authoritative without corroboration and investigation.


Do the different "types" of autovacuum have different performance characteristics?

In monitoring the autovacuum activity on my database, I've noticed that autovacuums tagged with (to prevent wraparound) seem to take longer than "regular" autovacuums.

That's to be expected. They have to do more work. These are essentially a vacuum freeze run by autovacuum.

Freezing tuples means marking them as visible by all current and future transactions by replacing their transaction ID with a special fixed value FrozenTransactionId (xid 3). Since the xmin for a tuple is stored in the tuple on disk, this means each tuple that's not already frozen and is a candidate for freezing needs to be modified. This often triggers a full-page write since the tuple is generally the first modification on the page since the last checkpoint, resulting in an 8k write for the first frozen tuple on each page. (This also bloats WAL and therefore PITR backups, replication streams, etc).

Freeze vacuums in PostgreSQL 9.6 and below cannot skip parts of the table; they must scan the whole table each time, even if only a little of it is a candiate for freezing.

Unless you tune autovacuum to freeze more aggressively, it tends to leave forced freezes quite late and then do a LOT of work at once. This is efficient since we don't scan the table lots of times, but expensive since we do a lot of work each time we do process it.

When vacuum isn't freezing tuples it's still doing much of the same work. But it's usually touching much smaller numbers of pages on each pass since it only has to care about tuples that have a not-for-locking-only xmax set by a since-committed transaction. The pages with these tuples are also MUCH more likely to be in cache since they're recently deleted, and any multixact data for them is more likely to be in cache too.

You can make freeze vacuums faster by:

Lowering autovacuum_vacuum_cost_delay - this will make autovacuum use more resources and put more load on the system, but run faster.
Lowering autovacuum_freeze_max_age so they run more often and do less work each time. This is overall less efficient, since the whole table must be scanned each time, evicting pages from the OS buffer cache as we read the table. (Pg uses a ring-buffer for seq scans, but the OS generally won't). But it means we do fewer writes for each pass and each pass takes less time.
Doing COPY with the FREEZE option to bulk-load a table inside the same transaction that TRUNCATEd it or CREATEd it. Tuples loaded this way start off pre-frozen. (See the manual on COPY for details).
Manually freezing tables after bulk-loading them, so you pay the freeze cost at a time you control.

Personally I'd like normal VACUUMs (and autovacuums) to do more opportunistic freezing; in particular, if we've just touched the item pointer array on a page to mark some tuples as free space, we should probably scan for freeze-able tuples while the page is dirty. This would play very well with the new freeze map code in PostgreSQL 10.

There are some big improvements in freezing in PostgreSQL 9.6 that reduce the impact of repeated vacuums on tables and make freezing much cheaper. Due to the introduction of the freeze map functionality in the visibility map we can avoid scanning the whole table, skipping over frozen pages. See commits a892234 and fd31cd265.

commit fd31cd265138019dcccc9b5fe53043670898bc9f
Author: Robert Haas <rhaas@postgresql.org>
Date:   Thu Mar 10 16:12:10 2016 -0500

    Don't vacuum all-frozen pages.

    Commit a892234f830e832110f63fc0a2afce2fb21d1584 gave us enough
    infrastructure to avoid vacuuming pages where every tuple on the
    page is already frozen.  So, replace the notion of a scan_all or
    whole-table vacuum with the less onerous notion of an "aggressive"
    vacuum, which will pages that are all-visible, but still skip those
    that are all-frozen.

    This should greatly reduce the cost of anti-wraparound vacuuming
    on large clusters where the majority of data is never touched
    between one cycle and the next, because we'll no longer have to
    read all of those pages only to find out that we don't need to
    do anything with them.

    Patch by me, reviewed by Masahiko Sawada.

I think there is also work in progress for PostgreSQL 10 to entirely avoid the need to modify tuples on disk to mark them as frozen, but can't find the relevant mailing list thread atm.


Mapping SQL Server integer to Oracle Datatype

I don't see any reason to not use integer in Oracle. It will be mapped to the approriate Oracle datatype but keeps the DDL at least somewhat portable.

I also always use VARCHAR instead of VARCHAR2 as they are equivalent anyway. Oracle does not recommend this, as in the future this might change, but this has been true since version 6, so I doubt that it will actually change at all ;)


Try looking at a couple of things to help determine if you are having issues with the network.
Wait Stats

ASYNC_NETWORK_IO

On The Server

Network Interface:Output queue length
Redirector:Network error/sec

This should always be zero

TCPv4:Segments retransmitted/sec & TCPv6:Segment retransmitted/sec

When divided over the total number of segments should not be more than 3%.


Fill a GTT with external data each session

I have to import an external csv file into a table in database each
  day.

  ...

  Additionally, It is much appreciated if the importing could be done
  entirely without the need for GTTs and loading a file straight into
  the destination table.

And that is what SQL*Loader was made for.

SQL> create table employee
(
  id integer,
  name varchar2(10),
  dept varchar2(15),
  salary integer
);

$ cat employee.csv
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
501,Ritu,Accounting,5400

$ cat load.ctl
load data
 infile 'employee.csv'
 append
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )

Load data into empty table:

$ sqlldr user/password@database control=load.ctl

SQL> select * from employee;

        ID NAME       DEPT                SALARY
---------- ---------- --------------- ----------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000
       501 Ritu       Accounting            5400

Append data (notice the append keyword in load.ctl):

$ sqlldr user/password@database control=load.ctl

SQL> select * from employee;

        ID NAME       DEPT                SALARY
---------- ---------- --------------- ----------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000
       501 Ritu       Accounting            5400
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000
       501 Ritu       Accounting            5400


Does Oracle RAC require that the Ethernet aliases be in a different IP range than the base addresses?

From what I've seen RAC requires that you create Ethernet alias
  interfaces (eth0:0) on each node, with addresses in a different range.

Thats not correct. If eth0 is the interface for the public network, then the VIPs and SCAN VIPs must be on the same subnet, that is exactly what you are asking for. Also its not you, but Oracle Grid Infrastructure that creates and manages these virtual IPs, you just specify the name and address for them.

Furthermore, the private virtual IPs (called HAIP) on the private network (lets say eth1) will be created in the 169.254.0.0/16 subnet, no matter what.

So here is a sample configuration for a 3 node cluster:

Public addresses:

node1, eth0: 192.168.1.1/24
node2, eth0: 192.168.1.2/24
node3, eth0: 192.168.1.3/24

VIPs, with names defined in DNS or hosts file:

node1-vip, eth0:0: 192.168.1.11/24
node2-vip, eth0:0: 192.168.1.12/24
node3-vip, eth0:0: 192.168.1.13/24

Private addresses (note the different subnet), names are not necessary to be defined:

node1-priv, eth1: 192.168.2.1/24
node2-priv, eth1: 192.168.2.2/24
node3-priv, eth1: 192.168.2.3/24

SCAN VIPs (11.2.0.1 or above), with one single name defined in DNS:

192.168.1.21/24
192.168.1.22/24
192.168.1.23/24

Can run on any node as eth0:X. Number of SCAN VIPs is independent of number of nodes.

HAIP (11.2.0.2 or above), no names, the IPs are dynamic, so I have just put some random IPs here to complete the example:

node1: eth1.1: 169.254.190.49/16
node2: eth1.1: 169.254.203.17/16
node3: eth1.1: 169.254.243.66/16


Importing .dmp file into SQL developer: Do I need to import seperately for each table?

This import file is an export done with a tool such as exp73, exp or expdp.

The manner of importing data you are referring to when you have data in an excel sheet with coma separated value, SQL developer will create a large insert statement with the data for you and run it, which will populate a table.

What you want to do is to use oracle import tools such as impdp or imp.

Do you happen to have the export's log ? There might be a file with the same name that finishes with a .log

Here is an example of syntax on how to import specific tables of an Oracle export

impdp username/password@oracle_sid tables=table1,table2 directory=data_pump_dir dumpfile=dumpfilename.dmp logfile=import_exec_log.log

You will want to run the following query to find where your data pump dir location is and place the export in the corresponding folder :

 select * from dba_directories where directory_name ='DATA_PUMP_DIR';


join and select multi values for each row

Should be as simple as:

SELECT  U.ID, S.ID
FROM    SKILL S
INNER JOIN USER U      -- in case you want more user details in the results.
ON      U.ID = S.USER_ID
ORDER BY U.ID, S.ID

Will retrieve all the users and each skill they have.

If Skills is not a child of User (i.e. each skill can also be associated with multiple users) then you need a USER_SKILL table to link the two together. Then the query would be:

SELECT  USER_ID, SKILL_ID
FROM    USER_SKILL
ORDER BY USER_ID, SKILL_ID

In future it is a good idea to include the query you are using (which caused the error) in your question.


A parametrized query will help you here. It's a well-established way to create queries that protect against  SQL injections and often improve performance too. The advantage in your case is that parametrization handles string escaping, so you don't need to worry about passing an apostrophe.

As a side note, depending on your database, trying to escape strings might be fool's errand, as PHP/MySql's notorious mysql_real_escape_string() proves with attack opportunities. Writing secure code is hard, and any database is vulnerable to attacks via poor development practices.

Using parameters requires one to build a .Net SqlCommand and add parameters into its Parameters collection.

The bad news is that configuring a SqlCommand requires working proficiency in .Net, and the documentation is huge. Relevant subsections are about commands and parameters.
Static typing for parameters is possible, though implicit conversion works in many cases. For static types, see .Add() method's numerous overloads. Further develpoment questions and script improvement would be better on-topic at Stack Overflow.

In case of link rot, a sample from the web that seems to work fine. Note that it relies on implicit conversion.

function exec-query( $sql,$parameters=@{},$conn,$timeout=30,[switch]$help){
 if ($help){
 $msg = @"
Execute a sql statement.  Parameters are allowed.
Input parameters should be a dictionary of parameter names and values.
Return value will usually be a list of datarows.
"@
 Write-Host $msg
 return
 }
 $cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
 $cmd.CommandTimeout=$timeout
 foreach($p in $parameters.Keys){
    [Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
 }

 $ds=New-Object system.Data.DataSet
 $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
 $da.fill($ds) | Out-Null

 return $ds
}

$conn=new-object data.sqlclient.sqlconnection "Server=<MyServer>;initial catalog=<SomeDatabase>;Integrated Security=True"
$conn.open()
(exec-query "select column from dbo.mytable where column=@type" -parameter @{type=1} -conn $conn).tables
$conn.dispose()


How can I achieve a unique constraint with two fields?

Assuming the following table definition

 CREATE TABLE T
 (
 Name VARCHAR(50) NOT NULL,
 IsDeleted BIT NOT NULL
 )

Then you can achieve this with a unique index filtered to only include those Names that you wish to apply the constraint on.

CREATE UNIQUE INDEX ix ON T(Name) WHERE IsDeleted = 'false'


Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

Does it have to execute immediately after the restore finishes, or can it be briefly delayed? One idea I had is to have a job that runs every minute, and checks for the Audit Backup/Restore Event in the default trace.

DECLARE @fn VARCHAR(MAX);

SELECT @fn = SUBSTRING([path], 0, LEN([path])-CHARINDEX(CHAR(92), REVERSE([path]))+1)
  + CHAR(92) + 'Log.trc'
FROM sys.traces
WHERE is_default = 1;

SELECT
    DatabaseName,
    StartTime,
    TextData
FROM sys.fn_trace_gettable(@fn, DEFAULT)
WHERE EventClass = 115
AND TextData LIKE '%RESTORE%'; -- since can't differentiate between backup/restore

You could store the cleanups you've already done based on StartTime, and even limit the trace query that runs every minute (or whatever your acceptable delay threshold is) to only look at StartTime values greater than the last row you pulled, or the last time the job ran, whichever is less.


That limitation is for a single line.

You can break your statement into multiple lines to overcome that limitation, such as:

begin
  :sql_txt := 'select ... ' ||
  ' ... ' ||
  ...
  ' ... ';
end;
/


How I can copy a collection from a Mongo Cluster to another Mongo Cluster?

As per MongoDB documentation here through the db.cloneCollection(from, collection, query) it's Copies data directly between MongoDB instances.

use users
db.cloneCollection('mongodb.example.net:27017', 'profiles',
                    { 'active' : true } )

This operation copies the profiles collection from the users database on the server at mongodb.example.net into the users database on the local server. The operation only copies documents that satisfy the query { 'active' : true }.

For further your ref here , here , here and here


A PIVOT solution requires a well-know number of columns.
If you're looking for an answer that covers no matter which dates, I'd suggest you to search for 'Dynamic pivot'

;WITH data AS
(
    SELECT  dname,
            tr - dc as result,
            LEFT(DATENAME(MONTH, sdate), 3) + '-' + RIGHT(CAST(YEAR(sdate) AS varchar(10)), 2) as DateName
    FROM    @Test
)
SELECT dname, [Jan-16],[Jan-17],[Feb-16],[Feb-17]
FROM   (SELECT * FROM data) src
PIVOT  (SUM(result) FOR DateName IN([Jan-16],[Jan-17],[Feb-16],[Feb-17])) pvt;

GO

dname | Jan-16 | Jan-17 | Feb-16 | Feb-17
:---- | -----: | -----: | -----: | -----:
Green |    103 |     90 |   null |    375

dbfiddle here


ORA-12899 when creating varchar virtual column based on numeric column

The error message is somewhat strange as presented, but when you get this message creating a virtual column, the value for maximum is the minimum length that you need to specify to be able to create the column.

The reason for this is that Oracle doesn't know the maximum length of the expression to_char(a) and assumes that it will be 40 characters: 38 places of precision for the number, plus 1 for each of the decimal point and positive/negative indicator.

Add 2 characters for "A:" and the minimum length that Oracle judges as "safe" is 42.


Gaps and islands with GPS breadcrumb data

This was a tricky one. I've used PostgreSQL for the fiddle - you should be able to "translate" into SQL Server dialect - with a bit of modification, it'll work on any server that has Common Table Expressions.

I'll outline the steps. Full code as well as data are on the fiddle as well as at the end of this answer. There are a few "fossils" - i.e. extra fields that helped in the problem solving process that I haven't deleted, but they should be easy to clear up.

The first thing to do (a bit like relationships) is to establish boundaries.

Find the upper limits of the 1 hour differences between tranches of measurements - start by using the LEAD() function:

SELECT
  truck_id,
  location_name,
  loc_date_time,
  LEAD (loc_date_time, 1) OVER (PARTITION BY truck_id, location_name ORDER BY loc_date_time) AS the_lead
FROM truck_loc
ORDER BY loc_date_time;

Result (for brevity, only 3 records are shown):

truck_id      location_name loc_date_time           the_lead
MonsterTruck       Home 2019-10-22 10:27:40 2019-10-22 10:27:40
MonsterTruck       Home 2019-10-22 10:27:40 2019-10-22 10:33:10
MonsterTruck       Home 2019-10-22 10:33:10 2019-10-22 11:22:40

Then, find the gaps which are greater than or equal to 1 hour:

SELECT t1.truck_id, t1.location_name, t1.loc_date_time
FROM
(
  SELECT
    truck_id,
    location_name,
    loc_date_time,
    LEAD (loc_date_time, 1) OVER (PARTITION BY truck_id ORDER BY loc_date_time) AS the_lead
  FROM truck_loc
) AS t1
WHERE t1.the_lead >= t1.loc_date_time + INTERVAL '1 HOUR'
OR t1.the_lead IS NULL
ORDER BY t1.loc_date_time;

The OR t1.the_lead IS NULL test is there because the last LEAD() of a series like this is always NULL.

Result:

truck_id        location_name   loc_date_time
MonsterTruck             Home       2019-10-22 11:22:40
MonsterTruck      Gas Station       2019-10-22 13:43:00
MonsterTruck             Home       2019-10-22 17:43:00
MonsterTruck             Home       2019-10-22 20:56:00

Inspection shows that this is correct.

Then, you basically do the same again for LAG() - the code is in the fiddle (extra fields are "fossils")!

substr  location_name   loc_date_time           t           tlag            diff
Mon          Home   2019-10-22 10:27:40 1571740060
Mon   Gas Station   2019-10-22 12:43:00 1571748180
Mon          Home   2019-10-22 16:43:00 1571762580  1571743360  19220
Mon          Home   2019-10-22 20:43:00 1571776980  1571766180  10800

So, now you form 2 CTEs (Common Table Expressions) from the two queries above and run the following SQL:

SELECT
  SUBSTR(c1.truck_id, 1, 3) AS tid_1, SUBSTR(c2.truck_id, 1, 3) AS tid_2,
  SUBSTR(c1.location_name, 1, 3) as loc_1, SUBSTR(c2.location_name, 1, 3) AS loc_2,
  c2.loc_date_time AS ldt_1, c1.loc_date_time AS ldt_2,
  (EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) AS diff
FROM cte1 c1
JOIN cte2 c2
  ON c1.truck_id = c2.truck_id AND
     c1.location_name = c2.location_name
WHERE ABS(EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) <= 3600
ORDER BY c2.loc_date_time;

Result:

tid_1   tid_2   loc_1   loc_2    ldt_1                   ldt_2                  diff
  Mon     Mon     Hom     Hom    2019-10-22 10:27:40     2019-10-22 11:22:40    3300
  Mon     Mon     Gas     Gas   2019-10-22 12:43:00     2019-10-22 13:43:00 3600
  Mon     Mon     Hom     Hom   2019-10-22 16:43:00     2019-10-22 17:43:00 3600
  Mon     Mon     Hom     Hom   2019-10-22 20:43:00     2019-10-22 20:56:00 780

Which is the desired result! Full SQL below. It requires a bit of tidying up, but I'll leave that to you - I've left the extraneous stuff in so that it will (should!) be easier to follow my thought processes as the solution evolved.

In this thread, even though I didn't answer the question asked, this might prove helpful in getting the hang of LAG() and LEAD(). Also you could take a look at the accepted answer in that same thread.

=============== Full SQL and DDL and DML for data ================

SQL:

WITH cte1 AS
(
  SELECT t1.truck_id, t1.location_name, t1.loc_date_time
  FROM
  (
    SELECT
      truck_id,
      location_name,
      loc_date_time,
      LEAD (loc_date_time, 1) OVER (PARTITION BY truck_id ORDER BY loc_date_time) AS the_lead
    FROM truck_loc
  ) AS t1
  WHERE t1.the_lead >= t1.loc_date_time + INTERVAL '1 HOUR' -- https://stackoverflow.com/a/13828231/470530
  OR t1.the_lead IS NULL
  ORDER BY t1.loc_date_time
),
cte2 AS
(
  SELECT t1.truck_id, t1.location_name, t1.loc_date_time,
         EXTRACT(EPOCH FROM t1.loc_date_time) AS t, EXTRACT(EPOCH FROM t1.the_lag) AS tlag,
         EXTRACT(EPOCH FROM t1.loc_date_time) - EXTRACT(EPOCH FROM t1.the_lag) AS diff
  FROM
  (
    SELECT
      truck_id,
      location_name,
      loc_date_time,
      LAG (loc_date_time, 1) OVER (PARTITION BY truck_id, location_name ORDER BY loc_date_time) AS the_lag
    FROM truck_loc
  ) AS t1
  WHERE t1.the_lag <= t1.loc_date_time - INTERVAL '1 HOUR' -- https://stackoverflow.com/a/13828231/470530
  OR t1.the_lag IS NULL
  ORDER BY t1.loc_date_time
)
SELECT
  SUBSTR(c1.truck_id, 1, 3) AS tid_1, SUBSTR(c2.truck_id, 1, 3) AS tid_2,
  SUBSTR(c1.location_name, 1, 3) as loc_1, SUBSTR(c2.location_name, 1, 3) AS loc_2,
  c2.loc_date_time AS ldt_1, c1.loc_date_time AS ldt_2,
  (EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) AS diff
FROM cte1 c1
JOIN cte2 c2
  ON c1.truck_id = c2.truck_id AND
     c1.location_name = c2.location_name
WHERE ABS(EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) <= 3600
ORDER BY c2.loc_date_time;

DDL:

CREATE TABLE truck_loc
(
    truck_id VARCHAR(50),
    location_name VARCHAR(12),
    loc_date_time TIMESTAMP
);

DML:

INSERT INTO truck_loc (truck_id, location_name, loc_date_time)
VALUES
('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'),
('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'),
('MonsterTruck', 'Home', '2019-10-22 10:33:10.000'),
('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'),
('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'),
('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'),
('MonsterTruck', 'Gas Station', '2019-10-22 12:43:00.000'),
('MonsterTruck', 'Gas Station', '2019-10-22 13:13:00.000'),
('MonsterTruck', 'Gas Station', '2019-10-22 13:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 16:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 16:49:00.000'),
('MonsterTruck', 'Home', '2019-10-22 17:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 20:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 20:56:00.000');


Oracle 12c. Is it possible to export a TDE encrypted table using expdp / impdp to a database without TDE / wallet enabled?

TDE encrypts the data at rest only. As soon as the data is read off the disk, it is decrypted.
So if you need to copy the data to a new database instance you can just do a CTAS or IAS over the db link.
You should be able to use datapump also, but you have db link between the two database it is the easiest solution.


As stated before, you can use Resource Governer to limit memory usage for specific users.

To summarize shortly the steps you should do:

Create a resource pool. This example has max 30% of memory, be careful with that.

CREATE RESOURCE POOL UserMemoryPool
WITH
( MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=30)
GO

Create a workload group which will use resource pool.

CREATE WORKLOAD GROUP UserMemoryGroup
USING UserMemoryPool;
GO

You should define a Classifier function to tell SQL Server who is gonna be in this group.

CREATE FUNCTION dbo.UserClassifier()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Workload AS SYSNAME
    IF(SUSER_NAME() = 'UserNameToLimit')/*Specify the user*/
        SET @Workload = 'UserMemoryGroup'
    ELSE
        SET @Workload = 'default'
    RETURN @Workload
END
GO

Lastly to enable Resource Governor

USE master
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.UserClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;

You can find additional info at Resource Governor at MSDN


If we look at the actual plan, the current query is reading too much data from the table to be updated.  This is from the index seek on BoxTrackInfo:

This is an index seek on btid for each row that comes out of the scan of BlueTrackEvents.  Update locks are acquired as btStatusID is checked to see if the row qualifies for the update.  Only 1,401 rows qualify for the update, but many more locks are taken in the process - resulting in lock escalation to the table level.
You really want a different plan shape - to seek into the BoxTrackInfo table on btStatusID and then join to BlueTrackEvents, which should acquire significantly less locks.  To that end, adding an index like this should help:
CREATE NONCLUSTERED INDEX IX_btStatusID
ON dbo.BoxTrackInfo (btStatusID)
INCLUDE (btType);

This should more efficiently locate qualifying rows, hopefully allowing the update to complete without lock escalation.

As a side note, the current execution plan validates the foreign key constraint on btStatusID using a merge semi-join:

This is probably not a big deal in your case, since there are only 267 rows in the LBoxTrackStatus table.  If that table were larger, you might consider adding a LOOP JOIN or FAST 1 hint to the query in order to get nested loops FK validation.  See this post for details:
Why am I getting a snapshot isolation issue on INSERT?


drop table t1 purge;
create table t1 (id number, cxml clob);
insert into t1 values (1,
'<?xml version="1.0" encoding="UTF-8"?>
<l-analysis version="2.9">
   <vehicle>
      <odo-reading latest="true">
         <reading value="653463">653,463</reading>
         <reading-date value="2018-12-04">4-Dec-2018</reading-date>
         <reading-unit code="K">Km</reading-unit>
      </odo-reading>
  <odo-reading latest="false">
     <reading value="391264">393,264</reading>
     <reading-date value="2018-12-04">4-Dec-2018</reading-date>
     <reading-unit code="K">Km</reading-unit>
  </odo-reading>
   </vehicle>
</l-analysis>');
commit;

select extractvalue(xmltype(cxml), 'l-analysis/vehicle/odo-reading[@latest="true"]/reading/@value') as value from t1;

VALUE
----------
653463

Answer to the original question before it was edited:

drop table t1 purge;
create table t1 (id number, cxml clob);
insert into t1 values (1,
'<?xml version="1.0" encoding="UTF-8"?>
<l-analysis version="2.9">
   <vehicle>
      <odo-reading latest="true">
         <reading value="653463">653,463</reading>
         <reading-date value="2018-12-04">4-Dec-2018</reading-date>
         <reading-unit code="K">Km</reading-unit>
      </odo-reading>
   </vehicle>
</l-analysis>');
insert into t1 values (2,
'<?xml version="1.0" encoding="UTF-8"?>
<l-analysis version="2.9">
   <vehicle>
      <odo-reading latest="false">
         <reading value="653463">653,464</reading>
         <reading-date value="2018-12-04">4-Dec-2018</reading-date>
         <reading-unit code="K">Km</reading-unit>
      </odo-reading>
   </vehicle>
</l-analysis>');
commit;

select id, extractvalue(xmltype(cxml), 'l-analysis/vehicle/odo-reading/reading/@value') as value from t1
where extractvalue(xmltype(cxml), 'l-analysis/vehicle/odo-reading/@latest') = 'true';

        ID VALUE
---------- ----------
         1 653463


Bypass MS Access 2GB file size limitation

You can't bypass the built-in limitation of an Access database. You can however circumvent the built-in limitation by using built-in features like splitting the database objects between multiple *.accdb files and referencing them accordingly.

The Access 2016 Specifications state:

  Total size for an Access 2016 database (.accdb), including all database objects and data:

  2 gigabytes, minus the space needed for system objects.

  Note: You can work around this size limitation by linking to tables in other Access databases. You can link to tables in multiple database files, each of which can be as large as 2GB.
  Tip:  For more information on reducing the size of your database, see Help prevent and correct database file problems by using Compact and Repair.

You can even go a step further and, as you have already mentioned, split the data and the interface.

Split an Access database

  Consider splitting any database that several people share over a network. Splitting a shared database can help improve its performance and reduce the chance of database file corruption.

  After you split database, you may decide to move the back-end database, or to use a different back-end database. You can use the Linked Table Manager to change the back-end database that you use.

Because it is Microsoft Access, I would recommend sticking with the supported options rather than trying another solution that might or might NOT work.

We used to have an application that had the interface (FE) and data (BE) split. No noticeable performance difference, when hosted on a file server. However, when the BE reached 2 GB a write corrupted the BE and the data was unrecoverable, that was the time we decided to switch to SQL Server Express and then later to SQL Server. I don't think there should be a noticeable performance impact when you access multiple BE files containing the different tables. But having the BE on file shares will generally be slower than storing them locally.


In the job step advanced properties, send output to a flat file by inputing the "Output file" location and filename.  That should include all of your output for later viewing.

Or, if you want all of the output directly in the history of the job, you can select the check box "Include step output in history".  It'll use history entry rollover to include all of the data.  That might be a little cumbersome in looking at after-the-fact, though, but still effective.


2 similar queries in large sql server table - 1 takes too long

The explain plan (or execution plan) should tell you exactly where the difference lies, and which indexes are being used.

See here for more info on how to get an explain plan.


Add column on huge InnoDB table in MySQL 4.1

You can use statement-based replication and add the column to a slave first.  Any pending modifications will queue up on the slave, and apply once the alter has complete.  When the slave is fully up to date with the new schema, you can effectively 'promote' it.

I am sure you are aware, but MySQL 4.1 is quite old.  Triggers were not introduced until MySQL 5.0 (2005), so this limits your options to use tools like pt-online-schema-change.


The different service tiers of Azure SQL Database are limited by DTUs. Microsoft guarantees a minimum level of resources, which they compute as DTUs. The documentation provides the definition for a DTU as (emphasis mine):

  This amount of resources is calculated as a number of Database
  Transaction Units or DTUs, and is a blended measure of CPU, memory,
  I/O (data and transaction log I/O). The ratio amongst these resources
  was originally determined by an OLTP benchmark workload designed to be
  typical of real-world OLTP workloads.

If you are seeing LOG_RATE_GOVERNOR waits, then you are bumping into the limits imposed on transaction log I/O by your performance tier.

If this wait is impacting performance negatively, you would need to either increase your service tier, or update your code to perform fewer writes. If you have one process that is IO intensive, you could throttle it yourself so that you leave transaction log IOs available for other processes before hitting the limit imposed by your SQL DB service tier.

I wrote a blog post where I used the DTU Calculator to estimate how each of these performance metrics combine to affect the DTU calculation. You may find that helpful in understanding how different types of load are "blended" into the DTU limits.


How to setup sync between MongoDB and C# .NET Application

You need a tailable cursor (http://docs.mongodb.org/master/tutorial/create-tailable-cursor/) that will read the MongoDB oplog and propagate the changes. The oplog logs every CRUD operation and the tailable cursor will trigger on every change.


connect to server with IP address running sql-express instance through sql-management studio

Ensure that the secondary instance (in this case SQL Express) has a TCP port set.

Under SQL Server Configuration Manager, go to SQL Server Network Configuration, then Protocols for [INSTANCENAME].
Right click TCP/IP and under the IP Addresses tab make sure the field "TCP Port" has a value (for example 1533).

Then try to connect to the instance with the newly assigned port:

172.16.11.2\sqlexpress,1533

Voila!


How to create a second socket for mysqld

You will want to use mysqld_multi, which is part of a typical MySQL server install. On my Ubuntu server, it was located at /usr/bin/mysqld_multi.

MySQL's Documentation on mysqld_multi

Here is a how to, I used to set up mysqld_multi: MySQL Multi How to

Update, running a 2nd instance from the shell

Start another mysqld using a different, port, socket and data directory

# make a new data directory
mkdir /var/lib/mysql2

# give ownership to mysql user
chown mysql:mysql -R /var/lib/mysql2

# install mysql database
mysql_install_db --user=mysql --datadir=/var/lib/mysql2/

# run 2nd daemon
/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql2
--user=mysql
--log-error=/var/log/mysqld2.log
--pid-file=/var/lib/mysql2/mysql2.pid
--bind-address=127.0.0.1 --port=3307
--socket=/var/lib/mysql2/mysql2.sock &

Then connect to the 2nd instance with mysql

mysql --port 3307 --socket=/var/lib/mysql2/mysql2.sock


In Oracle AWR report how to use "Latch statistics" section in analysis

Usually, latches are used to optimize SQL. Latches are what provide concurrent access to Oracle's internals. You always want a high hit ratio, although occasionally a high hit ratio isn't indicative of everything running well. Always see if latch "latch free" is in your top wait events: if it is, you might want to look closely at your latches.

Actually using these statistics can be difficult if your new to SQL tuning. A common latch in transaction heavy databases (the ones that I work in are) is the "In memory undo latch" which allows you to rollback. If you ever noticed a high miss rate on this type of latch, you would want to examine not only the total number of SQL events that are rolling back during your AWR window, but you might examine any culprits that are might be issuing rollbacks when there is actually nothing to roll back at all (relatively common in nested loops and recursive function calls).

Thats just one example, and to be honest if you have the time to be researching your latch performance then your DB is running pretty smoothly (unless its on your top 5 wait events). Use them to examine your SQL and you should start to see some benefit in your CPU wait times.

Here are links for more information from people who know far more about this than I do:

http://www.dba-oracle.com/oracle10g_tuning/t_latch_statistics.htm

http://www.dba-oracle.com/m_latch_hit_ratio.htm


If it is always 32 ascii bytes, then say CHAR(32) CHARACTER SET ascii.

There are subtle reasons for not making the field bigger than necessary.  They mostly involve how temporary tables are generated in complex SELECTs.  Most importantly, the tmp table is MEMORY and turns VARCHAR(255) into CHAR(255) of the given charset.  If that is utf8, then you are unnecessarily using 755-32 extra bytes for each row in the tmp table.  This can lead to slowdowns.

"32" smells like hex for something?  UNHEX(...) and put it into BINARY(16) to make the PK even smaller.

Every secondary key (in InnoDB) implicitly includes a copy of the PK.  Making the PK bigger than necessary can impact disk space.


Is there a MySQL character set and encoding that will allow for both emojis and accents?

I was running into problems when people would query for an emoji in the database and my React app would throw an error

What was the exact error message? What were the character set and collation of the column before you changed it to utfmb4 and utf8mb4_unicode_ci? In MySQL, collation can be set at many levels, including the client connection.

That said, Unicode (utf8 being one of the Unicode encodings), supports all characters. If your character set truly is utf8mb4, then there is no need to change that.

You said:

  I'm getting errors when adding new terms saying for example that a duplicate row for "beyoncé" already exists, however when I query the db for "beyoncé", it returns the row with "beyonce" in it.

The MySQL documentation states:

  For nonbinary collation names that do not specify accent sensitivity, it is determined by case sensitivity. If a collation name does not contain _ai or _as, _ci in the name implies _ai and _cs in the name implies _as.

So, since your collation is utf8mb4_unicode_ci, then it is both case-insensitive and accent-insensitive. And this is why "beyoncé" matches "beyonce".

If you need "beyoncé" and "beyonce" to be considered different, then ideally you would use a case-sensitive (and either explicitly-stated or implied accent-sensitive) collation. However, it looks like this is not available in MySQL 5.6 (or even 5.7), while MySQL 8.0 does have utf8mb4_0900_as_cs, or even utf8mb4_0900_as_ci if you only want the accent to distinguish between the values while allowing "beyonce" and "Beyonce" to match.

So for now, it looks like you might need to use a binary collation, utf8mb4_bin, either by changing the collation of the column, or by adding COLLATE utf8mb4_bin to one or more queries.


MySQL how to release memory used up by SELECT

You could run RESET QUERY CACHE to eliminate collected result sets in the query cache. You could also be more aggressive and drop the query cache and recreate it a different size. For example, to set the query_cache_size to 512M, do the following

SET GLOBAL query_cache_size = 0;
SELECT SLEEP(30);
SET GLOBAL query_cache_size = 1024 * 1024 * 512;

Unfortunately, you can't release the MyISAM Key Buffer and InnoDB Buffer Pool in the same manner. You would have to resize them in /etc/my.cnf (or my.ini for Windows) and restart mysql.

Reading your comments, I can confidently say that MyISAM does not cache data pages. The query cache is the only thing I can think of that grabs and holds result sets. If you are loading in bulk, there should be a lot of query cache invalidations going on.

I am not Cluster Certified, but I will take shot at saying this: Since you are using MySQL Cluster, you may want to try shutting down any mysqld processes or having the cluster disconnect data nodes. Also, be aware of the transmission speed that data hops around the Cluster.


How to use OPENQUERY to properly execute a Stored Procedure that updates a linked server table?

OpenRowset is primarily designed for retrieving data, though you can use it to insert and execute stored procedures. I would recommend that you switch to CLR function for executing the remote stored procedure. Heres a simple code you can compile to a .net assembly.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Collections;
using System.Data.SqlTypes;
using System.ComponentModel;

namespace ETL
{
    public class SQLServer
    {

        [SqlFunction(Name = "ExecSQL", SystemDataAccess = SystemDataAccessKind.Read, DataAccess = DataAccessKind.Read)]
        public static SqlString ExecSQL(string server, string Database, string cmdSQL)
        {
            string rValue = "0";
            using (var conn = new SqlConnection(string.Format(@"Server={0};DataBase={1};Integrated Security=SSPI", server.ToString(), Database.ToString())))
            using (var command = new SqlCommand(cmdSQL.ToString(), conn)
            {
                CommandType = CommandType.StoredProcedure
            })
            {
                command.CommandTimeout = 800;
                conn.Open();
                command.ExecuteNonQuery();
                rValue = "1";
            }
            return rValue;
        }
    }
}

Good Luck :)


How can you monitor if MySQL binlog files get corrupted?

There are four(4) things to look for to see if a binlog is corrupt on not:

Binlog Magic Number
Binlog Size
mysqlbinlog
MySQL Replication

BINLOG MAGIC NUMBER

Back on Dec 26, 2011, I wrote about a based-64 number that sits at the top of any binlog (hex value 0xfe 0x62 0x69 0x6e) regardless of MySQL version and regardless of binlog format (STATEMENT,ROW,MIXED). If this hex value is not in the header of the binlog, it is definitely corrupt.

BINLOG FILESIZE

For any binlog position, there is a a corresponding filesize. In other words, whenever mysqld records a SQL command, it does the following:

retrieves the current filesize to the statement size
calculates the size of the statement it will record (comments included)

Comment contains filesize + statement size
This sum is designated the binlog position

Statement is appended to the binlog
Comments are appended to the binlog

In light of this, you can run SHOW MASTER STATUS; and get something like this:

mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000109 | 569025336 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

If there are no writes going on (low writes,high reads), you could compare the filesize of the binlog with the reported binlog position.

Get both values and compare.

If the binlogs are in /var/lib/mysql

BINLOG_INFO=/tmp/binlog.txt
mysql -u... -p... -ANe"show master status" > ${BINLOG_INFO}
BINLOG_FIL=`cat ${BINLOG_INFO} | awk '{print $1}'`
BINLOG_POS=`cat ${BINLOG_INFO} | awk '{print $2}'`
cd /var/lib/mysql
BINLOG_SIZ=`ls -l ${BINLOG_FIL} | awk '{print $5}'`

If there are no writes going on, BINLOG_SIZ should be equal to BINLOG_POS

If there are writes going on, BINLOG_SIZ should be greater or equal to BINLOG_POS

You should never get BINLOG_SIZ greater than BINLOG_POS. If you do, binlog must be corrupt.

mysqlbinlog

The mysqlbinlog utility will dump a text representation of the binlog. If any of the output is garbled, then it it corrupt. However, don't jump to any conclusions if you see this:

BINLOG '
WFxKTRNJAAAAPwAAAKY/YwAAABsAAAAAAAEACHdlYmVkaTMwAA1QYXJ0bmVyQ29uZmlnAAQICA8P
BC0AYwAA
WFxKTRhJAAAAXAAAAAJAYwAAABsAAAAAAAEABP//8BoSAAAAAAAAggMAAAAAAAAJUEFDS0NPVU5U
Azc3MfAaEgAAAAAAAIIDAAAAAAAACVBBQ0tDT1VOVAM3NzI=
'/*!*/;

That is actually a normal signature at the beginning of a mysqlbinlog output. I wrote about this back on Feb 04, 2011 : MySQL master binlog corruption. This is the only exception. Any garbled output from mysqlbinlog that does not look like this is a definite sign of binlog corruption.

MySQL Replication

This is a double-edged sword because of how replication works. Nevertheless, it can still be helpful in two aspects. Keep in mind that a relay log has the exact same characteristics as a binary log.

On a Slave, the IO Thread would parse the incoming binlog entries from the Master and record them in the Slave's current relay log. It is actually running a process like mysqlbinlog. Thus, the mechanism used by the IO Thread is simple:

Read the Comments above the Statement
Get the Expected Position from the Comments
Read the SQL Statement

Subtract Previous Read Position from Last Read Position
This Determines How Many Bytes to Read to Get the SQL Statement

Append Comments and SQL Statement into the Current Relay Log adjusted by Relay Log Size

If Step 3 ever fails, this means the IO thread could not parse in the next incoming SQL.

The Slave will reveal this failure by writing in its error log something like this:

[ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236).
[ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position', Error_code: 1236
111014 20:25:48 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001067', position 183468345.

The fact that error number 1236 is a registered error condition shows that this impossible position scenario can happen upon failure to successfully read incoming SQL. I wrote about this on Oct 20, 2011 : How to handle the My SQL DB Master to Master replication when there was a disk space outage

The Slave knows what position it needed from the header (Binlog Comments) it read before expecting the incoming SQL. There are two causes for this:

CAUSE #1

If there was any network transmission noise or dropped packets, that slight "burp" interrupts the reading of the SQL statement. Thus, it is possible for the header comments to be written and not the SQL that follows.

CAUSE #2

If the binlog on the Master was corrupt to begin with, the IO thread on the Slave will fail for a more obvious reason: GARBAGE IN, GARBAGE OUT !!!

WORKAROUND

The error message example says Slave I/O thread exiting, read up to log 'mysql-bin.001067', position 183468345.. Here is how you can tell which CAUSE happened. Run the following on the Slave:

STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE=''mysql-bin.001067'',
MASTER_LOG_POS=183468345;
START SLAVE;
SELECT SLEEP(10);
SHOW SLAVE STATUS\G

When you run SHOW SLAVE STATUS\G, if you see

       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes

replication is fine. It was CAUSE #1

but if you see

       Slave_IO_Running: No
      Slave_SQL_Running: Yes

BANG !!!...The master binlog was corrupt anyway (CAUSE #2)

You would need to mysqldump the Master, load it onto the Slave, and setup replication from scratch.


Shell Script / Tool to check the MySQL Replication Status?

If you don't have a monitoring framework in place, I would highly recommend looking into Nagios. There are others, but for the purpose of this question, Percona has many MySQL monitoring tools available. Two of them specifically monitors slave lag and slave failures. From their blog post:

  The pmp-check-mysql-replication-delay plugin checks for excessive delay, and pmp-check-mysql-replication-running checks that replication is functioning correctly.


Numeric/Decimal, Float/Real. There's a great answer here, some official documentation here and here, and I also posted a few opinions ages ago, but here is a quick demonstration:

-- overflow error:

DECLARE @x BIGINT        = 9999999999999999999;

GO

-- works:

DECLARE @x DECIMAL(38,0) = 99999999999999999999999999999999999999;


How compatible are Sybase and SQL Server clients connecting to the alternate back end?

It's true that Sybase and the Microsoft SQL Server databases share common roots, but they are not any more compatible.

Yes, both engines support T-SQL, they have very similar syntax, even many functions looks and works the same. But this is not enough, between the two products have appeared too many differences at this moment. If you want to migrate between them you need to review code, it's easier because many similarities, but must be done.

Microsoft recommended to use SQL Server Migration Assistant (SSMA) for Sybase - it can help you migrate tables, views, indexes, triggers, and stored procedures. In most cases you need to perform just a few manual patches.

Microsoft provided many helpful resources within subject of Sybase and SQL Server:

Similarities and Differences description
Guide to Migrating to SQL Server 2008
Guide to Migrating to SQL Server 2012


How is it possible for mysql storage engine to be NULL?

I actually wrote a post back in July 2011 ( Modify DEFINER on Many Views ) about how to access views for modification.

It is a fact that when the storage engine is NULL, it is always a View.

The actual definition of the View is

MySQL 5.1/5.5

mysql> desc information_schema.views;
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG        | varchar(512) | NO   |     |         |       |
| TABLE_SCHEMA         | varchar(64)  | NO   |     |         |       |
| TABLE_NAME           | varchar(64)  | NO   |     |         |       |
| VIEW_DEFINITION      | longtext     | NO   |     | NULL    |       |
| CHECK_OPTION         | varchar(8)   | NO   |     |         |       |
| IS_UPDATABLE         | varchar(3)   | NO   |     |         |       |
| DEFINER              | varchar(77)  | NO   |     |         |       |
| SECURITY_TYPE        | varchar(7)   | NO   |     |         |       |
| CHARACTER_SET_CLIENT | varchar(32)  | NO   |     |         |       |
| COLLATION_CONNECTION | varchar(32)  | NO   |     |         |       |
+----------------------+--------------+------+-----+---------+-------+
10 rows in set (0.02 sec)

MySQL 5.0

mysql> desc information_schema.views;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)  | NO   |     |         |       |
| TABLE_NAME      | varchar(64)  | NO   |     |         |       |
| VIEW_DEFINITION | longtext     | NO   |     | NULL    |       |
| CHECK_OPTION    | varchar(8)   | NO   |     |         |       |
| IS_UPDATABLE    | varchar(3)   | NO   |     |         |       |
| DEFINER         | varchar(77)  | NO   |     |         |       |
| SECURITY_TYPE   | varchar(7)   | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

To see what the view definition is for sakila.actor_info you can either run

SELECT * FROM information_schema.views
WHERE table_schema='sakila' AND table_name='actor_info'\G

or

SHOW CREATE VIEW sakila.actor_info\G


Query that displays backup information (successful and failed) SQL Server

Sadly, backupset does not contain failed backups, and I don't know of anywhere else in msdb these may be stored, unless you can rely on sysjobhistory, which doesn't contain all of time (depending on your retention settings), and which would ignore any backup attempts that were made outside the context of a job, and which - in the case of a job that backs up many databases - would not provide differentiation about which database actually failed, unless it happened to happen early on in the job - this is because the messaging is quite verbose but gets truncated.

If you absolutely know that Job n only backs up the one database, and that every failure of that job means that the database wasn't backed up (since the job could also fail after the backup succeeded, e.g. trying to shrink or perform other maintenance), then you could use a query like this:

DECLARE @job sysname, @db sysname;

SELECT @job = N'Job 1', @db = N'db_name';

SELECT
   bs.database_name,
   bs.backup_start_date,
   bs.backup_finish_date,
   [Total Time] = CAST((DATEDIFF(SECOND, bs.backup_start_date,bs.backup_finish_date))
     AS varchar(30))+ ' secs',
   CAST(bs.backup_size/1024/1024 AS decimal(10,2)) AS 'Backup Size(MB)',
   h.[message]
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j
ON h.job_id = j.job_id
AND h.step_id = 0
LEFT OUTER JOIN msdb.dbo.backupset AS bs
ON bs.database_name = @db
AND
 ABS(DATEDIFF(SECOND, bs.backup_start_date, CONVERT(DATETIME,convert(char(8),h.run_date)
   + ' ' + STUFF(STUFF(RIGHT('0'+CONVERT(char(6),h.run_time),6),3,0,':'),6,0,':')))) < 5
WHERE j.name = @job
ORDER BY bs.backup_start_date;

Yes, it's really ugly, because sysjobhistory still, in SQL Server 2014 even, stores run_date and run_time as separate integers. I bet whoever made that decision is still on the background of dartboards all over building 35. It also assumes that the backup is the very first step in the job, hence the rather less than scientific date/time comparison to make sure we've properly correlated the right instance of the job to the right instance of the backup. Oh, how I wish I could redesign the schema for backups and jobs.

If you want broader scope outside of the job, you can look for failed backups in the SQL Server error log (if they haven't been cycled away):

EXEC sp_readerrorlog 0, 1, 'BACKUP failed'; -- current
EXEC sp_readerrorlog 1, 1, 'BACKUP failed'; -- .1 (previous)
EXEC sp_readerrorlog 2, 1, 'BACKUP failed'; -- .2 (the one before that)
....

(But I don't know of a nice and easy way to incorporate that output into your existing query.)

You can also correlate "missing" successful backups from the default trace, e.g.

DECLARE @path nvarchar(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT dt.DatabaseName, dt.StartTime, bs.backup_start_date, bs.backup_finish_date,
  [Status] = CASE WHEN bs.backup_start_date IS NULL
    THEN 'Probably failed'
    ELSE 'Seems like success'
  END
FROM sys.fn_trace_gettable(@path, DEFAULT) AS dt
LEFT OUTER JOIN msdb.dbo.backupset AS bs
ON dt.DatabaseName = bs.database_name
AND ABS(DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date)) < 5
WHERE dt.EventClass = 115 -- backup/restore events
AND UPPER(CONVERT(nvarchar(max),dt.TextData)) LIKE N'BACKUP%DATABASE%'
--AND dt.DatabaseName = N'db_name' -- to filter to a single database
--AND bs.database_name = N'db_name'
ORDER BY dt.StartTime;

Of course this also relies on the data from the default trace cycling away, the database name not having changed, etc. And unfortunately, the default trace doesn't differentiate between successful and failed backups, and the start time will not precisely match the MSDB data, but as long as you're not running backups in a loop, this should be okay for eyeballing. I've tried to incorporate these issues into the query.

Finally, you may want to use a FULL OUTER JOIN there, in case backupset has longer history than the default trace. This changes the semantics of [Status] slightly.

You also might want to give this nasty thing a try, though I didn't have much luck with it. I was only able to see the current or most recent status, so that only helped when the job failed the last time it ran, and - like sysjobhistory - wasn't able to obtain information about any backups that were attempted but not through a job.


Whats the difference between Transaction logs and Binary logs?

Answering only the MySQL Part of the Question

A binary log records completed SQL statements. You can have many binary logs. Under default settings, binary logs rotate at the 1G mark (see expire_logs_days and max_binlog_size).

You can see binary logs by running one of the following:

SHOW BINARY LOGS;
SHOW MASTER LOGS;

Ther current master log is always the last one in the list. To see just the last binary log, which is the current one, run this:

SHOW MASTER STATUS;

When it comes to InnoDB storage engine and transactions

There is a metadata file (ibdata1, which holds, by default, data pages, index pages, table metadata and MVCC information), also known as the InnoDB tablespace file.
You can have more than one ibdata file (see innodb_data_file_path)
There are redo logs (ib_logfile0 and ib_logfile1)
You can have more than two redo logs (see innodb_log_files_in_group)
You can spread data and indexes across multiple ibdata files if innodb_file_per_table is disabled
You can separate data and index pages from ibdata into separate tablespace files (see innodb_file_per_table and StackOverflow Post on how to set this up)


Assuming that you have a valid column name or a properly escaped literal in @rowname_date and you don't have leading spaces in it then you have to have a space after SELECT clause

set @sqlt = concat("select", @rowname_date," from syslog_data.syslogs_index_1 ");
                         ^^

Here is SQLFiddle demo


replace all references to a drive inside the jobs to another drive

You could try this cursor approach to read rows from dbo.sysjobsteps and execute sp_update_jobstep after doing a search/replace for the string you want to change.

This could also be improved to ONLY update job steps that actually have the string.

DECLARE @cmd VARCHAR(max)
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @step_id INT
DECLARE @command NVARCHAR(max)

DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT job_id
    ,step_id
    ,command
FROM msdb.dbo.sysjobsteps

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @job_id
    ,@step_id
    ,@command

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @command = replace(@command, 'g:', 'I:')

    EXEC msdb.dbo.sp_update_jobstep @job_id = @job_id
        ,@step_id = @step_id
        ,@Command = @command

    FETCH NEXT
    FROM _CURSOR
    INTO @job_id
        ,@step_id
        ,@command
END --End While

CLOSE _CURSOR

DEALLOCATE _CURSOR


Hide zero hours and minutes with NLS_DATE_FORMAT

No, it can not be done, because 00:00 is a valid time. That you use it to represent "no time available" in your application is your own decision and you have to take care of the consequences yourself.

You could store date and time in separate columns and show only the date if the time is NULL.


How to select the first row of each group?

Use a common table expression (CTE) and a windowing/ranking/partitioning function like ROW_NUMBER.

This query will create an in-memory table called ORDERED and add an additional column of rn which is a sequence of numbers from 1 to N. The PARTITION BY indicates it should restart at 1 every time the value of Val changes and we want to order rows by the smallest value of Kind.

WITH ORDERED AS
(
SELECT
    ID
,   Val
,   kind
,   ROW_NUMBER() OVER (PARTITION BY Val ORDER BY Kind ASC) AS rn
FROM
    mytable
)
SELECT
    ID
,   Val
,   Kind
FROM
    ORDERED
WHERE
    rn = 1;

The above approach should work with any RDBMS that has implemented the ROW_NUMBER() function. Oracle has some elegant functionality as expressed in mik's answer that will generally yield better performance than this answer.


Tool for converting all VARCHAR to NVARCHAR in one or few steps

no tool needed just some good old TSQL code.

This will generate all the statements.

DECLARE @Statements table (Statement varchar(max))
DECLARE @CMD varchar(max)
DECLARE @DB varchar(500)
DECLARE c CURSOR for select distinct name from sys.databases where database_id > 4
OPEN C
FETCH NEXT FROM C into @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD = ' USE '+quotename(@DB)+';

select ''USE '+quotename(@DB)+' GO
ALTER TABLE ''+QUOTENAME(s.name)+''.''+QUOTENAME(t.name)+''
ALTER COLUMN ''+quotename(c.name)+'' varchar(''+cast(c.max_length as varchar)+'')
GO'' from '+QUOTENAME(@DB)+'.sys.tables t
inner join '+QUOTENAME(@DB)+'.sys.schemas s on s.schema_id = t.schema_id
inner join '+QUOTENAME(@DB)+'.sys.columns c on c.object_id = t.object_id
where is_ms_shipped = 0
and system_type_id = 231
'

INSERT INTO @Statements
EXEC (@CMD)

FETCH NEXT FROM C into @DB

END
CLOSE C
DEALLOCaTE C

SELECT * from @Statements


It turns out that I was putting my pidfile in /var/run which is fine for running it but when server is rebooted the file is deleted and pgbouncer can't find the file and it gives the this error

FATAL @src/main.c:553 in function write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: No such file or directory

So simply moving the file to another location and chown the directory was all that was needed

$ sudo mkdir ~/pgbouncer
$ sudo chown -R postgres:postgres ~/pgbouncer


SQL Server : how to drop all constraints on a table in T-SQL

You will have to build your T-SQL statement for each constraint that you want to drop. There are undocumented procedures that could be used for this like sp_MSforeachtable, but I am not to fond of using them. However, SQLUSA.com provides this as an example:

-- SQL disable all constraints - disable all constraints sql server
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

Another option was provided here on SO, by Aaron Bertrand.

Then if you like PowerShell you could do something like this (forum located on SQLServerCentral.com), provided by Rob Farley:

#This is one line just broken up for readability.
PS SQLSERVER:\sql\localhost\default\Databases\YourDatabase> dir Tables\*\Columns\*\DefaultConstraint | % {$_.Script(); $_.Drop()}

And if you want a few more examples check here.


Custom index maintenance percentages for a very large table

It really depends on how transactional your tables are. If they are frequently being updated and inserted into, you'll definitely hit the 10%. If you're not hitting the 10% then it's probably not even worth it to lower to 5% because that means your tables are fairly well unfragmented already.
I've personally worked on a very similarly sized database to your example, also SQL Server 2016 standard, on a server that only had 16 GB of RAM. Our database was fairly transactional (adding about 1,000 new records every few minutes) and we were hitting the 10% threshold for reorgs every night. Because the operation was so heavy and took a while, I actually did the opposite and increased the fragmentation threshold for reorgs to 20% for our larger Tables' indexes. This proved a lot better for our business because the different in performance was negligible and it generally allowed for the larger tables to not be reorganized until the weekend.
Worrying about index reorgs, and even more so rebuilds, is on the low end of things to optimize for. Even Brent Ozar says not to worry about it. :)


How to create trigger to update timestamp when record is updated or inserted in another table

Thanks Michael for your help!

Good to know that @variables are users variables and without @ are local variables.

The following snipped worked well

USE `myTask`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `t_taskaction_AUPD`
AFTER UPDATE ON `t_taskaction`
FOR EACH ROW
BEGIN
    DECLARE _idTask INT(11);
    DECLARE _idTaskState INT(11);

    set _idTask = NEW.idTask;

    set _idTask = (SELECT idTaskState
    FROM t_task WHERE idTask = _idTask);

    UPDATE t_task
    SET timestamp = now()
    WHERE idTask = _idTask;
END$$
DELIMITER ;


Memory and core count can go a long way towards affecting the plan choice.

You can force the dev instance to think it has the same core-count and memory in place, for the purposes of plan-creation, with DBCC OPTIMIZER_WHATIF.

The syntax is:

dbcc optimizer_whatif ({property/cost_number | property_name}
    [, {integer_value | string_value} ])

Properties you can specify include:

Status
CPUs
MemoryMBs

dbcc optimizer_whatif ('Status'); shows the current settings in effect.  On SQL Server 2016, I see the following output:

---------------------------------------------------------
Optimizer what-if status
---------------------------------------------------------
property_number current_value default_value property_name
---------------------------------------------------------
         1                  8             0 CPUs
         2                  0             0 MemoryMBs
         3                  0             0 Bits
         4               1000          1000 ParallelCardThreshold
         5                  1             1 ParallelCostThreshold
         6                200           200 ParallelCardCrossProd
         7                 50            50 LowCEThresholdFactorBy10
         8                 12            12 HighCEThresholdFactorBy10
         9             100000        100000 CEThresholdFactorCrossover
        10                 10            10 DMLChangeThreshold

So, if your production server has 16 cores, but your dev server only has 4 cores, you can trick your dev server query optimizer to act like it has 16 cores by running this:

dbcc optimizer_whatif ('CPUs', 16);

Memory can be similarly "reconfigured", with:

dbcc optimizer_whatif ('MemoryMBs', 20480);

Now, when I run the Status command, I see:

---------------------------------------------------------
Optimizer what-if status
---------------------------------------------------------
property_number current_value default_value property_name
---------------------------------------------------------
         1                 16             0 CPUs
         2              20480             0 MemoryMBs
         3                  0             0 Bits
         4               1000          1000 ParallelCardThreshold
         5                  1             1 ParallelCostThreshold
         6                200           200 ParallelCardCrossProd
         7                 50            50 LowCEThresholdFactorBy10
         8                 12            12 HighCEThresholdFactorBy10
         9             100000        100000 CEThresholdFactorCrossover
        10                 10            10 DMLChangeThreshold

FYI, in order to see the output above, you need to enable the following trace flags:

dbcc traceon (2588);
dbcc traceon (3604);

2588 can be used to force DBCC HELP to display syntax for undocumented DBCC statements, such as optimizer_whatif.

3604 sends trace output to the client. Effective only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.

I would recommend setting the CPU and memory with optimizer_whatif to the same values you see in production, and re-run your queries.  You may need to flush the plan cache prior to re-running your queries.