Mongodb: Determining shard key strategy on compound index

MongoDB supports compound indexes, where a single index structure holds references to multiple fields ( As MongoDB Limits and Thresholds MongoDB imposes a limit of 31 fields for any compound index.) within a collection’s documents. The following diagram illustrates an example of a compound index on two fields:

  How the data will be distributed to shards during insertion and how
  the range of a chunks are calculated during insertion?

Starting in version 2.6, MongoDB can use  index intersection to fulfill queries. The choice between creating compound indexes that support your queries or relying on index intersection depends on the specifics of your system.

Index intersection does not eliminate the need for creating compound indexes. However, because both the list order (i.e. the order in which the keys are listed in the index) and the sort order (i.e. ascending or descending), matter in compound indexes, a compound index may not support a query condition that does not include the index prefix keys or that specifies a different sort order.

For example, if a collection orders has the following compound index, with the status field listed before the ord_date field:

{ status: 1, ord_date: -1 }

The compound index can support the following queries:

db.orders.find( { status: { $in: ["A", "P" ] } } )
db.orders.find(
   {
     ord_date: { $gt: new Date("2014-02-01") },
     status: {$in:[ "P", "A" ] }
   }
)

But not the following two queries:

db.orders.find( { ord_date: { $gt: new Date("2014-02-01") } } )
db.orders.find( { } ).sort( { ord_date: 1 } )

However, if the collection has two separate indexes:

{ status: 1 }
{ ord_date: -1 }

The two indexes can, either individually or through index intersection, support all four aforementioned queries.

  Since the PropertyId is random value. Does the compound key fits the
  definition of monotonically increasing value?

For the fastest processing, ensure that your indexes fit entirely in RAM so that the system can avoid reading the index from disk.

To check the size of your indexes, use the db.collection.totalIndexSize() helper, which returns data in bytes:

> db.collection.totalIndexSize()
4294976499

The above example shows an index size of almost 4.3 gigabytes. To ensure this index fits in RAM, you must not only have more than that much RAM available but also must have RAM available for the rest of the working set.

If you have and use multiple collections, you must consider the size of all indexes on all collections. The indexes and the working set must be able to fit in memory at the same time.

For further your ref Indexing Strategies


What we would like to also see in the email is WHY and/or WHO.

Then you have quite a good bit of development work ahead of you. Seriously.

Everyone asks this same question, but it is not an easy one to answer with any more resolution than "It failed over at this time." Things such as virtualization make this even harder as the tools and the hypervisor itself may do things that are outside the purview of your sandbox.

However, to get you started, places to scrub for data:

SQL Server Errorlog
Application Event Log
System Event Log
Cluster Log/Clustering Event Log
Hypervisor Logs/Database
Internal Lists of Downtimes/Upgrades/Patches/Etc.

Best of luck.

  We have other teams in there that restart, test and delete things all the time and we would like to know if this is someone working on something vs something wrong.

That's an organizational information sharing/Administration issue and not one you're going to have much help in dealing with through a simple SQL Server Alert. In fact, to do this properly, you'll want to take SQL Server out of the picture!

Most likely you're going to need to write a service that constantly consumes event logs, traces, error logs, etc., and then takes action when issues occur.


Using pgpool and PostgreSQL in differents hosts.

They do not have to be on the same host; otherwise lots of the useful things you can do with PgPool-II would not be possible.

PgPool-II communicates with PostgreSQL over PostgreSQL's TCP/IP protocol.

The reason it wouldn't start on the same host is probably because you were trying to run both PgPool-II and PostgreSQL on the same port (5432).


for Insert multiple rows in one single insert MySQL

The elapsed time, not the CPU time is more interesting.
You are measuring the client time, not the server time.
There are many factors on the server that get 'bad' for large chunks: buffers, undo log, replication delays, etc.
The parse time is the main overhead (on the server) for short chunks.
You have not specified the row size; this has some impact on the client, network, and server.
What connection between client and server?  Socket?  LAN?  WAN?  What is the latency between them?  On a WAN, the network delays would overwhelm perhaps all other considerations.
`key_buffer_size applies only to MyISAM; I hope you are not using that Engine.
Were there any indexes on the table?  If so, what?  If, for example, the PRIMARY KEY were a UUID, there could have a huge impact.  Also, UNIQUE keys need to check for duplicates.  (Again, this is a server task.)

My experiments show that chunks of 100-1000 rows is close to optimal in many cases.  (This happens to somewhat agree with your findings, but that is coincidental, since I looked at different timings.)


SQL Server Alerts (notify operator when job failed)

Regarding the status of job run, that's the correct steps.

However, its recommended you configure the alerts for any database corruption, HW/SW failures or any other cause for an outage. Read here to configure the same.

In order to test, create a TEST job and purposefully make it fail and see if you receive the failure notification email or not.


After Update Trigger When Multiple Rows To Update

You are right, there is a virtual table called "inserted" which is what you would need to join on. This table contains all the rows which have been inserted in the original table insert statement. So something like:

CREATE TRIGGER [dbo].[UpdateFields]
ON  [dbo].[TFData]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE a
Set
a.DateOfInjury = b.InjurDate
,a.GameNum = c.GameNum
FROM [dbo].[InjuryReport] a
INNER JOIN [dbo].[GameStats] b
ON a.playnum = b.playnum
INNER JOIN [dbo].[GameSched] c
On b.gamenum = c.gamenum
INNER JOIN inserted i
ON b.linkfield = i.linkfield


What does it mean when a merge replication generation has no changes?

A generation with 0 changes is not indication of a problem. It is just indication that the generation contains no changes.

If you look at the statement you quoted from BOL, it reads "Generations are closed each time the Merge Agent runs", so this means that the Merge Agent could run and close the generations with no changes if no changes were performed on the publisher and/or subscribers.


Need to take backup from other server on network using AutoMySQLBackup

1: Are you able to do the backup? as i see an error on that message i got confused about it.

2: Did you set up a CRON to run the script daily? First thing that come to mind was to run a ssh copy of the backup to server B in the CRONjob (using SCP command)


MySQL Tuner returns: MySQL's maximum memory usage is dangerously high

RAM consumption for mysql/mariadb (and probably for other engines) can be represented this way:

+----+----------------+-------------------+-----------------------------+
| OS | Other services | DB common caching |      DB clients buffers     |
|    |                |                   +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|    |                |                   | | | | | | | | | | | | | | | |
+----+----------------+-------------------+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Common DB caching is a sum of key_buffer and InnoDB_buffer_pool values.
Buffer size per each client is a sum of read_buffer, read_rnd_buffer, join_buffer and sort_buffer. This amount is multiplied by max_connections value. Both values together shouldn't be bigger than reasonable part of the whole RAM. Some RAM is required by host's OS itself and some amount of RAM should be leaved for 3d-party services on the host.

Maximum possible memory usage: 9.0G (116.34% of installed RAM) means that you have dedicated to the mysql service even more RAM than your host physically has installed. This doesn't mean some problems until you'll get too many simultaneous connections to the DB. And even then you'll meet a slowdown first because of swapping.

If you want to tune your mysql perfectly you should know how many connections can be established at the same time. This value is shown here:

Highest usage of available connections: 40% (61/151)

Then you have to decide how much RAM each client needs. This value depend on your DB data and queries. If your DB isn't very critical you can start from some reasonable values and then decrease them until the overall performance goes down significantly. Then increase them 2x. But in general 1-2MB for each client's buffer and 4-8MB in total is good enough.
Now multiply total client's buffers by max_connections value. By default it is 151 but you can change it to the 1.2-1.5x of the real peak number of connections.

Say, for max 61 actual connections I'll reserve max_connections = 80 with 8MB buffers per connection. That mean that I reserved 80x8=640MB of RAM.

Now I leave 1GB for OS itself and 512MB for other services. The remainder is:

8GB - 1GB - 512MB - 640MB = 6016MB

Let's round it to the 6GB. This RAM you can split between MyISAM key_buffer and innodb_buffer_pool. If you have no MyISAM tables in the DB you can set key_buffer_size = 256k because mariadb 10.1.x is still used MyISAM/ARIA for internals. And all the rest could be dedicated to the innodb caching: innodb_buffer_pool_size = 6G

Now you'll get something like that:

Total buffers: 6.G global + 8M per thread (80 max threads)
Maximum possible memory usage: 6.7G (84% of installed RAM)


Delete the folder C:\Program Files (x86)\Microsoft SQL Server\140\Tools\ and then try the installer again. You may need to reboot to release all file handles.


Grant read/read-write access to SSAS cubes

Writeback permissions are a bit tricky. I documented them here. The relevant part is:

  Next, build a separate Writeback role. On the Cubes tab, grant
  Read/Write, check Process, and (optionally) Drillthrough permissions.
  I’m frankly not sure why you need Process permissions to perform ROLAP
  writeback, but it appears you need Process permissions for ROLAP or
  MOLAP writeback.

  In the Data Sources tab, make sure to grant Read permissions on the
  data source used for writeback. For example, when I created my
  writeback partition, I used the Greg Test data source to direct that
  writeback table to live in another database (such as a database with
  Full recovery model).

Sounds like you already figured it out but we can document this for posterity.


How to reduce table duplication, while ensuring integrity of the database?

I might suggest to reorganize schema, to make it more scalable.

DROP columns entityId and entityType.
CREATE an Objects (or Entities) table which will store public / shared information for all objects / entities. It's primary key will migrate to Companies and to Houses with 1:1 relation. For one object might exist only one company OR only one house. Also, you will have to remove AUTO_INCREMENT from Companies and Houses, because in this case they will no longer generate identifiers themselves.
Add foreign key to Contacts table from shared Objects table - contacts might exist for any type of object.

Structure might look like this:

You might obtain entity type by following query:

SELECT
    cnt.*,
    CASE
        WHEN NOT cmp.id IS NULL THEN 1
        WHEN NOT hs.id IS NULL THEN 2
    END as entityType
FROM
    Contacts as cnt
        INNER JOIN Objects as obj ON cnt.objectId = obj.id
        LEFT JOIN Companies as cmp ON cmp.id = obj.id
        LEFT JOIN Houses as hs ON cmp.id = hs.id

Also, note that entityId becomes objectId in this case.

Here is a CREATE-script for benchmarking and test:

CREATE TABLE IF NOT EXISTS `Objects` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `shared_property1` VARCHAR(45) NULL,
  `shared_property2` VARCHAR(45) NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Companies` (
  `id` BIGINT NOT NULL,
  `company_name` VARCHAR(300) NULL,
  `private_property1` VARCHAR(45) NULL,
  `private_property2` VARCHAR(45) NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `FK_Companies_Objects`
    FOREIGN KEY (`id`)
    REFERENCES `Objects` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Houses` (
  `id` BIGINT NOT NULL,
  `address` VARCHAR(300) NULL,
  `private_property1` VARCHAR(45) NULL,
  `private_property2` VARCHAR(45) NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `FK_Houses_Objects`
    FOREIGN KEY (`id`)
    REFERENCES `Objects` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Contacts` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `objectId` BIGINT NOT NULL,
  `name` VARCHAR(300) NULL,
  `phone` VARCHAR(300) NULL,
  `email` VARCHAR(300) NULL,

  PRIMARY KEY (`id`),

  INDEX `FK_Contacts_Objects` (`objectId` ASC),

  CONSTRAINT `FK_Contacts_Objects`
    FOREIGN KEY (`objectId`)
    REFERENCES `Objects` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


Oracle dbms_xplan.Display() equivalent to SQL Server 2017

SET STATISTICS XML ON;
GO
SELECT ... FROM ...;

This will produce a rowset with a clickable XML showplan:

Clicking on the XML will render a new tab with the graphical showplan. If you just want text output, instead of STATISTICS XML, use:

SET SHOWPLAN_TEXT ON;
GO
SELECT ... FROM ...;


Find addresses shared by more than one customer_id where customer_name is is distinct

The query above groups by ADDRESS, CUSTOMER_ID and CUSTOMER_NAME. Since CUSTOMER_ID is unique, the output will have 1 row for each CUSTOMER_ID.

This query gives the correct result:

SELECT
    CUSTOMER_ID = MIN(ct.CUSTOMER_ID)
    , ct.CUSTOMER_NAME
    , ct. ADDRESS
    , MEMBER_SINCE = MIN(ct.MEMBER_SINCE)
    , cnt.c
FROM CUSTOMER_TABLE ct
INNER JOIN (
    SELECT ADDRESS, COUNT(DISTINCT CUSTOMER_NAME) as c
    FROM CUSTOMER_TABLE
    GROUP BY ADDRESS
    HAVING COUNT(ADDRESS) > 1
) as cnt ON cnt.ADDRESS = ct.ADDRESS
GROUP BY ct.ADDRESS, ct.CUSTOMER_NAME, cnt.c
ORDER BY MIN(ct.CUSTOMER_ID);

It should group by ADDRESS andCUSTOMER_NAMEonly and pick one of theCUSTOMER_IDandMEMBER_SINCE. I choose theMIN(value)` here.

Since you also need the count of distinct CUSTOMER_ID with similar ADDRESS, it has been added to a subquery.

Result:

CUSTOMER_ID | CUSTOMER_NAME | ADDRESS           | MEMBER_SINCE              | COUNT
A1000       | John Doe      | 123 West Lane     | 2005-02-23 00:00:00.000   | 3
A1002       | Tom White     | 456 East Street   | 2006-05-07 00:00:00.000   | 4
A1003       | Frank Smith   | 123 West Lane     | 2006-12-12 00:00:00.000   | 3
A1004       | Lisa Simpson  | 456 East Street   | 2007-07-19 00:00:00.000   | 4
A1006       | Darth Vader   | 123 West Lane     | 2008-09-29 00:00:00.000   | 3
A1007       | John Doe      | 456 East Street   | 2007-05-23 00:00:00.000   | 4
A1008       | Bart Star     | 456 East Street   | 2006-04-19 00:00:00.000   | 4

Note that I don't have access to SAP-Hana. However it seems to be pretty standard SQL and with query should work on most RDBMS.


Error text: "Key ... is not present in table" even though the key is there?

Someone named "RhodiumToad" in the #postgresql channel on IRC pointed out the problem.  Because I didn't explicitly state the column name, the 2nd table is referencing the primary key column "id" from the first table.  Instead, I wanted it to reference the drug_code column.

So where I had previously defined the 2nd table as:

CREATE TABLE dpd.inactive
(
    id          BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    drug_code   INTEGER NOT NULL REFERENCES dpd.qrym_drug_product,
    ...

I had to fix it up to be this:

CREATE TABLE dpd.inactive
(
    id          BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    drug_code   INTEGER NOT NULL REFERENCES dpd.qrym_drug_product (drug_code),
    ...


Chain replication M1 (5.1) -> S1 (5.5) -> S2 (5.6) to limit downtime for an upgrade 5.1 to 5.6

I have a caution to share with you in your setup.

Using MIXED for all 3 servers would be OK, but I would prefer you use STATEMENT during this migration time. Why ??? On Jan 02, 2015 I wrote the answer to this post : mysql replication master 5.5 slave 5.1 error on create database

I discussed how the format of binlog events can change between versions.

In that post, I gave an example that my former manager (at the NY Times) discovered. There was a setup of MySQL 5.5 master with MySQL 5.6 slave. Replication would break very quickly and repeatedly. My then manager found an odd trace dump in the error log with the phrase unpack:

/usr/sbin/mysqld(_Z10unpack_rowPK14Relay_log_infoP5TABLEjPKhPK9st_bitmapPS5_PmS5_+0x43f)[0x898b7f]
/usr/sbin/mysqld(_ZN14Rows_log_event24do_index_scan_and_updateEPK14Relay_log_info+0x100)[0x873c50]
/usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0x852)[0x877c32]
/usr/sbin/mysqld(_ZN9Log_event11apply_eventEP14Relay_log_info+0x74)[0x87fb54]
/usr/sbin/mysqld(_Z26apply_event_and_update_posPP9Log_eventP3THDP14Relay_log_info+0x263)[0x8aefd3]

Please note the Z10unpack_rowPK14Relay_log_info

What was the issue ??? It turned out that DATETIME fields would break replication. How ??? MySQL 5.6 started supporting microseconds in DATETIME values. MySQL 5.5 did not. So, when replication threads attempted to unpack the binlog event from the relay log, the format for DATETIME was wrong in the eyes of MySQL 5.6.

My manager's workaround ??? Switch to STATEMENT for binlog_format, use statement-based replication. Everything went fine from there.

You said you are using MIXED and that's OK. I would just make sure you get away from the 5.1 and 5.5 server as fast as you can (Do the Migration in Haste).

Such a setup is fine. A current colleague at my current employer did MySQL 5.1 to 5.7 in stages. No issues (I assumed he used MIXED or STATEMENT).

You should try this setup in DEV/STG and make sure DATETIME value don't keel over replication.


Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity

At first glance, I would say that your innodb_log_file_size is way too small. It should be bigger to do two things:

Accommodate any big BLOB or TEXT fields
Holding bigger transactions

Here is what you should do for now to see if it helps:
STEP 01) Change the following in /etc/my.cnf
[mysqld]
innodb_log_buffer_size          = 32M
innodb_buffer_pool_size         = 3G
innodb_log_file_size            = 768M

STEP 02) service mysql stop
STEP 03) rm -f /var/lib/mysql/ib_logfile*
STEP 04) service mysql start
This will rebuild the following files

/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1

Give it a Try !!!
UPDATE 2013-07-03 12:37 EDT
I have updated my other posts on this and missed this one
ButtleButkus just commented at 2013-07-03 07:18:56 EDT

Wouldn't it be advisable to copy the ib_logfile* to another location for backup before deleting them?

Since there could be unfinished transactional data inside, here is what should be done
STEP 01) Change the following in /etc/my.cnf
[mysqld]
innodb_log_buffer_size          = 32M
innodb_buffer_pool_size         = 3G
innodb_log_file_size            = 768M

STEP 02) mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0;"
STEP 03) service mysql stop
STEP 04) rm -f /var/lib/mysql/ib_logfile*
STEP 05) service mysql start
I added SET GLOBAL innodb_fast_shutdown = 0;. What does that do? It forces InnoDB to completely purge transactional changes from all of InnoDB moving parts, including the transactional logs (ib_logfile0, ib_logfile1). Thus, there is no need to backup the old ib_logfile0, ib_logfile1. If deleting them makes you nervous, then make Step 04
mv /var/lib/mysql/ib_logfile* ..

to put the old logs in /var/lib. If the recreation of the logs is successful and mysqld starts up, then you can delete the old logs.
I have been using this feature for a year now. I have updated my other posts to reflect this...

May 20, 2013 : Innodb one file per tablespace
May 05, 2013 : Issue after moving the ib_logfile1 and ib_logfile0 files
Jan 10, 2013 : Finding and fixing InnoDB index corruption
Dec 17, 2012 : MySQL Start/Stop
Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'? (Last Update)
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons? (Last Update)
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine? (Added as Step 03 on Jun 04, 2013)

If there are other older posts of mine where I do not mention innodb_fast_shutdown, let me know so I can update it. Thanks again, ButtleButkus.


How Does Max Server Memory Affect PLE on a Host That Is Not Memory Bound?

I'd suspect most of the memory was in use by the procedure cache or another component before you set the maximum memory limit. When you change the maximum memory limit, caches are flushed (you'll see this in the error log), which would have made more space available for caching data pages, which would in turn improve PLE.

If that is what happened, the problem will likely return in time as usage from the components that were using memory before the flush grows back. As you may know, Glenn Berry has some useful queries for analysing memory usage in SQL Server.

As somewhat of an aside, 1.6 or 1.7 GB was roughly the maximum memory 32-bit SQL Server could use on 32-bit Windows under a default configuration. There are some settings that can allow it to use more memory, but they can cause memory starvation of the kernel or user processes if not carefully managed. On newer, 64-bit versions of Windows and SQL Server, memory management is generally far less problematic.


I use to follow this path:

create all table, only structure (fields), without any Primary Keys nor Foreign Key, without any Index, the order doesn't matter

1.1 inject predefined (aka static) datas

create Primary Keys & Indexes
create Constraints if any
finally, create inter-table relations aka Foreign keys

Gains expected:

no time spent to manage table order (your primary goal)

=> in the worst case, you can order your tables by an alphabetic order
declarations are grouped by meaning (table then PK & Indexes then Constraints then FK), a gain of visibility and accessibility
easiest to debug
you can store all these items all in one file (for a simple project), or one file by step (big or complex project)


From Performance analysis of MySQL's FULLTEXT indexes and LIKE queries for full text search by Henning Koch:

  FULLTEXT performs better when your text has low redundancy
  FULLTEXT performance differs by a factor of 78 between a vocabulary of 1,000 words and 100,000 words. I guess that larger vocabularies result in a very wide but shallow inverted index that can quickly determine if a query has matches or not. An educated person has a passive vocabulary of 15,000 to 20,000 words, so FULLTEXT should work well for natural language texts.


Launching a python script from an insert trigger

Don't make your user transaction wait for the (hopefully!) successful completion of the Python script. Your entire transaction sits there and waits for this external process to run, try to send mail, etc. I doubt the e-mail really has to go out that instant - especially given you can't control any delays it has as it gets routed to the recipient's inbox anyway. Why not just run the process more frequently, if timing is so important?

Please give this tip a look-through.

If you really, really, really want to do this the wrong way, you can just enable xp_cmdshell and fire away.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

Now, assuming the user has access to xp_cmdshell and/or the SQL Server service account can see the folder where the python script is stored, you should be able to do this from within your trigger:

EXEC master..xp_cmdshell N'C:\Python27\python.exe C:\source\NotifyAgents.py';

As an aside, you should state in your question that you aware that this is a very bad thingTM, but you are not concerned with that, for whatever reason. I still don't think you're going to get as real time as you expect, even if you do fire this from the trigger. Have you considered database mail instead of python?


Get Total in a single line in SQL Server

You need to modify the GROUP BY and the ORDER BY clauses.

Try this:

SELECT
       ISNULL([Task IT Assets], [Project IT Asset]) AS Asset ,
       SUM(P.ActualWork) AS taskHours
 FROM
      IR.TimesheetHoursProjectTask p
 WHERE
      p.ActualWork > 0 AND
      ([Task IT Assets] IS NOT NULL OR [Project IT Asset] IS NOT NULL)
 GROUP BY
      ISNULL([Task IT Assets], [Project IT Asset])
 ORDER BY
      SUM(P.ActualWork) DESC

As an aside, note that your table is literally named "IR.TimesheetHoursProjectTask" and not "TimesheetHoursProjectTask" under the "IR" schema. To specify the later, using the [] notation, it would be [IR].[TimesheetHoursProjectTask].


I've recently been working on a powershell script that, when pointed
  at an instance, iterates through the databases on that instance and
  performs those three tasks on demand.

  My question is whether anyone can see any downside by doing this for
  all instances, I.e. Having a single powershell script on our DBA
  server that iterates through a list of instances on a windows schedule
  and executes the maintenance tasks. Any errors would be handled /
  written out to logs etc.

Your problems will be based on how you wrote your process. So just a few things I can think of right off to consider. Although any answer is going to an opinion of the writer.

Does it do parallel processing or sequential?

If you are using sequential processing you will never know when one particular server is going to have a load from the index maintenance or stats maintenance. As well, it could also vary each time it runs based on other processes running on a given instance. Something to consider is if your maintenance window is large enough to handle that fluctuation.

When you run it from one server are you using something like Invoke-Sqlcmd to run it or Invoke-Command (PowerShell remote).

This is considering when you run a command in PowerShell will you process any amount of data in PowerShell or do it all in SQL Server. If you do any kind of piping with the data consider how it will be processed. Certain things (piping to an executable) can require all the data to be cached in memory before it moves down the pipe to the next command.


Can SQL Server disable IntelliSense on SSMS?

Permissions
As the comment answers you've received so far imply, the most likely reason for this problem is that you don't have the appropriate permissions to that database.  This is (rather vaguely) documented here:
Troubleshooting IntelliSense -> Database Engine Query IntelliSense

Completion lists do not include database objects for which you do not have permissions. IntelliSense flags references to objects for which you do have permissions. For example, if you open a script that is written by someone else, any references to objects for which that person has permissions and you do not are flagged as incorrect.

The easiest way to fix this is to have a DBA grant the "View Definition" privilege to the database user that you're using to connect to this database.
For example, I can create this login, and a database user for a specific database, on my local SQL Server 2016 instance:
USE [master]
GO
CREATE LOGIN [nerd-login] WITH PASSWORD = 'NerdPassword1';

USE [SomeDatabase]
GO
CREATE USER [nerd-user] FOR LOGIN [nerd-login] WITH DEFAULT_SCHEMA=[dbo]
GO

And if I login as nerd-login, I can connect to the SomeDatabase database, but I can't get any intellisense on it.  But, after running this command:
USE [SomeDatabase]
GO
GRANT VIEW DEFINITION TO [nerd-user];

I'm able to get intellisense on the objects in the SomeDatabase database.
This can also be granted more broadly at the instance level (by executing GRANT VIEW ANY DEFINITION in the master database context), or at the per-object level.
Latency
In the comments here, you've mentioned:

Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.

Factors like the amount of metadata in a particular database (# of tables, views, columns, functions, stored procedures, etc), the network speed between you and the database, and how busy the other database is, can all affect intellisense.
Here's a pretty thorough treatment of the latency issue in a blog post from Aaron Bertrand:
Troubleshooting IntelliSense in SQL Server Management Studio 2012

And finally, you may just need to be patient. Check your network speed, the general health of the server, and be understanding if your metadata is quite large - if you are running SAP, for example, there's a lot more data to bring across than AdventureWorks.
When SSMS is attempting to get metadata from the destination server, it may time out before it starts receiving results. This timeout is hard-coded to two seconds in SSMS 2012, but in previous versions, it would wait for the database connection to time out (which could be 30 seconds or more). It will continue trying in the background, so you may not see the drop-down appear right away, but it could appear after successive attempts at pressing Ctrl+J.
You can force it to try to reload by pressing Ctrl+Shift+R or the menu option Edit > IntelliSense > Refresh Local Cache. Once metadata is retrieved in the background, the data is cached in local memory. If the connection is working and none of the above factors are in play, the cache will eventually be populated and you should be able to use all of the functionality.
But again, on SSMS 2008 or SSMS 2008 R2, this may take a little bit longer because of the more relaxed timeout.

Note: I realize that's about SSMS 2012, but the same general principles still apply


Oracle 11g database datafile addition to ASM without '+' sign

If you miss the '+' sign, there will be several scenarios:

db_create_file_dest is set and points to ASM (i.e: +DATA):
The database will take 'DATA' as an alias and will create ASM file on the destination associated with the 'DATA' alias that will be located at the ASM +DATA root directory.
db_create_file_dest is set and points to filesystem:
It should be quite obvious that you'll have a new 'DATA' file in the location pointed at the parameter.
db_create_file_dest is not set (worst scenario):
The database will default to $ORACLE_HOME/dbs and create a datafile there with the name 'DATA'.

A final piece of advice: Set the db_create_file_dest to point to the ASM DATA disk group and remove the datafile name parameter from your command:

alter tablespace CWSC add datafile size 100M AUTOEXTEND ON;

As you may know, even if you give a name to the datafile, when it's created on ASM, that name becomes an alias and the real filename is set by the ASM instance itself.

I hope this info helps you.


mysql 8 - rds - 1022 Can't write; duplicate key in table

That's the wrong way to use DATE_FORMAT.  It is given a DATE or DATETIME and a string providing the 'formatting'.  Example:

mysql> SELECT DATE_FORMAT(NOW(), "%Y-%m-%d"), DATE(NOW());
+--------------------------------+-------------+
| DATE_FORMAT(NOW(), "%Y-%m-%d") | DATE(NOW()) |
+--------------------------------+-------------+
| 2019-01-30                     | 2019-01-30  |
+--------------------------------+-------------+

In your case, it would be something like

  GROUP BY DATE_FORMAT(performance_stats_date, "%Y-%m-%d")

But this is even simpler:

  GROUP BY DATE(performance_stats_date)

For further discussion, please provide

SHOW VARIABLES LIKE 'char%'
SHOW CREATE TABLE` for each table.
the entire query


According to the Glossary from the Oracle Documentation

  System Change Number. A database ordering primitive. The value of an SCN is the logical point in time at which changes are made to a database.

There is lots of info you can easily Google

http://oracledbascriptsfromajith.blogspot.com/2009/05/understanding-scnsystem-change-number.html
http://www.adp-gmbh.ch/ora/concepts/scn.html

Even the DBA StackExchange already has questions around the SCN

How do I find my current SCN?
RMAN-06054: media recovery requesting unknown archived log, Where is SCN kept?
Commit vs Fast Commit vs Commit Cleanout in Oracle Database
more...


'insert' permission should be all you need to do an insert:

create database stack;
create user dummy;
create table stack.t(v integer primary key);
grant insert on stack.* to dummy;

as dummy:

insert into stack.t values(1);
Query OK, 1 row affected (0.00 sec)

select * from stack.t;
ERROR 1142 (42000): SELECT command denied to user 'dummy'@'localhost' for table 't'

and the grant like this will even apply to tables created after the grant was made - it gives a database privilege

As you also want to select you will also need to grantselecton stack.* to dummy


Set a check constraint for a set of conditions which aren't universally true

Since the status column is not nullable, the check constraint expression in your own solution

case
  when status = 2 and PatientId is null then false
  else true
end

is equivalent to this more concise expression:

not (status = 2 and PatientId is null)

Applying De Morgan's laws, you can transform it further:

        not (status = 2 and PatientId is null)   →

    →   not (status = 2) or not (PatientId is null)   →

    →   status <> 2 or PatientId is not null

which is what McNets suggested in the comments.

Using the final expression, your check constraint definition would look like this:

alter table tbl
  add constraint cons_name
  check (status <> 2 or PatientId is not null);


Difference in Value between latest two rows?

Another way using lag() to access the previous value and TOP to only get the latest.

SELECT TOP(1)
       value - lag(value, 1, 0) OVER (ORDER BY registerdate)
       FROM elbat
       ORDER BY registerdate DESC;


Probably the least complicated option would be to select all your data into a new table with the correct column names, filtering out the row of values containing the header values. From there, you can drop the malformed table, and rename the new one.

Example:

CREATE TABLE dbo.wrongo(wrong INT, [column] INT, names INT);
CREATE TABLE dbo.righto(correct INT, [column] INT, names INT);

INSERT dbo.righto ( correct, [column], names )
SELECT wrong, [column], names
FROM dbo.wrongo
WHERE wrongo.wrong <> 'column name'; --this will throw an error, but you get the idea.

DROP TABLE dbo.wrongo;

EXEC sp_rename 'dbo.righto', 'wrongo', 'OBJECT';


If NULL value of @SubDesc1 means "return all records, do not apply filtering", then

SELECT *
FROM Table1
WHERE Category = 'Category1'
  AND [Description] ='ABC'
  AND (    SubDescription1 = @SubDesc1
        OR @SubDesc1 IS NULL)

If NULL value of @SubDesc1 means "return only those records, where the field IS NULL too", then

SELECT *
FROM Table1
WHERE Category = 'Category1'
  AND [Description] ='ABC'
  AND (    SubDescription1 = @SubDesc1
        OR COALESCE(@SubDesc1, SubDescription1) IS NULL )


whats best practice for Log Truncation in SQL Server?

Don't truncate or shrink your log files.
See point 1

Why do you think this is a good idea?

With simple recovery, the internal log entries are removed periodically (ready to be reused) but the file requires no more processing.

The log file is the size it is because of your day to day activities. If you shrink the log file you'll end up with poor performance.


Replication

"Database Replication" refers to dynamically copying any incoming insert/update/delete statements being made to one database to another identical database. The primary and secondary databases are considered "replicas" because at all times they contain the same data produced by the same history, though the secondary may be delayed by a small stretch of time. The secondary may be used for read-only queries in place of the primary. And the secondary may be transformed into a primary in case of fail-over.

Migration

What you are seeking is loosely called  "Database Migration", also called schema migration. The idea here is to reproduce the same structure (DDL) as another database. But each produced database is independent of the others, each having different users’ data stored in them.

A prime example example is multi-tenancy.
Another use might be as part of a vertical-market app being licensed to various customers where on each new sale you need to establish a fresh database in a certain configuration as expected by the app.

Migration tools

Developers and DBAs used to do this migration work manually, devising their own ad-hoc solutions or habits. Fortunately we now have a choice of tools to help with this tedious though crucial chore.

Flyway

My favorite tool is Flyway. Built in Java, but wrapped as command-line  executables so you can use from scripts or other tools.

Flyway runs through a collection of SQL scripts, and optionally certain Java libraries you may write. The order of execution is determined by a certain file naming scheme.

A table is automatically created within the database to track scripts run. When new scripts are encountered, Flyway detects them and appropriately runs those that have not yet been applied to that particular database.

Flyway or other such tools are extremely helpful not only in production but also in testing and development. You can recreate a throw-away database automatically, and even bring it up to any particular point in the history of its schema evolution.

Anyone considering Flyway should also consider another quite similar product: Liquibase.

Other choices exist as well, such as the commercial tool Redgate.

Templates

Some databases such as Postgres provide for templates where you can create some tables and indexes and so in a database. After that, you can create a fresh new database whose initial state is copied from that state.

Indeed, Postgres always works that way, where template0 and template1 databases are used transparently by default when you ask for a database without explicitly specifying any other template.

Templates only solve the genesis problem, getting started. You will almost certainly evolve your database later. You'll add or drop tables, columns, indexes, and so on. You may need to run code to convert or update existing data en masse. For this you'll want to use migration tools, as the templates cannot help you after that initial genesis. So in my opinion, I would not bother with defining a template, instead using migration scripts for all that initial setup.


Postgres CTE execution guarantees, inlining

Yes, this execution guaranteed . Postgres will not inline it in the way you mentioned.

  A useful property of WITH queries is that they are evaluated only once
  per execution of the parent query, even if they are referred to more
  than once by the parent query or sibling WITH queries. Thus, expensive
  calculations that are needed in multiple places can be placed within a
  WITH query to avoid redundant work. Another possible application is to
  prevent unwanted multiple evaluations of functions with side-effects.
   (from Postgres doc


MongoDB allow access only from local server

You have to enable MongoDB access control. This uses a bindIp value in the mongod.conf file that by default listens to connections locally (e.g. bindIp:127.0.0.1 or with another local address, bindIP:127.0.0.1,10.0.0.10).

For that, you will need to create an admin or root user by doing this:

use admin
db.createUser(
  {
    user: "root",
    pwd: "abc123",
    roles: [ { role: "root", db: "admin" } ]
  }
)

Something similar you will need to do to create a user for your application:

use appDatabase
db.createUser(
  {
    user: "appUser",
    pwd: "appUserPassword",
    roles: [ { role: "readWrite", db: "appDatabase" } ]
  }
)

Then, in your mongod.conf file (in a CentOS server is located at /etc/mongod.conf), do the following:

Uncomment 'security'.
Add below it 'authorization: enable' (keep authentication commented out).
Make sure the bindIp value is set to 127.0.0.1

Then, when logged onto your server, if you need to connect to your MongoDB instance, do that with the admin/root user you just created:

mongo -u root -p --authenticationDatabase admin

You should also make sure you can successfully log onto mongo with your appUser:

mongo -u appUser -p --authenticationDatabase appDatabase

From your application, you will need to connect with an URL similar to this one:

mongodb://appUser:appUserPassword@localhost:27017/appDatabase?authSource=appDatabase

Reference:
MongoDB - Enable Authentication |
MongoDB - Configuration File Options


SQL Server Log Shipping: File copy options?

If I roll my own log shipping, what are established ways to move the log file?

Why do you want to roll out your own logshipping solution ? Microsoft has put in a lot of effort for giving out-of-box Logshipping from SQL 2005 and up. This is tested by millions of customers since sql 2005, so highly unlikely you will encounter any bugs with it.

You can refer to : Step By Step SQL Server Log Shipping for how to configure native logshipping.

References for Custom Logshipping :

Log shipping and A Control Mechanism
Custom Log Shipping
How to Setup Log Shipping for Multiple Databases

Using Powershell :

Log Shipping via PowerShell Invoke-LSRestore
Your poor man’s SQL Server log shipping-PowerShell version
Secure Log Shipping via SSL FTP
Automating SQL Log Shipping over SFTP (SSH), the simple way


How Can I Measure How long a query takes on Azure SQL Data Warehouse

Add a label to your query using the OPTION ( LABEL ... ) syntax which is supported in Azure SQL Data Warehouse.  You can then monitor it either via the portal or using the DMV sys.dm_pdw_exec_requests, for example

SELECT *
FROM dbo.yourBillionRowTable
OPTION ( LABEL = 'Your Unique Query Label 042' )

-- In a separate window...
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [label] =  'Your Unique Query Label 042'

Results:

Or monitor it in the portal:


some say, cluster index stores the whole records in deepest leaf of index.

Yes this is correct. The leaf pages of the clustered index contain the actual table rows. This is the actual data. There is no other primary copy of it held elsewhere.

The leaf pages also allocate a few bytes to hold the address (file and page) of the next and previous pages in index key order to form a doubly linked list. Following the index in key order may or may not be the same as following it in physical order depending on the level of fragmentation.

The rows on the individual leaf pages may not be written physically exactly in index key order but the slot array on the page, with pointers to the rows it contains, is ordered by key order.

  Does it use B-Tree structure (like non-clustered index)?

As long as the table doesn't fit on a single page then there will be one or more levels of the B-tree above the leaf. These higher levels contain index key values and pointers to pages in the level below to allow efficiently looking up key values. This is exactly the same principle used in nonclustered indexes too.

The leaf level pages of a clustered index are categorised as data pages and non-leaf pages as index pages. But the data pages are still part of the index.

(Image Source)

Also see my answer to a related question on Stack Overflow:

What do Clustered and Non clustered index actually mean?


Use trigger to synchronize columns with fields in json column on insert or update

Dynamic fields are notoriously difficult in plpgsql. In particular there's no way we can write new.variable := something where variable stands for a column name.

See How to set value of composite variable field using dynamic SQL for ways that involve querying the catalog at runtime.

Personally, I'd suggest a simpler solution with the plv8 language.

CREATE FUNCTION json_fn() RETURNS trigger AS
$$
  var obj = JSON.parse(NEW.json);
  for (var key in obj) {
    NEW[key]=obj[key];
  }
  return NEW;
$$
LANGUAGE plv8;

CREATE TRIGGER json_trigger
BEFORE INSERT OR UPDATE ON testy FOR EACH ROW
EXECUTE PROCEDURE json_fn();

Otherwise, you may consider if your implementation is not simplified by moving the json field out of this table and into a separate table, assuming it's still compatible with your general intention.


CSS-like dynamic properties cascading configuration table?

I think you are describing an EAV model.
Data driven systems arent uncommon. But if the design allows the application to change without altering the schema then you're talking about EAV.
The advantage of this is that non-technical users can extend or customise an application and add attributes or fields without changing the actual schema.
The downside is that you rely on the app to enforce integrity and datatyping. Performance suffers too.
In my experience you see less of these as xml & json have become more prolific.
Have a read of the various database design models at the bottom of this wikipedia page


DBCC FREEPROCCACHE along with Re-Organise Index maintenance task

The DBCC FREEPROCCACHE command is not beneficial (for index maintenance), and should not be executed in this context.

It flushes the plan cache, which causes all query plans to be recompiled when they are executed again.

If there's no way to turn that setting off manually, it is better to use recommended third-party index maintenance scripts, such as those by Ola Hallengren or Minionware.

EDIT: For an index re-organize, you should add a step to update statistics as well, because they are not updated automatically. This step alone has the same effect as invalidating stale plans, without the negative of flushing the entire cache. The above third-party maintenance solutions include a step to only update statistics that need updating.


The reason you can't tell which one is being used is because of a bug whereby explain does not include the index name of the index being used for geo index queries:

https://jira.mongodb.org/browse/SERVER-4213

What we do know is that it has to be one of the first two you have listed, that have a 2d element first.  It cannot be the "indexContents": NumberInt(1) index.  You can use .hint() to specify the index to use and then compare the results of the index in each case.  I would expect the first one to perform better in general (but be larger) since it contains all of the elements you are querying on:

"LongitudeLatitude": "2d",
 "Prominent"▼: NumberInt(-1),
 "indexContents": NumberInt(1)

However, that will depend on the actual data itself.  I would test over a representative set of queries and then remove the slower of the two indexes to avoid confusion.


PostgreSQL difference between VACUUM FULL and CLUSTER

To check what CLUSTER does, I took a table fo mine from an earlier experiment which basically contained the first 10 million positive integers.  I already deleted some rows and there is an other column as well but these only affect the actual table size, so it is not that interesting.

First, having run VACUUM FULL on the table fka, I took its size:

\dt+ fka
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description
--------+------+-------+----------+--------+-------------
 public | fka  | table | test     | 338 MB |

Then let's see the physical order of the data from the very beginning of the table:

SELECT *, ctid FROM fka ORDER BY ctid LIMIT 5;

 id  | col1 |  ctid
-----+------+---------
   2 | 2    | (0,1)
   3 | 3    | (0,2)
   4 | 4    | (0,3)
   5 | 5    | (0,4)
   6 | 6    | (0,5)

Now let's delete some rows:

DELETE FROM fka WHERE id % 10 = 5;
--DELETE 1000000

After this, the reported table size did not change.  So let's see now what CLUSTER does:

CLUSTER fka USING fka_pkey;

SELECT *, ctid FROM fka ORDER BY ctid LIMIT 5;

 id  | col1 |  ctid
-----+------+---------
   2 | 2    | (0,1)
   3 | 3    | (0,2)
   4 | 4    | (0,3)
   6 | 6    | (0,4)
   7 | 7    | (0,5)

After the operation the table size changed from 338 to 296 MB.  From the ctid column, which describes the physical place of the tuple in the page, you also see that there is no gap where the row matching id = 5 used to be.

As the tuples were reordered, indexes should have been recreated so that they point to the correct places.

So the difference looks to be that VACUUM FULL does not order the rows.  As far as I know, there is some difference in the mechanism the two commands use but from a practical point of view this seems to be the main (only?) difference.


Linked server to Oracle doesn't use filters. Are there any performance changes in different versions of SQL Server?

By default SQL Server doesn't believe that string comparisons will have the same semantics on the linked server that they have locally.  So it can't "push the predicate" to the remote server.

In particular Oracle is always case-sensitive, and SQL Server by default is not.  So in SQL Server you get the same results with

WHERE  F_DRSO = '84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39'

and

WHERE  F_DRSO = '84C9e2E5-2634-4f6A-B1B7-E1505D4A1C39'

But not in Oracle.

You can set the "Collation Compatible" setting of the linked server to allow SQL Server to push predicates.

But I really prefer to use OPENQUERY or EXEC ('sql') AT LinkedServer and retain explicit control over the remote query.

To enable the predicate to be sent to the linked server, the query needs to use 4-part names.

For oracle this would look something like:

select *
from ORCLLS..ADMIN.IE_Docs_Subscrs
WHERE F_DRSO = '84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39'

Oracle traditionally (pre 12) has only a single database per instance, so the second part of the 4-part name is empty.


Updating row of one table using rows of another table based on specific conditions

You could try MERGE instead of UPDATE. It is often faster and (in my opinion) more clear than UPDATE when updating a table based on another table

merge into FA_SUBLEDGER_MST s1
using (
    select SL_CODE, OFFICE_ID, OFFICE_TYPE
    from EMPLOYEE_MST
    where OFFICE_TYPE != 'DHB'
) emp
on (s1.SL_CODE = emp.SL_CODE)
when matched then update set
    s1.OFFICE_ID = emp.OFFICE_ID,
    s1.OFFICE_TYPE = emp.OFFICE_TYPE
where sl.STATUS = 'A'
    and (s1.OFFICE_ID <> emp.OFFICE_ID
    or  s1.OFFICE_TYPE <> emp.OFFICE_TYPE);

Make sure that using-subquery doesn't return duplicate rows for each SL_CODE

Also your last example is missing the STATUS = 'A' condition


DBMS_METADATA.GET_DDL is not mandatory to view the source of a package.

You can use DBA_SOURCE/ALL_SOURCE: https://docs.oracle.com/cd/A87860_01/doc/server.817/a76961/ch277.htm#101236

select text from dba_source
where owner = 'OWNER_OF_PACKAGE'
and name = 'NAME_OF_PACKAGE' order by type, line;


how to convert this varchar to datetime2(7) format?

There may be a better way, but you can do this by concatenating it into the proper form.

DECLARE @TimeString char(20) = '20200309150920'

SELECT CONVERT(datetime2(7),
    CONCAT(
        SUBSTRING(@TimeString,1,8)
       , ' '
       , SUBSTRING(@TimeString,9,2)
       , ':'
       , SUBSTRING(@TimeString,11,2)
       , ':'
       , SUBSTRING(@TimeString,13,2)
        )
    )


As seen on ola's Site, you can exclude backups by adding , -database.

An example of this:

  USER_DATABASES, -Db1  All user databases, except Db1

Or in you case, with ag databases:

  AVAILABILITY_GROUP_DATABASES, -Db1 All availability group databases, except Db1

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'AVAILABILITY_GROUP_DATABASES, -Db1'
@URL= xxx,
@Credential = xxx,
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y'


SQL Server DB Restore Clustered to Single Node?

Yes. The clustered production SQL Server instance should be transparent to the database


(This "Answer" does not answer the Question; it eliminates the Question.)

Don't use MyISAM; switch to InnoDB.  That gets rid of the need to repair tables.

Don't use MyISAM; switch to InnoDB.  That gets rid of the need to OPTIMIZE tables.


Struggling to design addresses table for fast querying

A single address table will be a clean and efficient solution to maintain and execute queries against. From the information you've given the main reason you might not want this is you have two well defined use cases, one of which expects additional fields to be completed while the other accepts a significant number of blank fields.

Handle this in your application logic - users should never be allowed to progress or commit data if all required fields are not validated in the front end.

If you do not already have a design that makes the address/user type clear (e.g. FK to user with a user type) you could always add a bit flag to your address table that defines the address type. If you store both a personal and a business address you may need this anyway.

AddressIsPersonal BIT NOT NULL

This will allow you to ensure a unique PK and to query Address table directly for data checks and searches. For example if AddressIsPersonal = FALSE means we expect to see complete addresses returned and we may only be searching for business posts.


Is matriculation number a good primary key?

I would go with the surrogate key approach. I'm not familiar with matriculation number, but i expect it to be large enough so that having a surrogate integer might be the preferred alternative. Having a larger PK, means larger indexes, which means less performance for reads and writes, plus more space usage, and more space for referring tables too.
Go with the surrogate key.


How do I generate a date series in PostgreSQL?

You can use generate_series for this, but be sure to explicitly cast the arguments to "timestamp without time zone" otherwise they will default to "timestamp with timezone". PostgreSQL overloads generate_series for both inputs.

Problems with timestamp with timezone

You can see the drawback here.

SET timezone = 'America/Santiago';
SELECT generate_series(date '2016-08-15', date '2016-08-15', '1 day');
SELECT generate_series(date '2016-08-14', date '2016-08-15', '1 day');

Both of the above return the same amount of days. You can see it again here.

SET timezone = 'America/Sao_Paulo';
SELECT generate_series(date '2016-10-16', date '2016-10-17', '1 day');
SELECT generate_series(date '2016-10-17', date '2016-10-17', '1 day');

The above shows two ranges of one day.

The reason for this behavior is that these timezones have their "DST boundary at midnight, rather than a more sensible time in the small hours"

So what does it look like to "do it right",

SELECT generate_series(
  timestamp without time zone '2016-10-16',
  timestamp without time zone '2016-10-17',
  '1 day'
);

Now you can cast to date..

SELECT d::date
FROM generate_series(
  timestamp without time zone '2016-10-16',
  timestamp without time zone '2016-10-17',
  '1 day'
) AS gs(d);

This question and answer was inspired by a conversation with RhodiumToad on IRC (irc://irc.freenode.net/#postgresql). He altered me to this issue and provided the solution.

Update: two potential fixes

Option 1: generate_series(date,date,interval)

Playing around, I discovered I could perhaps save the need to explicitly cast to timestamp without time zone by overload for generate_series(date,date,interval)

Here is my function,

CREATE FUNCTION generate_series( t1 date, t2 date, i interval )
RETURNS setof date
AS $$
  SELECT d::date
  FROM generate_series(
    t1::timestamp without time zone,
    t2::timestamp without time zone,
    i
  )
    AS gs(d)
$$
LANGUAGE sql
IMMUTABLE;

Now I can rerun the test case above and it's no longer fishy. These two both return the same thing,

SET timezone = 'America/Santiago';
SELECT d::date
FROM generate_series(date '2016-08-15', date '2016-08-15', '1 day')
  AS gs(d);

SELECT d::date
FROM generate_series(
  timestamp without time zone '2016-08-15',
  timestamp without time zone '2016-08-15',
  '1 day'
)
  AS gs(d);

As do these two,

SELECT d::date
FROM generate_series(date '2016-08-14', date '2016-08-15', '1 day')
  AS gs(d);

SELECT d::date
FROM generate_series(
  timestamp without time zone '2016-08-14',
  timestamp without time zone '2016-08-15',
  '1 day'
)
  AS gs(d);

Option 2: generate_series(date,date,int)

Another option is to create a new function generate_series(date,date,int) however you can't have both for the reasons mentioned here. So pick one of these,

generate_series(date,date,interval)
generate_series(date,date,int)

If you want the second option, try this one:

CREATE FUNCTION generate_series( t1 date, t2 date, i int )
RETURNS setof date
AS $$
  SELECT d::date
  FROM generate_series(
    t1::timestamp without time zone,
    t2::timestamp without time zone,
    i * interval '1 day'
  )
    AS gs(d)
$$
LANGUAGE sql
IMMUTABLE;

Caveats

With review on irc, there are some problems with those ideas,

  < johto> generate_series(date, date, unknown) already works today. when you don't break it outright with the int version (e.g. generate_series(date, date, '1 day')) you change the return type from timestamptz to date. (date, date, interval) would break fewer cases, but you'd still change the output type. (it's also not obvious what should happen with  (date, date, '1 hour')  that currently "works" just fine)


DBCC CHECKIDENT ('table name', RESEED, 20000) not working

It turns out after multiple conversations with the developers of this application using the table that there was a 'hidden' stored procedure that was grabbing the maximum value of the ID column in the table and adding an arbitrary number to it then issuing an IDENTITY_INSERT statement resulting in the newly reseeded value.


Microsoft Access constrain view by date in outer query

How about:

SELECT a.orderid,
       a.ocustomerid
FROM   orders AS a
       INNER JOIN (SELECT b.orderid,
                          b.ocustomerid
                          b.opromisedshipdate
                   FROM   orders AS b
                   ) AS c
       ON c.orderid = a.orderid
WHERE  c.opromisedshipdate = a.opromisedshipdate


Are parallel queries with mysqlnd actually possible?

Recommend you work on speeding up each query. Parallel execution in PHP, especially on Windows, is mostly a myth.

Better evidence


MySQL is not logging queries into log file

For logging slow queries do like below, It will log all queries those are not using indexes as well as which are taking more than 1 sec

slow_query_log                 = 1
slow_query_log_file            = /var/log/mysqld_slow_query.log
long_query_time                = 1
log-queries-not-using-indexes

For Error log

log-error                      = /var/log/mysqld.log

For general log

general_log_file               = /var/log/mysqld_normal.log
general_log                    = 1


mysql_insert_id() on galera mysql cluster

The "insert_id" is local to the connection.  No locking is needed to get the value.  This applies in all flavors of multi-processing, clustering, engines, etc.

Still, you may need "transactions" for other purposes.  You have not described the code enough to say whether you need to exactly replace LOCK TABLES (etc) with BEGIN..COMMIT.  Or whether some variation is needed.

One thing to learn is the following pattern:

BEGIN;
SELECT ... FOR UPDATE;  -- rows you need to look at before changing
do stuff
UPDATE those rows
COMMIT;

The "do stuff" could include a ROLLBACK and exit from the flow.

AUTO_INCREMENT:

The INSERT reserves the id before the operation.  In some cases, it will reserve an id but then not use it.  Example: INSERT IGNORE when it hits a dup key.

Galera (and any other Multi-Master setup) skips ids.  That is, one node will assign ids 1,4,7,10,...; another node: 2,5,8,...; the third: 3,6,9,...  Depending on the flow of INSERTs, you may see most of the numbers used, or you may see lots of gaps.  Also you may "see" id=124 "before" id=123 shows up.

More tips on moving to Galera and InnoDB


Short version is yes its possible to do what you are describing.

Your report will have a spatial query to display the shapes you want. Whether that's a map or a floor plan.

You can then add analytical data in a second query to colour the map. The two queries needs to be joined by a key.
Im not familiar with qgis but ideally you'd want both the spatial data and analytics in sql server.

The other option is to have 2 layers. The first layer gives the 'map' shape and the 2nd adds points on top.

If you search Google images for "SSRS map" you'll see lots of examples.


SQL Server 2000\2005 compatibility with Active Directory 2016

I have not gone through this process directly, but have worked with querying AD in a modern environment and a less modern environment.  I believe that the OLE DB Provider remains compatible between the older and newer versions of AD. Also I would not expect the loss of any commonly used (at least in my experience) properties, which would be the other risk.

If you do run into trouble, however, I would be prepared to migrate the databases to a newer instance of SQL Server running those DBs at a lower db compatibility level.  This will still be challenging, as I think you can only go back to 100 (2008). However, from a mitigation standpoint I would suggest trying to go this route regardless.  This would also allow you to retire the older instances (and likely old servers they are running on).  Why not make a bigger project even bigger (though you can do it ahead of time, so it doesn't all fail at once).

I will qualify this with my of querying AD had been generally focused on getting basic user properties and group membership, and do it so infrequently I have to go look everything back up.  And it's been a few years since I have to do much Windows Admin work.


Snapshot_isolation_state_desc NOT turning off

I found another way to turn ON snapshot_isolation_state_desc . Go to your DB, right click go to properties and find "Options" under Select a page on left side and then look for "Miscellaneous" option and you will see the first variable names as "Allow Snapshot Isolation" and just select the "True" and hit OK. it should be good to go. When I am running following query now to select the status, I get following.

snapshot_isolation_state_desc  = ON &
is_read_committed_snapshot_on = 0.


Why is Row Overflow not working on SQL Server 2005

This is just a warning message.  You can verify that rows are overflowing to the ROW_OVERFLOW_DATA pages by using the undocumented command DBCC IND.

Personally this sounds like a table which could stand some normalization.


MySQL group by multiple columns on multiple tables only one result per group

Setup the tables and some sample data:

drop table if exists users;
drop table if exists views;
drop table if exists tracks;

create table users
(idu      int
,username varchar(30));

create table views
(id       int
,`by`     int
,track    int);

create table tracks
(id       int
,uid      int
,title    varchar(30)
,time     datetime);

insert into users values
(567,'TrainingPuppy'),
(687,'BathroomMakeover'),
( 45,'PoshNachos'),
( 15,'SewingButtons');

insert into views values
(1,  5, 55),
(2,  5, 55),
(3, 67, 55),
(4,  6, 29),
(5,125,  2),
(6,  5,698),
(7,  5,698);

insert into tracks values
(  2, 15,'Worf is in the air','2016-02-11 22:57:35'),
( 29,567,'Stargold'          ,'2016-08-11 22:57:28'),
( 55,567,'No love liers'     ,'2016-10-11 22:57:51'),
(698,567,'Lofe'              ,'2016-11-11 22:57:44');

Start with a (ANSI standard group by) query to obtain our counts:

select tracks.id,
       tracks.uid,
       count(*) as ucount,
       users.username,
       views.track,
       tracks.title,
       tracks.time

from   tracks
join   views
on     views.track = tracks.id

join   users
on     users.idu = tracks.uid

group by tracks.id,
         tracks.uid,
         users.username,
         views.track,
         tracks.title,
         tracks.time

We'll call the above 'user_counts', and create a query that generates a ranking over username ordered by ucount desc:

select id,
       uid,
       ucount,
       username,
       track,
       title,
       time,
       @user_rank := if(@current_username = username, @user_rank:=@user_rank+1, 1) as user_rank,
       @current_username := username

from   user_counts,

        -- initialize our variables
join   (select @user_rank := 0, @current_username := 'abcdefg') r

order by username, ucount desc

Then wrap this in our final select, making sure to only display those rows with a ranking of '1' ... pulling it all together into a single query we have:

select id,
       uid,
       ucount as 'count',
       username,
       track,
       title,
       time

from   (select id,
               uid,
               ucount,
               username,
               track,
               title,
               time,
               @user_rank        := if(@current_username = username, @user_rank:=@user_rank+1, 1) as user_rank,
               @current_username := username

        from   (select tracks.id,
                       tracks.uid,
                       count(*) as ucount,
                       users.username,
                       views.track,
                       tracks.title,
                       tracks.time

                from    tracks
                join    views
                on      views.track = tracks.id

                join    users
                on      users.idu = tracks.uid

                group by tracks.id,
                         tracks.uid,
                         users.username,
                         views.track,
                         tracks.title,
                         tracks.time) user_counts

                -- initialize our variables
        join   (select @user_rank := 0, @current_username := 'abcdefg') r

        order by username, ucount desc) dt

where   user_rank = 1

order by ucount desc, username

And the results:

id uid count username      track title              time
-- --- ----- ------------- ----- ------------------ -------------------
55 567     3 TrainingPuppy    55 No love liers      2016-10-11 22:57:51
 2  15     1 SewingButtons     2 Worf is in the air 2016-02-11 22:57:35

Some fiddles:

MySQL 5.6
MySQL 5.7
MariaDB 10.2

NOTE: If a given username has the same max(count) for more than one track/title, this solution will only display one record; to display multiple records would require a tweak to ensure said records receive the same ranking (ie, user_rank = 1).


Declare Variable to use as placeholder text for tablenames, not using declare table type

In SQL Server, you need to keep table names and column names static in code that isn't dynamic.

To do what you are trying to do, you'd have to use Dynamic SQL - declare variables for your SQL Statement, populate the SQL statement dynamically - using your @TableName variable to supply the table name - then execute the dynamic SQL.

There are a lot of gotchas to think about with Dynamic SQL. A good place to start is a rather lengthy piece on the good and bad side of Dynamic SQL from Erland Sommarskog in this article, "The Curse and Blessings of Dynamic SQL"


oracle DB connection - hostdef extension doesn't exist

The correct format for the connection string is:

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = "HOST name")(PORT = "PORT number")) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = "SERVICE Name") ) )


Writing Select statement to join multiple tables

Change the INNER JOIN to LEFT JOIN. There's a chance there are missing foreign key values that are causing it to not find any matches across the whole set.

Where you see NULL in the result set, will be an indicator where there is no matching foreign key.


Would generate a new table help reducing the overall size

You would not actually save any space using BIT rahter than TINYINT (and would also give yourself more options to add other entities). This is because 8 or less bits are stored in a byte. If you were using multiple bit entries this could save you space, but it's ultimately very limiting. A TINYINT would use the same 1 byte of storage, but would allow you to store up to 256 different entity values.

Saying this though, from a practicality standpoint, unless you are looking at ridiculously high volumes of data the space savings are not going to be that significant regardless of using TINYINT or SMALLINT (~65k values & 2 bytes).

Using normalization for this sort of thing is very common, and helps prevent excessive bloat (after all in your second table you would store 1 bytes instead of twelve to represent the person entity). Don't forget to add foreign key references.


I think your column definition will be as shown in this query:

select
    user_ID,
    tanggal,
    kode_layanan,
    nomor_antrian,
    aksi_ID,
    jam,

    case aksi_ID
        when 4 then
            timediff (
                jam,
                (
                    select t2.jam
                    from transaki t2
                    where t2.aksi_ID = 9
                    and t2.nomor_antrian =
                        (
                            select max(t3.nomor_antrian)
                            from transaki t3
                            where t3.nomor_antrian < transaki.nomor_antrian
                        )
                )
            )
        else
            null
    end as idle_time

from transaki
order by jam;

The logic is: "if the current row has an aski_ID value of '4' then find the time difference between the current row's jam value, and the jam value of the row having an aski_ID value of 9 and a nomor_antrian value that is the maximum nomor_antrian value less than the current row's nomor_antrian value".

You can see this result working on db-fiddle.


What is the difference between an after update and a before update in PostgreSQL

There is one primary difference in a trigger that is AFTER from one that is BEFORE .. but both get executed regardless.

In a BEFORE trigger, the trigger is executed BEFORE the DML statement is executed..  So you have a chance to modify the :NEW (old :OLD in case of DELETE) row BEFORE it is inserted/updated/deleted...  But anything you want to do you can do (like check data in a separate table and issue an update/insert/whatever in that table, etc).  If, for some reason, the trigger causes an EXCEPTION, then execution ceases and the DML statement is never executed.

In an AFTER trigger, the trigger is executed AFTER the DML statement is executed.  You have already lost any ability to modify the :NEW and :OLD records and have it actually mean anything.  However, you can still do whatever you want in the trigger.  If the trigger causes an exception, for PostgreSQL, this will cause a rollback of the original DML. I do not believe this is true for all RDBMSs - but am having a hard time finding an example right now.

A common use of a BEFORE trigger is to set a timestamp column to "now" before the data has been inserted.

A common use of an AFTER trigger is to populate an audit/history table with the changes.

In either case, you would usually want to issue a ROLLBACK if you find an exception has been raised.


How Do I Grant Permissions to Replace a Function?

Apparently, whatever you are doing tries to CREATE OR REPLACE the function SYSTOOLS.JSON_VAL2. To replace any object the ID performing the operation must be the owner of that object, that is, the same ID that issued the very first CREATE statement.

You have several options:

Use the TRANSFER OWNERSHIP statement to change the function owner. SECADM can do that.
Determine the current owner (SELECT OWNER FROM SYSCAT.ROUTINES WHERE ROUTINENAME = 'JSON_VAL2' AND ROUTINESCHEMA = 'SYSTOOLS') and perform your task while logged in as that user.
Drop the object explicitly, for which DBADM is sufficient, before performing your task.


Oracle how to make primary key index usable from unusable?

01502, 00000, "index '%s.%s' or partition of such index is in unusable state"

  MERGE: 1489 RENUMBERED TO 1502

  Cause: An attempt has been made to access an index or index partition
          that has been marked unusable by a direct load or by a DDL
          operation.

  Action: DROP the specified index, or REBUILD the specified index, or
          REBUILD the unusable index partition.

Now, you have to rebuild index(es) according to their type, Global or Local.

  You can rebuild global index partitions in two ways:
  Rebuild each partition by issuing the ALTER INDEX REBUILD PARTITION statement (you can run the rebuilds concurrently).

  Drop the entire global index and re-create it. This method is more efficient because the table is scanned only one time.

  Rebuild local indexes using either ALTER INDEX or ALTER TABLE as follows:

  ALTER INDEX REBUILD PARTITION/SUBPARTITION

  This statement rebuilds an index partition or subpartition unconditionally.

  ALTER TABLE MODIFY PARTITION/SUBPARTITION REBUILD UNUSABLE LOCAL INDEXES

  This statement finds all of the unusable indexes for the given table partition or subpartition and rebuilds them. It only rebuilds an index partition if it has been marked UNUSABLE.

Documentation:About Rebuilding Index Partitions


How can I backup & restore a single FILEGROUP in Sql Server 2008

You need to restore the transaction logs so that you have a consistent database.  Currently the filegroup which you have restored is at a different point in time than the rest of the database.  Once the logs have been rolled forward and everything is consistent then you can bring the database online.

If you look at the data in the msdb database you'll be able to see which transaction log files you need to restore in order to bring the database online.

There's nothing wrong with your backup and recovery syntax.


Rows in a table don't have order. You will need an additional column to enforce the order, if order is important for you.


what would be the primary disadvantages of splitting my tables into table spaces and/or separate schemata

DISADVANTAGE #1
InnoDB only has one Buffer Pool. The benefits of partitioning or compartmentalizing database tables for different clients can still be bottlenecked by database buffers (log buffer, InnoDB Buffer Pool)
DISADVANTAGE #2
Scaling becomes dependent on manual operation. This applies to scaling up disks, hardware (disk controllers), and memory.
DISADVANTAGE #3
More tables means more open file handles to cache distinct tables. Requires more flush to disk.
DISADVANTAGE #4
Introducing new groups of related tables regurgitates the first three(3) disadvantages and  exacerbates the overall design.
See my other posts on such setups and other drawbacks

Nov 15, 2011 : One Big Database vs. Several Smaller Ones
Mar 11, 2011 : What problems will I get creating a database per customer?

SUGGESTIONS
You might have to compensate these deficiencies with lots of RAM with the InnoDB Buffer Pool and the Log Buffer getting the lion's share.
You could create a hybrid setup where InnoDB Log Files are on one disk and InnoDB System Tablespace is on another, and all the data on a third disk. This concept is well expressed in YOSHINORI MATSUNOBU'S BLOG (FaceBook DB Engineer).


Backup Files with Append option growing huge Vs Better Options

Don't use append, it just makes that single file bigger and bigger, which makes it harder to manage. Also, if you need to restore from it, now you have to figure out which file number you need from inside (and more often than not, people don't realize there are multiple files, and they just run a restore from it and get the default file, not the one they expected).

I strongly recommend always creating independent full backups, and having the date/time embedded in the filename is useful for manual inspection / cleanup. Thankfully, you can parameterize the output filename, so you can build it dynamically with the current date (and time where appropriate). Date alone is useful when you're only doing single daily full backups and exact time is unimportant; times are likely more useful for log/diff backups. Here's a full backup with both date and time:

DECLARE @file nvarchar(512) = N'C:\temp\foo_'
    + CONVERT(char(8), GETDATE(), 112) + '_'
    + REPLACE(CONVERT(char(8), GETDATE(), 108),':','')
    + N'.bak';

BACKUP DATABASE foo TO DISK = @file
WITH INIT, COMPRESSION, STATS = 20;

For me this created the following backup file:

C:\temp\foo_20180403_082056.bak

And yes, this backup will include Filestream data.


You might get it to work using only system procedure sp_lock. Although it's deprecated and it will be removed in a future version, it still works on SQL 2008 R2 and SQL 2012.

USE master;
GO
EXEC sp_lock;
GO
EXEC sp_lock 53; -- 53 is the spid for some specific user session;
GO

Another way is to use the DMV sys.dm_tran_locks.

Select *
from sys.dm_tran_locks dl
join sys.sysprocesses sp on dl.request_session_id = sp.spid

I think that a more clearer way is to use Adam Machanic's procedure WhoIsActive which has a specific parameter for showing locks for a session.

Use them all and choose whatever suits you best, but the WhoIsActive procedure provides more info than just locks.


Something like this:

SELECT

    min(date) OVER (PARTITION BY Site) AS MinDate,
    a.*
FROM

    (select * from
    (select 4 AS Site,   '10/10/18' as date union
    select 3,   '10/10/18' union
    select 4,   '07/10/18'  union
    select 5,   '12/10/18'  union
    select 3,   '11/10/18'  union
    select 5,   '13/11/18'  as date ) b) a
    order by MinDate, Site


Why is it not a good idea to use MariaDB's extra_port for replication?

Replication do not need some special ports and can use the common 3306. An extra  port is a port that can be used by root as back door if there is no access through the common port.
Say if you have max_connections = 100 and your app is already used all that 100 connections, only one extra connection can be used by user with ALL PRIVILEGES granted. If someone is already connected as DBA you have no chance to connect to the server. But you still can connect via extra_port cause it has no max_connections limit. And that is the reason why you should not use it for any other tasks except an emergency access. With no max_connections set to the reasonable value you can easily exhaust your host's RAM that can hang your DB or even a host in a pretty bad way.


Query plan do not get invalidated after update statistics

Your original sample code wasn't re-runnable (the table creation wasn't in there, or the population of rows) so I rewrote it to flesh it out and make it re-runnable.

The problem is that your query is trivially simple: it doesn't matter whether stats are updated or not, it's going to produce the same plan. This tweak will make your SELECT query bypass trivial optimization:

SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere' AND 1 = (SELECT 1);

Try the test again with that:

/* Start a trace monitoring recompiles */
exec sp_BlitzTrace @Action='start', @TargetPath='c:\temp\', @SessionId=@@SPID, @TraceRecompiles=1;
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere' AND 1 = (SELECT 1);
GO
/* Increment the row mod counters to encourage a stats update */
BEGIN TRAN
DELETE dbo.MyTable;
GO
ROLLBACK
GO
UPDATE STATISTICS dbo.MyTable WITH fullscan
GO

/* We don't strictly need to wait, but makes different executions easier to see: */
WAITFOR DELAY '00:00:10';
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere' AND 1 = (SELECT 1);
GO
EXEC sp_BlitzTrace @Action='stop'
GO
EXEC sp_BlitzTrace @Action='read'
GO

And now you get recompiles - note the third line that shows recompile_cause = "Statistics changed"

This is one of the reasons that sp_BlitzCache (disclaimer: I'm a very small author on that open source script) shows warnings for trivial plans - you don't get all of the cool stuff you get on fully optimized plans, like missing index recommendations.

I'm certainly not saying that adding "WHERE 1 = (SELECT 1)" is a good idea in your queries - just saying that when building repro scenarios, you gotta watch out for trivial queries. To know if your query is trivially simple, view the execution plan, right-click on the root node (like the SELECT), click Properties, and look at the Optimization Level. If it says Trivial, you're not getting the full genius of those folks at Microsoft.


How to correctly use group by for getting the record with the closest time for each rec_id

WITH cte AS ( SELECT rec_id,
                     rec_time,
                     FIRST_VALUE(rec_value) OVER (PARTITION BY rec_id ORDER BY rec_time DESC) rec_value
              FROM records
              WHERE rec_id IN (4,9,21,565,951,93,6,15,64)
              AND rec_time <= 590 )
SELECT rec_id, MAX(rec_time) rec_time, rec_value
FROM cte
GROUP BY rec_id /* , rec_value */ ;

or ever

SELECT DISTINCT rec_id,
                MAX(rec_time) OVER (PARTITION BY rec_id) rec_time,
                FIRST_VALUE(rec_value) OVER (PARTITION BY rec_id ORDER BY rec_time DESC) rec_value
FROM records
WHERE rec_id IN (4,9,21,565,951,93,6,15,64)
AND rec_time <= 590;


An alternative would be checking for each of the changes you want to make — does table foo exist, does column bar exist in table baz, and so on. Which sounds cleaner, perhaps, but means a lot more i/o.

So the best way would be something like

int databaseVersion = getDatabaseVersion(); // because of course it's a function

try {
   if (databaseVersion < 2) {
      // Update database schema to version 2
      databaseVersion = 2;
   }

   if (databaseVersion < 3) {
      // Update database schema to version 3
      databaseVersion = 3;
   }

   // and so on...
}
finally {
   setDatabaseVersion(databaseVersion);
}

Don't forget to write the version to the database.

If you do not tie the database version number to your program version number (so databaseVersion <> programVersion, but of course a specific version of your program has a specific version of the database), but instead increment the version number after each DDL statement you execute, this, coupled with the version being written in the finally, ensures that if for whatever reason the update crashes halfway through, the next time you can continue from that point on.


Have you considered using a Stored Function ?

You can split the union into your individual queries and if it passes any one of the three, return count(*). Otherwise, return 0

Some restrictions on stored functions

I recommended function over procedure because it's invoked like:

SELECT myfunc(userID, eventID);

and your query doesn't look like it needs a resultset returned, just a single value. If it needs a resultset, you would need a procedure (as noted in the restrictions documentation)


postgresql cannot connect as user to data server

The default db name, if not provided, is the username.
In this case oak_admin.

Provide the database name you want to
connect to using the --dbname= option. https://www.postgresql.org/docs/current/static/app-psql.html

In this example you should add --dbnsme=oakdb


Just found the answer:

Create SQL login with minimal privileges
Execute function from source database under that user (WITH EXECUTE AS)
Module sign object
GRANT AUTHENTICATE to user created from certificate (this is the one I missed)


Set an SSIS SQL Agent job to unexpected error or failure

If you trace the query the report is generating you'll see that the executions are stored in the [catalog].[executions] of the database behind the Integration Services Catalog.

You can just modify your [end_time] there and you should be able to 'fix' the report.

I haven't managed to reproduce your exact scenario (i.e. hard resetting a VM to get a package in the running state) but I could modify the value in that field and the All Executions report reflects the new values.

However, don't set the end_time too much behind the start_time as you'll get overflow errors in the report if your package appears to have run for days.

According to the documentation you have these statuses available to you:

  The status of the operation. The possible values are created (1),
  running (2), canceled (3), failed (4), pending (5), ended unexpectedly
  (6), succeeded (7), stopping (8), and completed (9).

So running this:

UPDATE [catalog].[executions]
SET [end_time] = DATEADD(minute, 2, start_time)
    ,[status] = 6
WHERE [execution_id] = 2;

on my system results in a package that shows "unexpected termination" after 2 minutes.


You need to refer to the parameters in your main sql query.

Something like:

Where databaseName = @databases

Or if you want to be able to select multiple databases.

Where databaseName in (@databases)

Here is an example using date parameters and multi-select parameters in a report https://technet.microsoft.com/en-us/library/aa337396(v=sql.105).aspx

If you Google " ssrs parameter sql query" there are lots of working examples and even some youtube videos giving step by step instructions.


Declaring and using variables in triggers

As per my comment above, the chances are that you have a foreign key that is set to delete on cascade. This is what is causing your lookup to fail.

I hope this helps you.


Is there a shortcut for 'SELECT * FROM table' in relational algebra?

Yes.

In relational algebra the relation

Book

corresponds to

select * from Book

If you want to restrict to some rows you apply a selection σ:

σISBN=43221(Book)

In SQL this is a where-clause like

select * from Book where ISBN=43221

If you want to reduce the columns you need a projection π:

πISBN, title, type(σISBN=43221(Book))

In SQL this is a select-list different from *:

select ISBN, title from Book where ISBN=43221


Look up PIVOT command in Oracle SQL reference manual, it is what you need to solve this problem. Over a million records shouldn't be a problem. Since you want day list as static, you can "pivot max(hours) as hrs for to_char(day,'DY') in ('SUN','MON'))" etc. Once you have the query, adjust for column heading appropriately, and generate a totals column. If this igoing to be regularly used, you can generate a MV using this logic and update it appropriately.

Here is a simple example (I just used a row generator for sample data)

with x as (select 'Test' typ, to_char(sysdate+level,'DY') dt
from dual connect by 1=1  and level <= 366)
select * from x pivot (count(*) as cnt
for dt in ('SUN' as sun,'MON' as mon,'TUE' as tue, 'WED' as wed,
'THU' as thu, 'FRI' as fri, 'SAT' as sat))


Your breakup of the table is largely correct.  There is three tangible objects, a book, the author, and the publisher.  Each of these needs a table, we also need a table to store the relationship between Authors and Books and Publishers and Books as these relationships are many to many.

Books

ISBN (primary key)
Title
Price

Authors

AuId (primary key)
AuName
AuPhone

Publishers

PubId (primary key)
PubName
PubPhone

Authors to Books

AuBId (primary key)
AuId (composite key)
ISBN (composite key)

Publishers to Books

PubBId (primary key)
PubId (composite key)
ISBN (composite key)

You could just use the composite keys instead of having an explicit primary key on the last two tables that define the many-to-many relationships.

Edit:

When I say composite key I mean if a primary key exists that it is an alternate unique key made of multiple columns.  If there is no other primary key defined then the primary key would be defined as both columns, i.e. PubId and ISBN.  There are both pros and cons for doing using a composite key as the primary key, people have different takes on the best option.

There is no reason to explicitly store the libary table with the table structure above, all the information is already stored.  If absolutely necessary I would think that the ISBN is unique, if not you could as mentioned add a primary key column like serialno which maintain integrity.


Broken Slave MySQL : How To Fix MySQL Replication (BINLOG) error in the Slave?

HOW TO BYPASS ALL DUPLICATE KEY ERRORS
There are two ways to bypass duplicate key errors
METHOD #1 : Configure it in my.cnf
Add this line under the [mysqld] group header in my.cnf
[mysqld]
slave-skip-errors=1062

then restart mysql.
Once replication begins, all duplicate key errors will be bypassed. Once Seconds_Behind_Master is 0, you are all caught up. However, the data on the Slave is out-of-sync with the Master. What do you do next ???
METHOD #2 : Use an Automated Tool
I  am sure you don't have the time to run
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

over and over again. Wouldn't it be nice if an automated tool can do that for you ? Well, such a tool exists. It's called pt-slave-restart.
You would run it something like this:
pt-slave-restart --error-numbers 1062

Put this in a crontab to go off every minute.
* * * * * pt-slave-restart --error-numbers 1062

Even better, you can make a server daemon and not use a crontab
pt-slave-restart --error-numbers 1062 --daemonize

and you do not need to restart mysqld.
Either way, pt-slave-restart will skip that error over and over again until Seconds_Behind_Master is 0. When you are done having the Slave get caught up, stop using pt-slave-restart.
However, you still have the same problem as METHOD #1: the data on the Slave is out-of-sync with the Master. What do you do next ???
OK, WHAT DO YOU DO NEXT ???
You must synchronize the data on the Slave with your Master. Wouldn't it be nice if a utility exists that can do that for you ? Well, such a tool exists. In fact, there are two tools. They are called pt-table-checksum and pt-table-sync.
What do they do ???
You use pt-table-checksum to check on MySQL replication integrity. If you encounter any table that have different checksums between Master and Slave, such a table on the Slave is out-of-sync.
You use pt-table-sync to synchronize MySQL table data.
You can run
pt-table-sync --print > sync_file.txt

to see what will change (this is like a dry run of the real thing)
You then run
pt-table-sync --execute

to perform the actual sync.
PLEASE READ THE PERCONA TOOLKIT DOCUMENTATION.
ALWAYS HANDLE PERCONA TOOLKIT RESPONSIBLY !!!
NOTE : Sadly, there is absolutely nothing you can with MySQL 5.5 and GTID.


Azure SQL Database Pricing: Standard vs Premium tier

In the example you only refer to IO metric and forget about CPU and memory factors which are also a part of the DTU model. In the example you provided database engine with 400 DTU will give you more CPU and memory capabilities than 125 DTU.
The factor which also could be important while choosing between Premium and Standard tier is Max concurrent workers (requests). For S6 it is 800 and for P1 it is 200, so if you have a lot of concurrent queries, you would like to go with Standard which has better worker/EUR ratio.
Just to clarify, Columnstore Indexes are also available in Standard tier starting from S3 (100 DTU).
Generally speaking Premium Tiers are best suited for IO intensive workloads, while Standard tiers could be beneficial in terms of costs perspective for high concurrent systems based on intensive CPU queries.
Materials:

Service tiers in the DTU-based purchase model
Resource limits for single databases using the DTU purchasing model - Azure SQL Database
New Azure SQL Database Standard Tier Sizes


Are there any other ways to get results from Two tables without using Sub Query

I need the result of the customers who did not buy in last month.

  That is from the order table Customer 3 and 4 have purchased in last
  month(November). The result should not include customer 3 and 4 even
  they had purchase in earlier months.

I am a bit confused with your question.

If you want to see customers who did not purchase last month (in this case, November) Then below query should be able to handle it.

SELECT DISTINCT
    c.id,
    c.name
FROM #Customer c
LEFT JOIN #Orders o ON c.id = o.c_id AND MONTH(o.OrderDate) = MONTH(GETDATE()) - 1
WHERE o.c_id IS NULL

If you want to see customers who only purchased last month (in this case, November) Then below query should be able to handle it.

 SELECT DISTINCT
        c.id,
        c.name
    FROM #Customer c
    INNER JOIN #Orders o ON c.id = o.c_id AND MONTH(o.OrderDate) = MONTH(GETDATE()) - 1


Referring alias name in subselect returns "Unknown column in 'where clause'"

For the alternative way to count these:

select
    day(date) as day,
    hour(date) as hour,
    count(1) as c,
    sum(a=1) as a1,
    sum(a=0) as a0,
    (sum(a=1) / count(1)) as pct
from t
group by hour, date(date)

I added the date to the group by so the same hour of different days do not get counted together - change that the way you need.


Cannot create maintenance plan, I get the message "The task with the name '' and the creation name is not registered for use on this computer"

I have been having the same issue with SSMS 18 (preview 4) when trying to access any maintenance plan tasks.

I opened this item on the feedback site to capture the bug. Feel free to upvote if this describes the issue you're experiencing.

For now I am just using 17.x versions of SSMS to do any maintenance plan related tasks, since you can have both installed side-by-side.

The bug was acknowledged on 10/15 and it looks like MS is working on a fix, so hopefully the next release resolves the issue.


Assumption:

some batch work (either as part of a SQL batch, or perhaps a user-defined function) is acceptable (otherwise the batch code could be rolled into a couple additional CTEs)

A batch/CTE solution:

declare @mydate  datetime,

        @mymon   int,
        @myday   int,
        @myyr    int,

        @startyr char(4),
        @endyr   char(4)

select  @mydate = '01/02/2018'

-- break @mydate into numeric components to make it a little easier in the
-- next step to determine our start/end years

select  @mymon = month(@mydate),
        @myday = day(@mydate),
        @myyr  = year(@mydate)

-- determine our start/end years based on current year of @mydate; since we'll
-- be feeding these variables into a convert(datetime,<string>) construct, go
-- ahead and convert to char(4) here in order to minimize coding later

select  @startyr = convert(char(4), case when @mymon = 10 and @myday >= 30 then @myyr
                                         when @mymon in (11,12)            then @myyr
                                                                           else @myyr - 1
                                    end),
        @endyr   = convert(char(4), case when @mymon = 10 and @myday >= 30 then @myyr + 1
                                         when @mymon in (11,12)            then @myyr + 1
                                                                           else @myyr
                                    end);

-- use a CTE to build our list of quarters, using the @starty/@endyr variables
-- to create the datetime ranges that make up the quarters in which
-- @mydate date can fall within

with
quarters as

(select *
 from   (values ('Quarter1', convert(datetime,'10/30/'+@startyr), convert(datetime,'01/29/'+@endyr) ),
                ('Quarter2', convert(datetime,'01/30/'+@endyr),   convert(datetime,'04/30/'+@endyr) ),
                ('Quarter3', convert(datetime,'05/01/'+@endyr),   convert(datetime,'07/30/'+@endyr) ),
                ('Quarter4', convert(datetime,'07/31/'+@endyr),   convert(datetime,'10/29/'+@endyr) ) ) as q(qtr, qstart, qend)
)

-- now it's just a matter of finding which datetime range (aka quarter)
-- @mydate falls between

select q.qtr, q.qstart, q.qend

from   quarters q
where  @mydate between q.qstart and q.qend;

Some sample runs (changing the value of @mydate):

-- @mydate = '10/29/2017'

 qtr      | qstart              | qend
 -------- | ------------------- | -------------------
 Quarter4 | 07/31/2017 00:00:00 | 29/10/2017 00:00:00

-- @mydate = '10/30/2017'

 qtr      | qstart              | qend
 -------- | ------------------- | -------------------
 Quarter1 | 30/10/2017 00:00:00 | 29/01/2018 00:00:00

-- @mydate = '08/17/2016'

 qtr      | qstart              | qend
 -------- | ------------------- | -------------------
 Quarter4 | 31/07/2016 00:00:00 | 29/10/2016 00:00:00

-- @mydate = '01/23/2016'

 qtr      | qstart              | qend
 -------- | ------------------- | -------------------
 Quarter1 | 30/10/2015 00:00:00 | 29/01/2016 00:00:00

Here's a dbfiddle


SQL Server 2012 has an indicator in the plan itself, RetrievedFromCache, which can be either "true" or "false".

This appears to be the property you are asking about.

This is a sample (the last line shows the property):

<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1"
StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound"
StatementSubTreeCost="0.0508992" StatementText="SELECT COUNT(*)
&#xD;&#xA;FROM sys.tables" StatementType="SELECT"
QueryHash="0x9A4B63A948B30EA0" QueryPlanHash="0xF357CAE882D5B15D"
RetrievedFromCache="true">

Unfortunately, I do not see anything similar in a plan generated by SQL Server 2008 R2.

In SQL Server 2008 R2, you can use the sys.dm_exec_query_stats system DMV to inspect the creation_time column for plans that have the same query_hash value.  The query hash can be obtained from the header of the plan XML (see the example above).  This query will return rows regarding the plan mentioned above:

SELECT *
FROM sys.dm_exec_query_stats qs
WHERE qs.query_hash = 0x9A4B63A948B30EA0;


Real-world scenarios for table with DENY SELECT and GRANT [INSERT/UPDATE/DELETE]

There is one simple enough scenario where I could see permissions being REVOKEd/DENYed for SELECT and GRANTing INSERT/UPDATE/DELETE makes sense.

Suppose one works for an institution wheire confidentiality is crucial and any large scale publication and/or loss of user data would be disastrous. Say, a bank or a medical insurance company?

You take the agents who answer calls for these institutions.

You allow the customer to key in their details (account number).

Then, the first thing the agent sees are the security questions (mother's maiden name, first pet's name, &c.).

These questions being answered correctly, the system allows the agent to see the record of that person and that person only.

Then, the agent is allowed to UPDATE or DELETE that account (change of address, cancellation of account). An INSERT wouldn't be a problem either (new account - no risk).

This way, an agent can only see one person's account details at a time and cannot SELECT large quantities of data for potential download/malicious release for criminal/blackmail/"hacking" purposes.

My girlfriend's sister is a manager in a telecenter for a large bank and the large-scale release of data is their biggest nightmare. I discussed this with her (me from a database perspective and her from a banking one). She couldn't release details of their system, so it was like 20 questions, but the clear impression I was given is that something like this was in place.