Indexes for select performance I have a table:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` enum('new','paid') NOT NULL DEFAULT 'new',
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `can_be_closed` tinyint(1) NOT NULL DEFAULT '1',
  `close_reason` varchar(255) DEFAULT NULL,
  `interval_before_close` int(11) NOT NULL DEFAULT '10',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

And following select:

SELECT `id`, `status`, `close_reason` AS `closeReason`, `createdAt`, `updatedAt`
FROM `t1` as t
WHERE (
`t`.`status` = 'new'
AND `t`.`updatedAt` < '2018-06-22 10:14:36' + INTERVAL `interval_before_close` MINUTE
AND `t`.`can_be_closed` = true
)

For this query, explain return a full table scan.

I tried to add the following indexes:

ALTER TABLE `t1`
ADD INDEX `updatedAt` (`updatedAt` ASC);

and:

ALTER TABLE `t1`
ADD INDEX `updatedAt` (`updatedAt` ASC, `interval_before_close` ASC);

but without any change in explain.

Fidle ex.: link

Plan A (not good):

Off hand, I would say it is optimal is to list = column(s) first, then a range:

INDEX(status, can_be_closed,   -- in either order
      updatedAt)               -- last

But...  It is more complicated than that.

'2018...' + INTERVAL interval_before_close ...

is essentially a function call to compute a date.  That makes it useless to put either updatedAt or interval_before_close in the index.

Plan B (wimpy):

So...  The following may be useful, but only if not many of the rows match those two flags:

INDEX(status, can_be_closed)   -- in either order

Cookbook

Plan C (complex):

If, on the other hand, you had a column (real or computed) that contained

updatedAt - INTERVAL interval_before_close MINUTE

Say that it column is called early_close; then change to

AND early_close < '2018-06-22 10:14:36'

and add

INDEX(status, can_be_closed, early_close)


PWDENCRYPT in user-defined function In writing a random number function, I have learned that you can’t use NEWID() or RAND() inside a user function because they are non-deterministic.

I have also discovered that I can use PWDENCRYPT and get away with it. I would have thought that that was also non-deterministic. It certainly gives me a random-enough value for testing purposes (I’m not trying to simulate quantum physics here).

My questions:

is PWDENCRYPT really acceptable inside a user function (i.e., it works now, but can I expect it to keep working)?
is it random enough (for a reasonable definition of random)?

PWDENCRYPT is kind of deprecated, so no, you shouldn't be using it now, and you certainly shouldn't expect it to work indefinitely in any fashion - deterministic or not. From the documentation:

  PWDENCRYPT is an older function and might not be supported in a future release of SQL Server. Use HASHBYTES instead. HASHBYTES provides more hashing algorithms.

I don't know that anybody can answer your second question. How do you define "random enough"?

Also, just to be clear, NEWID() and RAND() are disallowed in functions not because they are non-deterministic, but rather because they are considered "side effecting" - even though there is nothing you or I could assume about how these functions affect anything, other than incrementing some internal random number sequence generator.

And there are easy workarounds to that, anyway - e.g. create a view and have the function reference the view, pass NEWID() into the function, etc.

In addition to PWDENCRYPT(), other functions that are also obviously not deterministic (such as GETDATE(), SYSUTCDATETIME(), CURRENT_TIMESTAMP, and others) are allowed. Example:

CREATE OR ALTER FUNCTION dbo.what()
RETURNS int
AS
BEGIN
  RETURN (SELECT @@DBTS + @@CPU_BUSY + @@CONNECTIONS
    + DATEDIFF(SECOND, GETDATE(), SYSUTCDATETIME()));
END
GO

SELECT dbo.what();

The function will be marked as non-deterministic (which you can confirm with OBJECTPROPERTY()), which can limit how the UDF is in turn used, but it isn't the case that "non-deterministic functions are not allowed inside user-defined functions." Even though that's what a lot of people have written, it isn't strictly true.

In fact, RAND() is documented to be deterministic when a seed is provided; RAND(5); is demonstrably deterministic. But it still isn't allowed in a function (this returns the same side-effecting error):

CREATE FUNCTION dbo.who()
RETURNS decimal(20,19)
AS
BEGIN
  RETURN (SELECT RAND(5));
END
GO


MySQL, comparison between, tables with not all records common, on 2 time intervals I want to make comparisons on the volume of sales, turnover and margin of all product sales
between 2 time intervals (2017-10-1 to 2017-10-31 ) and (2018-10-1 to 2018-10-31).

for example:

Product Name | Volume 2017 | Volume 2018 | Margin 2017 | Margin 2018 |
     A       |     200     |     100     |     10      |      12     |
     B       |     190     |     120     |     23      |      20     |
     C       |  'no sales' |     100     |  'no sales' |      12     |
     D       |     300     |  'no sales' |     25      |  'no sales' |

My approach is the following:

select
    /* attempt to get either name if null but doesnt work as I join product ID's*/
    if(ifnull(q1.ProductName,false), q2.ProductName , q1.ProductName) as 'Name'
    ,q1.NumofProductsSold as 'Volume 2017'
    ,q2.NumofProductsSold as 'Volume 2018'

    ,format(q1.Margin,2) as 'Margin 2017'
    ,format(q2.Margin,2) as 'Margin 2018'

    ,q1.TotalTurnOver as 'Turnover 2017'
    ,q2.TotalTurnOver as 'Turnover 2018'

from

(   select

    format(sum(oi.product_quantity),0) as 'NumofProductsSold'
    ,format(sum(oi.turnover),2) as 'TotalTurnOver',
    ,SUM(oi.total_Mark_up) + o_inf.Markup 'Margin'
    ,order_id
     ,ifnull(p.name, 'empty') as 'ProductName'
     ,p.id as 'PID'
     ,op.date as 'date'

     FROM order_items oi JOIN products p ON p.id = op.product_id
                        join product_categories pc on p.id = pc.product_id
                        join orders o on op.order_id = o.id
                        join order_info o_inf on o.order_number = o_all.orderNumber

     where pc.category_id = 1 -- cat A products only
         AND op.order_status != 'CNL' -- not canceled
           AND op.date >= '2017-09-01' AND op.date <= '2017-10-31'

     group by p.id) as q1

left join

(  Select

            /* same query BUT for 2018 date range */

 ) as q2

on q1.PID = q2.PID  -- join on product ID

My issue is that if a product doesn't have any sales in either/both the first or second time interval, I get only the rows that correspond on both, so I always "miss" some products if they have sales in one time interval and not the other.

With left/right join I can get correct results only if either set is a subset of the other respectively, i.e if a product ID appears in the right and not the left it doesn't appear with left join. I tried UNION it works BUT it doesn't produce the outcome in specified in the description above. Is there a workaround based on how I designed the Query or should I follow a different approach?

All suggestions-corrections-critique more than welcome! Thanks!

Algorythm:

SELECT product_name,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2017-10-1' AND '2017-10-31'
                         THEN volume END), 'no sale') AS volume2017,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2018-10-1' AND '2018-10-31'
                         THEN volume END), 'no sale') AS volume2018,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2017-10-1' AND '2017-10-31'
                         THEN margin END), 'no sale') AS margin2017,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2018-10-1' AND '2018-10-31'
                         THEN margin END), 'no sale') AS margin 2018
FROM datatable
GROUP BY product_name
/* WHERE `date` BETWEEN '2017-10-1' AND '2017-10-31'
      OR `date` BETWEEN '2018-10-1' AND '2018-10-31' */


Count business days between 2 dates in postgreSQL I am trying to count business days between a certain date range in PostgreSQL.

My table:

|start_date |end_date |
------------------------
|2017-06-01 |2017-06-01|
|2017-05-29 |2017-06-02|

This is my code:

SELECT pto.start_date, pto.end_date,
SUM(CASE WHEN extract (dow FROM foo) IN(1,2,3,4,5) THEN 1 ELSE 0 END) as theDIFF
FROM (
  SELECT start_date, (start_date::date + (generate_series(0,end_date::date
        - start_date::date)||'days')::interval) AS foo
    FROM pto
) foo inner join pto pto
on pto.start_date = foo.start_date
group by pto.start_date, pto.end_date

My OUTPUT:

      |start_date(date)| end_date(date) |theDiff(integer)
      ---------------------------------------------------
      |2017-06-01      |  2017-06-01    |        29     |
      |2017-05-29      |  2017-06-02    |        12     |
      ---------------------------------------------------

Expected Output:

      |start_date(date)| end_date(date) |theDiff(integer)
      ---------------------------------------------------
      |2017-06-01      |  2017-06-01    |        1      |
      |2017-05-29      |  2017-06-02    |        5      |
      ---------------------------------------------------

You're not using (or showing us) a unique key, if you have more than one row with the same (start_date, end_date) you will get wrong results.

select start_date, end_date,
       sum(case when extract (dow from dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select start_date, end_date,
              generate_series(start_date, end_date, '1 day'::interval) as dt
       from   tbl
     ) t
group by start_date, end_date;

start_date          | end_date            | thediff
:------------------ | :------------------ | ------:
2017-05-29 00:00:00 | 2017-06-02 00:00:00 |       5
2017-06-01 00:00:00 | 2017-06-01 00:00:00 |       1

select id, start_date, end_date,
       sum(case when extract (dow from dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select id, start_date, end_date,
              generate_series(start_date, end_date, '1 day'::interval) as dt
       from   tbl
     ) t
group by id, start_date, end_date;

id | start_date          | end_date            | thediff
-: | :------------------ | :------------------ | ------:
 1 | 2017-06-01 00:00:00 | 2017-06-01 00:00:00 |       1
 2 | 2017-05-29 00:00:00 | 2017-06-02 00:00:00 |       5

db<>fiddle here


Why am I seeing `ERROR 1290 (HY000)` when altering my table? When I run the following statements on my test_date table, I get error ERROR 1290 (HY000):

desc test_date;
+------------+-----------+------+-----+-------------------+-------+
| Field      | Type      | Null | Key | Default           | Extra |
+------------+-----------+------+-----+-------------------+-------+
| gmt_create | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+------------+-----------+------+-----+-------------------+-------+
1 row in set (0.00 sec)

alter table test_date modify gmt_create datetime not null default current_timestamp;

ERROR 1290 (HY000): The MySQL server is running with the
--log_bin_use_old_datetime_format option so it cannot execute this statement

I don't know what is the problem.Looking for your help.

From the 5.6.6 Changelog:

In MySQL, the TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) data type differs in nonstandard ways from other data types:

TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns not explicitly declared with the NULL attribute are assigned the NOT NULL attribute. (Columns of other data types, if not explicitly declared as NOT NULL, permit NULL values.) Setting such a column to NULL sets it to the current timestamp.

The first TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) column in a table, if not declared with the NULL attribute or an explicit DEFAULT or ON UPDATE clause, is automatically assigned the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

Those nonstandard behaviors remain the default for TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) but now are deprecated and this warning appears at startup:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).

As indicated by the warning, to turn off the nonstandard behaviors, enable the new explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ) system variable at server startup. With this variable enabled, the server handles TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) as follows instead:

TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns not explicitly declared as NOT NULL permit NULL values. Setting such a column to NULL sets it to NULL, not the current timestamp.

No TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) column is assigned the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes automatically. Those attributes must be explicitly specified.

TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns declared as NOT NULL and without an explicit DEFAULT clause are treated as having no default value. For inserted rows that specify no explicit value for such a column, the result depends on the SQL mode. If strict SQL mode is enabled, an error occurs. If strict SQL mode is not enabled, the column is assigned the implicit default of '0000-00-00 00:00:00' and a warning occurs. This is similar to how MySQL treats other temporal types such as DATETIME ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ).

To upgrade servers used for replication, upgrade the slaves first, then the master. Replication between the master and its slaves should work provided that all use the same value of explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ):

Bring down the slaves, upgrade them, configure them with the desired value of explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ), and bring them back up.

The slaves will recognize from the format of the binary logs received from the master that the master is older (predates the introduction of explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp )) and that operations on TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns coming from the master use the old TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) behavior.

Bring down the master, upgrade it, and configure it with the same explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ) value used on the slaves, and bring it back up.

(Bug #63034, Bug #13344629, Bug #55131, Bug #11762529)


How to export own objects using expdp without special privileges? As per this post on Oracle forums expdp can be used to export your own schema without any special privileges:

  It all depends on what you want that user to be able to export/import.
  If all that use is exporting/importing is their own objects, then they
  don't need any raised privs. If you want that user to be able to
  export objects owned by other schemas, then they would need the
  exp_full_database and in order to import, they would need
  imp_full_database.

However I just can't get it to work. Directory object is required to be specified otherwise expdp displays error:
ORA-39145: directory object parameter must be specified and non-null
But creating directories requires CREATE ANY DIRECTORY system privilege.

How then can I export my own schema as a normal user?

Creating Directory Objects and Export/Import are two different operations. Directory Objects are owned by SYS.

  A directory object maps a name
  to a directory path on the file system. DBAs must ensure that only approved users are
  allowed access to the directory object associated with the directory path.
  If you are not a privileged user, then before you can run Data Pump Export or Data
  Pump Import, a directory object must be created by a database administrator (DBA) or
  by any user with the CREATE ANY DIRECTORY privilege.

  Schema Mode

  A schema export is specified using the SCHEMAS parameter. This is the default export
  mode. If you have the DATAPUMP_EXP_FULL_DATABASE role, then you can specify a list of
  schemas, optionally including the schema definitions themselves and also system
  privilege grants to those schemas. If you do not have the DATAPUMP_EXP_FULL_
  DATABASE role, then you can export only your own schema.

Data Pump Export-Schema Mode


xtrabackup "failed to read metadata" Doing a staging run setting up a new xtrabackup restore script I'm running into a somewhat cryptic error I can't readily google a solution for.

I'm working with an incremental backup design.  During the initial copy back of the last full the an attempt to apply changes to the first incremental is failing with

xtrabackup: error: failed to read metadata from
/var/tmp/mysql_backups/inc/day1/xtrabackup_checkpoints

The cited file is present and world readable.

It's contents are

backup_type = incremental
from_lsn = 12:4121088784
to_lsn = 12:4121089203
last_lsn = 12:4121089203

Thoughts on what to check from here?

Edit

I think I found my problem.  My testing server install was an ubuntu mysql default; not percona.

https://bugs.launchpad.net/percona-xtrabackup/+bug/655584/comments/3


Find users having grants on a table in MySQL We grant access to users with the following
grant select, insert, update, delete on product_events to 'product_admin'@'%' ;

Now, like this there are a lot of tables, and a lot of users.
I want to find all users having any grants on a particular table
That is, given product_events table, I would like to get 'product_admin'@'%'

This messy query should give you every user that can access product_db.product_events
SELECT user,host,'Global' Level FROM mysql.user WHERE
select_priv='Y' OR insert_priv='Y' OR update_priv='Y' OR delete_priv='Y'
UNION
SELECT user,host,'DB' FROM mysql.db
WHERE db='product_db' AND
(select_priv='Y' OR insert_priv='Y' OR update_priv='Y' OR delete_priv='Y')
UNION
SELECT user,host,'Table' FROM mysql.tables_priv
WHERE db='product_db' AND table_name='product_events';

If you are looking for users with all four(4) permissions (SELECT,INSERT,UPDATE and DELETE grants), then use this (basically using AND instead of OR)
SELECT user,host,'Global' Level FROM mysql.user WHERE
select_priv='Y' AND insert_priv='Y' AND update_priv='Y' AND delete_priv='Y'
UNION
SELECT user,host,'DB' FROM mysql.db
WHERE db='product_db' AND
(select_priv='Y' AND insert_priv='Y' AND update_priv='Y' AND delete_priv='Y')
UNION
SELECT user,host,'Table' FROM mysql.tables_priv
WHERE db='product_db' AND table_name='product_events';

NOTE : Table level privileges are even more messier to check since the grants are listed as a set rather than as individual columns
mysql> desc mysql.tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                                                                | Null | Key | Default           | Extra                       |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                                                                | NO   | PRI |                   |                             |
| Db          | char(64)                                                                                | NO   | PRI |                   |                             |
| User        | char(16)                                                                                | NO   | PRI |                   |                             |
| Table_name  | char(64)                                                                                | NO   | PRI |                   |                             |
| Grantor     | char(77)                                                                                | NO   | MUL |                   |                             |
| Timestamp   | timestamp                                                                                | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv  | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO   |     |                   |                             |
| Column_priv | set('Select','Insert','Update','References')                                                                                | NO   |     |                   |                             |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql>


If an 'after' DDL trigger causes an error, is the DDL rolled back? It has been suggested that DDL is logically performed something like this:

begin
    COMMIT;
    perform any appropriate pre-DDL trigger code;
    do the ddl;
    perform any appropriate post-DDL trigger code;
    COMMIT;
exception
    when others then
         ROLLBACK;
         raise;
end;

Which would suggest that any error in a trigger would cause the DDL to be rolled back. Is this the case?

The answer, at least on 11.2, is "It depends":

This create is rolled back:

create trigger trig_foo after create on schema
begin
  raise_application_error(-20001, 'Dont do it!');
end;
/
--
create table foo as select level as id from dual connect by level<=10000;
/*
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Dont do it!
*/
--
select count(*) from foo;
/*
SQL Error: ORA-00942: table or view does not exist
*/

But this truncate is not:

create table foo as select level as id from dual connect by level<=10000;
--
create trigger trig_foo after truncate on schema
begin
  raise_application_error(-20001, 'Dont do it!');
end;
/
--
truncate table foo;
/*
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Dont do it!
*/
select count(*) from foo;
/*
COUNT(*)
----------------------
0
*/


Postgres: Sum from multiple joins resulting in values being more than expected I have 3 tables: invoices, invoice_payments, invoice_credit_notes. I want to fetch all those invoices where the total paid amount is less than the grand_total amount in invoices table.

An invoice can have multiple invoice_payments as well as multiple invoice_credit_notes.

invoice_payments has amount and discount field in it.

invoice_credit_notes has amount field in it.

Here's what I came up with:

SELECT invoices.* FROM invoices
LEFT OUTER JOIN invoice_payments ON invoice_payments.invoice_id = invoices.id
LEFT OUTER JOIN invoice_credit_notes ON invoice_credit_notes.invoice_id = invoices.id
GROUP BY invoices.id
HAVING ((COALESCE(SUM(invoice_payments.amount + invoice_payments.discount), 0) + COALESCE(SUM(invoice_credit_notes.amount), 0)) < ROUND(invoices.grand_total, 0))

But there's an issue with my solution as there can be multiple invoice_payments or invoice_credit_notes the (COALESCE(SUM(invoice_payments.amount + invoice_payments.discount), 0) + COALESCE(SUM(invoice_credit_notes.amount), 0) is returning an amount that is higher than the actual paid amout. This is most probably because multiple tables are being joined.

So, how to overcome this issue?

Join against the results of the aggregation:

select inv.*
from invoices inv
  left join (
    select invoice_id, COALESCE(SUM(amount + discount), 0) as total_payments
    from invoice_payments
    group by invoice_id
  ) ip on ip.invoice_id = inv.id
  left join (
    select invoice_id, coalesce(sum(amount),0) as total_credit
    from invoice_credit_notes
    group by invoice_id
  ) icn on icn.invoice_id = inv.id
where coalesce(ip.total_payments,0) + coalesce(icn.total_credit,0) < ROUND(inv.grand_total, 0))


8Kb row size limits ignored - but only sometimes! As a result of poorly tested code (not mine!) I've ended up with a table containing 319 VARBINARY(MAX) fields, plus two DATETIME fields and two UNIQUEIDENTIFIER fields. Obviously this is not ideal, but it should still be within the limits of what SQL Server can handle in terms of row size.

As I understand, VARBINARY(MAX) is stored on-table as a 24-byte pointer to off-row storage. 24*319 = 7656 bytes, plus the other four fields = 7704 bytes, so I'm just in the limit of what I can put into one row.

This all behaved fine until the code started inserting data into this table. It got 6 rows in before crashing with the message:

  Cannot create a row of size 8345 which is greater than the allowable maximum row size of 8060. The statement has been terminated.

This is very puzzling as none of the rows should be any larger than the others (on-row at least). I've scripted the table off and it looks fine. I did notice that it was created with ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] although I'm not sure what effect that would have.

Does anyone know what might be happening here, and more importantly how do I get around it?

Here's the code used to create the database, and the insert command

CREATE DATABASE MyBigDB;
GO
USE [MyBigDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Autogenerated table with really big data](
    [Primary_key] [uniqueidentifier] NULL,
    [A Foreign key] [uniqueidentifier] NULL,
    [Created] [datetime] NULL,
    [Updated] [datetime] NULL,
    [c1] [varbinary](max) NULL,
    [c2] [varbinary](max) NULL,
    [c3] [varbinary](max) NULL,
    [c4] [varbinary](max) NULL,
    ...
    ...
    etc
    ...
    ...
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

INSERT INTO [Autogenerated table with really big data] ([Primary_key], [A Foreign key], [Created], [Updated], [c1], [c2], [c3], [c4],............)
VALUES (<a UID>, <a UID>, <a DATETIME>, <a DATETIME>, <binary blob>, <binary blob>,............)

SQL Version:
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
Response:
The pointer to the LOB_DATA page (your 24 byte pointer) is only used when the row containing your VARBINARY(MAX) column (s) no longer can fit on the page.
I am still working on matching the exact scenario you mentioned above.
Reference(s):
Slot Array and Total Page Size

USE [master];
GO

IF DATABASEPROPERTYEX (N'RowSize', N'Version') > 0
BEGIN
    ALTER DATABASE [RowSize] SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [RowSize];
END;
GO

CREATE DATABASE [RowSize];
GO

USE [RowSize];
GO

IF OBJECT_ID('test' , 'U') IS NOT NULL
BEGIN
     DROP TABLE dbo.test;
END;

-- Create the wide table
CREATE TABLE dbo.test
(
      c1 UNIQUEIDENTIFIER NULL
    , c2 UNIQUEIDENTIFIER NULL
    , c3 DATETIME NULL
    , c4 DATETIME NULL
    , c5 VARBINARY(MAX) NULL
    , c6 VARBINARY(MAX) NULL
    , c7 VARBINARY(MAX) NULL
    , c8 VARBINARY(MAX) NULL
    , c9 VARBINARY(MAX) NULL
    , c10 VARBINARY(MAX) NULL
    , c11 VARBINARY(MAX) NULL
    , c12 VARBINARY(MAX) NULL
    , c13 VARBINARY(MAX) NULL
    , c14 VARBINARY(MAX) NULL
    , c15 VARBINARY(MAX) NULL
    , c16 VARBINARY(MAX) NULL
    , c17 VARBINARY(MAX) NULL
    , c18 VARBINARY(MAX) NULL
    , c19 VARBINARY(MAX) NULL
    , c20 VARBINARY(MAX) NULL
    , c21 VARBINARY(MAX) NULL
    , c22 VARBINARY(MAX) NULL
    , c23 VARBINARY(MAX) NULL
    , c24 VARBINARY(MAX) NULL
    , c25 VARBINARY(MAX) NULL
    , c26 VARBINARY(MAX) NULL
    , c27 VARBINARY(MAX) NULL
    , c28 VARBINARY(MAX) NULL
    , c29 VARBINARY(MAX) NULL
    , c30 VARBINARY(MAX) NULL
    , c31 VARBINARY(MAX) NULL
    , c32 VARBINARY(MAX) NULL
    , c33 VARBINARY(MAX) NULL
    , c34 VARBINARY(MAX) NULL
    , c35 VARBINARY(MAX) NULL
    , c36 VARBINARY(MAX) NULL
    , c37 VARBINARY(MAX) NULL
    , c38 VARBINARY(MAX) NULL
    , c39 VARBINARY(MAX) NULL
    , c40 VARBINARY(MAX) NULL
    , c41 VARBINARY(MAX) NULL
    , c42 VARBINARY(MAX) NULL
    , c43 VARBINARY(MAX) NULL
    , c44 VARBINARY(MAX) NULL
    , c45 VARBINARY(MAX) NULL
    , c46 VARBINARY(MAX) NULL
    , c47 VARBINARY(MAX) NULL
    , c48 VARBINARY(MAX) NULL
    , c49 VARBINARY(MAX) NULL
    , c50 VARBINARY(MAX) NULL
    , c51 VARBINARY(MAX) NULL
    , c52 VARBINARY(MAX) NULL
    , c53 VARBINARY(MAX) NULL
    , c54 VARBINARY(MAX) NULL
    , c55 VARBINARY(MAX) NULL
    , c56 VARBINARY(MAX) NULL
    , c57 VARBINARY(MAX) NULL
    , c58 VARBINARY(MAX) NULL
    , c59 VARBINARY(MAX) NULL
    , c60 VARBINARY(MAX) NULL
    , c61 VARBINARY(MAX) NULL
    , c62 VARBINARY(MAX) NULL
    , c63 VARBINARY(MAX) NULL
    , c64 VARBINARY(MAX) NULL
    , c65 VARBINARY(MAX) NULL
    , c66 VARBINARY(MAX) NULL
    , c67 VARBINARY(MAX) NULL
    , c68 VARBINARY(MAX) NULL
    , c69 VARBINARY(MAX) NULL
    , c70 VARBINARY(MAX) NULL
    , c71 VARBINARY(MAX) NULL
    , c72 VARBINARY(MAX) NULL
    , c73 VARBINARY(MAX) NULL
    , c74 VARBINARY(MAX) NULL
    , c75 VARBINARY(MAX) NULL
    , c76 VARBINARY(MAX) NULL
    , c77 VARBINARY(MAX) NULL
    , c78 VARBINARY(MAX) NULL
    , c79 VARBINARY(MAX) NULL
    , c80 VARBINARY(MAX) NULL
    , c81 VARBINARY(MAX) NULL
    , c82 VARBINARY(MAX) NULL
    , c83 VARBINARY(MAX) NULL
    , c84 VARBINARY(MAX) NULL
    , c85 VARBINARY(MAX) NULL
    , c86 VARBINARY(MAX) NULL
    , c87 VARBINARY(MAX) NULL
    , c88 VARBINARY(MAX) NULL
    , c89 VARBINARY(MAX) NULL
    , c90 VARBINARY(MAX) NULL
    , c91 VARBINARY(MAX) NULL
    , c92 VARBINARY(MAX) NULL
    , c93 VARBINARY(MAX) NULL
    , c94 VARBINARY(MAX) NULL
    , c95 VARBINARY(MAX) NULL
    , c96 VARBINARY(MAX) NULL
    , c97 VARBINARY(MAX) NULL
    , c98 VARBINARY(MAX) NULL
    , c99 VARBINARY(MAX) NULL
    , c100 VARBINARY(MAX) NULL
    , c101 VARBINARY(MAX) NULL
    , c102 VARBINARY(MAX) NULL
    , c103 VARBINARY(MAX) NULL
    , c104 VARBINARY(MAX) NULL
    , c105 VARBINARY(MAX) NULL
    , c106 VARBINARY(MAX) NULL
    , c107 VARBINARY(MAX) NULL
    , c108 VARBINARY(MAX) NULL
    , c109 VARBINARY(MAX) NULL
    , c110 VARBINARY(MAX) NULL
    , c111 VARBINARY(MAX) NULL
    , c112 VARBINARY(MAX) NULL
    , c113 VARBINARY(MAX) NULL
    , c114 VARBINARY(MAX) NULL
    , c115 VARBINARY(MAX) NULL
    , c116 VARBINARY(MAX) NULL
    , c117 VARBINARY(MAX) NULL
    , c118 VARBINARY(MAX) NULL
    , c119 VARBINARY(MAX) NULL
    , c120 VARBINARY(MAX) NULL
    , c121 VARBINARY(MAX) NULL
    , c122 VARBINARY(MAX) NULL
    , c123 VARBINARY(MAX) NULL
    , c124 VARBINARY(MAX) NULL
    , c125 VARBINARY(MAX) NULL
    , c126 VARBINARY(MAX) NULL
    , c127 VARBINARY(MAX) NULL
    , c128 VARBINARY(MAX) NULL
    , c129 VARBINARY(MAX) NULL
    , c130 VARBINARY(MAX) NULL
    , c131 VARBINARY(MAX) NULL
    , c132 VARBINARY(MAX) NULL
    , c133 VARBINARY(MAX) NULL
    , c134 VARBINARY(MAX) NULL
    , c135 VARBINARY(MAX) NULL
    , c136 VARBINARY(MAX) NULL
    , c137 VARBINARY(MAX) NULL
    , c138 VARBINARY(MAX) NULL
    , c139 VARBINARY(MAX) NULL
    , c140 VARBINARY(MAX) NULL
    , c141 VARBINARY(MAX) NULL
    , c142 VARBINARY(MAX) NULL
    , c143 VARBINARY(MAX) NULL
    , c144 VARBINARY(MAX) NULL
    , c145 VARBINARY(MAX) NULL
    , c146 VARBINARY(MAX) NULL
    , c147 VARBINARY(MAX) NULL
    , c148 VARBINARY(MAX) NULL
    , c149 VARBINARY(MAX) NULL
    , c150 VARBINARY(MAX) NULL
    , c151 VARBINARY(MAX) NULL
    , c152 VARBINARY(MAX) NULL
    , c153 VARBINARY(MAX) NULL
    , c154 VARBINARY(MAX) NULL
    , c155 VARBINARY(MAX) NULL
    , c156 VARBINARY(MAX) NULL
    , c157 VARBINARY(MAX) NULL
    , c158 VARBINARY(MAX) NULL
    , c159 VARBINARY(MAX) NULL
    , c160 VARBINARY(MAX) NULL
    , c161 VARBINARY(MAX) NULL
    , c162 VARBINARY(MAX) NULL
    , c163 VARBINARY(MAX) NULL
    , c164 VARBINARY(MAX) NULL
    , c165 VARBINARY(MAX) NULL
    , c166 VARBINARY(MAX) NULL
    , c167 VARBINARY(MAX) NULL
    , c168 VARBINARY(MAX) NULL
    , c169 VARBINARY(MAX) NULL
    , c170 VARBINARY(MAX) NULL
    , c171 VARBINARY(MAX) NULL
    , c172 VARBINARY(MAX) NULL
    , c173 VARBINARY(MAX) NULL
    , c174 VARBINARY(MAX) NULL
    , c175 VARBINARY(MAX) NULL
    , c176 VARBINARY(MAX) NULL
    , c177 VARBINARY(MAX) NULL
    , c178 VARBINARY(MAX) NULL
    , c179 VARBINARY(MAX) NULL
    , c180 VARBINARY(MAX) NULL
    , c181 VARBINARY(MAX) NULL
    , c182 VARBINARY(MAX) NULL
    , c183 VARBINARY(MAX) NULL
    , c184 VARBINARY(MAX) NULL
    , c185 VARBINARY(MAX) NULL
    , c186 VARBINARY(MAX) NULL
    , c187 VARBINARY(MAX) NULL
    , c188 VARBINARY(MAX) NULL
    , c189 VARBINARY(MAX) NULL
    , c190 VARBINARY(MAX) NULL
    , c191 VARBINARY(MAX) NULL
    , c192 VARBINARY(MAX) NULL
    , c193 VARBINARY(MAX) NULL
    , c194 VARBINARY(MAX) NULL
    , c195 VARBINARY(MAX) NULL
    , c196 VARBINARY(MAX) NULL
    , c197 VARBINARY(MAX) NULL
    , c198 VARBINARY(MAX) NULL
    , c199 VARBINARY(MAX) NULL
    , c200 VARBINARY(MAX) NULL
    , c201 VARBINARY(MAX) NULL
    , c202 VARBINARY(MAX) NULL
    , c203 VARBINARY(MAX) NULL
    , c204 VARBINARY(MAX) NULL
    , c205 VARBINARY(MAX) NULL
    , c206 VARBINARY(MAX) NULL
    , c207 VARBINARY(MAX) NULL
    , c208 VARBINARY(MAX) NULL
    , c209 VARBINARY(MAX) NULL
    , c210 VARBINARY(MAX) NULL
    , c211 VARBINARY(MAX) NULL
    , c212 VARBINARY(MAX) NULL
    , c213 VARBINARY(MAX) NULL
    , c214 VARBINARY(MAX) NULL
    , c215 VARBINARY(MAX) NULL
    , c216 VARBINARY(MAX) NULL
    , c217 VARBINARY(MAX) NULL
    , c218 VARBINARY(MAX) NULL
    , c219 VARBINARY(MAX) NULL
    , c220 VARBINARY(MAX) NULL
    , c221 VARBINARY(MAX) NULL
    , c222 VARBINARY(MAX) NULL
    , c223 VARBINARY(MAX) NULL
    , c224 VARBINARY(MAX) NULL
    , c225 VARBINARY(MAX) NULL
    , c226 VARBINARY(MAX) NULL
    , c227 VARBINARY(MAX) NULL
    , c228 VARBINARY(MAX) NULL
    , c229 VARBINARY(MAX) NULL
    , c230 VARBINARY(MAX) NULL
    , c231 VARBINARY(MAX) NULL
    , c232 VARBINARY(MAX) NULL
    , c233 VARBINARY(MAX) NULL
    , c234 VARBINARY(MAX) NULL
    , c235 VARBINARY(MAX) NULL
    , c236 VARBINARY(MAX) NULL
    , c237 VARBINARY(MAX) NULL
    , c238 VARBINARY(MAX) NULL
    , c239 VARBINARY(MAX) NULL
    , c240 VARBINARY(MAX) NULL
    , c241 VARBINARY(MAX) NULL
    , c242 VARBINARY(MAX) NULL
    , c243 VARBINARY(MAX) NULL
    , c244 VARBINARY(MAX) NULL
    , c245 VARBINARY(MAX) NULL
    , c246 VARBINARY(MAX) NULL
    , c247 VARBINARY(MAX) NULL
    , c248 VARBINARY(MAX) NULL
    , c249 VARBINARY(MAX) NULL
    , c250 VARBINARY(MAX) NULL
    , c251 VARBINARY(MAX) NULL
    , c252 VARBINARY(MAX) NULL
    , c253 VARBINARY(MAX) NULL
    , c254 VARBINARY(MAX) NULL
    , c255 VARBINARY(MAX) NULL
    , c256 VARBINARY(MAX) NULL
    , c257 VARBINARY(MAX) NULL
    , c258 VARBINARY(MAX) NULL
    , c259 VARBINARY(MAX) NULL
    , c260 VARBINARY(MAX) NULL
    , c261 VARBINARY(MAX) NULL
    , c262 VARBINARY(MAX) NULL
    , c263 VARBINARY(MAX) NULL
    , c264 VARBINARY(MAX) NULL
    , c265 VARBINARY(MAX) NULL
    , c266 VARBINARY(MAX) NULL
    , c267 VARBINARY(MAX) NULL
    , c268 VARBINARY(MAX) NULL
    , c269 VARBINARY(MAX) NULL
    , c270 VARBINARY(MAX) NULL
    , c271 VARBINARY(MAX) NULL
    , c272 VARBINARY(MAX) NULL
    , c273 VARBINARY(MAX) NULL
    , c274 VARBINARY(MAX) NULL
    , c275 VARBINARY(MAX) NULL
    , c276 VARBINARY(MAX) NULL
    , c277 VARBINARY(MAX) NULL
    , c278 VARBINARY(MAX) NULL
    , c279 VARBINARY(MAX) NULL
    , c280 VARBINARY(MAX) NULL
    , c281 VARBINARY(MAX) NULL
    , c282 VARBINARY(MAX) NULL
    , c283 VARBINARY(MAX) NULL
    , c284 VARBINARY(MAX) NULL
    , c285 VARBINARY(MAX) NULL
    , c286 VARBINARY(MAX) NULL
    , c287 VARBINARY(MAX) NULL
    , c288 VARBINARY(MAX) NULL
    , c289 VARBINARY(MAX) NULL
    , c290 VARBINARY(MAX) NULL
    , c291 VARBINARY(MAX) NULL
    , c292 VARBINARY(MAX) NULL
    , c293 VARBINARY(MAX) NULL
    , c294 VARBINARY(MAX) NULL
    , c295 VARBINARY(MAX) NULL
    , c296 VARBINARY(MAX) NULL
    , c297 VARBINARY(MAX) NULL
    , c298 VARBINARY(MAX) NULL
    , c299 VARBINARY(MAX) NULL
    , c300 VARBINARY(MAX) NULL
    , c301 VARBINARY(MAX) NULL
    , c302 VARBINARY(MAX) NULL
    , c303 VARBINARY(MAX) NULL
    , c304 VARBINARY(MAX) NULL
    , c305 VARBINARY(MAX) NULL
    , c306 VARBINARY(MAX) NULL
    , c307 VARBINARY(MAX) NULL
    , c308 VARBINARY(MAX) NULL
    , c309 VARBINARY(MAX) NULL
    , c310 VARBINARY(MAX) NULL
    , c311 VARBINARY(MAX) NULL
    , c312 VARBINARY(MAX) NULL
    , c313 VARBINARY(MAX) NULL
    , c314 VARBINARY(MAX) NULL
    , c315 VARBINARY(MAX) NULL
    , c316 VARBINARY(MAX) NULL
    , c317 VARBINARY(MAX) NULL
    , c318 VARBINARY(MAX) NULL
    , c319 VARBINARY(MAX) NULL
    , c320 VARBINARY(MAX) NULL
    , c321 VARBINARY(MAX) NULL
    , c322 VARBINARY(MAX) NULL
    , c323 VARBINARY(MAX) NULL
);
GO
/* I GET THIS WARNING...

Warning: The table "test" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

*/

-- Create one row
INSERT INTO dbo.test (c1 , c2 , c3 , c4)
    VALUES (NEWID() , NEWID() , GETDATE() , GETDATE());

-- Is it there?
SELECT * FROM dbo.test;
GO

-- How many pages?
DBCC IND (RowSize,test,-1); -- We have the IAM page and a single data page
GO                          -- Note that the row is stored as IN_ROW_DATA at this point.
DBCC TRACEON(3604);
GO
DBCC PAGE (RowSize, 1, 283, 3);
GO

/*
m_freeCnt = 7999
10003400 c203754c 9b36f44b 8dafee3b b32bd7c6
3106ac3a 8ec6854e b68291e6 4ccf7872 b321e100
f2a40000 b321e100 f2a40000 4301f0ff ffffffff
ffffffff ffffffff ffffffff ffffffff ffffffff
ffffffff ffffffff ffffffff ffff07

Tag A:              0x10
Tag B:              0x00
Null Bitmap Offset: 0x0034 = 52 dec.
GUID 1:             c203754c 9b36f44b 8dafee3b b32bd7c6
GUID 2:             3106ac3a 8ec6854e b68291e6 4ccf7872
DATETIME 1:         b321e100 f2a40000
DATETIME 2:         b321e100 f2a40000
Column Count:       0x0143 = 323 dec.
NULL Bitmap:        0xc0fff...

Variable Length Column Count
+ Variable Length Column Offset Array
+ Variable Length Columns: Do not exist at the moment

Total Overhead:
    Tag A:              1B
    Tag B:              1B
    Null Bitmap Offset: 2B
    Column Count:       2B
    NULL Bitmap:        41B
    Page Header:        96B
    Slot Array:         2B  = 145B

Fixed Size of Row:
    (16 + 16 + 8 + 8)B = 48B

Total Used Bytes: 145B + 48B = 193B = 8192B [8K page] - 7999B [m_freeCnt]

*****************************
Note: There are no pointers stored. The row is stored as IN_ROW_DATA at this point.
*****************************
*/

-- Update one row
UPDATE dbo.test
SET  c5  = CONVERT(VARBINARY(MAX), REPLICATE(0x11, 7961));
GO

-- What is m_freeCnt now?
--      m_freeCnt = 34

-- How many pages?
DBCC IND (RowSize,test,-1); -- We have the IAM page and a single data page
GO
DBCC PAGE (RowSize, 1, 283, 3);
GO

/*

m_freeCnt can be made to equal 20 when snapshot isolation is turned on and used,
but it cannot go lower than that, since the remaining bytes are
reserved for future use by the SQL Server team

Variable Length Column Count:         0x0001 (2B)
Variable Length Column Offset Array:  0x157c (2B)
Variable Length Columns:              0x11111111...

*/

-- Update one row
UPDATE dbo.test
SET  c5  = CONVERT(VARBINARY(MAX), REPLICATE(0x11, 7962));
GO

-- How many pages?
DBCC IND (RowSize,test,-1); -- We have 2 IAM pages, one IN_ROW_DATA page, and one LOB_DATA page
GO
DBCC PAGE (RowSize, 1, 283, 3);
GO

/*

Your 24 byte pointer is on page 283 ( the IN_ROW_DATA page)

*/


Postgres caching and bytea I have a DB which, among other things, stores images (as bytea, if that's interesting). It also stores users' metadata, activity logs, etc., but images currently take 500MB out of the total 600MB data.

I'm a complete newbie, but if I understand correctly PG has (at least) 2 pools of buffers, one for indexes and one for "heap" (data).

I imagine that my images 'pollute' the heap cache, making virtually nothing else cached (the cache obviously is not large enough to accommodate all the images).

Can I stop the image blocks only (I don't mind the image indexes being cached) from being cached?

I think the first question is how much RAM the system has.  If you have enough that the database fits comfortably in RAM then this is not a major problem.  Basically the OS disk cache is very fast, and if the db is sitting there effectively, then you don't have anything to worry about.  If you are on a system with, say, 4GB RAM and not a whole lot else running, I wouldn't worry.

The problem happens when this is not true, and then the shared buffers try to hold the most frequently used information.  I would expect that caching would be ok there too. What you have to remember is that the PostgreSQL cache is slower than the OS cache because it provides features aimed at keeping frequently used data in the cache.

Of course all of the above depends highly on your query patterns.


Maintaining permissions on a replicated view after reinitialization When we were on SQL Server 2005, there was some way that I was able to keep custom permissions on a view at the subscriber.

Now that I'm on SQL Server 2012, there doesn't seem to be a way to do that.

In the article properties of a table, there is the "Action if name is in use" property "Truncate all data in the existing object" which will keep any permissions on the table.

However, views do not have this option. It is either "Keep existing object unchanged" or "Drop existing object and create a new one." If I update my view, I want the changes to be reflected in the replicated view, but I don't want my custom permissions to disappear.

Am I looking at starting a post-reinitialization script for this purpose or is there something I'm missing?

I ended up using a post-initialization script to grant this permission at the subscriber and it worked great.


MongoDB failover process Hello guys I've been all over the MongoDB documentation (http://docs.mongodb.org) and I can't find a solution to my problem. Imagine the following scenario:

I have 2 Nodes (1000Km apart). Both nodes can communicate with the other;
Node1 has several replicasets and therefore several Masters, Slaves and Arbiters;
Node2 has a replica (slave) and a hidden replica (hidden slave) of each Master in Node1.
Here's a diagram to help you guys understand

I want to know what might happen if:

The link between the two nodes fails;
My guess is the non hidden slaves in Node2 will be elected Masters. What happens when the link between nodes returns? 2 masters?

Node 1 fails completely;
Same scenario? 2 Masters when Node1 returns?

Is this the best way to do this? Any sugestions on how should I do it so I won't lose any information if Node1 fails completely (avoiding the two masters situation)

So assuming that node(n) is a physical box, and the host(n) is a mongod process...If the link between the nodes fails, nothing cool will happen :).

node1 will continue operating as normal, and node2 will just sit there asking wtf.  The regular secondary will only have 2/4 votes which is not the strict majority.

If node1 fails, then you will have to manually configure the secondary on node2 as the primary.

When node1 comes back online, the previous primary will come back online as a secondary and it will detect any unfinished writes, and do a rollback which will require manual intervention.

Keeping in mind that you need a strict majority to elect a new primary, I would try putting the arbiters on their own system that is independent of the two nodes.


Normalize End Date - Nulls or separate table I was hoping for some advice on best practice in schema design - as far as End Date values are concerned.

In this case it is for a Suppliers table - we are required to store information on the Start Date and - should the relationship finish the End Date.

In an environment with low Supplier turnover - I can see the argument for allowing a nullable end date column in the Supplier Table.

Conversely, in a high turnover environment for pushing SuppierID and EndDate into a separate SupplierEndDate table.

The majority of articles I have read - have concluded that it should be judged on a case by case basis. However, I tend to lean more toward creating SupplierEndDate Table.

Is there a definitive best practice for OLTP schema or at least a favoured approach?

When the supplier 'comes back' then do you want to set the EndDate to null again or do you want to keep track of the periods in which the supplier supplied to you? In the last case you must put the StartDate and EndDate in a separate table. If you put it in a separate table then you always must do a join to see if it is an active supplier unless you put an active Y/N switch in the supplier row.


Return a percentage of the full record set I'm pulling back a list of orders but I need to only display a percentage of the records.  for example we want the top 30 or top 50.  The percentagbe is variable so I'd like to not use TOP.  I tried the code below but I'm getting back no records.

SELECT x.SelectionCriteriaID,
       x.DeliveryCriteriaID,
       x.SurveyID,
       x.intappid,
       x.intcustomerID,
       x.intorderid,
       x.RowNumber RowNumber,
       x.cntoid
  FROM (SELECT @SelectionCriteriaID SelectionCriteriaID,
               @DeliveryCriteriaID DeliveryCriteriaID,
               @SurveyID SurveyID,
               ord.intappid,
               ord.intcustomerID,
               ord.intorderid,
               ord.cntoid,
               ROW_NUMBER() OVER (ORDER BY ord.intcustomerid) AS RowNumber
          FROM ord
         WHERE ord.cntoid >= ISNULL(@MinNumberOrders, 1)
           AND ord.cntoid < ISNULL(@MaxNumberOrders, 999999999))x
 GROUP BY x.SelectionCriteriaID, x.DeliveryCriteriaID, x.SurveyID, x.intappid, x.intcustomerID, x.intorderid, x.RowNumber, x.cntoid
 HAVING x.rownumber < MAX(x.RowNumber)/(100/@percent)

You really should be using the TOP clause.  This is perfectly legal (assuming you're using SQL Server):

declare @top_val int = 30

select top (@top_val) percent
    col1,
    col2,
    col3
from yourTable
order by <something>;

The above query will select the top 30% of the rows.  As far as I can tell from your question, this satisfies your requirements and is the simplest method.


computed column making the select query to run slow We are creating indexes, as per the requirements, on one server and copying all the indexes to the other servers. All servers have similar objects & schema but different resource configuration values.

We have an issue where a query that joins 4 views is completing on one server but the same query is taking 4 minutes on the other server. If I take out the computed column from the select list it is completing in 4 seconds.

The two execution plans are quite different.

1)  Is this a good plan to copy the indexes blindly where SQL is choosing different execution plans?

2) I have also tried to create the index on the computed column on the base table, still no luck, any further suggestions? ( We can’t create an index on views for some reason.)

"Is this a good plan to copy the indexes." Indexes exist to improve the response time of read operations. Those operations arise from the workload. If the workload differs, the indexes should differ, too.

For example, it is not uncommon to have one server handle the OLTP activity and replicate the data to a second server where analytic queries are processed. The first server's work will be mostly single row lookups. The second's range scans and aggregates. In this case having different indexes on each server makes good sense. If all indexes were on both servers there would be additional, unnecessary work to do during writes and maintenance.


SQL - Best way to limit access to small number of tables I have a login I've created on SQL Server 2008, it's a SQL Server login not using AD. I want to give them access to every table in two databases except the tables that I store login information in for sites and such. This is one table in each database. Ideally, I'd like them to automatically get access to any new tables added to either database without breaking that restriction on those two login info tables.

Just place a DENY ALL  on the table for that user. DENYs are always evaluated first, and will override the GRANT off SELECT to the overall schema:

DENY ALL ON [schema].[object] TO {user_name}

There is no reason to move the login table into a separate schema.


mysqldump Unknown table 'COLUMN_STATISTICS' in information_schema (1109) I am having some difficulty with my MySQL database.
It appears the server suffered from a power outage during the night. As a result, MySQL wouldn't start when it reboots.
I viewed the error log and saw:

[ERROR] InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT
between the checkpoint 322393393 and the end 322394369. [ERROR]
InnoDB: Plugin initialization aborted with error Generic error
[ERROR] Plugin 'InnoDB' init function returned er [ERROR] Plugin
'InnoDB' registration as a STORAGE ENGINE failed. [ERROR] Failed
to initialize builtin plugins. [ERROR] Aborting

MySql will only start with innodb_force_recover=6
At this point I am trying to save the data by running from MySQL Workbench:
 mysqldump.exe --defaults-file=####  --user=#### --host=localhost --protocol=tcp --port=59452 --default-character-set=utf8 --single-transaction=TRUE --skip-triggers "BusinessManager"
Workbench shows me this error:

mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'BusinessManager' AND TABLE_NAME = 'Activities';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
Operation failed with exitcode 2

However, the MySQL log shows:

[ERROR] InnoDB: Failed to find tablespace for table BusinessManager.Activities in the cache. Attempting to load the tablespace with space id 195

The log error is repeated for each table and the SQL dump fails.
Is there any way to save the data in the database and bypass these errors?
Thanks!

As far as the the Workbench error is concerned what you need to do is disable the "column statistics" argument on export. So, you should write the command adding "--column-statistics=0". This should resolve the first-Workbench issue:

mysqldump.exe --defaults-file=####  --user=#### --host=localhost --protocol=tcp --port=59452 --default-character-set=utf8 --single-transaction=TRUE --skip-triggers --column-statistics=0 "BusinessManager"

Regarding tablespace error, the documentation describes it here:

On the new MySQL instance, recreate the table in a database of the same name using the command CREATE TABLE ...
Discard the tablespace of the newly created table:

ALTER TABLE database.tableName DISCARD TABLESPACE

Copy the orphan .ibd file from your backup directory to the new database directory using shell:

cp /backup_directory/tableName.ibd path/to/mysql-5.6/data/database/

Finally, import the orphan .ibd file. A warning is issued indicating that InnoDB will attempt to import the file without schema verification.

ALTER TABLE database.tableName IMPORT TABLESPACE; SHOW WARNINGS;

This should do it. As a test, you should run the following command in mysql:

    SELECT COUNT(*) FROM database.tableName;

If this executes correctly, tablespace error should disappear.


Is my postgres index being used? I have a large PostgeSQL 9.1 database in production use. I want to free up some disk space. There is a large index, and I'm unsure if it's being used.

select * from pg_stat_all_indexes where indexrelname = 'myindexname';
 relid | indexrelid | schemaname |     relname     |     indexrelname      | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-----------------+-----------------------+----------+--------------+---------------
 20625 |   19563303 | public     | mytablename     | myindexname           |   575598 |       281635 |             0
(1 row)

Is this index being used? idx_tup_fetch is 0. Does that mean it's not being used? Can I drop the index without affecting any reads/queries?

This database is in production, real world use. This index was recently rebuilt, and there has been real world use since. If it hasn't been used since then, it will never be.

The index is about 130G and the table 50G.

The documentation states the following:

  Indexes can be used via either simple index scans or "bitmap" index
  scans. In a bitmap scan the output of several indexes can be combined
  via AND or OR rules, so it is difficult to associate individual heap
  row fetches with specific indexes when a bitmap scan is used.
  Therefore, a bitmap scan increments the
  pg_stat_all_indexes.idx_tup_read count(s) for the index(es) it uses,
  and it increments the pg_stat_all_tables.idx_tup_fetch count for the
  table, but it does not affect pg_stat_all_indexes.idx_tup_fetch.

  Note: The idx_tup_read and idx_tup_fetch counts can be different even
  without any use of bitmap scans, because idx_tup_read counts index
  entries retrieved from the index while idx_tup_fetch counts live rows
  fetched from the table. The latter will be less if any dead or
  not-yet-committed rows are fetched using the index, or if any heap
  fetches are avoided by means of an index-only scan.

This gives you two possibilities.  The first one you've excluded by rebuilding the index, so one could guess this index is used exclusively via index-only scans and/or bitmap index scans.  You can confirm this buy checking the EXPLAIN output of your queries that may use this index.

You can read a bit more about bitmap scans on depesz's page.


What service that lets you create records/data into an immutable database storage system that cannot be deleted? I'm not sure if this exists or not, but is there a database storage system that is immutable, whereby it cannot be erased or be altered without someone knowning? Use case would be for public data that should not be manipulated or erased at all. And if there are any modifications it would be known. Also the data would have to be redundantly stored to prevent data loss or deletion.

Is there a service that lets you host any type of data/records in this decentralized manner?

While the specifics will depend on the database system itself this could be handled with pretty much any of the RDBMS platforms out there.  Permissions will pretty much handle what you want.  The process that puts the data in would need to have insert permissions and all other access would be select only.

As far as tracking the modifications - most of the vendors have some sort of auditing capabilities.  I am most familiar with Oracle and in that you can audit the activity of a user, activity on a specific table, or even any time a specific data modification statement (insert, update, delete) is executed.  Again this is vendor and version specific.

As a further protection - make sure to have good backups that can be used to restore to a specific point in time.  This will ensure that you can return the data to a specific state in case of an error or system failure of some type.


Why does null and true evaluate to null but null or true evaluates to true? Why does null and true evaluate to null in PostgreSQL but null or true evaluates to true?

I would have thought using null with and or or would've evaluated to null in both cases?

OR is defined that if either side is true the expression evaluates to true.
AND is defined such that if both sides are true boolean-3VL context, the expression evaluates to true;
null is not true.


Declare variable mysql I am new to mysql, I have been using SQL in the past. I am trying to insert rows into a table via creating a loop. But struggling with it. Any help?

DECLARE @minid  INT = (SELECT MIN(Product_ID) FROM
    Product_Report_Offer_Interaction_Jan_2012 pn)
DECLARE @maxid  INT = (SELECT max(Product_ID) FROM
    Product_Report_Offer_Interaction_Jan_2012 pn)

DECLARE @topid INT= @minid + 50000
BEGIN

WHILE ( @minid < @maxid )
    BEGIN
        insert into  Product_Report_Offer_Jan_2012 (Month, Product_ID, Offerview,
        EnlargeImage, DynamicPopUp, ShareonFacebook, BuyNowPress, Sendtofriend,
        Printed, StoreLocated, uLike, iPhoneOfferDetails, iPhoneStoreLocated,
        WishlistAd, Tweet, Catalogue1pageview, Catalogue2pageview, SearchListing,
        Featured, RelatedAdImpression, Wishlist)
        select pn.Month, pn.Product_ID, pn.Offerview, pn.EnlargeImage,
        pn.DynamicPopUp, pn.ShareonFacebook, pn.BuyNowPress, pn.Sendtofriend,
        pn.Printed, pn.StoreLocated, pn.uLike, pn.iPhoneOfferDetails,
        pn.iPhoneStoreLocated, pn.WishlistAd, pn.Tweet, pm.Catalogue1pageview,
        pm.Catalogue2pageview, pm.SearchListing, pm.Featured,
        pm.RelatedAdImpression,
        pm.Wishlist
        -- select count(*)
        -- select *
        from Product_Report_Offer_Interaction_Jan_2012 pn
            left join Product_Report_Offer_Impression_Jan_2012 pm on pn.product_ID = pm.product_ID
            where pn.product_id != '0'
                and pn.Product_ID BETWEEN @minid AND @topid

        SET @minid = @minid + 50000 + 1
        SET @topid = @topid + 50000 + 1

  END
END

Thanks jakub

User-defined session-scope variables, beginning with @ are not declared and are loosely and implicitly typed.  Change DECLARE to SET and drop the INT after the variable name if these are the variables you intend to use.

Program variables are declared and typed, and do not start with @.

http://dev.mysql.com/doc/refman/5.6/en/user-variables.html
http://dev.mysql.com/doc/refman/5.6/en/stored-program-variables.html


SQL Server Stored Procedure - Does Raiserror affect performance Does RAISERROR in a SQL Server stored procedure affect performance?

Would I be better off just having a try-catch wrapper around the whole procedure and just catch the errors in the database access layer?

Right now for logging in a user, I check if the user exists and RAISERROR if the user does not exist. Would I better just let the procedure return null and interpret that in the database access layer?

I would not worry about performance in this situation.
I would go with raising an error for 2 reasons:

You quit earlier (when you find out user does not exist), and make a clear message.
IMHO raising an error is more readable and makes your code clearer.


Does MongoDB produce a single file like sqlite does for an individual database? Can it? Does MongoDB store it's data like SQLite does at all?  A single file.  From what I could see it is using "wiredTiger," but I can't find a specific file for my database I created.

As at MongoDB 3.4, all of the standard storage engines (WiredTiger, MMAPv1, ..) create multiple files in the dbPath. For example, in WiredTiger there are a number of metadata files, journal files, and a file per collection and per index (see: Why is an empty MongoDB database so big?). Multiple files generally allow more granular concurrency and storage management; single-file databases are more common for embedded use cases.

The file and directory layout is dependent on the storage engine and server configuration options, so it is conceivable that someone could develop a new MongoDB storage engine which has a more concise file layout (but I'm not aware of anyone having done so).

Irrespective of the layout on disk, you could use the standard mongodump utility to create a single compressed & archived backup of a database to copy and restore on another system using mongorestore. The archive feature is available in MongoDB 3.2 or newer. For more information, see Archiving and Compression in MongoDB Tools.


How to Check and Report time taking queries I am using MySQL 5.6.24

How do I capture the queries which are using more system resources?

How do I capture slow running queries and queries not using indexes?

What is the best way to optimize the slow running query?

Thanks in advance

To capture the slow running queries in MySQL, please enable slow query log. Refer the below URL:

http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

To optimize the queries, you need to get the explain plan of the queries using EXPLAIN command:

EXPLAIN SELECT * FROM categories

Detailed documentation is available in the below URL:

https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/


How to Import Data to MySQL RDS Instance? I have a huge database approx 200GB (Some tables are more than 25GB in size).

We are willing to migrate to MySQL RDS Instance.but as it does not provide any ssh access so we can't copy file system over there(Like backup taken from Hot backup tools).

we will have to take the dump using mysqldump but it may take days to complete the backup and in same in restoring it into RDS.

As backup will lock the tables for a huge time so we will also require a long downtime.

How Can we do it Quickly?I am looking for Quick and dirty solution?
Also What are the limitation of the RDS Instance?

When we did something similar recently ~250GB, we first used an EC2 slave from production database as a staging slave with SSL and binary logging enabled  -- to make table dumps, etc. Converting any tables to InnoDB if you need here. Load data dumps from EC2 to RDS. Then used a binary log feeding strategy to load data and keep it current. At cutover time, flush the last binary log-- and load it into RDS and you're done. I prepped the logs first (removing any set cmds that require super) and just fed them to the mysql client on rds. Next time I want to try Tungsten replicator.


Password reset for database for VCenter Install I'm trying to reinstall vCenter SSO and it is prompting me for the "sa" password.  I set it up previously, but cannot remember what I used.  I uninstalled SSO (as well as the vCenter Server, and Inventory Service), and I'm running through setup again.  I can't get past setting up the database without the password.

How can I reset this?

newDBA,

Welcome to dba.stackexchange.com.  What you are looking for is the SQL Server SA password which was probably installed with VCenter, or it was installed prior and VCenter was configured to use that SQL Server.

Option 1:
If you have admin rights to the server, you can try logging in 'as a windows user' and seeing if the admin was configured to have SA rights.  If you can get in, you can change the SA password.

Option 2:
If you do not have access to SQL via Windows Logins, you can still use what some people consider an exploit, and access SQL Server via the NT Authority System userCheck out some blogs on it here, it's pretty easy to do.  This is the command as copied out of the blog:

PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

Recover access to a SQL Server instance.  Written by our very own Aaron Bertrand.

Option 3 (The nuclear option):
If you are sure there is nothing you need on this SQL Server and it was only installed for VCenter you could always uninstall and reinstall.  This is a last resort however, I don't see any reason why you would have to entertain this idea for your current issue.


Whats SQL Server NVARCHAR(max) equivalent in MySQL? Using SQL Server you just have to give the "MAX" parameter to the length of a text data type, but in MySQL there's no such a thing.

According to Ispirer:

  "n" is the maximum number of characters, optional

  Range:    1 ⇐ n ⇐ 21845 (65535 bytes is the maximum row size shared among all columns)

Does it mean that:

[SQL Server] "NVARCHAR(MAX)" == [MySQL]"NVARCHAR(N)"

Or do i have to say NVARCHAR(21845) as NVARCHAR(MAX) in MySQL?

According to this MySQL document the LONGTEXT datatype in MySQL is 4 Gigabytes.

http://wiki.ispirer.com/sqlways/mysql/data-types/longtext

Note that the number of characters depends on the encoding.


Which version of AdventureWorks has a column called CustomerId on sales.vIndividualCustomer? The SQL Native Client MOF Files here come with a Visual Studio Solution that queries adventure works for testing purposes. One of the first queries that application performs when it is compiled and run is

SELECT CustomerID, Title, FirstName, MiddleName, LastName, Suffix,
    Phone, EmailAddress, EmailPromotion, AddressType, AddressLine1,
    AddressLine2, City, StateProvinceName, PostalCode,
    CountryRegionName, Demographics
FROM Sales.vIndividualCustomer

This returns the following error on Adventureworks 2008, 2012 and 2014.

Msg 207, Level 16, State 1, Line 3
Invalid column name 'CustomerID'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Phone'.

I downloaded these three AdventureWorks editions off of CodePlex today. For 2008 annd 2012 I downloaded a zip file with an LDF and MDF that I attached via CREATE DATABASE FOR ATTACH. For the 2014 version of the database I restored a bak file with RESTORE DATABASE WITH MOVE. What file should I be downloading from CodePlex?

Which version of AdventureWorks has a column called CustomerId on sales.vIndividualCustomer?

SQL Server 2005 version of Adventureworks has CustomerId and Phone.

The schema is changed from 2008 for Sales.vIndividualCustomer view :


How can I speed up "show columns" on MySQL? My application depends on running "show columns" for certain tables. It takes about 60ms to run, whereas all our other queries take under a ms. Querying information_schema directly is even slower.

The database contains about 250 databases, with 100 to 200 tables per database (about 20k tables in total).

How can I find out why these operations are so slow?
Is there perhaps some setting I can change to make it run faster, or to cache it SQL-side?

(The application does about 14 such queries per page load - I'm well aware that this legacy code needs to be cleaned up, but looking for possible options while I work on the long-term fix.)

MySQL recalculates table statistics for certain operations that access INFORMATION_SCHEMA tables (SHOW COLUMNS is just a convenient alias for querying INFORMATION_SCHEMA.COLUMNS).  Set innodb_stats_on_metadata to be false, which will prevent this recalculation from occurring when you ask for metadata from the table.

SET GLOBAL innodb_stats_on_metadata=0;

and add the following to my.cnf

[mysqld]
innodb_stats_on_metadata = 0


Will Postgresql statements execute synchronously if autocommit=true? If a single application thread sends a series of INSERT statements and autocommit=true, will the statements be executed synchronously on the server?

I read that on the postgres server side, "there is one client process connected to exactly one server process",  but haven't found much more beyond that. I need synchronous execution to satisfy foreign key relationships, but I suspect the server (9.6) may run the statements concurrently.

With autocommit enabled each statement forms a separate transaction. In a single session (connection) only one transaction can be active at a time. However, transactions in multiple concurrent sessions can, and will run simultaneously. In other words, as long as you issue your insert statements using the same database connection they will execute serially.

Whether you do that in a single application thread or not is irrelevant, since it's possible to open multiple database connections in a single thread, as it is possible to reuse the same connection between multiple threads (with proper synchronization, of course).


search_path set and seen but not used! Even though I'm (apparently) setting the search_path correctly I still have to prefix everything with the schema. What's wrong here?  How do I get a default schema from the search_path?

psql blip
psql (9.4.1, server 9.4.4)
Type "help" for help.

blip=> \dt messages

no matching relations found.
blip=> \dt blip_schema.messages
            List of relations
   Schema    |   Name   | Type  | Owner
-------------+----------+-------+--------
 blip_schema | messages | table | dbuser
(1 row)
blip=> set search_path to "blip_schema,public";
SET
blip=> show search_path;
     search_path
----------------------
 "blip_schema,public"
(1 row)

blip=> \dt messages
No matching relations found.
blip=> \dt blip_schema.messages
            List of relations
   Schema    |   Name   | Type  | Owner
-------------+----------+-------+--------
 blip_schema | messages | table | dbuser
(1 row)

Double quotes denote identifiers that contain special characters, so set search_path to "blip_schema,public" will set the search path to a single schema named blip_schema,public.

The names you supply to set search_path have to follow regular naming rules, so no quotes at all, unless you have one schema that requires quoting:

set search_path to blip_schema,public;

or if you do want to use quotes, you need them for each identifier:

set search_path to "blip_schema","public";


What to monitor (key indicators) for MySQL server I have installed Munin for monitoring my MySQL database server. It enabled a large amount of MySQL plugins out of the box. Since running these plugins can be CPU expensive I want to limit them to the ones that I actually need, and that is going to tell me something usefull.

Binary/Relay Logs
Command Counters
Connections
Files and tables
InnoDB Buffer Pool
InnoDB Buffer Pool Activity
InnoDB Insert Buffer
InnoDB IO
InnoDB IO Pending
InnoDB Log
InnoDB Row Operations
InnoDB Semaphores
InnoDB Transactions
MyISAM Indexes
Network Traffic
Query Cache
Query Cache Memory
Replication
Select types
Slow Queries
Sorts
Table locks
Temporary objects

So my question is; which of these parameters are key indicators and has any value to monitor?

Without knowing more about your environment and application(s) that your supporting I would suggest starting here:

Innodb Performance Optimization Basics By Peter Zaitsev

This is a great article that will give you a better idea of what metrics you should be paying attention to on the regular.


Using Wildcard with `IN ('%')`? Is it possible to modify the ('%') to return a single row for the following query, i.e. while keeping the entire statement before it?

select 1 FROM DUAL WHERE TRIM('asdfsdf') in ('%')

I do not believe it is possible to use the LIKE wildcard in an IN statement.  To achieve what you want to do, you need to take out all your LIKE wildcards and put them in a separate OR clause.

WHERE string_col IN ('string1', 'string2')
   OR string_col LIKE '%string3%'
   OR string_col LIKE '%string4%'

But this seems like a REALLY bad idea ... I think you should update your post to answer Martin's question (in the comment) to get a better solution for what you want to do.  (or ask a different question)


SQL Server: Case expression returns NULL even with an ELSE Does anyone know why this case expression would be returning NULLs even when there is an ELSE?  It returns 'Normal' and 'Premature', but also numerous NULLs.

   select distinct(external_id), encounter_date,
(select
 case
   when h2.encounter_date < DATEADD(WEEK, -3, isnull(f2.edd_final_date,'01-jan-1900'))
      and h.encounter_date < DATEADD(dd, 29, isnull(t2.date_value,'01-jan-1900'))
     then 'Premature-Emergency'
   when h2.encounter_date < DATEADD(WEEK, -3, isnull(f2.edd_final_date,'01-jan-1900'))
     then 'Premature'
   when h.encounter_date < DATEADD(dd, 29, isnull(t2.date_value,'01-jan-1900'))
     then 'Emergency'
   else 'Normal'
 end
 from enhfile h2
inner join ar00file a2 on a2.patient_no=h2.patient_no and a2.trans_date=h2.encounter_date
      inner join endfile d2 on d2.patient_no=h2.patient_no and d2.encounter_no=h2.encounter_no
         and d2.charge_no=a2.charge_no
      left join fmhfile f2 on f2.patient_no = h2.patient_no
      left join notesdtl t2 on t2.patient_no=n.patient_no and t2.note_id=n.note_id
          and element_id in ('4559','4560')
 where h2.patient_no=a.patient_no and h2.encounter_date=
     (select MaxEncDate
      from (select MAX(h4.encounter_date) as MaxEncDate from enhfile h4
              inner join ENDFILE d4 on h4.PATIENT_NO=d4.patient_no and h4.ENCOUNTER_NO=d4.encounter_no
            where h4.patient_no=h2.patient_no
              and a2.procedure_code in ('59409','59410','59400','59514','59515','59510','59415')
           ) v
     )
  and f2.history_date=(select MaxHxDate
                            from (select MAX(f3.history_date) as MaxHxDate from fmhfile f3
                                  where f3.patient_no=h2.patient_no
                                 ) w
                      )
  group by h2.patient_no, h2.encounter_no, t2.date_value, h2.encounter_date, f2.edd_final_date)
  as Circumstance
from ar00file a
inner join mpfile m on m.patient_no=a.patient_no
inner join exidfile x on m.patient_no=x.patient_no
inner join chhfile c on a.procedure_code=c.procedure_code
inner join enhfile h on a.patient_no=h.patient_no and  a.trans_date=h.encounter_date
inner join endfile d on h.patient_no=d.patient_no and h.encounter_no=d.encounter_no
left join fmhfile f on f.patient_no=h.patient_no
left join noteshdr n on n.patient_no=h.patient_no and n.note_date_time=h.encounter_date
          and n.note_type='FSP'
left join notesdtl t on t.patient_no=n.patient_no and t.note_id=n.note_id
          and element_id in ('4559','4560')
where d.charge_no=a.charge_no
and a.trans_date between '01-jan-2016' and '30-jun-2016'
and a.PROCEDURE_CODE in ('58600','58611','58670','58671','58150','58150GY','58180GY','59525')

I would try adding some ISNULL functions to the left sides of the Case checks (h.encounter_date and h2.encounter_date), as I don't see a WHERE h.encounter_date/h2.encounter_date IS NOT NULL in your WHERE clause.


look for missing datetime records in database I have a python script that scrapes a webpage every minute of every day and saves the results in a postgres database.

As I browse through the database, I've noticed there are minutes here and there that are missing from the database. This may be because of my server's downtime or because of the website downtime (but the reason for the gaps is not relevant to my question here).

I'm trying to figure out the best way to catalog what minutes are missing from the database.

Right now I have a python script that creates a list of every minute of every day and use that list to iterate through the datetime column in my database table, something like this:

def pd(start, end, delta):
    c = start
    datelist = []
    while c < end:
        datelist.append(c)
        c += delta
    return datelist

Is there a way to do this directly using SQL?

You can do something similar.  First generate the minutes (if you do this often, probably you would like to store them in a permanent, indexed table), then pick the ones not in your table.  The query would look like (no looping!):

WITH minutes(m) AS (
    SELECT i::timestamp(0)
    FROM generate_series('2013-04-17 12:00:00', '2013-04-17 13:00:00', interval '1 minute') t(i)
)
SELECT m
FROM
    minutes
    LEFT JOIN your_table ON m = your_timestamp
WHERE your_table_id IS NULL;

You may have to transform your timestamp stored if the seconds part is non-zero.  And, with big amounts of data at least, you will need an index defined on the timestamp (and possibly your_table_id, it would help a lot if you use PostgreSQL 9.2).


Cost of updating one of several toasted columns I have a table in Postgresql 12 which has --- among other columns --- two columns a and b of type bytea which are most of the time TOASTed. STORAGE is set to EXTENDED for both of them.

Assuming all other columns could be stored in the non-toasted table, would an update to column b require writing column a again? Would Postgres store the values of column a and b of one row in one row of the TOAST table or in two or does it depend? And then on what?

You can assume the values in a and b occupy usually multiple kilobytes, up to several hundred kilobytes.

An experiment says more than a thousand theoretical considerations:

/* helper function to create large byteas */
CREATE OR REPLACE FUNCTION noise(size integer) RETURNS bytea
   LANGUAGE plpgsql AS
$$DECLARE
   b bytea := '';
   i integer;
BEGIN
   FOR i IN 0..size/16 LOOP
      b := b || ('\x' || md5(random()::text))::bytea;
   END LOOP;
   RETURN b;
END;$$;

CREATE TABLE toastme (
   id integer PRIMARY KEY,
   a bytea,
   b bytea
);

INSERT INTO toastme VALUES (
   1,
   noise(2048),
   noise(2048)
);

Let's check the physical location of the new row in the main table:

SELECT ctid
FROM toastme
WHERE id = 1;

 ctid
-------
 (0,1)
(1 row)

Now let's have a look at the toast table, along with the physical location of the rows:

SELECT ctid
FROM toastme
WHERE id = 1;

 ctid
-------
 (0,2)

SELECT reltoastrelid::regclass AS toast_table
FROM pg_class
WHERE relname = 'toastme' \gset

SELECT ctid,
       chunk_id,
       chunk_seq,
       substring(chunk_data FOR 10)
FROM :toast_table;

 ctid  | chunk_id | chunk_seq |       substring
-------+----------+-----------+------------------------
 (0,1) |    57174 |         0 | \x0f9eae178bcda3dbf745
 (0,2) |    57174 |         1 | \xecbe99e83b8416d4f31c
 (0,3) |    57175 |         0 | \x4b3b9425742c299c7d20
 (0,4) |    57175 |         1 | \x43d1b1782ad3e0c1bf4c
(4 rows)

There are two chunk_ids for the two table columns, each of which consists of two “slices” of TOAST.

Let's update one of the toasted columns:

UPDATE toastme
SET a = noise(2048)
WHERE id = 1;

Now let's check again:

SELECT ctid,
       chunk_id,
       chunk_seq,
       substring(chunk_data FOR 10)
FROM :toast_table;

 ctid  | chunk_id | chunk_seq |       substring
-------+----------+-----------+------------------------
 (0,3) |    57175 |         0 | \x4b3b9425742c299c7d20
 (0,4) |    57175 |         1 | \x43d1b1782ad3e0c1bf4c
 (0,5) |    57176 |         0 | \xc780f3b8e2a5e4976f4a
 (0,6) |    57176 |         1 | \x5702616863dfbc54829b
(4 rows)

What do we see:

When there is an update of the table row — even if only a toasted value is modified — a new row version is written.
Only the toasted values that are modified are changed, the unmodified toasted values stay the same.


MongoDB, Getting Started and Connecting with Compass I'm trying to connect to my mongoDB running as a service on my rPi3B+, using openSuse Tumbleweed 64bit, mongo version 3.6.2, from my laptop sharing the same LAN/Wifi (pi on LAN & laptop on Wifi).

I completed this install guide(https://www.osradar.com/install-mongodb-opensuse-15/) directly after a fresh install of the rPi3 JeOS image provided here: https://en.opensuse.org/HCL:Raspberry_Pi3

Now when I tried to connect to the db service using compass using the same IP I used to connect to the Pi over ssh, and the standard port of 27017. It then reports "MongoDB not running on the provided host and port".

However, I have the shell running on the Pi at the same time, and when I exit the shell so that compass is the only client attempting a connection. When I run sudo systemctl status mongodb the service is active.

When I run netstat -lntu I get the following output:

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:27017         0.0.0.0:*               LISTEN
tcp6       0      0 :::22                   :::*                    LISTEN
tcp6       0      0 ::1:27017               :::*                    LISTEN
udp        0      0 192.168.1.163:123       0.0.0.0:*
udp        0      0 127.0.0.1:123           0.0.0.0:*
udp        0      0 0.0.0.0:123             0.0.0.0:*
udp        0      0 0.0.0.0:68              0.0.0.0:*
udp6       0      0 fe80::ba27:ebff:feb:123 :::*
udp6       0      0 ::1:123                 :::*
udp6       0      0 :::123                  :::*

When I ping the Pi's IP address using the local IP I get the expected returns showing that it is reachable.

Are there some settings I need to change from within the shell to make it reachable by Compass?

Alrighty, I guess this was a noob question.

Answers can be found here as well: https://stackoverflow.com/questions/42739166/could-not-connect-to-mongodb-on-the-provided-host-and-port#49220957

Pretty sure many of their different methods would work. For some reason mongo --bind_ip_all didn't work for me and seems like the simplest. I think it has something to do with openSuse's arm version doesn't use mongod and instead just mongo.

What worked for me was going into my '/etc/mongodb.conf' file and changing the boundIP from 127.0.0.1,::1 to 0.0.0.0,::1

Not to sure what the ::1 is for but I can connect to the db with compass now.


Designing a dimensional DB off a normalized source that already implements SCD's I have built an SSIS ETL to integrate various data sources (one from MySQL, two from SQL Server) into a single SQL Server relational and normalized database, which I've called [NDS].

The SSIS ETL handles type-2 updates, and so the [NDS] generates surrogate keys and SCD tables include an [_EffectiveFrom] timestamp and a nullable [_EffectiveTo] column, and there are constraints for the natural keys and beautiful foreign keys linking all the data together.

Now, I wanted to build an SSAS dimensional database off of it, and it didn't take too long before I realized I was setting myself up for a snowflake schema:

So I'm thinking of adding a new [DDS] (relational) database, to create the actual dimension and fact tables that will feed the DSV's for the SSAS db.

This [DDS] database would be as denormalized as humanly possible, so as to "flatten" the facts and dimensions (like, [OrderHeaders]+[OrderDetails] into an [Orders] fact table, and [CustomerStores]+[Customers]+[SalesReps] into some [Customers] dimension table) - doing this should not only make it easier for me to build the dimension hierarchies in SSAS, it should also make it easier to come up with an actual star schema.

I have a few questions though:

Can I reuse a subset of my existing surrogate keys? I'm thinking to take the existing key for the most granular level and make that the dimension key. Is that a good approach, or should I just ignore the [NDS] surrogate keys and make the [DDS] (relational db) generate a new set of surrogate keys?
How to handle SCD's? For example, "Materials" and "Suppliers" will generate new records in [NDS] when some specific fields change in the source system... I think I'll have to design the SSIS ETL to only load the "last image" records into the [DDS] db, and then re-implement the type-2 updates in that process, i.e. treat the [NDS] as a "source system" that keeps history, while duplicating everything in this [DDS] database. But then, why would I need to keep history in the [NDS] and the [DDS]? Clearly something's not right.

Am I setting myself up for a Big Mess™, or I'm on the right track?

As you are seeing, one of the benefits of Kimball Dimensional Modeling is that the data warehouse design is essentially your SSAS design. While there are always exceptions, you can typically select a table in the DSV and immediately move to hierarchy design, cube relationships etc.

I'd recommend moving to the DDS with the caveat that you phase out the NDS. For the simple reason that you mention with the SCD Type II - there's no reason to duplicate all of that data, ETL, and code base. Keeping both leads to an overly complex solution with a lot of maintenance and risk - this is the primary Big Mess™ you need to avoid.

Here's the justification:

The DDS sounds like a Kimball dimensional design that will be easily
understood and maintained by any new hires and contractors
There are lots of proven designs for schema and ETL that go along with this, making it easier to design and maintain
Easier to deal with large data quantities via options like fact partitioning and column store indexes
Easier DW queries in the future for yourself and power users
Simpler overall design

The alternative design I'd suggest only if you have small quantities of data, no significant expectation of increases, and no capacity to switch to the data warehouse. This suggestion ultimately includes a lot of the design work that would otherwise go into the DW and as such is similar to keeping the NDS and creating the DDS:

You can create your cubes with queries manifested as Views or hard-coded into the DSV.

It sounds like you overcame a lot of hurdles and created a great functional solution with the NDS. Unfortunately what it doesn't do are two important things that dimensional modeling provides: simple query patterns and easy translation into multidimensional analytics. Thankfully, a lot of the ETL design is likely useful as a template or starting point for loading a different table structure.


How does a tagging article database look like? I have a article website and each article has some tags. How should be my database structure?

This is my article table (article):

article_id (key)  |  article_content  |  article_title  |  article_lang_id

This is the list of my tags (tag_list):

tag_list_id (key)  |  tag_list_name  |  tag_list_description

And this is my tag table (tag_link):

  tag_id (key)   |   article_id (f-key)  |   tag_list_id (f-key)

I echo the article content with php, how can I echo (I mean showing tag bellow content) tags bellow the content?

This is my question:

1- How can I show each content tags near content?

(each content can has less than 5 tags)

2-What is the MYSQL query that I can run to select my tags with content?
(is there any professional MySQL orders to select content with tags form tag_link and then select the name of tag from tag_list and then show it near article, can I do it with MySQL orders like join, having, on and several other? How?)

3-Is my database structure for tagging true?

(I can do this with php variables and ... , but I need to do it by MySQL)

1 & 2:use inner join

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

3:
yes your structure is true


Improve performance for order by with columns from many tables Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two columns takes 5 minutes). E.g.:

select r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
from requests r
inner join samples s on (s.code = r.sample_code)
order by s.barcode  asc , r.code asc
limit 21;

Table Info:

CREATE TABLE public.samples (
  code BIGINT NOT NULL,
  barcode VARCHAR(40) NOT NULL,
  registry_date TIMESTAMP WITH TIME ZONE NOT NULL,
  CONSTRAINT samples_pkey PRIMARY KEY(code)
);

CREATE INDEX idx_samp_barcode ON public.samples (barcode);
CREATE INDEX idx_samp_barcode_code ON public.samples (barcode, code);
CREATE INDEX idx_samp_barcode_code_desc ON public.samples (barcode DESC, code DESC);
CREATE INDEX idx_test_identifier_desc ON public.samples (barcode DESC);

CREATE TABLE public.requests (
  code BIGINT NOT NULL,
  test_code INTEGER NOT NULL,
  sample_code BIGINT NOT NULL,
  CONSTRAINT requests_pkey PRIMARY KEY(code),
  CONSTRAINT "Requests_S_fk" FOREIGN KEY (sample_code)
    REFERENCES public.samples(code)
    ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE,
  CONSTRAINT "Requests_T_fk" FOREIGN KEY (test_code)
    REFERENCES public.tests(code)
    ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE
);

CREATE INDEX idx_req_test_code ON public.requests (test_code);
CREATE INDEX idx_req_test_code_desc ON public.requests (test_code DESC);
CREATE INDEX request_sample_code_index ON public.requests (sample_code);
CREATE INDEX requests_sample_code_desc_idx ON public.requests (sample_code DESC, code DESC);
CREATE INDEX requests_sample_code_idx ON public.requests (sample_code, code);

Each table has 1 million rows.
All rows in both tables are distinct.
If I order by either column alone, the execution time is ~ 30 ms.
There is no sample without request.
A sample can have many s.code for the smaes.barcode.

How can I improve the performance?

Problem

This is a more complex problem than is obvious on a quick glance. You are sorting by two columns, each from a different table, while you join on two other columns. This makes it impossible for Postgres to use the provided indexes and it has to default to (very) expensive sequential scans. Here is a related case on dba.SE:

Can spatial index help a “range - order by - limit” query

Indexes

You need two indexes for best performance, both of which are already present:

CREATE INDEX idx_samp_barcode_code ON public.samples (barcode, code);
CREATE INDEX requests_sample_code_idx ON public.requests (sample_code, code);

But the plain query you have can not make use of them, not even in pg 9.4.

Query

Postgres 8.4 is quite a hurdle to take. But I think I found a way with a recursive CTE:

WITH RECURSIVE cte AS (
   ( -- all parentheses are required
   SELECT r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
   FROM  (
      SELECT s.barcode
      FROM   samples s
      -- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)
      ORDER  BY s.barcode
      LIMIT  1  -- get smallest barcode to start with
      ) s0
   JOIN   samples  s USING (barcode)  -- join all samples with same barcode
   JOIN   requests r ON r.sample_code = s.code
   ORDER  BY r.code  -- start with ordered list
   )

   UNION ALL
   (
   SELECT r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
   FROM  (
      SELECT s.barcode
      FROM   cte c
      JOIN   samples s ON s.barcode > c.barcode
      -- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)
      ORDER  BY s.barcode
      LIMIT  1  -- get next higher barcode
      ) s0
   JOIN   samples  s USING (barcode)  -- join all samples with same barcode
   JOIN   requests r ON r.sample_code = s.code
   ORDER  BY r.code  -- again, ordered list
   )
   )
TABLE cte
LIMIT 21;

Tested and works for me in pg 9.4. It uses the indexes (partly in index-only scans in pg 9.2+).

pg 8.4 already has recursive CTEs. The rest is basic SQL (even TABLE is available in pg 8.4 (and can be replaced with SELECT * FROM). I hope there are no restrictions to spoil the party, I don't have an installation of pg 8.4 any more.

Explanation

The commented parts:

-- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)

would be needed if there could be samples with no requests at all, which you ruled out in a comment.

The query relies on this documented behavior of recursive CTEs:

  Tip: The recursive query evaluation algorithm produces its output in
  breadth-first search order.

Bold emphasis mine. And on this, too:

  This works because PostgreSQL's implementation evaluates only as many
  rows of a WITH query as are actually fetched by the parent query.
  Using this trick in production is not recommended, because other
  systems might work differently. Also, it usually won't work if you
  make the outer query sort the recursive query's results or join them
  to some other table.

Bold emphasis mine. So, it's only guaranteed to work in PostgreSQL and only if you do not add another ORDER BY in the outer query.

This is fast for a (relatively) small LIMIT in the outer query. For the case at hand, it should be very fast. The query would be no good for a large LIMIT.

PL/pgSQL function

The other option I can think of is a procedural solution with plpgsql. Should be a bit faster yet, especially for repeated calls:

CREATE OR REPLACE FUNCTION f_demo(_limit int)
  RETURNS TABLE (code int8, test_code int, sample_code int8
               , barcode varchar, registry_date timestamptz) AS
$func$
DECLARE
   _barcode text;
   _n       int;
   _rest    int := _limit;  -- init with _limit parameter
BEGIN
   FOR _barcode IN
      SELECT DISTINCT s.barcode
      FROM   samples s
      -- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)
      ORDER  BY s.barcode

   LOOP
      RETURN QUERY
      SELECT r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
      FROM   samples  s
      JOIN   requests r ON r.sample_code = s.code
      WHERE  s.barcode = _barcode
      ORDER  BY r.code
      LIMIT  _limit;

      GET DIAGNOSTICS _n = ROW_COUNT;
      _rest := _rest - _n;
      EXIT WHEN _rest < 1;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM f_demo(21);

MATERIALIZED VIEW

A large OFFSET has a similar effect as a large LIMIT. While all the skipped rows don't add to I/O, they still have to be computed to determine the first rows after the offset. If you need that, use a MATERIALIZED VIEW with an added row number and an index on that.

Postgres 9.3+ has a built-in feature, with your outdated software you have to knit the solution by hand. It's not that complicated, though. Basically a snapshot- table thats populated from a pre-defined SELECT statement or VIEW, like. Basically, create the table as:

CREATE TABLE req_sample AS
SELECT row_number() OVER (ORDER BY s.barcode, r.code) AS rn
     , r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
FROM   requests r
JOIN   samples s on (s.code = r.sample_code)
ORDER  by s.barcode, r.code;

ALTER TABLE req_sample ADD CONSTRAINT req_sample_rk PRIMARY KEY (code);
CREATE INDEX foo ON  req_sample (rn);

Save that SELECT as function, view or the plain query text. Refresh (expensive) in one transaction:

BEGIN;
-- drop PK & index
TRUNCATE req_sample;
INSERT INTO req_sample SELECT ... ;
-- add PK & index
COMMIT;

Redundant barcode column

The MV gets more expensive if underlying tables change a lot and you need "current" results. There's one more halfway cheap option: store the barcode in the requests table redundantly. You could include it as 2nd column in the FK constraint to samples ("Requests_S_fk") and make that ON UPDATE CASCADE to avoid outdated / conflicting data. Then you can work with an index on (barcode, code) in requests.

For the last two options you can then simplify pagination with directly applicable indexes with a WHERE clause on values from the previous page replacing the OFFSET. Consider this presentation about "Pagination done the PostgreSQL way" on use-the-index-luke.com.


Is this a good case for multiple SQL Server instances on a VM? We have a suite of applications utilizing SQL Server have the following characteristics:

Users create/destroy DBs via a UI, so DBs appear/disappear without DBA assistance.
DBs of the same name can be created for each of the applications (because they're related to the same subject matter and the apps are used by the same set of people)
DBs are many, but generally small.  For example ~70 DBs totaling 28 GB, ~130 DBs totaling 2 GB.
Only a handful of DBs are in use at any one time.

I've been asked about configuring a VM with three instances to support these three applications.  And, while DBAs generally run screaming when the question of multiple instances on a server comes up, I'm wondering whether this is a scenario where multiple instances would be, if not blessed, tolerated.

I do have large physical servers that I run multiple instances on.  But, not VMs.  So, I'm comfortable managing the multiple instances in this fashion although the idea of doing on a VM made me a bit queasy.

Thoughts?

I'd say it's all based on the resource consumption (cpu / ram / concurrent disk activity) of each instance, as well as the overall health of the virtualization environment. Virtualization is all about resource queues. If it looks like physical environment might hit a bottleneck based on the cumulative resource consumption of three instances in one VM, it's better to break that into three VMs. Smaller VMs are more agile and have less of an overall impact to the physical machine, which lends to better performance. Larger VMs give you less individual OSs to manage, but tend to be larger consumers of resources and therefore harder to maneuver in the virtual environment.

If the overall load looks reasonable, you might be ok with the three instances on a VM. If the overall load is high and growing, split them apart into three VMs to be safe now and for the future.


unable to connect to mongo in instance I am unable to connect to mongo its show the below error

Check that /var/log/mongodb/mongod.log, there is error message what tells what went wrong.

BTW, please don't insert screen captures. Just copy&paste lines and tag text as "code sample"


export data from postgresql I have some text like xml -code in a field of table. I want to export the data from my field into file without any changes.

I've tried to use a lot of variants but all of them changes the text. The most successful variants were:

COPY (SELECT alll FROM super.a_a ) TO 'D:\alll.txt' CSV ;

But there appeared extra quotes "".

COPY (SELECT alll FROM super.a_a ) TO 'D:\alll.txt CSV QUOTE ' ' ;

But there appeared extra spaces.

In my field I have such date like : name="firsname" and when I use any of varients I get something like that : name=""firstname"" or name=\n\r"firstname\n\r""

Maybe, you can just execute the query in psql and redirect its output to a file after removing all headers. Did you try something like:

$ psql
user=> \o outputfile.txt
user=> \t on
user=> SELECT alll FROM super.a_a;
user=> \q