Methods of Linking:
1. Logically just left join doctors table and get doctor ID during patients data insert as showed below
2. Physically add foreign key constraint and during insert left join doctors table and get doctor ID during patients data insert

 /* Supposed your OriginalTable is with following structure */

    create table #OriginalTable
    (
    ID int,
    patfname varchar(100),
    patlname varchar(100),
    pataddress1 varchar(100),
    pataddress2 varchar(100),
    patcity varchar(40),
    patstate varchar(4),
    Patzipcode varchar(20),
    Docfname varchar(100),
    Doclname varchar(100),
    Docaddress1 varchar(100),
    Docaddress2 varchar(100),
    Doccity varchar(40),
    Docstate varchar(4),
    Doczipcode varchar(20),
    DocNPI varchar(40)
    )

    /*Dumpy data insert*/
    insert into #OriginalTable
    select '1', 'pat1fname','pat1lname','pat1address1','pat1address2','Pat1City','p1S','pt1zip'
    ,'doc1firstname','doc1lastname','doc1address1' ,'doc1address2' ,'doc1City','d1S','dt1zip','000001'
    union all
    select '2', 'pat2fname','pat2lname','pat2address2','pat2address2','Pat2City','p2S','pt2zip'
    ,'doc2firstname','doc2lastname','doc2address1' ,'doc2address2' ,'doc2City','d2S','dt2zip','000002'
    union all
    select '3', 'pat3fname','pat3lname','pat3address3','pat3address2','Pat3City','p3S','pt3zip'
    ,'doc1firstname','doc1lastname','doc1address1' ,'doc1address2' ,'doc1City','d1S','dt1zip','000001'
    union all
    select '4', 'pat4fname','pat4lname','pat4address4','pat4address2','Pat4City','p4S','pt4zip'
    ,'doc1firstname','doc1lastname','doc1address1' ,'doc1address2' ,'doc1City','d1S','dt1zip','000001'

    /* create patients table with all required fields*/
    create table Patients
    (
    ID int identity(1,1),
    FirstName Varchar(100),
    LastName Varchar(100),
    Adress1 Varchar(100),
    Adress2 Varchar(100),
    City Varchar(50),
    [State] Varchar(4),
    ZipCode varchar(20),
    DoctorID Int
    )

    /* create Doctors table with all required fields*/
    create table Doctors
    (
    ID int identity(1,1),
    FirstName Varchar(100),
    LastName Varchar(100),
    Address1 Varchar(100),
    Address2 Varchar(100),
    City Varchar(50),
    [State] Varchar(4),
    ZipCode varchar(20),
    NPI varchar(40)
    )

    /*Remove existing data from patients and doctors table*/
    delete from Doctors
    delete from patients

    /* insert all distinct doctors data from Orginal table */
    insert into Doctors( FirstName ,LastName ,Address1 ,Address2 ,City ,[State] ,ZipCode ,NPI )
    select distinct docfname,doclname,docAddress1,docAddress2, docCity, docState, docZipcode, docNPI
    from #OriginalTable

    /* Set Identity insert On to add OLD unique ID values as they are in #OriginalTable */

    SET IDENTITY_INSERT Patients ON

    insert into  Patients( ID , FirstName ,LastName ,Adress1 ,Adress2 ,City ,[State] ,ZipCode ,DoctorID )
    select old.ID ,old.patfname ,old.patlname ,old.pataddress1 ,old.pataddress2 ,old.patcity ,old.patstate ,old.Patzipcode ,doc.ID
    from #OriginalTable old left outer join Doctors doc
    on old.Docfname=doc.FirstName
    and old.Doclname=doc.LastName
    and old.Docaddress1=doc.Address1
    and old.Docaddress2=doc.Address2
    and old.Doccity=doc.City
    and old.Docstate=doc.[State]
    and old.Doczipcode=doc.ZipCode
    and old.DocNPI=doc.NPI

    /*Set Identity insert off */
    SET IDENTITY_INSERT Patients OFF

    /* Query table to verify inserted data*/
    select * from #OriginalTable
    select * from Patients
    select * from Doctors

    DROP Table #OriginalTable


You need to restore/recover your database. If you can't then yes the code is buried in the system table spaces. But it won't be easy looking through the data file with a hex editor trying to find the code. You should either try to restore the database or just rewrite the code. Do you have a backup of your database? Do you have all of the data files, control files, online redo files? If you don't have a backup and your database is in an inconsistent state, then you can probably alter database recover until cancel and then point Oracle to your online redo logs to see if it can find what it needs.

Otherwise you might be out of luck. What is in your alert log for your database?


How to Import mongodb database from development Environment to local env by using Commmand?

The mongoimport tool is only intended for importing simple data in text formats (JSON, CSV, TSV).

To backup and restore a full database (including index definitions & collection options) typical approaches are to use either:

Filesystem snapshots
mongodump / mongorestore

The MongoDB manual has a section on Backup and Recovery describing supported approaches for different types of deployments (standalone, replica set, sharded cluster).


I have always implemented a Soft Delete by including two additional columns in my tables, one for status and one for delete date.

My table structure would be similar to the following:

create table myTable
(
  id int,
  name varchar(50),
  IsDeleted bit,
  DeletedDate datetime
);

The IsDeleted column has a default value of no and the DeletedDate is not populated until the row is actually marked as deleted.  I include a date column in the event I ever want to know when a row was soft deleted.

Then when you query your data, you will include this column in the WHERE clause:

select id, name
from myTable
where IsDeleted = 0

Note: You will want to include an index on the IsDeleted column.


I would say using a local server would be better. At least that way you know the hardware and can simulate it as close to the production server as possible. With a cloud-based server you have no idea what the underlying hardware is, and with some services like Azure you will have to take the 3x write policy into account, different network connectivity, volume bursts for other activity within your "cloud", etc. So your performance picture will be quite different even if the hardware might be similar.


SQL Server - Adding non-nullable column to existing table - SSDT Publishing

I'll share how I have done this in the past.  It is designed to solve the
specific limitation of pre-deployment scripts that you call out in your second
point:

  Adding the column in a pre-deployment script will fail the publish when it
  automatically tries to create the same column, a second time (even if the pre-
  deployment script is written to be idempotent)

Why pre-deployment scripts don't work for this

When you deploy an SSDT project, the way it stitches thing together is like
this (a bit simplified, but in general):

Do the "schema compare" between the source (dacpac file) and the target
(database)
Generate a deployment script based on the results of that compare
Process any pre-deployment scripts in the dacpac (doing token replacement,
etc.) and insert the contents into the beginning of the deployment script
Do the same for post-deployment scripts, appending to the end of the
deployment script

When a new column exists in the dacpac and not in the target database, step #2
will generate code to add that column.  So if the pre-deployment script adds
this column, the main part of the script will fail (because it assumes the
column doesn't exist, based on the results of the schema compare in step #1)

Solution: pre-SSDT script

Martin Smith mentioned this option in a comment, and it's the solution that
has worked best for me so far:

  We use premodel scripts in our deplyment pipeline. This is not something
  that is part of SSDT but a step that runs before the dacfx publish. So in
  this case the premodel script could add the column with the desired values
  and make it not null and by the time the publish happens it is already in
  the state expected by SSDT so it doesnt have anything to do. I'm still yet
  to find much use for predeploy scripts. – Martin Smith
  Jun 1 at 21:45

The steps to implement this solution in general are:

Create a script in the SSDT project to hold your "pre-SSDT" T-SQL code

Depending on how your deployment process works, code in these files
should probably be idempotent

Make sure to set this script to "Build Action=None" and "Copy to Output
Directory=Copy always"

the "copy always" option is especially important, as the deployment
process needs to be able to find this script in your
deployment artifacts

In your deployment process, locate and run this script (or scripts)
before the SSDT schema compare occurs
Once that script has been executed successfully, you can engage DacServices
/ DacFx / whatever to complete your deployment as usual

In the end, this allows you to add the column using whatever custom code you
like, populated using complex business logic, in the pre-SSDT script.

You also add the column definition in the SSDT project (so source control
still matches the real life state of the database).  But when the schema
compare runs, it sees no changes related to that column (because you've
already deployed it).

Other uses of pre-SSDT

I often find when testing deployments that SSDT performs a "table rebuild"
operation* when it's completely unnecessary.  This is where a new table is a
created with the updated schema, all data is copied to that table, the old
table is dropped, and the new table is renamed to replace the old table.

This can lead to massive transaction log file growth and other problems if
the table is large. If I notice that a schema change is causing this, I'll
instead make the change myself in pre-SSDT (which is usually a simple
ALTER TABLE statement) and avoid the table rebuild.

Is this a good idea?

I think so.  If you read Critiquing two different approaches to
delivering databases: Migrations vs state by Alex Yates, this is
essentially combining the two approaches a bit.  SSDT is state based, but we
incorporate a migration step (before SSDT) to handle some of the more complex
scenarios that SSDT just has no way of dealing with in a general way.

In doing some searching while writing this answer, this is actually a very
common approach discussed in the SSDT user community once you know what to
search for.  I've seen it called:

pre-compare
pre-model
pre-DAC
pre-SSDT

Etc.  Here's a great article that covers a lot of the points that I mentoned
above:

Pre-Compare & Pre-Deployment Scripts to SSDT

And one from Red Gate (in the #4 – Changing from system type to user
defined type section) that also refers to this as pre-compare:

How to Fix Ten SSDT Deployment Snags, With or Without ReadyRoll

So what's the point of pre-deployment scripts?

Martin points out that he hasn't found "much use for predeploy scripts."  I
tend to feel the same way.  But there are scenarios where they can be useful.

One example a coworker pointed out to me was storing some data in a temp table
to be used in the post deployment script (say you're moving a column from one
table to another).

*The table rebuild looks like this, which is horrifying, right?

GO
PRINT N'Starting rebuilding table [dbo].[MyTable]...';

GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_MyTable] (
    [Id] BIGINT IDENTITY (1, 1) NOT NULL,
    -- etc, other columns
);

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[MyTable])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_MyTable] ON;
        INSERT INTO [dbo].[tmp_ms_xx_MyTable] ([Id], ...)
        SELECT   [Id],
                 -- etc, other columns
        FROM     [dbo].[MyTable]
        ORDER BY [Id] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_MyTable] OFF;
    END

DROP TABLE [dbo].[MyTable];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_MyTable]', N'MyTable';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


Cleaning up orphaned metadata in information_schema

The way that I found to "solve" this problem is fairly annoying, but there is a script that handles it.

Essentially, you need the ibdata1 and ib_logfile* files to go away (they contain the mappings of foreign keys, among other things). The only safe way to do this is to export all of your databases, stop mysql, remove the files, start mysql, and then import the files.

The script that helps solve this problem is https://github.com/uberhacker/shrink-ibdata1, even though the stated purpose of this script is different, it does solve the problem.


I would recommend using this trigger template where possible.  change it to suit your needs, of course.
It uses an IF UPDATE(column) to short circuit if the DML doesn't reference the column the trigger cares about.  It also uses the EXCEPT clause for what I've found the fastest way to compare the INSERTED and DELETED tables.  Just add more columns to the lists to get more comparisons without needing to worry about NULL/not null values.
Just adding an IF UPDATE block would solve your issue immediately with the least change to the trigger logic.
As for why your statement hung; the INSERTED and DELETED triggers are heaps without indexes.  This makes joining them on the primary key an expensive operation, much less the comparison of the relevant column.  You can make it as fast as possible (as the below has done) but your best bet to avoid it seemingly hanging would be to batch your statements.  Do 5,000 rows at a time to avoid unnecessarily large strain on your tempdb.
A random additional note; we have gone a few steps further, we don't have audit triggers like this, but we use INSTEAD OF INSERT/DELETE to enforce the use of soft-delete in our application.  However, when we do need to hard delete records, we have another short circuit that checks context info for a special binary value, then just doesn't fire off for that value.  You could implement something like that as well.  Leave the trigger in place for the application, but special statements like huge table maintenance could just issue the proper context info and the trigger would ignore the statement.  You would then need to handle the logic for what the trigger was supposed to do on your own, but triggers are brute force.
ALTER TRIGGER trgMyTriggerName ON dbo.MyTableName
AFTER UPDATE
AS
BEGIN

    /** Inside triggers, UPDATE(column) checks to make sure that the update statement
        actually referenced the column.  NOTE that this does not mean the value actually
        changed, just that the update (or whatever DML) statement referenced the column.

        If you use an ORM like entity framework then the whole row gets updated all the time
        anyways, regardless of if the value changed.  still, this is a good short circuit to
        prevent unnecessary logic from being executed.
        **/
    IF UPDATE(MyAuditColumn)
    BEGIN

        ;WITH CTE_Changed AS
            (
            SELECT I.PrimaryKeyID, I.AuditColumn
            FROM INSERTED AS I
            EXCEPT
            SELECT D.PrimaryKeyID, D.AuditColumn
            FROM DELETED AS D
            )
        INSERT INTO audit.MyTableName
            (transaction_type, transaction_user, primaryKeyId, someColumn)
        SELECT 'U', SYSTEM_USER, C.PrimaryKeyID, C.AuditColumn
        FROM CTE_Changed AS C

    END

END


MySQL- Performance implications of changing a column collation from latin1_swedish_ci to latin1_bin

Not significant performance difference.

It will require rebuilding any indexes involving that column.  That will take time, but it is a onetime issue.

Any *_ci collation is slightly more complex than a *_bin collation, at least when doing comparisons or sorting.

If you choose to undo the change, be explicit about the collation.  Your proposed ALTER hopes that the 'default' will be used.

An ALTER .. COLUMN will rebuild the necessary indexes.


Azure Database Size Same Before and After Deleting Most of the Rows

I think this may be answered here:
https://stackoverflow.com/questions/19677883/how-to-shrink-azure-sql-server-db-18mb-of-data-charged-for-5gb-of-server-space

To summarize
You need to reindex, then wait after you have done the deletes.

I don't think azure counts the size of logs in the space used.

Also see here to check what is using the space (I have summarized below)
http://blogs.msdn.com/b/dilkushp/archive/2013/07/28/fragmentation-in-sql-azure.aspx
Once you have done the reindex to check out various sizes run the following script.

-- Size of each table in MB
select obj.name, sum(reserved_page_count) * 8.0 / 1024 as "size in MB" from sys.dm_db_partition_stats part, sys.objects obj where part.object_id = obj.object_id group by obj.name
Order By sum(reserved_page_count) desc

-- Total Database size in Meg
select sum(reserved_page_count) * 8.0 / 1024 as "size in MB" from sys.dm_db_partition_stats


Is Data Warehousing possible in MySQL and PostgresSQL

You can certainly use MySQL or PostgreSQL for this requirement using Python as your database access language. I've never used Python cubes so I can't speak to that.

I would recommend that you use PostgreSQL - it has windowing functions and CTEs (common table expressions). It also supports CHECK CONSTRAINTs and a full range of set operators.

MySQL is reasonably good for read heavy OLTP database loads but
little else.
PostgreSQL is superior for analytical work (DW - data warehousing, or
OLAP) while at the same time being an excellent all-rounder in the
OLTP arena.


As far as I know, it is not possible to apply condition on OUTPUT directly, but you can achieve what you need using output of the MERGE:

INSERT INTO @tblOut(bar)
SELECT foo
FROM (
    MERGE INTO @tblA t
    USING (VALUES (1),(2),(3)) s(foo)
    ON 1 = 0
    WHEN NOT MATCHED THEN
        INSERT (foo)
        VALUES (s.foo)
    OUTPUT inserted.foo
) m
WHERE m.foo = 1;


Would it make security sense to split up tables into different databases and or servers?

Not knowing what system you are using I can only make a couple of general observations.  If you have multiple tables in a database then the administrator of the database will have access to all of them but otherwise you can control the table level security.  If you have multiple databases on a server then the administrator of the server will have access to all of them but otherwise you can control the security as you desire.  The best reason to split the data up between servers is to limit the access of an administrator on the server.

At least in MS SQL Server (the DB I know the most about) you can locate tables across multiple databases on the same instance with no performance issues.  Once you are dealing with multiple instances on the same server you have a distributed transaction and may or may not take a performance hit.  If the instances are on separate servers in the same location then you add in performance degradation caused by the LAN. And of course if you have servers in different locations you are dealing with WAN performance.


As you can see, the process that takes most time is "query end". There is an interlocking problem when multiple threads want to write the file at the same time, this way the log will be flushed every second:

innodb_flush_log_at_trx_commit = 0

in the /etc/my.cnf file

Also, you should try Optimize InnoDB as explained above for future query improvement.


Round robin vs Demand Parallelism partitioning

Should I always use round robin instead

Only if you find it produces "better" results for your particular workload, or for particular queries within that workload.

In general, I would say no.  My personal experience is that Demand (D) partitioning tends to have fewer problems than Round Robin (RR). The particular plan you shared does not seem to have any of the more common features that can cause performance problems with RR, but it is tough to assess at a distance.

  why does round robin starts processing rows a lot faster than demand partitioning

If I were to hazard a guess, I would say the D partitioning threads are checking and yielding the processor more frequently. A thread that uses less of its quantum on average (by checking and yielding more often) is a good scheduling citizen, but it may be punished for that good behaviour (before SQL Server 2016). The screenshot you shared does seem to show the D plan getting less CPU time than the concurrent RR execution.

D pulls single rows across the exchange whereas RR pushes whole packets - and yield checks occur at exchanges (among many other places). A very detailed analysis would be required to confirm this (or any other) theory.

  can I do more optimization for demand partitioning?

The plan seems reasonable enough from what I can see.

It is interesting that the screenshot shows a merge join whereas the uploaded plans all use hash join. Trying a co-located merge join was one of the first suggestions that came to mind - as explained in my article Improving Partitioned Table Join Performance.

You could also check on thread distribution across schedulers and perhaps experiment with different thread placement strategies. I would certainly be checking that the D plan was allocating threads as optimally as the RR version (e.g. no concurrent same-branch threads sharing the same scheduler).

Perhaps also look at presenting the demand rows in a different order. What you have now may be based on a sensible expectation, but that's not to say it is necessarily optimal.

In any case, I would be very wary of drawing general conclusions about D vs RR based on this one example.

  I noticed demand creates one extra thread compared to round robin

This is an illusion due to a timing difference. In the RR plan, the Index Scan completes very quickly, with its rows stuffed into packets on the exchange. The thread running the scan thus terminates quickly.

In the D plan, individual rows are pulled on demand across the exchange and fully processed before a new row is pulled across. So, the Constant Scan thread hangs around for longer.

Both plans have two concurrent branches, with DOP (16) threads reserved for each. Both use 17 threads as reported in the ThreadStat showplan element (16 for the main branch, 1 for the serial index scan/constant scan).


100M rows is not scary.
Partitioning -- No.  It is unlikely to add any performance benefit.  If, however, you need to purge "old" data, there might be a use for partitioning.
If recordId is an INDEX of SubRecords -- Good.
If recordId is the first column in the PRIMARY KEY of SubRecords -- better.
Show us SHOW CREATE TABLE for further advice.
Your 3 SELECTs would run faster if you JOINed the 3 tables in a single SELECT.
Clustering
A slight improvement would be to improve the "clustering" of the rows that you fetch at the same time.  For SubRecords,
ALTER TABLE SubRecords
    DROP PRIMARY KEY(id),
    ADD PRIMARY KEY(recordId, id),
    ADD INDEX(id);

That way, when you get the several SubRecords for one Record, they will be located next to each other.  This is because the PRIMARY KEY (in InnoDB) is 'clustered' with the data.  The INDEX(id) is to keep AUTO_INCREMENT happy.
This change may not show any noticeable improvement until the dataset is bigger than RAM.
A similar thing can be done with SubSubRecords.


Transactional Replication with Always On via Sql Server 2014

Yes it can be done like normal.

32/64 bit can replicate to each other (you wouldn't think so but they do and Microsoft has documented it on MSDN).

Also you can have an AG as a subscriber but you must use the name of the listener when setting it up. There's an MSDN article for this as well.


I would recommend adding another table, StreetLightGrouping. This table should have two columns, StreetLightGroupId and StreetLightId.  All the StreetLights under one StreetLightGroupId are part of the same group.

Also, if a StreetLight can belong to a maximum of one group, a unique constraint can be established on the StreetLightId column.


Why does this PostGIS query take so long to run?

Your query is needlessly complicated and can be untangled to this 100 % equivalent one:
SELECT gid, id, ST_MakeValid(geom) AS geom
FROM   schema.table_polygons
WHERE  geom IS NOT NULL;

Neither original nor this one remove any duplicates (except by possibly removing some with geom IS NULL, but I doubt that was your intention).
Possible misunderstanding
There is a common misunderstanding concerning output column names in the WHERE clause - where you can only reference input column names. So, geom in:
WHERE geom IN ...

references the original input column not the output column (the result of ST_MakeValid(geom)).
Related:

PostgreSQL Where count condition

If your actual intention was to only keep rows where the result of ST_MakeValid(geom) is equal to any currently existing geom entry:
SELECT gid, id, ST_MakeValid(t.geom) AS geom
FROM   schema.table_polygons t
WHERE  EXISTS (
   SELECT 1
   FROM   schema.table_polygons
   WHERE  geom = ST_MakeValid(t.geom)
   );

I added a table alias and table qualification to clarify what's what.
The index you mention is only going to help with this variant.
If your actual intention was to merge duplicate rows after fixing geom values:
SELECT DISTINCT ON (ST_MakeValid(t.geom))
       gid, id, ST_MakeValid(geom) AS geom
FROM   schema.table_polygons t
ORDER  BY ST_MakeValid(t.geom), gid, id;

You did not define how to break ties and which row to keep from a set of dupes. I keep the row with the smallest gid from each set. And the smallest id from remaining dupes. Details:

Select first row in each GROUP BY group?

Aside: Calling a schema "schema" is like naming your son "son" and hoping for the best that no more sons shall arrive. In other words: don't.


How to install Oracle sqlplus, rman utilities on local Windows workstation?

First, download the full Oracle Database Client package that matches the version of your Oracle database.  If your machine is 64-bit, download the 64-bit package. Note: you must download the full client, not the "Instant Client." 2020-04-29 UPDATE: There is now an Instant Client optional package that includes sqlplus (but not rman - it needs the full client).

Unzip the package and run the setup program with administrator privileges.  Choose 'Custom' installation.

A few screens will follow.  Choose the language of installation.  When it asks what account you want services to run under, just choose "Windows Built-in Account," since we won't be installing any services anyway.

It will ask you where you want your "Oracle Base" directory to be.  You can see what I chose in the following screenshot.  The "Oracle Home" location field should automatically set itself according to what you choose for your Oracle base directory.  I recommend not messing with the software location; just stick with the default.

On the next screen we choose what components to install.  Choose "Oracle Database Utilities," "SQL*Plus," and "Oracle Net."

Finish the remaining screens to complete the installation.  SQLplus and rman should now be available to you on your path and command line.

Before you can use sqlplus to connect to your database, you must create and configure a tnsnames.ora file.  That is outside the scope of this question, but once the file is created, it should be placed in its default location, which is <<ORACLE_HOME>>\network\admin folder.  If you cannot determine the location of your <<ORACLE_HOME>> folder, you can find it in your Windows registry at [HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraClient12Home1] in a string value called ORACLE_HOME.

You should now be able to open a command prompt and type sqlplus username@oraclesid (where oraclesid is the sid of your Oracle database as specified in your tnsnames.ora file) and connect from your workstation.  rman should work from the command line as well.


How do I use a different domain and username in a SQL Server BACKUP LOG command?

Powershell won't help, it is just a wrapper for the BACKUP command. Your problem is that it is indeed the database engine that produces the backup file, and there is no option for impersonation here. So, you really need to sort out that service account, I'm afraid.

(There used to be something called a nullsession-share. I doubt it still exists, and basically you open that share to the whole world. Not recommended, I'm just mentioning here to recommend against it.)

Come to think of it, how about the computer account? I'm not sure if that will help, but if you grant privileges to that share for the computer account where your SQL Server is?


what happens when InnoDB logspace is too small

The InnoDB Logfile is not related to your Binlog.
The InnoDB log is used in case of crash recovery. So you may loose some transactions in case of a crash. In addition to that a small InnoDB log can impact your overal MySQL performance because MySQL has to flush the data very often to disk if you have a small iblog.

In short: there is no direct impact impact when this error happens, but if you encounter a mysql crash you may loose some transactions.
In addition to that you can optimized your IO performance if you increase the size because the logfiles are sequential IO which is much faster than the random IO if you have to actually write your data to the tablespace.


Can SQL Server Change Data capture (CDC) log rows to an external DB?

CDC is just the component that exposes the changes. It is strictly tied to the host database where the changes occur. CDC is useless without an application you provide which consumes the changes. This application of yours can do anything with the changes, including storing them anywhere you fancy. You just have to implement this application.


MySQL - change all tables' ENGINE from MyISAM to InnoDB with a single query

My strong advice is not to try and do this on a running database! MyISAM isn't transactional in any case, so any operation that spans many tables can't be performed in this manner (it can in InnoDB - your reason for wanting to change?). You will have to either dump the db and modify the text file or iterate through the tables.

MySQL is moving firmly away from MyISAM - it's being deprecated. Not even the system tables are MyISAM anymore (MySQL >= 8.xx).

BTW, please always include the version of MySQL that you are using - it can be very important - see below!

Therefore, I propose the following solution:

Step 1:

Take a backup of your database using mysqldump - you should be doing this on a regular basis anyway - here is a reference (there are many!):

mysqldump database_name > database_name.sql

of if you have multiple databases on the same server:

mysqldump -u root -p --databases database_name_a database_name_b ... > databases_a_b.sql

Step 2:

Using the text editor of your choice, go through the file and search for the word ENGINE and replace each occurrence of ENGINE=MyISAM with ENGINE=InnoDB.

If you're on *nix, then something like this will do the trick:

sed -e 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > new_file.sql

Do not do this for every occurrence of MyISAM in the entire MySQL server! If you are using a version of MySQL which still has MyISAM system tables - this is very risky - from here - MySQL 5.7. Will not apply for later versions which have moved the system tables to InnoDB anyway:

  Warning Do not convert MySQL system tables in the mysql database from
  MyISAM to InnoDB tables. This is an unsupported operation. If you do
  this, MySQL does not restart until you restore the old system tables
  from a backup or regenerate them by reinitializing the data directory
  (see Section 2.10.1, “Initializing the Data Directory”).

So, do not do the change for any tables in the mysql, information_schema or performance_schema databases - only in the databases that you've created yourself.

Step 3:

Restore from this backup and you're good to go! You should drop your original database and recreate an empty schema (see reference above) of the same name and then:

mysqld  database_name < new_file.sql

At this point, you may wish to implement FOREIGN KEYS - one of the many good reasons to upgrade to InnoDB! Be careful - MyISAM doesn't have DRI (Declarative Referential Integrity) so there may be orphaned records in your existing system - you might want to run tests to check on this?

Running system alternative:

If you're unable to bring down the database, then a solution like this (iterating through every table in the schema) might be helpful (adapted from here and here):

CREATE PROCEDURE UpdateTables ()
BEGIN
  DECLARE Finished BOOL DEFAULT FALSE;
  DECLARE TableName VARCHAR(64);

  DECLARE TablesCursor CURSOR FOR
    SELECT c1.TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c1
    WHERE c1.TABLE_SCHEMA = 'my_schema';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;

  OPEN TablesCursor;

  MainLoop: LOOP
    FETCH TablesCursor INTO TableName;
    IF Finished THEN
      LEAVE MainLoop;
    END IF;

    SET @queryText = CONCAT('ALTER TABLE ', TableName , ' ENGINE=InnoDB');  -- may or may not need backtics
    PREPARE updateQuery FROM @queryText;
    EXECUTE updateQuery;
    DEALLOCATE PREPARE updateQuery;
  END LOOP;

  CLOSE TablesCursor;
END

Note: this is untested - please use with caution and do your own tests before committing to a definitive solution. I would also advise doing something like this when the system load is as light as possible!

Alternatives to the alternative (again, not tested):

If you're a PHP person, this might be suitable.

Another (interesting) approach is this one by Shlomi Noach - a big hitter in the MySQL world.


Fixed this by changing the quotes from "schema_name" to '"schema_name"'

See: pg_dump doesn't dump datascheme with uppercase letters in

...where Tom Lane replied:

  The problem is that there are two levels of quoting needed: one for the shell and one for SQL.  As you wrote it, the double quotes are stripped off by the shell and so pg_dump gets Schema, which is case-folded per normal SQL rules.  So you need to write:

pg_dump -n '"Schema"' -U postgres database

  Now, the shell eats the single quotes and passes "Schema" to pg_dump.


How should an object that can reference an object of multiple types be modeled in a relational schema?

If my memory serves  me well, 'One to either' is called Polymorphic Association. Usually, it can be resolved by using a common parent table (index definitions are skipped for simplicity):

PortfolioParent (id int not null PRIMARY KEY,
portfolio_type char(1) not null,
CONSTRAINT UQ_PORTFOLIO_PARENT UNIQUE (id,portfolio_type),
-- unique constraint may seem redundant, but it's required so other tables can refer to
-- (id,portfolio_type)
CONSTRAINT CHK_PORTFOLIO_PARENT_TYPE CHECK portfolio_type IN ('P','S')) --'P' - portfolio,'S'-subportfolio

);

Portfolio(id int not null PRIMARY KEY,
parent_id int not null,
portfolio_type char(1) not null,
--portfolio attributes
CONSTRAINT CHK_PORTFOLIO_TYPE CHECK (portfolio_type ='P'),
CONSRTRAINT FK_PORTFOLIO_PORT_PARENT FOREIGN KEY (parent_id,portfolio_type)
  REFERENCES PortfolioParent(id,portfolio_type));

SubPortfolio (id int not null PRIMARY KEY,
parent_id int not null,
portfolio_type char(1) not null,
-- sub-portfolio attributes, portfolio_id
CONSTRAINT CHK_SUBPORTFOLIO_TYPE CHECK (portfolio_type ='S'),
CONSRTRAINT FK_SUBPORTFOLIO_PORT_PARENT FOREIGN KEY (parent_id,portfolio_type)
   REFERENCES  PortfolioParent(id,portfolio_type));

Project(id int not null PRIMARY KEY,
parent_id int not null,
--project attributes
CONSTRAINT FK_Project_PARENT FOREIGN KEY(parent_id) REFERENCES PortfolioParent(id));


What configurations affect the speed of DDL statements in PostgreSQL

In what sense is database integrity not important? Even if you don't care about integrity, if the production database doesn't accept entries in table baz because of a foreign key constraint against table bar, do you want that constraint emulated in development? Or do you want to find out you forgot about it when you move to production? Personally, I would not sacrifice integrity in development.

Official toolkit, pg_dump, pg_restore

I would personally use pg_dump and pg_restore. They're configurable, user-friendly and very easy to use. That said, there are faster options but I have never had a need for them for my use cases. The official tools do not support binary dumps, but they also work between versions fairly well.  pg_restore -1 will wrap the whole thing in a transaction and disable WAL.

Raw Data with TABLESPACE

If you're willing to make raw backups you can get far greater speed. You can do this,

Create a a folder somewhere like /data/dbs chown it to be owned the user/group postgres

mkdir -p /data/dbs;
chown postgres:postgres /data/dbs;

Make this into a TABLESPACE

CREATE TABLESPACE dbspace LOCATION '/data/dbs';

Create a database there..

CREATE DATABASE foobar WITH TABLESPACE = dbspace;

Now you can connect to -d foobar. And do some stuff.

CREATE TABLE foo ( idfoo serial PRIMARY KEY, name text );
CREATE TABLE baz ( idbaz serial, idfoo int REFERENCES foo );
INSERT INTO foo (name) VALUES ('foo'), ('bar');
INSERT INTO baz (idfoo) SELECT idfoo FROM foo;

Now you can disconnect.. you can copy the data-directory to backup. this process can be scripted easily. For me,

the data directory is PG_9.5_201510051 for you it may different.
the command to stop PostgreSQL runs though /usr/sbin/service (I use Ubuntu)

So for me, it looks like this.

sudo service postgresql stop
sudo cp -pR PG_9.5_201510051/ backup
sudo service postgresql start

Reconnect.

DROP TABLE foo CASCADE;

Then when disconnect and again stop the service we can restore the backup (also easily scripted),

service postgresql stop;
cp -pR backup/ PG_9.5_201510051/

Upon reconnecting, foo is magically back and restored. You can't get faster than this...

Not fast enough?

If this isn't fast enough. In the event your database has TB of data or something crazy.

You can use rsync to only synchronize files changed.
You can likely use ZFS snapshots or BTRFS snapshots on a block device mounted to the data directory. YMMV.


How do I get mysql to start for the first time?

To setup mysql database server, follow step by step of below given URL

http://www.cyberciti.biz/faq/how-to-install-mysql-under-rhel/


Data discretization based on rank position

You can use the NTILE() function:

select id, value,
       ntile(2) over (order by value) as normalized
from table_name ;

The 2 means that the values should be split into two buckets, after they are ordered according to the over clause: (ORDER BY value).


Top x distinct values ordered by date for each uid

WITH cte AS ( SELECT *,
                     DENSE_RANK() OVER ( PARTITION BY interviewee_uid
                                         ORDER BY interviewer_uid) iid,
                     ROW_NUMBER() OVER ( PARTITION BY interviewee_uid,
                                                      interviewer_uid
                                         ORDER BY idate DESC) did
              FROM datatablename )
SELECT *
FROM cte
WHERE did = 1
  AND iid <= 3;

fiddle

UPDATE.

Previous query contains logical error.

Edited variant:

WITH
cte1 AS ( SELECT interview_id, interviewee_uid, interviewer_uid, idate,
                 ROW_NUMBER() OVER (PARTITION BY interviewee_uid,
                                                 interviewer_uid
                                    ORDER BY idate DESC) did
          FROM test ),
cte2 AS ( SELECT interview_id, interviewee_uid, interviewer_uid, idate,
                 ROW_NUMBER() OVER (PARTITION BY interviewee_uid
                                    ORDER BY idate DESC) did
          FROM cte1
          WHERE did = 1
)
SELECT interview_id, interviewee_uid, interviewer_uid, idate
FROM cte2
WHERE did <= 3;

fiddle (with demonstration that previous query is wrong).


SQL Server: Why would an ALTER TABLE to add nullable column run forever?

Yes, some other session was blocking. A few min later, this command executed instantly.


What is the undo pass that SQL Server does during a RESTORE WITH STANDBY operation?

why are those operations on the log file if they haven't been
  committed in the first place

All operations, committed or uncommitted, are written in the log. A commit ensures that all log entries are made durable (flushed to disk), but nothing prevents uncommitted entries from being flushed before that (either because a log block fills or because another transaction commits, thus forcing the flush for every transaction). A rollback must analyze all the transaction entries and generate compensating actions (for every insert do a delete, for every delete do an insert, for every update do an update that reverses the data back). These compensating actions are, of course, logged.

When a database is recovered it must rollback any transaction that is not committed in the log. It must as such analyze the log, figure out the uncommitted transactions, and then generate compensating actions for any all actions belonging to uncommitted transactions. Online recovery will write the compensation actions into the log itself. Standby recovery will write the compensating actions into an alternative stream, thus allowing for further log to be applied form a 'master' source later (this how log shipping standby read only access works).

Before asking any clarification question, please read ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging.


Table from one of the SELECTS cannot be used in field list

To make the two queries compatible for a UNION fill the column list of the second one with NULLs to match the length of that of the first one. To be sure the types of the first column match, you could explicitly cast() them to char. (Optional if they are already of compatible types.)

Also use UNION ALL because UNION (without ALL) tries to eliminate duplicates.

And I guess the ORDER BY was meant for the result FROM grafana.annotatio not for the whole result set. To make MySQL know that, put the second query in parenthesis.

SELECT cast(alert.name AS char),
       alert.new_state_date,
       alert.prev_state_date
       FROM grafana.alert
       WHERE alert.id = 1
UNION ALL
(SELECT cast(annotation.epoch AS char),
        NULL,
        NULL
        FROM grafana.annotation
        ORDER BY annotation.id DESC
        LIMIT 1);

If both queries return one row, you could also cross join them, to have the results side by side.

SELECT *
       FROM (SELECT alert.name,
                    alert.new_state_date,
                    alert.prev_state_date
                    FROM grafana.alert
                    WHERE alert.id = 1) x
            CROSS JOIN (SELECT annotation.epoch
                               FROM grafana.annotation
                               ORDER BY annotation.id DESC
                               LIMIT 1) y;


Decorating an existing database table with information that no one else cares about

Your instincts about indirect access are correct. Adding columns for clients to the table flies in the face of this and is a generally bad idea. From a mechanical point of view, you are taking on lots of DDL changes to a key table -- how much do you want to touch it? Will all the tracking map to something you can keep in a single row? Will someone stuffing data into this decide to dump a big nasty serialized XML object in there destroying performance? What happens when two of the apps want a field called "report_status"?

The correct course of action is to push these folks to separate tables or other data stores for their tracking. Then they can track what they need to track without interfering with the core data or with anyone else.


I'm using the SQL Server Agent to schedule even non-database tasks - is this a bad idea?

At my previous job I did exactly this, mostly because the jobs were all run from our central, primary cluster, which was the most visible server. I could see all of our scheduled tasks in one place instead of having to go check command line stuff on a bunch of servers.

While this is largely subjective (and it's going to be hard to derive a "correct" answer in this format), I don't think there's anything inherently wrong with this approach other than it becomes a single point of failure.

That might not be relevant if all of the tasks interact with, or depend on, the database server being up and the SQL Server services running (in our case, it did).

Oh, and you need to add in error handling for cases where the server where the task tries to run is not up - something you wouldn't have to do if the task were set up on that server.


Why `distinct` and `count` operations generate `"op": "command"` in system.profile

Originally, all database operation in MongoDB are encoded in the Wire Protocol. However, this would mean that creating a new command would involve:

Creating a new opcode in the wire protocol, and
Bumping the wire protocol version.

Another disadvantage with this approach is that the format of the opcodes cannot change without changing the wire protocol version. As more features are introduced, this situation will become unavoidable and potentially occur more often in the future.

To overcome this limitation, newer version of MongoDB started to migrate the existing wire protocol functions into its command form. For example, the command forms of the CRUD operations are detailed in the Query and Write Operation Commands documentation page.

This is the main reason why some operations are recorded as commands, and some are recorded as the actual wire protocol codes. Eventually, the command forms will be the preferred form in the future.

There is no single generic ticket that describes this move, but the DOCS-7489 ticket kind of describes the process for the drivers.

For a more in-depth technical details on upcoming driver changes with regard to the wire protocol, please see New Driver Features for MongoDB 3.6, specifically the section about OP_MSG.


Performance difference between Clustered and Non Clustered Index

Very good question as it is such a important concept. This is a big topic though and what I am going to show you is a simplification so you can understand the base concepts.

Firstly when you see clustered index think table. In SQL server if a table does not contain a clustered index it is a heap. Creating a clustered index on the table actually transforms the table into a b-tree type structure. Your clustered index IS your table it is not separate from the table

Ever wondered why you can only have one clustered index? Well if we had two clustered indexes we would need two copies of the table. It contains the data after all.

I am going to try and explain this by using a simple example.

NOTE: I created the table in this example and filled it with over 3 million random entries. Then ran the actual queries and pasted the execution plans here.

What you really need to grasp is O notation or operational efficiency. Let's assume you have the following table.

CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](100) NOT NULL,
[CustomerSurname] [varchar](100) NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
  , IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON
  , ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

So here we have  basic table with a clustered key on CustomerID (Primary key is clustered by default). Thus the table is arranged/ordered based on the primary key CustomerID. The intermediate levels will contain the CustomerID values. The data pages will contain the whole row thus it is the table row.

We will also create a non-clustered index on the CustomerName field. The following code will do it.

CREATE NONCLUSTERED INDEX [ix_Customer_CustomerName] ON [dbo].[Customer]
 (
[CustomerName] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
  , SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF
  , DROP_EXISTING = OFF, ONLINE = OFF
  , ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

So in this index you would find on the data pages/leaf level nodes a pointer to the intermediate levels in the clustered index. The index is arranged/ordered around the CustomerName field. Thus the intermediate level contains the CustomerName values and the leaf level will contain the pointer (these pointer values are actually the primary key values or CustomerID column).

Right so if we execute the following query:

SELECT * FROM Customer WHERE CustomerID = 1

SQL will probably read the clustered index via a seek operation. A seek operation is a binary search which is much more efficient than a scan which is a sequential search. So in our above example the index is read and by using a binary search SQL can eliminate the data that don't match the criteria we are looking for.  See attached screen shot for the query plan.

So the number of operations or O Notation for the seek operation is as follows:

Do binary search on clustered index by comparing the value searched for to the values in the intermediate level.
Return the values that match( remember since the clustered index has all the data in it can return all the columns from the index as it is the row data)

So it is two operations. However if we executed the following query:

SELECT * FROM Customer WHERE CustomerName ='John'

SQL will now use the non-clustered index on the CustomerName to do the search. However since this is a non-clustered index it does not contain the all of the data in the row.

So SQL will do the search on the intermediate levels to find the records that match then do a lookup using the values returned to do another search on the clustered index(aka the table) to retrieve the actual data. This sounds confusing I know but read on and all will become clear.

Since our non-clustered index only contains the CustomerName field(the indexed field values stored in the intermediate nodes) and the pointer to the data which is the CustomerID, the index has no record of the CustomerSurname. The CustomerSurname has to be fetched from the clustered index or table.

When running this query I get the following execution plan:

There are two important things for you to notice in the screen shot above

SQL is saying I have a missing index(the text in green). SQL is suggesting I create a index on CustomerName which includes CustomerID and CustomerSurname.
You will also see that 99% of the time of the query is spent on doing a key lookup on the primary key index/clustered index.

Why is SQL suggesting the index on CustomerName again? Well since the index contains only the CustomerID and the CustomerName SQL still has to find the CustomerSurname from the table/clustered indexes.

If we created the index and we included the CustomerSurname column in the index SQL would be able to satisfy the entire query by just reading the non-clustered index. This is why SQL is suggesting I change my non-clustered index.

Here you can see the extra operation SQL needs to do to get the CustomerSurname column from the clustered key

Thus the number of operations are as follows:

Do binary search on non-clustered index by comparing the value searched for to the values in the intermediate level
For nodes that match read the leaf level node which will contain the pointer for the data in the clustered index (the leaf level nodes will contain the primary key values by the way).
For each value returned do a read on the clustered index(the table) to get the row values out here we would read the CustomerSurname.
Return matching rows

That is 4 operations to get the values out. Twice the amount of operations needed compared to reading the clustered index. The show you that your clustered index is your most powerful index as it contains all the data.

So just to clarify one last point. Why do I say that the pointer in the non-clustered index is the primary key value? Well to demonstrate that the leaf level nodes of the non-clustered index contains the primary key value I change my query to:

SELECT CustomerID
FROM Customer
WHERE CustomerName='Jane'

In this query SQL can read the CustomerID from the non-clustered index. It does not need to do a lookup on the clustered index. This you can see by the execution plan which looks like this.

Notice the difference between this query and the previous query. There is no lookup. SQL can find all the data in the non-clustered index

Hopefully you can begin to understand that clustered index is the table and non-clustered indexes DON'T contain all the data. Indexing will speed up selects due to the fact that binary searches can be done but only clustered indexes contain all the data. So a search on a non-clustered index will almost always result in values being loaded from the clustered index. These extra operations make non-clustered indexes less efficient than a clustered index.

Hope this clears things up. If anything does not make sense please post a comment and I will try clarify. It is rather late here and my brain is feeling a wee bit flat. Time for a red bull.


NEWSEQUENTIALID() resets sequence on server restart

No way to seed it. That whole "globally unique" part gets in the way.

the bigger question here is "do you truly require this?"

GUIDs are big, GUIDs -even with newsequentialid, can lead to index fragmentation. If you need GUIDa where do you need them? They typically do not make great clustered index candidates. For both of these reasons and more.


Azure PostgreSQL pg_dump impact on backup "chain"

If Azure is anything like PostgreSQL, an online physical backup won't interfere with pg_dump and vice versa (except that both will generate I/O load).

pg_dump uses a REPEATABLE READ transaction which guarantees a consistent backup.


My question is when you see a list of Waits how do you relate them back to a proc/query that is causing them?

You can't, generally speaking. You mention in a comment on another answer that your monitoring tool shows you a jump in wait stats. If your monitoring tool can't correlate resource spikes to what was running at the time, you have a bad monitoring tool.

Tools that  make that easy include

Sentry One Performance Advisor
Quest Spotlight

If you're already using one of those and having a hard time, you should schedule a call with a vendor representative to walk you through the product.

You also can't use most scripts to figure out what happened later. You have to catch things while they're happening. Some easy ways to do that (for free):

Logging Activity Using sp_WhoIsActive – Take 2
First Responder Kit Power BI Dashboard

If you're on SQL Server 2017 or higher, and you have Query Store enabled, it will track aggregated waits stats.

It's up to you to keep track of these things, though.

Best of luck!


SQL Server using Parallel Plan for Cost less than 5

This can be confusing when you're first looking at parallel plans. The thing to keep in mind is that the cost based optimizer picks what it estimates will be the cheaper query to execute. It's not always right, but hey, it's not rocket science.

As a simple example, here are two count queries. One is allowed to go parallel, the other is forced to run serially.

SELECT COUNT(*)
FROM dbo.Posts AS p;

SELECT COUNT(*)
FROM dbo.Posts AS p
OPTION(MAXDOP 1);

Their query plans have the information we need:

The serial plan costs 121 query bucks, and the parallel plan costs 69.


I think you're reading the wrong things. The general consensus among SQL Server professionals is to use a fixed-size autogrowth in megabytes due to the reasons you've mentioned. I would ignore TechNet in this case.

Here is a relevant article on the topic, though it's targeted at answering one of the findings in sp_Blitz.


postgres+JDBC: fire trigger with non autocommit transaction

Your trigger is fired for each statement not for each row. In statement level triggers you can not access the new and old records.

You need to change your create trigger to create a row-level trigger, rather than a statement level trigger:

CREATE TRIGGER my_trigger
  AFTER INSERT ON table2
  FOR EACH ROW -- this is the change
EXECUTE PROCEDURE my_procedure();

Triggers are always executed in the same transaction as the triggering statement.


Think of it this way:

the database is the set of physical files, on disk. It's a completely passive "thing". It doesn't "do" anything on its own - it's just data.
the instance is the software/processes (with its memory) that manages the database. It's the active part. It's what clients connect to, it's what processes SQL, reads data, updates it, maintains the transaction logs, etc.

When you start with nomount, you start the instance (start a few processes, create the memory areas), but don't actually go look at the database. You'll have something in memory ready to mount a database, or do the few maintenance things you can do in that state (like creating a database), but that's all. The database is completely inaccessible at that point.

When you mount, the control file (which contains a description of the database's files) is read, but the database is still not open. I.e. you can't access the data itself.

Only you finally open the database can clients start interacting with the database.

Things get a bit more complicated with RAC, but the principle is the same: the database is only the set of files. The instances are what allow you to interact with the database.

Shutdown progresses in the opposite direction. For a normal, non-RAC shutdown, the instance flushes all the in-memory data that's not yet saved in the database, and closes the database files. Clients can no longer access the data.
Then the instance dismounts the database, closing the control files. At this point you're left with pretty much the same thing as with an instance in nomount - a set of processes and memory that can't really do much.
The final step is the actual shutdown: the memory areas are released back to the OS and the processes die, which is the end of that instance.

The database doesn't ever do anything. The instances are the only active parts of the system.

For a more in-depth view, see Oracle Database Instance. It has an overview of the startup and shutdown sequence, and other essential information.


how to find backup history of SSAS database

There is no dynamic management view(DMV) available to get this backup history details about SSAS database. You can get these details by using below method:

Implement this backup process with SSIS either using XMLA or SSIS task
Use to implement the logging and completion of backup task time to a log table.

Unlike a relational database backup file, an SSAS backup file can contain only one backup. In other words, you can’t create backup sets. If you want to retain multiple backups, you must create multiple files. Also, SSAS doesn’t provide options for differential backups or log file backups.

By default, two check boxes are selected in the Backup Database window -  Apply compression and Encrypt backup file. When the Apply compression option is selected, SSAS compresses the backup file, which saves disk space at the expense of higher CPU utilization during the backup operation. When the Encrypt backup file option is selected, SSAS accepts a user-supplied password, which it uses to encrypt the backup file. Note that, unlike the password for a SQL Server relational database backup set, this password prevents the reading of the backup data by other means. If you choose to encrypt SSAS backups, make sure that you keep a record of the password in a safe and separate location. More details are here, here and here.

Script for XMLA and using ascmd.exe is mentioned at this link.

Hope above helps.


Which is faster, SUM(CASE) or CTE PIVOT?

Are there examples where PIVOT is slower?

This is unlikely in simple cases. As Itzik Ben-Gan notes in his SQL Server Pro article, Pivoting Data when looking at the plan for a PIVOT query (emphasis added):

  Figure 3 shows the plan for the PIVOT query. As you can see, this plan is very similar to that of the standard solution—so much so that if you look at the properties of the Aggregate operator, under Defined Values, you’ll find that SQL Server constructed CASE expressions behind the scenes:

  …
  [Expr1022] = Scalar Operator(SUM(CASE WHEN [InsideTSQL2008].[Sales].[Orders].[shipcity]=N'Barcelona' THEN [InsideTSQL2008].[Sales].[Orders].[freight] ELSE NULL END))
  …

  With this in mind, you shouldn’t expect the solution that’s based on the PIVOT operator to perform better than the standard solution. The main benefit in the PIVOT operator at the moment is that it’s less verbose.

For more advanced pivoting requirements that the (non-standard) PIVOT syntax does not directly support, workarounds are needed. These may or may not lead to worse performance compared with CASE, depending on various factors including the skill level of the implementor.

Examples of these problematic cases are covered in Itzik's article, and also well explained in Robert Sheldon's Simple Talk article, Questions About Pivoting Data in SQL Server You Were Too Shy to Ask.

My experience has been that PIVOT and Agg(CASE... generate extremely similar plans with extremely close performance characteristics when both are written optimally. My usual advice is to write queries using whatever syntax feels most natural to you, and to only try rewrites if performance is not acceptable.

Internals

The SQL Server query processor does have a built-in Pivot logical operator (LogOp_Pivot), so it is maybe not quite correct to say that SQL Server rewrites pivots to aggregates and case expressions, at least if we are talking about parsing and compilation activities that take place prior to cost-based optimization (trivial plans are not available for pivot queries).

On the other hand, it is true that the only way the optimizer can implement a query tree containing LogOp_Pivot is via the exploration rule ExpandPivot. This rule expands LogOp_Pivot into a normal grouped aggregate (LogOp_GbAgg) with associated scalar expressions. When this rule is disabled, pivot queries fail to compile.

In practice then, we can say that pivots are always (eventually) 'rewritten' as aggregates and scalar expressions before an executable plan can be produced.

Anyway, the result of the rewrite to LogOp_GbAgg is converted to the physical operators needed for an executable plan by the regular group-by aggregate implementation rules GbAggToHS (hash) or GbAggToStrm (stream).

As a side note, the reason 'manual pivots' (aggregates on case expressions) have an extra Compute Scalar below the aggregate is that the case expressions are pushed toward the leaf level of the query tree during Project Normalization (an early stage of compilation, before cost-based optimization).

Queries that use the PIVOT syntax do not have this because the expressions are not created until ExpandPivot runs during cost-based optimization. At the (earlier) time Project Normalization runs, the query tree still has LogOp_Pivot elements, so there are no projections to push down, and the case expressions typically end up inside the hash or stream aggregate.

There is typically no advantage in avoiding the Compute Scalar, since from SQL Server 2005 onward, expression evaluation is normally deferred until the result is required by a later operator. In this case, evaluation of the case expressions is deferred until the aggregate (hash or stream) requires it.


Recursivly get a Tree Via self joined table

Recursive CTEs are notoriously hard to learn.  Take a look at this fiddle to play with a working model.

The 2nd query in the CTE, or Common Table Expression, needs to reference the first query.  This is where the "recursive" name comes from in CTE.

My fiddle has a single table, where each cat can only have a single parent, which is arguably a more common tree scenario.  This makes it slightly easier to understand, in my opinion.

Anyway, the DDL consists of:

CREATE TABLE cats
(
    cat_id SERIAL
      PRIMARY KEY
  , cat_name VARCHAR(20) NOT NULL
  , parent_cat_id int NULL
);

ALTER TABLE cats ADD FOREIGN KEY (parent_cat_id) REFERENCES cats (cat_id);

INSERT INTO cats (cat_name, parent_cat_id)
VALUES ('garfield', NULL);

INSERT INTO cats (cat_name, parent_cat_id)
VALUES ('bertina', 1);

INSERT INTO cats (cat_name, parent_cat_id)
VALUES ('oletha', 2);

And the recursive CTE looks like:

;WITH RECURSIVE cats_hierarchy AS
(
  SELECT c1.cat_id
      , c1.cat_name
      , cast('' as varchar(20)) as parent_cat_name
      , c1.parent_cat_id
      , 1 as level
  FROM cats c1
  WHERE c1.parent_cat_id IS NULL

  UNION ALL

  SELECT c2.cat_id
      , c2.cat_name
      , c1.cat_name AS parent_cat_name
      , c2.parent_cat_id
      , c1.level + 1 AS level
  FROM cats c2
      INNER JOIN cats_hierarchy c1 ON c2.parent_cat_id = c1.cat_id
)
SELECT *
FROM cats_hierarchy;

Results look like:

╔═════════╦═══════════╦══════════════════╦════════════════╦═══════╗
║ cat_id  ║ cat_name  ║ parent_cat_name  ║ parent_cat_id  ║ level ║
╠═════════╬═══════════╬══════════════════╬════════════════╬═══════╣
║      1  ║ garfield  ║                  ║ (null)         ║     1 ║
║      2  ║ bertina   ║ garfield         ║ 1              ║     2 ║
║      3  ║ oletha    ║ bertina          ║ 2              ║     3 ║
╚═════════╩═══════════╩══════════════════╩════════════════╩═══════╝

In the CTE the first query (before the UNION ALL) shows cats that are at the root.  The 2nd part of the CTE (after the UNION ALL) gets cats related to the parent cat.  Those returned results are then queried again by the CTE, generating a tree.

Let me know if that helps, or if you need further clarification.


User is in multiple Active Directory groups with multiple roles in database

First step is to validate that the user is seen as a member of both AD groups by SQL Server by running the below as a sysadmin:

EXEC xp_logininfo 'DOMAIN\UserName', 'all';

The returned table should show all paths of server access for this user, i.e. one row for each AD group the user is a member of that provides access to SQL Server. If the group does not appear, verify in AD that they are in fact a member.

Next, have the user log out and back in from the machine from which they're trying to access SQL Server. Their AD Group membership is contained in a security token issued by the DC when they log in. If they haven't logged out and back in since being added to the AD Group then the security token will not reflect their current membership status and so the write access won't be granted.


Restoring MySQL database with db dump and binlogs

They are two things that can be good for you in this instance

GOOD THING #1 : You can convert the two MyISAM tables to InnoDB
GOOD THING #2 : The two MyISAM tables are never changing

If either one of these good things apply, then I have good news for you

STEP01) Zap All Binary Logs on the Master (OPTIONAL)

Run this command on the Master:

mysql> RESET MASTER;

If this part scares you, you could either skip it or make a copy of the binary logs before doing it

STEP02) Create the mysqldump as follows:

echo "STOP SLAVE;" > MySQLSlaveReload.sql
mysqldump -v --master-data=1 --single-transaction --routines ....  >> MySQLSlaveReload.sql
echo "START SLAVE;" >> MySQLSlaveReload.sql

STEP03) Move the MySQLSlaveReload.sql to the Slave

STEP04) Load MySQLSlaveReload.sql on the Slave

On the Slave, load the script as follows:

mysql -u... -p... -A < MySQLSlaveReload.sql

The script will stop the slave, load the data, and start the slave. What about the log file and position? Before you do STEP02, look at line 22 of MySQLSlaveReload.sql

head -22 MySQLSlaveReload.sql | tail -1

The mysqldump option --master-data=1 recorded the log file and position as of the start of the mysqldump on line 22.

Give it a Try !!!


How to run a long query from a trigger without upset

I would recommend you to use "SQL Server Service Broker". It allow you to run queries asynchronously not only in triggers.

Here is the link to documentation: https://msdn.microsoft.com/en-us/library/bb522893.aspx

Here is a link to a tutorial that works through how to set this up: http://www.sqlservercentral.com/articles/Service+Broker/73398/


Deleting multiple tables in a single delete query in Mysql

How does it works internally?

I assume that it performs the JOINs as if you were SELECTing data out of those tables, nothing which rows matched, and then removing them.

  Any other performance caveats compared to spliting the query into 4 separate delete queries?

That depends on what you intend. If you are otherwise doing:

DELETE FROM table1 WHERE pk IN (101,102,106,...)
DELETE FROM table2 WHERE pk IN (101,102,106,...)
DELETE FROM table3 WHERE pk IN (101,102,106,...)
DELETE FROM table4 WHERE pk IN (101,102,106,...)

the joining of data may well make the process slower. If you need to join to only delete rows that exist in all four table (see below) then this will be faster as that operation only needs to be done once.

  Any other performance caveats compared to spliting the query into 4 separate delete queries?

A very important consideration here is not standard SQL syntax that will be usable elsewhere. It will limit portability and may confuse future maintainers of the code, so I would not recommend it.

Also, it may not be doing what you actually intend. The JOINs there will enforce only deleting rows that match in the first table, so if pk=102 exists in tables 2, 3, and 4 but not in 1, it will not be deleted from the three it does exist in. Unless your sample query is incorrect: if table 1 contains parent entities for entities in the other tables, and so forth, therefore you are matching a foreign key to a primary key, like so:

DELETE table1,table2,table3,table4
  FROM table1
  LEFT JOIN table2 ON table1.pk=table2.fkReferecingTable1
  LEFT JOIN table3 ON table3.pk=table2.fkReferecingTable1
  LEFT JOIN table4 ON table4.pk=table3.fkReferecingTable1
WHERE table1.pk IN (101,102,106,...)

If this is the case then you might want to consider defining the foreign keys with ON DELETE CASCADE in which case you could simply execute

DELETE table1 FROM table1 WHERE table1.pk IN (101,102,106,...)

and let the database engine handle the rest. I say "might want to consider" because IMO ON DELETE CASCADE can be rather dangerous, so I usually avoid it unless it is the only efficient way.


Tool to find CPU utilization for a particular query in Postgres

You can use the CPU monitor of your operating system (e.g. top in Linux or ProcessExplorer in Windows).

Each connection to the database will create a new process on the server. The process id related to the connection is reported in the column pid of the system view pg_stat_activity.

With that process ID you can monitor the CPU usage of that process which is essentially the CPU usage of your query (while it's running of course)

More details in the manual: http://www.postgresql.org/docs/current/static/monitoring.html  and the Postgres Wiki: https://wiki.postgresql.org/wiki/Performance_Analysis_Tools


You can use both, Nagios and Oracle Cloud Control 12c (also applies for Grid Control 10g and 11g).

I used Nagios about 8 years ago to run custom SQLs but I guess there is much changed since then. Maybe someone else has recent experiences with that.

In Oracle Cloud Control you have 2 options:

Write your own Agent Plugin. I never did that because after some testing I considered it to be to much work.
Use "Metric Extensions" available with "Diagnostic Pack".


Create fast-refresh MV over dblink on table without PK?

As already demonstrated in your other question, a unique constraint on a NOT NULL column is good enough to create an mview log with "PK" values. That can be used for fast refresh.
SQL> conn bp/bp
Connected.
SQL> create database link self connect to bp identified by bp
     using 'localhost:1521/RYMIN19';

Database link created.

SQL> create table t1 (c1 number not null unique, c2 number);

Table created.

SQL> create materialized view log on t1 with primary key;

Materialized view log created.

SQL> create materialized view mv1 refresh fast on demand as select * from t1@self;

Materialized view created.

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

1 row created.

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

1 row created.

SQL> commit;

Commit complete.

Then:
SQL> exec dbms_mview.refresh('MV1', 'f');

PL/SQL procedure successfully completed.

SQL> select last_refresh_type from user_mviews where mview_name = 'MV1';

LAST_REF
--------
FAST


NoSQL- automatically cache and update references to other documents as embedded

You may be looking for MongoDB Aggregation pipeline stage $lookup.

Example:
A collection orders contains the following documents:

{ "_id" : 1, "item" : "abc", "price" : 12, "quantity" : 2 }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1 }

Another collection inventory contains the following documents:

{ "_id" : 1, "sku" : "abc", description: "product 1", "instock" : 120 }
{ "_id" : 4, "sku" : "jkl", description: "product 4", "instock" : 70 }

The following aggregation operation on the orders collection joins the documents from orders with the documents from the inventory collection

db.orders.aggregate([
    {
      $lookup:
        {
          from: "inventory",
          localField: "item",
          foreignField: "sku",
          as: "inventory_docs"
        }
   }
])

Example output:

{
  "_id" : 1,
   "item" : "abc",
  "price" : 12,
  "quantity" : 2,
  "inventory_docs" : [
    { "_id" : 1, "sku" : "abc", description: "product 1", "instock" : 120 }
  ]
}

See more examples.

You could combine $lookup with $out stage to create another collection. See related $graphLookup


Very slow query despite index being used

There are two indices on (insert_time) and (insert_time DESC). B-tree indices can be scanned backwards at practically the same speed. And insert_time is NOT NULL, so there is no point whatsoever. Drop one of those in any case.

I made some assumptions where info is missing:

Current Postgres 12.
You are free to redesign the table and lock the table exclusively for some time.
The table is basically "append only".
New rows are added in order of insert_time.
The current table is physically out of order and/or bloated (leading to the index scan in your query plan instead of bitmap index scan)

I would rewrite the table like this:

BEGIN;
-- SET maintenance_work_mem = ???   -- set as high as you can afford temporarily

-- drop idx first to free space
DROP INDEX public.idxgin;     -- acquires exclusive lock on the table
DROP INDEX public.tv_smartdev_insert__0ae03a_idx;
DROP INDEX public.tv_smartdevicemeasurement_modbus_smart_device_id_62c12ed0;
-- DROP INDEX public.tv_smartdev_insert__1f0611_idx; -- might help SELECT, drop later

ALTER TABLE public.tv_smartdevicemeasurement_modbus ALTER measurement_id DROP DEFAULT;
DROP SEQUENCE public.tv_smartdevicemeasurement_modbus_measurement_id_seq;  -- drop owned sequence
ALTER TABLE public.tv_smartdevicemeasurement_modbus
         RENAME TO tv_smartdevicemeasurement_modbus_old;    -- free org. name

CREATE TABLE public.tv_smartdevicemeasurement_modbus (
   measurement_id   serial PRIMARY KEY    -- consider IDENTITY column instead, see below
 , smart_device_id  integer NOT NULL      -- reordering saves 4-8 bytes alignment padding per row
 , insert_time      timestamp with time zone NOT NULL
 , data             jsonb NOT NULL
 , CONSTRAINT tv_smartdevicemeasur_smart_device_id_62c12ed0_fk_tv_smartd
      FOREIGN KEY (smart_device_id)
      REFERENCES public.tv_smartdevice_modbus (device_id) DEFERRABLE INITIALLY DEFERRED  -- WHY deferrable / deferred? see below
);

INSERT INTO public.tv_smartdevicemeasurement_modbus
      (measurement_id, smart_device_id, insert_time, data)
SELECT measurement_id, smart_device_id, insert_time, data
FROM   public.tv_smartdevicemeasurement_modbus_old
ORDER  BY insert_time DESC;   -- CLUSTER while rewriting

DROP TABLE tv_smartdevicemeasurement_modbus_old;

CREATE INDEX tv_smartdev_insert__1f0611_idx ON public.tv_smartdevicemeasurement_modbus (insert_time DESC);
-- CREATE INDEX tv_smartdev_insert__0ae03a_idx ON public.tv_smartdevicemeasurement_modbus (insert_time);  -- nope!

CREATE INDEX idxgin ON public.tv_smartdevicemeasurement_modbus USING gin (data);  -- ?
CREATE INDEX tv_smartdevicemeasurement_modbus_smart_device_id_62c12ed0 ON public.tv_smartdevicemeasurement_modbus (smart_device_id); -- ?

COMMIT;

VACUUM ANALYZE public.tv_smartdevicemeasurement_modbus;

This rewrites the table saving some space (which also helps performance). Most importantly, it clusters the table according to your main index - and removes all possible bloat while being at it. This should help locality of data and make Postgres read fewer data pages. Unless your data column is big, you should see bitmap index scan in the query plan now. And if data is small, consider a covering index  to get index-only scans. See:

Does a query with a primary key and foreign keys run faster than a query with just primary keys?

How does the changed column order save space?

Calculating and saving space in PostgreSQL

DEFERRABLE INITIALLY DEFERRED? This is rarely necessary. And cheaper without. See:

SET CONSTRAINTS ALL DEFERRED not working as expected

serial vs. IDENTITY? See:

How to change a table ID from serial to identity?

Then query like this:

SELECT data
FROM   tv_smartdevicemeasurement_modbus
WHERE  insert_time >= '2019-08-01'  -- included
AND    insert_time <  '2019-09-25'; -- excluded

Be aware that these date literals are interpreted according to your local time zone setting. Consider true timestamptz input to be unambiguous. See:

Ignoring time zones altogether in Rails and PostgreSQL

These two indices are orthogonal to the query at hand: idxgin and tv_smartdevicemeasurement_modbus_smart_device_id_62c12ed0. Drop unless needed for unrelated stuff.


Enforce a column as unique based on another column value

When you have a unique constraint that you want to apply to only a subset of rows, you can enforce this using a unique, filtered index. The index that seemed to work for you in this case is:

CREATE UNIQUE INDEX [UNQ_SampleTable_Code]
  ON dbo.[SampleTable]([Code])
  WHERE   ([Deleted] = 0);

This ensures that only one distinct value of Code can exist for rows where Deleted is 0, but duplicates can exist where Deleted is 1. Typically this will also help the performance of some queries, since you will often be interested in only the active rows (and not the soft deletes), but you may want to consider adding columns to the INCLUDE clause if this doesn't cover queries (SQL Server may choose to scan the clustered index, or a different index, if lookups are deemed too costly).


Trying to replicate 2 or more Postgres instances to one standby host

You can do that , just treat your instances as if they where separate nodes.  In your case you will end up with 3 instances on the standby server.

In repmgr you will need to create a cluster for A, B and C.  Let's call them CA, CB and CC, then you start by adding the nodes A and B and C as a primary node.  Your layout on SRV2 will have to allow those instances to live next to eachother.  Since you don't really mention what OS/flavor you are using it's hard to give specific advice there.

afaik you will most probably need to run several repmgr daemons if you use them in conjunction with different repmgr.conf config files which is probably the biggest hassle.

CORRECTIONS:

I seem to have misunderstood it a bit, you seem to have 2 clusters, with 4 instances (3 existing and 1 pending), Instances SRV1/A + SRV2/C make up cluster CA , then you just need to add instance D to cluster SRV1/B , making SRV1B + SRV2/D cluster number 2.  In any case, you need 2 repmgr.conf files, I've checked the docs and it never talks about multiple cluster support.  But nothing would stop you from running 2 repmgrd deamons with different config files.


Posgresql - deadlock on non related queries

These queries are generated internally by the triggers that implement foreign key constraints.

For example, adding a row to a table with a foreign key constraint will add such a key share lock on the corresponding row in the referenced table, so that nobody can delete it or modify the primary key until the inserting transaction is done.

This can cause deadlocks between transactions that modify both tables.

When searching for the statements at fault, don't forget that it was not necessarily the last statement in the transaction (that is shown in the log message) that took the problematic lock.

One way to proceed is to set log_min_duration_statement to 0, and when the deadlock strikes again, examine all statements run by the affected transactions. The other way is to review the application code to find out which statements were executed in the transactions.


Sch-M WAIT blocks Sch-S in SQL Server 2014 but not SQL Server 2008 R2?

The behaviour did change between SQL Server 2008 R2 and SQL Server 2012. The 2008 R2 implementation was inconsistent with the documented 'relaxed FIFO' semantics:

  Locks are granted in a relaxed first-in, first-out (FIFO) fashion. Although the order is not strict FIFO, it preserves desirable properties such as avoiding starvation and works to reduce unnecessary deadlocks and blocking.New lock requests where the requestor does not yet own a lock on the resource become blocked if the requested mode is incompatible with the union of granted requests and the modes of pending requests.A conversion request becomes blocked only if the requested mode is incompatible with the union of all granted modes, excluding the mode in which the conversion request itself was originally granted.

In 2008 R2, a new Sch-S lock request was granted despite it being incompatible with the union of granted and waiting requests, which might lead to lock starvation. In 2012, the Sch-S lock request is blocked.

The reproduction script below uses regular tables rather than a Service Broker queue:

-- Session 1
CREATE TABLE dbo.LockTest (col1 integer NULL);

INSERT dbo.LockTest (col1) VALUES (1);

BEGIN TRANSACTION;

-- Will hold row-X, Pag-IX, and Tab-IX
INSERT dbo.LockTest (col1) VALUES (2);

-- Session 2
-- Blocked waiting on Sch-M
TRUNCATE TABLE dbo.LockTest;

-- Session 3
-- Takes Sch-S only
-- Not blocked in 2008 R2
SELECT * FROM dbo.LockTest AS LT WITH (READUNCOMMITTED);

In summary, 2008 R2 did not behave as designed. The problem was fixed in SQL Server 2012.


Default value of a column based on other column in MySQL

If the code field values are altered never the solution is trivial - look on the fiddle provided in Sam's comment. The only point I'd recommend to specify VIRTUAL attribute for calculated field explicitely.

If the code field values specified in a question are default only, and they can be altered from the default value freely either when inserting or by data updating, I'd recommend to make this field NULLable and use NULL value as a mark that the value is default one and must be calculated in a query. In all places where you need the value of this field you will use the below expression instead of direct field's value selection:

SELECT  ...,
        COALESCE(code, CONCAT(SUBSTRING(name,1,2),id)) code,
        ...


I did some real digging in the bug list for event scheduler issues.

It seems that a certain time calculation for the event scheduler was not portable. This bug was fixed as of MySQL 5.1.48 (Fixed June 2, 2010).

There was a past issue about SHOW EVENTS not getting events from the correct database. This bug was fixed as of MySQL 5.1.57 (Fixed May 5, 2011).

The latest scheduler bug was fixed July 5, 2011 in MySQL 5.1.58

You are using MySQL 5.1.41. You may want to upgrade to the latest version of MySQL 5.1 which is 5.1.58. No scheduler bugs exists as of today.

CAVEAT

On another note, I would change the SQL query to not only do less work

Instead of your DELETE query:

delete from ta_table where timestampdiff(minute,ti_time,now())>120;

Restructure it as follows:

delete from ta_table where ti_time < (now() - interval 2 hour);

Your DELETE will calculate against every row in the table. This new DELETE stops short when comparing ti_time against a time value  (now() - interval 2 hour) instead of computing timestampdiff on every row.

Make sure ti_time is indexed. If not, do this:

ALTER TABLE ta_table ADD INDEX (ti_time);

Assuming the table is MyISAM, you may also want to periodically shrink the table every month like this:

ALTER TABLE ta_table ENGINE=MyISAM;

I hope this information helps !!!

UPDATE 2011-07-19 08:00 EDT

From the last chat room session lovesh and I had, here is the example I ran to create the event on my PC running MySQL 5.5.12:

drop database lovesh;
create database lovesh;
use lovesh
create table mydata (id int not null auto_increment primary key,ti_time timestamp DEFAULT current_timestamp) ENGINE=MyISAM;
DELIMITER $$
DROP PROCEDURE IF EXISTS `lovesh`.`LoadMyData` $$
CREATE PROCEDURE `lovesh`.`LoadMyData` ()
BEGIN
    DECLARE NDX INT;
    SET NDX = 0;
    WHILE NDX < 100 DO
        INSERT INTO mydata (ti_time) VALUES (NOW() - INTERVAL CEILING(14400*RAND()) SECOND);
    SET NDX = NDX + 1;
    END WHILE;
END $$
DELIMITER ;
show create table mydata\G
SHOW CREATE PROCEDURE LoadMyData\G
CALL lovesh.LoadMyData();
CREATE TABLE ta_table LIKE mydata;
ALTER TABLE ta_table DISABLE KEYS;
INSERT INTO ta_table SELECT SQL_NO_CACHE * FROM mydata;
ALTER TABLE ta_table ENABLE KEYS;
CREATE EVENT ev
    ON SCHEDULE
      EVERY 1 MINUTE
      STARTS (NOW() + INTERVAL 1 MINUTE)
    DO
      DELETE FROM ta_table WHERE ti_time > NOW() - INTERVAL 2 HOUR;
SELECT COUNT(1) FROM ta_table;
SELECT SLEEP(62);
SELECT COUNT(1) FROM ta_table;

This worked for me when ta_table was MyISAM. It just kept running using InnoDB. This may be the sticking point.


Linked Server Between SQL Server and iSeries is rounding decimals

Figured it out!!

Apparently we didn't have the latest service pack installed. We had to install the 64bit version of the service pack (32bit version failed to install), and when it finished the decimal numbers were coming across.

EDIT: In case anyone was wondering:
We're using "IBM iAccess for Windows 7.1 (07.01.0800) (64bit)" and using "Microsoft OLE DB Provider for ODBC Drivers" as the linked server sql driver.

EDIT 2017: Seeing as I just ran into this problem again and had to hunt down the service pack again, here is the link to the service pack download page, so that in a few years when I run into this problem again I will find it easier...

http://www-03.ibm.com/systems/power/software/i/access/windows_sp.html


Removing a FAILED node from repmgr cluster

As stated in https://groups.google.com/forum/#!topic/repmgr/R7osmnYLn4g :

  probably an UNREGISTER command is needed for that, for now... just
  delete the entry from repl_nodes

This is done by accessing the master node with the same connection details used by repmgr and issuing the following:

DELETE FROM repmgr_<cluster name>.repl_nodes WHERE name = '<name>';


Change constraint on column based on value of another

A foreign key can not be "conditional". The only "exception" to that rule are null values which can't reference another table by definition.

If I understand your question correctly, you are trying to implement a constraint that says "if refunded is true then refund_id must reference an existing transaction".

I don't think you need the refunded column at all. Because the flag refunded can be derived from the value of the refund_id  using the expression: refund_id is not null.

If you do want a column like that, just create a view with that expression.

If you do insist on having the redundant refunded flag, you could setup a check constraint like this:

CREATE TABLE transactions(
    id SERIAL PRIMARY KEY,
    type TXN_TYPE NOT NULL,
    amount BIGINT,
    . . . .,
    refunded boolean DEFAULT FALSE,
    refund_id integer null REFERENCES transactions,
    constraint check_refund
       check ( (refunded and refund_id is not null or
               (not refunded and refund_id is null) )
);


Inserting Serbian Latin and Cyrillic characters

You have to specify that you are inserting wide character data:

CREATE TABLE #t (id INT,c1 VARCHAR(MAX),c2 NVARCHAR(MAX));
INSERT INTO #t VALUES(1,'žđšćč žđćčžđšćčŽĐŠĆČ','žđšćč žđćčžđšćčŽĐŠĆČ');
INSERT INTO #t VALUES(2,N'žđšćč žđćčžđšćčŽĐŠĆČ',N'žđšćč žđćčžđšćčŽĐŠĆČ');
SELECT * FROM #t;
DROP TABLE #t;

Result:


Can lowering fill factor result in more page splits?

When you have lots of page splits caused by random middle-of-BTree inserts, your pages will naturally average around 65%* full.  Split pages start at 50% full and any that fill up past 100% get split back down to 50%.

So rebuilding your index with a fill factor of 85% might temporarily increase page splits, just not as much as rebuilding with a fill factor of 100%

*Why not 75%?  I think it's because each split page covers half the value range of the original page, the new pages fill up half as fast as the original page.


SQL Query logging in extended events deadlock xml report is too short

If there isn't a way to tell sql not truncate, what is the best way of
retrieving the whole query

Every deadlock graph contains sqlhandle for every process that partecipates and it can be used to retrieve sql_text:
select *
from sys.dm_exec_sql_text(0x01000c001247710230b2bfa34800000000000000000000000000000000000000000000000000000000000000)

where the value for parameter for sys.dm_exec_sql_text is the sqlhandle from deadlock graph.
This way you may get your statement if its execution plan is still in cache.
You need the VIEW SERVER STATE permission to query sys.dm_exec_sql_text.


Easily grant access to list of users after database is created

Create an Active Directory group and add all of the necessary users to it. Grant the group access to the database, which can be done easily with CREATE USER. If you want to add the group to a role, such as db_owner/db_datareader/db_datawriter, then use ALTER ROLE.

You could also do the steps that you've been doing in the UI, but then select the option to script it out so that you don't have to lookup the syntax for CREATE USER or ALTER ROLE. I often use the UI to help me with scripts I'm building.

Once the script works as desired, save it and use it for a recreated database. You should just need to switch which database to run the script in.

Alternatively, add the group to the model database and whichever roles the group should be in so that when you create a new database the group automatically has access. See this page for more information on model. But only use model if the group needs access to all newly created databases.


Is there a database table design to avoid storing table names in a table?

If you know in advance that you're going to have at most a smallish (under 10) set of services and you don't need to add/remove/change them programmatically or often, I would use separate link tables for the services. Since the client needs to request a service to be added, it's probably a lot simpler to create a new link table and make code changes to reflect the new service than to create a "clever solution" which is overly clever for the amount of changes you're going to have.

With generic solutions you tend to exchange efficiency/simplicity and other things for ease of use (e.g. you have just a single generic link table, but you end up with more complexity because you can't have a foreign key, your queries need to work with your custom solution, you must differentiate the services somehow in your application anyway, etc.).

Even if you were to have a lot of services and they would often change, I wouldn't go for the "dynamic link table" approach. You can definitely make it work somehow, but it would be a hack rather than a good solution. So what would be a good solution in that case? Hard to say from the question alone, but I'm sure it would involve a lot more than just the database.

The gist of the question is "How can I treat different things as if they were the same, except in many cases they need special treatment", and I'm offering the "don't even try if it isn't strictly needed" answer. If that's not to your liking, you'll have to explain a lot more about your system, the services, the data, etc.

One (not saying the best) way to get the amount of services provided by a business would be to use UNION for example. It gives a readable (and easily editable!) query, for when a service is added.

SELECT COUNT(*) FROM (
SELECT 1 FROM business bus JOIN business_services_a a ON (bus.id = a.business_id)
UNION
SELECT 1 FROM business bus JOIN business_services_b b ON (bus.id = b.business_id)
UNION
SELECT 1 FROM business bus JOIN business_services_c c ON (bus.id = b.business_id)
)


Fastest way to check if InnoDB table has changed

For the table mydb.mytable, run this query:

SELECT update_time
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';

If you want to know what tables have changed in the last 5 minutes, run this:

SELECT table_schema,table_name,update_time
FROM information_schema.tables
WHERE update_time > (NOW() - INTERVAL 5 MINUTE);

Give it a Try !!!

UPDATE 2011-12-21 20:04 EDT

My employer (DB/Wweb hosting comany) has a client with 112,000 InnoDB tables. It is very difficult to read INFORMATION_SCHEMA.TABLES during peak hours. I have an alternate suggestion:

If you have innodb_file_per_table enabled and all the InnoDB tables are stored in .ibd files, there is a way to ascertain the time of the last update (up to the minute).

For the table mydb.mytable, do the following in the operating system:

$ cd /var/lib/mysql/mydb
$ ls -l mytable.ibd | awk '{print $4,$5}'

This timestamp is from the OS. You can't go wrong on this one.

UPDATE 2011-12-21 22:04 EDT
    [mysqld]
    innodb_max_dirty_pages_pct=0;

Add this to my.cnf, restart mysql, and all InnoDB tables will experience fast flushes from the buffer pool.

To avoid restarting, just run

mysql> SET GLOBAL innodb_max_dirty_pages_pct=0;

UPDATE 2013-06-27 07:15 EDT

When it comes to retrieving the date and time for a file, ls has the --time-style option:

$ cd /var/lib/mysql/mydb
$ ls -l --time-style="+%s" mytable.ibd | awk '{print $6}'

You can compare the timestamp of the file against UNIX_TIMESTAMP(NOW()).


Connection issues during a drop in CPU usage

It sounds somewhat like you are hitting a bit of memory pressure. Dirty Buffers are pages in memory that have changes in them that have not been written to disk yet. Normally they would stay in memory until one of the various processes clean them up and/or write them to disk.

There are some things you will want to look into but with only the information you have given here I have to ask, "How much ram have you reserved for the OS?"

You are running a 64 bit version of Windows. Now, the 32 bit kernel will use 4 GB of RAM. However, we have found (the hard way) that 64 bit versions of the OS will use 8 - 12 GB of RAM dependent on how busy the Sql server is. By that I mean are you doing a lot of reading and writing to disk. If the OS can't allocate the memory it needs for this (because the Sql server has sucked it all up) you will see the CPU being consumed for the additional context switching it needs to do to write those buffers out to disk. When you added the additional RAM to the server did you reset the Sql Server Max Memory setting? When you only had 16 GB of RAM your Sql server was constrained as much as the windows kernel was and so you were likely using virtual disk (hard drive space) quite a bit. When you added more RAM you opened things up a bit and now the Sql server is attempting to keep more of the database(s) in memory.


mysqlsh rebootClusterFromCompleteOutage force prompt

I have used the rejoinInstances option to specify all my nodes, and it seems that doing it that way the shell doesn't ask for confirmation:

dba.rebootClusterFromCompleteOutage(clusterName, {rejoinInstances: ["node-2:3306","node-3:3306"]});

Note: don't include the seed in the array.


mysqldump generates incorrect create view queries

When dumping / pumping MySQL databases, the output script generates this as a temporary structure first, and later below at the end of the script, it drops this and create the actual view itself, something like this:

...DROP VIEW IF EXISTS myview ...

If you end up with the final view still selecting 1s, it would be probably because database name mismatch, when you use --databases option in the mysqldump , the script will output

USE database_name

in multiple places (not only at the top of the script), and specifically there is one before the DROP and Recreating the actual view.

This happened to me when exporting and importing databases with different name.


Does SQL Server 2008 have an equivalent to Oracle's “table%rowtype” syntax or (Data Type)?

No version of SQL Server has this. It would be useful (PostreSQL has the same and I like it).

In SQL Server, there are table types (CREATE TYPE), temporary tables (CREATE TABLE #foo) and table variables (DECLARE @var TABLE), but you can't mimic or inherit a table definition using %TYPE or CREATE TABLE .. INHERITS/OF


Deciding what values should be used in a data warehouse dimension?

If you have an attribute with the same granularity of the Fact table (i.e.: "TransactionID") and it is usually used to filter one single Fact table, you don't have to create a separate table (aka Shared Dimension) for it. Instead, this attribute can live in the fact table itself.
This is know as Degenerate Dimension

You can find more about it here: Degenerate Dimensions