Can an SQL Job Deletes itself after its end date?

  I have created a job which will sets up its start date and end date
  automatically with a dynamic value.

  Is there a way to code it while creating or scheduling ?

You can do this by using sp_delete_job and dynamically creating applicable syntax and controlled logic to execute this for the dynamically created job name when date wise it needs to be deleted.

Since you are building the dynamic SQL Agent jobs and creating them with a start date and end date, you'd just put additional logic in to execute sp_delete_job if the date is equal to day eight at the end of the job WHERE @job_name = '<Dynamic Job Name>'.

You could put the additional logic in at the beginning of the job to execute sp_delete_job if the date is equal to day nine or the day after the eighth day WHERE @job_name = '<Dynamic Job Name>'.

  Syntax

sp_delete_job { [ @job_id = ] job_id | [ @job_name = ] 'job_name' } ,
     [ , [ @originating_server = ] 'server' ]
     [ , [ @delete_history = ] delete_history ]
     [ , [ @delete_unused_schedule = ] delete_unused_schedule ]

  source

Since you control the logic that creates the dynamic SQL Agent jobs you should be able to test with additional logic that will delete the dynamic SQL Agent job and execute sp_delete_job if the date is equal to a particular date (e.g. day eight or day nine).

It may be best to use logic that will execute sp_delete_job if the date is greater than or equal to day eight or nine in case it doesn't run on the exact date due to system issues, etc. with logic such as: WHERE Date >= CONVERT(datetime, 'YYYY-MM-DD' )

  Example

USE msdb ;
GO

EXEC sp_delete_job
    @job_name = N'NightlyBackups' ;
GO

  source


What does it mean to have many rows for a SPID in sp_who2

why is it ignoring MaxDegreeOfParallelism setting of 8?

That is because MAXDOP is per operator and not per execution plan. Have your MAXDOP setting to a good value.

  the MAXDOP setting is used to limit the number of threads per operation in the execution plan and does not limit the number of threads used to execute the query. So it is normal to see  threads per SPID in sysprocesses greater than MAXDOP setting.

Also see How it Works: SQL Server Per Query Degree Of Parallelism Worker Count(s)

As a side note: use sp_whoIsActive - there are many options and uses DMVs

-- sp_who2 is legacy, uses old deprecated tables and was written during ice age 1995/11/03 10:16 :-)


Select the first row returned from the join query based on a condition

If your version of Oracle is 12 or above, you can use FETCH FIRST n ROWS ONLY (doc link):

SELECT p.PRODUCT_ID, p.PRODUCT_NAME, m.MANF_ID, m.ITEM_NO, m.DEFAULT_MANF
FROM PRODUCT p
INNER JOIN MANUFACTURER m on p.PRODUCT_ID = m.PRODUCT_ID
WHERE p.PRODUCT_ID = 'PROD001'
AND m.MANF_ID = (SELECT MANF_ID FROM MANUFACTURER man WHERE man.PRODUCT_ID = p.PRODUCT_ID and coalesce(man.DEFAULT_MANF, 'N') = 'Y' ORDER BY man.DEFAULT_MANF, man.MANF_ID FETCH FIRST 1 ROWS ONLY)

If you're on Oracle 11 or below you need to use rownum=1 instead.

I'll add that if your schema was designed properly there would only be one default manufacturer for a product, and the subquery wouldn't be needed.

To actually answer your question though, you can abuse row_number.

SELECT p.PRODUCT_ID, p.PRODUCT_NAME, m.MANF_ID, m.ITEM_NO, m.DEFAULT_MANF
FROM PRODUCT p
INNER JOIN MANUFACTURER m on p.PRODUCT_ID = m.PRODUCT_ID
WHERE p.PRODUCT_ID = 'PROD001'
AND m.MANF_ID = (
    SELECT MANF_ID FROM (
        SELECT MANF_ID, ROW_NUMBER() OVER (ORDER BY man.DEFAULT_MANF, man.MANF_ID) as RN
        FROM MANUFACTURER man
        WHERE man.PRODUCT_ID = p.PRODUCT_ID
        and coalesce(man.DEFAULT_MANF, 'N') = 'Y'
    ) HACK
    WHERE RN=1
);


I already addressed something of this nature using Stored Procedures : Find highest level of a hierarchical field: with vs without CTEs (Oct 24, 2011)
If you look in my post, you could use the GetAncestry and GetFamilyTree functions as a model for traversing the tree from any given point.
UPDATE 2012-12-11 12:11 EDT
I looked back at my code from my post. I wrote up the Stored Function for you:
DELIMITER $$

DROP FUNCTION IF EXISTS `cte_test`.`GetFamilyTree` $$
CREATE FUNCTION `cte_test`.`GetFamilyTree`(GivenName varchar(64))
RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN

    DECLARE rv,q,queue,queue_children,queue_names VARCHAR(1024);
    DECLARE queue_length,pos INT;
    DECLARE GivenSSN,front_ssn VARCHAR(64);

    SET rv = '';

    SELECT SSN INTO GivenSSN
    FROM Employee
    WHERE name = GivenName
    AND Designation <> 'OWNER';
    IF ISNULL(GivenSSN) THEN
        RETURN ev;
    END IF;

    SET queue = GivenSSN;
    SET queue_length = 1;

    WHILE queue_length > 0 DO
        IF queue_length = 1 THEN
            SET front_ssn = queue;
            SET queue = '';
        ELSE
            SET pos = LOCATE(',',queue);
            SET front_ssn = LEFT(queue,pos - 1);
            SET q = SUBSTR(queue,pos + 1);
            SET queue = q;
        END IF;
        SET queue_length = queue_length - 1;
        SELECT IFNULL(qc,'') INTO queue_children
        FROM
        (
            SELECT GROUP_CONCAT(SSN) qc FROM Employee
            WHERE MSSN = front_ssn AND Designation <> 'OWNER'
        ) A;
        SELECT IFNULL(qc,'') INTO queue_names
        FROM
        (
            SELECT GROUP_CONCAT(name) qc FROM Employee
            WHERE MSSN = front_ssn AND Designation <> 'OWNER'
        ) A;
        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_names;
            ELSE
                SET rv = CONCAT(rv,',',queue_names);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;

    RETURN rv;

END $$

It actually works. Here is a sample:
mysql> SELECT name,GetFamilyTree(name) FamilyTree
    -> FROM Employee WHERE Designation <> 'OWNER';
+------+-----------------------+
| name | FamilyTree            |
+------+-----------------------+
| A    | B,F,C,H,L,I,K,D,E,G,J |
| G    |                       |
| D    |                       |
| E    |                       |
| B    | C,H,L,I,D,E,G,J       |
| F    | K                     |
| C    | D,E                   |
| H    | G                     |
| L    |                       |
| I    | J                     |
| K    |                       |
| J    |                       |
+------+-----------------------+
12 rows in set (0.36 sec)

mysql>

There is only one catch. I added one extra row for the owner

The owner has SSN 0
The owner is his own boss with MSSN 0

Here is the data
mysql> select * from Employee;
+-----+------+-------------+------+
| SSN | Name | Designation | MSSN |
+-----+------+-------------+------+
| 0   | A    | OWNER       | 0    |
| 1   | A    | BOSS        | 0    |
| 10  | G    | WORKER      | 5    |
| 11  | D    | WORKER      | 4    |
| 12  | E    | WORKER      | 4    |
| 2   | B    | BOSS        | 1    |
| 3   | F    | BOSS        | 1    |
| 4   | C    | BOSS        | 2    |
| 5   | H    | BOSS        | 2    |
| 6   | L    | WORKER      | 2    |
| 7   | I    | BOSS        | 2    |
| 8   | K    | WORKER      | 3    |
| 9   | J    | WORKER      | 7    |
+-----+------+-------------+------+
13 rows in set (0.00 sec)

mysql>


Impact of OS timezone change on Postgres Database

If you have set a time zone for your Postgres DB cluster in postgresql.conf, changing the time zone of the server OS will have no bearing on your databases at all - as long as you don't actually change the time itself, not just the time zone.

You need to make sure that none of your client programs feed local time to the database, which would actually change with the time zone setting of the server.

Even the time zone of the Postgres server has no bearing on data while operating with the data type timestamptz (timestamp with time zone). Only the display is adjusted to the timezone setting of each session - which defaults to the setting of the DB cluster unless specified otherwise. But the value is always the same.

However, it matters for timestamp (timestamp without time zone). And it also matters whenever a timestamptz is cast to timestamp or date (and the time zone is not provided explicitly with the AT TIME ZONE construct). This may even be hidden in expressions transforming or formatting temporal data. All of those will show an actual time-shift of two hours when the timezone setting of the session changes.

Related:

PostgreSQL timezone does not match system timezone
Ignoring timezones altogether in Rails and PostgreSQL


Problem with Czech collation Czech_CI_AI in MS SQL Server 2008 R2

R and Ř are different letters, as opposed to modified base letters(?) like German umlauts with ö vs o

From Czech language on Wikipedia (my bold)

  The acute accent (čárka) letters (Á, É, Í, Ó, Ú, Ý) and the kroužek letter Ů all indicate long vowels. They have the same alphabetical ordering as their non-diacritic counterparts. ...The háček (ˇ) indicates historical palatalization of the base letter. The letters Č, Ř, Š, and Ž currently represent postalveolar consonants and are ordered behind their corresponding base letters; while Ď, Ň, Ť represent palatal consonants and have the same alphabetical ordering as their non-diacritic counterparts.

A test with Ö and O in Swedish (different letters) and German (modified)

SELECT 'Finnish_Swedish_100_CI_AI'
          WHERE N'Ö' COLLATE Finnish_Swedish_100_CI_AI = N'O'
UNION ALL
SELECT 'Latin1_General_CI_AI'
          WHERE N'Ö' COLLATE Latin1_General_CI_AI = N'O'


MySQL Workbench 5.2 edition - Unable to establish the connection

I hope your problem might have been already resolved. However, I recently had a similar experience and I had to do following to resolve it.
1. Launch MySQL Workbench (Mine was 5.2.36 CE Revision 8542)
2. Double click on a server instance to manage the server instance
3. Goto Users and Privileges
4. Select root or other user you wish to check from the list in "Server Access Management" tab
5. Change the "Limit connectivity to hosts matching:" to IP that you are using to connect OR just change it to "%" to remove the IP restriction.<6>
6. In my case I had to change it to "%" to make it work.

Hope this will be helpful to someone, someday!
Cheers
Manish.


Referencing multiple tables from a single table and store additional data

I think what you're missing is a car_configuration table. This will have an FK to the cars table and have car_config_parts and car_config_tunings as children, like so:

create table car_configuration (
  config_id integer primary key,
  car_id integer references cars (car_id)
);

create table car_config_parts (
  config_id integer references car_configuration (config_id),
  part_id integer references parts (part_id),
  constraint car_config_part_pk primary key (config_id ,part_id)
);

create table car_config_tunings (
  config_id integer references car_configuration (config_id),
  tuning_id integer references tuning (tuning_id),
  constraint car_config_tuning_pk primary key (config_id, tuning_id)
);

You can then include the config_id as a reference in the timeset table (with no reference to cars). You'll need to ensure that you can't change the parts and tunings for a configuration or implement some form of versioning on this table to ensure you can reconstruct the exact details used to set a given time in the past.


PostgreSQL predicate `IS OF` and `IS NOT OF` supported or documented?

It's not documented, yet, but it is certainly supported and should be moving forward. It's actually in the SQL 2011 spec as type predicate.

8.19 <type predicate>
Function
Specify a type test.

Format
<type predicate> ::=
<row value predicand> <type predicate part 2>

<type predicate part 2> ::=
IS [ NOT ] OF <left paren> <type list> <right paren>

<type list> ::=
<user-defined type specification>
[ { <comma> <user-defined type specification> }... ]


Rejoining range seek on nullable composite index?

Is there any way to write a query that just seeks into that composite index key and then follows it along to retrieve the next chunk of 1000 rows?

A favourite solution of mine is to use an API cursor:

SET NOCOUNT ON;
SET STATISTICS IO ON;

DECLARE
    @cur integer,
    -- FAST_FORWARD, AUTO_FETCH, AUTO_CLOSE, CHECK_ACCEPTED_TYPES, FAST_FORWARD_ACCEPTABLE
    @scrollopt integer = 16 | 8192 | 16384 | 32768 | 1048576,
    -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE
    @ccopt integer = 1 | 32768 | 65536,
    @rowcount integer = 1000,
    @rc integer;

-- Open the cursor and return (up to) the first 1000 rows
EXECUTE @rc = sys.sp_cursoropen
    @cur OUTPUT,
    N'
    SELECT A, B, C
    FROM T
    ORDER BY A, B;
    ',
    @scrollopt OUTPUT,
    @ccopt OUTPUT,
    @rowcount OUTPUT;

IF @rc <> 16 -- FastForward cursor automatically closed
BEGIN
    -- Name the cursor so we can use CURSOR_STATUS
    EXECUTE sys.sp_cursoroption
        @cur,
        2,
        'MyCursorName';

    -- Until the cursor auto-closes
    WHILE CURSOR_STATUS('global', 'MyCursorName') = 1
    BEGIN
        EXECUTE sys.sp_cursorfetch
            @cur,
            2,
            0,
            1000;
    END;
END;

SET STATISTICS IO OFF;

The overall strategy is a single scan that remembers its position between calls. Using an API cursor means we can return a block of rows rather than one at a time as would be the case with a T-SQL cursor:

The STATISTICS IO output is:

Table 'T'. Scan count 1, logical reads 1011, physical reads 0, read-ahead reads 0
Table 'T'. Scan count 1, logical reads 1001, physical reads 0, read-ahead reads 0
Table 'T'. Scan count 1, logical reads 516, physical reads 0, read-ahead reads 0


Oracle Script For Getting Long Active Sessions

Presumably they are active and doing things so looks like you might need to get more info about your sessions before killing them off. I usually use a query like this to get important info about active sessions including how long they have been logged on (oldest at top), and also how long they have been running the current SQL.

SELECT USERNAME,
       TERMINAL,
       PROGRAM,
       SQL_ID,
       LOGON_TIME,
       ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
       ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
  From v$session
 WHERE STATUS='ACTIVE'
   AND USERNAME IS NOT NULL
ORDER BY MINUTES_LOGGED_ON DESC;


Excel grab data from MS SQL Server with WHERE clause based on cell contents

Could you make a tab in your sheet that holds all of your SQL data?  Each time you load your sheet you could refresh this data to make it fresh then everything else could be an Excel formula (lookup on SQL tab) to get at what you want.  My reason for suggesting this is you sound a lot more familiar with Excel over MS Query.

Otherwise in MS Query use your table as a data source and join with the SQL data (again I'd probably throw the result in a new tab)


This totally depends upon the load on the system, if its OLAP, OLTP or somewhere in between. What we have in our system is , generally higher MAXDOP for OLAP and lower, i.e 0 and 1 set for OLTP workload systems.

But above depends totally on the queries as well along with hardware configuration in terms of logical processors, hyper threading is enabled and NUMA node set up.

A good read on better understanding would be Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

Also, what has helped me in the past is following the codes from the discussion on What is a good, repeatable way to calculate MAXDOP on SQL Server?, the one from @Kin and @Max , really helps you getting a start as what can be used initially, thereafter you need to monitor and conclude what setting suits best for you're environment.


MariaDB Concurrent Connections with Update Statement

You cannot have "concurrent" updates of a single row.  The best you can do is to make them fast.

If you don't need the value, do it in a single statement:

UPDATE user SET files = files + 1 WHERE id = 1

Also, be sure that autocommit=ON is configured.

This will easily handle 100 increments per second on a spinning drive.  If you need even more speed, then set

innodb_flush_log_at_trx_commit = 2

This will cut back significantly on disk hits, at the potential loss of data for up to a second (in the case of a power failure).

max_connections = 25000 is grossly unreasonable; if you get more than a few dozen connections stumbling over each other, the system will appear to 'hang'.  At which point, more and more connections will be started, while the running connections run slower and slower.  This will because the OS is sharing the CPU, etc, among too many threads to actually get anything completed.

If you need thousands/second, I'll provide you with some other ideas.  (Note:  I say "per second", not "concurrently".)


'SELECT my_table.id' slower than 'SELECT my_table.*'

The EXPLAIN plan for SELECT * and SELECT id, which you said were identical, would be separated by the number of rows accessed. How are the rows being accessed ? Through the index_things_on_active_and_date index.  The SIMPLE in the EXPLAIN means it is a scan. In both cases, it is an index scan based on active=1 and date < '2015-07-11 00:00:00'

How does the index scan occur ?

Since the chosen index has active and date, the range scan would occur from two columns.
Look at the WHERE clause. You are retrieving owner_id and status and checking the values. This requires you accessing the whole row.

SELECT * means you make the whole row part of the result set
SELECT id means you make the id part of the result set

Where is this leading up to ?

In both cases, you are reading an entire row to check two additional columns not in the index.
The result set of each query is either an entire row or single column (id).
It must take some additional time to create a smaller result set. Why ? There comes a point when a longer result of id values would take longer the build that going with the whole row you read anyway. You yourself empirically tested that and discovered the following:

< 200 rows -> SELECT id is faster
> 200 rows -> SELECT * is faster
= 200 rows -> SELECT id and SELECT * are about the same

There is something else you need to realize about InnoDB

InnoDB stores data for a row is stored along with the index pages making up the PRIMARY KEY. This is known as the clustered index (gen_clust_index)
Non-unique indexes will store the primary key fields of row along with the index entries.

This tells you that the index index_things_on_active_and_date actually has three columns: 1) active, 2) date, 3) id.

You are probably saying: Why is SELECT * running better that SELECT id? (which is the original question) It goes back to what I said: the WHERE clause is causing the Query Optimizer to check non-indexed columns status and owner_id. You are creating additional work check an index entry and something from the row that is indexed.

If you create this index

ALTER TABLE things
    ADD INDEX index_everything_and_kitchen_sink
    (active,date,owner_id,status)
;

and run the two queries, then you will see the advantage go to SELECT id no matter how many rows you are accessing. Why ? Because all the columns in the WHERE clause are checked from the index only. This type of index is called a covering index.

Here are some good links about covering indexes

http://peter-zaitsev.livejournal.com/6949.html
http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
http://ronaldbradford.com/blog/tag/covering-index/

I mentioned these links in some of my answers:

Feb 10, 2012 Unexpected extremely long query time (~5 minutes using nested WHEN-INs)
Oct 17, 2012 : Combining columns in index
Jan 11, 2013 : MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)


How to use the output of one query in other

Use a CTE (common table expression):

with myquery as (
SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ja.last_executed_step_id
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
stop_execution_date is null
)
SELECT *
FROM myquery
JOIN ....

Documentation link.


Can't find missing packages Oracle Linux 8

You want to install the "oracle-database-preinstall-18c" package, using the "yum" utility as root. That will load all of your dependencies for you:
[root@orcl1 ~]# yum search oracle-database-preinstall-18c
Loaded plugins: langpacks, ulninfo
================================================================== N/S matched: oracle-database-preinstall-18c ===================================================================
oracle-database-preinstall-18c.x86_64 : Sets the system for Oracle Database single instance and Real Application Cluster install for Oracle Linux 7

[root@orcl1 ~]# yum install oracle-database-preinstall-18c
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-preinstall-18c.x86_64 0:1.0-1.el7 will be installed
--> Processing Dependency: compat-libstdc++-33 for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64
--> Running transaction check
---> Package compat-libstdc++-33.x86_64 0:3.2.3-72.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================================================================================================
 Package                                                   Arch                              Version                                  Repository                             Size
==================================================================================================================================================================================
Installing:
 oracle-database-preinstall-18c                            x86_64                            1.0-1.el7                                ol7_latest                             18 k
Installing for dependencies:
 compat-libstdc++-33                                       x86_64                            3.2.3-72.el7                             ol7_latest                            190 k

Transaction Summary
==================================================================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 208 k
Installed size: 866 k
Is this ok [y/d/N]:

It should be noted that Oracle 18c isn't supported on Oracle Linux 8; ideally you should be using Oracle Linux 7. See these articles for install steps and guidelines:

Database Installation Guide for Linux
Oracle Database 18c Installation On Oracle Linux 8 (OL8)


Optimize Percona MySQL InnoDB 3.2TB database

Forget about mysqltuner and check for human advice. This tool tells you general recommendations that may be useless and even hurtful in some cases. Optimize table is probably going to be useless, but it locks your tables for writes. A consultant may save you time and money in the long run.
Swapping should be a no-go for MySQL. Make sure to tune your innodb_buffer_pool_size to maximize memory usage without swapping. Please make sure you understand that there may be other things using memory: filesystem cache, needed for logging and other operations, and per-client used memory, like the join buffer and sort buffers. All together should be less than the available physical memory, as MySQL/InnoDB does its own way of buffering, and if it uses virtual memory that in reality it is on disk, you are going to have a bad time.
It is difficult to provide foolproof "general advice" without a full review of your server. But in my experience, 90% of the problems are with the queries and the database design, not with the server configuration.
However, here you have a good introduction to MySQL/Innodb configuration optimization by a good human (Peter Zaitsev). Hope it helps.


SQL Server + Microsoft Business Intelligence -- free version or online learning environment?

Evaluation Edition is Enterprise Edition with a timebomb (180 days). It includes SSIS, SSAS, SSRS, etc. So you can evaluate that for 180 days to learn the ins and outs of BI.

Developer Edition, also, provides these tools for ~$50-60 (Microsoft sells it direct for $59.95, though you may find it cheaper from other retailers). The difference is no timebomb but a vastly different EULA. (And if you have an MSDN subscription, you can get this edition - well, any edition - for free as part of your subscription. But there are still licensing restrictions on use, so please pay attention to the EULAs.)

There may also be ways to evaluate these features in the cloud, using Azure Virtual Machine images. I haven't done this, so I don't know if there's any way to take full advantage of this without incurring some level of cost, but I'm pretty sure you can sign up for a trial (I'm also pretty sure it will be significantly less than 180 days).

Also SQL Server Data Tools (SSDT) includes some additional features around building and maintaining solutions that might be useful.


why is this left join faster than an inner join?

It looks as though some of the difference is because the slow plan was run first.

The slow plan was clearly operating against a cold cache as it shows additional physical reads and managed to accumulate an additional 15 seconds of PAGEIOLATCH_SH waits compared to the fast case.

This looks as though it affected both the execution of the TVF itself (which took 5.5 seconds compared with 1.35 in the fast case.) and the wider plan using the result of it.

You say in the comments that on second execution it took 11 seconds. This is still 3 times slower than the fast plan (3.446 seconds) so doesn't explain all the performance difference.

The main problem you are experiencing is due to the poor default estimation for multi statement TVFs (fixed guess of 100 rows in compatibility levels 2014/2016 and 1 in earlier versions). In reality your TVF returns 1,715 rows.

In the inner join case because inner joins are associative and commutative it can be re-ordered flexibly and gets moved to the deepest part of the tree. This would make sense if one row was actually returned as it could whittle down the row count early for the other joins in the plan.

The execution plan is below. The pink annotations are "Actual Elapsed Time (ms)" from the XML.

Because of the 1 row estimate it starts off badly by joining onto dbo.ParticipantTrades and selecting a plan with nested loops and lookups. That nested loops has an elapsed time of 13.976 seconds (presumably most of which was spent waiting on the spilling sort immediately upstream to request rows from it, with 3.26 seconds taken up on the lookups themselves and associated IO waits for the physical reads at that operator).

923,646 rows are emitted from the join vs an estimated 1423.18 and this mis-estimate propagates upwards in the plan through 3 sorts and a hash join spilling as it goes (due to the row underestimate)

When you add the LEFT JOIN it can not be as freely re-ordered and the join happens much higher up in the plan (where it would do less damage in the problematic INNER JOIN case). The semantics of outer join help here anyway.

Whilst the estimate for the number of rows coming out of the TVF is still 1 this does not adversely affect the estimates for the join it is directly involved in (SQL Server assumes that the cardinality coming out of that join will be the same as that of the other sub tree to the join and this is in fact what happens - an outer join cannot reduce this number as a non joined row would still pass through - just with NULL for the fvh columns).

There are still cardinality estimation errors in the outer join plan but not of the same magnitude and it requests a sufficient memory grant to avoid spilling anywhere.

This cardinality estimation issue has been resolved in the most recent version with interleaved execution. In the meantime (as you say in the comments it needs to be 2008 compatible) you can manually interleave it by storing the TVF result into a #temp table and joining onto that to allow the count of the intermediate result (and column statistics) to be taken into account.


Running an "ORDER BY CASE" query on MySQL view breaks it afterwards

1)Syntax error: need add space between THEN and 0 (eq. ELSE).


Migrating from SQL Server to MySQL using MySQL Workbench tool

Well, since you have data currently stored in SQL Server, and it's already in an NVARCHAR column, then either it's an NVARCHAR <= 4000 (in which case you can't lose any data, and should just change all instances of NVARCHAR(8000) to NVARCHAR(4000)), or it's an NVARCHAR(MAX) column, in which case you change all instances of NVARCHAR(8000) to NVARCHAR(MAX). Or just leave out those CASTs - do you really need them?

As an aside, you should probably change as NTEXT to as NVARCHAR(MAX) as well.


Sql Server Replication - Modify Statement Delivery Stored Procedures before the replication starts

From docs:

  Default and custom stored procedures

  The three procedures that
  replication creates by default for each table article are:

  sp_MSins_< tablename >, which handles inserts.

  sp_MSupd_< tablename >, which handles updates.

  sp_MSdel_< tablename >, which handles deletes.

  The  used in the procedure depends on how the article was
  added to the publication and whether the subscription database
  contains a table of the same name with a different owner.

  Any of these procedures can be replaced with a custom procedure that
  you specify when adding an article to a publication. Custom procedures
  are used if an application requires custom logic, such as inserting
  data into an audit table when a row is updated at a Subscriber. For
  more information about specifying custom stored procedures, see the
  how to topics listed above.

Default and custom stored procedures

So you create the custom stored procedures in the subscriber database and configure the article to use them in sp_addarticle.


Not authorized on test to execute command

Turns out that I was missing a /authDBName in my connection string. It should be this:
mongodb://myUser:userPW@SG-staging-111.servers.mongodirector.com:27017,SG-staging-43334.servers.mongodirector.com:27017/stagingDB?replicaSet=RS-staging-0&ssl=true&authSource=stagingDB


Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB

If you enable MySQL with innodb_file_per_table, each InnoDB table will have its own dedicated tablespace. The system tablespace file, bwetter known as ibdata1, will grow very slowly.

If innodb_file_per_table is disable, everything and its grandmother goes into ibdata1. That will grow quickly.

If you are not using innodb_file_per_table, you need to rearchitect InnoDB's infrastructure. This will give you a limit of 2TB per table instead of 2TB for all InnoDB. This is the case for ext3. On ext4, the table size can be up to 16TB.

Please see my past posts on how to do that

https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261 (Oct 29, 2010)
https://stackoverflow.com/questions/5248420/at-what-point-does-mysql-innodb-fine-tuning-become-a-requirement/5250306#5250306 (Mar 09, 2011)
https://serverfault.com/questions/155455/how-do-myisam-and-innodb-utilize-hd-space/246111#246111 (Mar 11, 2011)
https://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-cons/231400#231400  (Feb 04, 2011)
https://serverfault.com/questions/253059/mysql-innodb-optimisation/253233#253233 (Mar 29, 2011)


Oracle Ref Partitioning: Deadlock due to child table row migration

That could be caused by unindexed foreign keys.
Tom Kyte has a query that you can use to find them.
It could be as simple as adding a few indexes
on the child tables.

Unindexed Foreign Keys


How to specify “partition (partx)” in “mysqldump” command?

Plan A:  Provide a WHERE clause that exactly covers that partition.

Plan B:  Let's discuss (1) why you are using partitioning, and (2) why you need that kind of dump.

Plan C:  See "transportable tablespaces" as a way to break off a partition, turning it into an independent table.


Query to find sql server services currently running

The DBATools PowerShell module is very useful for such tasks.
You can use Get-DbaService function from DBATools to get list of SQL related services installed on one or more servers.
Get-DbaService -ComputerName YourServer


Why do LocalTemp table showing with lengthy underscore under Temporary Tables of tempdb in SSMS 2017 Express Edition?

The lengthy underscore followed by the number were added by the engine to your requested table name in order to make the table name globally unique in the tempdb database. You can query your local table in the session that created it using the #localtemptable name that you gave it. What you are seeing is perfectly normal behavior and there's nothing to be alarmed about.


If the table has more columns than the 3 described, it would be more efficient to scan the IX_LastName_Age index.  If the table is just those 3 columns, it may scan either the clustered index, or the IX_LastName_Age index, each would essentially be equivalent.  In no case could there be a seek given the info in this scenario.


INSERT/UPDATE stored proc deadlocking itself

I think you will find this pattern better:

BEGIN TRANSACTION;

UPDATE dbo.OrganizerDataDependencyChange WITH (HOLDLOCK)
  SET Updated = GETDATE()
  WHERE TableID = @TableID
  AND [Database] = ISNULL(@Database, '');

IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO dbo.OrganizerDataDependencyChange
      ( TableID, [Database], Updated )
  VALUES(@TableID, ISNULL(@Database, ''), GETDATE());
END

COMMIT TRANSACTION;

Stop thinking about having to "check" if there is a row to update, and then updating it, which can lead to two full scans. Just try to update it. If no rows are updated, no harm, no foul - you can simply perform an insert (I talk about this a bit here).


Why does "SELECT POWER(10.0, 38.0);" throw an arithmetic overflow error?

Instead of meddling with Martin's answer any further, I'll add the rest of my findings regarding POWER() here.

Hold on to your knickers.

Preamble

First, I present to you exhibit A, the MSDN documentation for POWER():

  Syntax

  POWER ( float_expression , y )

  Arguments

  float_expression
  Is an expression of type float or of a type that can be implicitly converted to float.

  Return Types

  Same as float_expression.

You may conclude from reading that last line that POWER()'s return type is FLOAT, but read again. float_expression is "of type float or of a type that can be implicitly converted to float". So, despite its name, float_expression may actually be a FLOAT, a DECIMAL, or an INT. Since the output of POWER() is the same as that of float_expression, it too may also be one of those types.

So we have a scalar function with return types that depend on the input. Could it be?

Observations

I present to you exhibit B, a test demonstrating that POWER() casts its output to different data types depending on its input.

SELECT
    POWER(10, 3)             AS int
  , POWER(1000000000000, 3)  AS numeric0     -- one trillion
  , POWER(10.0, 3)           AS numeric1
  , POWER(10.12305, 3)       AS numeric5
  , POWER(1e1, 3)            AS float
INTO power_test;

EXECUTE sp_help power_test;

DROP TABLE power_test;

The relevant results are:

Column_name    Type      Length    Prec     Scale
-------------------------------------------------
int            int       4         10       0
numeric0       numeric   17        38       0
numeric1       numeric   17        38       1
numeric5       numeric   17        38       5
float          float     8         53       NULL

What appears to be happening is that POWER() casts float_expression into the smallest type that fits it, not including BIGINT.

Therefore, SELECT POWER(10.0, 38); fails with an overflow error because 10.0 gets cast to NUMERIC(38, 1) which isn't big enough to hold the result of 1038. That's because 1038 expands to take 39 digits before the decimal, whereas NUMERIC(38, 1) can store 37 digits before the decimal plus one after it. Therefore, the maximum value NUMERIC(38, 1) can hold is 1037 - 0.1.

Armed with this understanding I can concoct another overflow failure as follows.

SELECT POWER(1000000000, 3);    -- one billion

One billion (as opposed to the one trillion from the first example, which is cast to NUMERIC(38, 0)) is just small enough to fit in an INT. One billion raised to the third power, however, is too big for INT, hence the overflow error.

Several other functions exhibit similar behavior, where their output type is dependent on their input:

Mathematical functions: POWER(), CEILING(), FLOOR(), RADIANS(), DEGREES(), and ABS()
System functions and expressions: NULLIF(), ISNULL(), COALESCE(), IIF(), CHOOSE(), and CASE expressions
Arithmetic operators: Both SELECT 2 * @MAX_INT; and SELECT @MAX_SMALLINT + @MAX_SMALLINT;, for example, result in arithmetic overflows when the variables are of the named data type.

Conclusion

In this particular case, the solution is to use SELECT POWER(1e1, precision).... This will work for all possible precisions since 1e1 gets cast to FLOAT, which can hold ridiculously large numbers.

Since these functions are so commonplace, it's important to understand that your results may be rounded or may cause overflow errors due to their behavior. If you expect or rely on a specific data type for your output, explicitly cast the relevant input as necessary.

So kids, now that you know this, you may go forth and prosper.


This sounds vaguely familiar. Oh yea, I wrote about this back on Aug 14, 2012  : error 1118: row size too large. utf8 innodb. Here is additional information from a Percona Expert on this subject : InnoDB create table error: “Row size too large”

I can see this putting a strain on an InnoDB table in terms of row length limits. Turning TEXT fields of ominous lengths into VARCHARs or even CHARs in InnoDB will have no effect (at least, no positive effects).

Your concern is quite valid and merits some respect. Why?

Some would do one of the following:

Perform the conversion and deal with (mostly complain about) worse consequences
Perform the conversion in a Development server, see the effects, and judge accordingly
Ask why, do research, carry out due diligence in a Development server

It sounds like you did Option #3 from the above list. Your conclusion, given the lengths of data, is correct for your given dataset.


As renaissancedev blog documentation here In order to build flexible and maintainable cloud infrastructure we need a way to connect services together with minimal effort. For a long time DNS has been the way to do that, but it also poses a different set of problems. How do you keep the DNS entries up to date? How do you make sure that the instances that are registered in DNS are actually healthy and able to serve traffic?

Consul from Hashicorp is a modern answer to these problems that is being widely adopted by teams of all sizes. It is a service discovery layer for your datacenter, as well as being a distributed key-value store and able to perform cross-datacenter replication and discovery. One of the wonderful things about it is the fact that the service discovery layer can be used via a built-in DNS interface. Services that are registered in Consul can have an associated health-check which will cause a failing instance to be removed from the list of DNS entries for that service. All-in-all it’s a pretty great system that is definitely worth considering if you haven’t already started using it yourself.

For Example the Solution of this problem

Once I fully understood what the failure was, the solution became obvious. Since you can register arbitrary service names in Consul and associate a variety of different health checks, all I had to do was set one up that would only point to the node that was currently the master. In order to make that work I wrote a scripted health check that would run the following script every 10 seconds:

#!/bin/bash
ISMASTER=$(/usr/bin/mongo --quiet --eval 'db.isMaster().ismaster')
if [ "$ISMASTER" = "true" ]
then
    exit 0
else
    exit 2
fi

I then configured the Consul agent on the MongoDB instances with this service definition:

{
  "services": [
    {
      "check": {
        "interval": "10s",
        "script": "/consul/scripts/mongo_is_master.sh"
      },
      "name": "mongodb-master",
      "port": 27017,
      "tags": [
        "mongodb",
        "master"
      ]
    }
  ]
}

Whichever instance is passing that check will be registered as the mongodb-master.service.consul DNS record in Consul. That means that the clients just need to be configured to connect to mongodb://mongodb-master.service.consul:27017/database?replicaSet=rs0&readPreference=primaryPreferred and everyone will be happy.

For your further info here and here


How to get updated stats for a truncated table

We now used the following solution to ensure that all our raw data tables were filled:

CREATE OR REPLACE FUNCTION util.assert_table_not_empty(schema_tablename TEXT)
  RETURNS BOOLEAN AS $$
DECLARE
  result TEXT;
BEGIN
  -- * doesn't work if the first cell would be NULL
  EXECUTE 'SELECT 1 FROM ' || schema_tablename || ' LIMIT 1'
  INTO result;

  IF result is NULL
  THEN
    RAISE EXCEPTION 'assertion table not empty, failed for:
%', schema_tablename;
  END IF;
  RETURN result IS NULL;
END
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION raw_data.ensure_data()
  RETURNS VOID AS
$$
DECLARE
  tablename TEXT;
BEGIN

  FOR tablename IN SELECT table_schema || '.' || table_name
                   FROM information_schema.tables
                   WHERE table_schema IN ('raw_data')

  LOOP
    PERFORM util.assert_table_not_empty(tablename);
  END LOOP;
END;
$$ LANGUAGE 'plpgsql';

-- in the ETL parts:
SELECT raw_data.ensure_data();


if table has no record match from table 1 will be automatically should be null

SELECT j.judge_name,
       s.score
FROM tbl_judges j
LEFT JOIN tbl_scoring s
       ON j.id = s.jud_id
      AND s.crit_id = 1
      AND s.can_id = 11

  or much better for default score of 87

SELECT j.judge_name,
       COALESCE(s.score, 87) score
FROM tbl_judges j
LEFT JOIN tbl_scoring s
       ON j.id = s.jud_id
      AND s.crit_id = 1
      AND s.can_id = 11


Avoiding null comparisons in where clause

If you have an index on DestinationCountry,BeginPostCode,ENDPOSTCODE then your original query seeks into the DestinationCountry part but no further.

You may find better performance rewriting as an UNION ALL if you have that index.

The below can seek into non overlapping parts of this index.

An exact seek on (DestinationCountry, BeginPostCode, ENDPOSTCODE) =  (@COUNTRY,NULL, NULL)
An exact seek on (DestinationCountry, BeginPostCode, ENDPOSTCODE) =  (@COUNTRY,NULL,  @POSTCODE)
An equality seek on (DestinationCountry) =  (@COUNTRY) and then a range seek on the secondary key BeginPostCode <= @POSTCODE with a residual predicate on all rows that match.

__

WITH Country
     AS (SELECT *
         FROM   dbo.DpdRoute
         WHERE  DestinationCountry = @COUNTRY)
SELECT *
FROM   Country
WHERE  BeginPostCode IS NULL
       AND ENDPOSTCODE IS NULL
UNION ALL
SELECT *
FROM   Country
WHERE  BeginPostCode IS NULL
       AND ENDPOSTCODE = @POSTCODE
UNION ALL
SELECT *
FROM   Country
WHERE  BeginPostCode <= @POSTCODE
       AND ( ENDPOSTCODE >= @POSTCODE
              OR ( BeginPostCode = @POSTCODE
                   AND ENDPOSTCODE IS NULL ) );


Ensure N:ALL relation ship for some rows

This is a mistake. Just join the tables and infer permissions.

CREATE TABLE users (
  userid       serial  PRIMARY KEY,
  username     text    UNIQUE,
  is_superuser bool    NOT NULL DEFAULT false
);
CREATE TABLE permissions (
  permid  serial PRIMARY KEY,
  name    text
);
CREATE TABLE user_permission (
  permid  int NOT NULL REFERENCES permissions,
  userid  int NOT NULL REFERENCES users,
  PRIMARY KEY (permid, userid)
);

INSERT INTO users ( username, is_superuser ) VALUES
  ( 'ecarroll', true ),
  ( 'jbob', false );

INSERT INTO permissions (name) VALUES ('delete');
INSERT INTO permissions (name) VALUES ('select');

INSERT INTO user_permission (permid, userid) VALUES (2,2);

Now we write a query that cross joins users with permissions so we can see all a users permissions. And, then we left outer join that for all users that are not superusers, with the linking table. We infer if they has_permission if they're is_superuser OR /have entry in linking table/. The is_superuser is a superior source: we no longer even care about what's in the linking table if we have that bool set. This makes your job simple and efficient. You don't have to keep the tables in sync.

SELECT
  users.userid,
  users.username,
  permissions.name,
  users.is_superuser OR user_permission.userid IS NOT NULL AS has_permission
FROM users
CROSS JOIN permissions
LEFT OUTER JOIN user_permission
  ON NOT is_superuser
  AND user_permission.userid = users.userid
  AND user_permission.permid = permissions.permid
ORDER BY users.userid;

Produces

 userid | username |  name  | has_permission
--------+----------+--------+----------------
      1 | ecarroll | delete | t
      1 | ecarroll | select | t
      2 | jbob     | delete | f
      2 | jbob     | select | t
(4 rows)


Why does querying the right most index of a composite index does not result in full table scan in postgres?

I can't replicate this without going to extreme contortions, like setting enable_seqscan, enable_bitmapscan, and enable_indexonlyscan all to off.  What version are you using, what are you non-default settings, and how did you populate the data (as shown, you would get an empty table to query)?

But anyway, what it is doing here is a full index scan, using the index as a skinny table (although in your case, not much skinnier than the table itself), and applying the condition as an in-index filter.  Unfortunately, the displayed plan does not distinguish between the "Index Cond:" is being used for jump-to selectivity, versus just as an in-index filter.  But you can see the cost estimate for the query that uses it as an in-index filter is far higher (50 fold) than the one where it uses jump-to selectivity.


Access denied for user 'root'@'localhost' (using password: NO) on 'service mysql start'

I had similar problem.

https://stackoverflow.com/questions/38098505/mysql-works-with-sudo-but-without-not-ubuntu-16-04-mysql-5-7-12-0ubuntu1-1

And solved id following by:

https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04

sudo mysql -u root
DROP USER 'root'@'localhost';
CREATE USER 'root'@'%' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;


Update based on MongoEngine usage in comments:

MongoEngine defers to pymongo for the connection pool setting. Hence, based on the pymongo FAQ:

  In versions before 2.6, the default max_pool_size was 10, and it did
  not actually bound the number of open connections; it only determined
  the number of connections that would be kept open when no longer in
  use.

  Starting with PyMongo 2.6, the size of the connection pool is capped
  at max_pool_size, which now defaults to 100. When a thread in your
  application begins an operation on MongoDB, if all other connections
  are in use and the pool has reached its maximum, the thread pauses,
  waiting for a connection to be returned to the pool by another thread.

Assuming you are using a version later than 2.6 you will have a default of 100, which theoretically should be enough.  However even if the responses are very quick you still have some overhead (connect, run query, return result, return connection to pool) with the pool in order to reach 100 concurrent users on the front end. Therefore, I would still look at increasing the max_pool_size parameter to see if that unblocks your load testing (say 150 to start with).


Return number of rows based on column value

For Postgres you can use:

select guid, g.i
from the_table
  cross join lateral generate_series(1, entries) as g(i);


SQL query to find player with maximum runs

With the table of separate runs, making the sums will mean reading entire table each time so for performance it would be good to have the sums precomputed somewhere, maybe even in Player table, with updates either periodically (daily, each hour) or on insert/update in Runs table (trigger, ORM event or other ways).

But that may be not possible for your case, so query to compute them and find the max:

select
  sum(r.runs) total,
  r.p_id,
  p.name
from Runs r
join Player p using(p_id)
group by r.p_id
order by sum(r.runs) desc
limit 1;

Can be seen in action at http://sqlfiddle.com/#!9/27820/2

When two players have the same max, it will pick one of them (probably the one with lower p_id, but that does not have to be true). If it is not OK, define further requirements for this.

It would be good to have index on (p_id, runs) in Runs table so summing per player can be done fast, but then to find the max a filesort is still needed, so if you plan to have hundreds of thousands or millions of rows in Runs table, then you will probably really want to precompute it.

Players table only needs index in p_id, which is easily satisfied by it being primary key which seems quite natural.


MariaDB on Windows Server 2012 R2 - out of memory

The 32bit is the culprit. Due to a strange rounding up when calculating buffers, the server would allocate almost twice as much memory (3GB instead of 1.5GB), leaving only 1GB address space for anything else .It is fixed in 10.2.7 and later, the bug report is here https://jira.mariadb.org/browse/MDEV-12097


How to view table data in the newest 6.0 MySQL Workbench

If you just want to see the data in a table then do a select for that table in a query editor. That's the most basic operation wrt SQL. Using the table search with some "mysterious query" certainly does not help to accomplish that task. Search is, as the name implies, a search. Put the value you are looking for in the search field and let MySQL Workbench do the search for you. As easy as that.

If you want a mouse only approach then open the schema in the tree and right click on the table you want to see. A context menu appears and the first entry is the select (which takes the limit into account that is set in the preferences, to avoid pulling a potential million rows).

The old "Edit table data" was a very similar shortcut, but that uses the EDIT keyword to tell the editor that the result is editable. That was an intermediate addition as MySQL Workbench had no other way to know if that is the case. In the meantime the application can detect this automatically (a PK must exist, no aggregates, no joins etc.), so the EDIT keyword as well as the shortcut have been removed.


Any gotchas at all with converting from MyISAM to InnoDB?

Here are some gotchas
Memory Usage
MyISAM

only caches index pages.
shared keycache (sized by key_buffer_size).
You can also set up dedicated keycache, one or more tables per cache table.

InnoDB

caches data pages and index pages.
one buffer pool and one size before MySQL 5.5
1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.
FULLTEXT Indexes
MyISAM

Supports FULLTEXT indexes

InnoDB

Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

MySQL 5.5 and back
To locate which MyISAM tables have a FULLTEXT index run this query:
select tbl.table_schema,tbl.table_name from
(
    select table_schema,table_name
    from information_schema.tables
    where engine='MyISAM'
    and table_schema NOT IN ('information_schema','mysql')
) tbl
INNER JOIN
(
    select table_schema,table_name
    from information_schema.statistics
    where index_type='FULLTEXT'
) ndx
USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.
OPTIMIZE TABLE
MyISAM

The MyISAM table is shrunk
ANALYZE TABLE runs index statistics on all indexes

InnoDB

ANALYZE TABLE is totally useless because index stats are always being recollected
With innodb_file_per_table disabled, ibdata1 will grow bigger
With innodb_file_per_table enabled, tablespace (.ibd) file is shrunk


innodb_file_per_table option enabled but ibdata1 file still grows up

A number of operations use ibdata1 temporarily.  The actual data for the given table will be entirely in the .ibd file.

It's rather normal for ibdata1 to be at least tens of MB, even with all tables in their .ibd files.

Do you have the following case?

innodb_file_per_table=OFF
Create and populate table1 -- this will go into ibdata1
change to innodb_file_per_table=ON
Create and populate table2 -- this will go into table2.ibd
insert into table1 -- this will still go into ibdata1

The way to move an existing table to ibd is to set ON, then do

ALTER TABLE table1 ENGINE=InnoDB;

If this does not explain your situation, please keep an eye on (1) what operations are being performed on any table, and (2) the size of ibdata1.  If it grows again, the (1) may give us some more clues.

Note especially any operations involving large tables.


This is a good case for CHECK constraints using a function. Step one, write a function that does something like (EDIT: Used Event instead of Action sorry!):

CREATE FUNCTION CheckReasonValid(@intReason, @intEvent)
RETURNS bit
AS
BEGIN
   DECLARE @NumRows int
   SELECT @NumRows = COUNT(*) FROM master_action_reason mar
       INNER JOIN Events e ON mar.Action = e.Action
       WHERE mar.Reason = @intReason AND e.Event = @Event
   RETURN @NumRows
END;

Then in your table definition, add the CHECK constraint, something like:

ALTER TABLE Reasons
ADD CONSTRAINT chkActionReasonValid CHECK (dbo.CheckReasonValid(Reason,Event) >= 1 );


How can i fix the Oracle Enerprise Manager error - Agent is blocked?

I suggest you re-sync your agent using the EM GUI !
Here is a article showing how this is done !

In short :

Check your agent status :

./emctl status agent

Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 12.1.0.3.0
OMS Version : (unknown)
Protocol Version : 12.1.0.1.0
Agent Home : /em_agent12.3/agent_inst
Agent Binaries : /em_agent12.3/core/12.1.0.3.0
Agent Process ID : 64164
Parent Process ID : 64110
Agent URL : https://bih002:3872/emd/main/
Repository URL : https://dcg023:4900/empbs/upload
Started at : 2013-11-13 15:26:08
Started by user : em_user
Last Reload : (none)
Last successful upload : (none)
Last attempted upload : 2013-11-13 15:26:14
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload : 72
Size of XML files pending upload(MB) : 0.11
Available disk space on upload filesystem : 91.16%
Collection Status : Collections enabled
Heartbeat Status : **Agent is blocked**

Next re-sync your agent using the GUI:

Now check you agent status again!


Nested Dynamic Query with SP_EXECUTE_REMOTE for binding the Parameters

SP_EXECUTE_REMOTE in Azure works very similar to native sp_executesql. So you should be able to bind the params & values. But I believe there is a limitation that you can't get a output param value from it. Try this and see if it works for your case. Thanks.

EXEC SP_EXECUTE_REMOTE
    N'External_DB1',
    N' DECLARE @param1 nvarchar(max)= @param1_from_sp_DB2, @outparam nvarchar(max)
    EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'',
    N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
    @param1 ,@outparam OUTPUT
    SELECT @param1 AS Paramvalue,@outparam AS outputvalue',
    N'@param1_from_sp_DB2 nvarchar(max)',
    @param1 --Since this is the input param name from sp_DB2


You can try to move your function call to an OUTER/CROSS APPLY and then use the APPLY result to check your value:

SELECT <your columns>
FROM <your tables>
    CROSS APPLY
    (
        SELECT dbo.GetFinalLetter(Id, CASE
        WHEN Id IN (
                ---------
                )
            THEN 1
        WHEN Id IN (
                --------
                )
            THEN 0
        END) as LetterText
    ) AS x
WHERE x.LetterText IS NOT NULL ;

OR you can also use a subquery of the full query:

SELECT <your columns>
FROM
(
    SELECT <your columns>,
        dbo.GetFinalLetter(Id, CASE
        WHEN Id IN (
                ---------
                )
            THEN 1
        WHEN Id IN (
                --------
                )
            THEN 0
        END) as LetterText
    FROM <your tables>
) AS t
WHERE LetterText IS NOT NULL ;

The way to do that may depend on the data volume of the tables.

Hope this helps.


SQL To Return True False base on condition where it either matches the condition or is completely null

Yes, this code will work, although I would put the NOT EXISTS check before the EXISTS check if the NOT EXISTS condition will be matched very frequently (more than 10-20% of the time, depending on actual performance). Other than that, this query should do what you need it to do.


Can I use symlink or data directory for ARCHIVE MySQL tables?

All mention of either using a symlink or data/index separate data directories state that MyISAM is the only supported

Almost, but not quite "all."

There are three ways of symlinking in MySQL:

manually symlink the individual table files - only supported for MyISAM and even then the documentation says "as a last resort."
automatically symlink MyISAM MYD and MYI files with DATA DIRECTORY and INDEX DIRECTORY directives.
manually symlink a database (i.e. a single schema) directory to a directory in a different location

This last one is the one you want, and will work transparently with any of the normal storage engines.

Stop reading the page from the manual that you mentioned above before you get to section 8.11.3.1.2, and this will make more sense.  Everything from that point forward is related to table files being symlinked, not directories.  The information that follows is not relevant if your symlinks are done at the database directory level.

Though not directly related to your question, I'll point out that your entire /usr/local/mysql/data directory can also be a symlink to a directory elsewhere.  I almost always do this instead of actually telling MySQL to use a different directory, so that it's obvious to any sysadmin looking at the server itself where the actual data lives.


Update a table with data from another table

You can use next syntax:

update tbl1
join   tbl2
on     tbl1.user_id = tbl2.user_login
set    tbl1.contact_email = tbl2.user_email;

select * from tbl1;

user_id | contact_email  | contact_name
------: | :------------- | :-----------
    123 | test@test.com  | deft
    124 | test3@test.com | deft3

dbfiddle here


MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?

Do these numbers include mysqldump (backup) data or not ?

It would include mysqldump for one simple reason: It uses a DB Connection. That DB Connection, when you do SHOW PROCESSLIST;, has a SELECT SQL_NO_CACHE running on a table at any given moment during the mysqldump.

  Do they include the log file which is being read by slaves or not?

In MySQL Replication, you have two threads: IO Thread and SQL Thread. They are, for all intents and purposes, DB Connections as they are counted (included) under the global status variable Threads_created. DB Connections and Threads are really one and the same.

What then gets counted?

Bytes sent : IO Thread requesting binlog entries from the Master
Bytes recevied : IO Thread reading binlogs entries from its Master
Bytes recevied : SQL thread reading its own relay logs


Why do I get update conflict in this situation instead of just blocking

It is a product defect, which is fixed in SQL Server 2019.
A snapshot write conflict occurs when a snapshot transaction attempts to modify a row that has been modified by another transaction that committed after the snapshot transaction began.
The reason for the incorrect behaviour in your example is somewhat esoteric. The update plan uses something called Rowset Sharing. This means the Clustered Index Seek and Clustered Index Update share a common rowset.
This is an optimization so the Clustered Index Update does not need to locate the row to update via a normal seeking operation. The common rowset is already correctly positioned by the Clustered Index Seek. The update operator performs its work on the "current row" in the rowset.
This causes the erroneous message because the version of the row seen by the seek (the row before the uncommitted change) is shared with the update operator. The update sees that the row it is trying to update has changed and concludes (incorrectly) that an update conflict has occurred.
The correct behaviour can be obtained in many ways. One way to rewrite the update so that rowset sharing is not possible is to force the seek to use a different index. With different access methods, there is no common rowset to share:
UPDATE CT
SET CT.additional = 222
FROM dbo.call_test AS CT WITH (INDEX(ix_Call))
WHERE CT.Id = 1;

A more direct way is to use an undocumented and unsupported trace flag to disable the Rowset Sharing optimization (this is for demo purposes only, do not use it on a real database):
UPDATE dbo.call_test
SET additional = 222
WHERE [Id] = 1
OPTION (QUERYTRACEON 8746);

The plan looks the same as the original (the rowset sharing property is not exposed by default) but it will correctly block instead of throwing an update conflict error.
You can also avoid the error (and retain rowset sharing for the Clustered Index Update) by forcing a wide (per-index) update plan:
UPDATE dbo.call_test
SET additional = 222
WHERE [Id] = 1
OPTION (QUERYTRACEON 8790);

Encountering the bug requires rowset sharing and a base table update that also maintains secondary indexes (narrow, or per-row update).
If this behaviour is causing you real-world problems, you should open a support case with Microsoft.

Josh has correctly answered your second question. I will just add that you can see the nonclustered index maintenance on the Clustered Index Update operator in SSMS -- you need to look in the Properties window and expand the Object node:


When a report is first run in Visual Studio, it creates a cache of the data that is used for all subsequent executions, as long as you don't change parameters. You can refresh the data by either changing parameters, or deleting this cache of the data.

To do so, navigate to your solution file and find the file for the report like YourReport.rdl.data and delete that. The next time you preview your report, it will query the database for the fresh data.


Top 2 rows per partition from an absolute value date difference

This is just a starting point and will likely need some tweaking.

Essentially this will get you the 4 closest capture dates to your specified date (2 closest after and 2 closest before).

You will need to add some logic to your outer select to pick which ones to use, but you will be doing a DATEDIFF on 4 fields instead of all of them.

WITH CTE AS
(
SELECT camera, MIN(r1.capture_date) as 'After', MAX(r2.capture_date) as 'Before'
FROM rs_camera_pictures r1
INNER JOIN rs_camera_pictures r2
    ON r1.location = r2.location
    AND r1.camera = r2.camera
WHERE r1.capture_date > @date
AND r2.capture_date < @date
GROUP BY camera
UNION ALL
SELECT camera, MIN(r1.capture_date) as 'After', MAX(r2.capture_date) as 'Before'
FROM rs_camera_pictures r1
INNER JOIN rs_camera_pictures r2
    ON r1.location = r2.location
    AND r1.camera = r2.camera
INNER JOIN CTE c
    ON r1.location = c.location
    AND r1.camera = c.camera
WHERE r1.capture_date > c.[After]
AND r2.capture_date < c.Before
GROUP BY camera
)


Oracle 19c Standard Edition RPM installation

Restrictions and Guidelines for RPM-Based Installations

Patching Oracle Database software using RPMs is not supported. Please    use the OPatch utility and follow the regular patching process to    apply Oracle Database patches.
An RPM-based Oracle Database    installation is not available for Standard Edition 2.
RPM-based    database upgrades using rpm -Uvh is not supported. For Oracle    Database upgrades, follow the regular upgrade process.
An RPM-based    installation supports the installation of multiple Oracle Database    software versions into different Oracle homes on the same machine.


What's the difference between fetch and fetch next?

There is no difference, and omitting NEXT will not lead to duplicates or different results in any way. NEXT is just an explicit declaration of the default behavior. From the syntax diagram:

FETCH
      [ [ NEXT | PRIOR | FIRST | LAST
                | ABSOLUTE { n | @nvar }
                | RELATIVE { n | @nvar }
           ]
           FROM

NEXT is not the only option, and most of the other options are only available with certain types of cursors. It's hard for me to get into this habit, because I exclusively use fast-forward, single-pass, "firehose" cursors, but best practice is to include NEXT explicitly.

I would look at the query, as I suspect it will also be returning duplicates when there is no cursor involved. Or, whatever the stored procedure is doing, is making it seem like there are duplicates. Another possible explanation is if the query is using WITH (NOLOCK) / READUNCOMMITTED and you are reading the same row twice in an allocation order scan due to data movement.


WITH statement does not persist outside of query

You can actually chain CTEs:

WITH psql as ( /* stuff */ ),
disca as ( SELECT * FROM psql )
INSERT INTO callcenter.campanha_cliente_chamada ( columns )
SELECT col1, col2, col3
FROM   disca;

... which may help you.


How should I be using Users and Access Roles in MongoDB?

Because user security is handled with your application and it's application what contacts the database (not user), you don't need to create every user to the database as a database user.

However, it is still good practice to create a user for every application that uses the database system, so that application can only use those databases what that application needs to access and do only those operations (insert, delete, update,...) what that application needs to do. Nothing else.


Databases created with different collations - why?

The COLLATE clause of CREATE DATABASE can be used to specify a collation different from the Default Server collation.  Also, if the databases were backed up from another server, they would be restored with the collation they had on the source server.

If they are created using CREATE DATABASE and a collation is not specified via the COLLATE keyword, then the default collation would be used.


Difference between clustered index seek and non clustered index seek

A nonclustered index that has the same key(s)* as the clustered index, may still be useful, because the nonclustered index will usually be smaller and denser. Remember, a clustered index includes all in-row data, so it is normally the widest (least dense) index possible.

* The same key columns, in the same sequence, sorted the same way (asc/desc).

For a singleton seek (a seek using an equality predicate into a unique index), the main difference is the presumed greater chance of the index page(s) needed to find that single record being already in memory. All things being equal, a nonclustered index with 100 pages has a better chance than a clustered index stored on 10,000 pages.

For any other kind of seek except a singleton, there will be a scanning component as well. The scanning portion will also benefit from the greater density of the nonclustered index (more rows per page). Even if the pages must come in from persistent storage, reading fewer pages is faster.

You should check the system documentation, ask the person that added the index, and/or look for code comments to understand why this index exists. You also need to check the complete index definitions carefully, including compression, and look at index maintenance scripts. There could be other special reasons for having the nonclustered index (e.g. ability to rebuild online).


Does PostgreSQL write first to WAL and then to data files?

Your understanding is not correct.

  Specifies whether transaction commit will wait for WAL records to be
  written to disk before the command returns a "success" indication to
  the client

means that the transaction will be marked as committed without waiting for the success message.  This may come somewhat later (usually the time difference is very-very small), do to some magic done by the storage (caching, for example).  This means that you issue the COMMIT;, then the WAL data is written to the cache and only after this written actually to the disk.  Writing to the data files may happen later, but your data have been persisted at this point.

Depending on the type of the cache and other things, there is a short time window with synchronous_commit = off when you may lose data if power is lost, for example.


Best database and table design for billions of rows of data

This is exactly what I do every day, except instead of using the hourly data, I use the 5 minute data. I download about 200 million records everyday, so the amount you talk about here is not a problem. The 5 minute data is about 2 TB in size and I have weather data going back 50 years at an hourly level by location. So let me answer you questions based on my experience:

Don't use NoSQL for this. The data is highly structured and fits a
relational database perfectly.
I personally use SQL Server 2016 and I have no problems applying computations across that volume of data. It was originally on a
PostgreSQL instance when I started my job and it couldn't handle the
volume of data as it was on a small AWS instance.
I would highly recommend extracting the hour portion of the date and storing it separate from the date itself. Believe me, learn from my mistakes!
I store the majority of data list-wise (DATE,TIME,DATAPOINT_ID,VALUE) but that is not how people will want to interpret the data. Be prepared for some horrendous queries against the data and vast amounts of pivoting. Don't be afraid to create a de-normalized table for result sets that are just too large to compute on the fly.

General tip: I store most of the data between two databases, the first is straight-up time series data and is normalized. My second database is very de-normalized and contains pre-aggregated data. As fast as my system is, I am not blind to the fact that users don't even want to wait 30 seconds for a report to load – even if I personally think 30 seconds to crunch 2 TB of data is extremely fast.

To elaborate on why I recommend storing the hour separate from the date, here are a few reasons why I do it that way:

The way that the electrical data is presented is by Hour Ending – therefore, 01:00 is actually the average of the electrical power for the previous hour and 00:00 is Hour Ending 24. (This is important because you actually have to search for two dates to include the 24 hour value – the day you are looking for plus the first mark of the following day.) However, the weather data is actually presented in a forward manner (actual and forecast for the next hour). In my experience with this data, consumers wish to analyze the effect that the weather has on the power price/demand. If you were to use a straight-up date comparison, you would actually be comparing the average price for the previous hour versus the average temperature for the following hour, even though the time stamps are the same. Storing the hour separate from the date allows you to apply transformations to the time with less performance impact than you would see applying a calculation to a DATETIME column.
Performance. I would say at least 90% of the reports that I generate are graphs, normally plotting the price against the hour either for a single date or for a range of dates. Having to split out the time from the date can bog down the speed of the query used to generate the report depending on the date range that you want to see. It is not uncommon for consumers to want to see a single date, Year-on-Year for the past 30 years (in fact for weather this is required to generate the 30 year normals) – this can be slow. Of course you can optimize your query and add indexes, and trust me I have some insane indexes that I would rather not have but it makes the system run fast.
Productivity. I hate having to write the same piece of code more than once. I used to store the date and time in the same column, until I had to write the same query over and over again to extract the time portion. After a while I just got sick of having to do this and extracted it to its own column. The less code you have to write the less chance there is of an error in it. Also, having to write less code means that you can get your reports out faster, nobody wants to be waiting all day for reports.
End users. Not all end users are power users (i.e. know how to write SQL). Having the data already stored in a format that they can bring into Excel (or other similar tool) with minimal effort will make you a hero in the office. If the users cannot access or manipulate the data easily, they will not use your system. Believe me, I designed the perfect system a couple of years ago and nobody used it because of this reason. Database design is not just about adhering to a predefined set of rules/guidelines, it is about making the system usable.

As I said above, this is all based on my personal experience, and let me tell you, it has been a hard few years and a lot of redesigns to get to where I am now. Don't do what I did, learn from my mistakes and make sure you involve the end users of your system (or developers, report authors etc...) when making decisions about your database.


Partial access MongoDB databases without replication. Is that possible?

You haven't mentioned what driver you will be using, so I can't give you a direct link to the specific option in your language of choice, but yes there is an option to return partial results from a sharded cluster if there are shards down (I still don't advocate running without replica sets, but that's a different discussion).

You can set it in the shell as follows (sample values, will work for findOne also):

db.collection.find({}, {}, 0, 0, 0, DBQuery.Option.partial)

Each of the drivers should have support for setting this option, either directly or similar to the shell via an option.  See the partial option toward the bottom of the node.js cursor documentation for example or the DBCursor setOptions and associated QUERYOPTION_PARTIAL in Java.  For reference, the relevant feature requests to add partial results options are SERVER-2020 and SERVER-4610


How to make a table with fields that are other tables in MySQL?

This is a really basic example of normalization.

Typically you set up a foreign key relationship.  In your example you would have two tables:

Artists - id, name, etc...

Songs - id, artist_id, name, etc...

You would retrieve needed data from Artists using a JOIN.  You should look into some entry level SQL tutorials.  JOINs are the bread and butter of SQL, and what makes it a relational database.


Query performance degraded after upgrading Postgres

Which index to use?
While your WHERE conditions are not very selective, the current query plan makes a lot of sense with ORDER BY activity_count DESC LIMIT 101. See:

How to search a table with 80 million records faster?

However, your predicates strike me as pretty selective:
WHERE (search_index) @@ (to_tsquery('pg_catalog.english', '''1234567890'':*') AND active = true

That's unless your example '1234567890' is misleading. (Are you sure you want the single quotes in the search term?) It's obviously not the actual query you used. Unmatched parentheses. In any case, you probably want the  "simple" text search configuration (plus some improvements):
WHERE  search_index @@ to_tsquery('pg_catalog.simple', '''1234567890'':*')
AND    active

See:

Get partial match from GIN indexed TSVECTOR column

Statistics
So it seems your statistics are not up to date.

after an upgrade from 11 to 12, a few queries starting performing
horribly. They went from taking ~1 second to ~5 minutes. I tried
rebuilding all indexes, vacuuming, and all usual Postgres low-hanging
fruit, but performance is still terrible.

Why is it only using the user_activity_count index and not the more efficient GIN index?

Did you also add ANALYZE to your basket of all usual Postgres low-hanging fruit? You certainly didn't mention it. Consider instructions in the manual:

Statistics
Because optimizer statistics are not transferred by pg_upgrade, you
will be instructed to run a command to regenerate that information at
the end of the upgrade. You might need to set connection parameters to
match your new cluster.

If your DB is big, you may be interested in vacuumdb with the option --analyze-in-stages. (That ship may have sailed for the case at hand.) The manual:

This option is useful to analyze a database that was newly populated
from a restored dump or by pg_upgrade. This option will try to create
some statistics as fast as possible, to make the database usable, and
then produce full statistics in the subsequent stages.

Related:

Optimizing slow performance of simple SELECT query


Problem using sample schemas by Oracle in Oracle APEX

You can unrestrict it.

You just need to run the APEX_SITE_ADMIN_PRIVS.UNRESTRICT_SCHEMA proc.

EXEC FLOWS_XXXXXX.APEX_SITE_ADMIN_PRIVS.UNRESTRICT_SCHEMA(p_schema => 'SCOTT');

Replace FLOWS_XXXXXX with the name of your APEX schema.

You can also check which schemas are restricted:

SELECT * FROM flows_XXXXXX.wwv_flow_restricted_schemas;

Documentation link.


How do I reset the postgres password for PostgreSQL on Windows?

From the 9.0 FAQ, I lost the database password. What can I do to recover it?:

I lost the database password. What can I do to recover it?
You can't. However, you can reset it to something else. To do this, you

Edit pg_hba.conf to allow trust authorization temporarily - go here for its location
Reload the config file (pg_ctl reload)
Connect and issue ALTER ROLE postgres WITH PASSWORD 'newpassword'; to set the new password (for first time users note the semi-colon)
Edit pg_hba.conf again and restore the previous settings
Reload the config file again


select table and replace column fields by value from multiple tables in mysql

You could change sample_id, like below:

DROP TABLE TableData_elements;
CREATE TABLE TableData_elements
 (Dataid INT,
 sample_id CHAR(10),
 sample_title TEXT
 );
INSERT INTO TableData_elements VALUES
('400','1','Fname'),
('400','2','Sname'),
('400','3','Lname'),
('400','4_1','first'),
('400','4_2','second'),
('400','4_3','third');

OR you could change query at the end:

SELECT
  CONCAT( 'SELECT ',
          GROUP_CONCAT(CONCAT('sample_',
                              te.sample_id,
                              CASE WHEN teo.option_id>0 THEN
                                    CONCAT('_',teo.option_id)
                                    ELSE '' END,
                              ' ',
                              sample_title)),
          ' FROM TableData_400' )
FROM TableData_elements te
LEFT JOIN TableData_elements_options teo ON
        teo.Dataid=te.Dataid and
        teo.sample_id=te.sample_id ;

EDIT: (also tak not of optionss):

SELECT
  CONCAT( 'SELECT ',
          GROUP_CONCAT(CONCAT('sample_',
                              te.sample_id,
                              CASE WHEN teo.option_id>0 THEN
                                    CONCAT('_',teo.option_id)
                                    ELSE '' END,
                              ' ',
                              CASE WHEN teo.option_id>0 THEN
                                 teo.optionss
                              ELSE sample_title END)),
          ' FROM TableData_400' )
FROM TableData_elements te
LEFT JOIN TableData_elements_options teo ON
        teo.Dataid=te.Dataid and
        teo.sample_id=te.sample_id ;


Troubleshooting high CPU usage from postgres and postmaster services?

You can match a specific Postgres backend ID to a system process ID using the pg_stat_activity system table.

SELECT pid, datname, usename, query FROM pg_stat_activity; can be a good starting point.
Once you know what queries are running you can investigate further (EXPLAIN/EXPLAIN ANALYZE; check locks, etc.)


You can save the index creation DDL statement and delete the indexes, to save more space before any archival path you choose. There's more advice on shrinking and fill factor here: http://www.brentozar.com/archive/2010/02/how-to-really-compress-your-sql-server-backups/

I've used option 2 to get better results than with the SQL built-in backup compression. Always start with DBCC CHECKDB ! Also take into consideration that your restore time will go up if you go on this path, as it takes a while to (de)compress with 7-Zip's best compression settings.


RECOVERY MODEL changed on a database, no record in Default Trace

Answering my own post as I found additional information:
The trace log had no indication of Object: Altered for that database, which happened to be msdb (might be important here). In the SQL logs, I found indications that someone had applied updates, and after the first instance start-up, there was indication that msdb had an upgrade script applied then setting of SIMPLE recovery, along with creation of several synonyms.


Adding LIMIT to the query often makes Postgres choose a different query plan. If statistics or cost estimates stray too far from actual data distribution / actual costs, you may end up with a slower query, even though Postgres reckoned it would be faster. Irregular data distribution may cause the query planner to misjudge the selectivity of WHERE conditions.

In particular, increasing the STATISTICS target for status, account_id, created_at and id might help.

Details:

How can I avoid PostgreSQL sometimes choosing a bad query plan for one of two nearly identical queries?

Aside from that, a (partial) multi column index may help. Depends on the complete situation. Which of your WHERE conditions are constant? How can they vary? Is some additional maintenance cost and disk space acceptable for a special index?

For example:

CREATE INDEX messages_special_idx ON messages (account_id, created_at, id)
WHERE status = 'unread'
AND   created_at >= '2014-01-01 01:00:00';

Run ANALYZE messages; before you try your queries again.


A Cold Backup is making a copy of the files with the database closed. These commands are both for hot backups using RMAN.  They are both preferred over cold backups due to their flexibility.

The difference between the two commands is whether or not archive logs are backed up.  If you need be able to recover to any point between your backups or anytime after your most recent backup, then you will need to be in archivelog mode.  If you are in archivelog mode then something needs to be done to backup those archive logs and use should use the PLUS ARCHIVELOG syntax.

It is advisable to periodically test a recovery as well to make sure your backups are working and you can recover.


SQL design for hierarchical structure where parent unit should not exist without subunits

Are you saying that you have troubles with FOREIGN KEYs.  If so, don' t use them.

If you are saying that a POST exists briefly without any PAGEs, then put the generation of both the POST and the PAGEs inside a transaction (BEGIN...COMMIT).


How can a session be blocked by an session that is idle?

It would appear the idle session has a lock on at least one record required by the blocked query.  The user has already executed a query that acquired a lock.  Normally the lock will be held until they commit or rollback the transaction.

I've run into this a few times when a developer went for lunch while in the middle of updating or inserting data.  The lock was removed shortly after they returned from lunch.

If their session times out or you kill their session, the transaction should rollback and release the lock.

If you can determine what location they are connected from you may be able to contact them and request they commit or rollback their transaction.


How do I add a constraint after I've truncated a table?

Use WITH NOCHECK instead of WITH CHECK to add a constraint that conflicts with existing data. https://stackoverflow.com/questions/529941/whats-the-difference-between-with-check-add-constraint-followed-by-check-constr

Perhaps you are getting the error because you "2. Trucate a table." and not all of them? Maybe you are removing the Authors or the Books and are leaving the records in AuthorBooks that references these other tables.


You can use the technique described in:

http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

to mimic:

row_number() over (partition by ... order by ...)

In your case that would be something like:

SELECT user_id, comment, row_number
FROM (
    SELECT @row_number:=CASE WHEN @user_id=user_id
                             THEN @row_number+1
                             ELSE 1
                        END AS row_number
         , @user_id:=user_id AS user_id
        , comment
    FROM t
       , (SELECT @row_number:=0,@user_id:='') AS u
    ORDER BY user_id, comment
) as v
WHERE row_number <= 2;


SQL Server backup very large compared to table size

It is interesting that shrinking reduced the backup size. Backup doesn't include unused extents. And such a massive reduction in backup size can't be explained by "lots of half-full pages" alone.
This was apparently some very special situation, in the sense that a shrink did indeed reduce the backup size to that level. One theory I have is that there are heap tables that had lots and lots of unused pages (but still being considered as used from the backup's perspective). I.e., you have some table which is being heavily inserted into and deleted from, and the delete pattern causes the free space to never being deallocated, so to speak.
If you want to aid in the value for this thread for future readers, you can run something like below to see if you have heap tables. We can't say now if potential heap tables has vast amount of free space since the shrink was performed already, but at least we would know if you have heap tables.
SELECT OBJECT_SCHEMA_NAME(OBJECT_ID), OBJECT_NAME(object_id), type_desc
FROM sys.indexes
WHERE type_desc = 'HEAP'
AND OBJECTPROPERTY(object_id, 'IsMsShipped') = 0

Perhaps LOB data not being compacted/deallocated could also explain this, but that is just me thinking out loud...


Just a guess as there isn't enough information to really figure this out but it could be network latency. You're saying that local connection to local DB is fast but local to remote DB is slow. All other things being equal the only difference is the network connection between you (local) and the remote DB.

Generally AWS is pretty fast and has a stable network but you might notice latency issues for more chatty protocols. I'm not too familiar with the wire protocol for SQL Server but I know that Oracle for example is a relatively chatty protocol so it could be similar. As such you'll see much better performance when the network connection between the client and the server (the database) are on a low latency network with a minimal amount of hops.

Another issue could be if your local internet connection doesn't have much bandwidth (say a 1 Mb/s DSL) and you try streaming back a lot of rows from the remote database.

You should repeat your test from a Windows instance that's close to the RDS instance. I don't think you're allowed to remote into the RDS instance itself but you should be able to spin up a Windows instance that's in the same availability zone. That should be close enough to what it's like in a real world deployment.

Also, if you're planning on using the remote Amazon RDS instance as the database for an application that's not running on Amazon then you really need to test out the performance in advance. The intended use case is having your application running on EC2 so that you're on a low latency network with the DB. It would still work remotely but the performance will we wildly different.


Howto implement correctly Businness Logic in database

Best practice...

A database is a place to store data.  It is the 'source of truth'.

The application is the place for
* interpreting the data
* "Business logic"
* Formatting and pretty-printing of output
* Cleansing the data as it is stored into the database.

It is often wise (especially in more complex systems) to have a "database layer" in which the "business" info is transformed to/from "database" info.  On the database side, there is "INT" and "FLOAT"; on the business side, there are other terms for numeric things.  The business may say "store this"; the layer may turn that into one INSERT, or split the columns across two tables, or normalization plus inserts, or INSERT .. ON DUPLICATE KEY UPDATE .., or INSERT IGNORE, or an INSERT plus augment a summary table, or ...

Yes, some of those things could be done with TRIGGERs and FOREIGN KEY "cascading constraints", etc.  But I would rather put all the logic in a single place.  I find triggers clumsy to code.  I find FKs limted in capability.


Providing an ad-hoc report facility for business users in SQL Server 2008 R2

You're probably looking for Report Builder 3.0 and/or Power Pivot.

  Microsoft SQL Server 2008 R2 Report Builder 3.0 provides an intuitive
  report authoring environment for business and power users. It supports
  the full capabilities of SQL Server 2008 R2 Reporting Services.

Your Excel ninja users will likely prefer Power Pivot, your more typical tabular report folk will feel right at home with Report Builder. There's a useful summary of the differences between Report Builder and the BIDS Report Designer at Designing Reports in Report Designer and Report Builder 3.0 (SSRS).


Why does assigning FOR XML to an XML variable warn about CONVERT_IMPLICIT(XML)?

The return type of the FOR XML PATH expression is NVARCHAR(MAX), not XML.

I was being confused by the fact that SSMS renders any column named XML_F52E2B61-18A1-11d1-B105-00805F49916B as if it has the XML data type. If I run the following command, I get a clickable XML result cell in SSMS:

SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml');

I was able to figure out the actual type by getting SQL Server to spit out a type-conversion error:

SELECT SQL_VARIANT_PROPERTY((SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml')), 'BaseType');

Msg 206, Level 16, State 2, Line 1
Operand type clash: nvarchar(max) is incompatible with sql_variant

To fix the warning completely, simply add the TYPE directive to the FOR XML clause:

SET STATISTICS XML ON;
DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml'), TYPE);


SQL Server 2008 having issues with HAVING clause

If you are struggling with "Group By" queries than @Kenneth Fisher's answer might help or you need to provide more details same as asked by @dnoeth

I wanted to point out that all values in the image table are already less than -5 or 5. Because values are negative so -42,-71,-100 are less than -5 or 5. (In your question you said -5 but in query you have 5)

Second, about DateDiff returned result.
Syntax = DATEDIFF ( datepart , startdate , enddate )
so if enddate is today and if DateDiff result is positive, it indicates past duration,
if DateDiff result is negative, it indicates future duration.

If you are interested in order shipped in past 5 minutes you should look for

DATEDIFF(MINUTE,ShipDate,GETDATE()) > 0 AND DATEDIFF(MINUTE,ShipDate,GETDATE()) <= 5

(I am guesing this based on your column name, mins_elapsed indicates that you are interested in past duration.)

Third, In DateDiff you are considering only time values,
examine below queries,

DECLARE @ShippedYesterday DateTime, @ShippedToday DateTime;
SELECT @ShippedYesterday = DATEADD(DAY,-1,GETDATE()), @ShippedToday = GETDATE();
SELECT @ShippedYesterday, @ShippedToday;
SELECT convert(varchar(8), @ShippedYesterday, 114) AS YesterdayShippedTime, convert(varchar(8), @ShippedToday, 114) As TodayShippedTime;
SELECT DATEDIFF(MINUTE, convert(varchar(8), @ShippedYesterday, 114), convert(varchar(8), @ShippedToday, 114));
SELECT DATEDIFF(MINUTE, @ShippedYesterday, @ShippedToday);

second last query returns 0 while the last query returns > 1000.

If you store shipping date in your database than you should restrict it to today's date before using DateDiff with Current date.

Right now you are probably comparing today's 4pm to yesterday's or day before yesterday's 5pm and you get result -60 minutes.


SQL Server 2008 R2 : LDF file size not increasing

I wouldn't expect a copy operation to increase file sizes unless the file size was too small to begin with.  SQL Server will first use unallocated space within data files before growing the file.  Similarly, if the log file is large enough for the operation, that file shouldn't grow either.

Also regarding log space usage, some operations can be minimally logged to reduce logging requirements.  Whether or not your INSERT...SELECT is fully logged depends on the database recovery model and indexes on the target table.


Unless the MySQL lords know better than me, it's equivalent to the query:

UPDATE engine.storage
SET bytes_inuse = bytes_inuse + 40000
WHERE (size_bytes - bytes_inuse) >= 40000;

I imagine in your real query you have a more restrictive WHERE clause etc.


Add array table constraints, empty or null if other column is false

The constraint should be written using an OR like (NOT A) OR B

ALTER TABLE jobs
ADD CONSTRAINT error_message_is_null_if_failed_is_false
   CHECK ( NOT ( status IN ('UNSTARTED', 'STARTED', 'COMPLETED')) OR ( error_message IS NULL OR error_message = '{}' ));

INSERT INTO jobs VALUES ('UNSTARTED', null); -- no error
INSERT INTO jobs VALUES ('STARTED', null); -- no error
INSERT INTO jobs VALUES ('FAILED', null); -- no error
INSERT INTO jobs VALUES ('COMPLETED', null); -- no error

INSERT INTO jobs VALUES ('UNSTARTED', '{}'); -- no error
INSERT INTO jobs VALUES ('STARTED', '{}'); -- no error
INSERT INTO jobs VALUES ('FAILED', '{}'); -- no error
INSERT INTO jobs VALUES ('COMPLETED', '{}'); -- no error

INSERT INTO jobs VALUES ('UNSTARTED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('COMPLETED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('STARTED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('FAILED', '{"error message"}'); -- no error


SELECT...FOR UPDATE - Will only an UPDATE release the lock?

Don't know about MySQL, but in Postgresql, the lock is only valid for the duration of the current transaction, so:

you must have started a transaction
the lock will be released if you commit or rollback the transaction

If you don't start an explicit transaction, then the lock will be released right away.


How long does it take for Oracle Flashback Database to restore the data?

Flashback Database

Flashback Database relies on flashback logs (stored in the flash recovery area) to restore a database to a particular point in time. The time taken to restore a database using Flashback Database would therefore be dependent on the amount of flashback log created between the restoration period and the current point in time. See this link for more information & limitations.

I'll add that it's lots quicker than a point-in-time restore using RMAN (I imagine that's what you were eluding to when you asked the question in the first place), but there is also the extra overhead in writing flashback logs that needs taking into account.


Yes, columns in the select-list cannot reference other columns by alias. This is standard SQL.

MySQL does not guarantee the order of evaluation of the select-list from left to right, so it would not be a recommended practice to use variables like you show.

Another workaround is to do computations and create column aliases in a derived table, and then reference them in the outer query. This is standard SQL and is reliable.

SELECT old_price, old_price + 100 AS total_price
FROM (
    SELECT sum(price) AS old_price FROM sales
) AS t;


Merge Join SSIS Throwing Error Despite Sort and Matching Meta

There is a setting where you can tell the Merge Join that the incoming information is sorted, although in the documentation, the Sort task is supposed to properly set these properties.  I wonder if it's because you have other tasks after the sort, and the IsSorted property is getting set back to False.

  There are two important sort properties that must be set for the source or upstream transformation that supplies data to the Merge and Merge Join transformations:

  The IsSorted property of the output that indicates whether the data has been sorted. This property must be set to True.

    Important

    Setting the value of the IsSorted property to True does not sort the data. This property only provides a hint to downstream components that the data has been previously sorted.

  The SortKeyPosition property of output columns that indicates whether a column is sorted, the column's sort order, and the sequence in which multiple columns are sorted. This property must be set for each column of sorted data.

  If you use a Sort transformation to sort the data, the Sort transformation sets both of these properties as required by the Merge or Merge Join transformation. That is, the Sort transformation sets the IsSorted property of its output to True, and sets the SortKeyPosition properties of its output columns.

  However, if you do not use a Sort transformation to sort the data, you must set these sort properties manually on the source or the upstream transformation.

The documentation tells you how to set those settings manually.


Open a Oracle 7.3.x database in Oracle 11

No, you have to upgrade in stages, which wouldn't actually be possible now because Oracle 8.x and 9.x are now unsupported and the software is unavailable (unless you have an Oracle support contract and ask them for copies of the software). Read the Oracle® Database Upgrade Guide
11g Release 2 (11.2) for more information.

Your best bet, assuming you have the 7.x database up and running, is to use exp to export the schemas you require, then import them into a fresh 11.2.x database.