MySql InnoDB tables are missing after restarting

If you have MySQL 5.6 running before, then the MySQL data is located in C:\Program Files\MySQL\MySQL Server 5.6\data\

If you had recently installed MySQL 5.7, that instance has its own MySQL data in C:\Program Files\MySQL\MySQL Server 5.7\data\.

You need to migrate the data from MySQL 5.6 into MySQL 5.7.

If you are not sure what what version of MySQL was actually running on your Windows Server, here are two things you must investigate:

INVESTIGATION #1

Goto to Windows Explorer
Navigate to C:\Program Files\MySQL

INVESTIGATION #2

For every folder under C:\Program Files\MySQL

Go inside the subfolder MySQL 5.x
Locate the file my.ini

Whichever folder has my.ini was in the installed version of MySQL

If none of them has my.ini, you have to dig a little deeper

INVESTIGATION #3

Click Start -> Control Panel -> Administrative Tools -> Services
Once the Services panel is open, scroll down alphabetically to MySQL.
Right click on MySQL
Choose Properties

This will show you the path to the MySQL binary it called and the my.ini associated with it. If it does not show a my.ini, then the my.ini should appear in the parent directory of the executable.

If this does not work, then you will have to look inside MySQL Workbench for the original connection settings you were connecting with.


why processlist list shows limited threads

I interpret your results to say that only 266 connections existed at that moment.  (If there aren't 500 users trying to connect, there won't be 500 rows in the processlist.)
Were you getting some error from other attempted connections not being accepted?  If so, what was the exact text of the error message.
266 connections, each running a query, is a problem.  I highly recommend you find the full text of the query (or queries) involved.  SHOW FULL PROCESSLIST may be useful if the queries were truncated.  Then show us the query, EXPLAIN SELECT ... and SHOW CREATE TABLE.  It is likely that some new INDEX (possibly composite)` would make them run faster -- and you might never see as many as 266 again.


How does a integer data record store in SQL Server page

One important thing to keep in mind is that rowstore tables have a minimum row size of 9 bytes. You can see some details about that in the answer and comments here. If you're going to be creating sample data and digging around in pages I recommend creating at least a few columns to make what you're looking at more clear. Otherwise you can run into a case where a table with a single TINYINT column appears to take up as much space as a table with a single SMALLINT, which doesn't make sense at first.

CREATE TABLE dbo.X_TINYINT (ID SMALLINT NOT NULL);
CREATE TABLE dbo.X_SMALLINT (ID SMALLINT NOT NULL);

INSERT INTO dbo.X_TINYINT WITH (TABLOCK)
SELECT TOP (100000) 0
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

INSERT INTO dbo.X_SMALLINT WITH (TABLOCK)
SELECT * FROM dbo.X_TINYINT;

sp_spaceused 'X_TINYINT'; -- 1096 KB reserved

sp_spaceused 'X_SMALLINT'; -- 1096 KB reserved


Database structure for a large amount of data with different types

You didn't mention whether your parameters are dynamic and variable.  If not then the correct logical design is:

(id,ts,speed,temp,pressure,status)

And the correct physical design is a clustered columnstore with a non-clustered primary key on (id,ts).  Possibly partitioned by id or ts into ranges.

If your parameters are variable, you should consider using JSON over using a Key-value-pair across multiple rows.

(id,ts,telemetry_json)

or

(id,ts,telemetry_json_gzip)

With the telemetry data stored in JSON key-value pairs, and/or JSON arrays.  Not only is this model efficient and handy, you can use a Value Conversion in EF Core to have a complex .NET type serialized to/from JSON for storage in the database.

You can use

(id,ts,attribute,value)

but this pattern is generally not the most efficient, or convenient, and is widely frowned-upon.  There many discussions on this site and others regarding the Entity-Attribute-Value model.


Does the recovery model affect query speed?

No, the transaction log behaves and performs the same way in all recovery models, the only difference is when log truncation occurs.

The MSDN article on the transaction log provides detail on this.


What is the best use of memory in MySQL?

This strongly depends on the application using the database. A good starting point for optimization is the MySQL Performance Tuning Primer Script


How to access to other INSERTed/UPDATEd rows from trigger in PostgreSQL?

See the docs, you should be able to access the old and new records from a statement trigger:

CREATE TRIGGER some_table_update_trigger
  AFTER UPDATE ON some_table
  REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
  FOR EACH STATEMENT
  EXECUTE PROCEDURE do_something_with_newtab_and_oldtab();


Checking for dupes on Compact Insert Statement

Why insert into tblusers directly at all?

I always use staging tables. You can use SSIS of course for the same result at with greater complexity

INSERT INTO [staging].[Users]
       ([username], [password])
VALUES ('user1', 'pass1'),
       ('user2', 'pass2')

INSERT INTO [tblUsers]
       ([username], [password])
SELECT DISTINCT [username], [password] --edit, added DISTINCT
FROM [staging].[Users] SU
WHERE NOT EXISTS (SELECT * FROM [tblUsers] U WHERE ...)


Connect to Microsoft Access database via Microsoft SQL Server / MySQL

An access database is not a server, it's just a file.  To access it, your server will need access to the filesystem on which it resides.  Or, if you want to set up an SQL server on the same machine as the access database, it will still need access to the folder in which the file is stored.

A user logged into a VPN may not have the same drive mappings or file system permissions as a server process running on the same machine, so you will need to reflect this in the path to the file you use in the code.


Upgrade SQL Server 2014 AG to SQL Server 2017 AG

As per the msdn link here, the answer is very much or possibly yes. I have not tried this out so I cannot assure that it's 100 percent will work out. It suggest that if you have a test environment go ahead and setup scenario like this and give it a shot. I is Just like how mirroring works, it's expected to be same or similar to logshipping. But make sure that the secondary is not readable as the secondary replica will be in higher SQL Server version .


I inadvisable to use Datetime type for your answer_time, change it to TIME type because the 000-00-00 will fail with all datetime functiosn (look at the MySQL documentation : link).

So, when your table looks like :

[09:47:55] [DEV\MFO] mysql> desc MFO.phone_call;
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| phone       | bigint(20) | YES  |     | NULL    |       |
| answer_time | datetime   | YES  |     | NULL    |       |
+-------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

You could try that :

SELECT phone,
   Count(*),
   Count(CASE
           WHEN Time(answer_time) = 0 THEN 1
         end) AS NOT_ANSWERED,
   Count(CASE
           WHEN Time(answer_time) > 0 THEN 1
         end) AS ANSWERED
FROM   MFO.phone_call
GROUP  BY phone;

+-------+----------+--------------+----------+
| phone | count(*) | NOT_ANSWERED | ANSWERED |
+-------+----------+--------------+----------+
| 11111 |       21 |           10 |       11 |
| 22222 |        2 |            0 |        2 |
+-------+----------+--------------+----------+
2 rows in set (0.00 sec)

Max.


The type InstalmentList is a nested table of InstalmentEntry, so you need to build a set of InstalmentEntry objects to get the list.

The following query works on 11.2:

SELECT column_value AS list, rownum AS rownumber
  FROM TABLE(powermultiset(
               CAST(MULTISET (SELECT InstalmentEntry(1, 1) /*Entry "constructor"*/
                                FROM dual)
                    AS InstalmentList)
             ));


What is lost when I create a Foreign Key using `WITH NOCHECK`?

As you have discovered with your exists example, SQL Server can use the fact that a foreign key is trusted when the query plan is built.

  Is there anything else I lose by using NOCHECK?

Apart from the fact that you can add values to a column that should not be there as answered by Ste Bov you will have more scenarios where the query plan will be better when the foreign key is trusted.

Here is one example with an indexed view.

You have two tables with a trusted FK constraint.

create table dbo.Country
(
  CountryID int primary key,
  Name varchar(50) not null
);

create table dbo.City
(
  CityID int identity primary key,
  Name varchar(50),
  IsBig bit not null,
  CountryID int not null
);

alter table dbo.City
  add constraint FK_CountryID
  foreign key (CountryID)
  references dbo.Country(CountryID);

There are not so many countries but a gazillion of cities and some of them are big cities.

Sample data:

-- Three countries
insert into dbo.Country(CountryID, Name) values
(1, 'Sweden'),
(2, 'Norway'),
(3, 'Denmark');

-- Five big cities
insert into dbo.City(Name, IsBig, CountryID) values
('Stockholm', 1, 1),
('Gothenburg', 1, 1),
('Malmoe', 1, 1),
('Oslo', 1, 2),
('Copenhagen', 1, 3);

-- 300 small cities
insert into dbo.City(Name, IsBig, CountryID)
select 'NoName', 0, Country.CountryID
from dbo.Country
  cross apply (
              select top(100) *
              from sys.columns
              ) as T;

The most often executed queries in this application is related to finding the number of big cities per country. To speed things up with that we add an indexed view.

create view dbo.BigCityCount with schemabinding
as
select count_big(*) as BigCityCount,
       City.CountryID,
       Country.Name as CountryName
from dbo.City
  inner join dbo.Country
    on City.CountryID = Country.CountryID
where City.IsBig = 1
group by City.CountryID,
         Country.Name;

 go

create unique clustered index CX_BigCityCount
  on dbo.BigCityCount(CountryID);

After a while comes a demand of adding a new country

insert into dbo.Country(CountryID, Name) values(4, 'Finland');

The query plan for that insert has no surprises.

A clustered index insert to the Country table.

Now, if your foreign key was not trusted

alter table dbo.City nocheck constraint FK_CountryID;

and you add a new country

insert into dbo.Country(CountryID, Name) values(5, 'Iceland');

you would end up with this not so pretty picture.

The lower branch is there to update the indexed view. It does a full table scan of City to figure out if the country with CountryID = 5 already has rows in the table City.

When the key is trusted, SQL Server knows there can be no rows in City that would match the new row in Country.


SA permissions issues with many nested objects

Can tell you solution I came up with in situation like yours (its quite complicated, but still love it):

I have database DB1 with Service Broker an DB2 where data has to be updated as well (I do have triggers in DB2 as well).

I am using certificate to create server login + users in each db. I am signing procedures and triggers to Grant rights. You can read everything you need here.

-- Master DB:
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Example'
Go
BACKUP CERTIFICATE MyCertificate TO FILE = 'c:\Cert.cer'
    WITH PRIVATE KEY (
    FILE = 'c:\CertKey.key',
    ENCRYPTION BY PASSWORD = 'pwd');
Go
Create Login CertLogin From Certificate MyCertificate

-- DB1:
CREATE CERTIFICATE MyCertificate
FROM FILE = N'c:\Cert.cer'
WITH PRIVATE KEY
    (
        FILE = N'c:\CertKey.key',
        DECRYPTION BY PASSWORD = N'pwd'
    );

Create User CertUser From Login CertLogin;

-- DB2:
CREATE CERTIFICATE MyCertificate
FROM FILE = N'c:\Cert.cer'
WITH PRIVATE KEY
    (
        FILE = N'c:\CertKey.key',
        DECRYPTION BY PASSWORD = N'pwd'
    );
Go
CREATE USER [CertUser] FROM Login CertLogin;

To grant rights (In my case no need for Select/Insert/Update/Delete rights because all tables and procedures in DB2 is in the same schema. So ownership chaining in DB2):

-- DB1:
Grant Exec On dbo.usp_MyProc_DB1 To SomeUser -- User that calls procedure
ADD SIGNATURE TO dbo.usp_MyProc_DB1 BY CERTIFICATE MyCertificate;
-- DB2:
ADD COUNTER SIGNATURE TO dbo.usp_MyProc_DB2 BY CERTIFICATE MyCertificate;
ADD COUNTER SIGNATURE TO tr_MyTrigger_DB2 BY CERTIFICATE MyCertificate;
Grant Exec On usp_CheckPatientDetailedRole To CertUser;


Remove all records with duplicates in db2. (Not just the duplicate records)

Assuming your desired uniqueness constraint is (one,two), here is one way to do it:

DELETE FROM session.test WHERE (one, two) IN
(
   SELECT one, two FROM session.test
   GROUP BY one, two
   HAVING COUNT(*) > 1
)


Just because you have a covering index doesn't mean it won't scan, nor does it mean that a scan isn't still the best way to access the requested data.

You're returning, what, 3 million rows? Even if that's not most of the table, it still wouldn't make much sense to perform 3 million seek operations, nor is it likely there would be any benefit in seeking to the first row and performing a range scan for the rest. There is also the issue that multiple EXISTS clauses - multiple of which do not join to the index's leading key column - will not likely all be able to take advantage of the same output from the seek. And you have filters on all of the INCLUDE columns - they're not part of the key, so seeks on those columns are going to be difficult (in fact the only case where I've seen an index still be used to seek when there are WHERE clauses against non-key columns is when the column was BIT, and those scenarios did not introduce all the other variables you have above). You're also using PIVOT and aggregating, which I'm sure don't help.

Also, what table is CodCampo in (why isn't it properly prefixed with a table alias)? You may have one or both of the following two problems:

It is a disjunction (OR clause) which can discourage seeks (see Paul's answer here).
If it is in tblcliente, then your index is not covering anyway.

You could try running the query with the FORCESEEK hint (also mentioned in Paul's example) and it will either fail to produce a plan (unlikely) or you will get a plan that you can share with us and, if you also share the scan plan you get without the hint, we can give further insight into why SQL Server chose the scan instead.

As an aside (this certainly won't help you get a seek), since the estimates were far lower, I'd check that your statistics are current.

And finally, don't always assume that a scan is bad and something you need to eliminate! In a lot of cases, a scan really is the best way to retrieve the data.


First, since you already have a UNIQUE index that contains the user_id, you should be able to get rid of the id field, and use the UNIQUE index as the PRIMARY KEY:

CREATE TABLE `twitter_relationships` (
  `user_id` int(11) NOT NULL,
  `source_twitter_id` bigint(20) NOT NULL,
  `target_twitter_id` bigint(20) NOT NULL,
  `relationship_status` tinyint(1) NOT NULL,
  `status_change_date` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`,`source_twitter_id`,`target_twitter_id`),
  KEY `target_status_and_change_date_index`
    (`user_id`,`target_twitter_id`,`relationship_status`,`status_change_date`),
  KEY `user_id_index` (`user_id`,`status_change_date`)
) ENGINE=InnoDB AUTO_INCREMENT=116597775 DEFAULT CHARSET=latin1
PARTITION BY HASH (user_id) PARTITIONS 1000;

Unfortunately, while this removes an index, it may increase storage requirements, due to the way that InnoDB indexes data. See "How Secondary Indexes Relate to the Clustered Index" in http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html

Second, while the source_and_target index has two of the three fields in your WHERE clause, MySQL will have to do an additional read to find the relationship_status.

Therefore, to improve performance, create an index that includes all three fields in your WHERE clause:

CREATE INDEX user_source_status ON twitter_relationships
    (`user_id`,`source_twitter_id`,`relationship_status`);

Then, if MySQL doesn't use this index automatically, you can force using it, with:

SELECT target_twitter_id
 FROM `twitter_relationships` FORCE INDEX (user_source_status)
WHERE (`twitter_relationships`.`user_id` = ?
   AND `twitter_relationships`.`source_twitter_id` = ?
   AND `twitter_relationships`.`relationship_status` = ?)
LIMIT ?, ?

Lastly, you're missing the UNSIGNED attribute on the id, user_id, source_twitter_id, and target_twitter_id fields. I'm guessing these fields will never store negative values, so it would make sense to make them UNSIGNED.


How can I provision more CPU from VMware when MAXDOP is set to 1?

Are all of my assumptions above correct?

Yeah, you're correct on all counts as far as I can tell.

If so, does that mean there is no way to boost the CPU available to these queries?

Given the constraints you've described, the only way to "boost the CPU" for those queries would be to move the VM to a host with a CPU that has a faster clock speed.

Is there anything else we can do?

Your best bet would probably be to identify the problematic queries and set up plan guides to add MAXDOP hints to those specific queries.  This will override the server level setting, so those specific queries would go parallel, while the rest would still be at DOP 1.
Some other options, that are less ideal / more likely to cause problems:

Increase server level MAXDOP from 1 - this goes against the config guidance of course, but it is an available option to test and see if it benefits these problematic queries without causing much harm to other queries
Add more CPUs - this doesn't help individual queries that are CPU-bound, but it will allow more queries to run at once potentially.  This could help or hurt depending on the details of the workload.  For example, running more queries at once might just lead to contention (locks or tempdb or something else) rather than increased throughput


How to setup Master to Master Replication with Application auto failover

Generally Im not a fan of Multi-Master. Since native MySQL multi-master setup will not handle the conflict detection. Im not sure about your workload, but from my personal experience, a single MySQL master can handle 30k-50k QPS. If you stick with Multi-Master think about Percona XtraDB Cluster or MySQL Group Replication.
In your case, you need a load balancer on top of mysql nodes to server the traffic without downtime.
Setup Basic Multi-Master:
There are so many articles on the internet to setup Multi-Master. I request you to use enable primary key on all tables(Innodb works better with PK). Use auto increment is ODD for Master 1, and Even identity Numbers for Master2.

[NOTE: if you have more than 2 masters, then ignore this part, it
won't work out. Its a simple to avoid conflict detections(Not all the
time)]

ProxySQL:
This is my favorite option, This will work as a Load balancer and so many features. Put both masters in the same host group. So proxysql will balance the traffic to both the servers.
HA Proxy:
I didn't use this before, but this will act as a normal load balancer. It won't any magic like ProxySQL.
Network Load balancer[If you are in Cloud]:
If you are using AWS, GCP or azure, they have inhouse load balancers. We can use them as well.


Should I mark a composite index as unique if it contains the primary key?

Should I mark a composite index as unique if it already contains the primary key?

Probably not. The optimizer can generally use information about the uniqueness of the contained key column anyway, so there's no real advantage.

There is also an important consequence of marking an index unique on update plans that modify keys of that index to consider:

Setup

CREATE TABLE dbo.Customers
(
   CustomerID int NOT NULL PRIMARY KEY,
   FirstName nvarchar(50),
   LastName nvarchar(50),
   [Address] nvarchar(200),
   Email nvarchar(260)
);

CREATE NONCLUSTERED INDEX
    IX_Customers_CustomerIDEmail
ON dbo.Customers
(
   CustomerID,
   Email
);

-- Pretend we have some rows
UPDATE STATISTICS dbo.Customers
WITH ROWCOUNT = 100000, PAGECOUNT = 20000;

Per-index update plan (non-unique index)

UPDATE dbo.Customers
SET Email = N'New', [Address] = 'New Address'
WHERE Email = N'Old'
OPTION (QUERYTRACEON 8790); -- Per-index update plan

Execution plan:

The optimizer often makes a cost-based decision between updating nonclustered indexes per-row (a 'narrow' plan) or per-index (a 'wide' plan). The default strategy (except for in-memory OLTP tables) is a wide plan.

Narrow plans (where nonclustered indexes are maintained at the same time as the heap/clustered index) are a performance optimization for small updates. This optimization is not implemented for all cases - using certain features (like indexed views) means that the associated index(es) will be maintained in a wide plan.

More information: Optimizing T-SQL Queries that Change Data

In this case, I have used undocumented trace flag 8790 to force a wide update plan: The plan therefore shows the clustered and nonclustered indexes being maintained separately.

The Split turns each update into a separate delete & insert pair; the Filter filters out any rows that would not result in a change to the index.

More information: (Non-updating updates) by the SQL Server QO Team.

Per-index update plan (unique index)

-- Same index, but unique
CREATE UNIQUE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   CustomerID,
   Email
)
WITH (DROP_EXISTING = ON);

UPDATE dbo.Customers
SET Email = N'New', [Address] = 'New Address'
WHERE Email = N'Old'
OPTION (QUERYTRACEON 8790); -- Per-index update plan

Execution plan:

Notice the extra Sort and Collapse operators when the index is marked unique.

This Split-Sort-Collapse pattern is required when updating the keys of a unique index, to prevent intermediate unique key violations.

More information: Maintaining Unique Indexes by Craig Freedman

The Sort in particular can be a problem. Not only is it an unnecessary extra cost, it may spill to disk if estimates are inaccurate.

About nonclustered keys

Another factor to consider is that nonclustered index structures are always unique, at every level of the index, even if UNIQUE is not specified. The clustering key(s) - and possibly a uniquifier if the clustered index is not marked unique - are added to a non-unique nonclustered index at all levels.

As a consequence, the following index definiton:

CREATE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   Email
)
WITH (DROP_EXISTING = ON);

...actually contains the keys (Email, CustomerID) at all levels. It is therefore 'seekable' on both columns:

SELECT *
FROM dbo.Customers AS C WITH (INDEX(IX_Customers_CustomerIDEmail))
WHERE C.Email = N'Email'
AND C.CustomerID = 1;

More information: More About Nonclustered Index Keys by Kalen Delaney


What is "special" about PostgreSQL update vs delete+insert

Yes, there is a difference between UPDATE and DELETE + INSERT.
Let's use the pageinspect extension to look at the tuples and the tuple headers.
If you want to repeat my experiment, you have to drop and re-create the table in between. Also, there may be additional flags (hint bits) if you selected the rows before examining them.
The meaning of infomask2 and infomask can be found in src/include/access/htup_details.h, see the quotations at the end of the answer.
After the UPDATE:
SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask2, t_infomask, t_attrs
FROM heap_page_item_attrs(get_raw_page('example', 0), 'example');

 lp | t_xmin | t_xmax | t_ctid | t_infomask2 | t_infomask |            t_attrs
----+--------+--------+--------+-------------+------------+-------------------------------
  1 | 380943 | 380944 | (0,2)  |       16386 |        256 | {"\\x01000000","\\x02000000"}
  2 | 380944 |      0 | (0,2)  |       32770 |      10240 | {"\\x01000000","\\x02000000"}
(2 rows)

The first tuple is the dead one. Its t_ctid has been changed to point to the updated version.
This is one of the key points, so let me expand on this: the ctid of a tuple is the combination of the block number and the “line pointer” (lp in the query result. t_ctid is normally redundant, but in this case it is used to point to the new row version. This is the link between the original tuple and the updated version.
t_infomask2 is 2 (the number of columns) plus HEAP_HOT_UPDATED, so this row received a HOT update (there was enough space in the block, and there is no index). t_infomask is HEAP_XMIN_COMMITTED (a hint bit).

The second tuple is the new version.
t_infomask2 is 2 plus HEAP_ONLY_TUPLE, so this is  “heap-only tuple” that is only reachable via the updated ctid of the old version. t_infomask is HEAP_XMAX_INVALID (true, it is 0) plus HEAP_UPDATED (this is the updated version).

After the DELETE + INSERT:
SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask2, t_infomask, t_attrs
FROM heap_page_item_attrs(get_raw_page('example', 0), 'example');

 lp | t_xmin | t_xmax | t_ctid | t_infomask2 | t_infomask |            t_attrs
----+--------+--------+--------+-------------+------------+-------------------------------
  1 | 380958 | 380961 | (0,1)  |        8194 |        256 | {"\\x01000000","\\x02000000"}
  2 | 380961 |      0 | (0,2)  |           2 |       2048 | {"\\x01000000","\\x02000000"}
(2 rows)

Again, the first tuple is the dead one.
t_infomask2 is 2 plus HEAP_KEYS_UPDATED (this is a deleted or updated tuple), and t_infomask is HEAP_XMIN_COMMITTED (the tuple was valid before it was deleted).

The second tuple is the inserted one:
t_infomask2 is 2 plus, and t_infomask is HEAP_XMAX_INVALID (it is 0), so this is a new tuple.

Explanation of the observed difference:
In READ COMMITTED isolation level, a transaction always sees the latest committed version of a row. The DELETE in session B has to lock the row and is blocked by the UPDATE or DELETE in session A.
The documentation explains what happens when the lock is released:

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row.

In the case of the UPDATE there is a link between the old and the new row version, so PostgreSQL locks and deletes the new row version, while in the case of DELETE + INSERT there is no valid version of the row after the lock is gone, and nothing is deleted.
So while in many respects UPDATE and DELETE + INSERT are quite similar in PostgreSQL, they are not the same: in the second case, there is no connection between the deleted and the inserted row.
Appendix: the meaning of infomask and infomask2
t_infomask:
/*
 * information stored in t_infomask:
 */
#define HEAP_HASNULL            0x0001  /* has null attribute(s) */
#define HEAP_HASVARWIDTH        0x0002  /* has variable-width attribute(s) */
#define HEAP_HASEXTERNAL        0x0004  /* has external stored attribute(s) */
#define HEAP_HASOID             0x0008  /* has an object-id field */
#define HEAP_XMAX_KEYSHR_LOCK   0x0010  /* xmax is a key-shared locker */
#define HEAP_COMBOCID           0x0020  /* t_cid is a combo cid */
#define HEAP_XMAX_EXCL_LOCK     0x0040  /* xmax is exclusive locker */
#define HEAP_XMAX_LOCK_ONLY     0x0080  /* xmax, if valid, is only a locker */

 /* xmax is a shared locker */
#define HEAP_XMAX_SHR_LOCK  (HEAP_XMAX_EXCL_LOCK | HEAP_XMAX_KEYSHR_LOCK)

#define HEAP_LOCK_MASK  (HEAP_XMAX_SHR_LOCK | HEAP_XMAX_EXCL_LOCK | \
                         HEAP_XMAX_KEYSHR_LOCK)
#define HEAP_XMIN_COMMITTED     0x0100  /* t_xmin committed */
#define HEAP_XMIN_INVALID       0x0200  /* t_xmin invalid/aborted */
#define HEAP_XMIN_FROZEN        (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)
#define HEAP_XMAX_COMMITTED     0x0400  /* t_xmax committed */
#define HEAP_XMAX_INVALID       0x0800  /* t_xmax invalid/aborted */
#define HEAP_XMAX_IS_MULTI      0x1000  /* t_xmax is a MultiXactId */
#define HEAP_UPDATED            0x2000  /* this is UPDATEd version of row */
#define HEAP_MOVED_OFF          0x4000  /* moved to another place by pre-9.0
                                         * VACUUM FULL; kept for binary
                                         * upgrade support */
#define HEAP_MOVED_IN           0x8000  /* moved from another place by pre-9.0
                                         * VACUUM FULL; kept for binary
                                         * upgrade support */
#define HEAP_MOVED (HEAP_MOVED_OFF | HEAP_MOVED_IN)

#define HEAP_XACT_MASK          0xFFF0  /* visibility-related bits */

t_infomask2:
/*
 * information stored in t_infomask2:
 */
#define HEAP_NATTS_MASK         0x07FF  /* 11 bits for number of attributes */
/* bits 0x1800 are available */
#define HEAP_KEYS_UPDATED       0x2000  /* tuple was updated and key cols
                                         * modified, or tuple deleted */
#define HEAP_HOT_UPDATED        0x4000  /* tuple was HOT-updated */
#define HEAP_ONLY_TUPLE         0x8000  /* this is heap-only tuple */

#define HEAP2_XACT_MASK         0xE000  /* visibility-related bits */


how to bring in the most recent record in SQL in Teradata

This is a ranking task, easily done based on RANK/ROW_NUMBER.

select ...
from
 ( select ...
      ,rank() over (partition by Store order by Inventory_year desc) as rn
   from tab
 ) as dt
where rn = 1

Teradata supports an SQL extension to avoid nesting:

select *
from tab
QUALIFY rank() over (partition by Store order by Inventory_year desc) = 1


First of all, your runtime error is based on COUNT (Num). It should be COUNT(Num)

SAMPLE DATA

mysql> DROP DATABASE IF EXISTS guru;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE guru;
Query OK, 1 row affected (0.00 sec)

mysql> USE guru
Database changed
mysql> CREATE TABLE logs
    -> (id int not null auto_increment,
    -> num int not null,
    -> primary key (id));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM logs;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   2 |
|  5 |   1 |
|  6 |   2 |
|  7 |   2 |
+----+-----+
7 rows in set (0.00 sec)

YOUR QUERY EXECUTED

mysql> SELECT Num FROM Logs GROUP BY Num HAVING COUNT(Num) > 1;
+-----+
| Num |
+-----+
|   1 |
|   2 |
+-----+
2 rows in set (0.00 sec)

mysql>

LOOK AT THE QUESTION AGAIN

Write a SQL query to find all numbers that appear at least three times consecutively

From the output in your question, 2 does not occur three times consecutively.

I have an iterative solution using user-defined variables

MY PROPOSED QUERY

SET @curnum = -999;
SET @same = 1;
SET @group = 0;
SELECT DISTINCT num FROM
(
    SELECT grp,num,MAX(samecount) same FROM
    (
        SELECT
            (@group := @group + IF(@curnum=num,0,1)) grp,
            (@same  := IF(@curnum=num,@same+1,1)) samecount,
            (@curnum:=num),num
        FROM logs ORDER BY id
    ) AA GROUP BY grp,num HAVING same >= 3
) A;

Looks messy, right ? Here is is executed

MY PROPOSED QUERY EXECUTED

mysql> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT num FROM
    -> (
    ->     SELECT grp,num,MAX(samecount) same FROM
    ->     (
    ->         SELECT
    ->             (@group := @group + IF(@curnum=num,0,1)) grp,
    ->             (@same  := IF(@curnum=num,@same+1,1)) samecount,
    ->             (@curnum:=num),num
    ->         FROM logs ORDER BY id
    ->     ) AA GROUP BY grp,num HAVING same >= 3
    -> ) A;
+-----+
| num |
+-----+
|   1 |
+-----+
1 row in set (0.01 sec)

mysql>

WHY IT WORKS

Let's strip all the aggregation and subquery stuff and start with the innermost query

SET @curnum = -999;
SET @same = 1;
SET @group = 0;
SELECT
    (@group := @group + IF(@curnum=num,0,1)) grp,
    (@same  := IF(@curnum=num,@same+1,1)) samecount,
    (@curnum:=num),num
FROM logs ORDER BY id;

When you run this, here is the output

mysql> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     (@group := @group + IF(@curnum=num,0,1)) grp,
    ->     (@same  := IF(@curnum=num,@same+1,1)) samecount,
    ->     (@curnum:=num),num
    -> FROM logs ORDER BY id
    -> ;
+------+-----------+----------------+-----+
| grp  | samecount | (@curnum:=num) | num |
+------+-----------+----------------+-----+
|    1 |         1 |              1 |   1 |
|    1 |         2 |              1 |   1 |
|    1 |         3 |              1 |   1 |
|    2 |         1 |              2 |   2 |
|    3 |         1 |              1 |   1 |
|    4 |         1 |              2 |   2 |
|    4 |         2 |              2 |   2 |
+------+-----------+----------------+-----+
7 rows in set (0.00 sec)

mysql>

Note the column samecount. When samecount reaches 3 or more, the num column has the value that has been repeated 3 or more times.

Adding the aggregation layer, you get this:

mysql> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT grp,num,MAX(samecount) same FROM
    -> (
    ->     SELECT
    ->         (@group := @group + IF(@curnum=num,0,1)) grp,
    ->         (@same  := IF(@curnum=num,@same+1,1)) samecount,
    ->         (@curnum:=num),num
    ->     FROM logs ORDER BY id
    -> ) AA GROUP BY grp,num HAVING same >= 3;
+------+-----+------+
| grp  | num | same |
+------+-----+------+
|    1 |   1 |    3 |
+------+-----+------+
1 row in set (0.00 sec)

mysql>

After the aggregation, it is possible that a number can be repeated multiple times later on.

Adding the DISTINCT layer on just NUM, you get the end result I said before.

QUERY AGAINST MORE DATA

Let's add more data

INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
INSERT INTO logs (num) VALUES (7),(7),(7),(8),(8),(9),(5);
INSERT INTO logs (num) VALUES (5),(6),(11),(11),(11),(2),(2);
INSERT INTO logs (num) VALUES (4),(4),(4),(4),(4),(3),(3);
INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);

MY PROPOSED QUERY EXECUTED WITH MORE DATA

mysql> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT num FROM
    -> (
    ->     SELECT grp,num,MAX(samecount) same FROM
    ->     (
    ->         SELECT
    ->             (@group := @group + IF(@curnum=num,0,1)) grp,
    ->             (@same  := IF(@curnum=num,@same+1,1)) samecount,
    ->             (@curnum:=num),num
    ->         FROM logs ORDER BY id
    ->     ) AA GROUP BY grp,num HAVING same >= 3
    -> ) A;
+-----+
| num |
+-----+
|   1 |
|   7 |
|  11 |
|   4 |
+-----+
4 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Add ENum Attribute to existing table using Alter

I think you want this:

ALTER TABLE TRUCK
    ADD (ttype VARCHAR2(6)
    CONSTRAINT con_type
    CHECK (ttype in('SMALL','MEDIUM','LARGE'))
    );


Import local data to remote server for SQL Server

Use BCP, or the SQL Server Import and Export Wizard.  Both will read from local files.


How to configure encrypted SQL Server connections to prevent MITM attacks?

When I saw this question, I opened SSMS with the intent to read the help documentation on the "Trust server certificate" option only to discover that this is an undocumented feature with no description of what it does on MSDN. My guess is that it can be used to trust expired certificates or those which can't be validated by walking the chain of trust. Since there is no chain of trust for a self signed certificate, SSMS trusts it when it verifies the signature using the public key.

I think your suggestion would make an excellent server configuration option for high security implementations of SQL Server. It seems like you want a configuration option to reject all self-signed certificates, since avoiding those is the only real way to mitigate a man in the middle attack. It is exactly the concern that you raise that causes me to recommend configuring the Certificate tab with a domain or public CA certificate.


Delete or hide the row in set text in mysql result

Answered in a comment by danblack:
mysql -B --skip-column-names -e "SHOW VARIABLES LIKE '%verbosity%'"


Taking backup of all the databases in SQL Server

Note: First create a folder on D: drive. (e.g. D:\User_DataBackup\)

Step 1: Create a procedure that are given below.

Create PROCEDURE [dbo].[UserDataBaseBackUp]
AS

BEGIN
SET NOCOUNT ON;

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'D:\User_DataBackup\' -- as same as your created folder'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
DECLARE db_cursor CURSOR FOR
  SELECT name
  FROM MASTER.dbo.sysdatabases
  WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
  BACKUP DATABASE @name TO DISK = @fileName
  FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END

Step 2: Execute the above procedure.

 EXEC [UserDataBaseBackUp]

You can also schedule this procedure. This procedure is tested hopefully it will help.


Drop and Recreate All Objects in Database Project

This is accomplished through the CreateNewDatabase command line publish parameter:

/p CreateNewDatabase=True

  Specifies whether the target database should be updated or whether it should be dropped and re-created when you publish to a database.

You can also set this in the publish xml file:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    ... other properties ...
    <CreateNewDatabase>True</CreateNewDatabase>

Or you can use the publish UI (which sets the above mentioned XML property):

I consider the documentation on this to be a little hard to find, so here's a link to it: Microsoft Docs - SqlPackage.exe


What is purpose of `ALLOW_CONNECTIONS` being set so no one can connect to the new Postgres database?

It gives you a chance to revoke connect from unwanted users before doing alter database ... allow connections

Also in all cases users with superuser permission are allowed to connect, so you can create an unconnectable database, then as superuser populate it and create constraints ans indices before making it publicly available.


Where is stored the messages results from T-SQL?

The message you're seeing is not generated by SQL Server itself; it just returns "12".

SQL Server Management Studio generates the complete message; it's a .NET application so it's probably coming from a .resx file which gets compiled into the application package. With a decompiler or DLL analyzer you might be able to find it among the other messages.


How to get pg_locks related to a specific database in Postgres?

As documented in the manual pg_locks contains a column database which you can join to the pg_database view:
select l.*
from pg_locks l
  join pg_database d on d.oid = l.database
where d.datname = current_database();


Cannot connect remotely to sql server 2012 instance using SSMS

You should not need to specify the port for a named instance (that's kind of the point of the name - and since it's dynamic you won't want to hard-code a port into your connection strings etc. unless you want to change your config files every time the port changes). Try connecting to:

123.123.123.123\SQLEXPRESS

Also make sure that your server is exposed to the Internet on that port (firewalls may be preventing this) and that the SQL Browser service is running.

PS Ping means nothing. You need to try to telnet to the port that SQL Server is exposed on.


MySQL: Importing databases from older version

Writing out @Akina's suggestion:

Edit your MariaDB server options file, which I think is it at /etc/mysql/my.cnf on Debian. In the [mysqld] section, add this line: secure_auth=OFF
Restart MariaDB server: systemctl restart mariadb (assuming you have systemd)
You should now be able to log in using the mysql client and run something like: SET PASSWORD = password('your_new_password'); Alternatively, run mysql_secure_installation from the command-line.
Edit the MariaDB server options file again and remove the line you inserted before, then restart the MariaDB server, and everything should be working, hopefully.


There's a long delay in this query that I need to resolve

Testing the Impact that OPTION(OPTIMIZE FOR @PORTALID = 0) could have

Create the tables

CREATE TABLE dbo.[Roles]
(
RoleID INT PRIMARY KEY,
PortalId INT,
RoleName
varchar(255)
)

CREATE TABLE DBO.[UserRoles]
(
UserRoleID INT IDENTITY(1,1),
RoleID INT FOREIGN KEY(RoleID) REFERENCES dbo.roles,
Status int default (1),
EffectiveDate datetime2,
ExpiryDate datetime2
)

Insert some data into roles

INSERT INTO DBO.Roles(RoleID,PortalId,RoleName)
VALUES(1,0,'Role1') -- 1 value with portal id = 0

DECLARE @ID INT = 2
WHILE @ID < 1000
BEGIN
INSERT INTO DBO.Roles(RoleID,PortalId,RoleName)
VALUES(@ID,1,'Role2') -- 998 rows with @PortalID = 1

SET @ID += 1
END

Insert some data into UserRoles

DECLARE @ID INT = 1
WHILE @ID < 1000
BEGIN

INSERT INTO DBO.[UserRoles](RoleID,EffectiveDate,ExpiryDate)
VALUES(@ID,getdate(),NULL) -- one user role for each role id.

SET @ID += 1
END

Testing the query with @PortalID = 0 and @PortalID = 1

@PortalID = 0

SET STATISTICS IO, TIME ON;
declare @PortalID int = 0
SELECT R.*,
    (SELECT COUNT(*) FROM dbo.[UserRoles] U
     WHERE U.RoleID = R.RoleID
     AND U.Status = 1
     AND (U.EffectiveDate < GETDATE() OR U.EffectiveDate IS NULL)
     AND (U.ExpiryDate > GETDATE() OR U.ExpiryDate IS NULL)) AS UserCount
 FROM dbo.[Roles] R
 WHERE (R.PortalId = @PortalID OR R.PortalId is null)
AND (R.RoleId >= 0)
ORDER BY R.RoleName
OPTION (OPTIMIZE FOR (@PortalId = 0))

The ususal scan's since we don't have indexes

The expectations match 100%

@PortalID = 1

declare @PortalID int = 1
SELECT R.*,
    (SELECT COUNT(*) FROM dbo.[UserRoles] U
     WHERE U.RoleID = R.RoleID
     AND U.Status = 1
     AND (U.EffectiveDate < GETDATE() OR U.EffectiveDate IS NULL)
     AND (U.ExpiryDate > GETDATE() OR U.ExpiryDate IS NULL)) AS UserCount
 FROM dbo.[Roles] R
 WHERE (R.PortalId = @PortalID OR R.PortalId is null)
AND (R.RoleId >= 0)
ORDER BY R.RoleName
OPTION (OPTIMIZE FOR (@PortalId = 0))

That's gonna hurt on bigger tables

Low expectations

What if we run it without the optimize for? Spoiler: (This creates a new execution plan)

declare @PortalID int = 1
SELECT R.*,
    (SELECT COUNT(*) FROM dbo.[UserRoles] U
     WHERE U.RoleID = R.RoleID
     AND U.Status = 1
     AND (U.EffectiveDate < GETDATE() OR U.EffectiveDate IS NULL)
     AND (U.ExpiryDate > GETDATE() OR U.ExpiryDate IS NULL)) AS UserCount
 FROM dbo.[Roles] R
 WHERE (R.PortalId = @PortalID OR R.PortalId is null)
AND (R.RoleId >= 0)
ORDER BY R.RoleName

That's better

TL;DR

As is usual, YMMV.

What will happen when implementing something like this depends on the indexes, the data, and all the other things SQL Server takes into account when creating an execution plan. How much the query is executed with certain parameters, ....

This is just to show what might happen if you implement OPTION (OPTIMIZE FOR (@PortalId = 0)) on a production system.
In the end it is up to you to test the possible outcomes, and what is best for your system.
You could opt to use the query as it is and deal with parameter sniffing, to use optimize for unkown so the SQL Server engine will use stats and other heuristics to optimize its execution plan, to use OPTION(RECOMPILE) as to not cache the plan but create it on each execution, to use OPTION( OPTIMIZE FOR @PortalId = 0), but deal with queries that using a different value, as they might go slower, ... .


You will have both employee table and company table to store employee and company info. But you need another table for the relation since it is a many-to-many relationship.

Also here, the work hours info is a relation attribute. It does not exist until an employee starts to work for a company.

The ER diagram will simply be as the following:

When you map this relation, you will have a table company_employee(employee_id, company_id, work_hours)

Your SQL code for the tables:

CREATE TABLE employee (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL
);

CREATE TABLE company (
    company_id INTEGER PRIMARY KEY,
    company_name VARCHAR(300) NOT NULL
);

CREATE TABLE company_employee (
    employee_id INTEGER NOT NULL,
    company_id INTEGER NOT NULL,
    work_hour_start TIME NOT NULL,
    work_hour_end TIME NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employee (employee_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (company_id) REFERENCES company (company_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (employee_id, company_id, work_hour_start, work_hour_end)
);

In the company_employee table, you can store work hours in a single column too, depending on your needs.

To view the columns,

SELECT e.employee_name, c.company_name, ec.work_hour_start, ec.work_hour_end
FROM employee e
INNER JOIN company_employee ec
ON e.employee_id = ec.employee_id
INNER JOIN company c
ON c.company_id = ec.company_id;

Lastly, this will show who worked how many hours for what company:

SELECT c.company_name, e.employee_name, MAX(ec.work_hour_end - ec.work_hour_start) AS max_hours
FROM employee e
INNER JOIN company_employee ec
ON e.employee_id = ec.employee_id
INNER JOIN company c
ON c.company_id = ec.company_id
GROUP BY c.company_name, e.employee_name
ORDER BY c.company_name;


Ola Hallengren's IndexOptimize for Updating Statistics exclude schema

Ola covers this scenario in the documentation.
For the @Indexes parameter:

The ALL_INDEXES keyword is supported. The hyphen character (-) is used to exclude indexes, and the percent character (%) is used for wildcard selection. All these operations can be combined by using the comma (,).

If you want to do "everything on the server, except one schema in one database" you'd supply parameters like @Databases='ALL_DATABASES', @Indexes= 'ALL_INDEXES, -Db1.Schema1.%'.
If you want to do "everything on the server, except never the staging schema in any database" you'd supply parameters like @Databases='ALL_DATABASES', @Indexes= 'ALL_INDEXES, -%.staging.%'.


I've worked with both Postgres and SQL Server. I found Postgres to be superior in GIS functionality. And while I'm going to briefly detail my findings below, I'd suggest this: Give yourself a brief but reasonable time period to review the unfamiliar solution over the one you know, with specific goals in mind. For example, maybe a 2 week time period to install and learn some specific functionality that is currently in use. If you find that you are stuck or lack functionality within that time period, then you know it's not for you. It's a investment in research that broadens your view and helps you realize you may have been missing something that you were unaware of before, or simply confirm your current course is the right now.

As far as the database goes, I found Postgres to have a shorter, and more shallow, learning curve. The documentation is just incredible. SQL Server does have quite a bit of documentation, but I find a lot of it hard to read, with not enough examples and tutorials.

PostGIS vs SQL Server Spatial is similar to the above regarding documentation, but PostGIS beats the pants off SQL Server Spatial in functionality. For example, Google Maps, and to a lesser degree Bing Maps, has recently added full geoJSON support to their maps API. Well, PostGIS can easily return a geoJSON result directly from a database query using ST_AsGeoJSON(). This geoJSON result can then be passed directly to whatever can understand geoJSON. SQL Server requires you to use additional library and processing, or use ogr2ogr. In addition PostGIS has over 300 functions available for data conversion into and out of the database, compared to SQL Server which has around 70-100.


Reporting Services - Host header and Windows Authentication

Solved issue by adding new host header address to local intranet site in the browser.


Can PostgreSQL databases be attached/detached on the fly?

No, short answer is no. This has been asked at the PostgreSQL development mailing list at 2011: Detach/attach database
 and the many replies in summary said "yes, this is feasible to implement but quite complicated to do so.":

  You would have to do quite some surgery because of oids from shared tables. I don't think thats easily doable.

  Andres Freund

  The main reason this doesn't work is XID management.
  It's barely possible you could make it work if you first locked all
  other sessions out of the DB and then froze every XID in the database,
  but that's a sufficiently heavyweight operation to make it of dubious
  value.
  ...

  Tom Lane

  I have to admit I've thought about this from time to time, and it
  would be pretty cool.  I was initially thinking that it wouldn't be
  that difficult to do this on a per-database level, because if you
  slurp up a whole database then by definition you're also including the
  system catalogs, which means that you have the pg_class, pg_attribute,
  and pg_type entries that are necessary to interpret the table
  contents.  If you do anything more fine-grained (per-tablespace,
  per-table, or whatever) then things get much more complex, but at the
  database level you only need to worry about interactions with other
  globals: tablespace and role definitions.  And we could probably write
  code to grovel through the system catalogs for a newly "mounted"
  database and do search and replace on the appropriate columns, to map
  from the old OIDs to the new ones.  It wouldn't be simple, but I think
  it could be done.

  But Tom's point about XIDs and LSNs seems like it kind of puts a
  bullet through the heart of the whole idea.  Now, before you can move
  the database (or table, or whatever) between clusters, you've got to
  rewrite all the data files to freeze XIDs and, I don't know, zero out
  LSNs, or something.  And if you're going to rewrite all the data, then
  you've pretty much lost all the benefit of doing this in the first
  place. In fact, it might end up being slower than a dump and
  restore; even an uncompressed dump will be smaller than the on-disk
  footprint of the original database, and many dumps compress quite
  well.

  Robert Haas

  What about having database-level XIDs rather than cluster-level?  Is that
  remotely feasible?

  Thom Brown

  Maybe.  You'd need a set separate set for shared catalogs, too.  It
  seems like a heck of a lot of work, though, especially since (IME,
  anyway) most people only really one run one database per cluster.

  Robert Haas

Leaving aside all the internal implementation details / problems that would make this possible, the last comment has something that may be helpful to you:

Run a different cluster per database. You would probably need to implement
something of an infrastructure so when a user wants to "attach" a database, it would start up the related cluster and stop it when the user wants to "detach" it.


Oracle won't do this natively. You'll have to use an external utility to do this.

On Unix systems this is usually achieved with logrotate.


I have MSSQL 2019 installation at my PC and could figure out the path. You can find this tool in the below directory:
C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn

You may also download it fresh from below links:
https://go.microsoft.com/fwlink/?linkid=2142258
https://go.microsoft.com/fwlink/?linkid=2142257
Same is documented here on MS site.
Hope this helps.


How to check and change function volatility in Postgres?

I checked the documentation again, and the answer to my second question is, yes you can alter it using the alter function query.

alter function my_function() stable;

Also, I see that checking for function volatility has been asked and answered before.

https://stackoverflow.com/questions/54452415/how-to-check-whether-a-function-is-immutable


Modeling a three-way association with optional relation

Using your Model #1, I believe the database itself can be made to enforce the stated constraints (i.e., a category and a product may only be related when they are both related to the same party) using the following constraints.

The primary key for category_party consists of category_id and party_id. This creates an optional many-to-many relationship between categories and parties. This implies the following.

A category is classified by zero-one-to-many parties.
A party classifies zero-one-to-many categories.

The primary key for product_party consists of product_id and party_id. This creates an optional many-to-many relationship between categories and parties. This implies the following.

A product is classified by zero-one-to-many parties. This will be overridden below to meet your stated requirements.
A party is classified by zero-one-to-many products.

There is unique constraint on product_id in the product_party. This makes the relationship between party and product a one-to-many instead of many-to-many. This implies the following.

A product is classified by one-to-one party, overriding the previously-stated cardinality of this relationship.

The primary key of product_category_assignment consists of category_id, product_id and party_id.
The foreign key from product_category_assignment to category_party consists of category_id and party_id
The foreign key from product_category_assignment to product_party consists of product_id and party_id
The constraint that a category and a product can only be related when they are related to a common party is enforced because product_category_assignment only contains one party_id which is used in both foreign key relationships.

I believe that the only requirement not covered here is that a product is required to have a related party (i.e., party is not optional).

Using Model #2 would require much more code (i.e., an ugly stored procedure?) to enforce the constraint because the structure of the database will allow the following.

The category can be have one party_id (e.g., 1).
The product can have a different party_id (e.g., 2).
The product_category can have yet a third party_id (e.g., 3).

I do want to point out that there appear to be some unstated requirements here that could have some minor bearing on my answer above. For example...

What is the correct cardinality of the relationship between category and party? The two models you presented are different in this respect.

Model #1 allows a category to be related to zero, one or many parties.
Model #2 only allows for zero or one party for each category.

Is a category required to have (at least) one party or is that relationship optional? This is not clear from your description or either of your models.

I have always said that there are three things that are important in a data model: details, DeTaiLs and DETAILS. Sometimes nailing down the details of every relationship around the complexity can help you discover the best way to design the data model. Sometimes, complex relationships like this may point to missing entities (tables). Sometimes, complex requirements defy enforcement using only database structural constraints and require a bit of code (e.g., a stored procedure?) to finish the job.

Hope that helps.


Your syntax for ALTER INDEX...REBUILD WITH (DATA_COMPRESSION...) is correct.  The problem is actually revealed in the error message:

  Invalid usage of the option data_compression in the ALTER INDEX
  REBUILD statement.

For a spatial index in SQL Server 2008, using the data_compression option is not supported. According to Microsoft Documentation, support for data compression was added for spatial indexes in SQL Server 2012:

  DATA_COMPRESSION = {NONE | ROW | PAGE}

  Applies to: SQL Server 2012 through SQL Server 2017, SQL Database.

If/when you upgrade to a newer version of SQL Server, your original ALTER INDEX statement would work to rebuild the index using PAGE compression. Alternatively, as @AaronBertrand points out in a comment, you can also use CREATE...WITH (DROP_EXISTING=ON, DATA_COMPRESSION=PAGE... to change the compression settings, however this requires re-supplying the entire index definition, where the ALTER requires you only specify the options you are altering.


Alternative to Temporary Table with Create View - MySQL

View is just an alias to the query that allow you to use its result in other queries in terms of tables not subqueries. But each time you refer to the view, the query from its definition is invoked. No temporary tables created when view is defined.

Therefore views do not improve the overall performance at all and are intended to make DB structure more clear and logical.

Prior to the MySQL 5.7 query results can be cached that really speed up some queries. But as far as caching conflicts with InnoDB internals, it is disabled now and can cause significant slowdown and overall performance losses when enabled. So views should be considered as threat to the performance.

On the other hand, temporary tables, especially those with ENGINE=MEMORY,  can significantly improve the performance. But you have to ensure that temptable is updated each time the referred tables are updated. Triggers are the good tool to keep reference consistency of the derived tables. Sure even memory-based temptables should be properly indexed for maximum performance.


Extend a single week's win/loss ratio calculation to a whole season

Assuming a standard many-to-many implementation ...

SELECT week_id
     , count(*) FILTER (WHERE week_points > team_points) AS losses
     , count(*) FILTER (WHERE week_points < team_points) AS wins
     , count(*) FILTER (WHERE week_points = team_points) AS draws
--     , count(*) AS total -- redundant check
FROM (
   SELECT week_id, team_id, sum(points) AS week_points
        , first_value(sum(points)) OVER (PARTITION BY week_id
                                         ORDER BY team_id <> 7) AS team_points
   FROM  (SELECT id AS week_id FROM weeks WHERE year = ?) w  -- your year
   CROSS  JOIN unnest(?::int[]) t(team_id)  -- your teams, see below!
   JOIN   player_scores p USING (week_id, team_id)
   WHERE  EXISTS (  -- only weeks where the team actually played
      SELECT 1 FROM player_scores
      WHERE week_id = p.week_id
      AND   team_id = ?  -- your team_id
      )
   GROUP  BY 1, 2
   ) sub
WHERE  team_id <> ?  -- your team_id again
GROUP  BY 1;

Returns something like:

week_id | losses | wins | draws
--------+--------+------+------
1       | 3      | 0    | 0
2       | 5      | 7    | 0
3       | 2      | 2    | 1

The key feature is the window function first_value() to get the weekly score for the team in the same query. Aggregate functions can be nested in window functions. Consider the sequence of events:

Best way to get result count before LIMIT was applied in PHP/PostgreSQL

The aggregate FILTER clause requires Postgres 9.4+. There are other ways. Details in this closely related answer on SO:

How can I simplify this game statistics query?

Assuming you provide your list of teams as array literal in the form '{1,2,3}'. There are other ways ...


If as You explain in parallel question - Combine two Data from 2 Tables using SQL

Your current database MS SQL Server, You can start from:

SELECT
    tbl_ADMasterfile.QuartNo,
    tbl_ADMasterfile.SORG,
    tbl_ADMasterfile.Ace_Code,
    tbl_ADMasterfile.Outlet_Code,
    tbl_ADMasterfile.Outlet_Name,
    tbl_ADMasterfile.RSM,
    tbl_ADMasterfile.KAS,
    tbl_ADMasterfile.Gondola_Type,
    tbl_ADMasterfile.No_Of_Unit,
    tbl_ADMasterfile.Rent_Unit,
    tbl_ADMasterfile.Monthly_Rental,
    tbl_GMFormat.Count_Date,
    tbl_GMFormat.Gondola_Type
    tbl_GMFormat.no_of_units
FROM
    tbl_ADMasterfile
FULL JOIN tbl_GMFormat
     ON tbl_ADMasterfile.Outlet_Name = tbl_GMFormat.Outlet_Name
     AND tbl_ADMasterfile.Gondola_Type = tbl_GMFormat.Gondola_Type

then You can add GROUP BY conditions (with aggregate functions) if necessary, DISTINCT You do not need (at first look)

little more information about JOINs - http://www.w3schools.com/Sql/sql_join_full.asp


Export, based on query, into INSERT text file

Have you looked into using the UTL_FILE package ?
Using that, you can write to ( and read from ) files really easily and directly from PL/SQL, with great robustness.
You need to configure a Directory object in your database, and then you can write to it with ease.

Here's some material on the subject : http://psoug.org/reference/utl_file.html , http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm


Yes you can do that with Failover Clustering. But you will need to license this second instance.

If one node fails and can't be brought up quickly, you need to consider whether or not the hardware can support both instances for a period of time. In a 2-instance scenario, it is generally advised to have 3 nodes: active/active/passive.


Have you start the service broker on MSDB ? You need the service broker on to be able to process mail queue.

Read this post on Stackoverflow.
An other link that may help you is how to troubleshoot a queue in sql.
You can also use this link to get your service broker up and running on MSDB.

Hope that those links may help you.


Can two servers access the same database files?

It is possible for two separate servers to simultaneously access a shared read-only database. The feature is known as Scalable Shared Databases.

From your description, it sounds more like you need a failover cluster, not a shared read-only copy.


How to resolve the master server shut down/unavailability in mysql with master - slave replication

Before you perform any mysqldump to fully restore a Slave, you should consult the output of SHOW SLAVE STATUS\G. Let's start with a sample SHOW SLAVE STATUS\G:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Please notice that there are two sets of replication coordinates from the Master

(Master_Log_File,Read_Master_Log_Pos)
(Relay_Master_Log_File,Exec_Master_Log_Pos)

There is a major difference between them

(Master_Log_File,Read_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs.
(Relay_Master_Log_File,Exec_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs THAT IS NEXT TO BE EXECUTED ON THE SLAVE.

The timestamps from these two coordinates helps you figure out Seconds_Behind_Master.

Knowing these things, here is what you can do:

Step 01) Run SHOW SLAVE STATUS\G
Step 02) Get Relay_Master_Log_File,Exec_Master_Log_Pos from SHOW SLAVE STATUS\G (In the sample, that would be (mysql-bin.000254,858190247)
Step 03) STOP SLAVE;
Step 04) CHANGE MASTER TO master_log_file='mysql-bin.000254',master_log_pos=858190247;
Step 05) START SLAVE;
Step 06) Wait 10 seconds
Step 07) Run SHOW SLAVE STATUS\G and check Seconds_Behind_Master

If the Seconds_Behind_Master is a number and eventaully drops to zero, replication is fully reesatablished.

After doing all this, if replication breaks because of a corrupt binary log from the master, then you do the last resort:

Steo 01) On the Master, RESET MASTER; to erase all binary logs and start with a new one
Step 02) On the Master, run this

This create proper dump for the slave

echo "STOP SLAVE;" > /root/MySQLData.sql
mysqldump --all-databases --routines --triggers --flush-privileges --master-data=1 >> /root/MySQLData.sql
echo "START SLAVE;" >> /root/MySQLData.sql

Step 03) scp /root/MySQLData.sql over to the Slave and Load it in MySQL on the Slave

Give it a Try !!!


pgAdmin 4 including  in all copied strings

It turns out this is a known bug:

https://redmine.postgresql.org/issues/2980

It has been fixed in source already. Expect the fix in pgAdmin 4 v3. Khushboo said in the pgadmin-support list that it will be out soon.


There are two problems

PROBLEM #1

The visit_time needs to be prefixed with NEW.

PROBLEM #2

It needs to be a BEFORE UPDATE, not AFTER UPDATE because you cannot change values in an AFTER trigger.

Your code should read

DELIMITER //
CREATE TRIGGER myTrigger BEFORE UPDATE ON cityies
FOR EACH ROW
BEGIN
    IF NEW.visitors <> OLD.visitors THEN
        SET NEW.visit_time = now();
    END IF;
END
//
DELIMITER ;


What should I do with this large database filling up the disk?

You have a few options. However, if your database is this size, normally it would only grow.

First option, make sure you need all the data. Ask to the business if it is necessary to keep all data, if not you could write some kind of archiving process to clean up older data, making place for new data to come in.

If your SQL Server version is enterprise you could benefit greatly by implementing row compression or even page compression, this will definitely compress your tables. However before you implement this always test, because this can have a negative effect on your performance. Especially when using Page compression. If your bottleneck is CPU I would not implement it. Here is a blog on what benefits you can get.
https://www.mssqltips.com/sqlservertip/3187/demonstrating-the-effects-of-using-data-compression-in-sql-server/

Another thing you can do is add another disk to your database and add an additional filegroup & files on that disk. Then rebuild your indexes to that filegroup, in that way you open up space inside your current files, and you can use 2 disks instead of 1 for your data storage. This will also in most cases improve your IO operations. https://www.mssqltips.com/sqlservertip/2601/using-multiple-filegroups-for-a-database-and-changing-the-default-filegroup/

If you have partitioning in place, you could store parts of tables on other disks as well. This will also make the maintenance of your indexes easier since you can rebuild a partition instead of a whole index.

Also if this is a datawarehouse database, I've seen epic datacompression using Clustered Columnstore Index on your Fact Tables.

That are the thing I would look into.


Consistent drop in performance on SQL Server 2016 SP2

Solved: Not sure it will help anyone else, but just in case it will, what I found was the serial nature of the process (one command per connection) was causing the process to send 143,000+ sp_setapprole calls during the process. This was bloating the TokenAndPermUserStore cache by 1GB each time it ran. This is why a restart of the service saw it run quickly and subsequent runs got slower as it took longer to search through the security cache. Long term fix is, of course, to have the process rewritten correctly with aggregate inserts and updates, but as a temporary solution, I have a job monitoring the size of the cache and flushing it with DBCC FREESYSTEMCACHE ('TokenAndPermUserStore'). This will add some IO to other calls, but sometimes we're forced to do what we can with the code we're given.


Access SQL Server from a different subnet

If this is installed on a windows 2008 server then you will need to check that the firewall is either disabled (not recommened) or that there is a firewall rule for the SQL port (1433 for default instance, 1434 and whatever is configured for a named instance).


Does an index continue to be used after it is dropped (Database Polling investigation)

As Peter mentioned, the query may still be using a far better execution plan - and since you know how to check that, I'd suggest doing so - but it is not using the index.

When you drop an index, it is gone. There isn't any way it can linger around once your DROP command returns successfully.

I feel like I should write more, but its really just that simple. Check your execution plan and you should find your answer pretty easily.


Select previous value in row without using a subquery

As Akina has pointed out in comments LAG() function requires an ORDER BY clause to show the properly values.

Given the next example:

CREATE TABLE t (id int, bar int);

INSERT INTO t VALUES
(1, 7),
(2, 8),
(3, 30),
(4, 25),
(5, 24),
(6, 24),
(7, 35),
(8, 40);

You can get the previous bar value ordering by id, (if you don't set an order, what is the previous row?)

SELECT
    id,
    bar,
    LAG(bar) OVER (ORDER BY id)
FROM
    t;

id | bar |  lag
-: | --: | ---:
 1 |   7 | null  --<< Note the NULL value
 2 |   8 |    7
 3 |  30 |    8
 4 |  25 |   30
 5 |  24 |   25
 6 |  24 |   24
 7 |  35 |   24
 8 |  40 |   35

Then simply use a conditional statement IF or CASE

SELECT
    id,
    bar,
    CASE
        WHEN LAG(bar) OVER (ORDER BY id) < bar THEN 'Lower'
        WHEN LAG(bar) OVER (ORDER BY id) > bar THEN 'Higher'
        WHEN LAG(bar) OVER (ORDER BY id) = bar THEN 'Equal'
        ELSE 'Nothing to compare'
    END As Previous
FROM
    t;

id | bar | previous
-: | --: | :-----------------
 1 |   7 | Nothing to compare
 2 |   8 | Lower
 3 |  30 | Lower
 4 |  25 | Higher
 5 |  24 | Higher
 6 |  24 | Equal
 7 |  35 | Lower
 8 |  40 | Lower

db<>fiddle here


Removing duplicates from a query with conditions in Access 2007

If all the "duplicate" rows differ only at the InterestRate and you never have more than one row with rate over 0, and (one more assumption) there is no negative interest rate, then you can simply:

SELECT
    TransactionID, MAX(InterestRate) AS InterestRate,
    col1, col2, ..., colN               -- all the other columns

FROM
    tableX AS t
GROUP BY
    TransactionID,
    col1, col2, ..., colN ;             -- all the other columns


I friend told me the solution:

update Z_VERTRAGSDATEN_HIST HIST
  set
    ( HIST.VERHI_BISDAT
    , HIST.VERHI_ENDE
    , HIST.VERHI_ZEITSTEMPEL
    )  =
    ( select
      UPDA.VERHI_BISDAT
      , UPDA.VERHI_ENDE
      , sysdate
     from
       Z_VERTRAGSDATEN_UPDATE UPDA
     where
       HIST.VERHI_VER_ID = UPDA.VERHI_VER_ID
     )
where
  HIST.VERHI_VER_ID in
  ( select
      verhi_ver_id
    from
      Z_VERTRAGSDATEN_UPDATE
  )
;
commit;


IBM DB2 9.7, possible to temporarily disable transaction logs?

Dropping tables will engage the transaction logs. Actually dropping pretty much everything (except a tablespace or a bufferpool) engages the transaction logs.

Couple things that may help you

Using truncate on each table will drop the data from the table without logging. This only works for tables. Dropping tables and other objects will still log, but truncate would help you cut down on logging.
Dropping a tablespace (pending you are using your own and not the default IBM provided ones) will automatically drop ANY and ALL objects associated with that tablespace (usually tables), but drops the tables and the data in a way that is not logged. (There is a way to mark a tablespace for recoverable tables, but for the most part you get a clean database table-wise). NOTE: you still have to drop other objects, which will log some.
Dropping the whole database. This will drop ALL objects immediately without logging.

I guess depending on what you are doing, the second or third points will get you there the quickest. Just remember that anything dropped that does not engage the transaction logs is not recoverable from, without restoring from a backup.

I'd recommend taking a backup before you try any of these as a best practice. You could also use db2look to get the schema changes and db2move to get the schema with data (although I don' think you get any security with either of those). Again, I'd stick to a backup before you try dropping anything.

EDIT: You can indeed get some security with db2look. These are usually the -x and -xdep parameters.


Where are you seeing the message?  With the job gone, I wouldn't expect to see that message in an active job.  Do you have *JOBCTL authority?  It may be that the job isn't gone, you just don't have authority to see it.


How can I find a table in the whole instance?

To do it through code...

1) If you can assume that this table is in the dbo schema.

SELECT name
FROM   sys.databases
WHERE  CASE
         WHEN state_desc = 'ONLINE'
              THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[costomersHR]', 'U')
       END IS NOT NULL

2) Otherwise you could loop through all databases (and query sys.tables in each) with something like this code for a more reliable and flexible sp_MSforeachdb


Should I create a column for specific behavior?

If I'm understanding your question correctly, what you might consider doing is having 3 tables. Table1 would be your "Line" table. You'd have a table, maybe called "Errors," containing the possible types of errors that could exist. Then you'd have a table joining them together, maybe called "LineErrors," which would reference the line, and the type of error it had.

In this case, you could also include a column in your Line table which held a bit value indicating if that line had at least 1 error, which you could determine by looking that line number's primary key up in the LineError table.

Hopefully this makes sense, and that I'm understanding your problem sufficiently.


Move data from a table to another without losing data

Consider creating a new table with the partitioning strategy you want, and add a view atop both tables that does a union all. Have people use the view, and write instead-of triggers against the underlying tables & views.

Inserts should be sent to the new table, updates should move data to the new table, and deletes should be applied to both tables.

Then do batch moves in the background, moving as many records at a time as you can over to the new table. You can still have concurrency issues while this is going on, and some terrible execution plans, but it lets you stay online while the moves are happening.

Ideally, you start the process on a Friday afternoon to minimize the effect on end users, and try to get it done before Monday morning. Once it's in place, you can change the view to point to just the new table, and the terrible execution plans go away. Ideally.

To avoid the triggers firing when the data is being migrated in batches, look at the number of rows in the deleted/inserted tables in the trigger, and skip the activities if they're near to the number of rows in your batch.

The more transparent you want this to be for your end users, the more work (and testing) it's going to take. This especially holds true if you're using partitioning: very often folks believe it's going to make all their queries faster, and yet some of them end up much slower. Try to test as much of your workload on a development server with the partitioned tables if you can.


Find the missing numbers in a given range

In postgresql:

CREATE TABLE ranges
(n1 int,
n2 int);

INSERT INTO ranges
VALUES (9,10),(11,19),(29,44),(45,51),(56,60);

SELECT n FROM ranges
RIGHT JOIN generate_series(15,60) n
ON (n1 <= n AND n <= n2) WHERE n1 IS NULL;


How can I enforce a one-to-zero-or-one relationship with multiple types?

You can add an attribute to MEMBER that defines the type. Example:

CREATE TABLE MEMBER
( MEMBER_ID INT NOT NULL PRIMARY KEY -- ID is to vague IMO
, MEMBER_TYPE CHAR(1) NOT NULL
, ...
, CONSTRAINT <name> CHECK (MEMBER_TYPE IN ('R','M','P'))
) ;

I've heard that some DBMS support CHECK CONSTRAINTS with SELECT and for such you can add a CHECK constraint in each sub table that verifies the type

For DBMS that does not support that you can add a unique constraint in MEMBER:

ALTER TABLE MEMBER ADD CONSTRAINT <name> UNIQUE (MEMBER_ID, MEMBER_TYPE);

and "inherit" the MEMBER_TYPE attribute to the sub tables:

ALTER TABLE REFEREE ADD COLUMN MEMBER_TYPE CHAR(1) DEFAULT 'R' NOT NULL;
ALTER TABLE REFEREE ADD CONSTRAINT <name> CHECK (MEMBER_TYPE = 'R');
ALTER TABLE REFEREE ADD CONSTRAINT <name>
     FOREIGN KEY (MEMBER_ID, MEMBER_TYPE)
     REFERENCES MEMBER (MEMBER_ID, MEMBER_TYPE);

Another idea is to add validation triggers to the sub tables that validate that the member is of correct type before inserting or updating.


OS error 3 means path not found.  You must specify a UNC network share such as \\computername\snapshot rather than a local path when using pull subscriptions and/or a remote Distributor.  This is covered in Secure the Snapshot Folder.  I have also covered this in a blog post found here or here.

Also, ensure your replication agent process account has read permissions on the snapshot share.  This can be done by right-clicking on the snapshot folder -> Properties, and adding the user on the Security tab with Read permissions.

If you have any questions please let me know.


For sysadmins, xp_cmdshell runs the command as the SQL database engine service account.

Make sure that account has been granted write permissions on the target location.


Identify rows that match on a key but differ in other columns

I think that this is what you're looking for...

WITH AllData AS (
  -- Get All Records that exist in both tables
  SELECT ID, Descr, Qty FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.ID)
  UNION ALL
  SELECT ID, Descr, Qty FROM B WHERE EXISTS (SELECT * FROM A WHERE B.ID = A.ID)
)
SELECT ID, Descr, Qty
FROM AllData
GROUP BY ID, Descr, Qty
HAVING COUNT(*) = 1
ORDER BY ID

It first selects all of the rows where the ID exists in both tables (since if an ID exists in only one table, it can't have different values in the other table.)  Then it groups by the columns that you want to compare.  If the count is > 1, then they matched.  If the count = 1 then there was a difference.  Here is the SQL Fiddle that I used to verify this.

Edit:  For large data sets, the performance will likely be poor.  But indexing your tables should help with that.


I would advice 1st one is better approach , which is

Table: `users`; contains all similar fields as well as a `user_type_id` column (a foreign key on `id` in `user_types`
Table: `user_types`; contains an `id` and a `type` (Student, Instructor, etc.)
Table: `students`; contains fields only related to students as well as a `user_id` column (a foreign key of `id` on `users`)
Table: `instructors`; contains fields only related to instructors as well as a `user_id` column (a foreign key of `id` on `users`)
etc. for all `user_types`

Reason:-

User related data like columns in students, instructors etc tables may increase in future so it will be easy to manage with 1st approach.

and choosing the other options to create a table with too many numbers of columns which would be not good in terms of future database management and with increasing number of columns in future it will more problematic.


SQL Server replication for off site copy

Based on what you have described Log shipping is going to be the way to go.  Log shipping is old school, tried and true.

Replication is pretty fragile, provides massive amounts of room for failure especially when it comes to adding new tables and getting those tables into the replication topology.

As for 2012 upgrade costs they may not be as bad as you think. If you have software assurance and an enterprise agreement that'll save you a bunch of money on licensing.


User options is a combination of bit values, for example:

16    ANSI_PADDING
32    ANSI_NULLS
64    ARITHABORT

To turn on only ARITABORT the value should be 64, and for example for ANSI_NULLS and ARITHABORT it should be 96.

Full list of values available here


Can there be situations where using (noexpand) on an indexed view is undesirable?

Firstly, NOEXPAND is an enterprise edition feature of SQL Server, that is why you have to specify it manually if you are not on this edition.

Based on the optimizer's prediction there may be a situation, where NOEXPAND would be harmful. But it's pretty rare and most likely NOEXPAND isn't the issue in such cases. Most likely the issue is poorly chosen indexes. For instance, an index on a column with low cardinality.

Additionally, have a look at this noexpand hint for non-enterpise edition and performance  post as it answers the rest of your questions. And also provides you with a workaround.


Howto disable Postgres listening on TCP?

Maybe it is not the finest Solution / Answer to my question, but at least it will point anybody (facing the said challenge I had in my original question)

To disable listening on TCP/IP network I used this command line option when starting the server application:

postgres [other arguments] -c listen_addresses=''

Addition:
The remaining open udp 127.0.0.1:38860 connection is supposedly linked to the
purpose of the the statistics collector subprocess as suggested on postgresql.org


Can DBA revoke access privilege from the owner of a table?

A user with DBA privillege can do anything. He can access or grant privillege of any table or database objects.


Try the hint INLINE

with abc as
(
  select /*+ INLINE */ count(distinct id) as my_count, some_column
  from  large_view v where some_column in (...constant values here...)
  group by some_column
) select my_count from abc;


Yes, you can use an EXCLUDE constraint, which is a generalization of UNIQUE constraints:

ALTER TABLE prices
  ADD CONSTRAINT unique_price_per_product_quantity_daterange
    EXCLUDE  USING gist
    ( product_id WITH =,
      quantity WITH =,
      daterange(start_date, end_date, '[]') WITH &&   -- this is the crucial
    );

The constraint can be interpreted as saying:

  Don't allow two rows that have same product_id, same quantity and overlapping (&&) date ranges.

The '[]' is for the wanted all-inclusive date range (the default is [) for range types).

See the documentation on constraints on range types. You probably also need to add the extension by running (once, for each database where you want this installed):

CREATE EXTENSION btree_gist;


Searching Oracle tables for particular values

This script constructs one select statement per table for all CHAR and VARCHAR2 columns.
It will give error ORA-01489 if some of the tables have many columns with long names.
It uses LIKE and equality search, in hope of an index.
The result is the table name and exact row, where the first occurrence of string has happened. Run resulted statement to quickly get this row.
If you don't have access to DBA_ views, and want to search in schema you are connected to, then change DBA_ to USER_ and remove rows 11 and 12.
For large schemas (many gigabytes or terabytes) it is slow to do such search.
HR@mydb> declare
  2   l_owner varchar2(30):= 'HR';
  3   l_search_string varchar2(200):= 'King';
  4   l_sql varchar2(30000);
  5   l_rowid rowid;
  6  begin
  7    l_search_string := upper(l_search_string);
  8    for c in (select t.table_name, listagg ('upper('||column_name||')',',') within group ( order by column_id )col
  9                from DBA_tables t, DBA_tab_columns c
 10               where t.table_name = c.table_name
 11                 and t.owner = c.owner
 12                 and t.owner = L_OWNER
 13                 and c.data_type in ('VARCHAR2','CHAR')
 14               group by t.table_name
 15               order by t.table_name )
 16    loop
 17      begin
 18        dbms_application_info.set_action('table: '||c.table_name);
 19        l_sql := 'select rowid from '|| l_owner||'.' ||c.table_name||
 20          ' where (' || replace(c.col,',',' like ''%'||L_SEARCH_STRING||'%'' or ' )|| '='''||L_SEARCH_STRING||'''' ||
 21          ') and rownum = 1';
 22        --DBMS_OUTPUT.PUT_LINE ( 'l_sql = ' || l_sql );
 23        --
 24        execute immediate l_sql  into l_rowid;
 25        --
 26        DBMS_OUTPUT.PUT_LINE ( 'select * from  '|| l_owner||'.' || c.table_name||' where rowid = '''||l_rowid|| ''';'  );
 27      exception when no_data_found then null;
 28      end;
 29    end loop;
 30    dbms_application_info.set_action(null);
 31  end;
 32  /
select * from  HR.EMP where rowid = 'AAAW1OAAGAAACErAAI';
select * from  HR.EMPLOYEES where rowid = 'AAAVPuAAFAAAADPAAA';
select * from  HR.NEW_EMPS where rowid = 'AAAW1XAAGAAACE7AAK';
select * from  HR.T where rowid = 'AAAXE9AAHAAAIACAAT';
select * from  HR.TT where rowid = 'AAAWTDAAIAAAA2CAFk';

From another session, you can monitor on what table the select is working at that moment.
select sid, action, username,  program
  from v$session
 where action like 'table: %'
/


Optimize a query that is using sub-query in where clause

You can try the following:

WITH CTE_Trades
AS (
    SELECT  ct.rateString
    ,       ct.currency
    ,       RowNum  = ROW_NUMBER() OVER (PARTITION BY ct.currency ORDER BY ct.created_at DESC) -- try with ct.id too
    FROM    myTable.trades ct
    WHERE   ct.isExecuted = 1
    AND     ct.status = 'executed'
    AND     ct.transactionType = 'sell'
)

SELECT rateString, currency FROM CTE_Trades WHERE RowNum = 1


Is it possible to force the optimizer to eliminate irrelevant tables in this partitioned view?

Enable trace flag 4199.

I also had to issue:

UPDATE STATISTICS dbo.ObservationDates
WITH ROWCOUNT = 73049;

to get the plans shown below. Statistics for this table were missing from the upload. The 73,049 figure came from the Table Cardinality information in the Plan Explorer attachment. I used SQL Server 2014 SP1 CU4 (build 12.0.4436) with two logical processors, maximum memory set to 2048 MB, and no trace flags aside from 4199.

You should then get an execution plan that features dynamic partition elimination:

select
    od.[Year],
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where
    od.[Year] >= 2000 and od.[Year] < 2006
group by
    od.[Year]
option (querytraceon 4199);

Plan fragment:

This might look worse, but the Filters are all start-up filters. An example predicate is:

Per iteration of the loop, the start-up predicate is tested, and only if it returns true is the Clustered Index Seek below it executed. Hence, dynamic partition elimination.

This is perhaps not quite as efficient as static elimination, especially if the plan is parallel.

You may need to try hints like MAXDOP 1, FAST 1 or FORCESEEK on the view to get the same plan. Optimizer costing choices with partitioned views (like partitioned tables) can be tricky.

The point is you need a plan that features start-up filters to get dynamic partition elimination with partitioned views.

Queries with embedded USE PLAN hints: (via gist.github.com):

Serial plan
Parallel plan


You can always nest blocks in PL/SQL

DECLARE
  <<define your other local variables>>

  PROCEDURE drop_if_exists( p_table_name IN VARCHAR2 )
  AS
    l_num_tables pls_integer;
  BEGIN
    SELECT COUNT(*)
      INTO l_num_tables
      FROM user_tables
     WHERE table_name = p_table_name;

    IF( l_num_tables = 1 )
    THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name;
    END IF;
  END drop_if_exists;
BEGIN
  drop_if_exists( 'MY_TEST_TABLE' );
  drop_if_exists( 'MY_TEST_TABLE' );

  <<more code here>>
END;


data guard / network failure: deadline before global crash?

If the standby site becomes unreachable, the primary site will not delete archivelogs as they will be still needed by the standby site. If you try to backup and delete these archivelogs, you will receive an ORA-08137 error, and eventually your archive area will become full and your database will stall.

Setting log_archive_dest_state_n to DEFER lifts this "restriction". If the stanbdy database becomes unreachable, you can manually set log_archive_dest_state_n to DEFER, with that, you disable that destination, and the primary site will be able to delete the archivelogs.

An other way to work around this is to write your backup scripts in a way that is prepared to handle this situation. By adding the FORCE option to a DELETE operation, RMAN skips the above check and deletes the archivelogs specified regardless of the state of the standby database. For example adding this line to the end of your backup script:

delete force noprompt archivelog until time 'sysdate -1' backed up 1 times to 'sbt_tape';

The above command deletes all archivelogs older than 1 day and backed up at least once to your backup server.


The most likely reason is you have a firewall or gateway which is not very patient and is dropping the connection after 5 minutes.  Usually when this happens, it makes it look to the client like the server closed the connection, and to the server like the client closed the connection.  That seems to be compatible with the error message you see in python.  What does the server's log file blame the problem on?


Changing a column from NOT NULL to NULL - What's going on under the hood?

As alluded to by @Souplex in the comments one possible explanation might be if this column is the first NULL-able column in the non clustered index it participates in.

For the following setup

CREATE TABLE Foo
  (
     A UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
     B CHAR(1) NOT NULL DEFAULT 'B'
  )

CREATE NONCLUSTERED INDEX ix
  ON Foo(B);

INSERT INTO Foo
            (B)
SELECT TOP 100000 'B'
FROM   master..spt_values v1,
       master..spt_values v2

sys.dm_db_index_physical_stats shows the non clustered index ix has 248 leaf pages and a single root page.

A typical row in an index leaf page looks like

And in the root page

Then running...

CHECKPOINT;

GO

ALTER TABLE Foo ALTER COLUMN B  CHAR(1) NULL;

SELECT Operation,
       Context,
       ROUND(SUM([Log Record Length]) / 1024.0,1) AS [Log KB],
       COUNT(*) as [OperationCount]
FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName = 'dbo.Foo.ix'
GROUP BY Operation, Context

Returned

+-----------------+--------------------+-------------+----------------+
|    Operation    |      Context       |   Log KB    | OperationCount |
+-----------------+--------------------+-------------+----------------+
| LOP_SET_BITS    | LCX_GAM            | 4.200000    |             69 |
| LOP_FORMAT_PAGE | LCX_IAM            | 0.100000    |              1 |
| LOP_SET_BITS    | LCX_IAM            | 4.200000    |             69 |
| LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | 8.700000    |              3 |
| LOP_FORMAT_PAGE | LCX_INDEX_LEAF     | 2296.200000 |            285 |
| LOP_MODIFY_ROW  | LCX_PFS            | 16.300000   |            189 |
+-----------------+--------------------+-------------+----------------+

Checking the index leaf again the rows now look like

and the rows in the upper level pages as below.

Each row has been updated and now contains two bytes for the column count along with another byte for the NULL_BITMAP.

Due to the extra row width the non clustered index now has 285 leaf pages and now two intermediate level pages along with the root page.

The execution plan for the

 ALTER TABLE Foo ALTER COLUMN B  CHAR(1) NULL;

looks as follows

This creates a brand new copy of the index rather than updating the existing one and needing to split pages.


How to make tables unavailable for users during certain times in Oracle?

You can put the database in a Quiesced State (Oracle Database Administrator's Guide)

Restrict access. Only DBAs will be able to access the DB (i.e. user SYS or SYSTEM):

ALTER SYSTEM QUIESCE RESTRICTED;

Restore the database to normal operation:

ALTER SYSTEM UNQUIESCE;


CPU usage among Numa nodes in sql server

The short answer is that the behavior you indicate is expected. A single thread by definition only exists on a single logical processor, so for a large single-thread operation, you will not see it equally spread across all nodes. It can't be since it only exists in one place.

The longer answer is that SQL Server backup operations use one thread per data file for reading, and one thread per backup file being written to. The reader thread will create a set of buffers to store information which will subsequently be read from by the writing thread.

For instance if your database uses one file on the D:\ drive, and you are backing up to a single file on the E:\ drive, you will have one thread reading from D:\ and storing that information in buffer, and another thread reading from information in the buffers and writing it to the single file on the E:\ drive.

If you back up your database to 4 files, you will have 5 threads: one reading from D:\ and putting information into the buffers, and four getting information from buffers and writing to the separate files on E:.

In NUMA architecture, the design is that these related threads will exist on the same node to optimize memory access - after all, the four writer threads want information from the buffers that the associated reader thread is using. It only makes sense to keep them on the same node.

For further reading see this post by Shanky_621.


Postgres/pgloader: setting a delimiter for multiple line text records

I don't believe you can use pgloader in this way if any of your HTML contains character sequences that would be interpreted in the context of CSV. For example, any backslash followed by digits is perfectly valid (and not-special) HTML, but would be transformed to something else by pgloader while importing. I can't even estimate how pgloader will handle the double-quote and single-quote symbols scattered around in your documents.

You would also need a byte that doesn't exist anywhere in your content to serve as a row delimiter; you could use some low-value control byte for this purpose assuming your HTML is valid and doesn't contain any of these special bytes. This naturally depends on your chosen character encoding, and is rendered worse if you have multiple encodings among your documents.

Assuming everything is UTF-8, the NUL byte should not appear in any UTF-8 stream. Assuming you aren't concerned about special CSV character sequences, you could attempt to do this import using NUL as your separator value:

--with "lines terminated by '\0x00'"

I wouldn't recommend this avenue though for fear of subtle corruption of your documents (as noted above).


Can someone give me feedback on my ER diagram?

1) There are 2 independent relations: from Employee to Job and from Employee to Job History. This means, that potentially Employee can have Jobs that are not contained in the Job History, and vice versa, there can be Job History elements for which there is no element in the Job relation.

2) Wording is misleading. Based on relation from Job to Job History one can suppose that StartDate and EndDate mean period where this Job existed. To different times different Employees could have been assigned to this Job. But based on the relation from Employee to Job History one can suppose that StartDate and EndDate means dates of assignment of particular employee to this Job. Which of these you mean is not clear.

3) I would expect that Job is related to Department, not the Job History.


How do I see where postgres privileges are coming from for a role?

The permissions are stored on the individual objects, and there is no central view to see them all.
You could use the access privilege inquiry functions for each type of object to get that information.
For SELECT on tables, views, sequences, composite types or materialized views that could be:
SELECT oid::regclass, relkind
FROM pg_class
WHERE  has_any_column_privilege(
          'cloudsqliamserviceaccount'::regrole,
          oid,
          'SELECT'
       );

You need similar queries for other object types.
Note that such a query will only give information about objects in the current database, so you would have to run it on each database in turn.
Another crude method is to run
DROP ROLE cloudsqliamserviceaccount;

That will give you an error message and list some of the privileges the role has in the current database.


What's the purpose of the cmd column in the sp_who stored procedure result set?

From the documentation for sp_who (Transact-SQL):

╔════════╦═══════════╦═══════════════════════════════════════════════════╗
║ Column ║ Data type ║                    Description                    ║
╠════════╬═══════════╬═══════════════════════════════════════════════════╣
║ cmd    ║ nchar(16) ║ Database Engine command ( Transact-SQL statement, ║
║        ║           ║ internal Database Engine process, and so on)      ║
║        ║           ║ executing for the process.                        ║
╚════════╩═══════════╩═══════════════════════════════════════════════════╝