Follow up to certificate sharing question

When you backup and restore a certificate for the purpose of moving the certificate you shouldn't have to include any clause related to the private key. I explained in the linked post the reasoning why the value of private key derives primarily from it's uniqueness and any operation that duplicates a private key dillutes its value:

it can no longer be used as proof of identiy (ie. signature) since multiple copies exists throughout the world and non-repudiation is busted
it can no loger be used as a secure means to achieve privacy (ie. encrypt with corresponding public key) because since multiple copies exists, a copy may had been compromised

Just to drive the point home, a certificate is a 'an electronic document which uses a digital signature to bind a public key with an identity' and the correct terminology is public key certificate. In other words a certificate does not have a private key. I myself am carefull to say 'certificate and the associated private key'.

The few operations when is acceptable to backup and restore a certificate and the associated private key are key escrow  and backup for recovery purpose (not for move).

Now after this long dissertation, the answers:

yes, use BACKUP CERTIFICATE testcert TO FILE = 'd:\mssql\testcert.cer'
Use CREATE CERTIFICATE testcert2 FROM FILE = 'd:\mssql\testcert.cer'


Performance: Left joining same table multiple times

Approach 1 will be better in your scenario, as it will give the result by one query run and seems to be properly joined as required by the business, though same table is joined multiple times.An index on id column will perform better.

Approach 2 with order by will require some more space in temp db for processing and the IN operator will degrade the performance in query 2 if the #ids has a lot of values returned within it(say there are large number of updated,deleted or created users returned by query 1).


I agree with TomTom on the merits of the Datamodel resource book - it will give you more insight than StackOverflow, and if memory serves, there's a fairly detailed discussion of exactly this scenario.

The design you've come up with is similar to what I've seen for other systems - what you call ProductVariation is often called "variant".

I'm a little confused about the role of SKU in your design - the acronym stands for "Stock Keeping Unit". As it's supposed to be unique and never change, I'd make that the primary key of your ProductVariation table, rather than ID.

If you're only ever going to sell your current product range, or are happy to refactor in future, you can leave it as is; alternatively, you could consider a design with "table per subclass". This would give you a schema along the lines of:

Product (id, name, categoryID, description)

SizeVariant (SKU, productID, finish, width, thickness, Size, location, quantity)
ColourVariant (SKU, productID, Colour, finish, location, quantity)
NoVariant (SKU, productID, location, quantity)

Note how I've also put some of the attributes you've currently assigned to "Product" into the child tables.

This keeps the schema more "self describing", but adds a lot of work - only do that if you think the product catalogue will grow in the future, and you want to avoid EAV.


Both are related to performance and response speed.

A hint gives the Cost-Based Optimiser (CBO) a "nudge" in a particular direction; an explicit instruction to try and get to the data in the way you tell it to.  The use of hints requires a [very] good understanding of the CBO and how Oracle chooses to do things internally.  Given that this might change with any given Release, it's probably the less effective of the two, generally speaking.

I can't recall having applied hints to any Oracle SQL I've ever written.

Indexes are generally there to speed up access.
If Oracle can use an index to get to data faster, then it [usually] will and the increase in speed can be huge!  Put millions of rows into an un-indexed table and try to query them; Oracle has no choice but to scan through them all and it will be really, really slow.  Put an index on the field you're filtering by and things can get [several] orders of magnitude faster.  A simple binary search on a single value can get to any of, say, one million records in about twenty comparisons (instead of the million).  Oracle's indexes are generally even better than that, holding more values per "bucket" and getting down to the detail even faster.

Of course, the flip side is that inserts and updates can get slower, because Oracle has to update the base data and then the index[es] as well.  It's trade-off, like so much else in the Database World.


Resources on database design and data integrity enforcement best practices?

I'm curerntly going throught the following. I've read Louis Davidson's earlier books and fond them very useful so I'm looking forward to reading his latest one (for SQL Server 2012).

"Pro SQL Server 2005 Database Design and Optimization", Louis Davidson et al.
"Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design", Michael J. Hernandez.

More generally, authors you should look out for include Chris Date, Fabian Pascal, Joe Celko (for advanced and/or theoretical topics).

Also check out this other DBA question - some of the answers there may help.


Sure, you can generate the list of years in dynamic SQL, but I suggest passing them as numbers (just one less thing that can go wrong if people pass in bad data, and I'm sure you didn't really mean to hard-code the values inside the procedure, defeats the purpose):

DECLARE @year1 smallint, @year2 smallint

SET @year1 = 2014;
SET @year2 = 2016;

DECLARE @years nvarchar(max) = N'', @select nvarchar(max) = N'';

SELECT  @years += N',' + QUOTENAME(y), @select += N',
  ' + QUOTENAME(y) + N' = COALESCE(' + QUOTENAME(y) + N',0)'
FROM
(
  SELECT TOP (@year2-@year1+1)
    y = @year1 - 1 + ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects ORDER BY [object_id]
) AS y;

DECLARE @sql nvarchar(max) = N'Select field1' + @select + N'
FROM
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM dbo.Testing -- ALWAYS use schema prefix!
) x
pivot
(
  SUM(Amt) for annual IN (' + STUFF(@years, 1, 1, N'') + N')
) p;';

PRINT @sql;

-- EXEC sys.sp_executesql @sql;

Slightly related:

Script to create dynamic PIVOT queries in SQL Server


Try it and see.

You'll find that it works perfectly.

Now, if you want to insert data ignoring duplicates, that's another problem entirely.


mysql won't start, it says Table mysql.plugin doesn't exist

If you're upgrading you should use mysql_upgrade script to install system tables.


If 2 tables are 1:1, then probably they should be a single table.  If 1:many, then 2 tables and the 'many' table has the id of the other table.  many:many requires a 3rd table.

It sounds like you have 1:many, so you don't need the user_paths table.

More on optimal schema for many:many.


Simplified/automated datafile free disk space reclaim

The short answer is No.  Unfortunately the way to do this in Oracle does require the "big stack of boring queries".   The articles you linked to are some of the best information available on the subject.  The datafile does indeed become fragmented, so that even if free space exists below the highest segment, Oracle will not automatically consolidate it when a RESIZE is done.

To "defragment" the tablespace you need to move these segments to the start of the datafile rather than at the end.  For tables this is an offline process meaning the table will be unavailable while the move is taking place.  Indexes can be moved either offline or with Enterprise Edition they can be moved online.  Since you have an outage window I recommend you follow these steps.

A. Shrink datafiles with free space beyond the high water mark.  This can be done as follows (the query is similar to Frosty Z's procedure):

SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size",
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.",
   ceil( blocks*(a.BlockSize)/1024/1024) -
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings",
   'alter database datafile '''|| file_name || ''' resize ' ||
      ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100  || 'm;' "Command"
FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a
JOIN v$parameter p ON p.Name='db_block_size') a
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b
ON a.file_id = b.file_id
WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 )
   > 100 /* Minimum MB it must shrink by to be considered. */
ORDER BY "Savings" Desc;

B. After shrinking things above the high water mark, find out what tablespaces would still benefit from having segments moved.

SELECT DISTINCT tablespace_name FROM
(
    SELECT tablespace_name, block_id + blocks LastBlock,
       lead(block_id) OVER (PARTITION BY File_ID
          ORDER BY tablespace_name, file_id, block_id) NextBlock
       FROM dba_free_space
) WHERE LastBlock <> NextBlock AND NextBlock IS NOT NULL;

C. For each of these tablespaces determine which segments need to be moved. (Replace USERS with the name of your tablespace or join it with the previous query)

SELECT distinct de.segment_name
FROM dba_extents de
JOIN
(
   SELECT tablespace_name, file_id, MIN(block_id) LowestFreeBlock
   FROM dba_free_space
   WHERE tablespace_name = 'USERS'
  GROUP BY tablespace_name, file_id
) dfs ON dfs.tablespace_name = de.tablespace_name AND dfs.file_id = de.file_id
WHERE de.tablespace_name = 'USERS'
AND de.block_id > dfs.LowestFreeBlock;

D. Move each table and rebuild the indexes and statistics.

E. Repeat step A.

I just built most of these queries, so you will want to thoroughly test them before use.  I suppose you could create a procedure that would use EXECUTE IMMEDIATE to create the actual statements to run dynamically, but because queries will receive ORA-08103: Object no longer exists while the move is in progress, I think it is best to control that process manually even if it does mean a bit more time/effort.


sql 2000 does have some performance tools built into them.  Run these on your queries to see if they give you any help.

Query Analyzer
Profiler
Index Wizard
Performance Monitor


SSIS how to parse column with erratic data

For something like this the optimal solution of course is to control your input. That said, the reality is you have to parse the supplied input.

For something as complex as your parsing, I'd skip the Derived Column transformation and go straight for a Script transformation. I select my source column, Input and create three output columns: number, trash and Interval. number and Interval will hold the parsed values while trash will only be populated when the script can't make heads or tails from the input.

I use two member variables, numbersRegex and periodDomain. periodDomain is just a list with the acceptable values. For string comparisons, I force everything to lowercase and hope for English. numbersRegex is a regular expression that is used to identify digits in a string.

For every row that comes in, the script will split the Input value based on whitespace. For each of those tokens, I test whether the token has a digit in it. If it does, we'll call the GetBiggestNumber method. Otherwise, we'll call the ValidatePeriodDomain Once all the tokens have been processed, then it's important to make certain both values have been set.

GetBiggestNumber attempts to look at all the groupings of number and find the largest set.

ValidatePeriodDomain attempts to compare the current value to a known list of acceptable values.

using System.Text.RegularExpressions;
using System.Collections.Generic;

/// <summary>
/// The amazing script transformation
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    Regex numbersRegex;
    List<string> periodDomain;

    /// <summary>
    /// Initialize member variables
    /// </summary>
    public override void PreExecute()
    {
        base.PreExecute();
        // match consecutive digits
        this.numbersRegex = new Regex(@"\d+", RegexOptions.Compiled );
        this.periodDomain = new List<string>(){ "year", "month" };
    }

    /// <summary>
    /// Parse the incoming data
    /// </summary>
    /// <param name="Row">The row that is currently passing through the component</param>
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        string[] parts = Row.Input.Split();
        string period = string.Empty;
        int? foo = null;

        foreach (string token in parts)
        {
            // try to do something with it
            // If the token has a digit in it, then we'll extract the largest value
            // if no digits, then the first token matching our domain is preserved
            if (this.numbersRegex.IsMatch(token))
            {
                foo = GetBiggestNumber(token);
            }
            else
            {
                if (ValidatePeriodDomain(token))
                {
                    period = token;
                }
            }
        }

        // at this point, we've processed the input data
        // If the local variables are in their initial states, then we didn't find
        // anything of note and need to populate the Row.Junk column
        // Why local variables, because can't read from Row.column
        if (period == string.Empty || (foo == null))
        {
            Row.trash = Row.Input;
        }
                    else
                    {
            Row.number = foo.Value;
            Row.Interval = period;
                    }
    }

    private bool ValidatePeriodDomain(string token)
    {
        return (this.periodDomain.Contains(token.ToLower()));
    }

    private int? GetBiggestNumber(string token)
    {
        int? bigOne = null;
        int? current = null;
        // Get all the groups of numbers and compare them
        foreach (Match item in this.numbersRegex.Matches(token))
        {
            current = int.Parse(item.Value);
            if (!bigOne.HasValue)
            {
                bigOne = current;
            }

            if (current.Value > bigOne.Value)
            {
                bigOne = current;
            }
        }

        return bigOne;
    }
}

Using the above script, you can see how it slices and dices the Input data. I made a minor change between the code that generated the below screenshot and what's posted. I observed that the input value 9000 was assigned to Row.number but as that Row never had an Interval assigned, I deferred the actual Row population to the end of the script (it was in the


sp_executesql adds statements to executed dynamic script?

Prior to the sp_executesql you should see an sp_prepare statement in Profiler that would give more insight: http://msdn.microsoft.com/en-us/library/ff848808(v=sql.110).aspx

Sometimes this can be hard to find, but was made a bit easier with extended events in 2008R2 - specifically causality tracking.

http://msdn.microsoft.com/en-us/library/bb630284.aspx


Is There a Single-File Based Graph Database Management System?

Quick google search showed this result

TinkerPop3
Embedded Neo4j (java)
Arango DB which does graphs


MySQL can run as a service and I hope that windows is stopping the services gracefully or you are likely to have more problems outside of MySQL. If you want to be careful, stop the service by hand. This should allow MySQL to flush all buffers before stopping. I would guess from your comment that you're getting corruption that you are using the MyISAM engine; my advice would be to migrate your tables to the InnoDB engine to make best use of the durability of the engine. In the event of a crash or ungraceful shutdown, MySQL will better recover data.


What are the consequences of dropping or changing the user name for the postgres DB account?

The Linux postgres user is entirely different from the postgres database user. It's created via the rpm package. When you're logged in as the system postgres user, it assumes the database user and database name that you're wanting to connect to is the same as the operating system user that you're attempting to connect from. That's why a simple psql works, and logs you into the database as the postgres user and database.

The postgres database is just a copy of template1. You can delete it or recreate it with few problems. You won't be able to drop the postgres user, however, because it's required by the database system.

user1@[local]:5432:user1:=# DROP ROLE postgres;
ERROR:  cannot drop role postgres because it is required by the database system
Time: 50.570 ms
user1@[local]:5432:user1:=#

However, you can block the login of the user by setting all the connections that you use to reject for the postgres, which will keep users from using that particular user for login, instead of their assigned users.


Create dynamic WHERE clause from a JSON field

Here is a small example to prove my point:

CREATE TABLE some_data (
    data hstore
);

INSERT INTO some_data
VALUES ('a=>bla'),
       ('access=>bla'),
       ('access=>user');

Now taking your JSON as an example, we can do something like this:

WITH condition_elements AS (
    SELECT key,
           ARRAY(SELECT jsonb_array_elements_text(value->'authorized_keys')) AS value
      FROM jsonb_each('
        {
         "matricule":   {"authorized_keys":["12"],
                         "unauthorized_keys": ["1", "2", "20"]},
         "departement": {"authorized_keys": ["it"],
                         "unauthorized_keys":["finance", "account"]},
         "access":      {"authorized_keys" : ["superuser", "user", "login"],
                         "unauthorized_keys" :  ["web"]}
        }'::jsonb)
)
SELECT data->key AS key,
       value,
       data->key=ANY(value) AS matches
  FROM some_data,
       condition_elements;

The actual output is to describe what to expect from above:

 key  │         value          │ matches
──────┼────────────────────────┼─────────
      │ {superuser,user,login} │
 bla  │ {superuser,user,login} │ f
 user │ {superuser,user,login} │ t
      │ {12}                   │
      │ {12}                   │
      │ {12}                   │
      │ {it}                   │
      │ {it}                   │
      │ {it}                   │

If we move the necessary part to a condition, it will work nicely:

-- add some more data for testing
INSERT INTO some_data
VALUES ('departement=>bla,access=>bla'),
       ('departement=>it,access=>bla'),
       ('departement=>it,access=>login');

WITH condition_elements (... as above ...)
SELECT *
  FROM (
        SELECT data, bool_and(data->key=ANY(value)) AS full_match
          FROM some_data, condition_elements
         GROUP BY data
       ) t
 WHERE full_match;

                  data                  │ full_match
────────────────────────────────────────┼────────────
 "access"=>"login", "departement"=>"it" │ t
 "access"=>"user"                       │ t

Notes:

It is always better to present real code in your question - like actual CREATE TABLE statements, a syntactically correct JSON snippet and a query that actually can run.
For translating JSON arrays into PostgreSQL arrays, I used here a technique that is described in detail in Erwin Brandstetter's excellent answer.
Check the documentation about the different JSON(B) operators and functions.  It contains quite a lot of useful stuff.


Is it possible to generate workload for hammerora (hammerdb for oracle) from AWR tables?

In short I would say: NO.

If you want to invest lot's of time ... you could generate SQL's and usage frequencies from awr sql history tables but the parameters will be the same for all occurences of an SQL since the bind variables are not recorded for every execution.
I am not sure about hammerora but from swingbench I know you can create your own transactions and for that you could use awr sql history as a guide on how many tx/s to generate. It still remains a synthetic load.

Smarter would be to capture the databases input from a proxy between the application server and the database.

Oracle Replay has a price, for a reason.


MySQL Daemon failed to start on CentOS 6 after automatic update

That issue is related to a broken update by Plesk.

Please read the article https://support.plesk.com/hc/en-us/articles/115001906725 .

You can fix it by running this command:

install -m 0700 -o mysql -g mysql -d /var/lib/mysql-files

and start mysql:

service mysqld start


When you shutdown/restart your computer/server, the service for the SQL Server Express instance, will be triggered to stop too. While the service is shutting down the SQL Server Database Engine will try to shut down the databases gracefully, in order to ensure they can be restarted in a timely fashion when the system comes back up.
If the system was forced to shutdown/restart before the database was able to be brought to a consistent state, then the recovery will be performed automatically, when the instance is brought back up. The SQL Server Database Engine will find the corespondeing *.mdf and *.ndf file(s) and together with the existing *.ldf file try and bring the database back online.
If for some reason the database was in the middle of (e.g.) a database reindex task or reorganize then there will be a lot of outstanding transactions to either roll-forward (COMMIT) or roll-back (ROLLBACK). Until this procedure has finished, the database will be marked as RECOVERING.
If for some reason the database is unable to brought to a consistent state, then the database will switch to the RECOVERY PENDING... state.
Reference: Database States (Microsoft | SQL Docs)
Seeing as your database is neither in the RECOVERING nor in the RECOVERY PEDNING state, then we can conclude that an actual RESTORE DATABASE ... has been triggered, which results in the database displaying the state RESTORING... which should be identical to the French expression RESTAURATION....
You can double-check the state of the database by issuing the command:
SELECT sdb.name, sdb.state_desc
FROM sys.databases AS sdb
WHERE sdb.name = 'ADMINISTRATEUR';

Now if for some reason the database does not display the same state in the SELECT statement, then you might just have a simple Refresh issue.
Solution: Right-click the Bases de données in SSMS and click on Refresh
If the database state returned from the query is the same, then have a look at the ERRORLOG file of your SQL Server instance. There should be an entry, when the RESTORE started.
If not have a look at the restore history in the msdb database:
SELECT restore_date,
       destination_database_name AS NewDBName,
       destination_phys_name AS DestinationPath,
       database_name AS OrigDBName,
       server_name AS ServreName,
       physical_device_name AS PhysicalSourceName,
       rh.[user_name] AS UserName,
       'EOR' AS EOR
FROM   msdb.dbo.restorehistory rh
       JOIN msdb.dbo.restorefile rf
            ON  rh.restore_history_id = rf.restore_history_id
       JOIN msdb.dbo.backupfile bf
            ON  bf.backup_set_id = rh.backup_set_id
       JOIN msdb.dbo.backupset bs
            ON  bs.backup_set_id = rh.backup_set_id
       JOIN msdb.dbo.backupmediafamily bmf
            ON  bmf.media_set_id = rh.backup_set_id

ORDER BY 2,1

This will show you when the restore started and (if lucky) the user that started the restore.
Solution: End the restore of the database by issuing the command pointed out by Dan Guzman in his comment:
RESTORE DATABASE ADMINISTRATEUR WITH RECOVERY;

It could be that a previous RESTORE DATABASE .... WITH NO_REECOVERY was issued, which will not finalize the RESTORE. Issuing the command stops the ability to further RESTORE DATABASE ... with additional Transaction Log backups.


Poor execution plan leads to query time increasing dramatically

You can force it not use that index by rewriting the join condition to gtf.descendant_id = gv.garage_id + 0.  Whether it will then switch to the good plan, or just switch to a different bad plan is not something we can predict.  You will have to try it and see.
If you want use to analyze why it is making the wrong choice, you will have to show us the complete plans, not just your interpretations of them.


Migrating SSRS from SQL Server 2012 to 2016

Try RSMigrationTool.exe and RSMigrationUI.exe from Microsoft

Here's  a brief excerpt from the white paper link I included below, together with the migration tool download URL and Backup/Restore Encryption Key URL:

Download the tool at http://www.microsoft.com/en-us/download/details.aspx?id=29560.
Extract the files from the MigrationToo.zip file. You need the following two executable programs in the extracted files.
RSMigrationTool.exe: Command line utility that helps in exporting the reporting services artifacts.
RSMigrationUI.exe: GUI interface that allows the users to export the reporting services artifacts.
Export the SSRS items by using the RSMigrationTool.exe.

a.  Open a command prompt as an administrator.

b.  Navigate to the folder that contains the extracted RSMigrationTool.exe.

c.  Run the tool with the following parameters.

RSMigrationTool.exe [-n native_report_server_url] –I
native_report_server_instance [-h] –f
migration_metadata_files_folder [-p password]

White Paper

http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/MigratingContentBetweenReportServers.docx

Migration Tool

https://www.microsoft.com/en-us/download/details.aspx?id=29560

Backup and Restore Encryption Keys

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-back-up-and-restore-encryption-keys?view=sql-server-2017

Alternately try restoring the SSRS databases

And, if you want to go that route, you can try to restore the SSRS ReportServer and ReportServerTempDB  databases to the target server. This example shows moving from SQL 2005 to 2008 SSRS:

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-back-up-and-restore-encryption-keys?view=sql-server-2017

As always, try all these things on a test/dev SQL Server first.


Is reconfiguration required when switching sql server machine to use a FQDN?

No, changing the dns suffix of the server should not necessitate any change at the SQL Server.

The age-old mantra of test, test, test, certainly applies if you are making a change to a production machine since there may be repercussions at the client end depending on how your clients address the machine.


You could try applying the filter

(WHERE concat(SR_PO, '-', SR_RlsSequence) IN ('100063-100', '100063-101', '100063-103', '100063-104'))

Directly on the index instead of it being implemented as a filter further in the execution plan.

You can do this by not applying functions on columns in your where clause and adding an index. The group by is also changed to work without the function.

Query adaptation:

SELECT
    concat(SR_PO, '-', SR_RlsSequence) as [Order],
    count(*) as Todo,
    sum(isnull(SR_Enabled,0)) as ToLoad,
    sum(isnull(SR_Done,0)) as Loaded,
    sum(isnull(SR_Enabled,0))-sum(isnull(SR_Done,0))-sum(isnull(Rejected,0)) as Missing,
    count(EndTime3) as Quot,
    sum(isnull(Rejected,0)) as Scrapped
FROM [Edgebanding]
WHERE SR_PO = '100063' AND SR_RlsSequence IN ('100','101','103','104')
GROUP BY SR_PO, SR_RlsSequence;

And index:

CREATE INDEX IX_SR_PO_SR_RlsSequence
ON [dbo].[Edgebanding](SR_PO,SR_RlsSequence)
INCLUDE(SR_Enabled,SR_Done,Rejected,EndTime3);


How many database users/admins should be created for a website?

You should at least not be using root for your application. The rest completely depends on how the application was developed. It sounds like you wrote it yourself, so my opinion is based on that.

My suggestion is to use two accounts, as you said one for write access (to the CMS database only) and one with read-only access (again, only for your CMS database). That way, if you have any kind of vulnerability with, for instance, the display portion of your application, you can somewhat potentially limit the damage an attacker can do.

You could do it with one user for the whole CMS; most applications you could download like WordPress and so on do it this way to make installation easier and because some hosts don't allow more than one user account. If you have the means to do so, I prefer to isolate roles as much as possible.

The reasons to not use root are many, but basically you want to give the user the minimal set of permissions needed; if an attacker gains access through your root account they can create new users, delete data, change configuration variables, and much, much more. If they gain access through a limited account that only has access to read and write to your CMS database, they can't create accounts, they can't delete other databases, and they can't insert malicious data in to other databases. They might mess with your CMS database, but it's the principle of limiting the damage at that point.


db2 performance issue - large number of IOPS how to determine problem?

The message you include from your db2diag.log shows that there is a potential space problem (i.e. the file system holding your data is filling up).  The monitoring data here shows that you are ranging between 89% and 94% over a 90 minute period with increases and decreases – this is probably associated with system temporary tables in the database.  It may be worthwhile to increase the size of the filesystem(s) holding the data since you're getting very close to running out of space.

This space utilization, however, has nothing to do with how much I/O activity (IOPS) the database is generating.

Generally when you have an I/O problem where you see a much different usage pattern than normal.  You mention that normally there the system is generating 300 IOPS, but lately it's been generating 5000 IOPS.

Assuming that the I/O activity can be traced to the file system(s) holding DB2 data, then you need to monitor the database during these periods of high I/O to find out what queries are active and are causing lots of physical I/O, and then do some investigation to figure out why.  You can use the db2top utility as well as many other monitoring tools to figure this out.

At that point it's a tuning exercise.  Are your bufferpools too small?  Was an index dropped?  Or is the cause a new query that needs to be reviewed / tuned?


I don't think adding a new column will be efficiently and you'll need to do more things like adding TRIGGERs to update the field and you'll get the newColumnvalue in every row for the table UserRatingsForProductsTable.

Information:

create Table Products(
  ProductId INT(4),
  ProductName VARCHAR(75),
  Cost DECIMAL(10,2),
PRIMARY KEY (`ProductId`));

CREATE TABLE UserRatingsForProductsTable(
  RatingId int(6) auto_increment,
  UserId int(5),
  ProductId int(4),
  RatingCategoryId int(2),
  Score decimal(5,2),
PRIMARY KEY (`RatingId`));

insert into Products select 20,'Product1',23.33;
insert into Products select 23,'Product2',223.33;
insert into Products select 34,'Product3',323.33;

insert into UserRatingsForProductsTable select 1,5006,20,1,4.5;
insert into UserRatingsForProductsTable select 2,5006,20,2,5.5;
insert into UserRatingsForProductsTable select 3,5600,23,1,3.5;
insert into UserRatingsForProductsTable select 4,7800,20,1,2.5;
insert into UserRatingsForProductsTable select 5,8900,34,1,4.0;
insert into UserRatingsForProductsTable select 6,8900,34,2,4.5;

Query:

SELECT
    p.ProductId,
    p.ProductName,
    p.Cost,
    ROUND(sum(URPT.Score)/(COUNT(DISTINCT URPT.RatingCategoryId)*COUNT(URPT.UserId)),1) as OverallRatingForThisProd
FROM Products as p
LEFT JOIN UserRatingsForProductsTable AS URPT ON (URPT.ProductId=p.ProductId)
GROUP BY p.ProductId
ORDER BY p.ProductId;

Try it on SQLFiddle.


How do I write portable SQL that refers to a linked server?

The name of your linked server doesn't have to be the server's name.  You can use a generic name.

EXEC master.dbo.sp_addlinkedserver
    @server = N'COMMONNAME',
    @srvproduct=N'MSDASQL',
    @provider=N'SQLNCLI',
    @provstr=N'DRIVER={SQL Server};SERVER=ACTUALSERVERNAME;UID=user1;PWD=rosebud567;',
    @catalog=N'database1'

Set up the linked server on each environment with the same name, but actually point them at different servers.


select records by a particular month wise in sql

You could use a WHERE clause to filter the desired rows:

SELECT   TOP (10) [CUSTOMERID],
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 101) AS DATEPART,
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 108) AS TIMEPART
FROM     [TP_CUSTOMERTRIPS]
WHERE    YEAR([ENTRYTRIPDATETIME]) = 2017
AND      MONTH([ENTRYTRIPDATETIME]) = 2
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME]
HAVING   COUNT(*) > 2;

CUSTOMERID | DATEPART   | TIMEPART
---------: | :--------- | :-------
  20124122 | 02/01/2017 | 02:47:01
  20108092 | 02/10/2017 | 00:37:09

db<>fiddle here

Or use a range of dates:

SELECT   TOP (10) [CUSTOMERID],
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 101) AS DATEPART,
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 108) AS TIMEPART
FROM     [TP_CUSTOMERTRIPS]
WHERE    [ENTRYTRIPDATETIME] >= CAST('20170201' AS DATETIME)
AND      [ENTRYTRIPDATETIME] <  CAST('20170301' AS DATETIME)
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME]
HAVING   COUNT(*) > 2;

db<>fiddle here


One other important aspect of audit tables that hasn't been highlighted thus far, is that in addition to keeping track of who did what to which record (often including before and after snapshots) audit tables are write-once.

Records in an audit table may not be updated or deleted (see note), only inserted.  This is sometimes imposed using triggers or maybe just application logic, but it's important in practice because it gives you "proof" that nothing's been tampered with in a way which is difficult to detect.

Note: Cleaning out old records from an audit table requires special processes which often have to be approved by management or auditors.


Summarise data into count per hour, average per day

date_tunc() is the key.
Transactions per hour
SELECT date_trunc('hour', datetime) AS hour
     , count(*) AS transactons
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

Average transactions per hour

... for weekday and weekend, respectively.

SELECT extract('ISODOW' from hour)::int/6 AS weekday_weekend
     , round(avg(transactions), 2) AS avg_transactions
FROM  (
   SELECT date_trunc('hour', datetime) AS hour
        , count(*) AS transactions
   FROM   tbl
   GROUP  BY 1
   ) sub
GROUP  BY 1;

0 .. weekday
1 .. weekend
ISODOW produces 1 - 7 for Mon - Sun. Sat & Sun are "weekend". Integer division x/6 returns 0 for weekdays and 1 for weekend.
Include hours without activity
Above queries only observe hours with actual transactions. To include hours without transactions (no rows int the table) with a value of 0 transactions, (outer) join to a an hourly raster / grid for all of the given period of 28 days. And use COALESCE(). Like:
SELECT extract('ISODOW' from hour)::int/6 AS weekday_weekend
     , round(avg(COALESCE(transactions, 0)), 2) AS avg_transactions
FROM  (
   SELECT generate_series(timestamp '2020-12-01' -- first day
                        , timestamp '2020-12-28' -- last day
                          + interval '1 day - 1 hour'
                        , interval '1 hour')
   ) grid(hour)
LEFT JOIN (
   SELECT date_trunc('hour', datetime) AS hour
        , count(*) AS transactions
   FROM   tbl
   GROUP  BY 1
   ) sub USING (hour)
GROUP  BY 1;

The expression timestamp '2020-12-28' + interval '1 day - 1 hour' produces the latest hour of interest for the time series.
db<>fiddle here
Further reading:

PostgreSQL: running count of rows for a query 'by minute'
Generating time series between two dates in PostgreSQL
How to get the number of days in a month?
Calculate working hours between 2 dates in PostgreSQL


That is because the main query doesn't reference division_by_zero anywhere.
The documentation says:

[...] PostgreSQL's implementation evaluates only as many rows of a WITH query as are actually fetched by the parent query.

That is zero in your case.
If you want the CTE to be executed, you could for example add a WHERE condition like
WHERE EXISTS (SELECT 1 FROM division_by_zero)


How to uninstall pgAdmin4 on Ubuntu 16.04?

sudo apt autoremove pgadmin4

works for ubuntu version 18.04


How to get current month and year of the database as a macro in SQL server?

Use the DATEPART and/or DATENAME functions.
If you want to reuse, you can create a function to return a string.

DECLARE @Updated VARCHAR(20)

-- numeric month
SET @Updated = 'Product_' + CONVERT(VARCHAR, DATEPART(MONTH, GETDATE())) + CONVERT(VARCHAR, DATEPART(YEAR, GETDATE()))
PRINT @Updated

Product_112018

-- 3 letter month
SET @Updated = 'Product_' + CONVERT(VARCHAR, LEFT(DATENAME(MONTH, GETDATE()), 3)) + CONVERT(VARCHAR, DATEPART(YEAR, GETDATE()))
PRINT @Updated

Product_Nov2018


mysql: free space after drop table with innodb_file_per_table=False

The only way to give the free space back to the OS is to dump all the tables, remove ibdata* and reload.

Meanwhile, there is a lot of free space in ibdata1.  When you do SHOW TABLE STATUS, you will probably see that most tables have the same, large, Data_free value.  This is actually how much is free in ibdata1.  As you add more data (to existing or new tables), Data_free will decrease until it gets close to zero; then the size of the ibdata1 file will increase.

Search around for other discussions of innodb_file_per_table.

As for doing a backup with low disk space, consider sending the data across the network to a disk on another machine.  For example, if the MySQL server is host1, do this on host2:

host2> mysqldump -h host1 ... >/a/local/dumpfile.sql

Before you rebuild, be sure to set innodb_file_per_table=ON since it needs to apply when you CREATE the TABLEs.


How do you update a large MySQL table that's on a server?

Given the clarifications on your comment, I would recommend 2 options:

Use LOAD DATA INFILE IGNORE to load the data directly from the filesystem. This will insert new domains and not touch the "old" ones, but it will not delete the ones that are removed. On the bright side, it will reduce IO a lot.
Go for your approach: use LOAD DATA INFILE on a new table, then RENAME the old and new table, which is a very fast operation. Inserting on smaller tables is faster than inserting on larger tables on InnoDB, but the whole table has to be written.

In both cases, there are a lot of things that I can recommend you to improve the inserting performance.

If you do not perform other write operations than the import, this would be one of the few cases in which I would recommend you to use MyISAM- its import performance is faster, as it does not have to deal with a transaction log, and you will not have to deal with corruption, as you will only use it as a read-only table. If the import fails, you can always repeat it from the file.

If you use InnoDB, on the other side, you can check for duplicates/missing records and modify them in parallel to match MyISAM performance but that only will be performant on the latest MySQL versions (>= 5.6).

Other things to have into account: disable temporarily the binary log; disable keys, try fixed-with format (for MyISAM), disable the doublewrite buffer and autocommit, augment the buffer pool and transaction log, make sure you use innodb_file_per_table=1 (for InnoDB). With proper tuning, for 5.5, I am able to insert a 50 million table (much wider than yours) in 2-3 minutes in MyISAM (without flushing the cache) and 11-12 minutes (durably) in InnoDB on spinning disks. 5.6 and 5.7 improved InnoDB performance closer to MyISAM.

Addendum: On other databases, I would recommend you to use a cursor to analyse the table row by row, but I do not have much faith on MySQL's programatic features.


Cannot Find Job Associated Active Email Notifications

It is probably either coming from a job or within a stored procedure somewhere. If it's a stored procedure, then after you find it, use the job search from below to look for the job calling that sp.

---- Search Job Steps
SELECT
J.name AS JobName,
JS.step_name AS StepName,
JS.step_id AS StepNumber,
JS.command

FROM
msdb.dbo.sysjobs J
INNER JOIN msdb.dbo.sysjobsteps JS
ON J.job_id = JS.job_id

WHERE
JS.command LIKE '%XXXXXX%'

---- Search Stored Procs, might want to loop through all databases

USE XXXXX

SELECT
DB_NAME() AS DatabaseName,
SCHEMA_NAME(O.schema_id) AS SchemaName
O.name AS SPName,
MO.definition AS SPDefinition

FROM
sys.sql_modules MO
INNER JOIN sys.objects O
ON MO.object_id = O.object_id

WHERE
MO.definition LIKE '%XXXXXX%'

Replace the WHERE clause with a short bit from the email itself that you have confidence would be in the body of the definition. It's not obvious from your post if some of that content in the email is dynamic or the same every time.


why are estimated rows exponentially increasing with each join?

As you're using hibernate this is going to be a bit cumbersome. You can 'guide' the optimizer by telling him how to do the joins.
For example select * from a inner hash join b on a.x = b.x will tell the optimizer to do a hash join. It could also be a merge join or a loop hint.
This might result into writing the sql queries manually. Using hints should also be one of the last things to try. Your tables might change over time and the hints might not apply/be good for the performance at a later stage (mostly when switching from development to production).


Restrict two specific column values from existing at the same time

One row per rare type
If you want to allow 0-n rows with type 'c' and only 0-1 rows for type 'a' and 'b' (each), you can combine a simple CHECK constraint with a partial unique index:
CREATE TABLE example (
  example_id    serial PRIMARY KEY,
  example_state "char" NOT NULL CHECK (example_state IN ('a', 'b', 'c'))
);

CREATE UNIQUE INDEX example_unique_exception_idx ON example (example_state)
WHERE example_state <> 'c'; -- column is not null;

Related answer:

PostgreSQL multi-column unique constraint and NULL values

An exlusion constraint would be a related concept, but it won't work with multiple entries for 'c'. You could use NULL instead of 'c' to make that work.

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

Also note the special data type "char" (in double-quotes!), which seems to be perfect for your simple purpose (unless it's just a simplification for the purpose of the question). It only occupies a single byte - as opposed to char(1), which needs at least 2 bytes on disk and 5 in RAM.
Singleton row with any rare type
To restrict to a single row overall that does not have type 'c', use a partial unique index on an expression:
CREATE UNIQUE INDEX example_single_exception_idx ON example ((example_state <> 'c'))
WHERE example_state <> 'c';


Yes, this is possible. Here is how

Create a copy of the table (you can use the partition management tool for that: https://sqlpartitionmgmt.codeplex.com/)
Switch the partition you want to "compress" into the copy
Rebuild the indexes on the copy with FF = 100.
Switch the partition back into the original table

It should be pretty trivial to automate this with a SQL agent job.

Step 3 is best done in this order:

Drop all secondary indexes
Rebuild the cluster index with FF = 100
Create all the secondary indexes again with FF=100

Also, if you have SQL Server Enterprise Edition, you could compress the data with PAGE compression. This would require a different process:

Create a copy of that table as above
Switch the data you want to compress into the copy
Compress the data by rebuilding indexes as above
ALTER the original table to enable page compression on the now empty partitions (the ones you switched out)
SWITCH the compressed data back into the original table.

With SQL Server 2014 Enteprise Edition, you can do the above as an online operation directly on the partition you want to compress. However, it is still a good idea to drop the secondary indexes in a copy first, because it will typically give you better data layout on the disk.


Can't start mongodb server using 'sudo service mongod start'

Running following code in terminal fixed my issue for error 62:

mv /var/lib/mongodb /var/lib/mongodb_backup
mkdir /var/lib/mongodb
chmod 700 /var/lib/mongodb
chown mongodb:daemon /var/lib/mongodb
systemctl restart mongodb


Oracle: update table with object type udt

Use an alias. It is mandatory when updating a table with an object type.
UPDATE

  t_alias

  Specify a correlation name (alias) for the table, view, or subquery to
  be referenced elsewhere in the statement. This alias is required if
  the DML_table_expression_clause references any object type attributes
  or object type methods.

Create table client (client_id number,person person_ty);

insert into client
values(1,person_ty('pinco pallo',
         address_ty('Boulevard street' ,'torino','to',10100)));
Commit;

...

update client c
set c.person.address.prov='MI'
where c.client_id=1;

1 row updated.


ERROR: 42803: column "wage_histories.wage" must appear in the GROUP BY clause or be used in an aggregate function

If you just need one row:

SELECT wage
FROM wage_histories
WHERE uid = ?
  AND start <= ?
ORDER BY start DESC
LIMIT 1


How to model an “is a” relationship in an ERD using Crow’s foot notation?

In ER modeling, IsA relationships go by the name "generalization/specialization".  A search on this should give you a few pointers.  Diagramming this pattern requires using a notation that was originally called "Enhanced ER modeling", although it's been a long time since this extension was introduced.

Here's the wikipedia article:

http://en.wikipedia.org/wiki/Enhanced_entity%E2%80%93relationship_model

and here are some sample diagrams, from googling "Enhanced ER Images"

https://www.google.com/search?q=enhanced+er+images&rlz=1C1CHFX_enUS586US586&espv=2&biw=1600&bih=775&tbm=isch&imgil=E75HgoIGspploM%253A%253B5D6WMu1kSMfWrM%253Bhttp%25253A%25252F%25252Fwww.siue.edu%25252F~dbock%25252Fcmis450%25252F4-eermodel.htm&source=iu&pf=m&fir=E75HgoIGspploM%253A%252C5D6WMu1kSMfWrM%252C_&usg=__MdUOOzgIiqsPSTBQjFkTyRuaAlQ%3D&dpr=1&ved=0CDEQyjc&ei=TpMBVeDoKMOjgwSerIOYBg#imgdii=_

I generally use the triangle notation.


Measuring the relative performance of queries given the assertion that "cost" isn't reliable

There are a variety of metrics you can use.

If you actually execute the queries, Oracle provides a ton of different metrics to evaluate relative performance.  In SQL*Plus, if you enable autotrace, you'll get a quick listing of a few execution statistics after you run a query.  In the vast majority of cases, if you are reducing logical I/O (the "consistent gets" metric in the autotrace output), you're improving response time.

SQL> set autotrace on;
SQL> select ename from emp;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

ENAME
----------
JAMES
FORD
MILLER

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |    84 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |    84 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        340  recursive calls
          0  db block gets
         71  consistent gets
          8  physical reads
          0  redo size
        715  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         14  rows processed

If you want to go into more detail, however, Oracle provides far more metrics.  If you query V$STATNAME, you'll see hundreds of statistics that are available.  V$SESSTAT contains the current value of every statistic for your session so you can grab the "before" data, run a query, then grab the "after" data and look at the change in virtually any performance statistic you might be interested in.  Tom Kyte put together a nice runstats package that automates this so that you don't have to write the test harness yourself.


Trying to run query and getting error: Error 1064 (42000)

select
       flwusertest_users.ID,
       flwusertest_users.display_name,
       flwusertest_users.user_email
from
      flwusertest_users
INNER JOIN
      flwusertest_usermeta
      ON flwusertest_users.ID = flwusertest_usermeta.user_id
WHERE (
      flwusertest_usermeta.meta_key = '_um_last_login'
      AND flwusertest_usermeta.meta_value NOT BETWEEN '1483228800' and '1520952132'
      )
AND <some_field> NOT IN (SELECT DISTINCT(flwusertest_users.ID)
                         FROM
                                flwusertest_users
                         INNER JOIN
                                flwusertest_em_bookings
                               ON flwusertest_users.ID=flwusertest_em_bookings.person_id
                        WHERE
                              flwusertest_em_bookings.booking_status=1);

There is more than one error in your sentence.

AND <some_field> NOT IN (SELECT DISTINCT(flwusertest_users.ID),
                                flwusertest_users.display_name,
                                flwusertest_users.user_email

You cannot return more than one field to use it with IN. You should return only

AND <some_field> NOT IN (SELECT DISTINCT(flwusertest_users.ID)
                         FROM
                                flwusertest_users
                         INNER JOIN
                                flwusertest_em_bookings
                               ON ...


How set balancing as main task for sharder MongoDB cluster?

My shards have 2662, 77, 77, 77 chunks.

If you are creating a new sharded collection and want to minimise the time to re-balance a large amount of data, the recommended approach would be to pre-split chunks in an empty collection before inserting the data. In your example, you would have pre-split based on the data distribution so each shard would have an equal number of chunks representing about 1/4 of the data.

Assuming you have chosen a suitable shard key for data distribution going forward, there should not be a significant number of regular chunk migrations unless you are adding or removing shards.

  Could anybody know any command which set balancing as the most task?

If your shards are backed by replica sets you can make the balancer more aggressive by disabling _secondaryThrottle, which reduces the write concern used for documents migrating between shards. By default, _secondaryThrottle is true which is equivalent to a {w:2} write concern: each document move during chunk migration propagates to at least one secondary before the balancer proceeds with the next document. In MongoDB 3.0+, there is also an option to configure an explicit writeConcern for the secondaryThrottle operation.

For example, to disable _secondaryThrottle from a mongos shell:

db.getSiblingDB('config').settings.update(
   { "_id" : "balancer" },
   { $set : { "_secondaryThrottle" : false } },
   { upsert : true }
)

Balancer setting updates will not take immediate effect if there is currently a balance round / migration in progress, so you may want to disable & re-enable the balancer.

For normal usage the default _secondaryThrottle behaviour is recommended to ensure documents have propagated and reduce the impact of balancing. If your replica sets have more than 3 members, you could also consider increasing the writeConcern to majority (see: Write Concerns for Replica Sets).


As far as the server and the network protocol are concerned, the result of a query and the binary COPY protocol are pretty much identical.

So you would choose COPY only if your client's PG library handles it better in your situation.


Composite Primary Key in multi-tenant SQL Server database

Having worked on a large-scale, multi-tenant system (federated approach with customers spread across 18+ servers, each server having identical schema, just different customers, and thousands of transactions per second per each server), I can say:

There are some folks (a few, at least) who will agree on your choice of GUID as the IDs for both "TenantID" and whatever entity "ID". But no, not a good choice. All other considerations aside, that choice alone will hurt in a few ways: fragmentation to start with, vast amounts of wasted space (don't say disk is cheap when thinking about enterprise storage — SAN — or queries taking longer due to each data page holding fewer rows than it could with either INT or BIGINT even), more difficult support and maintenance, etc. GUIDs are great for portability. Is the data generated in some system and then transferred to another? If not, then switch to a more compact data type (e.g. TINYINT, SMALLINT, INT, or even BIGINT), and increment sequentially via IDENTITY or SEQUENCE.
With item 1 out of the way, you really do need to have the TenantID field in EVERY table that has user data. That way you can filter anything without needing an extra JOIN. This also means that ALL queries against client-data tables are required to have the TenantID in the JOIN condition and/or WHERE clause. This also helps guarantee that you don't accidentally mix data from different customers, or show Tenant A data from Tenant B.

  I'm thinking to add TenantId as primary key along with Id. And possibly add OutletId too. So primary key(s) in sales order table will be Id, TenantId, OutletId.

Yes, you should have your clustered indexes on the client-data tables be composite keys, inclusive of TenantID and ID **. This also ensures that TenantID is in every NonClustered index (since those include the Clustered Index Key(s)) which you would need anyway since 98.45% of queries against client-data tables will need the TenantID (the main exception is when garbage collecting old data based on CreatedDate and not caring about TenantID).

No, you would not include FKs such as OutletID to the PK. The PK needs to uniquely identify the row, and adding in FKs would not help with that. In fact, it would increase chances for duplicated data, assuming that OrderID was unique for each TenantID, as opposed to unique per each OutletID within each TenantID.

Also, it is not necessary to add OutletID to the PK in order to ensure that Outlets from Tenant A don't get mixed up with Tenant B. Since all user-data tables will have TenantID in the PK, that means TenantID will also be in the FKs. For example, the Outlet table has a PK of (TenantID, OutletID), and the Order table has a PK of (TenantID, OrderID) and an FK of (TenantID, OutletID) which references the PK on the Outlet table. Properly defined FKs will prevent Tenant data from getting intermixed.

  Does the composite key order matter?

Well, here is where it gets fun. There is some debate as to which field should come first. The "typical" rule for designing good indexes is to pick the most selective field to be the leading field. TenantID, by its very nature, will not be the most selective field; the ID field is the most selective field. Here are some thoughts:

ID first: This is the most selective (i.e. most unique) field. But by being an auto increment field (or random if still using GUIDs), each customer's data is spread out throughout each table. This means that there are times when a customer needs 100 rows, and that requires almost 100 data pages read from disk (not fast) into the Buffer Pool (taking up more space than 10 data pages). It also increases contention on the data pages since it will be more frequent that multiple customers will need to update the same data page.

However, you typically do not run into as many parameter sniffing / bad cached plan issues as much since the statistics across the different ID values is fairly consistent. You might not get the most optimal plans, but you will be less likely to get horrible ones. This method essentially sacrifices performance (slightly) across all customers to gain the benefit of less frequent problems.
TenantID first: This is very much not selective at all. There might be very little variation across 1 million rows if you only have 100 TenantIDs. But the statistics for these queries is more accurate since SQL Server will know that a query for Tenant A will pull back 500,000 rows but that same query for Tenant B is only 50 rows. This is where the main pain-point is. This method greatly increases the chances of having parameter sniffing issues where the first run of a Stored Procedure is for Tenant A and acts appropriately based on the Query Optimizer seeing those statistics and knowing it needs to be efficient getting 500k rows. But when Tenant B, with only 50 rows, runs, that execution plan is no longer appropriate, and in fact, is quite inappropriate. AND, since the data is not being inserted in the order of the leading field, this method creates a lot more page splits (hence more fragmentation) in a short period of time than the other approach.

However, for the first TenantID to run a Stored Procedure, the performance should be better than in the other approach since the data (at least after doing index maintenance) will be physically and logically organized such that far fewer data pages are needed to satisfy the queries. This means less physical I/O, fewer logical reads, less contention between Tenants for the same data pages, less wasted space taken up in the Buffer Pool (hence improved Page Life Expectancy) etc.

There are two main costs to getting this improved performance. The first is not so difficult: you must do regular index maintenance to counteract the increased fragmentation. The second is a bit less fun.

In order to counteract the increased parameter sniffing issues, you need to separate the execution plans between Tenants. The simplistic approach is to use WITH RECOMPILE on procs or the OPTION (RECOMPILE) query hint, but that is a hit on performance that could wipe away all of the gains made by putting TenantID first. The method that I found worked best is to use parameterized Dynamic SQL via sp_executesql. The reason for needing the Dynamic SQL is to allow for concatenating the TenantID into the text of the query, while all other predicates that would normally be parameters are still parameters. For example, if you were looking for a particular Order, you would do something like:

DECLARE @GetOrderSQL NVARCHAR(MAX);
SET @GetOrderSQL = N'
  SELECT ord.field1, ord.field2, etc.
  FROM   dbo.Orders ord
  WHERE  ord.TenantID = ' + CONVERT(NVARCHAR(10), @TenantID) + N'
  AND    ord.OrderID = @OrderID_dyn;
';

EXEC sp_executesql
   @GetOrderSQL,
   N'@OrderID_dyn INT',
   @OrderID_dyn = @OrderID;

The effect this has is to create a reusable query plan for just that TenantID that will match the data volume of that particular Tenant. If that same Tenant A executes the stored procedure again for another @OrderID then it will reuse that cached query plan. A different Tenant running that same Stored Procedure would generate a query text that was different only in the value of the TenantID, but any difference in the query text is enough to generate a different plan. And the plan generated for Tenant B will not only match the data volume for Tenant B, but it will also be reusable for Tenant B for different values of @OrderID (since that predicate is still parameterized).

The downsides to this approach are:

It is a little more work than just typing in a simple query (but not all queries need to be Dynamic SQL, just the ones that end up having the parameter sniffing problem).
Depending on how many Tenants are on a system, it does increase the size of the plan cache since each query now requires 1 plan per TenantID that is calling it. This might not be an issue, but is at least something to be aware of.
Dynamic SQL breaks the ownership chain, which means read/write access to tables cannot be assumed by having EXECUTE permission on the Stored Procedure. The easy but less secure fix is just to give the User direct access to the tables. This is certainly not ideal, but that is usually the trade-off for quick and easy. The more secure approach is to use Certificate-based security. Meaning, create a Certificate, then create a User from that Certificate, grant that User the desired permissions (a Certificate-based User or Login cannot connect to SQL Server on its own), and then sign the Stored Procedures that use Dynamic SQL with that same Certificate via ADD SIGNATURE.

For more information on module signing and Certificates, please see: ModuleSigning.Info

Please see UPDATE section towards the end for additional topics related to the issue of dealing with mitigating statistics issues resulting from this decision.

** Personally, I really dislike using just "ID" for the PK field name on every table as it is not meaningful, and it is not consistent across FKs since the PK is always "ID" and the field in the child table has to include the parent table name. For example: Orders.ID -> OrderItems.OrderID. I find it much easier to deal with a data model that has: Orders.OrderID -> OrderItems.OrderID. It is more readable, and cuts down on the number of times you will get the "ambiguous column reference" error :-).

UPDATE

Would the OPTIMIZE FOR UNKNOWN Query Hint (introduced in SQL Server 2008) help with either ordering of the composite PK?

Not really. This option does get around parameter sniffing issues, but it merely replaces one problem with another. In this case, rather than remembering the statistical info for the parameter values of the initial run of the stored procedure or parameterized query (which is definitely great for some, but potentially mediocre for some, and potentially horrible for some), it uses a general statistic of data distribution to estimate row counts. This is hit-or-miss as to how many (and to what degree) queries will be affected positively, negatively, or not at all. At least with parameter sniffing some queries were guaranteed to benefit. If your system has Tenants with widely varied data volumes, this could potentially hurt performance for all queries.

This option accomplishes the same thing as copying input parameters to local variables and then using the local variables in the query (I have tested this but no room for that here). Additional info can be found in this blog post: http://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/. Reading the comments, Daniel Pepermans came to a conclusion similar to mine regarding the use of Dynamic SQL that has limited variation.
If ID is the leading field in the Clustered Index, would it help / suffice to have a Non-Clustered Index on (TenantID, ID), or just (TenantID) to have accurate statistics for queries that process many rows of a single tenant?

Yes, it would help. The large system that I mentioned working on for years was based on an index design of having the IDENTITY field as the leading field because it was more selective and reduced parameter sniffing issues. However, when we needed to operations against a good portion of a particular Tenant's data, the performance did not hold up. In fact, a project to migrate all data into new databases had to be put on hold because the SAN controllers got maxed out in terms of throughput. The fix was to add Non-Clustered Indexes to all tenant data tables to be just (TenantID). No need to do (TenantID, ID) since ID is already in the Clustered Index so the internal structure of the Non-Clustered Index was naturally (TenantID, ID).

While this did solve the immediate issue of being able to do TenantID-based queries much more efficiently, they still weren't as efficient as they could have been if it were the Clustered Index that were in that same order. And, now we had yet one more index on every table. That increased the amount of SAN space we were using, increased the size of our backups, made the backups take longer to complete, increased the potential for blocking and deadlocks, decreased performance on INSERT and DELETE operations, etc.

AND we were still left with the general inefficiency of having a Tenant's data spread out across many data pages, intermixed with many other Tenant's data. As I mentioned above, this increases the amount of contention on these pages, and it fills up the Buffer Pool with lots of data pages that have 1 or 2 useful rows in them, especially when some of the rows on those pages were for clients that were inactive but hadn't been garbage collected yet. There is much less potential for re-use of the data pages in the Buffer Pool in this approach, so our Page Life Expectancy was pretty low. And that means more time going back to disk to load more pages.


will return two IDENTICAL rows with no ability to differentiate which one corresponds to which table/key.

Then don't use information_schema:

select nsp.nspname as constraint_schema,
       c.conname as constraint_name,
       format('%I.%I', ts.nspname, t.relname) as target_table,
       pg_get_constraintdef(c.oid) as constraint_definition
from pg_class t
  join pg_constraint c on t.oid = c.conrelid
  join pg_namespace nsp on t.relnamespace = nsp.oid
  join pg_namespace ts on t.relnamespace = ts.oid
where c.contype in ('f')
 and c.conname = 'fk_fooid';

Example: http://rextester.com/DAR30737


With rare exceptions, do not create identical tables split by year or month or customer or whatever.  If a table is getting slower as it gets bigger, then you don't have the right indexes and/or you need to redesign the queries.  Even a billion rows in a table is not 'unmanageable'.

Clients, bills, banks, transactions -- sounds like about 4 tables, plus some tables for Normalization.  A bill might contain a client_id column, but not the client's name, address, etc.


Excluding database objects from a DacPac database deployment?

Instead of using a DACPAC, you might find generating database scripts more flexible for your needs if you want to exclude specific entities. Here's a good walkthrough on how to do it: Methods to script SQL Server database objects


How to update Column related to Row Number

UPDATE contact
SET ContactNumber = (@rownum := 1 + @rownum) + 500
WHERE 0 = (@rownum:=0)
-- for a given client, for example, Client_Id=123, use
-- WHERE Client_Id = 123 + (@rownum:=0)
ORDER BY id;


Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT?

If the SQLCAT team says that FK validation is done using read-committed isolation, then they must know what they're talking about. Emphasis on validation. The real question is Why would a report trigger FK validation? Validation occurs on writes, and reports are supposed to be reads. Either your reports are causing writes, in which case snapshot isolation levels will help nothing, either the cause of the deadlock is different.

The only way to make progress is to capture the deadlock graph.

As for the other question, how can you check if you operate under snapshot isolation: look in sys.dm_tran_active_snapshot_database_transactions.


I suspect that rows in sys.partitions is 0 due to not being committed yet. But this does not mean that SQL Server is unaware of what will go there if the Transaction does commit. The key is in remembering that all operations go through the Buffer Pool (i.e. memory) first, regardless of COMMIT or ROLLBACK of the operation. Hence, we can look in sys.dm_os_buffer_descriptors for that info:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT  --OBJECT_NAME(sp.[object_id]) AS [TableName], sdobd.*, '---', sp.*, '---', sau.*
       SUM(sdobd.[row_count]) AS [BufferPoolRows],
       SUM(sp.[rows]) AS [AllocatedRows],
       COUNT(*) AS [DataPages]
FROM sys.dm_os_buffer_descriptors sdobd
INNER JOIN  sys.allocation_units sau
        ON sau.[allocation_unit_id] = sdobd.[allocation_unit_id]
INNER JOIN  sys.partitions sp
        ON  (   sau.[type] = 1
            AND sau.[container_id] = sp.[partition_id]) -- IN_ROW_DATA
        OR  (   sau.[type] = 2
            AND sau.[container_id] = sp.[hobt_id]) -- LOB_DATA
        OR  (   sau.[type] = 3
            AND sau.[container_id] = sp.[partition_id]) -- ROW_OVERFLOW_DATA
WHERE   sdobd.[database_id] = DB_ID()
AND     sdobd.[page_type] = N'DATA_PAGE'
AND     sp.[object_id] = (SELECT so.[object_id]
                          FROM   sys.objects so
                          WHERE  so.[name] = 'TestDump')

If you want to see the details, uncomment the first row of items in the SELECT list, comment out the remaining 3 lines.

I tested by running the following in one Session and then repeatedly running the query above in another.

SELECT so1.*
INTO   dbo.TestDump
FROM   sys.objects so1
CROSS JOIN sys.objects so2
CROSS JOIN sys.objects so3;


count(column) does not count null values so you could use count() with a case statement that returns h.USERID or null.

, COUNT(DISTINCT CASE WHEN h.CUSTOM2 = 'Work Web' THEN h.USERID ELSE NULL END) UserCountWorkWeb
, COUNT(DISTINCT CASE WHEN h.CUSTOM2 = 'OUTLOOK' THEN h.USERID ELSE NULL END) UserCountOutlook


When are dimension tables still needed using clustered columnstore indexing?

I don't think that your question is applicable to any RDBMS that supports columnar storage. I'm writing my answer from the point of view of SQL Server and most of the reasons depend on implementation details specific to SQL Server.

  When does one still need dimension tables when using CCI?

1. The volume of changes to the dimension table makes updating the CCI fact table impractical

With fact tables of 500 M rows you might need to update hundreds of millions of rows in a CCI if some of the dimension columns change in an unlucky way. The only practical ways that I know to do this are to rewrite the whole table or to do a delete + insert. For the delete + insert approach you'll likely need to write the data for all columns to a staging area, wait for the serial delete queries to finish (unless you can delete by partition), read all of the columns for all of the rows for rowgroups which might contain a row that needs to be changed, and so on. It can be a hassle to code and pretty expensive to convert the data. The problem gets worse as your fact table gets wider.

2. The length and number of string columns makes CCI compression impractical due to memory limits

Memory grant requests for string columns can get out of control depending on how you're building the CCI. For example, a REBUILD of a VARCHAR(8000) column requests 6.5 GB per DOP and scales down with column length. Memory grant requests time out for CCI inserts at 25 seconds (as far as I know there's no way to change this). This means that some of your CCI insert queries could start directly writing to the delta store (along with deadlocking and other bad things) if you don't have enough memory to perform the compression.

3. Your ETL or maintenance processes aren't designed to prevent or clean up delta stores

You mention "massive compression" in your question, but data in delta stores isn't compressed. If your ETL process creates a heap and later compresses that data into columnstore format then you could be using a lot more temporary space for staging than you're used to. If you do a lot of parallel inserts into partitioned tables you could end up with thousands or more delta stores for which data won't be compressed, and so on.

4. The dimension table has a lot of unique, long strings

SQL Server 2016 is limited to 16 MB dictionary sizes per column. If a column has too many unique values then you can exceed that limit and the rowgroup will be split up due to dictionary pressure. Adding string columns to an existing CCI fact table can result in smaller compressed rowgroups which can reduce the effectiveness of compression and querying performance.


In PostgreSQL, will VACUUM reset the dead tuple count (n_dead_tup in pg_stat_all_tables)?

Yes, it resets the value. But it is from the stats collector, not vacuum.

postgres=# create table x (a int);
CREATE TABLE
postgres=# insert into x values (1),(2),(3),(4);
INSERT 0 4
postgres=# delete from x where a%2 =0;
DELETE 2

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_all_tables where relname='x';
-[ RECORD 1 ]-------+----------
relid               | 588915846
schemaname          | public
relname             | x
seq_scan            | 1
seq_tup_read        | 4
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 4
n_tup_upd           | 0
n_tup_del           | 2
n_tup_hot_upd       | 0
n_live_tup          | 2
n_dead_tup          | 2
n_mod_since_analyze | 6
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

postgres=# vacuum x;
VACUUM

postgres=# select * from pg_stat_all_tables where relname='x';
-[ RECORD 1 ]-------+------------------------------
relid               | 588915846
schemaname          | public
relname             | x
seq_scan            | 1
seq_tup_read        | 4
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 4
n_tup_upd           | 0
n_tup_del           | 2
n_tup_hot_upd       | 0
n_live_tup          | 2
n_dead_tup          | 0
n_mod_since_analyze | 6
last_vacuum         | 2016-01-12 16:49:32.248731+08
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 1
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0


Virtulization is an option if it won't work on Windows 7.


How do I rename my local SQL development server?

How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

Rename the computer.
Restart the computer - SQL Server will recognize the new name during startup, but the sys.servers table will still contain the old name (you can run SELECT @@SERVERNAME to confirm it).
Run the sp_dropserver and sp_addserver procedures to update this table.

According to BOL, the former requires the ALTER ANY LINKED SERVER permission and the latter - membership in the setupadmin server role; however, brief sp_helptext and Google investigation suggests that actually they both require this ALTER ... permission.
Restart SQL Server and use SELECT @@SERVERNAME to verify that the previous step has been successful.

Apart from technical considerations explained in the How to: Rename a Computer ... article (e.g. computers involved in replication cannot be renamed), the thing I would worry most about is finding and changing all the connection strings:

the ones in app.config and web.config files,
the ones hard-coded in some prototype applications,
the ones hidden somewhere deep in configuration of SharePoint, Analysis Services and other systems,
the ones embedded in Excel files or Access databases).


Here's your query:

    SELECT
        concat("fund_key: ",stock_key,", units: ",sum(units),", price: ",round(sum(units*price)/sum(units),2)) as result
    FROM PRUEBA.new_table2
    GROUP BY stock_key,user_key;

Result:


Load balancing writes in SQL Server is hard. Are you sure this is actually a requirement? If not, and the servers just ended up this way because the implementers didn't understand the requirement, I would start with a single copy of the database that both web applications use, instead of trying to give each database an independent copy.

If it really is the requirement, then you could look into Distributed Partitioned Views, Peer-to-Peer Replication, Bidirectional Replication, or Merge Replication, all of which allow you to combine writes or otherwise share data from multiple instances. My guess, though, is that these solutions are overkill for your actual business requirements.


NULL is special. It is neither equal nor non-equal.

Given the same temp tables in your example, consider the results from these queries:

--returns 3 rows
SELECT * FROM #Data;

--returns 2 rows
SELECT * FROM #Data
WHERE answer NOT IN (SELECT answer1 FROM #GoodAnswer);

--returns 0 rows
SELECT * FROM #Data
WHERE answer IN (SELECT answer1 FROM #GoodAnswer);

This is because the definition of  NULL is that it is unknown. Since you don't know the value of it, you can't evaluate it's equality or inequality. Instead, you have to explicitly query for IS NULL or IS NOT NULL.


MySQL replication - replicate to multiple DBs on slave?

The first obvious question is why would you want to do this?  It's probably a bad idea and completely unnecessary, since you can join across databases:

SELECT * FROM db1.table1 JOIN db2.table2 ON db1.table1_id = db2.table2_id WHERE ...

Next, in order to understand why this doesn't work, you first need to understand the basics of MySQL replication.  In summary:

On the master, a statement is executed
On the master, that statement is written to the binary log
On the slave, the SLAVE IO_THREAD connects to the master, reads the binary log from a known position, and writes the queries to the local relay log
On the slave, the SLAVE SQL_THREAD reads the relay log and executes the statements.

You seem to be expecting that by having multiple rewrite statement in place for the same database, that the SQL thread will execute the relay log statements twice.  This is not the case.  From the documentation, replicate-rewrite-db simply changes the value of USE db as it is being written to the relay log, so one or the other of your statements is being evaluated, but not both.

To accomplish what you are trying to do, there are a couple options that come to mind

Run multiple instances of MySQL on the same host on different ports.  This can be done with mysqld_multi.  This is very easy and can fit in with your rewrite rules.
Use the BLACKHOLE storage engine with AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers on the master.  This is very ugly, but could work for certain use cases

The way #2 would look is like this:

On the master server, create the second database and a copy of the table that uses the BLACKHOLE storage engine:

mysql> create database db1;
Query OK, 1 row affected (0.03 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

master> use db1;
Database changed
mysql> create table foo ( id int auto_increment primary key, name varchar(32) not null ) engine=innodb;
Query OK, 0 rows affected (0.16 sec)

mysql> use db2;
Database changed
master> create table foo ( id int auto_increment primary key, name varchar(32) not null ) engine=BLACKHOLE;
Query OK, 0 rows affected (0.05 sec)

master> use db1;
Database changed
master> CREATE TRIGGER insert_into_blackhole AFTER INSERT ON foo FOR EACH ROW INSERT INTO db2.foo SELECT * FROM db1.foo WHERE id = NEW.id;

master> CREATE TRIGGER update_into_blackhole AFTER UPDATE ON foo FOR EACH ROW UPDATE db2.foo2 SET id = NEW.id, name = NEW.name WHERE id = OLD.id;

master> CREATE TRIGGER delete_from_blackhole AFTER DELETE ON foo FOR EACH ROW DELETE FROM db2.foo2 WHERE id = OLD.id;

On the slave, create the tables as their regular storage engine.

INSERT/UPDATE/DELETE operations into master://db1.foo will get written to the binary log for db2.foo and then get replayed on the slave, where it will exist in both places.

There are a lot of caveats with this, of which here are only a few.  I recommend extensive testing.

ONLY INSERT/UPDATE/DELETE operations will get reproduced in both places.
ALTER TABLE operations will not get reproduced, so changes will have to be made to both the BLACKHOLE and the main table manually.
TRUNCATE TABLE operations will not get reproduced.
It will be very difficult to detect out-of-sync data.
Bulk operations will block until the trigger has completed.  BLACKHOLE is quite fast because it's essentially /dev/null, but in large enough batches simply generating the statements may take a long time.  If you perform a bulk INSERT/UPDATE/DELETE of 1000 rows, that will generate 1000 individual statements to execute against the BLACKHOLE table.  You can improve on this by setting up a relay slave.
Because of the conversion from bulk updates to individual row updates, the slave may have a tendency to lag behind.

Using a relay slave:
Your replication topology would look like this:

master -> relay -> slave

In the relay slave topology, you'd end up with an unaltered master and instead, the BLACKHOLE configuration would be on the relay slave.  Your regular slave that contained the copies would be a slave of the relay.  This would prevent any slowdown of the writes on the master.  Note, due to the way triggers are applied on slaves, the relay slave topology will only work with STATEMENT based replication.

CAVEAT: I'm certain I've missed some critical detail, so test this extensively.

My advice: Don't do it.  I can't think of any good reasons why you would need to have the same exact data replicated in two places on the same server.


Oracle database silent install failing on shared pool/sga size

After testing your code, found the issue and modified the command which works fine for me.

[oracle@oracle templates]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE -gdbname oracdb -sid oracdb -emConfiguration NONE -storageType ASM -asmsnmpPassword grid -diskGroupName +DATA  -initParams memory_max_target='1300M',memory_target='1300M',sga_max_size='1300M',sga_target='0M',pga_aggregate_target='0M',pga_aggregate_limit='0M'
Enter SYS user password:

Enter SYSTEM user password:

Registering database with Oracle Restart
5% complete
Copying database files
7% complete
8% complete
9% complete
10% complete
DBCA Operation failed.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/oracdb/oracdb12.log" for further details.

The error log.

[oracle@oracle templates]$ cat /u01/app/oracle/cfgtoollogs/dbca/oracdb/oracdb12.log
The specified shared pool size or SGA size "80MB" does not meet the recommended minimum size requirement "331MB". This will make database creation fail. Do you want to continue?

Unique database identifier check passed.

+DATA has enough space. Required space is 6262 MB , available space is 10195 MB.
File Validations Successful.
Registering database with Oracle Restart
DBCA_PROGRESS : 5%
Copying database files
DBCA_PROGRESS : 7%
DBCA_PROGRESS : 8%
ORA-01078: failure in processing system parameters

DBCA_PROGRESS : 9%
DBCA_PROGRESS : 10%
ORA-01034: ORACLE not available

ORA-01034: ORACLE not available

DBCA_PROGRESS : DBCA Operation failed

I faced the same error as you have.

[oracle@oracle admin]$ oerr ora 01078
01078, 00000, "failure in processing system parameters"
// *Cause:  Failure during processing of INIT.ORA parameters during
//        system startup.
// *Action:  Further diagnostic information should be in the error stack.

Further information is the error message ORACLE not available, which means instance creation is failed.

During the database creation, DBCA creates a dummy init.ora files under the ORACLE_BASE/cfgtoollogs/dbca/<oracle_SID>/ directory.

Let's see what parameters were used in the init files for that instance.

[oracle@oracle oracdb]$ cat initoracdbTempOMF.ora
##############################################################################
# Copyright (c) 1991, 2013 by Oracle Corporation
##############################################################################

###########################################
# Cache and I/O
###########################################
db_block_size=8192

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=""
db_name="seeddata"

###########################################
# File Configuration
###########################################
db_create_file_dest="+DATA"
db_recovery_file_dest="+DATA"
db_recovery_file_dest_size=4560m

###########################################
# Miscellaneous
###########################################
compatible=12.1.0.2.0
diagnostic_dest=/u01/app/oracle
memory_max_target=1300M
memory_target=Unexpected error!!!
pga_aggregate_limit=0M

###########################################
# Network Registration
###########################################
local_listener=LISTENER_ORACDB

###########################################
# Processes and Sessions
###########################################
processes=300

###########################################
# SGA Memory
###########################################
sga_max_size=1300M
sga_target=0M

###########################################
# Security and Auditing
###########################################
audit_file_dest="/u01/app/oracle/admin/oracdb/adump"
audit_trail=db
remote_login_passwordfile=EXCLUSIVE

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=oracdbXDB)"

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=0M

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1

db_unique_name="oracdb"
_diag_hm_rc_enabled=false
control_files="/u01/app/oracle/cfgtoollogs/dbca/oracdb/tempControl.ctl"
enable_pluggable_database=true

  MEMORY_TARGET=Unexpected Error!

There was the main problem. Then I tried to edit the command and removed that size delimiter M then ran the command once again.

[oracle@oracle templates]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE -gdbname oracdb -sid oracdb -emConfiguration NONE -storageType ASM -asmsnmpPassword grid -diskGroupName +Data  -initParams memory_max_target='1300M',memory_target='1300'
Enter SYS user password:

Enter SYSTEM user password:

Cleaning up failed steps
5% complete
Registering database with Oracle Restart
11% complete
Copying database files
12% complete
14% complete
21% complete
27% complete
34% complete
44% complete
Creating and starting Oracle instance
46% complete
51% complete
52% complete
53% complete
58% complete
62% complete
63% complete
66% complete
Completing Database Creation
70% complete
73% complete
76% complete
86% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/oracdb/oracdb13.log" for further details.
[oracle@oracle templates]$

And it has completed successfully.

The memory target value appeared as memory_target=1300m.

I don't know why the DBCA has made that error. Same time other parameters are using same values(with the character M) as valid value.

If you wish to use automatic memory management and provide memory related parameters with templates in DBCA silent mlde, you can use -automaticMemoryManagement true(which forces to use memory target hence its not necessary to use memory parameters)  along with -totalMemory <memory allocated for Oracle in MB>.

Example: ...-automaticMemoryManagement true -totalMemory 1650 is equivalent to ... -initParams memory_target = '1650'. Size is expressed in MB.

sga_target and sga_max_target parameters are not supported on command line.
Details are in MOS document(2212857.1)


I would try to go with Oracle without ODBC. ODBC adds one more layer and both Oracle and ODBC usually are not as well tested as MySQL or PostgreSQL. Like others noticed the problem is with checking for OCIEnvCreate in -lclntsh... no. The most probably configure cannot find required libraries. You should try two things:
 1. Check config.log for error messages about OCIEnvCreate.
 2. Shouldn't httpd's configure command line say LDFLAGS=-L$ORACLE_HOME/lib? Of course I install instant client from RPMs but libclntsh.so is in lib/ subdirectory.


Tools and best practice for reporting/presentation database

It seems you are referring to a concept called a data warehouse.  Data Warehousing is a huge topic, with a few main types of architecture: star schema, snowflake, and data vault. Googling these might give you some ideas on how to proceed.


How do I set MySQL's "character_set_server" and "collation_server" variables when creating a database?

Stick these in the [mysqld] section of your MySQL config file:

skip-character-set-client-handshake
collation-server=utf8_unicode_ci
character-set-server=utf8

The documentation for this is here.


Optimize two sql sentences adding indices

If you're not interested in rows that aren't 255, you could use a filtered index:

CREATE INDEX ix255 ON Code (AggregationLevelId)
INCLUDE (Serial, CodeId)
WHERE CommissioningFlag = 255;

This means it will only index the ones that have 255, in AggregationLevelId order, including the values of Serial and CodeId because you need to count/return those.

If you cannot (or do not want to) use a filtered index, try the following (as originally suggested in a comment):

CREATE INDEX pick_a_name_for_the_index
ON Code (CommissioningFlag, AggregationLevelId)
INCLUDE (Serial, CodeId);


How to select all attributes of object type

I think this might be the kind of thing you need.

WITH get_detail
  AS (SELECT get_addr(col1) AS obj_type from my_test)
SELECT x.obj_type.district_name FROM get_detail x
/

This is what I created to test it out.

CREATE OR REPLACE TYPE my_ADDRESS_DETAIL_T AS OBJECT
(
 apt_number        NUMBER,
 district_name     NVARCHAR2(40),
 zipcode           NUMBER
)
/
create table my_test
(col1 number,
 addr_detl my_address_detail_t)
/
INSERT INTO my_test
values(1, my_address_detail_t(1,'HOME',12345))
/
commit
/

And the function;

CREATE OR REPLACE FUNCTION my_get_addr(addr_id IN NUMBER)
RETURN my_address_detail_t IS
addr_t my_address_detail_t;
BEGIN
SELECT addr_detl
  INTO addr_t
  FROM my_test
 WHERE col1 = addr_id;

RETURN addr_t;
END;

You will obviously need to define a join somewhere for get_detail to have the correct col1 to retrieve data for. Im not really sure of the full requirement so haven't tried that bit but this should give you the basics.


You can use a SUM(CASE) statement and a GROUP BY in this way:

select
    date,
    sum(case when category = 1 then price else 0 end) as blue,
    sum(case when category = 2 then price else 0 end) as red
from
    data d
group by
    date


You can use two nearly identical subqueries in your original SELECT:

SELECT
  kk_data_id,
  kk_date,
  kk_egg,
  kk_cash,
  kk_in,
  kk_out,
  kk_expenditure,
  (  SELECT kk_egg              -- You get kk_egg from the "previous" row
       FROM kknagar_data k2
      WHERE k2.kk_data_id < k1.kk_data_id
   ORDER BY kk_data_id DESC
      LIMIT 1
  ) AS prev_kk_egg,
  (  SELECT kk_cash              -- You get kk_cash from the "previous" row
       FROM kknagar_data k2
      WHERE k2.kk_data_id < k1.kk_data_id
   ORDER BY kk_data_id DESC
      LIMIT 1
  ) AS prev_kk_cash
FROM
  kknagar_data k1
  INNER JOIN kknagar_rates kr on kr.kk_rate_id = k1.kk_rate_id ;

Having two subqueries looking for two different columns on the same row looks like a waste of time, but it basically mimics what you're doing right now and it works.

You can check it at SQLFiddle.

Note on aliases

You have one table (kknagar_data) that is accessed for three different purposes. First you use it to get the list of "normal records". That's when this table is used as kknagar_data k1 (which can also be written as kknagar_data AS k1). The k1 is just an alias for the table, within that specific context.

Then, in your subqueries, you also refer to the same table, but for a different purpose. In that case, I've used a different alias (FROM kknagar_data **k2**), so that I can refer to the same table with two different contexts. The condition k2.kk_data_id < k1.kk_data_id means: look at the same table (k2 and k1 are actually the same table), but right now (k2) choose the ids that are less than the ones for the "normal records" (k1).

Both subqueries use k2 as an alias because I just copied and pasted the code, and changed the column name. That's possible because the symbol k2 is local to each subquery, and cannot be used outside of its context. That's the same as using the same variable name in two adjacent blocks of a program written in, let's say, Java. It is not advisable to use the same alias when one block contains the other, because you would have, at least, ambiguities.

Aliases are needed when you refer more than once to the same table. For instance, when you have self-referencing tables (i.e.: a table persons where you have a parent-child relationship), or when you want to access "a record of the same table somehow related to the one you already have", such as in your case. Sometimes it is just used for simplicity (k1 is shorter than kknagar_data).

Window functions as a (better) alternative

Databases allowing for WINDOW functions would let you to use the lag() function to have the same effect, in a more efficient manner. Check Introduction to LEAD and LAG for Microsoft SQL Server. You can achieve the same with PostgreSQL, or also with the latest versions of MariaDB. MySQL 8.0 is going to have window functions as well.


Passing an array of integers as a parameter of an (inline) user defined function (SQL Server)

On SQL-Server 2014 you can take advantatge of TYPE (alias data type), and pass it as a parameter of an Inline UDF simulating an array of integer, or any other tabular data.

Keep in mind you must use READONLY.

IF OBJECT_ID('dbo.MyTest') IS NOT NULL
    DROP TABLE dbo.MyTest;
GO

IF OBJECT_ID('dbo.MyFunction') IS NOT NULL
    DROP FUNCTION dbo.MyFunction;
GO

IF TYPE_ID ('dbo.Mytype') IS NOT NULL
    DROP TYPE dbo.Mytype;
GO

CREATE TYPE dbo.Mytype AS TABLE (Id int);
GO

CREATE TABLE dbo.MyTest (Id int, Val char(3));
INSERT INTO dbo.MyTest VALUES (1,'V1'),(2,'V2'),(10,'V10'),(11,'V11');
GO

CREATE FUNCTION dbo.MyFunction(@Table MyType READONLY)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM   MyTest
    WHERE  Id IN (SELECT Id FROM @Table)
);
GO

DECLARE @myTable Mytype;
INSERT INTO @myTable(Id) VALUES(1),(2),(3),(4),(5);

SELECT * FROM MyFunction(@myTable);

| Id | Status |
|:--:|:------:|
|  1 |   V1   |
|  2 |   V2   |

dbfiddle here


The DBCC WHATIF command (also see http://devondba.blogspot.co.uk/2014/03/dbcc-optimizerwhatif.html) can be used to simulate a certain hardware configuration for the current session:

dbcc OPTIMIZER_WHATIF ({property/cost_number | property_name} [, {integer_value | string_value} ])

The MemoryMBs property must be set to some value.

After that it appears to be necessary to recompile the query in question (for example using DBCC FREEPROCCACHE on a dev box, or using OPTION (RECOMPILE) if that hint is acceptable for later production use).

It is easy to tell that the WHATIF value "took" by the fact that it influences the <OptimizerHardwareDependentProperties> values in the XML execution plan.


Simple fix, was to include the database and user flags while connecting.


converting then outputting varbinary(max) FOR XML PATH

Don't know that you're going to get anything much simpler than this:

DECLARE @x TABLE(a VARBINARY(64));

INSERT @x(a)
SELECT 0x480069002100
UNION ALL
SELECT CONVERT(VARBINARY(64),(REPLICATE(N'x',32)));

SELECT CONVERT(NVARCHAR(32), a) FROM @x FOR XML PATH('tag');

Results:

<tag>Hi!</tag>
<tag>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx</tag>

I'll leave all the other XML arrangement you may want to do with the output, but essentially IMHO you should convert to (the right!) string output before putting the data anywhere near XML.


First, you need CREATE DATABASE permissions to create a database. The db_creator role may be best for you in this case. Second, you need to script the restore.

For example:

RESTORE DATABASE someDatabase
FROM DISK = N'D:\sql\backups\myDatabase.bak'
WITH STATS = 10 ;
GO


Disable console output when executing a SQL file using MySQL source command

Here is the problem. The OS has two modes to print things

stdout
stderr

The --silent  only affects stdout. How do you nail stderr?

Try one of the following, and see if it works:

mysql --user=myusername --password=mypassword --silent --force -b 2> nul
mysql --user=myusername --password=mypassword --silent --force -b --tee=nul

Give it a Try !!!

CAVEAT : I have dealt with something this before when answering a question about mysqldump : How to log verbose output from mysqldump?


How can I know whether postgres is currently running synchronous or asynchronous replication?

If:

SHOW synchronous_standby_names;

on the master that's expected to have synchronous replicas reports a non-empty value, then synchronous replication is active.


AT TIME ZONE employs some logic to calculate Daylight Savings Time. DST offset values are not immutable (they are subject to change via windows updates) and are contained externally in the Windows registry, so therefor the AT TIME ZONE function cannot be deterministic since it is relying on external data.

Similarly, this is why sys.time_zone_info is a view and not a static reference table, it needs to be calculated depending on the registry values which have the most up to date timezone information.


SQL Server 2005: Full-text search space requirements

Here is some old code I wrote for SQL 2000.  It still works on SQL 2005.  You can use this to come up with a rough estimate of how much space you are using per document, then multiply that by your estimated number of documents.

   select 'CatalogName'   = left([name],30),
          'Status'        = case(FULLTEXTCATALOGPROPERTY ([name],'populatestatus'))
                              when 0 then 'Idle'
                              when 1 then 'Full population in progress'
                              when 2 then 'Paused'
                              when 3 then 'Throttled'
                              when 4 then 'Recovering'
                              when 5 then 'Shutdown'
                              when 6 then 'Incremental population in progress'
                              when 7 then 'Building index'
                              when 8 then 'Disk is full.  Paused.'
                              when 9 then 'Change tracking'
                              else 'Unknown'
                            end,
          'ItemCount'     = FULLTEXTCATALOGPROPERTY (name,'itemcount'),
          'IndexSize(MB)' = FULLTEXTCATALOGPROPERTY (name,'indexsize'),
          'UniqueWords'   = FULLTEXTCATALOGPROPERTY (name,'uniquekeycount'),
          'ErrorLogBytes' = FULLTEXTCATALOGPROPERTY (name,'logsize'),
          'Location'      = left(s.path,50)
   from sysfulltextcatalogs s