Disabling password change for a MySQL user

One way to achieve this could be by using pluggable authentication. This way the password is stored externally and therefore can't be changed from within mysql. This allows you to use PAM, LDAP or other authentication services.

For PAM, assuming you have configured PAM already in your OS (assuming Linux below), then in MySQL do:

INSTALL PLUGIN authentication_pam SONAME 'authentication_pam.so';
CREATE USER bob@'%' IDENTIFIED WITH authentication_pam AS 'mysql';

... where AS 'mysql' refers to a file /etc/pam.d/mysql.

If the user then tries to set their password (I've tested this only on MariaDB with the unix_socket plugin):

SET PASSWORD = PASSWORD('My_very_clever_password');

... that query will "work", however it gives a warning:

  SET PASSWORD has no significance for users authenticating via plugins


I think the answer to many of your questions is that it depends. What performance problems are you having? It seems unusual that a database would have performance problems just from growing to 250GB in size.

Perhaps your queries are performing table scans on the entire fact table even when only a small portion (e.g., the last year) of the date range is needed? If there is a particular query that is most important to optimize, considering posting your schema, query, and an actual execution plan in another question to see if it can be optimized.

    Do you prefer one of the solutions over the other?

I generally prefer the history database, and I think Guy describes good reasons for this in his response.

The primary disadvantage I see for a history database (as opposed to a schema) is that you can no longer use foreign keys for your archive table. This may be fine for you, but it's something to be aware of.

The disadvantage you listed for this approach is not accurate; you will be able to query across databases on the same server easily and the query optimizer generally handles cross-database queries very well.

    Are there any better possibilities?

If you need to query the archive data regularly, I might consider partitioning the table by date. However, this is a big change that can come with a lot of performance implications, both positive (e.g., partition elimination, more efficient data loading) and negative (e.g., slower singleton seeks, greater potential for thread skew in parallel queries). So I wouldn't make this decision lightly if it's a heavily used database.

    Would the newly created archive table also need primary / foreign keys?
    Or should they just have the columns but without keys / constraints?

I would recommend having at least the primary key and unique indexes so that you can get the data integrity benefits they provide. For example, this will prevent you from accidentally inserting a year of data into the history table twice. And as a side benefit it may improve performance if you do need to query the history table.

    Any other thoughts?

Since you are using Enterprise edition and planning to upgrade to SQL 2008+, you might consider data compression for this table. Compression will certainly reduce disk space, but depending on your server's disk and CPU resources it may also improve query performance for reads by reducing disk I/O and improving memory utilization (more data fits in cache at once).


Why is my primary key a separate relation taking up storage?

In PostgreSQL, the only way to guarantee uniqueness is with a unique index. So a unique index is used to implement all primary key and unique indexes in PostgreSQL.
The extra space that the primary key takes is to be expected.

Also, PostgreSQL does not maintain a certain order in the table. Whenever you update a row, it moves somewhere else, and the free space left by past updates and deletes is reused for future inserts.


Database keeps using utfmb4 instead of utf8

So it looks like when the server variables are set, they don't change the database that had already been created before. So what I did was to:

Set the server variables as shown in the question.
Verify that they are now as expected as shown in the question.
Recreate the database again, and check that the database encoding and collation is correct.


What are the disadvantages of using Couchbase's Global Secondary Indexes (GSI)?

The other advantage views have theoretically is that with a large rate of mutations, you'd have a lot of index recalculations.  Views are local to each node, so you don't need to move the data across the physical network to index it.

This all depends on implementation details, the size/types of your documents/indexes, how your system resources are laid out, etc.  Testing should tell you which one works best and note that there is a lot of active development on GSI features such as plasma.


Postgres fetch exact value when satisfied all conditions

I think this does what you want:

select *
from product p
where opid = 1 and opvalue = 1
and not exists (
  select *
  from product x
  where x.info = p.info
  and (x.opid != p.opid or x.opvalue != p.opvalue)
);

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1efcb7a125d52c27b2c386307d37da5b


It's difficult without the data but let's recreate a mental experiment.

CREATE TEMPORARY TABLE foo AS
SELECT * FROM ( VALUES
 ( 1, 2, true ),
 ( 1, 3, true ),
 ( 1, 4, false)
) AS t(cp, bd, contains);

Now... What happens if I join this table to itself on condition that returns true more than once.

SELECT *
FROM foo AS f1
JOIN foo AS f2
  USING (contains);
 contains | cp | bd | cp | bd
----------+----+----+----+----
 t        |  1 |  2 |  1 |  3
 t        |  1 |  2 |  1 |  2
 t        |  1 |  3 |  1 |  3
 t        |  1 |  3 |  1 |  2
 f        |  1 |  4 |  1 |  4
(5 rows)

Now this may make perfect sense in this form, but if you conceal a table from the result..

SELECT f1.*
FROM foo AS f1
JOIN foo AS f2
  USING (contains);
 cp | bd | contains
----+----+----------
  1 |  2 | t
  1 |  2 | t
  1 |  3 | t
  1 |  3 | t
  1 |  4 | f

Now you're going to be confused. So the problem here is that you have multiple cases where one cp.geom contains numerous bd.geoms

Now how does that work if you add a GROUP BY..

SELECT f1.*, sum (f2.bd)
FROM foo AS f1
JOIN foo AS f2
  USING (contains)
GROUP BY f1.cp, f1.bd, f1.contains
 cp | bd | contains | sum
----+----+----------+-----
  1 |  3 | t        |   5
  1 |  2 | t        |   5
  1 |  4 | f        |   4

And, that's precisely your problem. Here you're getting a sum() of 5. Which is understood given the above tables that produced that.

So if you want to debug this check the condition ST_Contains(cp.geom, bd.geom) output cp.geom and bd.geom using ST_AsText()


not a GROUP BY expression error when using a function in a query

You can not just use any function in the SELECT list when you use GROUP BY.

You need to use an aggregate function (MIN, MAX, SUM, etc) on columns not included in the GROUP BY list, or include the columns used by a regular function in the GROUP BY list.

You use PROF_EMPLOYEE_DATA.SHIFT_ID and PROF_EMPLOYEE_DATA.EMPLOYEE_ID with your function, but they are not included in the GROUP BY list.

Including those columns in the GROUP BY list should solve this, but that may not provide the result you want.

Another way is to write your own aggregate function:

Using User-Defined Aggregate Functions


I want to know if SQL Server 2012 protect msdb.dbo.sp_send_dbmail parameters?

You can SQL Inject it without much issue, sadly.  Here's a simple test I just ran with an elevated account with the results included:

USE [master]
GO

DECLARE @msgbody NVARCHAR(4000)
SET @msgbody = N'Test to see if this SP can suffer from SQL Injection' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile'
    , @recipients = 'john.eisbrener@Contoso.com'
    , @reply_to = 'john.eisbrener@Contoso.com'
    , @subject = 'SQL Injection Test'
    , @query = 'CREATE TABLE MyDB.dbo.johnSITest
(
      ID INT IDENTITY(1,1)
    , VAL VARCHAR(50)
)

INSERT INTO MyDB.dbo.johnSITest
    (VAL)
VALUES
    (''yes''), (''you''), (''can'')'
    , @body = @msgbody
GO

Mail (Id: 36756) queued.

SELECT *
FROM MyDB.dbo.johnSITest
GO

ID          VAL
----------- --------------------------------------------------
1           yes
2           you
3           can

(3 row(s) affected)

DROP TABLE MyDB.dbo.johnSITest
GO

Now if the account executing it doesn't have elevated rights, this should limit the exposure of the SP.  The minimum permissions needed for an account to execute this SP are as follows:

  Permissions

  Execute permissions for sp_send_dbmail default to all members of
  the DatabaseMailUser database role in the msdb database.
  However, when the user sending the message does not have permission to
  use the profile for the request, sp_send_dbmail returns an error
  and does not send the message.

The Principle of least privilege should always dictate your security approach, but again, yes it can be SQL Injected much like anything that ingests Dynamic SQL.

EDIT: In response to your follow-up question in the comments, you can further lock this down by wrapping a call to sp_send_dbmail within another stored procedure where the @query parameter is statically defined or more tightly controlled.  This new SP can then take advantage of impersonation (ref1, ref2) and you would then only need to grant EXECUTE rights to explicit users that you want to expose this functionality to.  This should lock things down pretty well and depending on how you limit the use of the @query parameter being passed to sp_send_dbmail, you may be able to completely eliminate the potential for SQL Injection occurring from normal accounts.  Again, any elevated account that can directly call sp_send_dbmail will still have the ability to extort the potential for SQL Injection, but this approach will lock it down about as good as you can.

Because I typed this up hastily, here's also an example in hopes it shows the approach a bit better:

-- CREATE THIS PROCEDURE USING AN ACCOUNT THAT HAS SUFFICIENT ACCESS TO THE DATA AND SP_SEND_DBMAIL
CREATE PROCEDURE [dbo].[limited_sp_send_dbmail]
      @messageBody NVARCHAR(MAX)
    , @recipientList VARCHAR(MAX)
    , @reply_to_address VARCHAR(MAX)
    , @subject_line NVARCHAR(255)
WITH EXECUTE AS SELF
AS
    -- does not allow the usage of @query parameter
    EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile'
        , @recipients = @recipientList
        , @reply_to = @reply_to_address
        , @subject = @subject_line
        , @body = @messageBody
GO

EXECUTE [limited_sp_send_dbmail] @messageBody = N'Hello World!', @recipientList = 'john.eisbrener@Contoso.com', @reply_to_address = 'john.eisbrener@Contoso.com', @subject_line = N'Hello World!'
GO

Mail (Id: 36757) queued.


Get total amount of time between records when changed state

We can use conditional aggregation and an OVER clause to get the number of broken devices for each row in devicestatus.

SELECT device_owner,
       timestamp,
       sum(CASE status
             WHEN 0
               THEN 1
             WHEN 1
               THEN -1
           END) OVER (PARTITION BY device_owner
                      ORDER BY timestamp) broken#
       FROM devicestatus;

We now use lag() to get the previous number of broken devices for each row.

SELECT device_owner,
       timestamp,
       broken#,
       lag(broken#,
           1,
           0) OVER (PARTITION BY device_owner
                    ORDER BY timestamp) previous_broken#
       FROM (SELECT device_owner,
                    timestamp,
                    sum(CASE status
                          WHEN 0
                            THEN 1
                          WHEN 1
                            THEN -1
                        END) OVER (PARTITION BY device_owner
                                   ORDER BY timestamp) broken#
                    FROM devicestatus) x1;

Now we use conditional aggregation and the OVER clause once again to get an identifier for each period where at least one device of a user was down.

SELECT device_owner,
       timestamp,
       sum(CASE
             WHEN previous_broken# = 0
               THEN 1
             ELSE
               0
           END) OVER (PARTITION BY device_owner
                      ORDER BY timestamp) broken_period#
       FROM (SELECT device_owner,
                    timestamp,
                    broken#,
                    lag(broken#,
                        1,
                        0) OVER (PARTITION BY device_owner
                                 ORDER BY timestamp) previous_broken#
                    FROM (SELECT device_owner,
                                 timestamp,
                                 sum(CASE status
                                       WHEN 0
                                         THEN 1
                                       WHEN 1
                                         THEN -1
                                     END) OVER (PARTITION BY device_owner
                                                ORDER BY timestamp) broken#
                                 FROM devicestatus) x1) x2;

From here on we can now use GROUP BY to get the minimum and maximum timestamp of the periods where at least one device of a user was down.

SELECT device_owner,
       max(timestamp) mints,
       min(timestamp) maxts
       FROM (SELECT device_owner,
                    timestamp,
                    sum(CASE
                          WHEN previous_broken# = 0
                            THEN 1
                          ELSE
                            0
                        END) OVER (PARTITION BY device_owner
                                   ORDER BY timestamp) broken_period#
                    FROM (SELECT device_owner,
                                 timestamp,
                                 broken#,
                                 lag(broken#,
                                     1,
                                     0) OVER (PARTITION BY device_owner
                                              ORDER BY timestamp) previous_broken#
                                 FROM (SELECT device_owner,
                                              timestamp,
                                              sum(CASE status
                                                    WHEN 0
                                                      THEN 1
                                                    WHEN 1
                                                      THEN -1
                                                  END) OVER (PARTITION BY device_owner
                                                             ORDER BY timestamp) broken#
                                              FROM devicestatus) x1) x2) x3
       GROUP BY device_owner,
                broken_period#;

We can now simply use datediff(), sum() and GROUP BY to calculate the total downtime for each device owner.

SELECT device_owner,
       sum(datediff(minute,
                    maxts,
                    mints)) total_breakdown_min
       FROM (SELECT device_owner,
                    max(timestamp) mints,
                    min(timestamp) maxts
                    FROM (SELECT device_owner,
                                 timestamp,
                                 sum(CASE
                                       WHEN previous_broken# = 0
                                         THEN 1
                                       ELSE
                                         0
                                     END) OVER (PARTITION BY device_owner
                                                ORDER BY timestamp) broken_period#
                                 FROM (SELECT device_owner,
                                              timestamp,
                                              broken#,
                                              lag(broken#,
                                                  1,
                                                  0) OVER (PARTITION BY device_owner
                                                           ORDER BY timestamp) previous_broken#
                                              FROM (SELECT device_owner,
                                                           timestamp,
                                                           sum(CASE status
                                                                 WHEN 0
                                                                   THEN 1
                                                                 WHEN 1
                                                                   THEN -1
                                                               END) OVER (PARTITION BY device_owner
                                                                          ORDER BY timestamp) broken#
                                                           FROM devicestatus) x1) x2) x3
                    GROUP BY device_owner,
                             broken_period#) x4
       GROUP BY device_owner;

SQL Fiddle


SQL Server Query Store - Force Plan does not work

Query store uses the query hash to identify and group queries together. If you're seeing a new plan ID every time this proc executes, it could be because you have a different SQL hash each time maybe because of dynamic SQL. Without seeing the code it is difficult to determine why.

You should check your cache for recent executions of this procedure and verify if they have different query hash values as this will be impacting Query Store's ability to track that query's executions.

To force the old Cardinality Estimator, you can add OPTION(QUERYTRACEON 9481) to your queries to force the use of the old pre-2014 cardinality estimator. In SQL 2016 SP1 and higher, you can also use OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) which does the same thing but doesn't require permissions to turn on trace flags.

This should force the use of the old CE and return your query to more predictable performance levels, however, you should also look at tuning and refactoring your stored procedure to identify the cause of the poor performance with the 2014+ CE and resolve it if possible.


How do I update azure SQL baseline to remove certain db owners?

You can remove members from a role using the sp_droprolemember stored procedure. Similarly, the stored procedure sp_addrolemember can add additional users to the role.
Here is a statement to remove members from the db_owner role.
EXEC sp_droprolemember [db_owner],[<User Name here>]


PostgreSQL crashes during count row query

I run everything on my OS X Catalina laptop and use PostgreSQL 12
  EnterpriseDB.

There is a JIT-related problem on this platform and version that leads to the kind of crash you're reporting. It's explained on mailing lists:

Re: PG v12.2 - Setting jit_above_cost is causing the server to crash

At the moment, the workaround seeems to be to set jit = off until EDB releases a new version with a fix, hopefully in PostgreSQL 12.3


That might've been a little bit of poor wording on Microsoft's part but basically what they meant is run that query in the context of the database which you want to check if there are Orphaned Users for. So which ever database you run it under, it'll tell you only for that database of there are Orphaned Users.
They clarify that slightly, just below the example query they provide in the article you linked:

The output lists the SQL Server authentication users and corresponding
security identifiers (SID) in the current database that are not linked
to any SQL Server login.

So if you have 3 databases on your server, for example, then you'd need to run that query 3 times (once in the context of each database) to verify for all of them.


A couple of notes,
Rather than track only failures, it's generally a good idea to know when people sign in, too. There is probably already a table that handles authentication tokens, but there's no reason why failed_login_attempts couldn't contain both successful and unsuccessful authentication attempts.
By having isLocked on a secondary table you leave yourself open to a bunch of crazy hack "solutions" to fix data inconsistencies when an account should be marked as locked or not ... particularly for users who regularly lose their passwords. Logically, if an account is "locked", then that should go on the Account (or User) table so that you're not always hitting a table that will quickly have a lot of data to determine whether a login can take place.
For example:
Account
  id
  name
  email
  ...
  is_locked
  updated_at

Then ...
LoginTXN
  id
  account_id
  success
  source_ip
  created_at

From here you can record all login attempts into LoginTXN (or whatever you decide to call it), mark the success field as 0 on failure, 1 on success (or N and Y), and go from there.
When checking to see how many login attempts have been made, you'll need to do something like:
SELECT COUNT(txn.id)
  FROM LoginTXN txn INNER JOIN (SELECT z.account_id, MAX(z.id) as recent_success
                                  FROM LoginTXN z
                                 WHERE z.account_id = 999
                                 GROUP BY z.account_id) sok ON txn.account_id = sok.account_id
                                                           AND txn.id > sok.recent_success

Note: This is pseudo-code. Don't simply copy/paste it.

Which will then give you a count. If you want to have conditions like "5 failed attempts in the last hour", thereby locking the account for — at most — 60 minutes, you could do that with a WHERE statement that examines the created_at value in LoginTXN.
Of course, if this is being put into a view or a stored procedure, then an is_locked field on Account may be wholly unnecessary as the authentication rule could be encapsulated completely within the view or stored procedure.
Hope this gives you a couple of pointers that will lead you to a more complete solution.

Friendly Aside: Try to stick with a consistent naming scheme when making tables. If you prefer to use words separated by underscores, like is_locked, then stick with that. If you prefer camel-case, like isLocked, then stick with that. You — and every other developer who works on this code in the future — will appreciate a predictable consistency. As you can probably see from my examples above, I tend to go with camel-cased tables, and underscored columns.


Why is pg_restore unable to create database with --create option before restoring from dump?

In order to create a database, it needs to be able to connect to your database server in order to issue the "CREATE DATABASE" command.  Connecting to the server requires you to specify an existing database to connect under, and obviously that cannot be the database which fails to exist and which is to be created.  There are special databases usually reserved for maintenance operations, usually postgres, which you can connect to for this purpose.

When using --create mode, --dbname specifies not the name of the database to be restored into, but rather the name of the database to be used to create the other database.


MongoDB - Database vs Collection memory-wise

As you said you have MongoDB version 2.4, the MongoDB documentation here and here specifications

WiredTiger Storage Engine

Starting in MongoDB 3.2, the WiredTiger storage engine is the
default storage engine.
Minimum log record size for WiredTiger is 128 bytes. If a log record
is 128 bytes or smaller, WiredTiger does not compress that record.

Memory Use

With WiredTiger, MongoDB utilizes both the WiredTiger internal cache and the filesystem cache.

Starting in 3.4, the WiredTiger internal cache, by default, will use the larger of either:

50% of (RAM - 1 GB), or
256 MB.

By default, WiredTiger uses Snappy block compression for all collections and prefix compression for all indexes. Compression defaults are configurable at a global level and can also be set on a per-collection and per-index basis during collection and index creation.

  Note : For existing deployments, if you do not specify the --storageEngine or the storage.engine setting, the version 3.2+ mongod instance can automatically determine the storage engine used to create
  the data files in the --dbpath or storage.dbPath.

MMAPv1 Storage Engine

MMAPv1 is the default storage engine for MongoDB versions 3.0 and
earlier.
MongoDB 4.0 deprecates the MMAPv1 Storage Engine and will remove
MMAPv1 in a future release. To change your MMAPv1 storage engine
deployment to WiredTiger Storage Engine, see:

a.) Change Standalone to WiredTiger

b.) Change Replica Set to WiredTiger

c.) Change Sharded Cluster to WiredTiger

Memory Use

With MMAPv1, MongoDB automatically uses all free memory on the machine as its cache. System resource monitors show that MongoDB uses a lot of memory, but its usage is dynamic. If another process suddenly needs half the server’s RAM, MongoDB will yield cached memory to the other process.

Technically, the operating system’s virtual memory subsystem manages MongoDB’s memory. This means that MongoDB will use as much free memory as it can, swapping to disk as needed. Deployments with enough memory to fit the application’s working data set in RAM will achieve the best performance.

  For example i need to have 5 collections, each collection is about
  10GB. What is the difference in performance, with emphasis on memory
  usage, between assigning each said collection to a database, versus
  having all of these collections in the same database?

As per google group forum by Mr.Kevin Adistambha defined here it is not advisable to push any technology to such extreme numbers without careful thought due to security, availability, and performance issue.

MongoDB is perfectly capable of doing 5 collections , as each collection with 10 GB in a single database.

The page MongoDB Limits and Thresholds describes the limits of MongoDB capabilities in detail.

For further your ref here and here


How to avoid server crash during backup of dabase using a crontab

Try using the -q option of mysqldump to see if it could help. Otherwise, try using multiple mysqldumps specifying individual tables.


DBIO Error: Utility 'mysqldump.exe' not found in MySQL home 'MySQL Connector/Net'

I've figured it out. Export database is working now.

I had to copy mysqldump.exe from

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe

to

C:\Program Files (x86)\MySQL\Connector NET 6.5.4\mysqldump.exe


Is Pseudo-Simple SQL Server Recovery a real thing?

See this article from Paul Randal, an expert on database recovery models: New script: is that database REALLY in the FULL recovery mode?

In particular this quote confirms the behavior of a "pseudo simple" recovery model:

  ...when you switch a database into the Full recovery mode, it actually behaves as if it’s in the Simple recovery mode until the log backup chain is established (this is commonly called being in ‘pseudo-Simple’).

Kimberly Tripp elaborates on the topic here: Database Maintenance Best Practices Part III – Transaction Log Maintenance

  What that means is that SQL Server automatically clears inactive records from the transaction log once it knows that it no longer needs them. It no longer needs them to be stored in the log because no one is using the log

I don't totally agree with your statement "Nothing remotely simple about that"
 regarding the transaction log backup failing in the FULL recovery model prior to a FULL backup.  That's just like the SIMPLE model (log backups not allowed).  The error message is different because it's trying to tell the end user how to solve the problem.

I tried your test on SQL Server 2017, and running the copy-only backup consistently triggers an internal CHECKPOINT and clears the log (PercentFree increases for the log file).

On the initial run, PercentFree from the _log file is 65.04.
I ran the copy-only backup, and PercentFree jumped to 88.68.

I confirmed with the following extended events session that a CHECKPOINT was run at this time as well.

CREATE EVENT SESSION [checkpointage] ON SERVER
ADD EVENT sqlserver.checkpoint_begin(
    ACTION(sqlserver.database_id)),
ADD EVENT sqlserver.checkpoint_end(
    ACTION(sqlserver.database_id))
ADD TARGET package0.event_file(SET filename=N'checkpointage')
WITH (STARTUP_STATE=OFF)
GO


Impact of shrinking transaction log file after transaction log backup in SQL Server

Yes there may be performance issues if you continuously shrink the log files. When autogrowth event is triggered so that log file can grow for the database requirement all activities in database freeze to honor the growth request once this is acknowledged and done the processes resumes. Now if you daily shrink your log file it would grow frequently because you would run transactions and this frequent growth would frequently cause autogrowth events and thus frequent freezings. All this combined on a busy system can impact performance. This is why it is always advise to pre size your transaction log file to avoid as much less autogrowth as possible. Also note since Instant File Initialization is not there for log files the autogrowth would take some time as some more time would be spent on zeroing of the records.

Please read
Importance of proper transaction log size management By Paul Randal

PS: It does not matter when you shrink transaction log file it always has some negative affect. You may shrink the log file when some unwanted transaction has bloated the log file and there is a space crunch but never make it a habit. Think of shrink button as option provided to you to get  out of unavoidable circumstances/situations(like disk space issue, transaction log bloat) not for daily use.


Add constraint on number of entries of certain type in referenced table

What you're looking for is called an assertion. While these are in the ANSI standard few DBMS implement them.

The closest alternative is to write code in an AFTER trigger that throws an exception if the desired condition isn't met.


One option is to create a staging table with a new ID as Row_number(). Consider the below sql:

begin transaction createstaging

select row_number() over (order by existingID) newIDValue, DataColA, DataColB, ...
into staging_table
from source_table

truncate table source_table

commit transaction createstaging
GO

set identity_insert source_table on
GO

insert source_table
(existingID, DataColA, DataColB, ...)
select
newIDValue, DataColA, DataColB, ...
from staging_table
order by newIDValue

GO

set identity_insert source_table off
GO

drop table staging_Table
GO

The truncate table in combination with the identity_insert should mean that you do not have to manually reset the identity seed on the source table.


Storing sensitive information in a SQL Server project

You've got to put these things somewhere, where they are:

accessible only to the people who should have access to them;
protected from the people who shouldn't; and,
recoverable in the event of any disaster (be it people moving on or machines getting thrown in a tar pit).

But only you can know where that somewhere is in your environment. It could very well be source control (but probably not); Azure Key Vault is an option, depending on how cloud-averse your business is; some companies use a service like Enterprise LastPass or 1Password Business; or maybe a sticky note on Milton's red stapler is more your speed... as your peers and not business stakeholders, we just don't know.


why GenerateScripts doesn't include SCOPED CONFIGURATION lines?

Because When scripting objects, DB Scoped configuration that has default values are omitted.

Check SQL Server Management Studio - Changelog (SSMS) for more information.

SSMS 18.0 (preview 4) -> Bug fixes -> Object Scripting


Why does the SQL Server build number sequence not correlate with the release date sequence?

Build 13.0.5101.9 - released 9/07/2019 is
https://support.microsoft.com/en-us/help/4505220/kb4505220-security-update-for-sql-server-2016-sp2-gdr-july-9-2019
Build 13.0.5102.14 - released 11/02/2020 is
https://support.microsoft.com/en-us/help/4532097/kb4532097-description-of-the-security-update-for-sql-server-2016-sp2-g
These are General Distribution Release (GDR) builds, intended to be installed by all customers running the affected Service Pack level.
Basically, those are critical patches for SQL 2016 SP2 which don't require installing any of the Cumulative Updates.
The relevant fixes would also have been included in the next CU, and probably available as a hotfix for the current CU.
As for the build numbers, GDR is a seperate branch from CU.  It's tricky to apply a single build number system to a branched codebase, but the strategy appears to be that the GDR build numbers are placed between the common root build number (SP2) and the first CU (SP2 CU1).

if I upgrade my system to 13.0.5149.0,

That's CU1, and once you are on the "CU Train" you must apply subsequent CUs to get fixes.  That's why the KB's say

To apply this update, you must have SQL Server 2016 SP2 or any SQL
Server 2016 SP2 GDR release through this SQL Server 2016 SP2 GDR
installed.


Magicly, mytop thought of your question, a decade in advance, and had a -h flag. It also supports -u for user, -p for password, -P for port, and other options as per the mytop pod page in a similar way to mysql.

So you're able use it on a remote host like:

mytop -h db-test-1


Tuning Mongo (TokuMX) for fastest bulk reads

The first thing to look at is db.serverStatus().ft.  This has a bunch of metrics that may be helpful, to figure out where you're spending time.  These are documented here: http://docs.tokutek.com/tokumx/tokumx-server-status.html

Usually the way to improve query time is to make sure you have the right index for your query.  You might be doing a query on {field: value} and have an index on {field: 1}, but if you're not projecting in your query, you'll still be doing lots of point queries back to the primary key index to get the full document, which is very inefficient.  You may want to add other fields to the index to make it covering, and then project to those fields, e.g. index on {field: 1, other: 1, fields: 1} and then make sure you project in your query like db.foo.find({field: "val"}, {field: 1, other: 1, fields: 1}).

Alternatively, you can use a custom primary key of {field: 1, _id: 1} if these are your most common queries, or instead just make the secondary key on {field: 1} a clustering key.

If you want more help, it would be great if you could provide your server configuration, collection schema (db.foo.getIndexes() is fine) and stats (db.foo.stats()), realistic sample queries and cardinality, and output of db.serverStatus().ft.


Why is LIKE producing different results than my regex?

The forward slash is just a regular character in your regular expression, it is not a delimiter like it would be in Perl.  So it will only match strings which have literal forward slashes in them.


SQL Server Failover Cluster Instances: why current vote is 0?

As i checked the Nodes, i noticed that: Both are Up, assigned vote = 1. But one has current vote = 1, the other one has current vote = 0.

That's dynamic quorum kicking in with a 2 node cluster and no witness. I definitely wouldn't continue to run this way, either add another node or add a witness.

  If i set a file share witness from 3rd server (the one is outside of cluster and used to manage), the node with current vote = 0 turned to 1.

This is due to dynamic quorum and dynamic witness kicking in. Dynamic witness says if there are an even number of possible votes, give the witness a vote. If there are an odd number, take the vote away.

Since you have two nodes each with a possible vote (assigned = 1) then there are an even number of possible votes. Thus, dynamic witness says the witness must have a vote to make it an odd number. Dynamic quorum then says that it feels the best way to set the dynamic weights so that the cluster has the best uptime is to set all votes to 1.

Dynamic quorum is in WS2012 and newer, it can be turned off but is on by default.
Dynamic witness is in WS2012R2 and newer, it cannot be turned off.


How to find the date a login password was last changed in SQL Server 2008?

Use the PasswordLastSetTime option of the LOGINPROPERTY.

SELECT LOGINPROPERTY('YourLoginName', 'PasswordLastSetTime');


Postgres Slow Queries - Autovacuum frequency

vacuum: Clears out deleted records from disk

Not exactly. VACUUM marks rows that aren't visible to any active transaction any more as dead (and ready for re-use). It does not shrink the physical file size that represents the table, except for completely dead / empty pages at the physical end. The manual explains everything and probably better than I could recap here.

  analyse: Updates the query planner

It's officially ANALYZE, with Z, but ANALYSE is accepted as alternative spelling, too. And it updates statistics used by the query planner. Again, the manual already provides the best explanation.

  In postgres.conf ... my presumption is that even though it's commented out, it should still be on?

That's correct. Again, consider details in the manual.

With that many write operations (a few thousand records a minute) your system is in bad need of regular VACUUM / ANALYZE runs. You have read the manual by now, so you understand the consequences. If your table ...

  hadn't been autovacuumed for more than a week.

... then that's bad for multiple reasons. Also consider @Daniel's answer how this may have transpired with a huge table like yours. Or maybe the high load constantly locks the table and never lets VACUUM do its work. Again, it's all documented in the manual. Here is a related case with good answers how to tune settings:

Aggressive Autovacuum on PostgreSQL

Remember that you can have per-table settings (STORAGE parameters) to fine tune for special needs of a special table and leave the rest of the system alone.

If you are mostly updating recently inserted rows, a FILLFACTOR below 100 may be very helpful. You can compact the table (once) with CLUSTER or pg_repack an then set the FILLFACTOR below 100. And for huge tables it may also help to set a higher STATISTICS target for key columns with irregular data distribution.

Configuring PostgreSQL for read performance
Are regular VACUUM ANALYZE still recommended under 9.1?

Also, if old rows are updated seldom, partitioning might be a good solution, to treat old sections differently. It really depends on the complete picture ...

Also, don't forget indices, those can get bloated, too. Only keep indices that you actually need.

To see a count of dead and live tuples:

What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables


SQL Server: efficient query with (double) linked servers

Since you know exactly the T-SQL you'd like to run on server 'B', why not just execute that query remotely, at server 'B', like this:

DECLARE @cmd nvarchar(max);
SET @cmd = N'SELECT t.c1
    , t.c2
    , t.c3
FROM OPENQUERY (SERVER_C, N''SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x AND mykey=27;'') t;
';
EXEC [SERVER_B].tempdb.sys.sp_executesql @cmd;

That will cause server 'B' to query server 'C' with all the parameters you want, and should be about as quick as you can get.


Stored procedure to validate xml content against dtd

It is not possible to validate XML against a DTD file using SQL Server.

You can validate the XML with a XSD stored in a XML Schema Collection using a strong typed XML variable

DECLARE @x xml(YourXMLSchemaCollectionName);
SET @X = 'YourXML';


Oracle 12cR1 - Duplicate Database With No Resetlogs

So you described Data Guard with a standby database. If you want a standby database, tell RMAN to create a standby database:
duplicate target database for standby from active database;


Automate: importing xls to table (without microsoft excel)

You don't need to install Excel on the server to import with SSIS, you only need the "Access Database Engine", which is simply the data access layer:

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

We definitely don't have Excel installed on our database server, and Excel imports work just fine with this.


Azure SQL Database "Login failed for user" contained users.

It looks like you mixed a few different steps from the Azure article. Many of these Azure articles contain lots of great information but the layout is borderline horrible. Topics just blend into each other making it confusing for most readers (DB firewall configuration is another commonly confused doc).

If you want to use contained DBs with contained users, your create user statement should be something like

CREATE USER MyContainedUser01 WITH password='MyL@m3P@$$0rd';

as described in https://azure.microsoft.com/en-gb/documentation/articles/sql-database-manage-logins/#managing-contained-database-users

When you create a user mapped to a login as per your sample script, that's a regular, non-contained user. With that, you need to connect using the login which then maps to the user.


Get rows that exist exactly once per day for a given period

select did from foobar2 where
    date between '2019-01-01' and '2019-01-03'
    group by did
    having count(distinct date)=3 and count(date)=3;

If it has at least one of each date, but more than one of some, then count(date) will be > 3.  If it has less than one for some date, then count(distinct date) will be < 3.

  I also wish to get the count, which would be 2 in this case

Figuring out that B and D have a cardinality of 2 seems pretty easy.  Does that really need to be done inside the query?  If so, use a window function

select did, count(*) over () from foobar2 where
    date between '2019-01-01' and '2019-01-03'
    group by did
    having count(distinct date)=3 and count(date)=3;


Why does imported txt table consume so much space

There is a metadata storage overhead for columns in SQL Server that you do not have to pay for in a flat file. Using compression can reduce this overhead. From Row Compression Implementation:

  It reduces the metadata overhead that is associated with the record. This metadata is information about columns, their lengths and offsets. In some cases, the metadata overhead might be larger than the old storage format.

Try compressing your table with row or page compression and see if that reduces the space required in SQL Server.

ALTER TABLE table1 REBUILD WITH (DATA_COMPRESSION = ROW);

In my experience, compression can both improve and degrade query performance. It depends on the data and the query that you're running. It's possible that you'll need to make a trade off between space and query performance.


Table Partition Switch Problem SQL Server

You've created partitioned indexes but haven't partitioned the underlying table by creating a partitioned clustered index.

Note that the partitioning column must be part of the key for partitioned unique indexes. The implication here is that you need to add the id_file column to the primary key so that the partitioned table is aligned, which is a requirement in order to use SWITCH.

For partitioned non-unique non-clustered indexes, the partitioning column doesn't need to be part of the key. It will be implicitly included as the row locator in the index leaf nodes, just like a clustered index key.


Incorrect environment. ORACLE_HOME for grid should be pointing to grid directory and ORACLE_SID for ASM should be +ASM.

Try this:

export ORACLE_HOME=$GRID_HOME
export $ORACLE_SID=+ASM
export PATH=$GRID_HOME/bin:$PATH

– Kumar


SQL Server Plan cache size only 4GB on a server with 500GB RAM

SQL Server uses memory only as it needs to... with in-memory technology as the exception, it won't jump to using up all of the memory indicated in the configuration for max server memory (or even min server memory), until you actually request data or plans that require memory.

So if SQL Server has not been up for a long time (maybe you restart every night, or recently installed patches), it won't use that memory until your workload makes it happen. And it may never happen, depending on your available memory and the size of your data. As an example, if you allocate 30GB to SQL Server, but the sum of your data is only 10GB, you're unlikely to see all of your max server memory ever being used, regardless of uptime.

Several of these aspects of SQL Server memory management are widely misunderstood.


How to set variables to multiple values (that are integers)?

You can use a table-valued parameter.

CREATE TYPE dbo.Facilities AS TABLE
(
  FacilityID TINYINT PRIMARY KEY
);

Then your stored procedure can say:

CREATE PROCEDURE dbo.whatever
  @FacilityDivisionID TINYINT,
  @Facilities dbo.Facilities READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ... FROM dbo.wherever AS w
    INNER JOIN @Facilities AS f
    ON f.FacilityID = w.FacilityID
  WHERE w.FacilityDivisionID = @FacilityDivisionID;
END
GO

Then you can call it from T-SQL like this:

DECLARE @t dbo.Facilities;
INSERT @t VALUES(1),(2),(3),(4),(5);
DECLARE @d TINYINT = 6;
EXEC dbo.whatever @FacilityDivisionID = @d, @Facilities = @t;

Or from C# using a DataTable and a parameter type as Structured. You can search to find plenty of examples of using TVPs from application code.


Impossible to log errors after dooming a transaction within an INSERT-EXEC statement

There is a way to first write a 'custom' log entry by using raiserror with LOG. It is going to raise the error before the throw, and afterwards we will read from the log using xp_readerrorlog.

It is for sure not going to be pretty, and not really suited for production.

But here it goes:

  IF OBJECT_ID('TempLog') IS NULL
    CREATE TABLE TempLog
    (
         RowNumber INT NOT NULL IDENTITY(1, 1)
        ,LogMessage VARCHAR(MAX) NOT NULL
        ,TimestampUtc DATETIME NOT NULL DEFAULT(GETUTCDATE())
    )
GO
TRUNCATE TABLE TempLog
GO
IF OBJECT_ID('Failer') IS NOT NULL
    DROP PROCEDURE Failer
GO
CREATE PROCEDURE Failer
AS BEGIN
    BEGIN TRANSACTION;
    SELECT 1 / 0
    --unmatched COMMIT for fun
END
GO
IF OBJECT_ID('Wrapper') IS NOT NULL
    DROP PROCEDURE Wrapper
GO
CREATE PROCEDURE Wrapper
AS BEGIN
    BEGIN TRY
        EXEC Failer
        SELECT 'success'
    END TRY
    BEGIN CATCH
        BEGIN TRY
            INSERT INTO TempLog(LogMessage) VALUES ('test 1')
        END TRY
        BEGIN CATCH
            PRINT 'failed test 1: ' + ERROR_MESSAGE()
        END CATCH
        BEGIN TRY
            IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
            INSERT INTO TempLog(LogMessage) VALUES ('test 2')
        END TRY
        BEGIN CATCH
            PRINT 'failed test 2: ' + ERROR_MESSAGE()
        END CATCH
        BEGIN TRY
            IF XACT_STATE() = -1
                ROLLBACK TRANSACTION;
            IF XACT_STATE() = 1
                COMMIT TRANSACTION;
            INSERT INTO TempLog(LogMessage) VALUES ('test 3')
        END TRY
        BEGIN CATCH
            PRINT 'failed test 3: ' + ERROR_MESSAGE()
        END CATCH
 -- Create a custom error message with an identifier for reading from the log (WrapperIssue)
 DECLARE @ErrorMessage NVARCHAR(4000)
 DECLARE @ErrorSeverity INT
 SELECT
  @ErrorMessage = (cast(ERROR_MESSAGE() as nvarchar(2000)) + N' WrapperIssue'),
  @ErrorSeverity = ERROR_SEVERITY()

 -- RaisError with logging to the SQL Server LOG
 RAISERROR(@ErrorMessage, @ErrorSeverity, 1) WITH LOG
        ;THROW
    END CATCH
END
GO
DECLARE @Result TABLE(RowNumber INT IDENTITY(1, 1), OtherStuff VARCHAR(MAX) NULL)
INSERT INTO @Result(OtherStuff)
EXEC Wrapper

SELECT [@Result]='', *
FROM @Result

IF XACT_STATE() = -1
    ROLLBACK TRANSACTION;
IF XACT_STATE() = 1
    COMMIT TRANSACTION;
GO
SELECT [Trancount] = @@TRANCOUNT
SELECT * FROM TempLog

-- Read from the rror log using a text filter + only for the last minute
-- Credits for query: https://sqlserver-help.com/2014/12/10/sql-internals-useful-parameters-for-xp_readerrorlog/
Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,    -- Don't know
        @LogType = 1,          -- 2 = SQL SERVER
        @Filter1Text='WrapperIssue',     -- First Text Filter
        @Filter2Text=NULL,     -- Second Text Filter
        @FirstEntry=dateadd(minute,-1,getdate()) ,      -- Start Date
        @LastEntry=NULL        -- End Date
Create table #readerrorlog(LogDate datetime2,
                           Processinfo nvarchar(2000),
                           text nvarchar(4000))
-- Get the data from the error log
insert into #readerrorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'DESC', @InstanceName

-- Finally insert into the TempLog table
INSERT INTO TempLog(LogMessage)
select Text from #readerrorlog

select * From TempLog

drop table #readerrorlog


How to relate two rows in the same table

What you have designed is good. What needs to be added is a constraint to make the relationship directionless. So, you cannot have a (1,5) row without a (5,1) row being added as well.

This can be accomplished* with a self referencing constraint on the bridge table.

*: it can be accomplished in Postgres, Oracle, DB2 and all DBMS that have implemented foreign key constraints as the SQL standard describes (deferred, e.g. checked at the end of transaction.) Deferred checking is not really needed anyway, as in SQL-Server that checks them at the end of statement and this construction still works. You cannot do this in MySQL because "InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row".

So, in Postgres the following will match your requirements:

CREATE TABLE x
(
  x_id SERIAL NOT NULL PRIMARY KEY,
  data VARCHAR(10) NOT NULL
);

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    FOREIGN KEY (x_id2, x_id1)
    REFERENCES bridge_x (x_id1, x_id2)
);

Tested at: SQL-Fiddle

If you try to add a row (1,5):

INSERT INTO bridge_x VALUES
(1,5) ;

It fails with:

  ERROR: insert or update on table "bridge_x" violates foreign key constraint "x_x_directionless"
  Detail: Key (x_id2, x_id1)=(5, 1) is not present in table "bridge_x".:
  INSERT INTO bridge_x VALUES (1,5)

Additionally, you can add a CHECK constraint if you want to forbid (y,y) rows:

ALTER TABLE bridge_x
  ADD CONSTRAINT x_x_self_referencing_items_not_allowed
    CHECK (x_id1 <> x_id2) ;

There are other ways to implement this as you mention, like storing only one direction of the relationship (in one row, not two) by forcing the lower id in x_id1 and the higher id in the x_id2 column. It looks easier to implement, but usually leads to more complex queries later:

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    CHECK (x_id1 <= x_id2)                       -- or "<" to forbid `(y,y)` rows
);


How to convert binary string value from error log?

That error indicates that you are inserting a value in a column that is expected to be unique. The error translates any values in the range 0x20 to 0x7E into their easier-to-read form, and uses an \x prefix for others. So the value you are inserting probably looks like this:

0x9F989E726E068B4E978DB541B56512C92D9CE11A695C

(I got that by keeping any pair of values appearing immediately after an \x, and converting any other values (r, n, N, A, e, -, i, and \) to their ASCII hex values. I assume the \ is a literal, but I would have expected it to be escaped.)


A little clarification on how 'LIMIT' works

mysql should optimize a query like your example and not do a full table scan just to retrieve 10 records.  Adding an order by without an index before the limit 10 would perform a full table scan.

Are you sure that your "table" is really a table and not a view?  You might be selecting from a slow view.


Was Management Studio removed from SQL Server 2016 installation media?

SQL Server Management Studio is now available on its own page:
SQL Server Management Studio - Release Notes

  Welcome to our generally-available release of SQL Server Management Studio! This release of SQL Server Management Studio (SSMS) is a stand-alone install outside of the SQL Server release. Our goal is to update this frequently with new functionality, fixes, and support for the newest features in SQL Server and Azure SQL Database [...]

  Download SQL Server Management Studio
  Previous SQL Server Management Studio Releases

It is free and no SQL Server licence is required anymore:

  This generally available release of SSMS is free and does not require a SQL Server license to install and use.

It is now updated on a monthly basis since June 2016: Announcing SQL Server Management Studio – July 2016 Release


SQL Server can't see subst/virtual drive

It's because subst just gives you a drive letter alias for your current session; it's not a server level thing.  This is not going to be possible using this command.  Use the real path instead.


SQL Server detected 2 sockets with 4 cores per socket but only one NUMA node

The following two articles make it clear that VMware will present a virtual NUMA configuration to the guest OS that is optimal.  Accordingly, the number of sockets that are configured on the VM are more or less irrelevant as to the number of NUMA nodes.  If the actual hardware has at least 8 cores in a NUMA node, then VMware is only going to present one NUMA node to a guest that has 8 CPUs, regardless of the VM's socket count.

Does corespersocket Affect Performance?

Virtual Machine vCPU and vNUMA Rightsizing – Rules of Thumb

Determining NUMA node boundaries for modern CPUs contains two helpful quotes that show that, even on hardware, number of sockets is not always equal to number of NUMA nodes:

When we originally did this analysis, the common multi-core processors were dual core or at most quad core.  On these chips, the hardware manufacturers divided the NUMA boundaries into cores, rather than sockets.  But it appears that that configuration is not the same for the larger multi-core (6, 12, etc.) chips.

and

In short, don’t assume that your NUMA boundaries are divided into cores – it very much depends on your specific CPU architecture.

I also ran across an TechNet article,Soft-NUMA (SQL Server),  stating that each socket is usually represented by one NUMA node, but it doesn't explain when it wouldn't be.

You also might check the BIOS of the hardware to ensure that NUMA is enabled.  I would be surprised if it was disabled, but I've learned through my career to never assume that someone didn't do something completely irrational.

Answers to your questions:

Given the evidence at hand, yes since both SQL Server and Windows are telling you that there is only one NUMA node, and we have some documentation from sources at VMware that it will present the optimal vNUMA configuration to the guest, which will be one NUMA node as long as the guest is configured with less than or equal to the amount of cores in the hardware sockets, and the number of sockets presented to the guest is irrelevant.
If the system architecture is telling the OS that there is one NUMA node, then there is one NUMA node and that is best because all CPUs are using the same memory.  If it were actually two NUMA nodes but SQL Server only sees one, then yes, it would impact performance for anything that runs parallel.
The CPU architecture, which in turn dictates the mainboard architecture.  On VMs, the NUMA configuration presented to the guest is determined by virtualization host (VMware, Hyper-V, etc.).


How can I use Invoke-SqlCmd with variables to output the SQL command only or some similar method?

I don't think invoke-sqlcmd can do thi.  Regular sqlcmd.exe has the -e parameter to output the batches, although it will not output the "GO"
.  EG

sqlcmd -e -i restore.sql -v database="foo" databaseBak="bk" > Restore.final.sql

You can put SET NOEXEC ON at the top, or just point it at an dummy instance to generate the script.

Or something like:

sqlcmd -e -Q "set noexec on;`n:r restore.sql" -v database="foo" databaseBak="bk"


SQL Server Application paths to include in server backup?

For server backup on Windows the backup software will typically interact with the the Volume Shadow Copy Service to generate a a point-in-time consistent view of each volume regardless of changing data or open files.
Then for server-level recovery, restore based on the volume snapshots to and then perform SQL Server database RESTORE using backups taken sometime later.
If you don't have the ability to protect whole volumes and open files, then the server backup is not going to be much use in any recovery scenario.


Unfortunately DBMS_CRYPTO in Oracle 11.2 only supports SHA1 (documentation link), which is 160-bit. .

DBMS_CRYPTO in Oracle 12.1 supports SHA2 HASH_SH256 (documentation link), which does what you require.

There are some free implementations of SHA2 just a google away. This blog post, for example.

As for decrypting a hashed password? I don't think you understand hashing. Hashing is 1-way, unless you brute-force it, or use Rainbow Tables. To check if a password is correct, you would hash it and compare it to the stored hash.


How to shutdown mongodb sharded cluster temporary

stop balancing - run sh.stopBalancer() from mongos
shutdown mongos services - run db.shutdownServer() from mongos
shutdown config mongod services - run db.shutdownServer() from config server mongod
shutdown shards mongod services - run db.shutdownServer() from replica set mongod


Exclusion constraint on ENUM errors: data type dow has no default operator class for access method "gist"

This is apparently a known issue, and it was fixed in PostgreSQL 10. The new version of btree_gist (10+) has in the docs,

  btree_gist provides GiST index operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, char, varchar, text, bytea, bit, varbit, macaddr, macaddr8, inet, cidr, uuid, and all enum types.


Delete Old Backup History
You might want to have a look at this stored procedure I created some time back. Instead of entering a date it gets the current date and goes 1080 days (default) back in time and starts deleting the backup history in steps of 1 (default) up until 180 days (default).
With a large msdb database you might have a lot of info that takes a long time to delete. Entering a simple date using the sp_delete_backuphistory procedure can cause issues, because it has to go a long way back in time to delete the information from the various tables.
My stored procedure is just a wrapper which automates the task of deleting old backup history using the built-in sp_delete_backuphistory (Microsoft | SQL Docs) procedure. The advantage of my procedure is that you don't have to manually enter dates to clean up the history in large msdb database, but can use days instead and can turn on transactional handling.

    use msdb
    go

    CREATE PROCEDURE [dbo].[spdeletehistory]
    /*
    -- ==================================================================
    -- Author......:    hot2use
    -- Date........:    04-Jan-2006
    -- Version.....:    1.3
    -- Server......:    [server name here]
    -- Database....:    msdb
    -- Name........:    spdeletehistory
    -- Owner.......:    dbo
    -- Table.......:
    -- Type........:    Stored Procedure
    -- Description.:    Delete old backup history on a server
    -- History.....:    04-Jan-2006    1.0    First created
    --                  07-Dec-2005    1.1    Modified seperators
    --                  04-Jul-2006    1.2    Modified Text Output for return value
    --                                        Added Constants for defaults
    --                  25-Jan-2017    1.3    Slight modifications regarding comments
    --
    -- Editor......:    UltraEdit 11.10a (using Syntax Highlighting)
    --                    Tabstop Values = 4
    -- ==================================================================
    */

        /*input variables*/
        @iDaysBackToStart int = 0,
        @iDaysToKeep int = 0,
        @iDayStep int = 0,
        @iDebug int = 0

        /*output variables*/

    AS
    BEGIN
         /* Turn off double quotes for text strings */
        set quoted_identifier off
          /* Dont return the count for any statment */
        set nocount on
         /* debugging configuration */
        declare @debug int
         /* debug settings
        1 = turn on debug information
        2 = turn off all possible outputs
        4 = turn on transaction handling
         e.g.: Adding an @iDebug paramter of 6 will...
        ... turn on transaction handling (4)
        ... turn off all possible output information (2)
         e.g.: Adding an @iDebug value of 1 will turn on all debugging information
        */

        set @debug = @iDebug

        /* day constants */
        declare @iDaysBackToStart_CONST int
        declare @iDaysToKeep_CONST int
        declare @iDayStep_CONST int

        /* constant settings
        set the defaults here instead of in the input variables
        it makes for easier changing, when comparing the default values in the code
        */
        set @iDaysBackToStart_CONST = 1080
        set @iDaysToKeep_CONST = 180
        set @iDayStep_CONST = 1

        if @iDaysBackToStart = 0
            BEGIN
                set @iDaysBackToStart = @iDaysBackToStart_CONST
            END

        if @iDaysToKeep = 0
            BEGIN
                set @iDaysToKeep = @iDaysToKeep_CONST
            END

        if @iDayStep = 0
            BEGIN
                set @iDayStep = @iDayStep_CONST
            END
        /*
        return values
        0 Successful execution.
        1 Required parameter value not specified.
        2 Invalid parameter value specified.
        3 not defined
        4 not defined
        */
        declare @iRetVal int

        declare @dtDateToDelete datetime
        declare @dtCurrentDate datetime
        declare @dtStartDate datetime
        declare @dtStopDate datetime
        declare @vSQL nvarchar(2000)

        if @debug & 1 = 1  print 'Checking variables...'
        if (@iDaysToKeep < @iDaysToKeep_CONST)
            BEGIN
                PRINT 'Invalid parameter value specified (1)'
                PRINT 'e.g. spdeletehistory @iDaysBackToStart = 1080, @iDaysToKeep = 180, @iDayStep = 1 '
                PRINT 'The default values are the above values'
                PRINT '@iDaysBackToStart must be >= ' + convert(varchar(20), @iDaysBackToStart_CONST) + ''
                PRINT '@iDaysToKeep must be >= ' + convert(varchar(20), @iDaysToKeep_CONST) + ''
                PRINT '@iDayStep must be > ' + convert(varchar(20), @iDayStep_CONST - 1) + ''
                PRINT ''
                RETURN(2)
            END
        else if (@iDaysToKeep < @iDaysToKeep_CONST) or (@iDaysBackToStart < @iDaysBackToStart_CONST)
            BEGIN
                PRINT 'Invalid parameter value specified (2)'
                PRINT 'e.g. spdeletehistory @iDaysBackToStart = 1080, @iDaysToKeep = 180, @iDayStep = 1 '
                PRINT 'The default values are the above values'
                PRINT '@iDaysBackToStart must be >= ' + convert(varchar(20), @iDaysBackToStart_CONST) + ''
                PRINT '@iDaysToKeep must be >= ' + convert(varchar(20), @iDaysToKeep_CONST) + ''
                PRINT '@iDayStep must be > ' + convert(varchar(20), @iDayStep_CONST - 1) + ''
                PRINT ''
                RETURN(2)
            END
        else
            BEGIN
                if @debug & 1 = 1  print 'Checking if just defaults have been used...'
                if (@iDaysToKeep = @iDaysToKeep_CONST) and (@iDayStep = @iDayStep_CONST) and (@iDaysBackToStart = @iDaysBackToStart_CONST)
                    BEGIN
                        if @debug & 2 <> 2
                            BEGIN
                                PRINT ''
                                PRINT 'Running with default parameters'
                                PRINT ''
                                PRINT '@iDaysBackToStart ='  + convert(varchar(20), @iDaysBackToStart_CONST) + ''
                                PRINT '@iDaysToKeep = ' + convert(varchar(20), @iDaysToKeep_CONST) + ''
                                PRINT '@iDayStep = ' + convert(varchar(20), @iDayStep_CONST) + ''
                                PRINT ''
                            END
                    END
                -- if @debug & 1 = 1
                if @debug & 1 = 1  print 'Settings date variables...'
                select @dtCurrentDate = getdate()
                if @debug & 1 = 1  print '@dtCurrentDate..: ' + convert(varchar(60), @dtCurrentDate, 20)
                select @dtStartDate = @dtCurrentDate - @iDaysBackToStart
                if @debug & 1 = 1  print '@dtStartDate....: ' + convert(varchar(60), @dtStartDate, 20)
                select @dtStopDate = @dtCurrentDate - @iDaysToKeep
                if @debug & 1 = 1  print '@dtStopDate.....: ' + convert(varchar(60), @dtStopDate, 20)
                select @dtDateToDelete = @dtStartDate
                if @debug & 1 = 1  print '@dtDateToDelete.: ' + convert(varchar(60), @dtDateToDelete, 20)

                if @debug & 1 = 1  print 'Starting to loop...'
                while @dtDateToDelete < @dtStopDate begin

                    set @vSQL = 'msdb.dbo.sp_delete_backuphistory ''' + convert(varchar(60), @dtDateToDelete, 20) + ''''
                    if @debug & 1 = 1  print '@vSQL.........: ' + @vSQL
                    if @debug & 4 = 4
                        BEGIN
                            begin tran john
                        END
                    exec @iRetVal = sp_executesql @vSQL
                    if @iRetVal <> 0
                        BEGIN
                            /* Turn on double quotes for text strings */
                            set quoted_identifier on

                             /* Return the count for any statment */
                            set nocount off

                            /* If transactions have been turned on then rollback if failed */
                            if @debug & 4 = 4
                                BEGIN
                                    rollback tran john
                                END

                            /* If general output has not been turned off print output*/
                            if @debug & 2 <> 2
                                BEGIN
                                    PRINT 'Return Value: ' + convert(varchar(100), @iRetVal) + ' at current time: ' + convert(varchar(20),getdate())
                                end
                            RETURN(@iRetVal)
                        END

                    /* If transactions have been turned on then commit on success */
                    if @debug & 4 = 4
                        BEGIN
                            commit tran john
                        END

                    /* If general output has not been turned off print output*/
                    if @debug & 2 <> 2
                        BEGIN
                            PRINT 'Return Value: ' + convert(varchar(100), @iRetVal) + ' at current time: ' + convert(varchar(20),getdate())
                        end
                    /* Get next date to delte history data */
                    select @dtDateToDelete = @dtDateToDelete + @iDayStep
                end
            END
        /* Turn on double quotes for text strings */
        set quoted_identifier on
         /* Return the count for any statment */
        set nocount off
        /* If general output has not been turned off print output*/
        if @debug & 2 <> 2
            BEGIN
                print 'Finished.'
            END
        RETURN(0)
    END
    GO

Once you have the procedure in your msdb database, then run the query via:
msdb.dbo.spdeletehistory

The stored procedure will then run with the default values:
@iDaysBackToStart = 1080
@iDaysToKeep = 180
@iDayStep = 1

You can change these values to meet your requirements. For example:
spdeletehistory @iDaysBackToStart = 2000, @iDaysToKeep = 180, @iDayStep = 2

If you want lower values than the defaults, then you will have to modify the @i..._CONST values in the stored procedure, as the defaults are the lowest values possible.
If you want to see what happens, then set the @iDebug parameter (bitwise):
/* debug settings
1 = turn on debug information
2 = turn off all possible outputs
4 = turn on transaction handling
e.g.: Adding an @iDebug paramter of 6 will...
... turn on transaction handling (4)
... turn off all possible output information (2)
e.g.: Adding an @iDebug value of 1 will turn on all debugging information
*/

E.g. turning on @iDebug = 4 will delete the history in transactions which can reduce the overall impact on the msdb database.
Example Run
Running the following command:
msdb.dbo.spdeletehistory @iDaysBackToStart = 1080, @iDaysToKeep = 180, @iDayStep = 1, @iDebug = 7

Produces the following output:

Checking variables...
Checking if just defaults have been used...
Settings date variables...
@dtCurrentDate..: 2020-07-03 13:25:20
@dtStartDate....: 2017-07-19 13:25:20
@dtStopDate.....: 2020-01-05 13:25:20
@dtDateToDelete.: 2017-07-19 13:25:20
Starting to loop...
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2017-07-19 13:25:20'
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2017-07-20 13:25:20'
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2017-07-21 13:25:20'
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2017-07-22 13:25:20'
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2017-07-23 13:25:20'
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2017-07-24 13:25:20'
....
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2020-01-04 13:25:20'

This should clear up old backup information in the msdb database.
However, it will not shrink the msdb database.  That's a different issue, which can be achieved with ...
DBCC SHRINKFILE
The DBCC SHRINKFILE (Microsoft | SQL Docs) command can be used to reclaim space that was once used in a database. It is not recommended for general use, as a database will normally reclaim the released space again in most cases, but seeing as you have a rather large msdb database....
USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBData' , 0, TRUNCATEONLY) -- release unused space
GO

USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBData' , 96) -- shrink mdf data file to 96 MB size
GO

Please be aware that if the msdb database is under pressure that you might not be able to actually shrink the database in one go.
You might not even be able to shrink the database down to 98 MB. If you right click the msdb database and then select: TASK... | Shrink ... | Files ... you will be presented with a dialog that will tell you the minimum size, that the database file(s) can be shrunk to. Take that value and add it to the second command:
USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBData' , 1024) -- shrink mdf data file to 1 GB size
GO

Good luck.


I found the answer to be,

CASE (SELECT RoleType FROM user)
    WHEN 'standard' THEN
        GRANT PROXY ON 'standard_user'@'localhost' TO CURRENT_USER();
    WHEN 'admin' THEN
        GRANT PROXY ON 'admin_user'@'localhost' TO CURRENT_USER();
END CASE;


PostgreSQL: Generate a series of dates for each group in a table

1. CROSS JOIN, LEFT JOIN LATERAL to subquery
SELECT a.user_id, COALESCE(b.balance, 0) AS balance, d.as_of_date
FROM   (
   SELECT d::date AS as_of_date  -- cast to date right away
   FROM   generate_series(timestamp '2016-01-01', '2016-01-03', interval '1 day') d
   ) d
JOIN   accounts a ON a.create_date <= d.as_of_date
LEFT   JOIN LATERAL (
   SELECT balance
   FROM   balances
   WHERE  user_id = a.user_id
   AND    as_of_date <= d.as_of_date
   ORDER  BY as_of_date DESC
   LIMIT  1
   ) b ON true
ORDER  BY a.user_id, d.as_of_date;

Returns your desired result - except that as_of_date is an actual date, not a timestamp like in your example. That should be more appropriate.
Users that are created already, but don't have any transactions, yet, are listed with a balance of 0. You did not define how to deal with the corner case.
Rather use timestamp input for generate_series():

Generating time series between two dates in PostgreSQL

It's crucial for performance that you back this up with a multicolumn index:
CREATE INDEX balances_multi_idx ON balances (user_id, as_of_date DESC, balance);

We have had a very similar case on SO just this week:

Aggregating the most recent joined records per week

Find more explanation there.
2. CROSS JOIN, LEFT JOIN , window functions
SELECT user_id
     , COALESCE(max(balance) OVER (PARTITION BY user_id, grp
                                   ORDER BY as_of_date), 0) AS balance
     , as_of_date
FROM  (
   SELECT a.user_id, b.balance, d.as_of_date
        , count(b.user_id) OVER (PARTITION BY user_id ORDER BY as_of_date) AS grp
   FROM   (
      SELECT d::date AS as_of_date  -- cast to date right away
      FROM   generate_series(timestamp '2016-01-01', '2016-01-03', interval '1 day') d
      ) d
   JOIN   accounts a ON a.create_date <= d.as_of_date
   LEFT   JOIN balances b USING (user_id, as_of_date)
   ) sub
ORDER  BY user_id, as_of_date;

Same result. If you have the multicolumn index mentioned above and can get index-only scans out of it, the first solution is most probably faster.
The main feature is the running count of values to form groups. since count() does not count NULL values, all dates without balance fall into the same group (grp) as the most recent balance. Then use a simple max() over the same window frame extended by grp to copy the last balance for dangling gaps.
Related:

Select longest continuous sequence
How to get max sequence of rows?


postgres simple number to string mapping

IMHO the easiest way is by using a lookup table.

create table lk (id int, name text);

insert into lk values
(1, 'apple'),(2, 'google'),(3, 'msft'),(4, 'amazon');

create table t (id serial, lk int);
insert into t (lk) values (2),(1),(3),(4);

select
    t.id,
    lk.name
from   t
join   lk
       on lk.id = t.lk;

id | name
-: | :-----
 2 | apple
 1 | google
 3 | msft
 4 | amazon

db<>fiddle here


SQL - Sum amounts daily from non-daily data

This gives you the expected output for a given date range.  May not be the most elegant though, but it includes an example of creating a Date table on the fly, although for large date ranges it will perform poorly.

DECLARE @Accounts TABLE
    (
    ACCT_ID INT NOT NULL
    , EFFDATE DATE NOT NULL
    , AMT INT NOT NULL
    )

;WITH CTE_Values AS
    (
    SELECT ACCT_ID
        , EFFDATE
        , AMT
    FROM (VALUES (1112, '12/30/2018', 500)
                , (1112, '12/25/2018', 675)
                , (1112, '12/21/2018', 187)
                , (1112, '12/19/2018', 332)
                , (1112, '12/10/2018',  92)
                , (1112, '12/3/2018', 300)
                , (2223, '12/29/2018', 225)
                , (2223, '12/20/2018', 100)
                , (2223, '12/11/2018', 525)
                , (3334, '12/31/2018',1000)
                , (3334, '12/20/2018', 600)
                , (3334, '12/14/2018', 490)
                , (3334, '12/1/2018', 350)
                , (4445, '12/20/2018', 300)
                , (4445, '12/19/2018', 250)
                , (4445, '12/12/2018', 200)
                , (4445, '12/9/2018', 150)
                , (4445, '12/6/2018', 100)
                , (4445, '12/1/2018',  50)
        ) AS P (ACCT_ID, EFFDATE, AMT)
    )
INSERT INTO @Accounts
(ACCT_ID, EFFDATE, AMT)
SELECT ACCT_ID, EFFDATE, AMT
FROM CTE_Values

/***************************************************/

DECLARE @EndDate DATE = '12/31/2018'
DECLARE @StartDate DATE = DATEADD(DAY, -5, @EndDate)

;WITH CTE_Dates AS
    (
    SELECT 0 AS Number
        , @EndDate AS TheDay
    UNION ALL
    SELECT D.Number + 1 AS Number
        , DATEADD(DAY, (D.Number + 1) * -1, @EndDate) AS TheDay
    FROM CTE_Dates AS D
    WHERE DATEADD(DAY, (D.Number + 1) * -1, @EndDate) >= @StartDate
    )
, CTE_EveryAccountEveryDay AS
    (
    SELECT A.ACCT_ID
        , D.TheDay
    FROM CTE_Dates AS D
        CROSS JOIN (SELECT DISTINCT ACCT_ID FROM @Accounts) AS A
    )
, CTE_AccountBalances AS
    (
    SELECT E.ACCT_ID
        , E.TheDay
        , A.AMT
        , PriorAMT = LAG(AMT) OVER (PARTITION BY E.ACCT_ID ORDER BY E.TheDay)
    FROM CTE_EveryAccountEveryDay AS E
        LEFT OUTER JOIN @ACcounts AS A ON A.ACCT_ID = E.ACCT_ID AND A.EFFDATE = E.TheDay
    )
, CTE_WrappingItUp AS
    (
    SELECT ACCT_ID
        , TheDay
        , AMT = COALESCE(AMT, PriorAMT, (SELECT TOP (1) AMT FROM @Accounts WHERE ACCT_ID = B.ACCT_ID AND EFFDATE <= B.TheDay ORDER BY EFFDATE DESC))
    FROM CTE_AccountBalances AS B
    )
SELECT TheDay AS EFFDATE
    , SUM(AMT) AS AMT_Total
FROM CTE_WrappingItUp
GROUP BY TheDay
ORDER BY TheDay DESC
OPTION (MAXRECURSION 0)


Delete backups from old incarnation in rman

I couldn't find any clause to the REPORT or LIST commands which would allow you to specify the incarnation of interest.

You could however correlate your latest incarnation reset time with backup completion time to identify the backups completed before your current incarnation began.

First, let's set the environment variables so that RMAN output also includes time.

$ NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
$ NLS_TIMESTAMP_FORMAT='DD.MM.YYYY HH24:MI:SS'
$ export NLS_DATE_FORMAT
$ export NLS_TIMESTAMP_FORMAT

Now let's examine which incarnations we have in RMAN repository.

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- ---     ---------- ----------
1       1       OCP      2737702586       PARENT  1          25.08.2013 05:18:32
2       2       OCP      2737702586       PARENT  635002     05.03.2014 10:49:33
3       3       OCP      2737702586       PARENT  750347     13.03.2014 18:08:15
4       4       OCP      2737702586       PARENT  750727     18.03.2014 11:37:57
5       5       OCP      2737702586       CURRENT 1137478    05.08.2014 08:19:38

It's shown in the output that our current incarnation's reset time is 5 August 2014 8:19 AM (05.08.2014 08:19:38).

I have some backups completed before and after the current incarnation began.

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
28      B  F  A DISK        05.08.2014 08:21:56 1       1       YES        TAG20140805T082051
29      B  F  A DISK        05.08.2014 08:12:43 1       1       YES        TAG20140805T081243
30      B  F  A DISK        05.08.2014 08:10:36 1       1       YES        TAG20140805T081036
31      B  A  A DISK        05.08.2014 08:21:59 1       1       YES        TAG20140805T082159
32      B  F  A DISK        05.08.2014 08:13:48 1       1       YES        TAG20140805T081243
33      B  A  A DISK        05.08.2014 08:20:49 1       1       YES        TAG20140805T082048
34      B  F  A DISK        05.08.2014 08:20:51 1       1       YES        TAG20140805T082051
35      B  F  A DISK        05.08.2014 08:11:51 1       1       YES        TAG20140805T081036
36      B  A  A DISK        05.08.2014 08:20:48 1       1       YES        TAG20140805T082048

Now we can list backups which were completed before the current incarnation began.

RMAN> list backup summary
2> completed before
3> "to_date('05.08.2014 08:19:38', 'dd.mm.yyyy hh24:mi:ss')";

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
29      B  F  A DISK        05.08.2014 08:12:43 1       1       YES        TAG20140805T081243
30      B  F  A DISK        05.08.2014 08:10:36 1       1       YES        TAG20140805T081036
32      B  F  A DISK        05.08.2014 08:13:48 1       1       YES        TAG20140805T081243
35      B  F  A DISK        05.08.2014 08:11:51 1       1       YES        TAG20140805T081036

As you can see, only backups with completion time before the current incarnation's reset time are shown in the output.

After you narrowed down the backups to the ones whose completion time is before your current incarnation's reset time using COMPLETED subclause of the LIST command, you can specify the same condition to the DELETE command.

RMAN> delete backup
2> completed before
3> "to_date('05.08.2014 08:19:38', 'dd.mm.yyyy hh24:mi:ss')";

There are also AFTER and BETWEEN specifiers to the COMPLETED subclause. See the section Listing Selected Backups and Copies in Database Backup and Recovery User's Guide. An example of BETWEEN specifier usage:

RMAN> list copy of datafile 2 completed between '10-DEC-2002' and '17-DEC-2002';

UPDATE

You can switch between incarnations using RESET DATABASE TO INCARNATION n command (check the scenarios of usage), but you'll always end up with the same output from the LIST and REPORT commands with respect to backup sets, no matter which incarnation you switch to.

You can search Database Backup and Recovery Reference for the word "incarnation", to see when you can specify the incarnation and to which RMAN commands.


Difference between DataLinks and External tables

If I remember correctly, Data Links Manager attempted to manage external files that were referenced in a database, rather than providing relational access to the data in those files. Think of an image or PDF document that would be related to a record. You could store those documents in the filesystem (instead of in a LOB inside the database). The files could be accessed outside of the database, but Db2 would also (supposedly) provide some control to prevent files from being deleted or moved.

It was, perhaps, closer in function to products like Content Manager OnDemand or Filenet.


Does partitioning a MySQL table also increase index creation performance?

Partitioning usually rather decreases ALTER TABLE speed. Index and data is stored in separate files for each partition (a separate .ibd file for INNODB if innodb_file_per_table=1), so that you might make alter in parallel. But partitioning add an extra processing step to choose the partition for storing data/index, thus the whole operation is made slower.

mysql> create table alter_test (id int primary key auto_increment, v   char(20));
mysql> load data infile '/tmp/1' into table alter_test;
Query OK, 500000 rows affected (9.98 sec)

mysql> alter table alter_test add index i_test(v);
Query OK, 0 rows affected (4.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table alter_test (id int primary key auto_increment, v  char(20)) partition by key(id) partitions 10;
mysql> load data infile '/tmp/1' into table alter_test;
Query OK, 500000 rows affected (9.98 sec)

mysql> alter table alter_test add index i_test(v);
Query OK, 0 rows affected (4.83 sec)

As for the shared lock for ALTER TABLE, all partitions are locked in the same time, so partitioning does not help.

Hints:

Upgrade to  MySQL 5.6, since it supports online alter table.
Use scripts such as oak-online-alter-table or pt-online-schema-change as a workaround for MySQL version < 5.6


postGIS schema qualifier - best practice

A schema is like a namespace in programming. It lets you have different things with the same name.

For example, you might have a function foo() in your public schema. If PostGIS has a function foo() as well, that wouldn't work without name qualification.


Msg 8985, Level 16, State 1,Could not locate file 'MyTal_Log'

I would start by saying that shrinking, be it that of data or log file, is really bad avoid it as much as possible. There is hardly anything you can benefit, specially with what you are doing.

As for your question look at the below specific error message

  Msg 8985, Level 16, State 1, Line 16 Could not locate file 'MyTal_Log' for database 'master' in sys.database_files

The query is looking for logical file named MyTal_log in master, is this the logical filename of log file of database tal ?, I believe it is not. To  confirm run below

sp_helpdb Tal

As shown in pic, select the logical name and use that name in the query, this is Tal database created on my system.

DBCC SQLPERF (Logspace)
USE master;
GO
ALTER DATABASE Tal
 SET RECOVERY FULL;
GO
BACKUP DATABASE Tal
TO DISK = 'D:\SQLServerBackups\MyTalLog.bak'
 WITH FORMAT;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TAL
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
USE tal--**database name**
GO
DBCC SHRINKFILE ('MyTal_Log', 1,TRUNCATEONLY);--add correct logical name
GO
-- Reset the database recovery model.
ALTER DATABASE TAL
SET RECOVERY FULL;
GO


What is the available SQL Server Express database size?

I haven't used SQL Express personally but from my understanding of how Microsoft's licensing works you'll be able to grow to the 10 GB limit, but then if the file tries to grow any further the operation will fail.
The limit is the allocated size of the MDF file. So technically you have about 2 GB left. Once your MDF is 10 GB, even if not all of that space is used, you're at the limit per the licensing agreement. You should still be able to use the server and fill the unused allocated space up though (without incurring any further file growths) and still be compliant.


How to give a user "create any database" permission in SQL Server?

The Server-Level Roles documentation says:

  Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

This obviously exceeds the ability to just "create any database" mentioned in the title of your question.

To add a login to the dbcreator role:

EXECUTE sys.sp_addsrvrolemember
    @loginame = N'LoginName',
    @rolename = N'dbcreator';

The process to grant only the permission CREATE ANY DATABASE is as simple as:

-- Must be in master to grant server-scoped permissions
USE master;
GRANT CREATE ANY DATABASE TO LoginName;

It is generally seen as best practice to grant only the minimum permissions required.


EventID 1196 | SQL Cluster & FailoverClustering

From How to fix Hyper-V Cluster error 1196: Cluster Name failed registration by Matthew Mombrea:

The issue, it turns out after days of forehead slapping, is that there were some additional external DNS servers specified on the NIC for that particular node - a remnant of its pre-cluster days. Your cluster node network interfaces should only have DNS records for your internal DNS server. While this didn't cause issues in most cases, for the purpose of cluster network name registration it was wreaking havoc.
First, check the NIC settings for each of your cluster nodes to make sure there are no external DNS records present. If there are, delete them. Next, ensure the A record for your cluster exists on your internal DNS servers in case they have been scavenged.


Creating index on large MySQL table taking forever

Yes, there is a safe way (not faster though) to alter schemas if you use tool. This does not require any downtime.  The one we use everyday on our production is to use

pt-online-schema-change
this tool will show you the progress of the SQL.

I hope this helps


How to get the column names of a RECORD?

Generally PLpgSQL doesn't allow dynamic work over fields of composite types. This languages is pretty static - more similar to older compilied languages like Pascal, Ada, Modula.

Some modern version of PostgreSQL allows transformation record->json and Postgres has lot of functions for work with JSON values (I use row_to_json and json_each_row` functions):

do $$
declare
  r record;
  key text;
  val text;
begin
  select 1 as a,3.4 as b into r;
  for key, val in
     select * from json_each_text(row_to_json(r))
  loop
    raise notice '% %', key, val;
  end loop;
end $$;

NOTICE:  a 1
NOTICE:  b 3.4
DO

I don't know your case, but if you need this feature, probably you are using plpgsql not well. It is static language - mostly time you should to know, what fields are in record before you are execute query. The programming of stored procedures is pretty far to programming in dynamic languages like JavaScript or Python or Lua.


Oracle 11g Enterprise downgrade to Standard One

Ultimate source for such answers is Oracle Database Licensing Information.

Sadly to downgrade from Enterprise to Standard Edition you have to export all the data install Oracle Standard Edition and import data (Doc ID 139642.1).

From your list "Automatic SQL Tuning Advisor" is Enterprise Edition only feature. As a rule of thumb - features which needs AWR are Enterprise Edition only plus you need to buy Oracle Diagnostics Pack.


How to lower "Created_tmp_disk_tables" - temporary tables count created on DISK?

What do you mean by a "26GB Heap"?

tmp_table_size = 256M is dangerously high.  If multiple connections decide to need tmp tables, you could run out of RAM.  Swapping is worse for performance than lowering various settings.

Tmp tables are necessary in many situations.  Don't fear them.  But do check on them.

DISTINCT, GROUP BY, ORDER BY and UNION often require a tmp table.  If the tmp table fits in min(tmp_table_size, max_heap_table_size), then the tmp table may be in RAM using Engine=MEMORY.  If bigger than that, then the tmp table is Engine=MyISAM and is slower.  There are other reasons for using MyISAM, most notably is selecting a TEXT field.  More details.

Another common "error" is to blindly use VARCHAR(255) and utf8.  When using MEMORY, that becomes a 765-byte CHAR, which hastens the conversion to MyISAM.

The numbers you gave...

Created_tmp_disk_tables is nearly as large as Created_tmp_tables -- This implies that either tmp_table_size is not big enough (which I doubt), or MEMORY cannot be used (which I suspect).  Above 20% is a red flag in my analysis.
Created_tmp_tables / Uptime -- above 20/second is a red flag
Created_tmp_disk_tables / Uptime -- above 1/second is a red flag
Created_tmp_disk_tables / Questions -- above 4% is a red flag

Set long_query_time = 1 and turn on the SlowLog, preferably to FILE.  Wait a day, then use pt-query-digest on the slowlog to find the "worst" queries.  If you can't figure out how to improve them, ask us.

I'll disagree with Rolando on two items:

RAM disk is a bad idea -- it is better to give the extra RAM to other caches rather than trying to outsmart MySQL.
OPTIMIZE TABLE is rarely useful, and not very relevant for the question.


Without hunt_teams table, a simple query for the teams playing a specific Hunt would require joining players with team_members and grouping them by team_id. A lot more work compared to querying hunt_teams. And that’s probably a query you’ll be running often.

I can also imagine that this additional source of truth will be nice to have in the long run. Running some stats or dealing with eventual inconsistencies in the database will be much easier.

So the first schema is one I would go with


I think you are missing user id condition in the outer query.

select
* FROM
user_plan inner join receipe
on a.receipe_id = b.id
where a.user_id =:user_id and b.week_id
= ( SELECT max(week_id) FROM user_plan where user_id=:user_id)


It depends on the SQL SECURITY characteristic you defined when you created the procedure.
SQL SECURITY for a stored procedure can be either DEFINER or INVOKER.

If your user can not create table but the definer of the stored procedure can, then your user will be able to create a table through the stored procedure.

By default, the SQL SECURITY characteristic in MySQL is DEFINER.

To put it in a nutshell :

Stored procedure with SQL SECURITY DEFINER : runs with stored procedure creator rights
Stored procedure with SQL SECURITY INVOKER : runs with current user rights


Purging of unreferenced data via unconditional DELETE, by leaning on foreign key constraints

One option would be to create views for these "inconvenient" tables that only return rows where all foreign key relationships have been broken.  Then you could do unconditional deletes from those views.

You would have the overhead of coding each of these views*, but that's a one-time startup cost.  This is essentially the same as what you mentioned already:

  I could write some code find out which rows in a certain table aren't referenced anywhere else

But in the end, you will have the "unconditional deletes" in your cleanup job.

Note: the approach below would not scale well on really large tables without indexes, and potentially some form of batching (e.g., putting a TOP 100 and ORDER BY in the view definition

Say you have two "main tables" that reference this "inconvenient table" (the one you need to remove rows from when they are no longer referenced:

CREATE TABLE dbo.InconvenientTable
(
    Id int IDENTITY(1,1) NOT NULL,
    [Description] varchar(10) NOT NULL,

    CONSTRAINT PK_InconvenientTable
        PRIMARY KEY (Id)
);
GO

CREATE TABLE dbo.MainTable1
(
    Id int IDENTITY(1,1) NOT NULL,
    [Description] varchar(10) NOT NULL,
    InconvenientTableId int NOT NULL,

    CONSTRAINT FK_MainTable1_InconvenientTable
        FOREIGN KEY (InconvenientTableId)
        REFERENCES dbo.InconvenientTable (Id)
);

CREATE TABLE dbo.MainTable2
(
    Id int IDENTITY(1,1) NOT NULL,
    [Description] varchar(10) NOT NULL,
    InconvenientTableId int NOT NULL,

    CONSTRAINT FK_MainTable2_InconvenientTable
        FOREIGN KEY (InconvenientTableId)
        REFERENCES dbo.InconvenientTable (Id)
);

The inconvenient table has 3 rows, all 3 of which are referenced in MainTable1, while only 1 is referenced in MainTable2.

INSERT INTO dbo.InconvenientTable
    ([Description])
VALUES
    ('One'),
    ('Two'),
    ('Three');
GO

INSERT INTO dbo.MainTable1
    ([Description], InconvenientTableId)
VALUES
    ('One', 1),
    ('Two', 2),
    ('Three', 3);

INSERT INTO dbo.MainTable2
    ([Description], InconvenientTableId)
VALUES
    ('Two', 2);

Now we need a view that shows all rows in dbo.InconvenientTable that are not referenced by the two main tables:

CREATE VIEW dbo.InconvenientTable_RowsToDelete
AS
SELECT it.*
FROM dbo.InconvenientTable it
WHERE
    NOT EXISTS (SELECT NULL FROM dbo.MainTable1 mt1 WHERE mt1.InconvenientTableId = it.Id)
    AND NOT EXISTS (SELECT NULL FROM dbo.MainTable2 mt2 WHERE mt2.InconvenientTableId = it.Id);
GO

Currently all rows are referenced, so this query returns 0 rows:

Now let's delete the 'Two' row from both main tables:

DELETE dbo.MainTable2 WHERE InconvenientTableId = 2;
DELETE dbo.MainTable1 WHERE InconvenientTableId = 2;

And now the view returns that unreferenced row:

Now we can delete everything from the view, which successfully removes the 'Two' row from our inconvenient table for compliance reasons:

*You could also attempt to automate the creation of the views with dynamic SQL and metadata queries, but that seems risky


Implications of removing unnecessary column from primary key

The ideal clustered index in SQL Server meets the following criteria:

Unique
Never changes
New values are strictly ascending
Narrow
Queries against the table run faster because they can take advantage of range seeks or the ordering of the clustered index

In production you may not be able to pick a clustered index which meets all of those criteria, which is fine. You can only do the best that you can do. A clustered index of four GUIDs is an uncommon choice. I assume that it will meet the first two criteria but not the other three. One performance impact of the current index is that nonclustered indexes will take up additional disk space because they each store a copy of the clustered index. That's why clustered indexes are ideally narrow.
Based on the information provided in this question, you will likely be better off by changing the clustered index to just be the SomeTableID column. I'm making some assumptions here, but it's unlikely that you'll see performance degradation caused by that switch.
This answer addressed clustered indexes and not primary keys because the clustered index is the thing that matters for performance, which seems to be the focus of this question. Primary keys are to enforce data integrity and business constraints. Continuing to match the columns for the clustered index and the primary key should be fine in this case unless there is a business reason to keep the current primary key columns.


How to prevent memory optimized tables from blowing out OPT_MEMORY_OPTIMIZED\$HKv2

It turned out the prolem was caused by the fact that:

My database used recovery mode Full; and
I wasn't backing up my logs.

Once I set my database to use recovery mode Simple the problem went away.


Are UDF used by a stored procedure optimised/merged in the SP itself?

If I understand the question correctly, it should depend on the type of function. Inline Table-Valued Functions have their definitions substituted into referencing queries, just like what happens with views. This is what is meant by "inline", and why they are not true modules that can be signed, among other differences with the other function types.

Multi-statement Table-Valued Functions and Scalar User-Defined Functions are completely independent modules that are executed separately and have their own plans.

This behavior should have nothing to do with them being referenced in a stored procedure or in an ad hoc query.

Now, whether or not UDFs are called every time, that depends on the type of UDF:

T-SQL UDFs are executed per row and prevent parallel plans
SQLCLR UDFs, if they are marked as IsDeterministic=true; (is false by default), and are not doing any data access, do not prevent parallel plans and should be able to have their return values cached (per query execution, I assume).

All of this is why, whenever possible, it is best to convert T-SQL UDFs into T-SQL Inline TVFs.


Issue with getting data from XML file by using SSIS

Those attributes are on different levels so this is kind of the way the XML Source works.  You have a couple of options:

Use Sort options with a Merge Join to bring the two sections back together, something like this:

Capture the XML in a variable, pass it to a stored proc as a parameter and shred it in the proc, using something like this:

DECLARE @xml XML = '<?xml version="1.0" encoding="iso-8859-1"?>
<exchangerates type="Exhange rates" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" refamt="1" refcur="DKK" author="Bank">
    <dailyrates id="2016-07-22">
        <currency rate="505.47" desc="Australian dollars" code="AUD"/>
        <currency rate="380.36" desc="Bulgarian lev" code="BGN"/>
        <currency rate="205.85" desc="Brazilian real" code="BRL"/>
        <currency rate="515.31" desc="Canadian dollars" code="CAD"/>
        <currency rate="684.99" desc="Swiss francs" code="CHF"/>
        <currency rate="101.21" desc="Chinese yuan renminbi" code="CNY"/>
    </dailyrates>
</exchangerates>'

SELECT
    dr.c.value('@id', 'DATE') id,
    c.c.value('@rate', 'MONEY') rate,
    c.c.value('@desc', 'VARCHAR(50)') [desc],
    c.c.value('@code', 'VARCHAR(50)') code
FROM @xml.nodes('exchangerates/dailyrates') dr(c)
    CROSS APPLY dr.c.nodes('currency')  c(c)

Good luck!