Frequently Querying the DMV - sys.dm_exec_sessions, sys.dm_exec_connections and sys.dm_exec_requests

It depends on the amount of connections and your hardware + your ability to change the query.

The first thing you should do is look at the query and decide which columns you need and which columns you don't need.

If we run the query with

set statistics io, time on;

On an idle server with < 100 connections

select count(*) from sys.dm_exec_connections

Result

20

The result is clear:

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 18 ms.

The logicalreads are also very small (thanks statisticsparser):

This should mean that the query is harmless on this server,even if we run it every 5 seconds.

On a prod server with over 10k connections

select count(*) from sys.dm_exec_connections

Result

13713

Which means that every 5 seconds, around 300 - 400 ms cpu time will be used:

 SQL Server Execution Times:
   CPU time = 422 ms,  elapsed time = 485 ms.

And that amounts to the query running 1/10th of every 5 seconds.

However, if you can change the query, either by omitting some columns, or using a temp table, the overhead could be lowered by a lot.

E.G. Splitting up the query in two parts and removing some columns.

set statistics io, time on;
select sys.dm_exec_sessions.session_id, host_name as [Connecting machine name], client_net_address AS [Connecting machine IP],
program_name,client_interface_name,login_name,nt_domain,nt_user_name,
sys.dm_exec_sessions.status AS [Session status],command,r.Status AS [Request Status],
last_request_start_time,last_request_end_time,r.connection_id
INTO #temp
from sys.dm_exec_sessions
inner join sys.dm_exec_connections on sys.dm_exec_sessions.session_id = sys.dm_exec_connections.session_id
inner join sys.dm_exec_requests r on sys.dm_exec_connections.connection_id = r.connection_id

select #temp.session_id, #temp.[Connecting machine name], #temp.[Connecting machine IP],
program_name,client_interface_name,login_name,nt_domain,nt_user_name, name as DatabaseName,
#temp.[Session status],#temp.command,r.Status AS [Request Status],
last_request_start_time,last_request_end_time,r.status, text,  *
FROM #temp
inner join sys.dm_exec_requests r on #temp.connection_id = r.connection_id
inner join sys.databases on r.database_id = sys.databases.database_id
cross apply sys.dm_exec_sql_text(r.sql_handle)

DROP TABLE #temp

Resulting in 10X lower amount of CPU usage.

Extra testing

On a server with 600 connections

select count(*) from sys.dm_exec_connections

600

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 40 ms.

About twice as much cpu time as with 20 connections.

Testing every 5 seconds on the idle server

If we add two jobs that run every 10 seconds.

(with 5 seconds between them because we cannot go lower than each 10 seconds scheduling.)

And watch the result, The jobs started executing at ~9 AM on a server with 2 cores @3,5GHZ:

The cpu usage difference is not visible, before and after the 9AM mark.

Server activity remains closely the same

DB IO.

Wait stats went up with a negligible 20ms on ASYNC_NETWORK_IO.


Depending on what country you are in the rules for addresses (especially postal addresses) can get pretty dicey. For example, you're pretty safe to assume that a zip/postal code has one official city name, but both the U.S. and Canada allow for alternative city names for a postal code. I know this for a fact because I used to develop postal address validation software for North America. The non-official names are often recognized by the postal authorities and you typically have to permit their use.

Outside of U.S. / Canada the rules can get even more loose.  There are not that many countries with postal authorities that standardize addresses in a fairly hierarchical structure (U.K., Germany, Australia) so in a lot of countries you can't even count on that.

The difficulty with normalizing address data is that, as you've already seen, it is somewhat hierarchical, but inconsistent across different geographies.  Japan has prefectures, Canada has provinces and territories, the U.S. has states, districts, territories and commonwealths.  Apart from naming conventions, in different places you may skip (or add) entire levels of geography.

You're thinking of the problem from the wrong angle.

You're instinct to be wary of redundant data is a good one.  In transactional systems you should always try to start with data that's in third normal form (3NF) at least.  This helps you to avoid insert, update and delete anomalies.  In other words, the problem with redundant data is that it can become inconsistent when you start changing it.

In your case, however, all of your geographic data is coming from a Google API. If you change an address, I presume you're going to go back to the Google API and refresh the whole thing as a unit.  You wouldn't go in and selectively change one element in an address because it had been renamed, for example.

Normalization doesn't especially help you because you aren't manipulating data elements within the address, you're always working with the address as a whole.

In your case, I would recommend leaving address elements classified in columns according to what the Google API says they are.  Don't try to build trees because they will be hard to work with and the usual reasons for wanting to represent your data in a tree don't apply to your situation.


How to join multiple results into one row with dynamic columns?

One SQL sentence can't have variable fields per row; every row has same fields. But every field can have or haven't (null) value.

If you want that in pure SQL you can group_concat the fields of wp_usermeta, like:

SELECT use.name, use.email, group_concat(concat('{', meta_key, '##', meta_value, '}')) AS metadata
FROM wp_users use
INNER JOIN wp_usermeta met ON (use.user_id = met.user_id)
GROUP BY met.user_id

But then you need post-process outside Mysql.


Workload groups are a concept of the resource governor feature in SQL server.  You would define workload groups (such as a prod group and a test group) on a server and then define the amount of resources that can be used by each.

So for instance if you only had one SQL server for test and prod say, you could define the 2 workload groups and limit the CPU/Memory that can be used by each, to limit the impact of the test workload on the prod workload (SQL 2014 can also limit the IOPs used by each group too).

You should read up more on the resource governor here:

Resource Governor


Mysql causing high load average with large amount of server resources left

Are you using MyISAM?  Or InnoDB?  Or both?

  [!!] Query cache is disabled

This is "good", not "bad".

  [!!] Table cache hit rate: 0% (400 open / 78K opened)

How many tables to you have?  Thousands?  Please elaborate on why so many.  (This is usually a design flaw.)  Meanwhile, increase table_open_cache to, say, 2000.  What MySQL version are you running?  Set table_open_cache_instances = 8 if it is a new enough version.

  [OK] Highest usage of available connections: 36% (294/800)

Yeah that is "OK" in one sense.  But in another sense, 294 simultaneous connections is a lot.  Please elaborate.  Perhaps they are failing to disconnect?

  [--] Up for: 9h
  [OK] Slow queries: 0% (5K/42M)
  load average is about 3~4
  long_query_time                 = 1

Those sort of contradict each other.  "Load average", in my opinion, is not very useful.  Do you have a measure of CPU utilization?  Still, if CPU is high, why aren't there more queries taking more than 1 second?  Anyway, please run pt-query-digest and let's see the first couple of queries it finds.  We may be able to speed them up; that may speed up the system.

Update

Thanks for the update.

SELECT COUNT(*) on InnoDB tables is proportional to the size of the table, so it will get slower and slower.  The slowlog will point out which of them are run often enough to be a nuisance.

Between the SSDs and sufficient caching (key_buffer, buffer_pool, table_open_cache) plus innodb_flush_log_at_trx_commit  = 2, you have probably minimized the I/O.  Now to focus on slowlog.


PostgreSQL - using password/md5 authentication with empty password

Indeed an empty password is equivalent to no password so it's not going to be accepted by the server when pg_hba.conf says that a password is required.

Still, there are various ways to avoid inputting the password:

trust method in pg_hba.conf, possibly constrained to a specific user and database
client-side PGPASSWORD environment variable
client-side .pgpass file
dummy password like "test" stored in the same place than the login name


Most efficient (or meaningful) way to index grid-based data

Two things

Since  range of x and z are +/- 1000000, use meduimint (Range of meduimint is +/- 8388608)
Since ranges of offset and type is 1-65535, use smallint unsigned

Make these chnages

CREATE TABLE `bitstrings` (
  `x` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `z` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `type` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-4096',
  `offset` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-65535',
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192'
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

When it comes to indexes, you may have a choice

CHOICE #1 : Use a primary key of ID

Setup the table like this

CREATE TABLE `bitstrings` (
  `id` int unsigned not null auto_incrmenet,
  `x` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `z` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `type` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-4096',
  `offset` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-65535',
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192',
  primary key (id),
  key xztype (x,z,type)
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

This could allow you to query using x and z, while retrieving all ids to have a quick reference back to the row:

SELECT id FROM bitstrings WHERE x=0 and z=0;

If you ever have to retrieve the data for a given id, you could select it as

SELECT * FROM bitstrings WHERE id = 12;

This will retrieve the specific info for whatever x, z and type are there.

CHOICE #2 : Use x,z,type as the PRIMARY KEY

Run this query

SELECT COUNT(1) rcount,x,z,type FROM bitstrings GROUP BY x,z,type HAVING COUNT(1) > 1;

If this query comes back with no rows at all, this can be your primary key

CREATE TABLE `bitstrings` (
  `x` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `z` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `type` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-4096',
  `offset` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-65535',
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192',
  primary key (x,z,type)
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

CONCLUSION

Since all queries are based on mainly on x and z, either choice would be OK

The first choice would yield a bigger .MYI file, but two ways to gather data (grouped by x,z or granularly by id).
The second choice only gathers by x and z but a smaller .MYI file.


Use uuid if you need it.
Don't use it if you don't.

bigint is smaller and a bit faster in multiple ways. 8 vs. 16 bytes do not sound like much, but the difference stacks up for big tables and multiple instances. (And rarely would either uuid or bigint make sense for small tables to begin with.)

bigint is typically also much easier on the human eye and for human handling and for representation when numbers don't get that big after all. UUID always requires 32-character representation (optionally plus dashes).

If you have two of those (PK & FK) in the same table, rows grow by 16 bytes. That may make a difference for narrow rows, or be negligible for wide rows. More importantly, associated indexes also grow in size. While the PK index for a bigint occupies 16 bytes per row (incl. 8 byte index tuple overhead), it's 24 bytes for uuid. If RAM is not available in abundance, that can mean the difference of several indexes residing in RAM vs. being evicted and read back from storage repeatedly. And that would be a substantial difference.

Do you even need bigint? Often, a 4-byte integer is good enough (if it's really good enough) ...

Related:

What is the optimal data type for an MD5 field?


How to auto assign ordinal ranking in mysql with as minimal queries for new rows?

If you are content with adding and deleting a view_id per user_id, here is what I see

Adding View 12 To User 10

I see three queries

Insert new view into user with BogusRank
Move all rank down below NewRank
Change BogusRank to New Rank for new view

Here is the proposed code

SET @view_id = 12;
SET @user_id = 10;
SET @newrank = 3;
SET @defrank = 999999999;
INSERT INTO userviewranks
(view_id,user_id,ordinal_ranking)
VALUES (@view_id,@user_id,@defrank);
UPDATE userviewranks SET ordinal_ranking = ordinal_ranking + 1
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking >= @newrank;
UPDATE userviewranks SET ordinal_ranking = @newrank
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking = @defrank;

Deleting View 12 From User 10

I see three queries

Retrieve Row With View to Delete
Move All View Up in Rank
Delete Row with Old View

Here is the proposed code

SET @view_id = 12;
SET @user_id = 10;
SELECT id,ordinal_ranking INTO @id_to_zap,@rank_to_zap FROM userviewranks
WHERE view_id = @view_id AND user_id = @user_id;
UPDATE userviewranks SET ordinal_ranking = ordinal_ranking - 1
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking > @rank_to_zap;
DELETE FROM userviewranks WHERE id = @id_to_zap;


Is there are a way to restore System schema to it's original state?

There is no way to restore SYSTEM schema to the original state of empty database if you haven't backups, so you should recreate database. You could do this with Database Configuration Assistant (I'm using Windows Server 2012): run it, select delete database option, select database you want to delete, enter password to dba user and execute it.

After this you could recreate database with options you need.


You need a multicolumn index (id, timeC, value) or (timeC, id, value) because existing timeC and defailt id indexes are not sufficient.

The common rule is to create the index containing all the columns used by JOIN, GROUP BY, ORDER BY and WHERE clauses. The order columns should be listed in the index can vary due to the exact queries, columns cardinality and even the amount of data.


Do not lock the whole table on select statement

Sounds like you want to consider READ COMMITTED SNAPSHOT ISOLATION, which uses tempdb to provide a point-in-time version of the data and prevents writers from blocking readers and vice-versa (as long as it's ok that readers do not see the most current version of the row). With a table that large and having this much of an impact by concurrent write operations, the impact to tempdb could be huge, and there's no way for us to predict it, so you will need to test it.

Kendra Little has a great intro post from the administration side here:

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide

And Paul White goes into detail about what happens at the query level here, in his series on ACID and the various transaction isolation levels in SQL Server:

Read Committed Snapshot Isolation

Official documentation which you'll want to review, especially to understand how it works and the limitations involved:

Understanding Row Versioning-based Isolation Levels
Choosing Row Versioning-based Isolation Levels
Enabling Row Versioning-based Isolation Levels
Using Row Versioning-based Isolation Levels


Sequence according to value from table in postgresql

If I understand correctly you want to have ids that depend on another column. I'm sorry not to answer your question, because I don't understand that need. Why would you need that ?

The only purpose of a sequence is giving unique values. What's wrong with that kind of data?

id       type_id     value
-------  -------  -------
1        1         value1
2        2         value2
3        1         value3
4        1         value4
5        2         value5

If your problem is that you want to be able to sort your data by type_id and then by id, just try that query :

select id,
  type_id,
  value
from my_table
order by type_id, id


Why do I have RID locks if I have a clustered index?

Use sp_WhoIsActive, a totally free script from MVP Adam Machanic:

sp_WhoIsActive @get_locks = 1

That adds an XML column of lock data for each running query. You can see which indexes, objects, databases, etc are locked, what kind of locks are held, and how many of them are held.


Mysql master-slave replication makes no sence

Am I missing something here ?

sure, just few row above:

  For example, DDL statements such as CREATE TABLE and ALTER TABLE are always logged as statements, without regard to the logging format in effect, so the following statement-based rules for --binlog-do-db always apply in determining whether or not the statement is logged.


How to both reference and return a result set from a stored procedure in mysql?

Here you can use user defined variables, as long as you only have only one table2.id
BEGIN
   #return this and also use it in the following query
   select id, name, @id :=  table2_id
   from table1
   where id = variable_id;

   select id, table2_name
   from table2
   where table2.id = @id
END

Or if you have more than one table2.id
BEGIN
   #return this and also use it in the following query
   select id, name, table2.id
   from table1
   where id = variable_id;

   select id, table2_name
   from table2
   where table2.id in (select table2<_id
                       from table1
                       where id = variable_id);
END

Or you can join directly, and ave only one result set
BEGIN
   #return this and also use it in the following query
   select t1.id, t1.name, t1.table2_id,t2,id, t2.table2_name
   from table1 t1 INNER JOIN table2 t2 ON t2.id = t1.table2_id
   where t1.id = variable_id;
END


How to use SELECT statement to get data from SQlite table using with dictionary values?

Have you tried implementing row factory? https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory
More details here: https://xbuba.com/questions/3300464/how-can-i-get-dict-from-sqlite-query


Have I misunderstood the amount of "automatic optimization" that PostgreSQL does without me having to be "clever" when designing my tables?

Having separate tables for the ua and ref strings will result in space savings if those values are long enough and repeated often. The added complexity during query and insert is nothing I would worry about.
A bigint uses 8 bytes of storage, and a row in a table has an overhead of at least 24 bytes. The latter is irrelevant if the values are repeated often enough.
A string like MOSAIC 1.0 will occupy 11 bytes on disk after TOASTing, so the savings over the 8 bytes of a bigint will be minimal. I wouldn't consider using a separate table in that case just for saving space.


RDS Replication Error (Apply Error 1406 / Truncation)

Get 5.6.3 (or later).  As I understand it, these need to be applied to the Replica:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
ALTER TABLE tbl ROW_FORMAT=DYNAMIC; -- or COMPRESSED

That should allow you to keep 255 (instead of 191), thereby avoiding the truncation.


Amusingly enough, you have the answer in your question, specifically, run as. RunAs and in case MS butchers links again, RunAs

RunAs allows you to run a program as another user. You can't automate the entry of credentials, which is a pain and you won't be able to see your password but it does work. It also fails in situations like SSRS development when you attempt to preview a report, that is spawned in a separate process which does not inherit the credentials. But, I had no issues with it from an SSIS perspective.

runas /netonly /user:OtherDomain\billinkc "devenv.exe /nosplash"

where "devenv.exe" is the full qualified path to your Visual Studio version and whether you have a 64 bit OS. On my machine, that could be

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe


"Force Encryption" vs "Force Protocol Encryption" in SQL Server

Microsoft's MSDN blog has a table describing the possible conditions and their outcomes.

See Selectively using secure connection to SQL Server to understand client side setting and connection property options impacting secure connections for just the client involved. The server and other clients are not impacted.


SQL Server Management Studio 2012 - horrible blue background

Are you talking about the below appearance?

If that's the case, then I don't think that blue background is able to be changed.  I'm not sure why that'd be such a hang-up, as if you're staring at that background in SSMS, then you're probably not really doing anything.  Like Martin said above, the query window or other placeholders in that area will cover that completely.

When you have a query window open, it should look similar to the below screenshot:

As you can notice, you don't really see that blue background at all.  99% of the time (at least for me when I'm in SSMS), this will be the visual representation of SSMS.


How to store lots of small text in a SQL Server database?

Because this is a log of sort, and you have space limitations on your database (due to your Express edition) I would recommend either archiving your historical log data away from the database or simply deleting data that is older than a certain elapsed duration threshold. It is not uncommon to have an agreement that history is retained only for a certain time period. Just ensure this is covered in your service level agreement provided it pertains.


check if a user-defined type exists before dropping it

This works the same as in SQL Server; use the following style of construct:

IF OBJECT_ID('dbo.MyObject') IS NOT NULL
BEGIN
    EXEC sp_droptype 'dbo.MyObject';
END

OBJECT_ID(...) returns an integer representing the object_id from sysobjects for every object stored in the current database.  It will return NULL if the object does not exist.

Another way of testing for object existence:

IF EXISTS (SELECT 1 FROM systypes st WHERE st.name = 'MyType')
BEGIN
   EXEC sp_droptype 'MyType';
END

I don't have the ability to create a type in ASE at the current time, so I cannot say for certain if OBJECT_ID() will work.


How to run update query without interrupt other processes

That's the way InnoDB works anyway. Take a look at the MVCC (multi-version concurrency control - aka record-versioning or record-shadowing) mechanism which most databases use today.

If you are doing a long update and somebody wants to read a record, what the person doing the read will see is the database as it was before the update started. Then, when that long update commits, all subsequent queries will see the system post-update. This way, InnoDB's transactions are ACID, but futhermore, writers don't block readers and vice versa.

For more detail about MySQL specifics, see the manual here - then get back to us if you have more specific questions. If you're really interested in this topic, then I suggest that you read this chapter of the manual which goes into (a lot of) detail about the architecture of the InnoDB storage engine.

The MyISAM engine exclusively uses a locking mechanism, but nobody should really be using it any more as it's now deprecated (if Bill Karwin says so, it must be true!).


Fastest way to get current user's OID in Postgres?

The fastest way I know of is a lookup in the system catalog view pg_roles:

SELECT * FROM pg_roles WHERE rolname = 'postgres';

Strictly speaking, it would be even slightly faster to use the underlying table pg_authid, but access to it is restricted to superusers for good reasons.

There is no object identifier type like for tables or types, which would allow a simple cast like 'mytable'::regclass.

Be aware though, that OIDs are not stable across a dump / restore cycle. So, the OID is no good for the use case!

In some databases I have a separate login table with a serial primary key that I use for similar purposes. Maintained manually. And functions using it are prepared to occasionally not find a user in this table. A very basic and fast table:

CREATE TABLE users.login (
  login_id serial PRIMARY KEY
 ,username text NOT NULL UNIQUE
);

When creating new users I use a plpgsql function that creates the new user in the system and enters it into my table at the same time. And I use this login_id in many places. For instance I keep track of who made the last change to a row in most tables. I use this simple function:

CREATE OR REPLACE FUNCTION public.f_login_id()
  RETURNS int AS
$func$
SELECT COALESCE((SELECT login_id FROM users.login
                 WHERE  username = session_user::text), 0::int)
$func$ LANGUAGE sql STABLE;

No foreign key constraints so to keep things fast and simple. Obviously, I don't need strict referential integrity ...


How to make PG query faster even when it is using indices

One odd thing is that the filter Filter: (school_year = 2018) doesn't remove any rows.  So either the entire table has school_year = 2018, or all the rows which meet the criteria classes in ('1st','2nd','3rd') have school_year = 2018. Either way, it seems a bit odd.  Is this how your real problem is, or is it an artefact of creating a smaller demonstration problem?

The slowness probably comes from the IO needed to read a large amount of the table.  Doing the EXPLAIN (ANALYZE, BUFFERS), especially with "track_io_timing" turned on, would be especially helpful to figure that out.

If so, creating an index that includes all of the columns, like:

create index on mytable (classes, school_year, score);

should make this much faster.  It will allow an index-only scan, instead of an scan which still needs to visit the table for each row.  But for this to be effective, the table must be kept well-vacuumed.  The default settings of autovac will probably not be enough.


create index - invalid memory alloc request size

This sort of problem almost certainly means damaged table information, either regarding table headers, page headers, or tuple headers.  Your best option is to dump and restore, or to restore from backup before the crash.

Wish I had a better answer for you but you have to consider that you have fixed some problems and now there are others.  You have to assume that there will be others still.


The tuples would have to be ordered by from_id, to_id in such a way that when from_id or to_id changes, it would fall under a different grouping.
I was thinking of writing this as a Stored Procedure, but I thought of something much more intriguing (I just got the idea from my answering this question Update ranking on table about 3 hours ago)

I'll do it in stages

Stage 1 : Sample Data

DROP DATABASE IF EXISTS bootvis;
CREATE DATABASE bootvis;
USE bootvis
CREATE TABLE graph_edges
(
    pk int not null auto_increment,
    object_id int default null,
    tm timestamp not null default current_timestamp,
    from_id int,
    to_id int,
    primary key (pk)
);
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
SELECT SLEEP(2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
SELECT SLEEP(1);
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1);
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
UPDATE graph_edges SET object_id = 1 WHERE object_id IS NULL;
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1);
SELECT SLEEP(2);
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
UPDATE graph_edges SET object_id = 2 WHERE object_id IS NULL;
SELECT * FROM graph_edges;

Stage 2 : Sample Data Loaded

mysql> DROP DATABASE IF EXISTS bootvis;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE bootvis;
Query OK, 1 row affected (0.00 sec)

mysql> USE bootvis
Database changed
mysql> CREATE TABLE graph_edges
    -> (
    ->     pk int not null auto_increment,
    ->     object_id int default null,
    ->     tm timestamp not null default current_timestamp,
    ->     from_id int,
    ->     to_id int,
    ->     primary key (pk)
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT SLEEP(2);
+----------+
| SLEEP(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2);
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1);
Query OK, 8 rows affected (0.08 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UPDATE graph_edges SET object_id = 1 WHERE object_id IS NULL;
Query OK, 22 rows affected (0.06 sec)
Rows matched: 22  Changed: 22  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1);
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT SLEEP(2);
+----------+
| SLEEP(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UPDATE graph_edges SET object_id = 2 WHERE object_id IS NULL;
Query OK, 28 rows affected (0.06 sec)
Rows matched: 28  Changed: 28  Warnings: 0

mysql> SELECT * FROM graph_edges;
+----+-----------+---------------------+---------+-------+
| pk | object_id | tm                  | from_id | to_id |
+----+-----------+---------------------+---------+-------+
|  1 |         1 | 2012-11-20 17:29:13 |       1 |     2 |
|  2 |         1 | 2012-11-20 17:29:13 |       1 |     2 |
|  3 |         1 | 2012-11-20 17:29:13 |       1 |     2 |
|  4 |         1 | 2012-11-20 17:29:13 |       1 |     2 |
|  5 |         1 | 2012-11-20 17:29:15 |       2 |     1 |
|  6 |         1 | 2012-11-20 17:29:15 |       2 |     1 |
|  7 |         1 | 2012-11-20 17:29:16 |       1 |     2 |
|  8 |         1 | 2012-11-20 17:29:16 |       1 |     2 |
|  9 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 10 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 11 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 12 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 13 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 14 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 15 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 16 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 17 |         1 | 2012-11-20 17:29:16 |       1 |     2 |
| 18 |         1 | 2012-11-20 17:29:16 |       1 |     2 |
| 19 |         1 | 2012-11-20 17:29:16 |       1 |     2 |
| 20 |         1 | 2012-11-20 17:29:16 |       1 |     2 |
| 21 |         1 | 2012-11-20 17:29:17 |       2 |     1 |
| 22 |         1 | 2012-11-20 17:29:17 |       2 |     1 |
| 23 |         2 | 2012-11-20 17:29:17 |       1 |     2 |
| 24 |         2 | 2012-11-20 17:29:17 |       1 |     2 |
| 25 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 26 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 27 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 28 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 29 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 30 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 31 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 32 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 33 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 34 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 35 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 36 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 37 |         2 | 2012-11-20 17:29:19 |       2 |     1 |
| 38 |         2 | 2012-11-20 17:29:19 |       2 |     1 |
| 39 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 40 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 41 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 42 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 43 |         2 | 2012-11-20 17:29:19 |       2 |     1 |
| 44 |         2 | 2012-11-20 17:29:19 |       2 |     1 |
| 45 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 46 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 47 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 48 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 49 |         2 | 2012-11-20 17:29:19 |       2 |     1 |
| 50 |         2 | 2012-11-20 17:29:19 |       2 |     1 |
+----+-----------+---------------------+---------+-------+
50 rows in set (0.00 sec)

mysql>

Stage 3 : Create Query with Running Counters That Changes When Tuple Changes (call it the Tuple Change Query)

SET @r=0;
SET @old_from=-1;
SET @old_to=-1;
SELECT from_id,to_id,
@inc:=((@old_from<>from_id)||(@old_to<>to_id)),
@old_from:=from_id,@old_to:=to_id,
@r:=(@r+@inc) as group_number
FROM graph_edges;

Stage 4 : Run the Tuple Change Query

mysql> SET @r=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @old_from=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @old_to=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT from_id,to_id,
    -> @inc:=((@old_from<>from_id)||(@old_to<>to_id)),
    -> @old_from:=from_id,@old_to:=to_id,
    -> @r:=(@r+@inc) as group_number
    -> FROM graph_edges;
+---------+-------+------------------------------------------------+--------------------+----------------+--------------+
| from_id | to_id | @inc:=((@old_from<>from_id)||(@old_to<>to_id)) | @old_from:=from_id | @old_to:=to_id | group_number |
+---------+-------+------------------------------------------------+--------------------+----------------+--------------+
|       1 |     2 |                                              1 |                  1 |              2 |            1 |
|       1 |     2 |                                              0 |                  1 |              2 |            1 |
|       1 |     2 |                                              0 |                  1 |              2 |            1 |
|       1 |     2 |                                              0 |                  1 |              2 |            1 |
|       2 |     1 |                                              1 |                  2 |              1 |            2 |
|       2 |     1 |                                              0 |                  2 |              1 |            2 |
|       1 |     2 |                                              1 |                  1 |              2 |            3 |
|       1 |     2 |                                              0 |                  1 |              2 |            3 |
|       2 |     1 |                                              1 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       1 |     2 |                                              1 |                  1 |              2 |            5 |
|       1 |     2 |                                              0 |                  1 |              2 |            5 |
|       1 |     2 |                                              0 |                  1 |              2 |            5 |
|       1 |     2 |                                              0 |                  1 |              2 |            5 |
|       2 |     1 |                                              1 |                  2 |              1 |            6 |
|       2 |     1 |                                              0 |                  2 |              1 |            6 |
|       1 |     2 |                                              1 |                  1 |              2 |            7 |
|       1 |     2 |                                              0 |                  1 |              2 |            7 |
|       2 |     1 |                                              1 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       1 |     2 |                                              1 |                  1 |              2 |            9 |
|       1 |     2 |                                              0 |                  1 |              2 |            9 |
|       1 |     2 |                                              0 |                  1 |              2 |            9 |
|       1 |     2 |                                              0 |                  1 |              2 |            9 |
|       2 |     1 |                                              1 |                  2 |              1 |           10 |
|       2 |     1 |                                              0 |                  2 |              1 |           10 |
|       1 |     2 |                                              1 |                  1 |              2 |           11 |
|       1 |     2 |                                              0 |                  1 |              2 |           11 |
|       1 |     2 |                                              0 |                  1 |              2 |           11 |
|       1 |     2 |                                              0 |                  1 |              2 |           11 |
|       2 |     1 |                                              1 |                  2 |              1 |           12 |
|       2 |     1 |                                              0 |                  2 |              1 |           12 |
|       1 |     2 |                                              1 |                  1 |              2 |           13 |
|       1 |     2 |                                              0 |                  1 |              2 |           13 |
|       1 |     2 |                                              0 |                  1 |              2 |           13 |
|       1 |     2 |                                              0 |                  1 |              2 |           13 |
|       2 |     1 |                                              1 |                  2 |              1 |           14 |
|       2 |     1 |                                              0 |                  2 |              1 |           14 |
+---------+-------+------------------------------------------------+--------------------+----------------+--------------+
50 rows in set (0.00 sec)

mysql>

Please notice that @inc changes only when the tuple changes !!!

Stage 5 : Put Tuple Change Query in a Subquery, extract Needed Data From Tuple Change Query, Run GROUP BY group_number (call it Duplicate Extraction Query)

SET @r=0;
SET @old_from=-1;
SET @old_to=-1;
SELECT group_number,from_id,to_id FROM
(
    SELECT from_id,to_id,
    @inc:=((@old_from<>from_id)||(@old_to<>to_id)),
    @old_from:=from_id,@old_to:=to_id,
    @r:=(@r+@inc) as group_number
    FROM graph_edges
) g GROUP BY group_number;

Stage 6 : Run Duplicate Extraction Query

mysql> SET @r=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @old_from=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @old_to=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT group_number,from_id,to_id FROM
    -> (
    ->     SELECT from_id,to_id,
    ->     @inc:=((@old_from<>from_id)||(@old_to<>to_id)),
    ->     @old_from:=from_id,@old_to:=to_id,
    ->     @r:=(@r+@inc) as group_number
    ->     FROM graph_edges
    -> ) g GROUP BY group_number;
+--------------+---------+-------+
| group_number | from_id | to_id |
+--------------+---------+-------+
|            1 |       1 |     2 |
|            2 |       2 |     1 |
|            3 |       1 |     2 |
|            4 |       2 |     1 |
|            5 |       1 |     2 |
|            6 |       2 |     1 |
|            7 |       1 |     2 |
|            8 |       2 |     1 |
|            9 |       1 |     2 |
|           10 |       2 |     1 |
|           11 |       1 |     2 |
|           12 |       2 |     1 |
|           13 |       1 |     2 |
|           14 |       2 |     1 |
+--------------+---------+-------+
14 rows in set (0.00 sec)

mysql>

Stage 7 : (OPTIONAL) Show Count for Each group_number

SET @r=0;
SET @old_from=-1;
SET @old_to=-1;
SELECT group_number,from_id,to_id,count(1) group_count FROM
(
    SELECT from_id,to_id,
    @inc:=((@old_from<>from_id)||(@old_to<>to_id)),
    @old_from:=from_id,@old_to:=to_id,
    @r:=(@r+@inc) as group_number
    FROM graph_edges
) g GROUP BY group_number;

Stage 8 : (OPTIONAL) Run the Show Count Query  for Each group_number

mysql> SET @r=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @old_from=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @old_to=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT group_number,from_id,to_id,count(1) group_count FROM
    -> (
    ->     SELECT from_id,to_id,
    ->     @inc:=((@old_from<>from_id)||(@old_to<>to_id)),
    ->     @old_from:=from_id,@old_to:=to_id,
    ->     @r:=(@r+@inc) as group_number
    ->     FROM graph_edges
    -> ) g GROUP BY group_number;
+--------------+---------+-------+-------------+
| group_number | from_id | to_id | group_count |
+--------------+---------+-------+-------------+
|            1 |       1 |     2 |           4 |
|            2 |       2 |     1 |           2 |
|            3 |       1 |     2 |           2 |
|            4 |       2 |     1 |           8 |
|            5 |       1 |     2 |           4 |
|            6 |       2 |     1 |           2 |
|            7 |       1 |     2 |           2 |
|            8 |       2 |     1 |           8 |
|            9 |       1 |     2 |           4 |
|           10 |       2 |     1 |           2 |
|           11 |       1 |     2 |           4 |
|           12 |       2 |     1 |           2 |
|           13 |       1 |     2 |           4 |
|           14 |       2 |     1 |           2 |
+--------------+---------+-------+-------------+
14 rows in set (0.00 sec)

mysql>

Stage 9 : Give it a Try !!!


INSERT don't work with NOT NULL but UPDATE does, why?

The problem is in your database configuration.
MariaDB by default SQL_MODE isn't STRICT and UPDATEs even if it's set to NOT NULL.
But this is a pointer to the answer:

A mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled is called strict mode. With strict mode not set (the default), MariaDB will automatically adjust invalid values, for example, truncating strings that are too long, or adjusting numeric values that are out of range, and produce a warning.
With strict mode set, the statements will fail, and an error will be returned instead. The IGNORE keyword can be used when strict mode is set to convert the error to a warning.

So you need to set the appropriate SQL_MOD as described at: https://mariadb.com/kb/en/mariadb/sql_mode/


Cannot insert row into table after creating a trigger

The IF statement should only check the column that is being inserted for 'SI', see revised code.

DELIMITER $$
CREATE TRIGGER aio_mtltp2016 AFTER INSERT ON master_table_linfomastp2016
BEGIN
        IF New.IMHQ_solicitado = 'SI'
        THEN
        INSERT INTO imhq (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad)
        VALUES (NEW.ID_CASO, NEW.Tipo_de_estudio, NEW.Año, NEW.Codigo_Interno, NEW.Iniciales_Px, NEW.Sexo, NEW.Edad);
        END IF;
END;$$
DELIMITER ;


Put a copy of B and C in the range field (as an extra column) and make that your range key like "B_C" and you can still also have separate columns for B and C if you need.


Is it safe to use innodb_flush_log_at_trx_commit = 2

You can lose up to one second's worth of transactions. The default value is 1, which helps keep InnoDB ACID Compliant.

According to the MySQL Documentation on innodb_flush_log_at_trx_commit

  If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is
  written out to the log file once per second and the flush to disk
  operation is performed on the log file, but nothing is done at a
  transaction commit. When the value is 1 (the default), the log buffer
  is written out to the log file at each transaction commit and the
  flush to disk operation is performed on the log file. When the value
  is 2, the log buffer is written out to the file at each commit, but
  the flush to disk operation is not performed on it. However, the
  flushing on the log file takes place once per second also when the
  value is 2. Note that the once-per-second flushing is not 100%
  guaranteed to happen every second, due to process scheduling issues.

  The default value of 1 is required for full ACID compliance. You can
  achieve better performance by setting the value different from 1, but
  then you can lose up to one second worth of transactions in a crash.
  With a value of 0, any mysqld process crash can erase the last second
  of transactions. With a value of 2, only an operating system crash or
  a power outage can erase the last second of transactions. InnoDB's
  crash recovery works regardless of the value.

  For the greatest possible durability and consistency in a replication
  setup using InnoDB with transactions, use
  innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in your master
  server my.cnf file.

  Caution

  Many operating systems and some disk hardware fool the flush-to-disk
  operation. They may tell mysqld that the flush has taken place, even
  though it has not. Then the durability of transactions is not
  guaranteed even with the setting 1, and in the worst case a power
  outage can even corrupt the InnoDB database. Using a battery-backed
  disk cache in the SCSI disk controller or in the disk itself speeds up
  file flushes, and makes the operation safer. You can also try using
  the Unix command hdparm to disable the caching of disk writes in
  hardware caches, or use some other command specific to the hardware
  vendor.

Based on this, values other than 1 put InnoDB at risk of losing 1 second's worth of transactions, or a transaction commit's worth of data.

The documentation also says use sync_binlog=1.

According to the MySQL Documentation on sync_binlog

  A value of 1 is the safest choice because in the event of a crash you
  lose at most one statement or transaction from the binary log.
  However, it is also the slowest choice (unless the disk has a
  battery-backed cache, which makes synchronization very fast).

Your safest choice is

[mysqld]
innodb_flush_log_at_trx_commit=1
sync_binlog=1

If you do not mind possible data loss (up to 1 second's worth) then you can use either 0 or 2 at your own risk if the rewards (faster write speed) are worth it.


Installing SQL Server Express instance so common users can access

/SQLSYSADMINACCOUNTS adds users and groups to the SQL Servers sys admin group (all powerful).  That sys admin group is a sql server level security concept.

SQL Server and SQL Server Express runs as a service (and what it runs as does not dictate end user security).  Therefore, permissions on data directory (and the .mdf files) does not control how end users connect.

Your connection string should look something like:

Data Source=computerName\SQLExpress;Initial Catalog=myDataBase;Integrated Security=SSPI;

By default SQL Express is installed under a named instance called "SQLExpress" so the data source is the machine name where the service is running and then \ instance name.  Integrated security tells the client to connect with it's current credentials.  If that user is in the group that has sys admin role membership, it will be powerful.

Here's a link with more connection string samples:

http://www.connectionstrings.com/sql-server-2005

You can also use sql accounts (not integrated) and then embed those in the connection string:  https://stackoverflow.com/questions/997169/c-sharp-2008-sql-server-express-connection-string


Non-Clustered Index takes much longer to create than clustered Index

Open Questions

What does the table's DD look like?
What order are the columns in?

Clustered Index Creation

When you create a clustered index, you are actually sorting the data in the table in the order of the clustered index you are creating.

  They are not ~a copy~ of the table, they are the table, ordered by the column(s) you choose as the key. It could be one. It could be a few. It could be a GUID! But that’s for another time. A long time from now. When I’ve raised an army, in accordance with ancient prophecy.

Reference: Clustered Index key columns in Nonclustered Indexes (Brentozar.com)

Non-clustered Index Creation

The column order of the clustered index is totally different from the order of the columns in the non-clustered index, which has a direct impact on the creation of the index. The database engine has to search through your clustered index multiple times until it has all the information together in the correct order to create the non-clustered index. That is why it is taking so long to create the non-clustered index.

Then there is the overhead that a clustered index is always part of the non- clustered index.

Differences between SQL Server 2014 and SQL Server 2016

As for the difference in index creation on the various versions: Are you really comparing apples with apples and not with pears? Or to put it differently: Are the SQL Server 2014 servers running on identical hardware with the same CPU core frequency, with the same amount of RAM and identical MAX_DOP settings?

There some limitations/restrictions regarding index creation when using Standard or Enterprise Edition...

Enterprise vs. Standard Edition

  Parallel index operations are not available in every SQL Server edition. For more information, see Features Supported by the Editions of SQL Server 2016

  [...]

  Parallel index execution and the MAXDOP index option apply to the following Transact-SQL statements:

  CREATE INDEX
  ALTER INDEX REBUILD
  DROP INDEX (This applies to clustered indexes only.)
  ALTER TABLE ADD (index) CONSTRAINT
  ALTER TABLE DROP (clustered index) CONSTRAINT

Reference: Configure Parallel Index Operations (Microsoft Docs)

If you then have a quick look at the features for SQL Server 2014 and 2016 then both editions limit MAXDOP for Standard Edition:

SQL Server 2014 - Editions and supported features of SQL Server 2016 - RDBMS Manageability

                            Enterprise    Standard
Parallel indexed operations     Yes          -

Reference:Features Supported by the Editions of SQL Server 2014 (Microsoft Docs)

SQL Server 2016 - Editions and supported features of SQL Server 2016 - RDBMS Manageability

                            Enterprise    Standard
Parallel indexed operations     Yes          No

Reference:Features Supported by the Editions of SQL Server 2014 (Microsoft Docs)

This option has a direct impact on index creation and this is probably the reason why you are seeing great differences in index creation time.

Thoughts

You might want to consider a re-design of your indexes. I'd recommend reading some of the following articles:

SQL Server Index Architecture and Design Guide (Microsoft Docs)
Where Clustered Index Keys Dare (Brenozar.com)
Nonclustered indexes require the “lookup” key in the b-tree when? (SQLSkills.com)
SQLskills SQL101: Indexing Basics (SQLSkills.com)

Additional Reading

Configure Parallel Index Operations (Microsoft Docs)
When should a primary key be declared non-clustered? (DBA.SE)


Since both ALTER EVENT SESSION and DROP EVENT SESSION are DDL statements that have Server Scope, I would suggest setting up a Server DDL Trigger to capture when your EE is stopped/dropped.

This generates a simple notification when either DDL statement is executed, but feel free to alter it to your liking:

CREATE TRIGGER EE_Change_Notifications
ON ALL SERVER
FOR ALTER_EVENT_SESSION, DROP_EVENT_SESSION
AS
    DECLARE @msgBody NVARCHAR(MAX)

    SET @msgBody = N'Extended Event [' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)') + N']' + CHAR(13) + CHAR(10)
            + N'Event Type: [' + EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(256)') + N']' + CHAR(13) + CHAR(10)
            + N'Login: [' + EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(256)') + N']' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
            + N'Statment:' + CHAR(13) + CHAR(10) + EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')

    EXEC msdb..sp_send_dbmail
          @profile_name = 'DB Mail Profile Name'
        , @recipients = 'person@place.com'
        , @body = @msgBody
GO

If this doesn't provide notification that the EE has been stopped, you may be running into a situation where the event is failing for whatever reason.  At that point review your ERROR logs and see if anything occurred and dig from there.  If you can narrow down a specific error code, you could then configure a SQL Agent Alert to email you if/when that specific error happens again.


How can I deny a user the ability to Alter DB files and options but still allow schema and data modifications?

You can put the users in the db_ddladmin, db_datawriter, and db_datareader roles.  This would prevent them from modifying permissions on the DB but either the db_securityadmin or db_accessadmin roles could be granted as appropriate keeping in mind that it's possible for db_securityadmin to be used for privilege escalation but db_accessadmin may not meet your needs.  As always, test the setup before deploying it to production to make sure it restricts what you want it to while allowing what you want it to.

If the goal is to prevent DB growth make sure autogrow is disabled on these databases.


If it possible for you to use partitioning, I would highly recommend leveraging it in this situation as you know that you're working with periodic data loads which fall into months or years. With partitioning, you could reserve the creation of your indexes on other columns that you may need on things such as InvoiceNo, etc. Let partitioning take care of isolating which month or period you are working with.

If you are fairly sure about the fact that date is integral portion of your join or searches, then you I would associate a "smartkey" for your loading strategy and place a clustered index on that instead.

If you are truly determined to use the date as an index seek, then I would place an non-clustered index on this field as you'll likely have to scan rows in your use-case anyway. Keep in mind creating a clustered index on date and then incurring updates or deletes will fragment your table.


Get rows inserted and updated dynamically in SSIS

It all depends on your SSIS version, but see Integration Services (SSIS) Logging in the official documentation.

Assuming a reasonably modern deployment I can show you an example of SQL 2012 Basic Logging for SSIS packages. It's very simple, as it is the default configuration, and all you need to do is drill down the provided reports by right clicking on your package and selecting the appropriate report.

First confirm your logging level in SSISDB:

Basic reporting will include a final rows written for your destination. It doesn't provide row counts throughout, if you want that level of detail either increase the logging level (will affect performance and storage!) or create your own solution.

You should be able to query all of the log tables and generate your own reports. Microsft just ships a pre-built set of reports that sit atop the basic logging.


SQL Server 2008 R2 service pack steps .....on cluster

I would make a slight modification to the procedure to reduce the number of failovers:

Remove Nodes 3 and 4 from the list of possible owners for each SQL Server Cluster Instance, leaving Nodes 1 and 2 as possible owners to preserve failover capability while doing the rolling upgrade.
Apply the Service Pack and latest Cumulative Update to nodes 3 and 4.  Reboot as necessary.
Make nodes 3 and 4 possible owners for all clustered instances.
Failover the clustered instances to either node 3 or 4.
Remove nodes 1 and 2 from the list of possible owners.
Apply the Service Pack and latest Cumulative Update to nodes 1 and 2.  Reboot as necessary.
Add nodes 1 and 2 back in as possible owners.
Leave the instances on node 3 or 4, or fail them back to node 1, if desired.

Microsoft has documentation providing more details if required.


Foreign key referencing table with composite primary key when I only need 1 column association

The alternative is to create an additional table

CREATE TABLE CurrentVersions (
    Id INT PRIMARY KEY,
    Version INT
)

with appropriate foreign key relations to VersionedData. You can then have the foreign key FK_Pnt_Ver reference CurrentVersions instead.

You'll either need an extra JOIN to fetch the data from VersionedData (I assume it contains additional columns), or alternative store the data you need in CurrentVersions as well. In that case, you don't even need the Version column.

This situation is similar to how Stack Exchange stores posts and revisions. Their database looks very much like the public Data Explorer whose schema is documented here on Meta. The Posts table contains the current version of a post as HTML (Body) and the revisions are stored (as Markdown) in the Text column of the PostHistory table.


PostgreSQL: After restoring data, unable to use Views (permission denied)

To troubleshoot, I would start by looking at the output of SELECT current_user; and SHOW search_path;. Also, the output of \dp termin in psql.

My suspicion here is that you are stumbling over mixed case identifiers. The error message mentions a table

Map

Notice the capital letter M. Are you sure there is no mix-up with another table named map (lower case)?
Are PostgreSQL column names case-sensitive?

(Or, in another schema coming earlier in the search_path?)


Primary key vs. unique index: performance difference with foreign keys?

It has nothing to do with performance as such, but it is a conceptual
 issue.

Use UNIQUE CONSTRAINT to state a fact. Use UNIQUE INDEX when you have an
 index which happens to be unique, for instance because you add the primary
 key to it.

As per the Scenario:

Primary Key

When you want to keep a identifier for each row. So each row can be obtianed by the Key value.(since pk unique and not null)

Unique Key

When you want to keep secondary identifier where already the primary exists in the same table. So each row can be obtained by your PK or UK. Unique will allow null but only once(what is the use to allow single null).

Note:

Both can be either clustered or non-clustered. When you create a constraint you are allowed to choose which type of index you want to use for these keys.

Sources:

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/290619be-b892-4f52-99e0-d8e2ff80aec4

http://bytes.com/topic/sql-server/answers/83999-unique-constraint-vs-unique-index-ms-sql-2000-a


Suppose I have a replication pattern of a single write DB and multiple
  read DB replication slaves - I do not have to have GUID's as my unique
  IDs fields. Is that a correct assumption?

Correct. As Basil Bourque wrote it doesn't matter what you kind of IDs you use if you have a single master source of data.

  If I have multiple DB instances where all are writeable and sync
  between themselves, GUID's in unique ID fields is mandatory. Is that
  also correct?

No, it is not mandatory to use GUID in this case, but it may be convenient.

I assume a quite common scenario: You have N writable databases DB1 - DBn. They can "talk" to each other to exchange data, but connection between them is not 24/7 or not fast enough to be done "in real-time" as the data changes. So, each database accumulates its changes locally and then periodically the changes are transferred to other databases. Since, once in a while all databases have exactly same data, there must be a method to assign some identifier to each row that would be unique not just within one database, but within all databases.

There are many ways to achieve it, GUID is just one.
Another common method is to have ID that consists of two parts: ID of the database plus standard sequence of growing unique numbers.

Another method that I used for two-way sync of two databases: ID effectively consists of two numbers. First number is a standard unique sequence of the local database, second number is a sequence from remote database.


Groups relationships - Detailed or Direct?

First, it's hard to ascertain what you need as I don't know what your data means. What is ShareID and what are the groups for? How do ItemIDs relate to ShareID? Is the data one-to-one or one-to-many? etc...

With that said, I do know for sure that Option 1 does not follow good practice. You don't store different types of data in the same columns. You have to think not only about how complicated your logic in your queries will be, but also what about data changes? It looks like you have the each person in a group in their own row followed by the group in a different row. Data changes will painfully difficult on top of slow because you're not going to have to use very complicated logic.

So option 2 seems to better to me. It doesn't have any blaring issues. Also remember the number of rows does not impact performance in the way you think it does. If you have more rows with good structure so you are using SQL the way it was optimized(dealing with sets not Row by Agonizing Row(RBAR)), then it will actually perform better than less rows in bad structure.

Just remember keep narrow data types, use indexes, and follow good SQL coding practices: always set-based(no cursors or loops), only the columns you need, and no functions on the predicate of the where clause.

I do suggest you read up on data normalization(1NF,2NF,3NF) and I'm sure there are plenty of helpful articles and examples on how to structure your tables that you can find online.


In the first case the filtering by WHERE is done before the grouping so only those with review_channel_id = 3 are aggregated, but in the second case all are aggregated and only those who get review_channel_id = 3 from group by are shown.

What's probably a worse problem - you use GROUP BY incorrectly by selecting columns which are neither grouped by, nor aggregated or functionally dependent on the grouping columns (id, review_channel_id etc.).

To explain that imagine a table of fruit:

kind  | color | price
------+-------+------
apple | red   |     5
apple | green |     7

You can do

select kind, avg(price) from fruit group by kind;
> apple - 6

but if you try

select kind, avg(price), color from fruit group by kind;
> apple - 6 - ??(red/green)

The column can contain only one value and you gave it no instructions to decide which one so it picks one "randomly" (= it depends on specific implementation and the plan used to execute the query - implementation details can change between versions and the plan can change between two executions) so you may get different results from the same query and no one of them is "right".

You are allowed to do that in MySQL because it cannot detect functional dependencies before version 5.7.5 so it lets you write the code but trusts you to check the dependencies yourself. You can make it not to trust you by enabling ONLY_FULL_GROUP_BY - with that your query would not be allowed at all.

In 5.7 the check was implemented which can decide if the column is functionally dependent on the grouping columns -> if for each group there is at most one possible value for that column. Imagine another table fruit_shape, where "apple" is "round" and "pear" is "pear-shaped", if you join such a table to the fruits, each "apple" will always only be "round" by definition so there is no need to pick from different values as with red/green => the shape is functionally dependent on the kind.

If you want to learn more, try this article and its follow-up for 5.7

Now for your case:

do not use views if you can avoid it - there are some performance hits because the planner cannot work freely with the views as it can in the case you use the full query (and GROUP BY in the view definition limits the options even more)
if you have to use the view for any reason, add the review_channel_id to the GROUP BY list - it will compute the results for all (business, channel) pairs (and for each month of those) and you can filter by WHERE in the query later - but if you used the full query in the first time, it would compute only those pairs you ask for
do not select the id (=review_id) if there are more than one of them for any (business, channel, year, month) tuple - it will not give any meaningful value
the same with the review date probably, select year() and month() instead (or make the date showing the first day of a given month, but I would guess even 5.7 will have a hard time to detect that dependency even when it exists)


I would draw a text backup and search it with vim or any tool of your choice.

As far as plpgsql functions are concerned (there is one behind every trigger), you can query the system catalog pg_proc:

SELECT *
FROM   pg_catalog.pg_proc
WHERE  prosrc ILIKE '%dblink%';


moving database from old disk to new disk

You should be able to create a new database with the same name on the new system, shut down the service, replace the new (empty) files for your database with the old ones and finally start the service again. See http://www.kimvette.net/linux/recover_and_mount_mysql_database_files_after_recovery for more info.


How to find what SQL Jobs are using a specific account as Proxy?

The servers I was looking at had only few credentials. While they had several jobs only a handful were using “Run As” Proxy credentials.  This is the solution I used.

First query shows the account (credential identity) linked to the proxy
Second Query shows what Job and Step is using a Proxy

    -- Search Credentials (shows account for Name)

    use msdb
    select *
    from sys.credentials

    --Search Jobs where there is a 'Run As' proxy and get the name of that proxy

    use msdb

    select  sysjobsteps.job_id
    , sysjobs.name as 'JobName'
    , sysjobsteps.step_id
    , sysjobsteps.step_name
    , sysjobsteps.subsystem
    , sysjobsteps.last_run_date
    , sysjobsteps.proxy_id
    --, sysjobsteps.step_uid
    , sysproxies.name as 'ProxyName'

    from sysjobsteps
    left join dbo.sysproxies
     on sysjobsteps.proxy_id = sysproxies.proxy_id
    left join dbo.sysjobs
     on sysjobsteps.job_id = sysjobs.job_id

    where sysjobsteps.proxy_id > 0

I tried a couple of ways of joining sys.credentials to dbo.sysproxies. If there was more than one credential it did not work well. Separate quires met my needs so I did not dwell on the join.


Two FOREIGN KEY constraints with SET NULL targeting the same PRIMARY table

It would seem you have cascading deletes on and you'll end up deleting someone who could theoretically be referenced in another group as a treasurer or secretary.

The group table is violating the 1st normal form. This is akin to having address1, address2 in a table.

You need a third table GroupPersonRole that holds groupid, the person id and the roleid.

Define your person in one table. Define your group in another table. And define your role in another table. Then in a fourth, say GroupPersonRole, you can combine them and draw your FKs as needed.

What if someone wants to add more group roles? You don't want to be modifying schema every time someone gets a new idea.


How to translate high level entity relationship into schema

The key to understand this case is at the ninth page from Doc-A:

  "product" and "customer" are entity types, and "shipped to" is a
  relationship... the verb "ship" is then converted to a gerund... in
  other words, the relationship... has been converted into the entity
  type "shipping"

Hence, the final third form will have "shipped to" converted to an entity by itself, "shipping".

This is necessary because "Shipped to" is a MANY-TO-MANY relationship (although to myself Chen's text is not clear on this).

But, your example does not relate - the relationship "Identified by" is an ONE-TO-MANY (a card belongs to ONE customer / a customer have ONE OR MORE cards issued).

Hence, the model would have the relationship between "Bank" and "Bank card" identified by TWO verbs.

Or, making the model stronger, to register several authentication events, one may turn "Authenticated by" into a entity by itself, "Authenticating" (to register event date, data shared, etc.).


Strange MySQL database that I can't select

This is nothing but a folder/file in database directory.

The default location for data directory is /var/lib/mysql on ubuntu.Go to that location(datadir) and find what is there in that folder if it not your database please move that to some another place.

If you need to find the location of data directory issue command on MySQL shell

SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /db-data/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

Which will give you the location of data directory


SQL server DB status runnable, sleeping, suspended, running and background meanings

The status values are documented with sp_who:

  The possible values are:

  Dormant - SQL Server is resetting the session.

  Running - The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run
  multiple batches. For more information, see Using Multiple Active
  Result Sets (MARS).

  Background - The session is running a background task, such as deadlock detection.

  Rollback - The session has a transaction rollback in the process.

  Pending - The session is waiting for a worker thread to become available.

  Runnable - The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.

  Spinloop - The session's task is waiting for a spinlock to become free.

  Suspended - The session is waiting for an event, such as I/O, to complete.


join two tables, include one row from the parent table for each primary key

Perhaps I'm missing something, but I don't understand why this is not a simple UNION.

--Demo setup
Declare @Parent table (ID int, Name varchar(30))
insert into @Parent(id, Name) values(0,'FOO'),(1,'BAR'),(2,'ASD')
Declare @Children table (ID int, Name varchar(30),ParentId int)
insert into @Children(id, Name,ParentId) values(10,'bas',0),(11,'zap',0),(12,'zam',1),(13,'maz',1)

--The solution
;with UnionedData as
(
SELECT ID AS ParentId
    ,' ' AS ChildID
    ,NAME
    ,'False' AS isChild
FROM @Parent

UNION ALL

SELECT p.Id
    ,convert(VARCHAR(5), c.id) AS ChildID
    ,c.NAME
    ,'True' AS IsChild
FROM @Parent p
JOIN @Children c
    ON c.ParentId = p.id
)
SELECT *
FROM UnionedData
ORDER BY ParentId
    ,ChildID

| ParentId | ChildID | NAME | isChild |
|----------|---------|------|---------|
| 0        |         | FOO  | False   |
| 0        | 10      | bas  | True    |
| 0        | 11      | zap  | True    |
| 1        |         | BAR  | False   |
| 1        | 12      | zam  | True    |
| 1        | 13      | maz  | True    |
| 2        |         | ASD  | False   |


Select only databases with a certain table name and specific columns

I've set up this sample on fiddle.uk just to show you one option.

CREATE TABLE logsys_a(id int, v1 int, v2 int, v3 int, v4 int, v5 int);
CREATE TABLE logsys_b(id int, v1 int, v2 int,         v4 int, v5 int);
CREATE TABLE logsys_c(id int, v1 int, v2 int, v3 int, v4 int, v5 int);
GO

Instead of use USE [?] you can use TABLE_CATALOG to filter on which database are you searching for. In my example I've used LIKE 'fiddle%' because it changes every time you executes the query.
You can check if EXISTS a table with all the required columns using HAVING COUNT() = x, as you can see in this case it only returns logsys_a and logsys_b.

As far as I don't know if you need all table columns I haven't used again COLUMN_NAME IN ('v1','v2','v3','v4','v5') on the main query.

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM   INFORMATION_SCHEMA.COLUMNS T1
WHERE  TABLE_CATALOG LIKE 'fiddle%'
AND    TABLE_NAME LIKE '%logsys%'
AND    EXISTS (SELECT   T2.TABLE_SCHEMA, T1.TABLE_NAME
               FROM     INFORMATION_SCHEMA.COLUMNS T2
               WHERE    TABLE_CATALOG LIKE 'fiddle%'
               AND      T2.TABLE_SCHEMA = T1.TABLE_SCHEMA
               AND      T2.TABLE_NAME = T1.TABLE_NAME
               AND      T2.COLUMN_NAME IN ('v1','v2','v3','v4','v5')
               GROUP BY T2.TABLE_SCHEMA, T2.TABLE_NAME
               HAVING COUNT(*) = 5);

GO

TABLE_CATALOG                           | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME
:-------------------------------------- | :----------- | :--------- | :----------
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | id
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v1
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v2
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v3
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v4
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v5
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | id
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v1
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v2
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v3
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v4
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v5

dbfiddle here


Want to get vat-amounts in invoice table

It should be fairly simple then:

SELECT
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
    , SUM([HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100) AS fldVatTotalAmount
FROM [HJ-Admin].tblVatRates
LEFT OUTER JOIN [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
GROUP BY
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate

or with a total rows as well:

SELECT
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
    , SUM([HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100) AS fldVatTotalAmount
FROM [HJ-Admin].tblVatRates
LEFT OUTER JOIN [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
GROUP BY
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
WITH ROLLUP

Or with the grouping that you are after (only an overall total row, no totals per grouping level):

SELECT
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
    , SUM([HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100) AS fldVatTotalAmount
FROM [HJ-Admin].tblVatRates
LEFT OUTER JOIN [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
GROUP BY GROUPING SETS(
        ([HJ-Admin].tblVatRates.fldVatID, [HJ-Admin].tblVatRates.fldVatRate)
        , ()
    )

With InvoiceID:

SELECT
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
    , SUM([HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100) AS fldVatTotalAmount
FROM [HJ-Admin].tblVatRates
LEFT OUTER JOIN [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
WHERE [HJ-Admin].tblInvoiceDetail.fldInvoiceID = @intInvoiceID
GROUP BY GROUPING SETS(
            ([HJ-Admin].tblVatRates.fldVatID, [HJ-Admin].tblVatRates.fldVatRate)
            , ()
        )

Try to put the InvoiceID into the where, will be more performant.

If you want to also include all non matching rows from the tblVatRate table:

WITH Inv AS (
    SELECT
        r.fldVatID
        , r.fldVatRate
        , SUM(i.fldLineTotal * r.fldVatRate / 100) AS fldVatTotalAmount
    FROM [HJ-Admin].tblVatRates r
    INNER JOIN [HJ-Admin].tblInvoiceDetail i ON r.fldVatID = i.fldVatRateID
    WHERE i.fldInvoiceID = @intInvoiceID
    GROUP BY GROUPING SETS(
                (r.fldVatID, r.fldVatRate)
                , ()
            )
)
SELECT ISNULL(i.fldVatID,@intInvoiceID) AS 'fldVatRateID', r.fldVatRate, SUM(ISNULL(i.fldVatTotalAmount,0)) AS 'fldTotalVat'
FROM [HJ-Admin].tblVatRates r
LEFT OUTER JOIN Inv i ON r.fldVatRate = i.fldVatRate
GROUP BY GROUPING SETS(
                (r.fldVatID, r.fldVatRate, i.fldVatID)
                , ()
            )


Can I enable SQLCMD mode from inside a script?

As has been said, there isn't a way.
However, one thing we do is to include a quick check in our script header, to determine whether SQLCMD mode is on (and terminate the script if not):
:setvar DatabaseName "MyDatabase"
GO
IF ('$(DatabaseName)' = '$' + '(DatabaseName)')
    RAISERROR ('This script must be run in SQLCMD mode. Disconnecting.', 20, 1) WITH LOG
GO
-- The below is only run if SQLCMD is on, or the user lacks permission to raise fatal errors
IF @@ERROR != 0
    SET NOEXEC ON
GO

PRINT 'You will only see this when SQLCMD mode is on'
-- Rest of script goes here

GO
SET NOEXEC OFF
GO

There is a reason Severity 20 is used, it tends to kill the connection immediately, preventing any more script code from running, even if there are GO batch terminators later in the script.

Severities 20-24:
Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect.
Error messages in this range can affect all of the processes accessing data in the same database and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 24 are written to the error log.


Very Simple "SELECT * FROM" Times Out (InnoDB)

First of all, here are a few things you can try to get more info about the query in your case:

After starting the query, you can check its status by running

SHOW FULL PROCESSLIST

Also, you can try running the query on a smaller set of rows by adding another where predicate (covered by an index), for example:

SELECT * FROM image WHERE id < 500 and LOWER(image_name) = 'img_0209.jpg'

As for why the query may be slow, I believe that what happens with the third query

SELECT * FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

is that since the index can't be used, the engine does a full table scan and reads all the data for each row (including full BLOB data) and only then tries to filter them. So it probably reads a few GBs of data with that query.

Looking at the amount of data read from disc during the query may give some hints. You can try using the innodb_data_read service status variable:

SET @read_metric = 'innodb_data_read';
SET @read_before = (
  SELECT variable_value
  FROM information_schema.session_status
  WHERE variable_name = @read_metric
);

SELECT * FROM image WHERE id < 500 and LOWER(image_name) = 'img_0209.jpg';

SET @read_after = (
  SELECT variable_value
  FROM information_schema.session_status
  WHERE variable_name = @read_metric
);

SELECT (@read_after - @read_before) / 1024 / 1024;

It should be similar to the size of data in BLOBs:

SELECT sum(length(image_data)) / 1024 / 1024 FROM image WHERE id < 500;

To speed up the query you need to avoid accesing BLOBs as much as possible. A few suggestions:

in your specific case changing the collation to an ignore-case one should help, so the image_name index may be used to find the single row you need
try using a self join to separate image_name and BLOBs access

SELECT image_data.image_data
FROM image
JOIN image as image_data on image_data.id = image.id
WHERE LOWER(image.image_name) = 'img_0209.jpg';

as described in the Optimizing for BLOB Types manual, move the BLOBs to a separate table with just two colums: id and data


Actual moving of tablespaces in Postgres, not copy-delete

No, there is no such way.

PostgreSQL does not know about the underlying storage, so it doesn't know when to perform a hard link instead.

Besides, it makes no sense to have two tablespaces on the same file system, and with different file systems movement means copy + unlink anyway.


Yes.

You need the USE PLAN hint.

In which you supply the XML from the first plan.

SELECT *
FROM T
OPTION (USE PLAN N'<?xml version="1.0" encoding="utf-16"?> ....')

Whilst it doesn't guarantee that the plan will be exactly the same (e.g. compute scalar operators can move around for example) it will likely be pretty close.


What's a more natural column name for Tables whose name share a column name?

Personally, I usually like my column names to be meaningful, as it makes it easier to follow in queries. I can see Scotts Hodgin's point, certainly, but you could end up with a query that looks like:

SELECT cust.full_name
      ,stat.name
      ,type.name
      ,clhd.name
      ,src.name
....

Of course, good table names or aliases can ameliorate my complaint (even in my example, you can take a stab at what these things might be). However, (again, for me) having to use 20 different columns with the same name in a query can make it easier to use the wrong one in the wrong place, and can make it harder to catch. I'd rather see:

SELECT cust.full_name
      ,stat.status
      ,type.cust_type
      ,clhd.reason
      ,src.source
....

Again - I'm presenting my perspective - this is really something that is a matter of personal taste.

It is a good idea to ask a team to be consistent. If you're the team lead, you can either dictate (your preference wins), take a vote (if most people want one option, then compliance with that option should be better), or ignore it (leading to the best chance of confusion for all).


Is XFS still the best choice for mongodb?

The provenance of information on the MongoDB production notes isn't apparent at the moment, but WiredTiger and the recommendation to use XFS was definitely added much later than the Linux kernel details. The production notes share collective experience from known issues, but are typically recommendations rather than strict guidance. Most notes are added at the time a widespread problem is observed, but circumstances could certainly change.

  It tells that for the WiredTiger storage engine, XFS is strongly recommended against ext3 or ext4.

The specific recommendation against ext4 is based on observed stalls during WiredTiger checkpoints as reported by a number of production users. Per SERVER-18314 (May 2015), there is a likelihood of stalls when ext4 journaling coincides with WiredTiger checkpoints. This may not be an issue for all workloads, but is a significant enough caveat that a startup warning was added in MongoDB 3.4 (released Nov 2016) for deployments using ext4 with WiredTiger. There have been no similar reports with XFS, and it has been observed to generally perform better with MongoDB.

There are other available filesystems on Linux (ZFS, btrfs, ...) but these are currently not widely used in production (as compared to ext4 and XFS) so XFS is the recommended filesystem based on testing and experience.

  Nowdays we are using Linux kernel 4. The 2.6 series of kernels date back to 2003. That is incredibly old.

While most distributions have moved on from the 2.6 kernel, there are some Enterprise stalwarts like RHEL6 that will not upgrade the default kernel during their release support lifecycle. Redhat does cherrypick and backport fixes with their own kernel subversion, but will remain on the original kernel base from the time of release. RHEL has a 10-year support policy followed by 4 years extended lifecycle support, so old versions tend to remain in the field much longer than you might expect. RHEL6 moves into extended support in 2020, so there are still new deployments using the 2.6 kernel in 2017. RHEL7 uses the 3.10 Linux kernel and won't reach extended support until 2024.


Try this:

select
    XRows.query('.')
FROM
    @xml.nodes('/row') AS XTbl(XRows)
WHERE
    XRows.value('(Id)[1]', 'int') = 1

This will return the entire <row> XML element as XML


Determine theoretical max connections for MariaDB

Every formula I have found is wrong in some respect.  Almost every computer I have encountered has "overcommitted" RAM; that is, the formula would say that too much RAM could be used.
Their list is missing two important settings:  max_heap_table_size and tmp_table_size.  The min of those two is what is used for internal temp tables in complex SELECTs.  And there could be multiple uses in a single query!  I recommend limiting each to 1% of RAM.
Overall...

Leave most VARIABLES at the defaults.
The 1% mentioned above
use InnoDB, not MyISAM.
Set key_buffer_size to just 20M.
Set innodb_buffer_pool_size to about 70% of available RAM if you have more than 4GB of RAM.  (A lower percentage for tiny machines.)  This is the first thing to reach for if swapping occurs.
When you find problems (swapping, high cpu, high I/O, etc), ask for help.  It will usually be solved by better INDEXing and/or reformulating queries, not by tuning.

Ignore the formulas like what your question is posing.
As for "too many connections", there are two main causes and tuning MySQL/MariaDB is not the cure.

The queries are running so slowly that they need help -- tuning queries is a solution here.
The clients are flooding the database.  Some web servers think that they can efficiently run hundreds or thousands of threads.  But, in reality, they stumble over themselves and cause the database to also stumble over itself.  Throttle them.

On this last bullet item, think of a grocery store where so many shoppers are in there that they can't even push their carts to the next aisle, much less ever get finished.
Put another way, if you let too many connections in, throughput stalls (hits some limit) and latency suffers badly.
Things to watch for:

Swapping means things are tuned too high -- discussed above
100% CPU or I/O -- Fine and fix the queries.  For help in finding them:  http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
Poor latency (queries taking "too long") -- See the other two items.


I get a query error 'unknown column' while the column exists

You are using aliases for those tables. So, specify the alias when selecting the column.

CREATE VIEW survey_codes_complete_overview AS
SELECT *,
a.supplier_id AS a_id,
b.supplier_id AS b_id
FROM wp_jpnx_survey_code AS a
LEFT JOIN wp_jpnx_survey_supplier_info AS b
ON a.supplier_id = b.supplier_id


Do I need to convert varchar(32) and varcha(64) to update from MySQL 5.7 to 8.0

No you can change the character set, because you don't come to the limit that is explained in the article.

The InnoDB storage engine has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 character.

So if your utf8 column varchar has a size of more than 191 characters, you will get an error when converting, because tf8mb4 needs 4 bytes per characters and that will exceed the limit.
But as you only have 32 and 64 characters you are wide blow the limit of 191


Store a revenue based dataset in a relational database

Why do you believe that the second design is not efficient? It would be the design that I would recommend to you. It allows the data set to be scaled to add more subjects and more years without needing to adjust the schema.

To extend the normalization, place the subjects within their own table and reference that from your main table. This will reduce duplicate names in your main table and also reduce the space required to store your data.

Depending on whether you can or not, you could also reduce your table count down to 3 by adding a column to your main tables called website, then create a new table to hold the website list and reference this again from your main table.

You would then end up with a structure similar to this:

create table "websites"
(
  id int not null primary key,
  name varcher(64) not null unique
);
create table "subjects"
(
  id int not null primary key,
  name varcher(100) not null unique
);
create table "revenue"
(
  id int not null primary key,
  website int not null,
  subject int not null,
  year smallint not null,
  value int not null,
  constraint uk_revenue unique (website, subject, year),
  constraint fk_revenue_website foreign key (website) references website (id),
  constraint fk_revenue_subject foreign key (subject) references subject (id)
);

This creates a table for the website, a table for subjects and one for the revenue. The revenue table contains references to the former tables and also creates a unique constraint across the combination of the website, subject and year to prevent duplicate entries.

Naturally, normalization makes query definitions larger (as you now need to join to other tables) but this is trivial really and the ability to scale out to multiple websites, subjects and years easily is in my opinion worth the extra effort of writing a longer query.

After time, should the data set grow to a point whereby querying it becomes very slow, you can either archive off data that you don't need anymore or warehouse it in a denormalized fashion.


How to see amount of reusable space for a table

check out pg_freespacemap module: http://www.postgresql.org/docs/9.3/static/pgfreespacemap.html


Min no. of replicas to set up SQL Server Always ON over a Failover Cluster on a Windows Cluster

If by AlwaysOn you mean "Availability Groups" (which is typically the case), then you can most definitely use your failover cluster instance as a replica.

For this to be an Availability Groups design that is actually useful in the sense of AGs, then you would most likely want another server to host another replica in the Availability Group (for whatever it is that you're looking for:  HA, DR, reporting, etc.).  All of the servers that are going to be participating in the Availability Group need to reside in the same WSFC.

  There is no point in having AO when only 1 instance of SQL is running at a given time

The replicas in an Availability Group are individual SQL Server instances (whether it be a FCI or standalone instance), so each of the replicas are "running" in a normal condition.

  what would I need to set up AO?

Technically speaking?  You would just need another instance of SQL Server to have as an additional replica for the AG.

Realistically speaking?  We can't answer that right now with the current state of the question.  We'd need to know exactly what you want your Availability Group for.


You usually don't want to disable parallelism as that will also disable it for admin tasks.  Your best bet is to fix the queries that are causing the parallelism through adding or fixing indexes or through making full on schema changes.

Based on your updated questions...

Some people will change MAXDOP to 1 for vendor built applications because they can't control the database or schema and they don't want a single query to take over the entire system.

Personally I always keep MAXDOP at 0 except for some rare cases.

Parallelism is caused by a single operation within an execution plan having an execution cost which goes over a pre-set setting (the cost threshold for parallelism setting).  When this happens the SQL Server will kick in parallelism so that it can multi-thread the request in an attempt to speed up the process.  The default value for the cost threshold for parallelism is 5.  In many OLTP platforms you'll want to raise that up to 30 or 40 so that parallelism only kicks in for the really expensive queries.


SQL Server 2014 SP1 installation iso file question

What files do I need to install SQL 2014 SP1 CU6?

You would need

SQL Server 2014 RTM setup
SQL Server 2014 SP1 Setup. This can be downloaded from here
You would need SQL Server 2014 Sp1 CU6 setup. This can be downloaded from here.

Note: Quite a few times you would find SP1 embedded in SQL Server 2014 installation files in that case you dont need to download Sp1 you just need to download CU6. When SP1 is embedded it wuld be mentioned in product software like SQL Server 2014 Sp1 Developer edition. If you have option go for this one.

  Is there a slipstream SQL 2014 SP1 ISO file available for download or do I install SQL 2014 RTM then SQL 2014 SP1 and then SP1 CU6.Which RTM CU6 file should be used?

There is no Slipsteam file available for download its a way to install updates with SQL Server. Yes you can install SQL Server 2014, SP1 and Sp1 CU6 all together

Microsoft blogs already has information about Create a merged (slipstream) drop containing SQL Server 2008, Server Pack 1 and a Cumulative Update (CU) based on Server Pack 1.

I also suggest you read SQL Server 2008 Slipstreaming FAQ

Boris Hristov has also written good article see This link. This uses command line to install SQL Server and the updates.

Here is what we should do to slipstream SQL Server 2012 and 2014:

Download SQL Server 2012 or SQL Server 2014 media
Download all the needed SPs, CUs and Hotfixes that you want to
“embed”
Put all of the .exe files in one directory
Launch the setup.exe from CMD by issuing the command:

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource=”path_to_the_directroy_where_the_hotfixes_are”

Place all the Sp and CU on one location after extraction and give the location in /UpdateSource

For example if location is C:\SQLServerFixes the command would be

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource=”C:\SQLServerFixes”

  Is there a separate download required for SSMS 2014 etc.

No, while installing the update you can select the features you want to update.

EDIT:

  Do I have to download a different file for developer edition Or download EE and provide developer license information.

Developer edition is same as enterprise in functionality but you can ONLY use developer edition for development. Starting from 31 March 2016 Dev edition is free to download for Visual studio DEV essential members. So if you are grab it. Their is no point in going for EE edition.

  SQL 2014 RTM download version without CU as SP1 would cover it? What filename / size would this be - if this information is easily available.

You have to just download as I have said in starting


Data is stored on shared storage. Nodes do not store any data, they just process it.
Yes.
Yes.


Why an object became invalid in oracle databse.

In short, no.

You can however use the DBA_DEPENDENCIES view to check which objects the views and functions depend on, then query DBA_OBJECTS to check the last_ddl_time for each of these dependant objects - that should give you an indication of which objects have changed and caused the view to be invalid as a result.

It's quite normal for objects to become invalid in development databases when columns are added to tables etc.