Why does altering a table and adding a foreign key create an extra constaint?

The only way I can think of that a DDL statement like yours may result in an "extra" constraint is when there's a DDL trigger that creates that constraint in response to your DDL statement(s). If that is not the case with your database, the said extra constraint is definitely the result of a totally different statement unconnected with the one that creates the foreign key.

It could have appeared at an earlier stage (e.g. when the table was being created), or it could have been added by a statement that was executed in the same script with the one in your post and that fact for some reason didn't register with you at the time.


Postgresql two tablespaces one on SSD and one on SATA III HD

You can set seq_page_cost and random_page_cost per tablespace via ALTER TABLESPACE without restarting Postgres.


The hypothetical indexes created by the Index Tuning Wizard start with a name of "hind_%" and should not exist after the tuning has finished; they should all be removed. You can run the following script from the SQL Server Query Analyzer to remove any such indexes that may exist. You must log in by using an account that has either sysadmin or db_owner permissions, or is the owner of the object on which these statistics were created.

For example

DECLARE @strSQL nvarchar(1024)
DECLARE @objid int
DECLARE @indid tinyint
DECLARE ITW_Stats CURSOR FOR SELECT id, indid FROM sysindexes WHERE name LIKE 'hind_%' ORDER BY name
OPEN ITW_Stats
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
WHILE (@@FETCH_STATUS  -1)
BEGIN
SELECT @strSQL = (SELECT case when INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 then 'drop statistics [' else 'drop index [' end + OBJECT_NAME(i.id) + '].[' + i.name + ']'
FROM sysindexes i join sysobjects o on i.id = o.id
WHERE i.id = @objid and i.indid = @indid AND
(INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND
INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0)))
EXEC(@strSQL)
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
END
CLOSE ITW_Stats
DEALLOCATE ITW_Stats

Above script is provided by Microsoft for more detail you can visit following link.
http://support.microsoft.com/kb/q293177


What is a valid use case for using TIME WITH TIME ZONE?

There is no valid use case. time with time zone is broken by design and only included in Postgres since it's in the SQL standard. Don't use it.

Time zones can have daylight saving time (DST) rules, which do not work with timetz at all.

In fact, the use of time with time zone is officially discouraged:

  The type time with time zone is defined by the SQL standard, but the
  definition exhibits properties which lead to questionable usefulness.
  In most cases, a combination of date, time, timestamp without time zone,
  and timestamp with time zone should provide a complete range of
  date/time functionality required by any application.


How to detect compatibility errors - SQL Server 2012

Can you show the query? Just because it runs doesn't mean it's correct. :-) For example this is not legal, but it works:

SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date
FROM sys.objects AS o
ORDER BY o.modify_date;

Technically, it should be as follows, since o.modify_date is not in the SELECT list:

SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date
FROM sys.objects AS o
ORDER BY modify_date;

In this Connect bug, it was stated that this rule would be removed from the Upgrade Advisor. I suspect that one of the following (or both) happened:

They forgot to remove the rule as they said they would
They had other complications which prevented the above syntax from being blocked by the parser

That all said, there are some breaking changes the upgrade advisor will never catch. For example, this will work on 2005, 2008 and 2008 R2:

CREATE TABLE #foo(id INT);

IF OBJECT_ID('tempdb..#foo') > 0
BEGIN
  DROP TABLE #foo;
END
GO

CREATE TABLE #foo(id INT);

This will break in SQL Server 2012, however, since #temp tables now get a negative object_id. The proper way to test is:

IF OBJECT_ID('tempdb..#foo') IS NOT NULL

I go over a few other issues in this blog post. A couple of other breaking changes:

https://sqlblog.org/2011/06/28/sql-server-v-next-denali-breaking-change-to-fn_virtualfilestats
https://sqlblog.org/2011/07/08/sql-server-v-next-denali-breaking-change-to-system-databases-database_id-db_id

If you use any of the memory-related DMVs there has been a major overhaul to some of the columns:

https://blogs.msdn.microsoft.com/sqlosteam/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012/

Again, most of these are not caught by the upgrade advisor, so running that tool and not doing any thorough testing could really put you in a bad spot.

And I agree with Eric's answer - you should try to track deprecated events. For completeness, here are three approaches:

Profiler
Server-side trace
Extended Events


Why is replication attempting to distribute NULL values into Identity columns

After much trial and error we think the answer was and has always been triggers! Despite being disabled in the subscriber they kept on being re-enabled which was causing a second insert command to run for affected replicated commands.

Basically check your triggers!


You where clause is incomplete and wouldn't work that way anyway.

You need a subselect for this:

SELECT   *
FROM the_table
WHERE cli_nom in (select cli_nom
                  from the_table
                  where CLI_ENSEIGNE = 'A' );

alternatively a co-related sub-select with an exists clause:

SELECT t1.*
FROM the_table t1
WHERE EXISTS (select 42
              from the_table t2
              where t2.cli_nom = t1.cli_nom
                and CLI_ENSEIGNE = 'A' );


https://docs.microsoft.com/en-us/sql/reporting-services/report-design/add-a-hyperlink-to-a-url-report-builder-and-ssrs
... add a hyperlink

On the Action tab, select Go to URL. An additional section appears in the dialog box for this option.
In Select URL, type or select a URL or an expression that evaluates to a URL, or click the drop-down arrow and click the name of a field that contains a URL.
For an item published to a report server configured for native mode, use a full or relative path. For example, http:///images/image1.jpg.
For an item published to a report server configured in SharePoint integrated mode, use a fully qualified URL. For example, http:////Documents/images/image1.jpg.
Click OK.

After you add a hyperlink

(Optional) The text is not automatically formatted as a link. For text, it is helpful to change the color and effect of the text to indicate that the text is a link. For example, change the color to blue and the effect to underline in the Font section in the Home tab of the Ribbon.
To test the link, click Run to preview the report, and then click the report item that you set this link on.


What are the disadvantage/consequences of moving updated column to separate table to prevent table lock on the original table?

From what I remember about this database from your earlier questions, I'd suggest you look at these alternatives to your approach:

Consider RCSI or Snapshot isolation. This will prevent your select queries being blocked during the update. Be mindful of the increased usage of tempdb this can generate but IIRC the nature of your database/data should mean this is small.
Updating the points record in batches, rather than one almighty transaction for the entire table. See How to Use Batch Size to Speed Mass Updates, Inserts and Deletes for an old (but still relevant) approach.


As Martin Smith suggested in a comment the necessary syntax is very similar to FOR XML. Here is how I solved my problem:

SELECT
    teste2.descricao AS cliente,
    teste2.id AS clienteID,
    (
        SELECT
            teste.id,
            teste.descricao AS produto
        FROM relacao
        INNER JOIN teste ON relacao.idTeste = teste.id
            WHERE relacao.idTeste2 = teste2.id
       FOR JSON PATH
    ) AS produtos
FROM teste2
FOR JSON PATH, ROOT('clientes')

Result:

{
    "clientes": [{
        "cliente": "Raphael Schubert",
        "clienteID": 1,
        "produtos": [{
            "id": 1,
            "produto": "Casa"
        }, {
            "id": 2,
            "produto": "Carro"
        }]
    }, {
        "cliente": "Lorraine Schubert",
        "clienteID": 2,
        "produtos": [{
            "id": 3,
            "produto": "Moto"
        }, {
            "id": 1,
            "produto": "Casa"
        }]
    }]
}


You are obviously suggesting that CONSTRAINTs in a database should be enforced by the application(s) that/which access that database?

There are many reasons why this is a bad (bad, bad...) idea.

1) If you are building a "roll-your-own" constraint "engine" (i.e. within your application code), then you are merely emulating what Oracle/SQL Server/MySQL/PostgreSQL/<.whoever...> have spent years writing. Their CONSTRAINT code has been tested over those years by literally millions of end-users.

2) With all due respect to you and your team, you are not going to get it right even in a matter of years - from here, MySQL code alone cost 40 Million dollars. And MySQL is the cheapest of the 3 servers above, and they don't even implement CHECK CONSTRAINTs. Obviously, getting R.I. (Referential Integrity) completely right is difficult.

I used to frequent the Oracle forums and I can't tell you the number of times that some poor manager/programmer has had a project thrust upon him where the genius who had his job before had the "bright" idea of doing what you suggest.

Jonathan Lewis (he wrote a 550 page book on the fundamentals of the Oracle optimiser) gives as no. 2 of his Design Disasters in another book ("Tales of the Oak Table" - the Oak Table is a group of Oracle experts) is

  We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.

3) Even if by some miracle you can properly implement RI, you will have to completely reimplement it time and again for every application that touches that database - and if your data is important, then new applications will. Choosing this as a paradigm will lead to you and your fellow programmers (not to mention support staff and sales) to a life of constant fire-fighting and misery.

You can read more about why implementing data CONSTRAINTs at the application level is nothing short of madness here, here and here.

To specifically answer your question:

  Just why are they declared at all? It seems very helpful, but is it
  actually necessary to have a database that functions

The reason that KEYs (either PRIMARY, FOREIGN, UNIQUE or just ordinary INDEXes) are declared is that, while it is not strictly necessary for a database to have them for it function, it is absolutely necessary for them to be declared for it to function well.


Random unguessable primary key which preserves correct order

Don't do that.

If you want to make a user-unique-and-crazy-key don't use the primary key of the table, create another column with this kind of data and you can generate a unique code using md5 of the unix microtime for example.

The Human world are strange for databases, believe me or not! Let the keys to be keys, its nice and faster to table relationships.

And for any user or system search you use another column, its very like to want to relationship users by using a person one identification code, so you change the country and discovery the code repeats, and all your database is lost. You want a unique crazy code, but it does not mean nothing for your machine, how your machine will work for nothing to make your relationships working. For relationships use things that your machine can really understand, in another words: Use sequential numbers.


Here's a link describing and solving the same problem, do note that you still have to make your own pfile, but that isn't hard.
http://myracle.wordpress.com/2008/01/11/recover-database-without-control-files-and-redo-log-files/


Clarification on UNION ALL of JSONB_EACH result

Question 1
From PostgreSQL Documentation Table 8.23 - JSON primitive types and corresponding PostgreSQL types:

JSON primitive type | PostgreSQL type | Notes
null                |(none)           | SQL NULL is a different concept

So you must not confuse SQL NULL value with with JSON null type.

Question 2
In your previous question you wanted the JSON value null to act as a kind of DELETE type. If you have both {"b": 3} and {"b": null} in the UNION and you do value <> 'null' the {"b": 3} will not be deleted. When you do NOT IN the pair b:3 will be left out of the new set too (the null type is found for the key "b" in the subquery).

Question 3
Related to question 1: NULL::JSONB means a SQL NULL value of the data type JSONB, not a JSON null type.


Two things:

As you posted 600% of CPU usage, I'll guess that you are using Linux, so no worries, actually this means that you are just using 100% of 6 processors, so you still have 10 processors totally free. When you start seeing a CPU usage above 1400% then you can start worrying.
More important than point number 1 is the crazy mess you have in your config file. You can't just grow those values with no knowledge of what are you doing. My recommendations is to hire a experienced DBA or learn how to tune a MySQL server properly.


If you doing on a regular basis UPDATE and/or DELETE statements on a table the SELECT speed will decrease over time because the data and indexes (if present) will be fragmented thus the search process will take more depending on the degree of fragmentation so i purpose :

to run OPTIMIZE on the afflicted table on a regular basis (once a day should be enough) because this will defragment the data and rebuild the indexes (if present)
add indexes (if you do not already have) on the columns most commonly used in SELECT statement


Is CHARSET important for tables with INT only?

The character set and collation of a table affects the newly created columns in that table. It also affects char/text columns without a specified character set/collation.
So your int only tables don't have any specific difference because of character set, until you add a column that uses it.


Is varchar(x) as fast as `text CHECK ( char_length(x) )`?

No, it's not strictly true. Here is what the linked docs say,

  There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

So essentially

There is no reason to use varchar over text when the column is unconstrained. This is not true in some databases, namely ones that implement schemes like VARCHAR(max).
When it's constrained in a column, it's slower. There is a reason to use varchar(x) over text CHECK ( length(x) <= x)

Example

You can easily see this,

\timing 1

CREATE TABLE foo (
  x varchar(255) NOT NULL
);

CREATE TABLE bar (
  x text NOT NULL
    CHECK (char_length(x) <= 255)
);

INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);

INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);

INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);

INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);

Note, char_length and length are the same here. They internally call textlen.

Results

test=# INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
Time: 1156.529 ms
test=# INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);
Time: 1268.869 ms
test=# INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
Time: 1107.869 ms
test=# INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);
Time: 1283.043 ms
test=# INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
Time: 1121.788 ms
test=# INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);
Time: 1238.194 ms
test=# INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
Time: 1116.421 ms
test=# INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);
Time: 1248.272 ms

In each case, the insert into bar is slower

Type-constraints rechecked

I would have though too that the checks would have carried over to tables using the same types. This kind of optimization may be possible some day, but alas no joy,

test=# CREATE TABLE foo2 ( LIKE foo INCLUDING ALL );
Time: 7.143 ms
test=# CREATE TABLE bar2 ( LIKE bar INCLUDING ALL );
CREATE TABLE
Time: 10.762 ms
test=# INSERT INTO foo2 TABLE foo;
Time: 3613.517 ms
test=# INSERT INTO bar2 TABLE bar;
Time: 4061.650 ms

Though the varchar form is still noticeably faster.

Other Notes

As it relates to emails the subject of the tweet, this post may also be useful.


Does the keyword NEW have the per-connection basis aspect to it?

A trigger is fired by a SQL statement that makes changes to data in the table for which it is defined. It is important to note that triggers are executed for each row of data affected, not for each statement executed. Therefore the scope of new is actually more limited than session variables (which are connection-specific). The new and old aliases exist only within the local scope of the trigger, so concurrency should not be an issue as long as it is handled correctly in any stored programs which cause a trigger to fire.


migrate users from mariadb10.1 to mysql5.7

Use pt-show-grants utility to generate a "logical dump" of your accounts and grants. You can do the same thing manually if you just run show grants for for each user, but this percona-toolkit utility will make it even easier.

Unless you have special configuration or authentication plugins, this should mostly work (unlike trying to dump and restore all user and priv tables physically). However, in some cases (in compatible permissions, roles, plugins), you may want to review after sending the sql for errors or missed grants. Also check for configurations that may make the loading failing, like temporarily disabling NO_AUTO_USER_CREATE and the default authentication plugin (native, not sha...).

I recently ported MariaDB 10.1 grants to MySQL 8 and it required some iterations, such as different names for the unix socket authentication plugin, the default authentication plugin (vs native) and the usage of VIA vs WITH. Mostly due to the default configurations, something you will have to work in anyway for the migration.


How to login with Windows Authentication mode after domain name changes in SQL Server?

You should login with SQL authentication (with your sa account)
and run this query:

DROP LOGIN [OldDomain\UserName]
GO

CREATE LOGIN [NewDomain\UserName] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NewDomain\UserName]
GO

You will then be able to login with your new domain name (be sure you change the domain name and username in the code).

If you need to recover your sa password you can read this article
http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/


How to send only updated values of a row?

This sends a JSON array of columns that actually changed, with their new value:

CREATE OR REPLACE FUNCTION notify_col()
  RETURNS trigger AS
$func$
BEGIN
   PERFORM pg_notify('channel'
                   , (SELECT json_agg(n)::text
                      FROM   json_each_text(to_json(OLD)) o
                      JOIN   json_each_text(to_json(NEW)) n USING (key)
                      WHERE  n.value IS DISTINCT FROM o.value));

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER tbl_upd_aft
BEFRE UPDATE ON tbl
FOR EACH ROW
WHEN (OLD IS DISTINCT FROM NEW)  -- optional
EXECUTE procedure notify_col();

db<>fiddle here

If works by converting OLD and NEW to JSON and expanding each to a set of key/value pairs. Then join on the key and only keep columns (now rows) that actually changed. Finally aggregate into a JSON array.

Note the WHEN condition to the trigger. Updates that do not actually change anything don't fire the trigger. (So the trigger is guaranteed to process actual changes.)

If there can be more triggers consider an AFTER trigger to only report what's actually changed. And reconsider the WHEN condition if triggers might add changes - which would circumvent this condition.

Related:

Is it possible to dynamically loop through a table's columns?
Trigger with multiple WHEN conditions
How do I (or can I) SELECT DISTINCT on multiple columns? (about empty updates)

Aside: consider this note in the manual:

  array_to_json and row_to_json have the same behavior as to_json except for offering a pretty-printing option.


composite clustered index with partitioning

I do highly recommend the link above then.  I actually gave a presentation on this to my SQL User Group a few months ago.  Here is a possible table structure for you including a partition aligned non-clustered index, as well as the procedure I used to rotate the partitions around.  It should help you get started.

Link to article: http://kejser.org/table-pattern-rotating-log-ring-buffer/

CREATE PARTITION FUNCTION [PF_100](int) AS RANGE RIGHT FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100)
GO

CREATE PARTITION SCHEME [PS_100_dbo] AS PARTITION [PF_100] ALL TO (DBO)
GO

CREATE TABLE dbo.MyLog
    (
    UniqueID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_MyLog_UniqueID DEFAULT (NEWSEQUENTIALID())
    , LookupID INT NOT NULL
    , TypeID INT NOT NULL
    , Created DATETIME2(7) NOT NULL
    , PartitionOffset AS (CONVERT(INT, DATEDIFF(DAY,CONVERT(DATETIME, 0),Created)%(100))) PERSISTED NOT NULL
    , CONSTRAINT PK_MyLog_UniqueID PRIMARY KEY CLUSTERED (UniqueID, PartitionOffset)
    ) ON PS_100_dbo (PartitionOffset)

CREATE NONCLUSTERED INDEX IDX_MyLog_LookupID_TypeID_Created
    ON dbo.MyLog (LookupID, TypeID, Created, PartitionOffset)
    ON PS_100_dbo (PartitionOffset)

CREATE TABLE dbo.MyLog_SwitchOffset
    (
    UniqueID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_MyLog_SwitchOffset_UniqueID DEFAULT (NEWSEQUENTIALID())
    , LookupID INT NOT NULL
    , TypeID INT NOT NULL
    , Created DATETIME2(7) NOT NULL
    , PartitionOffset AS (CONVERT(INT, DATEDIFF(DAY,CONVERT(DATETIME, 0),Created)%(100))) PERSISTED NOT NULL
    , CONSTRAINT PK_MyLog_SwitchOffset_UniqueID PRIMARY KEY CLUSTERED (UniqueID, PartitionOffset)
    ) ON PS_100_dbo (PartitionOffset)

GO

CREATE OR ALTER PROCEDURE dbo.usp_SwichPartitions
    @SourceTableName NVARCHAR(128)
    , @TargetTableName NVARCHAR(100)
    , @PartitionsToKeep INT
    , @IsDebug BIT = 0
AS
BEGIN

    /** Procedure dbo.usp_SwichPartitions

        Based on http://kejser.org/table-pattern-rotating-log-ring-buffer/

        This procedure:
        * does a few sanity checks.
        * creates a small table with all possible dates, assuming that today is the most recent partition and that the offset modulo is as described.
        * Determines how many should be kept (most recent X partitions)
        * for any partitions that we want to discard, AND has data, switch to the @TargetTableName
        * truncate @TargetTableName

        INPUTS:
            @SourceTableName NVARCHAR(100)  The base table (include schema!)
            @TargetTableName NVARCHAR(100)  The table to switch to (include schema!)
            @PartitionsToKeep INT = 5       The number of partitions to keep (keep most recent X days)
            @IsDebug BIT = 0                Debug mode?

        @SourceTableName and @TargetTableName MUST exist (they must also have the exact same definition [indexes optional]

        @PartitionsToKeep must be greater than 1.

        @IsDebug (default 0), if set to 1, shows you a "what if" scenario, scripting out all of the required commands.
            NO CHANGES WILL BE MADE

    */

    /** For Testing values...
        DECLARE @SourceTableName nvarchar(100) = 'dbo.LogTable'
        DECLARE @TargetTableName nvarchar(100) = 'dbo.LogTable_SwitchTarget'
        DECLARE @PartitionsToKeep int = 5
        DECLARE @IsDebug bit = 0
        */

    SET NOCOUNT ON

    /** INPUT VALIDATION **/
    DECLARE @ThrowMessage NVARCHAR(1000);
    IF OBJECT_ID(@SourceTableName) IS NULL
    BEGIN
        SET @ThrowMessage = 'Specified @SourceTableName: ' + COALESCE(@SourceTableName, 'NULL') + ' does not exist (include schema).';
        ;THROW 50000, @ThrowMessage, 1;
    END

    IF OBJECT_ID(@TargetTableName) IS NULL
    BEGIN
        SET @ThrowMessage = 'Specified @TargetTableName: ' + COALESCE(@TargetTableName, 'NULL') + ' does not exist (include schema).';
        ;THROW 50000, @ThrowMessage, 1;
    END

    IF COALESCE(@PartitionsToKeep, 0) <= 1
    BEGIN
        SET @ThrowMessage = 'You must keep at least one (1) partition, you specified @PartitionsToKeep: ' + COALESCE(CAST(@PartitionsToKeep AS VARCHAR(10)), 'NULL') + '.';
        ;THROW 50000, @ThrowMessage, 1;
    END

    /** VARIABLE DECLARATION **/
    DECLARE @SQLCommand NVARCHAR(4000);
    DECLARE @AllPartitionCount INT;
    DECLARE @PartitionToSwitch INT;
    DECLARE @PartitionDate DATE;
    DECLARE @PartitionRowCount BIGINT;
    DECLARE @TimeToUse_UTC DATETIME2(7);
    DECLARE @RotateLogID INT;

    SET @TimeToUse_UTC = SYSUTCDATETIME();
    SET @AllPartitionCount = (  SELECT COUNT(*)
                                FROM sys.partitions AS P
                                WHERE P.object_id = OBJECT_ID(@SourceTableName)
                                    AND P.index_id IN (0, 1)
                                );

    DECLARE @PartitionsTable TABLE
        (
        PartitionTableID INT NOT NULL PRIMARY KEY
        , PartitionDate DATE NOT NULL UNIQUE
        , PartitionOffset INT NULL
        , PartitionNumber INT NULL
        , IsKeepPartition BIT NOT NULL DEFAULT (0)
        , PartitionRowCount BIGINT NOT NULL DEFAULT (0)
        );

    IF @AllPartitionCount IS NULL
        OR @PartitionsToKeep > @AllPartitionCount
    BEGIN
        ;THROW 50000, 'Cannot keep more partitions than the current buffer size', 1;
    END

    /** Build up a table of dates (starting from today) and going back for as long as many days as there are partitions.
        - PartitionOffset should match the computed column in the log table (# of Days since epoch % Total Number of partitions)  [-1 because of 0]
        **/
    ;WITH CTE_Number AS
        (
        SELECT 0 AS N
        UNION ALL
        SELECT N + 1
        FROM CTE_Number
        WHERE N <= @AllPartitionCount
        )
    INSERT INTO @PartitionsTable
    (PartitionTableID, PartitionDate, IsKeepPartition)
    SELECT TOP (@AllPartitionCount)
        N
        , DATEADD(DAY, (N *-1), @TimeToUse_UTC)
        , CASE WHEN N < @PartitionsToKeep  THEN 1 ELSE 0 END
    FROM CTE_Number
    ORDER BY N
    OPTION (MAXRECURSION 1000);

    UPDATE @PartitionsTable
    SET PartitionOffset = CONVERT(INT, (DATEDIFF(DAY, CAST(0 AS DATETIME), PartitionDate) % @AllPartitionCount))

    UPDATE @PartitionsTable
    SET PartitionNumber = PartitionOffset + 1

    --Get Row Count...
    UPDATE @PartitionsTable
    SET PartitionRowCount = P.row_count
    FROM @PartitionsTable AS T
        INNER JOIN sys.dm_db_partition_stats AS P ON P.object_id = OBJECT_ID(@SourceTableName) AND P.index_id IN (0, 1) AND (P.partition_number) = T.PartitionNumber;

    IF @IsDebug = 1
    BEGIN
        SELECT PartitionTableID
            , PartitionDate
            , PartitionOffset
            , PartitionNumber
            , IsKeepPartition
            , PartitionRowCount
        FROM @PartitionsTable
        ORDER BY PartitionDate DESC;
    END

    IF @IsDebug = 1
    BEGIN
        PRINT '/** Variables ';
        PRINT ' @TimeToUse_UTC = ' + CONVERT(VARCHAR(20), @TimeToUse_UTC, 121);
        PRINT ' @PartitionsToKeep = ' + CAST(@PartitionsToKeep AS VARCHAR(10));
        PRINT ' @SourceTableName = ' + COALESCE(@SourceTableName, 'NULL');
        PRINT ' @TargetTableName = ' + COALESCE(@TargetTableName, 'NULL');
        PRINT ' @AllPartitionCount = ' + CAST(@AllPartitionCount AS VARCHAR(10));
        PRINT ' **/';
        PRINT ' ';
    END

    /** Loop through every partitions that needs to be deleted that also
        has a PartitionRowCount.  Order by PartitionDate, removing the oldest first.
        */
    DECLARE curPartLoop CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR SELECT PartitionNumber
            , PartitionDate
            , PartitionRowCount
        FROM @PartitionsTable
        WHERE IsKeepPartition = 0
            AND PartitionRowCount > 0
        ORDER BY PartitionDate;

    OPEN curPartLoop;

    FETCH NEXT FROM curPartLoop
    INTO @PartitionToSwitch, @PartitionDate, @PartitionRowCount;

    IF @IsDebug = 1
        AND NOT(@@FETCH_STATUS = 0)
    BEGIN
        PRINT '/** NO PARTITIONS DETECTED AS NEEDING TO BE REMOVED **/';
    END

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @SQLCommand = CONCAT('ALTER TABLE ', @SourceTableName ,' SWITCH PARTITION ', CAST(@PartitionToSwitch AS NVARCHAR(10)), ' TO ', @TargetTableName, ' PARTITION ', CAST(@PartitionToSwitch AS NVARCHAR(10)));

        IF @IsDebug = 1
        BEGIN
            PRINT '/** @PartitionToSwitch = ' + CAST(@PartitionToSwitch AS VARCHAR(10));
            PRINT ' @PartitionDate = ' + CONVERT(VARCHAR(20), @PartitionDate, 121);
            PRINT ' @PartitionRowCount = ' + CAST(@PartitionRowCount AS VARCHAR(10));
            PRINT ' */';
            PRINT @SQLCommand;
            PRINT ' ';
        END

        IF @IsDebug = 0
        BEGIN

            EXEC sp_executesql @SQLCommand;

        END

        FETCH NEXT FROM curPartLoop
        INTO @PartitionToSwitch, @PartitionDate, @PartitionRowCount;

    END --WHILE @@FETCH_STATUS = 0

    --Cleanup.
    CLOSE curPartLoop;
    DEALLOCATE curPartLoop;

    --Truncate the switch table.
    SET @SQLCommand = CONCAT('TRUNCATE TABLE ', @TargetTableName);

    IF @IsDebug = 1
    BEGIN
        PRINT '/* TRUNCATE SWITCH TABLE */';
        PRINT @SQLCommand;
    END

    IF @IsDebug = 0
    BEGIN

        EXEC sp_executesql @SQLCommand;

    END

END


Diferent result between query and application (MySQL)

Make sure both approaches are looking the same DATABASE.

Further debugging:

Turn on the general log.
Run the query both ways.
Turn off the general log.
Compare the two queries; there must be something different.
Figure out what is changing what.

Meanwhile, these indexes may help performance:

creditos_ren:  INDEX(id_credito, canceladom fecha_vto, id)
creditos:      INDEX(gestion_local, enumerador)

What table is id_Cliente in?


What adverse effects does a table have when it's not even in 1nf?

No monsters will crawl out of the tables that are not in first normal form, however, you may turn into one when a simple change request requires major remodelling of your table structure. Let me go to this in further detail.

According to Wikipedia article on first normal form, it helps to

Eliminate repeating groups in individual tables.
Create a separate table for each set of related data
Identify each set of related data with a primary key

Now let's assume that Stack Exchange would have ignored this advice when designing their database back-end and have stored all their questions in one big table. When a tag gets added to a question a new row gets added to the question table. This all works fine until of the moderators has noticed that the SQL Server tag has been used inconsistently(SQL Server, Microsoft SQL, Microsoft SQ Server,etc.) and wants to clean this up.

In a normalised form this could be achieved by updating and deleting a handful of rows, however, without normalising you will need to update every single question that has been tagged incorrectly. This can result in locking of the database and freezing of the site. This means the moderator can't simply change the tags immediately using the moderator tools, and instead these changes would have to be queued until the site is less busy.

This normalised form would also have resulted in huge performance issues as the site grows in size. As each question can have multiple tags there would be many duplicate rows in your question table and it would get slower and slower to query.  You would, for example, quickly run into problems querying all available tags as you would have to query the entire question table instead of the tag table.


why my MySQL binary log file size is so small?

Is your MySQL Server restarting?  Check your uptime:

SHOW GLOBAL STATUS LIKE 'uptime';

The binary log gets closed at shutdown, and a new binary log created at startup.  The fact that you get "MySQL server has gone away" while querying above might also suggest the server restarted.


Should SQL Server 2000 throw an error if update statement fails within a SP?

I suspect what happened is that the row you expected to find was not found, perhaps because the name of the job was off, or because GETDATE() being evaluated multiple times will, once every so often, be evaluated on different seconds. So I'm not quite sure why you have a new job for every timestamp you're handling, but it's certainly plausible that it didn't match for this reason (just hard to reproduce on modern versions). See if #tmp ever gets populated:

SET NOCOUNT ON;
CREATE TABLE #tmp(i int);
GO

INSERT #tmp(i) SELECT o4.id + 0
 FROM sysobjects AS o0, sysobjects AS o1,  syscolumns AS o2,
      sysobjects AS o3, syscomments AS o4, syscolumns AS o5
WHERE CONVERT(char(8), getdate(), 108) <> CONVERT(char(8), getdate(), 108);
GO 1000

SELECT i FROM #tmp;
GO

DROP TABLE #tmp;

If you get one or more rows, then you know getdate() was evaluated differently. If you don't, you were lucky, this time.

To avoid this possibility, assign getdate() to a variable:

UPDATE /*app table*/
   set dateprocessed = @currentDate
 where jobname = 'Job_'
       + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '')
       + LEFT(REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''), 4)
       + '_'
       + (SELECT Description FROM #tempTable1 WHERE id = @i);

Remember (not just you, but future readers too), try this on SQL Server 2000. If memory serves, older versions were much more susceptible to this, and it is unlikely you will reproduce on modern SQL Server.


For two sums and three medians, on a single table, I honestly can't see the benefit of using a complicated dynamic or function-based solution.

It is quite easy to construct a single query using Peter Larsson's median method that I showed you before:

CREATE TABLE dbo.Listing
(
    ListingCol1 integer NULL,
    ListingCol2 integer NULL,
    ListingCol3 integer NULL,
    ListingCol4 integer NULL,
    ListingCol5 integer NULL
);

CREATE TABLE dbo.Result
(
    Col1 integer NULL,
    Col2 integer NULL,
    Col3 integer NULL,
    Col4 integer NULL,
    Col5 integer NULL
);

-- Just to show indexes are helpful for the median calculations
CREATE INDEX i ON dbo.Listing (ListingCol3)
CREATE INDEX j ON dbo.Listing (ListingCol4)
CREATE INDEX k ON dbo.Listing (ListingCol5)

Solution

INSERT dbo.Result
(
    Col1,
    Col2,
    Col3,
    Col4,
    Col5
)
SELECT
    SC.SumCol1,
    SC.SumCol2,
    SQ3.MedianCol3,
    SQ4.MedianCol4,
    SQ5.MedianCol5
FROM
(
    -- Sums + counts needed for the median calculations
    SELECT
        SumCol1 = SUM(L.ListingCol1),
        SumCol2 = SUM(L.ListingCol2),
        CountCol3 = COUNT_BIG(L.ListingCol3),
        CountCol4 = COUNT_BIG(L.ListingCol4),
        CountCol5 = COUNT_BIG(L.ListingCol5)
    FROM dbo.Listing AS L
) AS SC
CROSS APPLY
(
    -- Median for column 3
    SELECT
        MedianCol3 = AVG(1.0 * SQ.ListingCol3)
    FROM
    (
        SELECT
            L3.ListingCol3
        FROM dbo.Listing AS L3
        WHERE
            L3.ListingCol3 IS NOT NULL
        ORDER BY
            L3.ListingCol3 ASC
        OFFSET (SC.CountCol3 - 1) / 2 ROWS
        FETCH NEXT 1 + (1 - SC.CountCol3 % 2) ROWS ONLY
    ) AS SQ
) AS SQ3
CROSS APPLY
(
    -- Median for column 4
    SELECT
        MedianCol4 = AVG(1.0 * SQ.ListingCol4)
    FROM
    (
        SELECT
            L4.ListingCol4
        FROM dbo.Listing AS L4
        WHERE
            L4.ListingCol4 IS NOT NULL
        ORDER BY
            L4.ListingCol4 ASC
        OFFSET (SC.CountCol4 - 1) / 2 ROWS
        FETCH NEXT 1 + (1 - SC.CountCol4 % 2) ROWS ONLY
    ) AS SQ
) AS SQ4
CROSS APPLY
(
    -- Median for column 5
    SELECT
        MedianCol5 = AVG(1.0 * SQ.ListingCol5)
    FROM
    (
        SELECT
            L5.ListingCol5
        FROM dbo.Listing AS L5
        WHERE
            L5.ListingCol5 IS NOT NULL
        ORDER BY
            L5.ListingCol5 ASC
        OFFSET (SC.CountCol5 - 1) / 2 ROWS
        FETCH NEXT 1 + (1 - SC.CountCol5 % 2) ROWS ONLY
    ) AS SQ
) AS SQ5;

Expected execution plan shape:


You can customize this to your needs - (I agree that you should not be shrinking files as part of a scheduled maintenance task - Read these words of wisdom.)

DECLARE @SqlText VARCHAR(max)
DECLARE @BackupIsRunning BIT = 1 --Force initial value to 1

WHILE @BackupIsRunning = 1
BEGIN
    SELECT @SqlText = s.TEXT --Check for any BACKUP commands currently running
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
    WHERE r.command IN (
            'BACKUP DATABASE'
            ,'BACKUP LOG'
            )

    IF @@ROWCOUNT = 0 --If @@ROWCOUNT = 0, then no backups are running
    BEGIN
        SET @BackupIsRunning = 0 --Drop out of WHILE loop

        BREAK
    END

    WAITFOR DELAY '00:00:10' --Take a break and check again
END


I don't know much about this particular product - you probably should also ask this question directly to the vendor.
It looks like they'll simply give you a VM with more main memory and configure the database accordingly to use more memory for buffering of data file pages (shared_buffers). They might also configure more memory that can be used by each individual connection (work_mem), for example to sort tuples in memory instead of on disk, or for joins.
When asking the database to return data to you, having the pages already in the database's buffers hugely increase response times. Having more memory available for each query to do sorting and joins is advantageous with "analytical" queries.
Nobody in their right mind would think of selling PostgreSQL instances that use RAMdisks.
To achieve durability of changes to the database, PostgreSQL does not only apply the changes to the buffers (which will only eventually be written out to the disk), but it also records all changes into a Write-Ahead-Log (WAL), sometimes also called Transaction Log (XLOG).
Using the default configuration, a Writing Query is only confirmed to the client as having been written when this WAL has been flushed to the disk. I strongly assume that Azure follows this best practice.
In the end, aside from the amount of memory, and memory related settings, I doubt that there is much difference in the configuration of these machines. The database itself still works in much the same way, there is no general option to turn PostgreSQL into an in-memory database, it is always a database running on secondary storage, using primary storage to speed up most of the operations that one would notice judging by throughput or latency.


What's the difference between expensive and cheap SQL Server backup products?

Is there a particular reason you need to pay for a backup solution?

I think 'free' is always a good contender - Ola Hallengren's maintenance scripts are used by a LOT of people.

Simply schedule them using the SQL Agent.


SQL Server transferring results slowly in table result set, but instantly with XML

Those XML results you keep mentioning. I do not think you are seeing what you think you are seeing. ;-)

Is FOR XML magic?

When you have the XML result in the grid, click on the left margin that contains the row number of the result set (there should be just 1 row). That should highlight the XML result. Now hit Control-C to copy the XML and then in the query editor above, put the cursor on the beginning of an empty line and hit Control-V to paste the XML. That should leave the cursor on the far right, at the end of the XML. Look down at the status bar that has the Line #, Column #, and Character #. What does it shown for Column # and Character #? Mine shows 2033, yet the XML got clipped and there should be more.

Now click on the XML itself in the result set. That takes you to another tab with the full XML result, unclipped. That is more than likely well over 2033 characters of text.

The "instantaneous" result you are seeing with the FOR XML is merely the fact that SSMS is only displaying the first 2033 characters of however many there are, where it is 5000 or 5,000,000 characters more in the actual result value. And in fact, if your query takes long enough, you can pay close attention to the query timer in the lower, right-hand corner and if you re-run the query and are returning 10+ rows, that counter can still be going after the result grid finishes displaying its truncated XML snippet.

Do the number of rows and columns greatly affect the time to display?

I put together some code (shown below), which returns 2300 columns, to test this behavior. It starts out with the FOR XML being uncommented. After the initial run, it returns immediately each time, no matter how you adjust the @Rows input param. Set it to 10, then 20, then 40. The XML always comes back immediately. But also watch the query timer as it will keep going, especially as you increase the value of @Rows.

Then comment-out the SET statement that adds the FOR XML so you get the 2300 individual fields in the grid. It might take 30 takes to run the first time. Running again it took me only 2 - 3 seconds each time, even as I increased the @Rows from 4 to 10, 20, 40, 50, even 80. Hence, the time it takes to display the results for 2000+ columns is not a function of how many rows are being returned, at least not at this small scale.

The Code

DECLARE @Select NVARCHAR(MAX) = N'SELECT TOP (@Rows)
         OBJECT_NAME(sac1.[object_id]) AS [ObjectName], *';
DECLARE @From NVARCHAR(MAX) = N'FROM   [sys].[all_columns] sac1';
DECLARE @JoinTemplate NVARCHAR(MAX) = N'
INNER JOIN [sys].[all_columns] sac{{counter}}
        ON sac{{counter}}.[object_id] = sac1.[object_id]
       AND sac{{counter}}.[column_id] = sac1.[column_id]';

DECLARE @CRLF NCHAR(2) = NCHAR(0x0D) + NCHAR(0x0A);
DECLARE @Counter INT = 2;

WHILE (@Counter < 93)
BEGIN
  SET @From += REPLACE(@JoinTemplate, N'{{counter}}', CONVERT(NVARCHAR(5), @Counter));

  SET @Counter += 1;
END;

SET @Select += @CRLF + @From;
--SET @Select += @CRLF + N'ORDER BY OBJECT_NAME(sac1.[object_id])';
SET @Select += @CRLF + N' FOR XML RAW, ELEMENTS';

EXEC sp_executesql
  @Select,
  N'@Rows INT',
  @Rows = 4;

Uncomment the SET line that has the ORDER BY to see that it takes the full 30 - 40 seconds every time you run the query (due to the sort).


How do I identify the column(s) responsible for "String or binary data would be truncated."

No, it is not logged anywhere. Go vote and state your business case; this is one on the long list of things that should be fixed in SQL Server.

This was requested years ago on Connect (probably first in the SQL Server 2000 or 2005 timeframe), then again on the new feedback system:

binary or string data would be truncated => error message enhancments

And now it has been delivered in the following versions:

SQL Server 2019
SQL Server 2017 CU12
SQL Server 2016 SP2 CU6

In the very first public CTP of SQL Server 2019, it only surfaces under trace flag 460. This sounds kind of secret, but it was published in this Microsoft whitepaper. This will be the default behavior (no trace flag required) going forward, though you will be able to control this via a new database scoped configuration VERBOSE_TRUNCATION_WARNINGS.

Here is an example:

USE tempdb;
GO
CREATE TABLE dbo.x(a char(1));

INSERT dbo.x(a) VALUES('foo');
GO

Result in all supported versions prior to SQL Server 2019:

  Msg 8152, Level 16, State 30, Line 5
  String or binary data would be truncated.
  The statement has been terminated.

Now, on SQL Server 2019 CTPs, with the trace flag enabled:

DBCC TRACEON(460);
GO

INSERT dbo.x(a) VALUES('foo');
GO
DROP TABLE dbo.x;
DBCC TRACEOFF(460);

Result shows the table, the column, and the (truncated, not full) value:

  Msg 2628, Level 16, State 1, Line 11
  String or binary data would be truncated in table 'tempdb.dbo.x', column 'a'. Truncated value: 'f'.
  The statement has been terminated.

Until you can move to a supported version/CU, or move to Azure SQL Database, you can change your "automagic" code to actually pull the max_length from sys.columns, along with the name which you must be getting there anyway, and then applying LEFT(column, max_length) or whatever PG's equivalent is. Or, since that just means you'll silently lose data, go figure out what columns are mismatched and fix the destination columns so they fit all of the data from the source. Given metadata access to both systems, and the fact that you're already writing a query that must automagically match source -> destination columns (otherwise this error would hardly be your biggest problem), you shouldn't have to do any brute-force guessing at all.


How to know the problem when i make this Full Backup Scenario?

If the database is down when the backup is supposed to start the backup can not run. If the database goes offline while the backup is running the backup will simply stop. It'll need to be restarted after the database comes back online.

There won't be anything logged that there was a backup running when the database went offline. You'd need to look at the job history of the backup job to see if it was running when the database went offline.

There's no way to guarantee that the backups run without issue.


Change LAST_NUMBER value in a sequence (NEXTVAL)

Change increment by, select next value, set increment back to original value:

SQL> create sequence s1 start with 5;

Sequence created.

SQL> select last_number from user_sequences where sequence_name = 'S1';

LAST_NUMBER
-----------
          5

SQL> alter sequence s1 increment by 57;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
        61

SQL> alter sequence s1 increment by 1;

Sequence altered.

SQL> select last_number from user_sequences where sequence_name = 'S1';

LAST_NUMBER
-----------
         62

Or use RESTART:

SQL> alter sequence s1 restart start with 77;

Sequence altered.

SQL> select last_number from user_sequences where sequence_name = 'S1';

LAST_NUMBER
-----------
         77


ALTER PLUGGABLE DATABASE

pdb_open::=

instances_clause::=

To open the PDB on all RAC instances:

ALTER PLUGGABLE DATABASE PDB1 OPEN INSTANCES=ALL;


Postgres full text search with better match

You want to sort by ts_rank() or ts_rank_cd()

By default, those terms would give the same ranks, but you can normalize by length

Try:

select
  *
from geocode.addresses
where
  ...
order by
  ts_rank( to_tsvector(...), plainto_tsquery(...), 1) desc
limit 1

Where the ts_rank(..., ..., 1) divides the rank by 1 + the logarithm of the document length

There are other ts_rank normalizations in the link


Postgres Materialized View Dramatically Increasing In Size

My issue was running a "refresh materialized view concurrently" before the autovacuum had finished running, leading to table bloat. Now I manually vacuum analyze after every refresh.


Replication with MySQL 5.6 as the master and 5.7 as the slave

Drop the mysql.slave_relay_log_info table on the 5.7 slave. Log out of mysql, run mysql_upgrade -uroot -p -s to recreate the dropped table. Log back in to mysql instance then stop slave / start slave all should be good.

The problem was when you source dump.sql it somehow changed the table definition in 5.7 hence the error you were getting.

A simple test I tried was on a working 5.6 master to 5.7 slave repl, I dropped the slave_relay_log_info table on 5.7 and recreated it with 5.6's table structure and got the same error when I started slave replication.

mysql [localhost] {msandbox} ((none)) > use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql [localhost] {msandbox} (mysql) > CREATE TABLE `slave_relay_log_info` (
    ->   `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
    ->   `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
    ->   `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
    ->   `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
    ->   `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
    ->   `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
    ->   `Number_of_workers` int(10) unsigned NOT NULL,
    ->   `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
    ->   PRIMARY KEY (`Id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (mysql) > start slave;
ERROR 1805 (HY000): Column count of mysql.slave_relay_log_info is wrong. Expected 9, found 8. The table is probably corrupted

EDIT:
You can drop the table with the wrong structure, and recreate it with this:

CREATE TABLE `mysql`.`slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  `Channel_name` char(64) NOT NULL COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication',
  PRIMARY KEY (`Channel_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';


MySQL possibility to create global routines (stored procedures and/or functions)

There is no way to define stored procedures or stored functions (or events) that are global.

One approach is to create a shared common schema and then qualify the calls to the functions and procedures with the name of that schema (CALL shared.the_procedure();).

This is something I do with my collection of custom date/time calculation functions (e.g., SELECT date_time.next_quarter_start_after(NOW())), and with the ever-so-handy common_schema framework, which, of course, lives in `common_schema`.

If you take that approach, you have to remember then when a routine is running, the "current" database automatically changes, and the return value of the DATABASE() function returns the name of the schema under which the routine was defined, not your session's current database.  It changes back when the routine exits, so if used in a trigger, it doesn't break anything surrounding it but you don't have a way of knowing from inside the routine what the current database was, if you needed to know.


How to measure I/O contention after adding calculated fields on indexed views to a table in SQL Server?

What DMVs can I use that will give me an indication of I/O pressure by database object?

Probably the closest is sys.dm_db_index_operational_stats. This gives low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

Adding persisted computed columns typically has little impact, though they will add the usual extra costs if indexed. This is just the normal trade-off for adding an index though, and not specific to the underlying column being computed.

Using indexed views will tend to increase the chance of deadlocks, because indexed view maintenance has to be performed internally at the serializable isolation level (which typically acquires transaction-length key-range locks) to guarantee consistency when more than one table is referenced in the view.

Related question: How, when and why does implicit changing of transaction isolation level happen?

Indexed views can also increase the risk of incompatible locks if the view contains aggregation, because this has the effect of concentrating data ranges in the underlying tables into fewer rows in the indexed view.

There is always a trade-off when adding indexes and indexed views. In many cases, the deadlock risks can be minimized through careful design and attention to detail with respect to the execution plans generated for statements that modify the additional indexes.

My other piece of advice would be to look at investing in a commercial performance monitoring tool. It is possible to write your own based on DMVs, but it is not always easy, and much of the hard work (including presentation and historical reporting) has already been done in these products.


How can I Loop a column value if Row exists using INSERT ON KEY DUPLICATE UPDATE?

The simple expression which returns what you want is (NOT active).

So, your insert will become (you missed the UPDATE keyword):

INSERT INTO table
    (name, active)
VALUES
    ('max', 1)
ON DUPLICATE KEY UPDATE
    active = NOT active ;

If you want to have only one row per name, then you need to change the UNIQUE constraint to (name) only. With that change, the above insert will do exactly what you want:

Insert the row except if a row with that name already exists, in which case toggle the existing active value (essentially ignoring the 1 / 0 in the insert VALUES).


Restore database from backup file of different version / edition

From Developer to Enterprise will be fine, just be sure that if you are using processor licensing you have licenses on the target server to cover all of the CPUs. And it's not enough to just hide them from SQL, if they're physically connected to the machine, you're responsible for them.

Also when you go from a lower build to a higher build your database version will increase. There are a few scenarios where this can be problematic - e.g. if you are using 15,000 partition support on a specific build of 2008 it will not work when you upgrade to a specific build of 2008 R2. You may also be relying on optimizations (and have workarounds in place) that are actually bugs in an older build but are fixed in the new build, and this may lead to worse performance. It is also vital to review any trace flags in use at the source and determine if they should also be enabled at the destination. Never mind jobs, logins, etc.

Of course you cannot go backwards. I've never tried a minor downgrade like 10.0.5512 -> 10.0.5500 but it is definitely not possible to go down in service pack or version. So if you have a 2012 database on your Developer Edition instance and you want to put it on your 2008 instance in production, you'll have your work cut out for you (see here and here) - especially if you've used 2012 features.

But to cover other cases that might land people at this question (e.g. someone wants to go from Developer -> Standard or Enterprise -> Express or what have you)...

There are other edition -> edition upgrades that won't go so well, e.g. from Developer -> Express if you've used any features that aren't supported in Express (and same goes for any edition other than Enterprise really). Some examples of features you won't be able to use on down-level editions (in which case the restore will die at the point it tries to bring the database online):

Partitioning
Change Data Capture
Data Compression
Transparent Data Encryption

I don't know if there's a way to tell this directly from the .BAK file (I'm sure there's some magic that can be extracted from page headers somewhere, or if you've got a weekend to burn with a hex editor), but while the database is still intact on the source instance, you can always do the following to see if you're using any features that are available because of the SKU you're in:

SELECT feature_name FROM sys.dm_db_persisted_sku_features;

I'm not sure if SQL Server Audit should be on that list - the edition exclusivity of that feature has changed, so it probably depends on what you're doing with it. There are other things that you might be using but won't show up in the DMV (some because they are in your code, which the DMV doesn't parse, and some because your database is relying on external things such as SQL Server Agent, Service Broker, etc.):

mirroring
certain forms of replication
log shipping
database snapshots
online indexing
updateable distributed partitioned views
backup compression
policy-based management
plan guides
database mail
maintenance plans
full-text search

There are also cases where you won't be able to go from Developer to Express because of file size limitations (Express databases are limited to 10GB in total data file size).

Of course there may be other gotchas that you won't be warned about - they won't prevent the migration, but they might lead to very different performance on the target. Examples:

Different memory / CPU limitations on the target edition (or even the underlying operating system on the target). This bit a lot of people who went from 2008 R2 Enterprise to 2012 Enterprise (CAL), where the service is artificially limited to the first 20 cores). This can lead to straightforward performance differences (not enough memory to satisfy a query, for example, or much slower parallel query performance); more subtle ones include plan choices that are made because of the different underlying hardware.
Reliance on features such as indexed view matching on the source will not be automatically respected on the target without changing source code to use NOEXPAND. And you may not even be aware that this capability is why your queries suddenly slow down.
Same goes for parallel index operations and probably a slew of other optimizations that aren't coming to mind this moment (thankfully I work almost exclusively in the Enterprise space, so I don't have to worry about the limitations of lower editions in most cases).

UPDATE based on this duplicate:

There may be cases where you try to restore a database from a certain edition to a lesser edition (even on the same version), and you get errors that are less than helpful:

  RESTORE failed for server 'server\instance'.
  RESTORE could not start database 'databasename'.

This isn't very intuitive. However if you look deeper in SQL Server's event logs, you will see more useful errors (just one example):

  Database 'databasename' cannot be started because some of the database functionality is not available in the current edition of SQL Server.
  Database 'databasename' cannot be started in this edition of SQL Server because it contains a partition function '_dta_pf__9987'. Only Enterprise edition of SQL Server supports partition functions.

Now, that's not quite true - you can also restore to Evaluation Edition or Developer Edition, but that's beside the point. In order to restore this database, you basically have two options:

Restore to an appropriate edition of SQL Server - which will mean locating or installing a new instance.
Restore the backup on the source server as a new database with a different name, remove any and all Enterprise features, then backup the database again, and restore that on the lesser edition. (In this specific case, I left the name of the partition function in the error message, because this seems like a discardable thing anyway - it was created by the Database Engine Tuning Advisor and may have been done so by someone who didn't quite know what they were doing. This isn't always the case.)

A variation on (2) would be to just remove the partitioning and other features on the source database, and take another backup. But if it ain't broke...


what oracle data dictionary view can be used to decribe types defined in a plsql package?

If all of the types uses are used explicitly, meaning now using execute immediate, then you can look at dba_dependencies to see what types a PL/SQL stored procedure uses.


Help with adding a time filter to Postgres query

posted_at should certainly be data type timestamptz, not varchar.
(And id should be int or bigint.)

Assuming timestamptz for posted_at and leaving further performance optimization aside, you could make it work range types and the range overlap operator &&:

SELECT (a.status, b.status)
FROM   FB_status a
JOIN   FB_status b ON ST_Dwithin (a.geom, b.geom, 0.01)
WHERE  similarity (a.status, b.status) > 0.5
AND   tstzrange(a.posted_at, a.posted_at + interval '10 min')
   && tstzrange(b.posted_at, b.posted_at + interval '10 min');

One might argue it should really be:

   tstzrange(a.posted_at - interval '5 min', a.posted_at + interval '5 min')
&& tstzrange(b.posted_at - interval '5 min', b.posted_at + interval '5 min')

But the result is the same for a self-join.

In reality, you probably need to optimize performance with nearest-neighbor queries on top of (functional) GiST indexes using LATERAL joins ...

I would consider operating with a spacial or temporal raster (granulation) instead to largely simplify things. Using generate_series() and / or width_bucket().


Locating a value implies traversal from the upper level point anchor (which explains the 6es). IS locks are required for traversal under read-commited isolation. This is neither "excessive" nor unexpected. If I'd venture a guess I would speculate that path_id is a low selectivity value and the index is not helping much.

You complain about deadlocks, I suggest you investigate them accordingly, starting with the deadlock graph.

As a side note, excessive locking (when truly a concern) can be alleviated using row versioning based isolation levels. If you're interested in low level details on how B-Tree seek actually works, start with Transaction Processing: Concepts and Techniques

To detail a bit: an inner page will contain a key and a pointer to a page/slot. This means that 'the page starting at the pointer contains values smaller than this key value'. Seek will search the upper page until it finds the first key bigger than the value seek-ed. It jumps to the pointer and continues from there, recursively. If the upper page says 'from page 148 all keys are smaller than 8' and the page 148 contains 6es, seek will  lock page with 6es. There is no magic pixie dust here. Locking lots of 6es is a clear hint that the key has very low selectivity: there are many rows that repeated a small range of key values.


How can I tune MySQL to behave better under load

If you stay with MySQL 5.1
Since you cannot upgrade at this time, you need to install the InnoDB Plugin. It was available since MySQL 5.1.38 (See the Release Notes). I wrote a post about it : MySQL - Installing InnoDB Plugin
Surprising fact: Percona already ahd the InnoDB in Percona Server 5.0.45 long before
Once you do, you need to set the following

Set innodb_read_io_threads
Set innodb_write_io_threads
Set innodb_thread_concurrency to 0

You can increase the innodb_buffer_pool_size, but do not exceed half the installed RAM. Set it to 20G.
When you upgrade to MySQL 5.6
#1: Tune Read and Write I/O
InnoDB has new options to engage multiple CPUs. I wrote about this over the past 3 years

Jun 01, 2012 : I've got 16GB of ram, how should I configure MySQL Server?
May 07, 2012 : MySQL Server Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

#2: Partition the InnoDB Buffer Pool
Since MySQL 5.5, you can partition the InnoDB Buffer Pool to reduce I/O thread contention and swapping

Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?

#3: You can snapshot the InnoDB Buffer Pool to Disk
You can use the following

innodb_buffer_pool_dump_at_shutdown : MySQL dumps the map of the Buffer Pool on Shutdown
innodb_buffer_pool_load_at_startup : MySQL load the Buffer Pool on Startup
Setup a script to run SET GLOBAL innodb_buffer_pool_dump_now= 1; in a crontab job to dump the Buffer Pool map every 5 minutes or so. See innodb_buffer_pool_dump_now

There is so much more I could write, but I'll stop here.
CAVEAT : Why is this tuning necessary ?
See my post Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
I have mentioned the Plugin for InnoDB Before : Performance settings for MyISAM tables - keep everything in memory
GIVE IT A TRY !!!


Auto_increment value needs to be set as S1

Autoincrement values can only be numeric (integer and floats types if I remember correctly).

If you want to store S1, ...., S1000, just store an integer, and return the extra S on select or on client side:

create table tbl(ID bigint not null primary key auto_increment,name varchar(100)) auto_increment=1;

insert into tbl (name) values ('a');
...

select concat('S', id) as id, name from tbl;
+----+------+
| id | name |
+----+------+
| S1 | a    |
| S2 | a    |
| S3 | a    |
| S4 | a    |
...


MYSQL - Run a query based on the results of a query

Prepared Statement is the answer for this.

set @stmt1= concat( 'select ', @var, ' from ', @vardb, ' where ', @varcond);
prepare stmt from @stmt1;
execute stmt;

For getting @tbl used properly,

   concat( 'select max(length(', @tbl, ')' )

will get it. But to select required columns from information_schema, you need to run a query first followed by coining this prepared statement and then execute the same.


Mysql HY000 the table table_name is full when adding new column

There is no maximum in the file_path command you provided that limits the size of the ibdata1 file. Perhaps you don't have enough disk space left. It would be a good idea to add more disk space to the current volume or clean up first as mysql needs that volume.

If you're worried about the ibdata files filling up disk space, you can always add another ibdata file and place that file anywhere that has sufficient disk space. You would have to set permissions so that the mysql user could access the directory where you place the second file.

Example:

innodb_data_file_path = ibdata1:12M:autoextend

becomes

innodb_data_file_path = /ibdata/ibdata1:(NN)M;/disk2/some_directory/ibdata2:128M:autoextend

Where (NN)M is the current size of the ibdata1 file.

The second file will start out with a fixed size of 128M and will autoextend.

Here is the process

Increasing InnoDB table space

Shut down the MySQL server.

If the previous last data file is defined with the keyword autoextend, change its definition to use a fixed size, based on how large it has actually grown. Check the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify this rounded size explicitly in innodb_data_file_path.

Add a new data file to the end of innodb_data_file_path, optionally making that file auto-extending. Only the last data file in the innodb_data_file_path can be specified as auto-extending.

Set permissions for mysql.

chown mysql:mysql -R /disk2/some_directory/ibdata2

Start the MySQL server again.

The other thing to keep in mind with ibdata files is the fact that they can't be easily shrunk. If you are not using innodb_file_per_table, then ibdata will just grow and grow and even if you delete database objects. If you have deleted databases and are sure that your ibdata file will remain small, you can always recreate that file and the iblogs to give disk space back to the OS. Rolando wrote a post that will help you out with that.


Recommended procedure to remove old files after major version PostgreSQL database cluster upgrade

One thing to be very careful about here is whether you’ve done a pg_dump+pg_restore or a pg_upgrade.

It sounds like you’ve done a pg_dump+pg_restore based on the docs section you’ve linked to.

In that case it should be safe to completely remove the old data directory, including table spaces, as you have a full copy of the data in the data directory of the new cluster.

If you have used pg_upgrade that is not safe to do (neither are the two methods you’ve described), because some of the files are hard-linked from the old to the new cluster.

Edit: As pointed out in a comment, the latter is only true if you have used the “--link” option for pg_upgrade.


Replace , inside trigger code, the table Shipment with Inserted;

CREATE TRIGGER [dbo].[qytcontrolcheck]
   ON  [dbo].[Shipment]
   AFTER  INSERT,UPDATE
   AS
BEGIN
            if exists (SELECT        Shipment.Qty, Supplier.Status
                    FROM   Inserted as Shipment
                            INNER JOIN   Supplier ON Shipment.SID = Supplier.SID
                    WHERE        (Shipment.Qty >= 10000) AND (Supplier.Status <= 30) )
                                    BEGIN
                RAISERROR('NOT ENOUGH STATUS TO REGISTER THIS TRANSACTION',16,1);
                rollback transaction;
            END
END

dbfiddle here


SQL Server - Linked Server error - Could not connect to server XYZ because

The linked server was also used for replication.
The order of Subscribe and Create linked server was the issue.

Reproduce the error:
1.  Drop the subscription
2.  Drop the linked server
3.  Configure the Subscription first
4.  Linked Server next
5.  Testing
- a.    Select will return results
- b.    Exec proc result in error

Fix:
1.  Drop the subscription
2.  Drop the Linked Server
3.  Create the Linked Server first
4.  Configure the Subscription next
5.  Testing
- a.    Select will return results
- b.    Exec proc will succeed

Also, executing the following on Linked Server worked as well.

--Run this statement on LNK_SRVR
exec sp_AddRemoteLogin 'CurrServer','sa'


Does a record with NULL data does not eat any storage on the harddrive?

You haven't mentioned your RDBMS system. You should always do this when asking any question.

Secondly, this would be easy to test. I'm sure that NULLs will add storage - maybe your question is about "How much storage exactly?"?

Why not add 10,000 records of your structure with full data, then with NULL data and compare the size on disk. I'm fairly sure that the exact number will vary by RDBMS. There will be some overhead associated with fields even if empty.

There are many reasons why managers decide to do things - one of them is a lack of understanding of the technology. One solution might be to have another table "hanging" off your main table - only those records for which extra data is available will be in your subsidiary table?

To answer your last comment

"i have also looking at another solution, like having a table with a
few columns, with each row is storing one property key with its value on
the next column. But this ended up pushes the development back too far
because of the complexity in data design and communication."

This sounds like you are considering an EAV-type design - NOT a good idea - see here and links from that post.


Replacing too many tables with too many columns?

Solution 3 is the best solution provided that you move the deviceId from the variables table into the data table.

The reason why it is better is that when you need to add a variable to one device you can do this without changing your devices and data tables in solution 2. If you want to query the data in the solution 2 way then you can create views that look like the devices and data tables in solution 2.


Create a table based on a table depending on the values in the initial table column

Well this may solve your problem.

SQL> desc tbl1
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                         NUMBER
 NAME                           VARCHAR2(20)
 ADDRESS                        VARCHAR2(20)

SQL> select * from tbl1;

    ID NAME         ADDRESS
---------- -------------------- --------------------
     1 Ivan         Moscow,Moscow
     2 Peter        Samara,Samara
     3 Grigor       Moscow,Korolev
     4 John         Moscow,Moscow

SQL> create or replace view vtest as
select id, name, substr(address,1,instr(address,',')-1) address from tbl1
union
select id, name, substr(address,instr(address,',')+1, length(address)) address from tbl1;

View created.

SQL> select * from vtest;

    ID NAME         ADDRESS
---------- -------------------- --------------------
     1 Ivan         Moscow
     2 Peter        Samara
     3 Grigor       Korolev
     3 Grigor       Moscow
     4 John         Moscow

SQL>


How can I optimize a query where a IN clause is replaced with multiples UNION ALL?

if slow_view don't have too many records, you can consider fetching all the records to application and apply the filter in the application side result set.
This approach helped me in a specific case to reduce the database load.


T-SQL Query to calculate Settele and withdraw amount in MSSQL- SERVER

Avoid <> in where condition.Instead use >-1
When you are already filtering anything like  where s.cfcifno<>-1  ,then no need of putting same in case condition because that condition is already filtered.

Your query seem ok to me,

SELECT
          S.OPRDATE ,
          S.CFCIFNO ,
          SUM(CASE WHEN S.SATNAPAYATYPE=1 THEN S.AMOUNT ELSE 0 END) AS SATNA_DEPOSIT_AMNT,
          SUM(CASE WHEN S.SATNAPAYATYPE=2 AND
                        S.OPRCOD=0
                        THEN S.AMOUNT ELSE 0 END) AS SATNA_WITHDRAW_AMNT,

          COUNT(CASE WHEN S.SATNAPAYATYPE=1 THEN 1 ELSE NULL END) AS SATNA_DEPOSIT_COUNT,
          COUNT(CASE WHEN S.SATNAPAYATYPE=2 AND
                          S.OPRCOD=0
                          THEN 1 ELSE NULL END)   AS SATNA_WITHDRAW_COUNT
   from factsatnaevent s
   where s.cfcifno>-1
   GROUP BY S.OPRDATE ,
            S.CFCIFNO ;

Are you facing any performance issue ?


Best use of RAM for multiple SQL instances?

You can allocate maximum amounts of memory that each SQL Server instance can use through the sp_configure stored procedure.  However, that won't be an absolute maximum, but a maximum amount of memory allocated to the storage of certain types of data, mainly data cached from the database.

If you're planning on running one instance of each version of SQL Server since 2008, you're looking at five instances of SQL Server on a 32MB server; you'll need to leave a few GB RAM available for the operating system and anything else on that server (SSIS?  SSRS?  SSAS?).  At a conservative estimate, you'll probably only have about 4GB RAM available for each instance, so you would run something like:

exec sp_configure 'show advanced options', 1;  -- ensure memory configuration option is available
GO
RECONFIGURE;
GO
exec sp_configure 'max server memory', 4096; -- maximum memory in MB
GO
RECONFIGURE;
GO

Note that difference versions of SQL Server interpret the configuration setting in different ways - more recent versions apply the setting to more types of memory allocation.  There's a table about half way down this sizing consideration document that shows what's changed.


How to copy user rights and roles from one user to another

may be helpful.

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','username') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','username') FROM DUAL;

set long 9999999
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','username') FROM DUAL;


Just restore the last full backup, the most recent Differential and then the transaction logs after the Diff, up to the point in time you want to restore. And Igor is right.


How can I fix "Login failed for user sa" error in SQL Server?

In SSMS, right-click on the server and go to Properties and under the security page, check what Server authentication looks like. Odds are it's set to Windows Authentication only and needs to change if you want to use SQL Server users:


If there is room for more drives, order more drives. If there isn't room for more drives, order more drives... and an external enclosure.

I'm assuming this data has at least some value to the business and from your comment we can also infer that you're experiencing performance issues already. So, zero redundancy and single spindle performance isn't going to cut it.

If you absolutely have no other choice than to stick with these 5 drives, I'd probably go for a 4 disk RAID10 with a hot spare. Possibly, maybe, all 5 in RAID5. Separating tempdb and indexes at this sort of scale is pointless.


Sybase ASE retrieves port numbers for the listeners from the interfaces file. On *nix systems, this is typically in the Sybase Home directory ($SYBASE/interfaces)

On Windows systems it's called interfaces.ini and can be found in %SYBASE%, which is likely to be c:\sybase (I think..been a while since I've run on Windows).

I believe you can launch the GUI interfaces editor through Sybase Central, if you are using that to manage your ASE databases.  dsedit and dscp are the two utilities that can be used to edit the interfaces file.

You can also manually edit the interfaces file using a text editor such as vi but be sure to pay attention to the format of the entries and permissions, so as not to accidentally muck it up.


Can the SQL Server optimizer use indexes to update auto created statistics?

TL;DR

Auto created statistics will be improved by an index if you are using fullscan updates & have the column as the first key column in an index.

(Default) sample rate statistic updates will use the clustered index / heap table scan even when a NCI exists with the column as the first key column.

Even when updating the statistic of the NCI.

As such, updating statistics with a sample rate will not be positively impacted by adding an index.

Setup

Creating a table with a primary key & 10M rows:

CREATE TABLE dbo.testing(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, val nchar(500));

INSERT INTO dbo.testing  WITH(TABLOCK) (val)
SELECT top(10000000) REPLICATE('b',((ROW_NUMBER()OVER (ORDER BY (SELECT NULL))) % 400))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
CROSS APPLY master..spt_values spt3;

A nchar(500) value is used to show a clear difference between statistic updates.

We can create an auto created statistic on the val column with this query:

SELECT val
FROM dbo.testing
where val = 'bla'
order by val;

Full scan stat updates

Updating the statistic with fullscan:

SET STATISTICS IO, TIME ON;
UPDATE STATISTICS dbo.testing([_WA_Sys_00000002_4F5F3681]) WITH FULLSCAN;

Results in 127 seconds of cpu tme on my machine:

CPU time = 127141 ms,  elapsed time = 80147 ms.

Now when we look at the query that the stat updates actually runs:

SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [val] AS [SC0] FROM [dbo].[testing] WITH (READUNCOMMITTED)  ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 2);

We can see that it went parallel on the two cores on my machine & did order by the val column.

As a side note, there was also a memory grant & tempdb was used to support the ordering, The memory grant was as high as 12484 MB or ~ 12 GB

Let's see what happens when we create this index:

CREATE INDEX IX_val
on dbo.testing(val);

And re-run the stat update:

SET STATISTICS IO, TIME ON;
UPDATE STATISTICS dbo.testing([_WA_Sys_00000002_4F5F3681]) WITH FULLSCAN;

This results in an execution time of

 CPU time = 10578 ms,  elapsed time = 10882 ms.

And the memory grant & tempdb usage is gone since it does not have to sort anymore.

The statistic update with fullscan on the auto created stat is able to use the index.

This would also work for an index like this:

CREATE INDEX IX_val_val2
on dbo.testing(val,val2);

But not when creating an index like this:

CREATE INDEX IX_val2_val
on dbo.testing(val2,val);

Sample rates

If we change the update stats to sample 10 percent instead of doing a fullscan:

UPDATE STATISTICS dbo.testing([_WA_Sys_00000002_4F5F3681]) WITH SAMPLE 10 PERCENT

Without an index this amounts to

CPU time = 10500 ms,  elapsed time = 11100 ms.

With an index this amounts to

CPU time = 10312 ms,  elapsed time = 11038 ms.

Auto created statistics with sample rates are not positively impacted by indexes on the column where the column is the first key column.
Why this happens is explained further below

Comparison with updating the index' stats

As expected, updating the statistic on the index takes the same amount of time as the auto created statistics.

UPDATE STATISTICS dbo.testing(IX_val) WITH SAMPLE 10 PERCENT

CPU time = 11203 ms,  elapsed time = 11849 ms.

UPDATE STATISTICS dbo.testing(IX_val) WITH FULLSCAN;

CPU time = 10938 ms,  elapsed time = 11005 ms.

Sampled statistic update on an indexed column stat

The query used when updating statistics with a sample rate of 10 percent is different:

SELECT StatMan([SC0], [SC1], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], step_direction([SC0]) over (order by NULL) AS [SB0000]
FROM (SELECT [val] AS [SC0], [ID] AS [SC1]
FROM [dbo].[testing] TABLESAMPLE SYSTEM (9.999986e+000 PERCENT) WITH (READUNCOMMITTED) )
AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SC1], [SB0000] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 1)

TABLESAMPLE will give us random pages and it's explanation can be found here.

The statistic orders by val,id,step_direction([SC0]) over (order by NULL)

We can trace the query plan of this StatMan query with the profiler & the Showplan XML Statistics Profile event:

UPDATE STATISTICS dbo.testing(IX_val) WITH SAMPLE 10 PERCENT

The execution plan shows the clustered index being used, instead of the expected nonclustered index:

I cannot share the query plan with paste the plan unfortunately, it is malformed.

The same is true for heap tables. The usage of the NCI would not make a difference here, the TABLESAMPLE SYSTEM (9.999986e+000 PERCENT) retrieves 10 percent of the data pages in a random order. This is why the CI was used.


A PRIMARY KEY does not allow NULLs.  But a UNIQUE KEY does.  I suggest you try a simple table that has A and B, each nullable, then INSERT suitable combinations to see if they work.

UNIQUE(A,B) says that the pair is unique, while allowing either column to be repeated.  Your Question is vague--is that what you wanted?

This will not check for "can't both be null"; that would have to be tested for at the application level.

Based on your Comment, you seem to be asking about the WHERE clause of a SELECT, not the table definition.  And it seems like neither column should allow NULLs:

CREATE TABLE ... (
    vat ... NOT NULL,
    ssn ... NOT NULL,
    ...
    PRIMARY KEY(vat),
    UNIQUE(ssn)
)

(or swap the two keys)

Then the application should decide which of these to use:

SELECT ... WHERE vat = ?
SELECT ... WHERE ssn = ?
SELECT ... WHERE vat = ? AND ssn = ?

depending on what is given in the <form>.


How to -create- performance issues? - oracle

This is a pretty broad topic that would largely depend on your specific needs.

Here is a link to a fairly recent article about load testing tools.   It provides pros and cons of several different options:

http://www.pythian.com/blog/oracle-database-load-testing-tools-overview/

Personally, I like the Silly Little Oracle Benchmark (SLOB).  http://kevinclosson.wordpress.com/2013/05/02/slob-2-a-significant-update-links-are-here/


Why cp1256 collation is not 1 byte in MySQL?

That's a limit of the InnoDB engine. It cannot index more than 767 bytes (for a single column)

See the manual:

http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

  By default, an index key for a single-column index can be up to 767 bytes
  ...
  When you attempt to specify an index prefix length longer than allowed, the length is silently reduced to the maximum length


Running MongoDB and SQL Server on the same host- memory pressure

Set the min server memory option for SQL Server.  It will still release pages under memory pressure, unless you also allow SQL Server to lock pages in memory. The combination of setting min server memory and locking pages in memory should resolve the problem.

I've never used MongoDB, so I don't know if you can manage memory usage.  A quick search makes it seem you can't.

Assuming you're running a 64-bit server, and have more than 4 GB of RAM, you could run a 32-bit MongoDB instance, which will limit the memory to 2, 3, or 4GB depending on it's architecture.  Does a 32-bit MongoDB even exist?


Why is the optimizer doing a table scan vs Index Seek

The optimizer is convinced that if it's going to have to go back to the disk for retrieving column data anyway, it might as well scan the table in the first place, since that'll be less work for it to do.  It'll use the seek with the CHAR( 7 ) scalar because the statistics for the index know it's not going to find anything, but if data needs to be returned, it has to consider both CPU and I/O weights.

USE tempdb;
GO

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'tst'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.tst;
    CREATE TABLE [dbo].[tst]
    (
        Mon                     [char](6) NULL,
        COL1                    [varchar](50) NULL,
        COL2                    [varchar](50) NULL,
        COL3                    [varchar](50) NULL,
        COL4                    [varchar](50) NULL,
        COL5                    [varchar](50) NULL
    );

    INSERT INTO dbo.tst ( [Mon] )
    SELECT  TOP 100000000
            CONVERT( CHAR( 6 ), DATEADD( DAY, ( ABS( CHECKSUM( NEWID() ) ) % 10000 + 1 ),
                '20000101' ), 112 )
    FROM    sys.all_objects so
    CROSS APPLY sys.all_objects sp;

    CREATE NONCLUSTERED INDEX IX__tst__Mon
        ON dbo.tst ( Mon )
    WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 100 );
END;

SELECT  Mon, COUNT( 1 )
FROM    dbo.tst
GROUP BY Mon
ORDER BY Mon;

SET STATISTICS IO, TIME ON;

SELECT  Mon, COL1
FROM    dbo.tst
WHERE   Mon = '201509'

SELECT  Mon, COL1
FROM    dbo.tst WITH ( INDEX = IX__tst__Mon )
WHERE   Mon = '201509'

SELECT  *
FROM    dbo.tst
WHERE   Mon = '201509'

SELECT  *
FROM    dbo.tst WITH ( INDEX = IX__tst__Mon )
WHERE   Mon = '201509'

SET STATISTICS IO, TIME OFF;

Specifying the hint, in both cases, does reduce the time required for the query to resolve, but the index seek + RID lookup actually results in a significant increase in the number of reads necessary ( my test indicated a 60% increase ).  Obviously it's not a 1:1 trade off, since the time difference is about 6x, but regardless, the optimizer is choosing the scan instead.

If you can INCLUDE the columns you need in the index, you'll get the best of both worlds, eliminating the RID lookup and the additional reads.

--DROP INDEX dbo.tst.IX__tst__Mon
CREATE NONCLUSTERED INDEX IX__tst__Mon
    ON dbo.tst ( Mon )
INCLUDE ( COL1, COL2, COL3, COL4, COL5 )
WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 100 );

SET STATISTICS IO, TIME ON;

SELECT  *
FROM    dbo.tst
WHERE   Mon = '201509'

SET STATISTICS IO, TIME OFF;


Is there a tool or library to extract data from Mysql database files without having the database engine available?

If you are brave and a little fool-hearty, try this crazy idea:

Since myisamchk can read, write, and analyze MyISAM tables independent of mysqld, get a hold of the source code for myisamchk, take a look around and see if you can borrow any C/C++ code, algorithms, structures, classes, or just any ideas from it.


List parent then nested child items in a single table

As far as you want to obtain all records where parent_id is null, I'd suggest to use an inline udf function.

-- Inline UDF function
CREATE FUNCTION fnTree(@ID int)
RETURNS table AS
RETURN
(
    WITH tree AS
    (
      SELECT c1.id, c1.parent_id, c1.name, [level] = 1, path = cast(c1.id as varchar(100))
      FROM dbo.[software] c1
      WHERE c1.id = @ID
      UNION ALL
      SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1, Path=Cast(tree.path+'/'+cast(c2.id as varchar(10)) as varchar(100))
      FROM dbo.[software] c2 INNER JOIN tree ON tree.id = c2.parent_id
    )
    SELECT tree.path, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name as name
    FROM tree
);
GO

Then you can CROSS APPLY this function with all records where parent_id is null
Take care when using a recursive function, you should set OPTION (MAXRECURSION XXX) just to ensure it can hold all returned records.

SELECT      f.id, f.name, f.parent_id, f.path
FROM        software
CROSS APPLY fnTree(software.id) f
WHERE       software.parent_id IS NULL
ORDER BY    f.path
OPTION (MAXRECURSION 0);
GO

id | name                  | parent_id | path
-: | :-------------------- | --------: | :----
 1 | Main Product          |      null | 1
 3 |   Addon Product       |         1 | 1/3
 5 |     Sub-Addon Product |         3 | 1/3/5
 4 |   Addon Product       |         1 | 1/4
 2 | Next Main Product     |      null | 2

dbfiddle here


Setting the SMTP_OUT_PARAMETER in 10G at runtime

Looking at the docs, you cannot selectively choose from multiple SMTP_OUT_SERVER values. Here's how that parameter works:
http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams230.htm#REFRN10241

SMTP_OUT_SERVER specifies the SMTP host and port to which UTL_MAIL delivers out-bound E-mail. Multiple servers may be specified, separated by commas.
If the first server in the list is unavailable, then UTL_MAIL tries the second server, and so on.

It looks like you will need to manage this requirement externally.


Lock wait timeout exceeded on insert despite low lock_time in the slow query log

The query times are wall time from start to finish.  The lock times are times spent actually locking affected tables.  If you run

select sleep(60);

You'll see a slow query entry with a query time of 60 seconds and no lock time.

50 seconds is a standard lock wait time out setting. Run

show variables like 'innodb_lock_wait_timeout';

I'll bet it's 50.  The queries in your OP are likely showing a query time of this b/c of the time out setting.  They're probably sitting there not really doing anything waiting for some other query to finish until they're effectively killed.  If they're hitting that limit it should be easy to just run show processlist; to see what's going on.  You'll likely see many threads in a Locked state (i.e. doing nothing but waiting) where there's one thread "doing something".

Are your truncated "insert" statement ultimately doing some kind of expensive subselect from other tables?

Is your application explicitly locking tables and not letting go?

Edit: Queries hitting a lock wait timeout does not mean they are holding on to a lock for that limit but rather they got tired of waiting for someone else to give it up and let them have a go.

show engine innodb status\G

Can also give you insight to what is locked at the moment and who is holding them.  Although the output can be a bit cryptic at first glance it can give some pointers.


Revision Tracking & Source Control for Azure SQL Data Warehouse

Microsoft has started work on this and there is a signup page for a preview version. In the meantime you may want to look at DBUp and FiveTran, although I haven't worked with either of these.

Update: As of Aug 2019, it is publicly available in preview.
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-install-visual-studio

You'll want to vote for this thread:

https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/13313247--in-preview-database-project-from-visual-studio-t


Recommendations for my.cnf on MySQL Replicant

There are no hard-and-fast rules for such Slaves. Let's look at two scenarios

SCENARIO #1

Some standardize all DB Servers to have the same configuration. The rationale behind this is promote a Slave to a Master should have to fail over to it.

SCENARIO #2

A scaled down Slave being used as a data backup. No reads from that Slave.

COMPARISON OF SLAVES

For a Slave whose sole job was backup and simple data redundancy, using less optimization is fine but can come at a premium. How so ?

If you have Master that has high write volume, this can result in MySQL Replication having lots of Replication Lag (Seconds_Behind_Master > 0 and increasing). This can occur when a Master has 20 INSERTs, UPDATEs, and DELETEs happening in parallel. Those 20 commands are serialized in the Master's binary logs and are serially processed by the SQL thread on the Slave. If you scale down the hardware and MySQL config settings on the Slave too much, that Replication Log can get worse than the aforementioned serialization of updates.

CONCLUSION

The volume of writes on the Master should be inversely proportional to scale down of the Slave:

the higher the volume of writes on the Master

the less the scale down of the Slave
the lower the tolerance for Replication Lag (preferably none)

the lower the volume of writes on the Master

the more the scale down of the Slave
the higher the tolerance for Replication Lag (expected to be infrequent)

CAVEAT

In this context, scale down means

less RAM required
less need for CPU/cores
lower cache settings

innodb_buffer_pool_size
innodb_log_file_size (default is OK)
innodb_log_buffer_size (default is OK)
key_buffer_size

lower max_connections

UPDATE 2015-02-02 12:53 EST

Based on the question in your comment about writes, you can increase the following on the Slave

innodb_write_io_threads to tweak the CPU for writes
innodb_log_buffer_size to cache writes


Performance problem using mysql fulltext search

Solved it by using MariaDB 10.2

MariaDB reduced the query execution time from 300 seconds to 30 seconds.

I then profiled and optimized the query down to 0.156sec.


insufficient privileges while executing oracle stored procedure?

The pl/sql execution context does not include Role. So your risght have been delegated by a role. The Pl/SQL doesn't see it.
So, first, you can try to give the right to alter the user directly not via a role.
2nd, i would try this option on the package definition (auhtid) which is explained here


Periodically update a table with new records from another server

Create a linked server connection with read access from Server B to Server A.  Have a process running on Server B that does the following:

Examine the table on ServerB to identity the last inserted record from Server A.  Hopefully you have an auto-incrementing primary key (int).  If not, look for the greatest "last updated timestamp".
Connect to Server A and retrieve all records from Server A that are greater than this primary key / timestamp
Insert these new records into Server B.

I always recommend full error trapping and process logging in any sort of automated system like this.  You need to know how it is behaving, and you need to be alerted if there are any problems.

I would put all the logic in a stored procedure, connect from A to B using a linked server, and have the procedure sleep for X seconds after it is finished processing.

Put the stored procedure in a SQL Server job that is set to run continuously.  It will appear to always be running, but the stored procedure will be sleeping most of the time.

Note: The reason I create jobs to run continuously and then having the procedure sleep (using a wait statement) is to prevent clutter in the job history logs.  I actually create the job to run on a schedule (every minute) but because the stored procedure sleeps, the job never finishes.  You can chalk it up to personal preference.


Is it possible to install SQL Server 2014 Enterprise on Windows 7?

Yes, in spite of what that document says, you can install SQL Server 2014 Enterprise Edition on Windows 7. I just did it, and there were no warnings, errors, or blockers of any kind:

  click to enlarge

Now, whether it's supported or not is a different issue (you really should contact your licensing rep), as is whether it makes any sense whatsoever to install Enterprise Edition on a workstation when Developer Edition (which you can typically find for $59 at retail) behaves the exact same way - literally the only differences are (a) what @@VERSION returns and (b) what the EULA states about terms of use.

Note: Developer Edition is now free in SQL Server 2014, you just have to sign up for Visual Studio Dev Essentials (I blogged about this here). SQL Server 2016 Developer Edition will also be free once it's released.

If the issue is that this workstation is going to be in production use, why is it a workstation? You're going to spend thousands and thousands of dollars on SQL Server license, then save a few bucks on a proper server, or spend even less on a workstation operating system? Tread lightly here, because if this configuration isn't supported (or you're using MSDN or grandfathered server/CAL Enterprise licenses and that isn't valid), an audit is going to be very consequential. Please make sure you discuss this with your company's bean-counters and the people who will fire you if this goes south.