MySQL set ID of primary child with constraint I have two MySQL tables where the primary relationship between the two is one-to-many. I also need a one-to-one relationship in the parent_table with the ID for the primary record from the child_table. You can kind of think of it as like a dad having lots of kids but having one kid that's his favorite ;P. Is it possible to setup a DB constraint such that a parent can have the ID of a child only if that child is a child of the parent?

parent_table (One)
  id: Primary Key
  primary_child_id: {ID from child table}

child_table (Many)
  id: Primary Key
  parent_table: {ID from parent table}

Circular foreign key

create table parent
(
    id int primary key,
    primary_child_id int
);

create table childs
(
    id int primary key,
    parent_id int references parent (id)
);

alter table parent
    add constraint fkPrimaryChild
    foreign key (primary_child_id)
    references childs (id)

First time you insert a new parent record, primary_child_id must be null.

insert into parent values (1, null);

insert into childs values (1, 1), (2, 1), (3, 1);

After you have inserted one or more child records, then you can assign primary_child_id.

update parent set primary_child_id = 1 where id = 1;

delete from childs where id = 1;

Cannot delete or update a parent row: a foreign key constraint fails
(`fiddle_JKZMCYWHMDENXXNQQOMG`.`parent`, CONSTRAINT `fkPrimaryChild`
FOREIGN KEY (`primary_child_id`) REFERENCES `childs` (`id`))

dbfiddle here


How to save database connection information when using `psql`? I am learning to use psql to work with a postgres database on linux.
I can connect using a long connection string, for example by running:
psql postgresql://myusername:mypassword@localhost:5433/dbname
However, I would like to save the details of my connection string somewhere, to simplify the command to connect to the database.
I've added the parameters to ~/.pgpass, and I've also changed the environment variables PGDATABSE, PGHOST, PGPORT, and PGUSER.  However, I'm not sure how to tell psql to source connection information from either ~/.pgpass or the environment variables.  When I just type psql I get an error, and I can tell from the details of the error that it is trying to connect using default parameters, like port 5432 instead of my specific port.
So how to I tell psql to use the parameters in ~/.pgpass or the environment variables?  Or more broadly, how can I save my connection information to simplify the command to connect in the future?

Thanks to the comment from @a_horse_with_no_name:
Enter the details in ~/.pg_service.conf, for example:
#comment
[myconnection]
host=localhost
port=5432
user=myuser
dbname=mydb
password=mypassword

Then set a PGSERVICE environment variable specifying which connection to use
echo PGSERVICE=myconnection

Then simply entering psql should make the connection.


Where are Statistics physically stored in SQL Server? Where are the Statistics used by the Query Optimizer physically stored inside a SQL Server database file and the Buffer Pool?

More specifically, is there a way to figure out the pages used by statistics using DMVs and/or DBCC?

I own both SQL Server 2008 Internals and SQL Server Internals and Troubleshooting books and none of them talks about the physical structure of the statistics; if they do I haven't be able to find this information.

Found them.

Create a table with a simple stats object.

CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE TABLE dbo.foo(bar INT, munge INT);
GO
CREATE STATISTICS x ON dbo.foo(bar);
CREATE STATISTICS y ON dbo.foo(munge);
GO
INSERT dbo.foo SELECT s1.[object_id], s2.[object_id]
  FROM sys.objects AS s1
  CROSS JOIN sys.objects AS s2;
GO
UPDATE STATISTICS dbo.foo;
GO

Connect using the DAC (ADMIN:Server[\instance]).
Run the following queries:

DBCC SHOW_STATISTICS('dbo.foo', 'x') WITH STATS_STREAM;
DBCC SHOW_STATISTICS('dbo.foo', 'y') WITH STATS_STREAM;

SELECT name, imageval
  FROM sys.stats AS s
  INNER JOIN sys.sysobjvalues AS o
  ON s.object_id = o.objid
  AND s.stats_id = o.subobjid
WHERE
  s.object_id = OBJECT_ID('dbo.foo');

You will note that imageval for each stats object isn't the same as the stats blob, but it does contain the stats blob - it's just offset. On my system it yielded this for x (I've obviously truncated a fair bit of bits):

0x0100...bunch of chars...000007000000C4E1BE00EEA0...rest the same
                            0x07000000C4E1BE00EEA0...rest the same

And this for y:

0x0100...bunch of chars...430007000000C7E1BE00EEA0...rest the same
                            0x07000000C7E1BE00EEA0...rest the same

The same was true for index-based statistics.

You could probably do further validation of this with a series of queries using DBCC commands. First, find out the pages that are involved with the clustered index on sys.sysobjvalues (substitute your database name):

DBCC IND('splunge', 'sys.sysobjvalues', 1);

The result will list a bunch of pages, you're interested in the ones of PageType = 1. With a new database, you should be able to find this info on one of the pages with the highest PagePID values. E.g. on my system this was page 281, so then I looked closer at that page:

DBCC TRACEON(3604);

DECLARE @dbid INT = DB_ID();

DBCC PAGE(@dbid, 1, 281, 3);

DBCC TRACEOFF(3604);

Sure enough, I found the data in slot 17:

(On larger databases, you might have to do a lot more hunting and pecking, since there's no guarantee that even a new stats object will end up on a new(er) page.)

Go ahead and try this at home, but there is a reason you need to connect with the DAC for this. I'd be curious to know, of course, what you are going to do with this information that you couldn't do with DBCC SHOW_STATISTICS output.

Note that this of course does not try to decode the STATS_STREAM to provide histogram or other information, and I could not find any evidence that the tabular output of DBCC SHOW_STATISTICS ... WITH HISTOGRAM is stored anywhere in table format. Joe Chang has some information about decoding if that's what you're after. I don't think it's something you'd want to do in a query - just use DBCC.


SA user password for software which automatically installs SQL Server 2005 I am testing a software which requires SQL Server 2005 for storing the data. The SQL Server 2005 database is automatically installed when I installed the software. During the software installation I didn't encounter any screen to configure the SQL Server 2005 database so I am assuming the configuration is done by the software installer.

I can reset the password for the sa but theoretically it would stop the application from working. Can anyone push me in a direction by which I can access the database with a client?

With SQL Server 2005, you have at least a couple of options

run sqlprov.exe as local admin which allows you to add domain
accounts to the sysadmin role
stop SQL Server and start in single user mode with the parameter -m
and use SQLCMD or OSQL to add your domain account to the sysadmin
role (EXEC sp_addsrvrolemember 'MyDatabase\MyNTUserName',
'sysadmin';). You can also create a new SQL Server login and add
it to the sysadmin role from the same SQLCMD prompt.


Show current row "win streak" I need to show the running win/loss streak per row in a query, so given the table below, the query should return the "expected" column. I've tried some approaches with window functions, but no success.

create table matches (player text, dt date,  is_winner boolean, expected integer )
insert into matches values
('A', '2019-01-01', TRUE, 0),
('A', '2019-01-03', TRUE, 1),
('A', '2019-01-04', TRUE, 2),
('A', '2019-01-09', FALSE, 0),
('A', '2019-01-10', FALSE, -1),
('A', '2019-01-15', TRUE, 0);

player  dt          is_winner   expected
A       2019-01-01  true        0
A       2019-01-03  true        1
A       2019-01-04  true        2
A       2019-01-09  false       0
A       2019-01-10  false       -1
A       2019-01-15  true        0

The logic is:

Resets to 0 when winning after a loss, or losing after a win.
Increments after a win, but not if it's case 1.
Decrements after a loss, but not if it's case 1.

Any insights on how to tackle this are welcome. My last resort would be a function with a loop called by every row.

I've done this in stages using CTEs so that you can see how it's done as the queries progress. Each CTE adds a column in the output in order to show you progress.

It's pretty much self-documenting with the CTE names, to be honest.

with lags as (
  select player,
         dt,
         is_winner,
         lag(is_winner) OVER (partition by player ORDER BY dt ASC) as prev_is_winner,
         expected
  from matches
),
     group_changes as (
       select lags.*,
              case
                when prev_is_winner <> is_winner or prev_is_winner is null
                  then 1
                else 0
                end as is_new_group
       from lags
     ),
     groups_numbered as (
       select *,
              sum(is_new_group)
                  over (partition by player order by dt, is_winner desc) as streak_group
       from group_changes
     ),
     expected_in_groups as (
       select groups_numbered.*,
              row_number()
                  over (partition by player,streak_group
                    order by dt asc, streak_group asc) - 1 as expected_unsigned
       from groups_numbered
     )
select expected_in_groups.*, case when is_winner = 't' then expected_unsigned else expected_unsigned * -1 end as actual
from expected_in_groups
order by player asc, dt asc;

DB Fiddle Link (I added an extra row just to make sure it was working at a certain point)

Basically:

lags CTE: use LAG() to get the previous result relative to the current row.
group_changes CTE: Detect whether the previous streak, whether win or loss, has ended
groups_numbered CTE: Give each streak a number
expected_in_groups CTE: Number the rows in the group
Final select: negate the loss streaks

.

+--------+------------+-----------+----------------+----------+--------------+--------------+-------------------+--------+
| player |     dt     | is_winner | prev_is_winner | expected | is_new_group | streak_group | expected_unsigned | actual |
+--------+------------+-----------+----------------+----------+--------------+--------------+-------------------+--------+
| A      | 2019-01-01 | t         |                |        0 |            1 |            1 |                 0 |      0 |
| A      | 2019-01-03 | t         | t              |        1 |            0 |            1 |                 1 |      1 |
| A      | 2019-01-04 | t         | t              |        2 |            0 |            1 |                 2 |      2 |
| A      | 2019-01-09 | f         | t              |        0 |            1 |            2 |                 0 |      0 |
| A      | 2019-01-10 | f         | f              |       -1 |            0 |            2 |                 1 |     -1 |
| A      | 2019-01-11 | f         | f              |       -2 |            0 |            2 |                 2 |     -2 |
| A      | 2019-01-15 | t         | f              |        0 |            1 |            3 |                 0 |      0 |
+--------+------------+-----------+----------------+----------+--------------+--------------+-------------------+--------+


Why is the database size larger than sum of table sizes I wanted to know why  database size is a way greater than thesum of table size in a database? How does it change?

why database size is a way greater than the sum of table size in a database?

Databases contain lots of other things besides "just" your data.
Indexes are the largest and most obvious.  Every Index contains its key values, possibly some other stuff as well, depending on the Index type, and a pointer to the corresponding  Data record.
Every Table, and every Row within every Table, has some overhead associated with it, over and above the actual Data that you or I care about.
Then there's all the stuff that the DBMS creates for itself, like statistics tables, entire schemas for metadata, "code" objects like procedures and functions, and many, many more.


Adding time to an event that runs on a weekly basis I have this event that runs every Thursday

CREATE EVENT le_drop_database
ON SCHEDULE
  EVERY 1 WEEK
STARTS CURRENT_DATE + INTERVAL 3 - WEEKDAY(CURRENT_DATE) DAY
 DO BEGIN
  drop database carbon;
    END */$$
DELIMITER ;

I was wondering how i would add some parameter that indicates the time this event should run on Thursday for example run at 11:00 a.m

There are three things you should do with this

Change DROP DATABASE to DROP DATABASE IF EXISTS
Add INTERVAL 11 HOUR to the START (as mentioned by @ypercube)
Add ON COMPLETION PRESERVE to make it a repeatable event so you don't have to create it again.

Here are the changes

DELIMITER $$
CREATE EVENT le_drop_database
ON SCHEDULE
  EVERY 1 WEEK
STARTS CURRENT_DATE + INTERVAL 3 - WEEKDAY(CURRENT_DATE) DAY + INTERVAL 11 HOUR DO
ON COMPLETION PRESERVE
BEGIN
  DROP DATABASE IF EXISTS carbon;
END $$
DELIMITER ;

I hope this event is not in the carbon database

If you ever need to disable the event, just run

ALTER EVENT le_drop_database DISABLE;

to reenable

ALTER EVENT le_drop_database ENABLE;

CAVEAT

Please make sure you have this in my.cnf

[mysqld]
event_scheduler=1

You can enable this without restarting mysql by running

mysql> SET GLOBAL event_scheduler = 1;

Give it a Try !!!


How to count up rows with duplicate id I want to count the number of rows with duplicate ids

My current code is

SET @x=1;

SELECT

c.id,
so.trans_id,
IF(c.id = c.id, @x + 1, @x) as count_t

FROM customer c
LEFT JOIN sales_order so

Current Result

+-----+----------+----------+
| id  | trans_id | count_t  |
+-----+----------+----------+
| 101 | 100001   |  2       |
| 101 | 100059   |  2       |
| 101 | 100061   |  2       |
| 102 | 100030   |  2       |
| 102 | 100035   |  2       |
| 102 | 100090   |  2       |
| 103 | 100005   |  2       |
| 103 | 100115   |  2       |
+-----+----------+----------+

Desired Result

+-----+----------+----------+
| id  | trans_id | count_t  |
+-----+----------+----------+
| 101 | 100001   |  1       |
| 101 | 100059   |  2       |
| 101 | 100061   |  3       |
| 102 | 100030   |  1       |
| 102 | 100035   |  2       |
| 102 | 100090   |  3       |
| 103 | 100005   |  1       |
| 103 | 100115   |  2       |
+-----+----------+----------+

SQL Version:

MySQL 5.6.32-78.1

UPDATE:

I added a nested query and a GROUP BY function to my code but it doesn't seem to count properly.

My NEW code is

SELECT

id,
time,
next_time,
(SELECT COUNT(1) FROM ecms.customer c1
LEFT JOIN ecms.sales_order so1 ON so1.customer_id = c1.id
WHERE c1.id = t.id AND so1.date_created <= t.time) as count_t

FROM(
SELECT

c.id,
so.date_created as time,
(
    SELECT
        MIN(so1.date_created)

    FROM ecms.customer c1
    LEFT JOIN ecms.sales_order so1 ON so1.customer_id = c1.id
    WHERE c1.id = c.id
    AND so1.date_created > so.date_created
    AND IF((TIME_TO_SEC(TIMEDIFF(so1.date_created,
    so.date_created))/360)>=60,1,0)=1
    ) as next_time

    FROM ecms.customer c
    LEFT JOIN ecms.sales_order so ON so.customer_id = c.id
    LEFT JOIN ecms.sales_order_item soi ON soi.sales_order_id = so.id
    LEFT JOIN ecms.product p ON p.id = soi.product_id
    LEFT JOIN ecms.history h ON h.transaction_id = so.transaction_id
    GROUP BY c.id, next_time
) as t

WHERE id = 7941

Sample Result would be

+-----+----------+-----------+----------+
| id   | time    | next_time | count_t  |
+-----+----------+-----------+----------+
| 7941 | 100001  | 100061    |  1       |
| 7941 | 100061  | 100121    |  4       |
| 7941 | 100121  | 100181    |  7       |
| 7941 | 100181  | 100241    |  8       |
| 7941 | 100241  | 100301    |  16      |
| 7941 | 100301  | 100361    |  21      |
| 7941 | 100361  | 100421    |  45      |
| 7941 | 100421  | NULL      |  69      |
+------+---------+-----------+----------+

Sample Result Desired

+-----+----------+-----------+----------+
| id   | time    | next_time | count_t  |
+-----+----------+-----------+----------+
| 7941 | 100001  | 100061    |  1       |
| 7941 | 100061  | 100121    |  2       |
| 7941 | 100121  | 100181    |  3       |
| 7941 | 100181  | 100241    |  4       |
| 7941 | 100241  | 100301    |  5       |
| 7941 | 100301  | 100361    |  6       |
| 7941 | 100361  | 100421    |  7       |
| 7941 | 100421  | NULL      |  8       |
+------+---------+-----------+----------+

Try something like

SELECT @cnt:=CASE WHEN c.id=@id THEN @cnt+1 ELSE 1 END count_t,
       @id:=c.id id,
       so.trans_id trans_id
FROM (SELECT @id:=0, @cnt:=0) init_vars,
     customer c LEFT JOIN sales_order so ON ...
ORDER BY c.id, so.trans_id;

fiddle


SQL Server 2012 Very Slow to Consume Memory I have a bit of a conundrum.  I recently upgraded a ~20gb database from Window Server 2008 R2 || SQL Server 2008 R2 to Window Server 2012 || SQL Server 2012. The previous server had 32gb of memory, upon booting the Max Server memory of 28gb would be consumed within one day.  The new server has 48gb of memory however upon booting the database

does not take the minimum memory of 4gb
after a week of running has only consumed 7gb of data

The database is mainly ad-hoc workload that does not utilize stored procs. So the hope in adding memory that was more of query plans would stay cached.

Any idea what could be causing this ?

Edit

dm_os_bufferpool_descriptors:

My first question where are you looking for SQL Server memory consumption. Please don't use task manager or process monitor, these do not show correct value when SQL Server service account has Locked pages in memory privilege.  just use below query to see memory consumed by SQL Server

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

There was notable change in SQL Server memory code in SQL Server 2012 but no where its related to what you are seeing. SQL Server memory allocation is dynamic in nature when SQL Server boots up it would grow its memory notification till the highMemoryResourceNotification resource notification is revoked.SQL Server will keep allocating memory based on its need as long as there is memory available I.e. as long as MEMPHYSICAL_HIGH (HighMemoryResourceNotification )notification is signaled in widows  and will scale down its usage when there is MEMPHYSICAL_LOW (LowMemoryResourceNotification) signaled in windows. When available memory is between the low memory and high memory SQL Server will try to maintain the memory usage stable( RESOURCE_MEM_STEADY) with some exceptions.

With 48G memory you have set max server memory to 44G and only given 4 G to OS. IMO this is not correct you must give at least 6 G OS. You should give enough memory to OS so that it would have enough to allocate otherwise it would start signaling low memory notifications and SQL Server would have to trim its memory consumption.

Does SQL Server service account has Locked pages in memory privilege. If not you should give it, but before doing that please leave 6-7 G for OS( I am assuming here that this is dedicated system for SQL Server)

If you want SQL server to consume more memory create a dummy table and then start inserting large number of records in it at same time start running multiple Select * statements you would see SQL Server memory consumption rising. If you want to check SQL Server memory consumed by SQL Server use query posted above


Using count in triggers to update another table Say i have a table called trx_request with the following data
id | confirmed(TIME) | finished(TIME)
-------------------------------------
1  | 12:05:00        | NULL
2  | 13:11:00        | NULL

and another table called trx_updates with the following data
request(FK) | role(INT) | added(TIME)
------------------------------------------
1           | 1         | 14:02:00

I'd like to make a trigger that after a record is inserted into trx_updates, counts how many records has the same value in the request field (such as how many '1's there are). And then, does a check to see if the count reaches 4 ('1' shows up 4 times). When it does, it updates the trx_requests.finished to NOW().
How would one make a count statement in triggers? or are triggers the way to go?

This trigger does the job:

create trigger trx_updates_atrig
after insert on trx_updates for each row begin
DECLARE updatecount INT;
  set updatecount = ( select count(*) from trx_updates where request = new.request );
  if updatecount=4
    then
      update trx_request set finished=NOW() where id = new.request;
  end if;
end//

SQLFiddle Demo.


MySQL ADD KEY meaning This code below is creating a primary key on table User, but Im not understanding the second ADD KEY (C_NATIONKEY), do you know what is doing? Its something about foreign keys?

ALTER TABLE USER
ADD PRIMARY KEY (C_USRKEY),
ADD KEY (C_NATIONKEY);

As per MySql documentation, KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.
 In your case ADD KEY will explicitly define index on that field.


How is SSDT Data Flow batching handled? How does SSDT handle batching of Data Flow tasks?

I am parsing a CSV that is about 1GB into SQL Server using SSDT.

In the Data Flow there is a Flat File source, that goes to an OLE DB destination (a staging table). Then an SP is executed using a SQL Task.

The CSV being parsed contains a summary table and a child table with a foreign key reference to the summary table. As such the CSV contains duplicated IDs of the summary table (so there is one line per child row). If a single summary ID were to be split across two batches, then I would lose data - the SP does something like "delete from child table where ID in staging, then re-insert from staging into child table". Previously we had to do this because the vendor wasn't exporting a unique ID for the child data. They are now, so I can use a merge statement.

But. I would still like to know if Data Flow tasks are batched, and if so, how?

As mentioned in the comment under the question, an answer to this question is included this post: http://blogs.lobsterpot.com.au/2011/02/17/the-ssis-tuning-tip-that-everyone-misses/

That is, that batching is done by default (10k rows per batch).


Save schedules (working hours) I have an app to make appointments with doctors. Doctors have schedules (working hours).

For example, let's say doctor A usually works 10:00 - 14:00 on weekdays. I could save this info in each doctor's table; however, there are holidays, sick days and so on- maybe even short days 11:00 - 13:00. How to represent them in the PostgreSQL database?

My current approach is to create a schedules table and store the usual working hours. For doctor A on weekdays from 10:00 - 14:00, and for other days create separate tables in which we specify when the doctor cannot work.

save the unavailable hours as a special type of appointment


Are physical backups sufficient in PostgreSQL? Should I make logical backupds too from time to time or are physical backups sufficient for recovery in case of emergency in PostgreSQL? What are the advantages of logical backups?

I have heard somewhere that physical backups are not reliable enough without logical ones. But do not know why.

Physical backups can only be restored onto the same version of PostgreSQL running on the same OS and type of hardware.  If you are running on obscure hardware or operating system, or ancient versions of PostgreSQL, this can be a problem.  If you are not doing those things, it should not be a problem.


Querying 'random' Oracle data via openquery in SQL Server returning the same data for multiple runs I'm asked to help a developer figure out why the "random" data we should be getting from Oracle is exactly the same results each time you run it, rather than being actually random.
SQL 2012, Oracle 11g, linked server is using OLE DB connector type.

When they run the following query directly in the Oracle DB, they get proper "random" results. Through the linked server in SQL, they'll always get the same rows (and I've verified this by running the linked server query myself).

select * from openquery(ORA_DB, 'select XX_NUMBER, XX_CODE, UNIT, XX_serial, XX_date, XX_area, building, location, XX_status, owner_code, qa_XX_no
                               from qa_eligible_XX
                               where qa_XX_no = 4
                               and owner_code <> ''G''
                               and last_trans_code = ''OL''
                               and XX_cycle = ''90 - 120 Periodic''
                               and XX_date < sysdate - 90
                               and rownum <= 200
                               order by dbms_random.value')

Note: I'm not an Oracle DBA but I work with one; he didn't see an issue with the Oracle part of the above query. Also, I edited a lot of stuff out and replaced with XX for privacy.

Each time we run this query in SSMS, we get the same 200 rows. I even connected from another SQL server that has the same linked server and ran this query, and got the same 200 rows (the same Oracle user is used for both connections). This leads me to believe Oracle may be caching the results; but somehow it's not doing this when the query's run in Oracle directly. Is there a way to force it to return new results through openquery? (so that the 200 rows it returns are a truly random sampling each time)

In the query you have it appears as though you are grabbing the first two hundred rows, and then sorting those by dbms_random.value.

This would be due to Oracle's query precedence which performs the query and then sorts the results. You would want to randomly sort the results first and then return the first two hundred rows. Using a subquery should provide you with the data that you want.

select * from openquery(ORA_DB, 'select * from
                                (
                                   select XX_NUMBER, XX_CODE, UNIT, XX_serial, XX_date, XX_area, building, location, XX_status, owner_code, qa_XX_no
                                   from qa_eligible_XX
                                   where qa_XX_no = 4
                                   and owner_code <> ''G''
                                   and last_trans_code = ''OL''
                                   and XX_cycle = ''90 - 120 Periodic''
                                   and XX_date < sysdate - 90
                                   order by dbms_random.value
                               )
                               where rownum <= 200 ')


Count the total occurences for each day in given dates I have this table and don't enough SQL to make a query to achieve the result shown in the image below.

CREATE TABLE myDates (
    id integer NOT NULL,
    startDate date NOT NULL,
    endDate date NOT NULL
);

insert into myDates(id, startDate, endDate)
values
(1, '2017-12-26', '2017-12-29'),
(2, '2017-12-29', '2017-12-29'),
(3, '2017-12-14', '2017-12-29'),
(4, '2017-12-18', '2017-12-21'),
(5, '2017-12-26', '2017-12-29'),
(6, '2017-12-28', '2017-12-29'),
(7, '2017-12-26', '2017-12-29'),
(8, '2017-12-25', '2017-12-27')

Look at all days between 'startDate' and 'endDate' and count the total of occurrences per day:

Expected result

I just added this image from an Excel File to illustrate better what I am trying to achieve. I am moving from excel to sql. Ignore weekends and 22/12 and 25/12.

Regrads,
Elio Fernandes

Here's a set-based approach that uses an Common Table Expression (CTE) to generate a quick Date Dimension table and then we use the CTE to group by date

The answer is the same as @nates

Declare @myDates TABLE  (
    id integer NOT NULL,
    startDate date NOT NULL,
    endDate date NOT NULL
);

insert into @myDates(id, startDate, endDate)
values
(1, '2017-12-26', '2017-12-29'),
(2, '2017-12-29', '2017-12-29'),
(3, '2017-12-14', '2017-12-29'),
(4, '2017-12-18', '2017-12-21'),
(5, '2017-12-26', '2017-12-29'),
(6, '2017-12-28', '2017-12-29'),
(7, '2017-12-26', '2017-12-29'),
(8, '2017-12-25', '2017-12-27')
;

WITH dates ([Date])
AS (
    SELECT convert(DATE, '2017-01-01') AS [Date] -- Put the start date here

    UNION ALL

    SELECT dateadd(day, 1, [Date])
    FROM dates
    WHERE [Date] <= '2020-12-31' -- Put the end date here
    )
SELECT [Date]
    ,count(*)
FROM dates d
JOIN @myDates m ON d.[Date] >= m.startDate
    AND d.[Date] <= m.endDate
GROUP BY [Date]
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!


What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table? We're using PostgreSQL v8.2.3.  Ours is a web-based application and we're using pgpool-II v 2.0.1 purely for connection pooling (we
don't use other features of pgpool like Replication, Load Balancing, etc.).

Recently, in our Production server there was a drastic and unexpected growth in
database disk space.  In just 2 days, database size has grown from 6 GB to
14 GB.

I then ran the following query to find the size of the top 20 biggest
relation in the database:

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class
C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN
('pg_catalog') ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;

I didn't find any issues here.  Even I could say that the sum of
"total_size" of the above command is less than the size occupied by the
database itself.  I'm using the following command to find the size of
database:

select oid, datname, pg_database_size(datname) as actualsize,
pg_size_pretty(pg_database_size(datname)) as size from pg_database order by
datname;

I also used to physically check the database size occupied using the following
command:

du -sh /usr/local/pgsql/data/base/2663326

I then physically listed the file size in descending order from the location
"/usr/local/pgsql/data/base/2663326".  Here, "2663326" is the OID of my
database.

[root@dbserver 2663326]# ll -lhS |head -15
total 14G
-rw-------  1 postgres postgres  1.0G Sep  6 15:03 1508904
-rw-------  1 postgres postgres  1.0G Sep  2 21:16 1924478.10
-rw-------  1 postgres postgres  1.0G Sep  2 21:17 1924478.2
-rw-------  1 postgres postgres  1.0G Sep  2 21:19 1924478.3
-rw-------  1 postgres postgres  1.0G Sep  2 21:17 1924478.4
-rw-------  1 postgres postgres  1.0G Sep  2 21:18 1924478.5
-rw-------  1 postgres postgres  1.0G Sep  2 21:20 1924478.6
-rw-------  1 postgres postgres  1.0G Sep  2 21:20 1924478.7
-rw-------  1 postgres postgres  1.0G Sep  2 21:14 1924478.8
-rw-------  1 postgres postgres  1.0G Sep  2 21:19 1924478.9
-rw-------  1 postgres postgres  876M Sep  6 15:02 1508614
-rw-------  1 postgres postgres  615M Sep  6 15:03 1508904.1
-rw-------  1 postgres postgres  531M Sep  2 21:20 1924478.11
-rw-------  1 postgres postgres  235M Sep  6 15:02 1510463

Though these files are not human-readable, from whatever I was able to read
from the file, I found that the top 10 files created are related to a
particular complex application report.   In this complex report, we're
creating temporary table using CREATE TEMP TABLE MY_TEMP_TABLE(col1, col2,
...), 5 columns in this temp table are indexed and it's being heavily used.
Though temporary tables are automatically dropped at the end of a session,
I'm finding that the disk space occupied by these temporary tables are not
being freed-up.  As you can also see, some file names are numbered with
decimal places (1924478.2, 1924478.3, etc.) with a maximum file size of 1
GB.  Particularly, these type of files are related to this complex report
that makes use of temporary tables.

I can also confirm that my temporary tables are not getting listed from the
following query (which shows that as per system catalog tables, it's been dropped):

select pn.nspname, pc.relname, pc.relfilenode from pg_class pc, pg_namespace
pn where pc.relnamespace = pn.oid and pc.relname ilike 'my_temp_table';

NOTE: Auto vacuum daemon is already running in the server.  Even a manual
VACUUM FULL ANALYZE, followed by REINDEX command is not able to reclaim the
lost disk space.  Only when we exported and imported, the database size
comes back to the original 6 GB size.

So, based on my observations, it appears that at some point of time/context, for some unknown reasons, the disk
space occupied by the temporary table is not being released properly by PostgreSQL server.

What are all the reasons/possibilities that it may fail to free-up disk space
occupied by TEMPORARY table?   How do I fix/handle in this situation?

In more recent versions of Postgres (I think since 8.3) you can assign a special tablespace for temporary tables which might help you. This is documented here:

http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-TEMP-TABLESPACES

Given the fact that 8.2 will be de-supported at the end of the year it might be a good idea to upgrade. There have been numerous enhancements to VACUUM and the handling of temporary files since 8.2 so you might benefit from those.

Edit:
The reason why I think this (separate tablespace) could help you, is that you can simply drop and recreate the tablespace (files) to reclaim the spaces occupied.

But then I'd assume that due to all the improvements that were implemented in the last 5 years the current version might release the space without any further action from your side (especially because VACUUM FULL has been completely rewritten in 9.0)


Getting unique names when the IDs are different (DISTINCT ?) I've got this table:

id  | name
1   | John
2   | John
3   | Peter
4   | Mike
5   | Mike

And I want to get the unique names, getting the latest one in the list. I could go for:

SELECT DISTINCT name FROM table ORDER BY id DESC LIMIT 2

This would get me: Mike and Peter. All good. However, I also need the IDs, so then DISTINCT does not work anymore. What would be the right query to get:

5   |   Mike
3   |   Peter

In words: the latest 2 entries that are unique

Here is the query

SELECT id,name FROM
(SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A
ORDER BY id DESC LIMIT 2;

Here is some sample data

mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO mytable (name) VALUES
    -> ('John'),('John'),('Peter'),('Mike'),('Mike');
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | John  |
|  3 | Peter |
|  4 | Mike  |
|  5 | Mike  |
+----+-------+
5 rows in set (0.00 sec)

mysql> SELECT id,name FROM
    -> (SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A
    -> ORDER BY id DESC LIMIT 2;
+------+-------+
| id   | name  |
+------+-------+
|    5 | Mike  |
|    3 | Peter |
+------+-------+
2 rows in set (0.00 sec)

mysql>


Besides attaching the debugger, is there any other differences between "(local)\Instance" and "MyLocalName\Instance"? When using SQL Server Management Studio for SQL Server 2008 R2 connecting to a local SQL Server 2008 R2 instance (version 10.50.2500), I know that if I am using the instance name (local)\sql2008 it will fail to attach the debugger to any queries I attempt to debug with the error message

  Unable to start T-SQL Debugging. Could not connect to computer '(local)'.  The requested name is valid, but no
  data of the requested type was found.

However, If I change that connection to src\sql2008 the debugger runs happily.

Are there any other differences in having your connection use the (local) alias other than the debugger issue I pointed out above?

(local) will work with any programs that understand to change (local) or . or localhost into the machine's network location. I would imagine that is the issue with the debugger. But as long as the program you are using (i.e. SSMS) understands that translation, there is no behavior difference to worry about.

I would imagine for most production servers, most connections will not be happening from a local machine as you would have a separate database server for security, availability and performance. So for that reason, it may be better to refer to the server as you will from other machines, but most of the time when working locally I just connect to . or .\instanceName since I hate typing ;-)


Power went out - Did query finish? Is there a way to check and see if a query finished?  I ran 3 very long running update queries (+/- 25 hours each) as I went out the door for vacation last week.  Unfortunately, somewhere during the week the power went out and the machine running MYSQL shut down. Is there a way to check and see which of the 3 (or all three) queries were completed?

I know I could check to see if the data had been updated, but NULLs are to be expected with a correct and complete execution and there's 48 million rows of data to peruse.  Any thoughts?

If you are running with binary logs enabled this can be checked with relatively high reliability.

First, to see whether binary logs are indeed enabled, run:

SHOW BINARY LOGS;

If they're enabled, you should get an output like this:

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000244 |  15462544 |
| mysql-bin.000245 | 102622775 |
+------------------+-----------+

Otherwise you would get an error message.

Now, if binary logs are enabled, then any successful commit is also written to binary logs. I say "commit", but the truth is any successful operation, even on non transactional tables such as MyISAM, is written there. But, to be honest, to have any certainty about the outcome of your queries, I do hope for your sake you're using a transactional engine such as InnoDB, or else you cannot be sure of anything.

OK, so asssuming now that you have binary logs turned on, and that your tables are transactional (hopefully InnoDB), then a successful completion of your queries is expected to be written to the binary logs.

You must now hunt down the relevant binary log and look for the query there. If you find the query - good! If not - it's most probably not there. I'll explain shortly.

Which binary log contains your query? Look at the binary log files themselves, typically in your data directory. Look for their timestamps. When the power came up, a new binary log was created. Find it. Your queries are most probably in the binary log one before that one. This is a guess. It could also be one before that, etc. But it's a good guess.

Now, using the mysqlbinlog utility, execute, from command line, something like this:

mysqlbinlog mysql-bin.000245

Replace file name with the one you suspect to contain the query.

This will output all queries in this binary log file into standard output. On Unix, use grep to find your query:

mysqlbinlog mysql-bin.000245 | grep "something which identifies the query"

On Windows, good luck. Open with notepad++ or something and search manually.

Is the query there? Great - you know it has been committed.

Is the query not there? Need to check on sync_binlog param. Is it 1? Then query not in binary log ==> query not committed.
But if sync_binlog is not 1, there could still be a chance that the query has been committed yet not in the binary log, as the crash may have happened just after the commit and just before the binary log was flushed to disk. You then need to revert to other means.

Those being: (and hopefully, again, you're using InnoDB): look for a single row that can identify outcome of the query. With InnoDB you get "all or nothing". If you can be certain of a single row affected by the query - you can be sure that the query has completed.

edit: of course, if slow log is enabled, you can expect such long query to be logged there after completion as well...

Good luck!


MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases I'm running into a problem when I create a large number of databases consecutively on an Amazon RDS db.m1.large instance. The MySQL Engine Version is 5.5.23. At some point, the RDS instance drops all connections and refuses any new ones for a period of about 10-20 minutes.

The database engine is InnoDB. Each database is exactly the same and is created from a MySQL Dump file. There are 65 tables, each is empty aside from one or two tables that have a row or two, so the data size is very small. Foreign key constraints are turned off before the tables are created and turned back on afterwards for each database created.

The following pseudo code essentially describes what I am doing:

For # of databases: // around 1000-5000 usually results in a crash
   CREATE DATABASE foo_num CHARACTER SET utf8 COLLATE utf8_bin; // done via Python Django connection
   mysql -u USER -h HOST --password=PASSWORD DBNAME < dbcreate.sql

The dbcreate.sql looks similar to:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `FooTable1`;
CREATE TABLE `FooTable1` (
   ...
)
...
DROP TABLE IF EXISTS `FooTable65`;
CREATE TABLE `FooTable65` (
   ...
)
SET FOREIGN_KEY_CHECKS=1;

Here is a snapshot showing the connections dropping.

I noticed, however, that the CPU is not pinning or anything before, during or after the connections drop:

However, I did notice the read throughput spikes during the connection drop:

Update 1

Okay, so as Rolando suggested, it may be that RAM is the limitation. The instance I am using has ~7GB of RAM available and as I was looking through the monitoring graphs I noticed the freeable memory metric was around the 7GB mark at the same time the connections were dropped. See the attached chart:

The question is, why is there this sudden spike in RAM usage and can it be avoided without having to scale up the hardware?

The first thing that comes to mind is the Server Model : db.m1.large
What limiting factors are placed on a MySQL RDS?
If you spin up an Amazon RDS instance of MySQL, you would subject yourself to whatever  constraints are given. All models of MySQL Amazon RDS have the same major options but differ in only two aspects

max_connections
innodb_buffer_pool_size

Here is a Chart I posted Last Month
MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

I posted this in my other posts in the DBA StackExchange

Local database vs Amazon RDS
Scaling Percona datacenters: setup and replication

You are using m1.large. Since the InnoDB Buffer Pool is 3/4 of the Instance. That's means you have 7.2G but only 1.8 is usable. That model is capable of having up to 623 connections. Each connection can consume memory because of

join buffers
sort buffers
read buffers
thread info

Amazon RDS is simply micromanaging resources. Since DB Connections can consume RAM, connections are probably being disallowed due to the lack of RAM needed.
SUGGESTIONS

Try reducing InnoDB log IO innodb_flush_log_at_trx_commit=0 during the mass creation
Make sure you are not doing large transactions during any automatic backups or snapshots
Try a bigger server model m1.xlarge or m2.xlarge


InnoDB AUTO_INCREMENT resetting itself without MySQL restart We've recently noticed the following scenario happening in many of our databases:

A record is inserted, and receives an AUTO_INCREMENT primary key.
The record is deleted.
Another record is inserted, and receives the same AUTO_INCREMENT id as the deleted one.

I'm aware that this will happen for InnoDB tables (which is what these are) if MySQL restarts between setps 2 and 3, but it is not restarting. Is there any other event that will cause the AUTO_INCREMENT counter to be re-initialised? The time period between 2 and 3 can be a few minutes or up to a month.

Over the past month we've had some performance issues that we have so far resolved my increasing table_open_cache. The server has up to 8000 databases in use at any one time, and we found that we had a lot of processes in the "Opening tables" and "Closing tables" states. Could this be related?

Edit in response to Rick James' comment regarding FLUSH TABLES

The MySQL documentation (under "InnoDB AUTO_INCREMENT Counter Initialization") states that:

  If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

This is the counter that is initialised to MAX(id) + 1 on restart. However, the glossary entry for the data dictionary states that:

  This metadata is physically located in the InnoDB system tablespace.

This in turn links to the glossary entry for the system tablespace, which states that it is stored in "One or more data files (ibdata files)". This is contradictory but  I'm inclined to believe the InnoDB AUTO_INCREMENT documentation. If the counter were stored on disk in the system tablespace, there would be no need to initialise it with MAX(id) + 1.

If this is the case, then FLUSH TABLES would have no effect, but if the counter is persisted to the system tablespace then it should work. Does anybody know which part of the documentation is correct?

This can be one of known bugs: https://bugs.mysql.com/bug.php?id=77743 (fixed in 5.6.27) or https://bugs.mysql.com/bug.php?id=76872 (still not fixed)


Mysql SHOW OPEN TABLES shows In_use but nothing in SHOW PROCESSLIST I'm looking for advice for a MySQL database used for my development. I have a table is locked, although no process is active.

Here's some context: I am a developer on this MySQL development database without administration rights. All tables are on MyISAM. For the second time in two days I found that all processes accessing a specific table were waiting for a lock. I'm running those 30 concurrent processes on the applicative tier. Those processes are doing steady updates on this table (probably adding like 40 rows per second to this table). This table has a primary key, and one additional index. It has 6 columns including 2 with a blob. At the moment, the table contains 23.7 million rows, and is roughly 51GB in size.

When I found my 30 processes were all stuck due to this lock on this table, I killed them all first. Then I killed the still opened sessions by doing a SHOW PROCESSLIST and issuing KILL for all sessions. The problem is that even after killing those sessions,  the table in question still shows for SHOW OPEN TABLE a IN_USE value of 2, although SHOW PROCESSLIST is empty.

I can't figure out a reasonable reason why this would happen. There's no process to kill anymore now. I can't repair the table as the REPAIR TABLE waits for the lock. Yesterday night I had the issue, and this morning the locks were gone like magic. Now it's reoccuring again.

Does anybody have a clue on why this could be happening? Any hint for me or my DBA on the right approach to recover and avoid further occurrences of the problem?

EDIT: Strangely, by the time I wrote this, the IN_USE for this table decreased from 2 to 1. And for another table in the same database, moved from 0 to 1. As I'm not doing anything that could have explained the latter increase, I suspect MySQL might be doing something special under the hood.

Speculating, since the tables are in MyISAM, perhaps there is a corruption in the table(s). Since it is locking when checking with the server online, try using myisamchk on the stuck table(s) (and perhaps all the tables) after shutting down the server if possible. 51GB is a lot of data so it may take a while. If possible, suggest following commenter's recommendation to convert the tables to InnoDB; adjust server configuration accordingly.

Ref: http://dev.mysql.com/doc/en/myisamchk.html


postgres materialized views and indexes to improve aggregate performance I have a large table with a number of materialized views used for pre-calculating aggregates. New data is inserted only once a day so think of this as a data warehouse type system.

Obviously when I query my main (non-aggregated) table, the planner uses any indexes it deems appropriate. Does that extend to materialized views? If I query the main table and the query involves aggregates, does it recognize that my materialized view already has the values it needs?

No. How would the query processor recognize that it should use the materialized view.  The materialized view is another object.

See  http://www.postgresql.org/docs/9.4/static/rules-materializedviews.html

In part that explains: "When a materialized view is referenced in a query, the data is returned directly from the materialized view."

So materialized views are useful, but you need to program not only for their maintenance, but also doing the necessary coding to use the materialized view.


Postgres bypassing index with IN clause with 100+ values? I came across this on another forum:

  PG has a limit of 100 for values in IN queries after which the index on said column is not used. For ex: a typical SELECT ... WHERE IN (...) query on the PK will be turned into a full table scan if the IN list exceeds 100.

I wasn't able to find anything about this.  Does PG have such a limit (I imagine so), and if so, what is the limit?

I know there are times where having a large subselect would be better used in a temp table, but it would be helpful to have knowledge of where the cutoff is.

I wouldn't know of any hard-wired limit in the query planner. And a quick test proves it wrong - for both variants of the IN construct, the one taking a set and the one taking a list:

db<>fiddle here

See the index scans for 103 elements?

Related:

Optimizing a Postgres query with a large IN

There is a related limit: a VARIADIC function takes a maximum of 100 individual parameters. Beyond that one needs to pass an array ...

Efficiently return two aggregated arrays from a m:n table

The only cases where a temporary table regularly improves performance is when you create useful indexes on the intermediary result before you proceed. Else, temp tables are typically (much) slower.


How to design a table for reconciliation A Bank B sends a set of transactions to Bank C every few minutes. Same transactions could be sent multiple times (Bank C should recognize those transactions and should not process them).

At the end of the day, Bank B sends a reconciliation file (that SHOULD contain all transactions that SHOULD BE processed during the day) to Bank C.

Reconciliation process should provide a report with missing information for set of transaction during the day comparing with the set at the end of the day. Beside that, if any missing transaction is found, it should be processed.

Should the system use one table for transactions and another one for reconciliation or one single table for both information since the structure of data is the same?

It depends on amount of data and how it will be used. You can have one table for with flag for intraday/COB datasets, then queries that would filter on those flags. After that you'll probably use EXCEPT to get unprocessed transactions. Then you need to keep this data for  2-5-7 years. There may be more requirements. It is OK if you have enough capacity and the only use for this table is one-off reconciliation and then storing data.

However, if you need to run reports on COB data, join it to dimensions, do more reconciliation, enrich with more attributes, send to downstream systems, then you would go for a separate table.


Database Shrink task fails I created a maintenance plan a week ago and it's been executing fine every night. Now the Database Shrink task has stopped working and throws this error:

  Property Size is not available for Database '[foo]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

Yesterday I installed a couple of SQL Server hotfixes offered by Windows Update (4411 and 4457, if that matters). It's the only change I've possibly made.

My task looks like this:

Connection: Local server connection (sa)
Database(s): All databases All user databases
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink (10%)
Return freed space to operating system

Some of my findings:

If I generate the T-SQL code and execute it manually it runs flawlessly.
If I create a new maintenance plan with an identical Database Shrink task it runs flawlessly.

What can the problem be?

As Shanky already commented, running scheduled shrinks is wrong. There is ton of info online about it. Find the root cause of that growth and solve it. I would get rid of it immediately.

As for the error, seems related to db ownership. If you run a search with the error text your will find several related questions here, here and more.

As a recommendation, I would suggest switching to Ola Hallengren maintenance solution for administrative tasks as DBCC, index maintenance, backups, etc.


Enforcing that a user should have one or more email addresses I'm trying to design an application where a user can authenticate oneself with one password and one or more email addresses. I'm not sure how to enforce this constraint.

The tables so far are simple:

Table users:

id | password |
--------------------------------------

Table email_addresses:

user | email |
--------------------------------------

However, in the current situation, a user can also have zero email addresses, thereby preventing the user from authenticating oneself.

What's the best way to deal with this type of constraint?

Add a column to the users table; email_primary.  Prevent the new column from accepting NULL values.

When you want to authenticate a user, simply use a UNION query, similar to:

SELECT id, email_primary
FROM users
UNION ALL
SELECT user, email
FROM email_addresses;


SSMS Linked Server -ORA-28009: connection as SYS should be as SYSDBA or SYSOPER (Microsoft SQL Server, Error: 7303) How could I use "Linked Server" from SSMS IDE to connect the Oracle server database with "SYSDBA"?

I've tried many methods as following to show you what I did right now:
Oracle client connect to Oracle Server data:

SSMS connect to Oracle Server data:

More testing information
I also use "SQLPLUS" to check tnsnames.ora the file is okay or not but the answer is as following

sqlplus SYS/Oradoc_db1@DBNAME as SYSDBA <-- is okay
sqlplus SYS/Oradoc_db1@DBNAME <-- Error

I found the problem and you can follow my steps to fix this issue.
1. Don't use SYS to be your USERID
2. Add new USERID

-- CREATE USER SQL
1. CREATE USER a
2. IDENTIFIED BY a
3. DEFAULT TABLESPACE "USERS"
4. TEMPORARY TABLESPACE "TEMP";
*** <-- if you got the error, please follow above content to fix problem
 (SQL> alter session set "_ORACLE_SCRIPT"=true;  )
-- ROLES
GRANT DBA TO a;


Is there any reason to maintain audit columns when the target table already has an audit table? I'm currently designing a database schema for a small microservice which handles products.
The service contains a simple REST API which allows users to perform basic management of products and also some end points to query the full audit history about products. Schema already contains a snapshot-based audit table for products to support storing the full audit trail per product.
In addition of having separate audit history end points, each product specific REST API response must also include the createdAt, createdBy, updatedAt, updatedBy fields. The thing I'm wondering here is whether I should add these fields into products table or is it better to query information to those fields directly from the product audit table? Somehow I feel like adding these fields into the products table would seem a bit overkill when the same data (and even more) could be queried from the audit table.
Edit
Each CREATE, UPDATE and DELETE operation on the product table will also append a new entry into the audit table. This audit table contains the same columns than the actual product table plus additional columns for the timestamp, user ID and the type (ADD, MOD, DEL) of the audit event. The frequency of the management operations is quite low.

If you are going to follow the pattern of inserting into the audit table after each insert, no there is no need to retain the creation/alter timestamps as they are present in the audit table.
However, if the pattern is to only insert into the audit table after an update/delete (this is more common), then you will need to retain any relevant timestamps/user info with the data so the original data can be copied into the audit table.
Generally, unless there is a very specific use case, it isn't necessary to store the created at/created by information as this is easily derived from the audit information itself.


Tablespace utilization in db2 (percentage of tablespaces) how much free space is there…in each tablespace, How to calculate…

i need tablespace utilization in MB's and...how can i get tablespace percentage also...used and usable

You can use list tablespaces show detail (you need to be connected to the database for the show detail clause). This will show you the amount of disk (in bytes if I'm not mistaken) available to the tablespace, the amount used, and the amount "claimed" (known as the "high water mark"). Claimed vs used can differ at times. Sometime after massive deletes and even a REORG the amount of the tablespace used is significantly reduced, but if the high water mark is high, then DB2 still has laid claim to this disk and not released it back to the OS.


Multiple Entries Return Lowest Data With Lowest Date We had a few spreadsheets get imported multiple times into a database so we now have duplicate data (which of course had primary keys etc been assigned this would not have happed...)

However - the issue at hand is I need a way to return the data associated with the smallest (oldest) date for each PN in the database.  Below is a sample of the data and the structure.  I tried to use Top 1 but I realized there was no rhyme or reason to what SSMS deemed as the 1st entry.

What would a query be for the syntax below to return the oldest date - expected returned results:

  Number One 111111   2014-01-01
    Number Two 2222     2014-02-22

Sample DDL ->

  Declare @Test Table (pn varchar(200), ECN int, d1 date)
  Insert Into @Test (pn, ECN, d1) Values
  ('Number One', '111111', '2014-01-01')
  ,('Number One', '111111', '2014-01-31')
  ,('Number One', '111111', '2014-02-14')
  ,('Number Two', '2222', '2014-02-22')
  ,('Number Two', '2222', '2014-02-25')

SELECT pn, MIN(d1) AS [oldestDate]
FROM @Test
GROUP BY pn

pn          oldestDate
Number One  2014-01-01
Number Two  2014-02-22


MySQL: many Sleep processes I've queried SHOW processlist as root after a server restert, and got a long list of Sleep threads:

+-----+-------------+-----------------+---------+---------+------+-------+------------------+
| Id  | User        | Host            | db      | Command | Time | State | Info             |
+-----+-------------+-----------------+---------+---------+------+-------+------------------+
| 217 | root        | localhost       | masters | Sleep   |  371 |       | NULL             |
| 251 | mastersuser | localhost:50883 | masters | Sleep   |  297 |       | NULL             |
| 252 | mastersuser | localhost:50884 | masters | Sleep   |  297 |       | NULL             |
| 253 | mastersuser | localhost:50885 | masters | Sleep   |  297 |       | NULL             |
| 254 | mastersuser | localhost:50891 | masters | Sleep   |  296 |       | NULL             |
| 261 | mastersuser | localhost:50897 | masters | Sleep   |  281 |       | NULL             |
| 260 | mastersuser | localhost:50896 | masters | Sleep   |  281 |       | NULL             |
| 262 | mastersuser | localhost:50899 | masters | Sleep   |  281 |       | NULL             |
| 263 | mastersuser | localhost:50900 | masters | Sleep   |  280 |       | NULL             |
| 272 | mastersuser | localhost:50908 | masters | Sleep   |  263 |       | NULL             |
| 273 | mastersuser | localhost:50909 | masters | Sleep   |  263 |       | NULL             |
| 274 | mastersuser | localhost:50911 | masters | Sleep   |  263 |       | NULL             |
| 275 | mastersuser | localhost:50912 | masters | Sleep   |  263 |       | NULL             |
| 287 | mastersuser | localhost:52434 | masters | Sleep   |  242 |       | NULL             |
| 288 | mastersuser | localhost:52435 | masters | Sleep   |  242 |       | NULL             |
| 289 | mastersuser | localhost:52436 | masters | Sleep   |  242 |       | NULL             |
| 290 | mastersuser | localhost:52437 | masters | Sleep   |  242 |       | NULL             |
| 296 | mastersuser | localhost:52445 | masters | Sleep   |  233 |       | NULL             |
| 298 | mastersuser | localhost:52446 | masters | Sleep   |  232 |       | NULL             |
| 299 | mastersuser | localhost:52447 | masters | Sleep   |  232 |       | NULL             |
| 300 | mastersuser | localhost:52448 | masters | Sleep   |  232 |       | NULL             |
| 301 | mastersuser | localhost:52449 | masters | Sleep   |  231 |       | NULL             |
| 308 | mastersuser | localhost:52453 | masters | Sleep   |  219 |       | NULL             |
| 315 | mastersuser | localhost:52457 | masters | Sleep   |  210 |       | NULL             |
| 316 | mastersuser | localhost:52458 | masters | Sleep   |  210 |       | NULL             |
| 317 | mastersuser | localhost:52459 | masters | Sleep   |  210 |       | NULL             |
| 319 | mastersuser | localhost:52460 | masters | Sleep   |  210 |       | NULL             |
| 330 | mastersuser | localhost:52468 | masters | Sleep   |  196 |       | NULL             |
| 331 | mastersuser | localhost:52469 | masters | Sleep   |  196 |       | NULL             |
...
| 436 | mastersuser | localhost:52547 | masters | Sleep   |   47 |       | NULL             |
| 468 | root        | localhost       | masters | Query   |    0 | NULL  | SHOW processlist |
+-----+-------------+-----------------+---------+---------+------+-------+------------------+

After a while, the sleep processes are gone.

Should I regard this as a problem?

To me, it seems like an application connecting using the 'mastersuser' account is not properly closing connections, so yes I'd say it's a problem. They likely disappear after reaching the wait_timeout or interactive_timeout setting.


Query Slower after Rows Deleted I have a Table that stored GPS data from vehicle trackers.

There are 8 vehicles tracked via an API. Each time the information is returned from the web server each of the vehicles has its information stored in the table. The table is pinged every 10 seconds. So 8 new rows are added every 10 seconds.

I was doing a backup and noticed that the DB had grown from 100mb to approx ~3Gb.

I deleted all rows from the GPS table where the TimeStamp was older than 30 days which took the row count from 4 million down to 2.5 million.

The query to pull historical vehicle data was typically taking about 5 seconds before the purge; after it, it times out.

This is the query that displays dates for which vehicle data is available:

SELECT CAST(TIMESTAMP AS DATE) AS `Vehicle_Date`
FROM `GPSLog`
GROUP BY `Vehicle_Date`
ORDER BY TIMESTAMP DESC

This is the query that is now timing out:

SELECT JSONString, TimeStamp, DATE(TimeStamp) as JDate, ID
FROM BusinessManager.GPSLog
Where IMEI = @ID
  AND DATE(TimeStamp) = @D1

I have tried OPTIMIZE TABLE GPSLog and increasing the Buffer_Pool_Size but neither of these things have resolved the issue.

Any anybody suggests any alternative actions I can take?

The table structure is:

ID (INT 10) PK/AI
IMEI (Big INT 20) 0
JSONString (Json) NULL
TImeStamp (TimeStamp) CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
GPSLog  0   PRIMARY 1   ID  A   2055001             BTREE
GPSLog  1   Vehicle 1   TimeStamp   A   367903          YES BTREE
GPSLog  1   Vehicle 2   IMEI    A   2055252         YES BTREE

Table, Create Table

GPSLog

CREATE TABLE `GPSLog` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `IMEI` bigint(20) DEFAULT '0',
  `JSONString` json DEFAULT NULL,
  `TimeStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Vehicle` (`TimeStamp`,`IMEI`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5533794 DEFAULT CHARSET=utf8

EXPLAIN

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'GPSLog', NULL, 'ALL', NULL, NULL, NULL, NULL, '2055356', '100.00', NULL

ANALYZE TABLE .. may refresh the statistics such that queries run faster.  (Defragging (OPTIMIZE TABLE) takes a long time and is probably overkill.)
For future purging, implement PARTITION BY RANGE; see this for discussion.
The first query would be faster if the ORDER BY said the same thing as the GROUP BY.
Instead of AND DATE(TimeStamp) = @D1, say AND Timestamp >= @D1 AND Timestamp < @D1 + INTERVAL 1 DAY, plus have composite INDEX(IMEI, Timestamp).  (The index you have is not as efficient for this query.)
innodb_buffer_pool_size should be somewhere around 70% of RAM.  Increasing it too much will lead to swapping, which is terrible for performance.


Would like to know why this query is behaving this way? I have an emp table, with schema and some initial values like below:

 emp_id | name | last_name | role_id
--------+------+-----------+---------
      1 | hell | bell      |       1
      2 | well | tell      |       2
(2 rows)

And another role table, which emp table has referenced on role_id.
Below is the role table:

 role_id | role_name
---------+-----------
       1 | tech
       2 | op
       3 | dev
(3 rows)

I was just playing around, and wrote the below query, which I suppose don't make much sense.

SELECT emp.name, role.role_name, emp.emp_id
FROM emp
INNER JOIN role
    ON emp.role_id = (
        SELECT role_id from role WHERE role_name = 'tech'
    );

It worked and returned this result:

 name | role_name | emp_id
------+-----------+--------
 hell | tech      |      1
 hell | op        |      1
 hell | dev       |      1
(3 rows)

I would like to know why I'got this result and how it worked?

Your query, given your data, is equivalent to this one:

SELECT emp.name, role.role_name, emp.emp_id
FROM emp
INNER JOIN role
    ON emp.role_id = 1; -- because this is the role_id for 'tech'

This means that you don't define a condition about how to join the two tables.  In turn, this results in a Cartesian product of role (without any restriction about which rows to return) and emp (where role_id is 1).

Again rewriting the query, it is effectively a CROSS JOIN:

SELECT emp.name, role.role_name, emp.emp_id
FROM emp
CROSS JOIN role
WHERE emp.role_id = 1;

See them on SQLFiddle.

What you possibly want is

SELECT emp.name, role.role_name, emp.emp_id
FROM emp
INNER JOIN role
    ON emp.role_id = role.role_id
WHERE emp.role_id = 1;


Does SQL Server Date Correlation Optimization only work on DateTime fields, or will a Date work, too? I ask because this is an older feature that I just discovered recently. The documentation mentions DateTime a few times but doesn't explicitly say that other date types are compatible or not.

I have a Data Warehouse that I believe could benefit from this, but 90% of the dates that matter are Date types, not DateTime. I am on SQL 2014, but information on any version would be helpful.

Yes, it works for both date and datetime columns. The experiment is rather simple - create a database with the option on, create tables with both types, insert data that satisfies a correlation pattern (like all dates in one table are after the dates in the other table), run some queries that exploit that pattern, and then observe the auto-created stats are the same for both sets of tables.

Create the database and turn on the option:

USE master;
GO
CREATE DATABASE splut;
GO
ALTER DATABASE splut SET DATE_CORRELATION_OPTIMIZATION ON;
GO
USE splut;
GO

Create two tables with correlated date columns, and two tables with correlated datetime columns:

CREATE TABLE dbo.d1(id int primary key, d date);
CREATE TABLE dbo.d2(id int not null foreign key references dbo.d1(id), d date);
CREATE TABLE dbo.d3(id int primary key, d datetime);
CREATE TABLE dbo.d4(id int not null foreign key references dbo.d3(id), d datetime);
GO

Insert some correlated data:

INSERT dbo.d1(id, d) SELECT DISTINCT ABS(object_id),'20190101' FROM sys.all_objects;
INSERT dbo.d2(id, d) SELECT id,DATEADD(DAY, ABS(id)%20, '20190101') FROM dbo.d1;
INSERT dbo.d3(id, d) SELECT DISTINCT ABS(object_id),'20190101' FROM sys.all_objects;
INSERT dbo.d4(id, d) SELECT id,DATEADD(DAY, ABS(id)%20, '20190101') FROM dbo.d3;

Now run a query that might benefit from correlation statistics (though, really, any join will do):

SELECT TOP (10) * FROM dbo.d1 INNER JOIN dbo.d2 ON d1.d = d2.d;
SELECT TOP (10) * FROM dbo.d3 INNER JOIN dbo.d4 ON d3.d = d4.d;

Now, check the stats:

SELECT OBJECT_NAME(s.object_id), s.name, s.stats_id,
   sc.stats_column_id, c.name, c.system_type_id, t.name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id
INNER JOIN sys.columns AS c
ON sc.object_id = c.object_id AND sc.column_id = c.column_id
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE s.auto_created = 1 AND OBJECT_NAME(s.object_id) IN (N'd1',N'd2',N'd3',N'd4');

Results on my machine (your stats names will vary) show that stats are created both on (id, date/datetime column of parent) and (date/datetime column of child):

I'll leave it as an exercise for the reader if this holds true for time, datetime2, and datetimeoffset, or if it will also work if one column is date and the other is datetime.


Finding gaps-and-islands with repeating group numbers We have a MS-SQL dataset of tests run in various groupings and need to uniquely  identify the groups

sr is the device identifier
tx is the TrackingIndex (insert order)
id is the loop order of each grouped sr

The table looks like:

sr   |tx     |id
-----+-------+---
0027 |9748116|  1
0027 |9748117|  1
0027 |9751933|  1
0027 |9751934|  1
0027 |9751935|  1
01672|9813195|  1
01672|9813196|  2
01672|9813197|  3
01767|9794370|  1
01767|9794374|  2
01767|9794378|  3
01767|9794382|  4
01767|9794386|  5
01767|9813181|  1
01767|9813182|  1

I would like to see a column added like:

sr   |tx     |id | grp
-----+-------+---+---
0027 |9748116|  1| 1
0027 |9748117|  1| 2
0027 |9751933|  1| 3
0027 |9751934|  1| 4
0027 |9751935|  1| 5
01672|9813195|  1| 6
01672|9813196|  2| 6
01672|9813197|  3| 6
01767|9794370|  1| 7
01767|9794374|  2| 7
01767|9794378|  3| 7
01767|9794382|  4| 7
01767|9794386|  5| 7
01767|9813181|  1| 8
01767|9813182|  1| 9

I have been trying to use the Island method, but am not able to create the above column. Most solutions expect the "id" number to be one continuous count with holes.  Ours should always start at one.

SELECT *, ROW_NUMBER() OVER (ORDER BY tx) - id AS grp
FROM S

In the long run I may just change our insert to create a grouped column.  Regardless, I will have to recreate it for all existing entries.


Adding index to large mysql tables I have a table

  | base_schedule_line_items | CREATE TABLE base_schedule_line_items (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    installment int(10) unsigned NOT NULL,
    on_date date NOT NULL,
    actual_date date DEFAULT NULL,
    payment_type int(11) NOT NULL,
    scheduled_principal_outstanding decimal(65,0) NOT NULL,
    scheduled_principal_due decimal(65,0) NOT NULL,
    scheduled_interest_outstanding decimal(65,0) NOT NULL,
    scheduled_interest_due decimal(65,0) NOT NULL,
    currency int(11) NOT NULL,
    updated_at datetime NOT NULL DEFAULT '2013-01-06 14:29:16',
    created_at datetime NOT NULL DEFAULT '2013-01-06 14:29:16',
    loan_base_schedule_id int(10) unsigned NOT NULL,
    lending_id int(10) unsigned NOT NULL,
    reschedule tinyint(1) DEFAULT '0',
    PRIMARY KEY (id),
    KEY index_base_schedule_line_items_loan_base_schedule   (loan_base_schedule_id),
    KEY index_bslt_spd (scheduled_principal_due),
    KEY index_bslt_lending (lending_id),
    KEY index_bslt_actualdate (actual_date),
    KEY index_bslt_spsila   (loan_base_schedule_id,scheduled_principal_due,scheduled_interest_due,actual_date),
    KEY index_bslt_ondate (on_date),
    KEY index_bslt_oa (on_date,actual_date),
    KEY index_bslt_ol (on_date,loan_base_schedule_id),
    KEY index_bslt_oli (on_date,lending_id)
  ) ENGINE=InnoDB AUTO_INCREMENT=30410126 DEFAULT CHARSET=utf8 |

Now this table has 30 million records in it, I need to add two more indexes to this
and its like it takes years to add it.

  alter table base_schedule_line_items add index index_bslt_sla     (scheduled_principal_due,actual_date,lending_id);
  alter table base_schedule_line_items add index    index_bslt_ssla(scheduled_principal_due,scheduled_interest_due,lending_id,actual_date);

I used the below mentioned query to find out size of table

  SELECT table_name AS "Tables",  round(((data_length + index_length) / 1024 /  1024), 2) "Size in MB"  FROM information_schema.TABLES  WHERE table_schema =   "my_database_name";

The result came out as

base_schedule_line_items       |   20111.00

I used this just to calculate data length and left out index length

  SELECT table_name AS "Tables",  round(((data_length) / 1024 /  1024), 2) "Size in MB"  FROM information_schema.TABLES  WHERE table_schema =   "my_database_name";

and result was

  base_schedule_line_items       |    9497.00

The indexes

  KEY index_bslt_actualdate (actual_date),
  KEY index_bslt_spsila   (loan_base_schedule_id,scheduled_principal_due,scheduled_interest_due,actual_date),
  KEY index_bslt_ondate (on_date),
  KEY index_bslt_oa (on_date,actual_date),
  KEY index_bslt_ol (on_date,loan_base_schedule_id),
  KEY index_bslt_oli (on_date,lending_id)

were added by me, but I am not fsure or what purpose other indices were added for. This is fairly big application . Now i need to add those two indices mentioned above as they help me extracting a report using a select statement and I find it very difficult to add them. Any help would be greatly appreciated

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git


No syntax for Nonclustered index in MySQL? So, I have seen people discussing about differences between clustered V Non Clustered index, however, I didn't see any syntax for Non Clustered Index in MysQL. How does MySQL creates Nonclustered Index?

The only thing I saw is Secondary Index in the documentation here

InnoDB:  The PRIMARY KEY is UNIQUE and clustered -- always.
Secondary keys are non-clustered.  They have the PRIMARY KEY (not a rownum or rowid) in the leaf node of the secondary key index.

MyISAM:  Keys are non-clustered -- always.  The leaf nodes of indexes have a pointer (think: byte offset) into the data file to get from the index to the data.  ALTER TABLE tbl ORDER BY ...; will reorder the "data", thereby temporarily give you clustering on whatever you ORDERed BY.

MySQL decided those were "good enough".  I have only very rarely found a need for something else.


How do I convert a BIGINT to a DATETIME in Informix 12.1 I'm new to Informix, and I'm trying to convert a bigint to a datetime.

SELECT sdatetime
FROM CallDetail;

Results:
sdatetime
----------
1572509662678
1572518550704
1572519033540

This seems to be the number of ms from 1-1-1970 but I can't get the syntax right to convert this.  The closest I got was getting a date with no time.  I need both.

How can I do this?

One way to do it is with this procedure:

{
#   "@(#)$Id: frunixtime.spl,v 1.2 2002/09/25 18:10:48 jleffler Exp $"
#
# Stored procedure FROM_UNIX_TIME written by Jonathan Leffler
# (jleffler@us.ibm.com) as counterpart to TO_UNIX_TIME.
#
# If you run this procedure with no arguments (use the default), you
# need to worry about the time zone the database server is using because
# the value of CURRENT is determined by that, and you need to compensate
# for it if you are using a different time zone.
#
# Note that this version works for dates after 2001-09-09 when the
# interval between 1970-01-01 00:00:00+00:00 and current exceeds the
# range of INTERVAL SECOND(9) TO SECOND.  Accepting DECIMAL(18,5) allows
# it to work for all valid datetime values including fractional seconds.
# In the UTC time zone, the 'Unix time' of 9999-12-31 23:59:59 is
# 253402300799 (12 digits); the equivalent for 0001-01-01 00:00:00 is
# -62135596800 (11 digits).  The integer part of both these values is
# unrepresentable in a 32-bit integer, of course, so most Unix systems
# won't handle this range, and the so-called 'Proleptic Gregorian
# Calendar' used to calculate the dates ignores locale-dependent details
# such as the loss of days that occurred during the switch between the
# Julian and Gregorian calendar, but those are minutiae that most people
# can ignore most of the time.
}

CREATE PROCEDURE from_unix_time(v DECIMAL(18,5) DEFAULT 0)
            RETURNING DATETIME YEAR TO FRACTION(5);
    DEFINE n DATETIME YEAR TO FRACTION(5);
    DEFINE i1 INTEGER;
    DEFINE i2 DECIMAL(11,5);
    LET i1 = v / (24 * 60 * 60);
    LET i2 = v - (i1 * 24 * 60 * 60);
    LET n  = DATETIME(1970-01-01 00:00:00.00000) YEAR TO FRACTION(5);
    LET n  = n + i1 UNITS DAY;
    LET n  = n + i2 UNITS FRACTION(5);
    RETURN n;
END PROCEDURE;

You'd use:

SELECT sdatetime, from_unix_time(sdatetime / 1000)
  FROM CallDetail;

Example output:

1572509662678    2019-10-31 08:14:22.67800
1572518550704    2019-10-31 10:42:30.70400
1572519033540    2019-10-31 10:50:33.54000

I suspect there may now be a built-in method to achieve an equivalent result, but I've forgotten (or never learned) what it is.


Improving statistics on a large "WHERE IN" SQL Server query Currently, I'm trying to run the following example query:

SELECT [DATA1], [DATA2] FROM TABLE WHERE
    [DIMENSION0] IN (1, 5, ... (possibly 10s of numbers)) AND
    [DIMENSION1] IN (5) AND
    [DIMENSION2] IN (10) AND
    [DIMENSION3] IN (48) AND
    [DIMENSION4] IN (1) AND
    [DIMENSION5] IN (1) AND
    [DIMENSION6] IN (8) AND
    [DIMENSION7] IN (1) AND
    [DIMENSION8] IN (52) AND
    [DIMENSION9] IN (1, 10, ... (possibly 100s of numbers)) AND
    [DIMENSION10] IN (1, 235, ... (possibly 1000s of numbers)) AND
    [DIMENSION11] IN (1)

The table looks like the following;

D = Dimension

[D0] [D1] [D2] [D3] [D4] [D5] [D6] [D7] [D8] [D9] [D10] [D11] [DATA1] [DATA2]

Which contains a clustered index along all the dimensions, and could contain millions of records.

When I run this query through SSMS, I get the following query plan:

Here, it wildly overestimates the number of records it is looking for, which I believe is the reason for it running so slowly.

I've updated the statistics, but that wasn't the problem, so I'm left with it being an issue with the query.

I've also been able to improve the speed of the query by forcing SQL to run in parallel by using:

OPTION(QUERYTRACEON 8649)

This produces the following execution plan:

This is faster, but it is still overestimating the number of rows.

I was hoping that someone might be able to help me understand why this estimation is so high, and how I could possibly reduce it.

Clustered index definition:

/****** Object:  Index [ClusteredIndex-20180726-083210]    Script Date:
26/07/2018 09:47:58 ******/
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20180726-083210] ON
[dbo].[TABLE]
(
[DIMENSION0] ASC,
[DIMENSION1] ASC,
[DIMENSION2] ASC,
[DIMENSION3] ASC,
[DIMENSION4] ASC,
[DIMENSION5] ASC,
[DIMENSION6] ASC,
[DIMENSION7] ASC,
[DIMENSION8] ASC,
[DIMENSION9] ASC,
[DIMENSION10] ASC,
[DIMENSION11] ASC,
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I perform insert, updates and deletes to the table.

I can't use a Clustered Columnstore Index, as the Data2 column is varbinary(max). I tried using a non-clustered version, but the query plan just used the clustered index.

I did update statistics with FULLSCAN. There are some Dimensions that are hit more than others. I have previously experimented with the order of the Dimensions in the Clustered Index, but it still overestimates the number of rows.

Full query with plan: https://www.brentozar.com/pastetheplan/?id=HyLHtXDVX

The issue may not be with improving statistics, but rather a problematic indexing strategy.

Indexing all (or many) columns is usually not effective. Indexes were intended to be built from a small number of high-cardinality columns using small data types (int, bigints, small varchars).

You may consider hashing any huge columns like the varbinary(max) as part of the insert or ETL process and then indexing them:

How to handle uniqueness of many large columns in SQL Server?

How many rows in your main table? What percent of those rows will be returned by your query?

I ask because if your query returns ~20% of the data then nonclustered indexes will probably be eliminated as a retrieval path because scanning NC indexes can result in hitting the same datapage at different times during the scan to retrieve multiple rows on that page.


Progress of SELECT INTO statement Our ETL flow has a long-running SELECT INTO-statement, that's creating a table on the fly, and populating it with several hundred million records.

The statement looks something like SELECT ... INTO DestTable FROM SrcTable

For monitoring purposes, we would like to get a rough idea of the progress of this statement, while it is executing (approx. rowcount, written number of bytes, or similar).

We tried the following to no avail:

-- Is blocked by the SELECT INTO statement:
select count(*) from DestTable with (nolock)

-- Returns 0, 0:
select rows, rowmodctr
from sysindexes with (nolock)
where id = object_id('DestTable')

-- Returns 0:
select rows
from sys.partitions
where object_id = object_id('DestTable')

Furthermore, we can see the transaction in sys.dm_tran_active_transactions, but I was not able to find a way to get the count of affected rows on a given transaction_id (something similar to @@ROWCOUNT perhaps, but with the transaction_id as argument).

I understand that on SQL Server the SELECT INTO-statement is both a DDL and a DML statement in one, and as such, the implicit table creation will be a locking operation. I still think there must be some clever way to obtain some kind of progress information while the statement is running.

I suspect that rows in sys.partitions is 0 due to not being committed yet. But this does not mean that SQL Server is unaware of what will go there if the Transaction does commit. The key is in remembering that all operations go through the Buffer Pool (i.e. memory) first, regardless of COMMIT or ROLLBACK of the operation. Hence, we can look in sys.dm_os_buffer_descriptors for that info:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT  --OBJECT_NAME(sp.[object_id]) AS [TableName], sdobd.*, '---', sp.*, '---', sau.*
       SUM(sdobd.[row_count]) AS [BufferPoolRows],
       SUM(sp.[rows]) AS [AllocatedRows],
       COUNT(*) AS [DataPages]
FROM sys.dm_os_buffer_descriptors sdobd
INNER JOIN  sys.allocation_units sau
        ON sau.[allocation_unit_id] = sdobd.[allocation_unit_id]
INNER JOIN  sys.partitions sp
        ON  (   sau.[type] = 1
            AND sau.[container_id] = sp.[partition_id]) -- IN_ROW_DATA
        OR  (   sau.[type] = 2
            AND sau.[container_id] = sp.[hobt_id]) -- LOB_DATA
        OR  (   sau.[type] = 3
            AND sau.[container_id] = sp.[partition_id]) -- ROW_OVERFLOW_DATA
WHERE   sdobd.[database_id] = DB_ID()
AND     sdobd.[page_type] = N'DATA_PAGE'
AND     sp.[object_id] = (SELECT so.[object_id]
                          FROM   sys.objects so
                          WHERE  so.[name] = 'TestDump')

If you want to see the details, uncomment the first row of items in the SELECT list, comment out the remaining 3 lines.

I tested by running the following in one Session and then repeatedly running the query above in another.

SELECT so1.*
INTO   dbo.TestDump
FROM   sys.objects so1
CROSS JOIN sys.objects so2
CROSS JOIN sys.objects so3;


MS SQL Table and ETL connundrum We have a database which has a number of tables that all utilise different identification methods for currencies. Some use ISO numbers other tables use ISO Codes. All the data and (and effectively) structure of these tables is from outside third party sources, so there is no influence on how and the format of the data produced.

We have a table that is a Currency table that contains both ISO number and ISO alpha codes, and a CurrencyId (just a simple integer: 1, 2, 3 etc).

We have an ETL that imports the data, and in some tables, the data is converted at import time from one currency identifier (eg ISO number) to the other currency identifier (eg ISO code). This I think is a result of different, incongruent, teams working on the software but that is a topic in its own right lol.

We have a .net software application that references these tables directly, and obviously there's no consistency in which ISO code (number or alpha) that the software is referencing.

It is also proving difficult to maintain code by referencing sometimes the ISO code and other times the ISO Number for the currency.

So we are considering one of the following resolutions:

Import all data and map at that point a new derived field (we are using SSIS) that is the CurrencyId for that currency from the "Currency" table based on the ISO code number or alpha (and still import the currency identifier that the original data does). Then change the references in the software to utilise the CurrencyId instead of the currency ISO code or number.

OR

build views for all the relevant tables and join them to the currency table on either ISO number or ISO code (whichever relevant). Then change all the references in the software to reference the view instead and utilise the CurrencyId instead of the currency ISO code or number.

We would like some advice and input as to which would be better approach for a performance perspective. Data storage perspective I imagine will be a small impact since I think the CurrencyId can be contained within a tinyint datatype field, but still very interested to know what you think in this area too.

The common sense approach would be to cleanse and sanitise the data on import. Creating a view to mask the problem is not fixing the underlying issue, it's just hiding it.

Also, having the views is unlikely to be a one stop fix, you'll likely need to write other code in other places to work around the issue, which will all need to be maintained.

Having code that works around a problem might be the quicker of the options, but in the long run it will create more work and problems than fixing the data as it comes in to the system.


SQL Error(1054): Unknown column in 'where clause' when trying to update. Column is actually available I have two tables namely table_a and table_b.
Table_a has several columns including student_id and course_start_date
Table_b has several columns including student_id and fee_pay_date
student_id is INT field and course_start_date & fee_pay_date are DATE fields
I want to update column course_start_date in table_a by reading fee_pay_date from table_b for each student.
When I write the following command in MariadB, it throws up an error:
UPDATE table_a
SET table_a.course_start_date = table_b.fee_pay_date
WHERE (table_a.student_id = table_b.student_id);

Error: SQL Error(1054): Unknown column 'table_b.student_id in 'where clause'

I have checked the tables. All column names are correct. Can anyone help on how to resolve this issue?
Thanks in advance.

That is not valid SQL - you are not referencing table_b in any FROM or JOIN clauses so can not use columns from it in SELECT, SET, WHERE or other parts.
Sticking to standards compliant SQL you need to use a sub-select here:
UPDATE table_a
   SET table_a.course_start_date = (
           SELECT table_b.fee_pay_date
             FROM table_b
            WHERE table_b.student_id = table_a.student_id
            );

Some databases support using JOINs in UPDATE statements so avoid sub-selects. This can often be easier to read but will break if you need to support different database back-ends. For mySQL and MariaDB this would be something like:
UPDATE table_a
  JOIN table_b
       ON (table_a.student_id = table_b.student_id)
   SET table_a.course_start_date = table_b.fee_pay_date
       ;

Not that both of these statements will update every row in table_a even if the row already has the right value so could be very inefficient if only a few rows actually need updating. This is by design, as you might want triggers to fire and so forth even for a NoOp update. To avoid this if it is not desirable add a WHERE clause to block unneeded updates:
UPDATE table_a
  JOIN table_b
       ON table_a.student_id = table_b.student_id
   SET table_a.course_start_date = table_b.fee_pay_date
 WHERE table_a.course_start_date <> table_b.fee_pay_date
    OR (table_a.course_start_date IS     NULL AND table_b.fee_pay_date IS NOT NULL)
    OR (table_a.course_start_date IS NOT NULL AND table_b.fee_pay_date IS     NULL)
       ;

or:
UPDATE table_a
  JOIN table_b
       ON table_a.student_id = table_b.student_id
   SET table_a.course_start_date = table_b.fee_pay_date
 WHERE COALESCE(table_a.course_start_date, '9999-01-01') <> COALESCE(table_b.fee_pay_date, '9999-01-01');

(you can simplify those WHERE clauses a bit if table_b.fee_pay_date can never be NULL)

As a related side-issue: to illustrate the concern of using non-standard syntax, the SQL Server equivalent to
UPDATE table_a
  JOIN table_b
       ON table_a.student_id = table_b.student_id
   SET table_a.course_start_date = table_b.fee_pay_date
       ;

would be
UPDATE table_a
   SET course_start_date = table_b.fee_pay_date
  FROM table_a
  JOIN table_b
       ON table_a.student_id = table_b.student_id
       ;

and in postgres you would need:
UPDATE table_a
   SET course_start_date = table_b.fee_pay_date
  FROM table_b
 WHERE table_a.student_id = table_b.student_id
       ;

Not vastly different, but if you want to support multiple databases or later move from one to another, you need to be careful of this sort of difference. The sub-query version should behave the same in all three (and most other places).


Understanding of schema and tablespaces - recommendation for a later seperation DLDR: I would  like to get

a confirmation of my understanding of tablespaces and schemas (see image)
a recommendation how to organize schemas and tablespace to make a later separation easy.

Background: In our project we have two different teams that are working on the implementation of a website. The idea is to later separate the basic-services from the website (cms / portal) so that different websites / portals can be created an all are using the same independent basic-services.

The current installation looks like this:

TABLESPACE_N  MB  AUTOEXTENSIBLE
FOOBAR_DATA    1024       YES
FOOBAR_INDEX   1024       YES
FOOBAR_LOB     1024       YES
SYSAUX         600       YES
SYSTEM         700       YES
TEMP          1024       NO
UNDOTBS1       456       YES

This question is about how to move a schema to another tablespace. Some recommendations store indexes in another filesystem are controversial / questionable. But this is not the scope of my question. I would to get a recommendation how to organize schema and tablespace to make a later separation easy.

Confirmation / Clarification: My underständing of oracle is the following:

Tablespace: logical storage unit, one or more datafiles
Datafiles: physical structure that conforms to the OS, a datafile can be associated with only one tablespace and only one database
Schema: a collection of logical structures of data (table, index), or schema objects (view,..), same name as the db-user that owns it, each user owns a single schema. [A USER may be given access to  SCHEMA OBJECTS owned by different USERS.]

In the attached image you can see two schemas:

white schema S1
and yellow schema S2

Question 1:

Is my understanding how a schema could be organized within the database correct?
So we could create two schemas (/ two users) and separate the objects for the FOOBAR-services (yellow) from the FOOBAR-website (white) in any way we want to?

Schema 1 could only use tablespace 1 with datafile 1 and 2?
Schema 2 could only use tablespace 2 with datafile 3?

Question 2:

What structure would you recommend to make a later separation of the FOOBAR-website and the FOOBAR-services very easy

different tablespaces for FOOBAR-Web and FOOBAR-Services?
each schema within its own tablespace (or n spaces)?

I think you are over complicating this.  Grants and roles govern access by users not what table space and data files where the data is located in.  Yes, backup, recover and export are much easier if schemas have their own table space but that doesn't seem to be your question.

Separate your business domains by user/schema.
Give each user/schema their own group of tablespaces which contains their tables/indexes/lobs eg: User1Data, User1Indexes, User1LOBS
create roles to allow users/schemas to access data/code in other schemas

From your comment below I would add:

user/schema are the logical way to separate data and code.  Use different schemas from day one if your business domain works that way (and that seems to be what you are indicating).  Moving tables to different schemas after deployment can be done but it's a pain.
in your comment you still link table spaces and permissions.  There is no link between what table space a user's data is in and the permissions granted on that data.

If you will allow me a simplification:

users and schemas allow  you to control access to data and code through roles and grants
table spaces and data files allow you to manage the physical files used by the database.  Easier backup and recovery are one of the things that is easier to do when each user has their data in a clearly defined set of table spaces.


Add New Columns that Repeat Min and Max Dates I have a table that has a column for the Line # and a column for the date that Line # was invoiced for.  I want to create two new columns: one column that repeats the Min date for the Line # and another column that repeats the Max date for the Line #.

Here is the original table:

Here is what I want the result to be:

You can use a SQL Server windowing function like this:

--demo setup
Declare @t table ([month] date, linenumber int)
insert into @t([month],linenumber) values
('12/30/2018',123), ('1/29/2019',123),
('7/30/2018',789), ('8/29/2019',789)

SELECT [month]
    ,linenumber
    ,min([month]) OVER (PARTITION BY linenumber) AS mininvoicedate
    ,max([month]) OVER (PARTITION BY linenumber) AS maxinvoicedate
FROM @t

| month      | linenumber | mininvoicedate | maxinvoicedate |
|------------|------------|----------------|----------------|
| 2018-12-30 | 123        | 2018-12-30     | 2019-01-29     |
| 2019-01-29 | 123        | 2018-12-30     | 2019-01-29     |
| 2018-07-30 | 789        | 2018-07-30     | 2019-08-29     |
| 2019-08-29 | 789        | 2018-07-30     | 2019-08-29     |

For more information about SQL Server windowing functions, check out the documentation in this post SELECT - OVER Clause (Transact-SQL)


MySQL multi-table update I'm a bit confused about the proper way to update one table, based on the values of corresponding rows in another table.

Here is the query I have:

UPDATE
    features,
    images
SET
    features.deleted = 1
WHERE
    features.objectType = 'image'
    AND images.id = features.objectId
    AND images.uploaded = 0;

My goal:

  I want to set the features.deleted column value for all rows in the
  features table to be 1, only if the corresponding
  images.uploaded value for that associated image is 0.

Business logic:

  I want to go over all the images that been created in my app, and if
  the image wasn't successfully uploaded, I want to delete the
  corresponding features record.

I believe this works, but have been having a hard time finding specific examples using my approach (most suggest using joins).

My concern is that the UPDATE query is too general: I just want to make sure that it will only update feature records that have their associated image record with an uploaded value of 0.

Thanks for any guidance/confirmation!

I was correct, and the query above worked as desired. For anyone stumbling on this in the future, the format seemed to work given my context.

UPDATE
    features,
    images
SET
    features.deleted = 1
WHERE
    features.objectType = 'image'
    AND images.id = features.objectId
    AND images.uploaded = 0;

Thanks for your time everyone :)