How to connect use INSERT query using SELECT query in mySQL

if columns same:

INSERT INTO cce_activity select * from cce_sa_fa where id NOT IN (select safa from cce_activity)

if not, something like:

INSERT INTO cce_activity (col1, col2, col3) select col1, col2, col3 from cce_sa_fa where id NOT IN (select safa from cce_activity)


"SQL command not properly ended" with trigger

(Edited following comments and after checking what INSERTED is in SQL Server)
The INSERTED table in SQL Server represents the rows that were inserted by the triggering statement. Oracle doesn't have this. Instead, a row-level trigger can refer to the column values of each row affected by the trigger, using :new and :old values. (Only :new values are populated for inserts, and only :old for deletes.) Therefore I think what you need is something like this:
create or replace trigger create_base_invoice
after insert on reservation
for each row
begin
    insert into invoice
         ( resnum
         , bnum
         , rnum
         , cfname
         , clname
         , bstartdate)
    select :new.resnum
         , :new.bnum
         , :new.rnum
         , c.cfname
         , c.clname
         , :new.bstartdate
    from   customer c
    where  c.cnum = :new.cnum;
end create_base_invoice;

This creates an invoice for each new reservation, including details looked up from the customer table.

I do not understand the point of the SELECT command

There are two flavours of insert:
insert into tablename (col1, col2, col3) values (val1, val2, col3)

and
insert into tablename (col1, col2, col3)
select t2.col1, t2.col2, t2.col3
from   othertable t2
where  ...

One is for inserting fixed values, the other is for loading the results from a query.


Export a SQL Server 2000 database to Server 2012

Since your source server is SQL Server 2000, you can't restore directly to SQL Server 2012. You will need to restore to another instance first (2005, 2008, or 2008 R2).

On 2000:

BACKUP DATABASE dbname TO DISK = 'D:\backups\db.bak' WITH INIT;

On 2005 or 2008 or 2008 R2:

RESTORE DATABASE dbname FROM DISK = 'D:\restores\db.bak'
  WITH REPLACE, RECOVERY, MOVE (...you will likely have to specify new file locations...);

Then from there, back it up again, and then restore on the 2012 server.

Once you've restored, you'll probably want to adjust the compatibility level:

ALTER DATABASE dbname SET COMPATIBILITY_LEVEL = 110;

As well as update statistics. But note that 80 compatibility will automatically be upgraded to 90 (and you will have issues if you use syntax that is no longer supported, e.g. *= joins). While it won't catch everything, you may want to run the 2012 Upgrade Advisor while the database is in its temporary home on a 2005, 2008 or 2008 R2 instance to catch any potential issues. Also see this answer for ways the change in compatibility level may affect your existing code (the upgrade advisor won't catch many of those).


How to set the deadlock priority for a maintenance task created trough the graphical interface

Try adding an Execute T-SQL Statement Task at the beginning of your maintenance plan

With the following statement in it :

SET DEADLOCK PRIORITY NORMAL


Analytics functions to the rescue

In particular, lead/lag will be your friend.

SELECT
   LAG(ACTION_DATE) OVER (PARTITION BY PERSON ORDER BY ACTION_DATE ASC)  PREVIOUS_ACTION_DATE
,  LAG(VALUE) OVER (PARTITION BY PERSON ORDER BY ACTION_DATE ASC)  PREVIOUS_VALUE
,  PERSON
,  ACTION_DATE
,  VALUE
FROM
    SomeTabe

SQLFiddle


Shrinking a SQL Server 2008 Standard database to move it into SQL Server 2014 Express

Normally the thing holding my data files back from being shrunk is that a CHECKPOINT is needed.  I have had to do several iterations of:

CHECKPOINT;
GO
DBCC SHRINKFILE(...)
GO

Before the file would finally shrink.

If the data files still doesn't shrink, try the troublshooting steps here to confirm that you really have the free space that you think you have.  Here's the query from the docs link:

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;


What about ALTER TABLE RENAME followed by CREATE TABLE?


You should use one table per object of interest.  That means one table for users, one table for pages, one table for posts, etc.  Use a normalized database (See database normalization) for transactional data.  This is precisely what relational database management systems are built to do.  Don't presume you are going to have performance problems because you have many rows.  Most systems perform better with many rows than with just a few.


How to Pivot Multiple Columns in SQL Server

Something like:

select hour_of_day,
       avg( case when day_of_week = 2 then item_count else null end ) Mondays,
       avg( case when day_of_week = 3 then item_count else null end ) Tuesdays,
       avg( case when day_of_week = 4 then item_count else null end ) Wednesdays,
       avg( case when day_of_week = 5 then item_count else null end ) Thursdays,
       avg( case when day_of_week = 6 then item_count else null end ) Fridays,
       avg( case when day_of_week = 7 then item_count else null end ) Saturdays,
       avg( case when day_of_week = 1 then item_count else null end ) Sundays
where ...
group by hour_of_day


T-SQL Query to retrieve the last full backup (Including split and copy only), Size and Location

For copy only backups, just remove this condition in the WHERE clause:
AND A.is_copy_only = 0

By "split backup", I assume you mean that you have something that is using VSS to create a snapshot backup? Are you 100% certain that this isn't in the result? I don't have such a backuphistory to play with at the moment, but I suggest you start by doing SELECT from backupset and see if those backups are present. If they are, it should be pretty easy to adjust the SQL based on that. If they aren't, then you probably do a snapshot backup without SQL server knowing it, and those aren't considered valid backups from SQL Server's viewpoint (potentially corrupt databases if you restore from such a backup).


IIRC, try this. I think this is how I fixed it some time ago...

exec sp_configure 'Show Advanced Options', 1; reconfigure;
exec sp_configure 'Ad Hoc Distributed Queries', 1; reconfigure;
go

Edit, attempt 2:

Check this. I've seen this at a previous company (I wasn't sysadmin there) and can't remember what teh DBAs had to do for us to get our linked server to work (also Sybase)

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx


When it comes to CPU core usage, Standard Edition starts with the first NUMA node. Whenever it exhausts the available licensing (2016 & newer, it's 4 sockets or 24 cores, whichever comes first - 2014 & prior, it's 4 sockets or 16 cores, whichever comes first), then it doesn't use the remaining cores.

Here's the tricky part: if it exhausts licensing, and whole NUMA nodes remain with zero cores activated, then that entire NUMA node is offline - unusable for CPU work or memory. I've blogged about that in more detail here.

You asked about memory, and I have a hunch (although I don't have a server handy to prove it) that it might work the same way. If the first NUMA node contains 128GB or more of memory, then that might be the vast majority of memory in use.

It's a very rare edge case, and I wouldn't expect to see any guidance from Microsoft to confirm or deny this. (It's certainly not something they'd test as part of mainstream delivery - if you've got 384GB RAM, you'd wanna use Enterprise Edition instead.)

I'm stretching to think about how you might disable this behavior, other than playing around with soft NUMA.


If the rows will be re-used frequently, it is likely better to set the values to NULL, or simply add a column is_deleted to indicate that the row has been soft-deleted.  Continuously deleting and inserting rows can lead to a heavily fragmented table, and can be a cause of performance degradation.  This is typically used for queues where there are a known number of slots (rows) in the queue, and those slots are allocated ahead of time.

If the rows will not be re-used, or will be re-used very infrequently, then it's better to deallocate the space used by those rows.


extract different number of rows from multiple tables

Your JOIN is behaving perfectly normally.

If you have one airports row, and no matching contacts or runways, you would get something like this:

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol |   NULL  |  NULL

If you had one airport, one contact, and one runway, you'd get:

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol | Ruppert |  28R

However, when you've got multiple matching contacts and runways, and there's no connection between a contact and a runway, then each contact will get paired with each runway, as you've seen:

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol | Ruppert |  28R
 Schiphol | Ruppert |  12L
 Schiphol | Ruppert |  14R
 Schiphol | Griffin |  28R
 Schiphol | Griffin |  12L
 Schiphol | Griffin |  14R

This is called a cross-product. You're getting double the rows you think you want because you're crossing 2 rows with 3 rows (2 * 3 = 6). If you had 7 contacts and 5 runways, you'd be getting 35 rows.

What your best solution is will depend on what you're DBMS is capable of.

Worst case scenario, run three queries: one with the airport results, one with the contacts results, and one with the runways results. Pull all three results sets into PHP. If you're bringing back results for more than one airport, you'd loop through the airports results, and (inside that loop) loop through the other two result sets to pull out the contacts and runways for the current airport.

Many DBMSes provide functions that would let you combine the rows for each airport's contacts or runways into XML or JSON. In that case, you'd have one main query, but you'd have two sub-queries within it to generate the two objects for the other tables. It's be something like (pseudo-code only!):

SELECT airport_name, airport_iata, airport_icao
      ,(SELECT <XML/JSON func>(first_name, last_name, email_address, phone_number)
          FROM contacts
         WHERE airport_id = airports.id
       ) as contacts_obj
      ,(SELECT <XML/JSON func>(designator, length, surface, landing_system)
          FROM runways
         WHERE airport_id = airports.id
       ) as runways_obj
  FROM airports
 WHERE airports.id = 1;

Again, pseudo-code only; you'll need to figure out the details of the function for your DBMS.

Barring an explicit XML or JSON function, most DBMSes have the ability to create string aggregates. This would work much like the function above, except more manually; you'd probably build a string for each row (first_name + '|' + last_name + '|' + ...), and use the aggregate function to combine the multiple contacts into a single string (with a different separator, of course).


The recommended way to do this is to set up a VPN between the two sites.  The steps to do this vary depending on the particular firewall/router being used.  If a VPN cannot be used, the following information is provided for how to set up a connection with port mapping.  If port-mapping is used, it should only be used in conjunction with additional firewall rules to restrict what source IP addresses can route through the mapped port so that every hacker on earth doesn't have access to your SQL Server.

1. Server IP Configuration

Check your Windows Server's IP configuration by running IPCONFIG / ALL. Your results can contain both IP addresses, but at least your internal IP:
- 192.168.1.24
- 95.218.133.168

2. Check your routing

Start a traceroute with the following command: TRACERT <external IP of server>. Verify that you receive the route to your server.

  Important do the same from your LAN and from the INTERNET.
  The results should look like similar to these for the INTERNET check:

 1    <1 ms    <1 ms    <1 ms  <IP of your router>
 2    <1 ms    <1 ms    <1 ms  <IP of your ISP>
 3    <1 ms    <1 ms    <1 ms  <Another IP connecting to your company>
 4     2 ms     1 ms     1 ms  outside.yourcompany.com [<public IP of your company>]
 5     3 ms     1 ms     1 ms  dmz.yourcompany.com [<IP of DMZ component>]
 6     3 ms     1 ms     1 ms  router.yourcompany.com [<IP of router>]
 7     3 ms     1 ms     1 ms  sqlserver.yourcompany.com [<IP of server>]

  Depending on your firewall settings, you might not get past your company's firewall, but if tracert is going in the right direction, then it "knows" the way. Your firewall is just not telling anybody what your network looks like and might not be forwarding the port request to your SQL Server (See Router Configuration later on)

3. Check your SQL Server IP Configuration

In the SQL Server Configuration Manager open up the branch for SQL Server-network configuration and select your instance. (E.g. Protocols for 'MSSQLSERVER')
Right click and open up the properties. Verify that you don't have any settings that could inhibit a connection. Close the setting when you have finished.
In the right hand pane for the protocol settings verify that the TCP/IP protocol is 'Enabled'.
Now right click the TCP/IP setting and open up the properties. In the Protocol tab...
a) Verify again that Enabled is set to Yes.
b) Verify that the setting Listen All is set to Yes.
c) The screen should look like this:

Switch to the IP-Addresses tab and verify for each IPn that ...
a) Active is set to Yes
b) Enabled is set to Yes
c) IP address is your external IP address (or your internal address if you are unable to assign the external address to your SQL Server, because you only have one public IP.)
d) TCP Dynamic Ports is set to  (No value/empty)
e) TCP port is set to 1433
f) The screen should look a bit like this, but with your IP address:

An then verify the settings for the IPAll portion, by checking that ...
a) TCP Dynamic Ports is set to  (No value/empty)
b) TCP Port is set to  (No value/empty)
c) The screen should look like this:

4. Check your router configuration

Your public IP is not the server's, it's the router's. If your server is behind the router then you have to ensure your router is forwarding the request to your SQL Server. This configuration setting can vary from router to router. Here a few examples for configuring routers:
 - Port Forwarding (Zyxel)
 - Setting up static port sharing (Fritz AVM)
 - How do I configure Port Forwarding on my router? (D-Link)

With the provided tips and tricks you should be able to set your SQL Server to accept connections from the Internet.

  DISLCAIMER: It is not really recommended to allow connections via the Internet to a database in your LAN. You risk being attacked.


SQL Server Service Broker - CHECKDB found consistency errors, how could this happen?

Corruptions can occur due to media corruption or memory bit-flips. Ideally, one has a backup and restores from a backup.

If you cannot restore from a uncorrupted DB, when you have a situation where you have inconsistencies between internal tables you can try to run REPAIR_ALLOW_DATA_LOSS. If repair does not fix the issues you can force a new broker in the database (ALTER DATABASE ... SET NEW_BROKER) which will wipe clean all conversations and messages. You will loose all messages pending processing. You need to exercise care if you have remote conversations (endpoints in different server or even different DB) as the partner endpoint will be left stranded and will have to be cleaned up. NEW_BROKER changes the service_broker_guid in sys.databases and this may impact routes.

If you need more detailed help you'll have to open a case with product support.

  Are there any potential DEV/DBA errors or gotchas e.g. while performing SQL upgrade, that might lead to such corruption?

I'm not aware of any, I suspect more likely to be flaky media/memory/cpu (they all can cause corruptions).


Starting points for creating a mysql database proxy

there two or three such software packets available, but usually you use this two split the incoming packages after rules to different servers.
It is in the begining , sowhat difficult to configure, but there a a lot of tutorials
https://proxysql.com/  or https://github.com/mysql/mysql-proxy


Architecture of table for lot of inserts per second in SQL Server 2019

I have some experience with high throughput tables, partitioning and Availability Groups, so I feel confident enough to post this as an answer, although the best I can really do is raise points for you to consider.

First - your 3 synchronous replicas are going to be the slowest links in your chain.  Every commit is going to have to wait for all three to confirm before the client receives the commit signal.  I don't know what to tell you here but make sure you are on fast network and fast disks.

Second - I don't think you need the multiple files to make your solution fast and it may slow things down.  If you move items between partitions then if they are in the same filegroup that's fairly fast and meta-only.  However, if each partition has it's own file/filegroup and you move things around, the system has to pick that data up and physically move it instead of just updating an index.

Third - What is the goal/point of your partitioning?  You should be able to run many hundreds of inserts to a single table.  And SQL 2019 has improvements for highly concurrent inserts to avoid the hot spot issue.  Have you tested it without all the partitioning overhead?

Are you going to be updating this data?  Are you going to be purging the data later?  If you are purging the data later then http://kejser.org/table-pattern-rotating-log-ring-buffer/ may be of use to you.  I've implemented that pattern in my production environment to great success.

What are the datatypes for this 45 column table?  If you are using VARCHAR(MAX), XML or other large datatypes then there may not be much that can be done without re-architecting the table.

Partitioning isn't really great at speeding table access up.  It's main benefit is in allowing for rapid maintenance activities.  Such as quickly loading new data, or discarding old data.


Availability Group failover stuck in Resolving State

Were you able to get this sorted out?

If not , try the following:

Go to the Failover Cluster Manager ( cluadmin.msc from Run).
Expand the Cluster name ( Expand meaning click on the + sign ).
Expand Services and Applications. You should now see your Availability Group listed there.
R-click on that and go to Properties.
In the General Tab you should see Preferred Owners.

Since you've mentioned this to be a lab environment, play around with the options ( by selecting both or alternating selection of preferred owners ) and see where that gets you. Also the tab next to General would specify if you need it to fail back. Post back with results.


PostgreSQL: Recommendation size for shared_buffers

I would avoid placing MySQL and PostgreSQL on the same server. They compete for the same resources. If you can, port everything to one RDBMS. My obvious choice would be PostgreSQL.

Then you can set shared_buffers to something like 500 MB and effective_cache_size to something like 1.5 GB. Be sure to read hints in the manual.

But I would also recommend to add more physical RAM. 2 GB is not much. Hardly enough for good performance with millions of rows. A few more GB of RAM shouldn't cost much.

If you have to stick with your setup 250 MB for Postgres seems reasonable. If MySQL has three times as much traffic, less might be better overall. Like 128 MB.

Basics for performance optimization in the Postgres Wiki.


Like all things, the answer is "it depends".
More tables <> bad if the tables are necessary for proper normalization.  Splitting nullable columns off is something I do often, but not always.  What factors into my decision are things like:

How often will the column be NULL? If it's rarely populated, may make sense to have it as an optional column, implemented as its own table.  This acts like a filtered index any time you might need to locate certain records.
What is the context of the optional column(s)?  If it's information you won't have until a later event occurs (shipping/payment info, etc), then logically it may make more sense for you to implement it separately from the parent table.
How will the data be accessed/how wide is the parent table?  I often deal with summary data from vendors that results in very wide tables with a lot of null values.  I usually will split each column into its own table in order to speed up ad hoc queries that only need a small subset of the data.


Using OLAP-specs to compute selective running totals

Given your sample data, I don't think it is necessary to use OLAP functions. Something like:

select id, sum(case when state = 0 then value end) as sum_w_s_0
     , sum(value) as total_sum
from t
group by id

But in the subject, you mention running totals, which usually means a cumulative sum of some kind. Can you add more sample data in case this answer does not achieve what you want?


Does large table variables fill up logs in tempdb?

Your DBA is correct that actions on table variables are logged to the tempdb transaction log but so are operations on #temp tables so the proposed resolution doesn't help. Indeed if there is any surrounding user transaction the #temp table version may be worse as the tempdb log cannot be truncated until that completes.

Both have their pages allocated in the data file (mdf).

The understanding that the table variable is "stored in the ldf" is completely incorrect. All that gets written to the log is the information necessary to roll back the statement (required in case the statement encounters an error and needs to be rolled back)

You can see examples of the logging involved using sys.fn_dblog. The script below inserts a couple of rows in a table variable and #temp table and updates them.

Example Results

(The log records relating to the table variable show up with the system generated name #162F4418 in the output below)

+-----------------+----------+---------------------------------+-------------------+
|    Operation    | Context  |          AllocUnitName          | Log Record Length |
+-----------------+----------+---------------------------------+-------------------+
| LOP_MODIFY_ROW  | LCX_PFS  | dbo.#162F4418                   |                80 |
| LOP_FORMAT_PAGE | LCX_IAM  | dbo.#162F4418                   |                84 |
| LOP_MODIFY_ROW  | LCX_IAM  | dbo.#162F4418                   |                88 |
| LOP_FORMAT_PAGE | LCX_HEAP | dbo.#162F4418                   |                84 |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#162F4418                   |                72 |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#162F4418                   |                72 |
| LOP_MODIFY_ROW  | LCX_HEAP | dbo.#162F4418                   |               172 |
| LOP_MODIFY_ROW  | LCX_HEAP | dbo.#162F4418                   |               172 |
| LOP_MODIFY_ROW  | LCX_PFS  | dbo.#T___ ... _____000000000056 |                80 |
| LOP_FORMAT_PAGE | LCX_IAM  | dbo.#T___ ... _____000000000056 |                84 |
| LOP_MODIFY_ROW  | LCX_IAM  | dbo.#T___ ... _____000000000056 |                88 |
| LOP_FORMAT_PAGE | LCX_HEAP | dbo.#T___ ... _____000000000056 |                84 |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#T___ ... _____000000000056 |                72 |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#T___ ... _____000000000056 |                72 |
| LOP_MODIFY_ROW  | LCX_HEAP | dbo.#T___ ... _____000000000056 |               172 |
| LOP_MODIFY_ROW  | LCX_HEAP | dbo.#T___ ... _____000000000056 |               172 |
+-----------------+----------+---------------------------------+-------------------+

Script

USE tempdb
GO

CHECKPOINT

GO

/*Table Variable*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, Description CHAR(100))

INSERT INTO @T VALUES (1, REPLICATE('A', 100)), (2, REPLICATE('A', 100))

UPDATE @T
SET    Description = REPLICATE ('B', 100)

/*Temporary Table*/
CREATE TABLE #T ([9E2E9F95-2B75-456B-BF2F-BAE7BCA4109F] INT, Description CHAR(100))

INSERT INTO #T VALUES (1, REPLICATE('A', 100)), (2, REPLICATE('A', 100))

UPDATE #T
SET    Description = REPLICATE ('B', 100)

/*Check the transaction log*/
SELECT  Operation,
        Context,
        AllocUnitName,
        [Log Record Length]
FROM   fn_dblog(NULL, NULL)
WHERE AllocUnitId IN (SELECT a.allocation_unit_id
                                FROM   tempdb.sys.partitions AS p
                              INNER JOIN tempdb.sys.system_internals_allocation_units AS a
                                         ON p.hobt_id = a.container_id
                                       INNER JOIN tempdb.sys.tables AS t
                                         ON t.object_id = p.object_id
                                       INNER JOIN tempdb.sys.columns AS c
                                         ON c.object_id = p.object_id
                                WHERE  c.name IN ('C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3',
                                                  '9E2E9F95-2B75-456B-BF2F-BAE7BCA4109F'))

DROP TABLE #T


MySQL Workbench -- How to efficiently review large database tables?

The first suggestion was to use the "pager less" command - but as pointed out, even at 10 pages a second, it would take 25 hours to get through 900,000 records.

Unfortunately, the "pager" command doesn't work on Windows. It was then suggested to send the output to a file and scroll through that - looking for a disconnect between the .csv input and the final table.

Then, this was proposed

    Or!! You could save your table as a .csv (with a different name
to your original file) and then do a diff on the files - that might
immediately show up problems?

This appears to have resolved the issue for the OP.


Two Intel E5-2643 (4 cores each) or single E5-2690 (8 cores) for Oracle 11.2.0.3 on Oracle Linux

Performance will be very similar. GHz difference is not essential because e.g. E5-2690 has 3.8GHz Max Turbo Frequency vs 3.5GHz on E5-2643. You can find full comparision here:

http://ark.intel.com/compare/64596,64587

The big thing may be licensing. If you are using Standard Edition or Standard Edition One per CPU licensing they are licensed per socket. So in case you buy second E5-2643 you will also have to buy one more Oracle license. Of course that it not the case with the Enterprise Edition.

If licensing is also not an issue then you still have two decide between two choices:

buying 2nd E5-2643 will be just cheaper now. Why not to save some money if everything else is the same?
if you buy E5-2690 and after some time you will hit CPU bottleneck again you will have an option to buy 2nd E5-2690 and once again double the performance.

Also with such amount of memory you really really should use Huge Pages. Of course that means no more AMM and back to ASMM. But especially if you have many connections to Oracle the difference can be really big. You can read about configuring Huge Pages e.g. here:

http://www.oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64.php


You fell victim to a cascade of bad ideas.

1. DateStyle

Do not use locale-dependent format in your statements if you can avoid it. If locale settings change (lc_time and DateStyle in particular), your statement suddenly does something different. '03/11/2018' is ambiguous unless coupled with your specific settings. Always use ISO format which is unambiguous and does not depend on additional settings: '2018-03-11'.

2. timezone

Do not assign a timestamp value (or literal) to a timestamptz column. The assignment cast depends on the current timezone setting of your session which introduces another dependency. With a different timezone setting your statement does something different. Use an explicit cast or a timestamp with time zone literal (timestamptz). In your case:

UPDATE details
SET    interval_start_timestamp = '2018-03-11 02:00:00'::timestamp AT TIME ZONE 'America/Denver'
WHERE  id = 2395

Using an actual time zone name (not an abbreviation) accounts for DST reliably - if your OS is up to date as Postgres works with information provided by the underlying OS.

Related:

Ignoring timezones altogether in Rails and PostgreSQL
Oddities with `AT TIME ZONE` and UTC Offsets

3. DST

Which results in '2018-03-11 03:00:00'. As ypercube pointed out, you hit the start of Summer Time a.k.a. Daylight Savings Time. Clocks were turned forward 1 hour at this time. Each time between 02:00:00 and 02:59:59.999999 is interpreted to mean something between 03:00:00 or 03:59:59.999999 respectively, by the silly rules of DST.

The mere existence of "daylight saving time" (DST) is an insult to reason. It should be abolished and never be spoken of again (except for historic time values doomed to mess with this forever).

Test:

SELECT '2018-03-11 02:34:00'::timestamp AT TIME ZONE 'America/Denver' AT TIME ZONE 'America/Denver' AS t2
     , '2018-03-11 03:34:00'::timestamp AT TIME ZONE 'America/Denver' AT TIME ZONE 'America/Denver' AS t3;

t2                  | t3
:------------------ | :------------------
2018-03-11 03:34:00 | 2018-03-11 03:34:00

dbfiddle here


How to determine the root cause of a performance spike during a heavy load on DB2?

This could be one of the autonomic maintenance processes, e.g. automatic collection of table statistics, although the 37 minute interval looks a bit suspect.

You can start with looking at db2diag.log at around the time of the spike -- most auto maintenance tasks would be logged there.

For a quick look at what thread might be busy, run

db2pd -edus interval=<seconds> -repeat

around the time of the spike, choosing the interval appropriate for the spike width.

Another useful tool is the system stored procedure MONREPORT.DBSUMMARY(<seconds>), which will print the summary of various wait times over the specified period -- run it before, during, and after the spike to compare the numbers.


Failing to automatically re-connect to New PRIMARY after a replica set failover , from Mongoose (MongoDB, NodeJS Driver)

You  need something like the flag { auto_reconnect: true } as described here http://mongodb.github.io/node-mongodb-native/api-generated/db.html.

According to documentation: The server option auto_reconnect is defaulted to true which can be overridden.


Confusing Query: Mixing Aggregates and Non-Aggregates

The solution is what you already found, in DBMS like SQL Server 2005+ that have implemented window functions, we can use them to get aggregates over each partition while keeping the original table, so not collapsing the table as a group by does. I'm not writing the query here, please edit your answer.

In older versions (or other DBMS) that don't have the OVER () clause syntax available, we could use GROUP BY in a derived table (or a CTE) and then a "self" join back to the original table, like this:

SELECT
    i.VendorID, i.InvoiceDate, i.InvoiceTotal,
    grp.VendorTotal,
    grp.VendorCount,
    grp.VendorAvg
FROM
      dbo.Invoices AS i             -- our original table
  JOIN
      ( SELECT
            VendorID,
            Sum(InvoiceTotal) AS VendorTotal,
            Count(InvoiceId) AS VendorCount,
            Avg(InvoiceTotal) AS VendorAvg
        FROM
            dbo.Invoices
        GROUP BY
            VendorID
      ) AS grp                      -- the aggregated table
  ON
      grp.VendorID = i.VendorID ;


Requirements for IIS server used for web sync, merge replicaiton

As mentioned in How to: Configure IIS for Web Synchronization, SQL Server connectivity components must be installed on the computer that is running IIS.  Also, If you use the Configure Web Synchronization Wizard (which is recommended), you'll also need to install SQL Server Management Studio on the computer that is running IIS.


SQL - sum columns with same name from diferent tables

If they're all in the same order, you can do it easily like this,

SELECT t.id, sum(c1), sum(c2)
FROM (
  SELECT *
  FROM tbl1
  UNION ALL ( TABLE tbl2 )
  UNION ALL ( TABLE tbl3 )
) AS t
GROUP BY t.id
ORDER BY 1;

If they're in different orders just replace TABLE tbl2 with SELECT id, c1, c2 FROM tbl2 (they're the same thing).


Why am I getting an error if I am trying to modify data type?

In your image you have far to many parenthesis
ALTER TABLE store MODIFY DOB char(40);

The parenthesis you put around column name and type is not valid.


Sync 8 tables of differents databases on same server

There's actually a very simple solution to "syncing" multiple identical tables in MySQL, and that is by not trying to "sync" actual base tables, at all... but instead creating something that looks like another table but is essentially something along the lines of a "reference" or "alias" to the base table, using a simple view like this:

CREATE ALGORITHM=MERGE VIEW this_database.users AS SELECT * FROM that_database.users;

  Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table...

  — http://dev.mysql.com/doc/refman/5.6/en/view-updatability.html

The view definition, above, meets these criteria, and would be updatable.  Queries referencing a view with a simple definition like this will behave, essentially, no differently than if they were actually written to update (and insert into, and delete from) the actual underlying base table.

So INSERT INTO this_database.users is exactly the same as INSERT INTO that_database.users, and the same is true for UPDATE and DELETE.

If there are foreign keys involved, then the child tables have to have their foreign keys declared against the actual base parent table, but MySQL (InnoDB) has no issue with foreign key constraints that cross database boundaries.

Indexes on the base table will still be used where appropriate, since the MERGE algorithm essentially causes MySQL to "merge" the logic of the view definition into the query you've actually written -- thereby accomplishing the magic of a table that functionally is a reference to another table.

You don't actually have to explicitly declare ALGORITHM=MERGE, since the optimizer will always choose it on such a simple view, but it's still probably a good choice to include that in the definition.

While it is possible that Wordpress might do something internally that prevents this from working, I can't think of anything offhand.  It seems like it would be worth testing in your environment.


Tables not showing up when linking SQL Server to Access using ODBC connection

I know this was 8 months ago. Your problem may be that you did not select a default database. When linking, select the options button and select the database that is not appearing. I hope this helps.


Permission required to view a database diagram

From the documentation:

  Although any user with access to a database can create a diagram, once the diagram has been created, the only users who can see it are the diagram's creator and any member of the db_owner role.
  Ownership of diagrams can only be transferred to members of the
  db_owner role. This is only possible if the previous owner of the
  diagram has been removed from the database.
  If the owner of a diagram has been removed from the database, the diagram will remain in the database until a member of the db_owner role attempts to open it. At that point the db_owner member can choose to take over ownership of the diagram.

So it seems that you won't be able to do it with lower roles like db_datareader.

Behind the scenes, here is what Management Studio is calling to drive the list:

CREATE PROCEDURE dbo.sp_helpdiagrams
(
    @diagramname sysname = NULL,
    @owner_id int = NULL
)
WITH EXECUTE AS N'dbo'
AS
BEGIN
    DECLARE @user sysname
    DECLARE @dboLogin bit
    EXECUTE AS CALLER;
        SET @user = USER_NAME();
        SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
    REVERT;
    SELECT
        [Database] = DB_NAME(),
        [Name] = name,
        [ID] = diagram_id,
        [Owner] = USER_NAME(principal_id),
        [OwnerID] = principal_id
    FROM
        sysdiagrams
    WHERE
        (@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
        (@diagramname IS NULL OR name = @diagramname) AND
        (@owner_id IS NULL OR principal_id = @owner_id)
    ORDER BY
        4, 5, 1
END

So you can see this matches the documentation.

Now a couple of workaround ideas:

In a Logon trigger, update the principal_id of all diagrams to be the current login. This means they will have access to all diagrams until the next person logs in. Not optimal.
Use a trigger on the sysdiagrams table itself (it's not really a system table), and whenever a diagram is created or updated, add / update a copy for each principal (with their user name appended). Not optimal either, and you could have people overwriting each other's diagrams all day long.

Here is an idea of the second workaround - all you really have to maintain here is a list of the database principals that you want to be able to access the diagrams (you'll also want to have something to clean up diagrams that have been deleted, and also some periodic maintenance that deletes diagrams for principals that have been deleted):

CREATE TRIGGER dbo.sysdiagrams_distribute
ON dbo.sysdiagrams
WITH EXECUTE AS N'dbo'
FOR INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @p TABLE(principal_id INT, name SYSNAME);

  INSERT @p SELECT principal_id, name
    FROM sys.database_principals
    -- change this list:
    WHERE name IN (N'test_blat_user', N'test_blat_user2', N'dbo');

  UPDATE d
    SET [version] = i.version, definition = i.definition
  FROM inserted AS i
  CROSS JOIN @p AS p
  INNER JOIN dbo.sysdiagrams AS d
  ON d.name = i.name
  AND d.principal_id = p.principal_id;

  INSERT dbo.sysdiagrams(name, principal_id, version, definition)
    SELECT i.name, p.principal_id, i.version, i.definition
    FROM inserted AS i
    CROSS JOIN @p AS p
    WHERE NOT EXISTS
    (
      SELECT 1 FROM dbo.sysdiagrams WHERE name = i.name
      AND principal_id = p.principal_id
    );
END
GO

After creating a couple of diagrams, here is what an abridged version of Object Explorer looked like for these users:

Now, dbo will collect a whole bunch of copies of diagrams, which maybe isn't necessary, but you probably want those to be the "master" in most circumstances.


The short answer is: Access is weird ;)

Long answer is that Access has certain restrictions in the SQL syntax it supports. It requires parentheses around some constructions, for example when more than 2 tables are joined.

I think the same applies here. It's not that an alias is required, it's that (Access) syntax requires parentheses in INSERT INTO tableA SelectQueryB; when SelectQuery is a UNION query, even though the SQL standard does not require them. I think[1] that this works, too (and doesn't need an alias):

INSERT INTO AssetControl
(SELECT * FROM CSVImport
UNION
SELECT * FROM AssetTemp) ;

[1]:  It appears that this is not allowed either.

Your solution works because it's equivalent to the standard use of INSERT .. SELECT:

INSERT INTO AssetControl
SELECT U.*
FROM SomeTable AS U ;

where SomeTable is replaced by a derived table:

(SELECT * FROM CSVImport
UNION
SELECT * FROM AssetTemp)


Running a speculative transaction in SQL Server

No, there isn't a built-in way to run a "speculative transaction" in SQL Server.

If multiple transactions make incompatible changes, there will be blocking. You would need separate copies of the data to avoid this.


SqlPackage.exe unnecessarily drops and recreates indexed view with schemabinding

SqlPackage.exe often selects a hard way to do things.
We had a following process for issues like this one:
1. Generate change script on dev environments and look for any long steps, like the one you have.
2. Manually create a script to implement needed changes (not all changes, so in your case it will be the view update). Execute this script before SQLPackage.
3. Run SqlPackage again. It should notice that this view do not require changes and will implement only other changes.


Get two weeks of data but group by a period of 7 days

SELECT 'last week' AS week, COUNT(*) AS rows
    FROM table_name
    WHERE date_logged >= CURDATE() - INTERVAL  6 DAY
      AND date_logged  < CURDATE() + INTERVAL  1 DAY
  UNION ALL
    SELECT 'previous week' AS week, COUNT(*) AS rows
    FROM table_name
    WHERE date_logged >= CURDATE() - INTERVAL 13 DAY
      AND date_logged  < CURDATE() - INTERVAL  6 DAY ;


Active Directory group permissions not being applied to user

Is your service account an AD user? It would need to be to evaluate group memberships, for example. (If you're logged in as an AD user, SQL can evaluate those permissions.)

Microsoft recommends using a domain user for service accounts.
http://msdn.microsoft.com/en-us/library/ms143504.aspx


Database file ..._log has a max file size set to x MB. If it runs out of space, the database will stop working

When someone does a BEGIN TRAN, inserts a row, locks their computer, and goes home for the weekend, your log file will grow and grow and grow.

That would be a Bad Thing™.

Rather than facing an outage, you're better off letting the log file grow, and using your monitoring tool to get alerts that there's a problem.


There are many ways for doing this.

When measurements can be trusted to be sequential (time), then one can use a simple self left join, which should perform very fast:

SELECT *
FROM data d
LEFT JOIN data prev ON (prev.time = d.time - 1)
WHERE (d.foo, d.bar) IS DISTINCT FROM (prev.foo, prev.bar)

Another one is using window functions (PG version >= 8.4):

SELECT time, foo, bar
FROM (
  SELECT *, lag(foo) over () AS prev_foo, lag(bar) over () AS prev_bar
  FROM data
  ) d
WHERE (foo, bar) IS DISTINCT FROM (prev_foo, prev_bar)

And another option is using a recursive CTE (PG version >= 8.4):

WITH RECURSIVE d(time, foo, bar) AS (
  SELECT * FROM (SELECT * FROM data ORDER BY time LIMIT 1) a
  UNION ALL
  SELECT *
  FROM (
    SELECT data.*
    FROM data, d
    WHERE data.time > d.time AND (data.foo, data.bar) IS DISTINCT FROM (d.foo, d.bar)
    ORDER BY data.time
    LIMIT 1) a
)
SELECT * FROM d


MySQL replication works but slave keeps reconnecting to master

It turns out my slave's server_id was too long. I was naming these after the slaves' public IP addresses, and this IP was longer than the max value of 4294967295. I should have checked when I decided to use this naming scheme, but a warning from MySQL would also have been nice.

All's well that ends well. I now use the last three decimals of the public IP address (9 digit max).


As per MongoDB documentation here From the Documents tab in MongoDB Compass, you can view, insert, clone, modify, and delete documents .

First you have to connect your mongod with MongoDB Compass then you shall able to change your  desire data type of their respective field in MongoDB.

Change Field Type

You can change the data type of a field by using the data type selectors on the right of the field.

  Note: Creating, deleting, editing and cloning documents is not permitted in MongoDB Compass Readonly Edition.

For your further ref here


Replication of a remote MySQL database to local MySQL database

Check here (http://drcharlesbell.blogspot.cl/2011/06/comparing-databases-with-mysqldbcompare.html)

It explain how to Compare-Copy a DB to another DB, my case is the same as yours.

Depending on your access level you can:

Use a external client as middle man (to make de compare-copy)
Script in PHP to check new lines in the original DB and copy them
Use one of the many tools of MySQL to copy

MySQLDBCompare
MySQLDiff
MySQLDBCopy


DELETE/TRUNCATE from a large table makes MariaDB crashing

You can follow below steps, it may help you.

CREATE TABLE db_test.table2_tmp LIKE db_test.table2;

]RENAME TABLE db_test.table2 TO db_test.table2_to_be_deleted;

RENAME TABLE db_test.table2_tmp TO db_test.table2;

DROP TABLE db_test.table2_to_be_deleted;


Oracle how to manage the flashback log space?

Short answer: Oracle will keep flashback logs as long as it can, limited only by space in FRA.

Longer answer: The database FRA (Fast Recovery Area) is a fixed size, defined by parameter db_recovery_file_dest_size

In this, go various things, including:

Archive logs
Flashback logs
Redo logs
Standby recovery logs
Backups
And more!

Oracle will, under certain circumstances and under space pressure, automatically delete stuff from FRA to make room for new stuff.  It will delete flashback logs at will, even if that brings the possible flashback time under the value that the db_flashback_retention_target parameter is set to.  Remember: It's a target.

If, under space pressure, Oracle can't delete enough stuff (e.g., archive logs that  haven't been backed up) in order to continue, it will "freeze" and give an error on log in.

If there is no space pressure within the FRA, it will continue to store stuff for as long as it can.


How do I determine what object two SQL process are contending over?

In management studio you can right click the server node and select Reports -> Standard Reports -> Activity - all Blocking Transactions to see blocking currently ongoing.

The query that report runs can be traced through Profiler and is pasted below.

declare @tab_tran_locks as table(
        l_resource_type nvarchar(60) collate database_default
,       l_resource_subtype nvarchar(60) collate database_default
,       l_resource_associated_entity_id bigint
,       l_blocking_request_spid int
,       l_blocked_request_spid int
,       l_blocking_request_mode nvarchar(60) collate database_default
,       l_blocked_request_mode nvarchar(60) collate database_default
,       l_blocking_tran_id bigint
,       l_blocked_tran_id bigint
);
declare @tab_blocked_tran as table (
        tran_id bigint
,       no_blocked bigint
);
declare @temp_tab table(
        blocking_status int
,       no_blocked int
,       l_resource_type nvarchar(60) collate database_default
,       l_resource_subtype nvarchar(60) collate database_default
,       l_resource_associated_entity_id bigint
,       l_blocking_request_spid int
,       l_blocked_request_spid int
,       l_blocking_request_mode nvarchar(60) collate database_default
,       l_blocked_request_mode nvarchar(60) collate database_default
,       l_blocking_tran_id int
,       l_blocked_tran_id int
,       local1 int
,       local2 int
,       b_tran_id bigint
,       w_tran_id bigint
,       b_name nvarchar(128) collate database_default
,       w_name nvarchar(128) collate database_default
,       b_tran_begin_time datetime
,       w_tran_begin_time datetime
,       b_state nvarchar(60) collate database_default
,       w_state nvarchar(60) collate database_default
,       b_trans_type nvarchar(60) collate database_default
,       w_trans_type nvarchar(60) collate database_default
,       b_text nvarchar(max) collate database_default
,       w_text nvarchar(max) collate database_default
,       db_span_count1 int
,       db_span_count2 int
);

insert into @tab_tran_locks
select  a.resource_type
,               a.resource_subtype
,               a.resource_associated_entity_id
,               a.request_session_id as blocking
,               b.request_session_id as blocked
,               a.request_mode
,               b.request_mode
,               a.request_owner_id
,               b.request_owner_id
from sys.dm_tran_locks a
join sys.dm_tran_locks b on (a.resource_type = b.resource_type and a.resource_subtype = b.resource_subtype and a.resource_associated_entity_id = b.resource_associated_entity_id and a.resource_description = b.resource_description)
where a.request_status = 'GRANT' and (b.request_status = 'WAIT' or b.request_status = 'CONVERT') and a.request_owner_type = 'TRANSACTION' and b.request_owner_type = 'TRANSACTION'

insert into @tab_blocked_tran
select  ttl.l_blocking_tran_id
,       count(distinct ttl.l_blocked_tran_id)
from @tab_tran_locks ttl
group by ttl.l_blocking_tran_id
order by count( distinct ttl.l_blocked_tran_id) desc

insert into @temp_tab
select  0 as blocking_status
,       tbt.no_blocked
,       ttl.*
,       st1.is_local as local1
,       st2.is_local as local2
,       st1.transaction_id as b_tran_id
,       ttl.l_blocked_tran_id as w_tran_id
,       at1.name as b_name,at2.name as w_name
,       at1.transaction_begin_time as b_tran_begin_time
,       at2.transaction_begin_time as w_tran_begin_time
,       case when at1.transaction_type <> 4
                then case at1.transaction_state
                        when 0 then 'Invalid'
                        when 1 then 'Initialized'
                        when 2 then 'Active'
                        when 3 then 'Ended'
                        when 4 then 'Commit Started'
                        when 5 then 'Prepared'
                        when 6 then 'Committed'
                        when 7 then 'Rolling Back'
                        when 8 then 'Rolled Back'
                end
                else case at1.dtc_state
                        when 1 then 'Active'
                        when 2 then 'Prepared'
                        when 3 then 'Committed'
                        when 4 then 'Aborted'
                        when 5 then 'Recovered'
        end end b_state
,       case when at2.transaction_type <> 4
                then case at2.transaction_state
                        when 0 then 'Invalid'
                        when 1 then 'Initialized'
                        when 2 then 'Active'
                        when 3 then 'Ended'
                        when 4 then 'Commit Started'
                        when 5 then 'Prepared'
                        when 6 then 'Committed'
                        when 7 then 'Rolling Back'
                        when 8 then 'Rolled Back'
                end
                else case at2.dtc_state
                        when 1 then 'Active'
                        when 2 then 'Prepared'
                        when 3 then 'Committed'
                        when 4 then 'Aborted'
                        when 5 then 'Recovered'
                end
        end w_state
,       at1.transaction_type as b_trans_type
,               at2.transaction_type  as w_trans_type
,       case when r1.sql_handle IS NULL then '--' else ( select top 1 substring(text,(r1.statement_start_offset+2)/2, (case when r1.statement_end_offset = -1   then (len(convert(nvarchar(MAX),text))*2) else r1.statement_end_offset  end - r1.statement_start_offset) /2  ) from sys.dm_exec_sql_text(r1.sql_handle)) end as b_text
,       case when r2.sql_handle IS NULL then '--' else ( select top 1 substring(text,(r2.statement_start_offset+2)/2, (case when r2.statement_end_offset =-1 then len(convert(nvarchar(MAX),text))*2  when r2.statement_end_offset =0  then len(convert(nvarchar(MAX),text))*2  else r2.statement_end_offset  end - r2.statement_start_offset) /2  ) from sys.dm_exec_sql_text(r2.sql_handle)) end as w_text
,       ( Select count(distinct database_id) from sys.dm_tran_database_transactions where transaction_id = st1.transaction_id ) as db_span_count1
,       ( Select count(distinct database_id) from sys.dm_tran_database_transactions where transaction_id = st2.transaction_id ) as db_span_count2
from @tab_tran_locks ttl
inner join sys.dm_tran_active_transactions at1 on(at1.transaction_id = ttl.l_blocking_tran_id)
inner join @tab_blocked_tran tbt on(tbt.tran_id = at1.transaction_id)
inner join sys.dm_tran_session_transactions st1 on(at1.transaction_id = st1.transaction_id)
left outer join sys.dm_exec_requests r1 on(at1.transaction_id = r1.transaction_id )
inner join sys.dm_tran_active_transactions at2 on(at2.transaction_id = ttl.l_blocked_tran_id)
left outer join sys.dm_tran_session_transactions st2  on(at2.transaction_id = st2.transaction_id)
left outer join  sys.dm_exec_requests r2 on(at2.transaction_id = r2.transaction_id )
where st1.is_user_transaction = 1
order by tbt.no_blocked desc;

with Blocking(
        blocking_status
,       no_blocked
,       total_blocked
,       l_resource_type
,       l_resource_subtype
,       l_resource_associated_entity_id
,       l_blocking_request_spid
,       l_blocked_request_spid
,       l_blocking_request_mode
,       l_blocked_request_mode
,       local1
,       local2
,       b_tran_id
,       w_tran_id
,       b_name
,       w_name
,       b_tran_begin_time
,       w_tran_begin_time
,       b_state
,       w_state
,       b_trans_type
,       w_trans_type
,       b_text
,       w_text
,       db_span_count1
,       db_span_count2
,       lvl
)
as( select blocking_status
        ,       no_blocked
        ,       no_blocked
        ,       l_resource_type
        ,       l_resource_subtype
        ,       l_resource_associated_entity_id
        ,       l_blocking_request_spid
        ,       l_blocked_request_spid
        ,       l_blocking_request_mode
        ,       l_blocked_request_mode
        ,       local1
        ,       local2
        ,       b_tran_id
        ,       w_tran_id
        ,       b_name
        ,       w_name
        ,       b_tran_begin_time
        ,       w_tran_begin_time
        ,       b_state
        ,       w_state
        ,       b_trans_type
        ,       w_trans_type
        ,       b_text
        ,       w_text
        ,       db_span_count1
        ,       db_span_count2
        ,       0
        from @temp_tab
   union all
        select E.blocking_status
        ,       M.no_blocked
        ,       convert(int,E.no_blocked + total_blocked)
        ,       E.l_resource_type
        ,       E.l_resource_subtype
        ,       E.l_resource_associated_entity_id
        ,       M.l_blocking_request_spid
        ,       E.l_blocked_request_spid
        ,       M.l_blocking_request_mode
        ,       E.l_blocked_request_mode
        ,       M.local1
        ,       E.local2
        ,       M.b_tran_id
        ,       E.w_tran_id
        ,       M.b_name
        ,       E.w_name
        ,       M.b_tran_begin_time
        ,       E.w_tran_begin_time
        ,       M.b_state
        ,       E.w_state
        ,       M.b_trans_type
        ,       E.w_trans_type
        ,       M.b_text
        ,       E.w_text
        ,       M.db_span_count1
        ,       E.db_span_count2
        ,       M.lvl+1
        from @temp_tab as E
    join Blocking as M on E.b_tran_id = M.w_tran_id
)

select (dense_rank() over (order by no_blocked desc,b_tran_id))%2 as l1
,       (dense_rank() over (order by no_blocked desc,b_tran_id,w_tran_id))%2 as l2
,       *
from Blocking
order by no_blocked desc,b_tran_id,w_tran_id ;


SQL - query multiplying records returned - Cartesian Product

Your cartesian product should be the product of # of rows in properly joined tables times the rows in tables that are not properly joined (i.e. don't have effective condition in the WHERE clause). For cases where specimen.snop_axis = 'M' morphology and specimen are propely joined but functions isn't, and when specimen.snop_axis <> 'M' functions and specimen are properly joined but morphology isn't. That's most likely why the resulting number of rows doesn't seem to be any direct product of rows in the tables.

To solve this I'd first try to join all the tables properly by replacing

AND if(specimen.snop_axis = 'M', morphology.morphology_code = specimen.snop_code, functions.functions_code = specimen.snop_code)

with

AND functions.functions_code = specimen.snop_code
AND morphology.morphology_code = specimen.snop_code

To me, this seems to give ok result with the sample data, but I wouldn't be too sure of how it behaves with your real data.

I'm not completely sure what you are trying to obtain by having that IF condition in WHERE clause. If the objective is to return morphology data and no functions data when specimen.snop_axis='M' and in other cases to return functions data and no morphology data, I'd suggest having the selection logic applied in the columns that are returned, somehow like this:

SELECT
IF(specimen.snop_axis = 'M', morphology_pk, functions_pk) matching_pk,
IF(specimen.snop_axis = 'M', morphology_code, functions_code) matching_code,
IF(specimen.snop_axis = 'M', morphology_nomen, functions_nomen) matching_nomen,
specimen.*,
topography_index.*

instead of just having columns from both tables returned always. But as I said, I'm not quite sure I understand what is required.


How to limit the connections per host in MongoDB?

I've listed some alternatives for connection management below, in order of most to least recommended.

Increase the connections allowed on the server

The total incoming connection limit on the server is determined by the lesser of the limits imposed by the operating system or maxIncomingConnections (aka maxConns in MongoDB 2.4 and earlier).

Typically Linux distributions limit file descriptors per process to 1024, of which MongoDB will use 80% for incoming connections (leaving about 819 available connections).

You can check current and available connections in the mongo shell via:

db.serverStatus().connections

For production systems it is typical to adjust the ulimit settings on Linux to allow more concurrent connections. For more best practices, I would recommend reviewing the Production Notes in the MongoDB manual.

Provide an API

If you are managing a shared server with resource limits, it is common to provide your own API rather than direct database access. This approach gives you an extra layer of abstraction so you can manage resource usage and server deployment independent of the client configuration. For example, you could move your database server or reconfigure from a standalone to a replica set, and the clients would not have to be aware of this. You can also manage custom resource limits (such as connections per client) via your API, based on the credentials the client uses to connect.

Reduce the connection pool size in the clients

MongoDB (as at 2.6) doesn't have an option to limit the connections per client. Normally client limits would be imposed via the driver (i.e. setting the connection pool size). For example, in the Java driver the MongoClient default maximum pool size is 100.

You've already suggested this isn't a desirable option as you don't want the clients to mess with the connection limits, but if you are going to impose a server side limit it would still be reasonable to have them set the pool size appropriately. Otherwise their applications will get frequent exceptions as you kill off excess connections.

Monitor client operations

If adjusting limits on the client or server isn't an option, an alternative to consider is implementing a script to count concurrent client connections (by IP) via db.currentOp() and kill excess connections via db.killOp(). You'd have to be very careful to only kill client requests. The killOp() command is a superuser command that will let you kill internal database threads as well (which can lead to unpredictable outcomes).

NOTE: This approach will be unsuccessful if your clients are connecting via a shared gateway (i.e. where the source IP does not uniquely identify a client).


The comment left by user sepupic is correct:

  And indeed there is no interest to server to know how did you name your connection or local registration, so only your client tool may know this

The only places I might have expected to see this would be the "connection" related DMVs, for instance if SQL Server kept track of the connection name used to connect to it (similar to how it keeps track of the client host name, set options used, etc).

However, it's not present in any of the main ones I checked:

sys.dm_exec_connections
sys.dm_exec_requests
sys.dm_exec_sessions

And this makes sense, especially with registrations where the client has to translate those names before creating the connection anyway.


How to use numeric operator between varchar value?

The short answer is that you shouldn't. It is almost certainly possible using the various string manipulation functions to find and extract the right value then cast it to a numeric for comparison, but that would be massively inefficient, difficult to get right (you have to account for the keys you are looking for appearing as partial matches within other key names or values for instance, you would be trying to write a full JSON parser in SQL string functions), and a pain to maintain if your requirements change over time.

If you genuinely need to operate in this manner, consider using a document-store database (like many of the noSQL options that are out there), particularly one designed around JSON as the storage format (CouchDB, Mongo, ...) as that seems to be the format you are using.

Otherwise if you want (or have) to use a relational database then store your data in a relational manner. Of course if you have little control of what keys will be in the data then you migth be forced to use the key/value pair structure (also called the EAV (Entity-Attribute-Value) model and often considered an anti-pattern also) so this may not be optimal depending on your application.

Some relational databases are starting to offer direct support for indexing properties found in arbitrary JSON strings (see http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/ for notes on the support recently added to postgres) allowing you to mix both worlds, but I can't comment on the relative efficiency of this compared to a pure relational model or a pure document store based model as I've never used such features myself yet (and anyway, mySQL isn't one of DBMSs that support it).


Why am I getting permission denied on an ownership change?

There are some unintuitive permissions requirements when using REASSIGN without a superuser.  See this other pots where I answered the same question: https://stackoverflow.com/a/62557497/79079


Is there a way remove SQL Server listener IP on maintenance

better approach than deactivating all listeners is to set your instance in single user mode. Single user mode is recommended for maintenance work and dont allow normal users to connect to the instance. you do not need to change any listeners and permissions on your database. Check this msdn page.


Issue with users creating objects in a user owned schema

See Books Online for CREATE SCHEMA
The article above contains the following:
Implicit Schema and User Creation
In some cases a user can use a database without having a database user account (a database principal in the database). This can happen in the following situations:
- A login has CONTROL SERVER privileges.
- A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a member of a Windows group which has a database user account (a database principal for the Windows group).

When a user without a database user account creates an object without specifying an existing schema, a database principal and default schema will be automatically created in the database for that user. The created database principal and schema will have the same name as the name that user used when connecting to SQL Server (the SQL Server authentication login name or the Windows user name).

This behavior is necessary to allow users that are based on Windows groups to create and own objects. However it can result in the unintentional creation of schemas and users. To avoid implicitly creating users and schemas, whenever possible explicitly create database principals and assign a default schema. Or explicitly state an existing schema when creating objects in a database, using two or three-part object names.


Postgresql schema only works when wrapped in quotation marks

authorization is a reserved keyword in Postgres, so it needs to be quoted to be used as a (database/schema/table) name. See the list: Appendix C. SQL Key Words

And a very popular opinion among Postgres developers - to avoid the complications - is:
Do not use reserved words as identifiers.


Update reps table with right values in a trigger after insert on userterritory

I edited your TRIGGER to do this:

I added the vTerritoryCount variable.
If you have more than 1 rows with the same userid it wont update the reps.territories_id.

Here's the TRIGGER:

USE `veeva_new`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW
BEGIN
    # [ VARIABLES DECLARATION ]
    DECLARE vUserID VARCHAR(18) DEFAULT '';
    DECLARE vTerritoryRep VARCHAR(45) DEFAULT '';
    DECLARE vTerritoryLastMod DATETIME DEFAULT '0000-00-00 00:00:00';
    DECLARE vTerritoryCount INT(7) DEFAULT 0; #[060715.01]
    # [ VARIABLES ASIGNATION ]
    SET vUserID         =NEW.userid;
    SET vTerritoryRep   =(SELECT IFNULL(reps.territories_id,'N/A') FROM pdone.reps WHERE reps.veeva_rep_id=vUserID LIMIT 0,1);
    SET vTerritoryLastMod=(SELECT reps.lastSyncAt FROM pdone.reps WHERE reps.veeva_rep_id=vUserID LIMIT 0,1);
    SET vTerritoryCount =(SELECT COUNT(*) FROM veeva_new.userterritory WHERE userterritory.userid=vUserID);
IF (NEW.territoryid != vTerritoryRep) THEN # [ IF THE NEW TERRITORYID IS DIFFERENT FROM TERRIROTYID IN reps TABLE ]
    IF (vTerritoryCount>1) THEN
        SET @Blah='There\'s more than 1 rows, I\'m not going to do anything.';
    ELSE
        UPDATE pdone.reps
        SET reps.territories_id = NEW.territoryid,
            reps.lastSyncAt = IF(NOW()>vTerritoryLastMod,NOW(),reps.lastSyncAt),
            reps.updatedAt=now()
        WHERE reps.veeva_rep_id=vUserID;
    END IF;
END IF;
END


How is encoding done in SQL database tables for pattern matching?

Are strings in table columns represented as bit patterns or Unicode?

Computers only deal with 1's and 0's (i.e. binary); datatypes indicate how to interpret that info.

  Would the queries perform faster if the column in the table was designed to have the year at the left of the name in the strings that are stored? (assuming the year component of the string was the most unique)

If your data has "components" that need to be parsed out, that will never perform well regardless of RDBMS or indexing. The entire purpose of having different fields and different datatypes is to represent each component individually according to what it actually is. As @Michael pointed out in his comment, your one field is poorly modeled and should be three fields:

Make  VARCHAR(something)
Model VARCHAR(something)
Year  INT (or maybe SMALLINT)

Then you can index them as appropriate. Comparing 91 (or actually 1991) to 1991 as a numeric comparison is faster than comparing that value as a string to partial-match against 1991FordEscort, of even if it were a separate string field. The degree to which it is faster is related to several factors, including the size of the field, the field's collation, and vendor-specific implementations and features.

  If there is a character-by-character, left-to-right matching process, the process could be more resource intensive than evaluating bit pattern hashes representing the strings being compared.

Left-to-right vs right-to-left, case-sensitive vs case-insensitive, etc is usually determined by the collation of the field and the language/locale/lcid of the database. But searching on fields can't be matching hashes as hashes can only rule out exact matches and cannot prove a positive match (due to collisions) or help with partial matches. If you need very string character matching, look into using a BINARY collation.


Why is it truncate table, and not simply truncate?

The syntax choice is likely for consistency, albeit redundant.  TRUNCATE TABLE follows the same pattern as other DDL statements (CREATE, ALTER, DROP), where the object type immediately follows the action keyword.

I should add TRUNCATE TABLE is part of the ANSI SQL Standard as of the 2008 version, although it was implemented in SQL Server long before then.


Oracle Enterprise Manager - top activity

The graph is showing the number of sessions in wait. The colors represent a broad category of wait class, which assists in quickly identifying the primary type of wait faced by sessions in the database.

The maximum CPU line simply gives you a marker to determine if the amount of CPU wait is reaching extremely critical levels.


E11000 duplicate key error While Adding Shard

Problem is that your replica set (rs3) has been or it is part of some other cluster! Replica set what has never been connected to shard don't have that document. Error comes when cluster tries to add this document to this admin.system.version during connection-to-cluster procedure.

If you query that collection, you can see "which" cluster that RS thinks it belongs.

If you know that this rs3 is not part of any other cluster, you can connect directly to that replica set and remove that document from admin.system.version -collection. After that, addShard command on mongoS should work fine.


Sql Script takes much longer than estimated

I figured it was the Revovery Model that was left set to FULL. As the article linked in the question says,  it is required to change it to simple of bulk: "The recovery model SIMPLE and BULK_LOGGED perform "best" contrary to the recovery model FULL."
So, after setting it to simple the process performed within couple of minutes.


How to create sql query results in columns from rows?

I don't have the energy to deal with all the joins through all your tables, but the general idea would be to join to the store table twice and check the results.

 SELECT f.film_id,
   CASE when store1.store_id is not null then 'Item exists in store 1' else 'Item missing from store 1' END as 'Store 1 Status'
 FROM film f
 LEFT OUTER JOIN store store1 on f.film_id = store1.film_id AND store1.store_id = 1
 LEFT OUTER JOIN store store2 on f.film_id = store2.film_id AND store2.store_id = 2

This doesn't scale well if you had 100 stores, but for two it wouldn't be evil.

Hope that helps.


Append to an existing backup setup when doing a Transaction Log backup?

I personally create a whole new NTFS file for each backup. Why? Because there is no chance of accidentally overwriting a backup (with init). A lost transaction log backup is crippling. In my opinion, the maintenance overhead of multiple backup devices/files is negligible.


Does it make sense to run a cluster of database servers in docker containers?

Galera (for MySQL, Percona XtraDB Cluster or MariaDB Cluster, they are essentially the same but from different vendors and base mysql version) can work perfectly with 2 machines, and it is a very common setup for substituting standard MySQL replication.

Requiring 3 nodes is not a requirement of Galera, but of any cluster that cares about data consistency over availability. In other words, it is needed in order to provide availability and data integrity in the case of only one node suffering from network partition (causing a "split brain" on the cluster). We need an odd number of nodes and 1 node is not a cluster :-). You probably knew that, but I wanted to clarify this for any people reading this answer.

Given that the only reason to have 3 nodes is for availability in case of a node failure/network failure, the recommended way to setup a 2 node cluster with network split protection is to setup a Galera Arbitrator which is essentially an emulation of a MySQL node, but without storing any local data. You can install that anywhere, although you must be careful because if still may influence the performance of the cluster if it has network latency problems.

Running several instances on the same physical node is useless (docker is not needed, by the way, you can do that by just changing the local configuration), as if you absolutely can only run things on 2 machines, you better disable the automatic shutdown on one of the nodes and manage the failover manually. The 3 limitation is just for data consistency, there is nothing physical or on the protocol requiring a certain number of nodes.

You can read more information about it on the official documentation.


If you copied and pasted that output from your SELECT statement, you have a typo in 'localhost'. That could be causing your problem.


Implicit conversion does not affect performance

The issue with implicit conversion is that it can prevent the efficient use of an index. If a function has to be applied to a column to get the correct comparison value, then an index seek on that column cannot be used in the search.

However, SQL Server has to decide which value to convert when it has to compare values of different types. It does this based on its Data Type Precedence rules.

If you look at that page, you'll see that integer values have a higher precedence that varchar values. So, the varchar value is the one that gets converted.

In the example you present, the indexed column is an integer column, and the hard-coded values are varchar (or char, or nvarchar - all have a lower precedence than integer).

So, the hard-coded values are converted to integers. Since your column does not undergo a transformation, the index can still be used.

NOTE: this is not the only factor that goes into this. For example, conversions between different data types that are in the same "family" (for example, int and bigint) will still allow an index seek, regardless of which datatype is the indexed column.

This is why one should match datatypes whenever possible, or use explicit conversions on non-indexed data where needed; by controlling the conversion process, you can usually avoid the problem.

On a personal note - this precedence order has long been an annoyance. If you have a character value that you need to compare to a numeric value, unless you explicitly perform the conversion, SQL Server will try to convert the character value (which will not always have a valid numeric representation) to a numeric, instead of converting the numeric value (which should always have a valid character representation). Sigh.

Bob Klimes pointed out a couple of related articles:

Implicit Conversions that cause Index Scans, by Jonathan Kehayias, which conducts a test to see which data types result in an index scan when converted implicitly; and
Data Type Mismatches Don’t Always Cause a “Bad” Implicit Conversion and Index Scan, by Kendra Little, which covers the fact that implicit conversions between different types in the same "family" still allow an index seek instead of an index scan.


One other way of doing this would be to aggregate the status column by ip_addr, where the total is equal to zero.

The test-bed:

CREATE TABLE `ports`
(
    `id` INT NOT NULL
    , `ip_add` VARCHAR(15) NOT NULL
    , `port` INT NOT NULL
    , `status` BIT NOT NULL
    , `probe_meta` DATETIME
);

INSERT INTO `ports`
VALUES (1, '192.168.0.1', 22, 1, '2016-03-21 00:00:00');

INSERT INTO `ports`
VALUES (1, '192.168.0.2', 22, 0, '2016-03-21 00:01:00');

INSERT INTO `ports`
VALUES (1, '192.168.0.3', 22, 1, '2016-03-21 00:02:00');

INSERT INTO `ports`
VALUES (1, '192.168.0.3', 22, 0, '2016-03-21 00:03:00');

The query:

SELECT ip_add
FROM `ports`
GROUP BY ip_add
HAVING SUM(status) = 0;

The results:

SQLFiddle


Missing Encryption Key when migrating Reporting Services from SQL Server 2008 to SQL Server 2016

Unfortunately we don't have the Encryption Key.

This is the problem, and you already know what I'm going to say next. I hope your 350 reports were in a source control system.


Auto-increment ids with multiple inserts in mysql/mariadb

Yes, for that particular statement, MySQL knows how many rows are going to be inserted, and a lock and/or a mutex is used to retrieve the ids to be inserted at the start of the query. It is fully deterministic and values will be consecutive.

However, depending on the variable innodb_autoinc_lock_mode, other non-trivial inserts like insert... on duplicate key update and insert... select may not guarantee that deterministic behavior (as they may not know the actual number of rows to be inserted). This is to allow for extra concurrency by sacrifying the consecutive values. In all cases, a unique, monotonically increasing value is guaranteed for the table as a whole, it is just that in some cases there may be gaps and interleavings.

You can know more about innodb autoinc behavior in the manual. And you can also test it in practice by doing:

$ mysql test -e "INSERT INTO test (session) VALUES (1), (sleep(1)), (1)"

while you insert as fast as possible other value in concurrency.

Addendum: I just realized a case in which you cannot assume that: if you use auto_increment_offset :-)


Import bacpac from SQL Azure into local server fails.

Please install the  latest versions of SSMS, then try to run the wizard again. Please download the latest version from here.


How to create multiple container databases in 12c R1?

you can create it manually or automatically through dbca

How to start and stop pluggable databases in Oracle 12c

Because the instance architecture of pluggable databases is entirely      different from a non-container database,  one would imagine that managing their state of readiness is also  different.
     Well, it’s true.

The first thing to remember is that because the CDB maintains the
       instance for which all PDBs share,
       that instance must be up and open for people to be able to connect to the PDBs.
       Starting and stopping the CDB is not different from non-CDBs.
       The next thing to remember is that when you start a CDB,
       all of its associated PDBs are left in MOUNT state, which means
       that, by default, they are not opened with the CDB.

Unfortunately, 12cR1 doesn’t offer an option to change this behavior.
        However, 12c does provide a new type of trigger that will fire if it
        detects a CDB opening and will then open specified PDBs.
        See the Oracle documentation for further information on setting this up.
        After starting and opening a CDB, you can open any corresponding PDBs
        like so

    alter pluggable database orclpdb2 open;
    -- Pluggable database altered.
    -- or
    alter pluggable database all open;

To close PDBs, you can essentially do the opposite of the preceding  commands:

 alter pluggable database devpdb1 close;
 -- Pluggable database altered.
 -- Or
 alter pluggable database all close;

To drop pluggable database

 drop pluggable database orclpdb2 including datafiles;

You can unplug a PDB from a CDB with this syntax:

alter pluggable database orclpdb2
unplug into
      '/{installationdir}|{backupdir}/orclpdb2pxml';

reference0

reference1


How can I improve performance of the stored procedure?

Here's an attempt to rewrite the function into the procedure:

CREATE PROCEDURE [dbo].[ImproveProcedure]
(
  @port varchar(50)
 ,@portdate datetime
)
AS
BEGIN
    DECLARE @intdate datetime;
    SELECT @intdate = MAX(rate_date)
      FROM Interestrate
     WHERE rate_type = 'Zero'
       AND rate_date <= @portdate
    ;

    IF (OBJECT_ID('tempdb..#tmp_fx') IS NOT NULL) DROP TABLE #tmp_fx;
    CREATE TABLE #tmp_fx
         ( currency1 varchar(10)
          ,currency2 varchar(10)
          ,month int
          ,rate_date datetime
          ,rate float
          ,CONSTRAINT PK_tmp_fx PRIMARY KEY (currency2, month)
         );

    -- NOTE: currency1 not needed in PK, as it's always 'USD' - may want to change if add'l values are added.

    -- Get base currency pairs, months, and max dates
    INSERT INTO #tmp_fx
    SELECT currency1, currency2, forward_month, MAX(rate_date)
      FROM (SELECT currency1, currency2, forward_month, rate_date
              FROM fx
             WHERE currency1 = 'USD'
               AND rate_date <= @portdate
            UNION ALL
            SELECT currency2, currency1, forward_month, rate_date
              FROM fx
             WHERE currency2 = 'USD'
               AND rate_date <= @portdate
           ) sq
     GROUP BY currency1, currency2, forward_month
    ;

    -- Set rates based on previous query:
    --   Step 1: rates where currency1 = currency1 -- multiply
    UPDATE t
       SET rate = fx.Rate
      FROM #tmp_fx t
             INNER JOIN fx ON (    t.currency1 = fx.currency1
                               AND t.currency2 = fx.currency2
                               AND t.month = fx.forward_month
                               AND t.rate_date = fx.rate_date
                              )
    ;

    --   Step 2: rates where currency2 = currency1 -- divide
    UPDATE t
       SET rate = 1 / fx.Rate
      FROM #tmp_fx t
             INNER JOIN fx ON (    t.currency2 = fx.currency1
                               AND t.currency1 = fx.currency2
                               AND t.month = fx.forward_month
                               AND t.rate_date = fx.rate_date
                              )
     WHERE fx.rate IS NULL
    ;

    -- NOTE - unset rates left NULL - will force to 1 in final query

    UPDATE tr
       SET NonDiscount = null
          ,Discount = null
          ,NonDiscountTcurr = null
          ,DiscountTcurr = null
          ,NonDiscountNew = null
          ,DiscountNew = null
     WHERE port = @port
       AND portdate = @portdate
    ;

    UPDATE tr
       SET NonDiscount =  CASE WHEN p1.Sub <> 'Option'
                            THEN CASE sss_ind
                                   WHEN 'P' then -exposure
                                   ELSE          exposure
                                 END
                            ELSE CASE WHEN buysell IN ('A', 'S')
                                   THEN -vol * markprice
                                   ELSE vol * markprice
                                 END
                          END
                        * ISNULL(fx.Rate, CAST(1 as float))
          ,NonDiscountTcurr = CASE WHEN p1.Sub <> 'Option'
                                THEN CASE sss_ind
                                       WHEN 'P' then -exposure
                                       ELSE          exposure
                                     END
                                ELSE CASE WHEN buysell IN ('A', 'S')
                                       THEN -vol * markprice
                                       ELSE vol * markprice
                                     END
                              END
      FROM tr
             INNER JOIN Phy p1 ON (tr.trans = p1.trans)
             LEFT  JOIN #tmp_fx fx ON (    'USD' = fx.currency1
                                       AND ccd = fx.currency2
                                       AND portdate = fx.rate_date
                                       AND  CASE
                                              WHEN DATEDIFF(month, portdate, COALESCE(priceend1, priceend2, mature)) < 1
                                                THEN 1
                                              WHEN DATEDIFF(month, portdate, COALESCE(priceend1, priceend2, mature)) > 48
                                                THEN 48
                                              ELSE   DATEDIFF(month, portdate, COALESCE(priceend1, priceend2, mature))
                                            END
                                          = fx.month
                                      )
     WHERE port = @port
       AND portdate = @portdate
       AND p1.Sub IS NOT NULL
    ;
END;

Your function uses ccd, portdate, and the number of months between portdate and three possible dates (using the first that's not NULL) to calculate the rate that the exposure should be multiplied by.

If you can find a match where currency1 is 'USD', then you multiple by rate;
If the only match is where currency2 is 'USD', then you divide by rate;
if no match is found, the rate defaults to 1

So, let's try to build a table with your available rates in it.

We have basically have two factors that identify the rate_date values we'll use:

the month, an integer between 1 and 48;
the two currencies, one of which will be 'USD', the other of which will vary.

The maximum date for each of these will determine what rate is needed.

That gives us at most 48 * (number of currencies) rows to worry about, in practical terms. I'll guess that's 10-15 thousand rows, not an unreasonably large table.

For joining purposes, it will help to standardize the currency, so the first one is always USD - that way we don't have to worry about whether we're multiplying or dividing by the rate.

So, we build a temporary table with the unique values of currency1, currency2, and forward_month (always putting 'USD' in the temp table's currency1 column), and the maximum rate_date for each unique set of those three values.

Then, we add in the rates. In case it's relevant, we'll do it just like you did; If we have a case where 'USD' is in fx as currency1, we use that in preference over any rows where 'USD' is in currency2. Where 'USD' is in currrency1, we store rate directly; where it's in currency2, we need to divide by the rate, so we store 1 / rate.

Then, instead of using the function to find the rate, we join to the temporary table, based on:

the temp table's currency1 being 'USD'
the temp table's currency2 being the ccd from the other tables in the query
the rate_date matching the portdate column (which matches the passed-in @portdate)
the month matches the number of months between portdate, and the first of priceend1, priceend2, and mature that's not NULL. (Note: COALESCE takes a list of two or more values, and returns the first non-NULL value from the list; it's a little easier to read than ISNULL(x,ISNULL(y,z)), but works the same)

We make this a LEFT JOIN, so we include rows where we can't find a matching row in the temp table.

Then, we simply multiply the appropriate value (depending on the value of Sub) by the rate returned (or by 1, if we get back a NULL, either because the temp table had no match, or because the temp table had a matching row with a NULL rate).

NOTE: I combined your original two UPDATE statements into one, adding another CASE on the value of p1.Sub. To guarantee that this is working like your original two statement version, I also put in a check to see if p1.Sub is NULL; if that cannot be NULL, then you can remove that check.

Try this out and see if it works for you. It's not fully optimized (I think we could reduce the rows in the temp table by checking for currencies that are actually in our target transaction rows, for example) but it does remove the row-by-row nature of the function you were using.

NOTE: code is untested.


Set field values to newly imported rows in PostgreSQL table with existing data

I'd recommend using the COPY command from psql. You can set a DEFAULT value for a column and omit that value from the COPY command, eg:

\copy tablename(col1,col2,col3) FROM 'thefile.csv' WITH (FORMAT CSV)

Alternately, you can create a new TEMPORARY table in PgAdmin-III wtih just the columns in the CSV, import the CSV into it, and use SQL like this to merge it into the main table:

INSERT INTO realtable (col1, col2, col3, colwithdefault)
SELECT
    col1, col2, col, 'some default value'
FROM tempcsvtable;

You can use this to calculate columns based on expressions, combine and split columns, omit some rows, etc.


Getting the "Column name or number of supplied values does not match table definition" when I use CTEs in an IF statement inside a stored procedure

This error message is generally associated with an insert, which you don't have in your sample code.  Assuming this is representative of your proc, I would guess you are using an INSERT INTO ... EXEC with the proc.  Since that take the first results set, it would mean that this supplants another result set that is returned when @Load = 0.
Under that assumption, since one works, and the other does not, it would also mean those have different columns.  If it was your intent to have this result set replace the other, then the solution is to make the columns of the conditional query match those of the second.
Otherwise, your code is not representative of the problem you are encountering.


good strategy for big tables and archiving in sql server 2012

Hopefully with that size you are on Enterprise edition? If not, good luck :) (just kidding see bottom of answer)
If you are on Enterprise, I would suggest looking into partitioning. This has saved my butt a time or two when dealing with this same kind of scenario. Create your partitions on the date field and you will have to best determine how big you want those partitions. I have done one  partition per day and I've also done one partition per month. It all depends on how much history you need (remember you only get a set limit on the number of partitions per table).
If you are limiting the partition to a day AND your queries are only for a specific day, the optimizer should be able to use partition elimination to pick just the partitions it needs for your query. Also, when the time comes to purge old data after several years, partition sliding makes it really easy/quick to purge data (rather than delete statements). Same with just archiving that old data to another table completely.

Non-Enterprise ideas:
Speaking of archiving to another table, that would help as well and not require Enterprise. If this table is only used for reads for anything older than a month or a year, then you can:
1) Create a second table named Stuff_Archive
2) Move everything older than a month or a year (your preference)
3) Rename your current Stuff table to Stuff_Current
4) Create a view named Stuff that unions Stuff_Current and Stuff_Archive. This way any application that uses Stuff will still be able to read from both. And you can change your queries to just go off Stuff.
One thing I think that may help even further is to add a constraint that you will need to change each time you move records to Stuff_Archive (I have not tested this, but I plan on trying it on a DB I currently need to maintain like this). This constraint would be on the date field so it helps the optimizer know "Ok, even though I'm unioning these two tables, you only actually even need to look at table A if I'm asking for dates between X and Y and table B if I'm asking for dates between Y and Z". Theoretically, I would assume the stats of the column/index would already tell the optimizer that, but I have always wondered (again, not had time to test) if a constraint would help?

And the other non-Enterprise idea would be to create a non-clustered index on the date field and even better (if you can fit it into a maintenance window) a filtered index where dt>=somedate in the past and dt<= somedate in the future. That way any queries you write will be able to use that filtered index and it will only be as big as the data within that date range (rather than on all that history you have in your ever growing table). In otherwords: If you create a filtered index for a week, you are only dealing with 500k*7=3.5m records rather than 5billion records. You just have to make sure you rebuild that index often enough to keep that window described in the filtered where criteria within the timeframe for your queries. So if you can rebuild it daily and you only need yesterday's data, then great. But if you can only rebuild it on the weekend, then you may need to make sure stats are kept up to date throughout the week.


Index file growing endlessly - how to fix it?

You're confusing a few terms, I think.  The ndf file is not an "index file", it is just another database data file to house the data.

It sounds like you have two database data files (the MDF and NDF).  With the size of your MDF being so small, my guess would be that your database has two different filegroups (the PRIMARY filegroup using the MDF, and the "other" filegroup using the NDF file).

You need to do a little discovery here.

What are the filegroups in your database?

select
    name,
    type_desc,
    is_default
from sys.filegroups;

Which database files belong to which filegroups?

select
    file_name = df.name,
    df.physical_name,
    filegroup_name = f.name
from sys.database_files df
inner join sys.filegroups f
on df.data_space_id = f.data_space_id;

And which data space do all of the allocation units live in, and how much are they?

select
    data_space_name = ds.name,
    total_pages = sum(au.total_pages),
    total_pages_mb =
        convert(decimal(11, 2), sum(au.total_pages) * 8.0 / 1024)
from sys.allocation_units au
inner join sys.data_spaces ds
on au.data_space_id = ds.data_space_id
group by ds.name;

My guess is that you have two filegroups, PRIMARY and another one.  The other filegroup probably has the NDF file that you are looking at linked up with it.  And maybe even the "other" filegroup is the default filegroup.  This is actually a prudent approach, so that you keep user objects off of the PRIMARY filegroup.

Why do you think this is a problem?  Maybe you just have 57.3 GB worth of data in this database now.  Don't be thrown off by the differing sizes of the MDF and NDF (unless they are in the same filegroup, in which case update your question with the specifics from my diagnostic queries above).

EDIT

  Every time I try to reorganise or rebuild indexes on this database, all available RAM is eaten up for this operation, the server becomes almost unresponsive and IO activity is incredibly high

You should only be selectively rebuilding/reorganizing your indexes based off of their fragmentation levels.  As for the memory consumption, there are ways to minimize that impact such as limiting by altering the max server memory server configuration so that SQL Server doesn't over consume memory and then battle with the OS.


SQL - Date Range Broken Down by Individual Month and Year while maintaining the ID

You can join against a date dimension table.  I've included a sample Common Table Expression date dimension table - (my sample is for months instead of days).
To create a permanent Date Dimension table (also known as a calendar table), check out this post - Creating a date dimension or calendar table in SQL Server
--demo setup
CREATE TABLE #TEMP
(GRP_ID CHAR(8),
BILL_YR INT,
BEGIN_DT DATETIME,
END_DT DATETIME
)

INSERT INTO #TEMP
(GRP_ID, BILL_YR, BEGIN_DT, END_DT)
VALUES
('12345678','1','2019-01-01','2019-12-31'),
('12345678','2','2020-01-01','2020-12-31')

--common table expression date dimension
;with dates ([Date]) as (
    Select convert(date,'2019-01-01') as [Date] -- Put the start date here

    union all

    Select dateadd(month, 1, [Date])
    from dates
    where [Date] <= '2020-12-31' -- Put the end date here
)

select GRP_ID
    ,  BILL_YR
    ,  DATEPART(MONTH,[DATE]) as BEGIN_DT
    ,  DATEPART(YEAR,[DATE]) as END_DT
    from dates d
    join #temp t on d.Date >= t.BEGIN_DT
        and d.Date <= t.END_DT
option (maxrecursion 32767) -- Don't forget to use the maxrecursion option!

| GRP_ID   | BILL_YR | BEGIN_DT | END_DT |
|----------|---------|----------|--------|
| 12345678 | 1       | 1        | 2019   |
| 12345678 | 1       | 2        | 2019   |
| 12345678 | 1       | 3        | 2019   |
| 12345678 | 1       | 4        | 2019   |
| 12345678 | 1       | 5        | 2019   |
| 12345678 | 1       | 6        | 2019   |
| 12345678 | 1       | 7        | 2019   |
| 12345678 | 1       | 8        | 2019   |
| 12345678 | 1       | 9        | 2019   |
| 12345678 | 1       | 10       | 2019   |
| 12345678 | 1       | 11       | 2019   |
| 12345678 | 1       | 12       | 2019   |
| 12345678 | 2       | 1        | 2020   |
| 12345678 | 2       | 2        | 2020   |
| 12345678 | 2       | 3        | 2020   |
| 12345678 | 2       | 4        | 2020   |
| 12345678 | 2       | 5        | 2020   |
| 12345678 | 2       | 6        | 2020   |
| 12345678 | 2       | 7        | 2020   |
| 12345678 | 2       | 8        | 2020   |
| 12345678 | 2       | 9        | 2020   |
| 12345678 | 2       | 10       | 2020   |
| 12345678 | 2       | 11       | 2020   |
| 12345678 | 2       | 12       | 2020   |


Compatibility level 100 on SQL Server 2008 R2

Your question is too wide I am not sure a clear answer can be given but I would try. As such sitting from here I cannot tell you what could break after chaning compatibility level. Only you can find it out by running each and evry procedures,functions, views and triggers  you have.

Microsoft has documented breaking changes in SQl server 2008 R2 before changing the compatibility level you must go through this article.

You must also read Change database compatibility level BOL document to see what could get affected after changing compatibility level. Please read complete article most of the things are documented.

Did you ran upgrade advisor before you migrated database to 2008 R2 ? that should as well have pointed out breaking changes.

The good thing with compatibility level is it can be immediately changed to previous value if you face issue. So you also have got hit and try method to check out


Checking SQL AlwaysOn backup target with fn_hadr_backup_is_preferred_replica, different answers with different permissions

You have to GRANT the user view server state and  view definition on availability group.


Cannot login to postgres with newly created users

2 things were required to solve this problem

service postgresql reload and I had to give postgres a password.
Unaware how to connect externally without a password but I wouldn't want to anyways so not concerned about it.

I also failed to have a new user function properly, only the default one worked. But it does work now.


Postresql replication pg_basebackup no pg_hbaconf entry for replication connection

You have to grant access in replication in pg_hba.conf.
Here's the line you should add:

host    replication     replicator      10.0.2.15/32               trust

You will find documentation here:
https://www.postgresql.org/docs/9.6/static/auth-pg-hba-conf.html

Here's the phrase that explain everything:

  The value replication specifies that the record matches if a replication connection is requested (note that replication connections do not specify any particular database).