Grouping Subsets of Rows with Null Values within an Ordered Set

Your sample data does not match your description and confused me at first. As sp_BlitzErik points out this is an island-and-gap problem. The solution is pretty straightforward if you have access to window functions. First, we can enumerate the table per member alone, let's call this full_order (this happens to be the same as day, but I'll ad it for generality). Second, we can enumerate the table per member and whether they were active on that day, let's call this partial_order

 select day, active, date, member
      , row_number() over (partition by member
                           order by day) as fullorder
      , row_number() over (partition by member
                          ,case when active is null then 0 else 1 end
                          order by day) as partialorder
 from src

DAY         ACTIVE     MEMBER      FULLORDER            PARTIALORDER

----------- ---------- ----------- -------------------- --------------------
      1 -                  123                    1                    1
      2 -                  123                    2                    2
      3 01/03/2017         123                    3                    1
      4 01/04/2017         123                    4                    2
      5 01/05/2017         123                    5                    3
      6 -                  123                    6                    3
      7 -                  123                    7                    4
      8 -                  123                    8                    5
      9 01/09/2017         123                    9                    4
     10 01/10/2017         123                   10                    5

If the difference between full_order and partial_order changes, it means that active has changed from null to a value, or vice versa. Therefore we can form a group with this difference. Within each such group we can pick the min(active) and max(active) to form an interval:

select member, grp, min(date), max(active)
from (
    select day, active, date, member
         , row_number() over (partition by member order by day)
         - row_number() over (partition by member
                             ,case when active is null then 0 else 1 end
                              order by day) as grp
    from src
)
group by member, grp

MEMBER      GRP                  3          4
----------- -------------------- ---------- ----------
    123                    0 01/01/2017 -
    123                    2 01/03/2017 01/05/2017
    123                    3 01/05/2017 -
    123                    5 01/08/2017 01/10/2017

It's probably easiest to add another level of nesting to get the desired result:

select member, min_active
     , coalesce(max_active, min_active) as max_active
     , case when max_active is null then 'INACTIVE' else 'ACTIVE' end as status
from (
    select member, grp, min(date) as min_active, max(active) as max_active
    from (
        select day, active, date, member
             , row_number() over (partition by member order by day)
             - row_number() over (partition by member
                                 ,case when active is null then 0 else 1 end
                                 order by day) as grp
        from src
    )
    group by member, grp)

MEMBER      MIN_ACTIVE MAX_ACTIVE STATUS
----------- ---------- ---------- --------
    123 01/01/2017 01/01/2017 INACTIVE
    123 01/03/2017 01/05/2017 ACTIVE
    123 01/05/2017 01/05/2017 INACTIVE
    123 01/08/2017 01/10/2017 ACTIVE


Best Practice For Data Views with Linked Server in SQL Server

In my experience, the round-robin joins on both legs of the query certainly make for an expensive plan.  It depends on a few issues, but consider using these options to help you:

Use a temporary or a work table on Server A named #DataTable_ServerB_local to store the results from Server B.
If possible create a stored procedure Prepare_Datatable_ServerB_Data on Server B that you can call remotely to get the Server B data. This may join back to Server A to get some data.
UseOPENQUERY to execute the Remote Stored Procedure Prepare_Datatable_ServerB_Data  to insert its data into Server A's #DataTable_ServerB_local. The code internal to the stored procedure would join back to the local server to get what I suspect are the relatively few rows that it needs.
Join DataView_ServerA with #DataTable_ServerB_local which are now both on Server A to provide the final result.

This strategy assumes that Server B joining back to Server A is the smaller set of joins.  (If this is not the case then you will need to switch directions with this strategy.)

The whole point of this approach is to:

Limit the number of rows passing between the servers.
Avoid as many joins across the linked servers as possible.
To the extent possible (e.g. the Prepare_Datatable_ServerB_Data procedure) process data on the server where the data is stored.

Big joins across a linked server can be very expensive in terms of time and the optimizer may not be able to make good decisions.


It worked as,

select a,(select (select count(id) from foo) + (select count(id) from bar))
as 'alpha' from beta;


Update end date by adding duration to start date in derby database

UPDATE PROJECT
SET PEDATE = DATE( {fn TIMESTAMPADD(SQL_TSI_MONTH, PDURATION, PSDATE)} );

This query (provided by Akina) updates my end date when adding duration to start date.


Postgres numeric type overflow after 1023 bisection of 1

The numeric(1000,900) column has nothing to do with it; the select alone produces the overflow error.

Calling power() with integer arguments will default to the floating-point version of the function. power(2,1024) is causing the overflow, as the maximum base-2 double-precision exponent is 1023.

There is also a version of power() which takes numeric arguments, and will handle 21024 without overflowing. If you cast your inputs to numeric, it will call this version instead:

select ((1 / power(2, i::numeric)))+10000000, pg_column_size(( 1/power(2, i::numeric))+10000000)
from generate_series(1, 1024) as i;


Reports with recurring events DB Schema in SQL Server

Take a look at the way SQL Server handles this for SQL Agent job schedules. Specifically, read through the columns prefixed freq_* as these determine the frequency of execution.

You could use the same or similar table schema to store frequency information about your reports. You could even display 'next scheduled date' information quite easily if you also stored the creation date of the schedule for a particular report.

There is a good article on SQLServerCentral that dives into this and should help you develop a solution that would suit.


User-defined function returns first character only although RETURN VARCHAR(10) is defined?

This is a bug with SQL Server inline functions in 2019 RTM.
DECLARE @Salary MONEY = 100440;
SELECT dbo.ufn_GetSalaryLevel(@Salary)

The expression in the constant scan (with CONVERT_IMPLICIT changed to CONVERT to make it runnable) is
SELECT CONVERT(VARCHAR(10), CASE
                              WHEN CASE
                                     WHEN CASE
                                            WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                              THEN ( 1 )
                                            ELSE ( 0 )
                                          END = ( 0 )
                                          AND CASE
                                                WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
                                                  THEN ( 1 )
                                                ELSE ( 0 )
                                              END = ( 0 )
                                       THEN ( 1 )
                                     ELSE
                                       CASE
                                         WHEN CASE
                                                WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                  THEN ( 1 )
                                                ELSE ( 0 )
                                              END = ( 0 )
                                              AND CASE
                                                    WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
                                                      THEN ( 1 )
                                                    ELSE ( 0 )
                                                  END = ( 1 )
                                           THEN ( 1 )
                                         ELSE
                                           CASE
                                             WHEN CASE
                                                    WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                      THEN ( 1 )
                                                    ELSE ( 0 )
                                                  END = ( 1 )
                                               THEN ( 1 )
                                             ELSE ( 0 )
                                           END
                                       END
                                   END = ( 0 )
                                THEN ''
                              ELSE CONVERT(VARCHAR(1), CASE
                                                         WHEN CASE
                                                                WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                  THEN ( 1 )
                                                                ELSE ( 0 )
                                                              END = ( 0 )
                                                              AND CASE
                                                                    WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
                                                                      THEN ( 1 )
                                                                    ELSE ( 0 )
                                                                  END = ( 0 )
                                                              AND CASE
                                                                    WHEN CASE
                                                                           WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                             THEN ( 1 )
                                                                           ELSE ( 0 )
                                                                         END = ( 0 )
                                                                         AND CASE
                                                                               WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
                                                                                THEN ( 1 )
                                                                               ELSE ( 0 )
                                                                             END = ( 1 )
                                                                      THEN ( 1 )
                                                                    ELSE
                                                                      CASE
                                                                        WHEN CASE
                                                                               WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                                THEN ( 1 )
                                                                               ELSE ( 0 )
                                                                             END = ( 1 )
                                                                          THEN ( 1 )
                                                                        ELSE ( 0 )
                                                                      END
                                                                  END = ( 0 )
                                                           THEN 'High'
                                                         ELSE CONVERT(VARCHAR(4), CASE
                                                                                WHEN CASE
                                                                                WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                                THEN ( 1 )
                                                                                ELSE ( 0 )
                                                                                END = ( 0 )
                                                                                AND CASE
                                                                                WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
                                                                                THEN ( 1 )
                                                                                ELSE ( 0 )
                                                                                END = ( 1 )
                                                                                AND CASE
                                                                                WHEN CASE
                                                                                WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                                THEN ( 1 )
                                                                                ELSE ( 0 )
                                                                                END = ( 1 )
                                                                                THEN ( 1 )
                                                                                ELSE ( 0 )
                                                                                END = ( 0 )
                                                                                THEN 'Average'
                                                                                ELSE CONVERT(VARCHAR(7), CASE
                                                                                WHEN CASE
                                                                                WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                                THEN ( 1 )
                                                                                ELSE ( 0 )
                                                                                END = ( 1 )
                                                                                THEN 'Low'
                                                                                ELSE NULL
                                                                                END, 0)
                                                                                END, 0)
                                                       END, 0)
                            END, 0)

The branch with the literal High has a CONVERT(VARCHAR(1) that truncates it.
Disable inlining for that function
CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money)
RETURNS VARCHAR(10)
WITH INLINE=OFF
AS

or install the latest CU to fix this

or cast all string literals in the function to VARCHAR(10) explictly (the varchar(1) is due to the RETURN ''

or rewrite the function to be less procedural. The expression generated by the inling is pretty complex in the original code and much simpler with the below (it is able to constant fold dbo.ufn_GetSalaryLevel(100440) to 'High' at compilation time).

    CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money)
    RETURNS VARCHAR(10) AS
    BEGIN
    RETURN CASE
             WHEN @Salary < 30000
               THEN 'Low'
             WHEN @Salary <= 50000
               THEN 'Average'
             ELSE 'High'
           END;

    END;


Can't get any fragmentation of index for testing in SQL Server database

If you're trying to artificially induce fragmentation, insert the data "backwards". That is, if your clustering key is an integer column ascending, load the data in descending order by that same integer column. You should get page splits aplenty.


Is it possible to prevent an insert without rolling it back?

I am not readily availble to provide exact details but I believe 2 more ways are possible:

Using blackhole engine on table_a
Using proxysql

Using Blackhole engine

It seems to be the simplest solution.

  The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it.

See MySQL 5.5 doc for more details.

First, if you need to keep the table_a you need to create a copy of it with the blackhole engine ;

CREATE TABLE table_a(
    id    INT unsigned auto_increment primary key,
    field CHAR(5)
)Engine=InnoDB;

CREATE TABLE table_blackhole(
    id    INT unsigned auto_increment primary key,
    field CHAR(5)
)Engine=BLACKHOLE;

Then, create the trigger on table_blackhole.
Inside the trigger, use

an INSERT on table_a (for example) for the normal treatment (we don't prevent the insertion)
or an INSERTon table_b for the specific treatment (we "prevent" the normal insertion but there is no rollback).

Instead of inserting rows in table_a, do your INSERT on table_blackhole.


How force specific table to not logging (for all DML)?

If you are so concerned about the redo logging volume, you can consider writing the logon/logoff audit records to a file instead, using UTL_FILE for example.

As an aside, I'd be hard pressed to believe that updates of the audit table generate more logs than other activities in the database. This would mean that your users mostly log on to the system and then immediately log off, without doing much else.


Need some help in Group by StateID - TSQL Query

Group by isn't needed since you only select one stateid

SELECT  StateID,
  SUM(CASE WHEN [Year] = 2016 THEN FileSize END) AS FileSize_2016,
  count(case when year = 2016 then Fileid end ) AS TotalFs2016,
  SUM(CASE WHEN [Year] = 2017 THEN FileSize END) AS FileSize_2017,
  count(case when year = 2017 then Fileid end ) AS TotalFs2017,
  SUM(CASE WHEN [Year] = 2018 THEN FileSize END) AS FileSize_2018,
  count(case when year = 2018 then Fileid end ) AS TotalFs2018,
  SUM(CASE WHEN [Year] = 2019 THEN FileSize END) AS FileSize_2019,
  count(case when year = 2018 then Fileid end ) AS TotalFs2019
--FileState
FROM
(
  SELECT StateID,[Year],FileID,FileTotalSizeMBs AS FileSize  --, FileState
  FROM #Temp1
  UNION ALL
  SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
  FROM #Temp2
  UNION ALL
  SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
  FROM #Temp3
  UNION ALL
  SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
  FROM #Temp4
)t
where t.StateID like '570'


What's the differences between utf8_general_ci and utf8_unicode_ci and utf8_binary collation in MySQL?

According to the MySQL Documentation

  Collations have these general characteristics:

  Two different character sets cannot have the same collation.

  Each character set has one collation that is the default collation.
  For example, the default collation for latin1 is latin1_swedish_ci.
  The output for SHOW CHARACTER SET indicates which collation is the
  default for each displayed character set.

  There is a convention for collation names: They start with the name of
  the character set with which they are associated, they usually include
  a language name, and they end with _ci (case insensitive), _cs (case
  sensitive), or _bin (binary).

  In cases where a character set has multiple collations, it might not
  be clear which collation is most suitable for a given application. To
  avoid choosing the wrong collation, it can be helpful to perform some
  comparisons with representative data values to make sure that a given
  collation sorts values the way you expect.

StackOverflow has a list of questions tagged utf-8 and collation

ServerFault only has one tagged utf-8 and collation

There is a website called efreedom.com that has links all around StackOverflow concerning utf8 : http://efreedom.com/Question/1-4784168/Change-Collation-Utf8-Bin-One-Go

Here is another site about collations as its place in the MySQL World : http://www.collation-charts.org/

Here is a link explaining binary collations : http://dev.mysql.com/doc/refman/5.0/en/charset-binary-collations.html


You can use the latest version of SSMS with SQL 2008 R2. Im not aware if any backward compatibility problems.

It will also work with SQL 2017 and Azure.

SSDT is a tool used to develop SSAS, SSIS and SSRS Reports. It wont cause any problems installing alongside SSMS.

SSIS changes a far bit in 2012 namely in the use of SSIS catalog. IMO its a great improvement. Depending upon your layout it may be feasible to upgrade SSIS first or independantly.

Note that once you open an old solution in a new version of SSDT or visual studio it will want to upgrade the solution. Once that happens you wont be able to open it again with an old version. Ive been through a similar scenario as you describe. In the end i found it was just easier to take the plunge and upgrade to the current client tools.


€ Euro sign/symbol not showing up on Oracle 10gr2 and aix 6.1

Your database character set is WE8ISO8859P1, it doesn't have the € sign. Either use NVARCHAR2 or change your database character set (export, reinstall with a character set that supports the euro sign like WE8ISO8859P15 or AL32UTF8, and import).

Here's an example of NVARCHAR2:

SQL> SELECT * FROM nls_database_parameters WHERE parameter LIKE '%SET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CHARACTERSET               WE8ISO8859P1

As you can see I am in the same situation as you.

SQL> CREATE TABLE prueba (id NUMBER, euro NVARCHAR2(20));

Table created.

SQL> INSERT INTO prueba VALUES (1, N'€');

1 row created.

SQL> INSERT INTO prueba VALUES (2, UNISTR('\20AC'));

1 row created.

SQL> INSERT INTO prueba VALUES (3, NCHR(8364));

1 row created.

SQL> INSERT INTO prueba VALUES (4, CHR(8364 USING NCHAR_CS));

1 row created.

SQL> INSERT INTO prueba VALUES (5, '€');

1 row created.

SQL> SELECT id, euro, ascii(euro) FROM prueba;

        ID EURO                 ASCII(EURO)
---------- -------------------- -----------
         1 €                           8364
         2 €                           8364
         3 €                           8364
         4 €                           8364
         5 ¿                            191


SQLAnywhere: How to write result of a query into a file with non-interactive session

You can specify the OUTPUT to a file in your SQL directly:

dbisql -c "connection-string" SELECT * FROM table; OUTPUT TO 'results.txt' FORMAT TEXT

As per the Sybase documentation.


Is there any way to force MySQL use Hash Join instead of Nested Loop Join?

Up to the (recently released) 5.6 version, no, you can't. Unfortunately, the only method that the optimizer knows is nested-loop method. As the 5.6 documentation says:

  MySQL resolves all joins using a nested-loop join method.

In other words, no hash join algorith or variation has been implemented, not even in version 5.7 - although that is still in development (hope never dies).

There is one alternative. MariaDB, a MySQL fork has implemented various other join methods in the latest (5.3 and 5.5) releases which can be drop-in replacements of MySQL (5.1 and 5.5 respectively) versions.

In their Block-Based Join Algorithms page, they describe the new methods:

  In the versions of MariaDB/MySQL before 5.3 only one block-based join algorithm was implemented: the Block Nested Loops (BNL) join algorithm. It could only be used for inner joins. MariaDB 5.3 (and later) enhances the implementation of BNL joins and provides a variety of block-based join algorithms that can be used for inner joins, outer joins, and semi-joins. Block-based join algorithms in MariaDB employ a join buffer to accumulate records of the first join operand before they start looking for matches in the second join operand.

  This page documents the various block-based join algorithms.

  Block Nested Loop (BNL) join
  Block Nested Loop Hash (BNLH) join
  Block Index join known as Batch Key Access (BKA) join
  Block Index Hash join known as Batch Key Access Hash (BKAH) join

There are also some optimizer switches that affect which algorithms will be considered when optimizing a query.


Global Temp Table Error when Stored Proc run by SQL Job on a Schedule

With global Temp Tables, when the session that created it no longer exists and no other T-SQL statements are actively referencing the table, it gets dropped automatically.
Per Microsoft's Create Table docs:

Global temporary tables are automatically dropped when the session
that created the table ends and all other tasks have stopped
referencing them. The association between a task and a table is
maintained only for the life of a single Transact-SQL statement. This
means that a global temporary table is dropped at the completion of
the last Transact-SQL statement that was actively referencing the
table when the creating session ended.

This is only a guess, but perhaps whatever session that is creating the global temp table and code that is actively referencing it both stop before the Agent Job gets a chance to access it? (E.g. if you're using multiple steps in the Agent job, and it's a subsequent step that tries to access the global temp table.) That would make the difference from you just executing the whole thing in a new query window in one go.


The following queries evaluate the expensive predicate just once for each parent row. To achieve this, the predicate is evaluated in a separate subquery. The first version uses a lateral join, the second an inner join. This meets the requirements from the question.

select   q2.period
from     parent,
lateral  (
           select <some complex predicate>
         ) as q1(predicate),
lateral  (
           select  parent.period
           where   q1.predicate
           union all
           select  child.period
           from    child
           where   child.parent_identifier = parent.identifier and
                   not(q1.predicate)
         ) as q2(period);

select     q2.period
from       parent,
inner join (
             select  parent.identifier, <some complex predicate>
             from    parent
           ) as q1(identifier, predicate)
on         parent.identifier = q1.identifier,
lateral    (
             select  parent.period
             where   q1.predicate
             union all
             select  child.period
             from    child
             where   child.parent_identifier = parent.identifier and
                     not(q1.predicate)
           ) as q2(period);


What happens if I load last 10 rows then next 10 after other rows are inserted?

And that is one of 3 problems with using OFFSET for "pagination".

The other two are

What is some rows were deleted?
Performance.

The solution to all of them is to "remember where you left off".  Here is my blog on the issues.

Most people who use OFFSET come to this forum bemoaning poor performance.  Not many have (yet) discovered the "bug" you are pointing out.


Pass array of mixed type into stored FUNCTION

Following the link you have supplied and carefully reading the good answers of a_horse_with_no_name and Erwin Brandstetter I've tried to solve this question.

Use array of composite type as function parameter and access it

First I've created a composite TYPE:

CREATE TYPE cr_journal AS (
  gid INTEGER,
  device_id TEXT,
  item TEXT,
  vat_percentage INTEGER,
  vat_sum INTEGER,
  price NUMERIC);

Then I've slightly edited your function:

CREATE OR REPLACE FUNCTION test_insert(daco TEXT, _journal_arr  cr_journal[]) RETURNS TEXT
  LANGUAGE plpgsql
  AS $function$

DECLARE

    cj cr_journal;

BEGIN

    FOREACH cj IN array _journal_arr LOOP

        INSERT INTO cashreg_journal
            (gid, device_id, item, vat_percentage, vat_sum, price)
        SELECT cj.*;

    END LOOP;

  RAISE NOTICE 'Daco: %', daco;

  RETURN 'Saved';

END;
$function$;

And the last step, you should modify your select sentence, instead of SELECT * FROM function() you should call it as SELECT function() and you should explicitly cast the array as an array of type cr_journal[] in this way:

SELECT test_insert('Test daco',
                   (ARRAY[(1, '000-111', 'Test-P', 20, 20, 100),
                          (1, '000-111', 'Test-F', 20, 10, 100)])::cr_journal[]);

returns: Saved

Now you can check it:

select * from cashreg_journal;

And this is the result:

id | gid | device_id | item   | vat_percentage | vat_sum | price
-: | --: | :-------- | :----- | -------------: | ------: | ----:
 1 |   1 | 000-111   | Test-P |             20 |      20 |   100
 2 |   1 | 000-111   | Test-F |             20 |      10 |   100

db<>fiddle here


dbms_datapump API exclude statistics during import

You need to use:

DBMS_DATAPUMP.METADATA_FILTER(l_job_handle,
                              'EXCLUDE_PATH_EXPR',
                              'IN (''STATISTICS'')');


This answer assumes that users.login is unique.

First, alter the users table to include an address_id column.
Next, if you add a addresses.login column you would then be able to do the following query after the insert into addresses:

UPDATE users, addresses SET users.address_id = addresses.address_id
WHERE users.login = addresses.login

Finally drop the addresses.login column

An alternative would be to use an AFTER INSERT trigger on addresses to update the users table based on the unique address entries. Unfortunately, this assumes that all the fields of addresses combined represent a unique users.login, which for example would be invalid if a married couple each had logins but shared the same address.


How to do a mysqldump with a use database in the dump

The flag is --databases

mysqldump -u user -p --add-drop-tables --databases database > *file*

So if you have more than one schema names you need to set which databases with the --databases flag. Then it inserts which one is necessary to be used.


This is not a "full-text"  search.
An index on parent_tree will probably do the job.
Adding a root column will be neater and might give the optimizer a better chance to take the right decisions.

... where root = 'a'


MySQL - Unable to Convert Primary Key to Auto Increment

Your post intrigued me. There is a "workaround" here, but I was curious, so I performed the following tests. You might like to try what worked for me below before trying the workaround. If there are FOREIGN KEYs, and potential conflicts (duplicates and/or KEY violations), then this won't work. It may be the reason for your error.

I created a table (bill) as follows.

mysql> CREATE TABLE bill(fred INT, paul VARCHAR(10));
Query OK, 0 rows affected (0.31 sec)

Then inserted some sample values.

mysql> INSERT INTO bill VALUES(1, 'adsfd');
    Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO bill VALUES(2, 'adsfd');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO bill VALUES(3, 'xxxx');
Query OK, 1 row affected (0.05 sec)

I then performed a SHOW CREATE TABLE My_Table\G (the \G gives clear output that can be cut and pasted).

mysql> SHOW CREATE TABLE bill\G
*************************** 1. row ***************************
       Table: bill
Create Table: CREATE TABLE `bill` (
  `fred` int(11) DEFAULT NULL,
  `paul` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Just to see the results of my INSERTs

mysql> SELECT * FROM bill;
+------+-------+
| fred | paul  |
+------+-------+
|    1 | adsfd |
|    2 | adsfd |
|    3 | xxxx  |
+------+-------+
3 rows in set (0.00 sec)
mysql>

Now, issue your ALTER TABLE statement.

ALTER TABLE bill MODIFY COLUMN fred INT AUTO_INCREMENT NOT NULL PRIMARY KEY;

Recheck the definition of your table.

mysql> SHOW CREATE TABLE bill\G
*************************** 1. row ***************************
       Table: bill
Create Table: CREATE TABLE `bill` (
  `fred` int(11) NOT NULL AUTO_INCREMENT,
  `paul` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`fred`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

And the field fred is indeed now a PRIMARY KEY and also an AUTO_INCREMENT.

And to check that it's working.

mysql> INSERT INTO bill (paul) VALUES('yyyy');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM bill;
+------+-------+
| fred | paul  |
+------+-------+
|    1 | adsfd |
|    2 | adsfd |
|    3 | xxxx  |
|    4 | yyyy  |
+------+-------+
4 rows in set (0.00 sec)

So, it's also AUTO_INCREMENTing correctly. I'm not sure why your original statement gave the strange ALTER TABLE causes auto_increment resequencing error - maybe a bug of some sort? While performing these tests, I made a number of small errors - MySQL appears to be very fussy about the exact order of steps and the particular syntax of the ALTER TABLE statement.


Merging two backups form a postgresql database

I don't know an easy way, but I'd try this:

Restore both databases individually and dump out only the data using INSERT statements (instead of COPY).  Something like pg_dump -a --inserts.
Restore the schema from the original backup into the target database.
Disable all foreign key constraints.
Restore the first dump created in 1.
Restore the second dump created in 1., ignore errors.
Enable foreign key constraints.


Is it possible to store such Tb's in a single machine

Definitely. Though that machine becomes a large single-point-of-failure.
Building a petabyte scale storage system that is efficient and fault tolerant can itself be a challenge, doing it with cost efficiency too even more so. I assume that being a "newbie as DBs" also means you are not particularly experienced with storage solutions either, so you don't want to be trying to put together a custom build.
A not un-common arrangement is to have a couple of clustered machines running your chosen DBMS (SQL Server etc.) using an "off the shelf" SAN based storage system. I did a quick calc with the first search result for "petabyte SAN" and a ~700Tb arrangement came in at around £40,000 and that is just the storage unit and drives to populate it: add onto that racks to hold it all, decent spec machines for the clustered database servers, the enterprise licensing that is likely to be needed, ... This level of storage is never going to be cheap. You also need to factor in the ongoing costs: electricity to power it all and the required air conditioning too, replacing drives when they fail, and also the man-power to monitor and maintain it all!
You would almost certainly be better off looking at managed solutions, perhaps with one of the bigger cloud providers, and let them worry about much of the scaling, redundancy, reliability, power, etc. problems for you. For this scale you are unlikely to find an "off the shelf" price so you'll need to talk to them directly. Be warned: this amount of storage, particularly for rapid access, with good resiliency (in terms of both data security and service availability) is not going to be remotely cheap, there is no way it can be.

as per my understanding, horizontal scaling is not possible in SQL. Please correct this if I am wrong.

It is possible, though whether it is practical compared to other options depends on your data and access patterns.

What will be the latency comparison of SQL vs NoSQL.
Because of horizontal scaling, will a noSQL db like DynamoDB give lower latency than a SQL Db.

All of the above can only really be answered with "it depends" without a lot more information about the data your are needing to store, and at least a vague idea of expected retrieval patterns and other requirements.
Just knowing the size and expected growth of the data is not nearly enough for a more detailed discussion.


Auto Switch DB to primary back (persistent mode?) when available in a High Availability Always On settings

You don't really want to do this automatically because:

A long-running transaction could be running on DB2, and you wouldn't want to interrupt it (especially given the problems with single-threaded rollbacks)
DB1 might come back online and SQL Server might be available, but the databases might have problems (or just be starting up)
DB1 may not be in sync (if DB1 went down for a while, it might take quite some time for it to get back in sync with DB2)


As far as I remember when I investigated RAC you need to add the VIP addresses to the DNS.


Why does this query take so long to execute?

Here's what I see in the XML for your actual plan:

<WaitStats>
<Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="66883" WaitCount="1649" />
<Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="56086" WaitCount="3980" />
</WaitStats>
<QueryTimeStats CpuTime="6665" ElapsedTime="73522" />

You can also see this information in the root node of the plan in SSMS if you don't like looking at XML:

You can read about the SOS_SCHEDULER_YIELD wait here. A good layman's explanation is that your query was only able to execute on a CPU for 6639 ms. It spent 66883 ms on a queue waiting to get on a CPU. Based on your description of the problem ("executing the view only takes 6 seconds"), you have a server performance issue instead of a query performance issue. Of course, there may be ways to make the query use even less CPU, but I would approach this problem by verifying that the server has the correct number of CPU cores/DTUs/whatever and by looking at the queries that use the most CPU on the server. Your query could run almost 12X faster if there was less load on the server.


As a_horse commented, a COMMENT ON DATABASE db IS 'foo'; would be a simple option.

set_config() settings are for the duration of the transaction or session only. If you need to persist information beyond the current session, that won't do.

For more structured bookkeeping, maybe including a timestamp etc, consider a bookkeeping table in another database in the same db cluster - without altering the db in question at all. (Maybe in addition to a comment on the main db indicating the current status.) Sample psql session:

CREATE DATABASE db_meta;
\c db_meta
CREATE TABLE db_status_log (
  log_id serial PRIMARY KEY
, log_ts timestamptz NOT NULL DEFAULT now()
, status text
);

Etc.

\c db_meta is the psql meta-command to connect to a db.


Will I need to create a pivot or can I alter this query

This should work.  I took the original CTE, partitioned by part and date and numbered those.  This gave every part on each date it's own number starting with 1.

Then in the case statement, adding WHERE Number = N allows it so that each part is unique.

Finally, the column names were modified to have their respective numbers.

Since this is unwieldy, dynamic sql could be used to generate the repetitive code.

    With CTE AS (SELECT * FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Part + Pull_Date ORDER BY PartSN) AS [Number],
  Pull_Date,
  Well_Name,
  Part,
  PartSN,
  PartPN
FROM (
SELECT Pull_Date, Well_Name, Part1 Part, Part1_PN PartPN, Part1_SN PartSN
  FROM test
UNION ALL
    SELECT Pull_Date, Well_Name, Part2, Part2_PN, Part2_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part3, Part3_PN, Part3_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part4, Part4_PN, Part4_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part5, Part5_PN, Part5_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part6, Part6_PN, Part6_SN from test
) subquery) new
  )

 Select Pull_Date, Well_Name,
    MIN(CASE WHEN Part='BODH' AND Number=1 THEN 'BODH' ELSE NULL END) [BODH1],
    MIN(CASE WHEN Part='BODH' AND Number=1 THEN PartPN Else NULL END) BODH_PN1,
    MIN(CASE WHEN Part='BODH' AND Number=1 THEN PartSN ELSE NULL END) BODH_SN1,
    MIN(CASE WHEN Part='BODH' AND Number=2 THEN 'BODH' ELSE NULL END) [BODH2],
    MIN(CASE WHEN Part='BODH' AND Number=2 THEN PartPN Else NULL END) BODH_PN2,
    MIN(CASE WHEN Part='BODH' AND Number=2 THEN PartSN ELSE NULL END) BODH_SN2,

    MIN(CASE WHEN Part='Cable' AND Number=1 THEN 'Cable' ELSE NULL END) [Cable1],
    MIN(CASE WHEN Part= 'Cable' AND Number=1 THEN PartPN Else NULL END) Cable_PN1,
    MIN(CASE WHEN Part= 'Cable' AND Number=1 THEN PartSN ELSE NULL END) Cable_SN1,
    MIN(CASE WHEN Part='Cable' AND Number=2 THEN 'Cable' ELSE NULL END) [Cable2],
    MIN(CASE WHEN Part= 'Cable' AND Number=2 THEN PartPN Else NULL END) Cable_PN2,
    MIN(CASE WHEN Part= 'Cable' AND Number=2 THEN PartSN ELSE NULL END) Cable_SN2,

    MIN(CASE WHEN Part= 'Pump' AND Number=1 THEN 'Pump' ELSE Null END) [Pump1],
    MIN(CASE WHEN Part= 'Pump' AND Number=1 THEN PartPN ELSE NULL END) Pump_PN1,
    MIN(CASE WHEN Part= 'Pump' AND Number=1 THEN PartSN ELSE NULL END) Pump_SN1,
    MIN(CASE WHEN Part= 'Pump' AND Number=2 THEN 'Pump' ELSE Null END) [Pump2],
    MIN(CASE WHEN Part= 'Pump' AND Number=2 THEN PartPN ELSE NULL END) Pump_PN2,
    MIN(CASE WHEN Part= 'Pump' AND Number=2 THEN PartSN ELSE NULL END) Pump_SN2,

    MIN(CASE WHEN Part= 'MLE' AND Number=1 THEN 'MLE' ELSE NULL END) [MLE1],
    MIN(CASE WHEN Part= 'MLE' AND Number=1 THEN PartPN ELSE NULL END) MLE_PN1,
    MIN(CASE WHEN Part= 'MLE' AND Number=1 THEN PartSN ELSE NULL END) MLE_SN1,
    MIN(CASE WHEN Part= 'MLE' AND Number=2 THEN 'MLE' ELSE NULL END) [MLE2],
    MIN(CASE WHEN Part= 'MLE' AND Number=2 THEN PartPN ELSE NULL END) MLE_PN2,
    MIN(CASE WHEN Part= 'MLE' AND Number=2 THEN PartSN ELSE NULL END) MLE_SN2,

    MIN(CASE WHEN Part= 'AGH' AND Number=1 THEN 'AGH' ELSE NULL END) [AGH1],
    MIN(CASE WHEN Part= 'AGH' AND Number=1 THEN PartPN ELSE NULL END) AGH_PN1,
    MIN(CASE WHEN Part= 'AGH' AND Number=1 THEN PartSN ELSE NULL END) AGH_SN1,
    MIN(CASE WHEN Part= 'AGH' AND Number=2 THEN 'AGH' ELSE NULL END) [AGH2],
    MIN(CASE WHEN Part= 'AGH' AND Number=2 THEN PartPN ELSE NULL END) AGH_PN2,
    MIN(CASE WHEN Part= 'AGH' AND Number=2 THEN PartSN ELSE NULL END) AGH_SN2

FROM CTE
GROUP BY Pull_Date, Well_Name


Backup and truncate LDF with xp_sqlmaint

How can I go about truncating the LDF after the backup?

Don't. A maintenance plan has no business truncating files, LDF nor MDF. There is no point to shrink something only to have it grown back overnite, at (huge) runtime cost. If the file is large, it means it has to be large. Truncating files is just another way of asking for downtime because it gives a false impressions of free disk, where the disk is reclaimed back during normal operations, but potentially can fail (grinding everything to a halt) because the needed space was meanwhile taken by other files.

So just don't.

On special occasions after the file has grown due to a one-time incident, a manual one-time truncation of LDF is OK. But not as part of regular maintenance, that is absolutely not OK.


SSIS Error: To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher

Turns out I didn't have SSIS installed on the inst1 server(s) and that is where the dtexec.exe process was being kicked off. It was not an edition problem, just a missing service problem.


MySQL Cluster - Management Node Redundancy

If you have split exactly in half brain situation MySQL Cluster resolves it designating one node as master. If you look into ndb_mgm output below you will notice asterisk at the end of node 10 line. It marks master.

It means that if your cluster will be split in two equal parts the half which contains master will continue running. Nodes from the other half will be shut down.

ndb_mgm> show
Connected to Management Server at: 10.1.1.10:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=10   @10.1.1.20  (mysql-5.6.24 ndb-7.3.9, Nodegroup: 0, *)
id=11   @10.1.1.21  (mysql-5.6.24 ndb-7.3.9, Nodegroup: 0)


How to determine the size of a SQL Server database for a specific date in the past?

Unless you capture that information on your own SQL Server does not record it anywhere. Most folks go by the backup size.

If you use 3rd party software to backup the server or where ever you store your database backups you might see if it has a history as well. If you have something like Netbackup Enterprise or other similar software it should keep at most the file name and size unless your backup admins purge that information as well, but most don't that I have worked with.


You haven't provided enough details but the problem is likely caused by the ill-defined foreign key. You have a foreign key (1 column) that does not reference the primary key (which is 2 columns):

primary key (Engineer_Name, Team),

foreign key (Boss) references `Team Engineers` (Engineer_Name)

This construction - while it is allowed by MySQL - it is not recommended, even by its documentation.

Solution:

Adjust the foreign key reference so it matches the primary key. I see 2 options here, depending on the business requirements. Can the Boss of an Engineer be on a different Team? Or are they always on the same Team?

If they can be on separate teams, then you need a Boss_Team column, too. Code:

-- added
Boss_Team char(50) null,

-- edited
constraint Team_Engineers_Boss_fk
    foreign key (Boss, Boss_Team)
        references Team_Engineers (Engineer_Name, Team)
        on update cascade

If Engineer and Boss are always on the same Team:

-- edited
constraint Team_Engineers_Boss_fk
    foreign key (Boss, Team)
        references Team_Engineers (Engineer_Name, Team)
        on update cascade

I assume that the PRIMARY KEY cannot be modified. If it is changed (say Engineer_Name becomes the primary key), then the foreign key you have will work correctly.


Grouping by more than 1 column using Partion By or any other method - Sql Server

Just create 2 separate row numbers for change of Type and SubType. I used Id field since it looks like you don't want it to be sorted by Name:

Select
  case row_number() over (partition by T.Id order by T.Id, S.Id, P.Id)
    when 1 then T.Name else '' end as Type,
  case row_number() over (partition by T.Id, S.Id order by T.Id, S.Id, P.Id)
    when 1 then S.Name else '' end as SubType,
  P.Name
FROM
  Product P
  INNER JOIN Type T ON T.Id = P.TypeId
  INNER JOIN SubType S ON S.Id = P.SubTypeId
 order by T.Id, S.Id, P.Id

Example in SQL Fiddle


Should dates in dimensional tables use dimDate?

I would model this by storing both the date value and the SK back to the date dimension. Here is why storing that actual state value allows you to have ms level precision and can be useful when someone wants to see exactly when something happened. however for aggregates the SK can be a computed column if you are worried about the two values going out of sync

Customer
ID               1
Name             John Smith
BirthdateValue   2018-07-04 04:20:69.007
BirthdateSK      20180704

Repeat the pattern as needed for other dates.


Create a Linked-Server from SQL Server 2008 R2 to AWS RDS SQL Server

On SQL Server 2008 R2 the default value of remote login timeout (s) is five (5) seconds, I believe, though it is configurable.

Since you are now trying to connect to an AWS RDS SQL Server, have you considered whether the remote login timeout (s) is appropriate for this environment?

Try raising the timeout a bit. You might start with 10 seconds:

sp_configure 'remote login timeout (s)',10;
RECONFIGURE;

I have had to bump up the timeout seconds in some environments, so do not be afraid to try this.  (It is equally simple to set it back to the previous default.)

You can try to configure a longer timeout, if needed.  So far 10 seconds has almost always worked for my environments, but I have gone to 20 seconds a couple of times.


Is there a way to get an event when there's a change in a table?

Windows Azure SQL Database (WASD) does partially support triggers so you could roll your own auditing.  See here.  Here's a really simple example:

CREATE TABLE dbo.Users (
    userId INT IDENTITY PRIMARY KEY,
    userName VARCHAR(30) NOT NULL UNIQUE,
    dateAdded DATETIME NOT NULL DEFAULT GETDATE(),
    addedBy VARCHAR(30) NOT NULL DEFAULT 'admin',
    dateModified DATETIME NULL,
    modifiedBy VARCHAR(30) NULL,
)
GO

INSERT INTO dbo.Users ( userName )
VALUES ( 'wBob' ), ( 'David' )
GO

CREATE TRIGGER dbo.utrg_Users ON dbo.Users
FOR UPDATE
AS
BEGIN

    -- INSERT INTO ... other table
    SELECT 'old record' source, *
    FROM deleted

END
GO

UPDATE dbo.Users
SET userName = 'wDavid'
WHERE userName = 'David'

WASD has also recently previewed an Audit feature which does include DML auditing.  It's only available for the Basic, Standard and Premium editions and writes off to an audit log stored on an Azure storage account (not a conventional relational table) so you could also have a look at that.  Looks like an interesting feature and includes an Excel template for quickly analysing the logs.  Start here.


Does postgresql pl/lua language have luajit performance?

According to Luis Carvalho, one of the developers of PL/Lua, PL/Lua can use LuaJIT.

However, you may find there are many more factors which affect the overall performance of the PL code you write, including the overhead of the language's bindings in PostgreSQL, data type conversions, familiarity of your developers with Lua vs. other languages and their ability to write performant code, and many more. There's some more discussion about performance of the PLs you may find interesting.

PL/Lua did do well in a benchmark done by Pavel Stehule.


How to convert an integer that was the end result of a bitwise OR to a table of the individual integers that the bitwise OR was applied against?

The answer provided by J.D. works, except in the example the value 1 is not included in the results, even though for 11 the correct bits are 1, 2, 8.

The below example extends the logic from that answer, but uses a recursive CTE instead of a loop to generate the bit values and wraps it in a function that can be called easily to return a table of bits for a given mask. It also doesn't use a table to store the bit values and generates them on the fly.

Code:

CREATE FUNCTION fnGetBitsForMask (@Mask INT)
RETURNS TABLE
AS
RETURN
    WITH BitwiseValues AS
    (
        SELECT CAST(1 AS INT) AS RowNumber, CAST(0 AS INT)  AS BitValue
        UNION ALL
        SELECT b1.RowNumber+1 AS RowNumber, POWER(2, b1.RowNumber-1) AS BitValue
        FROM BitwiseValues b1
        WHERE b1.RowNumber < 30
    )
    SELECT b.BitValue
    FROM BitwiseValues b
    CROSS JOIN (SELECT @Mask AS Mask) m
    WHERE b.BitValue & m.Mask > 0

In use:

SELECT * FROM dbo.fnGetBitsForMask(11)

Result:

BitValue
1
2
8

As a Table-Valued Function, you can use this in SELECT statements and other queries without having to maintain tables or use loops.


In Postgres, How can I merge two tables?

One approach could be to use a writable view to transform results in-query, eg

ALTER TABLE utilities.labpreferences
    RENAME TO utilities.old_labpreferences

CREATE TABLE utilities.labpreferences
( utilities.old_labpreferences EXCLUDING DEFAULTS )
WITHOUT OIDS;

CREATE OR REPLACE RULE "labpreferences_select" AS
  ON SELECT TO utilities.labpreferences
  DO INSTEAD
  SELECT -- fields with modifications --
    FROM utilities.old_labpreferences
    WHERE s.id like 'My.%'
  UNION
  SELECT -- fields without modification --
    FROM utilities.old_labpreferences
    WHERE s.id not like 'My.%'
    ;

Backing the changes out is as simple as removing the rule and new (empty) table, and renaming the old table back.

You will also need rules for update/insert/delete to this table - the postgres documentation for DO INSTEAD is pretty good on this front - see http://www.postgresql.org/docs/devel/static/rules-views.html


How to sync easily between a master database from Google Cloud SQL and a master database from Amazon RDS

Theoretically, just adding replication masters to the two servers should work over the MySQL protocol, and without needing any additional configuration.

You can review the procedures at the below links for details on each side of the process:

AWS - How can I replicate an Amazon RDS for MySQL instance by using binary logs to an on-premises environment?
GCP - Configuring external replicas
GCP - Replicating from an external server


Importing a large column (8000 chars) from Excel using SSIS

We finally resolved the issue.  It turns out that SSIS calculates the length based on the first handful of rows in the excel file.  When we moved the rows with the longer data to the top the columns changed to unicode text (allowing for the extra length).


Sharing keys and certificates between SQL Server 2014 server for encrypted backup and restoration

These links are a good place to start

https://www.mssqltips.com/sqlservertip/3572/recovering-a-sql-server-tde-encrypted-database-successfully/

https://msdn.microsoft.com/en-us/library/ff773063.aspx

To summarize:
On EACH Sql Server Instance on which you want to enable TDE:

1).  In "master', create the database master key to encrypt the certificate that will be used for TDE

USE [master];
GO
CREATE MASTER KEY
  ENCRYPTION BY PASSWORD = 'StrongPassw0rd!';
GO

2). Create the certificate we're going to use for TDE.  Make sure you include the EXPIRY_DATE.  The default is one year from creation date and even though TDE is technically unaffected by an expired certificate, it's still annoying to see the message 'The certificate you just installed has expired' when creating this certificate on another server.

CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Cert for Test',
EXPIRY_DATE = '99991231';;

3). Back up the certificate and its private key to a REALLY safe place.  Remember the password!  Losing this information will prevent you from being able to restore a TDE database,

BACKUP CERTIFICATE TDECert
  TO FILE = N'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'to
  );
GO

4). Switch over to the database you want to apply TDE to and create the Database Encryption Key so you'll be able to encrypt the database.

USE [DatabaseWithTDE];
GO

CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO

5).  Reconnect to master and turn on TDE for the user database

USE [master];
GO

ALTER DATABASE [DatabaseWithTDE]
  SET ENCRYPTION ON;
GO

Monitor the progress of encryption

SELECT db_name(database_id) AS DatabaseName
,CASE
    WHEN encryption_state = 0
        THEN 'No Database Encryption'
    WHEN encryption_state = 1
        THEN 'Unencrypted'
    WHEN encryption_state = 2
        THEN 'Encryption In Progress'
    WHEN encryption_state = 3
        THEN 'Encrypted'
    WHEN encryption_state = 4
        THEN 'Key Change In Progress'
    WHEN encryption_state = 5
        THEN 'Decryption In Progress'
    WHEN encryption_state = 6
        THEN 'Protection Changes In Progress'
    END AS EncryptionState
,percent_complete FROM sys.dm_database_encryption_keys

At this point, you have TDE on your chosen Sql Server instances and database.

For you to be able to restore databases from any other instance, you'll have to 'import' the certificates you created and 'exported' (Step 3 above) on all of the other instances.

So, let's say you're on SERVER1

Note that, when we originally created the certificates on each instance, they were all called TDECert.  When 'importing' the certificates from the OTHER instances, you'll need to give them a unique name (FromServer2, FromServer3, etc.) - although, I'd shy away from using the actual server or instance name as part of the certificate name as that could change in the future as newer servers replace older ones and the certificates get moved around.

--Create the certificate from SERVER2.
CREATE CERTIFICATE TDECert_FromServer2
--The directory where you saved the certificate for SERVER2
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER3.
CREATE CERTIFICATE TDECert_FromServer3
--The directory where you saved the certificate for SERVER3
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER4.
CREATE CERTIFICATE TDECert_FromServer4
--The directory where you saved the certificate for SERVER4
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER5.
CREATE CERTIFICATE TDECert_FromServer5
--The directory where you saved the certificate for SERVER5
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER6.
CREATE CERTIFICATE TDECert_FromServer6
--The directory where you saved the certificate for SERVER6
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

Go into each Sql Server instance and repeat the above 'importing' of the certificates that exist on OTHER instances.  So, for SERVER2, you'd 'import' certificates from SERVER1, SERVER3, SERVER4, SERVER5 and SERVER6

At this point, you 'should' be able to restore any TDE database from the above example to any Sql Server instance in the above example.

Also, if, during a restore, you DO get the message about 'thumbprint doesn't match', you can go into EACH Sql Server instance and SELECT * FROM sys.certificates and look for the 'thumbprint' column to figure out which certificate you're missing.


A column of database type "geometry" results in two extra columns hidden from ssms

use this query instead:
select
t.name as TABLE_NAME,
c.name as COLUMN_NAME,
c.precision as PRECISION,
c.scale as SCALE,
c.max_length as MAX_LENGTH,
c.is_nullable as IS_NULLABLE,
ty.name as DATA_TYPE
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty on c.user_type_id = ty.user_type_id and ty.NAME != 'sysname'
ORDER BY c.column_id

the join is based on the user_type_id so the results are the same as the GUI.


Sybase - How to get logsegments off my data devices?

I was unable to fix the database in place, but I did manage to solve this misconfiguration by:

Creating a new database with my optimal device layout
Migrating the schema via DDL generation scripts using ddlgen.bat
Using bcp to migrate the table data

Note: You could use Sybase Central's DDL generator, but it doesn't include table's indexes for some reason.

When done, I have well documented database schema scripts to place in source control.


How to create and populate a PostgreSQL table from CSV automatically?

You might look at PgFutter to help you out. It sounds like it would fit your use case well, because it creates tables for CSV formatted data based on the header line, and then loads the data into the tables.


Very short answer : No new datatypes have been created to accommodate you.

While we are on this subject

Let's try plain SQL

USE test
DROP TABLE IF EXISTS numtest;
CREATE TABLE numtest
(
  id int not null auto_increment,
  xx decimal(10,3) default null,
  primary key (id)
);
INSERT INTO numtest (id) values (0),(0),(0),(0),(0);
SELECT * FROM numtest;

Does this work ???

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS numtest;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE numtest
    -> (
    ->   id int not null auto_increment,
    ->   xx decimal(10,3) default null,
    ->   primary key (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO numtest (id) values (0),(0),(0),(0),(0);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM numtest;
+----+------+
| id | xx   |
+----+------+
|  1 | NULL |
|  2 | NULL |
|  3 | NULL |
|  4 | NULL |
|  5 | NULL |
+----+------+
5 rows in set (0.00 sec)

mysql>

OK, fine. It works with SQL. You are asking about LOAD DATA INFILE

You brought up a post I answered : MySQL is inserting "" as 0 in decimal fields. How to stop that?

Let's see if that bug was addressed since it was submitted. I will try to duplicate the code in that bug that did not work.

First let's create that table from the bug report

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE bug_repeat
    -> (
    ->   name varchar(10),
    ->   price decimal(12,6)
    -> )
    -> ENGINE=MYISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE bug_repeat\G
*************************** 1. row ***************************
       Table: bug_repeat
Create Table: CREATE TABLE `bug_repeat` (
  `name` varchar(10) COLLATE ascii_bin DEFAULT NULL,
  `price` decimal(12,6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin
1 row in set (0.00 sec)

mysql>

Next, let's make some data

C:\>type C:\MySQLDBA\bug_test.txt
name,
name,0
,
name,6
name,2
name,
name,0
name,0
name,
name,0

C:\>

Let's run the LOAD DATA INFILE

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
    -> into table test.bug_repeat
    -> fields terminated by ','
    -> lines terminated by '\n';
Query OK, 10 rows affected, 4 warnings (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 4

Ouch, what happened

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 1lue: '
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 3lue: '
*************************** 3. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 6lue: '
*************************** 4. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 9lue: '
4 rows in set (0.00 sec)

mysql> select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name | 0.000000 |
| name | 0.000000 |
|      | 0.000000 |
| name | 6.000000 |
| name | 2.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)

mysql>

What's the sql_mode ?

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql>

Let's blank out the sql_mode, truncate the table and reload

mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.00 sec)

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
    -> into table test.bug_repeat
    -> fields terminated by ','
    -> lines terminated by '\n';
Query OK, 10 rows affected, 4 warnings (0.02 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 4

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 1lue: '
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 3lue: '
*************************** 3. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 6lue: '
*************************** 4. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 9lue: '
4 rows in set (0.00 sec)

mysql>

Let doctor the input file with \N like the bug report had

C:\>type C:\MySQLDBA\bug_test.txt
name,\N
name,0
\N,\N
name,6
name,2
name,\N
name,0
name,0
name,\N
name,0

C:\>

Let's repeat all of this with InnoDB

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE bug_repeat
    -> (
    ->   name varchar(10),
    ->   price decimal(12,6)
    -> )
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.05 sec)

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
    -> into table test.bug_repeat
    -> fields terminated by ','
    -> lines terminated by '\n';
Query OK, 10 rows affected, 4 warnings (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 4

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 1lue: 'N
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 3lue: 'N
*************************** 3. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 6lue: 'N
*************************** 4. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 9lue: 'N
4 rows in set (0.00 sec)

mysql> select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name | 0.000000 |
| name | 0.000000 |
| NULL | 0.000000 |
| name | 6.000000 |
| name | 2.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)

mysql>

What version of MySQL am I using ???

mysql> show global variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.22                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql>

What about Linux ???

$ cat /tmp/bug_test.txt
name,\N
name,0
\N,\N
name,6
name,2
name,\N
name,0
name,0
name,\N
name,0

$

Logging in to mysql and trying ...

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE bug_repeat
    -> (
    ->   name varchar(10),
    ->   price decimal(12,6)
    -> )
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.04 sec)

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
    -> into table test.bug_repeat
    -> fields terminated by ','
    -> lines terminated by '\n';
ERROR 2 (HY000): File 'C:/MySQLDBA/bug_test.txt' not found (Errcode: 2 - No such file or directory)
mysql> show warnings\G
Empty set (0.00 sec)

mysql> select * from bug_repeat;
Empty set (0.00 sec)

mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.04 sec)

mysql> load data local infile '/tmp/bug_test.txt'
    -> into table test.bug_repeat
    -> fields terminated by ','
    -> lines terminated by '\n';
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

mysql> show warnings\G
Empty set (0.00 sec)

mysql> select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name |     NULL |
| name | 0.000000 |
| NULL |     NULL |
| name | 6.000000 |
| name | 2.000000 |
| name |     NULL |
| name | 0.000000 |
| name | 0.000000 |
| name |     NULL |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)

mysql> show global variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.21-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql>

Today's date ???

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-25 18:48:10 |
+---------------------+
1 row in set (0.01 sec)

mysql>

It's been a year and one week since that bug report was submitted and nothing has changed.

My answer to MySQL is inserting "" as 0 in decimal fields. How to stop that? still stands as of today.

You need to do this test against MySQL 5.6.23 and see if something has changed.


User has been denied select to table, but has access to table via synonym

This is because of the "ownership chaining" feature in SQL Server. It means that if one object (in your case a synonym) is calling another object (in your case a table), then it checks if the owner of the objects is the same. If the owner of both objects is the same then the access on the called objects (so your table) is not checked.
This feature is why for example users can have access to a view without having access to the underlying tables.
Normally this ownership chaining is not enabled cross-database by default. So check if it is in your case. It can be granted on database level:
SELECT is_db_chaining_on, name FROM sys.databases;

Or it can be granted on server level for all databases:
EXECUTE sp_configure 'show advanced', 1;
RECONFIGURE;
EXECUTE sp_configure 'cross db ownership chaining';


First, take a long, careful look at Aggressive Autovacuum on PostgreSQL as it may very well describe your problem, and check pg_locks to see if there is a locking issue on the table (there may well be one) and also look carefully at Josh Berkus's recommended autovacuum settings for aggressively autovacuuming.

To recover from a bloated table you can run the cluster command from psql (exclusively locks a table, but much faster than vacuum full).

Third, if this is a locking issue, you may try to use a cron job to run a vacuum or even vacuum full (or cluster) during low-utilization times.


Is forcing T-SQL query to use parallelism OK?

It has one big downside that is stated at the end of that post:

  Even experts with decades of SQL Server experience and detailed
  internal knowledge will want to be careful with this trace flag.  I
  cannot recommend you use it directly in production unless advised by
  Microsoft

Interesting to use when testing but not really for production.


Create a log file for check which time import has been done and to split the import files

Create a log table for your use such as:

CREATE TABLE dbo.ImportLogging
(ImportID INT IDENTITY,
 ImportFileName NVARCHAR(200),
 ImportBeginTime DATETIME,
 ImportEndTime DATETIME);

That assumes that you want to save the file name, begin time, and end time. That is up to you, of course. I would log the ImportBeginTime at the start of the import, then add the ImportEndTime at the end.  That way you will have some tracking even if the import fails.

And, assuming that you are importing from a file, I would actually recommend using BULK INSERT because you can set the batch size and let BULK INSERT handle the issue for you. Much better than writing it yourself.  E.g.

BULK INSERT dbname.schemaname.tablename
FROM 'G:\Import\Importfile.txt'
WITH (BATCHSIZE = 1000);

This will cause the BULK INSERT to process 1000 rows per transaction, commit the transaction, then start the next transaction until the file is processed.  Of course, you can change the 1000 to any number you choose.

If you are importing data from another database (either on the same server or across a linked server) then you may need to write some looping code to insert 1000 rows (or 1 row, if that is what you want) at a time.


SQL Server Clustering for HA combined with Log Shipping for DR

In general, log shipping works same way whether SQL server is clustered or not. Only thing you need to use windowsclustername\Share when you copy your logs instead of specifying node names e.g. nodel1\share  or node22\share so it works regardless what node your SQL serve is running.


Which index will be used in this scenario?

Index A is better for this query. When all the conditions in the WHERE are equality checks except one that is using a range condition or IN operator on a column, then that last column should be last in the index, after all the columns that have an equality check.

This allows the optimizer to use an index seek to the first row that matches the conditions and then traverse the index until it finds a row that doesn't match it. All the rows in between are a match, too.

So, the best index for this query would be either (to, from, date) (your model A) or (from, to, date).

The model B index has the date first so it is not the best, although it is still a covering index for the query. If this was used, the query plan would be almost the same. An index seek to find the first row that matches the range condition (date > '2016-02-28') and then traverse the index until it finds a row that doesn't match the date < '2016-04-01'. But all the rows in between do not necessarily match the 2 other conditions so they would have to be checked against these conditions and (possibly many of them) rejected.

So while the plans would be similar, the model A plan would have to only go through the part of the index that has all the needed rows and only them, while the model B plan would go through a (possibly much) larger part of the index.

It would also be best to use a 100% safe format for dates (YYYYMMDD).
And if you want the dates in March, you should use an inclusive-exclusive check:

AND flightdate >= '20160301' AND flightdate < '20160401'

Guaranteed to work with date and datetime types. Your current query will include also any row that has '2016-02-28' but a time different to '00:00:00' (can you guarantee that there isn't any?) which I assume you don't want. The inclusive-exclusive method will also work in leap years (reminding that 2016 is a leap year so there was a February 29th date as well which your query will return).

Read also these blog posts by Aaron Bertrand:

What do BETWEEN and the devil have in common?
Bad habits to kick : mis-handling date / range queries


Steps to upgrade MySQL from version 5.0 to version 5.5

Step 01 : Run this script to Dump Everything from MySQL 5.0

cd /root
MYSQL_CONN="-uroot -p..."
SQLSTMT="SELECT schema_name FROM information_schema.schemata WHERE"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
MYSQL_OPTIONS="--skip-column-names -A"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" > /tmp/dblist.txt
DB_OPTIONS="--databases"
for DB in `cat /tmp/dblist.txt` ; do DB_OPTIONS="${DB_OPTIONS} ${DB}"
MYSQLDUMP_OPTIONS="--routines --triggers ${DB_OPTIONS}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''"
SQLSTMT="${SQLSTMT}@''',host,''';') FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" > GetGrants.sql
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} < /root/GetGrants.sql | sed 's/$/;/g' > Grants.sql
rm -f GetGrants.sql

Step 02 : Backup config file

cp /etc/my.cnf /etc/my.cnf.50

Step 03 : Backup /var/lib/mysql

mv /var/lib/mysql /var/lib/mysql50

Step 04 : Uninstall MySQL 5.0

Step 05 : Install MySQL 5.5

Step 06 : service mysql start

Step 07 : Login to mysql

mysql -uroot

Step 08 : At the mysql prompt enter the following:

mysql> source /root/Grants.sql
mysql> source /root/MySQLData.sql

That's it.

Give it a Try !!!

I have suggested this before : MySQL upgrade 5.0.88 to latest


SQL Server 2012 Availability group disk configuration options

I would only use RAID 0 for the logs if you're okay with having reduced disaster recovery capability. You won't be able to make log tail backups in the event of a disk failure, and will only be able to restore to your most recent backup. However, this may be less of an issue if you're running Availability Groups, particularly with synchronous mirroring. You generally won't have any performance overhead for RAID 1 vs. a single non-RAID disk, but you will have better I/O performance with RAID 0 instead of RAID 1 for an equal number of disks.
Be aware that tempdb also has both data files and log files, and those could end up interfering with each other (performance wise) if placed on the same volume. You may want to consider foregoing the spare disk and using it for tempdb logs instead if performance still isn't acceptable. If your C: array is large enough, you might be able to get away with using it for on-disk backups, though you should probably partition it to prevent runaway backups from filling your OS disk.


Convert CreateLSN of DBCC LOGINFO into time

The LSN is incremented each time a modification is made to the database, irrespective of when the modification was made.

sys.fn_cdc_map_lsn_to_time relies on the current database having Change Data Capture enabled, which automatically creates several objects in the cdc schema.  The definition of fn_cdc_map_lsn_to_time shows this:

create function [sys].[fn_cdc_map_lsn_to_time]
(
    @lsn        binary(10)
)
returns datetime
with returns null on null input
as
begin
    declare @lsn_end_time datetime

    select @lsn_end_time = tran_end_time
    from [cdc].[lsn_time_mapping]
    where start_lsn = @lsn

    return @lsn_end_time
end

So, unless the current database has the cdc.lsn_time_mapping object (I'm unsure if this is a view or a table) present, the function will not work.

The notes in the docs page you linked says:

  This function can be used to determine the time that a change was committed based upon the __$start_lsn value returned in the row of change data.

I'm extrapolating that the function actually returns pre-recorded LSN-to-timestamp tuples, instead of actually converting an LSN into a timestamp.

@RemusRusanu has an excellent writeup about LSNs on his blog


What does “conn” mean in mongostat utility?

Ref: https://docs.mongodb.com/manual/reference/program/mongostat/

The total number of open connections, meaning all authenticated connections.  They can be running,  runnable, waiting or in sleeping state.


ORA-01017: invalid username/password;logon denied

I have seen this only once so far, but there is an issue described in the My Oracle Suppport note:

FIPS use causes ORA-01017: Invalid Username/password with Managed ODP.NET (Doc ID 1937500.1)

I will not post the full content of this as it is not public, but here is the essence:

  This bug has been fixed in 12.1.0.2 Managed Provider. Please note that the DB version must be 12.1.0.2 or higher for this fix to work as expected.

  The workaround is to set in the registry
  System\CurrentControlSet\Control\Lsa\FIPSAlgorithmPolicy\Enabled to
  0.

Some public references to this matter:

https://stackoverflow.com/questions/26410951/oracle-manageddataaccess-and-ora-01017-invalid-username-password-logon-denied

https://stackoverflow.com/questions/31785650/ora-01005-error-connecting-with-odp-net

https://community.oracle.com/thread/2557592?start=0&tstart=0


You can use Sql Database Migration Wizard or command line tool sqlpackage.exe for creating .bacpac file for SQL Server 2008.

Download + documentation for SQL Database Migration wizard:

https://sqlazuremw.codeplex.com

This is from Migration Wizard page, which mentions about supporting SQL Server 2008 version. Though in the download links it only starts from SQL Server 2008 R2 but I would give that a try. I have not tested anything below SQL Server 2012.

  SQL Database Migration Wizard (SQLAzureMW) is designed to help you
  migrate your SQL Server 2005/2008/2012/2014 databases to Azure SQL
  Database.

How to use sqlpackage.exe?

https://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx?f=255&MSPPError=-2147217396

There is a step by step guide about using these tools.


CLR Stored Procedure on Azure SQL Managed Instance error on execution: "Assembly in host store has a different signature than assembly in GAC"

One of the requirements / nuances of working with SQLCLR is that any assembly loaded into both the GAC and SQL Server must be the exact same version (i.e. down to the patch level, not just Major.Minor.*). So, your local instances might all be using 4.7.2 (or whatever), but if the Managed Instance is using 4.7, 4.7.1, 4.8, or anything else, then you will get that error. You would even get this error if one of your local instances was on a server running a different version of the .NET Framework than the one that you grabbed the System.Net.Http.DLL from. Of course, if all of these different instances you mentioned are running on the same physical server, then there is only one OS involved anyway, so of course they all work correctly ;-).

You will need to find out what specific version is being used on the Managed Instance and use that in your Azure install script. You shouldn't need to use the same version locally since the reference to a similar version that has the same signatures should work.

Now, how to find the exact Framework version on Managed Instance? The following query seems to be the best way to get that info:

SELECT olm.[name], olm.[file_version]
FROM   sys.dm_os_loaded_modules olm
WHERE  olm.[name] LIKE N'%mscoreei.dll%';

O.P. replied back that the query above returned a version of 4.7:2623.0 on the
Managed Instance, and a version of 4.7:3190.0 on the local (on prem) system.

This is the source of the "Assembly in host store has a different signature than assembly in GAC" error

So, in order for this to work, you need to find that specific version of that DLL and package it up to load into the Managed Instance.

P.S. This issue is one of the downsides to using unsupported .NET Framework libraries. They are going to be in the GAC (usually, right?) and if a Windows update (locally, or whatever they do to the host system of the Managed Instances) updates the version then your SQLCLR project stops working. And, if the new unsupported Framework DLL gets converted to a mixed mode assembly (both managed and unmanaged code), then you won't be able to load the new version into SQL Server and will need to re-code your project to not use that unsupported Framework assembly.

P.P.S. What are you using System.Net.Http for? If it's for web services stuff, then you should just use the System.Net.HttpWebRequest and HttpWebResponse classes. You will have to do a little extra coding to construct and parse the XML of the web request, but those classes exist in a library that's fully supported.

O.P. replied back with: "Correct, web services stuff."

Later, O.P. replied back with: "I've managed to successful deploy and execute a basic SQLCLR
procedure on Managed Instance using just HttpWebRequest and HttpWebResponse,
I think it'll be best to go down that line until Microsoft extend the list of natively supported libraries.."

P.P.P.S. You might want to take a look at SQL# (a SQLCLR library that I wrote), as there is a stored procedure that handles this, based on HttpWebRequest. It handles most (if not all) internally managed HTTP headers, allows for sending custom headers (including userid/password, if necessary), sending POST / GET data, and even some other stuff that most people usually leave out. Just a thought, though for full disclosure, while there is a Free version, INET_GetWebPages is only in the Full (paid for) version.


APEX_LISTENER and APEX_REST_PUBLIC_USER are not being created

That is because you tried installing APEX in the root container, I was able to reproduce the error.

Oracle Express Edition 18c uses the CDB architecture, meaning you have a root container and a pluggable database called XEPDB1.

Whatever you did, you should do it inside the XEPDB1 pluggable database instead of the CDB$ROOT root container. I am not saying it can not work like that, I just would not bother with it.

Understanding the Installation Choices

  Oracle recommends removing Oracle Application Express from the root
  container database for the majority of use cases, except for hosting
  companies or installations where all pluggable databases (PDBs)
  utilize Oracle Application Express and they all need to run the exact
  same release and patch set of Oracle Application Express.

So if you log in with sqlplus / as sysdba, you can change to XEPDB1 like this:

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XEPDB1                         READ WRITE NO
SQL> alter session set container=XEPDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
XEPDB1

To fix what you have, just log in with sqlplus / as sysdba and remove everything with apxremov.sql. Then log in like above, into the XEPDB1 pluggable database, and try again installing APEX. You can use these steps:

Downloading and Installing Oracle Application Express


Single-column junction table - does it make sense?

In my mind I would separate the logical and physical design. Logically there is a entity type called StoreGroup. Physically, this entity type is not implemented as a table. This is OK. Often the physical design does not include all aspect of the logical design due to time constraints, priorities, performance or other reasons. Foreign key constraints, and the corresponding run-time checks, is a typical example of this. You've discovered another.

There is a cost, too. As you say, foreign keys cannot be enforced in the DB. Perhaps your application has other mechanisms to ensure consistency? As is so often the case there is a trade-off and the designer must make a judgement call.


Changing the SQL Server service account - any additional considerations?

Your best bet before you change the service account would be to manually make sure all the permissions are correct.  This will allow you to also document what changes you make so in the future if you need to move the instance to a new server or set up another instance you will all ready have every thing you need.

This is also my preferred method of doing things since I have a slight mistrust of the built in GUI tools for SQL Server and prefer the command line or Windows folder management GUI for security modifications. (too many "Surprises" in the way SSMS does things"

Once you are ready to change the service account, the Sql Server Configuration Manager is the only way to go.  There are some other changes that the manager makes to the system besides just changing the account every thing runs under.


UPDATE not working properly with linked server

The fundamental issue is: Which server processes the query?

When using a linked server query, the query is executed on the LOCAL server and uses the linked server to access data on the remote server. (Linked servers are subject to several limitations, some caused by running through the Distributed Transaction Coordinator (DTC).)

When using OPENQUERY, the query is sent to the REMOTE server and is executed there in order to return data to the local server.  (You will notice that in OPENQUERY the query uses a three-part name, since it all runs on the remote server, rather than the four-part name used for the linked server.)

A simple exposure to the limitations can be found in this thread: http://www.sqlservercentral.com/Forums/Topic948316-338-1.aspx

It mentions network topology, data types, type of remote server, the code in the query, etc.

EDIT: See more on the Guidelines for Using Distributed Queries at:
http://technet.microsoft.com/en-us/library/ms175129(v=sql.105).aspx

The subheading Other Guidelines mentions:  "To create the best query plans ..., the query processor must have data distribution statistics from the linked server. ... If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server."

If your connection to the remote server does not have access to those statistics that might cause a poor plan.  Ideally not an inaccurate plan, but the query processor is imperfect.


Web application for Software as a Service - Shared database or Multiple Databases?

This is the old "how to handle multi-tenancy" question for which there is no one right answer. You have covered a good selection of the key points. I can't provide an answer beyond "it depends" but here are a couple of other points for you to consider:

mixing data of different clients ... if there is a bug, its easier for someone to end up accessing some else's data and Come up with a scheme associating each installation [correct] database - the risks here are very similar if the various databases are on or can be accessed by the same hosts, especially from the point of view of a defending against deliberate attacker, so that part of the decision comes down to which collection of potential security concerns you feel you can efficiently design around most effectively. If you feel your SQL skills are lacking that might push you in the multiple database direction due to concerns about large data sizes become inefficient as your client base grows.
For each installation, buy a DB from the hosting company for a SaaS solution you have an inappropriate hosting arrangement if you are paying per database or similar. It sounds like traditional shared web hosting were you should be looking at dedicated machines (virtual or physical) or a PaaS based platform.
Consider carefully how you intend the application to scale. The multiple databases option gives you the freedom to split clients between database servers as your needs grow, but the single database option is easier to manage in a PaaS environment if you design is appropriate, letting the platform provider worry about the physical reality of scaling as you pay for more resources.
Also on the scalability matter, make sure consider your administrative responsibilities: how will you backup the data, restore parts of it if there is an error/accident, or restore all of it in the event of an application/platform wide catastrophe, and what timescales do you want/need to work to in such events?

For more information see https://msdn.microsoft.com/en-us/library/aa479086.aspx, https://en.wikipedia.org/wiki/Multitenancy, and search for similar terms for further discussion. (that first link is from MS, but covers the matter from a general design perspective rather than being specific to MS SQL Server, it has also been around a while but the matters concerned haven't changed as much as you might think the would have in the last decade)


Setup new Window Server Failover SQL Cluster using an existing database

Build a new cluster with an FCI and migrate the database using backup/restore.  If you are not using VMs and need to reuse the existing server, then create a new 1-node cluster with an FCI, migrate the database, then rebuild the existing server, add it to the cluster and expand the FCI onto the second node.


I am trying to loop through my counter to create a dynamic sql query which should finally look like

You need to initialize @FinalHistoricalQuery before the loop and you need to use it in on the left side if the assignment in the loop.

declare @CurrentRow int
set @CurrentRow =0;
declare @RowsToProcess int
declare @FinalHistoricalQuery varchar(5000)

-- Set a start value
set @FinalHistoricalQuery = ''

WHILE @CurrentRow<3
BEGIN
                              -- Use variable here
   SET @FinalHistoricalQuery = @FinalHistoricalQuery + ' select 11'+convert(varchar(20),@CurrentRow) + ' union '
   SET @CurrentRow=@CurrentRow+1
END

SET @FinalHistoricalQuery = left(@FinalHistoricalQuery,len(@FinalHistoricalQuery)-6)

exec (@FinalHistoricalQuery)

Result:

-----------
110
111
112

(3 row(s) affected)


How to partition a very large table with limited db space?

You'll need to create a new table with the same schema, but as a partitioned object.  Optionally you can compress the table to save even more space.  As you are only putting on average one row per page I'm not sure how much space savings you will see.  I'd recommend putting a few thousand rows into the new table then compressing to see if the space savings is worth the CPU overhead.

As for how to move this much data without eating up all your drive space and without bloating your transaction log, that'll need to be done in a loop moving small amounts of data per run.  You'll want to do some data analysis to see how large of a window you can process, but I'll assume that based on the data volumes you need to move the rows one minute at a time.

DECLARE @processFrom as datetime
SELECT @processFrom = min(YourDateColumn)
FROM YourTable

DECLARE TABLE @Rows (...)

WHILE EXISTS (SELECT * FROM YourTable)
BEGIN
     DELETE TOP (10000) FROM YourTable
     OUTPUT DELETED.* INTO @Rows
     WHERE YourDateColumn = @processFrom

     INSERT INTO NewTable
     (...)
     SELECT ...
     FROM @Rows

     DELETE FROM @Rows

     IF @@ROWCOUNT = 0
              SET @processFrom = dateadd(dd, 1, @processFrom)
END

When everything is done and you've verified that all the data is in the new table drop the old table and rename the new table so that it has the old tables name.  This way nothing breaks.  You'll want to script out the permissions on the old table (if there are any) so that you can apply them to the new table.

If there are tables with foreign keys to this table you'll need to drop them before this will work.


EXEC @sql is invalid syntax (you need EXEC(@sql); - those parentheses are important). Nonetheless, I highly recommend:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'ALTER ...';
-----------^ this N is important too
...

EXEC sp_executesql @sql;

Doing this as a habit makes sure that you will be able to use strongly typed parameters when you can.


Incremental Backup. Base backup creation frequency?

Your step 2 in PITR should not be there.  You might want to remove the files from the restored copy of the base backup's pg_xlog.  You don't want to copy them.  (And there really shouldn't be anything to copy/move/remove, as you shouldn't be backing up that directory to start with).

If you only take one base backup and keep it forever (or until you upgrade to the next major version of PostgreSQL), then doing PITR will take longer and longer as you accumulate more archived log files.  This is the driving force in how often to take base backups: how long you are willing to wait for recovery.  Also, the more log files you need to replay during recovery, the more likely it is that one of them is going to somehow get lost or corrupted.

If you do decide not to take frequent base backups (or even if do take them frequently), make sure you test your PITR occasionally onto a test server.  This will both verify all your log files are intact, and give you evidence of how long it will take should you need to do a real recovery.


How to maintain visibility of all new transactions in append-only PostgreSQL DB without scanning the whole table

You should run VACUUM (FREEZE) occasionally. The longer it doesn't run, the more it has to do, and the longer it will take.

To speed up VACUUM, increase maintenance_work_mem.


From your question, it sounds like the restore has been working daily, for some time.  As of today it suddenly stopped working.

Yesterday it restored fine with a restored size of around 160GB today it needs 288GB to restore.

One of two things probably happened (or a combination of both)

The data files (mdf.ndf.) grew a bunch either from lots of new data coming in, or from someone increasing the file size.
The log files grew a bunch, probably because of an auto grow when someone ran a big ugly query.

You will need to look at the source database to find out what is bigger than it was yesterday. Then you will need to figure out why it grew. Then you will need to make a decision with the business on if the source should be shrunk back down, or if the secondary server needs to have more space added to it.


How to import json table into PostgreSQL table?

That's what I would do:

SELECT v.*
FROM t
     ,jsonb_to_recordset(t.j::jsonb) AS v(token text, name text, description text);

Or in a 2-step query:

SELECT vr.*
FROM t
     ,jsonb_array_elements(j::jsonb) AS tt(recs)
     ,jsonb_to_record(tt.recs::jsonb) AS vr(token text, name text, description text);

source @dba.stex

dbfiddle