Why does SESSION_USER return dbo instead of SQL login?

You are confusing Users with Logins. Users are "principals" at the Database-level while Logins are principals at the Server-level (a.k.a. Instance-level).

Each Database has a single User, dbo, that is mapped to a Login. A Login that is mapped to the dbo User will show up as dbo for SESSION_USER when the "current" Database is the DB in question.

AND, Logins in the sysadmin fixed server role (including Windows Logins that connect via a Windows Group that is member of the sysadmin fixed server role) will show up as dbo in SESSION_USER. This will be the case even if the Login is mapped to a User in that Database that has a default Schema other than dbo (since this issue has nothing to do with Schemas). An example of this is shown in "Test 3" in the test code below.

SESSION_USER can return a Login name when there is no User in the DB to map to. For example, if a Login has the CONTROL SERVER server permission such that they can connect to any DB but are not in the sysadmin fixed server role, SESSION_USER will return the Login name for only those DBs that do not have a User mapping. An example of this is shown in "Test 5" in the test code below.

What you want to use is ORIGINAL_LOGIN(). This function will return the actual Login that was used to connect to the instance, even if Impersonation has been used to alter the current security context to that of another Login.

The following tests illustrate and prove the behavior noted above:

SETUP

USE [master];
CREATE LOGIN [GazooLogin] WITH PASSWORD = 'NevrCrack';
CREATE DATABASE [GazooDB] COLLATE Latin1_General_100_CI_AS;
ALTER AUTHORIZATION ON DATABASE::[GazooDB] TO [sa];
GO

CREATE USER [GazooUser1]
  FROM LOGIN [GazooLogin]
  WITH DEFAULT_SCHEMA = [GazooSchema1];
GO

CREATE SCHEMA [GazooSchema1]
  AUTHORIZATION [GazooUser1];
GO

USE [GazooDB];
CREATE USER [GazooUser2]
  FROM LOGIN [GazooLogin]
  WITH DEFAULT_SCHEMA = [GazooSchema2];
GO

CREATE SCHEMA [GazooSchema2]
  AUTHORIZATION [GazooUser2];
GO

TESTS

--------------------------------------------------
-- Test 1:
USE [GazooDB];

EXECUTE AS LOGIN = N'GazooLogin';

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- database     CONNECT

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser2   Dali\Solomon    GazooLogin  GazooLogin

USE [master];
SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- database     CONNECT

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser1   Dali\Solomon    GazooLogin  GazooLogin

USE [GazooDB]; -- can only revert from DB where EXECUTE AS was run
REVERT;

--------------------------------------------------
-- Test 2:

USE [master];
EXEC sys.sp_addrolemember N'db_owner', N'GazooUser1';

EXECUTE AS LOGIN = 'GazooLogin';

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser1   Dali\Solomon    GazooLogin  GazooLogin

USE [GazooDB];

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- database     CONNECT

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser2   Dali\Solomon    GazooLogin  GazooLogin

USE [master]; -- can only revert from DB where EXECUTE AS was run
REVERT;

--------------------------------------------------
-- Test 3:

EXEC sys.sp_droprolemember N'db_owner', N'GazooUser1';

EXEC sys.sp_addsrvrolemember N'GazooLogin', N'sysadmin';

EXECUTE AS LOGIN = 'GazooLogin';

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- dbo  Dali\Solomon    GazooLogin  GazooLogin

USE [GazooDB];

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- dbo  Dali\Solomon    GazooLogin  GazooLogin

USE [master]; -- can only revert from DB where EXECUTE AS was run
REVERT;

--------------------------------------------------
-- Test 4:

CREATE LOGIN [SuperDuperDatabaseOwner] WITH PASSWORD = 'NevaCrack';
ALTER AUTHORIZATION ON DATABASE::[GazooDB] TO [SuperDuperDatabaseOwner];

USE [GazooDB];

EXECUTE AS LOGIN = N'GazooLogin';

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- dbo  Dali\Solomon    GazooLogin  GazooLogin

REVERT;

SELECT * FROM sys.database_principals;
SELECT * FROM sys.server_principals;

--------------------------------------------------
-- Test 5:

DROP SCHEMA [GazooSchema2];
DROP USER [GazooUser2];

USE [master];

EXEC sp_dropsrvrolemember N'GazooLogin', N'sysadmin';

GRANT CONTROL SERVER TO [GazooLogin];

EXECUTE AS LOGIN = N'GazooLogin';

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser1   Dali\Solomon    GazooLogin  GazooLogin

USE [GazooDB];

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooLogin   Dali\Solomon    GazooLogin  GazooLogin

USE [master]; -- can only revert from DB where EXECUTE AS was run
REVERT;

--------------------------------------------------
-- Test 6:

USE [GazooDB];

EXECUTE AS LOGIN = N'SuperDuperDatabaseOwner';

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- dbo  Dali\Solomon    SuperDuperDatabaseOwner SuperDuperDatabaseOwner

REVERT;

CLEANUP

--------------------------------------------------
-- CLEAN UP:

USE [master];
DROP DATABASE [GazooDB];

DROP SCHEMA [GazooSchema1];
DROP USER [GazooUser1];
DROP LOGIN [GazooLogin];
DROP LOGIN [SuperDuperDatabaseOwner];

!!!  ALSO, when working with Triggers in SQL Server, you need to be very careful when doing things such as the following:

SELECT @SOPNUMBE = SOPNUMBE FROM inserted
SELECT @DOCSTATUS = DOCSTATUS FROM INSERTED

The problem is that the INSERTED pseudo-table can have multiple rows in it, based on how many were affected by the DML operation. If there is more than one row that was updated, the value in those two variables will be that of the last row in INSERTED (and they are not in a specific order). If a column's value is the same across all rows then I suppose it doesn't matter, but it is still a best practice to not structure Trigger code in this manner.


Which databases support parallel processing across multiple servers?

Look into Hive:

http://wiki.apache.org/hadoop/Hive

It's free and scales very well.

A new presentation was posted recently with updated numbers:

http://www.slideshare.net/ragho/hive-icde-2010

also see

http://wiki.apache.org/hadoop/Hive/Presentations


There is nothing special about OUTPUT parameters when it comes to "named parameters" or "ordinal parameters". In SQL Server this terminology this applies to the EXEC call and how you specify parameters there: not direction

Ordinal = position must match and datatype must be compatible
Named = assign the local value to the stored proc parameter name. Only datatype must be compatible

This is ordinal

declare @bob as int, @rich as char(20)
--OK
exec test @bob output, @rich output
GO

declare @bob as int, @rich as char(20)
--Fail
exec test @rich output, @bob output

This is named

declare @bob as int, @rich as char(20)
--OK
exec test
      @ID = @bob output,
      @mark = @rich output
GO

declare @bob as int, @rich as char(20)
--OK
exec test
      @mark = @rich output,
      @ID = @bob output

If you had a stored proc with parameters with defaults ...

create procedure test2
  @ID as INT output
 ,@filler1 tinyint = 0 --has default
 ,@mark as char(20) output
 ,@filler2 tinyint = 0 --has default
as
     select @ID = 5,@mark='test'
go

.. then you need the DEFAULT keyword for ordinal parameters if you have subsequent mandatory parameters

exec test2 @bob output, DEFAULT, @rich output --don't need to do anything for @filler2

For named parameters, it can be DEFAULT keyword or ignored because order is irrelevant

exec test2
      @ID = @bob output,
      @mark = @rich output,
      @filler2 = DEFAULT

exec test2
      @mark = @rich output,
      @ID = @bob output

And the obligatory MSDN link


How use cascade delete for array field in PostgreSQL?

First of all you need to create gin index on array field:

CREATE INDEX index_university_user_id ON university USING gin (user_id);

Then you need to add trigger to process deleting users:

CREATE FUNCTION trg_d_users() RETURNS TRIGGER AS $f$
BEGIN
    UPDATE university
       SET user_id = array_remove(user_id, OLD.id)
     WHERE user_id = OLD.id;
    RETURN OLD;
END
$f$ LANGUAGE plpgsql;

CREATE TRIGGER trg_d_users BEFORE DELETE ON users FOR EACH ROW EXECUTE PROCEDURE trg_d_users()

If you want to remove university without users, you can add into function after UPDATE:

DELETE FROM university WHERE user_id = '{}';

And also I would reccomend to rename user_id field to user_ids. It very useful to see that field is array without checking schema.


It is still there in the compatibility view sys.sysindexes.

You can also use

SELECT OBJECT_NAME(p.object_id), SUM(rows)
FROM sys.partitions p
WHERE index_id < 2 and OBJECTPROPERTYEX (object_id ,'IsUserTable' ) = 1
GROUP BY p.object_id

This should not be taken as in any way guaranteed accurate. It includes the effects of uncommitted transactions, forwarding pointers on a heap and can be manually updated with some undocumented parameters to UPDATE STATISTICS.


You can use a CTE for this, if you want the row that's returned to be a complete, intact row rather than aggregates of any of the other columns. You can change the ORDER BY to prefer rows by any of the columns (the grouping is by the ones you think should be unique).

;WITH x AS
(
  SELECT col1, col2, col3,
    rn = ROW_NUMBER() OVER
    (
        PARTITION BY unique_columns
        ORDER BY unique_columns, tie_breaker_if_you_care
    )
  FROM dbo.source_table
)
SELECT col1, col2, col3 FROM x WHERE rn = 1;


Where to download SQL Server 2019 Express LocalDB Installer MSI from

If somebody stumbles upon this, here is the full download URL for SQL Server 2019 LocalDB


Enable SSL (Do this in addition to options below)

Besides the options listed below you should also enable SSL on your MySQL database. Otherwise usernames/passwords will be sent over the internet in plaintext. Based on your use case if a developer is sitting at a Starbucks and connects to your database (without SSL) then anybody could eavesdrop and steal the credentials to login.

You can read more about setting up SSL on MySQL here: http://dev.mysql.com/doc/refman/5.5/en/ssl-connections.html

Option 1 - Bastion Host

One approach to allow you to lock down access to your database is to setup a bastion host. The bastion host could be setup to allow connections from any IP address and your database would be setup to only allow connections from the bastion host. Basically it's like a proxy server.

The standard setup for it is to use a *nix machine with each developer having SSH access with key based authentication but no command execution privileges. Each developer could then SSH to the bastion and forward a port to the DB server but not execute anything else.

Securing the bastion becomes much easier than securing the DB server itself as authentication is done via SSH keys (vs passwords) and thus cannot be brute forced. Also, it doesn't matter that your MySQL server is running on Windows, only the bastion host needs to be *nix.

Option 2 - fail2ban (equivalent)

If you were running on a *nix OS I'd recommend running fail2ban. It's a daemon that scans your log files for unusually activity and sets up IP block rules based on it. For example you can configure it to blacklist IP addresses that are repeatedly trying to login to a website by having it scan the access logs.

You mention that your server is running Windows so fail2ban itself won't run on it (it's *nix only) but it looks like some other folks have tried similar things. I haven't tried either of these approaches so can't vouch for them but you could try them out:

https://serverfault.com/questions/27757/does-fail2ban-do-windows
https://serverfault.com/questions/43360/cygwin-sshd-autoblock-failed-logins

In both cases I think you'd have to tweak it to work with MySQL (it's setup to protect sshd).

Option 3 - JackDB

Full Disclosure: I'm the founder of JackDB

If your remote developers are connecting to your database to query/analyze/debug it via a database client then then you could have them use JackDB. It's a database client that runs entirely in your web browser.

Once it's setup, you could set up your firewall to only allow remote access from JackDB's IP address (it's a single static IP address) and your developer's could then use it to access an query your database. Your DB server would then be firewalled from the rest of the internet.

Note that this will only be a viable solution if your remote developers will only be executing SQL commands against the database and not remotely developing against it. JackDB allows you to run any SQL (SELECT, INSERT, UPDATE, DELETE, etc) but it does not allow for programmatic access for custom programs (it's a database client, not a driver interface).

You can read more about it and try it out here: http://www.jackdb.com/


Do I need MySQL Group Replication or a Single Server is enough for Click Counting, etc?

1M INSERTs/day = 12/second.  This is not very heavy -- for either a web server or MySQL.

While you presented an argument for having "reports" come produced on a Slave, there are ways to mitigate the load.

But creating and maintaining Summary Table(s), reports become light weight enough to not worry about.  The summary table(s) would be keyed by day plus some other dimension(s) and have COUNT(*), SUM(...), etc.  Daily subtotals are (usually) sufficient for weekly/monthly/yearly rollups.

For counting clicks -- assuming you just want to count them -- I recommend having a separate table with nothing but the id of what you are counting and an INT UNSIGNED (4 billion max).  By segregating this from the rest of stuff associated with id, there is much less interference.

Multiple web servers may not be needed, but scaling by adding another web server is relatively easy and painless.  Do, however, keep the number of threads in the web server down to, say, 30.

Each web servers can talk to at least one MySQL server.  I don't (yet) see the need for more than one MySQL server.  By not having multiple MySQLs, that complexity is avoided.

There are other reasons for using Replication (Group Repl or other) -- backups, HA, etc.; I am merely saying that your functional separation is not sufficient justification.

Another note... If click-counting is done with an HTTP request for which the reply is ignored, then there is no real reason to worry about whether they are queued up and delayed.  There will be bursts of more than 12/sec.  My Rule of Thumb is that a sustained rate of more than 100/sec. justifies extra consideration.


Retreive the list of Up/Down status of Oracle database using Shell Script

Are all the databases running on one server?  If so, start with

/home/mark > ps -fu oracle | grep pmon
oracle   15060     1  0  2019 ?        00:28:00 ora_pmon_test01
oracle   53410     1  0 Jan08 ?        00:15:33 ora_pmon_rcatn

And if that output matches what seems reasonable, pipe it through cut:
/home/mark > ps -fu oracle | grep pmon | cut -d_ -f3
test01
rcatn

But that really doesn't verify if the database is fully up and running; you'll likely need something like Jon mentioned in his comments.  And anything beyond what I listed above, gets complex quickly.
The PMON process is (to oversymplify) the main watchdog of an Oracle database, so if a PMON process is running, the database might be available and online.  99.9% of the time, yes, but you want to also know if it is accessible.


There are other memory consumers outside of the buffer pool.  For instance, SQL Server will use memory for worker threads, multi-page allocations, linked servers, extended stored procedures, CLR, etc.

The buffer pool is never going to equate to 100% of memory consumed by SQL Server.  It's a common misconception, as well as setting max memory will be the actual max memory SQL Server uses (not true).

EDIT:  Here's how I think you could get non-buffer pool memory size.  Note: wait for the input of other SQL Server professionals before taking this query as fact.  I cannot guarantee that it is absolutely correct.

declare
    @data_cache_size_mb decimal(12, 2),
    @plan_cache_size_mb decimal(12, 2),
    @total_mem_mb decimal(12, 2)

select
    @data_cache_size_mb = count(*) * 8 / 1024.
from sys.dm_os_buffer_descriptors

select
    @plan_cache_size_mb = sum(size_in_bytes) / 1024. / 1024.
from sys.dm_exec_cached_plans

select
    @total_mem_mb = sum(pages_allocated_count) * 8 / 1024.
from sys.dm_os_memory_objects

select
    @data_cache_size_mb as data_cache_size_mb,
    @plan_cache_size_mb as plan_cache_size_mb,
    @data_cache_size_mb + @plan_cache_size_mb as buffer_pool_size_mb,
    @total_mem_mb as total_instance_mem_mb,
    @total_mem_mb - (@data_cache_size_mb + @plan_cache_size_mb) as non_buffer_pool_size_mb


How to exclude tables when using pg_dumpall with postgresql?

The name pg_dumpall itself says “dump all”, which means you can't exclude anything. There is no such pg_cumpall option available.

Use pg_dump instead.


You have Table called 'product'
To find out the unique product among the one product, u need to maintain these structure.
1.Product table
2.Characteristics table. EX(size, color etc)
3.Product_Price table (A product price may vary based on characteristics. EX: Pendrive is a product. But their characteristics may change the price. 2GB pendrive,, 4GB Pendrive, 8GB pendrive. So, Based on characteristics the price also varying. This table for that purpose)
4. Product_Characteristics table. This is need to map the product and characteristics. Primary key from product table and primary key from characteristics table.

Then you can get one valuable structure for eCommerce products.
For stock you have to maintain from product price table.


Is Transaction Log Filling up Because Log Truncation is Delayed?

Am I interpreting these results correctly?

Yes, you are. Although it's a system database, it doesn't automatically back itself up. You must add these to your backup schedules just like the user databases.

  Can anyone recommend a way to free up space in the transaction log?

As you read, and understood, back it up. Once you back up the TLOG (which will require a FULL BACKUP first if you haven't done that) the % used will drop a lot. Then you can shrink the file. Go ahead and schedule these, or set the DB to SIMPLE MODE if you're good with that.

I should mention none of your log files are that big. They also probably aren’t set to a max growth so they probably won’t fill up until your volume does. Be sure to check them.


MongoDB: operator to match arrays whose elements are all contained in a specified array

Depending on your version of MongoDB server there are a few ways to approach this.

In MongoDB 3.6+ you can use the  $setIsSubset expression (which is true only when the first array given is a subset of the second) via the $expr operator:

> db.haystack.find({$expr:{$setIsSubset:["$vals",["A","B","D"]]}},{_id:0})
{
    "name": 1,
    "vals": [
        "A",
        "B"
    ]
}
{
    "name": 3,
    "vals": [
        "A",
        "D"
    ]
}

For earlier versions of MongoDB a similar outcome is possible (but less obvious) using double negation with the $elemMatch operator:

> db.haystack.find({$nor:[{vals:{$elemMatch:{$nin:["A","B","D"]}}}]},{_id:0})
{
    "name": 1,
    "vals": [
        "A",
        "B"
    ]
}
{
    "name": 3,
    "vals": [
        "A",
        "D"
    ]
}

Roughly translated: "find all documents where it is not the case that the vals array includes a value other than one in the provided list".


Disaster recovery/Server Migration for databases that are permanently in Standby

I think there are at least the following methods here for your consideration.

BCP (Bulk Copy) out each table and then script out the database and then you can recreate a new db using these data and scripts.
You can temporarily take the db offline (by detaching the db) and then copy the MDF file and/or LDF file. You can use the file copy to reattach it to another db.
If you want doing online, you can simply create an SSIS package to dump the data (of course, your destination db should be created the same way as the source db, which you can script it out)


Is it possible to restore SQL Server DB from transaction log (no MDF)?

Is it possible to restore the database to the last transaction (which should be just after the database was created) using just the transaction log, or is this something that can't be done?

No, restoring a transaction log is sequential. Transaction log relies on LSN (Log Sequence Number)

Also, you cannot restore your database with just transaction log. It requires the main database file (MDF) and NDF (secondary datafiles if there are any).


What should be the fill-factor for a table where only fixed-width columns will be updated?

Postgres doesn't store nulls directly in the field but rather keeps a bitmap of null values.
So using 100 will be problematic.

What you can do is use some default value such as 1/1/1970 just to allocate the spot for the field.


Sync two MySQL databases in two different locations

You have a few options:

Set up MySQL replication between the servers. Your internal server can act as the Master, and the web host server as the slave. Any updates performed on the Master will be replicated to the slave immediately (assuming a working connection). This will most likely be the easiest and most effective option to choose. To use replication, your internal database would need to be accessible over the network from the web host.
You can read more about replication here.

Each day, you can perform a mysqldump on the internal server, upload the dump file to the web host, and import the data. Since this is a full dump, if you have a very large database, this might not be feasible. If you like, this procedure could potentially be scripted to avoid having to do it manually.

You can set up binary logging on the internal server. You can then ship the binary logs to the web host and apply them to the database, effectively playing all transactions that occurred that day to the web server. In effect, this is what happens with replication anyway, so you would nearly always go with the replication set up instead of this option.

If there is no connection between the two databases, taking mysqldumps each day will be the easiest path to take.


right syntax to use near 'TOKUDB_DEFAULT'

Most likely the changes in this JIRA link: https://jira.percona.com/browse/PS-4942

To use a specific row_format or tokudb_row_format do the following instead:

SET SESSION tokudb_row_format=TOKUDB_LZMA;
CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB;

To check what compression method is used by the table you can run this command from the shell:

tokuftdump --header <tablename>_main*.tokudb


Yes you can rename the jobs.  They will still function normally because they are identified by job_id for system purposes.

You can find more info about how it is uses in the MSdistribution_agents table.  More info here: http://msdn.microsoft.com/en-us/library/ms174399(v=sql.90).aspx


Distribute rows into groups while minimizing difference

Only if you count SQL itself :) The DB does not have any way of solving this type of thing other than the naive way, but the naive way of generating all possible distributions of rows to groups, filtering to only those that satisfy the num_users_in_longest<=(num_users_in_shortest+1) requirement, and then calculating the delta for each distribution and using a LIMIT 1 to filter to the smallest one can be expressed in SQL without too much awkwardness. Here's a fiddle that does what you want. Again, this is doing the underlying work in the naive, inefficient way, and this won't scale to large datasets. For that, you'll need to find some clever algorithm to do this, and probably do it in a procedural language that gives you the flow control needed to implement it.


I'd recommend configuring the full text index for manual population; this will vastly reduce the number of "full-text auto population" messages written to the log.

The Microsoft Docs page shows how to configure a full text index for manual population.

Essentially, you explicitly define the index like so:

CREATE UNIQUE INDEX ui_ukDoc ON Production.Document(DocumentID);
CREATE FULLTEXT CATALOG AW_Production_FTCat;
CREATE FULLTEXT INDEX ON Production.Document
(
    Document                         --Full-text index column name
        TYPE COLUMN FileExtension    --Name of column that contains file type information
        Language 1033                 --1033 is LCID for the English language
)
    KEY INDEX ui_ukDoc
    ON AW_Production_FTCat
    WITH CHANGE_TRACKING OFF, NO POPULATION;
GO

Then, you'd need to schedule population via a SQL Server Agent job with the following command:

ALTER FULLTEXT INDEX ON Production.Document
   START FULL POPULATION;

Once you've completed the full population, you can schedule partial, incremental updates of the full text index via:

ALTER FULLTEXT INDEX ON Production.Document
   START INCREMENTAL POPULATION;

  INCREMENTAL
  Specifies that only the modified rows since the last population be retrieved for full-text indexing. INCREMENTAL can be applied only if the table has a column of the type timestamp. If a table in the full-text catalog does not contain a column of the type timestamp, the table undergoes a FULL population.

or

ALTER FULLTEXT INDEX ON Production.Document
   START UPDATE POPULATION;

  UPDATE
  Specifies the processing of all insertions, updates, or deletions since the last time the change-tracking index was updated. Change-tracking population must be enabled on a table, but the background update index or the auto change tracking should not be turned on.

You only need to do the full population once, after you initially create the full-text index. This page has details regarding incremental or update populations, and the requirements.


Execute xp_cmdshell as a non-windows user

You can use a SQL Server login.

The following procedure is explained in MSDN:

  To allow non-administrators to use xp_cmdshell, and allow SQL Server
  to create child processes with the security token of a less-privileged
  account, follow these steps:

  Create and customize a Windows local user account or a domain account with the least privileges that your processes require.
  Use the sp_xp_cmdshell_proxy_account system procedure to configure xp_cmdshell to use that least-privileged account.
  Note You can also configure this proxy account using SQL
  Server Management Studio by right-clicking Properties on your server
  name in Object Explorer, and looking on the Security tab for the
  Server proxy account section.
  In Management Studio, using the master
  database, execute the GRANT exec ON xp_cmdshell TO ''
  statement to give specific non-sysadmin users the ability to execute
  xp_cmdshell. The specified login must be mapped to a user in the
  master database.


is there a way (clean or unclean) to eliminate a filestream data file on a production SQL Server database?

To be clear, the FILESTREAM and In-Memory OLTP features are completely separate, and not compatible. Even though In-Memory OLTP uses some components of FILESTREAM, the FILESTREAM feature does not have to be enabled to use In-Memory OLTP, and memory-optimized tables are not able to be used with the FILESTREAM feature.

According to the error you received, you're trying to remove the memory-optimized filegroup, which is not possible.

What does the following query return?

 SELECT *
FROM [InMemTest2].sys.database_files
INNER JOIN [InMemTest2].sys.filegroups ON database_files.data_space_id = filegroups.data_space_id
WHERE filegroups.type = 'FX'


Primary key in resulting relation from union operation in relational algebra

By definition the union of two relation contains no duplicates. So it contains at least one candidate key, the composition of all attributes. In your example (id, name) is a candidate key.

DBMS do not "resolve" the issue by assigning a primary key because in almost all DBMS (at least those that use SQL), the union of two tables is a derived table (or a view or a CTE but not a base table) and primary keys(1) can be defined only for base tables (not for views, not for derived tables and not for common table expressions).

Still, the DBMS optimizer is often aware that the derived table that results from a union has no duplicates and that the composition of all attributes uniquely defines a row and may use this fact in subsequent operations on the table (e.g. in a more complex query where the union is joined to or unioned with other tables).

(1) : and constraints in general. There is an generalized SQL constraint construction (CREATE ASSERTION) that allows constraints across several tables that could be used for creating a unique constraint on a union but assertions have not been implemented by any major DBMS - probably because it's a very complicated issue. Firebird claims to have them but not sure how well or what the restrictions are.


sp_send_dbmail with multiple reply_to addresses

@reply_to can only accept 1 email address. Its by design - see this connect.

The document reflects that as well.

  [ @reply_to= ] 'reply_to'
  Is the value of the 'reply to address' of the email message. It accepts only one email address as a valid value. This is an optional parameter used to override the settings in the mail profile. This parameter is of type varchar(MAX). SMTP secuirty settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.


Since your view2 selects from view1 in another database, as it configured now the permissions on view1 will be checked, so if you just do

DENY SELECT ON [dbo].[ViewName] TO [UserWithoutAccess]

your select from view2 will fail with

  The SELECT permission was denied on view1, schema dbo, database db1.

What you should do is to enable your databases to cross-db chaining:
Enabling Cross-Database Access in SQL Server

i.e. execute this in both databases:

ALTER DATABASE db1 SET DB_CHAINING ON;
ALTER DATABASE db2 SET DB_CHAINING ON;

This way ownership chain will not be broken when you pass to another database and the permissions on view1 will not be checked.

Database owners of db1, db2 should be the same.

More on ownership chaining here: Ownership Chains


Aren't enums already ordered?

No.

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

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

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

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

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

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


Configure MariaDB client with host-specific credentials?

In ~/.my.cnf make sections like:

[clientmy_host_name_1]

[clientmy_host_name_2]

Then make an alias (assuming Linux-like OS - depending on shell, you can perhaps put the alias in ~/.bashrc_local):

alias mysql="mysql --defaults-group-suffix=\`hostname --short\`"

Now you can connect without specifying the password to any of the hosts for which you have stored the credentials in ~/.my.cnf. (These would be my_host_name_1 and my_host_name_2 in my example above.)


Should I be regularly shrinking my DB or at least my log file?

I strongly recommend you read Paul Randal's article on why you should NOT shrink data files (log files yes, data files no).

I won't quote or try to summarise the article as I really wouldn't do it justice! Just something I think you should at the very least be aware of.


How to find the nth highest salary in SQL?

What's happening here is the subquery is looking at each individual salary and essentially ranking them, then comparing those salaries to the outer salary (a separate query against the same table). So in this case if you said N = 4 it is saying:

WHERE 3 = (number of salaries > outer salary)

So looking at the data you have, let's rank them in order, and compare.

EmpID   Salary   How many *distinct* salaries are greater than this one?
-----   ------   -------------------------------------------------------
5       12000    6
4       37000    5
7       50000    4
3       54000    3
6       69000    2
2       80000    1
1       90000    0

So when n = 4, the row that will be returned is EmpID 3 (54000).

A much more intuitive way to write this query, in my opinion, is to use windowing functions like RANK(), ROW_NUMBER() or DENSE_RANK() (depending on whether or not you want ties). Let's take a look at how these different functions work against your data (and I've added an 8th row to represent a tie for 4th place):

DECLARE @salary TABLE(EmpID INT, Salary INT);

INSERT @salary VALUES
(1,90000),(2,80000),(3,54000),(4,37000),
(5,12000),(6,69000),(7,50000),(8,54000);

;WITH x AS
(
  SELECT EmpID, Salary,
    r  = RANK()       OVER (ORDER BY Salary),
    dr = DENSE_RANK() OVER (ORDER BY Salary),
    rn = ROW_NUMBER() OVER (ORDER BY Salary)
  FROM @salary
)
SELECT EmpID, Salary, r, dr, rn FROM x;

Results:

EmpID  Salary   r   dr  rn
-----  ------   --  --  --
5      12000    1   1   1
4      37000    2   2   2
7      50000    3   3   3
8      54000    4   4   4
3      54000    4   4   5
6      69000    6   5   6
2      80000    7   6   7
1      90000    8   7   8

I don't think you'd want to use RANK() for this specific problem, because of the way it works there is no 5th place, for example. So now it comes down to whether you want to include multiple rows in the case of a tie, and if not, if you want an arbitrary row or a specific row based on some criteria. So adjusting the statement slightly:

-- if you want ties:
;WITH x AS
(
  SELECT EmpID, Salary,
    dr = DENSE_RANK() OVER (ORDER BY Salary)
  FROM @salary
)
SELECT EmpID, Salary FROM x WHERE dr = 4;

-- results:
-- 3   54000
-- 8   54000

-- to take the *lowest* EmpID:
;WITH x AS
(
  SELECT EmpID, Salary,
    rn = ROW_NUMBER() OVER (ORDER BY Salary, EmpID)
  FROM @salary
)
SELECT EmpID, Salary FROM x WHERE rn = 4;

-- results:
-- 3   54000

-- to take the *highest* EmpID:
;WITH x AS
(
  SELECT EmpID, Salary,
    rn = ROW_NUMBER() OVER (ORDER BY Salary, EmpID DESC)
  FROM @salary
)
SELECT EmpID, Salary FROM x WHERE rn = 4;

-- results:
-- 8   54000


What is the date mentioned in @@version SQL Server

Its the SQL Server build date for the installed version. Refer to @@version on MSDN.


Chaining remote SQL Server Agent Jobs with success/failure feedback

You need to write a loop which polls serverB and checks whether the job has finished and what the outcome was. On each iteration of the loop make the job sleep using waitfor delay '00:02:00' (a 2 minute delay).

To check if the status on a job see the link below

https://sqljunkieshare.com/2014/11/21/get-sql-agent-job-status/


Data Migration from SQL Server to PostgreSQL

There is no "silver bullet" to complete this migration. You will need to set up your database schema in the destination, then perform an ETL process to pull the data out of SQL Server and insert it into Postgres. There will be changes required in the schema (and transformations of the data) because there isn't a 1:1 mapping of data types between the two platforms.

But that just moves the data.

Then you have to migrate your stored procedures, triggers, and other code embedded in the database. Again, there's no "here, run this script" to magically convert everything. These will require more effort to convert than the raw data.

If there is development ongoing with this system, you'll need to port each of those changes from SQL Server to Postgres. If you want a "live" migration and data is still being modified (insert, update, delete, anything that changes the state of the data), you'll need to replicate those changes to Postgres. When your cutover date arrives, you'll have to quiesce SQL Server, ensure that the last of the data and other updates have been migrated, then shut down SQL Server and update your apps to point at Postgres.

And don't forget to test before you make the final switch!


SQL Server Populate replication server from DPM

This steps will implement your second option.

  Or, Is it possible to have replication_server1 provide the initial
  snapshot, and then point it to the production_server publication once
  everything has caught up?

You will setup replication normally between repl1 and repl2.  Use the same distribution server that you will use when you setup prod-->repl2 server.

Once downtime start (confirm app servers are not making any new connection/writing to Production publishing articles):

Continue running the distribution agent jobs till undelivered command count is zero on both prod-->repl1 and repl1-->repl2. This is absolutely critical for this approach to work.
Stop the distribution agent job/jobs.
Remove replication (publisher and subscriber only) but keep the distributor as is.
Reinstall replication between prod-->repl2 with 'replication support only' when running sp_addsubscription. See details here.
Run snapshot jobs. It will not resync your table schema+data, rather create system objects if any missing.

  replication support only  Provides automatic generation at the
  Subscriber of article custom stored procedures and triggers that
  support updating subscriptions, if appropriate. Assumes that the
  Subscriber already has the schema and initial data for published
  tables. When configuring a peer-to-peer transactional replication
  topology, ensure that the data at all nodes in the topology is
  identical. For more information, see Peer-to-Peer Transactional
  Replication.

  Not supported for subscriptions to non-SQL Server publications.

You can use this query to find undelivered commands in distribution database.

SELECT ss.srvname AS publisher,
       [name]     AS Agent,
       publication,
       a.publisher_db,
       subscriber_db,
       art.article,
       art.destination_object,
       art.source_object,
       undelivcmdsindistdb,
       delivcmdsindistdb,
       s.article_id,
       agent_id,
       a.publisher_id,
       subscriber_id
FROM   distribution.dbo.msdistribution_status AS s WITH (nolock)
       FULL OUTER JOIN distribution.dbo.msdistribution_agents AS a WITH (nolock)
                    ON s.agent_id = a.id
       JOIN master.dbo.sysservers AS ss WITH (nolock)
         ON a.publisher_id = ss.srvid
       JOIN distribution.dbo.msarticles AS art WITH (nolock)
         ON s.article_id = art.article_id
WHERE  subscriber_db <> 'virtual'
       AND subscriber_db = 'SubscriberDBName'
ORDER  BY undelivcmdsindistdb DESC
COMPUTE sum(undelivcmdsindistdb)


You have a couple of different questions in here.
Can sp_BlitzWho get blocked?
Yes, any query can. There are DMVs that are known to ignore isolation level requests and incur blocking.
Should I use sp_BlitzWho to log wait stats by session?
I wouldn't recommend doing that on a scheduled basis, no. If you need wait stats by session, you're better off with a traditional monitoring tool with a higher sampling rate. You wouldn't want sp_BlitzWho running, say, every 5 seconds, but you really need data at that level of granularity if you want to analyze wait stats by session.
Why shouldn't I log wait stats by session?
Because your server can be a victim of the death-by-a-thousand-cuts scenario: a query that runs thousands of times per second, each time darting in and out, and never accumulating that much wait time each time it runs. However, in total, it can add up to a big deal.


As Oleg mentions, you really should normalize.  For your scenarios, though:

A - I would use an index on City INCLUDE ([name]) - you don't need to sort by name as well  but you do want that field at the leaf level of the index.

B - I'm not sure what you are asking here.  If you have an auto-incrementing ID then that should be your clustered index.

There's a lot of reasons to use an auto-incrementing ID as your clustered key.  I won't go in depth on these (there are a ton of resources, and Kim Tripp is a really good place to start):

Will only insert new records at the end of the index
Narrow (since it will be a part of every other index)
Unique (keeps it narrow since non-unique indexes need a "uniqueifier" additional int added)
Non-nullable


Tracking changes to a table via the update/insert cmds executed against it daily

This sounds like a situation where Oracle Change Data Capture would be appropriate.  You would need to write some code that would subscribe to changes to this table and process them by pushing the changes to your SAP BW system.

You can also use Oracle Streams to do heterogeneous replication from Oracle to a non-Oracle database using an Oracle Transparent Gateway.


Autogrow. Primary vs additional data files

Like @JonSeigel said above in his comment, this is not a SQL Server action.  Your third party application(s) must be doing this on their own, or somebody manually creating the additional data files.

  And, is there a way to combine these NDF files back into the primary MDF?

What you're looking for is DBCC SHRINKFILE(YourDataFileName, EMPTYFILE); where YourDataFileName is the logical name of the *.ndf file(s).  What this does is, provided you have sufficient space in the other data file(s) in the containing filegroup, is it empties the database data file by moving all of the data into the other data files in the same filegroup.  You can then remove it.

See this BOL reference: DBCC SHRINKFILE

There is an example on that same page that I will quote verbatim (as it seems to be exactly what you are trying to do [Note: The below excerpt is taken from the above link]):

  D. Emptying a file

  The following example demonstrates the procedure for emptying a file
  so that it can be removed from the database. For the purposes of this
  example, a data file is first created and it is assumed that the file
  contains data.

USE AdventureWorks2012;
GO

-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2012
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    );
GO

-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO

-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2012
REMOVE FILE Test1data;
GO


Giving Special Permissions to a Stored Procedure in SQL Server

This is fairly easy to accomplish (including handling multiple databases and Dynamic SQL) without any Impersonation (IMPERSONATE permission), cross-database ownership chaining (Server / Instance or Database setting), or TRUSTWORTHY (Database setting). What you need to do, generally speaking, is:

Create a Certificate in Database A
Sign Stored Procedure in Database A using ADD SIGNATURE
Create the same Certificate in Database B
Create a User in Database B based on that Certificate
Assign the new User the permissions needed to access the desired tables

Example:

CLEANUP

USE [master];
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseA')
BEGIN
    PRINT 'Dropping [DatabaseA] DB...';
    ALTER DATABASE [DatabaseA] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [DatabaseA] SET ONLINE;
    DROP DATABASE [DatabaseA];
END;

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseB')
BEGIN
    PRINT 'Dropping [DatabaseB] DB...';
    ALTER DATABASE [DatabaseB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [DatabaseB] SET ONLINE;
    DROP DATABASE [DatabaseB];
END;

IF (SUSER_ID(N'JohnnyLunchbucket') IS NOT NULL)
BEGIN
  PRINT 'Dropping [JohnnyLunchbucket] Login...';
  DROP LOGIN [JohnnyLunchbucket];
END;

IF (OBJECT_ID(N'tempdb..#CertInfo') IS NOT NULL)
BEGIN
  PRINT 'Dropping [#CertInfo] Temp Table...';
  DROP TABLE #CertInfo;
END;

SETUP

USE [master];

EXECUTE AS LOGIN = N'sa';
PRINT 'Creating databases...';
CREATE DATABASE [DatabaseA] COLLATE Latin1_General_100_CI_AS_SC;
CREATE DATABASE [DatabaseB] COLLATE Latin1_General_100_CI_AS_SC;
REVERT;
GO

ALTER DATABASE [DatabaseA] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseA] SET TRUSTWORTHY OFF;

ALTER DATABASE [DatabaseB] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseB] SET TRUSTWORTHY OFF;
GO

CREATE LOGIN [JohnnyLunchbucket] WITH PASSWORD = 'OhSoSecure;)';

USE [DatabaseA];

CREATE USER [JohnnyLunchbucket] FOR LOGIN [JohnnyLunchbucket];
GO

CREATE PROCEDURE dbo.RunReport
AS
SET NOCOUNT ON;

SELECT * FROM [DatabaseB].[dbo].[RestrictedTable];
GO

GRANT EXECUTE ON dbo.RunReport TO [JohnnyLunchbucket];

CREATE CERTIFICATE [PermissionsCert]
  AUTHORIZATION [dbo]
  ENCRYPTION BY PASSWORD = 'WeakPassword'
  WITH SUBJECT = 'Used to test granting permissions to code',
  EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE TO [dbo].[RunReport]
    BY CERTIFICATE [PermissionsCert]
    WITH PASSWORD = 'WeakPassword';

SELECT CERTENCODED(CERT_ID(N'PermissionsCert')) AS [PublicKey],
       CERTPRIVATEKEY(CERT_ID(N'PermissionsCert'), 'OtherPassword', 'WeakPassword')
                 AS [PrivateKey]
INTO   #CertInfo;
GO

USE [DatabaseB];

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = N'CREATE CERTIFICATE [PermissionsCert] AUTHORIZATION [dbo] FROM BINARY = '
               + CONVERT(NVARCHAR(MAX), [PublicKey], 1)
               + N' WITH PRIVATE KEY (BINARY = '
               + CONVERT(NVARCHAR(MAX), [PrivateKey], 1)
               + N', DECRYPTION BY PASSWORD = N''OtherPassword'''
               + N', ENCRYPTION BY PASSWORD = ''WeakPassword'');'
FROM   #CertInfo;

PRINT @SQL;
EXEC (@SQL);

CREATE USER [PermissionsUser] FROM CERTIFICATE [PermissionsCert];

CREATE TABLE dbo.[RestrictedTable]
(
  [ID] INT NOT NULL IDENTITY(1, 1)
     CONSTRAINT [PK_RestrictedTable] PRIMARY KEY,
  [Other] VARCHAR(50)
);

GRANT SELECT ON [dbo].[RestrictedTable] TO [PermissionsUser];

INSERT INTO dbo.[RestrictedTable] ([Other]) VALUES ('Ta da!');
GO

TEST

-- Quick test to show that [PermissionsUser] cannot be Impersonated:
USE [DatabaseB];
EXECUTE AS USER = 'PermissionsUser';
/*
Msg 15517, Level 16, State 1, Line 123
Cannot execute as the database principal because the principal "PermissionsUser" does not
exist, this type of principal cannot be impersonated, or you do not have permission.
*/

-- Main test:
USE [DatabaseA];

EXECUTE AS LOGIN = 'JohnnyLunchbucket';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

SELECT * FROM [DatabaseB].[dbo].[RestrictedTable];
/*
Msg 916, Level 14, State 1, Line 144
The server principal "JohnnyLunchbucket" is not able to access the database "DatabaseB"
under the current security context.
*/

EXEC [dbo].[RunReport]; -- SUCCESS!!!
-- 1    Ta da!

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

For more info on Impersonation vs Module Signing you can review this other answer of mine (and the answer linked in it):

SQL Server Impersonation is just NOT working


Using Transactions with PostgresQL dependency issue

You don't need to query the table, to get the generated id, you can get that using the lastval() function:

insert into company_profile (....)
values (...);
insert into related_table(company_id, ...)
values (lastval(), ...);


The list is kept for MVCC (https://en.wikipedia.org/wiki/Multiversion_concurrency_control) as @jkavalik mentions in his comment. This is required for providing isolation for transactions  (https://en.wikipedia.org/wiki/ACID). A record cannot be deleted under you as long as you're in a transaction.

When you start a transaction (depending on your isolation level) you get a "snapshot" of the current state of the database. This is implemented in MVCC by TRX_ID which is being checked against your actual transaction id. If it's higher you cannot see that because it means it was updated after you started the transaction.

Deleted (and also old versions of updated) records are picked up by a purge thread in the background as of MySQL 5.6. It used to be in the master thread and now the number of threads is configurable also (https://dev.mysql.com/doc/refman/5.5/en/innodb-improved-purge-scheduling.html)


One problem with your proposed design is that instead of simple JOINs you have to run dynamic SQL every time you want to see the actual value of any entities.  Furthermore, you will have complicated procedures (or triggers) for inserting data into ProjectContents.

Stick with the ProjectEntity1, ProjectEntity2, ... solution if you can have more that one EntityX coupled to a certain Project.  If this is not the case (that is, a Project can have maximum one of each Entities), you can create a table like this:

ProjectID | Entity1ID | ... | EntityNID

In either case, your JOINs will be relatively numerous (not that 10 JOINs in a query are a lot) but otherwise simple.  And, lastly, occasional maintenance (like adding new entities) will also be simple.


Modifying a part (one procedure) in oracle database package

Don't. Keep all your package sources in a source control system. That way you can go back to a previous version if you need to.


Database Schema for a User and his variable number of items

Your problem seems quite simple. Basically as I understand it you need to associate a collection of strings to a user. Your system would work ok, but I would recommend you replace the username with a UserId. That way the cost of updating client facing information is lessened, and you use less memory to store the data.

You didn't specify your target RDBMS but something like the following would work in SQL Server, and give you a good starting point for other RDBMS that use slightly different syntax.

CREATE TABLE dbo.Users -- Added the 's' so it wouldn't be a key word
(
   UserId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, -- FK to this instead of the UserName
   UserName NVARCHAR(50) NOT NULL UNIQUE, -- Unique constraint optional if not identifying
   <Other user details>
);

CREATE TABLE dbo.UserStuff
(
    UserStuffId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, -- Hook for targeted updates/deletes
    UserId INT NOT NULL, -- Not unique so can't be PK
    ItemDescription NVARCHAR(50) NOT NULL, -- Resize to fit needs
    <Other meta data you want like created timestamp>

    CONSTRAINT [FK_UserStuff_UserId_to_Users_UserId] FOREIGN KEY (UserId)
        REFERENCES dbo.Users(UserId)
);

If SELECT performance is poor then you can always add a covering index to the dbo.UserStuff table like so:

CREATE NONCLUSTERED INDEX [IDX_dbo_UserStuff_UserId] ON dbo.UserStuff(UserId)
   INCLUDE (ItemDescription);


MySql Federated table and changes propagation

I recommend you use standard master/slave replication with each node being slave to every other node (the multi-source) option. This isn't master-master as log-slave-updates should not be enabled.

Use the server configuration auto_increment_increment to 10, and have each node having auto_increment_offset to a unique number I think will satisfy you requirement that each node has a different, non-conflicting numbering of ids as an alternate to centralized issuing of numbers.

You application will need to ensure that the same deletes or updates of a row do not happen on simultaneous nodes. Inserts should also use the auto_increment field only.

Federated tables is a bit of a hack that isn't particularly maintained.


There is an connection issue between Server A and B, NOT local machine and Server B.


PostgreSQL: Performance: ORDER BY an indexed column is slowing down the query performance

It is normal that retrieving a sorted result is more effort.
If you want the total count, all rows have to be scanned, even if you only return the first 50.
You could try order by name || '' to see if a sort would be faster.
My recommendation is not to retrieve the total count together with the query.
The optimizer's estimates are good in this case, so you should use an approximate result count by running
EXPLAIN (FORMAT JSON) SELECT ...

and retrieving the Plan Rows.


What is fragmenting my index on a table with stable traffic?

In General

Just to be clear at the start: fragmentation refers to when the next logical data page (i.e. the values in the fields for 1 or more rows), is not the next physical data page (the location of page in the data file).

Non-fragmented pages (in Physical Page order):

Logical Page 1: A1, A2, A3, A4
Logical Page 2: B1, B2, B3, B4
Logical Page 3: C1, C2, C3, C4

Fragmented pages (in Physical Page order):

Logical Page 1: A1, A2, A3, A4
Logical Page 3: C1, C2, C3, C4
Logical Page 2: B1, B2, B3, B4

In a general sense, fragmentation is caused by:

Data inserted out of order (the order of the index in question).

Using ever-increasing values (e.g. an IDENTITY field) is one way to mitigate this as rows will always be created at the end of the index (assuming an ASC sort order).
FILLFACTOR is another means of reducing fragmentation when not using an ever-increasing value. But unlike an ever-increasing value, FILLFACTOR is only a certain amount of space per data page and depending on what data comes in, it might not be enough space, in which case page splits will occur. And depending on the max size of the row, it might be that no amount of reserved space will help (if the rows are over 4030 bytes).

Updates of variable-length fields (e.g. VARCHAR, NVARCHAR) and NULLable fix-length columns marked as SPARSE that change from NULL to a non-NULL value. If the combination of changes of these types of fields increase the size of the row and there is no more room left on the data page, a page split will occur.

FILLFACTOR again helps here as it reserves some amount of space (assuming the row size is small enough to allow for it) for update operations to increase the size of the row without needing to do a page split.

Deleting enough rows as to remove one, or more, data pages

Additional resources on index fragmentation:

SQL Server Index Fragmentation, Types and Solutions
Index fragmentation with insert/updates, measuring it and fixing it
Visualizing Index Fragmentation

In Particular

For this particular index, the average row size is 1413 so you will only get 5 rows (again, on average) per page. No FILLFACTOR is specified so it would take the system default value (which the default "default" value is 0, meaning to fill the data page). It seems most likely that the values are not ever-increasing given that FOONO is VARCHAR(12). This would give you some amount of fragmentation quite often as new rows come in. Of course, there will be a slight amount of natural reserved space, even without specifying a FILLFACTOR, since the row size is variable. Meaning, after a REINDEX there might be 2000 bytes left on the page as the next row in order was larger than 2000 bytes and could not fit on the page. If a new row comes in that logically fits between those existing rows and is <= 2000 bytes, it should go there. Hence, for these cases, in order to get a sense of the relationship between the values coming in and the increase in fragmentation, you need to identify the new rows, which seems doable given the timestamp field.

It has been confirmed that this table is INSERT-only. That narrows down the source of fragmentation being the new rows coming in. The variance in fragmentation should be a function of a) the distribution of the values of the FOONO field, and b) the width of the new rows. Given that there is a DATETIME field indicating when the rows are inserted, you should be able to query on a per-week basis for the past 6 weeks:

The FOONO field
The ID field
The total size of the variable-length fields in each row (based on the data there; using COALESCE(DATALENGTH(ColumnName), 0) for each of those fields). You are not concerned about the fixed-length fields since they aren't a factor in the increase in fragmentation (unless they are marked as SPARSE and are now coming in as non-NULL values whereas in prior weeks they were typically NULL).

What you are looking for is (comparing the most recent 3, or now 4, weeks with when fragmentation was lower; and be sure to ORDER BY the DATETIME field) at least one of the following:

a more varied distribution of FOONO values
ID values:

a distribution that is interspersed with existing ID values, AND / OR
non-sequential / out-of-order values with respect to chronological INSERT order as denoted by DATETIME timestamp field (hence why the need to do the ORDER BY TimestampField)

a greater overall row size

Any combination of those things happening should account for an increase in fragmentation.

Where the Rubber Meets the Road

The common theme among these 4 possibilities is that they all have the effect of increasing the rate of page splits. Page splits occur when a new row (or, updated row that is larger than it was before, but for now we are only concerned with new rows) should logically be placed on a data page that does not have enough free space to fit the new row. When this happens, SQL Server will (typically) take some amount of the latter portion of the existing data and move it to a new page and add the new row to whichever of the two pages it logically falls into. The end result is that the new page will have at least half, if not slightly more, of the rows in it (I tested several times with a table that holds 10 rows per page and each page split left the existing page with 5 rows and the new page with 6 rows).

The typical page split is just half of the story, though. The other half is what happens when data is added sequentially to these new pages created by the split operations. As new rows are added and the new pages fill up, new pages are created without moving any rows. When this happens the new page is not just in logical order, but also in physical order as compared to the prior page. This is not considered a fragmented page because from the prior page to this new page, they are in both logical and physical order.

Meaning, if we had full pages A0, B0, and C0 and added some rows that would logically go into B0, a page split will occur giving us a new page B1, with some amount of rows on both B0 and B1. But now, if we fill up B1 and keep adding rows in sequential order that logically come before the data on C0, it will (or will often) create a new page B2 for the new rows without moving anything from B1 onto B2. Physically the pages will be ordered as either:

B1, B2, A0, B0, C0

or

A0, B0, C0, B1, B2

The jumps from B0 to B1, and from B2 back to C0, are considered fragmentation as the logical order (A0, B0, B1, B2, C0) does not match the physical order. BUT, the jump from B1 to B2 is not considered fragmentation because the logical order does match the physical order.

Let's put all of this together now. Keeping in mind that the data pages for this particular index are packed fairly tightly (which is fine), most insert operations will have a high probability of being into an already filled page and hence requiring a split. If there are 4 new rows to add if they are spread out (either in terms of the value of FOONO and/or in terms of ID--if new values might be between already existing values) such that they should go into 4 separate pages, that is 4 page splits representing 4 new pages and 8 occurrences of fragmentation (the jump out of order and back into order for each of the splits). BUT, if the 4 new rows are in order, then it is possible that only one page split occurs, representing 1 new page and 2 occurrences of fragmentation. If more rows are added sequentially to the new page created by the split such that a new page is needed, they can roll onto a new next page that will obviously increase the page count by 1 but will not register as an occurrence of fragmentation.

The other scenario--data that is sequential in terms of the new records for the week but were not added in sequential order as determined by sorting by the "timestamp" DATETIME field--is mostly the same as the main scenario just described. The difference here is that a) without ordering by the "timestamp" field it might appear as though the data is sequential and will falsely get ruled out as the source of fragmentation, and b) page splits will happen more frequently within the set of created pages that were just created due to the initial page splits.

Hopefully the following example will help illustrate the issue. Assume we are staring with logical pages A0, B0, and C0 and they are also physically in order. Page B0 is filled and contains rows Ba, Bf, and Bn. New rows come in that are Bo, Bp, Bq, Br, and Bt. But they don't come in that order. The following maps out what should be happening to the data pages as each row comes in. The page IDs (e.g. A0) indicate the logical page while the order indicates their physical placement.

Start:

  A0
  B0 = Ba, Bf, and Bn (page is full)
  C0

Add Br:

  B1 = Bn and Br (physically before A0, but logically after B0 = fragmentation)
  A0
  B0 = Ba and Bf (Split: Bn moved to new page B1)
  C0

Add Bt:

  B1 = Bn, Br, and Bt (page filled)
  A0
  B0 = Ba and Bf
  C0

Add Bp:

  B1 = Bn and Bp (Split: Br and Bt moved to new page B2)
  B2 = Br and Bt (physically AND logically after B1 = NOT fragmentation)
  A0
  B0 = Ba and Bf
  C0

Add Bq:

  B1 = Bn, Bp, and Bq (page filled)
  B2 = Br and Bt
  A0
  B0 = Ba and Bf
  C0

Add Bo:

  B1 = Bn and Bo (Split: Bp and Bq moved to new page B2)
  B3 = Br and Bt (still physically after B1, but now logically after B2 = fragmentation)
  B2 = Bp and Bq (physically after B3, but logically after B1 = fragmentation)
  A0
  B0 = Ba and Bf
  C0

Result: 3 page splits, 3 new pages, 4 counts of fragmentation, 4 pages partially filled

Had those same rows been inserted in order, it would end up looking like:

Start:

  A0
  B0 = Ba, Bf, and Bn (page is full)
  C0

Final:

  B1 = Bn, Bo, and Bp
  B2 = Bq, Br, and Bt
  A0
  B0 = Ba and Bf (Split: Bn moved to new page B1)
  C0

Result: 1 page split, 2 new pages, 2 counts of fragmentation, 1 page partially filled

Misc.

Just to have it noted for those who have a similar setup but do allow UPDATE operations, if it were that UPATEs did occur then this question would contain a false premise and would ultimately be unanswerable. The issue at hand is that a timestamp (whether using a TIMESTAMP / ROWVERSION datatype or a DATETIME / DATETIME2 datatype) only tells you when the last change occurred; it does not tell you a) how many changes have happened, nor b) what were the specific changes that happened (i.e. did the row get smaller? Did 5 fields get much larger but 20 fields got slightly smaller so the row size actually stayed the same? etc). If updates do occur on this table, then the only way to see what caused the increase in fragmentation is to collect the audit data of the changes (whether custom, trigger-based solution or Change Data Capture). But in the current setup, there is no way to reliably correlate the number of rows changed--as indicated by the timestamp field--with any amount of fragmentation (again, IF updates are happening).

Also, regarding this statement:

  The table has 134 columns contains 14gb worth of data, and the primary key is not an identity (sigh)

If this were an OLTP table then I would find the 134 columns more problematic than the primary key not being based on an IDENTITY field. Since this is a history table then it is probably ok to have 134 columns. But even being a history table, it is not necessarily best that the PK be based on an IDENTITY field, or that the table even has a PK (I am assuming that PK and Clustered Index are not the same since the index in the question is UNIQUE CLUSTERED and not a PK). And even if we are talking about the Clustered Index (whether or not the PK), then still not necessarily always best as the leading column. It all comes down to how the data is accessed. If the leading field is an IDENTITY but that value is never used in JOINs or UPDATE or DELETE operations, then you will have sacrificed performance on all queries, even when there is no fragmentation (not fixable), just to avoid some amount of fragmentation (fixable via REBUILD or REORGANIZE).


Use the REPLACE function, e.g.

select replace(yourfield,'''','') from yourtable where ...

Here we are replacing a single quote character with an empty string. Since SQL character literals are themselves enclosed in single quotes, we escape the single quote character in the literal by doubling it.

More information about the use of the REPLACE function can be found in the manual, as is often the case.


How to implement Draft functionality, with attached files?

For the drafts side, what we do (LedgerSMB. PostgreSQL) is use a bool "approved" column or some approval information (where NULL means draft, for example "approved_by" or "approved_at").

I don't know the quirks of MySQL check constraints too well, but in PostgreSQL you can condition check constraints on the value in a field, something like:
CHECK (approved is not true or title is not null).  This makes it impossible to approve something that has not had everything filled on.  We don't do this generally because approval is supposed to be all or nothing, but there are a few areas where something like this is done.


How to interpret AlwaysOn event hadr_db_partner_set_sync_state

According to How It Works: Always On–When Is My Secondary Failover Ready?, a commit_policy of

DoNothing

is an indication that the secondary is offline.  A commit_policy of

Delay

indicates that the secondary is online, but catching up.  And finally,

WaitForHarden

indicates the secondary is caught up with the current transaction.


am I correct in assuming this would perform a full table scan in the sub queries, effectively reading more than actually necessary?

No.  The query optimizer should see through this.  There's no general rule that subqueries are materialized in query execution.

  Am I also correct in assuming that the below would actually be more efficient?

It may be that with enough joins and subqueries you could get a bad plan.  But it's not the first place to look.  Use the Query Store (SQL 2016+), or the plan cache DMVs, or XEvents to see the query plans and associated resource costs.

For instance on AdventureWorksDW, these two queries use the same plan.

select p.Color, sum(f.SalesAmount) SalesAmount
from
 (select * from FactInternetSales) f
join
 (select * from DimProduct) p
 on p.ProductKey = f.ProductKey
 group by p.Color

 go

select p.Color, sum(f.SalesAmount) SalesAmount
from FactInternetSales f
join DimProduct p
 on p.ProductKey = f.ProductKey
group by p.Color


Inserting documents via standalone connection to MongoDB replica set

As long as your clients are connecting to the current replica set primary they will be able to work with documents as usual and those writes will be replicated. This is useful as a transitional approach when upgrading a standalone deployment to a replica set.

However, without a replica set connection string your clients will be unaware of any changes in the replica set status. If your original mongod changes role from primary to another state those clients will be unable to write data: the server will return a "not master" error which will be mapped to an appropriate exception by your MongoDB driver.

For more information on expected behaviour for officially supported MongoDB drivers you may want to review the Server Discovery and Monitoring (SDAM) spec. In particular, the Rationale section addresses some implementation choices and common questions on topology discovery and monitoring.

Note for completeness: if you were upgrading to a sharded cluster you would definitely have to change your connection string. In a sharded cluster all client write operations should go via a mongos router.


Availability Group Upgrade 2012 to 2016 Data Move

You could look at log shipping to keep the new 2016 instances up to date well ahead of your upgrade. If you log ship from the current primary to all four new replicas then the cutover and sync is much quicker.

Assuming you're creating a second 2016 AG (and not joining the 2016 instances to the 2012 AG) then at the point of cutover:

Take a log backup on 2012 Primary and restore on 2016 servers with norecovery
Recover DB on 2016 primary
Add DB to AG on 2016 primary and take a log backup
Restore log backup on 2016 secondaries and join to AG
Remove listener from 2012 AG
Add listener to 2016 AG.

If you run into issues post-upgrade and need to revert to 2012, simply remove the listener from 2016 AG an add it back to 2012 AG. The 2012 AG will have carried on like normal and will continue working. The caveat here is that once 2016 is live (after any post-upgrade testing) you won't be able to rollback to 2012 without data loss.

If you're adding 2016 to 2012 AG then log shipping can still be used, but the cutover process is much more complicated as you have to replace the 2012 replicas in a piecemeal fashion as 2012 only allows 4 replicas. Rollback is also more complicated and may leave you without HADR after rolling back until you resynchronise the 2012 replicas.

You need to define this process thoroughly and run through the whole process in a lower environment multiple times to make sure you've got everything aligned and working.


Oracle: Is there a way to put exception handling into a function and just reference that function inside procedures?

Well, you could have a procedure that checked for various values of sqlcode or sqlerrm and performed whatever actions you have in mind based on those, and called that procedure from your exception handler passing the filename and directory object name. However, I don't think that would be a neat solution.
If the problem is trying to reproduce the (quite old) Oracle Base approach for handling utl_file exceptions in multiple procedures, don't do that. It just copies every single exception from the manual and dumps them all into a monumental end-of-procedure exception handler, regardless of context.
When you open a file, you should use an exception handler to ensure that it opened OK, otherwise you can report that you couldn't open file x in folder y. For example (from www.williamrobertson.net/documents/refcursor-to-csv.shtml):
begin
    l_file :=
        utl_file.fopen
        ( filename => p_filename
        , location => p_directory
        , open_mode => 'w'
        , max_linesize => 4000 );
exception
    when utl_file.invalid_operation then
        raise_application_error(-20007, 'File '''||p_filename||''' could not be opened in directory '||p_directory||' ('||k_dir_path||')', TRUE);
    when utl_file.invalid_path then
        raise_application_error(-20008, 'File location '||p_directory||' ('||k_dir_path||') is invalid.', TRUE);
    when utl_file.invalid_filename then
        raise_application_error(-20009, 'Filename '''||p_filename||''' is invalid.', TRUE);
end;

Similarly, when you write to a file you could use exception handlers to ensure that write errors are reported clearly with appropriate details.
Or, centralise all CSV writing operations in a single package like the one I linked to, where you just pass in a refcursor and the file details and it does the rest.


How to script all the service broker elements for each database it is on, in the current server?

After seeing a recent comment from David Browne:

  SSDT / SqlPackage supports Service Broker objects.

I tried it out. I created some service broker objects based on the demo here:

Configuring Service Broker for Asynchronous Processing

Then I download the DAC framework from here:

Download Microsoft® SQL Server® Data-Tier Application Framework (18.0)

Then I ran this command (without the carriage returns):

sqlpackage
  /Action:extract
  /SourceServerName:.\instancename
  /SourceDatabaseName:AsyncProcessingDemo
  /TargetFile:C:\scripts\sb.dacpac

Then I went to c:\scripts, right-clicked the dacpac file, and chose Unpack...:

This asked me for an output folder:

I clicked Unpack, went to that folder, and there were four files, including model.xml:

And sure enough, that .sql file contained a script for all the broker objects, assembled in almost the right order. It generated a script with the contract created before the message types it depends on. So you will end up with:

CREATE CONTRACT ... ([MessageType1] SENT BY INITIATOR, ...
GO
CREATE MESSAGE TYPE [MessageType1]...

You'll need to flip those around so the message types are created first.

I'm sure SqlPackage.exe and/or the DAC wizard has ways to restrict this to just the service broker objects, but this should be a good start.


Need help phrasing regular expression to find particular T-SQL pattern

The "magic" regexp to do what you want is:

^[\s\t]*INSERT( INTO|)[\s\t\r\n]*[A-Za-z0-9_."\[\]]*[\s\t\r\n]*(SELECT|VALUES)\s

I tested some scenarios in "The Regex Coach" (a free software to test regexp). I made sure it also works for tables like [dbo].[tablename] or "tablename" etc.


How to get top 1 row for each of the state in USA

You can use the ROW_NUMBER and PARTITION BY StateAbbreviation to obtain the first city per state:

WITH d
AS
(
    SELECT StateAbbreviation, CityName
         , ROW_NUMBER() OVER
          (
           PARTITION BY StateAbbreviation
               ORDER BY CityName
          ) AS Ordinal
      FROM dbo.Cities
)
SELECT d.StateAbbreviation, StateName, CityName
  FROM d
  JOIN dbo.States AS s
    ON s.StateAbbreviation = d.StateAbbreviation
 WHERE Ordinal = 1;

You can change the ORDER BY clause to whatever suits your requirement but the key here is to make sure that you partition by the StateAbbreviation to get the row number generated per state.


Query to find if there are more than X occurences within any period of given length minutes

Using the window function lag() we can mark all rows where a qualifying set ends:
SELECT *
     , occurred_at - lag(occurred_at, 2) OVER (PARTITION BY user_id ORDER BY occurred_at) <= interval '10 min' AS passed
FROM   timestamps
ORDER  BY user_id, occurred_at;

If the timestamp occurred_at for the same user (user_id) two rows back is within 10 minutes, we have a set of three.
For a given user_id:
SELECT count(*) FILTER (WHERE passed) AS qualifying_sets
FROM (
   SELECT occurred_at - lag(occurred_at, 2) OVER (ORDER BY occurred_at) <= interval '10 min' AS passed
   FROM   timestamps
   WHERE  user_id = 1  -- given user
   ) sub;

To get all user_id that pass the test at least once:
SELECT user_id, count(*) FILTER (WHERE passed) AS qualifying_sets
FROM  (
   SELECT user_id
        , occurred_at - lag(occurred_at, 2) OVER (PARTITION BY user_id ORDER BY occurred_at) <= interval '10 min' AS passed
   FROM   timestamps
   ) sub
GROUP  BY 1
HAVING bool_or(passed)
ORDER  BY 1;

The added count of qualifying_sets is optional.
db<>fiddle here


On DB2, who updated a record and what did the record look like before the update?

I've tested two tools that can provide info about transactions including when, who, using which computer and application

ApexSQL Audit creates auditing triggers for you, so if you're not up to coding and creating triggers for each of your tables, this is an option

It has 2 built in reports, and the good thing about it is that it saves all captured transactions into 2 tables, so you can run queries against them and extract any info you need

Unfortunately, it can't replay the transactions

Another one is ApexSQL Log. It reads transaction logs and trn backups, so the database has to be in Full recovery model. Besides the data transactions, it also tracks schema changes (create table, alter function, etc) and it can replay transactions


Improving database design, is this a valid case for entity attribute values?

I think that your proposed approach with a table row per language is fine, but I would definitely have language be a foreign key to a lookup table of languages.
Otherwise you'll end up with languages like English, en, EN or sprfz that people enter on a whim, and you will get complaints why the program does not display the proper English version of the text. Prefill that lookup table with entries for all conceivable languages.


What's called 'Universe Data Source' in Crystal Reports?

Universe Data Connection is for connecting Crystal Reports to a Universe type file(.unv), which are created by a Universe Design Tool by SAP. This file is an abstract layer between the db schema and the user. It gives an opportunity to do queries without knowing the underlying schema of the db, hence can be used by the staff who does not know SQL.
In contrast Relational Connecting directs to a specific relational database. Both types can have only one connection.


Can I modify a column's data type against a live table in Postgres?

Can I do this against a live production table, or do I need to bring down the database?

Yes. You can do it against a live table. Will even be very fast since citext and varchar are binary coercible, so no table rewrite is required (since Postgres 9.1).

But this still acquires an ACCESS EXCLUSIVE lock on the table, which makes any concurrent access on the table wait, and potentially fail. And if there are any indexes involving the column, those must still be rebuilt (automatically), which may take substantially longer.

And any dependent objects like views that use the column are road blockers and need to be removed before the change (and added back later).

Related:

Does "alter table … set data type" functionally differ from "alter table … type"?

  Do the indexes need to be rebuilt afterwards?

No. Indexes involving the column in any way have to be rebuilt, but that happens automatically. See above. The manual:

  Indexes and simple table constraints involving the column will be automatically converted to use the new column type by reparsing the originally supplied expression.

  Will this result in any space savings?

No. Binary coercible means same space on disk and in RAM. There may be a one-time effect for rebuilt indexes that are now in pristine condition - especially if those were bloated.

  Any kind of performance boost?

Not likely. Depends on the complete picture. citext may or may not have been useful. I don't recommend citext after mixed experiences. I rather go with expression indexes. Or consider a non-deterministic collation in Postgres 12 or later.

Generally, citext operators are a bit slower everywhere than plain text / varchar operators. (They have to do more work.) Possible one-time effects from rewriting involved indexes can be achieved with REINDEX as well.

Related:

Can I change CITEXT columns to VARCHAR without encountering any surprise difficulties?
What is the overhead for varchar(n)?


Importing a different character set export dump into another DB instance

For me it was the imp command that was wrong. The dump I got was taken using expdp and I was trying to import it with imp.

Got imported using impdp command.


Created databases and inserted data on MySQL slaves in Master-Slave replication - how to fix?

You say:

  databases have been created on the slaves and filled with data.

The easiest way of correcting this is to take a mysqldump of the specific databases on the slave, drop the database on the slave, and import the dumpfile onto the master. The slaves will then replicate the data.

This of course gets trickier if it is not whole databases or even tables (but specific rows inside a table).


Cascade primary key update to all referencing foreign keys in oracle

With DEFERRABLE foreign key constraints:

create or replace procedure cascade_update_primary
(
  p_table_name in varchar2,
  p_old_value in number,
  p_new_value in number
)
as
  l_column_name varchar2(128 char);
begin
  select
    cc.column_name into l_column_name
  from
    user_cons_columns cc
    join user_constraints c on (c.constraint_name = cc.constraint_name)
  where
    c.constraint_type = 'P' and c.table_name = p_table_name
  ;

  execute immediate 'set constraints all deferred';

  execute immediate 'update "' || p_table_name || '" set "' || l_column_name ||
    '" = :B1 where "' || l_column_name || '" = :B2' using p_new_value, p_old_value;

  for c in (
    select
      cc2.table_name, cc2.column_name
    from
      user_cons_columns cc
      join user_constraints c on (c.constraint_name = cc.constraint_name)
      join user_constraints c2 on (c.constraint_name = c2.r_constraint_name)
      join user_cons_columns cc2 on (c2.constraint_name = cc2.constraint_name)
    where
      c.constraint_type = 'P' and c.table_name = p_table_name
      and cc.column_name = l_column_name
  )
  loop
    execute immediate 'update "' || c.table_name || '" set "' || c.column_name ||
      '" = :B1 where "' || l_column_name || '" = :B2' using p_new_value, p_old_value;
  end loop;
end;
/

In case of NOT DEFERRABLE foreign keys, with some modifications:

insert parent row with new PK value
update child tables to new value
delete parent row with old PK value


Use data in a tablespace to populate another postgres database instance without actually inserting

A table belongs to one database. Period. You can share a tablespace among databases of the same DB-cluster, but you cannot share a table.

But you can use dblink for what you have in mind. This way you can query tables from a different database - even from a different DB-cluster on a different server. Simple SELECT queries on the same server are very fast. Try this search for code examples.

Or look at CREATE FOREIGN TABLE for a very similar functionality. For now, I prefer dblink, because it is mature and stable. The new SQL/MED features (Management of External Data) FOREIGN DATA WRAPPER and FOREIGN TABLE are still in the making ..


Why RECOMPILE doesn't fix parameter sniffing, but local variables do?

But why the recompile didn't help?

Using OPTION (RECOMPILE) is not magic. It does often produce a better-performing execution plan (at the cost of a full compilation each time) because the query is compiled each time for the specific values of any parameters at the time, and also enables parameter embedding, which may produce further benefits.

Determining the specific reasons that OPTION (RECOMPILE) did not help in your particular case would require details you are unable to provide.

  Local variables. The procedure was faster than ever.

Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem before OPTION (OPTIMIZE FOR UNKNOWN) and trace flag 4136 became available.

For more details please see:

Parameter Sniffing, Embedding and the RECOMPILE Options


Does Oracle support the VALUES expression construct?

Does Oracle support the VALUES ( expression [, ...] )? If not what is the alternative method of creating a table from row-literals provided in the query?

The most concise option we've got is with insert all:

insert all
  into t values(1,'one')
  into t values(2,'two')
  into t values(3,'three')
select * from dual

select * from t;

NUM | WORD
--: | :----
  1 | one
  2 | two
  3 | three

dbfiddle here

Note that the syntax is an extension to the standard and allows inserting into multiple tables with a single statement and conditional inserts, as the example shows:

insert all
when 1 = 1 then
  into t values (num, word)
when num > 0 then
  into t values (-num, 'minus ' || word)
select 0 as num, 'zero' as word from dual union all
select 1, 'one'   from dual union all
select 2, 'two'   from dual union all
select 3, 'three' from dual ;

7 rows affected

select * from t;

NUM | WORD
--: | :----------
  0 | zero
  1 | one
  2 | two
  3 | three
 -1 | minus one
 -2 | minus two
 -3 | minus three

dbfiddle here


In PostgreSQL we have a name for it INSERT ... RETURNING.

You can use this form in a CTE to obtain the effect you want..

CREATE TABLE foo ( foo serial, bar int );

WITH t1 AS (
  SELECT x
  FROM generate_series(1,10) AS t(x)
),
t2 AS (
  INSERT INTO foo (bar)
  SELECT x
  FROM t1
  WHERE NOT EXISTS (
    SELECT *
    FROM foo
    WHERE foo.bar = x
  )
  RETURNING foo, bar
)
SELECT foo, bar
FROM foo
INNER JOIN t1 ON (foo.bar = t1.x)
UNION ALL
  SELECT *
  FROM t2
;

Here we

Generate test data in a cte t1 which we plan to INSERT or SELECT on.
With t2 we INSERT the data we don't already have into our desired table... foo, and return the results (that which was inserted) as t2
We run a SELECT to get all of the elements in our test data (t1) that already exist.
INSERT all of the others.


Primary and secondary replicas on same server

You can and that's one of the beautiful things with availability groups over traditional clustering.  You'll need to set up two (or more if you wanted) AGs, one would initially have SQL01 as it's primary node and contain db01-db05 and the other would initially have SQL02 as it's primary node and contain db06-db10.

You are then free to fail them over independently as you wish, you could put all AGs on one server or split them out over the two.

I can't find any documentation or demos showing the setup of two AGs (perhaps it could be a subject for a future blog post).  The big difference between traditional clustering and using AGs is that the SQL servers aren't clustered in the way that your DBAs are probably thinking and there's no need for shared storage.  Basically, the SQL instances are installed as stand alone instances on each node of the cluster.  The AGs themselves are created as individual roles within the cluster and because of that can be failed over individually.

Hopefully that makes some sense, it's quite tricky to explain without an example.


How can I search SQL Server for maintenance plans that execute specific tasks?

Yes. This can be done by using a combination of the system SSIS tables and XQuery.

Note: In SQL 2012, this will only work for Maintenance Plans created within SSMS. If you used Data Tools to deploy a package that performs maintenance via the project deployment model, this will not work.

SQL Server saves maintenance plans as SSIS packages, hence the need for the system SSIS tables (sysssispackages and sysssispackagefolders).
The package-level data where we'll find the references to the tasks you're searching for will be found in the column sysssispackages.packagedata.

XQuery

If you are not familiar with XQuery, you can start here.

Find the task ExecutionType(s) for the Task(s)

The first thing that you'll need to find is the ExecutionType of the task(s) you're looking for.

Find an existing maintenance plan with the task(s) in question or build a new maintenance plan.
Remember the name(s) of the maintenance plans you're looking for.
Query msdb..sysssispackages for the plan name(s).

Example query to retrieve the package XML based on the maintenance plan name:

 SELECT
    name PlanName
    ,createdate PlanCreated
    ,CAST(CAST(pack.[packagedata] AS varbinary(max)) AS XML) PackageDataXml
 FROM msdb.dbo.sysssispackages pack
 WHERE name = 'RebuildIndexes'

Click on the results in the PackageDataXml column which will open the raw XML in another query tab.
In the newly opened XML tab, search for the string "ExecutableType". (Nothing fancy required here - just good old-fashioned CTRL+F.)

We do this because each task is saved as a DTS:Executable node, and each DTS:Executable node has a DTS:ExecutableType. The Executable type is what we can use in our final query to search for the specific tasks.
You'll find some ExecutableTypes for Executable nodes that don't relate to our quest. Keep searching until you find the relevant Executable node(s).
You'll know a good one when you see it, because the value for DTS:ExecutableType will have a useful descriptive name.
Only grab the first portion of the ExecutableType, which is usually the part of the string before the comma.

Search for the maintenance plans using the ExecutableType(s)

The following query uses XQuery and the ExecutableType values that were found in the step above to search in all maintenance plans.

There are commented out lines that would allow for filtering for plans associated with active jobs and/or active schedules.
Credit goes to StuartLC who helped me with the XQuery portion of this.
Has only been tested on 2012 and 2008R2.

/*
  Description: Search SSISPackages w/ xQuery for specific
  Maintenance Plan tasks. In this example:

    --> Index Rebuild
    --> Shrink Database
    --> Update Statistics

Known compatible versions: 2008R2 & 2012

    --> No 2008 or 2005 instances to try it out on. :'(
*/

-- Build a temp table to retrieve the list of plans
-- that use the tasks we're searching for.

IF OBJECT_ID('tempdb..#BadPlans') IS NOT NULL
  DROP TABLE #BadPlans;

CREATE TABLE #BadPlans
(
  PlanName nvarchar(128)
  ,PlanCreated datetime
  ,PlanFolder nvarchar(128)
  ,TaskDescription varchar(50)
  ,TaskExecutableType varchar(200)
);

;WITH XMLNAMESPACES
(
  'www.microsoft.com/SqlServer/Dts' AS DTS
  ,'www.microsoft.com/SqlServer/Dts/Tasks' AS SQLTask
)
INSERT INTO #BadPlans

--******2012 Results******

SELECT
  PlanName
  ,PlanCreated
  ,PlanFolder
  ,Nodes.node.value('(@DTS:Description)[1]', 'varchar(50)')
    AS TaskDescription
  ,Nodes.node.value('(@DTS:ExecutableType)[1]', 'varchar(200)')
    AS TaskExecutableType
FROM
(
    -- Grab the plan properties and the plan XML so that we can
    -- use CROSS APPLY for the DTS:Executable node.

  SELECT
    pack.name PlanName
    ,pack.createdate PlanCreated
    ,folder.foldername PlanFolder
    ,CAST(CAST(pack.[packagedata] as varbinary(max)) as xml) PackageDataXml
  FROM [msdb].[dbo].[sysmaintplan_subplans] sub
  JOIN msdb.dbo.sysssispackages pack on sub.plan_id = pack.id
  JOIN msdb.dbo.sysssispackagefolders folder on pack.folderid = folder.folderid
  --JOIN msdb.dbo.sysjobs jobs on sub.job_id = jobs.job_id
     -- optional, only needed if filtering for active jobs
  --JOIN msdb.dbo.sysjobschedules jsched on jobs.job_id = jsched.job_id
     -- optional, only needed if filtering for active schedules
  --JOIN msdb.dbo.sysschedules sched on jsched.schedule_id = sched.schedule_id
     -- optional, only needed if filtering for active schedules
  WHERE pack.packagetype = 6  -- required, indicates a user-created maintenance plan
    --AND jobs.enabled = 1    -- optional, will filter for plans associated with active jobs
    --AND sched.enabled = 1   -- optional, will filter for jobs with active schedules
) AS SysPackages
CROSS APPLY
-- Version-specific XPath for 2012. Look at this singular-plural-singular-plural-singular madness.
  SysPackages.PackageDataXml.nodes('/DTS:Executable/DTS:Executables/DTS:Executable/DTS:Executables/DTS:Executable')
  Nodes(Node)
WHERE
-- Each string that follows LIKE is the ExecutableType you found in the raw XML
-- in sysssispackages.packagedata.
  Nodes.node.value('@DTS:ExecutableType', 'varchar(200)')
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)')
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)')
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask%'

UNION ALL

--******2008R2 Results******

SELECT
  PlanName
  ,PlanCreated
  ,PlanFolder
  ,Nodes.node.value('(DTS:Property[@DTS:Name="ObjectName"])[1]', 'varchar(50)')
    AS TaskDescription
  ,Nodes.node.value('(@DTS:ExecutableType)[1]', 'varchar(200)')
    AS TaskExecutableType
FROM
(
    -- Grab the plan properties and the plan XML so that we can
    -- use CROSS APPLY for the DTS:Executable node.

  SELECT
    pack.name PlanName
    ,pack.createdate PlanCreated
    ,folder.foldername PlanFolder
    ,CAST(CAST(pack.[packagedata] as varbinary(max)) as xml) PackageDataXml
  FROM [msdb].[dbo].[sysmaintplan_subplans] sub
  JOIN msdb.dbo.sysssispackages pack on sub.plan_id = pack.id
  JOIN msdb.dbo.sysssispackagefolders folder on pack.folderid = folder.folderid
  --JOIN msdb.dbo.sysjobs jobs on sub.job_id = jobs.job_id
     -- optional, only needed if filtering for active jobs
  --JOIN msdb.dbo.sysjobschedules jsched on jobs.job_id = jsched.job_id
     -- optional, only needed if filtering for active schedules
  --JOIN msdb.dbo.sysschedules sched on jsched.schedule_id = sched.schedule_id
     -- optional, only needed if filtering for active schedules
  WHERE pack.packagetype = 6  -- required, indicates a user-created maintenance plan
    --AND jobs.enabled = 1    -- optional, will filter for plans associated with active jobs
    --AND sched.enabled = 1   -- optional, will filter for jobs with active schedules
) AS SysPackages
CROSS APPLY
-- Version-specific XPath for 2008R2. Not as much madness here.
  SysPackages.PackageDataXml.nodes('/DTS:Executable/DTS:Executable/DTS:Executable')
  Nodes(Node)
WHERE
-- Each string that follows LIKE is the ExecutableType you found in the raw XML
-- in sysssispackages.packagedata.
  Nodes.node.value('@DTS:ExecutableType', 'varchar(200)')
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)')
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)')
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask%';

SELECT * FROM #BadPlans;


Replication: Procedure or function sp_MSupd_PROC has too many arguments specified

It sounds like the replication procedures might need to be recreated. Use the sp_scriptpublicationcustomprocs procedure on the publisher to generate the replication procs and recreate them on the subscriber.


Poor SELECT performance on recheck cond with data and large (millions of rows) partial index

Add the second predicate of your query to the partial index as well:

WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398'

Your timestamp is probably a moving target, but I am going to assume you have lots of old rows that are excluded in most of your queries and only few "younger" rows are of interest. A typical use case. You can cut off old rows in your partial index.

CREATE INDEX posts_new_createdat_idx ON "Posts"("createdAt")
WHERE "new"
AND   "createdAt" > '2015-08-01 00:00';  -- replace with useful timestamp

This only makes sense if you can reduce the size of the index to a fraction. Else, the effect is typically not worth the trouble.

Replace with an opportune timestamp to cut off as many rows as possible while still covering relevant rows for your queries.

Modern versions of Postgres are smart enough to understand the index is applicable to queries cutting off at a later timestamp. For older versions you have to add the verbatim index condition redundantly to make the query planner use this partial index.

This index will be used by your query immediately as is. (I just removed some more noise):

SELECT COUNT(*) AS "count"
FROM  "Posts"
WHERE "createdAt" > '2015-08-19 14:55:50.398'
AND   "new";

Related:

Add datetime constraint to a PostgreSQL multi-column partial index
"Recheck Cond:" line in query plans with a bitmap index scan

Aside 1: "new" = TRUE is just a noisy way of stating "new" for a boolean value.

Aside 2: Don't use reserved words or CaMeL case or other illegal identifiers so you don't have to double-quote all the time. Very error prone.


This is actually part of the DOUBLE PRECISION type, synonym of float(53).

https://msdn.microsoft.com/en-us/library/ms173773.aspx


Is there any way to disable MongoDB's cache?

Mongo database service doesn't search for answers to your query in only the cache. It searches in the memory and if it doesn't find your answers, it has to go into the storage.
You can try the query below, and you will see the last data on your table.
db.zips.find().sort({ "_id": -1 }).limit(10)

Check out your data and your query, probably your condition is wrong.


Exporting/Importing data from SQL Server 2014 to PostgreSQL 9.5 with tab delimited files

(CSV is not a good option for me because of the text has commas in it and from what I am reading PG doesn't like double quotes)

Sure Pg accepts double quotes. See COPY

  QUOTE Specifies the quoting character to be used when a data value is quoted. The default is double-quote. This must be a single one-byte character. This option is allowed only when using CSV format.

You don't even have to do anything..

COPY foo FROM 'asdf.csv' WITH (FORMAT CSV);

  It looks to me like COPY doesn't recognize the tab, even though when I view the file it is tab delimited. There are no NULL values in this file btw.

If you want to use tabs, just set that up.

COPY foo FROM 'asdf.csv' WITH (FORMAT CSV, DELIMITER E'\t');


How do I split a long PL/pgSQL line of code over multiple lines?

String constants can be split over multiple lines as documented in the manual

INSERT INTO insert_log (log_time, description)
VALUES (
    now()
    , 'A description. Made up of 3 semi long sentences. '
      'That I want to split, in the code, not in the log table, '
      'over 3 lines for readability.'
);


how can I extract values from one cell to make it multiple rows

If the "list_of_jobs" is a known set of values, you can use UNPIVOT to do this, see this document

If it is an unknown set of values, you can use STRING_SPLIT in SQL Server 2016 or higher, see this document. NOTE: Your database compatibility level needs to be 130 or higher.

If you have SQL Server 2014 or earlier, or your compatibility level is below 130, you will need to write a user function to do the string splitting. Here's a good example.