Why does my query search datetime not match?

As several others have mentioned in comments and other answers to your question the core issue is 2015-07-27 23:59:59.999 is being rounded to 2015-07-28 00:00:00.000 by SQL Server. Per the documentation for DATETIME:

  Time range - 00:00:00 through 23:59:59.997

Note that the time range can never be .999. Further down in the documentation it specifies the rounding rules that SQL Server uses for the least significant digit.

Notice that the least significant digit can only have one of three potential values: "0", "3", or "7".

There are several solutions/workarounds for this that you can use.

-- Option 1
SELECT
    *
FROM A
WHERE posted_date >= '2015-07-27 00:00:00.000'
  AND posted_date <  '2015-07-28 00:00:00.000' --Round up and remove equality

-- Option 2
SELECT
    *
FROM A
WHERE posted_date >= '2015-07-27 00:00:00.000'
  AND posted_date <=  '2015-07-27 23:59:59.997' --Round down and keep equality

-- Option 3
SELECT
    *
FROM A
WHERE CAST(posted_date AS DATE) = '2015-07-27' -- Use different data type

-- Option 4
SELECT
    *
FROM A
WHERE CONVERT(CHAR(8), DateColumn, 112) = '20150727' -- Cast to string stripping off time

-- Option 5
SELECT
    *
FROM A
WHERE posted_date BETWEEN '2015-07-27 00:00:00.000'
  AND '2015-07-27 23:59:59.997' --Use between

Out of the five options I've presented above I would consider options 1 and 3 the only viable options.  They convey your intent clearly, and aren't going to break if you update data types.  If you are using SQL Server 2008 or newer I think option 3 should be your preferred approach.  That is especially true if you can change away from using the DATETIME datatype to a DATE data type for your posted_date column.

Regarding option 3, a very good explanation about some issues can be found here: Cast to date is sargable but is it a good idea?

I don't like options 2 and 5 because the .997 fractional seconds is going to be just another magic number that people are going to want to "fix." For some more reasons why BETWEEN isn't widely embraced you might want to checkout this post.

I don't like option 4 because converting data types to a string for comparison purposes feels dirty to me.  A more qualitative reason to avoid it in SQL Server is it impacts sargability aka you can't perform an index seek and that will frequently result in poorer performance.

For more information on the right way and wrong way to handle date range queries checkout this post by Aaron Bertrand.

In parting you would be able to keep your original query and it would behave as desired if you change your your posted_date column from a DATETIME to a DATETIME2(3).  That would save storage space on the server, give you greater accuracy at the same precision, be more standards compliant/portable, and allow you to easily adjust the accuracy/precision if your needs change in the future. However, this is only an option if you are using SQL Server 2008 or newer.

As a bit of trivia the 1/300 of a second accuracy with DATETIME seems to be a hold over from UNIX per this StackOverflow answer. Sybase which has a shared heritage has a similar 1/300 of a second accuracy in their DATETIME and TIME data types but their least significant digits are a touch different at "0", "3", and "6". In my opinion the 1/300 of a second and/or 3.33ms accuracy is an unfortunate architectural decision since the 4 byte block for the time in SQL Server's DATETIME data type could have easily supported 1ms accuracy.


MariaDB Incremental Backup always creates full backup (mariabackup/xtrabackup)

MyISAM tables are not transactional so they are copied in full with each incremental backup.


SQL Server 2016- Email Alerts fo Errors/ Warnings

Your question is very broad.  I hope you have some understanding or clarity on how SQL Server alerts/warnings work and what are the things you need to monitor based on your business need.

As a start read this about 3 types of alert that you can configure via SQL Server Agent.

Alerts

There are numerous articles written online about this topic and some scripts are available that you can use once you understand what you need.

If you have replication set up you will see a bunch of predefined alert show up in your SQL Server agent under alerts. Read details below:

Configure Predefined Replication Alerts (SQL Server Management Studio)

There will always be situation that you need to be alerted that is not covered above and you have to roll out your own scripts.  That can be using tsql, Powershell and run via SQL Agent Jobs.  If you manage many SQL Server instances you might have consider having a central monitoring/DBA instance and manage alerts from there.

Few resource I am listing for you to start with:

SQL Server Alerts: Soup to Nuts by Robyn Page and Phil Factor
SQL Server Agent Alerts by Tim Radney
Blitz Result: No SQL Server Agent Alerts Configured
The Ten Commandments of SQL Server Monitoring by Adam Machanic


PostgreSQL isn't using lots of RAM, the kernel is using it for disk cache.

This is exactly what you want to happen. There is nothing to fix here.

"Free" RAM is wasted RAM. The best thing for a database server is for as much RAM as possible to be used as disk cache.


Postgres Number of Updates/Deletes Since Last Autovacuum

n_dead_tup in pg_stat_all_tables is the perfect way to monitor that, because that is the value that triggers autovacuum.

From v13 on, you also have to watch n_ins_since_vacuum, because autovacuum is also triggered by inserts there.

Normally, there is no reason to be afraid of anti-wraparound vacuum runs. PostgreSQL 9.6 introduced an optimization that allows such VACUUM runs to skip pages with only frozen tuples.

The exception might be a table that receives almost only inserts. You could potentially get massive anti-wraparound VACUUM runs there. A way to avoid that is to drastically lower autovacuum_freeze_max_age for such tables, so that you get anti-wraparound VACUUM runs there earlier and more often. This problem has been abolished with PostgreSQL v13.


How do I render GeoHashes with PostgreSQL?

PostGIS supports it with ST_GeoHash

  ST_GeoHash — Return a GeoHash representation (geohash.org) of the geometry.


You can use the APPLY operator to count the number of employees active at the termination date.  As I looked at the question at first I missed that there were different companies, so once I added AND company_id = pr.company_id to the query I get the result you are expecting.

SELECT * FROM #PersonnelRecord pr
OUTER APPLY (
    SELECT count(person_id) as person_count FROM #PersonnelRecord
    WHERE pr.exit_date > entry_date
        AND pr.exit_date <= exit_date
        AND company_id = pr.company_id
     ) a
ORDER by person_id


If I fail over one database, do the others that share the same mirror endpoint fail over as well?

@mrdenny's answer is accurate that failing over one database will not result in all the other databases failing over as well.

However just to give more overview of what a database mirroring Endpoint is:

From BOL,

Connection management in Microsoft SQL Server 2005 and later versions is based on endpoints. An endpoint is a SQL Server object that enables SQL Server to communicate over the network. For database mirroring, a server instance requires its own, dedicated database mirroring endpoint. All mirroring connections on a server instance use a single database mirroring endpoint. This endpoint is a special-purpose endpoint used exclusively to receive database mirroring connections from other server instances.

To be more flexible, you can use T-sql for doing manual failover:

Turn mirroring off (break mirroring) on the mirror DB.

ALTER DATABASE <<DB NAME>> SET PARTNER OFF

Set the recover mod the DB to RECOVERY

RESTORE DATABASE <<DB NAME>> WITH RECOVERY

Fix and orphaned users if you are using SQL authentication

EXEC sp_change_users_login ‘Auto_Fix’ , ‘<<username>>’

EDIT:

Since you are using high safety mode (as per your screenshot), you can use the following from the principal server

ALTER DATABASE <<DB NAME>> SET PARTNER FAILOVER

Doing so, only the role reversal happens and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN.

Note that per this MSDN article

  Manual failover can be initiated only from the principal server.

If you want to automate the entire failover then refer Database Mirroring Automation


Unless you have a storage snapshot, restore point or backup of the database before the testing started, TRUNCATE is a way to go. You may need to disable FK constraints for that. Another way is to run expdp with CONTENT=METADATA_ONLY, drop the schema, and import the metadata back.


It is perfectly valid to increase the SGA_MAX_SIZE without changing the SGA_TARGET though it probably isn't going to change your performance.  Setting the SGA_MAX_SIZE to a larger value allows you to adjust the SGA_TARGET upward without restarting the database.  It won't affect the actual amount of RAM allocated to the SGA, that's controlled by the SGA_TARGET.  If your intention was to increase the size of the SGA from 2 GB to 4 GB, you need to adjust the SGA_TARGET.


PostgreSQL priority between CTE and real table

PostgreSQL searches the CTE namespace with scanNameSpaceForCTE as the very first thing it does in searchRangeTableForRel

if (!relation->schemaname)
{
    cte = scanNameSpaceForCTE(pstate, refname, &ctelevelsup);
    if (!cte)
        isenr = scanNameSpaceForENR(pstate, refname);
}

If there is no schema

check for a CTE
check for a "Ephemeral Named Relation"

This is similar to Variable Shadowing if there is no namespace, and what the spec otherwise demands.

Everything in PostgreSQL has a namespace, if you want to address the table as compared to the CTE, consider providing (qualifying) the namespace.

CREATE TABLE foo AS VALUES (0);

WITH foo AS ( VALUES (1) )
SELECT *
FROM ( VALUES (2) ) AS foo   -- inline virtual-table
UNION TABLE foo              -- CTE
UNION TABLE public.foo;      -- explicitly qualified the namespace;


The decision of whether to merge these tables or not should depend on whether the separated tables provide useful information.  Use the rules of database normalization.  What are the functional dependencies?  What are the candidate keys?

Season_League doesn't seem to have any information in it other than which leagues exist in which seasons (pure intersection)  League_Division just adds division to this pure intersection.  Division_Schedule doesn't seem to add any information at all.

One question I would have is whether it is useful to know that a league exists in a season.  If you combine the tables, then what you have is a division exists in a league in a season.  There is no "independent life" of a league except that it has a division.  There are other subtleties too, like whether a division can exist in more than one league within a given season.  If you combine the tables into one then this type of business rule could not be enforced with declarative referential integrity.  Does this matter to you?  It depends on what other manual or automated controls you're willing to put in place to manage this risk.

Without any more information about your intended business rules, I would say these three tables should definitely be combined.


For a case-sensitive join on the CLERK field, do I need to specify the collation in the join clause as well, or does the fact that it is specified at the DDL level mean that it isn't needed in the join?

Simple answer: No need to specify the collation in the query. DDL level collation (especially when it's the same on both sides and both sides are columns) will be used (which is why we specify it at the DDL level in the first place).

Detailed answer: there is a hierarchy of precedence for which collation to use in a given operation (concatenation, predicate, etc). The collation used not only depends on whether it's a column vs a literal, etc, but also whether it's Unicode vs non-Unicode, and even binary vs non-binary. The full description can be found here, Collation Coercibility in Expressions, which is the MySQL 5.7 documentation since you are using that version. One rather interesting rule is:

  For an operation with operands from the same character set but that mix a _bin collation and a _ci or _cs collation, the _bin collation is used. This is similar to how operations that mix nonbinary and binary strings evaluate the operands as binary strings, except that it is for collations rather than data types.

All of that being said, I need to point out that the statement "binary collations are case-sensitive", while extremely common, is definitely incorrect.

On a very basic level, sorting is different. Case-sensitive collations will sort "a" with "A" (though which one comes first can depend on the culture), "b" with "B", and so on. Binary collations will sort according to the underlying value / Code Point of each character, which becomes very apparent when uppercase and lowercase versions of a character separated by others according to their value.
Being case "sensitive" means that you can also be "INsensitive" to other properties of characters, the main one being accents. That is also the only other property when it comes to non-Unicode character sets, but Unicode allows for Kana type sensitivity, width sensitivity, and SQL Server (as of version 2017) even allows for variation selector sensitivity. Binary collations do not allow for a character to equal anything other than itself, even if other forms of it exist. Again, this doesn't really happen so much in non-Unicode character sets, but in Unicode there can be several versions of a character, including wide, superscript, subscript, italics, upside down (referred to as "turned"), etc. MySQL, starting in version 8.0 (as far as I can tell), is adding more variations to the sensitivity options (at least for the ut8mb4 character set and collations):

New collations in MySQL 8.0.0 (accent and case INsensitive)
MySQL 8.0.1: Accent and case sensitive collations for utf8mb4 (accent and case sensitive)
The MySQL 8.0.2 Milestone Release is available (accent sensitive, case INsensitive)

To illustrate both of these points, I have set up a demo on the awesome db<>fiddle. I had to use MySQL 8.0 not only to get the _ai_ci collation, but also because the COLLATE latin1_general_ci clause (2nd to last query, #5) had no affect (for some odd reason; documentation states that when collation name has only _ci, then _ai is implied, yet for both MySQL 5.6 and 5.7 on db<>fiddle, the behavior is still _as_cs or _bin).

AND, there are even other ways in which binary collations are not "case-sensitive". They cannot account for:

combining diacritics
expansions
contractions

I did not list or provide examples for these earlier because they do not pertain to 8-bit encodings, and latin1 is an 8-bit encoding. These are features of Unicode, and so should apply to any Unicode collation (though I have not tested them on MySQL, but they are implemented correctly in SQL Server).

P.S. I have a detailed explanation of all of this (including the Unicode-specific behaviors noted directly above) in the following post: No, Binary Collations are not Case-Sensitive. That is currently framed only in the context of SQL Server, but I can work in the example that I came up with for this answer when I have time. What's important is that the concept is the same across RDBMSs.

For reference (just in case db<>fiddle is inaccessible), the queries are:

Query 1

CREATE TABLE CLERK (
  CLERK_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL
);

CREATE TABLE CUSTOMER (
  CUSTOMER_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  NAME VARCHAR(10) NOT NULL,
  CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL,
  CLERK_CI_AI CHAR(3) CHARACTER SET latin1 COLLATE latin1_general_ci NULL
);

INSERT INTO CUSTOMER (NAME, CLERK, CLERK_CI_AI) VALUES('John Doe', 'Ü', 'Ü');

INSERT INTO CLERK (CLERK) VALUES('Ü');
INSERT INTO CLERK (CLERK) VALUES('ü');
INSERT INTO CLERK (CLERK) VALUES('U');
INSERT INTO CLERK (CLERK) VALUES('u');
INSERT INTO CLERK (CLERK) VALUES('Ù');
INSERT INTO CLERK (CLERK) VALUES('ù');
INSERT INTO CLERK (CLERK) VALUES('Û');
INSERT INTO CLERK (CLERK) VALUES('û');

INSERT INTO CLERK (CLERK) VALUES('Y');
INSERT INTO CLERK (CLERK) VALUES('y');
INSERT INTO CLERK (CLERK) VALUES('Ý');
INSERT INTO CLERK (CLERK) VALUES('ý');

Query 2

SELECT 0 AS "ver", C.*
FROM   CLERK C;

Query 3

SELECT "bin sort" AS "ver", C.*
FROM   CLERK C
ORDER BY C.CLERK;

Query 4

SELECT "cs sort" AS "ver", C.*
FROM   CLERK C
ORDER BY C.CLERK COLLATE latin1_general_cs;

Query 5

SELECT 1 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK = C.CLERK;

Query 6

SELECT 2 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK = C.CLERK COLLATE latin1_bin;

Query 7

SELECT 3 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK_CI_AI = C.CLERK;

Query 8

SELECT 4 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK_CI_AI = C.CLERK COLLATE latin1_bin;

Query 9

# is accent-sensitive even though documentation states it should be
# accent-INsensitive
#
SELECT 5 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK_CI_AI = C.CLERK COLLATE latin1_general_ci;

Query 10

SELECT 6 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON CONVERT(S.CLERK_CI_AI using utf8mb4)
       = CONVERT(C.CLERK USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;


A group inherits permissions to all the members that it contains. So NO, you cant do that with AD Group.

You can use database roles but that has to be on individual logins in your case.

Best is to

Remove that login from AD group and grant indivudual permission or make it part of database role and grant permissions to that role.
Ask that user to use sql authentication so that the person can view table.


Running a sql server Trace of 1 GB using sqlcmd

I assume the original trace contains the actions of multiple clients, with many queries executing in parallel? Your playback via SQLCMD is occuring serially, which is why it will take much longer to complete the same workload.

SQLCMD is the wrong tool for this, you need the RML Utilities. A very brief outline of the tools (taken from a previous answer):

ReadTrace converts Profiler traces from .trc files to .rml (Replay Markup Language).
OStress is used to replay .rml and .sql files against a server.
ORCA (OStress Replay Control Agent) co-ordinates replay across distributed instances of OStress.
Reporter provides performance analysis reports.

This way you'll get the parallel playback you need to draw any meaningful comparison between the workloads on different instances.


VLDB is stuck in recovery state. It's a permission issue, now what should I do?

SQL server service account does not have permission to access the file.

Navigate to the folder in which this file is residing
Grant Full control privilege on this folder to the SQL server service account.
Bring the DB Offline - Online or Restart SQL server services.


The control file (or recovery catalog) holds information about the paths to the data files, online redo log files, archived redo log files, and other information RMAN uses during backup and recovery.

Whenever redo log switch occurs, Archiver (ARCn) process copies online redo logs to archive log location specified by LOG_ARCHIVE_DEST_n initialization parameter(s) and adds the appropriate record to the control file (or recovery catalog) which contains the path to the archived log, log sequence number and other related info.

Because the path has changed, you should either update information about existing archived redo log files and their paths in the RMAN recovery repository or ignore the existing archived redo log files and perform full database backup so that you can recover your database in case of media failure.

The first scenario can be accomplished with the following commands in RMAN:

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;

Now you can add info about existing archived redo log files using CATALOG RMAN command:

RMAN> catalog archivelog '/ora/archivelog/O1_MF_1_1508_8VDHNOLY_.ARC';

Or you can use CATALOG START WITH to catalog multiple files specifying the path to the files:

RMAN> catalog with '/ora/backups/';

Set the LOG_ARCHIVE_DEST_n initialization parameter value correctly so that new archived redo log files are copied to the appropriate locations:

SQL> alter system set log_archive_dest_1 =
  'LOCATION=USE_DB_RECOVERY_FILE_DEST'
  scope = both;

Manually switch the redo log group to make sure ARCn copies the archived redo log files to the new location:

SQL> alter system switch logfile;

If the new archived redo log files are successfully copied to the destination you specified, you can backup the database including old archived redo log files and new ones in RMAN:

RMAN> backup database plus archivelog delete input;

In the second scenario, you perform the full backup of the database without including the archived redo log files from the now invalid path You just clean up existing records about archived redo log files in RMAN repository and make the full database backup including all subsequent archived redo log files copied to the valid location. The commands used are the same, you just omit the CATALOG command.


Finding gaps-and-islands with repeating group numbers


set serveroutput on size unlimited
for i in (select 'alter user '||username||' account unlock' cmd
            from dba_users
           where account_status = 'LOCKED'
          )
loop
   dbms_output.put_line (i.cmd);
 --  execute immediate i.cmd;
end loop;
end;
/

This works. Be careful, I have seen ppl doing similar things without testing, effectively destroying databases, especially when using patterns and manipulating database objects with the results. If the output is OK, activate the execute immediate.


SSAS tabular browse Excel relationships not working

I think i've found the problem.  SSAS doesn't seem to relate data between tables if there are no measures defined.  I added a simple measure like grade count and voila i have my data relationship.


Why is the schema/structure dump so slow?

This may be a consequence of slow queries issued by activerecord-oracle_enhanced-adapter to determine the database structure, eg. issues/2004


SSMS fail to connect localhost SQL Server 2008 R2 suddenly

If it's a named instance well, then, you should have always been using localhost\instance. This is how it's supposed to work. You shouldn't have been able to connect before and I suspect there is a vital detail missing that would explain that.

Possible explanations (there may be others): the instance was the only one on the machine, and maybe an alias was set up or it happened to be running on port 1433 or the browser was able to redirect to the instance. The alias has since been removed or the service has been restarted and/or the instance is for other reasons (such as a manual config change) now on a different port or the browser service has been disabled.

In any case, the solution is to update your connections to reference the full path to the instance, namely: localhost\instance or as Kin suggests make sure the instance is hard-coded to run on a specific port and then use localhost,<port number>.


mysql - is it possible to create a partition expression based on two keys / indexes?

I had written an interesting alternative of implementing your own manual hash indexes for your table, and then I made maths and realised your constraints:

Having into memory 3 bigints will cost you 500*10^6*(8*8*8)/(1024*1024*1024) = 11.17GB that you do not have. RDS is simply not adequate for you anymore, as it is not flexible enough to try some alternative engines -other than InnoDB (you need an engine that works well with indexes on disk/clustering on several keys/hash indexes)- and probably too costly to handle a large table like that.

You need either a higher-end instance or migrate to EC2 to deploy an alternative engine.

Best recommendation that I could give you for your current constraints (7GB ram, InnoDB):

Use your most frequently accessed keys as your primary key, partition by RANGE on that (lets call it id_1). Do not create any other secondary keys:

CREATE TABLE `t1` (
    id_1 bigint unsigned PRIMARY KEY,
    id_2 bigint unsigned not null,
    col3, col4 ... colN ...
) ENGINE=InnoDB
PARTITION BY RANGE (id_1) (
    PARTITION p0 VALUES LESS THAN (n),
    PARTITION p1 VALUES LESS THAN (m),
    ...
);

Create a separate table with (id_2, id_1):

CREATE TABLE `t1_id2_index` (
    id_2 bigint unsigned PRIMARY KEY,
    id_1 bigint unsigned not null,
) ENGINE=InnoDB
PARTITION BY RANGE (id_2) (
    PARTITION p0 VALUES LESS THAN (n),
    PARTITION p1 VALUES LESS THAN (m),
    ...
);

Obviously you will have to insert on this second table each time you insert on the first one. You may think this is worse, but it will not be that bad as you are getting rid of huge merging processes of the secondary keys and minimising memory usage (which is your goal, afterwards).

This will only access 1 partition on access by id_1 and 2 partitions (one on each separate table) on access through id_2:

SELECT * FROM `t1` WHERE id_1 = 123;
-- or
SELECT STRAIGHT_JOIN t1.*
FROM `t1_id2_index`
JOIN `t1`
ON t1_id2_index.id_2 = 456
   and
   t1.id_1 = t2.id_1;

If your most frequent accesses are on the latest partitions, you will get the desired improvements -make sure you partition with that in mind. You can check partition pruning by using EXPLAIN PARTITIONS. Of course, if access patterns are completely random, you will not get any advantage. The goal is to maintain everything on disk except for a small set of primary keys for both id_1 and id_2 and selected rows.

You may want to minimise read ahead caching and tune innodb_old_blocks_pct and innodb_old_blocks_time for more effective caching/eviction on the buffer pool. I hope you are also using SSDs.

This is not beautiful, but please refer to my initial suggestion of migrating away from SAAS for custom requirements.


What PUBLIC access means on tables in Oracle?

Grants and Revokes of Privileges to and from the PUBLIC Role

  You can grant and revoke privileges and roles from the role PUBLIC.

  Because PUBLIC is accessible to every database user, all privileges
  and roles granted to PUBLIC are accessible to every database user. By
  default, PUBLIC does not have privileges granted to it.

  Security administrators and database users should grant a privilege or
  role to PUBLIC only if every database user requires the privilege or
  role. This recommendation reinforces the general rule that, at any
  given time, each database user should have only the privileges
  required to accomplish the current group tasks successfully.

  Revoking a privilege from the PUBLIC role can cause significant
  cascading effects. If any privilege related to a DML operation is
  revoked from PUBLIC (for example, SELECT ANY TABLE or UPDATE ON emp),
  then all procedures in the database, including functions and packages,
  must be reauthorized before they can be used again. Therefore, be
  careful when you grant and revoke DML-related privileges to or from
  PUBLIC.

If you do not use the above packages, it will not have any impact on your application.


What ports does SQL Server need to communicate with a web server?

I would suggest following the guildelines provided by Microsoft here:
TCP/IP port numbers required to communicate to SQL overall a firewall

  By default, when an application requests a socket from the system for an outbound call, a port number between the values of 1024 and 5000 is supplied.

You brought up the above statement in your comments to the other answers. This is something you have to handle on your firewall. The client chooses the port it wants to be communicated back on. As stated later down in the article above your firewall rules have to allow the dynamic allocation to occur.

I don't work with the application side but there might be something you do to force the port it wants to talk on.


Sharing a single primary key sequence across a database?

Acceptable? Sure. Common? No. Beneficial? Doubtful.

At my old job we inherited a system where they had a central sequence generator (this was a SQL Server system long before SEQUENCE was introduced in SQL Server 2012). It wasn't really a performance bottleneck and shouldn't be unless you're generating hundreds of thousands of values per second. But it made all of the code a lot more complex than it had to be, for no good reason. The intent of the design was to be sure that if something in the system was assigned an ID value of 12, only one thing in the system could have the ID 12. This seemed quite obtuse to me and I never understood it. If I have a customer with CustomerID = 12, why does that preclude me from having an order with OrderID = 12?

I do see the usefulness of a central sequence generator if you have multiple systems and you are generating IDs for a certain type of entity (say, a customer or an order) from these multiple systems. A central sequence can dole out new values to multiple systems without being a bottleneck (just a single point of failure) and without fear of two systems generating the same ID.


If you don't have backup.

I think the recommended way to recover that databases is start MySQL with innodb_force_recovery = 4 (or higher values) and dump the databases to a SQL.Then drop it and recover from backup. Instead of dropping the original database I prefer to create the new databases with another name or in another server and check the content first.

STEPS TO BE FOLLOWED

In mysqld section of my.cnf add a line innodb_force_recovery = 4  and then restart MySQL server using /etc/init.d/mysql restart.
Take backup.
Restore it on other server and verify the contents.

Please also have a look at Forcing InnoDB Recovery.

UPDATE : For your comment how to progress on that so that it would not happen again

You should not kill MySQL it is not a good practice that may cause MySQL server to crash,whenever you make any configuration changes you should proceed as follows.

Safely stop MySQL Server using /etc/init.d/mysql stop
Make changes
Restart MySQL Server


You do not need a function to check if the temporary table exists. You do not even need the table. The query below gives all tables settings in all schemas (except of public and system schemas) in a database, with appropriate schema names:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE
    table_type = 'BASE TABLE'
    AND table_name = 'settings'
    AND table_schema NOT IN('information_schema', 'pg_catalog', 'public')

In the anonymous function below we shall build sql query instead of storing results in a temp table, according to the following pseudocode:

for all tables 'settings':
    build select query text and store it in 'vselect',
    append 'vselect' to 'vquery' joining consecutive selects with word 'UNION'

At the end of the loop we shall have in vquery something like this:

SELECT 'schema1', prefix, id, value FROM schema1.settings WHERE id = 'VERSION'
UNION
SELECT 'schema2', prefix, id, value FROM schema2.settings WHERE id = 'VERSION'
-- etc

and we are going to use this in COPY to a file.

The function:

DO $$
DECLARE
    vrec record;
    vquery text = '';
    vselect text;
BEGIN
    FOR vrec IN
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
            AND table_name = 'settings'
            AND table_schema NOT IN('information_schema', 'pg_catalog', 'public')
    LOOP
        vselect = format(
            'SELECT ''%s'', prefix, id, value FROM %s.%s WHERE id = ''VERSION''',
            vrec.table_schema, quote_ident(vrec.table_schema), vrec.table_name);
        IF vquery = '' THEN vquery = vselect;
        ELSE vquery = format('%s UNION %s', vquery, vselect);
        END IF;
    END LOOP;
    EXECUTE format('COPY (%s) TO ''/tmp/out-%s'' CSV', vquery, current_database());
END $$;


How to execute SQL Server query in SSMS using PowerShell

You probably shouldn't do this, but it's possible to simulate user activity by using SendKeys.

  Sends keystrokes to the active application.

This does what you want:

add-type -AssemblyName System.Windows.Forms

ssms.exe 'c:\temp\test.sql' -E -S localhost

# wait to make sure the application is open
start-sleep -Milliseconds 10000

# press F5
[System.Windows.Forms.SendKeys]::SendWait("{F5}")

# wait for the query to finish
start-sleep -Milliseconds 10000

# press ALT-F4
[System.Windows.Forms.SendKeys]::SendWait("%{F4}")


The way I am using might not the best, however, just to reassure you that this is possible and it has been working fine for me:

if you really like the debug least for now I am using both versions of ssms, the 17.9 and the 18 side by side as you can see on the picture below:

I use sql server from 2008 r2 up to sql server 2017.


Next time, please include the EXPLAIN output rather than making us dig for it in your scripts.  There's no guarantee my system is using the same plan as yours (although with your test data it is likely).

The rule system here is working properly.  First, the I want to include my own diagnostic queries (note I did not run EXPLAIN ANALYSE since I was just interested in what query plan was generated):

rulestest=# explain DELETE FROM user_hits WHERE day = '2013-03-16';
                                              QUERY PLAN

--------------------------------------------------------------------------------
----------------------
 Delete on application_hits  (cost=0.00..3953181.85 rows=316094576 width=24)
   ->  Nested Loop  (cost=0.00..3953181.85 rows=316094576 width=24)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..128.53 rows=6352 width=22)
               ->  Nested Loop  (cost=0.00..96.78 rows=6352 width=22)
                     ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 wi
dth=10)
                           Filter: (day = '2013-03-16'::date)
                     ->  Materialize  (cost=0.00..2.49 rows=16 width=16)
                           ->  Nested Loop  (cost=0.00..2.41 rows=16 width=16)
                                 ->  Seq Scan on application_hits  (cost=0.00..1
.10 rows=4 width=10)
                                       Filter: (day = '2013-03-16'::date)
                                 ->  Materialize  (cost=0.00..1.12 rows=4 width=
10)
                                       ->  Seq Scan on client_hits  (cost=0.00..
1.10 rows=4 width=10)
                                             Filter: (day = '2013-03-16'::date)

 Delete on client_hits  (cost=0.00..989722.41 rows=79023644 width=18)
   ->  Nested Loop  (cost=0.00..989722.41 rows=79023644 width=18)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..43.83 rows=1588 width=16)
               ->  Nested Loop  (cost=0.00..35.89 rows=1588 width=16)
                     ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 wi
dth=10)
                           Filter: (day = '2013-03-16'::date)
                     ->  Materialize  (cost=0.00..1.12 rows=4 width=10)
                           ->  Seq Scan on client_hits  (cost=0.00..1.10 rows=4
width=10)
                                 Filter: (day = '2013-03-16'::date)

 Delete on project_hits  (cost=0.00..248851.80 rows=19755911 width=12)
   ->  Nested Loop  (cost=0.00..248851.80 rows=19755911 width=12)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..16.91 rows=397 width=10)
               ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 width=10
)
                     Filter: (day = '2013-03-16'::date)

 Delete on user_hits  (cost=0.00..1887.00 rows=49763 width=6)
   ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=6)
         Filter: (day = '2013-03-16'::date)
(39 rows)

rulestest=# select distinct day from application_hits;
    day
------------
 2013-03-15
 2013-03-16
(2 rows)

rulestest=# select count(*), day from application_hits group by day;
 count |    day
-------+------------
     4 | 2013-03-15
     4 | 2013-03-16
(2 rows)

rulestest=# select count(*), day from client_hits group by day;
 count |    day
-------+------------
     4 | 2013-03-15
     4 | 2013-03-16
(2 rows)

rulestest=# select count(*), day from project_hits group by day;
 count |    day
-------+------------
   397 | 2013-03-15
   397 | 2013-03-16
(2 rows)

If your data is anything like your existing data, neither rules nor triggers will work very well.  Better will be a stored procedure which you pass a value and it deletes everything you want.

First let's note that indexes here will get you nowhere because in all cases you are pulling half of the tables (I did add indexes on day on all tables to help the planner but this made no real difference).

You need to start with what you are doing with RULEs.  RULEs basically rewrite queries and they do so using ways that are as robust as possible.  Your code also doesn't match your example though it matches your question better.  You have rules on tables which cascade to rules on other tables which cascade to rules on other tables

Therefore when you delete from user_hits where [criteria], the rules transform this into a set of queries:

DELETE FROM application_hits
 WHERE day IN (SELECT day FROM client_hits
               WHERE day IN (SELECT day FROM user_hits WHERE [condition]));
DELETE FROM client_hits
  WHERE day IN (SELECT day FROM user_hits WHERE [condition]);
DELETE FROM user_hits WHERE [condition];

Now, you might think we could skip the scan on client_hits in the first, but that isn't what happens here.  The problem is that you could have days in user_hits and application_hits that are not in client_hits so you really have to scan all tables.

Now here there is no magic bullet.  A trigger isn't going to work much better because, while it gets to avoid scanning every table, it gets fired every row that gets deleted so you basically end up with the same nested loop sequential scans that are currently killing performance.  It will work a bit better because it will delete rows along the way rather than rewriting the query along the way, but it isn't going to perform very well.

A much better solution is to just define a stored procedure and have the application call that.  Something like:

CREATE OR REPLACE FUNCTION delete_stats_at_date(in_day date) RETURNS BOOL
LANGUAGE SQL AS
$$
DELETE FROM application_hits WHERE day = $1;
DELETE FROM project_hits WHERE day = $1;
DELETE FROM client_hits WHERE day  = $1;
DELETE FROM user_hits WHERE day = $1;
SELECT TRUE;
$$;

On the test data this runs in 280 ms on my laptop.

One of the hard things regarding RULEs is remembering what they are and noting that the computer cannot, in fact, read your mind.  This is why I would not consider them a beginner's tool.


It depends on your table structures, data layout and usage pattern. However I wouldn't consider 12 gb or actually anything that can fit into the RAM of a reasonable priced commodity server big data (~100-400 Gb). I work with MySQL servers happily running with terabytes of data. If your tables are well designed, indexed and your queries are well written you won't see issues.

After that it's usually much easier to split your dataset by common usage pattern and logical dependencies. Move tables to a different database. For example split your log tables to a new server. You can repeat that as long as want until you reach the write limit of a single server and your cannot split your database anymore (all tables are strongly related). Then you can think about sharding.


Keep getting "Connected to an idle instance" in 18c when trying to connect to database

If the environemnt variables ORACLE_SID, ORACLE_HOME and ORACLE_BASE are set, then you should be able to connect to an IDLE INSTANCE with SQLPlus.

C:\> sqlplus sys@<SID> as sysdba

When connected you then create a editable PFILE copy of the instances current SPFILE (run time configuration). Before that be sure to copy the current editable PFILE to some safe locaiton. If you don't have an SPFILE then skip to step 5.

  Notice:
  Please replace the <SID> placeholder I used in my commands with the SID of your actual Oracle instance, and of course the paths to the local SPFILE and PFILE should reflect your actual paths of your installation.

Configuring Oracle Memory Settings

Save the initial PFILE that is created after a setup

C:\> COPY C:\Oracle\admin\<SID>\pfile\init<SID>.ORA C:\Oracle\admin\<SID>\pfile\init<SID>_backup.ORA

Save any current PFILE that may be laying around

C:\> COPY C:\Oracle\admin\<SID>\pfile\pfile_<SID>.ORA C:\Oracle\admin\<SID>\pfile\pfile_<SID>_backup.ORA

Log into idle instance

C:\> sqlplus sys@<SID> as sysdba

Create a PFILE (modifiable configuration file) from current SPFILE (run-time configuration file)

SQL> CREATE PFILE = 'C:\Oracle\admin\<SID>\pfile\pfile_<SID>.ORA' from SPFILE = 'C:\Oracle\admin\<SID>\pfile\spfile_<SID>.ORA';

Shutdown the idle instance

SQL> SHUTDOWN IMMEDIATE

In an editor open your current PFILE C:\Oracle\admin\\pfile\pfile_.ORA or the INIT_.ORA file and locate these parameters:

*.memory_max_target=0
*.memory_target=0

These parameters are set when using Automatic Memory Management. If these are both set to 0 (like above) then you are using Automatic Shared Memory Management. You will have to set the following parameters instead:

*.sga_max_size=0
*.sga_target=0

Either of these value pairs should be set. If not, then modify the memory_max_target and memory_target parameters to something like 4G. Save the configuration file and...
Start the Oracle instance using the freshly created PFILE.

C:\> sqlplus sys@<SID> as sysdba
SQL> STARTUP PFILE='C:\Oracle\admin\<SID>\pfile\pfile_<SID>.ORA';

When instance is running, create the run-time configuration file.

SQL> CREATE SPFILE='C:\Oracle\admin\<SID>\pfile\spfile_<SID>.ORA' FROM PFILE='C:\Oracle\admin\<SID>\pfile\pfile_<SID>.ORA';

When the SPFILE has been created shutdown the instance.

SQL> SHUTDOWN IMMEDIATE

Restart the instance which should use the freshly create SPFILE (run-time configuration).

C:\> sqlplus sys@<SID> as sysdba
SQL> STARTUP

And you should be on your way.


CONVERT(CONVERT(CONVERT(`Question` USING latin1) USING binary) USING utf8)


What's the meaning of the Collation Code (blank, 90, 100, 110) in SQL Server 2012

The one without the code is a version 90 (SQL Server 2005) collation, used by previous versions of Windows / SQL Server. The collation including 100 is newer (SQL Server 2008).

There's a bit of an overview of the differences in the SQL Server 2008 documentation, here, however it doesn't go into great detail.

If you're developing a new application, you may find that sorting is slightly more logical (particularly for unusual characters) with the 100 version. However, if you've got any other SQL Servers running with French_CI_AS, it might be better to keep it consistent with them.


SQL Server 2012 Developer edition questions

1: yes

2: yes. Limits generally are documented in the documentation. In this particular case you shall look to http://technet.microsoft.com/en-us/library/ms143432.aspx for all capacity specifcations for all editions of sql server.

3: Why you have to? Normally developers have MSDN (Microsoft Developers NEtwok) and get the licenses there.

Then - ever heard of google? Finsind a shop is trivial.

For example google and "sql server developer edition price" led me to
http://www.amazon.com/SQL-Server-Developer-Edition-2012/dp/B007RFXQAM

  so the only option seems to be, to rip the CD/DVD and then upload it to the
  development server which would be a tedious process.

No need to "rip" - a file copy is good enough. SQL Server does nothing fancy with the install media. But generally I keep ISO files around for all the stuff I need to develop - easier than looking for the DVD anyway every time I need to install it.

Generally if you want to do anything professional - or even semi professional - these days better stop assuming copying an ISO file up to the cloud is tendious. You will move a lot more around than you ever think of. This is 2013.

If you would have MSDN you could just download the ISO from the MS official repositories.


MySQL / InnoDB: Does daily optimization improve performance?

MyISAM
Doing OPTIMIZE TABLE mydb.mytable; performs two basic operations
ALTER TABLE mydb.mytable ENGINE=MyISAM;
ANALYZE TABLE mydb.mytable;

This definitely cleans up table fragmentation and computes fresh statistics for indexes
InnoDB
Doing OPTIMIZE TABLE mydb.mytable; performs two basic operations
ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;

While this eliminates fragmentation, ANAYLZE TABLE is complete useless for InnoDB. I wrote about this a long time ago:

Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?
Oct 16, 2011 : Suddenly have to rebuild indexes to prevent site from going down

If your data growth rate is very small (or in other words, your dataset stays basically the same size for months at a time), then defragmenting tables would just be overkill. You should probably focus on tuning InnoDB's other aspects (See my Aug 04, 2011 post: Optimizing InnoDB default settings)
I also have posts in the Drupal StackExchange on opimizing MySQL

Jun 07, 2011 : How do I convert a database from MyISAM to InnoDB?
Apr 15, 2011 : What would be the optimal MySQL configuration for a Drupal 7 site?


Why is my query returning an unexpected result?

My best guess is that what you are actually trying to query is:

SELECT S.sname
FROM Suppliers S
WHERE S.sid IN (SELECT C.sid
FROM Catalog C, Catalog C1
WHERE C1.pid <> C.pid  AND C1.cost = C.cost);

I changed the subselect to return C.sid, and removed the parts table altogether, since in your original body text you stated no conditions on the table.

Is this what you are looking for?


Well.. MySQL doesn't work well as a search engine. I went with a search engine and selects with multiple filters are instant.


I believe, log-shipping would not work in your case (AG1 ot AG2), due to following reasons.

Database cannot be restored while it's already part of AG/Mirroring
Basically, Log-shipping does backups on primary replica, and restores on secondary replica with defined interval time

Log-Shipping would be good choice when Secondary replica in Log shipping topology is standalone and same version as primary SQL Instance. i.e. Your Data-center-1 AG act as primary replica and Standalone Instance at Data-center-2 act as secondary replica.

While you implementing Log-shipping for a Database that is already part of AG, you need to ensure that the Backup preference cannot be set to secondary within AG as the log-shipping cannot work with secondary backup copies from AG. In your case AG of Data-center-1.

Since you already have Availability Groups in-place with SQL 2016 in two sub-nets, distributed AG might be correct solution.

You may follow these reference in either case:
Distributed Availability Groups
Log-shipping from AG Database


SQL Server 2019 setting Force Encryption with a script

Yes use dbatools in particular the command Enable-DbaForceNetworkEncryption


Implicit conversion between decimals with different precisions

The type of the result is determined by the rules set out in Precision, scale, and Length (Transact-SQL):

  The following table defines how the precision and scale of the result are calculated when the result of an operation is of type decimal. The result is decimal when either:

  Both expressions are decimal.
  One expression is decimal and the other is a data type with a lower precedence than decimal.

  The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression. The function max(a,b) means the following: take the greater value of "a" or "b". Similarly, min(a,b) indicates to take the smaller value of "a" or "b".

  * The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it's reduced to 38, and the corresponding scale is reduced to try to prevent truncating the integral part of a result. In some cases such as multiplication or division, scale factor won't be reduced, to maintain decimal precision, although the overflow error can be raised.

  In addition and subtraction operations, we need max(p1 - s1, p2 - s2) places to store integral part of the decimal number. If there isn't enough space to store them that is, max(p1 - s1, p2 - s2) < min(38, precision) - scale, the scale is reduced to provide enough space for integral part. Resulting scale is MIN(precision, 38) - max(p1 - s1, p2 - s2), so the fractional part might be rounded to fit into the resulting scale.

A convenient quick way to see the resulting type is to use SQL_VARIANT_PROPERTY:

DECLARE @V1 decimal (9, 6) = 123.456789;
DECLARE @V2 decimal (7, 2) = 12345.67;

SELECT
    [BaseType] = SQL_VARIANT_PROPERTY(@V1 + @V2, 'BaseType'),
    [Precision] = SQL_VARIANT_PROPERTY(@V1 + @V2, 'Precision'),
    [Scale] = SQL_VARIANT_PROPERTY(@V1 + @V2, 'Scale');

Output:

╔══════════╦═══════════╦═══════╗
║ BaseType ║ Precision ║ Scale ║
╠══════════╬═══════════╬═══════╣
║ decimal  ║        12 ║     6 ║
╚══════════╩═══════════╩═══════╝

db<>fiddle demo


Designing a platform: one database or multiple databases?

In my opinion, the key differentiator of true SOA systems (over the pseudo SOA, ntier/distributed systems that are becoming ubiquitous) is that there should be zero interaction between discrete services. Where this is achieved, any application you compose from these services can and should be built to tolerate the failure of any consistuent part. A failure reduces functionality but service is maintained.

In this scenario it's logical, or required, to separate the underlying database for each service. If however you have services which are interdependent, there is little (perhaps nothing) to be gained from a split.

I'd recommend reading sites such as HighScalability.com which dig into the architectures adopted by the never-fail type websites. One of my favourites of late was the story of the Netflix Chaos Monkey which was mentioned on Coding Horror.

Addressing a couple of the points in your question:

  In the event of a disaster, restoring the platform to a consistent
  state is easier.

This is true but you should perhaps be thinking about how to better decouple these services so this stops being an issue. Alternatively, there are methods to ensure synchronisation across multiple databases, transaction marks in SQL Server for example.

  For data that is referenced by multiple services, data cached by one
  service is likely to be used soon after by another service.

Distributed cache solutions (memcached et al) could help here but you'd be violating the service independence principles. This would be comparable to having two services communicating with each other directly, or worse having a service access anothers data store, bypassing the service interface altogether. Inevitably data will be related and will be handed between services by the calling platform, the tricky decisions tend to be around which service will own which pieces of data. StackOverflow or Programmers sites might be better placed to help with the more general SOA issues.

  Assuming each database is on separate hardware, scaling up yields more
  performance benefits.

Certainly it can be cheaper to scale out across multiple lower spec machines than to scale up a single machine. Although, the lower hardware costs may be dwarfed in the total cost of ownership when the soft costs of additional development effort and operational complexity are factored in.

If this isn't SOA and you just have a case where the component services of this platform are being built by different teams/suppliers for logistical reasons, stick with a single database and completely ignore everything above! :)


For a MySQL query, how do you determine physical and logical I/O?

I recommend using Percona Server, for the slow query log enhancements.  I answered a similar thread to this on Stack Overflow:

https://stackoverflow.com/questions/3393206/what-is-the-equivalent-query-in-mysql/3443944#3443944

Take a look at this in particular (from http://www.percona.com/docs/wiki/percona-server:features:slow_extended?redirect=1#changes_to_the_log_format):

# User@Host: mailboxer[mailboxer] @  [192.168.10.165]
# Thread_id: 11167745  Schema: board
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: Yes  Disk_tmp_table: No
# Filesort: Yes  Disk_filesort: No  Merge_passes: 0
# Query_time: 0.000659  Lock_time: 0.000070  Rows_sent: 0  Rows_examined: 30  Rows_affected: 0  Rows_read: 30
#   InnoDB_IO_r_ops: 1  InnoDB_IO_r_bytes: 16384  InnoDB_IO_r_wait: 0.028487
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 5
select count(distinct author_id) from art87.article87 force index (forum_id) where forum_id = 240215 and thread_id = '710575'


After Upgrade to 2016, SQL Server Agent can't connect to Instance

Problem resolved by adding an alias to the instance in the SQL Server Agent Configuration.

The alias was defined using the fully qualified domain name of the server. Example:servername.domain.biz\instance_name otherwise the agent will still error out and shutdown.

Also, the Agent was disabled in sp_configure

sp_configure 'Agent XPs', 1
go

reconfigure with override
go

So reenabled it.


Compare data between 2 rows of same table

Based on your requirement if both columns are in the same table we could use ISNULL to achieve this output?

e.g.

select ISNULL(date2, date1) as CalcDate
from <table>

Results:

CalcDate
2017-03-06 17:02:00.000
2017-02-17 11:20:00.000
2017-03-13 17:07:00.000
2017-03-17 12:05:00.000
2017-01-24 11:45:00.000


Ok, so my answer here is conditioned on the following assumptions:

This categorization needs to be relatively stable and
It may be re-used in various ways, and
It needs to perform well.

The obvious, simple answer is to use table methods in PostgreSQL.  What you do is create a SQL language function which does not hit the table and returns the value you want.  Make it immutable so you can index the output if you want to query against it, etc.  Note the specific rules aren't really clear from your post but this should get you started.

CREATE OR REPLACE FUNCTION subcategory(task) -- task is table name
RETURNS text LANGUAGE sql IMMUTABLE AS
$$
    SELECT CASE WHEN $1.type = 3 THEN
                CASE WHEN $I.reference LIKE 'IX#%'
                     THEN 'Company IX Chargeable'
                     ELSE 'Other Chargeable'
                END
                WHEN $1.reference LIKE 'IX#%' THEN
                CASE WHEN $1.type = 5 THEN 'Company IX Rebool'
                     ELSE 'Company IX Other'
                END
                WHEN $1.reference LIKE 'THR#%' AND $1.type IN (1, 2) THEN
                CASE WHEN $1.type = 2 THEN 'THR Return to Base'
                     WHEN $1.type = 1 THEN 'THR On Site'
                END
                WHEN $1.type = 2 THEN 'Return to Base'
                ELSE 'Other Nonchargeable'
           END;
$$;

You can then query this using:

SELECT t.subcategory FROM task t;

Note that the table name is non-optional here.  The parser converts this to:

SELECT subcategory(t) FROM task t;

You can use this however in any part of the select statement including the where clause.  If it proves slow, you can add cost estimates (but I think this should be fast), and you can even index the output using PostgreSQL's functional indexes.

On the performance side, LIKE and substring() don't seem to be significantly different:

postgres=# select count(*) from generate_series(1, 10000000);
  count
----------
 10000000
(1 row)

Time: 9007.618 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE s::text like '1%';
  count
---------
 1111112
(1 row)

Time: 13653.000 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE substring(s::text from 1 for 1) = '1';
  count
---------
 1111112
(1 row)

Time: 16681.860 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE s::text like '1%';
  count
---------
 1111112
(1 row)

Time: 17163.470 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE substring(s::text from 1 for 1) = '1';
  count
---------
 1111112
(1 row)

Time: 17052.004 ms

So sometimes one is faster than the other, but they don't seem out of line with eachother.


Here is a solution using dynamic SQL

--Demo setup
DROP TABLE IF EXISTS Table1;

CREATE TABLE Table1
    ([Name] varchar(1), [Feature] varchar(2))
;

INSERT INTO Table1
    ([Name], [Feature])
VALUES
    ('A', 'F1'),
    ('A', 'F1'),
    ('A', 'F1'),
    ('A', 'F2'),
    ('A', 'F3'),
    ('B', 'F1'),
    ('B', 'F1'),
    ('C', 'F3')
;

--Solution using dynamic SQL
Declare @sql nvarchar(4000)
Declare @col varchar(max)
Declare @colCoalesceNull varchar(max)
SELECT @col =
        Coalesce(@col + ', ', '') + QUOTENAME([Feature])
    ,@colCoalesceNull =
        Coalesce(@colCoalesceNull + ', ', '') + 'coalesce(' + QUOTENAME([Feature]) + ',0) as ' + QUOTENAME([Feature])
FROM (
    SELECT DISTINCT
        [Feature] AS [Feature]
    FROM Table1
    ) AS Table1;

PRINT @col
PRINT @colcoalescenull

SET @sql = N'
with _cte as
(
SELECT Name,Feature,COUNT(Feature) FeatureCount FROM Table1 GROUP BY Feature,Name
)
SELECT Name, ' + @colCoalesceNull + ' FROM _cte
    PIVOT(SUM(FeatureCount)
    FOR Feature IN (' + @col + ')) AS PivotData';

print @sql
EXEC sp_executesql @sql;

| Name | F1 | F2 | F3 |
|------|----|----|----|
| A    | 3  | 1  | 1  |
| B    | 2  | 0  | 0  |
| C    | 0  | 0  | 1  |


Oracle 11g Enterprise Manager Login Fail Internal Error has Occured

I managed to fix the issue, turns out it was a time zone issue. The DB time zone and computer time zone were different. Thus the DB Console would not start and was giving the error "EM daemon is not running".
I fixed it by following instructions here.

Also note before I did that I completely uninstalled Oracle and reinstalled it. There were some issues on that install that were causing SQL not to run. But the above is what fixed the Internal Error I was getting as I got it again even after the fresh install.


How can I export just the structure of a MySQL database?

Since you specifically asked about phpMyAdmin and the previous answers, while correct, are both about doing this on the command line:

Navigate to the database you wish to export. Across the top row of tabs click "Export"

Next, click the Custom radio button

Halfway down the "Format-specific options" area, select "structure" (the default is "structure and data")

Check the other options and press Go to download your .sql file with no data, only the table structure.


A couple of things to check into. These two you could capture with a server-side trace or extended events (though I haven't used the latter for this specific task):

explicit calls to DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE
certain explicit calls to RECONFIGURE (or RECONFIGURE WITH OVERRIDE)

If you see these calls happening often, you'll want to figure out where they're coming from and why, because they absolutely flush the procedure cache.

Also check the error log for events containing the text "cachestore flush":

EXEC xp_readerrorlog 0, 1, N'cachestore flush';

If you see these events make sure the database in question does not have the option AUTO_CLOSE on. You may want to make sure this isn't the case anyway, and it is often turned on with Web Edition since this edition is typically used to hold a lot of infrequently-used databases at a web host.


Your outline is exactly what you would want to do. If this was a real product the only addition I might add is that for the Timesheet table you would probably want your Clustered Index on the TimeStamp field as it sounds like one of the primary filters and you'll be selecting ranges from the table. If you are always searching by TimeStamp, Employee, and Employer key you can make the Clustered Index on all three.

A Clustered Index is the table itself on the disk, and it will be stored using the selected index as the logical sorting for the data. In most SQL servers when you create a Primary Key it automatically creates the Clustered Index on those columns, however it is not a requirement that they are the same columns (or even both need to be present) and can be on different columns. The primary reason to have the Clustered Index on other columns is to improve query performance on commonly searched data that is grouped by value or accessed via a range.

In general Indexing requires careful analysis of your queries to insure they benefit from all your indexing choices.


Chronological list of statements from Oracle trace

While TKProf is generally referred to as something that makes a trace file 'human readable' it is primarily a Profiling tool that aggregates the SQL statements to find the most impactful.

The raw trace file can be used. However if you are primarily interested in what the client sends to the server, you should look at logging options in JDBC (
https://www.oracle.com/technetwork/topics/11-2.pdf ) or in the SQL Net layer (see Oracle Client trace not generating )


This is from the sqlt instruction file itself:

"For statements that modify data, i.e. INSERT/UPDATE/DELETE, a savepoint is created prior to statement execution and the transaction is rolled back to the savepoint at the conclusion of the session. For further information regarding SAVEPOINT please consult the Oracle Concepts reference manual".

This means if you are using XECUTE method, the data it inserted during execution will be rolled back at the end of execution.
If you are running this in a production system with thousands of transactions per second, you might want to consider the additional load this insert/rollback will put on the system.

Hope this helps :)


Is not nice, is not elegant, but still it works.
Hope this helps to any lost DB Adventurer in a desperate crusade for a sessions historical report.
COL METRIC_NAME FOR A40
COL BEGIN_TIME FOR A20
COL END_TIME FOR A20
COL METRIC_UNIT FOR A20
set lin 330 pages 5000

SELECT
AVG_SES_COUNT.SNAP_ID,      AVG_SES_COUNT.DBID,     AVG_SES_COUNT.INSTANCE_NUMBER,
AVG_SES_COUNT.BEGIN_TIME,   AVG_SES_COUNT.END_TIME,
--AVG_SES_COUNT.METRIC_NAME,
AVG_SES_COUNT.AVERAGE AVG_TOTAL_SESSIONS,       AVG_SES_COUNT.MAXVAL MAX_TOTAL_SESSIONS,
-----
AVG_ACT_SES.AVERAGE                AVG_ACTIVE_SESSIONS,     AVG_ACT_SES.MAXVAL            MAX_ACTIVE_SESSIONS,
-----
AVG_SERIAL_SESSIONS.AVERAGE        AVG_SERIAL_SESSIONS,     AVG_SERIAL_SESSIONS.MAXVAL    MAX_SERIAL_SESSIONS,
-----
AVG_PARALLEL_SESSIONS.AVERAGE      AVG_PARALLEL_SESSIONS,   AVG_PARALLEL_SESSIONS.MAXVAL  MAX_PARALLEL_SESSIONS,
-----
AVG_PQ_SESSIONS.AVERAGE      AVG_PQ_SESSIONS,   AVG_PQ_SESSIONS.MAXVAL  MAX_PQ_SESSIONS,
-----
AVG_PQ_SLV_SESSIONS.AVERAGE      AVG_PQ_SLAVE_SESSIONS,     AVG_PQ_SLV_SESSIONS.MAXVAL  MAX_PQ_SLAVE_SESSIONS
FROM
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT
from dba_hist_sysmetric_summary
where  METRIC_ID=2143
order by INSTANCE_NUMBER,SNAP_ID ) AVG_SES_COUNT,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT
from dba_hist_sysmetric_summary
where  METRIC_ID=2147
order by INSTANCE_NUMBER,SNAP_ID ) AVG_ACT_SES,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT
from dba_hist_sysmetric_summary
where  METRIC_ID=2148
order by INSTANCE_NUMBER,SNAP_ID ) AVG_SERIAL_SESSIONS,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT
from dba_hist_sysmetric_summary
where  METRIC_ID=2148
order by INSTANCE_NUMBER,SNAP_ID )  AVG_PARALLEL_SESSIONS,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT
from dba_hist_sysmetric_summary
where  METRIC_ID=2137
order by INSTANCE_NUMBER,SNAP_ID )  AVG_PQ_SESSIONS,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT
from dba_hist_sysmetric_summary
where  METRIC_ID=2138
order by INSTANCE_NUMBER,SNAP_ID )  AVG_PQ_SLV_SESSIONS
WHERE
    AVG_SES_COUNT.SNAP_ID           = AVG_ACT_SES.SNAP_ID       (+)
AND AVG_SES_COUNT.DBID              = AVG_ACT_SES.DBID          (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_ACT_SES.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID           = AVG_SERIAL_SESSIONS.SNAP_ID       (+)
AND AVG_SES_COUNT.DBID              = AVG_SERIAL_SESSIONS.DBID          (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_SERIAL_SESSIONS.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID           = AVG_PARALLEL_SESSIONS.SNAP_ID         (+)
AND AVG_SES_COUNT.DBID              = AVG_PARALLEL_SESSIONS.DBID            (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_PARALLEL_SESSIONS.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID           = AVG_PQ_SESSIONS.SNAP_ID       (+)
AND AVG_SES_COUNT.DBID              = AVG_PQ_SESSIONS.DBID          (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_PQ_SESSIONS.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID           = AVG_PQ_SLV_SESSIONS.SNAP_ID       (+)
AND AVG_SES_COUNT.DBID              = AVG_PQ_SLV_SESSIONS.DBID          (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_PQ_SLV_SESSIONS.INSTANCE_NUMBER (+)
order by INSTANCE_NUMBER,SNAP_ID;


Unless you have a special case, an INSERT with 1.2 million rows should be completed in seconds or minutes at most.

Yes, direct-path insert may significantly reduce execution time.

The above hint in INSERT has no effect without the necessary prerequisites.

Enable Parallel DML Mode

  A DML statement can be parallelized only if you have explicitly
  enabled parallel DML in the session or in the SQL statement. To enable
  this mode in a session, run the following SQL statement:

  ALTER SESSION ENABLE PARALLEL DML;

  To enable parallel DML mode in a
  specific SQL statement, include the ENABLE_PARALLEL_DML SQL hint. For
  example:

  INSERT /*+ ENABLE_PARALLEL_DML */ …

  This mode is required because parallel DML and serial DML have different locking, transaction, and
  disk space requirements and parallel DML is disabled for a session by
  default.

  When parallel DML is disabled, no DML is executed in parallel even if
  the PARALLEL hint is used.


Using On-Premise SQL Data Warehouse Data for OLTP applications

Can you have your cake and eat it, too?

nope.

But there are some things you can do. I've worked with this EHR software called Nextgen. It has an OLTP database that never deletes/archives any data, so it's also the data warehouse.

Short story: it sucks.

less-short story: You can keep things kinda-not-slow if you cluster every table by some kind of date, so the most recent data is on top.

You also have to pay attention to DUI's, making sure they don't lock tables for too long at a time. I would sometimes resort to cursors, which made the overall duration of the DUI longer, but reduced the time of each individual lock.

I also avoided triggers, using jobs running every 5-15 minutes instead, whenever possible.

Here's the script I used to get the whole database at once:

    /*this script will find all the heaps in the database that have the columns you specify, then index them on those columns and write a record to jm_clusterize
    It was written for sql server enterprise edition, taking advantage of online indexing, so if you have trouble, get rid of 'with (online = on)
    You should run this script after hours. It will *try* to do the index online, but if it can't, it will do the index offline, which will lock the table for a
    few seconds/minutes
    */

    declare @table varchar(255),
    @sqlcreate nvarchar(max),
    @sqlDrop nvarchar(max),
    @column1 varchar(255),
    @column2 varchar(255),
    @column3 varchar(255)

    /*specify the coulumns you want to cluster on here. You always want to cluster on the create timestamp first, and nextgen is pretty good
    at having a create_timestamp on every table, but you will want to cluster some tables on seq_no, or uniq_id, then patient demographics tables will need to
    use person_id first. I'm on the fence on clustering on 3 columns - it may be faster, it may be slower.

    */
    set @column1 = 'create_timestamp'
    set @column2 = 'enc_id'
    --set @column3 = 'seq_no'

    --drop table jm_clusterize
    if not exists (select name from sys.objects where name = 'jm_clusterize')
    begin
    create table jm_clusterize (table_name varchar(255), column1 varchar(255), column2 varchar(255), column3 varchar(255), create_statement nvarchar(max),
    drop_statement nvarchar(max), create_timestamp datetime, modify_timestamp datetime)
    end

    declare c cursor for

    --This is the query that will show you all the heaps that could be clustered on the columns you pick
    SELECT TBL.name AS TableName
    FROM sys.tables AS TBL
         INNER JOIN sys.schemas AS SCH
             ON TBL.schema_id = SCH.schema_id
         INNER JOIN sys.indexes AS IDX
             ON TBL.object_id = IDX.object_id
                AND IDX.type = 0 -- = Heap
                inner join sys.columns sc1 on tbl.object_id = sc1.object_id
                inner join sys.columns sc2 on tbl.object_id = sc2.object_id
                --inner join sys.columns sc3 on tbl.object_id = sc3.object_id
                INNER JOIN
        sys.partitions p ON idx.object_id = p.OBJECT_ID AND idx.index_id = p.index_id
    INNER JOIN
        sys.allocation_units a ON p.partition_id = a.container_id
        where sc1.name = @column1
        and  sc2.name = @column2
        --and sc3.name = @column3
        group by TBL.name
    ORDER BY SUM(a.total_pages) desc, TableName

    open c

    fetch next from c into @table

    while @@fetch_status = 0
    begin

    set @sqlcreate = 'begin try create clustered index inx_jm_clstr on ' + @table + ' (' + @column1 + ','
    + @column2
    --+ ',' + @column3
    + ') with (online = on) end try
    begin catch create clustered index inx_jm_clstr on ' + @table + ' (' + @column1 + ','
    + @column2
    --+ ','+ @column3
    + ') end catch'

    set @sqlDrop = 'drop index inx_jm_clstr on ' + @table

    insert into jm_clusterize (table_name, column1, column2, column3, create_statement, drop_statement, create_timestamp, modify_timestamp)

    select @table, @column1, @column2, @column3, @sqlcreate, @sqlDrop, current_timestamp, current_timestamp

    --comment out the line below and it won't actually create the index
    exec sp_executesql @sqlcreate

    fetch next from c into @table
    end

    close c

    deallocate c

    select * from jm_clusterize
    where column1 = @column1
    and column2 = @column2
    and column3 = @column3


Does READ COMMITED commited blocks any rows?

Locks is a way to implement isolation levels, exactly what gets locked and not is implementation specific. So it may be better to think in terms of what the isolation level guarantees. READ COMMIT guarantees that there are no dirty reads. I.e. we won't see uncommitted data that is later on rolled back. However, both Nonrepeatable reads and Phantom reads are possible. Non-repeatable means that if we do:

BEGIN T
select * from tbl -- s1
select * from tbl -- s2
END T

s1 and s2 is not guaranteed to return the same result, i.e. it is valid for another transaction to delete or update from tbl so that s1 and s2 differs. Phantom read typically means that another transaction inserted a row between s1 and s2.

Depending on the implementation, some rows may be looked during REPEATABLE READ, but probably not all.

Db2 as an example (not considering CURRENTLY COMMITED here), locks a row that matches the predicate with a Shared-lock[1] (in the example there is none so all row matches) until it finds a new row that matches the predicate. This may be different than how it is implemented in other DBMS, so you should check out what mechanism the DBMS you have in mind is using.

[1] Shared means that other readers can read the row, but writers must wait


This works although I am not sure how to handle the date (sample is not a date):

Select DATENAME(month, [Date]) as 'date'
    , SUM(Case When Operator = 'Operator 1' then Invoice1 end) as 'Sum of Invoice 1 for Operator 1'
    , SUM(Case When Operator = 'Operator 1' then Invoice2 end) as 'Sum of Invoice 2 for Operator 1'
    , SUM(Case When Operator = 'Operator 2' then Invoice1 end) as 'Sum of Invoice 1 for Operator 2'
    , SUM(Case When Operator = 'Operator 2' then Invoice2 end) as 'Sum of Invoice 2 for Operator 2'
    , COUNT(distinct Client) as 'Count of Distinct Clients'
    , COUNT(distinct Entity) as 'Count of Distinct Entities'
From @data
Group By [date];

Output:

date        | Sum of Invoice 1 for Operator 1   | Sum of Invoice 2 for Operator 1   | Sum of Invoice 1 for Operator 2   | Sum of Invoice 2 for Operator 2   | Count of Distinct Clients | Count of Distinct Entities
January     | 40                                | 50                                | 130                               | 140                               | 2                         | 4
February    | 60                                | 70                                | 160                               | 170                               | 2                         | 4

Your data (replaced by real dates):

Declare @data TABLE ([Date] datetime, Invoice1 int, Invoice2 int, Operator varchar(10), Client varchar(8), Entity varchar(10));
INSERT INTO @data(Date, Invoice1, Invoice2, Operator, Client, Entity)
VALUES
    ('20150101', 10, 15, 'Operator 1', 'Client 1', 'Entity A'),
    ('20150201', 20, 25, 'Operator 1', 'Client 1', 'Entity B'),
    ('20150101', 30, 35, 'Operator 1', 'Client 2', 'Entity C'),
    ('20150201', 40, 45, 'Operator 1', 'Client 2', 'Entity D'),
    ('20150101', 50, 55, 'Operator 2', 'Client 1', 'Entity E'),
    ('20150201', 70, 75, 'Operator 2', 'Client 2', 'Entity F'),
    ('20150101', 80, 85, 'Operator 2', 'Client 1', 'Entity G'),
    ('20150201', 90, 95, 'Operator 2', 'Client 2', 'Entity H')
;


SERIALIZABLE transaction information leak via serial

For various performance and concurrency reasons, sequences are non-transactional, but atomic. In other words, they always give a distinct value to each session, but they don't reuse values, nor are they "gapless" where they return a continuous sequence of numbers.

The second transaction in your example was handed distinct sequence values for each of the rows inserted, and since that is atomic, but non-transactional, the first transaction sees the sequence's nextval increased even though the transaction isolation level is SERIALIZEABLE.

The behavior is noted at the bottom in the Sequence Manipulation Functions documentation.

Neil Conway also explains this quite well in his post FAQ: Using Sequences in PostgreSQL, including a link to a method of creating a "gapless" sequence that should behave how you had initially expected, but at the cost of some concurrency and performance.


Can SQL Server Management Studio Express be installed standalone?

Yes, you can install Management Studio (or Management Studio Express) on a workstation that doesn't have SQL Server services. Just pick Management Tools - Complete and Management Tools - Basic during setup. You should be able to do this with any edition of SQL Server you have except for the most basic Express editions - just be aware that technically you need to possess at least one license when using it to manage production instances (though that may have loosened up over the years).

If you don't have an edition of SQL Server that comes with client tools, or you don't have a license to use the full version of Management Studio, you can always download Management Studio Express. @Bummi recommended the RTM release of SQL Server 2012 Express, but the Service Pack 2 version is a better option - since it provides a full version of Management Studio that no longer has feature limitations or licensing requirements:

http://www.microsoft.com/en-us/download/details.aspx?id=43351

I have more thorough download/install instructions in this answer:

https://dba.stackexchange.com/a/73919/1186


MySQL MSI appears to be installing wrong (latest) MySQL version

Have you fully removed previous MySQL version (including all components, folders and registry keys) before install? Do it.

And temporary disable network connection to Inet during install... it prevents offering to install up-to-date version.


Put a non-clustered index on b.ReportID, and 'include' all the other columns from b that your query uses.


logical reads on global temp table, but not on session-level temp table

Minimal logging is not being used when using INSERT INTO and global temp tables

Inserting one million rows in a global temp table by using INSERT INTO

INSERT INTO ##t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

When running SELECT * FROM fn_dblog(NULL, NULL) while the above query is executing,  ~1M rows are returned.

One LOP_INSERT_ROW operation for each row + other
log data.

The same insert on a local temp table

INSERT INTO #t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

Only going up to 700 rows returned by  SELECT * FROM fn_dblog(NULL, NULL)

Minimal logging

Inserting one million rows in a global temp table by using SELECT INTO

SELECT top(1000000) s1.r
INTO ##t2
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

SELECT INTO a global temp table with 10k records

SELECT s1.r
INTO ##t2
FROM dbo.s1;

Time and IO Statistics

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 10 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

Based on this blogpost we can add TABLOCK to initiate minimal logging on a heap table

INSERT INTO ##t1 WITH(TABLOCK) (r)
SELECT   s1.r
FROM dbo.s1

Low logical reads

Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(10000 rows affected)

Part of an answer by @PaulWhite on how to achieve minimal logging on temporary tables

  No. Local temporary tables (#temp) are private to the creating
  session, so a table lock hint is not required. A table lock hint would
  be required for a global temporary table (##temp) or a regular table
  (dbo.temp) created in tempdb, because these can be accessed from
  multiple sessions.

Creating a regular table to test this:

CREATE TABLE dbo.bla
(
    r int NOT NULL
);

Filling it up with 1M records

INSERT INTO bla
SELECT   top(1000000)s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

>1M logical reads on this table

Table 's1'. Scan count 17, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bla'. Scan count 0, logical reads 1001607, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Paul White's answer explaining the logical reads reported on the global temp table

  Generally, logical reads are reported for the target table when the
  insert is not minimally logged.

  These logical reads are associated with finding a place in the
  existing structure to add the new rows. Minimally-logged inserts use
  the bulk-loading mechanism, which allocates whole new pages/extents
  (and so does not need to read the target structure in the same way).

Conclusion

The conclusion being that the INSERT INTO is not able to use minimal logging, resulting in logging every inserted row individually in the log file of tempdb when used in combination with a global temp table / normal table.
Whereas the local temp table/ SELECT INTO/ INSERT INTO ... WITH(TABLOCK) is able to use minimal logging.


Seperate volumes for tempdb, data, and backups on SQL Servers in VMs

I like to separate them even if they are pointing to the same drives for disk space reasons. If I run out of space on my backup drive/mount point, it's not going to break the database (not right away at least).

This is how  I usually separate things at a minimum:

Data files for user databases
Log files for user databases
Data file(s) for tempdb
Log file for tempdb
Data and log files for system databases, except tempdb
Backups (though I prefer to use a network share instead)

Each of the above would be a separate drive or separate mount point. Depending on the system, I might have separate devices for each database's data files.

But my systems have always used a SAN rather than local disks. If you had the choice between 3 drives or 5 drives, I would just ensure backups are separate and tempdb is separate. But again only for disk space reasons.


Are there alternatives to Microsoft Access?

Check out Linked Tables to SQL Server if you're running up against complexity requirements for your data.  Basically, as you are expecting, Access would still work as your front-end and SQL Server would work as the back-end.  For complex SQL statements, you can make use of Stored Procedures that you call from Access as well.  If nothing else, this could give you some more time to rewrite the Access application in something a little more robust like .NET (language/platform of your choosing).  Honestly though, if there's no pressing need to change out the Access application, keep it around if it's doing the job.  I support clients that run tons of business functionality off of an Access front end tied to a basic availability group on SQL Server Standard Edition for high availability.  It does the job just fine and it doesn't cost an arm and a leg.


Singleton Table. In relational terms it is a relation with primary key ∅ (the empty set).


A couple of points.

When looking at benchmarks, I would double-check to see that it is running the type of load that you are focused on. You mention supplying "the call data daily". Is this via a large batch? How much data per call? I imagine it can't be very much - with say 500 bytes of data per call, that makes 50GB per day = 18TB per year (~5 TB/90 days) - but with aggregation, you should be able to reduce this storage requirement.

Will you need old(er) data for this analysis? How far back do you want to go?

You should also factor in a reasonable path for

a) increasing load,

and

b) upgrading your h/ware

My guess is that if a telco is looking for an upgrade, money will be no object (:-)).

If I were you, I would be looking at a system which can handle aggregate queries well - I would be looking at performing these aggregates on a daily, weekly, monthly and accounting-period  basis.

Since you are (apparently) adding data on a daily batched basis, you should be able to perform these aggregations on the fly with your data load process.

You appear to wish to use Open Source databases, and therefore I would recommend PostgreSQL - it is (IMHO) a much more capable database technically than MySQL.

It has CTEs, Window (aka Analytic) functions, CHECK constraints (astoundingly, MySQL doesn't have these) and Materialized Views (all natively). MySQL is working on some of these, but they are in beta - check out dev.mysql.com.

I would be looking at storing the raw data in as compressed a format as possible and working day-to-day on aggregates at least on a daily basis per phone number. This will allow you to respond quickly to most queries while still keeping all your data accessible if required.

Hardware, I'd be using RAID 1+0 - I'm guessing that like most database apps, your system will be disk bound - make sure you get the best that you can afford on that score! I believe that a fairly average (your definition may vary!) server in terms of CPU and RAM should be able for this, but again, YMMV! I'd be looking to start with at least 16GB of RAM (upgradable to at least 64) with reasonably modern CPUs.

Finally, remember (and this is critical) try to benchmark on your load with your hardware and your software. Benchmarks are tricky - basically there are lies, damned lies and statistics. After that, there are (other people's) database benchmarks! :-)

Best of luck with your project and welcome to the forum. Your question is a (bit) over-general here. You might like to take a look at the help centre - take the the tour.


Is there any way we can identify it, so that we can monitor/restrict how much resources being/will be used by AlwaysOn through resource governor.

I would absolutely not advise trying to do something like this. The point of Always On (I'm assuming you mean availability groups, here) is generally for high availability, disaster recovery, or multiple copies for read heavy workloads without using Replication.

Always On is accomplished through system processes on hidden schedulers (depending on version of SQL Server) and there wouldn't be a straight-forward way to do this with resource governor.

  I'm able to get application name for SSRS, web App and SSIS using APP_NAME() function, but not able to get application name for AlwaysOn processes.

Always On Availability Groups aren't an application but parts of the system. They use all of the normal built in mechanisms to SQL Server with only a few specifically built for it.

For example, part of the process is to write log information to disk. This is no different than writing any other log to disk, when you look at it from the outside. If you were to govern the amount of IOPs that could be done to a database then it would affect the entire database, not just the Availability Group process.


Look over the documentation regarding this extension

pg_stat_statements  does not persist between restarts...
pg_stat_statements

pg_stat_statements_reset() will clear all data


You are fighting against the technology that you're using. The point of Always Encrypted is that data is only decrypted on the client. A few quotes from the documentation:

  To successfully update the column [ed: as opposed to updating in
  T-SQL], do the following:

  SELECT the data out of the SSN column, and store it as a result set in the application. This will allow for the application (client
  driver) to decrypt the column.
      INSERT the data from the result set into SQL Server.

...

  Queries can perform equality comparison on columns encrypted using
  deterministic encryption, but no other operations (for example,
  greater/less than, pattern matching using the LIKE operator, or
  arithmetical operations).

...

  Always Encrypted is not supported for the columns with the below
  characteristics (for example, the Encrypted WITH clause cannot be used
  in CREATE TABLE/ALTER TABLE for a column, if any of the following
  conditions apply to the column):

  Columns referenced by computed columns (when the expression does    unsupported operations for Always Encrypted)
  Columns referenced by check constraints

Put more simply, you can't do things like pattern matching or inserting data from any column, even the non-encrypted ones, into a temp table. If you could find a way to do what you want within a stored procedure with no other involved tables then you've broken the security of Always Encrypted and should report that issue to Microsoft. Whoever is asking you to do this is asking for you to do something unreasonable. Do the validation on the client because that's where you have access to unencrypted data.

The below is a bad idea, and you should not do this.

Meeting your requirements is possible if your SSN column was encrypted using deterministic encryption. From the documentation:

  Use deterministic encryption for columns that will be used as search
  or grouping parameters, for example a government ID number.

This is important because it allows joins. The trick is to load all valid SSNs into a different table and to encrypt that column in the same way. You can then join the two tables to find any SSNS that aren't valid. Loading a billion valid SSNs into a table is pretty easy and takes about five minutes on my machine. You can reduce the result set further if you want to do more robust validation. The problem is applying the encryption to a billion row table can take a long time. To go through a proof of concept with just one million rows:

Here's one valid row and one invalid row loaded into the table that needs to be validated:

DROP TABLE IF EXISTS dbo.SSNS;

CREATE TABLE dbo.SSNS (
ID BIGINT NOT NULL,
SSN VARCHAR(25) COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = SSNS__SSN,
        ENCRYPTION_TYPE = DETERMINISTIC ,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);

GO
DECLARE @ssn VARCHAR(25) = '111-11-1111';
INSERT INTO dbo.SSNS VALUES (1, @ssn);
GO
DECLARE @ssn VARCHAR(25) = 'SEAN GALLARDY WAS HERE';
INSERT INTO dbo.SSNS VALUES (2, @ssn);
GO

Next populate the table of all allowed SSNs. In the code below I only insert 0.1% of the data.

DROP TABLE IF EXISTS #t_100;
DROP TABLE IF EXISTS #t_1000;
DROP TABLE IF EXISTS #t_10000;

SELECT TOP (100)
    RIGHT('0' + CAST(t.RN AS VARCHAR(10)), 2) NUM
INTO #t_100
FROM
(
    SELECT -1 + ROW_NUMBER()
        OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

-- only insert 0.1% of data as a proof of concept
SELECT '111' NUM
INTO #t_1000;

--SELECT TOP (1000)
--  RIGHT('00' + CAST(t.RN AS VARCHAR(10)), 3) NUM
--INTO #t_1000
--FROM
--(
--  SELECT -1 + ROW_NUMBER()
--      OVER (ORDER BY (SELECT NULL)) RN
--  FROM master..spt_values t1
--  CROSS JOIN master..spt_values t2
--) t;

SELECT TOP (10000)
    RIGHT('000' + CAST(t.RN AS VARCHAR(10)), 4) NUM
INTO #t_10000
FROM
(
    SELECT -1 + ROW_NUMBER()
        OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

DROP TABLE IF EXISTS dbo.ALL_VALID_SSNS_POC;

CREATE TABLE dbo.ALL_VALID_SSNS_POC (
SSN VARCHAR(25) COLLATE  Latin1_General_BIN2
);

-- about 5 minutes for all data, significantly faster for 1 million rows
INSERT INTO dbo.ALL_VALID_SSNS_POC WITH (TABLOCK)
SELECT
     t1000.NUM
        + '-' + t100.NUM
        + '-' + t10000.NUM AS SSN
FROM (SELECT TOP (1000) * FROM #t_1000) t1000
CROSS JOIN #t_100 t100
CROSS JOIN #t_10000 t10000
OPTION (MAXDOP 8, NO_PERFORMANCE_SPOOL, FORCE ORDER);

CREATE CLUSTERED INDEX CI ON dbo.ALL_VALID_SSNS_POC (SSN);

Now the data needs to be encrypted which is the problem. The process took about an hour and grew the transaction log by 250 GB before I killed it. Among other things, updating a billion rows like this will be expensive:

UPDATE [dbo].[ALL_VALID_SSNS] SET
[dbo].[ALL_VALID_SSNS].[tceGuidCol1] = NEWID()

It seems possible to do if you're willing to wait long enough and you have enough log space. Be sure to increase your timeout. I also had better results generating powershell code through the "Encrypt Columns..." SSMS database task and running the code that way.

Once the encryption is done you can get all of the rows with valid SSNs with a query like this:

SELECT s.*
FROM dbo.SSNS s
WHERE EXISTS (
    SELECT 1
    FROM dbo.ALL_VALID_SSNS_POC vs
    WHERE s.SSN = vs.SSN
);

and you can get all of the rows with invalid SSNs with a query like this:

SELECT s.*
FROM dbo.SSNS s
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.ALL_VALID_SSNS_POC vs
    WHERE s.SSN = vs.SSN
);


Does InnoDB store row counts in secondary index B-Tree non-leaf nodes?

I am not 100% sure about the first part, but I would say NO - if it did, it would have to update the counts all the way to root on each write, which would mean writing more pages to disk and syncing that. When you add/remove the leaf node, then in the good case you just need to write that one page to disk, and with this you would have to update all those "parents" - say 2-5 more pages depending on the number of rows in the table.

The other thing is that InnoDB cannot use that information anyway in queries. When you run the COUNT, you run it in some transaction, that usually means "repeatable reads" mode (but similar reasons will apply to other modes). So the query has to check the visibility of each row/record in the current transaction scope. And you cannot keep separate count per active transaction in the b-tree.