Add a New Column with ALIASES on 'date' by automation based on search results I am trying to create a new column based on date search result.

I have written a code in the link SQL fiddle as I am looking for date automation on each column based on the date format. the date format Coloum can be up to 7 column or less or more, just based on how many dates in the record.

SQL fiddle

Here is a screenshot as I would like to get this similar and the data is based on the number of sold SKU.

Update status:-

As got advice and helpful from @ypercube

so far I got sorted with Fix static Pivot table but not dynamic Pivot table,

Here is a Static pivot table

    SELECT
    ls.sku                                AS list_sku,
    COALESCE( MIN(suo.sku), 'Not Sold' )  AS sold_sku,
    SUM(CASE suo.date WHEN '2018-04-30' THEN units_ordered ELSE 0 END) AS Monday,
    SUM(CASE suo.date WHEN '2018-05-01' THEN units_ordered ELSE 0 END) AS Tuesday,
    SUM(CASE suo.date WHEN '2018-05-02' THEN units_ordered ELSE 0 END) AS Wednesday,
    SUM(CASE suo.date WHEN '2018-05-03' THEN units_ordered ELSE 0 END) AS Thursday,
    COALESCE( SUM(suo.units_ordered), 0 ) AS total_sold
 FROM   tbl_list_sku AS ls
       LEFT JOIN tbl_sku_units_order AS suo
              ON suo.sku = ls.sku
GROUP  BY ls.sku
ORDER  BY total_sold DESC ;

Here is output results

So far I will update  this answers when I got a fix with Dynamic table

Update: Got fix with Dynamic Pivot Table and please with it

    SET
   @sql = NULL;
SELECT
   GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(suo.date = ''', date, ''', suo.units_ordered, 0)) AS ', CONCAT("`", date, "`") ) ) INTO @sql
FROM
   tbl_sku_units_order;
SET
   @sql = CONCAT('SELECT ls.sku AS "List SKU",
 COALESCE( MIN(suo.sku), "Not Sold" ) AS "sold_sku", ', @sql, ' , COALESCE( SUM(suo.units_ordered), 0 ) AS total_sold
FROM
   tbl_list_sku AS ls
   LEFT JOIN
      tbl_sku_units_order AS suo
      ON suo.sku = ls.sku
GROUP BY
   ls.sku
ORDER BY
   total_sold DESC');
 PREPARE stmt
FROM
   @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is a result

I know I'm not great code in the world, but delight with results and thank for your help @ypercube.


DB2 zOS increasing DSSIZE vs adding partition? I'm trying to load data into a DB2 table. The tablespace has 3 partitions with about 60M,60M,70M rows. The 3rd partition is failing to load. It gives me a warning that loading this partition's dataset will exceed the maximum size of # pages.

The DSSIZE is currently set to 4G for this table space. To increase to 8G, I would need to change the storage groups to SMS.

The data is currently partitioned by a numeric range.

P1 00-33
P2 33-66
P3 66-100

Would adding another partition and reorginize the data be a better solution?

P1 00-25
P2 25-50
P3 50-75
P4 75-100

Are there any other suggestions that would help load this data?

As long as there is no application code that uses the partitioning scheme in their logic, adding a partition is a solution to your space issue.

I would suggest that you use 5 partitions instead of 4. Your data should then spread out more evenly.

P1 00-20
P2 21-40
P3 41-60
P4 61-80
P5 81-100


What's the differences between the two version of oracle client download file, "client" and "client home"? I noticed the Oracle database download page has two groups of clients for users to download. There is a thing called "client" and has 32-bit version and 64-bit version. Also, there is a thing called "client home", which also has both 32-bit and 64-bit version. What are the differences between them? When should I use which?

Update

The download page URL is https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle19c-linux-5462157.html

LINUX.X64_193000_client.zip is a fully featured installer where you can choose what to install and where, then the installer copies the files to the destination and registers the client.

LINUX.X64_193000_client_home.zip is an "image" of and "Administrator" type installation, that you unzip to the destination folder then register it.

Simplified Image-Based Oracle Database Client Installation

  Starting with Oracle Database 19c, the Oracle Database client software
  is available as an image file for download and installation. You must
  extract the image software into a directory where you want your Oracle
  home to be located, and then run the runInstaller script to start the
  Oracle Database client installation. Oracle Database client
  installation binaries continue to be available in the traditional
  format as non-image zip files.

  As with Oracle Database and Oracle Grid Infrastructure image file
  installations, Oracle Database client image installations simplify
  Oracle Database client installations and ensure best practice
  deployments.

About Image-Based Oracle Database Client Installation

  Starting with Oracle Database 19c, installation and configuration of
  Oracle Database Client software is simplified with image-based
  installation.

  To install Oracle Database Client, create the new Oracle home, extract
  the image file into the newly-created Oracle home, and run the setup
  wizard to register the Oracle Database product.

  You must extract the image software (client_home.zip) into the
  directory where you want your Oracle Database Client home to be
  located, and then run the Setup Wizard to start the Oracle Database
  Client installation and configuration. Oracle recommends that the
  Oracle home directory path you create is in compliance with the Oracle
  Optimal Flexible Architecture recommendations.

  Using image-based installation, you can install Oracle Database Client
  32-bit and 64-bit configurations of the Administrator installation
  type.

  As with Oracle Database and Oracle Grid Infrastructure image file
  installations, Oracle Database Client image installations simplify
  Oracle Database Client installations and ensure best practice
  deployments. Oracle Database Client installation binaries continue to
  be available in the traditional format as non-image zip files.


Ordering by date and other columns I have a one table Q&A like below:

CREATE TABLE  `totaalconceptterreinonderhoud`.`acties`
(
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
      `type` int(11) NOT NULL DEFAULT '3' COMMENT 'Actie types:\n1 = vraag\n2 = antwoord\n3 = afspraak\n4 = nieuws\n',
      `aanvang_actie` date NOT NULL COMMENT 'Aanvang afspraak datum + tijd',
      `start_tijd` time NOT NULL COMMENT 'Start tijd',
      `eind_tijd` time NOT NULL COMMENT 'Eind tijd',
      `proj_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Koppeling met Project',
      `onderwerp` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Onderwerp afspraak',
      `notitie` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Tekstuele omschrijving',
      `locatie` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
      `meld_aan_contacts` int(11) NOT NULL DEFAULT '1' COMMENT 'Melden aan contactpersonen',
      `koppel_id` int(11) NOT NULL DEFAULT '0' COMMENT 'koppelinng met andere id',
      `aangemaakt_door` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Wie heeft laatst aangepast',
      `aanmaak_datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Wanneer aangemaakt',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Acties'

There are 4 types of record:

type=1 (question)
type=2 (answer)
3 and 4 (not relevant now)

What I want is to list all questions with their answers below. The questions and answers have a koppel_id field that connects them. The ordering must be last asked question first, with answers right below.

Is this hard to do? Any help would be nice.

It would be something like the following. Please verify the syntax and functionality as I have not tested it. There might be a better way to do this.

SELECT * FROM
(
    SELECT
        @rownum1 := @rownum1 + 1 AS rownum,
        acties.*
    FROM
        acties
        INNER JOIN (SELECT @rownum1 := 0) AS rownum
    WHERE
        type = 1
    ORDER BY
        aanmaak_datum DESC
) AS question
UNION
SELECT * FROM
(
    SELECT question2.rownum, answers.*
    FROM acties AS answers
    INNER JOIN
    (
        SELECT
            @rownum2 := @rownum2 + 1 AS rownum,
            acties.*
        FROM
            acties
            INNER JOIN (SELECT @rownum2 := 0) AS rownum2
        WHERE
            type = 1
        ORDER BY
            aanmaak_datum DESC
    ) AS question2 ON answers.koppel_id = question2.koppel_id
    WHERE answers.type = 2
) as answer2
ORDER BY rownum, type


Backup File Size Almost 50% of Database File Size with TDE Enabled I receive a full backup of a very large database once per week from a vendor. It contains transactional data which we then load into our data warehouse.

Fully restored, the database is around 707GB. It is comprised of 3 files, 1 data file which is about 385GB, a second data file which is about 132GB, and 1 log file which is about 190GB.

This database has TDE (transparent data encryption) enabled. Everything that I've read says that with TDE, backup compression should be minimal, if any. However, the full backup file which I receive is 386GB in size. This is about 54% of the total file size before the backup.

I have been told that the vendor is performing native SQL backups and the file is not decrypted before they back it up.

How are the backup files so much smaller than the database files? I'm really hoping someone can help shed some light on this for me.

Community Wiki answer generated from comments on the question by QueueMann and Aaron Bertrand♦

Database backup files throws away any unused space in your data files in the backup process.

TDE doesn't have to encrypt empty pages, and the backup process doesn't have to include the space in the backup, but they are still empty pages contributing to data file size.


SSMS 2016 Status bar -no option to see session id I am using SSMS 2016 version.

Microsoft SQL Server Management Studio                      13.0.16106.4
Microsoft Analysis Services Client Tools                    13.0.1700.441
Microsoft Data Access Components (MDAC)                     10.0.14393.0
Microsoft MSXML                                             3.0 6.0
Microsoft Internet Explorer                                 9.11.14393.0
Microsoft .NET Framework                                    4.0.30319.42000
Operating System                                            6.3.14393

In previous version I was able to see session id at the status bar or tab text.
I could navigate to Tools-->Options-->XML-->Editor Tab and Status Bar and turn it on of off.  I do not see the same in SSMS 2016.

Anybody know if this option is removed or relocated under other option.

I found it. It was hidden due to long name of my instance and userId.  Once I hover the cursor on top I could see it. Here is a screenshot.


How frequent do we Analyze Table Statistic We have a Oracle 11g database running with a table that insert entries with around 100records/min. We met some spiking issue mainly due to table is performing full table scan. We have indexed the required key, however, sometime it does not take into effect.

What we do is to do a 'analyze table statistic' and it resolve the issue.

May i ask, why do oracle require a table to be analyzed? how often do we analyze a table? Currently we have set a job scheduler for every 3 hours, however, it seems like sometime within 30mins it require another statistic to be analyzed

Do not use analyze table statistic, this is only supported for backward compatibility (this was already the case in Oracle 10g), see ANALYZE

  Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. The optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS. See PL/SQL Packages and Types Reference for more information on the DBMS_STATS package.

Use DBMS_STATS.GATHER_TABLE_STATS() instead, see DBMS_STATS. Be default Oracle collects statistics automatically during night time. Usually this is sufficient. Inserting data does not necessarily mean that you also need new statistics. Typically new data are similar to existing data, Min/Max value or distribution of values does not vary much.


Can not ping Always On listener from outside primary node I just setup SQL Server 2012 (on Windows Server 2012) Always On feature and have a problem with connecting to SQL Server Listener from non-primary node (it works from primary node though).

Here is the scenario:

2 nodes in different subnets:
   SQL1 10.0.2.11 (primary)
   SQL2 10.0.6.11 (secondary)
Listener:
  Name: SQLPROD1
  Port: 1433
  Subnets       IP address
  10.0.2.0/24   10.0.2.102
  10.0.6.0/24   10.0.6.102

I can connect to SQLPROD1 in SSMS on primary node (either SQL1 or SQL2) but can't connect (not even ping) SQLPROD1 from secondary or from any other client in the network.

I checked firewall and it doesn't seem to be the cause of the problem. I can ping SQL1 or SQL2 from any client but can't ping SQLPROD1.

Beside this, everything else works in Always On. I can successfully fail-over and see databases on other node.

We had the same issues as this. Recreated the DNS entries for the two listeners and making sure "Allow any authenticated user to update DNS records with the same owner name" is checked. Believe the failover event caused issues with the listener not updating it's IP address.


Good encryption performance I'm converting a sql server database to postgresql 9.4. I'm stuck on something that is really really slow.

I use copy to bulk insert 200 files of 5000 lines each into 200 tables (one file per table).

Each table have a before trigger that use pgcrypto to encrypt two bytea column from the files and 3 default text into 3 additional column

The trigger looks like this :

select password into var_pwd from temp_table;  --The password is put into a temporary table before the copy.

new.bytea1 : = pgp_sym_encrypt_bytea(bytea1,var_pwd);
new.bytea2 : = pgp_sym_encrypt_bytea(bytea2,var_pwd);
new.text1 : = pgp_sym_encrypt('default text1',var_pwd);
new.text2 : = pgp_sym_encrypt('default text2',var_pwd);
new.text3 : = pgp_sym_encrypt('default text3',var_pwd);

It takes 55 minutes to load everything with the trigger(3 minutes without it, so the encryption is the problem).

It takes around 7 minutes to insert and encrypt everything in Sql Server (if it is of any use, i use a symmetric key, the EncryptByKey command and an instead of trigger. Everything else is the same)

The data needs to be decrypted, so a two way encryption is needed.

Is there any alternative i could try to speed up the query (another encryption extension, some postgresql.conf settings i should look into / etc)?

Thanks in advance.

It's probably fastest to use COPY (or \copy) to copy the data into unencrypted tables. Disable triggers, foreign keys, indexes.

Then encrypt, and add your indexes and keys again.

Use one SQL operation to encrypt, like

update my_table set
    bytea1 = pgp_sym_encrypt_bytea(bytea1,var_pwd),
    bytea2 : = pgp_sym_encrypt_bytea(bytea2,var_pwd)
    ....;

Or do a insert into...select into a new table, encrypting in the select.

See also https://wiki.postgresql.org/wiki/Performance_Optimization


How to get all documents from a particular chunks in MongoDB? From mongos log, I got the following message

 balancer move failed: { chunkTooBig: true, estimatedChunkSize: 408839903, ok: 0.0, errmsg: "chunk too big to move" } from: shard4 to: shard1

I know this issue occurs due to either

documents size to large,
index key not granular enough, and/or
chunkSize too small

While I'm confident that case 2 and 3 are not the cause, I want to see whether someone abuses the database by inserting extra large documents.

The mongos log shows the docs are in the range :

min: { Country:"US", City:"LA" } max: { Country:"US": City:"NY" }

min: { Country:"FR", City:"Paris" } max: { Country:"US": City:"New York" }

I want to aggregate the schema of documents within this range, and see if some documents contain extra fields.

Since the indexes are not monotonically increasing, I can't get all documents within this range (or can i?).

How can I

get the chunkID with the query

  db.coll.find({ Country:"US", City:"LA" })
  get all the docs within this chunk?

  Other suggestions are welcome.
  Thanks in advance

use config show collections

  db.chunks.findOne()

  use config db.settings.save({_id: "chunksize", value: 2})

  sh.status()


Strategy to scale a comparison table query and load time given a product table

PRODUCT
id   some_column
1    10
2     2
3     8

when comparing all them I get a triangular matrix that I load from a csv file

COMPARISION
id  product_left  product_right   result
1              1              2        3
2              1              3        8
3              2              3        2

where I want to perform a query that returns all the best n comparisons for a given product

SELECT * FROM COMPARISION WHERE ( product_left = 1 OR product_right = 1 ) ORDER BY result DESC LIMIT n

I've made an index for product_left and another for product_right column

But I'm not convinced that it will scale for tens of thousands products

ie:
20k products will produce ~200 million comparisons
the load takes 15 hours in regular desktop computer
and it will get slower to load with more indexes

so, which should be the best strategy to scale it?

partitioning
do a different query
index the result column to help order by clause
...

you can find the solution here
https://github.com/rubentrancoso/cosinsimilarity

This will probably run faster:

( SELECT * FROM Comparision WHERE product_left  = 1
           ORDER BY result DESC  LIMIT n
)
UNION DISTINCT
( SELECT * FROM Comparision WHERE product_right = 1
           ORDER BY result DESC  LIMIT n
)
ORDER BY result DESC  LIMIT n

If there is no change of fetching the same row from the two SELECTs, change to UNION ALL to be a little faster.

Have these two 'composite' indexes:

INDEX(product_left,  result)
INDEX(product_right, result)

Yes, the ORDER BY and LIMIT are duplicated.  If you also need OFFSET, then it gets more complex, but still possible.

Why?  With the indexes I gave you, each SELECT will optimally filter (WHERE) and sort (ORDER BY), thereby touch only n rows.  Then the UNION puts together the 2*n rows, re-sorts and delivers the desired n.

With just INDEX(product_left) it will gather all the 1 rows, sort them and peel off n -- slower.

Without the UNION, the query will simply scan the entire table, ignoring your indexes (or any others).  (OK, there is a chance of some index being usable, but what I provided is better.)  Use EXPLAIN SELECT ... to see what is going on.  (Ask for help it it is not obvious.)


Efficient way to query set of rows from MySQL I am using MySQL database engine. My Java application uses simple JDBC to retrieve data from database.
I have following database schema: main table with two columns: (INTEGER id, BLOB data). id is primary key.
From user actions I have set of few ids (x, y, ... all integers) for which I want to fetch data.
What is most efficient way to build query? Is there any performance difference if I use

SELECT data FROM table WHERE id IN (x, y, ...)

or

SELECT data FROM table WHERE id = x OR id = y OR ...

or maybe there is another way to build that query?

First, try putting together the ids in a query (a tableless query)

SELECT 1 id UNION
SELECT 2    UNION
SELECT 3    UNION
SELECT 4    UNION
SELECT 5    UNION
SELECT 6    UNION
SELECT 7;

Watch it work...

mysql> SELECT 1 id UNION
    -> SELECT 2    UNION
    -> SELECT 3    UNION
    -> SELECT 4    UNION
    -> SELECT 5    UNION
    -> SELECT 6    UNION
    -> SELECT 7;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.00 sec)

mysql>

Now, JOIN that tableless query to the data table

SELECT
    B.mydata
FROM
    (SELECT 1 id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) A
    INNER JOIN mytable B ON A.id = B.id
;

or

SELECT
    B.mydata
FROM
    (SELECT 1 id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) A
    INNER JOIN mytable B USING (id)
;

Give it a Try !!!


How should I represent a latitude and longitude in Postgres without using PostGIS? How should I represent a latitude and longitude in Postgres without using PostGIS? The system I am using does not allow SQL passthrough so I cannot use POSTGIS.

You can use the builtin POINT datatype without postgis.


Working with Materialized View I have a materialized view which takes around 57 second to be created and I'm using PostgreSQL 9.4.

When I do an insert into a table, a trigger will call a trigger function which will do a REFRESH MATERIALIZED VIEW CONCURRENTLY view the trigger fires after each statement (insert,update,delete)

What happens is, to do a insert it will take around 57 seconds.

How can I fix this and still use a materialized view?

I had this implemented for a long time, and never had this problem, recently I've added pgbouncer, can it be the cause of this?

Generally, refreshing the MV immediately only seems reasonable if write access to underlying tables is a rare event. A statement-level trigger is better than a row-level trigger, but may still prove too much for big tables. I would consider a solution that polls the database every n minutes checking for updates. You could have a trigger write to a table with a single row.
Or you could use LISTEN / NOTIFY.

As long as you want to propagate every change (almost) immediately, you may be better off with row-level triggers on insert / update / delete writing to a hand-knit "materialized view" (a table), like @trygvis suggested.

Since pgbouncer keeps sessions open, there may be locking issues with uncommitted transactions slowing the process down, but I am merely speculating here. You could run tests in a copy of your database without pgbouncer. Or check for zombi transactions by looking up pg_stat_activity.


SQL Server Agent Job failure We have a log backup job running every hour. The log backups are being taken for all the databases except one of them. For security purposes lets call this database FAILED_DB.
These are the things I have tried:

I looked at the history of the job and it has been failing since Friday.

In the job history, it states that Executed as user: ...tabase backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). However, FAILED_DB is set to Full recovery model & there has been a full backup taken last night.

In the SQL Server logs, it states BACKUP failed to complete the command BACKUP LOG FAILED_DB. Check the backup application log for detailed messages.

I also checked the Event Viewer, and it still states to check the backup application log for detailed message.

Has anyone ever faced this error before? And if so, what was your way to resolve this? Any pointers would be much appreciated.

You included the following in your question:

Executed as user: ...tabase backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).

The error you're seeing (error 4214) indicates that there has been no current database backup performed.
This is the actual text of that error message:

BACKUP LOG cannot be performed because there is no current database backup.

I was able to look that up using this query:
SELECT m.message_id
    , m.severity
    , m.text
FROM sys.messages m
WHERE m.message_id = 4214
    AND m.language_id = 1033; --Show the English version of the message

This means you need to ensure you take a full database backup in order for the BACKUP LOG to complete without error.
If, by chance, you changed the recovery model of the database from simple recovery to full recovery since the last full backup occurred, you won't be able to do a BACKUP LOG until you take another full database backup.  Changing the recovery model doesn't actually take effect until a full database backup completes.  The following excerpt was taken from the Microsoft Docs:

Immediately after switching to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
NOTE: The switch to the full or bulk-logged recovery model takes effect only after the first data backup.


How to find index organized table I'm working on Oracle 11g database and I need to list all index-organized tables.
Is there a dba's view to query for that ?

Thanks

Here is the query you require:

SELECT TABLE_NAME
FROM DBA_TABLES
WHERE IOT_TYPE IS NOT NULL;

The documentation states:

  If this is an index-organized table, then IOT_TYPE is IOT,
  IOT_OVERFLOW, or IOT_MAPPING. If this is not an index-organized table,
  then IOT_TYPE is NULL.


Confused over encoding/locale in postgresql I am a little confused over the difference between encoding and locale as it pertains to the postgresql database.

When initializing the database, you can specify both an encoding and a locale.  Correct me if I am wrong but I assume the encoding defines what the database is actually stored as on the computer.

So if I specify UTF8, all the characters in the UTF8 character set would be valid characters.  If I specified WIN1252, all those characters would be valid characters.  What I dont get is where the locale comes into play.  If I specify my encoding as UTF8 and then specify my locale as English_United States.1252, what does that exactly mean?

I think the WIN1252 character set is a subset of UTF8 so is the locale just specifying the subset of characters to be used from the UTF8 character set?

From what I have read is that UTF8 can be used with ANY locale so what is the point in specifying different encodings if UTF8 is so ubiquitous and the locale is really the one specifying the specific character set to be used?

Also, on Linux, the locale can be specified like so: en_us.utf8.  So the database encoding is specified in the locale?  If the encoding is specified in the locale, why even have a -encoding flag when initializing the database?

The encoding defines the very basic rules how characters are represented in binary format (like @a_horse explains in his comment). It should be mentioned that the server encoding has to match the client encoding for successful communication. Postgres can translate if necessary, there is a dedicated setting client_encoding for this.

The locale is a superset of settings, which can be split up for PostgreSQL into

LC_COLLATE
LC_CTYPE
LC_MESSAGES
LC_MONETARY
LC_NUMERIC
LC_TIME

The settings of particular interest for you are LC_COLLATE (defines how strings are sorted) and LC_TYPE (defines the type of characters).
In older versions, these two settings could not be changed after a database had been initialized. Since Postgres 9.1 you can at least override the collation setting when needed.


Query runs slowly when a non-indexed column is added to the WHERE clause My SQL Server houses a logs table that has 4 columns:

RowID - GUID PRIMARY KEY
OperationID - GUID (NONUNIQUE INDEXED)
Line - INT
Message VARCHAR(250)

Some table facts:

An operation may have anywhere between 1 and 380,000 messages
operationID is the same for all log lines that pertain to a particular operation
Line increments from 1 for each operationID (hence the combination of operationID and Line is unique- I don't know why the original table designer added a GUID PK)
The message varies considerably
A nonclustered nonunique index is present on OperationID. It INCLUDEs no other columns
The table has about 13.5 million rows

I find the following query reasonably performant, it returns 7000 rows in 5 seconds or less. The query plan shows index seek for all rows matching the operationID, key lookup on the primary key and then nested loops to produce the resultset:

SELECT *
FROM logs
WHERE operationid = <some guid>

Side info: SQL Server Management Studio complains that the query would be better served if an index were created on OperationID that INCLUDEd line and message.

If I add something that I consider trivial:

SELECT *
FROM logs
WHERE operationid = <some guid> AND Line > 2000

Suddenly the query takes well over 3 minutes to return a result. My immediate thought was to look at the execution plan differences. The only significant difference I find is that in addition to the "seek predicate" that is associating the table rows with index rows, there is a "predicate" section in the tooltip of the table for the nonperformant query. The "predicate" is listed as "line > 2000".

Side info: SQL Server has switched to complaining that the query would be better served by indexing OperationID and Line and INCLUDEing Message.

Is SQL Server doing things in the wrong order? i.e. is it looking through 13.5 million rows for all those whose Line > 2000, and then filtering and joining to the index?

As a human I can see this query should be sub-5-second, because filtering all the rows based on the index first (13.5M -> 7K) and then filtering 7K rows for the matching Line would be a better strategy, than finding potentially millions of rows with line > 2000. I was expecting that filtering any large table on an indexed column is something SQL Server would prefer over starting with non indexed columns in a where clause?

I don't really need an index on Line, as we don't select entries based on line ranges in common use - this is just for code development, I know for this particular operation I'm analysing now that all line entries before 2000 are uninteresting. I'm more interested as to why a query WHERE indexedcol = value is super fast but WHERE indexedcol = value and unindexedcol = othervalue runs like a dog. And by that I don't mean a greyhound.

You're getting a secondary predicate on the key lookup operation. That's going to slow stuff down. It's not doing a full scan, but remember that even seeks are just limited scans. So the key lookup is still doing what it did before, but now it's also adding a filtering step because of that added index.

You could look at exploring a covering index, however, because you have a SELECT *, covering means all columns, effectively creating a second clustered index. There'll be some sacrifice of performance on data modification queries and on disk space (depending on where you are on Azure SQL Database, that could bump you to another tier).

Instead, I'd start with experimenting with your existing nonclustered index. See if adding this new column to the key of that index helps at all. Keep it as the second column, other wise you're changing your histogram which could cause other issues.

You're going to have to experiment to see for sure what works best in your situation.


Oracle Conditional sum This question was born in my mind looking at this kind of code:

select sum(value)
  into positive
  from my_table
 where value > 0;

 select sum(value)
  into negative
  from my_table
 where value < 0;

This works well for take the totals of this table.

But, my question is how I can get the sub-totals of my_table using only a statement.

expected result:

Having this data on my_table

VALUE
-----
  1.0
 -2.0
  3.0
 -4.0
  5.0
 -6.7
  8.9

I'd a DML statement who can give me this output

positive | negative
---------+---------
    17.9 |    -12.7

Is this kind of operation possible?
If yes, how can i get it?

SQL> r
  1  with t as (
  2      select  1.0 as x from dual
  3      union all select -2.0 as x from dual
  4      union all select  3.0 as x from dual
  5      union all select -4.0 as x from dual
  6      union all select  5.0 as x from dual
  7      union all select -6.7 as x from dual
  8      union all select  8.9 as x from dual)
  9  select sum(case when x > 0 then x else 0 end) as sum_of_positives,
 10         sum(case when x < 0 then x else 0 end) as sum_of_negatives
 11*   from t

SUM_OF_POSITIVES SUM_OF_NEGATIVES
---------------- ----------------
            17,9            -12,7


PostgreSQL - Best way to incremental export 1000+ tables every 5 mins I have 6 PostgreSQL database servers(v11) hosted on some third-party vendor. I don't have access to setup pg_logical. Each server has 1 database but 1000+ tables.
So I want to get the data from these 6 servers to my central PostgreSQL database. The sources tables are having PK and last_updated_timestamp columns.
Im just trying to find the best approach to get the data at every 5 or 15mins interval from these tables to my central database server.
My goal is to sync the whole database to Report database server with every5mins interval.

In the central database, define foreign tables using postgres_fdw that point to the other databases. For each table, remember the timestamp from the last time you fetched data. Then regularly run
INSERT INTO localtable
SELECT * FROM foreigntable
WHERE last_upd_ts > ...
ON CONFLICT ON (...)
   DO UPDATE ...


Update rows in table with set-returning function I am trying to update a table of 100 already existing rows with 100 random values from a Gaussian distribution, for which I am using the normal_rand function from the tablefunc extension. Since this is a set-returning function I placed it it the FROM-clause.

UPDATE foo
SET bar = r
FROM normal_rand(100, 0, 1) AS r;

However, when executing the statement, all rows in the table are assigned the same value (presumably the first), as such:

bar
-------
0.2451
0.2451
0.2451
...
0.2451

I also tried generate_series, but this yields similar results. How can I assign the first value of normal_rand(100, 0, 1) to the first row, the second to the second etc.?

This is not going to be fast, but you need a subquery that assigns row numbers for each row that can be used for joining the generated values with the table. You can use with ordinality to generate such number for the result of a set-returning function.

with numbered as (
  select id,
         row_number() over (order by id) as rn
  from foo
)
update foo
  set r = t.val
from (
  select n.id,
         nr.val
  from numbered n
    join normal_rand(1000, 5, 3) with ordinality nr(val,idx) on n.rn = nr.idx
) t
where t.id = foo.id

Replace id with the primary key column of your table.

Online example


Is it possible to use one certificate with aliases for MongoDB cluster? I currently configure a MongoDB cluster to use TLS certificate.

It could be far more easier to deploy, one certificate with aliases accross all cluster, instead of one certificate per node. But there is no mention of this in documentation, is it possible ?

Actually, yes :
https://docs.mongodb.com/manual/core/security-internal-authentication/#x-509

  Either the Common Name (CN) or one of the Subject Alternative Name
  (SAN) entries must match the hostname of the server, used by the other
  members of the cluster.

  For example, the certificates for a cluster could have the following
  subjects: subject= CN=,OU=Dept1,O=MongoDB,ST=NY,C=US
  subject= CN=,OU=Dept1,O=MongoDB,ST=NY,C=US subject=
  CN=,OU=Dept1,O=MongoDB,ST=NY,C=US


What are the possible causes for sp_reset_connection taking a long time to execute? Why would the sp_reset_connection system stored procedure be taking longer than a few milliseconds to execute, as viewed through SQL Server Profiler?

I took a simple trace from a production system using SQL Server Profiler and then used SqlNexus to analyze it. SqlNexus indicates that sp_reset_connection has the highest cumulative duration -- 33% of the overall trace. The observed duration ranges from 0-7 seconds (12 to 6,833,270 microseconds) but averages at 0.956s.

I understand that sp_reset_connection is being called when a pooled connection gets reused. I have seen a suggestion that this can be happening due to extraneous traces, but that doesn't seem to be the case.

I have read what the server is doing when the sproc is called but I don't believe any of those would be problematic in this case -- the code is not leaving open transaction or huge temporary tables that would need to be cleaned up.

I also looked at https://serverfault.com/questions/199974/sp-reset-connection-taking-a-long-time-to-run but it wasn't helpful.

EDIT (2013-12-23):
In all cases, reads and writes are 0 and CPU is almost always 0 (only two instances of non-zero CPU, both at 16ms).

Finally got some time to write a more detailed answer.

There are typically three main reasons a simple procedure like sp_reset_connection will take a long time to run.

You are waiting for CPU resources
You are blocked on a lock somewhere (perhaps as a result of DML or a competing transaction)
Your network is slow and it takes a long time to return the result back to the client

Ad 1) If you are waiting for CPU resources, this should show up as signal waits. Please see my comment on your question on how to diagnose if this is the issue

Ad 2) If you are waiting for a lock, this is best diagnosed by comparing two snapshots of sys.dm_os_wait_stats. See this article on how to do this:

http://www.sqlsoldier.com/wp/sqlserver/sqludbaweekbeanefficientdba

If you see long waits for LCK_[Something], query sys.dm_tran_locks to track down which objects are being locked. In your case, I would expect to see some form of SCH-[Something]> locks blocking you.

Ad 3) The easiest way to diagnose network issues to first look for OLEDB and ASYNC_NETWORK_IO waits in step 2 (if you wait a long time for network, one of those show up). If those waits are high, use xperf -on latency or a network monitoring program like netmon or wireshark to check your latencies. If the network looks slow, this could also be caused by the calling application server not responding fast enough to the connection being recycled.


How do I cleanup Postgres WAL? What is the correct way to cleanup WAL postgres? I have a database with more than 100 GB and it haves around 600 GB in pg_wal. Also I have 2 logical replications set up.

Master and Slaves - Postgres 10.

Master and Slaves have commented wal_keep_segments and max_wal_size.

pg_archivecleanup did not work with %r option, then I got the archive name by searching in pg_controldata latest checkpoint's REDO WAL file and deleted the logs, but then one replica stopped with the error

  could not receive data from WAL stream: FATAL: requested WAL segment xxx has already been removed

To solve this I deleted and recreated the replica.

"pg_wal" cleans itself up. You should almost never touch pg_wal by hand. If it is not cleaning itself up, you need to figure out why and fix the underlying issue.
One possible reason is that you have a replication slot which is holding it back.  Either a replica is using a slot and is unable to keep up. Or you have a slot which has no replica attached, for example you destroyed the replica but didn't drop the slop it used to occupy.  You can see what slots you have by querying pg_replication_slots, and if necessary drop one with pg_drop_replication_slot, both run on the master.  You would look for the slot with the oldest non-NULL value of "restart_lsn".
Another reason is that you have "archive_mode" turned on, but your "archive_command" is constantly failing or can't keep up.  You will see warnings about this in your server log file if it is failing.
"pg_archivecleanup" is used to clean up a WAL archive.  "pg_wal" is not the archive, it is the live WAL files. You are lucky you didn't destroy your database by monkeying around in there.


Reindexing / rebuild Indexes on Linked Server tables I am trying to run the below query to rebuild the index on a table on the Linked server, but it is not working. Can any one please let me know what I am doing wrong?

ALTER INDEX ALL ON [LinkedServerName].[dbname].[dbo].[tablename] REBUILD
WITH (FILLFACTOR = 90)

This is giving me below error:

  Cannot find the object "[LinkedServerName].[dbname].[dbo].[tablename]" because it does not exist or you do not have permissions.

Four-part-naming syntax is not supported for ALTER INDEX.

Try this:

EXEC ('ALTER INDEX ALL ON [dbname].[dbo].[tablename] REBUILD WITH (FILLFACTOR = 90);')
AT LinkedServerName;


Materialized view for the last 24 hour period I am collecting spatial point data that has an associated timestamp with it. I want to create a view of this data for the last 24 hour period. Because I am piping this spatial data through ArcGIS server, there are performance problems related to just piping in a straight view (this view can contain easily 100-200k+ points). To make a long story short, to mitigate these performance issues, I have created materialized views of the data. However, I want this data to be updated every 3 - 4 minutes.

My question is, when I add the current_timestamp to the query:

SELECT *
FROM points
WHERE timestamp > current_timestamp - interval '24' hour

...this no longer can be used as a fast refresh. Is there any way to correct that, or will it always be a complete refresh? If shown, what should I be mindful of when it comes to the performance of updating the materialized view (again, this dataset can easily be 100-200k points with 3 - 6 points added every second).

A fast-refreshable materialized view cannot contain a non-deterministic function like current_timestamp.  So if you want to materialize the data from the last 24 hours in a materialized view, the materialized view would need to do a complete refresh every time.

Do you need a materialized view?  Could you maintain your own staging table and create a custom job that runs every few minutes, deletes the data that is now more than 24 hours old, and inserts the new data (either by directly querying the table or creating a trigger that writes the new data to a different table)?  That's likely a non-starter if you need to use the query rewrite functionality of a materialized view but if you just want the benefit of having a smaller table to query, custom code may be more efficient.

Or could you partition the table by day so that queries for the past 24 hours always just have to hit the two most recent partitions?


Data Warehouse indexing approach - to verify Could you take a look at my approach to the Data Warehouse indexing (SQL Server 2014 SP1 Ent) below and tell me is it correct or should I change it ?
(our Data Warehouse most of the time is used for SSAS Cube and sometimes for direct reports)

My indexing approach:

All primary key's (in a Dimension tables) have a clustered Index
For bigger Dimension tables (50k+ of rows) I have a dedicated non-clustered index per frequently use column
All Fact tables have a PK clustered index implemented (on single non FK column like FactTableKey) – but I red this shouldn’t be placed there
All foreign key’s (in a Fact tables) have a dedicated non-clustered index (per one FK column in it)
In addition I’m considering the implementation of a CLUSTERED COLUMNSTORE INDEX on a Fact tables instead of many non-clustered indexes for every FK’s -  but in this case I’ve red that this is a not recommended solution

In addition – on my test lab with Visual Studio SSDT – I tried to put a CCI index on a Fact table which belongs to a FILEGROUP but I get an error with information that I cannot use CCI on a table which belongs to a FILEGROUP. What’s strange I can create such CCI from SSMS directly. Visual Stusio issue or SSMS bug ?

You mention that you added single-column indexes for each foreign key in your fact table. Often at least some of the foreign keys have low cardinality so they are likely not useful in an index on their own.
https://www.ibm.com/developerworks/data/library/techarticle/dm-1309cardinal/

They may be more useful as part of a multi-column index which you can design based on the way that you expect users to query the table.

If your workload suits it, then non-clustered columnstore index should be considered on large dimension tables and fact tables. They are ideally suited for data warehouse workloads. http://www.nikoport.com/2016/03/07/columnstore-indexes-part-79-loading-data-into-non-updatable-nonclustered-columnstore/

Since you are using 2014 then non-clustered is the only option if you want to keep constraints and other indexes.


What is the rationale for requiring a column list when inserting into a table with identity column? When inserting into a table with an identity column from some similar staging table, we have to set IDENTITY_INSERT to ON and specify a column list.

Why do we have to specify a column list in this case?

Wouldn't it be better to specify a change request at Microsoft Connect?

If you don't specify the column list then this implicitly assumes a column list including all user insert-able columns (non identity, rowversion, computed) as below.

CREATE TABLE #T
(
C INT IDENTITY,
D INT,
E INT
)

INSERT INTO #T
VALUES (2,3)

This is far more generally useful as the most common reason for having an IDENTITY column is to allow SQL Server to manage the values.

I suppose it could imply that if there was an extra column in the insert source on an identity table that it should generate an execution plan for the explicit identity_insert case but not much benefit to this IMO.


Should a Fact table contain a column with a single unique value? In order to simplify reporting, I am wondering if for each of the Fact tables we have, whether I can include a column that states the name of the Fact table, albeit in a more user friendly format

e.g.

FACT_SALES
Source     ID      Name
Sales      1       Bob
Sales      2       Terry
....

FACT_TESTING
Source              ID      Type
Product Testing     23      Adhoc
Product_Testing     29      ...
...

If it should not, then what is an appropriate way of adding this information without requiring any action by the end user in terms of them having to add some sort of coding to show this?

What I recommend is that you have a fact_source table:

CREATE TABLE fact_source
(
  fact_source_id INTEGER,  -- auto-increment.
  fact_source_name VARCHAR (50)
);

Use your own RDBMS's (you didn't say which) own auto incrementing key system to generate the fact_source_id)

INSERT INTO fact_source fact_source_name) VALUES ('Sales'), ('Testing');

and use that as a FOREIGN KEY in the relevant tables.

You can then "pre-cook" your user-friendly (i.e. text) output in report queries if your testing shows that it works nicely and isn't a resource burden.

You should not use ENUMs for the reasons outlined here. Just because systems have features does not mean that you have to use them! You should always use either CHECK constraints (Unbelievably, MySQL doesn't have these) or a lookup table as described above.

Storing your key as a SMALLINT (or similar) will save a bit of space!


Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition? Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire set of conditions, does that mean that the last condition evaluating to true overwrites what the first condition that evaluated to true did? For example:

SELECT
    CASE
        WHEN 1+1 = 2 THEN'YES'
        WHEN 1+1 = 3 THEN 'NO'
        WHEN 1+1 = 2 THEN 'NO'
    END

The results is "YES" even though the last when condition should make it evaluate to "NO". It seems that it exits once it finds the first TRUE condition. Can someone please confirm if this is the case.

•Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.
Reference
https://docs.microsoft.com/sql/t-sql/language-elements/case-transact-sql

This is standard SQL behaviour:

A CASE expression evaluates to the first true condition.

If there is no true condition, it evaluates to the ELSE part.

If there is no true condition and no ELSE part, it evaluates to NULL.


MySQL table architecture Background information:

I have a table containing upload information, every day I get more and more uploads but most of the queries I run center around information gathered within the last 12 months; frequently it uses an even tighter time scale and queries are restricted to information gathered in the last 30 days

This system has been in existence from 2004 when there were 400 uploads, today there are 2+ million

Table structure:

CREATE TABLE `data_mediagallery` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `contenttype` char(40) NOT NULL DEFAULT '',
  `filename` varchar(100) NOT NULL DEFAULT '',
  `injector` char(40) NOT NULL DEFAULT '',
  `hits` int(11) DEFAULT '0',
  `message` longtext,
  `date` datetime DEFAULT NULL,
  `title` varchar(80) NOT NULL DEFAULT '',
  `tags` varchar(255) NOT NULL DEFAULT '',
  `metadata` blob,
  `location` char(8) NOT NULL DEFAULT '',
  `uid` int(11) unsigned NOT NULL DEFAULT '0',
  `filesize` bigint(20) NOT NULL DEFAULT '0',
  `upload` datetime DEFAULT NULL,
  `privacy` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `width` int(10) unsigned NOT NULL DEFAULT '0',
  `height` int(10) unsigned NOT NULL DEFAULT '0',
  `offensive` int(10) unsigned NOT NULL DEFAULT '0',
  `sourcelocation` char(8) NOT NULL DEFAULT '',
  `autoblog` tinyint(1) NOT NULL DEFAULT '0',
  `extension` char(10) NOT NULL DEFAULT '',
  `filetype` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `conversiontime` float NOT NULL DEFAULT '0',
  `converttime` datetime DEFAULT NULL,
  `sender` varchar(100) NOT NULL DEFAULT '',
  `vhost` int(10) unsigned NOT NULL DEFAULT '0',
  `channel` int(10) unsigned NOT NULL DEFAULT '0',
  `rotation` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `ofilesize` bigint(20) NOT NULL DEFAULT '0',
  `moderationstatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rating` decimal(8,6) DEFAULT NULL,
  `votecount` int(10) unsigned NOT NULL DEFAULT '0',
  `url` varchar(150) NOT NULL DEFAULT '',
  `geo_latitude` double DEFAULT NULL,
  `geo_longitude` double DEFAULT NULL,
  `length` decimal(8,2) DEFAULT '0.00',
  `parentid` int(11) NOT NULL DEFAULT '0',
  `language` char(2) NOT NULL DEFAULT '',
  `author` varchar(100) NOT NULL DEFAULT '',
  `context` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `externalid` varchar(255) DEFAULT NULL,
  `originalsaved` bit(1) NOT NULL DEFAULT b'1',
  `hidden` tinyint(4) NOT NULL DEFAULT '0',
  `commentcount` int(11) NOT NULL DEFAULT '0',
  `approvedcomments` int(11) NOT NULL DEFAULT '0',
  `notdeniedcomments` int(11) NOT NULL DEFAULT '0',
  `lastupdatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `channelleft` int(10) unsigned NOT NULL DEFAULT '0',
  `originalLocation` char(8) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `upload` (`upload`),
  KEY `vhostupload` (`vhost`,`upload`),
  KEY `vhostmodstatus` (`vhost`,`status`,`moderationstatus`,`uid`),
  KEY `complexfiletype` (`vhost`,`status`,`moderationstatus`,`filetype`,`channel`),
  KEY `vhostcontext` (`vhost`,`moderationstatus`,`context`,`parentid`,`status`,`filetype`),
  KEY `externalid` (`externalid`),
  KEY `externalcomments`.
  KEY `vhostchannel` (`vhost`,`status`,`moderationstatus`,`context`,`channelleft`)
) ENGINE=InnoDB;

Questions

Is there a way to partition the table that would make the most sense?
Does partitioning even make sense?
How do I deal with new data if I do partition?

Why do you want to partition? Is you performance not good, or do you have other reasons?

I don't see an index for the lastupdatetime column, and you wrote that you query for it a lot.

An alternative to partitioning could be creating tables for each year, and use merge tables to access the subsets you need.

Would have just left a comment, alas, not enough reputation.


Is it possible for master, tempdb, model and msdb to have a database_id other than 1,2,3,4 respectively? SQL Server System Databases, as far as I know, always have those same IDs and I have seen lots of maintenance scripts on the internet relying on the predicate WHERE database_id > 4 to exclude them from the actions of the script.

Also, if I run SELECT name, schema_id FROM sys.schemas; on a new user database, I get:

name                 schema_id
dbo                  1
guest                2
INFORMATION_SCHEMA   3
sys                  4
db_owner             16384
db_accessadmin       16385
db_securityadmin     16386
db_ddladmin          16387
db_backupoperator    16389
db_datareader        16390
db_datawriter        16391
db_denydatareader    16392
db_denydatawriter    16393

I ran that query on two different instances, one a SQL Server 2016 and the other a SQL Server 2005, and both returned that same result.

Questions:

Is there any situation (or sql server version) in which the system databases master, tempdb,
model and msdb would have a database_id other than 1,2,3,4
respectively?
Can I really trust on the fact that the schemas I listed will always have the same IDs on any instance of SQL Server so that I can write maintenance scripts based on those IDs?

In theory

Below actions are only for testing purposes. Do not attempt any of this if you are not ok with your database instance becoming unusuable, unresponsive and/or your data being lost

Detaching the master database to try and reuse it's database_id is not possible.

Detaching model  will result in the instance crashing and you not able to attach it back. This results in you having to Rebuild all your system databases. This process removes all information such as logins, jobs, .... And your user databases are detached.

Detaching Tempdb to try and reuse it's database_id's by attaching it back under a different name resulted in the database_id 2 not being reused & tempdb not working.

name    database_id
master  1
model   3
msdb    4
test3_2__   5
test3_2_    6
tempdb  7

I was able to change the name back to tempdb, but the instance was pretty messed up, and I was unable to start it normally.

  Could not find database ID 2. Database may not be activated yet or
  may be in transition. Reissue the query once the database is
  available.

---> Rebuild all your system databases.

But, in theory there are 2 (or more) options to change the msdb system database id:

You can change the msdb database id by detaching msdb, creating a new user database and finally creating a new msdb database while following the documented steps to create a new msdb database.

You can change the msdb database id by detaching msdb, reattaching it under a different name and changing the name back to msdb.
UNSUPPORTED, please do not do this

First start sql server in 'master only recovery mode'

NET START MSSQL$InstanceName /f /T3608"

Detach msdb and create a new database

EXEC sp_detach_db msdb;
GO
CREATE DATABASE test3;
GO

Change the names of msdb files, Restart the instance normally

Initialize msdb:

SQLCMD -E -S<servername> -i"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.out"£

View the database id's:

SELECT name,database_id
FROM sys.Databases;

name    database_id
master  1
tempdb  2
model   3
test3   4
...
msdb    12

Unsupported method

The 2 normal attach procedures
sp_attach_db and create database ... for attach do not work when trying to attach msdb.

Error message:

  FILESTREAM database options cannot be set on system databases such as
  'msdb

What you can do, solely for testing purposes is create a user database, msdb2

CREATE DATABASE msdb2
    ON (FILENAME = N'X:\MSSQL\DATA\MSDBData.mdf'),
    (FILENAME = N'X:\MSSQL\DATA\MSDBLog.ldf')
    FOR ATTACH;

Rename the database to msdb

ALTER DATABASE msdb2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE msdb2 MODIFY NAME = msdb ;
GO
ALTER DATABASE msdb SET MULTI_USER

Restart the instance normally

NET STOP MSSQL$InstanceName
NET START MSSQL$InstanceName

and view the database_id's:

SELECT name, database_id
FROM sys.databases;

Result

name    database_id
master  1
tempdb  2
model   3
test    4
...
msdb    10

In practice

I have fortunately not seen this anywhere, but database_id 4 could in theory be reused by user databases. If this happened filtering out system databases in my queries would probably be the least of my problems.

That said, using WHERE DB_NAME(database_id) NOT IN ('master','tempdb','model','msdb','distribution','ssisdb') is the sure choice.


What's the point of SQL joins if you have OLAP? I want to understand the purpose of using SQL joins from a business analysis perspective.

Let's say I want to analyze customer data. Why would I want to pull data from multiple tables using join statements if I can use a cube or tabular model, which seem omnipresent, even excel has this functionality.  I can use point and click adhoc, slicers and other filtering and aggregation which if I understand correctly are a lot faster and if I want to do a very complex analys, I can write MDX queries or cubeformulas, in the excel example.

What can SQL joins can help me with?

You're basically talking about two entirely different technologies, used for two different purposes.

A relational database stores information, commonly in a normalized fashion which is most suited for day-to-day processing and storage of business information. Given this storage model, you need joins to.. well, join those relational tables together when you want to compile data and properties from those different sources.

OLAP cubes are not really used to store information, but rather duplicate and re-organize the information in a denormalized way so as to allow for typical analytical queries, using MDX for instance. What makes OLAP cubes so fast is that a lot of aggregates are pre-calculated, and this comes at a potentially great cost in storage.

Excel is not a realiable OLAP tool, imho - it can act as an interface to cubes, but if you try to use PowerPivot with any larger set of data, you'll very quickly find your workstation out of memory.

On a final note, I wouldn't say that cubes and tabular models are "omnipresent". I would say that they can often be found for specific end-user applications, and as such they are often stripped down to a minimum of dimensions, attributes and measures in order to avoid confusing the users. Power-users will often go directly to the relational database to get what they need.


NVARCHAR column as PRIMARY KEY or as UNIQUE column I'm developing a SQL Server 2012 database and I have a doubt about nvarchar columns as primary keys.

I have this table:

CREATE TABLE [dbo].[CODES]
(
    [ID_CODE] [bigint] IDENTITY(1,1) NOT NULL,
    [CODE_LEVEL] [tinyint] NOT NULL,
    [CODE] [nvarchar](20) NOT NULL,
    [FLAG] [tinyint] NOT NULL,
    [IS_TRANSMITTED] [bit] NOT NULL DEFAULT 0,
     CONSTRAINT [PK_CODES] PRIMARY KEY CLUSTERED
    (
        [CODE_LEVEL] ASC,
        [CODE] ASC
    )
)

But now I want to use [CODE] column as primary key and remove [ID_CODE] column.

Is there any issue or penalty if I have a NVARCHAR column as PRIMARY KEY?

[CODE] column value must be unique, so I've thought that I can set an UNIQUE constraint to that column.

Do I have to use [CODE] as primary key or it is better if I set an UNIQUE constraint on [CODE] column?

Yes, absolutely there are negative consequences for using a string instead of a numeric type for a Primary Key, and even more so if that PK is Clustered (which it indeed is in your case). However, the degree to which you see the effect(s) of using a string field is a function of a) how many rows are in this table, and b) how many rows in other tables are Foreign Keyed to this PK. If you only have 10k rows in this table and 100k rows in a few other tables that FK to this table via that field, then maybe it won't be so noticeable. But those effects certainly become more noticeable as row counts increase.

You need to consider that the fields in a Clustered Index are carried over to Non-Clustered Indexes. So you aren't just looking at up to 40 bytes per row, but (40 * some_number) bytes. And in any FK tables you have those same 40 bytes in the row plus more often than not there will be a Non-Clustered index on that field as it is being used in JOINs, so now it is really doubled in any tables that FK to this one. If one is inclined to think that 40 bytes * 1 million rows * 10 copies of it is nothing to be concerned about, please see my article Disk Is Cheap! ORLY? which details all (or at least most) of the areas impacted by this decision.

The other thing to consider is that filtering and sorting on strings, especially when not using a binary Collation (I assume you are using the database default which is typically case-insensitive) is far less efficient (i.e. takes longer) than when using INT / BIGINT. This impacts all queries that filter / join / sort on this field.

Hence, using something like CHAR(5) would probably be OK for a Clustered PK, but mostly if it was also defined with COLLATE Latin1_General_100_BIN2 (or something like that).

And can the value of [CODE] ever change? If yes then that is even more reason to not use it as a PK (even if you do set the FKs to ON UPDATE CASCADE). If it can't or won't ever change that is fine, but still there is more than enough reason already to not use it as a Clustered PK.

Of course, the question might be incorrectly phrased as it appears that you currently already have this field in your PK.

Regardless, your best option, by far, is to use [ID_CODE] as the Clustered PK, use that field in related tables as the FK, and keep [CODE] as a UNIQUE INDEX (which means it is an "alternate key").

Update
A little more info based on this question in a comment on this answer:

  Is [ID_CODE], as PRIMARY KEY, the best option if I use [CODE] column to look up the table?

This all depends on a great many factors, some of which I have already mentioned but will restate:

A Primary Key is how the individual row is identified, whether or not it is referenced by any Foreign Keys. How your system internally identifies the row is related to, but not necessarily the same as, how your users identify themselves / that row. Any NOT NULL column with unique data could work, but there are practicality issues to consider, especially if the PK is, in fact, referenced by any FKs. For example GUIDs are unique and some people really like using them for various reasons, but they are quite bad for Clustered Indexes (NEWSEQUENTIALID is better, but not perfect). On the other hand, GUIDs are just fine as alternate keys and used by the app to look up the row, but the JOINs are still done using an INT (or similar) PK.

So far you haven't told us how the [CODE] field fits into the system from all angles, outside of now mentioning that this is how you look up rows, but is that for all queries or just some? Hence:

Regarding the [CODE] value:

How is it generated?
Is it incremental or psuedo-random?
Is it uniform length or varying length?
What characters are used?
If using alphabetical characters: is it case-sensitive or insensitive?
Can it ever change after being inserted?

Regarding this table:

Do any other tables FK to this table? Or are these fields ([CODE] or [ID_CODE]) used in other tables, even if not explicitly Foreign Keyed?
If [CODE] is the only field used to get individual rows, then what purpose does the [ID_CODE] field serve? If it isn't used, why have it in the first place (which might depend on the answer to "Can the [CODE] field ever change?")?
How many rows in this table?
If other tables to reference this table, how many and how many rows in each of them?
What are the indexes for this table?

This decision can't be made purely on the question of "NVARCHAR yes or no?". I again will say that generally speaking I do not find it to be a good idea, but there are certainly times when it is fine. Given so few fields in this table it is not likely that there are any more, or at least not many, indexes. So you might be fine either way to have [CODE] as the Clustered Index. And if no other tables reference this table then you might also be fine making it the PK. But, if other tables do reference this table then I would opt for the [ID_CODE] field as the PK, even if Non-Clustered.


Graceful Test for Linked Server Availability Inside a TVF or View I’ve got a mission critical table valued function that needs to return results from several linked servers simultaneously. If any of those servers are down, it still needs to return results from the other servers. Because it’s function a Try/Catch block is not allowed. Dynamic SQL is not permitted inside functions either.
sys.sp_testlinkedserver seems like the obvious solution. It returns an integer when the linked server is online. However, it throws an error when there’s a problem with he linked server, so it’s useless.
This TVF will replace a similarly unreliable view based on the following query:
SELECT Col_1, Col_2, Col_3, Col_4 FROM Server_A.Some_DB.TheTable
UNION
SELECT Col_1, Col_2, Col_3, Col_4 FROM Server_B.Some_DB.TheTable
UNION
SELECT Col_1, Col_2, Col_3, Col_4 FROM Server_C.Some_DB.TheTable

Again, if any of the servers are down, the query throws an error. This is why I thought that a TVF would be useful so that I can test a server before doing anything that could cause an error.
Are there any graceful ways to test the availability of a linked server that don’t throw errors when the linked server is down?

The solution is to create a stored procedure on the local server that includes error handling for queries to remote servers and returns a single dataset. Then to create a view that calls the stored procedure using OPENROWSET.
The stored procedure has to use a table variable for holding the data it gets from each server. It can’t use a UNION between them because that would defeat the purpose of error trapping.
Here is an example of the stored procedure. In this example, I’m querying sys.Databases because that’s a view that everyone has and can easily test:

USE MyDatabase
GO
CREATE PROCEDURE TEST_PROC
AS
    SET FMTONLY OFF
    SET NOCOUNT ON

    DECLARE @OUTPUT TABLE (
        Server_Name NVARCHAR(100),
        Name        NVARCHAR(128),
        Database_ID INTEGER,
        State       INTEGER,
        State_Desc  NVARCHAR(60) )

    BEGIN TRY
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc)
        SELECT 'SERVER_A',  Name, Database_ID, State, State_Desc
        FROM SERVER_A.Master.sys.Databases
    END TRY
    BEGIN CATCH
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc)
        SELECT 'SERVER_A',  'Server is Unavailable', Null, NULL, ''
    END CATCH

    BEGIN TRY
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc)
        SELECT 'SERVER_B',  Name, Database_ID, State, State_Desc
        FROM SERVER_B.Master.sys.Databases
    END TRY
    BEGIN CATCH
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc)
        SELECT 'SERVER_B',  'Server is Unavailable', Null, NULL, ''
    END CATCH

    BEGIN TRY
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc)
        SELECT 'SERVER_C',  Name, Database_ID, State, State_Desc
        FROM SERVER_C.Master.sys.Databases
    END TRY
    BEGIN CATCH
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc)
        SELECT 'SERVER_C',  'Server is Unavailable', Null, NULL, ''
    END CATCH
GO

You will have to create a TRY/CATCH block for every server you want to query. With a little creativity, I’m guessing that it’s possible to do the above using a cursor loop based on sys.servers to generate and execute the above TRY/CATCH blocks for all linked servers.
Here is the associated view:

CREATE VIEW TEST_VIEW
AS
    SELECT SELECT Server_Name, Name, Database_ID, State, State_Desc
    FROM OPENROWSET('SQLNCLI11', 'Server=.;Trusted_Connection=yes;', 'EXEC MyDatabase.dbo.Test_Proc')
GO

Note that “Server=.;” in the connection string points to the local server. My understanding of SQL data sources is a little shaky, so this may not work for everyone. Also, you may have to update the rest of the connection string to match the needs of your environment.
The only downside I’ve encountered in testing is that if one of the servers is offline, it’s got about a 21 second timeout. So it takes at least that long to return. When all servers are up, it runs in a fraction of a second.  I’ve tried changing the timeout parameters in the linked servers definitions, but they don’t appear to make a difference. I’ll experiment more and update this solution if I find better results.
Thanks J.D. I wouldn’t have known to go this route without your suggestions.
-Robbie


Slow performance of remote restore Over the weekend, I was participating in a DR test. On the database side, that meant restoring backups from the production system to the DR system. What struck me as odd about the performance of the restores was that it seemed to read the backup file quickly enough (i.e. would report 100%), but would then spend a lot of time before actually reporting the restore complete (i.e. x pages restored to file n, y pages restored to log file). Once I copied the backup files to be local to the DR system, everything went quickly. What explains this behavior?

When a database backup is restored it almost immediately allocates the space needed for the database files.  So a 300 GB database will quickly appear in the file system at full size, but the file will be essentially empty to start with.

Once the space is allocated, the restore process goes on to read the backup file restoring data at the speed sustainable by the network and the servers. Once the backup is complete, restoring all the backup files, handling roll backs and roll forwards, then the database will be available.

A few years ago we had a very big database with the backup files stores remotely that was going to take 12 hours to restore over the network.  So, in that case we cancelled the restore.  Then we copied the files locally (about 1 hour) and restored quite quickly after that.

Note that if you database backups are compressed, the backup file will restore faster since there is less network traffic.


A simple question regarding database design that has always bothered me Say I have a table of schools, and each school is either Public, Private, or For-Profit. As well, each school is either '2-4 Year,' '4+ Year', or 'Graduate'. My initial instinct in this situation is to create another table that holds attributes, then create a junction (many-to-many) table to map a school to its attributes. This seems simple enough, but every time I am faced with this situation my stomach begins to turn and the following two questions always arise:

Is it worth it? What reason really is there not to just store the text (Public, Private, For-Profit) in a column in the school table?
The other issue is in naming. Say the first table is named school, the attribute table is named school_attribute (assume there are also attributes that apply to other things, e.g., companies), then I am stuck naming the junction table school_school_attribute, and that just sounds terrible. The naming problem often leads me to not normalize my table, even though I know that's the wrong thing to do. However, I'd almost rather be stuck with a database that is not 3NF than redundant naming in my tables.

So, first I'd like to know if it is even worth it, and for what reason (e.g., it saves space); second, I'd like to know how people name their tables to avoid redundancy.

EDIT

Another option would be to create a table called attribute with a column named type. The type column would tell me what type of attribute it is (e.g., school, company, etc). Wait, no reason to store the text in the column, let me create a table called attribute_type, and now a junction table named attribute_attribute_type... Aggg! This is my biggest block in database design for some reason.

There really is a whole raft of pros to normalizing your database. If the main issue you're having is naming, then you're in pretty good shape. Apart from space which you've already mentioned:

The width of your table will be smaller, this will increase the speed of your selects.
Updates and inserts will be quicker because you're inserting/updating integers instead of text
Maintaining your data will be easier ie if there was a new type of school which needed to be added, you should just be able to add it to the table.
You can create hierarchies in your lookup tables
You don't need to reply on your application layer to enforce referential integrity.
You can manage changes to your lookup options over time (ie what is "Public" is no longer applicable after July 1st but older records created before this date must still show this as an option?)
Loading data into a data warehouse is simplified
It makes it easier to other DBAs to understand your schema

Normalization is usually going to have a level of subjectivity. You database schema should reflect a balance between maintainability, complexity and performance. On the flip side, if you're only going to have 1 or 2 tables and a small amount of records, maybe normalization is overkill? I would tend to normalize anyway, because if the database needs to scale up, you're already in a good position to do so.

As for naming, have a think about the most sensible name that will describe the table's contents without it sounding crazy like the example you gave above. Otherwise you'll end up with a schema which is hard to understand.

Hope this helps.


Define relationships for 3 tables I have 3 tables that are currently defined as:

Is this the best way, or should I use an associative table?

I put the relationships backwards, 1 group can contain many users and many students.  There is no other relationship between the user and the student other than the group.  This is for a tracking program where the user or users are taking a student or group of students through a check-in procedure.

Based on your description you want to have a GroupMembers table that does all the many to many joins for you.

Your current schema only allows a Group to contain one User and one Student as you have made the GroupName the Primary Key.  You really want to break the Group out to its own table in case the group name changes.

This can allow you to have:

A Group with many Users and many Students
Users can be members of many Groups
Students can be members of many Group
A User should not be duplicated in a Group
A Student should not be duplicated in a Group

Thats achieved with a schema like this:

Because you only ever want one combination of a given group, user and student you can make the foreign key columns the primary key too.

This all follows Third Normal Form (at least).


Selecting and Inserting into linked server I am having a situation with linked server which I am not able to understand.

So we have a linked server from a 2008R2 server to a 2014 server.
The below sample query is executing from 2008R2 server and it works fine.

SET XACT_ABORT ON;
 Declare @BatchSize int = 10
      DELETE from LINKEDSRV.DB.DBO.Table
      INSERT INTO LINKEDSRV.DB.DBO.Table (ECN)
      SELECT TOP (10) C1 from LINKEDSRV.DB.DBO.Table22 --order by C1
      SELECT * FROM LINKEDSRV.DB.DBO.Table

But when I execute the same thing with the order by C1 it does not return any results.

Second case - If I replace  the TOP(10) with TOP(@BatchSize) and no order by also I dont get any results out. eg

SELECT TOP (@BatchSize) C1 from LINKEDSRV.DB.DBO.Table22

All of the scenarios work if I SET XACT_ABORT OFF.  So does XACT_ABORT has any kind of restriction when it comes to linked server?

Edit - Did some more testing and looks like it has to do something to do with the number of rows also

Possible Repo

On ServerA

use testdb
go
create table t1( c1 int, c2 datetime)
create table t2( c1 int, c2 datetime)

insert into t2 select 1, GETDATE()
insert into t2 select  * from t2 -- insert close to 5000 rows, I found the issue around over around 35000 rows

On ServerB

Create linked server to ServerA

SET XACT_ABORT ON;
Declare @BatchSize int = 10
delete from ServerA.testdb.dbo.t1
insert into ServerA.testdb.dbo.t1 (c1)
select top (@BatchSize) c1 from ServerA.testdb.dbo.t2 --order by c2
select * from ServerA.testdb.dbo.t1

No output.  But if you reduce the number of rows in table t2 to say around 2000, it works fine.

All of the scenarios work if I SET XACT_ABORT OFF. So does XACT_ABORT has any kind of restriction when it comes to linked server?

The setting of XACT_ABORT is propagated to the remote SQL Server, as documented in Handling Errors in Server-to-Server Remote Stored Procedures.

The setting also affects if updates are possible, and how they are handled, as documented in Distributed Queries and Distributed Transactions. Your INSERT is allowed with XACT_ABORT OFF because SQL Server supports nested transactions.

Nevertheless, there appears to be an implementation bug, because tracing the activity on the 2014 server during the insert reveals that an error occurs when SQL Server attempts to release a schema lock:

This error aborts the statement (attempting to release the schema lock) on the remote server, but when XACT_ABORT is OFF, the remote server continues processing the next statement. The insert completes, despite the schema lock release error.

When XACT_ABORT is ON, the whole remote batch is aborted, so the insert is rolled back.

I was able to reproduce your problem locally, but the ORDER BY clause was not significant.

You can avoid the problem in several ways, including wrapping the INSERT in an explicit transaction (assuming DTC is available to you).

I recommend you avoid four-part name syntax for remote changes, since the implementation is based on a cursor model. You will normally get better performance using a bulk method, or by pulling the data at the remote server (rather than pushing it from the local server).

See the related Q & A Which one is more efficient: select from linked server or insert into linked server?


How is data lake and polyglot persistence different? I just came across a new technology called polyglot persistence. However, it sounds very much like a data lake. So, how are these two things different?

The term "polyglot" (meaning "speaking or using several different languages") was first used by Neal Ford* with respect to ICT in 2006 in a blog post in which he coined the term "polyglot programming" where he stressed that nowadays, it's not good enough to be an expert in (for example) C and C only - a competent software engineer must be able to move between significantly > 1 langauge(s).
Interestingly, he also mentions the JVM (and/or .NET) as kinds of universal machines (which they sort of are) and where one would use different JVM languages for different tasks and then combine the bytecode into one executable - we see this now for example (an interest of mine) in Clojure and Java - you have the functional aspects of Clojure which you can combine with the massive Java ecosphere (think of the huge number of libraries for example) to get the best of both worlds. Scala and Kotlin are other examples of where this can also be achieved.
This concept of platform diversity was extended by Martin Fowler to databases in approx. 2011 when he coined (in what he calls his "bliki" - blog/wiki) the term "polyglot persistence". His point was that different functionalities within a system can be best served by different databases - as per this image:

With the current move away from software monoliths and the trend for microservices where each area of a system is a different programme, possibly/probably living in a different container and/or server, this model is becoming more and more pertinent since the universal data interchange language of JSON and RESTful web services means that the impedance mismatch between different database systems is mitigated and not as important as it perhaps once was.
Now, as for data lakes, an excellent definition is available on the wiki (and excellent article BTW - well worth reading in its entirety):

A data lake is a system or repository of data stored in its
natural/raw format, usually object blobs or files. A data lake is
usually a single store of all enterprise data including raw copies of
source system data and transformed data used for tasks such as
reporting, visualization, advanced analytics and machine learning. A
data lake can include structured data from relational databases (rows
and columns), semi-structured data (CSV, logs, XML, JSON),
unstructured data (emails, documents, PDFs) and binary data (images,
audio, video).

So, the relationship between polyglot persistence and data lakes is clear, but they are definitely not one and the same thing!
To my mind, one can think of a data lake as an instance of polyglot persistence (in much the same way as an object is an instance of a class). Polyglot persistence is a concept, a data lake is a materialisation of that concept in action.
Unsurprisingly, traditional vendors are fighting back against this trend by trying to consolidate these database functionalities in their own products - Oracle for example has embraced JSON document stores into its flagship product (from here):

Oracle as a Document Store
Oracle Database 18c fully supports schemaless application development
using the JSON data model. This allows for a hybrid development
approach: all of the schema flexibility and speedy application
development of NoSQL document stores, combined with all of the
enterprise-ready features in Oracle Database 18c.

and it also has a separate Key-Value store - from here:

Oracle NoSQL Database is a solution for applications with the
following characteristics:
Produce and consume data at high volume and velocity
Require instantaneous response time to match user expectations
Developed with continuously evolving data models
Scale on–demand based on the dynamic workloads

It is worth nothing that the Oracle's flagship database and their NoSQL one are separate products. Interestingly, the most "holistic" approach to polyglot persistence appears to be being taken by PostgreSQL. In their database, you can have a Key-Value store, JSON documents and of course, normal relational tables and one can perform SQL between and within these different storage types.
Amazingly, PostgreSQL's performance as a document store is approx. 2.5 times better than MongoDB's with JSON documents according to this benchmark. Admittedly, EnterpriseDB could be seen to be biased as they are PostgreSQL specialists, but they put the benchmark up on GitHub for anyone to test and I haven't been able to find a refutation by MongoDB.
Finally, just a word of warning - while you may be able to do it, this might not necessarily be a good idea! I'll leave the final word to 2ndQuadrant (PostgreSQL experts):

PostgreSQL has json support – but you shouldn’t use it for the great
majority of what you’re doing. This goes for hstore too, and the new
jsonb type. These types are useful tools where they’re needed, but
should not be your first choice when modelling your data in
PostgreSQL, as it’ll make querying and manipulating it harder.

One thing's for sure, the database world will keep evolving and keep all of us in jobs for a long time to come! :-)
p.s. +1 for an interesting question and welcome to the forum!
* Interestingly, he calls himself a "meme-wrangler" - I like it, kinda quirky


Oracle DB Shutdown issue Gave command: "SQL> shutdown immediate" to shutdown DB. But it is hung.
Also I am not able to connect to DB via PL/SQL Developer. Its throwing error: "ORA 12537: TNS Connection closed

shutdown immediate will end idle sessions, but my understanding is that it will not kill of any statements which are in the middle of processing. But once issued, it will stop any new sessions from starting (thus you cannot connect any more).

So you can either wait for the operations to finish, or try shutdown abort.


WHERE Clause Compare VarChar(n) Column = VarChar(MAX) Variable Results in Inequality Comparison and Poor Query Performance Earlier today I was working on a Reporting Stored Procedure which had poor performance.  I was able to fix the issue by changing a static variable which held a string of ten characters or less from VARCHAR(MAX) to VARCHAR(10).  I am looking to understand why that would have made a difference.
Additional Details If Needed
The Original query is much bigger than what needs to be looked at but I saw the below things happening, when joining the foundational table to a Key-Value Pair table we were returning a ton more rows than was actually needed (in some instances over 14 billion rows for a final output of ~60,000 rows) and we had a few extra operations (particularly the Lazy Table Spool).  Image of that part of the Query Plan is below:

I could see a weird part of the execution plan was how the @ProgramID variable was being handled.  This variable is used as [Column] = @ProgramID and the Column is part of the tables Non-Clustered Index and is a VarChar(10).  But we are still doing an Inequality Search instead of an Equality Search.

After modifying this parameter/variable from VarChar(Max) to VarChar(10).  The execution time dropped from several hours to about 30 seconds and the plan had a very different structure.  Looking at a similar snippit from the execution plan, we were not returning nearly as many rows, we don't have the extra operations and we were doing an equality search on [Column] = @ProgramID (again Column is part of the tables Non-Clustered Index and is a VarChar(10)).

Below is a simplified query which shows some of this behavior (we don't get all the extra steps, but the Seek Predicate difference is still there): (Paste The Plan)
DECLARE @CompanyID VarChar(10) = 'RxCRoads'
        ,@ClientID VarCHar(10) = 'Amgen'
        ,@ProgramID VarChar(MAX) = 'Foundation'
        ,@StartDate DATETIME = '2020-01-01'
        ,@EndDate DATETIME = '2020-12-07'
        ,@RxOnly INT = 9

SELECT PC.CompanyID,
PC.ClientID,
PC.ProgramID,
PC.PatientID,
PC.CaseID,
RxOnly.[Value] AS RxOnly
FROM PATIENTCASES PC
    LEFT OUTER JOIN PatientCaseDetail RxOnly
        ON RxOnly.CompanyId = PC.CompanyID
        AND RxOnly.ClientId = PC.ClientID
        AND RxOnly.ProgramId = PC.ProgramID
        AND RxOnly.PatientID = PC.PatientID
        AND RxOnly.CaseID = PC.CaseID
        AND RxOnly.PatientCaseAdditionalElementId = @RxOnly
WHERE PC.CompanyID = @CompanyID
AND PC.ClientID = @ClientID
AND PC.ProgramID = @ProgramID
AND PC.CaseCreateDateTime >= @StartDate
AND PC.CaseCreateDateTime < @EndDate

Just changing the @ProgramID VarChar(MAX) = 'Foundation') to @ProgramID VarChar(10) = 'Foundation' returns a more streamline plan and better performance (even though not really noticeable in this particular query). (Paste The Plan)
Is there any particular reason why this change in the size of the data type would result in the change in the execution plan?  Running the same query with @ProgarmId VarChar(8000) instead of @Program VarChar(10) has no noticeable difference from @Program VarChar(10). (Paste The Plan)
My guess is that VarChar(MAX) has different comparison rules due to its size, and this is just a state of what VarChar(MAX) is.  But I didn't know if anyone had a better/more technical answer than I did so I can better apply this lesson in the future.

The Problems
You have two things working against you:

Local variables often have weird side effects
MAX datatypes can't be pushed, so you end up with late filters in your query plans

Some further reading:

Yet Another Post About Local Variables
MAX Data Types Do WHAT?
Why You Shouldn’t Ignore Filter Operators In Query Plans Part 1 | Part 2
Why does SQL Server use a better execution plan when I inline the variable?

In Particular
In your worse plan, there are two filter operators:

Each one is there to deal with the MAX type on @ProgramId. The length of the parameter doesn't matter much until you hit the max type, so long as it's the correct type, e.g. varchar = varchar or nvarchar = nvarchar
You may also see GetRangeThroughMismatchedType or GetRangeThroughConvertthere to deal with a date \ datetime mismatch:

Cast to date is sargable but is it a good idea?

You may find some overall relief by adding a recompile hint, but it won't fix the issue you're having around the MAX variable.