In my experience with audits, some questions repeat every year, and some are tweaked repeatedly, either as the company moves from being less compliant to more compliant (requiring more detail), or as the rules change over time.

Also, some requests require human verification (for example, if you're asked to note all accounts with elevated privileges, and why those accounts have those privileges, then simply check for anything new added or old removed, and then handing them the same thing you gave last year (modulo those changes), doesn't really do the trick - someone actually needs to look at the reasons given, and be sure they still apply. Because, if next year someone responds "Gee, that shouldn't be on there still, we stopped using that software 5 years ago," lots of people will not be happy.

Finally, some things (like screen shots showing the "sa" login is disabled, if it is) aren't easily automated (as far as I know), or require enough effort to automate that it would take years for it to balance out.

For many of the rest, the auditing team wouldn't necessarily want there to be an automated where miscellaneous people can run (for instance) a query to see what logins have sysadmin access. Among other things, if such a tool is available, it's exploitable. They'd probably rather have a sysadmin user run these things as needed, for that reason.

However, that doesn't mean the sysadmin user can't save the script they ran last year, and use it again next year. Upgrades and such can make this less useful (if a command you were using was removed, or if new options make your script obsolete), but a starting point is always good.

When I was responsible for this sort of thing, I had a few scripts saved off that I'd run each year to provide as much of what was wanted as I could. In fact, some of the data was part of jobs that triggered things (like reviewing privileged logins/users at least 4 times a year, or whatever). Early on I would tweak them regularly to make sure I was getting everything needed, but after two or three iterations they generally stayed the same.

Unfortunately, as such code belongs to my former employer, I can't really pass any of it on to anyone else. However, once you have what you need for this year, make sure it's saved, and appropriately documented (as in, what was used to fulfill what requests), and known to all DBAs in the department; put it in your knowledgebase, or wherever your people look to find how to do something they haven't done before. Also keep the requests and responses, so you can refer back to them (as the auditors change, sometimes the terminology they use when making a request also changes - being able to say, "Here's what I think we gave the auditors last year in response to a similar request - is that what you're looking for?" is very helpful.

If your audit team is good about maintaining an email-distribution list, then automated jobs are much more useful; if not, then your job output is likely to be sent to one guy who's not doing audits any more, and 5 addresses that no longer exist.

The best requirements from your auditors (internal or external) tends to be "whatever worked last year". In fact, if you can say "here's what we gave them last year", and it's not good enough this year, it tends to put the onus on the auditors to give you an updated script that covers what else they need. After all, who's to say you know how to get it, or you'll get it right (sometimes, the "DBA" is the one guy who could actually write a SELECT query if he had to, maybe even without a query building tool).

If you're too swamped to do something when they ask, don't be afraid to tell them. Maybe they don't have to have it for a month, and are fine with you not running it for three weeks. They are being paid by your company, and generally don't want to interfere more than they have to.

That said, I always try to be as helpful as possible with the auditors. Yes, they're interfering with your normal work, but they're just doing their job, and it's necessary for your company to continue. And, if you're always honest and up-front with them, they may be far more understanding if they do see something amiss than if you've seemed adversarial in general.


In MySQL, how exactly does data flows from query to disk?

A Google of "peter zaitsev" file systems buffering double write gives several good references (Peter Zaitsev works for Percona who are big hitters in the MySQL world.

Of these, I picked out 4 as being of particular interest[1, 2, 3, 4].

In the first reference, Zaitsev gives a great précis of the various reasons why partial page writes might occur (section entitled "Now lets talk a bit about partial page writes").

Zaitsev then goes on to a section about how double write works, but I think that a better explanation is to be found in reference 2 - in particular this bit:

  If a partial page write occurs in the data files, InnoDB / XtraDB will
  check on recovery if the checksum of the page in the data file is
  different from the checksum of the page in the doublewrite buffer and
  thus will know if the page is corrupt or not. If it is corrupt, the
  recovery process will use the page stored in the doublewrite buffer to
  restore the correct data.

  If a partial write occurs in the doublewrite buffer, the original page
  is untouched and can be used with the redo logs to recover the data.

Basically, this behaviour is controlled by a system parameter called innodb_flush_log_at_trx_commit. This section is too big to be quoted in its entirety here, I'll just quote the bit about the default value: (I strongly recommend that you read it in full to get a good overview).

  ◾ The default value of 1 is required for full ACID compliance. With
  this value, the contents of the InnoDB log buffer are written out to
  the log file at each transaction commit and the log file is flushed to
  disk.

However, this section of the manual also says:

  Controls the balance between strict ACID compliance for commit
  operations, and higher performance that is possible when
  commit-related I/O operations are rearranged and done in batches. You
  can achieve better performance by changing the default value, but then
  you can lose up to a second of transactions in a crash.

In plain English this means that "ye gets what ye pays for" - i.e. there's a balance to be struck between data integrity and performance. Despite searching high and low however, I haven't been able to find any more than this in terms of detail - unless you want to go rummaging round the code (above my pay grade I'm afraid :-) ).

You can switch off the parameter innodb_doublewrite and then this leads one to another system parameter innodb_flush_method - again it's all about performance vs. data integrity. My references 3 and 4 go into detail on this - basically the debate in these two references is about whether it is a good idea to switch off doublewrite if you have a transactional file system.

The consensus appears to be that if you're using ZFS (and other, perhaps esoteric, file systems/devices), you're safe switching off doublewrites - assuming RAID with battery operated disk caches. Reference 4 IMHO suggests that the debate is still open as to whether ext4 is safe - also see here.

The diagram below provides a good overview of the MySQL's InnoDB architecture.


Is there a way to speed up a DISTINCT query?

As suggested, an index on (site, sn) could speed this up, especially if the table is well-vacuumed so that it benefits from index-only scans.

If that is not enough of a speed up, then there is a way to use this index in a "skip scan" or "Loose Index Scan" to speed up queries where the number of distinct values is much lower than the size of the index. Unfortunately PostgreSQL's planner does not automatically detect opportunities for this, but you can force it yourself by writing a recursive common table expression.  You can find a discussion of this technique on the PostgreSQL wiki, although you will need to adapt it to your parameterized version.

The resulting queries are quite ugly, so you might want to wrap them into a view.  Or in this case, since it is parameterized, into a set-returning function.  Something like this works for me:

CREATE FUNCTION public.foobar(text) RETURNS SETOF text
    LANGUAGE sql
    AS $_$ with recursive r as (
  (select sn from t where site = $1 order by sn limit 1)
   union all
  SELECT (SELECT t.sn FROM t WHERE site=$1 and t.sn > r.sn ORDER BY sn LIMIT 1) from r where sn is not null
)
select * from r where r.sn is not null $_$;

Setting up the table and index with:

create table t as select
    floor(random()*1.2)::int::varchar as site,
    (-floor(50*log(random()))::int)::varchar as sn
from generate_series(1,10000000);

create index on t (site ,sn);


Need help in picking solution and understanding when to add columns vs EAV


When is it appropriate to use a scalar valued function?

There are good reasons to be critical of the performance of scalar UDFs. Performance problems include forcing the entire plan to be run serially, excessive memory grants, cardinality estimation problems, and lack of inlining. However, that doesn't mean that they don't have good use cases.

Consider a series of stored procedures which needs to call the same complicated business logic that returns a scalar value. A scalar UDF used to assign the requested value to a local variable can work very well. The performance issues of scalar UDFs are really only felt when they are used inside of a query. Using a Scalar UDF inside a stored procedure will not force the entire stored procedure to run serially.

Some applications might require you to send the exact same SQL code to multiple RDBMS platforms. Scalar UDFs can be a good workaround to get functionality which is implemented in different ways on different platforms. For example, truncating a datetime in both SQL Server and Oracle is fairly difficult to do with the same code unless you use a UDF. In this case you will pay the performance penalties of UDFs, but sometimes they are your only option. For a scenario like this you may not be able to use TVFs because that syntax may not be supported by the other platform.

Other than that it's difficult to think of a good use for scalar UDFs. I suppose you might have a query for which response time is not critical. It might be easier to code the query with a scalar UDF, but you're likely to get better (or at the very least no worse) performance with a TVF instead.


Show all options with many-to-many and many-to-one

Given this sample:

CREATE TABLE #Products (ProductId int, Name varchar(200));
insert into #Products values
(1, 'Desk'),
(2, 'Table');

CREATE TABLE #Options(OptionId int, ProductId int, Name varchar(200));
insert into #Options values
(1, 1, 'Colour'),
(2, 1, 'Weight'),
(3, 2, 'Colour'),
(4, 2, 'Width');

CREATE TABLE #OptionsValues(OptoinValueId int, OptionId int, Name varchar(200));
insert into #OptionsValues values
(1, 1, 'Oak'),
(2, 1, 'Beech'),
(3, 2, '10kg.'),
(4, 2, '12kg.'),
(5, 2, '13kg.'),
(6, 3, 'Ivory'),
(7, 3, 'Blue sky'),
(8, 4, '10cm.'),
(9, 4, '20cm.');

You can use STUFF((SELECT .... FOR XML PATH(''), 1,1,'') to get a concatenated column's values, delimited by some character (usually comma)

SELECT p.Name
       ,STUFF ((SELECT ', '
                       + Name
                       + ': {'
                       + STUFF((SELECT ',' + Name
                               FROM #OptionsValues ov
                               WHERE ov.OptionId = o.OptionId
                               FOR xml path('')
                              ), 1, 1, '')
                       + '}'
                FROM #Options o
                WHERE o.ProductId = p.ProductId
                FOR xml path('')
              ), 1, 2, '') Options
FROM   #Products p;

The result is:

+-------+--------------------------------------------------+
|  Name |                      Options                     |
+-------+--------------------------------------------------+
|  Desk | Colour: {Oak,Beech}, Weight: {10kg.,12kg.,13kg.} |
+-------+--------------------------------------------------+
| Table | Colour: {Ivory,Blue sky}, Width: {10cm.,20cm.}   |
+-------+--------------------------------------------------+

Check it here: http://rextester.com/YBX28904


Is it faster to split a large table into 12 rolling monthly tables & use UNION them for reports or keep large table & delete rows older than 1 year?

Do not split the table. Use Range Partitionig instead. Study MySQL 8.0 Reference Manual  /  Partitioning. Use MySQL 8.0 Reference Manual / ... / ALTER TABLE Partition Operations. Keep in mind that it is best to create partitions for future periods in advance (and do not forget to create LESS THAN MAXVALUE partition). Creating new partitions and moving existing data to them at the same time can be more expensive.

Do not delete data permanently. Move it into separate archive table. If you do not have enough disk space - make a backup of such an archive table, check its validity, and only if successful then delete the table. If necessary (it will - be sure!), you can recover and use this data.


How to change "Initial File Size" before restoring

I don't believe this is possible, typically the database would need to be shrunk before or after a restore.

If this were me I'd restore the database on another server, maybe on a dev server, shrink the files, and then copy and restore locally.


Problem with complex queries after migrating from MySQL 5.1 to MariaDB 10.4

I would suggest getting rid of the single-column indexes for device and person, and replace them with:
INDEX device_person_index (deviceid, person_id)

If you have queries with predicates for person_id but not deviceid, you can add another index for that:
INDEX device_person_index (person_id, deviceid)

The above suggestion holds for both tables. I find KEY in this context very confusing, so I replaced it with INDEX which I beleive is a synonym.
I don't know whether MySQL/Mariadb can do index anding, but a composite index is anyhow more efficient than two separate indexes.


Handling differences between MS-SQL and PostgreSQL money datatypes

In Postgres, the cast from money to numeric is exact, immutable and does not round.
In SQL Server the data type MONEY uses 8 bytes to store a values in any currency in the numeric range from -922337203685477.5808 to 922337203685477.5807.
Matches numeric(19,4).
In PostgreSQL the data type money also occupies 8 bytes but the allowed range is -92233720368547758.08 to +92233720368547758.07. Same precision, but different scale by default (without LC_MONETARY setting other than 'C') - only two fractional digits.
Matches numeric(19,2).
But, quoting the manual:

The fractional precision is determined by the database's lc_monetary setting.

I updated the tag info for money accordingly.
As @a_horse commented (repeatedly), money is not defined by the SQL standard.
And it's pretty much consensus around here to stay away from the money data type if you can.


Full text search partial search with 'in:*' not working

That is because "in" is a very frequent word in the English language, so it is eliminated as a "stopword".
You could use the simple text search configuration, but I suspect that full text search is not the right tool for this. Trigram indexes work better for substring searches, but nothing will really work well for strings as short as two characters.


SQL Server 2008 R2 SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file '

Just a hunch... is that ldf growing when you get this error? I seen this error a few times and always been that someone set the growthrate in too big steps for the io subsystem to handle at same time as normal io within 15 seconds. Go and see if it happens to bet set to 10% and how big a step that would be. If it is GBs you might want to set it to a smaller size to grow and also in MB instead of percent.

Who knows I might get lucky on this one ;-)


1.1.2 as a location seems very arbitrary.  Your structure will not allow you to do much dynamic reporting and scaling up would cause issues. Every time you need to query up the chain, you will need to break the item up, which will break your index.

1.1.1 may refer to NY Fl 5, but 1.1.2 could refer to LA (los angeles, Louisiana,???) floor 4.

TBL1 - 1, USA
TBL2 - 100, STATE, TBL1.FK
TBL3 - 1000, CITY, TBL2.FK
TBL4 - 10000, BUILDING, TBL3.FK, May want to also include ZIPCODE here.
TBL5 - 100000, FLOOR, TBL4.FK

In you Data warehouse, you could flatten it, but you might want to keep separate Dimension tables for cube reporting.


Why do my backup policies fail when a database's guest user is disabled?

I've spent most of today bashing my head against this, and didn't come up with anything. I tried:

Messing with msdb roles on the CMS
Messing with msdb roles on the target servers
Messing with server roles on the CMS and target servers
Petitioning the gods of old
Trying something convoluted involving credentials and proxies

In the end, nothing worked. I believe this is possible on SQL Server 2014, which has a "CONNECT ALL DATABASES" server-level permission, but 2012 has no equivalent that isn't also a much larger security risk than I'm willing to accept.

I decided to bite the bullet and add the account as a user on every database. Here's what I did:

exec sp_MSforeachdb 'if ''?'' in (''master'',''msdb'',''tempdb'') return; use [?]; CREATE USER [domain\account] FOR LOGIN [domain\account];'

Executing this script against the CMS added the user to every database on every instance except for master, msdb, and tempdb. I left model in so that when we create databases in the future, they'll already have the user added.

If anyone has a better way that doesn't involve mapping to every database, feel free to share, but at this point I'm not convinced it can be done (short of making the account sysadmin, which I doubt would make it into production).


Why can't trigger be dropped from database?

The transaction is being rolled back in the DDL trigger, PreserveTrigger, not the trigger you're trying to drop, trg_write_something. This has nothing to do with the code in the table-level trigger, and nothing to do with permissions either. A sysadmin doesn't get to blindly bypass things like DDL triggers, though it is possible for the code in the trigger to have been written to check the user's security level before deciding what to do (but that is uncommon). It is, of course, very easy for a sysadmin to disable or drop a DDL trigger, obviously.

You're seeing these specific errors because a user-defined error message is being raised within the DDL trigger, followed by an explicit ROLLBACK TRANSACTION;. In a comment above, I mentioned that it could be because of a runtime error, but that is not enough to generate the pair of error messages you are receiving (or force an explicit rollback, unless you somehow managed to get a DDL trigger to raise a severe enough error level to terminate the batch - 16 wouldn't do that).

You can discover this DDL trigger as follows:

SELECT name
  FROM sys.triggers
  WHERE name = N'PreserveTrigger'
  AND parent_class_desc = N'DATABASE';

Or all DDL triggers like this:

SELECT name
  FROM sys.triggers
  WHERE parent_class_desc = N'DATABASE';

(You can also find them in Object Explorer under Programmability > Database Triggers.)

And you can see the code this way (which you should probably do before you drop it - maybe it's there for a reason, even if it is currently standing in your way of dropping this specific trigger):

SELECT m.[definition]
  FROM sys.sql_modules AS m
  INNER JOIN sys.triggers AS t
  ON m.[object_id] = t.[object_id]
  WHERE t.name = N'PreserveTrigger';

Or by right-clicking in Object Explorer and selecting Script Database Trigger As > CREATE To > New Query Editor Window.

You can disable the trigger temporarily like this...

DISABLE TRIGGER PreserveTrigger ON DATABASE;

...which, again, is probably better/safer than just dropping it.


How can I see execution time of an ALTER TABLE statement?

Turn on \timing, run your ALTER TABLE and SELECT 1.
Subtract the values for a good estimate.


Migrate MySQL tables to new server with same tables already on it?

So, you want to import the new data without overwriting and no duplicate keys, right? I'm no expert, but as I see it, you can either:

Remove the current data and import the whole old DB,
Use --ignore or --replace on mysqlimport - either won't duplicate rows (they'll either --replace existing rows or --ignore duplicates),
mysqldump without creating the database, the table and using the Insert Ignore option (--insert-ignore), so it'll insert the data and skip the duplicate keys,
Use --force when importing, to skip errors while inserting, this way if there's already one row with the key being inserted, it won't generate an error, just skip it and go to the next insert.

You can look up another already answered questions and try to find the best solution: here or here.

I tried with the --insert-ignore option on mysqldump and when importing I had no errors, the new data was inserted with no duplicates.


SQL Server AlwaysOn database stuck in Not Synchronizing / In Recovery mode after upgrading. Error: Cannot open database '...' version 782

After poking around in SSMS for a while I noticed that on the secondary replica there was a pause icon next to the Availability Databases. The primary had shown both were "green", but there was an option on the secondary to Resume Data Movement. I resumed the first database, and immediately the In Recovery status message was removed. A minute later it changed from Not Synchronizing to Synchronized, and everything worked as expected.

Here is a screenshot of the AG Databases after I fixed "Patch", but before fixing the test database:

Note you can also use TSQL on the secondary to resume replication on multiple database at the same time:

ALTER DATABASE [Patch] SET HADR RESUME;
ALTER DATABASE [test] SET HADR RESUME;
GO


SQLite where condition - compare two last rows

Something like this should work (it's hard to verify without a fiddle):

WITH temp AS (
  SELECT code, sma_9, sma_26, rsi,
    RANK() OVER (PARTITION BY code ORDER BY date DESC) AS rank
  FROM Stock_price_Table
)
SELECT t1.code
  FROM temp AS t1
  INNER JOIN temp AS t2
    ON t1.code = t2.code
   AND t1.rank = 1
   AND t2.rank = 2
  WHERE t1.sma_9 >= t1.sma_26
    AND t2.sma_9 <= t2.sma_26
    AND t1.rsi >= t2.rsi

Basically, in the temp table you select the stock records, RANKed by date and 'grouped' by code (through the PARTITION BY clause). In the main SELECT, you link the first and second ranked records per stock code, and select only those which match your criteria (the WHERE clause).


Why SqlBulkCopy gets slower by table's row count?

Why you write that you don't make any index ?
What is PK_DeliveryReports in your opinion ?
As for me - the clu of your problem is just rebuilding of PK.
Try to use partitions to your table - or drop this index (PK) and rebuild it after the load - should be quicker (of course if you don't need index during the load for other purposes).


Which SQL table statistics are used after they are updated when NORECOMPUTE is turned on?

According to this:
https://docs.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-2017#recompiling-execution-plans
ALTER TABLE and UPDATE STATISTICS commands trigger execution plan recompilation. So seems like the new statistics would be used by all queries executing after either of those procedures.


Slow insert performance in mysql innodb table for solr indexing

OBSERVATION #1
Larger transactions need a larger log buffer
Please expand innodb_log_buffer_size to 32M because as the MySQL Documentation says

The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB.
A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

OBSERVATION #2
You have innodb_thread_concurrency set to 32. It should be 0. It allows infinite concurrency. Even Percona Server has innodb_thread_concurrency set to 0 by default.
I have written about this in the past

Is the CPU performance relevant for a database server? (Apr 26, 2012)
Multi cores and MySQL Performance (Sep 20, 2011)
Possible to make MySQL use more than one core? (Sep 12, 2011)
About single threaded versus multithreaded databases performance (May 26, 2011)

OBSERVATION #3
Please Change the Following :

innodb_flush_method to O_DIRECT. This may alleviate the OS of caching responsibility
innodb_io_capacity to 5000 (You can set this to other value dynamically with SET GLOBAL innodb_io_capacity = 5000; or any other value)
innodb_change_buffering to inserts to help InnoDB focus on INSERTs


DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone


You should check the error log on both Principal and Mirror server.

One common error is that you run your Database Engine Service with a different accounts on your servers, in that case your need to grant connect permissions to the accounts on respective endpoints.

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\username4service];
GO


Organizing Relational Database Logical Model with Varying Categories

Something like this,

CREATE SCHEMA catalog;

CREATE TABLE catalog.item   ( item_id  serial PRIMARY KEY, item_name  text );
CREATE TABLE catalog.color  ( color_id serial PRIMARY KEY, color_name text );
CREATE TABLE catalog.size   ( size_id  serial PRIMARY KEY, size_name  text );
CREATE TABLE catalog.item_color (
  item_id int  REFERENCES item,
  color_id int REFERENCES color
);
CREATE TABLE catalog.item_size (
  item_id int  REFERENCES item,
  color_id int REFERENCES size
);

Now, we need orders

CREATE SCHEMA orders;

CREATE TABLE orders.shipment (
  shipment_id serial PRIMARY KEY,
  customer_name text,
  customer_address text
);

CREATE TABLE orders.item (
  item_id serial PRIMARY KEY
  item_id int    REFERENCES catalog.item,
  color_id int   REFERENCES catalog.color,
  size_id int    REFERENCES catalog.size,
);

CREATE TABLE orders.shipment_item (
  shipment_id int REFERENCES orders.shipment,
  item_id     int REFERENCES orders.item
);

to query the catalog,

SELECT item_name, color_name, size_name
FROM catalog.item
LEFT OUTER JOIN catalog.item_size USING (item_id)
LEFT OUTER JOIN catalog.item_color USING (item_id)

Then you just have a shipment schema which includes different order items.

Now for your categories, you can either create a category table that has  (1) Clothing (2) Footwear (3) Other. in the same style, or you can just add a tags column to catalog.item and tag them with clothing and footwear or whatever. Whatever you want to do..


What are the available options to backup DB2 Express-C?

Well, DB2 has a backup/restore system, and this allows to make full, incremental and delta backups. For more information about this, you can take a look at: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0005945.html

However, if you want to do a clone of the database, by just copying the data files, you could use the split mirror feature. This feature will stop all IO writes to the database, and you just have to copy the tablespaces and database files into other disk and voila.
For more information about spli mirroring: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/t0006422.html
This feature uses the "set write"command: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0009503.html

Other recent features in DB2 includes the possibility to merge backups, restore tables, and more characteristics that are part of the DB2 recovery expert: http://www-01.ibm.com/software/sw-library/en_US/detail/W269469I30015Y81.html


PostgreSQL How to DEFAULT Partitioned Identity Column?

I don't know of a better solution in general. A few minor things, though:

pg_get_serial_sequence()

If you don't know the name of the parent's implicit sequence, use pg_get_serial_sequence().

SELECT pg_get_serial_sequence('data.log', 'id');

You might even use the expression in the CREATE TABLE script directly, but that would impose a very minor additional cost to compute the actual name for the default (once per transaction, I think), and since this is about performance optimization ...

COPY overrides GENERATED ALWAYS, but not trigger

Defining your id column as GENERATED ALWAYS AS IDENTITY has the effect that you are never allowed to provide user values for the column id in INSERT statements, even when using the override clause like:

INSERT INTO data.log (epoch_millis, message) OVERRIDING USER VALUE
VALUES (1000000, 'hello');

It would have to be GENERATED BY DEFAULT for this to work, or omit id from the INSERT completely. The manual:

  OVERRIDING USER VALUE

  If this clause is specified, then any values supplied for identity
  columns defined as GENERATED BY DEFAULT are ignored and the default
  sequence-generated values are applied.

  This clause is useful for example when copying values between tables.
  Writing INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1 will
  copy from tbl1 all columns that are not identity columns in tbl2 while
  values for the identity columns in tbl2 will be generated by the
  sequences associated with tbl2.

COPY still overrides in any case. The manual:

  For identity columns, the COPY FROM command will always write the
  column values provided in the input data, like the INSERT option
  OVERRIDING SYSTEM VALUE.

But while writing to a partition directly, with your solution, INSERT also  overrides, so it will be your responsibility to avoid providing user values for the id column directly. An alternative would be to use a trigger instead of the default value in the partition:

CREATE OR REPLACE FUNCTION trg_log_default_id()
  RETURNS trigger AS
$func$
BEGIN
   NEW.id := nextval('data.log_id_seq')
   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER insbef_default_id
  BEFORE INSERT ON data.foo_log  -- the partition
  FOR EACH ROW
  EXECUTE PROCEDURE trg_log_default_id();

This assigns a number from the sequence in any case, more closely emulating the GENERATED ALWAYS behavior of the parent - stricter, even, also preventing COPY from violating your rule. The manual:

  COPY FROM will invoke any triggers and check constraints on the destination table.

But the trigger is a bit more expensive than a plain default value. And it would burn an extra serial number per row for regular inserts via the parent table. (It should be possible to distinguish cases in the trigger, didn't try now.)


shall I use multiple separete tables or one table with multiple partition on oracle

This depends on two things: the number of (expected) rows that you'll be dealing with, and the number of different fields between each ResponseType.

How many rows are you going to be dealing with?  And about how many of each ResponseType?  If you're talking about a solution that involves millions of rows, then partitioning the tables makes sense.  However, if you're only talking about hundreds or even thousands of rows then I wouldn't worry about it.

If the fields between each ResponseType aren't very different, the you probably won't need as many individual ResponseType tables.  However, if the data for each ResponseType is significantly different, then I can see the reasons behind creating (many?) multiple ResponseType tables, with one global Response table.  If it were me, I would try to create as few ResponseType tables as possible.


New table row autopopulated with data from other rows?

If you want the average for all horses:

insert into horse (id, name, stat1)
select nextid, 'name', avg(stat1)
from horse;

If you want the average for a subset of horses you can add a where clause:

insert into horse (id, name, stat1)
select nextid, 'name', avg(stat1)
from horse
where ... ;

If id is autogenerated:

insert into horse (name, stat1)
select 'name', avg(stat1)
from horse
where ... ;


This link may help you to back up the data


Sync data between 2 databases Oracle 10g

On the machine where you installed your database you should have SQL*Plus installed.

For your question. Create a database link from DB_NEW to DB_LIVE and use your Read Data user. Now you can query the data.


Oracle Data Pump imported table, some columns not selectable

Let me guess: when you describe the table, you see lowercase letters in column names, meaning the column name is case-sensitive, but you tried to select it without double quotes, in a case-insensitive manner.

Try quotes as in the below example:

SQL> create table t1(column1 number, "column2" number);

Table created.

SQL> insert into t1 values (1, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COLUMN1                                            NUMBER
 column2                                            NUMBER

SQL> select COLUMN1, column2 from t1;
select COLUMN1, column2 from t1
                *
ERROR at line 1:
ORA-00904: "COLUMN2": invalid identifier

SQL> select * from t1;

   COLUMN1    column2
---------- ----------
         1          1

SQL> select COLUMN1, "column2" from t1;

   COLUMN1    column2
---------- ----------
         1          1


What causes sql server to generate a new plan for a stored procedure?

For SQL 2016 here is the list why 'statement_recompile_cause' can happen.

Schema changed
Statistics changed
Deferred compile
Set option change
Temp table changed
Remote rowset changed
For browse permissions changed
Query notification environment changed
PartitionView changed
Cursor options changed
Option (recompile) requested
Parameterized plan flushed
Test plan linearization
Plan affecting database version changed
Query Store plan forcing policy changed
Query Store plan forcing failed
Query Store missing the plan

You can get the same list for any other versions that support Extended Event tracing.

 Select
  [name],
  map_key,
  map_value
FROM sys.dm_xe_map_values
WHERE [name] ='statement_recompile_cause'

Few good references:

A good article to read by Bhavesh Patel
Another Q&A in StackOverflow.
When does a Stored Procedure recompile?(Sql Server)


Yes (but..). You need to enable auditing, what is (sadly) enterprise feature.


When do I need to install Oracle database client?

You haven't installed any client because SQL Developer is a Java application, and it uses the Oracle JDBC driver by default, which is located under jbdc/lib inside the directory of SQL Developer and it is included in the zip file you downloaded. SQL Developer is able to use an Oracle client as well.

Yes, you need to have something for connecting to Oracle databases.
It can be a JDBC driver, a .NET provider, or the collection of libraries that is called the "client".

32 or 64 bit depends on the platform and application you use. For modern tools and application you typically go for 64 bit.


I think that this part is responsible for the error;

SELECT
    ( (SELECT PAYMENT_DATE FROM INVOICES WHERE INVOICE_ID = Inv_ID)
      - (SELECT INVOICE_DUE_DATE FROM INVOICES WHERE INVOICE_ID= Inv_ID)
    )
INTO Late_Payment
FROM Invoices;

If there is more than 1 row in Invoices, the result of the query will be many rows (with the same value) and we can only insert one value into the variable Late_Payment.

You can change the FROM Invoices to FROM dual (a special Oracle table with a single row):

SELECT
    ( (SELECT PAYMENT_DATE FROM INVOICES WHERE INVOICE_ID = Inv_ID)
      - (SELECT INVOICE_DUE_DATE FROM INVOICES WHERE INVOICE_ID= Inv_ID)
    )
INTO Late_Payment
FROM dual ;

or - since both subqueries use the same FROM and WHERE - simplify to:

SELECT PAYMENT_DATE - INVOICE_DUE_DATE
INTO Late_Payment
FROM INVOICES
WHERE INVOICE_ID = Inv_ID ;


Index on foreign key makes query extremely slow

The CBO will consider lots of different ways to execute your query, taking into account all the indexes and statistics on both tables and indexes.

If you disable an index, obviously the CBO then cuts out all the plans it might have considered that involved that index.

The question really boils down to: "why is the CBO choosing a poor plan", and the first thing to consider is whether your stats are accurately reflecting reality in your database. Is this the case? How do you gather stats, and how often on these tables and indexes?

If and only if the stats are good and the CBO is still choosing a bad plan, you'll need to start considering "why?", then you'll want to have a look at the plan, especially the estimated cardinalities for each step, and see which are badly off. In some cases this is because of correlations that standard stats cannot help with, but cross that bridge when you get to it :)


sys.dm_tran_version_store_space_usage reports always zero space used

Azure SQL Database uses Accelerated Database Recovery, and so RCSI doesn't use TempDb for the version store.  Instead the version store is inside the database to enable "Instantaneous transaction rollback", which is especially important during a failover:

The persisted version store is a database engine mechanism for persisting the row versions generated in the database itself instead of the traditional tempdb version store. PVS enables resource isolation and improves availability of readable secondaries.

Accelerated Database Recovery
So you should look in sys.dm_tran_persistent_version_store_stats, or see generally Manage accelerated database recovery


Does increasing the tempdb file size require a restart

Increasing the size does not require a restart.

A small example on my dev environment

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 4096MB )
GO
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 4096MB )
GO

From:

To:

If you are decreasing the size with MODIFY FILE it would require a restart.
If you want to decrease the size immediately, you would have to start shrinking (not ideal).


pgAdmin - "cannot open logfile!" (WINDOWS 10; Postgres 9.4)

First you need to identify the value of %APPDATA% on your WINDOWS system.
Change directory to %APPDATA%
Does the folder postgresql exist?  If not create the folder with this name.
Chand directory into postgresql
Does the folder pgAdmin exist?
if not create the folder with this name (note the capital "A").
In here copy the postgresql.conf file from your Postgres or pgAdmin installation folder
Create an empty pgAdmin3.log file in here
Done


I have one table for users, one for a collection of games. My users can own one or many games. How do I structure that in my database?

You first need to decide: can a game be owned by more than one person? (consider also if the answer might possibly be yes in the future).

If yes) Then you have a many-to-many relationship and as you described the only way to do this is to use an intermediary table with atleast these three columns:

ID
Game_ID (foreign key referencing the primary key in the Game table)
Owner_ID (foreign key referencing the primary key in the Owners table)

The naming convention for an intermediary table is normally a combination of the two tables you are connecting. So in your case it would probably be 'Game_Owner'.

If no) You have a one-to-many relationship and no intermediary table is required. In the Game table include a column called 'Owner_ID' which is a foreign key.


How to Concat Datetime without Losing Second Precision

You are losing seconds because the default format for converting datetime to string is the same as:

SELECT CONVERT(char(19), GETDATE(), 0);

Which outputs (for me, right now):

Oct 30 2018  2:02PM

You can try something more like this:

SELECT CONCAT
       (
         'ABC ',
         CONVERT(char(19), GETDATE(), 25)
       );

Which outputs (again, for me, right now):

ABC 2018-10-30 14:02:55

If second aren't enough, just change char(19) to char(23). If that's still not enough, you can consider changing that column to datetime2 to get more precision.


Your code is executed in a single DO statement and hence in a single transaction. So if any of the statements cause an error, all statements will be rolled back.

If you want to avoid that, you can use subtransactions by using an EXCEPTION clause, for example:

BEGIN
    EXECUTE format('DROP SEQUENCE %I.%I',
                   myschema.schema_name),
                   myseq.sequence_name);
EXCEPTION
    WHEN dependent_objects_still_exist THEN
        RAISE WARNING 'could not drop object %.% because of dependencies',
                      myschema.schema_name,
                      myseq.sequence_name;
END;

Then the failed statement is rolled back, but processing continues.

Another thing you could consider is using

DROP ... CASCADE

which will remove dependent objects automatically.


Understanding Sort Estimates with TOP(variable)

This would seem to be an issue with parameter sniffing (or lack thereof in this case).

The optimizer has to choose the best plan for the batch, in this case without knowing how many rows the user is going to need from the TOP operator. The key here is that in your case the @Maxrowstoreturn is set during the batch in which it is used, meaning the engine needs to create the entire plan before that variable is initialized or used. As for why the number 100 is used, it's likely an arbitrary decision by the developers as there is no way to provide a solid estimate in this situation anyway.

If we run the following code, this corrects the estimate, as SQL is able to use the input parameter to create a more accurate execution plan:

create table justint ( c1 int )
go

insert into justint
select top 1000000 row_number() over(order by t1.number) as N
from   master..spt_values t1
cross join master..spt_values t2
go

update statistics justint with fullscan;
go

create procedure #tmpproc

(@Maxrowstoreturn int)

 AS
begin

SELECT  top (@MaxRowsToReturn) c1
FROM justint WITH (NOLOCK)
--where  c1 >= 600000
ORDER BY c1 ASC
end
go

exec #tmpproc 10000

drop table justint


How to join two table and show one query result in MySQL?

It seems that your error it's cause en table2 the column you want to filter is anothernamename, not name, you can't put an alias on the WHERE. In any case, in your question, you don't seem to want to filter your results, so that WHERE is of no use at all. Your query should just be:

SELECT
FROM (  SELECT [name]
        FROM table1
        UNION
        SELECT anothernamename
        FROM table2) AS Data
WHERE ....
ORDER BY ....
LIMIT .....


MySQL Reusing calculation in SELECT clause in a VIEW

You can only do this in a view by repeating the expression.

CREATE VIEW upcoming_events_view AS
SELECT *,
       TIMESTAMPDIFF(WEEK, `create_date`, NOW()) AS week_diff,
       (TIMESTAMPDIFF(WEEK, `create_date`, NOW()) % week = 0) AS do_current_week
  FROM event_repeats

The NOW() function uses the time at the beginning of statement execution no matter how long the query takes to run, so you'd never get two different answers from NOW() in the same query.  The TIMESTAMPDIFF() function's second invocation will probably not be optimized away by the optimizer, but this is a trivial calculation.  It benchmarks at 0.5 microseconds on an AMD Opteron 1GHz processor, so a million calls to that function takes half a second.

Since your create_date is a DATE, not a DATETIME, the CURDATE() function might be a more technically accurate choice, since the TIMESTAMPDIFF() function first converts your dates to datetimes with an implicit 00:00:00 time element.

mysql> select TIMESTAMPDIFF(WEEK,NOW(),'2013-02-23'), TIMESTAMPDIFF(WEEK,CURDATE(),'2013-02-23');
+----------------------------------------+--------------------------------------------+
| TIMESTAMPDIFF(WEEK,NOW(),'2013-02-23') | TIMESTAMPDIFF(WEEK,CURDATE(),'2013-02-23') |
+----------------------------------------+--------------------------------------------+
|                                      0 |                                          1 |
+----------------------------------------+--------------------------------------------+


UNION excludes duplicate rows.
You want to use UNION ALL in this example:
 SELECT id, SUM(num) as sum
 FROM (
     SELECT 1 AS id, 2 AS num UNION ALL
     SELECT 1, 2 /*!*/) AS a
 GROUP BY id

Results:
id          sum
----------- -----------
1           4


What is the difference between Aurora Mysql 5.7.x and Aurora 2.x.x

They're differences in the minor/patch versions of Aurora.
If you spin up an instance of Aurora (MySQL)-5.7.12, then run:
SELECT @@aurora_version; -- Returns 2.02.5

It's essentially the first version of MySQL 5.7 compatible Aurora.
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Updates.html


MySQL 5.7 not accepting values of innodb_buffer_pool_instances greater than 1

Fixed it. I just reinstalled MySQL and now the variable updates just like it should. Don't know why it wasn't working, but I don't really care since it works now.


What is the proper way to relate many to many?

If the model is stable and there are always just these three references from shipment to personal_data, plain columns with FK constraints are the right and most efficient way to do it. Something like:

CREATE TABLE shipment (
  shipment_id serial PRIMARY KEY
, shipper_id  int NOT NULL REFERENCES personal_data
, receiver_id int NOT NULL REFERENCES personal_data
, notify_id   int NOT NULL REFERENCES personal_data
, ... -- more columns
);

NOT NULL may or may not fit your requirements.

Related:

How to implement a many-to-many relationship in PostgreSQL?

A junction table like you considered in closing could make sense for a varying number of references and/or references of dynamic nature (some types of references may be added / removed dynamically). More flexible, but takes up more storage and leads to more complicated queries.


Unsupported languages in Postgres Full text search

There are Postgres extensions to handle Chinese. Have you tried http://pgxn.org/dist/zhparser/? You can then maybe port it to Vietnamese.


Does a varchar column in be eliminated in a 3NF database?

If there are a lot of rows with customizations, I might keep the name column in the main table.

If on the other hand, only a small percentage of the rows in the main table ever contain a customized name column, I might consider making a related table to store the name column.

For instance:

IF OBJECT_ID(N'dbo.CustomName', N'U') IS NOT NULL
DROP TABLE dbo.CustomName;
IF OBJECT_ID(N'dbo.Main', N'U') IS NOT NULL
DROP TABLE dbo.Main;
CREATE TABLE dbo.Main
(
    MainID int NOT NULL IDENTITY(1,1)
        PRIMARY KEY CLUSTERED
    , LastName varchar(30) NOT NULL
    , FirstName varchar(30) NOT NULL
) ON [PRIMARY];

CREATE TABLE dbo.CustomName
(
    MainID int NOT NULL
        CONSTRAINT PK_CustomName
        PRIMARY KEY CLUSTERED
        CONSTRAINT FK_CustomName_MainID
        REFERENCES dbo.Main(MainID)
    , CustomName varchar(30) NOT NULL
) ON [PRIMARY];

GO

INSERT INTO dbo.Main (LastName, FirstName)
VALUES ('Vernon', 'Max');

INSERT INTO dbo.CustomName (MainID, CustomName)
VALUES (1, 'Maxwell Vernon, Esq.');

The output might look like:

SELECT m.MainID
    , [Name] = ISNULL(cn.CustomName, m.FirstName + ' ' + m.LastName)
FROM dbo.Main m
    LEFT JOIN dbo.CustomName cn ON m.MainID = cn.MainID

+--------+----------------------+
| MainID |         Name         |
+--------+----------------------+
|      1 | Maxwell Vernon, Esq. |
+--------+----------------------+

For a main table with only a small number of rows, this might not be worthwhile, however if the Main table is very large, and the number of customizations is relatively small, this technique may be something worth investigating.


Get transactions that failed or were never committed

No, SQL Server does not maintain any history about transactions that were aborted / rolled back that is trivial to get to and doesn't introduce additional potential problems (as outlined in @ooutwire's answer). Or even transactions that have been committed.

You will have to perform your own logging within your error handling, or capture specific transaction-related events using server-side trace or Extended Events.

Trace:

Extended events:


Left Outer Join doesn't give expected result. What am I missing?

Assuming "Table 1" is tbl1a, this should give you what you want:

SELECT DISTINCT a.fld
FROM tbl1 a
LEFT JOIN tbl2 b ON a.fld = b.fld
WHERE b.fld IS NULL


Window Functions: What is the purpose of Rows Unbounded Preceeding?

Semantic Differences Between Rows and Range

If we look at the results of this query, it's fairly easy to see when we might want to examine use range or rows.

SELECT   OwnerUserId,
         CAST(CreationDate AS DATE) AS DumbedDownDate,
         Score,
         SUM(Score) OVER
                   ( ORDER BY CAST(CreationDate AS DATE)) AS Not_Specified,
         SUM(Score) OVER
                   ( ORDER BY CAST(CreationDate AS DATE) RANGE UNBOUNDED PRECEDING ) AS Range_Specified,
         SUM(Score) OVER
                   ( ORDER BY CAST(CreationDate AS DATE) ROWS UNBOUNDED PRECEDING ) AS Rows_Specified
FROM     dbo.Posts
WHERE    OwnerUserId = 1
AND      CAST(CreationDate AS DATE) BETWEEN '2008-08-01' AND '2008-08-31'
ORDER BY DumbedDownDate;

When no boundary is set, the results are the same as if we specifically ask for a RANGE. Asking for ROWS gives us a different result all together.

The ROWS result is more like a running total, and the RANGE result is the total value for the... range of rows.

Performance

If you use rows, the window spool operator can use an in memory work table. Range uses an on disk work table, which is sometimes slower.

Compare these two queries:

SELECT   OwnerUserId,
         CreationDate,
         Score,
         SUM(Score)
             OVER ( ORDER BY CreationDate RANGE UNBOUNDED PRECEDING ) AS Range_Specified
FROM     dbo.Posts
WHERE    OwnerUserId <= 22656
AND      CreationDate >= '20080101'
AND      CreationDate < '20150101'
ORDER BY CreationDate;

SELECT   OwnerUserId,
         CreationDate,

         Score,
         SUM(Score)
             OVER ( ORDER BY CreationDate ROWS UNBOUNDED PRECEDING ) AS Rows_Specified
FROM     dbo.Posts
WHERE    OwnerUserId <= 22656
AND      CreationDate >= '20080101'
AND      CreationDate < '20150101'
ORDER BY CreationDate;

The query plans for them show quite different timing for the Window Spool:

If we look at the output of STATISTICS IO for both queries, they're also quite different:

Table 'Worktable'. Scan count 1363952, logical reads 11944334, physical reads 0,
read-ahead reads 3884, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 4722714, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

vs.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 3884, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 4723890, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Batch Mode

In SQL Server 2019, where Batch Mode is generally available for Row Store queries, or SQL Server 2016/2017 where there are various methods to introduce Batch Mode processing to Row Store queries, it's possible to get close to even performance thanks to the addition of the Batch Mode Window Aggregate.

See the three part series from Mr. Itzik Ben-Gan here:

Part 1
Part 2
Part 3


Database Size is Different after pg_restore (389 GB vs 229 GB)

Dump doesn’t account for dead tuples and only takes live tuples into account but dead tuples do account for space hence the space difference.

The reason is dump being a logical one it will only create statements to insert your data and the dead rows would anyways be invisible to it. If you have lots of updates and deletes happening or in other words your db is highly transactional, it will create more dead row versions and needs an aggressive vacuum to deal with bloating as well. If and when you compare the dead vs live row counts, before and after the restore you will see the difference.

Also just to be on a safer side do perform a manual vacuum analyse on the db post a dump restore, i have seen in the past that due to wrong estimation the planner changes the optimum plan to be used for the queries.


For the InnoDB/MySQL 5.6 table with 30M+ rows of FLOATs result is the next.

select SUM(x + y) from points;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 8,596 sec. */

select SUM(x + y) from points;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 8,799 sec. */

select SUM(x) + SUM(y) from points;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 2 queries: 8,720 sec. */

select SUM(x) + SUM(y) from points;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 2 queries: 8,580 sec. */

select SUM(x + y) from points;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 8,642 sec. */

In general the timings are of the same range so i suppose the difference is unsignificant.


MySQL Point in Time Recovery For Just a Single Schema?

You can extract the SQL from a specific schema with the
Use mysqlbinlog utility against all binary logs that have the data and time ranges. You will have to name the database you are extracting:

start-datetime
stop-datetime
database (-d)

For example, suppose you have the following scenario

You have these binary logs
mysql-bin.000019
mysql-bin.000020
mysql-bin.000021
mysql-bin.000022
Binary Logs span from 2013-01-30 09:30:00 to 2013-02-04 12:00:00
You have a full backup from 2013-02-03 00:00:00 (mysqldata-20130203.sql)
You want to restore MyProdDB from 02/03/13 at 2:45 PM 2013-02-03 14:45:00

#STEP 01) Get all DB Changes from the Binary Logs
DB=MyProdDB
BEG_DT="2013-02-03 00:00:00"
END_DT="2013-02-03 14:45:00"
BINLOGS="mysql-bin.000019 mysql-bin.000020 mysql-bin.000021 mysql-bin.000022"
mysqlbinlog -d ${DB} --start-datetime="${BEG_DT}" --stop-datetime="${END_DT}" ${BINLOGS} > mysqldata-delta.sql

#STEP 02) Load Data into Staging Database called MyProdDBStaging
mysql -u... -p... -DMyProdDBStaging < mysqldata-20130203.sql

#STEP 03) Load Delta into Staging Database called MyProdDBStaging
mysql -u... -p... -DMyProdDBStaging < mysqldata-delta.sql

Now, the target database will be the point and time instance for MyProdDB at 2:45 PM on Feb 3, 2013.
Give it a Try !!!


Single data type for imprecise date values, as allowed by ISO 8601

I have used CHAR and VARCHAR in the past, replacing the missing pieces with question marks or dashes. Question marks mean "not known", and dashes meant "not applicable". This proved to be intuitive enough for the users (secretaries and paralegals in complex litigation), flexible enough for the lawyers, and it sorted sensibly.

"1964------"
"1964-??-??"
"1964-05---"
"1964-05-??"
"1964-05-02"
"1964-06---"
"1964-06-??"

Wrap your declaration and CHECK constraints in CREATE DOMAIN or CREATE TYPE to make maintenance easier. CREATE DOMAIN doesn't require additional coding. CREATE TYPE requires support functions written in a low-level language.


How to call stored procedure inside trigger?

Instead of creating trigger you can use below code, It will also return you success or failure message.

DELIMITER ;
DROP FUNCTION IF EXISTS to_updatequantity;

DELIMITER //
CREATE FUNCTION to_updatequantity(id int, qty int) RETURNS CHAR(100) DETERMINISTIC
BEGIN

    SELECT prod_qty INTO @var_prod_qty FROM product_master WHERE prod_id = id;

    IF (@var_prod_qty > qty) THEN
        UPDATE product_master SET prod_qty = prod_qty - qty WHERE prod_id = id;
        SET @msg = 'prod_qty is updated successfully';
    ELSE
        SET @msg = 'prod_qty is less than or equal to ordered qty';
    END IF;

RETURN @msg;
END //
DELIMITER ;


You will have to use the mongodb aggregation framework.

Check this example: https://docs.mongodb.org/manual/tutorial/aggregation-zip-code-data-set/


Port 28017 is usually the HTTP Console interface, not the port you should be using to connect via the shell.  Change the port to 27017 instead and retry.


Using WHERE col IN with many data OR select all rows

You mentioned that the IN statement will include almost all possible values (90-95% based on your example numbers).  Joe W is correct that depending on your setup it could go either way and it's impossible to say for sure without testing which of those two would be faster.

However, you will eventually run in to a diminishing return on a query like that.  While one or the other will be faster than the other, neither will be fast. (by default MySQL will allow you up to max_allowed_packet for # of values, which is a lot).  A faster option would be to use where NOT IN and just list the 5-10% of values you didn't want.


Store extra, optional information about any entity

Suggestions

My first suggestion is that the business database is not the right place to store comments on what it contains.

Keep the comments in your issue tracker instead. A good issue tracker provides all the features to meet your requirements.

If you still want to do this in the database, research the history table pattern. See this Stack Overflow question for design ideas.

Essentially, you'd have an extra table for every table that you want to store comments about. Each table would refer to the business data by primary key, and would store the name, date, revision of the comment as well.

I've stated my assumptions below. Please correct me so we can help you better.

Assumptions

You have an issue tracker. It's regularly backed up and you can restore from backup.

You want to store instructive or descriptive text for some of the business data.

You would distinguish multiple comments by date of entry and by order of entry.

Most of the data would have no comments.

You want to be able to search the comments. Comment search queries run seldom compared to business queries.

You want to keep comments on deleted data.

You don't need to programatically cross-reference the business data and the comments or analyze them together. You just need to view and edit complete comments.

The comments do actually have value to the business (You are part of the business, right?).

Greater comment visibility would benefit the business.

Your business would react badly if the comments were lost.


Slow postgres query that uses quick sort instead of hash-aggregate

You can see the flaw here,

  Hash Cond: (((event_page_2016_8.person_alias)::text = (a.alias)::text) AND ((event_page_2016_8.session_id)::text = (s.session_id)::text))

It thinks that condition returns 2 rows. Instead it returns 11,861,365. If it returned 2 rows, a quick sort almost certainly would have been faster. The question is primarily why does it think that it's so far off there..

You may want to try to refresh the statistics (or even raising the statistics target for those tables) with ANALYZE event_page_2016_8 alias session, or see what could be returning so many rows to begin with.

Also this conditional doesn't make much sense to me

join alias as A
  on (O.person_alias = A.alias)
left outer join session as S
  on (O.session_id = S.session_id)
join alias as A1
  on ( A1.alias = S.person_alias and A.person_id = A1.person_id)

Maybe I'm missing something. So you're self-joining A to A1 on person_id but also S.person_alias which has to share a session_id.. Why not just

join alias as A
  on (O.person_alias = A.alias)
join session as S
  USING (session_id)

And leave out A1 entirely? You're not using it in the SELECT list?


Unable to open database after losing data file

You just need to restore or catalog the backups which were stored another location. Follow the following steps.

I assume that you have spfile, controlfile and redo log files in place.

As you said you have copied the RMAN backup in another location, restore that backup to your regular backup location.

Catalog the backup files.

RMAN> catalog start with '/u01/app/oracle/fast_recovery_area/backupset/';

Then use RMAN to restore database.

RMAN> restore database;

Recover the database with available archived logs.

RMAN> recover database;

Then simply try to open the database(use resetlogs clause if you performed incomplete recovery).

SQL> alter database open;

OR

 SQL> alter database open resetlogs;

Done!


Is "SQL Server Auditing" what I'm looking for?

Since you are using SQL Server 2016, you are having advantage of using temporal tables. It seems like you need to have combination of things for addressing your objective. As far as tracking changes in terms of insert/updates and deletes are concerned, this can be very much addressed by using temporal table. This will also serve your second question i.e. what was the value before. Current value is anyway stored in the subject table.

As far as your first question is concerned, I think you need to have auditing enabled on subject table to track who did mentioned operation i.e. insert/delete/update.

Lets understand temporal table a bit here:

  Temporal tables - not to be mistaken with temporary tables - were
  introduced as a new feature in SQL Server 2016. Temporal tables, also
  named system-versioned tables, allow SQL Server to automatically keep
  history of the data in the table.

A system-versioned table allows you to query updated and deleted data, while a normal table can only return the current data. For example, if you update a column value from 5 to 10, you can only retrieve the value 10 in a normal table. A temporal table also allows you to retrieve the old value 5. This is accomplished by keeping a history table. This history table stores the old data together with a start and end data to indicate when the record was active.

These are the most common use cases for temporal tables:

Audit --> With temporal tables you can find out what values a specific entity has had over its entire lifetime.
Slowly changing dimensions --> A system-versioned table exactly behaves like a dimension with type 2 changing behavior for all of its columns.
Repair record-level corruptions --> Think of it as a sort of back-up mechanism on a single table. Accidentally deleted a record? Retrieve it from the history table and insert it back into the main table.

You can read more about temporal tables at below links with examples:

Koen Verbeeck -  https://www.mssqltips.com/sqlservertip/3680/introduction-to-sql-server-temporal-tables/
Alex Grinberg - https://www.red-gate.com/simple-talk/sql/sql-training/sql-server-temporal-tables-recipes/
Prashanth Jayaram - https://www.sqlshack.com/temporal-tables-in-sql-server/

You can enable auditing at database level to capture insert/update and delete operation. This would capture who performed the mentioned operation with some other basic details like at what time this was done.

There are few disadvantage to this auditing:

SQL Server Audit uses the resources of the audited SQL Server itself, which can degrade performance
It is difficult to comprehensively manage multiple instances and consolidate the audit data.
There is a lot of wet-work involved in managing, analyzing and archiving audit data, whether in a file or log, and necessitates manual effort for importing, archiving and reporting.
This feature isn’t available in the standard version of SQL Server until SQL Server version 2016.

In your case, if you combine both these concepts, this should serve your purpose.

Hope above helps.


Reclaim disk space from large database's primary filegroup after truncating tables (other than shrink)

Community wiki answer:

It's going to take some time for the shrink operations on such large database files to run. Honestly, you should consider running the DBCC command and just letting it run as long as you can, on days and at times you can afford. Just let it do it's thing.

If the shrink operation is stopped before completion it does not roll back the changes so even if it runs for 2 hours on one day, another day for 4 hours, and so forth until a weekend or something where you can let it run a whole day until it's able to complete quickly.

Run scripts to check DB free space before and after each run too.

This way no exporting to new DB structure or whatever, and you can just let it do it's own thing. Do some more research, but in similar-sounding cases, I've been involved in the past (huge data warehouses where data loaders did things all wrong - using the actual DB for temp tables rather than tempdb), it happened and was what it was. I got the issues tracked down, retrained data loaders, and then did the cleanup. Afterwards I worked on taking care of fragmentation, etc. where space was higher priority than performance.

Because of the issues mentioned, we have always shrunk files in smaller chunks. In my experience, the shrinks are excruciatingly slow initially, i guess because SQL Server has to move the pages from the end of the file before shrinking. But as you progress it becomes faster and faster.


Fetch just inserted data in master/slave model?

This is often handled by the application actively choosing to read from the master based on context.

A common example I've seen mentioned is a database of blog posts.  *Viewing, a blog post would obviously be done from a replica, but previewing an edit to a blog post -- which may be the same code, since it's rendering the post -- might be something you do by reading from the master.

Depending on the application, you might also read from the replica and then fall back to reading from the master if you don't find what you expect.

There are also solutions, like Maxscale -- designed by MariaDB but works with MySQL too -- that are essentially a proxy between your app and both MySQL servers.  Whenever you are in a transaction, you're talking to the master, whenever you're not in a transaction and issue a SELECT, the queries go to the replica... so you can easily force a read from the master just by doing it in a transaction.  There are other rules in play as well that send certain statements to both servers, such as USE and SET statements.  If the replica lag goes beyond a certain threshold, Maxscale sends everything to the master.


What is a suitable index for this query?

Using SQL Server 2012 (hopefully there is an equivalent in MySQL):

CREATE TABLE dbo.site_map_template
(
  row_mod datetime NULL,
  row_create datetime NULL,
  template_name varchar(50) COLLATE Latin1_General_BIN2 NULL,
  alias_to varchar(100) COLLATE Latin1_General_BIN2 NULL,
  package varchar(50) COLLATE Latin1_General_BIN2 NULL,
  fake_uri varchar(255) COLLATE Latin1_General_BIN2 NULL,
  precedence integer NULL,
  parameters1 varchar(255) COLLATE Latin1_General_BIN2 NULL,
  parameters2 varchar(255) COLLATE Latin1_General_BIN2 NULL,
  parameters3 varchar(255) COLLATE Latin1_General_BIN2 NULL
);

A good clustered index for this query is:

CREATE UNIQUE CLUSTERED INDEX PK__site_map_template_precedence_fake_uri
ON dbo.site_map_template (precedence, fake_uri DESC);

Or, a nonclustered index:

CREATE NONCLUSTERED INDEX nc1
ON dbo.site_map_template
    (
    precedence,
    fake_uri DESC
    )
INCLUDE
    (
    alias_to,
    parameters1,
    parameters2,
    parameters3,
    template_name
    );

The query (again, in SQL Server syntax) is:

SELECT
    alias_to,
    fake_uri,
    [parameters] = CONCAT(parameters1, parameters2, parameters3),
    precedence,
    template_name
FROM dbo.site_map_template
ORDER BY
    precedence,
    fake_uri DESC;

And the execution plan is a simple scan of the index:

Or, using the nonclustered index:

The important thing in both cases is that each index covers all the columns needed by the query, and the index keys are precedence ASC and fake_uri DESC. Without the descending second key, a full sort would be required, and this is likely the cause of your slow performance.


Database Mirroring - Mixed SQL Server 2012 to SQL Server 2016

The short answer is that yes, you can Mirror up to a higher version, but you should really only do this if you're going to upgrade.

Once you fail over, you can't fail back down to a lower version.


Display Monthly Attendance Report in MySql

This type of rotation of data from columns to rows is known as a PIVOT. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression to get the result.

My first suggestion would be to determine if you have a calendar table or a table that contains all of the dates that you want to display.  If not, then I would suggest creating one similar to the following:

CREATE TABLE calendar (`Date` datetime) ;

INSERT INTO calendar (`Date`)
VALUES
    ('2013-06-01 00:00:00'),
    ('2013-06-02 00:00:00'),
    ('2013-06-03 00:00:00'),
    ('2013-06-04 00:00:00'),
    ('2013-06-05 00:00:00'),
    ('2013-06-06 00:00:00'),
    ('2013-06-07 00:00:00'),
    ('2013-06-08 00:00:00'),
    ('2013-06-09 00:00:00'),
    ('2013-06-10 00:00:00');

This will make it possible for you to generate a list of all of the dates that you want to display.

Second, you will need to generate the list of each student and each date.  You can do this by using a CROSS JOIN between your tbl_admission and the calendar table:

select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a;

See Demo.  Once you have this list, then you can use a LEFT JOIN to your existing tbl_absentees table to get the result:

select
  ca.studentname,
  ca.rollno,
  ca.class,
  max(case when ca.date = '2013-06-01' then coalesce(p.status, 'P') end) `2013-06-01`,
  max(case when ca.date = '2013-06-02' then coalesce(p.status, 'P') end) `2013-06-02`,
  max(case when ca.date = '2013-06-03' then coalesce(p.status, 'P') end) `2013-06-03`,
  max(case when ca.date = '2013-06-04' then coalesce(p.status, 'P') end) `2013-06-04`,
  max(case when ca.date = '2013-06-05' then coalesce(p.status, 'P') end) `2013-06-05`,
  max(case when ca.date = '2013-06-06' then coalesce(p.status, 'P') end) `2013-06-06`,
  max(case when ca.date = '2013-06-07' then coalesce(p.status, 'P') end) `2013-06-07`,
  max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-08`,
  max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-09`,
  max(case when ca.date = '2013-06-10' then coalesce(p.status, 'P') end) `2013-06-10`
from
(
  select c.date, a.studentname, a.rollno, a.class
  from calendar c
  cross join tbl_admission a
) ca
left join tbl_absentees p
  on ca.rollno = p.rollno
  and ca.date = p.date
group by ca.studentname, ca.rollno, ca.class
order by ca.rollno;

See SQL Fiddle with Demo.  Of course for your request, you most likely want to query the data based on a date range so you will not want to hard-code the values.  If that is the case, then you will need to look at using a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN ca.date = ''',
      date_format(date, '%Y-%m-%d'),
      ''' THEN coalesce(p.status, ''P'') END) AS `',
      date_format(date, '%Y-%m-%d'), '`'
    )
  ) INTO @sql
FROM calendar
where date>='2013-06-01'
  and date <= '2013-06-05';

SET @sql
  = CONCAT('SELECT ca.studentname,
              ca.rollno,
              ca.class, ', @sql, '
            from
            (
              select c.date, a.studentname, a.rollno, a.class
              from calendar c
              cross join tbl_admission a
            ) ca
            left join tbl_absentees p
              on ca.rollno = p.rollno
              and ca.date = p.date
            where ca.date>=''2013-06-01''
              and ca.date <= ''2013-06-05''
            group by ca.studentname, ca.rollno, ca.class
            order by ca.rollno');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo. Both of these queries will give a result similar to:

| STUDENTNAME | ROLLNO | CLASS | 2013-06-01 | 2013-06-02 | 2013-06-03 | 2013-06-04 | 2013-06-05 | 2013-06-06 | 2013-06-07 | 2013-06-08 | 2013-06-09 | 2013-06-10 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|       Naren |      1 |    22 |          A |          A |          A |          A |          P |          P |          P |          P |          P |          P |
|       Srinu |      2 |    22 |          P |          P |          P |          P |          P |          P |          P |          P |          P |          P |
|        Blah |      3 |    22 |          A |          P |          P |          P |          P |          P |          P |          P |          P |          P |


Why is it necessary to run both mongod and mongo.exe in windows? What's happening behind the scene?

As @Stennie explained it's exactly the other way around .. mongod is the daemon background process running the database server and mongo.exe is the interactive shell.

As such - both are running if you want to use the database from the command prompt / a console [CMD] window.


Is it good practice to rebuild index on FileTable?

(converting my comment to answer)

FileTable uses Filestream technology. From Paul Randal's blog - Defrag the NTFS volume if needed before setting up FILESTREAM, and periodically to maintain good scan performance.

Instead of rebuilding, you should defrag the ntfs volume as suggested.

Also, highly suggest to read - Best Practices on FILESTREAM implementations - by Pedro Lopes (Senior PM at Microsoft Database Systems)


Availability Groups automatic failover is not so automatic

You have a quorum issue in this setup.  There are only two participants in the cluster:

SQL1
SQL2

If SQL1 goes down, there's no way for SQL2 to know that it should be the primary.  For all SQL2 knows, the network has gone down and SQL1 is still operating as the primary.  Thus, SQL2 goes to the RESOLVING state (it's not sure what to do).  This is to prevent a "split brain" scenario.
For a two-node cluster, generally you need to add a File Share Witness as a resource in the WSFC cluster.  This will allow the remaining instance, SQL2, to establish quorum with the FSW, and take over as the primary.
Additionally, the behavior of AGs is not to automatically fail back to the "original primary" as soon as it's back up.  Automatic failover is only in the case of an outage on the current primary, it's not intended to "automatically fail back."

Note that the other scenario you described, stopping the primary cluster node, is not how you want your AGs to failover.  Don't do that:

Do not use the Failover Cluster Manager to manipulate availability groups, for example:

Do not add or remove resources in the clustered service (resource group) for the availability group.

Do not change any availability group properties, such as the possible owners and preferred owners. These properties are set automatically by the availability group.

Do not use the Failover Cluster Manager to move availability groups to different nodes or to fail over availability groups. The Failover Cluster Manager is not aware of the synchronization status of the availability replicas, and doing so can lead to extended downtime. You must use Transact-SQL or SQL Server Management Studio.


Database Backup from 2008 R2, Restore on 2012 Failover Cluster Instance

Does this failover cluster impose any restrictions on this backup restore or will it be a normal backup restore operation.

It will be a normal backup and restore operation (provided there are no other issues with the actual backup and/or restore).

The fact that you are going from a standalone instance to a failover cluster instance will not negatively affect an otherwise normal backup and restore.

The same rules will apply here, of course, as well as any other backup/restore operation regardless of standalone or FCI.


Rather than subqueries, use conditional aggregation:
SELECT
      DateArret
    , SUM(CASE EnumArret WHEN 'HI' THEN DATEDIFF(minute,HeureDebut, HeureFin) ELSE 0 END) AS HI
    , SUM(CASE EnumArret WHEN 'PS' THEN DATEDIFF(minute,HeureDebut, HeureFin) ELSE 0 END) AS PS
FROM dbo.ArretProductionJournee
WHERE EnumArret IN('HI', 'PS')
GROUP BY
    DateArret
ORDER BY
    DateArret;

Below is more complete sample data and schema to match your desired result.
CREATE TABLE dbo.ArretProductionJournee(
     ID int NOT NULL IDENTITY CONSTRAINT PK_ArretProductionJournee PRIMARY KEY
    ,DateArret date NOT NULL
    ,HeureDebut datetime2(3) NOT NULL
    ,HeureFin datetime2(3) NOT NULL
    ,EnumArret char(2) NOT NULL
);
INSERT INTO dbo.ArretProductionJournee(DateArret, HeureDebut, HeureFin, EnumArret)
VALUES
     ('2020-10-30', '2020-10-30 15:00:00.000', '2020-10-30 15:05:00.000', 'HI')
    ,('2020-10-30', '2020-10-30 15:00:00.000', '2020-10-30 15:07:00.000', 'HI')
    ,('2020-11-30', '2020-11-30 14:00:00.000', '2020-11-30 15:00:00.000', 'PS')
    ,('2020-11-30', '2020-11-30 16:00:00.000', '2020-11-30 17:00:00.000', 'HI')
    ,('2020-11-30', '2020-11-30 16:00:00.000', '2020-11-30 17:00:00.000', 'XX');


Which is the best approach to "suspend" the activity of a whole AOG cluster thinking on start it again later?

You don't need to suspend data movement, but otherwise what you've written for option #1 is a good approach!  Before doing anything, I would disable automatic failovers on all replicas, to prevent any unexpected failovers when you bring the AG back up.
Then, as you said, shut down the secondaries first, then shut down the primary.
When you bring the primary back up, it should come online as the primary again.  Then bring up the secondaries.
Make sure to turn automatic failovers back on once everything is stable, if you had it on in the first place.


Invalid column name 'database_id' in sp_blitzfirst

First, thanks for using our scripts.

Second, sorry you ran into this problem.

For support, the best place to file issues is here. Just remember to search for issues first before filing.

There's a fix for this in this commit. You can grab the version from there to use until this month's release drops.

Thanks!


InnoDB in Percona Server 5.0.91 and MySQL 5.5.x has three options

innodb_read_io_threads
innodb_write_io_threads
innodb_io_capacity

These options were introduced in MySQL 5.1.38 InnoDB Plugin

Tuning these will engage more CPUs within InnoDB/XtraDB

I have discussed this in past posts in the DBA StackExchange along with other InnoDB tuning suggestions

Multi cores and MySQL Performance
About single threaded versus multithreaded databases performance
Possible to make MySQL use more than one core?
Query runs a long time in some newer MySQL versions

Click here for the XtraDB features that the InnoDB Plugin does not have.


Postgresql - Internal Array handling - array type or pointer type

Arrays are defined in src/includes/utils/array.h and src/backend/utils/adt/arrayfuncs.c and the other array* files in src/backend/utils/adt/.

An array is a VARLENA (see src/backend/utils/adt/varlena.c) which is a palloc()'d type, a scoped-storage wrapper around malloc(). Arrays are block-allocated data, not linked lists.


SQL Server How to Set up Maintenance plan to shrink log

When you switch the recovery mode to shrink the log file you are invalidating prior backups because it breaks the log chain.  What you need to do it switch it to simple, shrink it down to what you want, then switch it back and set up regular log backups (I would recommend hourly) in addition to full backups.  When a log backup is performed the log is truncated (note, not shrunk.  it will remain the same size on disk) and then the database will be free to re-use the space at the beginning of the file instead of appending to the end of the log file and causing file growth.


The sorting can be taken care of with a CASE expression, something along the lines of:

ORDER BY
    CASE WHEN @SortDirection = 'A' THEN
        CASE
           WHEN @SortBy = 'JobNumber' THEN JobNumber
           WHEN @SortBy = 'JobId' THEN JobId
        END
    END ASC
    , CASE WHEN @SortDirection = 'D' THEN
        CASE
           WHEN @SortBy = 'JobNumber' THEN JobNumber
           WHEN @SortBy = 'JobId' THEN JobId
        END
    END DESC

You may want to reconsider the OR'd where conditions as they are likely to generate poor plans. One of the best articles I've read covering this (and the alternative approaches) is Dynamic Search Conditions in T-SQL

Edit: Looking again at your parameter list, the primary filters appear to be @CustomerId and @UserId. I'd suggest creating two procs, spJobs_SelectByCustomerId and spJobs_SelectByUserId, which filter by their respective parameters so you eliminate the '@Param = 0 or Column = @Param' conditions. I guess the next important param is @ShowCompleted (presuming that once a job is 'done', it isn't shown unless @ShowCompleted=1), which i'd consider including in indexes on the CustomerId and UserId.

Edit2: Funny how these questions sometimes tick over in the back of your mind! :) On indexing @ShowCompleted, this is one of the occasions where Using a Low-Selectivity BIT Column First Can Be the Best Strategy. Filtered indexes should also be considered.


Two key attributes in an EER, what does it mean?

This only tells that an Employee has two attributes which are bith a combined primary key

he has a name and
a employer number

In a Table structure , it will be displayer as two columns for the Table Employee and the would have a PRIMARY KEY(Emp_nr,Name)
Employee
-------
Emp_nr

Name


The "VIRT" size (3.2 GB in this case) is the total amount of memory the process can see. The reason all of the postgres processes have a similar VIRT count is because they all map the 3 GB of shared memory into themselves, and in addition to that allocate whatever more they need to operate.

The "RES" size is the amount of memory touched by the individual processes. So, the checkpointer process has touched (directly read or written) about 1.5 GB of memory, most of which is almost certainly from the shared buffers pool. So, this 1.5 GB of RES is a mostly a subset of the 3 GB allocated for the shared buffers, plus a smaller amount for active per-process work.

To see which is which, use a utility such as pmap.


What parameters should be modified after upgrading RAM memory in Oracle server

In 9i, multiple parameters are used for sizing components of the SGA. Below is a typical (but not complete) list:
DB_CACHE_SIZE
JAVA_POOL_SIZE
LARGE_POOL_SIZE
SHARED_POOL_SIZE
And SGA_MAX_SIZE, if set.
Also I assume for simplicity that you use automatic PGA management, so for PGA:
PGA_AGGREGATE_TARGET


There are a few ways that you can perform this data transformation. You have access to the PIVOT function then that will be the easiest, but if not then you can use an aggregate function and a CASE.

Aggregate /Case version:

select personid,
  max(case when optionid = 'A' then 1 else 0 end) OptionA,
  max(case when optionid = 'B' then 1 else 0 end) OptionB,
  max(case when optionid = 'C' then 1 else 0 end) OptionC
from PersonOptions
group by personid
order by personid;

See SQL Fiddle with Demo

Static Pivot:

select *
from
(
  select personid, optionid
  from PersonOptions
) src
pivot
(
  count(optionid)
  for optionid in ('A' as OptionA, 'B' OptionB, 'C' OptionC)
) piv
order by personid

See SQL Fiddle with Demo

Dynamic Version:

The two versions above work great if you have a known number of values, but if your values are unknown, then you will want to implement dynamic sql and in Oracle you can use a procedure:

CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select personid ';

    begin
        for x in (select distinct OptionID from PersonOptions order by 1)
        loop
            sql_query := sql_query ||
                ' , min(case when OptionID = '''||x.OptionID||''' then 1 else null end) as Option_'||x.OptionID;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from PersonOptions group by personid order by personid';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

Then you return the results, you will use:

variable x refcursor
exec dynamic_pivot_po(:x)
print x

The results are the same with all versions:

| PERSONID | OPTIONA | OPTIONB | OPTIONC |
------------------------------------------
|        1 |       1 |       1 |       0 |
|        2 |       0 |       0 |       1 |
|        3 |       0 |       1 |       0 |
|        4 |       1 |       0 |       1 |


You cannot do that.

As the documentation says:

  CREATE TRIGGER creates a new trigger. The trigger will be associated
  with the specified table or view

This clearly excludes types and domains.  (Very logically: triggers are normally fired upon changing something - here you want to change a column, not the type.)

You should instead use a stored procedure to take care of your password column(s).  And personally I think the less places password are stored at, the better.


What is the best way to reduce the size of ibdata in mysql?

Keep in mind the busiest file in the InnoDB infrastructure is /var/lib/mysql/ibdata1

This file normally houses many classes of information (when innodb_file_per_table is 0)

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data

Rollbacks Segments
Undo Tablespace

Table Metadata
See Pictorial Representation

Many people create multiple ibdata files hoping for better diskspace management and performance. It does not help.

Unfortunately, OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things:

Makes the table's data and indexes contiguous inside ibdata1
It makes ibdata1 grow because the contiguous data is appended to ibdata1

You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table.
To shrink ibdata1 once and for all you must do the following

Step 01) MySQLDump all databases into a SQL text file (call it SQLData.sql) (More details here)

Step 02) Drop all databases (except mysql, performance_schema, and information_schema)

Step 03) Shutdown mysql

Step 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

Step 05) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

Step 06) Restart mysql

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

Step 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many times in my career as a MySQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!

If you would like to see how much actual data is stored in MyISAM and InnoDB, please run this query:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;


postgres; do indexes automatically reindex new data?

New data gets indexed automatically. Do note however that the presence of an index will therefore slow down inserts and updates (and deletes too of course). For tables with an extremely high volume of transactions, you have to be very careful about adding indexes. For most tables in most systems this is not an issue.

reindex is rarely needed under normal circumstances. Of these rare cases, the most common is probably "bloat" caused by lots of mutations -- usually when those mutations cause data on the disk to be removed from the "middle" and added to the "end".


How does InnoDB represent composite integer keys?

InnoDB uses B+Tree to store indexes and tables (the entire table is stored inside its primary key) and it stores the multiple columns as tuples (ordered lists) so actually there is somewhere an equivalent to a tree node having two properties - server and ticket both separate numbers.

The entire tree is then a way to store an ordered list of such pairs/tuples - they are ordered first by the first column (server) and in case of a tie the second columns (ticket) are compared and decide the ordering. For the primary key there should be no ties on both columns because it is unique, but for non-unique composite indexes there might be. But in InnoDB each secondary index has the primary key columns added to it (because the primary key is the pointer to actual data row) so any ties in there can be solved by ordering on the added primary key columns.

If you want to imagine it, then take a phonebook - each row consists of three values (lastName, firstName, phoneNumber) and is sorted in that order - the ordering might be effectively equivalent to lastName & " " & firstName but it is really done piecewise.


SQL Server Web Service in a web environment architecture

Typically, you'd have a web service hosted in IIS or some other http daemon in the internal network, with a firewall hole opened up for the specified port and machine where the http web service is hosted.

The web service, being inside, would have access to the SQL Server as required.

This maintains protection for the SQL Server since it is not directly accessible from the dmz/website.

FYI, SOAP/xml endpoints in SQL Server were deprecated as of SQL Server 2012.


Oracle 10.0 package shows INVALID status in reference on oracle 11g on import with IMPDP

Most of the times some object privileges are missing from the owner[s] of the imported objects.
the error PLS-00302: component 'XXX' must be declared
can be explained by missing synonyms and/or missing privileges on that object. Verify in the source database that the owner - or sometimes public - had privileges on the object 'XXX' and make sure this is granted in the target database too.
impdp does not give grants on objects that it did not import in the same session. (the not created by me syndrome) Most likely sys objects are playing a role here.


mariadb (mysql) innodb not optimizing SELECT for partitions?

PARTITIONing with fewer than 10 partitions is rarely useful.

Perhaps PARTITION BY RANGE(TO_DAYS(date)) would be better?  Perhaps daily partitions is best for your application.

Is your goal to purge only "level 7 AND old"?  The gets rather messy because  you would be purging only part of a partition, not being able to DROP PARTITION.

On the other hand, maybe this is a valid application for subpartitioning??  That would be exciting (to me) since I have not yet seen a use for subpartitioning.  Beware of the limitations of what you can put in the BY clause.

I think you would have to go with

 PARTITION BY RANGE(date)
     SUBPARTITION BY KEY(level)

And use ALTER TABLE ... TRUNCATE PARTITION ...  This would leave an empty partition behind, but it would be quick.  Check the ramifications of innodb_file_per_table=1.


Matching single column against multiple values without self-joining table in MySQL

I have found a clever way to do this query without a self join.

I ran these commands in MySQL 5.5.8 for Windows and got the following results:

use test
DROP TABLE IF EXISTS answers;
CREATE TABLE answers (user_id VARCHAR(10),question_id INT,answer_value VARCHAR(20));
INSERT INTO answers VALUES
('Sally',1,'Pouch'),
('Sally',2,'Peach'),
('John',1,'Pooch'),
('John',2,'Duke');
INSERT INTO answers VALUES
('Sally',1,'Pooch'),
('Sally',2,'Peach'),
('John',1,'Pooch'),
('John',2,'Duck');

SELECT user_id,question_id,GROUP_CONCAT(DISTINCT answer_value) given_answers
FROM answers GROUP BY user_id,question_id;

+---------+-------------+---------------+
| user_id | question_id | given_answers |
+---------+-------------+---------------+
| John    |           1 | Pooch         |
| John    |           2 | Duke,Duck     |
| Sally   |           1 | Pouch,Pooch   |
| Sally   |           2 | Peach         |
+---------+-------------+---------------+

This display reveals that John gave two different answers to question 2 and Sally gave two different answers to question 1.

To catch which questions were answered differently by all users, just place the above query in a subquery and check for a comma in the list of given answers to get the count of distinct answers as follows:

SELECT user_id,question_id,given_answers,
(LENGTH(given_answers) - LENGTH(REPLACE(given_answers,',','')))+1 multianswer_count
FROM (SELECT user_id,question_id,GROUP_CONCAT(DISTINCT answer_value) given_answers
FROM answers GROUP BY user_id,question_id) A;

I got this:

+---------+-------------+---------------+-------------------+
| user_id | question_id | given_answers | multianswer_count |
+---------+-------------+---------------+-------------------+
| John    |           1 | Pooch         |                 1 |
| John    |           2 | Duke,Duck     |                 2 |
| Sally   |           1 | Pouch,Pooch   |                 2 |
| Sally   |           2 | Peach         |                 1 |
+---------+-------------+---------------+-------------------+

Now just filter out rows where multianswer_count = 1 using another subquery:

SELECT * FROM (SELECT user_id,question_id,given_answers,
(LENGTH(given_answers) - LENGTH(REPLACE(given_answers,',','')))+1 multianswer_count
FROM (SELECT user_id,question_id,GROUP_CONCAT(DISTINCT answer_value) given_answers
FROM answers GROUP BY user_id,question_id) A) AA WHERE multianswer_count > 1;

This is what I got:

+---------+-------------+---------------+-------------------+
| user_id | question_id | given_answers | multianswer_count |
+---------+-------------+---------------+-------------------+
| John    |           2 | Duke,Duck     |                 2 |
| Sally   |           1 | Pouch,Pooch   |                 2 |
+---------+-------------+---------------+-------------------+

Essentially, I performed three table scans: 1 on the main table, 2 on the small subqueries. NO JOINS !!!

Give it a Try !!!


Since correlated subqueries are not allowed, try using derived tables and then joining them.

select
    a.id,
    a.sum_all,
    sum(b.sum_all) as csum
from
        ( select col1 as id,
                 sum(col2) as sum_all
          from t
          group by col1
        )  a
    join
        ( select col1 as id,
                 sum(col2) as sum_all
          from t
          group by col1
        )  b
     on
        ( b.sum_all < a.sum_all )
     or ( b.sum_all = a.sum_all and b.id <= a.id )
group by
    a.sum_all, a.id
order by
    a.sum_all, a.id ;

This is essentially a self-join on the derived group-by table. It might be more efficient to first save the group-by results into a temporary table and then do the self-join.

According to the manual, Hive also has windowing aggregates, so you could use them as well:

select
    a.id,
    a.sum_all,
    sum(a.sum_all) over (order by a.sum_all, a.id
                         rows between unbounded preceding
                                  and current row)
        as csum
from
        ( select col1 as id,
                 sum(col2) as sum_all
          from t
          group by col1
        )  a
order by
    sum_all, id ;

or with:

select
    col1 as id,
    sum(col2) as sum_all,
    sum(sum(col2)) over (order by sum(col2), col1
                         rows between unbounded preceding
                                  and current row)
        as csum
from
    t
group by
    col1
order by
    sum_all, id ;


Normalization of library book table with multiple authors

The only sensible design I can think of - since books can have 20 authors - is to always use a junction table and don't bother storing the AuthorID in the book table some of the time (and don't even think about storing a comma-separated list of AuthorIDs, please, or adding Author2, Author3, ... columns). This will just make queries complex.

BookAuthors(BookID (FK points to Book), AuthorID (FK points to Author))

In this case, you can remove the AuthID column from the Book table. Don't bother abbreviating to AuthID - you don't really think that makes you more productive or makes the code easier to read, do you?

Also, as a general rule, don't spend time trying to worry about how you will eliminate NULLs everywhere.


postgreSQL - Checking if one date interval does not overlap a date interval in array of intervals for each row

Why don’t you use second table “rooms_reservations” with period of data reservation? It’s more easy and faster with big data.


Postgresql 9.6, create a user with only select permissions for a given database/schema

Please read below mentioned official documentation from Postgres
Official Documentation of REVOKE:
https://www.postgresql.org/docs/9.6/sql-revoke.html
Official Documentation of GRANT:
https://www.postgresql.org/docs/9.6/sql-grant.html
To grant select privilege to schema you need to run following query
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO user_name;

For more details read following Post
https://dba.stackexchange.com/questions/91953/grant-access-to-all-tables-of-a-database


Forced Parameterization and CASE statements

So I should have done some more digging before I posted this. The restriction is explained deep down on this page:
Forced Parameterization | Microsoft Docs

Specifically, this section:

  The <select_list> of any SELECT statement. This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.

This tells me it's not specifically the CASE, but that I have included literals in the SELECT list.

Still, if anyone has any suggestions for working around this, I'd be interested.