Union of results of all database query to also include database name

Just add the DBName to your first query:

DECLARE @sql varchar(max);

SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') +
        'SELECT '''+name+''' AS DBName, count(*) as ''Number of Sections'' FROM ' + QuoteName(name) + '.dbo.section' + CHAR(13)
FROM   sys.databases
where database_id > 4;

--PRINT @sql
EXEC (@sql);

Generally you want to avoid sp_msforeachdb.  It has some well known bugs.

https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb

Edit: If you want to run this on multiple servers you could write a quick SSIS package that loops through an instance table and collects the data to a central location.  I did a walkthrough on the following 3 posts:

Using a fixed list and a for each loop that changes a connection manager with expressions.
Using the package to store data to a central location.
Touching up the package and expanding it to use an instance table to store the list of servers.

Combining all of that into this answer would be a bit long but here are the basics:

Create an instance table on one of your instances.
Create a table to store the data you want to collect (probably the same instance).
In an SSIS package create a SQL Task that queries the instance table and stores the results in an object variable.
Create a for each loop that loops through the variable.
In the for each loop change a variable to be equal to the current instance name from the object variable.
Create a connection manager that uses an expression to change the server name property to the contents of the instance variable.
In the for each loop create a data flow

The data flow source uses the connection manager with the expression.
Put the query that collects your data in the source.
Set the destination to the table you created in step 2.


In the meantime I just figured this out, total brain burp. I had indicated what I thought was a fill factor of 90 in the rebuild job, but it's worded as "change free space percentage to" so by using a value of 90 in there, I was actually using a fill factor of 10!! DOH. No wonder it grew 10x as big. I'm going to rebuild with the correct fill factor then shrink. Thanks to everyone for the input though.


Create your statement as follows and check your data format in your table and ensure that you match the time as well (for example, |2011-01-23 14:44:36|2011-01-23 15:44:44|20140512|
is the my time format in my DDL)

load from myexport.del of del modified by coldel| delprioritychar dateformat=\"YYYY-MM-DD\" timestampformat=\"YYYY-MM-DD HH:MM:SS TT\"  insert into mytable

Good luck


How to change disk for "There is insufficient free space on disk volume"?

Use RESTORE ... WITH MOVE instead of pointing and clicking in the GUI.

First, run RESTORE FILELISTONLY FROM DISK = 'D:\path\file.bak';

You will get output, like:

LogicalName    PhysicalName      ...
-----------    ---------------   ---------
SomeName1      E:\whatever\...
SomeName2      E:\whatever\...

For each LogicalName, add a MOVE clause.

RESTORE DATABASE [Configurations_6.2.2.25] -- terrible name, btw, for multiple reasons
  FROM DISK = 'D:\path\file.bak'
  WITH REPLACE, RECOVERY,
    MOVE 'SomeName1' TO 'D:\wherever\file.mdf',
    MOVE 'SomeName2' TO 'D:\wherever\file.ldf';


Best practice for a DBA to login to a database

Log in as sys, create an account for yourself and grant that account just the privileges you require.  Give yourself a reasonable password.  "Password" is not a good password.

Here are the top ten most used passwords and even if it is your intention that only you log on as DBA these are still not good passwords:

password
123456
12345678
1234
qwerty
12345
dragon
pussy
baseball
football

Here is a script I have used on Oracle 9i and 11g that grants more than I've ever needed.  Further pruning of the permissions on a production system would be a good exercise in security.

Edit @ miracle173  asks what is the difference between the script below and just granting DBA privilege?
The most important difference is that by granting individual privileges you can remove what is unnecessary. If you grant a DBA role then you cannot pick and choose unless you edit the role which is not advisable.  The requirements change from when you are developing a database where you could need everything to a database that has been deployed to production where you want to have the least privileges necessary. Best practice is exactly what Leigh Riffel answered: do what you need to do with the least privileges.

Edit @miracle173 correctly points out that SYSTEM tablespace is not recommended for a user. I have changed it to USERS in the script and on my primary development database.

Edit @miracle173 also makes some good points on the distinction in privileges between a DBA who maintains and a developer.  I do DBA and application development for the databases I work on so you could break down the script according to what you are doing. Practices and standards vary by industry, organization and habit so what you need to work with can change from instance to instance.  It's common in my shop for the development database to have more privileges for admins and developers than production.

CREATE USER ADMIN
  IDENTIFIED BY <choose a good password>
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

A DBA might need these depending on what Oracle options are in use:

  GRANT ADMINISTER DATABASE TRIGGER TO ADMIN;
  ALTER USER ADMIN DEFAULT ROLE ALL;
  GRANT ALTER ANY CLUSTER TO ADMIN;
  GRANT ALTER ANY DIMENSION TO ADMIN;
  GRANT ALTER ANY INDEX TO ADMIN;
  GRANT ALTER ANY INDEXTYPE TO ADMIN;
  GRANT ALTER ANY LIBRARY TO ADMIN;
  GRANT ALTER ANY MATERIALIZED VIEW TO ADMIN;
  GRANT ALTER ANY OUTLINE TO ADMIN;
  GRANT ALTER ANY PROCEDURE TO ADMIN;
  GRANT ALTER ANY ROLE TO ADMIN;
  GRANT ALTER ANY SEQUENCE TO ADMIN;
  GRANT ALTER ANY TABLE TO ADMIN WITH ADMIN OPTION;
  GRANT ALTER ANY TRIGGER TO ADMIN;
  GRANT ALTER ANY TYPE TO ADMIN;
  GRANT ALTER DATABASE TO ADMIN;
  GRANT ALTER PROFILE TO ADMIN;
  GRANT ALTER RESOURCE COST TO ADMIN;
  GRANT ALTER ROLLBACK SEGMENT TO ADMIN;
  GRANT ALTER SESSION TO ADMIN;
  GRANT ALTER SYSTEM TO ADMIN;
  GRANT ALTER TABLESPACE TO ADMIN;
  GRANT ALTER USER TO ADMIN;
  GRANT ANALYZE ANY TO ADMIN;
  GRANT AUDIT ANY TO ADMIN;
  GRANT AUDIT SYSTEM TO ADMIN;
  GRANT AUTHENTICATEDUSER TO ADMIN WITH ADMIN OPTION;
  GRANT BACKUP ANY TABLE TO ADMIN;
  GRANT BECOME USER TO ADMIN;
  GRANT COMMENT ANY TABLE TO ADMIN;
  GRANT CREATE ANY CLUSTER TO ADMIN;
  GRANT CREATE ANY CONTEXT TO ADMIN;
  GRANT CREATE ANY DIMENSION TO ADMIN;
  GRANT CREATE ANY DIRECTORY TO ADMIN;
  GRANT CREATE ANY INDEX TO ADMIN;
  GRANT CREATE ANY INDEXTYPE TO ADMIN;
  GRANT CREATE ANY LIBRARY TO ADMIN;
  GRANT CREATE ANY MATERIALIZED VIEW TO ADMIN;
  GRANT CREATE ANY OPERATOR TO ADMIN;
  GRANT CREATE ANY OUTLINE TO ADMIN;
  GRANT CREATE ANY PROCEDURE TO ADMIN;
  GRANT CREATE ANY SEQUENCE TO ADMIN;
  GRANT CREATE ANY SYNONYM TO ADMIN;
  GRANT CREATE ANY TABLE TO ADMIN;
  GRANT CREATE ANY TRIGGER TO ADMIN;
  GRANT CREATE ANY TYPE TO ADMIN;
  GRANT CREATE ANY VIEW TO ADMIN;
  GRANT CREATE CLUSTER TO ADMIN;
  GRANT CREATE DATABASE LINK TO ADMIN;
  GRANT CREATE DIMENSION TO ADMIN;
  GRANT CREATE INDEXTYPE TO ADMIN;
  GRANT CREATE LIBRARY TO ADMIN;
  GRANT CREATE MATERIALIZED VIEW TO ADMIN;
  GRANT CREATE OPERATOR TO ADMIN;
  GRANT CREATE PROCEDURE TO ADMIN;
  GRANT CREATE PROFILE TO ADMIN;
  GRANT CREATE PUBLIC DATABASE LINK TO ADMIN;
  GRANT CREATE PUBLIC SYNONYM TO ADMIN;
  GRANT CREATE ROLE TO ADMIN;
  GRANT CREATE ROLLBACK SEGMENT TO ADMIN;
  GRANT CREATE SEQUENCE TO ADMIN;
  GRANT CREATE SESSION TO ADMIN;
  GRANT CREATE SYNONYM TO ADMIN;
  GRANT CREATE TABLE TO ADMIN;
  GRANT CREATE TABLESPACE TO ADMIN;
  GRANT CREATE TRIGGER TO ADMIN;
  GRANT CREATE TYPE TO ADMIN;
  GRANT CREATE USER TO ADMIN;
  GRANT CREATE VIEW TO ADMIN;
  GRANT DBA TO ADMIN WITH ADMIN OPTION;
  GRANT DEBUG ANY PROCEDURE TO ADMIN;
  GRANT DEBUG CONNECT SESSION TO ADMIN;
  GRANT DELETE ANY TABLE TO ADMIN;
  GRANT DELETE, SELECT ON SYSTEM.MVIEW_FILTERINSTANCE TO ADMIN WITH GRANT OPTION;
  GRANT DELETE_CATALOG_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT DROP ANY CLUSTER TO ADMIN;
  GRANT DROP ANY CONTEXT TO ADMIN;
  GRANT DROP ANY DIMENSION TO ADMIN;
  GRANT DROP ANY DIRECTORY TO ADMIN;
  GRANT DROP ANY INDEX TO ADMIN;
  GRANT DROP ANY INDEXTYPE TO ADMIN;
  GRANT DROP ANY LIBRARY TO ADMIN;
  GRANT DROP ANY MATERIALIZED VIEW TO ADMIN;
  GRANT DROP ANY OPERATOR TO ADMIN;
  GRANT DROP ANY OUTLINE TO ADMIN;
  GRANT DROP ANY PROCEDURE TO ADMIN;
  GRANT DROP ANY ROLE TO ADMIN;
  GRANT DROP ANY SEQUENCE TO ADMIN;
  GRANT DROP ANY SYNONYM TO ADMIN;
  GRANT DROP ANY TABLE TO ADMIN;
  GRANT DROP ANY TRIGGER TO ADMIN;
  GRANT DROP ANY TYPE TO ADMIN;
  GRANT DROP ANY VIEW TO ADMIN;
  GRANT DROP PROFILE TO ADMIN;
  GRANT DROP PUBLIC DATABASE LINK TO ADMIN;
  GRANT DROP PUBLIC SYNONYM TO ADMIN;
  GRANT DROP ROLLBACK SEGMENT TO ADMIN;
  GRANT DROP TABLESPACE TO ADMIN;
  GRANT DROP USER TO ADMIN;
  GRANT EXECUTE ANY INDEXTYPE TO ADMIN;
  GRANT EXECUTE ANY LIBRARY TO ADMIN;
  GRANT EXECUTE ANY OPERATOR TO ADMIN;
  GRANT EXECUTE ANY PROCEDURE TO ADMIN;
  GRANT EXECUTE ANY TYPE TO ADMIN;
  GRANT EXECUTE_CATALOG_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT EXP_FULL_DATABASE TO ADMIN WITH ADMIN OPTION;
  GRANT FLASHBACK ANY TABLE TO ADMIN;
  GRANT FORCE ANY TRANSACTION TO ADMIN;
  GRANT FORCE TRANSACTION TO ADMIN;
  GRANT GATHER_SYSTEM_STATISTICS TO ADMIN WITH ADMIN OPTION;
  GRANT GLOBAL QUERY REWRITE TO ADMIN;
  GRANT GRANT ANY OBJECT PRIVILEGE TO ADMIN;
  GRANT GRANT ANY PRIVILEGE TO ADMIN;
  GRANT GRANT ANY ROLE TO ADMIN;
  GRANT HS_ADMIN_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT IMP_FULL_DATABASE TO ADMIN WITH ADMIN OPTION;
  GRANT INSERT ANY TABLE TO ADMIN;
  GRANT LOCK ANY TABLE TO ADMIN;
  GRANT LOGSTDBY_ADMINISTRATOR TO ADMIN WITH ADMIN OPTION;
  GRANT MANAGE TABLESPACE TO ADMIN;
  GRANT OEM_MONITOR TO ADMIN WITH ADMIN OPTION;
  GRANT OLAP_USER TO ADMIN WITH ADMIN OPTION;
  GRANT ON COMMIT REFRESH TO ADMIN;
  GRANT QUERY REWRITE TO ADMIN;
  GRANT QUEUE_USER_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT RECOVERY_CATALOG_OWNER TO ADMIN WITH ADMIN OPTION;
  GRANT RESOURCE TO ADMIN WITH ADMIN OPTION;
  GRANT RESTRICTED SESSION TO ADMIN;
  GRANT RESUMABLE TO ADMIN;
  GRANT SELECT ANY DICTIONARY TO ADMIN;
  GRANT SELECT ANY SEQUENCE TO ADMIN;
  GRANT SELECT ANY TABLE TO ADMIN WITH ADMIN OPTION;
  GRANT SELECT ON SYSTEM.DEF$_AQCALL TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.DEF$_CALLDEST TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.DEF$_DESTINATION TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.DEF$_ERROR TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.DEF$_LOB TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.REPCAT$_REPPROP TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.REPCAT$_REPSCHEMA TO ADMIN WITH GRANT OPTION;
  GRANT SELECT_CATALOG_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT UNDER ANY TABLE TO ADMIN;
  GRANT UNDER ANY TYPE TO ADMIN;
  GRANT UNDER ANY VIEW TO ADMIN;
  GRANT UNLIMITED TABLESPACE TO ADMIN WITH ADMIN OPTION;
  GRANT UPDATE ANY TABLE TO ADMIN;
 BEGIN
 SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE
  (GRANTEE_NAME   => 'ADMIN',
   PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
   ADMIN_OPTION   => FALSE);
END;
/

A developer might need these privileges depending on what you are working with

 GRANT OLAP_DBA TO ADMIN WITH ADMIN OPTION;
  GRANT AUTHENTICATEDUSER TO ADMIN WITH ADMIN OPTION;
  GRANT RESOURCE TO ADMIN WITH ADMIN OPTION;
  //java privileges
  GRANT EJBCLIENT TO ADMIN WITH ADMIN OPTION;
  GRANT JAVAUSERPRIV TO ADMIN WITH ADMIN OPTION;
  GRANT JAVA_DEPLOY TO ADMIN WITH ADMIN OPTION;
  GRANT JAVADEBUGPRIV TO ADMIN WITH ADMIN OPTION;
  GRANT JAVAIDPRIV TO ADMIN WITH ADMIN OPTION;

      -- 4 Java Privileges for ADMIN
    DECLARE
     KEYNUM NUMBER;
    BEGIN
      SYS.DBMS_JAVA.GRANT_PERMISSION(
         grantee           => 'ADMIN'
        ,permission_type   => 'SYS:java.lang.RuntimePermission'
        ,permission_name   => 'createClassLoader'
        ,permission_action => ''
        ,key               => KEYNUM
        );
    END;
    /
    DECLARE
     KEYNUM NUMBER;
    BEGIN
      SYS.DBMS_JAVA.GRANT_PERMISSION(
         grantee           => 'ADMIN'
        ,permission_type   => 'SYS:java.io.FilePermission'
        ,permission_name   => '<<ALL FILES>>'
        ,permission_action => 'execute'
        ,key               => KEYNUM
        );
    END;
    /
    DECLARE
     KEYNUM NUMBER;
    BEGIN
      SYS.DBMS_JAVA.GRANT_PERMISSION(
         grantee           => 'ADMIN'
        ,permission_type   => 'SYS:java.lang.RuntimePermission'
        ,permission_name   => 'writeFileDescriptor'
        ,permission_action => '*'
        ,key               => KEYNUM
        );
    END;
    /
    DECLARE
     KEYNUM NUMBER;
    BEGIN
      SYS.DBMS_JAVA.GRANT_PERMISSION(
         grantee           => 'ADMIN'
        ,permission_type   => 'SYS:java.lang.RuntimePermission'
        ,permission_name   => 'readFileDescriptor'
        ,permission_action => '*'
        ,key               => KEYNUM
        );
    END;
    /
  --advanced queue
  GRANT AQ_ADMINISTRATOR_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT AQ_USER_ROLE TO ADMIN WITH ADMIN OPTION;
      BEGIN
    SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
      PRIVILEGE    => 'MANAGE_ANY',
      GRANTEE      => 'ADMIN',
      ADMIN_OPTION => TRUE);
    END;
    /
      BEGIN
    SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
      PRIVILEGE    => 'ENQUEUE_ANY',
      GRANTEE      => 'ADMIN',
      ADMIN_OPTION => TRUE);
    END;
    /
      BEGIN
    SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
      PRIVILEGE    => 'DEQUEUE_ANY',
      GRANTEE      => 'ADMIN',
      ADMIN_OPTION => TRUE);
    END;
    /
  //moving data in and out
  GRANT EXP_FULL_DATABASE TO ADMIN WITH ADMIN OPTION;
  GRANT IMP_FULL_DATABASE TO ADMIN WITH ADMIN OPTION;


How to achieve many to many relationship in this database design?

Your design looks OK to me. The following query will give the trips from a particular users wish list:

SELECT user_name, trip_name

FROM user u
JOIN wishlist w ON u.user_id = w.user_id
JOIN trip t ON w.trip_id = t.trip_id


What accounts for a large single index size (other than bloat?)

There's almost no deletion taking place on the foos table, so the bloat is non-existent.

You don't need deletions to get bloat.  updates will do so as well.  A freshly made index on 10,000,000 ints gives me a size of 214MB, so you do have bloat (or are using weird hardware, perhaps).  You can't detect bloat just by thinking about to.  Rebuild the index and see if it gets smaller, or use pgstattuple.  Of course having a tightly packed index is not very efficient, as then nearly every operation needs to do page splits.

Your mental model of an index does not match how PostgreSQL does things.

An index does not point to a primary key value, it points directly into the table.  This is called a "tid", and generally takes 6 bytes.

The data value itself is stored to 8 byte alignment, so even if your data is an int4, it still takes 8 bytes of disk space.  Due to this alignment, You could build an index on two int4 columns, and it will still take the same size as one int4 column.

PostgreSQL is always prepared to handle NULL values on each row, as well as variable length data, even if the definition of the table/index precludes such things.  So each index tuple has a header which tells us whether that row has any NULL or variable length data, as well as the overall length of that tuple.  This takes up some space.

PostgreSQL uses page-organized storage, not just a huge malloc chunk.  Each page contains some more overhead, both per-block and per-entry within the block (see typedef struct ItemIdData).

So you get 8 bytes for the data itself (bar_id, aligned), 8 bytes of the tuple header which includes the tid pointer, and 4 bytes for the line pointer within each block, that comes up to 20 bytes.  With some overhead of non-leaf pages plus per-block overhead and some space within blocks which is not usable due to alignment or because they are at the ragged end of the data, I get an actual value of 22.4 bytes.  The other 4.5 fold would be bloat (although some of the "bloat" may be useful free space, unless your index is going to be absolutely static from now on)


Store large compressed documents in Postgresql

Postgres compresses automatically large objects chunks, as well as bytea through the TOAST mechanism.

But most PDFs are already compressed. As an example I have 1000 dedup-ed pdfs in my mailbox stored as large objets totalling 281MB, and the compression ratio brought by postgres on these is only 11%.

For large objects you may query the compressed size, as a superuser, with

SELECT sum(pg_column_size(data)-4) FROM pg_largeobject WHERE loid = :id

Compression of BYTEA might be a bit better because it compresses the entire contents as a whole (limited to 1GB), whereas large objects are compressed as individual chunks of 2048 bytes or less (in fact, pagesize/4)

The compression ratio of bytea contents can be measured by comparing octet_length(column) with pg_column_size(column)-4

  Does it make sense to try using Postgresql for this?

You should probably import a few hundred of your files in Postgres and see how they compress. For large objects it's a simple as \lo_import filename in psql.


Custom Sequence using Trigger & Sequence in Oracle SQL

Gap Free Sequential Number

What you seek can be referred to as a "gap free sequential number".

These types of Business Requests are known to slow down performance for the tables involved.  This is due to the required serialization on the entity that generates the number.

Parent Table

PARENT_ITEM.ID is not going to be a "gap free sequential number"; you are missing the NOCACHE option.

Even then, you can still have gaps.

Child Table

I recommend you keep CHILD_ITEM.ID as a surrogate key.  I would keep the defaults for the Identity column.  Also, you should have a separate column for the numerical value that represents "-1".  I'll call this column CHILD_NUMBER_SEQUENCE.

Now, to serialize the "number generator" for CHILD_SEQUENCE, you must obtain a lock based on PARENT_ITEM_ID.

For Oracle, the easiest way is to lock the parent row.  If the table PARENT_ITEM maintains "current child sequence number" the algorithm becomes easy since you can "lock the parent row" and "get the next value" at the same time.

Table Modifications

alter table parent_item add ( current_child_sequence_number number(5) default 0);
alter table child_item add (child_sequence_number number(5) not null);
alter table child_item add
  constraint child_item_uq1 unique (parent_item_id, child_sequence_number);

Code to generate `CHILD_ITEM.CHILD_SEQUENCE_NUMBER'

-- this locks and updates the parent row.
update parent_item p
set p.current_child_sequence_number = nvl(p.current_child_sequence_number,0) + 1
where p.id = :new.parent_item_id
returning p.current_child_sequence_number into :new.child_sequence_number;
-- make sure you check that 1 and only 1 row was updated.
-- fail if that isn't true.

Final Notes

The code is specific for single row processing.

If you want performance for bulk loading, you will have to use a staging table (eg Global Temporary Table) and a Procedure.

Again, the Business Requirement of "gap free sequential numbers" is slow by nature.  Don't expect it to be fast.


Get TEXT value of a CLOB OID in Postgresql

Thanks goes to a_horse_with_no_name.  Solution is:

update answers set answer_value = lo_get(cast(value as bigint))

Note - the lo_get function appears to be present in Postgres 9.4 or higher.  For earlier versions, I don't see a way of doing this directly.  I'm currently running 9.0, but this just accelerated my upgrade plans.


How to clean every non-digit except the + at beginning of string with regexp_replace in Postgresql?

You can anchor the first character to be replaced to the begin of the string and search for everything not 0 - 9 or + and assert with a look behind, that any other replacement is preceded by any character, hence it's not the begin of the string.

regexp_replace('+(7)9125415501', '^[^0-9\+]|(?<=.)[^0-9]', '', 'g')

Look behinds were introduced in version 9.6. If you're on anything lower than that you can only use look aheads. You can do so reversing the string and instead of looking behind and anchoring for the beginning look ahead and anchor for the end. Rereverse it in the end.

reverse(regexp_replace(reverse('+(7)9125415501'), '[^0-9](?=.)|[^0-9\+]$', '', 'g'))


How to determine if a column already exists in a MySQL table before altering the table to add it?

You were on the right track.  Add one thing before your code:

USE YourDb;  -- You need to be in _your_ db to make `DATABASE()` work right

SELECT COUNT(*) as col_count
    FROM information_schema.COLUMNS
    WHERE  TABLE_SCHEMA = DATABASE()
      AND  TABLE_NAME = 'mytable'
      AND  COLUMN_NAME = 'status';


How to replicate particular table in MySQL master-slave configuration?

The only other way would be at the application level but it will be a little messy.

TECHNIQUE #1

In your application, you can prevent replication of an INSERT of any table as follows:

SET sql_log_bin = 0;
INSERT INTO ...
SET sql_log_bin = 1;

You would surround SQL statement or transaction with SET sql_log_bin = 0; and SET sql_log_bin = 1;.

TECHNIQUE #2

Another way (less code editing) would be to run SET sql_log_bin = 0; as the first statement after establishing a persistent DB Connection.

You would then need to enable binary logging for the table you want replicated like this:

SET sql_log_bin = 1;
INSERT INTO ...
SET sql_log_bin = 0;

EPILOGUE

Obviously, TECHNIQUE #2 would be best. However, you would still need to deploy the code changes for this. You must make sure you have persistent DB Connections. You would also need to test this if you are using Connection Pooling.

UPDATE 2019-06-01 21:57 EDT

replicate-do-table must be setup on a slave, not a master.

In your particular situation, there is just one major problem: This option was first created in MySQL 5.6. It is also available in MySQL 5.7 and MySQL 8.0.

You had posted the same question in another post (which I closed). In that post, you specified MySQL 5.1. replicate-do-table does not exist in MySQL 5.1 or MySQL 5.5. This make my somewhat convoluted answer more applicable because MySQL 5.1 does not have this option.

You could just update Master and Slave to MySQL 5.6 and then setup the replication-do-table on the slave to your heart's content.

CAUTION

You may want to not use such filtering if you only have one slave. The use of only one slave makes it virtually impossible to perform any kind of point-in-time recovery using a slave. See Percona Blog "Why MySQL’s binlog-do-db option is dangerous" on this. Even though the Blog in 10 years old, it still applies.

If you do upgrade Master and Slave to MySQL 5.6, get a second slave. Setup one slave with no filters and the second slave with replication-do-table.

If you cannot upgrade MySQL, then you must go with the application-level techniques I first suggested. At least it will allow Master and Slave to be identical in data content.

Upgrading to MySQL 5.6 is actually the best option.


Trace flag and which need to be turned off and Why

The Database Upgrade Advisor can report that trace flag 8017 needs to be turned off during the upgrade process, and re-enabled afterward.

Trace flag 8017 controls whether SQL Server creates schedulers for all logical processors, including those that are not available for SQL Server to use (according to the affinity mask).

Enabling the flag does not create these 'offline' schedulers, saving a few resources. It also means you cannot dynamically add processors for SQL Server by changing the affinity mask; you would need to change the affinity mask and then restart the instance.

If you do not understand why the trace flag is present, do not remove it. There should be some documentation somewhere in your organization to say when and why it was added (and by whom). If not, well there's a process you should fix :)


See X-Request-ID in error messages of PostgreSQL

Postgres knows nothing about how your application generates the SQL requests that are sent to the database engine. So it also has no idea that there is a HTTP request behind it, let alone the header of such a request.

The information that can be put into a log line is fixed and documented in the manual

The only "log variable" that you can dynamically change, is the application name. However that requires that your application sets that each time it processes a request.

Before running a SQL query, your application needs to run something like this:

set application_name = 'request-42';

where 'request-42' is the request ID you extracted from the HTTP request.

Once you have that, you can add the %a to log_line_prefix and it will be shown in the log file.


Take advantage of monotonic columns in PostreSQL

if in a row col1 is greater then or equal two col1 in another row, then the same relation is valid between the two corresponding col2 entries

In which case you can reformulate your query to look like:

SELECT * FROM table WHERE col2 >= val1 AND col2 <= val2;

because you can find the lower bound for col2 from the lower bound for col1, like this:

schema:

create schema stack;
set search_path=stack;
--
create table t(foo integer, bar integer);
insert into t(foo,bar) select 10*g, 20*g from generate_series(1,100000) g;
create index on t(foo);
create index on t(bar);

method:

explain analyse select min(foo) from t where foo>500; -- assuming val1=500

  ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                              QUERY PLAN                                                               │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Result  (cost=0.38..0.39 rows=1 width=0) (actual time=0.063..0.064 rows=1 loops=1)                                                    │
│   InitPlan 1 (returns $0)                                                                                │
│     ->  Limit  (cost=0.29..0.38 rows=1 width=4) (actual time=0.059..0.060 rows=1 loops=1)                                             │
│           ->  Index Only Scan using t_foo_idx on t  (cost=0.29..2803.63 rows=33167 width=4) (actual time=0.058..0.058 rows=1 loops=1) │
│                 Index Cond: ((foo IS NOT NULL) AND (foo > 500))                                                                       │
│                 Heap Fetches: 1                                                                                │
│ Total runtime: 0.087 ms                                                                                │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

  select min(foo) from t where foo>500;

  ┌─────┐
│ min │
├─────┤
│ 510 │
└─────┘

  select min(bar) from t where foo=510;

  ┌──────┐
│ min  │
├──────┤
│ 1020 │
└──────┘

  explain analyse select * from t where bar>=1020 and bar<= 1100; -- assuming val2=1100

  ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                     QUERY PLAN                                                      │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on t  (cost=13.42..485.00 rows=500 width=8) (actual time=0.011..0.013 rows=5 loops=1)              │
│   Recheck Cond: ((bar >= 1020) AND (bar <= 1100))                                                                   │
│   ->  Bitmap Index Scan on t_bar_idx  (cost=0.00..13.29 rows=500 width=0) (actual time=0.008..0.008 rows=5 loops=1) │
│         Index Cond: ((bar >= 1020) AND (bar <= 1100))                                                               │
│ Total runtime: 0.030 ms                                                                                │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

  select * from t where bar>=1020 and bar<= 1100;

  ┌─────┬──────┐
│ foo │ bar  │
├─────┼──────┤
│ 510 │ 1020 │
│ 520 │ 1040 │
│ 530 │ 1060 │
│ 540 │ 1080 │
│ 550 │ 1100 │
└─────┴──────┘

clean up:

drop schema stack cascade;


Why doesn't jsonb constructor from a NULL record behave like hstore constructor?

The workaround here is to construct the jsonb by proxy of jsonb_populate_record

SELECT jsonb_populate_record(
  null::my_type,
  '{}'
);

Or, in the event you're just trying to update the record type, you can do it directly with the one call.

SELECT jsonb_populate_record(
  null::my_type,
  jsonb_build_object('name', 'Evan')
);


Display non-null and null count using case statement

This may not directly help you, since I only know SQL Server, but in any case this is how I would approach this:

Sample Data:

CREATE TABLE #Example
(
    Eid integer NULL
);

INSERT #Example
    (Eid)
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (NULL),
    (NULL),
    (NULL),
    (NULL),
    (NULL);

My idea revolves around the idea that COUNT(column) does not count NULLs, whereas COUNT(*) does:

SELECT
    [Number] = COUNT(E.Eid),
    [Null] = COUNT(*) - COUNT(E.Eid)
FROM #Example AS E;

Output 1:

+--------+------+
| Number | Null |
+--------+------+
|      5 |    5 |
+--------+------+

If the specific arrangement of rows and columns is important, I would UNPIVOT:

WITH Previous AS
(
    -- Same query as above
    SELECT
        [Number] = COUNT(E.Eid),
        [Null] = COUNT(*) - COUNT(E.Eid)
    FROM #Example AS E
)
SELECT
    U.Eid,
    U.Counts
FROM Previous
UNPIVOT
(
    Counts FOR Eid IN ([Number], [Null])
) AS U;

Output 2:

+--------+--------+
|  Eid   | Counts |
+--------+--------+
| Number |      5 |
| Null   |      5 |
+--------+--------+

Using CASE

SELECT
    C.Name,
    Counts = COUNT(*)
FROM
(
    SELECT
        Name =
            CASE
                WHEN E.Eid IS NULL
                THEN 'Null'
                ELSE 'Number'
            END
    FROM #Example AS E
) AS C
GROUP BY
    C.Name;

Output 3:

+--------+--------+
|  Name  | Counts |
+--------+--------+
| Null   |      5 |
| Number |      5 |
+--------+--------+

Edit:

This is the query form Babu ended up using:

select
    count(*) as cnt,
    case
        when smpth is null
        then 'NULL'
        else 'NUMBER'
    end numbertest
from employees
group by
    case
        when smpth is null
        then 'NULL'
        else 'NUMBER'
    end numbertest


Copy mysql_native_password to new server

Each version of MySQL features a specific number of columns for the mysql.users table
I mentioned this back on April 12, 2012 : Cannot GRANT privileges as root
You cannot mysqldump the mysql schema from one major version and load it into a different major version.
What you need is to generate the SQL to create the users
I wrote a version for MySQL 5.6 and back on Mar 22, 2015 : Export all MySQL users
I had suggested using pt-show-grants. I also suggested an emulation of it.
Here is the  emulation code from that post
GRANT_CMDS=/tmp/ShowGrantsToExport.sql
GRANT_FILE=MyDatabaseUSers.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_CMDS}
mysql ${MYSQL_CONN} < ${GRANT_CMDS} | sed 's/$/;/g' > ${GRANT_FILE}

WHAT ABOUT MySQL 5.7+ ???
pt-show-grants will not generate the SQL for the password to be visible. (PCI auditors will be crawling the walls looking for password exposures)
Here is a script to to dump create user commands with their password hashes
GRANT_CMDS=/tmp/ShowGrantsToExport.sql
GRANT_FILE=MyDatabaseUSers.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 1024 * 1024 * 10;"
SQL="${SQL} SELECT CONCAT('CREATE USER IF NOT EXISTS ',QUOTE(user),'@',QUOTE(host),' IDENTIFIED WITH mysql_native_password AS ',QUOTE(authentication_string),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>'';"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_FILE}
SQL="SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_CMDS}
mysql ${MYSQL_CONN} < ${GRANT_CMDS} | sed 's/$/;/g' >> ${GRANT_FILE}

If you want this to works for MySQL 5.5 and 5.6, change the word authentication_string to password, and adjust the CREATE USER syntax as needed, perhaps something like this
GRANT_CMDS=/tmp/ShowGrantsToExport.sql
GRANT_FILE=MyDatabaseUSers.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 1024 * 1024 * 10;"
SQL="${SQL} SELECT CONCAT('CREATE USER ',QUOTE(user),'@',QUOTE(host),' IDENTIFIED AS ',QUOTE(password),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>'';"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_FILE}
SQL="SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_CMDS}
mysql ${MYSQL_CONN} < ${GRANT_CMDS} | sed 's/$/;/g' >> ${GRANT_FILE}

I did not test these. So, please test them and see if it produces it for you.
WHAT NEXT ???
You then load the MyDatabaseUSers.sql into the new MySQL instance
Please avoid using --all-databases when dumping everything. You shouuld dump everything except the mysql schema (See my old post How do you mysqldump specific table(s)? on how to do that)


Do you mean exactly one occurrence?  Or at least one occurrence?

These will (I think) find No for "at least":

SELECT No  FROM tbl AS m
WHERE  EXISTS ( SELECT 1 FROM tbl WHERE
                 No = m.No AND  letter IN ('a', 'x', 'y', 'z') )
  AND  EXISTS ( SELECT 1 FROM tbl WHERE
                 No = m.No AND  letter IN ('b', 'w', 'l') )
  AND  EXISTS ( SELECT 1 FROM tbl WHERE
                 No = m.No AND  letter IN ('c', 'k', 'j') )

Another...

SELECT No
    FROM ( SELECT No, GROUP_CONCAT(letter SEPARATOR '') AS lets
               FROM table
               GROUP BY No
               HAVING lets RLIKE '[axyz]'
                  AND lets RLIKE '[bwl]'
                  AND lets RLIKE '[ckj]' ) AS n

(Nothing even close to your invented IN syntax is allowed.)


The parameter is described in the Upgrade Guide. You cannot downgrade a database to a version that is below the version of the COMPATIBLE parameter. The COMPATIBLE parameter controls how data is written to disk. But it does not control the usage of optimizer features. There are a lot of parameter to control the oprimizer features. The OPTIMIZER_FEATURES_ENABLED is a parameter that you have to set appropriately to enable the optimizer features of the Oracle version. Tom Kytes discusses the difference of these two parameter in this article.


Can the table spool operator be parallelised?

I am assume you are meaning common sub expression spools here (i.e. calc once, used many). If so then I would expect this behaviour.

A common sub expression spool is effectively a temp table (its a bit more sophisticated than that but you get the idea). The complex action is completed once and then stored to be re-used many times. If you think about it like this then hopefully you can see why it would be breaking parallelism as the "temp" table can't be read by multiple threads simultaneously due to locking etc.

Adam Machanic has a lot of great info on the things that break parallelism from his SQLBits X session:

https://sqlbits.com/Sessions/Event10/Query_Tuning_Mastery_The_Art_of_Manhandling_Parallelism


Right way to delete the archive - Oracle

In order to delete them you can do: RMAN>crosscheck archivelog all;
RMAN>delete noprompt expired archivelog all; , you can also include the delete input clause when you back them up, and they will be deleted after they have been backed up (it is up to you). You can try and run the command you have shown manually to see what will happen in RMAN (in other words, does it work or does it give an error).


Will Re-Seeding an Identity column back to 0 cause page splits?

My question is, how will SQL Server behave when the value of the identity column wraps around?

Assuming the index is 100% full the first few inserts at the new key value will cause one or two page splits, where half the rows on the full page are copied to the new page, and then the new row will be inserted into one of the half-full pages.

However after that you resume low-cost inserts and page allocations.  When the page fills up a new, empty page is allocated and inserted into the doubly-linked list of leaf pages.  No rows are copied to the new page, and the new row is written to the new page.

The only difference between this and an end-of-index insert is the presence of a next page after the new page.  And the next page must be updated with a reverse page pointer to the new page.

See this post for details: Good Page Splits and Sequential GUID Key Generation

  Will inserting into gaps cause a performance issue?

No.

As SQL Continues inserting into the "middle" of the index, when the target page for an insert is full, SQL will always allocate a new page.  It won't put the row on the next existing page, even if that page is not full.  The non-leaf levels of the clustered index store the starting value for each leaf-level page, so SQL can't insert the new row onto the next existing page without rewriting the non-leaf level of the index.

You can see this behavior easily by forcing page locks and looking at the lock footprint of your inserts (or less easily with DBCC PAGE). In the example below a clustered index table that can fit exactly 4 rows on a page take a series of mid-index inserts.

  Assume every page can hold 4 rows. You have full page existing rows
  with ascending key values from 10-20, and now you want to insert key
  values 1-9.

use master
drop database ps_test
go
create database ps_test
go
use ps_test
go
drop table if exists ps_test
set nocount on

go

--8,060=4*(4+2011)
create table ps_test(id int primary key, data char(2011) not null default '')

go
DBCC TRACEON(3604)
go

insert into ps_test(id) values (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21)--,22

go

dbcc ind('ps_test','ps_test',1)
--three data pages
/*

PageFID PagePID         PageType
------- ------- / /     --------
1       336             10
1       328             1
1       329             2
1       330             1
1       331             1
*/

insert into ps_test(id) values (1)

dbcc ind('ps_test','ps_test',1)
--four data pages 332 has been inserted between 328 and 330
/*

PageFID PagePID        PageType PrevPagePID NextPagePID
------- ---------/ /   -------- ----------- -----------
1       336            10       0           0
1       328            1        0           332
1       329            2        0           0
1       330            1        332         331
1       331            1        330         0
1       332            1        328         330
*/

dbcc page(ps_test,1,328,3)
-- 328 has rows 1,10 and 4048 bytes free
-- the page is split
/*
PAGE: (1:328)
...
pminlen = 2019                      m_slotCnt = 2                       m_freeCnt = 4048
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1
...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 10

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/

dbcc page(ps_test,1,332,3)
--page 332 has 11,12,13 and 2024 bytes free
/*

PAGE: (1:332)
...
pminlen = 2019                      m_slotCnt = 3                       m_freeCnt = 2024
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 11
...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 12
...
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 13
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/

insert into ps_test(id) values (2)
insert into ps_test(id) values (3)

dbcc ind('ps_test','ps_test',1)
--no page split.
/*

PageFID PagePID     PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- -------- ---------- ----------- ----------- ----------- -----------
1       336         10       NULL       0           0           0           0
1       328         1        0          1           332         0           0
1       329         2        1          0           0           0           0
1       330         1        0          1           331         1           332
1       331         1        0          0           0           1           330
1       332         1        0          1           330         1           328

the new rows went on page 328, which now has

pminlen = 2019                      m_slotCnt = 4                       m_freeCnt = 2024
*/

So now we have page 328 is full and the next page 332 has only 3 rows.  But the lowest value on 332 is 11.  So when we insert the value 4, does go on 332, which will cause another bad page split when 5 is inserted?

insert into ps_test(id) values (4)
dbcc ind('ps_test','ps_test',1)

/*
A new page 333 has been inserted in the list between 328 and 332

PageFID PagePID     PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- -------- ---------- ----------- ----------- ----------- -----------
1       336         10       NULL       0           0           0           0
1       328         1        0          1           333         0           0
1       329         2        1          0           0           0           0
1       330         1        0          1           331         1           332
1       331         1        0          0           0           1           330
1       332         1        0          1           330         1           333
1       333         1        0          1           332         1           328

*/

dbcc page(ps_test,1,333,3)

/*

PAGE: (1:333)
...
pminlen = 2019                      m_slotCnt = 1                       m_freeCnt = 6072
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 10
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

So another "bad page split", but this one only moved the row with id=10.  And leaves page 328 full with values 1,2,3,4

dbcc page(ps_test,1,328,3)
/*
PAGE: (1:328)
...
pminlen = 2019                      m_slotCnt = 4                       m_freeCnt = 0
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1
...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 2
...
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 3
...
Slot 3 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 4
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

Now all the larger key values are on seperate pages, and from here on out there are no more bad page splits.  The next insert requires a new page, and only the new row is on the new page:

    insert into ps_test(id) values (5)
    dbcc ind('ps_test','ps_test',1)

/*

PageFID PagePID      PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- -----------  -------- ---------- ----------- ----------- ----------- -----------
1       336          10       NULL       0           0           0           0
1       328          1        0          1           334         0           0
1       329          2        1          0           0           0           0
1       330          1        0          1           331         1           332
1       331          1        0          0           0           1           330
1       332          1        0          1           330         1           333
1       333          1        0          1           332         1           334
1       334          1        0          1           333         1           328

*/

The new page 334, has only the last-inserted row.

 dbcc page(ps_test,1,334,3)
/*
PAGE: (1:334)
...
pminlen = 2019                      m_slotCnt = 1                       m_freeCnt = 6072
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 5
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
id = 5

*/

So after a couple of initial "bad page splits" that required moving rows, subsequently new pages are allocated and spliced into the middle of doubly-linked list of leaf pages.  The new pages are allocated when a new row wont fit on the previous page and no rows are migrated to the new page.


yes, you can safely delete the extra column if you are sure it's not in the master.

But the bigger question is why didn't the change carry over to the slave, Any errors when you do a "show slave status" is the "seconds behind master" 0?

You might as well refresh the slave with a newer backup from the master to be sure everything is consistent.


Try This

SELECT A.*,B.mat_nama,C.mats_nama
FROM pengeluaran AS A
INNER JOIN material AS B ON A.mat_id = B.mat_id
INNER JOIN mat_satuan AS C ON B.mat_satuan_id = C.mats_id
WHERE A.mat_id = '7'
   AND A.pengeluaran_tgl BETWEEN '2014-01-01'  AND '2014-02-28'

UNION

SELECT A.*,B.mat_nama,C.mats_nama
FROM penerimaan_sub AS A
INNER JOIN material AS B ON A.mat_id = B.mat_id
INNER JOIN mat_satuan AS C ON B.mat_satuan_id = C.mats_id
WHERE A.mat_id = '7'
  AND A.penerimaan_tgl BETWEEN '2014-01-01' AND '2014-01-28'


Restrict access to Edition- or instance-specific databases

There is no way to prevent a client software from accessing a SQL Server instance when correct credentials are supplied.

SQL Server Management Studio is the same product for all editions (actually, starting from SQL Server 2016, it is a separate product which ships separately from the engine).

Your only option to prevent users from accessing one of the instances in your server is granting permissions only to the people entitled to access the data.


How can I force SQL Server Management Studio to update the IntelliSense cache?

You need to use this key combination:

  CTRL + SHIFT + R

Alternatively, use the menu item:

  Edit > IntelliSense > Refresh Local Cache

This article might also be useful (for future readers who have more perplexing IntelliSense issues):

Troubleshooting IntelliSense in SQL Server Management Studio


High number of page reads and PLE counter, but only when tracing with Profiler

Unfortunately, as is the case with other counters, the definition of 'reads' is not identical across the board. If a plan has calls to UDFs, statistics IO may under-report them (or hide them completely) while profiler still displays them.

Regarding the PLE counter, if the server has more than one (physical) NUMA nodes, it is important to use the ones under 'SQL Server: Buffer Node'. The PLE figure under 'SQL Server: Buffer Mgr' is an average of the Node PLEs, and can be hiding horrors sometimes (I've seen PLE of 400 on one node and 7000 on the other, with SQL supposedly using both nodes).


How to alter database hour and date in Oracle?

ALTER DATABASE

set_time_zone_clause::=

  set_time_zone_clause

  This clause has the same semantics in CREATE DATABASE and ALTER
  DATABASE statements. When used in with ALTER DATABASE, this clause
  resets the time zone of the database. To determine the time zone of
  the database, query the built-in function DBTIMEZONE. After setting or
  changing the time zone with this clause, you must restart the database
  for the new time zone to take effect.

  Oracle Database normalizes all new TIMESTAMP WITH LOCAL TIME ZONE data
  to the time zone of the database when the data is stored on
  disk.Oracle Database does not automatically update existing data in
  the database to the new time zone. Therefore, you cannot reset the
  database time zone if there is any TIMESTAMP WITH LOCAL TIME ZONE data
  in the database. You must first delete or export the TIMESTAMP WITH
  LOCAL TIME ZONE data and then reset the database time zone. For this
  reason, Oracle does not encourage you to change the time zone of a
  database that contains data.

  For a full description of this clause, refer to set_time_zone_clause
  in the documentation on CREATE DATABASE.

SQL> select dbtimezone from dual;

DBTIME
------
+02:00

SQL> ALTER DATABASE SET TIME_ZONE = '-05:00';

Database altered.

SQL> select dbtimezone from dual;

DBTIME
------
+02:00

SQL> startup force
ORACLE instance started.

Total System Global Area  536868920 bytes
Fixed Size                  8898616 bytes
Variable Size             226492416 bytes
Database Buffers          293601280 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL> select dbtimezone from dual;

DBTIME
------
-05:00


I have an application that (mostly) uses only the very latest data
  available for a certain identifier (may consist of multiple
  columns/keys). However, these data may be bad or implausible. In these
  cases, it has to access the history of the entry to verify that value
  or calculate an alternative.

  For the second use case I created an sorted index identifier..., time.
  However, in my mind this index does not really fit the main use case,
  because it still have to consider n values (with different times) for
  each identifier.

  Is there a way in mongodb to create an index on something like
  identifier..., $max(time)? Or isn't that necessary at all because
  mongodbs internal query optimization is smart enough to only look at
  the first/last value of the index?

As per MongoDB Blog documentation here By default, MongoDB generates a unique ObjectID identifier that is assigned to the _id field in a new document before writing that document to the database. In many cases the default unique identifiers assigned by MongoDB will meet application requirements. However, in some cases an application may need to create custom unique identifiers, such as:

The application may require unique identifiers with a precise number
of digits. For example, unique 12 digit identifiers might be required
for bank account or credit card numbers.
Unique identifiers may need to be generated in a monotonically
increasing and continuous sequential order.
Unique identifiers may need to be independent of a specific database
vendor. Due to the multi-threaded and distributed nature of modern
applications, it is not always a straightforward task to generate
unique identifiers that satisfy application requirements.

Use ObjectID as a unique identifier

MongoDB database drivers by default generate an ObjectID identifier that is assigned to the _id field of each document. In many cases the ObjectID may be used as a unique identifier in an application.

ObjectID is a 96-bit number which is composed as follows:

a 4-byte value representing the seconds since the Unix epoch (which
will not run out of seconds until the year 2106)
a 3-byte machine identifier (usually derived from the MAC address)
a 2-byte process id, and
a 3-byte counter, starting with a random value.

Benefits

ObjectID is automatically generated by the database drivers, and will
be assigned to the _id field of each document.
ObjectID can be considered globally unique for all practical
purposes.
ObjectID encodes the timestamp of its creation time, which may be
used for queries or to sort by creation time.
ObjectID is mostly monotonically increasing.
ObjectID is 96-bits, which is smaller than some (eg. 128-bit)
alternative UUID implementations, which will result in slightly
smaller documents that will use slightly less disk space and RAM than
these alternatives.

Use a single counter document to generate unique identifiers one at a time

A counter document for unique identifier generation could look as follows:

{
    "_id"   : "UNIQUE COUNT DOCUMENT IDENTIFIER",
    "COUNT" : 0,
    "NOTES" : “Increment COUNT using findAndModify to ensure that the COUNT field will be incremented atomically with the fetch of this document",
}

And the unique identifier-generation document could be atomically requested and incremented as follows. Note that by default the document returned from findAndModify is the pre-modification document:

db.uniqueIdentifierCounter.findAndModify({
    query: { _id: "UNIQUE COUNT DOCUMENT IDENTIFIER" },
    update: {
        $inc: { COUNT: 1 },
    },
    writeConcern: 'majority'
})

Use a single counter document that allocates batches of unique identifiers

This approach is similar to the previous approach, with the difference being that instead of incrementing the COUNT value by 1, we may wish to increment it by a larger number that will represent a batch of unique identifiers that will be allocated by the database to the application.

For example, if the application knows that it needs 1000 new unique identifiers, then the application would use findAndModify() to atomically get the current COUNT and increment the COUNT value by 1000. The document returned from the findAndModify command would contain the starting value for the batch of unique identifiers, and the application would loop over 1000 values from that starting point.

var seq_increment = 1000;
db.uniqueIdentifierCounter.findAndModify({
    query: { _id: "UNIQUE COUNT DOCUMENT IDENTIFIER" },
    update: {
        $inc: {COUNT: seq_increment },
    }
    writeConcern: 'majority'
})


Change

select @cols = STUFF((SELECT distinct ',' +
                    QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
                  FROM OSCL
                  WHERE     ([status] = - 3)

                  FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)')
                    , 1, 1, '');

to

select @cols = STUFF((SELECT ',' +
                    QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
                  FROM OSCL
                  WHERE     ([status] = - 3)
                  GROUP BY QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate))), DATEPART(MONTH, CONVERT(DATETIME, createDate))
                  ORDER BY DATEPART(MONTH, CONVERT(DATETIME, createDate))

                  FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)')
                    , 1, 1, '');


You could use a row-level security policy with a statement_type limited to update (or more likely update and delete, and maybe insert too). See the DMBS_RLS.ADD_POLICY docs for details.

Dummy scenario: a list of tasks, only task owners can modify their task.

create table owners(owner_id int primary key
                  , owner_name varchar2(10));
create table tasks(task_id int primary key
                 , owner_id int
                 , description varchar2(20)
                 , completion number);

insert into owners(owner_id, owner_name) values (1, 'Mat');
insert into owners(owner_id, owner_name) values (2, 'Mark');
insert into tasks(task_id, owner_id, description, completion)
       values (100, 1, 'Task for Mat', 0);
insert into tasks(task_id, owner_id, description, completion)
       values (200, 2, 'Task for Mark', 0);
commit;

The policy function:

create or replace
function tasks_update_policy(schema varchar2, tab varchar2)
return varchar2
is
  owner_id  number;
begin
  select owner_id into owner_id
    from owners
    where lower(owner_name) = lower(sys_context('userenv','session_user'));
  return 'owner_id = ' || owner_id;
exception
  when no_data_found then
    return '1=2';  -- deny unregistered users
end;

Implementing the policy:

begin
  dbms_rls.add_policy(object_schema => 'MAT'
        , object_name => 'TASKS'
        , policy_name => 'Tasks_update_policy'
        , policy_function => 'tasks_update_policy'
        , statement_types => 'update,delete,insert'   -- policy restriction
        , update_check => true);
end;
/

When logged in as myself:

SQL> select * from mat.tasks;

   TASK_ID   OWNER_ID DESCRIPTION      COMPLETION
---------- ---------- -------------------- ----------
       100      1 Task for Mat          0
       200      2 Task for Mark         0

SQL> update mat.tasks set completion = 20 where task_id = 100 ;

1 row updated.

SQL> update mat.tasks set completion = 20 where task_id = 200 ;

0 rows updated.

When connected as 'Mark':

SQL> insert into mat.tasks values (101, 1, 'More work for Mat', 0);
insert into mat.tasks values (101, 1, 'More work for Mat', 0)
                *
ERROR at line 1:
ORA-28115: policy with check option violation


Sadly, you cannot do that with MySQL.
Notwithstanding, there are two things try
1: Tweek the Query Optimizer
You can change the Query Optimizer behavior. Please see my posts about doing so, but always check the MySQL Documentation first and foremost.
2: Make MySQL use the index
Rather that reinvent the wheel, see my old post from Oct 28, 2011, Using FORCE INDEX


I don't see the need for multiple stage tables at all (tip: if you have increasing numbers on columns or tables you probably violated normalization rules).

I would create a single stage table that contains the information which stage that is:

CREATE TABLE stage
(
  id SERIAL PRIMARY KEY,
  level integer not null check (level between 1 and 4),
  description TEXT
);

CREATE TABLE task
(
  id SERIAL PRIMARY KEY,
  description TEXT,
  stage_id integer references stage
);

If you get new stages in the future, all you need to do is add new rows to the stage table.


Postgresql AUTOVACUUM ANALYSE parameters for a specified table

Since your table has 15998 pages but only 150 rows, it must be quite bloated.  A one time VACUUM FULL or CLUSTER should fix that.  Then the question would be whether it will just get bloated again.

In order to make it more aggressive, you would probably need to lower autovacuum_naptime (unless you have already done so) because it is already being vacuumed very often.

10 seconds is about how long I would expect it to take to analyze a table of that size under default autovac io throttling settings.  But in that case, it is hard to believe that it is consuming a meaningful amount of CPU time.  Have you measured the CPU usage?

What is the lock interfering with?  If you are routinely running commands that conflict with an ANALYZE, that could explain why the table got so bloated in the first place.


Your query must be using an index on "id" to scan the index in the implied order, and then filtering out everything where "user_id" does not equal 123, stopping after it finds 31 rows which survive the filter.  Going in one direction it quickly finds 31 such rows, going in the other direction needs to filter out a large number of rows before 31 survive (because none/few of the rows starting at that end have user_id=123).
You could readily confirm this theory by doing an EXPLAIN (ANALYZE, BUFFERS) of the queries.
This is not fundamentally about the order of the index scan.  If you picked a value for 123 which had the opposite property (they all occurred at the logical end of the index rather than the logical beginning) then the situation would be reversed. Specifying DESC would fix the problem, rather than causing it.

Any idea on what I can do to try and improve the speed of this query when sorting by DESC?

Your query seems pointless.  Counting is not an order-dependent activity.  This is probably not your real query.  So who knows if our suggestions would transfer over to your real query?  The most straighforward fix for this query would be to build a multicolumn index on (user_id, id).  Then no rows would get filtered out one by one, as they would be removed in wholesale through the operation of the index.


dbatools start-dbamigration running very slowly

You can look at the code for each Copy command used in the Start-DbaMigration command and try running them each individually (for the objects you want to migrate).

Overall the start command can take some time to run as it does a good bit of extra stuff in the command. Using each Copy command individually is the common method when you are not performing a full migration (including databases).

If you find any particular bugs or slowness you can get further assistance with issues by submitting an issue on our repository here.


Why not shard on a custom _id value since it's guaranteed to be unique? With the size around 1,000 an index on playerId should allow for good performance as well.

Still do some testing of course because it can still vary based on number of shards and overall size of the collection.

Found a video I was looking for at the time from the Mongo University M102 course: Cardinality & Monotonic Shard Keys, which provides some more insight.


You should familiarize yourself more with top - it shows > 100% because it displays this data as a percentage of a single CPU and presumably you have more than one of those.
I'd recommend a more robust monitoring setup for your SQL Server. The MS CAT team has a good article on how to do this for SQL Server on Linux.
Finally, stop running your application and SQL Server on the same host. Your error is actually concerning a lack of memory, of which you are probably capping SQL Server from using and/or it is competing with the application for. More resource contention issues like this are probable in the future with keeping both on the same host, as well as security concerns, patching difficulties, and more.


You join Completion (columns userID,recordID) to Record (columns userID,ID)

You join Record (column codeID) to Code (column ID)

Here is the proposed query

SELECT c.objectiveID,
   GROUP_CONCAT(d.description SEPARATOR ',') AS records,
   GROUP_CONCAT(c.text SEPARATOR ',') AS text
FROM Completion AS c
INNER JOIN Record AS r ON c.userID=r.userID AND c.recordID=r.ID
INNER JOIN Code   AS d ON r.codeID=d.ID
GROUP BY c.objectiveID;

Since GROUP_CONCAT's default separator is a comma, you could rewrite as

SELECT c.objectiveID,
   GROUP_CONCAT(d.description) AS records,
   GROUP_CONCAT(c.text) AS text
FROM Completion AS c
INNER JOIN Record AS r ON c.userID=r.userID AND c.recordID=r.ID
INNER JOIN Code   AS d ON r.codeID=d.ID
GROUP BY c.objectiveID;


You can use a CASE expression inside SUM():

SELECT
    l.whse, l.partid, i.descr, l.qty,

    CASE WHEN f.Netflag = 0 THEN 'NONUsable' ELSE 'Usable' END
        AS UsableStatus,
    SUM(CASE WHEN f.Netflag = 0 THEN l.qty ELSE 0 END)
      OVER (PARTITION BY l.partid) AS TotalNonUsable,
    SUM(CASE WHEN f.Netflag = 0 THEN 0 ELSE l.qty END)
      OVER (PARTITION BY l.partid) AS TotalUsable
FROM
    location l
  JOIN inventory AS i ON l.partid = i.partid
  JOIN loctable AS f ON l.whse = f.whse ;

By the way, I don't see why you need DISTINCT in the query.


MySQL 5.7 - Disable use of SSL connection

I would block the SSL port using your machine's software firewall (iptables, etc). This avoids having to restart mysqld.

e.g. if your SSL port is 3307:

iptables -I INPUT -i eth0 -p tcp --dport 3307 -j DROP

Otherwise, simply add a hash at the beginning of each line containing 'ssl' in your /etc/my.cnf, then restart mysqld.

e.g.

# have_ssl...
# anything_ssl...

If you're running mysqld < 5.7.x, then you can use the ssl=0 configuration option here. That option is deprecated and will be removed in a future release, though.

However, your best bet is to block all other ports than your non-ssl port and allow ssl clients to fail attempting to connect to that port.


How to improve performance if index is not Most SELECTIVE?

Without having much detail, I can't recommend much.

One thing that does jump out at me is that it's very likely you can improve performance on the table by normalizing it! The presence of so many duplicated (so few unique) values in the columns you listed suggests that perhaps many others in the table are not normalized, as well. I'm suggesting making the Name column an int (or even smallint) with a foreign key to a Names table, and the Current_state column bit (or alternately a tinyint) with a foreign key to a WhateverStates table. You would have to, of course, change your data access code to deal with this indirection, but that is nothing more than the basic job relational database developers have always had to do.

Normalizing will reduce the number of bytes per row, increasing the number of rows per page, reducing the number of pages that have to be read to satisfy any particular query, helping performance across the board! Right now the columns given require likely close to 34 bytes each. After the change I suggest, those columns will only require 11 bytes each. Of course, I haven't seen your whole table--your rows may be so big that it doesn't matter.

What columns and datatypes are in the clustered index (if there is one)? This can radically affect the size of the nonclustered indexes, again affecting performance in exactly the way I described (rows per page).

When you do query based on non-selective columns such as Current_state, what other columns are always or almost always included? It may be okay for you to have a nonselective column in an index if the index also contains a more selective column (or that in conjunction with the less selective column is more highly selective). If on the other hand you generally query often for rows based on the single column Current_state = 'Pending', then you can add a filtered index:

CREATE INDEX IX_YourTable_Pending ON dbo.YourTable (ClusteredColumnsInOrder)
WHERE Current_State = 'Pending'; --SQL 2008 and up only

This technique could help you even when you also include other columns: you would want to put those in the index instead of the ClusteredColumnsInOrder columns I suggested (which was just a tricky way to not put any additional columns into the nonclustered index, since--remember, now--nonclustered indexes always have all the columns of the clustered index implicitly included). Or, if you only pull a very few other columns, you can make your nonclustered index cover the query by adding INCLUDE (AdditionalColumn1, AdditionalColumn2) so that the query engine doesn't have to go back to the clustered index to satisfy the query.

You haven't provided very much information such as full table schema, sample data, and sample queries, and without those it's going to be pretty hard to give you very specific advice about what to do.

One thing I can say, though, is that indiscriminately throwing indexes at the table may not improve things much and could in fact hurt performance of your system overall.

If the hints I have given you here don't seem to help much, then I recommend that you do come back with some of the additional info I mentioned so that we can do a better job of assisting you.


A dynamic management view, like the name implies, isn't a table with indexes and statistics, but rather a view which could potentially use a large number of system tables.

To generally speed up complex DMV queries, you could try dumping the contents of some of the larger DMVs into a temp table or table variable and then use those. You can control the indexing on the temp tables, and it also reduces the number of join operations that the server has to perform.

For the same reason, if your solutions allows for it, consider using WITH (NOLOCK) in order to minimize locking on those system tables, which could otherwise affect your entire database or server.


Accepting only SSL connections to MS SQL server

Assuming you are using the Force Protocol Encryption option in SQL Server Configuration Manager for the server then nothing but SSL will work (assuming you have a certificate configured).

Clearly, the only way to know is to test.  Use SSMS or SQLCMD to attempt a connection that does NOT use SSL.  If it connects, you have done something wrong.


Having multiple refs in an intersection table

This is a possible design based on Joel Brown's correct answer (+1).  The WorkIds could come from the same sequence.

Author Table

╔══════════╦═══════════╦══════════╗
║ AuthorID ║ FirstName ║ LastName ║
╠══════════╬═══════════╬══════════╣
║        0 ║ A.R       ║ Smith    ║
║        1 ║ J.R.      ║ Hartley  ║
╚══════════╩═══════════╩══════════╝

AuthorWorks Table

╔══════════╦════════╗
║ AuthorId ║ WorkId ║
╠══════════╬════════╣
║        0 ║      1 ║
║        0 ║      2 ║
║        1 ║      3 ║
║        0 ║      4 ║
╚══════════╩════════╝

Book Table

╔════════╦══════════╗
║ WorkId ║  Title   ║
╠════════╬══════════╣
║      1 ║ asdf     ║
║      3 ║ jkl      ║
║      4 ║ zxcvzxcv ║
╚════════╩══════════╝

Journal Table

╔════════╦══════════╗
║ WorkId ║  Title   ║
╠════════╬══════════╣
║      2 ║ dfghdfgh ║
╚════════╩══════════╝


How to create SQL Server backup from Windows Server to Linux Client with SQLCMD?

the backup is done by the sql server process in a place reachable from the server.
the options usually are:

a local path
a networkpath (like a samba or cifs share)
an cloud storage

So what you are asking can't be done in the way you want.
As alternative you can script the content:
https://docs.microsoft.com/it-it/sql/ssms/scripting/generate-scripts-sql-server-management-studio?view=sql-server-ver15
Schema and data.


Aruba-MySQL: can't create/change table to engine=INNODB

Take a look at the InnoDB Internals (Picture from Percona CTO Vadim Tkachenko)

Please note the data dictionary inside ibdata1 (the system tablespace)

It sounds like you have a broken data dictionary entry on the task table when it existed

The only way around would be move all the tables into another database.

For starters, suppose you have database mydb1. Create another database mydb2

CREATE DATABASE mydb2;

For example, to move mydb1.mytable into mydb2, do this

ALTER TABLE mydb1.mytable RENAME mydb2.mytable;

Move all your tables sideways into mydb2.

You would then have to drop your old database

DROP DATABASE mydb1;

You should be able to convert the task table in mydb2 into InnoDB.

USE mydb2
ALTER TABLE `task` ENGINE = INNODB;

Give it a Try !!!


Setup logical Database model for measurements

From what I understand of your spreadsheet and your description, this is the normalized logical ERD for your data:

Now, based on how many GAP_CATEGORY records you have as a rule, and how many FORCE_SOURCE records you have, you might decide to do some denormalization.  You have to decide what is easier to build, maintain and use and makes more sense in your application.


Unique Constraints on Nullable Columns in SQL Server 2005

Actually, I had to do something like this once. It involved creating a computed column that takes the value of the Unique column when is not NULL and the value of the primary key (with some other logic to make it impossible to clash with the values on the unique column), and making the unique index on that column. You can see an example of this and the trigger method here.


You can try both of this:

SELECT a.customer_id, b.value, b.attribute_id
FROM a
JOIN b
    ON a.customer_id = b.customer_id
WHERE b.attribute_id = 2

or:

SELECT a.customer_id, b.value, b.attribute_id
FROM a
JOIN (SELECT value, attribute_id FROM b WHERE attribute_id = 2) as b
    ON a.customer_id = b.customer_id


sp_executesql not accepting multiple types

It's the same reason this doesn't work:
DECLARE @x nvarchar(4000) = N'foo', @y bit = 1;

SELECT x = @x
UNION ALL
SELECT y = @y;

Same error. If you're going to use the wrong data types (for example, key is varchar(50), so why is @k6 nvarchar(4000)?), you're going to need to massage them to prevent unexpected errors that come from how SQL Server might construct an execution plan. Try making your statement:
INSERT INTO dbo.Settings ([key], [value])
VALUES (@k6,CONVERT(nvarchar(max),@v6)),
       (@k8,CONVERT(nvarchar(max),@v8));


Row level security and physical user creation is it an overhead

If you suspect that you'll be creating millions of database users, you should probably not do that.  I don't know what the maximum number of users for a single database is, but see Michael Swart's excellent article Swart’s Ten Percent Rule for why you don't want to come even close to that maximum.

You have a couple of other options rather than creating database users for each connecting user.  See Limiting access to data using Row-Level Security on Microsoft's SQL Server Blog.

  Can I limit access based on AD group memberships?

  Yes, you can use the IS_MEMBER() function in your predicate to check SQL role or AD group memberships. For an example, see the RLS Hospital Demo script.

  What if my application uses connection pooling with a single login for all users?

  No problem, your application can use the new SESSION_CONTEXT feature to get and set session-scoped key-value pairs to identify users for RLS, while still enabling efficient connection pooling.

The IS_MEMBER() approach is similar to the common USER_NAME() approach.

The SESSION_CONTEXT approach has a more detailed example in the docs here:

Scenario for users who connect to the database through a middle-tier application

The filter function pulls an application-set user id out of the SESSION_CONTEXT:

CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
        AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO

Which means the application needs to be modified to set that value whenever it connects to SQL Server to execute queries:

EXEC sp_set_session_context @key=N'UserId', @value=1;


User defined function can only have select statements

This is not about function (UDF) vs procedure. It is the context where you use them.

You can have DML operations in a function:

SQL> create table t1 (c1 number);

Table created.

SQL> create or replace function f1 return number as
  2  begin
  3    insert into t1 values (1);
  4    commit;
  5    return 0;
  6  end;
  7  /

Function created.

SQL> declare
  2    i number;
  3  begin
  4    i := f1;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from t1;

        C1
----------
         1

But if you use your function as and UDF in a query:

SQL> select f1 from dual;
select f1 from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "BP.F1", line 3

You can still work around this with an autonomous transaction:

SQL> create or replace function f2 return number as
  2    pragma autonomous_transaction;
  3  begin
  4    insert into t1 values (1);
  5    commit;
  6    return 0;
  7  end;
  8  /

Function created.

SQL> select f2 from dual;

        F2
----------
         0

SQL> select * from t1;

        C1
----------
         1
         1

But should you do this? I do not think so.


How to increase sql server performance? denormalization (making cascade delete off) vs making duplicate data

How big is this database? How many rows are in each table? Etc?

I would say that normalized data is default state to try to obtain. It is a leaner database, rows are shorter, and indexes may be used more effectively.  The short, leaner rows therefore lead to a smaller, leaner database.

One of the major accelerators of performance is memory.  If you can get your 5 tables to remain cached in memory, that will be a performance accelerator for your queries since you will avoid much of the disk I/O overhead.

You identify that you are joining with IDs (which are usually integers), so your indexes may be narrow and offer relatively inexpensive joins.

If you decide to denormalize, your tables will be bigger because they are carrying more redundant data on every row.  This causes a need for more memory to keep the data in cache and will require even more I/O when the cache is insufficient to buffer the data. (And your backups are bigger.)

In addition, you have taken on the task to denormalize and to maintain the denormalized data. This is an extra load of programming and on the server as well: consuming memory, I/O, and CPU.

But sometimes denormalization is the best choice.  Data Warehouses, for example, are largely denormalized data.  Also, you may find that in your system the benefits of denormalization may exceed the cost.

Still, you are asking a forum for an answer.

Your best answer would come by building normalized test case and seeing how it works.  Even though you may not have a lot of 'real data', you should generate a fairly large data set in the millions of rows to test with.

You can try to find a tool that does it for you (RedGate has one for example, but it is not free) or generate the data yourself so that you control the complexity.  There are online sources of states, cities, et cetera, and you can make up call centers, generate landline numbers and so forth.

Then try it.

If you do not like the performance, then create a denormalized table to test.  And put some effort into writing the code to maintain the denormalization, since that will become integral to your process.


How to reinstall PostgreSQL over an existing installation

Actually I've found out the problem. At least at my end with the same error.

I was creating an installer for my software with NSIS and it uses the postgresql database, in order to use the postgresql silent install I previously installed it on the target machine to test which parameters I should use, after tested it successfully I then uninstalled it so it could be installed by my software.

Then problem appears when I run my installer and the same error shows:

  The installation directory must be an absolute path, containing only letters, numbers and the characters '-', '/', '.' and '_', and must be writable.

I start to test everything from users, to user permissions, folder permissions, length of folder names.

Then I went to windows registry and found out the my first installation of postgresql left some registries for it even though I uninstalled it from the control panel also it left the postgres user on the machine.

So I delete all postgresql (Edit->find->postgresql) entries from the registry and also deleted the Windows user (postgres) and then everithing works great.

The only problem now is how to do it through the NSIS program.

Hope it helps.


Why does most Galera cluster documentation recommend having at least 3 nodes as the typical set up?

If you want real HA ("High Availability"), you need 3 nodes in 3 separate locations.

When one machine goes down, the rest of the nodes vote and see that they are in the majority.  They see 2 out of 3, so they declare themselves to be the cluster and their sibling to be out of the cluster.  If the dead machine comes back online, the majority chatter with the reanimated one and decide how to repair it.  All of this is automagic.

Let's take a slight variant of that -- 3 machines, but a network outage splits one away from the two.  The two, as above, declare them still in the cluster.  The one, not having a majority, realizes that he should not be the cluster.  So, it plays dead (or something).

What about only 2 nodes? -- If one dies, or is isolated, the other one does not have a majority, so it plays dead.  You have no cluster, and you are hosed.

2 + garbd -- This is where you have 2 machines with all the data, plus a third, little "arbitrator" machine.  The garbd pretends to have the data, but is really empty.  He votes, so any single machine outage can keep the cluster alive.

Why did I say "separate locations"?  What if you have a 3-node cluster with all 3 in the same rack in the same building on the same flood plain on the same earthquake fault on the path of a tornado, etc?  Some disaster is very likely to take out all 3 nodes.  No HA.

I have a few more comments and tips on Galera in http://mysql.rjweb.org/doc.php/galera


database design for storing individual users' items

A general rule of thumb is that you will have a table for each type of thing that you are recording information about.  Each instance of a thing will be a record in a table.

It would probably* be a terrible idea to have one table per user.  Have one table with USERS and one table with THINGS.  In the THINGS table you will have a foreign key which is a column containing a user_id that points at the USERS record for the owner of the thing.

Relational databases are designed specifically to make this kind of structure easy and fast to work with.  You shouldn't* ever need to worry about how many users there are or how many things they have.

* NOTE: Your requirements are very sparse.  These are rules of thumb.


User Mapping on a DB that's part of an AG. DB is READ only?

You'll need to create the logins on each instance (box), but you can only set up the user mappings on the one that is currently acting as the "primary" for that AG (the one that is not read only).

If you fill out all the necessary information in the "New Login" dialog, including the "User Mapping" tab, then click the "Script" button at the top, you end up with something like this:

USE [master]
GO
CREATE LOGIN [ACME\jdarnell] FROM WINDOWS
GO
USE [VersionStoreTestDB]
GO
CREATE USER [ACME\jdarnell] FOR LOGIN [ACME\jdarnell]
GO
USE [VersionStoreTestDB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ACME\jdarnell]
GO

For this test, I said to create a new login named "test-user" and map it to the "VersionStoreTestDB" database in the "db_datareader" role.

What you'll need to do is run the "CREATE LOGIN" portion on both boxes, and then run the "CREATE USER" and "ALTER ROLE" portions on just the primary box.  The mappings will be replicated to the other box.

Note that this example uses a Windows-based login.  If you're creating SQL logins, you will need to make sure and specify the SID on both servers.  See Transferring Logins to a Database Mirror for more details on that, and a script for making the job easier.

At this point, you already know which server is the primary (since the one acting as the secondary gave you the error about the database being read only).  You can check to if you're looking at the primary or the secondary in SSMS by expanding the "Always On High Availability" folder, then the "Availability Groups" folder.  The availability groups listed will either be suffixed with "(Primary)" or "(Secondary)."


Defrag a HEAP by creating clustered index and immediately dropping it

How do I make the DROP wait for the CREATE CLUSTERED to finish?

You don't, it already does. The statements are sequential, i.e. synchronous, one after the other.

What is heap storage? Paul Randal explains

So what you really have is just a stack of paper thrown all over the desk, almost literally. Your operation neatly numbers the pages on the bottom right corner, collates the pages in sequential order, binds them, and then... unbinds and throws them back onto the desk again in a heap.  The total achievement is near zero.  Sure you will regain some usage from tidying up and removing forwarded pages, the data pages may be a tad bit more compact.  Other than that, it's just as messy as you started.


Is there a situation where MySQL creates a nonclustered index automatically?

The PRIMARY KEY is a UNIQUE key; it is also "clustered". (Note: Clustered)
In the absence of an explicit PK, the 'first' UNIQUE key containing only non-NULL column(s) is effectively promoted to be the PK.
If that fails, a hidden 6-byte number is used for the PK.
A UNIQUE key is an INDEX, plus a uniqueness constraint. (Note: may be clustered)
An INDEX is a BTree that is used for efficient access.
A FOREIGN key creates an INDEX if there is not already a suitable index. (Note: Non-clustered)

The 3 "Notes" are implicitly-created indexes, but not necessarily non-clustered.


Why does Postgresql Data directory revert to default after updates/reboots?

After looking up some more details. It seems that the better options is create an override file in /etc/systemd .
Though, I cannot vouch for if this is best practice, I believe that it is a better option since I do not have to edit the default file.
dir=/etc/systemd/system/postgresql-11.service.d
if [ ! -d $dir ]
then
    mkdir $dir
    chmod 755 $dir
fi
cd $dir
touch override.conf
echo '[Service]' >> override.conf
echo 'Environment=PGDATA=/usr/local/pgsql/data' >> override.conf
systemctl daemon-reload
systemctl restart postgresql-11

I know normally this is done via systemctl edit, but from what i can tell, this does the same thing, just without using systemctl. This DID work and it allowed postgres to work even though the data directory in /usr/lib/systemd/system/postgresql-11.service does not have a working data directory in it. If someone has a better answer, I can gladly accept that though.
I arrived at this solution based off
https://pgstef.github.io/2018/02/28/custom_pgdata_with_systemd.html


You need to configure DCOM permissions as DCOM is used for the RPC communications between controller and clients as well as the controller and administration tool. I think this link might help you (under the heading DCOM permissions). msdn

I won't repeat all the info on here since its on the msdn page but following all the steps on there allowed me to get distributed replay working.


Postgres show count 0 for non-matching rows for 1 table

WITH RECURSIVE
cte AS ( SELECT date_trunc('day', now() - interval '5 day') AS period
         UNION ALL
         SELECT period + INTERVAL '1 day'
         FROM cte
         WHERE period < date_trunc('day', now()) )
select
  cte.period,
  count(distinct foo_access.user_id) as n
from cte
LEFT JOIN foo_access on cte.period = date_trunc('day', foo_access.created_at)
                    AND foo_access.user_id > 0
group by cte.period;

fiddle

  I want to see how many different users have accessed foo at least once a day, for the last 5 days.

In my query you'll get 6-day statistic (except while quering at the midnight). You may fix it if needed.


Group by multiple column acquired from join

You can do this using COALESCE. If table1.user_id is null then it will use table2.user_id. If that is null then it uses table3.user_id and so on.

SELECT COALESCE(table1.user_id, table2.user_id, table3.user_id) AS `xx_user_id`
FROM ...table joins...
GROUP BY `xx_user_id`

You won't be able to use the derived xx_user_id column in a WHERE clause however you can use it in a HAVING clause

SELECT COALESCE(table1.user_id, table2.user_id, table3.user_id) AS `xx_user_id`
FROM ...table joins...
GROUP BY `xx_user_id`
HAVING `xx_user_id` = 123;


It is quite simple:

select AIN,
       max(ACCEPT_TYPE) keep (dense_rank first order by date1) ACCEPT_TYPE_1,
       max(ACCEPT_TYPE) keep (dense_rank first order by date1 desc) ACCEPT_TYPE_2,
       min(DATE1) date_1,
       max(DATE1) date_2
  from my_table
 group by AIN


Object Auditing with Availability Groups

No, auditing only reflects the one server that is running the audits. If you want to centralize audit results from multiple servers, you'll need to combine that data yourself.

Keep in mind that if someone really wanted to circumvent this, they'd simply add another replica on any server they want, do the querying they need, and then destroy the replica. An audit on the primary would only capture the fact that a replica was added (and even then, only if you're auditing for those events.)


Good databases for unanchored LIKE condition

One technique is to split the "unanchored" query into two "anchored" parts.

As you've shown a B-Tree search with a trailing wildcard is fast. The problem is you also need a leading wildcard. If you can turn the leading wildcard into a trailing one it would be good. The REVERSE function will help here. The query becomes

WHERE dts.synonym_simple LIKE "abc%"
AND dts.synonym_simple_reversed LIKE "cba%"

To be effective there must be an index on the reversed text. This is an overhead at write-time and will use additional storage. Waiting until read-time would require a scan of the data, which rather defeats the purpose. See examples here amongst others.


SQL Server Linked Server Best Practices - Step by Step

I would start by using Windows Auth and @useself='True', which will use the source server's SQL Server Agent Service Account to make the remote connection.  If you get a login failure, look in the target server's SQL Server Error Log to determine what Windows principal to create a login for on the target server.  So the linked server definitaion would be something like:

USE [master]
GO

EXEC sp_addlinkedserver @server = N'TargetServer', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'targetserver.mydomain.com'

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'TargetServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'collation compatible', @optvalue=N'true'
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'data access', @optvalue=N'true'
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO

Impersonating the caller is more complicated with remote connections to the SQL Server where the Linked Server is defined, as you have to properly configure Kerberos Constrained Delegation.  But when used from a SQL Agent Job, it's not a "double hop" and so is a really simple configuration.


Order by column should have index or not?

Yes, MySQL can use an index on the columns in the ORDER BY (under certain conditions).  However, MySQL cannot use an index for mixed ASC,DESC order by (SELECT * FROM foo ORDER BY bar ASC, pants DESC).  Sharing your query and CREATE TABLE statement would help us answer your question more specifically.

For hints on how to optimize ORDER BY clauses:
http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html

Edit 2012-01-21 8:53AM

There were questions about the source of my statement about using an index with mixed ASC/DESC
in the ORDER BY.  From the ORDER BY Optimization documentation:

  In some cases, MySQL cannot use indexes to resolve the ORDER BY,
  although it still uses indexes to find the rows that match the WHERE
  clause. These cases include the following:

  ...

  You mix ASC and DESC:

  SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

  ...

Also, the DESC keyword is meaningless in CREATE INDEX

  An index_col_name specification can end with ASC or DESC. These
  keywords are permitted for future extensions for specifying ascending
  or descending index value storage. Currently, they are parsed but
  ignored; index values are always stored in ascending order.


Is it good practice to check for existence of records before deleting them

I would just delete.

Even if I had to only delete if there were say exactly 2 records, then I would add this condition to the where clause, like

DELETE FROM table1
WHERE col1='somevalue'
AND (SELECT COUNT(*) FROM table1 WHERE col1 = 'somevalue') = 2;

In all cases when writing a delete statement consider that in general the where clause is usually either

restricting to a single row using a key;
restricting to a set of rows using a flag/code/status column; or
restricting to a set of rows using a range of values -- often dates.


Query stuck in mattermost postgresql communication

The query has already ended 9 minutes ago. PostgreSQL is sitting idle and waiting for the next statement on this connection, which isn't coming.

The block must be somewhere else in your startup process.


updating associated records' tsvectors when the primary record is updated

Make an after-update trigger on "customer" which does a dummy update on "project", which will then fire the trigger on project to do its thing:

CREATE OR REPLACE FUNCTION customer_depend_trigger() RETURNS trigger AS $$
    BEGIN
        update project set customer_id=NEW.id where customer_id=NEW.id;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

create trigger foobar after update ON customer
    for each row execute procedure customer_depend_trigger() ;


One Availability group, two primary nodes

As Aaron Bertrand mentioned:

You need one Windows Server Failover Cluster (WSFC) with both server member of the WSFC.

You need AG1 (Availability Group) and Listener1. DB1 will go in this Availability Group.

You need AG2 (Availability Group) and Listener2. DB2 will go in this Availability Group.

Now you have the option of having both database primary in one server or each database primary on separate server.

You should also upgrade from RTM. There will be no service packs for SQL 2017, and so CUs aren't really optional.  You don't have to install every one, but you need to have a regular schedule of applying them, and for a new solution you should always start with the latest CU. See Announcing the Modern Servicing Model for SQL Server.


Cube processing either takes too long or fails

Oh my dear..

First of all. In your case I would like to suggest you to choose the "Process Default" option. If you are using Process Full always you are dropping all data and objects and recreating all of your stuff. You don't need them. Very often the proposal option is to use ProcessData followed by ProcessIndex.

I didn't proces the cube from within the SSIS, but probably SSIS is checking something and locking something on the database. The best option is to use XMLA. If you want to process your cube from within the SSIS you can write a script task to perform the processing - you can find a scripts on the net.

You need to also check if you have a clean data. Check twice in your project and in your data if you have all the keys and so on. If you are processing separately dimension the data is not comparing between measures and dimensions. I am pretty sure that you have some keys in fact tables that have no corresponding values in your dimension. Make an order with this. If you have no values in dimension and it's correct (doesn't exist in the source tables) you can add to each dimension one extra value with i.e. key "-1" and all values like "N/A" (not applicable) and point the not founded values to this record in the dimensions.

And at the end. If you have a big SSAS database please think about a partitioning.

It's a lot to talk about. If you have some specific questions feel free to ask.


SQL Server Trace - Filter on System Processes

So I actually went and tried this myself.

The "s" suffix in the log is only for display purposes to show that it was a system SPID. It's not actually part of the SPID.

In Profiler, there's an IsSystem column that you can filter on, but otherwise, the system SPIDs show up as per normal (i.e., "spid25s" is simply SPID 25), and you can filter as normal using the SPID column.

I'm not sure what problem you're having with that, as I was able to capture activity on the system SPIDs during my test.

That said, since traces don't persist on instance restart, it might be difficult, or impossible to capture startup activity immediately as the server comes up. You could start a trace by executing a startup stored procedure, or create an Extended Events session that does persist on startup.

While I didn't go to either of those extents, I tried resuming a trace using Profiler while I clicked Yes on the Service Control dialog to start the database engine service. I didn't see any activity at all.

Just because a process (system or otherwise) has activity (disk I/O, CPU, etc.) associated with it doesn't necessarily mean the process is running a T-SQL statement. It's possible there just isn't anything to see and analyze through this channel.

If you're having issues with slow startup, it may be more fruitful to look at the SQL Server error logs to see if there are any long-running operations that stick out.


MySQL error 1066 - Error Code: 1066. Not unique table/alias: 'TAXES'

Because you have 2 times TAXES in your FROM clause (here between **):

FROM TICKETLINES
INNER JOIN PRODUCTS_CAT ON PRODUCTS.ID=PRODUCTS_CAT.PRODUCT
JOIN TAXCATEGORIES ON PPRODUCTS.TAXCAT=TAXCATEGORIES.ID
JOIN **TAXES** ON TAXCATEGORIES.ID=TAXES.ID
LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT=PRODUCTS.ID
LEFT OUTER JOIN TICKETS ON TICKETS.ID=TICKETLINES.TICKET
LEFT OUTER JOIN RECEIPTS ON RECEIPTS.ID=TICKETS.ID, **TAXES**


How Long Would Take to Upgrade an 1T Oracle DB from 10g to 11g?

30-90 minutes according to Oracle's Best Practices for Upgrading.   This is about the closest estimate you will get given all the unknowns in this situation.

The size of the database really matters very little in determining how long the upgrade will take.  Here are the main factors effecting the duration (from the Oracle.com upgrade blog):

Number of installed database components and options - the more components/option gotten installed, the more upgrade scripts will have to be run, the longer it'll take
Valid and non-stale dictionary statistics - even though creating dictionary statistics in some older releases of Oracle wasn't a brilliant idea since desupport of the rule based optimizer the data dictionary should be analyzed. Especially right before an upgrade. Otherwise this will happen during the upgrade while the database is started in a restricted upgrade mode causing additional downtime.
Number of rows in AUD$ if audit_trail is set to DB
Number of synonyms when upgrading from Oracle 9i - synonyms will be touched and get a new dependency in the dictionary in DEPENDENCY$ - if there's a high number (such as 100,000) this can eat up some time
Number of objects in XDB
At a very low rate if COMPATIBLE will be increased: Number of datafiles and the size of the redologs

Here are some additional factors you may want to consider that aren't related to the core of the upgrade itself:

Whether the pre-upgrade script has been run and issues resolved.
How many invalid objects there are.
If the upgrade is being done in place (vs. Import/Export, Streams, Data Guard, etc.)
Whether the DBUA or scripts are used to do the upgrade.
If new Oracle home pre-installed.
Disk speed and throughput.
Other CPU/Disk activity occurring at the same time.
Archive Log Mode.
Other changes being made with the upgrade.
If a cold backup is necessary before and/or after the upgrade.
Any patch sets or one off patches that will also be applied.
How much verification of the upgrade needs to be done before it can be made available.

Probably the biggest factor effecting the upgrade is the unknown factor.  Even when the upgrade is practiced in advance on similar hardware with similar data sets, etc. things can still occur that were unanticipated and can drastically effect the duration.  With that in mind you should mimic the production environment as closely as you can for the test upgrades.  That is, as close as your budget will allow.

If space is the issue preventing you from testing the upgrade, consider restoring the database to a test box excluding some of the larger user tablespaces.  This won't give you an exact feel for the time, but it should give you a closer ballpark and allow you to work through more of the unknowns.