(MySQL) user table and profile separate, or same table?

Keep them together.  You're describing vertical partitioning, which is generally only used when a table is getting really wide - dozens or hundreds of fields.  Until and unless there's a compelling reason to separate them (e.g., a single blogger can have multiple profiles, for different blogs, audiences, or languages), there's no need to complicate the schema.


If you are going to remove 'old' records, then you are defeating the "uniqueness" of uniqueness -- since you cannot check against already-deleted rows.

So, make that just a INDEX, not UNIQUE.

Meanwhile, of what use is id?  You may as well promote uniqueness to be the PRIMARY KEY.  (Yeah, this contradicts my previous recommendation.)

But, back to the question of PARTITIONing forcing the inclusion of last_update on all unique keys...

Plan A:

no `id`
PRIMARY KEY (uniqueness, last_update)

Uniqueness would need to be checked with a SELECT, not INSERT IGNORE.

Plan B:

no `id`
INDEX (uniqueness)

This has problems in Replication and other situations when you need to specifically identify a row.

Plan C:

keep `id`
PRIMARY KEY(id, last_update)
INDEX(uniqueness)

Uniqueness would need to be checked with a SELECT, not INSERT IGNORE.

I prefer Plan A, unless there is somthing to indicate that id is necessary.

Meanwhile, see my advice on using PARTITIONing for a sliding dataset -- here

Addenda

With Plan A, do something like this to prevent a race condition:

BEGIN;
$result = SELECT uniqueness FROM tbl WHERE uniqueness = $x FOR UPDATE;
if $result, you have a dup; ROLLBACK and do whatever is appropriate.
INSERT INTO tbl (...) VALUES (...);
COMMIT;

Be sure to check for errors at each step -- deadlocks and timeouts can happen (rarely).  If so, start over at the BEGIN.

If practical, consider batching the SELECT and INSERT so you can check and insert multiple rows at the same time.  (I assume dups are rare?)


How to check backup against SQL AG by Get-DbaLastBackup?

Get-DbaLastBackup currently doesn't support the ability to check the last backup on a database that's in an availability group.  You will either need to modify the GetDbaLastBackup code or use another script that queries the servers in the availability group, collects the last backup information from each of them, and returns the latest date that was collected.

This would certainly be a nice feature--even some of the big money SQL Server monitoring tools don't provide a nice mechanism for monitoring transaction log backups on availability groups.


Loop through all tables and detect changed rows using checksum or hashbytes

select *
from table
where BINARY_CHECKSUM('a','b') <> checksumCol

But if they can change a or b they can change the checksumCol


Execute Stored Procedure that cannot be manually stopped

What you want is, by definition, impossible. Because the definition of admin is: the user to which no privilege can be denied. Therefore any attempt to do something that has the phrase 'admin cannot' is flawed from the get go, because the admin will, simply, always 'can'.

There are more fallacies in your reasoning, like the belief that there is any way to make privilege decision based on untrusted input. Case in point, the requirement that The stored procedure must be invoked from SSMS ONLY.. This is a security decision (privilege to run the procedure) based on an untrusted input (the APP_NAME()).

What I suggest is rather than ask us how to implement what you already concluded must be the solution, better lay down your requirements and let us suggest possible solutions to your problem.


How can I generate a row_number without using a window function?

If it is performance you are worried about, use row_number without order by to avoid sorting.

row_number() over ()


non clustered index on same table but the order is different?

It does matter some times. Colleen Morrow does a good demo on this at
http://colleenmorrow.com/2011/04/07/composite-indexes-does-column-order-matter/

In essence she says that if your WHERE clause contains the first column in the index it will do an index seek. If it contains the third column in the index and not the first it will do an index scan which is not as good. In this case the new index would be better (assuming the optimizer chooses it). If your WHERE clause contains both columns then apparently either index would serve.


postgres listen only runs when I do something

It is only in PSQL when I run any statement do I get my notification

An application doesn't have to poll the database to receive notifications, but it has to poll the status of its socket (network connection) to know that there is a notification to collect, otherwise there's no way to know about it. psql doesn't do that when waiting on user input. It blocks on user input, as most command-line oriented applications.

  Would I have to be running SELECT 1; in a constant loop every second
  or something to get notified?

No, but you need to implement some sort of event loop in your application to detect the notification and handle it. But neither the detection nor the handling need to send any query to the database server.


Multiple SQL Server installations on ONE server?

To assist with your research, this is often called "Instance Stacking" when discussing installing multiple instances of SQL Server on the same box.

You can read about some of the pros and cons here: Should I Install Multiple Instances of SQL Server?

The biggest downsides would be maintenance / availability, which Iłya Bursov mentioned in the comments:

  if this one server is down - all applications are down too (for example during upgrade)

Over time, it can become very difficult to schedule downtime as more apps are added to the machine.

The other tough one is performance.  Making sure that the max and min memory settings for each instance or configured to keep from oversubscribing the box.  Going all rocket surgeon with affinity masking to make sure CPU is distributed (or not doing that and letting one instance trash the server).

This also makes it tough to understand wait stats and other performance metrics, since they are usually reported at the instance level.  You'll need to aggregate them somewhere, or correlate the stats between the different instances.

You can weigh these challenges against the licensing savings / convenience of only having one Windows box.  It sounds like perhaps these applications are not super resource intensive, or not 24/7 or business critical, in which case you could be in for a quick win in terms of licensing and manageability!  But it's good to see all sides in order to make an informed choice.


get table does not exist in mysql after adding new hard disk

You can't move InnoDB tables around by moving files.  There is critical info in ibdata1 that you lose by simply moving /var/lib/mysql/mydb/.

Nor does it work for a database, such as /var/lib/mysql/mydb/.


serializability ordering after one transaction finishes in postgres

The serializable isolation says that the result of concurrent transactions has to be consistent with one serial order, no matter which one.

Here there are two possible serial (sequential) orders, T1 followed by T2 and T2 followed by T1.

The resut of T1 followed by T2 would be:

| id  |  price
---------------
| one | 13   |
| two | 13   |
| three| 10  |

It happens to be exactly the result you got in the concurrent execution of T1 and T2.

This is why there is no reason for the engine to raise a
serialization exception. This particular concurrent execution meets the condition of success of the serializable isolation level.

  I would have expected the serialized order to be T2 -> T1 (since T2
  finished much befor) but the result set implies the serialized order
  is T1 -> T2.

Here you are confused about how it works. The isolation level does not influence the order of execution. It merely detects serialization anomalies to raise an error when they happen (and also in fact, when they might happen, because false positives are possible).


SQL Server - Data Warehouse large log file, how to safely shrink?

Switch to SIMPLE recovery mode.

FULL recovery mode is for when you need point-in-time recovery, and requires that you take frequently transaction log backups.

Since you've stated (in the comments) that you don't need point-in-time recovery, and that you can rebuild the entire thing in a matter of 30 minutes, just switch to simple.

The transaction log file will still be used by in-process transactions, but when those complete/commit (or are cancelled/rolled back) the tran log space is marked for re-use.

Once you've made the change, you can recover some of the additional space by using SHRINKFILE:

DBCC SHRINKFILE (NAME = 'mydatabase_log', 5000)


The wording on this error might as well be:

  ORA-00600: "Something" is broken in the database.

It "recently" gained a close "cousin":

  ORA-07445: "Something" is broken, but not necessarily in the database itself.

If there's nothing immediately obvious in the logs, raise a Support call with Oracle.


Identify when Table Schemas were last Modified

When InnoDB add an index it adds a record to I_S.INNODB_SYS_INDEXES. INDEX_ID of a new index will be highest. Analyzing existing records you can get an idea in what order indexes where created, but that's it. INNODB_SYS_INDEXES doesn't contain any time fields.

On other hand, any DML will be logged to a binlog. If it's enabled and it hasn't been rotated yet you will see the ALTER TABLE with a timestamp.


A trigger making a timestamp of when another column was changed

Semicolon is missing after sysdate().
new should be :new.
You can not assign values to :new in an AFTER UPDATE trigger, becasue it fires after the update, the value is already updated. So I changed it to BEFORE UPDATE.

create table vtiger_crmentity (deleted number, deletedtime date);

CREATE OR REPLACE TRIGGER trg_deletiontime
BEFORE UPDATE
OF DELETED ON VTIGER_CRMENTITY
FOR EACH ROW
WHEN (old.DELETED = 0 AND old.DELETEDTIME is NULL)
BEGIN
:new.DELETEDTIME := sysdate();
END;
/


CREATE WRAPPER, CREATE USER MAPPING and CREATE NICKNAME are all straight-forward SQL statements so you can execute them through JDBC the same as you would, say, a CREATE TABLE statement (same goes for DROP WRAPPER).

However, ATTACH, CATALOG, and UNCATALOG are entirely different beasts. They don't operate on a DB2 database, rather they affect a DB2 instance. I'm not aware of a method of executing these from JDBC (which is largely database-centric and doesn't have any special knowledge of structures "above" the database level). If all this needs to go together you're probably best off shoving it all in a batch file and executing that in a sub-process rather than trying to shoe-horn it into JDBC.


Use multiple columns for location or supertype/subtype?

My approach to design is to think about the what-if's:

How would the design need to change to add a 4th type?
How would it need to change to store a common attribute?
How would it need to change to store an attribute for only one of the types?

I think after going through the process of answering the questions above for the proposed designs, it will be obvious that using a supertype/subtype structure is definitely the way to go (I dare say this is a best-practice for the situation).

There are things to be concerned about, of course -- for example, ensuring that for a given supertype, a matching record must exist in exactly 1 subtype table. With a well-written stored procedure to do "transfers" between types (which is something I assume you'll need), this should be pretty straightforward to implement.


Depending on the version of Oracle and whether you have the appropriate privileges (FLASHBACK ANY TABLE) and if the change was relatively recent,

SELECT text
  FROM dba_views AS OF TIMESTAMP( systimestamp - interval '1' hour )
 WHERE owner = <<owner of view>>
   AND view_name = <<name of view>>

will give you the text of the view definition as of an hour ago.  You can obviously adjust the flashback time to a timestamp just before the DDL you want to undo.


I think that you want something like this (see below for table defs). Something appears to have happened my SQLFiddle demo.

SELECT bs.bugid, bs.bugtitle, bn.bugnotesid, bn.notetime, bn.notetext
FROM bugs bs, bugnotes bn
JOIN
(
  SELECT bugnotesid, MIN(notetime)
  FROM bugnotes
  GROUP By (bugid)
) mytab
ON bn.bugnotesid = mytab.bugnotesid
WHERE bs.bugid = bn.bugid;

CREATE TABLE bugs values
(
  bugid int,
  bugtitle varchar(34)
);

INSERT INTO bugs values(1, 'the first bug');
INSERT INTO bugs values(2, 'the second bug');
INSERT INTO bugs values(3, 'the third bug');

CREATE TABLE bugnotes
(
  bugnotesid int,
  bugid int,
  notetime datetime,
  notetext varchar(400)
);

INSERT INTO bugnotes values(1, 1, '2014-03-14', 'afasdfasdf');
INSERT INTO bugnotes values(2, 1, '2014-03-15', 'afasdfasdf');
INSERT INTO bugnotes values(3, 1, '2014-03-16', 'afasdfasdf');
INSERT INTO bugnotes values(4, 1, '2014-03-17', 'afasdfasdf');
INSERT INTO bugnotes values(5, 2, '2014-02-14', 'afasdfasdf');
INSERT INTO bugnotes values(6, 2, '2014-02-20', 'afasdfasdf');
INSERT INTO bugnotes values(7, 2, '2014-02-22', 'afasdfasdf');
INSERT INTO bugnotes values(8, 2, '2014-02-25', 'afasdfasdf');
INSERT INTO bugnotes values(9, 2, '2014-02-28', 'afasdfasdf');
INSERT INTO bugnotes values(10, 3, '2013-03-14', 'afasdfasdf');
INSERT INTO bugnotes values(11, 3, '2013-03-16', 'afasdfasdf');
INSERT INTO bugnotes values(12, 3, '2013-03-19', 'afasdfasdf');
INSERT INTO bugnotes values(13, 3, '2013-03-20', 'afasdfasdf');
INSERT INTO bugnotes values(14, 3, '2013-03-27', 'afasdfasdf');

[EDIT - after profiling the two queries offered as solutions here]

This bit is really a question to Joshua - not sure if I should make it a new post or keep going here - I can always move it if requested.

Hi Joshua,

I'm sort of starting with MySQL so I took advantage of our exchange to profile our two responses to the OP. I'm a bit puzzled by what I found. Mine takes ~ 200 us, whereas yours appears to take more than double that - and there are far more rows in yours (more info?). I'm curious as to why? Do you know what's going on.

I hasten to add that I did a RESET QUERY CACHE before running the second (mine) query. I also ran them in reverse order and obtained the same result.

I'm baffled!

Joshua's query.

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000186 |
| checking permissions | 0.000011 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000043 |
| init                 | 0.000069 |
| System lock          | 0.000019 |
| optimizing           | 0.000012 |
| statistics           | 0.000038 |
| preparing            | 0.000057 |
| Creating tmp table   | 0.000056 |
| Sorting result       | 0.000008 |
| executing            | 0.000006 |
| Sending data         | 0.000218 |
| Creating sort index  | 0.000060 |
| end                  | 0.000011 |
| removing tmp table   | 0.000029 |
| end                  | 0.000009 |
| query end            | 0.000013 |
| closing tables       | 0.000018 |
| freeing items        | 0.000027 |
| cleaning up          | 0.000024 |
+----------------------+----------+
21 rows in set, 1 warning (0.00 sec)

mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)

My query.

mysql> show profile for query 2;
+----------------+----------+
| Status         | Duration |
+----------------+----------+
| starting       | 0.000131 |
| query end      | 0.000010 |
| closing tables | 0.000007 |
| freeing items  | 0.000020 |
| cleaning up    | 0.000031 |
+----------------+----------+
5 rows in set, 1 warning (0.00 sec)

mysql>


Is there a way to truncate the microseconds of a CSV value when importing data using LOAD DATA INFILE?

You can use variables to edit your input data for example.

  LOAD DATA LOCAL INFILE 'file name ' INTO TABLE table (@var1) SET
  Date=str_to_date(@var1,'%Y-%m-%d')

You can use this method for a lot of different stuff like loading only the 3 first char of a string or doing some mathematical funcion etc.

Example

  LOAD DATA LOCAL INFILE 'file name' INTO TABLE table (@var1) SET
  Date=substring(@var1,1,3).

Hope this helps


Can the subquery be simplified/optimized?

Yes, you can refer to rows of higher levels in a subquery. But that would not solve your problem since you need to consider the whole table, not just the current row.

It's not completely clear what the query is trying to achieve and data types (table definition!) are also missing. Be aware that your invocation of generate_series() returns timestamptz, which is cast to date (I assume) and this cast depends on your current timezone setting. Results for the same query may be different with a different setting for the calling session.

It's a bit cleaner and cheaper to work with timestamp to begin with. See:

Generating time series between two dates in PostgreSQL

And NOT IN (<subquery>) is almost always a bad choice. There are faster options which are less error-prone. NOT IN carries traps for NULL values.

Select rows which are not present in other table

Each of these queries should be faster. The winner depends on data distribution in your table. Test both:

Query 1 (returns text like your original):

SELECT to_char(day, 'YYYY-MM-DD')
FROM   generate_series(timestamp '2015-12-20', LOCALTIMESTAMP, interval '1 day') d(day)
WHERE  NOT EXISTS (SELECT 1 FROM records WHERE day = d.day);

Query 2 (returns date):

SELECT generate_series(timestamp '2015-12-20', LOCALTIMESTAMP, '1 day')::date AS day
EXCEPT ALL
SELECT day FROM records;   -- data types must match


Spike in AWS RDS Oldest Replication Slot lag

The lagging size of the replica lagging the most in terms of WAL data received. Applies to PostgreSQL.

If you’re using logical replication you might consider setting up some monitoring around this metric as it could indicate that your logical replication slot on RDS has switched to inactive - meaning it is no longer consuming the transaction logs.

If left unchecked a replication slot in an inactive state can consume all your disk space.

If you’re using DMS, check the task and replication instance metrics to see if anything sticks out as a bottle neck.

Source: https://docs.aws.amazon.com/en_pv/AmazonRDS/latest/UserGuide/MonitoringOverview.html


SQL2008 - Alter Table Alter Column to increase length results in data loss

Typically you would get an error message and the operation would be prevented, e.g.

create table #name (name nvarchar(20))
insert #name (name) values ('Robert Carnegie')
select * from #name
ALTER TABLE #name ALTER COLUMN name nvarchar(10)  -- error and halts here
SELECT * FROM #name

(1 row(s) affected)
name
--------------------
Robert Carnegie

(1 row(s) affected)

Msg 8152, Level 16, State 13, Line 10
String or binary data would be truncated.
The statement has been terminated.

However, as explained in https://blog.sqlauthority.com/2015/02/14/sql-server-msg-8152-level-16-state-14-string-or-binary-data-would-be-truncated/
that error message isn't produced after the command SET ANSI_WARNINGS OFF.

You could have applied the command to the wrong table or column.

You could have mistaken the column sizes e.g. originally nvarchar(2555) or now nvarchar(51).  Or maybe there had already been an undocumented change in the production database to make the column width 600 characters - or maybe "max" - so, again, you'd be reducing it.  But you should get the error message and the change wouldn't happen.

You could have blocked someone else's transaction storing new data to the table - although I think making a varchar column longer may not even require altering the data pages.  The actual storage wouldn't change.  But - I don't know - SQL Server may check all of the data anyway for any ALTER TABLE ALTER COLUMN command.


How to set up indexed view when SELECTing TOP 1 with ORDER BY from different tables

It seems to ignore any index I put on it

Unless you're using SQL Server Enterprise Edition (or equivalently, Trial and Developer), you will need to use WITH (NOEXPAND) on the view reference in order to use it. In fact, even if you are using Enterprise, there are good reasons to use that hint.
Without the hint, the query optimizer (in Enterprise Edition) may make a cost-based choice between using the materialized view or accessing the base tables. Where the view is as large as the base tables, this calculation may favour the base tables.
Another point of interest is that without a NOEXPAND hint, view references are always expanded to the base query before optimization begins. As optimization progresses, the optimizer may or may not be able to match the expanded definition back to the materialized view, depending on previous optimization activity. This is almost certainly not the case with your simple query, but I mention it for completeness.
So, using the NOEXPAND table hint is your main option, but you might also think about just materializing the base table keys and the columns needed for ordering in the view. Create a unique clustered index on the combined key columns, then a separate nonclustered index on the ordering columns.
This will reduce the size of the materialized view, and limit the number of automatic updates that must be made to keep the view synchronized with the base tables. Your query can then be written to fetch the top 1 keys in the required order from the view (ideally with NOEXPAND), then join back to the base tables to fetch any remaining columns using the keys from the view.
Another variation is to cluster the view on the ordering columns and table keys, then write the query to manually fetch the non-view columns from the base table using the keys. The best option for you depends on the broader context. A good way to decide is to test it with the real data and workload.
Basic solution
CREATE VIEW VI_test
WITH SCHEMABINDING
AS
    SELECT
        t1.PK_ID1,
        t1.something1,
        t1.somethingelse1,
        t2.PK_ID2,
        t2.FK_ID1,
        t2.something2,
        t2.somethingelse2
    FROM dbo.TB_test1 t1
    INNER JOIN dbo.TB_test2 t2
        ON t1.PK_ID1 = t2.FK_ID1;
GO
-- Brute force unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.VI_test
    (somethingelse1, somethingelse2, PK_ID1, PK_ID2);
GO
SELECT TOP (1) *
FROM dbo.VI_test WITH (NOEXPAND)
ORDER BY somethingelse1,somethingelse2;

Execution plan:

Using a nonclustered index
-- Minimal unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.VI_test
    (PK_ID1, PK_ID2)
WITH (DROP_EXISTING = ON);
GO
-- Nonclustered index for ordering
CREATE NONCLUSTERED INDEX ix
ON dbo.VI_test (somethingelse1, somethingelse2);

Execution plan:

There is a lookup in this plan, but it is only used to fetch a single row.
Minimal Indexed View
ALTER VIEW VI_test
WITH SCHEMABINDING
AS
    SELECT
        t1.PK_ID1,
        t2.PK_ID2,
        t1.somethingelse1,
        t2.somethingelse2
    FROM dbo.TB_test1 t1
    INNER JOIN dbo.TB_test2 t2
        ON t1.PK_ID1 = t2.FK_ID1;
GO
-- Unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.VI_test
    (somethingelse1, somethingelse2, PK_ID1, PK_ID2);

Query:
SELECT TOP (1)
    V.PK_ID1,
    TT1.something1,
    V.somethingelse1,
    V.PK_ID2,
    TT2.FK_ID1,
    TT2.something2,
    V.somethingelse2
FROM dbo.VI_test AS V WITH (NOEXPAND)
JOIN dbo.TB_test1 AS TT1 ON TT1.PK_ID1 = V.PK_ID1
JOIN dbo.TB_test2 AS TT2 ON TT2.PK_ID2 = V.PK_ID2
ORDER BY somethingelse1,somethingelse2;

Execution plan:

This shows the table keys being retrieved (a single row fetch from the view clustered index in order) followed by two single-row lookups on the base tables to fetch the remaining columns.


Storing draft version without any constraint checks

It will depend in part on how long a draft should live:

Should it disappear if the user logs out or if user session timeout occurs? Then it would make sense to keep it in memory, e.g. in a Session variable (esp. for Web Apps that often already have support for this).
Should it be kept 'forever' until processing is resumed, e.g. an e-mail draft? Then by all means make it persist in the database.

If you choose to persist in the database then personally I would put it in the regular tables, with a Draft/Final flag on the main record. And think about proper cleanup code (perhaps Final records can't be removed, but Drafts can).

Also consider not enforcing all possible rules on it inside the database, instead I would do so using business logic in the Application layer, which can often be done in more flexible ways than what you can (or want to) express in database constraints.


It appears that, in the summary.txt log file, that second build number is used to indicate the service pack level.

13.0.5698.0 is SQL Server 2016 SP2 CU12.  Summary.txt replaces that bolded 0 with a 2 to indicate it's SP2.

I double checked this on a few of my instances of SQL Server, and it seems to hold true for SQL Server 2016 and 2014 consistently.  For instance, I have a SQL Server 2014 SP2 GDR instance installed locally, and Summary.txt shows the patch level as 12.2.5223.6, even though the real build number (per @@VERSION) is 12.0.5223.6.

The details of what is in Summary.txt aren't documented per se, so it's probably best to rely on SELECT @@VERSION rather than the contents of that file.


Processlist show most of the queries are sending data for select query

This is very simply a case where your server does not have sufficient capacity (often disk I/O, but sometimes memory or CPU) to handle the workload.  Poorly-written queries and insufficient or sub-optimal indexes are a common contributor, as are applications that give up too soon and re-send the same query that's already running, and an improperly sized innodb_buffer_pool_size with InnoDB.
Sending Data was a very poorly-chosen name for the thread state.  It simply does not mean that the server is necessarily "sending" anything.  The server may, in fact, still be doing table scans and may have nothing, yet, to send anywhere, and the thread will still be in this state.  A more appropriate name for the state might have been "preparing data to send."
By contrast, when the server is in fact actually sending data to the client, the state is Writing to net and it's pretty rare to actually see that in the processlist unless the resultset is particularly large or the client is particularly slow about accepting data from the network.

Sending data
The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.
Writing to net
The server is writing a packet to the network.
— http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html


Why does an alias with a 'having' clause not exist in PostgreSQL?

That's the rule the SQL standard sets (and MySQL ignores some of the rules the standard defines and allows invalid SQL).
You can't use a column alias on the same level where you defined it and having is only allowed in a query that uses aggregation. If you want to avoid repeating the expression, use a derived table.
It's also typically faster to use NOT EXISTS instead of NOT IN
SELECT *
FROM (
  SELECT *,
        ( 6371 * acos( cos( radians(latitude) ) * cos( radians( latitude1 ) ) *
          cos( radians( longitude1 ) - radians(longitude) ) + sin( radians(latitude) ) *
          sin( radians( latitude1 ) ) ) ) AS distance
  from table t1
  WHERE verified = true
    AND best_for LIKE '%xyz%'
    AND NOT EXISTS (SELECT *
                     FROM table2 t2
                     WHERE t2.from_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date
                       AND t2.to_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date
                       AND t2.uuid::uuid = t1.uuid)
) x
WHERE distance < 50
ORDER BY distance


SQL performances of selecting recently modified records Solr

In SQL Server, TIMESTAMP does not represent a time, it's just a number representing the rowversion. So it's unlikely you can just change the datatype of that column for performance reasons.
Yes, that will probably perform better, because it can use the index over updated_on, something the MAX()-function prohibits in the original query. As far as I can see, the result should be identical.


not in (Select f.EventID from TABLE1 f)

optimizes poorly.  Turn it into a LEFT JOIN:

LEFT JOIN TABLE1  ON e.EventID = f.EventID
WHERE f.EventID IS NULL


Update: version 166 of the postgresl-common package added pg_renamecluster. See Ilya's new answer about that.

In older versions (including Postgres 9.1.4 on Ubuntu 11.10) the name of a cluster is bound to the name of the data directory in the "Multi-Version/Multi-Cluster PostgreSQL architecture" introduced by Martin Pitt. This is shipped with Debian packages (Debian, Ubuntu and related distributions). For a detailed description of the architecture, see:

/usr/share/doc/postgresql-common/README.Debian.gz

Or browse the file online here.

Other installations do not have all of these features, so some people may be confused when you talk about cluster names.

Just renaming the data directory does not work, because you have to change the directory in/etc/postgresql/<version>/<cluster> accordingly.

And there are a couple of settings in your postgresql.conf, that have to be adjusted, too. Run a grep in the directory to see:

postgres@db:/etc/postgresql/9.1/main$ grep 'main' ./*


Joining multiple tables results in duplicate rows

Presumably, you want to see a single entry for each unique person/address/email/website combination.  If so, try this:

SELECT (person.FirstName + ' ' + person.LastName) as FullName
    , ISNULL(Person.isClient, '')
    , ISNULL(Person.UDF1, '')
    , ISNULL([Address].City, '')
    , ISNULL([Address].[state], '')
    , PersonAddress.Person
    , PersonAddress.[Address]
    , ISNULL(Phone.PhoneNumber, 'N/A')
    , Email.Email
    , Person.Website
FROM dbo.Person
    LEFT JOIN dbo.PersonAddress ON Person.ID = PersonAddress.Person
    LEFT JOIN dbo.[Address] ON PersonAddress.[Address] = [Address].ID
    LEFT JOIN dbo.PersonPhone ON Person.ID = PersonPhone.Person
    LEFT JOIN dbo.Phone ON PersonPhone.Person = Phone.ID
    LEFT JOIN dbo.Email WITH (NOLOCK) ON Person.ID = Email.Person
WHERE (
        isclient = 'prospect'
        or isclient = 'client'
        )
    and [Address] is not null
    and name like '%Mike%'
GROUP BY (person.FirstName + ' ' + person.LastName)
    , ISNULL(Person.isClient, '')
    , ISNULL(Person.UDF1, '')
    , ISNULL([Address].City, '')
    , ISNULL([Address].state, '')
    , PersonAddress.Person
    , PersonAddress.[Address]
    , ISNULL(Phone.PhoneNumber, 'N/A')
    , Email.Email
    , Person.Website
ORDER BY isClient asc;

The GROUP BY clause at the end ensures only a single row is returned for each unique combination of columns in the GROUP BY clause.  This should prevent duplicate rows being displayed in your results.

A couple of things to note:

Always use the schema qualifier on the FROM clause.  FROM Person should be FROM dbo.Person -> this eliminates any confusion if you introduce new schemas in the future, and prevents the query optimizer from having to look for the default schema for your user.
For maintainability in the future, you probably want to name columns the same regardless of which table they are in.  So for instance, instead of the ID column in the People table being named ID, and it being named Person in the Address table, I'd name it PersonID in both tables.  This prevents confusion (read bugs) in joins such as dbo.Person LEFT JOIN dbo.Address ON Person.ID = Address.Person.
Instead of naming tables like Person, they should be named after the collection of items they contain, in plural.  So, Person becomes People, and Address becomes Addresses.  This eliminates confusion -> does the Address table actually contain a single address or multiple addresses?
WITH (NOLOCK) should be avoided like the plague it is unless you fully understand the consequences of reading rows that have been modified by other transactions but not yet committed.  From MSDN:

  Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

  In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

    The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

    The SNAPSHOT isolation level.


You can create views using LIMIT and give those view names to the end users. If you're using a development tool such as DBVisualizer, you can set the number of records to limit.

As always, query labels allow for easy tracking of any requests.


You can use current_setting() function to get the TIMEZONE setting.

SELECT current_setting('TIMEZONE') TZ;

Sample output:

# SELECT  current_setting('TIMEZONE') TZ;
     tz
------------
 US/Eastern
(1 row)

Here is SQLFiddle demo


Is it reasonable to mark all columns but one as primary key?

Imagine you are out with a group of friends and the conversation turns to movies. Someone asks, "What did you think of 'The Three Musketeers'?" You respond, "Which one?"

What additional information would you need to be absolutely certain you are both thinking of the same movie? The director's name? The production studio? The year it was released? One of the star's names? Some combination of two or more?

The answer to my question and yours are the same.

However, I would not think that genre would be a good candidate. One reason, genre is much too subjective a criteria. Is 'The Three Musketeers' action? drama? adventure? comedy? action-adventure? romantic-comedy? I often see the same movie listed under different genres. Even when you allow for multiple genres, your user may select an entirely different one not listed with the actual movie they are looking for.

Even runtimes can differ, especially between theater and VCR/DVD/b-ray versions.

So you need hard, objective attributes which will not change from one media release to another. Unfortunately, that can exclude the name of the movie as movies have been known to be renamed, especially after the release of a sequel.

What about release date? The theatrical release of 1993? The VCR release of 1999? The DVD release of 2004? You get the idea.

Come to think about it, what of all those movies directed by Alan Smithee? Has the real director ever finally stepped forward to put his name on the project after the fact? I don't know.

Hmm, I'd better stop while there are still some criteria left.

Some additional points:

Yes, keep the surrogate key and create a unique index on the natural key fields (if you can finally nail those down). The surrogate key is best for foreign key references. You don't want to duplicate all the natural key fields in every table that contains a reference to a movie.
Drop the array fields (genres, tags, origins). Go ahead and properly normalize those attributes. I have never seen an array field that was not a great deal more trouble than it was worth, especially if you want them to be searchable ("...where genre = 'horror'..."). Note this will not automatically eliminate any issues with case differences and spelling ( "Science Fiction" vs "SciFi" ) -- unless you properly maintain the lookup tables. But it is a whole lot easier to check for such differences in one field of a small table than every array cell of every row of a large table.


What is the difference between membership and securables? Does one override the other?

When setting permissions for a database user, what is the difference between 'Membership' and 'Securables'?

Membership is what the user belongs to... Since users are a database principal this could be database level roles, application roles, etc.

Securables are what the name implies, items that are authorized access to.

So, let's say we have a login which is a server level principal. The login can belong to a server level role.. it can be a member.. of that role. Then we give the server level role access to server level securables, such as and endpoint. Maybe we grant control endpoint to this role. Since the login has membership in this role it is granted the control endpoint permission against the endpoint securable.


I would guess that there is a weekly maintenance job that is running on that one day a week, that is affecting something that is forcing extra reads of the index.  Is your daily batch job always run long on the same day of the week each week?  If so,  look at the views dba_scheduler_jobs and dba_scheduler_schedules to see if any jobs are scheduled.

Now the bigger question is, is why not fix the UPDATE statement to include criteria to avoid updating rows where the value is already the correct value?  IE:

update student set score = 100 where score != 100 -- and whatever other criteria

Also as a temporary fix to your "temporary" index, create it using the nologging clause in the index attributes part might help a little.


Did you check firewall enabled or not on your Linux machine? If it is enabled then you should need to add your port 1521 for accepting requests just like the following command.

sudo iptables -I INPUT -p tcp --dport 1521 -j ACCEPT


Create a table dynamically in SQL Server

You can try with different format of date. I also changed AUTO_INCREMENT to Identity

DECLARE @tableToDump nvarchar(100);

SET @tableToDump = 'backupCdc'+CONVERT(varchar(10),getdate(),112);

DECLARE @DynamicSQL nvarchar(1000);

SET @DynamicSQL=N'create table '+ @tableToDump +' ('+'cid int primary key IDENTITY(1,1), employeeno Varchar(100), fieldName Varchar(100) NOT NULL, fieldValue Varchar(1000))';
/*Adding braces is important as suggested by ypercube */
exec (@DynamicSQL);


MySQL UPDATE statement using a table created on the fly

UPDATE syntax in MySQL is different than SQL-Server. This will do:

UPDATE Table1 AS t
  JOIN
      ( SELECT A, SUM(UNO) AS ctemp
        FROM Table1
        GROUP BY A                     -- no need for `ORDER BY` in either DBMS
      ) AS temp
    ON temp.A = t.A
SET
    t.COUNT = temp.ctemp ;


Does Oracle randomly switch execution plans to check if there is a better solution?

Whenever the optimizer looks at a query to produce a plan the relevant statistics are examined and it will compare the cost of various plans and pick the cheapest.
It doesn't look at all possible plans as it can 'shortcut' some options.

An execution plan will be produced whenever a hard parse occurs and this only happens when the query to be executed does not already exist in the shared pool.

So . . . the execution plan might change over time as your database may change over time, e.g. that small history table you had last year now has 20million rows rather than 10,000 so the optimiser decides to use an index instead of the full scan.

I've come across many 'tipping points' for the optimizer in the past but they are hard to predict at a granular level because we don't know exactly what algorithms the optimizer uses.

Oracle wont however change its execution plan 'just to see' if it already has a plan for the query.

Maybe the person you spoke to was referring to the new adaptive plans in Oracle 12c but that still doesn't mean Oracle will sometimes try a different plan. Look here for info on adaptive plans https://blogs.oracle.com/optimizer/entry/what_s_new_in_12c


large number of tables and postgres query performace

If anything, the large number of tables would affect planning time.

EXPLAIN (ANALYZE, BUFFERS) your query to see the planning time and figure out where the time is spent.


Mysql - Average price for top 10% cheapest volume (algorithm help)

If you are using a older MySQL versions like 5.1 - 5.7 , you need to use MySQL user variables as these MySQL versions does not support window functions and or common table expressions ..

I ported the answers of @Akina and @mustaccio and the  fiddle of Akina
to a MySQL 5.1+ working query.

Warning as MySQL 5.1 to MySQL 5.7 does not use window functions and or common table expressions  you have to write tricky MySQL code like this.  Note if you are using MySQL 8 use one off those answers.

Query

SELECT
 AVG(price)
FROM (

  SELECT
     t.price
   , (@RUNNING_SUM := @RUNNING_SUM + t.volume) AS running_sum
  FROM
   t
  CROSS JOIN (SELECT @RUNNING_SUM := 0) AS init_user_param
  ORDER BY
   t.price ASC
) AS alias
WHERE
 alias.running_sum <= (0.1 * (
                         SELECT
                            MAX(running_sum)
                         FROM (
                           SELECT
                                t.price
                             , (@RUNNING_SUM_1 := @RUNNING_SUM_1 + t.volume) AS running_sum
                            FROM
                           t
                           CROSS JOIN (SELECT @RUNNING_SUM_1 := 0) AS init_user_param
                         ORDER BY
                         t.price ASC
                      ) AS a
                ))

see demo


You have several choices. You can either create the tables without the constraints & add them afterwards, or create the tables with the foreign keys & them import the data with foreign key checks disabled - simply run SET FOREIGN_KEY_CHECKS=0; in your mysql session to temporarily disable them.

For example:

mysql> insert into favourite values( 1,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tmp`.`favourite`, CONSTRAINT `favourite_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`))
mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into favourite values( 1,1);
Query OK, 1 row affected (0.00 sec)


Optimize query with multiple IN statements

Possible optimisations

There are a few tricks you can employ:

Use a partial index to try to skip out all the deleted_at rows.
Use a few strategic composite indexes on tags to look for specific classrooms, students or locations.
Try to have those indexes to be covering indexes
Make sure that PostgreSQL has got all the information about row visibility (i.e. VACUUM ANALYZE) so thtat PostgreSQL tries to use index only scans.
Have the query avoid ORs in conditions (they tend to be very difficult to optimize). Use UNION instead.

Practical approach

This is how I have tried to mimick your scenario on DBFiddle (I have slightly changed your column naming and followed a more "PostgreSQL-standardish" approach):

First, I create a simplified version of your tables, and fill it with an amount of data similar to the one you state

 -- Simplified version of posts table...
 CREATE TABLE posts
 (
     post_id integer primary key,
     deleted_at timestamp without time zone default NULL
 ) ;

 -- Simplified version of tags table...
 CREATE TABLE tags
 (
     tag_id integer primary key,
     post_id integer NOT NULL REFERENCES posts(post_id),

     classroom_id integer,
     location_id integer,
     student_id integer
 ) ;

 -- Insert 116K posts
 INSERT INTO posts (post_id, deleted_at)
 SELECT
    generate_series(1, 116001),
    case when random() < 0.25 then date '2017-01-01' else NULL end AS deleted_at ;

 -- Insert 136K tags
 INSERT INTO tags(tag_id, post_id, classroom_id, location_id, student_id)
 SELECT
      generate_series(1, 136000) AS tag_id,
      random()*116000 +1 AS post_id,
      random()*100 +1 AS clasroom_id,
      random()*100 +1 AS location_id,
      random()*100 +1 AS student_id ;

At this point, I execute a query taht tries to mimick the provided WHERE condition, and have PostgreSQL explain it:

 -- Explain how long does it take to use the "reference query"
 EXPLAIN ANALYZE
 SELECT
     post_id
 FROM
     posts
 WHERE
     posts.deleted_at IS NULL AND
     (
     EXISTS
       (
       SELECT 1
         FROM tags
        WHERE tags.post_id = posts.post_id  AND
          (
          tags.classroom_id IN (1, 20, 35) OR
          tags.location_id IN (32, 40, 23) OR
          tags.student_id IN (20, 30, 40)
          )
       )
     ) ;

 -- DBFiddle (at this moment, and under current load conditions) takes about 70 to 120 ms to perform this query

 | QUERY PLAN                                                                                |
 | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
 | Nested Loop  (cost=4015.66..5369.37 rows=276 width=4) (actual time=41.336..72.243 rows=8094 loops=1)                                                                   |
 |   ->  HashAggregate  (cost=4015.37..4017.37 rows=200 width=4) (actual time=41.320..44.876 rows=10787 loops=1)                                                          |
 |         Group Key: tags.post_id                                                                                |
 |         ->  Seq Scan on tags  (cost=0.00..3999.04 rows=6534 width=4) (actual time=0.035..37.036 rows=11284 loops=1)                                                    |
 |               Filter: ((classroom_id = ANY ('{1,20,35}'::integer[])) OR (location_id = ANY ('{32,40,23}'::integer[])) OR (student_id = ANY ('{20,30,40}'::integer[]))) |
 |               Rows Removed by Filter: 124716                                                                                |
 |   ->  Index Scan using posts_pkey on posts  (cost=0.29..6.75 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=10787)                                             |
 |         Index Cond: (post_id = tags.post_id)                                                                                |
 |         Filter: (deleted_at IS NULL)                                                                                |
 |         Rows Removed by Filter: 0                                                                                |
 | Planning time: 0.353 ms                                                                                |
 | Execution time: 73.526 ms                                                                                |

To use the tricks explained before:

 -- We create a few indexes
 -- One *conditional* index to clear out "deleted_at" rows in posts
 CREATE INDEX idx_posts_alive ON posts(post_id) WHERE deleted_at IS NULL ;

 -- Compound indexes for classroom, location and student + post_id in the tags table
 CREATE INDEX idx_tags_classroom_id ON tags(classroom_id, post_id) ;
 CREATE INDEX idx_tags_location_id ON tags(location_id, post_id) ;
 CREATE INDEX idx_tags_student_id ON tags(student_id, post_id) ;

and make statistics up-to-date for PostgreSQL:

 -- Update all table information to allow for index-only-scans
 VACUUM ANALYZE tags;

 -- (id. just in case, for table posts)
 VACUUM ANALYZE posts;

And now, we

     -- An alternate version of the query, that focus first on tags (using index-only scans)
     -- It avoids ORing conditions (which tend to be difficult to optimize)
     -- and uses, instead, UNIONs

 EXPLAIN ANALYZE
 SELECT
     posts.post_id
 FROM
     tags
     JOIN posts ON posts.deleted_at IS NULL and posts.post_id = tags.post_id
 WHERE
     tags.classroom_id IN (1, 20, 35)
 UNION
 SELECT
     posts.post_id
 FROM
     tags
     JOIN posts ON posts.deleted_at IS NULL and posts.post_id = tags.post_id
 WHERE
     tags.location_id IN (32, 40, 23)
 UNION
 SELECT
     posts.post_id
 FROM
     tags
     JOIN posts ON posts.deleted_at IS NULL and posts.post_id = tags.post_id
 WHERE
     tags.student_id IN (20, 30, 40) ;

 -- In DBFiddle, as of now, this provides about 31 ms exeuction time (about 3x better than the original query)

 | QUERY PLAN                                                                                |
 | :-------------------------------------------------------------------------------------------------------------------------------------------------------------- |
 | HashAggregate  (cost=7097.27..7185.50 rows=8823 width=4) (actual time=34.253..36.499 rows=8094 loops=1)                                                         |
 |   Group Key: posts.post_id                                                                                |
 |   ->  Append  (cost=0.71..7075.22 rows=8823 width=4) (actual time=0.044..30.810 rows=8688 loops=1)                                                              |
 |         ->  Nested Loop  (cost=0.71..2335.97 rows=2956 width=4) (actual time=0.044..8.648 rows=2562 loops=1)                                                    |
 |               ->  Index Only Scan using idx_tags_classroom_id on tags  (cost=0.42..118.05 rows=3931 width=4) (actual time=0.030..0.876 rows=3391 loops=1)       |
 |                     Index Cond: (classroom_id = ANY ('{1,20,35}'::integer[]))                                                                                |
 |                     Heap Fetches: 0                                                                                |
 |               ->  Index Only Scan using idx_posts_alive on posts  (cost=0.29..0.55 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=3391)                 |
 |                     Index Cond: (post_id = tags.post_id)                                                                                |
 |                     Heap Fetches: 0                                                                                |
 |         ->  Nested Loop  (cost=0.71..2354.87 rows=2999 width=4) (actual time=0.023..9.642 rows=3037 loops=1)                                                    |
 |               ->  Index Only Scan using idx_tags_location_id on tags tags_1  (cost=0.42..119.03 rows=3987 width=4) (actual time=0.018..1.090 rows=4076 loops=1) |
 |                     Index Cond: (location_id = ANY ('{32,40,23}'::integer[]))                                                                                |
 |                     Heap Fetches: 0                                                                                |
 |               ->  Index Only Scan using idx_posts_alive on posts posts_1  (cost=0.29..0.55 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=4076)         |
 |                     Index Cond: (post_id = tags_1.post_id)                                                                                |
 |                     Heap Fetches: 0                                                                                |
 |         ->  Nested Loop  (cost=0.71..2296.15 rows=2868 width=4) (actual time=0.023..9.715 rows=3089 loops=1)                                                    |
 |               ->  Index Only Scan using idx_tags_student_id on tags tags_2  (cost=0.42..115.99 rows=3813 width=4) (actual time=0.018..1.159 rows=4117 loops=1)  |
 |                     Index Cond: (student_id = ANY ('{20,30,40}'::integer[]))                                                                                |
 |                     Heap Fetches: 0                                                                                |
 |               ->  Index Only Scan using idx_posts_alive on posts posts_2  (cost=0.29..0.56 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=4117)         |
 |                     Index Cond: (post_id = tags_2.post_id)                                                                                |
 |                     Heap Fetches: 0                                                                                |
 | Planning time: 0.667 ms                                                                                |
 | Execution time: 37.769 ms                                                                                |

The end result is a 3x speed increase when doing SELECT. Obviously, this will need updating depending a lot on your specific case (I've made a very simplified version to show the tricks). Obviously, this comes at the cost of maintaining a certain number of indexes, and increasing INSERT and UPDATE times. You hae to analyze, for your specific case, if this is an overall improvement or an overall worsening..

dbfiddle here


Can I change the profile of oracle sys user?

Our monitoring keeps telling us the password of sys has expired. The "Default“ profile has a standard limit of 180 days.

You should not try to change the Profile for internal, Oracle accounts (indeed, there is one, very oddly named, account that you cannot changed in any way, shape or form!).
You should not change the behaviour of the DEFAULT profile.
The "default" behaviours can change from version to version of Oracle Database and so may be unexpectedly "reset" on you, during an upgrade.
If you want to tie down these behaviours, define your own Profiles and configure them as you need them.
CREATE PROFILE APPLICATION_ACCOUNTS
    LIMIT
    PASSWORD_LIFETIME UNLIMITED
    ;
ALTER USER APP_1_ACCOUNT PROFILE APPLICATION_ACCOUNTS ;
ALTER USER APP_2_ACCOUNT PROFILE APPLICATION_ACCOUNTS ;

CREATE PROFILE INTERACTIVE_USER
    LIMIT
    PASSWORD_LIFETIME 180
    . . .
    ;
ALTER USER FRED   PROFILE INTERACTIVE_USER ;
ALTER USER BARNEY PROFILE INTERACTIVE_USER ;

Anything you don't specify in your custom profiles will, of course, be "inherited" from the DEFAULT one.


Oracle 19c runInstaller reports PRVF-5311 while checking SSH connectivity

Try below solution:
Rename the original scp. ( find where is the scp executable file present)
mv /usr/bin/scp /usr/bin/scp.orig
Create a new file .
vi /usr/bin/scp
Add the below line to the new created file .
/usr/bin/scp.orig -T $*
Change the file permission.
chmod 555 /usr/bin/scp
Once installation is done , you can revert to the original
mv /usr/bin/scp.orig /usr/bin/scp
REFER- >
https://dbaclass.com/article/upgrade-grid-infrastructure-from-oracle-12c-to-19c/


PostgreSQL: How to create full copy of database schema in same database?

There's no simple way to do this in pg_dump/pg_restore itself.  You could try the following if you are able to remove the database temporarily.

Take a dump of your public schema using pg_dump
run "ALTER SCHEMA public RENAME TO public_copy"
Restore your dump of your public schema from step 1 using pg_restore


What is a standard or conventional name for a column representing display order?

It depends on what your philosophy is around column naming.

If you go for very descriptive long column names, then display_order is perfectly fine, since that is exactly what your column contains.

If you are comfortable with shorter names that may seem a little cryptic, but which can be figured out pretty easily, then you could use seq or ord.  For English speakers, seq is pretty unambiguous as there aren't a lot of words that this could reasonably represent.  I would pick that over ord for that reason.


Summation query with complex grouping criteria

One of many possible ways:

SELECT  avg(cnt) AS avg_per_user
FROM   (
   SELECT usr, count(DISTINCT date) AS cnt
   FROM   codes c
   WHERE  actioncode in (61, 62, 700, 701)

   OR     actioncode = 84
   AND EXISTS (
      SELECT 1 FROM codes c1
      WHERE  c1.date = c.date
      AND    c1.actioncode = 85
      )

   OR     actioncode = 10
   AND EXISTS (
      SELECT 1 FROM codes c1
      WHERE  c1.date = c.date
      AND    c1.actioncode = 11
      )
   AND EXISTS (
      SELECT 1 FROM codes c1
      WHERE  c1.date = c.date
      AND    c1.actioncode = 12
      )
   GROUP  BY usr
   ) sub

We don't need parenthesis, since operator precedence works in our favor.

DISTINCT is only still necessary if there can be duplicates. The cases for (10 + 11 + 12) and (84 + 85) only produce a single row.

I am using usr instead of user, since I am not comfortable with abusing reserved words as identifiers. date isn't a good idea either.


MongoDB all replica set members are "OTHER"?

When you enable authentication you must also specify (at the very least) a keyfile also (see step 1 in the procedure for enabling auth), setting auth=true without a keyfile will mean that the mongod processes will not be able to authenticate with each other (this is what the keyfile is used for).  Hence they cannot communicate, elect a primary, respond to heartbeats etc.

Follow the guide/docs linked above to create and configure a keyfile and your replica set should come up and elect a primary without an issue.


MySQL: Try to Port a Trigger from SQL Server

This has been discussed before by others

Making a column immutable in MySQL
What are some good methods of structuring a MySQL audit trail to be both immutable, and perform well for usage?

Your question is a little different in this instance. You may have to experiment with INSERT IGNORE or REPLACE for the desired effect.

You may need to also experiment with disabling the trigger midstream : Disable trigger for just one table


I've seen this very issue and the hotfix that was ultimately released to fix it was actually a direct result of my case with Microsoft CSS. There is no public KB article for the fix. Please make sure you've applied Service Pack 4 and the most recent cumulative update to SQL Server (at the time of writing, that's Cumulative Update #3 (9.00.5259)).

Until the hotfix was released, Microsoft's suggestion was to simply stop creating #temp tables (much like KB #916086). Since this would have meant a substantial re-write of dozens and dozens of reporting procedures, the workaround in my case (regardless of trace flags or temp file layout) was to restart our cluster every other weekend. Yuck.

In order to track down tempdb usage, there are several scripts around that can help, e.g. see Adam Machanic's sp_whoIsActive, specifically:

sp_whoisactive: Analyzing Tempdb Contention
How to identify which query is filling up the tempdb transaction log?

And also this script (and ones in the comments) from @SQLSoldier:

Breaking Down TempDB Contention

I would make sure all your cursors are using LOCAL STATIC READ_ONLY FORWARD_ONLY (see this and this), and see if there are any known expensive queries that make extensive use of #temp tables / @table variables, CTEs, or may contain unnecessary sorts or lead to hash joins... all of which can contribute to the problem (I doubt you'll find one golden cause). The easiest sweeping fix as a "bang-for-your-buck" starting point will be to use proper and inexpensive cursor options instead of the defaults.

In the meantime I would (a) install CU#3 and (b) call PSS. Tell them you are after a very specific fix that has already been confirmed as a bug and released to other users as a private hotfix: "VSTS #109112 - Temp table deferred drop doesn't scale for certain workloads." You may have to pay the case fee initially but, since it is a bug, the charge should be refunded.


postgresql: there is no unique constraint matching given keys for referenced table

After some clarification (in comments and chat), it seems that:

ChildA and ChildB are subtypes.
a VIPUser has a many-to-many relationship with all Child entities ("VIPUser "uses" Child").
there is a many-to-many relationship between VIPUser and ChildType (stored in Parent), essentially what types of Child a `VIPuser can use ("VIPUser "can use" a child of ChildType").

Then the relationships between entities can be shown in the diagram
(I renamed Parent to VIPCanUse):

               ChildType
VIPUser        /      \
      \       /        \
       \     /          \
      VIPCanUse          \
           \           Child
            \         /    |
             \       /     ------------
              \     /        |        |
               \   /       ChildA   ChildB
              VipUses


You have several options:

In SSIS, in the SSIS ToolBox there is a component called Transfer
Database task.
use the method provided here
use the method provided here

I would probable go with 3), but that is a matter of taste.


SQL Percentage of users falling in a specific range

SELECT 100.0 * SUM(birthyear between 1990 and 2000) /
               COUNT(*)  AS pct
    FROM ( SELECT userid, MAX(birthyear) AS birthyear
             FROM visitors GROUP BY userid ) AS x

Some notes:

SUM(boolean-expr) is a way to count how often it is TRUE.
between 1990 and 2000 includes 101 distinct years because BETWEEN is 'inclusive'.  Are you sure you wanted that?
COUNT(*) will count all the rows from the derived table (the subquery)
MAX is a kludge -- A visitor might put down different birthyears on different visits.
Whenever a subquery is used, it must have parentheses around it.
The 'composite' index INDEX(userid, birthyear) would help performance some.


You'd have a:

RecipeSteps table containing, recipe_id, stepnumber for order, and a HowToStep ref

And:

RecipeSections table containing, recipe_id, sectionnumber for order, and a HowToSection ref

The stepnumber and sectionnumber need to be non overlapping (per recipe_id for the pulling in the steps/sections in an order.


Select those entries where the relationship of the columns f1:f2:f3 is roughly number1:number2:number3

Maybe something like ...

-- test table and data

CREATE TABLE `t` (
    `f1`    INTEGER,
    `f2`    INTEGER,
    `f3`    INTEGER
);

insert into t ( f1, f2, f3 )
values (1,3,5),(5,7,3);

select * from t;

f1  f2  f3
1   3   5
5   7   3

Query:

   -- roughly 5:7:4 :: +/-1

    select * from t
    where (
          ( f1 between 4 and 6 )
      and ( f2 between 6 and 8 )
      and ( f3 between 2 and 4 )
    ) ;

    -- result
    f1  f2  f3
    5   7   3

Dbfiddle

--  roughly 5:7:4 :: +2

select * from t
where (
      ( f1 between 5 and 7 )
  and ( f2 between 7 and 9 )
  and ( f3 between 4 and 6 )
) ;

-- no rows returned


RMAN log file with date and name in Oracle 11gR2 in Linux

If I were doing this in the bash shell on Linux, I'd use the date command:

$ date '+%y%m%d-%H%M%S'
20191115-111213

$ LOG_PATH="/u01/backup/logs"
$ UNIQUE="NAME"

$ rman target / LOG="${LOG_PATH}/rman.${UNIQUE}.$(date '+%y%m%d-%H%M%S').log" <<EOF
. . .
EOF

$ ls ${LOG_PATH}
rman.NAME.20191115-111314.log

Remember that you'll need to clean up all these log files, from time to time.


Intellisense is not working since upgrade to SQL Server 2014

Well, I'm a little ashamed... SQL Server 2014 SP1 was not installed on my client machine. Doing so seems to fix the problem with SSMS Intellisense.


ReFS vs NTFS - which is better for SQL Server 2019 on Windows Server 2019

So I'm not familiar with Nimble Storage or ReFS but it looks like the manufacturer (HPE) recommends against using ReFS with Nimble Storage, as a quick Google search turns up on their documentation here.
That being said, what is your Server Admin trying to achieve with Nimble Storage and/or ReFS?...this is definitely a very edge case setup as far as SQL Server goes (the majority of instances in the wild are likely running on NTFS).
In terms of reliability (which it seems like the point of ReFS is), from a database perspective, having a data redundancy, backup, and recovery plan that keeps RTO (Recovery Time Objectives) and RPO (Recovery Point Objectives) in mind is what would be recommended. Whether those objectives are accomplished through appropriately scheduled Full, Differential, and Transaction Log backups, implementing Availability Groups, or any other related means, none of them will be dependent on nor recommend a specific file system for your server's disk.


It doesn't look like there are any supported ways to change this.  However...

I noticed that the bacpac file is just a zip archive, so I unzipped it.  The basic structure of the folder looks like this:

I then used KDiff to diff two bacpacs of the exact same data - one that was read-write, and one that was read-only.  The main difference of substance was in model.xml:

So you should be able to remove those two lines* from the bacpac, and then import it, and it will be read-write! I attempted this on my local Developer Edition copy of SQL Server 2016, but got this error message:

  Could not load schema model from package. (Microsoft.SqlServer.Dac)

  The calculated checksum for model.xml in the package C:\Users\username\Desktop\my_database\my_database.bacpac is different from the saved checksum. (Microsoft.Data.Tools.Schema.Sql)

Which led me to this blog post: Azure SQL Database Support - Editing a .bacpac file

  There are times when for whatever reason you just wish you could make a change to a .bacpac file before you import it into Azure.

So I followed the instructions there, running this PowerShell script to generate a new checksum:

$modelXmlPath = Read-Host "model.xml file path"

$hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")

$fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)

$hash = $hasher.ComputeHash($fileStream)

$hashString = ""

Foreach ($b in $hash) { $hashString += $b.ToString("X2") }

$fileStream.Close()

$hashString

Then updating the Origin.xml checksum line at the end:

<Checksums>
    <Checksum Uri="/model.xml">long checksum number goes here</Checksum>
</Checksums>

Zipped all the files again (I used the "Deflate" algorithm in 7-zip), and then imported the database.  It came in perfectly as a normal, read-write database.

*Note: I didn't explicitly enable snapshot isolation, that happens automatically when you set a database to read-only.  Not even Paul White is sure why, so what hope do the rest of us have?  For more, see Snapshot_isolation_state_desc on a Read only/Stand by Database


How to restrict access to SQL Server user in some period of time

You can :

Method1

1) Create a trigger, in case if its the concern for a single table that user A should be not be conflicting. Therefore you can create the trigger which should restrict user A from the current permission it have , that you would like to restrict.

2) Once done with creating trigger, you will need to create an SQLAgent job which enables the trigger during the time when user B starts its process and second step of that job will be to disable the trigger when user B finishes it tasks.

Method 2

You can also, in case the whole database is in question where user B needs no conflict with user A you can script out the permission for user A and schedule a jobs which (during you're required time frame)will remove the permissions of user A and second job which add backs the permission of user A after a defined time as per the need.

There can be various other methods for sure, but may be i am not able to recall as of now. Hope if above 2 helps.


How to set all auto increments to 1 in a Schema

If the tables already have rows, you can't do it.

If you want to throw away all the data, then you could do TRUNCATE TABLE for each table.

Or you could get all the table definitions via mysqldump --no-data and make sure there are DROP TABLE for each.  Then have a script that runs the output.


How to restore a database with innodb tables from files?

With innodb files you need to restore the entire database server to a new instance.Use mysqldump oc_activity_mq to extract a SQL version of the table out of that backup. And then import this into your active instance.

This assumes you've made consistent backup. If mysql was running and being updated at the same time it was copied it might have some form of corruption.

Revist your backup strategy using innodbbackup, lvm, and/or mysqldump/pump.


Set FORCESEEK for only one of hinted indexes

I think the answer to your question is no, at least I haven't found a way to do it.

It would be interesting to know what you hope to accomplish with this set of hints, specifically as a query hint (rather than normal inline table hints).

Here's a repro of your scenario:

DROP TABLE IF EXISTS dbo.Hintable;
CREATE TABLE dbo.Hintable
(
    Id int NOT NULL,
    Rando int NOT NULL,
    INDEX CSI_Index CLUSTERED COLUMNSTORE,
    CONSTRAINT PK_Index PRIMARY KEY NONCLUSTERED (Id)
);

INSERT INTO dbo.Hintable
    (Id, Rando)
SELECT
    v1.number,
    MAX(v1.[high])
FROM master.dbo.spt_values v1
WHERE v1.[high] IS NOT NULL
GROUP BY [number];

SELECT *
FROM dbo.Hintable ta
WHERE Id = 500
OPTION (TABLE HINT(ta, NOLOCK, INDEX (CSI_Index, PK_Index), FORCESEEK));

db<>fiddle link

That results in this error, which is different from the one you reported:

  Msg 8722 Level 16 State 1 Line 20
  Cannot execute query. Semantic affecting hint 'nolock' appears in the 'TABLE HINT' clause of object 'ta' but not in the corresponding 'WITH' clause.  Change the OPTION (TABLE HINTS...) clause so the semantic affecting hints match the WITH clause.

That's a documented limitation, and is easy to resolve by removing NOLOCK or adding it to an actual table hint:

SELECT *
FROM dbo.Hintable ta WITH (NOLOCK)
WHERE Id = 500
OPTION (TABLE HINT(ta, NOLOCK, INDEX (CSI_Index, PK_Index), FORCESEEK));

Now we get the error you implied (but didn't include specifically in your question):

  Msg 366 Level 16 State 1 Line 19
  The query processor could not produce a query plan because the FORCESEEK hint on table or view 'Hintable' cannot be used with the column store index 'CSI_Index'.

There is a way to write the query that compiles with a combination of query and table hints:

SELECT *
FROM dbo.Hintable ta WITH (NOLOCK, INDEX (PK_Index), FORCESEEK)
WHERE Id = 500
OPTION (TABLE HINT(ta, NOLOCK, INDEX (CSI_Index, PK_Index)));

db<>fiddle link

The execution plan is kind of odd looking, in that it includes a seemingly-pointless index intersection:

Both the scan and the seek filter on Id = 500
the scan outputs Id, Rando, and the columnstore row locator
the seek just outputs the row locator
and then they join on the row locator

The scan can satisfy the query without needing the seek or join. The join doesn't filter out any rows (since it is on the row locator of the base table). The optimizer is honoring the requirement to use both indexes in the only way it can.


Clearing Log Shipping config Secondary server inaccessible

I had similar error few months ago and solved by this.

USE master;
GO

EXEC master.dbo.sp_delete_log_shipping_primary_secondary
    @primary_database = N'<PrimaryDatabase>'
   ,@secondary_server = N'<SecondaryServer>'
   ,@secondary_database = N'<SecondaryDatabase>'

GO


Installing oracle 9i in RHEL5 ... error during the process

OK, I'm sorry ... the problem was that the installers were corrupt, just use others and everything went well. Anyway if someone needed it I leave here step by step the installation that worked for me:

http://ivan.kartik.sk/oracle/install_ora9_elinux.html

Thank you very much


reduce number of innodb pages read by a query

Short Answer:  It depends.

Let's start by assuming "simple query on secondary key", leads to one row and you have

INDEX(x)
SELECT * FROM t WHERE x = 123

Then, for 600M rows, and a cold cache (buffer_pool), here is the analysis:

Drill down the BTree for INDEX(x) looking for 123:  about 5 blocks need to be read because the BTree will be about 5 levels deep.  The leaf node will give you the primary key column(s) for the one (as assumed) row with x=123.
Drill down the BTree for the PRIMARY KEY and data:  also about 5 blocks.

Total 10 blocks.

Let's expand that to saying that there are 200 rows with x=123.

5 blocks to get started in the index.
Scan forward in the index, perhaps 2 more blocks.  (Rule of Thumb:  100 rows per block.)
For each of the 200 x=123 index entries, drill down the PK to find a row.
The first one (assuming cold cache) needs to read about 5 blocks (as before).
The other 199 will need fewer than 5 blocks.  For one thing, the root node is already there.  Are the x=123 values randomly scattered?  Or is x, say, a TIMESTAMP that marches in sync with the PK (which might be an AUTO_INCREMENT?)  So, these 199 could average as low as nearly 1.

Total:  ~20 to ~600.

Now let's change to steady state:

If your table will fit in cache, then eventually the number of blocks read will be zero.  But, let's say your 600M rows is too big for that.

Again, I invoke a Rule of Thumb:  "Ignore the drilldown, but not the leaf node".

for 1-row:  2 blocks.
for 200 rows:  3+200 blocks -- 3 in the index; 200 (or fewer, depending on randomness of x versus PK) in the data.

Rick's "Rules of Thumb".

Text/Blob

If there are large text or blob columns and you are fetching them, then "off-block" storage will be hit.  This will add perhaps at least 200 more reads -- depending of number/size/existence of the wide columns in each row.


Intermittent SQL Delegated Authentication Errors

If your SQL Servers are clustered, and their service accounts are configured to allow them to manage their own Service Principal Names, you might be running into a known issue.
In an environment with physically distanced Domain Controllers, a planned failover  event can cause the Kerberos delegation for double-hop authentication to fail with symptoms matching the error message in your question.
When SQL Server shutdown occurs, the SQL Server service attempts to deregister the Kerberos SPN with a Domain Controller.  Then cluster failover occurs, and the SQL Server service is started up on one of the other nodes.  This can happen quickly enough that the other Domain Controllers in the organization haven't yet received the synchronization for the removal of the SPN.  At this point, SQL Server will try to register the SPN, but can't because it hasn't yet been removed on all Domain Controllers.  As a result, the attempt at registering the SPN fails.  SQL Server starts up normally, accepting auth requests via NTLM and no one is any wiser unless someone attempts double-hop authentication.
There isn't a great solution to this, other than having permanent SPN entries created in the Active Directory domain, and preventing SQL Server from managing them automatically.


SELECT B.*,A.sum_hits
FROM
(
    SELECT date_time, SUM(diac.requests) AS sum_hits
    FROM ox_data_summary_ad_hourly diac
    JOIN ox_zones z ON (z.zoneid = diac.zone_id)
    JOIN ox_affiliates p ON (p.affiliateid = z.affiliateid AND p.agencyid = '3')
    WHERE diac.date_time BETWEEN '2012-07-01 00:00:00' AND '2012-07-01 23:59:59'
    AND p.agencyid = '3'
    AND z.zone_type in ('application')
    AND z.delivery in (8)
    GROUP BY date_time
) A INNER JOIN
(
    SELECT s.date_time AS date_time,
    SUM(s.requests) AS sum_requests,
    SUM(s.impressions) AS sum_views,
    SUM(s.clicks) AS sum_clicks,
    SUM(s.conversions) AS sum_conversions,
    SUM(if(d.network_type like '%_user' and d.is_network=1,impressions,0)) AS sum_effimp,
    SUM(s.total_revenue) AS sum_revenue,
    SUM(s.total_revenue) AS sum_spend,
    SUM(s.total_basket_value) AS sum_bv,
    SUM(s.total_num_items) AS sum_num_items,
    SUM(s.total_pubrevenue) AS sum_pubrevenue
    FROM ox_data_summary_ad_hourly AS s
    INNER JOIN ox_banners AS d ON (d.bannerid=s.ad_id)
    INNER JOIN ox_campaigns AS m ON (m.campaignid=d.campaignid)
    INNER JOIN ox_clients AS a ON (a.clientid=m.clientid)
    LEFT JOIN ox_zones AS z ON (z.zoneid=s.zone_id)
    LEFT JOIN ox_affiliates AS p ON (p.affiliateid=z.affiliateid)
    WHERE a.agencyid = 3
    AND z.zone_type = 'application'
    AND z.delivery = 8
    AND s.date_time>='2012-07-01 00:00:00' AND s.date_time<='2012-07-01 23:59:59'
    GROUP BY date_time
) B USING (date_time);


MySQL - "CREATE_OPTIONS" in "information_schema.tables"?

I got answer for my question. "CREATE_OPTIONS" gets updated as row_format=DYNAMIC when you specify it in the create statement explicitly. For partitioned tables, it takes row_format=DYNAMIC partitioned.

Table collation has nothing to do with it.


Is there a simple way in VS SSIS to swap existing DB connection manager with existing project connection manager?

I guess the answer is no but I can say that I followed the steps in the article and it worked for me using SSDT 2017. Wasn't too bad doing the necessary find and replace in Notedpad ++ (vs the carpal tunnel method of clicking around a million times in VS which is more error prone compared to find and replace)


Put auto_explain into shared_preload_libraries in postgresql.conf and restart PostgreSQL.

Set

auto_explain.log_min_duration = 0
auto_explain.log_nested_statements = on
auto_explain.log_analyze = on
auto_explain.log_buffers = on

in postgresql.conf (and reload).

Then run the slow query again.

This will log all SQL statements and their execution plan in the PostgreSQL log. Examine the plan for the statement run in your function. That should give you a clue.

The most likely explanation would be a missing index on catalogue.users(username).


Fetching Remote Encryption Key for MariaDB

Rather than making it its own server, extend the mariadb service:
mkdir /etc/systemd/system/mariadb.service.d

create a file /etc/systemd/system/mariadb.service.d/prefetch.conf with contents
[Service]
ExecStartPre=/usr/bin/wget -P /etc/mysql https://example.com/my_maria_key.key
ExecStartPost={something to ensure the key is loaded - mysql -e '...' }
ExecStartPost=rm my_maria_key.key

I don't know if this will deleting the key too early. Consider including journalctl -u mariadb.service -n 50 in your question.


TSQL DateTime DataType - fractional seconds vs milliseconds

Instead of specifying 23:59:59.999 you must specify 23:59:59.997 due to rounding.

The far better way would be to :

WHERE MyDate >= '2013-03-05' AND MyDate < '2013-03-06'

Since this captures ALL activity on 2013-03-05


How to check the consistency of tables in mysql

Run CHECKSUM TABLE command against a table.

There is a caveat for this. According to the MySQL Documentation on CHECKSUM TABLE:

  If the checksums for two tables are different, then it is almost
  certain that the tables are different in some way. However, because
  the hashing function used by CHECKSUM TABLE is not guaranteed to be
  collision-free, there is a slight chance that two tables which are not
  identical can produce the same checksum.

OK, CHECKSUM TABLE is 99.99%, not 100%.

How can you aggressively check all the items of a table for matches and mismatches? You can actually run a great tool from Percona Tools called pt-table-sync. It will check for differences in tables between Master and Slave, as long as the table structure is identical.

For example, to sync the table db.tb1 on a Slave in relation to its Master, run pt-table-sync as follows:

pt-table-sync --print --sync-to-master h=SlaveIP,D=db,t=tbl > SQLtoFixDifferences.sql

When you run this, the table db.tb1 on the Slave is compared to that of its Master using the Primary Key (or Unique Key is there is no Primary Key) of the Slave's db.tb1. The output will be a series of SQL commands, usually REPLACE INTO and DELETE FROM. The script SQLtoFixDifferences.sql is to be executed on the Slave. When done, the data in db.tb1 should be identical to its counterpart on the Master.

To test it then, run these commands:

CHECKSUM TABLE db.tb1;
SELECT COUNT(1) FROM db.tb1;

The results should be identical on Master and Slave.

CAVEAT

I have never used pt-table-sync. I have been using mk-table-sync (MAATKIT tool from Percona) for years and I know it has always worked for me. I fully trust Percona's pt-table-sync is at the least the same, if not superior, quality.


Why can't I use a CASE statement to see if a column exists and not SELECT from it?

The following query uses the same idea as in this amazing answer by ypercube:

SELECT x.*
FROM (SELECT NULL AS SomeCol) AS dummy
CROSS APPLY
(
  SELECT
    ID,
    SomeCol AS MyTest
  FROM dbo.Customers
) AS x;

It works like this:

if dbo.Customers has a column named SomeCol, then SomeCol in SomeCol AS MyTest will resolve as dbo.Customers.SomeCol;
if the table has no such column, the reference will still be valid, because now it will be resolved as dummy.SomeCol: dummy columns can be referenced in that context.

You can specify multiple "spare" columns that way. The trick is not to use the table alias for such columns (which is a frowned-upon practice in most situations, but in this case omitting the table alias helps you to resolve the issue).

If the table is used in a join and the other table has its own SomeCol, you will probably need to use the above query as a derived table before using it in the join in order to keep the trick working, something like this:

SELECT ...
FROM
(
  SELECT x.*
  FROM (SELECT NULL AS SomeCol) AS dummy
  CROSS APPLY (
    SELECT
      ID,
      SomeCol AS MyTest
    FROM dbo.Customers
  ) AS x
) AS cust
INNER JOIN ...
;


Running out of disk space, start saving new data to different disk

The normal short-term solution here is to simply move some indexes and tables to the new filegroup to free up space in the primary filegroup.

But

  Can I implement manual partitioning so all new data goes to the new disk?

Yes you can.  The key idea is to create a new table on a partition scheme that is compatible with the existing table, and partitioned on a clustered index key column.  Then switch the table into a partition on that partition scheme, and split its partition function so new rows go to the new filegroup.

And I just discovered that you can create a partitioned table with only the "primordial" partition, and then switch a non-partitioned table into the primordial partition without needing a CHECK constraint on the non-partitioned table.

Here's an end-to-end demo:

use master
go
drop database parttest

go
create database parttest
go

use parttest
go
drop table if exists large
go
create table large(id int identity primary key, a char(1000), b int, c datetime);
create index ix_large_b on large(b);

--load some data
with q as
(
  select top 100000 row_number() over (order by (select null)) n
  from sys.messages m, sys.objects o
)
insert into large(a,b,c)
select replicate('z',n%989), n%39, getdate()-n%1000
from q;

--add a new filegroup with a single file
alter database current
  add filegroup newfg

alter database current
  add file (name = 'newfg_data', filename = 'c:\temp\newfg_data.mdf')
  to filegroup newfg

--create the new partition function and partition scheme
--but don't specify any boundary points, so the target
--table can be switched in without a check constraint
create partition function pf_large(int)
  as range right for values ()

 create partition scheme ps_large
   as partition pf_large all to ([Primary])

--create the table to switch into
create table large2(id int identity primary key, a char(1000), b int, c datetime)
 on ps_large(id);

--create the secondary indexes on the new table
--if you don't create them they won't be switched
create index ix_large2_b on large2(b) ;

alter table large switch to large2 partition 1

-- drop and rename
begin transaction
  drop table large
  exec sp_rename 'large2','large'
commit

--reset the identity values
dbcc checkident('large')
go

--split the partition function so new rows to to the new filegroup
alter partition scheme ps_large next used newfg

begin transaction
  declare @splitPoint int = 1+(select max(id) from large with (tablockx) )
  alter partition function pf_large() split range (@splitPoint)
commit

go

--load some more data
with q as
(
  select top 100000 row_number() over (order by (select null)) n
  from sys.messages m, sys.objects o
)
insert into large(a,b,c)
select replicate('z',n%989), n%39, getdate()-n%1000
from q;

go

--check that it went to the new filegroup
select i.index_id, p.partition_number, ds.name fg, au.data_pages
from sys.tables t
join sys.indexes i
  on t.object_id =i.object_id
left join sys.partitions p
  on p.object_id = t.object_id
  and p.index_id = i.index_id
left join sys.allocation_units au
  on (au.type in (1,3) and au.container_id = p.hobt_id)
   or(au.type = 2 and au.container_id = p.partition_id)
left join sys.data_spaces ds
  on ds.data_space_id  = au.data_space_id
where t.object_id = object_id('large')
order by ds.data_space_id, index_id, partition_number

outputs

index_id    partition_number fg           data_pages
----------- ---------------- ------------ --------------------
1           1                PRIMARY      14286
2           1                PRIMARY      174
1           2                newfg        14286
2           2                newfg        231

You will have to drop and recreate foreign key constraints referencing this table.  And recreating the FK constraints after the switch the switch require table scans during DDL, so can be lengthy offline operations.  To optimize for up time you can recreate the FK constraints with NOCHECK and CHECK them later.  Until then they will be enforced for DML but not trusted by the query optimizer.


How does SSMS connect to a server's database without the instance name?

For SQL Server the default instance of a  is SQLServerName\MSSQLSERVER.  However, since MSSQLSERVER is the default instance name that is never displayed.

A SQL Server using the default instance is only referenced by the SQLServerName.

Actually, you can install SQL Server Express and give it the instance name of MSSQLSERVER and you will get the default instance.  Of course, any server can only have one default instance.

That is probably most useful when installing a SQL Server Express and you do not want to have SQLExpress for the part of the name.

When installing a default instance of SQL Server Express at the Instance Configuration page, you must choose the Named Instance radio button and type in the default instance name of MSSQLSERVER.

Just take the steps to name the default instance name MSSQLServer (which will not appear).  Any other instance name on the computer needs a unique name, such as SQLServerName\Alpha, SQLServerName\Beta, et cetera.

Note: Installing SQL Server Express will, by default behavior, put the SQLExpress into the server name.  But that is not the default name. Whenever there is \Sometext name, then that is called a named instance.


Take a look at http://ola.hallengren.com/

He has a great maintenanceplan which is free to use. I am using it for every project.


mongoexport not exporting all possible data

I can only speculate without having access to the data, but I suspect that you have non-numeric data in the id field.  Try using the $type operator to verify that some of the fields have not been passed in as strings or similar and that all the fields are homogeneous in terms of type.


row count incorrectly displayed as 0 in redshift

Given the following table:

CREATE TABLE
    t
AS
SELECT
    *
FROM
(
    VALUES
        (   1,    1),
        (   2,    0),
        (NULL,    1)
) AS x (user_id, some_property) ;

The SELECT COUNT from the original poster returns 0, the second one returns 2, and the last one returns 1.

Let's see why:

If you take out the counts and retrieve the rows, we get:

(1)

SELECT DISTINCT
    user_id
FROM
    t
WHERE user_id NOT IN (SELECT DISTINCT user_id FROM t WHERE some_property = 1) ;

|---------|
| user_id |
|---------|

(that is: nothing. The count, in this case, is clearly 0)

(2)

SELECT DISTINCT user_id FROM t ;

|---------|
| user_id |
|---------|
|   NULL  |
|---------|
|    1    |
|---------|
|    2    |
|---------|

(there are 3 rows, but the count is 2, because you don't count NULL)

And last,

(3)

SELECT DISTINCT user_id FROM t where some_property = 1 ;

|---------|
| user_id |
|---------|
|    1    |
|---------|
|   NULL  |
|---------|

(There are 2 rows, but Count is 1, because, again, you don't count NULL).

You have to take into account that, according to SQL standard:

a NOT IN (x, y, z)                is equivalent to
NOT (a IN (x, y, z))              which in turn, is equivalent to
NOT (a = x OR a = y OR a = z)

If a IS NULL, you get NOT (NULL = x OR NULL = y OR NULL = z) which is NULL, regardless of the values of x, y or z.


AlwaysOn Statistics getting updated on Readonly node immediately after updating primary node with Fullscan

I did open a case with Microsoft Support. I was notified similar behavior was reported by other customers. For now the solution is to rebuild the index.  Microsoft is investigating further about the root cause.


Controlling how tempdb activity is split across drives (tempdb on both solid state and spinning drives)

File writes are distributed across files in the same file group proportionally based on the current size of each file in the file group.  This is referred to as the "proportional fill algorithm" - look at http://sqlserver-performance-tuning.net/?p=2552 for some interesting details around that.

tempdb can have only a single filegroup.  If you attempt to create a filegroup in tempdb you get the following:

Msg 1826, Level 16, State 1, Line 1
User-defined filegroups are not allowed on "tempdb".

If you have trace flag 1117 turned on, files within a group will autogrow simultaneously across files in the filegroup for each file that is not currently at its maximum size, and where space exists on the disk.

Does your instance have trace flag 1117 turned on?  You'd probably want it off in this particular instance even though "best practices" often indicate having this turned on.  There is an item on Microsoft Connect asking to have a setting like this that can be enabled/disabled on a per-database basis, here:  https://connect.microsoft.com/SQLServer/feedback/details/781198/trace-flag-1117-autogrowth-of-data-files-is-instance-wide-would-like-a-flag-for-just-tempdb

Assuming the SSDs are devoted to tempdb, I agree with your assertion and suggest making the tempdb on the SSDs as big as possible (not 100% of the drive, perhaps leave 10% free).  Make the tempdb files on disk as small as possible, say 1MB, with autogrowth, and maximum file size as big as you need.  Monitor for tempdb filegrowth on the HDDs, and make the case for getting bigger SSDs if you think the company would benefit from it.

According to the documentation, SQL Server files can be created on raw partitions (partitions that haven't been formatted) by using just the drive letter of the partition in the ALTER DATABASE ... ADD FILE syntax.  This apparently removes the need to grow or shrink the file since it inherently uses the entire raw partition, as needed.  I'm uncertain if this would help your situation or not; just thought I'd throw it out there as an interesting factoid.  See "If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be put on each raw partition."


Simple model database transaction log full 'CHECKPOINT'

It sounds like you've got a huge transaction that has remained open which has done a lot of work.  Run DBCC OPENTRAN on the database and see how long the oldest transaction has been open for.  You'll probably need to kill that transaction (or have the user commit if it's someone in Management Studio).  Then the log will clear automatically.


Elapsed time in SSMS is consistently higher than SQL Server Execution Time - connection issue?

If you're after a system that takes 0 ms to return and render results, good luck.

This doesn't mean that SQL Server is 'just slow', though I'm not sure what your expectations for grid rendering are.

The only way to see what your server is waiting on is to look at its wait stats. If you'd like a free script that does that, head over here and check out sp_BlitzFirst (full disclosure, I contribute to this open source project).

You can run it this way to look at your server's wait stats since it started up:

EXEC sp_BlitzFirst @SinceStartup = 1

Or this way to get a sampling during a window length of your choosing:

EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1

And if you find a system that returns and renders results in 0 ms, drop me a line! I'd love to hear about it.


Synchronizing data from AWS Sql Server to local database

The basic options for replication in SQL Server are available between AWS and on-premise...as long as you have the network access in place.

I'm not sure what source you used to find it was not supported. AWS also offers the Database Migration Service which supports multiple RDBMS and various targets. You can find more information on that here.


In oracle XE, what is the difference between the autobackup and backupset direcories?

AUTOBACKUP, by default, contains automatic backups of the controlfile and the spfile (if the database has one), if the AUTOBACKUP feature is enabled.

rman target /
show all;

There you should see:

CONFIGURE CONTROLFILE AUTOBACKUP OFF# default

Or ON, if it is configured. With AUTOBACKUP enabled, the controlfile will be automatically backed up on backup operations, and on changing the structure of the database (e.g creating a new tablespace, adding a new datafile). In 10g it is performed immediately after changing the structure, starting with 11.2, it is performed a bit later (Controlfile Autobackup Deferral), allowing the execution of multiple structure changes without creating an unnecessary controlfile autobackup for each action.

BACKUPSET, well, that is where all other backups go, by default. Backups of the datafiles, archivelogs and controlfile and spfile backups if AUTOBACKUP is disabled, or created explicitly.


How SQL Server distributes queries between Database Engine and FTS Engine

You might find the diagrams and explanation found in the SQL Server 2008 R2 documentation useful. (Nothing has fundamentally changed recently on Full Text processing.)

https://technet.microsoft.com/en-us/library/ms142541(v=sql.105).aspx

Quoting from that explanation: "The query processor compiles and executes SQL queries. If a SQL query includes a full-text search query, the query is sent to the Full-Text Engine, both during compilation and during execution. The query result is matched against the full-text index."

So you might view the Full Text search as a 'function' that returns its portion of the query from the Full Text Engine, then joins with any operators from the Database Engine that may also be part of the overall SQL Server query.


Fastest way to restore a Sql Server database

Database snapshots are probably the right answer for your specific situation, per Nic's comment.

But for future users finding this question, there are definitely ways to improve native SQL database backup and restore times:

Backup your databases with compression: I/O is typically the bottleneck, this shifts some of the work to CPU.
Change your storage configuration: Store backups locally or on higher-performance SAN
Backup your databases to multiple files (stripes): This might depends on your specific storage and network configuration, but I've found 4- and 8-stripe backups and restores in our environment complete 2x-10x faster than single-stripe backups. Certainly worth some testing.
Make sure instant file initialization is enabled: Helps you carve out the space for restoring files very quickly
Reduce the size of the database: Drop old tables, etc. When possible, of course.
Shrink away empty space: All the appropriate warnings and caveats about shrinking apply, of course.
Enable page or row compression: Makes the database files smaller, so less to write upon restore.


PostgreSQL GRANT ALL on mutliple tables using LIKE

GRANT doesn't take wildcards in table identifiers.

You can use ON ALL TABLES IN SCHEMA, but that requires a single schema name.

If you want to do things with wildcard pattern table names you will need to use PL/PgSQL's EXECUTE format(...) in a DO block to loop over the information_schema.tables view. See many related answers here on DBA.se and Stack Overflow for dynamic DDL in PL/PgSQL.

Untested rough example to give you the idea:

DO
$$
DECLARE
    t record;
BEGIN
    FOR t IN
    SELECT table_schema, table_name
    FROM information_schema.tables
    WHERE table_schema = 'public'
    AND table_name LIKE 'test\_%'
    LOOP
        EXECUTE format('GRANT ALL ON TABLE %I.%I TO test;', table_schema, table_name);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

For what %I means see the reference for the format function. If you don't have a format function your PostgreSQL is obsolete and you should probably plan an upgrade; you can use quote_ident and string concatenation in the mean time.


Firstly, make sure the account executing the job has sufficient privileges to run alter index.  If that fails, then find the index "DTA_reports_indexcolumn_index2" and see if you can rebuild it yourself by hand.  If that works, then you may have another process hitting this table at the same time the index maintenance is running.  You'll have to look through what else is running around this time.

If rebuilding it by hand doesn't work, then explore what error you get, if you get one.  You might have to drop and recreate the index.  You might also run dbcc checkdb against the database to ensure that there's no corruption and run dbcc checktable against said table to ensure it's not corrupted.


how to change a user password while checking the current password in oracle?

11g and higher accept this syntax:
ALTER USER username IDENTIFIED BY new_password REPLACE old_password;


Unable to check is schema is present first

The schema creation has to be the only statement within it's own batch, with no statements preceeding or following it.

As the IF statement is considered to be in the same batch, the statement is syntactically incorrect.

You have to wrap the schema creation within an EXEC, so it is considered to be in a separate batch.

Therefore the correct syntax for what you are doing is:

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'SQLInstance_Baseline'))
BEGIN
    EXEC ('CREATE SCHEMA [SQLInstance_Baseline] AUTHORIZATION [dbo]')
END