When executing multiple commands in a script in pgAdmin they are automatically wrapped into a transaction. You can explicitly begin and commit transactions, but not with commands that won't run in a transaction context. The manual:

  DROP DATABASE cannot be executed inside a transaction block.

Similar in psql when called with -c command. The manual:

  If the command string contains multiple SQL commands, they are
  processed in a single transaction, unless there are explicit
  BEGIN/COMMIT commands included in the string to divide it into
  multiple transactions.

However, when fed to psql via standard input:

  This is different from the behavior when the same string is read from
  a file or fed to psql's standard input, because then psql sends each
  SQL command separately.

  Because of this behavior, putting more than one SQL command in a
  single -c string often has unexpected results. It's better to use
  repeated -c commands or feed multiple commands to psql's standard
  input, either using echo as illustrated above, or via a shell
  here-document, for example:

psql <<EOF
\x
SELECT * FROM foo;
EOF

So you can use psql with standard input in default autocommit-on mode.
Else, you can only run separate commands.

You could use the shell-command dropdb - or write a shell-script with it that drops multiple databases.

BTW, the only difference between what you can run from pgAdmin and psql are the meta-commands of psql - which are not SQL. Those are interpreted by psql and not the database engine. To switch between meta-commands and SQL, use the separator meta-command \\. There is an example in the manual.


Usually, we use SCD-type-4 when a dimension(SCD Type 2) grows rapidly due to the frequently changing of its attributes. These frequently changing attributes will be removed from the main dimension and added in to a new one known as Mini-Dimension.

==> Mini Dimension do not store the historical attributes, but the fact table preserved the history of dimension attribute assignment

A good example is illustrated here : link-SCD4


Comparing DISTINCT, GROUP BY and ROW_NUMBER() in SQL Server 2008 with data warehouse workloads

In my experience, an aggregate (DISTINCT or GROUP BY) can be quicker then a ROW_NUMBER() approach. Saying that, ROW_NUMBER is better with SQL Server 2008 than SQL Server 2005.

However, you'll have to try for your situation.
Compare query plans, and use Profiler and SET to capture IO, CPU, Duration etc

For a lot of background, see these SO questions:

Why are logical reads for windowed aggregate functions so high? (follow the links from Martin Smith)
can I get count() and rows from one sql query in sql server? (note the comments in the answers from me and Chris Bednarski)

Finally, do you need the ROW_NUMBER approach? It looks like you're fixing a problem caused by de-normalisation.

And some notes:

shouldn't YearID be in the GROUP BY or PARTITION BY?
Won't DISTINCT give different output?
Are these columns indexed?


ODAC OLE DB provider "OraOLEDB.Oracle" for linked server supplied inconsistent metadata for a column

Even though Oracle support note 207303.1 states that the 12c client works with 11gR2 client, this error seems to be caused by a compatibility issue between the 2 different versions.  To fix, I followed these steps:

Uninstall the 12c ODAC version, by opening an Admin command prompt, changing directories to the installed Oracle home folder, and running: uninstall.bat all d:\oracle, where d:\oracle is my Oracle home location.
Remove the PATH environment variables
Reboot
Install the 11.2.0.4 ODAC version "64-bit ODAC 11.2 Release 6 (11.2.0.4.0) Xcopy for Windows x64", download the "ODAC112040Xcopy_64bit.zip" option.  Make sure you install via Admin command prompt (the install command is: install.bat all d:\oracle11gr2 odac11gr2).  Download from: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
Add back the PATH environment variables for d:\oracle11gr2;d:\oracle11gr2\bin
Reboot again


You might be able to do something like this.

SELECT t.*
FROM (SELECT * FROM table ORDER BY id DESC LIMIT 120) AS t
ORDER BY t.id ASC ;

Another alternative would be to load the results into a temp table, then query that for the desired order.


In trying to setup back up device, net use command throws operating system error 53 when passing in a unc

You have to give it both the server name and a shared folder, i.e., \\Server\SharedFolder.


How to configure PG text search dictionary through sql

By design (it's hardcoded) tsearch_data directory is in SHAREDIR. If you want to change SHAREDIR you should use your own compiled version of PostgreSQL. If you are interested, you can read the documentations' Installation from Source Code section and If you still want to change tsearch_data, well PostgreSQL is opensource, you can change Makefile and ts_utils.c and again have your own version of PostgreSQL by compiling it.

You can also use symbolic link.


The problem was I was using the variable TWO_TASK. Once I unset the variable, I nolonger had issues logging in a sysdba.

$ unset TWO_TASK
$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 6 02:51:55 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup;
ORACLE instance started.

Total System Global Area 1707446272 bytes
Fixed Size          1336988 bytes
Variable Size         989858148 bytes
Database Buffers      704643072 bytes
Redo Buffers           11608064 bytes
Database mounted.
Database opened.
SQL> exit

I found the answer here.


return continuous set of IDs based on two other columns

You can do something like this..

SELECT
  array_agg(id),
  code,
  segment,
  grp,
  count(*)
FROM (
  SELECT
    id,
    code,
    segment,
    count(is_reset) OVER (ORDER BY id) AS grp
  FROM (
    SELECT
      id,
      code,
      segment,
      CASE
        WHEN
          code <> lag(code) OVER (ORDER BY id)
          OR segment <> lag(segment) OVER (ORDER BY id)
        THEN 1
      END AS is_reset
    FROM foo
  ) AS t
) AS g
GROUP BY code, segment, grp;

   array_agg   | code | segment | grp | count
---------------+------+---------+-----+-------
 {11}          |    1 |       2 |   6 |     1
 {10}          |    0 |       2 |   5 |     1
 {1,2,3}       |    0 |       0 |   0 |     3
 {7}           |    1 |       1 |   2 |     1
 {4,5,6}       |    0 |       1 |   1 |     3
 {16}          |    0 |       3 |   8 |     1
 {8}           |    0 |       1 |   3 |     1
 {12,13,14,15} |    0 |       2 |   7 |     4
 {9}           |    1 |       2 |   4 |     1
(9 rows)

To query this, just search for code = 0 AND count >= 4.

Explanation

First we generate a reset

SELECT
  id,
  code,
  segment,
  CASE
    WHEN
      code <> lag(code) OVER (ORDER BY id)
      OR segment <> lag(segment) OVER (ORDER BY id)
    THEN 1
  END AS is_reset
FROM foo

 id | code | segment | is_reset
----+------+---------+----------
  1 |    0 |       0 |
  2 |    0 |       0 |
  3 |    0 |       0 |
  4 |    0 |       1 |        1
  5 |    0 |       1 |
  6 |    0 |       1 |
  7 |    1 |       1 |        1
  8 |    0 |       1 |        1
  9 |    1 |       2 |        1
 10 |    0 |       2 |        1
 11 |    1 |       2 |        1
 12 |    0 |       2 |        1
 13 |    0 |       2 |
 14 |    0 |       2 |
 15 |    0 |       2 |
 16 |    0 |       3 |        1
(16 rows)

Then we create groups,

  SELECT
    id,
    code,
    segment,
    count(is_reset) OVER (ORDER BY id) AS grp
  FROM (
    SELECT
      id,
      code,
      segment,
      CASE
        WHEN
          code <> lag(code) OVER (ORDER BY id)
          OR segment <> lag(segment) OVER (ORDER BY id)
        THEN 1
      END AS is_reset
    FROM foo
  ) AS t;
 id | code | segment | grp
----+------+---------+-----
  1 |    0 |       0 |   0
  2 |    0 |       0 |   0
  3 |    0 |       0 |   0
  4 |    0 |       1 |   1
  5 |    0 |       1 |   1
  6 |    0 |       1 |   1
  7 |    1 |       1 |   2
  8 |    0 |       1 |   3
  9 |    1 |       2 |   4
 10 |    0 |       2 |   5
 11 |    1 |       2 |   6
 12 |    0 |       2 |   7
 13 |    0 |       2 |   7
 14 |    0 |       2 |   7
 15 |    0 |       2 |   7
 16 |    0 |       3 |   8
(16 rows)

Now you'll notice that the groups a fn(code,segment), reseting when either changes. Now we group by the grp, and do a count(*), but because you want the ids we run them through array_agg and store them as an array.

Note, you say and the sequence of IDs is not broken. I'm working off the assumption that this is not what you meant. PostgreSQL doesn't have a concept of gapless sequences, so that's not a good idea anyway. I assume you wanted what I've done.


1024 partitions means 1024 "sub-tables".  Certain operations will open all 1024.
A table (or partition) is implemented using files in the OS.  You are at the mercy of the OS speed or sluggishness.
If you have WHERE ClientId = 123 that should lead to "partition pruning", which leads to opening the one partition, then scanning the entire partition.
INDEX(ClientId), without partitioning, would be almost as good.

This would be better:

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ClientId, id),
INDEX(id)

Now the data is clustered on ClientId, there is no extra thing to "open", and the performance will be better.

Please provide the main queries you have so that I can check whether I have messed up other cases.

Other issues...

AccountId BIGINT,
AccountNumber VARCHAR(250),

sounds grossly redundant.  Is there a 1:1 mapping between that Id and that Number?  Then the pairs should be in another table, and only the Id should be in this table.  Ditto for Devices.

Making that change would significantly shrink this table, thereby making operations somewhat faster.

Meanwhile, learn about JOIN in order to get the ...Number from the ...Id.

More on partitioning.


"Storage Controllers" <> "RAID".  RAID is a configuration, not hardware.

For example, RAID 5 refers to having at least 3 physical disks typically attached to a disk controller such that stripes of data and parity are written across all three disks any time data is written to the array.  If you had 3 x 76GB drives, a RAID-5 array would have 76 x 2 = 152GB of usable capacity since 1/3 of the total space is dedicated to parity data.  The parity data is used to calculate the missing data if any one of the drives in the array fails. The RAID array looks like a single physical disk to the operating system installed on the machine.

For SQL Server, RAID-5 is typically counter-indicated since calculating and writing the parity information causes writes to be slower than with RAID-10.  RAID-10 consists of at least 4 drives, 2 mirrored sets of RAID-0 arrays.  Since RAID-10 does not have to calculate parity data, it is typically faster than RAID 5, and since the array is mirrored, it can survive up to 2 simultaneous drive failures.  However, capacity of a RAID 10 array is smaller than a RAID 5 array consisting of the same number and size of drives.

To answer your question, no, these controllers do not by themselves mean you have any RAID at all in the system.  Since the server you are looking at is a virtual machine, you would need to find out from the system administrator of the physical host machine what kind of storage it has.


Get all records from inner joined tables if second table does not have data

I think the condition ON a.ID = ISNULL(b.ID,a.id) is not the best way to do this. It may lead to inefficient plans and may return unwanted results (according to your specifications). If the tblxyz table has rows with NULL values, you'll still get all the rows from table a (and possibly multiple times).

I would write the query without using the ISNULL() function, like this:

SELECT a.*
FROM tblabc a
    INNER JOIN #tblxyz b
    ON a.ID = b.ID
    OR NOT EXISTS (SELECT * FROM #tblxyz) ;

or like this:

SELECT a.*
FROM tblabc a
    INNER JOIN #tblxyz b
    ON a.ID = b.ID

UNION ALL

SELECT a.*
FROM tblabc a
WHERE NOT EXISTS (SELECT * FROM #tblxyz) ;


Database Exceptions with Jetty 8.1.0 with BTM

It looks like the null in the parameter variable is single quoted, like 'null'. In the context of SQL query nulls are going without quotation.

This is how it should be:

=# select 1 from t where i = null;
 ?column?
----------
(0 rows)

And this is what you have:

=# select 1 from t where i = 'null';
ERROR:  invalid input syntax for integer: "null"
LINE 2: select 1 from t where i = 'null';

So you either need to remove any quotation from the parameter variable or to use parameter binding with the database adapter. The second option is preferable.


Find 13th min value in table group by a column


SQL Server Storage for multiple data files

Space will not be the only factor in where you place the new space. If you are talking about expanding the capacity of the existing drives you should look at what do the underlying volumes have backing them. I.e how many physical drives are assigned to the drive arrays and what the RAID levels are for those arrays.

For HDDs the more drives you have in an array the better it will perform. The amount of improvement will be a "Your mileage will vary" answer based on what your hardware is and how it is configured.

As an example of how to start at making that determination let assume the following:

Drive G has 20 1TB drives in a RAID 10 configuration.
Drive H has 22 146GB drives in a RAID 10.

Your new drives are another 28 146GB drives. You might want to add those to the H drive array as they are the same drive spec.

If your new drives are not the same to the drives already being used in one of the two arrays I would do some capacity planning to see how to break down the new drives into multiple arrays and spread some of the existing data files across things based on the access pattern of your data.

You can also check out some posts from Brent Ozar and team on disk io

For SSD disks there is a completely different way of looking at things. Check out the video from Brent


Role db_ddladmin not working properly on SQL Server 2008

EDIT:

I re-read your question and I may have got it incorrect before. Looks like you are running the scripts using SSMS instead of using T-SQL scripts. I verified that it works when using T-SQL scripts and doesn't work when using SSMS GUI. To get around this issue, you may want to use T-SQL scripts.

This is a known issue and looks like this is fixed in DENALI CTP3 and I can confirm that.

Ref: https://connect.microsoft.com/SQLServer/feedback/details/467582/db-ddladmin-role-doesnt-allow-use-of-design-functions-in-ssms

Just verified the (correct) behavior also works in SQL Server 2008 R2. I don't have a SQL Server 2008 instance to verify unfortunately.


What you can do is something like this (see the fiddle here):
Table:
CREATE TABLE t
(
  user VARCHAR (10) NOT NULL,
  permission VARCHAR (10) NOT NULL
);

Data:
INSERT INTO t VALUES
('user1', 'a'),
('user2', 'a, b, f'),
('user3', 'b, c'),
('user4', 'e, f'),
('user5', 'f, g');

Query:
SELECT * FROM t
WHERE permission LIKE '%a%'
OR    permission LIKE '%b%'
OR    permission LIKE '%c%'
OR    permission LIKE '%d%'
OR    permission LIKE '%e%';

Result:
user    permission
user1   a
user2   a, b, f
user3   b, c
user4   e, f

user5  is excluded because his permissions are 'f,g' and these don't appear in the sequence a-e.
p.s. welcome to the forum!


How to inner join some tables and outer join others?

It sounds like you are looking for a FULL [OUTER] JOIN. Per documentation:

  FULL OUTER JOIN

  First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row
  is added with null values in columns of T2. Also, for each row of T2
  that does not satisfy the join condition with any row in T1, a joined
  row with null values in the columns of T1 is added.

SELECT *
FROM   users           u
JOIN   user_products   up ON u.id = up.user_id
                         AND u.id = 1
JOIN   products        p ON p.id = up.product_id
FULL   JOIN categories c ON c.id = p.category_id;

This returns all categories and also returns all user-product combinations for the given user_id.

If you are enforcing referential integrity with foreign keys and products.category_id is defined NOT NULL, so that every product is assigned to an existing category (not in your question), you can replace the FULL JOIN with a RIGHT JOIN.

Plus, either way, the condition to select a specific user has to move to a subquery or (simpler) to a JOIN condition between the first three tables. It has to be applied before the last table categories is joined.

SQL Fiddle.


SHOW CREATE TABLE real;  -- to avoid typos
edit it change name to new and to add DATA_DIRECTORY= ... or whatever
perform the CREATE TABLE new ...
INSERT INTO new SELECT * FROM real; -- single, fast, step.  But not instantaneous
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

Step 5 is the main added "elegance".  It is atomic and instantaneous; it won't interrupt the live system.  Also, it give you a chance to test the new real before DROP TABLE old.

If this is InnoDB, you must have innodb_file_per_table ON before step 3.  Was it ON when the old real was created?  If not, ibdata1 will not free up any space on the old disk.  Resolving this takes several more steps.

(My steps should work fine for a MyISAM table.)


Restoring an SQL Server 2012 MDF to SQL Server 2008

Your only avenue is to export the database and then import it back on SQL 2008. SSMS has an Export/Import Wizard you can give a shot. Other tools exists from 3rd party vendors like Red Gate's SQL Compare to copy the schema and Data Compare to copy the data. I'm sure there are more. The gist of it that you need to go through some sort of export/import, SQL Server does not support any downgrade path for the MDF format. Obviously, you must not had rely on new SQL Server 2012 features like columnstores.


Startup procedure for availability replica becoming primary

Since you are talking about Availability Groups, there isn't an instance startup like you're thinking about as there is with failover cluster instances.  Because all replicas/instances are online (during normal operation of course) you can't leverage the same implementation of running a "startup procedure".

Off the top of my head, the way to do this would be one of a couple of ways.

First off, you could have a scheduled SQL Server Agent job that routinely checks to see if the local replica is currently the primary in the availability group.  And then disable whatever jobs you need to given the certain condition.  I don't recommend this method.

But my recommendation would be to have this in your existing SQL Server Agent jobs.  So for instance, in your jobs you would have logic that does the check to see if the local replica is currently primary, and if so do what the job currently does.  Something like this:

if (
    select role_desc
    from sys.dm_hadr_availability_replica_states
    where is_local = 1
) = 'primary'
    begin
        -- do your job's task
    end
else
    begin
        -- don't do anything (this 'else' block can be omitted)
        -- or if you want to handle accordingly, do so
    end

So in this example, no jobs are explicitly disabled.  They'll just do a quick conditional check to see if it should actually do something in a particular role. This is basically the way that backups are typically designed in an AG.


Why is my Mac install of Postgres not tokenising Thai words?

My question is, how do I figure out what is different

show lc_ctype;

in both databases

  and how do I
  make my mac install of PG work like the Linux one?

Recreate the database on the mac with a proper utf-8 lc_ctype.

Related: Postgres to_tsvector is case sensitive on mac os


ORA-04031: unable to allocate 32 bytes of shared memory

check SGA region sizes - buffer pool, dictionary cache, library cache.

Also check the dynamic view v$sga_resize_ops. There you might find a reason.
Also these version of Oracle had a bug, which caused that Oracle could not get rid of the old exec plans if there were too many of them. You should upgrade to terminal patchset 10.2.0.5.

You should also consider setting parameter cursor_sharing, but this might not help in some cases. 1st you should find a culprit, typically this is caused by some ORM framework like Hibernate.

Update:
There is also memory leak in early release 12c RAC cluser. So when you execute this:

SQL> select * from ( select name, bytes/1024/1024/1024 from v$sgastat where pool ='shared pool' order by 2 desc ) where rownum <11;

NAME                       BYTES/1024/1024/1024
-------------------------- --------------------
ges resource dynamic                 7.42374295
value block free list                4.61375274
free memory                          2.66423168
gcs resources                        1.78276435
ges enqueues                         1.45691999
gcs shadows                          .990424648
db_block_hash_buckets                .343753815
gcs res hash bucket                        .125
gc name table                            .09375
KTSL subheap                         .086000413

So you can see that 7GB of SGAs RAM are occupied by some clusterware buffers.

But most common source of these errors is caused by enormous amount of hard parsing causing library cache growth.


Run MySQL replication by schedule or on demand

On your Slave issue the STOP SLAVE, START SLAVE commands to stop / start replication on Demand. If you want to run it to a schedule write a batch file / cron to STOP SLAVE and START SLAVE at the required times. I'm assuming you have a good reason for doing this?


Sounds like some code somewhere is performing SET @@max_allowed_packet=1024;.

You could turn on the "general log" to find out where it is coming from.


Recreate Indexes on 1 billion record table

Now, the best way to answer this is to set up a smaller test database of a few gigabytes. It will accurately predict the best technique.

Your technique (2) is horrible because step 1 leads to all NCI's being rebuilt to work together with the resulting heap table. Don't do that.

The good news is that DROP_EXISTING works with changing the index definition including columns and partition scheme:

  The existing index is dropped and rebuilt. The index name specified
  must be the same as a currently existing index; however, the index
  definition can be modified. For example, you can specify different
  columns, sort order, partition scheme, or index options.

And this is the reason why this feature exists. It is not equivalent to first dropping, then creating.

Here are the basic techniques (all of yours are horrible due to the intermediate heap table):

Drop all NCIs, rebuild the CI WITH (DROP_EXISTING = ON), create missing NCIs
Drop all non-aligned NCIs, rebuild the CI WITH (DROP_EXISTING = ON), create missing NCIs
Rebuild the CI WITH (DROP_EXISTING = ON)

(1) is very light on the transaction log. It is not maximally efficient. (3) is the simplest - it will cause all indexes to be rebuild as aligned indexes. This is not so good because you will have to rebuild those that you don't want to align.

(2) is probably best in all cases. I can't think of any case where it might be worse-off. It has the advantage of reusing existing indexes for a more efficient rebuild (this is possible because you didn't just drop all NCIs). NCIs which you want to be non-aligned don't benefit from this method and would be rebuild needlessly. So get rid of them before starting the procedure.

All of this actually works online although you said you don't need that.

Again, just try it out on a sample database. Certainly you shouldn't entrust the availability of your database to my advice. I suggest you take the ideas from this post and try them out.


How to mention declared integer in where condition using dynamic sql?

IMHO the best option to deal with dynamic SQL is:

Build your command
PRINT the resultant text.
Copy & paste and execute it in a new query window just to check it.

Example:
CREATE TABLE t (id int identity, foo varchar(10));

INSERT INTO t VALUES ('RED'),('GREEN'),('BLUE');

DECLARE @cmd NVARCHAR(MAX);
DECLARE @param VARCHAR(10) = 'GREEN';

SET @cmd = 'SELECT id, foo FROM t WHERE foo = ' + @param

PRINT @cmd;

Then execute the command:
SELECT id, foo FROM t WHERE foo = GREEN

SELECT id, foo FROM t WHERE foo = GREEN

Msg 207 Level 16 State 1 Line 1
Invalid column name 'GREEN'.

db<>fiddle here


When running the second statement, each session has already inserted rows in adt.[ClientBusinessUnitBehaviour], and has those new rows exclusively locked.

The UPDATE statement may need to read the new rows inserted by the other session to discover if they have a NULL RowVersion column.


Which database engines will allow me to GRANT/REVOKE on a specific column?

SQL Server 2000, 2005, 2008 has this capability

GRANT { ALL [ PRIVILEGES ] }
       | permission [ ( column [ ,...n ] ) ] [ ,...n ]
       [ ON [ class :: ] securable ] TO principal [ ,...n ]
       [ WITH GRANT OPTION ] [ AS principal ]

ADDED

In SQL Server 2005, 2008 it is possible to encrypt a column of data by using symmetric encryption, see B.Symmetric encryption that includes an authenticator. This variant returns a NULL value, just as if the wrong key was used.


MySQL using 100% of CPU with 1500 active connections

You may have encountered a Von Neumann Bottleneck.

CPU usage is expected to increase as the number of connections increases.  As the connections increase the number of active queries should also increase.  Each query will use some CPU.  Eventually, you will have enough queries to use all available CPU.

There are some additional factors that while use more CPU as the number of concurrently running queries increase:

Time spent waiting for resource locks.  This may involve a lock spin which will consume additional CPU.
Context switching when the O/S switches the currently running query.  This is a side effect of the multi-processing that allows you to run more than one query at a time.

Running a program like sar may provide more details on where the CPU is being used.


Oracle: Performance centric ways to notify user on reaching "logical" lock expiration event

seems like a serious design flaw. probably resulting from a pile of flaws. why is it that the application knows when the lock should be expired, and yet it checks again and again to see if it has expired? this means that the time for expiry is meaningless.
if you can change the complete design, the current situation is probably resulting from the fragmentation of the data handling parts of your application.
if you can't, you still need to realize the problemof a going for a time-based lock of a rigid constant (which goes as high as 30 seconds, a performance red flag), and in the same time creating a propagating mechanism notifying the release.
in my opinion, The much-less-than-perfect solution, in your case, is that those waiting for the logical lock to be released should sleep for the time-frame in which the lock is held, and then they should access and see if the record is still locked. if it still not free, they should probably sleep themselves into a random short period (realistic in your app's terms), before checking again to see if the record is locked. some upper threshold should be set for these waits as well.
in general, what you need is a messaging system between waiters.


Making sure balance field is always consistently updated with triggers

First, this UPDATE is buggy:

 UPDATE "public"."Users" SET balance=(NEW.amount+currentBalance);

because it updates the entire "Users" table, which is obviously not what's wanted.

Let's assume that a WHERE clause is added and the code becomes:

DECLARE
currentBalance INTEGER;
BEGIN

currentBalance = (SELECT balance FROM "public"."Users" WHERE id=NEW."UserId");

UPDATE "public"."Users" SET balance=(NEW.amount+currentBalance);
  WHERE id=NEW."UserId"

RETURN NEW;

This code is subject to a race condition under the default isolation level (Read Committed). Say this transaction is T1 and another one is T2.
If T2 was updating the balance for this user, and it commits
between the SELECT and the UPDATE, the UPDATE of T1 will overwrite the row, leading the change of T2 being lost and the balance being wrong.

To implement this logic in a concurrent-safe way, the recommendation of the SQL standard is to use transactions at the serializable isolation level. See Transaction Isolation in PostgreSQL documentation. Alternatively, you may implement your own locking strategy at the less isolated levels to avoid race conditions, but it's harder to get it right.

  Q: Do I right in assumption that inserting into Bets table is one
  transaction along with trigger function updating balance, so in case
  something goes wrong with insertion into Bets, balance wont be updated
  OR if something wrong goes with balance updating, new record wont be
  inserted into Bets table

Yes, but that's a virtue of transactions, not of triggers.
Normally, you get atomicity by putting the list of SQL statements between a BEGIN...COMMIT pair, not by moving them into a trigger. The point of triggers is to implement side-effects, not the main logic. Quoting Tom Kyte's The Trouble with Triggers excellent post:

  Triggers should be viewed with a skeptic’s eye. If the trigger is in
  place to enforce entity integrity, be very suspicious of it. Think
  about multiuser conditions. Think about what happens when two or three
  people operate on similar data at, or at about, the same time.

  [...]

  Triggers should be the exception, not the rule. They should be used
  only when you cannot do something any other way. Given the concurrency
  issues, the problems with doing nontransactional operations in them,
  and the maintenance problems, triggers are something to use sparingly,
  if at all.


timestamp is not a valid type when using ACCEPT. Use date.

SQL*Plus® User's Guide and Reference - ACCEPT

SQL> !cat 1.sql
ACCEPT v_cheindatetime date FORMAT 'DD-MON-YY HH:MI:SS' PROMPT 'Enter Checkin Date and Time : '
ACCEPT v_cheoutdatetime date FORMAT 'DD-MON-YY HH:MI:SS' PROMPT 'Enter Checkout Date and Time : '

--execute the INSERT statement
INSERT INTO booking VALUES
('&v_cheindatetime','&v_cheoutdatetime');

SQL> @1
Enter Checkin Date and Time : 06-AUG-16 12:00:00
Enter Checkout Date and Time : 07-AUG-16 12:00:00
old   2: ('&v_cheindatetime','&v_cheoutdatetime')
new   2: ('06-AUG-16 12:00:00','07-AUG-16 12:00:00')

1 row created.


MySQL - run SELECT statement on another server without defining the table structure

How is a "linked server" connection different than any other connection defined by a MySQL client?

  In MS SQL Server, however, I can run any SQL statement against a
  linked server. Is it possible to do the same thing in MySQL?

Maybe I'm missing something but yes, just setup a connection.

mysql -u <user> -p <password> -D <database> -H 127.0.0.1

import MySQLdb
db = MySQLdb.connect(host="localhost", user="<user>", passwd="<password>", db="<database>")

You could go so far as setting up a reverse tunnel with SSH.

mysql -u <user> -p <password> -D <database> -H 127.0.0.1 -P 33061

Other than that, FEDERATED tables are the closet thing.

I must be missing something.


Moving the binary location of MySQL for replication

Yes, you have two options.  Both require none of the data changes in any of the files during copying.

Completely shutdown the server you are going to clone.  Copy it over, make relevant .cnf changes (especially server-id), start up the new instance.
If you're using LVMs you can take an LVM snapshot while the server is running and then copy that snapshot over.

If you're doing this to get a slave running, in both cases you'll want to have run show master status before shutting down/taking the snapshot so you'll know what to CHANGE MASTER TO when spinning up the new slave.  I would recommend setting read_only=1, then check the position.  If going the snapshot route you can set read_only=0 so the master can continue to take writes.


Mysql import csv not complete by Mysql bench

It all comes down to the contents of your CSV-file - and that in turn is determined by the used program that created it in the first place ...

Check if all lines have the desired number of values per line, in combination with:

Check if escape-characters (if any) are there .. and you give them in mysql-workbench as well ...

special case for Microsoft Excel - it used to create ONE escape sequence for first occurance, 2 Escape sequences for 2nd occurance and so forth ... I had to use a php-script to import them, where I had to hand-twiddle until mysql was satisfied with the input-format

compare those successful lines with other lines in the csv - What is the difference between them?


Low Cardinality of Index performing better than High Cardinality?

The between condition is short form for:

 ... range_from <= $value
 and $value <= range_to

In this context range_from and range_to have an effective cardinality of 2, in range or out of range.

Given your indexes the range_from needs to be searched for all values up to and including $value.  If value is close to the end the possible values, the number of records that need to be checked is quite high.  If you are looking for recent records, this is will be close to all the records.

Using the index with the depot column first, far fewer records match the range_from value.  If you have 100 depots, you have 1% of the records to check compared to not using it.  Depots with few records will be faster than those with more records.  Your results on table A will vary depending on the value of $other.

In either case, the query could be completed within the index.  Without depot in the index, the query optimizer might decide to scan the table rather than the index.  Some SQL dialects allow a hint to the optimizer indicating the number of rows the query will match.  This can influence the resulting query plan.

While you consider the cardinality low, from an optimization standpoint, it is quite high it reduces the number of index rows to be scanned by a factor of 100.

While the cardinality of the from_range may be high from a values standpoint. When running a range search it is of little value in searching for records.  For high values (which may be the most common case), it will match close to 100% of the records.  Its high cardinality, is of more use when running a range check of the form:

where from_range between $low and $high

The index may be more useful if the range columns are ordered range_to, range_from.


How to represent aggregates in a data warehouse

You should not change your date or pizza dimensions. What you need is a fact table that keeps your aggregate data.

Therefore you will have Date dimension with these columns:

DateId -Pk

Day

Month

Quarter

Year

DateName

....

Your Pizza dimension will be like:
PizzaId -Pk

PizzaName

PizzaSize

PizzaType

...

And now your fact table which will be linked to the dimensions above using their IDs:

Create Table PizzaSale (keeps the aggregate data):
Id

PizzaId -Fk to Pizza dimension

SaleMonthId -Fk to Date dimension

Pizza_total_sale -Aggregate value

... Other aggregate values if needed

So as you can see by joining these 3 tables you can get the number of sold pizzas per month. The type can be got from Pizza dimension.


What reason could cause AWS RDS connections to spike

If you run show processlist; it'll show you all the connections that are running against your DB.
Running show status like 'Conn%' can show how many connections are active

You may think that a high number of connections would increase the CPU but ultimately it depends what those connections are trying to accomplish, if they aren't doing much but are preventing you're main application from running their connections then the normal processing level will go down while you are not running your normal processes

The annoying thing is to be able to run the show processlist command you need to actually be connected to the DB which you wont be able to as you're at max connections.

A slight work around is to increase your max connections (if you're able to) and set up some monitoring somewhere that runs every so often (every 5 minutes would have caught that) and when you're over 50 connections, run a command that dumps out all the active connections somewhere so you can review later.


"Merging" grouped rows (column-wise, picking the first non-null value, ordered by another column)

You can use the FIRST_VALUE analytic function for this:

select distinct
  record_group,
  first_value(col_a ignore nulls) over
    (partition by record_group order by record_level
      rows between unbounded preceding and unbounded following) as col_a,
  first_value(col_b ignore nulls) over
    (partition by record_group order by record_level
      rows between unbounded preceding and unbounded following) as col_b,
  first_value(col_c ignore nulls) over
    (partition by record_group order by record_level
      rows between unbounded preceding and unbounded following) as col_c
from
  tbl_record
order by
  record_group
;

RECORD_GROUP         COL_A                     COL_B COL_C
-------------------- -------------------- ---------- --------------------
group1               foo2                          0 bar1
group2               foo3                          5 bar4
group3               foo6                          1

We find the first value (FIRST_VALUE) for each row, ignore nulls (ignore nulls), for the current record_group "partition/group" (partition by record_group), for the whole "partition/group" (rows between unbounded preceding and unbounded following) based on the order of record_level (order by record_level), and get rid of the duplicate rows with DISTINCT.


Databse design for a part/subassembly list

To reproduce this structure, you need an Assembly table referencing itself through a ParentAssemblyID and a Part table. The diagram in Access looks like this:

Note that the assembly table appears twice in this diagram. But there's really only one unique table named Assembly in the DB.

AssemblyID and PartID must be declared as primary key (PK). Its easiest to declare them as AutoNumber column.

In the Realtionships diagram, insert the Assembly table twice, so that you can draw a relation between them. Right click the relation lines and check the boxes "Enforce Referential Integrity" and "Cascade Delete Related Records".

Your example data would have to be entered like this

Assembly
-----------------------------------------------------
AssemblyID  Assembly                 ParentAssemblyID
----------  -----------------------  ----------------
  1         Main Assembly
  2         Group 1: SubAssembly        1
  3         Group 2: SubAssembly        1
  4         Group 1: SubSubAssembly     2
-----------------------------------------------------

Part
---------------------------------
PartID  AssemblyID  Part
------  ----------  -------------
  1       1         Part 1: Rivet
  2       1         Part 2: Rivet
  3       2         Part 1: Rivet
  4       2         Part 2: Bolt
  5       2         Part 3: Bolt
  6       4         Rivet
  7       4         Rivet
  8       3         Rivet
  9       3         Bolt
---------------------------------

Answer to follow-up question: "How would I structure it if I wanted the same part to be usable in many assemblies?"


MySQL Error : 1422 Explicit and Implicit Commit is not allowed in SP and Trigger

(Too much for a Comment...)  (Working backwards...)

LIMIT 1, but no ORDER BY??  You don't care which item you get?

SELECT .. INTO badgeId, but then don't really use it?  Perhaps the subsequent SELECT should have WHERE name  = 'Golden Ball'?

Instead of doing IF EXISTS, try to cause the final SELECT to return nothing.  That would have the same effect, and will probably eliminate the 'need' for a temp table.

Does that IF EXISTS do anything other then see if there are any rows in tempname??  If so please explain.

Is d.user_id the PRIMARY KEY of team_trophies?  If not, the GROUP BY may be improper.

No mention of OLD. or NEW.??

I suspect the INSERT .. SELECT could be simply use the original SELECT, plus an additional WHERE clause based 'Golden Ball', but I am too lost to attempt to rewrite it.


How to get a last DML operation in Oracle 10g-11g

With this query you will see your last DML event by loonking on last change , most recent date is the last operation

  SELECT TABLE_OWNER,
   TABLE_NAME,
   INSERTS,
   UPDATES,
   DELETES,
   TIMESTAMP AS `LAST_CHANGE`
FROM  ALL_TAB_MODIFICATIONS
WHERE TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY')
ORDER BY LAST_CHANGE DESC;

But the only way to use it its separating the DML events in differents querys or adding three differents last_change per dml event.
In any case you tell me your doubts


Upper or Lower Case Optimisation on MS SQL Server

I would say it depends on your install of SQL server. The default ones that I have seen are case insensitive.

Run select serverproperty('SQLsortorderName') if you get back nocase_iso it would mean that your searches are case insensitive. So using UPPER would give you the same results, but it would add a bit more work on the CPU.


Storing multiple tables by type vs one table

As long as all the data have (roughly) the same attributes, and the only difference is the type, storing them in a single table is fine.
To make your query fast, create an index on type. Then two things can happen:

there are few enough rows of that type that you get an index scan (or bitmap index scan in PostgreSQL)

the type occurs often enough that PostgreSQL opts for a sequential scan

In both cases you won't be much slower than with many small tables.
The final decision might depend on the number of tables (having 10000 tables in a database is not much fun) and the type of query you have:

The following can be a static query with parameters:
SELECT ... FROM mytable WHERE type = 'type1';

while the following requires you to compose an SQL statement for every query:
SELECT ... FROM mytable_type1;

If you want to query several types at once, it is nicer to write
SELECT ... FROM mytable WHERE type IN ('type1', 'type2', 'type3');

than having to write
SELECT ... FROM mytable_type1
UNION ALL
SELECT ... FROM mytable_type2
UNION ALL
SELECT ... FROM mytable_type3;


Proper procedure for migrating a MySQL database to another Debian machine?

That is a normal procedure for setting up a new server from old database, so there's nothing wrong with this. Probably the reason there is no mention of the passwords getting overwritten is admins know the old passwords so can easily update them on the new server.

One thing you can do is modify the dumpfile to use the new passwords for root and your Debian admin user prior to loading it into the new server.


Support
The 5.7 release is still maintained, and has support until October 2023. So there is nothing that makes it a bad idea to use it for Production.
However, if you are starting from scratch, using 8.0.23 (the newest release in the 8.0 chain) will get you some new features, and support until April 2026.
Security
If 8.0 was bleeding edge, a case could be made that it is likely to have more yet-to-be-discovered security vulnerabilities than 5.0, while the 5.0 release chain has had more time to find and patch them. But because 8.0 has been around for 2+ years, I think it is not likely to suffer from that problem. And other than bleeding-edge considerations, the newer version is probably more secure.
Performance
A benchmark from severalnines.com indicates that 8.0 is quite a bit faster than 5.7.

The benchmark results reveals that there has been an impressive improvement, not only on managing read workloads, but also on a high read/write workload comparing to MySQL 5.7.

Overall, MySQL 8.0 has dominated MySQL 5.7 efficiently.

General
And the MySQL marketing materials suggest that 8.0 is better in many ways.

MySQL 8.0 includes significant performance, security and developer productivity improvements

Summary
5.7.33 would be fine, but unless you have a specific reason to stick with 5.7.x, go to 8.0.


Scalar Operator in Seek Predicate and a Row Estimate of 1

On the inner side of a nested loop join, the estimated number of rows represents an estimate per iteration of the loop. The actual number of rows is the total number of rows returned for all iterations of the join. This is unintuitive and a common source of confusion.

We can see this action with a simple demo:

CREATE TABLE dbo.X_OUTER_TABLE (ID INT NOT NULL);

INSERT INTO dbo.X_OUTER_TABLE WITH (TABLOCK)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

CREATE TABLE dbo.X_INNER_TABLE (ID INT NOT NULL, PRIMARY KEY (ID));

INSERT INTO dbo.X_INNER_TABLE WITH (TABLOCK)
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

Detailed information for the clustered index seek against X_INNER_TABLE:

The estimated number of rows is 1 because the seek is performed against the full primary key of the table. The actual number of rows is 100 because the outer table has 100 rows, the loop iterates 100 times, and 1 row is found in each loop.

A similar scalar operator shows up in the simple demo as you observed in your query plan. There isn't a lot to go on in your original question, but it's likely that the scalar is indeed a red herring. Instead, I would focus on the difference between the estimated number of executions (580308) and the actual number of executions (6740779). SQL Server estimated that the outer table would have 10% of the rows that it actually had, so the nested loop is costed inappropriately low. It's possible that if the optimizer had a more accurate cardinality estimate for the outer table it would have switched from a nested loop join against this table to a different plan.


RegisterAllProvidersIP 1 or 0 For The Cluster Resource

I believe the RegisterAllProvidersIP is set to 1 automatically when you create the listener. With this setting, all subnets' IP addresses will be attached to the listener name. This is great, unless you're using older clients that do not support the MultiSubnetFailover option. If you do have such clients, you'll end up getting client connection timeouts (in our testing, we have 2 subnets and we'd get 50% connection timeouts).

To work around this (because we have fun legacy code), we set RegisterAllProvidersIP to 0 which only allows the primary node's IP address to register as the listener in DNS. The downfall of this is that we then had to understand DNS replication such that, in the event of a failover, the domain controller may not be in DNS cache (which relies on TTL for when to refresh) so the instance may not be available for a bit after failover, until the cache refreshes. We decided upon a 5 minute HostRecordTTL value (instead of the default 20 minutes) since our second subnet would only be used in a DR exercise or situation and that 5 minutes would be absorbed in the other DR activities.

At any rate, here is how you can get and set those values on the local machine:

Import-Module FailoverClusters
Get-ClusterResource {{name}} | Get-ClusterParameter


Loop INSERT EXEC AT through linked servers

If I am understanding this correctly (even though the first part of what you are doing is not being shown), I think you just need to place the declaration and setting of @remotecmd into the @LinkedServer variable. For example:

DECLARE @CRLF NCHAR(2) = NCHAR(0x000D) + NCHAR(0x000A);
DECLARE @LinkedServer NVARCHAR(MAX) = N'DECLARE @RemoteCMD NVARCHAR(MAX);' + @CRLF;

SET @LinkedServer += N'SET @RemoteCMD =
N''some query here'';
';

SELECT @LinkedServer += N' INSERT INTO #eapb EXEC(@RemoteCMD) AT [' + name + N'];' + @CRLF
FROM   sys.servers s
WHERE  server_id > 0;

When doing Dynamic SQL, I find it best to use NVARCHAR(MAX) for the variable, and also prefix string literals / constants with a capital-N.

Also, since you are using SQL Server 2014, you can take advantage of the += operator (introduced in SQL Server 2008, I believe), and save yourself some typing :-)


mysql - create a summary of event data in a event list by date even with 0 events in a given time frame

2 Tricks needed:

UNIX_TIMESTAMP() allows application of integer division to datetimes
Pivoting isn't part of SQL proper, but group_concatcan do something similar.

Putting it together:

select
  startpit,
  FROM_UNIXTIME(UNIX_TIMESTAMP(startpit)+@interval) as endpit,
  group_concat(concat(camname,': ',num)) as events
from (
select
  from_unixtime(
    floor(
      unix_timestamp(camtimestamp)/@interval
    )*@interval
  ) as startpit,
  camname,
  count(*) as num
from events,
(select @interval:=3600) as init
-- where
group by floor(unix_timestamp(camtimestamp)/@interval)*@interval, camname
) as baseview
group by startpit;

See this SQLfiddle (I replaced changed every second rows from 'wsl1' to 'wsl2' since your test data has only one cam.

EDIT

MySQL 8.0 defaults to a very buggy implementation of strict grouping. It will e.g. think, that FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(camtimestamp)/@interval) * @interval is (quote) "not functionally dependent" on FLOOR(UNIX_TIMESTAMP(camtimestamp)/@interval) * @interval. This can easily be resolved by using the name of the calcualted column (startpit). So

SELECT
  startpit,
  FROM_UNIXTIME(UNIX_TIMESTAMP(startpit)+@interval) AS endpit,
  GROUP_CONCAT(CONCAT(camname,': ',num)) AS the_events
FROM
(
  SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(camtimestamp)/@interval) * @interval
) AS startpit,
  camname,
  count(*) as num
FROM event,
(
  SELECT @interval := 3600) AS init
-- where
  GROUP BY startpit, camname
) AS baseview
GROUP BY startpit;

works with MySQL 8.0, while the functionally identical original does not. This is not a flaw in the SQL, but in how MySQL 8.0 understands it. Unexperienced SQL programmers might consider this a good thing.

EDIT 2

There is no pivoting in SQL, i.e. the number of columns can not depend on the data so the format with a variable number of columns and an integer in each is not reachable via pure SQL.

Assuming, that the SQL is generated by another layer higher in the stack you can reach it by creating SQL along the lines of

SELECT
  startpit,
  FROM_UNIXTIME(UNIX_TIMESTAMP(startpit)+@interval) AS endpit,
  SUM(IF(camname='wls1', 1,0)) as wls1,
  SUM(IF(camname='wls2', 1,0)) as wls2,
  SUM(IF(camname='wls3', 1,0)) as wls3
  -- and so on
FROM
(
  SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(camtimestamp)/@interval) * @interval
) AS startpit,
  camname,
  count(*) as num
FROM event,
(
  SELECT @interval := 3600) AS init
-- where
  GROUP BY startpit, camname
) AS baseview
GROUP BY startpit;

You can even put creating this SQL as a string into a stored procedure and then use PREPARE STATEMENT and EXECUTE to run it. Be advised, that tis creates a stored procedure where the number of output columns depends on the data, which is unusual at least.


View dropped, now shows as table "in use", can't drop

You should be able to simply rename that file to just about anything that doesn't end in .frm and then SHOW TABLES.  It should be gone.  If it's still there, FLUSH TABLES, then SHOW TABLES again.


I'm not familiar with SQL Server's brand of
SQL, but it must be something like:

SELECT ID FROM My_Table
WHERE
(A0 = A1) AND
(A1 = A2) AND
(A2 = A3) AND
(A3 = A4);

Check out this dbfiddle.uk which shows all the answers in the answer thread.


A few things:

Check licensing, depending on edition and how you are licensed it might be ignoring some of the extra cores.

It might be that your load can't efficiently use more cores. Using multiple cores for any statement doesn't necessarily make it faster, in fact for many it can make them slower due to the work to split the data and recombine the output, so the processing engine might simply not be seeing an advantage in using more cores right now. If your general workload is that it is running ~16 processes that it thinks won't benefit from multiple threads then it is only going to be using ~16 cores worth of CPU power.

You might be hitting an IO bottleneck that was previously masked by the CPU bottleneck.

Each of those cores may be more powerful than each of the ones in the previous chip(s), which will further reduce the likely peak % CPU use if you are hitting IO bottlenecks.

If your workload involves a lot of table/index scanning on objects that are of significant size compared to the CPU's cache (which most objects in a large DB will be) but not compared to the machine's RAM, then you might be hitting a memory bandwidth bottleneck instead of an IO bottleneck.

Finally, though perhaps most importantly: Is anything actually being too slow? It might be that your workload simply does not need more hardware power at this time.


How to prevent LCK_M_IX waiting/locking on the following Delete and Insert queries

Your problem has a name of lock escalation. You can read more on this here: How to resolve blocking problems that are caused by lock escalation in SQL Server.

  By default, SQL Server will acquire the finest-grain lock possible, in
  order to attain the  greatest concurrency. In most cases, this means
  SQL Server will acquire row (RID or  KEY) locks. SQL Server can
  acquire hundreds or thousands of individual locks on data  in a single
  table without causing any problems. In some cases, however, if SQL
  Server  determines that a query will access a range of rows within a
  clustered index, it may instead acquire page locks. After all, if
  every row on a page is going to be accessed, it's  easier to manage a
  single page lock than dozens, or hundreds, of row locks. In other
  cases,  primarily when there is no usable index to help process a
  query, SQL Server may lock an  entire table right at the beginning of
  processing a query.

In your case I suspect there are some indexes on the table (this means that delete acquires locks on them too) or you do your deletes in one transaction (this means that all the locks are held until the end of the transaction). In this case this happens:

  SQL Server  will escalate locks when any individual session acquires
  more than 5,000 locks in a  single statement. In this case, there is
  no randomness in choosing which session will get  its locks escalated;
  it is the session that acquired the locks.

So you should get rid of the wrapping transaction if it exists, or decrease batch size to a smaller one, or disable lock escatation on this table:

alter table order_option
set ( lock_escalation = disable )

  Do you think lowering the delete batch to less than 5000, or removing
  the index would help? In the case of removing the index, wouldn't that
  slow the delete considerably?

You should not drop this index, it helps you with your delete.

You can try batches of 2000 rows, it should help.

I don't know the kind of queries that run against this table but it could make sense to have clustered index on update_time and PK as nonclustered


You should not define a Tcl procedure inside body of SQL function. Instead use only following line as function implementation (and nothing more):

expr {sqrt([lindex $argv 0])}

If you define a proc inside function definition you will be creating a new Tcl procedure each time you call the SQL function, which makes no sense, especially when you don't call that procedure in the end.

Anyway, stick to the simple implementation as mentioned above. That should do the work.


Retrieving data speed tweaks SQL Server 2005

Your table is a "heap," which means that it doesn't have a clustered index.  The short version of what that means is that your data isn't laid out on the disk in a logical way.  A heap really isn't an optimal structure for an actively updated table.  Here's an excellent video on heaps.

So, my suggestions--please test them before deploying them in production--are:

BEGIN TRANSACTION

USE fp; ALTER TABLE fp_core ADD ID bigint NOT NULL IDENTITY (1, 1)
GO

USE fp; CREATE CLUSTERED INDEX IX_fp ON fp_core (ID) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

COMMIT

This will take some time to complete AND will be a blocking query, so do it during downtime.

This will give you a clustered index and lay out your table on a completely random sequential number, unfortunately, but since you don't have any non-unique values that might be the best course of action.

I would then include the columns note_id and timeoffset in your nonclustered index on HASHKEY.

BEGIN TRANSACTION

USE [fp]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[fp_core]') AND name = N'IX_Agency_TIMESTAMP')
DROP INDEX [IX_fp_core] ON [dbo].[fp_core] WITH ( ONLINE = OFF )
GO

USE [fp]
GO

CREATE NONCLUSTERED INDEX [IX_fp_core] ON [dbo].[fp_core]
(
    [hashkey] ASC
)
INCLUDE ( [note_id],[timeoffset])
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

COMMIT

(Or you could do it through the GUI.  If you right-click on the index and choose properties, there's an included columns pane.)

This will hopefully have the effect of replacing your heap scans with nonclustered index seeks, but since your query has a "where in [30,000 items], it might do a index scan instead.  Either way, it won't be rooting around in the heap, which I expect will be a good thing.


How do I create a database+tablespace+user in Oracle 11gR2?

I had no problems with a VirtualBox 4.14 Windows 8 preview VM and x64 Oracle 11gR2.

My VM uses 40 GB Hard Disk. While installing it downloaded .Net Framework 3.51
I started with a fresh Windows 8 VM in VirtualBox 4.14 and installed guest additions and run Windows update once. Afterwards I installed Oracle and got the items below on my Start page.

I find the databasefiles of my new database Bk_W8 in C:\app\berndk\oradata\Bk_W8. That is the location I would use for additional tablespaces.

The rest can be done by scripts which can be executed by sqlplus from this machine or from another client and is not Windows 8 specific.

Edit:

By right-clicking on the item on the start page, via advanced | open file location I found the path to the start menu:

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Oracle - OraDb11g_home1\Konfigurations- und Migrations-Tools

I guess you will find

C:\ProgramData\Microsoft\Windows\Start Menu\Programs

the other is specific for a German Oracle installation.

To get access to the hidden C:\ProgramData folder see this link showing how to access folder options in Windows 8

Final hint: My VM currently uses 23 GB for the virtual hard disk.

OK I'm going to retry this with the Oracle Database Express Edition 11g Release 2 (September 2011). But that takes some time.

Installation done:
The is the Web interface of Oracle 11g XE in Windows 8

For the question of creating addition databaseinstances:
from this question on SO we learn, that it is not possible.

Creating tablespaces and users can be done using sqlplus. Which I found at C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe.

Now going to remove that Virtual machine. I think there are better ways to learn Oracle.


How can I create a fresh copy of a large database on SQL Server?

Taking into account a 400GB database, its upto you to choose any of the below routes :

Method 1 :  Backup and Restore

Depending on your hardware and the amount of activity going on, it will be slower than Method 2 - BCP OUT / BCP IN

Below is the script that will help you :

/************************************************************************************************************************************************
Author      :   KIN SHAH
Purpose     :   Restore database on the same server with different Name
DATE        :   06-03-2013

Note        :   1. Change as per your environment by replacing the bits marked by "--- CHANGE HERE !!"
                2. If you are using SQL 2008 and up depending on Enterprise or Standard Edition, you can add "COMPRESSION" to the backup command as well.
                3. As a cleanup task, this script requires xp_cmdshell to delete the backup file on the server. I have commented that out for now.
************************************************************************************************************************************************/
declare @dbname NVARCHAR(MAX)

set @dbname = 'test1' --- CHANGE HERE !!

if object_id('tempdb..#temp1') is not null
            begin
            drop table #temp1
            END
if object_id('tempdb..#temp2') is not null
            begin
            drop table #temp2
            END
if object_id('tempdb..#temp3') is not null
            begin
            drop table #temp3
            END

 --backup path goes here
DECLARE @path NVARCHAR (MAX)
-- Here the path is hard-coded  as all the server has 'D:\2restore' folder. This can be made as input parameter also !
SET @path = 'C:\test' --- CHANGE HERE !!
 -- generates Archive database name
DECLARE @archivedbname NVARCHAR(MAX)
SET @archivedbname = @dbname+'_Archive'+'_'+DATENAME(MONTH, GETDATE())+'_'+ CAST(DATEPART(YEAR, GETDATE()) as VARCHAR(MAX))
--PRINT @archivedbname

-- check that the database to be archived is  there and is not a system database ...

IF  @dbname in (SELECT NAME FROM MASTER..sysdatabases WHERE DB_ID(NAME)>4)
    BEGIN
        select 'The database is correct. starting Archiving Process .....'
            BEGIN TRY
                        select * INTO #temp1
                            FROM MASTER.sys.master_files
                            WHERE database_id = cast(DB_ID(@dbname) AS NVARCHAR(MAX))

                    -- now get the logical and physical names of the database to be archived
                    -- type 0 = data
                    CREATE TABLE #temp2 (ldata NVARCHAR(MAX), pdata NVARCHAR(max))
                    DECLARE @ldata NVARCHAR(MAX)
                    DECLARE @pdata NVARCHAR(MAX)
                    SELECT  @ldata = 'select [name],[physical_name] from #temp1 where  type = 0 and database_id ='+ cast(DB_ID(@dbname) AS NVARCHAR(MAX))
                    INSERT INTO #temp2
                    EXEC (@ldata)

                    SELECT @ldata = ldata FROM #temp2
                    --PRINT @ldata
                        SELECT @pdata = pdata FROM #temp2
                        SELECT @pdata = left(@pdata, len(left(@pdata,LEN(@pdata)-4))-len(@ldata))+@archivedbname+'.mdf'
                        --PRINT @pdata
                        -- type 1 = log
                        CREATE TABLE #temp3 (llog NVARCHAR(MAX), plog NVARCHAR(max))
                        DECLARE @llog NVARCHAR(MAX)
                        DECLARE @plog NVARCHAR(MAX)
                        SELECT  @llog = 'select [name],[physical_name] from #temp1 where  type = 1 and database_id ='+ cast(DB_ID(@dbname) AS NVARCHAR(MAX))
                        INSERT INTO #temp3
                        EXEC (@llog)
                        SELECT @llog = llog FROM #temp3
                        --PRINT @llog
                        SELECT @plog = plog FROM #temp3
                        SELECT @plog = left(@plog, LEN(left(@plog,LEN(@plog)-4))-LEN(@llog))+@archivedbname+'_log.ldf'
                        --PRINT @plog

                        -- now we will take backup of the database that is specified ....
                        select 'Taking backup of database ' + @dbname

                        DECLARE @sql NVARCHAR (MAX)
                        -- use compression using Redgate backup
                        select @sql = 'backup database '+@dbname+' to disk ='''+@path+ '\'+ @dbname+'_FULL_'+convert(varchar(10),getdate(),112)+'.bak'+''' with init, stats = 10'

                        --print @sql
                        exec(@sql)

                        select 'The backup is done for ' + @dbname
            END TRY
            BEGIN CATCH

                    SELECT
                        ERROR_NUMBER() AS ErrorNumber,
                        ERROR_SEVERITY() AS ErrorSeverity,
                        ERROR_STATE() AS ErrorState,
                        ERROR_PROCEDURE() AS ErrorProcedure,
                        ERROR_LINE() AS ErrorLine,
                        ERROR_MESSAGE() AS ErrorMessage

            END CATCH

                -- now restore the database as archive database
        BEGIN TRY
         select 'Starting restore part for ' + @archivedbname

             SELECT @sql = 'restore database '+@archivedbname + ' from disk = ''' + @path +'\'+ @dbname+'_FULL_'+ CONVERT(VARCHAR(8), GETDATE(), 112)+'.bak'+''' with recovery, stats = 10 '+','+ ' move '''+@ldata+''' '+'to ' +''''+ @pdata +''''+','+ ' move '''+@llog+''' '+ ' to '+ ''''+ @plog +''''
                --print (@sql)
                exec (@sql)

                select 'Restore is done sucessfully ! And the new database name is '+@archivedbname + '!!'

        END TRY
        BEGIN CATCH

                SELECT
                    ERROR_NUMBER() AS ErrorNumber,
                    ERROR_SEVERITY() AS ErrorSeverity,
                    ERROR_STATE() AS ErrorState,
                    ERROR_PROCEDURE() AS ErrorProcedure,
                    ERROR_LINE() AS ErrorLine,
                    ERROR_MESSAGE() AS ErrorMessage
        END CATCH

select 'Database is restored as Archive, so now lets do some clean up task ...'
-- delete the backup file
select @sql= 'exec master.dbo.xp_cmdshell ''Del '+@path+'\'+ @dbname+'_full_'+ CONVERT(VARCHAR(8), GETDATE(), 112)+'.bak'''

print (@sql)
--EXEC (@sql) --- CHANGE HERE !! (if you want to delete the backup file after the restore is done !!

    END

ELSE
    BEGIN
            select ' The database is INCORRECT ! Check if the database exists or is not a system database'
    END
GO

Method 2 - BCP OUT / BCP IN

Script out the database SCHEMA_ONLY and recreate an empty database on the same server with a different Name.

Use BCP OUT and BULK INSERT to insert data. Below script will help you with that :

    /************************************************************************************************************************************************
Author      :   KIN SHAH    *********************************************************************************************************************
Purpose     :   Move data from one server to another or same server but different database ***************************************************************************
DATE        :   05-28-2013  *********************************************************************************************************************
Version     :   1.0.0   *************************************************************************************************************************
RDBMS       :   MS SQL Server 2008R2 and 2012   *************************************************************************************************
*************************************************************************************************************************************************/

-- save below output in a bat file by executing below in SSMS in TEXT mode
-- clean up: create a bat file with this command --> del D:\BCP_OUT\*.dat

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe  --- CHANGE HERE !!
        +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'
        +  QUOTENAME(name)
        +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored --- CHANGE HERE !!
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','')
        + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format --- CHANGE HERE !!
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema
order by schema_name(schema_id)

--- Execute this on the destination server.database from SSMS.
--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destination_database_Name'               -- Destination Database Name where you want to Bulk Insert in --- CHANGE HERE !!
select 'BULK INSERT '                                       -- Remember Tables **must** be present on destination Database
        +  QUOTENAME(@Destdbname)+ '.'
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'
        +  QUOTENAME(name)
        + ' from ''D:\BCP_OUT\'                             -- Change here for bcp out path --- CHANGE HERE !!
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','')
        +'.dat''
        with (
        KEEPIDENTITY,
        DATAFILETYPE = ''native'',
        TABLOCK
        )'  + char(10)
        + 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+  REPLACE(name,' ','')+' is done... '''+ char(10)+'go'
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'         -- Optional to exclude any schema
order by schema_name(schema_id)


Allowing a user to view/edit only a specific table in a database with ~50 tables

It'll involve 1) removing the user from any database fixed roles like db_datareader (only public will remain), 2) creating a custom role in the DB, 3) granting the new role specific permissions on the one table, and 4) adding the user as a member of the new role. You'll also want to make sure public has no grants to user objects as all users in a DB are a part of the public role.


Extend Query to include additional column

I'm having trouble with exactly where to place the join

Directions is joined to Readers - so you must place it adjacent to this table enclosind this pair in additional parenthesis:

SELECT Users.Name,
       Users.Surname,
       Transactions.Timestamp,
       AccessPoints.Description as Door,
       Readers.Direction,
       Directions.DirectionsText
FROM ( ( Readers
         INNER JOIN
         Directions ON Readers.Direction = Directions.Direction
       )
       INNER JOIN
       ( AccessPoints
         INNER JOIN
         Points_Readers ON AccessPoints.PointID = Points_Readers.PointID
       ) ON Readers.ReaderID = Points_Readers.ReaderID
     )
     INNER JOIN
     ( Transactions
       INNER JOIN
       ( AccessNumbers
         INNER JOIN
         Users ON AccessNumbers.AccessNumberID = Users.AccessNumberID
       ) ON Transactions.Number = AccessNumbers.Number
     ) ON Readers.ReaderID = Transactions.ReaderID
WHERE (Transactions.Timestamp>#2018-08-15  06:50:16#)
ORDER BY Transactions.Timestamp;

  that I don't keep getting errors in the FROM clause

Build the query text using Query Builder - it always creates correct code or produce error message if there are any logical problems.

Or (if you want write query text "by hands" without using the master) rewrite the query to cartesian form (with comma-separated tables list and all joining conditions in WHERE clause) - it do not need the accuracy in parenthessis:

SELECT Users.Name,
       Users.Surname,
       Transactions.Timestamp,
       AccessPoints.Description as Door,
       Readers.Direction,
       Directions.DirectionsText
FROM Readers,
     AccessPoints,
     Points_Readers,
     Transactions,
     AccessNumbers,
     Users,
     Directions
WHERE AccessPoints.PointID = Points_Readers.PointID
  AND Readers.ReaderID = Points_Readers.ReaderID
  AND AccessNumbers.AccessNumberID = Users.AccessNumberID
  AND Transactions.Number = AccessNumbers.Number
  AND Readers.ReaderID = Transactions.ReaderID
  AND Readers.Direction = Directions.Direction
  AND Transactions.Timestamp>#2018-08-15 06:50:16#
ORDER BY Transactions.Timestamp;

The order of either tables in FROM clause or conditions in WHERE clause does not matter in those form.


How to convert control characters in MySQL from latin1 to UTF-8?

I'm not certain.  I tried to start out be reproducing your problem but the alter worked fine for me.

test > CREATE TABLE `bar` (  `content` text ) ENGINE=MyISAM DEFAULT CHARSET=latin1;  INSERT INTO bar VALUES (0x8081828384858687898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F);
Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

test > ALTER TABLE bar CHANGE content content TEXT CHARACTER SET UTF8;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

test > select * from bar;
+---------------------------------+
| content                         |
+---------------------------------+
| ����������������������������� |
+---------------------------------+
1 row in set (0.00 sec)

test > set names utf8;
Query OK, 0 rows affected (0.00 sec)

test > select * from bar;
+---------------------------------------------------------------------------------+
| content                                                                         |
+---------------------------------------------------------------------------------+
| €‚ƒ„…†‡‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Here's my related char settings

test > show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Edit

My char settings before running set names utf8

test > show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Version

test > select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.1.41-3ubuntu12.10-log |
+-------------------------+
1 row in set (0.00 sec)


This query gives you company names:

SELECT company
FROM @Table
WHERE addon IN ('theft cover', 'key loss') AND status = 'yes'
GROUP BY company
HAVING COUNT(DISTINCT addon) = 2;

It gets all rows with requiered values (WHERE clause)
It GROUP BY company names in order to count how many of them are present
It only keeps companies with a COUNT of DISTINCT values of 2 (='theft cover'+'key loss')

Output:

company
comp1
comp3
comp5

You can then join it with you main table and get each associated rows:

SELECT t.addon, t.status, t.amt1, t.amt2, t.company
FROM @Table t
INNER JOIN (
    SELECT company
    FROM @Table
    WHERE addon IN ('theft cover', 'key loss') AND status = 'yes'
    GROUP BY company
    HAVING COUNT(distinct addon) = 2
) as c
    ON c.company = t.company
-- where t.compny = 'xxx'
-- where at.mt1 = 7 or t.amt2 = t.amt1
-- where ...
;

Output:

addon       status  amt1    amt2    company
theft cover yes     7       7       comp1
key loss    yes     2       2       comp1
key loss    yes     1       1       comp3
theft cover yes     12      22      comp3
theft cover yes     22      55      comp5
key loss    yes     33      44      comp5

Sample data:

Declare  @Table table([addon] varchar(20), [status] varchar(3), [amt1] varchar(4), [amt2] varchar(4), [company] varchar(5));

INSERT INTO @Table([addon], [status], [amt1], [amt2], [company])
VALUES
    ('theft cover', 'yes', '7', '7', 'comp1'),
    ('key loss', 'yes', '2', '2', 'comp1'),
    ('theft cover', 'no', NULL, NULL, 'comp2'),
    ('key loss', 'yes', '2', '33', 'comp2'),
    ('key loss', 'yes', '1', '1', 'comp3'),
    ('theft cover', 'yes', '12', '22', 'comp3'),
    ('theft cover', 'yes', '11', '22', 'comp4'),
    ('key loss', 'no', NULL, NULL, 'comp4'),
    ('theft cover', 'yes', '22', '55', 'comp5'),
    ('key loss', 'yes', '33', '44', 'comp5');


How do I track down wait events for a specific transaction in Oracle Enterprise Manager?

The ASH report shows top wait events, top SQL and top sessions. Using Enterprise Manager, you can generate the ASH report by navigating to the performance page and then clicking 'Run ASH Report'. On thhe performance page, you can also click the 'Top Activity' link. On the Top Activity page, you can then choose a time interval by dragging the shaded area(may have different drag controller version to version and browser to browser), and then click Run ASH Report.
For details:
Identifying High-Load SQL Statements


How query execution time and concurrent users affect DTU

As I understand it, the concept of DTUs is deliberately a bit vague. From the same page the graphic in the question came from:
SQL Database service tiers

Understanding DTUs
The Database Throughput Unit (DTU) is the unit of measure in SQL Database that represents the relative power of a database to process transactions given the constraints of the hardware driving the database. DTU is a concept similar to horsepower in a car. It describes the relative performance of one database to another. Just like with horsepower in a car, the more DTU a database has, the more power it has. Horsepower is a function of many different factors: weight, engine capacity, turbo, exhaust system. Similarly, DTU is a function of the amount and type of memory, compute, and disk the database can consume. You can read the details of how we determine DTU in the Benchmark Overview).

(emphasis added)
There are more details about how to monitor your usage and decide on an appropriate tier in the links above, the rest of the official documentation, and the Azure SQL Database blog.


How to troubleshoot enq: TX - row lock contention?

Query v$transaction on each node to see uncommitted sessions:

SELECT t.start_time, s.sid, s.serial#, s.username, s.status,s.schemaname, s.osuser
   , s.process, s.machine, s.terminal, s.program, s.module
   , to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
FROM v$transaction t, v$session s
WHERE s.saddr = t.ses_addr
ORDER BY start_time;


More than likely, you have not promoted the views to be production views. When views are "development" views they will only select a very small subset of data for efficiency purposes. Once they are promoted to production and gather everything, then they will actually see the entire data set.

At this time, there is no way to span buckets with views in Couchbase. If you need to do that, I would ask why you have to have things in different buckets? What is your strategy for why you have different buckets. Usually you would split it out into different buckets if you need to isolate data for performance or application reasons since buckets have different resource allocations. Another reasons is for cardinality of data. Say you need to use views and some data will never be seen by a set of views, it might make sense to split that data into different buckets.

All that being said, take a look at Couchbase's upcoming N1QL query language that will let you query across buckets. http://query.couchbase.com/.


How do I store spatial coordinates in SQL Server 2005

GPS co-ordinates are just latitude and longitude - if you have to support SQL 2005, then store them as numbers to your required precision.

To calculate the distance, you can implement the Haversine formula


Copy complete structure of a table in MySQL without using a backup?

You should be able to do :

CREATE TABLE `actor_copy` LIKE `actor`;

to get the structure, complete with indexes. However Foreign Keys will still need to be created manually.

The above will give you structure only. Then you can proceed to copy the data if you wish:

INSERT INTO `actor_copy` SELECT * FROM `actor`;


How to Remove Scripts Date Comments from Powershell Generate Database Script?

From the SMO documentation for ScriptingOptions:

IncludeHeaders Gets or sets a Boolean property value that specifies whether the generated script is prefixed with a header that contains information which includes the date and time of generation.

So you should be able change this line of your PowerShell script:

$CreationScriptOptions.IncludeHeaders = $true; # of course

to

$CreationScriptOptions.IncludeHeaders = $false; # of course not


Starting a Postgres slave that is in hotstandy - stuck after start

Although this message could look like an error:

  cp: cannot stat `/usr/local/pgsql/wals/000000010000003B000000BA': No
  such file or directory

it's actually harmless, it's just the way of your restore_command to check for a new WAL file that's not necessarily there yet.
Presumably this command looks like:

cp /usr/local/pgsql/wals/%f "%p"

Should you want to get rid of that message, you could provide a more sophisticated version of the command to silently check for the existence of the file before attempting to copy it.

In the rest of the log output, there is nothing that looks wrong. If it's a hot standby, you should be able to connect to it with psql.


SQL Server backups strategy with limited storage available

Ideally you'd add a 4th volume for backups, but I understand that's likely not an option. (FWIW, Sql Server does support making backups to network shares).

With that out of the picture, it sounds to me like you're trying to keep it so that losing volume 2 on any given day means you can recover the full backup on volume 3 and still have the ldf file valid for that full day to get you back to current, or that losing volume 3 would mean you can still replay the transaction log backups on volume 2 from that day to get back up to the last hour. However, I don't think this will work how you want. I think you'll end up with much larger ldf and transaction log files than you need, and Sql Server will want to keep a lot more of that ldf information in memory, hurting performance.

I think you will be better served by taking nightly full backups, and doing transaction log backups as often as performance and disk space allow (which could be fairly often... but you'll need to test to know). This should result in better overall performance and reliability for your system. I do think it is okay to separate which volumes you're using for each role, as you suggested.

The one other thing I would add is to look at where you're keeping your tempdb. Moving that around can have a big impact on things as well. You might find (just as one example) that keeping your database's mdf and ldf together on one volume with tempdb and backups on the other gives better results.


Linked Server alternative in Sql Azure database

You can use cross database queries as explained here. For example, to make reference to a remote table on a query you need to create a data source:
CREATE EXTERNAL DATA SOURCE MyExtSrc
WITH
(
 TYPE=SHARD_MAP_MANAGER,
 LOCATION='myserver.database.windows.net',
 DATABASE_NAME='ShardMapDatabase',
 CREDENTIAL= SMMUser,
 SHARD_MAP_NAME='ShardMap'
);

Define an external table using that data source.
CREATE EXTERNAL TABLE [dbo].[all_dm_exec_requests](
 [session_id] smallint NOT NULL,
 [request_id] int NOT NULL,
 [start_time] datetime NOT NULL,
 [status] nvarchar(30) NOT NULL,
 [command] nvarchar(32) NOT NULL,
 [sql_handle] varbinary(64),
 [statement_start_offset] int,
 [statement_end_offset] int,
 [cpu_time] int NOT NULL
)
WITH
(
 DATA_SOURCE = MyExtSrc,
 SCHEMA_NAME = 'sys',
 OBJECT_NAME = 'dm_exec_requests',
 DISTRIBUTION=ROUND_ROBIN
);

Finally, use the external table on SELECT statements.
SELECT TOP 10
 [request_id],
 [start_time]
 [status],
 [command]
FROM all_dm_exec_requests
ORDER BY [cpu_time] DESC


give me an example how to do it

Get something like

SELECT t1.email
     , t1.telegram
     , t1.twitter
     , t1.address
     , COALESCE(t2.email, '') AS refer_email
     , count(t3.referrer) AS refer_count
FROM wpkx_airdrop_claim AS t1
LEFT JOIN wpkx_airdrop_claim AS t2 ON t1.referrer = t2.refer_link
LEFT JOIN wpkx_airdrop_claim AS t3 ON t3.referrer = t1.refer_link
WHERE t1.post_id = 8037
GROUP BY t1.id
/* If ONLY_FULL_GROUP_BY, replace with
GROUP BY t1.email
       , t1.telegram
       , t1.twitter
       , t1.address
       , COALESCE(t2.email, '') AS refer_email
*/
ORDER BY refer_count DESC

And create indices by: (post_id, referrer, refer_link), (referrer) and (refer_link). Then test does they are used.


Should MySQL database be in dedicated server?

The argument against a dedicated server is invalid, if the microseconds that account for the delay when talking to a different server matter you would not be asking about MySQL.

But there is one thing you could check before going out and buying hardware: A good database is trying to utilize as much main memory as possible for caching. Make sure your system is not swapping memory out to disk when your application is running simultaneously on the same server.


They're all listed in Setting Database Options:

bulkcopy: ALTER DATABASE .. SET RECOVERY BULK_LOGGED;
trunc. log: ALTER DATABASE .. SET RECOVERY SIMPLE;
dbo use: ALTER DATABASE ... SET RESTRICTED_USER;
single: ALTER DATABASE ... SET SINGLE_USER;
ansi null default: ALTER DATABASE ... SET ANSI_NULL_DEFAULT ON;
db chaining: ALTER DATABASE ... SET DB_CHAINING ON;


Which files in the “MySQL-5.0.27-win32” folder do i need to backup?

You are using a very old, end-of-life, and unsupported version of MySQL.

There is not exactly a correct answer to your question, because it is impossible in many cases to safely back up or clone a MySQL server that is still running.

The only absolutely safe way to copy data using this method is by stopping the mysqld process, gracefully, and copying all of the files, for all of the databases.

If you are using MyISAM, which, again, you almost certainly should not, it is possible to copy a single database by copying all of the files in that database's named directory, but the tables have to be locked and flushed, or the backup will either not contain the most recent changes, or will be corrupt.

If you are using InnoDB, it is almost impossible to copy only one database from one server by copying files, unless you use the "transportable tablespaces" feature introduced in MySQL 5.6.

The correct answer to the question is "don't do it this way."

The correct solution is to use mysqldump to make the backup, which does not copy files at all.  Instead, it creates a file containing the SQL statements necessary to recreate your tables and reinsert the data that was in those tables when the backup was made.

The output of mysqldump is also human-readable, so you can actually examine what's in the backup.

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html


postgres wal sender replication timeout during pg_basebackup

Many thanks to Albe Laurenz from the pgsql-admin mailing list.

The server error message means that the client did not send a status update within wal_sender_timeout milliseconds, see documentation.

The basebackup needs to complete before this wal_sender_timeout period, else the server resets the connection.

Side note, I am running 9.2 so this parameter is called replication_timeout in the older version.


Postgres ERROR: tuple concurrently updated

Your plain subselect fetches up to 100 rows, but does not lock them against write access. Concurrent transactions can update or delete one or more of those rows before DELETE can lock the rows (at least with the default isolation level READ COMMITTED). This would result in your error message.

To defend against this race condition, lock the rows in the SELECT with FOR UPDATE (or other options):

DELETE FROM test t
USING (
   SELECT test_id
   FROM   test
   WHERE  User_id = 2
   ORDER  BY test_id  -- acquire locks in consistent order!
   LIMIT  100
   FOR UPDATE         -- lock rows
   ) x
WHERE x.test_id = t.test_id;

Do this in a consistent order to prevent deadlocks between multiple transactions collecting locks in different order and ending up blocking each other out.

There are even better solutions. The best option would be with FOR UPDATE SKIP LOCKED in Postgres 9.5 or later:

Postgres UPDATE ... LIMIT 1

Consider upgrading to a current version of Postgres.


Is there any way to access the variables in a dynamic sql which is declared outside the dynamic sql

Unless I am mistaken, the request is to ultimately dynamically construct a string to be executed that would be able to pull in local variable values that exist in the context that is executing the Dynamic SQL. For example:

The function constructs and returns something along the lines of:

SET @ReturnString = N'@Param1 INT = ''+@Param1+'', @Param2 VARCHAR(15) = ''+Param2+''';

The proc that calls the function EXECs that Dynamic SQL in the hopes of replacing the escaped-quoted parameters as follows:

SET @SQL = N'INSERT INTO dbo.spcallTracking (ProcName, SpCallDetails) VALUES ('''
              + OBJECT_NAME(@@PROCID)
              + N''', '''
              + @ReturnString
              + N''');';

EXEC sp_executesql @SQL;

and the desire is that @ReturnString actually renders as:

@Param1 INT = 2, @Param2 VARCHAR(15) = 'bob'

Unfortunately no, you cannot access variables that exist in the parent context. That would require an eval() (JavaScript / C# 1 / C# 2 / PHP)-like system stored procedure to execute the dynamic SQL in the current context and no such functionality exists, at least not in T-SQL.


PostgreSQL error: OperationalError: FATAL: role "username" does not exist

The role name is case sensitive, but with your queries it gets translated to lower case tcoyuksel,

CREATE ROLE TCOYUKSEL superuser;
ALTER ROLE TCOYUKSEL WITH LOGIN;

as key words and unquoted identifiers are case insensitive (4.1.1. Identifiers and Key Words).

  There is a second kind of identifier: the delimited identifier or
  quoted identifier. It is formed by enclosing an arbitrary sequence of
  characters in double-quotes ("). A delimited identifier is always an
  identifier, never a key word. So "select" could be used to refer to a
  column or table named “select”, whereas an unquoted select would be
  taken as a key word and would therefore provoke a parse error when
  used where a table or column name is expected.

You need to use the quotes to bypass the normalization i.e. use TCOYUKSEL as delimited identifier:

CREATE ROLE "TCOYUKSEL" superuser;
ALTER ROLE "TCOYUKSEL" WITH LOGIN;

If you run it directly from the command line, you need to double the quotes:

postgres "CREATE ROLE ""TCOYUKSEL"" superuser; ALTER ROLE ""TCOYUKSEL"" WITH LOGIN;"


SQL Server 2012+ Agent Job with Parameter to determine job flow

Expanding a bit on what @Nic said in his comment.  Create a quick table in an applicable database.

CREATE TABLE JobParam AS (Param1 varchar(50))

Then set it appropriately before you start your job.

UPDATE JobParam SET Param1 = 'JobPath1'

As Nic suggested I would put some code at the top of steps 6+ like such.

IF (SELECT 1 FROM JobParam WHERE Param1 = 'JobPath1')
BEGIN
    T-SQL statements
END

Another option is to put code in the job that causes a failure if the param has a specific value and use the fail/succeed logic of the job steps to branch your job.

Either way I would make sure to put in a step at the end that always runs and re-sets your value to a default in case the job is run but someone forgot to update the parameter.  Maybe have the default bypass the entire job.

UPDATE JobParam SET Param1 = 'FailJob'


select distinct one column with all instances of another column in a second column

You can use a pattern like this:

SELECT files.database_id, db.name AS DatabaseName,
        STUFF((SELECT ', ' + names.name
                FROM sys.master_files names
                WHERE names.database_id = files.database_id
                FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
            , 1, 2, '') AS NameList
FROM sys.master_files files
JOIN sys.databases db
    ON files.database_id = db.database_id
GROUP BY files.database_id, db.name


SQL Server starts normally from Windows Services screen, but shows error through SQL Server Configuration Manager. Why?

SQL Server Configuration Manager performs additional operations that the Windows Services does not. Please check Technet SQL Server Configuration Manager particularly if you have changed the service account and/or password recently or use startup options.


MySQL Cluster over WAN: Best Architecture Options?

For high-latency the main choice is between Percona XtraDB Cluster (Galera+InnoDB-based) or the new WAN replication in MySQL Cluster 7.2 (NDB-based). The downside of NDB is greatly increased complexity and application changes; it is NOT a drop-in replacement for your existing system.  Percona XtraDB Cluster has only a few small differences, e.g. locking is pessimistic on the local node (as currently) but optimistic cluster-wide, so if there are conflicting changes on a remote node you won't find out until you commit. (If there are conflicting changes, the commit will fail and roll back; you can't break the cluster as you can with standard MySQL replication.)


AUDIT_SYS_OPERATIONS

  The audit records are written to the operating system's audit trail.

SYS audit entries are not stored in the database, but on the host in text files (or the event log on Windows).

But yes, you are correct, if you are SYS, you have full access to the database, and the host, with the user the database runs as. So yes, you can manipulate audit rules and data as well.

For that reason (and performance-wise), usually I suggest using AUDIT_SYSLOG_LEVEL, and configuring syslog to forward all entries to a remote server.


Copying a MySQL 5.6 server settings for a new environment?

The first recommendation is to not use Windows 10 on your installation if this is for a production server. I highly recommend you Linux but if that's not possible at least Windows Server.
Asking your question, YES.
Just install MySQL (same version as on old machine) and follow these steps

Stop MySQL on both machines (New and old)
Locate and delete datadir folder on the new machine and copy the one from the old machine
Do the same with my.ini file (Delete the new one and replace it with old one).

In theory, you should be able to start MySQL on the new machine and access the data.
Check data and if everything goes fine, you can delete old machine service/data.


Always On Availability Group ApplicationIntent=ReadOnly Not routing to Secondary

You have to have a read-routing list, even with only two instances, if you want to offload reads to a secondary replica.

The following script is an example of setting this up (you would change the AG name to whatever your AG is, and the replicas to your servernames (using the fully qualified domain name, and adjusting the port if required).

ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA1' with (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://REPLICA1.FQDN:1433'));
ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA2' with (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://REPLICA2.FQDN:1433'));

ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA1' with (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('REPLICA2', 'REPLICA1')));
ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA2' with (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('REPLICA1', 'REPLICA2')));

As regards the flags:

ReadIntent allows for connections only passed along with the
ApplicationIntent=ReadOnly flag
Yes allows for any connection to the
database (meaning you could connect directly with SSMS and run
queries)


How to synchronize a Windows database for Linux?

A single time syncron can be done by simply dumping the database on the Windows, and piping into a command line postgresql client to your Linux machine:

pg_dump --host=your.windows.host --port=5432 --user=you|\
psql --host=your.linux.host --port=5432 --user=you

If you are thinking on some type of replication cluster, then it is hard to answer here, but you could read the docs.

There is no major OS-dependence in the storage areas and protocols in PostgreSQL, but these are enough to make the cross-os physical replication impossible (ref). From PostgreSQL 10, you could use logical replication, or use some third party solution.


If you try it, you'll get:

ERROR: nextval: reached maximum value of sequence "testseq" (9223372036854775807): select nextval('testseq')

This is the case when the sequence is created automatically for a serial or bigserial column.  You can create a sequence with specifying CYCLE, in which case it will restart from the MINVALUE specified (or left at the default 1).


Without more more information it might be hard to diagnose this - however, usually the only reason why a shrink wouldn't work (but completes, no errors and doesn't hang) is because there is no free space to release anywhere.

The only way to reduce the size on disk therefore is to delete data.

Run sp_spaceused to find out how much your database is actually taking up. If it's occupying all (or most) of the space on disk, then your shrink operation won't do anything - there's nothing to shrink.

NB (almost tempting to put this at the top, but I thought I'd answer the question first):

If this is on a dev environment, then you're probably OK to play around and try this out.

However, if you're doing this in production - don't.

It's a long quoted (for good reason) fact that shrinking a db is pretty much the worst thing you can do. Performance suffers for the sake of space that you could very well just use up again at some point - and disks are cheap.

Don't take my word for it Brent Ozar, Paul Randal, Stack Exchange Q, Other SE Q (there's plenty more examples saying the same thing).

Ps. Check out SQLServerCentral and Stack Exchange Q for more options of checking space used.