The four properties of a primary key In our dbms class, these were the four properties of a primary key discussed for Oracle.

Unique
Not NULL
Fully functional dependency
Indexed

I understand all the properties except the 3rd one (fully functional dependency). Doesn't unique property ensure this?

No, from uniqueness you cannot deduce full functional dependency.

I you have a relation (=table) with a candidate key(~primary key) P and additional attributes A,B,... than all attributes depend from P because P is the candidate key, but all atrributes depend from the combined superkey PA too (this means the key containing both P and A), but this is not a full functional dependency because they depend on a subset of attributes of PA, on the attribute P. PA is unique and not null but PA is no candidate key.


SQL Replication for Schema changes I want to configure the snapshot replication for all the articles. The problem iam facing is first time all the articles got sync on the subscriber. But later on let suppose if there is a schema change (added new table) in the publisher that newly created table is not getting replicated on the subscriber.

So every time if there a schema changes in publisher i have to manually update the selected articles on the publisher to get it replicated on the subscriber.

Is there a way to automate this step that whenever there is a schema changes at the publisher (added new articles) it should be automatically replicated on the subscriber ? Thanks

Is there a way to automate this step?

Sure.  You can write a proc that uses sp_helparticle to compare the articles with the sys.tables, and then runs sp_addarticle to add articles for the new tables.

But there's no replication setting that will do this for you.


Importing data from file with ID dependencies I have this little conundrum going on: I want to bulk import historical data from multiple CSV files like binance_btc_usd.csv with structure of time|value into table id|time|value where id refers to the identification of what exchange and what currency pair is going on, which is the file name. I dont want to bake-in an ID into the CSV files, as they may not be valid next time I re-run the script. COPY doesn't seem like the thing I am looking for.

CREATE TABLE history_data (
    id    SERIAL PRIMARY KEY,
    pair  INTEGER NOT NULL,
    time  TIMESTAMP,
    value DOUBLE PRECISION,
    CONSTRAINT pair_pair_id_fk FOREIGN KEY (pair) REFERENCES pair (id) ON DELETE CASCADE
);

CREATE TABLE pair (
    id    SERIAL PRIMARY KEY,
    exchange  INTEGER NOT NULL,
    base_coin INTEGER NOT NULL,
    quoted_coin INTEGER NOT NULL
    # and other ...

    CONSTRAINT exchange_currency_id_fk FOREIGN KEY (exchange) REFERENCES exchange (id) ON DELETE CASCADE
    CONSTRAINT base_coin_id_fk FOREIGN KEY (base_coin) REFERENCES currency (id) ON DELETE CASCADE
    CONSTRAINT quoted_coin_id_fk FOREIGN KEY (quoted_coin) REFERENCES (id) ON DELETE CASCADE
    CONSTRAINT pair_pair_id_fk FOREIGN KEY (pair) REFERENCES pair (id) ON DELETE CASCADE
);

The only thing I can think of is a combination of a staging table and some shell scripting.

First step: import the raw file into a staging table, e.g. history_data_import which only contains time and value.

Second step: extract the corresponding values for the pair table from the filename and create a SQL statement like the following:

with pair_id as (
  select id
  from pair
  where exchange = 'binance' --<< this is "generated"
    and base_coin = 'btc' --<< this is "generated"
    and quoted_coin = 'usd' --<< this is "generated"
)
insert into history_data (pair, time, value)
select pi.id, imp.time, imp.value
from history_data_import imp
  cross join pair_id pi;

This assumes that the combination of (exchange, base_coin, quoted_coin) is unique in the pair table so that only a single row is returned when looking up the id.

How exactly you extract the parts of the filename is a different topic and depends on the operating system you are using. On Linux this is probably a piece of cake for awk gurus. But this would better be asked on e.g. https://superuser.com/


You can't really do this unless you are using MySQL 5.7 and the category column is using the JSON datatype. If it is, you can use the JSON_CONTAINS function:
SELECT * FROM `yourtable`
WHERE JSON_CONTAINS(category, '"11"')

Pay attention to the rules of containment:

A candidate scalar is contained in a target scalar if and only if they are comparable and are equal. Two scalar values are comparable if they have the same JSON_TYPE() types, with the exception that values of types INTEGER and DECIMAL are also comparable to each other.

A candidate array is contained in a target array if and only if every element in the candidate is contained in some element of the target.

A candidate nonarray is contained in a target array if and only if the candidate is contained in some element of the target.

A candidate object is contained in a target object if and only if for each key in the candidate there is a key with the same name in the target and the value associated with the candidate key is contained in the value associated with the target key.

We can see the issue with datatypes in this dataset:
CREATE TABLE myjson (
  id tinyint unsigned primary key auto_increment,
  category JSON
);

mysql> SELECT * FROM myjson;
+----+---------------+
| id | category      |
+----+---------------+
|  1 | ["2", "3"]    |
|  2 | ["254", "34"] |
|  3 | ["11", "25"]  |
|  4 | [2, 11]       |
+----+---------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM `myjson` WHERE JSON_CONTAINS(category, "11");
+----+----------+
| id | category |
+----+----------+
|  4 | [2, 11]  |
+----+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `myjson` WHERE JSON_CONTAINS(category, '"11"');
+----+--------------+
| id | category     |
+----+--------------+
|  3 | ["11", "25"] |
+----+--------------+
1 row in set (0.00 sec)


Could not start WAL streaming, replication slot is active I want to set up replication between two of my databases.

One is an Aurora database in PG 10 the publisher, and the other one is an RDS database in PG 10.

But I'm facing an issue, I have 500 GB of data to transfer, so I'm adding table one by one to the replication and waiting for the status to be ready before adding another one.

But after a while, I've got this error: ERROR: terminating logical replication worker due to timeout on the subscriber side. And the worker cannot restart because when it tries to restart the following error appears on the logs: ERROR: could not start WAL streaming: ERROR: replication slot "xxx" is active for PID 25860

After that, the WAL file keeps increasing in the publisher database, and the LSN does not move anymore.

The command I made were quite simple:

CREATE PUBLICATION xxx FOR TABLE xxx;
CREATE SUBSCRIPTION xxx CONNECTION 'xxx' PUBLICATION xxx;

The issue was that the wal_(receiver|sender)_timeout were too low...

I changed the value from one minute to ten minutes and it works.


How to know db size by the backup? in DB2 I want to add validation for free space before restore a db2 database.
I have the *.001 backup file (compressed).

How do I check the estimate db size of that backup?

I can't estimate because the compression ratio is very diverse. db2ckbkp doesn't have that data.

It is relatively complicated to do, but it's possible.

If your database has only DMS tablespaces that contain table data, you can use db2ckbkp -T <backup_file> alone to get this information:

   SYSCATSPACE
              tbspInImage: T

                       ID: 0
                    flags: 7102
                   flags2: 400
              extent_size: 4
            prefetch_size: 8
                  version: 5294
                  flavour: 6
                    state: 0
      statechangeobjectid: 0
        statechangepoolid: 0
                  LifeLSN: 000000000000000C
           LoadPendingLSN: 0000000000000000
          LoadRecoveryLSN: 0000000000000000
                 BeginLSN: 0000000000000000
                   EndLSN: 0000000000000000
               StordefLSN: 0000000000000000
          Full Backup LSN: 0000000FE0EAC00F
          Last Backup LSN: 0000000FE0EAC00F
         Full Backup Time:  519BCFCE = "20130521124934"
         Last Backup Time:  519BCFCE = "20130521124934"
               TotalPages: 87824
             UseablePages: 87816
              reorgPoolID: 0
               reorgObjID: 0
           poolReorgCount: 0
          # of containers: 2
            current_group: 0
                cont_csum: 1311788850
      current_map_entries: 1
                page_size: 4096
                 map_csum: 4294967292
     tsp rfwd encountered: 16

            Container CB
                             Type: 6
                       TotalPages: 43912
                      UsablePages: 43908
               # of OS rsvd bytes: 512
                    Page 0 offset: 16384
                       Tag offset: 512
                    Extent offset: 0
                             Name: /db2/data1/db2inst1/NODE0000/SAMPLE/T0000000/C0000000.CAT

            Container CB
                             Type: 6
                       TotalPages: 43912
                      UsablePages: 43908
               # of OS rsvd bytes: 512
                    Page 0 offset: 16384
                       Tag offset: 512
                    Extent offset: 0
                             Name: /db2/data2/db2inst1/NODE0000/SAMPLE/T0000000/C0000001.CAT

Using TotalPages and page_size from the main section of the output, you can calculate the total space. (You can ignore the Container CB sections that give details for each container in the tablespace).

If you have SMS tablespaces that store table data (i.e. not just system- or user-temporary tablespaces), you'll see tablespaces in this output that show TotalPages: 0.

To calculate the amount of data used by these SMS tablespaces, you will need to dump out all of the information from the backup image using db2ckbkp -a. You will get lines like this:

BufAddr  PoolID Token Type     Offset   FileSize ObjectSize   OrigSize Object Name
-------- ------ ----- ---- ---------- ---------- ---------- ---------- -----------
00000000:     0    10   16       1042     167680     107757     524288 "Tablespace: 0, Table: SQL00010"

OrigSize shows the amount of uncompressed data in this buffer. The line above shows data from an SMS tablespace;  DMS tablespaces will also show lines like this, but the ObjectType will show DMS.TABLESPACE.DATA.

So, if you look for lines where ObjectType starts with "Tablespace:, you can add up the OrigSize of these lines, and find the additional storage space that all SMS tablespaces will use.


Why doesn't jsonb constructor from a NULL record behave like hstore constructor? When I construct an hstore

CREATE EXTENSION hstore;
SELECT hstore(null::my_type);
                     hstore
------------------------------------------------
 "name"=>NULL, "street"=>NULL, "location"=>NULL
(1 row)

While the jsonb method,

SELECT to_jsonb(null::my_type);
 to_jsonb
----------

(1 row)

This creates a surprising effect too when you try to merge (||) another like type,

SELECT hstore(null::my_type) || hstore('name', 'Evan');
                     ?column?
--------------------------------------------------
 "name"=>"Evan", "street"=>NULL, "location"=>NULL
(1 row)

SELECT to_jsonb(null::my_type) || jsonb_build_object('name', 'Evan');
 ?column?
----------

(1 row)

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')
);


What is the fastest way to clone/copy oracle databases on the same machine? I want to clone oracle databases on the same machine and
I need to change user etc. from e.g. PROD to DEV.
For that I use expdp/impdp, but this tends to be slow with increasing database size.
I need to do this on linux and windows machines.

Is using expdp/impdp the fastest way to do this?

Or should I use a completly different way of doing this?

rman is the tool for this.

https://docs.oracle.com/cd/E11882_01/backup.112/e10642/toc.htm

There is a learning curve, but once configured, it's much faster than export/import.


Select rows based on latest date with multiple joins I have this query (SQLFiddle):

SELECT
c.name,
a.user_id,
a.status_id,
a.title,
a.rtime,
u.user_name,
s.status_name

FROM company c

LEFT JOIN action a ON a.company_id=c.id
LEFT JOIN user u ON u.id=a.user_id
LEFT JOIN status s ON s.id=a.status_id

WHERE u.user_name='Morgan'

-- WHERE c.name='Fiddle'

GROUP BY c.id

HAVING a.rtime IS NULL OR a.rtime = (
 SELECT max(rtime)
 FROM action a2
 WHERE deleted IS NULL
 AND a2.company_id = c.id
 )

Problem 1

I want to list all the companies, and show the user and status where they last made an action on the company. At the same time show the companies where no actions have been made.

Problem 2

I also need to be able to search for the user by name, thereby selecting all companies were this user had the last activity. The query is made from a form, so I can inject variables.

I am not able to change the database SCHEMA at the moment, but advices for a future migration is much appretiated.

I've tried binding it together with INNER JOIN ( SELECT.. ) t ON but I can't get my head around it.

I've also tried methods from
here, here, and here but I can't get the person with the latest activity right.

MySQL version: 5.5.16.
The company table has about 1mill rows, and the action table is at 70K, growing. Performance is important to me here.

How can this be solved?

Your query can be simplified to:

SELECT
    c.id,
    c.name,
    a.rtime,
    s.status_name,
    u.user_name
FROM company c
    LEFT JOIN
      ( SELECT
            company_id,
            MAX(rtime) AS maxdate
        FROM action
        WHERE deleted IS NULL
        GROUP BY company_id
      ) AS x ON x.company_id = c.id
    LEFT JOIN action a ON  a.deleted IS NULL
                       AND a.company_id = x.company_id
                       AND a.rtime = x.maxdate
    LEFT JOIN user u ON u.id = a.user_id
    LEFT JOIN status s ON s.id = a.status_id
WHERE
    c.name LIKE 'Company%' ;

An index on (deleted, company_id, rtime) would make the derived table subquery efficient. I suppose you already have indexes on the columns used for the joins and on the Company (name).

SQL-Fiddle


Help me define the tables in this scenario We have 2 tables setup like this:

WEB_GK_STATE
    PRG_CODE    Varchar2(5)
    STATE       Varchar2(2)

WEB GK
    PRG_CODE    Varchar2(5)
    TEXT        Varchar2(4000)

Here is some sample data:

INSERT INTO WEB_GK_STATE (`PRG_CODE`, `STATE`) VALUES ('PA', 'OL');
INSERT INTO WEB_GK_STATE (`PRG_CODE`, `STATE`) VALUES ('RC', 'TX');
INSERT INTO WEB_GK_STATE (`PRG_CODE`, `STATE`) VALUES ('PA', 'TX');

INSERT INTO WEB_GK (`PRG_CODE`, `TEXT`) VALUES ('PA', 'THIS IS THE GK FOR PA');
INSERT INTO WEB_GK (`PRG_CODE`, `TEXT`) VALUES ('RC', 'THIS IS THE GK FOR RC');

We use this Oracle function to retrieve the values:

CREATE OR REPLACE FUNCTION MAIN.F_GET_GK_TEXT (P_PRG_CODE IN MAIN.WB_GK_STATE.PRG_CODE%TYPE
                                             , P_STATE IN MAIN.WB_GK_STATE.STATE%TYPE)
    RETURN varchar2 IS V_STR varchar2(2000);
BEGIN
    for recs in (select TEXT from wb_gk_state a, WB_GK b
                 where state=p_state and a.prg_code=p_prg_code
                       and a.prg_code=b.prg_code) loop
           V_STR:= recs.text;
    end loop;
    return V_STR;
END;
/

Now, our dilemma is that we want to be able to provide a different WEB_GK TEXT for the PRG_CODE 'PA' WHERE STATE = 'TX'

If we do it the way we have now, the WEB_GK TEXT will show the same for both OL and TX because we key on PRG_CODE.

My first thought would be to have a STATE column in WEB_GK and get rid of the WEB_GK_STATE table altogether,
but that means we would have to write 50 records to support the same WEB_GK for 50 states.

How could I do this with minimal disruption of our code and modification of our tables?

My answer assumes that PRG_CODE is the primary key of both tables..  (or PRG_CODE + STATE is pkey of WEB_GK_STATE .. but PRG_CODE for WEB_GK)

My suggestion would be to redefine the PK of your WB_GK_STATE table..

WEB_GK_STATE
    WEB_GK_STATE_ID INTEGER PRIMARY KEY
    PRG_CODE    Varchar2(5)
    STATE       Varchar2(2)

Then alter your other table's PK as well.

WEB_GK
    WEB_GK_STATE_ID    INTEGER REFERENCES WEB_GK_STATE (WEB_GK_STATE_ID)
    TEXT        Varchar2(4000)

But that's only one possible solution.

Naturally, there would be several steps required to achieve this - this is simply what the end version of the tables would look like..

The advantage is that the insert/select from WEB_GK_STATE would be pretty much unaltered (assuming you have a trigger to give the WEB_GK_STATE_ID a valid value). Your stored procedure would have to be altered (minimally), and any SQL in the code that looked at the two tables would need to be reviewed and have it's JOIN condition (if there is one) altered.. I don't think that would be much work, though.

EDIT

Given further information, instead of changing your existing two tables, I would add a third..

WEB_GK_STATE_OVERRIDE
    PRG_CODE    Varchar2(5)
    STATE       Varchar2(2)
    TEXT        VARCHAR2(4000)

You would then need to edit your MAIN.F_GET_GK_TEXT function to first look at the WEB_GK_STATE_OVERRIDE function and see if an entry exists for PRG_CODE/STATE .. if so, return the TEXT value .. if not, return the TEXT value recorded in the WEB_GK table.

But your existing design and this one will be fragile.. They will work, but it's very much a bandaid instead of an actual good design that will be flexible in the long run.


An INSERT statement triggers the SQL Server Audit to write it down in the logs although INSERT is not listed as an Audit Action Background:
I am trying to keep a track record of what users are querying in the database. One option I found is to create a Server and Database Audit Specification. I did not want to include INSERT statements since those are going to be logged for each record inserted which will be a lot if there are bulks of data being added to the table(s).
Problem:
We have a SQL Job (within the Job Agent) that inserts data into a table using an INSERT query. I would expect these logs would not appear in our .sqlaudit file but it does. Even do INSERT is not apart of our Actions within our Database Audit Specifications. I am wondering if it is triggered do to another action? I have listed all the actions that I have included but from my understanding none of them will trigger the server to write the INSERT logs in the .sqlaudit file. What could be the issue?
NOTE: I have also executed an INSERT query using the query tool and it still logs that as well.
Audit Action Types Added to Database Audit Specifications:

DATABASE_OBJECT_ACCESS_GROUP
SCHEMA_OBJECT_ACCESS_GROUP
DATABASE_OBJECT_CHANGE_GROUP
SCHEMA_OBJECT_CHANGE_GROUP
DELETE
EXECUTE
SELECT
UPDATE

Here is the statement that it is logging in the .sqlaudit file:
INSERT [dbo].[table_name]([a],[b],[c],[d],[e],[f],[g],[h],[i],[j],[k],[l]) VALUES(@Param000004,@Param000005,@Param000006,@Param000007,@Param000008,@Param000009,@Param000010,@Param000011,@Param000012,@Param000013,@Param000014,@Param000015)

The optimal approach would be to disable the INSERT audit for that table, but since you seem to have a peculiar situation, here's one way of dealing with it:
Once you made sure the INSERT audit is not enabled by connecting to the audited database and running this query:
-- List enabled database specifications
SELECT  a.audit_id,
        a.name as audit_name,
        s.name as database_specification_name,
        d.audit_action_name,
        s.is_state_enabled,
        d.is_group,
        s.create_date,
        s.modify_date,
        d.audited_result
FROM sys.server_audits AS a
JOIN sys.database_audit_specifications AS s
ON a.audit_guid = s.audit_guid
JOIN sys.database_audit_specification_details AS d
ON s.database_specification_id = d.database_specification_id
WHERE s.is_state_enabled = 1

You can filter that audit to avoid the inserts from being registered. The Create a Server Audit and Server Audit Specification doc says:

Optionally, on the Filter page, enter a predicate, or WHERE clause,
to the server audit to specify additional options not available from
the General page. Enclose the predicate in parentheses; for example:
(object_name = 'EmployeesTable').

The Filter page can be found by right click the audit > properties. If you prefer, the T-SQL to alter the audit not to register the inserts would be something like this:
USE [master]
GO

ALTER SERVER AUDIT [Your_Audit_Name]
WHERE (NOT [statement] LIKE 'INSERT%')
GO

Some relevant content:
Filter SQL Server Audit on action_id / class_type predicate
sys.fn_get_audit_file (Transact-SQL)
How to analyze and read SQL Server Audit information


Which way should be selected when move from mysql to mariadb? Now, the mysql version is 5.1.I plan to move to mariadb 10.2.Use mysqldump and source to update databases.
Based on MySQL Upgrade Strategies,

  Upgrading to the latest release is recommended before upgrading to the
  next version

.
Way 1:mysql 5.1->mysql 5.7->mariadb 10.2
Way 2:mysql 5.1->mariadb 5.1->mariadb 10.2
Which way is better(less data loss)?

We can't do anything without a problem except point you to the official information,

How to migrate from MySQL to MariaDB on Linux in five steps
Migrating to MariaDB

Generally the further you are from an original fork the more complexities you'll encounter in the migration. Therefore the earlier the version of MySQL the fewer features of MySQL that are MySQL-specific that you'll have to contend with. Upgrading to a newer of MySQL before the migration to MariaDB is likely to make your job harder.


Confused over table_open_cache I've been reading about the table_open_cache and I'm slightly confused about how big it should be, I've seen conflicting advice. My DB is running on MyISAM and has 199 tables.

I ran into an issue months ago whereby the server was running out of file descriptors, so I've increased the number of file descriptors to 2500 (from default), and the table_open_cache to 1000, and max connections to 500 (probably too high).

I've read this article on negative scalability (https://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/) and now I'm wondering if by increasing these values, performance has been impacted.

I've also ran MySQLTuner and my table cache hit rate is 0%, it shows 1K tables open vs 1M opened. I'm also only using 47% of available the available file limit.

Could anyone point me in the right direction?

Yes, increase table_open_cache.

Are you using Windows?  Or *nix?  (You are in an area where the OS matters.)

Keep in mind that each MyISAM table involves 3 files.

What is Max_used_connections?  That should give you a clue of what max_connections can be lowered to.


Will mariadb use mutliple cores at all? Good day.  We have an 8 core server for mariadb 10.1 on Centos 7. IT's a production database as well as a replication master. I am not sure if it's using more than one of the cores.

We also have a one-core virtual server acting as a slave.

Searching on this tells me that most queries are about using more than one core per query, which are repeatedly answered with NO, that's not possible.  I get that.

I just want to know if multiple queries can be apportioned out to multiple cores, whether configuration settings are required to do this, and how to tell if it's working.

I also need to know whether slave throughput could be improved by adding cores.

Thanks,

Ed Greenberg

The short answer is yes. It will use a thread per connection by default which you can delegate queries to concurrently, which will utilize multiple cores on the operating system level.

Slave throughput can be dramatically improved by adding cores using parallel replication which is not enabled by default. https://mariadb.com/kb/en/library/parallel-replication/


SQL Server: Should I overwrite existing file for additional backups? In the SQL Server 2012 wizard to backup a database, I previously created a 'full' backup.

Now I want to create a differential backup. However, the destination specified the same file name and I'm not sure what will happen when creating a new backup to that file in terms of restoration.

ie.

Will this append to the same file?

Will the full backup be replaced by the differential file? (Obviously this is not helpful)

Will something else happen?

we can do both append as well overwrite while taking backup .

Please find the link:https://technet.microsoft.com/en-us/library/ms191211(v=sql.105).aspx


Modify an Oracle Dataguard destination database I'd like to set up some Oracle triggers to take action after rows are inserted, but I can't do this on our production database because it's controlled by a vendor. One alternative I was looking at is doing Oracle Dataguard replication to a secondary database somewhere, and setting up triggers there to take my action. However, I've gotten the impression that I can't modify the Dataguard destination in any way, even if my triggers won't have impact on the data at all (they're all AFTER triggers).

Can I use dataguard in this way? If I can't, are there are other alternatives to Dataguard, like maybe Oracle streams? Some other type of replication?

If you setup a logical standby, then you can change the data in the logical standby. You will probably have trouble supporting the kind of change that you seem to want. Have you thought about talking to the vendor to see if they can add the logic that you want to the product you pay them for? If the vendor makes the change, then they will/should support it. You can create materialized views in a different schema and add whatever functionality that you want to the materialized views. You then just need to refresh them when needed.


DB2 Trigger: only update current row I've created following trigger :

CREATE TRIGGER probeer2
  AFTER INSERT ON libtn01leb.kofax_release
  FOR EACH ROW
  UPDATE libtn01leb.kofax_release SET RRNR='bleh'

This trigger will update EVERY ROW in the table, instead of the row I'm currently adding.
How can I make it only be valid for the row I'm inserting ?

Your UPDATE statement is wrong. It is updating all the records in the table because it doesn't have any WHERE clause. If you want to update only the current row, you need to use the updated/inserted records (like in Jack's example: REFERENCING NEW AS N_ROW and use it as filter).


Order of where clause evaluation If I have a MySQL table which has content from the last 3 months I actually do the following:

SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
FROM `mDelivered`
where _campaign = 2
  and `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f'
  and timeCreated > NOW() - INTERVAL 1 HOUR
group by `_campaign`, `session`

Now I think it first checks all elements where _campaign=2, is that right? Then it checks where session = ... and then it checks where _timeCreated > ....

How can I make the select faster?
Is it better to reduce the size by changing the order of the element like this:

SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
FROM `mDelivered`
where  timeCreated > NOW() - INTERVAL 1 HOUR
  and _campaign = 2
  and `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f'
group by `_campaign`, `session`

Or is it better to:

SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
from (
  SELECT *
  FROM `mDelivered`
  where  timeCreated > NOW() - INTERVAL 1 HOUR
)
WHERE _campaign = 2
  and `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f'
group by `_campaign`, `session`

or how should this be done to be faster?

See this answer. No, the order you put the conditions in the WHERE clause doesn't matter: In MySQL, does the order of the columns in a WHERE clause affect query performance?

If id is not nullable, you can replace the count(id) with count(*) and  try adding an index on (_campaign, session, timeCreated).

ALTER TABLE mDelivered
  ADD INDEX campaign_session_timeCreated_IX        -- choose a name for the index
    (_campaign, session, timeCreated) ;

And you don't need the GROUP BY since you are already restricting the rows to fixed _campaign and session values. Try this:

SELECT count(*) cnt
FROM mDelivered
WHERE _campaign = 2
  AND `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f'
  AND timeCreated > NOW() - INTERVAL 1 HOUR ;


What should be the fill-factor for a table where only fixed-width columns will be updated? We have a table where we are planning to update timestamp fields only.

In the first import these columns will be nulls then we will update them as the computation finished on these rows.

The question is; should we set a fill-factor lower than 100 for this table or PostgreSQL will allocate a fixed size for these columns so lowering the fill-factor makes no sense?

Postgres doesn't store nulls directly in the field but rather keeps a bitmap of null values.
So using 100 will be problematic.

What you can do is use some default value such as 1/1/1970 just to allocate the spot for the field.


Transaction log reported full but is not Batch jobs error emails report:

  The transaction log for database 'WebDB' is full.

No, it isn't. Its 90% free. The transaction log backups run normally. My hardware guy tells me the hardware is running perfectly, with plenty of space. SQL Server 2008 SP3, but not R2.

I'm a developer, we no longer have a DBA. So it falls to me to try and fix this.

In all probability you had a large transaction that filled up your transaction log.  When the transaction failed it rolled back.  This cleared up your transaction log.

If that particular transaction needs to be processed then you can either

Modify your log size and max log size - This is best if this is a regular process.  Running a transaction log backup right afterwards is not a bad idea either.
Change the code to reduce the amount of transaction space needed.  This could be by using a minimally logged operation or by doing several smaller transactions (with a transaction backup in the middle).


The choices are more for your convenience, not the database's ability to handle it.

Here's another:

+---------------+--------+------------+------------+
|  DESCRIPTION  | AMOUNT | COMMİSSİON | VALUE DATE |
+---------------+--------+------------+------------+
| INSTALLMENT   |    100 |          1 | 01.03.2018 |
| INSTALLMENT   |    100 |          1 | 01.04.2018 |
| INSTALLMENT   |    100 |          1 | 01.05.2018 |

This has the advantage of tying the AMOUNT and COMMISSION together.


Performance difference between MySQL and PostgreSQL for the same schema/queries I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS.

I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests comparing with MySQL too. We observe that MySQL is twice as fast as PostgreSQL.

I did a tangible performance test:

Same columns in table with equivalent column datatypes.
Same number of rows.
Same indexes in both (primary key included).
The CPU load are idle and Postgres machine it's significantly better.
And the same query (obviously).

What am I doing wrong?

P.S: I read many "howtos" on performance tuning for database engines.
P.S(2): We're using InnoDB (one file per table) on the MySQL database.

Hi Mat!

I did the three common select (and hardest) queries.

The question about disk, certainly it's not the same; In Postgres it's a SSD (almost three time fastest).

MySQL cache data:

+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 16777216             |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| table_definition_cache       | 256                  |
| table_open_cache             | 64                   |
| thread_cache_size            | 8                    |
+------------------------------+----------------------+

I don't know how to view this in PostgreSQL.

Thanks in advance.

MySQL and PostgreSQL are quite difference performance-wise.  InnoDB and PostgreSQL tables are optimized for different sorts of queries.  Understanding these differences is important to understanding how to get good performance out of either.

As an example, let's look at the most obvious difference.

PostgreSQL vs MySQL/InnoDB Table Structure and What This Means for Performance

In general, on complex work-loads, PostgreSQL will be faster, but on simple primary key lookups MySQL with InnoDB will be faster.

PostgreSQL tables are heap tables.  There is no option to build a table which is not a heap table.  The cluster command simply rewrites the heap ordered by a specified index.  Indexes then provide heap locations for tuples with various values.  Indexes cannot be traversed in physical order, only logical order so they have a lot of random disk I/O while reading a table sequentially usually means a lot of sequential disk I/O, since you can read a table in physical order.  Sequential disk I/O gets to use read-ahead cache and some other OS-level optimization.

What this means is that if you need a significant portion of records or over a few pages, it is usually faster to just read the pages from disk.  On the other hand, a primary key lookup for a table requires hitting the index, lookup up the location in the file then hit the heap table and pull the record.  This means a number of pieces of random disk I/O.

InnoDB uses a different approach.  With InnoDB, the table is a b-tree index with the actual data in the index payload.  This means that a primary key lookup already gets to pull the data from the leaf page, and so less random disk I/O is required for this.  At the same time, an index scan requires traversing two indexes instead of one, meaning use of any index other than the primary key ends up being slower and sequential scans are slower still.

Getting Diagnoses in PostgreSQL

I think you want to use something like:

 EXPLAIN (analyse, buffers, verbose)
 [query];

That will give you the query plan, initial estimates, actual times, buffer usage, and much more.


Can this query be improved? view for few count totals on a table I am not a dba but I am trying to create an effecient query for a view that will return some totals on a table

select
 (select count(1) from alerts) as Total,
 (select count(1) from alerts where iscomplete is null or iscomplete = 0) as Active,
 (select count(1) from alerts where iscomplete = 1) as Complete

Use CASE so that only one full scan is needed. An non-clustered index on iscomplete will cover this query.

SELECT
      COUNT(1) AS Total
    , SUM(CASE WHEN iscomplete is null or iscomplete = 0 THEN 1 ELSE 0 END) AS Active
    , SUM(CASE WHEN iscomplete = 1 THEN 1 ELSE 0 END) AS Complete
FROM dbo.alerts;


How do I insert record only if the record doesn't exist? I have a PaymentInformation table

ID   NAME       Start       End
1    Tina       1/7/2014    1/17/2014
2    Alice      3/7/2014    3/17/2014
3    John       5/7/2014    5/17/2014
4    Michelle   7/7/2014    7/17/2014

I'm building my SQL query dynamically, like this:

SQLiteDataBaseQueryHolder3 = "INSERT INTO " + SQLiteHelper.TABLE2_NAME + "(" +
                            "name, Start, End" +
                            ")VALUES('" +
                            tempName + "'" +
                            ",'" +start + "'" +
                            ",'" + end + "" +
                            "')" +
                            " WHERE NOT EXISTS ( SELECT * FROM " +SQLiteHelper.TABLE2_NAME +" WHERE name = '"+
                            tempName+"' AND Start = '"+Start+"')"

Which gives the following result (formatted for readability):

INSERT INTO PaymentInformation(NAME, Start, End)
VALUES('Tina','01/10/2017','2/10/2017')
WHERE NOT EXISTS (
    SELECT *
    FROM PaymentInformation
    WHERE name = 'Tina' AND duration_start = '01/10/2017'
)

Is this how it should be done?
It shouldn't insert if Tina is already in the table with a Start day of 01/10/2017.

You can do this with a unique constraint & insert or ignore.

Create table:

ironforge:tmp phil$ sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table PaymentInformation
   ...> (
   ...> ID int,
   ...> name varchar(100),
   ...> start date,
   ...> end date );
sqlite>

Add a unique constraint on name and start:

sqlite> create unique index PaymentInformation_name_start on PaymentInformation ( name, start ) ;

Add a row:

sqlite> insert or ignore into PaymentInformation values ( 1,'Phil','2017-01-01','2017-02-02' );
sqlite> select * from PaymentInformation;
1|Phil|2017-01-01|2017-02-02
sqlite>

Add another row with the same name & start date, and it gets ignored:

sqlite> insert or ignore into PaymentInformation values ( 2,'Phil','2017-01-01','2017-02-02' );
sqlite> select * from PaymentInformation;
1|Phil|2017-01-01|2017-02-02
sqlite>


Database Design - multiple tables I am trying to build a database that has all of our employees, their skills, and the level of their skills. The idea is that a manager could go in, type in a skill, and relevant employees are returned.
I am finding that I am getting stuck on PK's and FK's - and really  how to go about it altogether.

So far, I believe I need to create 3 tables (Employees, Skills that will be separated into 4 general areas, and Skill Level which will be 5 different options).

With that, I am at a loss for how I can connect them in a way that each employee can be attached with multiple skills that also shows at what level they're at.

Not necessarily looking for someone to do the work for me, but if someone can clarify whether or not this logic will work and how to get on track - I probably can research the sql code for it.

Thanks!

---EDIT----
Someone raised a good point. A many-to-many relationship seems like the best, but the skills table is something I am not understanding.

If I create a table/entity [Skills] that has the four attributes (financial, technology, creative, other) with specifics listed in each column (i.e. financial would have accounting, recon, etc. and so on), would the skill level be another table?
OR
Would there be separate tables for the four skills and the specifics listed as columns with the skill levels under each?
I'm going crazy trying to figure out the best way to go out this.

I hesitate to recommend this answer to a self described novice, but it seems to fit the case pretty well.

If were working this problem, I would go with a star schema approach. There would be three dimension tables:  Employees, Skills, and Levels.  And there would be one fact table, with foreign keys to all three tables, and no other columns (aka fields).

This particular fact table only conveys a count, and no other measure.

I might go ahead and make the hierarchy of skills a snowflake off of the skills dimension.

If you are new at database design none of this would be familiar to you at all.  When people begin to work with star schemas,  they usually have a few years of database design under their belt.  But just in case you have access to a good tutorial on star schema,  you might want to follow through here.

In addition, if I could fit it into the budget, I would want to buy some sort of business intelligence software that would allow the managers could use to browse this data without learning the table structure.  Failing in that,  I would just try to build in the kind of drill down capabilities the managers need into my app.

I hope all of this isn't mystifying.  Don't worry if it is.  Basically the fact table is capturing a many to many to many relationship,  which probably isn't covered in your learning materials.


Incremental column depening on column value I would like to have a column which has an incremental integer depending on the column value after sorting the table.

E.g. after sorting by state:

Nr  | State      | Counter
---------------------------
234 | Alberta    | 1
534 | Alberta    | 2
111 | Alberta    | 3
123 | California | 1
876 | New York   | 1
101 | New York   | 2
542 | New York   | 3
533 | New York   | 4

Is this even possible with MySQL?

Just found the solution after banging my head into the wall:

SELECT
  t.*,
  @row := IF(@lastState = t.State, @row + 1, 0) as counter,
  @lastFunkt := t.State
FROM
  table t,
  (SELECT @row := 0,
          @lastState := '') SQLVars


I have inserted your values into a table variable just to so I could pivot from it later.

The code came out like this:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW

declare @t table(srv nvarchar(128),dates date,[count] int)

insert into @t (srv,dates,[count])
select 'SRV1',cast ('03/25/2018' as date) ,1   union all
select 'SRV1',cast ('04/06/2018' as date) ,59  union all
select 'SRV1',cast ('04/07/2018' as date) , 9  union all
select 'SRV1',cast ('04/13/2018' as date) , 1  union all
select 'SRV1',cast ('04/15/2018' as date) , 1  union all
select 'SRV1',cast ('04/20/2018' as date) , 4  union all
select 'SRV1',cast ('04/21/2018' as date) , 2  union all
select 'SRV1',cast ('04/27/2018' as date) , 5  union all
select 'SRV1',cast ('04/28/2018' as date) , 5  union all
select 'SRV1',cast ('04/29/2018' as date) , 1  union all
select 'SRV1',cast ('04/30/2018' as date) ,62

select *
from (

       select srv
              ,dates
              ,[count]
          from @t

) radhe
pivot (sum([count]) for dates in (
  [2018-04-01],
  [2018-04-02],
  [2018-04-03],
  [2018-04-04],
  [2018-04-05],
  [2018-04-06],
  [2018-04-07],
  [2018-04-08],
  [2018-04-09],
  [2018-04-10],
  [2018-04-11],
  [2018-04-12],
  [2018-04-13],
  [2018-04-14],
  [2018-04-15],
  [2018-04-16],
  [2018-04-17],
  [2018-04-18],
  [2018-04-19],
  [2018-04-20],
  [2018-04-21],
  [2018-04-22],
  [2018-04-23],
  [2018-04-24],
  [2018-04-25],
  [2018-04-26],
  [2018-04-27],
  [2018-04-28],
  [2018-04-29],
  [2018-04-30])) as krishna
where 1=1

there are things that could be changed, but that is all up to you,
plus if you want to get rid of the nulls and replace them by zeros
please have a look at the following question and the answers at:

How to replace (null) values with 0 output in PIVOT

And that will also give you some ideas as how to produce all this in a dynamic way.


Sql Server 2005 Copy Only backup I have a database in Sql Server 2005. The database is configured to be backed up as Full (sunday) and Differential (weeknights) on a mediaset at a particular path.

When I run the command:

BACKUP DATABASE COB
TO DISK = ’C:BackupsCOB.bak’
WITH COPY_ONLY;

to backup the database to a different path, does that break the pre-configured backup sequence. I know that usually people run the copy_only backup without changing the backup sequence, but I was unsure what would happen if I changed the path. Still Learning. Any help will be much appreciated!

When you run a backup with copy_only, it does not interfere with the full/differential backup sequence.

You can think of a with copy_only backup as it never happened.


Is there an easy way to have a non-IDENTITY, auto incrementing column based on another column? +----+-------------+-----------------+-------------+
| ID | ProductName | ProductCategory | ProductCode |
+----+-------------+-----------------+-------------+
|  1 | Item 1      |               1 |           1 |
|  2 | Item 2      |               1 |           2 |
|  3 | Item 3      |               2 |           1 |
|  4 | Item 4      |               3 |           1 |
|  5 | Item 5      |               1 |           3 |
|  6 | Item 6      |               2 |           2 |
+----+-------------+-----------------+-------------+

Essentially what I am trying to do is have a column autoincrement but based on the last row in the same ProductCategory.

Currently I have a stored procedure that does the following

SELECT TOP 1 @NextProductCode = ProductCode + 1
  FROM Products
 WHERE ProductCategory = @ProductCategory

This works, but despite the fact that this is a pretty low traffic database there's an obvious concurrency issue here. I could throw a TABLOCKX in the stored procedure I guess but I'm curious if there's a more simple way to accomplish this.

I'm also trying to avoid dynamic SQL otherwise I could create a sequence for each new ProductCategory.

If I am Understanding you correctly, you are looking to select a group of product that's already in a DB, and assign a new Product ID to each Product that is Unique to each Product Category, if this is correct then you can add this to your SELECT statement,

ROW_NUMBER() OVER (PARTITION BY ProductCategory ORDER BY ID) AS ProductCode

it should do what you are looking for.


Getting ORA-32042 even though i can create the view in SQL Developer I have a strange problem creating a materialized view using the code generated by the export wizard.

I have created a materialized view and pasted following code into the sql query tab from the popup. Everything works fine and the view is there. But when i try to run this code in a simple sql query editor and press run script i get the following error message.

Database: 12c Release 1
Error code: ORA-32042
Description: recursive WITH clause must reference itself directly in one of the UNION ALL branches

What have i done wrong?

CREATE MATERIALIZED VIEW axchannel_customer_category_hierarchy_discount_mview (
    "CHANNEL_ID",
    "CATEGORY_ID",
    "CATEGORY_PARENT_ID",
    "CATEGORY_REC_ID",
    "CATEGORY_NAME",
    "CUSTOMER_ID",
    "DISCOUNT_PERCENT",
    "DISCOUNT_ORIG",
    "LVL"
) AS
    WITH prejoined_discounts AS (
        SELECT
            axchannel_category_hierarchy.*,
            axchannel_customer.id   AS customer_id,
            axchannel_customer.customer_nr,
            axchannel_customer_category_discount.discount_percent
        FROM
            axchannel_category_hierarchy
            CROSS JOIN axchannel_customer left
            JOIN axchannel_customer_category_discount ON axchannel_category_hierarchy.channel_id = axchannel_customer_category_discount
            .channel_id
                                                         AND axchannel_category_hierarchy.id = axchannel_customer_category_discount
                                                         .category_id
                                                         AND axchannel_customer_category_discount.customer_id = axchannel_customer
                                                         .id
        WHERE
            axchannel_category_hierarchy.channel_id = axchannel_customer.channel_id
    ),foo (
        channel_id,
        id,
        parent_id,
        category_rec_id,
        category_name,
        customer_id,
        discount_percent,
        discount_orig,
        lvl
    ) AS (
        SELECT
            c.channel_id,
            c.id,
            c.parent_id,
            c.category_rec_id,
            c.category_name,
            c.customer_id,
            c.discount_percent,
            c.discount_percent   AS discount_orig,
            1 AS lvl
        FROM
            prejoined_discounts c
        WHERE
            c.parent_id IS NULL
        UNION ALL
        SELECT
            c.channel_id,
            c.id,
            c.parent_id,
            c.category_rec_id,
            c.category_name,
            c.customer_id,
            CASE
                WHEN c.discount_percent IS NULL THEN foo.discount_percent
                ELSE c.discount_percent
            END discount,
            c.discount_percent   AS discount_orig,
            lvl + 1
        FROM
            foo
            JOIN prejoined_discounts c ON c.channel_id = foo.channel_id
                                          AND c.parent_id = foo.id
                                          AND c.customer_id = foo.customer_id
    )
    SELECT
          channel_id,
    id as category_id,
    parent_id as category_parent_id,
    category_rec_id,
    category_name,
    customer_id,
    discount_percent,
    discount_orig,
    lvl
    FROM
        foo;

Interesting. It works without the column alias in the mview definition.

with
  bar as (select * from dual),
  foo(lvl) as
  (
    select 1 as lvl from bar
    union all
    select foo.lvl + 1 from foo join bar on (foo.lvl < 5)
  )
select lvl from foo;

       LVL
----------
         1
         2
         3
         4
         5

Create mview fails with column alias in definition:

create materialized view mv1(lvl) as
with
  bar as (select * from dual),
  foo(lvl) as
  (
    select 1 as lvl from bar
    union all
    select foo.lvl + 1 from foo join bar on (foo.lvl < 5)
  )
select lvl from foo;

Error report -
ORA-12018: following error encountered during code generation for "BP"."MV1"
ORA-32042: recursive WITH clause must reference itself directly in one of the UNION ALL branches
12018. 0000 -  "following error encountered during code generation for \"%s\".\"%s\""
*Cause:    The refresh operations for the indicated materialized view could
           not be regenerated due to errors.
*Action:   Correct the problem indicated in the following error messages and
           repeat the operation.

But it works without the column alias:

create materialized view mv1 as
with
  bar as (select * from dual),
  foo(lvl) as
  (
    select 1 as lvl from bar
    union all
    select foo.lvl + 1 from foo join bar on (foo.lvl < 5)
  )
select lvl from foo;

Materialized view MV1 created.

I see no reason for your version not working, it may be a bug, but I could not find anything related to this on the support site of Oracle.

Above output is from version 18.4.


Manually documenting a database I am forced to manually document a legacy enterprise database. Are there some standard good practices or tools to do this?

To give you some more context, I have recently been given restricted access to a legacy enterprise database. There is very little documentation. I don't have permissions to view system tables or generate ER diagrams. I am exploring/using the database and manually documenting it as I go.

I would like to represent the schema with code (manually) in such a way that it can be parsed, say, with Python, to generate an ER diagram?

Are there tools to do this kind of thing? Or standard good practices to document the schema, annotate definitions and common keys etc?

I'm imagining some kind of a tree or graph like data structure that can be used to encode the schema, parsed with Python etc, and used to generate ERDs, etc, outside of the db management tool; (since I don't have permissions to do this inside the db management tool (Oracle SQL Developer)).

Since asking the question, I have found these two very nice web-based solutions:
https://dbdiagram.io/home
https://alternativeto.net/software/quickdbd/
and a basic one:
https://dshifflet.com/erd/index.html

Unfortunately due to security concerns, I am not allowed to use 3rd party cloud solutions. It's gotta be a standalone desktop software.

I also discovered ERAlchemy and it looks like I can manually generate ERDiagrams in Typora using markdown.

Any other useful ideas?


Is total_elapsed_time in DMV sys.dm_exec_requests completely inaccurate? I am running SQL Server 2012 and am trying to put some queries together for monitoring using the DMVs.  However, when looking at the total_elapsed_time field in the sys.dm_exec_requests DMV, the numbers look way off.  Here's an example:

SELECT
  session_id, RunTime = CURRENT_TIMESTAMP,
  start_time, total_elapsed_time
FROM sys.dm_exec_requests
WHERE session_id = 284;

session_id  RunTime                 start_time              total_elapsed_time
284         2016-04-07 16:14:03.690 2016-04-07 16:08:14.587 1419976

By my calculations*, the elapsed time should be around around 349,103 -- not 1,419,976.  That's off by over a factor of 4.

* From the difference, in milliseconds, between the current time and the start_time i.e.
    SELECT DATEDIFF(MILLISECOND, '2016-04-07T16:08:14.587', '2016-04-07T16:14:03.690');

Here's the server info:

SELECT @@VERSION;

Microsoft SQL Server 2012 - 11.0.5592.0 (X64)
    Apr 17 2015 15:18:46
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Any ideas what could be causing this discrepancy?

There is a bug that aggregates the time in a parallel operation.  This is fixed in 2014.
The total_elapsed_time will be correct for a particular parallel query in a batch until it moves on to the next statement in the batch, then the total_elapsed_time will explode by the DOP.
Example
Run this in one query window:
USE AdventureWorks2012
GO
SELECT *
FROM Sales.SalesOrderDetail sod
JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style

waitfor delay '00:00:15'

Run this in a second:
select
    DATEDIFF(ms, start_time, getdate()) ActualElapsedTime,
    total_elapsed_time from sys.dm_exec_requests
where session_id = <your session_id for the above batch>

The two values will be close to identical until SQL Server moves to the WAITFORDELAY statement, then you should see the total_elapsed_time explode (assuming the first query has a parallel plan as it does on my server).
I remember working on this for a customer a few years ago. Found the bug in the internal database (I am a Microsoft Premier Developer Consultant), but no public reference.


Better execution plan if parameter is stored in local variable I have two stored procedures. This one is incredibly fast (~ 2 seconds)

CREATE PROCEDURE [schema].[Test_fast]
   @week date
AS
BEGIN

    declare @myweek date = @week

    select distinct serial
    from [schema].[tEventlog]         as e
    join [schema].tEventlogSourceName as s on s.ID = e.FKSourceName
    where s.SourceName = 'source_name'
        and (e.EventCode = 1 or e.EventCode = 9)
        and cast(@myweek as datetime2(3)) <= [Date]
        and [Date] < dateadd(day, 7, cast(@myweek as datetime2(3)))
END

And this one runs slow (~ 2 Hours):

create PROCEDURE [schema].[Test_slow]
   @week date
AS
BEGIN

    select distinct serial
    from [schema].[tEventlog]         as e
    join [schema].tEventlogSourceName as s on s.ID = e.FKSourceName
    where s.SourceName = 'source_name'
        and (e.EventCode = 1 or e.EventCode = 9)
        and cast(@week as datetime2(3)) <= [Date]
        and [Date] < dateadd(day, 7, cast(@week as datetime2(3)))
END

The only real difference is the line (using the local variable @myweek):

declare @myweek date = @week

Here are the execution plans. The first plan is from [schema].[Test_fast] and the second is from [schema].[Test_slow]:

My question is: Why does SQL Server 2012 get a much better execution plan (faster) when I take the parameter and store it in a local variable, and then use this local variable. Is there something broken with the statistics or the indexes? (I also wonder why the second execution plan is not using any kind of parallel execution).

UPDATE:

I give the 2 SPs the same Parameter and started them in the same time (nearly 2s time diff) the is not auto update of statistic in this DB.

example:

EXEC    [schema].[Test_fast]
        @week = '2016-02-08'

EXEC    [schema].[Test_slow]
        @week = '2016-02-08'

Here is the Execution Plan:

https://gist.github.com/anonymous/6e404f896d9613c2061a#file-sp_execution_plan-sqlplan

An additional update of the index does also have no effect.

Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem before OPTION (OPTIMIZE FOR UNKNOWN) and trace flag 4136 became available.

From the execution plan provided, this is exactly what happened in your case.

When a local variable is used, the value in the variable cannot be sniffed:

Notice the blank "Compiled Value". The query optimizer estimates a higher number of rows based on the average distribution of values in the Date column, (or possibly a complete guess) leading to the parallel plan.

When the stored procedure parameter is used directly, the value of @week is sniffed:

The optimizer estimates the number of rows that will match the query predicates using the value '2016-02-08', plugged into:

and cast(@week as datetime2(3)) <= [Date]
and [Date] < dateadd(day, 7, cast(@week as datetime2(3)))

It comes out with an estimate of one row, leading to the choice of a serial plan with the key lookup. The predicates above are not very friendly for cardinality estimation, so the 1-row estimate may not be very accurate. You could try enabling trace flag 4199 but there's no guarantee the estimate will improve.

For more details please see:

Parameter Sniffing, Embedding and the RECOMPILE Options

In general, it is also possible that the initial run of the stored procedure occurs with a very selective value for @week, with only a small number of rows expected. Another possible cause of problems occurs when a very recent value of @week is used on the initial call, before statistics had been updated to cover this range of values (this is the Ascending Key Problem).

A very selective sniffed value for @week may cause the query optimizer to choose a non-parallel plan with an index seek and a key lookup. This plan will be cached for reuse for future executions of the procedure with different parameter values. Where a later execution (with a different value for @week) selects many more rows than originally, the plan is likely to perform poorly, because seek + key lookup is no longer a good strategy.


How do I find the next saturday in MySQL I have trigger and a stored procedure in it (so SP runs when trigger runs). I need a function which finds the next saturday to put in SP.

So lets say, it is Wednesday (2015-7-22) today. If my trigger runs today, the SP in it must find the next Saturday (2015-7-25).

Plus, even if it is Saturday, but the time is earlier of 9.30 pm, it must find the current day also. After 9.30 pm, it must return the next Saturday.

I would like to the put my whole trigger and sp on here but I don't want to get here crowded. I just need ideas, thanks.

EDIT:

Thanks to oNaye, I coded that:

CREATE DEFINER=`root`@`localhost` PROCEDURE `newGuess`(
IN `muserID` INT,
IN `numm1` INT,
IN `numm2` INT,
IN `numm3` INT,
IN `numm4` INT,
IN `numm5` INT,
IN `numm6` INT)
    begin
set @today = (select weekday(curdate())+1); /*monday is the first day in here*/
if @today<6 or @today=7 then /*it is NOT saturday*/
    set @nextSaturday = (SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY));
end if;
if @today = 6 then /* it is saturday */
    set @current_time = (select curtime());
    set @nextSaturday = (SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY));
    if @current_time < CAST('21:30:00' AS time) then
        set @nextSaturday = curdate();
    end if;
    if @current_time >=CAST('21:30:00' AS time) then
        set @nextSaturday = curdate()+INTERVAL 1 WEEK;
    end if;
end if;
insert into guessesTBL (userID,num1,num2,num3,num4,num5,num6,current__datetime,draw_date) values (muserID,numm1,numm2,numm3,numm4,numm5,numm6,NOW(),@nextSaturday);

end

It worked now.
Solved.

You can use WEEKDAY and DATE_ADD function to calculate the next weekday incoming.

Here what you have to do:

SELECT
  DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,
                             (6-WEEKDAY(NOW())),
                             (5-WEEKDAY(NOW()))) DAY);

The query meaning:

With DATE_ADD you will add an interval between the parameter NOW() and one condition assuming my weekday for Saturday is 5, you will have to evaluate if NOW() is greater than 5 or equal, you have to rest it with 6 (Sunday) and if NOW() is less than 5 you have to rest it 5 with the weekday of NOW().

Test:

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-07-22 07:51:33 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY);
+------------------------------------------------------------------------------------------+
| DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY) |
+------------------------------------------------------------------------------------------+
| 2015-07-25 07:51:34                                                                      |
+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

EDIT:

If you need to calculate to get the next Saturday if the actual Saturday is at 9:30PM:

New syntax:

SET @ACTUAL_DATE=NOW();
SET @NEXT_SATURDAY=DATE_ADD(@ACTUAL_DATE,INTERVAL IF(WEEKDAY(@ACTUAL_DATE)=5 && TIME(@ACTUAL_DATE)>'21:30:00',
                                            7,
                                            IF(WEEKDAY(@ACTUAL_DATE)>=5,
                                                (6-WEEKDAY(@ACTUAL_DATE)),
                                                (5-WEEKDAY(@ACTUAL_DATE))))
                                        DAY);

Today test:

mysql> SET @ACTUAL_DATE=NOW();
Query OK, 0 rows affected (0.01 sec)

mysql> SET @NEXT_SATURDAY=DATE_ADD(@ACTUAL_DATE,INTERVAL IF(WEEKDAY(@ACTUAL_DATE)=5 && TIME(@ACTUAL_DATE)>'21:30:00',
    -> 7,
    -> IF(WEEKDAY(@ACTUAL_DATE)>=5,
    -> (6-WEEKDAY(@ACTUAL_DATE)),
    -> (5-WEEKDAY(@ACTUAL_DATE))))
    -> DAY);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @NEXT_SATURDAY;
+---------------------+
| @NEXT_SATURDAY      |
+---------------------+
| 2015-07-25 21:30:01 |
+---------------------+
1 row in set (0.00 sec)

mysql>

But if we're on July 25th at 21:30:01 (Saturday):

mysql> SET @ACTUAL_DATE='2015-07-25 21:30:01';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @NEXT_SATURDAY=DATE_ADD(@ACTUAL_DATE,INTERVAL IF(WEEKDAY(@ACTUAL_DATE)=5 && TIME(@ACTUAL_DATE)>'21:30:00',
    -> 7,
    -> IF(WEEKDAY(@ACTUAL_DATE)>=5,
    -> (6-WEEKDAY(@ACTUAL_DATE)),
    -> (5-WEEKDAY(@ACTUAL_DATE))))
    -> DAY);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @NEXT_SATURDAY;
+---------------------+
| @NEXT_SATURDAY      |
+---------------------+
| 2015-08-01 21:30:01 |
+---------------------+
1 row in set (0.00 sec)


gcache.page_sizes and gcache.size are set at the same time. When will gcache.pagebe used? After the power failure and restart, the slave node creates a large number of gcache.page, which occupies the disk.

I would like to know if there is already gcache.cache, when will gcache.page be created?

gcache.keep_pages_size = 0;
gcache.mem_size = 0;
gcache.page_size = 128M;
gcache.size = 128M;

The gcache is a ringbuffer and the size of it is set with gcache.size and write-sets are appended to this file and the file can be reused.

If the transaction write-set is large enough not to fit in the the gcache ringbuffer then an physical disk file, a page, is allocated to cache the write-sets. Note that there are two types of pages:

A page with a standard size as defined by gcache.page_size (default=128M as above).
A page with non-standard page size is created if the transaction is large enough not to fit into a standard page. With a gcache.page_size of 1M and transaction write-set of 1.5M will result in a separate page (in turn on-disk file) with a size of 1.5M.


Query not using indexes I have a query which is not using the timestamp  index and scanning 200 million records. I know that the query will not use the index if there is a function applied on top of it. However is there any other way to re-write the query and it can use the index.

    SELECT
*
FROM `collect_location`
where DATE_ADD(timestamp,INTERVAL -1*-6 HOUR) >= '2016-02-24 15:55:01'
and DATE_ADD(timestamp,INTERVAL  -1*-6 HOUR) <= '2016-02-24 16:15:01'\G

 explain SELECT *
    -> FROM `collect_location` where DATE_ADD(timestamp,INTERVAL -1*-6 HOUR) >= '2016-02-24 15:55:01' and DATE_ADD(timestamp,INTERVAL  -1*-6 HOUR) <= '2016-02-24 16:15:01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: collect_location
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 24381470
        Extra: Using where
1 row in set (0.00 sec)

show create table collect_location\G
*************************** 1. row ***************************
       Table: collect_location
Create Table: CREATE TABLE `collect_location` (
  `device_id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `timestamp` datetime(3) NOT NULL,
  `location_provider` varchar(128) DEFAULT NULL,
  `collect_altitude` float DEFAULT '0',
  `session_id` varchar(128) DEFAULT NULL,
  `x_param` float(16,8) DEFAULT '0.00000000',
  `y_param` float(16,8) DEFAULT '0.00000000',
  `testcase_id` varchar(128) DEFAULT '',
  `last_location_time` datetime DEFAULT NULL,
  `location_tags` varchar(50) DEFAULT NULL COMMENT 'tag field',
  PRIMARY KEY (`device_id`,`job_id`,`timestamp`),
  KEY `fk_collect_location_job1` (`job_id`),
  KEY `collection_location.timestamp` (`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

DATE_ADD(timestamp,INTERVAL -1*-6 HOUR) >= '2016-02-24 15:55:01'

is the same as

timestamp >= '2016-02-24 15:55:01' - INTERVAL -1*-6 HOUR

(You might consider changing that triple-negative into a single-negative.)

A side note about float(16,8) -- Don't use (m,n) notation for FLOAT or DOUBLE; it leads to an extra rounding error and limits the range.  Alternatively you could use DECIMAL(16,8).


Infinite loop in CURSOR I'm trying to use a cursor to clean up temp tables when they are no longer needed. I have a small table which has the names of the temp tables along with an identifier. The cursor is stuck in an infinite loop, but only if I execute certain statements in it. If I just print out the values from the FETCH, it works perfectly. Here is the code.

DECLARE @id bigint;
DECLARE @table_name varchar(max);

DECLARE st CURSOR LOCAL FAST_FORWARD FOR
SELECT ID, TableName FROM SearchTables WHERE CustomerID IS NULL

OPEN st
FETCH NEXT FROM st INTO @id, @table_name
WHILE @@FETCH_STATUS <> -1
BEGIN
    IF(OBJECT_ID(@table_name) IS NOT NULL)
        EXEC('DROP TABLE ' + @table_name);

    UPDATE SearchTables SET Deleted=1 WHERE ID=@id;

    PRINT CAST(@id AS varchar(max)) + ' ' + @table_name;

    FETCH NEXT FROM st INTO @id, @table_name;
END

CLOSE st
DEALLOCATE st

If I comment out these lines

    IF(OBJECT_ID(@table_name) IS NOT NULL)
        EXEC('DROP TABLE ' + @table_name);

    UPDATE SearchTables SET Deleted=1 WHERE ID=@id;

PRINT outputs all of the IDs and table names. If I don't comment them, all I get is the first row over and over until I cancel the query. I also tried changing the IF line to EXEC('DROP TABLE IF EXISTS ' + @table_name) but that didn't work either.

Probably you're moving the row when you set Deleted=1 and reading it again with your FAST_FORWARD cursor.  Use a STATIC cursor instead, which will iterate a copy of the data, and avoid mutating the data structure you are traversing.

DECLARE st CURSOR LOCAL STATIC FOR . . .


Query Error: Error: ER_NON_UNIQ_ERROR: Column 'candidate_id' in field list is ambiguous This is my query

SELECT user_name,candidate_id
FROM user AS usr INNER JOIN candidate as can
ON usr.user_id = can.entered_by
INNER JOIN candidate_joborder as canjo
ON can.candidate_id = canjo.candidate_id
WHERE usr.user_id = 1

And my fiddle

https://www.db-fiddle.com/f/gWr5g2222yAE8Fh1Gc6Awf/2

How to resolve this?

Your query is right, only

    SELECT user_name, can.candidate_id
FROM user AS usr INNER JOIN candidate as can
ON usr.user_id = can.entered_by
INNER JOIN candidate_joborder as canjo
ON can.candidate_id = canjo.candidate_id
WHERE usr.user_id = 1


DBCC CHECKDB consistency-based I/O error Running:

DBCC CHECKDB(DatabaseName) with NO_INFOMSGS

gives me the following error:

  Msg 824, Level 24, State 2, Line 1
  SQL Server detected a logical
  consistency-based I/O error: incorrect pageid     (expected 1:7753115;
  actual 0:0). It occurred during a read of page (1:7753115) in database
  ID 11 at offset 0x00000ec9b36000 in file 'K:\UAT
  Databases\dbname.MDF'.  Additional messages in the SQL Server error
  log or system event log may provide more detail. This is a severe
  error condition that threatens database integrity and must be
  corrected immediately. Complete a full database consistency check
  (DBCC CHECKDB). This error can be caused by many factors; for more
  information, see SQL Server Books Online.'

Also i found a entry in dbo.suspect_pages

Please advise.

Ensure you have a valid backup; hopefully it will be from prior to the corruption, but not so long ago that the data isn't useful. You should set this aside in case direct repair isn't possible and you need to recover data from the backup.
The documentation explains what to do to correct the problem - you can try the REPAIR_REBUILD option, and if that doesn't solve it, you can move on to next steps according to the guy who wrote CHECKDB, Paul Randal. He has a ton of articles on CHECKDB, that cover just about every conceivable scenario, but which ones are relevant to you will depend on what happens next when you attempt your repair. One that may be useful as a start, so you know which table you'll need to fix:

Finding a table name from a page ID


Regarding Backup and Restore Strategy I have some questions regarding backup and restore strategies.

I want to take backup and restore of large databases approx. 60 to 70 GB and it is a critical database on SQL Server.

Backup schedule approx. I decided to:

Full backup every day
Differential backup every 2 or 3 hours
Transaction-log backup every 15 minutes

Is this a correct approach? If not then please suggest better option.

Which recovery model is best suited? (Simple, Full, Bulk-logged)

In case any of the backup files (full backup file, differential backup file or log backup) are corrupted, then what are the options to recover database quickly with minimum data loss?

Your backup plan seems to be good. Of course, everything depends on:

Recovery Point Objective (RPO) in other words – how much data can you afford to lose?

And

Recovery Time Objective (RTO) if database disaster occurs, how much time are you able to spend restoring a database to its working condition?

Note, if you will run your database under the simple recovery model you will not able to make  transaction log backups.


How to write a no-op UPDATE query? Suppose we have a simple update query:

UPDATE foo
SET
  bar=$1
WHERE id=$2

where bar=$1 is generated based on user's input and it might be null, e.g.

UPDATE foo
SET
  ${assignmentList}
WHERE id=$2

How do I make this update operation into a no-op in case assignmentList variable is empty?

I have tried using 1=1, i.e.

UPDATE foo
SET
  1=1
WHERE id=$2

but this gives a syntax error.

It is only slightly more code to skip the UPDATE if there is nothing to do.  And the code is much cleaner.  And less kludgy.


memory used by a query result set in Oracle 11g There is something not very clear in my mind and that I wanted to know when performing queries that retrieve large result sets:

Is a result set entirely loaded into memory and send to the client or only a part of it is loaded, send and then the memory is reused to load the next part until all data has been processed to fulfill the query ?

I suppose that for some queries that require a sort operation or use an analytical function it has to be fully loaded but what for basic queries that would only involve one table ? Like for instance:

select * from mytable where id between 1 and 1000000;

Will the one million records be loaded entirely into the memory allocated for my query or only a part of it ?

Thanks for your help,

Usually it's in the PGA (Program Global Area). But it will depend on a number of things. For more info, take a look at: https://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm#CNCPT007


How to tag a field in sql server? I want to categorizes things in data base so that one thing can be in several categories. I have a table for categories. I want to make foreign key to them but how can I make a field have several value? is there a way to make the coulmns dynamically or have an array of an enumeration or array of another table fields?

In generally, when you have a many to many relationship (one "thing" can be in many categories, one category can have many "things" in it), you have to set up a linking table to tie the two things together.

Each row in the linking table would have the ID of one row in the "thing" table, and of one row in the category table. So, many rows could be used to tie a given "thing" to its categories, and many rows could tie different "things" to a given category.

The thing_id and category_id columns in the linking table would be foreign keys to the "thing" and category tables (respectively).


Inserts/updates are extremely slow with Merge Replication in SQL Server 2008 I got hired into a new company and they had merge replication already in place. They have had issues with it to the point I have to evaluate whether to keep it or replace it. The main issue is it takes many hours (over 12 hours in some cases) to insert or update a record. Honestly that is just unacceptable.

As an example I am trying to insert one record into a table (ComputerUsers) that links a laptop to a user. You only have two columns (one is call ComputerID and one is called UserID). There is a Computers table that stores the ComputerIDs and the Computername. There also is a Users table that stored the UserID and username.

All I am trying is insert one record into the ComputerUsers table. Last time I ran it it took over 13 hours. This table is published and it is part of the Dynamic Filter.

I have tried everything I have read online. I created two separate indexes for the UserID and ComputerID columns since they are both included in separate joins in the Filter. There is also a clustered primary key on the Userid and ComputerID columns since the combination of the two are unique (A computer can be assigned to two users or vica versa). I have also have tried rebuilding the indexes and even created some suggested indexes on the merge replication tables.

I am at my wit's end. What am I missing? I find it hard to believe this is normal for Replication because I doubt anyone would use it if it was. Thanks.

I had a very similar sounding problem.  We replicated a table with millions of rows.  One of the subscribers would take up to 2 minutes to insert 100 new rows.

I solved the issue by adding an index to the rowguid column of the table it was inserting to.  I know this sounds like it should degrade performance but it looked like it was doing a full table scan before inserting, probably to make sure a duplicate row was not inserted.

Once I added this index replication was running smoothly again


How to model (a many to many) to many relationship Hello i am facing the following scenario. I have a student table with the following structure.

Student
id(PK), matriculation number, first_name, last_name, email

A student can have more than one Majors(BSc mechanical engineering, MSc mechanical engineering, etc...)

Major
id(PK), major

At the same time a major may be assigned to more than one students, therefore the relationship between  student and major is many to many. I implemented it using the following mapping table.

MajorStudent
id(PK), student_id(FK), major_id(FK)

Every student however can sit for many exams. But for every exam it is important to know his Major. That is he can sit for the same exam as BSc mechanical engineering and as MSc mechanical engineering, but this should count as two different exams. Meanwhile every exam is assigned a grade, which can have one of four values. My exam and grade tables looks like this.

Exam
id(PK), major_student_id(FK), grade_id(FK)

Grade
id(PK), grade(value1, value2, value3, value4)

What i am trying to get at the end is the following

View
student_id | first_name | last_name |... | major      | exam_id | grade
1            test1       testtest1        BSc M.engin   1         value1
1            test1       testtest1        MSc M.engin   2         value3

Do you think my desing is correct. And in general how would you go about modeling  a many to many to many relationship? (If it helps i am using MySQL and cakephp to implement it)

The tables student and major are good.

However following improvements can/should be made.

Merge the FKs in MajorStudent to a PK, getting rid of the id
Save a StudentFK and MajorFK rather than a MajorStudentFK in Exam, well one can discuss about this though but it'll be faster and in my consideration NOT redundant
Grade is not normalized. All attributes are supposed to be atomic and grade is not. Either make 4 columns (bad) or use the following

Exam(id[PK], studentId[FK], MajorId[FK])
Grades(id[PK], examId[FK], value)

I don't understand why one exam comes with four grades though.


Dump Oracle database into a textual SQL script I am thinking on what mysqldump or pg_dump are doing. So, they dump an active database into a textual file containing the SQL queries, which reproduce this whole database. In MySQL and in PostgreSQL, this is the normal database dump format.

Does it exist on Oracle? How can it be done?

It is absolutely not a problem if it is not a standard Oracle thing. I need a solution to the problem. Exporting single tables is not enough, I am asking for the dump of a whole DB.

It is a reduced developer environment of a big project (with multi-million row DB), but still having hundreds of tables and a very complex structure. And, I want to play with it fast. While I am playing with it, I also need to be able to execute tricky modifications on that. On a binary dump I can't do that, on a textual I can.

Does it exist on Oracle?

Similar utility exists in Oracle environment called Data Pump.

  How can it be done?

Use expdp command line tool to perform full database export.

Example:

expdp hr DIRECTORY=dpump_dir2 DUMPFILE=expfull.dmp FULL=YES NOLOGFILE=YES

Then can use impdp to import it in another database.

Example:

impdp hr DUMPFILE=dpump_dir1:expfull.dmp FULL=YES LOGFILE=dpump_dir2:full_imp.log

If you specify DUMPFILE parameter it writes a binary file called dump file. With the SQLFILE parameter(SQLFILE=/my/file/name.sql) you can get all of the SQL DDL that Import would have executed.

More on it depends upon the requirements.

Oracle Documentation explains more than I can here.

Overview of Oracle Data Pump


Post mortem on tempdb transaction log after instance restart? TempDB transaction log filled up and I had to restart SQL Server as a result. After the restart, is there any way to see what caused the transaction log to fill up in the first place?

Short answer is - Not Really as Kin got at.

That said the default server side trace, if enabled as it is by default, may be of help to you here. Look in the "LOGS" directory in SQL Server and look in the SQL Server installation directory for your instance. It doesn't show you everything that happened, unfortunately. But it could show you the times of the autogrowths to compare against (that is included in the trace). It can also show you hash warnings and sort warnings which could be a cause of TempDB growth. You can also see object creations and deletes for any objects for certain explictly created objects. You can see more about what is included and how to view/query this here.

Kin's answer is also good about looking at what is going on before you restart. Now one note here is you may not need to use the DAC to connect (This link describes what the DAC is). If you are connected or can connect, next time you can run some queries to at least get a sense for what is going on at the time of the event:

SP_Whoisactive is a great script to show you what is going on at that moment, you can also see who is consuming TempDB at that time using the TempDB_Writes/TempDB_Current columns to see the allocation in TempDB.
The scripts in this article are excellent at showing what is consuming space in TempDB. Also this question discusses it with some other queries.

Trick is seeing how TempDB is being used and addressing it over time in your processes and ensure TempDB is appropriately sized and the drives are appropriate for your needs.


How to update xml using value from a sql query I have a table with an XML column. I need to insert various nodes and then set those nodes using values from a sub-query.

Setup:

use tempdb

CREATE TABLE [dbo].[Sites](
    [SiteID] [int] IDENTITY(1,1) NOT NULL,
    [SiteInfo] [xml] NULL,
    [InVal] Varchar(50) NULL)

insert into [dbo].[Sites] ([InVal]) select 'ABC' union select 'DEF' union  select 'GHI'

update [dbo].[Sites] SET [SiteInfo] = '<SiteInfo />'

update [dbo].[Sites] SET [SiteInfo].modify('insert <SiteID/> into (/SiteInfo[1])')

Here is my update in it's most simple form.

update Sites SET [SiteInfo].modify('replace value of (/SiteInfo/@SiteID)[1] with sql:column("InVal")')

I have tried with both singleton and non singleton nodes

The end result should preferably not be a singleton. i.e something like:

<SiteInfo>
  <SiteID>ABC</SiteID>
</SiteInfo>

You've got nothing to replace in <SiteID/>. Try and use an insert instead:

update Sites SET [SiteInfo].modify('insert text{sql:column("InVal")} as first into (/SiteInfo/SiteID)[1]');


Selecting ports for Availability Groups Listener and SQL Instances Experts,

I have 2 node Availability Groups with  File share witness.

We chose the non standard port 17333 for the Listener and 15333 for the 2 SQL Server instances.

One of our newer application was giving issues connecting to listener and was expecting same port number for listener and sql instance.

Can someone advise what is the best practice.

Possibly we can have have
a) Same ports for listener and all sql instances
b) Different port for listener and same port for sql instances
c) Different port for each of listener and sql instances

According to this Microsoft Tech Community post, you can do any of the three port configurations you have listed.

You can choose the port numbers in many different ways.

Specify different port numbers for each instance and a different port number for the Listener.
Specify the same port number for all the instances (as a standard), and a different port number for the Listener.
Specify the same port number for all the instances as well as the Listener. This is possible because the IP address of the SQL Server instance is different from the IP address of the Listener.

It goes on to suggest the following as a possible best practice.

Don't turn on the SQL Browser service
Use the same non-default port number for SQL Server instances as well as for the Listener
Specify the port number whether connecting to a SQL Server instance, or connecting to the AG Listener

This practice may or may not work for all customers, but maintains consistency of the connection strings with or without the SQL Server Browser, with default or non-default port numbers, or whether connecting to the SQL Server instance or the AG Listener.


Post replication powershell script - on subscriber I need to execute a command before a replication executes on the publisher, and after, on the subscriber.

I seem to have it working on the publisher, by adding another step in the replication agent.

But i have yet to discover how i can execute another step on the subscriber.
I't has to be possible to somehow trigger a job on the subscriber, after a replication has succeded.

Do anyone know how this can be done?

Add another job step to the snapshot agent which runs sqlcmd with what ever T-SQL command needs to be run using the -S parameter to specify to connect to the remote SQL Server.


Having Updatable Views that obey initial select conditions Let's say I have a table like this:
+--------------+----------+-------------+
| name         | is       | in          |
|--------------+----------+-------------|
| Study        | <null>   | <null>      |
| Sport        | <null>   | <null>      |
| Biology      | Study    | <null>      |
| Linguistics  | Study    | <null>      |
| Art          | <null>   | <null>      |
+--------------+----------+-------------+

And I want to build a view as such:
CREATE VIEW v AS SELECT name FROM t WHERE "is" = 'Study';

Now if I want to insert into this view, it does not automatically set "is" to 'Study', I have to write a trigger and a function to adjust this behavior. However, this is a highly repeated pattern in my application, and I was wondering if there is a way to automatically achieve this (an updatable view that obeys the select condition).

maybe it works, it is in SQL server but concept maybe the same in your case:
If its always required you can set default. It will update always when you insert the view [is] with 'Study':
ALTER TABLE t ADD CONSTRAINT  df_is DEFAULT 'Study' FOR [is];

Or you can create another view only for insert:
CREATE VIEW v_insert AS SELECT name,[is] FROM t WHERE [is] = 'Study';
DECLARE @is NVARCHAR(max)='Study';
INSERT INTO dbo.v_insert (name, [is]) VALUES ('nametoinsert', @is )


How can I implement full-text search on a single value in a JSONB column? I have a table that stores a conversation between two people.

The data will look something like this:

CREATE TABLE foo
AS
  SELECT $$[
    { "user": 1, "timestamp": 1, "message": "First message" },
    { "user": 2, "timestamp": 2, "message": "Second message" },
    { "user": 2, "timestamp": 3, "message": "Debounced message from same user" },
    { "user": 1, "timestamp": 4, "message": "Last message" }
  ]$$::jsonb AS jsondata;

I never need to look up each message individually, so I just want to store the whole conversation in a single jsonb field. I need to perform a full-text search across all of the messages.

My first thought was to create a new text column, concat all of the messages into one long string, and create a trigram GIN index on that column.

That seems like a hack that wastes a lot of space, so I would like to avoid the intermediate column. How can I create the index directly from the jsonb column?

The way I read this question, you only care about message. The difficulty here is that you need to,

map over a json array returning the message element
reduce/fold the array of message element strings to an aggregate string.

This is easy in functional programming. It's not as easy with the stock functions in PostgreSQL, and it'd be difficult to make it work with a declarative language. Maybe one day you'll have a jsonb_array_elements(jsonb [,path]) which will get you by but until then we can create a function in our database.

Creating a function with plpgsql

Note this probably isn't as a fast nor as clean as a plv8 function, but in the next revision we'll return a tsvector.

Here we use jsonb_array_elements to expand the json, and then aggregate back the 'message' elements into a string.

CREATE OR REPLACE FUNCTION jsonb_message_to_string( jsondata jsonb, out string text )
AS $func$
  BEGIN
    SELECT INTO string
      string_agg(d->>'message', ' ')
    FROM jsonb_array_elements(jsondata) AS d;
    RETURN;
  END;
$func$ LANGUAGE plpgsql
IMMUTABLE;

Creating tsvector_agg and improving our function.

This function is not yet optimal though because it's returning a string. However, there is a second difficulty in that as of 9.6 PostgreSQL does not yet ship with a tsvector_agg; but, it's PostgreSQL so we can make one..

CREATE AGGREGATE tsvector_agg (tsvector) (
  SFUNC = tsvector_concat,
  STYPE = tsvector
);

This permits us to now return an aggregate tsvector which is faster and retains positional information. Now we can improve our function. Here we create a new jsonb_message_to_tsvector.

CREATE OR REPLACE FUNCTION jsonb_message_to_tsvector( jsondata jsonb, out tsv tsvector )
AS $func$
  BEGIN
    SELECT INTO tsv
      tsvector_agg(to_tsvector(d->>'message'))
    FROM jsonb_array_elements(jsondata) AS d;
    RETURN;
  END;
$func$ LANGUAGE plpgsql
IMMUTABLE;

Now we can create our index..

CREATE INDEX ON FOO
  USING gin (jsonb_message_to_tsvector(jsondata));

And we would query it like so..

SELECT jsonb_message_to_tsvector(jsondata) @@ 'first'
FROM foo;


Database backup and restore incase Always Encrypted feature is enabled I would like to know that How SQL database backup and its restoration on other server will work if Always Encrypted feature is implemented on most of its tables?

Thanks

I tested with "Window Certificate Store - Current User" and "Windows Certificate Store - Local machine (localhost) and concluded to.

Existence of any object related to Always Encrypted features (master key, column master key and the certificates used to create the master key) is limited to the database only.

So simple backup and restore process works fine.

Thanks!


Declare variable for SELECT statement in Postgres function I have modified my function, but I have problems with declaring variables. I use Postgres 8.4.

CREATE OR REPLACE FUNCTION requestcounterid(_mindate timestamptz, _maxdate timestamptz)
  RETURNS TABLE (kategorien text, requestcounter int) AS
$func$
DECLARE
_minid bigint;
_maxid bigint;

BEGIN

SELECT id  INTO _minid from tablename
where starttime >= $1 ORDER BY starttime ASC LIMIT 1;
SELECT id  INTO _maxid from tablename
where starttime < $2 ORDER BY starttime DESC LIMIT 1;

SELECT CASE WHEN duration <= 10000000 THEN '00-01 sec'::text
            WHEN duration <= 40000000 THEN '01-04 sec'
            WHEN duration <= 100000000 THEN '04-10 sec'
            WHEN duration <= 300000000 THEN '10-30 sec'
            WHEN duration <= 600000000 THEN '30-60 sec'
            ELSE 'more than 60 sec' END
     , count(*)::int
FROM   tablename
WHERE  id >= _minid and id <= _maxid
GROUP  BY 1
ORDER  BY 1;

END;
$func$ LANGUAGE plpgsql;

Error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "requestcounterid" line 12 at SQL statement

How to fix this?

If the SELECT with the CASE expression is something you want to return in the output table, just add RETURN QUERY before it:

RETURN QUERY SELECT CASE ...

Note: 8.4 is very old now.  Even 9.0 is out of support by now - consider upgrading to a recent major version soon.  The old ones usually don't get any security (and other) fixes anymore.


Resume job 'A' at some step till job 'B' is completed I have two SQL Agent jobs 'A' and 'B'
Job 'A' has 10 Steps and it starts at 5:00 Am as per schedule,
job 'B' has 5 Steps and it Starts by using trigger in step 3 of job 'A',
job 'B' should be Completed before starting of step 8 in job'A',
But sometimes Job 'B' is not completed when step 8 in job'A' starts.

So, my requirement is, i want to add one step before step 8 in that i want to check the status of job'B' when job'B' is completed then step 8 should start else it should not start and it should wait till job'B' completed

How can i achieve this?

Thank you.

You are using jobs incorrectly, the steps from job B should simply be in job A. Use stored procedures to group and reuse common code not jobs.

However it is possible to do this, in which case this is a duplicate of Good way to call multiple SQL Server Agent jobs sequentially from one main job?


T-SQL script to perform a compressed backup with maximum compression level I have a database with full backup of almost 2GB.
When I perform a full backup (without compression) and then compress it with winrar it become 140MB, but when I backup WITH COMPRESSION syntax the result is 315MB, and unfortunately after compression with winrar the size not changes.
How can I use T-SQL for full compression at maximum level?

SQL Server's built-in backup compression only has one level of compression (as Mark and Adrian have already said). The most likely reason that you are getting a smaller final file by compressing the uncompressed backup is that it will contain large amounts of plain text. WinRAR and other traditional compression utilities are very good at compressing plain text.

An alternative means of getting smaller backup files is to use a third-party backup application. There are several of these on the market, including Red Gate's SQL Backup Pro and Idera's SQL Safe Backup. These contain more efficient compression algorithms that should give you smaller backups without post processing. I don't use either of these personally so can't recommend a specific one.


Log statements from within triggers in Postgresql I have several sql statements running in some of my plpgsql triggers. I've enabled logging with the log_statement = 'all'. It logs the 'triggering statement', but not the statements within the query itself.

Is there a way to display statements that were executed within trigger in standard postgres log?

The auto_explain module has auto_explain.log_nested_statements since at least 8.4, so that's one option. It's a bit heavyweight but I'm not aware of any other logging option that will record statements run inside PL/PgSQL functions and triggers.

See this similar question.


compare to total, compare to percent of a main category, compare over periods e.g. month over month to get growth % This is kind of a big question. I am looking for maybe a general direction, possible solutions to my issue, if a specific solution is impossible to give over the forum.
I am working in BigQuery, however the syntax is almost identical to SQL with some functions being the exception.
I am relatively new to advanced SQL and I've been tasked with creating the following report:

from the following tables:

The report needs to be presented via Google Data Studio, where data modelling is not possible, so the data must be prepared in SQL in advance to loading in Data Studio which would be the visualization/presentation tool.
My idea was to write a query per each period required, and then UNION ALL these queries, so for example the query for "last week" as a period would be:
SELECT distinct orderid,
       net_revenue,
       country,
       main_category, sub_cat, sub_sub_cat --there's several nested categories
       'last week' as period,
FROM table_orders
join table_order_values
  on table_orders.orderid = table_order_values.orderid
full outer join categories as cats
  on cats.product_id = table_orders.product_id
join countries
  on table_order_values.country = countries.country
and table_order_values.date >
    DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK(sunday)), INTERVAL 1 WEEK)
    AND table_order_values.date <=
        DATE_TRUNC(CURRENT_DATE(), WEEK(sunday))

...and the end-table after UNION ALL of a bunch of tables for different periods would look like this:

However, with this end-table I have no clue how to get a growth % per period (so a comparison month over month, or week over week, etc.), or how to properly calculate a sub_category's revenue % of total revenue (and not with a bunch of declared variables per period), and even more difficult - to get a revenue % of any sub_category from the main category, which then also needs to be filterable by country...
Is this even possible the way it was requested? Could I restructure the data somehow from the very beginning to make these % from total, from main category, and growth over periods possible?

If anyone stumbles upon my question looking for an answer, I solved the % of total by persisting a table with totals per period (month, week, etc), then joined that table with the data table from above and used a sum(net_revenue)/max(total_revenue) and a visual filter for the period. It's not the most elegant solution, but it allows for some visual interaction within the nested categories.


How to recover from transaction replication failure? My transaction replication works such that, to curb data growth, every day articles in publishers are purged but this purging of data is not replicated on to the subscriber. So at any point of time my publisher only has current day's data and my subscriber has archived data. How can I recover from a transaction replication failure? My publisher doesn't have all the data so I can't reinitialize my subscription.

1)Take a backup of your existing subscriber

2)Restore backup to another location

3)Reinitialize subscription

4)Load data from backup restore into reinitialized database

Going forward I would consider archiving data in another way as to not run into this problem again.


MariaDB InnoDB does not rollback on timeout I have the ff. sample queries in this sequence:

START TRANSACTION
UPDATE
INSERT
COMMIT

Query #2 is timing out but I noticed that query #3 is not rolled back.
I am also trying to manually rollback by verifying (querying the table) if the updates went through before I commit. But when I do that, I get an explicit: General error: 2006 MySQL server has gone away error.
My questions:

I have innodb_rollback_on_timeout set to ON, isn't query #3 supposed to be rolled back?

Since I can't even manually roll back, what options do I have?

Note: This is on mariadb-10.4.17

What is the client?  Does it have "auto reconnect" turned on?  Turn it off.
What I think is happening:

The UPDATE croaks (in any way) and gets rolled back.
The connection is reestablished, perhaps with autocommit.
The INSERT runs successfully and autocommits.
The COMMIT is silently ignored.

Put error checks after every SQL statement.