I found that the following changes to postgres.conf yielded the best results so far:

archive_mode            = off       # old: on
autovacuum              = off       # old: on
commit_delay            = 100000    # old: 0
maintenance_work_mem    = 1GB       # old: 512MB
max_wal_size            = 10GB      # old: 1GB
synchronous_commit      = off       # old: on
wal_level               = minimal   # old: archive
work_mem                = 1GB       # old: 8MB

Also I had quite trouble getting the most out of a single pgloader invocation to migrate all the table. This would have been the most desired way, because pgloader that way also re-creates all the FK between the imported tables.

But the problems I was facing (pgloader hanging, not achieving the performance I was aiming for in terms of total time) I settled with this approach:

run a separate pgloader instance for every individual table, in parallel, inside tmux
using tmux wait-for the longest import migration
run a separate *.sql script for (almost each) table for post-processing (add FKs, add triggers), again in parallel inside a tmux
run vacuumdb --jobs=24 --analyze

To fit this purpose, the machine in question had been pimped:

14 cores (instead of 8)
64GB RAM (instead of 32GB; but they will eventually stay)

Also, for each individual pgloader invocation I had a seperate loader config with individual batch size configuration. The default batch size of 25000 wouldn't work for the bigger tables.

In the end, for my purpose and workload, this cut down the time

from 4h38m (1h11m importing, 1h41m adding FKs, 1h22 vacuuming)
to 2h8m6s (74m30s importing, 25m50s adding FKs, 27m44s vacuuming)

(Actually, the latest numbers are slightly below 2h even, but I don't have more detailed stats for them)


View permissions on specific Oracle object

You can check object privileges on tables through the view dba_tab_privs.

Here's a query:

SELECT grantee, privilege FROM dba_tab_privs WHERE table_name='XXXXX';

You can also learn more about privileges and stuff from this really cool website:

http://www.dba-oracle.com/forensics/t_forensics_object_privileges.htm


This is not the installation of SQL Server itself. It is much more likely one of the following:

a log file for a database (or databases) with a lot of activity, in full recovery mode, but you have never taken a backup or a log backup.
a data file that you have populated with a lot of data (perhaps inadvertently, like tempdb) or attached a large database from somewhere. This could also be filestream data; let us know if you have used that feature.
a large backup or a long series of backups, if in fact you are taking backups.

First, identify which file(s) are actually large - they're almost certainly .mdf / .ldf, but also check for .bak and .trn. Start in:

  C:\Program Files\Microsoft SQL Server\<instance>\MSSQL\DATA\
  C:\Program Files\Microsoft SQL Server\<instance>\MSSQL\Backup\

(But you may have to also check other locations you might have told SQL Server to use for data/log files, backups, filestream, etc.)

Then:

If the large files are .mdf / .ndf files, and they are user databases, you need to decide whether you want to keep those databases around, or keep the large tables within them. For databases you know you don't need, you can just drop them; for databases you might need, you can take a full backup and set them offline temporarily until you are certain you don't need them, then drop them. You can also just drop individually large tables inside an existing database, then shrink the data file to reclaim space (please read this entire post and also this one for more details); you can see which tables are largest using DMVs like sys.dm_db_partition_stats.

If they are system databases (like tempdb), then your next course of action depends on which system databases they are, why they are large, and whether you have alternative places to move the ones that are simple to move.
If the large files are .ldf files, and they are for databases you want to keep, you can clean up and reclaim space using a series of operations depending on your recovery model. Please read this post and all of its answers, in full, for a lot more detail.
if the large files are .bak / .trn, then you need to decide how far back you want to keep your backups, and which (if any) are ones you need to keep. Shift+Delete the ones you don't want (to bypass the Recycle Bin; at least I think that's how I remember to do it on Windows).

Or:

Move SQL Server (or the large databases) to a bigger drive (add a drive to your system, use an external drive with more capacity, etc). This may be easiest to accomplish by just installing a new instance and putting as many of the things away from C:\ as you can.


Which costing factors go into the optimizer choosing different types of spools?

This is a little broad but I think I understand the True Question and will answer accordingly. Just going to talk about table vs index spool though. I don't think it's quite correct to view there as being a choice between table and index spools. As you know, it's possible in a single subtree to get an index spool, a table spool, or both an index spool and a table spool. I believe it's generally correct to say that you get an index spool under the following conditions:

The query optimizer has a reason to transform a join into an apply
The query optimizer actually performs the transform to the apply
The query optimizer uses the rule to add an index spool (at minimum the index spool must be safe to use)
The plan with the index spool is selected

You can see most of these with simple demos. Start by creating a pair of heaps:

DROP TABLE IF EXISTS dbo.X_10000_VARCHAR_901;
CREATE TABLE dbo.X_10000_VARCHAR_901 (ID VARCHAR(901) NOT NULL);

INSERT INTO dbo.X_10000_VARCHAR_901 WITH (TABLOCK)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

DROP TABLE IF EXISTS dbo.X_10000_VARCHAR_800;
CREATE TABLE dbo.X_10000_VARCHAR_800 (ID VARCHAR(800) NOT NULL);

INSERT INTO dbo.X_10000_VARCHAR_800 WITH (TABLOCK)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

For the first query, there's nothing to seek on:

SELECT *
FROM dbo.X_10000_VARCHAR_901 a
CROSS JOIN dbo.X_10000_VARCHAR_901 b
OPTION (MAXDOP 1);

So there's no reason for the optimizer to transform the join into an apply. You end up with a table spool due to costing reasons. So this query fails the first test.

For the next query, it's fair to expect that the optimizer has a reason to consider an apply:

SELECT *
FROM dbo.X_10000_VARCHAR_901 a
INNER JOIN dbo.X_10000_VARCHAR_901 b ON a.ID = b.ID
OPTION (LOOP JOIN, MAXDOP 1);

But it's not meant to be:

This query fails the second test. A complete explanation is here. Quoting the most relevant part:

  The optimizer does not consider building an index on the fly to enable
  an apply; rather the sequence of events is usually the reverse:
  transform to apply because a good index exists.

I can rewrite the query to encourage the optimizer consider an apply:

SELECT *
FROM dbo.X_10000_VARCHAR_901 a
INNER JOIN dbo.X_10000_VARCHAR_901 b ON a.ID >= b.ID AND a.ID <= b.ID
OPTION (MAXDOP 1);

But there's still no index spool:

This query fails the third test. In SQL Server 2014 there was an index key length limit of 900 bytes. This was extended in SQL Server 2016 but only for nonclustered indexes. The index for a spool is a clustered index so the limit remains at 900 bytes. In any case, the index spool rule can't be applied because it could lead to an error during query execution.

Reducing the data type length to 800 finally provides a plan with an index spool:

The index spool plan, not surprisingly, is costed significantly cheaper than a plan with no spool: 89.7603 units vs 598.832 units. You can see the difference with the undocumented QUERYRULEOFF BuildSpool query hint:

This isn't a complete answer, but hopefully it's some of what you were looking for.


mysqlbackup failed with error when --use-tts option is used

This is some error cause by bad usage of the tool. Make sure you are running a mysqlbackup version that is compatible with the server version of MySQL your databases are running. Note that upgrading only either one will not work, so for instance MySQL version 8 and older only works with MySQL Enterprise Backup 8 or older.
Check from the documentation.


What Triggers This Warning: Type Conversion in Expression May Affect "CardinalityEstimate" in Query Plan Choice

The reason there is no "type conversion / cardinality estimate" warning in this query:

SELECT LEFT(30500600, 2);

...is because there is no cardinality estimate.  It's a constant expression being passed into a deterministic function - guaranteed to only return one "row."  I think the explanation is as simple as that, since the warning is more about the potential cardinality problems (rather than just the simple existence of the type conversion).

If I get the estimated execution plan for that statement, it's just a single "select without query" element:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5081.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementId="1" StatementText="select LEFT(30500600, 2)" StatementType="SELECT WITHOUT QUERY" RetrievedFromCache="false" />
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

For what it's worth, even the table variable version doesn't display the warning in SQL Server 2017 (because it's really a pointless warning when the conversion can't possibly affect a cardinality estimate - as in your example).

To be clear, that warning isn't always pointless.  Take this example:

SELECT message_id
INTO #SomeNumbers
from sys.messages;

SELECT *
FROM #SomeNumbers
WHERE LEFT(message_id, 2) = '50';

This puts 280,192 rows into a temp table.  The SELECT query has this warning, and the warning is legit.  SQL Server estimates there are 28,019.2 matching rows (10% of the table), when in fact there are only 2,156 matching rows (<1% of the table).

Regarding your example where you cast this input into the LEFT function, notice that the warning has changed slightly from CONVERT_IMPLICIT to just CONVERT:

  Type conversion in expression (CONVERT(varchar(12),[test].[col1],0)) may affect "CardinalityEstimate" in query plan choice

So you're just replacing an implicit conversion with an explicit one, which still warrants a warning.


Partition Function Question - DatetimeOffset

Be aware of the implications in regions that have the nasty habit of changing their clocks twice a year and Check this Link


Damn, my sleep() idea was oversimplistic!
SELECT * FROM users WHERE secure_compare(token, 'abcdef')
or...
SELECT * FROM users WHERE token = 'abcdef'

You'll want to index that to make it fast, which means timing information can be leaked not just from the string comparison, but also from the btree index walk. Can we actually do that?
CREATE UNLOGGED TABLE foo (t TEXT PRIMARY KEY);
INSERT INTO foo SELECT substr(sha256(n::TEXT::BYTEA)::TEXT,3) FROM generate_series(100000,199999) n;

...and python
def gettime(sql,N=300):
    t = time.time()
    for _ in range(N):
        cur.execute(sql)
    return (time.time()-t)/N

for sql in  ("select * from foo where t='3000000000000000000000000000000000000000000000000000000000000000';",
            "select * from foo where t='z000000000000000000000000000000000000000000000000000000000000000';",
            "select * from foo where t='3bb78535cc9555ff19fe3556aaa41c78a0a45c64d49ba2bc564507648a000000';"):
    print( sql )
    gettime(sql)
    r = [gettime(sql) for n in range(1000)]
    plt.hist(r,bins=30,label=sql)
plt.legend()
plt.show()

Oh yes, definitely a timing difference, about a microsecond between the case with no rows (hexadecimal hashes never start with a 'z') and the one that starts with a '3' which has plenty of matches in the table. I forgot to label the x axis as "time". Since this is most likely due to the path it takes walking through the btree index depending on the value of the hash, using a secure comparison function won't do a thing.

Let's try with a hash index instead of the btree:

Much more similar timings, in fact I had to set the histograms to be transparent since they overlap each other so much. Now, averaged over a much larger number of queries, there would probably be a detectable difference anyway. You could try it out...
What I'm saying is you're focusing on the string comparison you can see, in your query, but what about the rest of postgres code? As seen above, the btree index timing definitely leaks information. Hash doesn't seem to, the index only stores references to tuples that contain values having the same hash, and then pg goes to check the tuples. So perhaps with a bit of hacking, even the hash index could leak timing showing there are rows with the same hash in the bucket, depending how long it spends checking for tuples. And the postgres hash function isn't designed to be secure, so who knows. Besides, it will still use the equality operator, which is a string comparison. And if you want to index your own operator, you'll have to write a lot of C code.
What you could do to avoid too much hassle would be to index a BIGINT instead, which would contain some 64-bit hash of your API key. BIGINT comparisons should be constant time. And then index that with a hash index to make it fast, so you only need to run the secure_compare on the row matched by the index. That lets you use a super dumb version of secure_compare, like a bit of plpgsql that compares all characters in the string, and does not exit on the first character that does not match. Hell you could even use
SELECT sum(substr(str1,n,1) != substr(str2,n,1))
FROM generate_series(1,your_string_length) n;

But that doesn't guarantee that no part of postgres code will leak info.
I'd still hack it with a sleep(). But, instead of being random, I'd take the timestamp T_start of the beginning of the HTTP request, do all the work, and then sleep until say, T_start+100ms. That way, all requests will take about 100ms no matter what actually happens, even in code you didn't look at.


why doesn't this parameterized sql return anything while the non parameterized one does?

Try changing

sql.Parameters.AddWithValue("tb", "'" + table[2].ToString() + "'");

to

sql.Parameters.AddWithValue("tb", table[2].ToString() );

hope this helps.


Postgresql: large table or union of small tables

Not really clear from the question what you are trying to accomplish, but given that you tagged the question as postgresql-performance here are some general thoughts relevant to your question for you to consider.

Postgresql is a row database. Data is stored as rows. See this link on difference between row and column DBs.
In a row database you want to have data that needs to be retrieved and used at the same time, or most of the time. Unless your different versions of the JSON need to be read at the same time then you don't want it in the same row.
For keeping track of older / different versions of data you can either use different rows or a separate table. If older versions are not used often, or only for future reference and no current use, you may want to consider having a separate table so that data is not read/cached in memory. This is more relevant if you have a large table. If you have a small table, les than GBs worth of data, then you could use different rows and likely won't really matter much.
Check the rules for normalizing tables. For example here. Normalization is NOT always the answer to all designs, specially when you are doing analytics/OLAP, but it is good to at least be familiar with the concepts as many common design mistakes can be avoided by knowing about normalization.


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


Geo Route - Best way/db to store route path

Much depends on what you mean by "storing a route path"
(a) Do you mean storing the geographical shape of the road segments traversed by the truck ? That assumes you do a form of map-matching, i.e. correlate the GPS locations sent by the trucks with your road database.
(b) Do you mean constructing a track from the GPS points you receive from each truck ?
(c) Do you mean just storing the GPS coordinates in a table ?
You can use any database to store geographical information, whether that is individual points or complex shapes ... For points, a couple of X and Y numeric columns is all you need. For lines, you can use any of the available standard textual representations: OGC WKT, GML, GeoJson, even KML. They are typically stored in CLOB structures due to the potential size and complexity (essentially the number of vertices).
The real question is about the processing you do to construct those geographical shapes. Points (case c) are trivial. Constructing a line from points as a GPS track (case b) is more of an effort: building say a GeoJSON representation is easy: it just means adding a new point to the JSON string. What requires work is the calculations to throw out bad locations - too far off track to be anything but errors.
Doing the map matching (case a) is yet more work as it requires locating the proper road segment for each GPS location - considering the approximation of the coordinates and the history of preceding road segments. And of course you need a complete database of all road segments in the geographical area your application covers. That requires a fairly advanced database that has proper spatial types with indexing and spatial predicates. Few databases have this: Oracle has that as native capabilities. PostgreSQL has it via the PostGIS extension.
Then there is the question about what the application is all about: is it just to show a route on a map for each truck ? Do you need to replay a route ? View previous day routes ? Do route-deviation checks (match a route with the planned route and send alerts) ?  Predict arrival times ? Compare the pace of a truck with the planned route (is in early ? is it delayed). All those again need a database with advanced spatial processing capabilities. Unless you do all this processing in some external engine and only use the database for storing the results ...


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

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

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


How much of a view is persisted when you create an index?

All three columns are persisted to disk in the clustered index on the indexed view (no different, really, from a clustered index on a regular table). You can validate this using DBCC PAGE. I created the following structure in tempdb:

USE tempdb;
GO

CREATE TABLE dbo.a(a INT);
CREATE TABLE dbo.b(a INT, b DATETIME);
CREATE TABLE dbo.c(a INT, c CHAR(32));

INSERT dbo.a SELECT 1;
INSERT dbo.b SELECT 1, SYSDATETIME();
INSERT dbo.c SELECT 1, REPLICATE('c', 32);
GO

CREATE VIEW dbo.v
WITH SCHEMABINDING
AS
  SELECT a.a, b.b, c.c
  FROM dbo.a
  INNER JOIN dbo.b
  ON a.a = b.a
  INNER JOIN dbo.c
  ON a.a = c.a;
GO

CREATE UNIQUE CLUSTERED INDEX x ON dbo.v(a);
GO

Turned a trace flag on so I can use IND/PAGE:

DBCC TRACEON(3604, -1);
DBCC IND('tempdb', 'dbo.v', 1);

Results:

So I knew to check page 312:

DBCC PAGE(2, 1, 312, 3);

And here are the three values from the join, even though they're not all part of the unique aspect of the clustered index:


Log Custom SQL Results During Maintenance Plan

You can use a mixture of xp_cmdshell and bcp:

exec xp_cmdshell
    'bcp "SELECT * FROM YourTable ...." queryout Log.txt -c -T'

The above extended stored procedure bcp call assumes a few things, such as a trusted connection.  But utilize the BOL Reference for BCP to fine tune your call.


Authentification failure for PostgreSQL server (9.3)

I found a temporary solution to my problem. I edited the pg_hba.conf of the 9.3 server to say trust in the first two active lines. And after restarting (sudo service postgresql restart) I can connect to the server again using pgadmin. The downside is that the server might now not be password protected (I will open a separate question if I encounter any problems with that).

    # Database administrative login by Unix domain socket
    local   all             postgres                                trust

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5


Getting current date as part of an Stored Procedure

1) I'm not going to argue database design, since it seems that it's already in place.

2) Looking at the code, I have to presume that StartOnCallDate, StartOnCallTime, and Duration are Integers.  If you're trying to do that with DATETIME, then there are other issues to deal with.

To answer your question...

First off, the math in your where clause is still including the time (hours and seconds).  So this stuff:

DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
            DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE()))

Gives this result:

SELECT DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
        DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE()))
-----------------------
2011-08-12 15:35:00.000

So, when you compare DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899') to that mess above, it will almost never be equal.  That's because when you use DATEADD(DAY...) to that hard-coded date, you will only end up with values that are days:

SELECT DATEADD(DAY, 40800, '12/31/1899')
-----------------------
2011-09-15 00:00:00.000

To drop the hours and seconds, you could do a bunch more DATEADD stuff, or you could just do something like this:

(SQL Server 2008):
CONVERT (date, GETDATE())

(SQL Server 2005/2008 options):
CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME)
CAST(FLOOR(CAST(GETDATE() AS DECIMAL(12, 5))) AS DATETIME)

Those will get you JUST the date.  If you throw one of those into your where clause, it will allow you to filter the data, showing you all values that match the current DAY (anything that is going to occur today):

WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')=
    CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME) AND
    mOnCallAdd.SchedName = 'arc im'

To give you an idea of the values you are comparing, pull each of those out (with sample data) into a select statement:

SELECT
    DATEADD(DAY, 40766, '12/31/1899'),
    CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME)
----------------------- -----------------------
2011-08-12 00:00:00.000 2011-08-12 00:00:00.000

Now, to filter out anything that might have already passed, you want to add a condition to your WHERE clause that checks the ending time to determine if that is earlier that the current time.

 DATEADD(MINUTE, mOnCallAdd.duration,
         DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
         DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate()

This makes the entire where clause:

WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')=
        CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME) AND
     DATEADD(MINUTE, mOnCallAdd.duration,
             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate() AND
     mOnCallAdd.SchedName = 'arc im'

EDIT:

If you only want items to show up that are currently happening, you need to qualify on both your end time (as above) and your start time.  To qualify your start time, you need to get your start time and make sure that the current time is greater than or equal to the current time:

DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
        DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) <= GetDate()

This makes the entire WHERE clause:

WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')=
        CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME) AND
     DATEADD(MINUTE, mOnCallAdd.duration,
             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate() AND
     DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) <= GetDate() AND
     mOnCallAdd.SchedName = 'arc im'


PostgreSQL JSONB: How to get an element from Array of Embedded Documents

SELECT ($${
   "_id": 100,
   "type": "food",
   "item": "xyz",
   "qty": 25,
   "price": 2.5,
   "ratings": [ 5, 8, 9 ],
   "memos": [ { "memo": "on time", "by": "shipping" }, { "memo": "approved", "by": "billing" } ]
}$$::jsonb)@>'{"memos":[{"by":"shipping"}]}';
 ?column?
----------
 t

Note that that only works with jsonb.

You'll probably find the documentation on json indexing useful too.


You can manually take transaction log backup on any replicas of AG. For primary and secondary replica you can take normal T-log backup, only copy_only full backups are possible on secondary replica. I would suggest to take T-log backup from secondary replica this will put less load on primary replica and would also not affect peak time I/O.

In any case log backup chain would NOT be disturbed just make sure that when you take T-Log backup you do not delete it but store it like you do for your normal backups so that you can use it in case of disaster.

  In AlwaysON AGs do we take T-Log backups for Synchronization manually or do they happen over the wire (End points 5022 ) . Can some one please simplify this process for my understanding ?

T-Log backup has no relation with synchronization, yes communication happens via endpoints and commit handshake happens via endpoints. For more details read Always On Series


ORDERY BY random() is not a special case.  It generates random numbers, one for each row, and then sorts by them.  So it results in rows being presented in a random order.

Rather it is ORDER BY <literal constant> which is the special case, but that special case only works with integers.  It throws the error you show for non-integers.  The special case is that it uses the integer to index into the select-list, and orders by that column from the select list.  This lets you both select and order by an expression, without having to repeat the expression in both places.


If all your dependencies are those you have shown: {A->GH, B->IJ, C->A, F->B, FC->DK, K->E} then you have come to the wrong conclusion.

Your only candidate key is CF. The C and F are not candidate keys on their own.

Therefore, the F->B dependency (and the C->A as well) means that the relation violates 2NF.

For the other question, if you had for example these dependencies:

BC -> AF
AF -> BC
 F -> DE

where the candidate keys are BC and AF, then again the F -> DE would mean that the relation violates 2NF. To be in 2NF would mean that there is no dependency on any part of any candidate key.


Always On Availability Groups with Encrypted Connections

First for an AG you need to Force Protocol Encryption on each instance participating in the AG.  With an FCI there is only one instance.

Also with an AG clients can connect to the AG Listener, and they can connect to the instances directly.  So you have to configure the certificates accordingly.  See https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover#SSLcertificates

Note, that this configuration is not required to have protocol encryption.  It's required to authenticate the SQL Server to the clients.  If you merely want protocol encryption, clients may always request it, or you can force it on the server with a self-signed certificate.


In the FROM clause below, the subquery acts as a table:

SELECT * FROM (SELECT id, name FROM users)

That subquery is called an inline view. So in this case you are not directly selecting rows from a table, but from an "inline view".
 From your first link it seems that the user created an actual view, which you can do from a query. More information over here: https://www.postgresql.org/docs/9.2/static/sql-createview.html

But it seems that Erwin thinks it might be a better idea, performance wise in that specific case, to "inline the query" from the view. Or in other words, not by first creating a view but use inline views like I did.

Hope this helps.


But now it needs upgrade to 2012

I would suggest you to go with SQL Server 2014, unless you have a compelling reason to stick with SQL Server 2012.

AlwaysON is getting a lot of fixes back ported from SQL Server 2016.

  we like to do AlwaysON  but its mirroring behind AlwaysON so will it work or is there any other way ?

This is not true. The mechanism is different.

Mirroring will flush the log block to disk and once completed locally, it sends the block to secondary.

AlwaysON flushes the log block(s) in parallel.  A secondary could have hardened log block(s) before the primary I/O completes.    This design increases performance and narrows the NOT IN SYNC window(s).

  total data is about 2 TB now that cluster is log ship to 4000 Miles another data center.

The data size matter to a certain extent - when doing an initial sync. What matters is the number of transactions occuring - meaning how busy your database is and the network bandwidth between the 2 data centers. The way you do index maintenance needs to be revisited as well.

There are other stuff like - backups (Full and log backups), application connection string chages to use multisubnet failvoer and hostTTL settings, reading from secondary, etc play crucial role when embracing a new technology.

I would suggest you to start reading : Stairway to AlwaysOn as a good start and then AlwaysOn Professional for some advance stuff.

Read : How It Works: Always On–When Is My Secondary Failover Ready?


sp_BlitzCache throws Table DML warning on temp tables

Thanks for using sp_BlitzCache!

No, don't worry about that one. It's a more general check that sometimes snags temp tables.

As an aside, make sure you're running the most recent version, if you're not already.

You should also use the GitHub repository I linked to to file issues.

Thanks!


SP can be like (draft code):

CREATE PROCEDURE DeleteFromGeneralLogs (IN DeleteTill DATETIME)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done:=1;
-- IF DeleteTill IS NULL THEN LEAVE;
WHILE done = 0 DO
    SELECT SLEEP(1) INTO done; -- small pause between chunks
    DELETE
        FROM customer.generallogs
        WHERE DateEnter < DeleteTill
        LIMIT 100000;
END WHILE;
END;

And then

CALL DeleteFromGeneralLogs('2019-04-03 16:13:28');


How do I export a multi table database with exceptions?

Use SELECT INTO OUTFILE ... WHERE LENGTH(Status) > 0 for each table.

PHPMyAdmin isn't a tool for complex tasks.


Shipping big WAL archives to hot standby

First, in case there's an implicit misconception here: Note that the restore is not atomic. Your queries on the replica will see the intermediate states between the prior and new master state. It'll happen faster than it did on the master - assuming the replica replays reasonably fast - but clients will still see it and there's no way around that.

If you're trying to give clients an apparently atomic switch between old and new states you can't do it just by copying a bunch of WAL over.

  What would be the effect of suddenly introducing a 1-2 GB of WAL archives to the WAL restore folder on the slave?

Minimal. The only significant effect will be the disk I/O of writing it as you copy it.

  Would there be a big performance effect on the incoming queries to the slave?

Mild to moderate, probably mild. It'll have more impact than streaming them continuously would since the replica will be running restore flat-out so it'll churn the disk, kernel buffer cache and shared_buffers more.

Hard to say in detail, depends on workload, RAM, disk subsystem, etc.

  Would the slave be available for queries while the WAL logs are restored into the DB?

Yes.

Another alternative you may not have considered. It still needs 2x the disk, but:

Run pgbouncer in front of the replica.
pg_basebackup the replica locally to a temporary datadir. Yes, you can take a pg_basebackup from a replica.
Copy the WAL to the copied replica.
Start it, wait for it to catch up.
When it's caught up, change pgbouncer's configuration to point to the  new DB and sighup it to get it to reload its configuration.
Shut down the old replica

... and repeat, switching between the two datadirs.


Fast query in MySQL with table of 1B+ rows

I would suggest that the round(timestep/25) is what is causing the slowdown in the group by part of the view.

Could you add a further field (timestep_aggregate) to the table containing the results of the round function? Using a trigger to populate the field on insert / update.

Or if using MySQL 5.7.5 or above, you could use a generated column which would do this for you - see Generated Columns in MySQL 5.7.5

If so, you could then create a combined index on source_id and timestep_aggregate, then use the new field instead of the function within your view. This would be much more performant.


PostgreSQL: Extending database storage when disk is full

PostgreSQL doesn't support spanning a single table over multiple tablespaces directly.

You can use table partitioning to create a "master" table, with child tables on different tablespaces. It's a bit tricky and it means you can't use foreign keys or enforce a proper unique constraint on the primary key, so it's not generally the preferred option.

A more practical option is usually to use a logical volume manager, RAID system, or similar to span multiple physical disks into a single volume, with a single file-system on it. Then PostgreSQL just uses it like anything else and doesn't need to care. On Linux, this might mean LVM, the md software RAID driver in raid0 mode, btrfs, or all sorts of other options. Most (all) of these require you to build a new filesystem then move the PostgreSQL database onto it, you can't convert a regular filesystem that's directly on top of a partition into a raid volume / logical volume / etc.

Note that neither of these options are safe if the additonal storage is removable. If you remove a disk containing a tablespace, or remove part of a logical volume or spanned raid, your entire database will become inacessible. If you lose the disk/volume or its data, the entire DB will be unrecoverable.


Is there a way to only allow SQL Server 2014 syntax/functions to be used in SQL Server 2019?

I'm wondering if there's a solution to just allow 2014 syntaxes in 2019.

No. If you need your users to steer clear of things that won't work in versions newer than 2014, then they should develop on 2014.

At the very least, you could let them continue developing on 2019, but automate scripting their objects to a 2014 instance. You can't do this with backup/restore or detach/attach, but there are plenty of ways to transfer objects/data to an older version. You'd also have to run all of the code, because some problems won't crop up until runtime.

Compatibility level is not the answer here, except for very rare exceptions (like STRING_SPLIT). Compatibility level is not used for allowing / disallowing syntax, in modern versions at least. See this and this. In older versions, yes, a lot of syntax was controlled this way, but that’s not how it works anymore.


Does SQL Server Cache Aggregate Results When Duplicated Across Columns?

SQL Server only calculates the COUNT once. You can see this by looking at the properties of the execution plan for

create table Orders(order_id int, total int, discount int)

SELECT
    COUNT(order_id) AS num_orders
    , SUM(total) / COUNT(order_id) as avg_total
    , SUM(discount) / COUNT(order_id) AS avg_discount
FROM Orders

The stream aggregate (1) has the following defined values

[Expr1008] = Scalar Operator(COUNT([tempdb].[dbo].[Orders].[order_id])),
[Expr1009] = Scalar Operator(COUNT_BIG([tempdb].[dbo].[Orders].[total])),
[Expr1010] = Scalar Operator(SUM([tempdb].[dbo].[Orders].[total])),
[Expr1011] = Scalar Operator(COUNT_BIG([tempdb].[dbo].[Orders].[discount])),
[Expr1012] = Scalar Operator(SUM([tempdb].[dbo].[Orders].[discount]))

Expr1008 is the calculation of the COUNT that you ask about.

There are some other COUNT aggregates for the other two columns. These are needed because the correct result for SUM(total) (for example) if COUNT(total) is 0 should be NULL.

This is carried out by the next compute scalar along (2). This also converts the COUNT result (Expr1008) from bigint to int and labels that as Expr1003

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(int,[Expr1008],0)),
[Expr1004] = Scalar Operator(CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END),
[Expr1005] = Scalar Operator(CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012] END)

Finally the left most compute scalar (3) uses Expr1003 in the division operation...

[Expr1006] = Scalar Operator([Expr1004]/[Expr1003]),
[Expr1007] = Scalar Operator([Expr1005]/[Expr1003])

... and outputs columns Expr1003, Expr1006, Expr1007 as the final result

PS: AVG has been supported much longer than SQL Server 2008. I imagine it has likely been available in the beginning. However it does not have the same semantics as your rewrite in the presence of NULLs anyway.

I assume order_id is the primary key and therefore not nullable but for a table with 10 orders and two NOT NULL total values of 2 and 4 then AVG(total) would be 3 but SUM(total) / COUNT(order_id) would be 0.6 (or 0 once integer division is taken into account).


You wrote

  12c db has the exact data of 11g (expdp/impdp)

But are you sure?

1) Did you make a consistent export of your tables? This means
all table data is from the same point in time. For this it is sufficient that you
stop all other write activity on the database during export. Or you set the flashback_scn parameter
when using expdp or the consistent parameter when using exp.

2) Are your sequence value consistent with your table data? This means
all table data and the state of the sequence is from the same point in time.
  For this it is sufficient that you
stop all other read and write activity on the database during export.
This is the only way i know to guarantee this consistency.
I think that enen the flashback_scn or consistent parameters can't help here.
They are useful for for transactional data only. I think the structures like sequences are extracted before the table data is read, here is the log from an export
https://community.oracle.com/thread/3519031

> expdp system/system123 directory=DUMP_DIR dumpfile=hsfa.dump schemas=HSFA content=metadata_only

Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 20 February, 2014 7:55:03

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_16":  system/******** directory=DUMP_DIR dumpfile=hsfa.dump schemas=HSFA content=metadata_only
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
(...)

Solution

If you didn't make a data consistent export (case 1) you should repeat the export with the appropriate [consistency parameter].
If you have a problem with the sequences (case 2) you can try the following. Recreate the sequences of
the target database (where you have imported the schema) like  the current sequences of the source database.
Now all sequences produce values that weren't yet used by the application.
 Most application have no problems (should not have problems) if there are gaps in the series of sequence numbers.


Optimize very simple Postgresql query (and understand Query Plan)

You need to understand that the costs provided are not absolute values. It basically has no other meaning than helping the planner comparing plans. So you can't compare cost (that has no unit) to actual time (that's given in ms). You can compare costs between two plans (provided by the same postgres server, because it will depend on configuration parameters and sometimes Postgres version).

Then (and I think you figured it out by yourself), the performance difference between first time and second time is that the second time data are cached, so getting them is way faster...

Your query runs in 45ms when data are not cached... That's pretty good and it wouldn't need farther optimization normally. Remember the first rule for optimization is

  Don't do it

But I think you simplified your problem here so we could understand it. If your query is way more slower that what you showed and does use OR, remember it's quite expensive (in time and resource). You can always rewrite your query like that :

EXPLAIN ANALYZE SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id IN(461,7618,45581,5238);

or like that :

EXPLAIN ANALYZE
(SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id=461
UNION ALL
SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id=7618
UNION ALL
SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id=45581
UNION ALL
SELECT col1, col12, col22, col17, col41, col27 FROM table1 table1.id=5238);

Or (like a_horse_with_no_name suggested) with a join on a values clause :

EXPLAIN ANALYZE SELECT col1, col12, col22, col17, col41, col27 FROM table1 INNER JOIN (VALUES (461),(7618),(45581),(5238)) AS t(id) ON table1.id = t.id;

You might find usefull the using explain official documentation page.


How to compare data between staging and operational tables?

There are a couple of options if you want to use T-SQL and SSIS.

You could compare the key columns on the staging table vs ops table to determine if the row already exists and this way you would know if you need to do an INSERT or UPDATE.

If using SSIS you can use the lookup component. Your source componenet will have something like

SELECT keycol FROM OpsTable.

The lookup component will have a source something like

SELECT keycol FROM StagingTable.

You can use the 'No Match Output' option to get all the rows in OpsTable not in StagingTable.

If using T-SQL you can use the EXCEPT operator like so

SELECT keycol FROM OpsTable
EXCEPT
SELECT keycol FROM StagingTable

If you need to compare all the columns (key columns and non key columns) you can use the HASHBYTES and hash all the non key columns for comparison. Please note that HASHBYTES only accepts (VARCHAR, NVARCHAR, or VARBINARY) so you will have to convert any columns that are DATE or such into VARCHAR.

SELECT keycol,
       HASHBYTES('SHA1',col1 + col2 + ISNULL(convert(nvarchar(100),ColDate,109),'')
  FROM OpsTable

Hope this helps.

You can get more information on HASHBYTES here and on the EXCEPT operator here.


mysqltuner.pl shows [!!] 2 different collations for database

No, it is not a problem. The documentation sums things up.

  Every table has a table character set and a table collation. The
  CREATE TABLE and ALTER TABLE statements have optional clauses for
  specifying the table character set and collation

The default database character set/collation values are used for a table if the relevant clauses are not specified at table creation time.


A fairly easy option I like is just using PowerShell.

Get the out-datatable.ps1 module from CodePlex here and loop through your .csv files.

Then generate a SQL statement to create your table and use SQL bulk insert to load your data into your table.

This is a script that could do what  you want, it processes .txt files as tab-delimited and csv files as comma-delimited.

You may need to adapt it a bit for your needs, it's a script I used somewhere and tried to clean up before posting:

$ErrorActionPreference = "Stop"
########################################
#                                      #
#  Define paths and connectionstring   #
#                                      #
########################################

$ConnectionString = "Data Source=localhost; Database=dbname; Trusted_Connection=True;";
$ScriptDirectory = "C:\\scriptdirectory"
$FilePath = "C:\\import\\csv\\"

######################
#                    #
#  Import CSV Files  #
#                    #
######################

# Local variables
$CreateStatement = ""

# Go to working directory
Set-Location $ScriptDirectory
# Source script from https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd
. ".\Out-DataTable.ps1"

# Process files recursively
$Files = Get-ChildItem -Include @("*.txt","*.csv") -rec $FilePath | Where-object {!$_.psIsContainer -eq $true}
foreach ($File in $Files)
{
    $TableName = $File.BaseName
    # Read in CSV
    if ($File.Extension -eq ".txt")
    {
        $csvDataTable = Import-CSV -Delimiter "`t"  -Path $TempFileName | Out-DataTable
        Remove-Item $TempFileName
    }
    if ($File.Extension -eq ".csv")
    {
        $csvDataTable = Import-CSV -Delimiter ";"  -Path $File.FullName | Out-DataTable
    }
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnectionString
    $bulkCopy.DestinationTableName = "[" + $TableName + "]"
    # If datatable exists, create table & import datatable
    if ($csvDataTable -ne $null)
    {
        # Create Table
        $CreateStatement = "CREATE TABLE [" + $TableName + "] ("
        foreach($Column in $csvDataTable.Columns)
        {
            $CreateStatement += "[" + $Column.ColumnName + "]  NVARCHAR(MAX),"
        }
        # remove last column & close parentheses
        $CreateStatement = $CreateStatement.Substring(0,$CreateStatement.Length-1) + ")"
        $Connection = new-object system.data.SqlClient.SQLConnection($ConnectionString);
        $cmd = new-object system.data.sqlclient.sqlcommand($CreateStatement, $Connection);
        $Connection.Open();
        if ($cmd.ExecuteNonQuery() -ne -1)
        {
            Throw "Create Table failed"
        }
        $Connection.Close();
        $bulkCopy.WriteToServer($csvDataTable)
    }
}

It does have an issue with creating every column as NVARCHAR, that may not be suitable for your needs, but the datatable doesn't detect the data type unfortunately, if you figure that out let me know how you did it.


Will hacking away ibdata1 destroy databases with only MyISAM tables?

Since you are using MySQL 5.1.73, you can blow away the ibdata1 file without worries.

First, let's look inside ibdata1 (picture created by Percona CTO Vadim Tkachenko)

STEP 01 : Make sure there is no InnoDB

Run the following

SELECT
    table_schema,table_name,
    (data_length+index_length)/POWER(1024,3) table_sizegb
FROM information_schema.tables
WHERE
    engine='InnoDB' AND
    table_schema NOT IN ('information_schema','performance_schema','mysql');

If you get nothing, you can proceed.

If you do get something, either drop those tables, convert them to MyISAM, or mysqldump them to reload later.

Assuming datadir is /var/lib/mysql

STEP 02 : Configure innodb_file_per_table

You should add this to `my.cnf

[mysqld]
innodb_file_per_table=ON

This will allow future for CREATE TABLEs where you want .ibd files

STEP 03 : Flush everything from InnoDB

SET GLOBAL innodb_fast_shutdown = 0;

STEP 04 : Shutdown mysql

service mysql stop

STEP 05 : Delete ibdata and the redo logs

cd /var/lib/mysql
rm -rf ibdata1
rm -rf ib_logfile0
rm -rf ib_logfile1

STEP 06 : Start mysql

service mysql start

This will regenerate ibdata1, ib_logfile0, and ib_logfile1

NOTE: MyISAM tables are completely unaffected. Why ?

Suppose you have a table mytable in the mydb database.

The files for that one table are the following:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.MYD
/var/lib/mysql/mydb/mytable.MYI

The InnoDB file ibdata1 is usually located in /var/lib/mysql. Therefore, all MyISAM tables are safely tucked away in their respect database folders.

CAVEAT for MyISAM only users

If you know for a certainty that you will never use InnoDB, you could add this parameter to my.cnf

[mysqld]
skip-innodb

That way, when you start up mysqld in STEP 06, the process will not regenerate ibdata1, ib_logfile0, and ib_logfile1.

CAVEAT FOR MySQL 5.6/5.7 users

In MySQL 5.6, there are 5 tables in the mysql schema (/var/lib/mysql/mysql) that are InnoDB. MySQL 5.7 has 19.

Just run this query to see what those tables are

SELECT table_name FROM information_schema.tables
WHERE engine='InnoDB' and table_schema='mysql';

See my old post InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6 and the MySQL Documentation.

In this instance, you would mysqldump the mysql schema, blow away the InnoDB files, and manually delete the .ibd file in /var/lib/mysql/mysql. Then, reload the mysql schema from that dump.


There's not a clear-cut answer to your question without more information. However, your choice should be basically based on:

Ease of data loading: if you leave the keys as they are, you'll save a lot of trouble creating the equivalent INTEGER ids (you would need an equivalence table between every pair; and use this translation table when you import the data using an ETL process). If all keys are 22 chars in length, you're probably better of using char(22) instead of varchar(22) 1. (Against AUTO_INCREMENT)
Size: if the size of your dataset is very large, making the translation will save you a significant amount of space in both table rows and indexes. If there are lots of indexes with several varchar(22) and extra columns, you can hit the limit on index size 2. The smaller the indexes (and tables) the more performant a system is to query. (Pro AUTO_INCREMENT)
New keys: if you want to add more rows to your dataset, it will be easier to have an AUTO_INCREMENT key than to have a mechanism to generate varchar(22) unique ids. (Pro AUTO_INCREMENT)

Depending on your particular needs, balance the pros and cons, and choose.

Given the nature of the Yelp Dataset, I'd probably go for the INT, just for size efficiency. You'll need to translate business_id, review_id, user_id and photo_id. Provided you'll have to translate from JSON to CSV already, and convert arrays to normalized subtables, before being able to upload the different collections, doing one extra step shouldn't be that difficult.

Notes:

  If your content is a fixed size, you'll get better performance with CHAR.

from: What's the difference between VARCHAR and CHAR?

  maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.

from 14.8.1.7 Limits on InnoDB Tables

Side note: Consider using PostgreSQL and MADLib. I think this combination might give you some useful tools for this kind of challenge.


You could use GROUP BY, if you only need the basic information from first_table:

SELECT
    T1.id,
    T1.username,
    T1.firstname,
    T1.surname,
    T1.address
FROM first_table AS T1
JOIN second_table AS T2 ON (T2.user_id=T1.id)
JOIN first_table AS T3 ON (T3.id=T2.id AND T3.id<>T2.user_id)
WHERE T3.username='johnie'
GROUP BY T1.id;

Try it in SQLFiddle.

You can also test it with T3.username='alice'.


The general concept to consider with these issues is "transaction isolation level".

I believe the most plausible explanation with MariaDB would be that both writers and readers work in non-autocommit mode (either autocommit=0 in my.cnf or set explicitly where application opens new connection). In this case, you can give the reader sessions access to newly updated data by issuing their own local commit; command instead of full re-connect.


How can I recover my tables in sql server database

Yes, as Iren and Rahul said, you can use ApexSQL tools. If the tables were dropped, ApexSQL Recover can recover them even from databases in the simple recovery model. ApexSQL Recover can recover both table structure and table records

On the other hand, ApexSQL Log cannot recover records lost when a table was dropped, it can only recover the table structure

In case the records that were lost using DELETE (not DROP TABLE), both ApexSQL Log and ApexSQL Recover can help

The advantages of ApexSQL tools over recovery to a point in time is that ApexSQL will recover just the tables you specify (creates CREATE TABLE and INSERT INTO scripts) , while a point-in-time recovery will roll back all the transactions that happened in the meantime

A DROP TABLE statement marks the MDF file pages used by the dropped table for deallocation. These pages are actually still in the MDF file until overwritten by new operations. To prevent new operations overwrite the data necessary for successful recovery, we recommend creating a copy of the original MDF and LDF files immediately

Disclaimer: I work for ApexSQL as a Support Engineer


Does SQL Table Column is assigned with column number

The row number you get in SSMS (I'm presuming you were refering to this number) is not generated by SQL but by SSMS (so it doesn't really exist in your database).
SQL does have a "number" associated with each columns (their IDs) and you could get those with a query like this one:
  select c.column_id from sys.columns c
  join sys.objects o on o.object_id =c.object_id
  where o.name = 'YourTableName'

I'm not sure it will do exactly what you expect it to do, so if you could specify why you want those columns number, we will be in a better position to give you a clearer answer.


This query returns rows 1-2 and 6-7 because ActivityId 3 is before 2015-03-07 08:06:53.625 and ActivityId 4 is after 2015-03-07 08:06:53.625 for the 2 of them.

Note: Since there is no unique ID column, I supposed that there is no duplicates in [StartTime] for a [UserName].

Query 1 (link):

DECLARE @date datetime = '2015-03-07 08:06:53.625';

WITH list AS (
    SELECT *
        , n = ROW_NUMBER() OVER(PARTITION BY [UserName] ORDER BY [StartTime])
    FROM data
    WHERE [Activityid] IN (3, 4)
)
SELECT *
FROM list l1
INNER JOIN list l2 ON l1.UserName = l2.UserName AND l1.n+1 = l2.n
WHERE l1.Activityid = 3 AND l2.Activityid = 4
    AND l1.StartTime < @date AND l2.StartTime > @date;

Output 1:

Rownr   | UserName  | StartTime                     | Activityid    | Value | n | Rownr | UserName  | StartTime                     | Activityid    | Value | n
1       | Anneleen  | 2015-03-07 07:01:38.7070000   | 3             | 5811  | 1 | 2     | Anneleen  | 2015-03-07 08:52:23.4170000   | 4             | 5811  | 2
6       | Dimitri   | 2015-03-07 07:02:53.6330000   | 3             | 5817  | 1 | 7     | Dimitri   | 2015-03-07 21:24:20.6330000   | 4             | 5817  | 2

Using the same CTE this query returns rows where ActivityId 3 or 4 has no matching ActivityId 4 or 3.

Query 2 (link):

WITH list AS (
    SELECT *
        , n = ROW_NUMBER() OVER(PARTITION BY [UserName] ORDER BY [StartTime])
    FROM data
    WHERE [Activityid] IN (3, 4)
)
SELECT l1.* , *
FROM list l1
LEFT JOIN list l2
    ON l1.UserName = l2.UserName AND l1.n+1 = l2.n AND l2.Activityid = 4
WHERE l1.Activityid = 3 AND l2.Activityid IS NULL
UNION ALL
SELECT l1.* , *
FROM list l1
LEFT JOIN list l2 ON l1.UserName = l2.UserName AND l1.n-1 = l2.n AND l2.Activityid = 3
WHERE l1.Activityid = 4 AND l2.Activityid IS NULL;

Output 2:

Rownr   | UserName  | StartTime                     | Activityid    | Value | n
5       | Arnold    | 2015-03-07 11:03:20.4200000   | 3             | 5840  | 1
8       | Dimitri   | 2015-03-07 22:24:20.6330000   | 4             | 5817  | 3

Data used with an extra row for 4 without 3:

declare @data table
    ([Rownr] int, [UserName] varchar(8), [StartTime] datetime2, [Activityid] int, [Value] int)
;

INSERT INTO @data
    ([Rownr], [UserName], [StartTime], [Activityid], [Value])
VALUES
    (1, 'Anneleen', '2015-03-07 07:01:38.707', 3, 5811),
    (2, 'Anneleen', '2015-03-07 08:52:23.417', 4, 5811),
    (3, 'Anneleen', '2015-03-07 08:52:34.347', 3, 5811),
    (4, 'Anneleen', '2015-03-07 14:15:20.140', 4, 5811),
    (5, 'Arnold', '2015-03-07 11:03:20.420', 3, 5840),
    (6, 'Dimitri', '2015-03-07 07:02:53.633', 3, 5817),
    (7, 'Dimitri', '2015-03-07 21:24:20.633', 4, 5817),
    (8, 'Dimitri', '2015-03-07 22:24:20.633', 4, 5817);


Avoiding temporary tables while sorting by a column in a different table

You may need to bump up the following variables as well:

tmp_table_size
max_heap_table_size

If these are too small, the tmptable goes to disk quickly.

If these are too large, the tmp table goes to quickly when the limit is surpassed but creates intermittency due to moving the large in-memory tmptable to disk before completing the tmptable's usage.

So, you need to perform a tight balancing act with these tmp table variables. These variables also have the same per-thread constraint that @DTest mentioned in this answer.

BTW in theory a sort buffer is a tmptable in itself, though governed by a different session option (sort_buffer_size). Since you are sorting with a group-by column and not a pure table-established column, temptables are somewhat unavoidable.


Replication with Always On availability groups

The problem is that procedure only works if read access is enabled for the secondaries and, as per the documentation, Basic Availability Groups do not allow readable secondaries. You need to manually perform the checks that this procedure normally handles as per the doco:

  This is expected behavior. You must verify the presence of the subscriber server entries at these secondary replica hosts by querying for the sysserver entries directly at the host.

Basically, connect to each of your replicas and run this query:

SELECT * FROM sysservers

Make sure that your replication subscribers (SQL04) are listed in sysservers on each AG replica. If they are then you're fine.


Installing Full Text Search on SQL Server 2008 R2 Express

If you want to use Full text search, you need the 'Advanced Services' edition of SQL Server Express 2008, which can be obtained at Microsoft

However, you need to uninstall the current version first.


When you say Oracle 10g Lite I assume you mean Express Edition.  I recommend you read through the documentation, particularly the section on Connecting to the Database.  If you have a more specific question after reading that, we would be glad to help.


Avoiding deadlocks in PostgreSQL with multiple clients

From the comments to my question, the problem can be summarised, per @DanielVérité, as "concurrent DDL is bad; concurrent DDL and DML is really bad". I was not aware of that -- even with the care I took to make the DDL idempotent -- but it's a good thing to know. So, that leaves me with two broad solutions:

Don't do the DDL concurrently (e.g., have a single client instantiate the schema).

This is probably the "easiest" solution from an application development perspective -- and I see it as my backup plan -- but it has consequences on how the application is architectured. I only want to take this route if I have to.
Use advisory locks, on @a_horse_with_no_name's suggestion.

This requires a minor modification to my application's database library only, so no large architectural changes or changes to the schema. Its downside is that I have to acquire and release the lock on every transaction (to make sure it isn't already locked by the DDL section of the code), which necessarily serialises all transactions across clients.

I am experimenting with option 2 and it appears to be working. I need to give it a few more runs through to convince myself that the deadlocking has gone and what performance impact the serialisation has (anecdotally, so far, it's negligible). I'll report back with results when I have them...


Elevation data in a database (tens of billions of information)

PostgreSQL with the PostGIS extension would appear to have everything you need to manage this data.

PostGIS has a fairly rich set of functions that deal with rasters like the files that you have described.  Further documentation on enabling and using rasters is here.

Once you have the rasters loaded a query like the following should perform reasonably well

SELECT p.Identifier, ST_Value(r.rast, p.geog) AS elevation
FROM query_point p
    INNER JOIN elevation_raster r ON ST_Intersects(r.rast, p.geog);


Startup oracle database 12c manually with SQL*Plus command not when OS loads

You can configure this behaviour through the Administration Assistant, where you can choose to start (or not to start) the database instance with the Windows service.

Alternatively, you can do this with oradim, e.g:

oradim -edit -sid ORCL -startmode manual

The Windows service should be set to start automatically, and with the above setting, the database instance will not start with it.


BI and Self Service Reporting Tools that Support Web (HTML) Interface?

Pentaho.

There's Jaspersoft too, but I found Pentaho a little more useful, though Japser is prettier.


#Deleted as values when using MS Access to query a db2 database through ODBC

The cause of this problem most likely is the primary key of the table in DB2.

Access can not handle some data types as primary keys well.

Notoriously bad for PKs are all floating point numeric types. They may be rounded differently on the server and in Access. If Access uses a floating point value to query a single record from the server, there will be no results because the actual value on the server is different.
This is usually the cause for the #Deleted displayed in table cells.

Good data types for Primary keys are integer types (max int32) and string/varchar types (max length 255 characters).

Dates and fixed-decimal-point data types may be causing problems as well. That is mainly dependent on the actual backend database system and the used ODBC Driver. - I have no experience in that regard with DB2.

Here are some options to solve or work around this issue

If the problem is caused by date or decimal data types in the primary key column, you should check if there are settings in you ODBC-Driver that change its behaviour for those types. If not, you could research if there is another ODBC-Driver available that is more suitable for the use with Access.
Be aware that there will be data types that Access simply cannot support properly.
Lacking experience with DB2 I cannot give more detailed advice on this.
The best way to solve this issue is obviously changing the structure of the base table in the server database. If you only use unproblematic data types in all the primary key columns will make the problem disappear.
If Access is the primary frontend application for the database, I would strongly recommend going this way. Unfortunately there will be many scenarios where this is just not feasible.
If option 1 and 2 fail or cannot be used in your scenario, the workaround would be to create views on the server that wrap the problematic tables. You then link the views to the Access application instead of the base tables.

If you only need read only access to the data, this is a simple solution. When linking views you can (but don’t have to!) select the unique key of the view. If you don’t select any, the view will be read-only, but the data should be displayed properly.

If you do need direct write access to the data it becomes more complicated. You will need to create an artificial unique column in the view, by converting the data in the primary key column of the table to a varchar representation of the keys data. When linking the view to Access you select your artificial column as primary key. Now the data in the table should be displayed properly and should be editable (except for the key column of course).
This approach will incur performance penalties for the calculated PK-column is not indexed itself. You should investigate if the database supports persisting and indexing the artificial key column in the view.
If option 1-3 cannot be used, there is another option. Do not use a linked the table (or view) at all, but create a Pass-Through-Query in Access to query the data from DB2. PT-Queries are read-only though.

If you need to edit data returned by the PT-Query, you need to send custom SQL statements to the database to update the data. So in this case this will take considerably more effort than the other options.


Incomplete installation with unsupported locale

Install the package locales-all and your postgresql will start up.


The distribution agent failed to create temporary files

Error 5 is an NTFS permissions problem.  What account is the SQL Agent service on the distributor running under?


Please enable this setting:

http://msdn.microsoft.com/en-gb/library/ms188694.aspx
http://msdn.microsoft.com/en-us/library/bb669059(v=vs.110).aspx

This allows the security permission between databases to propagate. Additional details described well in this blog: http://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/


Export remote Postgres table to CSV file on local machine

Both the approaches already suggested appear to be unnecessarily complicated.

Just use psql's built-in \copy command, which works just like server-side COPY but does a copy over the wire protocol to the client and uses client paths.

Because it's a psql backslash command you omit the trailing semicolon, eg:

\copy products TO '/tmp/products.csv' CSV DELIMITER ','

See the \copy entry in the manual for the psql command and the COPY command documenation for more detail.

Just like COPY you can use \copy with a (SELECT ...) query instead of a table name when copying data out (but not in).

A generally inferior alternative that can be useful in a few limited situations is to use:

psql -t -P format=unaligned -P fieldsep_zero=on -c 'SELECT * FROM tablename'

and use the -o flag or shell output redirection to write the output to a file. You should almost always use \copy in preference to this.


Disabling the recycle timeout settings in SSRS

It's recommended to let SSRS recycle periodically so it doesn't hold onto memory it no longer needs, which may be useful to the rest of your server, for example if your SQL Server instance lives on the same server (you wouldn't want SSRS bottlenecking the rest of your SQL Server instance).
If you are able to adjust it to a recycle frequency that happens off-hours that may be best, as noted in this article. Otherwise the correct value is 0 to disable it, as you mentioned. For more information, see this other article Adjusting the SSRS service recycle time to zero seconds.


Can I log psql time and statements to a text file (without the data)?

No, it's not possible currently..

Look at PrintTiming, it is using printf. While the stuff that prints to the log file, is using fprintf. My assumption is that the patch that provides this would be a couple of lines.
You would have to have the ability to suppress the outputting of the result on --log-file, which we don't currently have. And in your case you want the summary logged SELECT 7790 but not the actual data.

.. maybe in the future.


TSQL: How to DELETE in cascade without altering table / indexes

Perhaps simply do it manually with two deletes, in a transaction to ensure consistency:

BEGIN TRANSACTION
DELETE T2 WHERE employeeID IN (SELECT employeeID FROM T1 WHERE <clauses_to_find_the_records_to_delete>
DELETE T1 WHERE <clauses_to_find_the_records_to_delete>
COMMIT TRANSACTION

You might want to add that FK constraint anyway, with or without the cascade options (I very much prefer without cascade, so still needing the manually ordered deletes, it is very convenient in some cases but adds dangers...), if it is correct for your data model.


Does the Always Encrypted certificates get copied over in availability groups?

In AlwaysEncrypted, the keys are not stored on the server to begin with.


Updating a column name to be a concatenation based on the same table

I am not quite sure if I understand your data model from your description, so please check the data to be updated before running it.
You can break the update into two separate statements with a temp table or CTE, which will give you the benefit of seeing the records you want to update. (Use a select from the temp table or CTE beforehand to verify the records found.)
Example
-- Load a temp table with students to update
DROP TABLE IF EXISTS #X

SELECT
student_id

INTO #X
FROM
Grades
GROUP BY
student_id
HAVING
COUNT(DISTINCT profession) > 1 -- Assuming they could have multiple grades.

UPDATE G

SET profession = CONCAT(profession," + ",grade)

SET
FROM
Grades G
INNER JOIN #X X
ON G.student_id = X.student_id

WHERE
X.student_id IS NOT NULL -- Always helpful to have WHERE clause to avoid any accidents.


Current SCN

Oracle 9i:

SELECT dbms_flashback.get_system_change_number as current_scn
FROM DUAL;

Oracle 10g and above:

SELECT current_scn
FROM V$DATABASE;

SCN Limits

SCN has a hard limit imposed by its format and a soft limit imposed artificially by Oracle, as described here. I've quoted the relevant portions below (emphasis added).

Hard Limit

  The architects of Oracle's flagship database application must have
  been well aware the SCN needed to be a massive integer. It is: a
  48-bit number (281,474,976,710,656). It would take eons for an Oracle
  database to eclipse that number of transactions and cause problems --
  or so you might think.

Soft Limit

  The soft limit derives from a very simple calculation anchored to a
  point in time 24 years ago: Take the number of seconds since 00:00:00
  01/01/1988 and multiply that figure by 16,384. If the current SCN
  value is below that, then all is well and processing continues as
  normal. To put this in simple terms, the calculation assumes that a
  database running constantly since 01/01/1988, processing 16,384
  transactions per second, cannot exist in reality.

SCN Limit Check

This script (Oracle 10g and above) will check how much of the hard and soft limits you have exhausted. Thanks to Rob for calling out the soft limit.

WITH limits AS (
  SELECT
      current_scn
  --, dbms_flashback.get_system_change_number as current_scn -- Oracle 9i
    , (SYSDATE - TO_DATE('1988-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * 24*60*60 * 16384
        AS SCN_soft_limit
    , 281474976710656 AS SCN_hard_limit
  FROM V$DATABASE
)
SELECT
    current_scn
  , current_scn/scn_soft_limit*100 AS pct_soft_limit_exhausted
  , scn_soft_limit
  , current_scn/scn_hard_limit*100 AS pct_hard_limit_exhausted
  , scn_hard_limit
FROM limits;


Switch MySQL lag slave to new master and keep lagging (5.1)

DISCLAIMER : Not a user of mha4mysql.

SUGGESTION #1

Hang the Lag Slave off from the Standby Server. How?

Step 01: Stop all Writes to the Master
Step 02: Run SHOW MASTER STATUS; on the StandByMaster
Step 03: Run mysqlbinlog against the binlog from Step 02
Step 04: Acquire timestamp from given position
Step 05: Subtract 43200 sec (12 hrs) from the Timestamp acquired
Step 06: Locate position just before timestamp acquired
Step 07: Using StandByMaster Host IP, Log File and Position, run this on Lag Slave

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='...',MASTER_LOG_FILE='...',MASTER_LOG_POS=...;
START SLAVE;

All manual steps. I'm sorry.

SUGGESTION #2

Setup the Lag Slave under one of the Slaves PERMANENTLY. You'll never have worry about moving the Lag Slave between Masters again.

SUGGESTION #3

Setup the Lag Slave under a Blackhole Slave. You'll never have worry about moving the Lag Slave between Masters again.

I hope these suggestions helped or at least got you thinking.


postgresql, duplicate database which is a replication of another

It looks like you have a physical standby/replica. In physical standby, you cannot have a writeable copy of any database. If you want a writable copy of the database, you can follow either of the following:

Create a new cluster postgreSQL and export the database from a READ replica (physical standby) using pg_dump and pg_restore. (Refer the pg_dump/pg_restore command from PostgreSQL document)
You can use logical replication from the master
https://www.enterprisedb.com/postgres-tutorials/logical-replication-postgresql-explained


Why does adding a TOP 1 dramatically worsen performance?

Since you get the correct plan with the ORDER BY, maybe you could just roll your own TOP operator?

SELECT DOCUMENT_ID, COPIES, REQUESTOR, D_ID, FILE_NUMBER
FROM (
    SELECT dc.DOCUMENT_ID,
           dc.COPIES,
           dc.REQUESTOR,
           dc.D_ID,
           cj.FILE_NUMBER,
           ROW_NUMBER() OVER (ORDER BY cj.FILE_NUMBER) AS _rownum
    FROM DOCUMENT_QUEUE dc
    INNER JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
    WHERE dc.QUEUE_DATE <= GETDATE()
      AND dc.PRINT_LOCATION = 2
) AS sub
WHERE _rownum=1;

In my mind, the query plan for the ROW_NUMBER() above should be the same as if you had an ORDER BY. The query plan should now have a Segment, Sequence Project and finally a Filter operator, the rest should look just like your good plan.


count(*)

should become

coalesce(count(*), 1)

for the simplest case. The denominator doesn't actually matter because 0/anything is always zero.

Also:

s.code IN ('10401','10402','10403') OR NULL

is nonsense. count(x OR NULL) is exactly the same as count(x). NULL is false in boolean context, x OR NULL is the same as just saying x. So just write:

s.code IN ('10401','10402','10403')

If what you're actually trying to say is "count rows that are either one of the listed values or are null" then you need a coalesce. You can't just use IN (..., NULL) since x IN (..., NULL) is always either true or NULL, since x IN (...) is equivalent to x = ANY (...) and x = NULL is NULL. So you must write:

s.code IN ('10401','10402','10403') OR s.code IS NULL


Is there a repository for pgadmin plugins?

Your best place would be pgfoundry. But you won't find much. Doesn't look like many plugins are available.


Perform calculation from count of values for a distinct value

First, distinct is not a function, it applies to the whole row. Most straight forward way would be:

Select host, status, count(1)
From logs
Where status in (200,404)
Group by host, status

This will however return one row for each status. My suggestion is to do what remains in the application since that is all about presentation. If you dont want to do that you can use a case expression to transpose the result:

Select host
    , count(case when status = 200 then 1 end) as #success
    , count(case when status = 404 then 1 end) as #failure
From logs
Where status in (200,404)
Group by host

Sorry about the formatting, typing from my mobile


Postgres GROUP BY id works on table directly, but not on identical view

The problem appears because Postgres knows that table t has (id) as the primary key - so we can use GROUP BY t.id and have more columns from that table in the SELECT list without aggregating them - but it can't infer the same for view vt.

For the examples provided, you can rewrite them as not to use GROUP BY at all. Since you don't need any column from the second joined table in the select list, you can convert the JOIN to an EXISTS subquery, i.e. convert the join to a semi-join. Now both the tables and views semijoin work without errors:

SELECT row_to_json(t.*) as "t_group" FROM t
WHERE t.id = 1
  AND EXISTS
      ( SELECT 1
        FROM y
        WHERE t.id = y.tid
          AND y.amount > 1
      ) ;

SELECT row_to_json(vt.*) as "t_group" FROM vt
WHERE vt.id = 1
  AND EXISTS
      ( SELECT 1
        FROM vy
        WHERE vt.id = vy.tid
          AND vy.amount > 1
      ) ;


Answer to own question: I was blind. The statements are not truncated, but they contain newlines. There was some pre preprocessing done before I looked at them. And this preprocessing was done lines based, so they looked like being cut.


How Can I Access the postgres database in a Script Executing pg_dumpall?

I decided to modify my pg_hba.conf file to the following:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    postgres        all             127.0.0.1/32            md5
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

I'm now able to execute my script using my role and successfully access the postgres database.


If a query does not have a memory grant then it cannot request memory from this 'workspace'. You can see which queries request memory in sys.dm_exec_query_memory_grants. As you already noticed only certain operators request memory grants, namely the operators that require some significant intermediate storage. A nested loop does not require any storage, as it operates one-row at a time, therefore it will not request a memory grant. A hash requires memory to built the hash tables, and a sort obviosuly requires sort tables. Queries without a grant still need some memory, but that adds up to trivial ammounts (maybe few KBs) and such memory is obtained form ordinary execution allocators. A grant is measured usually in MBs and can go into the 100s of MBs.

The memory reserved for grants is not exactlty 'part of the buffer pool', that makes it sound like is a separate portion of the buffer pool. Is more of a gray area. The memory grant is backed by the buffer pool. A query that is granted X amount of memory places a reservation on the BP. It does not 'steal' the pages from BP right away. But since the (unstolen part of) BP contains data pages, the BP can make good on the reservation promise because it can evict data pages to disk anytime it likes. As the query makes progress it may request pages from the BP from the grant it acquired (from the reservation) and as the BP fulfills the request, the pages handed out to the query become really 'stolen', ie. the BP can no longer use them for data until the query release them back. In order to ensure all grants can be honored the sum of reservations cannot exceed the BP size (ie. there is no VM style over-allocation). These grant reservations tend to err on the overallocation side by a generous margin as things can turn out quite ugly if a query consumes all the grant and styll needs memory: spills occur (extremly slow) or the query may give up and error.

See Understanding SQL server memory grant


Mysql find 10 nearest point from given lat/long per type?

Searching for the nearest 10 points of interest gives, usually, near points. In that case the usual cartesian distance (SQRT((LAT1 - LAT2)^2 + (LONG1 - LONG2)^2)) can be used.

Given a generic table (for the definition of latitude and longitude I've used this pointer.)

CREATE TABLE POI (
   ID INT IDENTITY(1, 1)
 , `Name` Varchar(100)
 , `Type` CHAR(1)
 , `LAT`  Decimal(10, 8)
 , `LNG`  Decimal(11, 8)
)

The query would be something like

DECLARE myLAT Decimal(10, 8) DEFAULT 45.48561;
DECLARE myLNG Decimal(11, 8) DEFAULT 123.47981;

(SELECT `Name`, `LAT`, `LNG`
 FROM   POI
 WHERE  `Type` = 'A'
 ORDER BY SQRT(POWER(myLAT - `LAT`), 2) + POWER(myLNG - `LNG`), 2)
 LIMIT 10)
UNION ALL
(SELECT `Name`, `LAT`, `LNG`
 FROM   POI
 WHERE  `Type` = 'B'
 ORDER BY SQRT(POWER(myLAT - `LAT`), 2) + POWER(myLNG - `LNG`), 2)
 LIMIT 10)
UNION ALL
(SELECT `Name`, `LAT`, `LNG`
 FROM   POI
 WHERE  `Type` = 'C'
 ORDER BY SQRT(POWER(myLAT - `LAT`), 2) + POWER(myLNG - `LNG`), 2)
 LIMIT 10)

In case the point of interest are few and far from the 'here' point and from themselves you have to use a different formula for the distance, to account that the earth is, more or less, a sphere.


Cannot revoke permissions or drop user in PgSQL AWS RDS

I was running into similar issue on Postgres RDS. This was the workaround I used to reassign objects from one user to another and then drop user. For you it would go like:

-- logged in as the rds_superuser role you defined when you created your RDS instance
CREATE ROLE change_owner LOGIN;
GRANT asd TO change_owner;
GRANT foo_migration TO change_owner;

-- log out of current user and log in as `change_owner`
REASSIGN OWNED BY asd TO foo_migration;

-- log out of current user and log in as rds_superuser role
DROP USER asd;


SQL Server: Enable Full-Text Search on model Database

Edited: It looks like the answer is:  No, you cannot create a full-text index in a system database.

You can, of course, run a script to turn on a full-text indexing after the new database is created from model, if that is necessary.  But by default all user databases in the more recent versions of SQL Server have full-text indexing turned on automatically.

Note that from SQL Server 2008 forward, the sp_fulltext_database command is a no-op, just supported for script compatibility.

So, there is certainly no reason, unless your are supporting SQL Server 2005 or earlier version, to have this code included in your standard scripts.  It is obsolete.


Oracle cannot connect to SQL developer as sys account

sqlplus user/password as sysdba uses OS authentication by default, and bypasses the username and password check, so as you said, you can provide any password or username.

Logging in from SQL Developer requires a remote session. For a remote SYSDBA connection, you need the following:

set remote_login_passwordfile to EXCLUSIVE or SHARED, but not NONE
create a password file for the database if it doesn't already exist:

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID

After this you should be able to log in with SYS and the password you provided.


If you use PostgreSQL probably http://pgxn.org/dist/temporal_tables/ can help you.


Remove duplicate records with timestamps

So doing a join on the elements that constitute a duplicate and the last where criteria shows which to delete.

delete t1
from table t1, table t2
where t1.user_id = t2.user_id and t1.stat1=t2.stat1
     and t1.stat2=t2.stat2  and t1.stat3=t2.stat3
and t1.row_id+1=t2.row_id


Does Query Plan cache gets cleared by itself?

It's very unlikely, but you could be experiencing the bug described in the first comment to this user voice item: Procedure Cache empties spontaneously even on an idle machine

  This seems to happen when USERSTORE_TOKENPERM hits a certain value.
  ...
  Under this configuration, the plan cache empties spontaneously when the USERSTORE_TOKENPERM size grows to around 5 GB

You can check for that with this query:

select pages_kb
from sys.dm_os_memory_clerks
where [type] = 'USERSTORE_TOKENPERM' and [name] = 'TokenAndPermUserStore';

There are several other factors that can cause your plan cache to be emptied.  A non-exhaustive list of these factors includes:

someone ran the DBCC FREEPROCCACHE command to manually clear it
The server is under memory pressure, and old or single-use plans are being evicted from the cache


Split date range (timestamp) into equal parts by Month- SQL Server

WITH
-- Range borders
src_data AS (SELECT CAST('2020-06-29 09:00:00' AS DATETIME) AS fromdate,
                    CAST('2021-06-29 09:00:00' AS DATETIME) AS todate),
-- numbers table
numbers AS (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
             UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
             UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11)
-- The query
SELECT num + 1 id,
       DATEADD(SECOND, num * DATEDIFF(SECOND, fromdate, todate) / 12, fromdate) fromdate,
       DATEADD(SECOND, (num + 1) * DATEDIFF(SECOND, fromdate, todate) / 12, fromdate) todate
FROM src_data, numbers
ORDER BY id;

fiddle
The range is divided to equal (in seconds) parts. You may alter the calculations granularity (for example, use HOURS instead of SECOND), parts lengths will be adjusted and rounded automatically.
You may replace numbers CTE with any other which gets/generates the integers list from 0 to parts_amount-1, and parametrize the divident used in a query (12, may be replaced with, for example, SELECT COUNT(num) FROM numbers).


How to turn off logging of "Software Usage Metrics enabled" and ESENT errors

I'll point out that disabling SQLCEIP's service is not a supported configuration, so while you can disable sending information to Microsoft in the paid versions of SQL Server 2016+, the proper mechanism for doing this is documented here:

https://docs.microsoft.com/en-us/sql/sql-server/customer-experience-improvement-program-for-sql-server-data-tools?view=sql-server-2017

(This would leave the SQLCEIP service running and not emitting information to Microsoft).  Microsoft recommends you follow documented procedures to make sure that when you are engaged with customer support on an issue they can help fix that issue quickly.

As to your specific question about what this "extra" session is, that feature is not something that is emitting information out to Microsoft.  It is a feature that enables a tool that customers can use to determine how many copies of SQL they have running in their organization.  The data emitted from SQL goes into a file on the local Windows machine.  You can use the tool yourself to see what it does - it is called the Microsoft Assessment and Planning Toolkit, and you can download it here:

https://www.microsoft.com/en-us/download/details.aspx?id=7826

As for whether SQLCEIP is "spyware", I'll try to make sure that you have full information about what kinds of information is collected/not collected through the SQLCEIP mechanism.  Microsoft does not try to capture keystrokes, customer data, or otherwise use information we collect to market to customers at all (as you might find in a traditional definition of spyware such as https://simple.wikipedia.org/wiki/Spyware ).  The information collected through SQLCEIP is used to make sure that features are working well for customers both during the development cycle of a new version of SQL/new features and to make sure that we understand how our customers use the product so we can make it better.  We don't collect information to personally identify anyone and we don't want to know who you are or what you do individually.  We are interested in "how many people use Columnstore indexes?" and "how many rowgroups inside of a Columnstore index are optimally packed so that the customer is likely to get a good performance experience?".  The information collected help us make sure that our engineering teams have done a complete job at making each feature work for customers.

You can audit all the information collected by SQLCEIP to see this for yourself.  The instructions are here:

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/local-audit-for-sql-server-usage-feedback-collection?view=sql-server-2017

Furthermore, we document our privacy policies about what kinds of information we collect/do not collect.  Microsoft recently re-vamped all parts of the company to use a single privacy statement, but SQL has a supplement which is aligned closely with its former separate privacy statement since we wanted it to be easy to read/understand and not get lost in the data collection policies for the other consumer services Microsoft provides.

Here's the top-level Microsoft privacy statement:

https://privacy.microsoft.com/en-US/privacystatement

Here's the privacy supplement for SQL Server:

https://docs.microsoft.com/en-us/sql/sql-server/sql-server-privacy?view=sql-server-2017

I hope that this answers your questions as to what the software usage metrics mechanism is and why it is not sending information to Microsoft/related to SQLCEIP.


I can't speak to 'via an AWS CLI script' specifically. But SQL does not care where the backups are.  It knows they got taken and where they were written to.

Assuming you are using a system wide backup product like TSM to backup your SQL backups as well as the rest of the machine:

Don't delete the fulls and diffs, overwrite them.

If you run your full and/or differential backups at 6AM and you backup them up remotely at noon.  You can safely overwrite them at 6AM the next day.

You can delete the local t-logs when they are at the remote location.  You might want to leave 24-48 hours of them locally for "accident" (someone did something stupid) recovery.

It is important that ensure that your SQL backups are completed before the the remote (i.e. TSM) backup process runs.  You create SQL backups, because products like TSM will not backup files in use, like your mdf & ldf files.  It will also not backup, your SQL backups if they are being written to during the off site backup.  I ran a 7 week experiment appending backup files, and TSM did not move the backups of site until the experiment ended.

  Off site backups, It looks like my offsite backup solution (IBM Spectrum) is not backing up the trn file. I suspect this is because the file is constantly being edited. Source

Besure to regularly validate your backup solution with restores from your offsite location.


The generic way to "remove" a table is to drop it:

DROP TABLE foo;

When you drop the table all indexes will also be dropped. If you need to drop them separately use

DROP INDEX foo_index;

For PostgreSQL the easiest way to get rid of all objects in a schema is to drop the schema:

DROP SCHEMA test_schema CASCADE;

Of course you could have found out that yourself by simply looking at the manual:

http://www.postgresql.org/docs/current/static/sql-droptable.html
http://www.postgresql.org/docs/current/static/sql-createindex.html

http://dev.mysql.com/doc/refman/5.5/en/drop-table.html
http://dev.mysql.com/doc/refman/5.5/en/drop-index.html


problems when importing csv files into SQL Server using SSIS when the columns are not consistent from quarter to quarter

You can use a script transformation to create the actual column mapping. For your flat file source, make every output line become a single large column.

In your script transformation, create column definitions for each column you need mapped / already know about.

This assumes your first line contains headers:

Then, in your script transformation , split each row by the delimiter you need. You can create a boolean value indicating if the first line has been read yet. If not, parse the line and store the column mapping positions in a Dictionary<string,int> such as ColumnMappings and do not write to OutputBuffer. If your row reader sees that the column mappings have been read, then you can set your output to the correct column with something like:

 OutputBuffer.MyMappedColumn = SplitRow[ColumnMappings["MyMappedColumn"]];

An example of an entire transformation with a column "Day" is below

using System;
using System.Data;
using System.Text;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent {
    UTF8Encoding enc = new UTF8Encoding();
    char[] separator = { ',' };
    bool _isColumnMappingComplete = false;
    System.Collections.Generic.Dictionary<string, int> _columnMapping;
    bool isWritable = false;

    bool isColumnMappingComplete { //Set true after first row processed
        get{ return _isColumnMappingComplete; }
        set{ _isColumnMappingComplete = value;}   }

    System.Collections.Generic.Dictionary<string, int> columnMapping{ //Object to hold column mapping indexes
        get {
            if (_columnMapping == null) { _columnMapping = new System.Collections.Generic.Dictionary<string, int>(); }
            return _columnMapping; }
        set {
            _columnMapping = value;
            isColumnMappingComplete = true; } }

    public override void PreExecute() {
        base.PreExecute(); /* Add your code here for preprocessing or remove if not needed */ }

    public override void PostExecute()    {
        isWritable = true;
        base.PostExecute();
        /* Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example: Variables.MyIntVar = 100 */  }

    public override void Input0_ProcessInputRow(Input0Buffer Row)    {
        bool isEmptyRow = false;
        //byte[] rawRowBytes = Row.Column0.GetBlobData(0,(int)Row.Column0.Length); //uncomment if using Text input datatype
        //string rawRow = enc.GetString(rawRowBytes); //uncomment if using Text input datatype
        string rawRow = Row.Column0; //comment out if using Text input datatype
        if (rawRow.Replace("\"", "").Replace(",", "").Replace(" ", "").Length == 0)
        { isEmptyRow = true; }
        if (!isEmptyRow) {
            string[] rowData = rawRow.Split(separator);

            if (!isColumnMappingComplete) {
                System.Collections.Generic.Dictionary<string, int> firstColMap = new System.Collections.Generic.Dictionary<string, int>();
                for (int i = 0; i < rowData.Length; i++)
                { firstColMap.Add(rowData[i], i); }
                columnMapping = firstColMap;    }
            else {
                Output0Buffer.AddRow();
                bool _rowError = false;
                //Day
                if (columnMapping.ContainsKey("Day"))
                { Output0Buffer.Day = rowData[columnMapping["Day"]]; }
                else
                { //Output0Buffer.Day = "-";
                    _rowError = true; }   }      }    }

    public override void CreateNewOutputRows()
    {   /* Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
         * For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput". */ }     }


An inner join is the subset of rows from the cartesian product where a certain condition is true. Although the cartesian product is not commutative (nor associative), it is with regard to relational theory.

It is so because the order of attributes doesn't matter in relational theory. If A and B are tables, then:

A x B = { (a1, a2, .., an, b1, b2, .. bn) | (a1..an) € A and (b1..bn) € B}
      = { (b1, b2, .., bn, a1, a2, .. an) | (a1..an) € A and (b1..bn) € B}  ((1))
      = { (b1, b2, .., bn, a1, a2, .. an) | (b1..bn) € B and (a1..an) € A}  ((2))
      = B x A                                                               ((3))

((1)) because the order of attributes doesn't matter
((2)) because the logical and is commutative
((3)) by definition of B x A

The condition for choosing which elements of the cartesian must be selected will be the same no matter the JOIN order, and as such, doesn't influence this reasoning.

The proof for associativity follows the same line of reasoning.