How to select sub-object with given keys from JSONB? How to extract / get / select a "sub object" from a json(b) object in Postgres?
There seems to be a lot of info that get me almost all the way there but not quite. Lots of stuff about converting to records, filtering and then building a new object out of it. TBH, I'm really surprised that this is not a builtin capability. Perhaps there's a simple way to achieve this by composing builtin fns? What I'm looking for is essentially the equivalent of select-keys; a function (say, jsonb_select_keys) that given this:
SELECT jsonb_select_keys('{"a":42,"b":43,"c":44,"d":97}', '{a,d,e}');

would return this:
{"a":42,"d":97}

Kind of like jsonb_path_query_array but for k-v pairs instead of values.

You can remove given keys with the - operator (the inverse of what you have in mind):

jsonb - text[] → jsonb
Deletes all matching keys or array elements from the left operand.
'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] → {}

But I wouldn't know of a built-in function or operator doing what you ask for in the current Postgres 13.
Remarkably, it's the other way round with  SQL SELECT statements. There you get a positive list with SELECT a,d,e FROM tbl, but cannot simply get "all columns except [a,d,e]" like you can get "all keys except [a,d,e]" from a JSON object with the above operator. I would love to have the complementary feature for each.
Also can't think of an easy way with the SQL/JSON path language (Postgres 12+).
Workaround for jsonb
You can create a simple function like:
CREATE OR REPLACE FUNCTION f_jsonb_select_keys(_js jsonb, _keys text[])
  RETURNS jsonb
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
SELECT jsonb_object_agg(t.key, t.value)
FROM   jsonb_each(_js) t
WHERE  t.key = ANY (_keys);
$func$;

db<>fiddle here
Call:
SELECT f_jsonb_select_keys('{"a":42,"b":43,"c":44,"d":97}', '{a,d,e}');

Returns:
{"a":42,"d":97}

You get NULL for NULL or empty input for either argument (as demonstrated in the fiddle). So I declared the function STRICT (a.k.a. RETURNS NULL ON NULL INPUT), even though the nested function jsonb_object_agg() is not STRICT. That could mess with function inlining, but since this one cannot be inlined anyway (containing an aggregate function), we might as well. See:

Function executes faster without STRICT modifier?
Can declaring function volatility IMMUTABLE harm performance?

IMMUTABLE is only kind of true. Keep reading.
Order of keys?
jsonb does not preserve the order of keys (keys are ordered in deterministic fashion internally), so we need not bother about the order of input rows in the aggregation. Except when there can be duplicates, then the latest copy from the input prevails. The manual:

Because the json type stores an exact copy of the input text, it
will preserve semantically-insignificant white space between tokens,
as well as the order of keys within JSON objects. Also, if a JSON
object within the value contains the same key more than once, all the
key/value pairs are kept. (The processing functions consider the last
value as the operative one.) By contrast, jsonb does not preserve
white space, does not preserve the order of object keys, and does not
keep duplicate object keys. If duplicate keys are specified in the
input, only the last value is kept.

Meaning, if there can be duplicate keys in the input, the order of rows is significant. My function does not explicitly order rows, so the result would not strictly be IMMUTABLE. But the input comes directly from jsonb_each() and there cannot be duplicate keys in jsonb (unlike json). Possible duplicates in the input array of keys are irrelevant for the = ANY construct. So duplicates can never happen. IMMUTABLE after all.
Again, this would prohibit function inlining - if it could be inlined to begin with.
Equivalent for json
For completeness: the same for json instead of jsonb:
The first variant does not try to preserve the given order of keys, and it eliminates possible duplicates in the input array of keys (but not possible duplicates from the json input!):
CREATE OR REPLACE FUNCTION f_json_select_keys(_js json, _keys text[])
  RETURNS json
  LANGUAGE sql STABLE STRICT PARALLEL SAFE AS
$func$
SELECT json_object_agg (t.key, t.value)
FROM   json_each(_js) t
WHERE  key = ANY (_keys);
$func$;

The second variant preserves the given order of keys and keeps all possible duplicates. (If the same key is 2x in the json input, and 3x in the input array of keys, you get it 6x in the result.):
CREATE OR REPLACE FUNCTION f_json_select_keys(_js json, _keys text[])
  RETURNS json
  LANGUAGE sql STABLE STRICT PARALLEL SAFE AS
$func$
SELECT json_object_agg (t.key, t.value ORDER BY ord)
FROM   unnest(_keys) WITH ORDINALITY k(key, ord)
JOIN   json_each(_js) t USING (key);
$func$;

About WITH ORDINALITY:

How to preserve the original order of elements in an unnested array?

I made the json functions STABLE because json_object_agg() is only STABLE as opposed to jsonb_object_agg(), which is IMMUTABLE.

You could use json_build_object ('a', js->'a', 'd', js->'d', 'e', js->'e') but that would include all keys, with a NULL value if not found. Not exactly your request. And you couldn't tell the difference between a missing key and the same key with an actual NULL value.


Note MySQL 8 is has been released for more then a year now
   which supports COUNT(DISTINCT ...) OVER(PARTITION BY ...) syntax..

There are multiple solutions possible,

Yes the solutions are hard, one of the problems is that MySQL does not (jet) support

COUNT(DISTINCT ...) OVER(PARTITION BY ...)

otherwise the solution would have been much more easy.

one of the solutions is.

Query

SELECT
 *
FROM (

  SELECT
   table1.*
  FROM
   table1

  UNION ALL

  SELECT
   table2.*
  FROM
   table2
) AS merged

INNER JOIN (

  SELECT
     total_data.IP
   , COUNT(*) = COUNT(DISTINCT total_data.Name) AS has_name_changes
   , COUNT(*) = COUNT(DISTINCT total_data.Gateway) AS has_gateway_changes
   , COUNT(*) = COUNT(DISTINCT total_data.Version) AS has_version_changes
  FROM (
    SELECT
     table1.*
    FROM
     table1

    UNION ALL

    SELECT
     table2.*
    FROM
     table2
  ) AS total_data
  GROUP BY
     total_data.IP
) AS search_filter
ON
   merged.IP = search_filter.IP
WHERE
   search_filter.has_name_changes = 1
 OR
   search_filter.has_version_changes = 1

Result

| IP  | Name | Gateway | Version | IP  | has_name_changes | has_gateway_changes | has_version_changes |
| --- | ---- | ------- | ------- | --- | ---------------- | ------------------- | ------------------- |
| 2   | B    | 2.0.0   | v2      | 2   | 1                | 0                   | 0                   |
| 2   | F    | 2.0.0   | v2      | 2   | 1                | 0                   | 0                   |
| 4   | D    | 4.0.0   | v4      | 4   | 0                | 0                   | 1                   |
| 4   | D    | 4.0.0   | v10     | 4   | 0                | 0                   | 1                   |

see demo

The other solution which is even harder and only works when you have two records, one in each table.

Query

SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT
   merged.IP
 , SUBSTRING_INDEX(
     SUBSTRING_INDEX(
       SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      )
      , ','
      , 1
    )
    , ','
    , -1
  ) AS Name
 , SUBSTRING_INDEX(
     SUBSTRING_INDEX(
       SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      )
      , ','
      , 2
    )
    , ','
    , -1
  ) AS Gateway
 , SUBSTRING_INDEX(
     SUBSTRING_INDEX(
       SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      )
      , ','
      , 3
    )
    , ','
    , -1
  ) AS Version

,  SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      ) AS merged_record
FROM (
  SELECT
   *
  FROM
   table1
  UNION ALL
  SELECT
   *
  FROM
    table2
) AS merged
CROSS JOIN (
  SELECT
   1 AS number
  UNION
  SELECT
   2 AS number
) AS sql_number_generator
GROUP BY
   merged.IP
 , sql_number_generator.number
HAVING
   MIN(merged.Name) <> MAX(merged.Name)
 OR
   MIN(merged.Version) <> MAX(merged.Version)

Result

| IP  | Name | Gateway | Version | merged_record |
| --- | ---- | ------- | ------- | ------------- |
| 2   | B    | 2.0.0   | v2      | B,2.0.0,v2    |
| 2   | F    | 2.0.0   | v2      | F,2.0.0,v2    |
| 4   | D    | 4.0.0   | v4      | D,4.0.0,v4    |
| 4   | D    | 4.0.0   | v10     | D,4.0.0,v10   |

see demo


SQL Server on Linux: Allocation unit on ext4 I'm testing SQL Server on Linux and now on the following point:
Allocation Unit Size of the partition: I learned at SQL Server on Windows the Allocation Unit Size (sector size) must be 64K because of the pages.

Is this also required for the SQL Server on Linux and if yes is this supported on ext4? At the system requirements on the docs of Microsoft is only the information of which file system but not more details.

Thanks

I learned at SQL Server on Windows the Allocation Unit Size (sector size) must be 64K because of the pages.

It doesn't have to be 64k, in fact you'll generally get the best performance based on your workload and the storage vendor's preferred size. If you don't know either, generally 64k will be used.

  Is this also required for the SQL Server on Linux and if yes is this supported on ext4?

It's not a requirement. If you can do testing ahead of time and figure out the sweet spot for your hardware and workload, then that would be the best. Most of the time, the performance difference is negligible unless there truly is a bad configuration for the hardware or workload.

  At the system requirements on the docs of Microsoft is only the information of which file system but not more details.

That's because it isn't required. You can use whatever you'd like, but if you truly wanted to find the proper value - testing is required.


How to generate create table script without primary and foreign key in Oracle I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well.

SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','schema') FROM DUAL

But this statement also generates all primary and foreign key scripts that belong to table. So, is there any way to not include primary and foreign keys in create table scripts

You could try the following:

set pagesize 0
set long 90000

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);

SELECT DBMS_METADATA.GET_DDL( 'TABLE','table_name','schema') FROM DUAL;

This should result in the DDL without any indexes and foreign keys.

Reference:

docs.oracle.com
dba-oracle.com


How do I set up Azure Sql Server to have multiple instances? We are setting up a new SAAS application on Azure and while we have used Azure before, not at this level of multiple apps, etc.

We plan to have both a web app and a cloud app in both a US and EU data center. They need to hit a common database because requests will go to the closest data center via traffic manager and a lot of the data works off of the customer table. And a customer (company) can have users in both the U.S. and E.U.

Is there a way to set up Azure Sql Server so it has instances in both data centers, and Azure keeps them synchronized? If I understand sharding right, that is not what we need as someone hitting either data center could be requesting any of the data in the DB.

thanks - dave

There is a feature in Preview called SQL Data Sync, which can sync content between an on-prem SQL Server database and an Azure database.  However being in preview means you can't use it in production, and it does look to have been in preview for some time.  Register your interest,  try it out, this lets them know people might want to use the product.

Windows Azure SQL Database does not support replication so otherwise you're looking at a 'roll-your-own' type option at the moment.


Compare only day / month / year portion of a timestamp field I am creating a view for a certain table, where rows have timestamp fields (planned_ts). The view needs to be sorted according to these timestamps, but since the application is only interested by the date and not the time, the result look sometimes random, especially when sorting over multiple fields.

The question I have is, if I have this query (for example) :

SELECT *
  FROM tblFoo
 WHERE ...
 ORDER BY planned_ts ASC NULLS LAST, priority ASC

(NOTE: where priority is an integer field, lower means more higher priority.)

How can I change the order clause, so it does not consider the time, as if every timestamps were at 00:00:00.000? I am aware that I can use functions in the sort clause, but I'm not sure how to do this in PostgreSQL.

Use date_trunc() function..

SELECT
  *
FROM tblFoo
WHERE ...
ORDER BY date_trunc('day', planned_ts) ASC NULLS LAST, priority ASC

Please see the documentation for the functions if necessary.


Teach MySQL cli autocomplete to only list table names, not field names? When starting to write a MySQL query, e.g. describe myt and hitting TAB, MySQL autocomplete will list not just the table names starting with myt (e.g. mytable), but all fields/columns of each matching table. E.g. mytable.id, mytable.name, etc.

This can easily result in Display all 505 possibilities? (y or n), and makes the autocomplete less useful than it could be.

Is there a setting or a trick to let MySQL (or MariaDB) only show the table names, not the field names? Or in other words, to treat the dot in the same way that regular shell/bash autocomplete treats the directory separator?

Interesting question! This is one reason I don't use the autocomplete.

It's called auto-rehash if you want to check out the documentation on it.

But, no, looking at the code, I don't believe you can configure this.


How many bytes does character takes if I created database with utf8 in mysql Suppose if create a database with utf8 character set and created table and inserted data.
Here my doubt is if the character is 1 byte then  it will occupy only 1 byte or it will occupy 3 bytes because of utf8 character set

Depends on a character. Some need one byte, some - four. See http://www.fileformat.info/info/charset/UTF-8/list.htm .
MySQL has two UFT8 character sets:

mysql> SHOW CHARACTER SET like 'UTF8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.01 sec)

If a character is a one byte, it will use one byte in memory and on a disk. If four - then four. Plus the string will use one or two bytes to encode length of the string.
Note, that utf8 character set cannot encode all UTF8 characters.


Any experience with cstore_fdw? It looks like there is a (finally) an open source columnar and compression extension for Postgres.

I wonder if anyone had any experience with it yet?

I've worked with Greenplum for years, but I've been looking desperately for the open source alternative.

you get an impression of the performance looking at the comparison with a real column store:
https://www.monetdb.org/content/citusdb-postgresql-column-store-vs-monetdb-tpc-h-shootout


DB2 ARCHIVE mode - Can all archive logs be deleted after doing the FULL OFFLINE backup? I am interested in one thing, if I understood correctly.
DB2 is in Archive mode (online backups are made and archive logs keep coming which is OK).

If I made offline full backup today and I know I will not need returning to previous days etc. can I be free to delete all archive logs? In case of doing the restore of today's backup I suppose I will not get some error "S000013.log is missing" or something like that?

By my opinion it should be OK but I want to check this with experts?

There are two ways to determine which logs to keep. One you write yourself. You would need to issue a db2 list history backup all for <db name>. This will show you the history file and locate the last backup and the last needed archive log. Anything prior to that can be deleted.

Now you can add in automation.

First step set db2 update db cfg using AUTO_DEL_REC_OBJ ON for the database you are connected to. This will now allow you to use the history file to prune unneeded backups and archive logs. You still will have to use fancy scripting and text parsing, but you can do it. (You can probably also locate a table in DB2 to get at the data. That may be easier to grab the data.) But then you can issue db2 prune history <timestamp> [with force option] and delete to remove unneeded backups. And db2 prune logfile prior to <log file name> to delete unneeded archived logs.

This is great, but why not let DB2 handle it for you all the way? Set db2 update db cfg using AUTO_DEL_REC_OBJ ON for the database in question. Then you can set db2 update db cfg using NUM_DB_BACKUPS <number>. This set the maximum number of backups you wish to retain for the period of REC_HIS_RETENTN days. Now set db2 update db cfg REC_HIS_RETENTN <number> to the number of days. If you set NUM_DB_BACKUPS to 3 and REC_HIS_RETENTN to 2, it will keep 3 backups for 2 days. In other words a backup needs to be at least two days old before it will be consider for removal. Once a backup is older than 2 days, it will check to see if there are more than 3 backups. If this is true, then it will remove the older backups.

Now for the logs.....As long as you set LOGARCHMETH1 to DISK rather than LOGRETAIN, DB2 will also clean up any archive logs that are no longer necessary. It will retain archive logs between each of the saved backups, but delete everything else. Example setting: db2 update db cfg using LOGARCHMETH1 'DISK:/archive/logs'. If you set to LOGRETAIN, then DB2 will not auto clean the logs and you are left to do it yourself.


Auto incrementing a foreign key value the same as the primary key of the parent table in SQL when inserting data? I have the following two connected tables as shown below in the Microsoft SQL Server.

When inserting data from visual studio, the foreign key in the Reservations table (Customer_ID) should be auto incremented and have the same value as the Primary key of the parent table Customer_Info (Customer_ID).

Should i do it via the SQL Statements in Visual Studio or it is possible to be done directly in the Database? How can i do it? Thanks

Visual Studio Windows Forms App Design:

The SQL Statement when button is clicked:

 var query = "insert into Customer_info(Customer_Name,Customer_Phone) " +
            "values('" + name + "','" + phone + "');" +
            " insert into Reservations(RV_DAY,Customer_ID,Table_Arrangement,RV_OT,RV_PW,RV_SIOC) " +
            "values('" + day + "',50,'" + tb + "','" + SR1 + "','" + SR2 + "','" + SR3 + "');";

Visual Studio Exception error:

It sounds like you have a slight misunderstanding of how these relationships works.

For the parent table, Customer_Info, your insert statements will look like this:

INSERT INTO Customer_Info
  (Customer_Name, Customer_Phone)
VALUES
  ('csandreas1', '555-5555');

Notice I didn't include the CustomerID field.  As long as you have this set as an IDENTITY field, it will be auto-incremented in the way you describe.

Now here's an insert for the child table, Reservations:

INSERT INTO Reservations
  (RV_DAY, Customer_ID, Table_Arrangement, RV_OT, RV_PW, RV_SIOC)
VALUES
  ('', 5, '', '', '', '');

Note that I left all the other columns blank but Customer_ID, only because I don't really know what they are.

So, when inserting into the Reservations table, you will need to explicitly indicate which Customer_Info row is associated with that reservation.

SQL Server will validate during the insert process that there is really a row in Customer_Info with an ID of 5.

Regarding your code, you will need to execute the insert into Customer_Info first, and then get the resulting generated CustomerID, and then insert into the Reservation table.

You can get the inserted value using the SCOPE_IDENTITY() T-SQL function.  In your example code above, you would replace "50" with "SCOPE_IDENTITY()" to insert the CustomerID for the just-added customer record.

As a VERY IMPORTANT side note regarding your .NET code, you should never concatenate user input with SQL strings.  Use a SqlCommand object and then add parameters.  There are many examples of this online, check one out here.

Doing what you're doing opens you up to the risk of SQL Injection attacks.


Is there a way that we could backup the database in number of smaller parts or change the backup path? Regards to this question that I had asked a day ago I want to know if there is any way that I could take the database backup in the number of smaller parts.

Or
If there is any way that I could change the database backup path to my local drive.

I am connecting to remote through Microsoft SQL Server Management Studio 2017.

Sure - Have a look at Backup to multiple files for faster and smaller SQL Server files.

Problem

Have you ever wished you could get your backups to run faster? Well there may be a way, by writing to multiple files. Creating SQL Server backups is pretty simple to do; you can use the SQL Server management tools or you can use T-SQL commands to issue the backup. But sometimes with large databases it takes a long time to run and it is difficult to copy this very large file across the network or even to your backup tapes.

Solution

Write your database backup to multiple files. In addition to writing your database backup to one file you have the ability to write to multiple files at the same time and therefore split up the workload. The advantage to doing this is that the backup process can run using multiple threads and therefore finish faster as well as having much smaller files that can be moved across the network or copied to a CD or DVD. Another advantage to writing to multiple files is if you have multiple disk arrays you can write your backup files to different arrays and therefore get better I/O throughput. One thing to note is that to maintain the I/O throughput you should keep the writing of your backup files and the reading of the database file on different disk arrays.

Backup to multiple files using T-SQL

BACKUP DATABASE [Northwind] TO
DISK = 'C:\Northwind_file1.bak',
DISK = 'D:\Northwind_file2.bak',
DISK = 'E:\Northwind_file3.bak',
DISK = 'F:\Northwind_file4.bak'
WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT

In reference to your question

  Or If there is any way that I could change the database backup path to
  my local drive.

It's important to note that when you are running backups against a particular SQL Server instance, the backup drives you use in the BACKUP command are drives known by the server that the SQL Server instance is running on.  Now, you could also use a fileshare (assuming you have the proper permissions to write to it).  As far as writing to your local drive, you might be able to achieve that if you create a fileshare on your computer and use that fileshare in your BACKUP command.

I ran a quick BACKUP on one of our 100+gb play databases using 4 backup files.  It appears that BACKUP certainly tried to divide the data as evenly as possible.

An important reminder is

  All of the files must be available, and all must be referenced in a
  restore session.

Some additional valuable information can be found at Improving the Performance of Backups


Distribution clean up job in transactional replication removed records but not files Distribution clean up job ran without errors according to schedule, but I noticed that the snapshot files were not removed even if when created beyond max_disretention period. Records from msrepl_commands and msrepl_transactions were removed, but the files were not.

immediate_sync = 1
max_disretention = 72 hours

the "immediate sync" option is the problem here. Unless you are regularly adding new subscribers every few hours, then this can be set to false and the snapshots are then cleared out after being applied.

exec sp_changepublication
          @publication=N'<publication_name>',
          @property=N'allow_anonymous',
          @value='false';
go

exec sp_changepublication
          @publication=N'<publication_name>',
          @property=N'immediate_sync',
          @value='false';
go

There is a lot of really good information about this here: http://www.replicationanswers.com/TransactionalOptimisation.asp


Are two indexes needed? Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter.

Which indexes should I use? Should I have two indexes for the field "title" (one index of the length 255 for ordering and one index of the length 1 for indexing by the first letter)?

CREATE TABLE `cyclopedy` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(255) collate utf8_bin NOT NULL,
  `article` mediumtext collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Well, now I understand that length 1 is not enough for indexing by the first letter, because an UTF-8 letter may consist of three bytes.

An index can seek by a subset of characters, as long as you're searching from the left.  E.g., "Inter%" can seek, "%net" will not.

However, the first character is not necessarily the character under which the article would be sorted.  "The Internet" should go under "I", not "T".  You probably need two fields, DisplayTitle and SortTitle; a single-character index on the latter may be worthwhile, but most likely a full-length index will be just fine.

Indexes are typically B-trees, and a seek will jump to the right location about equally quickly whether you have 10 or 100 entries per page.  Scans are another matter, but I'd start with the simplest solution and add an extra index only if performance proves inadequate in practice.


Upgrading SQL Server 2019 from CTP to RTM version A few days ago Microsoft announced RTM version of SQL Server 2019 finally and we are willing to install and use it on our business intelligence server.

We are already using Microsoft SQL Server 2019 Community Technology Preview 3.0 (CTP 3.0) so I gave it a shot to do in-place upgrade. However, Installation wizard detects CTP version and tries to install a new named instance. Obviously we don't want that.

I checked release notes but couldn't find anything about in-place upgrade support from CTP to RTM.

Questions:

Does anyone tried and succeed in-place upgrade from CTP to RTM version?
Is 'Uninstalling CTP and Installing RTM' the only way?

Update

I tried both Version Upgrade and Edition Upgrade using Installer but both didn't help.

It looks like uninstalling CTP components and then installing brand new Enterprise RTM is the only and correct way to do this.

I cannot find anything specific to SQL Server 2019, but I was able to find unofficial references about previous versions of SQL Server not supporting CTP to RTM upgrades.  Additionally, the reference Bahtiyar posted in the comments also shows that upgrades from previous versions of 2019 are not included.  Once the RTM is available, the documentation for the CTP builds is gone, so I'm not sure if this limitation is included in the documentation of the pre-RTM releases.

This makes sense from the standpoint that the preview and release candidate versions are not licensed, supported versions.  Supporting upgrades from these versions would require Microsoft to spend a lot of additional resources in testing those upgrades.  Additionally, this might encourage customers to put a production workload on a CTP or release candidate build thinking they can just upgrade to RTM, so by not supporting that upgrade, they are discouraging customers from putting production workloads on unsupported versions.


Need to Display Query Result without Null Values in one column I created a query which produced four column result, I wanted to display result in which the Outstanding column > 0.  My query below:

SELECT
CAST(xx_master_tickets.CreatedMoment AS DATE) TicketDate,
Outstanding =  COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 1 Then 1 End),
Closed = COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 2 Then 1 End),
Cancelled = COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 3 Then 1 End)
FROM         XX_MASTER_TICKETS INNER JOIN
             XX_DEF_ATM_LIST ON XX_MASTER_TICKETS.MachineID = XX_DEF_Machine_LIST.MachineID INNER JOIN
             XX_DEF_BRANCHES ON XX_DEF_Machine_LIST.BranchID = XX_DEF_BRANCHES.BranchID
WHERE     (XX_MASTER_TICKETS.CreatedMoment BETWEEN '01-01-2014' AND GETDATE()) and (XX_DEF_BRANCHES.BranchName = 'New York')
Group by CAST(xx_master_tickets.CreatedMoment AS DATE)
Order by CAST(xx_master_tickets.CreatedMoment AS DATE)

Result below:

TicketDate  Outstanding Closed  Cancelled
8/24/2014   0            65         1
8/25/2014   1            83         3
8/26/2014   0            77         3
8/27/2014   1            70         0
8/28/2014   4            88         1
8/29/2014   1            35         3
8/30/2014   2            65         2
8/31/2014   7            92         4
9/1/2014    0            77         2
9/2/2014    0            79         1
9/3/2014    39           44         2
9/4/2014    18           17         0

Result i wanted is below:

TicketDate  Outstanding Closed  Cancelled
8/25/2014   1            83         3
8/27/2014   1            70         0
8/28/2014   4            88         1
8/29/2014   1            35         3
8/30/2014   2            65         2
8/31/2014   7            92         4
9/3/2014    39           44         2
9/4/2014    18           17         0

Basically tracking dates with only outstanding values.

with adding

Having (COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 1 Then 1 End) > 0)

Now try the query below

SELECT
CAST(xx_master_tickets.CreatedMoment AS DATE) TicketDate,
Outstanding =  COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 1 Then 1 End),
Closed = COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 2 Then 1 End),
Cancelled = COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 3 Then 1 End)
FROM         XX_MASTER_TICKETS INNER JOIN
             XX_DEF_ATM_LIST ON XX_MASTER_TICKETS.MachineID = XX_DEF_Machine_LIST.MachineID INNER JOIN
             XX_DEF_BRANCHES ON XX_DEF_Machine_LIST.BranchID = XX_DEF_BRANCHES.BranchID
WHERE     (XX_MASTER_TICKETS.CreatedMoment BETWEEN '01-01-2014' AND GETDATE()) and (XX_DEF_BRANCHES.BranchName = 'New York')
Group by CAST(xx_master_tickets.CreatedMoment AS DATE)
Having  (COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 1 Then 1 End) > 0)
Order by CAST(xx_master_tickets.CreatedMoment AS DATE)


Remove Dot at end of line of the string in SQLserver I would to like to remove the dot at end of the line alone if exists in the string in SQL server.

Example:

ABC Private's co., ltd.

Required output:

ABC Private's co., ltd

You can use like to check for a . at the end of the string.

declare @S nvarchar(max) = 'ABC Private''s co., ltd.';

select case when @S like '%.'
         then left(@S, len(@S) - 1)
         else @S
       end;


MySQL read only slave - how to create views After a lot of resistance I have recently been given MySQL access to our CRM system, via a read-only slave.

I have an issue now in that I am really limited to what I can do with the data in MySQL - cannot create views etc. The IT head said I should continue to use Access, with the tables from the slave imported using ODBC connections. I can do this but I'm not sure it's the best plan long-term - I had planned to move my processes into MySQL.

Is there any way around this? Can I somehow link the read-only tables to another MySQL database which I can edit?

Thanks

I have an issue now in that I am really limited to what I can do with
  the data in MySQL - cannot create views etc.

The biggest issue in above is "etc".
Any editing operation on Slave can crash the replication process, and Your head of IT just absolutely correct want avoid regular restore of server.

The possible ways:

create all views on Master. It safe. Views not add additional loading to server and will be automatically replicate to Salve
If You do not need real-time data (it normal practice) - go from replication to nightly restore few important tables. Operation could be automate easily
and as mentioned in Your post - continue use Access. May be not the best, but it very good tools for personal use.
In addition to Access - look for PowerBI (or similar tools) they could make many transformation in memory without creating views

It is possible to create views on Slave without collisions with replication, but ... but feature unpredictable and IT just try to avoid any potentials issues, and they are right.


MySQL - Column value depends on values from two different tables I have a MySQL database that contains these tables (* denotes primary key):

TableA:

----------------------------------------------
|  *id  |  time  |  price  |  special_price  |
----------------------------------------------

TableB:

-------------------------------------
|  *table_a_id  |  *date  |  price  |
-------------------------------------

SpecialDate:

--------------------------
|  *date  |  description |
--------------------------

The value of TableB.price column of each entry in TableB will be either TableA.price or TableA.special_price of the corresponding TableA entry, depending on whether TableB.date is a special date or not (whether TableB.date exists in the SpecialDate table).

This design, as it stands, doesn't feel right as every time I'm adding a record to TableB, I have to look up the date from SpecialDate to see if it's a special date or not, then look up the corresponding entry from TableA to get the value for the price column. And every time a record from SpecialDate gets added or updated, I have to go over every entry in TableB to update its price column.

I'm thinking the column TableB.price should be removed and calculated when needed but I'm not sure how to do that using SQL. I have a lot of views that depend on that price column, for example:

TableAFullView:

-----------------------------------------------------------------------------
|  id  |  time  |  count_of_table_b_instances  |  sum_of_table_b_instances  |
-----------------------------------------------------------------------------

Where count_of_table_b_instances is the number of TableB entries that point to this TableA entry and sum_of_table_b_instances is the sum of their prices.

With my original design, this view is simple to implement and it performs well. But if I get rid of the column TableB.price, how would I implement this view? Will the performance take a hit?

With my original design, this view is simple to implement and it performs well. But if I get rid of the column TableB.price, how would I implement this view

create table TableA (
id int,
v_time date,
price int,
special_price int
);

create table TableB (
table_a_id int,
v_date date
);

create table SpecialDate (
v_date date,
description varchar(5)
);

insert into TableA values (1,'2019-09-01', 10, 5);
insert into TableB values (1,'2019-10-01');
insert into TableB values (1,'2019-11-01');

insert into TableA values (2,'2019-12-01', 50, 1);
insert into TableB values (2,'2019-12-02');
insert into TableB values (2,'2019-12-03');
insert into TableB values (2,'2019-11-01');

insert into SpecialDate values('2019-11-01', 'sale');

--your view definition

select a.id,
   a.v_time,
   count(*) as count_of_table_b_instances,
   sum(
      case
     when s.v_date is not null then a.special_price
     else a.price
     end
   ) as sum_of_table_b
 from TableA a
 join TableB b
   on a.id = b.table_a_id
 left join SpecialDate s
  on b.v_date = s.v_date
 group by a.id,
      a.v_time

  Will the performance take a hit?

I can't say for sure because I can't assume you have the right indexes setup to support this query without seeing the DDL of the tables. If you have indexes in place to support the join clauses, or if you don't have much data in these tables, I don't think you would take a performance hit.


How do I delete rows from a table on a linked server I need to delete rows from a table in several databases on several servers with the same schema. OPENROWSET() does not seem to be the answer.

If I do this:

SET @sqlStr = '
    SELECT TOP 0 * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Server + ';Trusted_Connection=yes;'',
        ''DELETE FROM [' + @DBName + '].dbo.tblName WHERE foo='bar') AS a';
EXEC (@sqlStr);

I get the error

Msg 11525, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'DELETE FROM  . . .

If I add SELECT 1; to the query so a resultset gets returned like i do below, I get one row one column result (the value of which is BTW not '1'). However, there delete operation does not occur.

SET @sqlStr = '
    SELECT TOP 0 * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Server + ';Trusted_Connection=yes;'',
        ''SELECT 1; DELETE FROM [' + @DBName + '].dbo.tblName WHERE foo='bar') AS a';
EXEC (@sqlStr);

What do I need to make the delete happen on the linked server?

Why not just:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'DELETE [' + @DBName + '].dbo.tblname WHERE foo = ''bar'';';
EXEC linkedserver...sp_executesql @sql;


AG of 2 replicas on 2 nodes in WSFC : is it possible to add 3rd replica to AG and NOT add 3rd machine WSFC? Please help to to research the possibility of below:

We have availability group that consists of two replicas:

replica 1: SQL Server 2017 on Windows Server 2016
replica 2: SQL Server 2017 on Windows Server 2016

Both machines are part of one Windows Server Failover Cluster

Is it possible to add a 3rd replica (SQL Server 2017 on Windows Server 2016) to existing availability group, without adding a 3rd machine to existing WSFC ?

Is it possible to add a 3rd replica (SQL Server 2017 on Windows Server 2016) to existing availability group, without adding a 3rd machine to existing WSFC ?

No, there are explicit checks to see if the node of an AG is already hosting the same AG. If you want a 3rd replica, add a 3rd note.

I'd also advise against stacking up on multiple instances of SQL Server on the same machine, simply for administrative and troubleshooting purposes.


When do i need to DEALLOCATE a prepared statement in a MySQL stored procedure? My question is simple. In what situations is it better to deallocate the prepared statement and thus removing it from memory and in what situations is it better to keep it in memory?

I have written a stored procedure that uses 2 prepared statements. One of the first few lines is to prepare the statement like so:

PREPARE selectStatementByJournalEntryAndLanguageQuery FROM "INSERT INTO results(id, content, title, language_id, journalentry_id) SELECT * FROM JournalEntryTitleAndContent jetc WHERE jetc.language_id = ? AND jetc.journalentry_id = ? LIMIT 1";
PREPARE selectStatementByJournalEntryQuery FROM "INSERT INTO results(id, content, title, language_id, journalentry_id) SELECT * FROM JournalEntryTitleAndContent jetc WHERE jetc.journalentry_id = ? LIMIT 1";

Now this stored procedure and thus these prepared statements could be used many times a day by many users. In this case it is better to not call DEALLOCATE on both prepared statements right? Because then the prepared statements remain in memory and do not have to be recompiled when someone else uses the stored procedure. But that raises a question about the 2 PREPARE statement lines. If i do not deallocate the prepared statements at the end and someone else starts the stored procedure, will the database then try to PREPARE the prepared statements again or will it ignore these 2 code lines when it detects that those prepared statements have already been prepared during another database stored procedure query session.

Thank you

[https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html] says:

  A prepared statement is specific to the session in which it was
  created. If you terminate a session without deallocating a previously
  prepared statement, the server deallocates it automatically.

  A prepared statement is also global to the session. If you create a
  prepared statement within a stored routine, it is not deallocated when
  the stored routine ends.

  To guard against too many prepared statements being created
  simultaneously, set the max_prepared_stmt_count system variable. To
  prevent the use of prepared statements, set the value to 0.

I would note run DEALLOCATE unless I have a large number of prepared statements close to the maximum (by default 16382 in MySQL 8.0).


Using the special Dual table to read sequences It there a performance impact when using the dual table to read a sequence' nextval over an direct insert?

I'm currently optimizing the performance in a large IT system and what I've found is that almost all sequence.nextval are read in the following way:

SELECT SOME_SQ.NEXTVAL INTO someID FROM DUAL;
insert into .... (someId)

I'm wondering whether the performance might suffer when using the way as described above over a standard:

insert into .... (some_sq.nexval, ...)

Thanks

Note that 11g has support for sequence expressions directly in PL/SQL

Otherwise, from the Oracle Docs:

  Beginning with Oracle Database 10g Release 1, logical I/O is not
  performed on the DUAL table when computing an expression that does not
  include the DUMMY column. This optimization is listed as FAST DUAL in
  the execution plan.

So any performance gain will be slight. In the following tests I dropped and recreated the table and sequence between each execution:

testbed:

create sequence s;
create table t(id integer);

tests:

declare
  n integer;
begin
  for i in 1..100000 loop
    select s.nextval into n from dual;
    insert into t(id) values(n);
  end loop;
end;
/
--average about 5s

declare
  n integer;
begin
  for i in 1..100000 loop
    insert into t(id) values(s.nextval) returning id into n;
  end loop;
end;
/
--average about 4s

begin
  for i in 1..100000 loop
    insert into t(id) values(s.nextval);
  end loop;
end;
/
--average about 3s

You should probably rerun these tests or ones like them in you own environment.


SSRS is missing after failover to other node We have 2 node setup cluster, after a failover I can't see the reporting services, even the service it self is missing.

I checked control panel program manager, SSRS is not installed, I open reporting service configuration manager when clicking Find, it can't find the server. Is there a way to retrieve that.

There are active reports already running before the failover.

Sorry I'm new in SSRS, I researched the web and can't find any solution.

Reporting services is not cluster-aware. If you want high-availability you need to set up a scale-out deployment where multiple Report servers share the same back-end database.

From the documentation: High availability in SQL Server Reporting Services

  A scale-out deployment provides a way to share a database. If one
  report server goes down, other servers in the same deployment will
  continue to work.

  Reporting Services is not cluster-aware. By itself, a scale-out
  deployment does not provide load balancing

So you would have needed to install SSRS on more than one machine and set it up like that before the failover happened.


UPDATE id table on the columns with the same name I may be a newbie question but I don't know how to resolve this. I have a table like:

name | id       | value
A      1286487    1286333
B      1286489    1286403
C      1286495    1286455
C      1286496    1286375
D      1286503    1286341
B      1286506    1286343

And I want to update this table to make it like this:

name | id       | value
A      1286487    1286333
B      1286489    1286403
C      1286495    1286455
C      1286495    1286375
D      1286503    1286341
B      1286489    1286343

So rows with name B and C have the same id like the first row with this name. Can anyone help me with that?

To fulfil the requirements, we have to first find the ID that we need - this can be found using the min() function, not surprisingly.  Then we do the update - the quickest to write is possibly the following:

UPDATE minupdate m
   SET id = (SELECT min(id)
               FROM minupdate
              WHERE name = m.name);

The backside of this is it can be very slow, if you have a bigger number of rows.  I have populated the table with some 50,000 random rows, using the following statement:

INSERT INTO minupdate (name, id)
SELECT translate(substr(i::text, 1, 1), '1234567890', 'ABCDEFGHIJ'),
       random() * 100000
FROM generate_series(1, 50000) t(i);

On my test box, without an index, this took looong minutes - I did not wait for it to produce an actual execution plan (using EXPLAIN (ANAYLZE, BUFFERS), but cancelled and run it with EXPLAIN to see what's wrong:

 Update on minupdate m  (cost=0.00..136044281.50 rows=87720 width=12)
   ->  Seq Scan on minupdate m  (cost=0.00..136044281.50 rows=87720 width=12)
         SubPlan 1
           ->  Aggregate  (cost=1550.87..1550.88 rows=1 width=4)
                 ->  Seq Scan on minupdate  (cost=0.00..1526.50 rows=9747 width=4)
                       Filter: (name = m.name)

So I've added an index in (name, id) to get some faster results:

 Update on minupdate m  (cost=0.00..417486.00 rows=50000 width=15) (actual time=1771.008..1771.008 rows=0 loops=1)
   Buffers: shared hit=394845 read=653 dirtied=654
   ->  Seq Scan on minupdate m  (cost=0.00..417486.00 rows=50000 width=15) (actual time=0.062..1317.366 rows=50000 loops=1)
         Buffers: shared hit=172641 read=192
         SubPlan 2
           ->  Result  (cost=8.31..8.32 rows=1 width=0) (actual time=0.018..0.020 rows=1 loops=50000)
                 Buffers: shared hit=171655 read=192
                 InitPlan 1 (returns $1)
                   ->  Limit  (cost=0.29..8.31 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=50000)
                         Buffers: shared hit=171655 read=192
                         ->  Index Only Scan using minupdate_name_id_idx on minupdate  (cost=0.29..8.31 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=50000)
                               Index Cond: ((name = m.name) AND (id IS NOT NULL))
                               Heap Fetches: 50000
                               Buffers: shared hit=171655 read=192

Now the problem is this: the inner part of the plan is executed 50,000 times - the number of rows to be updated.  In the index-less version, it would scan the whole table this may times - no wonder it would take very long.

But there is more to it.  Those 50,000 loops are ugly - let's find something nicer:

EXPLAIN (ANALYZE, BUFFERS)
UPDATE minupdate m
   SET id = mins.id
  FROM (SELECT name, min(id) AS id
          FROM minupdate
         GROUP BY name
       ) mins
WHERE m.name = mins.name;

This gives a more streamlined plan:

 Update on minupdate m  (cost=1861.76..4525.59 rows=91698 width=42) (actual time=995.845..995.845 rows=0 loops=1)
   Buffers: shared hit=241809 read=394 dirtied=843
   ->  Hash Join  (cost=1861.76..4525.59 rows=91698 width=42) (actual time=218.866..542.437 rows=50000 loops=1)
         Hash Cond: (m.name = mins.name)
         Buffers: shared hit=972 dirtied=427
         ->  Seq Scan on minupdate m  (cost=0.00..1402.98 rows=91698 width=8) (actual time=0.027..106.868 rows=50000 loops=1)
               Buffers: shared hit=486 dirtied=1
         ->  Hash  (cost=1861.65..1861.65 rows=9 width=36) (actual time=218.816..218.816 rows=9 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               Buffers: shared hit=486 dirtied=426
               ->  Subquery Scan on mins  (cost=1861.47..1861.65 rows=9 width=36) (actual time=218.738..218.790 rows=9 loops=1)
                     Buffers: shared hit=486 dirtied=426
                     ->  HashAggregate  (cost=1861.47..1861.56 rows=9 width=6) (actual time=218.722..218.740 rows=9 loops=1)
                           Group Key: minupdate.name
                           Buffers: shared hit=486 dirtied=426
                           ->  Seq Scan on minupdate  (cost=0.00..1402.98 rows=91698 width=6) (actual time=0.007..111.580 rows=50000 loops=1)
                                 Buffers: shared hit=486 dirtied=426
 Planning time: 0.164 ms
 Execution time: 995.908 ms

There are now two sequential scan nodes there (somewhat surprisingly), but both are executed only once (loops=1).

What I've done here is removing a correlated subquery, replacing it by a temporary view (the query after FROM), joining it to the table to be updated.  This approach often gives far better plans than the easy way.  On big tables the performance difference can be quite significant.


Why do roles and schemas have (mostly) the same name? When I create a database and add a user I get an interface with options. On that interface I can choose:

Owned Schemas
Membership

This question on dba.stackexchange HERE says schemas and roles are not related. This has confused me in the past since they have the same names. Why is this?

Also. In the Schemas folder, I see dbo, INFORMATION_SCHEMA, and sys. But in the user dialogue, under 'Owned Schemas' I don't see these options. Why is this?

This has confused me in the past since they have the same names. Why is this?

I think this is simply becuase of convention, it is easier to identify the owner of the schema.

Defualt schemas dbo, INFORMATION_SCHEMA, sys schemas are belongs to dbo, INFORMATION_SCHEMA, sys users respectively and these are inherited from model database.

INFORMATION_SCHEMA and sys are used to store system objects so that we can't select to store user-defined objects. dbo is the default schema so we don't need to select it.

Documentation


Split values from field and count them according to another field I have following MySQL db structure:

content
=======
id | tags
---------
1  | ,abc,def,ghi,
2  | ,abc,foo,
3  | NULL
4  | ,foo,bar,

categories
==========
id
--
1
2

categories_to_content
=====================
id | category_id | content_id
-----------------------------
 1 |           1 |          1
 2 |           1 |          2
 3 |           2 |          3
 4 |           2 |          4

What I'm trying to do is to SELECT tags assigned to category and count them. Result for given schema:

tag | category | count
----------------------
abc |        1 |     2
def |        1 |     1
ghi |        1 |     1
foo |        1 |     1
foo |        2 |     1
bar |        2 |     1

Decomposition isn't possible (I hook up into existing CMS database).

My existing query:

SELECT tags, category_id as category FROM content
JOIN categories_to_content ON content_id = content.id

You can achieve your goal by using a tally (number) table, which you can easily create in the following way

CREATE TABLE tally(n INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO tally (n)
SELECT NULL
 FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
;

  This will create a tally table and fill it with 100 rows with values
  1 to 100 accordingly.

You should adjust the number of rows in your table to be equal or more (e.g. double the number to be on the safe side) than a maximum number of delimiters (comma) in content table.
You can figure out the maximum number with a simple query

SELECT MAX(CHAR_LENGTH(tags) - CHAR_LENGTH(REPLACE(tags, ',', ''))) max_tags
  FROM content;

Output:

| MAX_TAGS |
|----------|
|        4 |

Now you can use it to split tags

SELECT category_id, tag, COUNT(*) count
  FROM
(
  SELECT cc.category_id,
         SUBSTRING_INDEX(SUBSTRING_INDEX(c.tags, ',', n.n), ',', -1) tag
    FROM categories_to_content cc JOIN content c
      ON cc.content_id = c.id CROSS JOIN tally n
   WHERE n.n <= 1 + (CHAR_LENGTH(c.tags) - CHAR_LENGTH(REPLACE(c.tags, ',', '')))
) q
 WHERE CHAR_LENGTH(tag) > 0
 GROUP BY category_id, tag

Output:

| CATEGORY_ID | TAG | COUNT |
|-------------|-----|-------|
|           1 | abc |     2 |
|           1 | def |     1 |
|           1 | foo |     1 |
|           1 | ghi |     1 |
|           2 | bar |     1 |
|           2 | foo |     1 |

Here is SQLFiddle demo


How to get the best time from submited values? I have a MySQL table, and i'm trying to figure how can i get the first result for each user.

For example:

Email               Time    DateSubmission
usera@email.com   | 0:14  | 28/2/2019 00:10
usera@email.com   | 0:10  | 28/2/2019 00:08
usera@email.com   | 0:10  | 29/2/2019 00:10
userb@email.com   | 0:10  | 28/2/2019 00:04
userb@email.com   | 0:11  | 28/2/2019 00:17
userc@email.com   | 0:11  | 28/2/2019 00:18

I've tried to use distinct, order by's but can't find how to get results like:

Email               Time    DateSubmission
userb@email.com   | 0:10  | 28/2/2019 00:04
usera@email.com   | 0:10  | 28/2/2019 00:08
userc@email.com   | 0:11  | 28/2/2019 00:18

Can anyone help?

You should be able to use CTE (common table expression) along with the ROW_NUMBER() function to select the top 1 for each email.

See here for a short primer on ROW_NUMBER().
And here for CTE.

Something like:

WITH CTE AS (
SELECT ROW_NUMBER() OVER (PARTITION BY email ORDER BY email, datesubmission) R, *
FROM MyTable
)
SELECT * FROM CTE
WHERE R = 1
ORDER BY datesubmission, email


How can I verify that MySQL's slave_compressed_protocol is working I've set slave_compressed_protocol=1 on both my master and slave.

Using SHOW VARIABLES, I can see that it's set.

How can I check that it's actually working?

Connection Phase of the Client/Server Protocol

At the Client Response, there is crosscheck of capabilities between the client and the server. The CLIENT_COMPRESS flag (value 0x00000020) needs to be mutually exchanged before continuing authentication.
To be honest with you, I have never heard of a MySQL question this deep. Good the thing all of this appears in the MySQL Internals Documentation.
How on earth would you actually know where to intercept the CLIENT_COMPRESS ?
I would suggest using tcpdump or snort to inspect packets.
I will leave it to you to read up on the needed exchange packets

Protocol::Handshake
Protocol::HandshakeResponse


How far should bind variables go in Oracle? I am currently examining all database users's SQL code in our department for not using bind variables. We do experience an ORA-04031 error within a constant period of time.

Now, I have a quite firm understanding where to use them but I am not sure how far this should go. Now, do I have to replace all literals with bind vars?

For instance:

select 1 from t1 ; => select :one from table t;

or

select c1, c2, from t2 where id = :id and status = 2 =>
select c1, c2, from t2 where id = :id and status = :two

or

select * from t3 where c3 > :value and exists (select 1 from t4 where <some condition>) =>
select * from t3 where c3 > :value and exists (select :one from t4 where <some condition>)

Same applies to DML.

My intuition says that everything has to be a bind variable to achieve optimal performance.

Use a bind variable anywhere that you might actually change the value that is passed in.

It probably doesn't make sense, for example, to use a bind variable in the EXISTS clause in your third example because it seems extremely unlikely that anyone would ever want to pass in a different constant for the EXISTS clause.

In your second example, it probably does make sense to use a bind variable rather than a hard-coded status assuming that the application(s) are likely to want to run this query for different id and status values.  On the other hand, if the code would only ever pass in a status of 2 for this query, it probably doesn't make sense to use a bind variable.  Assuming that status is not evenly distributed, you'd be better off giving the optimizer the information that you're always going to use a status of 2 rather than trying to rely on bind variable peeking to give the optimizer that information.

If the value is never going to change-- i.e. if the status in your second query will always be 2-- you can execute the query as many times as you'd like and it will only appear once in v$sql or v$sqlarea.  In my case, I'll execute the query 10 times

SQL> ed
Wrote file afiedt.buf

  1* select * from v$sql where sql_text like '%jc_bind_test%'
SQL> select /* jc_literal_test */ count(*)
  2    from emp
  3   where ename = 'KING';

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

And from v$sql or v$sqlarea, you'll see only one row for the query that has the hard-coded literal.  That row will show that the query was executed 10 times

SQL> ed
Wrote file afiedt.buf

  1* select sql_text, executions, loaded_versions, parse_calls from v$sqlarea where sql_text like '%jc_literal_test%'
SQL> /

SQL_TEXT                                 EXECUTIONS LOADED_VERSIONS PARSE_CALLS
---------------------------------------- ---------- --------------- -----------
select * from v$sql where sql_text like           1               1           1
'%jc_literal_test%'

select sql_text, executions, loaded_vers          5               1           5
ions, parse_calls from v$sql where sql_t
ext like '%jc_literal_test%'

select sql_text, executions, loaded_vers          1               1           1
ions, parse_calls from v$sqlarea where s
ql_text like '%jc_literal_test%'

select /* jc_literal_test */ count(*)            10               1          10
from emp  where ename = 'KING'


PostgreSQL: relation would be inherited from more than once Assume I have a schema which initially have several yearly sliced data like:

schema: land_price
  - land_price_1990
  - land_price_1991
  ...
  - land_price_2010

Now I would like to create a parent table so that I can better manage them. I firstly try

ALTER TABLE land_price.land_price_2010 INHERIT land_price.land_price_parents;

It works, however, when I try to alter the second table, it fails and returns

ERROR: relation "land_price_parents" would be inherited from more than once

So in this case, how can I solve this problem?

p.s. Here is the version information:

select version()
PostgreSQL 9.3.23 on x86_64-unknown-linux-gnu (Ubuntu 9.3.23-2.pgdg14.04+1), compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit

The error indicates, "the table is already inherited with same or other parent table.

Here is an example:

Create three tables Table_A, Table_B & Table_C and insert similar values to all of th three tables.

    CREATE TABLE Table_A
    (
                                            EmpId int,
                                            EName varchar(128)
    );

    INSERT INTO Table_A(EmpId,EName)
    VALUES(1,'Rajesh');
    INSERT INTO Table_A(EmpId,EName)
    VALUES(2,'Ranjan');
    INSERT INTO Table_A(EmpId,EName)
    VALUES(3, 'Alok');

    CREATE TABLE Table_B
    (
                                            EmpId int,
                                            EName varchar(128)
    );

    INSERT INTO Table_B(EmpId,EName)
    VALUES(1,'Rajesh');
    INSERT INTO Table_B(EmpId,EName)
    VALUES(2,'Ranjan');
    INSERT INTO Table_B(EmpId,EName)
    VALUES(3, 'Alok');

    CREATE TABLE Table_C
    (
                                            EmpId int,
                                            EName varchar(128)
    );

    INSERT INTO Table_C(EmpId,EName)
    VALUES(1,'Rajesh');
    INSERT INTO Table_C(EmpId,EName)
    VALUES(2,'Ranjan');
    INSERT INTO Table_C(EmpId,EName)
    VALUES(3, 'Alok');

Then execute ALTER statement to add inheritance. For both alter statements executed successfully.

    ALTER TABLE PUBLIC.Table_A INHERIT PUBLIC.Table_B;

    Result:
    ALTER TABLE
    Query returned successfully in 132 msec.

    ALTER TABLE PUBLIC.Table_A INHERIT PUBLIC.Table_C;

    Result:
    ALTER TABLE
    Query returned successfully in 137 msec.

Now execute the same ALTER statement again and it'll result similar error.

    ERROR:  relation "table_c" would be inherited from more than once
    SQL state: 42P07

You can use following query to find inherited tables.

SELECT
    A.*, c.relname AS child, C.relname AS parent
FROM
    pg_inherits A
INNER JOIN  pg_class AS B ON (A.inhrelid=B.oid)
INNER JOIN pg_class AS C ON (A.inhparent=C.oid);


Which Oracle datafiles grow in size? I have downloaded and started up Oracle's pre-built OTN Developer Day VM. The VM is running on an SSD. I want to move datafiles that can grow to a separate mount point (setup as a vmdk on a HDD. more details here ).

I am looking at moving the following to the HDD mount point:

USERS tablespace
Redo log files

Should datafiles like temp or undo also be moved if I want to minimize the growth of the VM on the SSD ?

I will move all pdb data files,undo and create temp file into new directory.You can skip redo log files as they are fixed ,in Oracle Developer Day VM total size of Redo group files is 600M.

To move data files online execute sql command

  alter database move datafile 'source' to 'target' reuse;

            [oracle@localhost ~]$ rman target /

            Recovery Manager: Release 18.0.0.0.0 - Production on Wed May 1 11:20:30 2019
            Version 18.3.0.0.0

            Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

            connected to target database: ORCLCDB (DBID=2760656746)

            RMAN> report schema
            2> ;

            using target database control file instead of recovery catalog
            Report of database schema for database with db_unique_name ORCLCDB

            List of Permanent Datafiles
            ===========================
            File Size(MB) Tablespace           RB segs Datafile Name
            ---- -------- -------------------- ------- ------------------------
            1    840      SYSTEM               YES     /u01/app/oracle/oradata/ORCLCDB/system01.dbf
            3    530      SYSAUX               NO      /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
            5    270      PDB$SEED:SYSTEM      NO      /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
            6    360      PDB$SEED:SYSAUX      NO      /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
            7    5        USERS                NO      /u01/app/oracle/oradata/ORCLCDB/users01.dbf
            8    100      PDB$SEED:UNDOTBS1    NO      /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
            9    380      ORCL:SYSTEM          YES     /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
            10   1210     ORCL:SYSAUX          NO      /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
            12   72       ORCL:USERS           NO      /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
            13   2        ORCL:APEX_1581590175460392 NO      /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1581590175460392.dbf
            14   50       UNDOTBS2             YES     /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
            15   50       ORCL:UNDOTBS2        YES     /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf

            List of Temporary Files
            =======================
            File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
            ---- -------- -------------------- ----------- --------------------
            1    33       TEMP                 32767       /u01/app/oracle/oradata/ORCLCDB/temp01.dbf
            2    62       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012018-10-15_15-43-55-439-PM.dbf
            3    62       ORCL:TEMP            32767       /u01/app/oracle/oradata/ORCLCDB/orcl/temp01.dbf

            RMAN> exit

            Recovery Manager complete.
            [oracle@localhost ~]$  mkdir -p /u02/oradata/pdb/orcl -- creating new directories for pdb files
            [oracle@localhost ~]$ sqlplus sys/oracle@orcl as sysdba --login into pdb database as sysdba

            SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 1 11:26:29 2019
            Version 18.3.0.0.0

            Copyright (c) 1982, 2018, Oracle.  All rights reserved.

            Connected to:
            Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
            Version 18.3.0.0.0

            SQL> alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf' to '/u02/oradata/pdb/orcl/system01.dbf' reuse;

            Database altered.

            SQL> alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf' to '/u02/oradata/pdb/orcl/sysaux01.dbf' reuse;

            Database altered.

            SQL> alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf' to '/u02/oradata/pdb/orcl/users01.dbf' reuse;

            Database altered.

            SQL>  alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1581590175460392.dbf' to '/u02/oradata/pdb/orcl/APEX_1581590175460392.dbf' reuse;

            Database altered.

            SQL>  alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf' TO '/u02/oradata/pdb/orcl/undotbs2.dbf' reuse;

            Database altered.

        SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/pdb/orcl/temp01.dbf' SIZE 65011712  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

        Tablespace altered.

    SQL> alter database tempfile '/u01/app/oracle/oradata/ORCLCDB/orcl/temp01.dbf' drop including datafiles;  -- drop tempfile on old directory

    Database altered.

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCLCDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    840      SYSTEM               YES     /u01/app/oracle/oradata/ORCLCDB/system01.dbf
3    530      SYSAUX               NO      /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
5    270      PDB$SEED:SYSTEM      NO      /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
6    360      PDB$SEED:SYSAUX      NO      /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
7    5        USERS                NO      /u01/app/oracle/oradata/ORCLCDB/users01.dbf
8    100      PDB$SEED:UNDOTBS1    NO      /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
9    380      ORCL:SYSTEM          YES     /u02/oradata/pdb/orcl/system01.dbf
10   1210     ORCL:SYSAUX          NO      /u02/oradata/pdb/orcl/sysaux01.dbf
12   72       ORCL:USERS           NO      /u02/oradata/pdb/orcl/users01.dbf
13   2        ORCL:APEX_1581590175460392 NO      /u02/oradata/pdb/orcl/APEX_1581590175460392.dbf
14   50       UNDOTBS2             YES     /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
15   50       ORCL:UNDOTBS2        YES     /u02/oradata/pdb/orcl/undotbs2.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    33       TEMP                 32767       /u01/app/oracle/oradata/ORCLCDB/temp01.dbf
2    62       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012018-10-15_15-43-55-439-PM.dbf
4    62       ORCL:TEMP            32767       /u02/oradata/pdb/orcl/temp01.dbf


Query sharded collection returns all the results from the primary shard AND from the other shards I tried to set up a shard to test this functionality and maybe migrate some of our data into it.

I followed the documentation guide https://docs.mongodb.com/v3.4/tutorial/convert-replica-set-to-replicated-shard-cluster/

So I have a first replica rs0 (containing 2 servers), a replica rs1 (containing 2 servers) and a config replica (containing 2 servers for my tests, I understand I should use at least 3 in production)

Now, when I'm connecting on mongos instance, I see that rs0 is my primary shard. And the collection I sharded contains 1,000,000 documents. rs1 contains 1 chunk which contains approximately 500,000 documents. If I execute db.partitioned_collection.count() in mongos it returns 1,500,000. Whereas I would expect it to return 1,000,000.

I get that the primary shard contains all the unsharded documents, but mongos should only query data inside rs0 chunks no ?

What am I doing wrong ?

My sh.status() result is :

--- Sharding Status ---
sharding version: {
    "_id" : 1,
    "minCompatibleVersion" : 5,
    "currentVersion" : 6,
    "clusterId" : ObjectId("5b992a952e6c702f9dd5b675")
}
shards:
    {  "_id" : "rs0",  "host" : "rs0/mongo-test:27017,mongo-test:27018",  "state" : 1 }
    {  "_id" : "rs1",  "host" : "rs1/mongo-test:27019,mongo-test:27020",  "state" : 1 }
active mongoses:
    "3.6.7" : 1
autosplit:
    Currently enabled: yes
balancer:
    Currently enabled:  yes
    Currently running:  no
    Failed balancer rounds in last 5 attempts:  0
    Migration Results for the last 24 hours:
            2 : Success
databases:
    {  "_id" : "config",  "primary" : "config",  "partitioned" : true }
            config.system.sessions
                    shard key: { "_id" : 1 }
                    unique: false
                    balancing: true
                    chunks:
                            rs0     1
                    { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : rs0 Timestamp(1, 0)
    {  "_id" : "test",  "primary" : "rs0",  "partitioned" : true }
            test.partitioned_collection
                    shard key: { "number" : 1 }
                    unique: false
                    balancing: true
                    chunks:
                            rs0     2
                            rs1     1
                    { "number" : { "$minKey" : 1 } } -->> { "number" : 4794 } on : rs1 Timestamp(2, 0)
                    { "number" : 4794 } -->> { "number" : 9586 } on : rs0 Timestamp(2, 1)
                    { "number" : 9586 } -->> { "number" : { "$maxKey" : 1 } } on : rs0 Timestamp(1, 2)

UPDATE #1 :

It seems I completely misread the following documentation https://docs.mongodb.com/v3.6/core/sharded-cluster-shards/#primary-shard . Indeed the primary shard does not hold the unsharded documents but the unsharded collections ...

So the question now is why my primary shard (rs0) which should only contains 2 chunks, is still containing the whole collection data ?

I finally tested again to follow exactly the example at https://docs.mongodb.com/v3.6/tutorial/convert-replica-set-to-replicated-shard-cluster/ . And this time it did work... It took some time (don't know how much exactly) for rs0 to delete the moved documents, but after a few minutes (or tens of minutes) they were not there anymore.

I have tried to remember what I did differently on the first time. I'm wondering if I did insert the test data after rs0 became a part of the shard directly on the rs0 replica set. And from what I understand we must not insert/update data on shard replica sets directly.


What are the mandatory/required oracle 11g tablespaces? Ok, what are the mandatory tablespaces in oracle 11g.  My research has lead me to believe there are three of them: System, Sysaux, and Undo.  However since I am taking the OCA and all the questions(and articles) I have read about mandatory spaces and answered only say two spaces are mandatory, and depending on where question came from the answer is different.

System is mandatory.  That is a fact and undisputed.

The Sysaux and Undo tablespaces are the ones in question.  Which one?  Again my research has lead me to believe all three are, but oracles documentation is unhelpful at this point(or maybe I am using the wrong keywords in search?) and the practice questions lead me to believe that the test is looking for two tablespaces.

You will have to examine how the question is worded.  By default 11g will create all three tablespaces.  You can however create a database with only SYSTEM and SYSAUX.  Technically speaking you can upgrade pre-10g database and not add SYSAUX, or remove the SYSAUX tablespace after the database is created and therefore have a database with only a SYSTEM tablespace, but you would be ill advised to do so and I really don't think they would look for that in an answer.

From the 11.2 Administration Guide:

  The SYSAUX tablespace is always created at database creation.

From another section of the Administration Guide:

  An auto-extending undo tablespace named UNDOTBS1 is automatically
  created when you create the database with Database Configuration
  Assistant (DBCA)...

  If no undo tablespace is available, then the
  instance starts without an undo tablespace, and undo data is written
  to the SYSTEM tablespace. You should avoid running in this mode.

From the 10.2 Concepts Guide:

  ...the SYSAUX tablespace is always created during database creation or
  database upgrade...

  During normal database operation, the Oracle database server does not
  allow the SYSAUX tablespace to be dropped or renamed. Transportable
  tablespaces for SYSAUX is not supported.

  Note: If the SYSAUX tablespace is unavailable, such as due to a media
  failure, then some database features might fail.

From the 10.1 SQL Reference:

  You cannot drop the SYSTEM tablespace. You can drop the SYSAUX
  tablespace only if you have the SYSDBA system privilege and you have
  started the database in MIGRATE mode.

From the 10.1 Administrators Guide:

  If the SYSAUX tablespace becomes unavailable, core database
  functionality will remain operational. The database features that use
  the SYSAUX tablespace could fail, or function with limited capability.

http://oradbpedia.com/wiki/The_SYSAUX_Tablespace has this take:

  The SYSAUX tablespace is a new tablespace that is required in Oracle
  Database 10g...

  When you migrate to Oracle Database 10g, the SYSAUX tablespace needs
  to be created as a part of that migration. This is done after mounting
  the database under the new Oracle Database 10g database software. Once
  you have mounted it, open the database in migrate mode with the
  startup migrate command. After the database is open, you can create
  the SYSAUX tablespace.

  Note: The loss of the SYSAUX tablespace is not fatal to your database.
  In our testing it appears that the only real impact is that certain
  functionality related to the occupants of the SYSAUX tablespace is
  lost.

Earlier versions did not have a SYSAUX tablespace:

  SYSAUX is the name of the compulsory tablespace, introduced in Oracle
  10g.


SQL Server 2014 Developer on Windows 10? Has anyone tried to use SQL Server 2014 Developer Edition (64 bit) on Windows 10 yet? I have a Lenovo G50-45 laptop that came with Windows 8.1 and I have the option to upgrade, but I don't want to shoot my working setup in the tail by upgrading -- unless it is pretty certain to be successful and reasonably bump free. I guess I'm hoping to hear from someone who took the plunge who can report unparalleled success (or abject failure) to help nudge me off the fence on upgrading Windows.

SQL Server 2014 works on Windows 10 just fine. Some upgrade paths, though, will move all your apps and services to Windows.old (this happened to me one upgrade out of 4 or 5), so be careful and take the usual backups and other precautions. Thankfully, reinstalling SQL Server is not an extremely elaborate process.


Oracle nologging impact for REDO Generation Oracle database is on noarchivelog mode and tablespace is configured with nologging option keeping there will not be a situation to recover any data in mind.

Following scenario was occured during ETL process:

1st hour AWR showing 15GB per second and 131K per transaction in the
Load Profile.
1st hour AWR showing 54TB Redo generation logged in the "Instance Activity Statistics"

I am wondering why such a big volume of redo is generated in this context.

Considering following context for excessive redo:

It's known that dictionary specific tablespace is forced for
logging.
Considering that NOLOGGING is not applicable by any mean to any
UPDATE operations resulting from the MERGE statement.
NOLOGGING is explicitely specified with CTAS and DMLs like
inserts and used  /*+ APPEND */ hint with the insert statement to
bypass logging even if nologging options is active in
table/tablespace level.

Questions:

Can anyone see any other considerations beyond 1, 2 and 3, for generating such big volume REDOs?
In the context of #2, 54TB(seems that Simply computed by 15G per
second X 60 for min X 60 for hr) redo generation is not possible
within one hour window. Suspecting some bug with the Oracle AWR
report, it should be total redo generated since the Oracle is start
up.
Suspecting bug with the value of redo generated per seconds.

Any constructive answers for above questions and considerations are highly appreciated, so that I could rectify myself if I am wrong.

To get to the bottom of this you likely need to identify the SQL statements that are generating the REDO.  Unfortunately I don't think there is any simple, completely automated way to detect REDO per SQL.

But it's usually not too difficult to track down the worst SQL statement.  Since you've already got AWR, look at the SQL Statistics section, ordered by Elapsed Time.  If there's a lot of REDO then one of those statements may stick out.  For example, if the top statement is an UPDATE, DELETE, MERGE (UPDATE), or an INSERT without a hint (or with a bad hint).

Possibly the top statements are all CTAS or INSERT /*+ APPEND */.  In that case, look at the explain plan for each of those top statements, for example by using select * from table(dbms_xplan.display_awr(sql_id => 'sql_id from AWR'));  If the execution plan says LOAD AS SELECT then it's using direct path inserts and is OK.  If it says LOAD TABLE CONVENTIONAL, that's the problem statement.

Possibly they are all ok, but the REDO is generated by the indexes.  The only way an index will not generate REDO is if the index is set to NOLOGGING and if the index is built after the INSERT.  Index NOLOGGING only applies to index DDL.  You may need to drop/disable and recreate/rebuild indexes to reduce REDO.

In my experience, the most likely causes of an INSERT statement not running in direct-path write are:

Foreign key indexes enabled on the target table, except when reference partitioning is used.
Bad hint syntax.  For example, if there's a space before the plus sign, like this: /* +.  Or if the hint is placed in a slightly wrong position, like this
INSERT INTO /*+ append */ ....
Weird data types that aren't supported.  I can't remember exactly, but I think with LOBs there are some extra limitations, like you may need to have partitioning.
Triggers on the target table.
There are likely some other reasons I'm forgetting.

Post the entire SQL statement and execution plan and we may be able to help investigate.

If none of that helps, than maybe it is an AWR bug.  15GB per second is very high, but not impossible.  I wouldn't be too surprised if an Exadata system could achieve that.


Database Maintenance Job and Backup Scheduling I would request to have expert advise on scheduling maintenance and backup jobs. Below was the scenario prior to my change:

Full backup of database scheduled to run at 12:30AM everyday.
Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour.
Log backup is scheduled to run every 15 minutes as Log-shipping is configured.
Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday morning at 1:45AM.

We used to face space issue on the storage disk in the above scenario as Full backup was run before Maintenance job and hence subsequent differential backup was getting bigger and bigger in size until next full backup was run. This prompted me to run Full backup after maintenance job and I also checked the fragmentation level in mid of week and based on the values decided to run maintenance job twice a week, below is the modified plan:

Full backup of database scheduled to run at 12:30AM on all days except on Sunday and Tuesday when maintenance job is scheduled. On Sunday and Tuesday, Full backup is taken at 2:30AM.
Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour - No change.
Log backup is scheduled to run every 15 minutes as Log-shipping is configured - No change
Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday and Tuesday morning at 1:45AM.

The issue I am facing right now is the size of log backup immediately after maintenance job, Log backup is much bigger than Full backup of database itself. Needless to say, Log backups are transferred to Secondary site, which is then uploaded there for syncing purpose. This taken longer than expected and in between Log-shipping alert gets triggered since primary and secondary is not in sync. Earlier also, Log backup was bigger however used to be much lesser than that of Full backup and was taking considerably lesser time in transferring from primary to secondary server.

I am not really sure, if this is a valid scenario wherein the changes(insert/update/delete) was so voluminous in last 3 days that the maintenance job created bigger log file than full backup and would stabilize gradually or I should be scheduling two full backup on Sunday and Tuesday(when maintenance job is running) - One at 12:30AM and another one after maintenance job.

Appreciate your kind advise.

According to the scheduled maintenance tasks and second backup strategy here is my suggestion which may suit your requirement to reduce transaction log backup.
As you said in the comment, “Log-Shipping is enabled for the databases. Log-Shipping feature support both FULL and BULK_LOGGED recovery model.

So

1st thing you can do if you want to continue with FULL recovery model.

You can add two steps in the schedule maintenance job:

Change recovery model of database to BULK_LOGGED
Execute Index maintenance script
And Change recovery model back to FULL

Pros:  It will reduce truncation log backup size.
Cons: Point-In-Time recovery will not be possible for the duration.

2nd thing you can do if it suits your SLA (RTO & RPO) as you are backing-up transaction log every 15 minutes, “change recovery model to BULK_LOGGED.

3rd action you can perform to find all unused indexes and remove them, it’ll help to reduce T-Log backup size and maintenance duration as well. You can use this script to find index details.

CREATE TABLE #tbl_index_info(
    [Table Name] [sysname] NOT NULL,
    [Index Name] [sysname] NULL,
    [Index Type] [nvarchar](60) NULL,
    [Index Columns] [sysname] NULL,
    [Row Count] [bigint] NULL,
    [Fill Factor] [tinyint] NOT NULL,
    [user_seeks] [bigint] NOT NULL,
    [user_scans] [bigint] NOT NULL,
    [user_lookups] [bigint] NOT NULL,
    [Index Read] [bigint] NULL,
    [Index Writes] [bigint] NOT NULL,
    [Last Used (Read)] [datetime] NULL,
    [Last Used (Write)] [datetime] NULL,
    [Last Index Re-Built/Re-Organize] [datetime] NULL,
    [index_id] [int] NOT NULL,
    [object_id] [int] NOT NULL
)
GO

insert into #tbl_index_info
select      t.name [Table Name]
            ,i.name [Index Name]
            ,i.type_desc [Index Type]
            ,c.name [Index Columns]
            ,p.rows [Row Count]
            ,i.fill_factor [Fill Factor]
            ,ius.user_seeks
            ,ius.user_scans
            ,ius.user_lookups
            ,(ius.user_seeks+ius.user_scans+ius.user_lookups) [Index Read]
            ,ius.user_updates [Index Writes]
            ,COALESCE(ius.last_user_seek,ius.last_user_scan,ius.last_user_lookup) [Last Used (Read)]
            ,ius.last_user_update [Last Used (Write)]
            ,STATS_DATE(s.object_id, s.stats_id) [Last Index Re-Built/Re-Organize]
            ,i.index_id
            ,t.object_id

from        sys.tables t
inner join  sys.columns c
on          t.object_id=c.object_id
inner join  sys.indexes i
on          t.object_id=i.object_id
inner join  sys.index_columns ic
on          t.object_id=ic.object_id and i.index_id=ic.index_id and c.column_id=ic.column_id
inner join  sys.dm_db_index_usage_stats ius
on          t.object_id=ius.object_id and i.index_id=ius.index_id
inner join  sys.stats s
on          t.object_id=s.object_id and i.index_id=s.stats_id
inner join  sys.partitions p
on          p.object_id=i.object_id and p.index_id=i.index_id
where i.name is not null and i.is_unique!=1
Order by ius.user_lookups desc
GO

Select      DISTINCT
            a.[Table Name],a.[Index Name],a.[Index Type],
            (SELECT SUBSTRING(
            (
                SELECT ',' + [Index Columns] FROM #tbl_index_info b
                WHERE a.[Table Name]=b.[Table Name] and a.[Index Name]=b.[Index Name]
                FOR XML PATH('')), 2,10000)
                )[Index Columns]
                ,a.[Row Count]
            ,ips.avg_fragmentation_in_percent,a.[Fill Factor],a.user_seeks,a.user_scans
            ,a.user_lookups,a.[Index Read],a.[Index Writes],a.[Last Used (Read)],a.[Last Used (Write)]
            ,[Last Index Re-Built/Re-Organize]
from
#tbl_index_info a
cross apply
            sys.dm_db_index_physical_stats(db_id(),a.object_id,a.index_id,0,DEFAULT) ips
ORDER BY [Index Read] ASC
GO

DROP TABLE #tbl_index_info
GO

4th you need to verify that you are using appropriate FILLFACTOR for indexes. Less FILLFACTOR means more number for pages. So chose FILLFACTOR according to usage of table.

Thanks!


Added .Net assembly to SQL CLR without turning TRUSTWORTHY ON I'm trying to add "System.Messaging.dll" using the guidance provided by Solomon Rutzky in his post Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key but I'm failing at the first hurdle.

The first part of the script is to create a certificate from the assembly;

CREATE CERTIFICATE [MS.NETcer] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll';
GO

However, when I execute this I get the error;

Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has invalid format.

The account I'm using to execute the command has the 'sysadmin' server role. This is on a SQL Server 2008 instance.

Please does anyone have any ideas regarding why this is failing?

---------- Update 1 -----------

I've taken Solomon's advice and amended my script so it looks like this;

CREATE ASYMMETRIC KEY [Key.System.Messaging] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll';
GO

CREATE LOGIN [CLR.Login.System.Messaging] FROM ASYMMETRIC KEY [Key.System.Messaging];
GO

GRANT UNSAFE ASSEMBLY TO [CLR.Login.System.Messaging];
GO

All good so far. I now run the CREATE ASSEMBLY command to add the assembly to the SQLCLR;

CREATE ASSEMBLY [System.Messaging]
FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE
GO

but this fails with the error;

CREATE ASSEMBLY for assembly 'System.Messaging' failed because assembly 'System.Windows.Forms' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

I've tried creating an asymetric key for 'System.Windows.Forms'

CREATE ASYMMETRIC KEY [Key.System.Windows.Forms] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll';
GO

but this fails with;

Msg 15468, Level 16, State 1, Line 1
An error occurred during the generation of the asymmetric key.

so I tried;

CREATE CERTIFICATE [Cer.System.Windows.Forms] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll';
GO

and this fails with;

Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has invalid format.

So now I'm stuck again and wondering if its possible to use System.Messaging.dll in the SQL CLR without having to enable the TRUSTWORTHY database property (and also why on earth does System.Messaging.dll need to have a dependency on System.Windows.Forms.dll!)

Any thoughts gratefully received

I executed the same command on SQL Server 2017 CU12 and got the same 15208 error. I then tried the following:
CREATE ASYMMETRIC KEY [MS.NETsnk] FROM EXECUTABLE FILE =
'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll';

and that succeeded! ("snk" in the name is for "Strong Name Key", since technically this isn't a certificate).
From what I can tell, most of the .NET Framework libraries for CLR 2.0 (i.e. .NET Framework versions 2.0, 3.0, and 3.5) are not signed with a certificate. Most, but not all, of the mscor*.dll files, and only one System.*.dll — System.EnterpriseServices.Thunk.dll — are signed with a certificate. The rest are only using a Strong Name Key / are strongly-named.
Starting with CLR 4.0 (i.e. .NET Framework versions 4.*), it appears that all .NET Framework libraries are signed with a certificate.
Hence, the more precise rule is:

For CLR 4.0 (i.e. SQL Server versions 2012 and newer), use CREATE CERTIFICATE

For CLR 2.0 (i.e. SQL Server versions 2005, 2008, and 2008 R2), use CREATE ASYMMETRIC KEY. But, if doing that results in a 15208 error ("The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it"), then switch to CREATE CERTIFICATE.
The reason for this group to start with ASYMMETRIC KEY is the very low probability of ever needing to use CERTIFICATE. I tried to import System.EnterpriseServices.Thunk.dll in both SQL Server 2019 (which used CLR 4.0) and SQL Server 2005 (which used CLR 2.0) and it failed in both — msg 6507 in SQL Server 2005, and msg 6544 in SQL Server 2019 — complaining of a "malformed assembly" (meaning it's a mixed-mode / not-pure-MSIL assembly). I also tried importing some of the mscor* libraries in SQL Server 2005 and they failed with the same 6507 error (not surprising that the core libraries would have unmanaged code in them). So, it's quite likely that there's not even a possibility of using CREATE CERTIFICATE when importing .NET Framework libraries for CLR 2.0.

Now, it appears that System.Messaging has several dependencies:

system.configuration.install
system.runtime.serialization.formatters.soap
system.windows.forms
system.drawing
accessibility
system.directoryservices

Those dependencies get imported automatically because the source for CREATE ASSEMBLY is a file and not a VARBINARY literal / hex bytes. While doing this automatic import of dependencies, the operation fails on system.windows.forms claiming that it is not authorized for UNSAFE, even though we have already created the Asymmetric Key and the other dependencies have imported correctly.
Further investigation reveals that the Strong Name Key used for system.windows.forms is different than what was used for the other libraries. The libraries that import successfully have a Public Key Token of b03f5f7f11d50a3a while the one that fails has a Public Key Token of b77a5c561934e089. As the O.P. discovered, creating an Asymmetric Key from system.windows.forms does not work. Creating a certificate from the library also doesn't work, but that's easier to explain: the library isn't signed with a certificate ;-). The issue with creating the Asymmetric Key, however, is probably due to how system.windows.forms is signed. It uses ECMA signing, which places a standard, place-holder value in the library instead of the public key, and this place-holder value is not a valid public key. For more info, please see the following:

Assemblies and the GAC (Mono project ; there are 2 definitions related to ECMA)
Quite special PublicKey in .NET core assemblies (Stack Overflow answer)

Now, I don't think we can get the public key file to create an Asymmetric Key from. It might be somewhere in the file system as it shouldn't be a security issue, being a "public" key and all, but as of right now I don't know where it is.
So, for the moment we should still be able to get around this using the same basic technique that allows pre-existing, unsigned assemblies in databases upgraded to SQL Server 2017 (or newer) from a pre-2017 version, to work: sign the assembly in-place. Of course, to get the assembly into SQL Server we will need to temporarily enable TRUSTWORTHY, but only for a single command. Then, once the assemblies have been imported, we can:

create a certificate
sign the assembly with it
back up the certificate (both public and private keys) to files (can't get the VARBINARY of the public key via CERTENCODED as that was introduced in SQL Server 2012)
remove the private key from the certificate so that it can't be used to sign anything else
create the same certificate in [master], but only with the public key (so it can't be used for signing)
create a login from that certificate
grant that signature-based login the UNSAFE ASSEMBLY permission

Try the following approach based on the steps outlined above. The example code below assumes that the steps to create the Asymmetric Key, associated signature-based Login, and GRANT on System.Messaging have already been completed.
--CREATE DATABASE [Test];
--ALTER DATABASE [Test] SET RECOVERY SIMPLE;
GO

USE [Test];
ALTER DATABASE [Test] SET TRUSTWORTHY ON;

CREATE ASSEMBLY [System.Messaging]
FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE;

ALTER DATABASE [Test] SET TRUSTWORTHY OFF;

SELECT * FROM sys.assemblies;
-- System.Messaging: publickeytoken=b03f5f7f11d50a3a
-- System.Windows.Forms: publickeytoken=b77a5c561934e089

CREATE CERTIFICATE [WinForms]
  ENCRYPTION BY PASSWORD = 'Use Another Password!'
  WITH SUBJECT = 'For ECMA-signed .NET Framework libraries';

ADD SIGNATURE
  TO ASSEMBLY::[System.Windows.Forms]
  BY CERTIFICATE [WinForms] WITH PASSWORD = 'Use Another Password!';

BACKUP CERTIFICATE [WinForms]
  TO FILE = 'C:\TEMP\WinFormsCert.crt'
  WITH PRIVATE KEY
  (
    FILE = 'C:\TEMP\WinFormsCert.pvk',
    ENCRYPTION BY PASSWORD = 'Backup Password!',
    DECRYPTION BY PASSWORD = 'Use Another Password!'
  );

ALTER CERTIFICATE [WinForms] REMOVE PRIVATE KEY;
GO

USE [master];
CREATE CERTIFICATE [WinForms] FROM FILE = 'C:\TEMP\WinFormsCert.crt';
CREATE LOGIN [WinForms] FROM CERTIFICATE [WinForms];
GRANT UNSAFE ASSEMBLY TO [WinForms];

This work-around shouldn't be necessary when working with CLR 4.0 (i.e. SQL Server 2012 and newer) since all of those assemblies are signed with a certificate.
For more info on working with SQLCLR in general, please visit: SQLCLR Info


How to safely resume a SQL Server Mirroring scheme that had a expired certificate? We have a production database with a SQL Server 2012 Mirroring scheme with three machines: Principal, Mirror, Witness.

The mirroring was configured with a certificate that had only one year of expiry date. Now, the certificate is expired and the mirroring has failed. The Principal and Mirror machines are both with the database status Disconnected and the Mirror is also with "In Recovery" status.

To resume the mirror, I want to follow these tutorials: link1 and link2.

They basically suggest that I have to create new certificates and alter the Endpoint. My issue is how to safely resume the mirroring since the machines are disconnected for over a week. If I use ALTER DATABASE [mirrored_database_name] SET PARTNER RESUME, the Mirror machine will need A LOT of time to sync ALL information that was change in a week.

Will the Principal database be unavailable at this syncing or be with a bad performance? Or should I create a new mirroring session from the scratch? (i.e.: creating a new Full backup and Transaction Log backup for the Mirror machine)

Since Mirroring operates by shipping over a live transaction log to the secondary servers, you'll need to start a new mirroring session.

Your secondary database has been out of sync for a week and unless you've managed to maintain a transaction log on the primary that spans that entire time (hint: you haven't), you'll need to start with a fresh backup on the secondary to establish a mirror session again.

This is also faster than theoretically letting a week's worth of logs get re-applied to the secondary anyways.

As for the primary database's availability, yes it will be available during any sync (whether resuming after a pause or setting up a new mirror), because it's always sending transaction logs to the secondary but that does not interfere with it's own availability.