Is it a good or bad idea to use "ON UPDATE CASCADE ON DELETE CASCADE" for foreign keys? Why does this mechanism exist at all?

I'm not sure about ON UPDATE CASCADE. If you find yourself needing this sort of cascaded update then that is perhaps a "code smell" in your database design. In theory your primary key should be static so changes that need cascading shouldn't need to happen. Perhaps it was added as a logical step along from ON DELETE CASCADE. It is at least safer than cascading deletes.
The existence of ON DELETE CASCADE makes more sense: while PKs shouldn't really change things do often get deleted. The cascading is simply a convenience, it saves you from having to write code to drop child entities manually when getting rid of a parent. Further more it might be considered safer than implementing this in other logic because the database is taking care of transactional consistency, deadlocks, and so forth, so the operation should (bugs permitting) be guaranteed atomic. If you implement your own "find children, delete, then delete parent" which may have to be nested, you have to do some legwork[!] to ensure that if there is an error part way through there is no way that you delete the great-great-great-grand-children of a row but leave the rest standing (leaving a partly deleted entity which could cause difficult to diagnose issues later).
[!] Taking appropriate locks, preferably not by locking whole tables, ensuring transaction isolation settings are right, ... - it isn't as simple as it might first look.
Why Not Cascade?
As I said above, I consider a need to cascade updates routinely to be a bit of a design smell. You shouldn't need to change a primary key value during normal operations.
I'm very wary of cascaded deletes, despite the danger of bugs in more manually deleting complex structured entities. Too often you see inexperienced people perform UPSERT operations[*] using a DELETE-then-re-INSERT method, even when the DB supports single-statement upsert operations[^], which damages your data if cascaded deletes are enabled: the delete removes the children too, and they don't get put back by the subsequent insert.
Also, in a lot of cases with real data you don't actually want a cascaded delete. For example: if a manager leaves a company you don't want to delete their sub-ordinates because assigning a new manager first was forgotten, or prevented by a bug.
[*] in postgres via INSERT ... ON CONFLICT ... but this is not standard and quite different syntax[†] is used elsewhere
[^] either because they aren't aware of the available syntax, or are avoiding it in order to be cross-DB compatible
[†] MERGE can be used in Microsoft's TSQL for the same effect, mySQL[‡] supports INSERT ... ON DUPLICATE KEY ...
[‡] mySQL also supports REPLACE INTO, but IIRC that is just syntactic sugar for delete+insert so has the same dangers


You are right in thinking that items of a list should be stored as individual values of a column, i.e. in separate rows of that column, rather than as a single value (CSV string or anything like that) – at least if you expect to have queries against individual items of the list. Relational databases are designed to work that way, and storing multiple values of a column as a list (single value) is a no-no in such cases.

Regarding your data sample of fruits and vegetables, I am assuming that you are not thinking of splitting two lists of values exactly like that, storing each list's elements alongside each other on the same row without there actually being a relationship between them, just for the sake of normalisation of the way the vegetables and fruits are stored. That would be wrong.

I mean, if currently you have a row with a list of fruits and a list of vegetables such that each fruit and each vegetable are related to this particular row, i.e. like this:

FoodsID  Fruits                      Vegetables     other  columns
-------  --------------------------  -------------  -----  -------
100      Apple,Avocado,Mango,Orange  Corn,Tomatoes  ...    ...

and you want to expand each list into a row set while keeping the relationship of each fruit and each vegetable to the Foods row, you do not just add more rows and put the first item of one list together with the first item of the other list on the same row, then the second item of each list on another row and so on, like this:

FoodsID  Fruits   Vegetables  ...
-------  -------  ----------  -----
100      Apple    Corn        ...
100      Avocado  Tomatoes    ...
100      Mango                ...
100      Orange               ...

Again, that would be wrong. Fruit Apple may well be related to food item 100 as may vegetable Corn, but if these two items are not related to each other, there is no reason to store them on the same row.

Commonly, when you want to store a relationship between a subset and an item, you use a separate table. As you have two subsets of different kinds that are related to the same item, you just use two tables. So it would be:

table Foods:

FoodsID  other  columns
-------  -----  -------
100      ...    ...

table Fruits:

FoodsID  Fruit
-------  -------
100      Apple
100      Avocado
100      Mango
100      Orange

table Vegetables:

FoodsID  Vegetable
-------  ---------
100      Corn
100      Tomatoes

where FoodsID in table Foods would be the primary key of that table and same-named column in tables Fruits and Vegetable would serve as a reference (foreign key) to the corresponding row in Foods.

This way, when querying against individual items of either list as belonging to that particular row in Foods, you would probably use a join. Your typical filter conditions would be as simple as Vegetable = 'some vegetable' or Fruit IN ('some fruit', 'some other fruit') – not FindPos(Vegetable, 'some vegetable') > 0 or anything of the kind. The conditions would be simpler because each column would now contain one value per row. That will lend well to further optimisation of performance, because when you query often against a column value (without applying a function to it first, that is), you can make such queries faster by adding an index on the column – something that would be pointless for lists stored as a single value.

More can be said on this topic, which would, however, be beyond the scope of your question. I suggest you look up the terms I highlighted in bold for more information.


How can I position the element generated by XMLTEXT in a FOR XML query?

After some more fiddling, I found out that there is an XML directive which does the job (if I omit the attribute name, test):

SELECT
  1    AS Tag,
  NULL AS Parent,
  id   AS [entry!1!id!ELEMENT],
  '<test type="dummy" />'
       AS [entry!1!!XML]
  FROM #temp
  FOR XML EXPLICIT;

This produces the results I want:

<entry><id>1</id><test type="dummy" /></entry>

(demo)

This directive is mentioned briefly here but I couldn't find more documentation or any examples.


Behaviour of RAISERROR within a TRY/CATCH within a loop

RAISERROR does not typically stop execution.  THROW does, but that was introduced in SQL 2012.

Put a RETURN; or RETURN -1; after the RAISERROR.

The following is taken from the "Remarks" section of the MSDN page for RAISERROR:

  When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:

  Outside the scope of any TRY block.
  With a severity of 10 or lower in a TRY block.
  With a severity of 20 or higher that terminates the database connection.

Below is a simple test of the behavior. If running on a version of SQL Server prior to 2012, comment out or remove the line with THROW.

RAISERROR(N'This is from RAISERROR - Severity 16', 16, 1);
PRINT N'----- 1';

;THROW 50505, N'This is from THROW', 1;
PRINT N'----- 2';

GO

PRINT N'----- 3';

-- WITH LOG needed for severity > 18
RAISERROR(N'This is from RAISERROR - Severity 20', 20, 1) WITH LOG;
PRINT N'----- 4';

GO

PRINT N'----- 5';

The above test returns the following in the "Messages" tab:

  Msg 50000, Level 16, State 1, Line 2
  This is from RAISERROR - Severity 16
  ----- 1
  Msg 50505, Level 16, State 1, Line 5
  This is from THROW
  ----- 3
  Msg 2745, Level 16, State 2, Line 4
  Process ID 54 has raised user error 50000, severity 20. SQL Server is terminating this process.
  Msg 50000, Level 20, State 1, Line 4
  This is from RAISERROR - Severity 20
  Msg 0, Level 20, State 0, Line 0
  A severe error occurred on the current command.  The results, if any, should be discarded.

And in the bottom, left corner of SSMS it will indicate "Disconnected".


Attribute MAX_RUN_DURATION does not terminate the job, it only raises an event.

I created a procedure like this:

procedure SchedulerWatchdog(jobName in varchar2) is

cursor Jobs is
select job_name
from user_scheduler_jobs
where JOB_NAME = jobName
   AND STATE = 'RUNNING'
   AND systimestamp - LAST_START_DATE > MAX_RUN_DURATION;

begin
   for aJob in Jobs loop
      DBMS_SCHEDULER.STOP_JOB(JobName, FORCE => TRUE);
   end loop;
end;

Run this procedure by scheduler job, perhaps every Minute.


Table variable not Recognized in stored procedure

There are quite a few problems with this code:

Consecutive calls of select ... into result do no accumulate rows into result, each one overwrites whatever was previously in result.
A variable typed after a table (result of type result_load_status here) is meant to contain only one row of this type, not an entire resultset. For an entire resultset, use CREATE TEMPORARY TABLE or a cursor if possible.
select * from name_of_a_variable does not exist as a valid construct. A temporary table instead of a variable's name would work.

Anyway since accumulating results is already built in RETURN QUERY, you don't need any of the above.

Quoting from the documentation:

  RETURN QUERY appends the results of executing a query to the
  function's result set

So you may just write:

RETURN QUERY execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || b;

RETURN QUERY execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || c;

without any need for the problematic result variable.


SQL Server - convert complicated view to table - the best way

I think the main problem here is that you're trying to use a single view to feed many different reports, thus complicating the view definition over and over.

Not all the reports will need all the columns, the calculations and the JOINs in that view, so, in many situations, you will be hitting tables and performing calculations for no reason.

A more sensible approach would be to have a separate, optimized, query for each report and have the bare minimum tables involved. I know that this sounds like a lot of work, but code reuse is really difficult to achieve in T-SQL without giving up performance.

That said, if all your reports are based on more or less the same data, an indexed view or a materialized intermediate table could be a viable solution. Indexed views have some limitations that make them impractical in some contexts (no self joins, no outer joins, no UNIONs etc...) and may introduce an unacceptable write overhead, but are really powerful when used correctly.


Product of two querysets with a parameter in where clause of second result

Here is the query that can be used to get desired result

select ta.id,ta.related_a_id,tc.c_field1 as cf1,sum(td.c_field3) as cf3
from tableA ta
inner join tablec tc on tc.a_id in (ta.id,ta.related_a_id)
inner join tabled td on td.id = tc.d_id
group by cf1, ta.id order by ta.id;


Data type for currency 'type' column, e.g. usd, cat, etc.

3 letter ISO currency codes are relatively small, so there is little overhead in using them as the key for the reference table.  Having certain items such as dates, GL codes and other analysis codes and currency codes directly on the tables is quite convenient for people reporting off the transactions and balances.  Many accounting packages - even big players like Oracle Financials - do it for this reason.


PostgreSQL: hstore trgm search on fixed key

With only 10-20 distinct keys, I would urgently consider one separate column per key in the same row. Unused keys stay NULL. Related case:

JSONB vs BRIN Index

That said, there is a way to get the list of distinct key names from a document type column like jsonb or hstore comparatively quickly:

Create a btree expression index on the sorted array of key names (or a concatenated string), traverse it with a recursive CTE to emulate a loose index scan and condense the distinct set of keys from the distinct set of combinations of keys.

That will read 1000 index tuples for 1000 combinations (ideally in an index-only scan) instead of all 10kk of rows. A difference of factor 10k.

You need an IMMUTABLE function extracting the array or list of keys for the index - like akeys() for hstore.

A btree index on an array is rarely useful. This is the rare case.

Related code examples:

GROUP BY one column, while sorting by another in PostgreSQL


One reason for normalization is to smartly decouple a table into multiple tables of related columns so only the needed set of columns are queried at a given time to maximize performance. If the CompanyId is a standard in most of your queries, then from a performance perspective it's not a bad idea to keep it in all relevant tables so you don't have to join multiple unrelated tables to the query, to get the Customer. But from a maintenance perspective it'll be more work to insert the same value into multiple tables as well. So it's a trade-off between performance and maintainability based on the number of tables your system will have.


Redundant transpose. Case is not enough to solve my problem

Telling us that you are using a CASE statement but not showing us the actual code that you're using doesn't help us much.  Assuming that "Result Table" is the desired output and that you want to use the old-style MAX(CASE syntax to do your pivot rather than using the newer PIVOT operator

SELECT emp_id,
       emp_name,
       nvl( max( case when sal_paid = 'JAN' then'Y' else NULL end ), 'N' ) jan,
       nvl( max( case when sal_paid = 'FEB' then'Y' else NULL end ), 'N' ) feb,
       nvl( max( case when sal_paid = 'MAR' then'Y' else NULL end ), 'N' ) mar,
       nvl( max( case when sal_paid = 'APR' then'Y' else NULL end ), 'N' ) apr
  FROM your_table
 GROUP BY emp_id, emp_name


Adding a foreign key constraint without losing the current data

Why would you lose the rows in the users table when you add a column to it? The rows just get initialized with a null value.  After you place the foreign key constraint, future rows with that column set to non-null get checked against the table the column references.

There are three ways for you to lose rows.

DELETE
TRUNCATE
DROP TABLE


Should domain "Users" group be removed from the SQL logins for database security?

Don't remove any SQL Login's until you are sure they are no longer required.

Now you might be mixing two definitions.

SQL Server databases contain database users
SQL Server instances contain SQL Logins

SQL Server Logins

A SQL Server login created on a SQL Server instance can either be a Windows Authenticated User or Group, or it could be a simple SQL Server account/login.
A SQL Server login will have its own password and rules.
A Windows Authenticated user or group created as a SQL Server login will be linked to the corresponding Windows Account and does not contain a password. It validates either against the Domain or against the Server the user/group belongs to.
A SQL Server login has permissions at SQL Server instance level.

Database Users

A database user is limited to permissions inside the database. (There are exceptions).
You can assign a database user DML or DDL permissions and grant EXECUTE permission on stored procedures.
You can assign a database user to database roles
You can assign a database user to a schema (default: dbo)

Linking database users and SQL Server logins

There can be a link between database users and SQL logins, but there doesn't have to be.

Let's give you a few examples

You could have the SQL Server login DOMAIN\Users with VIEW SERVER STATE permissions for the instance, but otherwise not linked to a database via database user. All domain users can then query the state of the SQL Server.
You could have the SQL Server login DOMAIN\Users linked to a database user TelephoneBook_Reader in the database TelephoneBook. The database user TelephoneBook_Reader might have SELECT permissions on the table Employees in the database. If you remove the DOMAIN\Users from the SQL Server instance, then nobody will be able to query the telephone book.
You could have the SQL Server login DOMAIN\Users linked to a database user  DOMAIN\Users in the database PsuedoDB. The Database user DOMAIN\Users might have permissions to select, insert, update and delete for the schema Inventory.
....

Answering your Question

No, just because a Domain group is assigned to a database via a SQL Server login linked to a Database user, does not have to be a security risk. It might be the easiest way to grant a lot of users access to a database.

Yes, if a Domain group has been granted permissions to access a database (via SQL Login and Database user) and to query information it should not be allowed to, then this can be a security risk.

As with so many things in SQL Server: It depends.

Check the database permissions assigned to the SQL Server login DOMAIN\Users and verify that these permissions are required.

From a hacker's standpoint: Every permission granted can be pose a security risk.


Decimal or Point Data Type for storing Geo location data in MySQL

You could use MySQL's spatial extensions with GIS.

In a code example from Google displaying points on a map, they state:

  When you create the MySQL table, you want to pay particular attention
  to the lat and lng attributes. With the current zoom capabilities of
  Google Maps, you should only need 6 digits of precision after the
  decimal.

  To keep the storage space required for our table at a minimum, you can
  specify that the lat and lng attributes are floats of size (10,6).
  That will let the fields store 6 digits after the decimal, plus up to
  4 digits before the decimal, e.g. -123.456789 degrees I wouldn't worry
  about performance differences between numeric types. Decent indices
  will have a far greater effect.

You could also try DECIMAL as @gandaliter suggest.

  DECIMAL is the MySQL data-type for
  exact arithmetic. Unlike FLOAT its precision is fixed for any size of
  number, so by using it instead of FLOAT you might avoid precision
  errors when doing some calculations. If you were just storing and
  retrieving the numbers without calculation then in practice FLOAT
  would be safe, although there's no harm in using DECIMAL. With
  calculations FLOAT is still mostly ok, but to be absolutely sure of
  8d.p. precision you should use DECIMAL.

  Latitudes range from -90 to +90 (degrees), so DECIMAL(10,8) is ok for
  that, but longitudes range from -180 to +180 (degrees) so you need
  DECIMAL(11,8). The first number is the total number of digits stored,
  and the second is the number after the decimal point.
  In short: lat DECIMAL(10,8) NOT NULL, lng DECIMAL(11,8) NOT NULL
  This explains how MySQL works with floating-point data-types.

Related information:

MySQL and GIS, GeoIP: FAQs, How-To, Articles, Blogs
Creating a Store Locator with PHP, MySQL & Google Maps
Using the new spatial functions in MySQL 5.6 for geo-enabled applications


As at MongoDB 3.4, the $maxTimeMS value is a cursor option that needs to be set by the client/driver making the request.

There is currently no equivalent server configuration directive, but there is a relevant feature request to watch/upvote in the MongoDB issue tracker: SERVER-13775:
maxTimeMS on an instance/database level.

There are some potential caveats to consider for a server-level default $maxTimeMS:

This would be a breaking behaviour change for use cases that have intentional long-running query execution times. For example, backup tools such as mongodump would have to be updated to explicitly set a no-MaxTimeMS option or handle unexpected early termination.
$maxTimeMS applies to cumulative time for query execution (including multiple batches of results for the same cursor) so this option may have an unexpected outcome for queries returning a larger number/size of results.

Generally your application will have more context on which queries can be terminated, so I suspect a closer fit would be adding the ability to set a default $maxTimeMS per authenticated user/role. The closest related feature suggestion is currently SERVER-15072:
Limit resource usage for certain users.


Confusion about charset for connected tables from MS Access

I'm going to guess that the problem might be the difference between your table collation and the data_charset. Maybe try using a compatible collation, so how about this? Use CHARACTER SET cp1250, COLLATE cp1250_general_ci:

CREATE TABLE pohoda.xfa (
  ID INT(10) NOT NULL,
  ... list of fields
)
ENGINE = CONNECT,
TABLE_TYPE = ODBC,
TABNAME = 'Fa',
BLOCK_SIZE = 10,
CHARACTER SET cp1250,
COLLATE cp1250_general_ci,
CONNECTION = 'DSN=pohoda_64b;DBQ=c:/path/to/mdb;';


calculate and return the ratings using sql

You just need an aggregative query using GROUP BY like so:
SELECT
    service_uuid,
    ((5 * SUM(five_rating)) + (4 * SUM(four_rating)) + (3 * SUM(three_rating)) + (2 * SUM(two_rating)) + SUM(one_rating))
    /
    (SUM(five_rating) + SUM(four_rating) +  SUM(three_rating)  + SUM(two_rating) + SUM(one_rating))
FROM RatingsTable
GROUP BY service_uuid

Also just keep in mind that your columns will return integer results and therefore the / symbol will do integer division which means it'll round down to the nearest 0 (e.g. 5 / 2 = 2). If you want a weighted rating that includes fractional decimal points then you just need to cast either the divisor or dividend to float or decimal. You can find more information about this in this StackOverflow post's accepted answer.


How to script permissions for all logins/users/tables/schemas

I have a couple of stored procedures that I wrote and use on a regular basis.

sp_SrvPermissions
sp_DBPermissions

They each have three outputs.

Principals : A list of the database/server principals and associated info including create/drop scripts.
Role membership : A list of what roles each database/server principal belongs to along with add/drop scripts.
Permissions : A list of individual permissions (EXECUTE, INSERT, UPDATE, CONNECT etc) for each database/server principal.

Obviously the Srv sp is for the Server principals and the DB sp is for the database level principals.  The DB sp also has an 'All' option for the database name.  With this option it runs through and pulls all permissions for all databases.

Note: This was originally intended as a research tool so there are a fair number of options that will let you parse down your data.  Just roles, everything but roles, just a specific principal name etc.


DB Design storing products sold in bulk, priced by vastly different units

There are two ways to do this. The fast, and the "academic".

In pseudocode (change to fit your database)

The fast/high performance way:

CREATE TABLE Products (
  ID INT
  , Name VARCHAR
  , PricePerKg DECIMAL NULL
  , PricePerMeter DECIMAL NULL
  ... etc...
)

While this is not pure 5NF, it is FAST because all lookups of products can be done with a single index seek. You should add a constraint that ensures that at least one of the price/unit is NOT NULL (easy with COALESCE or NVP). It is very easy to write code that will access the above.

The academic way

CREATE TABLE Product (
  ID INT
  , Name VARCHAR
)

CREATE TABLE ProductPrice (
  ID_Product INT NOT NULL FOREIGN KEY
  , ID_Unit INT NOT NULL FOREIGN KEY
  , PricePerUnit DECIMAL NOT NULL
)
PRIMARY KEY (ID_Product, ID_Unit)

CREATE TABLE Unit (
  ID_Unit NOT NULL PRIMARY KEY
  , Name VARCHAR
)

The latter requires up to 2 joins to grab the information. But it is of course more flexible. To make sure the last solution does not create bad quality data, you will want a trigger to enforce that when the last ProductPrice is deleted, the product goes with it (or you set a flag).

If the number of prices per unit is known to be small, there is really no reason to pick the latter solution. There are no awards for being academically correct.


Error connecting to the server:FATAL:couldnot open file"base\12135\11946":No such file or directory

A file in the base directory, where postgres stores it's data, is missing. This can be due to a corrupted filesystem or some program deleted the file.

At this point the first step you should do is to try and do a pg_dumpall. It will likely fail at the point where it encounters that missing file and then you will know what was impacted. Additionally, if you have other DBs besides the one impacted suggest you try to also back them up individually to make sure those other DBs don't have an issue.

After you identify what table(s) were impacted you then need to do one of these:

Restore the missing data from backups if you have them
If you don't have backups about the best you can do is to try and backup the individual object and see if you can get at least partially some of the data. The rest is likely lost.
After you have either deleted and restored, or just outright deleted, the table then do the backup of the impacted DB again to confirm if any other objects are also missing files.
Audit access to the server. Make sure nobody that should not have access has access.
Ask the people who have access to the server if they were doing any cleanups recently and they were deleting some files. Perhaps someone deleted the wrong file(s) by mistake and did not notice it.


SELECT INTO that won't stop on SQL Server

Well, what is the last wait type ?

If you cancelled the query, it has to rollback, so the cancel is not immediate.
For instance you want to insert into billions of rows in one statement (not sure it is a good idea, but let's imagine). It takes several hours to do it (and your disks are probably burning) and if you cancel it, it may also take hours to rollback everything that has been done.

Also, I often experienced similar issue when using a linked server to Oracle. Because the network was a bit sensitive and Oracle config a bit strict, long query using this linked server (more than 1 hour) were stuck : process wasn't there anymore on Oracle side, but still on SQLServer side, and this one was impossible to kill and was locking destination table. Only solution : restarting SQLServer service.

But that does not explain why SSMS is freezing...


XtraDB replaces InnoDB (it's compatible and a drop in replacement), so just add ENGINE = InnoDB

However, it should be the default in MySQL 5.5.x onwards


Try to find a soft that simulates replication or write a module for mssql yourself like adviced here https://stackoverflow.com/questions/14874587/replicate-microsoft-sql-to-other-databases


How to add days to a date in PostgreSQL?

I found the following solution

UPDATE table1 set table1.date = (table2.date + (table2.numberOfDays || 'day')::INTERVAL)
FROM tabel2
WHERE table1.id = table2.table1_id

OR

UPDATE table1 set table1.date = table2.date + make_interval(days => table2.numberOfDays)
FROM tabel2
WHERE table1.id = table2.table1_id


How to select a row based on the maximum value of a column, when ties are expected

select subscriber, date_column, time_column, value from (
  select
    subscriber, date_column, time_column, value,
    rank() over (partition by subscriber order by value desc, time_column) as r
  from mytab
) where r = 1
and subscriber = 'ccc';


How can I setup and use a PostgreSQL database without installing a driver?

postgres can be "installed" from a ZIP file by simply extracting it.

psql is part of the Postgres "installation" can be used without any further steps.

Any JDBC based query tool can also be used without "installing" a driver, as you can put the JDBC driver's .jar file anywhere you want.

It seems you can download the ODBC driver binaries directly without an installer. But I don't know if it's possible to properly register that with any tool that needs it.


How to update MySQL with CentOS 6 in most unintrusive way - in regard to perl and PHP packages?

For Official MySQL packages: Oracle yum repository for MySQL.
For MariaDB Packages: MariaDB yum repository.

After that, you will be able to update MySQL via yum up to the latest stable version. They only include the server, client and the minimal MySQL libraries.

For details, I've recently written a step-by-step guide on How to install MySQL 5.6 on CentoOS 7 (most of it applies to CentOS 6, except for the initial rpm version and the start commands). It is a very common task because Red Hat/CentOS has very ancient versions of MySQL (and PostgreSQL).


I certainly can't explain why any Windows patching would remove executables from your Binn folder; that sounds more like the work of "security" software or manual intervention.

In any case, there are a couple of things you can try:

run setup and try a repair against the SQL Server installation. Maybe start with just the shared components.
copy the missing files from the Binn folder of another installation that works and is the exact same edition and @@VERSION. If you don't have one of these readily available, you could probably install Evaluation Edition on your workstation or a VM. But really you should have a mirrored installation somewhere that matches production so that you can test Windows patching and any other updates there first before you "try it out" in production.


Need help to compare pricing using SQL queries

Disclaimer, everything is nullable in your tables so IS NULL predicates may give false positives.

Your query comes a long way, all you need is to add certain predicates:

1 and 2.

SELECT Table1.Brand ,
       Table1.Origin,
       Table1.Destination,
       Table1.Price,
       Table2.Price,
       Table1.Price - Table2.Price as diff
FROM Table1
  LEFT JOIN Table2
         ON Table1.Brand = Table2.Brand
        AND Table1.Origin = Table2.Origin
        AND Table1.Destination = Table2.Destination
WHERE Table1.Price - Table2.Price <> 0;

3 .

SELECT Table1.Brand ,
       Table1.Origin,
       Table1.Destination,
       Table1.Price,
       Table2.Price,
       Table1.Price - Table2.Price as diff
FROM Table1
  LEFT JOIN Table2
         ON Table1.Brand = Table2.Brand
        AND Table1.Origin = Table2.Origin
        AND Table1.Destination = Table2.Destination
WHERE Table2.Price IS NULL;


What is the benefit of marking function as IMMUTABLE?

The PostgreSQL documentation 37.7. Function Volatility Categories states the following on using the IMMUTABLE keyword with functions:

An IMMUTABLE function cannot modify the database and is guaranteed to
return the same results given the same arguments forever. This
category allows the optimizer to pre-evaluate the function when a
query calls it with constant arguments. For example, a query like
SELECT ... WHERE x = 2 + 2 can be simplified on sight to SELECT ...
WHERE x = 4, because the function underlying the integer addition
operator is marked IMMUTABLE.
For best optimization results, you should label your functions with
the strictest volatility category that is valid for them.

In short, the stricter volatility keyword you can use in your function definitions, the more optimized they'll perform in your queries. I suspect this is especially true when using the IMMUTABLE keyword because of it guaranteeing the function is determinsitic as stated here:

...guaranteed to return the same results given the same arguments
forever.


What does "WSREP Aborter Idle" mean in a MariaDB Galera cluster?

After extensive searching the only reference I can find is a bug report filed with Codership. Within that report the following quote makes reference to the process and seems to point to this being both required and part of the normal operation of the Galera cluster.

  wsrep aborter thread is missing in the process list in node carthusian. Checking by gdb, shows that aborter thread is alive and working well.
  Also whole cluster is healthy and works good under sqlgen test load

If anyone has further details about this I'd be interested to learn more!


How to remove sysadmin from sa login in sql server

You cannot remove sysadmin from this login. The login with sid 0x01 (generally named "sa") is always sysadmin.
I assume that you have disabled sa in the first place, btw.
How about renaming sa to some other name?


It looks like it should work because the general_log_file is dynamic (according to the MySQL Documentation).

Some six(6) years ago, Joel Hanger answered How do I output MySQL logs to syslog?. In his answer, he added it to the my.cnf. Evidently, a mysqld restart would be needed.

In your particular case, you may have to enable the general log first using

SET GLOBAL general_log = 1;

to initiate a general log with the default name of hostname.log.

This will create an open file handle. You may then have to try setting the general_log_file with general_log on and off. You may also have to run FLUSH LOGS; to force the closing and reopening of log file handles.

This answer is just a lazy guess, so try it and see.


Typically:

Server Configuration Checklist for Oracle Database Installation

Swap space allocation relative to RAM (Oracle Database)

Between 1 GB and 2 GB: 1.5 times the size of the RAM
Between 2 GB and 16 GB: Equal to the size of the RAM
More than 16 GB: 16 GB

Then there is Solaris...

Server Memory Minimum Requirements

  Note:

  On Oracle Solaris, if you use non-swappable memory, like ISM,
  then deduct the memory allocated to this space from the available RAM
  before calculating the swap space. If you plan to install Oracle
  Database on systems using DISM, then available swap space must be at
  least equal to the sum of the SGA sizes of all instances running on
  the servers.

It is about summing the SGAs of multiple database instances on the same server, not about multiple database instances in a cluster on different servers (RAC).


Can an encrypted MySQL restart without the keyring file on the box?

Yes, mysql will start, but you will receive an error if you try to access the encrypted table.


What security threats does the SA account and other known account names pose to security?

Does an known account name like sa, pose a security threat to database?

A "god" user account with a known name is generally considered a worse idea than a god user with a less well known name. It makes brute force attacks that bit easier as the attacker only has to guess the password and not the username and the password.

Also having a god user anyway can be dangerous. You are generally better off having specific users with specific rights for what they need to do. This sort of privilege based security is easier to implement from scratch than it is to retrofit into your environment later.

Disabling sa and giving specific users specific admin rights as needed in SQL server is essentially the same recommendation as disabling root and handing out admin rights as needed via sudo under Linux and similar. You can always re-enable sa once directly connected to the machine with adequate privileges should anything go wrong and you end up dropping all the rights your users need to operate (and fix the issue) just the same as you can engineer root access to a Linux box if you have physical access to the box - so disabling the account is no magic bullet (but once an attacker has physical access to your machine, or full Administrative access via RDC or SSH, all bets are off anyway).

  When using windows authentication on SQL Server does it impose the same password policy(if it was set to say account lockout after 5 times)?

When using Windows Integrated Authentication SQL server has no control over account lockouts and such - it just maps a Windows user to an SQL user and asks the OS to vouch for the fact that the user has provided appropriate credentials. For interactive human users this means any lockout would occur as the user attempted to authenticate with Windows, not as they logged in to SQL Server.


Bulk Import appending the data to the data file

Bulk inserts with small batch sizes can result in a significant increase in unused reserved space due to a full extent allocation for each batch for fast inserts per this Tiger Team post. I see from your comment that the batch size is only 200 rows and this explains the main reason for the large amount of unused space, although I would expect existing unallocated extents (not to be confused with unused space) to be reused before growing the file.

If you don't have control over the optimal batch size (e.g. third-party application), you can turn on trace flag 692 to disable fast inserts and reduce space needs but at the expense of performance.


System Database Migration and Security - Log Shipping or DB Mirroring

System databases cannot be mirrored or logshipping.

The best way is to script and sync up  desired objects e.g. logins, jobs, etc.

The best tool that I have used so far for migrating databases, jobs, agent schedules, operators, logins, etc is powershell based dbatools.io


Tables for two type of users in database. How to solve this problem?

Why do you have two separated tables? Your model is faulty by design. You don't need in this case two different tables. Just create one table for user accounts and a different one for user account type.


Good ratio for data, journal, and log drive sizes for MongoDB?

You can combine it on 1 disk, if you wish. Not obligated to split.

Journal

Journal will take 3GB (or less than 400MB if you use --small-files option)

Journal + Pre-Allocation

Be aware. If you don't use --small-files, then at least 8GB (journal and oplog included) will be pre-allocated to your disk. This is not lost space, but just reserved to improve the speed of mongo.  Using --small-files, only 1.4GB will be preallocated.

For discovering and testing purposes. Start with --small-files.

Logfiles

Logfiles will depend on the verbosity and insensitivity of the system. But for as you speak about a 8GB data disk. Then it won't be that much. Default is only some system messages and errors. (http://docs.mongodb.org/manual/reference/configuration-options/)

To let logfiles rotate, send "kill -SIGUSR1 pid" or
mongo --port 27017 --eval "db.runCommand({logRotate:1});" admin
(http://docs.mongodb.org/manual/reference/command/logRotate/). And then I delete daily via a crontab the logs older than 3 days.

Splitting on different drives

The reason is to optimize the disk for the purpose. Journal is a capped collection that just writes in sequence. So less IOPS needed. And logs are logs, just adding information. And then Data, well, reading jumps around a lot, and writing sometimes also, filling up gaps that were freed. And while journal and log are written away on other disks, the data disk doesn't lose time on that. Every little bit can help on intensive systems. The next step then is Replication and Sharding to spread the load.

On the https://university.mongodb.com, you can get more information about this if you are interested. Following M202 MongoDB Advanced Deployment and Operations now, what offers some specific information to optimize.


Why using a local temp table (instead of a global temp table or a regular table) influences the Query Optimizer to choose a poor query plan?

This just looks like a quirk with the optimisation of that query producing a slightly different tree shape when simple parameterisation is attempted vs when it isn't.
This is not attempted in the case of the local temp table but is for the global temp table and permanent table.
The auto parameterisation does not succeed in any case but when attempted on this query it results in a slightly different tree shape with the order of the predicates retained from the query text (rather than with the <> first as happens when it is not attempted).
My supposition is that this ordering the predicates end up in before cost based optimisation somewhat arbitrarily determines what will be the seek predicate and what will be the residual in this case (i.e. the competing options aren't considered during cost based optimisation).
Adding and 1=1 is one way of preventing this and the plan reverts.


Suppress specific SQL Server log warnings and messages

Overall, suppression or inclusion of logging is generally accomplished via trace flags - you are correct.

This, however, does not mean that:

There is a trace flag for each log item
The trace flags are publically documented
The trace flags don't differ through versions

  such as when a spid is killed

There isn't a trace flag to turn this off, and I highly doubt that if there were one it would ever be documented or given out.

If the killed session messages annoy you, filter them out as Aaron has stated. You can create your own noise words list or known message list that you don't care about specifically for your dev environments.

Additionally, I'd look into giving them some SQL Azure databases to let them do development so that I didn't have to worry about this kind of stuff and could focus my time on actual production issues or new projects.

Disclaimer: I work for Microsoft.


Dropping large clustered primary key fills up tempdb

Assuming you have a maintenance window that allows for a short period of downtime I would suggest using BCP to dump the table to a file. If space is an issue, compress the target folder in advance of the export.

bcp.exe "Database.dbo.OurTable"  OUT "C:\Temp\bcp\OurTable.dat" -S ServerName -T -c -r "|¬|\n" -t "|¬|" /b 10000

DROP your old table, CREATE new with BIGINT key, then import the data.

BULK INSERT
    dbo.OurTable
FROM 'C:\Temp\bcp\OurTable.dat'
WITH
(
    FIELDTERMINATOR = '|¬|',
    ROWTERMINATOR = '|¬|\n',
    BATCHSIZE = 10000,
    ERRORFILE = 'C:\Temp\bcp\OurTable_error.txt'
)

The odd looking choice of delimiter |¬| is a typically safe combination that avoids collisions in text fields. So far anyway.

As you mention in the comments you can minimise growth of the log by switching to bulk-recovery. Given that you will have no other activity during this maintenance window you could switch to simple, run the process, switch back to full and take a full backup instead. Note that use of BATCHSIZE in the example will create a separate transaction for each batch.


Revoke and Grant Privileges on SQL Server Database

REVOKE means is taking the privileges back. If you didn't give any permission to any user, you can not take a back.
You can use DENY, but not on the DROP and TRUNCATE. There is not a DROP and TRUNCATE permission just like you wrote.
DENY DELETE, UPDATE, INSERT ON DATABASE::[databasename] TO user1;

But, you should think about the privileges which are you want to grant and check out the database roles.


How to return decimals in the right number format with Oracle JSON_VALUE

You can use TO_NUMBER() without a REPLACE(). For example:

select to_number('12345,67', '9999999999D9999', 'NLS_NUMERIC_CHARACTERS='',.''')
from dual;

TO_NUMBER() documentation.


The Selected tables in the subqueries must be limited first before summing them up, but i can't reproduce your exspected result

CREATE TABLE IF NOT EXISTS fruit (
   fruit_id INTEGER  NOT NULL ,
   name1 varchar(50) NOT NULL,
    name2 varchar(50) NOT NULL,
   rating1 integer NOT NULL,
    rating2 integer NOT NULL,
     delivered_on date NOT NULL,
     rush_order varchar(3) NOT NULL,

   PRIMARY KEY  (fruit_id)
 );

INSERT INTO fruit (fruit_id, name1, name2, rating1, rating2, delivered_on, rush_order) VALUES
(1, 'Apple', 'Banana', 5, 4,'2015-10-01', 'yes'),
(2, 'Pear', 'Banana', 3, 4,'2015-10-27', 'yes'),
(3, 'Apple', 'Pear', 5, 4,'2015-10-29', 'no'),
(4, 'Banana', 'Apple', 2, 5,'2015-11-27', 'yes'),
(5, 'Banana', 'Apple', 4, 1,'2015-11-29', 'yes'),
(6, 'Apple', 'Banana', 4, 2,'2015-12-02', 'no'),
(7, 'Pear', 'Apple', 3, 5,'2015-12-20', 'yes');

 SELECT *,
    COALESCE(
        (SELECT sum(f2.rating1)
    FROM (SELECt * FROM fruit f2
        WHERE f1.name1 = f2.name1 AND f1.delivered_on > f2.delivered_on AND f2.rush_order = 'yes' ORDER BY f2.delivered_on LIMIT 2) f2),0) +
        COALESCE(
        (SELECT sum(f3.rating1)
    FROM (SELECt * FROM fruit f3
        WHERE f1.name1 = f3.name1 AND f1.delivered_on > f3.delivered_on AND f3.rush_order = 'yes' ORDER BY f3.delivered_on LIMIT 2) f3),0) AS fruit1_rating,
            COALESCE(
        (SELECT sum(f4.rating1)
    FROM (SELECt * FROM fruit f4
        WHERE f1.name1 = f4.name2 AND f1.delivered_on > f4.delivered_on AND f4.rush_order = 'yes' ORDER BY f4.delivered_on LIMIT 2) f4),0) +
        COALESCE(
        (SELECT sum(f5.rating1)
    FROM (SELECt * FROM fruit f5
        WHERE f1.name1 = f5.name2 AND f1.delivered_on > f5.delivered_on AND f5.rush_order = 'yes' ORDER BY f5.delivered_on LIMIT 2) f5),0) AS fruit2_rating
FROM fruit f1;

fruit_id | name1  | name2  | rating1 | rating2 | delivered_on | rush_order | fruit1_rating | fruit2_rating
-------: | :----- | :----- | ------: | ------: | :----------- | :--------- | ------------: | ------------:
       1 | Apple  | Banana |       5 |       4 | 2015-10-01   | yes        |             0 |             0
       2 | Pear   | Banana |       3 |       4 | 2015-10-27   | yes        |             0 |             0
       3 | Apple  | Pear   |       5 |       4 | 2015-10-29   | no         |            10 |             0
       4 | Banana | Apple  |       2 |       5 | 2015-11-27   | yes        |             0 |            16
       5 | Banana | Apple  |       4 |       1 | 2015-11-29   | yes        |             4 |            16
       6 | Apple  | Banana |       4 |       2 | 2015-12-02   | no         |            10 |            12
       7 | Pear   | Apple  |       3 |       5 | 2015-12-20   | yes        |             6 |             0

db<>fiddle here


LEFT JOIN subquery fails in the presence of an EXISTS clause in the main query

This looks like a bug. Please send a bug report to MariaDB. Some details:
First, make sure it is a bug.
Query A produces a single row (assuming that contact (id) has a unique (or primary key) constraint:
-- Query A
SELECT c.id contact_id
FROM contact c

 WHERE
  /* ➋ various conditions on table 'c' */
 c.do_not_email = 0 AND c.is_opt_out = 0 AND c.is_deceased = 0 AND c.is_deleted = 0
   AND c.contact_type = 'Individual'

   /* ➌ */
   AND EXISTS (
      SELECT gc.contact_id
      FROM group_contact gc
      WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12)
    )

   /* ➍ */
   AND c.id=1
ORDER BY c.id ;

Query B produces one row (with value 15 in sent)
-- Query B
/* ➊ Inner query */
SELECT eq.contact_id contact_id, COUNT(*) sent
FROM mailing_job j
INNER JOIN mailing_event_queue eq ON eq.job_id = j.id
WHERE j.end_date > NOW() - INTERVAL 3 MONTH
  AND eq.contact_id = 1
GROUP BY eq.contact_id ;

Query C produces a single row but with value NULL (or anything different than the 15 in Query B).
-- Query C
SELECT c.id contact_id, engagement.sent
FROM contact c
LEFT JOIN (
    /* ➊ Inner query */
    SELECT eq.contact_id contact_id, COUNT(*) sent
    FROM mailing_job j
    INNER JOIN mailing_event_queue eq ON eq.job_id = j.id
    WHERE j.end_date > NOW() - INTERVAL 3 MONTH
    GROUP BY eq.contact_id
  ) engagement ON engagement.contact_id = c.id

 WHERE
  /* ➋ various conditions on table 'c' */
 c.do_not_email = 0 AND c.is_opt_out = 0 AND c.is_deceased = 0 AND c.is_deleted = 0
   AND c.contact_type = 'Individual'

   /* ➌ */
   AND EXISTS (
      SELECT gc.contact_id
      FROM group_contact gc
      WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12)
    )

   /* ➍ */
   AND c.id=1
ORDER BY c.id ;

If the above do happen, it's almost surely either a bug or corrupted data in tables/indexes.
Other things to check / add to the report:

Execution plans for all queries (use EXPLAIN SELECT ...)
Complete CREATE TABLE for all tables involved, including indexes. You can use SHOW CREATE TABLE tablename ; for that.
Your exact version of MariadDB (try select version(); ).
Try dropping and recreating the indexes on the tables involved if possible (not the tables!) and then test again.
If you are not running the latest 10.3, try upgrading to the latest 10.3 if possible, and test again.
If you have available servers with more recent major versions (10.4 or 10.5), try recreating the tables with the same data and test again.
Try to remove conditions (as you did) or rewrite C and test if it produces different plan and correct or wrong results, eg.:

remove each one of these:
  do_not_email = 0 AND is_opt_out = 0 AND is_deceased = 0 AND is_deleted = 0
  AND contact_type = 'Individual'

remove the ORDER BY

remove subqueries 3 and/or 4.

add a condition in b subquery:
  LEFT JOIN (
      /* ➊ Inner query */
      SELECT eq.contact_id contact_id, COUNT(*) sent
      FROM mailing_job j
      INNER JOIN mailing_event_queue eq ON eq.job_id = j.id
      WHERE j.end_date > NOW() - INTERVAL 3 MONTH
        AND eq.contact_id = 1
      GROUP BY eq.contact_id
    ) engagement ON engagement.contact_id = c.id

rewrite the whole query as:
  -- Query Z
  SELECT z.contact_id, engagement.sent
  FROM
      (
      SELECT c.id contact_id
      FROM contact c

       WHERE
        /* ➋ various conditions on table 'c' */
       c.do_not_email = 0 AND c.is_opt_out = 0 AND c.is_deceased = 0 AND c.is_deleted = 0
         AND c.contact_type = 'Individual'

         /* ➌ */
         AND EXISTS (
            SELECT gc.contact_id
            FROM group_contact gc
            WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12)
          )

         /* ➍ */
         AND c.id=1
      ) AS z
      LEFT JOIN (
          /* ➊ Inner query */
          SELECT eq.contact_id contact_id, COUNT(*) sent
          FROM mailing_job j
          INNER JOIN mailing_event_queue eq ON eq.job_id = j.id
          WHERE j.end_date > NOW() - INTERVAL 3 MONTH
          GROUP BY eq.contact_id
        ) engagement ON engagement.contact_id = z.contact_id
      ;

combine any or all of the above rewrites and test execution plans and results and add to the report the ones (queries and plans) that fail to produce correct results.

Testing in the latest subversions is recommended, as this may be a bug that has been fixed.


MySQL - trying to combine `GROUP_CONCAT` with `COUNT()`

You've already found your answer, but here are the reasons:

The reason you're getting duplicates is because you're joining tab1 with itself without criteria on the join, which is simply resulting in duplicates. The query can be pretty easily accomplished without the join, looking like this:

SELECT A.team, A.name, GROUP_CONCAT(A.count SEPARATOR ' , ')
FROM
   (SELECT team, name, CONCAT(item, '(', count(item), ')') AS count
    FROM tab1
    GROUP BY team, name, item) A
GROUP BY A.team, A.name;


Since you're using 2012+, you have windowing functions available to you which I think make this easier to solve.

Here's my set up.

IF NOT EXISTS
(
    SELECT *
    FROM
        sys.tables AS T
        INNER JOIN
            sys.schemas AS S
            ON s.schema_id = t.schema_id
    WHERE
        S.name ='dbo' AND T.name = 'StagingBenefit'
)
BEGIN
    CREATE TABLE
        dbo.StagingBenefit
    (
        StagingBenefitSK int identity(1,1) NOT NULL

    ,   EmployeeID int NOT NULL
    ,   BenefitID int NOT NULL

    ,   BenefitProvider char(1) NOT NULL
    ,   Cost int NOT NULL
    ,   FlagA bit NOT NULL
    ,   FlagB bit NOT NULL
    ,   FlagC bit NOT NULL
    ,   BenefitName varchar(30) NULL
    ,   Scenario varchar(30) NOT NULL
    );
END

TRUNCATE TABLE dbo.StagingBenefit;

INSERT INTO
    dbo.StagingBenefit
SELECT
*
FROM
(
    VALUES
        (1,1, 'A', 100, 0, 0, 0, 'Teeth', 'SingleRow')
    ,   (2,2, 'A', 200, 0, 0, 0, 'Teeth', 'DoubleRow, Samesies')
    ,   (2,2, 'A', 222, 0, 0, 0, 'Teeth', 'DoubleRow, Samesies')
    ,   (3,3, 'A', 300, 0, 0, 0, 'Teeth', 'ThreeRow,DoubleFlagged')
    ,   (3,3, 'A', 330, 1, 0, 0, 'Elbow', 'ThreeRow,DoubleFlagged')
    ,   (3,3, 'A', 333, 0, 1, 0, 'Elbow', 'ThreeRow,DoubleFlagged')
    ,   (4,4, 'A', 400, 0, 0, 0, 'Teeth', 'MultipleProviders')
    ,   (4,4, 'A', 440, 0, 0, 0, 'First', 'MultipleProviders')
    ,   (4,4, 'B', 444, 0, 0, 1,    NULL, 'MultipleProviders')
)D
(
    EmployeeID
,   BenefitID

,   BenefitProvider
,   Cost
,   FlagA
,   FlagB
,   FlagC
,   BenefitName
,   Scenario
)
;

I would then look at a query something like this

SELECT DISTINCT
    SB.EmployeeID
,   SB.BenefitID
,
    CASE
        WHEN
            MIN(SB.BenefitProvider) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID)
            <> MAX(SB.BenefitProvider) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID)
            THEN 'Multiple'
        ELSE
            MIN(SB.BenefitProvider) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID)
        END AS BenefitProvider
    -- SUM this
,   SUM(SB.Cost) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) As TotalCost
,   CAST(MAX(CAST(SB.FlagA AS int)) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) AS bit) AS FlagA
,   CAST(MAX(CAST(SB.FlagB AS int)) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) AS bit) AS FlagB
,   CAST(MAX(CAST(SB.FlagC AS int)) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) AS bit) AS FlagC
,   MIN(SB.BenefitName) OVER
    (
        PARTITION BY SB.EmployeeID, SB.BenefitID
    ) AS FirstBenefit
,   SB.Scenario
FROM
    dbo.StagingBenefit AS SB

Picking through some of the tricks of the query

DISTINCT I feel this is the dirtiest part of it but it solves the problem
MIN/MAX BenefitProvider  - I find the first and last for my window (employee and benefit ids) and if they are different, which I determine via the CASE expression, I use the text Multiple. Otherwise, I just grabbed the minimum but max would work just as well. Heck, I probably could have just skipped the second aggregation call and just made it column name.
MAX Flag - I made this as hard as I could by using a bit data type which you cannot aggregate on so I explicitly convert to an integer and then cast the results of that back to a bit. Same windowing though. Net result is if anywhere my flag is set to true, it'll get preserved across rows
MIN BenefitName - the rule here is to pick the first non-null benefit name. The aggregate function eliminates the NULL and returns the text "First"


Is the innodb_lazy_drop_table problem solved?

It seems to be fixed (see percona manual):

  Feature has been removed because the upstream DROP TABLE implementation has been improved.


Yes.

If you want to import the structure of the Database on some other machine there are some steps as follows

1.on Machine A(generate the dump without the data)

mysqldump -uuser -ppass --no-data --databases db1 db2 db3 > database_structure.sql

using mysqldump with --no-data will generate the dump without the data.

if you want only data dump do as(It will not contain the create statements)

mysqldump -uuser -ppass --no-create-info --databases db1 db2 db3 > database_data.sql

2.copy the  database_structure.sql on Machine B

Then you want to restore its structure on Machine B do

3.mysql -uuser -ppass < database_structure.sql

Then you have database structure available on the Machine B.

try it..


Changing metadata changes query output issue

Is this behaviour correct?

It is intentional (by design) behaviour; whether it is 'correct' or not is more a matter of opinion.

The general point is: SQL Server does not guarantee the timing or number of evaluations of expressions. This behaviour exists to give the query optimizer the freedom it needs to find good execution plans.

As a consequence, query optimization may change the query's output or runtime behaviour, if there is a dependency on the timing or number of evaluations of an expression (or scalar function).

In the question's examples, the runtime behaviour depends on whether the query optimizer identifies each expression as a runtime constant or not. The execution engine may decide to evaluate (and cache) runtime constant expressions before query execution begins.

If the optimizer identifies the expression 1/0 as a runtime constant, the XML form of show plan output will show it labelled as ConstExprxxxx:

Runtime constants like this can be evaluated once before query execution begins, and the result cached, as mentioned previously. Where this occurs, a divide by zero error is returned during this pre-query constant expression evaluation.

Adding the index to the table happens to take a route through query optimization that does not result in 1/0 being labelled a runtime constant (no ConstExprxxxx below):

In this particular case, the presence of a uniqueness constraint means the optimizer decides there is no benefit in caching the result of an expression that will only be evaluated once. The start-up filter means the faulty expression is never evaluated.

These expression evaluation behaviours are not documented or completely determined by the presence of ConstExpr labels in all cases. The answer is not to write code that depends on the timing or number of evaluations of expressions by the execution engine.


handle subquery with joins where same result is to be joined with multiple tables

I have written same query subquery twice

Create VIEW (created once, it exists in database until ALTER or DROP, like tables):

CREATE VIEW my_subquery AS
SELECT tableB.ID
     , tableB.Address
--   , tableD.Name
--   , tableC.tableCName
--   , tableC.Country
FROM
    tableB
INNER JOIN tableD ON tableB.tableDID = tableD.ID AND tableD.Deleted = 0
INNER JOIN tableC ON tableD.tableCID = tableC.ID
WHERE
    tableB.Deleted = 0;

and then use it in Your query like a common table:

SELECT tableA.aa, tableA.bb, tableK.address, tableP.address
FROM tableA
LEFT JOIN my_subquery AS tableP ON tableP.ID = tableA.aa
LEFT JOIN my_subquery AS tableK ON tableK.ID = tableA.bb;


Assuming that "user" does not include any roles with superuser privileges (who can do everything), run this as owner of the table or superuser:

REVOKE ALL ON TABLE account FROM public;
GRANT SELECT, INSERT ON TABLE account TO public;

Users also need the USAGE privilege for the SEQUENCE attached to the serial column:

GRANT USAGE ON SEQUENCE account_user_id_seq TO public;

About serial:

Safely rename tables using serial primary key columns

.. and the USAGE privilege on the schema and CONNECT of the database, both of which are the case for schema public by default.

If you only want a subset of users to be affected, grant privileges to a group role instead of public and grant membership in that role to those users. You still need to revoke privileges from the pseudo-role public, which can be viewed as the default group role granting membership to all (irrevocably). And grant whatever is still needed to others (by way of another group role, for instance).

CREATE ROLE my_group;
GRANT my_group TO my_user1;
GRANT my_group TO my_user2;  -- more?
REVOKE ... FROM public;
GRANT SELECT, INSERT ON TABLE account TO my_group;
GRANT USAGE ON SEQUENCE account_user_id_seq TO my_group;

CREATE ROLE others;
GRANT others TO other_user1;
GRANT others TO other_user2;
GRANT ... TO others;

Related:

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?


Access Form: New Record command not available now

The fact that the error message said "acNewRec command is not available now" should have directed you to the line DoCmd.GoToRecord , , acNewRec.  The form's recordset is not guaranteed to be open and positioned correctly during the Form_Open() event, especially not always accessible via DoCmd methods.  Documentation says otherwise, but experience shows that there are potential problems with relying on the recordset to be in a reliable state in Form_Open.

There are other ways to make the form automatically open on a new record.  Try putting the same line of code within the Form_Load() event.  Setting the form's Data Entry value to Yes allows only new records.


You are working too hard.

Plan A -- RENAME TABLE

Assuming the production database is really readonly, you don't need FKs, just the indexes they generate.  (And you may not need all of them.)

So, before testing in Staging, turn off FK checks and drop all the FKs.  Then consider dropping any excess indexes.  Then test.

Since there is no RENAME DATABASE, you are stuck with a rather rapid series of

RENAME TABLE live.table1 TO old.table1,
             live.table2 TO old.table2,
             ...
             new.table1 TO live.table1,
             ... ;

Plan B -- 2 Instances

Better than that would be to have two instances of MySQL on the same server or (better yet) on different servers.  One for staging, one for production.  The flip-over does, however, require some way to redirect clients.  This may involve port number, proxy, IP address, etc.  Look at HAProxy and MaxScale for achieving the flip for you.  (They probably work for either case -- 2 instances on one server or 2 servers.)


How to duplicate the database in postgresql?

If you want to duplicate it within the same PostgreSQL install and you don't have actively connected users there's a handy shortcut:

CREATE DATABASE my_new_database TEMPLATE my_old_database;

or from the shell

createdb -T my_old_database my_new_database;

Otherwise you'll need to use pg_dump, createdb and pg_restore, e.g.

pg_dump -Fc -f olddb.pgdump -d olddb &&\
createdb newdb &&\
pg_restore -d newdb olddb.pgdump

If you're using nohup so the command doesn't die if you lose your ssh session, consider using screen instead.


How to "self audit" all of the SQL Server licenses at an organization?

[…] nothing seems to provide any information on the licenses that the organization owns or how to acquire it.
How the heck do I know what SQL Server licenses my new company owns […]

If you have a Premier Agreement
This is fairly straight  forward, you will have an Account Manager assigned (TAM) which can pull up any licensing information given to Microsoft (assuming it was bought through a licensed 3rd party or part of your Premier contract).
From the above you can then divvy out the licenses as needed/used based on your environment.
If you don't have a Premier Agreement
In this case you'll need to log-in/contact/find all records related to licensing on your own. This might mean looking for paper records from 2004 as you've suggested, contacting whatever 3rd party reseller of licensing you went through in those periods of time, or tracking down POs to find out if anything was bought and where it was bought.
The last ditch possibility under this item is to setup a time to speak with a licensing specialist or a Microsoft account representative to see if they can pull anything from previous agreements of years. Note that if you don't have SA, then the licenses may not be applicable to your environment. Also note there are older style licenses that also may no longer be applicable to the environment.

[…] most or all of the licenses do not have SA because it's cheaper to buy new ones.

SA gives other benefits than just being allowed to be at any version. It mainly focuses benefits around license mobility, HA/DR, virtualization, and hybrid scenarios. It's also generally cheaper than buying net new licenses.


How to set Parent field according to another hierarchy field

Direct update:

UPDATE c0
SET    ParentId = CASE WHEN c0.Serial IS NULL
                  THEN (SELECT TOP 1 c1.ChartId
                        FROM   @Chart c1
                        WHERE  c1.[Number] < c0.[Number]
                               AND c1.Serial IS NULL
                               ORDER BY c1.[Number] DESC)
                  ELSE (SELECT TOP 1 c2.ChartId
                        FROM   @Chart c2
                        WHERE  c2.[Number] = c0.[Number]
                        AND    c2.Serial IS NULL)
                  END
FROM   @Chart c0;

Using a CTE to calculate ParentId:

;WITH x AS
(
    SELECT
        c1.ChartId,
        CASE WHEN c1.Serial IS NULL
             THEN LAG(c1.ChartId) OVER (ORDER BY c1.[Number], c1.Serial)
             ELSE (FIRST_VALUE([ChartId]) OVER (PARTITION BY [Number] ORDER BY [Number], Serial))
             END AS ParentID
    FROM
        @Chart c1
)
UPDATE c1
SET    ParentId = x.ParentId
FROM   @Chart c1
JOIN   x
       ON x.ChartId = c1.ChartId;

ChartId | ParentId | Number | Serial | AccountName                     | CurrencyId | cType | TotalDebit | TotalCredit
------: | -------: | :----- | :----- | :------------------------------ | ---------: | :---- | :--------- | :----------
      1 |     null | 1      | null   | CAPITAL & LONG TERM LIABILITIES |          1 | T     | 0.00       | 0.00
      2 |        1 | 10     | null   | CAPITAL & LONG TERM LIABILITIES |          1 | T     | 0.00       | 0.00
      3 |        2 | 101    | null   | CAPITAL & LONG TERM LIABILITIES |          1 | T     | 0.00       | 0.00
      4 |        3 | 1013   | null   | CAPITAL & LONG TERM LIABILITIES |          1 | T     | 0.00       | 0.00
      5 |        4 | 1013   | 00001  | ShareHolder 1                   |          1 | R     | 0.00       | 0.00
      6 |        4 | 1013   | 00002  | ShareHolder 2                   |          1 | R     | 0.00       | 0.00
      7 |        4 | 1013   | 00003  | ShareHolder 3                   |          1 | R     | 0.00       | 0.00
      8 |        4 | 4      | null   | SUPPLIERS                       |          1 | T     | 0.00       | 0.00
      9 |        8 | 40     | null   | SUPPLIERS                       |          1 | T     | 0.00       | 0.00
     10 |        9 | 401    | null   | SUPPLIERS                       |          1 | T     | 0.00       | 0.00
     11 |       10 | 4011   | null   | SUPPLIERS                       |          1 | T     | 0.00       | 0.00
     12 |       11 | 4011   | 00101  | Supplier 1                      |          1 | R     | 0.00       | 0.00
     13 |       11 | 4011   | 00102  | Supplier 1                      |          2 | R     | 0.00       | 0.00
     14 |       11 | 4011   | 00103  | Supplier 2                      |          1 | R     | 0.00       | 0.00
     15 |       11 | 4011   | 00104  | Supplier 3                      |          2 | R     | 0.00       | 0.00
     16 |       11 | 4011   | 00105  | Supplier 3                      |          1 | R     | 0.00       | 0.00
     17 |       11 | 4011   | 00105  | Supplier 3                      |          3 | R     | 0.00       | 0.00
     18 |       11 | 41     | null   | CUSTOMERS                       |          1 | T     | 0.00       | 0.00
     19 |       18 | 411    | null   | CUSTOMERS                       |          1 | T     | 0.00       | 0.00
     20 |       19 | 4111   | null   | CUSTOMERS                       |          1 | T     | 0.00       | 0.00
     21 |       20 | 4111   | 00101  | Customer 1                      |          1 | R     | 0.00       | 0.00
     22 |       20 | 4111   | 00102  | Customer 2                      |          1 | R     | 0.00       | 0.00
     23 |       20 | 4111   | 00103  | Customer 2                      |          2 | R     | 0.00       | 0.00
     24 |       20 | 4111   | 00104  | Customer 3                      |          2 | R     | 0.00       | 0.00
     25 |       20 | 4111   | 00105  | Customer 4                      |          2 | R     | 0.00       | 0.00
     26 |       20 | 4111   | 00106  | Customer 4                      |          3 | R     | 0.00       | 0.00
     27 |       20 | 4111   | 00107  | Customer 5                      |          3 | R     | 0.00       | 0.00

db<>fiddle here


select LAST_LOAD_TIME, LAST_ACTIVE_TIME
from v$sql
where sql_id = Your SQL ID

Hope this will give you the expected result


How can I find out which tables use reference partitioning from the Oracle data dictionary?

To get a list of partitioned tables that have at least one referencing partitioned child table the [dba][all][user]_part_tables and [dba][all][user]_constrains data dictionary views, depending on the privileges granted, can be queried:

create table tb_part_parent(
  col  number primary key,
  col2 number
)
partition by range (col2) (
  partition part_1 values less than (100),
  partition part_2 values less than (300),
  partition part_3 values less than (500)
)

create table tb_part_child(
  col  number not null,
  col2 number,
  constraint fk_parent_1 foreign key(col) references tb_part_parent(col)
)partition by reference (fk_parent_1)

The query:

select t.table_name
  from user_constraints t
 where t.constraint_name in ( select w.r_constraint_name
                                from user_constraints w
                                join user_part_tables q
                                  on (q.table_name = w.table_name and
                                      q.ref_ptn_constraint_name = w.constraint_name)
                              )

Result:

TABLE_NAME
-----------------
TB_PART_PARENT


What is the Claim table used for?

Well, you can try that:

select EmployeeClaimId, sum(Amount)
from ClaimStatus cs
join (
    select EmployeeClaimId, max(version) as version
    from ClaimStatus
    group by EmployeeClaimId
    ) c on cs.EmployeeClaimId = c.EmployeeClaimId
        and cs.version = c.version


It is a direct tcp connection in a protocol developed by PostgreSQL. It can use  encryption if the server is configured to support it. You do not need to use SSH.

Remote connection alternatives:

unencrypted remote through postgres protocol
TLS encrypted remote through postgres protocol
SSH to the server then use psql/pgadmin local

Connection using the psql utility from remote host

psql -h yourserver.com.br -U yourusername yourdatabase

How to configure remote access through postgresql protocol

postgresql.conf

listen_address = '*'

The listen_address config must be changed to allow remote connections on the postgres protocol.

ssl_ciphers = 'HIGH:!aNULL'

The ssl_ciphers can be changed to improve encryption strength if you are more paranoid than the average admin. The example in the line reads allow only ciphers with more than 128 bit keys and do not allow unencrypted connections. If you want to know more about the ciphers you can look in the openssl man

ssl_cert_file = (...)
ssl_key_file = (...)

The ssl_(...)_file controls where to find the ssl certificates. In ubuntu and debian this is set to the self-signed certificate generated by the openssl installation. But you can change it to a valid SSL certificate. Postgres clientes such as psql, pgadmin and embedded in programming languages such as php will check the certificate chain against the trusted authorities.

pg_hba.conf

hostssl (...)

In all places you se a line host (...) in the pg_hba replace it by hostssl (...) in order to force your clients to use SSL connections.


You need to qualify SucNo DESC in the ORDER BY i.e. p.SucNo DESC.

Presumably the SQL2000 query processor can't deduce that f.SucNo and p.SucNo resolves to the same sort, whereas SQL2008 can.


deleting large volumes of rows from table efficiently in SQL Server

You can break it up into chunks - delete in a loop; each delete iteration it's own transaction and then clearing the log at the end of each loop iteration. Finding the optimal chunk size will take some testing.

I suggest you take a look at this article by Aaron Bertrand, where he explains the details and runs tests for different scenarios, to show the impact (duration, transaction log):
http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes

As for the where x like '%blah%' delete condition - it will make the query not sargable (can't leverage the indexes to do an index seek). So even if you have an index to support column x, it's going to be a scan.

Resources for this:

Brent Ozar - Sargability: Why %string% Is Slow
Index usage by %Like% operator – Query Tuning


SQL Server, ServicerBroker for asynchronized execution of stored procedures

At PROCEDURE proc_BrokerTargetActivProc we have infinite loop: WHILE
  (1=1). Why ?

The process for receiving messages in SQL Server requires what I call a semiactive polling. The code makes a request to the queue for the next message, then waits for a response. That is what the WAITFOR block is doing.

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM TASK_QUEUE
), TIMEOUT 5000;

If the process was not in an infinite loop, the procedure would poll once, either receive a message or timeout, then quit. With the infinite loop, the procedure polls continuously until stopped.

  ExecuteProcedureAsync push to queue message with name of procedure to
  execute. What now ? How does it work that BrokerTargetActivProc will
  be called with exactly one message ?

When ExecuteProcedureAsync is called, it places the message in an asynchronous message queue. This is a process that is external to SQL Server, accepts messages from sending processes, and forwards them to the receiving process when it is ready for the next message (that is, when the receiver polls the queue for the next message on the queue). The reference to BrokerTargetActiveProc when putting the message on the queue is letting the queue know where the message is going. It is not an interrupt that triggers the BrokerTargetActiveProc procedure.

In an asynchronous queue like this, if the receiver is down, then the messages simply wait in the queue until the receiver comes back up or the queue runs out of resources to hold messages.


Using postgres with the command line without pgAdmin

The PostgreSQL command line client is called psql.  You can do pretty much everything with it (apart from taking dumps, for example), you'd better reading its documentation.  Try running locate psql in a terminal and you will find its binary.

As far as I know, on OS X (and typically on other systems, too) pgAdmin is bundled with the server package, so you won't be able to install PostgreSQL without it.  Here I am not very well informed, so handle this with care.

Regarding the postgres user, on linux machines it is a must to have it (and again, it looks the same on all other systems, too), you simply cannot run the server with other users.  If you don't use PostgreSQL, the user will sit there quietly, won't eat your food and drink your beer, so you need not to worry about it.


SUGGESTION #1 : Use a Different Index
ALTER TABLE sale ADD INDEX tester2 (seller_id,buyer_id,date_sold);

I suggest this order because seller_id in the query is a constant (35514335). That would make looking up all buyer_id values.
SUGGESTION #2 : Redesign the Query
From the looks of the query, you are retrieving first date_sold for buyer_id for seller_id 35514335.
SELECT buyer_id,MIN(date_sold) date_sold
FROM sale WHERE seller_id = 35514335
GROUP BY buyer_id;

If seller_id 35514335 has buyers who purchased from other sellers as well, then do this:
SET @given_seller_id = 35514335;
SELECT DISTINCT A.buyer_id,B.seller_id FROM
(
    SELECT buyer_id,MIN(date_sold) date_sold
    FROM sale WHERE seller_id = @given_seller_id
    GROUP BY buyer_id
) A LEFT JOIN sale B USING (buyer_id);

If you do not want to include seller_id 35514335 in the output, then do this:
SET @given_seller_id = 35514335;
SELECT DISTINCT A.buyer_id,B.seller_id FROM
(
    SELECT buyer_id,MIN(date_sold) date_sold
    FROM sale WHERE seller_id = @given_seller_id
    GROUP BY buyer_id
) A LEFT JOIN sale B USING (buyer_id)
WHERE B.seller_id <> @given_seller_id;

Give it a Try !!!


Installing an older CU over a Security Patch?

#TL;DR
There is nothing you currently need to do to get the fixes included in CU4. They're already there, even though @@VERSION incorrectly suggests you have a GDR (and, as you've read elsewhere, GDRs do not contain CU fixes). If you want more background, please read on.
#Background
Patches are cumulative, including security updates, which means they include all previous patches in that branch. The size of the (compressed executable) file is not a very reliable way to determine whether or not it includes an earlier patch.
To be clear, each SQL Server release (including each service pack before SQL Server 2017) is what I'll call a "main branch." Once that main branch is released, they later need to enhancements, usually in categories of performance, stability, or security. They release these fixes in the form of GDR and CU updates. These become two separate branches off the main branch:

The CU branch contains all fixes - performance, stability, security. Currently the cadence for these is one a month for the first year of a main branch, and then every other month after that (until that branch is retired, which is longer now than it used to be because their are no service packs making previous service packs obsolete). Once you're on the CU branch, as you apply more updates, you will always be getting all the CU fixes. If you apply CU1 and then CU4, you have all the fixes from CU1, CU2, CU3, and CU4. If you apply CU5 and then CU12, you have all the fixes from CU1, CU2, ..., CU11, CU12.
Sometimes a security release is published out-of-band (meaning not as part of the monthly/bi-monthly CU cadence). This is usually either because the issue is critical or because it needs to be coordinated with a security bulletin that involves other products (like Windows). Like a CU, security updates that apply to a CU branch contain all the fixes from the CU branch up to that point.
The GDR branch only includes critical fixes, usually security-related, for the main branch (e.g. Service Pack 3). That is the branch I was talking about in the link referenced below: I meant that the GDR update for SP3 does not contain any of the CU updates (the post says "critical updates" but I don't have editorial control to fix it).
The point of the GDR branch is to provide as little surface area changes as possible (because they have to test the impact of all those changes), while still keeping customers safe. Each successive GDR update is just a new build on the GDR branch and so, like CUs and security updates on CU branches, a security update for the GDR branch also contains all the other GDR updates that came before it.
Except for occasionally early in a release, there are more CU updates than GDR updates, primarily because they're restricted to critical fixes and there are just a lot more non-critical fixes over time.

In your case, the (understandable) confusion comes from the fact that @@VERSION returns both CU and GDR in the name, which is nonsensical. When the patch was first released, I complained about this confusion, and after a lengthy debate, they fixed the terminology in the KB articles and other documentation. But it was too late to fix what @@VERSION returns on a build of the engine that was already distributed (and that problem remains today because that was the last update on the CU branch).
That's a lot of background, but to summarize, I can assure you that:

The build you have installed is on the CU branch and is not a GDR update, in spite of what @@VERSION might be telling you. You need to ignore what that string says because it is wrong but there is no way to fix it.
You definitely cannot apply CU4 on top of KB4535288. The installer will recognize that your version already includes all the fixes in CU4.
You do not need to uninstall the security update (which will put you back on whatever build you were on before you applied it), apply CU4 (which will put you on 12.0.6329.1), and reapply the security update (which will put you right back on 12.0.6372.1). You already have those fixes.
You can choose to not believe me and go ahead and do all of this, I'm just trying to help you avoid work that serves zero purpose, since you asked the question. Again, read everything here, but try to forget that your @@VERSION incorrectly suggests you are on a GDR build. You're not.

I had previously said the following, and don't believe I have contradicted myself with anything I've said here:
You can see the list of SQL Server 2014 updates here.
In this case, there is nothing you can possibly apply to SQL Server 2014 that will add anything to 12.0.6372.1. Literally nothing. Uninstalling KB4535288 to install CU4 just means you will be removing the security fix in KB4535288. I see absolutely no reason to do this. And no, you can't just "slap CU4 on top of KB4535288" - well, you can try, but you won't get very far, because the installer will recognize you already have everything in CU4.


How do I ORDER BY timestamp in this recursive query?

For simplicity I omit additional columns to focus on core of the problem.
Simple solution
Collect timestamps (created_at) in another array (path_created) like:
WITH RECURSIVE tree AS (
   SELECT id, created_at, ARRAY[id] AS path
        , ARRAY[created_at] AS path_created
   FROM   comments
   WHERE  parent_id IS NULL

   UNION ALL
   SELECT c.id, c.created_at, path || c.id
        , t.path_created || c.created_at
   FROM   tree     t
   JOIN   comments c ON t.id = c.parent_id
   WHERE  NOT c.id = ANY(t.path)
   )
SELECT *
FROM   tree
ORDER  BY path_created[1] DESC, path[1] ASC
        , path_created[2] DESC, path[2] ASC
        , path_created[3] DESC, path[3] ASC
        , ...

If two threads start at the same created_at, sort the smaller id first.
... which is good enough for a low maximum number of elements. Works if there are fewer elements in the array. It's neither very dynamic nor very elegant for an unknown number of levels, though.
Dynamic number of levels
You could concatenate or calculate a single value from created_at and id or use a document type like jsonb. All of these solutions have corner cases. If you put some smarts into it, it'll be unambiguous.
The general, clean solution is to compute the row number for each row per level with row_number(), ordered by any number of columns or expressions directly:
WITH RECURSIVE tree AS (
   SELECT id, created_at, ARRAY[id] AS path
        , ARRAY[row_number() OVER (ORDER BY created_at DESC, id ASC)] AS path_sort
   FROM   comments
   WHERE  parent_id IS NULL

   UNION ALL
   SELECT c.id, c.created_at, t.path || c.id
        , t.path_sort || row_number() OVER (ORDER BY c.created_at DESC, c.id ASC)
   FROM   tree t
   JOIN   comments c ON t.id = c.parent_id
   WHERE  NOT c.id = ANY(path)
   )
SELECT *
FROM   tree
ORDER  BY path_sort;

dbfiddle here
row_number() breaks ties among peers in any case. In the example the added id to the ORDER BY clause of the window definition. Assuming it is the PK and therefore breaks ties in deterministic fashion.


Ultimately, it depends on the architecture that your machine has.

(background) Nodes can solely store data in their properties. Their properties are stored using a key-value store. (per here)

The value in each property is limited to Java primitives (ints, floats, etc.), strings, and arrays of primitives/strings.

Therefore, the maximum amount of data a particular property can hold would be limited to the maximum size for a string or the maximum size for an array of strings (that's per node).  This limit (for 32-bit machines) is 4GB.  (Note that this may be limited to 2-3 GB.)

(Also, having said this, there was a bug previously that limited string size to 1 MB.  I expect that this is resolved.)

Of course, this raises the question of whether multiple properties could store more than 4GB per node.  Since the properties list is essentially a key-value store, it would expect that the maximum size would be limited by disk space and key selection.  I can't find anything to support or deny this, however.

That doesn't definitively answer your question, but from what I understand you should be able to store large amounts of data per node (up to disk space capacity).


I posted this answer in response to the same question on StackOverflow, so here is a re-post:

An important distinction to make is that according to IBM, zIIP is only available for "eligible database workloads", and those "eligible" loads are mostly targeted for large BI/ERP/CRM solutions that run on distributed servers, which are connecting through DDF (Distributed Data Facility) over TCP/IP.

IBM has a list of DB2 workloads that can utilize zIIP.  These include:

DDF server threads that process SQL requests from applications that
access DB2 by TCP/IP (up to 60%)
Parallel child processes. A portion of each
child process executes under a dependent enclave SRB if it processes
on behalf of an application that originated from an allied address
space, or under an independent enclave SRB if the processing is
performed on behalf of a remote application that accesses DB2 by
TCP/IP. The enclave priority is inherited from the invoking allied
address space for a dependent enclave or from the main DDF server
thread enclave classification for an independent enclave. (Versions up
to 11 allowed 80% to run on zIIP, v12 upped this to 100% eligible).
Utility index build and maintenance processes for the LOAD, REORG, and
REBUILD INDEX utilities.

And if you're on DB2 v10, you can also use zIIP with:

Remote native SQL procedures.
XML Schema validation and non-validation parsing.
DB2 utility functions for maintaining index structures.
Certain portions of processing for the RUNSTATS utility.
Prefetch and deferred write processing for the DB2 buffer pool

Version 11 added the following:

Asynchronous enclave SRBs (service request blocks) that execute in the
Db2 ssnmMSTR, ssnmDBM1 and ssnmDIST address spaces, with the exception
of p-lock negotiation processing. These processes include Db2 buffer pool
processing for prefetch, deferred write, page set castout, log read, and
log write processing. Additional eligible processes include index
pseudo-delete and XML multi version document cleanup processing.

Version 12 allowed parallel child tasks to go 100% to zIIP after a certain threshold of CPU usage.

So, if you're using COBOL programs, it would appear that IBM does not intend for you to use zIIP with these workloads.  You can still take advantage of zIIP with utilites (LOAD, REORG), and some steps of the RUNSTATS utility, so it may still be worthwhile to have some zIIP.


postgres backup / restore: restored database much smaller?

Maybe it's just index bloat. VACUUM FULL does not help with index bloat, on the contrary, as said in the doc for 8.4:

  The FULL option is not recommended for routine use, but might be
  useful in special cases. An example is when you have deleted or
  updated most of the rows in a table and would like the table to
  physically shrink to occupy less disk space and allow faster table
  scans. VACUUM FULL will usually shrink the table more than a plain
  VACUUM would. The FULL option does not shrink indexes; a periodic
  REINDEX is still recommended. In fact, it is often faster to drop all
  indexes, VACUUM FULL, and recreate the indexes.

(in more recent versions, this advice has disappeared because VACUUM FULL has been reimplemented differently).

See Routine reindeing and the REINDEX command.

The easiest way to reindex is to connect to the database with the db user that owns it and issue:

REINDEX database test_db;

Ideally, it should be done immediately after VACUUM FULL and the database should shrink to its lowest possible size at this point in time.


Sql server database restore vs index rebuilds

That doesn't make much sense.  If you restore a database from a time earlier, you won't be capturing data that has changed since the backup. (but I'm assuming you mean a backup directly followed by a restore)

If you're talking about taking a full backup and then immediately a restore, then you will persist the fragmentation in the restored database as it was when it was backed up.

So either way I will say no that is not advisable.  Prudent index rebuilds/reorganization based on the fragmentation is best practice.

See this blog post that on database restoration and index fragmentation that debunks that myth.  Restoring a database also restores the fragmentation at the time of the backup.


After dropping the clustered index, it is still attempting to do a clustered insert

You are removing the primary key in database Cvti101687 and inserting to a table in database cvti101639.


SQL Server 2016 Always ON high availability group listener configuration

What I don't understand is how the fail over is going to work

This is documented in a high level in Microsoft Docs.

  when I connect to the listener everything is fine when Node1 is primary, when I do manual failover to Node2, I can't connect.

That depends on your connection string, DNS infrastructure, and availability group setup.

If it's a single subnet, there shouldn't be an issue. If it's multi-subnet, you'll want to instruct the client driver to resolve all ips in parallel using the MultiSubnetFailover=true keyword in older version of .net/java/php/etc. Make sure you're using an updated client driver version that supports said keyword.

There are additional windows clustering private property settings for net names that can influence how these things work, but it doesn't sound like you've made any manual adjustments to these (defaults used) and I won't muddy the waters with it but did want you to be aware that there are additional settings.

  How will the listener switch to node2?

This happens at the WSFC level. A failure is detected or SQL Server initiates a failover which moves the resource group (and all contained resources) logically to the new node (where ever and which ever that may be). The resources are then attempted to come online. Part of the process to come on-line for a network name (listener) is to have at least one dependent IP address online and register with DNS among other items. This may or may not be the same IP Address based on how your networking is setup and the location of the new node owning the resources.

  Should the listener have 2 IPs for both nodes?

The listener needs at a minimum an IP Address for each subnet it could possibly be online in. It may have 1, 2, 3, ... 7, etc., depending on your specific setup and infrastructure.


SQL Azure Migration Wizard - Duplicate Primary Key

I ran into the same problem when trying to use SQL Migration Wizard to create a local copy of my database.  I know my source data didn't have duplicate key values, so I came up with the following script.  It simply causes duplicate key entries to be ignored (and a warning is generated instead of error).  Please read the comments in the code block below for instructions.

-- For some reason, SQL Azure Migration Wizard tries to insert duplicate records and fails.
-- I've wasted a lot of time making many many many attempts at copying the database, failing repeatedly,

-- So this is the workaround:
-- 1.0 In SQL Azure Migration Wizard: After selecting the database to script, select the "Advanced" button.
-- 1.1 Change the "Script Table / Data" property from "Table Schema with Data" to "Table Schema Only".
-- 1.2 Click "Next" and proceed until the "Schema Only" migrate script has been executed a new database.

-- 2. Run THIS Sql script with @IGNORE_DUP_KEY set to 'ON' against the TARGET database.

-- 3.0 Back in SQL Azure Migration Wizard, click "Back" until you see the advanced button again, and click it.
-- 3.1 Change the "Script Table / Data" property from "Table Schema with Data" to "Data Only".
-- 3.2 Click "Next" and proceed until the script has been executed.  NOTE: Table Name may still appear red like it failed, but looking at the message will show you that it was just a warning.
--     TO VERIFY: "SELECT COUNT(*) FROM TableName" on any tables that had the warning show in the source and target databases, and compare the counts.

-- 4. Run THIS Sql script, but set @IGNORE_DUP_KEY to 'OFF' to revert back to original/expected behavior.
DECLARE @IGNORE_DUP_KEY VARCHAR(MAX) = 'ON'; -- Options: ON, OFF

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)

DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'ALTER TABLE ' + @TableName + ' REBUILD WITH (IGNORE_DUP_KEY = ' + @IGNORE_DUP_KEY + ');'
        --print @sql
        EXEC (@sql)

        FETCH NEXT FROM TableCursor INTO @TableName
    END

CLOSE TableCursor
DEALLOCATE TableCursor

GO

I know this solution is being posted some time after the original question was asked, but hopefully this will help someone else out!


Continuously Adding SSDs to an existing PostgreSQL Server

Have you turned on TimescaleDB's native compression, available since v1.5?  I ask because you mention ZFS/BTRFS above, which suggests you aren't employing its compression already.
Typically see 90-98% storage savings in the wild...
https://docs.timescale.com/latest/using-timescaledb/compression
Additionally, you can use TimescaleDB's attach_tablespace command to add more disks, and then new chunks are load balanced across the available tablespaces.
https://docs.timescale.com/latest/api#attach_tablespace


Azure SQL - Setting up permissions for users

A separate schema will provide a security boundary and simply securty administration. Below is an example script that creates a schema for the team's objects with a schema-specific owner and grants permissions to create objects.

CREATE USER BOBO WITHOUT LOGIN;
GO
CREATE SCHEMA BOBO AUTHORIZATION BOBO;
GO
GRANT CONTROL ON SCHEMA::BOBO TO [<BOBO team AD group>];
GRANT CREATE TABLE TO [<BOBO team AD group>];
GRANT CREATE VIEW TO [<BOBO team AD group>];
GO


What are valid usage scenarios for HEAP tables?

The only valid uses are for

staging tables used in import/export/ETL processes.
ad-hoc, temporary and short term backup of tables using SELECT * INTO..

Staging tables are typically quite flat and truncated before/after use.

Note that a clustered index is typically few small compared to the data size: the data is the lowest level of the index structure.

Heap tables also have problems. At least these:

can not be defragmented to reduce space on disk. This matters because used data pages will be scattered throughout the MDF for example, because data has no "order" from the clustered index
non-clustered index now point to the row, not the clustered index entry. This affects performance: Need for reaching data through clustered index with a non-clustered index

Also see

http://www.sqlbadpractices.com/heap-tables/ (Bad practice)
http://msdn.microsoft.com/en-us/library/hh213609.aspx (MS's recommendations)
http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2930)-fixing-heap-fragmentation.aspx (Paul Randal's DBA myths)


Which SQL Server product does version 622 represent?

622 is an internal intermediate version, never released. How come you have an 622 version DB? The explanation is simple actually: an aborted upgrade. Look at the sequence Aaron posted:

Converting database 'x' from version 611 to the current version 655.
Database 'x' running the upgrade step from version 611 to version 621.
Database 'x' running the upgrade step from version 621 to version 622.
Database 'x' running the upgrade step from version 622 to version 625.

If the last step above crashes, your database is a version 622 now (each step commits). This version can only be attached to a SQL Server 2008 (or newer) to continue and finish the upgrade. It seems to me you had a SQL Server 2005 DB (v. 611) you attached to a SQL Server 2008 or higher, it started to upgrade but the upgrade failed at step 622->625 and now you're trying to attach it back to the SQL 2005 instance. It can't be re-attached, your only chance is to try to move forward. If the upgrade continue to fail, you will have to start again from an original SQL 2005 database (a copy of the files, a backup...).

Why would a database upgrade fail? Same reason any operation can fail: CTRL-C, power outage, corrupted original database, hardware problems, bugs in the product. Knowing what the upgrade step 622-625 does, my moneys are on a corrupted source DB. Recovering from a failed upgrade is a bit more tricky. As usual, having a good, tested, backup and restore strategy pays off.