When executing a command via xp_cmdshell what user issues the command to the system?

From xp_cmdshell (Transact-SQL) in the product documentation:

The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

See the full entry for the steps you need to take to set up the xp_cmdshell proxy account.


First, let me say that with the information given it will be hard to get at the root cause - it is usually an iterative process that takes multiple attempts to track down the culprit.  In the interest of answering your "what next?" portion of the question rather than identifying the root cause, read on.....

First, a couple of recommendations:

Get the host into MMS (it's free) - see http://mms.10gen.com - so you can graph stats over time and get a view of the issues without having to be sitting on the box running commands
Get munin-node installed too, so you can correlate ops etc. with IO (install docs for MMS explain this).

Next, a couple of quick checks for common causes:

What is your filesystem/kernel? - these generally need to be ext4/XFS and recent enough to have fallocate working (2.6.23 and 2.6.25 respectively) so that new file allocation is not slow
Assuming you don't get MMS and munin installed, get iostat output to match up with mongostat to determine if IO is the root cause for the bottleneck
Do you do any periodic batch updates that grow the documents significantly (i.e. that would cause moves)?  Moves are expensive and can cause IO to get backed up
Is your disk up to the data volume you are writing to it?  MongoDB fsyncs to disk every 60 seconds by default, if the volume that needs to be synced after 60 seconds is massive (say because of an insert spike) then you can also run into issues

That's not an exhaustive list, I have seen other issues cause this, but that should get you started down the right path.


SELECT id, name, empid
    MAX(c_lan)    AS c_lan,
    MAX(Java_lan) AS Java_lan,
    MAX(Dot_lan) AS Dot_lan
FROM tbl
GROUP BY id, name, empid;

If you really need no instead of NO, then add LOWER(...).

Edit.

I am assuming a table structure something like

 CREATE TABLE Lans (
     // id NAME empid c_lan Java_lan Dot_lan
     id SMALLINT UNSIGNED NOT NULL,
     name VARCHAR(50) NOT NULL,
     empid SMALLINT UNSIGNED NOT NULL,
     c_lan    VARCHAR(10) NULL,
     java_lan VARCHAR(10) NULL,
     dot_lan  VARCHAR(10) NULL,
     PRIMARY KEY(id, name)
 ) ENGINE=InnoDB;


How to select rows which are mapped to a junction table in a many to many relationship?

Assuming that locations and professionals also have their PK columns as location_id and professional_id, respectively, and location_professional uses those columns to link the two entities:

SELECT loc.*
    FROM location_professional AS lp
    JOIN locations AS loc  ON lp.location_id = loc.location_id
    WHERE lp.professional_id = 4;

In your simple example, there is no need to also JOIN professionals AS p ON ....  In a typical case, that would also be needed.


You can use online service https://recovery.twindb.com/ (I'm the author). Basically, it's a wed interface to mysqlfrm.

To restore structure from multiple frm files you can run this

find /path/to/frm_files -name '*.frm' | \
xargs mysqlfrm --basedir /usr --port 3333

In /usr mysqlfrm should find mysqld (it starts it to read the frm).


Can a trigger procedure in PostgreSQL learn its own name?

You can query pg_trigger catalog table and gather this information:

SELECT
    tg.tgfoid::regproc::text
INTO
    v_function_name
FROM
    pg_trigger tg
WHERE
    tg.tgrelid = TG_RELID
    AND tg.tgname = TG_NAME;

v_function_name is a variable you must declare before (as text or varchar).

Casting to regproc and then to text will make it double quote the name if not simple (just like quote_ident), if you want to avoid it, you can simple join pg_proc and get proname (in this case v_function_name can be of type name):

SELECT
    pr.proname
INTO
    v_function_name
FROM
    pg_trigger tg
    INNER JOIN pg_proc pr ON pr.oid = tg.tgfoid
WHERE
    tg.tgrelid = TG_RELID
    AND tg.tgname = TG_NAME;


How to reinitialise /var/lib/mysql files?

You should be able to just run mysql_install_db from the command line

mysql_install_db initializes the MySQL data directory and creates the system tables that it contains, if they do not exist. It also initializes the system tablespace and related data structures needed to manage InnoDB tables. As of MySQL 5.6.8, mysql_install_db is a Perl script and can be used on any system with Perl installed. Before 5.6.8, it is a shell script and is available only on Unix platforms.

Please read the MySQL Documentation in full for mysql_install_db, then ...
GIVE IT A TRY !!!
If it does not work on the first try, do this
mkdir /var/lib/mysql
mkdir /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql

then run mysql_install_db again


How can I create a recursive query over a column that has gaps?

To avoid gaps use additional CTE:

WITH re_enumerated_games AS
( SELECT ROW_NUMBER() OVER (ORDER BY id) id,
         id real_game_id,
         home_player,
         away_player,
         home_score,
         away_score
   FROM games ),


Use variables within a function with INSERT INTO

This is a bad idea. You're creating a MATERIALIZED VIEW, just use the native functionality for that.

CREATE MATERIALIZED VIEW schema_test
  AS SELECT timestamp, component_name, component_type, status, message;

Then simply run REFRESH MATERIALIZED VIEWschema_test; it will clear out the old results and insert the new results of the query.


rebuild on clustered index, why does datasize shrink?

When a table has a clustered index, the index is the table data (otherwise you have a heap type table). A rebuild of the clustered index (any index in fact, but the space wouldn't be counted as "data" for a non-clustered index) will result in partially used pages being merged into a more full form.

As you insert data into an index (clustered or otherwise) in index order leaf pages are created as needed and you will only ever have one partial page: the one at the end. As you enter data out of index order a page needs to be split for the data to fit in the right place: you end up with two pages which are approximately half full and the new row goes into one of them. Over time this can happen a lot, consuming a fair amount of extra space, though to an extent future inserts will fill some of the gaps. Non-leaf pages will see a similar effect too, but the actual data pages are far more significant in size then they are.

Also deletes may result in partial pages. If you remove all the rows in a page it gets counted as "unused" but if it has one or more row of data left it is still counted as in use. Even if there is only one row using 10 bytes in a page, that page counts as 8192 bytes in the count of space used. Again future inserts might fill some of the gap.

For variable length rows, updates can also have the same effect: as a row gets smaller it may leave space in its page that is not later easy to reuse, and if a row in a nearly full page grows longer it could force a page split.

SQL Server does not spend time trying to normalise the data by rearranging how pages are used, until explicitly told to such as your index rebuild order, as such garbage collection exercises could be a performance nightmare.

I suspect this is what you are seeing, though I'd say that having enough space allocated for ~2.7 times the amount the data absolutely needs to is a particularly bad case. It might imply that you have something random as one of the significant keys in the index (a UUID column perhaps) meaning that new rows are unlikely to ever be added in index order, and/or that a significant number of deletes have happened recently.

Page Split Example

Inserting in index order with fixed length rows of which four fit into a page:

Start with one empty page:
        [__|__|__|__]
Add the first item in index order:
        [00|__|__|__]
Add the next three
        [00|02|04|06]
Adding the next will result in a new page:
        [00|02|04|06] [08|__|__|__]
And so on...
        [00|02|04|06] [08|10|12|14] [16|18|__|__]

Now for adding rows out of index order (this is why I used even numbers only above): Adding 11 would mean either extending that second page (not possible as they are of fixed size), moving everything above 11 up one (far too expensive on a large index) or splitting the page like so:

[00|02|04|06] [08|10|11|__] [12|14|__|__] [16|18|__|__]

From here, adding 13 and 17 won't result in a split as there currently room in the relevant pages:

[00|02|04|06] [08|10|11|__] [12|13|14|__] [16|17|18|__]

but adding 03 will:

[00|02|03|__] [04|06|__|__] [08|10|11|__] [12|13|14|__] [16|17|18|__]

As you can see, after those insert operations we currently have 5 data pages allocated which could fit a total of 20 rows, but we only have 14 rows there ("wasting" 30% of the space).

A rebuild with default options (see below about "fill factor") would result in:

[00|02|03|04] [06|08|10|11] [12|13|14|16] [17|18|__|__]

saving one page in this simple example. It is easy to see how deletes can have a similar effect as out-of-index-order inserts.

Mitigation

If you are expecting data to come in a fairly random order with respect to index order, you can use the FILLFACTOR option when creating or rebuilding an index to tell SQL Server to artificially leave gaps to later fill in - reducing page splits in the long run but taking more space initially. Of course getting this value wrong can make things far worse rather than making the situation better, so handle with care.

Page splitting, particularly on the clustered index, can have a performance implication for inserts/updates so FILLFACTOR is sometimes tweaked for that reason instead of the space use issue in databases that see a lot of write activity (but for most apps, where reads outweigh writes by several orders of magnitude, you are generally better off leaving fill-factor at 100% except for specific cases like where you have indexes over columns with effectively random content).

I assume other big name DBs have a similar option, if you need this level of control in them too.

Update

Regarding the ALTER INDEX statement added to the question after I started typing the above: I assume that the options are the same as when the index was first built (or last rebuilt) but if not then the compression option could be very significant if it was added this time around. Also in that statement the fillfactor is set to 85% not 100% so each leaf page will be ~15% empty immediately after the rebuild.


Why do psql queries take longer when inserting many rows. Why is it non linear

Leaving aside the fact that the DISTINCT is causing some weird behavior, there are two main reasons why insert times get longer as bulk loads get larger:

B-tree indexes get less efficient to update as they get larger and have more tree levels.  So indexes take longer to insert a the millionth value than they did the 10th.
At certain sizes, you exceed certain thresholds which cause extra IO on the system, resulting in lag while the IO takes place.  These thresholds, which interact in complex ways, include:

the size of the WAL, causing log rotation
the size of the RAID cache, dropping to disk speeds
the size of Postgres' dedicated cache, causing flushing to the FS
the size of the FS cache's dirty block flushing threshold
the size of the entire FS cache, causing emergency flushing


MongoDB MMAPv1 vs WiredTiger storage engines

Personally, I prefer the mmapv1 storage engine as of now for three reasons.

Reason 1: Maturity

It isn't that WiredTiger is immature. But mmapv1 is well understood and battle tested all way up and down, back and forth and above and beyond. WiredTiger has had some serious issues (see http://jira.mongodb.com for details) fairly recently, and I am not willing to have my customers find the next one the hard way.

Reason 2: Features

Given, WT has some f... undamentally awesome features. The thing is: I have not seen anyone benefitting from them. Compression? Either way, you sacrifice rather hard to achieve performance for rather cheap disk space. Lack of the document migration problem for expanding documents? Well, we still have the 16MB size limit and added complexity for embedded documents, especially when embedding is overdone.

There are other features, but in general: I do not see to much benefit from them as of now.

Reason 3: Total cost of ownership

For new projects, WT might be fine, especially since 3.2 , since the following does not apply.

Doing data migrations is expensive. It needs to be planned, the plan needs to be agreed upon by all stakeholders, emergency contingency plans have to be created and agreed upon, the migration needs to be prepared, executed and reviewed. Now multiply the time needed with the stakeholders who are part of this process, and the costs for the data migration skyrocket. The return on investment on the other hand seems rather small. You can scale quite a bit instead of doing a migration if you take those factors into account. To give you an impression: I'd estimate roughly one "man-week" per stakeholder if a migration is planned, executed and reviewed properly. With costs of $100 per hour per person, and only three people involved (manager, DBA and developer), that amounts to $12.000. Note that this is a conservative estimate.

Conclusion

All those factors above have brought me to the conclusion not to use WT whatsoever. At the moment.

Update

This post is some months old now, so it deserves an update

On maturity

My original comments on maturity are sort of obsolete. WiredTiger has not had any major issues for a while now and has become the default storage engine as of MongoDB 3.2

On Features

My original comments still hold some validity, imho.

Compression

However, when being tight on budget or, more generally speaking, performance is not the primary concern, the performance tradeoff is rather small, and you basically trade slight performance impacts (when compared to uncompressed WT) for disk space, utilizing what otherwise would idle around: the CPU.

Encryption

MongoDB 3.2 Enterprise introduced the ability to have WiredTiger storages encrypted. For data with enhanced security needs, this is a killer feature and makes WT the only storage engine of choice, both technically (MMAPv1 does not support encryption) and conceptually. Setting aside the possibility of encrypted disk partitions, of course, although you might not have that option in some environments.

Document level locking

I have to admit that I basically omitted that feature of WT in my above analysis, mainly because it did not apply for me or my customers when I wrote the original answer.

Depending on your setup, mainly when you have many concurrent writing clients, this feature may provide a great performance boost.

On Total cost of ownership

Doing migrations is still expensive. However, taking the changes in maturity and the changed view on the features into account, a migration might be worth the investment if:

You need encryption (Enterprise Edition only!)
Performance is not your absolute primary concern and you can save money in the long run (calculate conservatively) using compression
You have a lot of processes writing concurrently, since the increase in performance might save you vertical or horizontal scaling.

Updated Conclusion

For new projects, I use WiredTiger now. Since a migration from a compressed to an uncompressed WiredTiger storage is rather easy, I tend to start with compression to enhance the CPU utilization ("get more bang for the buck"). Should the compression have a noticeable impact on performance or UX, I migrate to uncompressed WiredTiger.

For projects with a lot of concurrent writers, the answer to wether to migrate or not is almost always "Yes", too – unless the project's budget prohibits the investment. In the long run, the performance increase should pay for itself, if the deployment was otherwise reasonably planned. However, you need add some development time to the calculation, since in some cases the driver needs to be updated, and there might be problems which need to be dealt with.

For projects which are tight on budget and can not afford more disk space for the moment, migrating to a compressed WiredTiger can be an option, but the compression puts a bit load on the CPU, something unheard of with MMAPv1. Furthermore, the migration costs might be prohibitively expensive for such a project.


Microsoft SQL Server Management Studio - default table view, default table parameters

I'm not sure you can automate this in SSMS. Why not take the opportunity to learn a little SQL? The script below will create a table with ID as the primary key (int) - you can then just add the extra columns you need (refresh your table list first):

CREATE TABLE [dbo].[your_table_name](
[ID] [int] NOT NULL,
CONSTRAINT [PK_your_table_name] PRIMARY KEY CLUSTERED
(
   [ID] ASC
))


If AB->C is an FD for a database then does that imply A->C

I came to know that ab → c does NOT imply a → c and b → c

Correct. It does not imply either.

  ....
  So, that means that if A -> C, then we can imply AB -> C.

Yes, correct. but the following:

  Assuming ... Is this mathematical approach not correct or I am wrong in a different way?

No, A -> C is deducted from A -> BC, not from AB -> C.

A -> BC is just a shorthand notation for A -> B and A -> C


SQL Server : huge log file, we know why, but what can we do about it

You've said that it is in FULL recovery mode currently, and you want to keep it that way, but you haven't said whether you might need to roll back to any point-in-time prior to today.

Here are your two choices:

If there is any possibility you might need to restore to a past point-in-time, you need to fully back up the existing (huge) tran log (and keep the backup file). So go ahead and run that tran log backup. Yes, it will take a very long time, and yes, it will require a lot of disk space. But once that is done, each new tran log backup will be small and fast, and will only contain transactions from the last successfully log backup. You will be able to shrink the log down to something reasonable.
If you don't anticipate ever needing to roll back to a prior point-in-time (like if a disaster happened, you'd just restore one of the prior nightly fulls anyway), and you only care about future possible point-in-time recoveries, then switch to SIMPLE, shrink the log, switch back to FULL, run a full backup, and start your log backups. This will clear the huge backlog of completed transactions.

To be crystal clear, both SIMPLE and FULL recovery mode would allow you to restore to any prior full (or differential) backups. FULL recovery mode allows you to restore to an intermediate point between two full backups.

So let's say your nightly full backups happen at 8pm, and your database was irretrievably corrupted or data was accidentally deleted at 10am the next day. With SIMPLE mode (or without available tran log backups), your only choice is to restore the 8pm full backup from the prior night. With FULL recovery mode (if you've taken and kept tran log backups), you could restore the database to immediately before the problem occurred, at 9:59:59am.

Of course if you would never need point-in-time recovery at all (it's a reporting or processing database where you could simply restore the full and then reimport/reprocess all subsequent data from other sources), then you could just switch to SIMPLE mode permanently and forget about log backups altogether (although SQL still uses the tran log for in-progress transactions, then marks the space for re-use when they are complete).

But this is a business decision, not a tech decision. Talk to your managers or business stakeholders.


Lookup table for a one-to-many relationship

Your table will work fine for this purpose, but you probably want to add an index. If the primary reason for using this table is to take an outside_ticket_id and get the corresponding ticket_id's I would add the following clustered index:

CREATE CLUSTERED INDEX [CL_Lookup_OD_ID] on [lookup](outside_data_id)
GO

If the primary lookup will be the other way around (trying to find the outside_data_id  from a ticket_id) place the clustered index on the other column.

----  Oh, sorry, just noticed this is Postgres. The above syntax is SQL Server.  For Postgres, create an index on the column, then issue the cluster command, like so:

CREATE INDEX IX_outside_data_id on lookup(outside_data_id);
CLUSTER lookup using IX_outside_data_id;

You may also want to look at the "fillfactor" on the index depending on how heavy the insert load is here.  But that is a large topic worth exploring on it's own...


The CPU utilization spikes to 80+ even with just one user on Amazon RDS (MYSQL INNODB)

What you describe is the expected behavior.

An m3.xlarge has 4 vCPUs (virtual CPUs -- and having 4 of these typically means two cores, each with two hyperthreads).  100% utilization means all 4 vCPUs are completely busy, while 75% means 3 are busy, 50% means 2, and 25% means 1.  (Below 100%, this doesn't necessarily mean e.g. exactly 3 are working and one is idle, but refers to the total idle time of each vCPU, divided by the number of vCPUs, subtracted from 100%).

In MySQL Server, any single query can use only the equivalent of one vCPU (it's not physically locked to one specific vCPU by the OS, but it can't consume more than the equivalent of one) ... so with 2 or 3 long running queries, 50%-75% CPU utilization would be exactly normal, assuming the thing that causes the query to require a long amount of time in the first place is the amount of CPU work required to process it -- anything less than that means the CPU cycles aren't the primary factor that causes the query to be long-running.  (The usual other cause of long-running queries is the amount of disk I/O that must be done to satisfy the query.  Performance of queries is typically bounded by available disk or available CPU, with the nature of the query, and the size of the buffer pool relative to the working data set being significant factors).

If CPU cycles are indeed the primary cause for the query to require so much time, you can see why the numbers on the CPU are what they are -- it would not make sense to artificially throttle down the query to use even less, thus making it even slower, still... but this is what would have to happen.

As it stands, MySQL will consume the resources it needs, as quickly as the operating system will allow it to, in order to execute any individual query as quickly as possible. If you had more long running queries running at a given moment in time than there are vCPUs on the system, and the queries are CPU-bound, the OS thread scheduler is the entity responsible for allocating each of them a reasonable share of whatever is available --so on a 4 vCPU system, with 4 long-running queries already running, if a 5th query starts, it doesn't sit and wait for one of the others to finish -- it actually would slow each of them down by about 20% as it claimed its share of the total available CPU cycles -- all running queries get a share, based on how much work they are trying to do.

On a machine with only 1 vCPU, you'd find that only one single long-running query would spike the utilization to 100%, and this also would be completely normal, if the need for CPU cycles is the main factor influencing the query's overall execution time.


In his seminal paper A Relational Model for Large Shared Data Banks, Dr. E.F. Codd mentiones a system catalog. In his famous Twelve Rules he stipulates an (online) catalog.

Then, in his book entitled The Relational Model for Database Management, Version 2, he defines catalog and meta-data as follows:

  The catalog is […] a relational database itself—one that is dynamic and active and that represents the meta-data. Meta-data consists of data that describes all of the data in the database, as well as the contents of the catalog itself.

In chapter 15 of the same book, he makes a distinction between the terms catalog and dictionary that I consider quite relevant regarding your question:

  In the relational model, the catalog holds the database description. In some relational DBMS products this description is also called the catalog, while in others it is called the directory. Whatever it is called, it should be carefully distinguished from a dictionary, which normally includes all of the information found in the catalog, but also contains a large amount of information concerning the application programs that operate on a scheduled or non-scheduled basis upon various parts of the database.

Which confirms your description of a data dictionary.

After that, in the same book and chapter, he makes a comment that relates a catalog and a dictionary very closely:

  […] authorized
   users can easily extend the catalog so that it becomes a full-fledged, active, relational data dictionary, whenever the DBMS vendor fails to do so.

So, one can say that a data dictionary is an enhanced catalog.

In practice, of course, specific SQL platforms can use the same terms or establish their own names to refer to these elements. For instance, Oracle provides, precisely, a data dictionary that, according to this link from the online documentation, is…

  […] a read-only set of tables that provides information about the database.

  A data dictionary contains:

  The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
  How much space has been allocated for, and is currently used by, the schema objects
  Default values for columns
  Integrity constraint information
  The names of Oracle users
  Privileges and roles each user has been granted
  Auditing information, such as who has accessed or updated various schema objects
  Other general database information

So, in this case, the data dictionary includes the catalogue.

Each alternative name will depend on the particular platform of choice.


is it possible to create a form in the intranet that can be stored in a database?

You could look into APEX which is available even on the free Oracle Express Edition.

  Oracle Application Express (APEX) is a Web application development
  tool for Oracle Database. The tool uses built-in features such as user
  interface themes, navigational controls, form handlers, and flexible
  reports to accelerate application development.

  Oracle Application Express installs with the database and consists of
  data in tables and PL/SQL code. When you run an application, your
  browser sends a URL request that is translated into an Oracle
  Application Express PL/SQL call. After the database processes the
  PL/SQL, the results are relayed back to the browser as HTML. This
  cycle happens each time you request or submit a page.

  You can use Oracle Application Express with the embedded PL/SQL
  gateway. The gateway runs in the XML DB HTTP server in the database
  and provides the necessary infrastructure to create dynamic
  applications. As shown in Figure 19-1, the embedded PL/SQL gateway
  simplifies the application architecture by eliminating the middle
  tier.

You will need to evaluate this option with respect to your existing infrastructure and requirements.


Why does setting my column as Always Encrypted change its collation from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2?

SQL Server 2017 documentation:
Always Encrypted is not supported for the columns with the below characteristics:
(...)
String (varchar, char, etc.) columns with non-bin2 collations
(...)

Source: Always Encrypted

Looks like it is changing in SQL Server 2019:

Since its initial release, Always Encrypted has had a restriction regarding the use of collations: non-BIN2 collations are not allowed for character string columns encrypted using deterministic encryption. This restriction also applies to enclave-enabled string columns.

The use of non-BIN2 collations is permitted for character string columns encrypted with randomized encryption and enclave-enabled column encryption keys. However, the only new functionality that is enabled for such columns is in-place encryption. To enable rich computations (pattern matching, comparison operations), you must ensure the column uses a BIN2 collation.

Configure Always Encrypted with secure enclaves


SSIS Condition that include two connections/data bases

Put the CSV import in one data flow, then in a dependent data flow use an OLE DB Source that contains the results you need as the output of a SQL query.
Feed that into an OLEDB destination.

Your query would look similar to:

SELECT t1.col1,
CASE WHEN EXISTS (SELECT null FROM db2.dbo.table2 t2 WHERE t2.col1 = t1.col1) THEN '2'
ELSE '1' END as [col2]
FROM db1.dbo.table1 t1


SQL Server -- see when user/ login was last modified? And other meta-data

You can have a look at

Server audit logging
C2 Audits

When working at banks or some Government agencies one Normally needs some way to proof who has accessed the database as well as the reports that was generated.

What happens is one always has to log the machine and user name the user used to accessed the data. one can record this by caling SUSER_SNAME() and Host_Name() in your procedures and logging this data along with the parameters used so that one can verse engineer who has accessed/ seen what data.


ORA-01722: The specified number was invalid when truncating timestamp

The FIRST_LOAD_TIME column is actually a VARCHAR2 (which is a bit strange to me) so;

select to_date(first_load_time,'YYYY-MM-DD/HH24:MI:SS') from v$sql

Will show it as a date by applying the function TO_DATE.
Simply add a TRUNC to the beginning to get the value you want;

select TRUNC(to_date(first_load_time,'YYYY-MM-DD/HH24:MI:SS')) from v$sql


Trigger performance - UPDATE statement joining on INSERTED

as the number of records increase in TABLE_B, trigger performance degrades rapidly

That would imply the updates do not have appropriate indexes available, or are unable to use them for some reason, on TABLE_B or the tables that it is joined to.

Without more detail about the table layout (what indexes you have on each of the tables) and the query plan that SQL has decided to use we can't help you in much detail, so I suggest you update the question with that information.

Some first thoughts:

  FROM TABLE_C C INNER JOIN INSERTED I ON C.UNIQUE_ID = I.UNIQUE_ID AND C.USER_ID = I.USER_ID

As you say the performance is tied to TABLE_B this is probably OK, though again the query plan would be helpful information to provide. Presumably you have an index on TABLE_C.UNIQUE_ID or it is the primary key or subject to a unique constraint.

  INNER JOIN [TABLE_B] B ON B.UNIQUE_ID = C.UNIQUE_ID AND B.USER_ID = C.USER_ID

With the right indexes/keys this should not be an issue either. For maximum efficient for this specific statement TABLE_C should have an index covering UNIQUE_ID & USER_ID and TABLE_B one covering UNIQUE_ID & USER_ID and including ATTRIBUTES.

  WHERE B.[ATTRIBUTES] & 4 = 0

This may be significant. Depending on how TABLE_B and TABLE_C are getting joined (again, provide the index details and query plan) this could be forcing a table scan of TABLE_B. The expense of that operation would grow at linearly with the number of rows in TABLE_B.


I would look into partitioning. If partitioned by day, you could just drop the entire partition once it gets too old. You may even no longer have to vacuum.

Also, overall performance might increase, since you're not inserting where you're deleting. You would just need to write the code to create new partitions and delete old ones.

This is exactly what partitioning is for.


SQL Server Logical Device for Directories

I have different folders on different machines and I need one logical
name for them both, sure one on each server.

If you have different folders on different servers and you need them to be presented with the same name you could use mklink to create a directory symbolic link.


How to find a SQL Job details in Production environment?

You can use this query directly to get all the names of jobs that include specific text (for example "MyText"

SELECT j.job_id, j.[name], s.command
FROM sysjobsteps s
right join dbo.sysjobs j on s.job_id = j.job_id
WHERE command LIKE '%MyText%'

** Execute query on multiple SQL Servers can be done using SSMS by creating a local server group or a Central Management Server and one or more server groups

Open SQL Server Management Studio -> View  -> click Registered Servers
In the "registered Servers" windows you can create groups and add servers.
To execute query on a group simply right-click a server group, point to Connect, and then click New Query

** Execute query on multiple Azure SQL Databases can be done using Azure Elastic Job (you can search a recording of my lecture about Elastic Pool and Elastic Job).


fn_listextendedproperty fails in single_user mode

Apparently the big bold warnings were not just for show - setting AUTO_UPDATE_STATISTICS_ASYNC OFF fixed the issue.


Find when a specific database went offline and the reason for same

At minimum, please check sql logs, for example, if I purposely take a db offline using

alter database [TestDB] set offline with rollback immediate;

I can see in SQL Server log, the following message

  Date      10/24/2016 12:28:57 PM Log      SQL Server (Current - 10/24/2016
  12:00:00 AM)

  Source        spid51

  Message Setting database option OFFLINE to ON for database 'TestDB'.


It sounds like you want the GREATEST function

INSERT INTO my_table( date_column )
 VALUES( GREATEST( date_default, date_insert_value ));


ORACLE DB Connects only when I do not specify SID

Found the issue. But I don't have any explanation why or how it worked.
It worked after removing GLOBAL_DBNAME parameter in listener.ora under SID_LIST_LISTENER


SQL Server 2008 - Replacing csv substrings in view-row using data from other table

As a joke (but it works nevertheless):
WITH
cte1 AS ( SELECT id, ';'+value1+';' value1, value2, value3
          FROM data ),
cte2 AS ( SELECT id, ';'+CAST(id AS VARCHAR)+';' sid, ';'+code+';' code
          FROM codevalue ),
cte3 AS ( SELECT cte1.id, REPLACE(cte1.value1, cte2.sid, cte2.code) value1, cte1.value2, cte1.value3, cte2.id cid
          FROM cte1
          JOIN cte2 ON cte2.id = 0
        UNION ALL
          SELECT cte3.id, REPLACE(cte3.value1, cte2.sid, cte2.code) value1, cte3.value2, cte3.value3, cte2.id
          FROM cte3
          JOIN cte2 ON cte2.id = cte3.cid + 1 )
SELECT id, SUBSTRING(value1, 2, LEN(value1) - 2) value1, value2, value3
FROM cte3
WHERE cid = ( SELECT MAX(id)
              FROM codevalue )
ORDER BY id

fiddle
Needs CodeValue.id have no gaps. If not then add ROW_NUMBER() column to cte2 and use it for next codevalue row selection (do not forget to alter starting value in static part from 0 to 1).


Backup error: Failed to flush the commit table to disk

This usually happens when you have enabled change tracking on the database. A fix was released some time back:

FIX: Backup fails in SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012 if you enable change tracking on the database (Microsoft Support)

As it says there:

  The fix [...] was first released in Cumulative Update 7 for SQL Server 2008 Service Pack 2

The latest (and final) Service Pack 4 for SQL Server 2008 would also contain this fix.


Comparing the values of two columns with different datatypes

Here's an example of how you could use a combination of CONVERT, REPLACE and SUBSTRING to 'convert' your DateTime2 column to a float.

You can 'convert' the DateTime2 column to a varchar data type, then use SUBSTRING to retrieve the CCYY-MM part and then use REPLACE to eliminate the '-' in the date.

I added an extra row to the sample data so you could see that the compare works.

SET NOCOUNT ON
Declare @T TABLE (EventID float, EventStartDate Datetime2)
insert into @T VALUES
(201605, '2017-09-21 12:40:29.9800000'),
(201504, '2017-09-21 12:40:46.8430000'),
(201606, '2017-09-21 12:41:39.3370000'),
(201609, '2017-09-21 12:41:57.3100000'),
(201709, '2017-09-21 12:41:57.3100000')

SELECT *
    ,CONVERT(FLOAT, REPLACE(SUBSTRING(CONVERT(VARCHAR(7), EventStartDate), 1, 7), '-', ''))
FROM @T
WHERE EventID = CONVERT(FLOAT, REPLACE(SUBSTRING(CONVERT(VARCHAR(7), EventStartDate), 1, 7), '-', ''))

Results:

| EventID | EventStartDate              | (No column name) |
|---------|-----------------------------|------------------|
| 201709  | 2017-09-21 12:41:57.3100000 | 201709           |


Oracle 11gR2 permit NOLOGGING but ensure non-corrupt backups

The first principle to remember is, do not make a backup when a NOLOGGING operation is occurring. Oracle Database does not currently enforce this rule, so DBAs must schedule the backup jobs and the ETL jobs such that the NOLOGGING operations do not overlap with backup operations.

The above excerpt is from Oracle Documentation.

  Q1: What will happen if a NOLOGGING DML operation is running when backup script starts and FORCE_LOGGING=YES runs? Will DML proceed (and backup get NOLOGGING corruption on affected tables)? Will ALTER DATABASE wait for NOLOGGING transaction to commit (while preventing others from starting)? Will NOLOGGING DML operation quietly rollback and restart in LOGGING mode?

The FORCE_LOGGING=YES will be applied for subsequent operations only not for running operation.

  Q2: For recovery itself, is there special syntax to say, "Restore tablespace A, B, and C to time-of-failure, but only recover tablespace D to last backup?"

No, However you may use Tablespace Point-in-Time Recovery (TSPITR) for tablespace A,B and C.

  Q3: Can NOLOGGING tables be restored to incremental backup, or only full level-0 backups?

Yes(But not tables, for tables you can use Flashback). After restoring full/level 0 backup Oracle will apply incremental backups.

For details about NOLOGGING and Backup Strategy:
Plan for NOLOGGING Operations in Your Backup/Recovery Strategy


MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first 2 columns, the first 3 columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

SELECT * FROM test WHERE first_name='Andrew';

SELECT * FROM test
  WHERE last_name='Smith' OR first_name='Andrew';

Suppose that you issue the following SELECT statement:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer attempts to use the Index Merge optimization..

Source: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html


How come systemctl status postgresql.service and systemctl status postgresql@10-main show different results?

Ubuntu and Debian

If you're using Ubuntu, rather than using systemd's systemctl to gauge the status of your clusters use the distro's own pg_lsclusters. It'll tell you if they're running and the port number,

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log


Does the command SET STATISTICS...ON turn statistics information on for all queries running throughout the server or just the current connection?

The answer to the question can be found higher up the document tree:

SET statements:

  The Transact-SQL programming language provides several SET statements
  that change the current session handling of specific information. The
  SET statements are grouped into the categories shown in the following
  table.

Which explains that all these set statements are applied to the current session.

With the category for SET STATISTICS IO,TIME ON; being statistics statements


Update table with data from table in a separate database

You can try this

UPDATE a
SET a.Address = b.ID
FROM TableA a
JOIN TableB b ON b.ID = a.ID
WHERE a.ID <= 1449


How can I store latitude and longitude without PostGIS?

The simplest solution without PostGIS would be to store lat/long as two number columns.
numeric for exact precision.
double precision or even just real if you don't need the precision.

I see no reason why the data type point shouldn't work as well. Per documentation:

  Points are the fundamental two-dimensional building block for
  geometric types. Values of type point are specified using either of
  the following syntaxes:

( x , y )
  x , y

  where x and y are the respective coordinates, as floating-point
  numbers.

Related:

Which data type for latitude and longitude?


Pasting data from Excel table takes very long

You don't need to write code. Just use the Data Import/Export Wizard.

Right-click on the database in SSMS, go to Tasks and Import Data. Select Flat File Source as your data source (because you're using CSV), and then browse for the file. Click next, select the database and the target table. You'll need to check the mappings for the CSV columns to the table columns (making sure the CSV column headings are the same as the table columns helps with this), and you can preview the data to make sure.

If everything is correct, click Next, Next and Finish to import the data. If there's an error, it'll tell you what it is (sometimes slightly cryptic though).

As a note though: I'd advise against trying to copy/paste/edit data in the Table Edit window. Writing SQL statements is a much more effective and time efficient way of doing it (or using the Data Import/Export Wizards).


SSIS Script to split string into columns

Usually the way you'd go about this is to have a Script component and enter your VB / C# code to handle this type of input. If you have a variable number of columns than you should forget about SSIS being able to handle this since it doesn't work well with non-finite number of columns (being that tables have a finite number of columns on them).

Having said this, if you have a finite number of columns in the input file, than what you can do is specify the delimiter (the semicolon in the above case) and have the SSIS split the data into the columns and then do the ETL process on them, if needed.

If you have an upper limit of columns, but you get files with less columns than placing a script task before the DFT module to append the extra delimiters that are needed.

Since the first row looks like the header row (column names) you can set the flat file source to skip the first row.

Another alternative would be, if you have a small number of file formats, would be to create different names for each of the log files and have individual DFT modules handle each type of file.

Hope this helps.


Getting a SEEK rather than SCAN on an Aggregate

You want your index to have ClientID as the leading key.  E.g.:

CREATE INDEX IX_Visit_ClientID_StartDate ON Visit(ClientID, StartDate)

This is because you need to join on ClientID before finding the most recent StartDate. Therefore ClientID needs to appear first.

Along with that index change... Since you only want to find the most recent Visit.StartDate you can change your LEFT JOIN Visit to an OUTER APPLY that gets the most recent date.

Try this:

CREATE TABLE #SearchWords
(
    ID int IDENTITY(1,1),
    Word NVARCHAR(50)
)

DECLARE @SearchWordCount INTEGER;

INSERT INTO #SearchWords
(
    Word
)
SELECT
    value
FROM
    STRING_SPLIT(@SearchWords, ' ')
WHERE
    TRIM(value) <> ''

SELECT @SearchWordCount = @@ROWCOUNT;

SELECT
    C.ClientID,
    C.FirstName,
    C.LastName,
    C.FullName,
    C.DateOfBirth,
    G.GenderName,
    G.GenderIcon,
    C.VerificationCode,
    V.LastVisitDate
FROM
    Client C
OUTER APPLY (
    SELECT MAX(StartDate) AS LastVisitDate
    FROM Visit AS V
    WHERE C.ClientID = V.ClientID
) AS V
INNER JOIN LookUp.Gender G on
    C.GenderID = G.GenderID
WHERE
    (
        EXISTS( -- if we have words
                SELECT *
                FROM #SearchWords s
                WHERE (c.FirstName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.LastName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.VerificationCode LIKE CONCAT('%',s.Word,'%'))
            )
        OR @SearchWordCount = 0
    )
    AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth)
GROUP BY
    C.ClientID,
    C.FirstName,
    C.LastName,
    C.FullName,
    C.DateOfBirth,
    G.GenderName,
    G.GenderIcon,
    C.VerificationCode

  Note:

  I've also made the @@ROWCOUNT improvement I suggested in my comment.

The GROUP BY is almost certainly unnecessary now too.  Remove it if it is.

Off Topic:

One final last comment which is technically unrelated to your question. You seem to be doing something that Full Text Searching may be a better solution for.


database schema design for school system

To your specific question of (easily satisfied) plausibility, the answer is yes.  Since you seem to be looking for a broader critique. You seem to be mostly on the right track, but a few things are making your description overly complex.

It looks like your Classes table is more like what I would consider a course to be and your departments_teachers_and_classes is more of what I would expect a Classes table to be.  A course would be Math, but a class would be the Math course taught by a particular teacher during a particular school term.  A class is like an instance of a course.

To carry this change through, your departments_teachers_classes_and_students could simply be ClassStudents.

It seems as though you are using the department concept on several different levels.  You should decide where the department belongs and stop referencing it everywhere else.  You've said that a teacher can teach for multiple departments, so we can't put the department in the teachers table.  This leaves Classes and Courses.  Whichever you decide, it need only have a foreign key to the Departments table (unless you decide that one can be in multiple Departments).  This eliminates the Departments_and_teachers table.

Teachers_and_Students could be replaced with a teacher foreign key in the Classes table and students/classes foreign keys in a ClassStudents table.

In short, for the portion of the design you have described, you probably need the following tables:

Departments
Teachers
Students
Courses
Classes
ClassStudents
Terms


No.  Vacuum only runs on the master.  The results of vacuuming are WAL logged and so replicated through to the slave via the normal WAL replay mechanism.


For each record, count records dated earlier in PostgreSQL

You can use the RANK() function for this:

SELECT
    col1, col2, ..., year,
    RANK() OVER (ORDER BY year) - 1
      AS number_of_entries_prior_to_this_year
FROM
    tableX ;

Tested in SQLFiddle.

There is also a more complicated approach:

SELECT
    col1, col2, year,
    COUNT(*) OVER (ORDER BY year) - COUNT(*) OVER (PARTITION BY year)
      AS number_of_entries_prior_to_this_year
FROM
    tableX ;


you need to set up backend PostgreSQL servers for pgpool-II. These servers can be placed within the same host as pgpool-II, or on separate machines. If you decide to place the servers on the same host, different port numbers must be assigned for each server. If the servers are placed on separate machines, they must be configured properly so that they can accept network connections from pgpool-II.

if you will place two servers within the same host as pgpool-II, let's  suppose that you want to assign 5432, 5433 port numbers respectively. To configure pgpool-II, edit pgpool.conf as follows.

backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = 'localhost'
backend_port1 = 5433
backend_weight1 = 1

For backend_hostname, backend_port, backend_weight, set the node's hostname, port number, and ratio for load balancing. At the end of each parameter string, node ID must be specified by adding positive integers starting with 0 (i.e. 0, 1, 2, …).

backend_weight parameters are all 1, meaning that SELECT queries are equally distributed Between two servers.

Starting/Stopping pgpool

$pgpool

The above command, however, prints no log messages because pgpool detaches the terminal. If you want to show pgpool log messages, you pass -n option to pgpool command so pgpool-II is executed as non-daemon process, and the terminal will not be detached.

$ pgpool -n &

The log messages are printed on the terminal, so the recommended options to use the following.

$ pgpool -n -d > /tmp/pgpool.log 2>&1 &

The -d option enables debug messages to be generated.

To stop pgpool, execute the following command.

$ pgpool stop

If any client is still connected, pgpool waits for it to disconnect, and then terminates itself. Run the following command instead if you want to shutdown pgpool forcibly.

$ pgpool -m fast stop

For further your ref Here


He needs to connect with the hostname (or ip address) of your machine (the MySQL server).

Port 3306 needs to be open so he can get to MySQL.

Can't provide step-by-step without knowing OS of both machines, network connection between the machines, client he will be using, etc, etc.


How to grant permissions to allow anything inside a SQL Server schema?

Is this the right approach? Does it have any unintended consequences?
  I am most worried about how ownership chaining may affect this
  approach negatively with dbo being the owner of the schema and the
  role (as well as other schemas/objects in the DB), and the alter
  permission being granted on the schema.

The ALTER permission in the training schema will only grant ALTER permissions to objects in that schema, as stated by Solomon Rutzky in the comments, ownership chaining doesn't apply for DDL, only DML/Execute.

Ownership chaining provides a mechanism for providing limited to access to objects that a user does not have explicit permissions on by chaining the ownership of one object to another. For example, if you have a stored procedure in the training schema that queries a table in the dbo schema and both the procedure and table are owned by dbo then users who have permission to execute the procedure will be able to get the query results from the table they otherwise do not have access to.

This doesn't provide any direct access to the table, the only access to the table is via the stored procedure. This is a method for granting controlled access to an object without needing to expose the underlying object. See this link for more info.

In your specific scenario, to allow users to perform DML/Execute on objects in another schema, you would need a stored procedure/function/view that is owned by dbo and executes the DML/Execute statements against other schemas also owned by dbo. Without this, your users will be prevented from running the commands against schemas/objects outside of training.


I need to find mismatches between tables

Nevermind, I think I got this.

Used subselect to create a TypeA table and a TypeB table, joined on REF# and searched for TypeA.DOC# != TypeB.DOC#


What you are trying to do won't work because pgcrypto's decrypt function is not going to receive the KMS key you encrypted the data with.

Essentially, when you set up KMS it generates a key pair consisting of a public key and a private key. These are generated on a Hardware Security Module (HSM) and the public key is something you can query the KMS api for. The private key remains on the HSM and never leaves it. You can hand data to it for decryption using the api, but you cannot receive the key; the whole point of using KMS is that the private key remains secret, even from you.

What setting up KMS on RDS postgres does get you is that your tablespace and your backups are encrypted. If you also want to do row level encryption over and above the data at rest protections that are provided when you create your database with the --storage-encrypted True flag set; you will need to manage another set of keys for that.

The right way to do that is to generate symmetric encryption keys that are then encrypted with a KMS master key. Your application would use the API to decrypt those keys on start and would retain them in memory, passing them to the database with every query that needed decrypt operations.

Indexing an encrypted column is impossible by definition since the cryptext should appear random to anyone who does not have the key. The query you posted above would be slow even if you had the key to look at the column since it would have to perform the decryption operation for every row. Generally speaking if you are using row level encryption you want to be encrypting dependent attributes not key attributes; since selection, sorting and searching all require access to the plaintext of the values.


ibdata1 is messy to shrink.  For 5.7, see  https://dev.mysql.com/doc/refman/5.7/en/innodb-resize-system-tablespace.html for thorough details on dumping, deleting, and reloading.  That's essentially the only option.

You may as well change innodb_log_file_size (in my.cnf) before the load.

To resize just iblog* --

innodb_fast_shutdown = 0
do graceful shutdown
change innodb_log_file_size
start

I recommend innodb_file_per_table=ON for 'large' tables, at least when doing CREATE or ALTER.  Then maintenance on such tables not bloat ibdata1.  (I prefer OFF for small tables.  Yeah, it is messy to have some tables FPT, some not.)

Note: changing FPT is not a way to shrink ibdata1; ibdata1 only grows.

I recommend not letting free disk space drop below the size of the largest table.


Can I connect to a SQL Server 2000 instance from SQL Server Management Studio 2012?

Yes, I have done it.  My website http://www.rocketclubs.com is on SQL server 2000 and I updated it last night in full windows 8, SSMS 2012 setup.


The following query should get you both the percentile and the value

SELECT * FROM unnest(
  (select array_agg(fraction) from generate_series(0, 1, 0.2) AS fraction),
  (select percentile_cont((select array_agg(s) from generate_series(0, 1, 0.2) as s)) WITHIN GROUP (ORDER BY SIZE) FROM mytable)
);


upgrading SQL Server 2017 Standard to 2019 Enterprise

you are asking two things.

edition upgrade
version upgrade

edition upgrade is when you want to change from standard to enterprise.
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-to-a-different-edition-of-sql-server-setup?view=sql-server-ver15
It is quite easy, mount the sql image and in the menu select maintenance then edition upgrade. Try it on your staging server and go easely.
Version upgrade is when you change sql server version from 2017 to 2019. This is something you have to test carefully because can generate performance regression in query plans or other issues in appplication activities.
MIcrosoft suggest to evaluate it using this model:
https://docs.microsoft.com/it-it/sql/database-engine/install-windows/choose-a-database-engine-upgrade-method?view=sql-server-ver15
I suggest you to upgrade on a new server as a side-to-side migration, starting from DMA:
https://docs.microsoft.com/it-it/sql/database-engine/install-windows/prepare-for-upgrade-by-running-data-migration-assistant?view=sql-server-ver15
Then do some test migration to a new sql server 2019 env end test deeply applications.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly


How to SELECT from three different tables using multiple foreign keys

It's pretty obvious.  You JOIN to languages filter out the null rows because there's no matching to the languages table.  The combination of RIGHT JOIN and LEFT JOIN is bad, but it should work.

    SELECT m.module, m.content
    FROM `modules` m
    LEFT JOIN contents c ON m.id = c.module_id
    LEFT JOIN languages l ON c.language_id = l.id
    WHERE l.tag = 'en-US'


Adding this to the mysqldump will probably work:
 --insert-ignore

The error message implies that you already have a row with primary key id=1 and are trying to add it again.
Caveat:  If you have modified that row, this technique will not achieve the "merge" goal.  Please specify the "merge" more thoroughly.


Specify order of attributes in crosstable

Ok, I might have found an answer myself.
I learned that I can "emulate" the table generated by the second query by this:

$$VALUES ('MR_Type'), ('groundTruth'), ('origin')$$

This way I have control over the order. And if anything changes I have to add it into the AS statement nevertheless.

I don't want to eliminate the possibility to use maybe another special purpose table to control this mechanism, but for now this would be ok.


What backup/restore strategy can I use for the following scenario

You cannot exclude parts of the database from backup and restore in the sense that those parts disappear or just become empty. SQL Server requires that all filegroups are either current or offline. You can bring non-current offline filegroups online by making them current by restoring enough backups.

A database is an inseparable entity when it comes to data and consistency.

If you could empty partitions/tables by not restoring them you could break trusted foreign keys and indexed views.


Optimal query for comparing table column vs. summary column

Test

SELECT account.sfid,
       account.lastactivitydate,
       account.sales_activities_last_30_days__c AS expectedcount,
       COALESCE(subquery.cnt, 0) AS actualcount
FROM salesforce.account
LEFT JOIN ( SELECT task.accountid, COUNT(*) cnt
            FROM salesforce.task
            WHERE task.activitydate >= CURRENT_DATE - INTERVAL '60 days'
            GROUP BY task.accountid
          ) AS subquery ON subquery.accountid = account.sfid;

or

SELECT account.sfid,
       account.lastactivitydate,
       account.sales_activities_last_30_days__c AS expectedcount,
       COUNT(task.accountid) AS actualcount
FROM salesforce.account
LEFT JOIN salesforce.task
    ON task.accountid = account.sfid
   AND task.activitydate >= CURRENT_DATE - INTERVAL '60 days'
GROUP BY account.sfid,
         account.lastactivitydate,
         account.sales_activities_last_30_days__c;


MySQL 5.7 GetDiagnostics is always returning null for SQLSTATE What am I doing wrong?

Replace GET DIAGNOSTICS CONDITION 1

with GET STACKED DIAGNOSTICS CONDITION 1

In my case this helped me.


Does an index improve the querying performance in just two ways?

There's a step in query processing called optimization. This is when the SQL's logical operations (inner join, say) is translated into a physical operation (merge join, or hash join etc.). Each of these physical operations has a different fixed cost and per-row cost (by "cost" I mean time-to-get-results). So one physical operator will have a lower total cost for a few rows and a different one will have a lower total cost for many rows.

How does the optimizer know how many rows to expect and, hence, which physical operator to choose? With each index there will be a histogram summarizing the distribution of data referenced by that index.

John: three rows
Robert: two rows
Steve: two rows
Zach: seventy thousand rows

So we would expect a different query plan when listing Johns than when list Zachs. Choosing the wrong operator will result in a slower query. So indexes improve query performance because statistics are collected for each which allows better choice of physical operators.

Note, even if the associated index is not use in the final execution plan the statistics can still inform the optimizer's choices.

There are a great many index data structures and algorithms. The cannonical one for relatoinal databases is the B-Tree. This has one entry in the leaf nodes per row, with a pointer to the corresponding data row. Your example index on Name would look a bit like this on disk*

John(row4), John(row10), John(row12), Robert(row9), Robert(row20), Steve(row2), Steve(row6)

In a B+-Tree it's the level above the leaves that have the pointers and the leaf nodes are the data rows. So it could be something like:

Non-leaf node:
    John(Page 11 from row 4),
    Robert(Page 11 from row 7),
    Steve(Page 12 from row 1)

Leaf node page 11:
    < three other rows >,
    (John, Blue, Africa, 7),
    (John, Pink, Europe, 12),
    (John, Yellow, Asia, -3),
    (Robert, Red, NULL, 11), ...

Leaf node page 12:
    (Steve, Cyan, Antarctica, 42),
    (Steve, ...

For a hash index the name will be hashed to an offset which will have a pointer to an array of row locations. This is the most like your question, but is still a little different.

There are a great many other index structures. It is a field of active research.

* Except it's a lot more complicated, of course.


variable table and column names in dynamic SQL

Your set @sql statement includes 2 null variables so @sql is null.


Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect

I stumbled upon this question when I myself had encountered this error. I could finally figure out the configuration.

I didn't touch anything in /etc/mysql/my.cnf which already has bind_address = 127.0.0.1. So only localhost can connect.
I use OpenSSH server. So in its config file /etc/ssh/sshd_config I changed from no to yes the param responsible for TCP forwarding, thus AllowTcpForwarding yes.
Finally I have the following entered in MySQL WorkBench.

SSH Hostname: 192.168.0.8:22 (my SSH server listens to port 22)
SSH Username: sshuser
SSH Key File: *C:\Users\windowsuser\.ssh\id_rsa* (should be private key, even though it says public)
MySQL Hostname: 127.0.0.1 (this should not be changed, since MySQL server by default is bound to localhost only which I didn't change)
MySQL Server Port: 3306 (also default)
Username: root

The only remaining thing for you is to configure correctly your SSH server to work with keys rather than passwords. Hope this will help someone.


"Oneway" Replication from Oracle DB to TimesTen

It is possible (but not simple).
I just made the implementation of ORACLE to TimesTen real time replication.
The solution is based on ORACLE LogMiner utility. Using this tool you can generate the ORACLE transactions in a real time and then ship them to the program (TimesTen side) which will read and apply those transactions to the TimesTen database. The synchronization point is provided between ORACLE and TimesTen by another intermediate ORACLE database. This is very general description, but I can share with you the details if you wish...


Is an MDF "Last Modified Date" affected by transaction log backup?

My understanding is that the 'Last Modified Date' of the MDF file (in Windows) is only changed when the Database is Closed/Reopened or data is written to the MDF file causing it to grow (eg. by a Full Backup or Transaction Log Backup, assuming database recovery mode is full)

You are correct for the first part but why would full backup or log backup change "Last Modified" time of mdf file. Its actually reading from the data file its not making any changes. Normally in daily operation SQL Server reads quite often from data file but for every physical read it is not going to update the time stamp or modifiy the date that would be unnecessary.

Moreover the way SQL Server access the data and log file are not that straight it uses different mechanism so I guess its useless worrying about modified date.

  What if there was no write activity captured in the log during that hour? Would it not affect the .mdf modified date?

NO it would not

  And if this is the case, would the modified date for readonly databases (databases that are defunct for write purposes but still used for read purposes) ever change?

Well it can change when database is taken offline and brought online and after service restart


Without changes to application means you would have to do it at "the network layer". With that I mean things like DNS, rename machine etc.
If you can modify the connection string, then you can try below connection attribute. It is really meant for database mirroring, so you would have to try if it makes work in other situations:
Failover Partner=myMirrorServerAddress;

And, of course, make sure you handle all the other things that you have to handle when you move a database from one instance to another, like scheduled jobs, logins, linked servers etc. I.e., everything that is stored outside the database and is required for the system/application to be functioning.


How should I model a database structure to retain calculation results?

If you don't know how many columns it will have, it may vary all the way up to 250, and the entries might be sparse, I would:

Use the EAV design to store the calculation types on separate rows.
For reporting/analysis purposes, create a view or summary table that pivots rows to columns and "flattens" them out.

One day of data you could probably get fast with indexes. Three years, likely not. For the second case, you might want to look into something more analytically-focused, like Greenplum (on-prem) or Redshift (AWS).


Secondary index creation on innodb has been much faster since MySQL 5.1 + InnoDB plugin or MySQL 5.5. In fact, we refer to this feature as "fast index creation". If we don't touch the primary key, it only creates an additional structure and it only has to read the current values, without modifying the table data. Assuming you have enough memory, if the data pages are in memory and the index also fits into memory- this will be a memory-only operation, so potentially very fast. But the index will be ready to be used as soon as the process ends. In the background (after you get control back to the console), it will write the new pages to disk whenever it can. It is also dependent on how much data fragmentation you have in that particular tablespace. None of this can lead to losing data due to the transaction log (which is on disk).

But you are completely right by saying that some changes on secondary indexes are done in the background: it is called Change buffer, but it is used when modifying data records.

I must say that in my experience, InnoDB index creation is not as fast as it could :-), specially on very busy servers. However, starting with 5.6, creating secondary indexes is completely online (writers to that table are not blocked), which is a very nice improvement indeed.


Assign most elevated permission to Sql Server DB

Assuming this is a Windows Auth only instance (with no sa access), try using psexec per here.

An actual backdoor into SQL Server does exist that does not require restarting and/or rebooting anything into single-user mode. I have done this on systems where I did not have access but needed to check stuff.

Download PSexec tools from here. Place this on the server and then in a command prompt execute this command:psexec -i -s SSMS.exe, or sqlwb.exe

This will open up SSMS as the system account that has sysadmin access to the instance of SQL Server. This is done during installation of SQL Server, however I have heard that this will not be so with SQL 2012.


sql server log shipping: how to monitor that the replica is fresh? (e.g. that log shipping is working)

a) once a day, telling me: "Data as of is ### minutes old"

You have to  get the status using  master..sp_help_log_shipping_monitor to get the status. There are many scripts available that will send out HTML email through SQL Agent job like

Get an overview report on your log shipping setups
Logshipping status report Throgh Mail
Automating SQL Server Transactional Log Shipping Alerts

  b) any time data is older than N minutes (indicating log shipping has gone down)

The job LSAlert_XX where XX is the instance name, run a job  exec sys.sp_check_log_shipping_monitor_alert to check if the restore of the log has fallen behind N mins. You can edit/configure it to send out emails in case where it has fallen behind N mins.


SQL Server equivalent to functionality of Oracle RAC?

No, nothing in SQL Server can give you the same thing out of the box.

Currently, the only SQL Server technology that allows simultaneous writes at multiple nodes is Peer-to-Peer replication. Note that I did not to say "write scale-out," because it works in a way such that the entire system has the write capacity of only a single node. The introductory paragraph of that page is worded carefully to include the term "scale-out" without saying "write scale-out." Yay for marketing-speak.

From what I've read, Oracle RAC is the same in that respect. Functionally, the only thing that's missing from SQL Server in this configuration is a load-balancing solution, which is both an advantage (you can implement it any way you want) and a disadvantage (it's not in the box or supported by Microsoft) when comparing against competing products.

Conversely, Oracle RAC does not give you the same thing as SQL Server out of the box either. For example, RAC is recommended to be implemented with nodes within 100km of each other because of realtime network latency, whereas Peer-to-Peer replication has no such restriction. (I'm not saying one solution is better than the other: I'm just pointing out that they are different. The business should choose a solution that best fits their specific needs.)


Ways to Reduce Database Size and possibly improve performace. Untouched enviroment

Zerothly, you need to test your backups and have a recovery plan if your not doing that.

Let's talk about some quick win performance first of all. Here are some options. Google can help flesh these out more.

Are their multiple named instances on the box?
Have you set max memory?
Have you set the number of files for tempdb?
If it's at all possible, put the data files and log files (usually with the extensions  MDF and LDF respectively) on different physical disks. Not just logical drive letters on the same disk drive.
Determine your index fragmentation levels.
Maintain your indexes. You have two good free options:

Ola Hallengren's solution is something I like on servers I have to maintain because it has a very verbose full manual mode for running from SSMS. It can also be automated.
Minion Reindex is going to scale better for you actual automated reindexing.

This will require reformatting the drive with the SQL Server databases, but setting your disk partitions correctly is important.
Enable Instant File Initialization.
Set your file growth options sensibly.

Space-wise:

If you delete data or indexes from the database, the MDFs don't shrink. You have to shrink them. Shrinking is generally bad, but on rare ocassions it makes sense.
Are you doing FULL or SIMPLE recovery mode? If Simple, do your clients not need point in time data? If your doing full recovery are you properly managing your transaction log backups?
What are you options for archiving data, dropping unused indexes and altering schema?


You can specify the port SQL Server listens on for each IP address it is listening on, but not per database.  To change an existing installation of SQL Server, run SQL Server Configuration Manager, expand SQL Server Network Configuration, and select Protocols for MSSQLSERVER in the left pane.  Then right-click on TCP/IP in the right pane and select Properties.  On the IP Addresses tab, change the TCP Port for each address as desired.

You will need to restart the service for the change to take effect.

Most SQL Server clients will use port 1433 by default, so if you use a different port, clients will have to be configured to designate the port SQL Server is listening on.

One of the issues that can occur with specifying an alternate port is that another application is already using that port, or RPC may grab that port before SQL Server starts up.  Google "rpc dynamic port range" to research that.  So if you configure SQL Server to use a port that is in the range that RPC uses, SQL Server may start one time, but not the next.  Use the netstat command to see what ports are in use on the server, and review documentation of installed applications to ensure you don't select a port that your applications are using.  Just because you don't see it in use in netstat doesn't mean the application doesn't use it itermittently--not likely, but possible.

Also, firewall rules on client, server, and network devices that allow port 1433 may need to be modified or added to so that the port of your choice will not be blocked.


Old oracle home is no more useful after upgrade.
To safe remove it you should do either:

Start > Installation Products > Oracle Universal Installer.

from here you can select deinstall and remove the old oracle home.
One alternative is to manually perform the process running the command:

setup.exe -silent -detachHome ORACLE_HOME="C:\product\DBHome" ORACLE_HOME_NAME="DBHome"

You have to replace ORACLE_HOME_NAME and ORACLE_HOME with the one on your system. To find them, in Windows, first get the value of

\\HKEY_LOCAL_MACHINE\\Software\Oracle\inst_loc.

in your windows registry. Then navigate in the inventory location and open the file:

inventory.xml

here you'll find some lines like:

<HOME NAME="OUIHome1" LOC="C:\oracle\product\10.2.0\" TYPE="O" IDX="4" />

This is your line. Location is the one you know, write down the name and perform the command. When oracle home is detached you can manually delete it from windows explorer.

IN addition, manual delete without detaching does not cause any problem but leave some annoying pointer in the inventory


regexp_replace() seems ideally suited

SELECT REXEXP_REPLACE(column, $$<img src=[^>]*>$$, ' ','g') FROM table


Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL

You can get list of invalid indexes.

SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;

If you see your index in this query it means the index won't work and you have to recreate index.

Don't do REINDEX. It won’t re-create the index concurrently, it will lock the table for writes while the index is being created, the best solution is to drop the invalid index and recreate it with CONCURRENTLY flag


What is the proper setup for PG user for web application

The best way, really, is for the web app to have no right to create or modify tables at all. It should only have the rights to do what it needs to do to perform its day to day functions, not administration.

In particular, the web app must not run as a superuser and should preferably not own the database or tables.

If the webapp has built-in administration functions for changing tables when it's upgraded, etc, it should do so via a separate set of credentials and allow you to be prompted to enter those credentials each time you perform an upgrade. Or let you create the file with the admin credentials, run the upgrade, and delete it again.

If you can't fix the webapp to use different credentials for schema changes, then it might as well be the owner of the database and tables. Just don't make it a superuser!

If you can fix the app so it uses different credentials for schema changes, then its regular non-schema-change user should not own the tables or database. It should have only CONNECT and TEMPORARY rights on the database, USAGE rights on the schema(s), and the required rights on each table GRANTed to it. Just don't forget to REVOKE ALL FROM public ON DATABASE mydb; and REVOKE ALL FROM public ON SCHEMA public;.


How does objectproperty() figure out if a function is deterministic in sql server?

First it looks whether used functions are deterministic.
Then it looks whether your function is schema bound.
(Or in reverse order.)

So add with schemabinding to your function.


delete var/lib/mysql folders after dropping database via mysql

First, MySQL latest version is 8.x.x, so it's impossible for you to have 14.x, check that.
Second, maybe your data dir is not /var/lib/mysql. To check this variable run the following script and check the actual datadir for your instance.
mysql> SHOW VARIABLES LIKE '%dir%';
> +----------------------------+--------------------------------------------+
> | Variable_name              | Value                                      |
> +----------------------------+--------------------------------------------+
> | basedir                    | /usr/local/mysql-max-4.0.13-pc-linux-i686/ |
> | datadir                    | /usr/local/mysql/data/                     |

Also check for any errors when you run the DROP DATABASE sentence.


Relating 2 tables with possible wildcards?

Tables

DECLARE @RateCategory AS table
(
    RateCategoryID integer PRIMARY KEY NONCLUSTERED,
    Managed bit NOT NULL,
    Customer varchar(10) NULL,
    Category char(1) NULL,

    UNIQUE CLUSTERED (Managed, Category, Customer)
);

DECLARE @Job AS table
(
    JobNumber integer PRIMARY KEY,
    Managed bit NOT NULL,
    Customer varchar(10) NOT NULL,
    Category char(1) NOT NULL
);

Sample Data

INSERT @RateCategory
    (RateCategoryID, Managed, Customer, Category)
VALUES
    (10, 0, NULL, NULL),
    (20, 0, NULL, 'A'),
    (30, 0, 'CustC', 'A'),
    (40, 1, 'CustC', 'A'),
    (50, 1, NULL, 'A'),
    (60, 0, NULL, 'B');

INSERT @Job
    (JobNumber, Managed, Customer, Category)
VALUES
    (1, 0, 'CustA', 'C'),
    (2, 0, 'CustB', 'D'),
    (3, 0, 'CustC', 'A'),
    (4, 1, 'CustC', 'A'),
    (5, 0, 'CustD', 'A'),
    (6, 0, 'CustE', 'B'),
    (7, 1, 'CustF', 'A');

Solution

The logic here is to find a match on:

Managed, Category, and Customer
Managed and Category
Managed alone

Each step only occurs per job if the previous step did not find a match. The initial CROSS APPLY ensures a row is only returned if one of the steps found a match.

SELECT
    J.JobNumber,
    RC.RateCategoryID
FROM @Job AS J
CROSS APPLY
(
    SELECT
        RateCategoryID =
            COALESCE
            (
                -- Preferred order of matches
                MatchAll.RateCategoryID,
                Match2.RateCategoryID,
                Match1.RateCategoryID
            )
    FROM
    (
        SELECT NULL
    ) AS Dummy (NotUsed)
    OUTER APPLY
    (
        -- Complete match
        SELECT TOP (1)
            RC.RateCategoryID
        FROM  @RateCategory AS RC
        WHERE
            -- Conditions for this step
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer = J.Customer
    ) AS MatchAll
    OUTER APPLY
    (
        -- Match Managed and Category
        SELECT TOP (1)
            RC.RateCategoryID
        FROM  @RateCategory AS RC
        WHERE
            -- No previous match
            MatchAll.RateCategoryID IS NULL
            -- Conditions for this step
            AND RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer IS NULL
    ) AS Match2
    OUTER APPLY
    (
        -- Match Managed only
        SELECT TOP (1)
            RC.RateCategoryID
        FROM  @RateCategory AS RC
        WHERE
            -- No previous match
            MatchAll.RateCategoryID IS NULL
            AND Match2.RateCategoryID IS NULL
            -- Conditions for this step
            AND RC.Managed = J.Managed
            AND RC.Category IS NULL
            AND RC.Customer IS NULL
    ) AS Match1
) AS RC;

Results

Demo

Stack Exchange Data Explorer

Execution Plan

Note the Filter operators have startup predicates. This means a seek is only performed if the previous seek did not find a row.

Alternative

SELECT
    J.JobNumber,
    RC.RateCategoryID
FROM @Job AS J
CROSS APPLY
(
    -- Just the highest priority match per job
    SELECT TOP (1)
        Match.RateCategoryID
    FROM
    (
        -- Priority 1: Match Managed, Category, and Customer
        SELECT
            RC.RateCategoryID,
            [Priority] = 1
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer = J.Customer

        UNION ALL

        -- Priority 2: Match Managed and Category
        SELECT
            RC.RateCategoryID,
            [Priority] = 2
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer IS NULL

        UNION ALL

        -- Priority 3: Match Managed
        SELECT
            RC.RateCategoryID,
            [Priority] = 3
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category IS NULL
            AND RC.Customer IS NULL
    ) AS Match
    ORDER BY
        Match.[Priority] ASC
) AS RC;


Best way of aggregating, storing and using data in SQL Server (triggers, scheduled jobs, SSAS?)

The aggregate table can probably stay in the same DB. You can however create it on a separate filegroup and disk.

Full update of a new aggregate table

One way of doing it using pure SQL and your sample:

WITH list ([Type], [FeatureId], [Measure], [Value], [Timestamp], [ID]) as (
    SELECT *
        , ROW_NUMBER() OVER(PARTITION BY [FeatureId] ORDER BY [Measure])
    FROM (
        SELECT 0, [FeatureId], [MeasureFrom], [Value], [Timestamp] FROM data
        UNION ALL
        SELECT 1, [FeatureId], [MeasureTo], [Value], [Timestamp] FROM data
    ) l([Type], [FeatureId], [Measure], [Value], [Timestamp])
)
SELECT l1.FeatureId, [MeasureFrom] = l1.Measure, [MeasureTo] = l2.Measure
    , [Value] = CASE WHEN l1.Type = 0 THEN l1.Value ELSE l2.Value END
    , [Timestamp] = CASE WHEN l1.Type = 0 THEN l1.Timestamp ELSE l2.Timestamp END
FROM list l1
INNER JOIN list l2 ON l1.FeatureId = l2.FeatureId AND l1.ID+1 = l2.ID
;

With this really small sample, I am not sure it covers all your needs. It may help to add a bigger sample with more data.

It does 4 table scans. Since you have 250k rows, it may not perform so well. It will probablibly better to run it in batch of X consecutive FeatureIds.

This could be done using a job and a SSIS package with either a single update or batch update. You would have to truncate the aggregate table first.

Trigger on each new row in main table

For new rows, using a trigger, this query could be used:

WITH new([FeatureId], [MeasureFrom], [MeasureTo], [Value], [Timestamp]) as (
--    SELECT 1, 1, 20, 2, '2015-01-01'
--  SELECT 1, 5, 15, 3, '2015-01-02'
    SELECT 1, 9, 10, 8, '2015-01-03'
), gap([FeatureId], mn, mx) as (
    SELECT n.[FeatureId], mn.mn, mx.mx
    FROM new n
    CROSS APPLY (SELECT mn = MAX([MeasureFrom]) FROM data3 WHERE [FeatureId] = n.[FeatureId] AND [MeasureFrom] < n.MeasureFrom) mn
    CROSS APPLY (SELECT mx = MIN([MeasureTo]) FROM data3 WHERE [FeatureId] = n.[FeatureId] AND [MeasureTo] > n.MeasureTo) mx
), list (x,[FeatureId], [Measure], [Value], [Timestamp], [ID]) as (
    SELECT *
        , ROW_NUMBER() OVER(PARTITION BY [FeatureId] ORDER BY [Measure])
    FROM (
        SELECT 0, d.[FeatureId], d.[MeasureFrom], d.[Value], d.[Timestamp]
        FROM data3 d
        INNER JOIN gap g ON d.[FeatureId] = g.[FeatureId] AND (d.MeasureFrom >= g.mn AND d.MeasureFrom < g.mx)
        UNION ALL
        SELECT 1, d.[FeatureId], d.[MeasureTo], d.[Value], d.[Timestamp]
        FROM data3 d
        INNER JOIN gap g ON d.[FeatureId] = g.[FeatureId] AND d.MeasureTo = g.mx
        UNION ALL
        SELECT 2, [FeatureId], [MeasureFrom], [Value], [Timestamp] FROM new
        UNION ALL
        SELECT 3, [FeatureId], [MeasureTo], [Value], [Timestamp] FROM new
    ) l(x, [FeatureId], [Measure], [Value], [Timestamp])
)
MERGE data3 AS target
USING (
    SELECT l1.FeatureId, [MeasureFrom] = l1.Measure, [MeasureTo] = l2.Measure
        , [Value] = COALESCE(d.[Value], l1.[Value])
        , [Timestamp] = COALESCE(d.[Timestamp], l1.[Timestamp])
    FROM list l1
    INNER JOIN list l2 ON l1.FeatureId = l2.FeatureId AND l1.ID+1 = l2.ID
    LEFT JOIN data3 d ON d.MeasureFrom = l1.Measure OR d.MeasureTo = l2.Measure
) as source(FeatureId, [MeasureFrom], [MeasureTo], [Value], [Timestamp])
ON (target.FeatureId = source.FeatureId AND target.[MeasureFrom] = source.[MeasureFrom])
WHEN MATCHED THEN
    UPDATE SET target.[MeasureTo] = source.[MeasureTo]
WHEN NOT MATCHED BY target THEN
    INSERT (FeatureId, [MeasureFrom], [MeasureTo], [Value], [Timestamp])
    VALUES (source.FeatureId, source.[MeasureFrom], source.[MeasureTo], source.[Value], source.[Timestamp])
;

You must replace the new CTE by the values of the new inserted row in the trigger and use it to merge with the aggregate table.

Scheduled update of newly added rows

If you can find a way to get a list of all newly added rows to the main table since the last update of the aggregate table, you could schedule a job every x minutes or hours and only update what is necessary based on what has been recently added.

The trigger query will work as well with scheduled updates.

This can be run as a schedueled job and within a SSIS package.


Should Postgres function return/coerce numeric scale/precision?

RETURNS TABLE(m numeric(20,10), n numeric(20,10)) does not enforce the data types of the returned columns; it's simply a declaration, metadata intended to inform the callers what the function should return. The declaration cannot not require anything, it's a "promise" that is to be fulfilled by the function implementation. The function code itself is responsible for what it returns. In other words, you must use explicit type casts, as necessary, in the function body.
If the function doesn't fulfil the promise, it's an implementation bug.

P.S.
Looking at the source code of coerce_type, it simply compares type OIDs, so any numeric is compatible with any other numeric:

    if (targetTypeId == inputTypeId ||
        node == NULL)
    {
        /* no conversion needed */
        return node;
    }


Single node availability group - is it possible?

To specifically answer your question, yes you can create an availability group on a single node cluster.  I tested this with SQL Server 2017, and you can create an AG on a single node cluster using the "New Availability Group" context menu item in SSMS, or with the raw T-SQL.  However, you cannot create one using the "New Availability Group Wizard" item as it requires a second replica to be added to progress through the dialogs.

Based on your comments, I would expect that a two-node cluster with asynchronous replication and manual failover is a better solution.  There are no real benefits to using a distributed AG in this situation, and the benefit of a regular AG is that you can fail over and fail back as needed.  With a distributed AG, failover is possible, but failback is not.


Multiple THEN outputs on a PostgresQL query

A single case expression can only return a single value (=expression). So yes, you are right you can't do that. If you want to have two output columns, you need two case expressions.

SELECT CASE
         WHEN t.page ~* '...' THEN 'Product here'
         ... other outputs ....
        END AS page_type,
        CASE
         WHEN t.page ~* '...' THEN 'something else'
         ... other outputs ....
        END AS something _else
FROM ...

If you don't want to repeat the condition, you could wrap that into a derived table:

SELECT node, area, page_type,
       CASE
          WHEN page_type = 'ABC' THEN 'Product'
          WHEN page_type = 'DEF' THEN 'Something else'
          ELSE ''
       END AS "Node_Type"
from (
    SELECT area,
           Regexp_replace(t."page", '...', '\1\2\3\4\5\6') as node,
           CASE
             WHEN t.page ~* '...' THEN 'ABC'
             WHEN t.page ~* 'filter' THEN 'DEF'
             ELSE 'Other'
           END AS page_type,
           string_to_array(trim(substr(page, 1, strpos(page, '?')-1),'/'), '/') as levels,
           regexp_replace(page, '(^.*\?filter=)([0-9]+)', '\2', 'g') as filter_value
           length(t.page) as string_length
    FROM "V_2019-06-10_to_2019-06-20_GSC"
) t;

The expression string_to_array(trim(substr(page, 1, strpos(page, '?')-1),'/'), '/') will return the individual elements (levels) of the URL as array elements. '/level1/level2/level3/?filter=123' will yield and array {level1,level2,level3) and '/level1/level2/level3/level4/?filter=123' will return 4 elements. If you need to access the number of levels in the outer select, you can use e.g. cardinality(levels)

The expression regexp_replace(page, '(^.*\?filter=)([0-9]+)', '\2', 'g') will return the value of the parameter filter as a single expression (It's probably not 100% fool-proof, but URL parsing with regex is not a simple task).


Non-Clustered Index key columns to cover a variable where clause

That's a great question.
And there are good answers.

The engine definitely will use the index even if you don't use every key column.
That's especially so if they are in order, as you are talking about.
(can anyone else speak to different orders of key columns?)

You will benefit just fine from selecting just on the first column alone as a key, or multiple columns.
What will make a difference - for any index - is staying inside the INCLUDEd columns.
No matter how many key columns you use in your Where, the performance hit for having to go back to the primary key for additional columns can be huge as it doubles the "operations".

When it comes to dealing with performance vs. size, you have the same problem as with any index.
Since you know you want the same columns returned in all cases, if you are READ focused, you will probably want to the index with all 6, if you INCLUDE everything.
It will certainly save you db size compared to making both indexes.

On WRITE, you obviously have a bigger burden with a larger index.  That is a significant additional amount of sorting.
If you do just one row inserted at a time, maybe it won't hardly matter at all.
If you do bulk inserts, you'll definitely want to test the two indexes to see the write performance for your actual inserts.


You know what they say about the word assume

I assumed the config dir and data dir was the same.

Answer 1:

Change your command to this and it will work.

sudo -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \
-b /usr/lib/postgresql/9.6/bin -B /usr/lib/postgresql/10/bin \
-d /var/lib/postgresql/9.6/main/ -D /var/lib/postgresql/10/main/
-o 'config_file=/etc/postgresql/9.6/main/postgresql.conf' \
-O 'config_file=/etc/postgresql/10/main/postgresql.conf'

we can provide the special config file location as an option

Also, I believe you have identified a default install issue or the documentation needs to be updated

The default install should create the config files in the datadir

Answer 2:

Since this is play, try this too:

sudo -u postgres pg_dropcluster 10 main
sudo -u postgres pg_upgradecluster 9.6 main

Keeping this, since it might help someone else.

The empty databases seem to have not been created yet. Or are in a different directory. That's why postgresql.conf is not found in the error message.

ls -l /var/lib/postgresql/9.6/main/postgresql.conf /var/lib/postgresql/10/main/postgresql.conf

should show two files.

The equivalent initdb command for both versions should be run

pg_ctl -D /var/lib/postgresql/9.6/main initdb

pg_ctl -D /var/lib/postgresql/10/main initdb


Resolving issue with mysql.proc after upgrading MySQL from 5.0.* to 5.1.*

I was having this issue, and thought I'd mention that running "mysql_upgrade --force" fixed it for me.

I upgraded from 5.0 to 5.1 to 5.5.  The interesting this is that I had already run mysql_upgrade which is why I needed to add the --force option.  Hope this helps someone.


Query to find rows containing ASCII characters in a given range

Why is the accepted answer not working for char(150)?

Actually, it does. The problem is your test is bad / invalid. You test column, DataColumn, is using NVARCHAR instead of VARCHAR. The character itself works in both datatypes, but the behavior is different due to how it is being used in each case:

In the Find_Invalid_Chars() function (i.e. the "other" answer), the string is being converted back into VARCHAR since that is the datatype of the input parameter for that function. In this case it works as expected (although I believe it can be done much more efficiently than that loop, but that's for another time ;-)
In the LIKE query (i.e. the "accepted" answer), the expanded and concatenated result of '%[' + CHAR(127)+ '-' +CHAR(255)+']%' is actually converted into NVARCHAR since that is the datatype of the column it is being compared to (and NVARCHAR has a higher datatype precedence), hence that LIKE function is not behaving as expected: either the CHAR(255) character maps to a different code point, and/or the CHAR(150) character in the column itself maps to a different code point (the CHAR(127) character does not change as it's in the standard ASCII range). In either case, the conversion to NVARCHAR is causing the numerical value of the "En Dash" character ("–") to no longer be within that range. Meaning, the LIKE function is looking for values, y, between 127 and x (where x >= 128), and y for the "En Dash" character is now > x. Whereas in VARCHAR, x = 255 and y = 150.

The quick fix to see that it does work is simply to change the NVARCHAR datatype of the  DataColumn column to be VARCHAR (yes, just remove the initial "N"), then re-build the schema, then execute, and the LIKE query will behave as expected.
The following might help explain why making the test column NVARCHAR caused the LIKE query to not match the row:
SELECT UNICODE(CHAR(127)) AS [CHAR(127)],
       UNICODE(CHAR(150)) AS [CHAR(150)],
       UNICODE(CHAR(255)) AS [CHAR(255)];

/*
CHAR(127)     CHAR(150)     CHAR(255)
127           8211          255
*/

As you can see in the results below the query, the "bad dash", which was CHAR(150) became NCHAR(8211) when stored in the NVARCHAR column. And, since that predicate is using a binary collation (generally the correct thing to do in this scenario), it was looking at the code points / values, not the characters. Hence, the LIKE clause was looking for characters with values between 127 and 255, and 8211 is typically not in that range ;-).
P.S. Please keep in mind that the function CHAR(150) can return different characters, or even NULL, based on the default collation of the database in which you execute that function. This is because VARCHAR data is based on code pages, and those are determined by the collation, and the collation being used when executing the CHAR() function is the default collation of the active / current database. This affects values 128 - 255. Values 0 - 127 will always return the same characters, regardless of collation, as those are the standard ASCII character set and are the same across all of the code pages supported in SQL Server (though not in all code pages in general).
P.P.S. ALSO, I just noticed a slight difference in logic between the function and the query (i.e. the two answers from the linked question): CHAR(127) is considered good / valid in the Find_Invalid_Chars() function, yet it's considered bad / invalid in the LIKE query. If it were me, I would consider CHAR(127) valid as it's part of the standard ASCII character set. But, you need to decide what you consider it. Just be aware of that difference in case you do need to adjust the LIKE syntax a little.

Given:

The purpose of the query is to find characters in the source table/column that are not handled correctly by some software applications.

and:

The data can be in either VARCHAR or NVARCHAR.

I would say that:

You don't want to convert NVARCHAR source data into VARCHAR as there might be "best fit" mappings that translate invalid source characters into valid characters, but one or more of your software applications might not use "best fit" mappings.
SELECT NCHAR(178) AS [Unicode], -- Superscript 2 (U+00B2)
       CONVERT(VARCHAR(5), NCHAR(178)
                   COLLATE SQL_Latin1_General_CP1_CI_AS) AS [CodePage-1252],
       CONVERT(VARCHAR(5), NCHAR(178)
                   COLLATE Turkmen_100_CI_AS) AS [CodePage-1250]

/*
Unicode    CodePage-1252    CodePage-1250
²          ²                2
*/

It will likely be more reliable to look for characters not in a specific "valid" range as opposed to those in a specific invalid range, especially when dealing with NVARCHAR which holds a lot more than 256 characters.

You could get away with a single query if the "valid" range is always between values 0 and 127 (since those values are the same in both cases). But if you need to specify values above 127, then you will need one query for VARCHAR and one for NVARCHAR.

All that being said:

The following query returns rows containing at least one character that is not in the range of 0 - 127, for both VARCHAR and NVARCHAR. But, it only works with NVARCHAR columns for values above 127.
SELECT *
FROM   (VALUES (NCHAR(178)), (NCHAR(8211)), (N''), (NULL), (N'xy' + NCHAR(165)),
           (N'AA'), (N'mM' + NCHAR(999) + N'Nn'), (N'#!~()')) tmp(TestValue)
WHERE  tmp.[TestValue] LIKE N'%[^' + NCHAR(0) + N'-' + NCHAR(127)
          + N']%' COLLATE Latin1_General_100_BIN2;

/*
TestValue
²
–
xy¥
mMϧNn
*/

The following query also returns rows containing at least one character that is not in the range of 0 - 127, but only works for VARCHAR columns. However, it does allow using values between 128 and 255.
SELECT *
FROM   (VALUES (CHAR(178)), (CHAR(150)), (''), (NULL), ('AA'), ('#!~()'),
        ('xy' + CONVERT(VARCHAR(5), NCHAR(165) COLLATE Latin1_General_100_BIN2)),
        ('mM' + CONVERT(VARCHAR(5), NCHAR(199) COLLATE Latin1_General_100_BIN2) + 'Nn')
       ) tmp(TestValue)
WHERE  tmp.[TestValue] LIKE '%[^' + CHAR(0) + '-' + CHAR(127)
          + ']%' COLLATE Latin1_General_100_BIN2;

/*
TestValue
²
–
xy¥
mMÇNn
*/

Regarding:

The applications are interpreting the source correctly, but sometimes have issues with characters outside "standard" ranges, though the ranges vary by application.

Not sure I understand how there can be "issues" with some characters if the app is interpreting the source data correctly, unless you meant that they "mostly" interpret the data correctly.
The ranges varying by application sound like this might require more detailed investigation than can be done in a simple Q & A format such as this. This behavior can be due to them using different drivers to connect (ODBC / OLEDB / etc), what language they are written in, what assumptions they are making about the data they are getting, and so on. Some problems might be fixable with a configuration (no code change) of the app, some might be fixable only with a code change, etc.