Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?

Absolutely not. Proof:

SELECT
   A.[Name],
   ROW_NUMBER() OVER(ORDER BY A.[Name] ASC),
   ROW_NUMBER() OVER(ORDER BY A.[Name] DESC)
FROM
   [FooTable] AS A

The only way to guarantee an order in SQL is to ask for it, use  ORDER BY on the result itself.


For PostgerSQL:

with acupressure_point_data as ( -- insert into first table with output
                                 insert into acupressure_point(id, url)
                                 values('LL909', 'ww/33/11')
                                 returning id as acupressure_point_id )
-- insert into second table, use CTE output
insert into acupressure_point_region_sub_type(acupressure_point_id, region_sub_type_id)
SELECT acupressure_point_id , '9898'
FROM acupressure_point_data
-- return id for both tables
returning acupressure_point_id, id as acupressure_sub_type_id;

MySQL does not allow INSERT in CTE. Use separate queries and LAST_INSERT_ID().


Is the CPU performance relevant for a database server?

For PostgreSQL, CPU power can be very relevant, especially if a fairly high percentage of the active working set of your data fits in RAM.  Most of the databases I've worked with have had CPU power as the main bottleneck most of the time.  (I just checked vmstat on a server hosting web sites with millions of hits per day hosting over 5TB of database space, and I never saw more than 2% disk wait time, but saw a peak of 12% user CPU time.)

Since PostgreSQL is process-based, any single process can only run as fast as one core, but in a mix like like we have on the server mentioned above, with a high volume of small requests, total CPU across all cores is most important.  For the same total CPU power, PostgreSQL will generally do better with fewer, faster cores than many, slower cores.

Up to the point where a high percentage of your active data set is cached, adding RAM will typically show more bang for the buck than adding cores.  After you've got sufficient caching, the benefit of additional RAM goes down and you're better off boosting CPU power.

For more details on this topic as it pertains to PostgreSQL, I don't think there is a better source than PostgreSQL 9.0 High Performance by Greg Smith.  (Full disclosure, I was a technical reviewer for the book, but get no financial benefit based on sales.)


Store VarChar(Max) [Notes] Column on Same or Separate Table

While its great that you are even thinking about this - I would consider it to be micro-optimisation. SQL Server already has optimisations built into it for handling NVARCHAR(MAX) and so generally I would say to not second guess it until it becomes a problem. However, there are two main points that came to mind when reading your question.

The first is (as you touched on with your last point) would there actually be any performance gained by splitting the data out into another table over letting SQL Server optimise the pages and data itself? I don't have hard evidence with me right now but I suspect not. The reason is that, as you mention, your data is regularly over 8000 bytes and therefore will be moved to LOB pages in your Note table anyway. You would just be moving the perceived problem from one table to another. Add the overhead of performing a JOIN when having to fetch the data and you are likely end up slightly worse off than you were to begin with.

Secondly, as you tagged the question with database design, my actual recommendation is that you do split out the notes into another table. However, my reasoning is not so much driven by the performance optimisation but more specifically adding flexibility to the application. It is highly likely that a shipment may need more than one note attached to it (for example if it is delayed on transit, or a customer calls in etc...). If you have the note on the actual shipment itself it makes it harder to do this without duplicating data. If you extract it into its own table you will be able to assign multiple notes to a shipment, and track who entered them and when. This makes your application and database more flexible.

If you want to optimise then I would recommend removing the need to query the notes unless someone specifically wants to view them (such as having a dedicated form in your application to do so). That way, you only need to perform the join/lookup when somebody explicitly wants to view the data. Even if you didn't externalise the notes to another table, not including the column in the query if it is not needed will remove the need for SQL Server to read the LOB page. If possible I'd always optimise queries to return the least amount of information required before optimising at a micro level.


Cannot Access Non-System Databases in SQL Server Express 2016

From the looks of your screenshot, it looks like your query is not connected to the BERNARDO\SQLEXPRESS01 instance and is connected to the BERNARDO\SQLEXPRESS instance (as indicated at the bottom of the query tab).  Change the connection of that query pane to connect to BERNARDO\SQLEXPRESS01 and I suspect your issue will be resolved.


Can an EXISTS clause be improved when the condition is checked for the same value multiple times?

Exists and IN will give the same query plan because they are both "LEFT SEMI JOINs" internally. I prefer EXISTS generally because of consistency. NOT EXISTS is safer then NOT IN.

Caching does not apply and don't think about it. This is a set based operation and the SQL Server optimiser will work out the best way: It may spool the subquery or it may not. Note that a left semi join is typically very efficient. Don't overthink it.

Finally, a PK scan indicates a missing index on UserBlogRoles

Observation: use the the schema, Luke. dbo.UserBlogRoles for example


janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege.

You got at least one aspect of the command backwards in all your attempts:
Alter default privileges for the role that creates objects - which in turn grants privileges to another role. I.e.:

ALTER DEFAULT PRIVILEGES FOR ROLE janerole IN SCHEMA public
GRANT SELECT ON TABLES TO johnrole;

Do this while logged in as janerole (or a member of the role).

Note that default privileges only apply to the target role, not to members of this role.

And don't forget to GRANT USAGE ON SEQUENCES as well if you have any serial columns. Possibly SELECT and UPDATE, too.

There may be more problems, the question is not completely clear.

Related:

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?
Can't alter default privileges

Note: There is an open bug in pgAdmin, the display can be incorrect (still kicking in the current pgAdmin 1.22).


How to count occurrences of date only in a field that contains datetime data?

You just need to format the timestamp so it only shows the day, then group by that:

select date_format(foo_timestamp,'%Y-%m-%d'), count(*)
from foo_table
group by 1
order by 1;


What is an effective way at handling the issue of Postgres allocating significant memory when it forks a new process?

Interesting to hear that 10MB is "a significant amount of memory".
A database is not a web server, which is optimized for serving lots of short-lived connections. A PostgreSQL connection loads cached catalog data for efficiency.
That is why you use a connection pool, so that all your short database requests are handled by a small number of persistent database connections.
I doubt that this is specific to PostgreSQL – other databases benefit from connection pools as well, and some even have one built into the server. So I would see the statement you quote as hate speech from a competitor who cannot think of anything better than reiterating the old myth that PostgreSQL is slow and complicated.


Use STUFF function to insert at multiple points in a string?

You need to get it as rows before you can use STUFF.

DECLARE @rows nvarchar(max) = '[A],[B],[C]';
DECLARE @cols nvarchar(max);

SELECT LTRIM(RTRIM(j.value)) FROM STRING_SPLIT(@rows, ',') j

SET @cols = STUFF((SELECT ', ' + (col)
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,2,'');

SELECT @cols;

SET @cols = STUFF((SELECT ', ' + ('SUM(' + col + ')')
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,2,'');

SELECT @cols;

SET @cols = STUFF((SELECT ', ' + ('SUM(' + col + ') ' + col)
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,2,'');

SELECT @cols;
GO

| (No column name) |
| :--------------- |
| [A]              |
| [B]              |
| [C]              |

| (No column name) |
| :--------------- |
| [A], [B], [C]    |

| (No column name)             |
| :--------------------------- |
| SUM([A]), SUM([B]), SUM([C]) |

| (No column name)                         |
| :--------------------------------------- |
| SUM([A]) [A], SUM([B]) [B], SUM([C]) [C] |

dbfiddle here


Most efficient queries on flags in TSQL: bit field or table join?

It is very dependent on the makeup of the data, how the data changes over time and what queries are run as to what the most efficient method is.

I would suggest that a filtered index is the solution that is going to be the most efficient without talking about a specific scenario.

Edit:

You should add a filtered index filtered on where optIn = 1 with the fields that are required by the query of consequence as it will use this index to generate the result and not have to refer to the main table at all.  To check that it reads from just the index you can analyse the execution plan.  I would expect that the query of consequence would not require all of the fields for the user record and thus the fields in the index would be a subset of the fields in the actual user record.

In short the goal is to do a little extra work on inserting the record (updating the indexes) and storing extra data so that the query has to read a significantly smaller amount of data that is in continuous blocks on the disk (assuming we are talking about traditional disk technology) than it would if it had to scan through the whole table picking about the records and fields in those records required by the query.


Convert UTC time to local time with specific format in Redshift


Postgres streaming replication to a different schema on another server?

Streaming replication can't do this - it can only replicate the whole database cluster together, as a unit.

I think you want Londiste, Bucardo, or maybe even Slony-I for this. I've used Londiste for similar jobs and it works well.

There's also ongoing work on an in-core feature that meets this need, logical replication / bidirectional replication. It's pre-alpha quality, though, so I suggest waiting until it appears, hopefully in PostgreSQL 9.5. Parts of it have been included in 9.4, so some 3rd party tools may be able to use it down the track.


Here's what I came up with -- thanks to @CL. for the tips. I made it into a view so I can just query against that for a particular user.

CREATE VIEW user_permissions_deep AS
SELECT DISTINCT user_id, permission_id
FROM (
    SELECT urd.user_id user_id, pr.permission_id permission_id, urd.role_id role_id, urd.parent_id parent_role
    FROM (
        WITH RECURSIVE
          deep_roles(user_id, role_id, parent_id, depth) AS (
            SELECT ru.user_id, rr.role_id, rr.parent_id, 0 depth
            FROM role_roles rr
            INNER JOIN role_users ru ON rr.role_id = ru.role_id
            UNION
            SELECT dr.user_id, rr.role_id, rr.parent_id, dr.depth+1
            FROM deep_roles dr, role_roles rr
            WHERE rr.role_id=dr.parent_id
            AND dr.depth < 10
          )
        SELECT user_id, role_id, parent_id FROM deep_roles
        WHERE depth > 0
        UNION
        SELECT user_id, role_id, null parent_id
        FROM role_users
    ) AS urd
    INNER JOIN permission_roles pr
        ON pr.role_id = urd.role_id
        OR pr.role_id = urd.parent_id
    UNION
    SELECT user_id, permission_id, null role_id, null parent_id
    FROM permission_users
) AS upd
INNER JOIN permission p ON upd.permission_id = p.id;

I found I had to keep the depth check otherwise the loop would never exit if there was a cycle in the role inheritance chain.

If anyone has any suggestions on how this could be improved, please let me know!


How to turn SELECT statement into a DELETE statement

You can just use the select statement in the where clause and just select for the element you are matching on

DELETE FROM message_cus_board
WHERE message_id NOT IN (
    SELECT message_id
    FROM message_cus_board mcb
    WHERE mcb.board_id IN
        (SELECT mb.board_id
         FROM message_boards mb
         WHERE mb.customer_id = :customerid
         AND mb.universal_board = 'Y'))


Does SQL/MED (ISO/IEC 9075-9) defines a writable foreign data wrapper interface

Yes, it does define a writable interface.


Join second table based on third table with conditions

There are several problems with the query

The select list is missing.
The join syntax needs ON, not WITH:  a JOIN b ON <some condition>
There is a missing on condition (what you correctly identified as arbitrary/random join between Contractor and ContractorJob).
There are column names mismatches between the code and the design: (Job.contractor_job vs. Job.id). Which is the correct one?

How to write it with LEFT JOIN / IS NULL. The parentheses are redundant, placed only for making the joins more clear:

select
    c.*
from
        Contractor as c
    left join
        (
            ContractorJob as cj
        inner join
            Job as j
        on
                j.id = cj.job_id
            and j.start_date < :start_date
            and j.end_date > :end_date
        )
    on
        c.id = cj.contractor_id
where
    cj.contractor_id is null ;

How to write it with NOT EXISTS which I find easier to grasp:

select c.*
from Contractor as c
where not exists
    ( select *
      from ContractorJob as cj
        inner join Job as j
        on  j.id = cj.job_id
      where j.start_date < :start_date
        and j.end_date > :end_date
        and c.id = cj.contractor_id
    ) ;


How to find functional dependancies from simply looking at a relational schema?

You can't tell the functional dependencies just from the schema.

If you knew that the schema was in third normal form (3NF) or higher and you knew that there are no other candidate keys other than the primary key, then you would know that every non-key attribute was fully functionally dependent on the primary key.

Except for this particular situation, there is nothing about a schema that conveys functional dependency.  The purpose of a schema is to convey table structure, not functional dependencies.


Best way to setup master to multi master replication

There are four(4) options you need to combine
Option 1: MySQL in Star Topology
So as not to reinvent the wheel, please read my past posts about this subject

Mysql Master-Master Replication Topologies on >2 machines
https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5781504#5781504
https://serverfault.com/questions/264374/mysql-in-star-topology/264444#264444

Option 2: Use DRBD

The supermaster should be a DRBD pair and using a DBVIP
The Star Topology's distribution master should replicate from the DRBD primary
All Slaves should replicate from Star Topology's distribution master
See my past post on using DRBD in Circular Replication between two datacenters (DRBD cannot sync well over geographic distance but can provide disk-level + failover capability per data center)

Option 3: Use Semisynchronous Replication
Any network latency can affect the collecting of binary logs and its shipping of entries over a network could be bottleneck. Using MySQL 5.5's Semisynchronous Replication can allow you to tune the MySQL Heartbeat characteristics so as to minimize replication losing its place due to any significant network latency.
Option 4: Circular Replication
If you have multiple masters, circular replication is a must. There are many products out there for providing circular replication, such as mysql-mmm (mentioned in the comment), which is great within the confines of a single datacenter. This is only one piece of your puzzle in that four(4) things must be accommodated outside of just circular replication:

Network latency
Geogrpahic Distance
Disk-level Redundancy per DataCenter (handled by DRBD)
Automatic Failover of DBVIP backbone of DRBD (via ucarp)

CAVEAT
This may not be your full answer but these options can provide ideas you can try out in different scenarios. For example, when in the star topology, you could do the following:
SCENARIO #1

Have the DB Server at one data center be SuperMaster holding InnoDB data
Have the Distribution Master in the same data center (binary logs only)
Have all Slaves replicate from the Distribution Master

SCENARIO #2

Have the DB Server at one data center be SuperMaster holding no data, which means the supermaster is the Distribution Master (binary logs only)
Have all Slaves replicate from the Distribution Master

SCENARIO #3

Setup a Distribution Master in each Data Center in Circular Replication
Within each Data Center, have MySQL Instance (Local SuperMaster) with InnoDB as the only Storage Engine
From each Local SuperMaster, hang two or more read slaves. Each read slave should use MyISAM as the main Storage Engine. Each MyISAM table should have ROW_FORMAT=FIXED to increase read speed 20-30%

As far as the possibilities go, it's up to your imagination and department budget.
Links for MySQL and DRBD

http://dev.mysql.com/doc/mysql-ha-scalability/en/ha-drbd.html
http://downloads.mysql.com/docs/mysql-ha-drbd-en.a4.pdf

DRBD for Disk Level Redundancy and ucarp for DBVIP Automatic Failover

http://www.drbd.org
http://www.ucarp.org


Slony initial subscription is causing out-of-memory kernel panics. Why?

The problem turned out to be unrelated: in /etc/sysctl.conf, the system's shmmax value was set to an amount greater than the available RAM.

Setting it to 60% of RAM (our DB consultant's recommendation) solved the problem.

Why this issue didn't crop up before is a mystery to me.


In an almost literal translation from English to SQL, I would write the query as follows:
select guestNo
from guest
where guestName like 'Peter%'
and exists (
    select
    from booking
    where guestNo = guest.guestNo
    and dateTo is null
);

NB Postgres unlike some other DBMSes doesn't need to have any columns in the select clause! That's ideal in the subselect here as we're only interested in the existence of a record, not in its value).
See https://www.db-fiddle.com/f/tpzgVMwkQGAHBFxyMkyJvj/17


How to determine if pg_dumpall is still running

You can see if a dump is running from within PostgreSQL by checking for pg_dump or pg_dumpall as the application_name in pg_stat_activity. It's not perfect though, since the client has to connect to each DB in turn, so there might be a period with no visible connection. I haven't verified whether pg_dumpall maintains its connection throughout, while running the sub pg_dump sessions, or not.

You can use the handy flock command to maintain pid files at the shell level.

Personally, though, I strongly recommend simply rotating your dumps. Dump to a temporary file, and only when the dump is complete replace the old dump with the new one using the mv command. That way there is no window during which the dump file is invalid. If you replace the dump file while it's being read, anything reading the file will continue to read the old dump file it has open and won't care that it's been replaced.


How do I correctly group output from multiple tables?

So from what I can infer, your desired output is a column of all the dates from all 3 tables, with the 3 tables joined onto it.

So something like

Select dates.date, tbl1_col, tbl2.col, tbl3.col from
    (select distinct Date from table1
    union
    select distinct date from table2
    union
    select distinct date from table3
    )dates --Now we have a list of all the dates from all the tables.
left join table1 on dates.date=table1.date
left join table2 on dates.date=table2.date
left join table3 on dates.date=table3.date
--we add the tables back on, keyed against the dates.

could be what you are looking for. Just an idea, reading into how you have phrased it.


SQL server standard 2016 fail over cluster Amazon AWS

I did a presentation on this during the summer @ SQL Saturday - LA and you have a few options.  In some cases, you need clustering if Mirroring isn't an option and AlwaysOn AGs require cross database (but same instance I believe) transactions using MSDTC on some versions of SQL Server.

Thus in order to provide failover clustering support in AWS I used an AWS Storage Gateway to act as a routable SAN.  This was simple to setup and was secured easily via the AWS security groups.  We then used mirroring to mirror to Azure and on Azure I used the new SQL and Windows 2016 shared storage direct solutions which are an option for AWS if you use Windows 2016.

Here are the relevant slides on setting up a storage gateway:

-Select the "Storage Gateway" option from the AWS console.

-Select "Cached Gateway" from the options when you start

-Select "Cache Volume" inside the gateway.

-CHAP has to be fully configured. It is fully supported though.

Please note I had to configure both secret and not secret auth for the configuration to work.  YMMV

S2D Options For Windows 2016:
Here are a few items I had to do with S2D on Windows 2016.  Note the hypervisor in this case is Azure but same concept applies:

-These are the S2D requirements for us, read the docs for your environment.

-This tripped me up, be sure to find and use the 'Friendly Name'.

-You can verify it now

-Verify your steps on Node 2 and after this you will pass validation in WSFC.


Getting parsable deadlock XML from SQL Server error log

Trace flag 1222 will

  Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

For the limitations (cannot use - Extended events (and that includes the default system health extended event) or WMI event notifications (script that I wrote to log deadlock info to a table)) that is the max you can get.

Remember that system_health session is running by default.


Query requires 'Update statistics' very often in SQL Server

Data is bulk inserted into these tables in the order of ~ 200k rows per day... out of ~5 million rows

That's 200k new rows that do not have any stats for, so querying for those values will make the optimizer come up with scans because of the outdated stats do not inform it that the new values are in the range sampled.  Force a stats update after the bulk insert.

Not sure autostats can help given that 200k inserts fall bellow the 5 million 20% cadinality trigger, see Statistical maintenance functionality (autostats) in SQL Server.


Set of values of a field of a related entity must be unique: How do I express this constraint in SQL?

LAST UPDATE:

  I want to allow duplicate key in the table Type, but I want unique key for each GroupOfInstances – Patrick 21 mins ago

The answer do not match the question. Sorry...

Imagine that you have created the constraint which you need.

Imagine that you have a group with keys id=1 and id=2, and you want to create another group with keys id=1, id=2 and id=3.

Formally it is correct - in the final state the groups will have different keysets. But in dynamic...

You try to execute

INSERT INTO groups_to_keys (group, key) VALUES (2,1), (2,2), (2,3)

You hope that all records will be inserted... but the constraint is checked for each separate inserted record! So 1st value pair will be inserted sucessfully, whereas inserting 2nd pair will be rejected - at this moment the group 2 will fully duplicate the keyset for group 1!

But if you'll try to execute

INSERT INTO groups_to_keys (group, key) VALUES (2,1), (2,3), (2,2)

then all records will be inserted successfully.

The only simple solution I see is de-normalization. I.e. the keys are stored not in separate joining table but as an attribute of group entity in some arrayed form (for example, JSON). Such method allows to create UNIQUE constraint over this field (common UNIQUE index).

Of course this method do not allow to use the FK integrity check.

One more solution - separate table which stores unique keysets, and group is referenced to it. Looks like previous, but this allows to create predefined keysets, including those which are not assigned to any group yet. This table may be service one and available to users for reading only. Or even the user may have privileges to read and to insert, but not alter or delete.


The doubling up is because of your nested parenthesis. Processing will start with the inner-most pair and work outwards. In this case the innermost replacement is 1 -> Apple meaning 11 -> Apple1 -> AppleApple.

Reverse the nesting, starting with the longest source value and work outward. Better still, put the mappings in a separate table and join to it (if Foxpro supports join-like syntax).


Why is dropping a primary key expensive?

One of the reasons dropping the PK constraint, and the underlying clustered index, is expensive is that the underlying data is transformed into a HEAP. This affects all of the non-clustered indexes which have to update the pointers back to the row data from the old clustered index key to the new HEAP row. From Docs:

  The pointer from an index row in a nonclustered index to a data row is
  called a row locator. The structure of the row locator depends on
  whether the data pages are stored in a heap or a clustered table. For
  a heap, a row locator is a pointer to the row. For a clustered table,
  the row locator is the clustered index key.

So once you drop the PK and clustered index, the non-clustered indexes have their row locators updated from the clustered index key to the heap pointer. Depending on the size and number of indexes, there is potentially a substantial cost involved in this update.

This is also explained in this article:

  Dropping a clustered index can take time because in addition to
  dropping the clustered index, all nonclustered indexes on the table
  must be rebuilt to replace the clustered index keys with row pointers
  to the heap.


MariaDB - Slow GROUP BY on 55 Million row table

Some bugs:  Each time range leaves out one midnight.  Your first example is 2 months minus one second; nowhere near one "quarter"  The second is one quarter minutes one day minus one second.

As for the attempts you have made in indexing, this one is probably optimal:  (occurredAt, userId, trackId).  Rationale:

The first column completely handles the filtering for the WHERE.
The rest of the columns make the index "covering", so only the index will be used.

Note:  There could be caching issues that confuse the results of timing tests.

The reason the query with an id range may have seemed faster could be caching.  I would not trust it to be faster.

This may work better:  INDEX(userId, tracked, occurredAt)  I say "may" because it depends on a messy optimization that your version may not include.  Note that it could use the first two columns to handle the GROUP BY, thereby avoiding a sort.  And it is "covering".  But it depends on being able to hop through the index.  Suggest getting EXPLAIN FORMAT=JSON SELECT ..., which might provide enough clues.  Or Optimizer Trace.

A big Yes to a summary table.  Depending on the distribution of the data, I might add rows to it each night, then tally up the rows to get the quarterly total.  (This would let you get monthly or weekly tallies, too.  And they would be equally fast.)

A Rule of Thumb when designing a Summary Table:  Aim for one-tenth as many rows in the summary table as in the 'Fact' table.  (If the 'averge' user listens to only one track per day, daily subtotal would be useless.)

More on summary tables:  http://mysql.rjweb.org/doc.php/summarytables

Another issue...  What is the setting of innodb_buffer_pool_size; how much RAM do you have; and how big is the table?  (I would guess 5GB for "Data_length".)  If you have a small machine, we need to discuss shrinking the datatypes, normalization, and tuning.


MySQL: How to ignore /etc/mysql/my.cnf for mysqld --initialize

The manual mentions that the --defaults-file option has to be specified first, if you want to use it.
Otherwise the option is ignored.


Is it Ok to have one table with two relations?

If your user community sees these as two separate things then model them as such.  If a row for one type populates one set of columns but always leaves other columns empty whereas a row for the second type populates the columns the first type left empty, that is an indication that separating them may be beneficial.  There are many other things to consider, however, such as use patterns, update frequency, the number of columns involved and the characteristics of your particular DBMS software in each permutation.  You may be willing to accept a compromised design if it runs quickly.


So, no need to test as it turns out, I just remembered the key deciding limitations.  You can only create one text index on a collection (reference here), so you have no real choice.  Additionally, MongoDB can only use one index at a time to satisfy a query (until index intersection is introduced in 2.6).

Hence, the only workable option is to create the compound index as you outlined, on both fields as a single index:

db.articles.ensureIndex(
                     {
                       title: "text",
                       abstract: "text",
                     },
                     {
                       weights: {
                                  title: 1,
                                  abstract:1,
                                },
                       name: "TextIndex"
                     }
                   )

The same remains true for 3 or 4 fields, you would first have to drop the existing index, then create the new index and include 3/4/other.


Can we use wildcard characters on the PostgreSQL hba_conf file for database names?

According to the documentation for The pg_hba.conf File, you can have:

database
Specifies which database name(s) this record matches. The value all specifies that it matches all databases. The value sameuser specifies that the record matches if the requested database has the same name as the requested user. The value samerole specifies that the requested user must be a member of the role with the same name as the requested database. (samegroup is an obsolete but still accepted spelling of samerole.) Superusers are not considered to be members of a role for the purposes of samerole unless they are explicitly members of the role, directly or indirectly, and not just by virtue of being a superuser. The value replication specifies that the record matches if a replication connection is requested (note that replication connections do not specify any particular database). Otherwise, this is the name of a specific PostgreSQL database. Multiple database names can be supplied by separating them with commas. A separate file containing database names can be specified by preceding the file name with @.

That is, wildcards are not possible, although you have all the following alternatives:

The value all, which is actually working as a wildcard matching "all databases".
The value sameuser, which is a database with the same name as the user that connects to the database.
The value samerole
The value replication
Multiple database names, separated by commas.
@FileName, specifying a file which contains the names of the databases

In your specific case, you could use:

fcca, fccb, fccd, ...
@fcc_names.txt

(and have fcc_names.txt filled in with all fcc* database names)


What is the best option for High availability on a data warehouse?

Most data warehouses are in Simple recovery model, and most true HA solutions require Full recovery (AGs, Mirroring). The minimum for Log Shipping is Bulk Logged, but that's not really true HA since there's no automatic failover.

If that's the case for yours (because really, a data warehouse in Full recovery is banana-town-crazy), your best bet would be a Failover Cluster.

It doesn't care what recovery model your databases are in, since it's more reliant on Windows than SQL Server. Your SQL Server just has the option to live on different nodes in the cluster if something goes amok with one. The one requirement of this technology is using shared storage, like a SAN.

You get automatic failover in most situations, but no readable replica (you don't get that with BAG, either). With Standard Edition, you're limited to a two node cluster, but that shouldn't be a big deal.

Failover Clusters are also a lot easier to manage if you're not too savvy with SQL Server. You really need basic Windows sysadmin skills. AGs can be a tough cookie when they go down, or when a patch goes bad (which sadly happens rather often).


Is it good practice to create a separate table for views count?

At a mere 10 updates per second, this is a non-issue.
When you get to 100/sec, and are still using HDD, then we can discuss further.  Or 1000/sec with SDD.
At higher rates, yes, violate the textbook principles and have the view-counter in a separate table (with only the count, plus page_id as the PK page_id).  The reason is to avoid conflicts with non-counter accesses to the main table.
If you are keeping track of each 'view' as in a table of "who viewed what, when", the problem gets messier.  On the one hand, there are INSERTs into that table (again, 10/sec is not a problem).  On the other hand, SELECT COUNT(*) ... will have extremes -- a count of 100 is no problem, but a count of a million can be.
"Likes" have similar issues.
For more extreme traffic, you need to gather up the updates/inserts, consolidate them, then apply them.  This might get you another 10x speedup, at the expense of some complexity and a few seconds delay in updating the counters.
But, by that time, you will have outgrown a single server, and other solutions will be needed for all your problems.  Sharding is a likely part of this next level of design.
For any system that starts small, and grows to be huge, you must expect to do a major redesign every so often.  For you (today), moving the counters out is premature.  However, doing so could forestall (for a while) the next major redesign.
Rehash
Plan A:  (all in one)
CREATE TABLE Blog (
    id INT UNSIGNED AUTO_INCREMENT,
    lots of meta info -- title, etc
    view_ct INT UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
);

Plan B:  (split out just the counter)
CREATE TABLE Blog (
    id INT UNSIGNED AUTO_INCREMENT,
    lots of meta info -- title, etc
    PRIMARY KEY (id)
);
CREATE TABLE BlogViews (
    blog_id INT UNSIGNED,   -- not A_I; for joining to Blog
    view_ct INT UNSIGNED NOT NULL DEFAULT '0',
    ts TIMESTAMP NOT NULL,   -- optional -- time of last viewing??
    PRIMARY KEY(blog_id)
);

Discussion of Plan A:

Simpler
Good enough for "low traffic" website -- say 10 views/sec.

Advantages of B:

Needs JOIN, but only when both meta and count are needed together.  This JOIN is NOT a big burden.
Updating the count hits only BlogViews, thereby not interfering with any queries that need only meta info, especially UPDATEs to such.
Needed for busy website -- say peak loading of 1000 views/sec.

When to use C:

Thousands of views/sec.
C involves collecting views, consolidating them, then updating a structure like Plan B.
This further isolates both Blogs and BlogViews from interference.
View counts may be slightly delayed (seconds).
(Further details can be discussed elsewhere)

Plans A2, B2, C2, D2:

These are modifications to the other plans, wherein you keep track of 'who' views the blogs 'when'.
This needs to worry about SELECT COUNT(*) instead of merely SELECT view_ct.
SELECT COUNT(*) can be costly if you have a million views.
These extensions are best handled with "Summary Table" design concepts, which I cover here.

Plan E (Now that the actual schemas are presented, I'll call that E):
For video_blog_views_tracker, get rid of id, and have
PRIMARY KEY(video_blog_id, user_ip_address)  -- should be unique

That should be optimal for the counter query:
SELECT SUM(counter) FROM video_blog_views_tracker
    WHERE video_blog_id = ?

Yes, rolling that into video_blog.views via a TRIGGER or CRON job is possible.  But I would not do it until the need is determined.


Why does SQL*PLUS need a slash after CREATE TYPE?

you need a / after a PL/SQL block in SQL*Plus:

SQL> begin
  2     null;
  3  end;
  4  -- here you need a /
  5  /

PL/SQL procedure successfully completed

This is so SQL*Plus knows you are done with your statement (which could include intermediate, non-terminating ;).

SQL types may include PL/SQL code, therefore the SQL*Plus devs decided that you need a / in all cases after a CREATE TYPE:

SQL> CREATE OR REPLACE TYPE t AS OBJECT (
  2     x NUMBER,
  3     MEMBER PROCEDURE setx(p_x NUMBER)
  4  );
  5  /

Type created

SQL> CREATE OR REPLACE TYPE BODY t AS
  2     MEMBER PROCEDURE setx (p_x NUMBER) IS
  3     BEGIN
  4        x := p_x;
  5     END;
  6  END;
  7  /

Type body created

Note: you also need a / after you define a procedure, a package or a package body (for the same reason).


use an Index correctly to optimize queries

Your query is no faster with the index because SQL Server has determined that it would be more efficient to do a Clustered Index Scan, than use the IX_ActCost_ScenarioID that you have defined and perform a Key Lookup to retrieve the extra data needed.

As you've only defined the index on ScenarioID, with no INCLUDE columns, each extra column you wish to return from the query would need to be retrieved from the Clustered Index. On 50,000+ rows, this simply wouldn't be efficient, hence the index is ignored.

Firstly, you need to ask yourself if you really need ALL columns to be returned. I somehow doubt you do (the ID columns for example), but I may be wrong.  In any case, you should always define the column list rather than using *.

SELECT
 ActID,
 ActCostTypeID,
 Description,
 Cost,
 <rest_of_columns...>
FROM dbo.ActCosts
WHERE ScenarioID = 456;

Notes:
1. Don't avoid the schema prefix
2. Avoid using SELECT *

You then need to include the extra columns returned in IX_ActCost_ScenarioID:

CREATE NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost] ([ScenarioID] ASC)
INCLUDE ( <list_all_other_columns_returned> );
GO


Can I specify a name for the index which SQL Server creates for my unique constraint?

Referencing the documentation for Create Unique Constraints, you should be able to use something like this:

CREATE TABLE [dbo].[Something](
    [IdentityId] [uniqueidentifier] NULL,
 CONSTRAINT [MyUniqueConstraintName] UNIQUE NONCLUSTERED
(
    [IdentityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


why HDD is more sensitive to the change of innodb_fsync_threshold than SSD

Might a hardware perspective explain the differences?
The steps of disk I/O are something like:

buffering (for writes)
send data to the device
"Seek" the desired cylinder on the device
Wait for the read/write head to be over the sector that needs read/written
perform the I/O
buffering (for reads)

Steps 3 and 4 are the dominant time consumers for HDD.  For SDD there is no "head" to move nor platter spinning, so both of these steps take no time.
More to the point, 3 and 4 take a variable amount of time, based on, well, the phase of the moon.  And a good controller, etc, can play games that optimize disk activity.
Most benchmarks have "real life" flaws.

They ignore interference from other threads -- that may be fighting for access to the same drive.
They pound on the resource to find the "max" that can be handled.  While this is an interesting metric, in real life if the server is at the max, it is about to keel over.  A butterfly flapping its wings can cause it to fall off the cliff.

Some optimizations:

RAID can cache things -- read and/or write -- to smooth out activity.
RAID can "lie" about when action is finished.  This is because, for example, a "battery backed write cache" can safely hold a write in ram on the controller while returning "successful write" to the user.  This may lead to a puzzling inflection point in the graph -- the device will suddenly slow down when the write cache is full.


Open source tools for Oracle schema compares

You might want to have a look at Liquibase - according to the Liquibase documentation, it can perform diffs (I'm not 100% sure whether this requires using Liquibase to version your schemas, though).


What index can I create to help a query using multiple datetime columns and datetime functions in its where clause?

I think you can benefit from rewriting this query first, plunking it into a TVF and adding a non-clustered index on ValidFrom and DateEffective.

create nonclustered index ix_networks_validfrom_dateeffective
    on dbo.Networks (ValidFron, DateEffective)
    include (Id, Descriptor);
go

create function dbo.ValidNetworks (
    @dt datetimeoffset(7)
)
returns table
as
    return (
        with networks as (
            select
                Id
                ,Descriptor
                ,ValidFrom
                ,DateEffective
                ,isnull(ValidUntil, dateadd(day, 1, @dt)) as ValidUntil
                ,isnull(DateExpired, dateadd(day, 1, @dt)) as DateExpired
            from
                dbo.Networks
        )

        select
            Id
            ,Descriptor
        from
            networks
        where
            ValidFrom <= @dt
            and DateEffective <= @dt
            and ValidUntil > @dt
            and DateExpired > @dt
    );

You could also create computed columns for ValidUntil and DateExpired and eliminate the CTE from the TVF.

I would also be curious to see this split into two separate queries and union'd. Whereby in both queries you filter on ValidFrom and DateEffective. In one you filter for null values in ValidUntil and DateExpired, and the other compare against the @dt value.


When Master is being auto vacuumed, Is slave gettting exclusive lock on its table to apply WAL?

If autovacuum finds a bunch of empty pages at the end of the table, it will try to truncate those pages away to return the space to the OS.  It will need to obtain an Access Exclusive lock to do this, and that lock is recorded in WAL and is replayed by and held by the replica server for the duration of the truncation (and replay of the truncation WAL).  hot_standby_feedback will not directly prevent this lock from being taken, although it may delay the pages from becoming empty (as the tuples won't be removed if they are still needed on the standby) but sooner or later they will need to go.

If you are truncating away a large amount of empty pages, this can take a while (but that performance was improved substantially in v10).  If the truncation on the master detects that it is blocking someone, it will stop the truncation before completion, leaving the rest of the empty pages to be truncated by some future vacuum.  But it cannot detect that it is blocking queries on the replica, so it cannot abort the truncation.  Instead of killing the autovac worker, you could have just opened a session on the master and taken a dummy lock on the table to force the autovac worker to yield gracefully.  The dummy lock does not need to be held for a prolonged period, it can be dropped as soon as it is acquired, and it can be a weak lock, even just Access Share.

As far as I know, this is the only situation in which autovacuum will take an Access Exclusive lock.


How to convert a single MySql table to utf8mb4 (from latin1)

Going by what you have to do within a PHP-Webserver environment to use utf8 / utf8mb4, you have up to 5 or more construction places. And you covered 2 of them

collation of table and column(s)
overwork existing string-data in said columns

leaves:

make sure your application / tool uses the correct collation as well
make sure you communicate with the database using the correct collation
set the frontend of your application / tool to handle said collation

As JAVA is well prepared I guess its the database connection ...


Quite simply: Null VaLue

The function substitutes any NULLs in a given resultset column with the value given as the second parameter.


How to check programmatically which replica I'm connected to

You can run SELECT @@SERVERNAME which will give you the current servername you are connected to.  Would this suit your requirements?


Converted ERD into Dependency Diagram 1NF, 3NF

The first, you should read the NORMALIZATION concepts (1NF,2NF,3NF,...) after that you can use them to verify your dependency diagram. So, I'm talking about some basic steps to help you convert ERD, which I often do:

1- Identify objects (objects are WHO, WHERE, WHAT, WHEN ), you imagine that they are exited
and can be defined.
2- Identify natural keys of objects and their properties,...
3- Identify relationships of objects. The step help you answer some questions:

What actions?
Who took place actions?
Where actions are happened?
When actions happened

4-If we have many-many relationship, we separate them into an individual table. Otherwise, we use foreign keys or not.

5- Verify our diagram matching which NF.

Now, we come back your ERD and we will convert it to dependency diagram.

Step 1- identify objects: Here, we can understand that we want to manage list of appointments of doctors and patients. So, we have 2 objects Doctor and Patient

Step 2- identify natural keys/properties of objects: you listed out above.

Step 3- identify relationships of objects: As you know, a patient can have zero or many appointments with doctors and a doctor can have zero or many appointments with patients. So, we have a many-many relationship.

Step 4- Because we have a many-many relationship, we create a table here.

Finally, we have a diagram

Doctor(Doc_FEmpID,Doc_Fname,Doc_FCellPhone)          Patient(Pat_PatientID,Pat_PatientName,Pat_Phone,Pat_Address,Pat_City) Appointment(App_Num,Doc_FEmpID,Pat_PatientID,App_Date,App_Time)

Step 5- verify the diagram again.


After I kill my clients, MySQL continues to process queries for hours

Here is a VERY casual explanation...

MySQL works like this:

You send it a query that has a result set larger than its buffers, so it creates a temp table on disk to hold the data. That is an expensive process as it is usually reading from, swapping and writing to the same disk, unless purposely configured differently. Nonetheless, performance tanks. So now you're thinking "I have to do something, right?" So you kill your PHP process. The question now is "did your query process get killed, or is it running or is it now freeing space?" To find out what state your process is in, you'll need to run this as root or a user with the PROCESS privilege:

SELECT TIME,STATE,ID,HOST,DB,INFO
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE INFO IS NOT NULL
ORDER BY TIME DESC LIMIT 3\G

This will give you the top three longest running processes. Just increase the limit to reveal more. If you really want to end the offending process, look at the ID field and KILL that #.
E.g.

*************************** 1. row ***************************
 TIME: 0
STATE: executing
   ID: 4
 HOST: localhost
   DB: NULL
 INFO: SELECT TIME,STATE,ID,HOST,DB,INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NOT NULL ORDER BY TIME DESC
1 row in set (0.03 sec)

mysql> KILL 4;

This won't simply kill all the things happening with that ID, but at least you'll be assured that it's definitely set into a STATE of "FREEING ITEMS" or equivalent.


PostgreSQL to MySQL migration to use database for WordPress site - how to's?

Any specific reason to migrate to MySQL?

https://wordpress.org/plugins/postgresql-for-wordpress/

Else MySQL Workbench will work. You'll always have to take into consideration any functions/triggers, etc. that might contain advanced pgsql syntax, etc.

http://mysqlworkbench.org/2012/11/how-to-migrate-postgresql-databases-to-mysql-using-the-mysql-workbench-migration-wizard/

As @Max said, good luck!!


Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite?

The documentation says:

  Statistics gathered by ANALYZE are not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.

Here, "significantly" means that the new data changes the characteristics so much that the index is no longer as useful, or that it is much more useful than previously.

However, for a primary key, the selectivity usually does not change, so it should not be necessary to run ANALYZE after larger inserts.

(With or WITHOUT ROWID does not make any difference for this.)


Database encryption and key management with pg_crypto

Note that I am not a crypto systems or security expert. This is what I've seen done and it makes sense, but I cannot claim it doesn't have security issues beyond what I outline below. As always, get a proper security audit if it's important.

In the following, "credential" can refer to ssh keys, passwords, or whatever needs storing.

The typical model is to use a couple of levels of crypto.

For each user, generate and store a public/private keypair
Store the public part of the user's key in the clear in the user's record
Store the private part of the user's key encrypted to the user's passphrase in the user's record.
Encrypt each credential to a symmetric key that's unique to that credential
Encrypt each credential's symmetric key to the public key of every user who should have access to that key, and store the resulting encrypted material in a user_credentials mapping table.

Essentially, you maintain a keyring, much like the gpg keyring where the public part is in cleartext and the private part is encrypted to a passphrase. You should make sure the passphrase selected by each user is strong.

Now you can generate a symmetric key for each encrypted credential. Encrypt this key to every user's public key and store it in a user-credentials join table. Never store this symmetric key unencrypted, and use a different symmetric key for each credential.

You land up with something like this

[user]
user_id
username
login_password_salt
login_password_hash
public_key_text
private_key_sym_encrypted_to_passphrase

[user_credential]
user_id
credential_id
credential_key_encrypted_to_user_public_key

[credential]
credential_id
credential_data_encrypted_to_credential_key

There are quite a few advantages to doing things this way:

If a dump of your database is leaked your credentials are still safe, or as safe as your passphrases and key strengths.
Since you have a different symmetric key for each credential you can control which users have access to which credentials
Any user with access to a given credential can give another user access to it; they just decrypt it using their key and encrypt it to the other user's public key. You don't need to get everybody together to add credentials or add new users.
Because you're encrypting each credential to a symmetric key, and then encrypting that key to the user's key, you can change the stored credential without having to re-encrypt it to each user's key. It just makes updating stored credentials more convenient.

However, if the database host is compromised, an attacker can easily extract the passwords any given user has access to as soon as that user logs in. They capture the user's passphrase by wrapping functions or by enabled detailed logging and searching the logs. Then they can decrypt passwords in a stolen dump or from the live DB. A compromised database host is no danger until a user logs in to use it but then all is lost.

This means that the host of the database is security critical. Leaking the dump isn't fatal to security, but someone being able to modify the code running on the DB is a fatal security breach.

In addition to using your credentials management DB to extract ssh key material, passwords, etc when you need it, you can also use it as an authentication proxy where it can auth against a service without your user having to ever be able to learn the credential used. For example, your app might decrypt an ssh key, add it to an ssh agent, and use the decypted key to log in to a server via the agent without the user ever being able to see and access the key directly.


How to enforce that one row in a parent table MUST be associated with at least a row in a child table

There is no declarative referential integrity to enforce one or more children in SQL Server.  You have to enforce this with application logic.

Note too that if you are using a foreign key constraint on the child, you can't save the child until the primary key of the parent is known.  This means that you need to have a transactional wrapper (BEGIN TRAN / COMMIT) around the insert of the parent and it's first child, so that the parent is not kept unless the child is also successfully inserted.


Can oscillating number of fragment decrease FTS query performance?

Fragmentation can cause query performance issues if there is a significant number of them. I've generally seen it with fragments counts greater than 50 to 100 fragments for the specific full-text index. Not generally noticeable with 20 or less as you describe. It also sounds like master merge is doing its job if you see the number of fragments changing from 2 to 20 and then back down again. This would indicate that as population runs and increases the fragments that the master merge process is then reducing the number of fragments.

Execution plans will not change as a result of fragmentation because Full-Text is implemented via a TVF. So all you ever see in the execution plan is a TVF no matter how many fragments there are. If you are seeing execution plan changes, it is likely due to other reasons than fragmentation. Regrettably, there is currently no way to see what queries actually are in the TVF to resolve your full-text search request. The more complicated your search request, the more work full-text may need to do.

If you are experiencing plan changes and believe that full-text is the reason for the plan changes, it is more likely due to the estimated/actual number of rows the TVF is expected to return. Another thing that could affect performance is what point the TVF is joined to the table and whether or not the plan processed the TVF first or the table first. Search conditions for full-text obviously affect how many rows are returned so the one question I would have is do you get the same performance repeatedly for the same exact search term? Does the performance change as search terms are changed? Is there any wait stats for the query or is the query getting blocked? All these things may help further understand things.

More work is needed to fully understand why you are getting variable performance with full-text.

MORE INFORMATION
Fragments are created during population. This helps with speeding up the population process. Internally, each fragment is a separate table, so think of querying a lot of fragments as UNION of all the fragment tables. As you can imagine, the fewer of them the better the performance.

Many users of full-text confuse the rebuilding of a regular index and think it is also necessary to "rebuild" the full-text index, but actually the opposite is true.

A rebuild of a full-text catalog causes fragmentation of the index(es) because a rebuild is doing a full population and population creates fragments to speed up population. You should only ever rebuild a full-text index if it is corrupted or you change word breakers, ifilters, stop lists, - essentially anything that would change what is stored in the index.

At the end of a full or incremental population, the master merge process (what reduces fragments) automatically runs. However, there is a limit on the number of simultaneous merge processes that can be run at the same time. This is especially important to understand because if you have a lot of full-text indexes on the SQL Instance or you put several full-text indexes into the same full-text catalog and you either rebuild OR reorganize AND the population of several indexes finish near or at the same time it can exceed the threshold. This can result in the fragments for those merge process that exceed the threshold to go into a wait state and the end result is that the fragments for the affected full-text indexes is not reduced. See full-text population slow

Hope this helps.


How to calculate the final value of a row based on another table with costs in PostgreSQL

If I understood your question right , you'll need something like

select bs.key , coalesce(w.walls,0) + coalesce(f.floor,0) + coalesce(r.roof,0) as final_value
from building_specs bs
left join spec_value w on w.value= bs.wallsId
left join spec_value f on f.value= bs.floorId
left join spec_value r on r.value= bs.roofId

Output matches 701 and 703 from the question , but different for 702
(4.8 +10.1+3.6 != 12.2)


Transaction Log Backup Affecting Applications Performance

Does transaction log backup affect the performance of the application?

The transaction log backup reads from the transaction log file and writes to the backup location.  The application writes to the transaction log file when it changes the database.  If the log file or the log backup location and the database files or tempdb files are on the same disk (or even perhaps on the same SAN controller), then the log backup could impact access to those files too.

Capturing a snapshot of the wait stats during the application slowness should provide more details on what impact the backup is having.

Taking the backups more frequently will spread out the impact.  But you also may need more storage resources (more or faster disks) for this database.  With large databases you need to provide enough resources for both operating and maintaining the database, and maintenance tasks (like log backups, index maintenance, schema modification) can require significant additional resources.


Using mysqldump on RDS to export a large file in TSV format

Assuming RDS with MySQL 5.6 is being used, mydumper may be a better option. The newer versions have the RDS grants limitation into account and you will get all the advantages of mydumper over mysqldump (compression, parallel execution, overall faster speed, etc).

This wouldn't provide TSV data, but by using the --chunk-filesize option you can split the data into multiple files in parallel and then process them in parallel afterwards

option:: --chunk-filesize -F Split tables into chunks of this output file size. This value is in MB


How to fix indexes to reduce the fragmentation percentage

Check out the IndexOptimize job from Ola Hallengren
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
IndexOptimize is the SQL Server Maintenance Solution’s stored procedure for rebuilding and reorganizing indexes and updating statistics.
Example (Able to run once you've ran MaintenanceSolution.sql from Ola Hallengren)
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30


Proxy for MDW data collection on remote servers - SQL Server 2012

Operating system (CmdExec). The reason for this is that the documentation at https://msdn.microsoft.com/en-us/library/bb630341.aspx  states:

  Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration Services packages, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles.

So you need to modify anything which is running the SSIS jobs. In this case if you inspect the jobs themselves you'll see they're run using CmdExec running dcexec which runs the packages. So those are the ones you'd need a proxy on if you're interested in if you have that security issue.


We use Microfocus to emulate the z/os (and DB2) environment on the Windows platform - we have a couple thousand Cobol programs successfully accessing Sql Server daily to run our entire legacy system.  It's not cheap though.


SQL Server 2012 Express with Reporting Services

Following up on my comment, you have to select ENU\x64\SQLEXPRADV_x64_ENU.exe for installation.

Download link.

Then run the installation package and follow instruction until you get feature selection page.

Follow rest of the steps and complete the installation.

Now Verify the RS installation :

Note: You must be logged in as a member of the local administrators group on the computer where SQL Server 2012 Express is installed.

Navigate to Start  -> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> Reporting Services Configuration Manager.
In the RS Configuration Connection dialog, make sure that your local report server instance (for example SQLExpess) is selected and click Connect.

In the RS Configuration Manager left pane, select Web Service
URL.

Make sure that the Virtual Directory parameter is set to ‘ReportServer_’ (‘ReportServer_SQLEXPRESS’ for the SQLEXPRESS instance), and that the TCP Port is set to 80. Click Apply.
In the Reporting Services Configuration Manager left pane, select Database.

On this page, ensure that the SQL Server Name and Database Name fields contain correct values. If not, click the Change Database button and complete the Report Server Database Configuration wizard.
In the Reporting Services Configuration Manager left pane, select Report Manager URL.
On this page, ensure that the Virtual Directory parameter is set correctly, and that the URL is valid.
Click Exit to close the RS Configuration Manager.


Reclaiming unallocated space from tempdb database in SQL Server Express 2012

I have done it using

  DBCC SHRINKFILE ('tempdev',10)

Somehow DBCC SHRINKDATABASE was not working. Even DBCC SHRINKFILE ('tempdev',0) or DBCC SHRINKFILE ('tempdev') doesn't work. We have to assign some figure in MB to which it will shrink the database to.


Join on unnested integer array column and count occurrences in array

Basically, you attached the column alias to the wrong expression in your 2nd query.

SELECT id, concat_ws(' ', t.first_name, t.last_name) AS teacher_name, tm.id_count
FROM  (
   SELECT unnest(tm.teachers_id) AS id, count(*) AS id_count
   FROM   training_modules tm
   GROUP  BY 1
   ) AS tm
JOIN   teachers t USING (id);

Aside:
concat_ws(' ', t.first_name, t.last_name) is typically better, as it only adds the separator where it makes sense (when both first_name & last_name are NOT NULL).


INSERT BULK in SQL Profiler without values

I don't believe there's a Profiler trace event that shows this information.

Insert Bulk Background

Behind the scenes, the client performs a bulk load by sending a TDS SQL Batch message containing an INSERT BULK statement which specifies the destination table and columns as well as any bulk load options requested by the client (e.g. "keep NULLs," "check constraints" or "fire triggers").

However, as you've observed, this INSERT BULK statement doesn't contain the values to insert. Instead, these are transmitted by one or more TDS Bulk Load BCP messages that follow the SQL Batch message. The first Bulk Load BCP message starts with metadata describing the shape of the data set to come. Following this metadata comes the data to insert, encoded in an efficient binary format. (With minor exception, the format used is identical to the binary format the server uses to send results to the client.)

Unfortunately, Profiler doesn't provide an event that displays Bulk Load BCP messages. :-(

Reference

Possibly Helpful: A SO answer which provides clues on using Profiler to get a sense for what a bulk insert is touching. (Thanks to @SqlWorldWide for mentioning this answer in a comment.)


I did an innodb force recovery.
Customer is now saying some of data is wrong.
Is this possible?

Short answer: Yes.
Any form of recovery can result in data loss.
Using innodb force recovery, you are telling the DBMS to get that database back up and running any way it can, no matter what that means to the data [that used to be] inside it.
Presumably, you did the force recovery because you had a problem with the database or the server that it runs on?
Your first thought should have been to restore your database from your backups (which, after all, only exist to allow you to do exactly that).
Trying to get MySQL to try and piece together something that may or may not be on the disk any more is a Fool's Errand.


When to Disable and Recreate Indexes in Data Warehouse ETL?

The answer depends on a number of factors:

What is your goal:  Minimal I/O?  Minimal time to load?  Minimal effort to set up?  24/7 query performance?
How heavy are the indexes: Just one index with on ID column won't make much difference, but several indexes with lots of columns will make a significant difference.
What is the bottleneck?  If the data load is coming across the network, writing to disk may not be the bottleneck if you're writing to a high-performance SSD subsystem.  If you can write the tables and indexes as fast as it comes across the wire, then there's no way you'll make is faster.

So determine what your goal is, and then test each method.  For a 5 GB load, it should be a quick test.

Having said all that, for a 5 GB load I wouldn't bother trying to optimize it.  SQL Server can write that in just a few seconds.  Pick a strategy and run it--if it takes two minutes, then you can only make it about 2 minutes faster--is it worth your time?  If you're approaching this as a learning exercise, do it with a significant amount of data--something that will take 30 minutes.


Is MSMerge_History.time in GMT or local server time?

The MSmerge_history table does have a column named time and it's of the type datetime, it just isn't documented.

To answer your question, based on my tests it appears to be local server time.  The time column has a default value of getdate().


SQL Server 2012 SSIS : retrieve line number in Flat File Connection Manager

I found it!

http://weblogs.sqlteam.com/jamesn/archive/2008/02/13/60509.aspx

It's not a CCFM or a Flat File Source, but it works pretty fine and it's elegant.


passing --single-transaction in mysqldump command line tool

Sure enough, --single-transaction is supported from the MySQL command line.

mysqldump --single-transaction databasename

Will use all of the default options from --opt (except for --lock-tables, which is overridden by --single-transaction) as well as --single-transaction.

That really should be all there is to it.

I recommend briefly reading through all of the options of mysqldump - you may find that some of them, like --routines, are important for your database.


Processing Dimension completed but error occurs

If you take a look at your dimension table and run a query like the following:

Select count(suspect_column) as count_suspect_column, suspect_column
from Dimension_Date
GROUP BY suspect_Column
HAVING COUNT(suspect_column) > 1

It is likely that there is a duplicate value causing the process to fail. These values in that column need to be unique for the process to work. Also worth noting is that SSAS treats nulls as empty strings according to this excellent article:
https://www.sqlservercentral.com/articles/ssas-error-duplicate-attribute-key-found-when-processing


Building new version of a table and replacing the old one by rename, is it a good idea?

You mentioned that partition switching is more complex but that is not the case when the table is not partitioned.  One can use SWITCH to efficiently replace the entire contents of one non-partitioned table with another as a meta-data only operation.  The requirement for SWITCH is that the source and target table must have the same schema, indexes, constraints, and be storage aligned.  These requirements are presumably the same as the rename technique so the effort is similar.

Note that unlike table partitioning with a partition function and scheme, SWITCH of a non-partitioned table can be used in any SQL Server edition.

Below is a sample DDL script for option B:

CREATE TABLE dbo.Device(
      DeviceID int NOT NULL
        CONSTRAINT PK_Device PRIMARY KEY CLUSTERED ON [PRIMARY]
    , DeviceName varchar(30) NOT NULL
    );
CREATE INDEX idx_Device_DeviceName ON dbo.Device(DeviceName) ON [PRIMARY];
GO

CREATE TABLE dbo.Device_new(
      DeviceID int NOT NULL
        CONSTRAINT PK_Device_new PRIMARY KEY CLUSTERED ON [PRIMARY]
    , DeviceName varchar(30) NOT NULL
    );
CREATE INDEX idx_Device_new_DeviceName ON dbo.Device_new(DeviceName) ON [PRIMARY];
GO

CREATE PROCEDURE dbo.RefreshDevice
AS

SET XACT_ABORT ON;

BEGIN TRY

    TRUNCATE TABLE dbo.Device_new;

    --load dbo.Device_new with new data here

    BEGIN TRAN;

    TRUNCATE TABLE dbo.Device;

    ALTER TABLE dbo.Device_new
        SWITCH TO dbo.Device;

    COMMIT;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0 ROLLBACK;

    THROW;

END CATCH;
GO


MySQL - Pass input parameter into Cursor query

Thanks to Akina's comment using CONCAT in the where clause worked.

WHERE key_ LIKE CONCAT('sometext_', _TAG, '_sometext_', _ITEM_NAME)


Try this

$db.Discover() | select @{Name="Type";Expression={$_.GetType().Name}};


Run SQL Server 2016 side-by-side with older versions?

At the risk of speaking directly for @BrentOzar - I don't think he's saying you can't install SQL Server 2016 alongside a prior version.  His blog post1 states:

You probably shouldn’t run 2016 side-by-side with older versions because:
... You may get updates whether you want them or not.

This does not say you will get updates that will bork your box.  Essentially, any Enterprise installation of SQL Server should be carefully managed - you probably shouldn't allow automatic updates of SQL Server at all, and I'd be quite afraid of letting Windows Update install any updates automatically without some kind of pro-active management.
Test your planned deployment on a non-production box to see if the installation of SQL Server 2016 will cause problems for your environment.  Then make an informed decision about how to go about applying updates.  Decide for yourself if you can afford the risk of a side-by-side installation, versus the extra cost of running multiple SQL Server boxes.


In your situation, I would just spin up a PostgreSQL database somewhere and have them connect to it in Microsoft Access via ODBC.

http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html

And I would keep Access and its limitations and tradeoffs in mind while designing my PostgreSQL database.

Advanced features will require you writing pass-through queries to leverage PostgreSQL features directly, but they don't play at all with Access tables, and if you're going to be doing any updates, you're going to need custom code because pass-through queries are read-only.

I would definitely familiarize myself with Access as well:

https://msdn.microsoft.com/en-us/library/Bb221186%28v=office.12%29.aspx

and go through some tutorials:

http://www.gcflearnfree.org/access2013

Actually translating the PostgreSQL database to Microsoft Access would likely be a long, tedious process fraught with pain and peril as has been said, and is not recommended.


Using newly inserted values in trigger function in PostgreSQL

Within the trigger function you can use NEW.column_name to refer to the newly inserted / updated value.  In the reverse, OLD.column_name will refer to the value prior to the update / delete.

More info here: https://www.postgresql.org/docs/current/static/sql-createtrigger.html

Hope that helps.


How can I truly DELETE data from a SQL Server table - still shows up in notepad in backup file?

The problems with your approach:

if DELETE compacted the pages immediately, it will cost more I/O than it is worth. The point of RDBMS is to be fast. It will also not help fillfactors and b-tree balancing (indexes)
not mentioned, but similar - if DROPping columns really removed data immediately, it would require a large amount of I/O to completely restructure the table
DBCC Shrinkfile + NOTRUNCATE is already a brutal operation, it shuffles all used pages from the end of the file towards the front.  To have it move data within pages is even more expensive and would be a very bad idea, since you will end up with FILLFACTOR of 100%, which is very rarely a good ideal at all.
sp_clean_db_free_space which you have found is not risk-free, as Book Online clearly states. It is not something they envision sane DBAs to be running often (if even more frequently than once per lifetime), hence the warning "Before you run sp_clean_db_free_space, we recommend that you create a full database backup.", one of few procedures to carry it
If your database is full-logged, the transaction log contains a copy of the data before you removed it, so you need 2 iterations of backup before the log (contained in the backup) will no longer contain the data removed.


Benefits of using prepared statements in stored procedures?

So yes as you have mentioned about SQL injection the other advantage is what you guessed.
Quoting from documentation:

Using prepared statements with placeholders for parameter values has the following benefits:

Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of
almost-identical statements, with only changes to literal or variable
values in clauses such as WHERE for queries and deletes, SET for
updates, and VALUES for inserts.

Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.

So yes it does cache the prepared statements and routines.


Is there any way to Restore a Database from newer version to an Older Version of SQL Server?

The only way this would be possible would be to restore the Back up to a SQL 2012 instance and then try importing it to a SQL 2008-R2 instance. You may have to create some sort of ETL script to do the importing.


Is it possible to backup a subset of a database in Microsoft SQL Server?

In SQL Server Management Studio (SSMS), you can generate a script that would create the structural components of your database (tables and views, as well as users, functions, and procedures). You could use the script(s) returned on your development DB.

To run this, right-click on your database in SSMS, and choose Tasks => Generate scripts.... This opens a wizard that will step you through the process, giving you the option of what you want to copy over.

For tables, there are advanced options you can set. This allows you to include indexes and triggers, and to include the data in the tables. However, this would give you all the data from the tables, not just a selected amount.

If you want all the data from selected tables, and no data from the rest, then two passes through this will let you generate two scripts.

In the first pass, select everything, script things as "DROP if exists" (assuming you want to replace anything that's already there), do not include data, and save that as a single script.

In the second pass, only select the tables that you want to copy over the data for; do not choose the "DROP if exists" option; and do include the data, saving this as a second single script (or, if you prefer, as one script per table).

NOTE: This creates scripts that you would run on your database, rather than true backups.

Among other things, this does mean that any objects that have been removed from the production database, will not be removed by the script; it just recreates what it knows about. If what you really want is a clean DB to start with, you may want to drop the current dev database and create a new one with the same name. make sure you've captured every object in the first script, though, or things will be missing.
And, if you need to populate some tables with just a part of the production data, then you would either need to manually remove extra rows from the script file, or use a different process to populate the tables.


secure way to connect with remote Mysql database

What are the clients?  PHP?  Java?  (etc, etc)  There are potentially serious hacks that can do things like "SQL Injection" through them.  That is perhaps more common than IP restrictions.

Do you have root only from some secure place?  Does it have a hardened password?

Are all the application 'users' granted access only to the desired database, and with only the desired permissions?  And are all the passwords hardened?

Where are the client passwords?  Visible in code?  That is, if a hacker can get to the code, are the passwords a useless defense?

SSL is only one issue.  If your provider does not allow for SSL, see if someone else does.  If not, then consider hosting the servers yourself; it's cheaper in the long run, but much more work.  And it would solve the IP problem.

Check skip_name_resolve.


Why does selecting an attribute of an Oracle object require an alias?

Because Oracle made it so.
t_alias

t_alias
Specify a correlation name, which is an alias for the table, view,
materialized view, or subquery for evaluating the query. This alias is
required if the select list references any object type attributes or
object type methods. Correlation names are most often used in a
correlated query. Other references to the table, view, or materialized
view throughout the query must refer to this alias.


Add Postgresql server to postgres-common

Plain initdb creates a PostgreSQL cluster in the normal fashion, with all files contained in a single directory. Consequently, it is very easy to get rid of that new cluster: just delete the created directory and all files in it.


Create additional jobs to run backups in parallel.  Make sure to specify a single unique database per job.  Configure the SQL Server Agent jobs to start the backup at approximately same time, with a 1 minute delay between each job.

You may find that running backup jobs simultaneously does not significantly decrease backup time.  You should evaluate the I/O capability of the disks where the databases reside, the disks where the backups are being written to, and in the case of network backups, the capacity of the network to transfer data.


Truncate/delete tables for non-owner schema

You can give DROP ANY TABLE system privilege to A, then he will be able to truncate everything in any schema (not just in B's though) .

If you don't want to do that, a simple stored procedure created by B will do the job (procedure or function in Oracle are by default executed with definer rights) . Then you give need execute permission to A on this procedure.
For instance,

create procedure truncate_my_table(tab_name varchar2)
as
begin
    execute immediate 'TRUNCATE TABLE ' || tab_name;
end;

You may also want to add parameter validation (for example,  I'd do DBMS_ASSERT.ENQUOTE_NAME(DBMS_ASSERT.QUALIFIED_SQL_NAME(tab_name)) ) , error handling and logging to the procedure.


It may help to separate the hierarchy from the task-like objects, and implement them as inherited types. There would be four tables.
TaskTrees
  ParentTaskId   references Tasks.TaskId
  ChildTaskId    references Tasks.TaskId

Tasks
  TaskId
  TaskType      simple or group

GroupTasks
  TaskId   references Tasks.TaskId
  <group specific columns>

SimpleTasks
  TaskId   references Tasks.TaskId
  <other specific columns>

This way the Trees become simple hierarchies of "things" and don't much care what those things are. There are other ways to implement trees than this.
The domain-specific information is held in GroupTasks and SimpleTasks. This is where the values required to implement each is held. There are other ways to implement inheritance. I'm not sufficiently versed in MySQL to recommend one over another.
Integrity between the tables must be maintained through the application. Ensuring a simple task does not have children, for example, is not guaranteed through the data model alone.


Copy database schema from SQL Server 2008 to SQL Server 2005

If you aren't using any SQL2008+ only features (e.g. compression) you will be able to script out the objects you require and execute in SQL2005. If you're not sure if any SQL2008+ features are in use, trial and error (script and execute, see what happens) will soon tell you where the problems are.

This is very different to restoring a backup from 2008 to 2005, which is not possible.


Alternative for taking the backup of SQL DB's on backup drive

Speaking as someone who has taken over from multiple third parties who have done NetBackup database backups you can safely assume three things.

Many of your servers and individual databases have not been backed up. You would do best to confirm yourself through msdb checks.
The "tapes" being backed up to are probably not accessible, do not work, or otherwise don't exist.
You can probably find a lot of NetBackup failure notifications in ErrorLog which have been ignored for days, months, and years.

Regarding your specific situation backups are meant to be taken to network shares and backing up to the same server is wrong. What is going to happen when your server VM crashes or becomes corrupt? You've lost all your backups.

Whatever security / audit issues have come up are obviously wrong and you should be rallying against them. Or - print out your ironclad disclaimer and get your manager to sign it - because they're going to be fired down the line when they lose everything and you don't want this to destroy your career along with their poor decisions.

Best practice? A network share like you mentioned, and standardise on Ola Hallengren's scripts like others mentioned.

Don't be afraid to modify the setup script a little (or add your own to run afterwards) to set up the jobs the way you like them and to schedule them; you can also stagger them server by server if you like to spread the load. I wrote a corresponding script in PowerShell to help do the deployments (create a folder on each server to store the output text files; grant the service account access; create the network folder and grant the service or network account access; then deploy the jobs in the way we want them and with appropriate schedules).

You should also prioritise setting up your registered server list so that in the mean time you can run mass checks of msdb catalogs to make sure you haven't missed anything.

Network and CPU going high doesn't mean anything unless it impacts Production; the only important thing aside from gigabit network interfaces is that the network share is somewhere relatively close to whatever network segments you have. And the initial FULL backups will always be an issue, that's why they're scheduled for weekends, then daily DIFF will be fine.

Otherwise the only databases where this really becomes a problem are when they start going over 1TB (especially if it's much higher, 5TB, 20TB) and those need special attention.


Temp table usage is going to be totally up to your applications which have their databases on the SQL Server, or that are using the SQL Server.  For example the monitoring application itself could be using dozens of temp tables while it gathers up data.

As for the memory usage, SQL Server will by default use up as much memory as it needs to.  Every time is needs to modify data it loads that data into memory and then leaves that data in memory until the SQL Server needs the memory for something else.  In other words high memory usage isn't something that needs to be worried about as it is totally normal.  That said you'll want to cap the amount of memory that the SQL Server has access to so that it doesn't use all the memory on the server.


How to properly debug and inspect SQL Queries in PHP File?

SET GLOBAL long_query_time = 1;
to log slow queries taking 1 second or more.

EXPLAIN EXTENDED SELECT ..... to see valuable information about how the query is getting to the result.

Follow immediately in the same session,
SHOW WARNINGS;
to see how the Optimizer rearranged the sequence of events.
(Rick James, thanks for the reminder.)


Trouble deciding what/how many tables to use; normalisation suggests one

One point you might consider is to store the actual sales value any way. If you store ranges a user must convert from value to range and might make errors. If you create a table storing the ranges, the system can do this task. You will also be able to changes ranges later.

Normalisation also means not storing redundant information. As you can derive the day from a date (with Format) you do not need both. It might also cause inconsistent data.

Last thing you could do is to create a location table. Finding locations without sales is hard if the system does not know which locations exist. It will also help validate the user input.


How to get the sum of filtered fields in SQL

You want to PIVOT the StoreName.  Try something like this:

SELECT * FROM (
  SELECT
    Stock_TBL.StockID,
    Stock_TBL.ItemCode,
    Stock_TBL.ItemDescription,
    InvoiceItems_TBL.StoreName,
    InvoiceItems_TBL.Quantity
  FROM Stock_TBL
  INNER JOIN InvoiceItems_TBL ON Stock_TBL.StockID = InvoiceItems_TBL.StockID
  INNER JOIN Invoice_TBL ON InvoiceItems_TBL.InvoiceNo = Invoice_TBL.InvoiceNo
  GROUP BY Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription, InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity
) t
PIVOT ( SUM(Quantity)
  FOR StoreName IN (
    [E M B],
    [LB],
    [SHOP],
    [WAT],
    [NIH],
    [AJITH]
  )
) p

Note that you will have to specify the list of stores manually.  If you don't want to do this, then research dynamic pivot.


Can I create a user defined table type and use it in the same transaction?

This has been reported no less than four times (but all traces have been removed from the WayBack Machine since Connect was murdered). This one was closed as fixed:

connect.microsoft.com/SQLServer/feedback/details/365876/

But that wasn't true. (Also look at the workarounds section - the workaround I suggested is not always going to be acceptable.)
This one was closed as by design / won't fix:

connect.microsoft.com/SQLServer/feedback/details/581193/

These two are newer and still active:

connect.microsoft.com/SQLServer/feedback/details/800919/ (now closed as Won't Fix)
connect.microsoft.com/SQLServer/feedback/details/804365/ (now closed as By Design)

Until Microsoft can be convinced otherwise, you're going to have to find a workaround - just have all the types deployed before running your test, or break it up into multiple tests.
I will try to get confirmation from my contacts about what Umachandar meant by fixed in the earliest item, because obviously that conflicts with later statements.
UPDATE #1 (of, hopefully, exactly 2)
The original bug (that was closed as fixed) involved alias types, but not of type TABLE. It was reported against SQL Server 2005, which obviously didn't have table types and TVPs. It seems UC reported that the bug with non-table alias types was fixed based on how they handle internal transactions, but it did not cover a similar scenario later introduced with table types. I am still waiting on confirmation of whether that original bug should have ever been closed as fixed; I have suggested that all four be closed as by design. This is partly because it is kind of how I expected it to work, and partly because I get the sense from UC that "fixing" it to work in a different way is extremely complex, could break backward compatibility, and would be helpful in a very limited number of use cases. Nothing against you or your use case, but outside of test scenarios I'm not inclined to believe there is much value in this actually working.
UPDATE #2
I've blogged about this issue:
http://www.sqlperformance.com/2013/11/t-sql-queries/single-tx-deadlock


Solutions for reporting off of an EAV structured database?

Well...my solution is as follows:

I used a dynamic pivot table in a stored procedure. The stored procedure called a View that I created which denormalizes the data. I think used MS Query to hook the stored procedure up to Excel 2010. This loads it up into a nicely formatted table that allows the end user to sort and filter to their heart's content.

With 200,000 records, the query takes about 20-30 seconds to run... I pre-loaded the spreadsheet and set it to refresh in the background at set intervals.

They have it in their hands now, but not long enough to have any feedback. With all the tools I've tried and researched...this really is the only one that has met all my requirements...which is slightly frightening to me. Hope this helps anyone who stumbles across this question.


How to use value return by postgresSQL function in another query

It's not very clear what exactly you mean by a local variable - there is no such thing in SQL.  In this case, just use the function in the place where you would put your variable (like a_horse_with_no_name suggested in his comment), like

SELECT important_thing FROM fancy_table WHERE it_thing_happened > now();

now() is a function, and illustrates the case nicely.

If you are about PL/pgSQL variables, you can do

[...]
my_timestamp := now();  -- not very meaningful IRL
[...]

Note that you can do this only inside a PL/pgSQL function or a DO block.

Last, if you mean a psql (that is, client-side) variable, you can use the \gset command:

test=# SELECT now() AS ts
test-# \gset i
test=# \echo :ts
2017-05-31 10:34:48.148068+02

Note that \gset is available from psql version 9.3 only.


SQL Server Management Studio cannot connect because of common algorithm

One issue that can cause this is if TLS 1.0 is disabled on the client as part of a security hardening process.  Since the version of SQL Server you are trying to connect to doesn't support a version of TLS that is enabled on your system, the connection fails.
If you can't upgrade SQL Server, the only solution is to enable TLS 1.0 on the system where SSMS is running.  This can be done by modifying the appropriate registry settings specified in Transport Layer Security (TLS) registry settings

Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
To enable the TLS 1.0 protocol, create an Enabled entry in either the Client or Server subkey as described in the following table. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 1.


Modify TNSNAMES.ORA file to connect to an Oracle Database

You may start command prompt using windows commanline cmd and use below:

tnsping any_random_name

as you can see in the image, it uses parameter file where sqlnet.ora exists. This is the folder where your current oracle installation points to. You should make change in that folder's tnsnames.ora.


How do I Configure Per Database Roles for PostgreSQL?

There is no direct way. Roles are stored in the cluster-wide system table pg_authid And, quoting the manual:

  Because user identities are cluster-wide, pg_authid is shared across all databases of a cluster: there is only one copy of pg_authid per cluster, not one per database.

Bold emphasis mine.

There are also no per database settings in that table or related system tables. So there are no "per database roles".

There are ways to restrict access for each role to a single database. Most effectively in the pg_hba.conf file. But you still can only use each role name once in the whole DB cluster.

I obviously missed the db_user_namespace setting @jjanes remembered. It's the answer to the question, but I wouldn't use it.


SSMS 2016 Maintenance Plan Designer Perms

In your maintenance plan's "Manage Connections" define a new connection with integrated Windows authentication. Use that connection with your Notify Operator Task (set it here right-clik->Properties->Connection) and it should work.


Is the inverse of Armstrong's Axioms valid as well?

No, that is not true.

Armstrong's axioms are a sound and complete  axiomatization of the logical implication for functional dependencies.

Here is a relation, where the functional dependency XZ → YZ holds:

X  Y  Z
--------
x1 y1 z1
x2 y1 z1
x1 y2 z2
x2 y2 z2

But the functional dependency X → Y does not hold, as the tuples x1 y1 z1 and x1 y2 z2 show. Because of the soundness of Armstrong's axioms, which means that only valid functional dependencies can be derived, the "inverse" is not true. So we have constructed a counterexample.

If this "inverse" law would hold, then we can show that X → Y for arbitrary attribute sets X and Y:

Y → Y      (reflexivity)
XY → XY    (augmentation)
XY → Y     (reflexivity)
XY → YY    (idempodency of union-operator: Y=YY)
X → Y      ("inverse")


SA user password for software which automatically installs SQL Server 2005

With SQL Server 2005, you have at least a couple of options

run sqlprov.exe as local admin which allows you to add domain
accounts to the sysadmin role
stop SQL Server and start in single user mode with the parameter -m
and use SQLCMD or OSQL to add your domain account to the sysadmin
role (EXEC sp_addsrvrolemember 'MyDatabase\MyNTUserName',
'sysadmin';). You can also create a new SQL Server login and add
it to the sysadmin role from the same SQLCMD prompt.


Pivot add-on in cross-tab table Postgresql

caveats below

With tablefunc

First you need to add the tablefunc extension

CREATE EXTENSION tablefunc;

What you need is something like this,

SELECT *
FROM crosstab(
  $$
    SELECT 0, coalesce(continent, 'Total'), count(city.name)
    FROM encompasses AS e
    JOIN country ON e.country = country.code
    JOIN city ON country.code = city.country
    GROUP BY GROUPING SETS ((e.continent), ())
    ORDER BY 1, 2;
  $$,
  $$VALUES ('Africa'), ('America'), ('Asia'), ('Australia/Oceania'), ('Europe'), ('Total')$$
) AS t(rowid int, africa int, america int, asia int, australia_oceania int, europe int, total int);

Should get ya done.

Of course none of this works with the sample data because none of those countries have entires in encompasses,

INSERT INTO encompasses (country, continent) VALUES ('GR', 'Africa');

 rowid | africa | america | asia | australia_oceania | europe | total
-------+--------+---------+------+-------------------+--------+-------
     0 |      4 |         |      |                   |        |     4
(1 row)

You may want to replace

$$VALUES ('Africa'), ('America'), ('Asia'), ('Australia/Oceania'), ('Europe')$$

with

$$ SELECT DISTINCT continent FROM encompasses ORDER BY 1; $$

But either way, you'll have to type that out in the table definition (the list in the t(rowid int...))

Caveats

I don't actually do this in my code, ever. This pivot is a waste of time and computation. I would always do this in whatever language I'm using to interact with the db. I don't actually think this is a good idea (here). But this is how it's done.


Sybase 12 to behave like Oracle and SQL Server 2014 - non blocking transaction read

I finally found the options of the database in Sybase Central and had a play with various options and I got it to work with turning ON "allow_snapshot_isolation" it was OFF by default.


Unexpected results from MariaDB IF EXISTS ... THEN

SELECT COUNT(*) always returns at least one record (maybe with zero). So IF EXISTS is always true.

You may:

Replace SELECT COUNT(*) ... with SELECT 1 ...
Replace IF EXISTS(SELECT ... with IF 0 < (SELECT ...

I think that 1st variant is more fast. It only checks does at least one record exists, and so it must stop searching after first record found, whereas 2nd query must obtain all matched records, count them, and then compare the result with zero.


Selecting Values That Differ From Section Average

You're on your way and understand CTEs and Window Functions so here's an option:

With AvgSize As
(
SELECT
     AVG(Data_Wanted) As AveragePartitionSize
    ,[partition_id]
FROM Tble
GROUP BY [partition_id]
)

,LatestValue As
(
SELECT
      surrogate_id
     ,collection_ts
    ,ROW_NUMBER() OVER(PARTITION BY surrogate_id ORDER BY collection_ts DESC) RowNumber
FROM example_table
)

SELECT
    *
FROM BaseTable BT
JOIN AvgSize
    ON BT.[partition_id] = AvgSize.[partition_id]
JOIN LatestValue LV
    ON BT.surrogate_id = LV.surrogate_id
    AND BT.collection_ts = LV.collection_ts
    AND LV.RowNumber = 1
WHERE
    BT.Data_Wanted > AveragePartitionSize

CTE AvgSize establishes the average for the partition ID.

CTE LatestValue is your provided CTE restricted to necessary columns to establish the latest row.

The final step is to bring them all together. The CTEs restrict the query to the latest row and provide the partition average. The only thing left to do is compare the latest row partition size to the average partition size and you're left with your result set.

You could get a little more advanced with your variance check with something like:

ABS(AveragePartitionSize - BT.Data_Wanted) > 20


Why does "CREATE TABLE IF NOT EXISTS" lock a table?

The locks in mysql are un-necessarily restrictive before it even checks if the table already exists.  The bug report, that seems to be being ignored by mysql but has been fixed on a fork of mysql, explains it pretty well:

  If one session had any metadata lock on a table, another session
  attempting CREATE TABLE [IF NOT EXISTS] for the same table would
  hang. This occurred due to an attempt in the second session to
  acquire an exclusive metadata lock on the table before checking
  whether the table already existed. An exclusive metadata lock is not
  compatible with any other metadata locks, so the session hung for the
  lock timeout period if another session had the table locked.

https://bugs.mysql.com/bug.php?id=63144

The fork that fixed it by using the (rather obvious) solution of doing a shared lock to see if it exists, then if it did not exist upgrading to an exclusive lock.