How do I access a old saved tablespace after reinstalling postgres?

Recovering a tablespace if you've deleted the main data directory is difficult, because all the metadata about what's in the tablespace is gone. (Nor can you generally recover the main DB if you lose a tablespace.).

I really, really hope you had decent backups, because recovering your data will be difficult and time consuming. You'll need to learn a lot about PostgreSQL's innards.

Identifying relations (lost pg_class)

Pg's on-disk layout is minimalist, with almost all information about tables stored in the system catalogs, in tables like pg_class. That's necessary to properly support transactional DDL. The system catalogs are part of the main data directory.

There is a metapage for each table, but it won't tell you much.

The file layout of the tablespace is a tree of database-oids with relation-relfileid nodes within it. The mapping of relations relfileids to actual tables is stored ... in pg_class, in the main database.

So you have a collection of loose relation forks, with none of the metadata that tells you what they are. And it gets worse.

TOAST tables

As if that wasn't bad enough, lots of data is stored out-of-line in TOAST tables.

The only thing that connects TOAST tables to the main tables is the system catalogs.

Transaction visibility, lost pg_clog and pg_controldata

PostgreSQL records the status of transactions - which tx's are in progress, which ones committed, which rolled back, etc - in pg_clog. You guessed it, in the main database. The control file also resides in the main DB, and keeps track of things like the transaction ID wrap-around point.

Every tuple in a table has xmin and xmax fields, which together tell readers whether they should "see" a tuple when scanning the table. You've lost the records that give the transaction IDs in xmin and xmax meaning. So when you read your tables, you may get:

Duplicate rows where an UPDATE has marked the old row's xmax and created a new row.
Deleted rows
The results of rolled back transactions

Essentially, if you can read the table at all, it'll be a dirty read.

Unwritten changes and lost pg_xlog

PostgreSQL records changes to pg_xlog, then lazily applies them, possibly out of order, to the main relation forks. On crash it will replay these changes.

It doesn't matter if the on-disk status is inconsistent so long as the transaction logs are OK and the in-memory state in the buffer cache is fine.

You've lost the transaction logs, so your tables might be in a partially-replayed state with inconsistent/invalid data.

OK, so your data is a mess, what can you recover from it anyway?

Before you attempt to recover anything, make a complete copy of the original data. Do not change this. Any recovery attempts might make things worse, so you need to keep the originals.

Also, do not attempt any of this on a PostgreSQL install that contains anything you care about. initdb a throw-away PostgreSQL instance for the work. Also, you must be using exactly the same PostgreSQL version.

The first thing I'd do would be CREATE TABLE the original table structure. If you ever dropped columns or changed column types, you must repeat what you did exactly when you recreate the table. Once you're done, check the relfilenode that backs the table:

SELECT relname, relfilenode FROM pg_class WHERE relname = 'mytable';

You also need to find the associated TOAST table's relfileid.

Then I'd disconnect and stop PostgreSQL cleanly (pg_ctl stop -m smart) and try copying the lost table over to the file with the relfilenode of the newly recreated table. If the original had lots of forks (1234.1, 1234.2, etc) you need to copy and rename all of them. You might be able to match up which table is which based on file size, or by looking at strings for it.

Do the same for the TOAST table associated with the relation.

Then start PostgreSQL in standalone backend mode and attempt to read the table.

If you're really lucky it won't crash or ERROR, and will just give you an empty table or bogus results.

At this point, you then need to use pg_dirtyread to attempt to read the table data while ignoring transaction visibility. See the github repo.

It'd be quite nice to have a tool to dump a raw PostgreSQL database table standalone, but I don't think there's such a thing.

Others may have better suggestions about how to attempt recovery. It isn't something I've done, and I don't have time to test it out right now.

I suggest doing some more reading and research, see if anyone else has written about this already. Also post on pgsql-general@postgresql.org for further advice.


Description

I'm synthesising that these are your constraints:

One Book can be in MANY Category's, but only in ONE Category per Library.
Each Library can have ZERO or more Category's, which they do not have to "share" with other Library's.

Solution

This should all be achievable via the following structure (ignoring your wider solution space with users, borrowing, etc):

Note:  The link between library_has_category and book_in_library_category is a composite key of the library AND category keys together.


How can I generate call stack on error on the fly

If the reports are always referring to the same index, then you might just have a corrupt nonclustered index (since the index ID is greater than 1). I'd definitely keep a close eye on hardware diagnostics, and also keep SQL Server up-to-date on patches to identify the root cause of it becoming corrupted. But you may be able to repair the problem by simply rebuilding the index:

DBCC DBREINDEX(table_name, index_name)

It's possible that the corruption is a total fluke (either a software bug, or a hardware error that went undetected), but if it hits again, and corrupts a table/clustered index, rather than just a nonclustered index, then you could have a bigger problem on your hands.


Mongodb data sync between primary and secondary node even when port is closed

Have you closed that path both ways...

If primary cannot "ping" secondary, it's not reachable/healthy, but if secondary can connect primary, it can replicate opLog...


Return list of values in conditional where clause

Maybe something like that could work:

declare @values table(id int, value nvarchar(100))
insert into @values
values(1, N'FirstValue'), (1, N'SecondValue'), (2, N'ThirdValue'), (2, N'FourthValue')

select *
from dbo.MyTable t
join @values v on t.MyColumn = v.value
                and (v.id = @Parameter or @Parameter not in (1,2))


Updating unindexed values with postgresql question

PostgreSQL doesn't table-lock on update, it row-locks.

  You need to update this table so all occurrences of a particular string in the “type” column are replaced with another string.

That's simple,

UPDATE apps
SET type = regexp_replace(type, 'SearchRegex', 'Replacement', 'g')
WHERE type ~ 'SearchRegex';


Group permissions in postgreSQL not inheriting to table level

The simple test:

postgres=# create schema foo;
CREATE SCHEMA
postgres=# create table foo.t(x int);
CREATE TABLE
postgres=# create role foomaster nosuperuser nologin inherit;
CREATE ROLE
postgres=# create role fooslave password '111' login inherit in role foomaster;
CREATE ROLE
postgres=# grant usage on schema foo to foomaster;
GRANT
postgres=# set role fooslave;
SET
postgres=> select * from foo.t;
ERROR:  permission denied for relation t
postgres=> set role postgres;
SET
postgres=# grant select on foo.t to foomaster;
GRANT
postgres=# set role fooslave;
SET
postgres=> select * from foo.t;
 x
---
(0 rows)

It seems that you are doing something wrong.

Upd: About newely created table:

postgres=> set role postgres;
SET
postgres=# create table foo.tt(xx int);
CREATE TABLE
postgres=# set role fooslave;
SET
postgres=> select * from foo.tt;
ERROR:  permission denied for relation tt
postgres=> set role postgres;
SET
postgres=# grant select on foo.tt to foomaster;
GRANT
postgres=# set role fooslave;
SET
postgres=> select * from  foo.tt;
 xx
----
(0 rows)

PPS: Hope that my investigations was helpful at least for somebody.


How to give user access permission to dbo and single custom schema?

When you create your users userA, userB, userC, they have no permissions at all
(unless you grant some permission to public database role, but by default no permission is granted to public)

So they cannot access nothing, even dbo schema, so you have no need to revoke/deny any permission.

Then you create schemaA, schemaB and schemaC (there is no need to make any particular user the owner of these schemas) and grant the permissions to your users as follows:

GRANT CONTROL ON schema::[dbo] TO [userA];
GRANT CONTROL ON schema::[schemaA] TO [userA];

GRANT CONTROL ON schema::[dbo] TO [userB];
GRANT CONTROL ON schema::[schemaB] TO [userB];

GRANT CONTROL ON schema::[dbo] TO [userC];
GRANT CONTROL ON schema::[schemaC] TO [userC];

Of course you can do the same thing granting the permissions to Roles, not only to individual users


Speed up SELECT query with JOIN from table with 2.2M rows in PostgreSQL?

You should write it like this with a NOT EXISTS statement

SELECT account_id, email
FROM account AS a
WHERE NOT EXISTS (
  SELECT 1
  FROM account_intercom AS i
  WHERE a.account_id = i.account_id
);

Make sure your stats are uptodate and your table clean,

VACUUM FULL account;
VACUUM FULL account_intercom;

Paste the updated EXPLAIN ANALYZE


Can I copy a collection in mongoDB from one database to another by copying the db files directly?

You can copy an entire database across in this manner, by copying the files to a new instance, but not collections.  You also cannot simply copy the database files from database foo and alter the filenames at the filesystem level (to bar.0, bar.1 etc.) to avoid namespace collisions, the name must remain the same.

If you wish to move a single collection (or any subset of collections) rather than a whole database then you will have to use the command you mentioned or use mongodump/mongorestore to do the migration.  However, any per-collection method will require rebuilding the indexes from scratch - you only get them in place if you can copy the data files (i.e. move the entire database).

If the other collections are relatively small, you could look at copying them elsewhere,  out of the current database, leaving just the large collection you wish to move.  You can then copy the entire database, and move the database files as mentioned.


Copying MySQL schema, some tables not getting recognized!

The ".frm" file holds table definition. You might want to check mysql file-types info.

Usually when you move data-base physically do following:

Shutdown mysql
Copy datafiles to datadir
make sure mysql has full permissions on it:
chown mysql:mysql datadir/ -R
start mysql

So, make sure that the tables which are not "visible" have proper permissions for mysql to access it.

Also, when you say you moved "database" do you mean all-databases-on-server or single-database?
If your database have innodb tables you cannot move them like this!


You can concatenate text with text, so you need to CAST(). For example,

notes = CAST(GETDATE() AS varchar(max)) + 'Old-' + CAST(ISNULL(notes, '') AS varchar(max)) + 'New-' + CAST(@Quantity AS varchar(max))

Note that concatenate null with something will give you null, so to implement CONCAT() function's functionality you need ISNULL().


'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

use --no-tablespaces
see mysqldump-Documentation

mysqldump requires at least the SELECT privilege for dumped tables,
SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES
if the --single-transaction option is not used, and (as of MySQL
8.0.21) PROCESS if the --no-tablespaces option is not used.
Certain options might require other privileges as noted in the option
descriptions.

and see documentation of param no-tablespaces

--no-tablespaces, -y
This option suppresses all CREATE LOGFILE GROUP and CREATE TABLESPACE
statements in the output of mysqldump.


How do I set a database back to 'Restoring...'?

It's not quite as simple as re-enabling log shipping as you need to do some initial setup to be able to switch from one instance to another.

This article helps with that

You also need to perform some clean-up before switching back to the original primary as it will be holding databases that are now out-of-date.

If your hardware on the primary and secondary are the same and there is no performance loss or maintenance headache in staying on the secondary server then I would recommend doing that until the next failure as the step of failing back is not needed.


Unable to drop non-PK index because it is referenced in a foreign key constraint

Because a foreign key can point to a primary key or a unique constraint, and whoever created that foreign key possibly created it before the primary key existed (or they shifted the FK to point to the Unique index while they changed something else about the primary key). This is easy to repro:

CREATE TABLE dbo.MyTable(MyTableID INT NOT NULL, CONSTRAINT myx UNIQUE(MyTableID));

CREATE TABLE dbo.OtherTable1(ID INT FOREIGN KEY REFERENCES dbo.MyTable(MyTableID));

ALTER TABLE dbo.MyTable ADD CONSTRAINT PKmyx PRIMARY KEY(MyTableID);

CREATE TABLE dbo.OtherTable2(ID INT FOREIGN KEY REFERENCES dbo.MyTable(MyTableID));

In fact, both of these foreign keys will point to the first unique constraint defined on that column (myx).

You can fix the foreign key on the other table by dropping it and re-creating it. You will need to repeat that process for any other tables that point to this column. You can find these easily:

SELECT s.name,t.name,fk.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk
ON fkc.constraint_object_id = fk.[object_id]
INNER JOIN sys.tables AS t
ON fkc.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns AS c1
ON c1.[object_id] = fkc.referenced_object_id
AND c1.column_id = fkc.referenced_column_id
AND c1.name = N'MyTableID'
WHERE fkc.referenced_object_id = OBJECT_ID('dbo.MyTable');

Results:

dbo    OtherTable1    FK__OtherTable1__ID__32E0915F
dbo    OtherTable2    FK__OtherTable2__ID__35BCFE0A

And even generate a script to drop and re-create them (dropping the redundant unique constraint in the meantime):

DECLARE
  @sql1 NVARCHAR(MAX) = N'',
  @sql2 NVARCHAR(MAX) = N'ALTER TABLE dbo.MyTable DROP CONSTRAINT myx;',
  @sql3 NVARCHAR(MAX) = N'';

SELECT
  @sql1 += N'
ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';',
  @sql3 += N'
ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY '
  + '(' + QUOTENAME(c2.name) + ') REFERENCES dbo.MyTable(MyTableID);'
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk
ON fkc.constraint_object_id = fk.[object_id]
INNER JOIN sys.tables AS t
ON fkc.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns AS c1
ON c1.[object_id] = fkc.referenced_object_id
AND c1.column_id = fkc.referenced_column_id
AND c1.name = N'MyTableID'
INNER JOIN sys.columns AS c2
ON c2.[object_id] = fkc.parent_object_id
AND c2.column_id = fkc.parent_column_id
WHERE fkc.referenced_object_id = OBJECT_ID('dbo.MyTable');

PRINT @sql1;
PRINT @sql2;
PRINT @sql3;
-- EXEC sp_executesql @sql1;
-- EXEC sp_executesql @sql2;
-- EXEC sp_executesql @sql3;

Results:

ALTER TABLE [dbo].[OtherTable1] DROP CONSTRAINT [FK__OtherTable1__ID__32E0915F];
ALTER TABLE [dbo].[OtherTable2] DROP CONSTRAINT [FK__OtherTable2__ID__35BCFE0A];

ALTER TABLE dbo.MyTable DROP CONSTRAINT myx;

ALTER TABLE [dbo].[OtherTable1] ADD CONSTRAINT [FK__OtherTable1__ID__32E0915F]
  FOREIGN KEY ([ID]) REFERENCES dbo.MyTable(MyTableID);
ALTER TABLE [dbo].[OtherTable2] ADD CONSTRAINT [FK__OtherTable2__ID__35BCFE0A]
  FOREIGN KEY ([ID]) REFERENCES dbo.MyTable(MyTableID);

This explicitly handles this case, where the constraint only involves a single column. It gets a little more complex if there are multiple columns involved (and this answer is not meant to solve that problem). I also didn't test if this works exactly as coded if the foreign keys point to a redundant unique index (which has the same underlying structure but is created with slightly different DDL). Exercise for the reader. :-)


User has more privileges in redshift than shown by relevant query

information_schema.table_privileges only shows the privileges that were directly granted to a role (user), not the privileges inherited through role membership.
Now most catalog tables have SELECT privileges granted to the special role PUBLIC, which stands for “everybody”, so every user can SELECT from catalog tables.
And if you feel tempted to change that, don't. It will affect the usability of your database.


Does my PostgreSQL instance support kerberos/gssapi authentication?

First, let me add some more description of the environment where this problem was observed.
This excellent series of articles describes setting up kerberos and postgresql
PostgreSQL GSSAPI Authentication with Kerberos

Part-1
Part-2
Part-3

If you do this in AWS, you should also setup elastic-ip addresses (so you can stop and start your servers). I also setup rt53 for the host names and have those map to the elastic-ip addresses you setup.
Note: in my case I am using postgresql v13 and after my initial failure, I followed these instructions on the postgressql for installing postgresql.
You would think that would be enough. However, if you don't tweak the /etc/hosts file so that you have entries for your servers (as David did in his example, that was on 'in-house' hardware) you get this error:
postgres@jl-krb5dev-client:~$ psql -U postgres -d postgres -h
jl-krb5dev-postgres.matlabonlineserver.com psql: error: could not
initiate GSSAPI security context: Unspecified GSS failure.  Minor code
may provide more information: Server
krbtgt/COMPUTE-1.AMAZONAWS.COM@MATLABONLINESERVER.COM not found in
Kerberos database FATAL:  no pg_hba.conf entry for host "52.70.13.95",
user "postgres", database "postgres", SSL on FATAL:  no pg_hba.conf
entry for host "52.70.13.95", user "postgres", database "postgres",
SSL off

But if you add entries in the /etc/hosts file on your client machine for the kerberos and postgresql machines (using the elastic-ip addresses you setup earlier) everything works fine.
Note: this does require that you properly configured postgres with a keytab file.
For v13 of postgresql, I put it in /etc/postgresql/13/main/conf.d/krb5.keytab AND adjusted the postgresql.conf file (in /etc/postgresql/13/main) so that it has an entry like this: krb_server_keyfile = 'FILE:/etc/postgresql/13/main/conf.d/krb5.keytab
A Big Thanks to @jjanes for getting me on the right path in the comment to my initial question.


a) you need a command prompt with elevated privileges (run as administrator) to do this

b) alternatively, you can start the service from Windows GUI (services.msc)


MySQL: Migrating database with utf8 collation and charset but latin1 data to new full UTF-8 database

After reading various issues and trying different ways, this was the solution for me:

The dump has to be done like this (make sure you pass the filename as -r argument):

mysqldump -h HOST -u USER -p --default-character-set=latin1 DATABASE -r utf8.dump

Then I have opened utf8.dump file and changed one line at the top.

From

/*!40101 SET NAMES latin1 */;

To

/*!40101 SET NAMES utf8 */;

Then I imported it to (make sure to use mysql instead of mysqldump and source the file, do not use "<" to pass it from terminal):

mysql -u USER -p DB -h HOST

mysql> source utf8.dump

After this, all my data was correctly encoded and stored as UTF-8 (e.g. hÃ©t -> hét)


Should I create multiple maintenance plans to backup more than 200 User databases

I wouldn't use maintenance plans but either Hallengren's backup solution, or, Minion Backup (the latter would be better because you can still have a single job, but write a table into each database which tells it which day it backs up on). In your comment you said you can't use "third party tools". I wouldn't go so far as to call these that, they're scripts, and whatever red tape there is I'm sure you can work around it by asking.

Question: How long do all the backups take?

I would likely go with the single job assuming all backups can complete in a reasonable amount of time. But key to this is using operational validation - writing something to check that all databases have been backed up, and also testing the backups themselves on a cycle.

If you wanted to go the other route - staggering backups - I've also done this on lots of large (at the time) backups to slow storage. I wrote/used a simple formula to run across the databases and determine which day each backup would run.

Pick a measure for the databases (cost in minutes).
Construct a table of databases ordered from most cost to least cost.
Construct a table of the backup days, with slots for database name and cost.
Iterate the database table.
Find the next slot in the table of backup days with the least cumulative cost.
Move the database into that slot.
Repeat until there are no databases left.
You are left with a list backup days, databases, and costs that balance out surprisingly well.

So you'd then use that to set a different backup for each day with those databases (and something to catch anything left over - which is going to be pretty difficult).


How to use internal variables in Report Builder Textboxes

Ok, found out that if you're using SSRS, you cannot declare variables in the query and expect to use them on the layout of the report.  If you do, you need to declare and set the values in SSRS, not in the query.

Here is the page I used to set dates for start and end dates for the variables through SSRS:  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a62672c2-4005-4dc6-935f-7c90e9e55491/ssrs-expression-retrieve-last-week?forum=sqlreportingservices


Performance issue with larger resultsets SQL Server

Looking at the execution plan XML, there are a few things sticking out that may be the cause of your issue.

You are selecting everything back to the client. There are inherent wait times with transferring data over a network. The larger query is transferring almost 8x more data.
The larger query has a spill to TempDB. This means that parts of the query are using disk instead of memory. This will clearly slow it down much more than the smaller query.
You are running the under the compatibility level for SQL Server 7.0, so the fact that your arithabort setting is different for each execution can be causing issues. See here - http://www.sommarskog.se/query-plan-mysteries.html.

Regarding #3, you are either not running these the same way, or you are changing session level settings in between runs. You need to run these exactly the same way to get execution plans that can be compared accurately.


sync 2 tables in a single database in mysql with no duplicacy

The mysql Syntax for insert data is :

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

In your query you just missed values and you must pass where condition to select a specific row, in your case the last insert id:

 INSERT INTO report_backup(mid, account, dev, start, end, logged_user, added_time) SELECT mid, acc, device, start, end, login_user, date_added from main_tbl;

Correct one is :

INSERT INTO report_backup(mid, account, dev, start, end, logged_user, added_time) VALUES (SELECT mid, acc, device, start, end, login_user, date_added from main_tbl WHERE mid = 'Your Last Inserted Value');

Hope my answer is clear enough to fix your issue.


Lots of Indexes - MySQL vs MongoDB - Migrating

Given your description I strongly suggest against using MongoDB. Not because it would necessarily a bad choice (although I believe it is in your case for reasons other than pure technical ones).

Here are the points that caught my eye.

Data modeling

Trying to use MongoDB with relational data model with no adaptations almost always leads to tears and misery except for the most trivial use cases. And that is the better end of the story. The worse end is loosing money, potentially big time.

The reason for this is that with SQL, you identify your entities and their attributes and relations and then bang your head against the wall for a few hours to get your upper left above and beyond JOINS right to get your questions derived from your use cases answered. All while avoiding data redundancy like the devil holy water.

Data modeling with MongoDB works different. You identify the use cases and the questions derived from them and model your data in a way so that those questions can be answered in the most efficient way.

Since this is a bit abstract, let me give you an

Example

Let us assume you have a web application called "chirper", with users doing chirps. With SQL you would now model your data, coming up with something like a user table and a chirp table.

The first use case you encounter is that you want the latest 10 chirps to be displayed on the applications home page, together with the username of the chirper. With SQL it is easy enough, you do a join on chirps and users, sort the result descending by data and limit to 10 records.

With MongoDB, you'd have a careful look at what you need. For various reasons you do not want to embed the chirps into the user collection. But since you do need to relate the chirps to a user anyway, you decide to do so by username and come up with a "schema" for your chirps collection like this:

{
  _id: ObjectId("570b87a56931b8f21a8bf25c"),
  user: "jdoe",
  date: ISODate("2016-04-11T11:17:08.415Z"),
  text: "Chirp!!!"
}

and, miraculously enough, all you have to do is to do a

db.chirps.find({}).sort({date:-1}).limit(10)

which gives you the same result without a JOINS at the expense of relatively cheap disk space.

Resource limitation

  I want to keep disk space and ram at minimum use

MongoDB is a lot, but surely not resource friendly. It was never meant to be a replacement for MySQL – which by the way was specifically designed to be a relatively lightweight general purpose RDBMS. I strongly advice against running MongoDB and anything else on the same server for production purposes. The reasons are manifold, but here are the most important ones:

MongoDB will take up to 85% - 90% of your available, physical RAM. This is because the working set (the indices and a subset of the data) are kept in RAM.
Depending on the storage engine and its configuration you use for MongoDB, even CPU utilization can be considerable.
A heavily loaded MongoDB needs a lot of disk IO. I have seen systems with SSDs in a RAID0 in which the IO rate was at its limit.

Leaving out the petty details for the moment, this would mean that during situations where you do not want it the most (your application is taking off and has a lot of users), MongoDB would battle with the other parts of the application over resources.

Do not get me wrong: MongoDB is not a resource eating monster per se. But if it does what it is intended to do, namely dealing with huge amounts of data and data changes, you do not want to have the parts of your application battle for resources.

That being said: There are ways to limit MongoDBs resource allocation and make sure those limits are obeyed. Probably the most well known as of today is Docker. You have to decide wether it is worth it to run MongoDB in a Docker container.

As for the data file size: There is transparent compression available for the default wiredTiger storage engine. You can choose from either snappy(default) or the better known zlib compression. Both come at the expense of a higher CPU utilization, as mentioned above.

Time to market

Forgive me, but you demonstrate a lack of basic knowledge about MongoDB. Since you are not sure what to do, the best you can do is to research every step carefully, make a decision, rinse and repeat. And I am not even taking into account that you would need to remodel your data and most likely make massive changes to your applications persistence layer. If I were you and wanted/needed a fast time to market, I'd go with what I have as of now. In case problems occur during production, I'd get myself a consultant specializing on NoSQL to find out wether one of the various NoSQL databases suits your needs, identify said DBMS (which may or may not be MongoDB), get myself a specialist for that and do the migration only then. With a specialist on your side.

Administration

A mistake commonly made by people is that they think it is as easy to manage a MongoDB deployment as it is to getting it to work. It is not. Any DBA, Sysadmin or even DevOps (even when used in the wrongest sense of the word) should be able to get a sharded cluster running. Choose the proper dimensions, identify problems, decide when to scale, deal with non-trivial problems and errors? Not so much. And note that the latter is highly subjective and prone to the skills of the DBA in question. Do you really want to store your production data on a system you barely understand?

Conclusion

Imho, MongoDB does not fit the requirements you stated. Changing now will most likely increase your time to market, and very much so since you do not understand the intricacies and pitfalls of MongoDB. In the worst case you built up technical debts while simultaneously diminishing the performance of your application.

MongoDB is less well suited for your resource limitation requirements, and finding a sweet spot for resource limitations that give you an acceptable performance requires an experienced DBA and quite some time, during which your application might run with sub-par performance.

Let me put it in a bit of context: MongoDB might very well be suitable for your use cases, and even excel. But I do not think it is feasible to change your persistence technology as of now, given your level of knowledge and the stated requirements.

hth


Seeking feedback on table relationships, general schema design

Event Category and Sub-Category are classifications of events. They are your way of tagging events (i.e. meta-attributes).  The Event itself is a physical-thing/occurence (independent of you)  to which you are attaching these attributes.  Competition and competitors are also physical things- that are associated with the occurrence. When designing, it helps to be aware of what is a physical thing, and what is your design artifact.

I don't think Competition , or Competitors or Performers (if a non-sport event) should depend upon your classification.

** So I would take out the link between sub-category and competition. **

An Event has performers.  A performer can be an individual or a team.
An event will be associated with one or more performers.

A competition like UEFA-CL can be considered a collection of events (*Not the otherway around - as in your model *). Hence competition is independent (or rather, a higher level entity) compared to an event. An Event is kind of like the 'delivery' (or instance) of that competition at a particular time / place.

Hence, Competition --> has multiple (> 0) events --> has multiple (> 0) performers. (This is the way I would look at competitions).  So I would make competition above the Event (in the object heirarchy - not talking of inheritence here)

I can talk about UEFA-CL without referring to a specific event in its set. Competition is composed of (stable) teams.  The competition can span seasons - so you might even have search/drill-down criteria such as Soccer.  You need to setup your production so that you can do analysis easily on your data too. I wouldn't want to bury competition deep inside your heirarchy.


Multi-source replication from older masters

From what I can find on MariaDB's website, multi-source replication from MariaDB 5.5 to MariaDB 10.0 looks like it should not be an issue.

https://mariadb.com/kb/en/mariadb/upgrading-from-mariadb-55-to-mariadb-100/

"There are no changes in table or index formats between MariaDB 5.5 and MariaDB 10.0, so on most servers the upgrade should be painless."

They even mention considering Multi source replication on this same page (above).

This link provides incompatibilities with MariaDB 5.5 that you should watch out for: https://mariadb.com/kb/en/mariadb/multi-source-replication/

Incompatibilities with MariaDB/MySQL 5.5

max_relay_log_size is now (almost) a normal variable and not automatically changed if max_binlog_size is changed. To keep things compatible with old config files, we set it to max_binlog_size at startup if its value is 0.
You can now access replication variables that depend on the active connection with either GLOBAL or SESSION.
We only write information about relay log positions for recovery if innodb-recovery-update-relay-log is set.
Slave_retried_transactions now shows the total count of retried transactions over all slaves.
The status variable Com_slave_start is replaced with Com_start_slave.
The status variable Com_slave_stop is replaced with Com_stop_slave.
FLUSH RELAY LOGS are not replicated anymore. This is not safe as connection names may be different on the slave.


Stored Procedure with Table Value Parameter

A cursor should very rarely be your first approach. Especially if you've gone to the trouble of making a table type; the primary goal there is to avoid row-by-row processing, splitting strings, etc.

Making some guesses about the destination table, its columns, and the name of the column in your TVP, you can insert multiple rows in the destination table by using INSERT/SELECT:

INSERT dbo.destination_table
(
  computer_name, serial_number
)
SELECT @ComputerName, column_name
  FROM @Monitor_Serial;

To make sure you only insert new serial numbers, use:

  FROM @Monitor_Serial AS m
  WHERE NOT EXISTS (SELECT 1 FROM dbo.destination_table
    WHERE serial_number = m.column_name
    -- AND computer_name = @ComputerName
  );

(You may or may not need that commented line, since it's unclear if you want to prevent any duplicates overall or just for a specific computer name.)


What steps are required to move data from a custom schema in one database to the default schema in new database

You can script out that portion of the database and then run a CREATE DATABASE with the scripted schema and data from your original database.

When you Generate Scripts..., you will have the ability to select specific objects as opposed to the whole database.  You can select the desired schema, and all of the objects that the schema owns.  This will generate a script containing your schema and schema objects.  A bit of a manual operation, but probably the easiest method.

In the generated script, you can alter the schema name to be what the new database's schema should be called, but realize that the object definitions also need to reflect the new schema's name.


How did my colleague get his own schema?

As a rule, you should explicitely specify dbo schema if you want to create the object in this schema.

As you are db_owner, your default schema is dbo, so there is no problem when you don't specify dbo schema while creating objects. But for other (Windows) users it's not the same.

Your users are members of Windows group that has no default schema. In this case the corresponding user and the schema are created when the user creates any object, it's documented here: CREATE SCHEMA (Transact-SQL)

  Implicit Schema and User Creation

  In some cases a user can use a database without having a database user
  account (a database principal in the database). This can happen in the
  following situations:

  A login has CONTROL SERVER privileges.

  A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a
  member of a Windows group which has a database user account (a
  database principal for the Windows group).

  When a user without a database user account creates an object without
  specifying an existing schema, a database principal and default schema
  will be automatically created in the database for that user. The
  created database principal and schema will have the same name as the
  name that user used when connecting to SQL Server (the SQL Server
  authentication login name or the Windows user name).

  This behavior is necessary to allow users that are based on Windows
  groups to create and own objects. However it can result in the
  unintentional creation of schemas and users. To avoid implicitly
  creating users and schemas, whenever possible explicitly create
  database principals and assign a default schema. Or explicitly state
  an existing schema when creating objects in a database, using two or
  three-part object names.

To resolve the issue just assign dbo schema as default schema to all yor users-Windows groups or write the schema explicitely when creating objects. Always.


Huge 0.000001 and 0.000002 files and so on, in mysql data folder

These are your binary logs.
For some reason, your config file lists:

log-bin = 0

Did you mean by that to disable binary logs? Well, instead, you've simply named them as "0". Which is why you get all these files: 0.000001, 0.00002 etc.

If you want binary logging, better name them properly:

log-bin = mysql-bin

(this will make files named mysql-bin.000001, mysql-bin.000002 etc)

If you don't want binary logging, just comment the line:

#log-bin = mysql-bin

Typically, binary logs are desired: they are essential if you want to set up replication, or do logical point-in-time recovery.

If you do decide to disable them, and are convinced you do not need them, then you can also purge them:

mysql> PURGE MASTER LOGS TO '0.000025';

will delete all logs up to and excluding given log file.


Free some space on a full Express Edition instance

Your current process shouldn't really change application performance (especially with such tiny amount of data) but perhaps your database gets severe index fragmentation and creating the new database results in the indexes being rebuilt. (We wouldn't be able to determine this without more information such as a before and after execution plan for a specific query that improved due to your process.)
In any case, you can run the same shrink commands on your existing database, no need to export and recreate a brand new database every time. Though usually it's not recommended to shrink a database, in your use case where you're bound to only 10 GB per database, I understand the potential need to do so.
In the long term, I'd look to re-architect your application and database to use multiple databases if possible and makes logical sense, as a way to work around the hard cap of 10 GB in SQL Server Express. (E.g. group only certain related tables in one database and other groups of related tables across other databases, or decouple transactionally heavy tables from your less changing ones for another way to slice it.) Cross database querying is always allowed. Or you might find it worth it to purchase a small Standard Edition license eventually.
Furthermore, you might want to consider switching your database to Simple Recovery Model which will reduce the amount of space needed by automatically reclaiming the space used in the Transaction Log. This is likely all the space you're reclaiming with your current process when you run the shrink command, and therefore will likely remove your need to run the command anymore.
Regarding keeping your indexes defragmented, you should read up on Microsoft's BOL: Optimize index maintenance to improve query performance and reduce resource consumption and then you can look into Ola Hallangren's SQL Server Index and Statistics Maintenance which will help you automate that process to ensure your indexes are maintained.
All of this is fine to run in a production environment, but my advise is to always test in a development one first, come up with a game plan that works, then implement it in production.


Your alternatives are:

use 6th normal form and don't include a row if something doesn't exist.
use nulls
use some other value that will screw up all of your calculations.

My recommendation would be to use nulls. Why don't you want to use nulls?


Why do we have undocumented and unsupported functions in SQL Server?

As someone working for a large software vendor, I can say that undocumented and unsupported1 features typically fall into one of these three categories:

Tools for the exclusive use by the vendor support engineers. They are rarely needed, typically require intimate knowledge of internals, hard to set up and use, and often dangerous enough to be protected by one-time passwords or keys that should be explicitly provided by the vendor. They are never intended to become product features. They are there to aid support when troubleshooting hard to debug problems.

Fixes and workarounds for esoteric bugs. They are implemented as hot fixes for critical situations. Such bugs are rare, and their root causes are eventually resolved, so these "features" lose their usefulness at that point, and it's not in the vendor's interest to provide continuous support for them.

Beta and experimental features. They are typically first made available and sparsely documented for a small cohort of beta testers and major customers who requested them. Some of these will eventually become fully supported, some others will be left there to rot when stakeholders lose interest or when they conflict with other features that are deemed more important or useful.

As mentioned in the comments to your question, each fully supported and documented feature carries a cost to the vendor, and that cost must be justified by the expected benefit. If there is little benefit to the vendor from maintaining a certain feature, there's no reason to support it.

1 - They are not really totally unsupported; they are supported on a "best effort" basis, meaning "We might have a technote somewhere that briefly describes the feature, but we won't spend much time helping you make it work, and if it doesn't work or if it wipes out your data, well, tough".


Extracting Multiple Values from Single Field

Try this

select substring(custom_fields,instr(custom_fields,'gender')-7,5) as age_range from tbl;


I think you'd better create a filter table in which you put your replacement string pairs and then you can create a function to remove source string with your filter table.

Here is the example

use tempdb
-- filter table
create table dbo.Filter (id int identity, src varchar(100), tgt varchar(100));
-- populate a few filtering pairs
insert into dbo.Filter (src, tgt)
values ('.xlsx', '')
     , ('csv', '')
     , ('__', '_')
     , ('-Jun-', '');

-- your source table
create table #s (mycol varchar(1000))
-- populate source column
insert into #s (mycol)
values ('this .xlsx file is not __ created in -Jun- but in June')
     , ('this csv file should be removed  -Jun-')
go

-- filtering function
create function dbo.funcReplace (@s varchar(1000))
returns varchar(1000)
as
begin
    declare @src varchar(100), @tgt varchar(100);
    declare @curS cursor;
    set @curS = cursor for select src, tgt from dbo.Filter
    open @curS
    fetch next from @curS into @src, @tgt;
    while @@fetch_status = 0
    begin
        set @s=replace(@s, @src, @tgt);
            fetch next from @curS into @src, @tgt;
    end
    return @s;
end
go

-- before replacement
select bfr_replacement = mycol from #s

-- after replacement
select  after_replacment=dbo.funcReplace(mycol)
from #s

In future, if you have more replacement needed, just add them to the table dbo.Filter and you do not need to change anything elsewhere.


CREATE  DATABASE .. WITH TEMPLATE just copies from the template - like the term implies. Later changes to the template database are completely unrelated to databases created before that.

You have to deal with it in some other way. Like:

Create a backup of the schema.
Prepend the generated SQL script with DROP SCHEMA ... CASCADE;
Create a backup of just the schema in each target DB. Compare the dump with the template dump.
Be very sure, that it's safe to replace the whole schema. Did you make any updates to objects in the schema? Any at all? That includes FK or other references to any object in the schema from anywhere in your DB. Or even references from outside.
Unless you know what you are doing, create a backup for each DB you are going to update.
Run the script against each affected DB after that.


Concat a string to a variable to use as a column name in a query

The problem with your query is that you need to construct meta-data (name of a column) with data. You therefore need to first construct the query, and then execute it using dynamic SQL. Something like (untested)
CREATE PROCEDURE P
LANGUAGE SQL
BEGIN
    DECLARE STMT VARCHAR(100);
    DECLARE APPROP CHAR(1);

    SELECT APPROPRIATION INTO APPROP FROM ...;
    SET STMT = 'SELECT APPROP' || APPROP || ' FROM ... ';
    EXECUTE IMMEDIATE STMT;
END @

I used @ as a statement terminator. Note that the compiler has no way to verify that the query makes sense, so you may end up with run-time errors.
Debugging stored procedures
IBM Datastudio has a fully-fledged stored procedure debugger, i.e. you can set breakpoints, inspect variables, etc. If you for some reason does not want to install it (or like me, can not find the download page:-), you can use the DBMS_OUTPUT module:
~]$ # add some sample data
~]$ db2 "create table t (APPROPRIATION char(1) not null)"
~]$ db2 "insert into t (APPROPRIATION) values ('1')"
~]$ db2 "insert into t (APPROPRIATION) values ('2')"
~]$ db2 "insert into t (APPROPRIATION) values ('3')"
~]$ db2 "insert into t (APPROPRIATION) values ('4')"

~]$ cat p.sql
CREATE PROCEDURE P()
LANGUAGE SQL
BEGIN
    DECLARE STMT VARCHAR(100);
    DECLARE APPROP CHAR(1);

    SELECT APPROPRIATION INTO APPROP FROM T ORDER BY RAND() FETCH FIRST 1 ROWS ONLY;
    SET STMT = 'SELECT APPROP' || APPROP || ' FROM ...';
    call dbms_output.put_line('DEBUG: ' || STMT);
    -- EXECUTE IMMEDIATE STMT;
END @

~]$ db2 -td@ -f p.sql

~]$ db2 "SET SERVEROUTPUT ON"
DB20000I  The SET SERVEROUTPUT command completed successfully.
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP1 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP1 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP2 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP1 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP3 FROM ...

~]$ db2 "SET SERVEROUTPUT OFF"

Another option is to use an out variable:
~]$ cat p2.sql
CREATE PROCEDURE P2(OUT outstmt VARCHAR(100))
LANGUAGE SQL
BEGIN
    DECLARE STMT VARCHAR(100);
    DECLARE APPROP CHAR(1);

    SELECT APPROPRIATION INTO APPROP FROM T ORDER BY RAND() FETCH FIRST 1 ROWS ONLY;
    SET STMT = 'SELECT APPROP' || APPROP || ' FROM ...';
    SET outstmt = stmt;
    call dbms_output.put_line('DEBUG: ' || STMT);
    -- EXECUTE IMMEDIATE STMT;
END @

~]$ db2 -td@ -f p2.sql

~]$ db2 "CALL P2(?)"

  Value of output parameters
  --------------------------
  Parameter Name  : OUTSTMT
  Parameter Value : SELECT APPROP1 FROM ...

  Return Status = 0

One major drawback with both these methods is that they will not produce any output when you need it the most, i.e. when your procedure fails.
For future posts, always include a reproducible setup. Either include DDL (create table statements, etc) and SQL (insert table statements) or use a service like DB<>Fiddle.
Most people (including myself) usually just skip to the next question unless there an easy way to reproduce the problem. Not only will you get more answers, but they will also be ready to use since you defined the setup. As of now, if my answer is at all usable for you, you will have to adapt it to your actual setup since I invented some imaginary tables etc.


Mysql InnoDB Full-Text Search IN BOOLEAN MODE Sort Rows In Order of Decreasing Relevance

(A guess)
There are many queries where the results are in a predictable order even without an ORDER BY.  However, this is often an artifact of the implementation and not a guaranteed ordering.
It used to be that GROUP BY automatically ordered the results as if there were a matching ORDER BY.  However, the existence of that as a guarantee hobbled the Optimizer.  With an appropriate INDEX, such a group by will continue to be in order.  But for other cases, the Optimizer would rather use an in-memory hash table; this essentially guarantees a somewhat random order; furthermore, it makes getting the order be an extra effort.
So, I guess, the manual is leaving the door open for some future optimization.
Rule:  If you want a particular order, specify ORDER BY.  If the Optimizer sees that the code can (or could) guarantee the order without doing any extra effort, it will do so.
PS, in the past, you could nullify the GROUP BY's implicit ordering by saying ORDER BY NULL.  (Kludgy?)


How to disable triggers from nesting/recursing when accessing the same table

You don't need an update statement:

In a "before update" trigger, you can directly change any of the values in the new row.

if ( old.amount <> new.amount && old.accid = new.accid ) then
   set adj = new.amount - old.amount ;
   set new.accid_curr_accbalance = new.accid_curr_accbalance + adj ;
else
   . . .


How to avoid MySQL slave to have errors after crash

Regarding de-synchronization of a single or master server: Mosty, the cause of these problems come from the fact that the master is not in full ACID mode or it is using MyISAM tables. In order to avoid problems with a server, only use InnoDB as the engine of your tables and set innodb_flush_log_at_trx commit to 1. This is not efficient -you will lose a lot of performance in a busy server except if you can get advantage of the group commit, but it is the only way to guarantee data consistency with disk (no transaction loss).
Many people, that do not have appropriate hardware (hardware cache), are forced to relax the value of this variable to 0 or 2, but in that case, you (or your hardware) is responsible to assure the availability of the disk/service in case something goes wrong (crash).

The second reason where disk can be out of sync with a master or single server is not using sync_binlog=1. By default, this is set to 0, and that means that you can write data internally, but that doesn't assure that it is written to the binary log. This shouldn't be an issue if you assume the master is always going to be back online, but you must understand that may be an issue. Activating it almost doubles your IOPS requirements, so be careful. It is not usually necessary and I do not recommend you to blindly activate it.

Third, the most common cause of desynchronization is the storage of the replication position in the non-transactional master.info file in the slave. This is the default configuration. I highly recommend changing the configuration of the variables master_info_repository and relay_log_info_repository to use TABLE (InnoDB transactional tables) instead of FILE. This will get rid of the desynchronization problems when the slave crashes.

Additionally, in order to make sure that the master and slave are always in sync, and that no transactions are skipped, I recommend you to use the GTID replication, available in MySQL 5.6/MariaDB 10. While it does not prevent the desync of the master and slave, it makes easier to identify it and resync master and slave. This is a tutorial on how to enable it on MariaDB 10. Please note that GTID implementations between MySQL and MariaDB are not compatible. Once GTID is running, things like failover are greatly simplified.

There could be other reasons for desynchronization, like undeterministic updates, or replication filters. Avoid the later, and you can mitigate the former by using row-based replication binlog_format = ROW.

On a different note, it is important to remark that MySQL default replication protocol is asynchronous, so one can never guarantee the same state on the master and the slave. If slave drift and other associated issues are a no-go, you can try alternatives like the semi-sync plugin for replication or Galera cluster, which both make tighter links between nodes.
Alternatively, I recommend you to both constantly monitor and repair replication problems with tools like pt-heartbeat, pt-table-checksum and pt-table-sync, which will avoid the need to manually provision the slave for every incident you encounter.

In order to have a more accurate answer, I recommend you to hire a consultant so that you can apply the best strategy in your case (some of the above may have an important impact on your performance while not being your specific problem).


Replication master binlog rotation when network is unavailable

Whenever a Slave loses network connectivity, the IO thread simply dies and tries to reconnect every 60 seconds. You did the right thing in pointing the Slave back but let's clarify what to use to point back with.

For the give SHOW SLAVE STATUS\G

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.17.20.102
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002814
          Read_Master_Log_Pos: 823078734
               Relay_Log_File: relay-bin.007364
                Relay_Log_Pos: 823078879
        Relay_Master_Log_File: mysql-bin.002814
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 823078734
              Relay_Log_Space: 823079071
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

WHAT YOU SHOULD NOT PICK

Master_Log_File represents the log file containing the last successfully executed SQL statement on the Master that was recorded in the Slave's Relay Logs.
Read_Master_Log_Pos represents the position within Master_Log_File of the last successfully executed SQL statement on the Master that was recorded in the Slave's Relay Logs.

WHAT YOU SHOULD PICK

Relay_Master_Log_File represents the log file containing the last successfully executed SQL statement on the Master that was executed on the Slave.
Exec_Master_Log_Pos represents the position within Relay_Master_Log_File of the last successfully executed SQL statement on the Master that was executed on the Slave.

In most cases, when caught early enough, Master_Log_File and Relay_Master_Log_File are the same. When there is significant replication lag or if the SQL thread breaks and the IO Thread keeps collecting, Master_Log_File and Relay_Master_Log_File will be different.

CONCLUSION

ALWAYS PICK Relay_Master_Log_File and Exec_Master_Log_Pos as a restart point.

Here are my other posts that reemphasize this paradigm:

Dec 05, 2011 : Does `Seconds_Behind_Master` show exact Slave Lag from Master?
Jan 04, 2012 : MySQL Slave replication: Need to determine where replication left off to start slave in correct location
Jan 24, 2012 : Mysql master-master replication auto flush old logs (Under Reason #3)
Apr 04, 2012 : Seconds Behind Master in Cloud Service

CAVEAT #1

Please do not be concerned with binlog rotation on the Master when it comes to Replication's status. The Slave's IO Thread is very sensitive to the Master's change. Here is how you can verify this:

STEP 01 : On the Slave, run SHOW SLAVE STATUS\G

STEP 02 : On the Master

For MySQL 5.5, run FLUSH LOGS; or FLUSH BINARY LOGS;
Prior to MySQL 5.5, run FLUSH LOGS;

STEP 03 : On the Slave, run SHOW SLAVE STATUS\G

You will see the Master_Log_File and Read_Master_Log_Pos change immedaitely.

CAVEAT #2

Since Relay_Master_Log_File has the most recent binary from the Master to be executed on the Slave, that's the log you keep on the Master. From the above SHOW SLAVE STATUS\G, you would run

PURGE BINARY LOGS TO 'mysql-bin.002814';


How do you concatenate an entire column with a group by?

You are "barking up entirely the wrong tree"!

What you want is something like:

CREATE TABLE patient_diagnosis
(
  patient_id INTEGER   NOT NULL, -- FK to patient table
  admission_time TIMESTAMP NOT NULL,
  diagnosis_id INTEGER NOT NULL, -- FK to diagnnosis table. Under **no** circumstances
                                 -- allow this to be free text! You might want to allow
                                 -- the text in here but from a dropdown list!
  initial_diagnosis_time TIMESTAMP NOT NULL,
  condition_cleared_time TIMESTAMP -- nullable because a patient might have an incurable
                                   -- condition like Cystic Fibrosis or Alzheimer's?
);

PRIMARY KEY something like (patient_id, diagnosis_id, initial_diagnosis_time)
or (patient_id, admission_time, diagnosis_id) depending on your needs.

So, for a patient with multiple diagnoses, you will have multiple records - you'll be able to SELECT current diagnoses and history. This is ideal for spreadsheets and much easier than a long comma-separated list.

SQL is not good at manipulating such lists - it's brilliant at "slicing and dicing" small, simple atomic records. For example, "How many people with a diagnosis of bubonic plague were admitted in the first quarter of last year?".

From here (Codd's rules):

  Rule 2: The guaranteed access rule:

  Each and every datum (atomic value) in a relational data base is
  guaranteed to be logically accessible by resorting to a combination of
  table name, primary key value and column name.

Comma separated lists violate this basic rule derived from relational algebra which is the only mathematically sound theory underlying data storage and manipulation!


sql server 2012 Express bulk-insert fialed due to message Operating system error code 5(Access is denied.)

Run SQL Server Configuration Manager and under SQL Server Services find your SQL Express instance and see what the Log On As is set to. Alternatively, you can right click the server name in SSMS, Facets, then scroll down to "Service Account" to see it.

If it's the default NT AUTHORITY\NETWORK SERVICE then grant access to a computer account called YOURDOMAIN\YOURCOMPUTERNAME$. You may also need to grant access to the person executing the command if they're logged on using AD.
Regarding your other user-level access issues, if a user is part of the db_owner role as shown in the screenshot they have the highest database-level access possible and no other database roles are needed. And if they're the database owner (separate to db_owner but pretty much the same privilege wise) then you can't grant additional database-level rights nor do you need to.


SQL Server Specific term full text search is not working

Your query actually needs to use two properties of the CONTAINSTABLE function-

<maximum distance> and <match_order>.

Using

CONTAINSTABLE(OpinionDocuments, HTML, 'NEAR((Foo, bar), 0, TRUE)') AS KEY_TBL

Specifies to find "Foo" near "Bar" with 0 search objects between (<maximum distance>  ", 0,") and in the order specified (<match_order> ",TRUE")


How to return the value from a query with SP_EXECUTESQL in a stored procedure

You could use a table variable to hold the results of the dynamic SQL call until you are ready to exit the stored procedure.  Just before returning from the stored procedure, select the data from the table variable.

DROP PROCEDURE IF EXISTS dbo.MyProc;
GO
CREATE PROCEDURE dbo.MyProc
AS
BEGIN
    --Declare Table variable to hold results of query
    DECLARE @Results TABLE (ResultText VARCHAR(500));
    DECLARE @Query NVARCHAR(30);

    --This is the query
    SET @Query = 'select @@version'

    --Insert the results of the dynamic SQL execution into the table variable
    INSERT INTO @Results
    EXECUTE SP_EXECUTESQL @Query

    --Do more stored procedure logic

    --Finally, select the results of the table variable
    SELECT *
    FROM @Results
END
go
EXEC dbo.MyProc


why do you "need" to use this parameter?  I suspect yet another x-y question - that you are asking the wrong question to solve the wrong problem. Alternatively, why can't you just add the necessary entry to the listener configuration?
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=3010))(Presentation=HTTP)(Session=RAW))
    )
  )


First, I store PDF's in the db as bytea's.  They are easier to manage than large objects and you really dont get very much out of the streaming API (now, streaming video, that should go into lobs).  The one real problem with doing this is that it takes extra memory to unescape the data when it comes back to the database and you really can't stream it effectively.

The key thing is that you have to unescape the data when it comes back.  libpq provides functions for that as do other programming languages.  A few (like Perl) provide db drivers which automatically handle these cases.

You are on to the right structure here.  Store it as a bytea and unescape it on retrieval.


Are there any best practices before moving SQL Server to another node (controlled failover)?

First make sure you have both logins and jobs fully synchronised between your instances.
there are different ways to achieve these.

  index maintenance jobs and backups

I had good results by using ola hallegren maintenance jobs for these.

  replication and service broker

Always point to the listener instead of individual IPs or server names.

  inside the jobs

inside every step of every job I test if I am in the primary server, in my current availability group called sqlprodag.
Something like this:

if (select
        ars.role_desc
    from sys.dm_hadr_availability_replica_states ars
    inner join sys.availability_groups ag
    on ars.group_id = ag.group_id
    where ag.name = 'SQLPRODAG'
    and ars.is_local = 1) = 'PRIMARY'
begin
    -- this server is the primary replica, do something here
    print 'yes, primary'

    EXEC MYDATABASE.[dbo].[usp_ins_APfactAPApplicationBreakdown]

end
else
begin
    -- this server is not the primary replica, (optional) do something here
    print 'Not a primary server'
end

and the job will only run on the primary server.

  best practices before performing a failover

planned manual failover of an availability group

--======================================================================================
-- set the availability group to synchronous mode
-- wait until all is synchronized
-- do the failover

--SQLPRODAG is the availability group name
--servers are SQLPROD1 and SQLPROD2
--======================================================================================

--ON THE PRIMARY
ALTER AVAILABILITY GROUP SQLPRODAG
     MODIFY REPLICA ON N'SQLPROD2'
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

--GO TO THE SECONDARY
ALTER AVAILABILITY GROUP SQLPRODAG FAILOVER;

     SELECT ag.name,
   drs.database_id,
   drs.group_id,
   drs.replica_id,
   drs.synchronization_state_desc
   --ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;

     SELECT ag.name,
*
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;


If your date_and_time values are always the same, use this query:

select place, food_id, '10:30am' as Time1, '12:30pm' as Time2, '4:30pm' as Time3, DATE_FORMAT(MAX(date_and_time), '%Y-%m-%d') as Date from Activity group by food_id, place;


If the value is always going to be the same you can do something like this:

SELECT 'USD' AS `MONEY`, a.*
  FROM INFORMATION_SCHEMA.SCHEMATA AS a

Just replace a.* with the columns you want to return and INFORMATION_SCHEMA.SCHEMATA with the schema and table(s) you want to query.

I hope this is helpful to you.


Oracle XE: Transfer Database from Linux to Windows

You should be able to solve this with a full export and import of the database.

Run a full export of the database on the linux VM
Copy the dump file to your Windows machine
Do a full import to the database on the Windows machine.

I believe Oracle reccomends the use of Oracle Data Pump:  expdp and impdp on Oracle 11g and up, rather than the traditional imp and exp tools.

Oracle Data Pump is well documented: http://docs.oracle.com/cd/E11882_01/server.112/e16536/part_dp.htm


I would like to create a replica-set with oplog

As per MongoDB Documentation sources from MongoDB IN ACTION The Replica sets rely on two basic mechanisms: an oplog and a heartbeat. The oplog enables the replication of data, and the heartbeat monitors health and triggers failover.

At the heart of MongoDB’s replication stands the oplog. The oplog is a capped collection that lives in a database called local on every replicating node and records all changes to the data. Every time a client writes to the primary, an entry with enough information to reproduce the write is automatically added to the primary’s oplog.Once the write is replicated to a given secondary, that secondary’s oplog also stores a record of the write. Each oplog entry is identified with a BSON timestamp, and all secondaries
use the timestamp to keep track of the latest entry they’ve applied.

Applications sometimes query secondary nodes for read scaling. If that’s happening, this kind of failure will cause read failures, So it’s important to design your application with failover in mind.

let’s look more closely at a real oplog and at the operations recorded in it.
First connect with the shell to the primary node and switch to the local database.

amj:PRIMARY> use local
switched to db local

The local database stores all the replica set metadata and the oplog. Naturally, this database isn’t replicated itself. Thus it lives up to its name; data in the local database is supposed to be unique to the local node and therefore shouldn’t be replicated.If you examine the local database, you’ll see a collection called oplog.rs, which is where every replica set stores its oplog. You’ll also see a few system collections. Here’s the complete output:

amj:PRIMARY> show collections
me
oplog.rs
replset.minvalid
slaves
startup_log
system.indexes
system.replset

replset.minvalid contains information for the initial sync of a given replica set member, and system.replset stores the replica set config document. Not all of your mongod servers will have the replset.minvalid collection. me and slaves are used to implement write concerns, and system.indexes is the standard index spec container.

  right now only one mongod instance is running on the server, serving
  all applications; should I run different mongod instances (one for
  every application)

I don't think so it's good idea to run different one mongod instance for one application.

For Your Further Ref Here and Here


Unknown column 'actions_in_group' in 'field list'

The SUM should be on actions_in_user_group, not actions_in_group

Same goes with actions_in_user_collection

I also added groupby_stream_date to assist with the group by

SELECT *,
   SUM(actions_in_user_group) AS total_rows_in_group,
   GROUP_CONCAT(actions_in_user_collection) AS complete_collection
   FROM (SELECT stream.*,date(stream.stream_date) groupby_stream_date,
          COUNT(stream.id) AS actions_in_user_group,
          GROUP_CONCAT(stream.id) AS actions_in_user_collection
       FROM stream
       INNER JOIN follows
       ON stream.user_id = follows.following_user
       WHERE follows.user_id = '0'
       GROUP BY stream.user_id,
            date(stream.stream_date)
     ) As something
   GROUP BY object_id,
            groupby_stream_date
   ORDER BY groupby_stream_date DESC;


FillFactor: IndexOptimize script by Ola Hallengren

Ola's script will only change Fill Factor if you assign a value to the @FillFactor variable.

IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', FILLFACTOR = ' + CAST(@FillFactor AS nvarchar)

Which means someone:

Modified the stored procedure to use 90 as a default value;
Ran it out-of-band from maintenance; or
Changed the values using a different script or the GUI.

You can script out the stored procedure to see if someone changed the default value,

Or you can query the dbo.CommandLog table to see if a REBUILD command was run with a Fill Factor of 90.

SELECT *
FROM dbo.CommandLog AS cl
WHERE cl.CommandType = 'ALTER_INDEX'
AND cl.Command LIKE '%FILLFACTOR%'

If someone changed it by using a different script, or the GUI, that would be much more difficult to track down.


Bound to query
If you can only change the view, not the query: this is 100 % equivalent using a correlated subquery instead of the LEFT JOIN:
CREATE VIEW the_view_new AS
SELECT a.id, a.name
    , (SELECT age_group FROM table_b WHERE id = a.id) AS age_group
FROM   table_a a;

Your query as is just reads top and bottom row from the index now, IOW blazingly fast. This is a workaround, gets more complicated with more columns and may exhibit weak spots with other queries.
Better query
Your query:
SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" LIMIT 1;

Only needs table_a. In the view, table_b is joined with a LEFT JOIN. Obviously, the query planner does realize that table_b is not needed for the result of min() and max() after a LEFT JOIN (contrary to what I assumed at first). The query plan does not mention table_b.

It can also only return a single row, so LIMIT 1 is only complicating matters for the query planner further, to no effect. (Seems not to be the tipping point here.)

The obfuscation confuses the planner enough to read the whole 100 million rows from the index (rows=106434752), while it would only need to look up first and last row. That's a whole lot of pointless work.

This is simpler, cleaner and faster, while returning the same:
SELECT MIN(id) AS min_id, MAX(id) AS max_id FROM table_a;

As you can see in the EXPLAIN ANALYZE output of this SQL Fiddle with 100k rows in table_a:
Simple query
(actual time=0.010..0.010 rows=1 loops=1)
Your query:
(actual time=0.012..400.498 rows=100000 loops=1)
This looks like a weakness of the query planner in any case. We should repeat the test with pg 9.4 and possibly file a bug report ...
Bound to use the view
If you are bound to use the view (as commented), there is a workaround to convince the query planner (id must be NOT NULL - true in your case since PK):
SELECT (SELECT id AS min_id FROM the_view ORDER BY id ASC  LIMIT 1) AS min_id
     , (SELECT id AS max_id FROM the_view ORDER BY id DESC LIMIT 1) AS max_id;

Check the query plans in the fiddle: two times rows=1.
Aside: I suggest you take a look at the chapter Identifiers and Key Words in the manual.


Mirror db from production to test and acceptance nightly or weekly

It is true that Availability Groups can make replicas of the active database and can support (depending on version) one or two readable replicas.  This does not work with SQL Server 2008 R2 servers.  But even if it did, this is not really the vehicle (yes, it is overkill) for getting a couple of databases to test with.

Using the backups that you are already taking to restore a database to another server is the quickest and easiest way to get a database to test against.

If you use mirroring, log shipping, or Availability Groups you can make something work within the parameters of those technologies. All three of these approaches require terminating those processes in order to do tests that include any updates to the data.

So, check this discussion for some details on using backup/restore and a useful script to help you:

Automatically restore SQL Server database from file from another server

EDIT: Getting a copy of a database to another server or using a new database name on the same server are most easily done by restoring the database from existing backups.  (Provided that your backups are frequent enough to support your tasks.)

If you restore to the same server but using a different database name, the logins are already on that server, so no extra work is needed. Likewise, the linked server is not needed in this case, since the information in the msdb database is local to your restore.
If you restore to an other server, depending on your needs you might need to add missing logins to the other server.  Since you would run the restore on the other server the linked server provides the path needed for the other server to select the information from the original server's msdb database to do the restore on the other server.

The linked server in the post was intended for case 2 above, where you need to derive the restore information for the other server from the original server.

Paul Brewer's script (or your edited version of the script) will likely help you automate your process.  But there are plenty of other scripts out there if you search for them.


Shrinking and consolidating to only one log file

I'd consolidate the log files first, before worrying about the right size to set the log.

To do that, you would need to use the EMPTYFILE clause in a DBCC SHRINKFILE operation, so that the second file can be emptied out and then deleted.

Once you are back to a single log file, you can then shrink right back down to zero (to remove any fragmentation), and then resize it appropriately, to ensure good distribution of Virtual Log Files (VLFs) inside the transaction log file.

There's no way to know what the "right" size is for your log file until you run a normal workload against the database, and see how big it gets. Once you know that, you shrink it down again, and then resize back to that value.

Make sure you set a fixed growth setting appropriate for the performance of your underlying I/O subsystem, and not a percentage file growth. You might want to set it to 100 MB, or 1 GB. It's entirely up to you.

Regarding TRUNCATEONLY, all it does is free any inactive portion, at the end of the transaction log file, back to the operating system. No data inside the log is moved. If there's no inactive portion of the log at the end of the file, no space will be released.

The Simple Recovery Model does not allow point-in-time recovery, but I think you know that. If you want to do point in time recovery, you'll need the Full Recovery Model, and perform regular transaction log backups.


If you can't scale a major RDBMS then your database design (includes indexing, queries and the like) or hardware is wrong. The choice of platform is almost irrelevant.

It is that simple. Especially when you mention "few hundred megabytes" which implies low volumes (I mean a few dozen writes per second)


Oracle database installation directory using SQL

I am sure there is a even smarter way, but if you use the default trace file directories, which is usually the case, you will in most cases find both ORACLE_BASE and ORACLE_HOME via

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

This gives you the absolute path to the trace files and they are inside ORACLE_BASE and ORACLE_HOME respectively.

HTH KR Peter

Edit: Have a look here for a more straight forward approach: https://dba.stackexchange.com/questions/97390/query-to-get-oracle-home-path-in-oracle-11g


Possible to query all SQL Server Indexes which have page locks enabled?

SELECT OBJECT_SCHEMA_NAME(object_id) AS object_schema,
       OBJECT_NAME(object_id)        AS object_name,
       name                          AS index_name
FROM   sys.indexes
WHERE  allow_page_locks = 1


One time data conversion in Oracle tables with excel data

If you do not have access to create Directory, then you can use SQL Loader to get data into the database (provided you have Oracle client installed on your system). An outline of the steps is provided below:

a.) Create a table with the same structure as your excel file

b.) Convert your Excel File to .csv format file

c.) Create a Loader Control File. Refer the link SQL Loaer Reference Guide

d.) Go to command line and execute sqlldr command with appropriate format

e.) Verify the bad file and err logs to find if there are any rejections and run them again.

f.) run the update between new table and your existing table.

In case you do not have Oracle Client available, then you can use the data import facility provided in various IDEs like Toad and SQL Developer.


Implementing foreign keys on generalized data model

You can have two (or more) foreign keys in a table. They can be both regarding the same attribute and referencing different tables. So you could have 2 foreign keys from racoon(pk), one referencing wild(pk) and the other referencing domestic(pk). Something like:

CREATE TABLE raccon
( mammal_id INT  PRIMARY KEY
, FOREIGN KEY (mammal_id)
    REFERENCES domestic (mammal_id)
, FOREIGN KEY (mammal_id)
    REFERENCES wild (mammal_id)
) ;

(assuming all tables have mammal_id as the primary key)


Does PostgreSQL always sequentially scan pages in the same order?

If multiple processes are trying to seq scan the same table, it will try to line them all up so that all read the same pages at about the same time, to maximize cache hits.  It does this by having the late comers start in the middle (where ever the existing ones happen to be at), and wrap around the end to finish the table at the page before where it started.  A side effect of this is that if one seq scan stops early, the next one will start up on the same page where the last one stopped.  So if the entire LIMIT can be satisfied by rows from one page, a scan like yours will just scan the same page over and over.
You can turn this optimization off with set synchronize_seqscans=off, then they all start from the first block in the table.


Getting error on a particular query on a table in Postgres

CLOG is the PostgreSQL Commit Log Manager (CLog). It's telling you here that the record of transactions is fuxed. This may have been caused from any kind of corruption. I would back up the database before attempting anything. There is one suggestion the lists,

  There is a commit log corruption in your database. You need to make the
  files with right size (256K of zeroes). A suitable dd from /dev/zero will
  accomplish this on modern Unixen (ie, anything that has /dev/zero). Filling
  of those files with zeroes nothing but  "all those old transactions are
  aborted".

  The safest way to resolve the issue by using the below command:-

dd if=/dev/zero of=Transaction_missing_file_of pg_clog bs=256K count=1

  (To make the uncommitted record as they haven't been committed).

This is followed up with the amusing reply,

  Just for the record, playing with pg_clog files like this is an
  insanely unsafe thing to do. Notably it will make all aborted
  transactions in that range become committed, leading to all sorts of
  fun. This is a desperate measures only action and not one that looks
  immediately related to the problem.

I would try backing up, upgrading to PostgreSQL 11, running VACUUM FULL, and restarting before I messed around with this in 9.6. But I would give it a shot if that failed.


Getting n slip number based on the number of times in a month a slip has been created

Use the Windowing Function ROW_NUMBER().

SELECT SLIPNO, EMPNO, DT,
ROW_NUMBER() OVER (PARTITION BY EMPNO ORDER BY DT ASC) AS SLIPNUMBER
FROM SLIPS;

DB Fiddle Test to show an example.

Also note that you should not use DATE as a column name, as it is a reserved word.


How to Upgrade Master Slave MySQL on AWS RDS?

Look for Amazon instructions.  With RDS, the instructions are roughly:

Upgrade the Slave first.  Replication works when the Master is 'older'.
Failover, but do not turn on the Slave.
Upgrade the Slave (was Master)
Turn on replication (START SLAVE)
Wait for replication to catch up before using the Slave.


Database design for a car dealers website

I would say that your second suggestion, haivng a TechSpecID in the Vehicles table, makes the most sense. A way of thinking could be, is a Vehicle implementing a TechSpec or is a TechSpec implementing a Vehicle? I.e. what comes first (the Technical Specification) and what is 'using' your Technical Specifications (the vehicles). But, in the end, it's all down to your context and what makes the most sense in your particular situation.

You have a very broad question so it is difficult to answer specifically what you might be looking for.

One thing I'd like to point out, however, is that most people don't prepend tg_ or fwk_ to their table names. You could use schemas if you'd like to differentiate them some...


This is what I ended up going with:

create table blog_posts (
    id uuid not null,
    tenant_id uuid not null,
    title text not null,
    content text not null,
    primary key (id)
);

create unique index blog_posts_tenant_id_id_idx on blog_posts (tenant_id, id);

create table comments (
    id uuid not null,
    tenant_id uuid not null,
    blog_post_id uuid not null references blog_posts (id),
    content text not null,
    primary key (id)
);

alter table comments add foreign key (tenant_id, blog_post_id)
    references blog_posts (tenant_id, id)
    on update cascade;


Is it safe to change the Recovery Model of SSRS database ReportServer to SIMPLE?

If you're not using transaction log backups, then you don't need FULL recovery mode. It's that simple.


If I understood correctly, if stored procedure is called with @vCol1 = 'AA', @vCol2 = 'BB' and @vCol3 = NULL then you want to update only Col1 and Col2 keeping the Col3 value intact.
Then you can go with the following solution. I agree its not the best one but it will get work done in simple manner.

UPDATE table
SET col1 = ISNULL(@vCol1, col1)
  , col2 = ISNULL(@vCol2, col2)
  , col3 = ISNULL(@vCol3, col3)
WHERE id = 1


Postgres query plan has high rowcount, slow execution

(Unsure whether this belongs as an answer, comment or edit so I'll leave it here unless I hear otherwise.)

I was unable to find the cause of the bad row count. However, after rewriting the query like this,

SELECT t1."VisitorID", t1."Timestamp"
FROM Table1 t1
WHERE NOT EXISTS (
    SELECT t2.VisitorID, t2.Timestamp
    FROM Table2 t2
    WHERE t2.SessionID = t1.SessionID
        AND t2.Timestamp > t1.Timestamp
)

the estimated row count went to a normal level and execution time decreased drastically, even on large datasets. I tested this with and without composite indexes like so:

CREATE INDEX "IX_Temp_Table1_Visitor-Session-Time"
ON "Table1"
USING BTREE ("VisitorID","SessionID","Timestamp");

CREATE INDEX "IX_Table2_Visitor-Session-Time"
ON "Table2"
USING BTREE ("VisitorID","SessionID","Timestamp");

and found no significant performance difference.


Why does Concatenation operator estimate fewer rows than its inputs?

To quote Campbell Fraser on this Connect item:

  These "cardinality inconsistencies" can arise in a number of situations, including when concat is used. They can arise because the estimation of a particular subtree in the final plan may have been perfomed on a differently structured but logically equivalent subtree. Due to the statistical nature of cardinality estimation, estimating on different but logically equivalent trees is not guaranteed to get the same estimate. So overall no guarantees of expected consistency are provided.

To expand on that a little: The way I like to explain it is to say that the initial cardinality estimation (performed before cost-based optimization starts) produces more "consistent" cardinality estimates, since the whole initial tree is processed, with each subsequent estimation depending directly on the preceding one.

During cost-based optimization, parts of the plan tree (one or more operators) may be explored and replaced with alternatives, each of which may require a new cardinality estimate. There is no general way to say which estimate will be generally better than another, so it is quite possible to end up with a final plan that appears "inconsistent". This is simply the result of stitching together "bits of plans" to form the final arrangement.

All that said, there were some detailed changes to the new cardinality estimator (CE) introduced in SQL Server 2014 that makes this somewhat less common than was the case with the original CE.

Aside from upgrading to the latest Cumulative Update and checking that optimizer fixes with 4199 are on, your main options are to try statistics/index changes (noting the warnings for missing indexes) and updates, or expressing the query differently. The goal being to acquire a plan that displays the behaviour you require. This may then be frozen with a plan guide, for example.

The anonymized plan makes it hard to assess the detail, but I would also look carefully at the bitmaps to see if they are of the 'optimized' (Opt_Bitmap) or post-optimization (Bitmap) variety. I am also suspicious of the Filters.

If the row counts are anything like accurate though, this seems like a query that might benefit from columnstore. Quite aside from the usual benefits, you might be able to take advantage of the dynamic memory grant for batch mode operators (trace flag 9389 may be required).


First order by just the date, then prioritize the values where the timestamp is not midnight over those where it is.

ORDER BY CONVERT(DATE, col),
  CASE WHEN CONVERT(DATE,col) = col
  THEN 2 ELSE 1 END,
  CONVERT(TIME, col);


How do I kill a rollback process when I want to drop the database?

In short you can't. You have to wait for the transaction to rollback before the locks are released.

I suppose you could stop the SQL service and delete the database files. Then when the instance comes back online delete the suspect database.  But this requires an outage and that you are very sure you are deleting the correct files.


CPU and Hard Drive Spike for 20-30 Minutes

...once a day there's a CPU and hard drive activity spike for about 20-30 minutes...the buffer cache hit ratio drops to almost 0 at the beginning of this spike (returns to 99+% quickly).

This spike is almost certainly due to daily maintenance tasks - specifically, index rebuilds and DBCC CHECKDB.  These are very resource-intensive operations.  For instance, the buffer cache hit ratio change you mentioned is likely due to index rebuilds reading every page out of various indexes as they are processed.

  Any way to find out what might be causing this spike?

Look for SQL Server Agent jobs that are configured to start during this time period.  You can see an overview of scheduled jobs on the instance by double-clicking "Job Activity Monitor" in SSMS under the SQL Server Agent node.

You can also check for Maintenance Plans under the "Management" -> "Maintenance Plans" node (also in SSMS).

  I'm collecting data...in preparation for moving it to Azure

In terms of cost-saving in the cloud, it's possible you could forego some, or all, of the index maintenance.  One way to do this would be to use Ola Hallengren's Index and Statistics Maintenance procedure, which will skip rebuilds on indexes that are small or not-very-fragmented.


Seems like a good reason to visit the Oracle Documentation site. The 2-day dba documents are very good.

If your database is running in ARCHIVELOG mode, it copies all transactions to the archivelog destination. The transactions are always written to the redolog files but when they are full, they are only saved when running in archivelog mode. This enables you to restore your database to any point in time. This restore operation starts with restoring a full backup where you can apply the archives until you reach the point in time where you want to stop the recovery. For example, close before a table was dropped.

Normally production backups are made with the database online. Again, a reason to run your databases in archivelog mode. IF not running archivelog mode, in a disaster scenario you might loose all transactions made since the last backup. If your transactions are really important it could be smart to copy the archivedlog files to a second DC on a very regularly basis. How regularly depends on the cost of loosing transactions. If you can handle loosing one day, you copy daily, most sites copy a few times/hour or even use standby databases that receive the transactions in a near sync way.

In general you start restoring the database and perform a recovery until time. Normally we use rman to do this, something like

    rman
    run
    {
    set until time to_date('20140914 14:22','yyyymmdd hh24:mi');
    restore database;
    recover database;
    }

when ready and not done a full media recovery, open the database with a reset logs option.

alter database open resetlogs;

To prevent nightmares from happening, it might be smarter to get involvement from a dba.


Should I create a separate database table for temporary users

Why don't you just store it in the main user table (keeping a unique ID will be a nightmare otherwise), and have an ACTIVE bit field that you include in your indexes (you can filter if you like).

It won't really slow down access to the table since your indexes will be able to very quickly sort by active/inactive but you also avoid the headaches caused by trying to "roll your own" identity field.


How to efficiently handle querying for different combinations of optional parameters

I highly recommend that you don't go down that path.
You are mixing business logic with relational querying, and that's a sure recipe for many headaches later on, especially in terms of performance.
Keep in mind the rule of '1 query for 1 task', and the simpler and more granular, the better. You are trying to write 1 query, to handle different tasks.
Use a business control layer either on the client side, a dedicated business tier, or even in a stored procedure on the server, and execute different dedicated, simple SQL queries based on your logic.
For example, in your case you could write a procedure that will accept both parameters and execute the right query based on the values provided.
The procedure has negligible overhead, and you get the huge advantage of having separate, optimized query plans created for every combination.
CREATE PROCEDURE SomeProc
@PartNum VARCHAR,
@JobNum VARCHAR
AS
BEGIN
IF @PartNum IS NULL AND @JobNum IS NULL
-- Specific Query 1 (or execute sub-procedure) goes here
ELSE IF @PartNum IS NOT NULL AND @JobNum IS NOT NULL
-- Specific Query 2 (or execute sub-procedure) goes here
ELSE IF @PartNum IS NULL AND @JobNum IS NOT NULL
-- Specific Query 3 ... etc.
ELSE --
-- Generic query, not optimized
END

UPDATE:
If you need this to work for a large number of parameters, I highly recommend that you read this article by Erland Sommarskog. It is the best article that I know of for this type of challenge, and will offer you several solutions, with thorough analysis of the pros and cons of each. It's long, it's not an easy read, but it will be worth every minute you spend on it.
What worked well for me in previous similar cases, is a hybrid approach.
I start with a generic solution, you will find a few options in the article.
Then, I set up a trace, or a log table to record actual usage stats by the users and let it run for some time.
Although product and marketing will say that all combinations are equally important, you will find that in real life, most users tend to use only a few and repeat the same pattern.
Then, take the top used combinations, and write specific queries just for those, and use the generic query for the few instances that a unique combination of parameters is used. I've added the line of code to the example.
This way you get the best of both worlds - for most executions, you will get excellent performance, and still support all possible combinations.
BTW - An interesting side effect of this approach, is that over time users start to notice that it takes much longer when they use the non-standard combinations, and then they either complain about it, so you can write a specific query for that case and make them happy, but usually they tend to just use the common combinations more, that are way faster :-)
HTH


Well, the trick is that a database can only specify which "locale" it is used for at creation time. When you create a database you either specify what you want by specifying the codeset, territory and collation (example CREATE DATABASE MYDB AUTOMATIC STORAGE YES ON '/data' DBPATH ON '/dbdir' USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM), or you let DB2 guess based on some system settings.

You can check what yours is set to by running db2 get db cfg for <db name>. In the case of what was used above you would see the following:

Database territory                                      = US
Database code page                                      = 1208
Database code set                                       = UTF-8
Database country/region code                            = 1
Database collating sequence                             = IDENTITY
Alternate collating sequence              (ALT_COLLATE) =

Note, that you cannot change these settings after a database is created. One option would be to export the data from your database (using db2export), drop and rebuild the database the with language settings you want, and then re-import the data (using db2import).

Another option would be to see if there is a way to represent your character strings as pure "bit" data and store the bit sequence rather than the "string" or declare a field as VARCHAR FOR BIT DATA and store your strings as bit sequences (though I'm not 100% positive on that one...).

Also check out the links below as they have more information on the language thing and how to deal with multi-code-page results. Note that some of this information requires DB2 to be at least at version 9.5 or higher.

Choosing the code page, territory, and collation for your database
Unicode character encoding
Character conversion between different code pages

as well as check out the whole Multicultural support parent/sibling links related to that list link (the Character conversion between different code pages).


You will want to use the Lookup Table approach. If absolutely necessary there are tools in most database implementations you can use to turn rows into an array like result for another application to use. But as far as Databases are concerned stick to the row method to maintain a nice clean relational model you can JOIN easily on.


Encryption of Data, Log and Backup files using Always Encrypted in SQL Server 2016

The whole entire data/log file doesn't get encrypted. Only the specific fields you encrypt are encrypted, and yes, those are encrypted in the data, log, and backup files. SQL Server never sees the unencrypted values.

In the docs for Always Encrypted, Microsoft explains:

  Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine ( SQL Database or SQL Server).

That isn't great wording, but what it means is that not only is the data encrypted, the SQL Server engine doesn't even have the key to decrypt it for you.

Thus the name - as far as the SQL Server engine is concerned, the data is Always Encrypted.


Granting access to users to run specific SQL jobs that runs SSIS packages

Create a stored procedure that uses sp_start_job and grant the execute permission on the stored procedure to the necessary users.  The stored procedure can use EXECUTE AS to elevate its permissions so that it can start the job even though the users don't have permission to start the job.


Is there a way to connect to postgres when have error "sorry, too many clients already "?

Slots reserved for postgres superuser
PostgreSQL reserves a few connections for superusers. If your application is not using superuser connections you can connect as a superuser (eg postgres). Then run diagnostic queries like:
select * from pg_stat_activity;

superuser_reserved_connections setting
See the superuser_reserved_connections setting in the Postgres configuration. Defaults to 3 reserved slots.
To quote the version 11 documentation:

Determines the number of connection “slots” that are reserved for connections by PostgreSQL superusers. At most max_connections connections can ever be active simultaneously. Whenever the number of active concurrent connections is at least max_connections minus superuser_reserved_connections, new connections will be accepted only for superusers, and no new replication connections will be accepted.
The default value is three connections. The value must be less than max_connections minus max_wal_senders. This parameter can only be set at server start.


Adding one record from master-table in addition to other records from detail-table to a MySQL-View

CREATE VIEW v_Customers_Contacts
AS

SELECT a.Matchcode as Company
     , b.FirstName
     , b.SecondName
     , b.Phone
     , b.Email
FROM Customers          as a
JOIN Customers_Contacts as b ON b.ID_Customer = a.ID

UNION

SELECT Matchcode as Company
     , NULL      as FirstName
     , NULL      as SecondName
     , Phone
     , Email
FROM Customers ;

When selecting from the view:

SELECT Company
     , FirstName
     , SecondName
     , Phone
     , Email
FROM v_Customers_Contacts
ORDER BY Company, SecondName, FirstName ;


SQL Server 2005: Job scheduled every 4 hrs, but sp_help_jobactivity shows unexpected value for next_scheduled_run_date

The job runs every 4 hours starting with the start time in the schedule. If you run it manually that has no effect on the next scheduled startstart time.


created_at vs meaningful db column name , or both?

If you're setting a standard that all tables need a column_x, then the standard columns need to be... well... standard. The only way for the columns to be generalisable sensibly in the way you describe is to have created_at be metadata about the record and not the data for the row. processed_at is a different, separate data point that says something about the external world. If you hijack the created_at because it has a reasonably similar name then you've de-standardised the usage of that column - at which point why bother adhering to the standard rigorously? Just name each column what it is.

  TL;DR: use both if you need the standard columns to support your ORM. Don't hijack created_at for non-metadata