At What Point Does Having an Index Become Efficient

The exact limit is really hard to determine ahead of time.

One thing most people underestimate is the high requirements that an index must fulfill, before it becomes a candidate to be used in a query.

An efficient (nonclustered) index

offers great selectivity, e.g. returns only a very small percentage (< 1%, < 2%) of the total rows. If the selectivity is not a given - SQL Server's query optimizer will most likely ignore this index
should ideally cover the query, i.e. return all teh columns required by the query. If you can create an index that has 1 or 2 index columns, and includes another handful (2-4) columns as included columns and thus you can cover a query - then chances are the query optimizer will use this index. Which also means: if your code is always using SELECT * ..... to fetch all columns, the likelihood of indices being used goes down - quite dramatically, actually

I'm sure there are a ton of other criteria as well - but I would believe these two are the most critical ones. Of course, you should always keep your indices properly maintained (reorganize, rebuild) and make sure the statistics associated with your indices are up to date.

PS: nonclustered indices on foreign key columns are a special case; by default, I would always recommend adding those, since they help speed up both referential integrity checks, as well as JOIN's on those FK constraints. But even here, it's absolutely valid to "extend" those FK column indices by adding some additional "include" columns to make them even more useful.


DATEis the Oracle datetime data type for storing a datetime value with a precision of one second.  The function TO_DATE converts a character type (VARCHAR2,CHAR,...)  to the DATE type. The first string is the string to convert, the secons string tell the convert function which format is used to code the date in the first string. So the result of  TO_DATE('2014-04-18 10:33', 'YYYY-MM-DD HH24:MI') is the DATE  equivalent to '2014-04-18 10:33:00'. The seconds are set to 0.

The function SYSDATE returns a DATE value  so it must not be converted further. The column  test_time is of data type DATE, too, it also must not be converted further. To get the DATEdifference you have only to calculate test_time-SYSDATE This difference of two DATE values gives the number of days. Therefore you have to multiply the above value by 1440 (=24*60, the number of minutes per day). You end up with

select (test_time - sysdate)*24*60
  from test;

This value is negative if test_time is lies in the past. If you want an integer value then round the  result

select round((test_time - sysdate)*24*60,0)
  from test;


Analyse and report disk usage growth historically

If your DBs reside on a SAN or other shared storage that platform may have logs you can reference.  For local storage I know of nothing held by default.

A couple of years ago we started tracking DB size and disk free/used space. It has proved very valuable.  A couple of Powershell scripts, a scheduled task and a small table has helped us avoid several incidents.  Eventually we had enough data to make Monte Carlo prediction workable.


I know that reindexing the PK will lock the table, so I was wondering what best practice for this operation is?

Well having some DB file size/growth and free disk space monitoring would be good so this situation never happens. As you are in this situation I think the plan you have outlined is your only choice until you get more disk space and you really need more disk space.

  This script suits our needs for now, but what is considered best
  practice usually for a table of this size assuming we had infinite
  available space?

Regular index maintenance would be nice. Try looking at Ola Hallengren's maintenance scripts here to solve that.  These scripts provide an almost fully automated methodology for maintenance of your SQL Server databases.


How do I find out whenever a value changes in a certain column?

If you're not already logging changes yourself in your user defined tables, then you'll need to implement a feature that does so for your. Here is a list of features you can use to accomplish this:

Triggers - Fire whenever data changes in a table, can implement logic similar to being in the context of a stored procedure.

Temporal Tables - System versioned copies of the user defined tables that track changes.

Change Tracking - Automated tracking of changes for your user defined tables.

Change Data Capture - Keeps track of DML changes to the specified user defined tables.

Audit - Automatically tracks a multitude of actions at the server level.


MySQL service stuck at starting after increasing 'innodb_log_file_size'

I recommend you change back that setting.

The problem is that the old versions of MySQL did not have a way to change that setting without some extra manual effort, namely removing the log files.

Unless the setting was terribly small, the failure to change it won't impact performance much.

Hmmm...  Are you upgrading from some old version to 8.0?  If so, there are other steps.

Meanwhile,...

  Increase the value of open_files_limit to at least 30000.

That seems very high, unless you expect to have thousands of tables, which is another problem to discuss.

  Change the value of innodb_flush_log_at_trx_commit to 0.

There are 3 options for that variable; I wonder why they picked the worst.


Joining to Queries side by side calculating sales values by year

Use a JOIN clause. So instead of the comma betwee the tables, put FULL OUTER JOIN. And then after A2 put ON A1.Employee = A2.Employee AND A1.Title = A2.Title.

But a better option might be to use a single query, with:

select
   concat(e.titleofcourtesy,' ', e.firstname, e.lastname) as Employee,
   e.Title,
   sum(CASE WHEN o.ShippedDate between '1996-01-01' and '1996-12-31' THEN (od.unitprice * od.quantity) - (1 * od.discount) ELSE 0 END) as TotalSalesValue_1996,
   sum(CASE WHEN o.ShippedDate between '1997-01-01' and '1997-12-31' THEN (od.unitprice * od.quantity) - (1 * od.discount) ELSE 0 END) as TotalSalesValue_1997
from employees as e
inner join orders as o on o.employeeid=e.employeeid
inner join order_details as od on od.orderid=o.orderid
where o.ShippedDate between '1996-01-01' and '1996-12-31'
group by e.title, concat(e.titleofcourtesy,' ', e.firstname, e.lastname);


Saved state for SQL Server Always On on Hyper-V

Since this not production, it's not so critical to do this the "right" way.  Having said that, running QA the same way as production is beneficial for ensuring everything goes well in production.

"Best practices" would be to gracefully shutdown each VM prior to rebooting the host.  That allows the AG to realize the databases need to fail over to the remaining node(s).  You'd also need to do a single host reboot at a time, so the remaining hosts are able to take the load and retain quorum.

Of course, you could just save-state the VMs and let the AG sort itself out - that is after all what high availability is designed to do.


Compare Indexes/Constraints between two SQL Server databases

DECLARE @Sourcedb sysname
DECLARE @Destdb sysname
DECLARE @SQL varchar(max)

SELECT @Sourcedb = '<<Database1>>'
SELECT @Destdb = '<<Database2>>'

SELECT @SQL = ' SELECT ISNULL(SoSource.name,SoDestination.name) ''Object Name''
                     , CASE
                       WHEN SoSource.object_id IS NULL      THEN SoDestination.type_desc +  '' missing in the source -- '
                                                                                + @Sourcedb + ''' COLLATE database_default
                       WHEN SoDestination.object_id IS NULL THEN SoSource.type_desc      +  '' missing in the Destination -- ' + @Destdb
                                                                                + ''' COLLATE database_default
                       ELSE SoDestination.type_desc + '' available in both Source and Destination'' COLLATE database_default
                       END ''Status''
                 FROM (SELECT * FROM ' + @Sourcedb + '.SYS.objects
                        WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')) SoSource
      FULL OUTER JOIN (SELECT * FROM ' + @Destdb + '.SYS.objects
                        WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')) SoDestination
                   ON SoSource.name = SoDestination.name COLLATE database_default
                  AND SoSource.type = SoDestination.type COLLATE database_default
                  ORDER BY isnull(SoSource.type,SoDestination.type)'

EXEC (@Sql)


WAL Files are a record of all the transactions that have taken place .. but that's it - a delta between state before and state after, essentially.  Which means you have to have a BASE to start off of before you can apply any WAL Files.

If you clear out WAL archives without creating a new BASE, then you will be in trouble when you try to restore your database (via PITR) from them.

If you are going to delete WAL files, then you need to make sure you have a valid base to build from again afterward.

Read the documentation on WAL archiving for more information.

In terms of streaming replication, I believe that only uses files located in pg_xlog...  (files are not archived before they are sent, IIRC)

  If that's the case, and we're doing a pg_dump of our master every six hours, could I update our backup script to also clear out all archive files that exist at the start of the pg_dump (once the pg_dump is complete of course)?

Not entirely ... pg_dump creates a file that contains the information of your whole database that can be given to pg_restore...  For PITR, you actually need to create a file-level "backup" by copying your database files and WAL files (after issuing a pg_start_backup)


I've heard of a company which is doing this. They're using a Cassandra cluster with several nodes (in two data centers).


English version of ISO 3166-2 subdivision data

http://download.geonames.org/export/dump/
has (or used to have) asciiname names and "alternatenames".  The latter is a list of names, often in a variety of languages.  Here are the alternatenames for Abu Dabi:
A-pu-that-pi,AEbu Saby,AUH,Aboe Dhabi,Abou Dabi,Abu Dabi,Abu Dabis,Abu Daby,Abu Daibi,Abu Dhabi,Abu Dhabi emiraat,Abu Zabi,Abu Zaby,Abu Zabye,Abu Zabyo,Abu Ḍabi,Abu Ḑabi,Abu-Dabi,Abu-Dabi khot,Abu-Dabio,Abu-Dzabi,Abú Dabí,Abú Daibí,Abú Zabí,Abû Daby,Abū Dabī,Abū Z̧aby,Abū Z̧abye,Abū Z̧abyo,Abū Z̧abī,Ampou Ntampi,Ebu Dabi,Ebu Dhabi,Gorad Abu-Dabi,a bu zha bi,abu dhabi,abu-dabi,abudabi,abudhabi,abw zby,abwzby,aputapi,xa bud abi,Â-pu-tha̍t-pí,Äbu Saby,Əbu-Dabi,Άμπου Ντάμπι,Αμπου Νταμπι,Αμπού Ντάμπι,Абу Даби,Абу-Даби,Абу-Даби хот,Абу-Дабі,Горад Абу-Дабі,Әбу-Даби,Աբու Դաբի,אבו דאבי,أبوظبي,ئەبووزەبی,ابو ظبى,ابوظبی,ابوظہبی,अबु धाबी,अबू धाबी,আবুধাবি,ਅਬੂ ਧਾਬੀ,ଆବୁଧାବି,அபுதாபி,ಅಬು ಧಾಬಿ,അബുദാബി,අබුඩාබි,อาบูดาบี,ཨ་པོའུ་དྷ་པེ།,အဘူဒါဘီမြို့,აბუ-დაბი,አቡ ዳቢ,アブダビ,阿布扎比,아부다비
The table is over 1GB when loaded into MySQL.  I think I downloaded it in 2016.  If you need some tips on getting around hiccups in the loading, let me know.
Ghor and Kapisa are in the table but without the macron accents.


Partitioned Tables and Indexes - what are the downsides?

Expanding upon your list, here are a couple potential downsides that we have come across in real production workloads:

Seeking into multiple partitions

Expanding on the queries that do not use partition elimination could take longer to execute point, there is a specific pattern that is particularly affected: singleton seeks. This operation will become much slower if all (or even a modest subset of) partitions need to be accessed. The skip scan operation essentially performs a seek into every partition that cannot be eliminated.

Let's say that you have a billion row table (N = 1,000,000,000) with rows divided equally into 1,000 partitions (P = 1,000). A single seek is roughly O(log(N)) ~ 30 in a non-partitioned table. However, this same seek operation becomes roughly O(P*log(N/P)) ~ 20,000 in this hypothetical partitioned table. So the seek now performs over 500x more work if data from all partitions is needed (or sometimes even if it isn't needed, but SQL can't prove that based on your query).

Note that this can come up both when you explicitly query the table for one row (or a small range of rows) and in more complex queries when the partitioned table appears in the innder side of a loop join. The good news is that SQL Server is reasonably good about taking this into account in cost-based optimization, but that still typically means that you get a hash join when a loop-seek into a non-partitioned table would have been far more optimal.

Thread skew in parallel query execution

In parallel query plans, threads are allocated to partitions. If there is one partition that is much larger than the others, queries against the table may be particularly susceptible to thread skew. It's possible that one thread gets too high a proportion of rows and is processing long after the other threads have done their work. This situation can happen with non-partitioned tables as well, but any partition functions that do not equally distribute rows are particularly vulnerable.

See Parallel Query Execution Strategy for Partitioned Objects for a more detailed description of the allocation of threads to partitions. For example:

  The query processor uses a parallel execution strategy for queries that select from partitioned objects. As part of the execution strategy, the query processor determines the table partitions required for the query and the proportion of threads to allocate to each partition. In most cases, the query processor allocates an equal or almost equal number of threads to each partition, and then executes the query in parallel across the partitions.


How to re-include excluded folder in SSDT-BI / Visual Studio 2013 database project?

This is actually fairly easy to do. Do not re-create the folder.

With Visual Studio open and the solution loaded, do the following:

View the Solution Explorer (shows on the right side).
Select either the project or any of the folders or items within the project.
In the top button bar within Solution Explorer, click on the >> button on the far right. Three or four new buttons should appear below your cursor (depending on what type of item you had selected).
The far left button in the new set that appeared when you clicked the >> is for Show All Files. Click that. You should now see folders and files, etc that are dotted outline icons instead of solid shapes.
Right click on the folder that was previously excluded from the project.
Select Include In Project.
In the top button bar within Solution Explorer, click on the >> button on the far right. Three or four new buttons should appear below your cursor (depending on what type of item you had selected).
The far left button in the new set that appeared when you clicked the >> should already be selected. Click that to re-hide all of the folders and files, etc with the dotted outline icons.


basic geometric figures and mathematical objects

That isn't the clearest design goal.

"mathematical objects" as I know them are designated with formulas and permit infinity, and other abstract notions.
"geometric figures" are usually just a subclass of Cartesian polygons.

If you need true circles defined with a radius, you're in for a lot of work. Even professional systems like PostGIS struggle with this, reducing them to polygons. If you need simple geometric polygons modeled in the database, it's significantly easier. You can basically look at the geometric primitives that most systems use point/line/polygon.


While I am not advocating the approach being attempted here (i.e. manipulating the Transaction within the Trigger), I at least wanted to explain what was going on so that there was a clearer understanding of how Triggers work.

Here are some basic behaviors of Triggers:

When a Trigger is executed, it is always within a Transaction. Individual DML statements will execute within a system-initiated Transaction (known as auto-commit) if a Transaction was not already active at the time that the DML statement was executed. Meaning:

If @@TRANCOUNT == 0 before the DML statement, then @@TRANCOUNT will be 1 in the Trigger
If @@TRANCOUNT > 0 before the DML statement, then @@TRANCOUNT will be the same value in the Trigger

Unlike Stored Procedures where @@TRANCOUNT needs to be the same before and after execution, Triggers allow for @@TRANCOUNT being higher or lower after their execution, with one exception (see next item).
If the Trigger ends with @@TRANCOUNT being 0 (before the Trigger exits), then

you will get an error stating that the Transaction ended in the Trigger and that the batch is being aborted
the batch being aborted cannot undo / revert whatever ended the Transaction:

If the Transaction was committed, the data will still be committed.
If the Transaction was rolled back, those changes are gone.

Once the Transaction ends, either by COMMIT or ROLLBACK, the inserted and deleted tables no longer have any rows in them. If you need anything from them after the Transaction ends, then you need to capture it into variables and/or a temp table (or table variable) before the Transaction ends.
Triggers execute with an implied XACT_ABORT ON. This might complicate things, even if you have a TRY...CATCH construct. In some cases you might need to explicitly set XACT_ABORT OFF

That said, you certainly can COMMIT / ROLLBACK the Transaction within a Trigger. Simplistically, if you do either of those actions, you just need to issue a BEGIN TRAN; before the Trigger ends so that you don't get the error about it aborting the batch. This assumes, in the case of ROLLBACK, that you want to mask the fact that this happened instead of allowing ROLLBACK to cancel the entire operation, which is how it is normally used within Triggers.

However, reality is not so simple:

If there are multiple Triggers, the order in which they fire could result in different behavior. Assuming there were no more than 3 Triggers, you can control the order by setting the "first" and "last" Triggers. More than 3 Triggers will likely not be able to control the ordering of all of the Triggers.
If there are nested Triggers, then ending the Transaction in the Trigger will could lead to unexptected behavior.
If there is an active Transaction before the Trigger executes, then ending the Transaction in the Trigger will take control away from whatever is happening after the Trigger.

To account for the possibility of an active Transaction already existing prior to the Trigger firing, you would need to capture @@TRANCOUNT at the top and then issue the correct number of BEGIN TRAN;, and possibly also COMMIT TRAN;, statements:

DECLARE @TranCount INT = @@TRANCOUNT,
        @Index INT = 0;

...

-- For an actual COMMIT, do the following:
WHILE (@Index < @TranCount)
BEGIN;
   COMMIT TRAN;
   SET @Index += 1;
END;

....

-- If either COMMIT or ROLLBACK occurs, do the following:
SET @Index = 0; -- reset in case COMMIT was processed above
WHILE (@Index < @TranCount)
BEGIN;
   BEGIN TRAN;
   SET @Index += 1;
END;

Again, this is not recommended as it changes the nature of any existing Transaction to behave in an unexpected, non-intuitive way. The flow would end up being:

Begin Transaction A
Do something
Insert into this Table

Transaction A might be committed or rolled-back, in which case:

Actions performed as "Do something" above are either committed or rolled-back
Transaction B will be started

Transaction A might be left alone, in which case there is no Transaction B

After the Trigger exists

If Transaction A was committed or rolled-back, it won't be possible to change that action. Transaction B will now exist, and changes made at this point will be completely disconnected from changes made previously in Transaction A.
If Transaction A was left as active:

Actions taken previously are still available to COMMIT or ROLLBACK
Actions taken after the Trigger ends are grouped along with actions taken before the Trigger fired as an atomic unit (this is how things usually work and hence the expected behavior).

The definitive reference on all this is Error and Transaction Handling in SQL Server by Erland Sommarskog.


I think you are possibly confusing two different but related items. The first is the installation, which you absolutely should use the installer for. Full installation instructions can be found here.

The second item is about creating and publishing content. Generally you do this via SSDT or the BI Components of Visual Studio (or just knock up the XML in a text editor of your choice if you feel like a challenge). Once you have created your reports you can then publish them to SSRS via Visual Studio or take the RDL file and publish it manually. There is a brief tutorial on how to do this in MS Docs.

The most important difference between these two items though is that even if you installed SSDT/Visual Studio and have created your reports, unless you have actually got somewhere to host them (i.e. by installing SSRS on a server or within Power BI Service) then you cannot share them or access them other than from within Visual Studio.


Should a SKU be associated to warehouse or supply table?

If the SKU is an attribute of a supply entity being located at a warehouse, then I would agree with your assessment to put it in the warehouse_supply table.
OTOH, if the company has decided that SKUs are the same regardless of the location, then it is an attribute of supply.


SQL Server Express Edition will honor the specified processor affinity settings but not more than the 4 core edition limitation.  I don't think it's defined which 4 cores are used if you specify more than 4.  Note that unlike other SQL Server editions, a service restart is required for changes to take affect.


Scheduled copy of large table from SQL Server 2000 to 2008

I would likely put a TRIGGER on the 2000 table and insert the captured changes into an audit table, then either immediately sync the 2008 database with just those changes through SSIS ( or even a linked server if that suits you ) or do an overnight sync, depending on what would have the less impact on business-hours performance and the network as a whole, truncating the audit table on successful transfers either way.  This would ensure the changes are easy to acquire and small enough to pull across the network without generating calls from your infrastructure engineers.  The audit table itself could even live in a different file group to reduce disk contention if necessary.

That said, there's probably half a million ways to go about doing this, and your specific, optimal solution is going to revolve largely around the details of how your environment operates today and even the specific structure of the table in question.  For instance, if the "rebuild" you've mentioned is a severe example, involving either TRUNCATE or a complete DROP and CREATE, the simple TRIGGER suggestion I've made is not going to be the solution for you without a significantly extended amount of effort, especially compounded if your source table is using an IDENTITY column.

In that case, a Lookup task in a Data Flow may seem like a reasonable route at a glance, but this effectively results in the entire table being pulled across the network anyway for the comparison.  Comparison logic at this point is just unnecessary overhead, you may as well stick the receiving catalog in SIMPLE recovery mode and stage all of the data you've received on the other side of the pipe.  Doing an ALTER ... SWITCH after the transfer would at least then limit the lock on your reporting table to a single meta data operation, keeping it otherwise completely available to the reports being generated from it.


I've only briefly scanned though the document but my first thought was "oh, a SQLCAT publication". These guys push out some fantastic material but much of their guidance is derived from the very extreme end of pushing the boundaries.

You can learn a great deal from reading about SQL Server behaviour in multi-million $$$ implementations but very little of it applies to typical installations. This particular document is the result of testing a 10k/tps, 256 core, 500 spindle system.

In the real world where you aren't pushing SQL Server internals beyond their anticipated limits, these are interesting diversions, not recommendations.

Your question is perhaps somewhat dangerous. A casual passer by may read your question, briefly scan the linked document and conclude that padding rows to fill a page is a good idea.

  If I have 256 cores and 500 disks delivering 18GB/sec but I'm seeing
  PAGELATCH_EX waits for my highly specialised workload xyz, will I
  increase throughput by padding rows to fill a page?

Yes. As Thomas Kejser has demonstrated.

  Should we all be doing the same?

Absolutely not!


Why have both a postmaster and postgres executable?

The postmaster symlink appears to be historical. I didn't even know it existed until you pointed it out.

The other two are a typical pattern for UNIX daemon applications. postgres provides the server functionality, and pg_ctl provides control client for it.

It would be possible to bundle pg_ctl's functionality into postgres, but doing so would mean postgres would have to run in two modes: as a DB server, or as a client to connect to a running DB server. That's a little bit unpleasant architecturally, and would add what's essentially "dead code" (unreachable and unusable) to running PostgreSQL server instances.

Similarly, it'd be possible to remove the ability to launch a PostgreSQL server from the postgres executable and require you to do it via pg_ctl, but that'd be cumbersome, and would really annoy people who manage distribution init scripts. They usually want to bypass application startup programs and launch the daemon directly, so they can prevent it from detaching to run in the background, trap exit codes, capture stderr, etc.

Essentially: postgres is the low level server program. pg_ctl is a user tool for starting/stopping the server and other management actions. postmaster is an obsolete alias for postgres.

In general you use distribution service management (service, systemctl, update-rc.d, etc) if you installed PostgreSQL from packages, or pg_ctl if you installed from source or 3rd party binary installers that don't integrate with the operating system services system.


What model design supports both data store backed and free form entries?

Based on the details you've provided I'd say it sounds fine.

Things that may or may not be a problem based on further information.

(1) Even though you say there is no relationship between the two systems and no foreign key, if you were to switch third part systems, you would be left with a bunch of meaningless ids for books that were added from the previous 3rd part system.  Is that okay?  If not, you may need to add a lookup table of third party systems that can related to the IDs in the Book table so you can later tell which system they came from.

(2) If the only reason for the IDs from the third party system to be there is to check for their existence, maybe a simply IsFrom3rdParty or IsFreeForm boolean column would be sufficient.  It would seem to meet all the requirements you lay out here and would be simpler than the ID to implement and would better convey the actual information you have (which is simply that it came from a 3rd party system.  You can't guarantee that the ID will relate to anything especially if the backend system has changed)

Otherwise, yes it sounds good.


Implementation of a many-to-many relationship with total participation constraints in SQL

It's not easy to do in SQL but it is not impossible. If you want this enforced through DDL alone, the DBMS has to have implemented DEFERRABLE constraints. This could be done (and can be checked to work in Postgres, that has implemented them):

-- lets create first the 2 tables, A and B:
CREATE TABLE a
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT a_pk PRIMARY KEY (aid)
 );

CREATE TABLE b
( bid INT NOT NULL,
  aid INT NOT NULL,
  CONSTRAINT b_pk PRIMARY KEY (bid)
 );

-- then table R:
CREATE TABLE r
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT r_pk PRIMARY KEY (aid, bid),
  CONSTRAINT a_r_fk FOREIGN KEY (aid) REFERENCES a,
  CONSTRAINT b_r_fk FOREIGN KEY (bid) REFERENCES b
 );

Up to here is the "normal" design, where every A can be related to zero, one or many B and every B can be related to zero, one or many A.

The "total participation" restriction needs constraints in the reverse order (from A and B respectively, referencing R). Having FOREIGN KEY constraints in opposite directions (from X to Y and from Y to X) is forming a circle (a "chicken and egg" problem) and that's why we need one of them at least to be DEFERRABLE. In this case we have two circles (A -> R -> A and B -> R -> B so we need two deferrable constraints:

-- then we add the 2 constraints that enforce the "total participation":
ALTER TABLE a
  ADD CONSTRAINT r_a_fk FOREIGN KEY (aid, bid) REFERENCES r
    DEFERRABLE INITIALLY DEFERRED ;

ALTER TABLE b
  ADD CONSTRAINT r_b_fk FOREIGN KEY (aid, bid) REFERENCES r
    DEFERRABLE INITIALLY DEFERRED ;

Then we can test that we can insert data. Note that the INITIALLY DEFERRED is not needed. We could have defined the constraints as DEFERRABLE INITIALLY IMMEDIATE but then we'd have to use the SET CONSTRAINTS statement to defer them during the transaction. In every case though, we do need to insert into the tables in a single transaction:

-- insert data
BEGIN TRANSACTION ;
    INSERT INTO a (aid, bid)
    VALUES
      (1, 1),    (2, 5),
      (3, 7),    (4, 1) ;

    INSERT INTO b (aid, bid)
    VALUES
      (1, 1),    (1, 2),
      (2, 3),    (2, 4),
      (2, 5),    (3, 6),
      (3, 7) ;

    INSERT INTO r (aid, bid)
    VALUES
      (1, 1),    (1, 2),
      (2, 3),    (2, 4),
      (2, 5),    (3, 6),
      (3, 7),    (4, 1),
      (4, 2),    (4, 7) ;
 END ;

Tested at SQLfiddle.

If the DBMS does not have DEFERRABLE constraints, one workaround is to define the A (bid) and B (aid) columns as NULL. The INSERT procedures/statements will then have to first insert into A and B (putting nulls in bid and aid respectively), then insert into R and then update the null values above to the related not null values from R.

With this approach, the DBMS does not enforce the requirements by DDL alone but every INSERT (and UPDATE and DELETE and MERGE) procedure has to be considered and adjusted accordingly and users have to be restricted to use only them and not have direct write access to the tables.

Having circles in the FOREIGN KEY constraints is not considered by many the best practice and for good reasons, complexity being one of them. With the second approach for example (with nullable columns), updating and deleting rows will still have to be done with extra code, depending on the DBMS. In SQL Server for example, you can't just put ON DELETE CASCADE because cascading updates and deletes are not allowed when there are FK circles.

Please read also the answers at this related question:
How to have a one-to-many relationship with a privileged child?

Another, 3rd approach (see my answer in the above mentioned question) is to remove the circular FKs completely. So, keeping the first part of the code (with tables A, B, R and foreign keys only from R to A and B) almost intact (actually simplifying it), we add another table for A to store the "must have one" related item from B. So, the A (bid) column moves to A_one (bid) The same is done for the reverse relationship from B to A:

CREATE TABLE a
( aid INT NOT NULL,
  CONSTRAINT a_pk PRIMARY KEY (aid)
 );

CREATE TABLE b
( bid INT NOT NULL,
  CONSTRAINT b_pk PRIMARY KEY (bid)
 );

-- then table R:
CREATE TABLE r
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT r_pk PRIMARY KEY (aid, bid),
  CONSTRAINT a_r_fk FOREIGN KEY (aid) REFERENCES a,
  CONSTRAINT b_r_fk FOREIGN KEY (bid) REFERENCES b
 );

CREATE TABLE a_one
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT a_one_pk PRIMARY KEY (aid),
  CONSTRAINT r_a_fk FOREIGN KEY (aid, bid) REFERENCES r
 );

CREATE TABLE b_one
( bid INT NOT NULL,
  aid INT NOT NULL,
  CONSTRAINT b_one_pk PRIMARY KEY (bid),
  CONSTRAINT r_b_fk FOREIGN KEY (aid, bid) REFERENCES r
 );

The difference over the 1st and 2nd approach is that there are no circular FKs, so cascading updates and deletes will work just fine. The enforcement of "total participation" is not by DDL alone, as in 2nd approach, and has to be done by appropriate procedures (INSERT/UPDATE/DELETE/MERGE). A minor difference with the 2nd approach is that all the columns can be defined not nullable.

Another, 4th approach (see @Aaron Bertrand's answer in the above mentioned question) is to use filtered/partial unique indexes, if they are available in your DBMS (you'd need two of them, in R table, for this case). This is very similar to the 3rd approach, except that you won't need the 2 extra tables. The "total participation" constraint has still to be applied by code.


Is an ER diagram an example of a conceptual, logical or physical schema?

When ER Modeling was introduced in the 1970s, the intent was to provide a model that was not biased towards one particular implementation.  People were constructing relational models of a given project, even if the intent were to implement on a hierarchical or network database.

This evolved into what I learned as conceptual modeling, and that's probably what you learned as conceptual modeling as well.  The way I learned it, the conceptual model is useful for analysis of the subject matter from a data centric point of view.  That model captures what the information requirements on the database are going to be. It does NOT capture features of the proposed design.

ER diagrams initially reflected only ER models, and thus were suitable for depicting conceptual models.

When I learned logical modeling, this reflected the first stage of design, as distinct from analysis.  If the final target is a relational database, it makes sense for this model to be relational.  When I first learned this stuff, ER diagrams were not used to depict relational models.  Instead there was a kind of diagram that I'll call a relational schematic.  A relational schematic had somewhat different appearance from an ER diagram, and also somewhat different content.  For example, a relational schematic usually used arrowhead notation while an ER diagram usually used crow's foot notation.

Differences in content were mainly these:  a relational schematic included foreign keys while an ER diagram did not.  And many-to-many relationships were depicted differently.  In the relational schematic, a junction table is needed to hold the two (or more) foreign keys, whenever there's a many-to-many relationship.  In an ER diagram, a many-to-many relationship was depicted with a line with a crow's foot at both ends.

Physical models added details to the logical model, and generally reflected features available in the specific DBMS intended for implementation, as well as certain other considerations.

Over the last 20 years, there have been two broad trends.  The first trend has been to use ER diagrams to depict either relational models or ER models.  The second trend has been to skip over analysis completely and proceed directly to design. Thus, a single model depicts both the understanding of the requirements and the features of the proposed solution.

This blending of analysis and design works pretty well on small scale projects,  because the analysis is pretty trivial anyway.  For large scale projects, it can be a disaster.  You end up with the right solution to the wrong problem.  By the time the mismatch is discovered, the budget has been spent, and the deadline is looming.

There's more detail in a post-relational world, but I'll stop here.


Parent Child Relation in same Table . Select Query to get only parent records and Records not comes under parent child relation

SELECT t1.*
FROM "table" t1
WHERE t1.parent IS NULL
  AND /* NOT */ EXISTS (SELECT 1
                        FROM "table" t2
                        WHERE t1.id = t2.parent)

EXISTS - for parent records.

NOT EXISTS - for single records.


Call to Function in Oracle says it's not declared

A function has to return something (in your case an INTEGER) somewhere.

Try SELECT SOLICITAR_DISPONIBILIDAD(1,0,2014,0) FROM DUAL


Extract Date Portion Only From DateTime Field

You can check for the daily boundaries i.e. between the begining of the day at 00:00 and the begining of the next day at 00:00.

Query:

DECLARE @date datetime = '20160301'

SELECT *
FROM #Bbb
WHERE datetime1 >= @date AND datetime1 < DATEADD(day, 1, @date)
;

Or this WHERE clause for dates on a given day or later:

WHERE datetime1 >= @date

Output:

id  | datetime1
1   | 2016-03-01 17:35:19.000
2   | 2016-03-01 18:44:15.000

You can also remove the TIME part (set it to 00:00:00.000) with this query:

DECLARE @date datetime = getdate()
SELECT @date, DATEADD(day, DATEDIFF(day, 0, @date), 0);

However using functions will limit index usage.

Ouput:

datetime                | date
2016-03-09 14:40:25.080 | 2016-03-09 00:00:00.000


Proper way to create table friends in single statement

Don't know where shown_id came from, but I guess it's a typo.

Instead of populating only one column (user_id) of your friends table, you can add another user_id column to the select statement with friend_id alias. Immediate update query is not needed.

create table friends (
    user_id int,
    friend_id int,
    foreign key (user_id) references users (user_id),
    foreign key (friend_id) references users (user_id)
) as (
    select user_id, user_id as friend_id
    from users
);


Is it possible to give the optimizer more or all time it needs?

Next to trace flag 2301, there is 8780 which really does make the optimizer 'work harder' since it just gives it more time (not unlimited, as described in detail here (russian) and less detailed here) to do its thing.

Detailed description in english of the original author of the russian article. which includes the author's own warning:

  it is not recommended ever use it in production.

Combining the two and applying them (very selectively via query hint OPTION (QUERYTRACEON 2301, QUERYTRACEON 8780) to a query of 4-level nested inline TVFs (where only the one at the bottom would do any real work and the upper levels would correlate results via EXISTS subqueries) resulted in a nice MERGE JOIN and several LAZY SPOOLs that cut down execution time by half.


SQL Server connectivity and script execution using PowerShell

You need to have the accompanying assembly on the machine that you are trying to load on.

If you want to use SMO you have two options:

Install the required files on that machine
Run your script on a box that already has SMO

In order to install SMO without having to install SQL Server, follow the instructions on this BOL reference, Installing SMO.


You'll probably want to do this with bcp, which is intended for this kind of thing. It would be something like this:

bcp "SELECT ROW_KEY, ISNULL(CARRIER_ID, ''),ISNULL(CONVERT(VARCHAR(19),ORDER_DATETIME,120),''),ISNULL(HEIGHT, ''),ISNULL(RESIDENTIAL_CHARGE, '') FROM MYTABLE" queryout OUTPUT_FILE.txt -c -S <servername> -U <login_name> -P <password>

Replace -c with -w if you're dealing with nvarchar/nchar, and want Unicode output.


Transactional replication pre and post scripts

At my last job this is exactly what we did for a very similar scenario (also on SQL Server 2008 R2 - Standard Edition), and I was the one to build it out. We had an off the shelf vendor application we heavily used and needed to report out of. It was indexed differently than how we were reporting out of it.

We had one way transactional replication setup to sync from the vendor app to our report server. I setup pre and post scripts to drop and recreate the appropriate indexes (and some other relevant entities like indexed views), which of course checked for object existence first too (you can check this via the sys schema's tables and views) before executing.

The scripts themselves just pointed to a stored procedure where all the logic lived, on the subscriber server. This just felt more natural for maintaining the code than updating an external script file. The pre script to drop the custom indexes before the schema was synced was important because there was a bug we ran into where transactional replication would occasionally break and stop syncing otherwise. (If you need a code example of my pre and post scripts, let me know and I'll update this answer.)

In regards to your second question, in general it's faster to bulk insert your records into a table and then add your indexes after (this gives SQL server the full picture to arrange the data in the most efficient manner, at one time). To do this with transactional replication, you'll need to de-select "copy clustered indexes" (and non-clustered indexes for that matter) in the Publisher's Properties window under Article Properties for that table. (Letting it sync the data with the Publisher's clustered index is essentially just doubling the work your server will need to do when you re-index it on the Subscriber with your preferred clustered index.)


Left join on one or the other column without table-scanning

When you have two separate indexes on transact and on transactchild the server can't use them efficiently when searching for (t.rid=blk.transact OR t.rid=blk.transactchild).

The classic way to handle this type of OR search is to split it into two simple searches and then UNION results together.

So, you can try something like this:

SELECT ....
FROM ...
LEFT JOIN table2 blk ON (t.rid=blk.transact)

UNION

SELECT ....
FROM ...
LEFT JOIN table2 blk ON (t.rid=blk.transactchild)

In this case individual simple query should be able to use corresponding index, but the extra UNION step can be quite expensive, especially if it requires a sort.

If your data is such that you know that UNION ALL would produce correct result, then use UNION ALL, which is much more efficient than UNION. UNION eliminates duplicates, which is expensive. UNION ALL simply outputs results of two queries one after another.

If values in blk.transact do not overlap with values in blk.transactchild, then you can use UNION ALL.


QGIS PostGIS Authentication Failure with "trust" option

Just don't leave the Database field empty in the connection dialog.

When left empty, it will attempt to connect to a database named after your OS login name, which probably doesn't exist in your case.

The Host field should be left empty to use a Unix domain socket connection, as hinted by the snippet of your pg_hba.conf shown in the question.

When you put localhost in this field, it attempts a TCP connection which is not what's called local in the pg_hba.conf terminology, so that the entry local all all trust will not match with such a connection.


I need to run a procedure that goes through all schemas tables in MySQL server

Selecting TABLE_NAME is not enough. You need CONCAT(TABLE_NAME, '.', TABLE_SCHEMA).
You cannot use a variable in a query as a place where tablename is stored. You must use dynamic SQL (prepared statement).

Prepared SQL Statement Syntax

  I need now to loop on each table how could I do that in prepared statement

Get each database.table which must be converted. Build proper altering query text in a variable. Prepare. Execute. Iterate next. Repeat until all tables processed. - akina


Best indexing strategy for query that searches between two bigint columns

I think a composite index with both ip_from and ip_to would be the most efficient for this range search. If the only columns returned are latitude and longitude, those queries should be included as non-key columns in the non-clustered for the best performance of this particular query. The index should be clustered instead of you often return the other columns.

Because your query specifies TOP (1) without an ORDER BY, any arbritrary row may be returned within the range search.  Specify 'ORDER BY' with columns to uniquely identify a row if you need the same row returned each time.


How can I count distinct columns inside a group?

I'm pretty sure that this will work for you.  I don't have a MySQL instance available to me to check my syntax, but this should get you close.

SELECT gender, COUNT(id_number)
FROM (  SELECT DISTINCT
            name
            , surname
            , id_number
            , gender
        FROM table1
        ) AS D
GROUP BY gender;


How can I create a unique and random alphanumeric string using MySQL's Default Value? (v 5.6)

I'm a bit late, but I've been having difficulty finding a way to insert cryptographically secure alphanumeric text, so here's what I've come up with.

I created a function to generate the strings and a stored procedure to insert data into the table. It uses INSERT IGNORE and checks ROW_COUNT() to see if the insert succeeded. I know INSERT IGNORE is not ideal, but this is the best I have so far.

DROP FUNCTION IF EXISTS random_alphanumeric;
DELIMITER //
CREATE FUNCTION random_alphanumeric
(len INT)
RETURNS VARCHAR(512)
BEGIN
    DECLARE result VARCHAR(1200);
    DECLARE segment VARCHAR(700);
    DECLARE l INT;
    IF len<0 THEN
        SIGNAL SQLSTATE '45000' SET message_text = 'Attempt to generate random alphanumeric with negative length.';
    END IF;
    IF len>512 THEN
        SIGNAL SQLSTATE '45000' SET message_text = 'Cannot generate random alphanumeric longer than 512 characters.';
    END IF;
    SET result='';
    WHILE LENGTH(result)<len DO
        SET segment=REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        TO_BASE64(RANDOM_BYTES((len-LENGTH(result))+3)),'/',''
                    ),'=',''
                ),'+',''
            ),'\n',''
        );
        #The last characters of a base 64 string aren't always well distributed
        #Trim a couple off
        SET l=LENGTH(segment)-3;
        IF l>0 THEN
            SET result=CONCAT(result,SUBSTRING(segment,1,l));
        END IF;
    END WHILE;
    RETURN SUBSTRING(result,-1*len);
END //
DELIMITER ;

CREATE TABLE `test_random` (
  `id` VARCHAR(32) NOT NULL,
  `int_col` INT,
  `varchar_col` VARCHAR(45),
  PRIMARY KEY (`id`));

DROP PROCEDURE IF EXISTS test_random_insert;
DELIMITER //
CREATE PROCEDURE test_random_insert
( IN int_value INT, IN varchar_value VARCHAR(45))
        BEGIN
            DECLARE id_value VARCHAR(32);
            DECLARE ok INT;
            SET ok=0;
            WHILE NOT ok DO
                SET id_value=random_alphanumeric(32);
                INSERT IGNORE INTO `test_random` (`id`,`int_col`,`varchar_col`) VALUES (id_value, int_value, varchar_value);
                SET ok=ROW_COUNT();
            END WHILE;
            SELECT id_value id;
        END//
DELIMITER ;

To insert a record CALL test_random_insert(100,'text value'); The returned recordset has an id column, since LAST_INSERT_ID() only applies to autoincrement values.


LTREE extension does not exist after CREATE EXTENSION

The extension ltree is installed in the schema with the object ID 361964. If you want to know the name of that schema, run

SELECT 361964::regnamespace;

It could be that that schema is not on the search_path when you execute the function. That would explain the error.

If you install an extension in a schema other than public, it is usually best to reference its objects with schema qualification, for example

DECLARE
    db_path_ltree extschema.ltree;


What does it mean when a table is listed with fragmentation?

Dont try to reinvent the wheel as there is a cost to reinventing it.

Use Ola's maintenance scripts - they are tested well enough and follow best practice. You can even use LockTimeout in Ola's solution.

I would not worry on the fragmentation levels where the page count is less than 5K.

  What is the best way to use rebuild\reorganize in this situation?

Do the index maintenance during less activity or maintenance time.


Inserting Missing day of data into table - Need to increment primary key by 618 for each key

Since you're changing the identity of the row, can we assume that it's not used as a foreign key of another table? If it's only purpose is to uniquely identify the row of the table it's on, then don't bother attempting to assign it a new identity because it's already auto incremented by one, upload your CSV without specifying the id column and it will take care of the id.

You'd have to worry about key collision and identity_insert etc, it's just a mess, but if your heart is set on it, there are ways it could be done.


I'm a student and i have assignments and projects simultaneously. So I just want my project database and the other simple databases separate.

Kudos to you for some excellent thinking.
I know of "experienced", "professional" Developers that wouldn't have even thought of this.

... I have no option to use MySQL or others as my lecturer suggested only to use Oracle 11G and SQLPlus

Take this idea to your lecturer.
With any luck, they'll be as impressed as I was.
That said, Oracle 11 isn't SQL Server and creating an Oracle database is not a trivial exercise.  It's a shame you don't have access to Oracle 12, where Pluggable Databases make life a lot easier, but if you're stuck with '11 ...
I would suggest you use schemas within a single database to achieve your goal.
Here's how I would set up [one of] your Project[s]:
A Tablespace can be restored independently of any other so let's put all the "Project" stuff into a Tablespace of its own:
create tablespace my_project
   datafile '/.../my_project01.dbf' size 50M autoextend ;

A schema is just another account.
create user my_project
   identified by a_really_strong_password
   default tablespace my_project
   ;
grant create session, resource to my_project ;

Obviously to change the objects under the my_project schema, you'd sign on as the schema owner account.  However, to just use those objects, you probably ought to put a "safe distance" between you and doing any "damage".
Taking this step "away" from your data is another, big step to working well with Databases.
create role my_project_role
   identified by a_proper_password ;

The role will give [update] access to the objects in the schema, but only once you've given those privileges to the Role!  You'll need lots of the following, especially after you add, say, any new tables.
grant delete, insert, select, update on my_project.table_1 to my_project_role ;

If you're going to have other people looking at this, you might also want a second role to allow this, but not to let them break anything:
create role my_project_read
   identified by another_proper_password
   ;
grant select on my_project.table_1 to my_project_read ;

Lastly, you need an account for yourself:
create user me
   identified by yet_another_proper_password
   ;

grant create session, my_project_role to me ;

alter user me default role none ;

Now, when you sign on, you'll need to activate the role, with its password, before you can see any of your my_project.* objects.
set role my_project_role identified by a_proper_password ;

OK, I grant you this is a bit inconvenient, but it is more secure.
That said, if you're anything like me, after about a week you'll be royally fed up with it and you'll want the role to just be there, available "by default".
To achieve this, don't have a password on it and make it [one of] your "default" role[s]:
alter role my_project_role NOT IDENTIFIED ;
alter user me default role all ;


I don't see much difference in the two designs. Whether you have 2 tables for the tags or 1 table with a type attribute (that can take 2 values), you basically going to need the same (in matters or complexity and structure) queries.

What is different is your decision to materialize the relationship between items and the section_tags, the tags that are "inherited" to them through the relationship with the sections. But that can be done with either the first or the second design. And yes, it would simplify querying (on the expense of making harder to have the materialized tables/views in sync.)

So, about the choice of design, if as you say the item and the section tags are two different types of tags, either design would be ok and I don't think it would make much difference, except on the details in the interaction between applications and the database.

For the materialization part of question, I think the best would be to test both options. If you are to make a decision fast, you could take the first path. Keep the design simple and only if you have efficiency issues that can't be overcome, examine the option of materialization as it would be quite complicated (as you have already thought) to keep it synced.


How can I make a database backup go faster?

Theory

Multiple targets for the backup can be very useful, but only in certain situations.

Your source database can be read faster than your current single file backup can write.
Your backup targets for each database are on separate spindles.
You aren't using shared SAN HBA or iSCSI or other bandwidth between the SQL Server instance and the media.
i.e. the IOPS from writing Backup File A DO NOT use the same disks as writing Backup File B.

If all of these are true, then it's possible that some degree of parallelism (many output files) will increase the speed.  If all of these are not true, more than likely you'll cause one or more sets of disks to thrash, and your multi-file backups will actually be slower and also may cause OS filesystem or storage level fragmentation, because you're writing Backup File A and Backup File B at the same time!

Note that many of the same things apply that I covered in my answer to Transaction Log Backups Serial or Parallel?, since this is about writing in parallel (though not necessarily reading, depending on how your filegroups and files are laid out).

Experiment

To really understand this, you may need to see about borrowing some equipment from close friends.

Obtain one (1) fast solid state disk

Any of the good SATA2 or SATA3 or mSATA Samsung, Crucial, Mushkin, etc. internal drives will do
You need to ensure that you can read faster than you can write.

Beg, borrow, or buy at least two disk drives in addition to the drive your SQL Server data and log files are on.

One (1) of these can be a USB2 external drive (or a USB3 drive) plugged into a USB2 port
One (1) of these can be a USB2 external drive plugged into a USB3 port

We can be reasonably sure the USB2 and USB3 ports aren't sharing a bus different controllers
You can't put a USB3 drive into a USB3 port here, because we WANT our backup devices to be slow individually, but not contend with each other for bus bandwidth

One or more drives can be SATA spinning disks

Log into your local SQL Server Developer or SQL Server Express instance

Create a reasonably big database with data and log files on the fast SATA SSD.
Now try backing up twice in a row (two BACKUP DATABASE commands in the same SQL batch) to each backup device, one device at a time
Record the speed of backup and verify on only the second backup to each, i.e. after caches have been filled.
Now you know how fast each is by themselves; one is likely better than the others.
Now try backing up twice in a row (two BACKUP DATABASE commands in the same SQL batch) to ALL backup devices at once (i.e. with multiple DISK arguments)

See the speed difference?

Now play around with BUFFERCOUNT, MAXTRANSFERSIZE, and if you're on developer edition, COMPRESSION.

ALWAYS use the CHECKSUM option!


grant access to user 'root' to all databases, but least specific one

Unfortunately, you must do lots of these:

GRANT ALL ON db1.* TO ...;
GRANT ALL ON db3.* TO ...;  -- skipping db2
...

The syntax of GRANT/REVOKE is too clumsy to get what you want in any easier way.

You could write a query something like this to generate them:

SELECT
       CONCAT('GRANT ALL ON ',
              SCHEMA_NAME,
              '.* TO ...;')
    FROM information_schema.SCHEMATA
    WHERE SCHEMA_NAME NOT IN ('db2', 'mysql');

Then manually execute them.

Precede that by doing a GRANT USAGE TO ... to provide the password.


Just turn on timing by entering:

\timing


Oracle

From AskTom in 2000:

  ...the only method Oracle operates in is "client tells us when to commit".  We do not have an autocommit mode. Various TOOLS and API's do (eg: I can tell sqlplus to autocommit, that just means sqlplus will issue a commit after each statement).

This is still true and probably always will be. For a detailed description of commit see the Oracle docs

SQL Server 2000+

from SO (inverted):

  You can turn autocommit OFF by setting implicit_transactions ON

or if you prefer, from the docs (or Mark's more direct link)


How to grant drop table/function privileges to a role in PostgreSQL?

Only the owner (and superusers) can drop objects. Per documentation:

  The right to drop an object, or to alter its definition in any way, is
  not treated as a grantable privilege; it is inherent in the owner, and
  cannot be granted or revoked. (However, a similar effect can be
  obtained by granting or revoking membership in the role that owns the
  object; see below.) The owner implicitly has all grant options for the
  object, too.

So, make administrator own such objects that users should be able to drop.

ALTER FUNCTION foo() OWNER TO administrator;
ALTER TABLE foo      OWNER TO administrator;

And you remembered to actually grant group membership, right?

GRANT administrator TO _administrator;


How to install columnstore in MariaDB on Mac OS X with Homebrew

MariaDB currently is a distinct download from regular vanilla which adds in the columnstore engine: https://mariadb.com/downloads/mariadb-ax

However we don't currently have a native Mac build so the simplest recommendation is to run this via docker in a vm: https://mariadb.com/kb/en/library/mariadb-columnstore-docker-vagrant-and-windows-10-linux-setup-allows-for-ev/


MongoDB/MySQL - auto increment vs object ID

In MySQL, or almost any other RDBMS, it is common to have an ID for the row (object), so you use it in other tables to make a link between the tables (Relations, or PK and FK if you prefer). This is part of the normalization process that is recommended for most types of DB design [there are exceptions though].

In MongoDB however, the general way or design is to denormalize. i.e. the document (row) is stored with all its related objects.

To make it more clear:

In MySQL you would have two tables: cars and car_types. In cars you only store the ID of the type of this car.

In Mongo DB, you would have one collection called cars, and the document in this collection would have a sub-document that contains the type's info. (You still can have a separate collection for types, but you don't store only objectID in car's collection)

HTH


add mobile prefix in existing mobile field in mysql

A simple UPDATE should suffice. Make sure your column isn't a numeric type or you will lose leading zeroes.

UPDATE USERS SET MOBILE = CONCAT('092', MOBILE)

Also think about the possibility of keeping the prefix on a separate column, it would be easier to update and it won't be mixed up with the actual mobile number.


Getting time delta between two related rows

You can use the solution with the window function, you just need to filter out the "other" row. This can e.g. be done using Postgres' distinct on ()

SELECT distinct on (name)
       name,
       timestamp_received - LAG(timestamp_received, 1) OVER (PARTITION BY name ORDER BY id ASC) as delta
FROM message
order by name, timestamp_received desc;

This will always pick exactly one row per name (the one with the latest timestamp_received), even if there are more then two rows per name.


When creating an optimal index, start with = -- vendor_id in your case.

Then you get one crack at a "range" -- updated_at or (in the first case) code.

Also (or alternatively) "covering" the query can be beneficial.  This is where all the columns mentioned in the SELECT exist in the INDEX.  In the case of SELECT *, that is all columns in the table, and that is usually to many.  When doing code, count(*), only 3 or 4 columns are mentioned, so "covering" may be practical and beneficial.

The PRIMARY KEY (id) is included implicitly in any secondary key.

It is handy to provide the entire EXPLAIN when asking questions like these.  It is instructive to discuss "key_len" and how that tells you how many columns it used.  EXPLAIN FORMAT=JSON SELECT ... is even better, since it is explicit.

Why did it pick (vendor_Id, code, feedback, updated_At, id) for the first query?  It is missing created_at, so it is not "covering".  Probably it decided that vendor_id (=) plus code (range) was slightly better than vendor_id, updated_At.  I would expect the latter (aka "Index1") to be better because it might be able to consume the ORDER BY.

index1 (vendor_Id, updated_At) is redundant (and can be DROPped) because of codeIndex (vendor_Id, updated_At, code).

The second query needs (vendor_Id, updated_At, code) -- =, "range", "covering".  The query can be performed in the index, without touching the data.

The third query adds feedback; so it needs the columns of (vendor_Id, code, feedback, updated_At), but it is hard to know whether that order is best or (vendor_Id, updated_At, code, feedback).

What version are you using?  5.6, 5.7, and 8.0 have each made significant changes to optimizations in this area.

BTW, and  code is not null is redundant in the first query.

The first query cannot stop after 10 rows; this is because there is more than one "range" in the WHERE.


Would CRUD operations get very slow on an mysql innodb table which got more than 50 million rows?

I have seen a billion-row table hum along fine.

I have seen a thousand-row table cause the bones of the server to creak and groan.

It all depends on the queries.

"several thousand transactions per day" -- No Problem.  "several thousand transactions per second" -- This gets somewhat challenging, but probably doable.

What to do?

Provide SHOW CREATE TABLE
Provide the main queries.
Provide the main transactions.
Don't do COUNT(*)
Don't do anything that requires hitting all 50M rows -- ask for help in reformulating such queries.

If this is really a financial institution, be sure to hire a security consultant.  You do not want the hassle you will get if the data is lost/stolen.


You could quite easily write a simply Perl or PHP script, running on a cron, to periodically check the MySQL server and alert you to any problems.

For example, I have the following Perl code in my setup:

$sql="show slave status";
$sth=$dbhread->prepare($sql);
$sth->execute();
($result)=$sth->fetchrow_hashref();
$sth->finish();

if($result->{'Slave_SQL_Running'} ne "Yes") {
   &SendAlert("Slave SQL not running");
}

if($result->{'Slave_IO_Running'} ne "Yes") {
   &SendAlert("Slave IO not running");
}

if($result->{'Seconds_Behind_Master'} > $threshold) {
   &SendAlert("Seconds Behind Master is too high");
}

Obviously &SendAlert can be set up to do what ever you like (email/SMS alerting)


Retrieve XML of large plan from plan cache

For 157MB in Tools -> Options -> Query Results -> SQL Server -> Results to Grid screen you will need to set Maximum Characters Retrieved -> XML data to unlimited as below.


Digital content eCommerce schema advice?

Get yourself a good data model pattern book.
Accept the fact that most relationships are many-to-many.
Read up on table inheritance and the Party/Party Role model.

Some starting points:

An order has many line items, which belong to a product
A product will probably belong to multiple categories
A product will likely have more than one artist


Transaction log growing out of control on random times

If you are running in simple recovery mode, then the only transaction log data the full and differential backups will contain are for the very narrow time while the backup itself is in process, so looking at that is not going to help you here.

To see real-time activity, you can instead run the SQL Profiler.

Before I did that, though, I'd try a few other ways to identify open transactions:

Download and use sp_WhoIsActive, look at the open_tran_count and perhaps the start_time columns.
Run a query to show explicit and implicit open transactions.

Something like:

SELECT st.session_id, DB_NAME(sp.dbid) as dbname, at.name,
    at.transaction_begin_time, sp.last_batch, sp.status,
    RTRIM(sp.hostname) as hostname, RTRIM(sp.program_name) as program,
    RTRIM(sp.cmd) as command, RTRIM(sp.loginame) as loginname
FROM sys.dm_tran_active_transactions at
INNER JOIN sys.dm_tran_session_transactions st ON at.transaction_id = st.transaction_id
INNER JOIN sys.sysprocesses sp ON st.session_id = sp.spid
ORDER BY transaction_begin_time

If you see something that has been running for a very long time, try doing a kill and see if the log clears up.


Implicit conversion warning despite persisted computed column vs. performance hit from UDF in persisted computed column definition

However, execution plans still show an implicit conversion warning even though the conversion happens when the table is updated, not when it is read from.

Persisting a computed column does not guarantee that the persisted value will be used. The optimizer makes a cost-based decision between using the persisted value and computing the expression afresh, though there are also other factors in play. Simplifying, the process looks like this:

The computed column reference is always expanded to its definition before query compilation and optimization begins. This is very similar to the way view references are handled (unless indexed and referenced with a NOEXPAND hint). The expansion provides the greatest opportunity for orthogonal simplifications and optimizations to be applied.
Depending on the query and the code path taken through the query optimizer, a cardinality estimate may be requested for an expression in the expanded computed column definition. Deriving this cardinality estimation may result in a plan-affecting convert warning being added to an internal warning list.
The expanded computed column expression may be matched back to a persisted column or index later in the compilation and optimization process. Any plan-affecting convert warning added previously is not tracked and removed when this substitution occurs.

Like most warnings, the plan-affecting convert warning is opportunistic and informational. It is opportunistic in that it is only added if the optimizer follows a code path that attempts to compute cardinality on a suitable expression. This is similar to 'missing index suggestions', which are only added if the optimizer attempts to match to an 'ideal' index definition that is not found. To put it another way: neither of these facilities are based on exhaustive analysis.

  I tried using a UDF in the computed column definition to get rid of the warning

This 'works' because the optimizer cannot expand a scalar UDF into its definition before optimization. The UDF is a 'black box' with guessed cardinality and horrible runtime performance (close to the cost of running a complete separate query per function invocation). Without expansion, a cardinality estimation that might generate the warning cannot occur.

  Is there a reason to consider the warning anything other than a bug?

The warning indicates that the convert may affect plan quality because a cardinality estimation was performed on a problematic expression. The complexity of compilation and optimization is such that it is impossible to say if the cardinality estimate will affect the final quality of the plan or not, even if the computed column does end up being resolved to a persisted value or index.

So, the warning is generally useful because it indicates that internal optimization decisions may have been adversely impacted by the convert. I would always check a plan with this warning for inaccurate cardinality estimations, and any resulting performance or resource usage implications.

A final aside: the SQL Server version is not mentioned in the question, but for 2012 and later, TRY_CAST or TRY_CONVERT are more robust ways of handling this sort of requirement. This facility will not generally affect convert warnings one way or the other though.


How do I measure performance of a single sql execution in oracle

Generally not possible. You will have to time the execution of a new instance of that statement.

If you are lucky and the statement was running long enough to be sampled by ASH it should however show up in dba_hist_active_sess_history or for very recent invocations V$ACTIVE_SESSION_HISTORY.

It should show CPU time separate from DB time.


Why Postgresql 9.2 word boundary not working!

I'd suspect you have standard_conforming_strings set to OFF on the 9.1 instance (meaning it was changed explicitly, since the default is ON since 9.1), and the opposite in 9.2.

Demo:

test=> select version();
                                                   version
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

 test=> show standard_conforming_strings ;
 standard_conforming_strings
-----------------------------
 on
(1 row)

test=> WITH x(v) AS (VALUES ('text ACT txt'), ('ACT tests'), ('tests ACT'))
test-> SELECT v FROM x WHERE v ~* '\yACT\y';
      v
--------------
 text ACT txt
 ACT tests
 tests ACT
(3 rows)

Now with it to OFF:

test=> set standard_conforming_strings=off;
SET

test=> WITH x(v) AS (VALUES ('text ACT txt'), ('ACT tests'), ('tests ACT'))
SELECT v FROM x WHERE v ~* '\yACT\y';
WARNING:  nonstandard use of escape in a string literal
LINE 2: SELECT v FROM x WHERE v ~* '\yACT\y';
                                   ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
 v
---
(0 rows)

You'd need also to set escape_string_warning to OFF to mute the warning message and get a final result that plausibly matches your question:

test=> set escape_string_warning to off;
SET
test=> WITH x(v) AS (VALUES ('text ACT txt'), ('ACT tests'), ('tests ACT'))
SELECT v FROM x WHERE v ~* '\yACT\y';
 v
---
(0 rows)


From version 11g, you can use a virtual computed column:

create table StatusCode (
  StatusTable  varchar2(20) not null,
  StatusValue  varchar2(20) not null,
  primary key (statustable, statusvalue)
) ;

create table Thing (
  ThingId integer not null primary key,
  TableName varchar2(20) generated always as ('Thing') virtual,
  ThingStatus varchar2(20) not null,
  foreign key
          (TableName, ThingStatus)
      references StatusCode
          (StatusTable, StatusValue)
) ;


SQL Server - Create Additional Database File for New Schema

You can create a new filegroup, make it DEFUALT, create all schema objects and switch DEFAULT filegroup back. If at any later point you would need to add objects to that schema, you can explicitly specify filegroup through ON filegroup clause for each object in create statement or temporarily switch DEFUALT filegroup.

Alternatively you can explicitly specify ON filegroup for each object of this client's schema.


How to get first and last value by same item in sequence, with possibility of the same item occurring again

WITH
cte1 AS ( SELECT *,
                 CASE WHEN name = LAG(name) OVER (ORDER BY id)
                      THEN 0
                      ELSE 1
                      END the_same
          FROM test ),
cte2 AS ( SELECT *, SUM(the_same) OVER (ORDER BY id) group_num
          FROM cte1 )
SELECT DISTINCT
       name,
       FIRST_VALUE(value) OVER (PARTITION BY group_num ORDER BY id) value_1st,
       FIRST_VALUE(value) OVER (PARTITION BY group_num ORDER BY id DESC) value_last
FROM cte2;

fiddle


your foreign key definitions are wrong, you switched table and column names.

this should work:

CREATE TABLE IF NOT EXISTS apuntats(
    id INT NOT NULL AUTO_INCREMENT,
    idcurs INT NOT NULL,
    idusuari INT NOT NULL,
    coneixements TEXT,
    motiu TEXT,
    FOREIGN KEY(idcurs)
        REFERENCES cursos(id)
        ON DELETE CASCADE,
    FOREIGN KEY(idusuari)
        REFERENCES usuaris(id)
        ON DELETE CASCADE,
    PRIMARY KEY(id)
);


The problem was that I was using PGADMIN to see the constraints and references.

When I used the \d+ command, I was able to see the other references better.

So, There was another table that had the same data, and I had to delete them first.

Once I did it, I was able to delete the data I wanted.


Dynamic Select with error in where conditon

Whatever value of @P_SqlList the final WHERE sentence will be something like:

WHERE [SOME_FIELD] = @P_Input

due you are setting it here:

SET @P_Input='@P_Input'

To solve this, add @P_Inputto the parameters list. Or, build the whole filter outside the dynamic query and pass it as a single value:

WHERE ' + @Filter

This form allow you to build a more complicated WHERE sentence.


How to write a script to push the server time to out?

Unfortunately, you're trying to solve a problem with an inherently broken design: relying on a user-editable client-side variable to determine object version is never going to work perfectly.

Forcing time synchronization on the domain may help, but clients can still be out-of-sync, either because of clock drift, or because the user is a local admin and therefore has permissions to change the clock manually at any time, to any time.

Normally a server-side value such as an ever-increasing integer row version number is used for concurrency control instead of a client-side-generated value.

Also, if you're recording auditing information, as much of it as possible (particularly time values) should be generated by the server for both security and synchronization reasons.


Using * to enforce the need to update both tables together

I'm not entirely sure what he is trying to do either. This query is inserting into MAIN rows from IPR_CIP that don't already exist in MAIN. It depends on the two tables always having the same number of columns and corresponding columns having compatible data types. It would be much better to explicitly list the columns you are updating in MAIN and the columns you are selecting from IPR_CIP. The subquery is redundant and can be moved up into the parent. Here's the TSQL code I would use.

INSERT INTO MAIN (col1, col2, col3, ... , coln)
SELECT (col1, col2, col3, ..., coln)
FROM IPR_CIP
LEFT OUTER JOIN MAIN On IPR_CIP.CASE_NUM = MAIN.CASE_NUM
WHERE MAIN.CASE_NUM IS NULL


How to use variables inside a select (SQL Server)?

Derived table

SELECT
    EMail,
    hashbytes('SHA1', EMail) AS HashedEmail
FROM
    (
    SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
    FROM sometable
   ) foo

or CTE:

;WITH cEMail AS
(
    SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
    FROM sometable
)
SELECT
    EMail,
    hashbytes('SHA1', EMail) AS HashedEmail
FROM
    cEMail

However, in this case I'd consider using a computed column with the hash precalculated


Rights to execute stored procedure, but not read underlying tables directly

Sure, you just grant rights to those users to the stored procedure, not to the underlying table.

CREATE PROCEDURE dbo.test -- always use schema prefix!
AS
BEGIN
  SET NOCOUNT ON;

  SELECT t1.col1, t2.col2, ...
    FROM dbo.table1 AS t1
    INNER JOIN dbo.table2 AS t2
    ON ... ;
END
GO

GRANT EXEC ON dbo.test TO [user1], [user2], ...;

Here is a completely contained example that you can test. First, create a database (hopefully floob does not conflict with any database you already have):

CREATE DATABASE floob;
GO

Now, create two logins:

CREATE LOGIN floob1 WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
CREATE LOGIN floob2 WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO

And create database users to go with them:

USE floob;
GO
CREATE USER floob1 FROM LOGIN floob1;
CREATE USER floob2 FROM LOGIN floob2;
GO

Now, create a table that they don't have access to by default, and a stored procedure that queries it:

CREATE TABLE dbo.whatwhat(id INT);
INSERT dbo.whatwhat(id) VALUES(1);
GO
CREATE PROCEDURE dbo.select_whatwhat
AS
BEGIN
  SET NOCOUNT ON;

  SELECT id FROM dbo.whatwhat;
END
GO

Now, grant execute rights to floob1 only:

GRANT EXEC ON dbo.select_whatwhat TO floob1;
GO

Now, try to execute the procedure and select from the table as floob1:

EXECUTE AS USER = 'floob1';
GO
EXEC dbo.select_whatwhat; -- works
GO
SELECT id FROM dbo.whatwhat; -- fails with error
GO
REVERT;

You get a successful query resulting from executing the stored procedure, but an error message when trying to query the table directly, because you haven't been explicitly granted access to do so.

  Msg 229, Level 14, State 5
  The SELECT permission was denied on the object 'whatwhat', database 'floob', schema 'dbo'.

Now, try as floob2, who hasn't been given explicit access to execute the stored procedure, either:

EXECUTE AS USER = 'floob2';
GO
EXEC dbo.select_whatwhat; -- fails with error
GO
SELECT id FROM dbo.whatwhat; -- fails with error
GO
REVERT;

This time you get the same error on the SELECT, but also this error on the EXEC:

  Msg 229, Level 14, State 5, Procedure select_whatwhat
  The EXECUTE permission was denied on the object 'select_whatwhat', database 'floob', schema 'dbo'.

Don't forget to clean up:

USE master;
GO
DROP DATABASE floob;
DROP LOGIN floob1;
DROP LOGIN floob2;
GO

Regarding your comment:

  Is it possible to grant the users by using drop and click in Sql server instead of using T-SQL code?

It is, but that doesn't sound like a very good idea. Here is the process: you right-click the stored procedure in Object Explorer, hit Properties, move to the Permissions tab, hit Search..., type your username, hit OK, then check the checkbox where EXECUTE on the left meets GRANT on the top, and click OK. Now repeat for every stored procedure, and multiply that by 20 (all your users). Yeah, that sounds a lot easier than writing a script. Here is a better way to generate the script you need in one step, with a single copy, paste and execute instead of who knows how many points and clicks you'll need to do this interactively through the GUI:

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

;WITH users AS
(SELECT name = 'user1' UNION ALL SELECT 'user2' ... UNION ALL SELECT 'user20')
SELECT @xml += N'GRANT EXEC ON ' + QUOTENAME(s.name) +
  '.' + QUOTENAME(p.name) + ' TO ' + QUOTENAME(u.name) + ';
'
FROM users AS u CROSS JOIN sys.procedures AS p
  INNER JOIN sys.schemas AS s ON s.[schema_id] = p.[schema_id];

PRINT @sql;
-- EXEC sp_executesql @sql;

You could also add sys.database_principals to the mix to make sure you're getting the right set of users with no typos.


According to this information on the ICAEW site, the things to be copied are fairly well defined. This is based in the UK.  See:

http://www.icaew.com/en/members/practice-resources/news/money-laundering-what-do-you-do-with-passports

Basically they say that identification: "For the purpose of due diligence evidence under the Money Laundering Regulations you may copy the personal details page only.

HM Stationery Office (HMSO) guidance also makes it clear that passport photocopies must be in black and white only, so that they cannot be mistaken for an actual passport page.

Furthermore, you are only allowed to copy the details page."

The HMSO (Her Majesty's Stationary Office) has similar details.

http://www.hmso.gov.uk/copyright/guidance/gn_20(old%20format).htm

So it seems that if you preserve a black and white copy of the personal details page, you are complying.  Of course, the one who makes the copy (or scan) should verify personally against the original passport that the bearer is indeed the person described.

The verification process may be more stringent depending on where you are.


PostgreSQL won't run off NTFS on Linux. Format the drive as ext4.

Frankly, putting a database on a USB key on a tablespace isn't usually a great idea. The tablespace is unreadable without the rest of the database, and the rest of the database is useless without the tablespace. So you're creating a fragile system with two points of failure.

It's a much better idea to instead move the whole PostgreSQL data directory onto the USB drive.


That your indexing is slowing some queries but not others has a lot to do with the way spatial indexes work:

The spatial index is broken into 4 levels which are broken into grids.  With each level you will be able to specify a density which of low, medium or high corresponding to 16, 64 or 256 cells respectively. Then within each cell these are then subdivided into the next lower level with its setting.

All of this means that depending on your settings and your data distribution you may need to adjust the density at different levels to get proper index performance.  This may also mean that you need to balance your performance of your main queries of this data against your edge cases.

Take a look at this Microsoft TechNet library article for a more detailed explanation:

Spatial Indexing Overview


Finding reason behind high write IOPS on MySQL (RDS) server

Shown RDS statistics are too global and broad to provide an answer to the question "Who is to blame for the increased load?".

There are serveral ways to profile your queries, but as you are using RDS with MySQL 5.6, I would recommend you to use PERFORMANCE_SCHEMA to do it easily, as you won't need external software (some of which is not fully RDS-compatible). Here you have a video with an introduction to query profiling, with examples like IOPS and temporary table creation monitoring by query pattern, user and table. For a more specific guide (specially for configuration of metrics), you can have a look at the official manual and the sys schema documentation.

For a quick check you can also have a look at your SHOW GLOBAL STATUS like 'com\_%'; and SHOW GLOBAL STATUS like 'Hand%'; at time interval to see if you have an increase on the number of SQL queries per unit of time or on the number of engine row operations per unit of time (the first one does not seem abnormal according to the graphs shown).

Your goal is to detect the offending queries first so that you can modify the SQL, your data structure, the MySQL configuration and/or anything peripheral (like if your RDS instance is still good enough) accordingly. An increase on Write IOPS normally may mean extra SQL load (obviously), but also many other things, such as: too many temporary tables or worse query plans being executed due to a change on the query optimiser plan or on your data cardinality/size. It is critical to identify the underlying cause first before taking any action.


DB2 - Using stored procedure variable in a "CREATE TABLE as (..)" Statement

The use of an intermediary variable is not necessary here. Db2 regsitry variables, including CURRENT TIMESTAMP or its standard-compliant variant CURRENT_TIMESTAMP, are set once within the scope of a statement referencing them. This means that in a statement like yours:
SELECT
    x,
    y,
    CURRENT_TIMESTAMP as t_change
FROM
    bar

all rows will have the same value of t_change as of approximately the beginning of the statement execution.


How does merge replication work with SQL Server CE

SQL Server Compact has a built-in change tracking mechanism (similar to SQL Server Change Tracking), and when a database file is merge replicated, a number of system tables are added to the database, see http://blogs.msdn.com/b/sqlservercompact/archive/2007/04/18/merge-replication-system-tables.aspx - conflicts are only handled on ther server side. In addition to the tables mentioned in the blog post, some _sys columns are also added to each merge replicated table.


Select all overlapping ranges from starting range

A straightforward recursive solution splits the task in two parts:

Find the earliest start date by following the chain of overlapping ranges
Find the latest end date by following the chain of overlapping ranges

Details follow:

1. Follow the chain of overlaps leading to the earliest start date

Find the starting range
Find the overlapping range with the latest earlier start date than current
Go to step 2

Example code:

DECLARE @STARTDATE DATETIME = '2018-08-24 12:00:00';
DECLARE @ENDDATE DATETIME = '2018-08-31 12:00:00';

WITH MinStart AS
(
    -- Starting range
    SELECT TOP (1)
        D.ID,
        D.STARTDATE
    FROM dbo.DATA1 AS D
    WHERE
        D.STARTDATE >= @STARTDATE
        AND D.ENDDATE <= @ENDDATE
    ORDER BY
        D.ID ASC

    UNION ALL

    SELECT
        P1.ID,
        P1.STARTDATE
    FROM
    (
        -- Overlapping ranges with an earlier start date
        -- Numbered starting with the highest start date
        -- Tie-break dates using ID
        SELECT
            RN = ROW_NUMBER() OVER (
                ORDER BY D.STARTDATE DESC, D.ID ASC),
            D.ID,
            D.STARTDATE
        FROM MinStart AS R
        JOIN dbo.DATA1 AS D
            ON D.ENDDATE >= R.STARTDATE
            AND D.STARTDATE < R.STARTDATE
    ) AS P1
    WHERE
        -- Highest start date only
        P1.RN = 1
)
SELECT
    MS.ID,
    MS.STARTDATE
FROM MinStart AS MS
OPTION (MAXRECURSION 0);

2. Follow the chain of overlaps leading to the latest end date

Find the starting range
Find the overlapping range with the earliest later end date than current
Go to step 2

Example code:

DECLARE @STARTDATE DATETIME = '2018-08-24 12:00:00';
DECLARE @ENDDATE DATETIME = '2018-08-31 12:00:00';

WITH MaxEnd AS
(
    -- Starting range
    SELECT TOP (1)
        D.ID,
        D.ENDDATE
    FROM dbo.DATA1 AS D
    WHERE
        D.STARTDATE >= @STARTDATE
        AND D.ENDDATE <= @ENDDATE
    ORDER BY
        D.ID ASC

    UNION ALL

    SELECT
        P1.ID,
        P1.ENDDATE
    FROM
    (
        -- Overlapping ranges with a later end date
        -- Numbered starting with the lowest end date
        -- Tie-break dates using ID
        SELECT
            RN = ROW_NUMBER() OVER (
                ORDER BY D.ENDDATE ASC, D.ID ASC),
            D.ID,
            D.ENDDATE
        FROM MaxEnd AS R
        JOIN dbo.DATA1 AS D
            ON D.ENDDATE > R.ENDDATE
            AND D.STARTDATE < R.ENDDATE
    ) AS P1
    WHERE
        -- Lowest end date only
        P1.RN = 1
)
SELECT
    ME.ID,
    ME.ENDDATE
FROM MaxEnd AS ME
OPTION (MAXRECURSION 0);

With suitable indexing, both queries use an execution plan like:

The results given the sample data provided are:

╔════╦═════════════════════════╗
║ ID ║        STARTDATE        ║
╠════╬═════════════════════════╣
║  3 ║ 2018-08-24 12:00:00.000 ║
║  6 ║ 2018-08-23 12:00:00.000 ║
║  2 ║ 2018-08-22 12:00:00.000 ║
╚════╩═════════════════════════╝
╔════╦═════════════════════════╗
║ ID ║         ENDDATE         ║
╠════╬═════════════════════════╣
║  3 ║ 2018-08-29 12:00:00.000 ║
║  6 ║ 2018-08-30 12:00:00.000 ║
║  4 ║ 2018-09-02 12:00:00.000 ║
╚════╩═════════════════════════╝

So IDs 2, 3, 4, and 6 were used. The final range is 2018-08-22 12:00:00.000 (lowest start date found) to 2018-09-02 12:00:00.000 (highest end date found).

Note: The code above includes a tie-break on dates (using the ID column) due to duplicates in the sample data. This may, or may not, be required to solve the real problem.

The indexes used were:

CREATE TABLE dbo.DATA1
(
  ID integer PRIMARY KEY,
  STARTDATE datetime NOT NULL,
  ENDDATE datetime NOT NULL,
);

CREATE UNIQUE INDEX i1 ON dbo.DATA1 (STARTDATE DESC, ID ASC) INCLUDE (ENDDATE);
CREATE UNIQUE INDEX i2 ON dbo.DATA1 (ENDDATE ASC, ID DESC) INCLUDE (STARTDATE);

Demo: db<>fiddle

Note that truly optimal indexing for these types of queries can be difficult. Even so, the approach above often performs well for many practical tasks in this area.

Until proper support for interval queries and predicates is added to SQL Server, higher performance solutions require significant extra work. See:

Interval Queries in SQL Server Part 4 by Dejan Sarka
Interval Queries in SQL Server by Itzik Ben-Gan


Your myisam_max_sort_file_size is probably too small.

Perhaps your tmpdir hasn't got the elbow room.

First, kill the repair right now.

Add these two lines to my.cnf

[mysqld]
myisam_max_sort_file_size = 80G
tmpdir=/var/lib/mysql (or some other path or mount point)

Restart mysql and do the REPAIR TABLE again.

Give it a Try !!!


what does || (Parallel sign) mean in DB2 Sql?


Show values from list that are NOT returned by query

You can use the VALUES clause to create a "constant table":

Let's assume this is your table:

CREATE TABLE some_table
(
    appropriate_column TEXT
) ;

populated with:

INSERT INTO
    some_table (appropriate_column)
VALUES
     ('something_b'),
     ('something_d') ;

This is how you would make the query you're looking for:

SELECT
    v
FROM
    (
    VALUES
        ('something_a'),
        ('something_b'),
        ('something_c'),
        ('something_d')
    ) AS vs(v)
WHERE
    v NOT IN
    (SELECT
        appropriate_column
    FROM
        some_table
    ) ;

You can check everything at dbfiddle


Is accelerated database recovery available in SQL Server 2019?

The documentation page link in your question is specific to Azure SQL Database. Note Azure in the url:

https://docs.microsoft.com/en-US/azure/sql-database/sql-database-accelerated-database-recovery.

The Accelerated database recovery page in the general documentation shows ADR is included in SQL 2019 and Azure SQL Database.


Postgresql: Streaming Replication - determining wal sender process client port

The "wal sender process" is a client during streaming replication.  It connects to the standby servers port (5432?).  In order to allow access, you need to allow traffic from the senders (master) IP, to the standby's IP and port.  A simple firewall rule.  Normally these rules don't bother with the clients port number.


Would SQL Replication prove to be a FASTER option than downloading compressed DB files every time from servers at different geographic locations?

There are couple of options --

I would not suggest to go with replication as you are making schema changes and it will require to reinitialize the whole replication with a snapshot.

Logshipping with standby option:

Cheap and effective. Works great and less overhead of maintenance.
Things to look out :

If an adhoc log backup is taken, it will break the log chain.
If you take differential backup then make sure you take full backups with COPY_ONLY option.
Your secondary will be readonly - use the with standby option, so you can read from your secondary. See my answer here for more details.

SSIS with incremental load

Refer to SSIS Design Pattern - Incremental Loads from Andy Leonard

If you are using Enterprise edition then I would suggest you to look into AlwaysON Availability groups.


Unable to access sql database with login and password

The error indicates that the SQL Server Service does not have permissions to the folder that the mdf file is located in.

Since SQL Server 2008 the account running SQL Server has by default a minimal permissions set. If you want it to access files in non default directories, you need to explicitly grant access to that location.

In SQL Server 2012 things are complicated even more by Service Isolation.

In 2008 you can just grant access to the folder to the account is setup to start the sql server service. You can find that account in the SQL Server Service Manager.

In SQL 2012 you need to grant access to an account that looks like this: NT SERVICE\MSSQLSERVER or like NT SERVICE\MSSQL$InstanceName.

In your case, as you are using a default instance of SQL Server 2012, granting NT SERVICE\MSSQLSERVER to have write access to the path that contains db.mdf should make things work.


How to fetch file path from a folder through PL/SQL

Mr. Kyte wrote about accessing os-file-information utilizing the preprocessor directive a while ago. Perhaps this is what you are after.

query the operating system


Is PostgreSQL replication production ready?

Production ready?

Yes, it's production-ready and widely used. Heroku followers are based on PostgreSQL's built-in async replication for example, as are AWS RDS standbys and read replicas. Streaming replication is used almost universally with PostgreSQL.

Setup of replication isn't exactly lovely, but tools like repmgr help somewhat with that, and it's improving slowly with each major release. The ability for pg_basebackup to take a copy of the system using streaming replication (and do so from another standby) is a big help.

In general, a feature simply won't be released in PostgreSQL until it's production ready. Bugs happen, like in any software, but they're typically fixed shortly after they're identified. Really major new features sometimes have bugs and issues discovered after the .0 release, but if so fixing them is a high priority; bugs aren't just left around.

I'm not aware of any serious issues with streaming replication - sync or async - nor have I seen any reported for quite a while. They were less stable than Pg's usual standard in the .0 releases of the major versions they were introduced in, but both matured quickly and are thoroughly production-ready.

(Update: There was a specific bug in the new 9.3 version prior to 9.3.4 that did cause replication issues in some cases; users of 9.3 should update to 9.3.4 immediately. Older versions are not affected.)

The only caveat I want to mention is a a minor detail with synchronous replication: If you commit on the master, then cancel the query after it commits while it waits for the replica to confirm, it's treated as commited on the master even before it's replicated. You get the same effect by restarting the master while waiting for the replica to reply. In practice this is an irrelevance, but it's about the only problem I can think of.

Compare to MySQL?

Pg's native replication is quite different to MySQL's.

MySQL uses logical replication where it sends the logical changes made to table data, table structure, etc, and the replica applies those changes.

PostgreSQL's replication is lower level (in 9.5 and below; future versions may also add logical replication). It sends the blocks that changed in the tables. It's simpler, easier to get right, and imposes lower load on the replica server, but consumes more network bandwidth and requires more storage on the master to hold not-yet-replicated changes. It's best configured to use streaming replication with WAL-archiving fallback, making it more complex to configure than MySQL's. It replicates low-level changes like VACUUM activity, not just tuple changes, keeping the on-disk state of the replica the same as that of the master. It is incapable of replicating only one database; the whole system must be replicated, which can be frustrating if you have one big, high churn and unimportant database and one small, low-churn and vital database.

All in all, it depends on what you want to do with it.

I view PostgreSQL's replication as considerably better for replicas used for backup, high availability and disaster recovery. Particularly so when combined with point in time recovery (PITR).

On the other hand, it's not as good for read-only reporting replicas because the need to delay the application of replicated data while running long transactions means that you need to either let it cancel very long running queries or fall greatly behind the master, consuming more disk space on the master and forcing it to work harder to keep up.

There's ongoing work to enable logical replication in PostgreSQL, where the logical changes to table structure, table contents, etc are replicated, rather than their on-disk state. Pg's catalog design and support for user-defined everything makes this quite a complex task. Some of the groundwork has been put in place for 9.4, but full logical replication is unlikely to be usable before 9.6 or later.


Unable to connect to Oracle 11g server remotely using SQL developer ORA-12520

A couple of things can trigger this error. My guess is this the first thing below.
Max Process Limit Reached
You may have have exceeded the maximum number of processes. (see here and here.)
This query will show you the current utilization, max utilization, initial allocation, and limit for Processes:  select * from v$resource_limit where resource_name in ('processes','sessions');. You will need to run the query from the server, since you can't get in remotely. If Max Utilization is at or near your limit value, this may be the problem.
You can see what the process limit is by logging into Oracle as sysdba from the server. show parameter processes will display something that says processes and has an integer value. You can extend this value by issuing this command: alter system set processes=XXX scope=spfile; set XXX to a higher value. Oracle recommends 200 for a "small" system and 400 for a "medium" system. You will have to shutdown and restart Oracle to enact the change.
Oracle bug
Oracle mentions a Bug, bug number 13831723, in Oracle 11.1. Your information seems to indicate you're running 11.2, so this shouldn't apply. But if it does, then you'll need to install a patch from Oracle. Log into Oracle support and search for Oracle DOC ID 1566704.1. They say it is fixed in 11.1.1.7.0 and up.


Unique relationship attribute for every pair of entities

You would create a unique constraint on exam (StudentId, CourseId) this would usually be achieved with a unique index on that table that covered the two columns.
Here's a quick demo in MySql