Downgrading SQL Server 2012 to Lower Edition

You cannot directly change edition of SQL Server 2012 from Enterprise to Standard. See this page for the supported edition change scenarios.

There are a few different ways to go about doing this -- some recommend making copies of the system databases and overwriting them on the new instance (make sure you're at the exact same SP/CU level), or you could just set up a new environment from scratch. It really depends on how complex your environment is. I would definitely recommend testing the process in a separate environment if you can.

Also, before you start, go through your databases and check sys.dm_db_persisted_sku_features to make sure there aren't any Enterprise edition features in use.


Why does my query end up with two seeks instead of one and how do I fix that?

Try:

LEFT OUTER JOIN TaskItems AS TI ON O.OwnerId = TI.OwnerId
AND LastOperationTime IS NOT NULL

The engine could be grabbing the Null row separately, as it's a slightly different scenario to not finding one. But as you essentially want the same behaviour, just out the explicit filter in there, within the ON clause of your LEFT JOIN. Then the only NULLs will be as a result of the outer join.


PgPool fails to auth in master_slave mode

Solution, in 2 words: install latest pgpool2 from sources

Step by step solution:

Make sure that you have pgpool2 with version 3+
Create pool_passwd with pg_md5 command
Make sure, that you have md5 in pool_hba.conf. If you have pgpool2 from deb - it will not support md5 token in pool_hba.conf


Check if database is restored to the most recent log file

You should be able to use the restorehistory table on the server to which you are restoring to get the most recently restored LSN.

SELECT TOP 1 b.type, b.last_lsn
 FROM msdb.dbo.restorehistory a
 INNER JOIN msdb.dbo.backupset b
     ON a.backup_set_id = b.backup_set_id
 WHERE a.destination_database_name = 'YourDatabaseName'
 ORDER BY restore_date DESC

This was posted in a discussion of log files some years ago at: http://www.sqlbackuprestore.com/logsequencenumbers.htm

EDIT: As a further option, if you are using Enterprise Edition you can use database snapshots for reporting.  The database in this case remains in restore mode and the snapshot(s) read only.  If this is your environment, you could indeed use log shipping to keep your Reporting Server data up to date.


You will have no problem doing what you have described. SQL Server security is designed intentionally to allow you to grant different permissions to a group and to a person in that group, and keep the two separated.  They did this to address exactly the scenario that you are describing.

The only way that you would have a conflict is if you explicitly deny a permission. Then the deny would override any grant.


Why index on low selectivity column hurts query performance

Access to a row using a non-clustered index, which idx_name is, requires extra random I/O: b-tree lookup finds the clustered (primary key) index value, then you need to go and fetch the actual row from the clustered index.
The alternative is a sequential scan of the clustered index itself, which does not incur that extra I/O cost and is also simply more efficient by being sequential, not random.


Why no clustered index? Why no primary key?

This is most likely your problem: you don't have any order to the table (in the sense of, say, an IDENTITY column) this you are inserting into a heap

See

https://stackoverflow.com/q/5094400/27535 (SO)
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/08/14/are-inserts-quicker-to-heap-or-clustered-tables.aspx (Tibor Karaszi)

Edit, after update.

Having a non-unique clustered index requires a uniquifier (DBA.se links) per duplicate gn_ObjectId entry. This is not a good clustering key, which is best when it is

numeric
monotonically increasing
narrow

I also assume you haven't tried a IDENTITY column then...? Try it, report back..

Edit 2, as @JNK says, an ID column is not an overhead in this situation because of how your writes will be managed on disk


I added sql service account to Perform volume maintenance tasks and disk slowness problem appeared on the server. The storage team replaced the disks with faster disks. Problem is solved.


select data on base of comparing multiple values between two columns in same table

There are many solutions for this kind of problem (it's called relational division).

The most simple ways to write it would be either by self-joining the table (4 times) or with a GROUP BY and HAVING clause:

SELECT user_id
FROM users_responses
WHERE (question_id = 1 AND response_id = 1)
   OR (question_id = 3 AND response_id = 6)
   OR (question_id = 2 AND response_id = 4)
   OR (question_id = 3 AND response_id = 7)
GROUP BY user_id
HAVING COUNT(DISTINCT question_id, response_id) = 4 ;

If there is a UNIQUE constraint on (user_id, question_id, response_id), you can simplify the HAVING clause to:

HAVING COUNT(*) = 4 ;


SQL Server - Export large table without primary key

Assuming you don't have updates or deletes against source table you can try the following :
1. Make a copy of existing table using CTAS syntax (for SQLServer it's SELECT * into source_table_copy FROM source_table). Such operation is very fast even for huge tables.
2. Add after insert trigger on source_table that copies new record[s] to source_table_copy.
3. Now when all new records in source_table go to source_table_copy as well, and you can move data from copied table to Mysql in batches.
For instance, if you have a link between 2 servers, everything can be done within the body of TSQL stored procedure.
E.g. a piece of code that move up to 20 records to new server might look like

 --declare table variable to keep deleted records until they delivered to target host
  BEGIN TRANSACTION;
  DELETE TOP (20) FROM source_table_copy OUTPUT DELETED.* INTO @Table_Var;

  --insert data into linked server , or to csv file
  COMMIT;

It's also possible to use CURSOR to read data and then delete with where current of clause.

**Ideally you need to prevent applications from inserting data into source_table during step 1. If it's absolutely impossible , I'll go with an after insert trigger which is added right before step 1 and removed right after it's done which copies data to some other table I can later merge with source_table_copy.


Updating table schema with correct datatype in sqlite

If you want really want to, just use your first table as a staging table before you go ahead and create the final table with correct datatypes & data:
sqlite> PRAGMA table_info(testtb);
0|a|TEXT|0||0
1|b|TEXT|0||0
sqlite> create table testtb2 (a INT, b INT);
sqlite> insert into testtb2 (a,b) select a,b from testtb ;
sqlite> select * from testtb2;
1|2
sqlite> PRAGMA table_info(testtb2);
0|a|INT|0||0
1|b|INT|0||0
sqlite>

At the end of the day you have to remember (to some extent) that sqlite doesn't actually care about datatypes.

SQLite is very flexible with regard to datatypes.
Some commentators say that SQLite is "weakly typed" and that other SQL
databases are "strongly typed". We consider these terms to be
inaccurate and purgative. We prefer to say that SQLite is "flexibly
typed" and that other SQL databases are "rigidly typed".
The key point is that SQLite is very forgiving of the type of data
that you put into the database. For example, if a column has a
datatype of "INTEGER" and the application inserts a text string into
that column, SQLite will first try to convert the text string into an
integer, just like every other SQL database engine. Thus, if one
inserts '1234' into an INTEGER column, that value is converted into an
integer 1234 and stored. But, if you insert a non-numeric string like
'wxyz' into an INTEGER column, unlike other SQL databases, SQLite does
not throw an error. Instead, SQLite stores the actual string value in
the column.


In the cube-designer there is a tab "dimension usage" where you can set IgnoreUnrelatedDimensions for each measure group. When you set IgnoreUnrelatedDimensions to False for the measure group with the values you meant, the result should show null for levels below the granularity level.


Trigger with multipe IF conditions doesn't trigger

Debug your trigger.

Create service table.

CREATE TABLE service (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                      step INT,
                      data TEXT,
                      dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Add tracing info save (example code).

CREATE TRIGGER update_latest_activity
    AFTER INSERT ON activity
    FOR EACH ROW
BEGIN
    INSERT INTO service (step, data) VALUES (1, NEW.log);
    INSERT INTO service (step, data) VALUES (2, NEW.log = 0);
    INSERT INTO service (step, data) VALUES (3, NEW.type);
    INSERT INTO service (step, data) VALUES (4, NEW.data);
    INSERT INTO service (step, data) VALUES (5, JSON_VALID(NEW.data));
    INSERT INTO service (step, data) VALUES (6, NEW.data->>'$.status');
    INSERT INTO service (step, data) VALUES (7, NEW.log = 0
                                                AND (NEW.type = 'hired'
                                                OR (NEW.type = 'note'
                                                    AND JSON_VALID(NEW.data) = 1
                                                    AND NEW.data->>'$.status' = 1)));
    IF NEW.log = 0
        AND (NEW.type = 'hired'
        OR (NEW.type = 'note'
        AND JSON_VALID(NEW.data) = 1
        AND NEW.data->>'$.status' = 1))
    THEN
        INSERT INTO service (step, data) VALUES (8, 'Execute THEN branch');
        REPLACE INTO
            latest_activities (employee_id, activity_id)
        SELECT
            pivot.employee_id AS employee_id,
            pivot.activity_id AS activity_id
        FROM
            pivot
        WHERE
            pivot.activity_id = NEW.id
            AND pivot.type LIKE '%Employee';
    END IF;
END

Insert record which must (as you think) to perform the action.

Analyse the info in service table.


How to mount a pg_basebackup on a stand alone server to retrieve accidently deleted data

Problem solved. For future reference of anyone facing the same situation I have listed the process below

Saved test servers server.crt and server.key
File structure created by pg_basebackup copied across to a test server.
Replaced server.crt and server.key with saved versions

sudo /etc/init.d/postgresql start

Startup failed.

Starting PostgreSQL 9.1 database server
The PostgreSQL server failed to start. Please check the log output:
2018-07-22 16:19:45.055 AEST [8837] LOG:  database system was interrupted; last known up at 2018-07-14 05:59:04 AEST
2018-07-22 16:19:45.126 AEST [8837] LOG:  could not open file "pg_xlog/000000010000024600000093" (log file 582, segment 147): No such file or directory
2018-07-22 16:19:45.126 AEST [8837] LOG:  invalid checkpoint record
2018-07-22 16:19:45.126 AEST [8837] FATAL:  could not locate required checkpoint record
2018-07-22 16:19:45.126 AEST [8837] HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.1/main/backup_label".
2018-07-22 16:19:45.127 AEST [8836] LOG:  startup process (PID 8837) exited with exit code 1
2018-07-22 16:19:45.127 AEST [8836] LOG:  aborting startup due to startup process failure

Tried removing the backup_label file as suggested in the hint.
sudo mv /var/lib/postgresql/9.1/main/backup_label /var/lib/postgresql/9.1/main/old_backup_label

sudo /etc/init.d/postgresql start
Startup failed.

Starting PostgreSQL 9.1 database server

The PostgreSQL server failed to start. Please check the log output:
2018-07-22 16:22:53.689 AEST [8875] LOG:  database system was interrupted; last known up at 2018-07-14 05:59:04 AEST
2018-07-22 16:22:53.735 AEST [8875] LOG:  could not open file "pg_xlog/000000010000024600000093" (log file 582, segment 147): No such file or directory
2018-07-22 16:22:53.735 AEST [8875] LOG:  invalid primary checkpoint record
2018-07-22 16:22:53.735 AEST [8875] LOG:  could not open file "pg_xlog/000000010000024600000093" (log file 582, segment 147): No such file or directory
emphasized text2018-07-22 16:22:53.735 AEST [8875] LOG:  invalid secondary checkpoint record
2018-07-22 16:22:53.735 AEST [8875] PANIC:  could not locate a valid checkpoint record
2018-07-22 16:22:53.785 AEST [8877] [unknown]@[unknown] LOG:  incomplete startup packet
2018-07-22 16:22:54.141 AEST [8874] LOG:  startup process (PID 8875) was terminated by signal 6: Aborted
2018-07-22 16:22:54.141 AEST [8874] LOG:  aborting startup due to startup process failure

/usr/lib/postgresql/9.1/bin/pg_resetxlog -f /var/lib/postgresql/9.1/main

Note: This is where I went wrong in earlier attempts. I did not specifiy the full path to pg_resetxlog and received a message saying "The program 'pg_resetxlog' is currently not installed. You can install it by typing: sudo apt-get install postgres-xc". I installed that, ran it, destroyed my data and somehow lost the postgres role in the process".
Specifiying the full path to the correct version solved the problem.

sudo /etc/init.d/postgresql start

Starting PostgreSQL 9.1 database server                               [ OK ]

Seems to have fixed it
Tested with psql and I can query the databases.


There are two ways of approaching the answer to your question:

First: Is pre-optimization a good idea?

As a general rule, don't pre-optimize on the assumption that you will have a problem.  Use volume testing to determine if you have a problem and denormalize for optimization purposes if that is the best of your available solutions/compromises.

Second: Is this a good case for denormalization?

Having said that, there is a practical limit to how much you want to be fussy about functional dependencies.  Are your type A and type B messages really that different?  They both seem to quack like a duck, as it were.  Having only a single attribute different, and that difference being whether it is null for one set of records and not null for another set of records isn't necessarily a good reason to implement two distinct message tables.

You might want to have a logical model that makes the distinction between type A and type B messages, but it doesn't necessarily follow that your physical model has to implement these two entity-types as separate tables.

You have the option of using a constraint to enforce the relationship between message type and section number.  You don't have to implement your constraint through normalization.


SSIS: Selecting multiple input columns and mapping them to their respective destination in one action

I found the solution, you can Right-click in the column names area on the diagram & Select "Map Items By Matching Names"

(source: hoegaerden.be)


It's not necessarily the number of transactions, but the timeout. The parameter distrubuted_lock_timeout is set to 60 seconds by default. The purpose of this parameter is to avoid having distributed transactions in a long running wait status while something else is performing work on that row; the transaction will wait 60 seconds, then Oracle kills it.

You can modify this parameter (requires an instance restart) to whatever you want (in seconds).


InnoDB table size increased substantially after ALTER

It's difficult to answer without knowing the nature of the data being copied and maybe of the integration software that is used.

But I decided to share my guesses hoping it may lead to better diagnostic:

Row length increase may be because texts were converted into Unicode during migration.
Lower row count maybe due to some kind of deduplication that also removed records with less text data thus increasing average row length. Or it might be just that the migration process was aborted in the middle.

It might help to make a more educated guess if you supplement the question with SHOW CREATE TABLE output for both source and target tables.


Large table with geospatial data - slow queries during updates

You can use INSERT DELAYED to avoid blocking SELECTs:

  When a client uses INSERT DELAYED, it gets an okay from the server at
  once, and the row is queued to be inserted when the table is not in
  use by any other thread.

  Another major benefit of using INSERT DELAYED is that inserts from
  many clients are bundled together and written in one block. This is
  much faster than performing many separate inserts.

Unfortunately, there is no equivalent for UPDATE. You can do REPLACE DELAYED (which first deletes the existing row if one exists and then inserts a new row), but that's probably not useful to you.

A few other maybe useful optimizations:

innodb_flush_method= O_DIRECT

innodb_flush_log_at_trx_commit= 2

Maybe consider if the geometry types (POINT) would be useful instead of your lat, lon doubles. Geometry types are indexable in InnoDB as of MariaDB 10.2.

Looking at your configuration, I think your query cache is set quite large. (Though it might make sense in your specific case, I don't know.) This can result in degraded performance due to lock contention, as the query cache is locked while it's updated.

I can see how the query cache might make sense in your use case if you're not writing to the database all that frequently. Note however that MySQL is retiring support for the query cache in MySQL 8.0. I don't know what the fate of the query cache is on the MariaDB side.


We're trying to define a backup policy.

You are starting from the wrong end of things.

Backups are only the tools that you use to support a Recovery Strategy.

Start by working out - in conjunction with your Business partners; you cannot do this alone - how long you can take to get back a "broken" database (Recovery Time Objective) and how much data they're prepared to lose while you go about doing so (Recovery Point Objective).

Once you understand these two constraints, you'll have a better feeling of what you need your backup tools to do.


You can't directly filter SSMS's commands, but here's an easy workaround:

Create another table with the subset of data you want to export:

USE TemporaryStagingDatabase;
GO
SELECT *
  INTO dbo.MyTemporaryDestinationTable
  FROM SourceDatabase.dbo.MySourceTable
  WHERE (my filtering conditions);

Ideally, you do this in a different database so you don't bloat the data & log files with your temporary export work.

Then, use SSMS's magical wizards to export the data from your staging table. (If you have to do this a lot, though, check out SSIS.)


MySQL ODBC + setting up a DSN on windows + MySQL Workbench

In case anyone else has this question in the future, I just figured out that you need an SSH client like putty to do this.


You would have to unroll it with jsonb_to_record or write a function in plv8,

SELECT jsonb_build_object(
  'id1', jsonb_build_array(id1),
  'id2', jsonb_build_array(id2),
  'id3', jsonb_build_array(id3)
)
FROM (VALUES ($${
  "id1": "something1",
  "id2": "something2",
  "id3": "something3"
}$$::jsonb)) AS t(j)
CROSS JOIN jsonb_to_record(j)
  AS t2(id1 text, id2 text, id3 text);


Why is READ COMMITTED a common default transaction isolation level?

Though, I don't remember any reference mentioned in BOL thus can't provide it here but as per my understanding it is related to locking. Higher level of isolation level can cause locking issues. READ COMMITTED ISOLATION is more towards write locks than read, which fits good in OLTP environment when compared to OLAP. Commercial databases decides default isolation level which fits perfect for their internal algorithm. Choosing isolation level depends upon how RDBMS wants to deal with locking and care about reading correct data. Most of the RDBMS prefers READ COMMITTED for faster read, performance and to minimize locking.

A lower isolation level increase concurrency and descrease waiting for other transaction but increase the chances of reading incorrect data.
However, a higher isolation level decreases concurrency and increases waiting for other transaction, but decreases the chance of reading incorrect data.

As per Wikipedia

  The default isolation level of different DBMS's varies quite widely.
  Most databases that feature transactions allow the user to set any
  isolation level. Some DBMS's also require additional syntax when
  performing a SELECT statement to acquire locks (e.g. SELECT ... FOR
  UPDATE to acquire exclusive write locks on accessed rows).

  However, the definitions above have been criticized [3] as being
  ambiguous, and as not accurately reflecting the isolation provided by
  many databases:

  This paper shows a number of weaknesses in the anomaly approach to
  defining isolation levels. The three ANSI phenomena are ambiguous.
  Even their broadest interpretations do not exclude anomalous behavior.
  This leads to some counter-intuitive results. In particular,
  lock-based isolation levels have different characteristics than their
  ANSI equivalents. This is disconcerting because commercial database
  systems typically use locking. Additionally, the ANSI phenomena do not
  distinguish among several isolation levels popular in commercial
  systems.

You can read the complete synopsis here.


Identifying processes that open MySQL connections

It seems
ss -p '( dport = :mysql )'

does the trick, listing the process ids of the processes on the local machine that connect to the mysql driver's port.


Error while creating an admin user in replica set

I found the issue, actually for some reason the mongod instance on VM2 was still on 2.4, which has an incompatible way of creating users and would crash when receiving new-style user data from the primary. I just fixed it by upgrading MongoDB on VM2


Index not being used on array elements in JSONB column

The left hand side of the WHERE expression must match the expression in the index, in order for the index to be usable (because the index stores a copy of that expression's value).
As your index is created on the expression data -> 'permissions' you need to use that in the query:
select *
from testuser
where data -> 'permissions' @> '[{"type":"ARTICLE"}]';

Note that this doesn't guarantee that the index is used. If the condition returns a substantial part of the table, the Seq Scan is still faster.

For the condition data @> '{"permissions": [{"type": "ARTICLE"}]}' you would need to create an index on data (not an expression)


Why do I get "The initial snapshot for publication is not yet available."?

With basic Transactional Replication there are a variety of agents.

Helpful MSDN article

There is the Snapshot Agent which is responsible for the initial snapshot of data and schema. This can be scheduled to run at regular intervals (I had mine scheduled for every 12 hours).

There is the Log Reader Agent, which reads the transaction logs and sends those which are marked for replication on to the distributor.

Finally, there is the Distribution Agent which is responsible for moving the snapshot(s) and the transactions from the distributor to the subscriber(s).

Your error is telling you that it cannot apply transactions that are marked for replication to the subscriber because the initial snapshot (and/or any of the subsequent snapshots) have not yet been applied. You need to troubleshoot why this snapshot is not being applied. There are many reasons why it could be happening.

Has the Distribution Agent lost permissions to the directory that contains the snapshot files?

Is there some blocking activity on the subscriber that is causing the snapshot to be applied slowly?

Have you overloaded the distributor and/or subscriber by pushing all articles from a large published database to all subscribers at the same time?


Read only/Standby database - Set user permissions

The user has to be granted permissions in the source copy of the database.

The restored copy is just that -- a copy -- of the source, so there's no way to change one and not the other.

Since this is a SQL Server login, you'll need to create the logins on both servers with identical SIDs so the mapping from the database user to the login matches in all cases. You can do this by recreating the login on the secondary, specifying the SID manually in the CREATE LOGIN statement.

If you went with a solution such as replication (not saying you should, just giving an example), this works using two read/write databases, where you could grant permissions completely independently.


How to solve the error "Error 'Got error 0 'No error' from NDBCLUSTER' on query"?

Ah ha. This seems to be the error you get when you try to replicate from a server that uses statement binlogs. I initially overlooked that this scenario was not supported.

I was able to work around this by adding an intermediate mysqld that just converts the statement replication binlogs into mixed replication binlogs (using log-slave-updates and binlog-format=MIXED).

Hope this helps someone in the future.


It is a thread _id which you see in SHOW Prpcesslist

ID

The connection identifier. This is the same value displayed in the Id column of the SHOW PROCESSLIST statement, displayed in the PROCESSLIST_ID column of the Performance Schema threads table, and returned by the CONNECTION_ID() function within the thread.

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-processlist-table.html


MongoDB - _id as String instead of ObjectId ? Relationship between collections or duplicate data?

Is it a good practice to store networks in a separated colletion?
(attention to the requirement above)

It depends on your app and how do you use them, but I can say collection design is okay.

Is there any compliance/convention issues building a relationship
between these two documents (like an SQL DBMS)?

Generally, No-SQL databases don't use for relationship collections.

And, I was wondering... can I store _id as String instead of
ObjectId? I've read some advices, but I don't have a conclusive
opinion about it. e.g : ... network: { _id: "192.168.1.0/24" }
(uniqueness guaranteed :D)

Yes, you can. BTW, uniqueness guaranteed by mongodb. Because _id field has a unique index by default.
db.inspections.insert({ "_id": "192.168.1.0/24", "test": 1 })


how to set a column to fetch the date now?

Like this?

CREATE TABLE "DATENOW"
(
  ID Char(8) NOT NULL,
  "Start_Date" Char(10),
  "Client_Name" Varchar(30),
  BALANCE Integer,
  Now_Column TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT PK_datenow PRIMARY KEY (ID)
);

If you INSERT into the table, and ommit a value for Now_Column (IE: it's NULL), the current timestamp will be used instead.

Also, never use CHAR/VARCHAR datatypes to hold dates (which I suspect you are trying to do with your Start_Date column) - use proper date datatypes.

Untested, by the way - this is from the documentation.


Retain indent formatting when compiling a stored procedure without Data Studio

It works fine for me in clpplus 1.6
$ clpplus -h | head -1
CLPPlus: Version 1.6
$ cat sql
create or replace procedure proc1()
begin
    declare vId int default 0;
    call dbms_output.put_line(vId+1);

/*
    Testing
*/
end
/
SELECT TEXT FROM SYSCAT.PROCEDURES WHERE PROCNAME = 'PROC1'
/
$ clpplus -nw $user/"$password"@$host:$port/$database @"$PWD"/sql
CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.

Database Connection Information :
---------------------------------
Hostname = localhost
Database server = DB2/LINUXX8664  SQL11054
SQL authorization ID = paul
Local database alias = BP
Port = 50003

DB250000I: The command completed successfully.

create or replace procedure proc1()
begin
    declare vId int default 0;
    call dbms_output.put_line(vId+1);
/*
    Testing
*/
end
SQL>


You can get Oracle 11 to run natively on OS X, but there is a bit of work required, and not as quick to set up as a pre-installed environment. However, this is a tried and tested method, so it will work, and it will run quicker and more responsively than if you opt for a virtual machine solution.

Taken verbatim from Olivier Refalo's answer to Installing Oracle 11g on OSX (I've not added blockquote formatting as it would mess up the original formatting):

Yes! The fact that Oracle doesn't provide a OSX installer doesn't mean these product shouldn't work natively. Most of them are actually 100% Java.

Please follow the instructions below to install you development environment.

Instructions are for 11g 11.1.1.4.0 but were also tested with 11.1.1.5.0. The trick is to run the installer via a Linux VM and migrate the files to OSX.

Linux VM Installation

It doesn't really matter if you get a 32 or 64 bits Linux distribution. However, the installation folder should be the same on Linux and MacOS.

Download VirtualBox and create a Virtual Machine for Linux. Since we are dealing with Oracle products, rather user their linux distribution: look for this file on google OracleLinux-R5-U7-Server-i386-dvd.iso
With the VM installed, up and running, boot into Linux.
Install JDK 1.6 for Linux 32 bits

Download the following software, oracle offers native installers for windows and Linux. However you should stick to generic downloads as much as possible. Some of these packages come in several zips, extract files according to oracle directions.

Weblogic & coherence: wls1034_generic.jar
OEPE: oepe-helios-all-in-one-11.1.1.6.1.201010012100-win32-x86_64.zip
jDeveloper: jdevstudio11114install.jar
OSB: ofm_osb_generic_11.1.1.4.0
SOA: soa_generic_11.1.1.4.0

Install the software in the following order

Weblogic & Coherence: run with java -Xmx1024m -jar -Dos.name=unix wls1034_generic.jar
OEPE extract act in a folder called /oepe
OSB Disk1/runInstaller - Make sure OSB IDE extensions are being installed (in OEPE)
SOA Disk1/runInstaller
jDeveloper

OSX Installation

Middleware

Here comes the trick, copy the oOracle Middleware folder from your Linux VM to OSX. Make sure the location is the same. For instance if you installed under /Oracle/Middleware on Linux, you should copy to /Oracle/Middleware on OSX.

Fix JVM

For some obscure reasons, Oracle installers don't recognize the Apple JVM. While not mandatory, it is good practices to fix the issue with the following script.

 $ sudo mkdir -p /System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/Home/jre/lib
 $ cd /System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/Home/jre/lib
 $ sudo ln -s /System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/classes/classes.jar rt.jar

OEPE

Download the following software (64bits please!)
oepe-helios-all-in-one-11.1.1.6.2.201111102323-macosx-cocoa-x86_64.zip
Extract the file into /oepemac
Right click on Eclipse.app
On the opened menu, choose “Show Package Contents”
Edit file “/Contents/MacOS/eclipse.ini”, append the following lines

-Dweblogic.home=/Oracle/Middleware/wlserver_10.3
-Dharvester.home=/Oracle/Middleware/Oracle_OSB1/harvester
-Dosb.home=/Oracle/Middleware/Oracle_OSB1
-Dosgi.bundlefile.limit=750
-Dosgi.nl=en_US
Now copy the file oracle.osb.ide.link from the folder “oepe/dropins” to “oepemac/dropins” (both under your middleware home).

Oracle XE

Oracle_XE is unfortunately not supported on OSX. we will need to run it via a VirtualBox appliance. The good news is that Oracle provides a easy to install RPM.

Download oracle-xe-10.2.0.1-1.0.i386.rpm
Under Linux, as root, run 'rpm -i oracle-xe-10.2.0.1-1.0.i386.rpm'
Next step is to download and run Oracle RCU to prepare the data model, please refer to the next section for directions.

For reference, we allocated 1cpu, 600MB of ram to our Linux/OracleXE VM.

Next Steps

Configure your web logic development domain. Please refer to this document for instructions.
Quick Start Guide for Oracle® SOA Suite 11gR1 (11.1.1.5.0).pdf

Fix startup scripts

Finally, you will need to fix domain startup script as follows

------------- user_projects/domains/DEVdomain/bin/setDomainEnv.sh -------------

index f74490c..8d75c6c 100755
@@ -108,7 +108,7 @@ else
    else
        JAVA_VENDOR="Unknown"
        export JAVA_VENDOR
-       JAVA_HOME="/usr/java/jdk1.6.0_21"
+       JAVA_HOME=`/usr/libexec/java_home`
        export JAVA_HOME
    fi
 fi

------------ user_projects/domains/DEVdomain/bin/setSOADomainEnv.sh ------------

index 8c6743b..b92cfa4 100755
    @@ -144,6 +144,15 @@ case ${PLATFORM_TYPE} in
         fi
         export USER_MEM_ARGS
         ;;
    +#-----------------------------------------------------
    +# OSX
    +#-----------------------------------------------------
    +Darwin)
    +
    +  USER_MEM_ARGS="${PORT_MEM_ARGS}"
    +  export USER_MEM_ARGS
    +
    +  ;;

       #-----------------------------------------------------
       # Sun OS


Access denied when trying to delete files with xp_cmdshell

You need to have a connection as well as permissions. SQL Server won't automatically connect to a network resource. You can do this either inside or outside of SQL Server but the connection has to be made on the machine. If there is a permission issue, then the 'net use' in the SQL query will return more info than just 'Access Denied'.

exec xp_cmdshell 'net use \\morrison\backups\angelia\AS_ReportingServicesDW_MOLAP mypassword /USER:ntdomain\angeliasql'

exec xp_cmdshell 'del \\morrison\backups\angelia\AS_ReportingServicesDW_MOLAP\ReportingServicesDW_MOLAP-20151116.abf'


Every time I had to do this, I used the RML Utilities (x86 and x64 package download links can be found on this page).

Basically, you just have to set up a server-side trace using one of the templates included in the package. The captured trace can be analyzed using ReadTrace (included in RML Utilities) and it populates a database with query analysis results. The results are shown using a tool called Reporter, which is just reporting services repackaged for RML Utilities.

ReadTrace also produces the RML intermediate files that you can use for replay against the new instance. The tool for replay is called Ostress.

Before attempting the replay (or even the capture), read carefully the manual
in order to understand how to synchronize the capture with the backup you will need to ensure that the workload runs against the same data, so that you get consistent results.

Before performing the replay, set up a trace on the new instance using the same template used for the initial capture. When the replay is over, you can analyze the workload using ReadTrace and use the compare feature in Reporter. It will show you which queries improved or got worse in the two captures, by CPU, elapsed and I/O.

It's not trivial, but it can be done with some patience and a bit of trial/error. Good Luck!

PS: the new version of RML utilities can also analyze a workload captured using Extended Events. In my experience, the files are significantly bigger this way. The trace templates used by RML and Distributed Replay are different, so don't try to use them interchangeably. If I recall correctly, there is a way to feed the Distrivuted Replay with Extended Events files, but I might be wrong. In my opinion this is an option you should be investigating only if Ostress can't keep up with an highly concurrent workload.


is Mentor of is a relationship between a teacher and a student.
From your diagram I conclude that a course can be only be enroled by one student. I is not explicitely stated but I think that a course can be enroled by many students. Also I think that a course can be part of more then one program. Finally I get the following diagram:

The diagram is created with dia and the source can be found here.

In the diagram all relationships are defined mandatatory. So the the diagram should be refined in respect to this. For example it seems to be wrong that each teacher is mentor of at least one student.


How do I set up active-passive database clustering between data centers?

So you've got a couple of options when it comes to doing this.

Option 1: Use a geographically dispersed cluster which is a single cluster deployed in both data centers.  This gives you one single cluster which exists in both sites.  You would end up having several nodes in each site so that when doing local site patching you don't have to fail over to the second site.  In the event of a site failure you would then fail the cluster over to the second site.  To change to the clients would be needed as they would just connect to the same virtual name which would then resolve to a new IP address.

Option 2: Database Mirroring between sites.  This is the preferred option as it's easier to setup, doesn't require expensive SAN replication software, and failover and back is all handled via SQL Server.  I'd recommend a two node cluster in each site for local HA, then mirroring between the sites for your DR setup.  Local HA covers server patching which the database mirroring covers you for DR.  If the sites are far from each other setup the system with asynchronous mirroring, if they are close to each other the you can use synchronous mirroring which will allow for no data loss.


Want to use pivot and lag function together in SQL

Because you only provided pictures i populated the tables with different data but this should alse apply to your case. Is this what you are looking for?
EDIT:
I changed the query to be dynamic. This should do the trick. Of course, you have to change table name #tbl to your table name.
/*Creating a table and populating it*/
CREATE TABLE #tbl (product nvarchar(50), region nvarchar(10), [year] int, sales int);
DECLARE @sql nvarchar(max);
DECLARE @columnname nvarchar(max);

INSERT INTO #tbl(product, region, [year], sales)
SELECT  'TEA',  'D1',   2018,   40
UNION ALL SELECT    'TEA',  'D2',   2018,   30
UNION ALL SELECT    'TEA',  'D3',   2018,   20
UNION ALL SELECT    'TEA',  'D4',   2018,   10
UNION ALL SELECT    'TEA',  'D5',   2018,   7
UNION ALL SELECT    'COFFEE',   'D1',   2018,   70
UNION ALL SELECT    'COFFEE',   'D2',   2018,   100
UNION ALL SELECT    'COFFEE',   'D3',   2018,   200
UNION ALL SELECT    'COFFEE',   'D4',   2018,   300
UNION ALL SELECT    'COFFEE',   'D5',   2018,   400
UNION ALL SELECT    'TEA',  'D1',   2019,   1
UNION ALL SELECT    'TEA',  'D2',   2019,   2
UNION ALL SELECT    'TEA',  'D3',   2019,   3
UNION ALL SELECT    'TEA',  'D4',   2019,   4
UNION ALL SELECT    'TEA',  'D5',   2019,   5
UNION ALL SELECT    'COFFEE',   'D1',   2019,   6
UNION ALL SELECT    'COFFEE',   'D2',   2019,   7
UNION ALL SELECT    'COFFEE',   'D3',   2019,   8
UNION ALL SELECT    'COFFEE',   'D4',   2019,   9
UNION ALL SELECT    'COFFEE',   'D5',   2019,   10
UNION ALL SELECT    'TEA',  'D1',   2020,   13
UNION ALL SELECT    'TEA',  'D2',   2020,   26
UNION ALL SELECT    'TEA',  'D3',   2020,   39
UNION ALL SELECT    'TEA',  'D4',   2020,   52
UNION ALL SELECT    'TEA',  'D5',   2020,   65
UNION ALL SELECT    'COFFEE',   'D1',   2020,   78
UNION ALL SELECT    'COFFEE',   'D2',   2020,   91
UNION ALL SELECT    'COFFEE',   'D3',   2020,   104
UNION ALL SELECT    'COFFEE',   'D4',   2020,   117
UNION ALL SELECT    'COFFEE',   'D5',   2020,   130
;

SELECT @columnname=COALESCE(@columnname+ ',', '') + QUOTENAME(CAST(q.region AS nvarchar(20)),'[]')
FROM
(SELECT DISTINCT t.region
FROM #tbl t
UNION ALL
SELECT DISTINCT t.region+'D' AS region
FROM #tbl t) AS q

/*for your case, just replace the table name #tbl with the name of your table*/

SET @sql='
SELECT product, [year], '+@columnname+'
FROM
(SELECT product,
CASE WHEN salee=''diff'' THEN region+''D'' ELSE region
END AS sales, [year], sale
FROM
(SELECT t.product, t.region, t.[year], t.sales, ISNULL((t.sales - LAG(t.sales) OVER(PARTITION BY t.product, t.region order by t.[year] asc)),t.sales) AS diff
FROM #tbl t) AS a
UNPIVOT
(sale for salee IN (sales, diff)) AS up) AS q
PIVOT
(MAX(sale)
FOR sales IN ('+@columnname+')) AS p
ORDER BY product'

EXECUTE sp_executesql @sql

Output:
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| product | year | D1 | D2  | D3  | D4  | D5  | D1D | D2D | D3D  | D4D  | D5D  |
+=========+======+====+=====+=====+=====+=====+=====+=====+======+======+======+
| COFFEE  | 2018 | 70 | 100 | 200 | 300 | 400 | 70  | 100 | 200  | 300  | 400  |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| COFFEE  | 2019 | 6  | 7   | 8   | 9   | 10  | -64 | -93 | -192 | -291 | -390 |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| COFFEE  | 2020 | 78 | 91  | 104 | 117 | 130 | 72  | 84  | 96   | 108  | 120  |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| TEA     | 2018 | 40 | 30  | 20  | 10  | 7   | 40  | 30  | 20   | 10   | 7    |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| TEA     | 2019 | 1  | 2   | 3   | 4   | 5   | -39 | -28 | -17  | -6   | -2   |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| TEA     | 2020 | 13 | 26  | 39  | 52  | 65  | 12  | 24  | 36   | 48   | 60   |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+


T-SQL: Assign identifier to block of results

Referencing SQL Server Lag Function to Group Table Rows on Column Value Changes:
--demo setup
drop table if exists Table1;
go

CREATE TABLE Table1
    ([Id] int, [Title] varchar(7), [BlockName] varchar(3), [Order] int)
;

INSERT INTO Table1
    ([Id], [Title], [BlockName], [Order])
VALUES
    (100, 'Title A', 'Foo', 1),
    (101, 'Title B', 'Foo', 2),
    (102, 'Title C', 'Foo', 3),
    (103, 'Title D', 'Bar', 4),
    (104, 'Title E', 'Bar', 5),
    (105, 'Title F', 'Foo', 6)
;

--solution
;WITH cte
AS (
    SELECT id
        ,Title
        ,BlockName
        ,[Order]
        ,lag(BlockName, 1, '') OVER (
            ORDER BY [Order]
            ) previous
        ,CASE
            WHEN (
                    lag(BlockName, 1, '') OVER (
                        ORDER BY [Order]
                        )
                    ) = BlockName
                THEN 0
            ELSE 1
            END ischange
    FROM Table1
    )
    ,tbl
AS (
    SELECT t.*
        ,(
            SELECT sum(ischange)
            FROM cte
            WHERE id <= t.id
            ) groupno
    FROM cte t
    )
SELECT Id
    ,Title
    ,BlockName
    ,[Order]
    ,groupno AS BlockGroup
FROM tbl

Id          Title   BlockName Order       BlockGroup
----------- ------- --------- ----------- -----------
100         Title A Foo       1           1
101         Title B Foo       2           1
102         Title C Foo       3           1
103         Title D Bar       4           2
104         Title E Bar       5           2
105         Title F Foo       6           3


The view could do something like this (obviously I've guessed at table/column names):

CREATE VIEW dbo.whatever
AS
  SELECT src.columns /*...*/ , IDList = STUFF((SELECT ',' + CONVERT(VARCHAR(12), p.ID)
    FROM dbo.ProperRelationalTable AS p
    WHERE p.EntityID = src.EntityID
    FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
  FROM dbo.SourceTable AS src;

However as @Martin pointed out in a comment, this view will break the application if it expects to perform any DML against the view rather than the base tables. You can avert this problem by setting up INSTEAD OF triggers as he explained.

A related post that might be useful in other ways aside from just the presentation aspect:

http://www.mssqltips.com/sqlservertip/2074/make-your-sql-server-database-changes-backward-compatible-when-changing-a-relationship/


How to bulk insert data in vertical into sql server?

You can use BULK INSERT and provide the FIELDTERMINATOR and ROWTERMINATOR values like below which will insert the data into the correct columns. The only downside is the data also includes the column name. A simple cursor to clean this up and the data looks fine.

You would want to insert into a staging table as all data types are VARCHAR on insert, then you could insert into a final data table that has correct data types after cleaning.

Create Table to Store Data:

CREATE TABLE dbo.SampleCSVTable
(
    agency    VARCHAR(255),
    booknbr   VARCHAR(255),
    bookdttm  VARCHAR(255),
    arrdttm   VARCHAR(255),
    aj_id     VARCHAR(255),
    casenbr   VARCHAR(255),
    off1      VARCHAR(255),
    off2      VARCHAR(255),
    arrplace  VARCHAR(255),
    howarr    VARCHAR(255),
    juvstat   VARCHAR(255),
    ko        VARCHAR(255),
    remarks   VARCHAR(255),
    armed     VARCHAR(255),
    federal   VARCHAR(255),
)
GO

BULK INSERT the data:

BULK INSERT dbo.SampleCSVTable
FROM 'D:\SQLDATA\SQL01DEV\SampleData.csv'
WITH
(
    FIELDTERMINATOR = '\n',
    ROWTERMINATOR = '\n\n'
)
GO

Clean the data (this cursor loops through each column and removes the column name from the inserted data):

DECLARE @ColName NVARCHAR(255),
    @SqlCmd NVARCHAR(255)

DECLARE C1 CURSOR FOR
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SampleCSVTable'

OPEN C1

FETCH NEXT FROM C1 INTO @ColName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SqlCmd = 'UPDATE dbo.SampleCSVTable SET [' + @ColName + '] = LTRIM(RTRIM(REPLACE([' + @ColName + '], ''' + @ColName + ''', '''')));'
    EXEC sp_executesql @SqlCmd

    FETCH NEXT FROM C1 INTO @ColName
END

CLOSE C1

DEALLOCATE C1

Query the data:

SELECT * FROM dbo.SampleCSVTable

Results:

agency  |   booknbr     |   bookdttm            |   arrdttm             |   aj_id       |   casenbr     |   off1    |   off2    |   arrplace            |   howarr  |   juvstat |   ko  |   remarks |   armed   |   federal
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NO      |   06000330    |   2006-07-19 10:56:00 |   2006-05-07 05:42:00 |   A000009454  |   06006640    |   619     |   634     |   DENNYS RESTAURANT   |   O       |           |       |           |           |   N
NO      |   06000331    |   2006-07-24 12:11:00 |   2006-07-16 16:11:00 |   A000003215  |   06010336    |   641     |           |   219 W GLENCOVE AV   |   V       |           |       |           |           |   N
NO      |   06000332    |   2006-07-25 15:11:00 |   2006-06-13 22:47:00 |   A000009455  |   06008615    |   624     |           |   113 JULIE DR        |   V       |           |       |           |           |   N


You can just use dbatools --> Start-DbaMigration  to do the entire server migration/copy - (choose backup/restore method) including jobs, logins, user objects created in system dbs, etc

  Start-DbaMigration
    [[-Source] <DbaInstanceParameter>] [[-Destination] <DbaInstanceParameter[]>]
    [-DetachAttach] [-Reattach] [-BackupRestore] [[-SharedPath] <String>]
    [-WithReplace] [-NoRecovery] [-SetSourceReadOnly] [-ReuseSourceFolderStructure]
    [-IncludeSupportDbs]
    [[-SourceSqlCredential] <PSCredential>]
    [[-DestinationSqlCredential] <PSCredential>]
    [[-Exclude] <String[]>] [-DisableJobsOnDestination] [-DisableJobsOnSource]
    [-ExcludeSaRename] [-UseLastBackup] [-Continue] [-Force] [-EnableException]
    [-WhatIf] [-Confirm] [<CommonParameters>]


pgAdmin Query Tool throws error when I try to run multiple statements

Per the comment by Fung:

  ...it was actually some non-visible character at the beginning of each
  comment. And the reason the statements still worked when run
  individually was because I was not running them with the comments


Return ResultSet with column names from functions

Consider the example below:

CREATE TABLE test.child (
    id    integer,
    p_id1 integer,
    p_id2 integer
);

CREATE OR REPLACE FUNCTION named_cols()
RETURNS SETOF test.child
AS $$
  SELECT 0, 12, 23;
$$ LANGUAGE sql;

SELECT named_cols();
 named_cols
────────────
 (0,12,23)

SELECT * FROM named_cols();
 id │ p_id1 │ p_id2
────┼───────┼───────
  0 │    12 │    23

If you call the function the first way, you will get back a record, without the column names.  It looks like you are after the second one - you would experience the same behaviour with any programming language, not only Java.


tempdb logs on same drive as tempdb data or with other logs?

The answer here depends on a lot of things, since you are using direct disks (Local RAID) and not SAN disks which are wide striped (I assume), sequential operations are faster, so the logs (tempdb included) are benefited in E: . However there can be a situation where you don't want tempdb activity (logging activity) to interfere with the logging on your other databases, this can be the case if your tempdb activity is erratic and not related to application performance, while the logging performance of your databases is directly important to your use case.

I would generally test both setups and see which does better under the load you intend to use (the benchmarks could be transaction times for SQL Server, or disk queue lengths or writes\sec on the log drive).


Howto recover SQL 2016 cluster instance with bad tempdb path setting?

Pinal Dave wrote up a nice article on how to fix this. Have you tried this?

How to Start SQL Server Service Without tempdb?

  NET START MSSQLSERVER /f
  Connect to SQL via SQLCMD
  Run ALTER DATABASE tempdb command
  Restart SQL Server

From the looks of things you are having an issue starting SQL that is a named instance. This Microsoft article explains how to use that command with a named instance:

How to: Start an Instance of SQL Server (net Commands)
Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service

Are you getting any errors in the error log for the service startup failure?


XML Parsing Illegal XML character error in SQL 2008

Here is how to find the "illegal" character:

There are two places in your query that cast to XML, so start with confirming which one is throwing the error. I doubt it is the outer SELECT since that is just taking a subset of the data that is already valid XML. And, if it was the outer CAST, that would mean that an individual deadlock graph would have at least 22,203 lines it. Most likely the error line number that is leading you to the outer select is simply referencing the query, not a particular part of the query.

Remove the CAST( and AS XML) parts of the outer SELECT. If the error still happens, it is coming from the CAST(target_data part, else it really is the outer part. OR, you can just highlight and run the inner-most SELECT (i.e. the one that is the derived table, Data).
Next, we need to see the string that you are trying to CAST into XML. This is tricky as you can't display more than 64k characters in the Grid in SSMS, and using SQLCMD will get 1 million characters, but this data is likely well over even that limit.

However, we can break it up into smaller pieces. As far as my testing shows, the fact that you have a line # in the XML that is above 1 indicates that there are newlines in the source value. So, we can capture the incoming target_data value from sys.dm_xe_session_targets and print it to the Messages tab of SSMS. Yes, the PRINT statement is limited to 8000 VARCHAR characters or 4000 NVARCHAR characters, but we can split up the original value on the newline characters and call PRINT on each chunk. I have the code for a Stored Procedure that does just this on PasteBin at T-SQL Stored Proc to PRINT NVARCHAR(MAX) values, which is based on my DBA.StackExchange answer How to output more than 4000 characters in sqlcmd.

Create the DisplayN Stored Procedure and then run the following query:

DECLARE @ShouldBeXml NVARCHAR(MAX);

SELECT @ShouldBeXml = st.[target_data]
FROM   sys.dm_xe_session_targets st
INNER JOIN   sys.dm_xe_sessions s
        ON   s.[address] = st.[event_session_address]
WHERE  s.[name] = 'system_health'
AND    st.[target_name] = 'ring_buffer';

EXEC dbo.DisplayN @ShouldBeXml;
PRINT N'-------------------------------------';

SELECT CONVERT(XML, @ShouldBeXml);

Scroll on down to whatever line # is noted in the "XML parsing: line XXXXX" error message that is below the ------------ line in the "Messages" tab.

If there is no obviously invalid character on that line (and you can try copying and pasting that line into a SELECT CONVERT(XML, N'{that_bad_line}'); statement to test out to confirm), then we can modify the DisplayN Stored Procedure to also output the VARBINARY hex values of each line so we can find hidden / control characters.


How to access the number of rows accumulated by RETURNs in PL/pgSQL

Use

GET DIAGNOSTICS integer_var = ROW_COUNT;

More in the manual in the chapter Obtaining the Result Status.
Your example could look like this:

CREATE OR REPLACE FUNCTION f_test()
  RETURNS SETOF table_a AS
$func$
DECLARE
    i  int;
    ct int := 0;
BEGIN

RETURN QUERY SELECT * FROM table_a;    -- 14 records
GET DIAGNOSTICS i = ROW_COUNT;  ct := ct + i;

RETURN QUERY SELECT * FROM table_b;    -- 8 records
GET DIAGNOSTICS i = ROW_COUNT;  ct := ct + i;

RETURN QUERY SELECT * FROM table_c;    -- 22 records
GET DIAGNOSTICS i = ROW_COUNT;  ct := ct + i;

RAISE NOTICE 'Returned % rows', ct;    -- 44

END
$func$ LANGUAGE plpgsql


Regularly import tab-seperated log file to MySQL

Sure, a simple script can parse the results of the tool and insert rows into the database.

You can make composite unique keys in mysql:

 CREATE index foo (mac, time);

If you try to insert a dup, it will fail (but will you ever have a duplicate time?).


how to calculate difference of first row and last row fields value in each group

You are not showing the query you are using to obtain the results without diff. I'm assuming it is something like this:

SELECT
  min  = MIN(Value),
  max  = MAX(Value),
  avg  = AVG(Value),  -- or, if Value is an int, like this, perhaps:
                      -- AVG(CAST(Value AS decimal(10,2))
  Date = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
GROUP BY
  DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
;

Also, you are not explaining what first and last mean. In this answer, it is assumed that first stands for earliest in the group (according to the Date value) and, similarly, last means latest in the group.

One way to throw in diff could be like this:

First, add two more aggregated columns, minDate and maxDate, to the original query:

SELECT
  min     = MIN(Value),
  max     = MAX(Value),
  avg     = AVG(Value),
  minDate = MIN(Date),
  maxDate = MAX(Date),
  Date    = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
GROUP BY
  DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
;

Next, join the aggregated result set back to the original table on minDate and on maxDate (separately) to access the corresponding Values:

SELECT
  g.min,
  g.max,
  g.avg,
  diff = last.Value - first.Value,
  g.Date
FROM (
  SELECT
    min     = MIN(Value),
    max     = MAX(Value),
    avg     = AVG(Value),
    minDate = MIN(Date),
    maxDate = MAX(Date),
    Date    = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
  FROM atable
  GROUP BY
    DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
) g
INNER JOIN atable first ON first.Date = g.minDate
INNER JOIN atable last  ON last .Date = g.maxDate
;

Note that the above assumes the Date values (at least those that happen to be first or last in their corresponding hours) to be unique, or you would get more than one row for some of the hours in the output.

An alternative, if you are on SQL Server 2005 or later version, could be to use window aggregate functions MIN() OVER (...) and MAX() OVER (...) to calculate Values corresponding to either minDate or maxDate, before aggregating all the results similarly to how you are probably doing it now. Here's what specifically I'm talking about:

WITH partitioned AS (
  SELECT
    Value,
    Date,
    GroupDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
  FROM atable
)
, firstlast AS (
  SELECT
    Value,
    Date,
    GroupDate,
    FirstValue = CASE Date WHEN MIN(Date) OVER (PARTITION BY GroupDate) THEN Value END,
    LastValue  = CASE Date WHEN MAX(Date) OVER (PARTITION BY GroupDate) THEN Value END
  FROM partitioned
)
SELECT
  min  = MIN(Value),
  max  = MAX(Value),
  avg  = AVG(Value),  -- or, again, if Value is an int, cast it as a decimal or float
  diff = MAX(LastValue) - MIN(FirstValue),
  Date = GroupDate
FROM firstlast
GROUP BY
  GroupDate
;

As you can see, the first common table expression (CTE) merely returns all rows and adds a calculated column GroupDate, the one subsequently used for grouping/partitioning. So it essentially just assigns a name to the grouping expression, and that is done for better readability/maintainability of the entire query, as the column is later referenced more than once. This is what the first CTE produces:

+-------+------------------+------------------+
| Value |       Date       |    GroupDate     |
+-------+------------------+------------------+
|    10 | 10/10/2010 10:00 | 10/10/2010 10:00 |
|    11 | 10/10/2010 10:15 | 10/10/2010 10:00 |
|    15 | 10/10/2010 10:30 | 10/10/2010 10:00 |
|    15 | 10/10/2010 10:45 | 10/10/2010 10:00 |
|    17 | 10/10/2010 11:00 | 10/10/2010 11:00 |
|    18 | 10/10/2010 11:15 | 10/10/2010 11:00 |
|    22 | 10/10/2010 11:30 | 10/10/2010 11:00 |
|    30 | 10/10/2010 11:45 | 10/10/2010 11:00 |
+-------+------------------+------------------+

The second CTE adds two more columns to the above result. It uses window aggregate functions MIN() OVER ... and MAX() OVER ... to match against Date, and where the match takes place, the corresponding Value is returned in a separate column, either FirstValue or LastValue:

+-------+------------------+------------------+------------+-----------+
| Value |       Date       |    GroupDate     | FirstValue | LastValue |
+-------+------------------+------------------+------------+-----------+
|    10 | 10/10/2010 10:00 | 10/10/2010 10:00 |         10 |      NULL |
|    11 | 10/10/2010 10:15 | 10/10/2010 10:00 |       NULL |      NULL |
|    15 | 10/10/2010 10:30 | 10/10/2010 10:00 |       NULL |      NULL |
|    15 | 10/10/2010 10:45 | 10/10/2010 10:00 |       NULL |        15 |
|    17 | 10/10/2010 11:00 | 10/10/2010 11:00 |         17 |      NULL |
|    18 | 10/10/2010 11:15 | 10/10/2010 11:00 |       NULL |      NULL |
|    22 | 10/10/2010 11:30 | 10/10/2010 11:00 |       NULL |      NULL |
|    30 | 10/10/2010 11:45 | 10/10/2010 11:00 |       NULL |        30 |
+-------+------------------+------------------+------------+-----------+

At this point, everything is ready for the final aggregation. The min, max, and avg columns will be aggregated same as previously, and diff can now easily be obtained as the aggregated FirstValue subtracted from the aggregated LastValue. As you can see from the above result set, you can use various functions to get FirstValue and LastValue for the group: it could be MIN, MAX, SUM, AVG – any if these would do, because there's just one value in every group.

The main SELECT, however, as you can see, applies specifically MAX() over LastValue and MIN() over FirstValue. That is intentional. It's because this second suggestion doesn't really require Date to be unique, like the first one did, but, in case either minDate or maxDate does happen to have more than one associated Value, it would result in FirstValue or LastValue containing more than one value per group, something like this:

+-------+------------------+------------------+------------+-----------+
| Value |       Date       |    GroupDate     | FirstValue | LastValue |
+-------+------------------+------------------+------------+-----------+
|     9 | 10/10/2010 10:00 | 10/10/2010 10:00 |          9 |      NULL |
|    10 | 10/10/2010 10:00 | 10/10/2010 10:00 |         10 |      NULL |
|    11 | 10/10/2010 10:15 | 10/10/2010 10:00 |       NULL |      NULL |
|    15 | 10/10/2010 10:30 | 10/10/2010 10:00 |       NULL |      NULL |
|    15 | 10/10/2010 10:45 | 10/10/2010 10:00 |       NULL |        15 |
|    17 | 10/10/2010 11:00 | 10/10/2010 11:00 |         17 |      NULL |
|    18 | 10/10/2010 11:15 | 10/10/2010 11:00 |       NULL |      NULL |
|    22 | 10/10/2010 11:30 | 10/10/2010 11:00 |       NULL |      NULL |
|    30 | 10/10/2010 11:45 | 10/10/2010 11:00 |       NULL |        30 |
|    33 | 10/10/2010 11:45 | 10/10/2010 11:00 |       NULL |        33 |
+-------+------------------+------------------+------------+-----------+

I assumed that in this situation it would be more natural to take the difference between the greatest last value and the least first one. You, however, should know better what rule to apply here, so you'll just change the query accordingly.

You can test both solutions at SQL Fiddle:

#1
#2

Update

Starting from SQL Server 2012, you can also use the FIRST_VALUE and LAST_VALUE functions and substitute them for the CASE expressions in the firstlast CTE in my last query above, like this:

FirstValue = FIRST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
LastValue  = LAST_VALUE(Value)  OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

In this case it will not matter whether you use MIN or MAX over FirstValue and LastValue later (in the main SELECT): each column will have exactly the same value (first or last Value accordingly) across all rows of the same GroupDate group, and so MIN() and MAX() would return identical results in each case.

In fact, you can get diff directly in the firstlast CTE and then, in the main query, just either aggregate it using MIN/MAX or add it to GROUP BY and reference it without aggregation, like this:

WITH partitioned AS (
  SELECT
    Value,
    Date,
    GroupDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
  FROM atable
)
, firstlast AS (
  SELECT
    Value,
    Date,
    GroupDate,
    diff = LAST_VALUE(Value)  OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
         - FIRST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  FROM partitioned
)
SELECT
  min  = MIN(Value),
  max  = MAX(Value),
  avg  = AVG(Value),
  diff,
  Date = GroupDate
FROM firstlast
GROUP BY
  GroupDate,
  diff
;

Moving one step further, you could obtain min, max and avg in firstlast as well, instead of the main query – using the counterpart window functions:

min  = MIN(Value) OVER (PARTITION BY GroupDate),
max  = MAX(Value) OVER (PARTITION BY GroupDate),
avg  = AVG(Value) OVER (PARTITION BY GroupDate),

With these three additional columns and the previous change, the firstlast CTE will be returning a row set like this for your example:

+-------+------------------+------------------+-----+-----+-------+------+
| Value |       Date       |    GroupDate     | min | max |  avg  | diff |
+-------+------------------+------------------+-----+-----+-------+------+
|    10 | 10/10/2010 10:00 | 10/10/2010 10:00 |  10 |  15 | 12.75 |    5 |
|    11 | 10/10/2010 10:15 | 10/10/2010 10:00 |  10 |  15 | 12.75 |    5 |
|    15 | 10/10/2010 10:30 | 10/10/2010 10:00 |  10 |  15 | 12.75 |    5 |
|    15 | 10/10/2010 10:45 | 10/10/2010 10:00 |  10 |  15 | 12.75 |    5 |
|    17 | 10/10/2010 11:00 | 10/10/2010 11:00 |  17 |  30 | 21.75 |   13 |
|    18 | 10/10/2010 11:15 | 10/10/2010 11:00 |  17 |  30 | 21.75 |   13 |
|    22 | 10/10/2010 11:30 | 10/10/2010 11:00 |  17 |  30 | 21.75 |   13 |
|    30 | 10/10/2010 11:45 | 10/10/2010 11:00 |  17 |  30 | 21.75 |   13 |
+-------+------------------+------------------+-----+-----+-------+------+

Note how GroupDate, min, max, avg and diff – the columns that you really need for the final set – are simply repeated across all rows that belong to the same group. That means that you can get rid of Value and Date, rename GroupDate to Date, slightly rearrange the columns, apply DISTINCT to the resulting set – and you have eliminated the last SELECT:

WITH partitioned AS (
  SELECT
    Value,
    Date,
    GroupDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
  FROM
    atable
)
SELECT DISTINCT
  min  = MIN(Value) OVER (PARTITION BY GroupDate),
  max  = MAX(Value) OVER (PARTITION BY GroupDate),
  avg  = AVG(Value) OVER (PARTITION BY GroupDate),
  diff = LAST_VALUE(Value)  OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       - FIRST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
  Date = GroupDate
FROM
  partitioned
;

Finally, it is also possible to move the GroupDate calculation into the same scope where min, max etc. are calculated. You can use CROSS APPLY for that and thus avoid the need for nesting a query entirely – in other words, this way you can get rid of the partitioned CTE as well. The entire query would look like this:

SELECT DISTINCT
  min  = MIN(t.Value) OVER (PARTITION BY x.GroupDate),
  max  = MAX(t.Value) OVER (PARTITION BY x.GroupDate),
  avg  = AVG(t.Value) OVER (PARTITION BY x.GroupDate),
  diff = LAST_VALUE(t.Value)  OVER (PARTITION BY x.GroupDate ORDER BY t.Date ASC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       - FIRST_VALUE(t.Value) OVER (PARTITION BY x.GroupDate ORDER BY t.Date ASC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
  Date = x.GroupDate
FROM
  atable AS t
  CROSS APPLY (SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)) AS x (GroupDate)
;

and return the same results. You can test it at SQL Fiddle too.


You are not seeing anything in the command window because you are telling SQLCMD.EXE to direct all output to a file due to using the -o switch. If you want results to go to a file and messages to go to the screen (and not into the file), then you cannot use the -o switch.

Instead, you will need to manually redirect output (i.e. "stdout") to the file using > on the command line, as in > log.xls. However, by default, PRINT messages will also go to "stdout", so you need to send those to "stderr" by using the -r1 switch (so that they will be displayed instead of going into the file). That will leave you with (writing to C:\ is not a good practice, so maybe create a TEMP directory/folder, or something like that):

SQLCMD -S (local) -i script.sql -s, -e -r1 > C:\TEMP\log.xls

You will then see the following in the command window:

  EXTRAÍDO POR:
  PASSWORD SETTINGS
  PASSWORD SETTINGS

The "(18 rows affected)" will still be in the output file. If you want that in the window and not in the file, then you need to:

Add SET NOCOUNT ON; to the top of your SQL script
Add PRINT '(' + CONVERT(VARCHAR(10), @@ROWCOUNT) + ' rows affected)'; after each T-SQL query (i.e. after each SELECT and EXEC in this script).

After doing those 2 steps, you will see the following in the command window:

  (1 rows affected)
  EXTRAÍDO POR:
  (1 rows affected)
  PASSWORD SETTINGS
  (8 rows affected)
  PASSWORD SETTINGS
  (18 rows affected)

ALSO:

You might need to remove the -e switch if sending the original SQL queries in script.sql to the top of the output file causes a problem when opening it in Excel (which I assume you are wanting to do given the .xls file extension).
Depending on your OSs regional setting and the code page associated with the default collation of your login's default database, you might need to change the current code page of the command window so that the Í character displays properly.

Meaning: I am in the US so my default code page for command windows is 437, yet the collation of my login's default database is a Latin1_General_* collation which uses code page 1252. In SSMS, PRINT 'EXTRAÍDO POR:' displays as:

  EXTRAÍDO POR:

in the "Messages" tab. But, in my command prompt window, I see:

  EXTRAIDO POR:

Command prompt shows " I " instead of " Í ". To fix this, I needed to run the following in the command prompt window before running SQLCMD:

chcp 1252

P.S. No, GO is not required in this script.


User defined data types causing implicit conversions

Okay, here is a repro:

USE tempdb;
GO
-- create one table with padding off:
SET ANSI_PADDING OFF;
GO
CREATE TABLE dbo.ap_off(a CHAR(1),
 CONSTRAINT ck_apOff CHECK (a IN ('N','Y')));
GO
-- and other with padding on:
SET ANSI_PADDING ON;
GO
CREATE TABLE dbo.ap_on(a CHAR(1),
 CONSTRAINT ck_apOn CHECK (a IN ('N','Y')));
GO

-- now, let's test queries
-- with padding on or off
SET ANSI_PADDING OFF;
GO
-- implicit conversion:
DECLARE @off CHAR(1) = (SELECT a FROM dbo.ap_off);
-- implicit conversion:
DECLARE @on  CHAR(1) = (SELECT a FROM dbo.ap_on);

SET ANSI_PADDING ON;
GO
-- implicit conversion:
DECLARE @off CHAR(1) = (SELECT a FROM dbo.ap_off);
-- NO implicit conversion:
DECLARE @on  CHAR(1) = (SELECT a FROM dbo.ap_on);
GO

DROP TABLE dbo.ap_off, dbo.ap_on;

Here's the same version of the plan for the first three queries:

And the fourth, missing the compute scalar and any implicit conversion:

So, as I suggested above, this doesn't have anything to do with alias types, but rather the fact that ANSI_PADDING is problematic in a whole bunch of ways (here's a timely article, a Stack Overflow question, and Microsoft's own documentation from SQL Server 2005 telling you to stop using it).

My suggested approach is to build new versions of any table with these non-ANSI_PADDING columns (this time with ANSI_PADDING ON of course), migrate the data, drop the old tables, and rename the new tables. Or do the same type of thing with just the individual columns (in either case it's going to be a complicated and disruptive thing that you'll likely want to do during a maintenance window).

You can identify the affected tables with:

SELECT s.name, t.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE t.is_ms_shipped = 0
AND EXISTS (SELECT 1 FROM sys.columns
 WHERE [object_id] = t.[object_id]
 AND is_ansi_padded = 0);

I would do this with one table or columns first, to see if it's worth it. See, in addition to eliminating the implicit conversions (which you think might cause performance issues), you should also demonstrate that performance will actually be different without them - and different enough to justify the effort, risk, and potential downtime. Otherwise, is it worth doing? We can't answer that, only you and your stakeholders can.


One database per company for "security" reasons

Separate databases are preferable for many reasons. THese are the ones I gave when we had the same decision to make:

Separating client databases means there is no way someone can manipulate a querystring to view another client's data.
If client1 somehow nukes their database, Client2 through Client-n isn't affected and rollback only affects a single client.
Better performance because each database has it's own separate file with its own dedicated IO, cursors, etc.
Smaller tables are quicker to access. This may not be an issue now, but five years down the line when you have 10 10GB databases, you'll be glad you don't have only one 100GB database.
By separating the databases, you remove the need for one whole layer of management. You don't need to check whether a request for data is allowed based on the source client and the targer client's data. This speeds up the data access even more.
Backups are much more simple. What would you do if Client3 asks you to create a backup of their data? You'll need to write some serious SQL-fu if all the data is in one database. Having separate db's for each client means you can simply use SSMS to run a quick export. Onle a few clicks, a quick ZIPm and the backup is ready for delivery.


Single row update using primary key affects many rows

As answered by ypercube in a comment to the question

There are one or more trigger(s) on your table.

The multiple (1 row(s) affected) messages are generated not just by your single-row update statement, but also by other statements in the triggers fired from that original action.


Restore Database using GUI - Wrong file to restore

Chances are something in the backup history tables got out of sync and the UI is going to the last "consistent" full backup.  If you're really interested in why it's doing what it's doing, start a profiler trace limited to your account, walk through the restore steps in the GUI, and review the commands captured in the trace that show what the UI is doing behind the scenes.  This will get you the definitive answer you're looking for.

If you'd rather just move past this, you can clear your backup history via EXEC [msdb].[dbo].[sp_delete_backuphistory] @oldest_date=getdate() (depending on the last time you ran this, you may want to clear it out a month at a time) and then take a new full, etc.  I would suspect this will reset the GUI to use the proper backups going forward.

Finally, another option is to run this script authored by Wayne Sheffield.  It may provide some more information regarding any issues with the backup chain.  I didn't come across this until after I posted this answer originally, but hopefully it helps someone else out in the future.


PostgreSQL: Find rows for which any index of any value contained is over a given size

Let me begin this reply with a caveat:  I've never encountered this exact problem, so I don't fully understand the nature of it.  However, I'm going to give my advice so that perhaps it gives you the insight you need to complete your task.

pageinspect extension

Recent versions of PostgreSQL come with an extension which you can install that allows you to inspect the individual pages of elements stored in your database.

First things first, all you need to do to use it is to run the command

 CREATE EXTENSION pageinspect;

and it's ready to go.

Now, once again, I don't fully understand the issue, but it appears you are concerned with the size of the indexed entry, and not necessarily the actual row entry in the heap.

For that case, you should use a function call from the pageinspect extension to query the pages of your BTree index, as

SELECT * FROM bt_page_items('idx_123', 1);

and you'll get back some results like

itemoffset  ctid   itemlen  nulls  vars  data
-----------------------------------------------------------------
1           (1,182)  16     f      f     "00 3e 9b ac b2 02 01 00"
2           (0,1)    16     f      f     "00 20 d6 fa 65 02 01 00"
3           (0,2)    16     f      f     "00 09 7b 30 66 02 01 00"
4           (0,3)    16     f      f     "00 f2 1f 66 66 02 01 00"
5           (0,4)    16     f      f     "00 db c4 9b 66 02 01 00"
6           (0,5)    16     f      f     "00 c4 69 d1 66 02 01 00"
...

Presumably, though you should check against your own data, you can see the number of bytes which the index key is using by checking the itemlen field. I am betting that you can use this to check against your pre-determined limit which you said was 2712 bytes, and in that case use this info to collect all the ctids which violate your condition.

Broadly speaking then, you need to create a PL/pgSQL function to iterate over the pages of your index(s), checking the itemlen, and collecting the ctids of the heap entries, and once completed, since you state it was OK to drop violating rows, then wrap it all up with a

DELETE FROM my_table WHERE my_table.ctid = *** your ctid list ***;

I hope this helps!


Conditionally sum a column based on a mod result

As my boss stated, all I had to do was edit the inner statements instead of trying to manipulate the results on the outer query. We managed to do what I needed with this:

SELECT  y.datamov,
    y.n_conta,
    y.sinal,
    y.valor,
    a.nome,
    '',
    0,
    y.lrdarc,
    y.quantidade
FROM (
    SELECT k.datamov,
        k.n_conta,
        k.sinal,
        sum(k.qtde_valida) as quantidade,
        k.grupo as lrdarc,
        sum(k.total) AS valor
    FROM (
        SELECT DATE (b.dt_normal) AS datamov,
               b.historico,
               a.obs,
               a.total,
               b.sinal,
               c.nome,
               a.n_conta,
               c.lrdarc,
               case when mod(c.lrdarc, 2) !=0 then
                    a.qtde
               else
                    0
               end as qtde_valida,
               trunc(c.lrdarc / 10) as grupo
        FROM caixa.rtitens a
        LEFT JOIN caixa.rtlan b ON (b.numero = a.n_lan)
        LEFT JOIN caixa.rtctares c ON (c.numero = a.n_conta)
        WHERE DATE (b.dt_normal) BETWEEN DATE ('2010-02-13') AND DATE ('2010-02-20')
        AND c.lrdarc != 0 AND b.sinal = 'E'
        ORDER BY 1, 7, 5
        ) k
    GROUP BY 1, 2, 3, 5
    ORDER BY 1, 2, 3
    ) y
LEFT JOIN caixa.rtctares a ON (a.numero = y.n_conta)
ORDER BY 1, 2, 3

Note that the select from the most inner query, where we've added this:

...
c.lrdarc,
case when mod(c.lrdarc, 2) !=0 then
     a.qtde
else
     0
end as qtde_valida,
trunc(c.lrdarc / 10) as grupo

This solved the problem.


Is there any way to keep rows with same id sequential even if they are not stored sequentially?

In rare cases the storage order of the data does matter for performance.  By "rare" I mean less than maybe 2% of tables benefit enough to bother doing anything.

Here's a use case where clustering consecutive rows 'together' (or at least 'close') could lead to significant decrease in I/O (sometimes 10-fold).

The table is too big to be cached in RAM in InnoDB's buffer_pool -- hence the potential for being slowed down by I/O.
The main queries do a range scan on whatever controls the data order.  For InnoDB, that is a range scan on the PRIMARY KEY.
Or the main queries are otherwise "close" because of a 2-column PRIMARY KEY.  Think of a messaging system and PRIMARY KEY(user_id, message_id) and the user is jumping around in his messages.

In one application where message_id is AUTO_INCREMENT, changing from

PRIMARY KEY(message_id),
INDEX(user_id, message_id),
INDEX(user_id, ...)

to

PRIMARY KEY(user_id, message_id)  -- clustered primarily on user_id
INDEX(message_id),    -- sufficient for AUTO_INCREMENT
INDEX(user_id, ...)   -- benefits, too

nearly doubled the capacity of the server.

WordPress's wp_postmeta is an example of where this can be beneficial.  But I would say that less that 2% of such instances are big enough to matter.  Still, there are various reasons to change from

  PRIMARY KEY (meta_id),
  INDEX(post_id),
  INDEX(meta_key)

to

  PRIMARY KEY(post_id, meta_key),
  INDEX(meta_key)

More discussion:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta . (This link also discusses what to do if you really need to keep meta_id.)

That brings up another topic.  Should the PRIMARY KEY always be a surrogate AUTO_INCREMENT?  In my experience, two-thirds of tables have a perfectly good "natural key" (sometimes composite) that could be used instead.  Wp_postmeta is one of many examples where the surrogate gets in the way of performance.

Semi-related:  A huge table with a UUID index is hopeless for performance.  Clustering won't help because you never do a 'range' scan and two UUIDs are rarely 'near' each other.  (A partial workaround:  http://mysql.rjweb.org/doc.php/uuid )


Restoring .trn files on a regular basis to a backed-up database

You should only issue WITH RECOVERY when it's the last backup to restore or when you are sure that is the point where you want to go. After that there's no way to restore any more backups.

Now you will need to restore everything again to keep the same log chain.

If you need to "open" the db without break the log chain, you must use WITH STANDBY = 'path/file'. This will open the DB in READ ONLY mode and when you need to restore more files, SQL Server will use the 'path/file' you create to allow the DB to continue restore the log chain.

You may take a look to this option here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql?view=sql-server-2017


Do I need to increase undo_size? …

Yes, that is exactly what you need to do. The export needs a consistent MVCC snapshot from the start of the operation. UNDO_RETENTION is ignored if your UNDO tablespace is fixed size or can't be extended:

  UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

  … How it [undo size] should be calculated based on my database configuration?

It needs to be calculated based on how quickly you are generating UNDO (with DML), and how long the export takes — you'll need to find a size that works for you or try and do the export when less UNDO is being generated, or speed up the export. If you have the space available, increasing the size of your UNDO is the easy solution.


How to get all documents from a particular chunks in MongoDB?

use config show collections

  db.chunks.findOne()

  use config db.settings.save({_id: "chunksize", value: 2})

  sh.status()


With PostgreSQL getting ERROR: COPY format "CSV" not recognized

If format is a string, the string is case sensitive,

test=# \COPY table FROM 'file.csv' WITH ( FORMAT 'csv', HEADER true );

But, the format doesn't have to be a string,

test=# \COPY table FROM 'file.csv' WITH ( FORMAT CSV, HEADER true );

You're probably use to using the non-stringified version. The syntax for the copy_options is pretty hectic,

FORMAT format_name
OIDS [ boolean ]
FREEZE [ boolean ]
DELIMITER 'delimiter_character'
NULL 'null_string'
HEADER [ boolean ]
QUOTE 'quote_character'
ESCAPE 'escape_character'
FORCE_QUOTE { ( column_name [, ...] ) | * }
FORCE_NOT_NULL ( column_name [, ...] )
FORCE_NULL ( column_name [, ...] )
ENCODING 'encoding_name'

A lot of them only accept strings, but some of them accept regular tokens which get lowercased.

This specific thing isn't documented explicitly but you can find more information about COPY here


barcode inventory design - transaction or master?

Your sum idea works to a point, and then you will end up with a slow transaction whenever you add or remove items from stock due to scalability issues.

The way I did it for a similar problem, was to maintain a running total table, which would work in your case.

I had an audit table as well for accounting purposes, which had original values in case the accountants got twitchy, but the running total option is more scalable.


Working with PostgreSQL information_schema and tables (with same names, in different schemas) whose names start with a number

@ypercube's solution (in the comments above):

SELECT column_name, data_type
FROM   information_schema.columns
WHERE table_schema = 'crs_1973' AND
table_name = '2015_04_21'
ORDER  BY ordinal_position;

worked perfectly. I'm writing it up here so that I can accept the answer.


How to have CTE query recurse parent query result one by one?

It does not seem possible to get control on how the recursion happens in CTE.

One solution to disentangle results is to add info on the parent query and build a 'path' for each result.

As shown in this post:
How do I sort the results of a recursive query in an expanded tree-like fashion?

This seems to me the most acceptable way at the moment, but I'm still looking for a way to be able to directly get more concise results.


After adding indexes to an AWS RDS MySQL DB getting huge/crippling spikes in IOPS

Few things you can look forward to this issue.

IOPS limit is based on your volume size if you are using General Purpose SSD.
And IOPS are your reserved IOPS.

In staging its fine. Check both volumes are in the same size.
Sometimes you are getting a sudden spike, in this case, enable Enhanced monitoring to track every 1sec of MySQL activities.
Enable slow query log and set the long-running query time to 0. So it'll capture all the queries.
Compare the enhanced monitoring IOPS graph with the slow query log table, then you can get an idea for the bad queries.


Why does referencing a variable in a join predicate force nested loops?

I've tried your query on a SQL 2012 instance and trace flag 4199 seems to fix the issue. With it enabled I get a merge join for a total cost of 0.24 and none of the extra branches.

The specific KB article for this issue is Performance issues occur when the join predicate in your query has outer reference columns in SQL Server 2005 or in SQL Server 2008

To further qualify, TF 4199 enables all optimizer fixes.  See this link for more information. Enabling everything at once can have weird side-effects, so if you can find a specific fix it might be better to enable the fix on it's own.

You can enable a trace flag on a per query basis using OPTION (QUERYTRACEON 4199);


Availability Group Notification when new DB is added

...a notification email like below is sent to us

Sounds like you have alerts set up for error number 1480.  This is a good thing to have on systems that use Availability Groups.

  Can someone please decode the meaning of it?

When the message says "SECONDARY" to "SECONDARY" like that, it normally means there was just a brief disconnect between the PRIMARY and the SECONDARY, and the connection was re-established just before the error was raised.  It's essentially bad timing.

  Is it normal that a notification of such is sent when new DBs are added or am I doing it wrong or any other issues ?

I wouldn't call it "normal" but I don't think it's a cause for alarm, or something that you're explicitly causing.  I have heard that this can happen from time to time, especially on VMs with low core counts (1-3 cores).

I've personally experienced this more when adding a database to an AG with automatic seeding turned on.  I haven't seen any problems arise related to this no-op "failover" message.


what is default balancing window in MongoDB

The default balancing window is 24x7x365, which means balancer will always move chunks if its on


How to copy MongoDB database from server to local machine?

Basically what is missing from your mongodump command is... "" round your --host -value.
--host="host-name:27017"

Check similar question here


Proper creation of a database with categories that have sub-categories

Meals and Ingredients are two separate entities joined like M:N. The most common storing structure consists from 3 tables - one table per each entity (2 tables) and join table which is referenced to first two tables. - Akina

You might find the following Q & A helpful: When to add another lookup table for the same field


Java application unable to access Blob stream in Oracle after upgrade to 11g

I have faced similar situation for a procedure call with input Blob parameter. Same piece of code was working just fine on 11.2.0.2, but began to throw
"java.io.IOException: I/O Exception: Connection reset by peer: socket write error"
on 11.2.0.3. I have downgraded from ojdbc6.jar to ojdbc14.jar+ocrs12.jar now it is working. I don't know why but it seems ok for now.


Query Optimizer using clustered index seek the wrong time

Looks like query performance sometimes suffers due to parameter sniffing and the row goal implemented by your TOP 1 operator. I'll create some sample data similar to your table to illustrate what might be going on:

CREATE TABLE table_name (
size INT NOT NULL,
[weight] INT NULL,
[location] INT NULL,
INDEX CIX CLUSTERED (size)
);

-- insert around 6.5 million rows, lots of skew for weight column
INSERT INTO table_name WITH (TABLOCK)
SELECT 10
, FLOOR(1000 * SQRT(1 - (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 6500000.)))
, 0
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE INDEX IX ON table_name (size, [weight]);

CREATE STATISTICS table_name__weight ON table_name ([weight]) WITH FULLSCAN;

Here is an abbreviated histogram for the weight column:

╔══════════════╦════════════╦═════════╦═════════════════════╦════════════════╗
║ RANGE_HI_KEY ║ RANGE_ROWS ║ EQ_ROWS ║ DISTINCT_RANGE_ROWS ║ AVG_RANGE_ROWS ║
╠══════════════╬════════════╬═════════╬═════════════════════╬════════════════╣
║ 145          ║ 0          ║ 947     ║ 0                   ║ 1              ║
║ 172          ║ 27219      ║ 1139    ║ 26                  ║ 1046.885       ║
║ 190          ║ 20395      ║ 1261    ║ 17                  ║ 1199.706       ║
║ 207          ║ 21119      ║ 1379    ║ 16                  ║ 1319.938       ║
║ ...          ║ ...        ║ ...     ║ ...                 ║ ...            ║
║ 996          ║ 0          ║ 77686   ║ 0                   ║ 1              ║
║ 997          ║ 0          ║ 92220   ║ 0                   ║ 1              ║
║ 998          ║ 0          ║ 120274  ║ 0                   ║ 1              ║
║ 999          ║ 0          ║ 290616  ║ 0                   ║ 1              ║
╚══════════════╩════════════╩═════════╩═════════════════════╩════════════════╝

For example, thee table has 947 rows with a weight of 145 but 290616 rows with a weight of 999. Those filtered values will be used later.

You said:

  You would think it should be faster but the I/O cost is over 200.

The first important thing to realize is that not all clustered index scans or seeks are considered equal. SQL Server may report the same IO cost and CPU cost but it may scale down the total cost of the operator depending on how many rows it expects to read. Consider the following two trivial queries:

select top 1 [location]
from table_name;

select top 9999999 [location]
from table_name;

Here's the query plan:

Obviously the first query should be cheaper, but the query plans look the same. The operator cost for the first scan is 0.0032831 optimizer units and the operator cost for the second scan is 24.1525 optimizer units. Both scans have the same reported IO cost which is confusing. The physical operation for both queries may be a scan but the first scan can stop after it finds the first row. The row goal introduced by the TOP 1 reduces the subtree cost of the first query but not the second one.

You also said:

  When I look at the table, there is only one distinct Size value (10) in the table. So now, SQL server has to traverse the 8 million rows to find the second and third fields in the predicate.

Having just one distinct value for size actually favors the clustered index seek plan. The same principle described above applies when you have a filter. SQL Server may estimate based on statistics that it won't need to read all of the pages from the table to find just a single row that matches the filter. So if a filter condition is always met that means that SQL Server will have to scan less rows to find a match. For example, the following queries have different costs:

select top 1 [location]
from table_name
where size=10 and [weight]=999;

select top 1 [location]
from table_name  WITH (INDEX(0))
where size=10 and [weight]=145;

I'm using INDEX(0) to force a clustered index seek for the second query plan. Here's the query plan:

The first query has a lower cost because more rows in the table have a value of 999 for weight than 145. SQL Server expects to read back fewer rows in the scan before it finds the first matching one for the first query. The actual query plan in SQL Server 2016 shows exactly how many rows were scanned before a match was found. The first query is the best possible case because SQL Server scanned one row to find a match:

The second query is nearly the worst possible case because SQL Server scanned almost all rows to find a match:

Due to how the data was loaded we ended up with very inaccurate estimated costs. The costing model used for row goals can lead to inefficient queries if the assumptions of that model don't match how the data is distributed in the pages of the table.

Without the hint, you get the plan that you were expecting when filtering on a weight of 145:

I suspect what happened was there was no cached plan for this query. The first query that ran had very nonselective filter values for weight and plant. SQL Server calculated due to the row goal that a clustered index seek was cheaper than a seek with your nonclustered index and that plan was cached for later reuse.

So, what can you do about it? SQL Server 2016 has a query hint that can disable the row goal optimization for an individual query. This hint could have negative effects on other parts of the plan, but it will disfavor the clustered index seek that you want to avoid:

select top 1 [location]
from table_name
where size=10 and [weight]=999
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));

Since you're on SQL Server 2012 you can use trace flag 4138 as described here. If you'd like something less drastic, you could try defining a covering index on the table, you could add an ORDER BY to make the query deterministic, you could add an index hint to the query, or you can force the query plan using any of the available options in SQL Server 2012.


Database is too large due to appended backup

I'm seriously doubting that it should be anywhere near this.

Run sp_spaceused to analyze the actual space in use in the MDF. Run DBCC LOGINFO to analyze the space used in the LDF. Measurement beats guess every single time.

  I suspect it's because the db has been backed up by my host with the
  "Append to the existing backup set" option checked.

Absolutely unrelated.

  how can i get the db to a reduced size?

First, measure it, see what is the required size. If the database is indeed largely empty, first you need to understand why it reached a larger size. Unless you have a clear explanation why it grew (the backup option has, again, absolutely nothing to do with database growth) you cannot device a plan how to reduce the size. As things stand right now, w/o actual root cause analysis, the verdict is that the database has the right size and should not be touched. We cannot recommend any action w/o actually knowing 1) if the database is actually larger than needed, 2) what part of the database is actually too large (reserved space consumed by ghosts, unallocated space, log VLFs etc) and 3) what is the action that causes growth.

Of course, you can always shoot first ask questions later (like any good self respecting DBA from Monument Valley would...) and attempt to shrink the database w/o further analysis, but I hope you're better than that.


Here's a solution with a crosstab:

DECLARE @SampleData TABLE (
    Name varchar(50),
    Value varchar(50),
    Sequence int
)

INSERT INTO @SampleData
VALUES
 ('test'        ,'A1',         1)
,('test'        ,'A2',         3)
,('test2'       ,'A20',        5)
,('test2'       ,'A10',        8);

WITH RankedData AS (
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sequence)
    FROM @SampleData
)
SELECT Name,
    [Value+] = MAX(CASE WHEN RN = 1 THEN Value ELSE '' END),
    [Value-] = MAX(CASE WHEN RN = 2 THEN Value ELSE '' END)
FROM RankedData
GROUP BY Name


How can I make an Aging Report using mysql?

The CASE statement will work. It returns a value depending on a condition, so you can assign each invoice's value to exactly one age bucket.

To be able to roll up the values at whatever granularity required use a sub-query to put values into appropriate buckets, then in the super-query perform the required grouping.

select
  sum(Balance30) as Balance30,
  sum(Balance60) as Balance60,
  ..etc.
from (
  select
    supplier_id,
    case
      when datediff (curdate (), order.date) <= 30 then order.balance
      else 0
    end as Balance30,
    case
      When datediff (curdate (), order.date) > 30
        and datediff (curdate (), order.date) <= 60 then order.balance
      Else 0
    End as Balance60,
  ... etc
) as SomeAlias
group by
  supplier_id,
  .. or whatever

DATEDIFF calculates how old each invoice is, according to your chosen reference date. The CASE decides which bucket it should be in, and returns zero for each row which is not in this bucket. This assigns each invoice's value into exactly one of the buckets. The output from the inner query is one row per row in the base table, with key values and one column per bucket. The SUM in the outer query tallies all rows according to the grouping conditions, but we've substituted zero for non-matching rows so effectively filtered them out.

This assumes you want a column per bucket. If you need a row per bucket the same technique applies. Instead of returning order.balance from the CASE, return a bucket name. Wrap the whole query in group by bucketName and you will have the desired outcome.


Updating a WHERE clause to check if a value is NOT in a separate table

CREATE TABLE dbo.CustomerExclusions
(
  CustomerNumber VARCHAR(32) PRIMARY KEY -- Is CustomerNumber *really* a string?
);

INSERT dbo.CustomerExclusions(CustomerNumber) VALUES('1234'),('5678');

Now your WHERE clause across all queries becomes:

WHERE NOT EXISTS
(
  SELECT 1 FROM dbo.CustomerExclusions AS c
  WHERE c.CustomerNumber = SearchHistory.CustomerNumber
)


MariaDB Columnstore unexplained behaviour using CASE

WHEN 'string_hash_id' THEN

-->

WHEN `string_hash_id` THEN

Apostrophes and double-quotes are for strings; backtics are for column names.


SQL Server, attaching a database with Filestream, error 2

It appears you are trying to use the GUI interface to attach the database. In this instance you are going to have to write a T-SQL statement in order to attach it, and let SQL Server know where you put the filestream data as well.

There is a write up here that provides a walk through on doing this, the command the author used in order to attach a database once he had moved the default location of the filestream data for the example database:

USE [master]
GO
CREATE DATABASE [FileStreamDB] ON
( FILENAME = N'C:\FileStreamDB\FileStreamDB.mdf' ),
( FILENAME = N'C:\FileStreamDB\FileStreamDB_log.ldf' ),
FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT
( NAME = N'FileStreamDB_FSData', FILENAME = N'C:\FileStreamDB\FileStreamData' )
FOR ATTACH
GO


Best way to know if my environment has workload repository license

By looking at the contract.

Oracle does not technically limit installing and using features, anything can be installed, whatever you have in the database may not reflect what you are allowed to use. It is the responsibility of the DBA to not install or disable unlicensed features wherever it is possible.

Starting with 11g, there is a parameter called CONTROL_MANAGEMENT_PACK_ACCESS. Its default value enables both Diagnostic and Tuning Packs, but in a properly managed environment it should be set according to the license. When the value of the parameter includes DIAGNOSTIC, then Diagnostic Pack (which includes AWR) is enabled, otherwise it is disabled.


See other schema's tables in SqlDeveloper

If you have the correct privileges to see the objects in other schemas you can see them by expanding the connection in the connections tab and looking for Other Users and you will be able to see all other users objects.

You can also start with the schema prefix and use that to see and access objects in other schemas.