2 Level pivot using Postgresql I have a table whose schema along with data (table_name : raw_data) appears to be this :

name | category | clear_date |

A    | GOOD     | 2020-05-30 |
A    | GOOD     | 2020-05-30 |
A    | GOOD     | 2020-05-30 |
A    | GOOD     | 2020-05-30 |
A    | BAD      | 2020-05-30 |
A    | BAD      | 2020-05-30 |

Now if I perform a "groupby" operation using the following statement :

 SELECT name, category, date(clear_date), count(clear_date)
 FROM raw_data
 GROUP BY name, category, date(clear_date)
 ORDER BY name

I get the following answer :

  name | caetgory | date      | count |
  A    | GOOD     |2020-05-30 |  4    |
  A    | BAD      |2020-05-30 |  1    |
  A    | BAD      |2020-05-31 |  1    |

IN order to produce the pivot in following format :

 name | category | 2020-05-30 | 2020-05-31 |
 A    | GOOD     | 4          |   NULL     |
 A    | BAD      | 1          |   1        |

I am using the following query :

 select * from crosstab (
    'select name, category, date(clear_date), count(clear_date) from raw_data group by name, category, date(clear_date) order by 1,2,3',
    'select distinct date(clear_date) from raw_data order by 1'
    )
    as newtable (
    node_name varchar, alarm_name varchar, "2020-05-30" integer, "2020-05-31" integer
)
ORDER BY name

But I am getting results as follows :

    name | category | 2020-05-30 | 2020-05-31 |
    A    | BAD      |    4       | 1          |

Can anyone please try to suggest how can i achieve the result mentioned above. It appears crosstab removes the duplicate entry of A automatically.

For crosstab(), the "row_name" (cagetory in your example) must come before "extra" columns (name in your example).

Assuming name is indeed an "extra" column like the sample data suggests, this would do it:

SELECT name, category, "2020-05-30", "2020-05-31"
FROM   crosstab (
   'select category, name, date(clear_date), count(clear_date)::int from raw_data group
    group by 1,2,3 order by 1 DESC,2,3',
   'select distinct date(clear_date) from raw_data order by 1'
   )
   AS ct(category text, name text, "2020-05-30" integer, "2020-05-31" integer)
ORDER  BY name;

See:

Pivot on Multiple Columns using Tablefunc
PostgreSQL Crosstab Query

I left the 2nd function parameter 'select distinct date(clear_date) from raw_data order by 1' unchanged. But I would provide constants there, as you need to adjust the column definition list to match anyway.


Vertically align sql tool Is there a tool out there that will take sql and align it vertically?

what I have:

SELECT first_name AS foo
      ,last_name AS bar
      ,ssn AS bas
      ,address AS chu
FROM dbo.contacts;

desired results or something similar to the statement below:

SELECT first_name AS foo
      ,last_name  AS bar
      ,ssn        AS bas
      ,address    AS chu
FROM dbo.contacts;

My team has been using http://poorsql.com/ to line things up, but it would be nice to be able to make use of white space and have our AS statements line up without having to manually format everything.

I know that ApexSQL Refactor ( https://www.apexsql.com/sql_tools_refactor.aspx ) will cover the aliases as preset option, not sure about AS without doing some digging.


A question about oracle upgrade from 12.0.0.1 to 12.0.0.2 using dbua I found this guide for upgrade.
After the upgrade, can I delete safely the old DB product dir
/var/oracle/app/oracle/product/12.1.0 ?

Yes, but do not simply delete it - deinstall it properly: http://docs.oracle.com/database/121/LADBI/remove_oracle_sw.htm#LADBI7824

Run $ORACLE_HOME/deinstall/deinstall and follow the instructions.


Postgres database dump and restore on different database I have two Postgres databases on the same server, which have the same schemas.
The goal is to have DB1 as the production and DB2, as the database that will receive all the data that is migrated from a MySQL db, and then use the dump created after the migration on DB2 to restore the DB1. In other words, dump DB2 and use this dump to perform a restore on DB1.

This would allow to have the production DB1 "always" available even when the migration process is taking place on DB2.

My question is, is it possible to use the DB2 dump to restore the DB1? Or should a different strategy be used -
like renaming the databases?
Thank You

It sounds like you're trying to refresh a database that's a snapshot of another DB elsewhere, and do so without interrupting read access to the first DB.

If so, the easiest way is actually to do it live in the snapshot DB. Just begin a transaction, truncate all the tables, and populate them with new data, then commit. Concurrent transactions will see the old data until you commit, then see the new data.

If for some reason you can't do that, you may instead wish to populate one DB offline, then ALTER DATABASE ... RENAME to swap them. Doing this will require a brief period where all connections are forced offline and currently running transactions are aborted, which is why I suggest doing it with the first method above.

If all your tables are in one schema in the main DB you could instead populate a copy of the schema then ALTER SCHEMA ... RENAME to swap them. This doesn't require disconnection.


How to take a database dump for selective set of values in few tables? create table foo (id, val1, user_id,...)
create table bar (id, foo_id, val2, ...)
create table baz (id, bar_id, val3, ...)

select * from foo where user_id = 1;
select * from bar where id in (select id from foo where user_id = 1)
select * from baz where id in (for all the above foos)

How do I write a dump command which does the above?

I posted an answer similar to this back on Aug 15, 2011 : Is it possible to mysqldump a subset of a database required to reproduce a query?

You will have to employ the use of --lock-tables

From that post, the OP wanted all data connected with this query

select table1.id, table1.level, table2.name, table2.level
       from table1 join table2 on table1.id = table2.table1_id
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum');

I recommended doing these mysqldumps

mysqldump -u... -p... --where="name in ('fee','fi','fo','fum')" mydb table3 > table3.sql
mysqldump -u... -p... --lock-all-tables --where="table3_id in (select id from table3 where name in ('fee','fi','fo','fum'))" mydb table2 > table2.sql
mysqldump -u... -p... --lock-all-tables --where="id in (select table1_id from table2 where table3_id in (select id from table3 where name in ('fee','fi','fo','fum')))" mydb table1 > table1.sql

You then have to load them in a target DB server in reverse order:

mysql -u... -p... -D newdb < table1.sql
mysql -u... -p... -D newdb < table2.sql
mysql -u... -p... -D newdb < table3.sql

Now, let's look at your queries

select * from foo where user_id = 1;
select * from bar where id in (select id from foo where user_id = 1)
select * from baz where id in (for all the above foos)

You would dump as follows:

dump foo where user_id=1
dump bar where foo_id in (select id from foo where user_id = 1)
dump baz where bar_id in (select id from bar where foo_id in (select id from foo where user_id = 1))

Here the dumps

WHERE_CLAUSE="user_id=1"
mysqldump -u... -p... --where="${WHERE_CLAUSE}" mydb foo > foo_subset.sql
WHERE_CLAUSE="foo_id in (select id from foo where user_id = 1)"
mysqldump -u... -p... --lock-all-tables --where="${WHERE_CLAUSE}" mydb bar > bar_subset.sql
WHERE_CLAUSE="bar_id in (select id from bar where foo_id in"
WHERE_CLAUSE="${WHERE_CLAUSE} (select id from foo where user_id = 1))"
mysqldump -u... -p... --lock-all-tables --where="${WHERE_CLAUSE}" mydb baz > baz_subset.sql

Here is the reload order

mysql -u... -p... -D newdb < baz_subset.sql
mysql -u... -p... -D newdb < bar_subset.sql
mysql -u... -p... -D newdb < foo_subset.sql

Give it a Try !!!


Retrieving row count without using Count function I wrote a query below which shoots me a syntax error why would it do so,

SELECT MAX('Row') FROM

(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS)

Error Desc:

Incorrect syntax near ')'.

I don't get it :(

If you want to get the exact count of rows in an efficient manner, then COUNT(*) is it. The ANSI standard (look for "Scalar expressions   125") states that COUNT(*) give the row count of a table: it is intended to be optimised from the start.

  If COUNT(*) is specified, then the result is the cardinality of T.

A ROW_NUMBER() function isn't a practical option: it isn't a counting function (it's  "ROW_NUMBER") and it will run badly as you add rows: a few 1000 will show how bad this it
SUM(1) may be optimised to COUNT(*) internally but I'd never use it
@@ROWCOUNT will require all rows to be returned by the first SELECT, which is a huge unnecessary overhead.

If you can live with approximate for SQL Server then use sys.dm_db_partition_stats. Marian's answer is out of date now since SQL Server 2005 added dmvs

SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'Mytable' AND (index_id < 2)

See this on SO too for some more info: https://stackoverflow.com/questions/6069237/fastest-way-to-count-exact-number-of-rows-in-a-very-large-table/6069288#6069288

In summary, there is exactly one useful way of getting the number of rows in a table. COUNT(*)


select json_object() into list postgres function Here i am trying to get all the select values in a varible.

 DECLARE
   ite json;
 BEGIN
   select INTO ite from json_object('{1}','{1}');
 RETURN ite;

when i call this function i got the result null.

Have a look at SELECT INTO in Postgres docs.

SELECT INTO: define a new table from the results of a query

Synopsis

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

Examples

Create a new table films_recent consisting of only recent entries from the table films:

SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';

Try the next syntax:

CREATE OR REPLACE FUNCTION fn_get_json()
RETURNS text AS
$$
DECLARE
   ite text;
BEGIN
   select * into ite from json_object('{1}','{1}');
   return ite;
END
$$
LANGUAGE plpgsql;

select * from fn_get_json();

| fn_get_json |
| :---------- |
| {"1" : "1"} |

db<>fiddle here

Or as pointed out in comments a reduced version:

CREATE OR REPLACE FUNCTION fn_get_json()
RETURNS json AS
$$
BEGIN
   return json_object('{1}','{1}');
END
$$
LANGUAGE plpgsql;

Or even simpler with a SQL function:

CREATE OR REPLACE FUNCTION fn_get_json()
RETURNS json AS
$$
   select json_object('{1}','{1}');
$$
LANGUAGE sql;


Why can't I make this insert statement a procedure? The insert statement works, but when I try to make it a procedure it doesn't compile.
CREATE OR REPLACE PROCEDURE INSERT_INTO_CPP AS
BEGIN
    INSERT INTO cpp /*+ ignore_row_on_dupkey_index ( cpp ( pk_cpp ) ) */
    SELECT * FROM EXTERNAL (
        (
      cpp VARCHAR2 (50),
      rfu1 NUMBER (6, 2),
      rfu2 NUMBER (6, 2),
      mean_rfu NUMBER (6, 2),
      charge_ph7_4 NUMBER (2),
      hydropathy NUMBER (3, 1))

        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY cpp_data
        ACCESS PARAMETERS (
            RECORDS DELIMITED BY NEWLINE
            SKIP 1
            BADFILE cpp_data:'cpp_badfile.bad'
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
            MISSING FIELD VALUES ARE NULL
            )
        LOCATION ('LYS_cpp.CSV')
        REJECT LIMIT UNLIMITED) ext
        WHERE NOT EXISTS (
            SELECT * FROM cpp c
            WHERE c.pk_cpp = cpp );
END INSERT_INTO_CPP;
/

I get this error:
PL/SQL: ORA-06553: PLS-103: Encountered the symbol ")" when expecting one of the following:     . ( of

I've raised a bug for this. In the meantime, you can work around the issue by using dynamic SQL:
create or replace directory tmp as '/tmp';
declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ('TMP', 'test.csv', 'w');
  utl_file.put_line(f, '1,1');
  utl_file.put_line(f, '2,2');
  utl_file.fclose(f);
end;
/

create table t (
  c1 int, c2 int
);

create or replace procedure p as
begin
  execute immediate q'!insert into t
    select * from external (
    ( c1 int, c2 int )
    default directory tmp
    location ( 'test.csv' )
  )!';
end p;
/

exec p();

select * from t;

C1     C2
    1     1
    2     2


DBO on System Databases I am trying to implement some better security practices for our database systems. Right now, dbo is owned by SA. I plan to create new accounts on each server, with differing passwords, and assign SysAdmin to that local user. For many databases, dbo user is linked to the SA login. I can change these using ALTER AUTHORIZATION... no biggie.

My question is, I see MASTER and some other DB's have this mapping of DBO to SA. I don't believe that the DB Owner can be set to anything but SA on these db's.

Will disabling the SA account have impact on the opperation of the system databases when DBO is mapped to SA?

Thank you!

Wes

No, DBO is essentially just a default schema and you can make other accounts with different privileges that have separate schemas. Even though, not quite sure how disabling the SA would help in elevating security


Building a conditional check constraint I'm trying to build a conditional check constraint for my table TBL_AFIL, which has two fields:

Fec_Renun DATE
flg_afil INTEGER

Constraint: IF flg_afil=1 THEN Fec_Renun can not be NULL.

ALTER TABLE TBL_AFIL
ADD CONSTRAINT FecRenun_Not_Null CHECK (Fec_Renun IS not NULL and flg_afil =1) ;

But it throws this error:

ORA-02293: cannot validate (FecRenun_Not_Null) - check constraint violated

But when I run this query, it returns 0 rows.

SELECT * FROM TBL_AFIL
WHERE FEC_RENUN IS NULL
AND FLG_AFIL =1;

I know that to this constraint I could add ENABLE NOVALIDATE, but I'm intrigued why can't build this constraint.

CHECK (NOT (flag_afil = 1 AND fec_renum IS NULL))


Postgres 9.1 query plan not using check_constraints on partitions after large data load I've created a table in Postgres 9.1 called 'markets' that contains data about a market in a specific US zip code. I've partitioned the data on the zip code column, using the first digit of the zip code. So I have a 'markets' table and 10 inherited tables such as markets_0, markets_1, etc.

My constraint for each of the inherited table looks like such:

CONSTRAINT markets_0_zip_check CHECK ("left"(zip::text, 1) = '0'::text)
CONSTRAINT markets_1_zip_check CHECK ("left"(zip::text, 1) = '1'::text)

and so forth, for child tables markets_0 through markets_9. I also have an insert trigger that handles storing the data in the proper table.

I loaded a few millions rows and ran EXPLAIN on the queries and everything looked fine when I queried on zip code. The query planner went straight to the appropriate table. However, after loading about 12 million rows of data, the query planner is now looking at all 10 tables, even though I'm querying on the zip code.

For example, a query such as this:

EXPLAIN select * from markets where zip='75254'

produces this:

    ->  Seq Scan on markets  (cost=0.00..0.00 rows=1 width=116) (actual time=0.002..0.002 rows=0 loops=1)"
          Filter: ((zip)::text = '75254'::text)"
    ->  Index Scan using index_markets_0_on_zip on markets_0 markets  (cost=0.00..8.52 rows=1 width=118) (actual time=0.021..0.021 rows=0 loops=1)"
          Index Cond: ((zip)::text = '75254'::text)"
    ->  Index Scan using index_markets_1_on_zip on markets_1 markets  (cost=0.00..8.54 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)"
          Index Cond: ((zip)::text = '75254'::text)"
   ->  Index Scan using index_markets_2_zip on markets_2 markets  (cost=0.00..8.39 rows=1 width=118) (actual time=0.014..0.014 rows=0 loops=1)
          Index Cond: ((zip)::text = '75254'::text)

and so forth for each of the 9 tables.

So here is what I've done to trouble-shoot this:

I've verified that SET constraint_exclusion = partition; is enabled in my postgresql.conf file. I've tried setting this in the query itself with no difference in the results.
I've verified that there are no overlapping constraints or zip codes that don't start with 0-9.
I've verified that the data is being stored in each table properly.
I've run VACUUM ANALYZE on each of the tables; just in case.

I have other partitioned tables that are working fine on the same server, so I'm stumped as to why this is no longer working.

Any ideas?

A couple comments given the answer here.  First PostgreSQL currently does not check the where clause functions to see if all equality constraints can be met properly on all tables.  So you have to specify the condition for the partition often manually in the where clause.

A major point I would make here would be that if you are not doing a lot of frequent bulk operations, you may find that partitioning is not a net win here.  You may find that partial indexes get you what you want in a more flexible manner.  For example you could have a series of partial indexes:

CREATE INDEX markets_0_idx markets (id) WHERE zip between '00000' AND '09999';

and so forth.  However for a single zip-code lookup you are probably best to have just an index on zip.  Premature optimization is the root of all evil.


how to config SSRS 2005 email setting with google apps My Company recently switches email service to google apps (asp.google.com). all the current subscriptions in SSRS2005 are configured to MS exchange server and no longer workable. Do someone knows how to configure subscriptions to google apps. Thanks in advance.

This msdn walkthrough should have everything you need to accomplish what you want to do on the SSRS 2005 side.  Pay attention to the section on setting up for remote smtp.  Especially the part about setting SendUsing to 2.
This google guide provides the steps to configure things on their end.

The setup for SSRS 2008 isn't much different.


Any Tricks to Better Identify Disabled SSIS Elements? Does anyone have any tricks they care to share that will help better identify disabled elements within SSIS packages?  Ideally there's some manner to adjust the text color to red or or something along those lines, but the default behavior seems significantly lacking in my mind.  I've dug through a number of options, but nothing seems to impact the display of only the disabled elements.

Here's an example image where 4 of the 6 elements are disabled:

As much as I'd love to fall back to consistent SSIS development practices, these packages are mainly inherited, so I'm stuck with a mixed bag of approaches to work with.

This screenshot was taken from within Visual Studio 2012, but I believe the issue exists on a number of other versions as well.

You could always look at the underlying XML:

  DTS:Disabled="True"


What are the different connections of MySql? I have a software(toad for mysql) to connect to my MySql database.
We have to provide some data('username','password' etc) to this Software to connect database.
There is a field known as  'connection type' and it has 6 options

TCP
SSL SSH
NAMED PIPE
MEMMORY
HTTP PROXY
HTTP TUNNEL

Anybody explain about connection type and this six options?

These two are in your list

NAMED PIPE
MEMMORY

Are localized to the machine. i.e. do not use the network and thus are very safe

Named pipe or memory

The others use a network

TCP - Simple connection protocol using IP. See this

HTTP PROXY, HTTP TUNNEL - Uses the HTTP protocol to get over the (sometimes) problems of firewalls and routers). Either get a computer on the firewal just to forward requests on or just encode the reequests on the HTTP protocol (web) so it can pass thru. Both use TCP below it.

SSL - Just encrypts the communiction and uses TCP forthe communication with the encrypted data.

SSH is built upon SSL but does the authentication bit.

TO OTHERS - I HAVE SIMPLIFIED IT A BIT BUT GAVE LINKS


PostgreSQL SELECT primary key as "serial" or "bigserial" I've meshed together a way to determine what the data_type is as in the data_type you use in the syntax when creating a new table based off of the PostgreSQL wiki page.

If there is something wrong with my query I need to actually know what in a given scenario would throw it off on the explicit context of having a query or queries to run on a purely test database/table to modify that database/table so run this query on in order to test for any false-positives.

SELECT pg_attribute.attname,
format_type(pg_attribute.atttypid, pg_attribute.atttypmod),
CASE
WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='bigint' THEN 'bigserial'
WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='integer' THEN 'serial'
END AS type
FROM pg_index, pg_class, pg_attribute
WHERE pg_class.oid = 'delete2'::regclass
AND indrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary;

Here is a table with a primary key that does not return the primary key with this query:

CREATE TABLE delete_key_bigserial (
  test1 integer,
  id bigserial NOT NULL,
  col1 text,
  col2 text,
  test2 integer
);

Your query would fail, because the standard name of an integer is "integer", not "int". You can avoid this kind of error by comparing the internal regtype OID instead of a text representation. Many basic data types have several alias names, they all resolve to the same internal registered type.
That aside, you can largely simplify and improve:

SELECT a.attname
     , CASE a.atttypid
         WHEN 'bigint'::regtype THEN 'bigserial'
         WHEN 'int'::regtype    THEN 'serial'
         ELSE format_type(a.atttypid, a.atttypmod)
       END AS type
FROM   pg_index     i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
WHERE  i.indrelid = 'tbl'::regclass
AND    i.indisprimary
AND    a.attnum = ANY(i.indkey);

While this improves the query, it's still not doing what you hope it would.

Just because an integer column is (part of) the primary key, that doesn't make it a serial column, yet. Here is a detailed assessment of what a serial is:

Safely and cleanly rename tables that use serial primary key columns in Postgres?

You don't find anything for the presented table, because you are basing your query on pg_index, which is completely unrelated to serial types. A serial does not have to be indexed, only primary keys happen to be indexed.

Safe solutions

Just detect the serial type of the PRIMARY KEY:

SELECT a.attrelid::regclass::text, a.attname
     , CASE a.atttypid
         WHEN 'int'::regtype  THEN 'serial'
         WHEN 'int8'::regtype THEN 'bigserial'
         WHEN 'int2'::regtype THEN 'smallserial'
       END AS serial_type
FROM   pg_attribute  a
JOIN   pg_constraint c ON c.conrelid  = a.attrelid
                      AND c.conkey[1] = a.attnum
JOIN   pg_attrdef   ad ON ad.adrelid  = a.attrelid
                      AND ad.adnum    = a.attnum
WHERE  a.attrelid = 'tbl'::regclass   -- table name, optionally schema-qualified
AND    a.attnum > 0
AND    NOT a.attisdropped
AND    a.atttypid = ANY('{int,int8,int2}'::regtype[]) -- integer type
AND    c.contype = 'p'                 -- PK
AND    array_length(c.conkey, 1) = 1   -- single column
AND    pg_get_expr(ad.adbin, ad.adrelid)
     = 'nextval('''
    || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
    || '''::regclass)';                -- col default = nextval from owned seq

Returns nothing if the PK isn't a serial type.

Addressing comment by @jpmc26

A simplified check for just:

pg_get_serial_sequence(attr.attrelid::regclass::text, attr.attname) IS NOT NULL

would only check if there is a sequence "owned" by the column, but not whether the column default is also set to draw numbers from the sequence. The documentation:

  The function probably should have been called pg_get_owned_sequence;
  its current name reflects the fact that it's typically used with
  serial or bigserial columns.

To show ALL columns with proper data type - replaced with the appropriate serial type where applicable:

SELECT a.attrelid::regclass::text, a.attname
     , CASE WHEN a.atttypid = ANY ('{int,int8,int2}'::regtype[])
          AND EXISTS (
             SELECT FROM pg_attrdef ad
             WHERE  ad.adrelid = a.attrelid
             AND    ad.adnum   = a.attnum
             AND    pg_get_expr(ad.adbin, ad.adrelid)
                  = 'nextval('''
                 || (pg_get_serial_sequence (a.attrelid::regclass::text
                                          , a.attname))::regclass
                 || '''::regclass)'
             )
        THEN CASE a.atttypid
                WHEN 'int'::regtype  THEN 'serial'
                WHEN 'int8'::regtype THEN 'bigserial'
                WHEN 'int2'::regtype THEN 'smallserial'
             END
        ELSE format_type(a.atttypid, a.atttypmod)
        END AS data_type
FROM   pg_attribute  a
WHERE  a.attrelid = 'tbl'::regclass  -- table name, optionally schema-qualified
AND    a.attnum > 0
AND    NOT a.attisdropped
ORDER  BY a.attnum;

The check on the column default might break with exotic settings for search_path. Didn't test all combinations.

db<>fiddle here


Dropping unused indexes - Assessing the unexpected dangers We have a very large database with hundreds of unused indexes according to DMV statistics, which have been accumulating since the server was last rebooted in July. One of our DBAs made the following cautionary statements, which don't make sense to me:

Before we drop an index we need to make sure if it not enforcing a
uniqueness constraint, as the query optimizer may need this index to
exist.
Whenever an Index is created, statistics related to that index are
also created in SQL Server. A query might not be using the index but
it might be using its statistics. So we may run into a situation ,
after dropping an index a particular query performance goes really
bad. SQL Server does not keep the usage stats of statistics.
Although we have “Auto Create Statistics” feature enabled on our
database, I don’t know which all parameters have to be met
internally before the query optimizer will create the missing
statistics.

Regarding #1, it seems to me that SQL Server would actually do a seek on the index to determine uniqueness before an insert / update is done, and therefore, the index wouldn't show as being not used.

Regarding #2, is this really possible?

By the way, when I say an index is not used, I mean no seeks and no scans.

Your DBA's concerns are both valid.

  Regarding #1, it seems to me that SQL Server would actually do a seek on the index to determine uniqueness before an insert / update is done, and therefore, the index wouldn't show as being not used.

The uniqueness guarantee may be used by the optimizer in deciding which logical transformations or physical operations can be used to obtain correct results. The fact that the optimizer relies on a uniqueness guarantee to, for example, transform an aggregation or choose a one-to-many merge join, will not be reflected in index usage statistics, unless the index is also physically accessed in the final execution plan. One should therefore be very careful about removing (or disabling) any unique index or constraint.

  Regarding #2, is this really possible?

Yes, it is possible for the optimizer to use statistics associated with an index without the final execution plan featuring any access using that index. The processes of loading 'interesting' statistics, calculating cardinality estimates, and producing a finished execution plan are quite independent activities.

Dropping the index would also remove the associated index statistics, which may affect plan quality next time the statement is recompiled. The index statistics may be used in a cardinality estimation calculation that the final plan relies on, even where the index is not physically present in the final plan.

Your DBA knows his/her stuff.

None of this should be taken to mean that apparently-unused indexes should never be removed. I'm simply saying that your DBA's concerns are valid ones, and you should plan the change with them accordingly, with appropriate testing, and a recovery plan. In my experience, point #1 is more likely to be problematic than #2, but I have no way of knowing whether that applies to your situation.


Are these two queries technically equal to each other? Ran it in my query browser. I was am getting same results for both. But still need a confirmation.

SELECT
    cs.RSCode
    , CustomerName
    , em.EmailId
FROM
    Customer_STN cs
    JOIN EmployeeMaster em ON cs.RSCode = em.UserName
    LEFT OUTER JOIN Confirmed_Attendance ca ON cs.RSCode = ca.rsCode
WHERE
    isConfirmed=1
AND
    dateOfConfirmation = '2012-08-25'

Second query:

SELECT
    cs.RSCode
    , CustomerName
    , em.EmailId
FROM
    Customer_STN cs
JOIN
    EmployeeMaster em ON cs.RSCode = em.UserName
WHERE
    RSCode NOT IN (
        SELECT
             rsCode
        FROM
             Confirmed_Attendance
        WHERE
             dateOfConfirmation = '2012-08-25'
        AND
             isConfirmed = 1
)

EDIT: Added the WHERE part as Rob noticed. Not so similar any more.

No. But almost.

There are four things that joins do. I wrote about this at: JOIN simplification in SQL Server

Your "NOT IN" (which you should be careful of, regarding NULLs - try using NOT EXISTS) won't duplicate any rows. A LEFT JOIN can. But consider the use of Unique Indexes/Constraints/PKs, which can help the Query Optimizer treat these as identical.

On the whole, use NOT EXISTS rather than LEFT JOIN, because NOT EXISTS will get treated as an Anti Semi-Join, which is slightly better than what a LEFT JOIN / NOT NULL does. I show this at: Joins without JOIN

So... based on my assumption of what your queries were meant to be, the answer is:

Almost. (And how 'almost' depends on the metadata involved)


Optimize JOIN query with DISTINCT I have 2 tables, one for links (Links) and one for comments (Comments) where each comment contains a reference to a link (it can also point to NULL if it has no associated link, such comments we disregard for a moment). I want to order links such that links with newest comments are on top.

Currently, I do this:

SELECT DISTINCT linkId FROM Comments WHERE
  linkId IS NOT NULL AND
  dateAdded <= ?
ORDER BY id DESC
LIMIT 10

This is working very well (around 1ms with current 60k rows, I've tested it also with 800k rows, it still performs just as well)

However, now I'd like to classify the links into different categories and therefore I created a new column category in Links table and I want to order the links within their category. I tried running following query:

SELECT DISTINCT c.linkId FROM Comments c LEFT JOIN Links l ON c.linkId = l.id WHERE
  c.linkId IS NOT NULL AND
  c.dateAdded <= ? AND
  l.category = ?
ORDER BY c.id DESC
LIMIT 10

This makes the query execution time become ~1.5s with the table with 800k rows so it is obviously not scaling well.

The tables are defined as follows:

CREATE TABLE `Links` (
  `id` int(10) UNSIGNED NOT NULL,
  `category` int(10) UNSIGNED NOT NULL,
  `url` varchar(2083) CHARACTER SET utf8mb4 NOT NULL,
  `dateAdded` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `Links`
  ADD PRIMARY KEY (`id`),
  ADD KEY `category` (`category`);

CREATE TABLE `Comments` (
  `id` int(10) UNSIGNED NOT NULL,
  `linkId` int(10) UNSIGNED DEFAULT NULL,
  `content` varchar(2000) CHARACTER SET utf8mb4 NOT NULL,
  `dateAdded` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `Comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx2` (`linkId`,`dateAdded`);
ALTER TABLE `Comments`
  ADD CONSTRAINT `fk_link_id` FOREIGN KEY (`linkId`) REFERENCES `Links` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Is there any way how this query can be optimized?

I guess creating category column in Comments table would help but that could lead to inconsistencies (i.e. link pointing to one category but its links pointing to another category).

Because of filtering on two tables, it is not possible for a single index to handle all the WHERE, to handle the ORDER BY, much less optimize theLIMIT 10`.

The LEFT JOIN seems to be a JOIN.

This link on comments may help:  INDEX(dateAdded, linkId, id) (in that order).

If you want to discuss further, please provide EXPLAIN SELECT ... and EXPLAIN FORMAT=JSON SELECT ...


Are Stored Procedure failures logged by SQL Server? How can I see a list of stored procedure errors over a given timespan in SQL Server Management Studio 2008?

You can't, unless the procedure has code to write to the error log, or it results in a certain severity.

Investigate using TRY...CATCH error handling.   You should be able to capture errors and then write those to the error log.


full outer join not working I Want to do a full outer join on 2 tables but I get this error, what am I doing wrong?

mysql> select * from cajas full outer join almacenes on almacenes.codigo = cajas.almacen;

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join almacenes on almacenes.codigo = cajas.almacen' at line 1
  mysql>

So far as I know, you can't do a full outer join in MySQL, so just run it as the UNION of a LEFT JOIN and a RIGHT JOIN as

SELECT * FROM
cajas LEFT JOIN almacenes ON almacenes.codigo = cajas.almacen
UNION
SELECT * FROM
cajas RIGHT JOIN almacenes ON almacenes.codigo = cajas.almacen;

I think this would fix your problem.

EDIT: After a quick Googling, I realized that the above may not be perfect for all cases.  Instead you could do

SELECT * FROM
cajas LEFT JOIN almacenes ON almacenes.codigo = cajas.almacen
UNION ALL
SELECT * FROM
cajas RIGHT JOIN almacenes ON almacenes.codigo = cajas.almacen
WHERE cajas.almacen IS NULL;

See this blog post for reference.


How can I modify this trigger with loops and concat? I have this trigger below which inserts new values in the an emplog table. I want to have my trigger insert in the description column the event that took place. For example, if I changed the last_name I want it to record:

  old.lastname was changed to new.last_name

If its the first name or gender or DOB or marital or SSN that were updated it should do the same.

How can I achieve this? Can I use the concat function and loops?

CREATE TRIGGER emplog_update AFTER UPDATE ON emp
FOR EACH ROW
INSERT INTO emplog
VALUES (
    NEW.id
    ,NEW.lastname
    ,NEW.firstname
    ,NEW.gender
    ,NEW.dob
    ,NEW.marital
    ,NEW.SSN
    ,'U'
    ,NULL
    ,USER()
    ,('this is where the description will go')
    );

How about expanding this a little and do a proper log table, logging every update, insert or delete. That way you can keep a proper history.
For this, in MySQL, you will need three triggers :

CREATE TABLE EMP ( ID INT,
                   LASTNAME varchar(100),
                   firstname varchar(100),
                   gender varchar(1),
                   dob date,
                   marital varchar(1),
                   ssn varchar(20)
                 );

CREATE TABLE EMPLOG ( empid INT,
                   operation varchar(1),
                   operationdate date,
                   operated_by varchar(200),
                   LASTNAME varchar(100),
                   firstname varchar(100),
                   gender varchar(1),
                   dob date,
                   marital varchar(1),
                   ssn varchar(20)
                 );

CREATE TRIGGER emplog_insert AFTER INSERT ON emp
FOR EACH ROW
INSERT INTO emplog VALUES
(NEW.id,
 'I',
 Now(),
 USER(),
 NEW.lastname,
 NEW.firstname,
 NEW.gender,
 NEW.dob,
 NEW.marital,
 NEW.SSN
);

CREATE TRIGGER emplog_update AFTER UPDATE ON emp
FOR EACH ROW
INSERT INTO emplog VALUES
(NEW.id,
 'U',
 Now(),
 USER(),
 NEW.lastname,
 NEW.firstname,
 NEW.gender,
 NEW.dob,
 NEW.marital,
 NEW.SSN
);

CREATE TRIGGER emplog_delete AFTER DELETE ON emp
FOR EACH ROW
INSERT INTO emplog VALUES
(OLD.id,
 'D',
 Now(),
 USER(),
 OLD.lastname,
 OLD.firstname,
 OLD.gender,
 OLD.dob,
 OLD.marital,
 OLD.SSN
);

If you then wish to check out the history of what happened for a specific employee, you simply run this query :

select *
from Emplog
where empid = ?
order by operationdate


Why can a SQL Server table not have more than one IDENTITY column? Why can a SQL Server table not have more than one identity column?

CREATE TABLE t(id INT IDENTITY, id2 INT IDENTITY)

  Msg 2744, Level 16, State 2, Line 5
  Multiple identity columns specified for table 't'.
  Only one identity column per table is allowed.

I understand we can resolve it using a computed column.

As per the product documentation, a table can not have more than one identity column. But, why? What is the real reason behind it?

I don't think there's any real "internals" reason. The metadata is stored at column level not table level. It would need a rethink though of scalar functions such as scope_identity() and pseudo column syntax such as $identity as there would now be ambiguities.

Philosophically if the purpose of identity is to produce something that uniquely identifies an entity why would you need two different arbitrary calculated values acting in that role?

And where is the benefit anyway? This is a cross site dupe so I'll repeat my example from SO.

  An Identity Column in SQL Server has a seed and an auto increment. We
  could always calculate what the 2nd hypothetical id value should be if
  we knew the value of the first id column anyway.

  e.g. If this was legal syntax

create table #foo
(
bar int identity(1,10),
baz int identity(1000,1)
)

  We wouldn't need to store baz as it could be calculated from bar as
  follows.

baz = 1000 + (bar-1)/10

Since SQL Server 2012 you can knock yourself out and add as many columns using sequence defaults as you want to a table though. For example:

CREATE SEQUENCE dbo.Sequence1
    AS integer
    START WITH 1
    INCREMENT BY 1
    MAXVALUE 1000
    CYCLE
    CACHE 50;

CREATE SEQUENCE dbo.Sequence2
    AS decimal(5,0)
    START WITH 5
    INCREMENT BY 10
    MAXVALUE 250
    CYCLE
    CACHE 50;

CREATE TABLE dbo.T
(
    id integer NOT NULL DEFAULT NEXT VALUE FOR dbo.Sequence1,
    id2 decimal(5,0) NOT NULL DEFAULT NEXT VALUE FOR dbo.Sequence2,
);

INSERT dbo.T DEFAULT VALUES;
INSERT dbo.T DEFAULT VALUES;
INSERT dbo.T DEFAULT VALUES;

SELECT * FROM dbo.T;

DROP TABLE dbo.T;

DROP SEQUENCE
    dbo.Sequence1,
    dbo.Sequence2;


PSQL not executing SQL command - Powershell I have a simple script that should execute a select statement and output it to a CSV. My problem is that the script connects to the database and hangs waiting for my sql instructions (which are on the script and should execute automatically). Any help would be greatly appreciated.

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
.\psql --% -U postgres -w myDB
.\psql --% -c "copy {'SELECT * FROM myTable';} TO    'C:\Users\e\Desktop\test1.csv' CSV DELIMITER ',';"

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
.\psql --% -U postgres -w myDB
.\psql --% -c "copy {'SELECT * FROM myTable';} TO    'C:\Users\e\Desktop\test1.csv' CSV DELIMITER ',';"

You invoke psql twice here. The first enters interctive mode as no stdin was supplied, no -f filename parameter was supplied, and there's no -c command string.

So the script never gets to the second psql invocation. Put the parameters on one line.

Additionally:

You want WITH (CSV, HEADER) not CSV DELIMITER ','
{'SELECT * FROM myTable';} is complete nonsense. Simply use COPY mytable TO ... or if you need a subquery, use COPY (SELECT * FROM mytable) TO ...
If you're trying to do a line continuation, use  a backtick ` at the end of the line.
Unless you intend to use server-side paths, with the file permissions of the PostgreSQL server, you probably want \copy not COPY

(I'm assuming that --% stops Powershell from looking for Powershell flags and options in the rest of the command; I haven't seen it before).

I think you're looking for something more like:

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
.\psql --% -U postgres -w myDB -c "\copy (SELECT * FROM myTable) TO    'C:\Users\e\Desktop\test1.csv' WITH (FORMAT CSV, HEADER);"


Keep stored procedures / UDF in separate database Is it good practice to keep all stored procedures in separate database which has no data and all tables with real data in separate database(s)? Main reason: to be able to upgrade stored procedures with simple restore which does not affect the production data. Second (optional) reason: 2+ databases can be processed by the same set of stored procedures.

I have never seen that approach used.  If you are keeping your database schema in a source control system, then you can generate deployment scripts.  Such tools can compare previous versions of your schema and deploy the changes.  SQL Server Data Tools is available in Visual Studio.


MySQL 8 Geospatial Queries, MakeEnvelope Workaround? I'm trying to work with some data from Google Maps and have a few locations along with their bounding boxs (northeast, southwest) data stored.

Using that data, I have the following query...

SELECT
    *
FROM
    `address_components`
WHERE
    MBRContains (
        ST_MakeEnvelope (
            ST_GeomFromText ( 'POINT(39.643950680292 -79.981536819708)', 4326 ),
            ST_GeomFromText ( 'POINT(39.641252719708 -79.984234780292)', 4326 )
        ),
        `location`
    );

The problem I'm running into with this though is that it's saying:

  3618 - st_makeenvelope(POINT, POINT) has not been implemented for geographic spatial reference systems.

How can I work around this issue?

Additionally, how would I change this so that instead of hard-coding my bounding box, I can select a row based on a place_id and then find all records that its location falls within the envelope of?

There is clearly no work around for this issue.

MySQL can't do this operation (and many others) on a geographic srs.
You can't reproject in MySQL.

Oracle MySQL doesn't want to compete with Oracle Spatial. So you're SOL. MariaDB is currently behind MySQL in GIS too and it's a low-priority item for them. Either reproject to something Web Mercator and do Cartesian bounding box stuff, or PostgreSQL and PostGIS, which can do geographic bounding boxes, and on an index at that. And, also ST_Transform from one SRS to another.

PostGIS is the goto solution for GIS.


Seperate Address Table for Patient and Emergency Contact Person? Is it good to separate address table for patient and emergency contact person?

Currently this are my tables

Patients

id

first_name

last_name

status

gender

PatientAddresses

id

patient_id

address_line_1

address_line_2

address_line_3

city

postal_code

state

country

EmergencyContactPersons

id

patient_id

first_name

last_name

EmergencyContactPersonAddresses

id

emergency_contact_person_id

address_line_1

address_line_2

address_line_3

city

postal_code

state

country

OR THIS IS BETTER?

Addresses

id

address_line_1

address_line_2

address_line_3

city

postal_code

state

country

EmergencyContactPersonAddresses

id

emergency_contact_person_id

address_id

PatientAddresses

id

patient_id

address_id

The second approach is much better. One massive advantage is that both patient and emergency contact could have the same address. Typically an emergency contact is a parent or spouse, so you could have a checkbox in your application that says "Emergency contact's address is same as Patient's". This would save on space in the database, add convenience for users of your application without any extra development effort from yourself.


How To Access My PostgreSQL Server From Another PC Securely? I want to be able to connect from any PC anywhere to my Database.

Host

OS: Windows 10
PostgreSQL: 12.0

Currently all Tutorials say the same: (Thats what i also did)

Change listen_addresses to listen_addresses = '*' in the postgresql.conf
Add host all all 0.0.0.0/0 md5 in the pg_hba.conf

But they don't say how to connect from the Client Side and they don't pinpoint how secure it is to do so.

Let's say at the Host I Did the above steps and restarted.
I created a Database called TablesTest.

How do I connect to this database at the Client? How secure is it?

How to connect should be trivial: fill the appropriate values into the pgAdmin dialog (host, port, user, database). If I remember correctly, pgAdmin calls the database "administrative database" or similar.

This is fairly secure, if you want to do better, here are some ideas:

Use the scram-sha-256 authentication method rather than md5. This requires that you change password_encryption to scram-sha-256 on the server and assign a new password to the user.
Limit the pg_hba.conf entry so that only one user can connect to one database from a single host, for example

host databasename username 12.34.56.78/32 scram-sha-256

For better security, don't allow remote connections as a superuser.
For transport security, you can enable SSL connections. Without that, the communication between database client and database server is unencrypted.


Why should pupbld.sql be run as SYSTEM (and not as SYS)? pupbld.sql
--
-- DESCRIPTION
--   Script to install the SQL*Plus PRODUCT_USER_PROFILE tables.  These
--   tables allow SQL*Plus to disable commands per user.  The tables
--   are used only by SQL*Plus and do not affect other client tools
--   that access the database.  Refer to the SQL*Plus manual for table
--   usage information.
--
--   This script should be run on every database that SQL*Plus connects
--   to, even if the tables are not used to restrict commands.

-- USAGE
--   sqlplus system/<system_password> @pupbld
--
--   **Connect as SYSTEM before running this script**

-- If PRODUCT_USER_PROFILE exists, use its values and drop it

What are the specific reasons to run it as SYSTEM?

The script creates certain objects, and it will put them in the current schema. Oracle wishes that these objects be created on SYSTEM schema, hence the requirement.

There are two Oracle schemas, SYS and SYSTEM, because they have two purposes. In short, SYS truly is the "special" user in respect to database internals, while SYSTEM works normally.

The SYS is the owner of the database, the only user with access to X$ tables, the only user which acts as SYSDBA (internally), that bypasses logon triggers, and that, for some obscure reason, cannot obtain read-consistent view of data. As a schema it holds crucial Oracle stuff, mainly the data dictionary.

The SYSTEM is fairly normal schema with DBA privilege (database administrator, not the same as SYSDBA), only that it is built-in and contains some additional (but also quite important) Oracle stuff. You shouldn't put your own stuff in this schema, if not instructed so by Oracle.


Cumulative Subtraction from external input I have an external value 20. I need 1 to be subtracted from 20 (=> 19) and then 2 to be subtracted from 19 (=> 17) and so on.

Data:

Serial No | Value1 | External value(20)
       A  |     1  |                19
       B  |     2  |                17
       C  |     3  |                14
       D  |     4  |                10
       E  |     5  |                 5

How can I do it?

I supposed that the third column is the result of your subtraction and doesn't exist in your main table.

Query:

SELECT `Serial`, `Value1`
    , (@value := @value-Value1) `external value`
FROM `data` d
    , (SELECT @value := 20) v
ORDER BY `Serial`

The query uses a variable which is set to 20 at the beginning, cross join it to the data table and subtract each Value1 one by one. Value1 are order by Serial, from A to E. The order seems to be A to E but based on real data, you will have to order it according to your needs.

Output:

Serial | Value1 | external value
-------|--------|----------------
     A |      1 |             19
     B |      2 |             17
     C |      3 |             14
     D |      4 |             10
     E |      5 |              5

Sample (sqlfiddle):

CREATE TABLE data(Serial char, Value1 int);
INSERT INTO data(Serial, Value1) VALUES
  ('A', 1),
  ('B', 2),
  ('C', 3),
  ('D', 4),
  ('E', 5);


Reduce SQL Server Memory used by a Database For weeks I have been at more than 25k Page Life Expectancy, since last sunday on my UAT Server the Page Life Expectancy Dropped from ~25k to 400/500/600, I can't identify what cause this huge drop as this is a UAT Server no processes running during the weekend and nobody was connected.

Apart of this I identified that 95% of my buffer pages are allocated into one specific table on a clustered Index...

The application that access this table does a high amount of Adhoc queries without any parameterization (3rd Vendor Application, nothing I can touch...) I don't know if there is actually something I can do to leverage this RAM usage as they are consuming more than 50GB of RAM and I'm starting to face memory pressure BCHR <95% and  low Page Life Expectancy!

Version:

Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64)
Jan  7 2016 14:39:01
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Any help will be appreciated, Thanks!

Your best option is going to be using Resource Governor. Just create a resource for that vendor app and throttle the memory down for it.

You just have to come up with what you are going to identify that workload with, you are allowed:

The ability to classify incoming connections and route their
workloads to a specific group.
The ability to monitor resource usage for each workload in a group.
The ability to pool resources and set pool-specific limits on CPU
usage and memory allocation. This prevents or minimizes the
probability of run-away queries.
The ability to associate grouped workloads with a specific pool of
resources.
The ability to identify and set priorities for workloads.


Refactor two one-to-one and one-to-many relationships in a single table I have following database schema (simplified).

Requirements

One account must have one sub-account
One sub-account, depending on type, might have other sub-accounts (nested hierarchy).

Problems I am having

Database doesn't guarantee consistency, as I can insert account without a sub-account.
Joining account and the main sub-account might result in multiple rows if sub-account has other sub-accounts when only account and the main sub-account data is needed.
Need a way to identify main sub-accounts in the sub-accounts table. ParentSubAccountId IS NULL works but not sure if it's the cleanest way to do it.

Notes

The main sub-account contains exactly the same fields, as other sub-accounts, hence was originally placed in the same table.
Sub-account children could be retrieved as a flat list with parent sub-account IDs, and hierarchical structure will be restored at software layer.

Options considered

This solves #1 and #2 problems, #3 stays the same, however I need to perform recursive select to retrieve all sub-accounts for an account.
Questions
Is there a better approach to improve database design than the option I've considered?

The option I considered initially was a good start, just needed further improving. The schema of the solution is below.

The solution consists of two parts actually:

Add unique constraint on SubAccountId
Introduce table for SubAccount children hierarchy mapping, both columns have foreign key to Id of the SubAccount table.

Children nested hierarchy is restored at software layer, the adjacency list hierarchy was not really needed.


Trace flag and which need to be turned off and Why In SQL server there are so many trace flags. Why some of them need to be turned off. Some where i saw that trace flag 8017 need to be turned off. So i want to know which trace flag need to be tuned off and Why?

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

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

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

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


WIN10 -Unable to run MySql server after installation - 5.7 I previously had MySql 5.7 installed and it worked fine.  I last used it 3-5 months ago.  When trying to run the server/daemon I ran into the following error:

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld
mysqld: Can't change dir to 'C:\Program Files\MySQL\MySQL Server 5.7\data\' (Errcode: 2 - No such file or directory)
2017-10-09T23:44:49.897072Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-10-09T23:44:49.897072Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-10-09T23:44:49.898074Z 0 [Note] mysqld (mysqld 5.7.19) starting as process 39932 ...
2017-10-09T23:44:49.901585Z 0 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.7\data\MSI.lower-test
2017-10-09T23:44:49.901585Z 0 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.7\data\MSI.lower-test
2017-10-09T23:44:49.902588Z 0 [ERROR] failed to set datadir to C:\Program Files\MySQL\MySQL Server 5.7\data\
2017-10-09T23:44:49.903592Z 0 [ERROR] Aborting

2017-10-09T23:44:49.904597Z 0 [Note] Binlog end
2017-10-09T23:44:49.905096Z 0 [Note] mysqld: Shutdown complete

I uninstalled all MYSQL programs via Control Panel and then reinstalled from the site but I still receive the exact same error messages.  Does anyone have any advice for what to do?

Thanks in advance

A few potential issues:

In your my.ini file, remove the trailing \ from the datadir variable, and change the remaining final \ to /. You path will look like ...\MySQL\MySQL Server 5.7/data
Whatever user you are running mysqld as may not have permissions on C:\Program Files\MySQL\MySQL Server 5.7\data. If you installed as administrator, try running command prompt as administrator. If there is a service registered for MySQL, open up services.msc and start it from there.


How can I pass parameter to stored procedure through a query? I have this stored procedure:

CREATE PROCEDURE `test`(OUT `vote_value` INT, OUT `score` INT, IN `user_id` INT, IN `time_range` INT)
    NO SQL
BEGIN
    SELECT coalesce(sum(vote_value), 0), coalesce(sum(score), 0)
      INTO vote_value, score
    FROM reputations
    WHERE owner_id = user_id
      AND date_time > CASE time_range
          WHEN 'WEEK'
          THEN  unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK))
          WHEN 'MONTH'
          THEN  unix_timestamp(DATE_SUB(now(), INTERVAL 1 MONTH))
          WHEN 'YEAR'
          THEN  unix_timestamp(DATE_SUB(now(), INTERVAL 1 YEAR))
          ELSE 1 END;
END

As you see my procedure has 4 parameters. Both vote_value and score have OUT direction (as result), And both user_id and time_range have IN Direction. So I have to pass those two which have IN direction when I call the procedure.

Ok, I can call procedure about like this:

CALL test(@vote_value, @score, 10, 'WEEK');
SELECT @vote_value, @score;

It would work as well. But please focus on the third parameter which is 10. I don't want to pass it manually by hand. I want it be a value from a query. Something like this:

CALL test(@vote_value, @score, u.id, 'WEEK');
SELECT id, @vote_value, @score FROM Users u;

But it throws syntax error. Does anybody know how can I do such a thing? The expected result is this.

You can pass third parameter value as

CALL test(@vote_value, @score, (select u.id from Users u), 'WEEK');

Please put appropriate WHERE clause in above inline query.


Recovering a lost datafile with no backup available How do you recover a lost datafile if:

the database is running in archivelog mode
there is no backup is available

If you switched your database to ARCHIVELOG mode, all previous backups are invalidated and thus cannot be used for recovery; therefore, you have to do the full backup of the database immediately after switching it to the ARCHIVELOG mode, to make archive redo logs useful.

If you have a full backup(s) of database made before failure and all archive logs, you can easily restore the data file(s) and archive logs from the backup(s) and recover the database or data file(s) to a point of failure applying restored archive logs to the restored data file(s).

If you however don't have backups of database and archive logs made after switching, then, generally, you're out of luck. Though as @Mat pointed out, sometimes you can give the survived bits of data (archive logs and control file) a go at recovering your lost data file.

What can be useful for you is Data Recovery Advisor (DRA, read "Data Recovery Advisor" section in Database Concepts for more info). It can help you to recover database, and is specifically designed for situations like yours when you're not sure how to recover the database or have the time restrictions for the system downtime. It can well save you a lot of time during recovery, reducing downtime caused by failures.

To determine if you can do something about your lost data file, start RMAN:

$ rman target=/

List failures captured by Oracle:

RMAN> list failure;

(The information about failures listed is collected and stored in Automatic Diagnostic Repository (ADR), and is available even when the database is down. Read "Fault Diagnosability Infrastructure Overview" section in Database Administrator's Guide for more info.)

If some open failures are returned (they will actually be returned in your case since you lost one of data files), ask the DRA about advice:

RMAN> advise failure;

Read the output of the last command thoroughly and implement the recommended or required manual actions (if there are). If there are no manual actions listed, and the DRA prepared the repair scirpt, ask the DRA to repair the failures:

RMAN> repair failure;

The DRA is also available from Software and Support tab in EM Database Control.


How to set unique columns combination see this table:

    CREATE TABLE IF NOT EXISTS `default_messages_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_id` int(11) NOT NULL,
  `owner_user_id` int(11) NOT NULL,
  `to_user_id` mediumint(8) NOT NULL
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

I need to set only one possible combination of the columns message_id, owner_user_id, to_user_id meaning the combination (for example) 1,1,1 only can exists one time so no duplicates is allowed. How I do this? Creating an index between them?

Cheers and thanks in advance

A UNIQUE constraint (or a unique index, mysql does not distinguish much between the two concepts of constraint and index) can be composed from more than one columns (2, 3, ...), as needed. Example for your case with a constraint made from 3 columns:

ALTER TABLE default_messages_users
  ADD CONSTRAINT Unique__message__owner_user__to_user   --- you choose a sensible name
                                                        --- for the constraint
    UNIQUE message_id_owner_user_id_to_user_id_UQ       --- and the index
      (message_id, owner_user_id, to_user_id) ;


Nonclustered Index Insert Say I have a table like this:

create table SomeTable
(
    id int identity(1, 1) not null primary key clustered,
    SomeString1 varchar(50) not null,
    SomeString2 varchar(50) not null
)
go

create nonclustered index IX_SomeString1
on SomeTable(SomeString1)
go

If I was to do this:

insert into SomeTable(SomeString1, SomeString2)
values('foo', 'bar')
go

And view the actual execution plan, I only see a Clustered Index Insert.  Why am I not seeing a Nonclustered Index Insert in the execution plan?

For a single row insert you get a narrow/per-row plan

INSERT INTO SomeTable(SomeString1, SomeString2)
SELECT TOP 1 type, type
FROM master..spt_values

If you select the Clustered Index Insert Operator and View the properties window you can see the same information as shown in the XML.

If you try for 1,000 rows

INSERT INTO SomeTable(SomeString1, SomeString2)
SELECT TOP 1000 type, type
FROM master..spt_values

You get a different wide/per-index plan with the operations split out separately

See Wide vs. Narrow Plans or Craig Freedman's blog for more information about the two


If i issue a rollback when recovery mode is bulk logged, will my bulk operations be rolled back? If my recovery model is BULK LOGGED, I'm in a transaction, and I've issued some statements that are minimally logged like SELECT * INTO, will a ROLLBACK undo those minimally logged statements?

I decided asking here was just being ultra-lazy so tested it myself.

create table tmp_insert_in_txn ( something int not null primary key )
begin transaction
    select 1 as something into tmp_created_in_txn
    -- show the value was inserted
    select something as in_txn from tmp_created_in_txn

    insert into tmp_insert_in_txn  with ( tablock )
    (   something   )
    select something from tmp_created_in_txn

    -- show the value was inserted
    select something as in_txn from tmp_insert_in_txn
rollback
-- no rows after rollback:
select * from tmp_insert_in_txn
-- no table after rollback:
select * from tmp_created_in_txn

This shows that the rollback does remove the table tmp_test and the row from tmp_test2, so the answer is that minimally logged operations are still rolled back.

Hmm... so how come using them prevents the transaction log from getting really big?


How can I manage to view if a table has been read recently in postgresql 12? How can I manage to view if a table has been read recently in postgresql 12 ?

Unless the table is only ever accessed via stored procedures or other programmed logic and you have that set to audit access, and measures are taken to stop access by more direct means, then you probably can't.
DBMSs usually don't log read access in a manner that you could query this way, if at all.


High CPU usage on Azure SQL Server ~1.6m rows I'm currently running into an issue with one of our databases in Azure where 1 table is forcing us to scale the entire database up to cope with the CPU requirements. The basic structure of the table is as follows:

column_a is unique and not nullable. column_b can be null.
This table has ~1.6 million rows in it so it's not a large table but it is used throughout the system we have built. We have several feeds pushing data in which requires an entry in this table so for each new request we receive there is a check for an entry and then if that doesn't exist one is created and the id returned. Once that data is received, we have subscribers which receive that data and use this table to link other information the system holds in a nice interface.

The table above shows the most expensive queries being run on this database over a 24 hour period. The one highlighted in yellow is SELECT TOP (1) id FROM TableName WHERE column_a = @param1.
Is this just bad database architecture or are we missing a trick to optimize for the number of reads we are performing on the table? Unfortunately, the number of reads being performed is only going to increase as we are adding new data feeds every month and we are aiming to have 5 - 10x the number of feeds by the end of this year.
Any help is much appreciated and my apologies if any of the above is unclear.
Edit
Create Table query
CREATE TABLE [dbo].[TableName](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [column_a] [varchar](50) NOT NULL,
    [column_b] [varchar](50) NULL,
 CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [AK_Column_A] UNIQUE NONCLUSTERED
(
    [plate] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Edit 2
Please understand that I have had to redact the names.

I would say the issue is the # of executions. When u take the time per execution for two first rows, its about 0.8 seconds per row. But the select is executed so often It eats up cpu by comparision.
In adition, what is the datatype of @param?


Sql Server - Select while insert on another transaction gives unexpected results I stumbled at situation that changes my knowledge about transactions and locking fundamentally (I don't know much though), and I need help to understand it.

Let's say I have a table like this:

CREATE TABLE [dbo].[SomeTable](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SomeData] [varchar](200) NOT NULL,
[Moment] [datetime] NOT NULL,
[SomeInt] [bigint] NOT NULL
) ON [PRIMARY]

and I run this "insert 1000 rows within a transaction" query:

BEGIN TRAN t1

DECLARE @i INT = 0

WHILE @i < 1000
BEGIN
    SET @i = @i + 1

    INSERT INTO [SomeTable] ([SomeData] ,Moment, SomeInt)
    VALUES (CONVERT(VARCHAR(255), NEWID()), getdate(), @i)

    WAITFOR DELAY '00:00:00:010'
END

COMMIT TRAN t1

While this transaction is running, I'm executing a simple select:

SELECT Id, Moment, SomeData, SomeInt FROM [SomeTable]

It isn't always possible to reproduce it (apparently depends on timings) but sometimes select query will, after insert transaction finishes, return less than 1000 rows. In my ignorance I have believed that select will always return 1000 rows (given that isolation level is Read Committed), but obviously I have misunderstood how transactions and locking work.

However, if I put a primary key on Id column (which generates a clustered index), select query will, as long as I've tried, return all 1000 rows. Putting indexes some other way, with clustered index on composite key and a non-clustered index on some other columns, may again result in returning less number of rows than I've expected.

So, I have these questions:

Why select doesn't always return all rows committed by transaction?
If this is expected behavior what's the best way of actually making it work as I have expected? Basically, I want select to return the state of the table after (or before) the transaction, not some half-done data. Snapshot isolation is currently not an option. Putting TABLOCK seems to be doing the work, but is there a better solution? In real life I have tables that I wouldn't want to lock on this level if it is not absolutely necessary.
Why putting an index changes this behavior?

Thanks in advance.

I haven't managed to reproduce this after running your code a few times.

I presume that it must happen when a later row gets inserted onto an earlier page in the file though.

So the order of operations is (for example)

Rows inserted into heap on pages 200, 207, 223
Select statement starts and performs an allocation ordered scan. Finds that the first page is 200 and is blocked waiting on a row lock to be released.
Other rows are inserted by the first transaction. Some of them are allocated on a page before 200. Insert transaction commits.
Row lock released and continues allocation ordered scan. Rows earlier in the file are missed.

The table comprised 10 pages. By default the first 8 pages will be allocated from mixed extents and then it will be allocated a uniform extent. Maybe in your case space was available in the file for a free uniform extent prior to the mixed extents that were used.

You can test this theory by running the following in a different window after you have reproduced the issue and seeing if the missing rows from the original SELECT all appear at the beginning of this resultset.

SELECT [SomeData],
       Moment,
       SomeInt,
       file_id,
       page_id,
       slot_id
FROM   [SomeTable]
/*Undocumented - Use at own risk*/
CROSS APPLY sys.fn_PhysLocCracker(%% physloc %%)
ORDER BY page_id, SomeInt

The operation against an indexed table will be in index key order rather than allocation order so will not be affected by this particular scenario.

An allocation ordered scan can be carried out against an index but it is only considered if the table is sufficiently large and the isolation level is read uncommitted or a table lock is held.

Because read committed generally releases locks as soon as the data is read it is possible  at for a scan against the index to read rows twice or not at all (if the index key is updated by a concurrent transaction causing the row to move forward or back) See The Read Committed Isolation Level for more discussion about this type of issue.

By the way I was originally envisaging for the indexed case that the index was on one of the columns that increases relative to insert order (any of Id, Moment, SomeInt). However even if the clustered index is on the random SomeData the issue still doesn't arise.

I tried

DBCC TRACEON(3604, 1200, -1) /*Caution. Global trace flag. Outputs lock info
                               on every connection*/

SELECT TOP 2 *,
             %%LOCKRES%%
FROM   [SomeTable] WITH(nolock)
ORDER BY [SomeData];

SELECT *,
       %%LOCKRES%%
FROM   [SomeTable]
ORDER BY [SomeData];

/*Turn off trace flags. Doesn't check whether or not they were on already
  before we started, with TRACEOFF*/
DBCC TRACEOFF(3604, 1200, -1)

Results were as below

The second resultset includes all 1,000 rows. The locking info shows that even though it was blocked waiting on lock resource 24c910701749 when the lock was released it doesn't just continue the scan from that point. Instead it immediately releases that lock and acquires a row lock on the new first row.


How to use Italian locale for Postgres messages On Oracle I have set:

export NLS_LANG=Italian_ITALY.UTF8

.. on Unix shell and now I get status and error message in Italian.

How to set in Postgres shell (psql)?
I have set:

SET lc_messages TO 'it_IT.UTF-8';

But it is all in English:

prova
video-# ;
ERROR:  syntax error at or near "prova"
LINE 1: prova
        ^
video=# select count(*) from generale ;
 count
-------
  2170
(1 row)

First of all, this is an error message from PostgreSQL, which is not the same as psql (the command-line client). The SET command sets run-time parameters of the Postgres server, which is completely independent from the client (psql in this case).

Either way, it works for me. Testing the standard distribution of Postgres 9.4.1 on a Windows client set to a German locale (German_Germany.1252):

test=# SET LC_MESSAGES TO 'it_IT.UTF-8';
SET
test=# SELECT 1/0;
ERRORE:  divisione per zero

test=# SET lc_messages  = 'C';
SET
test=# SELECT 1/0;
ERROR:  division by zero

test=# RESET lc_messages;
RESET
test=# SELECT 1/0;
FEHLER:  Division durch Null

The same works on a Debian machine (where the respective locales are available!). And I can reproduce the same from any other client (like pgAdmin).

Maybe you are not running both commands in the same session?
Either way, Postgres uses locale support from the underlying OS. Per documentation:

  What locales are available on your system under what names depends on
  what was provided by the operating system vendor and what was
  installed. On most Unix systems, the command locale -a will provide a
  list of available locales. Windows uses more verbose locale names,
  such as German_Germany or Swedish_Sweden.1252, but the principles are
  the same.

And natural language support (--enable-nls) must be enabled at compile time which is the default and is the case for standard distributions. Details in the manual here.
Are you running a non-standard version? Compiled it yourself maybe?


Creating a case-insensitive and accent/diacritics insensitive search on a field I would like to know if it makes sense to create an index combining two functions while using Full Text Search: lower(name) and f_unaccent(name) Where f_unaccent is just my wrapper to make unaccent function immutable.

I do have an index working on: f_unaccent(name) using varchar_pattern_ops. My question is: would an index that combines lower and unaccent functions be triggered by full_text_search lower(f_unaccent(name)).

I don't know if the lower function would be useful working with Full Text Search algorithms.

First, we need to clarify a few things.

Pattern Ops and Trigram

This type of query can work on varchar/text pattern_ops,

foo LIKE 'bar%'

Queries with Un-anchored search patterns reqire trigram.

foo LIKE '%bar%'

Both of them can work on function expressions, so long as you maintain the same sequence in the query that you have in the index.

Full text search is something completely different. It transforms your text into lexemes by stubbing to the root words, lowercasing, removing stop words, and a slew of other tricks. Keep reading:

Unaccent by itself (wouldn't suggest it)

Unfortunately though unaccent is only "stable" not "immutable." And so if you try to create an index on unaccent(text) you'll get an error, functions in index expression must be marked IMMUTABLE. I assume this is why you created the wrapper f_accent having found that on Erwins answer on Stack Overflow. Which looks like this,

CREATE EXTENSION unaccent;
CREATE OR REPLACE FUNCTION f_unaccent(text)
  RETURNS text AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$
LANGUAGE sql
IMMUTABLE;

CREATE TABLE foo AS
  SELECT 'asdf'::text AS mytext;
CREATE INDEX ON foo (lower(f_unaccent(mytext)) text_pattern_ops);

Or, for trigram,

CREATE INDEX ON foo
USING gin(lower(f_unaccent(mytext)) gin_trgm_ops);

Now remember you have to carry that long sequence lower(f_unaccent(mytext))  through to make it work..

SELECT *
FROM foo
WHERE lower(f_unaccent(mytext)) LIKE '%whatever%';

Unaccent with a FTS Dictionary

Instead of that method, I would create a dictionary that uses unaccent and use FTS. With custom dictionaries though it will likely do what you want. FTS converts the words to tokens which makes searching very simple and fast.

CREATE EXTENSION unaccent; -- still required it plugs into FTS.

CREATE TEXT SEARCH CONFIGURATION mydict ( COPY = simple );
ALTER TEXT SEARCH CONFIGURATION mydict
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, simple;

Now you should be able to

Removing any varchar_pattern_ops or trigram index
if desired, create a GIST or GIN index on the tsvector

CREATE INDEX ON foo USING to_tsvector('mydict', mytext);

You can see the tokens and what it's doing here,

select to_tsvector(
  'mydict',
  'Zoë and Renée are elected to the council of Cheese Eating Surrender Monkeys'
);
                                                             to_tsvector
-------------------------------------------------------------------------------------------------------------------------------------
 'and':2 'are':4 'cheese':10 'council':8 'eating':11 'elected':5 'monkeys':13 'of':9 'renee':3 'surrender':12 'the':7 'to':6 'zoe':1
(1 row)

And you can query the table simply like this,

SELECT *
FROM foo
WHERE to_tsvector('mydict', mytext) @@ 'cheese & council';

You don't have to use simple either. If the column contains statements in just one language you can use one of the other dictionaries to simplify things and remove stop words.


What is held in memory during the life of a CTE? Assuming a multi-read/multi-write CTE, what exactly is held in memory during the life of the statement?

Is all data except RETURNING data discarded at the end of each sub-statement?

A CTE term's output is accumulated into a TupleStore. A TupleStore may internally be in-memory or may spill to disk. See src/include/utils/tuplestore.h and src/backend/utils/sort/tuplestore.c, particularly the comment at the top of tuplestore.c.

For a wCTE that'll be the RETURNING output for that term.

To determine whether anything else is retained requires analysis of the memory contexts in which each CTE term is executed, as PostgreSQL uses a hierarchical memory context based allocator.  Here you need to look at src/backend/executor/nodeCtescan.c. It's pretty complicated, but a read of CteScanNext suggests that the queries are often executing concurrently, with one query consuming the results of another.

So. While memory might be freed at the end of each sub-statement (CTE term) there's no gurarantee that any of the CTE terms finish at any particular time.

Rephrasing your question:

  Is all in-memory data from a CTE term except RETURNING data discarded before the next CTE term executes

the answer is "no".

However, that doesn't mean PostgreSQL will keep piles of data in memory. It frequently discards rows as soon as it's done with them and never needs to see them again. It can spill big temporary results to tempfiles on disk. If it materialises, sorts or aggegates query output it will generally throw away the un-aggregated/un-sorted data after it finishes. etc.


MERGE results with NULL values I have two tables.

One of them TABLE1 has values like this:

TABLE1

|A   |B   |C   |D   |E   |F   |G   |H   |
-----------------------------------------
|1   |NULL|4   |NULL|NULL|NULL|NULL|10  |

TABLE2 has more values compared to TABLE1.

|A   |B   |C   |D   |E   |F   |G   |H   |
-----------------------------------------
|1   |2   |3   |NULL|5   |NULL|6   |11  |

I need to update TABLE1 with values from TABLE2 for every column with NULL values on TABLE1.

The result that I need:

|A   |B   |C   |D   |E   |F   |G   |H   |
-----------------------------------------
|1   |2   |4   |NULL|5   |NULL|6   |10  |

I just found the answer myself using CASE WHEN for every column.

I was trying to achieve this because I was moving an audit table that was saving the information of the modified columns in lines (one by one) to another new table copying the whole line information (that could be many columns) in just one line.

Basically I was trying to merge the old data (old structure) to the new table.


Shrinking the database partition if autogrow is turned off? I want to know:

Are there chances of shrinking the database partition if autogrow is turned off?
Also, are there chances of data fragmentation and increase in seek times if we fully extend size instead of autogrow?
If the above are recommended.

You can shrink a database regardless of autogrow setting. These are unrelated except for the fact that if autogrow is off, and you shrink the database, you're likely headed for a failure as soon as you start adding more data. This obsession with shrinking files is unhealthy - why are you going to shrink the file? What are you going to do with the space you freed up, but only until the file has to grow again? If you are never going to update the data that's there or add more data, maybe this strategy makes sense, but for an active database, it just doesn't accomplish anything except cause you more pain (maybe not today, but it will catch up to you).

No, seek times are not increased because a file is large. The file is just a container.


Change an index order from ascending to descending I have a table index in my postgres 9.6 db that I think would be more efficient if the table was sorted descending rather than ascending (as it currently is). The data that I want to test is not in full production use, but could potentially be accessed by users at any time, and I'd rather not take it down if I can avoid it. Two questions:

Is it possible to switch the index from ascending to descending on-the-fly?
If not, I guess I need to drop the index and re-create it. Obviously this will slow down search queries while there is no index. Is that the worst that will happen? Nothing will stop working entirely, right? As I understand it, it will get an exclusive lock on the table before dropping the index, right?

Apologies for the n00b question, I'm quite new to databases. Please be gentle with me!

If you dont want to lock table data you can use the clause CONCURRENTLY to drop the index an recreate it, this process is slower but avoids to lock access to the users.

DROP INDEX CONCURRENTLY my_index;
CREATE INDEX CONCURRENTLY my_index ON my_table (my_column DESC);

You can take a look at the postgres index documentation for more detailed information.


Optimization profile and explain mode? The other day I noticed something strange that perhaps someone have an explanation for. The problem I was facing was a query that due to a skewed distribution of data generated a poor plan when a parameter marker was used. The execution time was prox 10 times higher compared to a hardcoded value concatenated into the query.

I have never tried optimization profiles before, so I thought it would give it a shot. I installed a profile like:

<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="10.5.8.0">
<STMTPROFILE ID="Guideline for update of process_state_id">
    <STMTKEY>
       <![CDATA[UPDATE NYA.PICKED_COURSE_OFFERING PCO SET PCO.PROCESS_STATE_ID = 90 WHERE EXISTS ( SELECT     1 FROM NYA.PERSON P     WHERE P.PERSON_ID = PCO.PERSON_ID AND SUBSTR(P.PNR, 9, 1) <> 'F' ) AND NOT EXISTS ( SELECT    1 FROM NYA.COURSE_OFFERING_PREVENT_LADOK_PUBLICATION CPL WHERE CPL.EDUCATIONORG_ID = PCO.EDUCATIONORG_ID AND CPL.ADMISSIONROUND_ID = PCO.ADMISSIONROUND_ID AND CPL.COURSEOFFERING_ID = PCO.COURSEOFFERING_ID ) AND PCO.ADMISSIONROUND_ID = ? AND PCO.RESULT = 20]]>
    </STMTKEY>
    <OPTGUIDELINES>
      <REOPT VALUE='ALWAYS'/>
    </OPTGUIDELINES>
</STMTPROFILE>

This gave me what I wished for, i.e. the query where recompiled every time it got a new parameter and benefitted from distribution statistics. So far so good.

I wanted to take a closer look at how the plan changed due to the optimization profile, so I turned on explain mode and fired of the query:

]$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
]$ db2 -tf update_orig.sql
SQL0217W  The statement was not executed as only Explain information requests are being processed.  SQLSTATE=01604
]$ db2exfmt -d ...

Much to my surprise, I get an error doing the same thing with the optimization profile enabled:

]$ db2 "set current optimization profile test.optprof"
DB20000I  The SQL command completed successfully.
]$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
]$ db2 -tf update_orig.sql
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0313N  The number of variables in the EXECUTE statement, the number of
variables in the OPEN statement, or the number of arguments in an OPEN
statement for a parameterized cursor is not equal to the number of values required.  SQLSTATE=07004

If I run the query from python or java everything works well, so it is more a question of the convenience to be able to explain statements from command line.

In addition, it also works to explain the plan like:

]$ db2 "explain plan for UPDATE NYA.PICKED_COURSE_OFFERING PCO SET PCO.PROCESS_STATE_ID = 90
> WHERE EXISTS (
>      SELECT     1
>         FROM NYA.PERSON P     WHERE P.PERSON_ID = PCO.PERSON_ID
>         AND SUBSTR(P.PNR, 9, 1) <> 'F'
> ) AND NOT EXISTS (
>     SELECT    1
>         FROM NYA.COURSE_OFFERING_PREVENT_LADOK_PUBLICATION CPL
>         WHERE CPL.EDUCATIONORG_ID = PCO.EDUCATIONORG_ID
>         AND CPL.ADMISSIONROUND_ID = PCO.ADMISSIONROUND_ID
>         AND CPL.COURSEOFFERING_ID = PCO.COURSEOFFERING_ID
> ) AND PCO.ADMISSIONROUND_ID = ?
> AND PCO.RESULT = 20"
DB20000I  The SQL command completed successfully.

This, however, becomes a bit unpractical for large queries since it requires either to change the query in the file, keep two files, one for explain and one without, or as above executing the query directly.

Does the optimization profile somehow invalidate the explain mode? It does not matter in what order I set explain mode and optimization profile, I cannot execute a query with a parameter marker either way. Any thoughts anyone?

The REOPT(ALWAYS) option conflicts with the explain facility behaviour, because in this case the statement compilation is delayed until the bind variable value is known -- which is the whole point of reoptimization, isn't it. The manual says in particular:

  The REOPT bind option defers statement compilation from PREPARE to OPEN or EXECUTE time, when the input variable values are known. The values are passed to the SQL compiler so that the optimizer can use the values to compute a more accurate selectivity estimate.

You are not providing the expected value, so compilation cannot proceed.

The EXPLAIN statement succeds I believe because it doesn't get to the re-optimization phase therefore not expecting a value for the bind variable. As a result, the plan it generates will probably differ from the reoptimized one.

You can address this by creating a global variable: CREATE VARIABLE myparm INT DEFAULT 5 (or whatever data type and value you need) and then referencing myparm in the statement where you have the parameter marker now. You will also need to create a new profile that matches the new statement text.

Tested and works on v11.1 but I assume the behaviour would be the same in 10.5.


Azure SQL Database SQL71562 error exporting to blob storage I am attempting to export an Azure SQL Database on an elastic pool to blob storage via the portal Export selection from the target database.
This errors with:
One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71562: Error validating element (and a partial listing of objects follows).
When moving from on-premises to Azure there were things that had to been done regarding database tables and stored procedures to ensure a successful bacpac creation.  It appears that is the case even with Azure SQL Databases but not finding anything that documents this (not sure why unsupported objects could be created within SQL Database).  Anyone else experience this?

This is an error I’ve seen after migrating to Azure; usually in stored procedures. The error is seen when something has a four-part table reference. For example: [server].[database].[schema].[table]. These are not supported and result in a SQL71562 error.
You may be able to identify the issue by querying your [sys] tables for procedures and views that specifically reference a server or database.
For stored procedures:
SELECT OBJECT_NAME(object_id),
       OBJECT_DEFINITION(object_id)
  FROM sys.procedures
 WHERE OBJECT_DEFINITION(object_id) LIKE '%[DB_NAME]%'

For views:
SELECT OBJECT_NAME(object_id),
       OBJECT_DEFINITION(object_id)
  FROM sys.views
 WHERE OBJECT_DEFINITION(object_id) LIKE '%[DB_NAME]%'

Be sure to replace DB_NAME with the name of one of your databases.


I need help with a SQL-query that results in an error I get the following error when I try to delete a record in the user table that has an entry in the feedback table:

e.g. when a record in the feedback table has a reference to the user table

  1451 - Cannot delete or update a parent row: a foreign key constraint
  fails (kbs.feedback, CONSTRAINT fk_feedback_user1 FOREIGN KEY
  (user_id) REFERENCES user (user_id) ON DELETE NO ACTION ON
  UPDATE NO ACTION)

The relationship between the two tables:

I hope someone could help me into the right direction and that my question is clear enough. I've followed a database design course but forgot a lot of stuff.

The user you are trying to delete have rows in the feedback table. Given your foreign key definition:

CONSTRAINT fk_feedback_user1
FOREIGN KEY (user_id)
    REFERENCES user (user_id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION

You need to first delete from the feedback table:

delete from feedback where user_id = ?;
delete from user where user_id = ?;

An alternative is to change the foreign key to:

CONSTRAINT fk_feedback_user1
FOREIGN KEY (user_id)
    REFERENCES user (user_id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION

Any rows in the feedback table for that user will then be deleted automatically when you delete a user


How often to update SQL Server? I am a new DBA at a company that has never had a DBA before me. SQL Servers are never upgraded unless it is part of a Windows Update on the server. Service Packs (or CUs) are never installed. We have a mix of versions from 2000 to 2014.

I'm trying to develop a policy for maintaining and upgrading the SQL Servers. How often do you upgrade your SQL Servers? Or more specifically, how up-to-date do you keep your SQL Servers and why?

For example:

SQL Servers will be upgraded when the version is not within Microsoft's Mainstream support.
SQL Servers will stay within 2 (two) versions of the most recent version released from Microsoft.

What about SPs and CUs?

How frequently you install Service Packs and/or Cumulative Updates will vary wildly based on your individual scenario. There is no objective one-size-fits-all answer.

The best general advice I could offer would be to follow Microsoft's advice. As of January 2016, Microsoft's advice is:

  we now recommend ongoing, proactive installation of CU’s as they become available.


Is it okay to have different lower_case_table_names value on master and slave? I have a OSX MySQL master with lower_case_table_names set to 2. I have just setup a Linux MySQL slave with lower_case_table_names=1.

I haven't started replication on the slave, but everything looks good at this point.

Will it cause any issues with replication?

According to the MySQL 5.0 Certification Study Guide

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

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

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

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

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

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

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

[mysqld]
lower_case_table_names=1

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

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

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

I have discussed mixing cases between operating systems in MySQL :

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


How to retrieve foreign key constraints data I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema.

I've found this, but does not gives all the info I need:
https://stackoverflow.com/questions/4389228/sql-for-oracle-to-check-if-a-constraint-exists

I'm currently working on it and may end up with a solution in the next minutes/hours. But if someone has already a full working solution, I'll be glad to know it :)

After some "reverse-engineering" on the queries made by the Navicat tool when opening the design table window for a table (queries retrieving info about foreign keys show up in the history window), here is a solution:

SELECT
    CONS.CONSTRAINT_NAME,
    CONS.TABLE_NAME,
    COLS.COLUMN_NAME,
    CONS.R_CONSTRAINT_NAME,
    CONS_R.TABLE_NAME R_TABLE_NAME,
    COLS_R.COLUMN_NAME R_COLUMN_NAME

FROM USER_CONSTRAINTS CONS
    LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
    LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
    LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME

-- returns only foreign key constraints
WHERE CONS.CONSTRAINT_TYPE = 'R'

ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAME


SQL Server 2012 running background task that fills memory In my installation of SQL Server 2012 on my development machine (Win8.1), the process "SQL Server Windows NT - 64 Bit" is running continuously, using up disk resources and filling up my memory over time, even though I'm doing nothing. If I reboot the computer or restart the service called SQL Server (MSSQLSERVER), then the memory allocation is reset and immediately begins to accumulate again.

How can I identify the operation that SQL Server is running to cause this usage? I don't recall changing any operations or configuration.

Edit:
The problem is not just that memory is filled, but also that it continually uses half my disk operation speed (the "Disk" column in Task Manager Processes is at 50-75%).  There appears to be an ongoing operation that perhaps has a memory leak.  In other words, I am doing no activity and sitting watching the Task Manager and the memory is going up steadily.

SQL Server does not use up memory without a workload running. I.E. an instance of SQL Server will consume the bare minimum it requires to keep the engine alive, listen for connections and handle background tasks (e.g. resource management). That's usually less than 100MB. If you have a min memory setting then it'll acquire that minimum IF there's demand because of workload and then sit there; won't release beyond the minimum even after the workload is done.

If your SQL Server process (sqlservr.exe in task manager or process monitor) is consuming lots of memory, something is going on. Check what's going on in the server using SELECT * FROM sys.dm_exec_sessions and look at the login_name column to see who's connected. That's a start to identify potential unknown workloads. From there, the other columns will provide information on how much resources it's using. It could be a real user, machine or even some old SQL Agent job gone wild.


get max value occurred within every 10 minute of a day I have a table named pointdb containing columns:

SOLAR_ACTIVE_POWER_G_VAL0
LOAD_ACTIVE_POWER_G_VAL0
timestamp

It contains every second wise data. I wanted to display max of SOLAR_ACTIVE_POWER_G_VAL0 and max of LOAD_ACTIVE_POWER_G_VAL0 occurring within every 10 minute interval. For that I have written a query as follows:

Collapse | Copy Code

SELECT max(SOLAR_ACTIVE_POWER_G_VAL0) AS Solar,max(LOAD_ACTIVE_POWER_G_VAL0) AS Load, (DATEPART(MINUTE, [timestamp]) % 10) AS [Minutes]
FROM pointdb WHERE CONVERT(varchar,[timestamp],101) in('03/07/2014')
GROUP BY (DATEPART(MINUTE, [timestamp]) % 10)

But it is not giving right output.

What about:

SELECT max(SOLAR_ACTIVE_POWER_G_VAL0) AS Solar,
       max(LOAD_ACTIVE_POWER_G_VAL0) AS Load,
       DATEADD(minute, DATEDIFF(minute, 0, timestamp)/10*10, 0) AS [Minutes]
FROM pointdb

WHERE CONVERT(varchar,[timestamp],101) in('03/07/2014')

WHERE [timestamp] >= '20140307' AND [timestamp] < '20140308'
GROUP BY DATEADD(minute, DATEDIFF(minute, 0, timestamp)/10*10, 0)

The part

DATEADD(minute, DATEDIFF(minute, 0, timestamp)/10*10, 0)

should round down to the start of the 10 minute period.
If you leave out /10*10, you get it to the nearest 1 minute period.


Multicondition Subquery Max Date and Item Number The following is a query to get latest Sales date for a customer.

select pt.CustomerId, pt.SalesDate, pt.*
from dbo.PurchaseTransactions pt
where pt.SalesDate = (
    select max(SalesDate)
    from dbo.PurchaseTransactions subpt
    where pt.CustomerId = subpt.CustomerId
)

Now my next question is, for a given purchase date, a Customer can have multiple purchase order numbers.

How would I acquire the largest order number for a given Maximum Purchase date using subquery? Order numbers can be random, and we just want a random or largest order number line.

We want to utilize subquery. Otherwise, I will have to rewrite as partition order by.

select pt.CustomerId, pt.SalesDate, pt.*
from dbo.PurchaseTransactions pt
where pt.SalesDate = (
    select max(SalesDate)
    from dbo.PurchaseTransactions subpt
    where pt.CustomerId = subpt.CustomerId
) and pt.OrderNumber = ??

Is this what you are looking for?

WITH MaxSalesDatesPerCustomer
AS
(
SELECT CustomerID, MAX(SalesDate) AS MaxDate
FROM dbo.PurchaseTransactions
GROUP BY CustomerID
)
SELECT MS.CustomerID, MS.MaxDate AS SalesDate, MAX(OrderNumber /*???*/) AS LargestOrderNumberForMaxDatePerCustomer
FROM MaxSalesDatesPerCustomer AS MS
     INNER JOIN
     dbo.PurchaseTransactions AS PT
     ON PT.CustomerID = MS.CustomerID
WHERE MS.MaxDate = PT.SalesDate
GROUP BY MS.CustomerID, MS.MaxDate