Changing TimeZone of an Oracle Database I am looking to change the timezone in a quite large Oracle database. What are some of the issues I might run into when trying to change the timezone in this way?

I don't want to 'trash' the database and am thinking of ways that the database might get trashed by changing TimeZones.

Any suggestions?

Changing the time zone is described in Oracle Database Globalization Support Guide
11g Release 2 (11.2)


Insert and select the row autoincremented id What I want to do is insert a row and than select its auto incremented id number, In a multhi-threaded enviroment.

I know how to do it with a Stored Procedure.

Will this line of code work:

BEGIN TRANSACTION;
INSERT INTO table (r1) VALUES (...);
SELECT id FROM table ORDER BY id DESC LIMIT 1;
COMMIT;

And are there any other options?

No.  Instead:

INSERT ...
SELECT LAST_INSERT_ID();

LAST_INSERT_ID() is session-specific, so there is never any confusion with other threads also inserting into the same table.

Also, there is no need for BEGIN ... COMMIT for this pair of statements.  (You may need it if there are other statements.)


How to display data from table 1 separately, and combined with matching rows from table 2 I have two tables, let's call them t1 and t2. They're associated - column c1 in t2 matches the primary key column i1 in t1.

For my results, I want every row in t1 to appear without any t2 data; then, in the following output rows, I want to see the combined data from the t1 row and any matching t2 rows. Is that possible in MS SQL? How can I do it?

Table t1:   Table t2:
+----+----+  +----+----+----+
| i1 | x  |  | i2 | c1 | c2 |
+----+----+  +----+----+----+
| 1  | a  |  | 1  | 1  |    |
| 2  | b  |  | 2  | 2  | g  |
| 3  | c  |  | 3  | 3  | h  |
| 4  | d  |  | 4  | 3  | i  |
+----+----+  +----+----+----+

Result
+----+----+----+---+----+----+
| i  | i1 | i2 | x  |c1 | c2 |
+----+----+----+---+----+----+
| 1  |  1 |NULL| a |NULL|NULL|<-- `t1` First Row
| 2  |  1 | 1  | a | 1  |NULL|
| 3  |  2 |NULL| b |NULL|NULL|<-- `t1` Second Row
| 4  |  2 | 2  | b | 2  | g  |
| 5  |  3 |NULL| c |NULL|NULL|<-- `t1` Third Row
| 6  |  3 | 3  | c | 3  | h  |
| 7  |  3 | 4  | c | 3  | i  |
| 8  |  4 |NULL| d |NULL|NULL|<-- `t1` Fourth Row
+----+----+----+---+----+----+

NOTE: column i in the output is simply a row number.

Your question is a little confusing (in particular around the joins), does this look something like what you are looking for?

CREATE TABLE T1 (i1 INT, x CHAR(1));
CREATE TABLE T2 (i2 INT, c1 INT, c2 CHAR(1));

INSERT INTO T1 (i1, x) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');

INSERT INTO T2 (i2, c1, c2) VALUES
(1,1,''),
(2,2,'g'),
(3,3,'h'),
(4,3,'i');
GO

CREATE VIEW TestView
AS
WITH cteInitialSet AS (
    SELECT i1, NULL AS i2, x, NULL AS c1, NULL AS c2 FROM dbo.T1
    UNION ALL
    SELECT T1.i1, T2.i2, T1.x, T2.c1, T2.c2
    FROM dbo.T1
        JOIN dbo.T2 ON T1.i1 = T2.c1
        )

SELECT ROW_NUMBER() OVER (ORDER BY i1, i2) AS i,
    i1 ,
    i2 ,
    x ,
    c1 ,
    c2
FROM cteInitialSet
GO

SELECT * FROM dbo.TestView;


Can you implement DR in cloud using Async AG, logshipping with an on-premise FCI solution? What are my options for building DR in cloud if I implement FCI for HA on-premise.

The goal is to have a Hybrid on-premise HA + DR in cloud solution.

Can I configure Async AG/logshipping in Azure with an on-premise FCI?

What are my options for building DR in cloud if I implement FCI for HA on-premise.

There are a ton. Whatever you could do now, you can do "in the cloud". Think of the cloud as an extension of your current datacenter (being a very basic analogy).

  The goal is to have a Hybrid on-premise HA + DR in cloud solution.

Different solutions are going to cost different amounts per month, so some maybe be more or less expensive than others - choosing the minimum needed infrastructure to meet the SLA's is often the best choice.

Outside of the other technical requirements (let's just keep it SQL Server at this point), you already have an FCI on site and setup. Depending on the version of SQL Server, you can do Availability Groups if you need that level of DR or BC. There is also Log Shipping, Transactional Replication, Backups (not even to an instance, just to a storage area), depending on the database feature usage there is also a bacpac which could be imported to IaaS or PaaS solutions.

  Can I configure Async AG/logshipping in Azure with an on-premise FCI?

Yes, but it'll require some extra infrastructure setup - still easily doable. Setting up an AG, though, would cost more than log shipping for the additional usage of components (at least in Azure - I work for Microsoft) and require much less setup. Things to think about.


Fire and Forget SQL Triggers? I have a 3rd party app that uses SQL Server as a back end. Specifically we have it on SQL Server 2000, SQL Server 2008 and SQL Server 2008R2. Any solution must be available to all, but solutions for only some would still be of interest.

The app is bespoke, out of support and the vendor doesn't exist any more. What I want to do is when it writes an insert is have a trigger to read that insert and start off a complex process. However, the app doesn't return focus until the SQL insert completes which means a lengthy trigger will cause the app to think SQL Server isn't responding and cancel/rollback the transaction.

Is there any kind of trigger that will 'release' immediately and then continue on with its processing? Or perhaps some other solution that I can implement internal to SQL Server.

I think I would build a table with a queue of some sorts, and have a job poll that table and pick up any work it has to perform.

So for example your trigger writes a record id in a table called processqueue, then you have a job that picks up all the records from the processqueue table and do it's heavy lifting for each and every record, removing the record from the queue once finished.

Schedule that job to run according to latency requirements


COLLATION and UNION ALL I have the following queries:

SELECT  *
FROM    OPENQUERY([SERVERA],
    'SELECT ''SERVERA'',[PhoneNum]
    ,[PhoneExt]
    ,COUNT(2) as CNT
    FROM [DB].[dbo].[ResourcePhone] WHERE PhoneNum like ''+44%''
    OR PhoneNum like ''+41%''
    GROUP BY PhoneNum, PhoneExt
    HAVING COUNT(2) > 1;')
        UNION ALL
SELECT  *
FROM    OPENQUERY([SERVERB],
    'SELECT ''SERVERA'',[PhoneNum]
    ,[PhoneExt]
    ,COUNT(2) as CNT
    FROM [DB].[dbo].[ResourcePhone] WHERE PhoneNum like ''+44%''
    OR PhoneNum like ''+41%''
    GROUP BY PhoneNum, PhoneExt
    HAVING COUNT(2) > 1;')

I am getting the error Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in UNION ALL operator.

I have checked the collation of the columns and database involved and they are all Latin1_General_BIN.  The difference is SERVERA has a collation of SQL_Latin1_General_CP1_CI_AS and SERVERB has a collation of Latin1_General_CI_AS.  I have tried adding COLLATE SQL_Latin1_General_CP1_CI_AS to the column names but this has not worked.

Can anyone advise how to resolve this?

Added exact query

SELECT  *
FROM    OPENQUERY([SERVERA],
    'SELECT ''SERVERA'',resourcephone.[PhoneNum]
    ,resourcephone.[PhoneExt]
    ,COUNT(2) as CNT
    FROM [rtc].[dbo].[ResourcePhone] WHERE PhoneNum like ''+44%''
    OR PhoneNum like ''+41%''
    GROUP BY PhoneNum, PhoneExt
    HAVING COUNT(2) > 1;')
        UNION ALL
SELECT  *
FROM    OPENQUERY([SERVERB],
    'SELECT ''SERVERB'' COLLATE Latin1_General_CI_AS, resourcephone.[PhoneNum] COLLATE Latin1_General_CI_AS
    ,resourcephone.[PhoneExt] COLLATE Latin1_General_CI_AS
    ,COUNT(2) as CNT
    FROM [rtc].[dbo].[ResourcePhone] WHERE PhoneNum  like ''+44%''
    OR PhoneNum like ''+41%''
    GROUP BY PhoneNum, PhoneExt
    HAVING COUNT(2) > 1;')

This query gives the following message:

Msg 457, Level 16, State 1, Line 1
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in UNION ALL operator.

The problem should be with the string literal (i.e. the ''SERVERA'') as that takes on the Collation of the database where that statement is executing.

So try the following for the top part of your query:

SELECT  *
FROM    OPENQUERY([SERVERA],
    'SELECT ''SERVERA'' COLLATE Latin1_General_CI_AS, [PhoneNum]
...

And, the bottom part of the UNION ALL (the part for [SERVERB]) should probably select "SERVERB" instead of "SERVERA" as the string literal :).

And to be technical, it is taking on the Collation of the DB, not the server, though if the default connection is to master (or any system DB), then that would naturally be the same as the Server-level Collation since the Server-level Collation is used to create the system DBs.

UPDATE:

The new error you are getting after adding the COLLATE clause to the 2nd query in the UNION ALL is due to adding the COLLATE clause to too many of the fields. It needs to go only to the literal string, and not on the resourcephone.[PhoneExt] field. Doing that one extra step put the fields that were already in agreement into conflict. Since both of those table fields are in Latin1_General_BIN, there is no need to override the Collation on the resourcephone.[PhoneExt] field from either Server. It is only the string literal "field" that needs to be overriden, and only from one of the two servers: you just need to make one match the other.

Adding the COLLATE clause to all fields, especially ones that are already working as expected (i.e. the resourcephone.[PhoneExt] field) is unnecessary and is over-complicating the query.


Convert Rows into Columns I have data like :

Emp Code | Name | Qualification | Completed Year
10000001   Ram    B.E               2005
10000001   Ram    M.E               2007
10000002   Sita   B.E               2003
10000002   Sita   MS                2005
10000002   Sita   Ph.D              2010

I want to convert like

Emp Code | Name |UG_Qualifi | Completed Yr| PG_Qualifi | Completed Yr
10000001   Ram    B.E               2005      M.E               2007
10000002   Sita   B.E               2003      MS                2005

Making a few assumptions, here is a query that would give you the desired results in Oracle:

SELECT EmpCode, Name
   , Max(UG_Qualification) UG_Qualification
   , Max(UG_CompletedYear) UG_CompletedYear
   , Max(PG_Qualification) PG_Qualification
   , Max(PG_CompletedYear) PG_CompletedYear
FROM
(
   SELECT EmpCode, Name
      , Case When Qualification='B.E' Then 'B.E'
         Else Null End UG_Qualification
      , Case When Qualification='M.E' Then 'M.E'
         When Qualification='MS' Then 'MS'
         Else Null End PG_Qualification
      , Case When Qualification='B.E' Then CompletedYear
         Else Null End UG_CompletedYear
      , Case When Qualification='M.E' Then CompletedYear
         When Qualification='MS' Then CompletedYear
         Else Null End PG_CompletedYear
   FROM e1
)
GROUP BY EmpCode, Name;

Results:

   EMPCODE NAME       UG_ UG_C PG_ PG_C
---------- ---------- --- ---- --- ----
  10000001 Ram        B.E 2005 M.E 2007
  10000002 Sita       B.E 2003 MS  2005

Setup:

drop table e1;
create table e1 (EmpCode Number(8), Name Varchar2(10)
   , Qualification Varchar2(5), CompletedYear Varchar2(4));
insert all
   into e1 values (10000001,'Ram','B.E','2005')
   into e1 values (10000001,'Ram','M.E','2007')
   into e1 values (10000002,'Sita','B.E','2003')
   into e1 values (10000002,'Sita','MS','2005')
   into e1 values (10000002,'Sita','Ph.D','2010')
   select * from dual;


PostgreSQL: Why is the schema owner not the database owner I've a simple question, yet I am unable to find the answer from the official documentation. When I create a database in PostgreSQL as a specific user, I'm the owner of all the objects under the database except for the schema. Is there a reason why the postgres role is the schema owner by default? I can't think of any explanation for this setup. Just curious ...

A new database is created by cloning an existing one. If you don't specify which one to clone, PostgreSQL clones the hidden-by-default database template1. All objects in template1 are owned by user postgres.

So you're not the owner of any of the objects in the database at the time it's cloned from template0 or template1; you only own the database its self.

New objects are created with your ownership because you created them. Existing objects retain their existing ownership.

See \dn * in psql to see the namespaces defined, \dp *.* to see table/view/sequence privileges, \df+ * to see function definitions with owners, \dT * to see data types, etc. You'll see that in a new blank DB they're all owned by user postgres.


Do MongoDB's "read uncommit" and "write lock" conflict with each other? I was reading the MongoDB documentation, where it says about read uncommit: MongoDB allows clients to read documents inserted or modified before it commits these modifications to disk, regardless of write concern level or journaling configuration[1] and about locks: Locks help guarantee that all writes to a single document occur either in full or not at all[2].

My question is, if the write operation has a lock, then why user can read the uncommitted data?

The write is done in-memory first and flushed to disk (asynchronously) later.  Any readers accessing a document will get the in-memory copy straight away, not waiting for the flush to disk to happen (otherwise the database would be disk bound in terms of performance).  The reference about locks applies to the in-memory portion, and guarantees the atomicity of the individual operation in-memory - it either happens in full or it does not happen, no in-between states for readers.  Hence the two do not conflict with each other, but you should be aware of how they function.

If you are concerned about persistence to disk for a particular write, then I would recommend looking into j:true and w:majority write concerns.  The first only returns true once the write is committed to the journal (on disk) and the second only returns true when the write has made it to a majority (2 out of 3, 3 out of 5, etc.) members of a replica set.  Please note: both write concerns will incur significant latency (and increased IO for the j:true option) compared to the default, so be sure they are required and that the level of performance is acceptable

Neither option (as the link you provided notes) stops readers from reading the data that has been committed in-memory but not flushed to disk, but j:true will give your application a notification that the write failed in the event that it does not make it to disk and w:majority will let you know if it has been replicated to other members (which would become primary in a failover event).  With that knowledge, your application can take the appropriate action (check, retry, other) based on the success/failure of the operation.


Have both distinct and count I have a table users(id and name) and order(cost and user: FK to users).

users.name users.id
bob               1
alice             1

and

orders.user orders.cost
          1          15
          1           5
          2           4
          1           4
          1           3
          1           3
          2           3

Here is a query which joints users and orders and sorts by order cost.

users.name users.id orders.user orders.cost
bob               1           1          15
alice             1           1           5
bob               2           2           4
alice             1           1           4
alice             1           1           3
alice             1           1           3
bob               2           2           3

If I were to do a distinct on user (in Postgresql), I would get

users.name    cost
bob              15
alice             5

I actually want the counts, while still being sorted on highest order cost/

users.name    count
bob               3
alice             4

Not sure I understand, but I guess something like:

select u.name, count(*)
from users u
join order o
    on u.id = o.user
group by u.name
order by max(o.cost) desc

would do. Please include your query and if relevant, table definitions for future questions.


Programmatic way to rewrite a query with the view definitions as sub query? Wondering if anyone has found or knows of a way to programmatically re-write a query to use the view definition as a subquery?
For example, let's say I have a view:
CREATE VIEW View1
AS
select * from table1
go

I want to be able to take the query:
select * from View1

And have it automatically rewritten as
select *
from (
  select * from table1
) as View1

This is a simple example, but I'd like to be able to do this for more complex queries, for example, if a view references another view, or there are multiple views joined together.  Rather than manually looking at the definitions of all the views and rewriting it manually, I was hoping to come up with a way to do this programatically some how.  I could probably figure something out on my own, but I just wanted to ask first in case someone knows of something that already does this so I don't have to recreate the wheel.

I've never heard of or seen anything of this ilk.
You're most likely better served rethinking how you utilize views and refactoring them in a way that isn't an impediment to development, instead of building a tool to do this.
Several layers of nested views are (to me) an anti-pattern and make it harder for developers and SQL Server alike. They're also very likely to hide bad code and obscure issues.
Also see:

The Seven Sins Against TSQL: Indulging in Nested Views


How to see who made a change to a table in SQL Server Is it possible to see who changed a table (or a stored procedure) in a database when the recovery model is simple? E.g. length of data type...
If the recovery model is full will it be possible so see this in the log file?
Is it only with a trigger that one can log every access to a table or is there a build in option for this?

You should have an extended event session in place but as a quick and dirty solution you can use the report below:


Dropping a table/column automatically drops views using it - how to warn instead? If I have a view that uses a column, and I remove that column/table, the view is dropped automatically.

Is there a setting I can change such that it instead prevents the removal of the column/table, warning that it has dependencies?

Operation System: I5 or IBM i

Operating System Version: V7R1M0

  [From comment] The view is not dropped; it may be marked as invalid.

SELECT * FROM QSYS2.SYSTABLES
WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' and table_name = 'MYVIEW';
--returns 1 row

ALTER TABLE MYLIB.MYTABLE DROP COLUMN TEST2;

SELECT * FROM QSYS2.SYSTABLES
WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' and table_name = 'MYVIEW';
--returns 0 rows

...That looks to me like it was dropped?

For i-series, at least for 7.1, you can try the RESTRICT clause of DROP COLUMN.

There is also a RESTRICT clause for DROP TABLE.

Per documentation

  RESTRICT

  Specifies that the column cannot be dropped if any views, indexes, triggers, or constraints are dependent on the column. 1

  If all the columns referenced in a constraint are dropped in the same ALTER TABLE statement, RESTRICT does not prevent the drop.


Unexpected Always On Availability groups failover happening--- databases changing to resolving I recently had done a SQL patch on a two node SQL availability group of SQL Server 2012. Everything went well as far as I can tell. Now two days after the patch unexpected failover happened. Then today as well unexpected failover happened.

For a temporary period, the primary of the availability group goes in to resolving state and forces it into failover to resume data movement as normal.

Now, I am not sure if this is anything related to the patch. Because the error logs show other issues. Here are the logs from the two times this happened.

  Date      6/11/2018 4:40:06 AM Log        SQL Server (Current - 6/11/2018
  2:25:00 PM) Source        Logon Message Error: 983, Severity: 14, State: 1.

  Date      6/11/2018 4:40:06 AM Log        SQL Server (Current - 6/11/2018
  2:25:00 PM) Source        Logon Message Unable to access database
  'accounting' because its replica role is RESOLVING which does not
  allow connections. Try the operation again later. Date        6/11/2018
  4:40:06 AM Log        SQL Server (Current - 6/11/2018 2:25:00 PM)

  And today Date        6/14/2018 3:13:47 PM Log        SQL Server (Current -
  6/14/2018 3:14:00 PM) Source      spid6s Message SQL Server has
  encountered 1 occurrence(s) of I/O requests taking longer than 15
  seconds to complete on file [C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\EveriT31_Park.mdf] in database
  [EveriT31_Parx] (21).  The OS file handle is 0x0000000000000B00.  The
  offset of the latest long I/O is: 0x00000002750000

  Date      6/14/2018 3:14:04 PM Log        SQL Server (Current - 6/14/2018
  3:14:00 PM) Source        spid127 Message Error: 18056, Severity: 20, State:
  46.

  Date      6/14/2018 3:14:04 PM Log        SQL Server (Current - 6/14/2018
  3:14:00 PM) Source        spid127 Message The client was unable to reuse a
  session with SPID 127, which had been reset for connection pooling.
  The failure ID is 46. This error may have been caused by an earlier
  operation failing. Check the error logs for failed operations
  immediately before this error message. Date       6/14/2018 3:14:05 PM
  Log       SQL Server (Current - 6/14/2018 3:14:00 PM)

The AG is set for automatic failover but the alwayson group is not really changing roles. After these episodes, the primary remains primary and the secondary remains secondars.

Every log I look at the first error I get is either the I/O issue or connection pool error (Error: 18056, Severity: 20, State: 46.). I hesitate to say the problem is really that. I don't think it is related with the SQL patch I just did. I don't see anything that is related to it and I want to rule that one out.

Can anyone suggest what might be causing these? I looked at some articles, but none gave me a clear answer.

it is set for automatic failover but the alwayson group is not really changing roles. After these episodes, the primary remains primary and the secondary remains secondars.

I've seen this pattern (PRIMARY -> RESOLVING for a brief period -> PRIMARY, and the same for the SECONDARY) before.  In my case, we were using a file share witness as a third node for quorum.  When this "not failover" happened, it was because the server where the file share witness lived had been taken down for maintenance.

Windows Server Failover Clustering (WSFC) uses a process called the Resource Hosting Subsystem (RHS) to monitor the health of resources in the cluster.  This includes both AG nodes, and in my case the file share witness resource.

While the AG (and cluster) can stay up and running with just the two AG nodes, after the file share witness has been down for 5 minutes, a process called RHS Recovery is started.  You can read more about that here: Understanding how Failover Clustering Recovers from Unresponsive Resources

If all your resources are hosted in the same RHS process (which is the default behavior), this will cause that "blip" in AG availability while the RHS.exe process is restarted.

Normally after one failure, the problematic resource should be automatically marked as "SeparateMonitor" started up in it's own RHS.exe process (so that it doesn't continue to cause problems for your other cluster resources).  So I'm surprised this happened more than once, but maybe someone changed the setting back, or the behavior is different for different resources or Windows versions.

You can check if that setting is on, or set it manually, in the Cluster Manager.  Here's a screenshot:

Anyway, this problem, and especially your comment, sounded very familiar to me so I thought I'd share the experience in case it helps you or others.

I blogged in detail about the troubleshooting process I used for this situation here: Troubleshooting an AG Failure

You should see evidence that RHS Recovery is the root cause of your problem by pulling the cluster log (run the Get-ClusterLog PowerShell command) and checking for messages around the time of your AG blip that mention "DeadlockMonitor", which is what checks "down" resources and initiates the recovery process.


is it possible to extract 'LifeCycle Status' using emcli ( Oracle Enterprise Manager ) It's easy to set the LifeCycle status ( and other properties ) with emcli,

emcli set_target_property_value -property_records="sometestdatabase:rac_database:LifeCycle Status:Test"

but getting the current values isn't clear - I'm not seeing a verb in the reference.

Is the only way via SQL against the repository?

Thanks
Simon

It seems that it's not possible, but, for the record, here's to get get the information via SQL.

SELECT CASE INSTR ( cm_target_name, '_' )
WHEN 0 THEN cm_target_name
    ELSE SUBSTR ( cm_target_name, 0, INSTR ( cm_target_name, '_') - 1 )
END database_name, property_value
FROM sysman.cm$em_tprops_ecm_view
WHERE cm_target_type = 'oracle_database'
AND property_name = 'orcl_gtp_lifecycle_status';


How do I reset the postgres password for PostgreSQL on Windows? I have installed PostgreSQL on my Windows machine for development. Now I haven't used it for a while, and have forgotten the password for user postgres (default admin user in PostgreSQL). How can I reset the password for postgres in PostgreSQL on Windows?

I followed Milen's suggestion, but got this message when trying to reload the configuration file:

C:\Program Files\PostgreSQL\9.1\data>pg_ctl reload -D .
pg_ctl: could not send reload signal (PID: 3396): Operation not permitted

In Windows there is a menu option "Reload Configuration" that worked better, as shown below:

From the 9.0 FAQ, I lost the database password. What can I do to recover it?:

I lost the database password. What can I do to recover it?
You can't. However, you can reset it to something else. To do this, you

Edit pg_hba.conf to allow trust authorization temporarily - go here for its location
Reload the config file (pg_ctl reload)
Connect and issue ALTER ROLE postgres WITH PASSWORD 'newpassword'; to set the new password (for first time users note the semi-colon)
Edit pg_hba.conf again and restore the previous settings
Reload the config file again


Changing mysql table comment I know that mysql table comment can be defined at the creation with:

create table (...)comment='table_comment';

And you can display comments by:

show table status where name='table_name';

How do you change (alter?) table comment after it's been created. I mean wihtout dropping and recreating the table again.

DROP TABLE IF EXISTS test_comments;
Query OK, 0 rows affected (0.08 sec)

CREATE TABLE test_comments (ID INT, name CHAR(30)) COMMENT 'Hello World';
Query OK, 0 rows affected (0.22 sec)

Check you comments in table structure

show create table test_comments\G
*************************** 1. row ***************************
       Table: test_comments
Create Table: CREATE TABLE `test_comments` (
  `ID` int(11) DEFAULT NULL,
  `name` char(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Hello World'
1 row in set (0.00 sec)

You can also check comments from information_schema like below

SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_NAME = 'test_comments';
+---------------+
| TABLE_COMMENT |
+---------------+
| Hello World   |
+---------------+
1 row in set (0.00 sec)

Alter table to modify comments

ALTER TABLE test_comments COMMENT = 'This is just to test how to alter comments';
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

Check the modified comments

show create table test_comments\G
*************************** 1. row ***************************
       Table: test_comments
Create Table: CREATE TABLE `test_comments` (
  `ID` int(11) DEFAULT NULL,
  `name` char(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='This is just to test how to alter comments'
1 row in set (0.00 sec)

SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_NAME = 'test_comments';
+--------------------------------------------+
| TABLE_COMMENT                              |
+--------------------------------------------+
| This is just to test how to alter comments |
+--------------------------------------------+
1 row in set (0.00 sec)


OR Operation in JOINS SQL(MYSQL) I am using MYSQL

SELECT  m.proposalId, m.title, n.stageNumber, n.committeeId, n.modifiedDate,
        o.msNumber , o.description,o.ics,o.edition
    FROM  mystands_Proposal m
    INNER join  mystands_ProjectLifecycle n on m.proposalId = n.proposalId
    INNER join  mystands_Project o          on m.proposalId = o.proposalId
    WHERE  n.newState=0
      AND  n.committeeId=79827
      AND  (n.assignedTo=29913 OR  n.actionBy=29913 )
      AND  n.proposalId LIKE '%sdas%'
      AND  o.projectNumber LIKE '%sdass%'
      AND  n.stageNumber=40.92
      AND  o.category=1
      AND  o.degreeofCorrespondence=1
      AND  o.msNumber LIKE '%sdas%'
      AND  (n.modifiedDate <='2015-05-15' AND  n.stageNumber=40.2 )
      AND  (n.modifiedDate <='2015-05-07' AND  n.stageNumber=30.99 )
      AND  (n.modifiedDate <='2015-05-27' AND  n.stageNumber=55.99 )

I am doing inner joins for three tables for search functionality and in java if the user enters the values then I am appending the value to the sql query on the fly. The above code is working fine for AND operations between fields.

How do I perform "OR" operation for the fields entered by the user and display the result. I have tried this:

SELECT m.proposalId, m.title, n.stageNumber, n.committeeId, n.modifiedDate, o.msNumber , o.description,o.ics,o.edition FROM mystands_Proposal m
INNER join
    mystands_ProjectLifecycle n on m.proposalId = n.proposalId
INNER join
    mystands_Project o on m.proposalId = o.proposalId
WHERE n.newState=0
      OR  n.committeeId=80246
      OR  (n.assignedTo=79977
              OR  n.actionBy=79977
          )
      OR  n.proposalId LIKE '%ads%'
      OR  o.projectNumber LIKE '%sds%'
      OR  n.stageNumber=30.99
      OR  o.category=1
      OR  o.degreeofCorrespondence=1
      OR  o.msNumber LIKE '%sadsa%'
      OR  (n.modifiedDate <='2015-05-22'
              AND  n.stageNumber=40.2
          )
      OR  (n.modifiedDate <='2015-05-22'
              AND  n.stageNumber=30.99
          )
      OR  (n.modifiedDate <='2015-05-29'
              AND  n.stageNumber=55.99
          )

Now what is happening is it is giving me the results of the inner joins because newState=0 is true and entire where condition is getting true and I am getting the results of inner joins on three table and the result is not being filtered as desired.

Can you please help where am going wrong? Thanks.

If n.newState=0 is always true then just remove it from your where statement.

or if everything you want to return needs to have that true then just change your first OR to a AND.
Then it will check to make sure that it is true and one other thing in your where statement is also true.

WHERE n.newState=0 AND ([all your other OR conditions])


How to open a sharded MongoDB (without replica set) environment on my dev machine? I have only found this tutorial here which seems to be most recent, other tutorials it seems are outdated and don't work anymore.

I want to create a sharded, non-replicated cluster on my dev machine and I couldn't find how to do it in Mongo 3.4

You can have one member replica set as a minimum. You will also need config server to keep track of shards.

Reference: https://docs.mongodb.com/manual/core/sharded-cluster-components/#development-configuration

  For testing and development, you can deploy a sharded cluster with a
  minimum number of components. These non-production clusters have the
  following components:

  A replica set config server with one member. At least one shard as a
  single-member replica set. One mongos instance.

WARNING:
Use the test cluster architecture for testing and development only.


Do BRIN indexes support ENUM types? BRIN indexes seem useful, but I'm not sure how to use one on an ENUM type.
I thought this code would work:

CREATE TYPE test_enum AS ENUM ('a', 'b');
CREATE TEMPORARY TABLE my_table (
    x test_enum
);

CREATE INDEX test_index ON my_table using brin (x);
ERROR:  data type test_enum has no default operator class for access method "brin"

Do I have to create a new operator class from scratch? Aren't enums already ordered?

This commit, from 2014, implies that BRIN indexes should work for ENUM types.

Aren't enums already ordered?

No.

  This commit, from 2014, implies that BRIN indexes should work for ENUM types.

That's actually not what that commit says. From the commit-message on the link you provided

  This type of operator class we call "Minmax", and we
  supply a bunch of them for most data types with B-tree opclasses.
  Since the BRIN code is generalized, other approaches are possible for
  things such as arrays, geometric types, ranges, etc; even for things
  such as enum types we could do something different than minmax with
  better results.  In this commit I only include minmax.

That doesn't mean it's there now. It is PostgreSQL for "patches accepted." In fact that commit explicitly says,

+/* no brin opclass for enum, tsvector, tsquery, jsonb, range */

Actually creating a BRIN index for an ENUM is, afaik, going to require some use of C and knowledge of index operator class implementation (pick/split/insert/merge).


Azure SQL - Setting up permissions for users I'm busy playing around within Azure SQL and have setup replication from my on-prem DB to the Azure DB for some reporting. I would like to ensure as part of this we setup the security correctly. In this instance I have a "Team" who do the BI/BO reporting (lets call them BIBOTeam). I would like them to have Read only access (db_datareader) on the Synced data, but they will need to create some Views (and potentially tables) on that database for their BI system.

They don't want to create the views in the "source" database as it's a vendore provided system.

I'm assuming schemas/roles is the way to go? I have an AD group called [GRP BIBOTeam] which is currently setup correctly and they can log into the DB and only read data in the Synced database.

Would love some guidance on what path is best to manage this access

Cheers

A separate schema will provide a security boundary and simply securty administration. Below is an example script that creates a schema for the team's objects with a schema-specific owner and grants permissions to create objects.

CREATE USER BOBO WITHOUT LOGIN;
GO
CREATE SCHEMA BOBO AUTHORIZATION BOBO;
GO
GRANT CONTROL ON SCHEMA::BOBO TO [<BOBO team AD group>];
GRANT CREATE TABLE TO [<BOBO team AD group>];
GRANT CREATE VIEW TO [<BOBO team AD group>];
GO


What is the "last" data that get's backed up, when running pg_dump on a live server? I'm wondering: If I start doing a pg_dump of a very large database (it would take hours), that is still running actively receiving writes, what is then the last data that goes into the backup? Is it:

The data as-it-was, at the point in time where the pg_dump command was initiated.
The last changes that it encountered at some point where it was dumping that individual record.
Something else.

Bonus question: If I'm trying to dump a database as part of trying to rescue it from corrupt data, will it then make any difference whether I use the directory format or the custom format?

It's the data at the start of the command for an entire database. According to the manpage:

  It makes consistent backups even if the database is being used
  concurrently. pg_dump does not block other users accessing the
  database (readers or writers).

and in SQL Dump:

  Dumps created by pg_dump are internally consistent, meaning, the dump
  represents a snapshot of the database at the time pg_dump began
  running

Dumping in parallel (--jobs) may be problematic with changing data, but only when targeting less recent versions:

  For a consistent backup, the database server needs to support
  synchronized snapshots, a feature that was introduced in PostgreSQL
  9.2

I don't think the output format makes any difference in the rescue operation. Note that for a parallel dump, directory is the only possible format.


How do I get DBeaver to do a DB2 UPDATE via Cursor The following query works when run from the console via db2 command. But fails when running via DBeaver.
BEGIN NOT ATOMIC
  FOR R AS C CURSOR WITH HOLD FOR
    SELECT ROW_NUMBER() OVER() AS INDEX, ID
    FROM A_TABLE a
    WHERE a.ID BETWEEN 1 AND 1000
    ORDER BY a.ID
  DO
    UPDATE A_TABLE SET A_COL = 1 WHERE ID = R.ID;--
    IF MOD(R.INDEX, 100) = 0 THEN
      COMMIT;--
    END IF;--
  END FOR;--
  COMMIT;--
END;

DBeaver gives the following result

SQL Error [42601]: An unexpected token "END-OF-STATEMENT" was found following "WHERE ID = R.ID".  Expected tokens may include:  "<psm_semicolon>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14

As mustaccio points out, you need to change your statement terminator. In DBeaver you can do that by:

Right-click on the data source and choose "Edit connection"
In the leftmost frame at the bottom, there is SQL Processing
Check the Datasource checkbox

Now you can alter the statement delimiter


SSMS can't connect to Azure database I got a new Windows 10 machine and installed the latest version of SQLEXPRESS and then the latest version of SSMS (17.9.1).

When trying to connect to my Azure database, I'm getting the following error.

  Error connecting to 'mydb.database.windows.net'.

  ------------------------------ ADDITIONAL INFORMATION:

  Failed to retrieve data for this request.
  (Microsoft.SqlServer.Management.Sdk.Sfc)

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  A transport-level error has occurred when receiving results from the
  server. (provider: TCP Provider, error: 0 - An existing connection was
  forcibly closed by the remote host.) (Microsoft SQL Server, Error:
  10054)

  An existing connection was forcibly closed by the remote host

What I've tried so far:

Uninstall/Reinstall - nope. I did this twice
Turn off firewall - didn't work. Besides, I can connect to the database fine through Visual Studio 2017, so it isn't a firewall issue. I also validated with netstat that port was open after I telnet-ed to the server.
Different version - I tried downgrading to SSMS version 17.9, but I get the same error. I'm in the process of bringing up 18.0 (preview 5) just to see if something will work for me. I may update here whether that works for me or not.
Different database - I have two Azure servers, I can't access either. I can connect to my local SQLEXPRESS instance without any issues.

My Google-foo has failed me on this so far. What am I missing? My current theory is that the SQLEXPRESS installation is colliding with SSMS install, but I don't know how to know that for sure - it was just something I read that used to be a problem.

Update

18 Preview 5 didn't help me. Same error :(

Others that appear to have this same issue without a resolution:

https://www.reddit.com/r/SQLServer/comments/9vfos7/issue_with_ssms_cant_connect_to_specific_server/
https://stackoverflow.com/questions/52885575/ssms-connect-to-azure-failure

Update 2

Uninstalling SQLEXPRESS and using IISCrypto to add specific TLS entries into the registry didn't help either.

Update 3 (After the answer below)

I installed SSDT and everything broke again :(. However, I did find that I can connect a single query in SSMS with no problem, but I cannot connect to a Server in the Object Explorer. Go figure.

I'll update here if there is something else I discover.

Update: I found that connecting to AWS failed too which led me to yet another Google search which led me here: https://www.sqlservercentral.com/Forums/1891503/Unable-to-connect-to-SQL-2008-Servers-from-SSMS-2017

Following the instructions from the last post in the referenced site, I downloaded IISCrypto and enabled all of the Ciphers. Bingo.

https://www.nartac.com/Products/IISCrypto/Download

I'll leave my previous answer below in case it brings comfort to anyone who went through sleepless nights and a couple of keyboards.

Old Update - After installing SSDT 15.8.2 the solution below stopped working for me :(

After days of:

MS Azure SQL Support - not helpful (they just referred me to this post. The one I made.)
Uninstalling SQL Server Express/Developer and three different versions of SSMS - all with the same results
Creating a VM on my computer with Win 10 Pro - SSMS worked on it no problem
Wiping the computer and starting from absolute nothing (using the MSI recovery) - still same issue
Inspecting TLS packets to ensure version 1.2 - it was 1.2
Trying to connect with SqlPackage.exe - never worked for me, same error as SSMS
Trying to connect with SQLCMD - it worked!
Trying to connect with SQL Native Client - also worked!

... I did finally get it to work. Good news is that it works. Bad news is that I don't know exactly why.

I currently have a fresh Win 10 Pro install with SSMS 17.9.1. I do not have any SQL Server instance installed. My default Windows Firewall is enabled and Norton AntiVirus is managing it (default with the computer). All Windows Updates have been applied as of this writing. I also downloaded and installed the SQL Server 2012 Native Client https://www.microsoft.com/en-us/download/confirmation.aspx?id=50402

When it started working, I had just used SQLCMD and connected to the Azure SQL Server master database and was querying the sys.event_log table. Immediately before that, I had used SQLCMD to connect to a different database on that same server. When I didn't see anything in the sys.event_log table I went back to SSMS and it worked for all of my servers (we have three).

TL;DR Connect with SQLCMD for a while and see if that helps??


Why did my transaction log grow so much? Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

About a month ago I migrated my SQL Server 2008 R2 database to a new schema. I ran scripts to migrate the data in the existing DB to the new DB. When I finished the new database was about 15 GB. A month goes by and my transaction log is 130 GB. Why did this happen and what should I do about it?

It sounds like your database is in full recovery mode. Under this recovery model your transaction log will continue to grow until it is backed up.  Because you're not backing up your transaction log you aren't able to reuse any portion of it.  This is all for point-in-time recovery. After you back up your transaction log, the reserved file space for the t-log file will not shrink. You will need to explicitly shrink that file with DBCC SHRINKFIKE. Note: file shrinking should not be a routine operation, but only to do a one-time on demand shrink when you know that file space will not be needed.

Which brings me to my next point. If you need this point-in-time recovery then you need to schedule transaction log backups routinely in order to prevent t-log growth (as you're currently seeing it). If for some chance you only require a recovery point objective that full backups and differential backups can deliver, then you should consider the simple recovery model.  Consideration to move to that recovery model needs to be a sound one, as you are losing a certain and import aspect of disaster recovery that most enterprise production databases require.

See this BOL reference on transaction logs and their truncation: http://msdn.microsoft.com/en-us/library/ms190925.aspx#Truncation


Recommended way to test code updates in Oracle We have lots of self-built high performance Oracle 12c database servers with two quite large instances (OLTP and data warehouse) for development purposes. Our PL/SQL developers sometimes have to redefine table structures, update millions to billions of rows, create/test new indexes and so on. At the moment everytime the update scripts run and the data is changed, we have to re-import all tablespaces, recreate replication, etc. because we need to test the updates again. This procedure takes about 3-5 hours (much of it running in the background) until the developers can access the server again. In the meantime they switch to another, unchanged server to test again, which, of course, is then unusable after the update (repeat as often as needed...).

My teams tasks include recreating the databases for these exact purposes, which is annoying, to say the least.

I'm trying to come up with some alternatives and have looked into creating RMAN backups prior to the tests, ARCHIVELOG-mode, huge UNDO tablespaces or retention time, FLASHBACK RECOVERY, etc.
Nothing I've come up with is exactly what I need, I think.

Mind: I've got very little DBA-experience and all of that little experience is self-taught or learning by doing.

So what I need or try to comprehend is, what techniques could I use to reset the database (or the changed tables / data) to some point in the past (maybe even five minutes ago), without having to re-import the database?
I think ARCHIVELOG mode and restoring the changed tables / data might be the best way, but I'm not remotely sure.

Could someone point me in the right direction?

This sounds like something where pluggable databases could be very useful.
You set up a 'standardised' test PDB and create a copy for each test. Further copies can be created for tests in parallel. Once the test is successful a new 'standard' PDB is created and the cycle continues.

I haven't used the techniques as we aren't currently on 12c here but this is one of the scenarios where we are expecting to be able to improve the testing cycle.


Primary Keys for Address tables I'm working on a database that stores address information. Each address has a Street, Number, Bus (as in the apartment they live in) and CityCode parameter. The CityCode parameter is a FK to the City table which has the postal code/zip code as PK but I think this is not important for this question.

I'm not sure what to do with the Address table. A Person will have an Address so these 2 tables will have a relationship. A combination of a Street, Number and Bus is as far as I know a unique entry in a database leading me to think that Street, Number and Bus could be a PK consistent of 3 columns.

On the other hand I would have to add 3 extra columns instead of 1 into my Person table to relate to the Address of the Person.

I've done some looking around and what I found is that PK's should be as short as possible for good performance. On the other hand, having just an auto-increment Id as PK makes it harder to view the raw data without joining tables.

What should I do in this case?

Using those 3 fields for a primary key is not a good idea (or did I misunderstood your question). There are 3 reasons for that:

The length of the key would be big.
The data would be stored in the Person table too.
Streets can sometimes be renamed or renumbered.

The best is to use a special field like you do for the Person data. As soon as you need a value that is not defined by yourself as PK then you should think twice on using it as a PK.

About your ZIP code. Not all countries have a single ZIP code for a single city. As long as you only need cities in your country then this is not a problem.


Lock database in time I'm currently working with prototyping some stuff with a dump of a MySQL (5.7) database where much of the data is time dependent. Most of the queries I write use the NOW() function and the results change over time as we get further away from the time the dump was taken.

Is there a way to "lock the database in time" to the time of the dump or is there some other way to deal with this sort of situation, other than replacing all references to NOW() with a hard coded time stamp?

You can use set timestamp <unix_epoch> which will make now() be fixed -- always return that value. See https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_timestamp

Note that this will not affect sysdate(). To fix sysdate, you can start MySQL with the --sysdate-is-now option which will make sysdate() return the same value as now(), so in conjunction with above sysdate() will now be fixed. See
  https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sysdate-is-now

(People coming from the Oracle world know this parameter as fixed_date.)


Group results from two different tables on same hours I have two simple tables:

indoor

id | timestamp | temp | humi

outdoor

id | timestamp | temp

And two selects which give me time and average temperature grouped by same hour for the last 24 hours:

SELECT DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_out_temp
FROM outdoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
ORDER BY timestamp ASC;

SELECT DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_in_temp
FROM indoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
ORDER BY timestamp ASC;

And now what I need to do is to group those two results by same hour, with respect to possibility that there can be no records in indoor or outdoor table for whole hour, so I need to get:

time | avg_out_temp | avg_in_temp
11:00 | 12.5 | 21.4
12:00 | 13.9 | null
13:00 | null | 22.4
14:00 | 14.0 | 22.5

I'm working with MariaDB:

mysql  Ver 15.1 Distrib 10.1.41-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

Because both your queries return the same number (and type) of fields, the can be combinied with UNION ALL. The ORDER BY is only needed once at then end of the query.

To see the difference between indoor and outdoor this could be added to your query.

SELECT 'out' as door,DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_out_temp
FROM outdoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
UNION ALL
SELECT 'in' as door,DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_in_temp
FROM indoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
ORDER BY timestamp ASC;


Incremental (not differential) backup of SQL Server 2008 R2 Is it possible (out of the box or free software) to make an INCREMENTAL backup of SQL Server 2008.

My problem is, the full backup and differential backup, backs up my 30gig database to 1 file. So when I run my incremental backup to the cloud, if the 30gig database file has a change, it will upload the entire 30gig.

Ideally, I'd like to be able to make an initial full backup to 1 file, then every night make differential backups to A NEW FILE (or a new file once a month) so that the backup doesnt re-upload the entire file every night.

I think what you are doing is you are writing your full and differential backups to the same physical file, which just appends each differential (and perhaps even additional full backups) to the same physical file container.

What you should be doing instead is using a different physical file name each time. I like embedding the date (and time, if we're talking about backups that are taken more often, like log backups) into the filename, like so:

BACKUP DATABASE foo TO DISK = N'c:\backups\foo_full_20150526.bak'
  WITH INIT;

BACKUP DATABASE foo TO DISK = N'c:\backups\foo_diff_20150527.diff'
  WITH INIT, DIFFERENTIAL;

And yes, you can do this dynamically:

DECLARE @path SYSNAME = N'c:\backups\foo_diff_'
  + CONVERT(CHAR(8), GETDATE(), 112) + N'.diff';

BACKUP DATABASE foo TO DISK = @path WITH INIT, DIFFERENTIAL;


How to prevent PostgreSQL from automatically rounding numeric types? I have a simple schema and query for a MWE.
Schema:
CREATE TABLE ttable (
  tcol Numeric(19,4)
)

Query:
INSERT INTO ttable (tcol) VALUES ('123.45678');
SELECT * FROM ttable;

Fiddle
Result:
123.4568
In this case, I have lost precision. I entered five decimal places, but Postgres automatically rounded down to four. I want this to be an error, or at least a warning of some kind that I can detect, so that I can tell the user about the loss of precision.
How can I make this happen?
Edit:
This question is obviously not a duplicate of this question. In that question, the user is using a client application which is rounding value below the stored precision. My question is about Postgres itself rounding the data to fit, not the client displaying. This would be obvious to anyone who looked further than the title of both questions.

I want this to be an error

You could use a check constraint that validates the scale of the value, rather than the definition of the data type:
CREATE TABLE ttable
(
  tcol numeric,
  constraint check_tcol_scale check (scale(tcol) <= 4)
);

Note also that the column itself needs to be redefined as just numeric, rather than numeric(19,4).
Then the following:
insert into ttable values (123.45678);

will result in

ERROR: new row for relation "ttable" violates check constraint "check_tcol_scale"
   Detail: Failing row contains (123.45678)

But
insert into ttable values (123.4567);

will succeed.
Check out this fiddle to see this solution in action.


How do you grant execute permission for a single stored procedure? Normally when I create a stored procedure I use the following as a template of sort

Create procedure <procedurename>
<@param1 <type>, @param2 <type>, etc..>
as begin

 <procedure>

end

Is there a way to include granting execute permission on only that stored procedure while I'm at it?
For instance like ...

Grant execute [User_Execute]

... but only for this stored procedure?

I've seen some other similar questions but they seem to all refer to ALL of the stored procedures and not just one, nor have I seen one where you can specify permissions inside of the create procedure script.  Even answers about how I can set permissions without the GUI for specific stored procedures would be welcome.

Edit
The top answer certainly pointed me in the right direction, this is is essentially what i was looking for, I didn't think about batching the commands which is what I ended up doing, batching the command along with my stored procedure.  Anyway, I think it's pretty slick.

Create procedure <procedurename>
<@param1 <type>, @param2 <type>, etc..>
as begin

<procedure>

end
GO
GRANT EXECUTE ON <procedurename> to <username>
GO

GRANT EXECUTE ON dbo.procname TO username;


Materialized View: What is the Update Trigger property? I've created a materialized view in an Oracle 18c database.
When I look at the Info tab of the materialized view in Toad, I see that there is a property called Update Trigger.

What does the Update Trigger property represent?

That's the way materialized views work. They essentially create a copy of the results of the query in the view. The base table(s) used in that query  are automatically updated when the base table changes. That is implemented via a trigger on the base table that records all changes into a materialized view log associated to that base table.
A subsequent process then reads that log to apply the changes to the materialized view table. That application can be automatic or manual.
This: https://oracle-base.com/articles/misc/materialized-views has a nice illustration of how this works:


SQL Permission to Truncate Table How Do I give permission for user to Truncate all tables in database?

The minimum permission required is 'ALTER on table_name' per articles below. With this, how do I give permission to allow user to Alter All tables in a database, so they can truncate? Is it something like for 'sp_MSforeachtable' or other method?

Thanks,

What permissions are necessary for truncating a table?

https://www.mssqltips.com/sqlservertip/4112/minimum-permissions-for-sql-server-truncate-table/

We have SQL Server 2016 Enterprise

Please do not grant any permission to any User outside of EXECUTE on a particular Stored Procedure that will do what you are wanting. Granting ALTER on a Table or Schema allows that User to make any change they want. You want them to be able to TRUNCATE, but granting them ALTER to a Table will allow the User to add / remove / change Columns, etc.

You want to create a Stored Procedure to contain only the specific actions that you feel are permissible. Whether that is a loop that goes through all tables and executes TRUNCATE on each one (will reflect new and removed Tables; might need a pattern specific for the Table name, perhaps?), OR is a list of accepted Tables to be truncated (won't reflect new and removed Tables, but maybe new Tables shouldn't be included anyway), is up to you. Either way, you should retain full control over the scenarios in which the User can perform this action.

Fortunately, this is fairly simple to accomplish. All you need to do is:

Create the Stored Procedure
Grant EXECUTE on the Stored Procedure to the User(s) and/or Role(s) that should be able to perform the TRUNCATE
Create a Certificate
Create a User from the Certificate
Grant the User any permissions needed to perform this action and/or add the User to any necessary fixed Database-Roles needed to perform this action.
Sign the Stored Procedure with the Certificate

The following is the T-SQL that corresponds to the steps noted above:

-- 1) Create the Stored Procedure:
GO
CREATE PROCEDURE dbo.[SomeTable_Truncate]
AS
SET NOCOUNT ON;

    TRUNCATE TABLE dbo.[SomeTable];
GO

-- 2) Grant EXECUTE on the Stored Procedure to the User(s) and/or Role(s)
--    that should be able to perform the TRUNCATE.
GRANT EXECUTE ON dbo.[SomeTable_Truncate] TO [SomeUserOrRole];

-- 3) Create a Certificate
CREATE CERTIFICATE [Permission$AlterSchema]
    ENCRYPTION BY PASSWORD = 'choose_a_password'
    WITH SUBJECT = 'Allow for TRUNCATE TABLE',
    EXPIRY_DATE = '2099-12-31';

-- 4) Create a User from the Certificate
CREATE USER [Permission$AlterSchema]
    FROM CERTIFICATE [Permission$AlterSchema];

-- 5) Grant the User any permissions needed to perform this action and/or add the User
--    to any necessary fixed Database-Roles needed to perform this action.
GRANT ALTER ON SCHEMA::[dbo] TO [Permission$AlterSchema];

-- 6) Sign the Stored Procedure with the Certificate.
--    Repeat this step as necessary.
ADD SIGNATURE
    TO dbo.[SomeTable_Truncate]
    BY CERTIFICATE [Permission$AlterSchema]
    WITH PASSWORD = 'choose_a_password';

That's it.


Scalability question There is a DB {MySql} with 5 tables A, BIG, C, D, E. Their grow factor is about 1/100/1/1/1.
The table BIG has Insert/Read/Update request ratio about 1/10/2. The Inserts and Updates 'cannot fail'.

The table 'BIG' has data that:

they are critical in the same day in which they are created (in the ACID point of view, the AC is very important), after 2 day its criticality gets smaller and smaller.
they provide the basis for statistical information located in some othe tables (F, G,...). There are some "data-pumps" that reads the data from BIG and write it on F, G. The data pumps reads about 100 rows from BIG and write about 1 row on F, 1 on G etc. After that operation the rows on BIG can be removed.

More Numbers: For the table BIG i expect that: About +2k rows per day in the peakday, about +0.5k rows per day as average. The growing is cyclical: (i.e. mon=+0.5,...,wed=+0.5,sat=+1k,sun=+2k, mon=+0.5,...) and for this reason i would activate the clean of the data once per week (i.e. on mondays)

Description of Data: they are basically user requests that needs to be served 'live' (max 1 hour). Basically there is no need to store those served requests after they are marked as consumed; I just need to do some stats on that (maybe after some day, but no hurry on that).

Deploy information: The deploy is on Heroku and i would use MySql (said to be good at reads) or Postgres (said to be good at updates?) any advice on that too?

What would be a good option in order to effectively manage the scalability of the DB? Is the data pump a good solution?

I was thinking an in memory table BIG but it is said to provide a good Read ratio (like it would be a cache), what about the Inserts and Updates?
Are there any other options ?

Please provide some sizes.  For example, how big would BIG be when it is time to remove the rows?  How big are the other tables?

MEMORY is not necessarily better than InnoDB.  It may be slower because of table vs row locking.  It may interfere with overall performance because of taking RAM away from the buffer_pool for BIG, thereby slowing down other things.

Yes, having a "staging" table is a practical way to do certain things.  For really high-speed staging, ping-ponging between two table may be desirable.

PARTITION is unlikely to be useful in what you have described so far.

Please describe what kind of data you have and why it needs AC or ACID (if it is not obvious from the data).

Edit

Thanks.  One row a minute?  Things don't get exciting until 100 per second.  Based on that, I would expect either MySQL or Postgres to consider this to be a "tiny" database.

I recommend doing it in whatever way is easiest for you.  I do not forsee any scaling/performance problems, at least not for the near future.  (I am assuming adequate INDEXes on the tables and reasonable queries.)  I'm still vague on what the "pump" is, but it sounds unnecessary.


Best way to know if my environment has workload repository license Usually, when I need to use something that use some resource from awr, I look for LAST_USAGE_DATE at dba_feature_usage_statistics with feature name Automatic Workload Repository, but I'm almost sure this is not a good way.

How I know if my environment has workload repository license?

By looking at the contract.

Oracle does not technically limit installing and using features, anything can be installed, whatever you have in the database may not reflect what you are allowed to use. It is the responsibility of the DBA to not install or disable unlicensed features wherever it is possible.

Starting with 11g, there is a parameter called CONTROL_MANAGEMENT_PACK_ACCESS. Its default value enables both Diagnostic and Tuning Packs, but in a properly managed environment it should be set according to the license. When the value of the parameter includes DIAGNOSTIC, then Diagnostic Pack (which includes AWR) is enabled, otherwise it is disabled.


Postgresql Equivalent of MySQL SHOW CREATE xxx Tried to search what are Postgresql equivalent of MySQL's SHOW CREATE TABLE <TABLE-NAME> and SHOW CREATE DATABASE <DB-NAME>. Checking old Answer, it seems impossible. I would have given up if it wasn't for PgAdmin4 which shows them nicely which emboldened me for the search adventure.

So far it have been unsuccessful, with Google showing nothing useful and up-to-date on the issue. trying reading PgAdmin4 proved to be disastrous as I was lost in the code without any guide.

So my question is, is this possible yet? If no how does PgAdmin4 do, if any have an idea?

Note: My app is not allowed to modify the database and insert Function at all

You cannot do it in SQL, but you can use the pg_dump executable to get the complete table definition:

pg_dump --schema-only --table=tablename databasename

This won't work for databases, however.


Getting PL/SQL: ORA-00942: table or view does not exist in plsql function but not from the command view i have function with a cursor the read the dba_scheduler_jobs which is a view. How can i use a view in a cursor?

create or replace function fn_gss_chk_scheduled_jobs(p_key IN varchar2)
  return boolean is
  --Result     boolean;
  failed_cnt number;
  stas       varchar2(20);
  check_type varchar2(50) := 'Scheduled Jobs';

  cursor c1 is

    select JOB_NAME, COMMENTS
      from dba_scheduler_jobs
     where owner = 'FCUBSPROD'
       AND state <> 'SCHEDULED'
       AND state <> 'RUNNING';

begin

  for rec in c1 loop

    IF c1%FOUND THEN
      Dbms_Output.Put_Line(Rec.Comments);
      stas := 'ERROR';
      pn_gss_update_dash_det(p_key, rec.job_name, rec.comments, 'D');

    ELSE

      stas := 'OK';
    END IF;
  end loop;

  pn_gss_update_dash_det(p_key, check_type, stas, 'S');

  return(true);
end fn_gss_chk_scheduled_jobs;

from sys user -->
grant select any dictionary to 'desired schema';


Hex and Binary Literals in MySQL / MariaDB? Another question I have pertains to just the & over string types. This question is over the binary-string literals as constructed with B'' or X''.

MariaDB supports binary and hex literals,

SELECT X'01', B'00000001', X'01' = B'00000001';
| X'01' | B'00000001' | X'01' = B'00000001' |
+-------+-------------+---------------------+
|    X  |       X     |                   1 |

* Using `X` because StackExchange strips the non-printable character.

However, interestingly, they're not the same when I pass them through the Binary-operators

SELECT X'01' & B'00000001', X'01' & X'01', B'00000001' & B'00000001';
| X'01' & B'00000001' | X'01' & X'01' | B'00000001' & B'00000001' |
+---------------------+---------------+---------------------------+
|                   0 |             0 |                         1 |

So if they're treated as bit-strings it doesn't make sense (because the result should be 1. And if they're treated as strings it doesn't make sense because the actual string is in fact (BIT-ANDed) & to 1. This shows they don't "behave as a string in all contexts".

SELECT X'01' & B'00000001', '01' & '00000001';
+---------------------+-------------------+
| X'01' & B'00000001' | '01' & '00000001' |
+---------------------+-------------------+
|                   0 |                 1 |
+---------------------+-------------------+

I see this, in the doc on hex literals,

  For hexadecimal literals, bit operations are considered numeric context, but bit operations permit numeric or binary string arguments in MySQL 8.0 and higher. To explicitly specify binary string context for hexadecimal literals, use a _binary introducer for at least one of the arguments.

MariaDB says,

  Normally, hexadecimal literals are interpreted as binary string, where each pair of digits represents a character. When used in a numeric context, they are interpreted as integers. (See the example below). In no case can a hexadecimal literal be a decimal number.

But that still doesn't seem to cut it, if anything it makes it worse (now none of the results of & return non-0.

SELECT _binary X'01' & _binary B'00000001', _binary X'01' & _binary X'01', _binary B'00000001' & _binary B'00000001';
| _binary X'01' & _binary B'00000001' | _binary X'01' & _binary X'01' | _binary B'00000001' & _binary B'00000001' |
+-------------------------------------+-------------------------------+-------------------------------------------+
|                                   0 |                             0 |                                         0 |

Even an explicit cast is wrong,

SELECT CAST(_binary X'01' AS BINARY(8)) & CAST(_binary B'0000001' AS BINARY(8));
| CAST(_binary X'01' AS BINARY(8)) & CAST(_binary B'0000001' AS BINARY(8)) |
+--------------------------------------------------------------------------+
|                                                                        0 |

Another bizarre thing is that they act different when stored on a table, for example,

CREATE TABLE g
AS
  SELECT b'00000001' AS value, b'00000001' = 0 AS isequal;

SELECT value, value=0, isequal AS wasequal
FROM g;
+-------+---------+----------+
| value | value=0 | wasequal |
+-------+---------+----------+
|      |       1 |        0 |
+-------+---------+----------+

Why do binary literals and hex-literals behave different and what are their behaviors?

This is expected behaviour in MariaDB since versions 10.0.3 and 5.5.31. It looks like you're running the queries in MariaDB, but reading the MySQL docs. These are similar, but diverging RDBMSes.

According to the MariaDB documentation on hexadecimal literals:

  The first two syntaxes; X'value' and x'value, follow the SQL standard,
  and behave as a string in all contexts in MariaDB since MariaDB 10.0.3
  and MariaDB 5.5.31 (fixing MDEV-4489). The latter syntax, 0xvalue, is
  a MySQL/MariaDB extension for hex hybrids and behaves as a string or
  as a number depending on context. MySQL treats all syntaxes the same,
  so there may be different results in MariaDB and MySQL (see below).

The queries in your example will give warnings such as:

  | Warning | 1292 | Truncated incorrect INTEGER value: '\x01' |

So you can get your desired behaviour by using the non-standard 0xvalue syntax:

SELECT 0x01 & B'00000001', 0x01 & 0x01, B'00000001' & B'00000001';

Which gives:

+--------------------+-------------+---------------------------+
| 0x01 & B'00000001' | 0x01 & 0x01 | B'00000001' & B'00000001' |
+--------------------+-------------+---------------------------+
|                  1 |           1 |                         1 |
+--------------------+-------------+---------------------------+


Can SQL Server's BCP output to multiple gzip compressed files? I've been looking into bcp.exe on SQL Server and am wondering if its abilities are limited to what the documentation actually says...

Is there no native way to output to multiple files?

And regardless of being able to output to multiple files or not, can it output to at least compressed files?

Not natively with BCP, but if you define SQL Server Integration Services as native (after all, it's free in the box with SQL Server), then use SSIS instead. You can even output to compressed files with third party add-ons for SSIS.


Excessive compile blocking on sp_procedure_params_90_rowset A resurgence of this questionon MSDN: Blocked-process-report: what is this waitresource "OBJECT: 32767:124607697:0 [COMPILE]"

I have caught these statements in Profiler. They all have durations over 3 seconds. Some over 10+. The blocking activity is the same as the link from MSDN.

The calls all use 3 part naming. All specify a different proc in form they look like the following:

exec [db1].[sys].sp_procedure_params_90_rowset N'proc1', 1, NULL, NULL
exec [db2].[sys].sp_procedure_params_90_rowset N'proc2', 1, NULL, NULL
exec [db3].[sys].sp_procedure_params_90_rowset N'proc3', 1, NULL, NULL
exec [db4].[sys].sp_procedure_params_90_rowset N'proc4', 1, NULL, NULL

What can I do to reduce this level of blocking?

(edit)
I am now seeing the same thing for:

exec [db1].[sys].sp_primary_keys_rowset N'view1', N'dbo'
exec [db2].[sys].sp_primary_keys_rowset N'view1', N'dbo'
exec [db3].[sys].sp_primary_keys_rowset N'view1', N'dbo'
exec [db4].[sys].sp_primary_keys_rowset N'view1', N'dbo'

There is a systemic something going on but I don't know what else to do. the caller is VB6 via ADO. It is ADO making these calls.

An example blocked process report is below

 <blocked-process-report>
    <blocked-process>
        <process
            id="process5bc1288"
            taskpriority="0"
            logused="0"
            waitresource="OBJECT: 32767:124607697:0 [COMPILE]"
            waittime="28887"
            ownerId="11638114050"
            transactionname="sqlsource_transform">
            <executionStack>
                <frame
                    line="1"
                    sqlhandle="0x000000000000000000000000000000000000000000000000">
                    <sqltext>EXEC [dbo].[spAlertDetectByPoll] ':V:^RMAlert^:Z:^&amp;N&amp;#RMAlert#&amp;S&amp;#L#&amp;UID&amp;#19#&amp;AGN&amp;#1#&amp;DFC&amp;#103#^', 1</sqltext>
                </frame>
            </executionStack>
            <inputbuf>
SET NO_BROWSETABLE OFF   </inputbuf>
        </process>
    </blocked-process>
    <blocking-process>
        <process
            status="suspended"
            waitresource="OBJECT: 32767:124607697:0 [COMPILE]"
            waittime="35693"
            spid="1121"
            sbid="0"
            ecid="0"
            priority="0"
            trancount="0"
            lastbatchstarted="2013-12-16T14:45:48.960">
            <executionStack>
                <frame
                    line="1"
                    sqlhandle="0x000000000000000000000000000000000000000000000000" />
            </executionStack>
            <inputbuf>
SET NO_BROWSETABLE OFF   </inputbuf>
        </process>
    </blocking-process>
</blocked-process-report>

There is an excellent blog post
http://blogs.msdn.com/b/support_sql_france/archive/2012/02/07/sql-server-compilation-gateways-and-resource-semaphore-query-compile.aspx
that explains what's happening.

SQL Server allows for a set number of compilations based on their complexity.  It groups them into small, medium, and large.  For large compilations, there can be onlyi one compiled at a time, so let's say all of your procs are considered large, then each one has to be compiled serially.  That could account for the blocking.
I think there may be several approaches to the problem - consider more resources (more CPUs will allow more small and medium queries to be concurrent or may up the threshold for what is considered medium).  Also, more memory may solve the problem.

If you're like most of us, that might not be possible.  Another option might be to review the ADO calls and see if the number of calls can be reduced or spread out so that not all calls happen at the same time.  Reducing the number at any given time should reduce your wait time.

If that doesn't work, consider fixing the 'compilability' of the stored procs.  Maybe break them down into smaller chunks which might reduce them to the small or medium buckets and allow more parallel compilations.  Or determine why the procs need to be recompiled each time.  See if they can be rewritten such that they don't need to be recompiled.  Finally, I'd consider using Plan Guides.  These will allow the procs to be precompiled and may save some time.

Hope that helps


How to connect once for all queries in SQL Server Management Studio? SSMS asks me to connect for every query in my project. Is there a way to connect only once per connection for all of the queries using it in my project?

The answers below (March 2014) don't answer the question. Still looking for a solution.

Click View --> Registered Servers
Right-click Local Server Groups --> New Server Registration

Then you can store the connection info, name it what you want, and just double-click then when you want to use it.


The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. When syncing a SQL Server 2008 subscriber with a publisher, the sync fails at around 98% with this error message:

  The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. This can occur when there are pending DDL changes waiting to be applied at the Subscriber. Restart the Merge Agent to apply the DDL changes and synchronize the subscription.

We have 30+ subscribers -- all of which are working except this one.

Is there any harm to other subscribers to restart the agent?

Bonus Points:

b) Is restarting the agent a good solution, or should I be looking elsewhere when I see this message?

This error message is also featured in Issue with SQL Server 2008 replication

I have run into this in the past.  In my experience typically this will resolve it's self by just restaring the sync process.  From the client in a pull situation, or restart from the publisher in a push situation.

In short the application of DDL changes ran into a conflict but restarting the sync will usually resolve the issue.  And no, it doesn't start from the beginning, it should just pick up from the point of failure.

It also wouldn't hurt to make sure that your snapshot is up to date and current.


Query Tuning and Index Tuning - SQL Server I've been asked to improve the performance of the following query.
I can't amend any existing Indexes but I can create new ones.

The table structures can be found in this SQL Fiddle

The row sizes of the tables are ( from development - query plans and statistics etc will be provided from development)

dbo.Logs        --6620130
dbo.LogMobiles  --6620082

For reference, the table sizes in Production are

dbo.Logs        --271856036
dbo.LogMobiles  --269611865

I updated the statistics on the tables before running the query like so

UPDATE STATISTICS dbo.Logs ;
UPDATE STATISTICS dbo.LogMobiles ;
DBCC UPDATEUSAGE(db1, 'dbo.Logs');
DBCC UPDATEUSAGE(db1, 'dbo.LogMobiles');

The query is as follows

DECLARE
     @rpStartDateTime       DATETIME    = '20180101'
    ,@rpEndDateTime         DATETIME    = '20180131' ;
DECLARE
     @iStart INT = DATEDIFF(SECOND, '19700101', @rpStartDateTime)
    ,@iEnd  INT = DATEDIFF(SECOND, '19700101', @rpEndDateTime);

SELECT
      ROW_NUMBER()OVER(PARTITION BY L.NodeId ORDER BY L.NodeId, L.GPSTime ASC)
    , V.NodeId
    , V.UnitMaxSpeed
    , M.DriverNodeId1
    , M.StatusText
    , M.Speed
    , ISNULL(S.StreetMaxSpeed,200)
FROM
    #xMobiles               V
INNER JOIN
    dbo.Logs        L   ON  L.NodeId = V.NodeId
INNER JOIN
    dbo.LogMobiles  M   ON  M.LogId = L.LogId
    --Must have Drivers identifiable
INNER JOIN
    #xDrivers       DN  ON  DN.DriverNodeId = M.DriverNodeId1
LEFT JOIN
    dbo.Streets     S   ON  S.StreetId = M.StreetId
WHERE
    L.GPSTime >= @iStart AND L.GPSTime <= @iEnd
ORDER BY L.NodeId, L.GPSTime
OPTION (MAXDOP 1) ;

The LogId from dbo.LogMobiles is a FK that references LogId (PK) from dbo.Logs.

Here is the Query Plan as is with no additional indexes

The Logical reads are as follows

From the Plan, the number of Actual Rows from dbo.Logs is 230768 which matches the final output.  The number of Actual Rows from dbo.LogMobiles (from a Clustered Index Scan) is 5676246, this is 24x greater.

Time taken to execute ~ 4 seconds.

There should be a one2one relationship between dbo.Logs and dbo.LogMobiles (although other functionality do log rows to dbo.Logs) and as you can see the big difference between the number of logical reads and actual number of rows retrieved from these tables.

I then added the following Index on dbo.LogMobiles

CREATE NONCLUSTERED INDEX IX_LogId_SBR ON dbo.LogMobiles ( DriverNodeId1 ASC, LogId ASC ) INCLUDE ( Speed, StatusText, StreetId  )

This is Plan with the new Index

This reduced the number of logical reads on dbo.LogMobiles

But increased the actual number of rows returned on the Index scan to 6620082 ( 28x greater).

Time taken to execute ~ 4 seconds - same.

What should I try next?

I am an Oracle Developer. Based on my understanding of Oracle performance tuning, this is how I would approach your situation.

I am seeing three entry points to your SQL.

a. xMobiles
b. xDrivers
c. GPSTime endpoints.

Each entry point will produce a different execution plan. Based on the selectivity of rows upfront in the execution plan tree, the best plan needs to be selected dynamically. Go through the data volume that gets generated at each step for the three possible execution plans. That will give you a good insight into the fact that "which plan suits your particular scenario".

Regarding your first execution plan, you have stated that the number of Actual Rows from dbo.Logs is 230768 and the Clustered Index(CI) Scan is showing 5676246 (24x greater).

Please note the difference between "SEEKs" and "SCANs". IX_Log_NodeID1 was accessed via an Index Seek. This uses the predicate values passed for that index and seeks only those rowids matching the predicate values. It does not scan the entire index. Whereas, in step two of the execution plan, the entire CI PK_LogMobiles is scanned into memory and only then a nested loop is performed. This is not optimal.

That is our issue. In step two PK_LogMobiles must be accessed via CI Seek, seeking only the 230768 logIDs out of the entire 5676246 logIDs. Instead the entire CI is scanned.

In Oracle such scenarios arise when the 230768 rows are scattered across too many pages (SQLServer) or blocks (Oracle) causing a major part / the entire CI to be scanned. This means that the records are not clustered enough for seeks. Improve the ClusteringFactor (Oracle Terminology) of your so called CI to resolve your performance issue.


Missing Non Clustered Index already part of Clustered Index I'm debugging a slow running query and in the execution plan a non-clustered index is suggested, with 51.6648 Impact. However, the non-clustered index only includes columns that are already in the Primary Key (PK) Composite Clustered Index.

Could this be because of the order of the columns in the index? i.e. if the columns in the clustered index are not in order from most selective to least then is there potential for a non-clustered index to improve performance?

In addition the non-clustered index only contains two of the three PK columns with the third added as an included column. Is the include another reason why use of the non-clustered index could be more optimal?

Below is an example of the table structures I am working with:

Tables-

Retailers (
    RetailerID int PK,
    name ...)

Retailer_Relation_Types (
    RelationType smallint PK,
    Description nvarchar(50) ...)

Retailer_Relations (
    RetailerID int PK FK,
    RelatedRetailerID int PK FK,
    RelationType smallint PK FK,
    CreatedOn datetime ...)

The table Retailer_Relations has the following composite PK index and suggested index-

CONSTRAINT PK_Retailer_Relations
PRIMARY KEY CLUSTERED (
    RetailerID ASC,
    RelatedRetailerID ASC,
    RelationType ASC
    ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX <NameOfIndex>
ON Retailer_Relations (
    RetailerID,
    RelationType
    )
INCLUDE (
    RelatedRetailerID
    )

The table Retailer_Relations has the following composite PK index and
  suggested index-

While missing indexes could be helpful and could definitely work, I would not spend too much time on missing indexes, these hints are created on the estimated execution plan, not on the actual execution plan.

More precisely, these index hints are based on the premise of reducing the cost of Query Bucks™ used by operators in the plan. The optimizer calculates the estimated costs, and adds missing index hints accordingly.

As a result they could be very wrong. If you are unsure if it is going to help, the best thing to do is test the situation before and after.
You could do this by adding the statement
SET STATISTICS IO, TIME ON; before running the query.

Also, you could use statisticsparser to make it easier to read these statistics.

  Could this be because of the order of the columns in the index?

That is correct, creating the missing index can improve the selectivity on queries, for example if your query looks like this:

SELECT  RelatedRetailerID
FROM Retailer_Relations
WHERE
RetailerID = 5 AND
RelationType = 20;

or like this:

SELECT  RelatedRetailerID
FROM Retailer_Relations
ORDER BY
RetailerID,
RelationType;

The reasoning behind this is that both indexes could seek on RetailerID, that part is not  going to change.
But what if extra filters/ordering is applied on RelationType?
It would be all over the place in the clustered index, as a result of it being the third key value, not the second key value. And as we know, it is the second key value in the NCI.

Okay, but when or how would the nonclustered index improve the query?

A couple of cases could be:

If relationType filters a lot of values, the residual I/O could be
high, resulting in the possible need of the nonclustered index (Query #1)
Ordering on the two columns occurs (One way), and the resultset is
large (Query #2).
As @AaronBertrand mentioned: if the CI size difference compared to the NCI is of a considerable amount, adding the NCI will reduce the pages read by queries that benefit from it.

NCI Side note

As a side note, adding the key columns to the include list in your NCI is not exactly needed, since CI key columns are automatically included in all Non clustered indexes.

You could opt to do so if you are not sure if the clustered index will remain the same, and want the column to always be included.

Regarding the query itself, if you added the execution plan via  PasteThePlan we could give some more information on indexing / improving the query.

Testing

Create table and add some rows

CREATE TABLE Retailer_Relations (
    RetailerID int ,
    RelatedRetailerID int ,
    RelationType smallint,
    CreatedOn datetime,
    CONSTRAINT PK_Retailer_Relations
PRIMARY KEY CLUSTERED (
    RetailerID ASC,
    RelatedRetailerID ASC,
    RelationType ASC
    ) ON [PRIMARY])

    DECLARE @I Int = 1
    WHILE @I < 1000
    BEGIN
    INSERT INTO Retailer_Relations(RetailerID,RelatedRetailerID,RelationType,CreatedOn)
    VALUES(@I,@I,@I,GETDATE()
    )
    set @I += 1
    END

Query #1

    SELECT  RelatedRetailerID
FROM Retailer_Relations
WHERE
RetailerID = 5 AND
RelationType = 20;

Plan without index Here

While it is doing a seek, it is doing a seek on RetailerID. Afterwards it is issueing a  residual I/O predicate on RelationType

Add the index

CREATE NONCLUSTERED INDEX IX_TEST
ON Retailer_Relations (
    RetailerID,
    RelationType
    )
INCLUDE (
    RelatedRetailerID
    )

The residual predicate is gone, everything happens in a seek predicate, on both columns.

Execution plan

With the second query, the added index helpfullness becomes even more obvious:

SELECT  RelatedRetailerID
FROM Retailer_Relations
ORDER BY
RetailerID,
RelationType;

Plan without the index, with a Sort operator:

Plan with the index, using the index removes the sort operator


How can I disable the notifications shown at bottom right corner in pgAdmin 4? The notification window at bottom right corner is pesky. Is it possible to disable it?

Changing the value in Query info notifier timeout to -1 it will disable it. This is documented in the official pgAdmin documentation in the Preferences Dialog help as:

  Use the Query info notifier timeout field to control the behaviour of the notifier that is displayed when query execution completes. A value of -1 will disable the notifier, and a value of 0 will display it until clicked. If a positive value above zero is specified, the notifier will be displayed for the specified number of seconds. The default is 5.


Query Editor Connection Error With Unattended Service Account ERROR:
When editing a report in report builder, opening the query editor gives the following error

You have specified integrated security or
credentials in the connection string for the
data source, but the data source is configured
to use a different credential type. To use the values
in the connection string, you must configure
the unattended report processing account for the report server.

SETUP:
1. SQL server 14.0.2027.2 (2017)
2. SSRS 14.0.600 .1274 (2017) configured with an unattended service account
3. Reporting portal is accessed through active directory login
4. The report uses a shared datasource with this connection string

Server=tcp:xxxxx;Database=yyyy;Integrated Security=true;
Encrypt=SSPI;Connection Timeout=30;MultipleActiveResultSets=True;
Persist Security Info=False;App=SSRS;

5.The Credentials are set to Without Any Credentials
6. Setup as documented by MSFT https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/configure-the-unattended-execution-account-ssrs-configuration-manager?view=sql-server-ver15

OBSERVATIONS:
 1. Viewing reports and subscriptions work as expected
 2. If i set credentials to As the user viewing the report, this fixes the issue but breaks subscriptions as they need either stored credentials or no credentials
 3. In report builder, doing a Test Connection on the data source, returns Connection created successfully

WORKAROUND:
 1. In report builder, changing the datasource from Use a shared connection or report model to  Use a connection embedded in my report with the exact same connection string as above allows the query editor to open. However i have to change it back to the using a shared connection after I make the required edits.

The workaround is not ideal for our user base and I would like to fix the underlying issue. Has any one experienced this issue before ?

I found this post while researching the same error message.
I am using 'SQL Server Data Tools for Visual Studio 2017’ My database is on SQL 2017, Enterprise.
The problem turned out to be that the credentials switched to "Do not use credentials" After I set it to "Use Windows Authentication" when I created the data source.
I changed it back (below) and saved, problem solved.
Data Sources > Rclick > Data Source Properties


Troubleshooting memory pressure - how to avoid paging out SQL Server? I have received the email below that states that an alert has been fired and

  a significant part of SQL Server process memory has been paged out

should I raise the Minimum server memory to 1740 too?

  Microsoft SQL Server 2005 - 9.00.5000.00 (X64)    Dec 10 2010 10:38:40
    Copyright (c) 1988-2005 Microsoft Corporation   Standard Edition
  (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

There are other applications, even monitoring applications running on that server.

You can have a glimpse of those applications by looking at the picture below.

We received an alert at 12:15:37 PM on the wrsleic1 default instance that "a significant part of SQL Server process memory has been paged out."

We've investigated the alert and found that SQL Server previously reported this condition last year on 13/04/2015. SQL Server is currently allocated a maximum memory of 1.7 GB while the server has 4 GB physical memory available to it (and a dynamically sized page file that is currently 5 GB).

At the time of the alert SQL Server had it's maximum memory reserved while not necessarily actively using the cached data. Therefore when the server detected memory pressures from an external process (potentially IIS but we can't determine this retrospectively), SQL Server trimmed its working set to accommodate the requirement and is currently utilising approximately 900 MB.

The OS is responsible for memory management and SQL Server is designed to respond to memory release requests as it did at the time of the alert. Therefore if there was no perceivable impact to performance on the application that uses the hosted databases on this instance then we don't recommend any further action.

Lock Pages in Memory

The lock pages in memory has not been enabled for any sql server account.

I was going to change this, however, I decided to leave this server as it is because:

1) it is running on standard edition sql server 2005 SP2 and on this page it says

  Standard Edition Up until SQL Server 2005 SP3 Cumulative Update
  Package 4 and SQL Server 2008 SP1 Cumulative Update Package 2 'Lock
  Pages in Memory' was not supported in SQL Server Standard Edition.

2) after enabling it - needs a reboot according to this page

  Question:         “Weather we require Lock Pages in Memory in SQL
  Server 64-Bit”?

  Answer:            YES

3) We should (hope so) migrate this server to sql server 2014 soon

this page has a bit more about lock pages in memory and a reminder:

a significant part of sql server process memory has been paged out

Let me explain you what this message means. This means SQL Server process was paged to disk because the OS or applications running on OS requested memory and OS could not find continuous chunk of it so paged SQL Server to disk, temporarily, and provided the physical memory to the process requesting.

If you read Karthick PK's blog there are couple of situations where SQL Server Process working set might be paged out by Windows

If windows is not sending the correct notifications to all listening processes at the right moment and thresholds.
If SQL Server is not responding fast enough to the low memory resource notification from Windows.
When low physical memory notification is received by SQL Server it will scale down its memory usage by trimming internal caches. This effect is applied for 5 seconds and then paused for 1 minute. This is to avoid any hungry/faulty application to consume all the memory making SQL Server to  scale its usage continuously. If low memory conditions still exist after 1 minute pause, the effect is applied again. So if there is physical memory pressure even after SQL Server scaling its usage for 5 seconds windows will still page out SQL Server’s working set.
Conditions in Windows where working sets of all processes are trimmed.
Windows might decide to trim a certain percentage of working set of various or specific processes

Why did SQL Server allowed OS to take away its memory, well this is how SQL Server is designed.

  should I raise the Minimum server memory to 1740 too?

I don't know from where you got this idea, min server memory has no role here don't touch it. Its OS which is facing memory pressure here NOT the SQL Server.

  We received an alert at 12:15:37 PM on the wrsleic1 default instance that "a significant part of sql server process memory has been paged out."

As I can read your question you got this message previous year and one occurrence now. Keeping its mind in occurrence I would suggest its not that grave issue something running on OS sometimes requests more memory and SQL Server becomes victim.

Workaround

The workaround would be here to provide SQL Server service account Locked pages in memory privilege. This will avoid paging out of buffer pool but non buffer pool can still be paged out.

Solution.

Find out what is that which is running on system and requesting more memory. Perfmon counters Process: Private bytes and Working set can come handy here.

Or download RAMMAP tool, that is there is Karthick's blog, and see memory consumption.

What I can feel is there are other services running on SQL Server machine and sometimes there occurs situation where memory requirement increases and thus forcing OS to take hard step of paging. I suggest you also add more RAM on windows machine


Looping over column names in SQLite and running a command for each column name I'm trying to run the following command

UPDATE attachments SET colname=TRIM(colname);

on every column name in a SQLite table.

I found the following approach to getting a list of column names in
an answer to "How to get a list of column names on sqlite3 / iPhone?", namely

SELECT name FROM PRAGMA_TABLE_INFO('your_table');

which seems like a nice, clean, simple way to do it. But this method is specific to SQLite, per PRAGMA Statements.

In any case, I'd like a solution that runs entirely in SQLite. I'm actually calling SQLite from Lua, using LuaSQL. So I could run the loop in Lua if necessary. But doing it inside SQLite just seems cleaner.

It would be even better if it's a general method that works for any RDBMS. But that may not be possible, and if it is, it would probably be more complicated

You can't do it in pure sqlite; column names have to be hardcoded in the query (also why they can't be bound to parameters), even if there was a way to use UPDATE in a loop, which there isn't.


How to change default db2 database admin username to something else in Linux? I have a DB2 database version 11.1 on Linux - rhel 7.7 in my test environment. I need help on how to change the default db2 admin username to something else, ie. db2inst1 to dbroot. And the new db2 user - dbroot should have the same privileges which old db2 user - db2inst1 had. Please share the steps to do the needful tasks.

Thanks in advance

It is not possible to directly rename the db2 instance name, But following steps can be used to move the db to new instance

  Take the backup of DB's and provide 755 permissions to backup images. Offline backup is recommended
  db2stop command to stop the instance
  drop current instance name by using db2idrop command
  create new instance by using db2icrt command
  once instance is created, switch to the instance id and  please start the instance by db2start command
  Restore the db to new instance name by using below command incase of auto storage
   db2 restore db  from  ON  NEWLOGPATH
  If there are user defined tablespaces, Which will need redirect restore
  db2 connect to

or Run below command to grant DBADM access to dbroot.

db2 " grant DBADM WITH DATAACCESS on database to user dbroot";


Unknown system variable 'show_compatibility_56' I like backupninja because of simplicity. Unfortunately it started giving me this problem:

  Warning: mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */
  dead.letter FROM GLOBAL_STATUS': The
  'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the
  documentation for 'show_compatibility_56' (3167)

It looks like this can be fixed with set @@global.show_compatibility_56=ON. Unfortunately that doesn't work on my MariaDB instance:

  Welcome to the MariaDB monitor.  Commands end with ; or \g. Your
  MariaDB connection id is 73 Server version: 10.1.26-MariaDB-0+deb9u1
  Debian 9.1

  Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

  MariaDB [(none)]> set @@global.show_compatibility_56=ON;
  ERROR 1193 (HY000): Unknown system variable 'show_compatibility_56'

What else can I try to resolve this?

This has been fixed so a later version (>1.0.2) of backupninja will likely resolve your issue.


Inserting data into table in server B whenever table in server A gets updated I want to synchronize data from table 1 in server A to tables 2 and 3 in server B, whenever table 1 gets updated.

Tables from server A are not related to tables from server B, so I'd have to specify which columns in table 1 correspond to which columns in tables 2 and 3, and perhaps do some sort of operation on that data.

Server A, Table Y

Name  | Country | Sales
Oscar | Peru    | 5000

Server B, Table X

Name  | Region | Amount | Currency | Multiplier | TotalAmount
Oscar | Peru   | 5000   | €        | 5          | 25000 (=5000*5)

The goal here is to insert the record from server A to the table X in server B.

The 'Name' field maps to the 'Name' field
The 'Country' field maps to the 'Region' field
The 'Sales' field maps to the 'Amount' field
The 'Currency' field in server B will always be €
The 'Multiplier' field would be retrieved from another table in server B
The 'TotalAmount' field would be the result of Amount * Multiplier

Would this be possible to automate? Is using triggers the way to go?

I'm using SQL Server (and SMSS). The sync doesn't need to be instantaneous, it could take up to 10 or 20 minutes. The two servers are already linked. Server B is not used just for read only queries.

Normally you would use materialized views for sync'ing tables, however your table structures are different which probably rules out this solution. The next solution would be to implement insert/update/delete triggers on your source table. This can cause performance bottlenecks on high volume transactions. The third solution is to implement ETL (extract/transform/load). There are a number of commercial and open source solutions. Informatica, GoldenGate from Oracle are a couple of commercial options, Telend, Jaspersoft ETL, KETL are great open source. http://www.butleranalytics.com/30-etl-tools/ has a list and links to some ETL tools.