Order result by count of common array elements

With tools of the basic Postgres installation only, you might unnest() and count in a LATERAL subquery:

SELECT i.name, i.user_ids_who_like, x.ct
FROM   items i
     , LATERAL (
   SELECT count(*) AS ct
   FROM   unnest(i.user_ids_who_like) uid
   WHERE  uid = ANY('{3,4,11}'::int[])
   ) x
ORDER  BY x.ct DESC;  -- add PK as tiebreaker for stable sort order

We don't need a LEFT JOIN to preserve rows without match because count() always returns a row - 0 for "no match".

intarray

Assuming integer arrays without NULL values or duplicates, the intersection operator & of the intarray module would be much simpler:

SELECT name, user_ids_who_like
     , array_length(user_ids_who_like & '{3,4,11}', 1) AS ct
FROM   items
ORDER  BY 3 DESC NULLS LAST;

I added NULLS LAST to sort empty arrays last - after the reminder from your later question:

How to get 0 as array_length() result when there are no elements

Install intarray once per database for this.

Use the overlap opertaor && in the WHERE clause to rule out rows without any overlap:

SELECT ...
FROM   ...
WHERE user_ids_who_like && '{3,4,11}'
ORDER  BY ...

Why? Per documentation:

  intarray provides index support for the &&, @>, <@, and @@ operators,
  as well as regular array equality.

Applies to standard array operators in a similar fashion. Details:

Can PostgreSQL index array columns?

Alternatively and more radically, a normalized schema with a separate table instead of the array column user_ids_who_like would occupy more disk space, but offer simple solutions with plain btree indexes for these problems.


in-place upgrade native SSRS without the SQL Server portion

Because of licensing concerns I cannot have SQL Server 2016 running but I still need to upgrade the SSRS to 2016.

They require the same license.  So if you can't run SQL Server 2016, you can't run SSRS 2016.


To elaborate on ypercube's correct comment, the problem is not that there is fragmentation on the VARCHAR, but in rows that contain variable length data.

If I were to have a table with these values:

+---+-------+
| i | v     |
+---+-------+
| 1 | some  |
| 2 | small |
| 3 | text  |
+---+-------+

And then issue:

UPDATE my_table SET v = 'dinosaur' WHERE i = 2

Then, depending on the storage engine, I might find that there's just no room for the word 'dinosaur' within the existing row location, since rows were written sequentially and compactly to disk. That might mean I would need to change the location of row #2, or place my 'dinosaur' text on an external location, or what have you.

On the other hand, if I were to issue:

UPDATE my_table SET v = 'a'

There would now be a lot of wasted space; I might be interested in reclaiming that space.

This is the fragmentation problem within rows.


You can try the following;

select inversor_insert(q'[INSERT INTO clien(column1,column2) values('value1','value2')]')

The q'[  ]' construct simply means that Oracle will treat anything within the brackets as it is written. you don't need to worry about quoting the quotes.


Filter first then join or join first then filter?

Build up the query and see.
The query optimizer is often smart enough to filter early.
SQL is logical - in the where does not mean it will process last.

Clearly you want indexes on join and filter.

When you get to 5 or more joins the optimizer will often get defensive and go into a loop join.
Do I have a citation - no.  It is an observation.

When it gets to 5 or more pulling the conditions into the join can help the optimizer.
Do I have a citation - no.  It is an observation.

select *
from tableA
join tableB
      on tableA.fkB = tableB.id
     and tableA.date1 >= @date1start
     and tableA.date1 <  @date1end
join tableC
      on tableA.fkC = tableC.id
     and tableC.filter1 = 'do me early'

Build it up one join at a time and see when it goes stupid.
Optimize one join at at time.

If you are going to materialize then put a pk on the #temp.
Start with where you think you are going to get the most bang.

OR condition in where / join are the most trouble and often lead to a loop join.
Those should be the first to materialize.

You can force hash join but that is a slippery slope that can go bad.

It seems kind of odd all tables have a date dimension, you would typically have some lookup type tables with more static type data.

If you don't need output from the table then a where exists may preform better.


ulimit can be set in the same terminal by
ulimit -n 10000;

You can change the value in /etc/security/limit.conf file by
nofiles to 10000 #line

the maximum no of files can be opened in any linux/unix system is 64000. be sure of this no. If your restoring of database size is huge then try to make them smaller pieces like create the database and then try to restore one collection or few collections at a time. If you have memory limit like 4/8 GB of RAM then it is better to restore collection by collection instead of whole database.

As you are trying mongorestore from local system I believe this is not a production system and not a serious business, if my assumption is correct please try to restore few collections at a time and see if you still face the same issue.

Thanks @wylliam-judd for sharing the solution; I didn't face this issue as I used linux as a remote host and accessed from putty and in the same window; anyway I'm adding your solution to help others

The solution was to run ulimit -n 64000 in the console running mongod rather than in the console running mongorestore. Though I did run it in both consoles to be safe, and I can't be sure whether the ulimit in the mongorestore console was necessary or not. I also attempted many other solutions in these months, and I can't be entirely sure that none of them had an impact, but the difference that finally mattered was running ulimit in the console running mongod.


MySQL 100+ instances on one Windows server

To reduce the RAM used by each instance from ~450MB to 17MB this setting was required:

performance_schema=off

To fix the

  InnoDB: Cannot allocate memory for the buffer pool

...the setting innodb_open_files=300 had to be changed to innodb_open_files=3000.


It might be worth posting the table definition from your other question for clarity.

The composite index is doing a few things for you:

As you know, enforcing uniqueness on (sensor_id, timestamp); I'm unsure whether this is an important data integrity constraint.
Allowing queries that filter on both columns to look up matching rows by using a single index. MySQL can answer some queries (equality conditions on multiple columns are the ones I know about) by merging two indexes, but this tends to be significantly slower compared to using a single composite index.
The index can also be used to search for values in a left-based subset of the composite index, but not a right-based subset. So in this case it could help a query that filters on sensor_id values or sensor_id and timestamp values, but not timestamp values alone.

There are a number of caveats to this, so it's good idea to look at the EXPLAIN output for your queries and verify what indexes they're using. Keep also in mind that indexes can support  the read part of UPDATE and DELETE queries, as well as JOINs, GROUP BY, ORDER BY, and other operations I'm neglecting.

An example of a scenario where the composite index is unnecessary would be if you don't care about the uniqueness constraint and all your queries filter on timestamp or sensor_id, but not both.

The single-column index on sensor_id is actually redundant since the composite index on (sensor_id, timestamp) can be used by the same queries, but still you might find that some queries perform faster when doing scans on the single-column index compared to using a composite index with a wider key. The difference might not be enough to matter, though, and some testing will probably be required to find out.

In addition to looking at the EXPLAIN output for your queries, tools such as pt-index-usage from the Percona toolkit or the table INFORMATION_SCHEMA.INDEX_STATISTICS if you're running Percona Server or MariaDB can help you assess what indexes are actually being used.

References:

The Optimization That (Often) Isn’t: Index Merge Intersection

Practical MySQL indexing guidelines


Sync mysql database between 2 geo locations (datacenters)

You have a 3-node Percona XtraDB Cluster now?  And all 3 nodes are in the same DataCenter?  And you want to use a second DC for HA?

Perhaps the 'common' way is to set up asynchronous replication (the old Master-Slave) between one of the nodes in the original DCs and a node of a new 3-node PXC cluster in the new DC.

Another approach is to continue to use a single PXC cluster --

4 nodes, 2 in each datacenter.
"weight" the nodes so that loss of one DC will let the other DC continue to run.  (Caveat:  That works one one direction; for full protection from DC failure, you need 3 DCs, with at least one node in each.)
Tweak some of the tunables due to the delays between data centers.
Set up some sort of Proxy to assist with the automatic routing of queries.  Such can probably coexist on the PXC nodes.

(Sorry, there are still more details to ferret out; this is only an overview.)


I did this with a project recently, only I had even fewer settings to store. I opted for using a two column table, with each setting being it's own row.

I think there are pluses and minuses to each, but if you're dealing with such a small set of configuration options, there is not going to be a huge performance hit. To put into perspective, phpBB's config table has almost 270 rows of config options.


Sql Server Fill Factor - Restart sql server services

Yes, it is clearly stated here which options require a restart: https://msdn.microsoft.com/en-us/library/ms189631.aspx


How can I reduce the Initial Size of a LOG file?

Does anyone know how I can redefine the Log file with an Init size of 200 mb?

You have to use ALTER DATABASE [YOUR_DB_NAME] ... MODIFY FILE ... SIZE

ALTER DATABASE [your_db_name]
SET RECOVERY SIMPLE;

CHECKPOINT;

-- shrink the log file to 200MB
DBCC SHRINKFILE (your_db_name_log,200);

--- define the initial size to 200MB as well.. CHANGE it as per your needs!
ALTER DATABASE [your_db_name]
MODIFY FILE (NAME=your_db_name_LOGICAL_log,SIZE=200MB,MAXSIZE=UNLIMITED,FILEGROWTH=100MB);

--Optional if you want the database in full recovery mode
--for point in time recovery going forward
ALTER DATABASE [your_db_name]
SET RECOVERY FULL;
--- Take full backup to establish log chain
-- take log backups to help truncate the log file and keep it manageable

Suggestions:

Initial size of 200MB is very less. Depending on how busy your system is and how frequent you take log backups (if your db is in full recovery), I would suggest you to look into making an initial size and autogrowth more sensible.
Its always a good idea to enable Instant file initialization to help for data file autogrowth events (as well as database creation and restoring database). Refer Database defragmentation and autogrowth settings answer for more details.


Why my code only return 1 row? Leetcode#626

You are using an agregate function which causes the result to collapse to single row.
Possible solution:
SELECT
(CASE WHEN mod(s.id,2)=0 THEN s.id-1
WHEN mod(s.id,2)=1 AND s.id != maxid.maxid THEN s.id+1
END) AS id,
s.student
FROM seat s
join (select max(s.id) as maxId from seat) as maxId on 1=1
ORDER BY id

You are using it in the SELECT, not CONDITIONAL (WHERE).
Now, the question you ask is a good one and i would have to refresh my knowledge of inner workings of SQL.
I think its because the moment it hits MAX, its collapses the result set, as it doesnt "see" a need to return all rows.
Simply said, the query processor doesnt care if its a condition in case statement or value to be returned. It's there and thats that.
Aggregation function without group by is a mysql thing, and not SQL standard, so the behaviour is specific to MySQL.


3 Simple Queries SQL Student Question, Query Errors in Microsoft Access

the first is simple access  has no !=
SELECT TripName AS Ausdr1
FROM Trip
WHERE ([State])<>'NH';

the form of the secnd is wrong and you should start to use proper JOINs a comma for tables means always a cROSS JoiN, which can be useful, but nit in this case
also strings must always be in quotes
SELECT TripName , MaxGrpSize
FROM Trip, TripGuides
WHERE ((([Type])='Hiking') AND (([GuideNum])='BR01'));

the last  us simple bith tables have the column ´CustomerNum´, so you have to tell access which one you wantg, but i guess it is a joining column.
SELECT DISTINCT  LastName, FirstName, [Customer].CustomerNum, ReservationID
FROM Customer, Reservation
WHERE TripDate = '2018-12';

what i mean by proper joining:
SELECT DISTINCT  LastName, FirstName, c.CustomerNum, ReservationID
FROM Customer C INNER JOIN Reservation r ON c.CustomerNum = r.CustomerNum
WHERE TripDate = '2018-12';

I don#t know ou complete tables , so i am only guessing


Open "SQL Server Configuration manager"=>set "SQL Server" logon as=>Local System.


PostgreSQL queries from external IP causing high CPU load. Have I been hacked?

After using clamscan to investigate further it found another backdoor installed.

/var/lib/postgresql/9.6/main/base/12405/2613: Win.Trojan.MSShellcode-6360729-0 FOUND

I also found out that PostgreSQL 9.6 has a lot of vulnerabilties, but apt-get wouldn't find a newer version than 9.6. To upgrade to Version 10 you'll need to add

 deb http://apt.postgresql.org/pub/repos/apt/ sid-pgdg-testing main 10

to your sources in order to upgrade via

 apt-get install postgresql-10


Can I query data_space names when not in context of the database for multiple databases?

Sorry. Afraid not. you have to query sys.filegroups and/or sys.data_spaces in each database.


Linked Server - Double Hop - Kerberos - TCP

Verify that both SQL Servers can use Kerberos

connect from your laptop to SQLSERVER1 using windows auth.
Run this query: select auth_scheme from sys.dm_exec_connections where session_id = @@spid It should return "Kerberos".
If it returns "NTLM":
Verify no duplicate spn's  (SETSPN -X)
Verify correct spn's (SETSPN -L)
Repeat for SQLSERVER2

Once you can connect to both servers using Kerberos, then you need to ensure that SQLSERVER1 is trusted to delegate to SQLSERVER2.  This is set in AD on the service account.


How do I solidify my SQL Server / T-SQL Knowledge?

There are probably a thousand good answers here and only some of them will apply to your learning style and situation. If you were able to read the 70-461 book and then use it to immediately pass the test, then you've already got a pretty good foundation. The question really becomes, what is your personal learning style and which methods apply to your learning style?

For me personally, I tend to read about a new concept and then try to apply it as soon as possible. Let's say for example that I have read about Window functions in the 70-461 book. The concepts are abstract and hazy at first and the words accompanying the explanation make sense but they don't have any solid value in my bank of experience. What I will try to imagine is a single scenario in which a Window function would be useful.

Sometimes I think back to my early days of writing SQL and wonder if I could have applied a better technique such as a Window function. Sometimes I'll actually dig up old queries and rewrite them. I will spend a lot of time comparing the results of the old query and the new query and the performance metadata. Some questions I will ask myself are: Was I able to perform the same task quicker and with fewer resources?

Also, one of my favorite techniques is to imagine a problem and try to solve it in at least 3 different ways. Let's say I need to create a time series comparing one year of traffic data to the previous year of traffic data. It turns out that there are at least 3 (maybe 4) ways to do this. Some are faster, some are slower, some are easier to write. I will try to master them all and understand them all. I never feel completely satisfied with a query. I will always leave open the possibility that I can improve upon a prior technique.

Another method I like to use is to find some old iterative code involving queries (something that runs a query 20000 times in a loop) and then try to come up with a set-based method for  producing the same results. Like you, I started out as an imperative language programmer and stumbled upon sets later in life. I didn't have a framework for how to work with sets and now I do. At work, I often run across old stored procedures that have 1000 lines of code using lots of materialized tables and loops. What I like to do is reduce them down to a single query of 100 lines. I don't know how much that will help you, but trying to re-solve old problems tends to enrich my learning experience.

As for administration itself -- I can only recommend that you download the virtual machine for the Windows Server trial edition and install the trial or CTP SQL Server editions over and over again and in as many ways as you possibly can. You might have to learn a lot more about Windows administration than you ever hoped possible, but that's the life of a Production DBA.

As for SSIS and SSAS and SSRS -- it's challenging to come up with imagined scenarios for those. There are probably a ton of packages out there for the AdventureWorks and other sample databases that you can play with. I find for those that it's extremely helpful to go through online tutorials. Try Microsoft's Virtual Academy or lynda.com to get started or even YouTube has a million video tutorials.

To answer your secondary question, how to prevent forgetting ... Reinforce those neural pathways in your brain by returning to them over and over. Every time your spark a synapse it'll get stronger and the memory of the process will become more focused/permanent. Try to learn as much as you can, as holistically as you can, with as much context as you can and keep applying it as often as you can.

PS, I was a teacher for 4 years and I took many pedagogy courses to specifically understand learning techniques and styles. The best learning environment is probably a low stress, contextual environment that tolerates mistakes -- i.e., a sandbox. So build yourself a sandbox where you can play and make as many mistakes as necessary.


Always On Availability Groups Network Configuration in AWS

In AWS EC2, you don't get the ability to add additional network ADAPTERS to your VM.

You can add additional network INTERFACES, which are just different IPs/subnets/routes/etc, but they're using the same underlying network adapters (cards).

You can choose to use instance types with faster networking ports, though. The EC2 instance configuration page lists which ones have 10 Gigabit ports, although I like ec2instances.info for easier sorting and filtering. When you're doing Always On Availability Groups, it's especially important to use this faster networking because high-network operations (like backups to another machine) can result in SQL Server timeouts (disclaimer: that's my blog post) if you've only got GbE networking. (That causes AG failovers.)


How to get next value of Sequence dynamically in Oracle

My_Sequence_Name.NextVal - check here and here. Psoug is a great site for Oracle.


WHERE...LIMIT much slower than WHERE...ORDER BY...LIMIT in MySQL 5.5

By adding the ORDER BY clause to your query, it resulted in the query optimizer choosing a different execution plan that uses the index to seek out the rows (which was a lot less rows to seek) as opposed to scanning the range of a lot more rows in your first query.
This isn't a guarantee that ORDER BY will always cause this to happen, but in this case it influenced the query optimizer to choose a different operation that happened to be more efficient (likely because the index it used is already sorted on the field you're ordering by).


There are a few things of interest here (note: I currently work in this field, so I'm not going to go into too much depth with regards to system functionality and requirements).

Avoid exposing database internal identifiers to the outside world. The field values in an integer IDENTITY column have no meaning outside the database. The absolute farthest they should go is the client-side data access layer.
Normally, students (people) are identified within a jurisdiction by a unique number (i.e., student number) assigned by the jurisdictional authority (i.e., department of education). In some jurisdictions, students (people) need to exist in the system without a unique number because the numbers aren't assigned synchronously as students (people) are registered. I don't know if you need to deal with that or not. If that is the case, students (people) without the unique number are determined to be unique by a business key, such as the combination of first name, last name, gender and birth date. Everyone, though, will have a unique surrogate key value in the database, which you (not the user) will use to uniquely identify a record.
You will have to implement functionality to search for students. Most of the time users of the system will be searching based on a name (or partial name), not a student number. The resulting search should display students to which the user "has access." For example, if the user is a teacher, they should only have access to the students they teach. The search interface will only allow the user to select a single student -- which will give you the internal identifier you need -- before proceeding.

So in summary, I think you're on the right path with the database design, it's just a matter of implementing some extra functionality on the front end for the user to be able to uniquely identify a student.


Permissions to see and script objects, but not alter them?

You will need VIEW DEFINITION on the database and that will give you access to the DDL.

USE <database>;
GRANT VIEW DEFINITION TO <user>;


Red Gate SQL Prompt in Visual Studio not working?

Unfortunately SQLPrompt works on the connection to a database and not the database project. If you deploy your DB then SQLPrompt can pick it up the schema.

Its on the backlog as a research item currently https://www.red-gate.com/products/sql-development/sql-prompt/roadmap.

If you want project based intellisense then you need to disable SQL Prompt in VS


Let's say your query looks like this:

SELECT type,person,COUNT(*) `Count`
FROM mytable GROUP BY type,person WITH ROLLUP;

The NULL values coming out can be substituted with something like this...

SELECT
    IFNULL(type,'All Types') Type,
    IFNULL(person,'All Persons') Person
    COUNT(*) `Count`
FROM mytable GROUP BY type,person WITH ROLLUP;

Using IFNULL() will forcibly put those Text String in place of the NULL for each level of the GROUP BY. As an example, here are past posts where I actively substitute NULL in my answer involving WITH ROLLUP:

Sep 13, 2011 : Determining max database and table size supported and present size
Dec 01, 2011 : MySQL Workbench Database Sizes
Jan 11, 2013 : Translating backup size to database size

From these posts I have this example,

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score, IF(ISNULL(table_schema)=1,
'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,
"Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),CONCAT(B.engine," Tables for ",
B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND engine IS NOT NULL
GROUP BY table_schema,engine WITH ROLLUP) B,(SELECT 3 pw) A) AA
ORDER BY schemaname,schema_score,engine_score;

Here is the output from one of my client's DB Servers:

+------------------------------------+----------------------+----------------------+----------------------+
| Statistic                          | Data Size            | Index Size           | Table Size           |
+------------------------------------+----------------------+----------------------+----------------------+
| MyISAM Tables for dev_oxygen       |             0.000 GB |             0.000 GB |             0.000 GB |
| InnoDB Tables for dev_oxygen       |             1.480 GB |             0.506 GB |             1.986 GB |
| Storage for dev_oxygen             |             1.480 GB |             0.506 GB |             1.986 GB |
| MyISAM Tables for dumbpdb          |             0.001 GB |             0.001 GB |             0.002 GB |
| InnoDB Tables for dumbpdb          |             1.552 GB |             0.583 GB |             2.135 GB |
| Storage for dumbpdb                |             1.553 GB |             0.584 GB |             2.137 GB |
| InnoDB Tables for duplicate_oxygen |             0.166 GB |             0.073 GB |             0.239 GB |
| Storage for duplicate_oxygen       |             0.166 GB |             0.073 GB |             0.239 GB |
| InnoDB Tables for facebook_app     |             0.000 GB |             0.000 GB |             0.000 GB |
| Storage for facebook_app           |             0.000 GB |             0.000 GB |             0.000 GB |
| InnoDB Tables for generationo      |             0.016 GB |             0.002 GB |             0.018 GB |
| Storage for generationo            |             0.016 GB |             0.002 GB |             0.018 GB |
| InnoDB Tables for mantisbt         |             0.000 GB |             0.000 GB |             0.001 GB |
| Storage for mantisbt               |             0.000 GB |             0.000 GB |             0.001 GB |
| MyISAM Tables for oxygen           |             0.003 GB |             0.002 GB |             0.005 GB |
| InnoDB Tables for oxygen           |             1.771 GB |             0.739 GB |             2.510 GB |
| Storage for oxygen                 |             1.774 GB |             0.741 GB |             2.515 GB |
| Storage for All Databases          |             4.990 GB |             1.907 GB |             6.897 GB |
+------------------------------------+----------------------+----------------------+----------------------+
18 rows in set (12.67 sec)

Notice I check the ISNULL() of each column and compare IF() function calls to produce a single column output. In your particular case, you are going to need one of these two queries

QUERY #1

SELECT
    IF(ISNULL(type)=1,'Grand Total',
        IF(ISNULL(person)=1,CONCAT(QUOTE(type),' Subtotal'),
        CONCAT(QUOTE(type),' Subtotal for ',QUOTE(person)))
    ) Statistic,`Count`
FROM
(
    SELECT type,person,COUNT(*) `Count`
    FROM mytable GROUP BY type,person WITH ROLLUP
) A;

QUERY #2

SELECT
    IF(type='AllDone','Grand Total',
        IF(person='AllDone',CONCAT(QUOTE(type),' Subtotal'),
        CONCAT(QUOTE(type),' Subtotal for ',QUOTE(person)))
    ) Statistic,`Count`
FROM
(
    SELECT
        IFNULL(type,'AllDone') type,
        IFNULL(person,'AllDone') person,
        COUNT(*) `Count`
    FROM mytable GROUP BY type,person WITH ROLLUP
) A;

Here is some sample data:

DROP DATABASE IF EXISTS ryanodonnell;
CREATE DATABASE ryanodonnell;
USE ryanodonnell
CREATE TABLE mytable
(
    id int not null auto_increment,
    type varchar(20) not null,
    person varchar(20) not null,
    key type_person_index (type,person),
    primary key (id)
);
INSERT INTO mytable (type,person) VALUES
('Type A','Person 1'),('Type A','Person 2'),('Type A','Person 3'),
('Type A','Person 5'),('Type A','Person 2'),('Type A','Person 3'),
('Type A','Person 2'),('Type A','Person 3'),('Type A','Person 1'),
('Type A','Person 5'),('Type A','Person 1'),('Type A','Person 5'),
('Type A','Person 3'),('Type A','Person 4'),('Type A','Person 5'),
('Type B','Person 1'),('Type B','Person 2'),('Type B','Person 3'),
('Type B','Person 2'),('Type B','Person 3'),('Type B','Person 4'),
('Type B','Person 3'),('Type B','Person 4'),('Type B','Person 5'),
('Type B','Person 4'),('Type B','Person 5'),('Type B','Person 4'),
('Type B','Person 3'),('Type B','Person 1'),('Type B','Person 3');

Here is that sample data loaded:

mysql> DROP DATABASE IF EXISTS ryanodonnell;
Query OK, 0 rows affected (0.00 sec)

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

mysql> USE ryanodonnell
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id int not null auto_increment,
    ->     type varchar(20) not null,
    ->     person varchar(20) not null,
    ->     key type_person_index (type,person),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO mytable (type,person) VALUES
    -> ('Type A','Person 1'),('Type A','Person 2'),('Type A','Person 3'),
    -> ('Type A','Person 5'),('Type A','Person 2'),('Type A','Person 3'),
    -> ('Type A','Person 2'),('Type A','Person 3'),('Type A','Person 1'),
    -> ('Type A','Person 5'),('Type A','Person 1'),('Type A','Person 5'),
    -> ('Type A','Person 3'),('Type A','Person 4'),('Type A','Person 5'),
    -> ('Type B','Person 1'),('Type B','Person 2'),('Type B','Person 3'),
    -> ('Type B','Person 2'),('Type B','Person 3'),('Type B','Person 4'),
    -> ('Type B','Person 3'),('Type B','Person 4'),('Type B','Person 5'),
    -> ('Type B','Person 4'),('Type B','Person 5'),('Type B','Person 4'),
    -> ('Type B','Person 3'),('Type B','Person 1'),('Type B','Person 3');
Query OK, 30 rows affected (0.00 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql>

Now, here is the output of Query #1:

mysql> SELECT
    ->     IF(ISNULL(type)=1,'Grand Total',
    ->         IF(ISNULL(person)=1,CONCAT(QUOTE(type),' Subtotal'),
    ->         CONCAT(QUOTE(type),' Subtotal for ',QUOTE(person)))
    ->     ) Statistic,`Count`
    -> FROM
    -> (
    ->     SELECT type,person,COUNT(*) `Count`
    ->     FROM mytable GROUP BY type,person WITH ROLLUP
    -> ) A;
+----------------------------------+-------+
| Statistic                        | Count |
+----------------------------------+-------+
| 'Type A' Subtotal for 'Person 1' |     3 |
| 'Type A' Subtotal for 'Person 2' |     3 |
| 'Type A' Subtotal for 'Person 3' |     4 |
| 'Type A' Subtotal for 'Person 4' |     1 |
| 'Type A' Subtotal for 'Person 5' |     4 |
| 'Type A' Subtotal                |    15 |
| 'Type B' Subtotal for 'Person 1' |     2 |
| 'Type B' Subtotal for 'Person 2' |     2 |
| 'Type B' Subtotal for 'Person 3' |     5 |
| 'Type B' Subtotal for 'Person 4' |     4 |
| 'Type B' Subtotal for 'Person 5' |     2 |
| 'Type B' Subtotal                |    15 |
| Grand Total                      |    30 |
+----------------------------------+-------+
13 rows in set (0.27 sec)

mysql>

Now, here is the output of Query #2:

mysql> SELECT
    ->     IF(type='AllDone','Grand Total',
    ->         IF(person='AllDone',CONCAT(QUOTE(type),' Subtotal'),
    ->         CONCAT(QUOTE(type),' Subtotal for ',QUOTE(person)))
    ->     ) Statistic,`Count`
    -> FROM
    -> (
    ->     SELECT
    ->         IFNULL(type,'AllDone') type,
    ->         IFNULL(person,'AllDone') person,
    ->         COUNT(*) `Count`
    ->     FROM mytable GROUP BY type,person WITH ROLLUP
    -> ) A;
+----------------------------------+-------+
| Statistic                        | Count |
+----------------------------------+-------+
| 'Type A' Subtotal for 'Person 1' |     3 |
| 'Type A' Subtotal for 'Person 2' |     3 |
| 'Type A' Subtotal for 'Person 3' |     4 |
| 'Type A' Subtotal for 'Person 4' |     1 |
| 'Type A' Subtotal for 'Person 5' |     4 |
| 'Type A' Subtotal                |    15 |
| 'Type B' Subtotal for 'Person 1' |     2 |
| 'Type B' Subtotal for 'Person 2' |     2 |
| 'Type B' Subtotal for 'Person 3' |     5 |
| 'Type B' Subtotal for 'Person 4' |     4 |
| 'Type B' Subtotal for 'Person 5' |     2 |
| 'Type B' Subtotal                |    15 |
| Grand Total                      |    30 |
+----------------------------------+-------+
13 rows in set, 2 warnings (0.00 sec)

mysql>

Give it a Try !!!


Best way to create dynamic table for search

You could use an unstructured column type like XML or JSON. But MySQL doesn't support these types AFAIK. You could store these types as a string in MySQL but I wouldn't recommend that. The reason for that is if you need to change one value in the XML/JSON you would need to change the whole string or somehow parse it with with RegEx. Nasty.

So to use an unstructered type you would need to move to a different RDBMS. MariaDB has some JSON support. Switching from MySQL to MariaDB should be an easy migration, see this guide from DigitalOcean.

If you don't want to change RDBMS then I think the EAV model mentioned by Phil is the best choice although these tend to degrade in performance as the data grows. Filtered indexes can help but they too AFAIK are not available in MySQL.


Cannot set MEMORY_TARGET for Oracle 11.2

First look what other parameters are set:

CREATE pfile='pfile.txt' FROM spfile;

Maybe db_cache_size, shared_pool_size, sga_target or other memory related parameters are set to non zero? Remember that when using AMM those parameters specify minimum memory allocated for particular pool. So if sga_target is 6GB you will not be allowed to set memory_target to 4GB.

Also sum of internal variables __sga_target, __db_cache_size, etc. may be more than your specified value of 4GB.

If you see those symptoms you can cleanup pfile bounce Oracle with pfile and recreate spfile. In the same step you can also set PGA_AGGREGATE_TARGET to zero.

STARTUP pfile='pfile.txt'
CREATE spfile from pfile='pfile.txt';


Materialized view not purging (over dblink via view)

Try basing your MV directly on the source table and not on an intermediate view. Include your "isgis=1" condition in the MV query.
create materialized view primary_system.workorder_mv
build immediate
refresh force
on demand
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    external_system.workorder@gistomax
    where isgis=1


Storing multiple IDs in column or in new table

There is an art and a science to database design.  The science is normalisation.  If you're intent on learning about databases you will need a solid understanding of it.

The art is in deciding just what exactly the "things" are that are going into your database.  Defining them in a why that is comprehensive and precise is a skill to learn.

In your particular case, storing multiple card ids in a single column prima face breaks first normal form.  This is where the "art" bit comes in.  If you only ever consider these cards all together - say, as a "hand" or a "pack" - and never as individual items then it would be legitimate to model them as a single column.  If you ever want to consider each card individually ("How many times has the Queen of hearts been delt?") then you would be better of modelling a seperate table with one row per card.

A second consideration is performance.  It will always be faster to insert or delete one row than 52.  It will almost always be slower to pull a substring out of a long varchar than to pick a single row from a large, well-indexed table. These considerations conflict. On balance, I would always recommend the one-row-per-card approach until it was proven otherwise.

You are best placed to judge the usage and volumes of your application and, hence, resolve the conflicts.


SQL Server : primary keys advice to my whitepaper needed

You are confusing various concepts here, the major error being primary key != clustered key. Because of this misunderstanding, the majority of the guidance is incorrect. To be brutally honest, you are probably not well placed to be writing these guidelines.

Primary key != clustered key
Unique identifiers make for very poor clustered keys
SQL Server is not "very well fine tuned to use integer PKs". The case for a narrow clustered key is covered in the link above, it is not a matter of SQL Server being optimised for integer clustered keys.


We're now looking for a way to monitor the replication; mostly to verify that the slave server is still up-to-date.

For monitoring replica lag, there are several ways that give slightly different answers, depending on which version of Postgres you are using. A simple query that can be done directly on the standby is:

SELECT (CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
        THEN 0
        ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
        END) AS log_delay;

Note this check will not report "lag" in cases where, say, the connection to the primary has been severed, since this check of difference between XLOG segments received and applied doesn't know anything about XLOG segments which have not been received yet -- there are additional checks of pg_stat_replication on the primary which may be helpful as well, see e.g. here.

  What does "promptly notice" mean in this context?

In this context, "promptly notice" is talking about how much time may or must elapse before a TCP client (i.e. the standby) notices that its connection to the other side has been severed. (I think the docs should really say "helps the standby" not "helps the primary" here, since it is the standby which must detect a broken connection and re-establish a new connection to the primary.)

See also this relevant question talking about how keepalive settings affect this time.

  Is there some error log where a broken connection gets reported?

I believe that you will see messages in the postmaster logs on both the primary and the standby side (particularly if you have log_connections / log_disconnections enabled) when SR connections are dropped or re-established.


What you would need is a so called "autonomous transaction" (a feature provided by oracle). At this point this is not possible in PostgreSQL yet.
However, you can use SAVEPOINTs:

BEGIN;
INSERT ...
SAVEPOINT a;
some error;
ROLLBACK TO SAVEPOINT a;
COMMIT;

It is not entirely an autonomous transaction - but, it allows you get "every transaction" right. You can use it to achieve the thing you expect from autonomous transactions.

Otherwise there are no other reasonable solution at this point.


Create temporary tables per session in a DB other than tempdb

There is no direct way to copy temp table behavior into non user databases. This starts with the easy collision preventions and continues to several advantages of temp tables (like reduced logging) that just can't be reproduced.

To create data sets in user databases that avoid collisions you have basically two options:

1. put all the data in one table and deal with the separation using some kind of session id in the table

The most obvious session id you could use for the single table approach is the @@SPID. But you could use other "random" values too. To achieve greater physical separation, you could partition the table on that column.
The obvious disadvantage is that all sessions have to use the same table layout.

2. create a random table name and use dynamic sql from then on.

The random-table-name option is going to make your code very hard to read. But as SQL Server does not allow statements to access objects based on names stored in variables, dynamic SQL is your only option.

If you go this way I recommend to include the current session_id and request_start_time (from sys.dm_exec_requests) as well as a guid in the name. That way it is easy to write some kind of garbage collector that goes around and deletes tables that got left behind. Depending on your object life time expectations you might have to use a different time stamp like the connect_time in sys.dm_exec_connections but the idea is the same. Once the request with that start time is gone, the object cannot be of use anymore because the variable holding its name went out of scope too.


What does a "Buffer cache hit ratio" of 9990 mean?

Confusing, right?

Well, to actually get the ratio, you'll need to do it yourself using the Buffer cache hit ratio base in addition to the Buffer cache hit ratio by taking the result from Buffer cache hit ratio / Buffer cache hit ratio base.

Try the below query (from Less Than Dot), which should give you the % you're looking for:

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters  a
JOIN  (SELECT cntr_value, OBJECT_NAME
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio base'
        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'


MySQL's result seems correct... MBRCONTAINS() tests whether the Minimum Bounding Rectangle of one object contains the other object.

Does the smallest possible rectangle you can draw around the polygon... contain those points?  Visually, it certainly appears that it does.

To test whether the polygon, itself (not the MBR) contains the point, the function you want is ST_Contains(), which was introduced in MySQL 5.6.

http://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions-object-shapes.html


SQL Server Error "Database 'mssqlsystemresource' does not exist."

The mssqlsystemresource database is only available in single-user mode. On Linux, that means

sudo systemctl stop mssql-server
sudo -u mssql /opt/mssql/bin/sqlservr -m


Password mismatch while logging to sql server

The only times I have ever seen the State 8 error for 18456 (Password Mismatch) was in fact a password mismatch. Try a new password. Type it in notepad, copy and paste it to SQL and then do the same into your app config. Make sure nothing is happening to that password when it is being handed off to SQL - no trimming, extra spaces, etc.

But then the second error you got seems to indicate you may have gotten past that bad password error and are now unable to access your database.

That can be one of a few things:

1.) Verify the database specified really does exist.
2.) Verify that the login you are using has been granted appropriate permissions into that database (If the permissions are at the SQL Server system administrator level then this shouldn't be a necessary step)

That's where I'd start anyway.


I'd move localization to Addresses. You'd probably like to know where an address is located anyway.
Some people have many addresses (a fiscal address,
shipping address, multiple houses) And some addresses belong to many people (families, companies...) Consider creating a many to many table that relates Persons and Addresses.


How to inquire about current value of MAX_EXECUTION_TIME

SHOW VARIABLES LIKE 'MAX_EXECUTION_TIME' or select @@max_execution_time for retrieval.
"The execution timeout for SELECT statements, in milliseconds. If the value is 0, timeouts are NOT enabled." per documentation.


What are the best ways to thrash SQL Server?

Just found out about Hammer DB which is an open-source, configurable load generator.  We're using it for VM overhead evaluation.  You should be able to pick just the right number of simulated users to stress your system without breaking it.


PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs

Probable bug on 9.6 and 9.6.1

This completely looks like a bug to me...

I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (in version 9.6.0 and 9.6.1).

CREATE TABLE users
(
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    column_that_we_will_drop TEXT
) ;

-- Function that uses the previous table, and that has a CTE
CREATE OR REPLACE FUNCTION __post_users
    (_useremail text)
RETURNS integer AS
$$
-- Need a CTE to produce the error. A 'constant' one suffices.
WITH something_even_if_useless(a) AS
(
    VALUES (1)
)
UPDATE
    users
SET
    id = id
WHERE
    -- The CTE needs to be referenced, if the next
    -- condition were not in place, the problem is not reproduced
    EXISTS (SELECT * FROM something_even_if_useless)
    AND email = _useremail
RETURNING
    id
$$
LANGUAGE "sql" ;

After this setup, the next statement just works

SELECT * FROM __post_users('a@b.com');

At this point, we DROP one column:

ALTER TABLE users
    DROP COLUMN column_that_we_will_drop ;

This change makes the next statement to generate an error

SELECT * FROM __post_users('a@b.com');

which is the same as mentioned by @Andy:

ERROR: table row type and query-specified row type do not match
SQL state: 42804
Detail: Query provides a value for a dropped column at ordinal position 3.
Context: SQL function "__post_users" statement 1
    SELECT * FROM __post_users('a@b.com');

Dropping and recreating the function does NOT solve the problem.
VACUUM FULL (the table or the whole database) does not solve the problem.

The bug report was passed to the appropriate PostgreSQL mailing list and we had a very fast response:

  I can't reproduce this in HEAD or 9.6 branch tip.  I believe it was
  already fixed by this patch, which went in a bit after 9.6.1:

  https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f4d865f22

  But thanks for the report!

  regards, tom lane

Version 9.6.2

On 2017-03-06, I can confirm that I cannot reproduce this behaviour on version 9.6.2. That is, the bug seems to have been corrected on this release.

UPDATE

Per comment of @Jana: "I can confirm the bug is present in 9.6.1 and was fixed in 9.6.2. The fix is also listed on postgres release website: Fix spurious "query provides a value for a dropped column" errors during INSERT or UPDATE on a table with a dropped column"


Using LIKE to search case insensitive over language specific characters (PostgreSQL)

unaccent

The traditional, non-PG 10 method of this is to the extension unaccent.

CREATE EXTENSION unaccent;
SELECT 'a' = lower(unaccent('Å'));

You jam this into a TEXT SEARCH CONFIGURATION

WITH unaccent, french_stem;

And then magically all of your fts stuff works as English speakers desire.


Indexes: Normal tables vs memory optimized

Since the architecture of these two types of tables and indexes is completely different, I would say the answer would be "no". If the table needs to be optimized for point lookups, you should use a HASH index, but that could cause you problems, because if the key is comprised of multiple columns - and you only supply the leading column - you'll get a table scan instead of a seek. In general, it's best to use RANGE indexes, and then only when you can prove you need a HASH index, to possibly deploy that.

Another area is columnstore indexes, which are radically different between the two table types. See my post here: Clustered columnstore: on-disk vs. in-mem.

There is a lot to consider before deploying the In-Memory OLTP feature. I have blogged almost exclusively on this topic for the last two years. I strongly advise you to read my posts....


Due to plperlu being untrusted, the account requires superuser authorization:

DROP LANGUAGE plperl;
DROP LANGUAGE plperlu;
CREATE OR REPLACE TRUSTED LANGUAGE plperlu;
UPDATE pg_language set lanpltrusted = true where lanname='plperlu';
GRANT ALL ON LANGUAGE plperlu TO account_name WITH GRANT OPTION;
ALTER USER account_name WITH SUPERUSER;

I do not know if plperl and plperlu are allowed to be installed at the same time, so I dropped the former. The function can now be created.


You will get errors if you restore a row that contains a value in nQuestionId2 that references a value in nQuestionId1 that has not been created yet.

There are a few ways to resolve this issue:

You could ensure that you create the data in the correct order.
You could disable the foreign keys before importing the data, then re-enable them after.
If disable/re-enable is not an option, you could drop/recreate them.
Alternatively you could split out the data so that the nQuestionId1 is in a separate table - this will allow you to restore all the nQuestionId1 values first.

I guess but the question really is about what you are trying to achieve.

I have seen designs like this that have a ID column and then a PARENT column that references the ID. In order to get the root parent you have to recurse through all of the parents of the object you are looking at. This means that even though a design like this may make sense logically, querying it could become quite difficult.

Database design is not just about mapping business rules and objects to tables and constraints, but also thinking about what is maintainable, how will the design hold up against large amounts of data, and being pragmatic about your approach.

I hope this helps you.


SSIS OnError propagating when it shouldn't

I suggest you approach this issue differently. If you suppress errors, you lose insight into the myriad errors other than a connection time-out that can occur. Nothing worse than a silent error that causes you to lose data or load bad data for... how long until anyone will ever find this with no alerts and no SSIS failures?

Handle the error in the code itself. Use TRY CATCH blocks to execute your code to an expected stopping point. In your case you want to specifically handle a System.TimeoutException by trying to connect again. For any other exception the package should fail. By handling the issue in the code you can allow true failures to bubble up through the SSIS failure propagation, while addressing the specific errors you expect or have encountered gracefully in the script component itself.


To do that you could try:

select
   level,
   owner, name,
   referenced_owner, referenced_name
from
   dba_dependencies
start with
    owner = '${YOUR_TABLE_OWNER}'
and name = '${YOUR_TABLE_NAME}'
connect by prior referenced_owner = owner
       and prior referenced_name = name
       and prior referenced_type = type
and type = 'TABLE';

Datapump uses a similar strategy to prevent constraint violations.
I hope this helps.


Find and replace database name in views and stored procedures

This could be done using the sys.objects table and the OBJECT_DEFINITION() function.

SELECT OBJECT_DEFINITION(id) AS ORIGINAL,
    REPLACE(OBJECT_DEFINITION(id),'VALUE_TO_BE_REPLACED', 'VALUE_TO_REPLACE_WITH') AS UPDATED
    ,*
FROM sys.sysobjects o
WHERE xtype = 'V'

The 'UPDATED' column from this query can be used to do a quick find and replace on whatever database or table name might be found in the view definition.  You will want to make sure you do the same for every possible version of the text you are searching.  For instance a database called 'db' might be written as [db] or db. Once you have what you want from the query just copy and paste to a query window and execute the whole thing.  Please make sure to backup the database prior to running it.  Also carefully proof read the output text so you can verify that the replace worked correctly and did not accidentally change more than you intended.

BTW, the answer WEI_DBA gave is perfectly acceptable also.  It's just a different approach to the same thing.  In his example, the GUI will aide in getting all of the object definitions to the query window.  Then you would just use Find/Replace(ctrl+h) to replace the database name.  To each his own in this case.
I don't know that there's an advantage in either approach except maybe that my approach will allow you to limit the query only to certain objects (views in this case) with additional filters added if needed in the where clause.   Generate scripts will allow you to filter to only views, stored procs, etc, and then individually select objects but additional filters will be limited to whatever the UI can do.


The problem was with the VM provider.
Sql Server 2012 Web license states it's limited to 4 sockets or 16 cores, so if you want to use 16 cores you need a machine with 4 sockets that have 4 cores each.
In my case I had 6 sockets with one core each, that's why only 4 were usable.

Hope this helps future developers facing a similar problem.


Implementing application locks within SQL Server (Distributed Locking Pattern)

Do I need to make the "unlock" SERIALIZABLE as well?

Despite the name, the serializable isolation level does not guarantee transactions will be executed sequentially, or in the order received. Rather, serializable guarantees transactions will have the same persistent effects on the database as if they had executed sequentially, in some undefined order (see the link for more details).

  Are there any other approaches that I could use/anything that I have forgotten?

Yes. SQL Server already provides support for arbitrary application locks via:

sp_getapplock
sp_releaseapplock
APPLOCK_MODE
APPLOCK_TEST

These built-in applock features provide a broad range of options, and include automatic deadlock detection (though any transaction rollback required is the programmer's responsibility). For example, you can choose from transaction- and session-scoped locking, in a variety of modes. Using these features to implement the behaviour you need should be quite straightforward, and certainly simpler than building your own resilient and reliable lock manager from scratch.


Maintenance Cleanup Job Task Fails but Backup Jop is successful

Much as the error message states, it can't find the file N'\\SQLSvr..."

If you know the file does exist, then that usually points to a permission problem with either the account SQL Agent is executing as or if you've created it, a credentials proxy account. Either way, it can't access the network share or it does not have permission to perform the delete operation.

I'd first verify the account can access the share, then look at folder permissions and ensure it has delete privilege.


IBM DB2 Timetravel logging based on some criteria

With Db2 (LUW) Temporal Tables, there is not any option to exclude updates from a certain user.

However, the SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY procedure is available, and can be called by a user (if a user is granted permission on it), and this will disable the recording of temporal history for system-period temporal tables with the the duration of the current session.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0060661.html

  Set the internal option to disable recording of temporal history for a system-period temporal table and allow values to be specified for the special temporal columns.


Understanding multiple table join with aggregation

What you see is a "proxy cross join". Aggregate first, then join:

SELECT m.id, COALESCE(l1.ct, 0) + COALESCE(l2.ct, 0) AS total_ct
FROM   main m
LEFT   JOIN (
   SELECT main_id, count(*) AS ct
   FROM   link1
   GROUP  BY main_id
   ) l1 ON l1.main_id = m.id
LEFT   JOIN (
   SELECT main_id, count(*) AS ct
   FROM   link2
   GROUP  BY main_id
   ) l2 ON l2.main_id = m.id
ORDER BY m.id;

SQL Fiddle.

Do not multiply rows with multiple unqualified joins and count(DISTINCT ...) later to fix the mistake. It happens to work in this case since counting DISTINCT link1.id / link2.id coincides with the desired result, but it's needlessly expensive and error prone.

Detailed explanation and a couple of syntax variants in these related answers on SO:

Multiple array_agg() calls in a single query
Two SQL LEFT JOINS produce incorrect result


Provided you have an index on user_id (or a compound index with user_id as the first column) and a user does not have to many rows, you don't have to index final_point and total_rank.


SQL server database Role management - Azure SQL MI

What is the best practice in managing users and roles?

Create custom roles and grant permissions to the roles; then add users to the roles.

eg:

create role reader_abc
grant select on dbo.a to reader_abc
grant select on dbo.b to reader_abc
grant select on dbo.c to reader_abc

create role reader_ab
grant select on dbo.a to reader_abc
grant select on dbo.b to reader_abc

alter role reader_abc add member alice
alter role reader_ab add member joe

You can also assign permissions to an entire schema:

grant select on schema::dbo to report_users;

Or the whole database:

grant select to report_users;


Can I alter data within a mariadb db using mysql-workbench?

In principle, yes.

Though it depends on the GRANTs for your particular database user. If you can't change the values, then try another database user or create a new user with the correct GRANTs.

You should be able to click on a table to list its records (it's the right-most icon when you hover over the table in my MySQL Workbench version), then double-click on the value you want to change, change the value, then hit the 'enter' key on your keyboard, then click the 'Apply' button below the table listing.


misplacing of on condition in a join was executing successfully instead of throwing a syntax error

This is from Itzik Ben-Gan's book Inside Microsoft SQL Server 2008: T-SQL Querying

I find this explanation (picture including) very helpfull


I'm using this sql to get 'application' (field) -> 'tour_id.id'

Assuming you meant to ask:

I'm using this SQL query to retrieve the row where application #>> '{0, tour_id, id}' equals the provided UUID.

.. which already carries half an answer.

If your outer JSON array only has a single element like your example value implies, use this simple query:

SELECT *
FROM   payment
WHERE  application #>> '{0, tour_id, id}' = 'a43d38d4-9bfb-4ffa-8704-e536f04b0c60'
ORDER  BY "createdAt" DESC;

Note how I replaced the (assumingly) unneeded fuzziness of
like '%a43d38d4-9bfb-4ffa-8704-e536f04b0c60%'
with simpler and faster
= 'a43d38d4-9bfb-4ffa-8704-e536f04b0c60'.

Else (for any number of elements in the outer JSON array):

SELECT *
FROM   payment
WHERE  application @> '[{"tour_id": {"id":"a43d38d4-9bfb-4ffa-8704-e536f04b0c60"}}]'
ORDER  BY "createdAt" DESC;

That's the jsonb containment operator @>, which can be supported with a generic index or a more specialized jsonb_path_ops index. Instructions and links:

How to get particular object from jsonb array in PostgreSQL?

The manual hints:

  For a full description of jsonb containment and existence semantics, see Section 8.14.3.


I believe the below should give you what you are looking for?

In the ReportServer database:

SELECT link.Name AS LinkName, link.Path AS LinkPath,
source.name AS OrginalReport, source.Path AS OrginalReportPath
FROM dbo.Catalog link INNER JOIN dbo.Catalog AS source ON link.LinkSourceID=source.ItemID


Delete/Modify row in one table based on a condition

Prevent Delete

Add "on delete set null" to the FK constraint.  Since the column is NOT NULL, this process can't happen.

 CONSTRAINT salaire_code_employe_fk FOREIGN KEY(CodeEmploye)
    REFERENCES EMPLOYE(CodeEmploye)
    ON DELETE SET NULL

Prevent Modify

What you'll need to do is check for "can you update?" during the UPDATE process. (eg within a trigger)

One method would look something like:

declare
  type CodeEmploye_nt_t is table of SALAIRE.CodeEmploye%type;

  CodeEmploye_nt   CodeEmploye_nt_t;
begin
  select a.CodeEmploye
    bulk collect into CodeEmploye_nt
  from SALAIRE a
  where a.CodeEmploye in ( new.CodeEmploye, old.CodeEmploye )
  FOR UPDATE; -- prevent other from modifying important SALAIRE rows
              -- while you're in the middle of a transaction

  raise some_error;
exception
  when no_data_found then
    null;
end;


Understanding execution plans using TVPs

I don't think this query is about TVPs so much as complexity.  The plan has an optimizer timeout.  You can see it in the F4 properties of the SELECT operator as "Reason for Early Termination of Statement = Time Out" or StatementOptmEarlyAbortReason="TimeOut" in the plan xml.  These are not always a disaster, it just means the optimizer has run out of time (more iterations) to come up with a plan and picks the lowest cost one it has at that stage.  Optimizer timeouts are often a sign of over-complexity in a query and the common recommendation is to simplify, eg break it up, remove unnecessary parameters and joins etc

Looking at your query, there are 13 tables.  Looking in the plan, many of them just do a "SELECT all records" from the lookup table ( eg all records from dbo.Station for the @from and @to tables, all ticketFilter and ticketClass records ), so actually you don't need these tables and removing them does not change the result.  I understand this is a user-driven query, so you might look at approach where you only join to the tables if the user has actually applied a filter.

I can reproduce the timeout, and remove it by commenting out 4 of the tables, eg:

SELECT d.Month_Year[PeriodName], qg.Title [Service_Area], AVG((CAST(sa.Answer AS smallmoney) * 10.00)) [Average]
FROM [dbo].StaticDataView d WITH (NOEXPAND)
    INNER JOIN [dbo].[Survey] s  ON (s.CustomerJourney = d.Journey_Id)
        INNER JOIN [dbo].[SurveyAnswer] sa   ON (sa.Survey = s.ID)
            INNER JOIN [dbo].[Question] q  ON (q.ID = sa.Question)
                INNER JOIN [dbo].[QuestionGroup] qg  ON (qg.ID = q.QuestionGroupID)

    --INNER JOIN @StationFrom sfl ON (sfl.ID = d.[Source])
    --INNER JOIN @StationTo stl ON (stl.ID = d.[Dest])
    INNER JOIN @InitialScores sc ON (s.Score = sc.ID)
    INNER JOIN @RouteList rl ON (rl.ID = d.Route_ID) -- route list
    --INNER JOIN @TicketClass tc ON (tc.ID = d.Class_ID)
    --INNER JOIN @TicketTypeFilter ttl ON (ttl.ID = d.Ticket_Filter_Type_ID)
    INNER JOIN @TicketDiscount td ON (td.ID = d.Discount_Type_ID)
    INNER JOIN @Days dy ON (dy.ID = d.[DayOfWeek])

WHERE s.DateCompleted IS NOT NULL AND sa.Answer != -1
AND (d.RSID = COALESCE(@RSID, d.RSID))
AND (d.[Date] BETWEEN @Start AND @End) -- between dates
AND (d.[Time] BETWEEN @StartTime AND @EndTime) -- between times
AND ((@Direction IS NULL AND d.Direction IN (0,1)) -- Both
        OR (@Direction = 1 AND d.Direction = 1) -- North
        OR (@Direction = 0 AND d.Direction = 0)) -- South
AND ((@PassengerJourney = 0 AND d.Journey_Type_ID IN (1,2,3)) -- ALL
    OR (@PassengerJourney IN (1,2) AND @PassengerJourney = d.Journey_Type_ID) -- Single or Return
    OR (@PassengerJourney = 3 AND NULLIF(d.RSID_LEG_2,'') IS NOT NULL)) -- multi part
GROUP BY d.Month_Year, qg.Title
OPTION ( RECOMPILE )

Does this make for a better plan?  Hard to say, as in my simple rig, I can't get this query to run in more than half a second, with or without the timeout.  Even SQLFiddle doesn't seem to struggle.  Can you see much difference between the SQLFiddle repro and your setup?


No, as defined, a Blob is "a binary large object that can hold a variable amount of data." It can hold between 0 and 2^16 - 1 bytes (65535). A LONGBLOB can hold up to 4,294,967,295 bytes (or 4GB).

In most engines, blobs are implemented dynamically, meaning that it will only take its actual size plus some extra bytes for lenght/addressing, etc. In fact, fixed-row size formats are normally not compatible with blobs (MyISAM fixed, Memory storage engine).

However, there are some reasons to avoid storing "files" on a relational database (and MySQL and InnoDB in particular, mainly due to the overhead on the transaction and binary logs, plus multiversioning and its incompatibilities with temporary tables on memory). The actual decision varies depending on the needs -the most common alternative is to store those on the filesystem and just add a URL on the database.

If you store those objects inside the database, the size will not be your biggest problem (unless they are over 4GB), but the performance on complex queries and writes. However, a pure key-value store may work well with MySQL model. There is also specialised document-based databases.


Is there a way to speed up an ALTER statement in SQL Server

You're using Enterprise edition, so you should be able to use online indexing. Add the WITH ONLINE = ON option to your index DDL statement. The operation will still take just as long (possibly longer), but SQL Server will do some magic to keep the table accessible throughout. Here's some documentation with assorted restrictions, but they mostly only come into play if you've got LOB columns in the table:

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

This won't help you with altering the column data type, of course. If it's constantly being inserted, but not necessarily updated or read (and you can live with such operations being unavailable during the transition), you can rename the original table, create a new one with the desired structure and permissions, move all the data over, then recreate the indexes on the new table. This may be slightly more complicated if you have an identity column and want to preserve values, but it's still doable.


Postgres latest sequence id and uncommitted writes

select max(id) is the most reliable way.
It's also quite fast if you have a b-tree index on id (performance is virtually independent of the size of the table).


Testing

Based on your estimated execution plan I tried to get some sample data and get an actual execution of your query. Remember that while I am trying to get closer to your issue, YMMV.

CREATE TABLE dbo.[Event](EventId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                    EventTime date,
                    userid INT,
                    Count INT);
CREATE INDEX IX_EventTime_UserID
ON dbo.[Event](EventTime,userid);
CREATE INDEX IX_Event_UserId
ON dbo.[Event](userid)
INCLUDE(EventTIme,Count)

INSERT INTO dbo.[Event](EventTime,userid,Count)
SELECT TOP(100000)
DATEADD(Minute,- ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),GETDATE()),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) % 2000,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2

This is the actual plan that came out.

While not all operators & plan choices are the same, some parts look like they could match. One of these are the high amount of spools & filters with high row counts:

The querytimestats here show as 60 seconds

 <QueryTimeStats CpuTime="58806" ElapsedTime="58867" />

  The generation of the dates and cohort tables is fairly quick when
  I've broken those out separately, so I don't think those are the
  culprit.

The performance can differ between separate executions and joining , filtering, ... on the execution of these date tables. It is much easier to calculate estimates on one small part than on everything at once.

Now, If I simply create a temporary table, and insert the results of the cohortevent cte into this temporary table to split up the work:

CREATE TABLE #temp(
StartDate date,EndDate date,CoHort varchar(50)
,CohortNo int,EventId int,EventTime date
,Count int,UserID int);

&

INSERT INTO #temp
SELECT c.StartDate,c.EndDate,c.CoHort,c.CohortNo,c.EventId,c.EventTime,c.Count,c.UserID
FROM cohortevent c;

The result is a query plan with less spools & better estimates due to not evaluating everything at once.
You are kind of giving the optimizer a breather by splitting the query in two parts.

Actual Execution plan
With the query time stats:

 <QueryTimeStats CpuTime="4957" ElapsedTime="4961" />
&
 <QueryTimeStats CpuTime="2347" ElapsedTime="2348" />

~ 6 - 7 seconds

Adding one more temp table on the cohort cte gives me the execution time I would desire.

Actual execution plan
With querytimestats=

<QueryTimeStats CpuTime="2" ElapsedTime="2" />

&

<QueryTimeStats CpuTime="929" ElapsedTime="475" />

&

    <QueryTimeStats CpuTime="2304" ElapsedTime="2305" />

~3 seconds

With the resulting query:

CREATE TABLE #cohortevent(
StartDate date,EndDate date,CoHort varchar(50)
,CohortNo int,EventId int,EventTime date
,Count int,UserID int);

CREATE TABLE #cohort(
StartDate date,EndDate date,CoHort varchar(50)
,CohortNo int);

;WITH dates AS
(
    -- Set up the date range
    SELECT convert(date,GETDATE()) as dt, 1 as Id
    UNION ALL
    SELECT DATEADD(dd,-1,dt),dates.Id - 1
    FROM dates
    WHERE Id >= -84
)
, cohort as (
    -- create the cohorts
    SELECT dt AS StartDate,
        convert(date,CASE WHEN DATEADD(DD, 6, dt) > convert(date,GETDATE()) THEN convert(date,GETDATE()) ELSE DATEADD(DD, 6, dt) END) as EndDate,
        CONCAT(FORMAT(dt, 'MMM dd'), ' - ', FORMAT(CASE WHEN DATEADD(DD, 6, dt) > GETDATE() THEN GETDATE() ELSE DATEADD(DD, 6, dt) END, 'MMM dd')) as Cohort,
        row_number() over (order by dt) as CohortNo
    FROM dates A
    WHERE  DATEPART(dw,dt)=1
)
INSERT INTO #cohort(StartDate,EndDate,CoHort,CohortNo)
SELECT StartDate,EndDate,Cohort,CohortNo
FROM cohort;

;WITH cohortevent as (
    -- The complete set of cohorts and their events
    select c.*, e.*
    from #cohort c
    left join Event e on e.eventtime between c.StartDate and C.EndDate
)
INSERT INTO #cohortevent
(StartDate ,EndDate ,CoHort
,CohortNo ,EventId ,EventTime
,Count ,UserID )
SELECT c.StartDate,c.EndDate,c.CoHort,c.CohortNo,c.EventId,c.EventTime,c.Count,c.UserID
FROM cohortevent c;

;WITH Retained as(
    -- Recursive CTE that works out how long each user has been retained
    select c.StartDate,c.EndDate,c.CoHort,c.CohortNo,c.EventId,c.EventTime,c.Count,c.UserID, case when Userid is not null then 1 else 0 end as ret
    from #cohortevent c
    union all
    select c.StartDate,c.EndDate,c.CoHort,c.CohortNo,c.EventId,c.EventTime,c.Count,c.UserID, ret+1
    from #cohortevent c
    join Retained on Retained.userid=c.userid and Retained.CohortNo=c.CohortNo-1 and Retained.eventid<c.eventid
)
, WeeksRetained as (
    -- Get the highest number of weeks, which is the actual number per user (could probably be combined with previous CTE)
    select StartDate, Enddate, Cohort, userID,
        case when max(ret)=1 then '<1W' else '+'+convert(varchar,max(ret)-1)+'W' end as Weeks
    from Retained
    group by StartDate, Enddate, Cohort,userid
)
-- Finally pivot this by the number of weeks
select *
from
(
  select StartDate, EndDate, Cohort, Weeks, count(distinct userID) as UserCount
  from WeeksRetained
  group by StartDate, EndDate, Cohort, Weeks
) src
pivot
(
  sum(UserCount)
  for Weeks in ([<1W], [+1W], [+2W], [+3W], [+4W], [+5W], [+6W], [+7W], [+8W], [+9W], [+10W], [+11W], [+12W])
) piv
OPTION (MAXRECURSION 0)

DROP TABLE #cohortevent
DROP TABLE #cohort

This will not be the best performing version of your query but this should resolve the issue with your spools going crazy. You should also investigate other workarounds like using a calendar table like @DanGuzman mentioned.


SQL multiple select with where conditions performs really poorly

Given this heap:

CREATE TABLE #foo(Number int, ID tinyint, [DateTime] datetime, [Status] varchar(10));

INSERT #foo(Number,ID,[DateTime],[Status]) VALUES
(50000001, 101, '1/1/2009 0:10', 'PO'),
(50000001, 110, '1/1/2009 0:11', 'PO'),
(50000001, 102, '1/1/2009 0:15', 'PO'),
(50000001, 101, '1/1/2009 0:10', 'PCK'),
(50000001, 102, '1/1/2009 0:12', 'PCK'),
(50000001, 110, '1/1/2009 0:12', 'PCK'),
(50000001, 101, '1/1/2009 0:15', 'C'),
(50000001, 101, '1/1/2009 0:15', 'C+'),
(50000001, 110, '1/1/2009 0:15', 'C'),
(50000001, 110, '1/1/2009 0:15', 'C+'),
(50000001, 102, '1/1/2009 0:15', 'C'),
(50000001, 102, '1/1/2009 0:15', 'C+'),
(50000002, 126, '1/1/2009 0:13', 'WO'),
(50000002, 126, '1/1/2009 0:14', 'PCK'),
(50000002, 126, '1/1/2009 0:14', 'C'),
(50000002, 126, '1/1/2009 0:14', 'S');

This query drops it from two scans to a single scan, gets rid of a hash match, and shifts most of the cost to a sort:

SELECT
  ID, Num = Number,
  OrderOpen    = MIN(CASE WHEN [Status] IN ('WO','PO') THEN [DateTime] END),
  OrderCleared = MAX(CASE WHEN [Status] IN ('S','C+')  THEN [DateTime] END)
FROM #foo
GROUP BY ID, Number;

This PIVOT is also cheaper, but still has the expensive sort:

SELECT * FROM
(
  SELECT ID, Number, [DateTime], BetterStatus = CASE
    WHEN [Status] IN ('WO','PO') THEN 'OrderOpen'
    WHEN [Status] IN ('S', 'C+') THEN 'OrderCleared' END
  FROM #foo
) AS f
PIVOT
(
  MAX([Datetime]) FOR BetterStatus IN ([OrderOpen],[OrderCleared])
) AS p;
GO

However, if we add a computed column and an index:

ALTER TABLE #foo ADD BetterStatus AS CONVERT(varchar(12),
  ISNULL(CASE WHEN [Status] IN ('WO','PO') THEN 'OrderOpen'
       WHEN [Status] IN ('S','C+')  THEN 'OrderCleared' END, '?'));
GO
CREATE INDEX x ON #foo(Number, ID, [Datetime], BetterStatus);
GO

Then these two queries fare much better (both producing ordered scans without any additional sorting required):

SELECT
  ID, Num = Number,
  OrderOpen    = MIN(CASE [BetterStatus] WHEN 'OrderOpen' THEN [DateTime] END),
  OrderCleared = MAX(CASE [BetterStatus] WHEN 'OrderCleared' THEN [DateTime] END)
FROM #foo
GROUP BY ID, Number;

SELECT ID, Num, OrderOpen, OrderCleared
FROM
(
  SELECT ID, Num = Number, [DateTime], BetterStatus FROM #foo
) AS f
PIVOT
(
  MAX([Datetime]) FOR BetterStatus IN ([OrderOpen],[OrderCleared])
) AS p;

So, if you don't have supporting indexes and/or can't add computed columns or new indexes, use one of the first two queries, and if you can add these items (or modify existing indexes to achieve the same effect), use one of the latter two.

This isn't exhaustive tuning, of course. Just a kickstart.


How to get performance benefits from a view vs subquery?

The case where you can achieve performance benefit using a view (or common table expression = "inline view") instead of a subquery is if you have to repeat the same subquery several times in your query.

If instead you can replace each subquery with the same view name (or named common table expression) then the optimizer knows it's the same thing and can either cache the result or restructure the execution path so that the result only needs to be queried once.


You pretty much answered your own question. Creating a new table will be more efficient when you want to delete a large portion of the table. Just counting the number of records to modify will give you a general idea of the impact. However, you also want to account for time spent updating indexes. If you are deleting a lot of records, you may be concerned about slack space in the table, meaning that each block is only partially filled. Considering all of this together, if you need to delete more than about 30% of a table, then creating a new table would be faster.

On the down-side, creating a second copy of the table will use more disk space, even if only temporarily. It will disrupt database replication, so it is generally not a good option when replication is involved.

If the goal is to minimize impact on the database, then I recommend pt-archiver, from the Percona toolkit. It deletes records in batches and keeps commits to a reasonable size. It's a good option if you want to automate your purge to run on a daily basis.


Mysql Group replication vs Mysql Cluster

Group Replication vs Galera (aka PXC)

Group Replication is only months old; Galera has been around for years.  Once GR establishes its worth (which I think it will), the answer may be something like this...

They are very similar.  Both do an excellent job of HA -- any single node crashing can be recovered from, usually automatically.  Both provide a high degree of read scaling.  GR promises to provide more write scaling.

Without knowing your requirements, and your tolerance for various things, I cannot advise on which way to go.  Perhaps 90% of MySQL/MariaDB/Percona users on this forum do not use any form of Replication.  So, the first question is, do you need any Replication solution?

The architectures are different, but the goals are similar.  That is, "there is more than one way to skin a cat".

Fault tolerance, in my opinion, is achievable only if you are willing to put nodes in at least 3 separate geographic locations (think flood/tornado/earthquake/etc).  Both allow for that.  I use the rule of surviving any single-point-of-failure, and include "datacenter" as a point-of-failure.

GR is a significant improvement over Oracle's previous offering, Fabric.

Since you did not mention Sharding, I did not mention it.

MySQL Cluster

The term "MySQL Cluster" is confusing; technically it refers to "NDB Cluster", which is significantly different than "Group Replication" aka "InnoDB Cluster".

NDB is discussed briefly by Ibrahim, but I would not go so far as to say "best solution ever".

NDB was originally a telco application for very reliable handling of telephone switching.  MySQL, Oracle, and SeveralNines have enhanced it _to be somewhat closer to a general RDBMS.  It retains its high reliability.

I would limit the use of NDB to applications that need the specific features that it provides.

Advice

For someone starting in the database world with MySQL, I would go with a non-replicated InnoDB-based dataset.  When replication / HA / scaling are needed, consider Group Replication or Galera.  Move to NDB only if it fits your application better.


PostgreSQL pass table name and column name as arguments to function

Yo can create the functions dynamicaly to get a pair of static triggers doing this:

DO
$COMMAND$
declare
  v_sql text:=
$CREATES$
CREATE OR REPLACE FUNCTION update_votes_in_posts() RETURNS TRIGGER AS
$$
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      UPDATE posts SET votes = votes + NEW.value WHERE id = NEW.post_id;
    ELSIF (TG_OP = 'DELETE') THEN
      UPDATE posts SET votes = votes - OLD.value WHERE id = OLD.post_id;
      return old;
    ELSIF (TG_OP = 'UPDATE') THEN
      UPDATE posts SET votes = votes - OLD.value + NEW.value WHERE id = NEW.post_id;
    END IF;
    return new;
  END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER update_votes_in_posts
  AFTER INSERT OR DELETE OR UPDATE ON posts_votes
  FOR EACH ROW
  EXECUTE PROCEDURE update_votes_in_posts();
$CREATES$;
begin
  execute v_sql;
  execute replace(v_sql, 'post', 'comment');
end;
$COMMAND$;

In one command (the DO) you set a variable (v_sql) that it contains the trigger for the posts table. You can execute the content of that variable directly. Then you can replace the inside the variable content all ocurrences of post (replacing with comment).

¡Voilá!

Look at a running complete example of this in: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=f6c9931bc985f3ee7ebec7ee51c5017f


Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys

The easiest way seems to be using the sys.crypt_properties Catalog View which holds the relationship between Certificates / Asymmetric Keys and signed modules, as well as the usage (i.e. Signature or Counter-Signature). There are a few other means of finding which modules are signed, but none seem to indicate Signature vs Counter-Signature.

SELECT SCHEMA_NAME(so.[schema_id]) AS [SchemaName],
       so.[name] AS [ObjectName],
       so.[type_desc] AS [ObjectType],
       ---
       scp.crypt_type_desc AS [SignatureType],
       ISNULL(sc.[name], sak.[name]) AS [CertOrAsymKeyName],
       ---
       scp.thumbprint
FROM sys.crypt_properties scp
INNER JOIN sys.objects so
        ON so.[object_id] = scp.[major_id]
LEFT JOIN sys.certificates sc
        ON sc.thumbprint = scp.thumbprint
LEFT JOIN sys.asymmetric_keys sak
        ON sak.thumbprint = scp.thumbprint
WHERE   so.[type] <> 'U'
ORDER BY [SchemaName], [ObjectType], [ObjectName], [CertOrAsymKeyName];

In my test DB, this query returns the following:

Schema  ObjectName     ObjectType            SignatureType                        CertOrAsymKeyName  thumbprint
------  ----------     ----------            -------------                        -----------------  ----------
dbo     fnPaymentCalc  SQL_SCALAR_FUNCTION   COUNTER SIGNATURE BY ASYMMETRIC KEY  KeyTest1           0x2333B2FA6AA8004E
dbo     ModuleTest2    SQL_STORED_PROCEDURE  SIGNATURE BY CERTIFICATE             CrossDatabaseCert  0x49BA174584C78C878D923690C15898A809CBACDF
dbo     TestSig        SQL_STORED_PROCEDURE  SIGNATURE BY CERTIFICATE             bob                0x778B3DB4ED981FC27AB301ACE7A1AB8424F64792
dbo     TestSig        SQL_STORED_PROCEDURE  SIGNATURE BY ASYMMETRIC KEY          KeyTest1           0x2333B2FA6AA8004E


How to count number of rows inside Oracle dump file?

grep -a -A 5 METRICSTposttables table.dmp

outputs:

METRICSTposttables actions
METRICET 381
METRICSTPost-inst procedural actions
METRICET 381
METRICSTDeferred analyze commands
TABLE "my_table"

Where 381 matched the export count:

. . exporting table          MY_TABLE        381 rows exported


Composite foreign key not verify all columns

A constraint of FOREIGN KEY will never forbid you from entering NULL values in nullable columns. The constraint is simply ignored for such rows.

You can make the columns not nullable, if you want to enforce this. Based on the comments though, you probably just need to add a CHECK constraint on the table, so the two columns are either null or not null, together:

ALTER TABLE dbo.Table2
  ADD CONSTRAINT Num_and_Dat_are_Null_Together
  CHECK ( Num IS NULL AND Dat IS NULL
       OR Num IS NOT NULL AND Dat IS NOT NULL
        ) ;


Run this query:

 SELECT ASCII(' ') AS [SingleSpaceString]
      , ASCII('') AS [NoSpaceString]

You will note the the Value for [SingleSpaceString] is '32', while [NoSpaceString] is NULL.

I'd suggest adding ASCII(Box.Ice) to your select statement to confirm that a true space is the value, vs. an odd character.


Since MySQL 5.1, there has been a native tool called MySQL for Excel.
It is bundled along with MySQL for Visual Studio, a bunch of connectors, sample databases and so forth.
The MySQL Documentation has a webpage for

MSI Installation
Editing
Importing
Appending
Exporting

There is also a forum for users of this product (As of this posting, there are 5 active questions that have activity this month)
MANUAL ALTERNATIVE
If you have the patience, you could do the following
STEP 01) Create a table using the CSV Storage Engine
When you create the table like this:
CREATE TABLE mydb.mytable
(
    i INT NOT NULL,
    c CHAR(10) NOT NULL,
    ...
    column29 INT NOT NULL,
    column30 CHAR(10) NOT NULL,
    column31 CHAR(30) NOT NULL
) ENGINE=CSV;

This will create two files under the mydb subfolder

mytable.frm
mytable.CSV

CAVEAT : I do not think NULL columns are allowed for MySQL CSV tables
STEP 02)

Using Excel, export the spreadsheet with the same number of columns as the MySQL CSV table. Empty Cells should come out as "".

STEP 03) If using MySQL in Linux, you could do this
cd /var/lib/mysql/mydb
mv mytable.CSV mytable_old.CSV

STEP 04) Copy the Excel output into /var/lib/mysql/mydb/mytable.CSV
STEP 05) In the mysql client, run
FLUSH TABLES;
SELECT * FROM mydb.mytable;

This will close and reopen all tables. If you see your data, CONGRATULATIONS !!!
STEP 06) If you get an Error
If you get an error on SELECT * FROM mydb.mytable;, then one of the cells is not field enclosed with double quotes. Just re-export the data and try Step 04 again until you get it right
For Those using MySQL for Windows
If you have MySQL for Windows, you can drop in mydb\mytable.CSV becauswe of the way Windows locks files. You will have to run net stop mysql, copy the file in, and net start mysql, skip the FLUSH TABLES; and just run SELECT * FROM mydb.mytable;
Give it a Try !!!


Offline to Online database synchronization programmatically using PHP and MySQL

Plan A:  You charge your client an arm an leg to develop code to keep track of diffs, avoid dup keys, upload data, etc.

Plan B:  Multi-source replication (available at least in MariaDB).

With Multi-source, each offline laptop can be a "Master".  The "online server" is a Slave.  All writes must be done on the Master(s).  Not being able to write to the online Slave should not be an issue, just add a extra, centralized, offline Master in HQ if needed.

As for dup keys -- You must design the schema to avoid any issues like that.  This probably means no AUTO_INCREMENT or FOREIGN KEYS.  It may mean each offline Master has separate tables (or separate a database) for anything that can't live without things that will cause trouble.  But then, that means having some sort of resolution code after each connect.  (The upload is merely Replication doing its thing.)

Plan C:  NDB Cluster.  This is a significantly different Engine available under MySQL.  The set up is complex.  However, it provides a different model for replication -- "eventual consistency".  Rather than hanging on a dup key (etc), you provide rules for what to do in such situations.  These rules are automatically applied when replication leads to a conflict.

Plan D:  Galera Cluster (built into MariaDB 10.x; manually installable on MySQL) -- With this clustering technology, "nodes" can come and go; when they reconnect they automatically get back in sync.  But...  There needs to always be a quorum, that is more than 50% online.  Although you can write to any node, you cannot write to nodes that are not part of the quorum.

Plan E:  Convince the client to stay connected.  Or not do any writes when disconnected.  In this case, the offline machine can be a readonly Slave.  Writes must wait for connection to the remote Master.

Note:  In any case, you should write the application with an explicit eye to what app actions need writes versus what can be readonly.  Suggest you go as far as to have two separate connections -- one for writes and associated reads, one for plain reads.  This will facilitate whatever Plan you pick.

Which do I recommend?  I have not heard enough about your situation to say which.


How to ensure that SQL Jobs run in the correct order within a minimal maintenance window?

You can create these jobs using the Maintenance plan wizard, and direct their work-flow. Open up the SQL management GUI (SSMS) then go to: SSMS>Management>Maintenance Plans>New Maintenance Plan. This will open up a subplan editing area in the right-hand side. On your left-hand size, the tool box should also be visible. You can drag and drop components from the toolbox into the subplan area. A little green arrow will be visible for the component dropped into the sublan area, and you can direct these green arrows in the fashion you want your jobs to run. Eg: Create two TSQL statement tasks A and B. Click on A and direct the green arrow from A to point to B. This will ensure that once A finishes, the flow will continue onto B.
The whole Maintenance plan can then be scheduled with one start time.


SSIS 2008: Illegal characters in path when trying to rename a file

\\\\servername\\csv\\BACKUP\\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

You almost had it here but you missed the leading and trailing quotes.
\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

Now you're missing the leading and trailing quotes and you failed to escape your back slashes.
"\servername\csv\BACKUP" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"
Now you've got the quotes but not escaping the backslashes
"\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"
This works correctly for me in SSIS expression.

FYI
\\servername\csv\BACKUP\"+1+".csv
again you are missing the leading and trailing quotes.  However this won't work because it sees 1 as an integer and you need to convert it.
"\\\\servername\\csv\\BACKUP\\"+"1"+".csv"

The above works.
Normally when doing these sorts of expressions I do it iteratively.  Adding 1 piece to the expression at a time so if it breaks I know where it broke.  Remember you can click "evaluate expression" to see the result.
If the expression evaluates correctly but the package doesn't work then it would be another issue with the package.


Script that alters all stored procedure on the database

Start out with getting all the procedures from sys.procedures

SELECT  * FROM sys.Procedures

Then use EXEC sp_helptext on each to load the text of the procedures.

Search for text to add, add if needed,

Then load the updated text to a variable and execute dynamically.


Slow Postgres updates if OTHER columns are indexed?

We're not creating more data, so existing data shouldn't need to be moved around on the HDD, it just needs to be overwritten

That's not the case.

In order to support rollback and crash-safety, PostgreSQL must write a new copy of every modified row, rather than modifying the row in-place. Twice, actually, because it must be written to WAL (a sequential log for crash recovery) then to the table.

PostgreSQL's autovacuum then comes along later and marks the old row versions as free space that can be re-used.

See the user manual for more information on concurrency control and MVCC.

  the data is not changing size (e.g., rewriting a boolean value)

Irrelevant, because the row must be rewritten anyway.

  only one column is being updated, and it is non-indexed
  there are indexes on other columns

This only matters insofar as it affects HOT updates, where PostgreSQL can potentially avoid writing new index entries for a row update if no indexed columns are modified and there's enough free space on the same disk page (8k block) to store a new copy of the row.

  Why do indexes on columns irrelevant to this query balloon the update time?

In most cases PostgreSQL must add new index entries even if you didn't modify indexed columns, because it has to write a new version of the row to a separate database page.

Setting a FILLFACTOR of 50 or less can help with this, as it encourages PostgreSQl to keep more free space for updates, at the cost of scans having to read and process more data.


Purpose of Try/Catch without Transactions

IMHO Using a TRY/CATCH block you don't need to check every single operation.

DELETE FROM Foo;
IF @@ERROR <> 0
BEGIN
    RAISERROR('Error deleting table Foo', 16, 1);
    RETURN -1;
END

OR

DELETE FROM Bar;
IF @@ERROR <> 0
BEGIN
    SET @ErrMsg = 'Error';
    GOTO ERROR;
END


troubleshooting missing entries using log files

A DELETE (and other writes) will be captured by both logs (assuming you don't have binlog-do/ignore setting in my.cnf).  It would be more efficient to use the binlog, but it needs mysqlbinlog to read it.  The general_log is plain text, but it gets huge fast, since it is capturing everything (including reads).

I think both logs have the query time and the login name.  (I hope you are not doing everything as root.)

Neither can capture anything that happened when they were turned off.  So turn one or both on now, and hope that the naughty query comes along soon.


PK Violation after transactional replication

It looks like that the identity column on the subscriber side is out of sync.  Run DBCC CHECKIDENT(TableA, RESEED) (or just DBCC CHECKIDENT(TableA)) and your problem will be gone.  You can check the documentation here.


Uncertainty about number format in MySQL

It's always a good idea to check the MySQL documentation for quick answers like this.

In a DECIMAL column declaration, the precision and scale can be (and usually is) specified; for example:
salary DECIMAL(5,2)
In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.

The decimal type has these constraints on scale and precision:

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:

M is the maximum number of digits (the precision). It has a range of 1 to 65.

D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.


What is a Scalable Storage Mechanism for large TTL data collections

I have created a very simple demo of how partition switching might work for you:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.largeTable') IS NOT NULL DROP TABLE dbo.largeTable
IF OBJECT_ID('dbo.largeTable1') IS NOT NULL DROP TABLE dbo.largeTable1
IF EXISTS ( SELECT * FROM sys.partition_schemes WHERE name = 'ps_date' ) DROP PARTITION SCHEME ps_date
IF EXISTS ( SELECT * FROM sys.partition_functions WHERE name = 'pf_date' ) DROP PARTITION FUNCTION pf_date
GO

CREATE PARTITION FUNCTION pf_date (DATE) AS RANGE RIGHT FOR VALUES ( '1 Jan 2013', '1 Feb 2013', '1 Mar 2013', '1 Apr 2013', '1 May 2013', '1 Jun 2013', '1 Jul 2013', '1 Aug 2013', '1 Sep 2013', '1 Oct 2013', '1 Nov 2013', '1 Dec 2013' );
GO

-- !!TODO don't use ALL TO PRIMARY, instead create individual files and filegroups
CREATE PARTITION SCHEME ps_date AS PARTITION pf_date ALL TO ( [PRIMARY] )
GO

IF OBJECT_ID('dbo.largeTable') IS NULL
CREATE TABLE dbo.largeTable
    (
    rowId INT IDENTITY,
    someData UNIQUEIDENTIFIER DEFAULT NEWID(),
    dateAdded DATE DEFAULT GETDATE(),
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(),
    ts ROWVERSION,

    CONSTRAINT pk PRIMARY KEY(dateAdded, rowId)
    ) ON [ps_date](dateAdded)
GO

CREATE TABLE dbo.largeTable1
    (
    rowId INT IDENTITY,
    someData UNIQUEIDENTIFIER DEFAULT NEWID(),
    dateAdded DATE DEFAULT GETDATE(),
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(),
    ts ROWVERSION,

    CONSTRAINT pk2 PRIMARY KEY(dateAdded, rowId)
    ) ON [PRIMARY]
GO

-- Create some dummy data
INSERT INTO dbo.largeTable DEFAULT VALUES
GO 5

-- Multiply the data a bit
INSERT INTO dbo.largeTable ( someData, dateAdded, addedBy )
SELECT someData, DATEADD( month, -2, dateAdded ), addedBy
FROM dbo.largeTable
UNION ALL
SELECT someData, DATEADD( month, -1, dateAdded ), addedBy
FROM dbo.largeTable
UNION ALL
SELECT someData, DATEADD( month, 1, dateAdded ), addedBy
FROM dbo.largeTable
GO

-- Have a look at the data
SELECT 'before' s, $PARTITION.pf_date( dateAdded ) p, dateAdded, COUNT(*) AS records
FROM dbo.largeTable
GROUP BY dateAdded
GO

-- Switch out oldest partition with data and truncate it
ALTER TABLE dbo.largeTable SWITCH PARTITION 9 TO dbo.largeTable1
GO

TRUNCATE TABLE dbo.largeTable1
GO

SELECT 'after' s, $PARTITION.pf_date( dateAdded ) p, dateAdded, COUNT(*) AS records
FROM dbo.largeTable
GROUP BY dateAdded
GO

-- Merge the range as no longer required
ALTER PARTITION FUNCTION pf_date() MERGE RANGE ( '1 Sep 2013' );
GO

TRUNCATE TABLE can be a minimally logged operation under certain conditions.  Please consult the Data Loading Performance Guide for a fuller treatment on the topic.  There is also a section on "Deleting All Rows from a Partition or Table".

Good luck!


Value of the Included Column is stored in Leaf Node?

Does that mean the value of surname column is stored in Leaf Node of the Nonclustered Index?

Yes, INCLUDE column values are stored at the leaf node of the index in a non-clustered index. In a clustered index, non-key columns are stored at leaf nodes in the same fashion. A heap works differently because there are no key columns.

Also, that means the value of surname is duplicated because it also stored in Clustered Index.

All non-clustered indexes store duplicate data. For example, the first name column is stored in the clustered index as well as the non-clustered index.

Without a clustered index, the data for all columns is stored in a heap and then data is duplicated in any non-clustered indexes for columns that are key columns or include columns in those indexes.

This is why when designing your indexing strategy, you need to balance performance vs storage/maintenance for the duplicated data.

Check this link for more information about SQL Server Indexes.


Divide a PostgreSQL database into schemas

schemas is used for organizational structure and namespacing. By namespacing I mean that you can have similarly named table in few places. And another feature of using schema is additional access control for that schema. You will have joins depending on your data model. You are using schemas implicitly already. There is a "search path" defined in PostgreSQL database.

So every time you type create table X, you use default schema.
And for queries you should use table aliases so you might add only schema name to every table but if you use alias your queries should be the same with or without schema.

I don't have experience with above feature (multitenant db and amazon and ...)


Import asp.net / SQL Server 2012 website database to local SQL Server 2014 express

This could be caused by any number of issues.

To help get to the bottom of this I would, In SSMS open a new query window and run the following command

RESTORE DATABASE [DatabaseNameHere]
FROM DISK='Path to the backup file here'

e.g. if my database was called AdventureWorks and the backup was located in c:\Temp\Adventureworks.bak the TSQL would be.

RESTORE DATABASE [AdventureWorks]
FROM DISK='c:\Temp\Adventureworks.bak'

The error produced will give you a much better indication as to what the issue is.