SQL Server TDE - Database backups and post restore behaviour

I believe there is more to the story than we're getting, here.

  I took a backup of the Database (From what I have learnt post TDE & EFS the backup of the Database is encrypted as well using Database Encryption Key Algorith . Is this point correct ?)

In this case, the database is still encrypted using TDE - however if you've changed the volume that the backup is on which isn't protected by EFS then EFS isn't in play. EFS is like TDE, but for the filesystem - so once you leave the protection boundary, it's no longer protected.

  Post taking a backup of the database by using Encrypted file above and restoring it on the same server , the database is now freed from both TDE & EFS , Can some explain me what happened here ?

I don't follow and there should be more to this that we're missing. Just restoring the database on the same server would require that the server certificate for TDE be available (which, since it's the same server it should be available) so the restore can take place. If you've chosen to MOVE the file location to a location not protected by EFS then EFS would no longer be in the picture. If you restored over the same database in the same location, then both TDE and EFS should bein effect. Regardless, though, TDE will still be in effect.

  Why the Database is not encrypted when I used a encrypted Database Backup file !

It is, so we're missing part of the story. If it's saying it isn't, then something else occurred after the RESTORE DATABASE command to disable TDE.

  Does the backup file post restoration gives decrypted files automatically by default behavior ?

No, that's why something else had to have happened. The database should still be TDE encrypted, whether or not EFS depends on the where the files were restored.

  How can I make sure (or) is there way at all when I restore a encrypted backup I can see the database is encrypted as well ?

I'm not sure I follow, again, as a backup of a TDE database is encrypted. If you restore it, it's still encrypted. Period. If this happens each and every time just running RESTORE DATABASE and nothing else, then more data capture will be needed. If you're using a 3rd party tool, you'll want to ask what they do and their process or grab some tracing during the restore to look into what they are doing.


Sqlyog: copy table from localhost to remote server without duplicate records

You can copy your table first into a temporary table in your source database, copy that table to the target database, and merge the data there.

That is, in your source database:

CREATE TABLE emails_temp AS
SELECT
    *
FROM
    emails ;

... copy this table to the target database using the Copy Database functionality; and drop it from the original:

 DROP TABLE emails_temp ;

Once you have your temporal table at the destination, add the new data to the existing table, doing something like:

INSERT INTO emails
SELECT
    *
FROM
    emails_temp
WHERE
    primary_key NOT IN (SELECT primary_key FROM emails) ;

(Obviously, you have to substitute primary_key by your actual PRIMARY KEY).

Or you can use also INSERT IGNORE.

After you have transferred all the new rows, you can

DROP TABLE emails_temp ; -- At the destination database


Wrapping up the repetitive sql statements into transaction in MySQL

I would do something like the following:

CREATE TABLE `DelContracts_20170607` (`contract_id` varchar(32), `chg_id` varchar(32));
INSERT INTO `DelContracts_20160607` VALUES ('1234', NULL);
-- ... insert rest of contract IDs

SET @DelDate = DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d %T');

UPDATE `dbo2`.`allcontracts` ac JOIN `DelContracts_20170607` d ON (ac.`ctr_id` = d.`contract_id`)
   SET ac.`ctr_status`='4'
;

INSERT INTO `dbo2`.`tctrchanges`
  (`chg_ctr_id`, `chg_type`, `chg_date`, `chg_status`, `chg_appr_date`,
   `chg_emp_id`, `chg_req`, `chg_reserved`)
  SELECT `contract_id`, '0', @DelDate, '1', @DelDate, '3', '31', `contract_id`
    FROM `DelContracts_20170607`
;

UPDATE `DelContracts_20170607` d JOIN `dbo2`.`tctrchanges` tcc ON (d.`contract_id` = tcc`chg_reserved`)
   SET d.`chg_id` = CAST(tcc.`chg_id` as char(32))
 WHERE tcc.`chg_date` = @DelDate
;

INSERT INTO `dbo2`.`allnotes`
  (`note_dt`, `note_user`, `note_ref_id`, `note_type`, `note_text`, `note_emp_id`, `note_reserved`)
  SELECT @DelDate, 'mikeb', '328515', '2',
         CONCAT('Automatic Note: <a href=\"ctrchange4.asp?chg_id=',`chg_id`,'">Contract was Cancelled '),
         '31', `contract_id`
    FROM `DelContracts_20170607`
;

DROP TABLE `DelContracts_20170607`;

NOTE: Code is untested.

First, you create a table to hold the contract ID values. Set this up to also hold the chg_id values form tctrchanges, once they're created.

Insert all the contract IDs into the table.

Next set a variable to the current date and time.

Update allcontracts by JOINing it to your newly created table.

Insert your rows into tctrchanges by selecting contract_id, the date variable, and the hardcoded values from your table.

Next, update your table, setting the chg_id value for each contract_id. You find the chg_id based on each row's contract_id and the value from your date variable. This is why we put the date in the variable instead of directly calling CURRENT_TIMESTAMP(); so we'd be sure we had the matching date when we went to search.

Then, insert into allnotes by selecting the hard-coded values (concatenating the chg_id into the URL) and the contract_id from your table yet again.

Finally, delete the table you created.


I have inserted your values into a table variable just to so I could pivot from it later.

The code came out like this:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW

declare @t table(srv nvarchar(128),dates date,[count] int)

insert into @t (srv,dates,[count])
select 'SRV1',cast ('03/25/2018' as date) ,1   union all
select 'SRV1',cast ('04/06/2018' as date) ,59  union all
select 'SRV1',cast ('04/07/2018' as date) , 9  union all
select 'SRV1',cast ('04/13/2018' as date) , 1  union all
select 'SRV1',cast ('04/15/2018' as date) , 1  union all
select 'SRV1',cast ('04/20/2018' as date) , 4  union all
select 'SRV1',cast ('04/21/2018' as date) , 2  union all
select 'SRV1',cast ('04/27/2018' as date) , 5  union all
select 'SRV1',cast ('04/28/2018' as date) , 5  union all
select 'SRV1',cast ('04/29/2018' as date) , 1  union all
select 'SRV1',cast ('04/30/2018' as date) ,62

select *
from (

       select srv
              ,dates
              ,[count]
          from @t

) radhe
pivot (sum([count]) for dates in (
  [2018-04-01],
  [2018-04-02],
  [2018-04-03],
  [2018-04-04],
  [2018-04-05],
  [2018-04-06],
  [2018-04-07],
  [2018-04-08],
  [2018-04-09],
  [2018-04-10],
  [2018-04-11],
  [2018-04-12],
  [2018-04-13],
  [2018-04-14],
  [2018-04-15],
  [2018-04-16],
  [2018-04-17],
  [2018-04-18],
  [2018-04-19],
  [2018-04-20],
  [2018-04-21],
  [2018-04-22],
  [2018-04-23],
  [2018-04-24],
  [2018-04-25],
  [2018-04-26],
  [2018-04-27],
  [2018-04-28],
  [2018-04-29],
  [2018-04-30])) as krishna
where 1=1

there are things that could be changed, but that is all up to you,
plus if you want to get rid of the nulls and replace them by zeros
please have a look at the following question and the answers at:

How to replace (null) values with 0 output in PIVOT

And that will also give you some ideas as how to produce all this in a dynamic way.


show extended index equivalent for MySQL 5.7

But it’s safe to assume PK is indeed present ?

Yes.  (The following applies only to InnoDB; other engines probably differ.)

A copy of the PK column(s) is tacked on the end of regular indexes.
FULLTEXT and SPATIAL work differently, so this discussion may not apply.
There has been some chatter about whether the FK columns are redundantly added, or only if not present in the declared index.
The benefit of the PK columns being tacked onto an index is for "covering".  This provides a (perhaps) 2x speedup.  Example:  Given PRIMARY KEY(id), if you also have INDEX(x), then SELECT id FROM t WHERE x=1 (without any other columns mentioned), then the query can be performed in the BTree for the index since it is effectively (x,id).
UNIQUE and non-UNIQUE work the same except for the uniqueness constraint.  This constraint applies only to the columns specified.  That is, given PRIMARY KEY(id), if you also have UNIQUE(uuid), then uuid is checked for uniqueness, even though the BTree containing that index has (uuid, id).
The PRIMARY KEY is a UNIQUE index, but it is different because it is clustered with the data.  That is it is in the same BTree and that BTree is ordered according to the PK.
Hence a "point query" using the PK, is a simple drill-down of the PK+data's BTree.
But a "point query" using a secondary index needs to first drill down the index's BTree to find the PK, then drill down the PK to find the row.  (Exception:  "Covering index", see above, avoids this second drill-down.)


Merging table records across TabName for each PersonID

Can you have , in  column BenefitType  , multiples unique information, for the same Person ?
if no(only one unique value/status), you can use MAX ,  else (u can have more unique values) - you must provide more information , an algorithm to how to retrieve the desired value .

The same , for for the column HealthStatus.

SELECT
    [PersonID] ,
    [FirstName],
    [Gender],
    MAX([BenefitType]) as BenefitType,
    MAX([HealthStatus]) as HealthStatus
 FROM Temp AS T
 GROUP BY
    [PersonID] ,
    [FirstName],
    [Gender]

Output:

PersonID    FirstName   Gender  BenefitType     HealthStatus
C103        John        M       Benefit Type 1  Healthy
C104        Mary        M       Benefit Type 2  Sick

dbfiddle here


Does changing the length limit (type-modifier) of varchar() result in a table or index rewrite?

Essentially, this is out of date information. It hasn't been relevant since 9.2. Now, the only drawback, that I can see, is that the index gets rewritten if the length constraint gets more restrictive and it has to be rechecked.

In the release notes for 9.1, as discovered by Erwin it states

    Allow ALTER TABLE ... SET DATA TYPE to avoid table rewrites in appropriate cases (Noah Misch, Robert Haas)

    For example, converting a varchar column to text no longer requires a rewrite of the table. However, increasing the length constraint on a varchar column still requires a table rewrite.

In the release notes in  9.2, as discovered by @a_horse_with_no_name found,

  Reduce need to rebuild tables and indexes for certain ALTER TABLE ... ALTER COLUMN TYPE operations (Noah Misch)

  Increasing the length limit for a varchar or varbit column, or removing the limit altogether, no longer requires a table rewrite. Similarly, increasing the allowable precision of a numeric column, or changing a column from constrained numeric to unconstrained numeric, no longer requires a table rewrite. Table rewrites are also avoided in similar cases involving the interval, timestamp, and timestamptz types.

The docs on ALTER have this to say

  Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Adding or removing a system oid column also requires rewriting the entire table. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.

Following with the test case provided by a_horse_with_no_name that I modified, let's see this in action.

\timing 1

CREATE TABLE alter_test (id int primary key, some_data varchar(50));
INSERT INTO alter_test
  SELECT i, md5(i::text)
  FROM generate_series(1,1e7) AS gs(i);

ALTER TABLE alter_test
  ALTER COLUMN some_data
  TYPE varchar(55);
Time: 5.671 ms

So we have no slow down without an index. Then we add an index, and try it again,

CREATE INDEX ON alter_test (some_data);
ALTER TABLE alter_test
  ALTER COLUMN some_data
  TYPE varchar(55);
Time: 6.423 ms

I tried VACUUM FULL ANALYZE on the table and making the length on varchar again longer, and it still didn't take any more time. Not saying it's well debunked in all cases, but at least in the simple cases even when indexed this seems to be not-a-concern if you're going to make the constraint less restrictive. However, making the length constraint more restrictive seems to be doing something.

ALTER TABLE alter_test
  ALTER COLUMN some_data
  TYPE varchar(50);
ALTER TABLE
Time: 59690.885 ms

Dropping the index and trying that again is substantially faster,

DROP INDEX alter_test_some_data_idx ;
DROP INDEX
Time: 85.978 ms
test=# ALTER TABLE alter_test
  ALTER COLUMN some_data
  TYPE varchar(49);
ALTER TABLE
Time: 9297.271 ms

So it seems only if the length constraint is made more restrictive and has to be revalidated does the index get rewritten.


Where does mysql write temporary tables?

From the MySQL manual for 5.1:

..
On Windows, Netware and OS2, MySQL checks in order the values of the TMPDIR, TEMP, and TMP environment variables. For the first one found to be set, MySQL uses it and does not check those remaining. If none of TMPDIR, TEMP, or TMP are set, MySQL uses the Windows system default, which is usually C:\windows\temp.
..
ALTER TABLE creates a temporary copy of the original table in the same directory as the original table.

In newer MySQL versions it gets a bit more complicated to explain because there is also an in-place fast ALTER TABLE, but for 5.1 the answer to your question is simply "in the table's directory".


The ref isn't referring to a constant, it's comparing two columns from two tables. Using the = operator will result in the ref type for table2.col = table1.id.

For a query this simple, it's hard to recommend any improvements without knowing data sizes, indexes, both table structures, etc. I would say that the SELECT * isn't generally advisable unless you absolutely need to return all columns.

If you have any examples of more complex iterations of the query, then there might be more than can be said.


PostgreSQL + query planner + amount of entities

You may have missed that spot in the chapter of the manual you quote:

  Note also that since ANALYZE uses random sampling while producing
  statistics, the results will change slightly after any new ANALYZE.

Emphasis mine.

If you actually mean "how many" rows in your question: the query planner has selectivity estimators for certain operators in the conditions. In combination with the statistics gathered the planner estimates the number of rows for every step.

Details of row estimation can be found the chapter of the manual you already link to in your question.

Estimations can be way off if the data distribution is very uneven within a table. That's why raising the setting for default_statistics_target (and running ANALYZE afterwards) may help in such cases.

Some special operators have special selectivity estimators. Like text search. Details in the source code in src/backend/tsearch/ts_selfuncs.c


If the SIDs for ##certificate-based## SQL Server Logins don't match across replicas in an Availability Group, will this be a problem on failover?

Is this of concern? Will a fail-over work despite these Logins having mismatching SIDs between some replicas?

I don't think this is a concern with fail-over due to following facts:

The fail-over basically targets user databases based on database mirroring endpoint
CONNECT permissions on the ENDPOINT required to user defined LOGIN or SERVICE ACCOUNT (not for server principals named like ##%).

As you mentioned, fundamentally, the SID is the correlation between Login and User in a database level. Since the server level metadata sync is out of scope in availability groups, we must maintain Server Level login's SID same in all the replicas (SQL Instances), so that it would match with User SID in secondary copy of the database.

When fail-over happens there is no failure of application connectivity as the login SID = user SID, eventually application gets the same permissions on the database. Otherwise users in database become Orphaned

Following are some points that indicates why we do not have to concern on server principals that are named '##%':

These are created during SQL Server installation, we do not have control over these to manage SID
These are only internal purpose, meaning only exists in system databases, doesn't have any relation with user database principals (sys.database_principals)
There is no issue with user DB connection, since any application usually doesn't have any relation with the principals that are named like '##%', and the principals (##%) not existed in user database.
Finally, our aim is to match Server principal's SID = database principal's SID within each replica. For the principals that are named like ##% are by default and always maintains same SID within replica which you can verify with following query:

select name, sid from sys.server_principals where name like '##%'

select name, sid from master.sys.database_principals where name like '##%'
select name, sid from msdb.sys.database_principals where name like '##%'

  As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?).

You're right, it's limitation of availability group which clearly stated here.., in this case contained database feature would be a solution to persists the authentication with database itself.

Not only logins, but also there are server level metadata aspects that need to be manged outside the availability group as required.


Yes!
Check here.

You can adjust

settings.heartbeatTimeoutSecs
settings.electionTimeoutMillis
settings.catchUpTimeoutMillis
settings.heartbeatIntervalMillis


You could do it using GridFS.

This is best approach.


Checking for fields not on SET clause on before update trigger on MySQL

flag will be what it had been before the UPDATE started.  Maybe it would feel 'right' to use OLD.flag instead of NEW.flag, at least in the if?

Addenda

NEW.flag contains the new value. NEW.flag <=> OLD.flag checks to see if it changed. I doubt if there is any way to see if it was set but not changed. (Note that I used the "NULL-safe equal" operator.)


SQL Server Authentication vs Windows Authentication

For Windows Authentication "SQL Server validates the account name and password using the Windows principal token in the operating system."

For SQL Server Authentication "logins are created in SQL Server that are not based on Windows user accounts. Both the user name and the password are created by using SQL Server and stored in SQL Server."

So SQL Server authenticated connections do not use DNS.

See this topic "Choose an Authentication Mode" in MSDN: http://msdn.microsoft.com/en-us/library/ms144284.aspx

EDIT: Even with SQL Server Authentication the initial connection to the SQL Server requires resolution of the SQL Server's name. It is, however, also possible to connect to the SQL Server using only the IP Address as you noted above.

See SQL Server name formats at:
http://msdn.microsoft.com/en-us/library/ms188642.aspx


Does requiring SSL matter if using AWS RDS and AWS EC2 in a VPC

This question -- and its answer -- are primarily regulatory/compliance matters.

If you need to comply with a standard that mandates encryption in transit between systems on the network, then you need SSL on RDS because the AWS network only isolates your VPC's traffic from the machines in all other VPCs -- the traffic is not actually encrypted on the wire as it traverses the cables, routers, and switches within the availability zone or region.

Traffic between systems in a VPC is still traveling unencrypted over the AWS infrastructure within the region, where it is theoretically possible for the traffic to be intercepted by a malicious actor who was able to compromise the AWS infrastructure. Such a compromise is considered highly unlikely, as there are protective controls in place such that it should almost necessarily require physical access and a conspiracy among multiple AWS insiders, but "highly unlikely" isn't the same as "impossible."


Try stopping the mysql server, instead of killing the process:

sudo /etc/init.d/mysql stop


The box lost connection to the domain controller, hence the login issues


How to convert from one type of relationship table to another type

You can add columns to the existing tables.  These should be initially nullable until the data is loaded.  After that you can alter the column definition to NOT NULL if appropriate.  You can also drop columns from the table definition.

The process I use is:

Add new columns to table definition.
Populate new columns. (Before using code that expects these columns to be populated.)
Alter old NOT NULL columns to NULL. (Before using code that doesn't populate these columns.)
Make new columns NOT NULL where appropriate. (After code that doesn't populate these columns has been retired. May require rerun of population scripts.)
Drop old columns.  (Can be done much later.)

These steps may be done over a period of days or weeks.  This depends on your development process.  The population script should be able to identify and populate rows created or modified by old code.


MYSQL only Show columns which allow null

Yes you can do it by using the information_schema database..

use the Query

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='db name'
  AND TABLE_NAME='table Name'
  AND IS_NULLABLE='YES';


Adding a geographically dispersed third node to an AAG cluster

My major questions would be where do I place the quorum (probably a disk and file share witness)

Quorum is a majority agreement, it is not a resource. It used to be a resource until 2003 where the only option was a disk, but this hasn't been the case since Server 2008.

There can only be a single witness, you can't have both a disk and a fileshare. The choice of which to have is going to be based on your favorable scenario during a failure and your internal implementation limits. For example, a company I work with has said absolutely no shared disk to any server, so that rules out a disk as a potential witness.

Most organizations want the whatever is considered the optimal  side for their business (though, proper planning and implementation there wouldn't be an "optimal" side) to stay running. In most cases we call this the "primary" side. Generally speaking the witness should be closest to the location that should stay up in case of a true split situation when using windows server 2012R2 or lower. If you're using Windows Server 2016 you will want to look into sites.

  And if Asynchronous commit is the only option available because of the geographical distance is it viable to just a have a manual failover option?

Again, depends. Will this ever be failed to? Maybe just in the instance of a true regional DR event where RTO trumps RPO? Will there be any reporting on this new replica? If so, does the reporting require a specific data freshness SLA?

Synchronous will obviously have an overhead and your round trip time (rtt) latency will play the largest factor for long distance AGs, followed next by disk. Asynchronous will obviously not have those problems in terms of reducing the throughput of the workload but will still feel it in terms of data freshness if it is used for reporting.

Remember, if it is a planned downtime you can change the availability mode before performing any activities.


How to reduce size of a large SQLite database with series data

There are some ways. Here are two that seem appropriate for your case:
WITHOUT ROWID tables
The ROWID is an internal primary key that SQLite tables have by default, taking 64 bit per record. Since you were talking about a UNIQUE constraint for the datetime column, this column seems a good candidate for the primary key (which always has a unique constraint attached). So you can create a SQLite table without ROWID, as follows:
CREATE TABLE "Data" (
    "dateTime" INTEGER PRIMARY KEY,
    "value1" REAL,
    "value2" REAL,
    "value3" INTEGER,
    "value4" INTEGER
) WITHOUT ROWID;

Storing delta values
I have never tried this in SQLite in practice, but: some data is more compactly stored by storing only the difference to the previous row. In this case, the datetime values are great for this, if they can be stored in order into the database. In that case, you would use three columns:

A sequentially numbered integer primary key.
A datetime integer column, only filled for every (say) 1000 records.
A datetime_delta column, storing the difference from the last row with a filled datetime column.

SQLite stores integer values individually into 2-9 bytes (incl. one storage mode byte), depending on their value. Assuming an average of 4 bytes for the sequential primary key and an average of 2.5 bytes for the delta values would save 2.5 bytes compared to the current 9 byte integer. Savings are larger if you have multiple columns in your table that profit from delta storage, as you need the sequential primary key only once. For example, if value1 … value4 are measurement values that tend to grow and shrink over time, they are also good candidates.
After loading a row at primary key value n, you would also have to retrieve the row at primary key value n DIV 1000 (meaning integer division) to have the base for your delta values.


You can see the progress of vacuum with

select * from pg_stat_progress_vacuum;

Here are some tips for vacuuming large tables

https://medium.com/coding-blocks/optimizing-storage-and-managing-cleanup-in-postgresql-c2fe56d4cf5

You do not have to wait too long for autovacuuming


Can I pass in a frozen time as a setting into postgresql and not have clock drift?

You could abuse a placeholder variable:

SET request.time = '2019-11-21 09:00:00';

Then you can use current_setting('request.time') in your queries to retrieve the string.


Querying a view that is defined as "select * from"

I think this would actually be a good exercise for you.  Create a table with a bunch of columns.  Create several different indexes (including a clustered index) on the table.  Try several variations of SELECT's against the table and tell SSMS to include the actual execution plan.  You will be able to see first-hand what indexes (if any) are used and what columns are returned.  Then, you won't have to take someone's word for it.

As far as SELECT * in general, there is some excellent information, courtesy of Dave Markle, here.

To summarize the linked post:

  Here are really three major reasons why SELECT * can be problematic:

Inefficiency in moving data to the consumer. When you SELECT *, you're often retrieving more columns from the database than your application really needs to function. This causes more data to move from the database server to the client, slowing access and increasing load on your machines, as well as taking more time to travel across the network. This is especially true when someone adds new columns to underlying tables that didn't exist and weren't needed when the original consumers coded their data access.
Indexing issues. Consider a scenario where you want to tune a query to a high level of performance. If you were to use *, and it returned more columns than you actually needed, the server would often have to perform more expensive methods to retrieve your data than it otherwise might. For example, you wouldn't be able to create an index which simply covered the columns in your SELECT list, and even if you did (including all columns [shudder]), the next guy who came around and added a column to the underlying table would cause the optimizer to ignore your optimized covering index, and you'd likely find that the performance of your query would drop substantially for no readily apparent reason.
Binding Problems. When you SELECT *, it's possible to retrieve two columns of the same name from two different tables. This can often crash your data consumer. Imagine a query that joins two tables, both of which contain a column called "ID". How would a consumer know which was which? SELECT * can also confuse views (at least in some versions SQL Server) when underlying table structures change -- the view is not rebuilt, and the data which comes back can be nonsense. And the worst part of it is that you can take care to name your columns whatever you want, but the next guy who comes along might have no way of knowing that he has to worry about adding a column which will collide with your already-developed names.

But it's not all bad for SELECT *. It is often used it liberally for these use cases:

Ad-hoc queries. When trying to debug something, especially off a narrow table I might not be familiar with, SELECT * is often my best friend. It helps me just see what's going on without having to do a boatload of research as to what the underlying column names are. This gets to be a bigger "plus" the longer the column names get.
When * means "a row". In the following use cases, SELECT * is just fine, and rumors that it's a performance killer are just urban legends which may have had some validity many years ago, but don't now:


Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnDiscountPrice", or the name is ambiguous

Assuming that the column name returned from dbo.fnDiscountPrice is DiscountPrice you could use it like this.

CREATE FUNCTION dbo.fnItemTotal
    (@ItemID INT = 0)
    RETURNS Table

RETURN (SELECT O.ItemID, SUM(DP.DiscountPrice * O.Quantity) AS TimeTotal
        FROM dbo.fnDiscountPrice(@ItemID) as DP
          CROSS APPLY OrderItems as O
        GROUP BY O.ItemID)


Migrating log files - should I copy file/directory permissions?

If you don't move the permissions too the service account under which SQL Server is running may not be able to access them.

You can use Robocopy to do this quite easily. I tend to use the following.

ROBOCOPY <source> <target> /MIR /SEC /SECFIX

More detaisl on this command can be found here

More Robocopy options can be found here


Make View Supersede Table in Same Database?

You could use an updatable view as a proxy in front of the base table behind it. The view should behave the same way the table did, with some exceptions (for example, TRUNCATE will not work).

The table can the have any name because it is never referenced by app code.


need help understanding the error-message: Error Connecting to database FATAL : no pg_hba.conf entry

The no pg_hba.conf entry message is a generic message that means that something did not work out with the connection attempt. PostgreSQL uses a generic message, so that an attacker cannot draw conclusions from the kind of message she gets.

The true problem is probably:

SSL error: certificate verify failed

That would mean that client and server attempt to start an encrypted connection, but the client does not recognize the server certificate (because the certificate that was used to sign the server certificate is not a certification authority that the client recognizes).


How to find index details on Temporary tables in SYBASE

Assuming this is Sybase ASE ...

I would probably pull the create index out to a different place in your code, eg:

create/select-into <table>, create index, looping/insert, update statistics <table>, run queries

or

create/select-into <table>, looping/insert, create index, (optionally update statistics <table> - depends on your query requirements), run queries

If you insist on throwing the create index into the middle of your looping/insert construct (I wouldn't recommend this), consider ...

to query the system tables you need to know which tempdb your session is assigned to (assuming your ASE is configured with multiple tempdb's), and then query the system tables in that particular tempdb (eg, build a dynamic query)

or

you can use some of the built-in system functions to run some tests

Your current tempdb's database id can be found in @@tempdbid (or you can call tempdb_id()).  You can then call db_name(@@tempdbid) (or db_name(tempdb_id())) to get your tempdb name.  From here you can build your dynamic query against sysindexes. [Plenty of posts on dynamic ASE queries so I'm not going to cover that here.]

Instead of building a dynamic query I'd probably opt for a (relatively) simple function-based test, eg:

NOTE: Assuming no other indexes on the table ... the index id should be 1 or 2 ... depending on the table's locking scheme and the type of index (clustered/non-clustered).

if  index_name(@@tempdbid,object_id(#TABLE_X),1) is NULL
and index_name(@@tempdbid,object_id(#TABLE_X),2) is NULL
begin
    create index ...
end

Regardless of whether you build/run a dynamic query, or use the system functions to test for the existence of the index, I would highly recommend you re-think your code design.  Do you really, Really, REALLY want to add the extra overhead for a dynamic-query/function-call to every pass through your looping/insert construct?


You can use your CASE variant with some modifications. First you need to extend the GROUP BY with MONTH(date):

SELECT product_name,
  CASE WHEN MONTH(date) = 1 THEN SUM(quant_ton) END AS JAN,
  CASE WHEN MONTH(date) = 2 THEN SUM(quant_ton) END AS FEV
FROM tb_import
JOIN tb_cost
    ON tb_import.productid = tb_cost.productid
JOIN tb_sites
    ON tb_sites.site_num = tb_import.site_cd
JOIN tb_product
    ON tb_product.prod_id = tb_import.productid
GROUP BY product_name, MONTH(date)

If not MySQL will randomly pick one month for you. There's a lot to be said about this default setting (insane comes to mind:-), but look up sql_mode ONLY_FULL_GROUP_BY for ways around it.

As you can see now you will get null for non-matching months:

product_name    JAN    FEV
gravel          41     (null)
gravel          (null) 22
soil            23     (null)
soil            (null) 10

By picking - for example - max of JAN and FEV you will get rid of those

SELECT product_name, max(JAN), max(FEV)
FROM (
    SELECT product_name,
        CASE WHEN MONTH(date) = 1 THEN SUM(quant_ton) END AS JAN,
        CASE WHEN MONTH(date) = 2 THEN SUM(quant_ton) END AS FEV
    FROM tb_import
    JOIN tb_cost
        ON tb_import.productid = tb_cost.productid
    JOIN tb_sites
        ON tb_sites.site_num = tb_import.site_cd
    JOIN tb_product
        ON tb_product.prod_id = tb_import.productid
    GROUP BY product_name, MONTH(date)
) as t
GROUP BY product_name;

product_name    max(JAN)    max(FEV)
gravel          41          22
soil            23          10

However, the pivoting is only a presentation of the result. I would choose a query like:

SELECT product_name,
       MONTH(date),
       SUM(quant_ton)
FROM tb_import
JOIN tb_cost
    ON tb_import.productid = tb_cost.productid
JOIN tb_sites
    ON tb_sites.site_num = tb_import.site_cd
JOIN tb_product
    ON tb_product.prod_id = tb_import.productid
GROUP BY product_name, MONTH(date)
ORDER BY product_name, MONTH(date);

and then format the result in the presentation layer of the application.


Are there any (functional or nuanced) differences between rebuilding an index and a full drop/create? If so, what are they?

From Microsoft SQL Server 2012 Internals, page 366

  You can completely rebuild an index in several ways. You can use a simple combinination of DROP INDEX followed by CREATE INDEX, but this method is probably the least preferable.

But appeals to authority are boring, so let's test it ourselves!

Performance:

Set up a table with a non-clustered index, load it with junk, and build a table for logging time.

CREATE TABLE IndexTest
(ID int identity(1,1) primary key clustered,
Junk uniqueidentifier)

CREATE TABLE IndexTestLogging
(Operation char(10),
TimeInms int)
GO

INSERT IndexTest
SELECT TOP 1000000 NEWID() AS Junk
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
CROSS JOIN sys.all_objects c

CREATE INDEX IX_IndexTest ON IndexTest(Junk)
GO

Now let's build some procs that will DROP/CREATE or REBUILD, plus log (approximately) the time taken:

CREATE PROC usp_DropCreate
AS
DECLARE @t1 datetime,
@t2 datetime

--scramble the table for fragmentation
UPDATE IndexTest
SET Junk = NEWID()

SET @t1 = GETDATE()
DROP INDEX IX_IndexTest ON IndexTest
CREATE INDEX IX_IndexTest ON IndexTest(Junk)
SET @t2 = GETDATE()

INSERT IndexTestLogging
SELECT 'DropCreate' AS Operation, DATEDIFF(millisecond,@t1,@t2) AS TimeInms
GO

CREATE PROC usp_Rebuild
AS
DECLARE @t1 datetime,
@t2 datetime

--scramble the table for fragmentation
UPDATE IndexTest
SET Junk = NEWID()

SET @t1 = GETDATE()
ALTER INDEX IX_IndexTest ON IndexTest REBUILD
SET @t2 = GETDATE()

INSERT IndexTestLogging
SELECT 'Rebuild' AS Operation, DATEDIFF(millisecond,@t1,@t2) AS TimeInms

OK, now we can run each procedure multiple times and gather data

EXEC usp_DropCreate
GO 10

EXEC usp_Rebuild
GO 10

SELECT AVG(
         CASE
         WHEN Operation = 'DropCreate' THEN TimeInms
         END) AS AvgDropCreate,
       AVG(
         CASE
         WHEN Operation = 'Rebuild' THEN TimeInms
         END) AS AvgRebuild
FROM IndexTestLogging

Here are my own results: average time in ms for DROP/CREATE: 2547, for REBULD: 1314

It looks like in my contrived example, the winner is REBUILD.

Constraints

What if the index was created to support a constraint? In this case, a simple DROP and CREATE will fail because you have to drop the constraint first. Let's look at the clustered index from the previous example.

--Find out what the clustered index is named - it will be different for you
SELECT name
FROM sys.indexes
WHERE object_id = OBJECT_ID('IndexTest')

--enjoy the error message
DROP INDEX PK__IndexTes__3215078707C12930 ON IndexTest

Note that the other two methods succeed:

CREATE UNIQUE CLUSTERED INDEX PK__IndexTes__3215078707C12930 ON IndexTest(ID)
WITH DROP_EXISTING

ALTER INDEX PK__IndexTes__3215078707C12930 ON IndexTest REBUILD

Of course, like Erik alluded to, your actual problems probably have nothing to do with fragmentation, but hey, this was fun.


Best storage engine for huge tables with mostly update operations (74 million records, MyISAM table size 7.6G)

For virtually all use cases, InnoDB out-performs MyISAM.

DDL operations have been greatly sped up in 5.6 and 5.7 for InnoDB.

InnoDB will be 15-20GB for that table.  Please provide SHOW CREATE TABLE for critique of how to save space.

Please provide some of you SELECTs for critique on how to improve JOIN performance.  See also Converting MyISAM to InnoDB.

After switching to InnoDB, set key_buffer_size = 20M and innodb_buffer_pool_size to 70% of RAM.

InnoDB will 'cache' data and indexes, so it is often as fast as "in memory tables".


Adding a Clustered index, should I drop the nonclustered indexes

Dropping the nonclustered indexes will make converting the heap to a clustered index faster since those nonclustered indexes will not need to be updated.

However, you probably will want to recreate them after the conversion process is complete, so make sure you script the CREATE INDEX scripts prior to dropping them.

Be aware, dropping the nonclustered indexes means any queries that run during the conversion may run very slowly since they may be expecting indexes that are no longer there.


Oracle : Multiple aggregate functions on a table

Option 1: You could write one subquery to aggregate the names, one subquery to find the max date, then join the second subquery against the original table on the date to get the rest of the C_ columns. This is ugly.

Option 2: You could use Analytic Functions:

SELECT MAX(date_col) AS max_date_col,
       LISTAGG(name) WITHIN GROUP (ORDER BY name ASC) AS names,
       MAX(c_a) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_a,
       MAX(c_b) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_b,
       MAX(c_c) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_c,
       MAX(c_d) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_d,
       MAX(c_e) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_e,
       MAX(c_f) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_f,
       MAX(c_g) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_g
  FROM /* ... */


Where to store administrative (DBA) data?

I am wondering if this same technique would be useful here.

Since MongoDB is a distributed database, nodes tend to be part of a larger deployment (replica set or sharded cluster) where configuration differences in individual nodes are usually managed externally. For example, using Configuration Management software like Chef or Puppet to generate mongod and mongos configuration files.

  Should/could I use the admin or local database for this information?

Each mongod node has a local database which is used for storing node-specific administrative or logging data such as the startup_log (a capped collection recording history of startup parameters and versions) and oplog.rs (the replication oplog).

You could store node-specific information in the local database, but I think it would make more sense to use a normal collection in your deployment in order to coordinate maintenance tasks and availability. Aside from backup, there aren't many node-specific maintenance tasks that should be performed without manual intervention.

  or should I create a new database to store essentially a single collection and document?

If you do find the need for node-specific tasks I imagine your maintenance database would have more than a single collection and document. For example, you might have a configuration document per node and perhaps other collections such as a history of maintenance tasks. Capped collections can be useful for storing a rolling snapshot of "recent" activity using a preset storage allocation.

  Would this change in a sharded/replica-set environment?

Assuming the details are node-specific, there would be no change in a sharded or replica set environment if you are accessing data in the local database. The contents of the local database are excluded from replication/sharding and you should only access the local database by connecting directly to a mongod node.


Postgres: SELECT with ORDER BY is 4x slower when using index

If your table is well-vacuumed, you can get an index only scan by creating the index:

CREATE INDEX ON temp_test(dt,val);

This might be quite a bit faster as it doesn't need to visit the table at all. (In my test, it was about 2.5 times faster than the sort.)

As to why it chooses the wrong plan with the index you already have, it could just be that you have an effective_cache_size that is set too high for the amount of RAM actually available to it.  You would have to tell us more about your settings and you hardware.

I've replicated your finding in the absence of the index-only-scan index, with the planner choosing the much-slower ordered index scan over the seq scan.  I replicated it on AWS m5.large machine, using Ubuntu 18.04, with PostgreSQL 10 from the ubuntu repository, and using the default settings for both OS and PostgreSQL, other than setting track_io_timing on and max_parallel_workers_per_gather=0; setting it up like this:

create table temp_test (
  id SERIAL PRIMARY KEY,
  dt DATE,
  val bigint,
  txt VARCHAR(64)
);
insert into temp_test select x, now()+interval '10 years'*random(), random()*1000000000, random()::text from generate_series(1,20000000) f(x);
vacuum ANALYZE temp_test ;

With the default shared_buffers of 128MB, the ordered index scan is preferred by the planner, but is slow as you indicate, even if all data is in memory (vmstat shows no read/write activity during the query).  But if shared_buffers is increased so that it holds all the data, then the ordered index scan becomes much faster (still slower than the sort, but not by nearly as much).

So the main problem is that it is slow to read data from the filesystem cache into shared_buffers, and this is not accounted for by the planner.  The code that implements effective_cache_size assumes that reading a page which is already in memory is absolutely free.  The reality is that while it is much cheaper to read a page from the filesystem cache than it is to read it from disk, it is still quite expense relative to, say, a cpu_tuple_cost.  So "free" is not nearly accurate.

The planner does not expose any knobs you can twist to make it account for the residual cost of reading a page from the filesystem cache, such that it does not appear to be free.  And it would not be easy to add such a knob, because to do so accurately you would need to distinguish between how many pages will be re-found in shared_buffers, from how many will be re-found in the filesystem cache but not in share_buffers.  The current 'Mackert and Lohman formula' doesn't make such a distinction and it is not clear to me how to make it do so.


I have compile a summary of MySQL limits.  Some, related to your question:

An InnoDB partition is limited to 64TB (2**46 bytes).
A MyISAM table is limited to 2** 56 bytes, but you need to change a setting to get beyond 2**48.
A table can have 1024 partitions until some recent release, then 8192.
(BIGINT is not relevant in these limits.)

Is there alarm?

Do you have an AUTO_INCREMENT?  If so, you need to watch out for it overflowing.  600M is 28% of the way toward INT SIGNED and 14% of the way to INT UNSIGNED.
58GB is big, but certainly not the biggest ever.
You did mention performance issues, so we probably don't need to discuss things that can go wrong with tables that big
Archiving -- Based on how fast the data is accumulating, when will you run out of disk space?  It would be better to be concerned when the disk is 50% full -- this would leave room for an ALTER.

Is this a Data Warehouse application?


MySQL - SELECT INTO OUTFILE, with a UNION

Give this a Try

SELECT * FROM
((SELECT email FROM table WHERE createdate >= date_sub(now(), interval 1 year))
UNION DISTINCT
(SELECT email FROM table WHERE createdate < date_sub(now(), interval 1 year) AND email not like '%@gmail.com')) A
INTO OUTFILE '/tmp/emailDump.txt' FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";


I have configured to run backups using the "prefer secondary" option.

Prefer Secondary - Will backup on secondary with highest priority. If no secondary's are available, it will backup on primary.

  When I triggered the job from the primary replica, only db's outside
  the AAG are backing up and when I trigger it from one of the secondary
  only AAG db's are backing up.

First of all i would like to say that i am try to explain in concise way but due to function logic , elaborate a bit more.

Once you have your backup preferences and priority established, then it is time to set up a SQL Server Agent Job to perform the backups. Thankfully Microsoft has provided us with a system function called sys.fn_hadr_backup_is_preferred_replica. This function will return 0 if the current instance is not the preferred backup location or 1 if it is the preferred replica for backups according to the preference and priority settings.

IF (NOT sys.fn_hadr_backup_is_preferred_replica(@DBNAME))
BEGIN
      Select ‘This is not the preferred replica, exiting with success’;
      RETURN 0 – This is a normal, expected condition, so the script returns success
END
BACKUP DATABASE @DBNAME TO DISK=<disk>
   WITH COPY_ONLY;

As per MSDB BOL Here To take the automated backup preference into account for a given availability group, on each server instance that hosts an availability replica whose backup priority is greater than zero (>0), you need to script backup jobs for the databases in the availability group. To determine whether the current replica is the preferred backup replica, use the sys.fn_hadr_backup_is_preferred_replica function in your backup script. If the availability replica that is hosted by the current server instance is the preferred replica for backups, this function returns 1. If not, the function returns 0. By running a simple script on each availability replica that queries this function, you can determine which replica should run a given backup job.

Scripting a backup job with this logic enables you to schedule the job to run on every availability replica on the same schedule. Each of these jobs looks at the same data to determine which job should run, so only one of the scheduled job actually proceeds to the backup stage.

Note:-There is no enforcement of AutomatedBackupPreference. The interpretation of this preference depends on the logic, if any, that you script into backup jobs for the databases in a given availability group. The automated backup preference setting has no impact on ad-hoc backups.

  As,i would like to say that as when you are configuring auto backup of
  AAG through maintenance Plan in 'Primary Replica' & when your Backup
  preferrence will be 'Preferred Secondary Replica' in that case also
  the Non AAG databases will be backup in primary replica backup
  location, after the execution of Maintenance plan schedule backup. And
  Availability databases you shall not find out in the backup location
  in Primary Replica . But When you shall check the 'Synchronization
  state' through ' Show Dashboard' you shall find out, the both Replica
  in 'Synchronized state'.

For reference i am writing the last script, which had ran in my server (as on dated '15-10-2016') through 'maintenance plan backup'. Which script was as follows:-

DECLARE @preferredReplica int

SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(''Database1''))

IF (@preferredReplica = 1)
BEGIN
    BACKUP DATABASE [Database1] TO  DISK = N''H:\SQLSRV-N01_Backup_Files\Automatic Backup\Database1_backup_2016_10_15_000001_6209992.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database1_backup_2016_10_15_000001_6209992'', SKIP, REWIND, NOUNLOAD,  STATS = 10
END

GO
DECLARE @preferredReplica int

SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(''Database2''))

IF (@preferredReplica = 1)
BEGIN
    BACKUP DATABASE [Database2] TO  DISK = N''H:\SQLSRV-N01_Backup_Files\Automatic Backup\Database2_backup_2016_10_15_000001_7520058.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database2_backup_2016_10_15_000001_7520058'', SKIP, REWIND, NOUNLOAD,  STATS = 10
END

GO
DECLARE @preferredReplica int

SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(''Database3''))

IF (@preferredReplica = 1)
BEGIN
    BACKUP DATABASE [Database3] TO  DISK = N''H:\SQLSRV-N01_Backup_Files\Automatic Backup\Database3_backup_2016_10_15_000001_7770006.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database3_backup_2016_10_15_000001_7770006'', SKIP, REWIND, NOUNLOAD,  STATS = 10
END

GO
BACKUP DATABASE [Database4] TO  DISK = N''H:\SQLSRV-N01_Backup_Files\Automatic Backup\Database4_backup_2016_10_15_000001_8030022.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database4_backup_2016_10_15_000001_8030022'', SKIP, REWIND, NOUNLOAD,  STATS = 10

GO
BACKUP DATABASE [Database5] TO  DISK = N''H:\SQLSRV-N01_Backup_Files\Automatic Backup\Database5_backup_2016_10_15_000001_8280125.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database5_backup_2016_10_15_000001_8280125'', SKIP, REWIND, NOUNLOAD,  STATS = 10

GO

Here I have only changed the database name otherwise everything is as it's the script.
As you can see the above code Database1,Database2 & Database3 are AAG Databases. That's why
automatically it check through 'Scalar-value Functions' (i,e; sys.fn_hadr_backup_is_preferred_replica).
And For the database4 & database5 , it does not check through that function, because these databases are Non AAG Databases.
So, after execution of my 'primary server replica''maintenance plan schedular backup'' only i have found out the Non AAG Databases in specified backup location.

So, I can say that you are not missing anything in Ola Hallalengren's script.

For your Ref Backing Up SQL Server Databases Participating In AlwaysOn Availability Groups and Active Secondaries: Backup on Secondary Replicas (Always On Availability Groups)


How can I limit the number of emails sent by SQL Server agent?

This feature is already built into SQL Server. I had a similar situation and what I did was to change my code to use a custom error number, above 50000, and set up a new Alert, under SQL Server Agent|Alerts in Management Studio, with that custom error number. Under the Options section, there is a Delay between responses section that can be used to mitigate the volume of notifications per hour.  I had my process monitoring every 10 seconds, but notifying every 5 minutes because I wanted to know when the critical situation started, but not be overwhelmed with alerts.


Statistics histogram AVG_RANGE_ROWS discrepancy

As described in answer to Badly-formed histogram causes bad estimates on Nested Loop there has been a change to the way sampled statistics are calculated and stored, more particularly when scaling is applied.
As a side-effect, the value for DISTINCT_RANGE_ROWS in your case is a fraction between 0 and 1 (980.235 / 386212.6 = 0.002538071). The column has an exposed type of bigint, so it is rounded down to zero.
Clearly there can't really be zero distinct values when the range contains a non-zero numbers of rows.
One can only hope these discrepancies are cleared up at some stage; though it's hard to imagine exactly what that might look like without a potentially breaking change of data type, extending also to sys.dm_db_stats_histogram (available on SQL Server 2016 and later).
As for what you do about it, if you are convinced this is not just a display issue and is actually causing poor estimations, you should report it as a regression.


Some SQL Server geospatial queries take much, much longer than others

Short answer: Compare actual execution plans for the fast and slow variants and you'll see yourself.

When the given @point is close to the points in the table, the tessellations used in the spatial index actually help to dismiss most of the rows and only few seeks of the index are necessary.

When the given @point is far from any point in the table, the engine effectively has to read all rows. It seeks an index 100K times, which is slow.

If you disable the spatial index you'll see that performance of the query becomes the same for any given @point. It will be slower than your fast variant when index is useful, but it will be faster than your slow variant when the index is harmful.

See Spatial Indexes Overview if you haven't already for the basic details about the internal structures of such index.

Sample test data

CREATE TABLE [dbo].[Factors](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [GeoLocation] [geography] NULL,
    [Factor] [decimal](18, 6) NOT NULL,
 CONSTRAINT [PK_Factors] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Generate ~100K rows within ~20km by ~20km area.

DECLARE @MinLat float = -38.180184;
DECLARE @MaxLat float = -38.000000;
DECLARE @MinLon float = 145.000000;
DECLARE @MaxLon float = 145.227707;
DECLARE @PointCount int = 317;

WITH
x AS
(
    SELECT TOP (@PointCount)
        ROW_NUMBER() OVER (ORDER BY [object_id]) AS rn
    FROM sys.all_objects
)
INSERT INTO [dbo].[Factors]
    ([GeoLocation]
    ,[Factor])
SELECT
    geography::Point(
         @MinLat + (TLat.rn-1) * (@MaxLat - @MinLat) / (@PointCount-1)
        ,@MinLon + (TLon.rn-1) * (@MaxLon - @MinLon) / (@PointCount-1)
        ,4326) AS GeoLocation
    ,0 AS Factor
FROM
    x AS TLat CROSS JOIN x AS TLon
ORDER BY TLat.rn, TLon.rn;

Create default spatial index

CREATE SPATIAL INDEX [IX_GeoLocation] ON [dbo].[Factors]
(
    [GeoLocation]
)USING  GEOGRAPHY_AUTO_GRID
WITH (
CELLS_PER_OBJECT = 16,
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Test queries

@point1 is close to other points in the table.
@point2 is far away from other points in the table.

declare @point1 geography = geography::Point(-38.000000, 145.000000, 4326);
declare @point2 geography = geography::Point(+38.000000, 145.000000, 4326);

select top 3
    Factor,
    GeoLocation.STDistance(@point1) as Distance
from dbo.Factors
where GeoLocation.STDistance(@point1) is not null
order by Distance
option(recompile);

select top 3
    Factor,
    GeoLocation.STDistance(@point2) as Distance
from dbo.Factors
where GeoLocation.STDistance(@point2) is not null
order by Distance
option(recompile);

Execution plans and IO

Index enabled

IO. Top result is fast (7ms, 171 reads). Bottom result is slow (5,693ms, 234,662 reads).

Fast.

Slow.

Index disabled

IO. Both queries have the same number of reads (601) and same duration (~1700ms).

Plan is the same for both queries:

It is faster to scan 100K rows, than seek 100K times.

I don't know how to solve the problem, if there is a way to get the best of the both worlds and somehow automatically decide whether to use the index or not.

You can try to calculate the bounding box (min/max lat/lon) and change the logic based on whether the given point is within the bounding box.

The most interesting thing happens in that built-in Geodetic Tesselation table-valued function and I don't see how to fine-tune it.

With spatial indexes a lot depends on your data distribution.

In some cases you may be better off with two separate simple standard indexes on latitude and longitude if you know that your data is dense and you can limit the search to a narrow stripe or small area (given point +- few km).


What is background job queue in sql server? How to create a background job?

You can't create one of these jobs. They aren't SQL Server Agent jobs or anything you can create manually, they're background tasks that SQL Server performs (like asynchronous updates to statistics).

You could trigger temporary population of the DMV by updating enough rows in a table and then running queries that force stats to be re-calculated, but this would be a very hunt-and-peck operation that will rely on incredibly lucky timing.


Does an IOT guarantee order in a select?

No.

The only thing that guarantees result set order is an ORDER BY clause in your query.

This is a popular question about SQL so it's worth repeating what I've written in response to similar questions about SQL Server and MySQL:

  In the SQL world, order is not an inherent property of a set of data.
  Thus, you get no guarantees from your RDBMS that your data will come
  back in a certain order -- or even in a consistent order -- unless you
  query your data with an ORDER BY clause.

In Oracle, an Index-Organized Table (IOT) will minimize the amount of work the database has to do to get you the data sorted in the index's sort order.  Though you may find that Oracle tends to return rows selected from an IOT in that same order, you are only guaranteed that order when you query the IOT with an ORDER BY clause.


How do I name the thing that is done in the FROM part of the query?

I just read your examples closer and understand your question better now. Your second example that you were doing uses a subquery but the first example your professor was doing is called an implicit inner join. It's just a shorthand way to write FROM employee EMP INNER JOIN employee SUP ON SUP.employee_ID = EMP.supervisor_ID like @stickybit commented.


I forgot to update this post with what ended up working for me.  I don't know why, but running SSMS as Administrator allowed the backup to complete as you'd expect.


What is and what is needed that parameter before the delete keyword
  (In my example coincides with table name)?

Referencing the table is a possibility due to the fact that you could DELETE FROM with an INNER JOIN.

A simple example of deleting from #footbl with an inner join to a different table

DELETE FT
FROM
#DifferentTable DT
INNER JOIN #fooTbl FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';

Whereas this generates an error due to not specifying what table to delete from

DELETE
FROM #DifferentTable AS  DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';

  Msg 156, Level 15, State 1, Line 45 Incorrect syntax near the keyword
  'AS'.

And this also works

DELETE  #fooTbl
FROM #DifferentTable AS  DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';

In short, you need to specify the alias/table to delete from if you are referencing multiple tables, but you don't have to when you only reference the one.

  What is the best practice?

In terms of best practice I don't think that it matters much for the single table. Aliasses can be useful as to make adapting / reusing scripts easier.

Some more examples in the Microsoft documentation

D. Using joins and subqueries to data in one table to delete rows in another table

Using the table name twice

DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;

Using an alias

DELETE spqh
  FROM
        Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson AS sp
        ON spqh.BusinessEntityID = sp.BusinessEntityID
  WHERE  sp.SalesYTD > 2500000.00;

Both serving the exact same purpose.


You need to have Progress installed. In it you will find a command: prorest run from command line that will help you restore a previously backed up database. At least if it has been backed up using the built in back up command (probkup).

To make things easier for yourself you could start the Progress command line environment (it should be in the bin directory in your installation path). Simply run proenv to start that and then follow the steps below. If on Windows you will most likely find proenv in your Start menu.

Basic syntax:

prorest [new database] [archive]

so for example

prorest c:\temp\mynewdatabase file.bup

After that you can connect to the database using Progress built in commands. You might find more help at:
Progress KnowledgeBase
and
Progress communities.

A specific description of the prorest command can be found here: Progress KnowledgeBase Article 99033


How to update integer primary key adding 1000?

Yes, you can do that, however if you need to do this regularly, then something is probably bad in your DB or software design.

The purpose of the primary keys is to identify the records. They should not have any additional information. This rule may be stepped over on the some optimization reasons, or in special cases (like DB merge, refactor) but that is all.

Furthermore, typically the primary keys are referred by external parts of your software (either by foreign keys from other tables, or by non-db parts of your software). Be careful, changing a primary key requires to change it everywhere where they are referred. The CASCADE parameter of the foreign keys may help a lot, if you are using MySQL.

Put this additional information in alternate columns, you will spare a lot of pain on the longterm with that.


create a visible uniquifier column for a nonunique clustered index

Is it possible to have such a visible column in SQL Server filled with automatic values, same as the hidden uniquifier column?

No there is no built-in feature to do this.

  Or do I have to manually calculate such a column when I insert rows?

You could consider using INSTEAD OF or AFTER triggers to maintain the correct uniqueifier-like behaviour for the same-time group when rows are inserted/updated/deleted.

This may require some careful thought to operate correctly under high concurrency, but it is probably best done as close to the data as possible (i.e. server-side, in the database, rather than client-side). A well-written trigger (with supportive indexing) will introduce minimal overhead.

Alternatively, make the index unique and implement retry logic. Since the expectations seems to be that collisions will be rare, this might well be acceptable.


How to create a differential backup considering the base the previous differential backup in SQL Server?

You are essentially asking for incremental, which is pretty much what a log backup is.  So your Diff1 and Diff2 would actually be log backups, which still gets you point in time restores.

Why are you trying to do this...purely academic reasons?


you can check if the functions are defined using

select pg_get_functiondef(to_regproc('gen_random_bytes'));
select pg_get_functiondef(to_regproc('gen_random_uuid'));

or:

select * from pg_proc where proname like 'gen_random_%';

if both functions are not defined then you probably had an error with the extension creation - just drop it and recreate:

drop extension pgcrypto;
CREATE EXTENSION pgcrypto;


Query plan missing ParameterCompiledValue

The XML plan uploaded includes:

<ParameterList>
    <ColumnReference Column="@today" ParameterCompiledValue="'2016-03-04 00:00:00.000'" />
</ParameterList>

There are multiple statements in the batch, so the XML contains multiple <StmtSimple> elements under <Statements>. Aside from the final select, the other statements are all assignments, which are not queries, so there is no parameter list at that level.

As Kin mentioned, you may find it useful to look at the plans in SQL Sentry Plan Explorer:

Related question:

Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN


Relation between Faculty, Student and Course tables

One Course can have many Sections.  "One-to-many".  That is handled via a course_id column in the Sections table.

Many Students take many Courses.  "Many-to-many".  That is handled by a many:many table with two columns: course_id and section_id.  Note that you can find the course_id from the Sections table.

Facilty:Sections is also many:many.

I don't know where you are going with "OneToOneField".  Generally it is not good to have two tables in a one-to-one relationship.

Students and Facilty, though both humans (I guess), do not seem to be related at all.  Well, maybe you have "student teachers"?


IMO It is much better to have a design with multiple tables.  You will be able to track changes more effectively that way.

Also, for the fields that change, I would not go with a Name-Value pair of storage.  I would actually create tables with appropriately named columns.

Is data space really an issue for you?  You should run a few calculations estimates to see if space is really going to be an issue.  You'll be very surprised to see how much of a non-factor hard disk space is.


How to choose a column of data from 2 days ago?

If the current WHERE condition specifies today and you want yesterday, then just subtract one day from both expressions. That will make the first expression CURDATE() - INTERVAL 1 DAY and the second simply CURDATE():

SELECT column1
FROM db.table
WHERE TS >= CURDATE() - INTERVAL 1 DAY AND TS < CURDATE();


Is it possible to upgrade mongodb cluster without upgrading arbiters?

The general recommendation is to have all members of a cluster or replica set running the same version, inclusing arbiters.  Hence, generally you should upgrade arbiters when you upgrade everything else.  In fact I would say to upgrade them first since if you have things configured correctly there will be no interruption in terms of operations from an arbiter upgrade (barring an outage elsewhere while you work).

They will probably work just fine with mixed versions, but since you would be then running in an untested (for long term use) configuration, if you run into any issues at all the first advice is going to be: upgrade.


Data structure design for supporting arbitrary number of columns in table or database

Given your "irregular" data, you might want to look at Magneto (see here and here). Originally designed for the clothing industry which can have many different products with many different attributes. It might just be a fit for your needs. There is a community edition so you can look at the code and (esp.) the database table structures and adapt to your own environment.

Be warned though! It is not a panacea - there are problems that come with the added flexibility. See my answer here which explains some of the pitfalls (be sure to follow the links) of this approach (known as EAV).


How can prevent writes to a Postgres schema for a period of time?

You can REVOKE access from that schema, but I am affraid the application will have some unwanted effects. PostgreSQL has fine tuned permission administration. And later you can GRANT access to that schema.


No need to load up the Smo object, you can query WMI to get this

gwmi -Query "select Name from cim_cluster" -namespace root\mscluster -computername [computername] | select Name


Should we create a schema per "project"?

Read this paper: Multi-Tenant Data Architecture. It extensively discusses on the pros and cons of 3 possible approaches (tenant ID in each table, schema per tenant, database per tenant). This approach is called "Shared database, Separate schemas" and is a valid approach.  Whether it is best for your case, I'm not in position to make that call. This is what the paper has to say about this approach:

A significant drawback of the separate-schema approach is that tenant
data is harder to restore in the event of a failure. If each tenant
has its own database, restoring a single tenant's data means simply
restoring the database from the most recent backup. With a
separate-schema application, restoring the entire database would mean
overwriting the data of every tenant on the same database with backup
data, regardless of whether each one has experienced any loss or not.
Therefore, to restore a single customer's data, the database
administrator may have to restore the database to a temporary server,
and then import the customer's tables into the production server—a
complicated and potentially time-consuming task.
The separate schema
approach is appropriate for applications that use a relatively small
number of database tables, on the order of about 100 tables per tenant
or fewer. This approach can typically accommodate more tenants per
server than the separate-database approach can, so you can offer the
application at a lower cost, as long as your customers will accept
having their data co-located with that of other tenants.

Read the paper.

which would improve select performance

If you are trying to do this split for performance reasons, you and your team are definitely on the wrong path. Address the perf issues by identifying them and fix them appropriately. Read How to analyze SQL Server performance.  Multi-tenancy ain't cheap, and you need to plan ahead and have an answer to simple questions like 'how do I deploy the next version of my app, which includes a database modification?' and 'how to restore the data for one particular project?'.  The issues you see now are indicative of app tunning problems (hint: you're missing indexes).
As a personal experience note: I've seen databases with millions of objects in them (which the 'shared database, separate schema' approach is naturally going to cause), and it wasn't pretty... I hope all your DB administration is 100% scripted/automated now, expanding SSMS tree views with to select the 1 in 1M objects is not going to work.


mysql run multiple selects on the same table at the same time

The workload needs to open sessions in parallel, instead of executing all queries serially from one bash script.

There is a MySQL tool aimed at just this, please see mysqlslap.

https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html


Large table random select with some peculiarity

First, ORDER BY RAND() will collect all the rows, tack on RAND(), sort, and then start work.

Actually, it is not that bad, since you have filtering.  But it may not help a lot.

AND id BETWEEN 1 AND 200000

limits the work to 200K instead of 800K.

The other filters involve what might be a terribly inefficient construct:  NOT IN ( SELECT ... ).  It may be turned into a different construct.  Please provide EXPLAIN SELECT  ...

DATE(added) = CURDATE()

Assuming added is DATETIME or TIMESTAMP, and there are not values in the future, then change to

added >= CURDATE()

and have INDEX(added, id).

After I see the EXPLAIN, I may suggest using EXISTS or LEFT JOIN.


Is there a way to have a system generated report of list of DBA in SQL Server?

This gives the list of users/logins/groups that have sysdba which sounds like your "Database Administrator" role.  This is not all logins, just the ones with the most powerful role.

USE master
GO

SELECT  p.name AS [loginname] ,
        CASE
        WHEN p.is_disabled = 1
        THEN 'LOCKED'
        ELSE 'OPEN'
        END as 'Account Status'
FROM    sys.server_principals p
        JOIN sys.syslogins s ON p.sid = s.sid
WHERE   p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
        -- Logins that are not process logins
        AND p.name NOT LIKE '##%'
        -- Logins that are sysadmins
        AND s.sysadmin = 1
GO

Or you may prefer this query that shows who has the privileged roles on the database.  As a comment indicates if you want to focus on what a login can do you would include the securityadmin role as users with this role can elevate their privileges.  See here for the documentation.  If there is poor access control you never know what you will find...

select * from sys.syslogins
go
select * from sys.sql_logins
go
sp_helprolemember 'db_securityadmin'
go
sp_helprolemember 'db_owner'
go
sp_helprolemember 'db_accessadmin'
go
sp_helpsrvrolemember 'sysadmin'
go
sp_helpsrvrolemember 'serveradmin'
go
sp_helpsrvrolemember 'securityadmin'
go


Fetching index avg fragmentation for each database

This script will loop through all databases and execute the selective reindex script.

SET QUOTED_IDENTIFIER ON
GO

SET NOCOUNT ON;

declare @dbname nvarchar(100)
declare @reindexcommand varchar(max)

DECLARE dbreindex CURSOR
FOR SELECT quotename([name]) from sys.databases
WHERE database_id <>2
AND is_read_only = 0

open dbreindex
while(1=1)
    begin
        FETCH NEXT FROM dbreindex
        INTO @dbname

        IF @@FETCH_STATUS < 0
        BREAK
        SELECT 'Rebuilding/Reorganizing indexes on '+@dbname+' Starting a t'+convert(varchar(50),getdate())+''
        SELECT ''

        set @reindexcommand = '
USE '+ @dbname + ';
SET NOCOUNT ON
DECLARE @reorg_frag_thresh   float   SET @reorg_frag_thresh   = 10.0
DECLARE @rebuild_frag_thresh float   SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor         tinyint SET @fill_factor         = 80
DECLARE @report_only         bit     SET @report_only         = 0
DECLARE @objectid       int
DECLARE @indexid        int
DECLARE @dbid           int
DECLARE @partitioncount bigint
DECLARE @databasename   nvarchar(130)
DECLARE @schemaname     nvarchar(130)
DECLARE @objectname     nvarchar(130)
DECLARE @indexname      nvarchar(130)
DECLARE @partitionnum   bigint
DECLARE @partitions     bigint
DECLARE @frag           float
DECLARE @command        nvarchar(4000)
DECLARE @intentions     nvarchar(4000)
DECLARE @table_var
TABLE(objectid     int,
indexid      int,
partitionnum int,
frag         float)
SET @databasename = '''+@dbname+'''
SET @dbid = DB_ID()
INSERT INTO    @table_var
SELECT    [object_id] AS objectid,
[index_id] AS indexid,
[partition_number] AS partitionnum,
[avg_fragmentation_in_percent] AS frag
FROM    sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, ''LIMITED'')
WHERE    [avg_fragmentation_in_percent] > @reorg_frag_thresh
AND    index_id > 0

DECLARE partitions CURSOR
FOR    SELECT * FROM @table_var
OPEN partitions
WHILE (1=1)
BEGIN
        FETCH NEXT FROM partitions
        INTO @objectid, @indexid, @partitionnum, @frag
        IF @@FETCH_STATUS < 0
        BREAK
        SELECT      @objectname = QUOTENAME(o.[name]),
                    @schemaname = QUOTENAME(s.[name])
        FROM        sys.objects AS o WITH (NOLOCK)
        JOIN sys.schemas as s WITH (NOLOCK)
        ON s.[schema_id] = o.[schema_id]
        WHERE       o.[object_id] = @objectid
        SELECT      @indexname = QUOTENAME([name])
        FROM        sys.indexes WITH (NOLOCK)
        WHERE       [object_id] = @objectid
        AND         [index_id] = @indexid
        SELECT      @partitioncount = count (*)
        FROM        sys.partitions WITH (NOLOCK)
        WHERE       [object_id] = @objectid
        AND         [index_id] = @indexid
        SET @intentions = @databasename + N''.'' + @schemaname + N''.'' + @objectname + N''.'' + @indexname + N'':''
        SET @intentions = ''====='' + @intentions
        SET @intentions = @intentions + N'' FRAGMENTATION: '' + CAST(@frag AS nvarchar) + N''%''
        IF @frag < @rebuild_frag_thresh
            BEGIN
                SET @intentions = @intentions + N'' OPERATION: REORGANIZE''
                SET @command = N''ALTER INDEX '' + @indexname + N'' ON ''+ @databasename + N''.''+@schemaname + N''.'' + @objectname + N'' REORGANIZE''
            END
        IF @frag >= @rebuild_frag_thresh
            BEGIN
                SET @intentions = @intentions + N'' OPERATION: REBUILD''
                SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @databasename + N''.''+ @schemaname + N''.'' + @objectname + N'' REBUILD''
            END
        IF @partitioncount > 1
            BEGIN
                SET @intentions = @intentions + N'' PARTITION: '' + CAST(@partitionnum AS nvarchar(10))
                SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10))
            END
        IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100
            BEGIN
                SET @intentions = @intentions + N'' FILL FACTOR: '' + CAST(@fill_factor AS nvarchar)
                SET @command = @command + N'' WITH (FILLFACTOR = '' + CAST(@fill_factor AS nvarchar) + '')''
            END
        IF @report_only = 0
            BEGIN
                SET @intentions = @intentions + N'' EXECUTING: '' + @command
                PRINT @intentions
                EXEC (@command)
            END
        ELSE
            BEGIN
                PRINT @intentions
            END
        END
CLOSE partitions
DEALLOCATE partitions
'
    --print @reindexcommand
    EXEC (@reindexcommand)
    SELECT'***************************************************************'

    end

close dbreindex
deallocate dbreindex
go


That's relatively easy. Using the UTL_FILE package in Oracle you can create a file in the servers filesystem and write the output of any PL/SQL statement to it. You can actually also use it to read data from a file.
http://psoug.org/reference/utl_file.html

You might also want to read up a little on the Directory object type as well, since that's a prerequisite for using the UTL_FILE package; http://psoug.org/reference/directories.html

--
Here's an example of a procedure that writes ascii data to an existing Oracle directory object. The user running the code needs to have read/write access to the Oracle Directory object.

CREATE PROCEDURE put_local_ascii_data (p_data  IN  CLOB,
                                    p_dir   IN  VARCHAR2,
                                    p_file  IN  VARCHAR2) IS
    -- --------------------------------------------------------------------------
      l_out_file  UTL_FILE.file_type;
      l_buffer    VARCHAR2(32767);
      l_amount    BINARY_INTEGER := 32767;
      l_pos       INTEGER := 1;
      l_clob_len  INTEGER;
    BEGIN
      l_clob_len := DBMS_LOB.getlength(p_data);

      l_out_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);

      WHILE l_pos <= l_clob_len LOOP
        DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer);
        IF g_convert_crlf THEN
          l_buffer := REPLACE(l_buffer, CHR(13), NULL);
        END IF;

        UTL_FILE.put(l_out_file, l_buffer);
        UTL_FILE.fflush(l_out_file);
        l_pos := l_pos + l_amount;
      END LOOP;

      UTL_FILE.fclose(l_out_file);
    EXCEPTION
      WHEN OTHERS THEN
        IF UTL_FILE.is_open(l_out_file) THEN
          UTL_FILE.fclose(l_out_file);
        END IF;
        RAISE;
    END;

The p_data parameter is a CLOB that would ( in this case ) contain all the ascii data being written to the p_dir directory and the p_file name ( which gets created at runtime ).
You might need to do some clob conversions on your data, depending on the data types in your query results that you'd use to populate the clob object.


MySQL and AUTO_INCREMENT columns in multiple-column index for InnoDB tables: support status?

Under normal circumstances, I would have closed this question as a duplicate. There are already close votes. In this particular instance, since you put a bounty on this question, I'll try to answer it as best as I can.

Look at the first post you mentioned. Note the table definition

CREATE  TABLE `issue_log` (
`sr_no` INT NOT NULL AUTO_INCREMENT ,
  `app_id` INT NOT NULL ,
  `test_id` INT NOT NULL ,
  `issue_name` VARCHAR(255) NOT NULL ,
primary key (app_id, test_id,sr_no)
);

The auto_increment column is sr_no. This is neither the only column nor the leftmost column in the primary key definition. Now, if the primary key was written

primary key (sr_no, app_id, test_id)

then sr_no would be leftmost. No error would have occurred with InnoDB.

When you ran the command

CREATE TABLE `tbl1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `data` datetime NOT NULL,
  PRIMARY KEY (`id`,`data`)
) ENGINE=InnoDB;

your auto_increment column id was leftmost.

You made reference to a second post. Please note that in the answer to that second post, it clearly notes that MyISAM and BDB storage engines support auto_increment ids. When it comes to MyISAM, the auto_increment behavior allows an auto_increment number to be paired with one or more keys in such a way that the number 1 can appear multiple times but be associated uniquely with the columns. I have discussed this a few times over the years:

Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Jun 10, 2012 : MySQL get next unique value without auto increment
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin
Aug 28, 2013 : How to go about modeling the main object of relationship?

Keep in mind that while InnoDB support for an auto_increment column in a PRIMARY KEY exists, it does not have the same auto_increment behavior as MyISAM. MyISAM can bind with other columns to allow a number to exist multiple times, InnoDB does not do that. Once an auto_increment value is used, it is unique for the whole table.

The excerpt you found

  an InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column.

comes from a page in the MySQL Documentation that has to do with how replication sees and handles auto_increment values. This is a vital issue since the order data are written on a Master may get serialized in a different order on a Slave.

Just recently (back on Jun 16, 2017) I answered Does it ever make sense to create an index with additional columns after the primary key?, where I mentioned from the MySQL Documentation that when there is an auto_increment column in a partitioned table, it is mandatory that the column is included in the PRIMARY KEY along with the columns that define the sharding of the table rows.

To conclude, let me just say this: support for auto_increment columns in a PRIMARY KEY in InnoDB does not come with any fringe benefits or additional bell-and-whistles like it does with MyISAM. That's why the MySQL Documentation does not have a lot written about it.

Support for this was mentioned long ago. If you look in the MySQL 5.1 Documentation on InnoDB Restrictions, you see bulletpoint #15

  For an AUTO_INCREMENT column, you must always define an index for the table, and that index must contain just the AUTO_INCREMENT column. In MyISAM tables, the AUTO_INCREMENT column may be part of a multi-column index.

There is more extensive stuff in the MySQL 5.1 Documentation AUTO_INCREMENT Handling in InnoDB. It is also available in the MySQL 5.5 Documentation. Unfortunately, these are in the old Documentation format. This is probably why you can't find anything now. Just be assured that the support is there.


Can SQL Server 2016 extract node names from JSON?

Select [key] from default OPENJSON schema.

DECLARE @json_doc nvarchar(4000) = '{"Name1":"Value1", "Name2":"Value2"}';

SELECT [key]
FROM OPENJSON(@json_doc);
GO

| key   |
| :---- |
| Name1 |
| Name2 |

dbfiddle here


I would really advise to not have a separate "totals" table that you have to maintain. There would be many downsides to doing so:

under high volume, you might get transactional locks
it's possible that your totals get "out of sync" with the source table. If/when this happens, you'd have to take an outage or block insertion of new votes while you're trying to get the totals correct.

Anyhow, I believe you can get a high-performance, real-time vote totals with simple code, without storing separately any totals information and therefore without needing a trigger. You also won't have to worry about any INSERT vs. UPDATE logic.

Make sure your vote_question table has an index (nonunique) on the question_id column. This will ensure performance for real-time.

Here's a totals query (all questions, summarized):

SELECT question_id
   , SUM((vote_question_type)='1') question_total_plus
   , SUM((vote_question_type)='-1') question_total_minus
   , count(*) question_total_mixed
FROM vote_question
GROUP BY question_id;

If you don't want to have to put that code in the application side, then no problem, just create it as a database view:

CREATE VIEW vote_question_totals
  AS
SELECT question_id
   , SUM((vote_question_type)='1') question_total_plus
   , SUM((vote_question_type)='-1') question_total_minus
   , count(*) question_total_mixed
FROM vote_question
GROUP BY question_id;

Then from your application/client side, you can reference the view just as though it were a table (and provide a question_id).

SELECT question_id, question_total_plus, question_total_minus, question_total_mixed
  FROM vote_question_totals
  WHERE question_id = '???' ;


You have to specify the format string twice, once for database backupsets, and once for the archivelog backups, for example:

backup as compressed backupset database format '/path/%U' plus archivelog format '/path/%U';


Select the same field based on different foreign keys

The query worked

  select parent.product_name as parent_product_name, child.product_name as child_product_name
  from plugins_t as parent inner join parent_2_childs_t p2ct on parent.rid = p2ct.parent_plugin_id
  inner join plugins_t as child on child.rid = p2ct.child_plugin_id


Which database could handle storage of billions/trillions of records?

In a company I work for we are dealing with similar amount of data (around 10 TBs of realtime searchable data). We solve this with Cassandra and I would like to mention couple of ideas that will allow you to do O(1) search on a multi TBs database. This is not specific to Cassandra db though, you can use it with other db as well.
Theory

Shard your data. There is no way a single server will reliably and realistically hold such volume of data.
Be ready for hardware faults and whole node failures, duplicate the data.
Start using many back-end servers from the beginning.
Use many cheaper commodity servers, compared to top-end high performance ones.
Make sure data is equally distributed across shards.
Spend a lot of time planning your queries. Derive API from the queries and then carefully design tables. This is the most important and prolonged task.
In Cassandra, you can design a composite column key and get access to that key in O(1). Spend time working on them. This will be used to access searchable records instead secondary index.
Make use of wide rows. They are useful for storing time-stamped events.
Never perform full-scan or in fact any operation more than O(Log N) on such volume. If you require anything more than O(Log N), offload such operations to Map-Reduce algorithms.

Practice

Don't spend time building OS images or installing servers on physical machines. Use cloud based providers for quick prototyping. I worked with Amazon EC2 and can highly recommend it for its simplicity, reliability and speed of prototyping.
Windows machines tend to be slower during boot time and take considerably more resources being in Idle state. Consider using Unix-based OS. Personally, I found Ubuntu server to be a reliable OS, but moreover there is a pretty good community at askubuntu
Think about networking, nodes shall ideally be close to each other to allow fast gossiping and meta-data exchange.
Do not go into extreme cases: really wide column rows or exceptionally long column families (tables). Best performance is achieved in the sane boundaries - if db supports that many N rows by design, it doesn't mean it performs well.
Our search takes about 3-5 seconds, much is due to the intermediate nodes between UI and the database. Consider how to bring requests closer to the database.
Use a network load balancer. Choose an established one. We use HAProxy, which is simple, but dead fast. Never had problems with it.
Prefer simplicity to complex solutions.
Look for free open-source solutions, unless you are backed up by a corporation's size budget. Once you go more than several servers, the costs of infrastructure might go sky high.

I do not work for Amazon and have no relations with HAProxy and Ubuntu teams. This is a personal opinion rather than any sort of promotion.


Set maximum cpu usage to a single job in SQL server

You can set the job to be owned by a specific login, and use the Resource Governor to limit CPU for that login.

CREATE RESOURCE POOL [Pool_ThatJob] WITH
(
    max_cpu_percent=50,
    cap_cpu_percent=50 -- 2012+ only
);
GO

CREATE WORKLOAD GROUP [Group_ThatJob]
  USING Pool_ThatJob;
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

CREATE FUNCTION dbo.RGClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT CASE ORIGINAL_LOGIN() WHEN N'that_login'
    THEN N'Group_ThatJob' ELSE N'default' END);
END
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.RGClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

This won't take effect if the job is already running; it will come into play the next time the job starts (or if that user establishes any other sessions).


Don't confuse the primary key with the key of the clustered index. Though they are often the same, they are independent.  You just need your primary key to uniquely identify a record in your table across all the databases that have a copy of it.

  Since recalling this data needs to be performance driven, I know right off the bat I want a clustered index on DATETIME2.

I agree with this choice, since you will primarily be writing, reading, and merging data by this column.  Be sure to pick the right precision for your DATETIME2.

As for the primary key, I suggest making it (system, id), where system is a unique system identifier and id is an IDENTITY value. A CHAR(1) or TINYINT will suffice for the system identifier if you have only a handful of systems.

Don't bother managing the ranges of the IDENTITY values across the different systems.  They can overlap as they please since the system identifier will distinguish records from different systems that share the same id.


How to modify the precision of a datetimeoffset column?

This should be a straightforward

alter table tab
alter column x datetimeoffset(0);


Couple of different approaches you could try with a Profiler trace.

Capture SQL:StmtCompleted, with a column filter on TextData %TableName%.
Capture Lock:Acquired event, with a filter on ObjectID with the id of the table.


do a command like this

ps -ef | grep "MONGO"

You will see output like

502  8066     1   0 12:23pm ??        12:25.00  /Applications/PyCharm.app/Contents/MONGO/pycharm

Now before you do the next command understand that killing a process when it may or may not be doing something is a VERY VERY DANGEROUS habit.

So if this corrupts your MongoDB Database - it is because you did this command.....

kill -9 8066

8066 is the 2nd number you can see from the ps command.

You can tell if the process is consuming cpu/disk using other systems tools such as

top
sar
instat

If these processes look like the MongoDB Process is doing some work - then the chances of something bad usually increases.

Good Luck !!

Thought: Ask the server to restart ??


How to unnest and GROUP BY elements of a JSON array?

The data type of the column people is json, as is the result of json_array_elements(people). And there is no equality operator (=) for the data type json. So you also cannot run GROUP BY on it. More:

How to remove known elements from a JSON[] array in PostgreSQL?

jsonb has an equality operator, so the "workaround" in your answer is to cast to jsonb and use the equivalent jsonb_array_elements(). The cast adds cost:

jsonb_array_elements(people::jsonb)

Since Postgres 9.4 we also have json_array_elements_text(json) returning array elements as text. Related:

How to turn JSON array into Postgres array?

So:

SELECT p.name, count(*) AS c
FROM   band b, json_array_elements_text(b.people) p(name)
GROUP  BY p.name;

It seems more convenient to get names as text instead of jsonb objects (double-quoted in text representation) and your "desired output" indicates you want / need text in the result to begin with.

GROUP BY on text data is also cheaper than on jsonb, so this alternative "workaround" should be faster for two reasons. (Test with EXPLAIN (ANALYZE, TIMING OFF).)

For the record, there was nothing wrong with your original answer. The comma (,) is just as "correct" as CROSS JOIN LATERAL. Having been defined earlier in standard SQL does no make it inferior. See:

What does [FROM x, y] mean in Postgres?

Neither is it more portable to other RDBMS, and since jsonb_array_elements() or json_array_elements_text() aren't portable to other RDBMS to begin with, that's also irrelevant. The short query does not get any clearer with CROSS JOIN LATERAL IMO, but the last bit is just my personal opinion.

I used the more explicit table and column alias p(name) and the table-qualified reference p.name to defend against possible duplicate names. name is such a common word, it might also pop up as column name in the underlying table band, in which case it would silently resolve to band.name. The simple form json_array_elements_text(people) name only attaches a table alias, the column name is still value, as returned from the function. But name resolves to it's single column value when used in the SELECT list. It happens to work as expected. But a true column name name (if band.name should exist) would bind first. While that won't bite in the given example, it can be a a loaded foot gun in other cases.

Don't use the generic "name" as identifier to begin with. Maybe that was just for the simple test case.

If the column people can hold anything but a plain JSON array, either query would trigger an exception. If you cannot guarantee data integrity, you might want to defend with json_typeof():

SELECT p.name, count(*) AS c
FROM   band b, json_array_elements_text(b.people) p(name)
WHERE  json_typeof(b.people) = 'array'
GROUP  BY 1; -- optional short syntax since you seem to prefer short syntax

Excludes violating rows from the query.

Related:

When can we use an identifier number instead of its name in PostgreSQL?


Make SQL Server SSDT Database Project Reference Different in Publish Profile

I'm not sure how to do this in the UI, however, the publish profiles are xml files, and SQL Command variables and their values can be stored within them.  In this specific case, you'd want to specify the value of the $(ProductVariable) like so for your ProductDev deployment:

  <ItemGroup>
    <SqlCmdVariable Include="ProductVariable">
      <Value>ProductDev</Value>
    </SqlCmdVariable>
  </ItemGroup>

You'd add that ItemGroup to your publish profile after the </Property Group> before the </Project>


Nested table as condition prevents using index

Hmmm.... this is an interesting one. The Oracle optimizer is a bit of a black box as far as mere mortals like myself are concerned... Jonathan Lewis wrote a 536 page book on the topic and that's just the **fundamentals* (and it ain't bedtime reading!).

Two questions.

a) Did you flush the caches before/after each query - maybe the first affects the second - i.e. the optimizer figures out after the first query that it's not worth using an index?

b) What happens if you bump up those numbers in 'M' and 'D'? Does there come a point where it switches back to an INDEX scan? I'm guessing that such a point will come (but see point a)

Just a couple of thoughts - I don't have an Oracle system to hand.


Currently Invoke-Sqlcmd does not support the ability to connect to a read-only secondary replica.

The SQL Server, and some of the PowerShell community, however started working with the Program Manager at Microsoft to get SQLPS and SSMS improvements organized. A Trello board was opened in the past month that we are showing by votes what priority we want Microsoft to take on the improvements. This has actually already gotten some things added to the SSMS March preview.

I created a card for the Application Intent functionality so go vote it up and the connect item linked.

Right now your only option is to call sqlcmd within PowerShell using the -K ReadOnly.


Need help in restore some rows into mySQL database frm myd myi

In order to restore the MyISAM called table_name into database_name you must do the following

STEP 01) Run this query : show variables like 'datadir';

This will tell you what the base directory is for data. For this example, let's use the default (/var/lib/mysql)

STEP 02) Copy table_name.frm to /var/lib/mysql/database

STEP 03) Copy table_name.MYD to /var/lib/mysql/database

STEP 04) Copy table_name.MYI to /var/lib/mysql/database

STEP 05) chown mysql:mysql /var/lib/mysql/database/table_name.*

That's it. A mysql restart is not needed because the information_schema database is very sensitive and detects folder changes very quickly.

To make sure the restored table is known to mysql, do the following:

STEP 06) Run this query

SELECT * FROM information_schema.tables
WHERE table_schema = 'database_name'
AND table_name = 'table_name'\G

You should see information about the table size and change dates

STEP 07) Run these commands

use database_name
show tables;

You should see the restored table in the listing


Any ideas on how to parallelize mysql_upgrade?

I upvoted your question because this is a common problem, and a solution would be welcome. But unfortunately, I believe that there is no way to parellalize it. mysql_upgrade always operates on the whole server directory, there is no way to only upgrade one database, and it reads the system tables.