PostgreSQL Automatic Failover (Trigger File Creation)?

repmgr 2.0 supports automatic failover.

However, I strongly advise you not to use automatic failover. It's generally a much better idea to use manually triggered automated failover, where the failover process is scripted/automated, but a human has to say "Yup, master's dead, do it".

If you do use automatic failover you need to make it a routine part of your operations, where you test it regularly. Otherwise it will bite you when you least expect it and can least afford it.

It is also vital that you consider fencing/STONITH as part of your failover solution, to prevent two diverging masters.


If view A is built atop other views and tables, does a user need permission to only view A, or everything down?

Do you need to grant permissions to every object built underneath the
  views? Or just the view itself? Or either one?

Permissions need to be granted to the person executing the query for every object referenced by the view. Except if they are owned by the view owner. In that eventuality Ownership Chains come into play.

  When an object is accessed through a chain, SQL Server first compares
  the owner of the object to the owner of the calling object. This is
  the previous link in the chain. If both objects have the same owner,
  permissions on the referenced object are not evaluated.

The below creates two tables. T1 (owned by TestUser1) and T2 (owned by TestUser2) and a view that references both tables and is owned by TestUser1.

A third user is granted SELECT permissions on the view but none of the underlying objects. Selecting from the view initially fails for them. However it succeeds after they are granted select permissions on T2. There is no need to grant them permissions on T1 as this is owned by the same user as the view.

Setup

CREATE USER TestUser1 WITHOUT LOGIN;
CREATE USER TestUser2 WITHOUT LOGIN;
CREATE USER TestUser3 WITHOUT LOGIN;

CREATE TABLE dbo.T1(C INT);

ALTER AUTHORIZATION ON dbo.T1 TO TestUser1;

CREATE TABLE dbo.T2(C INT);

ALTER AUTHORIZATION ON dbo.T2 TO TestUser2;

GO

CREATE VIEW dbo.V
AS
SELECT T1.C
FROM dbo.T1 CROSS JOIN dbo.T2

GO

ALTER AUTHORIZATION ON dbo.V TO TestUser1;

GRANT SELECT ON dbo.V TO TestUser3;

Test 1 (Fails)

EXECUTE AS USER='TestUser3';

SELECT *
FROM dbo.V;

REVERT;

  The SELECT permission was denied on the object 'T2', database 'Foo',
  schema 'dbo'.

Test 2 (Succeeds)

GRANT SELECT ON dbo.T2 TO TestUser3

EXECUTE AS USER='TestUser3';

SELECT *
FROM dbo.V;

REVERT;

Cleanup

DROP TABLE dbo.T1, dbo.T2
DROP VIEW dbo.V
DROP USER TestUser1
DROP USER TestUser2
DROP USER TestUser3


should I shrink the filegroup? how? SQL Server

Why would you?

I mean, the disk is there for you to use, and as I guess the server in which the database runs is devoted to it, there is no real reason to give the space back to the OS just to have the OS allocate it again to the same datafiles in the future.

Shrinking is in general not a really good idea, when you do it, you risk facing fragmentation issues which will hurt your performance.

In case you have a good reason to do it DBCC SHRINKFILE(yourfile) is what you need.


Why is a Star Schema more normalized than a 3NF Schema?

As @ypercube stated this seems to be a typo and should be changed to "more de-normalized schemas". One of the following paragraphsinthe Oracle manual states:

Snowflake schemas normalize dimensions to eliminate redundancy. That is, the dimension data has been grouped into multiple tables instead of one large table. For example, a product dimension table in a star schema might be normalized into a products table, a product_category table, and a product_manufacturer table in a snowflake schema.

This product dimension table of the star schema described here is not in third normal form  but are results of joining (denormalize) some tables of the snowflake schema.

I found aricles on the web that describe why a star schema is not in 3rd normal form link link


How to create Polygon from Point datatype in MySQL?

SELECT aois_id,
polygon(
   linestring(WS_A, WS_B, WS_C, WS_D, WS_A)
) as geom
FROM aois;


Reuse a complex non-deterministic expression in SELECT and WHERE clauses

This can't help with the determinism issue, but CROSS APPLY is a good tool for big expressions that need to be reused:

SELECT TOP 10 team_name, CxA.Score_Points
FROM teams t
CROSS APPLY (SELECT Score_Points = <big Expression, refer to t>) CxA
 WHERE CxA.Score_Points >= 100
 ORDER BY CxA.Score_Points DESC

CROSS APPLY expressions get calculated for every row and can be referred to in any part of the query.


I feel my answer may lack what you are looking for but with out more context here are a couple options:

Use the mysqldump command.

mysqldump -u user -pPassword   --no-create-db --no-create-info --where="primary_key=N"

will give you a sql insert command which you can use to reconstruct that row.

run a query like

select * into outfile '/tmp/rowdump.data' from table where primary_key=N

This will create a file /tmp/rowdump.data in tab separated format.  This can be reimported with

load data infile '/tmp/rowdump.data' into table `table`;

If these don't suit your needs please elaborate on your use case on what you are trying to accomplish.


Is OPTION (RECOMPILE) used in production environments?

OPTION(RECOMPILE) is used in real word production scenarios. I've employed it to address parameter sniffing and optimize kitchen sink queries. It might be the answer for your issue but the symptoms suggest OPTIMIZE FOR UNKNOWN (same as local variables) may address the problem as well.
I certainly would not avoid an option just because a bug once existed, and it was fixed several years ago. The main risk with OPTION(RECOMPILE) is when it's used inappropriately, such as high-frequency queries.


This code assumes that there will only be one record for any given date. It also does not check to make sure that dates are consecutive, if that's important.

SELECT the_date, my_state as the_state
  FROM (SELECT the_date
              ,if(@laststate = the_state, '', the_state) as my_state
              ,@laststate := the_state
          FROM myTest
         ORDER BY the_date
       ) sq
 WHERE my_state <> ''
 ORDER BY the_date DESC
;

First, in the subquery, it sets the state to an empty string if it's the same as the state in the previous row. Then, we select only those records form the subquery where the state is not an empty string.

See in db-fiddle.com.


how to minimize the data scanned on my database using index?

You should give CREATE TABLE statements or at least info about what is indexed and how. But what I can gather from what you posted:

JOIN on functions is bad, not indexable. If you cannot get rid of those substrings, you might store them precomputed in separate columns and index those (at least the dstchannel ones should be enough, because of current join order).

You can extend index on accountcode with dstchannel for better selectivity and calldate to get rid of the filesort.

The OR over a.channel is not very indexable as it is, but as it uses prefix, you might split your query into two, each one using one part of that OR (and "glue" them with UNION) and replace calldate with channel in that index, it will mean doing filesort, but can be better if those conditions on channelare selective enough (you will have to test that probably to find out which variant is better).


Postgres - Revoke database acess from user, but allow replication acess

I denied the acess by IP on the pg_hba.conf file.


This kind of dimension is called Role-Playing Dimension.
This is tipical of Multi-Dimensional SSAS modeling.
However SSAS Tabular modeling does not support role-playing dimensions;  you can overcome that limitation by using DAX, enabling inactive relationships for specific calculations (you can define multiple relationships between the same tables in Tabular). However, if you want to offer navigation in different role-playing dimensions to the user, the best practice for both Multidimensional and Tabular is to import the same table multiple times, renaming data and metadata.


SQL Server RDS Database Restore from S3 stuck in Restore Mode

It's not stuck, it's just still in restore mode because you used @with_norecovery = 1. You need to restore database with recovery to finish the process.


Inserting to table: Insert rows with Left Outer Join or Insert rows then Update?

When Orders and Deliveries are both properly indexed, the second query will generally perform better. There are two main considerations here:

Generally speaking, and without seeing the details of your tables - what probably happens with the INSERT/UPDATE construct is that it will first allocate a number of pages in the database in the initial INSERT. But when you UPDATE those records, the added new information has to fit inside those existing rows in the OrderInformation table's pages, which causes page splits, which are very I/O intensive and can fragment your table and/or indexes.

The second query (single INSERT using LEFT JOIN) is considerably better practice, since it minimizes the locking requirements. Imagine if another simultaneous process creates a new row in Orders and then a related row in Deliveries between the time of the INSERT and the UPDATE statements in the first example - you'll get a foreign key constraint violation unless you've put all of this in a potentially large transaction, which itself implies a bit of overhead.


How can i deploy ssrs report one server to another server

The URL you are currently using is for the report manager.
Report RDLs are deployed to the report server URL, so change the path to http://localhost/reportserver.

Alternatively, you can find the correct URL for your server configuration in the Reporting Services Configuration Manager > Web Service URL (generally located in the Start menu > All Programs > Microsoft SQL Server {Version} > Configuration Tools).

For more information: TechNet: How To Publish Reports


I would be willing to bet that your database is full recovery and that you are not taking transaction log backups.  Log backups are required for the transaction log to clear and reuse space.  Otherwise it will just continue to grow and grow.


How to check connection string in SSMS2012?

Within SSMS, I'm not sure there's an easy way to do that.  But a simple query will return you the information (without the password of a connection string, obviously):

select
    'data source=' + @@servername +
    ';initial catalog=' + db_name() +
    case type_desc
        when 'WINDOWS_LOGIN'
            then ';trusted_connection=true'
        else
            ';user id=' + suser_name()
    end
from sys.server_principals
where name = suser_name()

Note, db_name() will return the current database name if there is no parameter specified, so that is dependent on your current scope.


Can't find sun.jdbc.odbc package in Java version 8

Oracle has mentioned related to this in the Tec documents

  JDBC-ODBC Bridge The JDBC-ODBC Bridge allows applications written in
  the Java programming language to use the JDBC API with many existing
  ODBC drivers. The Bridge is itself a driver based on JDBC technology
  ("JDBC driver") that is defined in the class
  sun.jdbc.odbc.JdbcOdbcDriver. The Bridge defines the JDBC sub-protocol
  odbc.

And I think this is the reason why its not found in the Java version 8

  Status of the JDBC-ODBC Bridge The JDBC-ODBC Bridge should be
  considered a transitional solution; it will be removed in JDK 8. In
  addition, Oracle does not support the JDBC-ODBC Bridge. Oracle
  recommends that you use JDBC drivers provided by the vendor of your
  database instead of the JDBC-ODBC Bridge.

Source where the information was found TechNotes,Lance Andersen's Blog Removal of the JDBC-ODBC Bridge from Java SE 8 JDK

I suggest you try the following solutions

Use a third-party database driver
Use a previous version of java


I would try to understand why your query leads to a deadlock. It would be useful to look at deadlock graph info. Try enabling trace flags (1204, 1222) to get more details from the SQL Server log.
Since you are using the row level locking and get deadlocks I suspect that PrintBatchId might have a large number of records associated. So SQL Server has to escalate the lock to page/table level. And as a result, you get a deadlock with an update on different PrintBatchId. (BTW. Do you have an index on PrintBatchId? W/o proper index row level locking won't work). Try to make query filter smaller.
If you can't make your filter more precise I would suggest to catch deadlocks and retry update attempt. Here is a sample code:

DECLARE @Retries INT = 5
    , @ErrorMessage nvarchar(max)
    , @ErrorLine INT
    , @ErrorSeverity INT
    , @ErrorNumber INT

WHILE (@Retries > 0)
BEGIN
    BEGIN TRY
        --UPDATE Command....
        SELECT @retries = 0
    END TRY
    BEGIN CATCH
        SELECT
              @ErrorMessage = ERROR_MESSAGE()
            , @ErrorLine    = ERROR_LINE()
            , @ErrorSeverity= ERROR_SEVERITY()
            , @ErrorNumber  = ERROR_NUMBER();

        IF (@ErrorNumber = 1205 AND @Retries > 0) -- If Deadlock, then retry
            SELECT @ErrorMessage = 'Execution Failed due to a deadlock. Retrying... ';
        ELSE
        IF (@ErrorNumber = 1205 AND @Retries <= 0) -- If Deadlock, and max number of retries reached. Fail.
            SELECT @ErrorMessage = 'Execution Failed due to a deadlock after max number of retries ... ' + CHAR(13) + ' Original Error: Msg. ' + CAST(@ErrorNumber AS VARCHAR(MAX)) + ', ' + @ErrorMessage;
        ELSE
            SELECT @ErrorMessage = 'Execution Failed: Msg ' + CAST(@ErrorNumber AS VARCHAR(MAX)) + ', ' + @ErrorMessage;

        SELECT @Retries-=1;

    END CATCH;
END


Alter default privileges for a certain column

"alter default privileges" applies to newly (in the future) created tables, not newly created users/roles.  It generally doesn't make much sense to grant a column specific privilege on a table which does not yet exist, as it might not even have that column once the table is created.

If new users are granted the roleGroup role, then they will automatically have the desired permission on the existing column of the existing table.

I don't think there is a way to automatically grant a role to newly created users, you will have to remember to do it yourself, or script it.


If the only difference is local vs remote, then the problem has to be your round trip times.  The actual internal query execution is not aware of the type of connection used.  However I also wonder if you are also running your dev system on a different database.  In that case, caching could be very different and could also make a significant difference.

I see a few ways to solve this problem.  The first is just to have a dev copy of the database for the site on your dev system.  This data could then be sanitized so that if your dev system is compromised or lost (supposing it is a laptop for example) the data exposure is minimal.  Then you can test performance on a system which can do caching specific to your dev work and will not have the round trip.

The second approach is to build a much faster tunnel, and to possibly throw a lot more RAM at the db server.


I'm was thinking about some ugly query such as:

select isnull(convert(varchar(8), date1, 121),'')
        + isnull('#'+convert(varchar(8), date2, 121),'')
        + isnull('#'+convert(varchar(8), date3, 121),'')
        ...
        + isnull('#'+convert(varchar(8), dateN, 121),'')
from A

But we could do better using something like:

select cast((
       select (
              select '#'+T2.N.value('./text()[1]',  'varchar(max)')
              from (
                   select T.*
                   for xml path(''), type
                   ) as T1(N)
                cross apply T1.N.nodes('/*') as T2(N)
              for xml path(''), type
              ).value('substring(./text()[1], 2)',  'varchar(max)')
       for xml path(''), type
       ) as varchar(max))
from A as T


You've got something that doesn't convert to an integer value in your source data.

Since the expression language doesn't have a tryparse method, the cleanest mechanism is to use a script component as a Transformation and leverage the .NET methods.

Connect a Script Component to your source.
On the Input Columns tab, check col as ReadOnly.
On the Inputs and Outputs tab, expand Output 0, click Output Columns and select Add Column
Rename this column to CleanCol and leave with the default data type of DT_I4
Click back to the Script tab and click Edit Script...
Use the following method in your code

Line here so formatting picks up

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    int colOut = 0;
    if (!Int32.TryParse(Row.col, out colOut))
    {
        Row.CleanCol_IsNull = true;
    }
    else
    {
        Row.CleanCol = colOut;
    }
}

Here's my source query

SELECT
    D.*
FROM
(
VALUES
    (N'1')
,   ('')
,   ('0')
,   ('3')
,   ('X') -- Uncomment this one to generate error
,   (NULL)
) D(col);

Results

As I think about it a bit longer, in the above script, if you wanted to have zero outputted for "bad" values, replace the first action in the if statement with Row.CleanCol = 0; Otherwise, this will make numbers of numbers and NULL of non-numbers.


MySQL IS NULL / IS NOT NULL Misbehaving?

Do you have some zero dates? Datetime values of 0000-00-00 00:00:00 are considered by MySQL to simultaneously satisfy is null and is not null:

steve@steve@localhost > create temporary table _tmp (a datetime not null);
Query OK, 0 rows affected (0.02 sec)

steve@steve@localhost > insert into _tmp values ('');
Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1264): Out of range value for column 'a' at row 1
steve@steve@localhost > select a from _tmp where a is null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

steve@steve@localhost > select a from _tmp where a is not null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

See: http://bugs.mysql.com/bug.php?id=940

This is classified as "not a bug". They suggest a workaround: use strict mode, which will convert the insertion warning into an error.

Having said all that, this alone can't explain the wild variation in the results you're getting (the sum of the is null and is not null counts should exceed the unrestricted count)...


How to Find Missing number in Sequence using mysql?

Without much experience in writing SQL queries, it's hard to understand by simply staring it down. A good approach is to "rebuild" it to understand what each step does.

Start with some sample data:

CREATE TABLE wp_blogs
    (`blog_id` int)
;

INSERT INTO wp_blogs
    (`blog_id`)
VALUES
    (1),
    (2),
    (3),
    (6),
    (7),
    (9)
;

Now we rebuilt it step by step. Start with the JOIN. (I don't like joining in the where clause, so I've rewritten it, but it works the same):

SELECT
a.blog_id as aa,
b.blog_id  as bb
FROM
wp_blogs a
JOIN wp_blogs b ON a.blog_id < b.blog_id
ORDER BY aa, bb /*added the ORDER BY for readability in the output*/

This has the following output:

| aa | bb |
|----|----|
|  1 |  2 |
|  1 |  3 |
|  1 |  6 |
|  1 |  7 |
|  1 |  9 |
|  2 |  3 |
|  2 |  6 |
|  2 |  7 |
|  2 |  9 |
|  3 |  6 |
|  3 |  7 |
|  3 |  9 |
|  6 |  7 |
|  6 |  9 |
|  7 |  9 |

As you can see the table is joined to itself, and on the right side only the rows are displayed that are greater than the left side.

Now we continue with the GROUP BY to collapse the groups on the left side. Whatever column you have in the SELECT clause, but not in the GROUP BY clause, you have to apply an aggregate function on it. Otherwise it's not clear which row for each group should be displayed. You have to tell MySQL, that you want the smallest number in the group. This can be done like this:

SELECT
a.blog_id as aa,
MIN(b.blog_id) as bb
FROM
wp_blogs a
JOIN wp_blogs b ON a.blog_id < b.blog_id
GROUP BY aa
ORDER BY aa, bb

Result is:

| aa | bb |
|----|----|
|  1 |  2 |
|  2 |  3 |
|  3 |  6 |
|  6 |  7 |
|  7 |  9 |

What we are interested in now, are the rows where the number on the right is not the number that is followed immediately after the number on the left. We filter on this with the HAVING clause, not with the WHERE clause, because we're dealing with groups here. In your query this is done with HAVING start < MIN(b.blog_id). start refers to the alias set in the SELECT clause for a.blog_id+1.
In other words, we add 1 to the value in the left column. If this is equal to the value on the right side, don't display the row.

So the result of this is:

SELECT
a.blog_id + 1 as aa,
MIN(b.blog_id) as bb
FROM
wp_blogs a
JOIN wp_blogs b ON a.blog_id < b.blog_id
GROUP BY aa
HAVING aa < MIN(b.blog_id)
ORDER BY aa, bb

| aa | bb |
|----|----|
|  4 |  6 |
|  8 |  9 |

bb is the number that is found in the table. That's why in your statement in the SELECT clause again 1 is subtracted from MIN(b.blog_id). The final query is:

SELECT
a.blog_id + 1 as aa,
MIN(b.blog_id) - 1 as bb
FROM
wp_blogs a
JOIN wp_blogs b ON a.blog_id < b.blog_id
GROUP BY aa
HAVING aa < MIN(b.blog_id)
ORDER BY aa, bb

| aa | bb |
|----|----|
|  4 |  5 |
|  8 |  8 |

Hope it's clear now :)
Play around with it in this sqlfiddle if you like


Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions?

Community wiki answer:

Yes of course the other transaction will wait. (Nearly) Every DDL statement takes an exclusive lock on the object being modified (dropped, created). See Explicit Locking in the documentation.


Understanding comparison operators on columns

What you have done is performed a CROSS JOIN and then filtered on the result of that CJ! What a CROSS JOIN does is combine every field in the first table with every field in the second and is not frequently very useful - but not always unproductive!
Take the first small example:  (a PostgreSQL fiddle for all of the code in this answer is available here)
CREATE TABLE x (y INT, z TEXT); INSERT INTO x VALUES (1, 'h'), (2, 'i');
CREATE TABLE a (b INT, c TEXT); INSERT INTO a VALUES (8, 'r'), (9, 's');

Then:
SELECT x.y, x.z, a.b, a.c FROM x, a;

Result:
y   z   b   c
1   h   8   r
1   h   9   s
2   i   8   r
2   i   9   s

The systax below (called ANSI join - also, see the wiki):
SELECT * FROM x CROSS JOIN a;

is perhaps better because you're being explicit about your requirements (see fiddle).
Things are perhaps easier to understand if you eliminate the extraneous SQL from your statement above. In this case, I specifically mean your use of the COMMON TABLE EXPRESSION (CTE - aka the WITH clause) - instead, I'd do the following:

create the tables separately as follows:
 CREATE TABLE dat (d_num INT NOT NULL);
 INSERT INTO dat VALUES (1), (2), (3);

and
CREATE TABLE rand (r_num INT NOT NULL);
INSERT INTO rand VALUES (-23), (5), (3), (1);

So, now perform the CROSS JOIN with no filter. Just a note - you should be using ANSI joins - see this article from the excellent oracle-base site.
SELECT
  d.d_num AS dn,
  r.r_num AS rn
FROM dat d, rand r
ORDER BY dn, rn;

Which can be rewritten:
SELECT
  d.d_num AS dn, r.r_num AS rn FROM dat d
CROSS JOIN rand r
ORDER BY dn, rn;

Result:
dn     rn
 1    -23
 1      1
 1      3      <<---- rn > dn
 1      5      <<---- rn > dn
 2    -23
 2      1
 2      3      <<---- rn > dn
 2      5      <<---- rn > dn
 3    -23
 3      1
 3      3
 3      5      <<---- rn > dn

12 rows

So, now we filter (keeping ANSI syntax):
SELECT
  d.d_num AS dn, r.r_num AS rn
FROM dat d
CROSS JOIN rand r
WHERE d.d_num < r.r_num
ORDER BY dn, rn;

Result:
dn  rn
 1   3
 1   5    -- <<--- note 5 = MAX(r_num)
 2   3
 2   5    -- <<--- note 5 = MAX(r_num)
 3   5    -- <<--- note 5 = MAX(r_num)

 5 rows

And we can see that these 5 rows correspond to the 5 rows from the original CROSS JOIN.
So, to answer your question:

Can I be guaranteed that the comparison is always based on the max
value of some_selection.number? Or should I add an intermediate step
to explicitly extract max(some_selection.number)?

Your SQL will pick every number combination where ANY number in the first field is less than ANY number in the second field. This will pick any number that is less than the MAX() of field 2, but also any other combination where field 2 is greater than field 1.
If you want to just pick ANY number in the first table is less than the MAX() of the second table, you need to further refine your filter using a sub-SELECT as follows:
SELECT
  d.d_num AS dn, r.r_num AS rn
FROM dat d
CROSS JOIN rand r
WHERE d.d_num < r.r_num
AND r.r_num = (SELECT MAX(r_num) FROM rand)  <<--- New condition!
ORDER BY dn, rn;

Result:
dn  rn
 1   5
 2   5
 3   5

And we can see that this result corresponds to the 3 records where 5 is the rn above.
A few words of advice:

make your initial SQL as simple as possible - using the WITH clause to generate data (IMHO) made your SQL more complex and didn't contribute to understanding.

use table and column aliases to make your SQL more readable.

when testing any of your solutions, always try to use weird outlier values (see -23 above) - I had 17 as well as 5, but the number of results of the CJ started to become too large - I just needed one value that was greater than all the values in the dat table.

EVERY SQL statement should (I would say MUST) have an ORDER BY clause - leaving this out is one of the biggest factors leading to confusion with results and what record in table A corresponds to what record in table B. I believe that in this case, using ORDER BY helps comprehension!

you used a variable called NUMBER in your example - you should never use SQL keywords as table or column names. This avoids confusion with error messages, aids with debugging and makes any query more portable - different systems escape keywords in different ways!

always include your server (and version) in your questions - for this case, the SQL would have worked for any server, but answers can frequently depend on these factors.

persist with the ANSI syntax (do a search for sql ansi joins versus old style join) - it seems strange at first, but is worth the effort!

p.s. bon courage avec ton anglais - qui n'est pas mauvais du tout d'ailleurs!


Why doesn't the following stored procedure compile with MySQL Workbench version 6.3.?

You're just missing the keyword THEN. Read more about the correct syntax here.

Try it like this:

use puppy;
DELIMITER $$
CREATE PROCEDURE spGetSystemLicensing
(
    IN  _licenseName varchar(128)
)
BEGIN

IF (TRIM(_licenseName) = '') THEN
    select * from SystemLicensing;
ELSE
    select * from SystemLicensing where LicenseName=_licenseName;
END IF;

END $$
DELIMITER ;


How to give access to a user to a view, but not to the table the view is using?

It was a problem about the database owner.

Even if you can see an owner on this interface:

Be sure that an owner is entered into files tab:


How to interpret this XML plan fragment?

To answer my own question, my original understanding was correct: the discrepancy between EstimateRows="1" and ActualRows="10190123" was an indication of incorrect optimizer estimates, caused by stale statistics (as suggested by commenters). With updated statistics the node in the plan corresponding to node 6 in the question looks like so:

<RelOp NodeId="25" PhysicalOp="Index Seek" LogicalOp="Index Seek"
 EstimateRows="1.25676e+007" EstimateIO="23.8363" EstimateCPU="1.78132" AvgRowSize="24"
 EstimatedTotalSubtreeCost="25.6176" TableCardinality="4.94087e+009" Parallel="1"
 Partitioned="1" EstimateRebinds="0" EstimateRewinds="0">
...
<RunTimeInformation>
  <RunTimeCountersPerThread Thread="2" ActualRows="3457399" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="8" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="1" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="7" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="5" ActualRows="8053198" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="3" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="6" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="4" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>

Although the problem could be seen in the XML plan representation directly, for those who want a graphical representation there exist a free tool that does not require the SQL Server Management Studio, SQL Sentry Plan Explorer, or even Windows for that matter -- it is an XSL stylesheet created by the StackOverflow user Justin.


How to conduct integrity test on SQL Server database backup file?

There's an old saying...

  The only way to verify a backup is good/usable is to restore it.
  REPEAT – you don’t know you have a good backup until you have restored
  it!

I would highly recommend checking your backups more often than every two weeks - Weekly, at a minimum and preferably more often than that.

At my shop, we have a 'play' server that we use to automate restores of the latest FULL/DIFF of the databases we deem 'important'.  We use Windows Task Scheduler to kick off a bat file which has several SQLCMD steps.  After successful restores, we run full DBCC CHECKDB WITH ALL_ERRORMSGS,NO_INFOMSGS and output the result to a txt file.  Then we email the output txt files to the database group for evaluation.

This process tests TWO things

Can I restore my backups?
Is the data in the backup structurally intact (DBCC)

Here are some sample steps in our bat file

SQLCMD -S Localhost -Q "IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'<YourDatabase>') DROP DATABASE [<YourDatabase>]" -E -b -d master

SQLCMD -S Localhost -Q "EXEC SCUTILITY.dbo.RestoreProdForDBCC" -E -b -d SCUTILITY

SQLCMD -S Localhost -Q "print 'Starting DBCC ' + convert(char,getdate(),121);dbcc checkdb ('<YourDatabase>') WITH ALL_ERRORMSGS,NO_INFOMSGS;print 'Completed DBCC ' + convert(char,getdate(),121)" -E -b -d master -o "F:\DBCC\dbcc_output.txt"

SQLCMD -S Localhost -Q "EXEC msdb.dbo.sp_send_dbmail @profile_name='SqlServerEmailProfile', @recipients='<DatabaseTeam>', @subject='DBCC has completed!', @file_attachments='F:\DBCC\dbcc_output.txt';" -E -b -d msdb

Check out these excellent posts for more information on Database Integrity Checking:

DBCC CHECKDB / Database Integrity

CHECKDB From Every Angle


Hmm... I'm a bit confused. By definition, if two events happen at different places, then they're different events.

My advice in this scenario is to tave 3 tables as follows:

CREATE TABLE Event
(
  Event_ID INTEGER,
  Event_Description INTEGER,  -- FK
  Event_Location INTEGER,  -- FK
  Event_Date DATETIME
);

And then two other "parent" tables

CREATE TABLE Event_Description
(
  Description_ID INTEGER PRIMARY KEY, -- FK in Event table.
  ED_DESCRIPTION VARCHAR(50));
);

CREATE TABLE Event_Location
(
  Location_ID INTEGER PRIMARY KEY,  -- FK in Event table
  Location_Description VARCHAR(50)
);


As @a_horse_with_no_name mentions in his comment, division can be achieved by conversion to milliseconds.

SELECT
  (1000*EXTRACT(EPOCH FROM tend - tstart)
    + EXTRACT(MILLISECOND FROM tend - tstart))
  / (1000*EXTRACT(EPOCH FROM ticklength)
    + EXTRACT(MILLISECOND FROM ticklength)) AS numticks
FROM recordings;


How to extract the date from this varchar column


Storing timeseries data with dynamic number of columns and rows to a suitable database

Usually a dynamic data problem like this can be solved by storing the dynamic portion of the schema in its own table, transposed as rows.
For example you can have an Intervals table where one column is called Interval and another column called Values. Interval would store 100, 200, 300 etc for each instance of values for that interval.
You could either store the Timestamp as a column in this table too, or my recommendation would be to normalize Timestamp into its own table with a TimestampId that is a foreign key field in your Intervals table.
Implementing your schema this way then allows you to not worry about how many dynamic Intervals are created since this it's a row based generic solution.


Database growing like crazy - what do I do?

Take a look at this blog Removing Duplicate Indexes by Kimberly Tripp and use the scripts to check if you have a completely redundant/duplicate indexes


Is the genetic query optimiser enabled by default?

The configuration for the genetic optimizer is documented in the chapter "Query Planning" of the manual. Specifically the section Genetic Query Optimizer

The first configuration parameter that is listed there is:

  geqo (boolean) Enables or disables genetic query optimization. This is on by default. It is usually best not to turn it off in production; the geqo_threshold variable provides more granular control of GEQO.

(emphasis mine)

So whenever you run a query that has more tables in the FROM clause than specified by geqo_threshold, GEQO will be used.

If you want to see the effects of it, you could lower that value and compare those plans against plans done without it.


Outage due to Replica Set Protocol Version upgrade?

What that protocol change do, is change decision pattern what nodes do when new primary must be elected. At the situation where all SECONDARY nodes are up-to-date (not falling behind) and your current PRIMARY have higher priority than any of SECONDARY node, nothing will happen. The system continues as it was.

But afterward, if your current PRIMARY is unavailable, next primary is elected faster than it was with protocolVersion:0


MySQL set ID of primary child with constraint

Circular foreign key

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

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

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

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

insert into parent values (1, null);

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

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

update parent set primary_child_id = 1 where id = 1;

delete from childs where id = 1;

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

dbfiddle here


Is MongoDB secondary index size affected by primary key size?

I understand correctly, secondary index leaf nodes in MySQL InnoDB engine hold the table primary key value (at least for a unique index).

InnoDB tables have a clustered index which determines where data for rows is stored. The clustered index for an InnoDB table is either the PRIMARY KEY (if set), the first unique index with all key columns NOT NULL, or hidden index containing synthetic row ID values. Secondary index entries will include a reference to the clustered index for the record location.

  Is this how MongoDB WiredTiger secondary indexes work too?* Or do MongoDB secondary indexes store a reference to the physical block where the document resides?

MongoDB (as at 4.0) does not support clustered indexes. The WiredTiger storage engine uses an approach similar to the third option described for InnoDB: an internal unique RecordID which is a 64-bit integer.

The WiredTiger storage engine currently uses a separate file for each collection and index. Collection data (collection-*.wt) is indexed using the internal RecordID; indexes (index-*.wt) map keys to RecordIDs in the associated collection data. These implementation details aren't relevant for the MongoDB API, which provides a consistent end user interface for indexing, querying, and manipulating data. Storage engines have ownership over how data is represented on disk and in memory. The approach is analogous to what happens under the hood in other database engines like InnoDB (where end users are interacting via the MySQL API).

The size of your primary key (which would be the _id field in MongoDB) currently has no impact on the size of secondary indexes in WiredTiger.

Incidentally, if you happen to be using the older (and deprecated) MMAPv1 MongoDB storage engine it does store references to the disk location (file and offset) where data resides. A significant downside of this approach is that document moves in storage (due to document growth) also require updating all secondary indexes. WiredTiger only needs to update secondary indexes when associated field values are changed.


Error while changing the authentication - mongoDB

You need to login and use ADMIN database to perform the operation.


You don't say but I'm guessing that your log files are on your D: drive and what you're seeing here is writes to the transaction log files, not to the database files i.e. to ldf not to mdf.  You won't see the mdf files written until the DB hits a CHECKPOINT, which could be some minutes later.

The linear increase is good and what you'd hope for.  That you can get from 120 to 240 and 360 shows that the disk subsystem is capable of these rates.  Whether the absolute number is as good as you would hope for or not, that's another matter.  I would suggest that the nature of the test you've chosen is itself limiting the whole system throughput.  Now you have populated tables you can try

INSERT INTO TestTable
SELECT TOP 10000 * FROM TestTable;

and see what rate that gives you.


SQL Server 2012 Express Report Services report builder button not visible

You can still install through ClickOnce deployment via (assuming you have configured SSRS with ReportServer as the installation path):

http://<servername>/reportserver/reportbuilder/ReportBuilder_3_0_0_0.application

It's still functional, but it's not full-featured.

Source

For example, Report Models are not supported and neither are ad-hoc queries through Report Models. This is an SSRS Express limitation which will manifest in Report Builder. Additionally, the language of the Supported Features of SSRS Express are somewhat ambiguous (nature of the English language) especially with regards to this line:

  Ad hoc reporting through semantic models and Report Builder is not supported.

What does the "and" connect and how does it connect them? Is it the clause "semantic models and Report Builder" or is Ad hoc reporting not supported through both semantic models and Report Builder. Unfortunately, the answer to this question could be more readily parsed at Linguistics.SE. But I digress. Suffice it to say, Report Builder is available to you in SSRS Express.


You can try if your MySQL server supports and/or behaves better in your case with setting innodb_lazy_drop_table=1 in your my.cnf file.

This option works for older MySQL versions, my understanding is that the option got removed in the newer versions and the innodb_lazy_drop_table behavior was made the default one.


How to know db size by the backup? in DB2

It is relatively complicated to do, but it's possible.

If your database has only DMS tablespaces that contain table data, you can use db2ckbkp -T <backup_file> alone to get this information:

   SYSCATSPACE
              tbspInImage: T

                       ID: 0
                    flags: 7102
                   flags2: 400
              extent_size: 4
            prefetch_size: 8
                  version: 5294
                  flavour: 6
                    state: 0
      statechangeobjectid: 0
        statechangepoolid: 0
                  LifeLSN: 000000000000000C
           LoadPendingLSN: 0000000000000000
          LoadRecoveryLSN: 0000000000000000
                 BeginLSN: 0000000000000000
                   EndLSN: 0000000000000000
               StordefLSN: 0000000000000000
          Full Backup LSN: 0000000FE0EAC00F
          Last Backup LSN: 0000000FE0EAC00F
         Full Backup Time:  519BCFCE = "20130521124934"
         Last Backup Time:  519BCFCE = "20130521124934"
               TotalPages: 87824
             UseablePages: 87816
              reorgPoolID: 0
               reorgObjID: 0
           poolReorgCount: 0
          # of containers: 2
            current_group: 0
                cont_csum: 1311788850
      current_map_entries: 1
                page_size: 4096
                 map_csum: 4294967292
     tsp rfwd encountered: 16

            Container CB
                             Type: 6
                       TotalPages: 43912
                      UsablePages: 43908
               # of OS rsvd bytes: 512
                    Page 0 offset: 16384
                       Tag offset: 512
                    Extent offset: 0
                             Name: /db2/data1/db2inst1/NODE0000/SAMPLE/T0000000/C0000000.CAT

            Container CB
                             Type: 6
                       TotalPages: 43912
                      UsablePages: 43908
               # of OS rsvd bytes: 512
                    Page 0 offset: 16384
                       Tag offset: 512
                    Extent offset: 0
                             Name: /db2/data2/db2inst1/NODE0000/SAMPLE/T0000000/C0000001.CAT

Using TotalPages and page_size from the main section of the output, you can calculate the total space. (You can ignore the Container CB sections that give details for each container in the tablespace).

If you have SMS tablespaces that store table data (i.e. not just system- or user-temporary tablespaces), you'll see tablespaces in this output that show TotalPages: 0.

To calculate the amount of data used by these SMS tablespaces, you will need to dump out all of the information from the backup image using db2ckbkp -a. You will get lines like this:

BufAddr  PoolID Token Type     Offset   FileSize ObjectSize   OrigSize Object Name
-------- ------ ----- ---- ---------- ---------- ---------- ---------- -----------
00000000:     0    10   16       1042     167680     107757     524288 "Tablespace: 0, Table: SQL00010"

OrigSize shows the amount of uncompressed data in this buffer. The line above shows data from an SMS tablespace;  DMS tablespaces will also show lines like this, but the ObjectType will show DMS.TABLESPACE.DATA.

So, if you look for lines where ObjectType starts with "Tablespace:, you can add up the OrigSize of these lines, and find the additional storage space that all SMS tablespaces will use.


Query calculating AVG per minute GROUP BY timestamp

You should use AVG function if you need to get the average
Using MINUTE function will return the minute part from the date. i.e. 10:05:03 and 11:05:33 will give the same result. (It is fine if the interval is less than 60 minutes, however, it is safer to use the following query:)

SELECT left(timestamp, 16) as mtimestamp, avg(vif_2_rx)
FROM md_metrics_status
WHERE vm_name = 'r-23-VM' AND TIMESTAMP > NOW() - INTERVAL 15 MINUTE
GROUP BY mtimestamp


Which is more efficient b+ tree or search (binary or linear)?

Let us consider a dataset with N items, of which P items match the predicate and will be returned by the query.

1a.A linear search, unsorted data. There is no way for the algorithm to know whether a value will match the predicate until that value is read. Therefore the only solution is to read all values and ignore those which do not match the predicate. The effort will be O(N).

1b.A linear search, data sorted by M. In this case we are assured that all values which match the predicate are contiguous in the data. However, there is no way to know the offset of the first value so, again, reading from the beginning is the only approach. Once a value is read that is larger than the upper limit the algorithm can stop. So, in general, this approach is faster than the unsorted approach. It is still O(N), however.

2.Binary search on M. A binary search requires the data to be sorted. Finding the lower/ upper boundary will require fewer operations - it is an O(log N) algorithm. Reading all the values between the lower and upper bound is O(P), however, so overall that is the query's complexity.

3.BTree index on M. In this context a BTree is a more general form of the binary search. Both operate by eliminating a fraction of the possible results in each itteration of the algorithm. This fraction is known as the fan-out. For a binary search the fan-out is 2 i.e. 1/2 the values remain after each itteration. For a BTree the fan-out is determined by the number of index rows that can be held on a data page, which is typically much larger than 2, and so specific values can be found in fewer itterations. The complexity is the same, however, at O(log N) to find the lower limit and O(P) to read the values.

BTree on A. This will be useless as the query puts no restriction on A's value.

  when a query optimizer chooses optimization plans which of the given options will it pick

It will pick the cheapest one. It is the optimiser's job to examine various physical implementations of the query (use and index, scan all rows, sort the data etc), apply heuristics to assign a cost to each permutation and deliver the cheapest (in terms of estimated elapsed time) to the execution engine as a query plan. Note that the cheapest need not be the least complex in Big-O terms. Big-O notation shows how the effort required changes as the number of items approaches a very large limit. Most databases, however, do not have an infininte number of rows. The optimizer has information about the actual number and distribution of values within the dataset. These are stored in internal statistic objects.

Let's take an example where we have a BTree on M which is 3 levels deep. The full table occupies 1,000 pages on disk. Remember that all data movement happens one page at a time. If the given query were likely to return only a single row it would be efficient to use an index on M to read four pages in total (3 from the index on M and one from the table to get the corresponding value of A). On the other hand, if the statistics suggest that most of the data pages will be read it will be more efficient to ingore the index and scan all data pages, ignoring the ones which do not match the predicate.

I have never heard of binary search implemented in a DBMS. BTrees offer the same functionality with greater performance.

Linear search (also known as a table scan) will be used either when no index exists, or when the overhead of using the index is more than the benefit of identifying individual rows.

BTrees are not the only type of index. Single-value lookup and table scan are not the only possible algorithms.


As you can see from the definition you provided for [dbo].[ConfigData], both Value and ValueInUse are defined as SQL_VARIANT data types.  The error message you are receiving is:

  Msg 257, Level 16, State 3, Procedure usp_SysConfigReport1, Line 19
  Implicit conversion from data type sql_variant to int is not allowed.
  Use the CONVERT function to run this query.

So Value and ValueInUse are SQL_VARIANT (as also are the derived values for OlderValue, OlderValueInUse, RecentValue, and RecentValueInUse) but you want to use them as INT.

The error message is saying that you need to change the SQL_VARIANT into INT by using the CONVERT function.  An example of selecting a SQL_VARIANT and turning it into an INT.

SELECT CONVERT (int, Value) FROM [dbo].[ConfigData]

The CONVERT changes the datatype (if possible) from SQL_VARIANT to INT.

Just make sure to CONVERT to the proper data type and all should be well.


For this query

SELECT *
FROM table_a
    JOIN table_b USING (id)
WHERE table_b.column = 1

The optimal way is execute it is

The WHERE clause provides some filtering, so let's make use of it.  That is, have an index on table_b starting with column.  (Later we'll discuss whether to make it composite.)  So, the Optimizer will use that index to find row(s) of table_b.
For each of those rows, JOIN to table_a.  (Note that JOIN, not LEFT JOIN is being used; LEFT JOIN is a different story.)
To reach into table_a, an index starting with id is needed.  (Note:  USING(id) means table_a.id = table_b.id.)

So far, we have

b:  INDEX(column)
a:  INDEX(id)   -- though it probably exists as PRIMARY KEY(id)

Covering?

We don't know what other columns there are in the two tables.  If there are very few columns, then it might be tempting to build a "covering" index.  This is an index that contains all the columns needed anywhere in the SELECT.  The benefit is some performance speedup by looking only in the index's BTree and not having to touch the data BTree.

For table_b, it would be tempting to say INDEX(column, id).  That would be good (and 'covering') if there were only those two columns.  But there are probably more columns.  So, probably INDEX(column) is all that is worth doing.

For table_a, I assume that id is the PRIMARY KEY (which is, by definition, unique and an index).  So nothing more is needed there.

Bottom line:  Use the two single-column indexes listed above.

And this example does not exemplify anything about "composite" indexes.  For more on that, see

Cardinality & Range
Cardinality and Composite
Single-column index
Indexing cookbook

  but often I add a composite index with both that can improve efficiency of queries like this...

Better Example

As I said, your example does not exemplify the question.  So, I'll try to answer "When should I use a composite index"?  There are many cases (see the links); I'll give you a simple case.

WHERE x = 1
  AND y > 2

The relevant characteristics are:

x and y are in the same table.  (Can't build an index across two tables.)
AND is used.  (OR can't be optimized.)
One of the tests is with =.  (Composite won't help if both are ranges.)
y is a "range" (examples:  y>2, y LIKE 'm%', y BETWEEN ... AND ...).

The general rule is:

Put all the = columns first (x in my example)
Put one range column last (y)

That is, you must order it INDEX(x,y).

For WHERE x = 1 AND y = 2 (both =), it does not matter whether you have INDEX(x,y) or INDEX(y,x) .

Another tidbit:  With ENGINE=InnoDB, the PRIMARY KEY column(s) are implicitly tacked onto each secondary key.  Hence, your INDEX(column) is the same as INDEX(column, id).  But this fact does not play a role in this discussion.

I realize that I am disagreeing with other Answers here (and elsewhere), but I stand my ground.


You most probably re-index your database between the log backups. This means your log backup contains a lot of transactions from these maintenance operations.
Please check your maintenance plans.
The size of transaction log can be huge compared to your actual database, imagine this situation. You delete 100k rows and add 100k rows between your backups. Your database will in the end be the same size, but you will have a big transaction log.


How do I create a table best suited for storing address information

I would analyze the data and seriously consider scalability.

-Do you need to retain an old address for prior billing?
-Do you need to know when a client changed addresses?
-Are other countries currently involved now?
-Will other countries be involved?

3 address lines may not be enough.

You will likely need a total of:

-5 address lines
-City
-State
-Zip
-country
-Province ?
-CreateDate
-Expiredate
-Region  (I would consider this a way to group Zip codes 90000 series is Southern California, etc).  You could adjust later as needed.
-AcctType (billing, sales mailings, main office)


Why not just put the datapull for the linked data source in a stored procedure and then access it from a "local" database?


SUM over distinct rows with multiple joins

Since there can be multiple payments and multiple extras per item, you run into a "proxy cross join" between those two tables. Aggregate rows per item_id before joining to item and it should all be correct:
SELECT i.country         AS group_by
     , COUNT(*)          AS item_count
     , SUM(i.price)      AS cost
     , SUM(p.sum_amount) AS earned
     , SUM(e.sum_amount) AS extra_earned
FROM  items i
LEFT  JOIN (
   SELECT item_id, SUM(amount) AS sum_amount
   FROM   payments
   GROUP  BY 1
   ) p ON p.item_id = i.id
LEFT  JOIN (
   SELECT item_id, SUM(amount) AS sum_amount
   FROM   extras
   GROUP  BY 1
   ) e ON e.item_id = i.id
GROUP BY 1;

Consider the "fishmarket" example:

Two SQL LEFT JOINS produce incorrect result

To be precise, SUM(i.price) would be incorrect after joining to a single n-table, which multiplies each price by the number of related rows. Doing it twice just makes it worse - and also potentially computationally expensive.
Oh, and since we do not multiply rows in items now, we can just use the cheaper count(*) instead of count(DISTINCT i.id). (id being NOT NULL PRIMARY KEY.)
SQL Fiddle.
But if I want to filter by items.created?
Addressing your comment.
It depends. Can we apply the same filter to payments.created and extras.created?
If yes, then just add the filters in the subqueries as well. (Does not seem likely in this case.)
If no, but we are still selecting most items, the above query would still be most efficient. Some of the aggregations in the subqueries are eliminated in the joins, but that's still cheaper than more complex queries.
If no, and we are selecting a small fraction of items, I suggest correlated subqueries or LATERAL joins. Examples:

Multiple array_agg() calls in a single query


Problem Installing PostgreSQL 12.1 Win8.1: "the database cluster initialisation failed"

your having an ACL error Access Control List so this is windows security issue

try turning off UAC

if that fails try running initdb manually
C:\pathtoPosgresq\Bin\initdb -D path\to\data\

doing this manually make sure postgres.conf has the correct path to the directory its located in the data directory and you may need to adjust the service registry entries to the correct  database path or just start postgresql manually

https://www.postgresql.org/docs/current/creating-cluster.html
https://www.postgresql.org/docs/current/server-start.html


Try SSIS Logging:

You can use SSIS Logging for your SSIS package to push your errors to places you can monitor them. For example, we use SSIS logging to push our SQL maintenance errors to a local database/server, to a remote database/server, and also to a CSV file on a file share as shown in Figure A.

The SSIS logging that get's piped to SQL Server goes to a system table it creates in the database of your choosing called dbo.sysssislog. Once errors are piped to the sysssislog SQL table you can query it directly to check for errors using VS. (sysssislog is too raw a format for me personally. I parse this table to another table that has less and yet more meaningful information via a SQL Server job).

Or alternately, you can use SQL Server mail to continually check the sysssislog table and e-mail or text you if there's an error. If it's relatively low priority but needs to be reviewed, you could create an SSRS report to periodically review the data in the table. This won't fix your error, but at least you'll become more aware of what's going on and maybe give you more information you can use to find the actual underlying issue.

Figure A: SSIS Logging Example--SSIS execution information gets piped to a local database, remote database, and a CSV file.

Note:

From the testing I've done, I've noticed SQL/SSIS has issues if I actually delete the dbo.sysssislog table. So, instead I truncate that table instead of deleting it. Before you accidentally delete this table, make a copy of the schema first--in case you have to manually recreate it.

p.s.

Not knowing your situation, you may need to wrap your multi-point transaction within the distributed transaction coordinator (MSDTC or DTC). That way, if one part of your transaction fails between SQL Server, The web server and SSIS, the whole transaction is rolled back.  Here is an entertaining (and hopefully useful) example(s) using DTC.

https://www.mssqltips.com/sqlservertip/3272/example-using-web-services-with-sql-server-integration-services/

http://blog.arjanfraaij.com/2010/11/enabling-distributed-transaction.html


using SQLCMD to export/extract data withot trailing spaces

Despite how the SQLCMD usage screen (SQLCMD -?) describes the -W parameter:

[-W remove trailing spaces]

the actual purpose of this parameter is not to remove existing trailing spaces but rather to skip padding a value with spaces.  If a string value already has trailing spaces of its own, they cannot be removed if you simply specify the -W parameter.
As far as I can see, there is no way to resolve this issue with parameters.  You will have to either manipulate the output with text processing utilities or generate the values without the trailing spaces in the first place.  For the latter you can use the RTRIM() function, but that, of course, means you will have to replace the SELECT * with an actual column list, where some columns would be computed columns:
SELECT
  ...
  RTRIM(SomeStringColumn) AS SomeStringColumn,
  ...
FROM
  ...


This is a shortened, but public version of an MOS (My Oracle Support) note:

WHEN LOGIN VIA EM EXPRESS, ORA-979 IS RAISED (Doc ID 2390782.1)

Here we can find this information:

  CHANGES

  setting "_fix_control"='11657903:ON'

Which gives away the solution, without accessing the private content. Someone enabled the patch for bug 11657903, and that change caused this error. Try disabling the applied patch for bug 11657903:

alter session set "_fix_control"='11657903:OFF';

But this will not have any effect, as logging in to EM creates a new session. Instead of it, you can disable it at system level:

alter system set "_fix_control"='11657903:OFF';

This solves your problem, but that patch was applied to fix other issues, it is not nice to disable it completely. If you want to set a parameter dynamically, on LOGON, only for specific sessions, you can do that with a logon trigger. Here is an example of such a trigger that shows you how to set _fix_control for a specific user and patch, just change it to your needs.

http://karandba.blogspot.com/2015/03/tracing-session-with-client-identifier.html

CREATE OR REPLACE TRIGGER set_trace_logon_test1
AFTER LOGON ON SCOTT.SCHEMA
WHEN (lower(sys_context('USERENV', 'CLIENT_IDENTIFIER')) like '%hr%')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12 : 10053 trace name context forever, level 1''';
EXECUTE IMMEDIATE 'alter session set "_fix_control"="6765823:off"';
insert into sys.trigger_test values (sys_context('USERENV', 'CLIENT_IDENTIFIER'),sysdate,'LOGON');
commit;
END set_trace_logon_test1;
/


Updating rows conditionally with a gapless ascending value

See this discussion on AskTom about the way expressions are evaluated. The decode function does perform short-circuit in most cases: right-side expressions are not evaluated if a condition on the left is evaluated to true. Sequence are special however, they are evaluated for all lines in all cases.

The easiest workaround is to use a function that calls the sequence:

SQL> CREATE SEQUENCE seq;

Sequence created

SQL> CREATE OR REPLACE FUNCTION f RETURN NUMBER
  2  AS
  3     l_result NUMBER;
  4  BEGIN
  5     SELECT seq.nextval INTO l_result FROM dual;
  6     RETURN l_result;
  7  END;
  8  /

Function created

SQL> UPDATE t1 SET letter='x',
  2     number2 = DECODE(letter,'a',f,'d', f,'e', f,NULL);

7 rows updated

SQL> select * from t1;

LETTER    NUMBER1    NUMBER2
------ ---------- ----------
x               1          1
x               2
x               3
x               4          2
x               5          3
x               6
x               7

7 rows selected

As you can see the function is evaluated only when the DECODE expression requires it.

You can work a pure SQL solution in most cases. If we assume that the table has a PK (I'll take number1), this self-join SQL solution would work:

SQL> UPDATE t1 t1_out
  2     SET letter  = 'x',
  3         number2 = CASE WHEN letter IN ('a', 'd', 'e') --
  4                        THEN (SELECT COUNT(*)
  5                                FROM t1 t1_in
  6                               WHERE letter IN ('a', 'd', 'e')
  7                                 AND t1_in.number1 <= t1_out.number1)
  8                   END;

7 rows updated

SQL> select * from t1;

LETTER    NUMBER1    NUMBER2
------ ---------- ----------
x               1          1
x               2
x               3
x               4          2
x               5          3
x               6
x               7

7 rows selected

You have probably tried an analytics solution. It is possible to write a query that will return the appropriate result set but update and analytics don't work very well together (analytics tend to produce non-updatable views).


Sample query/DDL for producing page locks on a clustered index in SQL Server (without hints)

We can use a table of about the same size, but we need some more interesting data. Specifically, data that SQL doesn't have indexed but that we're using in our modification predicates.

USE tempdb;

CREATE TABLE dbo.Dummy
(
  Id INT IDENTITY(1, 1),
  Crap DATE,
  MoreCrap VARCHAR(1000)
);

INSERT dbo.Dummy ( Crap, MoreCrap )
    SELECT TOP 1000000 DATEADD(DAY, x.Rn % 365 + 1, GETDATE()), CONVERT(VARCHAR(100), NEWID())
        FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Rn
        FROM sys.messages AS m
        CROSS JOIN sys.messages AS m2
        ) AS x ( Rn );

CREATE CLUSTERED INDEX cx_RemusForPresident ON dbo.Dummy (Id)

With just the CX on the Id column, we update!

BEGIN TRAN
UPDATE d
SET d.MoreCrap = 'Happy Friday'
FROM dbo.Dummy AS d
WHERE d.Crap >= '20170601'
AND d.Crap < '20170630'
AND d.MoreCrap LIKE '0%'

And just like before, though hopefully without offending Aaron Bertrand, we check out sp_WhoIsActive

EXEC sp_WhoIsActive @get_locks = 1

And blammo! Exclusive locks on Pages, and IX locks on the object, with no key locks.


You may need to try some wildcard stuff

MATCH (field) AGAINST ('+85604*' IN BOOLEAN MODE)
MATCH (field) AGAINST ('+8560*' IN BOOLEAN MODE)

The MySQL Documentation says on '*':

  *

  The asterisk serves as the truncation (or wildcard) operator. Unlike
  the other operators, it is appended to the word to be affected. Words
  match if they begin with the word preceding the * operator.

  If a word is specified with the truncation operator, it is not
  stripped from a boolean query, even if it is too short or a stopword.
  Whether a word is too short is determined from the
  innodb_ft_min_token_size setting for InnoDB tables, or ft_min_word_len
  for MyISAM tables. The wildcarded word is considered as a prefix that
  must be present at the start of one or more words. If the minimum word
  length is 4, a search for '+word +the*' could return fewer rows than a
  search for '+word +the', because the second query ignores the
  too-short search term the.

Here is an example from the Documentation:

  'apple*'

  Find rows that contain words such as “apple”, “apples”, “applesauce”,
  or “applet”.

For more information, see MySQL Documentation on Fine Tuning Your Searches


Can I use pg_restore to restore data to subset of tables?

From pg_restore's documentation you can see:

  -t table
  --table=table

Restore definition and/or data of named table only.
Multiple tables may be specified with multiple -t switches.
This can be combined with the -n option to specify a schema.

Highlighting "Multiple tables may be specified with multiple -t switches", which means that you can use -t switch multiple times, and your example would work as:

pg_restore -a -d ACMETEST -n public -v -U acme -t hardware -t location /var/acme/backups/restore/ACMEDatabase.bak

As seen, I just used -t hardware -t location instead of -t hardware,location.


Restrict executeQuery in Java from data manipulation in an Oracle database

The JDBC standard does not specify the executeQuery() method behaviour, apart from requiring it to throw an exception when the statement returns something other than a single result set. The Oracle driver seems to comply with this requirement. The method is intended to be used with queries, but nowhere in the the documentation is it stated that it should prevent you from using it with DML statements.

You can choose to handle this exception and roll back the transaction, if you want to prevent changes to your data. Obviously, you will have to disable autocommit for this to work.

The correct way to achieve what you want would be to grant only select privileges to the user ID your application connects with.


Can we use MariaDB mysqldump CLI to take dump from MySQL Community version?

MariaDB JSON functions are different from MySQL ones.

Manually create the equivalent table in MariaDB-10.3.
remove the DROP TABLE {tablename} /CREATE TABLE {tablename} from your mysqldump output.
import the edited mysqldump file.

If there are a lot of these generated columns, suggest doing a mysqldump --no-data and getting the syntax of all the tables correct first.


UPDATE Only one row (using update and join)

LIMIT is not allowed when the UPDATE has joins. You can however move the join and the LIMIT inside a subquery and then join back to the table to be updated using the primary or a unique key. This is allowed:

UPDATE table1 AS upd
  JOIN
    ( SELECT t1.pk
      FROM table1 AS t1
        JOIN
           table2 AS t2 ON t2.col = t1.col
      WHERE t1.row2 LIKE '%something%'
      -- ORDER BY some_expressions
      LIMIT 1
    ) AS sel
    ON sel.pk = upd.pk
SET
   upd.row1 = 'a value' ;

It's also good to use ORDER BY with LIMIT. Otherwise an arbitrary row will be selected.

If you want to update both tables (one row from each one), then you just need to join the derived table to the second table, too:

UPDATE
    ( SELECT t1.pk AS pk1,                     -- The PK columns
             t2.pk AS pk2                      -- of each table
      FROM table1 AS t1
        JOIN
           table2 AS t2 ON t2.col = t1.col
      WHERE t1.row2 LIKE '%something%'
      -- ORDER BY some_expressions
      LIMIT 1
    ) AS sel
  JOIN table1 AS upd1  ON sel.pk1 = upd1.pk    -- join table1
  JOIN table2 AS upd2  ON sel.pk2 = upd2.pk    -- join table2
SET
   upd1.row1 = 'a value',
   upd2.roww = 'some other value' ;

Test at rextester.com


INSERT to a table from a database to other (same SQL Server) using Dynamic SQL

Unless I am mistaken, your problem is not as complicated as you think. From what I understand, you want to take some data from your current database, store it in variables and then insert that into another database, returning the id that was inserted. If so, this will do what you need:

CREATE PROCEDURE dbo.pr_consolidation_copy_group (
    @group_id numeric(10,0),
    @database_to varchar(100)
)
AS
SET NOCOUNT ON;

DECLARE @group_name char(100)
       ,@arrival datetime
       ,@departure datetime
       ,@contact_id char(82);

 SELECT @group_name = group_name ,
        @arrival = arrival,
        @departure = departure,
        @contact_id = contact_id
   FROM grp
  WHERE group_id = @group_id;

DECLARE @sql_cmd NVARCHAR(MAX);
    SET @sql_cmd = 'USE ' + QUOTENAME(@database_to) + ';'
                 + 'INSERT grp(group_name, arrival, departure, contact_id)'
                 + 'SELECT @group_name, @arrival, @departure, @contact_id;'
                 + 'SET @id = SCOPE_IDENTITY()';

DECLARE @params NVARCHAR(MAX);
    SET @params = '@group_name CHAR(100),'
                + '@arrival DATETIME,'
                + '@departure DATETIME,'
                + '@contact_id CHAR(82),'
                + '@id INT OUTPUT';

DECLARE @id INT;

   EXEC sp_executeSql @stmt = @sql_cmd
                    , @params = @params
                    , @group_name = @group_name
                    , @arrival = @arrival
                    , @departure = @departure
                    , @contact_id = @contact_id
                    , @id = @id OUTPUT;

-- @id is now available to use
END

You can find more information about outputting a variable in the documentation for sp_executeSql.


Moving two databases from MySQL 5.1 [Prod] to MySQL 5.6 [Stage], not working [Windows]

Doing this

C:> mysqldump -uroot -p abc > abc.sql

does not include the two lines you mentioned.

On the Prod Server, do this

C:> mysqldump -uroot -p --databases abc > abc.sql

Using --databases abc makes mysqldump write those two lines

On the Staging Server, do this

C:> mysql -uroot -p < abc.sql

Don't specify abc upon reload. Those two lines will create abc (if it is not already there) and make abc the default database before loading the data.


Use a union. If you want to keep track of the origin for each, you can add a constant column

Select c1 as x, c2 as y, 1 as origin from...
UNION all
Select c3 as x, c4 as y, 2 as origin from...


How can I get the last items by date desc then by id desc?

Your query seem ok.

you should thoroughly Test the Resultset in all server.

Only concern is that when it is ISNULL then Why you will update with 0.Is it really necessary ?

Also second CTE is not required.

WITH History AS (
    SELECT  Id, h.EmployeeId, CompanyCourseId, NextDueDate, HasAttachments,
        ROW_NUMBER() OVER (Partition BY EmployeeId,CompanyCourseId ORDER BY
        NextDueDate DESC, Id DESC) AS rownum
    FROM TrainingHistory h
)

 select ec.Id, l.HasAttachments
  INTO TempEmployeeCourse from History l INNER JOIN
      EmployeeCourse ec on l.CompanyCourseId = ec.CompanyCourseId AND
      l.EmployeeId = ec.EmployeeId
where WHERE l.rownum = 1

update EC
set HasAttachments =TEC.HasAttachments
,UniqueId =TEC.UniqueId
from EmployeeCourse EC
inner join dbo.TempEmployeeCourse TEC
on TEC.Id =EC.EmployeeCourse.Id


The error message is very clear, you need to specify a procedure or a valid pl/sql block.
If you really want to truncate the aud$ table from a piece of pl/sql, you will need to use dynamic SQL.

begin
execute immediate 'truncate table sys.aud$';
end;
/

put the code in the action or in the procedure definition of aud_clear_fun, that really needs to be a procedure to work with Oracle Scheduler. The Scheduler is not going to read the returned value ...


This totally depends on your application. MIME is made for and is most common for E-mail, but it's also common for the web (http) and in some NoSQL databases.


Will my sqlite database occupy less space if I insert nulls instead of zeros

In the SQLite record format, both NULL and the integer 0 need only a type code and no storage for the actual value itself.

In any case, you should use whatever value works best with your queries, and optimize only when needed.


How to dump only create table statements (or table definitions only) with pg_dump in Postgresql

I've just tried this:

pg_dump -s -t test.* -d test

It nicely exported all table definitions, as expected.  -s is the same as --schema-only.


updating a field with a partial pass parameter

That's really a bad idea, I don't understand why you need to do so, and it can't be done the way you are looking for, but if you going to do this, I suggest to declare 24 variable + 1 for the RowID.

For example:

CREATE PROCEDURE MyProc
  @RowID INT,
  @Col1 VARCHAR(45),
  @Col2 INT
  -- In your case you will need 24 variable, each one represent a value for a column
AS
BEGIN
  --Optional: Check if @RowID IS NULL and RAISERROR first
  DECLARE @SQL NVARCHAR(MAX) = N'UPDATE [T]
                                 SET [Col1] = ISNULL(@Col1, Col1),
                                     [Col2] = ISNULL(@Col2, Col2)
                                 WHERE [RowID] = @RowID';
  EXECUTE sp_executesql @SQL,
                        N'@Col1 VARCHAR(45), @Col2 INT, @RowID INT',
                        @Col1,
                        @Col2,
                        @RowID;
END

Then call your procedure, and pass NULLs for columns you don't want to be updated. That's the only way I can think of it.

Sample Demo

UPDATE:

You don't even need for DynamicSQL, so your SP may looks like

CREATE PROCEDURE MyProc
  @RowID INT,
  @Col1 VARCHAR(45),
  @Col2 INT
  -- In your case you will need 24 variable, each one represent a value for a column
AS
BEGIN
  --Optional: Check if @RowID IS NULL and RAISERROR ROLLBACK TRANSACTION
  UPDATE T
  SET Col1 = ISNULL(@Col1, Col1),
      Col2 = ISNULL(@Col2, Col2)
  WHERE RowID = @RowID;
END


Handle concurrency when insertion depends on reading

The problem is that there could be values present at the end of A's transaction - the ones inserted by B - that were not present when A first checked. This is known as a phantom

  A phantom is a row that matches the search criteria but is not initially seen.

The way to prevent phantoms is to use the Serializable isolation level. This is the only isolation level which prevents phantoms.

There are other ways to serialize a workload without using that isolation level. One is to obtain an exclusive lock on the whole table using TABLOCKX. When used within an explicit transaction (BEGIN TRANSACTION) this will take an exclusive lock on the table and hold it until the transaction is committed or rolled back. Therefore B will not be able to insert until A has committed. The table lock affects all work, however, including simple queries which would not induce interval overlaps.

A finer-grained approach would be to use sp_getapplock. This allows your code to generate its own lock with its own semantics. It acts as a MUTEX. When workload A starts it acquires this applock. It can then go on to preform the check and insert confident it alone has rights to do so. If workload B were to start concurrently it would attempt to acquire the applock and be blocked because that applock is owned by A. You have to be careful that all work that could be subject to the overlapping interval problem must start by acquiring this custom applock and finish by releasing it. Writes that do not suffer from the interval overlap problem and all reads can proceed without acquiring the applock or being blocked by it.


How to add/update data from slave server to master server in MariaDB?

You can use master-master replication which consists of two master-slave configurations: One master-slave where node 1 is master and node 2 is slave, and one where it's the other way around.

There are a couple of caveats with this, and it's a bit fragile: You need to set auto_increment_increment to 2 on both nodes and set auto_increment_offset to 1 on the one node and 2 on the other, IIRC. This way, you avoid INSERT collisions (assuming the tables use auto_increment). Here's an article that might be useful: MariaDB master/master GTID based replication with keepalived VIP

There is another solution called multi-master replication, and with MariaDB that can be achieved with MariaDB Galera Cluster. This is a more robust solution, although it does have some limitations - note e.g. that it only really works with the InnoDB storage engine. You would usually want to have an odd number of nodes (typically 3) to have quorum amongst the nodes in the event of a network split, although you can simulate this with 2 normal nodes and one Galera arbitrator.


Is it safe to delete dummy tables Oracle 11g XE

I wouldn't suggest removing those tables, no.  The Oracle XE browser-based interface is written in APEX (Application Express, a framework for developing web-based apps in the Oracle database).  If you break APEX, that interface will likely stop working.


How to create a database for unknown kinds of data?

Unknown kinds of data sounds to me somewhat fishy.  Your examples, of course, are all knowns.  For goods and services careful analysis and normalization are important and I think you can get away from EAV modelling (which I think will cause mor problems than it solves) for core data.  The rest could be stuffed in XML fields or the like.  Additionally if you do your design right you can always expand the information appropriately.  Consider the following three tables:

CREATE TABLE products (
    id int autoincrement primary key,
    sellprice numeric,
    part_code varchar(10),
    title varchar(32),
    description text
);

CREATE TABLE barcode_type (
    id int autoincrement primary key,
    label varchar(15) not null unique
);

CREATE TABLE make_model (
    id int autoincrement primary key,
    make varchar(15) not null,
    model varchar(15),
    barcode_type int references barcode_type(id),
    barcode varchar(32)
);

Now with this you can assign barcodes (including ISBN, EAN, UPC, etc, to various parts, one per make/model combination.  If you need to support more barcode types, this is not hard to add.  As far as expiration dates, where these go depends on where you are tracking them.  If you want to have temporary pricing, or pricing for groups of customers, you can add that too.

However what you are describing doesn't sound very unstructured.  I would suggest starting with a minimal design and expanding as needed rather than an EAV design and later regretting it.


SQLServer add a default value for a column

It's your quotes. If you remove those, and change your literal to use single quotes, you should be fine. SQL Servers DEFAULT is SET QUOTED_IDENTIFIER ON and with this literals must be delimited by single quotation marks. So, you really only need to change this part:

DEMO

ALTER TABLE "action"
ADD CONSTRAINT "df_action_0" DEFAULT 'To Do' FOR "action_type";

Though, you will more commonly see double quotes not used in this manner, and instead your DDL would be written as:

CREATE TABLE action
( action INT IDENTITY(1,1)
, name VARCHAR(250) NOT NULL
, owner VARCHAR(50)
, action_type VARCHAR(50) NOT NULL
, due_date DATETIME
, done_date DATETIME
, successful BIT NOT NULL
, job INT
, contract INT
, person INT
, description VARCHAR(MAX)
, deliverable VARCHAR(MAX)
, outcome VARCHAR(MAX)
, source VARCHAR(50)
, notes VARCHAR(MAX)
);

ALTER TABLE action
ADD CONSTRAINT df_action_0 DEFAULT 'To Do' FOR action_type;


Best way to backup/restore a very large DB (~2TB)

You may need to think further ahead -- When reloading, will you have enough space?

Setting innodb_file_per_table does not necessarily provide any extra space.

When you do SHOW TABLE STATUS;, the "Data_free" will indicate how much free space there is in ibdata1.  How big is that?  If it is small, then something more needs to be done.

Is it possible in that OS to "extend" your current drive with one of those spare drives?  That is can you, with an OS command and without dumping, extend the current "drive" to 4TB?  If so, that is is probably worth doing to get started.

If you do backup, it will need to be to some other drive, either on the same server or on another server.  (That is, do the dump across the 'network'.)


How to get a group where the count is zero?

The reason your query did not work as intended:

Inner join gives you the intersection of 2 tables. In your case, there was no entry for 5th street in your users table and that is why join did not produce any entry for that.

Outer join (right or left) will give the result of inner join and in addition all non-qualifying records from the left or right table depending on the type (left or right) of  outer join.

In this case, I put Street on the left of the join and used left outer join as you wanted all streets (even count is zero) in your result set.

Change your select query to this.

SELECT S.Name AS Street,
       Count(U.Username) AS COUNT
FROM Streets S
LEFT OUTER JOIN Users U ON U.Streetid = S.Id
GROUP BY S.Name

Result


How to perform a pivot on multiple columns

So here's what I did. I put what you had in a temp table and then grouped the results like so:

;WITH data AS (
  SELECT grp, k, k_ts = k + '_ts', ts, val
  FROM @Source

)
SELECT *
INTO #tmp
FROM data
PIVOT ( MAX(val) FOR k IN ([color], [shape], [subst]) ) p1
PIVOT ( MAX(ts) FOR k_ts IN ([color_ts], [shape_ts], [subst_ts]) ) p2

Select
    grp
    , max(color) as color
    , max(shape) as shape
    , max(subst) as subst
    , max(color_ts) as color_ts
    , max(shape_ts) as shape_ts
    , max(subst_ts) as subst_ts
from #tmp
group by grp

It's not pretty, but it worked.


How would you make this function work on any table?

In another answer, @SQLRaptor makes the suggestion of using STRING_AGG. Working with that, it seems like the following should do the trick:

SELECT  AdmitDateTime, RoomID, med.stuffMeds
FROM    AbstractData AD
OUTER APPLY (
    SELECT  STRING_AGG(DataItemID, ', ') WITHIN GROUP (ORDER BY DataItemID ASC) stuffMeds
    FROM    EmrAcctPha_Medication med
    WHERE   AD.VisitID = med.VisitID
) med


Yes you can create CNAME pointing to listener name. I have been using the same for over 100 Availability group. SQL Server version 2012, 2014, 2016 and 2017. I have been using this for close to 3 years without any issue.

Two suggestions for better manageability if you go to that route enterprise wide.

Write a collection of all CNAME pointing to listeners, save it in
your DBA database and update periodically. That way during any
migration you are not in doubt what CNAME's are pointing to a
particular listener name.
I prefer for DBA team to have privilege to update CNAME. That way during a migration you are not dependent on someone else to make the cut over.


How to create multiple graphs with TitanDB (+Cassandra) in the same machine?

See https://github.com/thinkaurelius/titan/wiki/Using-Cassandra
scroll down to "Cassandra Specific Configuration", and
use property "storage.keyspace=" to use multiple keyspaces in cassandra.
Each keyspace will hold a graph. (default keyspace is 'titan'.

So, if you want 3 graphs, create 3 config files, with diff "storage.keyspace" property.

When you open a graph from your client using TitanFactory.open(config), use the appropriate config file.


How to define the logical name in a backup ? ( Sql Server 2008 )

The logical file names returned by RESTORE HEADERONLY are those of the source database.  You cannot change these logical names during the backup or restore.  The backup file always contains the both the original logical and physical file names.

The original logical names may be specified with the MOVE option of RESTORE to relocate/rename the physical files during the restore process.  After the restore, you can rename the logical names using ALTER DATABASE:

ALTER DATABASE Teste_Dan
MODIFY FILE(NAME='Teste_Ferdando_Index', NEWNAME='Teste_Dan_index');

Personally, I avoid including the database name in the logical name to avoid this issue, only including the database name in the physical name.  Unfortunately, the database name is often included in the logical name by default.


How can I avoid that

The only way to avoid this is by not shrinking that is why shrinking is such a evil operation. But there are situations where conditions demand a shrink to be done to release excess space and for that case you have no option but to do it. You would then have to run defragmentation of the indexes. Paul Randal in This Blog showed alternate way (read the whole blog this is exactly what you may need).

Further I would also suggest you to read Mitigating Index Fragmentation

  The method I like to recommend is as follows:

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the
  tables and remove fragmentation from them at the same time
  Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

This may not always be possible in all environments but this is good alternate of shrinking.

  Or is there a way to defragment, for instance, if I restore a database, will it be defragmented again? Or will it have same fragmentation as the original db?

If you restore a database which has index defragmnated you do not need to run index defragmentation again after the restore. Restore creates exact copy of the database


Migrating database from SQL Server 2005 Enterprise to SQL Server 2008 Standard

Well, if you're only concerned with how to get around this partitioning issue you have, and are sure you won't face performance issues after you move to a single partition, then you could try to collapse all of your partitions into 1, and there's lots of ways to do.

If you feel your system can handle it, you could dump all the data into another table and give it a max ID number to dump into.  Then when the bulk of the data is copied into there, you could note the ID, find any new records that came in.  If it's a huge amount of records, dump those new records in the new table as well.  After the 2nd dump you should be almost caught up.  At that point you do not allow new data to come in for a few seconds/minutes while you run pre-scripted out commands to:

-move the remaining data into the new table that doesn't have partitioning enabled after making sure the schemas match.

-rename the old table.  Make sure you don't have blocking issues.

-rename the new table to the old table.

You might want to avoid adding indexes at the beginning, and do those at the very end.

If doing this on the live system isn't an option, you could move the data to another system and do all these steps there.

Perhaps you could use this opportunity to archive your old data.  You could consider creating a view and have your old data in a different archive read only database and have it referenced with a view.

How much data is it anyways, with and without indexes?


Are view references in a query properly called "derived tables" as such?

From a SQL Server perspective, a table expression is a named query. This concept of a named query seems in line with what @a_horse_with_no_name commented on earlier. There are four types of of table expressions which are:

Derived Tables
Common Table Expressions
Views
Inline Table-Value Functions

A Derived Table is a subquery that returns an entire table result.
A CTE is like a derived table but usually thought to be more readable and modular. It also has a recursive feature. A View is a reusable named query in which the definition of the table expression is stored. The primary difference between a View and an Inline Table-Valued Function is that the latter takes parameters whereas the former does not.

This has been compiled from the discussion on Table Expressions in Ben-Gan, et al, Querying Microsoft SQL Server 2012, Chapter 4.


Thank you for your response on my queries.

My thoughts on this:

Periods are only to label your data
It is not labeling every data of your database
Since as per your clarifications "When inserted, it is not checked if there is a period to insert the information", periods may overlapped each other
Query is not essentially labeling every data with a periods
Database is very small in size,

Linking a table to others needs some sort of consistency which is not available in this case.  Seeing these inputs, IMHO, we can safely keep this table as separate entity.

From Performance aspects, we can make better indexes based on our application's need on period table to avoid any bottleneck.

Thank you,

Sanjeeva


How to make table with row name and column name

You should revise your table structure.
You are trying to put too many different things into one table.

What you could do is have multiple tables like so:

Table: site
Has an ID and all other details about the site!
Table: Devices
Has an ID and all other details about the device.
Table: measurements
Has an ID and all other details about the measurement.

Now depending on if your measurements are linked to a single device or not you may need an intersection table or a device ID on the measurement,  and the same goes for the site.

When you wish to select all the details about a site,  you will need to use joins to get everything,  or maybe multiple queries.


A first step in improving the performance of WordPress is to change the "meta" schema as discussed here:

http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

If that does not help enough, I will take a deeper look at your query.


PosgreSQL: very slow select on a single table with no joins

Running an EXPLAIN ANALYSE VERBOSE massively underestimates the time
  taken to return the results

There's a misunderstanding here, because EXPLAIN ANALYZE does not estimate, it runs the query for real and reports the actual time taken by each steps, as opposed to EXPLAIN without ANALYZE that just reports the estimates without running the query.

Consider this line from your EXPLAIN ANALYZE output:

  (actual time=0.135..594.929 rows=426934 loops=1)

These 595 milliseconds are not an estimate, it's actual time. Besides, when postgres shows estimates, they're expressed in units of "cost", not in units of time.

When it's finished, EXPLAIN ANALYZE sends back the result of the analyze to the client, and discards the rows of the actual result. That differs from a real SELECT which has to send back the rows to the client.

For this reason, the big difference between both operations in your case could/should be accounted by the slowness in receiving the results, either because the network is slow, or because the client is slow, or both. Maybe the client is swapping like crazy if the resultset is too big to fit in the available RAM. I'd look at the vmstat 1 and iftop outputs (or even strace) on server and client during these 2 minutes to check what's doing what.


Does the primary key of a table help decide what block a row is written to in Oracle?

No, the values of a row have no implications for where Oracle will store it in a heap table. The only real exception is that if your row is going to be huge, Oracle will probably have to put it on a new block.
What makes you think the foreign key check is the cause for your poor performance? What’s the process that is too slow and how slow is it? A foreign key check on an insert requires very little IO as it’s just an existence check against an indexed column. There is usually much more significant low hanging fruit.


Always-On Read Only Routing vs External Load Balancer

Is it better to use always on feature "read only routing" or to use an external load balancer to balance connection between replicas ?

That's going to 100% depend on your needs. For example, if you're just wanted to have readable workloads hit a secondary, then inside of SQL Server should be fine. Now, if you wanted to hit the secondary that is closest (geographically) to your internal users depending on where they are... then you'll need some fancy rules and external load balancing.

Regardless which you choose, unless creating your own health check or probe the external load balancer will not know which secondary servers are healthy or unhealthy.

  does not need a dba to do network work

Neither of the ways requires a DBA to do "network" work, you setup the URL (SQL) and the list (SQL) and magic starts to happen.

  Does somebody have any insight or feedback on always on ROR?

Use it where you need it, it's just another tool in the collection. You may find that connecting directly to the secondary is better for your use cases and scenarios that using ROR... or you may write your own health checks for the external load balancer (if it supports it) to only route to secondary servers that take into account locality of the user.


How does MySQL decide what partitions to load into memory?

InnoDB's "buffer_pool" contains blocks that are kept there in (roughly) a "least recently used" order.  That is, when you touch a block for either reading or writing, it is pulled into the buffer_pool and marked as "most recently used".  If there was not room for this block, then the "least recently used" block was first removed from the buffer_pool.

PARTITIONing has nothing to do with the caching scheme.  In particular, partitioning by the PRIMARY KEY is almost always useless, and almost always redundant with letting the caching (described above) happen.

If you would like to discuss performance improvements, please provide SHOW CREATE TABLE and the slow query.

Using PARTITION BY RANGE(TO_DAYS(...)) is an excellent use of partitioning, but only because it makes dropping "old" data very efficient.  Note: I did not say BY RANGE(id).

Even if the math is correct, you may or may not have "the last 3 months" cached in RAM -- if someone scanned stuff from 4 months ago, someblocks would have to be bump to allow for that scan.  Then the 4-month-old bllcks would clutter the buffer_pool until they get bumped out.

Update

Since you have a "mission critical" system that must accept writes all the time, and have no PRIMARY KEY, you need some drastic action.  Here's what I would do.

I would back up into the code.  If there is not already code between the "users" and the database, I would start by building such a layer and force the "users" to call an API that say "write ... to the database".

To start with, the API would simply do an INSERT like the original code.  This has minimal impact.

Then I would prepare new code, wherein the API has more complex code behind it.  In particular, it would be writing to the existing table and the new table (with PRIMARY KEY, new index(es), changed datatypes, PARTITIONing, etc).

Meanwhile, another script would be copying, in chunks, the existing data from the existing table to the new table.  (This is part of what pt-online-schema-change does, but we will take some liberties.)

I suggest chunk size of 1000 rows, each chunk as a its own 'transaction' (assuming you are a using InnoDB).  The chunking should follow some index, whichever one closes to being unique.  (An yes/no column would be poor because you could not get more than 2 chunks.)

When all finished, you will have two copies of the data; you can test before dropping the old table.

Reads can proceed unchanged during the process because the old table remains intact.

Some blogs with tips:

Altering a huge table
Chunking
Partitioning
Don't use OFFSET


How does MS SQL Server decide when it can use "Parallelism" when executing queries?

SQL Server makes this decision based upon cost. If you execute 'sp_configure' (make sure advanced options are enabled) you will see an entry for 'cost threshold for parallelism'. If the optimizer estimates the cost to be greater than the run_value then your query will be executed in parallel.

If the ORDER BY clause in your example results in an increased cost, then the query will go parallel.


Group by checks constraints are possible?

You can use a unique index for that. Example:

create unique index one_leader_per_team
  on members
    (case when IS_LEADER = 'Y' then TEAM_ID else null end) ;

What it does is store team_id - in the index - only for those rows that have IS_LEADER = 'Y' and nulls in the rest of the rows  The UNIQUE ensures that have only one row per team with 'Y' and the nulls are ignored.

(I think that up to 11 and older version, Oracle doesn't store the nulls at all in the index, so the index is more space efficient - not sure if this has changed in version 12). That means that the Oracle index is equivalent to a filtered/partial index in other DBMS (SQL Server, PostgreSQL), i.e.:

-- this would work in SQL Server, Postgres
create unique index one_leader_per_team
  on members (TEAM_ID)
  where (IS_LEADER = 'Y') ;

Test in dbfiddle.uk:

create table members
  ( MEMBER_ID integer not null primary key,
    TEAM_ID integer not null,
    NAME varchar(64) not null,
    IS_LEADER char(1)  default 'Y' not null check (IS_LEADER in ('Y','N'))
  ) ;

create unique index one_leader_per_team
  on members
    (case when IS_LEADER = 'Y' then TEAM_ID end) ;

insert all
  into members values (1, 1, 'Alex', 'Y')
  into members values (2, 1, 'Bill', 'N')
  into members values (3, 1, 'Cath', 'N')
  into members values (4, 2, 'Diana', 'N')
  into members values (5, 2, 'Elsa', 'Y')
select * from dual ;

5 rows affected

insert all
  into members values (6, 3, 'Fay', 'Y')
  into members values (7, 3, 'George', 'Y')
select * from dual ;

ORA-00001: unique constraint (FIDDLE_TITLKRLCAFRLTVCBXZVI.ONE_LEADER_PER_TEAM) violated

insert into members values (8, 1, 'Harry', 'Y');

ORA-00001: unique constraint (FIDDLE_TITLKRLCAFRLTVCBXZVI.ONE_LEADER_PER_TEAM) violated

select * from members ;

MEMBER_ID | TEAM_ID | NAME  | IS_LEADER
--------: | ------: | :---- | :--------
        1 |       1 | Alex  | Y
        2 |       1 | Bill  | N
        3 |       1 | Cath  | N
        4 |       2 | Diana | N
        5 |       2 | Elsa  | Y

db<>fiddle here


Does UPDATE INDEXES work for local indexes?

From the Maintaining Partitions documentation:

You can optionally specify if local indexes are also to be exchanged (INCLUDING INDEXES clause), and if rows are to be validated for proper mapping (WITH VALIDATION clause).
[...]
Unless you specify UPDATE INDEXES, the database marks UNUSABLE the global indexes or all global index partitions on the table whose partition is being exchanged. Global indexes or global index partitions on the table being exchanged remain invalidated.

I believe you'll need to create a matching index on table2 and use both INCLUDING INDEXES and UPDATE INDEXES (but don't have a way of testing it right now).


Maybe you can try with $elemMatch :
https://docs.mongodb.com/manual/reference/operator/projection/elemMatch/


Maximum memory for SQL Server 2000 TB when my RAM is just 512 GB

Think of the "Maximum server memory" setting in your right screenshot as a limit setting. You can configure SQL Server so that it won't use any more than a certain amount.

By default, this number is effectively unlimited. (That's what your server is set to.) It's kinda like setting your own personal financial budget - by default, many of us are willing to spend an unlimited amount of money on things that we should probably avoid.

Generally, you want to cap SQL Server's memory consumption, and there are varying recommendations out there. In our SQL Server Setup Checklist, we recommend leaving 10% free for the OS as a starting point. That means you'd set SQL Server's maximum server memory to 512 * 90% = 461GB, so you'd put 461000 in the max server memory box. (It's measured in MB, not GB.)

It might seem odd to leave over 50GB of memory free, but I've seen what you people do on your SQL Servers. You remote desktop in, you run SSMS, SSIS, SSRS, SSAS, and Outlook from the SQL Server. If you run a tight ship, and you never remote desktop in, then you can go higher on max memory. (Although since you're remote desktopping in to run this stuff, you should probably leave memory free. And also, stop remote desktopping in.)


How to find the number of tuples that are vacuum-eligible?

It clearly tells you right there..

  DETAIL:  2185476 dead row versions cannot be removed yet.

That's how many versions are vacuum-eligible. They're not being vacuumed because you have a long running transaction holding a ACCESS SHARE lock on the table?

See this answer.