SELECT count(*) from a materialized view with unique index is slow

The first query is logical nonsense for your case:

SELECT count(DISTINCT a.id) from the_view a;

It makes no sense to use DISTINCT with the id defined unique.

The second query is typically faster with a sequential scan:

SELECT count(*) from the_view a;

Indexes don't contain visibility information. Due to the MVCC model of Postgres it needs to check visibility in the main relation (the heap / the table) - unless all rows of a page are marked visible for all transactions (visibility map). And a MATERIALIZED VIEW is basically just another table with functionally dependent data and a recipe how to derive it from other tables (the stored query).

When the whole table needs to be read anyway, it rarely makes no sense to use an index (additionally). So while Postgres may use an index (index-only scan) when counting only parts of a table, a sequential scan is typically faster for a full count. The decision also depends on your cost settings.

You can verify, that Postgres can in fact use an index-only scan (or a bitmap index scan) by disabling plan types (only in your session for debugging!). The manual instructs in the chapter Examining Index Usage:

  When indexes are not used, it can be useful for testing to force their
  use. There are run-time parameters that can turn off various plan
  types (see Section 18.7.1). For instance, turning off sequential scans
  (enable_seqscan) and nested-loop joins (enable_nestloop), which are
  the most basic plans, will force the system to use a different plan.
  If the system still chooses a sequential scan or nested-loop join then
  there is probably a more fundamental reason why the index is not being
  used;

In many cases an estimate is good enough and available in close to no time:

Fast way to discover the row count of a table

Remember that every count is hardly more than an estimate. It may be outdated in the moment after you count.

Related page on Slow Counting in the Postgres Wiki.


You don't have to "specify pool_size for my_db, too".

But your config won't limit the number of connections though. You need to limit max_db_connections for that like here:

[databases]
my_db = host=10.10.10.10
my_db_with_conn_limit = host=10.10.10.10 dbname=my_db pool_size=55  max_db_connections=55

Otherwise you limit the number of sessions in pool, but not the number of connections...

You can also just set different limits per database+user pair instead (which I believe is more common practice).


Another good way to do this is to copy 0.2 mln records to another table and drop the first table with 1.2 mln.


Taking load off the Master server by using a slave

Yes.  A slave can (and should) offload SELECTs from the Master.

If you outgrow one slave, you can have many.  This gives you unlimited read scalability.

Keep in mind that every write that happens on the Master must be replayed on every Slave.  Using Row Based Replication helps make this less of a burden on the Slaves.  But, eventually, you may become write bound.  (That's a separate topic; at 20/sec, you are probably nowhere near that.)

A "critical read" is one that depends on something that was just written.  Regular replication is "asynchronous", hence a write can take arbitrarily long to get to the Slave.  Hence, if, say, the user posts a comment, he will want to see it immediately.  The post must happen on the Master.  The "see" is a SELECT that would be nice to do on the Slave, but cannot be safely done ("critical read").  Other reads -- sure, do them on the Slave.


That's a standard suggested location in the installation layouts to-start-with and usually people tend to follow the defaults.

Just to answer it short - keeping databases under /var/lib/mysql as such is not a "risk" as such!

There are considerations though. You can always change your directory to a different partition for better manageability and to take IO advantages.
I've even seen people softlink /var/lib/mysql to a different partition to keep things simple-to-view at configuration level.


That is a matter of Windows/batch, it is not specific to SQL*Plus.

You can do this:

(echo connect bp/bp@192.168.1.71/min19_o71
echo select * from dual;
echo exit
) | sqlplus -S /nolog

Result:

C:\Users\balaz>(echo connect bp/bp@192.168.1.71/min19_o71
More? echo select * from dual;
More? echo exit
More? ) | sqlplus -S /nolog

D
-
X

C:\Users\balaz>

Or just use a script, that method is specific to SQL*Plus:

C:\Users\balaz>type script.sql
connect bp/bp@192.168.1.71/min19_o71
select * from dual;
exit

C:\Users\balaz>sqlplus -S /nolog @script.sql

D
-
X

C:\Users\balaz>


Use one of the various techniques for modeling hierarchical data in a relational database.  My preferred approach is visitation numbers (see my answer to this question for more information about visitation numbers) but you could also use adjacency lists.

This will simplify the retrieval of Laws that pertain to a particular Location.

You will also want to include some kind of level indicator on your locations.  This will allow local overrides of broader laws to be identified.  You can sort by the level indicator to pick the most localalized version of any particular law.


Beginner PL/SQL: Return row value from dynamic SQL function

For a single row, use EXECUTE IMMEDIATE ... INTO. A simple example:

set serveroutput on
declare
  l_result number;
begin
  execute immediate 'select count(*) from dual' into l_result;
  dbms_output.put_line(l_result);
end;
/

1

For multiple rows, use EXECUTE IMMEDIATE .. BULK COLLECT INTO, here is an example:

https://stackoverflow.com/questions/21117021/bulk-collect-into-and-execute-immediate-in-oracle

 declare
   type x is table of t.id%type index by pls_integer;
   xx x;
  begin
   execute immediate
   'select id from t' bulk collect into xx;
   dbms_output.put_line(xx.count);
 end;
 /

Ok, another example, with the 12c WITH function:

with function f1 (p_a number) return number
as
  l_result number;
begin
  execute immediate 'select count(*) from dba_objects where object_id = :B1'
  into l_result
  using p_a;
  return l_result;
end;
select f1(object_id) from dba_objects where object_name = 'HELP';
/

F1(OBJECT_ID)
-------------
            1


What are options for super volatile tables?

In my experience, Sql Server sometimes wants to scan small tables (even when they have indexes that you'd think would be used) which can lead to deadlocks on a hot table.

It might be a kludge solution, but I've had tables like this and was able to solve my problem by actually adding lots of 'bogus' rows to 'widen' the table and force Sql Server (optimizer) to seek instead of scan.  I've also had to resort to using the ROWLOCK hint to get around the deadlock problem (your mileage may vary)


How shared nothing and shared everything architectures look like in Oracle?

Shared nothing typically refers to hardware, though it could also be used when describing an SOA architecture.

From a hardware perspective, a "shared nothing" Oracle database simply means a machine using local disks, memory, etc.

Oracle RAC (real application clusters) is a shared disk architecture, but not shared memory. Horizontal scaling is then achieved by adding more machines to the cluster.

When talking in SOA terms, "shared nothing" means that each service has a corresponding database which is only accessed by that service. So the ACCOUNTS service accesses the ACCOUNTS_DB, ORDERS service the ORDERS_DB and so on. These databases could be shared nothing from a hardware perspective as well, or use RAC.

Ensuring consistency of data and references which would normally be handled using foreign keys becomes a challenge in SOA shared nothing databases.

Sharding typically refers to partitioning managed at the application level, rather than within the database. For example, you could partition accounts by email address and direct customers with address starting A-C to ACCOUNTS_DB01, D-F ACCOUNT_DB02 and so on. The shard mapping could be a simple range like this, a function on the input or a lookup database stating which database is stored in. The databases would be "hardware shared nothing" in this case as the idea is you use relatively cheap machines which are easily added and replaced.

You could shard your databases at the application level and still have Oracle partitioning at the table level within the database itself. So you could shard your ORDERS database by customer, then partition the orders table by order date as well inside the database.

The downside to both meanings of shared nothing comes if you frequently run queries that have to access several databases. In these cases your joins will be pushed into the application layer rather than the DB layer so are likely to be slower. Good governance is necessary to ensure this doesn't happen.


AlwaysOn Availability Group Forced Failover

After a forced failover testing with async mode, do I need to rebuild my old primary database?

I'm not sure exactly what you mean by "rebuild" the database, but provided the databases are still in a working condition then you shouldn't need to take any actions like that.
What you're seeing, by performing a forced failover, is by design.  If you do a forced failover, you could potentially have failed over to a replica that isn't completely caught up or at the same point-in-time as the primary replica.  Because of that, data movement is suspended to the secondary replica(s) from the now primary replica so there is a way to have manual intervention if you are now on a database that is "behind".  That behavior that you are seeing is a good thing.
This BOL reference explains it all:

After a forced failover, all secondary databases are suspended. This includes the former primary databases, after the former primary replica comes back online and discovers that it is now a secondary replica. You must manually resume each suspended database individually on each secondary replica.
When a secondary database is resumed, it initiates data synchronization with the corresponding primary database. The secondary database rolls back any log records that were never committed on the new primary database. Therefore, if you are concerned about possible data loss on the post-failover primary databases, you should attempt to create a database snapshot on the suspended databases on one of the synchronous-commit secondary databases.

Please see this BOL reference on how to resume an AG database.
The T-SQL for this would be:
alter database YourDatabaseName
set hadr resume;

NOTE/WARNING/DISCLAIMER: You really need to do some leg work to ensure that you are not causing data loss by resuming data movement.  See above, it could be a huge problem.  The point of suspending data movement is for this very reason:  To manually make sure you can recover as much data as possible.  When you resume data movement, that could be irreversible.  When you resume data movement after a forced failover, you have to have the words "potential data loss" in the forefront of your mind always.


How accurate is the sys.partition.rows column?

Books Online states that the rows field "indicates the approximate number of rows in this partition."  I would therefore expect it to be close, but not 100% accurate, 100% of the time.
Michael Zilberstein reports an example of sys.partitions being wildly incorrect in For want of a nail. Not saying it is a common occurrence, but it is possible.
sys.dm_db_index_physical_stats contains a record_count field that appears to be more accurate, although be aware running the DMV may result in a REDO blocking issue if you run it on an instance hosting an AlwaysOn Readable Secondary Replica.
The explanation for the record_count field shows the following info:

Total number of records.
For an index, total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.
For a heap, the total number of records in the IN_ROW_DATA allocation unit.
For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. This is because a row may contain multiple records. For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. Also, most large LOB rows are split into multiple records in LOB_DATA storage.
For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit.

See also Martin Smith's answer to a similar question on Stack Overflow.


Select where datetime is greater than other datetime which may not exist

Pull the COALESCE out of the subquery, to make it sargable:

        SELECT  min(eventtime) time_min
            FROM  `cel`
            WHERE  eventtime >
                COALESCE( ( SELECT  MAX(other_datetime)
                                   FROM  `call` ), 0)

And indexes:

call:  INDEX(other_datetime)
cel:   INDEX(eventtime)

That should be able to use both indexes to good advantage and take essentially zero time.

If there is an issue with 0 not being a date, try "0000-00-00"


This could be done using a left join to a derived table that picks the correct address:

select p.*, a.id as address_id, a.address_type
from person p
  left join (
     select ad.*,
            row_number() over (partition by ad.person_id
                               order by case when ad.address_type = 'Temporary' then 1 else 2 end) as rn
     from address ad
  ) a on a.person_id = p.id and a.rn = 1;

The case expression for the order by inside the window function could also be written as decode(ad.address_type, 'Permanent', 1, 2), but I prefer standard SQL over Oracle specific one if both are equivalent.

Another option - that might or might not be faster - is to use a lateral join with a fetch first clause.

select p.*, a.id as address_id, a.address_type
from person p
  left join lateral (
     select *
     from address ad
     where ad.person_id = p.id
     order by case when ad.address_type = 'Temporary' then 1 else 2 end
     fetch first 1 rows only
  ) a on 1 = 1;

The lateral left join returns one row for each person_id from the outer query (because of the fetch first 1 rows only)


Install SQL Server 2017 over 2016 Local DB and 2012 native client

The SQL Server 2012 Native Client is a client library for connecting to SQL Server, and SQL Server 2012 is the latest version. Installing SQL Server 2017 will not impact this, and vice-versa. More details here.

LocalDB is a feature of the Express Edition of SQL Server used by developers for testing against SQL Server with minimal configuration and overhead. You can install SQL Server 2017 side-by-side with a LocalDB instance without any issues. More details here.

SQL Server Management Studio has been separated from the SQL Server installation media since SQL Server 2016. You need to download and install SSMS separately from SQL Server. Download link here.


Why is SAN Replication is Causing Random Database Corruption?

in relation to your comment, I suspect a Ops related issue here instead of a SQL Server engine issue.  These SAN devices usually work on the block layer and some manage transaction log/data file sync better than others, as well as other areas.

You can show the ops team that no, SQL Server doesn't randomly corrupt data like this.  You can restore backups to another server, setup mirroring, and all this happens with no corruption.  The minute we do san level replication it happens.  If SQL Server caused corruption like this it wouldn't be around.  SQL Server has almost millions of lines of code dealing with corruption, fixing corruption, and reducing the potential of corruption.  You don't get this issue in any other environment and it only comes up with SAN replication, correct?

Firmware is often a major cause of these types of issues.  Get your Dell support rep on the line, they will have a lot more info and troubleshooting.  Don't settle for a lazy rep, your enterprise's data and time is on the line.  They have a lot of tools that check to see what is causing this in the background and other tools such as DPAC which might help.  This is not a SQL Server engine issue, we will need the full support of Ops.

Edit: If your firmware is out of date or mismatched, get a policy from the Ops team that manages the SAN which states that they will keep the firmware across the stack of the machines they manage up to date.  If this SLA doesn't exist, you should make a note of it to your managers because you're exposed to a lot of other issues besides this one.

I'm assuming you're using SAN block level replication.

It could often also be mismatch in settings.  Maybe different block sizes, etc. but the san os should be able to detect those issues usually.


How to accomplish such insert-or-update strategy?

You have to try to finalize any existent opened record for the given AssetID before insertion of the new record.

BEGIN
  DECLARE ts TIMESTAMP = NOW();
  DECLARE f1, f2, fN FLOAT;

  SELECT Field1, Field2, FieldN
    FROM prod_table
   WHERE AssetID = _givenID
     AND EndDate = NULL
    INTO f1, f2, fN
  ;

  IF ( f1 <> _field1 OR
       f2 <> _field2 OR
       fN <> _fieldN )
    THEN
      UPDATE prod_table    -- Here we suppose there is zero or one opened record
         SET EndDate = ts  -- If there is no records for _givenID, update do nothing
       WHERE AssetID = _givenID
         AND EndDate IS NULL
      ;

      INSERT prod_table (AssetID, BeginDate, EndDate, Field1, Field2, FieldN)
      VALUES ( _givenID, ts, NULL, _field1, _field2, _fieldN )
      ;
  END IF;
END


AlwaysOn Async read takes more time when compared to that of Sync secondary

There are a number of different factors involved but it is entirely possible to have a replica that is synchronous be faster or slower than an asynchronous one.

To list a few:

Replica is under too much load
Hardware specification differences (i.e. synch runs ssd and async runs rotational) or synch is using 8 cores @ 3.6 GHz and async is using 4 cores @ 2.4 GHz
Stats and/or indexes were updated and haven't yet been fully redone on the async
Geographical network latency


Generate a file or a script that can be used to reconstruct a database on a different PC

Using ssms, Right click the database .. tasks.. generate scripts and export the database as schema only. Schema only does not have data.

See reference : https://www.mssqltips.com/sqlservertip/2500/sql-server-2008-r2-generate-scripts-wizard-with-database-schema-and-data/


Check constraints can only be used to verify if the data falls within a given set of parameters.  The CHECK(...) clause is essentially validated as a boolean condition; if the result is 1, the row is inserted, if the result is 0 the row is not inserted, and an error is returned to the client.

To test this, I created your table, along with your proposed check constraint, and inserted several rows of test data:

CREATE TABLE dbo.tblTestCheckConstraint /* always specify the schema */
(
id INT,
NewColumn INT
);

ALTER TABLE tblTestCheckConstraint
ADD CONSTRAINT chk_tblTestCheckConstraint_NewColumn1 CHECK
(
    CASE
        WHEN NewColumn >4 THEN 1
        ELSE NewColumn
    END = 1
);

INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (5);

SELECT *
FROM dbo.tblTestCheckConstraint;

This results in several errors and allows two rows to be inserted:

Results:

When attempting to insert 2 and 3 (or in fact anything other than 1 or >4) the constraint will return false, indicating to SQL Server to not perform the insert, and return the error messages seen above.

Here is an example of how you could accomplish this using a trigger:

USE tempdb

CREATE TABLE dbo.tblTestCheckConstraint
(
id INT CONSTRAINT PK_tblTestCheckConstring PRIMARY KEY CLUSTERED IDENTITY(1,1),
NewColumn INT NULL
);
GO
CREATE TRIGGER dbo.trg on dbo.tblTestCheckConstraint
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
    SELECT NewColumn
    FROM INSERTED i
    WHERE i.NewColumn <=3 OR i.NewColumn IS NULL;

    INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
    SELECT 1
    FROM INSERTED i
    WHERE i.NewColumn >3;
END;
GO
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (4);
SELECT *
FROM dbo.tblTestCheckConstraint;

Ensure you remove the constraint from the table before trying to insert data, since it may conflict with the trigger code:

ALTER TABLE dbo.tblTestCheckConstraint
DROP CONSTRAINT chk_tblTestCheckConstraint_NewColumn1;


As others have mentioned, this can be done rather easily via a SubClass / Inheritance model.  I would advocate something closer to what Pieter Geerkens suggested than what VDohnal suggested as I prefer not having lots of NULL fields per row, depending on the "type" that the row represents (i.e. the [Value] field has to be NULL on an X row as X does not have that property, Y does but does not have the [attr] field so [attr] has to be NULL on rows representing Y objects). I just feel that is harder to maintain over time as someone needs to be VERY familiar with the app layer object model to know if the NULL field is a valid data point for that row or to be ignored for that row.  Even if you have CHECK constraints to enforce NULL, that is yet something else to maintain as you add properties to objects over time and it is still not easily visible when writing queries against these tables. Please note that the issue, in the past, would have also been a needless waste of space related to fixed-length data types at the data layer, but that has been mostly alleviated by the SPARSE option which was introduced in SQL Server 2008 (well, assuming you are using SQL Server).

Notes:

The essential aspect of this model is that the [Thing] table is the "base" table for your "Thing" object.
The [Thing] table has a [ThingType_ID] field which is not a functional requirement but is a data-dictionary / lookup-table so that you can associate meaningful information for people doing ad hoc queries or reports, etc. It also helps to determine in which subclass table to look for the extension properties, assuming there is a reason why you don't already know and are first looking in the [Thing] table for general info.
The [Thing] table has a [A_ID] field which is an FK back to the [A] table. This maps to your structure of having an A class that contains a collection of Thing.
The subclass tables of [ThingX], [ThingY], and [ThingZ] have the same PK as the base class table [Thing] (i.e. [Thing_ID]). The reason to not have separate IDENTITY values is that the subclass tables are not an entity by themselves but are merely an extension of the base [Thing]. Their identity IS the identity (the concept of identity, not the SQL keyword IDENTITY) of [Thing].
Going along with the prior point, the PK field of the subclass tables [ThingX], [ThingY], and [ThingZ] is also an FK back to the [Thing] table.
There is no need for a separate [B] table as those properties are all 1 to 1 with the
definition of the [ThingZ] class / sub-table.
All 4 of the base-class and sub-class tables have a [ModifiedDate] audit field as it is possible to update the properties of either the base [Thing] table OR a subclass table but not both in the same operation (e.g. updating the [ThingX].[Attr] field doesn't require updating the [Thing] table).
Only the base class table--[Thing]--has the [CreatedDate] audit field because rows in the subclass tables cannot be created independently (because it is only the combination of the [Thing] table and one of the subclass tables that make up an object).  I suppose it is possible to create a row in the [Thing] table only (i.e. no INSERT into any subclass tables) and later come back to INSERT into one of the subclass tables, but that is just silly ;-).

Example SQL using Microsoft T-SQL syntax (but no features highly-specific to SQL Server):

CREATE TABLE A
(
   A_ID  INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
   -- other fields?
   CreatedDate DATETIME NOT NULL DEFAULT (GETUTCDATE()),
   ModifiedDate DATETIME NULL
);

CREATE TABLE ThingType
(
   ThingType_ID TINYINT NOT NULL PRIMARY KEY,
   ThingType NVARCHAR(100) NOT NULL
);

CREATE TABLE Thing
(
   Thing_ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
   ThingType_ID TINYINT NOT NULL REFERENCES ThingType (ThingType_ID), -- FK to ThingType
   A_ID INT NOT NULL REFERENCES A (A_ID), -- FK to A
   -- other fields?
   CreatedDate DATETIME NOT NULL DEFAULT (GETUTCDATE()),
   ModifiedDate DATETIME NULL
);

CREATE TABLE ThingX
(
   Thing_ID INT NOT NULL PRIMARY KEY REFERENCES Thing (Thing_ID), -- FK to Thing
   Attr INT,
   -- other fields?
   ModifiedDate DATETIME NULL
);

CREATE TABLE ThingY
(
   Thing_ID INT NOT NULL PRIMARY KEY REFERENCES Thing (Thing_ID), -- FK to Thing
   Value NVARCHAR(500),
   -- other fields?
   ModifiedDate DATETIME NULL
);

CREATE TABLE ThingZ
(
   Thing_ID INT NOT NULL PRIMARY KEY REFERENCES Thing (Thing_ID), -- FK to Thing
   Something DATETIME,
   -- other fields?
   ModifiedDate DATETIME NULL
);

Side Notes:
Just to be clear about differences between this suggestion and Pieter's (since they are very similar):

I have no Table Type for [B] as I don't see a need for it
My interpretation of your structure (i.e. class A having List<Thing>) is such that both of the other answers have misconstrued that relationship:

Pieter's does not allow for having singular (i.e. non-repeated) properties of A while at the same time having multiple instances of Thing.
VDohnal's has the relationship backwards such that a single Thing will have multiple A s. (this has since been corrected).

On a practical level, I cannot see a case for doing the LEFT JOIN query at the bottom of Pieter's answer, but maybe on occasion there is. In such a case I have opted instead to query each type individually via INNER JOIN and then combining via UNION ALL (fields in one table that aren't in the other subclass table(s) need to be given literal values):

 SELECT base.field, subclass.field, subclass.[Attr]
 FROM [Thing] base
 INNER JOIN [ThingX] subclass
         ON subclass.Thing_ID = base.Thing_ID
 UNION ALL
 SELECT base.field, subclass.field, CONVERT(INT, NULL) AS [Attr]
 FROM [Thing] base
 INNER JOIN [ThingY] subclass
         ON subclass.Thing_ID = base.Thing_ID

EDIT:
Here are two more notes that I thought of:

One benefit of this broken out design, as opposed to the single table model, is that it allows for parts of the object model to change (as business needs change over time) without impacting the parts that aren't changing. For example, adding a new property to the X object does not impact either of the [ThingY] or [ThingZ] tables nor the queries that make use of [ThingY] and [ThingZ].
For simplicity of getting data that represents the full X, Y, or Z objects, you can create Views that are a simple CREATE VIEW [X] AS SELECT * FROM [Thing] base INNER JOIN [ThingX] sub ON base.Thing_ID = sub.Thing_ID and so on for the Y and Z objects.

EDIT 2:
Renamed the [X], [Y], and [Z] tables to be [ThingX], [ThingY], and [ThingZ] respectively. This naming convention for the subclass tables is more representative of them being extensions and not complete objects / entities by themselves. This should also make the model (and working with the tables) conceptually clearer in that [X] (the View suggested directly above) = [Thing] + [ThingX], and so on with [Y] and [Z].

Also, the tables will be grouped together in SSMS, queries against sys.tables / sys.objects / INFORMATION_SCHEMA.TABLES and hopefully in your source control system where you have individual SQL scripts for each table :-).


No, the column order in the table is not important at all for the performance of any related query or any additional index.

A table T1(a, b) is equivalent to T2(b, a), if the data inside is the same. The order of the columns matter only for an index key, because there the data is ordered after the first column of the key.

PS: let's say on table Table1 (a, b, c, d) an index is created the following way:

CREATE INDEX idx_Table1 ON Table1 (a, b);

This creates an index, which is a data structure additional to a table in the format of a balanced tree (B-Tree) that helps some queries run faster. The index key is that pair formed of the data from columns (a,b) concatenated.

For example a query like:

select a from Table1 where a = 1 and b = 2

would use the index idx_Table1, while a query like:

select a from Table1 where b = 1 and c = 1

would most probably wouldn't use it but would benefit from an index:

CREATE INDEX idx_Table2 ON Table1 (b, a);

Some references:

The MySQL reference page for CREATE INDEX is here.
How MySQL uses indexes: reference page.
A nice book on indexes: Use The Index, Luke.


MongoDB 3.4 Sharding Error. “Cannot accept sharding commands if not started with --shardsvr”

First.. You cannot have two same name replica sets in one cluster (rs0), rename that second one to rs1. (Ports 27020 - 270122).

Error message is telling directly what you should do.. So, add --shardsvr to every started replica set.

From mongod --replSet xxx  to  mongod --shardsvr --replSet xxx

And of course after log in to mongoS service, you need to add those (two) replica sets to your cluster

sh.addShard("rs0/address1:27000, address2:27001, address3:27002")

And that other rs1 too..


I use INIT and then my full backups are separate (and include a timestamp to immediately see when they were taken). I can use any method I want to clear out old files (C#, PowerShell, what have you, without having to know the internals of the backup files at all), rather than using xp_delete_file - which is undocumented, unsupported, and could change or be removed in a future release or even a service pack. I don't understand any advantage to stuffing multiple backup files into the same file (which you can't do if you name the file differently using the date anyway). I also don't know why you need to use CONCAT here, but in any case, you can't define an expression in the DDL that way. Why not just:

DECLARE @backup_file VARCHAR(255) = 'D:\Program Files\Microsoft SQL '
  + 'Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\perfMaster_'
  + CONVERT(CHAR(10), GETDATE(), 126) + '.bak';

BACKUP DATABASE perfMaster TO DISK = @backup_file
  WITH INIT, NAME = N'Full Database perfMaster Backup',
  SKIP, NOREWIND, NOUNLOAD, STATS = 20;

I would also suggest:

using COMPRESSION if your edition supports it.
not storing your backups on the local D:\ drive. If you lose your hard disk, you've lost your database and the backups.


How to rewrite this correlated query as a JOIN?

One variant:

SELECT
  a.dept,
  a.name,
  b.salary
FROM employees AS a
JOIN (
  SELECT
    MAX(c.salary) salary,
    c.dept
  FROM employees AS c
GROUP BY c.dept
) b
  ON a.dept = b.dept

If you are using TSQL you can replace JOIN with CROSS APPLY on move ON clause inside and replace it with WHERE


SQLCMD does not print Rows affected in log file

I'm not sure if this is your problem, but check your 'Connections' options for your Sql Server instance.

Using Sql Server Management Studio, right-click your instance and select 'Properties'.  Then select 'Connections'.  Under 'Default connection options', scroll down and make sure that 'no count' is NOT checked.


MySQL: display only the dates of the days that fall on a Sat. or Sun. between two given dates?

Your query does indeed generate dates - but do you really need to generate 100,000 days between 1970 and 2243-10-16 when all you need are dates from next month - see the fiddle here?

Getting code from the internet can be great - most of us do it all the time (it's the whole raison d'être of dba.stackexchange and all of the SE sites), but we have to be careful that a general solution is really suitable for our needs.

On my own laptop I profiled your query using the technique outlined in the MySQL documentation here.

Your query spends 0.173519 seconds executing.

If it's changed to this (commented out 3 lines):

SELECT selected_date, DAYNAME(selected_date) AS "Weekend dates"
FROM
(
  SELECT adddate(DATE(NOW(0)), t1*10 + t0) AS selected_date
  FROM
  (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
  (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1
--  (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2
 -- (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 -- (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
) tab
WHERE
  (selected_date BETWEEN '2020-04-08' AND '2020-04-21')
  AND (DAYOFWEEK(selected_date) = 1 OR DAYOFWEEK(selected_date) = 7)
ORDER BY selected_date;

during which only 100 dates are generated by the inner query, only 0.000212 seconds are spent executing - almost 1000 (818) times faster. May not seem like much, but if you have (don't know your circumstances) many of these queries hitting the server, this could add up! In any case, it's obviously better to reduce your processing where reasonably possible.

The result is:

selected_date   Weekend dates
   2020-04-11        Saturday
   2020-04-12          Sunday
   2020-04-18        Saturday
   2020-04-19          Sunday

whereas you say that the data you say you want is:

2020-04-08
2020-04-14
2020-04-15
2020-04-21

The days you specify fall on Sat. and Sun. in March of 2020 and not April. It's not really important in any case - you can just change your query's BETWEEN clause to cope with any date changes.

Again, the internet is great, but you can't beat the documentation! In particular, if you are sticking with MySQL, it's worthwhile getting to know its functionality in detail and I would recommend that you bookmark these two pages - MySQL's date and time functions and MySQL's string functions.

Finally, I would suggest that you upgrade to MySQL version 8 - it underwent a raft of very powerful enhancements that are (IMHO) essential in any dev/DBA's toolkit. Analytic - aka Window - functions, Common Table Expressions, Regular Expression enhancements inter alia  you can check them out here.

p.s. welcome  to the forum!


You do not want to change DBINSTANCE. All that does is change what instance is the "main" one for your OS. And you only have one instance from what I understand (named db2 I believe). I'm betting this has more to do with permissions as you are attempting to run db2sampl, which I believe creates the sample database if I remember correctly. Instance owner should be able to run this without any issues.

If I were you, I would run db2sampl as instance owner and then perform your necessary GRANTs for db2-user once the database is there.


How can I "shift" certain column values to different rows in an existing table?

You need to qualify SlotId in your sub-query with SlotTable or it will use the SlotId from the sub-query itself.

UPDATE [SlotTable]
SET [SlotVal1] = (SELECT [SlotVal1]
                  FROM [SlotTable] AS s2
                  WHERE s2.[SlotId] = ([SlotTable].[SlotId] - @rowsToShift)),
    [SlotVal2] = (SELECT [SlotVal2]
                  FROM [SlotTable] AS s2
                  WHERE s2.[SlotId] = ([SlotTable].[SlotId] - @rowsToShift))
WHERE [SlotId] >= (@firtRowToMove + @rowsToShift);

SQL Fiddle

BTW, your CTE version does not work either

Another way to do what you do in this update statement a bit more efficiently (at least in my tests).

update s1
set SlotVal1 = s2.SlotVal1,
    SlotVal2  = s2.SlotVal2
from SlotTable as s1
  inner join SlotTable as s2
    on s2.SlotId = s1.SlotId - @rowsToShift
where s1.SlotId >= @firtRowToMove + @rowsToShift


Powershell and dbo.sysjobsteps.Command field being truncated - not a length issue

Using the script Powershell export query to csv, I was able to run it under Powershell and successfully export the job commands without encountering any truncation.  Are you doing something different?  Here is the script I ran:

$server = "."
$database = "msdb"
$query = "SELECT
    j.Name
    , j.[Description]
    , js.Step_ID
    , js.Step_Name
    , js.Database_Name
    , js.Command
    --, j.*, js.*
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobsteps js
    ON js.job_id = j.job_id
--WHERE j.enabled = 1
ORDER BY j.name, js.Step_ID"

$extractFile = "C:\Scripts\TestCsv.csv"

$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | Export-Csv $extractFile


Scheduled event did not start when expected

Events are not "running" unless they are actually in the process of executing.

What you see in your processlist is the actual event scheduler, not the event itself.  You will always have this entry when the event scheduler is enabled.

mysql> show processlist;
+---------+-----------------+---------------------------+-----------------+---------+-------+-----------------------------+------------------+
| Id      | User            | Host                      | db              | Command | Time  | State                       | Info             |
+---------+-----------------+---------------------------+-----------------+---------+-------+-----------------------------+------------------+
|      48 | event_scheduler | localhost                 | NULL            | Daemon  |     1 | Waiting for next activation | NULL             |

Nothing shows in the processlist for scheduled events unless the actual query spawned by the event is currently running.

When that happens, this row will still be in the processlist, plus you will see a new thread that will appear while the event's query is actually running, and disappear when the event's query has finished.  The next time the event fires, it will have a new thread and a new thread ID.

To check on an event:

mysql> use the_event_db; # your database name
Database changed

mysql> show events;
+------------------+------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db               | Name                   | Definer   | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+------------------+------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| the_event_db     | scheduled_stats_update | tehuser@% | SYSTEM    | RECURRING | NULL       | 15             | MINUTE         | 2012-12-14 12:41:16 | NULL | ENABLED |         11 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------------------+------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)
mysql>

Note the Starts value.  In the event above, this event doesn't fire at :00, :15, :30, and :45 minutes of every hour -- it runs every 15 minutes after 2012-12-14 12:41:16... so each hour it runs at XX:11:16, and XX:26:16, XX:41:16, and XX:56:16.

Your original question shows this:

ON SCHEDULE EVERY 1 MONTH STARTS '2013-02-02 00:00:00'

This would run on the second day of every month, not the first.


SqlPackager.exe excluding users in report

https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx#DeployReport Parameters and Properties

look into:

/p:DropObjectsNotInSource= {True | False}

/p:DoNotDropObjectType=Users

/p:ExcludeObjectType=users

/p:IgnoreUserSettingsObjects

Some combination of these will likely give you the Output you want.   Search that link for the specifics.   I had a very similar issue, in that SSDT Schema compare and SQLPackage.exe generated different results


Get products of category plus all child categories till leaf nodes

If your data structure is fixed and out of your control, then the best way to do this is to use a recursive common table expression (CTE) like in this question.

If you can change the structure then there are ways to make such queries considerably more efficient. What you have there is often called a "naive tree" - while it allows easy construction and easy navigation up and down by one level at a time, more complex queries including those that need to consider arbitrary depth (like your question) can be a pain.

The common alternatives are:

Store the full path of each node as well as the parent relationship, and index it. This denormalises your data a little but makes "this node down" queries easy because you can search for WHERE tree_path LIKE current_node_path+'%' or to exlude the current node and list just those below it WHERE tree_path LIKE current_node_path+'/%'. Of course you have the extra work of updating the paths when nodes are moved - this is probably best done by trigger or if your application always uses stored procs for updating the tree instead of direct table access it is clear (and perhaps more efficient) to include the logic there.
Store a matrix of relationships, so for the tree 1->2, 1->3, 2->4, 2->5, 5->6 you would store:
n1  n2  depth
2   1      1
3   1      1
4   2      1
4   1      2
5   2      1
5   1      2
6   5      1
6   2      2
6   1      3
Again you have extra data to maintain, but it makes certain varieties of query more efficient and easer to write. For all under node 5 it is WHERE n1=5. Some would store the self relation (i.e. n1=5, n2=5, d=0) too to avoid unions when you want the node and all below - then "5 and below" is WHERE n1=5 and "below 5" is WHERE n1=5 AND depth>0.

There is a good chapter covering this in more detail in this book which I recommend anyone working with database has a read through - it has a good tone for the relatively inexperienced and the experienced will be reminded of important things they've forgotten! If you want to go into real depth there are whole books about handing hierarchical data in SQL and other relational systems. The usual recommendation is "Joe Celko's Trees and Hierarchies in SQL for Smarties".


Assuming your are using SQL Server database, you can use CONVERT function with style 114 to get the time portion from your seconds value in integer. Please refer to following code implementation.

Declare
     @FromTime int
    ,@ToTime int;

Set @FromTime = 20520;
Set @ToTime = 39360;

Select Convert(varchar, Dateadd(s, @FromTime, 0), 114) as FromTime;
Select Convert(varchar, Dateadd(s, @ToTime, 0), 114) as ToTime;

To update all the records, if your seconds fields are int or any numeric type you will require two additional time fields in your database and execute the update statement like below.

-- 1) if the fields are int type use following
Create table T1
     (FromTimeInSeconds int
    ,ToTimeInSeconds int);

Insert into T1 values (20520,39360)

--Alter table to add time fields
Alter table T1 add FromTime time, ToTime time;

Update T1
    set FromTime =  Convert(varchar, Dateadd(s, FromTimeInSeconds, 0), 114),
        ToTime =  Convert(varchar, Dateadd(s, ToTimeInSeconds, 0), 114);

If your seconds fields are char or varchar type you can update the same fields with statement like below.

-- 2) if the fields are character type use following
Create table T2
     (FromTime varchar(20)
    ,ToTime varchar(20));

Insert into T2 values ('20520','39360');

Update T2
    set FromTime =  Convert(varchar, Dateadd(s, cast(FromTime as int), 0), 114),
        ToTime =  Convert(varchar, Dateadd(s, cast(ToTime as int), 0), 114);

Please find a sample implementation of both the cases in this Fiddle.


Polybase with SQL Server 2019 on Linux or Containers

After a lot of trial and error and a lot of research, I found a solution.
I tried both Ubuntu 16.04 and Ubuntu 18.04.  This installation approach works for both, but I only was able to get Polybase to work under Ubuntu 18.04.
I performed a fresh install of Ubuntu and SQL Server.  As with every attempt, everything went well except the installation of the mssql-server-polybase-hadoop package. It failed on attempting to find and install the mssql-zulu-jre-11 package.
However, I downloaded the .deb package and manually installed it from these locations:
Ubuntu 16.04:  https://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/mssql-zulu-jre-11/mssql-zulu-jre-11_11.43.56-1_amd64.deb
Ubuntu 18.04:  https://packages.microsoft.com/ubuntu/18.04/prod/pool/main/m/mssql-zulu-jre-11/mssql-zulu-jre-11_11.43.56-1_amd64.deb
After installing it with dpkg -i, I was able to then install the mssql-server-polybase-hadoop package.
This process allowed me to configure and use Polybase.  (Only with Ubuntu 18.04)


Yes. All object definitions are available from within SQL Server as tables or views. These meta data sources are collectively known as "the catalog."

The industry standard way is through the information schema views. However, I've heard reports that SQL Server's implementation can be a bit rough.

There's another set with names like sys.tables, sys.views etc. These are the ones most often used.

A set of program APIs are published called server management objects (SMO). These can give meta data or script whole objects. They are what SSMS Object Explorer uses. The documentation can be terse but if you persevere it is very powerful. SMO can be called from C#, powershell or other languages.

To create the DTO for an arbitrary query I would reference the catalog views for the individual columns returned. Frankly your technique of creating a transient view is as good a way as any. You could also create a temporary table using select .. into #SomeName. Temp tables clean themselves away when they go out of scope. Similarly wrap the query in a temporary stored proc and use sys.dm_exec_describe_first_result_set.


Can SQL Server start taking backups by itself?

Most likely it is a 3rd party backup solution such as NetBackup that your sysadmins have pointed to your instance.

Try reading the default trace to see if the application name column gives you the specifics:

-- read all available traces.
-- including current
DECLARE @current VARCHAR(500);
DECLARE @start VARCHAR(500);
DECLARE @indx INT;

SELECT @current = path
FROM sys.traces
WHERE is_default = 1;

--remove the rollover number from the filename
--(i.e., log_157.trc becomes log.trc)
SET @current = REVERSE(@current)
SELECT @indx = PATINDEX('%\%', @current)
SET @current = REVERSE(@current)
SET @start = LEFT(@current, LEN(@current) - @indx) + '\log.trc';

-- CHANGE FILTER AS NEEDED
SELECT
    TextData,
    ApplicationName,
    StartTime,
    CASE EventClass
        WHEN 46 THEN 'Object:Created'
        WHEN 47 THEN 'Object:Deleted'
        WHEN 164 THEN 'Object:Altered'
        ELSE 'Other'
    END as EventClass,
    DatabaseName,
    ObjectName,
    ObjectType, -- https://msdn.microsoft.com/en-us/library/ms180953.aspx
    HostName,
    LoginName
FROM
    ::fn_trace_gettable(@start, DEFAULT)
WHERE 1=1
    AND DatabaseID <> 2 --eliminate tempdb
    AND DatabaseName = 'model'--Like '%master%'
    AND Textdata like 'Backup Database%'
ORDER BY StartTime DESC


Without knowing anything else about your system, that seems like total overkill.  You'd be better off with a single table that has ID, Skill, and SkillCount.

Having two tables for such a simple schema will result in unnecessary work for both the DBA and developer.  The database engine will need to perform extra work to JOIN the two tables whenever you require reading both Skill and SkillCount at the same time.

If you had a really, really large dataset (think hundreds of millions or billions of rows) and you regularly needed to access just the Skill and rarely needed to show the SkillCount field, then perhaps you might consider using your design.


Using MySQL 8.0 "Upgrade Checker" on RDS

You can run util.check_for_server_upgrade() with a Mysql user who has the ALL PRIVILEGES. As you are using AWS RDS your master MySQL user don't have SUPER user permission, that's why your are getting this error, you can check your user permission with SHOW GRANTS FOR 'user'@'host'.
Only rdsadmin user have the ALL PRIVILEGES in AWS RDS MySQL instance. rdsadmin is amazon's own user that is used by Amazon to perform release or version update, taking backups and other maintenance tasks. Amazon's support should be able to advise you better in this topics.


Effective way of accumulating index fragmentation statisitcs

I fully expect down-votes for this because my opinion doesn't fit convention in the SQL community, but I don't think you should be focusing on fragmentation levels nor should you be adjusting the fillfactor unless absolutely necessary.

The definition of Index Fragmentation is as follows:

  The logical ordering [of pages within an index], based on the key value, does not match the physical ordering inside the data file.

Ok, so what does that really mean?  It just means that you have an ordering mismatch between how the values are stored in the Index Tree and how they are stored on disk.  Low Index fragmentation does NOT mean data is stored contiguously or even on the same drive within a drive array.

How does Index Fragmentation hurt you?  Really, through my investigation, the only downside to Index Fragmentation that I've been able to identify is that read-ahead operations are less efficient (e.g. more are performed) against an index that is fragmented (in regards to Seeks and Range Scans operations) as opposed to said index being absent of fragmentation.  Read-Ahead operations only happen when you pull an index/table off of disk.  If you are able to maximize the time that the index remains cached, you will see less and less read-aheads negatively affect performance because you won't constantly be going back to the disk.  Additionally, Index Scan operations (as opposed to Index Seeks or Range Scans) are less troublesome because the entire index/table is coming off of disk any way.

Why does most documentation attribute High Index Fragmentation with Poor Performance?  The answer is that often times, High Index Fragmentation creates unnecessary white-space within an index due to page splits and other operations.  This white-space is the real detriment to performance because it requires more pages be read into memory to get less data.  More white space = More I/O requests = less performance.

As Kendra's article alludes to, even the BOL article makes mention that setting FILLFACTOR too low will cause performance issues.  This is because of the exact same reason.  More reads for less data is less performant.  Setting FILLFACTOR to anything other than 0 when necessary is forcefully adding whitespace to your indexes/tables.  Let's do some basic math here and see why this isn't a good blanket-recommendation against your database.

Let's say you have an index that is 100GB in a completely unfragmented state with the default FILLFACTOR set.  You set FILLFACTOR to 90 and rebuild the index.  The index will now become 111GB (100/90 * 100), which is an increase by 11%.  This also means that you'll have to read 11% more pages to get the same amount of data off of disk.  Apply this system-wide, and you've just increase read operations by 11% across the system.  The other detriment to white space is that it is not only stored on disk, but it's also stored when the page resides in memory.  That means you're reducing your memory footprint by 11% just to help "prevent" bad page splits.  Really, are page-splits so bad that you need to reduce your memory footprint and increase the amount of read operations off of disk?  This setting is a negative double-whammy.

There are definitely scenarios where adjusting FILLFACTOR is needed and in the best interest of your database, but to set it across the board in a blanket fashion is not a good idea in my opinion.  I'm not saying you shouldn't adjust FILLFACTOR, rather  you should only adjust it when other options are less desirable.

So, after my rambling, what I suggest you should be reviewing instead of index fragmentation is the avg_page_space_used_in_percent field in the sys.dm_db_index_physical_stats DMV.  If you see that creep up past 10% or higher, a reorg/rebuild would be worthwhile because this is the only way to compress white space out of an index.  Often times, I see that this percentage of white space hits when the index is significantly fragmented, generally at 60% or more.  Also, because this value doesn't creep nearly as quickly as index fragmentation, this may allow you to reduce the frequency you perform index maintenance operations.


Using "OR" against two indexed columns doesn't use indexes

OR does not optimize well.  A common workaround is to use UNION:

( SELECT * FROM replies WHERE `from` = "..." )
UNION ALL   -- or UNION DISTINCT if you know there are no dups
( SELECT r.* FROM replies AS r
    JOIN posts AS p  ON p.tid = r.tid
    WHERE p.from = "..." )

Notice that I also avoided the usually-inefficient IN ( SELECT ... )

For further performance, have these indexes:

replies:  INDEX(`from`)
posts:    INDEX(`from`, tid)  -- in this order
replies:  INDEX(tid)

(And note that the PRIMARY KEY is an index, so don't add a redundant index.)

In your second example, the "index merge" that you experienced may or may not be faster than a UNION.

Oh, it's an UPDATE

To optimize UPDATE, do two separate UPDATEs (no UNION, no OR).  One straightforwardly checks from.  The other is a "multi-table UPDATE" (see the manual) similar to the second select above.


Use Data tier Application to upgrade the database.


Delete records from table A where col 1 and col2 are in table B

Typically, you'd want to join to the two tables together, and delete rows from TableA that match rows in TableB.  Something like this:
DELETE a
FROM TableA a
    INNER JOIN TableB b ON a.Col1 = b.Col1
        AND a.Col2 = b.Col2;

Using the method you show in your question might result in the wrong rows being deleted.  Here's an example to show what I mean:
IF OBJECT_ID(N'tempdb..#TableA', N'U') IS NOT NULL
BEGIN
    DROP TABLE #TableA;
END
IF OBJECT_ID(N'tempdb..#TableB', N'U') IS NOT NULL
BEGIN
    DROP TABLE #TableB;
END

CREATE TABLE #TableA
(
    i varchar(5) NOT NULL
    , j varchar(5) NOT NULL
);

CREATE TABLE #TableB
(
    i varchar(5) NOT NULL
    , j varchar(5) NOT NULL
);

INSERT INTO #TableA (i, j)
VALUES ('asdf', 'lk')
    , ('asd', 'flk');

INSERT INTO #TableB (i, j)
VALUES ('as', 'dflk')
    , ('asd', 'flk');

First, we'll see what values are in both tables:
SELECT *
FROM #TableA;

SELECT *
FROM #TableB;

╔══════╦═════╗
║  i   ║  j  ║
╠══════╬═════╣
║ asdf ║ lk  ║
║ asd  ║ flk ║
╚══════╩═════╝

╔═════╦══════╗
║  i  ║  j   ║
╠═════╬══════╣
║ as  ║ dflk ║
║ asd ║ flk  ║
╚═════╩══════╝
The intention would be to delete the bottom row, while retaining the top row, in TableA.  However, if we run this:
DELETE FROM #TableA
WHERE #TableA.i + #TableA.j IN (SELECT #TableB.i + #TableB.j FROM #TableB);

SELECT *
FROM #TableA;

We'll see both rows are gone from TableA.
This statement, however, results in only the correct row being removed from TableA:
DELETE a
FROM #TableA a
    INNER JOIN #TableB b ON a.i = b.i
        AND a.j = b.j;
SELECT *
FROM #TableA;

Results:
╔══════╦════╗
║  i   ║ j  ║
╠══════╬════╣
║ asdf ║ lk ║
╚══════╩════╝


Pass data from a table row into a create login statement using dynamicSQL

I understand that the exercise is to use a cursor and dynamic SQL, but just to give you another view (and something to take beyond this one class I suppose), you can do a lot of administrative tasks like this without cursors. I feel that when you default to cursors for tasks where you do need to process things iteratively in a loop, you tend to then rely on them for tasks where you don't need a loop at all. So almost always better to think about what you need to do to a set of rows, rather than to each row. In this case, we need to execute the same set of commands against a set of users. Borrowing from the other answer:

DECLARE  @Administrators TABLE(firstname varchar(255),
                               lastname varchar(255));

INSERT INTO @Administrators VALUES('John','Wick'),
                                  ('Frodo','Baggins'),
                                  ('Gandalf','The Grey');

DECLARE @sql nvarchar(max) = N'';

;WITH u AS (SELECT u = QUOTENAME(firstname + lastname) FROM @Administrators)
SELECT @sql += CHAR(13) + CHAR(10)
  + N'CREATE LOGIN ' + u + N' WITH PASSWORD = ''temp'', CHECK_POLICY = OFF;
       ALTER LOGIN ' + u + N' WITH DEFAULT_DATABASE = MyGuitarShop;
       CREATE USER ' + u + N' FROM LOGIN ' + u + N';
       ALTER ROLE  OrderEntry ADD MEMBER ' + u + N';'
FROM u;

PRINT @sql;
--EXEC MyGuitarShop.sys.sp_executesql @sql;

Result:

CREATE LOGIN [JohnWick] WITH PASSWORD = 'temp', CHECK_POLICY = OFF;
  ALTER LOGIN [JohnWick] WITH DEFAULT_DATABASE = MyGuitarShop;
  CREATE USER [JohnWick] FROM LOGIN [JohnWick];
  ALTER ROLE OrderEntry ADD MEMBER [JohnWick];
CREATE LOGIN [FrodoBaggins] WITH PASSWORD = 'temp', CHECK_POLICY = OFF;
  ALTER LOGIN [FrodoBaggins] WITH DEFAULT_DATABASE = MyGuitarShop;
  CREATE USER [FrodoBaggins] FROM LOGIN [FrodoBaggins];
  ALTER ROLE OrderEntry ADD MEMBER [FrodoBaggins];
CREATE LOGIN [GandalfThe Grey] WITH PASSWORD = 'temp', CHECK_POLICY = OFF;
  ALTER LOGIN [GandalfThe Grey] WITH DEFAULT_DATABASE = MyGuitarShop;
  CREATE USER [GandalfThe Grey] FROM LOGIN [GandalfThe Grey];
  ALTER ROLE OrderEntry ADD MEMBER [GandalfThe Grey];


how to script out the user defined table types?

Honestly, the amount of time you'll spend writing a version of this that accounts for all possible combinations of indexes, constraints, and default values, and troubleshoot all the combinations you don't expect from your first use case, I don't think you'll ever get that time back no matter how many table types you have to script, which you can always do from Object Explorer (or Object Explorer Details, for multiple):

Just for fun, I ran a trace to see what Management Studio sends to SQL Server in order to generate the create script for that table type, and it was about as pretty as I expected:

exec sp_executesql N'SELECT
SCHEMA_NAME(tt.schema_id) AS [Schema],
tt.name AS [Name]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)',
N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',
@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
clmns.column_id AS [ID],
clmns.name AS [Name],
clmns.is_ansi_padded AS [AnsiPaddingStatus],
ISNULL(clmns.collation_name, N'''') AS [Collation],
clmns.column_encryption_key_id AS [ColumnEncryptionKeyID],
ceks.name AS [ColumnEncryptionKeyName],
clmns.is_computed AS [Computed],
ISNULL(cc.definition,N'''') AS [ComputedText],
s1clmns.name AS [DataTypeSchema],
(case when clmns.default_object_id = 0 then N'''' when d.parent_object_id > 0 then N'''' else d.name end) AS [Default],
ISNULL(dc.Name, N'''') AS [DefaultConstraintName],
(case when clmns.default_object_id = 0 then N'''' when d.parent_object_id > 0 then N'''' else schema_name(d.schema_id) end) AS [DefaultSchema],
clmns.encryption_algorithm_name AS [EncryptionAlgorithm],
CAST(clmns.encryption_type AS int) AS [EncryptionType],
clmns.generated_always_type AS [GeneratedAlwaysType],
ISNULL(clmns.graph_type, 0) AS [GraphType],
clmns.is_identity AS [Identity],
CAST(ISNULL(ic.seed_value,0) AS numeric(38)) AS [IdentitySeedAsDecimal],
CAST(ISNULL(ic.increment_value,0) AS numeric(38)) AS [IdentityIncrementAsDecimal],
CAST(0 AS bit) AS [IsClassified],
CAST(clmns.is_column_set AS bit) AS [IsColumnSet],
CAST(clmns.is_filestream AS bit) AS [IsFileStream],
CAST(ISNULL((select TOP 1 1 from sys.foreign_key_columns AS colfk where colfk.parent_column_id = clmns.column_id and colfk.parent_object_id = clmns.object_id), 0) AS bit) AS [IsForeignKey],
CAST(clmns.is_masked AS bit) AS [IsMasked],
CAST(ISNULL(cc.is_persisted, 0) AS bit) AS [IsPersisted],
CAST(clmns.is_sparse AS bit) AS [IsSparse],
CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
ISNULL((SELECT ms.masking_function FROM sys.masked_columns ms WHERE ms.object_id = clmns.object_id AND ms.column_id = clmns.column_id), N'''') AS [MaskingFunction],
ISNULL(ic.is_not_for_replication, 0) AS [NotForReplication],
clmns.is_nullable AS [Nullable],
CAST(clmns.scale AS int) AS [NumericScale],
CAST(clmns.precision AS int) AS [NumericPrecision],
CAST(clmns.is_rowguidcol AS bit) AS [RowGuidCol],
(case when clmns.rule_object_id = 0 then N'''' else r.name end) AS [Rule],
(case when clmns.rule_object_id = 0 then N'''' else schema_name(r.schema_id) end) AS [RuleSchema],
ISNULL(baset.name, N'''') AS [SystemType],
ISNULL(xscclmns.name, N'''') AS [XmlSchemaNamespace],
ISNULL(s2clmns.name, N'''') AS [XmlSchemaNamespaceSchema],
ISNULL( (case clmns.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
usrt.name AS [DataType]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
LEFT OUTER JOIN sys.column_encryption_keys AS ceks ON (ceks.column_encryption_key_id = clmns.column_encryption_key_id)
LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id and cc.column_id = clmns.column_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.schemas AS s1clmns ON s1clmns.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id
LEFT OUTER JOIN sys.default_constraints as dc ON clmns.default_object_id = dc.object_id
LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))
LEFT OUTER JOIN sys.objects AS r ON r.object_id = clmns.rule_object_id
LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=clmns.column_id AND p.class=8
WHERE
(clmns.name=@_msparam_0)and((tt.name=@_msparam_1 and SCHEMA_NAME(tt.schema_id)=@_msparam_2))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'DistCritTypeId',@_msparam_1=N'DistCritGroupData',@_msparam_2=N'dbo'

exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=clmns.column_id AND p.class=8
WHERE
(clmns.name=@_msparam_0)and((tt.name=@_msparam_1 and SCHEMA_NAME(tt.schema_id)=@_msparam_2))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'ItemAction',@_msparam_1=N'DistCritGroupData',@_msparam_2=N'dbo'

exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=clmns.column_id AND p.class=8
WHERE
(clmns.name=@_msparam_0)and((tt.name=@_msparam_1 and SCHEMA_NAME(tt.schema_id)=@_msparam_2))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'ObjectId',@_msparam_1=N'DistCritGroupData',@_msparam_2=N'dbo'

exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=clmns.column_id AND p.class=8
WHERE
(clmns.name=@_msparam_0)and((tt.name=@_msparam_1 and SCHEMA_NAME(tt.schema_id)=@_msparam_2))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'OperatorType',@_msparam_1=N'DistCritGroupData',@_msparam_2=N'dbo'

exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=0 AND p.class=6
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
i.name AS [Name],
CAST(ISNULL(si.bounding_box_xmax,0) AS float(53)) AS [BoundingBoxXMax],
CAST(ISNULL(si.bounding_box_xmin,0) AS float(53)) AS [BoundingBoxXMin],
CAST(ISNULL(si.bounding_box_ymax,0) AS float(53)) AS [BoundingBoxYMax],
CAST(ISNULL(si.bounding_box_ymin,0) AS float(53)) AS [BoundingBoxYMin],
CAST(case when (i.type=7) then hi.bucket_count else 0 end AS int) AS [BucketCount],
CAST(ISNULL(si.cells_per_object,0) AS int) AS [CellsPerObject],
CAST(i.compression_delay AS int) AS [CompressionDelay],
~i.allow_page_locks AS [DisallowPageLocks],
~i.allow_row_locks AS [DisallowRowLocks],

        CASE WHEN ((SELECT tbli.is_memory_optimized FROM sys.tables tbli WHERE tbli.object_id = i.object_id)=1 or
        (SELECT tti.is_memory_optimized FROM sys.table_types tti WHERE tti.type_table_object_id = i.object_id)=1)
        THEN ISNULL((SELECT ds.name FROM sys.data_spaces AS ds WHERE ds.type=''FX''), N'''')
        ELSE CASE WHEN ''FG''=dsi.type THEN dsi.name ELSE N'''' END
        END
       AS [FileGroup],
CASE WHEN ''FD''=dstbl.type THEN dstbl.name ELSE N'''' END AS [FileStreamFileGroup],
CASE WHEN ''PS''=dstbl.type THEN dstbl.name ELSE N'''' END AS [FileStreamPartitionScheme],
i.fill_factor AS [FillFactor],
ISNULL(i.filter_definition, N'''') AS [FilterDefinition],
i.ignore_dup_key AS [IgnoreDuplicateKeys],

        ISNULL(indexedpaths.name, N'''')
       AS [IndexedXmlPathName],
i.is_primary_key + 2*i.is_unique_constraint AS [IndexKeyType],
CAST(
          CASE i.type WHEN 1 THEN 0 WHEN 4 THEN 4
                      WHEN 3 THEN CASE xi.xml_index_type WHEN 0 THEN 2 WHEN 1 THEN 3 WHEN 2 THEN 7 WHEN 3 THEN 8 END
                      WHEN 4 THEN 4 WHEN 6 THEN 5 WHEN 7 THEN 6 WHEN 5 THEN 9 ELSE 1 END
        AS tinyint) AS [IndexType],
CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
i.is_disabled AS [IsDisabled],
CAST(CASE WHEN filetableobj.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS [IsFileTableDefined],
CAST(ISNULL(k.is_system_named, 0) AS bit) AS [IsSystemNamed],
CAST(OBJECTPROPERTY(i.object_id,N''IsMSShipped'') AS bit) AS [IsSystemObject],
i.is_unique AS [IsUnique],
CAST(ISNULL(si.level_1_grid,0) AS smallint) AS [Level1Grid],
CAST(ISNULL(si.level_2_grid,0) AS smallint) AS [Level2Grid],
CAST(ISNULL(si.level_3_grid,0) AS smallint) AS [Level3Grid],
CAST(ISNULL(si.level_4_grid,0) AS smallint) AS [Level4Grid],
ISNULL(s.no_recompute,0) AS [NoAutomaticRecomputation],
CAST(ISNULL(INDEXPROPERTY(i.object_id, i.name, N''IsPadIndex''), 0) AS bit) AS [PadIndex],
ISNULL(xi2.name, N'''') AS [ParentXmlIndex],
CASE WHEN ''PS''=dsi.type THEN dsi.name ELSE N'''' END AS [PartitionScheme],
case UPPER(ISNULL(xi.secondary_type,'''')) when ''P'' then 1 when ''V'' then 2 when ''R'' then 3 else 0 end AS [SecondaryXmlIndexType],
CAST(ISNULL(spi.spatial_index_type,0) AS tinyint) AS [SpatialIndexType]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
LEFT OUTER JOIN sys.spatial_index_tessellations as si ON i.object_id = si.object_id and i.index_id = si.index_id
LEFT OUTER JOIN sys.hash_indexes AS hi ON i.object_id = hi.object_id AND i.index_id = hi.index_id
LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.tables AS t ON t.object_id = i.object_id
LEFT OUTER JOIN sys.data_spaces AS dstbl ON dstbl.data_space_id = t.Filestream_data_space_id and (i.index_id < 2 or (i.type = 7 and i.index_id < 3))
LEFT OUTER JOIN sys.xml_indexes AS xi ON xi.object_id = i.object_id AND xi.index_id = i.index_id
LEFT OUTER JOIN sys.selective_xml_index_paths AS indexedpaths ON xi.object_id = indexedpaths.object_id AND xi.using_xml_index_id = indexedpaths.index_id AND xi.path_id = indexedpaths.path_id
LEFT OUTER JOIN sys.filetable_system_defined_objects AS filetableobj ON i.object_id = filetableobj.object_id
LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id
LEFT OUTER JOIN sys.stats AS s ON s.stats_id = i.index_id AND s.object_id = i.object_id
LEFT OUTER JOIN sys.xml_indexes AS xi2 ON xi2.object_id = xi.object_id AND xi2.index_id = xi.using_xml_index_id
LEFT OUTER JOIN sys.spatial_indexes AS spi ON i.object_id = spi.object_id and i.index_id = spi.index_id
WHERE
(tt.name=@_msparam_2 and SCHEMA_NAME(tt.schema_id)=@_msparam_3)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'DistCritGroupData',@_msparam_3=N'dbo'

exec sp_executesql N'SELECT
cstr.name AS [Name],
cstr.is_not_for_replication AS [NotForReplication],
~cstr.is_not_trusted AS [IsChecked],
~cstr.is_disabled AS [IsEnabled],
CAST(cstr.is_system_named AS bit) AS [IsSystemNamed],
CAST(CASE WHEN filetableobj.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS [IsFileTableDefined],
cstr.definition AS [Text]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.check_constraints AS cstr ON cstr.parent_object_id=tt.type_table_object_id
LEFT OUTER JOIN sys.filetable_system_defined_objects AS filetableobj ON filetableobj.object_id = cstr.object_id
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
ISNULL(s1tt.name, N'''') AS [Owner],
CAST(case when tt.principal_id is null then 1 else 0 end AS bit) AS [IsSchemaOwned],
tt.name AS [Name],
tt.type_table_object_id AS [ID],
SCHEMA_NAME(tt.schema_id) AS [Schema],
obj.create_date AS [CreateDate],
obj.modify_date AS [DateLastModified],
tt.max_length AS [MaxLength],
tt.is_nullable AS [Nullable],
ISNULL(tt.collation_name, N'''') AS [Collation],
CAST(case when tt.is_user_defined = 1 then 1 else 0 end AS bit) AS [IsUserDefined],
CAST(tt.is_memory_optimized AS bit) AS [IsMemoryOptimized]
FROM
sys.table_types AS tt
LEFT OUTER JOIN sys.database_principals AS s1tt ON s1tt.principal_id = ISNULL(tt.principal_id, (TYPEPROPERTY(QUOTENAME(SCHEMA_NAME(tt.schema_id)) + ''.'' + QUOTENAME(tt.name), ''OwnerId'')))
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
LEFT OUTER JOIN sys.objects AS obj ON obj.object_id = tt.type_table_object_id
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(i.name=@_msparam_2)and((tt.name=@_msparam_3 and SCHEMA_NAME(tt.schema_id)=@_msparam_4))
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'CIX',@_msparam_3=N'DistCritGroupData',@_msparam_4=N'dbo'

exec sp_executesql N'SELECT
clmns.name AS [Name],
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(clmns.name=@_msparam_2)and((i.name=@_msparam_3)and((tt.name=@_msparam_4 and SCHEMA_NAME(tt.schema_id)=@_msparam_5)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'ObjectId',@_msparam_3=N'CIX',@_msparam_4=N'DistCritGroupData',@_msparam_5=N'dbo'

exec sp_executesql N'SELECT
clmns.name AS [Name],
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(clmns.name=@_msparam_2)and((i.name=@_msparam_3)and((tt.name=@_msparam_4 and SCHEMA_NAME(tt.schema_id)=@_msparam_5)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'OperatorType',@_msparam_3=N'CIX',@_msparam_4=N'DistCritGroupData',@_msparam_5=N'dbo'

exec sp_executesql N'SELECT
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(i.name=@_msparam_2)and((tt.name=@_msparam_3 and SCHEMA_NAME(tt.schema_id)=@_msparam_4))
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'PK__TT_DistC__199F41EAA68706DB',@_msparam_3=N'DistCritGroupData',@_msparam_4=N'dbo'

exec sp_executesql N'SELECT
clmns.name AS [Name],
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(clmns.name=@_msparam_2)and((i.name=@_msparam_3)and((tt.name=@_msparam_4 and SCHEMA_NAME(tt.schema_id)=@_msparam_5)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'DistCritTypeId',@_msparam_3=N'PK__TT_DistC__199F41EAA68706DB',@_msparam_4=N'DistCritGroupData',@_msparam_5=N'dbo'

exec sp_executesql N'SELECT
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(i.name=@_msparam_2)and((tt.name=@_msparam_3 and SCHEMA_NAME(tt.schema_id)=@_msparam_4))
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'UQ__TT_DistC__199F41EAC5355FAA',@_msparam_3=N'DistCritGroupData',@_msparam_4=N'dbo'

exec sp_executesql N'SELECT
clmns.name AS [Name],
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(clmns.name=@_msparam_2)and((i.name=@_msparam_3)and((tt.name=@_msparam_4 and SCHEMA_NAME(tt.schema_id)=@_msparam_5)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'DistCritTypeId',@_msparam_3=N'UQ__TT_DistC__199F41EAC5355FAA',@_msparam_4=N'DistCritGroupData',@_msparam_5=N'dbo'

I mean, that is just awful. And all it does is return all of those resultsets to SSMS; then there is some C# and/or SMO plumbing that actually steps through all the possible combinations and generates the script. This is hidden away in application code that is much harder to trace (especially without violating EULA), and so your ability to determine all of the issues they encountered when generating the script is pretty limited.

So I think if your goal is to have a nice, handy little piece of T-SQL code that is faster than right-clicking a table name in Object Explorer, you're going to be out of luck.

If I didn't have SSMS (or the plan was to create 60,000 table types), I would rather keep track of these creations using a DDL trigger than try to reverse engineer the metadata to generate the original statement. I wrote this tip a long time ago, but the same concepts apply today (you could maybe just filter the types of objects you keep track of):

SQL Server DDL Triggers to Track All Database Changes

Borrowing from that, you could create this table:

CREATE TABLE dbo.TableTypeCreationEvents
(
  EventDate    datetime NOT NULL DEFAULT sysutcdatetime(),
  EventDDL     nvarchar(max),
  SchemaName   nvarchar(128),
  ObjectName   nvarchar(128)
);

And then this trigger:

CREATE TRIGGER DDLCaptureTableTypeCreations ON DATABASE
  FOR CREATE_TYPE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @EventData xml = EVENTDATA();

  DECLARE
    @sch sysname = @EventData.value(N'(/EVENT_INSTANCE/SchemaName)[1]', N'nvarchar(128)'),
    @obj sysname = @EventData.value(N'(/EVENT_INSTANCE/ObjectName)[1]', N'nvarchar(128)'),
    @s nvarchar(max)
                 = @EventData.value(N'(/EVENT_INSTANCE/TSQLCommand)[1]', N'nvarchar(max)');

  IF EXISTS (SELECT 1 FROM sys.table_types
    WHERE name = @obj AND [schema_id] = SCHEMA_ID(@sch))
  BEGIN
    INSERT dbo.TableTypeCreationEvents(EventDDL,SchemaName,ObjectName)
      SELECT @s,@sch,@tab;
  END
END
GO

Now, some time has passed, and you want the script for the most recent version of the type dbo.DistCritGroupData? No problem:

SELECT TOP (1) EventDDL
  FROM dbo.TableTypeCreationEvents
  WHERE [schema_id] = SCHEMA_ID(N'dbo')
    AND name = N'DistCritGroupData'
  ORDER BY EventDate DESC;

This will return exactly the same script that you ran when you created the type, and since there is no ALTER TYPE, you don't have to worry about catching a table type up to any changes that have been made since its creation.


cannot find old connection string connecting to old database and not new one that has been defined

To add on to what Max is saying, you could use the following to get you the sql info and use the port information and sql text to figure out which service is doing what and then use those additional clues to help you isolate where it's coming from.

SELECT
   des.host_name
    , des.program_name
    , des.login_name
    , dec.client_net_address
    , dec.client_tcp_port
    , db_name(a.dbid) as db_name
    , object_name(a.objectid) as object_name
    , a.text as sql_text
FROM sys.dm_exec_sessions des
    INNER JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_id
    CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) a
WHERE des.is_user_process = 1;


How SQL Server detects a record internally?

Could anyone explain the two points(specially the second one) in more details?

I hope not, since they are both wrong. In SQL Server rows are identified by keys (in B-trees) or by row ids (in heaps). Non-unique clustered indexes have an internal uniqueifier column. The fact that often times an IDENTITY type column is also used as a primary key is orthogonal to the question.

Do not base your knowledge on stuff from posts on forums, including StackOverflow, and including this very post. A good source is the official documentation, eg. Tables and Index Data Structures Architecture. Read a good book, like the SQL Server Internals series.


Do BIT columns offer any performance advantages for CCIs?

First, we are not comparing like data types.
Bit is defined as:

An integer data type that can take a value of 1, 0, or NULL.
BIT

Whereas BIGINT is a larger integer that consumes a significant amount of space by default.
That means by default SQL Server has statistics on each of the BIT columns and only one set for the BIGINT.
As you rightfully noted, BIT columns are optimized:

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
int, bigint, smallint, and tinyint

there must be a reason you are even considering taking at least 8 - 10 binary values and shoving them into a numerical number.
After all, why lot just use INT and save half the space? Since last I checked, 2,147,483,648 is 10 characters for just 4 bits and BIGINT has about 19, which technically is less space than BIT of youvspoit then into columns.
But this is losing sight of what your data is. How does BIT answer questions on what it represents? 10010 is just a number greater than ten thousand and yet in binary that actually represents something. If in "Saving" space you are forcing transformation before even using the data, will it still be efficient?
But please, do not conflate BIT with a numeric data type like Tinyint or BIGINT. They serve two different purposes.


Neither of your approaches is good.

You have Tests that produce Results for some Measurement that is done with some Method. The Method may applies to multiple Test. (see sample Data below)

So this is my approach

Test(**TestID**, Name, Description, ...)
Measurement(**MeasurementID**, TestID[FK], Description)
Result(**Measurement[FK]**, **ResultNr**, ResultValue, Time)

Sample Data:

Test
0 | Rat in the Maze | Our rat John in the large Maze testing him find the exit
1 | Water on the oven | Boiling Water on our nuclear oven

Measurement
0 | 0 | Distance to Exit in cm
0 | 1 | Aggressiveness of the rat in Units of Aggressiveness
1 | 1 | Temperature of the water in K
2 | 1 | Energy currently consumed by nuclear oven in W
3 | 1 | Amount of water evaporated

Result
0 | 0 | 10 | 0s
0 | 1 | 12 | 2s
0 | 2 | 7  | 6s
...

Ok however this still relies on your use case. Maybe you want to keep some other table for input circumstances and map them to your results as a FK.

But note how it differs from your approach:

Number of tables independet from number of Tests, Results, Mesuret Values
No Empty rows and expandable for more Measured Values.

You can still add more information (Columns) to the Measurment table to keep track of the Device used to measure, the accuracy or whatever.


How to force a merge join without a hint

To switch from a nested loop join to a merge join without a hint you need the plan with the merge join to have a lower estimated cost than the plan with the nested loop. Technically, you also need the query optimizer to find the lower cost plan during plan exploration but there isn't a lot that you can do about that.

First, a note about merge join costing. In my experience, SQL Server is pretty pessimistic about merge join costing. This seems understandable when you consider how much the IO requirements for a merge join can change when small amounts of data are changed. Consider a simple example in which one table has the integers from 1 - 10000 and the other has the integers from 100001 to 1100000:

DROP TABLE IF EXISTS X_SMALL_TABLE;

CREATE TABLE X_SMALL_TABLE (ID INT NOT NULL PRIMARY KEY (ID));

INSERT INTO X_SMALL_TABLE WITH (TABLOCK) (ID)
SELECT N
FROM dbo.GetNums(10000);

UPDATE STATISTICS X_SMALL_TABLE WITH FULLSCAN;

DROP TABLE IF EXISTS X_LARGE_TABLE;

CREATE TABLE X_LARGE_TABLE (ID INT NOT NULL PRIMARY KEY (ID));

INSERT INTO X_LARGE_TABLE WITH (TABLOCK) (ID)
SELECT N + 100000
FROM dbo.GetNums(1000000);

UPDATE STATISTICS X_LARGE_TABLE WITH FULLSCAN;

If I join the tables together I will get 0 rows returned. If I force a MERGE JOIN then SQL Server will scan all of the rows from the small table and only one of the rows from the large table. By default, SQL Server will traverse through the tables in ascending order according to the join key. However, the query optimizer estimates that all 1000000 rows will be scanned from the large table and subsequently assigns a relatively large cost to that operator:

As we can see from the actual plan, only one row was scanned. What happens if we insert just one new row into the small table?

INSERT INTO X_SMALL_TABLE
SELECT 1200000;

UPDATE STATISTICS X_SMALL_TABLE WITH FULLSCAN;

Since the merge join algorithm goes in ID order once we hit ID 10000 in the small table we should loop through all of the rows in the large table until we hit ID 1200000.

The actual plan confirms that all rows were read from both tables. So small changes in data can lead to large differences in merge join performance. Based on that it seems reasonable for SQL Server to be pessimistic about merge join costing, which will work against you if you are trying to get a merge join to appear in your query. In your second query you scan all of the rows from CustomerDiscountCards. That operation alone could have a higher estimated cost than the entire nested loop join plan.

When using hints to force a MERGE JOIN note that the MERGE JOIN join hint also adds a FORCE ORDER hint to the query. Based on the information in the question, your second query doesn't run quickly strictly because of the MERGE JOIN. It runs quickly because of the new join order. The optimizer joins together CustomerDiscountCards and DiscountCards using full scans on both tables and finds 0 matching rows. Any inner join against a 0 row result set is going to be fast because query execution can stop at that point (disregarding blocking operators which occur before that). So instead of focusing on forcing a merge join, which will probably be difficult without hints, I would focus on getting the join order correct.

A simple way to force join order is to materialize the first result set that you want into a temporary table. You could join CustomerDiscountCards and DiscountCards in an initial query and put the matching relevant rows into a temp table. This should run in under a second for your test case because you won't actually be inserting any rows into the temp table. If your second query joins to the empty table it should finish almost instantly. You will need to test the performance of the query for other parameter values in which you actually get rows back because you could be putting a fair number of rows and columns into tempdb.

You may be able to rewrite your SQL to effectively force the join order without using a temp table. Of course using the FORCE ORDER hint is the easiest way to do that but you said that you don't want to use hints and that hint can be a difficult one to use. It applies to the entire query so if your data ever changes you could end up with poor performance. For an idea on how to force join order without the hint, it looks like pairing together the tables that you want to be joined first in a derived table and adding a superfluous TOP operator seems to push SQL Server in the right direction:

INNER JOIN
(
    SELECT TOP 9223372036854775807 ...
    FROM
    CustomerDiscountCards
    INNER JOIN DiscountCards ON ...
) t ON ...

However, I cannot conclude if SQL Server is actually forced to evaluate that join separately from the rest of the query. As before you will need to test.

To give you a final option to consider, since the initial problem appears to relate to row goals you could hide the row goal from the optimizer. One way to accomplish that is to use the OPTIMIZE FOR query hint and to use a variable for your TOP statement. Consider the query snippet below:

DECLARE @TOP BIGINT = 10;

SELECT TOP (@top)
...
OPTION (OPTIMIZE FOR (@TOP = 9223372036854775807))

That query will only return the first 10 rows that it finds in the result set. However, the plan will be created as if it returns the first 9223372036854775807 rows. This may disfavor the nested loop join plan that you experienced the issues with, but it's hard to say how well this will perform in general.

Ultimately, it's hard to say more with the information that we have in the question. Presumably your query can return rows in some cases (there isn't much reason to continually run a query that always returnns no rows) so you'll need to test your options for all of the different cases.


How to "insert into select from" skipping exceptions

Unfortunately there is no is_json() or similar type check, but you can write your own:

create or replace function is_valid_json(p_text text)
   returns boolean
as
$$
begin
  if p_text is null then
    return true;
  end if;

  if p_text::json is not null then
    return true;
  end if;
exception
  when others then
    return false;
end;
$$
language plpgsql;

Then you could do:

insert into T( id, jblob )
select id, jblob::jsonb
from S
where is_valid_json(jblob);

Or do the test and cast in one go:

create or replace function cast_if_possible(p_text text)
   returns jsonb
as
$$
begin
  return p_text::json;
exception
  when others then
    return null;
end;
$$
language plpgsql;

and

insert into T( id, jblob )
select id, cast_if_possible(jblob) as jblob
from S;

That would store NULL values for invalid JSON values. If you don't want that you would need to add condition to exclude those.

If you have a substantial number of (invalid) duplicate values in the table, then declaring the function as stable then Postgres can cache the results of the function call, which might improve performance.

However this is not going to be fast as the exception handler and PL/pgSQL have a substantial performance overhead.


How to Get the First in and Last out in night shift?

For dayshift use

SELECT DISTINCT
       user_id,
       DATE(verify_date) the_date,
       MIN(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) min_datetime,
       MAX(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) max_datetime
FROM tbl_attendance
ORDER BY user_id, the_date;

For nightshift use

WITH cte AS (SELECT user_id,
                    verify_date,
                    LEAD(verify_date) OVER (PARTITION BY user_id ORDER BY verify_date) lead_date
             FROM tbl_attendance)
SELECT *
FROM cte
WHERE DATE(verify_date) != DATE(lead_date)
ORDER BY user_id, verify_date;

fiddle


How to only restore a specific filegroup?

A filegroup restore can be done on a database in FULL recovery model.  There are requirements and consequences of the restore.

You must be using Enterprise Edition.
The primary filegroup must be the first restore.
Filegroups that are not yet restored remain offline.

Start reading at this topic in MSDN:  http://msdn.microsoft.com/en-us/library/ms177425(v=sql.105).aspx

Read the topic: "Piecemeal Restore Under the Full Recovery Model"


Speeding up Postgresql manual index reset?

Are these databases in the same Postgres Cluster?

If so, you might consider a Foreign Data Wrapper, to create a "connection" to each source database and insert the data directly into the new table:

CREATE EXTENSION POSTGRES_FDW ;

CREATE SERVER foreign_db
FOREIGN DATA WRAPPER POSTGRES_FDW
OPTIONS
( HOST       'host_name'
, DBNAME     'database_name'
, UPDATABLE  'false'
);

CREATE USER MAPPING FOR fred
SERVER foreign_db
OPTIONS ( USER 'fred', PASSWORD 'password' );

CREATE FOREIGN TABLE foreign_old_table
(
   ...
)
SERVER foreign_db
OPTIONS ( TABLE_NAME 'old_table' );

Then; finally

INSERT INTO new_table ( everything, except, pk )
SELECT ( everything, except, id )
FROM foreign_old_table ;


MySQL is taking too much of the innodb buffer pool size for your table locking.

Insert innodb_buffer_pool_size=2g in my.cnf file, which is most probably in /etc/mysql/


Replication between SQL Server 2005 - 2005

I have found a solution to my problem. Beware though, the steps outlined below broke regular replication (or so it seems). Nevertheless, I was able to get replication running, and now there are no undelivered commands building up in the distribution database:

I Command You To Deliver Those Commands

The solution provided there is to run:

exec sp_changepublication
    @publication = 'xxxxx',
    @property = N'allow_anonymous',
    @value = 'false'
exec sp_changepublication
    @publication = 'xxxxx',
    @property = N'immediate_sync',
    @value = 'false'


Edition upgrade on SQL Server failover clustered instance

No, unlike Service Packs and Cumulative Updates where you're responsible to keep the bits in the same state across all nodes, the Edition upgrade applies to both nodes at once. I applied it myself to the 'passive' node and Configuration Manager on the active node showed Enterprise immediately. You still need to restart the service though, as SSMS showed Standard in Properties even after refreshing and reconnecting, but immediately reflected Enterprise after refresh once I'd restarted the service.


Execution Plan StatementText contains incorrect syntax

&#xD;&#xA; are carriage return/line feed. You could use SSMS find/replace to either remove them, or set them to start new lines. Just use the regular expression option.