Data archival strategy that handles schema changes?

You need to define your requirements more specifically before even thinking about a solution:

Why is archiving necessary? It sounds like the system already handles old data, so what is the business need to separate out this data? Performance?
Is archive data a read-only snapshot, or are historical data changes possible? If changes are possible, which types of changes will be supported (insert, update, delete, or some combination of those)?
Is the database multi-tenant, and if so, do you require the ability for each tenant to be able to archive at different points in time?
Does your application need to run with the archive data as a data source? I'm assuming yes since you mention synchronizing schema changes.
What is the minimum version/edition of the DBMS you need to support? This will determine which features are available to use in your strategy.
How much time do you have to implement archiving? Archiving is a very low-level design issue that ideally should be baked in right from the start; adding it on later can potentially take a significant amount of time to redesign and implement.

Now having said all that, one thing I would advise you, having done it here (SQL Server), is this: avoid multiple databases if at all possible, particularly if you require the ability to edit historical data.

While I can't give away our IP, I'll tell you that the process involved with shuffling data from the "live" database to the "archive" database is extremely complex if you take a dynamic approach like we did for ~700 tables. Depending on the state of your database schema, that kind of thing may not even be possible to accomplish, or result in data discrepancies you don't expect. If you don't have very many tables (< 200) and the schema is in rough shape, quite honestly, do not take a dynamic approach, or wait until it's cleaned up to a decent state. With a large number of tables and a rough schema, multiple databases is not a viable solution.
As you mentioned, you do have to run update scripts against multiple databases, and you have to keep track of all the databases somehow. It's really easy for things to become desynchronized, i.e., an archive database is moved to a different server or instance and your configuration database or table holds the old information. The choice is either to always synchronize the schema, or write applications to always be backwards compatible. (Hint: it's far easier to synchronize the schema.)

While I certainly don't recommend multiple databases, it is a possible solution depending on your requirements.


MySQL InnoDB restoration from hardware failure

I did it the easy way of starting a Windows MySQL 5 instance and coping the data files over. I was able to mysqldump to get what I needed.


How do you detect whether you have permission to query sys.dm_os_performance_counters?

Eventually we worked it out!

If you're connected to SQL Azure Database, and the database tier is Premium, check for the VIEW DATABASE STATE permission
If you're connected to SQL Azure Database, and the database tier is not Premium, check for EXECUTE permission on sys.dm_os_performance_counters
Otherwise, check for the VIEW SERVER STATE permission

The bit of this that isn't an obvious translation of the documentation is that "EXECUTE permission on sys.dm_os_performance_counters" is equivalent to "being a Server admin or an Azure Active Directory admin" in the case of non-Premium Azure SQL Database. This was just what we found by trial and error, and is not documented anywhere that I'm aware of.

DECLARE @Result VARCHAR(MAX)
SET @Result = ''

IF (SERVERPROPERTY('EngineEdition') = 5)
    BEGIN
        IF (DATABASEPROPERTYEX(DB_NAME(), 'Edition') = 'Premium')
            BEGIN
                IF NOT EXISTS(SELECT 1 FROM sys.fn_my_permissions(NULL, 'DATABASE') WHERE permission_name = 'VIEW DATABASE STATE')
                    SET @Result = 'You do not have VIEW DATABASE STATE permissions for this database.'
            END
        ELSE
            BEGIN
                IF NOT EXISTS(SELECT 1 FROM sys.fn_my_permissions('sys.dm_os_performance_counters', 'OBJECT') WHERE permission_name = 'EXECUTE')
                    SET @Result = 'You do not have server admin or Azure active directory admin permissions.'
            END
    END
ELSE
    BEGIN
        IF NOT EXISTS(SELECT 1 FROM sys.fn_my_permissions(NULL, 'SERVER') WHERE permission_name = 'VIEW SERVER STATE')
            SET @Result = 'You do not have VIEW SERVER STATE permissions within this instance.'
    END
RETURN @Result


Can I somehow Improve the relationships between tables in the following sample?

Asssuming that I have understood the problem, the following picture describes (a part of) your database, in which an arrow from A to B corresponds to a foreign key from table Ato table B:

Actually, there is a redundancy in that Grades are referred both by Groups and Subjects and each class must have the same grade if it is accessed through one of the two relations.

I think that in a case like this you have two options:

Keep this schema, and check, maybe with a trigger, that when you insert a class it refers to a course and a subject with the same grade, or
change the schema, removing from Subject the foreign key to Grade, and, if you need to find the grade of a certain subject, joining that table with classes, courses and grades. In this way you simplify the situation, but you could create subjects not tied to specific grades.

I have a light preference for the first solution: in both of them, however, it is important that the objects in tables Grade, Subject, Course and Classes be inserted with great attention to not cause inconsistencies. The best way to do this is to define in the database stored procedures to operate on more than one object and/or perform all the proper checks, and require that the application(s) use only them, without inserting explicitly records.


Sum of decimal values that come in a string

If your SQL version is 2016 or later, you could try to use STRING_SPLIT function.
Against earlier version, there are also lots of impletmentations of string split by searching GG.

Below is my example for SQL 2016+:

DECLARE @v nvarchar(256) = N'ABC $3.25, DEF $2.50, HIJ2 $0.25,ABC1 $3.25, DEF $2.50, HIJ $0.25, KLM $4.50'
SELECT  TRIM(value), RIGHT(value, LEN(value)-CHARINDEX('$',value))
FROM    STRING_SPLIT(@v,',')
SELECT  SUM(CONVERT(decimal(10,2),RIGHT(value, LEN(value)-CHARINDEX('$',value))))
FROM    STRING_SPLIT(@v,',')


SQL Server 2012 stored procedure runs very slow sometimes

First thing to check is if there's blocking happening. If the procedure / statement is blocked by another process then it's quite clear why it takes longer.

If that's not the case, you can try to check from plan cache if there's something that explains it. Here's one short example query to get some of the figure from there:

select top 100
SUBSTRING(t.text, (s.statement_start_offset/2)+1,
((CASE s.statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE s.statement_end_offset
END - s.statement_start_offset)/2) + 1) as statement_text,
t.text,
s.total_logical_reads, s.total_logical_reads / s.execution_count as avg_logical_reads,
s.total_worker_time, s.total_worker_time / s.execution_count as avg_worker_time,
s.execution_count
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text (sql_handle) t
where t.text like '%name_of_proc%'
order by s.total_worker_time desc

There's also a lot of other columns, like max_elapsed_time, max_worker_time, max_logical_reads etc. That might give some insight.


How much memory do I need for innodb buffer pool?

If you go strictly by that rule of accommodating an addition 10%, here is my suggestion:

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

This will produce exactly what you need to set innodb_buffer_pool_size in /etc/my.cnf. If you want to account for 25% increase in data and indexes over time, please change (SELECT 1 growth) BBB to (SELECT 1.25 growth) BBB

Recently, I answered another question like this in the DBA StackExchange.


Query plan showing a missing index even though the index is present in the table

Based on the information in your question, I think you've hit a bug that is referenced in this blog (Missing index DMVs bug that could cost your sanity…) and addressed in this Connect item

Per the blog post:
The bug is this: the missing index code may recommend a nonclustered index that already exists. Over and over again. It might also recommend an index that won't actually help a query.


Where are subscriptions registered in SSMS after creating subscription on Distributor Server?

From experience a few days later...

When creating a pull subscription, the subscription was added to the 'local subscriptions' folder in SSMS.

When creating a push subscription, nothing is added to the subscription folder until the subscription is run the first time. Thereafter it is added to the 'local subscriptions' folder.


PostgreSQL: Trouble accessing another server's Data via NFS

I figured out the issue. It was due to improperly shared NAS folder. I fixed it by using this entry in /etc/exports

/var/lib/postgresql/ 192.168.1.0/24(rw,nohide,insecure,no_subtree_check,async)

to share.

And if it interests anybody, I managed to get shared database running successfully. The only folder that needed to be common was the above folder. And both PostgreSQL Server instances need to have identical configuration files for the data folder to be shared successfully.


How do I copy from one meta_key to another meta_key in same table?

Based on the table structure what you need is a self-join in your update. To find lots of examples type mysql self join update into your favorite search engine. I think the basic structure you're looking for is:

UPDATE 'wp_thafxavhs9_usermeta' AS 'billing'
INNER JOIN 'wp_thafxavhs9_usermeta' AS 'address' ON
   'billing'.user_id = 'address'.user_id AND 'address'.meta_key = 'address'
SET
   meta_value = 'address'.meta_value
WHERE
   'billing'.meta_key = 'billing_address_1'

Of course don't run that or any SQL you find on the interwebs on production without testing it in a safe environment first.  That might not give you exactly what you want but should give you a solid start.

Also you should keep in mind that this won't keep the columns synced as new users are added. In order to do that you'll need to change your insert/update logic or add a trigger. Personally, I would recommend changing your insert logic over the trigger, but that isn't always practical.


Is there a way to setup a user to run SPs, but not access the underlying data tables?

Couldn't you have procedure A run with execute as owner or execute as 'db user tied to security account associated with linked server'? That way the rights of the caller don't have to transfer...

Or, you can create a login on the remote server that only has the ability to execute the queries you want, and add an impersonation account to the existing linked server on Server A to have the local webserver login impersonate the new login on server B.


Mongodb ubuntu - config server and shard together

An easy way to achieve that is to use cron's @reboot feature:

> crontab -e -u mongodb
@reboot /usr/bin/mongod --configsvr --dbpath /data/configdb/ --port 21001 --logpath /var/log/mongodb/configsvr.log --fork
@reboot /usr/bin/mongod --shardsvr --dbpath /data/db/ --port 31001 --logpath /var/log/mongodb/mongod.log --fork

However, you have to make sure that /etc/security/limits.conf holds the correct values.


How to re-simulate a long query stuck in mysql queue?

Can you please share more on what queries are you running? I think, you can make use of InnoDB engine which will do row level locking instead of table level lock. Also you can make you of slow query log which will log all the queries taking more than n seconds (as specified in config)


How to index for multiple inequality WHERE search conditions?

Create two separate indexes: idxA(COL_A) and idxB(COL_B).
MySQL will use both indexes and then merge the result.

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html

Secion: 8.3.1.4.3 The Index Merge Sort-Union Access Algorithm


Is it possible to view a SQL query in the PgAdmin III Graphical Query Builder?

It's a one-way street. You can use the GQB to generate SQL code but not the other way round.

I have been involved with pgAdmin development for a couple of years and I don't know of any features in the opposite direction. So I am pretty sure.


What is the SQL Server equivalent for Oracle character set AL32UTF8?

NVARCHAR is always UTF-16.
You can try Latin1_General_100_CI_AS_SC_UTF8 with a VARCHAR column, but that doesn't look like an encoding problem. It really shouldn't matter what the source encoding / collation was as long as you correctly indicate the encoding used to transfer the data.
Either way, those 3 bytes / characters could be a bug in the import tool/process or the export tool/process. How are you transferring the data? If the image you are showing from Notepad++ is from the file being used to migrate the data and the field is supposed to be empty, either the export messed up, or perhaps the field in Oracle is not truly empty.


Seeing the query text, not just the plan, could help us help you.

Your hash joins are resorting to multiple batches, so increasing "work_mem" (by a lot) could help, if you have the RAM to support it.

Increasing "max_parallel_workers_per_gather" could lead to more parallelism which could be beneficial.

An index on (calltime,imsi) if you don't already have one might help, but given that you are fetching nearly a million rows it might not.

What are you going to do with all these rows once you get them?  Maybe you do aggregation on the server, rather than doing it in the client (if that is what you are doing).


SQL Query suddenly slow --- how to test if its network or hardware (memory) issue?

There is not just a DBA but also a SysAdmin question.  Although, there are some basic DBA tools you should have in use to help you out.

Slow query monitoring:  most databases have tools that allow you to monitor SERVER SIDE for slow queries.  Your system could be indexing, re-allocating memory and a myriad of other server side functions that could interfere with the speed of your queries.  Slow query monitoring will help you start drilling down and isolate.
Basic logging:  Make sure your server date is correlated with your local time (as in Germany +8 from US PST) to make it easy to look up your logs.  I often just (I use Linux based systems) run a tail -f on appropriate logs until the slow queries surface or note times of slow queries and then run through the logs.
Basic network network monitoring:  Setup a constant ping to your server(there are also plenty of bandwidth monitoring tools) that can be setup to alert you for slow traffic between two points as well as longer ping times.

There are different indicators based on your operating system that will show memory issues such as page faults.  Memtester for Linux will stress test your memory and all of it when you direct it to.  Memory issues can be sporadic as not all memory is used at all times so access to bad memory can be intermittent.


Header and line item data source mismatch

If you want to de-normalize this into a single fact table, the fact table is going to be about line items. Therefore, the facts from DataSourceAHeader need to be split up and distributed to the relevant line items so they are not duplicated. As it is currently presented, that means dropping your total order cost and calculating this by summing the line item costs.

The DataSourceAHeader dimension keys (e.g. order date) can be taken from DataSourceAHeader and applied to the fact rows generated from DataSourceBLine. In the example there doesn't seem to be any info contained on DataSourceALine which isn't already included on either DataSourceAHeader or DataSourceBLine, but if there is this can be mapped across in a similar way.

This approach relies on a number of assumptions, the key one being that all the facts from DataSourceAHeader can accurately be distributed among its constituent line items. If this isn't true, loading two separate fact tables (one for the order and one for the line items) might well be a better approach. The same might be true if there are a lot of questions to be asked about orders, which do not consider line item specific info. This is labelled as "Bad Idea #2" in the article which you've referenced, but I have found that in  certain circumstances, it's actually a good idea.

Finally this assumes that the two data sources are in sync. If they're not, you'll be limiting yourself to loading data at the pace of the slower data source. This might be fine, but needs to be considered in the context of your needs and the difference between the two data sources.

Edit: De-normalizing into a single fact table may significantly impact performance when counting orders, as it's essentially a distinct count, which would be my main reason for considering two separate fact tables.

Edit 2 (in response to question edit):

Here, the issue is that at the most granular level (line) data is incomplete, in as much as not all rows have a cost value. However, the total cost information is available at the next level up (header).
This presents the situation where you cannot derive the higher level from the lower; let’s consider the resulting options:

Have a single fact table at the lowest granularity available (line). This is a non-starter, as we are now relying on the incomplete line data to answer questions at the higher level, which we know we could have answered.
Have a single fact table at the higher granularity (header). This means we can now answer questions at the higher level with the complete data, but can no longer answer questions at the more granular level at all. This may be considered to be acceptable, but in most cases we are throwing away potentially valuable data.
Have two related fact tables, one for the incomplete, more granular data (line) and one for the complete, less granular data (header). This is the ideal solution, as we can now answer questions at the higher level in full, and can give the best possible answer to questions at the lower level, given the incompleteness of the source data.

This question was raised because of doubts about having two related fact tables. The doubts stem from the fact that maintaining and joining two large fact tables can be resource intensive. That's true, and if your most granular information can be used to provide a full description of the situation then using a single fact table is preferable. However, in situations like this where that's not possible, two fact tables are required if you want to preserve as much information as possible.


Running a SQL Server query with as little impact as possible

It rather depends on what the scripts are intended to do...

If you can cope with the occasional dirty read or unrepeatable read issue (or they are truly simple enough that these are unlikely/impossible), also set your transaction handling to the most lenient mode possible with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. This is actually equivalent to WITH(NOLOCK) but covers the entire connection going forward instead of just the one table. There is also SET TRANSACTION ISOLATION LEVEL SNAPSHOT which generally stops your query from blocking other concurrent transactions without the risk of dirty or unrepeatable reads, but that does have some potential IO impact on tempdb.

If your queries are making any updates to the database then note that WITH(NOLOCK) support is going to be removed from UPDATE and DELETE statements at some point (see https://technet.microsoft.com/en-us/library/ms187373.aspx).


Either "index merge" is inefficient that most other techniques because of what it has to do:

Gather PRIMARY KEYs based on one index.
Gather PRIMARY KEYs based on the other index.
Sort each list
Merge the lists
Look up the desired rows based on the PKs
Then move on to whatever else is going on.

Furthermore, "Index merge intersect" is almost always less efficient than a composite index that combines the columns being searched on.  See my discussion here:  http://mysql.rjweb.org/doc.php/index1


Mysqlimport very slow when importing two smaller files

I am thinking of two things to try. If you get faster execution time, then the slowness would be related to the distribution of the data. If you still get slow execution time, it maybe then related to the server configs.

Try to load allelePiecesab file before. If this is slow, then the data in this file has higher cardinality of key fields.
Try to disable the indexes (as this is MyIsam), and load the files in whichever order, then re-enable the keys. If this is faster, then the slowness is related to the updating time of index files after certain growth:

ALTER TABLE allele DISABLE KEYS;
// Do the load
ALTER TABLE allele ENABLE KEYS;


"When disaster strikes" isn't a one-size-fits-all situation. The HADR worksheet from brentozar.com includes multiple categories, each with specific failure scenarios. Then, RPO & RTO are defined for each of those failure scenarios. I'd recommend you use this worksheet, or make one similar to it.

Next, you will want to develop a DR Plan for what you will do when those scenarios arise. DR Plans are organization-wide documents that involve not just data, but also detail failing over applications, infrastructure, etc. This is especially important for a failover to DR that involves data loss. You will want to pre-define who needs to be involved, what actions are needed for failover, and how long it takes to perform those actions. When data loss is involved, one of the most important actors in your DR Plan is identifying who will make the decision to accept data loss & fail over to your DR site. This is usually a management or business decision.

In the event one of these scenarios happens (ex, a Storage outage), that worksheet and DR Plan will guide you on how long you work to resolve the failure situation in your primary data center before beginning failover to DR.

Lets look at an example:

Let's say your RTO is 4 hours during a widespread outage. Your RPO is 20 minutes of data loss, and you are using 10-minute log shipping to your DR site, and this is working as designed. Your DR Plan estimates failover will take 90 minutes. The Director of IT and CTO are designated as being allowed to authorize failover to DR with data loss.

A complete power outage affects your primary data center, and all systems are completely offline. Because your log shipping is up to date, failing over would cause only 10 minutes of data loss--well within your 20-minute RPO goal. You can wait up to 2.5 hours for the power to come back online, and still meet your RTO goal.

The CTO or Director of IT would be charged with monitoring the situation and making the decision if and when to fail over to DR. If they choose to fail over at or before the 2.5 hour mark, you should meet your RTO target. If they wait longer, you would miss your RTO target--that's ultimately a business decision, not a technical one. As a DBA, your job is to provide enough information about the failure (and recovery/failover process) so the designated person can make that decision.


You are killing your own connection with that statement. You need to add and pid <> pg_backend_pid() to exclude the session where you run that statement:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = '$db'
  and pid <> pg_backend_pid();


Database design/structure for address details

This is a spin-off from your option two.  It uses two design patterns, namely class table inheritance and shared primary key.

In addition to having two tables, one for companies and one for persons, have a third table, called contacts, that is a generalization of companies and persons, regardless of type.

Contacts.ID will be the usual kind of primary key, probably some kind of autonumber.  Data that pertains to companies and persons alike can be stored in this table.

In the Companies and Persons table, don't include an ID field to use as a primary key.  Instead, declare Companies.ContactID and Persons.ContactID to be primary keys in their own table, and also references to Contacts.ID.  This has the nice effect that it enforces the one-to-one nature of the IS-A relationships, namely a company is a contact and a person is a contact.  It also means that you can use ContactID in any other table and refer to the contacts and also to the companies table or the persons table, as the case may be.

Data that pertains to person, and only to persons, goes in the persons table.

Data that pertains to companies, and only to companies, goes in the companies table.

You can have a ContactsAddresses table that contains ContactID and AddressID so as to provide a many to many link between contacts (whether persons or companies) and addresses.


Why does COUNT() aggregate return 0 for 'NULL'?

Aggregate functions ignore null values.

So

SELECT COUNT(cola) AS thecount
FROM tablea

is equivalent to

SELECT count(*) AS thecount
FROM tablea
WHERE cola IS NOT NULL;

As all of your values are null, count(cola) has to return zero.

If you want to count the rows that are null, you need count(*)

SELECT cola,
       count(*) AS theCount
FROM tablea
WHERE cola is null
GROUP BY cola;

Or simpler:

SELECT count(*) AS theCount
FROM tablea
WHERE cola is null;

If you want to count NULL and NOT NULL values in a single query, use:

SELECT count(cola) as not_null_count,
       count(case when cola is null then 1 end) as null_count
FROM tablea;


Took a full backup of a running mariadb server and restored, but none of the databases are visible

No matter what approach you take there will be at least a brief downtime.

You have M->S, and want G-G-G, correct?

To get another slave from M->S, stop the slave, clone the disk, change server_id, start them up, let replication catch up.  The master does not go down, but you will have S<-M->S.  But that is still not what you need.

I think (but am not sure) that this is the approach.  (There is not a lot of Galera help here, so I will give you more than nothing.)

Optionally keep read access going to your 1 slave.
Stop the Master.
Bootstrap the Master into the first Galera Node.
Stop the Slave.
Turn the Slave into the second Galera Node.
If you have the other Slave do 4&5 to it.  If not give Galera an empty machine and let it build the 3rd node.

Step 6 could be replaced with setting up a "garbd" node -- This is probably a lot faster since no data is needed.  You could do a Step 7 to build a 'real' node and get rid of the garbd.


You can compare things to NULL.

something=NULL is not an error.

It is a Boolean expression that evaluates to unknown. SQL uses three valued logic. The possible results of a Boolean expression are true, false or unknown.

The WHERE clause only returns rows where the predicate evaluates to true.

Whilst comparing to a literal null with =null is rarely (if ever) useful it is certainly useful to be able to use an col = something predicate against a column which may contain nulls and the semantics of how = something operates against null are the same there.


Should a composite primary key out of 3-4 attributes or a unique artificial ID should be used for the situation below?

I agree with your analysis. Using (MatriculationNumber, ExamID, Date) as the key of TestResult will, indeed, allow each person to take the test once per day at most.

What to do about it depends on the problem domain - what is and is not allowed in real life. If a person is not permitted to resit an exam immediately your initial model is fine. You've indicated you would like to accommodate this, so further data must be captured to support it.

The easiest change would be to stop using DATE and start using DATETIME1. This will allow separate results down to the resolution of the clock. Inside the computer that's about microseconds. In real life, say, one minute. Can a student complete an exam twice inside sixty seconds? No - good; dust hands; job done.

Let's say we must retain the DATE column, because < reasons >. Then some other attribute must be added. I'll propose a new column called attempt_number. And because I must know this value to uniquely identify a row it becomes part of the table key. It now has a four-column composite key on (MatriculationNumber, ExamID, Date, attempt_number).

What values should my proposed column allow? One option is it resets each day. On Monday I have attempts 1, 2 and 3. On Tuesday I have attempts 1 through 7 (busy day). On Wednesday I start again at 1 and pass on attempt 4. Another option is that it resets per exam - I start with attempt 1 on Monday and I pass at attempt 14 on Wednesday. Yet another is it resets per student: this exam took attempts 43-45 and 47-57 because I have already had 42 goes at other exams before I started this one and I attempted a different exam on Monday afternoon.

There is no right or wrong option here. Each is valid. Each makes some queries easier and some harder (e.g. "how many times in total did Joe sit exam Alpha" versus "how many times did Joe sit exam Alpha on Tuesday"). The choice would be governed by the real-world practices you are modeling.

There is one further option for values of attempt_number - that they are global across all students, exams and dates. The first time anyone sits any exam it is given attempt number 1, the next is 2 and so on. attempt_number is, in fact, unique through the whole table. This is a good point to introduce some nomenclature.

A key is any column or set of columns whose values uniquely distinguish one row from all other rows in that table. Because multiple sets of columns can be unique each set is called a candidate key. In formal relational theory a relation must have at least one candidate key and can have more. Relational DBMSs (the physical implementation of relational theory) however typically allow tables (the implementation of relations) to exist without any key defined. Keys that consist of columns that occur naturally in day-to-day user conversation are termed natural keys. Every student will know her matriculation number. Every examiner will know the exam's ID. If you say "this is my 4th attempt today," that will be undestood. These could be used in natural keys. Sometimes we introduce new columns for performance or design considerations. If these new columns summarise and stand in for other candidate keys they are called surrogate keys. They are system-generated an internal. They are not well-know to people in the domain. No-one would understand if you exclaimed "I passed on ResultID 8720982!"

Thinking further about an attempt_number that is global across the whole TestResult table i.e. every row has a different value. Since knowing one value uniquely identifies a row it is now a candidate key. Since it stands in for the natural key (MatriculationNumber, ExamID, Date, < something else >) it is also a surrogate key. This is what I think you mean with your ResultID column. If we implement ResultID in this way, my proposed attempt_number is redundant and can be forgotten.

Surrogates have the advantage that they are shorter than the natural keys they represent. This saves space at the "child" end of a foreign key, for example. Sometimes it is just not possible to uniquely identify an entity by natural values. A person is the canonical example. They do have great flexibility as we as programers define them and can define their range and semantics to suit whatever purpose we choose. The drawback of surrogates is that UI input, report output and data transfers invariably need the "natural" values, so the foreign keys have to be traversed in these circumstances.

For the example given, I would implement ResultID as a surrogate ID. It's values will be global across the table. This will give flexibility to extend the schema easily in the future. I would also change DATE to DATETIME so a believable natural key also exists.

1 Don't ever name a column the same as a datatype. This column will be exam_start_time or somesuch.


MySQL case sensitive table names on Linux

Just altering the lower_case_table_names setting isn't enough.  It needs to be done before you import your database(s).

The MySQL 5.1 documentation lists a procedure for moving between Windows and Linux/UNIX.  This will ensure that your desired rules for enforcing case sensitivity are followed.  Take a look and verify that you did these steps in the correct order:

  To convert one or more entire databases, dump them before setting
  lower_case_table_names, then drop the databases, and reload them after
  setting lower_case_table_names:

  1 - Use mysqldump to dump each database:

  mysqldump --databases db1 > db1.sql

  mysqldump --databases db2 >
  db2.sql

  ... Do this for each database that must be recreated.

  2 - Use DROP DATABASE to drop each database.

  3 - Stop the server, set lower_case_table_names in the [mysqld] section of your \etc\mysql\my.cnf file, and restart the server.

  4 - Reload the dump file for each database. Because lower_case_table_names
  is set, each database and table name will be converted to lowercase as
  it is recreated:

  mysql < db1.sql

  mysql < db2.sql


Is it a good practice to create a database for each year?

It really depends how your HR/, Payroll application presenting it to the users. I can imagine that can be done in completely ergonomic way, so basically it would be only integration problem for some kind of external analytics. And it probably may have advantage, past year databases may be put on read only mode, and that current year databases would be smaller than big, aggregate database..
But saying this, I would like to say, I've never seen any good system for such kind of database model.
Some random thoughts:

It is troublesome in every day use, as it is quite common some operations have to change databases records even in the past, at least for short time during years change
It is very hard to use such systems in external analytics, based on normal tools: instead writing queries on database with time periods as filters, you have to manage several database connections and aggregate statistical data outside, probably using temporary tables or even additional data warehouse
Some reports may be required, for time periods which not agree with years change. For example for accounting purposes.
Exporting duch data into other systems, for example when changing HR platform to other products, is nightmare in such scenario.
Upgrade. Usually is performed on present database. Will you upgrade historical ones as well? If yes - what I advantage of having it separately? If not - you will end with several incompatible database schemas.
Maintenance. It may be a lot of effort put to maintain several databases instead of only one. It is known issue.


Changing PostgreSQL port using command line

Since you're on Windows and are probably starting PostgreSQL as a Windows service, you'll need to edit postgresql.conf (inside your data directory) to set the new port there, then restart the postgresql service using the Services control panel or (as an Administrator) the net service command.

You can use a programmatic text editing tool to change postgresql.conf if you need to automate this. On a Linux/unix box you'd use a tool like sed or awk, but on Windows I imagine you'll be using VBS, powershell, or an add-on tool like Perl.

You'll need to change the connection settings in tools like PgAdmin-III to use the new port.

If you were starting PostgreSQL manually using pg_ctl you could set the PGPORT environment variable or pass a config option on the command line. This won't work when you're launching it as a Windows service.


Is there a complete list of Online operations for each SQL Server version?

Sounds like an undertaking. Online index operations are not available in every edition of Microsoft SQL Server, hopefully this can get you started (it's in table form):

SQL Server 2012:
http://msdn.microsoft.com/en-us/library/ms190981.aspx

SQL Server 2008R2: http://msdn.microsoft.com/en-us/library/ms190981(v=sql.105).aspx

SQL Server 2008: http://msdn.microsoft.com/en-us/library/ms190981(v=sql.100).aspx

SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms190981(v=sql.90).aspx


Data History Table vs Using a Current Record Flag

If we're explicitly discussing Slowly Changing Dimensions in a Data Warehouse scenario and you're following the Kimball methodology, there's actually more than 2 different ways of doing this.

The Kimball methodology has 7 different ways of recording SCDs (scroll down to Slowly Changing Dimension Techniques in the link). Types 1, 2 and 3 are apparently the most common, I don't really know of anyone using 5, 6 or 7.

The history table being Type 4, is the one I currently use. It allows us to keep the 'live' table as compact as it can be. We rarely query the history tables, so they're really just there for when we need to audit the history of a dimension. There is no relationship between this table and any others and it has a surrogate ID as the Primary Key.

I've worked in places where we've used Type 2, which is the Flag method and it can lead to unnecessarily large table growth on a key table. However, that's just my experience!


Configuring transactional replication with updatable subscription via SSMS 2012

updatable subscription is deprecated.

If you still want to implement it, you have to use T-SQL. You wont be able to do it using GUI.

See Updatable Subscriptions for Transactional Replication and Deprecated Features in SQL Server Replication


I'm not a ORACLE user but I've found an interesting article about it:

Oracle SQL GROUP BY – The Complete Guide

Scrolling down till the GROUPING Function you'll find and example on how use GROUP BY with ROLLUP:

Quoted from the article

  There are several SQL functions related to grouping that can help you
  identify and work with groups.

  Earlier in our examples, we noticed that subtotals had values of NULL
  for the columns that weren’t being considered in the group.

  How can we tell which values are subtotals and which values are NULL
  because of the underlying data?

  This is what the GROUPING function can help with.

  The GROUPING function takes a single column as a parameter. The output
  is either 1 or 0:

  It returns 1 if the column is being used as part of a subtotal and is
  showing NULL. It returns 0 if the underlying value is NULL or any
  other value.

  You can also order by these columns, to have the subtotals at the
  start or the end, instead of in the middle of the results.

According to this you could use the next syntax to produce the expected result:

SELECT
    Year,
    Month,
    SUM(Amount) as AmoutOfMone
FROM
    YourTable
GROUP BY ROLLUP (Year, Month)
ORDER BY
    GROUPING(Year) DESC,
    GROUPING(Month) DESC,
    Year ASC,
    Month ASC;

Due this question has a previous question here, I've used my fiddle to add this new information and it works properly.

/* aggregated sales by year-month */
SELECT
    T.CALENDAR_YEAR AS Year,
    T.CALENDAR_MONTH_DESC AS Month,
    SUM(S.AMOUNT_SOLD) AS AmountOfMoney
FROM
    SALES S
JOIN
    TIMES T
    ON T.TIME_ID = S.TIME_ID
WHERE S.PROD_ID = 1
GROUP BY ROLLUP (T.CALENDAR_YEAR, T.CALENDAR_MONTH_DESC)
ORDER BY GROUPING (T.CALENDAR_YEAR) DESC,
         GROUPING (T.CALENDAR_MONTH_DESC) DESC,
         CALENDAR_YEAR ASC,
         CALENDAR_MONTH_DESC ASC;

YEAR | MONTH | AMOUNTOFMONEY
---: | ----: | ------------:
null |  null |           450
2018 |  null |           450
2018 |     1 |           100
2018 |     2 |           200
2018 |     3 |           150

db<>fiddle here


SQL Server unique index, per subset of data, based on values of another column

Create a unique constraint over two columns.

alter table dbo.Emails add constraint UQ_Emails unique (CustomerId, EmailValue);

This will prevent duplicate emails per customer. Different customers can still have the same email.

From comments:

  What if I have an IsDefault field too? In that case, the requirement would be:

  There should be only one active email per customer, and each customer should not have duplicate emails"

You can do that if you add a filtered unique index:

create unique nonclustered index UX_Emails
on dbo.Emails (CustomerID)
where IsDefault = 1;


How to convert .rda database to excel workbook

Just because a file is names ".RDA" does not guarantee it is Oracle.
It could be Oracle, but then it would/should come with a whole infrastructure, not just a datafile.
And if it is Oracle, then you'll need Oracle to get the data out.
You may be able to use the EXPRESS EDITION depending on how official this is.

You need to speak to the 'owner' of the RDA file.
Where did it come from, how did you get it, what is  supposed to be in it?

Personally I would just use an Editor with HEX extension (Notepadd++, Textpad,...) to eyeball the contents.

Hein


SQL Server error importing an Excel sheet

After some careful thought and some experimentation, the AccessDatabaseEngine.exe installed on the server is probably part of the problem. There are actually two versions 32 bit and 64 bit. On my computer 64 bit, I have the 32 bit version of Office installed. The SQL Server on my computer is 64 bit. When I attempt to use openrowset with the same OLEDB provider on a spreadsheet, I get the same error as you. I can't install the 64bit version, without removing the 32 bit version of Office.

Here are some links to two different versions. The second one has both the 32bit and 64bit.

Access Database Engine 2007 32 bit

Access Database Engine 2010 32 bit and 64 bit

The lesson here is; be sure to install the version 32 bit or 64 bit that matches your SQL Server install. If you have a 64 bit server and 32 bit MS Office components installed, you won't be able to install the 64 bit version until you remove 32 bit MS Office.

Once you have the Access Database Engine that matches your SQL Server (64 bit or 32 bit), you can test the provider directly using a query to see if you can import from the spreadsheet.

OLE 12.0

SELECT *
FROM OPENROWSET
('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\somefile.xls;
HDR=YES', 'select * from [name_of_spreadsheet_in_file$]');

OLE 4.0

SELECT *
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\somefile.xls;HDR=YES',
'select * from [name_of_spreadsheet_in_file$]');


Prevent logins loss when restoring a database

The above issue which you are facing is commonly known as the case for Orphan users:

What commonly happens with SQL authenticated logins and database users on a restore is that the SIDS will be out of sync, thus breaking the relationship. This relationship must be repaired before you can connect to the database using that login, because in the eyes of SQL Server those principals are no longer connected.

On how to fix this you can refer Fixing Orphaned Users

Or as an alternative you can also make use of SP_help_revlogin

1) Run the sp_help_revlogins against the MASTER on SOURCE SERVER, and get the script of "creating logins".

2) During the restoring the backup to the DESTINATION SERVER, you can run the "creating logins" script against the MASTER database on the DESTINATION SERVER.

3) After the restoration DONE, please EXEC sp_change_users_login 'REPORT' against the RESTORED database.


Sharded DB accidentally created during update of MongoDB from 2.4.4 to 2.6.4

If all the other components are upgraded, then there will be no impact to the sharded database caused by upgrading the remaining primary.  The important pieces for the sharded database/collection are mongos and the config servers rather than the individual members in this case, and you have already done that piece.

If you want to be particularly careful, you can leave the primary on 2.4, step it down and allow a 2.6 secondary to step up to primary (this would be the recommended process anyway, you should always step down a primary to secondary before you upgrade it).  Doing it this way also means you have a very simple way to roll back the upgrade if necessary.  Once you verify no issues, upgrade the old priamry (now secondary) and you are done.

One caveat - stepping down a primary will force close all active connections (mongos, config servers mainly) to that primary, and they will re-establish connections to the new primary once it is elected.  This is expected (and necessary in fact) behavior, but can cause a brief spate of errors (for example for a long running query from that shard, which would error).


MariaDB 10.1.38 - Specified key was too long; max key length is 767 bytes

Ok so after a weekend long of research the answer is very simple and i'm going to leave it here for others facing this same problem.

This answer is tested on MariaDB 10.1.38, db charset utf8mb4, table type InnoDB and table charset utf8mb4_unicode_ci:

Open my.ini and add this lines(if they already exist just edit everything after =) right after [mysqld]:

innodb_file_format = Barracuda
innodb_file_per_table = on
innodb_default_row_format = dynamic
innodb_large_prefix = 1
innodb_file_format_max = Barracuda

You can also set them trough the cmd/terminal:

On windows - if your XAMPP is located at c:\xampp\:

Open up a cmd and navigate to mysql's bin - in this case:

cd c:\xampp\mysql\bin

Autenticate:

mysql -h localhost -u root

Once you're authenticated run this queries(one at a time):

SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_per_table = on;
SET GLOBAL innodb_default_row_format = dynamic;
SET GLOBAL innodb_large_prefix = 1;
SET GLOBAL innodb_file_format_max = Barracuda;

But i recommend sticking to editing the my.ini(or .cnf) since if you are going for queries your options might by rewritten every time you restart your Apache.

You can read more on the InnoDB storage format: here

Also this article is very interesting in helping you to optimize your varchars brefore creating the unique keys: here


How to execute MySQL query after 1 minute

You could create an event.

Rather than SLEEP in the same statement as an update try: 'SELECT SLEEP(360); UPDATE ....'.


What to do about upgrading "main" cluster from 8.4 to 9.1 when a 9.1 "main" already exists?

If the existing main is the default cluster that was created with your installation you can just drop it. Use the tool provided by your installation:
pg_dropcluster --stop 9.1 main

More info: man pg_dropcluster
There is nothing special about the main cluster. It's just a cluster like any other which happens to be the default in your installation.
If you haven't worked with the cluster yet, there is nothing in there, that you would lose. But check with psql or pgAdmin (or a client of your choosing) to make sure.


Case Behavior Changed Between 11.2.0.1 and 11.2.0.2

Can reproduce on the 11gR2 database hosting apex.oracle.com

The following query results in 3 rows:

SELECT t1.*, t2.*, t3.dummy, case when 1=1 then 'a' else 'b' end "Case/Decode"
FROM t1
LEFT JOIN (select 1 dummy from dual) t3 ON t3.dummy = t1.id1
LEFT JOIN t2 ON t1.id1 = t2.id2
WHERE (t2.id2 IS NOT NULL or t3.dummy IS NOT NULL)

Removing the CASE selection gives two rows and changes the value of t3.dummy from '1' to null.

Using the EXPLAIN PLAN, you can see that adding the CASE removes the FILTER

"T2"."ID2" IS NOT NULL OR CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NOT NULL"

I suspect the optimizer is rejigging the link to dual. An outer join to dual, performed in a inline view, is very odd thing so I suspect it won't affect many people.


You're trying to restore a database named [@DBName] (that's the literal name since it is surrounded by square brackets) onto files for the database named [test-db].  Remove the [ and ] from the [@DBName] piece of your code and you'll see you cannot use variable names in a RESTORE DATABASE command.

This needs to be performed with dynamic SQL.  The list of files to move should be generated by looking at RESTORE FILELISTONLY FROM DISK = 'C:\some_db_backup.bak';

Here is a very basic example of how to generate a RESTORE DATABASE command using dynamic SQL, for SQL Server 2005 to SQL Server 2012 (i.e. this is not tested on 2014, and may or may not work without modification there):

SET NOCOUNT ON;
DECLARE @FileListCmd NVARCHAR(MAX);
DECLARE @RestoreCmd NVARCHAR(MAX);
DECLARE @cmd NVARCHAR(2000);
DECLARE @BackupFile NVARCHAR(MAX);
DECLARE @DBName SYSNAME;

DECLARE @Version NVARCHAR(255);
DECLARE @VersionINT DECIMAL(10,2);

SET @BackupFile = 'C:\temp\backup.bak';
SET @DBName = 'SomeNewDatabase';

SET @cmd = '';
SET @Version = CONVERT(NVARCHAR(255),SERVERPROPERTY('ProductVersion'));
SET @VersionINT = CONVERT(DECIMAL(10,2),
    SUBSTRING(@Version,1 ,CHARINDEX('.',@Version, 4)-1));
IF @VersionINT >= 10.5
BEGIN
    -- WITH TDE OPTION
    DECLARE @FileList TABLE (
        LogicalName SYSNAME
        , PhysicalName VARCHAR(255)
        , Type CHAR(1)
        , FileGroupName SYSNAME NULL
        , Size NUMERIC(20,0)
        , MaxSize NUMERIC(20,0)
        , FileId BIGINT
        , CreateLSN NUMERIC(25,0)
        , DropLSN NUMERIC(25,0) NULL
        , UniqueId UNIQUEIDENTIFIER
        , ReadOnlyLSN NUMERIC(25,0) NULL
        , ReadWriteLSN NUMERIC(25,0) NULL
        , BackupSizeInBytes BIGINT
        , SourceBlockSize INT
        , FileGroupId INT
        , LogGroupGUID UNIQUEIDENTIFIER NULL
        , DifferentialBaseLSN NUMERIC(25,0) NULL
        , DifferentialBaseGUID UNIQUEIDENTIFIER
        , IsReadOnly BIT
        , IsPresent BIT
        , TDEThumbprint VARBINARY(32)
    );
END
ELSE
BEGIN
    --NO TDE OPTION
    DECLARE @FileListNoTDE TABLE (
        LogicalName SYSNAME
        , PhysicalName VARCHAR(255)
        , Type CHAR(1)
        , FileGroupName SYSNAME NULL
        , Size NUMERIC(20,0)
        , MaxSize NUMERIC(20,0)
        , FileId BIGINT
        , CreateLSN NUMERIC(25,0)
        , DropLSN NUMERIC(25,0) NULL
        , UniqueId UNIQUEIDENTIFIER
        , ReadOnlyLSN NUMERIC(25,0) NULL
        , ReadWriteLSN NUMERIC(25,0) NULL
        , BackupSizeInBytes BIGINT
        , SourceBlockSize INT
        , FileGroupId INT
        , LogGroupGUID UNIQUEIDENTIFIER NULL
        , DifferentialBaseLSN NUMERIC(25,0) NULL
        , DifferentialBaseGUID UNIQUEIDENTIFIER
        , IsReadOnly BIT
        , IsPresent BIT
    );
END

SET @FileListCmd = 'RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile + ''';';

IF @VersionINT >= 10.5
    INSERT INTO @FileList
    EXEC (@FileListCmd);
ELSE
BEGIN
    INSERT INTO @FileListNoTDE
    EXEC (@FileListCmd);
    INSERT INTO @FileList
    SELECT *, NULL
    FROM @FileListNoTDE;
END

DECLARE @MoveFiles NVARCHAR(MAX);
SELECT @MoveFiles = (SELECT ', MOVE ''' + fl.LogicalName + ''' TO '''
    + 'C:\Database\Data\' + @DBName + '\'
    + CASE WHEN fl.FileGroupName = 'PRIMARY' THEN 'system'
      WHEN fl.FileGroupName IS NULL THEN 'Log'
      ELSE fl.FileGroupName END + '\' + fl.LogicalName + '''
    '
FROM @FileList fl
FOR XML PATH(''));

SET @MoveFiles = REPLACE(@MoveFiles, '&#x0D;', '');
SET @MoveFiles = REPLACE(@MoveFiles, CHAR(10), CHAR(13) + CHAR(10));
SET @MoveFiles = LEFT(@MoveFiles, LEN(@MoveFiles) - 2);

SET @RestoreCmd = 'RESTORE DATABASE ' + @dbName + '
FROM DISK = ''' + @BackupFile + '''
WITH REPLACE
    , RECOVERY
    ' + @MoveFiles + ';
GO';

PRINT @RestoreCmd;


What are the ports and directionality of the TCP connections used in MySQL replication?

Your assumptions are incorrect.

The slave does not bind to 3306 (or whatever port it is listening to) when making its outbound connection to the master, and the port the slave is "listening to" for inbound client connections is otherwise irrelevant in replication.

The slave establishes a TCP connection from a high port to the master's port (e.g. 3306), authenticates itself, optionally does a little bit of session setup (for example, telling the master its desired heartbeat configuration), and then requests a binlog stream from the master, beginning at a specific file and byte position.

The master responds by beginning to stream binlog events beginning at that position (assuming it's valid) and continuing to autonomously send new binlog events to the slave over that same TCP connection as those events are generated on the master.  The master never makes a connection back toward the slave unless both machines are slaves to each other in a circular replication configuration where each of them is the other's slave.

The single TCP connection, originated by the slave, is the pipeline for transferring binlog events and the master will continue streaming events as long as it thinks this TCP connection is established.  If the connection is severed for any reason, the slave is responsible for reconnecting to the master, with the same process as before, and requesting a new start point for the binlog stream, based on the last valid event it received before it lost connectivity.

Ever server has a server_id global variable.  If a master accepts a connection from a slave that identifies itself with the same server_id as one that is already connected, the master will forcibly close those already-existing connections, preventing the possibility of the master wasting resources streaming traffic into a black hole because of network issues that make the master think the former connection (which should have been from the same slave) is still established.

So your single access list entry would be "TCP, from slave IP, high random port -- to master IP, port 3306" (assuming the master is listening on 3306).

Additional details:

The replication io_thread is the thread in MySQL that creates the outbound TCP connection to the master, receives the replication events over that connection, and writes them to the "relay log" (file) on the slave.  It doesn't "process" the replication events, it stores them for execution by the sql_thread.

The sql_thread on the slave then reads the relay log (file) written by the io_thread and executes the events found there.  It appears like any other client thread in SHOW PROCESSLIST but it is not created via a connection to port 3306 on the slave, because it's a thread created inside the MySQL process when you START SLAVE.

There is no reason a thread that's running inside the MySQL process would need to make a TCP connection to the same process -- it's already running inside the MySQL server and has access to the server internals to execute the replication events received from the master and stored to the relay log.


Get rows changed between two dates (latest and previous)

You need to find both times, one using what you have (LIMIT 1) and the second using LIMIT 1 OFFSET 1.

I'd also use ORDER BY ... DESC, not ASC. Your wording suggests you want the rows between the last 2 appearnces of 'user':

SELECT *
FROM user
WHERE lastmodifieddate >
        (SELECT sync_time
         FROM _sync_history
         WHERE object_name = 'user'
         ORDER BY sync_time DESC
         LIMIT 1 OFFSET 1
        )
  AND lastmodifieddate <
        (SELECT sync_time
         FROM _sync_history
         WHERE object_name = 'user'
         ORDER BY sync_time DESC
         LIMIT 1 OFFSET 0
        ) ;


Trigger after insert, insert new row into another table

Thank you Akina!

DELIMITER |
CREATE TRIGGER user_sub AFTER INSERT ON wp_usermeta
FOR EACH ROW
BEGIN
IF NEW.meta_value='subscribe' THEN
INSERT INTO newsletter_user_list SET list_id = 1,user_id=NEW.user_id;
END IF;
END;
|
DELIMITER ;


@ehubba, welcome to stackexchange!

Here are some considerations:

Is there a reason for the GROUP BY?
Why do you use one IN and two EXISTS? Generally you can use IN for smaller and EXISTS for larger results. IN will result in lots of ORs which is not the best idea in large numbers.
I don't like the two sub-selects at the beginning of the statement. Can you replace them with joins?

If you want to improve performance, there are a lot more things to consider...

Are your statistics up-to-date?
constraints help the optimizer to find the best join order
indexes? (please post the explain plan!)

When I write SQL statements I always ask myself "how can I minimize the resultset as fast and efficient as possible".


SSIS: Slowly changing dimension with lists (one-to-many relationship)

I think that in order to keep the logical aspect of a dimension you should use a third table to relate both dimensions:

PersonScd                     GroupOfCars               CarScd
---------                     -------------              ------
PersonBK      <--------->                       <----->  PK_Car
Unique_Person_code                                       Unique_Car_code
First Name                     Unique_Car_code            Year
Last Name                      Unique_Person_code         Make
Age                                                       Model
Street Address                                            Color
City                                                      EffectiveDate
State                                                     ExpirationDate
EffectiveDate
ExpirationDate

*
Unique_Car_code and Unique_Person_code are not foreign keys but unique_numbers used to identify a unique car or a unique person.


With PostgreSQL you have to use the following if the rows are not identical considering all columns:

SELECT DISTINCT ON (column1, column2) * FROM ...


Using a 512 bit binary column as a bit field

SQL Server supports up to 1,024 Columns per nonwide table. Use them. The bit data type, if declared as NOT NULL, will take one bit of storage. You can then address each state specifically by name, making for cleaner, more maintainable code.


Handling exceptions in stored procedures called using insert-exec blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

  Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );


conversion of high_value to date datatype

I think there is another solution to this.

DECLARE

   CURSOR c1 IS
   SELECT
      HIGH_VALUE
   FROM
      USER_TAB_PARTITIONS
   WHERE
      TABLE_NAME = 'MYTAB';

   ls_sql_statement   VARCHAR2(1000);
   ld_date_val        DATE;

BEGIN

   FOR c1rec IN c1 LOOP
      ls_sql_statement := 'SELECT '||c1rec.HIGH_VALUE||' FROM DUAL';
      EXECUTE IMMEDIATE ls_sql_statement INTO ld_date_val;
      --Do more stuff here with ld_date_val
   END LOOP;

END;
/


how to import an existing database into SQL server 2012?

Books_log is the log file for the database, i.e. all changes you make to the database are logged to be able to rollback, etc. To be able to use an existing mdf, you can use attach, right click on "Databases", click attach and select your mdf.


Bulk database update/insert from CSV file

There's a nice technology available in Oracle called External Tables. In your scenario, you could access your external plain-text data using External Tables from within the database and update your existing data in database with SQL statements you love and are used to – for example, INSERT, MERGE etc.

In most cases, using Oracle supplied utilities is the best way to perform ETL. And because your question sounds more like administrative one I suggest you to look at my previous post on DBA Stack Exchange "Update Oracle database from CSV".

UPDATE: This approach works pretty well for reading external data in database. Generally, you define external data format every time you need to process the plain-text file which has new format. Once external table is created you can query it just like a real database table. Whenever there is a new data to import, you just replace the underlying file(s) on the fly without need to recreate external table(s). Since external table can be queried as any other database table, you can write SQL statements to populate other database tables.

The overhead of using External Tables is usually lower compared to other techniques you would implement manually because this technology was designed with performance in mind taking into account the Oracle Database architecture.


MS SQL FileTable CreatedBy and LastModifiedBy

I was able to solve the problem by using instead triggers.

First, a FileTable has a fixed schema therefore new fields are not allowed. I've created a separate table to store authoring information in a 1-to-1 relationship keyed by 'path_locator'.

Using triggers, I was able to detect authoring information and store it.

When copying, deleting and renaming files, the authoring information (created by, modified by) can be detected by using suser_sname(). However when editing the document in Word, Excel, Notepad, there are scenarios where the suser_sname() will return 'dbo' instead of the credentials of windows credentials. I am not sure why this happens but I worked around it by joining to sys.dm_filestream_non_transacted_handles to get username from there. See code snippet below:

CREATE OR ALTER TRIGGER Authoring_Update ON dbo.DBADocuments AFTER UPDATE AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;

    UPDATE dbo.FileMetadataTable SET ModifiedBy = ISNULL(s.login_name, suser_sname())
    FROM dbo.FileMetadataTable f
    JOIN Inserted i ON i.path_locator = f.PathLocator
    LEFT JOIN sys.dm_filestream_non_transacted_handles s ON f.PathLocator = s.item_id
    LEFT JOIN dbo.FileTable ft WITH (NOLOCK) ON s.item_id = ft.path_locator
END


Deploying Oracle ADW Changes from development environment to production

Key Infrastructure Pieces

Code Repository/Version Control System
Separate (and licensed) Dev/Test/Prod environments

You seem to be missing the Test environment.
Test Environment
I highly recommend using a Test environment.  One advantage is: You can use the actual Schema's name irregardless of environment.
One way to use the Test environment:

Clone Prod to Test

usually a clone of a pluggable database

apply Change scripts
perform Unit Tests

Automatic Test Scripts
There is a PL/SQL based Unit Testing package on GitHub ( utPLSQL ).
But, if you want an Oracle product, SQL Developer has Unit Testing capabilities.
You'll most likely need to create the scripts that interface with your automation software.
Automatic Schema Changes
Your second link mentioned LiquiBase.  This is now integrated into SQL Developer. Demo for Dev Schema to Prod Schema is available here.
You will use the new command line interface (SQLcl) for automation, not SQL Plus.
Another alternative (for new tables) is the SQL Developer Cart feature. It can also work well for creating the INSERT scripts for the small Dimension Tables.
Code Review
One thing you may want to incorporate into your process is a way to automate parts of the Code Review process.  I believe the key words to search in Google/Bing are "code sniffer".
SQL Developer has a custom highlighting feature that I use to detect "bad code".
Example

TO_DATE() without a format.
commit inside of a loop.
when others then null ( other forum ).

The review process is manual for me.  But, the custom highlighting helps the human code reviewer.
TL;DR
You need to look at SQL Developer's set of features.


How to select columns in SQL server 2008 using WHERE and IN, having 2 columns in the where clause

I would do something along the lines of

select * from tbl where col 1 in (x1,x2) and col 2 in (y1,y2)

Unless you are looking for specific combinations such as xy and y1 together, you didn't specify enough in your question.


T-SQL to Group time interval change by date range in sql server

there is an example , from Itzik Ben Gan (Gaps and Islands in Sequences)
This is base on this article: Gaps

DECLARE @vt_Source AS TABLE
( ts datetime NOT NULL PRIMARY KEY,
 interval tinyint NULL
)

INSERT INTO @vt_Source(ts, interval)
VALUES('2016-12-31 00:28:00',     NULL)
,('2016-12-31 00:29:00'  ,   1)
,('2016-12-31 00:30:00'  ,   1)
,('2016-12-31 00:45:00'  ,   15)
,('2016-12-31 01:00:00'  ,   15)
,('2016-12-31 01:15:00'  ,   15)
,('2016-12-31 01:16:00'  ,   1)
,('2016-12-31 01:17:00'  ,   1)
,('2016-12-31 01:18:00'  ,   1)
,('2016-12-31 01:19:00'  ,   1)

SELECT
   min(ts_prev) AS startDate
  ,max(ts) AS endDate
  ,interval
FROM
   (SELECT
         ts
         ,interval
         ,ROW_NUMBER() OVER(ORDER BY ts ASC) AS  rn_all
         ,ROW_NUMBER() OVER(PARTITION BY interval ORDER BY ts ASC) AS rn_group
         ,LAG(ts,1,ts) OVER(ORDER BY ts ASC) AS ts_prev
    FROM
        @vt_Source
   )A
WHERE
   A.interval IS NOT NULL
GROUP BY
   rn_all - rn_group
   ,interval
ORDER BY
   startDate ASC

output for this:

startDate           endDate          interval
31/12/2016 00:28:00 31/12/2016 00:30:00 1
31/12/2016 00:30:00 31/12/2016 01:15:00 15
31/12/2016 01:15:00 31/12/2016 01:19:00 1

I added a WHERE clause to eliminate the first row , the one that have NULL on interval column

http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=fe1888d0a934d73de0ed9887aaf4d482


Remove substring from one column and put it on other column

UPDATE mytable
SET column2 = SUBSTRING(column1, 3 + LENGTH(SUBSTRING_INDEX(column1, ' - ', 1))),
    column1 = SUBSTRING_INDEX(column1, ' - ', 1)


Query tuning update with hash flow distinct operator

You have three main problems:

There is no useful index to support the join on id.
The TOP (100) introduces a row goal, so estimations may be too low.
The UPDATE is non-deterministic.

Multiple rows from table2 could match on id, so it is not clear which matching row from table2 should be used to provide values for the OUTPUT clause. The aggregate is there to group on table2 id and choose ANY matching values for the other columns. The aggregate is a Flow Distinct because of the row goal.
One needs to be very careful with ANY aggregates in non-deterministic UPDATE statements because you may get incorrect results.
There is not enough detail in the question to make high quality recommendations, but:

Add an index like CREATE INDEX i ON dbo.table2 (id) INCLUDE (CID, CDate);
Use OPTION (QUERYTRACEON 4138) to disable the row goal, or OPTION (ORDER GROUP) to use a Stream Aggregate instead of Hash.
How you fix the non-deterministic UPDATE depends on the data relationships. The key point is to identify at most one row from the source that matches each target row. Typically, this will involve a unique index or constraint, or using ROW_NUMBER or TOP (1).

Step 2 may or may not be necessary. I add it for completeness.
You may find it easier to visualize the issues and tune the query by writing it in this form:
UPDATE TOP (100)
    xyz WITH (UPDLOCK, READPAST)
SET xyz.flag = 1
OUTPUT
    inserted.Rcode, inserted.EDR, inserted.id,
    abc.EID, abc.CID, abc.ENID, abc.Cdate
FROM dbo.table1 AS xyz
CROSS APPLY
(
    -- At most one source row per target row
    SELECT TOP (1)
        abc.*
    FROM dbo.table2 AS abc
    WHERE
        abc.id = xyz.id
    -- ORDER BY something to choose the one row
) AS abc
WHERE
    xyz.flag = 0;

Execution plan:

I probably wouldn't bother with a filtered index on table1, but if you did want to try it, this appears to be suitable:
CREATE INDEX i
ON dbo.table1 (Recid)
INCLUDE (id, flag)
WHERE flag = 0;

If you want to continue with the update syntax given in the question without addressing all the underlying issues properly, you may find this is faster:
UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc.ENID,abc.Cdate
FROM dbo.table1 xyz WITH (UPDLOCK, READPAST)
INNER JOIN dbo.table2 abc WITH (NOLOCK, FORCESEEK)
on xyz.id=abc.id
WHERE xyz.flag = 0;


Count total where owner_user_id = some_value and deleted_by_owner = 0 or to_user_id = some_value and deleted_by_to = 0

Unless I'm misunderstanding your question, I think this solves your problem:

SELECT Count(*)
FROM default_message_users
WHERE (owner_user_id = @SomeValue and deleted_by_owner = 0)
    or (to_user_id = @SomeValue and deleted_by_to = 0)

Note the addition of ( and ) to denote the order of operations for the AND and OR operators.

EDIT:  As andry-m pointed out in the comments below my answer, order of operations dictates that AND conditions will be evaluated prior to OR conditions. My intention with this answer is that in more complicated cases it is easier to explicitly define the desired logic rather than to rely on the implicit order of operations.


mixing binlog formats between master and slave

It might be in the manual:

  Each MySQL Server can set its own and only its own binary logging
  format (true whether binlog_format is set with global or session
  scope). This means that changing the logging format on a replication
  master does not cause a slave to change its logging format to match.
  (When using STATEMENT mode, the binlog_format system variable is not
  replicated; when using MIXED or ROW logging mode, it is replicated but
  is ignored by the slave.)

-- http://dev.mysql.com/doc/refman/5.6/en/binary-log-setting.html

That is "Yes" to your questions.

Note that ROW is the default for 5.7.  That is, ROW has proven itself to be the 'right' way to do replication.  Galera clustering can't even work without ROW.


Should Amazon RDS SQL Server 2017 Web Edition Max Memory settings be different from that of a locally installed instance?

No, I wouldn't treat this any differently than you would an on-prem instance with regards to max server memory.  The Windows Server 2012 box is almost certainly a VM, but hopefully Amazon is a managing that well to avoid VM problems.

I would definitely reserve 3 GB at minimum for the OS and other overhead.


Is it possible to force a query to ignore the server option "remote proc transaction promotion" so it doesn't promote remote proc transaction?

Is there a query hint or some way to force it to not promote a remote procedure transactions?

No there isn't, but if you're currently doing something like this:

INSERT #Temp
EXECUTE schema_name.procedure_name AT linked_server;

or

INSERT #Temp
EXECUTE linked_server.db_name.schema_name.procedure_name;

You could work around it using:

INSERT #Temp
SELECT column_list
FROM OPENQUERY(linked_server, 'EXECUTE db_name.schema_name.procedure_name');

Assuming you're using a simple procedure that returns a single result set. You may also need to add SET NOCOUNT ON to the procedure as I mentioned in answer to Using OPENQUERY to execute a script.


Why running queries one by one yields correct results, but running lots of them in one chunk doesn't?

SELECT populateIfNotAlreadyIn(1, 'val1'),
       populateIfNotAlreadyIn(2, 'val2'),
       populateIfNotAlreadyIn(3, 'val3'),
       populateIfNotAlreadyIn(4, 'val4'),
       populateIfNotAlreadyIn(5, 'val5') ;

Meanwhile, you could shorten the function to just

RETURN EXISTS(SELECT * FROM t WHERE id = idArg);


Do you also need the Datacenter or Enterprise Edition of Windows
  Server 2012 or 2016 for Always on, or can you set this up properly on
  Windows Server Standard 2012 or 2016?

Windows Server Standard Edition is fine.

Starting with Windows Server 2012 every feature of Windows Server is available in Standard Edition.  In particular Standard Edition supports Windows Server Failover Clustering which is needed for some Always On capabilities.


Questions and answers database design for Core Data

So, this is

an MCQ (Multiple Choice Questionnaire)
a test where users will only take it once
there are time constraints

I think that you've over-engineered on your first pass - you woudn't be the first! :-)

A preliminary proposal would be be as follows:

CREATE TABLE Student (Student_ID int, Name VARCHAR(25));

CREATE TABLE Question (Question_ID int, Name VARCHAR(25),
                       Question_Text VARCHAR(64000),
                       Answer1 VARCHAR(4096),
                       Answer2 VARCHAR(4096),
                       Answer3 VARCHAR(4096),
                       Correct_Answer int,
                       Threshold_1 int,  -- time constraints per question
                       Points_T1   int,
                       Threshold_2 int,
                       Points_T2   int,
                       Threshold_3 int,
                       Points_T3   int);

The reason I put the thresholds in with the question is that you have said that they may vary with the question.

CREATE TABLE Student_Response (Student_ID int,
                               Question_ID int,
                               Student_Answer int,
                               Answer_Time int,
                               Answer_Points int);

Now, from here, SQLite doesn't support calculated/computed/generated fields, and from here nor does it support IF or CASE statements in TRIGGERs, so I think you're going to have to manage the calculation of Answer_Points in application code.

This will obviously depend on

whether the answer is correct and
the time taken to answer

You will also need the RI (Referential Integrity) CONSTRAINTs below:

ALTER TABLE Student_Response
  ADD CONSTRAINT fk_sr_question FOREIGN KEY (Question_ID)
    REFERENCES Question (Question_ID);
ALTER TABLE Student_Response
  ADD CONSTRAINT fk_sr_student FOREIGN KEY (Student_ID)
    REFERENCES Student (Student_ID);
 ALTER TABLE Student_Response
  ADD CONSTRAINT uq_sr_student_question
    UNIQUE (Student_ID, Question_ID);

You also said

  User will answer once a question

so, you will need this UNIQUE CONSTRAINT:

ALTER TABLE Student_Response
  ADD CONSTRAINT uq_sr_student_question
    UNIQUE (Student_ID, Question_ID);

.


query that does functionality of listagg without use of it

You can use a recursive common table expression. I have used a tmp table to enumerate the employees:

with tmp (empname, department, rn) as (
        select empname, department
             , row_number() over (partition by department
                                  order by empname) as rn
        from employees
), cte (empname, department, rn) as (
        select cast(empname as varchar(100)) as empname
             , department, rn
        from tmp
        where rn = 1
        union all
        select rtrim(c.empname) || ', ' || t.empname
             , t.department, c.rn+1
        from tmp t
           , cte c
        where t.department = c.department
          and c.rn + 1 = t.rn
)
select empname, department
from cte c1
where rn = (select max(rn)
            from cte c2
            where c1.department = c2.department)


How the sys user gets the sysdba privilege?

From the 12c docs:

  The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. Oracle Enterprise Manager Database Control does not permit you to log in as user SYS without connecting as SYSDBA or SYSOPER.

And you cannot revoke it:

SQL> revoke sysdba from sys;
revoke sysdba from sys
*
ERROR at line 1:
ORA-01998: REVOKE failed: user SYS always has SYSOPER
and SYSDBA

More information from the docs:

  These administrative privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself. Methods for authenticating database administrators with these privileges include operating system (OS) authentication, password files, and strong authentication with a directory-based authentication service.

  These privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion.

and:

  When you grant SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative privilege to a user, that user's name and privilege information are added to the database password file. A user's name remains in the password file only as long as that user has at least one of these privileges. If you revoke all of these privileges, Oracle Database removes the user from the password file.


How do I do a join and exclude certain records?

A unique constraint on TEACHER_ID, STUDENT_ID would prevent duplicate STUDENT_ID for a teacher.  In fact you could just make the the PK and drop ID.

This is TSQL so it may be wrong for mysql

SELECT S.ID
     , S.LASTNAME
     , S.FIRSTNAME
  FROM STUDENTS S
  LEFT JOIN TEACHERS T
        ON T.STUDENT_ID = S.ID
       AND T.TEACHER_ID = $teacherID
 WHERE T.TEACHER_ID is null


is it safe to create mysql database by just creating its directory?

UNSAFE!
Use CREATE DATABASE.

mkdir probably works for ENGINE=MyISAM, but that old engine should not be used anymore.
InnoDB needs to know what is going on.
You noticed db.opt; but what if something else gets added in the future?
MySQL 8.0 has an internal "data dictionary" that would be totally confused by that mkdir.
And who knows what future versions will be like !

It is possible to execute a command on the mysql commandline tool.  This makes CREATE DATABASE a one-liner, albeit more verbose than a simple mkdir.
If you want a table or partition to be on a different physical drive, check out the limited capabilities of specifying the location for the DATA DIRECTORY:  https://dev.mysql.com/doc/refman/8.0/en/innodb-create-table-external.html


Assignment of first group element - MySQL 4.7

This will run so only in MySQL, in MariaDB you will need a LIMIt in the inner SELECT
The principal thing is the ORDER  of the inner SRLCT, which determines , which rownumber (rn) it will get. here i choose ORDER BY id to achieve your result.
But as mustachio pointed out you have to define an order, so that this will work

CREATE TABLE IF NOT EXISTS t1 (
   id_ INTEGER  NOT NULL,
   value1 INT NOT NULL
)

INSERT INTO t1 (id_, value1) VALUES
(1, 1),
(2, 1),
(2, 1),
(3, 1),
(3, 1)

SELECT id_, value1
FROM (SELECT
 IF(id_ =  @id,@rn := @rn +1,@rn := 0) value1
 ,@id :=  id_ as id_
FROM t1,(SELECT @id := 0 ,@rn := -1) t2
ORDER BY id_) t3

id_ | value1
--: | -----:
  1 |      0
  2 |      0
  2 |      1
  3 |      0
  3 |      1

db<>fiddle here


How to Create Database Audit Specification for a column?

I am not sure you can. However it is a bit confusing, MSDN says that it is possible:

http://msdn.microsoft.com/en-us/library/cc280645(v=sql.105)

However following the example that they give I get the following error:

  Msg 33211, Level 15, State 1, Line 0
  A list of subentities, such as columns, cannot be specified for entity-level audits.

The message above seems to be pretty definitive, I have tried their example on 2008, 2008 R2 and 2012 and I get the same message every time.


Why expdp does not include identified by statement?

This is a known bug in 11.2.0.1 which is fixed in the 11.2.0.2 patch set. You should patch the database - or even better upgrade to a version that is supported.

You could create the user in advance before doing the import.


Not able to login on MySQL in AWS RDS using root

I am 99% sure that Amazon RDS does not give you a 'root' access. i.e. You can't create a user as 'root'@'localhost' or 'root'@''.

Can you confirm user you have used while launching Amazon RDS instance? and make sure that Password matches with user created.


My answer is a big YES - you should store business logic in data warehouse. That is one of the ideas of data warehouses in the first place. If you have tens of reports showing or filtering leads imagine if a rule to qualify someone as lead would change. Also, what if you have to access DWH data with different tools/systems - you'd need to replicate all the logic. Lastly, calculating this kind of labels can be time consuming and that's why you'd like to have everything precalculated in DWH for the reports to run fast.

To answer your first question - yes, you should go with SCD. There are couple apporaches and you should choose the one that suits best your requirements - it's all about how users want to analyze data (this is where the desing should start.


How to design two tables: A hasMany B, and one B per A has state 'active'?

You can use a partial unique index on the branches table:

create table branches
(
  id integer primary key,
  env_id integer not null references environments,
  active boolean not null,
  ... other columns ...
);

create unique index on branches (env_id)
where active;

Now for each env_id, there can be many different rows with active = false, but only one with active = true.


Temporary replication of data between continental datacenters

Of free solutions, I can only think of rolling your own with a trigger over a database link, or setting up streams.

I'd set up streams.


How to use SSIS , server side, without SSDT?

Could you be thinking of BIML (Business Intelligence Markup Language)? This can be used to generate SSIS packages.

Apparently there's a commercial version, and an open-source version. The open source version appears to integrate into Visual Studio (per this article at MSSQL Tips from 2013), so I can't say whether this is really a solution that uses other tools.

Note, of course, that an SSIS package is stored as an XML file with the extension .dtsx, so (in theory, at least) it might be possible to create one with a text editor. However, having dug through these to some extent, there's a lot of IDs and interlinkages amongst things, and it's not an experiment I personally would want to try.

The packages created by the import/export tool should be able to be saved either as a file, or to a server. Depending on the configuration of the server, and the options selected, they may wind up either stored in that server's msdb databases, or in a particular folder on the server (usually under the SQL root directory for the instance, in the subfolder \DTS\Packages\).


Existing DB2 connection does not work inside a script

As @mustaccio points out in the comment, a connection is local to its subshell.

In order to call the shell script in the context of the current shell, source could be used:

db2inst1@hostname:/tmp$ source test.sh

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME

Of course, . also works:

db2inst1@hostname:/tmp$ . test.sh

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME


Getting number of records in mysql inconsistency

I found out why from the MySQL Documentation:

  The number of rows. Some storage engines, such as MyISAM, store the
  exact count. For other storage engines, such as InnoDB, this value is
  an approximation, and may vary from the actual value by as much as 40%
  to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate
  count.

  The Rows value is NULL for INFORMATION_SCHEMA tables.

  For InnoDB tables, the row count is only a rough estimate used in SQL
  optimization. (This is also true if the InnoDB table is partitioned.)


Backing up Master-Master replicated MySQL databases

This is a Master/Master setup, so I would advise the following

Do not mysqldump from either Master. Both Masters should be used as Active/Passive

Active : Receives all writes
Passive : Replicates from Active (Hot Standby for Failover)

Add an additional slave from each Master

Can be low commodity in CPU since SQL thread processes one command at a time
Should have the same RAM and plenty of Diskspace for Backups

If you do go with additional slaves, you can use either mysqldump or xtrabackup.


How do I draw the erd to model this scenario?

Consider the "bill" itself as an entity that has associated entities (activities and items). Something like this:

Customers, Items and Activities should all be separate entities, and you should include a separate entity for Sales (Bills) and Sales Line Items (Activities and Items part of the Bill). This way you can define Items and Activities once and include multiple (or no) Items and Activities on a single Sale entity record. Example below.

Setup:

USE master
GO
CREATE DATABASE Sales
GO
USE Sales
GO

CREATE TABLE Customers (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    CustomerName NVARCHAR(255)
)
GO

INSERT INTO Customers (CustomerName)
VALUES ('Joes Appliances'), ('Franks Bar & Grill')
GO

CREATE TABLE Items (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    ItemName NVARCHAR(255),
    ItemPrice DECIMAL(10,2)
)
GO

INSERT INTO Items (ItemName, ItemPrice)
VALUES ('Refrigerator', 599.99),
    ('Oven', 1299.99),
    ('Television', 249.99)
GO

CREATE TABLE Activities (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    ActivityName NVARCHAR(255),
    ActivityPrice DECIMAL(10,2)
)
GO

INSERT INTO Activities (ActivityName, ActivityPrice)
VALUES ('Install (per hour)', 29.99),
    ('Install (flat fee)', 80.00)
GO

CREATE TABLE SalesHeader (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    CustomerID INT NOT NULL,
    SaleDate DATETIME,
    CONSTRAINT FK_SalesHeader_Customer FOREIGN KEY (CustomerID)
    REFERENCES Customers (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
GO

CREATE TABLE SalesActivities (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    SalesHeaderID INT NOT NULL,
    ActivityID INT NOT NULL,
    Quantity INT NOT NULL,
    CONSTRAINT FK_SalesActivities_SalesHeader FOREIGN KEY (SalesHeaderID)
    REFERENCES SalesHeader (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT FK_SalesActivities_Activity FOREIGN KEY (ActivityID)
    REFERENCES Activities (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
GO

CREATE TABLE SalesItems (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    SalesHeaderID INT NOT NULL,
    ItemID INT NOT NULL,
    Quantity INT NOT NULL,
    CONSTRAINT FK_SalesItems_SalesHeader FOREIGN KEY (SalesHeaderID)
    REFERENCES SalesHeader (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT FK_SalesItems_Item FOREIGN KEY (ItemID)
    REFERENCES Items (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
GO

Test Data:

-- Sales example #1: Joe's Appliances purchased 1 refrigerator, 1 oven and 5 televisions, plus an install with a flat fee

INSERT INTO SalesHeader (CustomerID, SaleDate)
VALUES (1, '2018-09-02')

INSERT INTO SalesActivities (SalesHeaderID, ActivityID, Quantity)
VALUES (1, 2, 1)

INSERT INTO SalesItems (SalesHeaderID, ItemID, Quantity)
VALUES (1, 1, 1),
    (1, 2, 1),
    (1, 3, 5)

-- Sales example #1: Franks Bar & Grill purchased 3 ovens, 2 refrigerators and 12 televisions, plus an install with an hourly rate for 5 hours

INSERT INTO SalesHeader (CustomerID, SaleDate)
VALUES (2, '2017-02-24')

INSERT INTO SalesActivities (SalesHeaderID, ActivityID, Quantity)
VALUES (2, 1, 5)

INSERT INTO SalesItems (SalesHeaderID, ItemID, Quantity)
VALUES (2, 2, 3),
    (2, 1, 2),
    (2, 3, 12)

Query:

SELECT c.CustomerName,
    sh.ID AS [InvoiceID],
    sh.SaleDate,
    a.ActivityName AS [InvoiceLineType],
    sa.Quantity AS [InvoiceLineQuantity],
    a.ActivityPrice AS [InvoiceLinePrice]
FROM SalesHeader sh
INNER JOIN Customers c ON sh.CustomerID = c.ID
LEFT JOIN SalesActivities sa
    INNER JOIN Activities a ON a.ID = sa.ActivityID
ON sa.SalesHeaderID = sh.ID
UNION
SELECT c.CustomerName,
    sh.ID AS [InvoiceID],
    sh.SaleDate,
    i.ItemName AS [InvoiceLineType],
    si.Quantity AS [InvoiceLineQuantity],
    i.ItemPrice AS [InvoiceLinePrice]
FROM SalesHeader sh
INNER JOIN Customers c ON sh.CustomerID = c.ID
LEFT JOIN SalesItems si
    INNER JOIN Items i ON i.ID = si.ItemID
ON si.SalesHeaderID = sh.ID

Results:

CustomerName        InvoiceID   SaleDate                InvoiceLineType     InvoiceLineQuantity InvoiceLinePrice
----------------------------------------------------------------------------------------------------------------
Franks Bar & Grill  2           2017-02-24 00:00:00.000 Install (per hour)  5                   29.99
Franks Bar & Grill  2           2017-02-24 00:00:00.000 Oven                3                   1299.99
Franks Bar & Grill  2           2017-02-24 00:00:00.000 Refrigerator        2                   599.99
Franks Bar & Grill  2           2017-02-24 00:00:00.000 Television          12                  249.99
Joes Appliances     1           2018-09-02 00:00:00.000 Install (flat fee)  1                   80.00
Joes Appliances     1           2018-09-02 00:00:00.000 Oven                1                   1299.99
Joes Appliances     1           2018-09-02 00:00:00.000 Refrigerator        1                   599.99
Joes Appliances     1           2018-09-02 00:00:00.000 Television          5                   249.99


What is the alternate to SA account in SQL Server?

On your development server, you can grant the standard users the db_datawriter and db_datareader roles. You probably want to grant the db_owner role to the power user. As these are database level roles, you have to repeat this for each of your databases. Regarding the web server, you should create a limited user that only has been granted the db_datareader role.

As the sa login is hardcoded to be a member of the sysadmin server, you can't limit the permission of this account. The best way to migrate is to replace all the connection string you can find and then finally disable the sa account.


SQL Server Encrypted Connections - Configuration Manager does not see Certificate

Answer from comment:

Try including -Type SSLServerAuthentication in the New-SelfSignedCertificate cmdlet to ensure the certificate is for Server Authentication which is a requirement for the SQL SSL Certificate. Also, check out this link for an example PowerShell script for generating a suitable self-signed cert


How I can place connection metadata in order to trace my queries on RDS performance insights fot my postgresql db?

In many cases, the application_name setting is shown for connections. If your database driver does not allow to set it when creating the connection, you can execute
SET application_name = '...';

at any time.
If your panel shows only the query text, then you have to add the information to the query text:
/* Hi, Mom! */ SELECT 42;


Yes, you are correct. You can often get bitten from the fact that the triggers are missing on the slave and thus the trigger effects are not there.

Also if the triggers use any routines (PROCEDUREs and FUNCTIONs) you have to make sure they are present on the slave too. The latter might bite you if you provision your slaves from a mysqldump, since by default mysqldump does not dump routines.


This is set by the client.

It is passed in via the "Workstation ID" property in the connection string. So I presume that you will need to make some configuration updates on your client machines.


If you will be constantly parsing the data out on the fly, you should probably just stored it parsed in the first place.

If you do want to store it as flat strings there are some things you can do to speed it up.

  where uri like 'A/B/C/D%'

This part can be sped up creating an index

create index on meta_info (uri text_pattern_ops);

How much of a speed up this is depends on how selective 'A/B/C/D%' is.

  EXPLAIN ANALYZE
      SELECT DISTINCT((regexp_split_to_array(uri, '/'))[1]) as branch
      FROM meta_info

This can be sped up 2 to 3 times if you avoid the unnecessary regexp machinery and the array machinery.

EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT split_part(uri, '/',1) as branch
FROM meta_info;


Check programmatically if SQL Server needs restarting

You can check for differences between value_in_use and value in sys.configurations for non dynamic changes.

Select * from sys.configurations
where value <> value_in_use and is_dynamic=0

to check for a changed logginmode

declare @tab table (name varchar(max),value varchar(max))
insert into @tab EXEC master.sys.xp_loginconfig 'login mode'
insert into @tab
SELECT 'current',CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows NT Authentication'
WHEN 0 THEN 'Mixed'
END

Select
CAST(
Case when (Select value from @tab where name='login mode')<>(Select value from @tab where name='current')
then 1 else 0 end as bit) as loginconfig_changed


I think the best to start with is http://www.postgresql.org/docs/manuals/

These are really well written and cover everything you will need to know that is PostgreSQL specific.


Need help designing a query to pull a list of songs based on ranking and the last play date

From what you've shown, each part of the query has unique aspect (only 5* songs, only 4* songs, etc.).

If that's true, I'd build each query on its own, then use UNION ALL to join them together. UNION ALL takes the results of two queries (with the same number and type of columns in the SELECT list), and gives you the results of both as a single result set (add another UNION ALL to do three queries, etc.)

Any sorting necessary must be done last, after the final query, and applies to the full result set.

If separate queries for each rating are advantageous, you could potentially call each query separately.  But especially in Access, UNION ALL queries are more expensive than a single query with a WHERE clause with multiple OR expressions: WHERE (Ranking = 5 AND ...) OR (Ranking = 4 AND ...).

UNION ALL queries are more useful when combing data from "incompatible" sources that cannot be done in a single query.  The Access visual query designer also does not work with UNIONs.


The following query will provide a list of statements you can copy-and-paste into a new query window if you need to perform this task once:

SELECT 'ALTER LOGIN ' + QUOTENAME(sp.name) + '
    WITH CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;'
    , *
FROM sys.server_principals sp
WHERE sp.type_desc = 'SQL_LOGIN'
    AND sp.is_disabled = 0
ORDER BY sp.name;

If you need to do this a lot, you could use dynamic SQL like this:

DECLARE @cmd NVARCHAR(MAX);

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT 'ALTER LOGIN ' + QUOTENAME(sp.name) + '
    WITH CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;'
FROM sys.server_principals sp
WHERE sp.type_desc = 'SQL_LOGIN'
    AND sp.is_disabled = 0
ORDER BY sp.name;

OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT (@cmd);
    --EXEC sp_executesql @cmd; /* uncomment this to actually modify logins */
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

The standard warnings apply about doing things like this in an automated fashion.  Test it in a non-production environment first, and roll this out to production with extreme caution.


What would be the most efficient way to check for null values in all columns for multiple tables?

For part 1 of your question, I created these three tables in an empty database:

CREATE TABLE dbo.table1(a INT, b INT);
CREATE TABLE dbo.table2(a INT, b INT);
CREATE TABLE dbo.table3(a INT, b INT);

INSERT dbo.table1(a,b) VALUES(1,1),(1,2),(1,NULL);
INSERT dbo.table2(a,b) VALUES(1,1),(NULL,NULL),(1,NULL);
INSERT dbo.table3(a,b) VALUES(1,1),(1,2),(1,2);

Then I wrote this code to extract various bits of NULL information:

CREATE TABLE #x
(
  t           NVARCHAR(512),                     -- table name
  nullrows    INT NOT NULL DEFAULT 0,            -- number of rows with at least one NULL
  nullvalues  INT NOT NULL DEFAULT 0,            -- total NULL values in table
  nulldist    NVARCHAR(MAX) NOT NULL DEFAULT N'' -- how many NULLs in each column
);

INSERT #x(t) VALUES(N'dbo.table1'),(N'dbo.table2'),(N'dbo.table3');

Now some dynamic SQL fun derived from metadata:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N'
  UPDATE #x SET nullrows = nullrows
    + (SELECT COUNT(*) FROM ' + t.t
    + N' WHERE (' + STUFF((SELECT N' OR '
    + QUOTENAME(c.name) + N' IS NULL'
  FROM sys.columns AS c
  WHERE OBJECT_ID(t.t) = c.[object_id]
  FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),
    1, 4, N'') + N')) WHERE t = N''' + t.t + N''';'
FROM #x AS t;

EXEC sys.sp_executesql @sql;
SET @sql = N'';

SELECT @sql = @sql + N'
  IF EXISTS (SELECT 1 FROM ' + t + N' WHERE '
    + QUOTENAME(c.name) + N' IS NULL)
  UPDATE #x SET nullvalues = nullvalues
    + (SELECT COUNT(*) FROM ' + t + N' WHERE '
    + QUOTENAME(c.name) + N' IS NULL),
    nulldistribution = nulldistribution + '''
    + c.name + N':'' + RTRIM((SELECT COUNT(*) FROM '
    + t + N' WHERE ' + QUOTENAME(c.name) + N' IS NULL))
    + N'','' WHERE t = N''' + t + N''';'
  FROM #x AS t
  INNER JOIN sys.columns AS c
  ON OBJECT_ID(t.t) = c.[object_id];

EXEC sys.sp_executesql @sql;

SELECT * FROM #x;

Results:

t             nullrows   nullvalues   nulldistribution
dbo.table1    1          1            b:1,
dbo.table2    2          3            a:1,b:2,
dbo.table3    0          0

I'll leave it as an exercise to the reader how to send an e-mail based on those results - that does not exactly seem like the hard part of this.

For part 2 of your question, as I said in a comment:

  There is no code you could possibly write that would tell you that DueDate and PaymentDate contain the same kind of information, other than checking that they share the same data type. This is what source control and documentation are for.

I would argue that this is a different and unanswerable question.


SQL Agent Jobs hanging at last Run date and time

Are you using this correct way for disable jobs?

  USE msdb ;
  GO
  EXEC dbo.sp_update_job
      @job_name = N'NameOgYourJob',
      @enabled = 0 ;
  GO

You don't have to update with update statement, it's describe HERE and HERE