MySql - alternate of auto_increment on multiple columns Since auto_increment on multiple columns is supported in MyISAM engine only and I can't use it because of its drawbacks (table lock, slower writes, no-transacts etc), I am looking for an alternative.

One thing I am already doing is to get the last id like following

Table

KEY1 INT NOT NULL -- PART OF PK
KEY2_ID INT NOT NULL -- PART OF PK
OTHERCOLUMNS ...

SELECT KEY1,IFNULL(MAX(KEY2_ID),0) + 1 NEXT_ID FROM TABLE1 WHERE KEY1 = ? GROUP BY KEY1;

Sample data

KEY1     KEY2_ID   OTHERCOLUMNS
1        1
1        2
2        1
2        2
2        3

When the above query is run for the key1=1 NEXT_ID will be 3 and for key1=2 it will be 4

Is there any other way we can do it?

Update: In response to DTest & Chris Travers

I am designing db for my browser based online game where game state data (users game objects like inventory including potions, weapons, buildings, livestock etc) is stored in multiple user_xxx tables.

For example, I have a table user_map where all user buildings (including road path), decorations (chair, lamp posts etc) is stored.

map_items
---------
id(pk)       name     type       dimensions (this is actually stored in two separate columns)
1        "bakery"  "building" "3x3"
2        "chair"  "chair"    "1x1"
3        "main road"  "road"      "2x2"

Above table is the lookup table which a player can buy and place in his virtual world

user_map
-----------
user_id(pk)       map_item_id(pk)       instance_id(pk)      location     state
1234              1                     1                    "10,10"      "producing"
1234              3                     1                    "15,15"      "na"
1234              3                     2                    "17,15"      "na"

Since a user can build/place same object multiple times, I am maintaining the instance_id so that user_id + map_item_id + instance_id will give a unique item

Like above table, I am maintaining instance_id for remaining tables like user_weapons, user_mounts, user_inventory etc.,

Now, to answer why I don't want to user a surrogate key is that I am afraid of limitation of an auto_increment column which will some day going to exceed the maximum number of the data type supports. I am actually infavour of using surrogate key (I actually started changing the model before starting this post and stopped)

Probably I am worrying too much about it because the length mysql supports looks fine but some how the using the unsigned ubigint might slow down my queries?

unsigned int 4,294,967,295
unsigned bigint 18,446,744,073,709,551,615

You could use a trigger.  The key problem there though is that it will have to do a lookup and so multiple concurrent inserts into the table may get messy (concurrent being within the life of the transaction).

A real alternative here is probably going to go with a natural candidate key (multiple columns of actual rather than artificial data) and add a single autoincrement key for easy joins.  Having a single column reference makes joins to the table a lot easier.

Those probably aren't the answers you want to hear but they should work.  If they won't work, then the question is why and what data modelling scenario you are trying to address.


Fail to start sql cluster role I have installed SQL Server using Failover Clustering on 2 nodes, running Windows Server 2012 R2 Enterprise Edition and SQL Server 2008R2 Standard SP1.  Shared storage is provided by an iSCSI NAS device.

If I move the services to node 1, then shutdown node 2, SQL Server continues to run, as expected, on node 1.  However, if I then shutdown node 1, and restart it, SQL Server fails to startup with the following error:

  The resource has failed to come online due to the failure of one or more provider resources.

Why can't I start SQL Server?

In order for SQL Server services to start as part of a Windows Failover Clustering Service, quorum needs to be attained by at least one node in the cluster.  If a two-node cluster has one node down, that node cannot contribute to the quorum, so some other method is necessary for the node that is up to attain quorum.  Quorum is essentially a set of votes to see which node gets to run SQL Server.  Without quorum, no node can run the service, which is the symptoms you are seeing.
The MSDN page on Failover Clustering quorums states:

Why configure the quorum?
To increase the high availability of the cluster, and the roles that are hosted on that cluster, it is important to set the cluster quorum configuration appropriately.
The cluster quorum configuration has a direct effect on the high availability of the cluster, for the following reasons:

It helps ensure that the failover cluster can start properly or continue running when the active cluster membership changes. Membership changes can occur because of planned or unplanned node shutdown, or when there are disruptions in connectivity between the nodes or with cluster storage.

When a subset of nodes cannot communicate with another subset of nodes (a split cluster), the cluster quorum configuration helps ensure that only one of the subsets continues running as a cluster. The subsets that do not have enough quorum votes will stop running as a cluster. The subset that has the majority of quorum votes can continue to host clustered roles. This helps avoid partitioning the cluster, so that the same application is not hosted in more than one partition.

Configuring a witness vote helps the cluster sustain one extra node down in certain configurations. For more information about configuring a quorum witness, see Witness configuration later in this topic.

Be aware that the full function of a cluster depends on quorum in addition to the following factors:

Network connectivity between cluster nodes

The capacity of each node to host the clustered roles that get placed on that node

The priority settings that are configured for the clustered roles

For example, a cluster that has five nodes can have quorum after two nodes fail. However, each remaining node would serve clients only if it had enough capacity to support the clustered roles that failed over to it and if the role settings prioritized the most important workloads.

You need to configure the failover cluster with one of the following options:

Node majority (no witness) - Only nodes have votes. No quorum witness is configured. The cluster quorum is the majority of voting nodes in the active cluster membership.

Node majority with witness (disk or file share) - Nodes have votes. In addition, a quorum witness has a vote. The cluster quorum is the majority of voting nodes in the active cluster membership plus a witness vote.  A quorum witness can be a designated disk witness or a designated file share witness.

No majority (disk witness only) - No nodes have votes. Only a disk witness has a vote. The cluster quorum is determined by the state of the disk witness.  The cluster has quorum if one node is available and communicating with a specific disk in the cluster storage. Generally, this mode is not recommended, and it should not be selected because it creates a single point of failure for the cluster.


show variables command constantly running on user I have a bit of an odd problem.  I have a number of application user accounts.  One account per application rather than one account per user.

I have one application that seems to be hammering the systems resources.  CPU usage is at 99% for MySQL from this single user.  If I kill the application then usage for the server goes back down to about 20%.

This user account appears to be continuously running the "show variables" command... as far as I can tell, there is absolutely no reference in the application code to "show variables"... though I'm not sure even that should slow down the machine that much... there are also a handful of queries that appear multiple times in the connections list.

So I have two questions/problems:

Is there any reason the "show variables" command would be running constantly like this?  Any other sql commands that would cause "show variables" to fire?

AND

Why would I be seeing so many queries getting duplicated for this user?  If I have it running from a single PC, that PC generates somewhere around 30-50 connections and many of them are for the same query.  So is it running the same query multiple times when it does this?

I'm trying to pinpoint the source of these oddities, so if anyone can point me in some general direction I'd appreciate it.

This specific command, "SHOW VARIABLES" is usually run a lot by "monitoring tools". Check if your monitoring tools are using this account.

Some other symptoms to tell if it is a monitoring system:

Using INFORMATION_SCHEMA related queries
SHOW STATUS command

HTH


Is DBSNMP used only by Oracle EM by default? I have Oracle database and I need to install agent for Oracle EM. It requires credentials for DBSNMP user, but it was lost. Is it safe to change password via SYS user to provide it to agent?
Is there any other systems in standard Oracle distribution that connects via DBSNMP user? I'm afraid this user can be used somewhere else.
If it matters database version is 12c, agent and OEM are 13c.

dbsnmp is used by the monitoring agent to connect on the database and report any issues to OEM which will decide if the issues require attention and announce oracle and customer, otherwise it will just classify and display as warnings.

You can change the password anytime as long as the agent "knows" it.


Change rowguidcol property to a different column In my SQL Server 2008 database, I have a table with two uniqueidentifier columns.

CREATE TABLE MyTable (
    Col1 uniqueidentifier rowguidcol not null,
    Col2 uniqueidentifier not null,
    AttachmentData varbinary(max) filestream null
)

Now, I want to switch the column that has the rowguidcol identifer on it by moving it from Col1 to Col2, so it would end up being

CREATE TABLE MyTable (
    Col1 uniqueidentifier not null,
    Col2 uniqueidentifier rowguidcol not null,
    AttachmentData varbinary(max) filestream null
)

When I try to do this through the table designer (by setting Col1.RowGuid = No and Col2.RowGuid = Yes), I receive the following error:

- Error modifying column properties for 'Col1'.
A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

Is there any way to switch the column with the rowguidcol identifier, or should I rename this table, make a new one, and then copy the data over from the old table?

First of all, the Table Designer is not the wisest of all software and probably does not choose the approach to change the table that you would expect.  The designer usually creates a new table, moves the data, then drops the original table.  However, based on your error the designer also may not be FILESTREAM savvy.

But if you try it yourself through ALTER commands, you will see that SQL Server cannot alter a column to remove the rowguidcol setting.   See what happens:

ALTER TABLE TABLEName ALTER COLUMN Col1 uniqueidentifier DROP ROWGUIDCOL;

Gives the error: Cannot alter column 'Col1' because it is 'ROWGUIDCOL'.

Acknowledgements to Martin Smith.  I overlooked the ROWGUIDCOL operators, which are:

ALTER TABLE MyTable ALTER COLUMN Col1 DROP ROWGUIDCOL;
ALTER TABLE MyTable ALTER COLUMN Col2 ADD ROWGUIDCOL;

However, because of the involvement of the FILESTREAM the DROP ROWGUIDCOL command cannot be processed.

So, yes, you need to create a new table and move the data.


Aggregate function that picks any/first/last/random value I run a query which looks like below

WITH src AS (
    ...
)
SELECT MAX(field1), array_agg(field2)
FROM src

I have knowledge that field1 is the same across all rows returned from src so I really don't really care whether the aggregate function used with it is MAX or MIN. In my case field1 is an array so I am curious if there is a more efficient way to grab any value of field1 which doesn't include overhead of comparing it with the previously saved MIN/MAX. If there was a function to grab the first / retain the last value of field1 that would be ideal.

I believe I found a more efficient SQL for what I am trying to achieve

WITH src AS (
    ...
)
, identical_field1 (
    SELECT field1 FROM src LIMIT 1
)
SELECT (SELECT field1 FROM identical_field1), array_agg(field2)
FROM src

Sub-select on field1 is evaluated only once and only requests one single row from src.

I am not marking it as an answer for now 'cause someone may still suggest a more efficient way.


How is localhost resolved by Oracle listener? When I o an Oracle test installation on a Windows server accepting mostly the defaults, I end up with a listener.ora like

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

THat never seems to enable the listener, to accept requests from remote machines.
I have to add a line with the actual IP address or DNS-name of the server. Giving something like

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 169.254.239.75)(PORT = 1521))
    )
  )

From Technet I know, that Windows has ways to resolve localhost others than reading from  etc/hosts. And typing

ping localhost

shows this.

Each time I do a test installation I have to manually edit listener.ora and tnsnames.ora files and replace the localhost by actual data.

Is the problem to resolve localhost caused by having more than one network adapters?

My normal test configuration is a virtual machine with one adapter connected to the internal network and another connected to the internet via NAT.

Is localhost in those files thought as template to be replaced by a real dns-names or IP-adresses or are there true server-configurations, where the files can be used as generated by DBCA?

By definition localhost traffic is never supposed to enter or leave the server.  It is a name for the addresses 127.0.0.1 and ::1.  It will be bound to a loopback device.  localhost is useful to allow IP access to services running on the local machine.

You likely want to use the host name of your virtual server rather than localhost.  This should resolve to you network interfaces.


Making a column unmodifiable/non-updatable after its row's initial creation? What is the most idiomatic way to make a column unmodifiable/non-updatable after its initial creation via INSERT in Oracle?

CREATE TABLE my_table (
  id           NUMBER       NOT NULL CONSTRAINT my_table_pk PRIMARY KEY,
  -- lots of other columns that are updatable
  crt_ts       TIMESTAMP(6) NOT NULL default CURRENT_TIMESTAMP, -- immutable?
  upd_ts       TIMESTAMP(6) NOT NULL
)

Is there a constraint or technique that can easily be applied to the crt_ts column that does not allow it to be modified on an UPDATE for a single user account.

I know I could hack the permissions to restrict writes to that table and that column and/or do some hacky triggers, updatable views, etc. Those solutions would not be maintainable because I have dozens of tables with columns that have this business logic requirement to be immutable and the database is already in production. I am not sure I might not be missing some low impact solution that is named something strange, because you know, Oracle.

Ideally I would like the crt_ts not to be INSERTable either and just force the default but that might be a stretch.

GRANTS

Oracle has column level GRANT capabilities.

Therefore, the answer you seek:
Don't GRANT the update privilege on that column of that table to that user.

grant insert,select,delete on my_table to some_role;
grant update (updatable_column, upd_ts) on my_table to some_role;
grant some_role to single_user;

VPD/FGAC

Note: Virtual Private Database (VPD) and Fine Grain Access Control (FGAC) require Enterprise Edition (EE);  18c XE also supports VPD/FGAC

VPD allows you to add a WHERE clause based specific conditions.  In this example, the function always_false generates the appropriate conditions.

create function always_false return varchar2 deterministic
as
begin
  if sys_context('USERENV', 'SESSION_USER' ) = 'SINGLE_USER'
  then
    return '1=0'
  end if;

  return null; -- does not apply a rule
end;
/

begin
  dbms_rls.add_policy (
     object_name       => 'MY_TABLE'
    ,policy_name       => 'NO_CRT_TS'
    ,policy_function   => 'ALWAYS_FALSE'
    ,statement_types   => 'UPDATE'
    ,enable            => 'TRUE'
    ,static_policy     => 'TRUE'
    ,sec_relevent_cols => 'CRT_TS'
  );
end;
/

note:  I haven't tested this code

TRIGGER

I wouldn't use TRIGGER to enforce this because triggers can be by-passed.


Fetch records only for today's date by using PHP & Mysql I have a column in my table as date and time format like YYYY-MM-DD HH:MM:SS
And i want to fetch data which was stored on current date only, everyday I'll be getting data for 24hrs only,

You have to use DATE() function:

SELECT *
  FROM table
 WHERE DATE(col1) = DATE(NOW()) -- date parts are equal (HH:MM:SS are dropped)
 ORDER BY col1 ASC
;

Keep in mind that such condition can't be covered by index and can be slow/very slow on a big tables. If the query performance is inappropriate you have to store the date and time parts separately. Columns of types DATE and TIME can be indexed by single multicolumn index.


Help with a complicated MySQL Query inserting data using select from 2 tables I am stuck with a case and unable to get the desired result from my query..

I have to insert into a table some records based on multiple records from 2 different tables..

Tables: Target: player_list_items Source1: list_items Source2: map_details

A player is supposed to play a list which contains a list of items; A list needs to be played over a map.

The map contains placeholders with their certain X and Y positions and I need to place list_items behind those placeholders. for that I have created the target table that will contain the list_items positioned randomly behind the placeholders.

Below is the query in which I was able to dump the list_items for a user into the target table but, now the problem is that how to get X and Y positions randomly for each list_items from the source2 table.

My First Query:

INSERT INTO player_list_items
(player_list_list_id, player_list_player_id, player_list_item_id, player_list_item_cellX, player_list_item_cellY)

SELECT li.list_item_list_id, 584488596, li.list_item_item_id, 2, 5
FROM list_items li
WHERE li.list_item_list_id = 2

Each list contains multiple items so the above query returns multiple items and its working fine, what needs to be considered is that the map_details table also contains multiple place holders and they would be more than the # of list_items for sure.

The required query is supposed to get all positions X and Y from the map_details table and assign then randomly to each individual item, positions for every item must be unique.

I have seen examples of multiple selects for insert but they are for single row insertion and my problem is multiple rows from multiple tables and random too.

My 2nd query:

INSERT INTO player_list_items (player_list_list_id, player_list_player_id, player_list_item_id, player_list_item_cellX, player_list_item_cellY)

SELECT list_item_list_id, 656328662, list_item_item_id,  game_map_details.cellX, game_map_details.cellY
FROM list_items
JOIN (SELECT * FROM game_map_details WHERE map_id = 1 ORDER BY RAND()) AS game_map_details
WHERE list_item_list_id = 2
GROUP BY list_item_item_id

This do adds the data from the map_details table but the data is not random, in fact its inserting only 1 pair of record for all 4 records from list_items table

Okay its done, the cursor usage worked

here is the code m using

UPDATE player_list_items SET player_list_item_cellX = cell_X, player_list_item_cellY = cell_Y
             WHERE player_list_id IN (
                 SELECT player_list_id FROM (
                 SELECT player_list_id FROM player_list_items
                 WHERE player_list_player_id = playerID AND player_list_list_id = @listID AND player_list_item_cellX IS NULL
                 ORDER BY player_list_id LIMIT 1
                 ) tmp
             );

please let me know if this solution is not a good one... as I found nothing besides this to update multiples rows with different values..

its in a loop against cursor that have all the map_details data needs to be put in this table


postgreSQL hot_standby pg_dump on slave I have master-slave (hot_standby) pgsql configuration and I want to make backups from one of my slaves. I don't want PITR, just good old database dump.

I switched to postgres from MySQL. With MySQL replication I made backups from slave with these steps:

stop slave;
perform mysqldump
start slave

Can I do something similar with postgres?

I tried pg_dump on slave, but it returned errors:

pg_dump: Dumping the contents of table "news_event" failed:   PQgetResult() failed.
pg_dump: Error message from server: ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

Also, pg_dump works good on master so I can do backups on master server, but I prefer to make it on slave.

Just run pg_dump.  The documentation says:

  pg_dump is a utility for backing up a PostgreSQL database. It makes
  consistent backups even if the database is being used concurrently.
  pg_dump does not block other users accessing the database (readers
  or writers).

In turn, the state of the database that you see on the slave is also consistent.  If there is a considerable delay in replication, the result may be slightly different than a dump from the master.


Best way to sync a table between two sql server databases with an identity key I have an SSIS package that moves data from a table into another table in another database.  The source table has an identity key.

I need to perform some operations on the data.  The only way I can see to do this is to disable the identity key in the destination table and then use the alter table switch approach outlined here: https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column-sql-server.

Is there an easier way to migrate data, preserve a key and then restart the identity column?

I would go this route:

SET IDENTITY_INSERT <table> ON
...
DBCC CHECKIDENT(<table>)
SET IDENTITY_INSERT <table> OFF

SET IDENTITY_INSERT:
http://msdn.microsoft.com/en-us/library/ms188059.aspx

DBCC CHECKIDENT:
http://msdn.microsoft.com/en-us/library/ms176057.aspx


Very slow insert performance on large table (EF) One of our systems has a table that receives around 20k inserts per day - it currently contains ~10m rows. We've just pushed out a system upgrade and I'm intermittently getting some shockingly slow performance on inserts to this table now (anything from 5 to 40s traces). Inserts will only ever insert a single row at a time. Entity Framework is generating the following SQL:

declare @generated_keys table([ID] uniqueidentifier)

insert [dbo].[Table]([Col1], Col2], etc)

output inserted.[ID] into @generated_keys values (@0, @1, etc)

select t.[ID] from @generated_keys as g join [dbo].[Table] as t on g.[ID] = t.[ID] where @@ROWCOUNT > 0

Nothing out of the ordinary - at least as far as I can see.

The table has 12 FKs. As the entity represents a time-based event, the clustered index is on a DateCreated column. There are 8 further non-clustered indexes, with various included columns. The ID column is the PK and has a unique, non-clustered index.

As a note, I have noticed that, due to an omission, the ID column is using newid() instead of newsequentialid().

The actual execution plan comprises the following major parts:

7x 3% - Clustered Index Seek on FK tables

1x 8% - Table Insert (@generated_keys)

1x 74% - Clustered Index Insert, 1 actual row, 0.09 cost

I'd really like to get to the bottom of this but I'm stumped now. Any advice?

It appears that the dates you are clustered on are not being inserted sequentially.  If your date field was recent purchases for example they would be inserted at the 'end of the table'.  Because it is taking so long to do the insert it seems that SQL is having to reorder your data with each insert instead of tacking it on the end.

Determine if your clustered index is important to reads.  It is always a balance on input / output when looking at indexes.  It may be that a different column is best for clustered index.  I almost always use an integer or long primary key with no business value for the clustered index and then index dates, maybe with included columns, for read performance.

https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/


Pros and Cons of building a relationship model where all table ids are just "_id"? I have always had the pk of the table as this pattern: lowercase tablename + Id.
That was what I was taught in college and didn't think about it. When I was challenged on it from someone using mongo db (a non-relational db) I couldn't figure out why I have ever done it this way?
The only thing I can think about is that some ORMs have it so that the auto build pk columns that normally match this pattern.
Any insight would be greatly appreciated.

There's no weighty reason to do one or the other.  Both styles are common and work fine.
AFAIK:
Pro: You always know how to join, as foreign keys use the same column name in each table, and join queries are easier to validate.
Con: You can't declare a supertype in your app that has the primary key as a property.  IE it's a relatively common pattern in .NET to have
public abstract class Entity
{
    public int Id { get; set; }
}

as a common base type for your entitites.
Some may argue that you shouldn't be adding synthetic keys to tables unless there's an actual need, but that's really a different issue.


MySQL - Set trigger BEFORE INSERT for new ID is safe? How to create customize primary key in MySQL?, example i have table and the table name is X, I have a table field as ID,Code,Name.

  I am afraid if I have 1000 users and when they input together will
  result in destruction

and i want to :

INSERT INTO `X` (`ID`,`Code`,`Name`) VALUES
('P3K','Alex'), // this primary key is "P3K-1"
('SOS','Force'), // this primary key is "SOS-1"
('P3K','Bash'), // this primary key is "P3K-2"

Right now, i using TRIGGER (BEFORE INSERT) for this, like this one:

SET NEW.`ID` = CONCAT(NEW.`Code`,'-',IFNULL(SUBSTRING_INDEX((
  SELECT `x`.`Code` FROM `X` WHERE
    X.`Code` = NEW.`Code` and
ORDER BY X.`Code` DESC
LIMIT 1 ),'-',-1),0) + 1))

I did not try this code, but my point is:

User insert
Before insert I checking LAST Primary
IF Null then i set 0, else i cut the symbol (-) and take the last part
I increments (using [+ 1])
Final, i concat CODE and New Number.

am i misguided? LOL, and if true, how to create like this one?
(I THINK) We can do it and maybe no one knows about this, how does AI in MySQL work so perfectly?

Do not make auto_increment any column you want to manipulate explicitly. That can confuse an engine and cause serious problems. If no column you have used for primary key are auto_increment you can do anything you want with them via triggers. Sure generated values will be rejected if they violate the mandatory uniqness of the primary key.


Understanding MS SQL Server encryption and backups So I was asking in chat about how secure SQL Server backup files are.

I'm told that if an attacker has access to the .bak file unencrypted that they can have access to the data.

So lets look at this scenario :

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

notice - there is no password here.

and then we encrypt our tables by :

UPDATE tbl1
 SET namePAss = ENCRYPTBYKEY(KEY_GUID('MySymetricKey'),name)
GO

Now lets say a hacker got my bak file. All he has to do in order to view the data (in his own computer & sql-server) is:

SELECT
       convert( NVARCHAR(max), decryptbykey(namePAss))
FROM tbl1

Would he still be able to access the data?

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION`
 BY CERTIFICATE MyCertificate`

  notice - there is no password here.

Yes, there is. You opened the certificate MyCertificate somehow, didn't you? There could be several cases, but any of them ends up with a password:

you opened the certificate using a password (obviously, there is a password in this case)
the certificate is encrypted with the database master key and you opened the database master key using the DBMK password -> that's the password
the certificate is encrypted with the database master key and the DBMK is encrypted with the service master key. The service master key was in turn opened using the service account DPAPI storage key, which is encrypted with the account password -> and that is the password

In the last key there seem to be no password, but there is one: the service account password. The backup, when restored on a different host, it will lack the service master key encryption of the database master key and this will make the data inaccessible.

See Encryption Hiearchy


Understanding repeatable read isolation level Wikipedia defines following terms:

  With a lock-based concurrency control DBMS implementation, serializability isolation level requires read and write locks (acquired on selected data) to be released at the end of the transaction. Also range-locks must be acquired when a SELECT query uses a ranged WHERE clause, especially to avoid the phantom reads phenomenon.
  In repeatable reads isolation level, a lock-based concurrency control DBMS implementation keeps read and write locks (acquired on selected data) until the end of the transaction. However, range-locks are not managed, so phantom reads can occur.

As long as I interpret it correctly, both serializability and repeatable read isolation levels corresponds to rigorous two phase locking (in which both shared and exclusive mode locks are held till transaction commits/aborts), in which transactions are serializable by their commit order, according to the book by Korth et al..

However, book by Korth et al also says following repeatable read isolation level:

  Repeatable read allows only committed data to be read and further requires
  that, between two reads of a data item by a transaction, no other transaction
  is allowed to update it.
  However, the transaction may not be serializable
  with respect to other transactions. For instance, when it is searching for data
  satisfying some conditions, a transaction may find some of the data inserted
  by a committed transaction, but may not find other data inserted by the same
  transaction.

My doubt is, if rigorous 2PL schedules are serializable by commit order of its transaction, then why book by Korth et al says repeatable read isolation level may not ensure serializability?

Serializable means that there is some order the transactions can be run in without overlapping and we'll end up with the same answers and the same state of the database as we get by running the transactions in parallel with serializable isolation level.
Given two transactions, A and B, the only valid states of the system are

All of A followed by all of B, OR
All of B followed by all of A.

That's it. If the system can end up in any other state the transactions are not serializable. If we can show that two transactions running in parallel do not correspond to one or other of these states then those transactions are not serializable.
Think about a table with 4 rows, ID values 2, 4, 6 and 8. There are two transactions A and B. A counts the number of rows. B inserts two rows, ID values 3 and 7.
If they run A->B then A returns 4. If they run B->A then A returns 6. Those are the only possible answers if we are to guarantee serialization.
So A starts under Repeatable Read isolation. It will perform a table scan. A reads row 2 and takes a lock, then reads row 4 and takes a lock.
Now B starts in parallel with A. B tries to insert row 3. Nothing prevents it; A has never read a row with ID 3 to take a lock on it. Then B inserts row 7 and commits, releasing its locks.
Transaction A continues reading. It has just finished with 4 so the next row is 6, then comes 7 (Tx B has committed so its lock on 7 has been released) and finally 8. So A has seen rows 2, 4, 6, 7 & 8 - five rows! This workload is not serializable.
This is the scenario in the second bullet point in the quote from Korth.
The "problem" is the phantom rows produced by B. They overlap the range of data to be read by A. But Repeatable Read does not issue range locks so B is free to do this. Under Serializable isolation B's insertion locks would have prevented A taking range locks or A's range locks would have blocked B's insertions.


MongoDB Ops Manager - CPU - No Data Available I have what I would consider a default installation of the latest version of MongoDB Ops Manager and MongoDB.

Everything is working except the Hardware > CPU monitor for my server.

Attached screenshots of the configuration and the screen showing no data.

CPU not showing data

Server Info

Ops Manager Version - 3.4.6

Auth Schema - 5

Feature Compatability - 3.4

Advanced startup options (default) -

nojournal - false

logappend - true

syslog - false

slowms - 100

profile - 1

Log Path - /var/log/mongodb/mongod.log

Ref:

https://docs.cloudmanager.mongodb.com/tutorial/configure-monitoring-munin-node/

To chart the hardware statistics collected with Munin, Cloud Manager supports the following munin-node plugins:

cpu plugin, which creates the cputime chart.
iostat plugin, which creates the iostat chart.
iostat_ios plugin, which creates the iotime chart.

  You must install the munin-node package on all of the host systems
  that you wish to monitor. Ensure that the Monitoring Agent can connect
  to the munin-node process on port 4949 of the monitored host to
  collect data.


Restoring ANY database to Azure MI fails Our freshly provisioned Azure SQL managed instance fails restoring backups from URL.

According to the server log the restore itself seems to finish after a certain amount of time but errors in following processes lead to a termination of the whole process...

The error message prompted by SSMS is

  Msg 22003, Level 16, State 1, Line 7
      Stale/aborted version cleanup was aborted for database id '5' due to database shutdown.
      Msg 3013, Level 16, State 1, Line 7
      RESTORE DATABASE is terminating abnormally.

The relevant part of the server log can be found here.

I tried backup files from different SQL Server versions (2012, 2016, 2019) -- even the backup of an empty database causes this error...

Did anybody experience something similar? Google does not seem to help at all.

The command used is:

RESTORE DATABASE Test FROM  URL = N'https://STORAGEACCOUNT.blob.core.windows.net/STORAGECONTAINER/Test.bak'

The necessary credential got created via:

CREATE CREDENTIAL [https://STORAGEACCOUNT.blob.core.windows.net/STORAGECONTAINER]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=xxxxxxxxxxxxxxxxxxxxxx';

I removed STORAGECACCOUNT and STORAGECONTAINER as well as most of the SECRET.

Just got feedback from Microsoft, this is a known issue which is currently being fixed.

As per their recommendation creating the backups with the option CHECKSUM leads to restorable backups.

BACKUP DATABASE [DBNAME] TO URL = N'https://<storage_account>.blob.core.windows.net/<backup_container>/on_prem_db_full.bak'
WITH CHECKSUM


What is the "SYS_C00054_14031410:34:03$" column in my regular Oracle table? Tried to do partition exchange and it failed because of 2 extra SYS_* columns in the table:

select NAME from SYS.COL$ WHERE obj# =41900658 and name like 'SYS_%';

SYS_C00054_14031410:34:03$
SYS_C00055_14031410:34:03$

ALL_TAB_COLUMNS show nothing on these

What are these? and why CTAS didn't pick them up?

If extended stats - how do i drop them?

Unused columns.

SQL> create table t1 as select * from dba_roles;

Table created.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLE                                      NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                                  VARCHAR2(8)
 AUTHENTICATION_TYPE                                VARCHAR2(11)

SQL> alter table t1 set unused (AUTHENTICATION_TYPE);

Table altered.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLE                                      NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                                  VARCHAR2(8)

SQL> select column_name from user_tab_cols where table_name = 'T1';

COLUMN_NAME
------------------------------
SYS_C00003_18012517:25:39$
PASSWORD_REQUIRED
ROLE

SQL> alter table t1 drop unused columns;

Table altered.

SQL> select column_name from user_tab_cols where table_name = 'T1';

COLUMN_NAME
------------------------------
PASSWORD_REQUIRED
ROLE

Edit:

Extra information: I used *_TAB_COLS, because *_TAB_COLUMNS does not list such columns.


Table design for “Users & Groups” plus “Groups within Groups” I need to represent users and groups, where groups can be nested within other groups.

(Not my design choice – I am replicating another system and must mimic this behavior. I have no access to its implementation details, only its user documentation.)

Putting a group into other groups creates a hierarchy. The users of one group placed in another group gains the access privileges of both groups. For example, if you place the Executives group in the Accounting group, the users of the Executives group enjoy the privileges of both groups. However, users placed only in the Accounting group benefit from the privileges of that group only. (Admittedly, not a likely realistic business scenario, just a contrived example.)

Something like this ERD diagram, I suppose.

I know how to handle a Many-to-Many relationship of user_ to group_. But I have no idea:

How to represent the nested group-within-group, where a group may belong to zero, one, or more than one other group.
How to query “Is this group X in group Y”.
How to query “Is this user Bob in group X” where “group X may be either directly assigned to that user or inherited via group-within-group”.

Is this group-within-groups considered a “recursive” relationship? What is the correct technical term?

Possibly related: Recursive query in SQL Server and Recursive SQL query to identify

I'd just go straight for a self-referencing key ( adjacency list, hierarchy relationship, recursive relationship, I have no idea what the "most correct and proper" term for it is, but any of these should get the point across to a sufficiently tuned-in audience ) on what would effectively be a "principal" or "role" table, along with a membership table.  While I'd draw my inspiration from SQL Server, PostgreSQL also does this for it's own security management, like what is shown through \du or \dg.  That said, there may even be value in considering trying to handle all of this with the built-in security management:

CREATE ROLE accounting;
GRANT SELECT ON account TO accounting;
CREATE ROLE executives;
GRANT accounting TO executives;
CREATE USER cto_bill;
GRANT executives TO cto_bill;

"CTO Bill" should now be able to SELECT from the account table, via the executives role through to the accounting role, all completely out of the box.  If an application needs to know the chained permissions, they could be exposed via \du or some form of query against the pg_catalog's pg_auth_members and pg_roles tables:

SELECT  r.rolname, r.rolsuper, r.rolinherit,
        r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
        r.rolconnlimit, ARRAY(  SELECT  b.rolname
                                FROM    pg_catalog.pg_auth_members m
                                INNER JOIN pg_catalog.pg_roles b
                                    ON  ( m.roleid = b.oid )
                                WHERE   m.member = r.oid ) as memberof
FROM    pg_catalog.pg_roles r
WHERE   r.rolcanlogin = 'f'
ORDER BY 1;

If a total nuts-and-bolts solution is deemed necessary, you could build a schema similar to this:

CREATE TABLE Principal
(
    Principal_PK             SERIAL NOT NULL,
    Name                     VARCHAR( 128 ) NOT NULL,
    PasswordHash             BYTEA,
    Type                     CHAR( 1 ) NOT NULL
);

ALTER TABLE Principal
ADD CONSTRAINT PK__Principal
    PRIMARY KEY ( Principal_PK );

ALTER TABLE Principal
ADD CONSTRAINT UQ__Principal__Name
    UNIQUE ( Name );

ALTER TABLE Principal
ADD CONSTRAINT CK__Principal__Type
    CHECK ( Type IN ( 'U', 'G' ) );

CREATE TABLE PrincipalMembership
(
    PrincipalMembership_PK   SERIAL NOT NULL,
    Principal_FK             INTEGER NOT NULL,
    IsMemberOfPrincipal_FK   INTEGER NOT NULL,
    DateCreated              TIMESTAMP NOT NULL
);

ALTER TABLE PrincipalMembership
ADD CONSTRAINT PK__PrincipalMembership
    PRIMARY KEY ( PrincipalMembership_PK );

ALTER TABLE PrincipalMembership
ADD CONSTRAINT FK__PrincipalMembership__Principal
    FOREIGN KEY ( Principal_FK )
    REFERENCES Principal ( Principal_PK );

ALTER TABLE PrincipalMembership
ADD CONSTRAINT FK__PrincipalMembership__IsMemberOfPrincipal
    FOREIGN KEY ( IsMemberOfPrincipal_FK )
    REFERENCES Principal ( Principal_PK );

ALTER TABLE PrincipalMembership
ADD CONSTRAINT UQ__PrincipalMembership__Principal__IsMemberOfPrincipal
    UNIQUE ( Principal_FK, IsMemberOfPrincipal_FK );

ALTER TABLE PrincipalMembership
ALTER COLUMN DateCreated SET DEFAULT ( NOW() );

INSERT  INTO Principal ( Name, PasswordHash, Type )
VALUES  ( 'accounting', NULL, 'G' ),
        ( 'executives', NULL, 'G' ),
        ( 'cto_bill', E'\\x00', 'U' );

INSERT  INTO PrincipalMembership ( Principal_FK, IsMemberOfPrincipal_FK )
SELECT  p.Principal_PK, m.Principal_PK
FROM (      SELECT  'executives', 'accounting'
UNION ALL   SELECT  'cto_bill', 'executives' ) pl ( Name, IsMemberOf )
INNER JOIN Principal p
    ON  p.Name = pl.Name
INNER JOIN Principal m
    ON  m.Name = pl.IsMemberOf;

Then use pretty much any tree-traversing method you'd like, such as a Recursive CTE, to determine various membership properties:

;WITH RECURSIVE cte_Membership AS (
    SELECT  p.Principal_PK, p.Name, pm.IsMemberOfPrincipal_FK
    FROM    Principal p
    LEFT JOIN PrincipalMembership pm
        ON  pm.Principal_FK = p.Principal_PK
    UNION ALL
    SELECT  c.Principal_PK, c.Name, pm.IsMemberOfPrincipal_FK
    FROM    cte_Membership c
    INNER JOIN Principal p
        ON  p.Principal_PK = c.IsMemberOfPrincipal_FK
    INNER JOIN PrincipalMembership pm
        ON  pm.Principal_FK = p.Principal_PK )
SELECT  m.Name, p.Name AS IsMemberOf
FROM    cte_Membership m
LEFT JOIN Principal p
    ON  p.Principal_PK = m.IsMemberOfPrincipal_FK
ORDER BY m.Name, IsMemberOf;

Edit: It's probably very much worth mentioning that management of solutions involving self-referencing keys and recursive traversals can go tits up in a hurry if caution isn't taken to ensure no cyclic references are made.  If somebody were to "accidentally" make accounting a member of the executives group, the sample CTE would no longer function, and as the complexity of the hierarchy increases, so to does the likelihood of such an error.  Safeguarding against cyclic references is a bit of a pain in the ass, frankly, but hiding CRUD operations against these tables in stored procedures can help immensely in cases like this one.  Since you're attempting to replicate a preexisting solution, perhaps there's some guidelines in that system which can assist in developing a suitable litmus test for the new one.


Easy SQL, embedded select I have a table of zip codes with latitudes / longitudes.  Unfortunately it doesn't seem to have a few zip codes.   I would like to try to approximate the latitude / longitude based on other zip codes that are at least in the area.

So for zip code 52733 which is not in my database,  I was thinking of taking the average latitudes of all other zip codes that are "LIKE 5273%".

 select ZipCode as z,
      (select AVG(Latitude)
       from tlkp_ZipCodes
       where ZipCode like LEFT(z, 4) + '%' )
 from tlkp_ZipCodes

That doesn't seem to work though.  It says "z" is an invalid column name.  How do I do this?

It seems that you would like to reference ZipCode in the OUTER query from the scalar subquery. You cannot reference the alias z since it has only just been defined in the same SELECT clause. The correct way to do this is just to use the base column, but to alias the table differently to prefix the column names with the aliases.

select a.ZipCode as z,
  (select AVG(b.Latitude)
     from tlkp_ZipCodes b
    where b.ZipCode like LEFT(a.ZipCode, 4) + '%' ) AvgLatitude
 from tlkp_ZipCodes a


How to make a flexible service broker receive in a stored sql procedure, how to pass "FROM" parameter into receive a while ago I asked a question about how to make begin dialog and send more flexible so that it can be embedded in a procedure that takes the to, from, parameters as sysname variables.

Service Broker Procedure that will be used by multiple initiators to begin dialog conversation.. how to pass a parameter into it

however as Rusanu mentioned in the answer, this same technique cannot be used for the From clause of the Receive.

  Actually it will work. Most SSB verbs accept parameters for they arguments (except queue name for RECEIVE, of course). The parameters are of type sysname ...

actually the send side is done and I am now trying to make the RECEIVE flexible in the same way, something like:

CREATE PROCEDURE QueueReceive
  @myTargetQueue SYSNAME
  @cg UNIQUEIDENTIFIER OUTPUT
  @ch UNIQUEIDENTIFIER OUTPUT
  @msg XML OUTPUT
as
    BEGIN TRANSACTION;
    WAITFOR
            ( RECEIVE TOP(1)
                @cg = conversation_group_id,
                @msg = cast(message_body as XML),
                @ch= conversation_handle
              FROM @myTargetQueue
            ), TIMEOUT 3000;
    COMMIT
...

It seems variables of type sysname cannot be used in the from clause of the RECEIVE?  If I have to do it in dynamic SQL, how would I return all the variables, conversation_group_id, conversation_handle, etc. from the dynamic sql execute of a receive function?  is there a better performing technique to accomplish the same thing?

Thank you.

FIX/Update SO FAR:
I am creating a bunch of IF clauses depending on what the passed parameter is, it will just execute a different whole receive statement.  It is not efficient because I have to change procedure code any time I add a new QUEUE, but I guess it will do for now...

Because RECEIVE is basically a DELETE and as such has a query plan, it must obey the same restrictions SELECT/INSERT/DELETE/UPDATE statements have, specifically the restrictions that the object it acts on must be known at compile time, not at execution time.

The only option is to use dynamic SQL, with all the blessings and pitfalls that follow.

You could also generate the procedure body during project deployment, have a single template and generate a specific procedure for each queue, specialized for the specific queue name. Whether this is feasible depends on many factors, first and foremost on the organization of your project and how you deploy.

On a side note I'm surprised to hear that you have many queues. In general the tendency is to have a single queue and several queue readers (activated procedures). Since SSB programing is event driven (wait for for message, process message, wait for message, process message, wait for message...) having more than one queue to wait for messages on it becomes harder, as the application now has to wait on multiple sources (eg. one thread per queue at least). Even with SSB activation, which alleviates the need for explicit wait wince it launches the code to process the message on-demand, multiple queues are harder to manage (max_queue_readers per queue adds up to perhaps too many internal activated procedures launched). Consider using a single service and queue on the RECEIVE side. Even when multiple services are needed (for whatever reason) they can be consolidated on a single queue.


SQL Server: Pass table name into table valued function as a parameter All of my tables have those two columns:

[Id] [uniqueidentifier] NOT NULL
[Revision] [bigint] IDENTITY(1, 1) NOT NULL

Every other time I write a query, I want to eliminate some of the rows. The elimination conditions are the same for every table. I want an easy way to specify those conditions once and then reuse them on any table.

I defined a table type as a "base class" for my tables

CREATE TYPE EntityTable AS TABLE (
    [Id] [uniqueidentifier] NOT NULL
    ,[Revision] [bigint] IDENTITY(1, 1) NOT NULL
)

And here's a table valued function I create

CREATE FUNCTION MaxRevision (@Entities EntityTable READONLY)
RETURNS TABLE
AS
RETURN

SELECT *
FROM @Entities e
WHERE e.Revision = (
        SELECT max(Revision)
        FROM @Entities er
        WHERE er.id = e.id
        )

I thought I would be able to use it like that

SELECT * FROM MaxRevision(NameOfSomeTableWhichHasIdAndRevisionColumn) WHERE /* some other conditions */

But I'm getting the error

Msg 207, Level 16, State 1, Line 2
Invalid column name 'NameOfSomeTableWhichHasIdAndRevisionColumn'.

Why it treats the table name I pass as a column name? Are there any other ways to implement the behavior I want?

Table-Valued Parameters (TVPs), and T-SQL in general, does not work like that. There is no way to define a partial schema as an interface and then pass in table that matches that interface. For TVPs, you need to pass in a table variable created from that table type. So clearly not the way to go here.

Also, you have said that all of these tables have those two columns, but not that all of these tables have all of the same columns. This is an important distinction because table-valued functions are required to return a consistent result set; the result set cannot be dynamic like it can be with stored procedures. I am mentioning this because your example function does a SELECT * from the TVP, implying that you might be expecting this function to return whatever columns are in whatever table name you pass in. For this function, you will need to return only the two columns that all of the tables have in common: Id and Revision.

That being said, there are two ways you can accomplish this: pure T-SQL and SQLCLR.

PURE T-SQL

The function uses a CASE statement to determine which sub-query to execute. Each sub-query is the same except for the table name (well, in my example the second column name, which is also used in the ORDER BY clause, is different, but in your situation it will always be Revision). Sub-queries can only return a scalar value, so I am using FOR XML to package up the two required columns into one value that can then be unpacked in the outer SELECT.

USE [tempdb];
GO

-- DROP FUNCTION dbo.MaxRevision;
CREATE FUNCTION dbo.MaxRevision(@TableName sysname)
RETURNS TABLE AS
RETURN

SELECT tab.BaseData.value(N'/row[1]/@object_id', N'BIGINT') AS [ObjectID],
       tab.BaseData.value(N'/row[1]/@some_id', N'BIGINT') AS [SomeID]
FROM (
    SELECT CASE @TableName
             WHEN N'objects' THEN (SELECT TOP (1) [object_id], [schema_id] AS [some_id]
                                   FROM   master.sys.tables
                                   ORDER BY [schema_id] DESC FOR XML RAW, TYPE)
             WHEN N'indexes' THEN (SELECT TOP (1) [object_id], [index_id] AS [some_id]
                                   FROM master.sys.indexes
                                   ORDER BY [index_id] DESC FOR XML RAW, TYPE)
             WHEN N'columns' THEN (SELECT TOP (1) [object_id], [column_id] AS [some_id]
                                   FROM master.sys.columns
                                   ORDER BY [column_id] DESC FOR XML RAW, TYPE)
           END AS [BaseData]
     ) tab;
GO

The tests:

SELECT * FROM dbo.MaxRevision(N'objects');

SELECT * FROM dbo.MaxRevision(N'indexes');

SELECT * FROM dbo.MaxRevision(N'columns');

SELECT * FROM dbo.MaxRevision(N'none');
-- 1 row of NULL, NULL

SQLCLR

SQLCLR allows for Dynamic SQL in a TVF, so you can pass in the table name, concatenate it into the query (after you verify that it is, in fact, a table name!), and that's pretty much it.

This option allows for a little more flexibility than the pure T-SQL option, but is also a bit more complicated to implement. Stick with the pure T-SQL option unless you need more flexibility, such as dynamically defining the queries, reading from temporary tables, etc.


Group by unique items in an array column and count records that contain each item I have a table that looks like this:

records
-------------------------------------
id    items[]
-------------------------------------
1     {toaster,piano}
2     {car}
3     {car, toaster}
4     {tv}
5     {toaster}

Items is an array column. I'd like to group by the different 'items' and count the number of records that contain that 'item'. So the result I'd like here would be:

item      count
toaster   3
piano     1
car       2
tv        1

Is this doable or do I really need an items table here?

Thanks!

You just need to unnest the arrays into a long form (which I do by joining against the "unnest" function call), and then re-aggregate them.

select item, count(distinct id) from items, unnest(items.items) as item group by item;

If you have to do this on a regular basis, you might want to rethink how you store the data in the first place.


CXPACKET SUSPENDED and NULL wait type I was was running the query in this article:

http://sqlity.net/en/708/why-cxpacket-waits-are-not-your-performance-problem/

To see what my threads were waiting on in regards to a suspended query with a wait type of CXPACKET.

However for the SPID in question threads that are running were showing wait types of NULL with every other thread in a SUSPENDED state with a wait type of CXPACKET.

I was expecting to one of the threads having some kind of wait type other than CXPACKET, can anyone explain to me what is happening in this situation?

Thanks

The CXPACKET was always the confusing wait type for younger DBAs and some predictably wrong reactions are generally expected. There are multiple acpects of the CXPACKLET wait type and I've tried in Troubleshooting the CXPACKET wait type in SQL Server article to bring on the table most of the reasons for high CXPACKET but also to explain the background of the CXPACKET, as the proper understanding of parallelism in SQL Server is the key for understanding

So for those who don't want to get in details, I will post the summary of the article here (but I would definitely suggest reading of the article for complete info about CXPACKET wait type):

  Do not set MAXDOP to 1, as this is never the solution
  Investigate the query and CXPACKET history to understand and    determine whether it is something that occurred just once or twice,
  as it could be just the exception in the system that is normally
  working correctly
  Check the indexes and statistics on tables used by the query and make    sure they are up to date
  Check the Cost Threshold for Parallelism (CTFP) and make sure that    the value used is appropriate for your system
  Check whether the CXPACKET is accompanied with a LATCH_XX (possibly    with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the
  case than the MAXDOP value should be lowered to fit your hardware
  Check whether the CXPACKET is accompanied with a LCK_M_XX (usually    accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is
  the case, then parallelism is not the bottleneck. Troubleshoot those
  wait stats to find the root cause of the problem and solution


Is there anyway to make this stored procedure thread safe? I have a stored procedure that deletes X rows. This procedure is called by a Job Scheduler (Not SQL Server Agent) from one of three Azure Instances. The deletion task is run every 30 minutes from each instance and almost always results in a deadlock. Basically, multiple/simultaneous calls of this same stored procedure keep blocking eachother out.

Any suggestions on how to make this stored procedure threadsafe ?

CREATE PROCEDURE [dbo].[RawData_d_DeleteObsoleteRawData]
         @RemoteReadingMeasureIdList [IntListType] READONLY,
         @MaxSavingDurationInDays INT
AS
BEGIN

         DECLARE @CurrentDate SMALLDATETIME = CAST(GETUTCDATE() as SMALLDATETIME)
         DECLARE @MaxTime SMALLDATETIME
         DECLARE @TotalDeleted INT = 0

         -- Max time for raw data
         SET @MaxTime = DATEADD(DAY, -@MaxSavingDurationInDays, @CurrentDate)

         -- Delete non remote reading raw data
         DELETE TOP (10000) FROM [dbo].[RawData]
         WHERE MeasureId NOT IN (SELECT Item FROM @RemoteReadingMeasureIdList)
         AND DateEnergy < @MaxTime

         SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

         -- Delete non remote reading raw data (keep 1 raw data per day for remote reading)
         ;WITH cte AS
         (
                 SELECT AreaId, MeasureId, DateEnergy,
                          ROW_NUMBER() OVER (PARTITION BY MeasureId, YEAR(DateEnergy), DAY(DateEnergy) ORDER BY DateEnergy DESC) AS rn
                 FROM [RawData] rd WITH (NOLOCK)
                 INNER JOIN @RemoteReadingMeasureIdList m ON rd.MeasureId = m.Item
                 WHERE DateEnergy < @MaxTime
         )
         DELETE TOP (10000) rd
         FROM [RawData] rd
         INNER JOIN cte ON rd.AreaId = cte.AreaId AND rd.DateEnergy = cte.DateEnergy AND rd.MeasureId = cte.MeasureId
         WHERE rn <> 1

         SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

         SELECT @TotalDeleted as DeletedRow

Update 26/03/2014

Using the READPAST solution recommended by Jon was the best overall option.

To test I created two new queries, each with their own spid

Spid 51

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Begin transaction
Exec [RawData_d_DeleteObsoleteRawData]

Spid 52

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Exec [RawData_d_DeleteObsoleteRawData]

The first SPID does not commit and the second one is run immediately after.

Without the READPAST hint, all other executions of RawData_d_DeleteObsoleteRawData never complete.

With the READPAST hint added to the two DELETEs and the SELECT, all other executions of RawData_d_DeleteObsoleteRawData are able to continue.

Thank you again to wBob and Jon Seigel for their assistance

I know raising the transaction isolation level and adding a transaction can seem counter-intuitive, but sometimes, you have to make things queue up and wait their turn.  Doing those two things worked for me in my simple test rig.  Try it and let us know how you get on.

EDIT: Test rig
OK, nice simple test rig using tempdb.  It's a bit rough and ready but provided an easy repro and provable fix for me.  You should enable SQLCMD mode in SSMS before you run.  I basically pit the proc against itself in multiple sessions using either ostress or GO 100.  Run the initial version of the proc, monitor for deadlocks; I get many.  Then, reset the rig, run in the revised version of the proc and try again.

USE tempdb
GO

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

SET NOCOUNT ON
GO

IF OBJECT_ID('[dbo].[RawData_d_DeleteObsoleteRawData]') IS NOT NULL
DROP PROC [dbo].[RawData_d_DeleteObsoleteRawData]
GO

-- Example table type
IF EXISTS ( SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IntListType' )
    DROP TYPE IntListType
GO

CREATE TYPE IntListType AS TABLE ( Item INT PRIMARY KEY )
GO
-- Create a large table
-- DROP TABLE dbo.rawData
IF OBJECT_ID('dbo.rawData') IS NULL
CREATE TABLE dbo.rawData (
    rowId INT IDENTITY PRIMARY KEY,
    AreaId INT,
    MeasureId INT,
    someData UNIQUEIDENTIFIER DEFAULT NEWID(),
    DateEnergy DATETIME DEFAULT GETDATE(),
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(),
    ts ROWVERSION
    )
GO

TRUNCATE TABLE dbo.rawData

-- Populate table
;WITH cte AS (
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.rawData ( AreaId, MeasureId, DateEnergy )
SELECT areaId.rn, MeasureId.rn, DATEADD( day, DateEnergy.rn % 300, '1 Jan 2014' )
FROM
    ( SELECT TOP 10 rn FROM cte  ) areaId,
    ( SELECT TOP 100 rn FROM cte  ) MeasureId,
    ( SELECT TOP 1000 rn FROM cte  ) DateEnergy,
    ( SELECT TOP 2 rn FROM cte  ) doubleUp
GO

-- Original proc
CREATE PROCEDURE [dbo].[RawData_d_DeleteObsoleteRawData]
         @RemoteReadingMeasureIdList [IntListType] READONLY,
         @MaxSavingDurationInDays INT
AS
BEGIN

         DECLARE @CurrentDate SMALLDATETIME = CAST(GETUTCDATE() as SMALLDATETIME)
         DECLARE @MaxTime SMALLDATETIME
         DECLARE @TotalDeleted INT = 0

         -- Max time for raw data
         SET @MaxTime = DATEADD(DAY, -@MaxSavingDurationInDays, @CurrentDate)

         -- Delete non remote reading raw data
         DELETE TOP (10000) FROM [dbo].[RawData]
         WHERE MeasureId NOT IN (SELECT Item FROM @RemoteReadingMeasureIdList)
         AND DateEnergy < @MaxTime

         SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

         -- Delete non remote reading raw data (keep 1 raw data per day for remote reading)
         ;WITH cte AS
         (
                 SELECT AreaId, MeasureId, DateEnergy,
                          ROW_NUMBER() OVER (PARTITION BY MeasureId, YEAR(DateEnergy), DAY(DateEnergy) ORDER BY DateEnergy DESC) AS rn
                 FROM [RawData] rd WITH (NOLOCK)
                 INNER JOIN @RemoteReadingMeasureIdList m ON rd.MeasureId = m.Item
                 WHERE DateEnergy < @MaxTime
         )
         DELETE TOP (10000) rd
         FROM [RawData] rd
         INNER JOIN cte ON rd.AreaId = cte.AreaId AND rd.DateEnergy = cte.DateEnergy AND rd.MeasureId = cte.MeasureId
         WHERE rn <> 1

         SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

         SELECT @TotalDeleted as DeletedRow

    END
GO
:exit

-- Setup END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Main proc call START
-- Run this section in two or more connections
-- eg using ostress
-- !!ostress.exe -E -S.\sql2008r2 -dtempdb -i"C:\temp\temp.sql" -n10 -r5 -mstress
------------------------------------------------------------------------------------------------

-- Proc call
SET NOCOUNT ON
DECLARE @tvp AS [IntListType]

INSERT INTO @tvp
VALUES ( 1 ), ( 2 ), ( 3 ), ( 5 ), ( 8 ), ( 13 )

EXEC [dbo].[RawData_d_DeleteObsoleteRawData] @tvp, 30
GO 100

-- Main proc call END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Revised proc START
------------------------------------------------------------------------------------------------

/*  Reset the rig and try with the revised proc

ALTER PROCEDURE [dbo].[RawData_d_DeleteObsoleteRawData]
         @RemoteReadingMeasureIdList [IntListType] READONLY,
         @MaxSavingDurationInDays INT
AS
BEGIN

         DECLARE @CurrentDate SMALLDATETIME = CAST(GETUTCDATE() as SMALLDATETIME)
         DECLARE @MaxTime SMALLDATETIME
         DECLARE @TotalDeleted INT = 0

         -- Max time for raw data
         SET @MaxTime = DATEADD(DAY, -@MaxSavingDurationInDays, @CurrentDate)

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    BEGIN TRY

        BEGIN TRAN

            -- Delete non remote reading raw data
            DELETE TOP (10000) FROM [dbo].[RawData]
            WHERE MeasureId NOT IN ( SELECT Item FROM @RemoteReadingMeasureIdList )
            AND DateEnergy < @MaxTime

            SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

            -- Delete non remote reading raw data (keep 1 raw data per day for remote reading)
            ;WITH cte AS
            (
                    SELECT AreaId, MeasureId, DateEnergy,
                            ROW_NUMBER() OVER (PARTITION BY MeasureId, YEAR(DateEnergy), DAY(DateEnergy) ORDER BY DateEnergy DESC) AS rn
                    FROM [RawData] rd
                    INNER JOIN @RemoteReadingMeasureIdList m ON rd.MeasureId = m.Item
                    WHERE DateEnergy < @MaxTime
            )
            DELETE TOP (10000) rd
            FROM [RawData] rd
            INNER JOIN cte ON rd.AreaId = cte.AreaId AND rd.DateEnergy = cte.DateEnergy AND rd.MeasureId = cte.MeasureId
             WHERE rn <> 1

             SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

             SELECT @TotalDeleted as DeletedRow

             COMMIT TRAN

         END TRY

         BEGIN CATCH

            DECLARE @errorMessage NVARCHAR(2048) = ERROR_MESSAGE()

            IF @@TRANCOUNT > 0 BEGIN PRINT 'Error handler rolling back...' ROLLBACK TRAN END

            RAISERROR( @errorMessage, 16, 1 )

         END CATCH

    END
GO
*/

-- Revised proc END
------------------------------------------------------------------------------------------------


UPDATE does not use INDEX, but SELECT does I'm actually struggling understanding the problem here. I read everywhere saying that UPDATE benefits from indexes on the WHERE clause.

However, this query,

UPDATE `documents` SET `read`="1" WHERE `docid` IN (<subquery>)

does not seem to be using an index.
The table documents has an index on docid and on read.

When I do an EXPLAIN, I see possible_keys = NULL and rows = 8011008 (full table). The subquery does use keys and read the proper rows (2 rows).

In the other hand, this query:

SELECT * FROM `documents` WHERE `docid` IN (<subquery>)

does use the index on docid and runs very quickly. It reads a few more rows than needed (as per EXPLAIN), but totally acceptable.

Is there any explanation for this?
I use MariaDB 10.

As a funny note (regarding SELECT), if in the <subquery> I use a UNION, while the subquery seems to get the proper number of rows, it seems the primary query does NOT use the index and does a full table scan.

If in the UPDATE I use JOIN instead of IN, the indexes are properly used. I resolved my problems by using JOIN.

To address the question "UPDATE does not use INDEX, but SELECT does"...

Until very recently, many UPDATEs were processed by different code than SELECTs.  Recently, there was a unification in the Oracle branch.  I don't think it has made its way into MariaDB yet.

Also, the construct IN ( SELECT ... ) has been optimized very poorly until 5.6.  Again, MariaDB may or may not have yet included some of the 5.6/5.7 improvements in this area.

It is almost always better to turn ... IN ( SELECT ... ) into JOIN ... ON ....  Such is possible in UPDATE; see "multi-table UPDATE".

For more discussion of your specific cases, please provide SHOW CREATE TABLE and EXPLAIN SELECT ....

I'm not saying that the Oracle branch necessarily does things better; rather that there could be a difference.


Error reading a concatenate command I'm trying to run a recursive alter table reading a couple of parameters from my sys table.

DECLARE @AlterCmd NVARCHAR(MAX)

SELECT top 1
        @AlterCmd = 'ALTER TABLE [dbo].[table] ALTER COLUMN ' + COLUMN_NAME + ' NVARCHAR(' + CHARACTER_MAXIMUM_LENGTH + ')'
    FROM
        information_schema.columns
    WHERE
        table_name = 'table_name' and DATA_TYPE like '%char%'

print @AlterCmd

In this sample code I just want to validate if the command is coming right but it's not possible to run it due an error in the concatenate. According to the error message I get:

Msg 245, Level 16, State 1, Line 63
Conversion failed when converting the varchar value ')' to data type int.

There are a couple things wrong:

You should always use QUOTENAME() when dealing with concatenated values, both from the perspective that it can prevent SQL injection attacks, and because it prevents errors when invalid characters get used.
Whenever you're concatenating values, you should use NVARCHAR literals and explicit converts to your desired data type and don't rely on implicit conversions.
Use statement terminators.

Code:

DECLARE @AlterCmd NVARCHAR(MAX);

SELECT TOP 1 @AlterCmd = N'ALTER TABLE [dbo].[table] ALTER COLUMN '
                         + QUOTENAME(COLUMN_NAME)
                         + N' NVARCHAR('
                         + CONVERT(NVARCHAR(10), CHARACTER_MAXIMUM_LENGTH)
                         + N');'
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = N'table_name'
       AND DATA_TYPE LIKE N'%char%';

PRINT @AlterCmd;


SSMS Activity Monitor Expensive Query start time I keep seeing rows in "Recent Expensive Queries" for queries that have not been run for hours.

This confuses me when trying to figure what is snarling things up.

Is there a way to add start time column to view - or somehow filter old queries out?

There are no such options!

However, you can create your own result set as shown in the following snippet.

Please, notice a couple of things.

First, you can change a period of time you are interested in by changing the first line of the snippet.

Second, the WHERE clause is applied on the last_execution_time column.

And, finally you can change the order if you are interested in expensive queries based on CPU or logical writes.

DECLARE @filterDateTime DATETIME = '2019-10-16 11:09:00.000'

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset
                                                                            WHEN-1
                                                                            THEN DATALENGTH(qt.TEXT)
                                                                            ELSE qs.statement_end_offset
                                                                        END - qs.statement_start_offset) / 2) + 1),
              qs.execution_count,
              qs.total_logical_reads,
              qs.last_logical_reads,
              qs.total_logical_writes,
              qs.last_logical_writes,
              qs.total_worker_time,
              qs.last_worker_time,
              qs.total_elapsed_time / 1000000 total_elapsed_time_in_S,
              qs.last_elapsed_time / 1000000 last_elapsed_time_in_S,
              qs.last_execution_time,
              qp.query_plan
FROM sys.dm_exec_query_stats qs
     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
     CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE last_execution_time >= @filterDateTime
ORDER BY qs.total_logical_reads DESC; -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time


New instance with same @@VERSION as existing instances I have a SQL Server 2008 R2 server with several instances installed. Is there a way to add a new instance and have it be to the same build as the existing (and updated / patched) instances?

No, unfortunately there is no magic "be like that guy" feature. What you have to do is either:

install RTM and then apply the SP + CU; or
slipstream the same SP + CU into the RTM binaries and install that. These instructions are applicable to both SQL Server 2008 and SQL Server 2008 R2, though the process is a little easier in SQL Server 2012.

The former is easier but could require more downtime.


Migrating development and production databases to AWS After careful thinking and asking on meta.stackoverflow, I'm posting this question on the DBA site. If anyone thinks this question should be on serverfault or any other site, feel free to close and migrate it.

We have a couple of self managed servers, production and development, with some not too big databases on a MS SQL Server 2008R2 express instance. Also we have some other databases hosted on the Amazon RDS service and a couple of web sites too. I've been asked to investigate the option of migrating all the production & development databases to Amazon AWS. Also check the viability of using EC2. Sorry if I'm miss using some terms, I'm just learning about all these services.

Where should I start doing some tests, maybe creating a dummy database to test the different configurations, compatibility issues, latencies, etc? Can I have some recommendation on tutorial/blog/faq's where to find some usefull information? I already opened an account on the "free tier" that Amazon offers for testing purposes and I'm reading everything I can find on the subject, other than the AWS Documentation site.

Any recommendation will be welcomed as well as any previous experience doing this kind of migration to the AWS. I'm willing to add to my question any information that could help to clarify it.

One of my customers is running their entire production site up on EC2.  They've had pretty good luck with it, except for when there is some sort of Amazon caused outage (which there have been a couple of over the last couple of years).

The biggest thing that you need to worry about is planning for an EC2 site failure with your application.  If EC2 has a major failure are you ok with the database being offline for a couple of days, or possibly loosing all the data in the database?  If not then you'll need to handle that via traditional SQL Server DR planning by having another server running as a hot standby server in another Amazon data center.  The same goes for the web servers and anything else that hits the database.

The slowest part of using EC2 is the storage.  There are ways around this by getting lots of volumes from Amazon and striping across them within the VM.

As for the dev machine, that's pretty easy.  Just toss it up there and it should be fine as long as you've got backups going somewhere.


why my split string functions are not working with large OBJECT_DEFINITION? I have in one of my databases a stored procedure

called PROCEDURE [repl].[usp_upd_repl_out_application], that is not exactly a very clever stored procedure but it has over 389 lines.

As I need to change this stored procedure, I like to back it up to a table before any alteration, I am aware of source control applications and that should be the way to go, but I am happy doing things my way and it works for me.

it might be another case of re-inventing the wheel, but I still would like to know why my split strings functions are failing.

So while attempting to backup this stored procedure
this stored procedure is object_id = 146815585 in my_database.

this works perfectly:

DECLARE
  @olddelim nvarchar(32) = char(13) + Char(10),
  @newdelim nchar(1)     = NCHAR(9999); -- pencil (✏)
SELECT *
  FROM STRING_SPLIT(REPLACE(OBJECT_DEFINITION(146815585)
  , @olddelim
  , @newdelim)
  , @newdelim);

this also works nicely, although it is not the way I want it:

if object_id('tempdb..#radhe') is not null
   drop table #radhe

create table #radhe(i int identity(1,1) not null primary key clustered, ln nvarchar(max))
insert into #radhe (ln)
exec sp_helptext '[repl].[usp_upd_repl_out_application]'

I have got this split function from here:

ALTER FUNCTION dbo.fn_TVF_Split(@arr AS NVARCHAR(max), @sep AS NCHAR(1))
RETURNS TABLE
WITH SCHEMABINDING,ENCRYPTION
AS
RETURN
WITH
   L0 AS (SELECT 1 AS C UNION ALL SELECT 1)       --2 rows
  ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)    --4 rows (2x2)
  ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)    --16 rows (4x4)
  ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)    --256 rows (16x16)
  ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)    --65536 rows (256x256)
  ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)    --4,294,967,296 rows (65536x65536)
  ,Nums AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)
SELECT
(n - 1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, N'')) + 1 AS pos,
SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n) AS element
FROM Nums
WHERE
    n <= LEN(@arr) + 1
    AND SUBSTRING(@sep + @arr, n, 1) = @sep
    AND N<=1000
GO

DECLARE
@olddelim nvarchar(32) = Char(10) -- char(13) + Char(10),
,@newdelim nchar(1)     = NCHAR(9999); -- pencil (?)
SELECT m.*
--INTO TableBackups.dbo._MMiorelli_20180305_repluspupdreploutapplication_140331_DB_ID_32
from  dbo.fn_TVF_Split( OBJECT_DEFINITION ( 146815585),  @olddelim) m

it only returns 18 rows out of the 398

and this one below, only returns 60 out of the 398.

--=============================================
-- this is the SplitString function
-- for use when we're below sql 2016
--=============================================
alter FUNCTION dbo.SplitString

    (
        @List NVARCHAR(MAX),
        @Delim NVARCHAR(255)
    )
    RETURNS TABLE
    WITH ENCRYPTION
    AS
        RETURN ( SELECT [Value] FROM
          (
            SELECT
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
              FROM sys.all_objects) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );

this is how I call this function:

 DECLARE
@olddelim nvarchar(32) = Char(10) -- char(13) + Char(10),
,@newdelim nchar(1)     = NCHAR(9999); -- pencil (?)

SELECT m.*
from  master.dbo.splitstring( OBJECT_DEFINITION ( 146815585),  @olddelim) m

returns 60 rows.

question:

is there a way I could modify my function master.dbo.splitstring so that it would return the whole 398 lines of the procedure?

I don't think your sys.all_objects has enough rows to make this work.  It looks like your split routine is reading each individual character and concatenating it.  You have more characters in your stored procedure than you have rows in sys.all_objects.

I created and populated a dbo.numbers table with 100,000 rows and modified your code to use that instead of sys.all_objects.  I also included the row_number I was on to be able to see how the 'numbers' were being consumed.

You can really see what's going on if you run with output to text.

--drop table numbers
SELECT TOP 100000
        IDENTITY(INT,1,1) AS Number
   INTO dbo.numbers
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--=============================================
-- this is the SplitString function
-- for use when we're below sql 2016
--=============================================
alter FUNCTION dbo.SplitString

    (
        @List NVARCHAR(MAX),
        @Delim NVARCHAR(255)
    )
    RETURNS TABLE
    WITH ENCRYPTION
    AS
        RETURN ( SELECT * FROM
          (
            SELECT
            convert(varchar(10),number) as num,
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY number)
              FROM dbo.numbers) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );

DECLARE
@olddelim nvarchar(32) = Char(10) -- char(13) + Char(10),
,@newdelim nchar(1)     = NCHAR(9999); -- pencil (?)

SELECT m.*
from  dbo.splitstring( OBJECT_DEFINITION ( 727113781),  @olddelim) m


Routing data from one SQL Server to another Is there a way in SQL Server 2012 to have data being written to one table routed to another SQL Server on another host?

To clarify, we are using Hibernate Envars to write audit logs. Hibernate Envars does not allow this data to be sent to another server instead of the server where the audit log was created by Hibernate.

I'd like for these audit logs to be sent to another server. Is there a way to configure SQL Server to write specific tables to another server?

Yes, I can actually think of three ways to accomplish this (well, 4 if you include the Update section ;-). All three methods use an INSTEAD OF INSERT Trigger on the AuditLog table.

For all three methods, first create the AuditLog table on the remote instance (this should be obvious, but stated for completeness).

Option #1

Set up a Linked Server to the server that you want the data to go to.
Create a Stored Procedure on the remote server that:

accepts an NVARCHAR(MAX) input parameter
converts the input parameter to XML stored in a local variable
inserts into the AuditLog table from a SELECT statement that parses the XML variable using FROM @XmlVariable.nodes()

Create the INSTEAD OF INSERT Trigger on the local instance in which it does something along the lines of:

DECLARE @RowsToSend NVARCHAR(MAX);
SET @RowsToSend = (
  SELECT *
  FROM   INSERTED
  FOR XML RAW
);
EXEC [LinkedServerName].[DatabaseName].[SchemaName].[StoredProcedureName]
          @RowsToSend;

The reason for using the Stored Procedure instead of a straight INSERT statement is that DML statements over Linked Servers have definite performance issues (I don't remember the exact cause(s), but I recall there being a problem with them reusing cached plans or something related to execution plans).

Option #2

Create a SQLCLR INSTEAD OF INSERT Trigger
Create a SqlConnection using "Context Connection = true;"
Create a SqlCommand with a CommandText of SELECT * FROM Inserted;
Create a SqlDataReader via SqlCommand.ExecuteReader();
Use SqlBulkCopy (String, SqlBulkCopyOptions) with a regular / external connection string to the remote instance
Call SqlBulkCopy.WriteToServer(SqlDataReader)
Since this makes a connection to a remote instance, the Assembly needs WITH PERMISSION_SET = EXTERNAL_ACCESS

Please do not set the database to TRUSTWORTHY ON in order to use EXTERNAL_ACCESS. Instead:
Sign the Assembly / DLL
Create an Asymmetric Key in the [master] database from the DLL
Create a Login from that Asymmetric Key
Grant the new Login the EXTERNAL ACCESS ASSEMBLY permission

Option #3

Set up Service Broker to send messages from the local Instance to the remote Instance
Package up the rows in a similar manner to how it was done in Option 1:

DECLARE @RowsToSend NVARCHAR(MAX);
SET @RowsToSend = (
  SELECT *
  FROM   INSERTED
  FOR XML RAW
);

SEND ... @RowsToSend;

See SQL Server Service Broker for details.
This option requires a little more setup than the other two options, but is better in terms of making the data transfer asynchronous (hence this is better if there is a high volume of inserts of logging data and/or there is latency on the transfer with the other two options as that would slow down the operations generating the log data).

-- UPDATE Uno --

Option #4

A variation of Option #1 that I have used before (but somehow forgot about until reading @datagod's answer) is to move the data over in batches all throughout the day.
Create the same Stored Procedure on the remote Instance that receives an NVARCHAR(MAX) input parameter, converts that to XML, and then inserts that via INSERT INTO dbo.AuditLog SELECT c.value('@field1', 'type'), ... FROM @XmlVariable.nodes('/row') t(c);
Instead of creating an INSTEAD OF INSERT Trigger, create a Stored Procedure on the local instance that transfers the data along the lines of:

SET XACT_ABORT ON;

BEGIN TRY

  BEGIN TRAN;

  -- create temp queue table dynamically to adjust for schema changes
  SELECT * INTO #TempRows FROM @b WHERE 1 = 0;

  -- delete rows first to ensure they don't get transferred again
  DELETE tmp
  OUTPUT DELETED.*
  INTO   #TempRows
  FROM dbo.AuditLog tmp;

  DECLARE @RowsToSend NVARCHAR(MAX);
  SET @RowsToSend = (
    SELECT *
    FROM   #TempRows
    FOR XML RAW
  );

  EXEC [LinkedServerName].[DatabaseName].[SchemaName].[StoredProcedureName]
          @RowsToSend;

  COMMIT TRAN;
END TRY
BEGIN CATCH
  IF (XACT_STATE() <> 0)
  BEGIN
    ROLLBACK TRAN;
  END;

  THROW;
END CATCH;

Create a SQL Agent Job to schedule the Stored Procedure above to run every few minutes
This has the asynchronous benefit of the Service Broker approach, but with a little less setup.
With regards to performance: I have used this method for years to move several million rows per day. If you need to move 20 million rows, or more, per day then maybe replacing the XML packaging here with the SqlBulkCopy via SQLCLR approach of Option 2 would be good to try because it would allow you to pass the local data to the remote Instance as a TVP. But otherwise this approach holds up quite well, especially if you stick with the attribute-based XML as I have shown here, which parses faster than element-based XML.

-- UPDATE Dos --

Regarding the ability of FOR XML RAW to handle all possible datatypes and data, I did the following testing:

First, create a test temp table that has columns and data that could potentially be an issue for converting to a text-based format, especially one that has certain characters reserved and hence need to be escaped if present in the data so as to not cause errors.

-- DROP TABLE #BadValues;
CREATE TABLE #BadValues (Col1 NVARCHAR(15), Col2 GEOMETRY, Col3 XML,
                         Col4 SQL_VARIANT, Col5 VARBINARY(MAX));
INSERT INTO #BadValues (Col1, Col2, Col3, Col4, Col5)
  VALUES (N'" =  & < ', 'LINESTRING(11 10, 20 20)', '<test/>', 23, 0x00125DFFFF);

If anyone is wondering where I got the  symbol from, it is a supplementary character that is part of UTF-16 but not in the base UCS-2 code points / characters. I created by doing the following:

SELECT NCHAR(150150) -- returns "" in a DB with a collation ending in "_SC", else NULL

Now, look at what it returns normally:

SELECT *
FROM   #BadValues;

-- " =  & <
-- 0x0000000001140000000000002640000000000000244000000000000034400000000000003440
-- <test />
-- 23
-- 0x00125DFFFF

Great. Now let's throw in the FOR XML RAW clause and see what happens:

SELECT *
FROM   #BadValues
FOR XML RAW;

/*
Msg 6865, Level 16, State 1, Line 1
FOR XML does not support CLR types - cast CLR types explicitly into one of the
supported types in FOR XML queries.
*/

Ok. So we need to convert the GEOMETRY field to something usable. All CLR types (whether custom UDTs or supplied by Microsoft) should have a .ToString() function, so let's try that:

SELECT Col1, Col2.ToString() AS [Col2stringified], Col3, Col4, Col5
FROM   #BadValues
FOR XML RAW;

/*
Msg 6829, Level 16, State 1, Line 1
FOR XML EXPLICIT and RAW modes currently do not support addressing binary data as
URLs in column 'Col5'. Remove the column, or use the BINARY BASE64 mode, or create
the URL directly using the 'dbobject/TABLE[@PK1="V1"]/@COLUMN' syntax.
*/

Well, we got passed the CLR-type error, only to get an error on the VARBINARY field. But it suggests that we use "BINARY BASE64" mode, so let's try that:

SELECT Col1, Col2.ToString() AS [Col2stringified], Col3, Col4, Col5
FROM   #BadValues
FOR XML RAW, BINARY BASE64;

/*
<row Col1="&quot; =  &amp; &lt; " Col2stringified="LINESTRING (11 10, 20 20)" Col4="23"
     Col5="ABJd//8=">
  <Col3>
    <test />
  </Col3>
</row>
*/

Not so bad. This just means that if you have a CLR type -- GEOMETRY, GEOGRAPHY, HIERARCHYID, or a custom UDT -- then you cannot use SELECT * when packaging up the records in the Inserted pseudo-table. In those cases you need to explicitly list out the columns so that you can apply .ToString() to any fields that are of a CLR type.

Please note that this is only when using the XML method of transporting the data. If you did this in SQLCLR using the SqlBulkCopy class (as noted above in a couple of places), then that might transfer directly (though I haven't tried it yet).

-- UPDATE Dos punto Uno --

Continuing the test from the prior Update Dos above, the following (using the same temp table and test row) shows the process of converting the initial table to XML and then to NVARCHAR(MAX), then pass that to a stored procedure that will convert it back to XML and then SELECT from that using the .nodes() function.

DECLARE @RowsToSend NVARCHAR(MAX);

SET @RowsToSend =
(
  SELECT Col1, Col2.ToString() AS [Col2stringified], Col3, Col4, Col5
  FROM   #BadValues
  FOR XML RAW, BINARY BASE64
);

SELECT @RowsToSend;
--=====--   here would be the call to the remote procedure, which would do the following:
DECLARE @RowsToInsert XML;

SET @RowsToInsert = CONVERT(XML, @RowsToSend);

SELECT @RowsToInsert;

SELECT c.value(N'@Col1', N'NVARCHAR(15)') AS [Col1],
       CONVERT(GEOMETRY, c.value(N'@Col2stringified', N'VARCHAR(MAX)')) AS [Col2],
       c.query(N'(./Col3/*)') AS [Col3], -- use .query() to get sub-element
       c.value(N'@Col4', N'INT') AS [Col4],
       c.value(N'@Col5', N'VARBINARY(MAX)') AS [Col5]
FROM   @RowsToInsert.nodes(N'/row') t(c);


How to determine or predict space on disk per table row? I'm very new to Postgres so my math could be off here ...

This is my table:

CREATE TABLE audit (
    id BIGSERIAL PRIMARY KEY,
    content_id VARCHAR (50) NULL,
    type VARCHAR (100) NOT NULL,
    size bigint NOT NULL,
    timestamp1 timestamp NOT NULL DEFAULT NOW(),
    timestamp2 timestamp NOT NULL DEFAULT NOW()
);

I want to make some estimations on how much space 1 row would occupy. After some reading I've come up with this, is it correct?

1 row =
23 (heaptupleheader)
+ 1 (padding)
+ 8 (id)
+ 50 (content_id)
+ 6 (padding)
+ 100 (type)
+ 4 (padding)
+ 8 (size)
+ 8 (timestamp)
+ 8 (timestamp)
= 216 bytes

I also created this same table in my local Postgres DB but the numbers don't seem to match:

INSERT INTO public.audit(content_id, type, size)
    VALUES ('aaa', 'bbb', 100);

SELECT pg_size_pretty( pg_total_relation_size('audit') );  -- returns 24 kb

INSERT INTO public.audit(content_id, type, size)
    VALUES ('aaaaaaaaaaaaa', 'bbbbbbbbbbbbbb', 100000000000);

SELECT pg_size_pretty( pg_total_relation_size('audit') ); -- still returns 24 kb

Which brings me to think that Postgres reserves a space of 24 kb to start with and as I put in more data it will get incremented by 132 bytes once I go beyond 24 kb? But something inside me says that can't be right.

I want to see how much space 1 row would occupy in Postgres db so I can analyze how much data I can potentially store in it. Maybe I'm missing something very obvious.

Your calculation is close for the maximum bare row size. The actual range is 68 - 212 bytes per row, or 84 - 228 bytes including the index.

Most importantly a varchar(n) does not have to occupy the maximum length. The data type is implemented with varlena internally, which adds 1 byte overhead for short strings on disk, plus the actual number of bytes for the string.

Data types implemented with varlena don't require alignment padding on disk.

And a NULL value (allowed for content_id) is effectively free. See:

Does not using NULL in PostgreSQL still use a NULL bitmap in the header?

Finally the PRIMARY KEY constraint is implemented using a standard btree index. We have to add that to the total size on disk.

So the calculation for the row size on disk is:

23        bytes  tuple header
 1        byte   padding or null bitmap
 8        bytes  id BIGSERIAL PRIMARY KEY
 0 - 51   bytes  content_id VARCHAR (50) NULL
 0        bytes  alignement padding
 2 - 101  bytes  type VARCHAR (100) NOT NULL
 ?        bytes  alignment padding
 8        bytes  size bigint NOT NULL
 8        bytes  timestamp1 timestamp NOT NULL
 8        bytes  timestamp2 timestamp NOT NULL
---
min 64 (incl 6 bytes padding) - max 208 bytes (no padding required)

+ 4      bytes item identifier in heap page
---
68 - 212 bytes

+ 16     bytes for the index tuple
---
84 - 228 bytes

Plus some overhead for heap pages and index pages as detailed in these related questions:

Calculating and saving space in PostgreSQL
Measure the size of a PostgreSQL table row
Configuring PostgreSQL for read performance

Actual row sizes on disk (without item identifier):

SELECT pg_column_size(a) AS size_on_disk, *
FROM   audit a;

Note that size in RAM can differ:

SELECT pg_column_size(content_id) AS content_id_size_on_disk
     , pg_column_size(content_id || '') AS content_id_size_in_ram
     , content_id
FROM   audit a;

See:

What is the overhead for varchar(n)?

About ...

  Which brings me to think that Postgres reserves a space of 24 kb to start

The heap starts with 0 bytes. The index starts with one meta-page (8kb).
After adding the first row, we see a minimum of 8 kb for the first heap page and 16 kb for the index (1 meta page + 1st index page). Details in the fiddle!

db<>fiddle here


Confused on how a value gets added with the DEFAULT constraint A recent definition I've read about Default Constraints:

DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added.

I believe I've seen code written without the DEFAULT constraint specified and a NULL value was assigned to the field.

If I add a column to a table, do I have specify the DEFAULT keyword for the default value to be assigned?  Or will NULL be assigned since I didn't delineate DEFAULT?

If your column definition does not have default constraint and it is nullable, the nullable field will have null mark, when you add a row even when you specify DEFAULT keyword for your insert value. For example for the following table definition

 CREATE TABLE Test(
    ColA  [nvarchar](50) NOT NULL,
    COLB [nvarchar](50) NULL)

The first three insert statements will insert NULL mark for ColB. Only the last one will have ColB value assigned.

 insert into Test(ColA, ColB) values ('a', DEFAULT);
 insert into Test(ColA, ColB) values ('b', NULL);
 insert into Test(ColA) values ('c');
 insert into Test(ColA, ColB) values ('d', 'e');

Whereas for the following table definition with DEFAULT constraint.

CREATE TABLE Test(
    ColA  [nvarchar](50) NOT NULL,
    COLB [nvarchar](50) NULL DEFAULT 'x')

Only the second statement below will insert NULL mark for ColB. The first and the third statements will assign 'x' as the DEFAULT value.

 insert into Test(ColA, ColB) values ('a', DEFAULT);
 insert into Test(ColA, ColB) values ('b', NULL);
 insert into Test(ColA) values ('c');
 insert into Test(ColA, ColB) values ('d', 'e');


How to compare the data type of a query value in Postgresql How to compare the data type of a query result value? For example, to check if something is an 'interval'
select pg_typeof(current_timestamp - '2020-01-01 00:00') = 'interval';

gives the error
ERROR: invalid input syntax for type oid: "interval"

You need to convert the string 'interval'  to a type OID:
select pg_typeof(current_timestamp - '2020-01-01 00:00') = 'interval'::regtype;


Table Design for Different Constituents All,
I work at a university and having discussions with our third party ERP vendor, we found out that the vendor's table for biographical information stores all the data from different constituents. The same table stores information about candidates, students, faculty, staff, alumni, etc.

This presents a challenge to us since we will have to create views to achieve row-level security for our end users and delegate access to the department based on their need. For instance, employees in the admissions office should not be able to query the biographical information of other employees, etc

Even in the vendor's application, users in the admissions office are able to access other employee's biographical information since the software runs queries on the same table. That seems to be a huge security issues in my view. The software has been created more than 10 years ago and little progress has been made towards fixing that.

As a general rule of thumb, what are the best practices for designing a biographical table? Should there be different tables for each constituent(candidate, employee, faculty) or one table for all of them?

Thank you.

The "best practice" would be to use an RDBMS that supports row-level security so that you only need the one table.

Example:  Oracle Enterprise Edition users would use the Virtual Private Database [VPD] feature to enforce row-level security.

In Oracle Standard Edition, you could simulate VPD by using a VIEW with a WHERE clause that references a database session variable that is retrieved via SYS_CONTEXT() function.  But, it won't be as secure as VPD.

I don't know how to do it in other RDBMS systems.