Oracle 12cR2 - Image Copy Backup Scenario

I assume you want something like this:

RUN
{
  RECOVER COPY OF DATABASE
    WITH TAG 'incr_update'
    UNTIL TIME 'SYSDATE - 7';
  BACKUP
    INCREMENTAL LEVEL 1
    FOR RECOVER OF COPY WITH TAG 'incr_update'
    DATABASE;
}

The first run takes a level 0 backup (even if the backup statement has level 1)
All the following backups are level 1 backup that contain only the differences to the previous backups. The recovery statement recovers until SYSDATE-7, that is until one week ago. After deleting all backups that aren't needed you have the following backups on disk:

a level 0 backup that is from 7 days ago
the daily  level 1 backup of the last 6 days including today's backup
the archive log backups of the last seven days

I never tried out this strategy but I think a recovery window of 7 days is the appropriate policy.
For control files the following can be found in the Database Backup and Recovery User's Guide

Backing Up Control Files with RMAN
You can back up the control file when the database is mounted or open.
...
If the CONFIGURE CONTROLFILE AUTOBACKUP command is set to ON (by default it is OFF), then RMAN automatically backs up the control file and server parameter file after every backup and after database structural changes. The control file autobackup contains metadata about the previous backup, which is crucial for disaster recovery.
If the autobackup feature is not set, then you must manually back up the control file in one of the following ways:

Run BACKUP CURRENT CONTROLFILE.
Include a backup of the control file within any backup by using the INCLUDE CURRENT CONTROLFILE option of the BACKUP command.
Back up data file 1, because RMAN automatically includes the control file and server parameter file in backups of data file 1.

We do a backup of the whole database that contains a backup of data file 1, so it will contain a backup of a control file, too. But I recommend to enable the autobackup of the control file, too.

CONFIGURE CONTROLFILE AUTOBACKUP ON;

or

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/path/autobackup/%U';

You want to be able to recover to every point in time ind the last 7 days, so you should configure

CONFIGURE  RETENTION POLICY  TO RECOVERY WINDOW OF 7 DAYS;

This deletion policy also includes the backups of the archive log.
You can also configure the archive log deletion policy:

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE disk;

This deletion policy is related to theog archiving destinations, not to the archive log backups. Usualy archiving is done to the "recovery_file_dest".

The archived log deletion policy applies to all log archiving destinations, including the fast recovery area. The policy does not apply to archived redo log files in backup sets.
Only archived redo log files in the fast recovery area are automatically deleted by the database. You can execute the BACKUP ... DELETE INPUT, DELETE ARCHIVELOG, or DELETE OBSOLETE commands to delete logs manually from log archiving destinations, including the recovery area. If FORCE is not specified on the deletion commands, then these deletion commands obey the archived log deletion policy. If FORCE is specified, then the deletion commands ignore the archived log deletion policy.

So finally you have the following scripts:
A configuration script that you have to run once:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/path/autobackup/%U';
CONFIGURE  RETENTION POLICY  TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE disk;

a script for the daily backup:

RUN {
allocate channel c1 device type disk format '/path/%U';
allocate channel c2 device type disk format '/path/%U';
allocate channel c3 device type disk format '/path/%U';
allocate channel c4 device type disk format '/path/%U';
allocate channel c5 device type disk format '/path/%U';
allocate channel c6 device type disk format '/path/%U';
allocate channel c7 device type disk format '/path/%U';
allocate channel c8 device type disk format '/path/%U';
RECOVER COPY OF DATABASE
  WITH TAG 'incr_update'
  UNTIL TIME 'SYSDATE - 7';
BACKUP
  INCREMENTAL LEVEL 1
  FOR RECOVER OF COPY WITH TAG 'incr_update'
DATABASE;
BACKUP ARCHIVELOG ALL DELETE INPUT;
DELETE OBSOLETE;
}

and the following script for the frequent backup of the archive logs. Maybe you want to use less channels for the archive logs.

RUN {
allocate channel c1 device type disk format '/path/%U';
allocate channel c2 device type disk format '/path/%U';
allocate channel c3 device type disk format '/path/%U';
allocate channel c4 device type disk format '/path/%U';
allocate channel c5 device type disk format '/path/%U';
allocate channel c6 device type disk format '/path/%U';
allocate channel c7 device type disk format '/path/%U';
allocate channel c8 device type disk format '/path/%U';
BACKUP ARCHIVELOG ALL DELETE INPUT;
}

If you do not want the recovery following each backup than another way is the following strategy using incremental backups
Once a week run the following script to get a level 0 backup

BACKUP
  INCREMENTAL LEVEL 0
  DATABASE;

On every other day take a levele 1 backup

BACKUP
  INCREMENTAL LEVEL 1
  DATABASE;

or you can do a

BACKUP
  INCREMENTAL LEVEL 0 CUMULATIVE
  DATABASE;

Again you should specify a recovery windows for 7 days and frequently take archive log backups.
You avoid the recovery of the database copy after each backup, but now you need more space for your backup because now you have always two full backups on your backup space. Also the number of level 1 backups ist more than 6.
Assume that  your retention policy is 7 days and you do your level 0 backup always on the same day at the same time (this is the way one usually does) , e.g. you start your backup always on Sunday at 12:00. Now we have Sunday the 29th, 12:00. We have already two level 0 backups on disk, one started on 22nd at 12:00 and one started at 15th at 12:00. If you want to restore the database now to the point in time 22nd, 12:00, this should be possible, because your retention policy is 7 days. But for this restore you need the backup of the 15th, because the backup didn't have finished (or even not started) on 22nd 12:00.
So if you issue a 'DELETE OBSOLETE' command at 29th, 12:00, this will not delete that level 0 backup started at 15th, 12:00. And if you start a level 0 backup now, you will have three level 0 backups on disk. If you want to avoid this to save space you can think about using a recovery window of 6 days instead of 7 days. Then you can issue a DELETE OBSOLETE statement on 29th, 12:00, before you start the backup and the backup of taken on 15th will be removed.
So finally you will have these scripts:
A configuration script that you have to run once:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/path/autobackup/%U';
CONFIGURE  RETENTION POLICY  TO RECOVERY WINDOW OF 6 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE disk;

A script for the weekly level 0 backup

RUN {
allocate channel c1 device type disk format '/path/%U';
allocate channel c2 device type disk format '/path/%U';
allocate channel c3 device type disk format '/path/%U';
allocate channel c4 device type disk format '/path/%U';
allocate channel c5 device type disk format '/path/%U';
allocate channel c6 device type disk format '/path/%U';
allocate channel c7 device type disk format '/path/%U';
allocate channel c8 device type disk format '/path/%U';
DELETE OBSOLETE;
BACKUP
  INCREMENTAL LEVEL 0
DATABASE PLUS ARCHIVELOG DELETE INPUT;
}

a daily script (except for the day when the level 0 runs) for the level 1 backups:

RUN {
allocate channel c1 device type disk format '/path/%U';
allocate channel c2 device type disk format '/path/%U';
allocate channel c3 device type disk format '/path/%U';
allocate channel c4 device type disk format '/path/%U';
allocate channel c5 device type disk format '/path/%U';
allocate channel c6 device type disk format '/path/%U';
allocate channel c7 device type disk format '/path/%U';
allocate channel c8 device type disk format '/path/%U';
DELETE OBSOLETE;
BACKUP
  INCREMENTAL LEVEL 1
DATABASE PLUS ARCHIVELOG DELETE INPUT;
}

and script for the archive log backups

RUN {
allocate channel c1 device type disk format '/path/%U';
allocate channel c2 device type disk format '/path/%U';
allocate channel c3 device type disk format '/path/%U';
allocate channel c4 device type disk format '/path/%U';
allocate channel c5 device type disk format '/path/%U';
allocate channel c6 device type disk format '/path/%U';
allocate channel c7 device type disk format '/path/%U';
allocate channel c8 device type disk format '/path/%U';
BACKUP ARCHIVELOG ALL DELETE INPUT;
}


You can just join the table to itself on the id field = the value + 1 and again on value - 1. My example uses an INNER JOIN which will only return the cases where both match, otherwise you'll want to use the appropriate OUTER JOINS or two separate queries, to not filter out.
SELECT * -- Replace with only the columns you need as SELECT * is bad practice
FROM SomeTable ST1
INNER JOIN SomeTable ST2
    ON ST2.id = ST1.value + 1
INNER JOIN SomeTable ST3
    ON ST2.id = ST1.value - 1


Will reloading a table cause recompiles downstream?

Yes, truncating a table with 90k rows will cause a recompile for any queries that touch it.

You could stop that by turning off auto update stats, and in any update-stats jobs or rebuild-index jobs, you exclude this table. However, I'd suggest that you leave it on - after all, the table really could change over time, and you'd want your query plans to update.


Connection reset to SQL Server when using SSH Tunnel

Try the JDBC driver or ODBC driver(Install/Config Info per platform) supplied by Microsoft for Linux clients.  I've found that I'm often more successful when using the drivers provided by the vendor than those that are included with the product (e.g. in this case DBeaver).  I would think all the other settings remain the same, just swap out the driver url to point the MS supplied driver instead.


While using Cross Apply, can I force "empty" records to appear?

It's a little unclear what you're after, but one way to add in missing values into a GROUP BY query is to add all rows to your starting table with 0 or NULL for the aggregated columns. As @RDFozz pointed out, you want to use NULL if you are doing COUNT aggregates because otherwise the results will be inflated. However, 0 is a good choice if you only do SUM aggregates and don't want to bother with a COALESCE.

You can accomplish this with UNION ALL. One implementation is as follows:

SELECT Payroll
      ,v.Item                       AS [Witholding]
      ,SUM(v.TaxValue)              AS Tax
      ,v.OrderByNumber              AS [OrderByNumber]
FROM
(
    SELECT *
    FROM #EmployeeTaxes

    UNION ALL

    SELECT DISTINCT Payroll, [STATE], [STATE], [STATE], 0, 0, 0, 0, 0
    FROM #EmployeeTaxes
) AS #EmployeeTaxes
CROSS APPLY (
    VALUES ([STATE]   + ' SIT', SIT, [STATE] + '110')
          ,(SDI_STATE + ' Employee SDI', EMPLOYEE_SDI,SDI_STATE + '111')
          ,(SDI_STATE + ' Employer SDI', EMPLOYER_SDI,SDI_STATE + '112')
          ,(SUI_STATE + ' Employee SUI', EMPLOYEE_SUI,SUI_STATE + '113')
          ,(SUI_STATE + ' Employer SUI', EMPLOYER_SUI,SUI_STATE + '114A')
          ,(SUI_STATE + ' Total', EMPLOYEE_SUI + EMPLOYER_SUI, SUI_State + '114B')
          ,(SDI_STATE + ' Total', EMPLOYEE_SDI + EMPLOYER_SDI , SDI_State + '114B')
          ,('---------------------------------',NULL,[STATE] + '121')

) v (Item, TaxValue, OrderByNumber)
GROUP BY Payroll, OrderByNumber, v.Item
ORDER BY PAYROLL, OrderByNumber;

I think that this is closer to what you want? Top result set is from your query in the question and the bottom result set is mine:


Primary Index VS Unique Index Performance

Indexes with the columns in the order given:

actions: INDEX(user_id_to, user_id_from) -- 'covers' first query
actions: INDEX(user_id_to, counter) -- may subsume ORDER BY and LIMIT in 3nd query

Either index will be useful for 2nd query.  DROP KEY user_id_to (user_id_to) as redundant when you add my two suggestions.

For various reasons, it may be better to change to PRIMARY KEY(user_id_to, user_id_from, action_type).  This would

replace the first index suggested above
mostly eliminate the need for the second index above, thereby speeding up inserts (by having fewer indexes)
If you need user_id_from in the WHERE, but without user_id_to, then you would need some index starting with user_id_from.  (I don't yet see a SELECT that begs for such.)

In summary (for actions indexes):

PRIMARY KEY(user_id_to, user_id_from, action_type)
INDEX(user_id_from, ...) -- if needed.

"action_type" -- Do you really need 4 bytes for it?  How many distinct values are there?  If very few, consider an ENUM, otherwise a TINYINT UNSIGNED or SMALLINT UNSIGNED (1,1,2 bytes, respectively).  Shrinking 4 bytes to 1 would save 8GB (3 bytes * 2 copies (data+secondary key) * 1.3B rows).

There is a perfectly nice 2-byte countryCode; don't use a separate Id:

countryCode CHAR(2) CHARACTER SET ascii

How many "locations" are there?  If not millions, I would not bother normalizing city, state, country.  If there are a lot of locations rows in each city, then consider normalizing only city, then have the state and country spelled out in the city table.  (I oppose "over-normalizing".)

If users has PRIMARY KEY(user_id), I have no further suggestion for indexing that table.

The 3rd query says LEFT JOIN users and users.something....  You probably did not want LEFT.


Need help in increasing maximum table size

Sounds like MISSION: IMPOSSIBLE. Just kidding. I just cringe when I see MyISAM.
I can tell you what the MySQL Documentation says. Please note what it says:

You are using a MyISAM table and the space required for the table exceeds what is permitted by the internal pointer size. MyISAM permits data and index files to grow up to 256TB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (2567 – 1 bytes).

With that in mind, you would have to change myisam_data_pointer_size to 7
First, add this to my.cnf under the [mysqld] group header
myisam_data_pointer_size=7

Then, run this in mysql
SET GLOBAL myisam_data_pointer_size = 7;
SET myisam_data_pointer_size = 7;

This will allow your current session and new connections to have the extended capacity and permit you to safely run those ALTER TABLE statements.
Give it a Try !!!
UPDATE 2014-12-03 00:51 EST
I was just thinking. The MyISAM tables vb_profilevisitor  and vb_post may not like the ALTER TABLE because they were created when myisam_data_pointer_size was 6.
To change it manually, do the following
CREATE TABLE vb_post_new LIKE vb_post;
ALTER TABLE  vb_post_new MAX_ROWS=98281474976710655 AVG_ROW_LENGTH=1000000000;
CREATE TABLE vb_profilevisitor_new LIKE vb_profilevisitor;
ALTER TABLE  MAX_ROWS=98281474976710655 AVG_ROW_LENGTH=1000000000;

Check the stats on vb_profilevisitor_new and vb_post_new
SELECT table_name,max_data_length,avg_row_length
FROM information_schema.tables
WHERE table_name IN ('vb_post_new','vb_profilevisitor_new');

If the max_data_length and avg_row_length changed, then copy the table data and swap new tables in
INSERT INTO vb_profilevisitor_new SELECT * FROM vb_profilevisitor;
RENAME TABLE
    vb_profilevisitor TO vb_profilevisitor_zap,
    vb_profilevisitor_new TO vb_profilevisitor
;
DROP vb_profilevisitor_zap;
INSERT INTO vb_post_new SELECT * FROM vb_post;
RENAME TABLE
    vb_post TO vb_post_zap,
    vb_post_new TO vb_post
;
DROP vb_profilevisitor_zap;


Can database backup and restore using pgdump resolve transaction wraparound in postgresql?

When restoring database from pg_dump, it will re initialize transaction id on the database. So, just like the beginning You create a database. It needs time for another transaction wraparound.

Transaction id wraparound is count on a per table level. If a table is truncated, but still another table is possible to be wraparound.

Hope it helps


Some SQLs sometimes take all available RAM

The main problem is with the shared_buffers = 32MB parameter. It is 2014 not 2000. You cannot expect any kind of decent performance on a 6Gb machine, if you do not increase that to at least 512Mb. I believe that you are also using PostgreSQL 64bit (if not make the change now). Also, you say that your dataset = 1.5 GB. Are you sure? 50 million records fit on 1.5GB on disk? Have you run SELECT pg_database_size('myDbName'); Does it still show 1.5GB?

I also see you do not have an index at status_razsmernika field and you do a join on that, which leads to a sequential scan. You also have (razsmernik.status_razsmernika!=6) and (razsmernik.status_razsmernika !=0). So, you really need to add a b-tree index to status_razsmernika.

Also you are using the "updated" field for order in descending value. If this is always the case, specify the order in the related index as well. http://www.postgresql.org/docs/9.3/static/sql-createindex.html


Getting SQL server to recognise a date column

I receive this error "Invalid column name 'Today'"

You can't reference the Today expression in the WHERE clause because the WHERE clause is evaluated before the SELECT clause according to the logical order of query processing.

Since your desired type is date, it would be better to use CAST or CONVERT rather than the DATEADD...DATEDIFF ugliness (common before SQL Server 2008 introduced the date datatype). Also, you need to use DATEADD to calculate yesterday's date instead of a subtraction operator.

The example below uses these techniques to get the orders from yesterday:

SELECT
      order_id
    , user_id
    , date_created
    , order_value
    , city_id
    , CAST(GETDATE() AS date) as Today
FROM dbo.orders
WHERE DATEADD(day, -1, CAST(GETDATE() AS date)) = date_created;


Do you have a PK in the abc table?
Try this=
ALTER TABLE dbo.abc
    ADD StartTime DATETIME2(2) GENERATED ALWAYS AS ROW START NOT NULL
GO

ALTER TABLE dbo.abc
    ADD EndTime DATETIME2(2) GENERATED ALWAYS AS ROW END NOT NULL
GO

ALTER TABLE dbo.abc
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
GO
ALTER TABLE dbo.abc
    SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.abc_history,     DATA_CONSISTENCY_CHECK = ON))
GO


SSIS - XML with row delimiter to a single column of a SQL table

In the flat-file connection manager, in the General pane, there is an option to set a text qualifier. If you set this to double quote (") for your file then SSIS should ignore any row delimiters that are contained between the double quotes.

There is also an AlwaysCheckForRowDelimiters property that could help. See this msdn page for more.


For client parameter, please mention the database server name along with FQDN.

For example if your server name is test and domain is abc.com, then enter the value for Client as "test.abc.com".

Also, please execute the statement  in one Line without any Returns/space:

EXEC @returnCode = dbo.emc_run_delete ' -b "2 months ago" -e
"now" -N mssql -a "DDBOOST_USER=user" -a
"DEVICE_PATH=/path" -a "DEVICE_HOST=host" -a
"CLIENT=test.abc.com"'


My guess is that you are running IndexOptimize within an existing transaction.  Ola's script is checking for an open transaction close to the very start of the procedure.

Would you really want the IndexOptimize process running under ONE transaction and possibly filling up the log along with possible blocking.

Ola's code has a variety of begin transaction and commit statements.  If the process is already running under a transaction, additional begin transaction statements create nested transactions and the subsequent commit statements only decrement the @@trancount, but don't actually commit the work.

Try running the process without an explicit begin transaction.


aggregating sums for specific time but would like to have multiple times in the same query

select menu_item_id,
       menu_item_header,
       sum( case when created_at between '2018-01-01'
                                     and '2018-12-31'
                 then quantity
                 end ) sum_2018,
       sum( case when created_at between '2019-01-01'
                                     and '2019-12-31'
                 then quantity
                 end ) sum_2019
from line_items
where order_id in ( select id
                    from orders
                    where menu_id=807
                      and stripe_transaction_state='paid'
                      and created_at between '2018-01-01'
                                         and '2019-12-31' )
group by menu_item_id,
         menu_item_header
order by menu_item_id;


SQL Server 2017 login language display is wrong

This bug has been fixed as of SSMS version 18.3.


Remove the space between the comma and the second IP. It does not look like mongod is handling gracefully.


Find the size of each index in a MySQL table

You should query the following:

INFORMATION_SCHEMA.STATISTICS
INFORMATION_SCHEMA.COLUMNS

If the table was called mydb.mytable, just run the following:

SET @PowerOfTwo = 0;
SET @GivenDB = 'mydb';
SET @GivenTB = 'mytable';
SELECT COUNT(1) INTO @MyRowCount FROM mydb.mytable;
SELECT
    index_name,SUM(column_length * @MyRowCount) indexentry_length
FROM
(
    SELECT
        index_name,column_name,
        IFNULL(character_maximum_length,
        IF(data_type='double',8,
        IF(data_type='bigint',8,
        IF(data_type='float',4,
        IF(data_type='int',4,
        IF(data_type='mediumint',3,
        IF(data_type='smallint',2,
        IF(data_type='datetime',4,
        IF(data_type='date',3,
        IF(data_type='tinyint',1,1)
        ))))))))
    ) / POWER(1024,@PowerOfTwo) column_length
FROM
(
    SELECT
        AAA.index_name,AAA.column_name,
        BBB.data_type,coalesce(AAA.sub_part,BBB.character_maximum_length) AS character_maximum_length
        FROM
        (
            SELECT table_schema,table_name,index_name,column_name,sub_part
            FROM information_schema.statistics
            WHERE table_schema = @GivenDB AND table_name = @GivenTB
        ) AAA INNER JOIN
        (
            SELECT
                table_schema,table_name,column_name,
                character_maximum_length,data_type
            FROM information_schema.columns
            WHERE table_schema = @GivenDB AND table_name = @GivenTB
        ) BBB USING (table_schema,table_name,column_name)
    ) AA
) A GROUP BY index_name;

Give it a try !!!

CAVEAT #1

Please note the first line

SET @PowerOfTwo = 0;

Here is how the setting affects the output

0 : Bytes
1 : KiloBytes
2 : MegaBytes
3 : GigaBytes
4 : TeraBytes

CAVEAT #2

This does not take BTREE overhead and fragmentation into account.

Here is another post from someone else : Find out MySQL index size for a concrete index

That answer suggests a fudge factor of 1.4 to 2.8.

Therefore, whatever answer my queries produce, multiple it by 1.4 or 2.8 to factor in BTREE nodes and possible fragmentation. You should defragment the table before running my queries.

If your table is MyISAM:

OPTIMIZE TABLE mydb.mytable;

If your table is InnoDB:

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;


I have an INSTEAD OF trigger, but PostgreSQL still complains while I insert into view

You have a trigger INSTEAD OF DELETE in your code, but none INSTEAD OF INSERT. Create that, too.
You can make that a trigger INSTEAD OF INSERT OR DELETE, but I'd suggest separate trigger functions for INSERT and DELETE and separate triggers. Simplifies the function code (no more need for IF TG_OP =  etc.).
Also consider this quote from the manual:

A row-level INSTEAD OF trigger should either return NULL to
indicate that it did not modify any data from the view's underlying
base tables, or it should return the view row that was passed in (the
NEW row for INSERT and UPDATE operations, or the OLD row for
DELETE operations). A nonnull return value is used to signal that
the trigger performed the necessary data modifications in the view.
This will cause the count of the number of rows affected by the
command to be incremented. For INSERT and UPDATE operations only,
the trigger may modify the NEW row before returning it. This will
change the data returned by INSERT RETURNING or UPDATE RETURNING,
and is useful when the view will not show exactly the same data that
was provided.


pg_basebackup not running even after trying all the methods

You did something that you shouldn't do: you used the root user to edit a file in the PostgreSQL data directory using vi.

Then the session got interrupted, and now you are stuck with a swp file in the data directory that is owned by root. The operating system user postgres, who reads the files in the data directory to send them to pg_basebackup, cannot read that file and throws the appropriate error message.

Solution: remove the offending file from the data directory, and everything will work again.


How to interpret timestamps stored as 10-digit negative integers?

Per Your comment the only place the values show as time stamps is in the application. You don't have access to the applications code.

The only possible solution, is to get a handful of actual time values shown in the application and relate them to values in the field, than figure how the difference is calculated. Then you will need to recalculate the values and add them to your database.

I strongly suggest that you do not replace the source data.  Possibly add columns to the table with the correctly formatted dates.


Is it possible to restore mongodb after db.dropDatabase() by replacing the contents of folder /data/db with older backup?

Yes and no. Yes, you can do this and your DB is at same state what it was when you took that backup especially if you used journaling. This means of course that everything that you did (changes to the DB) after backup disappears.
No part is more like that if you do your backup this way,
you should use filesystem snapshots or you should other way prevent disk writing during the copy process.


Insert Data in seconds between 30 minutes time interval

One way to achieve this involves creating a table of 48 daily half-hour period start times:

CREATE TABLE #HalfHours
(
    IntervalFrom time(3) PRIMARY KEY
);

INSERT #HalfHours
    (IntervalFrom)
SELECT
    DATEADD
    (
        MINUTE,
        30 * (SV.number - 1),
        CONVERT(time(3), '00:00:00.000')
    )
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 48;

We can then use a query like the following:

-- The specified times
DECLARE
    @Start time(3) = '08:05:13',
    @End time(3) = '12:09:49';

SELECT
    HH.IntervalFrom,
    IntervalTo = CA.IntervalEnd,
    Elapsed =
        CASE
            -- Current period fully covered
            WHEN @Start <= HH.IntervalFrom AND @End >= CA.IntervalEnd
                THEN 1800
            -- @Start within current period, @End not
            WHEN @Start > HH.IntervalFrom AND @End >= CA.IntervalEnd
                THEN 1800 - DATEDIFF(SECOND, HH.IntervalFrom, @Start)
            -- @End within current period, @Start not
            WHEN @Start <= HH.IntervalFrom AND @End < CA.IntervalEnd
                THEN DATEDIFF(SECOND, HH.IntervalFrom, @End)
            -- @Start and @End both within current period
            ELSE DATEDIFF(SECOND, @Start, @End)
        END
FROM #HalfHours AS HH
CROSS APPLY (VALUES(DATEADD(MINUTE, 30, HH.IntervalFrom))) AS CA (IntervalEnd)
WHERE
    @Start < @End
    AND HH.IntervalFrom < @End
    AND HH.IntervalFrom >=
        CASE
            WHEN @Start < CONVERT(time(3), '00:00:30.000')
                THEN CONVERT(time(3), '00:00:00.000')
            ELSE DATEADD(MINUTE, -30, @Start)
        END;

This can be generalized quite easily in case the input is more than a single pair of start/end times.


How can I write queries as fast as possible in MySQL?

I have used HeidiSQL for a while and it have very useful profiling feature that allow to get detailed profile of query execution. Also EXPLAIN is the first tool you have to learn.

Less obvious is that you have to place your databases on the filesystem with noatime option. Each time some software read/write from/to the disk, another implicit write(s) performed - to update "last access time" for accessed files. When noatime active that time is not updated and overall iops is halved.


In this case you can use GROUP BY to get distinct column1 values, and instead of convert the date to text you can use EXTRACT function for this purpose.

select
    colum1,
    count(*) as number_of_rows
from
    your_table
where
    extract(year from date) = 2014
group by
    column1;


Referencing database programmatically via T-SQL

Dynamic SQL comes in handy for these types of administrative tasks.  Here is a snippet from a stored procedure I wrote that not only gets the defragmentation levels, but also generates the code to do the defragmentation:

select @SQL =
'
select getdate(),
       ''' + @@ServerName + ''',
       ''' + @DatabaseName + ''',
       so.Name,
       si.Name,
       db_id(''' + @DatabaseName + '''),
       ips.object_id,
       ips.index_id,
       ips.index_type_desc,
       ips.alloc_unit_type_desc,
       ips.index_depth,
       ips.avg_fragmentation_in_percent,
       ips.fragment_count,
       avg_fragment_size_in_pages,
       ips.page_count,
       ips.record_count,
       case
         when ips.index_id = 0 then ''alter table [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
         else ''alter index '' + si.name + '' on [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
       end
  from sys.dm_db_index_physical_stats(db_id(''' + @DatabaseName + '''),null,null,null, ''' + @SampleMode + ''') ips
  join [' + @DatabaseName + '].sys.objects so  on so.object_id = ips.object_id
  join [' + @DatabaseName + '].sys.schemas ss  on ss.schema_id = so.schema_id
  join [' + @DatabaseName + '].sys.indexes si  on si.object_id = ips.object_id
                      and si.index_id  = ips.index_id
order by so.Name, ips.index_id
'

exec (@SQL)


How do I get the difference in minutes from 2 timestamp columns?

select
  round(
    (cast(current_timestamp as date) - cast(<other_timestamp> as date))
    * 24 * 60
  ) as diff_minutes
from <some_table>;

This is what I used to calculate the difference between the current timestamp and a heart beat table entry for latency monitoring.


SQL Server instance level action tracking

Actions happening at the instance level can be monitored by one of the following:

SQL Server Audit feature

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15

DATABASE_CHANGE_GROUP - who created database
SERVER_PERMISSION_CHANGE_GROUP - who gave server-level permissions to particular login
DATABASE_PERMISSION_CHANGE_GROUP - who gave database-level permissions to particular user
... etc

Extended Events feature

Provides some additional benefits and is highly configurable, allows to track 1000+ different kind of events and will cover most your needs

DDL and Logon triggers


In addition to @tombom's suggestions, creating an index on (user_id, post_id) instead of (or in addition, but the less indexes the better) separate indexes on user_id and post_id will simplify the query, probably getting rid of the filesort and temporary table, plus giving you the benefits of a covering index.

This will probably lower the query execution significantly if you have a large enough buffer pool and the query is relatively frequent.

If after doing that, the query is still slow, you will need to do (pre)caching in order to speed up the query execution.


Merging the sysdb database (a dependency due to conversion from Oracle) into my SQL Server 2014 Express Database

In SQL Server, you can take advantage of the SCHEMA and SYNONYM, the former which are similar to Oracle's User (hence the sysdb database) and the latter lets you hide the actual object name from your application. See the documentation for SCHEMA and SYNONYM.

1) On your target DB, create the sysdb schema:

USE [TargetDB]
GO
CREATE SCHEMA [sysdb] AUTHORIZATION [dbo]

2) In your nonproduction environment, merge all the relevant procedures, tables, and any required object into this schema.

You can either use the SQL Server Import and Export Data utility or script it out yourself.

3) If you are unable to specify a schema in your application, create SYNONYMS for each object you require:

USE [TargetDB]
GO
CREATE SYNONYM dbo.usp_myproc FOR [TargetDB].sysdb.usp_myproc
GO
GRANT EXECUTE ON dbo.usp_myproc TO [USER]
GO

Qualification: you can only specify the current Database or tempdb (if the object is a temp table) and There might be some issues if there are duplicate objects in each schema, but otherwise this will work just fine. :)


Cannot select features to install from SQL Server 2005 SP4 installation process

You are running SQL Server build 9.00.5294.00 that is more current than SP4 which is 9.00.5000. Your server has already been updated. (http://sqlserverbuilds.blogspot.com/)
(Build 7601: Service Pack 1) is the version of the operating system.


Table size is not changed after new column is added

You've added a new column, but SQL Server - if the default value is a runtime constant - will not update the existing pages that hold the table's data, it will be only a metadata change and that's the reason you see no increase in the space used.
For all intents and purposes (as Aaron mentioned in a comment), the values are there - and a SELECT will reveal them.
Think of it as SQL-Server black magic. It manages to store information in 0 bytes. Remus Rusanu has a nice blog post about this optimization and more details about what (magic) happens in the background and when you subsequently update the column:
Online non-NULL with values column add in SQL Server 2012
The basic info about it, in MSDN docs, ALTER TABLE:

Adding NOT NULL Columns as an Online Operation
Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and  the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD column syntax. A runtime constant is an expression that produces the same value at runtime for each row in the table regardless of its determinism. For example, the constant expression 'My temporary data', or the system function GETUTCDATETIME() are runtime constants. In contrast, the functions NEWID() or NEWSEQUENTIALID() are not runtime constants because a unique value is produced for each row in the table.


Comparing two queries in SQL Server 2012

I love your approach to careful consideration to query tuning and reviewing options and plans. I wish more developers did this. One caution would be - always test with a lot of rows, looking at the logical reads, this is a smallish table. Try and generate a sample load and run the query again. One small issue - in your top query you are not asking for an order by, in your bottom query you are. You should compare and contrast them each with ordering.

I just quickly created a SalesOrders table with 200,000 sales orders in it - still not huge by any stretch of the imagination. And ran the queries with the ORDER BY in each. I also played with indexes a bit.

With no clustered index on OrderID, just a non-clustered index on CustID The second query outperformed. Especially with the order by included in each. There was twice as many reads on the first query than the second query, and the cost percentages were 67% / 33% between the queries.

With a clustered index on OrderID and a non-clustered index just on CustID They performed in a similar speed and the exact same number of reads.

So I would suggest you increase the number of rows and do some more testing. But my final analysis on your queries -

You may find them behaving more similarly than you realize when you increase the rows, so keep that caveat in mind and test that way.

If all you ever want to return is the maximum OrderID for each Customer, and you want to determine that by the OrderID being the greatest OrderID then the second query out of these two is the best way to go from my mindset - it is a bit simpler and while ever so slightly more expensive based on subtree cost it is a quicker and easier to decipher statement. If you intend on adding other columns into your result set someday? Then the first query allows you do to do that.

Updated:
One of your comments under your question was:

  Please keep in mind, that finding the best query in this question is a
  means of refining the techniques used for comparing them.

But best takeaway for doing that- test with more data - always makes sure you have data consistent with production and expected future production. Query plans start looking data when you give more rows to the tables, and try and keep the distribution what you'd expect in production. And pay attention to things like including Order By or not, here I don't think it makes a terrible bit of difference in the end, but still worth digging into.

Your approach of comparing this level of detail and data is a good one. Subtree costs are arbitrary and meaningless mostly, but still worth at least looking at for comparison between edits/changes or even between queries. Looking at the time statistics and the IO are quite important, as is looking at the plan for anything that feels out of place for the size of the data you are working with and what you are trying to do.


"login failed for user " on SQL Azure from azure hosted web application

The problem turned out to be in the connection strings. There are some things you need to consider.

First of all there is an option in the publish wizard in Visual Studio in the Settings tab called Use this connection string at runtime (update destination web.config), that for some reason, checked or not, does not seem to work for me. But this may be an option in the first place.

Another place were a connection string is set is in Azure management portal. In your web application settings, in connection strings, you can check which connection string is effectively used by your application. In my case, regardless of the publish wizard settings I used, this connection string was wrong. I just deleted and created it again using my database username and password and everything works again.


Deploy SSRS 2012 report to a distant Sharepoint 2010 server

Problem solved.

I think it came from the absence of an Active directory to manage users. With it, users are defined precisely and the installation runs perfectly.


Tail log backup query in log shipping reversal

If I had to make a guess your database is just fine, but you never created the server principals (logins) associated with the database principals (users) in your database.  It's deprecated but try this and see if you get a result.

EXEC sp_change_users_login 'report'

You can also try this piece of code:

USE dbname;
SELECT logins.name as logins, users.name as users, users.type_desc
FROM sys.database_principals users
LEFT OUTER JOIN sys.server_principals logins
    ON users.sid = logins.sid
WHERE users.type IN ('U','S','G')

If there are any NULLs in the logins column then that's probably your problem.  Create the login (if it's a SQL ID then make sure you specify the correct SID).


Backups in SQL Server don't work that way, they aren't just copies of the database files in another location but instead they are backups of the database content stored in a backup file with a different structure than the database files (you could make a backup of just a database file but even in that case the backup format would be different).

To perform a backup the most basic way is doing something like this:

BACKUP DATABASE DBName TO DISK='C:\Backup directory\DB_backup.bak' WITH FORMAT

The backup files can't be used directly by SQL Server, you need to restore it on a database first (either overwriting an existing database or creating a new one):

RESTORE DATABASE DBName FROM DISK='C:\Backup directory\DB_backup.bak' WITH REPLACE

You could need additional backup/restore parameters depending of the DB properties and the exact operation you are doing (also you can perform and restore backups from SQL Server Management Studio)


Are there any DMVs where linked server info is maintained
  select * from sys.servers gives info about what are the linked servers configured .please see below msdn link for more details  on usage
https://msdn.microsoft.com/en-IN/library/ms178530.aspx

Is there any post/document which gives us more information on how to troubleshoot Linked server issues.
This seems like a generalized question,depending on error message,my basic analysis would start from access issues ,sql error log...If you want to troubleshoot performance issues with linked servers there are many links in SO as well as online to start with.

http://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/

Is there a way to find from SQL(2008) Side, how many linked servers are being used and how many are not.. as application team want us to remove the unnecessary linked servers.

This has been already answered here by Mr denny.
https://dba.stackexchange.com/a/5520/31995


I don't have 2008 to look at but do you have access to the RDL files that were published to the server?  I can't recall if those are plain old XML files, but I think if you have those, it's probably a convenient way - you could parse them with Linq to XML, or XSLT.

The schema is documented at:

http://msdn.microsoft.com/library/dd297486%28SQL.100%29.aspx


Add Secondary to Replica Set in MongoDB Windows

As per the error message says, you have added the Primary node of the replica set as localhost. The rule is all nodes of the replica set should be localhost or all should be hostname/ip address.

If you run rs.status() from primary, you will see the primary replica set member as localhost. So when you try to add a secondary with rs.add("192.168.56.136:27017") the system will throw an error.

How to correct this?

conf = rs.conf()

update the hostname of primary member in conf variable

then run

rs.reConfig(conf)

Now add the secondary member.


Add linked server from SQL-Server to MySQL: "Data source name not found" (Error:7303)

The ODBC Connection needs to be a System DSN. The connection is made as the service account, not you, and so the service account doesn't know about your User DSN.

From the article:

  Select the System DSN tab to configure a data source for the entire
  system.


MS Access + SQL Server for 1160 users - Is it worth it? (Please Help!)

This sounds like an enormous project.

My advice is to go back and look again for an open source, off-the-shelf product that meets your main needs.

It is difficult to believe that your school is alone in its needs and that no-one else has similar requirements.

Even if you the system does not do everything you require, as long as it is built in an open way, with a sound underlying data model then you will be able to extend it with small additional components.

I would also be careful to manage expectations and let people know that the system will not meet all their needs to start with.

good luck!


Block size is for Database or Tablespace?

db_block_size is the default block size of the database, but it does not necessarily mean all your tablespaces have the same block size, you can have tablespaces with different block sizes.

You need to define seperate cache areas for tablespaces with non-default block size. For example, if your db_block_size is 8192, and you want to create a tablespace with block size of 16384, first you need to specify a size for the "16K" cache with the parameter db_16k_cache_size, and after that you can create a tablespace with 16384 block size.


Failover clustering works to provide constant service in the event of a failure (defining a failure as an "abnormal termination of the previously active application, server, system, or network" - http://en.wikipedia.org/wiki/Failover).  Manual manipulation (start, restart, pause, and stop) upon the service being clustered (SQL Server, File Services, etc.) does not qualify as an abnormal termination of the service.

If I recall, at least in Windows Server 2008, you can simulate a failover test case in the Failover Management Console under Administration Tools.  Check this out for a list of testing methods: http://blogs.technet.com/b/vipulshah/archive/2009/06/17/failover-cluster-testing-methods.aspx


Preferred design to avoid circular/multiple update paths

The solution I've seen has been to remove the foreign key constraint, and to maintain relational integrity via triggers.

On the foreign (or "child") table, you need INSERT and UPDATE triggers. if the "foreign key" column(s) are inserted/updated, then you have to make sure the value(s) exists in the primary key of the "parent" table.

On the "parent" table, on UPDATE of the primary key column(s), you have to locate all rows in any "child" tables (FarmEquipment and Fields would both be "children" of Farmers) with the old value, and update them to the new value.

On the "parent" table, on DELETE, you have to check the "child" tables to see if any rows use the primary key(s) in question, and delete them all.

Having worked with a vendor-supplied system that worked this way, I can't recommend it.

As noted above, you must maintain three chunks of code for each foreign key relationship, across a minimum of two triggers (let's leave self-referential situations out of the discussion, for the moment at least).
A "parent" table that tied to multiple "child" tables would need a check for each "child" table. While the code should be very similar for each "child" table, that can actually make modifications more difficult, as it would be easy to make a change meant to affect FarmEquipment to Fields by mistake. (In the real-world example I mentioned, there was at least one parent table with at least 20 "FK" relationships - sometimes having multiple links in the same child table, to different fields.
Documentation of the relationship is no longer built-in. While you can't (to the best of my knowledge) right-click a table and find all foreign key relationships that point to it, it's easy to find a query to retrieve this information from SQL Server's structural tables. When the relationship is maintained via triggers, you pretty much have to go through the triggers manually to identify all the relationships, and the precise definition (the "parent" table's DELETE trigger is where you can determine if the relationship is meant to be CASCADE, NO ACTION, SET NULL, or SET DEFAULT - with foreign key constraints, this can be seen from the "child" table).

There are workarounds (the trigger code does exist in a queryable form), but they require that the developers follow strict rules regarding naming conventions, the construction of the query, and possibly even the formatting of the code. Even if one person is maintaining the code, and is using a template to set everything up, keeping everything exactly so as changes are made and bugs are found and fixed becomes much more difficult.

The system I worked with did work, with this method, for more than 10 years; they'd started their app in an environment that didn't have effective foreign keys. But even they had gotten to the point where they were using foreign keys for new tables, and starting to put them in place on existing tables where possible. It's just that working with it wasn't easy.

I suspect this wouldn't meet your criteria; however, as it is a viable solution, I thought it was at least worth covering.


Does Visual Studio 2017 Community (.NET Framework 4.6.2) support Oracle 8i

I don't have an 8i database handy to test. If you care about your data it'd be wise to upgrade to a supported version of Oracle ASAP. The last version of Oracle client that can connect to Oracle 8i is an Oracle 10 client (and you need to be on Oracle 8.1.7 server-side).

If you can find an Oracle 10 client install (either full-fat or the instant client), then you might be able to get it to work. - Phil

The article referenced in the question is rather bad. Listed prerequisites are wrong/outdated (was even in 2015), it does not properly use the tnsnames.ora file, and does not consider 32/64-Bit topic.

See my answer to Does ASP.NET MVC5 support Oracle 8i on Stack Overflow. - wernfried-domscheit


The logging collector tries to capture stderr and write it to a file.  But it looks like systemd wins the redirecting war, and sends the output to the journal.  You can check the systemd journal with:

journalctl -u postgresql

The systemd configuration is in usr/lib/systemd/system/postgresql.service. I've tried various settings for StandardError, but none seems to allow the regular Postgres logging to do its work.


MySQL : count occurences on different columns in a same query

SELECT user, MAX(nb) nb, MAX(nb_upgd) nb_upgd
FROM ( SELECT user,
              COUNT(user) nb,
              0 nb_upgd
       FROM t
       GROUP BY user
     UNION ALL
       SELECT upgrade,
              0,
              COUNT(upgrade)
       FROM t
       GROUP BY upgrade
     ) total
WHERE user IS NOT NULL
GROUP BY user;

fiddle


PostgreSQL - How can I manage WAL archiving for different databases on a server?

You can't split WAL archiving out so one database goes in one WAL archive stream and other DBs go in another WAL archive stream. A single write-ahead log is shared by all databases in a PostgreSQL server ("cluster" in PostgreSQL parlance). All databases managed by a postmaster have the same WAL. This is a sometimes unfortunate limitation of the current replication/PITR/archiving design and is true for the in-development-at-time-of-writing 9.3 and all and prior releases.

If you want to separate the WAL archiving, you have to separate the databases into different clusters. You can do this by running more than one instance of PostgreSQL on a server; they simply have to have different data directories and different listening ports. Some packagings of PostgreSQL provide built-in tools to do this, like the pg_wrapper tool used on Debian and Ubuntu, which has pg_createcluster. Alternately, you can always just use initdb to create a cluster and pg_ctl to start the cluster after editing the new cluster's postgresql.conf to set an appropriate port.

Tools like pgbouncer may be used, if desired, to multiplex the multiple clusters transparently, so pgbouncer listens on port 5432 (the default Pg port) and decides which server to send the connection to based on which database it asks for. Generally it's easier to just specify a port in the connection setup of the applications that use each database, though.

On PostgreSQL 9.2 and below you must allocate each server an exclusive, non-overlapping shared memory region for its shared_buffers. One server cannot make use of the other server's shared_buffers space when the other server is not busy. That means you are wasting some system resources. PostgreSQL 9.3 improves this by allocating shared_buffers out of anonymous memory, giving the OS more freedom about how it manages it.

If you don't want to run multiple clusters, you can use alternative replication or backup solutions based on out-of-tree tools like Slony-I, Bucardo, etc to manage your data replication and archival.


You may be able to stop the slave, create the routines, and restart the slave without a problem.

MySQL replication relies on the master and the slave servers being identical, but it does not matter how they came to be identical.  You could theoretically clone a master server by hand, one table and row at a time -- as long as the slave was identical (structures, data, AUTO_INCREMENT values, etc.) to what appeared on the master at the instant in time represented by the binlog coordinates where you started replication, everything would be fine.  Not practical, but technically no different than what we accomplish with mysqldump.

Now, having said that the servers must be identical, replication will not stop or detect a problem if the servers are not identical as long as none of the non-identical elements are referenced in any way by replication events.

If a table is altered, dropped, etc., on the slave, no replication error will occur as long as no inserts, updates, or deletes to that table are done on the master.

The extension of this concept is that if the routines (which means stored procedures and stored functions -- scheduled events and triggers are not "routines") have not been referenced by replication events, their absence may not yet have caused any problems.

If your master is configured with a binlog_format of MIXED or ROW, then it's entirely possible that no replication events may have been sent across that depended on those routines being present, even if your application uses them extensively.  This is because whenever the master chooses row-based logging for a replication event (as it is very likely to do most of the time in MIXED mode) the procs and functions on the slave don't execute -- instead, the row changes that occurred on the master as a result of the procs and functions are replicated row-by-row to the tables on the slave.  In MIXED mode, this is a decision made by the optimizer on the master when the query is executed.

The same thing is true of triggers.  In my environment, it's extremely rare for a trigger to fire on a slave, since the vast majority of things replicate in ROW format (with configuration set to MIXED).

On the other hand, if a query did get logged in STATEMENT mode that needed one of these routines to be present, replication should have stopped with an error when the procedure or function referenced in the statement was not found.  As long as you haven't done anything like set global sql_slave_skip_counter = 1 or configured your slave to ignore errors (don't), then you should be able to define the routines now, and START SLAVE;.  The failed query should re-execute.

If you have a lot of routines on the master, the closest thing I can think of for getting them would be:

mysqldump --routines --no-data --single-transaction --all-databases --skip-add-drop-table | perl -pe 's/^CREATE\sTABLE\s/CREATE TABLE IF NOT EXISTS /g' > routines.sql

This will generate a dump file with all of your table definitions and routines, but it will not have the DROP TABLE before each CREATE TABLE and the CREATE TABLE statements will be changed to CREATE TABLE IF NOT EXISTS which will skip creating the tables and instead throw warnings that the table was already there... and it will not have row data.  I'm not aware of a way to get mysqldump to skip the tables entirely, though that might be a nice feature (if not a bit ironic).

Open this file with a text editor and review it before applying it to your server.

As with any replication setup, never confuse the lack of errors with total consistency.  Verify the consistency of all your data on an ongoing basis, especially if anyone has write privileges on the slave (which they shouldn't).   A lack of errors only means no replication events have touched anything that resulted in a different outcome than what occurred on the master.


Blocking drop table for specific schema using trigger

TG_TABLE_SCHEMA is not set in event triggers.

You'll have to call pg_event_trigger_dropped_objects() to get information about the dropped objects. Among the result columns is schema_name, which contains the schema name of the affected object.


Index on Primary Key: is there any reason for that?

No, there is clearly no good reason.

Most probably the index originally contained another column that has been dropped - at least, this is the only explanation from the top of my mind.


What is the MySQL Mutex called "srv_sys_mutex"?

It's a system mutex. It's used to protect creating new threads, destroying them.
I guess, thread_cache_size may ease contention on it.


Count in an other table and show only theese records in query which has more than they used in other table

If I get it right something like:

select c.code_id
from codes c
join used u
    on c.code_id = u.code_id
group by c.code_id, c.amount
having count(1) < c.amount;

should do (Not sure why MySQL requires c.amount in the group by clause for it to be used in having)

If you want to show the remaining amount:

select c.code_id, c.amount - count(1) as remains
from codes c
join used u
    on c.code_id = u.code_id
group by c.code_id, c.amount
having count(1) < c.amount

I assumed here that amount is functionally dependent of code_id. Another option is to add a "fake" aggregate to c.amount:

select c.code_id, max(c.amount) - count(1) as remains
from codes c
join used u
    on c.code_id = u.code_id
group by c.code_id
having count(1) < max(c.amount);

There is only one unique amount per code_id so the purpose of max is to "legalize" the group by.

SQL92 demanded that all columns in the select beside aggregates where included in the group by clause. SQL99 loosened this restriction and only requires that all columns in the select clause is functionally dependent of the group by.

I haven't tested it my self, but I hear that the latest version of MySQL (or was it MariaDB?) implements it this way. I've also heard that recent versions of postgreSQL does it that way.


Remove String from column based on character

SELECT SUBSTRING_INDEX(name, '(', 1) FROM test;

UPDATE test
    SET name = TRIM(SUBSTRING_INDEX(name, '(', 1));

(assuming you want to get rid of the space, too)


Is it a good idea to add a reserved field to a large MySQL table?

I echo the "bad form" comment of @JohnM - design the thing properly, and if you have new requirements (or your design isn't perfect first time - unlikely I know :-) ), then choose to add new fields. Use JSON if it suits your clearly demonstrated requirements, otherwise stick with "normal" field types.

I've seen too many systems where these "spare fields" get used for anything and everything and it ultimately becomes an undocumented, alphabet soup of an unmaintainable mess.

"Hey Jimmy, what does field XYZ__001 do?"

"I don't know, ask Billy"

"But Billy's on holiday in the Algarve"

"Ooops!'


Way to add referencing and referenced entities of a specific entity in E-R Diagram MYSQL

SchemaCrawler is a free tool that can create database diagrams from the command-line. You can search for tables and columns that you want to include in your diagram using regular expressions. Once you have selected the tables to include, you can also include any number of levels of referenced tables in your diagram. (That is, tables related by foreign keys.) SchemaCrawler can also infer relationships even if foreign keys are not defined, if popular naming conventions are followed.

You meantion that it is tedious to manage E-R diagrams for 400 tables. Yes, it is. I would say, maintain many SchemaCrawler search expressions, which will let you generate many E-R diagrams, for example one for accounts, one for sales, and so on. The added benefit is that these diagrams never go stale - they can be regenerated instantly whenever the schema changes.

Sualeh Fatehi, SchemaCrawler


(SSMS) How do you prevent an open tab from querying a DB after disconnecting the server

The Object Explorer pane and the Query pane are independent. You can have the Object Explorer connected without having a Query connected, and vice versa.

The menu option you probably want is Query -> Connection -> Disconnect.

HTH


How do I group the data in the following format?

To display the given format, you can use the following queries instead:

  For MySQL, use the following:

  SELECT QuaterName, COUNT(OrderID) FROM
    (
        SELECT QUARTER(OrderDate) QuaterName, OrderID from bab_orders
    ) T1 group by QuaterName;

  for Oracle use the following:

SELECT QuaterName, COUNT(OrderID) FROM
(
    SELECT TO_CHAR(TO_DATE(OrderDate, 'dd.mm.yyyy'), 'Q')
              QuaterName, OrderID from bab_orders
) T1 group by QuaterName;

  to show always the Quarters even if you don't have a data, execute the
  following query:

SELECT QRTR, COUNT(OrderID) TheCount FROM
(
  SELECT QUARTER('2014-01-01')  QRTR
  union
  SELECT QUARTER('2014-04-01')  QRTR
  union
  SELECT QUARTER('2014-08-01')  QRTR
  union
  SELECT QUARTER('2014-12-01')  QRTR
)  QRTR_T
LEFT JOIN
(
   SELECT QUARTER(OrderDate) Quater, OrderID from bab_orders
) ORDERS
on Quater = QRTR
group by QRTR


mysqlcheck to optimize innodb tables -- performance/scheduling

Don't OPTIMIZE.

Seriously, there is almost never a need to OPTIMIZE an InnoDB table.  90GB would take hours, maybe days, depending on tunables, disk speed, RAID, etc, etc.

OPTIMIZE may free up some of the free space caused by DELETEs and UPDATEs.  But, normally, InnoDB takes care of itself, rarely leaving more than 50% of the disk footprint "free".  Normal random inserts (in data or secondary indexes) lead to an average of 31% "free" -- that's a mathematical fact for BTrees.

If your table is PARTITIONed (and you must OPTIMIZE), do it one partition at a time, and do not use OPTIMIZE.  There is a bug wherein it will do the whole table in spite of what you say.  REORGANIZE PARTITION can do a single partition.

Note that non-tiny InnoDB tables normally always say 4MB-7MB of "Data_free" in SHOW TABLE STATUS.  You can't get rid if that 'wasted' space.

OPTIMIZE will not rearrange the data or the secondary indexes -- the rows will continue to be in exactly the same order.  (This is unlike MyISAM, which has multiple reasons for doing OPTIMIZE.)

Caution:  Most ALTERs, OPTIMIZEs, etc, require enough spare disk space for a complete new copy of that table -- 90GB more.

If you would like to present more details on why you want to OPTIMIZE, I will see if there is a good reason for it.  Please provide SHOW CREATE TABLE and SHOW TABLE STATUS.  And maybe even a technique to avoid it without downtime.


Using a sequence created in the same script in Oracle

Something like this:

declare
   counter integer;
begin
   select count(*)
      into counter
   from user_sequences
   where sequence_name = 'MY_SEQUENCE';

   if (counter = 0) then
      execute immediate 'create sequence my_sequence';
   end if;
end;
/

insert into my_table (id, some_col)
values
(my_sequence.nextval, 42)
/

EDIT: If the insert is in the same block as the create then it will need to be done as an execute immediate as well else the Sequence won't be recognised. If they're in separate blocks then it should work fine.


counting the number of a specific word in one SQL column

Use:

select   vote, count(*)
from     voteCompost
group by vote;

Now you get the number of rows per vote value.


What is the name of this type of query, and what is an efficient example?

The problem you are solving is called Relational Division with two variations, the "Division with Remainder" and "Exact division" (which fits your description).

See also this article: Divided We Stand: The SQL of Relational Division

This question at StackOVerflow: How to filter SQL results in a has-many-through relation has a few ways to solve it and benchmarks for Postgres but it's not for the exact variation, only for the "Division with Remainder". The queries will be similar but an extra check/condition has to be added for the exact variation.

One way (of the too many) to solve this would be to self-join the table as many times as the "products" and then an extra check so only packages with these exactly products are kept):

SELECT m1.package_id
FROM marketing_package_product AS m1
  JOIN marketing_package_product AS m2
    ON m2.package_id = m1.package_id
WHERE m1.product_id = 1
  AND m2.product_id = 2
  AND NOT EXISTS
      ( SELECT *
        FROM marketing_package_product AS x
        WHERE x.package_id = m1.package_id
          AND x.product_id NOT IN (1,2)
      ) ;

Another would be to GROUP BY and then two HAVING conditions, one for the division and the other for the "exactness":

SELECT m.package_id
FROM marketing_package_product AS m
GROUP BY m.package_id
HAVING COUNT(CASE WHEN m.product_id IN (1,2) THEN 1 END) = @n
   AND COUNT(*) = @n ;

where @n would be 2 in this case, the number of products in your check.

Test at SQL-Fiddle.


Best Practice in storage allocation for compressed and partitioned tables

Rebuild the index to write out all pages freshly in the optimal way.

If you want to absolutely minimize space usage, specify ONLINE = OFF. Online operations add a small amount of per-row space overhead.

MAXDOP = 1 can be good to reduce fragmentation. Not sure if that can help with space usage, though. I can't think of anything that a DOP of 1 might save.

If the target file of the rebuild already contains fragmented freespace the newly built index/partition can turn out to be fragmented immediately. Consider building into an empty target filegroup that is pre-sized to approximately the right size.


I think that is the new way to express home based on the IPv6 protocol. It's new to me as well.

By this, my guess is your WAMP installed MySQL 5.5.

Just remember two things

There is no place like 127.0.0.1
There is no place like ::1


How to change query_cache_type to 2 in AWS RDS?

Have you attached the parameter group to the RDS instance properly? I have just checked on my installation, and things are working as expected. (Note, I do have my cache size set to zero, as for our workload it is not beneficial to enable it)

Parameter Group Settings

MySQL Query Cache

mysql> show global variables like 'query_cache%';
+------------------------------+--------+
| Variable_name                | Value  |
+------------------------------+--------+
| query_cache_limit            | 0      |
| query_cache_min_res_unit     | 512    |
| query_cache_size             | 0      |
| query_cache_type             | DEMAND |
| query_cache_wlock_invalidate | OFF    |
+------------------------------+--------+
5 rows in set (0.01 sec)

Parameter Group Assigned to RDS Instance

Regards,
Dave


TRUNCATE should be used only when it is certain that no other session is using the table at the same time. Otherwise DELETE should be used. It's slower (if the number of rows is significant), but it's designed to support concurrent access within the rules of the current isolation mode.

The doc says:

  TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates
  on, which blocks all other concurrent operations on the table. When
  RESTART IDENTITY is specified, any sequences that are to be restarted
  are likewise locked exclusively. If concurrent access to a table is
  required, then the DELETE command should be used instead.

If DELETE's slowness is not acceptable, to avoid the deadlock with 100% certainty, you'd need to put the entire sequence into a critical section, both in the unit test and in the cron job. A critical section is entered by requesting an explicit lock before any other operation, such as in:

BEGIN
 SELECT pg_advisory_xact_lock(1); -- (1 is arbitrary: choose any value reserved for this purpose by your apps)
 TRUNCATE ...;
 TRUNCATE ...;
 ...processing...
ROLLBACK;


Should I assimilate all address lines to a single line?

Separate fields. Every time. String operations are costly, especially cutting them up into bits.


Can I sync an existing database using a .dmp file?

Next time, instead of "deleting everything", you can tell imp to ignore table creation error and still load the data by specifying the IGNORE=y option:

imp user/password file=MyExportFile.dmp IGNORE=y

There are few other considerations that you should make when importing into an existing schema, as outlined in the manual. Specifically, you may need to disable referential integrity constraints if they exists, otherwise some data may be rejected.


If I understood the question right, you need something like

SELECT [id_value] as town_id, cat
FROM
(
    SELECT cat1 AS cat, id_value AS town_id FROM table1 WHERE town_id = [id_value]
    UNION
    SELECT cat2, id_value FROM table1 WHERE town_id = [id_value]
    UNION
    SELECT cat3, id_value FROM table1 WHERE town_id = [id_value]
)a

[id_value] - chosen town_id. UNION by default is distinct; result of union as far as I remember will be sorted automatically in most implementations, but you may add ORDER BY a.cat to make it clear...

Taking the OP's comment context:

SELECT town_id AS [16], cat FROM
(
  SELECT Category_id AS cat, id_value AS town_id
   FROM establishments AS estab1 WHERE town_id = 16
  UNION SELECT Category2_id, id_value
   FROM establishments AS etsab2 WHERE town_id = 16
  UNION SELECT Category3_id, id_value
   FROM establishments AS estab3 WHERE town_id = 16
) AS a;


It is very easy to connect, just configure ODBC. However, when you install Firebird Server, you must select the option to copy files to the Windows directory.


FLUSH LOGS gracefully does most of what you want.  It stops writing to a log, renames the file and starts a new one.  After that, you can deal with copying/moving/archiving/etc the files.

FLUSH LOGS can be run at any time without damaging anything or losing any log entries.

Caveat:  There are many different log files; FLUSH may not work the same way on each, especially in older versions of MySQL.

For those three logs, in most systems, the general log is the biggest, followed by slow, then error.  FLUSH also rotates the "binlog", but, again, that is "harmless".


Cannot start SqlLocalDB instance with my Windows account

Check the error log which usually under the folder %localappdata%\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0 will give you big hints.

Log back in as the administrator account (not an administrator), and start a command prompt with Run As Administrator. Then run this:

sqllocaldb share v11.0 MyInstance

You might want to explicitly add your Windows account as a sysadmin on this instance if that is a goal. So:

sqllocaldb start MyInstance

Then connect with SQLCMD, Management Studio, what have you, and run:

CREATE LOGIN [Domain\Username] FROM WINDOWS;
EXEC sp_addsrvrolemember N'Domain\Username', N'sysadmin';

Now log back in as your Windows account, and you should be able to start this instance using:

sqllocaldb start MyInstance

(It may already be started depending on your O/S and how you switched accounts.)

Then Domain\Username should be able to connect using SQLCMD, SSMS etc. using (localdb)\.\MyInstance.


mysql - adding alias columns as a new column

Try this:
select x.timest,
    max(case when x.devloc='outside' then x.value end) as outside,
    max(case when x.devloc='hvac_main_return' then x.value end) as hvac_main_return,
    max(case when x.devloc='hvac_main_supply' then x.value end) as hvac_main_supply,
    ((max(case when x.devloc='hvac_main_return' then x.value end)) -
      (max(case when x.devloc='hvac_main_supply' then x.value end))) AS diff
from sample x where date(timest) = curdate()
group by timest
order by timest desc;

If you want a more detailled answer, please provide some sample data as in the fiddle I've done here. Also, please provide your desired result here - always provide everything you include in any fiddle here also (linkrot).


Will certain Database Compatibility cause performance degradation?

All the compatibility level does is tell SQL Server to use the older version of the T-SQL language that was available in that version.  For example if within a SQL 2008 R2 server with the database running in SQL 2000 compatibility mode if you try and access some of the new syntax it won't work.

For example

select *
from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'limited')

will throw an error in a SQL 2000 compatibility mode database.

As for query execution, that won't be changed by the compatibility mode.


MongoDB shell version error (Server has startup warnings)

I don't believe there is a way to suppress only that warning, but you can always start your mongod instance with the --quiet flag.

  --quiet Runs the mongod instance in a quiet mode that attempts to limit the amount of output. This option suppresses:

  output from database commands, including drop, dropIndexes,
  diagLogging, validate, and clean. replication activity. connection
  accepted events. connection closed events.

  Reference - http://docs.mongodb.org/manual/reference/program/mongod/#cmdoption--quiet

So when you use this flag, you are actually disabling quite a lot of useful information. As you can see from the content of the warning, using the 32bit version will seriously limit the amount of data you will be able to store. I think you should consider upgrading to the 64bit version.


Is it necessary to run apply-log immediately after innobackupexec has run?

A quick 101 on Percona Xtrabackup:

Basically, what the tool does is copying the data live in an inconsistent way, but making sure it gets all the necessary changes that are happening at copy time, so it can revert it to a proper state. In other words, it does an "unclean full copy" and then a "controlled crash recovery" with a custom InnoDB instance. That is exactly why you need the two phases (the copy first and then the application of the log, where committed changes are reapplied and uncommitted changes are discarded).

That second part can be done on a different machine, without a database connection -that is why it is a separate command.

So you have two options: running the --apply-log as soon as the copy has ended or just before the restore.

Advantages of the first case: your copy is 100% ready to be restored in case of an emergency (just --copy-back it). You can delete the saved transaction log as it is not needed any more.
Its disadvantages: Once you have deleted the uncommitted changes, you cannot roll forward your full backup with following incrementals. Also, you will not be generating the extra information that may be needed for partial imports.

If you only intend to create in the future full backups and perform full restores, you can safely do it just after the copy finishes. If your apply-log phase is fast (or you do not care about the extra time and space of the transaction log) and you want to have extra flexibility (for partial/incremental backups), just wait until the start of the recovery.


How do I get the DDL used to create function based index?

You can use DBMS_METADATA package.

  The DBMS_METADATA package provides a way for you to retrieve metadata
  from the database dictionary as XML or creation DDL and to submit the
  XML to re-create the object.

For details: Oracle Documentation
DBMS_METADATA

SQL> create table fbi_tab(
  2  a number,
  3  b number,
  4  c number
  5  );

Table created.

SQL> create index idx on fbi_tab(a+b*(c-1),a,b);

Index created.

SQL> select dbms_metadata.get_ddl('INDEX','IDX','HR') from dual;

DBMS_METADATA.GET_DDL('INDEX','IDX','HR')
--------------------------------------------------------------------------------

  CREATE INDEX "HR"."IDX" ON "HR"."FBI_TAB" ("A"+"B"*("C"-1), "A", "B")
  PCTF

SQL>


AlwaysOn Shared network location change requrest

The shared network location in that wizard is only used in the wizard and only used as a temporary location to backup the databases to and restore from that location to any of the replicas that could host that database.

Apart from that, it has no use and is not used internally anywhere in availability groups.

You can easily change the value to whatever new value your storage team has given you, but you'll want to double check that all of the replicas have access to said shared location.


Instant File Initialization on Backup drive

If the archive location is part of same server and if you are performing restores to same server,then you will have IFI enabled.You can always enable Instant file initialization on server you are performing restores too ,I dont see an issue enabling it on prod server and not enabling it on test server.you can further check using below trace flags if IFI is enabled or not.

DBCC TRACEON(3004,-1);
DBCC TRACEON(3605,-1);


Need help achieving HAProxy active/passive configuration for mysql

The answer is to use the check backup option to second backend node in the conf file like so:

listen mysql-cluster
    bind 0.0.0.0:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server mysql-1 10.51.120.28:3306 check
    server mysql-2 10.51.123.25:3306 check backup


DBCC SHRINKFILE - Necessary to run more than once to achieve shrink?

There is likely not enough time passed between your truncates/deletes and the shrink operations. While I droned on and on and on about why you shouldn't shrink and what it means to have a too big log file in another answer, here's a situation where it makes sense to shrink.

I would suggest either building a pause in between the truncates and deletes or simply add a CHECKPOINT command within that database between the deletes and shrink. That will likely give you the desired results without the second shrink.


SQL Server CLE Encryption Implementation

Based on your requirements, I would actually recommend Transparent Data Encryption (TDE) instead of CLE.
TDE encrypts at the database level and therefore you don't have to worry about being compromised by storing the decryption process in a stored procedure. Rather, TDE works on the traditional private and asymmetric key pattern to prevent access to the unencrypted data without the proper key (which you can handle authenticating outside of the database to decouple the security concerns).
It's also better in my opinion because it doesn't force you to change your data types to VARBINARY which almost certainly could affect the performance of your queries. Rather it keeps everything as you designed under the hood (hence Transparent) when you access your database.


Storing/Retrieving Chinese/Unicode in PL/SQL

You can not store Chinese characters in a Western European characterset (WE8ISO8859P1).

Define the MANUFACTURER_ADDRESS_LINE column as NVARCHAR2 to use AL16UTF16 and insert the string as below (notice the N modifier):

INSERT INTO tc(MANUFACTURER, MANUFACTURER_ADDRESS_LINE )
SELECT 'Neal Chinese Test 06', N'统一通信系统）【功能性备注：集团电话' FROM DUAL;
commit;


Why does the query plan still sort a table despite having an index sorted on the columns?

PostgreSQL thinks the sort merge is faster. And in my hands on 9.1 it actually is faster than walking both indexes. You can try it for yourself by set enable_sort to off; and see what plan it gives, and how long that one takes to run.

Sorting is pretty efficient. Walking indexes is inefficient in 9.1 because it still has to visit the table to make sure the row is actually visible.  Indexes don't store visibility information. 9.2 introduced index-only scans, which gets around part of that problem.

Also, the index leaf pages are not necessarily logical order, so walking the index in logical order can lead to a lot of random IO.  (Of course, checking the table for visibility off of an index scan will as well.)

There have been a lot of improvements made since 9.1.


You are using plain text dump and that's why it break on those characters.
Try with custom format and use pg_restore for restoring the database.

check this tutorial

Be aware that you need to copy also user rights or recreate them on new machine related with the database.


Mongoimport monitoring successfully entered row/data

If your mongod uses journaling, follow local.oplog.rs collection, there are all inserts in the order.


Why is to_char left padding with spaces?

You can see that in a simpler test case here

SELECT '>' || to_char(1, '0') || '<';
 ?column?
----------
 > 1<
(1 row)

This is because, as @Abelisto said, the space is reserved for the sign glyph,

SELECT '>' || to_char(-1, '0') || '<';
 ?column?
----------
 >-1<
(1 row)

You can suppress the sign using FM, from the docs

Modifier Description Example
FM       prefix      fill mode (suppress leading zeroes and padding blanks) FM9999

So what you want is

SELECT '>' || to_char(1, 'FM099') || '<';

The zeros aren't suppressed when you demand them with FM0


Minimizing database size (Many small tables)

"Does minimizing the size of the database come at a cost of lower performance?"

Typically databases are IO limited, unless they are regularly recalculating reporting style queries. (in which case pre-calculated views can be added to again shift cpu to IO.)

Minimizing the size of the database typically minimizes the IO needed against the disk, as a higher % of all data can be in cache.

Compressed storage can also help here; if 1 disk iop can retrieve more rows, then that too can increase performance, without needing to logically re-arrange the data. (many compression schemes are cpu-efficient enough that it is possible to actually see a decrease in cpu usage, due to having to handle less data pages.)

So, in general, minimizing the size of the database does indeed increase performance, but always benchmark as there are many counter-examples to any generic statements like this one (note, the answer by RolandoMySQLDBA lists some downsides of compression concerning memory pressure).

some of the tradeoffs due to data compression are listed at http://dev.mysql.com/doc/refman/5.5/en/innodb-compression-internals.html#innodb-compression-internals-storage


LIKE search in bytea datatype (PostgreSQL)

The best way i found to make 'LIKE' search in PostgreSQL in columns with a lot of content was using tsvector columns.

One advantages is more fast than make 'like' searches and allow create index.

The boring part is create a trigger to replicate the content in a new column.

The article below shows how to use tsvector.

http://rachbelaid.com/postgres-full-text-search-is-good-enough/

Column  |       Type   | Collation |                 Default
---------+--------------+-----------+------------------------------------------
 id      | integer      |           | nextval('modlink_blob_id_seq'::regclass)
 content | bytea        |           |
 search  | tsvector     |           |

This is the SQL

SELECT COUNT(*) FROM "job" INNER JOIN "blob" ON ("job"."input_id" = "blob"."id")
WHERE blob.search @@ plainto_tsquery('csv');

A observation: I use text columns instead bytea.


Oracle Database Client 11g R2 Installation Types, which components?

The public link below gives an overview of the four installation type options for the 11gR2 client:

http://docs.oracle.com/cd/E11882_01/install.112/e24322/install_overview.htm#LACLI1245

  Instant Client: Enables you to install only the shared libraries required by Oracle Call Interface (OCI), Oracle C++ Call Interface (OCCI), Pro*C, or Java database connectivity (JDBC) OCI applications. This installation type requires much less disk space than the other Oracle Client installation types.
  Administrator: Enables applications to connect to an Oracle Database instance on the local system or on a remote system. It also provides tools that enable you to administer Oracle Database.
  Runtime: Enables applications to connect to an Oracle Database instance on the local system or on a remote system.
  Custom: Enables you to select individual components from the list of Administrator and Runtime components.

A complete list of which individual components are installed by which Installation Type can be found on My Oracle Support, Doc ID [1397346.1].  It also includes the different components that are installed only on Microsoft Windows and not on other platforms.

Because My Oracle Support requires an Oracle account to login and access this information, I do not believe it is allowed to be posted on public forums.  However, if you already have a support account with Oracle, you can go to https://support.oracle.com to login and search for the Document ID posted above to reference the complete list of components included in each Installation Type as you requested.

EDIT:  Also, to answer your second question, yes, "Oracle Data Provider for .NET" is the ODP.NET component.


Which problems arise, declaring the size of all varchar parameters as max in stored proc?

Since your talking about the proc params:

CREATE PROCEDURE myProc
  @var1 varchar(30),
  @var2 varchar(max)
AS
...

If the parameters are defined as the correct length, SQL will throw an error when calling the procedure. If you always define as (max) then you'll have to manually handle passed parameters that are too long for the destination table column before any processing in the proc happens.

So in a nutshell, more work on your end since you'll have to add error handling to the proc you didn't need before.


SQL Row concatenation with XML PATH and STUFF giving aggregate sql error

Sorry, I missed a step in the relationship. Try this version (though Martin's will work as well):

SELECT DISTINCT o.section, names= STUFF((
    SELECT ', ' + b.Name
    FROM dbo.TableA AS a
    INNER JOIN dbo.TableB AS b
    ON a.AccountId = b.AccountId
    WHERE a.Section = o.Section
    FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM dbo.TableA AS o;

An approach that is at least as good, but sometimes better, is switching from DISTINCT to GROUP BY:

SELECT o.section, names= STUFF((
    SELECT ', ' + b.Name
    FROM dbo.TableA AS a
    INNER JOIN dbo.TableB AS b
    ON a.AccountId = b.AccountId
    WHERE a.Section = o.Section
    FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM dbo.TableA AS o
GROUP BY o.section;

At a high level, the reason DISTINCT applies to the entire column list. Therefore for any duplicates it has to perform the aggregate work for every duplicate before applying DISTINCT. If you use GROUP BY then it can potentially remove duplicates before doing any of the aggregation work. This behavior can vary by plan depending on a variety of factors including indexes, plan strategy, etc. And a direct switch to GROUP BY may not be possible in all cases.

In any case, I ran both of these variations in SentryOne Plan Explorer. The plans are different in a few minor, uninteresting ways, but the I/O involved with the underlying worktable is telling. Here is DISTINCT:

And here is GROUP BY:

When I made the tables larger (14,000+ rows mapping to 24 potential values), this difference is more pronounced. DISTINCT:

GROUP BY:

In SQL Server 2017, you can use STRING_AGG:

SELECT a.section, STRING_AGG(b.Name, ', ')
    FROM dbo.TableA AS a
    INNER JOIN dbo.TableB AS b
    ON a.AccountId = b.AccountId
    WHERE a.Section = a.Section
    GROUP BY a.section;

The I/O here is almost nothing:

But, if you're not on SQL Server 2017 (or Azure SQL Database), and can't use STRING_AGG, I have to give credit where credit is due... Paul White's answer below has very little I/O and kicks the pants off of both of the FOR XML PATH solutions above.

Other enhancements from these posts:

Grouped Concatenation in SQL Server
Grouped Concatenation : Ordering and Removing Duplicates
Comparing string splitting / concatenation methods

Also see:

Bad habits to kick : avoiding the schema prefix


SQL Server builds different execution plans for TOP 100, using a different sort algorithm. Sometimes it's faster, sometimes it's slower.

For simpler examples of it, read How Much Can One Row Change A Query Plan? Part 1 and Part 2.

For in-depth technical details, plus an example of where the TOP 100 algorithm is actually slower, read Paul White's Sorting, Row Goals, and the TOP 100 Problem.

The bottom line: in your case, if you know that no rows will be returned, well...don't run the query, eh? The fastest query is the one you never make. However, if you need to do an existence check, just do IF EXISTS(stick query here), and then SQL Server will do an even different execution plan.


Column vs Field: have I been using these terms incorrectly?

Relational database theory does not include the use of the word Field.  Dr. E.F. Codd, who wrote the series of papers that provide the theoretical basis for RDBMS's never used the term.  You can read his seminal 1970 paper A Relational Model of Data for Large Shared Data Banks if you want to check.

Terms like Domain, Table, Attribute, Key and Tuple are used.  One reason for this, is that his papers were largely concerned with relational algebra, and the way a particular implementation would define a table in a database wasn't considered by Codd to be important.  Vendors would flesh that out later.  People also have to understand that historically, RDBMS's evolved from existing hierarchical and network databases that predate them, AND the inner workings of an RDMBS still have to be concerned with data organization and storage.

In common use, and you can easily verify this by simply doing a bit of googling, Fields and columns are the same thing.

PC Databases like DBase, Access and Filemaker typically use "field" instead of "column".  "Attribute" is another term that can be used interchangeably.

For example, here's a link to the MS Access manual on adding a "field" to a table.  It's clear to see that in MS Access a "field" is equivalent to a "column".

The same holds for Dbase and Filemaker Pro.

Sometimes people will refer to a specific value in a specific row as being a "field" or more properly a "field value" but that does not make the use of "field" when referring to a column or column-equivalent-concept incorrect.  This does tend to cause a level of confusion because people have used "field" to mean different things for many years.  In relational theory -- a single atomic value is referred to as a "Datum".

If someone stated that a "field" is one value in a relational database and not the same as a column, that is their opinion, since "field" is not part of relational database vernacular.  They are neither right nor wrong, however, throughout the database world, field is more often used to mean column.

With that said, projects and teams often have to work out an understanding of how they want to use particular terminology within the project to avoid confusion.

You aren't wrong, but you also might decide to simply go along with the convention being used, or avoid using the word field altogether in favor of "column".  With relational databases, "Table" and "Column" are the building blocks that exist in DDL and it's best to just use those terms and avoid "field" which isn't used, nor clearly defined.


You have actually 3 possibilities to run the queries:

Run everything that's in the editor. This is like running an external script. Workbench opens a result tab for each statement that returns a result set. Use the flash icon or cmd/ctrl+shift+enter to execute (cmd on macOS, otherwise ctrl).
Select a single statement or multiple statements and press the same shortcut as in point 1. This will only run the selected code. The editor is clever enough to find statement boundaries automatically to run complete statements.
Place your cursor in the statement you want to run and press cmd/ctrl+enter. This will only run this single statement (and hence only show a single result set).


Postgres continuous backups - should WAL numbers be sequential after pg_start_backup call?

At one point in the distant past, a comment in the docs was changed from saying "sequential" to saying "ever-increasing". Source

This wording is still in the 9.1 docs for WAL internals. So I'd say that you shouldn't expect a gapless sequence.


Creating mongo 3 node replica set on ec2 problems

This is probably a firewall issue. The long pause is the socket connection hanging while it waits for it to timeout.

By default on EC2 the security groups (eg. firewall rules) for instances deny all inbound traffic. You have to explicitly specify what IP ranges or security group members are allowed to connect.

Using security groups instead of IPs is generally more maintainable. It allows you to add/remove additional servers later without worrying about updating the IP addresses that get dynamically assigned. You just need to include them in the same security group.

To get setup:

Create a security group specifically for your MongoDB instances. Let's call it "My MongoDB SG" and assume that AWS gives it the unique id sg-123456.
Edit the inbound firewall rules for the security group to allow traffic from the same security group on port. The entry should look like:

 Source         Port
 sg-123456      27017

Add the security group to each of your MongoDB instances

Then you can retry starting up the cluster.


Stored procedure to check if username exists

Perhaps doing a COUNT is better since it must be a number always

DELIMITER $$
DROP PROCEDURE IF EXISTS `USER_EXISTS` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `USER_EXISTS`
(IN `GIVEN_USERNAME` VARCHAR(64) CHARSET utf8mb4)
BEGIN
    SET @User_exists = 0;
    SELECT COUNT(1) INTO @found
    FROM `dbname`.`tablename`
    WHERE `username` = GIVEN_USERNAME;
    IF @found > 0 THEN
        SET @User_exists = 1;
    END IF;
    SELECT @User_exists;
END $$
DELIMITER ;

You could also write it as a function

DELIMITER $$
DROP FUNCTION IF EXISTS `USER_EXISTS` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `USER_EXISTS`
(`GIVEN_USERNAME` VARCHAR(64) CHARSET utf8mb4) RETURNS INT
BEGIN
    SET @User_exists = 0;
    SELECT COUNT(1) INTO @found
    FROM `dbname`.`tablename`
    WHERE `username` = GIVEN_USERNAME;
    IF @found > 0 THEN
        SET @User_exists = 1;
    END IF;
    RETURN @User_exists;
END $$
DELIMITER ;

Then, you could call the function to get a number

SET @x = USER_EXISTS('Synnyster');
SELECT USER_EXISTS('Synnyster');

Give it a Try !!!


How could I use binary log as relay log?

If the Slave is at the state of the start of the binlogs, then simply perform a CHANGE MASTER to point the Slave at the start of the binlogs.  POS can be either 0 or 4.  (In case you see either of those.)

At that point, the Slave will rapidly "catch up".


Store regular MySQL query results in a timeseries DB

You can write a simple script, which would periodically execute SQL query and push its results to a TSDB via one of the supported data ingestion protocols. Various TSDBs support various data ingestion protocols: Influx line protocol, Graphite plaintext protocol, OpenTSDB telnet put protocol, JSON, CSV, etc. For example, all these protocols are supported by VictoriaMetrics - see these docs for details.


Sql Server string concat with order by and subquery

String concatenation in that form combined with ORDER BY is undefined (and unstable and unreliable). I have seen the same symptom where you only get one result. If you want all the rows, drop the ORDER BY (ordering won't be guaranteed, but maybe that's ok, since your TOP 100 doesn't have an ORDER BY, so that's not guaranteed to be deterministic either). If you need ORDER BY, use XML PATH or STRING_AGG() instead, depending on version.

SELECT @sql = STUFF((
  SELECT ',' + t.error + CONVERT(varchar(12), xC.[object_id]) + ','
    + t.error + ',' + t.error + ',' + t.error + ','
    + t.error2 + ',' -- carriage returns are good for the soul!
    + t.error + ',' + t.error + ',' + t.error + ','
FROM #test AS t
INNER JOIN sys.columns AS xC
  ON t.object_id = xC.object_id
ORDER BY t.tmp_id -- this is the key to ordering
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'');

On 2017+ you can use:

SELECT @sql = STRING_AGG(t.error + CONVERT(varchar(11), xC.[object_id]) + ','
    + t.error + ',' + t.error + ',' + t.error + ','
    + t.error2 + ','
    + t.error + ',' + t.error + ',' + t.error, ',')
  WITHIN GROUP (ORDER BY t.tmp_id)
FROM #test AS t
INNER JOIN sys.columns AS xC
  ON t.object_id = xC.object_id;

For more info, see Order by 1 shortens the result set to one row and the links therein.