What happens with WITH if later the query fails

A single statement is always run atomically.

Quote from the manual

  PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block

A set of common table expressions defined under the same WITH clause, however many of them (including writeable CTEs), is still part of a single statement.

So the statement either fails completely or succeeds completely.

You only need (explicit) transactions if you want to run atomically more than one statement.

The handling of generated sequence values is no different than in any other situation: a nextval operation is never rolled back


MySQL accepts ' and " interchangeably.  So use ' to quote " and " to quote ':

REPLACE(text, "'", '"')

(There are many other solutions, involving hex, conversions, etc, but the above seems simple and straightforward.)

But...  The error message does not look like a MySQL error, so I suspect your query is in a quoted string in your client language.  So...

What client language?  Does it use backslash (\) for escaping things?


How can I calculate the cost of the query, if there is a `between` condition?

If there is no index for A.id column, then database engine perform full table scan.  So O(N) operations are performed.

If there is a B+tree index for A.id then database optimizer may choose it, if it is better than full table scan.

In a B+tree all the leaf nodes/records are linked; a node may have more than 2 elements.  Because all the records/leaves are linked in order (asc|desc), to get matching value for given range, it is sufficient find the min record location then we can iterate sequentially till max value.

For a b-order B+ tree, finding a record requires O(logb(N)). If there is a m matching elements for (10,50 ) range, if we assume DB engine follow the above said method, then it requires O(logb(N))+O(m) operations to find matching rows.


You may have to login to MariaDB and run
SHOW GLOBAL VARIABLES LIKE 'open_files_limit';

and see if this is 32000 or 90162. mysqld may be capping it.
It is entirely possible that open_files_limit is getting autoadjusted. The Documentation says Default:5000, with possible adjustment and Maximum:platform dependent.
Here is how mysqld_safe and mysqld interact:

mysqld_safe will pass values to mysqld
mysqld may override values passed from mysqld_safe

According to MySQL Documentation on mysqld_safe:

Many of the options to mysqld_safe are the same as the options to mysqld. See Section 5.1.4, “Server Command Options”.
Options unknown to mysqld_safe are passed to mysqld if they are specified on the command line, but ignored if they are specified in the [mysqld_safe] group of an option file. See Section 4.2.6, “Using Option Files”.

What you may need to do is remove open_files_limit from my.cnf and let mysqld_safe assign the value it knows to mysqld.
Then, restart mysqld.
UPDATE 2018-02-28 08:24 EST
You may need to add [mysqld_safe] header in my.cnf and put open_files_limit=32000, like this:
[mysqld_safe]
open_files_limit=32000

However, don't restart mysqld. This may not properly terminate mysqld_safe because it contains an indefinite loop to check the return code of mysqld when it terminates. There is a certain return value that lets mysqld_safe know to relaunch mysqld. This means that mysqld_safe does not terminate when it handle a mysqld restart.
It is better to do the restart in two steps
service mysql stop && service mysql start

Doing service mysql stop will terminate mysqld and then exit the indefinite loop in mysqld_safe, terminating mysqld_safe. Then, launching service mysql start will launch a fresh mysqld_safe process. Give it a try and let us know.
If you cannot edit my.cnf, then specify the option on the command-line:
service mysql stop && service mysql start --open-files-limit=32000


Is there a way to input field information by cvs file?

I found how to copy structure only for a access database table:

  To copy the table structure, follow these steps:

  Right-click the existing table name in the Database Window of the original database and click Copy.
  Close the database Window and open your new database.
  Under Objects, click Tables. Then, right-click the database Window and click Paste.
  Enter a name for the new table, choose Structure Only, and then click OK.

Maybe this helps.

Source:

http://www.techrepublic.com/article/copy-an-existing-table-structure-into-a-new-access-database/


How high can I set undo retention on Oracle 11gR2

Very high is a relative term that depends on perspective.  You could mean 1 hour, 1 month, 1 year, etc.  Assuming you mean something between one minute and one month, the answer will depend on how much undo is being produced and how much storage you have available.  I haven't seen anything higher than two weeks.

Oracle automatically manages the undo space and will dynamically decrease the retention below the undo retention setting if space constraints require it.  The caveat to that is when the undo tablespace has guaranteed retention.

The Oracle Database Administrator's Guide covers all this information on one page.  Here are some excerpts:

  Oracle Database automatically tunes the undo retention period based on
  undo tablespace size and system activity. You can optionally specify a
  minimum undo retention period (in seconds) by setting the
  UNDO_RETENTION initialization parameter. The exact impact this
  parameter on undo retention is as follows:

  The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database always tunes the undo retention period for
  the best possible retention, based on system activity and undo
  tablespace size. See "Automatic Tuning of Undo Retention" for more
  information.
  For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by
  UNDO_RETENTION. When space is low, instead of overwriting unexpired
  undo information, the tablespace auto-extends. If the MAXSIZE clause
  is specified for an auto-extending undo tablespace, when the maximum
  size is reached, the database may begin to overwrite unexpired undo
  information. The UNDOTBS1 tablespace that is automatically created by
  DBCA is auto-extending.

  ...

  To guarantee the success of long-running queries or Oracle Flashback
  operations, you can enable retention guarantee. If retention guarantee
  is enabled, the specified minimum undo retention is guaranteed; the
  database never overwrites unexpired undo data even if it means that
  transactions fail due to lack of space in the undo tablespace. If
  retention guarantee is not enabled, the database can overwrite
  unexpired undo when space is low, thus lowering the undo retention for
  the system. This option is disabled by default.


Splitting a table into two tables randomly with 50-50 percent of records in impala

I assume that that you have 3 tables with identical structure, where the first has the data while the two others are empty.
I also assume that the tables or at least table1 has a primary key.

Searching through the Impala SQL documentation, I don't find any window functions, so a ROW_NUMBER() solution is out of the question.

We could use a random function to split the table in 2 (almost equal) halves.:

-- insert half of the rows into table 2
INSERT INTO table2
SELECT *
FROM table1
WHERE rand() < 0.50 ;

-- insert the rest rows into table 3
INSERT INTO table3
SELECT t1.*
FROM table1 AS t1
  LEFT JOIN table2 AS t2
    ON t1.pk = t2.pk
WHERE t1.pk IS NULL ;

After that, we can drop table1.


SQL Server stores Active Directory accounts using a unique identifier generated by Active Directory.  The unique identifier is generated when the object is created in Active Directory, and never changes.
This means you can change things like the user name, first name, last name, and any other Active Directory attribute for the user, without the need to even touch SQL Server or SSMS in any way.
In other words, you can change their account name in AD, and not worry about SQL Server at all.


Intellisense in SSMS 2012 not working for 2008 server

Try patching your server. I attempted to recreate your issue with SSMS 2012 (11.0.3000) by  connecting to four servers: one SQL Server 2008 (10.0.1600) and three SQL Server 2008 SP 1 (10.0.2531). As you can see the only difference between the four servers is one server does not have Service Pack 1  installed. On all of the servers with service pack 1 installed intellisence worked without issue. However, intellisence did not work on the server without service pack 1 installed.
You can download the service pack here.
http://www.microsoft.com/en-us/download/details.aspx?id=20302


I believe you are talking about SPARSE columns.


First, though slightly unrelated to the main question, your MERGE statement is potentially at risk of errors due to a race condition. The problem, in a nutshell, is that it is possible for multiple concurrent threads to conclude that the target row does not exist, resulting in colliding attempts to insert. The root cause is that it is not possible to take a shared or update lock on a row that does not exist. The solution is to add a hint:
MERGE [dbo].[InverterData] WITH (SERIALIZABLE) AS [TARGET]

The serializable isolation level hint ensures the key range where the row would go is locked. You have a unique index to support range locking, so this hint will not have an adverse effect on locking, you will simply gain protection against this potential race condition.
Main Question

Why are the SELECTs blocked by the [InsertOrUpdateInverterData] procedure that is only using MERGE commands?

Under the default locking read committed isolation level, shared (S) locks are taken when reading data, and typically (though not always) released soon after the read is completed. Some shared locks are held to the end of the statement.
A MERGE statement modifies data, so it will acquire S or update (U) locks when locating the data to change, which are converted to exclusive (X) locks just before performing the actual modification. Both U and X locks must be held to the end of the transaction.
This is true under all isolation levels except the 'optimistic' snapshot isolation (SI) not - to be confused with versioning read committed, also known as read committed snapshot isolation (RCSI).
Nothing in your question shows a session waiting for an S lock being blocked by a session holding a U lock. These locks are compatible. Any blocking is almost certainly being caused by blocking on a held X lock. This can be a bit tricky to capture when a large number of short-term locks are being taken, converted, and released in a short time interval.
The open_tran_count: 1 on the InsertOrUpdateInverterData command is worth investigating. Although the command hadn't been running very long, you should check that you don't have a containing transaction (in the application or higher-level stored procedure) that is unnecessarily long. Best practice is to keep transactions as short as possible. This may be nothing, but you should definitely check.
Potential solution
As Kin suggested in a comment, you could look to enable a row-versioning isolation level (RCSI or SI) on this database. RCSI is the most often used, since it typically does not require as many application changes. Once enabled, the default read committed isolation level uses row versions instead of taking S locks for reads, so S-X blocking is reduced or eliminated. Some operations (e.g. foreign key checks) still acquire S locks under RCSI.
Be aware though that row versions consume tempdb space, broadly speaking proportional to the rate of change activity and the length of transactions. You will need to test your implementation thoroughly under load to understand and plan for the impact of RCSI (or SI) in your case.
If you want to localize your usage of versioning, rather than enabling it for the whole workload, SI might still be a better choice. By using SI for the read transactions, you will avoid the contention between readers and writers, at the cost of readers seeing the version of the row before any concurrent modification started (more correctly, the read operation under SI will always see the committed state of the row at the time the SI transaction started). There is little or no benefit to using SI for the writing transactions, because write locks will still be taken, and you'll need to handle any write conflicts. Unless that is what you want :)
Note: Unlike RCSI (which once enabled applies to all transactions running at read committed), SI has to be explicitly requested using SET TRANSACTION ISOLATION SNAPSHOT;.
Subtle behaviours that depend on readers blocking writers (including in trigger code!) make testing essential. See my linked article series and Books Online for details. If you do decide on RCSI, be sure to review Data Modifications under Read Committed Snapshot Isolation in particular.
Finally, you should ensure your instance is patched to SQL Server 2008 Service Pack 4.


Index with ops for `LIKE` and `=` queries

Using an index which is somewhere between the one suggested by @ypercubeᵀᴹ and your index_2 ... which would be:

CREATE INDEX index_1 ON t
    USING btree
          (int1_col,
          lower((varchar_col)::text) varchar_pattern_ops,
          int2_col) ;

... seems to be a good compromise.

You put first the two columns that you use always, and add the third one as the last. You add the varchar_pattern_ops so that queries with LIKE use the index.

From the two always used columns, put the one which is always tested for equality first, the one which is tested with other operators (>=, <=, like) second. If both were tested for equality, you normally would want to have the one which is more discriminant [which means, more or less, that has a bigger number of different values] first. If several columns are equally discriminant, you put first the ones with the datatypes easier/faster to compare (for instance: fixed-size values such as integers would go before varying length values such as texts).

These are the exeuction plans of the two queries you want to perform:

 EXPLAIN ANALYZE
 SELECT
     *
 FROM
     t
 WHERE
     lower(varchar_col) = 'some_string'
     AND int1_col = 5678
     AND int2_col = 1234 ;

 | QUERY PLAN                                                                                |
 | :------------------------------------------------------------------------------------------------------------- |
 | Index Scan using index_1 on t  (cost=0.42..8.44 rows=1 width=40) (actual time=0.007..0.008 rows=1 loops=1)     |
 |   Index Cond: ((int1_col = 5678) AND (lower((varchar_col)::text) = 'some_string'::text) AND (int2_col = 1234)) |
 | Planning time: 0.274 ms                                                                                |
 | Execution time: 0.023 ms                                                                                |

 EXPLAIN ANALYZE
 SELECT * FROM t WHERE lower(varchar_col) LIKE 'some_str%' AND int1_col = 5678 ;

 | QUERY PLAN                                                                                |
 | :------------------------------------------------------------------------------------------------------------------------------------------- |
 | Bitmap Heap Scan on t  (cost=4.44..12.16 rows=1 width=40) (actual time=0.013..0.015 rows=3 loops=1)                                          |
 |   Recheck Cond: (int1_col = 5678)                                                                                |
 |   Filter: (lower((varchar_col)::text) ~~ 'some_str%'::text)                                                                                |
 |   Heap Blocks: exact=1                                                                                |
 |   ->  Bitmap Index Scan on index_1  (cost=0.00..4.44 rows=2 width=0) (actual time=0.005..0.005 rows=3 loops=1)                               |
 |         Index Cond: ((int1_col = 5678) AND (lower((varchar_col)::text) ~>=~ 'some'::text) AND (lower((varchar_col)::text) ~<~ 'somf'::text)) |
 | Planning time: 0.177 ms                                                                                |
 | Execution time: 0.027 ms                                                                                |

Both query plans use your index and are fast (although, my simulated data might not be realistic at all).

dbfiddle here


That page count number is really not a thumb rule, it's more an arbitrary one. I am not able to recall Paul Randal's blog where he has explained that this is a made up number to set up a threshold.

For bigger tables yes you can have the Ola's job run as per the maintenance schedule you have. For smaller ones having page count <1000 should not be a concern unless you see some major performance blips because this operation comes with a extra cost in terms of resource utilization.

Also as far as I can recall rebuilding small indexes might not help in lowering the fragmentation due to allocation of mixed extents, unless you have the trace flag for that enabled (trace flag 1118).


Realistically, there is no online method for table repait.
There are two techniques to repair mydb.mytable
TECHNIQUE #1 : Repair Online
REPAIR TABLE mydb.mytable;

This will perform the table repair with mysql runnng. This will perform a full table lock so no one can access the table.
TECHNIQUE #2 : Repair Offline
To repair a table offline, move the files making up the table to another folder and perform the repair there. For example, to repair mydb.mytable using the folder /var/lib/mysql
REPAIR_OPTION="-r"
DB_NAME=mydb
TABLE_NAME=mytable
FRM=${TABLE_NAME}.frm
MYD=${TABLE_NAME}.MYD
MYI=${TABLE_NAME}.MYI
cd /var/lib/mysql/${DB_NAME}
mv ${FRM} ..
mv ${MYD} ..
mv ${MYI} ..
cd ..
myisamchk -${REPAIR_OPTION} ${MYD}
myisamchk -${REPAIR_OPTION} ${MYI}
mv ${FRM} /var/lib/mysql/${DB_NAME}
mv ${MYD} /var/lib/mysql/${DB_NAME}
mv ${MYI} /var/lib/mysql/${DB_NAME}

If -r does not work, rerun these lines using REPAIR_OPTION="-o"
EPILOGUE
Neither of these techniques will allow REPAIR TABLE operations while the file is live.


Add composite indexes to your tables:

On COLOR_TAGS, index (COLOR_PROJECTID, NoteID)

On note_note, index (isdel, BibID, ID).


Is it possible to have a loop in SQL without creating a stored procedure?

You can put a pl/sql in an anonymous block. Something like this might help:

declare
  v_vertices  your_user_defined_data_type;

  cursor your_cursor_c
  is
    select vertices
    from   your_table
    where  your_column = 'A VALUE';

begin
    open your_cursor_c;
    loop
      fetch your_cursor_c
      into  v_vertices;
      exit when your_cursor_c%notfound;

      ST_NumPoints(v_vertices);
      ST_PointN(v_vertices);

      commit;
    end loop;
end;

Also with 12c there is some inline procedure/function stuff that you can do. Not sure if it is necessary but here is a link: https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1.


A couple of reasons I prefer a single table:

(1) with multiple tables you'll almost always be performing unions and you pay for the core data multiple times

(2) you can easily optimize for a subset of attributes or a combination of devices and attributes with indexes and use sparse columns to minimize wasted space

I wrote a bit about EAV here - it is by no means perfect, but we used it to solve many performance problems (at the cost of others) at a previous job.


Is it okay to have different lower_case_table_names value on master and slave?

According to the MySQL 5.0 Certification Study Guide

Chapter 32 Section 32.3.4, Pages 456,457 describe the Conditions for Binary Portability which bring out the following:

  Binary portability is important if you
  want to take a binary backup that was
  made on one machine and use it on
  another machine that has a different
  architecture. For example, using a
  binary backup is one way to copy
  databases from one MySQL server to
  another.

  For MyISAM, binary portability means
  that you can directly copy the files
  for a MyISAM table from one MySQL
  server to another on a different
  machine and the second server will be
  able to access the table.

  For InnoDB, binary portability means
  that you can directly copy the
  tablespace files from a MySQL server
  on one machine to another server on a
  different machine and the second
  server will be able to access the
  tablespace. By default, all the InnoDB
  tables managed by a server are stored
  together in the tablespace, so
  portability of the tablespace is a
  function of whether all individual
  InnoDB tables are portable. If even
  one table is not portable, neither is
  the tablespace.

  MyISAM tables and InnoDB tablespaces
  are binary portable from one host to
  another if two conditions are met:

  Both machines must use two's-complement integer arithmetic
  Both machines must use IEEE floating-point format or else the
  tables must contain no floating-point
  columns (FLOAT or DOUBLE)

  In practice, those two conditions pose
  little restriction. Two's-complement
  integer arithmetic and IEEE
  floating-point format are the norm on
  modern hardware. A third condition for
  InnoDB binary portability  is that you
  should use lowercase names for tables
  and databases. This is because InnoDB
  stores these names internally (in its
  data dictionary) in lowercase on
  Windows. Using lowercase names allows
  binary portability between Windows and
  Unix, to force the use of lowercase
  names, you can put the following lines
  in an option file:

[mysqld]
lower_case_table_names=1

  If you configure InnoDB to use
  per-table tablespaces, the conditions
  for binary portability are extended to
  include the .ibd files for InnoDB
  tables as well. (The conditions for
  the shared tablespaces still appliy
  because it contains the data
  dictionary that stores information
  about all InnoDB tables.)

  If conditions for binary portability
  are not satisfied, you can copy MyISAM
  or InnoDB tables from one server to
  another by dumping them using some
  text format (for example, with
  mysqldump) and reloading them into the
  destination server.

From this quotation, you can see that InnoDB's data dictionary is very case sensitive to table names. You are basically playing Russian Roulette by mixing tablename case sensitivity between Master and Slave.

I have discussed mixing cases between operating systems in MySQL :

Jan 07, 2013 : Is it possible to use the same directory for 2 MySQL servers?
Sep 28, 2012 : All our MySQL constraints have gone lowercase. What can cause this?
Sep 06, 2011 : Synchronizing local mysql database with remote one with a script or automatically?


Mysql has a built-in partitionning system, why not using it instead of creating several tables then merging them ?

CREATE TABLE product (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB
PARTITION BY LIST(year)(
    PARTITION product_2016 VALUES IN (2016),
    PARTITION product_2017 VALUES IN (2017),
    PARTITION product_2018 VALUES IN (2018)
 );

Mysql will split table into 3 files. It will speed up search in index because each index is smaller. It will enhance concurrent access.

But for performance reasons, it is better to partition table in lots of files something like :

CREATE TABLE product (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB
PARTITION BY KEY (barcode)
PARTITIONS 150 ;

I wonder why you have trouble to insert or update this table, 7 million rows, is not that much, I've got partitionned tables with 270 million rows and I don't have trouble with them.

Do you have concurrent inserts or updates ?

Also as year key is not very discriminant (only 2 values 2016 or 2017), it is better to put bar code first in your primary key like that :

CREATE TABLE product (
    year int not null,
    barcode varchar(8) not null,
    primary key(barcode, year)
) ENGINE=InnoDB
PARTITION BY KEY (barcode)
PARTITIONS 150 ;

Not only it will be quicker for Mysql innodb engine to recreate index when you insert a row but there won't be lock only on that file when an insert is done, so other inserts can be done at the same time on other files.

I see that you are talking about MyISAM engine, please note that MYISAM has traditionnaly locks problem, but if your table is partitionned properly, you won't have such lock problem on MYISAM table, so to consider if you have much more selects than inserts/updates and no transactions.


INDEX REBUILD IMPACT ON sys.dm_db_index_usage_stats

Kendra Little breaks this down in detail on her blog here: Index Usage Stats Bug Fixed in SQL Server 2012 SP2 CU12 / SP3 CU3
I've quoted the table from that blog post below:

SQL Server Version
sys.dm_db_index_usage_stats
Missing Index DMVs

SQL Server 2005 & SQL Server 2008R2
Reset on database offline/restart.
Reset on any index drop/disable/create on that table. Reset on database offline/restart.

SQL Server 2012
Reset on ALTER INDEX REBUILD of that index until SP2+CU12 or SP3+CU3 . Reset on database offline/restart.
Reset on any index drop/disable/create on that table. Reset on database offline/restart. Reset on ALTER INDEX REBUILD of any index on the table.

SQL Server 2014
Reset on ALTER INDEX REBUILD of that index until SP2. Reset on database offline/restart.
Reset on any index drop/disable/create on that table. Reset on database offline/restart. Reset on ALTER INDEX REBUILD of any index on the table.

SQL Server 2016
Reset on database offline/restart.
Reset on any index drop/disable/create on that table. Reset on database offline/restart. Reset on ALTER INDEX REBUILD of any index on the table.

The gist of it is that these stats got reset on index rebuilds in certain builds of SQL Server 2012 and 2014.  You should update to the latest SP / CU, or to SQL Server 2016 or higher, to avoid the problem.


mysql error handling stored procedure doesn't catch error


Not an expert SQL Server guy. However, Since Microsoft haven't made the same mistake as Oracle where the empty string and NULL are considered the same, this should work:

UPDATE TableName
SET My_String = ''
WHERE My_String IS NULL
AND Other_Conditions;


Update different oracle database from different oracle database

In B:

create database link dblink_to_a connect to myuser identified by mypassword using 'tns_a';

merge into destination_table d using source_table@dblink_to_a s on (d.id = s.id) when matched then update set d.myclob = s.myclob;
commit;


MySQL Insert into two tables using new IDs

After your query runs, you'll have a set of copied rows (with configid=41) and an identical set of pasted rows (except for the configid=76 and the auto-created id).

Since, these ids are not known in advance, you'll need another way to identify rows of the config table, e.g. a unique key (besides the auto-incrementing one), so you can match (join) the newly created rows with the old ones.

If, for example, the (configid, optionname) is unique, then the following would work:

INSERT INTO pricing
    ( relid, price, ... )          --- relid and all the other columns,
                                   --- except any autoincrement you may have
SELECT pasted.id, p.price, ....    --- and the same columns here
FROM
    pricing AS p
  JOIN
    tblproductconfigoptionssub AS copied
      ON  copied.id = p.relid
      AND copied.configid = 41
  JOIN
    tblproductconfigoptionssub AS pasted
      ON  pasted.optioname = copied.optioname
      AND pasted.configid = 76 ;


"Network connection was interrupted" error with shared Access database

Has anyone else encountered the same issue?

Yes. The Access Database Engine is designed to work with "real" Windows file sharing and Samba – or at least some versions of Samba – apparently do not implement all of the low-level features of the SMB protocol that are needed for the Access Database Engine to work reliably.

(ref: Corrupt Microsoft Access MDB Causes - Samba)


Postgres: Can I associate index with FK constraint?

I am not 100% certain what you are asking.

Your query shows the association of foreign key constraints to indexes at the target.

There is no association between indexes on the source and foreign key constraints.

You can use this query to find all foreign keys which have no index at the source that supports fast updates and key modifications on the target table:

SELECT conrelid::regclass, conname
FROM pg_constraint AS c
WHERE c.contype = 'f'
  AND NOT EXISTS (SELECT 1 FROM pg_index AS i
                  WHERE i.indrelid = c.conrelid
                    AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1] @> c.conkey);

The strange condition will test if the first index columns are identical to the columns in the foreign key definition.


You will need to respect MySQL syntax, here's something that passes the muster of DB Fiddle  (MySQL 5.7).  See https://www.db-fiddle.com/f/kMfBNBx9WRe65cwWyRmUMn/0

CREATE TABLE nation (
natcode CHAR(3),
natname VARCHAR(45) NULL,
exchange_rate DECIMAL(9,5) NULL,
PRIMARY KEY (natcode));

CREATE TABLE stock (
stckcode CHAR(3),
stckname VARCHAR(45) NULL,
stckprice DECIMAL(9,5) NULL,
stckqty DECIMAL(9,5) NULL,
stckdiv DECIMAL(9,5) NULL,
stckPE DECIMAL(9,5) NULL,
natcode CHAR(3) NOT NULL,
PRIMARY KEY(natcode),
CONSTRAINT natcode
    FOREIGN KEY(natcode)
    REFERENCES nation(natcode)
    ON DELETE RESTRICT
    ON UPDATE NO ACTION);

INSERT INTO nation (natcode,natname,exchange_rate) VALUES
  ('AUS','Australia','0.46'),
  ('IND','India','0.0228'),
  ('UK','United Kingdom','1'),
  ('USA','United States','0.67');


Query execution showing error as login failed

You need to provide password for remote sa account, like this:

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'OPSERVER', @rmtuser = 'SA', @rmtpassword = 'RemoteSaPassword'

Or if you run under local sa account

sp_addlinkedsrvlogin
  @rmtsrvname = 'OPSERVER',
  @locallogin = 'sa',
  @rmtuser = 'sa',
  @rmtpassword = 'RemoteSaPassword'

FOr windows auth:

sp_addlinkedsrvlogin
  @rmtsrvname = 'OPSERVER',
  @locallogin = 'domain\username',
  @rmtuser = 'sa',
  @rmtpassword = 'RemoteSaPassword'

Or

sp_addlinkedsrvlogin
  @rmtsrvname = 'OPSERVER',
  @useself = 'TRUE'


Managing timezones for user action datetimes

Which do you want?  Let's say you and I are in different timezones.

Should my 10:00 should show as "10:00" to you?  If so, use DATETIME.

Should my 10:00 should show as "17:00" to you because we are 7 timezones apart?  If so, use TIMESTAMP.

The only requirement is that our client machines be honest about what timezone we are each in.  (And some config stuff.)

Meanwhile, get rid of the useless id and promote the UNIQUE composite index to be the PK.


How to Add Federated engine after installing Mysql

I have managed to find a solution...
there is a plugin already available at

/usr/lib/mysql/plugin/ha_federated.so

i just logged in to mysql as root and run the following query

mysql>install plugin federated soname 'ha_federated.so';

then i added federated to my.cnf and restarted mysql


I am finding it hard to design a database that can help me fetch menus from different table for example as show in image below

To me this sounds like you are trying to make your database design match the menus in the UI layout. This might work until the UI changes. Then will you change the database structure?

It might be better off to create proper tables based on the data itself, and then design some views that are more accessible by the UI.


INSERT succeeds but all inserted values become NULL

Your insert syntax is wrong, using column=value in the values clause doesn't do what you think it does.

Try:

INTO oracle.PLAYLIST_MUSIC ( TID,
                ID,
                STATUS,
                CREATED_BY,
                CREATED_DATE,
                UPDATED_BY,
                UPDATED_DATE,
                `ORDER`
            )
VALUES(56919,
       115948,
       '1',
       15217,
       NOW(),
       15217,
       NOW(),
       0)

The values must be put in the same order as the column names appear in the insert statement.

It would probably be a good idea to quote the order column, since that's a reserved word.

I'm guessing the nulls come from the fact that the columns aren't bound to anything in the values clause, causing them to be null. And null = any value evaluates to null:

mysql> select null=1 ;
+--------+
| null=1 |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

But that's just a guess about what is actually happening, I'm surprised that parsed at all.


MySQL 5.7 + Ubuntu 14.04 eating up my RAM

MySQL 5.7.13 is OK

I was suffering from this problem. Same as you described: MySQL 5.7.11 with almost empty database and few requests in hour was eating memory up to 450MB.

So I've recently tried new version and it is totally OK. MySQL 5.7.13 instance is running for a week already and it takes only 73MB.

Also I've turned off performance schema in my.cnf:

            [mysqld]
            performance_schema=0


There are several options because DB2 LUW has the older snapshot monitoring and a new monitoring facility integrated into the Workload Manager. Both facilities offer views and table functions to consume the data as well as tools on top.

For an easy way to start I would suggest to use db2top. Connect to a database and use, e.g., "D" to go to the Dynamic SQL screen. It shows Execution Time, CPU Time, Rows Read and Written, and some more information.

As an alternative take a look at the total_cpu_time monitoring element. There are functions and views listed in which that time is reported back.


From your source:

  The normalization factor is the middle part of the formula. The idea
  of normalization is: if a document is shorter than average length then
  weight goes up, if it's average length then weight stays the same, if
  it's longer than average length then weight goes down. We're using a
  pivoted unique normalization factor. For the theory and justification,
  see the paper "Pivoted Document Length Normalization" by Amit Singhal
  and Chris Buckley and Mandar Mitra ACM SIGIR'96, 21-29, 1996:
  http://ir.iit.edu/~dagr/cs529/files/handouts/singhal96pivoted.pdf. The
  word "unique" here means that our measure of document length is based
  on the unique terms in the document. We chose 0.0115 as the pivot
  value, it's PIVOT_VAL in the MySQL source code header file
  myisam/ftdefs.h

So, it's a "best practice" pivot value.


Simple query with date type requires explicit conversion to_date of date

All dates in Oracle have a time component.

I'm pretty sure you want all rows between the 13th and the 15th included. However, when you write:

BETWEEN to_date('13-02-2011', 'DD-MM-YYYY') AND TO_DATE('15-02-2011', 'DD-MM-YYYY')

you select only all date up to the 15-02-2011 at midnight. All rows later that day won't be selected.

What is happening is that when you use TO_DATE on a date, it will be converted to VARCHAR2 and then converted back to a date. The net effect will be a truncation to your date format as shown:

SQL> alter session set nls_date_format='dd/mm/yyyy';

Session altered.

SQL> select to_char(to_date(sysdate), 'dd/mm/yyyy hh24:mi:ss') trunc,
  2         to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') not_trunc
  3    from dual;

TRUNC               NOT_TRUNC
------------------- -------------------
23/10/2012 00:00:00 23/10/2012 15:15:32

So your two clauses are not equivalent at all. The second one selects the whole day feb, 15th whereas the first one only selects the first second of the day.

I suggest you use the following constructions when writing date range:

WHERE dat >= to_date('13-02-2011', 'DD-MM-YYYY')
  AND dat < to_date('16-02-2011', 'DD-MM-YYYY')

Also don't use MON or DAY format in your code since they are dependent upon language. Don't use YY since this can be confusing (ever heard of the Y2k bug?). Both formats are ok to display information to your users of course.


You should understand how SQL Server data storage works. It isn't like you are used to with client applications.

For performance and other reasons, SQL Server reserves blocks of space on the HD (in the MDF file) for the each database's use (including tempDB). That space as seen by the file size is still reserved even if it is not actively being used.

Whenever the database exceeds the allocated space (depending on the file growth settings), it may grow the amount of reserved space (never to give it back). So 30GB is either the initially reserved space or you did some huge operation at some point that caused it to grow really big.

Tips:
You can use sp_spaceUsed to see how much of that file is actually in use.
You can shrink the database back to size (if it isn't all used) with DBCC_SHRINKDATABASE
As tempting as it may be, don't turn on the DB's AutoShrink property.

As for the last modified date, here is what I found:

  The modified dates change when SQL
  Server closes the files (SQL Server
  shut down or the database is
  detached), or when the file is grown
  (either automatically or manually).
  All other times, SQL Server
  essentially bypasses the file system
  when performing the writes, and so the
  modified dates aren't updated.


Finding rows where a column contains [ and ] characters

One option is to use ESCAPE:

DECLARE @x TABLE(s VARCHAR(32)); INSERT @x SELECT 'Hi [Sydney]!'

SELECT s FROM @x WHERE s LIKE '%\[Sydney\]%' ESCAPE '\';

Of course, you'll want to choose a character that is unlikely to appear in the data naturally, or this could mess up other searches.

Another option is to just escape the first character by including it in its own escape sequence, e.g. in this case the end ] is not considered a part of any wildcard sequence because the first one is eliminated:

DECLARE @x TABLE(s VARCHAR(32)); INSERT @x SELECT 'Hi [Sydney]!'

SELECT s FROM @x WHERE s LIKE '%[[]Sydney]%';

One more option is to not use patterns that support wildcards or wildcard sequences. For example, CHARINDEX:

DECLARE @x TABLE(s VARCHAR(32)); INSERT @x SELECT 'Hi [Sydney]!'

SELECT s FROM @x WHERE CHARINDEX('[Sydney]', s) > 0;


MSSQL Master db singleuser in sys.databases but multiuser in sp_helpdb

First,
Run the

ALTER DATABASE master
SET MULTI_USER;

and confirm you receive the error Option ‘MULTI_USER’ cannot be set in database ‘master’.

Check your errorlog for any information that could explain why and when.
Then

Backup the master database
Restore as a different database, example 'master_temp'.
Switch master_temp to MULTI_USER.
Backup master_temp.
Restore master with the backup of master_temp.


Recommendations about deleting large set of rows in MSSQL

I've had to do this a couple of times and this is my preferred method for transforming a very large table into a partitioned one with minimal downtime. It does however, rely on the fact that you are fine with waiting a few days/weeks for it to complete the process (don't panic - you will understand why it takes this long later on):

Create a new PARTITION FUNCTION if you do not have an existing one you can use.
Create a new PARTITION SCHEME if you do not have an existing one you can use.
Create a new PARTITIONED TABLE that will contain all new data.
Add a CHECK CONSTRAINT to the new table to ensure that no data with a date older than X is added.
Add a CHECK CONSTRAINT to the old table to ensure that no data with a date newer than X is added.
RENAME THE EXISTING TABLE to something to identify that it is old/archived data.
CREATE A PARTITIONED VIEW that combines the old and new tables with the correct name.
Allow the existing process of adding & removing data to continue.

All newly inserted data will be added to your newly partitioned table while the deletes will act upon the older table. Over time as the data is cleared out of the older table the deletes will become faster and have less of an impact on your system. Based on your estimate of 75 million rows per day, it will take about 6 weeks to get to the point where all new actions are performed against the partitioned table.
During this time you can develop a PARTITION SWITCHING strategy to use when deleting data from the newly partitioned table.
Once all of the data has been removed from the old table, you can DROP THE VIEW, DROP THE OLD TABLE and then rename your partitioned table correctly.
I have found this approach to be the least disruptive to a production environment because you never actually go back and partition the existing data - if you are happy and are permitted to wait the required amount of time. If it is a matter of urgency, then other approaches will require downtime while you partition the existing table.


Usually surrogate keys are implemented based on dimension tables and external data sources in order to have coherent and valid data records; the ETL process, which in charge of extracting data from different sources, transforming and finally delivering data into the data warehouse, must ensure this condition (i.e., surrogate keys refer to the right record).
Personally, I did perform a data validation once by simply using a comparison between the OLAP(i.e., Data Warehouse) result and OLTP(i.e., DB sources) result for the same simple analysis query in order to see if there are any incoherent information.


Parameter values containing double-quotes are not affected by the QUOTED_IDENTIFIER setting within the proc unless you use dynamic SQL. The setting affects how the T-SQL statements are parsed.

As long as the T-SQL proc code encloses string literals using ISO SQL standard single quotes like your example, you can safely recreate the proc from a session with QUOTED_IDENTIFIER ON to persist the setting as object meta-data. The QUOTED_IDENTIFIER ON setting will be used at run time and allow you to use filtered indexes and other SQL Server features that require the ISO standard setting.

I recommend you ensure both QUOTED_IDENTIFIER and ANSI_NULLS session settings are ON when creating creating objects to avoid surprises later.


Connect to SQL Server 2012 from an Windows XP client

The version of Management Studio you should be using is SQL Server 2012. Sadly - as you've confirmed - this is not supported on Windows XP. I don't think this is a wholly artificial restriction - I think even if you could figure out some way to hack the 2012 tools into installing, you'd have other behavioral issues to deal with.

So, you have a couple of options (and I understand that these may or may not be practical):

Upgrade to a new version of Windows (and if you need XP for compatibility reasons, use XP mode). You won't get a whole lot of sympathy from Microsoft running a 10-year old operating system.
Remote to the SQL Server 2012 machine and use its copy of Management Studio.
Deal with / ignore the exception (though you may be missing other functionality too).


migrate from sql server 2012 to sql server 2008 R2

One way to do this is to use a tool such as Redgate SQL Data Compare. I have had to do this in the past and it has worked well. First you run a schema compare to create an empty database in the 2008 server and then run data compare to pump the data in.

There are other vendors offering similar tools and I believe the new Visual Studio also has something like this. The Redgate offering does give you a two week trial which may be good if you only need it for a one of migration.

Click here for the trial.


Views for XML with attributes using XMlTable

The following worked -

CREATE OR REPLACE VIEW
      person_master_view(empid, empname, empproj, emporg)
    AS SELECT xt.*
         FROM PERSON P,
                    XMLTable ('//Person' passing P.object_value
                                     columns
                                         empid        number(10)   path '@empid',
                                         empname      varchar2(30) path '@empname',
                                         empproj      varchar2(30) path '@empproj',
                                         emporg       varchar2(30) path '@emporg'
                                 ) as xt


How to formulate equality predicate on POINT column in PostgreSQL?

Try as it might, operator type resolution will find no appropriate operator and give up with the error message you reported.

ERROR:  operator does not exist: point = unknown

Adding an explicit type cast (or the point() function achieving the same) to the right argument like you did in your added second attempt will only succeed in making the error message a bit more revealing:

ERROR:  operator does not exist: point = point

The subtle difference may serve as hint: the problem is not a missing cast (though being explicit about data types is never a bad idea). The problem is that there is, in fact, no equality operator = defined for the data type point. The manual:

  Note that the “same as” operator, ~=, represents the usual notion of
  equality for the point, box, polygon, and circle types. Some of these types
  also have an = operator, but = compares for equal areas only. The other scalar
  comparison operators (<= and so on) likewise compare areas for these types.

For the data type point, there is no = at all. See for yourself (tested in Postgres 12):

SELECT *            -- no rows
FROM   pg_operator
WHERE  oprleft = 'point'::regtype
AND    oprname = '=';

The solution is to use the appropriate "same as" operator ~= instead. Works with or without explicit type cast, with or without enclosing parentheses in the string literal, and with the function notation you tested as well - and in this case with either type of string literal as well as with two separate parameters as there are multiple overloaded versions of the point() function:

SELECT a.* FROM indsolv.address a
WHERE a.location ~= point '(93.2321, 21.0321)';

... WHERE a.location ~= point '93.2321, 21.0321';
... WHERE a.location ~= '93.2321, 21.0321'::point;
... WHERE a.location ~= '(93.2321, 21.0321)'::point;
... WHERE a.location ~= cast('93.2321, 21.0321' AS point);
... WHERE a.location ~= cast('(93.2321, 21.0321)' AS point);

... WHERE a.location ~= '(93.2321, 21.0321)';
... WHERE a.location ~= '93.2321, 21.0321';

... WHERE a.location ~= point('93.2321, 21.0321)';
... WHERE a.location ~= point('(93.2321, 21.0321)');
... WHERE a.location ~= point('93.2321', '21.0321');

Related:

ERROR:could not identify an equality operator for type point
Creating custom “equality operator” for PostgreSQL type (point) for DISTINCT calls


Reaching SQL Server AlwaysOn HA listener from the Internet

It's generally considered a really bad idea to make your database accessible from the internet.

Making the database accessible from the public internet is a HUGE security risk. It's easy to port-scan and find publicly accessible databases. Even if you run on a non-standard port, it's relatively trivial to discover the SQL Server instance that is internet-facing. Your database would be available to anyone to exploit. It's likely that you'd be victim of every zero-day exploit, in addition to brute force attacks. Eventually, your data will be stolen or destroyed.

Take a look at shodan.io to see how easy it is to discover databases on the public internet. You will essentially have a big sign saying "HACK ME" hanging on your IP address.

Ideally, you would have a web service that the application would interact with. That public web service would be the only thing that connects to the (non-public, firewalled) database. Doing this would require re-architecting your application to use the web service, rather than a direct client-server database connection. If you can't re-architect the application, then VPN is the only secure solution.


That just looks that way because SSMS fills that in with the local credentials even if you passed it something else via command line.

Behind the scenes, it is using the username you specified in the /user: argument. It would be interesting to know what the actual error message is and if you tried - instead of using a shortcut - from running that command directly in a command line window (and not closing the CMD window while SSMS is still running).

I was just using this feature yesterday so I am 100% certain it works.


Duplicate entry error for unique key that doesn't exist

Your update is the correct way to insert data. Using
INSERT INTO ... SELECT ... requires a source table and is not meant to be used with static values.

Using INSERT INTO ... VALUES ... is for inserting static data, such as CCC, into the table. The two are not interchangable and will cause the issues you've been seeing.

You can check the documentation on INSERT INTO ... SELECT ... here and note that it only talks about using a table source.


Can SQLServer implicitly combine clustered and nonclustered index for queries that hit the NCI with a WHERE and the CI with an ORDERBY?

Each leaf node row in a SQL Server b-tree index uniquely identifies a single row in the table by a unique key. In a non-clustered index, this unique key consists of the declared key columns plus the row locator. The row locator is the clustered index key (including uniqueifier for when the CI key value is not unique). With a heap (no clustered index on the table), the row locator is the physical location of the row (file,page,slot).
The DBCC PAGE output of this demo script shows both GuidColumn and AutoIncInt as index keys even though AutoIncInt was not specified:.
DROP TABLE IF EXISTS dbo.t;
CREATE TABLE dbo.t (
      AutoIncInt int IDENTITY NOT NULL CONSTRAINT pk_t PRIMARY KEY CLUSTERED
    , GuidColumn uniqueidentifier NOT NULL
    , SomeColumn char(5000) NOT NULL
)
CREATE INDEX idx ON t(GuidColumn) INCLUDE (SomeColumn);
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','3')
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','1')
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','2')

DECLARE @dbid int, @fileid int, @pageid int;
SELECT TOP (1)
      @dbid = DB_ID()
    , @fileid = allocated_page_file_id
    , @pageid = allocated_page_page_id
FROM sys.dm_db_database_page_allocations( DB_ID(), OBJECT_ID(N'dbo.t','U'), 2, 1, 'DETAILED')
WHERE
    page_type_DESC = 'INDEX_PAGE'
ORDER BY
      allocated_page_file_id
    , allocated_page_page_id;

DBCC PAGE(@dbid,@fileid,@pageid,3);
GO

Results:
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+
| FileId | PageId | Row | Level |           GuidColumn (key)           | AutoIncInt (key) | SomeColumn |  KeyHashValue  | Row Size |
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+
|      1 |    424 |   0 |     0 | 00000000-0000-0000-0000-000000000001 |                1 |          3 | (63f9557c63d5) |     5038 |
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+

This index architecture has positive performance implications for your query because the AutoIncInt column is not only implicitly included in the non-clustered index as the row locator, it is also part of the unique key and thus ordered. This allows the plan for your query to use an index seek for the GuidColumn WHERE predicate plus a backwards scan for the ORDER BY...DESC clause.
Below is an excerpt from the actual query plan shows a backwards scan of the index. The resultant non-clustered index will be the same regardless of whether AutoIncInt is explicitly specified as a key column.
<IndexScan Ordered="true" ScanDirection="BACKWARD"


How does MySQL store data, can I detach a mysql Database?

1.4GB is a tiny database.

To remove ones that are not of use any more, back them up with mysqldump and DROP DATABASE.

On files:
 - ib_log files are
   redologs
 - ibdata1 is a system
   tablespace.


Postgres: modify existing search_path (preserve current values)

SELECT set_config('search_path', 'fred,'||current_setting('search_path'), false);

The false says it's not a transaction-LOCAL setting.

For the bonus question, you can store the value in a custom setting:

SELECT set_config('tmp.search_path', current_setting('search_path'), false);

From version 9.2 on, you don't even have to define this setting in postgresql.conf.


PostgreSQL database does not exist error

The database was created with double quotes, so the name is now case-sensitive and you are now stuck with the dreaded quoted identifiers. "Owino" is a different name than Owino

GRANT ALL PRIVILEGES ON DATABASE "Owino" to OdooD;

Depending on how you created the user, you might need to use the dreaded double quotes for the user name as well ("OdooD").

I highly recommend to simply never use double quotes in SQL.

See the manual for more details:
https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS


How exactly does sp_msforeachdb work behind the scenes?

First of all don't use sp_msforeachdb it has several known issues.  You are better off using Aaron Bertrand's version here and here.

However it uses a cursor, dynamic SQL and a replace.  You can actually look at the code by using sp_helptext.

EXEC sp_helptext sp_msforeachdb

If you use this code it will fix some of your issues.

EXEC SP_msforeachdb 'SELECT ''?'' AS Database
                 FROM [?].sys.objects
                 WHERE name like ''%aetna%''

The brackets will the specific problem you mentioned.  You will however run into issues if you have a database with [ or ] in it.


Querying an array of strings with a string that represents search terms

I would do something like this,

create a helper function to generate your query terms from the text-string input. You could inline this into the function, but it's ugly and silly and if you need it here then you probably need it elsewhere.
Write a query with NOT word LIKE ANY()

Here is a demo of the helper function,

CREATE OR REPLACE FUNCTION my_stupid_query(q text)
RETURNS text[] AS $$

  SELECT ARRAY(
    SELECT regexp_replace(t, '[^[:alpha:]]', '_', 'g')
    FROM unnest(string_to_array(q, ' ')) AS q(t)
  );

$$ LANGUAGE sql
IMMUTABLE;

Then you can use NOT word LIKE ANY(). This shows what it would look like,

SELECT word
FROM (VALUES (ARRAY['hello world', 'foo', 'bar'])) AS t1(x)
CROSS JOIN LATERAL unnest(x) AS t2(word)
WHERE NOT word LIKE ANY(my_stupid_query('foo hello-world helloXworld'));

 word
------
 bar
(1 row)


What's the difference between "expression <=1." and "expression <= 1"?

1. is a NUMERIC (or DECIMAL) constant, while 1 is an INTEGER constant. In some cases it is useful to specify the data type of a constant explicitly to avoid an unnecessary (or undesirable) implicit type conversion.

Consider, for example

create table t(f1 int);
insert into t values (2);

Then select 1/f1 from t returns 0 (INTEGER), while select 1./f1 from t returns 0.5 (DECIMAL).

One might assume that on the left side of the comparison in your example there is a DECIMAL column, and explicitly specifying a DECIMAL constant could marginally improve performance by avoiding an implicit type cast.


Microsoft SQL Server local report culture is not supported: 3072 is invalide culture identifer

Had the same error.

Turns out that it was my setting in Region -> Formats -> Format. When it was on English (World) or English (Europe), I would get this error. Setting it to English (United States) solves it.

I'm guessing that English (World) or English (Europe) were new formats with Windows 10, and SSMS is not prepared for it.


Positional value issues with PostgreSQL crosstab

It's not clear to me what is the relation between one_y and indicator 35 but assuming that they have a 1 to 1 correspondence to the where part of your first query I can came up with a solution.

Clearly you need the updated two parameter version of crosstab function.
What you are missing is the right category values.

Try this as your second parameter...

select * from  (
            values  (35) , (36) ,(37) , (38) , (39) , (105) , (106) ,(107) , (108) , (109)) a

The final query would be...

select *
          FROM crosstab (
            $$
              select postcode::text, indicator::int, value::float
              from indicator_data
              where
                (indicator = 35 or indicator = 36 or indicator = 37 or indicator = 38 or indicator = 39 or
                indicator = 105 or indicator = 106 or indicator = 107 or indicator = 108 or indicator = 108) and date = '2017-04-01'
              order by 1,2
            $$,
            $$
            select * from  (
            values  (35) , (36) ,(37) , (38) , (39) , (105) , (106) ,(107) , (108) , (109)) a
            $$
         )
          AS ct (
            "postcode" text,
            "one_y" float,
            "two_y" float,
            "three_y" float,
            "four_y" float,
            "five_y" float,
            "one_l" float,
            "two_l" float,
            "three_l" float,
            "four_l" float,
            "five_l" float
            )

Resulting in ...

 postcode | one_y | two_y  | three_y | four_y | five_y | one_l | two_l  | three_l | four_l | five_l
----------+-------+--------+---------+--------+--------+-------+--------+---------+--------+--------
 NW3 3    |  1000 | [null] |    2000 | [null] | [null] |     2 | [null] |       3 | [null] | [null]
(1 row)

Regards


is id and phone number enough to uniquely identify a row in phone numbers

There are probably ways to solve this (e.g. an instead of trigger rather than an explicit constraint, or a clever filtered index if on 2008+), but why?

As an example, you can do this with two filtered indexes in 2008+:

CREATE TABLE #cpn(c INT, pn VARCHAR(32), x VARCHAR(8000));

CREATE UNIQUE INDEX x ON #cpn(c, pn) WHERE x IS NULL;
CREATE UNIQUE INDEX y ON #cpn(c, pn, x) WHERE x IS NOT NULL;

INSERT #cpn(c, pn   ) SELECT 1, '555-5555';        -- OK
INSERT #cpn(c, pn, x) SELECT 1, '555-5555', '345'; -- OK
INSERT #cpn(c, pn, x) SELECT 1, '555-5555', '555'; -- OK
GO
INSERT #cpn(c, pn   ) SELECT 1, '555-5555';        -- fails on index x
GO
INSERT #cpn(c, pn, x) SELECT 1, '555-5555', '345'; -- fails on index y

But I fail to see the point. This seems like unnecessary flexibility. If I'm going to call the office and try to reach Bob at extension 55, why would I be any more likely to reach him at extension 67? Does this seem like a feasible, real-world scenario to anyone?

I suggest you make it a business rule that a user can only have one extension at the same number. This way you can keep your constraint on customer id and phone number, and allow extension to be optional.

Also, assuming you are storing phone number and extensions as strings, you can go the other way and let people enter them together instead of separate. If they want to enter 15 different extensions at the same number, why stop them? I also think it's silly to allow an extension of 0, but this would allow that also.


How many queries per second is my Postgres executing?

Use this query to read total number of transactions executed in all databases:

SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database;

If you want the same counter for just one database, use:

SELECT xact_commit+xact_rollback FROM pg_stat_database WHERE datname = 'mydb';

To calculate TPS (transactions per second), run the query several times and calculate difference over time interval.

There are ready made tools for that, one of them is http://bucardo.org/wiki/Check_postgres

More info: http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW

Update: Konrad corrected my misunderstanding of his question. The goal was to count queries, not transactions.

How to count queries?

Method 1

Use pg_stat_statements contrib.

Method 2

Enable full logging of queries for a representative period of time.

To enable full logging, for PostgreSQL 9.0 - 9.3, change following settings in postgresql.conf

logging_collector = on
log_line_prefix = '%t '
log_rotation_size = 1GB
log_statement = all

If you want to see also query duration, you can set log_min_duration_statement = 0 instead of log_statement = all. This is very useful for query tuning.

Then reload config (restart or HUP) and collect enough log to estimate traffic.

Note: neither method will include queries embedded in user-defined functions.


What are the benefits of using a different tablespace for a new user schema?

If you are planning to drop schemas, having a separate tablespace which can also be dropped will reduce fragmentation in whatever tablespace you would otherwise use (unless you are using uniform extents, but that is not the default).

RMAN recovery options are different as you would be able to do TSPITR on each individual schema.

Fragmentation is not an issue for temp tablespaces because "All extents of temporary tablespaces are of uniform size". Obviously there are no backup/restore, export/import implications for temp. By giving each user their own temp tablespace you'd be imposing a kind of resource limit on them, but there are less wasteful ways of doing that if it is your aim.


Closed loops in database model, does it affect the performance?

I have heard that having loops in the database relational model should be avoided.

To me, a table structure (>1 table) where the foreign keys form circular dependencies (traversing foreign keys starting from one table, it's possible to end up back at the same table) is a red flag indicating a likely problem with the table/system design.

I'm not going to say this is never correct, but I have yet to see an example where this kind of thing was used correctly, or could not be reasonably redesigned to avoid the use of a circular dependency.

(It's the same type of issue in other areas too, as I pointed out here.)

  Table 'Answers' is linked to 'QuestionaireResultsets',but 'QuestionaireResultsets' is also linked to 'Questionnaires', which forms a loop.

Those tables are related, yes, but the relationships probably should be of the form:

Questionnaires 1..* QuestionaireResultsets

QuestionaireResultsets *..* Answers, with a join table in between

These changes result in a structure that does not contain a circular dependency.

Remember that foreign key relationships have a direction. This is very important. Tables can be related such that a schema diagram looks like a circle; it's only a problem when all the segments of the circle point in the same direction.

  I'm wondering if it affects the query plans and SQL Server performance.

If you ask SQL Server to perform a task using recursion instead of using sets or iteration, it's possible that performance could suffer; however, the reverse may be true as well. I'm not sure we can have a meaningful discussion about performance in this case without a specific example.


MongoDB -- Failed global initialization: Failed to open "var/log/mongodb/mongod.log"

You may need to change the permissions for the parents directory /var/log/ as the user running the mongod instance might not have access to it. The below code should do it

sudo chmod 755 /var/log


Create a view and populate 1 column with the extracted day-month from a epoch date from the main table

The answer is, like you already mentioned in the PostgreSQL documentation. An example is in the reply of Postgres: how to convert from unix epoch to date?

select timestamp with time zone 'epoch' + Day_start * interval '1 second'
from   "MainTable";

or simpler (thanks to @a_horse_with_no_name):

select to_timestamp(day_start)
from   "MainTable";

Use the to_char(timestamp, 'DD-MM') to get the day and month. The result to put in your CREATE VIEW would be something like:

select to_char(timestamp with time zone 'epoch' + Day_start * interval '1 second',
               'DD-MM') AS index;
from   "MainTable";

If you want the whole date as DATE type then use:

select (timestamp with time zone 'epoch' + Day_start * interval '1 second')::date AS index;
from   "MainTable";


Is SQL Server Backup single or multi-threaded?

Backup to multiple files is perhaps what your adviser is referring too. You'll often see improvements (although usually modest) if you're backing up to multiple files on a single disk/array, up to the point that you're saturating source and destination IO paths obviously.

BACKUP DATABASE [MyDatabase]
TO
  DISK = N'Z:\backup\MyDatabase_File1.bak'
  , DISK = N'Z:\backup\MyDatabase_File2.bak'
  , DISK = N'Z:\backup\MyDatabase_File3.bak'
  , DISK = N'Z:\backup\MyDatabase_File4.bak'
WITH
    NAME = N'MyDatabase - Full Backup'
    , INIT
    , STATS = 10

Compression is the obvious addition if you're using 2008+. If not, there are gains to be had from experimenting with BUFFERCOUNT, BLOCKSIZE and MAXTRANSFERSIZE option. Paul Randall's article on Advanced Backup & Restore Options is a good place to start.


Transactional Replication Synchronization taking too long to respond

Check the following things please:

Snapshot Agent shows as completed successfully and it has generated its files
Log reader agent is running and delivering

Right click on each service -> view details - that will help you get more info.

To add to the comments, check the subscription details:

Go to each tab and see if there's any error. If all is good, they should all show green, running, with an action list of delivering transactions.


Postgresql: Difference between pg_dump and backup/restore?

pg_dump is the basic official tool of the project to create backups of PostgreSQL databases (or selected objects in it) in the file system.

To restore, you can either use pg_restore or simply psql, depending on the format of the backup.

The "Backup" and "Restore" options of pgAdmin are just fronts to the above mentioned tools.

There is no (official) tool "backup" or "restore" like you seem to be implying. You are probably confusing terms.


SQL 2008 R2 Repair: Setup failed to install Windows PowerShell. Error code is -2145124329

Well, that error code is:

  SUS_E_NOT_APPLICABLE -- install is not needed because no updates are
  applicable

(source)

This happens when you're trying to install a patch for an application and the patch no longer applies to the application.  For example, if the application is version 10 and the patch fixes version 9, the patch will fail with that error.

One possible fix is to uninstall and reinstall SQL Server.  Also, you could check to see if you have any patches or fixes installed already.  I know that Service Pack 1 is already out for SQL Server.  If you have that installed, it could be causing this error (trying to add an old patch to a newer version of PowerShell).


Get table Data from single table but from different paths

Not sure I understand what the problem is, but you can use a derived table like:

select ur.id, c.scopeid
from userroles ur
left join clients c
    on ur.scopeid = c.id

and then join that with the rest of the tables. Something like:

select ...
from (
    select ur.id, c.id as clientid, ...
    from userroles ur
    left join clients c
        on ur.scopeid = c.id
) dt
join applications a
    on dt.id = a.id
    or dt.clientid = a.clientid
join applicationplans ap
    on ap.applicationid = a.id

In general DDL (create table ..., etc) is preferred over images for describing tables.


When the difference is positive, the SUBSTRING(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')),1,5) part returns a positive values, thats without the +.

But CONVERT_TZ expects the offset to contain either + or -, so without it the value is not proper offset and the function returns NULL as specified.

Simply said - results of timediff are not proper timezone offsets.

You might use DATE_ADD instead, it allows for - too.


SQL Insert with soft row limit in stored procedure

DECLARE @count INT = (SELECT 1 FROM Label WHERE CompanyId = @companyId)

This makes little sense. It should be SELECT COUNT(...)

  is it possible that it will insert a row over the limit due to concurrency issue?

Yes. More than just one row. If N concurrent users will reach the count at the same point and count limit -1, then all N will proceed and insert thus you will exceed by N-1.

  Should i use LOCK or TRANSACTION?

It is possible, is very difficult to get right, and will severely impact performance. And will be easily defeated by inserts that do not go through this stored procedure (data always outlives the app), by updates that modify the CompanyId and so on and so forth.

BEGIN TRANSACTION
DECLARE @count INT = (
  SELECT COUNT(*)
    FROM Label WITH (UPDLOCK)
    WHERE CompanyId = @companyId)
IF @count < @limit
BEGIN
    INSERT INTO Label (Label, CompanyId)
    VALUES (@label, @companyId)
END
COMMIT

I would strongly advise against doing something like this. Revisit the requirements, a 'soft limit' is very hard to get right in a relational model.

I recommend you read also Enforcing Complex Constraints with Indexed Views, maybe you find something to inspire you.


Duplicate database and keep it up to date - Replication or SSIS?

I would just backup/restore. This accomplishes the same thing, validates your recovery story, and will easily handle schema/object changes as well - without really putting any undue strain on the existing database (you're already backing it up, right?).  If it were on a different server (or if you ever split them up), I might suggest log shipping as opposed to mirroring or replication, that way you can fiddle with how long between log backups, how long to delay restores, what kind of maintenance window to use, etc. Of course even on a different server a very simple backup/restore will work fine.


Downsampling to (eg) "last result on Sunday night"

You can select only those rows that are less than five minutes before midnight:
... WHERE
      -- less than 5 nimutes before midnight
      TIME '24:00:00' - CAST(time AS time) < INTERVAL '5 minutes'
      -- on a Sunday
      AND extract (dow FROM time) = 0


Use of '@' '.' and 'public operator/keywords in Oracle database?

This diagram is from the Oracle Docs Syntax for Schema Objects and Parts in SQL Statements page:

It shows the general use of the period character (.) for qualification and the @ symbol for referencing dblinks.

The @ symbol is also used when creating dblinks.

Given that the public keyword is also used for creating them, I think it is likely that your external function is doing something with dblinks!


how to view data as history of field value change

This should work on SQL Server 2012 +

   ;with cte as
    (select employeeId,
            Salary AS 'NewSalary',
            lead(salary,1,0)over(partition by employeeid order by salary desc) as 'OldSalary',
            row_number()over(partition by employeeid order by salary desc) as rn
     from Table_2
     )

     select employeeId,
            NewSalary,
            OldSalary
     from cte
     where OldSalary <> 0 or (OldSalary = 0 and rn=1)


Tracking the update or changes in mysql table

This topic is very interesting for me now, so because it Up, some last information:

Replication is good, but what if You need replicate data to different technology Target System (for example Redshift)

I found and test few implementations for change data capture:

1) MariaDB MaxScale CDC - my personal test was unsuccessful (and builded from source and binary distribution return link for missed plugin). Ask a questions, wait for answer.

2) StreamSets - simulate slave, ship changes to Kafka MQ. Tested on real loading, work perfect

3) Yelp

4) Maxwell Daemon - under test now, example with NiFi - NiFI examples

5) Python Library - also simulate a slave for remote server

6) Lapidus - NodeJS implementations

Few other links also founded, some under active maintenance, some look like forgotten.

From the tests, StreamSets look very good, Maxwell also very interesting
MaxScale - sure it work, but may be available only under subscription support.

From "other" side - Talend as primary data integration tools, Redshift, memSQL.


I want to monitor accessNotInMemory of mongodb

The database-level recordStats information was specific to the older MMAP storage engine and removed during the MongoDB 3.0 development cycle as part of the work toward collection-level locking and the new storage engine API.

A more general performance indicator you can monitor is db.serverStatus().extra_info.page_faults. This metric is available in MongoDB 3.2 as well as prior versions (2.2+, at least).

Given there are multiple storage engines available in MongoDB 3.0, there are also more detailed per-engine metrics that might be of interest. For example, WiredTiger is the default storage engine as at MongoDB 3.2, and includes a comprehensive set of metrics in the wiredTiger section of serverStatus output.


How to represent directed graph with multiple parents?

Usually create a nodes table and a relationships table.  Directed graphs are't really hierarchical and they can have loops which makes querying harder.  But if you think of a DAG as being a generalised tree (I.e. a tree that allows multiple parents but is still strictly hierarchical) and a directed graph as being a generalised DAG (i.e. like a DAG but not strictly hierarchical) things get easier.

So for a very simple, PostgreSQL solution we might do something like:

CREATE TABLE node (
    id serial primary key,
    payload jsonb not null
);

CREATE TABLE relationship (
    id serial primary key,
    relationship_type text not null,
    from_node int references node(id) not null,
    to_node int references node(id) not null,
    payload jsonb not null
);

Then you can query something like this:

with recursive dg as (
    select n.id as node_id, null::Int as parent, array[n.id] as path
      from node n
    union all
    select to_node, from_node, path || to_node
      FROM relationship
      JOIN dg on dg.node_id = from_node AND NOT from_node = ANY(path)
)
select * from dg;


Backing up a MySQL server with mixed character sets

I think the safest thing is to specify dumping in utf8mb4.  Hopefully all the characters you have in all the character sets will convert correctly to (and from, when reloading) that charset.


Different Date format in predicate when comparing against a date or datetime

In your first example, you compare a datetime to a string. The format for that string is YYYY-MM-DD, meaning that the login's language will determine what is to be interpreted and month vs day. You seems to be in the UK, I assume a British login. Check out for instance sp_helplanguage you'll see how the "separated" datetime format is interpreted for various languages.

The "separated" format is, btw, not to be confused with the ISO 8601 format, which is YYYY-MM-DDTHH:MM:SS (the letter T is what makes the difference).

When you cast the column to the date type, you no longer compare the string to datetime, you compare it to the more modern date type. This will always interpret the "separated" format as ymd, and is in other words not dependent on the login's language.

If you were using the more modern datetime2, btw, you wouldn't have the problem in the first place, since it is handled as date.

See my datetime article for further elaboration: https://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes .


What's the most efficient UUID column type

A dedicated uuid type is your best bet for PostgreSQL. Hard to say with other DBs - it's not impossible for someone to impliment a uuid type that's stored less efficiently than a simple byte type.

Again in PostgreSQL, bytea would be a reasonable way to store UUIDs if you didn't have the uuid type. For other DBs it depends on how they store binary data.

Where possible I'd strongly avoid using hex-with-dashes. It's way less efficient to compare, sort, and store.

So really, "not (2) or (3)". Ever. Use (4) where supported, (1) otherwise.


Prevent COUNT() Subquery using window functions

SELECT
T1.FIELD1,
T2.FIELD2,
...
NVL(T2.COMMENTS, 0) AS COMMENTS
FROM T1
LEFT OUTER JOIN
(SELECT FOREIGN_KEY, COUNT (FIELD1) AS COMMENTS FROM OUTER_TABLE_NAME GROUP BY FOREIGN_KEY) T2
ON T2.FOREIGN_KEY = T1.PRIMARY_KEY
;


I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

More specifically:

  The user should not be able to connect to the primary replica, only secondary.

In this case, it's not possible to do what you're wanting. You can't use read only routing with this as the first step in read only routing is to connect to the primary to check if the requirements to meet read only routing are correctly used and then get the metadata from the primary to understand where the new connection should take place.

You can, however, use something such as a network load balance appliance to dynamically update a cname or A record (AAAA if IPv6) to always point to a secondary. This would be specific to the load balancing software/hardware you choose to use. You could also write your own with a trivial amount of work.


Multiple Merge Replications on same SQL Server Database Conflicts

Check Subscription expiration interval & put the value the same for the 2 publications.


Often the two terms are used interchangeably, so you may find Microsoft SQL Server or Postgres being referred to as either a DBMS or a database system.

Where a distinction is maintained "database system" usually refers to the overall application or set of applications that constitute a project. So for instance in the case of a business management suite that has a few applications which uses a few on-premises SQL Server databases for online storage, and has a data warehouse in some cloud database for reporting, and utilities for maintaining the warehouse and running reports; the application suite is a "database system" that itself uses one or more instances of one or more "database management systems" as part of its construction.

A "database system" in that respect might use several different database management systems in its design. A single application suite may use OLTP style stores for the main data, OLAP optimised stores for reporting, and NoSQL stores for "soft" supplementary data (and/or perhaps caching).


I see a few issues with your existing code to create the waiters table.

First, the index creation code is incorrect.  The column to be indexed should be inside the parentheses -- INDEX  (manager_id).

Second, you are trying to add an index on a column that is not listed in your waiters table. You need to include the manager_id as a column.

Third, the syntax for the FOREIGN KEY constraint is incorrect. The syntax is from the MySQL docs is:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

So your code should be:

CREATE  TABLE `waiters` (
  `waiter_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `manager_id` INT NOT NULL,
  PRIMARY KEY (`waiter_id`) ,
  INDEX  (`manager_id`) ,
  CONSTRAINT FK_Manager
    FOREIGN KEY (`manager_id`)
    REFERENCES `managers` (manager_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

See SQL Fiddle with Demo


How to observe output from query in Workbench 6.3?

Are there any records in table_A (not a good name by the way)? It should at least give you field names though.

For problems like this, it's always best to go back to the good-ol CLI (Command Line Interface) to make sure that nothing's wrong somewhere.

You could also open a new Query (interactive) window and copy, paste and run your code and check that way.

As @MikeLischke points out, the system wasn't designed for interactive scripting in modelling mode (unsurprising really).

However, when you open up MySQL Workbench, you have to choose the modelling option. You then get a new tab with that functionality. You can also open a new Query tab (File-> New Query tab...). You have to connect to an (already existing) schema to actually run any SQL (logically enough).

In the interests of fairness, I think you could maybe mark Mike's post as the correct answer (and give me a +1 if you think I deserve it! :-) ).


How far should bind variables go in Oracle?

Use a bind variable anywhere that you might actually change the value that is passed in.

It probably doesn't make sense, for example, to use a bind variable in the EXISTS clause in your third example because it seems extremely unlikely that anyone would ever want to pass in a different constant for the EXISTS clause.

In your second example, it probably does make sense to use a bind variable rather than a hard-coded status assuming that the application(s) are likely to want to run this query for different id and status values.  On the other hand, if the code would only ever pass in a status of 2 for this query, it probably doesn't make sense to use a bind variable.  Assuming that status is not evenly distributed, you'd be better off giving the optimizer the information that you're always going to use a status of 2 rather than trying to rely on bind variable peeking to give the optimizer that information.

If the value is never going to change-- i.e. if the status in your second query will always be 2-- you can execute the query as many times as you'd like and it will only appear once in v$sql or v$sqlarea.  In my case, I'll execute the query 10 times

SQL> ed
Wrote file afiedt.buf

  1* select * from v$sql where sql_text like '%jc_bind_test%'
SQL> select /* jc_literal_test */ count(*)
  2    from emp
  3   where ename = 'KING';

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

And from v$sql or v$sqlarea, you'll see only one row for the query that has the hard-coded literal.  That row will show that the query was executed 10 times

SQL> ed
Wrote file afiedt.buf

  1* select sql_text, executions, loaded_versions, parse_calls from v$sqlarea where sql_text like '%jc_literal_test%'
SQL> /

SQL_TEXT                                 EXECUTIONS LOADED_VERSIONS PARSE_CALLS
---------------------------------------- ---------- --------------- -----------
select * from v$sql where sql_text like           1               1           1
'%jc_literal_test%'

select sql_text, executions, loaded_vers          5               1           5
ions, parse_calls from v$sql where sql_t
ext like '%jc_literal_test%'

select sql_text, executions, loaded_vers          1               1           1
ions, parse_calls from v$sqlarea where s
ql_text like '%jc_literal_test%'

select /* jc_literal_test */ count(*)            10               1          10
from emp  where ename = 'KING'


Read concern level of majority error while upgrading Sharded Cluster from 3.2 to 3.4

Problem seems to be that mongodb 3.4 requires that config server is replica set (CSRS) and not old type single node config server. So you need to initialize your config server as replica set and then start it with --replSet  parameter.

Please check here


Select a single row from two distinct columns

Just INNER JOIN on a subquery:

SELECT  t1.[TicketNo],
        t1.[LocationID],
        t1.[FreightPay]
FROM [Tkhist1] t1
INNER JOIN (
                SELECT TicketNo,
                       LocationID,
                       MAX(UniqueID) UniqueID
                FROM Tkhist1
                GROUP BY TicketNo, LocationID
             ) sub
    ON sub.UniqueID = T1.uniqueId


How can I use user defined variables and `USE`?

The particular statement you want to run - USE - is not supported by dynamic SQL. I therefore suspect what you want to do is not currently possible.

If dynamic SQL did support the USE statement, you could have done:

MariaDB 10.1:

SET @sql = CONCAT('USE database_', @num);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;`

MariaDB 10.3:

EXECUTE IMMEDIATE CONCAT('USE database_', @num);

Both result in:

  ERROR 1295 (HY000): This command is not supported in the prepared
  statement protocol yet

The MariaDB knowledge base has a list of permitted statements that you can PREPARE. Effectively, this is a list of statements that can be used in dynamic SQL.

EDIT:

So instead I suppose you're left with options such as:

Generate your statements as text -- un-roll your loop to a long list of statements, dump them to a .sql file and then SOURCE that file.
Use a scripting language such as Python, bash, PHP etcetera. Do the loop in the scripting language. (Do not use prepared statements for the USE statement, obviously.)


What can I do to find out since which release a given DBMS supports a feature, function, etc?

Normally I would just google with some specific keywords. If you know the official website of the given DBMS, that could be even more helpful.

Example: To find out which version or release of Oracle the CTX_DOC.SNIPPET function comes from, just google: oracle new CTX_DOC SNIPPET function site:oracle.com .

Sometimes the last resort is probably the easiest way. You just need to fine-tune your search terms.


This is how DNS works with multiple servers the client will ask the servers in a round-robin fashion. if an entry is removed from one of the DNS servers the client will get Non-existent domain as an answer from that server and will not query the other.

Here you can get an overview but it boils down to this

The DNS Client service queries the DNS servers in the following order:

The DNS Client service sends the name query to the first DNS server on the preferred adapter’s list of DNS servers and waits one second for a response.
If the DNS Client service does not receive a response from the first DNS server within one second, it sends the name query to the first DNS servers on all adapters that are still under consideration and waits two seconds for a response.
If the DNS Client service does not receive a response from any DNS server within two seconds, the DNS Client service sends the query to all DNS servers on all adapters that are still under consideration and waits another two seconds for a response.
If the DNS Client service still does not receive a response from any DNS server, it sends the name query to all DNS servers on all adapters that are still under consideration and waits four seconds for a response.
If it the DNS Client service does not receive a response from any DNS server, the DNS client sends the query to all DNS servers on all adapters that are still under consideration and waits eight seconds for a response.


How do I turn off ALTER DATABASE script output in VS 2012?

I am using Visual Studio 2010 and SQL 2008 database projects, but the idea remains the same.
In your solution go to the project and do the following steps:

right click the project -> Properties
Project Settings (1st tab in this setting)
go to section Catalog Properties file: make sure a settings file is already selected (by default there's a file \Properties\Database.sqlsettings if it wasn't previously changed)
click Edit for the sqlsettings file: there you'll see all the options that are deployed each time to your target database. It should look like the following snap:

If you'd like to NOT deploy any of the project default settings, you should do the following steps:

go to tab Deploy
go to section Deployment configuration file (default it is Properties\Database.sqldeployment)
deselect the option "Deploy database properties". It should look something like the following snap:

That should help you change/remove the current project settings that you're now deploying.


Why does an index scan read more pages than exist in an index?

You should check what code is submitted to the server when you open "Fragmentation" (using SQL Server Profiler)

And you'll see the following code:

select partition_number as PartitionNumber,
       index_type_desc  as IndexType,
       index_depth as Depth,
       avg_fragmentation_in_percent as AverageFragmentation,
       page_count   as Pages,
       avg_page_space_used_in_percent   as AveragePageDensity,
       record_count as Rows,
       ghost_record_count   as GhostRows,
       version_ghost_record_count   as VersionGhostRows,
       min_record_size_in_bytes as MinimumRecordSize,
       max_record_size_in_bytes as MaximumRecordSize,
       avg_record_size_in_bytes as AverageRecordSize,
       forwarded_record_count as ForwardedRecords
from sys.dm_db_index_physical_stats(7, 773577794, 2, NULL, 'SAMPLED')

Note the mode: it's 'SAMPLED'.

The code returns only 1 row that reflects page_count of the leaf level of the index.

Now change the code to use 'DETAILED' and you'll find missing pages from non-leaf levels.

Here is my repro.

I first look at Fragmentation tab where I find that my ix_include_filler index has 20836 pages.

Then I executed the code that I saw in Profiler, and the code as it is uses "SAMPLED", returns 1 row and shows 20836 pages that we see in "Fragmentation" tab.

Then I changed "SAMPLED" to "DETAILED" and clearly see that these 20836 are only from a leaf level. Level N1 has other 36 pages + a root.

If your SSMS launches different code when you open Fragmentation tab, maybe you should update your question with SSMS version and spy the code it sends by yourself.

My SSMS's version is 10.50.6000.34 and my explanation is valid for this version.


'(There's) no such thing as a stupid question' is a popular phrase that has had a long history. It suggests that the quest for knowledge includes failure, and that just because one person may know less than others they should not be afraid to ask rather than pretend they already know. :)

I am using below code to transfer full backups to another location using XCOPY:

similarly you can modify and transfer the file from required path to another server.

 DECLARE @RowCnt INT

 SELECT bs.database_name AS DatabaseName,
 MAX(bms.physical_device_name) AS FullBackupName
 INTO #Backups
 FROM msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bms
 ON bs.media_set_id = bms.media_set_id
 INNER JOIN master.dbo.sysdatabases s
 ON bs.database_name = s.name
 where datepart(yyyy,backup_start_date)=datepart(yyyy,getdate())
 and datepart(dd,backup_start_date)=datepart(dd,getdate())
 and datepart(mm,backup_start_date)=datepart(mm,getdate())
 and bs.type='D'
  GROUP BY bs.database_name

 SET @RowCnt = @@ROWCOUNT

 WHILE @RowCnt <> 0
 BEGIN

  SELECT  TOP 1 @DBName = DatabaseName, @BkpFileName = FullBackupName
  FROM #Backups
  ORDER BY DatabaseName

  SET @RowCnt = @@ROWCOUNT

  IF @RowCnt <> 0
  BEGIN

        SELECT @SQL = 'xcopy ' + @BkpFileName +SPACE(1)+ '\\your\path\tobackup\'

     EXEC master.dbo.xp_cmdshell @SQL

    --print @sql

        DELETE FROM #Backups
        WHERE DatabaseName = @DBName

   END

   END

  DROP TABLE #Backups

 SET NOCOUNT OFF

Ps note: You have to make sure XP_Cmdshell is enabled for above to work!


A confusing thing said in the PostgreSQL manual:

I think the technical term for this sort of thing is "Covering your Behind".

All it's saying is that upgrades will work in the vast majority of cases but acknowledges that there's an outside chance that somebody, somewhere has got some convoluted set-up that means an extra step or two might be needed.  If the PostgreSQL team think that's a possibility for any given upgrade, then they'll tell us so and we can work out whether or not the problem case(s) apply to us and we can do the extra work, if necessary.

I would never contemplate completely automating database upgrades.
Databases are complex Beasties and can be "upset" by any number of things.  Having them "upset" in the middle of an upgrade is a really Bad Place to be.

Of course, you want to script up as much as you can, but you have to accept that you cannot cater for every possible eventuality.  You should always have somebody around during the upgrade, just in case.


MySQL Selecting and deleting whole entries

MySQL's DELETE syntax requires the table names involved in the DELETE.

However, I don't think the SELECT query is working as you want it to, either.

You have not established any connection between Songlist and Categorylist. So, it's going to give you a cross join - every row in Songlist wit the specified Artist and Title, matched with every row in Categorylist where CategoryID is not 12. If you had two matching rows in Songlist, and 11 matching rows in Categorylist, your result would be 22 rows.

Assuming you have a CategoryID in Songlist, you'd presumably want rows where the Songlist.CategroyID is not 12.

Try this:

Select Songlist.ID,Artist,Title,Filename,CategoryList.CategoryID
  from Songlist
         LEFT OUTER JOIN Categorylist ON (Songlist.CategoryID = Categorylist.CategoryID)
 where Artist = "Lady Gaga"
   AND Title = "Bad Romance"
   AND (   Categorylist.CategoryID IS NULL
        OR Songlist.CategoryID <> 12
       )
;

This should find all records with your artist and title, where either there's no record in Categorylist with the CategoryID in Songlist, or where the CategoryID is something other than 12.

Then, the delete statement to delete those records from Songlist would be:

DELETE Songlist
  FROM Songlist
         LEFT OUTER JOIN Categorylist ON (Songlist.CategoryID = Categorylist.CategoryID)
 where Artist = "Lady Gaga"
   AND Title = "Bad Romance"
   AND (   Categorylist.CategoryID IS NULL
        OR Songlist.CategoryID <> 12
       )
;

Presumably, leaving only any rows with that artist and title where the CategoryID is 12.

NOTE: Code is, obviously, untested.


Select rows with multiple AND conditions that override eachother

Depending on what you need

SELECT book_id
FROM books
GROUP BY book_id
-- Either 'Drama' or 'Thriller', at least one
HAVING SUM( attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller') )
   AND SUM( attribute_id = 36 AND attribute_value = 'Author2' )

or

SELECT book_id
FROM books
GROUP BY book_id
-- Both 'Drama' and 'Thriller'
HAVING SUM( attribute_id = 35 AND attribute_value = 'Drama' )
   AND SUM( attribute_id = 35 AND attribute_value = 'Thriller' )
   AND SUM( attribute_id = 36 AND attribute_value = 'Author2' )

Also you may add

WHERE ( attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller') )
   OR ( attribute_id = 36 AND attribute_value = 'Author2' )

in both (this is not misprint!) queries. This will decrease the amount of records grouped using index if exists, but will add filesort instead. If the index not exists this conditions add is preferred.


Postgres authentication fails: log refers to "line 90" in pg_hba.conf that shouldn't exist

User jjanes' comment helped me find the real hba-file. It's actually here:

/etc/postgresql/12/db_test/pg_hba.conf

(and not in the /var/lib/postgresql/12/... Don't remember why I had expected it there. As I'm new to all this, the branches of a linux directory tree are misterious to me.)


Replica identity value on PostgreSQL read-only replica

The query will return the same on both servers, because streaming replication keeps them physically identical.
"Replica identity" is a concept from logical replication, which is something quite different.


if you add a second disk i suggest to put data on to different tablespaces. a tablespace offers additional storage and is VERY easy to use:

mkdir /whatever

then in SQL:

CREATE TABLESPACE myspace LOCATION '/whatever';
ALTER DATABASE x SET TABLESPACE myspace;

of course you can also do this for single tables only.
and yes, alternatively you can move the entire data dir and tell the system where the new location is.


How to convert following mysql query to oracle?

I found the query:

SELECT TO_CHAR(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(ceil( TIME/ (20160  * 1000)) * 20160 )/60/60/24 , 'yyyy-mm-dd HH24:MI') "Time",
  SUM(CreatedInstanceCount)     AS "CreatedInstanceCount",
  SUM(InitializedInstanceCount) AS "InitializedInstanceCount",
  SUM(ActiveInstanceCount)      AS "ActiveInstanceCount",
  SUM(TerminatedInstanceCount)  AS "TerminatedInstanceCount"
FROM MEMBER_COUNT
WHERE ApplicationId='wso2is-500-application'
AND to_date('1970-01-01 00','yyyy-mm-dd HH24:MI')              +(ceil( TIME/ (20160  * 1000)) * 20160 )/60/60/24 BETWEEN sysdate-7 AND SYSDATE
GROUP BY TO_CHAR(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(ceil( TIME/ (20160  * 1000)) * 20160 )/60/60/24 , 'yyyy-mm-dd HH24:MI'),
  ApplicationId
ORDER BY TO_CHAR(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(ceil( TIME/ (20160  * 1000)) * 20160 )/60/60/24 , 'yyyy-mm-dd HH24:MI') ;


Split datetime records that fall between 10pm to 7am into new record

Try using a recursive Common Table Expression (CTE) to generate a list of hours between the minimum and maximum date/times. Then you can use DENSE_RANK to group the records.

Example:

WITH
source_data
AS
(
    SELECT tbl.* FROM (VALUES
      ( 1, '10-Aug-2018 21:00', '10-Aug-2018 22:00')
    , ( 2, '10-Aug-2018 20:00', '11-Aug-2018 07:00')
    , ( 3, '11-Aug-2018 07:00', '11-Aug-2018 09:00')
    , ( 4, '10-Aug-2018 20:00', '11-Aug-2018 09:00')
    ) tbl ([EmployeeId], [InTime], [OutTime])
)
,
source_data_dates
AS
(
    SELECT [InTimeMin] = MIN([InTime]), [OutTimeMax] = MAX([OutTime]) FROM source_data
)
,
source_data_times([n], [InTime])
AS
(
    SELECT 1, CAST([InTimeMin] AS DATETIME) FROM source_data_dates
    UNION ALL
    SELECT n + 1, [InTimeMin] = CAST(DATEADD(hh, n, [InTimeMin]) AS DATETIME)  FROM source_data_times, source_data_dates WHERE n <= DATEDIFF(hh, [InTimeMin], [OutTimeMax])
)
,
source_data_times_type
AS
(
    SELECT
          [InTime] = [InTime]
        , [OutTime] = DATEADD(hh, 1, [InTime])
        , [Type] =
            CASE
                WHEN CAST([InTime] AS TIME) >= '22:00:00' OR CAST([InTime] AS TIME) < '07:00:00'
                THEN 'night'
                ELSE 'day'
            END
    FROM
        source_data_times
)
,
source_data_times_type_rank
AS
(
    SELECT
          sd.[EmployeeId]
        , tt.[InTime]
        , tt.[OutTime]
        , tt.[Type]
        , [GroupRank] = DENSE_RANK() OVER (PARTITION BY sd.[EmployeeId] ORDER BY tt.[Type], CAST(tt.[InTime] AS DATE))
    FROM
        source_data AS sd
        INNER JOIN source_data_times_type AS tt ON tt.[InTime] >= sd.[InTime] AND tt.[InTime] < sd.[OutTime]
)
SELECT
      [EmployeeId]
    , [InTime] = MIN([InTime])
    , [OutTime] = MAX([OutTime])
    , [Type]
FROM
    source_data_times_type_rank
GROUP BY
      [EmployeeId]
    , [Type]
    , [GroupRank]
ORDER BY
      [EmployeeId]
    , MIN([InTime])

Results: