Walking a BTREE index as far as possible in MySQL

If you really want to walk just the b-tree index, using the innodb_ruby project can help
http://blog.jcole.us/2013/01/14/efficiently-traversing-innodb-btrees-with-the-page-directory/

I think your logic is reversed in your query. It'll start with the shortest word.

How I would handle it is this

DROP PROCEDURE IF EXISTS `find_longest_prefix`;

DELIMITER $$

CREATE PROCEDURE `find_longest_prefix`(IN `word` varchar(255), OUT `word_prefix` varchar(255))
BEGIN
    SET max_sp_recursion_depth = 255;
    SET @nextWord = LEFT(`word`, LENGTH(`word`)-1);

    SELECT COUNT(DISTINCT `words`) FROM `myTABLE` WHERE `words` LIKE CONCAT(`word`, '%') INTO @word_count;

    IF (@word_count > 0)
    THEN
        SET `word_prefix` = `word`;
    ELSE
        IF (LENGTH(@nextWord) > 0)
        THEN
            Call `find_longest_prefix`(@nextWord, `word_prefix`);
        ELSE
            SET `word_prefix` = '';
        END IF;
    END IF;
END$$

DELIMITER ;

This uses the fact that finding a miss on a btree is fast so we just loop around calling recursively until we have a hit.

Examples

No results

mysql> CALL find_longest_prefix(';autobon', @prefix);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @prefix;
+---------+
| @prefix |
+---------+
|         |
+---------+
1 row in set (0.00 sec)

A few results

mysql> CALL find_longest_prefix('autobon', @prefix);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @prefix;
+---------+
| @prefix |
+---------+
| auto    |
+---------+
1 row in set (0.00 sec)

And as you can see, the data is correct:

mysql> SELECT * FROM myTable WHERE words LIKE 'auto%' OR words LIKE ';auto%';
+----+-----------------+
| id | words           |
+----+-----------------+
| 19 | AUTOCOMMIT      |
| 20 | AUTOEXTEND_SIZE |
+----+-----------------+
2 rows in set (0.00 sec)

This method should be much faster then actually checking every step before the longest and getting a lot more returned data.

It should be easy for you to have the stored proc SELECT the rows once it finds the correct prefix and return them if you'd prefer.


Avoid user login Directly to AlwaysOn High Availability Primary

There are a few questions that are similar to what you are asking, may be board-line duplicate but either way:

Protect SQL Server from hackers
Limit connection rights to SQL Server

That second question listed is probably the duplicate of this one.

Overall if you are looking at ensuring logins can only access via the listener you are going to have problems trying to use a login trigger. I am not sure you can pick up DNS or IP address it used to actually connect to SQL Server. If it can then it would be a viable option.

I would opt to use your firewall or network infrastructure. A firewall can be much more efficient and easier to manage with blocking access to either the IP or access from an IP range to a specific IP.


mysqlimport imports NULL values unwantedly as int 0

This issue is caused by the way MySQL LOAD DATA handles NULLs:

  Handling of NULL values varies according to the FIELDS and LINES
  options in use:

  For the default FIELDS and LINES values, NULL is written as a field
  value of \N for output, and a field value of \N is read as NULL for
  input (assuming that the ESCAPED BY character is ).

  If FIELDS ENCLOSED BY is not empty, a field containing the literal
  word NULL as its value is read as a NULL value. This differs from the
  word NULL enclosed within FIELDS ENCLOSED BY characters, which is read
  as the string 'NULL'.

  If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.

  With fixed-row format (which is used when FIELDS TERMINATED BY and
  FIELDS ENCLOSED BY are both empty), NULL is written as an empty
  string. This causes both NULL values and empty strings in the table to
  be indistinguishable when written to the file because both are written
  as empty strings. If you need to be able to tell the two apart when
  reading the file back in, you should not use fixed-row format.

In your case NULL is considered as word 'NULL' and if you try to cast 'NULL' to number it will return 0. You need to change NULL to \N or enclose strings using some character.


Full-text search on column for existence of a keyword from a table

Concatenating the keywords with an OR(|) separator into a variable appears to work.

DECLARE
    @Keywords NVARCHAR(4000)

SELECT
    @Keywords = STUFF((
                    SELECT
                        '|' + Word

                    FROM
                        dbo.Keywords

                    FOR XML PATH('')
                ), 1, 1, '')

SELECT
    *

FROM
    dbo.Notes

WHERE
    CONTAINS(Note, @Keywords)


How to count number of occurrences for all different values in database column and obtain 0 if are there empty result?

Use the FILTER clause

SELECT city,
       sum(quantity::integer) FILTER (WHERE type = 'Man') AS man,
       ...
FROM doors JOIN ...
GROUP BY doors.city, ...;


Since the search always includes the ReturnDate column, it seems reasonable to create the clustered index on that column.
Based on your stated assumption that you'll add around 400,000 rows per year, and you have 4,000,000 rows in the table now, it seems like you might reasonably have 10 years of data, or 3650 unique ReturnDate values at the current time.  (4,000,000 / 400,000 = 10)
With the clustered index on ReturnDate, selectivity on the clustered index would be on average around 1,300 rows per ReturnDate value.
If SQL Server does a seek into the clustered index to the start of the range of values fitting the desired ReturnDate, it will only need to scan approximately 1,300 rows to find the other values to fit the search criteria.
I've setup a simple test-bed to test this hypothesis:
IF OBJECT_ID(N'dbo.Bil_full', N'U') IS NOT NULL DROP TABLE dbo.Bil_full;

CREATE TABLE dbo.Bil_full
(
      PolicyNumber nvarchar(32) NOT NULL
    , PaymentReference nvarchar(32) NULL
    , AccountingDate datetime NOT NULL
    , ReturnDate datetime NOT NULL
    , PaymentAmount decimal(19, 2) NOT NULL
    , RegionCode nvarchar(5) NULL
    , PolicyStateCode nvarchar (3) NULL
    , CompanyCode nvarchar(4) NULL
    , LineOfBusiness nvarchar(4) NULL
    , ReturnReasonCode nvarchar(3) NULL
    , PaymentType nvarchar(20) NULL
    , PaymentPlan nvarchar(20) NULL
    , EntryUserId nvarchar(20) NULL
    , PolicyTermId bigint NULL
    , PaymentDate datetime NULL
    , DateOfFirstReturn datetime NULL
    , BankId nvarchar(9) NULL
    , EquityDateOfPolicy datetime NULL
    , OrginalStartDateOfPolicy datetime NULL
    , PaymentSource nvarchar(20) NULL
    , DBTransactionTimeStamp DATETIME NOT NULL
        CONSTRAINT DF_bil_ts
        DEFAULT(GETDATE())
) ON [PRIMARY];

CREATE CLUSTERED INDEX CX_Bil
ON dbo.Bil_full(ReturnDate);

Here, I'm adding 4,000,000 rows to the table, with a variety of "made-up" data that perhaps approximates the data you might actually have.
;WITH src AS (
    SELECT num = 1.0 + v1.num
        + (v2.num * 10)
        + (v3.num * 100)
        + (v4.num * 1000)
        + (v5.num * 10000)
        + (v6.num * 100000)
        + (v7.num * 1000000)
    FROM  (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v1(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v2(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v3(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v4(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v5(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v6(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v7(num)
)
INSERT TOP(4000000) INTO dbo.Bil_full (PolicyNumber, PaymentReference, AccountingDate, ReturnDate, PaymentAmount, RegionCode, PolicyStateCode, CompanyCode, LineOfBusiness, ReturnReasonCode, PaymentType, PaymentPlan, EntryUserId, PolicyTermId, PaymentDate, DateOfFirstReturn, BankId, EquityDateOfPolicy, OrginalStartDateOfPolicy, PaymentSource, DBTransactionTimeStamp)
SELECT PolicyNumber = N'PN' + RIGHT(N'000000000' + CONVERT(nvarchar(10), src.num), 10)
    , PaymentReference = N'PR' + RIGHT(N'000000000' + CONVERT(nvarchar(10), src.num - 10000), 10)
    , AccountingDate = DATEADD(DAY, src.num % 3715, N'2012-01-01T00:00:00')
    , ReturnDate = DATEADD(DAY, src.num % 3715, N'2012-03-01T00:00:00')
    , PaymentAmount = src.num * CONVERT(int, CRYPT_GEN_RANDOM(1)) * 0.47
    , RegionCode = CASE WHEN src.num % 3 = 1 THEN REPLICATE(char(65 + src.num % 26), 5) ELSE NULL END
    , PolicyStateCode = CASE WHEN src.num % 3 = 2 THEN REPLICATE(CHAR(65 + src.num % 26), 3) ELSE NULL END
    , CompanyCode = CASE WHEN src.num % 5 = 3 THEN REPLICATE(CHAR(65 + src.num % 26), 4) ELSE NULL END
    , LineOfBusiness = CASE WHEN src.num % 6 = 3 THEN REPLICATE(CHAR(66 + src.num % 26), 4) ELSE NULL END
    , ReturnReasonCode = CASE WHEN src.num % 3 = 2 THEN REPLICATE(CHAR(69 + src.num % 26), 3) ELSE NULL END
    , PaymentType = CONVERT(varchar(10), CRYPT_GEN_RANDOM(8), 1)
    , PaymentPlan = CONVERT(varchar(10), CRYPT_GEN_RANDOM(8), 1)
    , EntryUserId = CONVERT(varchar(10), CRYPT_GEN_RANDOM(8), 1)
    , PolicyTermId = CONVERT(bigint, CRYPT_GEN_RANDOM(1))
    , PaymentDate = DATEADD(DAY, src.num % 3715, N'2012-01-01T00:00:00') + 27
    , DateOfFirstReturn = DATEADD(DAY, src.num % 3715, N'2012-03-01T00:00:00') - 16
    , BankId = CONVERT(varchar(8), CRYPT_GEN_RANDOM(6), 1)
    , EquityDateOfPolicy = DATEADD(DAY, src.num % 3715, N'2012-01-01T00:00:00') + 365
    , OrginalStartDateOfPolicy = DATEADD(DAY, src.num % 3715, N'2012-01-01T00:00:00') + 15
    , PaymentSource = CONVERT(varchar(10), CRYPT_GEN_RANDOM(8), 1)
    , DBTransactionTimeStamp = DATEADD(SECOND, src.num, N'2013-01-01T00:00:00')
FROM src
ORDER BY src.num

The table has 1346 rows per ReturnDate from 2012-03-05 to 2022-05-02.
Assuming every query against this table does in fact include a ReportDate value, you could expect execution to consist of a single clustered index seek, which is going to be quite efficient.
For instance, the query:
SELECT *
FROM dbo.Bil_full
WHERE Bil_full.ReturnDate = N'2016-04-01 00:00:00.000'
    AND Bil_full.PolicyNumber LIKE N'%89%'
    AND Bil_full.PaymentType LIKE N'XX%'

Has this query plan:

Statistics I/O shows the following:
Table 'Bil_full'. Scan count 1, logical reads 50, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0,
  lob read-ahead reads 0.

You may notice I've removed the ID column from the table.  If you don't need the column, why include it?  If the date columns do not include times, you can save some space per row by changing the data type to DATE instead of DATETIME.  The nvarchar(..) columns could be changed to just varchar(..) columns if you are not actually storing unicode data; if that is applicable those columns will become half the size they currently are.
I've not created a primary key on the table since your question makes no mention of requiring one for referential integrity or other requirements such as uniqueness.  The table does have a clustered index, which optimizes the physical layout of the table on disk versus the table being a simple heap.  The clustered index (i.e. the table itself) will experience a fair amount of page splits if the ReportDate value for rows being inserted are not monotonically increasing.
You asked about performing a range-scan for the ReportDate where your client is asking for rows between two dates - as in:
SELECT *
FROM dbo.Bil_full
WHERE Bil_full.ReturnDate >= N'2016-04-01 00:00:00.000'
    AND Bil_full.ReturnDate < N'2017-04-02 00:00:00.000'
    AND Bil_full.PolicyNumber LIKE N'%89%'

Queries like this will still perform relatively well; the above query returned 19432 rows very quickly.  Of course, the size of the date range will affect query performance. Keeping the ranges as small as possible, i.e. allowing a maximum range of 30 days, will ensure performance remains manageable.


I was able to solve this problem by doing the following :

Move all the contents of my data directory (in my case /var/lib/mysql/ ) to a safe place.
Execute the command service mysqld start, this will:

recreate two DBs:

mysql
test

recreate ibdata1, ib_logfile0, and ib_logfile0

Copy the contents that you backed up (in step 1) from the safe place to the data directory again, of course, except both mysql and test directories.
Using the following command mysqladmin -u root -p'' password yournewpass reset the password of your root user.

An Advice:

Always make sure you make a backup of your databases structure and data to avoid looping in such loops, just delete every thing, re-install your engine, and restore your data :)


You have couple options. I'd describe one I like the most, but you can find others searching for 'resolving polymorphic association in database'.  Add a common table for companies and contacts (I'd prefer to rename contacts to people or persons), say party. Thus, companies and contacts will have FK to party. Then you add link table party_address(address_id,party_id) with FKs to addresses and party respectively.

UPDATE
For instance (I know it's oversimplified, normally you may have different address_type, address change history, etc, but I guess it illustrates idea).

Note: enum is used because mysql still doesn't have check constraints. Unique constraint on (party_id, party_type) added so child tables can have a foreign key references to it; thus, optional relationship implemented and enforced on database level- Party can be either Person or Organization; no way it can be Person and Organization at the same time.

  CREATE TABLE PARTY (party_id int not null auto_increment primary key,
party_type enum('person','organization') not null,
CONSTRAINT UQ_PARTY UNIQUE(party_id,party_type));

CREATE TABLE PERSON (party_id int not null primary key,
party_type enum('person') NOT NULL DEFAULT 'person',
....
CONSTRAINT FK_PERSON_PARTY FOREIGN KEY(party_id,party_type)
 REFERENCES PARTY(party_id,party_type));

CREATE TABLE ORGANIZATION (party_id int not null primary key,
party_type enum('organization') NOT NULL DEFAULT 'organization',
....
CONSTRAINT FK_PERSON_PARTY FOREIGN KEY(party_id,party_type)
 REFERENCES PARTY(party_id,party_type));

CREATE TABLE ADDRESS(address_id INT NOT NULL auto_increment PRIMARY KEY,
.... );

CREATE TABLE PARTY_ADDRESS (party_id INT NOT NULL, address_id INT NOT NULL,
CONSTRAINT PK_PARTY_ADDRESS PRIMARY KEY(party_id,address_id),
CONSTRAINT FK_PARTY_ADDRESS_PARTY FOREIGN KEY (party_id) REFERENCES PARTY(party_id),
CONSTRAINT FK_PARTY_ADDRESS_ADDRESS FOREIGN KEY (address_id) REFERENCES ADDRESS(address_id));


how to get any record in any table that would violate a foreign key constraint that would prevent my delete?

I have a table called tblBOrder which references a bunch of different tables.

when I run the following script to check all the tables it references I get the result below.

declare @table sysname
declare @Obj int

select @table            = 'tblBOrder'                   --'dbo.tblBCataloguePriceSet'   --'tblBCataloguePriceSetItem'
select @referenced_table = ''                            --'dbo.tblBCataloguePriceSet'

select @obj     = OBJECT_ID(@table)
select @ref_obj = OBJECT_ID(@referenced_table)

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    sch.name AS [referenced_schema],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas sch2
    ON tab2.schema_id = sch2.schema_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
where 1=1
  --AND ( @ref_obj IS NULL OR @ref_obj = tab2.object_id )
  AND ( @OBJ IS NULL OR @obj = tab1.object_id)

Now I want to delete a specific record from my order table.
this is the record I want to delete: USZ000026936

Using the following script I generate a script to view all the records that I need to get rid of first, before I can delete my USZ000026936 in the order table.

SET NOCOUNT ON

declare @RowId varchar(100)
declare @TableName sysname
declare @Command varchar(max)

select @RowID = 'USZ000026936'
select @TableName = 'tblBOrder'

select fkc.*
from sys.foreign_key_columns fkc
    inner join sys.columns col
           on fkc.parent_object_id = col.object_id
          and fkc.parent_column_id = col.column_id
where object_name(referenced_object_id) = @TableName

select @Command = isnull(@Command + CHAR(10) + CHAR(13) + ' union all ', '') + 'select ''' + object_name(parent_object_id) +
    ''' where exists(select * from ' + object_name(parent_object_id) + ' where ' + col.name+ ' = ' + '''' + cast(@RowId as varchar) + '''' + ')'
from sys.foreign_key_columns fkc
    join sys.columns col on
        fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
where object_name(referenced_object_id) = @TableName

print @command

and here is the script generated:

select 'tblBOrderItem' where exists(select * from tblBOrderItem where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderRefund2' where exists(select * from tblBOrderRefund2 where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderAuth2' where exists(select * from tblBOrderAuth2 where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderHistory' where exists(select * from tblBOrderHistory where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderPayment' where exists(select * from tblBOrderPayment where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderOfferOverride' where exists(select * from tblBOrderOfferOverride where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderShipOverride' where exists(select * from tblBOrderShipOverride where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderSpecialDeliveryInstruction' where exists(select * from tblBOrderSpecialDeliveryInstruction where strBxOrderNo = 'USZ000026936')

 union all select 'tblBReturn' where exists(select * from tblBReturn where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderRelationship' where exists(select * from tblBOrderRelationship where strBxOrderNo1 = 'USZ000026936')

 union all select 'tblBOrderRelationship' where exists(select * from tblBOrderRelationship where strBxOrderNo2 = 'USZ000026936')

 union all select 'tblPanViewerAuditLog' where exists(select * from tblPanViewerAuditLog where strBxOrderNo = 'USZ000026936')


How to specify a display name in emails from SSRS subscriptions?

This will need to be set up in Exchange Properties for the email address which you specify in the Reporting Services Configuration Manager


I need a help to get data from a table which has id,serialnumber,process(prcs) and result

Try:

WITH cte AS ( SELECT id,
                     serialnumber,
                     prcs,
                     result,
                     ROW_NUMBER() OVER (PARTITION BY serialnumber
                                        ORDER BY id DESC) rn
              FROM tblTest )
SELECT id, serialnumber, prcs, result
FROM cte
WHERE rn = 1
  AND result = 'FAIL'

  if lastprocess and 5th process fail, should show both

Try:

SELECT t1.*
FROM tblTest t1
WHERE t1.result = 'FAIL'
  AND NOT EXISTS ( SELECT 1
                   FROM tblTest t2
                   WHERE t1.serialnumber = t2.serialnumber
                     AND t1.id < t2.id
                     AND t2.result != 'FAIL' )


How to write a query to get concatenated names of riders with the same weight?

You're looking for GROUP_CONCAT:

SELECT weight, GROUP_CONCAT(name SEPARATOR ' ') as name_list
FROM Rider
GROUP BY weight


CSV files are not included in the dacpac that you deploy so you will need to make sure that you copy it with the dacpac and that if you use relative paths the working directory is what you think it is.

What I do for extra files is set the "Build Action" of the file to "Copy if Newer", when the dacpac is built in the bin folder you will also have a "Data" subfolder - just make sure you copy that with your dacpac always.

ed


Proper technique for storing users event data

You have made a very good observation. The Activity table will grow fast and large. What I have done in the past is archive off the older data (say older than 14 days) to an ActivityHistory table. Doing so keeps the Activity table to a manageable size and if you need to do research you can always look back at the ActivityHistory table.


How to remove diacritics in computed persisted columns? COLLATE is non-deterministic and cannot be used

Why not just create the table column with a case-insensitive, accent-insensitive collation? This prevents duplicates according to the collation rules, and allows the sort of searches you seem to require:

CREATE TABLE Test
(
    col1 varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AI PRIMARY KEY
);

-- Success
INSERT dbo.Test VALUES ('Gagné');

-- Failed, duplicate key
INSERT dbo.Test VALUES ('Gagne');

-- Success
SELECT *
FROM dbo.Test
WHERE col1 = 'gaGNE';

As an important side note, I should mention that T-SQL scalar and multi-statement functions have very poor performance characteristics. Using them in a computed column definition is even worse. Avoid.


Is it possible for me to be notified if an 'after insert or update trigger' stops working?

A trigger cannot simply stop working.

A trigger can be disabled.  A trigger can be dropped.  A trigger will be made invalid if DDL is done to one of the objects it references but it will still be executed if the triggering statement is executed.  If the trigger fails to recompile successfully, the triggering statement will get an error

ORA-04098: trigger ' MASTER.INSTANCE_STEP_TRG' is invalid and failed re-validation

It also seems possible that the trigger was working correctly as declared but not as you intended.  For example, it is possible that the new.sysid was not greater than 0 (remember that NULL is not greater than 0) causing the WHEN clause to not be met.  It is possibly that your SELECT statement returned a value less than or equal to 0, thus causing your INSERT not to be executed.  If either TEMPLATE.wf_step or TEMPLATE.step_stage has a column STEP_ID, the WHERE clause

where ws.sysid= step_id;

will interpret STEP_ID as the column in the table, not your local variable STEP_ID.  One of the reasons that PL/SQL developers generally add prefixes to local variables (i.e. L_STEP_ID rather than STEP_ID) is to avoid inadvertently using a name that is also used by a column in a table because these sorts of scope resolution problems are notoriously difficult to debug.


Capturing sp_executesql statement in sql profiler

You don't have to count both. As you see, you get an rpc completed for sp_executesql. Your Select which shows up as StmtCompleted is a "subtask" of the rpc. The SQL trace already has the sum of all statements inside a rpc summed up for you.


What is a good size to accommodate a single-use database for a small application?

It is generally accepted, by those who study such things in the Big Data realm, that the majority of databases fit within 10 GB. There are many examples of some truly very large databases in the Terabyte to PB range but these are not the norm and I gather not applicable to your use case. Per your description, your use case would appear to easily fall within 10GB or less category. I would further guess you could refine that number for your use case to between 2 to 5GB for "single-use databases for small applications."

If it was my use case, I'd go to the developers and ask to see their local instances of the DB and refine my estimations.


What's the fastest way to replicate a MySQL database from one machine to another?

I would personally do it in one step with a pipe using mysqldump like so

mysqldump -u user_source -p --opt database_name table_name | mysql -u user_target -p --host=target_db_ip -C target_database

but if mysqldump is not allowed just save the dump somewhere you can access it then use wget to move it to the other server and unpack there using mysql.  I normally use this command because it doesn't require me to stop the database and is a single command with no files rather than a process where I have to manage where the data files are.


There is no solution for fan-in with traditional MySQL replication.

Another option is Tungsten Replicator. Here are a couple of posts on how it supports fan-in replication:

http://narmitag.wordpress.com/2012/09/27/setting-up-fan-in-using-tungsten-replicator/
https://code.google.com/p/tungsten-replicator/wiki/TRCMultiMasterInstallation

Another option is Galera replication, which is integrated with Percona XtraDB Cluster and MariaDB Galera Cluster. In Galera, all cluster nodes take replicated changes from all other cluster nodes. Which satisfies your goal of fan-in -- sort of -- but it means that all nodes have all the data.

For what it's worth, MariaDB 10 made their General Availability announcement on March 31, 2014.

I don't recommend the Federated storage engine. It should really be deprecated, because it's slow, buggy, and fragile. My feel is that MySQL is considering scrapping it. See http://www.tocker.ca/2013/10/17/how-do-you-use-the-federated-storage-engine.html


In Oracle, how to globally enable parallel execution?

Simple answer: Yes. Unfortunately it is not quite a simple as that, as if you want to enable parallelism, then you are obviously concerned about performance, and it is extremely tunable.

Start with alter system set paralllel_automatic_tuning=true scope=spfile; then restart. This will have Oracle do what it thinks is best. There are many other parameters to set (e.g. parallel_execution_message_size) and you will need to experiment to find the best. Be careful with this; it is easy when you start out with parallel query to get carried away in benchmarking then discover in the "real world" a few queries fly but the overall throughput of the system actually decreases as some sessions are starved of CPU or the system is forced into swap. The documentation warns:

  When concurrent users have too many
  query server processes, memory
  contention (paging), I/O contention,
  or excessive context switching can
  occur. This contention can reduce
  system throughput to a level lower
  than if parallel execution were not
  used.

So I strongly advise putting some limits in place, e.g. with resource consumer groups.

Also for any table you can do ALTER TABLE table_name PARALLEL (DEGREE x); where x is a number, 4 or 8 might be good places to start.


Select by grouped rows and order by date

select t1.*
from table t1
          join (select t2.session, max(t2.created_at) max_created_at
                from table t2
                group by t2.session
                order by max_created_at desc
                limit 10
               ) t3
          on t1.session = t3.session
order by t3.max_created_at desc, t3.session ;


Is there any reasonable way to determine what executed a query?

Use SQL Auditing to track from where those statements are coming.

Set-AzureRmSqlDatabaseAuditing -ResourceGroupName "resourceGroup"
 -ServerName "SQL Server Name" -DatabaseName "AdventureWorksLT"
 -StorageAccountName "storageAccount"
-AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "BATCH_COMPLETED_GROUP"
 -AuditAction "UPDATE ON database::[AdventureWorksLT] BY [public]"
 -RetentionInDays 60

A sample audit record for the BATCH_COMPLETED_GROUP will show the following information:

As you can see it gives you the IP address, the principal name and the application name that generated the SQL statement.


How to backup databases to ensure data recovery

I am noting the other answers here, but I don't believe any of them (at this moment) is a complete answer / solution to your problem.  Yes, you can backup to a remote location, but that is not always the best way to do it.  Backing up to a network location can be murderous to your bandwidth.

Transaction Log backups is very important and is the right way to go, and they can be off-setted with differentials.  But there is usually some theory and planning that needs to go into your implementation thereof.

Differential and Transaction Log backups should cause a lot less performance issue for you, as the backups are a lot smaller.  Differential Backups only contain changes made since the last full backup, and Transaction Log Backups only contain transactions done since the last full, differential or transaction log backup (normally just a few MBs).

Based on the details you have provided, there are over 1000 transactions done per day.  On a normal 8 hour work day, this calculates to just over 2 transactions a minute.

Before you implement any backup plan, you need to determine (as a business) what your Recovery Time Objective (RTO) and Recovery Point Objective (RPO) is.

RTO relates to the maximum amount of time you are willing to lose during a disaster, to restore normal operations.

RPO relates to the maximum amount of data that you willing to lose during a disaster.

In most cases, the lower the RTO, the higher the RPO and vice versa.

With the metrics above, you should then decide what the best backup plan is to implement.

For e.g.:

PLAN A: (Low RPO, i.e. minimal data loss)

Daily Full Backups
4 Hourly Differential Backups
Transaction Log Backups every 10 minutes

No more than 10 minutes of data will be lost, but the restoration process might be complexed and therefore time-consuming (close to 30 backup files to restore from)

PLAN B: (Low RTO, i.e. minimal downtime)

Daily Full Backups
12 Hourly Differential Backups
4 Hourly Transaction Log Backups

Up to 4 hours of data could be lost, but the restoration process would be faster, with only a max of 4 files to restore

Importantly, you should note that if you back up your databases to the same drive as where your data is, and you have a catastrophic drive loss, you are likely to lose the backups too.  Always have your backups on a separate drive from data and logs.

Suggestions to improve / edit this answer is welcome!


Two foreign keys referring to one primary key

Example SQL code (create with MySQL Workbench):

CREATE TABLE `test`.`user` (
  `iduser` INT NOT NULL,
  `username` VARCHAR(45) NULL,
  PRIMARY KEY (`iduser`));

CREATE TABLE `test`.`transfer` (
  `transactionID` INT NOT NULL,
  `from_user` INT NULL,
  `to_user` INT NULL,
  `transfer_amount` FLOAT NULL,
  PRIMARY KEY (`transactionID`),
  INDEX `from_user_key_idx` (`from_user` ASC),
  INDEX `to_user_key_idx` (`to_user` ASC),
  CONSTRAINT `from_user_key`
    FOREIGN KEY (`from_user`)
    REFERENCES `test`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `to_user_key`
    FOREIGN KEY (`to_user`)
    REFERENCES `test`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

This works. However if you want to query the 'transfer'-table and want to substitute the IDs for usernames it gets a bit more complicated. Heres an example query:

select a.username as from_user, b.username as to_user, transfer_amount
from transfer
inner join user a
on transfer.from_user = a.iduser
inner join user b
on transfer.to_user = b.iduser;


How can I delete a file from the primary filegroup?

The primary database file can never be removed because it contains special page structures that exist only on that file and cannot be relocated.  If you want a single database file, move all data from the secondary file using DBCC SHRINKFILE with the EMPTYFILE option and then remove the secondary file.  Finally, rebuild/reorganize indexes to reduce fragmentation.

An example of unmovable page is the database boot page, which is page 9 of the primary data file of every database.  See Paul Randal's Anatomy of a Page for details.


How to auto replicate specific columns only from a table in MySQL

Why not create one separate db for your SSO, which is accessible for each site?
Copying data around will create problems when two sites are editing "the same field".

If you only want to use a copy to set up, you could:

CREATE TABLE newdb.users LIKE otherdb.users;
INSERT newdb.users SELECT * FROM otherdb.users;

If you really want to continuously synchronise tables between databases, you will have to do it with a custom script.
Most important to consider in that case, will be the question of what to do when conflicts arise?
But I would really consider creating one specific db for SSO.


SQL Server Sch-S locks on unrelated tables

Potential Causes

There are two likely reasons for this

There's a foreign key on the table
There's a trigger on the table

There are less likely reasons, too

Computed column based on a scalar function
Indexed view maintenance

There's also the possibility that the blocking is due to metadata changing, where modifications to system views can block each other.

Figuring It Out

You can use sp_WhoIsActive.

If you run it like so: sp_WhoIsActive @get_locks = 1;, it should help you get to the root of your blocking issue.


VLF are totally managed internally by SQL server, I am talking about there size. If you have large amount of VLF's for the database its not a good sign.I guess you are referring to large amount of VLF as VLF fragmentation.
High amount of VLF can be attributed to fact that autogrowth settings are not proper for database may be very small and database has to allocate new vlf reach times it grows. Ofcourse VLF size would be small and eventually be many. 160 VLF count does not seems issue to me.Below blog will help you in further understanding

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/


Outer join on a view with calculated columns performs calculation for missing ("outer") records

If the two queries:

SELECT *
FROM foo f
  LEFT JOIN baz b
  ON f.x = b.y;

and

SELECT *
FROM foo f
  LEFT JOIN (SELECT * FROM baz) b
  ON f.x = b.y;

return different results, then it's a bug and the table and view definitions are irrelevant. That should never happen.

Before you go and post a bug report, check your view and queries in the version (5.5.54). Your 5.5.40 is 14 minor versions and about 2.5 years older than the latest 5.5 version.

If the bug still persists, then file a bug report to MySQL with this example. A similar bug has already been reported: Return inconsistent result when using inline SELECT vs VIEW for left join, so if you report I suggest you add your examples there.

MySQL developers have comfirmed that one and suggested a few workarounds (until it is fixed). You can try the suggestion to create the views with ALGORITHM = TEMPTABLE:

  There are a few workarounds:

  In all version, the query using the view will give correct result if view is created with "CREATE ALGORITHM=TEMPTABLE VIEW ..."

  For 5.7/8.0, both queries will give the correct result by setting optimizer_switch='derived_merge=off'

  In 8.0 the NO_MERGE hint can be used to get correct behavior in both cases:
  SELECT /*+ NO_MERGE(t2) */ ...

Having said that, the query is more complex than needed. You could have used something simpler (although I don't like having a result with columns identically named):

CREATE VIEW baz2
AS
SELECT
   f2.x AS x
   f1.x AS x,
   CASE WHEN f1.x IS NOT NULL THEN f2.x END AS y,
   SIGN(f1.x) AS z
FROM foo f2                   -- changed:   f2 LEFT JOIN f1
  LEFT JOIN bar f1
  ON f1.x = 2 * f2.x;

and

SELECT * FROM baz2 ;


MSSQL - Query had to wait for memory grant

As sp_BlitzErik suggested, I used sp_Blitzcache to highlight the issues in the offending proc. I was able to address them and the problem went away. I highly recommend having a look at the SQL First Responders Kit. I wish I could give the credit for this answer to to Erik.


There is no such notion as CPU "traffic". A single thread runs on a single Core. Always. While a single core can execute multiple threads and even multiple processes, a single thread can not be split to be executed among multiple cores. That would require the system to understand the purpose of the program, which would be a pretty frightening thing.

So let us assume you have 4 threads and 4 Cores. Now let us assume 3 of those threads do not have much to do (one is logging, one is listening for configuration changes and one accepts new connections and spawn additional threads dealing with those connections), but the other one really has to do some computation. The core the computational intensive thread is attached to will have a higher utilization. And this behavior is even intended – or would you want the system to slow down in accepting new connections just because somebody makes a computational intensive request?


The thread-safety parameter is only relevant for the client library libpq it doesn't influence how the backend is build. The backend actually doesn't even use threads, it uses a process per connection.

edit
just noticed I didn't address the renaming of the configure parameter. The point with configure is that the build scripts can support optional features. If you have a feature X you can pass either --enable-X or --disable-X. However it's help output will only list one of the two based on the current default (enabled is the default since 9.0 for thread-safety).


Create Slowly Changing Dimension table from Repeated Data

The problem here is the way you're loading data. With a Type 2 SCD (Effective Date) you want to add a new row only when there is a change to the data. The first four rows in your dataset do not change except for the load date.

You need to ETL your data from the source files into your database where you can more easily identify if records have been changed and only add new rows for the changed records. Begin Date would be the date that row is first loaded and End Date would be when one of the values changes marking the original row as no longer current. For the current row, End Date is NULL.

The query then becomes much simpler:

select
    product.Productid
    ,product.ProductTitle
    ,product.ProductCategory
    ,BeginDate
    ,CASE EndDate
        WHEN NULL THEN '12/31/9999'
        ELSE EndDate
     END AS EndDate
from dbo.Product product
group by Productid, ProductTitle, ProductCategory

The end result is your table would only have 3 rows rather than 10. If you're importing directly using TSQL scripts, look at using MERGE to UPDATE\INSERT as appropriate. If you're using an ETL tool like SSIS, they have native functions for handling SCDs. There are lots of ways to solve this, but the key is importing your data in a better way.


How to return ids with specific values in another column

SELECT user_id
FROM
user_role
WHERE role IN('ADD','DELETE','VIEW')
GROUP BY user_id
HAVING count(*)=3

Here is the link to SqlFiddle


'Refreshing' SSRS subscriptions (parameter values not valid due to lack of data in the tables)

What you do is to create your subscription to run only once. Then you go to the agent job it created and copy the step's code. This you can then run in a stored procedure after checking if there are data:

if (select * from table where date = '2018/05/18')
exec [ReportServer].dbo.AddEvent
    @EventType='TimedSubscription',
    @EventData='xxx data copied from job xxx'

The AddEvent will then execute the report to execute.


Select for indexed field with >= is much faster than with =

With >=, the optimizer had two indexes to choose from.  Apparently it chose (exp_time).  Please provide EXPLAIN SELECT ... to confirm this. Also run EXPLAIN FORMAT=JSON SELECT ...

With =, the optimizer still has two to choose from, but probably picked = (asset_id). But there were a lot of 71's, so it turned out to be a bad choice.

That choice can be improved by having INDEX(asset_id, exp_time) in place of simply (asset_id).  This may run faster than even 0.5s.  This 'composite' index can use both parts of the WHERE clause, thereby being more efficient.  (The >= version can't use both columns of a composite index because it can't handle 2 ranges at the same time.)

And even better than  (asset_id, exp_time): (is_demo, asset_id, exp_time) or (asset_id, is_demo, exp_time).

It could be that the "statistics" are stale -- run ANALYZE TABLE Deals to see if things improve.  But don't hold your breath; this is not a panacea.


Can I use an RMAN Backup of DB1 to replace DB2

Sure you can, you can duplicate a database using RMAN backups. For example:

shut down DB2
delete all datafiles, redo logs, controlfiles of DB2
start DB2 in NOMOUNT
with RMAN, connect to DB1 as target, DB2 as auxiliary:

rman target sys/password@DB1 auxiliary /

duplicate the database until the required time, and rename files on the fly (if required), for example::

duplicate database to db2
until time "to_date('2016-02-20 16:00:00', 'YYYY-MM-DD HH24:MI:SS')"
db_file_name_convert (/oradata/DB1, /oradata/DB2)
logfile
'/oradata/DB2/redo01.log' SIZE 50M,
'/oradata/DB2/redo02.log' SIZE 50M,
'/oradata/DB2/redo03.log' SIZE 50M;


Align auto increment index to next thousand

If using mariadb-10.3 you can use sequences with a sequence defined to a 1000 increments that can be used for columns (ref).

MySQL and MariaDB have an auto_increment_increment global system variable that globally controls the amount that an AUTO_INCREMENT column will increment. It is global in the sense it will affect all tables. You'll need to alter the table in question to have the next AUTO_INCREMENT value at a 1000 offset. Its designed around a multi-master replication purpose so you can't use it with bi-directional (or galera cluster) replication configurations.

In general an AI value is meant to be an internal detail rather than something for humans to consider so another differentator might be better. Its hard to read your business case.


I'd recommend using the BCP utility.  From that documentation:

Copying table rows into a data file (with a trusted connection)

Basic

This example creates a data file named StockItemTransactions_character.bcp and copies the table data into it using character format.

At a command prompt, enter the following command:

bcp WideWorldImporters.Warehouse.StockItemTransactions out D:\BCP\StockItemTransactions_character.bcp -c -T


Take it all the way up. FWIW I have systems where 500m and even 1g ORLs are normal. Don't be afraid of this on modern hardware - set archive_lag_target to give you a reasonable log switch (I go for 230 seconds to keep it off 5-minute boundaries).


Best practices to maximize portability in SQL Server 2016

User Aaron Bertrand made some comments that align well with my thoughts on your question.  This is more of a frame challenge than an answer to your specific question, but I think it's valuable to consider in this context.

  Portability is a nice textbook goal, but it rarely happens in practice.

If you have to change platforms at some point, there will be changes needed to the application, the database, and probably many other things.  If you can be somewhat "platform agnostic" without too much effort, that's fine.  But it's really a bad business decision to use that as a design goal.

There are many places online where people discuss the downsides or programming this way, here's one of them that I find pretty compelling:

Database Abstraction Layers Must Die!

  The Portability Fallacy

  The author uses an argument I hear all the time: If you use a good abstraction layer, it'll be easy to move from $this_database to $other_database down the road.

  That's bullshit. It's never easy.

  In any non-trivial database backed application, nobody thinks of switching databases as an easy matter. Thinking that "the conversion will be painless" is a fantasy.

  Good engineers try to select the best tools for the job and then do everything they can to take advantage of their tool's unique and most powerful features. In the database world, that means specific hints, indexing, data types, and even table structure decisions. If you truly limit yourself to the subset of features that is common across all major RDBMSes, you're doing yourself and your clients a huge disservice.

  That's no different from saying "I'm doing to limit myself to the subset of PHP that's the same in Perl and C, because I might want to switch languages one day and 'painlessly' port my code."

  That just doesn't happen.

  The cost of switching databases after an application is developed and deployed is quite high. You have possible schema and index changes, syntax changes, optimization and tuning work to re-do, hints to adjust or remove, and so on. Changing mysql_foo() to oracle_foo() is really the least of your problems. You're gonna touch most, if not all, of your SQL--or you'll at least need to verify it.

  That doesn't sound "painless" to me.


Postgres - Vacuum doesn't complete on large/busy table

You don't show your settings for "autovacuum_work_mem" and "maintenance_work_mem".  The default settings in 9.4 are very low (64MB, which only allows 11M tuples to be vacuumed per pass over the index), unless RDS (or you) changed them.  You need to set these to the highest value you can given the amount of RAM you have.

"index": "446 GB",
"toast": "29 MB",
"table": "37 GB"

Having the index be 12 times bigger than the table seems demented (Edit: or it would be if there was only index--I didn't think about having many indexes).  Is this a normal btree index, or is it pg_trgm or something?  Is the index corrupted somehow?  Do you know how you got into this situation?

Space in indexes is harder to reuse than space in tables. A given leaf page can only be used for new tuples if the new tuples are near the same range of values as the tuples it already holds, or if the page is completely empty.  So if the key space is alway moving in one direction (like a sequence), and almost-but-not-quite-all of the old tuples eventually get deleted, then you could be left with a bunch of pages which only hold one tuple each, and can't be reused.  Tables don't have this problem as mostly-empty pages can be used to hold any tuple that comes along.  Or, if your table was horribly bloated at some point but then got cleaned up, it is possible the table got shrunk down but the indexes did not.  (Tables can shrink if they happen to have a big chunk of completely empty pages at the end of the table).  It can be hard to tell which of these things is the one that happened.  You can use pg_freespacemap on the indexes to see how many pages are completely empty, but that probably won't be accurate until the vacuum finishes.

The fastest way out of this, although with some down time, might be to start a VACUUM FULL of the table--you don't want to spend a lot of time vacuuming a hopelessly bloated index when you could just throw it away and rebuild it in a non-bloated state. The VACUUM FULL will block, then in another session, kill the autovacuum worker (which holds a lock blocking the VACUUM FULL). You want to have the other command already waiting when you kill the autovac worker, because it will be restarted quickly and so take the lock again, unless something else is already there waiting to grab it.

  I've tried to change a few of those for the table, but it just sits there when I try to write any values for that particular table.

Changing those table-specific settings requires a lock on the table.  The autovac worker is blocking that lock.  Normally an autovac will detect when it is blocking something else and surrender the lock, but (to prevent wraparound) does not do that.  So you would need to kill the autovac worker to be able to make this change (which is a general theme of everything here).

I wouldn't recommend changing that table-specific setting anyway. If you want this to be a one-time thing, just run a manual VACUUM or VACUUM FULL to accomplish that.  If you want it to be permanent, it is hard to justify why this one table needs a different "autovacuum_vacuum_cost_delay" than your other tables, at least based on the info given, so just change it at the system level.  If you change it at the system level, it still won't take effect in the middle of the ongoing autovac, you would need to kill it so the next one picks up the change.

  Also, would effect would a reboot of the database have on all this?

A reboot would cause the autovac to lose part of the work it has already done, and start over again.  It wouldn't accomplish anything, unless you also made meaningful configuration changes which will take effect after the reboot.


How do I rewrite the below Oracle trigger so it will work on SQL Server?

The bad news is that SQL Server doesn't support FOR EACH ROW triggers, so you'd have to take extra care to convert the trigger code.

The good news is that this appears to just be doing what can be achieved with an IDENTITY column in SQL Server, and you won't need the trigger at all.

In your CREATE TABLE COURSE_DELEGATES... statement, declare the COURSE_DELE_ID column something like this (use a more appropriate data type if it isn't int):

CREATE TABLE COURSE_DELEGATES (
    COURSE_DELE_ID int NOT NULL IDENTITY(1,1),
    ....
)

Then any rows inserted in COURSE_DELEGATES will get an auto-generated value for COURSE_DELE_ID, starting with 1, and incrementing by 1 for each new row.


Display list of SSRS reports in a dropdown list, and allow users to select which report to display

Reports should be stored in the Catalog table in the SSRS database, you should be able to create a report based off the contents of the Catalog table...


With partitioned table, How can I use hints to group each partition separately

The database does this by default, no hints/tuning needed.

create table data(partitioned_key_index number, some_dummy_measure number)
  partition by list (partitioned_key_index)
(
     partition p1 values (1),
     partition p2 values (2),
     partition p3 values (3),
     partition p4 values (4),
     partition p5 values (5)
);

insert into data with g as (select * from dual connect by level <= 1000)
select mod(rownum, 5) + 1, rownum from g,g where rownum <= 500000;
commit;

Then run the query:

alter session set statistics_level=all;

select partitioned_key_index, sum(some_dummy_measure) from data
  group by partitioned_key_index;

PARTITIONED_KEY_INDEX SUM(SOME_DUMMY_MEASURE)
--------------------- -----------------------
                    1             25000250000
                    2             24999850000
                    3             24999950000
                    4             25000050000
                    5             25000150000

Check what happened:

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID  537qapda9hdy4, child number 0
-------------------------------------
select partitioned_key_index, sum(some_dummy_measure) from data group
by partitioned_key_index

Plan hash value: 3405952922

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      5 |00:00:00.14 |    1065 |       |       |          |
|   1 |  PARTITION LIST ALL |      |      1 |    585K|      5 |00:00:00.14 |    1065 |       |       |          |
|   2 |   HASH GROUP BY     |      |      5 |    585K|      5 |00:00:00.14 |    1065 |    34M|  6473K|  738K (0)|
|   3 |    TABLE ACCESS FULL| DATA |      5 |    585K|    500K|00:00:00.06 |    1065 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Operations below PARTITION LIST ALL were done for all partitions. We have 5 partitions, including the GROUP BY. As you can see from the Starts column, HASH GROUP BY was really performed 5 times.

This is how it looks like, when GROUP BY is performed for the whole table at once:

select (partitioned_key_index + 0), sum(some_dummy_measure) from data
    group by (partitioned_key_index + 0);

(PARTITIONED_KEY_INDEX+0) SUM(SOME_DUMMY_MEASURE)
------------------------- -----------------------
                        1             25000250000
                        2             24999850000
                        5             25000150000
                        4             25000050000
                        3             24999950000

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2xxf42mtp53sc, child number 0
-------------------------------------
select (partitioned_key_index + 0), sum(some_dummy_measure) from data
group by (partitioned_key_index + 0)

Plan hash value: 3651737839

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      5 |00:00:00.24 |    1065 |       |       |          |
|   1 |  HASH GROUP BY      |      |      1 |    585K|      5 |00:00:00.24 |    1065 |    34M|  6473K| 4574K (0)|
|   2 |   PARTITION LIST ALL|      |      1 |    585K|    500K|00:00:00.14 |    1065 |       |       |          |
|   3 |    TABLE ACCESS FULL| DATA |      5 |    585K|    500K|00:00:00.06 |    1065 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected.

HASH GROUP BY was performend only once, for the whole amount of data, after it was collected from all partitions.


Searching for pairs of date/time values in a single row

I'm not sure of db2 syntax, but how about this?

-- declare your 4 variables
@START_ROSDAT
@STOP_ROSDAT
@START_ROSTIM
@STOP_ROSTIM

select  *
from    PHONELOGTABLE
where   (   @start_rosdat <> @stop_rosdat                                    -- search covers more than 1 day
           and  (       (ROSDAT > @START_ROSDAT AND ROSDAT < @STOP_ROSDAT)   -- catches all full days between start and stop
                   or   (ROSDAT = @START_ROSDAT AND ROSTIM >= @START_ROSTIM) -- catches everything on the "start" day
                   or   (ROSDAT = @STOP_ROSDAT AND ROSTIM <= @STOP_ROSTIM)   -- catches everything on the "stop" day
                )
        )
  or    (   @start_rosdat = @stop_rosdat                                     -- only search a single "day"
           and  (       (ROSDAT = @START_ROSDAT AND ROSTIM >= @START_ROSTIM)
                   and  (ROSDAT = @STOP_ROSDAT AND ROSTIM <= @STOP_ROSTIM)
                )
        )

WHERE condition simplified a bit (the parentheses are there only for clarity, you can safely remove them):

where   ( ROSDAT > @start_rosdat   and  ROSDAT < @stop_rosdat )

   or   (  @start_rosdat < @stop_rosdat
      and  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim )

   or   (  @start_rosdat < @stop_rosdat
      and  ROSDAT = @stop_rosdat   and  ROSTIM <= @stop_rostim  )

   or   (  @start_rosdat = @stop_rosdat
      and  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim
                                   and  ROSTIM <= @stop_rostim  )

and another version (parentheses are needed here):

where   (  ROSDAT > @start_rosdat
       or  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim )

   and  (  ROSDAT < @stop_rosdat
       or  ROSDAT = @stop_rosdat   and  ROSTIM <= @stop_rostim  )


mysql prevent update other salve-master db while any record update in slave db

If we assume you have the following databases:

Master  (DB0);
Slave 1 (DB1);
Slave 2 (DB2);
Slave 3 (DB3);

The only way for a 'slave' (e.g DB1) to update the 'master' (DB0) is if the 'slave1' (DB1) is also acting as a master to the 'master' (DB0). (commonly known as Master to Master Replication)

You can check if this is the case by connecting to your 'master' (DB0) database and running SHOW SLAVE STATUS;

The result 'should' be blank, most likely it isn't.

Look for the fields:
Master_Host and Master_Port - this is the database from which it is receiving data. It will probably relate to one of your 'slaves' (DB1).

Slave_IO_running and Slave_sQL_running - if these are both 'Yes' then replication is up and running, and any changes made to the database above will appear on your 'master'

The quickest way to stop it is to then issue STOP SLAVE; on your 'master' (DB0).

Depending on what version of MySQL you are running there are then some things you can do to erase the settings. https://dev.mysql.com/doc/refman/5.6/en/reset-slave.html (I recomend making a note of the settings in SHOW SLAVE STATUS before issuing any kind of RESET, just in case)

Also you may want to add:
skip-slave-start to your my.cnf file on your master, which will prevent the database starting replication again if the server is restarted.

As @ypercube mentions above, you should avoid writing to a 'slave' database, unless you have specifically configured it as part of a master to master setup.


Command line switch to disable Coherence?

It's funny that you should ask about this. There are other requests for the same (e.g. some sort of command line flag to "disable" Coherence), and I believe that it is planned for an upcoming release (either the currently-rolling-out 12.1.3 or the following release).

Is there anything in specific that you see Coherence "doing" that you're trying to avoid?

(Disclaimer: I work at Oracle, including on Oracle Coherence.)


What is the optimal data type for an MD5 field?

The data type uuid is perfectly suited for the task. It only occupies 16 bytes as opposed to 37 bytes in RAM for the varchar or text representation. (Or 33 bytes on disk, but the odd number would require padding in many cases to make it 40 bytes effectively.) And the uuid type has some more advantages.
Example:
SELECT md5('Store hash for long string, maybe for index?')::uuid AS md5_hash;

See:

Convert hex in text representation to decimal number
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

You might consider other (cheaper) hashing functions if you don't need the cryptographic component of md5, but I would go with md5 for your use case (mostly read-only).
A word of warning: For your case (immutable once written) a functionally dependent (pseudo-natural) PK is fine. But the same would be a pain where updates on text are possible. Think of correcting a typo: the PK and all depending indexes, FK columns in "dozens of other tables" and other references would have to change as well. Table and index bloat, locking issues, slow updates, lost references, ...
If text can change in normal operation, a surrogate PK would be a better choice. I suggest a bigserial column (range -9223372036854775808 to +9223372036854775807 - that's nine quintillion two hundred twenty-three quadrillion three hundred seventy-two trillion thirty-six something billion) distinct values for billions of rows. Might be a good idea in any case: 8 instead of 16 bytes for dozens of FK columns and indexes!). Or a random UUID for much bigger cardinalities or distributed systems. You can always store said md5 (as uuid) additionally to find rows in the main table from the original text quickly. Related:

Default value for UUID column in Postgres

As for your query:

Optimizing a Postgres query with a large IN

To address @Daniel's comment: If you prefer a representation without hyphens, remove the hyphens for display:
SELECT replace('90b7525e-84f6-4850-c2ef-b407fae3f271', '-', '')

But I wouldn't bother. The default representation is just fine. And the problem's really not the representation here.
If other parties should have a different approach and throw strings without hyphens into the mix, that's no problem, either. Postgres accepts several reasonable text representations as input for a uuid. The manual:

PostgreSQL also accepts the following alternative forms for input: use
of upper-case digits, the standard format surrounded by braces,
omitting some or all hyphens, adding a hyphen after any group of four
digits. Examples are:
A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

What's more, the md5() function returns text, you would use decode() to convert to bytea and the default representation of that is:
SELECT decode(md5('Store hash for long string, maybe for index?'), 'hex')

\220\267R^\204\366HP\302\357\264\007\372\343\362q

You would have to encode() again to get the original text representation:
SELECT encode(my_md5_as_bytea, 'hex');

To top it off, values stored as bytea would occupy 20 bytes in RAM (and 17 bytes on disk, 24 with padding) due to the internal varlena overhead, which is particularly unfavorable for size and performance of simple indexes.
Everything works in favor of a uuid here.


How to add an article to existing replication

Adding an a new article involves adding the article to the publication, creating a new snapshot, and synchronizing subscriptions to apply the schema and data for the newly added articles.  Reinitialization is not required, but a new snapshot is.

Your steps are correct if the publication only has existing push subscriptions.  If the publication has existing pull subscriptions, you will also need to execute sp_refreshsubscriptions.  And you are correct, to generate a new snapshot you can use the View Snapshot Agent dialog, or you can execute sp_startpublication_snapshot.

To avoid generating a full snapshot when adding a new article, publication properties @immediate_sync and @allow_anonymous must be set to 0. Execute sp_addarticle, then execute sp_addsubscription. If the publication has existing pull subscriptions, you must also call sp_refreshsubscriptions. Then generate a snapshot and only a snapshot for the newly added article will be generated.  Note that this approach is not necessary but is typically used when Administrators want to avoid generating a full snapshot when adding a new article.

Please see Add Articles to and Drop Articles from Existing Publications for more information.


use alter user to lock all user accounts except scott

You can't lock out a login except by attempting logins with incorrect passwords enough times to cause it to be locked out.  The ALTER LOGIN documentation shows the UNLOCK option, but there is no LOCK option.

Additionally, you can't force a password to be expired because the expiration is defined by the domain or local system policy.  You would have to change the policy to a date that would force the desired logins to have expired passwords, but there's no way you could ensure that would apply to only certain logins (unless you disabled password expiration on the others).


MySQL: check max chracter length (ERROR 1118 (42000): Row size too large)

According to this, your problem is not with VARCHAR fields. However:

SELECT id, LENGTH(field1)+LENGTH(field2)...+LENGTH(field_n) AS total_length WHERE total_length > 8126
Can be done using 2 queries for each field. (This can be done in a complex query if you want to use INFORMATION_SCHEMA):

SELECT MAX(LENGTH(field1)) INTO @max_length1 FROM table_name;
SELECT * FROM table_name WHERE LENGTH(field1)=@max_length1;

Note: Increasing innodb_page_size's value may be helpful in your case.

Edit

To generate all the queries with the help of information_schema, run the following queries:

CREATE TABLE queries(field_name varchar(255), query1 varchar(4000), query2 varchar(4000));

INSERT INTO queries SELECT column_name,
CONCAT('SELECT MAX(LENGTH(',column_name,')) INTO @max_length FROM table_name;'),
CONCAT('SELECT * FROM table_name WHERE LENGTH(',column_name,')=@max_length;')
FROM information_schema.columns WHERE table_schema='test' and table_name='myisam_table';

SELECT * FROM queries;

Note that you have to run query1 then query2 from the result. DO NOT run all query1 then all query2!


Access table field on creating a trigger postgresql

Per documentation:

  The trigger function must be defined before the trigger itself can be
  created. The trigger function must be declared as a function taking no
  arguments and returning type trigger. (The trigger function receives
  its input through a specially-passed TriggerData structure, not in the
  form of ordinary function arguments.)

Bold emphasis mine.

To pass those parameters:

arguments

  An optional comma-separated list of arguments to be provided to the
  function when the trigger is executed. The arguments are literal
  string constants. Simple names and numeric constants can be written
  here, too, but they will all be converted to strings. Please check the
  description of the implementation language of the trigger function to
  find out how these arguments can be accessed within the function; it
  might be different from normal function arguments.

And finally, in the chapter Trigger Procedures:

  Note that the function must be declared with no arguments even if it
  expects to receive arguments specified in CREATE TRIGGER — trigger
  arguments are passed via TG_ARGV, as described below.

  ...

TG_ARGV[]

  Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.

The final difficulty is that SQL only works with static identifiers. If you want to parametrize a column name, you need dynamic SQL. In a plpgsql function, use EXECUTE for that.

Example

CREATE OR REPLACE FUNCTION trg_foo()
  RETURNS trigger AS
$func$
DECLARE
   _result text;
BEGIN
   CASE TG_OP
   WHEN 'INSERT', 'UPDATE' THEN
      EXECUTE format('SELECT %I FROM %s WHERE tbl_id = $1', TG_ARGV[1]
                                                          , TG_ARGV[0]::regclass)
      INTO  _result
      USING NEW.sometbl_id;
   WHEN ...

   END CASE;

   -- do something with _result ...

   RETURN NULL;   -- since it's an AFTER trigger
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER foo_trigger
AFTER INSERT OR UPDATE OR DELETE ON foo
FOR EACH ROW
EXECUTE PROCEDURE trg_foo('foo', 'field'); -- two params: table, field

Answer on SO with details about the cast to regclass:
Table name as a PostgreSQL function parameter


This can be done with PIVOT and it can be done dynamically, but before you jump in trying to do this dynamically, you should try to get the result you want using a static or hard-coded version of the query, then convert it to dynamic sql.

Since you are using SQL Server 2008 and you want a total column for both Usage and Cost, I would first start by looking at sum(<your column) over(...). This will allow you to aggregate your data in a single step before pivoting it.

To get the static version, I'd first start out with query similar to this:

select
    b.BillingId,
    bd.Feature,
    bd.Usage,
    TotalUsage = sum(bd.Usage) over(partition by bd.BillingId),
    TotalCost = sum(bd.Cost) over(partition by bd.BillingId)
from Billing b
inner join BillingData bd
    on b.BillingId = bd.BillingId
where b.BillYear = 2018 and b.BillMonth = 4

See SQL Fiddle. This query gets you the basic data that you want to pivot:

| BillingId |       Feature | Usage | TotalUsage | TotalCost |
|-----------|---------------|-------|------------|-----------|
|         1 |         Walks |    25 |        583 |  14037.95 |
|         1 |         Baths |     5 |        583 |  14037.95 |
|         1 |         Walks |   500 |        583 |  14037.95 |
|         1 |         Baths |    53 |        583 |  14037.95 |
|         2 |         Baths |    53 |        344 |   1591.21 |
|         2 | Litter change |    53 |        344 |   1591.21 |
|         2 |         Groom |    25 |        344 |   1591.21 |
|         2 |    Scratching |   213 |        344 |   1591.21 |

Including your BillingId, each of the Features you eventually want in a new column, then the Usage, TotalUsage, and TotalCost for each BillingId. The sum(<yourcolumn> over(partition by bd.BillingId) gives you the value for each account without having to use GROUP BY.  Once you've got this data, you can apply the PIVOT function:

select
    BillingId,
    Walks = IsNull(Walks, 0),
    Baths = IsNull(Baths, 0),
    [Litter Change] = IsNull([Litter Change], 0),
    Groom = IsNull(Groom, 0),
    Scratching = IsNull(Scratching, 0),
    Usage = TotalUsage,
    Cost = TotalCost
from
(
    select
        b.BillingId,
        bd.Feature,
        bd.Usage,
        TotalUsage = sum(bd.Usage) over(partition by bd.BillingId),
        TotalCost = sum(bd.Cost) over(partition by bd.BillingId)
    from Billing b
    inner join BillingData bd
        on b.BillingId = bd.BillingId
    where b.BillYear = 2018 and b.BillMonth = 4
) x
pivot
(
    sum(Usage)
    for Feature in ([Walks], [Baths], [Litter Change], [Groom], [Scratching])
) piv;

See SQL Fiddle for Demo. This gives a result:

| BillingId | Walks | Baths | Litter Change | Groom | Scratching | Usage |     Cost |
|-----------|-------|-------|---------------|-------|------------|-------|----------|
|         1 |   525 |    58 |             0 |     0 |          0 |   583 | 14037.95 |
|         2 |     0 |    53 |            53 |    25 |        213 |   344 |  1591.21 |

Now that you've gotten the final result you are looking for, you can  start converting the query to dynamic SQL. In order you do this, you will need to get a list of the values you want to be column aka the Feature values. This is done by querying your tables with the BillYear and BillMonth you want, and concatenating the values into a string, then taking that list of columns and executing a full sql string. The full code could be similar to:

DECLARE
    @BillYear int = 2018,
    @BillMonth int = 4,
    @colsNull AS NVARCHAR(MAX),
    @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ', ' + QUOTENAME(bd.Feature)
                    from Billing b
                    inner join BillingData bd
                        on b.BillingId = bd.BillingId
                    where b.BillYear = @BillYear
                        and b.BillMonth = @BillMonth
                    group by bd.Feature
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

select @colsNull = STUFF((SELECT ', IsNull(' + QUOTENAME(bd.Feature)+',0) as '+ QUOTENAME(bd.Feature)
                    from Billing b
                    inner join BillingData bd
                        on b.BillingId = bd.BillingId
                    where b.BillYear = @BillYear
                        and b.BillMonth = @BillMonth
                    group by bd.Feature
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'');

set @query = N'SELECT BillingId, ' + @colsNUll + N', TotalUsage, TotalCost
            from
             (
                select
                    b.BillingId,
                    bd.Feature,
                    bd.Usage,
                    TotalUsage = sum(bd.Usage) over(partition by bd.BillingId),
                    TotalCost = sum(bd.Cost) over(partition by bd.BillingId)
                from Billing b
                inner join BillingData bd
                    on b.BillingId = bd.BillingId
                where b.BillYear = '+cast(@BillYear as nvarchar(4))+N'
                  and b.BillMonth = '+cast(@BillMonth as nvarchar(2))+N'
            ) x
            pivot
            (
                sum(Usage)
                for Feature in (' + @cols + N')
            ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo. You'll notice that there are two variables for the columns - one @cols this is used inside of the PIVOT function and then @colsNull this is similar to the first, but it replaces the nulls in the final select list with zero - you can exclude using this if you don't need it. If you execute this for BillingMonth = 4 you'll get the same result as the static version:

| BillingId | Baths | Groom | Litter change | Scratching | Walks | TotalUsage | TotalCost |
|-----------|-------|-------|---------------|------------|-------|------------|-----------|
|         1 |    58 |     0 |             0 |          0 |   525 |        583 |  14037.95 |
|         2 |    53 |    25 |            53 |        213 |     0 |        344 |   1591.21 |

Then if you change the BillingMonth = 5 you get the results without having to change the query (Demo):

| BillingId | Exercise | Groom | Pilling | TotalUsage | TotalCost |
|-----------|----------|-------|---------|------------|-----------|
|         3 |        0 |     0 |      11 |         11 |        10 |
|         4 |        1 |     5 |       0 |          6 |     75.12 |


Should I create a child table without primary key?

If the records needs to be unique, then it is up to you to either pick a composite key (multiple columns) to make them unique, or use a primary key (unique by definition). I don't think there is anything more efficient. If the concern is running out of bigint values, you won't. Knowing now that you will never query or join on this table seems shortsighted.

EDIT After More Info: If some combination of the receipt_id, prod_id and coupon_id are unique then you can just use that to establish uniqueness with a compound/composite primary key and dump the bigint id.


How to perform server-side transaction timeout if COMMIT TRANSACTION will never arrive?

The underlying OS is capable of detecting all the above mentioned possible disconnects and break the communication channel (TCP, net pipe etc). This will result in the rollback. Don't try to outsmart this.

Of course, one could ask why not

use a reliable queueing communication channel (eg. MSMQ)
submit the entire order in one call rather than 100 calls
commit the order line by line in 'pending' state

Any of the above would work. 30 second transactions would not work.


Does MySQL support replicating all databases?

To answer your question about how MySQL replicates all databases, here is how it works...

Master has Binary Logging enabled
Master records completed SQL Statements
Master will inject 'USE dbname;' among the SQL Statements it records
Slave connects to Master (IO Thread)
Slave IO Thread requests next SQL statement from the Binary Logs
Slave IO Thread copies it to its Relay Logs
Slave SQL Thread Processes its Relay Log Entries FIFO (Queue)

Once USE dbname; is issued out of the relay logs,  multiple SQL statements will be processes chronologically until another USE dbname; (a different database) is issued from the Relay Logs.

To see this, pick any Binary Log (except the Current One) on the Master, and run this:

mysqlbinlog binarylogname > SQLStatements.sql

To see all the binary logs and the current binary log on the Master, run these:

SHOW BINARY LOGS;
SHOW MASTER STATUS;

Long story short: By default, all DBs are replicated due to the USE dbname; command within the binary logs on the master and the relay logs on the slave.

Abdul's answer shows how to filter in or filter out databases from being processed by the slave.


MySQL schedular every day in a time periode every hour

-- main action to perform at 6:15 - 7:15 - ... 23:15
CREATE PROCEDURE main_action()
BEGIN
    -- ...
END;

-- event procedure
CREATE PROCEDURE evt_action()
BEGIN
IF HOUR(CURRENT_TIMESTAMP) > 5 THEN
    CALL main_action();
END IF;
END;

-- event
CREATE EVENT evt_name
ON SCHEDULE EVERY 1 HOUR STARTS '2019-04-15 06:15:00'
DO CALL evt_action();

-- start scheduler
SET GLOBAL event_scheduler = ON;


Different results from sys.dm_db_file_space_usage with database prefix

It looks like that particular DMO was changed to work with all databases. This can be demonstrated by running the following scripts on a 2008r2 server.

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM model.sys.dm_db_file_space_usage;

-- The "other" version
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

As you can see the output will be the same. However, if you run the same scripts on a 2012 server the output will be different.

Technet explains that in the 2008R2 the DMO only worked on tempdb http://technet.microsoft.com/en-us/library/ms174412(v=sql.105).aspx and in 2012 that restriction was removed http://technet.microsoft.com/en-us/library/ms174412(v=sql.110).aspx.

Hope That Helps.


Running MySQL in virtualbox with data dir on shared folder sometimes results in data corruption

When the production box crashes, and you find that the MyISAM tables are corrupted there, and you find how how many hours (or days) it takes to REPAIR them, you may rethink whether moving to InnoDB is justified.

At some point your 'suspend' + corruption may make the MyISAM tables so messed up that dev is impacted.

OK, lecture over; back to your questions.

How big is the VM?  If you are 50/50 InnoDB/MyISAM on that box, then use these settings if the VM is at least 4GB:

key_buffer_size = 10% of the VM size
innodb_buffer_pool_size = 35%

If less than 4GB, lower those percentages some.  The goal is to avoid ever swapping.


Why T-log is not truncated after T-log backup?

The 8th row in your first screenshot shows what appears to be an active VLF in the file. If there is a single active log record in a VLF then the overall VLF will be treated as active log and that VLF cannot be truncated.

If the server is SQL 2016 SP2 or later, you can use the below DMF to get information on the VLFs and check the active state and size. You will likely find that the active VLF is around 350 MB, and if even one byte is active, the entire VLF is considered active and backing up the log won't mark it for truncation.

SELECT s.[name], l.vlf_size_mb, l.vlf_active
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l

Further reading:

What are virtual log files in a SQL Server transaction log?

SQL Server Transaction Log Backup, Truncate and Shrink Operations


Which graph database for search engine and gis?

Well you could use IBM DB2 LUW (Linux,Unix, Windows) Express-C edition. It is free and has community support. It is the same engine/binaries as DB2 Enterprise Edition, it just has certain features "turned off" and has memory and CPU caps, but for what you are describing, it may suit your needs. If you do find you need more memory/CPU you could always purchase a license from IBM to upgrade it to Express or one of the higher editions.

There is an add on called DB2 Spatial Extender, which has functionality for geo-spatial analysis. According to this chart here, it should be free to download and use, even with Express-C edition. You can download DB2 Spatial Extender here. And here is the Information Center documentation on DB2 Spatial Extender. Use the table of contents to browse.


You can cast it to varchar(2000) or varchar(max) depending on your needs
so both should have the same data type and size
for value 1, it using an integer/int data type
From BOL related to CTE ,

The data type of a column in the recursive member must be the same as    the data type of the corresponding column in the anchor member.

WITH results(n, string ) AS (
            SELECT 1,CAST('lol' as varchar(2000))
            UNION ALL
            SELECT n+1, CAST(string + ' lol' as varchar(2000))
            FROM results
            WHERE n<6
            )
            SELECT * FROM results

you can see the data type by using SQL_VARIANT_PROPERTY
WITH results(n, string, expressionType1, expressionType2) AS (
            SELECT 1,cast('lol' as varchar(2000)),
                    SQL_VARIANT_PROPERTY(1, 'BaseType'),
                    SQL_VARIANT_PROPERTY(cast('lol' as varchar(2000)), 'MaxLength')
            UNION ALL
            SELECT n+1, cast(string + ' lol' as varchar(2000)) ,
                SQL_VARIANT_PROPERTY(n+1, 'BaseType'),
                SQL_VARIANT_PROPERTY(cast(string + ' lol' as varchar(2000)), 'MaxLength')
            FROM results
            WHERE n<6
            )
            SELECT * FROM results

output:
n           string               expressionType1 expressionType2
1           lol                  int             2000
2           lol lol              int             2000
3           lol lol lol          int             2000
4           lol lol lol lol      int             2000
5           lol lol lol lol lol  int             2000
6           lol lol lol lol lol  int             2000

Data type for value/expression 1 can be checked by:
select SQL_VARIANT_PROPERTY(1,'basetype')
int


Derby/SQL - How to increase performance when left joining and retrieving varchar columns

The lack of a WHERE clause or an ORDER BY clause probably means that your query is not using an index (and is most certainly selecting all rows in the table).

So, I'd suggest:

Add a "WHERE" clause
Add an "ORDER BY" clause
Make sure that you have appropriate indexes on the two tables for the ids and the columns in the WHERE and ORDER BY clauses.
Look at the query execution plan to make sure that your indexes are used.


Search for current month data in postgresql 9.2

This is what you need:

SELECT *
FROM ticketdata
WHERE data_cadastro >= date_trunc('month', CURRENT_DATE);


Check 2 tables for differences, use programming language or pure SQL

Assuming you have access to all three servers from one of the servers, you can do this easily in SQL. I'm not sure if you meant Microsoft SQL Server when you tagged your question as "sql-server" (which is the tag for specifically Microsoft SQL Server), since you mention Python and Java so I won't go into the specifics of setting up linked servers or remote querying.
But in general this is an ANSI SQL way of comparing two tables and getting only the differences (so this will work in Microsoft SQL Server, Oracle SQL, MySQL, PostgreSQL, etc):
SELECT * -- Replace * with the actual columns you want, as SELECT * is bad practice
FROM table1 -- Source table
INNER JOIN table1a -- Server 1's Table
    ON table1.id = table1a.id -- Same id
    AND table1.price <> table1a.price -- Different price

If you want one dataset combined of the differences from all three servers this is how you can do it:
SELECT * -- Replace * with the actual columns you want, as SELECT * is bad practice
FROM table1 -- Source table
INNER JOIN table1a -- Server 1's Table
    ON table1.id = table1a.id -- Same id
    AND table1.price <> table1a.price -- Different price

UNION ALL

SELECT * -- Replace * with the actual columns you want, as SELECT * is bad practice
FROM table1 -- Source table
INNER JOIN table1b -- Server 2's Table
    ON table1.id = table1b.id -- Same id
    AND table1.price <> table1b.price -- Different price

UNION ALL

SELECT * -- Replace * with the actual columns you want, as SELECT * is bad practice
FROM table1 -- Source table
INNER JOIN table1c -- Server 3's Table
    ON table1.id = table1c.id -- Same id
    AND table1.price <> table1c.price -- Different price

The above uses the UNION ALL operator to bring back all 3 datasets as a single dataset.
Per your comments that this is Microsoft SQL Server, then you can look into using Linked Servers and OPENQUERY. Additionally these resources are particularly helpful for setting up access to remote servers:

MS Docs on Remote Servers
Querying remote data sources in SQL Server

To answer your main question, in short, yes this is a perfect data problem that can be done purely and simply in SQL.


What would be the fastest approach to bulk update this mysql table (Innodb)?

The first thing you need to determine about col2 is if it can be a PRIMARY KEY.

Run this query

SELECT COUNT(1),col2 FROM table GROUP BY col2 HAVING COUNT(1) > 1;

If nothing comes back, then col2 can be a UNIQUE KEY. If even one row comes back, then col2 cannot be a UNIQUE KEY. You can create an index on it.

Since this query would take a while without an index, just go ahead and index it

ALTER TABLE table ADD INDEX col2 (col2);

but that might take a very long time.

You may need a temp table to accomplish this:

Step 01) Create a table that will hold the one million col2 values

DROP TABLE IF EXISTS col2values;
CREATE TABLE col2values ENGINE=InnoDB SELECT col2 FROM table WHERE 1=2;
ALTER TABLE col2value ADD PRIMARY KEY (col2);

Step 02) You can load the col2values table with the one million col2 values

Step 03) Index col2

CREATE TABLE table2 LIKE table;
ALTER TABLE table2 ADD INDEX col2 (col2);
INSERT INTO table2 SELECT * FROM table;
DROP TABLE table;
ALTER TABLE table2 RENAME table;

Step 04) Perform update using INNER JOIN

UPDATE col2values A
INNER JOIN table B
USING (col2)
SET B.col1=1;

In the above steps

Step 03 needs to be done only once
Step 03 is done with 5 lines rather than ALTER TABLE table ADD INDEX col2 (col2); because it would require a full rollback operation should anything go wrong.
All other steps are your bulk insert steps going forward

Give it a Try !!!


Full list of steps very depends - what OS (source and target)?
Generally You can copy mysql between 100% similar computers, but You are must copy ALL folders and after - or start mysql manually or manually add MySQL to run as Windows / Linux service.

More easy ways:

If computer Similar - You can install MySQL from installation package and then copy only data folder (name and location depend from OS)
If computer different (like Windows -> Linux) - You can install MySQL on target computer, on Source computer dump all data using mysqldump, on Target computer make restore from dump using command line client


Try attaching the databse with ATTACH_REBUILD_LOG after moving the original log file.


Is it safe to make a Unique Index the Primary Key?

No it's not safe.

From a query perspective it might look OK, but if you don't know how the application handles primary keys you shouldn't change that without checking with the vendor first.

You could break their error handling, their future update scripts etc.

Just to give you an example, during a future upgrade they may want to change some tables where you added CDC and they will receive an error. What does your contract say about support in that case?

If you are using a vendor database it's up to them to support it. If you go around making changes you are basically putting your system in a state they never tested so they would (rightfully so) probably tell you you have an unsupported configuration when things go wrong.

We don't know what application or vendor you are talking about, but if it doesn't support customizations you probably shouldn't customize the database without asking them first.


Create/copy new superuser from an existing one

Well, if you have an existing superuser role called "super1", you could do:

CREATE ROLE super2 WITH SUPERUSER LOGIN;
GRANT super1 TO super2;

And, in theory at least, any of super1's privileges would be transferred over to super2. However, I'm not sure your question makes much sense, since superusers generally are allowed to override any privilege checks. As the documentation explains:

  It should be noted that database superusers can access all objects regardless of object privilege settings. This is comparable to the rights of root in a Unix system. As with root, it's unwise to operate as a superuser except when absolutely necessary.

I'd be interested to hear what privileges your existing superuser role had which were not automatically granted to whatever new superuser role you created by default.

Edit: and if you're interested in copying over the per-role configuration parameters (i.e. those documented under configuration parameters), then you could use a function like this (demo only, you may need extra error handling, security considerations, etc. for production use):

CREATE OR REPLACE FUNCTION copy_role_configs(source_role text, target_role text)
RETURNS VOID AS
$$
DECLARE
  setconfig_val text;
  eq_pos int;
  i int = 1;
BEGIN

  LOOP

    SELECT setconfig[i] INTO setconfig_val
    FROM pg_db_role_setting WHERE
      setdatabase = 0 AND setrole =
      (SELECT oid FROM pg_authid WHERE rolname = source_role);

    EXIT WHEN setconfig_val IS NULL;

    RAISE NOTICE 'Copying config % from % to %', setconfig_val, source_role,
                  target_role;
    SELECT strpos(setconfig_val, '=') INTO eq_pos;

    EXECUTE 'ALTER ROLE ' || target_role || ' SET ' ||
               substr(setconfig_val, 0, eq_pos + 1) || '''' ||
               substr(setconfig_val, eq_pos + 1) || '''' ;

    i := i + 1;

  END LOOP;

  RETURN;
END;
$$ LANGUAGE plpgsql strict;

-- Now call SELECT copy_role_configs('role1', 'role2') to copy configuration
-- parameters from role1 to role2.


Deadlocks With Trigger Based Replication

Before rows  are updated in the REFERENCE table the corresponding rows must be updated in the ORIGINAL table. If a session waits for a lock of a row of the REFERENCE table to update it must get the lock of the corresponding ORIGINAL table row lock. But this is hold by the session that holds the REFERENCE table row lock.
I can't see any problem with this design, but I am not 100% sure. Nevertheless I think it i easier to read if you create an update statement that is triggered for updates and an insert statement that is triggered for inserts. But maybe this is a matter of taste.


Always On Availability Group manual failover and OS upgrade considerations

Firstly - is there a good script to give me an overview of AG
  configuration and health? I understand the console in SSMS gives me an
  overview, but is there no script that can automate the task and be
  used to detect or report on servers lagging behind, etc?

Keep in mind that there are many things that are important regarding Always On Availability Groups that cannot be answered in one or multiple answers alone, below are some considerations.

The dashboard in ssms that you mention is a view into the AG, but you there are many columns unchecked. A few of these:

This dashboard is running queries behind the scenes, you could in theory trace your login with the profiler / extended events and get that you are running with SSMS.

An example of important monitoring is the secondary not being able to follow in redoing the log records, resulting in longer failover times.
This can be checked with the REDO QUEUE size metric.

More on that here

Querying the redo queue size is not that hard:

SELECT redo_queue_size
FROM master.sys.dm_hadr_database_replica_states;

Some of the important dmv's you should look through:

master.sys.availability_groups
master.sys.dm_hadr_database_replica_states
master.sys.availability_replicas
master.sys.dm_hadr_database_replica_states

  Secondly, how can I check whether any secondary has been set up for
  read-only routing, and/or backups? I believe backups are run only
  against SQL01, so what happens if/when there is a failover making
  SQL02 the primary?

This depends on your backup preferences in the AG properties:

In your case, either Primary, Prefer Secondary or Any replica will be set.
If Primary is set then you should change this to one of these two other options.

Even better is that you opt for a solution that is AG aware, one free solution is Ola Hallengren's Maintenance Solution Backups

You could check if read only routing is set up by querying the read_only_routing_url column.

SELECT replica_server_name
    , read_only_routing_url
    , secondary_role_allow_connections_desc
FROM sys.availability_replicas

  Thirdly and probably related - if the AG exists to route queries to a
  given node, I can understand that all databases are kept in sync so
  that applications can continue querying and any failover between nodes
  should be transparent to the application, but if backups (or mailing
  of query/report results) are run by scheduled Agent jobs, how are
  these handled after failover? Is this something manually kept in sync
  by the DBA?

Backups running on the primary will be rolled back when a failover occurs.

Not sure about when a secondary transfers to primary

You could add retry attempts to your jobstep / other process used to take backups.

  Fourth, along similar lines - is it conceivable that there are
  differences between databases across two nodes. ie. I'm not talking
  about differences that will synchronise automatically; I mean might
  someone have made a change - such as adding a trigger to a table - on
  a replica that won't have replicated across all nodes?

Operations on your primary Always On AG databases are transferred over to other nodes.
The trigger will be replicated in normal circumstances.

What you do need to manage yourself are Jobs and Logins.

You can find more of that in this post: Management of Logins and Jobs for the Databases of an Availability Group (SQL Server)

An idea for managing these is by using the commands Copy-DbaLogin and Copy-DbaAgentJob from dbatools to copy these jobs over.

Running these on a set schedule via a job is a possibility.

  Fifth - to start thinking about manual failovers, what things should
  be checked ahead of failover? I saw a question here talking about
  checking how quorum is formed - do I need some kind of report on
  Quorum setup before I fail over? If so, what am I looking for?

The main database health (Synchronized for Sync replica's, Synchronizing for Async replica's), redo_queue size, log_send_queue_size, if any big queries are running on the primary that will take a while to rollback.

Regarding quorum, starting from Windows Server 2012 you have something called dynamic quorum, meaning that votes are recalculated when the quorum changes. This is done by changing the vote state. For example when you have two nodes without a fileshare witness in your WSFC, one of the secondaries will have a vote state of 0 instead of 1.

Read up on dynamic quorum here

  Sixth, another question here mentions checking the is_failover_ready
  column on sys.dm_hadr_database_cluster_states - but when I query that
  table, I'm told "invalid object name". What gives?

I remember that question and asked him about it, that person meant sys.dm_hadr_availability_replica_cluster_nodes.

  Seventh To the specifics of OS upgrade, what is the basic approach -
  Should I, say, take SQL04 off the AG (or pause syncing), then upgrade
  its OS, restart the machine, then re-add to the AG / continue syncing?
  If so, how? And how do I know at the end of everything that all is ok?
  What would be the "rollback plan" if something doesn't work out as
  hoped?

A main walktrough of patching AG's can be found here

Do not remove these databases from the AG, as synchronization might not work when log backups where taken. You would have to restore the log backups before being able to add them again.

Regarding the steps you should execute, there are some differences between what AG setup you have.

When you have only two nodes to remember to change the failover mode to manual because you don't want unexpected failovers while you are patching this secondary.

If you have only two possible secondaries you should do the same when patching these nodes. In short, always set the failover mode of secondaries that are being patched to manual.

The other considerations before turning of the secondary / failing over align with these for a manual failover process discussed earlier.
After the node comes back online, validate the synchronization & database states.

The rollback plan would be that you still have the other nodes, re-add the databases to the node that failed.

  Eighth Continuing with the OS update, do I do the same thing for
  SQL02, then failover from SQL01 to SQL02, upgrade SQL01 OS then fail
  back? Again, how to determine each step is "successful"?

Answered in the answer to question #8.

  Ninth - what happens if I initiate a manual failover but SQL01 is in
  the middle of backing up 20 or so databases? Is there something to
  check for me ahead of time, on how database backups might be affected,
  or something to check after failover, to ensure database backups are
  still functioning as required, post failover? We have full nightly
  backups on all databases plus partial and incremental backups - but
  only on some databases.

Backups on the primary will rollback on failover, you would have to add retry logic.
Not sure about when a secondary transfers to primary

  Tenth - similarly, what if automated jobs are running at the time of
  failover? These might be Agent jobs updating data or running queries
  for mailout, or they might be a third-party BI system pulling data for
  ETL prior to import into the BI end. Is it safe to presume the BI
  systems will continue receiving their data post-failover, or should I
  be checking how they're making their connections to the DB first? (Is
  it possible they are connecting to SQL01 specifically (or any other
  node really), in spite of the AG, so that when SQL01 goes down for the
  OS upgrade, the BI extract fails?

These uncommitted transactions will be rolled back and the job will fail, you would have to add retry logic.

If it is a big operation, consider splitting it up in smaller parts.


Selecting data from one table via categoryID field and displaying in different areas whilst excluding categoryID for one area

You can use LEFT JOIN for this:

SELECT
     articles.*,
     art_categories.*
FROM art_categories LEFT JOIN articles
ON articles.categoryID=art_categories.categoryID
WHERE art_categories.categoryID=8
ORDER BY publishdate DESC LIMIT 5

as advice - avoid use *, better to list all columns by name, it also allow avoid duplicates and/or arrange aliases for them


How Plan Explorer Group Operators in Query Columns Tab

At first, it looks like you've modified XML, because the query does not exactly match the plan.

At second, format your query like this

SELECT o2.Column1,
       o2.Column2,
       o1.Column3,
       o1.Column4,
       o1.Column5
  FROM Object1 as o1,
       Object2 as o2,
       Object3 as o3
 WHERE o1.Column3 = o3.Column6
   AND o2.Column1 = o3.Column7
   AND o3.Column8 between ? and ?
   and o3.Column7 < ?

At third, instead of having separate indexes on Column7 and Column8 (green and red seeks) you better create one index on Column7 + Column8 (green first) and have included Column6. That will help you to avoid Red Scan.

At fourth, (optional) you can create index on o1.Column3 with included columns o1.Column3, o1.Column4, o1.Column5