Partitioning a really large table, or splitting in multiple tables?

I doubt anyone can tell you whether the performance of your home-grown system will be better or worse than the performance of MySQL built-in partitioning.

In general, some thoughts:

Using MySQL's partitioning, you would get the benefits of any improvements simply be upgrading to a newer version; in most cases, your code would probably not need to change.
MySQL's partitioning might allow more flexibility in what you already do, making some things that are impractical today work adequately.
You've got to support your home-grown version yourself, and make it work with other changes to your system.
Your home-grown version (presumably) works exactly how you need it to, and works adequately well with the rest of your application(s).
If you have a special need, you have the option of modifying your home-grown system; while modifying MySQL is possible, it's a bit more involved.
To move your current system to MySQL's partitioning would probably require some code changes in your system, and might require tuning of multiple queries (to avoid hitting more partitions than necessary).

Therefore:

It might be worthwhile to test switching your table to MySQL partitioning, to get an idea of how it performs with your data (switching and seeing a major performance degradation would be a bad thing).
In the long run, you would probably want to try to switch to using MySQL's partitioning (as it's likely to get better with time, and maintaining your solution could grow more complicated with time)...
...unless it turns out that some features of your home-grown solution are absent in MySQL partitioning and are critical to your application(s).
In any case, I'd allow for at least as much time and effort in switching to MySQL partitioning as you spent in developing and perfecting your own solution. It may be much simpler, but you may have inadvertently built other parts of your system around your solution that would have to be reworked to function with MySQL partitioning.

This is all completely generic advice - I don't know anything about MySQL partitioning (or about your solution, for that matter). I have built some system where we created our own solution instead of using existing products, though. I know that sometimes they work much better for your needs - but they do require maintenance, and switching to using a tool less tailored to your specifics can eventually save you that maintenance time, but can cost you in terms of flexibility, and can take a while to understand as well as you understand your solution.

If your solution is low-maintenance and works well for you, with no major performance concerns, I'd be inclined to maintain it for now at least. However, given the time to explore and the fact that there may now be a built-in solution as good or better than what you have, I'd want to check it out and see if it'd work for you.


Your second option is a better solution. Add a new column to your tickets table, called ticket_concert_number. The logic to begin at 1 for each new concert is something that's probably best controlled in your application. You should also have a column called concert_id so you know which concert the ticket is for.

If you create a new table for each concert, you'll quickly fill your database with many many tables with relatively few records. This could lead to performance problems (when compared to a single table that's properly indexed). And if the table structure changes, you need to decide if you're going to propagate the structural changes to all of the existing tables, and if you don't, you'll need to be aware of the fact that you will have to query all the old tickets tables differently depending on what "version" of the original structure they were based on. And then you also have to consider how your application will even find the correct table, when the tables are being generated for every new concert.


Optimize wildcard searching for a NVARCHAR(MAX) column

Yes and no.

It is the best built in way.

You COULD handle it on application layer and ALSO store the text in something made for that (i.e. a search engine style like Lucene).

You COULD roll your own. Trigram/Trigraph style. Will use a lot of programming and a lot of space, but will make queries faster.

One way to get an index seek for a leading %wildcard by Aaron Bertrand

Trigram Wildcard String Search in SQL Server by Paul White

Have what you need to get started there ;)


How can we execute a log file from pg_xlogs directory postgresql?

No, it doesn't work like that.

The transaction logs don't contain queries. They contain the changes to disk blocks - heap and indexes - along with records of transaction ID allocations, commits, etc. It's all very low level, and it makes absolutely no sense to try to apply it to another unrelated PostgreSQL instance.

You can use the transaction logs (WAL) for replication, per the PostgreSQL manual.

PostgreSQL also has the beginnings of logical replication functionality. Currently that's mainly accessible via 3rd party tools like pglogical. There are trigger-based alternatives like Londiste too.


Should SQL Server Always-On Availability groups have storage deduplication enabled?

storage engineers want to save on storage and provide one big storage array for
  implementing synchronous AG replicas.

Dump. The whole idea behind using an AOAG - instead of simple failover - is to have 2 storage backends. Because those ALSO SOMETIMES FAIL (and yes, even a big HP EVA can fail). The whole concept is that:

You can use local cheap high performance storage, not slow storage arrays. And yes, I mean slow - I can get a 2HE server these days that has literally hundred gigabyte / second IO locally on quite a lot of PCIe connected NVM drives. For a lot less cost.
You have redundant storage so you rule out any storage backend failure, because regardless what you do, that one SOMETIMES also fails. Been there, seen that. Rare but it happens.

Puttig an AOAG on an XIO already is eliminating a lot of the benefit (cost), but then using it as shared storage.... pretty much totally kills the benefit. Not EXACTLY totally because I can come up with some rare edge cases where it still is better (corruption wise), but really... not a lot left compared to normal clustering.


How to connect client applications to SQL SERVER 2012 cluster?

Neither of those should work. If it's a named instance, it should be:

DATA SOURCE=ClusterName\SQLInstanceName;

If it's a default instance, just:

DATA SOURCE=ClusterName;

You are always connecting to an instance of SQL Server, whether it is clustered or not. The cluster will know which node in the cluster is currently hosting the instance, and this should not really be a concern of yours, at least in terms of the connection string - that's kind of the whole point, a failover to a different node in the cluster should not require your application to change anything.


PostgreSQL: How to get a sum of values and the total rows that went into each value?

I have created the following sample DDL structure in order to work the necessary queries:

CREATE TABLE my_data
(
    my_data_id  SERIAL,
    theme_id    INT NOT NULL,
    priority_id INT NOT NULL,
    my_value    INT NOT NULL,
    CONSTRAINT pk_my_data PRIMARY KEY (my_data_id)
);

CREATE TABLE theme
(
    theme_id  SERIAL,
    name      CHAR(6) NOT NULL,
    CONSTRAINT pk_theme PRIMARY KEY (theme_id)
);

CREATE TABLE priority
(
    priority_id  SERIAL,
    name         CHAR(6) NOT NULL,
    CONSTRAINT pk_priority PRIMARY KEY (priority_id)
);

Then, inserted the following test data into the respective tables:

INSERT INTO theme (name)
VALUES ('first');
INSERT INTO theme (name)
VALUES ('second');
INSERT INTO theme (name)
VALUES ('third');

INSERT INTO priority (name)
VALUES ('high');
INSERT INTO priority (name)
VALUES ('medium');
INSERT INTO priority (name)
VALUES ('low');

INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (1, 1, 6);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (1, 2, 5);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (1, 3, 8);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (1, 1, 3);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (1, 2, 4);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (1, 3, 11);

INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (2, 1, 7);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (2, 2, 3);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (2, 3, 9);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (2, 1, 1);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (2, 2, 13);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (2, 3, 10);

INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (3, 1, 1);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (3, 2, 8);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (3, 3, 2);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (3, 1, 6);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (3, 2, 5);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (3, 3, 4);

And, finally the concrete SELECT statement with its corresponding aggregate functions that provide your desired result set:

SELECT theme_id,
       priority_id,
       SUM(my_value) AS values_sum,
       COUNT(*)      AS total_rows
  FROM my_data
GROUP BY theme_id, priority_id
ORDER BY theme_id ASC, priority_id ASC;

In this way, we can see that you almost had it with your second query but, as @a_horse_with_no_name has pointed out, you needed to replace count(id) with count(*), so that you could get your correct data. I have also included the ORDER BY clause for sorting the columns.

By means of this SQL Fiddle sample, you can confirm that the results yielded by SUM and COUNT are correct. As you can see I've added theme and priority, which are relevant tables for this query, along with the required test data.


Need to optimize simple SELECT query for InnoDB table with 2million rows

First, what you need to focus on are the three fields in the query

id
created_at
person_id

The index you have (created_at,person_id) will make the query do an index scan across all the days of created_at after CURRENT_DATE looking for the person_id.

SUGGESTION #1 : You will definitely need a different index

MyISAM

If login_events is MyISAM, this is the index you need

ALTER TABLE login_events ADD INDEX person_date_ndx (person_id,created_at,id);

This changes the query because the query will look for the specific person_id and scan all days for person_id 1 only. The reason id is included in the index ? The query will retrieve the id from the index only file rather than the table. That way, all 3 fields are retrieved from the index file instead of 2 fields from the index and 1 from the table.

InnoDB

If login_events is InnoDB, this is the index you need

ALTER TABLE login_events ADD INDEX person_date_ndx (person_id,created_at);

The reason I recommend this is the same, but you do not need to include id. Why? All index pages include an index point back to the clustered index so retrieval of an index will intrinsically access the row anyway, thus accessing id. Adding id to the index would simply be redundant.

SUGGESTION #2 : Change the Date Comparison

From the expression

DATE(created_at) >= DATE(CURRENT_DATE)

I can tell that created_at is either DATETIME or TIMESTAMP.

The expression forces the query to convert every row's DATETIME value of created_on into a DATE.

Therefore, instead of

SELECT login_events.id
FROM login_events
WHERE (
  DATE(created_at) >= DATE(CURRENT_DATE)
  AND person_id = 1
)
LIMIT 1

express the date comparison as a time comparison starting from midnight of today

SELECT login_events.id
FROM login_events
WHERE (
  created_at >= (DATE(NOW()) + INTERVAL 0 SECOND)
  AND person_id = 1
)
LIMIT 1

CAVEAT

Since the table is so small, either storage engine would be fine. I would give the edge to MyISAM.

Give it a Try !!!


Tool to test RDBMS SQL dialect for ANSI SQL standard compliance

Finally after looking at SQLite unit tests and postgreSQL unit tests we decided to use these tools

https://github.com/anse1/sqlsmith
https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki


According to the link to a Heroku discussion here: Howto: Restore a single table to an existing database, the way your're doing it is always a destructive action.

A better solution proposed is to dump the table locally, and import the data remotely.

You could do a single dump of the table using a command like this:

pg_dump -U <myuser> <mydb> -t <mytable> -f <my_table>.sql

And then try to restore it like this:

psql -U <myuser> <mydb> -h <my_heroku_url> -f <my_table>.sql

If you need a more intricate restore, I would recommend using pg_dump from the originating database with the -Fc flag, like this:

pg_dump -U <myuser> <mydb> -Fc -f <my_db>.dump

And then using the pg_restore utility to restore just the parts you need.

Further documentation for pg_restore is here: http://www.postgresql.org/docs/9.4/static/app-pgrestore.html

And for pg_dump: http://www.postgresql.org/docs/9.4/static/app-pgdump.html

Hope that helps. =)


Is there a way to have multiple error messages displayed with RAISERROR conditionally?

One method is to build an error message string and issue a single RAISERROR conditionally after all validations are done.

DECLARE @ErrorMessage varchar(2047);
IF @ForeCastYearCheck = 'false'
    SET @ErrorMessage = @ForeCastYearCheckError;
IF @ForeCastQuarterCheck = 'false'
    SET @ErrorMessage = COALESCE(@ErrorMessage + ',' + @ForeCastQuarterCheckError, @ForeCastQuarterCheckError);
IF @ForeCastComboCheck = 'false'
    SET @ErrorMessage = COALESCE(@ErrorMessage + ',' + @ForeCastComboCheckError, @ForeCastComboCheckError);
IF @ErrorMessage IS NOT NULL
    RAISERROR(@ErrorMessage, 16, 1);


A PIVOT can be written as a CASE statement. So for your example:

SELECT
    sku,
    MAX (CASE WHEN stock_date = '2016-06-01' THEN 'Y' ELSE 'N' END) AS [1],
    MAX (CASE WHEN stock_date = '2016-06-02' THEN 'Y' ELSE 'N' END) AS [2],
    MAX (CASE WHEN stock_date = '2016-06-03' THEN 'Y' ELSE 'N' END) AS [3],
    ...
FROM
    dbo.mytable
GROUP BY sku

Note that this can (and should) be dynamically generated.

(Source: https://avaldes.com/pivot_using_case/)


MySQL keeps getting “connect” commands 100X per minute!

I would check / verify if there is a init_connect option in your my.cnf, if so that would be a great place to start to see what the var is set to.

Also:

While the server is running, in terminal, run:

ps -aef | grep mysql

and identify the user(s) that are running processes up against mysql. If you end up seeing a sort of trend in the processes than grep out the parent processes for each child process to get to the route PID that is engaging.

Also:

If this MySQL instance is not in production, I would suggest a server restart as well. We all know MySQL can be finicky and in many cases a clean restart clears up buggy issues.

Side note: Is there a proxy in front of your MySQL server? If so, I would look into this layer just as intensive as the database.

Gluck! Cheers.


How to find number of user connections to an Oracle database between a specific time period?

That is what we have auditing for. Enable auditing, for example:

alter system set audit_trail=db scope=spfile;

You need to restart the database for this setting to take effect.

Audit logins:

audit create session;

If you created your database with DBCA, both the above are automatically enabled in 11.2.

Then you can query and browse logins in DBA_AUDIT_SESSION.


Community wiki answer:

Check for triggers, and DDL triggers on the database.

The error message gives the name of what to look for: procedure DatabaseAudit line 47.


How do I drop the TIME portion from DATEADD?

Well, assuming that someone is holding a gun to your head, forcing you to not change the data type to something more sensible and logical:

UPDATE CCCINC.dbo.Employees
SET DateOfBirth = CONVERT(CHAR(8), DATEADD(DAY, ABS(CHECKSUM(NEWID())
  % 41975), '1900-01-01'), 112);

Strong, strongly, strongly suggest you change the data type of that column to use the right data type (DATE). Then you can just use your original query and not worry about the "format" at all. Format is what you worry about when you present the data, not when you store it.

Bad habits to kick : choosing the wrong data type
Bad habits to kick : mis-handling date / range queries


Whole row
You don't mention JSON, but it seems you are after to_json(b)() or similar. Example:
SELECT t.*, to_json(u) AS author
FROM   trips t
JOIN   users u ON u.id = t.created_by;

Or, without involving JSON:
SELECT t.*, u AS author
FROM   trips t
JOIN   users u ON u.id = t.created_by;

Then author is a ROW value, but without column names - that information is only in the definition of the row type.
Selected columns
You later commented:

... if I want id, email and name only from users table?

JSON:
SELECT t.*
     , json_build_object('id', u.id, 'email', u.email, 'name', u.name)) AS author
FROM   trips t
JOIN   users u ON u.id = t.created_by;

Or ROW value:
SELECT t.*
    , (SELECT x FROM (SELECT u.id, u.email, u.name) AS x)) AS author
FROM   trips t
JOIN   users u ON u.id = t.created_by;

See:

Return as array of JSON objects in SQL (Postgres)
Select columns inside json_agg
Raw SQL queries in a REST Api


Use TIMESTAMP, not DATETIME.  Then set each client to the timezone where it resides, or to UTC for the main server.

That way, the conversions will be done automatically.  That is, no conversion code is needed.


Table design - one table or hundreds of small ones

Another solution is one table per quantity.

+-----------+------------+-----------+
| device ID | date       | quantity1 |
+-----------+------------+-----------+
|         A | 2020/06/03 |        10 |
+-----------+------------+-----------+

+-----------+------------+-----------+
| device ID | date       | quantity2 |
+-----------+------------+-----------+
|         B | 2020/06/04 |        50 |
+-----------+------------+-----------+

+-----------+------------+-----------+
| device ID | date       | quantity3 |
+-----------+------------+-----------+
|         A | 2020/06/03 |       100 |
+-----------+------------+-----------+
|         B | 2020/06/04 |       100 |
+-----------+------------+-----------+

The wide table with NULLs can be re-created by joining these tables. Joseph's tall table can be created by unioning them.

In terms of storage, NULLs are quite efficiently represented. They need not take up the full byte count of the column's declared data type. Don't be afraid of storing NULLs.

If space overhead is a concern then likely the one-column-per-quantity will have the best key-to-data ratio, followed by table-per-quantity then the single table.

If you're using compressing or columnar storage this can be more disk-efficient, too, but depends on the storage engine. Adjust the table design and data sorting to best suit the engine.

My suggestion is that you think about the use you will make of the data and consider how queries will be written for each design. For a given device & date do you need several quantities or one only? Each design can support all queries, just that some designs make the queries simpler to write and faster to run.

Whatever the table design additional consideration must be given for reporting on devices that recorded no quantity on a given day or days where a device is absent.


Here's one attempt (this is not a day to day activity for me, so I may be doing some weird errors below):

A and B are clearly candidate keys of R. Therefore C is the only non-prime attribute of R

R is in 3NF iff:

a) R is in 2NF

b) Every non-prime attribute of R (C) is non-transitively dependent on every superkey of R.

The superkeys of R is (A, B), (A, C), (A, B, C)

By reflexivity (A, C) -> C AND (A, B, C) -> C
By composition of A->C and B->C we get (A, B) -> (C, C), i.e. (A, B) -> C

C is therefore non-transitively dependent on every superkey of R, and hence R is in 3NF.


SQL Server - Combining Bulk Insert with Manual value

A possible solution is to use OPENROWSET

Assuming a csv file (emp.txt):

User1,100,Dep1
User2,1000,Dep2

Assuming a format file (format_file.xml):

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="Salary" xsi:type="SQLDECIMAL"/>
  <COLUMN SOURCE="3" NAME="DepartmentName" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

--demo setup
drop table if exists employees;
drop table if exists departments;
go
CREATE TABLE Employees (
    NAME NVARCHAR(20)
    ,Salary DECIMAL
    ,DepartmentID INT
    )
GO

CREATE TABLE Departments (
    DepartmentID INT
    ,DepartmentName NVARCHAR(20)
    )
GO

INSERT INTO Departments
VALUES (1,'Dep1')

INSERT INTO Departments
VALUES (2,'Dep2')

--solution
--Insert data into Employees table
INSERT INTO Employees
SELECT NAME
    ,Salary
    ,DepartmentID
FROM OPENROWSET(BULK 'C:\testdata\emp.txt', FORMATFILE = 'C:\testdata\format_file.xml') emp
JOIN Departments dep ON (emp.DepartmentName = dep.DepartmentName)

--verify
select * from Employees

| NAME  | Salary | DepartmentID |
|-------|--------|--------------|
| User1 | 100    | 1            |
| User2 | 1000   | 2            |


You have three(3) options
OPTION #1 : Redo the XtraBackup with the Proper Preparation Steps
The preparation step was meant to carry the logs along for the ride. You must go back and run the backup again.
OPTION #2 : Play with Forced Recovery
Rather that reinvent the wheel, please see my post
The InnoDB log sequence number is in the future
OPTION #3 : Run Multiple rsyncs
This is rather risky, but you can run rsync multiple times against the live MySQL instance until two consecutive rsyncs take the same amount of time. Then, shutdown mysql on the source, run one more rsync. Then, after due diligence, startup mysql on the new server.
See my earlier post Any better way out of MySQL InnoDB log "in the future"?
Give it a Try !!!


How to convert _id field of all existing documents from type String to Number

db.getCollection('test').find().forEach(function (doc) {
    db.getCollection('test').remove({ _id : doc._id});
    tempId = new NumberLong(doc._id);
    doc._id = tempId;
    db.getCollection('test').save(doc);
    }
);

This will do. You may want to add error handling as per your use case. Also make sure you have backup before migrating.


mysql general_log only shows a hex number instead of SQL?

Use this query to get the argument as text:

select a.*, convert(a.argument using utf8) from general_log a;


Split value from a date range in to individual values per date

WITH cte AS ( SELECT {columns list}, start_date as single_date
              FROM source_table
            UNION ALL
              SELECT {columns list}, DATEADD(day, 1, single_date)
              FROM cte
              WHERE single_date  < finish_date )
SELECT *
FROM cte;

Example fiddle


Typically caused by the segment attributes of the tables.

You can use TRANSFORM=SEGMENT_ATTRIBUTES:N, so the tables will be created with default options.

You can use TRANSFORM=SEGMENT_CREATION:N as well, so your segments will not be created at all, unless deferred segment creation is disabled in your database.

TRANSFORM

DEFERRED_SEGMENT_CREATION


Using MySQL ver. 5.5.55 in a production env

There are four reasons to upgrade the software:

Significant bug was removed
Security breach was removed
Hardware have
to be changed
Database structure was modified dramatically and
require some features not available in the older versions.

Updates performed just for software actuality are inappropriate in the production.

phpMyAdmin is well-known as unsecure software with lot of breaches so its location should be at least password-protected and restricted to the secure subnet(s).


Why doesn't this invalid subquery raise a syntax error?

This is the same as:

(
  SELECT id AS delete_test_backup
)

In other words, you have merely assigned a column alias to id, and there is no reference at all to a table called delete_test_backup.

According to the ANSI standard, what SQL Server is supposed to do in this case - when it has a subquery and does not find id at that scope - is traverse to the outer scope(s) until it finds one. If it does, it assumes that's the one you meant.

See:

Best practices : Properly referencing columns
KB #298674
Connect #735178
Connect #624370
Connect #392492
Connect #362016
Connect #265772
Connect #772612

  * Microsoft decided to eradicate all of Connect instead of archiving the content, and also disappeared the knowledge base article for some reason.

To avoid the problem, always use table aliases, which allow you to be 100% explicit about which table a column comes from (not naming every key column id helps, too). Of course you can leave these prefixes out accidentally, just like you left out the FROM, but the following syntax would have failed as you expect, and most other omissions would have left you with a parsing error rather than accidental execution:

(
  SELECT x.id FROM delete_test_backup AS x
)


The error message is telling you that you can't use a set returning in the SELECT list. You need to put it into the FROM clause:

SELECT array_agg(t.k)
FROM table tbl, jsonb_object_keys(tbl.data) as t(k)
WHERE tbl.some_id = 3
GROUP BY tbl.some_id;


Recommended model for application level event logging

Your basic model is fine.  It gives you a standard, normalized intersection (many to many) of events of specific types associated with particular users.  Without knowing more about your intentions or requirements, it's difficult to offer any criticism of the model.

I would make these observations:

You can use events.id for your PK on the events table if you like.  That is probably the way that I would do it.  You have a choice, however, to consider making the PK of event the combination of events.user_id and event_type_id.  This will be just as good, possibly slightly better, if you will have no other tables directly referencing the events table.  The potential advantage has to be considered in light of how you plan to manage the space for this table and how fast events will accumulate.  Using events.id as a clustering index could create a hotspot.  On the other hand, using the compound key could create long delays in your inserts if you don't manage your page fills properly.
You've mentioned that the model is simplified, but you haven't said in what ways.  One thing that I would say is that the model as presented gives very little information about events.  You know who did what (and when) - but the what is limited to a static description string.  Other event logging systems I have seen (and built) include places to record specific details of the object of the event.  If I were you I would ask myself questions like:

Would it help if there were a text field on the events table that could be filled with specific details of what happened during the event?
Would it help to have columns that record which instance of another item were impacted by the action (i.e.: events.object_type and events.object_id)?
Are there complex events that have multiple parts which should be grouped into a single logical event?  Would having a field that allows multiple events records to be tied together be helpful?

If you can tell us more about how you plan to use the events, from the perspective of how the information is meant to be helpful, I might be able to give you more concrete advice.


Check if two tables have the same structure (are compatible)

Having example schema like:

create table table1 (
  id integer,
  txt text,
  col1 integer,
  col2 integer);

create table table2 (
  id integer,
  txt text,
  col1 text,
  colx integer);

and assuming you don't care about column names you can check potential conflicts in union-ed columns with query:

(select data_type, ordinal_position
from information_schema.columns
where table_name = 'table1'
except
select data_type, ordinal_position
from information_schema.columns
where table_name = 'table2')
union all
(select data_type, ordinal_position
from information_schema.columns
where table_name = 'table2'
except
select data_type, ordinal_position
from information_schema.columns
where table_name = 'table1'
)
order by 2;

The result will show offending column's position in tables' structure and data type.

data_type   ordinal_position
integer     3
text    3

If you have this shortened list you can check details in information_schema.columns disctionary to check details about each column.


Storing only changes will be a good idea, because storing older documents as separate documents or attachments to the final revision of database will create an overhead to database server.

When ever you change a key value in your document, add a new key named _h_i_s_<key_name>. In the newly created( or created during last update), append objects like below after every edit/update:-

{
key_name: "Hello",
_h_i_s_key_name:{time_of_update:value_of_key_name_before_update},
....
}

or

    {
    key_name: "Hello",
    _h_i_s_key_name:[{time:time_of_update,value:value_of_key_name_before_update}, {time:time_of_last_update,value:value_of_key_name_before_last_update}],
    ....
    }

This approach will save a lot of disk space and replication bandwidth in long run.


What my colleagues are attempting to say in the comments, is that, they / we are making an assumption that the issue is due to the account / sql login not existing on your new server (the one where you restored your database). The most likely cause of what is going on is that since the login / sql account you use on your original server doesn't exist on the new server it's causing your application / connection to time out.

No biggie... easy fix with two options that come to mind to help resolve the issue.

First option - Manually create a new user with the same name and password then run the following script on the database you restored:

sp_change_users_login 'update_one', '[yourDBuser]', '[yourSQLlogin]'

This will connect the newly created SQL Server login to the user in the database you are having issues with.

Second option (I prefer this one) - Install / create the sp_help_revlogin stored procedure on your original server. You can find the script here.

Once it's installed execute the following:

sp_help_revlogin '[yourSQLlogin]'

This will generate an output like so:

-- Login: yoursqllogin
CREATE LOGIN [yoursqllogin] WITH PASSWORD = 0x0200EF889B2ECBF4A17B836A62AE80BC0F718EBF117C73697892B112C3744D04AB18490E8C666C1154F42BC3D0C49346843032AFBFBEBA28CFE018AB1FED25EF2830A5F046E9 HASHED, SID = 0x01, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF

Copy and paste the output onto your new server, and you will be rockin' and rollin' in no time.


What happens if two process try to REFRESH MATERIALIZED VIEW CONCURRENTLY at the same time?

As mentioned in this answer, "REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock" on the table. Following the crumb trail to documentation we can read that an EXCLUSIVE lock on a table "allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed". In the same paragraph we can see that "EXCLUSIVE conflicts with ... EXCLUSIVE", meaning that another REFRESH MATERIALIZED VIEW CONCURRENTLY statement, which requests the same EXCLUSIVE lock, will have to wait until the earlier EXCLUSIVE lock is released.

If you want to avoid waiting for this lock for an undefined period, you may want to set the session variable lock_timeout to a sensible value.


Best approach for querying multiple tables

I'm not sure why you join your dependent tables with the distinctValuesView. Is it to rule out sourceValues that does not exist in Source? In that case you may try to use EXISTS instead:

SELECT q.SourceValue, SUM(q.Count) as Count
FROM (
    SELECT dt1.SourceValue, COUNT(*) AS Count
    FROM DependantTable1 dt1
    WHERE EXISTS (
        SELECT 1 FROM Source s
        WHERE s.SourceValue = dt1.SourceValueColumn
    )
    GROUP BY dt1.SourceValue

    UNION ALL

    ...
) q
GROUP BY q.SourceValue

Note that I changed UNION to UNION ALL. I assume that if you have 4 occurrences of 'value1' in DependantTable1 and 4 occurrences in DependantTable2 the sum should be 8. If you use UNION the result will be 4

If most of your SourceValue exists in Source, you may try to apply the union first:

SELECT q.SourceValue, COUNT(*)
FROM (
    SELECT dt1.SourceValue
    FROM DependantTable1 dt1
    UNION ALL
    SELECT dt2.SourceValue
    FROM DependantTable1 dt2
) q
WHERE EXISTS (
    SELECT 1 FROM Source s
    WHERE s.SourceValue = q.SourceValueColumn
)
GROUP BY q.SourceValue


Solaris 10 - Why shell limits needs to be increased to run oracle database?

Oracle might be binary, but it will still initiate system commands which in term could involve shells. On UNIX systems, the ulimit command controls the limits on system resource, such as process data size, process virtual memory, and process file size. It is also mostly the users processes affect parent processes. So the parent processes manage the users. Normally ulimit is inherited from the main user, so allowing you to set a value for Oracle might be in the interest of your system administrator. In Solaris it is ulimited. ulimit -Ha and ulimit -Sa will show the hard and soft limits. Good rule of thumb is to ensure the soft limits do not exceed the hard. But unlimited for a dedicated Oracle server would be better.


This may be Oracle client/server version compatibility issue.

  From java.sql.SQLException: ORA-28040: No matching authentication protocol

    This happens because you are using a non compatible version of JDBC driver.

    To resolve this issue, make sure that you are using the latest version of Oracle JDBC driver, if not use JDBC 12c or higher versions.


Does the mysql parameter "max_connections" apply for unix socket connections?

This limit applies to the total TCP socket connections and Unix socket connections, combined, so connecting via the Unix socket won't help you if your connection count reaches the configured max.

However, the slot reserved for a user with SUPER privilege is not a single slot in the way your question implies.  Any time the server's current client thread count is equal to the value of max_connections, one more user with the SUPER privilege will be allowed to connect, regardless of how many SUPER connections are currently already established.

So the max+1 allowance refers not to a single slot that can be occupied and whose occupancy is remembered, but rather to an exception in the code that bypasses the logic that denies new connections connections when current == max and new attempt comes from a user with super, each time that condition is encountered.

This is one good reason, among others, that your application should never use an account with SUPER.


Shrinking the database will allow for you to give back some space to the O/S, but that space comes at a performance cost as the shrink activity fragments the database. You can fix this by rebuilding your indexes, but that usually causes the space to be reclaimed. And the ides that a shrink would be a solution is false, because you are not addressing the growth issue itself, you are merely trying to put a bandage on a sucking chest wound.

Placing your files on disks that can be expanded easily is your best bet. External storage works well (or a SAN, if possible). You cannot use a UNC path for your database files, but you could use DFS to mount a drive and point to a network location and everything would work fine.

HTH


How to design database for monitoring the time trends of article tags?

Having a comma-separated list of tags would save on the number of rows written to a database, but would make querying far more computationally expensive. Typically this sort of table would have one record per tag, as this would allow for simpler queries to be written. A structure similar to something you'd find in a blogging database would work. For example:
TagHistory
  article_id
  tag
  flat
  posted_at
  board_name
  author

Then you would have records like this

article_id
tag
flat
posted_at
board_name
author

123
pokémon
pokemon
2021-05-01 00:00:00
games
BillyBob

129
PokEmon
pokemon
2021-05-01 00:07:41
games
Jackie

129
cards
cards
2021-05-01 00:07:41
games
Jackie

129
Deck
deck
2021-05-01 00:07:41
games
Jackie

147
MewTwo
mewtwo
2021-05-01 00:11:12
games
Archibald

The flat field would contain a lower-case tag value with all accents and other non-alphanumeric components removed so that values like pokémon, PokEmon, and Pokemon do not appear as three separate values.
With this sort of design, you could then begin writing queries that could rank tag usage over a period of time. If you were interested in the five most popular tags from last week, you could write a query like this:
SELECT WEEK(posted_at) as week_no, flat, COUNT(article_id) as usages
  FROM TagHistory
 WHERE `posted_at` BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL DAYOFWEEK(NOW()) + 6 DAY), '%Y-%m-%d 00:00:00')
                   AND DATE_FORMAT(DATE_SUB(NOW(), INTERVAL DAYOFWEEK(NOW()) -1 DAY), '%Y-%m-%d 23:59:59')
 GROUP BY week_no, flat
 ORDER BY usages DESC
 LIMIT 5;

Alternatively you could do it by board, or by author, or across a period of weeks or months. The sky is the limit and the queries are simpler to write. If there is a regular means of presentation, this data could be collated every week and written to a summary table that would save you the hassle of looking up and calculating tag usage over time.
For example:
TagHistorySummary
  year
  week_no
  flat
  usages

Then if you wanted to present a chart showing the popularity of the pokemon tag for all of 2020 (similar to Google's Ngram Viewer), you could write a query like this:
SELECT week_no, usages
  FROM TagHistorySummary
 WHERE flat = 'pokemon' and year = 2020
 ORDER BY week_no;

Note: Using year as a field name would be a poor choice given it's a reserved word by most database engines. Do not take these examples as verbatim.

This data could then be used to populate a simple line chart:

Hmm ... who knew that Pokemon was a thing before WWI? I sure as heck didn't.
This isn't the only way to accomplish your goal, of course. Experience has shown that it is one of the simpler ways, though, particularly when working with data that is supplied by people.


How to design to avoid two-way reference between two tables

Sounds like you may need a self-join:

Get rid of the relatives table entirely
Define the main_contact as a foreign_key to the id column of the member table.

Or depending on the relationship a many-to-many resolver table in the middle:

Get rid of member_id from relatives
Get rid of main_contact
from from member
Add a new table member_relative with the
columns member_id, relative_id and a column to indicate
relationship_type.


You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:

BACKUP LOG
    lagerdb
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn'
WITH NAME = N'lagerdb logs'

Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.


Server permissions of an activation stored procedure of a Server Broker queue

See Signing an activated procedure for an example of how to properly sign an activated procedure exactly so it it can leverage VIEW SERVER STATE privilege from an activated procedure. The steps are:

inspect the procedure code to ensure that you trust it
change the procedure to have an EXECUTE AS OWNER clause (without EXECUTE AS, even if the module is signed, the principal will not have access outside the host database because of how Service Broker executes the activation procedure)
create a certificate with a private key in your app database
sign the procedure with the private key of the certificate you created
drop the private key of the certificate (to prevent it from ever being used again)
copy the certificate into the master database
create a login from the certificate
grant AUTHENTICATE SERVER to the certificate derived login
grant any additional privilege required by the procedure (e.g. VIEW SERVER STATE) to the certificate derived login


Migrate data from MySQL to SQL Server, database names differ

Yes you can migrate database and rename database name.


Why is "INSERT INTO table;" a syntax error?

The syntax rules require at least one target column to be specified. If you want to let the defaults "kick in" for all columns, you can use the default values clause.
INSERT INTO people DEFAULT VALUES;

another alternative is to provide one column with the DEFAULT clause:
insert into people ("timestamp") values (default);


Unable to create Functions/Store Procedure Azure mySQL

I seem to have found the awnser - I have had to change the below setting in the server parameters.


Without rewriting this query, is there a way to force the query optimizer to better estimate the amount of resources required, e.g. with query hints?

This query, as written, must compare every pair of rows, and the cost estimate is reasonably accurate.

On a supported version of SQL Server you might get a parallel plan, or there is an undocumented query hint that will force a parallel plan.

select t1.itemid, t2.itemid
from #temp T1
inner join #temp as t2
  on t1.itemid like '%' + t2.itemid + '%'
  and t2.itemid <> t1.itemid
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))


How to apply patches in MySQL in any version

The concept of "applying patches" is not meaningful in MySQL unless you are building from source, and even then, it's not really a question that has an answer, since they don't release "patches."

What are you trying to accomplish?  What version are you running, now?  When you say "applying patches in MySQL 5.1.67," do you mean applying fixes found in 5.1.67 to a server of a previous version?

I ask that, because, at the time of this writing, 5.1.67 is still a very recent release and I would contend that "so many bugs" is not a phrase that describes this release at all.  It (5.1.67) is very stable and is, in fact, my current production release of choice for everything based on 5.1.

The fixes present in 5.1.67 cumulative from the beginning of 5.1.  To have these fixes, you have to have 5.1.67 because changes made to 5.1.67 would often depend on other changes made to intermediate versions.  Hence, you upgrade.

Within a release series (e.g., 5.1.x) the functionality of the server should not change after the series reaches General Availability, and the release notes will document any incompatible changes that are introduced.

Upgrading should not be a problem within a release series (although you should always have backups, of course).  It also should not be a problem going from series to series either, as long as you follow the instructions and don't try to skip over a major release series.


How are pages reallocated in a a data file after shrinkfile sql server

The shrink algorithm works exactly as you described it. A good description can be found in Paul Randal's blog: https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

  Why does this happen? A data file shrink operation works on a single
  file at a time, and uses the GAM bitmaps (see Inside The Storage
  Engine: GAM, SGAM, PFS and other allocation maps) to find the highest
  page allocated in the file. It then moves it as far towards the front
  of the file as it can, and so on, and so on.

In your example, Page5 ends up in Empty1.


Correct me if I'm wrong but as I understand it is that SQL Server uses a CLOCK algorithm for the cache- and userstores. Each clock has two hands, an external and an internal.

Yep, almost like what you have said but let me add something more. Cache and Userstores shares common costing and algorithm called as Least Recently Used(LRU) algorithm. This LRU mechanism has what is called as "clock algorithm". This algorithm has what is called as "hands". There are various eviction policies which work inside SQl Server to remove "old" entries from SQL Server caches and to help this SQL Server has 2 clock hands:

External: Implements Global policy
Internal: Implements local policy

Policy is something which you can consider as rule when dealing with removing old entries from caches.

  If a worker that accesses a cache notices that a cache is greater than a certain percentage of memory, the internal clock hand starts to free memory for that cache.

There is lot more that happens it is not like that internal clock will "only" free memory by removing old entries after total memory utilized is  greater than certain percentage, LRU and internal memory pressure is also taken into the account.

  What is the difference between last_tick_time and round_start_time? Is the cache not cleaned when the hand is moved?

last_tick_time - time when a clock hand moved last.

round_start_time - time when a clock hand started current round. The round which it did to sweep the caches.

  How many entries get removed with each tick of the clock?

There is no predefined value, after LRU algorithm has done its task other thread would see how many entries in cache are "old" and then would remove it. If there is memory pressure the value may increase and the sweeping may happen too often.

  Can somebody helps me understanding the working of this clock algorithm in SQL Server

Slava Oaks from Microsoft has already written a lot about it. I will quote from his blog

  In SQL Server 2005 we introduced new common caching frameworkAll caches except for Buffer Pool leverage it. The framework consist
  of set of stores and ResourceMonitor. There are three types of stores:
  cache store, user store  (the name here is related to internal usage
  of the store - it does not have to do anything with a user)  and
  object store. Both cache and user stores share common LRU mechanism
  and leverage common costing. Procedure cache is an example of a cache
  store and metadata cache is an example of a user store. Object stores
  are just pools of memory blocks and don't require either LRU nor
  costing. Network library leverage object store for pooling network
  buffers.

  Store's LRU mechanism is a rather simple - we use variation of
  thoroughly discussed in operating system's literature clock algorithm.
  As you remember clock algorithm has a hand. When hand moves it
  decreases a cost of an entry. Once cost of an entry reaches 0, the
  entry can be discarded from the cache. Cost gets reset whenever an
  entry is reused. When dealing with multiple caches a designer has to
  take into account global and local eviction policies. Global policies
  consider global picture and enable running of LRU, clock algorithm,
  across all the caches. In its turn, local policies look at the local
  resource consumption only and run LRU for a given cache only.

  In order to satisfy global and local policies SQL Server's stores
  implement two hands: external and internal (please don't confuse a
  clock hand names with types  of memory pressure. There is no
  connection!). External clock hand implements global policy and
  internal clock hand implements local policy. Even though every cache
  has its own external hand, external hands move "synchronously" - one
  at a time. Their task is to simulate global hand.

Additional Reading: The Clock Hands of Buffer cache


Storing application settings with different datatypes

The simplest way I can think of which keeps the types is the following: create as many columns in your application_settings table as many different types you have.  This means normally a boolean, a numeric, a date and a text field.  So your table would look like

CREATE TABLE application_settings (
      id serial PRIMARY KEY
    , setting_name text NOT NULL -- possibly UNIQUE
    , boolean_setting boolean
    , numeric_setting numeric -- or this can be an integer, too
    , date_setting date -- again, this can be timestamp, too
    , text_setting text
);

If you plan to keep only one row of each setting (no historical data, then set the setting_name field to UNIQUE.  And if you keep exactly one value in a row (which is what I'd expect), then you can add a check constraint like

, CHECK (
    (boolean_setting IS NOT NULL)::integer +
    (numeric_setting IS NOT NULL)::integer +
    (date_setting IS NOT NULL)::integer +
    (text_setting IS NOT NULL)::integer
    = 1
  )

Depending on how you pass the values into this table, you may or may not need some sort of validation as well.  This may involve checking setting names against the type of values provided and a list or a range of valid inputs.


Best Way to Index an INT Column Used for BitWise Queries

Index on computed (AKA virtual) column

Demo

with t(i) as (select 0 union all select i+1 from t where i<999)
select 1+t0.i+1000*t1.i as FLAGS into MyTable from t t0,t t1 option (maxrecursion 0)

alter table MyTable add flag_bit_2 as (cast ((FLAGS & 4) as bit))
create index MyTable_ix_flag_bit_2 on MyTable (flag_bit_2)

select count(*) from MyTable where flag_bit_2 = 1


Large MySQL table, inserts with primary key check

I think what you're looking is simply to use INSERT IGNORE. Forget about steps 1 and 2, just insert and ignore :)

  If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.


AWS RDS Postgres pg_hba.conf auth-method

From what I can gather, SSL is usually being used.  What varies is the level to which server certificates are verified.

Reading the guide from Amazon is helpful albeit lengthy.

Things to remember...

To determine if SSL is on (apparently this requires a plugin but works by default on AWS RDS):

  show ssl;

There is a ~/.pgpass file that automatically supplies passwords when using psql (this confused me for a while)
Full server certificate verification (requires the root cert from AWS):

  $ psql -h testpg.cdhmuqifdpib.us-east-1.rds.amazonaws.com -p 5432 "dbname=testpg user=testuser sslrootcert=rds-ca-2015-root.pem sslmode=verify-full"

SSL is still on with this command however (no sslmode,sslrootcert):

  $ psql -h testpg.cdhmuqifdpib.us-east-1.rds.amazonaws.com -p 5432 "dbname=testpg user=testuser"

This will not connect because root cert is not supplied:

  $ psql -h testpg.cdhmuqifdpib.us-east-1.rds.amazonaws.com -p 5432 "dbname=testpg user=testuser sslmode=verify-full"

I never established a non-SSL connection however it is possible to prevent non-SSL connections to a DB instance with a parameter on the server: rds.force_ssl


First date in a week, not exceed from the month, start from specific day

I hope to understand your question. You want translate a date to a customizable first day of week. I call it start_day_of_week. So if your start_day_of_week is greater than day_of_weeek you must subtract the difference otherwise you must first subtract seven day (i.e. translate to previous week) and then add the difference. For example:

if your date is Fri (day of week 6) and you want to start at Thu (day of week 5) you subtract 1 day
if your date is Fri (day of week 6) and you want to start at Sat (day of week 7) you must first subtract 7 day i.e. go to the previous week and then add 1 day.

I think it better to write a mysql function that takes as parameters a day, a customizable start_day_of_week and returns a date translated.

To begin, I write some test. I use a table with three columns. One for input date, and input start_day_of_week, and a column with the result date. So the date '2014-10-23' translates to date '2014-10-19' when the start_day_of_week is Sunday (1), but it translates to  '2014-10-17' when the start day is Friday (7).

use test;
-- (1 = Sunday, 2 = Monday, …, 7 = Saturday).
drop table if exists fixture;
create temporary table fixture (input date, result date, day_of_week tinyint);
insert into fixture values
('2014-10-01', '2014-10-01', 1),
('2014-10-03', '2014-10-01', 1),
('2014-10-09', '2014-10-05', 1),
('2014-10-16', '2014-10-12', 1),
('2014-10-23', '2014-10-19', 1),

('2014-10-01', '2014-10-01', 2),
('2014-10-03', '2014-10-01', 2),
('2014-10-09', '2014-10-06', 2),
('2014-10-16', '2014-10-13', 2),
('2014-10-23', '2014-10-20', 2),

('2014-10-01', '2014-10-01', 6),
('2014-10-03', '2014-10-03', 6),
('2014-10-09', '2014-10-03', 6),
('2014-10-16', '2014-10-10', 6),
('2014-10-23', '2014-10-17', 6);

The function is:

drop function if exists mydatef;
delimiter $$
create function mydatef (d date, my_start_day_of_week int) returns date deterministic
begin
  set @v := IF(my_start_day_of_week - DAYOFWEEK(d) <= 0,
               DATE_ADD(d, INTERVAL(my_start_day_of_week - DAYOFWEEK(d)) DAY),
               DATE_ADD(d, INTERVAL(my_start_day_of_week - DAYOFWEEK(d)  - 7) DAY));
  return
    IF(@v < DATE_FORMAT(d ,'%Y-%m-01'), DATE_FORMAT(d ,'%Y-%m-01'), @v);
END;
$$
delimiter ;

So to test the function I write a simple query:

mysql> select input, day_of_week, mydatef(input, day_of_week),
 if(result = mydatef(input, day_of_week), 'OK', 'ERR' ) as result from fixture;
+------------+-------------+-----------------------------+--------+
| input      | day_of_week | mydatef(input, day_of_week) | result |
+------------+-------------+-----------------------------+--------+
| 2014-10-01 |           1 | 2014-10-01                  | OK     |
| 2014-10-03 |           1 | 2014-10-01                  | OK     |
| 2014-10-09 |           1 | 2014-10-05                  | OK     |
| 2014-10-16 |           1 | 2014-10-12                  | OK     |
| 2014-10-23 |           1 | 2014-10-19                  | OK     |
| 2014-10-01 |           2 | 2014-10-01                  | OK     |
| 2014-10-03 |           2 | 2014-10-01                  | OK     |
| 2014-10-09 |           2 | 2014-10-06                  | OK     |
| 2014-10-16 |           2 | 2014-10-13                  | OK     |
| 2014-10-23 |           2 | 2014-10-20                  | OK     |
| 2014-10-01 |           6 | 2014-10-01                  | OK     |
| 2014-10-03 |           6 | 2014-10-03                  | OK     |
| 2014-10-09 |           6 | 2014-10-03                  | OK     |
| 2014-10-16 |           6 | 2014-10-10                  | OK     |
| 2014-10-23 |           6 | 2014-10-17                  | OK     |
+------------+-------------+-----------------------------+--------+
15 rows in set (0.00 sec)

So to respond you answer

SET @date:='2014-10-03';
-- 5 is Thursday
mysql> select mydatef(@date, 5);
+-------------------+
| mydatef(@date, 5) |
+-------------------+
| 2014-10-02        |
+-------------------+
1 row in set (0.00 sec)

-- 2 is Tuesday
mysql> select mydatef(@date, 2);
+-------------------+
| mydatef(@date, 2) |
+-------------------+
| 2014-10-01        |
+-------------------+
1 row in set (0.00 sec)


setting a default collation for a postgresql database

you have to define it at database creation time, try this way:

postgres=# create database lctest encoding UTF8 LC_COLLATE 'tr_TR.utf8' LC_CTYPE 'tr_TR.utf8' template template0;
CREATE DATABASE
postgres=# \l
                                                  List of databases
                Name                 |     Owner      | Encoding |   Collate   |      Ctype    |   Access privileges
 -----------------------------------+----------------+----------+-------------+-------------+-----------------------
 en_US.UTF-8 | lctest                | postgres       | UTF8     | tr_TR.utf8  | tr_TR.utf8  |

postgres=# \c lctest
You are now connected to database "lctest" as user "postgres".
lctest=# create table lc_test (one varchar(10));
CREATE TABLE
lctest=# insert into lc_test values ('inci');
INSERT 0 1
lctest=# select upper(one)  from lc_test ;
 upper
-------
 İNCİ
(1 row)

Hope this help
MarcoP.


There is da ROW in the table Monitors, which has a value in the column "GroupId" for which the foreign key constraint fails (=it does not exist in the table MonitorGroups).
Check if there is a GroupId whithout a corresponding Id-Value in table Groups.


Is there a performance benefit to use tinyint instead of int for Primary Key?

Yes, there is a performance benefit to using TINYINT vs INT.

  Design your tables to minimize their space on the disk. This can
  result in huge improvements by reducing the amount of data written to
  and read from disk. Smaller tables normally require less main memory
  while their contents are being actively processed during query
  execution. Any space reduction for table data also results in smaller
  indexes that can be processed faster.

Read the entire page of the documentation where I pulled that paragraph from for even more information.

For your particular case, the use of TINYINT instead of INT in the company table will mean that the foreign key in the project table will 5 bytes, instead of 8. This is because all secondary keys include the primary key of the table itself - this is so that it can look up the primary key record and get more information. (Which leads into a discussion on clustered indexes, covering indexes, and double lookups.)

So if you will be joining on or filtering by this foreign key to the company table, then you will use 5/8ths as much space for each index entry, which means you'll be able to fit about 8/5ths as much more per page lookup, which means less pages have to be loaded into memory to handle any given query.

In general, smaller is always better. Not for the disk space usage per se, but for when you need to have the data loaded into memory for joins and WHERE clause filters.

That said, TINYINT is quite small. Are you sure you will never need more than 256 companies? My personal rule of thumb is take your best guess on how many records you will have, and then pick a datatype 10 times or more larger. Why? Because people are both bad at estimating and at anticipating future use cases. SMALLINT or MEDIUMINT may avoid you pain in switching in the future.

So, go for small, but big enough to make it unlikely to have to change anytime soon (or preferably, ever.)


In some cases, MySQL requires an alias, for example, a self join:

mysql> CREATE TABLE test (id SERIAL, name text);
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT name FROM test JOIN test on test.id=test.id;
ERROR 1066 (42000): Not unique table/alias: 'test'

mysql> SELECT T1.name FROM test T1 JOIN test T2 on T1.id=T2.id;
Empty set (0.00 sec)

Also when using certain subqueries:

mysql> SELECT name FROM (SELECT * FROM test) JOIN test;
ERROR 1248 (42000): Every derived table must have its own alias
mysql> SELECT T1.name FROM (SELECT * FROM test) T1 JOIN test T2;
Empty set (0.00 sec)

I think this could be the origin of "Always using an alias".

I would agree that the second example is more legible, but that is orthogonal to the use of aliases - nobody is forcing you to use 1-letter aliases, you can name the alias with the same name as the table itself:

SELECT orders.id, COUNT(*)
FROM orders orders, order_items order_items
WHERE order_items.order_id = orders.id
GROUP BY orders.id
HAVING COUNT(*) > 1

If that makes sense, but it is out of the scope of the original question. However, many people do not do that, as the other main reason to use aliases is actually to type less, which it is true that in most contexts decreases legibility - although as it is very extended in the SQL community. However, please take into account that in some cases, conventions makes things more legible (for reference, the i, j, k widely-accepted standard for counters in programming languages).

However, here is my preferred formatting for your query, in which the slight different syntax makes it better to read for me:

SELECT   O.id, count(*) AS `times`
FROM     orders O
JOIN     order_items OI
ON       OI.order_id = O.id
GROUP BY O.id
HAVING   `times` > 1

For larger queries you may want to go for something like this (exaggerated, in my opinion, for such a small query):

SELECT
    O.id,
    count(*) as `times`
FROM
    orders O
JOIN
    order_items OI
ON
    OI.order_id = O.id
GROUP BY
    O.id
HAVING
    `times` > 1

The JOIN syntax is preferred. I also, personally, prefer uppercase for aliases, either with or without a _ sign. The alias of the column is because count(*) as an identifier is ugly (in a practical way, as it has non-ascii characters).

The important part is sticking to a style guide or convention, that could be different for each project you are in.


Speed efficient query for membership first joined, latest category from membership table (min, max)

Not sure if I understand your requirements correctly. This query will return, for every organization, the minimum start date and the latest membership_subcategory_id (when the order is by start):

SELECT    organisation_id,
            MIN(`start`) AS
          first_start_date,
            ( SELECT    m2.membership_subcategory_id
              FROM      membership AS m2
              WHERE     m2.organisation_id = m.organisation_id
              ORDER BY  m2.`start` DESC
                LIMIT 1
            ) AS
          last_membership_subcategory_id
FROM      membership AS m
GROUP BY  organisation_id ;

The subquery will be executed as many times as the number of different organizations in the table, so efficiency depends on that. An index on (organisation_id, start, membership_subcategory_id) would help minimizing the subquery execution time.

Using a derived table:

SELECT    gm.organisation_id,
          gm.first_start_date,
          m.membership_subcategory_id AS last_membership_subcategory_id,
          m.*                           --- whatever other data you want
                                        --- from the latest `start` date
FROM      ( SELECT    organisation_id,
                      MIN(`start`) AS first_start_date,
                      MAX(`start`) AS last_start_date
            FROM      membership
            GROUP BY  organisation_id
          ) AS gm
    JOIN
          membership AS m
              ON  m.organisation_id = gm.organisation_id
              AND m.`start` = gm.last_start_date
  ;


The session is sleeping if the session_id value of sys.dm_os_tasks is NULL. This is stated in the link above under the Examples section: Associating Session IDs with Windows threads.

  The following query does not return information for sessions that are
  sleeping.


Online index rebuild higher fragmentation on intermediate level

Try this:
ALTER INDEX pk_test ON dbo.test REBUILD WITH (PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = ON,
    ONLINE = ON,
    ALLOW_ROW_LOCKS = OFF,
    ALLOW_PAGE_LOCKS = OFF,
    FILLFACTOR = 90,
    OPTIMIZE_FOR_SEQUENTIAL_KEY = ON)

This way you will have a FILLFACTOR of 90% and you would probably avoid to have the table fragmentation to spike up again.
Keep in mind that your table size will increase of 10%


MySQL Incremental backup on Master/Slave

It's a good idea to take a backup from a slave as long as you make sure the slave is consistent with the master. Usually a regular pt-table-checksum job gives you a good level of confidence.

Yes, you can create clones from a backup taken from the slave. Just make sure you add --slave-info option so xtrabackup records the binary log position. You can use it in CHANGE MASTER when setting up a new slave.

At TwinDB we decided to go with differential backups. It's when you take a full copy on Sunday and each other day you save the difference between Sunday and the current day. That has some benefits:

Faster recovery time. You need to prepare the full copy and only one incremental copy.
Lower probability of failure. If you have a chain of incremental copies every copy must be good otherwise you can't restore. With differential backups there is only one copy to apply on top of the full copy. So it's less likely to fail.
In most of cases total size of backup copies is still reasonable. But of course with incremental copies save more space.

Between daily backup jobs it makes sense to store binary logs. So you can do point in time recovery and recovery time can be reasonable.


Here's a solution without having to convert the value to strings.

DECLARE @ValueTable TABLE (Value INT)

INSERT INTO @ValueTable (Value)
VALUES
    (5),
    (57),
    (3124),
    (645),
    (312114),
    (9000092)

SELECT
    OriginalValue = V.Value,
    SumOfDigits =
        V.Value % POWER(10, 1) / POWER(10, 0)
        + V.Value % POWER(10, 2) / POWER(10, 1)
        + V.Value % POWER(10, 3) / POWER(10, 2)
        + V.Value % POWER(10, 4) / POWER(10, 3)
        + V.Value % POWER(10, 5) / POWER(10, 4)
        + V.Value % POWER(10, 6) / POWER(10, 5)
        + V.Value % POWER(10, 7) / POWER(10, 6)
        + V.Value % POWER(10, 8) / POWER(10, 7)
FROM
    @ValueTable AS V

Result:

OriginalValue   SumOfDigits
5               5
57              12
3124            10
645             15
312114          12
9000092         20

I'll explain how this works with an example, like value 645. To get the last digit we calculate the rest of the original value when divided by 10 (this is the mod operator %). Since 645 / 10 is 64.5 (it won't show the decimal value if done on SQL Server because it's treated as INT), the rest is 5.

SELECT 645 % 10 -- Result: 5

To get the second to last digit, we calculate the rest of the original value when divided by 100 (100 = 10 * 10 = 10 ^ 2 = POWER(10, 2)). Since the rest of a division by 100 might result in 2 digits (from 0 to 99) and we just need the tens digit, we divide this result by 10. The result of the division will be INT if the original number was INT and this is exactly what we wanted.

SELECT
    645 % 100,      -- Result: 45
    645 % 100 / 10  -- Result: 4 (second to last digit)

To get the 3rd to last, we just add an additional 0 (multiply by 10) to both the mod operator value and the division:

SELECT
    645 % 1000,         -- Result: 645
    645 % 1000 / 100    -- Result: 6 (third to last digit)

This can also be written with the POWER function, which is the expression wrote in the solution.

SELECT
    645 % POWER(10, 3),                 -- Result: 645
    645 % POWER(10, 3) / POWER(10, 2)   -- Result: 6 (third to last digit)

One thing to note in this solution is that you must add as many additions (each with it's own % and /) as you might have digits on the supplied values.

Also, if you want to repeat this process if the resulting number has more than one digit (for example value 993 -> 21 -> 3), there's a very simple and curious algorithm for this that's called the digital root.

Here's a more expressive solution from Andryi M:

SELECT
    OriginalValue = V.Value,
    SumOfDigits =
          V.Value            % 10
        + V.Value / 10       % 10
        + V.Value / 100      % 10
        + V.Value / 1000     % 10
        + V.Value / 10000    % 10
        + V.Value / 100000   % 10
        + V.Value / 1000000  % 10
        + V.Value / 10000000 % 10
FROM
    @ValueTable AS V


SQL Server suggestion to create nonclustered index - on 2 columns, reversed

Yes there is a big difference between both indices.
The following index will use the courseID as a seek predicate. This will help  SQL Server to find all pages with a defined courseID. In addition, the column userID is included in the pages of the index. This will allow SQL Server to serve those values directly from the index.
CREATE NONCLUSTERED INDEX [<name>]
ON [dbo].[tblUserCourses] ([courseID])
INCLUDE ([userID])

The creation of this index will help SQL Server to perform quick responses for Queries like this:
SELECT userID FROM yourTable WHERE courseID = 1

The courseID can be used for the search in the index. Where the userID is served from the leaf level pages from the index.
Where as the following index works exactly the same but the other way round. It will use the userID for the seek and can provide the courseID directly from the leaf level pages from the index.
CREATE NONCLUSTERED INDEX [<name>]
ON [dbo].[tblUserCourses] ([userID])
INCLUDE ([courseID])

The index will improve the performance of this query:
SELECT courseID FROM yourTable WHERE userID = 1

SQL Server may also use indices and join the results of both indices.
In example, you can serve this query totally from the indices:
SELECT userID, courseID FROM yourTable

This will be served by a hash match using the both indices.
Another one which could benefit from both together may be this one:
SELECT COUNT(*) FROM yourTable WHERE userID=2 and courseID=10

It might happen, that your SQL Server provides an index hint and sees that he could perform better by using a special index. After you created those index, he recognizes (during the new index) that another one would be even better. This isn't uncommon.
This may also happen if your query optimizer runs into a timeout. This can happen very fast. In the case your query optimizer times out, SQL Server will use the best plan he found until the timeout. This can vary and this may have some bad effects. Using the indices, you help your query optimizer and he will use it's time more efficient. To provide good plans.
To really see if your indices are a good choice, you need to show a bit more of your code. Additionally you can try to evaluate your index usage. There is a good DMV for this called dm_db_index_usage_stats. It will provide you how many reads, seeks, updates, etc. happend on an index. This will help you to get your own decisions if it's good or bad. An index is mostly on then useful if you read it more than you write it.
SELECT * from sys.dm_db_index_usage_stats

Hopefully this will help to clear things up for you.
Edit
Due to the lastly provided information, I would suggest to try an index based on EventID, CourseID (same order!).
On your other table maybe an index over EventID, StatusID too.


Do you have a fast spindle or spindle set that is not part of the DB itself to back up to?

Or even a tape drive local to SQL ?

If backup space is snug, a tool like redgate sql backup will compress a backup. A 1TB database might be 100-300mb compressed. The backup is much faster also, because the amount being written is so much smaller (our backups of 0.5TB went from an hour to 19 mins).

The key is to have sufficient space, either local on the box or local on a fast LAN, so you can do a straight, single shot backup. The target should be a spindle set that is not used for SQL data or logs, or any other high IO operation (as the backup itself is heavy IO).

Additionally: If possible, take the db to single user, or read only state before running the backup. In some cases this can improve performance (by eliminating concurrent IO to the db itself during the backup).


Change host IP of master in mysql replication

After a long internet surfing I got a thing. MASTER_LOG_FILE will take Relay_Master_Log_File and MASTER_LOG_POS will take Exec_Master_Log_Pos as their respective value in CHANGE MASTER TO statement. In the Exec_Master_Log_Pos section of mysql docs it is clearly stated. I have found another article which says the same thing :

When you’re using CHANGE MASTER TO to set start position for the slave
you’re specifying position for SQL thread and so you should use
Relay_Master_Log_File:Exec_Master_Log_Pos. Otherwise you’re going to
ruin your replication.

It does not help SHOW MASTER STATUS has Master_Log_File value and
CHANGE MASTER TO accepts Master_Log_File parameter, while you actually
want to use Relay_Master_Log_File instead. This naming was left from
pre MySQL 4.0 era and it still causes confusion so many years later.

Conclusion :

STOP SLAVE;
SHOW SLAVE STATUS \G
note down Relay_Master_Log_File value (Example : mysql-bin.000007) and Exec_Master_Log_Pos value (Example : 775)
CHANGE MASTER TO MASTER_HOST='<New IP of Master>', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=775;
START SLAVE;


How to clean up tables that don't belong into mysql/mysql database?

No clean way...

USE MyDatabase;
RENAME TABLE mysql.mytable1 TO mytable1;
RENAME TABLE mysql.mytable2 TO mytable2;

etc.

You said "large number"... You could get the list thus:

SELECT CONCAT('RENAME TABLE mysql.', TABLE_NAME, ' TO ', TABLENAME, ';')
    FROM information_schema.TABLES
    WHERE schema_name = 'mysql';

But be very careful not to rename the system tables!


Turns out that even if you pay for reserved resurces and are guaranteed 8GB of RAM, you aren't really guaranteed it. And it's not really reserved for you. In our case it turned out that another Windows Azure customer was using too much resources and our database got 1GB instead of 8.


Sorry for the delay answer.

“OP Code is not valid: Check Message format” will happen if you are trying to connect a REST client to the Mongo wire listener.

You need to start the listener with listener.type=rest set in your listener properties file.

As for the same question above, there is clearly an old version of the listener being used.

The listener is giving a very clear error message saying that the REST listener type is not supported. The REST listener was introduced in 12.10.xC4. But from the presence of the JsonListenerCLI class in the exception stack, I can determine that the installation is using a listener from either 12.10.xC2 or xC3.  The JsonListenerCLI class was renamed to ListenerCLI in 12.10.xC4 which is also the first version that include REST listener support.


PostgreSQL 8.3: Slow GROUP BY on large table

So why does Postgres 9.2 still show a sequential scan? I quote the Postgres Wiki:

  Is "count(*)" much faster now?

  A traditional complaint made of PostgreSQL, generally when comparing
  it unfavourably with MySQL (at least when using the MyIsam storage
  engine, which doesn't use MVCC) has been "count(*) is slow".
  Index-only scans can be used to satisfy these queries without there
  being any predicate to limit the number of rows returned, and without
  forcing an index to be used by specifying that the tuples should be
  ordered by an indexed column. However, in practice that isn't
  particularly likely.

  It is important to realise that the planner is concerned with
  minimising the total cost of the query. With databases, the cost of
  I/O typically dominates. For that reason, "count(*) without any
  predicate" queries will only use an index-only scan if the index is
  significantly smaller than its table. This typically only happens when
  the table's row width is much wider than some indexes'.

Emphasis mine.
There is hardly anything to gain from an index scan here, as long as your talbe isn't bloated with more (big) columns.


Oracle alternative edition for Windows Server 2012

You'll find that information the the Oracle reference document 1307195.1: Certification Information for Oracle Database on Microsoft Windows x64 (64-bit).

The latest Windows server version where 11.2 is supported is Windows 2008R2. Oracle 12c (12.1) is supported on Windows 2012 (including Hyper-V guests), but only in single instance mode (no RAC).

You can also find that information in the install guides, e.g.:

11.2 install guide, software requirements - Windows 2012 not listed
12.1 install guide, software requirements - Four editions of Windows 2012 x64 listed (note that the Server Core edition is not supported).


Find unmatched rows between two tables dynamically

I derive from your example that you only want to match tables with the same structure (compatible row types).
Base query
First of all, your base query is needlessly complex and possibly incorrect. Consider instead:
(TABLE a EXCEPT ALL TABLE b)  -- ALL keeps duplicate rows and is faster
UNION ALL
(TABLE b EXCEPT ALL TABLE a);

I doubt you want to fold duplicates. Typically, tables do not hold complete duplicates to begin with, and omitting the ALL key word would instruct Postgres to try and fold duplicates in every step, which is just a waste of time. Details:

Select rows which are not present in other table

Even if completely duplicate rows are possible, it might be best to return all of them to avoid misleading answers. If you want to fold duplicates in the result anyway, a single UNION does the job:
(TABLE a EXCEPT ALL TABLE b)
UNION
(TABLE b EXCEPT ALL TABLE a);

However, a query with NATURAL FULL OUTER JOIN is more efficient, doing almost the same:
SELECT *
FROM   a NATURAL FULL OUTER JOIN b
WHERE  a IS NULL OR b IS NULL;

There are two subtle, exotic corner cases:

This returns rows that are all NULL to begin with. It would be an odd table design that allows such rows, though.
This does not return duplicate rows that are not matched with the same number of duplicate rows in the other table: a single match is enough to eliminate all - which is subtly different from folding duplicates in the result! Have a look at the fiddle below. Again, it would be an odd table design that allows completely duplicate rows.

Function
What you have so far does not work for several reasons.
To return actual table rows dynamically (and not just a count or a text representation), you need to use a polymorphic type.
Since the second table is bound to have a compatible row type (as per my assumption) it's enough to hand in just the table name for that.
CREATE OR REPLACE FUNCTION f_tbl_diff(_tbl1_type ANYELEMENT, _tbl2 text)
  RETURNS SETOF ANYELEMENT AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %1$s NATURAL FULL OUTER JOIN %2$I
      WHERE  %1$s IS NULL OR %2$I IS NULL'
    , pg_typeof(_tbl1_type), _tbl2);
END
$func$  LANGUAGE plpgsql;

Call:
SELECT * FROM f_tbl_diff(NULL::a, 'b');
Note the special syntax for the first argument! We hand in an actual row defining the return type, not just a table name. Related answer on SO with ample details (scroll to the last chapter):

Refactor a PL/pgSQL function to return the output of various SELECT queries

This returns all rows that have no exact, complete match in the other table
SQL Fiddle also demonstrating exotic corner cases.
About the dynamic SQL:

SQL injection in Postgres functions vs prepared queries


Automatic Failover At Always On High Avability Group SQL SERVER

That's not what it means.
With a FCI, the protection is done at the service level. It means that if the primary node have an issue, the service will restart on the other node. As all node share the same storage, you end up with the exact same "instance" after a failover (same jobs, same logins, same DBs, etc..) but running from a different server.
In AG, the services is up and running on both node. The protection is set on at the database level (you can have some DB on the primary node that are in AG and some other that aren't for example). Each nodes also have their own storage.
If you are in sync mode and have the failover set to "automatic", then if the primary node of you AG fail, all DBs that are in your AG will failover to the other node.
P.s. The system databases are not part of the AG so you need to be carefull with everything releated to those (ex: Jobs, logins, etc) as they won't "failover".


Unable to get output variable of Oracle procedure using Sql linked server

You have wrong string length in your SQL Server proc, look at this:

v_update_status VARCHAR2(50);
v_error_message VARCHAR2(500);

DECLARE @op_update_status Nvarchar(50)
DECLARE @op_error_message Nvarchar(500)

Original oracle parameters are of length 50 and 500 bytes, but you use NVARCHAR and your SQL Server variables are 100 and 1000 bytes long (UCS-2 uses 2 bytes to store each symbol)

Update: I've found Oracle data types and saw that varchar2 is not unicode data type, Oracle uses nvarchar also, so all what you need is to change your Nvarchar type to varchar in your SQL Server code


Object_name(object_id) returning incorrect results

The answer is quite simple. You're using a load of ORs, which should be ANDs:

object_name(object_id) NOT LIKE '%BACKUP%'
  or object_name(object_id) not like '%backup%'
  OR object_name(object_id) NOT LIKE '%Wi%'

In your case, object_name(object_id) not like '%backup%'  will still match OR object_name(object_id) NOT LIKE '%Wi%' and so will return the row etc etc.

Change all of the above conditions to AND and you'll be OK.


update an id to max+1 in current table? (MySQL)

UPDATE jobs j1
INNER JOIN jobs j2 ON j1.worker_id <= j2.worker_id
LEFT OUTER JOIN jobs j3 ON j2.worker_id < j3.worker_id
SET j1.worker_id = j2.worker_id + 1
WHERE j1.worker_id = 3 AND j3.worker_id IS NULL;

j1 is the row you want to change, based on the condition in the WHERE clause.
j2 is the set of rows with a worker_id greater or equal to j1.
j3 is the set of rows with a greater worker_id than j2 (when j2 has the greatest worker_id in the table, there is no row for j3, so j3.* will be NULL).
j2 therefore has the greatest worker_id, so use its value + 1.

Demo:

mysql> create table jobs (id serial primary key, worker_id int);
mysql> insert into jobs (worker_id) values (1), (2), (3), (4), (5);

mysql> select * from jobs;
+----+-----------+
| id | worker_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         3 |
|  4 |         4 |
|  5 |         5 |
+----+-----------+

mysql> update jobs j1 inner join jobs j2 on j1.worker_id <= j2.worker_id
 left outer join jobs j3 on j2.worker_id < j3.worker_id
 set j1.worker_id = j2.worker_id + 1
 where j1.worker_id = 3 and j3.worker_id is null;

mysql> select * from jobs;
+----+-----------+
| id | worker_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         6 |
|  4 |         4 |
|  5 |         5 |
+----+-----------+

It works regardless of the value we want to change:

mysql> update jobs j1 inner join jobs j2 on j1.worker_id <= j2.worker_id
 left outer join jobs j3 on j2.worker_id < j3.worker_id
 set j1.worker_id = j2.worker_id + 1
 where j1.worker_id = 5 and j3.worker_id is null;

mysql> select * from jobs;
+----+-----------+
| id | worker_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         6 |
|  4 |         4 |
|  5 |         7 |
+----+-----------+

And it works even if we're changing the row that already has the highest value in the table:

mysql> update jobs j1 inner join jobs j2 on j1.worker_id <= j2.worker_id
 left outer join jobs j3 on j2.worker_id < j3.worker_id
 set j1.worker_id = j2.worker_id + 1
 where j1.worker_id = 7 and j3.worker_id is null;

mysql> select * from jobs;
+----+-----------+
| id | worker_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         6 |
|  4 |         4 |
|  5 |         8 |
+----+-----------+


SQL Server - backup checksum default vs VSS

This gives you a little bit of extra protection for your database backups, since the backup operation verifies each page for checksum and torn page, and generates a checksum for the entire backup. This is not a substitute for actually restoring you database backups on a regular basis, but it is a useful extra step to help ensure that your backups are actually good.
CHECKSUMs does not add too much CPU load but doing a VERIFYONLY does. There is no impact on vss snapshot creation.


Why does phpMyAdmin execute all queries with an apostrophe?

Those around values are single quotes - ' and denote string values. Numbers should not be quoted if they are supposed to go to a numeric type column but in PhpMyAdmin every input field is textual by default so it probably just tries to stay safe with quoting the '5' too, MySQL can convert the type when needed.

The other type are backticks ` and it is a different character. MySQL uses it to quote identifiers (names of column, tables, indexes etc.). It is not needed when you use normal identifiers like name or email but it is needed for others:

if you need to use a reserved word as an identifier - `insert` or `order` - not a good idea to create tables with such columns, but sometimes it already exists and you just need to query it.
when you want to use complex names - `column named X` - again better not to name your columns like this, but it is often used for column aliases in selects

.

select col1 as `column 1`, col2 as `column with diacritics áéí` from table;

For PMA it is just simple to quote every identifier than to check if it is safe or not to go unquoted.


Allow public use of functions without giving them database control

You need to grant rights to use the function.

GRANT EXECUTE ON Function to UserOrRole


Most efficient way to retrieve a sub-query COUNT grouped by top table?

You're on a newer version of SQL Server so the actual plan gives you a lot of information. See the caution sign on the SELECT operator? That means that SQL Server generated a warning which could affect query performance. You should always look at those:

<Warnings>
<PlanAffectingConvert ConvertIssue="Seek Plan" Expression="[s].[logger_uuid]=CONVERT_IMPLICIT(nchar(17),[d].[uuid],0)" />
<PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(100),[d].[name],0)=[g].[name]" />
</Warnings>

There are two data type conversions caused by your schema. Based on the warnings I suspect that name is actually an NVARCHAR(100) and logger_uuid is an NCHAR(17). The posted table schema in the question may not be correct. You should understand the root cause of why these conversions are happening and fix it. Some types of data type conversions prevent index seeks, lead to cardinality estimate issues, and cause other problems.

Another important thing to check is wait stats. You can see those in the details of the SELECT operator as well. Here's the XML for your wait stats and the time spent by the query:

<WaitStats>
<Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="49515" WaitCount="3773" />
<Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="57164" WaitCount="2466" />
</WaitStats>
<QueryTimeStats ElapsedTime="67135" CpuTime="10007" />

I'm not a cloud guy but it looks like your query isn't able to fully engage a CPU. That's probably related to your current Azure tier. The query only needed about 10 seconds of CPU when executing but it took 67 seconds. I believe that 50 seconds of that time was spent being throttled and 7 seconds of that time was given to you but used on other queries that were concurrently running. The bad news is that the query is slower than it could be due to your tier. The good news it that any reductions in CPU could lead to a 5X reduction in run time. In other words, if you can get the query to use 1 second of CPU then you might see a runtime of around 5 seconds.

Next you can look at the Actual Time Statistics property in your operator details to see where the CPU time was spent. Your plan uses row mode so the CPU time for an operator is the sum of time spent by that operator as well as its children. This is a relatively simple plan so it doesn't take long to discover that the clustered index scan on logger_data uses 6527 ms of CPU time. The loop join that calls it uses 10006 ms of CPU time, so all of your query's CPU is spent at that step. Another clue that something is going wrong at that step can be found by looking at the thickness of the relative arrows:

A lot of rows are returned from that operator so it's worth looking at detail. Looking at the actual number of rows for the clustered index scan you can see that 14088885 rows were returned and 14100798 rows were read. However, the table cardinality is just 484803 rows. Intuitively that seems pretty inefficient, right? The clustered index scan returns far more than the number of rows in the table. Some other plan with a different join type or access method on the table is likely to be more efficient.

Why did SQL Server read and return so many rows? The clustered index is on the inner side of a nested loop. There are 38 rows returned by the outer side of the loop (the scan on the logger table) so the scan on logger_data executes 38 times. 484803*38 = 18422514 which is pretty close to the number of rows read. So why did SQL Server choose such a plan that feels so inefficient? It even estimates that it'll do 57 scans of the table, so arguably the plan that you got was more efficient than it suspected.

You might have been wondering why there's a TOP operator in your plan. SQL Server introduced a row goal to when creating a query plan for your query. This might be more detail than you want, but the short version is that SQL Server does not always need to return all rows from a clustered index scan. Sometimes it can stop early if it only needs a fixed number of rows and it finds those rows before it reaches the end of the scan. A scan isn't as expensive if it can stop early so the operator cost is discounted by a formula when a row goal is present. In other words, SQL Server expects to scan the clustered index 57 times, but it thinks that it will find the single row that it needs very quickly. It only needs a single row from each scan due to the presence of the TOP operator.

You can make your query faster by encouraging the query optimizer to pick a plan that doesn't scan the logger_data table 38 times. This might be as simple as eliminating the data type conversions. That could allow SQL Server to do an index seek instead of a scan. If not, fix the conversions and create a covering index for the logger_data:

CREATE INDEX IX ON logger_data (category_name, logger_uuid);

The query optimizer chooses a plan based on cost. Adding this index makes it unlikely to get the slow plan which does many scans on logger_data because it'll be cheaper to access the table through an index seek instead of a clustered index scan.

If you aren't able to add the index you can consider adding a query hint to disable the introduction of row goals: USE HINT('DISABLE_OPTIMIZER_ROWGOAL')). You should only do this if you feel comfortable with the concept of row goals and understand them. Adding that hint should result in a different plan, but I can't say how efficient it'll be.


RAID0 instead of RAID1 or 5, is this crazy?

There's one very important aspect I think you are missing in your assessment:

How do you plan to recover?

When raid5 loses a drive, it will run in a degraded state until it has recovered automatically. (At least if you have a hot spare at hand.)

When a raid0 loses a drive, it cannot ever recover at all. This means you have lost redundancy, and to recover it, you need to rebuild your raid0, and copy all the data (not just the data on the broken drive) back from the secondary that is now under production load. That is, instead of the single degraded raid5 array, it's now your entire production setup that gets the performance hit.

If the raid5 (or raid6) degraded state performance penalty isn't something you can cope with, you should probably do raid 1+0 instead. Yes, it costs more, but disk prices being what they are, it's going to be money well spent.

Maybe "actively monitor the raid5 state, and transfer the load off the primary when a drive fails" is the solution that gives you most of the benefits without any drawbacks? (Apart from losing the coolness factor of running without any local redundancy, of course.) If your raid5 drive recovery is taking a lot longer than a complete database data sync, either your raid software is acting strangely, or you have seriously oversized disks, I'd think.


Large database backup files integrity check after or before copy

SQL Server has a very handy command which checks the headers, checksums (if present) and that the backup set is complete. A data integrity check will be performed if a checksum is present but is optional. From the documentation here

RESTORE VERIFYONLY FROM DISK = 'D:\AdventureWorks.bak' WITH CHECKSUM;
GO


How to write a wrapper for a heterogeneous VARIADIC function like jsonb_build_object()?

jsonb_build_object() takes VARIADIC "any" as input. The pseudo-type "any" is a placeholder for any input type. Currently, that only works for a C function. The manual:

  Functions coded in C (whether built-in or dynamically loaded) can be
  declared to accept or return any of these pseudo data types. It is up
  to the function author to ensure that the function will behave safely
  when a pseudo-type is used as an argument type.

  Functions coded in procedural languages can use pseudo-types only as
  allowed by their implementation languages. At present most procedural
  languages forbid use of a pseudo-type as an argument type, and allow
  only void and record as a result type (plus trigger or event_trigger
  when the function is used as a trigger or event trigger). Some also
  support polymorphic functions using the types anyelement, anyarray,
  anynonarray, anyenum, and anyrange.

There is no one-to-one translation for an implementation with another procedural language. There may be workarounds, depending on your exact objective.


Problems while building plpgsql_check on Windows

You can find Adam Bartoszewicz's instructions here on Google Groups (also published on Pavel Stehule's blog):

  Download and install PostgreSQL 9.3.4 for Win32 from http://www.enterprisedb.com
  Download and install Microsoft Visual C++ 2010 Express
  Lern tutorial http://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows
  The plpgsql_check depends on plpgsql and we need to add plpgsql.lib to the library list. Unfortunately PostgreSQL 9.4.3 does not contain this library.
  Create a plpgsql.lib from plpgsql.dll as described in http://adrianhenke.wordpress.com/2008/12/05/create-lib-file-from-dll
  Change plpgsql_check.c file, add PGDLLEXPORT line before evry extension function, as described in http://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows
  ...
  PGDLLEXPORT
  Datum plpgsql_check_function_tb(PG_FUNCTION_ARGS);
  PGDLLEXPORT
  Datum plpgsql_check_function(PG_FUNCTION_ARGS);
  ...
  PGDLLEXPORT
  Datum
  plpgsql_check_function(PG_FUNCTION_ARGS)
  {
  Oid            funcoid = PG_GETARG_OID(0);
  ...
  PGDLLEXPORT
  Datum
  plpgsql_check_function_tb(PG_FUNCTION_ARGS)
  {
  Oid            funcoid = PG_GETARG_OID(0);
  ...
  Build plpgsql_check.dll
  Install plugin

  copy plpgsql_check.dll to PostgreSQL\9.3\lib
  copy plpgsql_check.control and plpgsql_check--0.8.sql to PostgreSQL\9.3\share\extension


Difference between database vs user vs schema

In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

See this post on Stack Overflow: difference between a User and a Schema in Oracle? for more details and extra links.

You create users with the create user statement. This also "creates" the schema (initially empty) - you cannot create a schema as such, it is tied to the user. Once the user is created, an administrator can grant privileges to the user, which will enable it to create tables, execute select queries, insert, and everything else.

The database is the thing that contains all the users you've created, and their data (and a bunch of predefined system users, tables, views, etc. that make the whole thing work). You should look at the Oracle Database Architecture documentation in the Concepts Guide (actually, that whole page is worth a read - there's a section about users and schemas higher up in that page) to get an introduction to what a database is, and what a database instance is - two important concepts.

You can create a database with the create database statement, once you've installed the Oracle software stack. But using dbca (database creation assistant) is easier to get started.


Drop the user from Read only Secondary database Always ON server

You can't do this to the secondary only.  Dropping a user modifies the database, which is not possible because the secondary AG database is in read only mode.

You would have to drop it on the primary - then the change will be replicated to the secondary.

From the updated question, it sounds like you're trying to recreate both the login and the user after removing the user from the primary.

It's hard to tell without seeing the actual T-SQL being executed, but you may have better luck with removing the login from the secondary first, as that is a server level object and thus wasn't removed when you dropped the user on the primary.

  The login and user have been deleted from the primary database, and we are trying to recreate the login on primary. But when we are trying to create the login on primary, it is throwing an error saying it already exists.

I think it's important to clarify something real quick.  A login exists at the instance level, not in any specific database.  You can just connect to each instance and create the login.

A user exists at the database level, and is linked to a login.  To create the user, you'll need to connect to the primary database (whichever instance that happens to be on), and create the user there.  It will automatically be created on the secondary.


What is the history of the SQL Server ANSI (ISO) compatibility settings?

SQL Server came from Sybase, which had different default settings for these kind of things. Gradually, MS introduced SET settings to allow us to control behavoiur. A big push to follow ANSI standard was with the 6.0 release in 1995. I believe they even hired Joe Celko with the work to be ANSI SQL certified (yes, there was a certification process back in the days - anybody ever used SET FIPS_FLAGGER? :-)). This was when the specific SET ANSI setting was inroduced - to allow us to "be ANSI" with only one setting. But this is all a mess with the combo of database settings (which are largely ignored) along with different APIs and tools that has different combo of these settings.