Complete Failover Cluster Failure SQL Server 2012 We have experienced a full cluster failure on our 3 node cluster running SQL Server 2008 R2 with 7 SQL Server instances. It started when we rebooted one of the nodes and it returned an error:

  Node '%1' failed to establish a communication session while joining the cluster. The was due to an authorization failure. Please verify that the nodes are running compatible versions of the cluster service software.

The other two nodes were still online. Running a cluster validation test passed. After consulting with Microsoft we decided to take down all 3 nodes, bring one node online with force quorum option (so the cluster would come up on a single node), then bring the other nodes online one at a time.  Unfortunately, after doing this, we were left with a single node forced online, because neither of the other nodes would join. The same error above was being reported on both the other nodes.

No resolution has been found for this after 2 weeks of investigation so we were presented with two options:

Rebuild the cluster
Evict the nodes and try to get them rejoined

My question is, this cluster has 7 SQL Server instances on it, and one File Server service. If the cluster is completely rebuilt, or a node is evicted, what are the steps to get SQL Server back working? Do we have to completely reinstall SQL Server or dDo we just have to add the instances back into the new cluster using Failover Cluster Manager without lengthy software installs?

If you evict nodes you'll end up formatting the nodes and rebuilding them.  Once a node has been evicted from the cluster you won't be able to uninstall the SQL Server instance from the node.  You can try manually removing all the registry keys and files, but honestly it's probably faster to reinstall Windows and try getting it back into the cluster.

Has Microsoft not been able to get you towards any resolution on this?

Either way you're looking at a cluster rebuild basically from scratch here with 21 SQL installs, which I'm sure as you know takes forever.  There are some ways to make this take less take (building a new two node cluster, installing everything there, then doing a controlled migration between clusters, use DNS to redirect connections to the new names so applications don't need to change, etc.

If this was my environment I'd burn it down and bring it back fresh, obviously with as little downtime as possible.

--sales pitch--
It might be a good idea to bring someone in that has a lot of clustering experience (me, someone else, either way) to help with the rebuild to make sure that everything is setup exactly as it should be and to help take the pressure off your internal team (who probably hasn't been getting a lot of sleep the last two weeks).  If you've got a lot of clustering experience in house then this isn't needed of course.  I'm just worried because something went horribly wrong doing something which souldn't have been that big of a deal to do.
--end sales pitch--

Disclaimer: I'm a consultant.


Sql server, altering column to smaller size I'm doing a project that's adding some columns to a fairly badly designed table, and I noticed that it's got a lot of wasted space.

I see a lot of posts about adding columns on the end being relatively quick and cheap (for reasons I don't quite get - the follow-on links were broken), and I see a lot of posts about how expensive it is to grow columns, and how it essentially boils down to "make a new table and copy all the old data into it" (in fact ssms does that with Generate Script no matter what change you make).

I'm curious about a more niche concern I guess - altering columns to smaller data sizes and how to do that efficiently.

Specifically, this table has multiple datetime columns that really only want the date.  In fact the sql is doing all the date arithmetic to strip time off of GETDATE().  I want to

ALTER TABLE Foo ALTER COLUMN BAR DATE NULL

But I don't want to incur all the expense of creating a temp table and re-writing the old data (if I can avoid it).

Seems like all the old data in place would be fine, just smaller.

And I'm hoping that freeing up that space in the block would make the new requirements I have to add less onerous (but I obviously don't get the deep internals of row allocation).

So what about going to smaller fixed-size types with an ALTER statement?  Will that just be okay and not blow up the log?

Thanks

Changing from DATETIME to DATE changes the binary representation of the value, so the overhead will be similar to the one when increasing in size.

IIRC the only change that can be metadata only is one that doesn't change the binary representation, like changing from VARCHAR(100) to VARCHAR(50), and some may not even require scanning the data to make sure it fits, such as changing from VARCHAR(50) to VARCHAR(100).

Any change in data type that is not metadata only, will require physical update of the rows, and will cause a similar overhead as the entire row needs to be logged.


SQL Server - Export large table without primary key I need to sync a large table ~500 millions rows without a primary key between SQL Server and MySQL. The table has only a clustered composite non-unique index.

I do have a ODBC connection between the servers, but an import of ~8 million rows took around 45 minutes, so I believe a larger single import would be unreasonable as interruptions may occur at any point. I can't change the existing table structure, I can add other tables. After further reading, offset / fetch is not an option for large tables. "Select ... where x between ... and ... " is not an option as I don't have an unique key.

How can I export the table in batches that are guaranteed to contain all rows?
My problem is that since the clustered key is not unique, ordering after it would not guarantee the physical rows have the same order between consecutive queries and ordering after all columns would take too long. And how would you recommend to migrate the batches, through ODBC or CSV files?

Assuming you don't have updates or deletes against source table you can try the following :
1. Make a copy of existing table using CTAS syntax (for SQLServer it's SELECT * into source_table_copy FROM source_table). Such operation is very fast even for huge tables.
2. Add after insert trigger on source_table that copies new record[s] to source_table_copy.
3. Now when all new records in source_table go to source_table_copy as well, and you can move data from copied table to Mysql in batches.
For instance, if you have a link between 2 servers, everything can be done within the body of TSQL stored procedure.
E.g. a piece of code that move up to 20 records to new server might look like

 --declare table variable to keep deleted records until they delivered to target host
  BEGIN TRANSACTION;
  DELETE TOP (20) FROM source_table_copy OUTPUT DELETED.* INTO @Table_Var;

  --insert data into linked server , or to csv file
  COMMIT;

It's also possible to use CURSOR to read data and then delete with where current of clause.

**Ideally you need to prevent applications from inserting data into source_table during step 1. If it's absolutely impossible , I'll go with an after insert trigger which is added right before step 1 and removed right after it's done which copies data to some other table I can later merge with source_table_copy.


Can I restrict a SQL Server spatial field to hold values of specified spatial types and SRIDs? I am trying to understand geography data types so I can integrate my database more closely with GIS processes. From my reading, it appears that a geography field can hold many different types of objects.

If this is the case, how do I prevent someone from loading, say, polygons into a field that I want to use to retain points, or loading points in the incorrect coordinate system? Will I have to check the data type and projection each time I write a query so I don't get erroneous results?

The usual way to do this would be through a check constraint.

The properties you want to check are .STSrid and .STGeometryType()

So something like

ALTER TABLE Test
    ADD CONSTRAINT chk_shape_gtype_srid
    CHECK (
        shape.STSrid = 4326 AND
        shape.STGeometryType() in ('POLYGON','MULTIPOLYGON')
    );


Auto Increment with TEX I have a table name: my_table

Column: ID, name, department (department is a drop down list as below:

AOD
CRM
RMD

Now I want to that when auto increment will generated it will depend on department as like below.

If department AOD then ID will generated as below:

AOD00001
AOD00002
AOD00003

If department CRM then ID will generated as below:

CRM00001
CRM00002
CRM00003

If department RMD then ID will generated as below:

RMD00001
RMD00002
RMD00003

or

AOD00001
CRM00002
AOD00003
RMD00004
CRM00005

I'd suggest either to use an additional table or by adding a new counter field to your current Departments table.

Then build an stored procedure or use a BEFORE INSERT trigger, to quietly add the next ID every time a new record is inserted.

CREATE TABLE [dbo].[DepartmentSeq]
(
    [department]        varchar(10)     NOT NULL,
    [sequence]          int             NOT NULL,

    CONSTRAINT [PK_DepartmentSeq]
        PRIMARY KEY CLUSTERED ([department] ASC)
)
ON [PRIMARY];
GO

--= SP [sp_DeparmentSeq_next]
--------------------------------------------------
CREATE PROCEDURE [dbo].[sp_DeparmentSeq_next]
(
@department varchar(10),
@next_sequence int OUT
)
AS
BEGIN

    DECLARE @sequence int;
    SET @sequence= 0;
    SET @next_sequence = 0;

    BEGIN TRY

        SELECT   @sequence = [sequence]
        FROM     [dbo].[DepartmentSeq] WITH (UPDLOCK)
        WHERE    [department] = @department;

        IF @@ROWCOUNT > 0
        BEGIN
            SET @sequence= @sequence + 1;

            UPDATE [dbo].[DepartmentSeq]
            SET    sequence = @sequence
            WHERE  [department] = @department;
        END
        ELSE
        BEGIN
            SET @sequence = 1;

            INSERT INTO [dbo].[DepartmentSeq] (department, sequence)
            VALUES      (@department, @sequence);
        END

        SET @next_sequence = @sequence;

    END TRY
    BEGIN CATCH

        DECLARE @en INT, @es INT, @ep SYSNAME, @el INT, @em NVARCHAR(2048);
        SET     @en = ERROR_NUMBER();
        SET     @es = ERROR_SEVERITY();
        SET     @ep = ERROR_PROCEDURE()
        SET     @el = ERROR_LINE();
        SET     @em = ERROR_MESSAGE();
        -- EXEC [dbo].[vsp_log_errors_insert] @ep, @en, @em, @es, @el;
        RAISERROR(@em, @es, @el);
        RETURN @en;

    END CATCH

    RETURN 0;

END
GO

As a second option, if there is a limited number of departments and depending on which version of SQL Server you use, you can use a SEQUENCE for each department.


You could use GROUP BY GROUPING SETS and the GROUPING() function:

SELECT
    idx = ROW_NUMBER() OVER (ORDER BY GROUPING(a_id), a_id),
    a = CASE WHEN GROUPING(a_id) = 0 THEN a_id ELSE 'Total' END,
    number = COUNT(*),
    value = SUM(amount)
FROM #t
GROUP BY GROUPING SETS ((a_id), ())
ORDER BY idx ;

In older versions (that don't have GROUPING SETS) you could use ROLLUP instead. In even older versions (or any version if you prefer a simpler query), you could split the query into 2 parts and UNION ALL with:

SELECT
    idx = ROW_NUMBER() OVER (a_id),
    a = a_id,
    number = COUNT(*),
    value = SUM(amount)
FROM #t
GROUP BY a_id

UNION ALL

SELECT
    1 + COUNT(DISTINCT a_id),
    a_id,
    COUNT(*),
    SUM(amount)
FROM #t

ORDER BY idx ;

Tested at rextester.com


Moving data from Oracle to SQL Server : Table size optimization I am loading data using SSIS from Oracle to an SQL Server database. One table I moved had a size of 4 MB under Oracle but when loaded to SQL Server, the size grows to 7.96 MB!

I analyzed the size (in KB) depending on the rows inserted and got the following results:

Rows    SQL Server  Oracle
10      16          64
50      48          64
100     88          64
150     128         128
200     168         128
250     208         128
300     248         128
350     288         192
400     328         192
450     368         192
500     408         192
550     448         256
600     488         256
650     528         256
700     568         256
750     648         320
800     688         320

I am suspecting that the datatypes used in SQL Server should be readapted. Here is a summary of the structure of the tables used in Oracle and SQL Server:

Oracle_DataType     SQL_Server_Datatype     #OfColumns
DATE                datetime                2
INTEGER             int                     1
NUMBER(1,0)         numeric(1, 0)           3
NUMBER(15, 5)       numeric(15, 5)          52
NUMBER(2, 0)        numeric(2, 0)           1
NUMBER(3, 0)        numeric(3, 0)           5
NUMBER(3, 1)        numeric(3, 1)           1
NUMBER(5, 0)        numeric(5, 0)           1
NUMBER(5, 2)        numeric(5, 2)           4
NUMBER(6, 0)        numeric(6, 0)           1
NUMBER(8, 3)        numeric(8, 3)           2
NUMBER(8, 4)        numeric(8, 4)           1
NUMBER(9, 0)        numeric(9, 0)           7
NUMBER(9, 3)        numeric(9, 3)           6
VARCHAR2(1)         nvarchar(1)             1
VARCHAR2(12)        nvarchar(12)            1
VARCHAR2(13)        nvarchar(13)            1
VARCHAR2(14)        nvarchar(14)            1
VARCHAR2(16)        nvarchar(16)            3
VARCHAR2(20)        nvarchar(20)            1
VARCHAR2(24)        nvarchar(24)            1
VARCHAR2(80)        nvarchar(80)            1

Do you have any idea if it will be possible to reduce the size in SQL Server so it could, at least, matches the size in Oracle?

Thanks,

Try non unicode strings: varchar instead of nvarchar. Oracle's VARCHAR2 isn't unicode datatype and in nvarchar every character takes 2 bytes instead of 1.


Opponent Winrate Over another opponent sql I am trying to find the most efficient way to build a counter warehouse.

shortened the tables just to needed info for question

Match Table

MatchId, version, type

MatchParticipants

matchId, playerid, characterid, teamid (only team1 or team2), winner (1 or 0)

10 of these rows per match

Characters

characterid, name

I considered doing a cross join of characters on characters to give me all the possible of opponents for each character. Then I would have do a massive sub-query to look into the matches table where the id's were on opposite teams.

Any ideas essentially what I see the warehouse table looking like.

character1.characterid, character1.name,
character2.characterid, character2.name,
winrate of character1 over character2

I've mocked up the minimal tables you mentioned in your question.

First, we'll run this in tempdb and cleanup anything that might get in the way:

USE tempdb;
IF OBJECT_ID(N'dbo.MatchesParticipants', N'U') IS NOT NULL
DROP TABLE dbo.MatchesParticipants;
IF OBJECT_ID(N'dbo.Matches', N'U') IS NOT NULL
DROP TABLE dbo.Matches;
IF OBJECT_ID(N'dbo.Participants', N'U') IS NOT NULL
DROP TABLE dbo.Participants;
GO

Next, we create three tables.  Matches, Participants, and MatchesParticipants:

CREATE TABLE dbo.Matches
(
    MatchID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Matches
        PRIMARY KEY CLUSTERED
    , VersionNum int NOT NULL
    , MatchType int NOT NULL
);

CREATE TABLE dbo.Participants
(
    ParticipantID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Participants
        PRIMARY KEY CLUSTERED
    , ParticipantName varchar(100) NOT NULL
);

CREATE TABLE dbo.MatchesParticipants
(
    MatchID int NOT NULL
        FOREIGN KEY REFERENCES dbo.Matches(MatchID)
    , ParticipantID int NOT NULL
        FOREIGN KEY REFERENCES dbo.Participants(ParticipantID)
    , TeamID int NOT NULL
    , Winner bit NOT NULL
    , CONSTRAINT PK_MatchesParticipants
        PRIMARY KEY CLUSTERED (MatchID, TeamID, ParticipantID)
);

Here we create some "matches"; on my system this inserted over 522,000 rows:

INSERT INTO dbo.Matches (VersionNum, MatchType)
SELECT c1.object_id % 5
    , c2.object_id % 5
FROM sys.columns c1
    CROSS JOIN sys.columns c2;

And this inserts the "Participants"; on my system I got over 700:

INSERT INTO dbo.Participants (ParticipantName)
SELECT c1.name
FROM sys.columns c1;

I used this to create rows in "MatchParticipants".  The code uses a CTE to cross join Matches and Participants in such a way that only 1/50th of the rows in a "real" cross join are inserted.  This resulted in 7,558,671 rows.  The CASE WHEN ASCII(CRYPT_GEN_RANDOM(1)) ... piece randomly selects each participant to be either on Team 0 or Team 1, with Team 1 always being the "winner":

;WITH src AS
(
SELECT m.MatchID
    , p.ParticipantID
    , val = CASE WHEN ASCII(CRYPT_GEN_RANDOM(1)) < 127
        THEN 1 /* randomly assigned winning team */
        ELSE 0
        END
FROM dbo.Matches m
    INNER JOIN dbo.Participants p
        ON m.MatchID % 50 = p.ParticipantID % 50 /* 1/50th of a CROSS JOIN  */
)
INSERT INTO dbo.MatchesParticipants (MatchID, ParticipantID, TeamID, Winner)
SELECT src.MatchID
    , src.ParticipantID
    , src.val
    , src.val
FROM src;

Some sample data from the MatchesParticipants table:

╔═════════╦═══════════════╦════════╦════════╗
║ MatchID ║ ParticipantID ║ TeamID ║ Winner ║
╠═════════╬═══════════════╬════════╬════════╣
║ 739     ║ 39            ║ 0      ║ 0      ║
║ 739     ║ 89            ║ 0      ║ 0      ║
║ 739     ║ 139           ║ 0      ║ 0      ║
║ 739     ║ 189           ║ 0      ║ 0      ║
║ 739     ║ 239           ║ 0      ║ 0      ║
║ 739     ║ 289           ║ 0      ║ 0      ║
║ 739     ║ 339           ║ 0      ║ 0      ║
║ 739     ║ 389           ║ 0      ║ 0      ║
║ 739     ║ 439           ║ 1      ║ 1      ║
║ 739     ║ 489           ║ 0      ║ 0      ║
║ 739     ║ 539           ║ 1      ║ 1      ║
║ 739     ║ 589           ║ 0      ║ 0      ║
║ 739     ║ 639           ║ 0      ║ 0      ║
║ 739     ║ 689           ║ 0      ║ 0      ║
║ 740     ║ 40            ║ 1      ║ 1      ║
║ 740     ║ 90            ║ 0      ║ 0      ║
║ 740     ║ 140           ║ 0      ║ 0      ║
║ 740     ║ 190           ║ 0      ║ 0      ║
║ 740     ║ 240           ║ 1      ║ 1      ║
║ 740     ║ 290           ║ 1      ║ 1      ║
║ 740     ║ 340           ║ 1      ║ 1      ║
║ 740     ║ 390           ║ 1      ║ 1      ║
║ 740     ║ 440           ║ 0      ║ 0      ║
║ 740     ║ 490           ║ 1      ║ 1      ║
║ 740     ║ 540           ║ 1      ║ 1      ║
║ 740     ║ 590           ║ 1      ║ 1      ║
║ 740     ║ 640           ║ 1      ║ 1      ║
║ 740     ║ 690           ║ 0      ║ 0      ║
╚═════════╩═══════════════╩════════╩════════╝

This shows the row counts for each table:

SELECT ParticipantCount = (SELECT COUNT(1) FROM dbo.Participants)
    , MatchCount = (SELECT COUNT(1) FROM dbo.Matches)
    , MatchParticipantCount = (SELECT COUNT(1) FROM dbo.MatchesParticipants)

╔══════════════════╦════════════╦═══════════════════════╗
║ ParticipantCount ║ MatchCount ║ MatchParticipantCount ║
╠══════════════════╬════════════╬═══════════════════════╣
║ 723              ║ 522729     ║ 7558671               ║
╚══════════════════╩════════════╩═══════════════════════╝

To get the entire list of stats for each pair of players, I used this query:

;WITH source AS
(
    SELECT Winner = Winners.ParticipantID
        , Loser = Losers.ParticipantID
        , MatchID = Winners.MatchID
    FROM (
            SELECT *
            FROM dbo.MatchesParticipants mp1
            WHERE mp1.Winner = 1
        ) Winners
        INNER JOIN (
            SELECT *
            FROM dbo.MatchesParticipants mp2
            WHERE mp2.Winner = 0
            ) Losers ON Losers.MatchID = Winners.MatchID
                AND Losers.ParticipantID <> Winners.ParticipantID
)
, WinsLosses AS
(
    SELECT source.Winner
        , source.Loser
        , Wins = COUNT(1)
        , Losses = (SELECT COUNT(1)
                     FROM source l
                     WHERE l.Loser = source.Winner
                         AND l.Winner = source.Loser
                   )
    FROM source
    GROUP BY source.Winner
        , source.Loser
)
SELECT Winner = pw.ParticipantName
    , Loser = pl.ParticipantName
    , WinsLosses.Wins
    , GamesPlayed = WinsLosses.Wins + WinsLosses.Losses
    , WinRatio = CONVERT(decimal(10,5), WinsLosses.Wins) /
                      (WinsLosses.Losses + WinsLosses.Wins)
FROM WinsLosses
        INNER JOIN dbo.Participants pw ON WinsLosses.Winner = pw.ParticipantID
        INNER JOIN dbo.Participants pl ON WinsLosses.Loser = pl.ParticipantID
ORDER BY pw.ParticipantName
    , pl.ParticipantName;

Results:

╔══════════╦═════════════════════╦══════╦═════════════╦════════════════════╗
║ Winner   ║ Loser               ║ Wins ║ GamesPlayed ║ WinRatio           ║
╠══════════╬═════════════════════╬══════╬═════════════╬════════════════════╣
║ addr     ║ column_id           ║ 2651 ║ 5252        ║ 0.5047600913937547 ║
║ addr     ║ csn                 ║ 2535 ║ 5210        ║ 0.4865642994241842 ║
║ addr     ║ depid               ║ 2663 ║ 5294        ║ 0.5030222893842085 ║
║ addr     ║ fileguid            ║ 2592 ║ 5256        ║ 0.4931506849315068 ║
║ addr     ║ grantee             ║ 2604 ║ 5179        ║ 0.5027997682950376 ║
║ addr     ║ internalstatus      ║ 2636 ║ 5224        ║ 0.5045941807044410 ║
║ addr     ║ name                ║ 2662 ║ 5265        ║ 0.5056030389363722 ║
║ addr     ║ name                ║ 2552 ║ 5206        ║ 0.4902036112178255 ║
║ addr     ║ ParticipantName     ║ 2580 ║ 5248        ║ 0.4916158536585365 ║
║ addr     ║ pgfirst             ║ 2593 ║ 5252        ║ 0.4937166793602437 ║
║ addr     ║ product             ║ 2599 ║ 5280        ║ 0.4922348484848484 ║
║ addr     ║ protocol            ║ 2635 ║ 5238        ║ 0.5030546009927453 ║
║ addr     ║ service_id          ║ 2610 ║ 5216        ║ 0.5003834355828220 ║
║ affinity ║ binary_message_body ║ 2605 ║ 5222        ║ 0.4988510149368058 ║
║ affinity ║ dbid                ║ 2568 ║ 5208        ║ 0.4930875576036866 ║
║ affinity ║ diffbaseseclsn      ║ 2554 ║ 5199        ║ 0.4912483169840353 ║
║ affinity ║ encoding_type       ║ 2652 ║ 5259        ║ 0.5042783799201369 ║
╚══════════╩═════════════════════╩══════╩═════════════╩════════════════════╝

The plan:

The Source CTE joins the "winners" and the "losers" for each match.  The WinsLosses CTE provides the total for Wins and Losses.  Finally, the main query shows each participant name, their opponent's name, the number of Wins, Games Played, and of course, the Win Ratio.  On my system, this generates 9,744 rows in about 18 seconds - the results of this query could be pre-computed every 15 minutes for display to the end user.

To get that speed out of this query, I created these two covering indexes:

CREATE NONCLUSTERED INDEX MatchesParticipants_ix01
ON dbo.MatchesParticipants(Winner, MatchID)
INCLUDE (ParticipantID);

CREATE NONCLUSTERED INDEX MatchesParticipants_ix02
ON dbo.MatchesParticipants (ParticipantID, Winner)
INCLUDE (MatchID);

If you're interested in obtaining the stats for any specific given combination of players, this query does the trick:

DECLARE @ParticipantID1 int;
DECLARE @ParticipantID2 int;

SET @ParticipantID1 = 216;
SET @ParticipantID2 = 566;

;WITH source AS
(
    SELECT Winner = Winners.ParticipantID
        , Loser = Losers.ParticipantID
        , MatchID = Winners.MatchID
    FROM (
            SELECT *
            FROM dbo.MatchesParticipants mp1
            WHERE mp1.Winner = 1
        ) Winners
        INNER JOIN (
            SELECT *
            FROM dbo.MatchesParticipants mp2
            WHERE mp2.Winner = 0
            ) Losers ON Losers.MatchID = Winners.MatchID
                AND Losers.ParticipantID <> Winners.ParticipantID
)
, WinsLosses AS
(
    SELECT source.Winner
        , source.Loser
        , Wins = COUNT(1)
        , Losses = (SELECT COUNT(1) FROM source l WHERE l.Loser = source.Winner AND l.Winner = source.Loser)
    FROM source
    GROUP BY source.Winner
        , source.Loser
)
SELECT Winner
    , Loser
    , WinsLosses.Wins
    , GamesPlayed = WinsLosses.Wins + WinsLosses.Losses
    , WinRatio = CONVERT(decimal(10,2), WinsLosses.Wins) / (WinsLosses.Losses + WinsLosses.Wins)
FROM WinsLosses
WHERE Winner = @ParticipantID1
    AND Loser = @ParticipantID2
ORDER BY Winner
    , Loser;

The results of the query:

╔════════╦═══════╦══════╦═════════════╦═════════════════╗
║ Winner ║ Loser ║ Wins ║ GamesPlayed ║ WinRatio        ║
╠════════╬═══════╬══════╬═════════════╬═════════════════╣
║ 216    ║ 566   ║ 2637 ║ 5233        ║ 0.5039174469711 ║
╚════════╩═══════╩══════╩═════════════╩═════════════════╝

Results are returned immediately.  This is the actual execution plan:


Performance impact MySQL replication vs table-synchronisation script Currently I am considering implementing a MySQL table replication solution for a client, this client has 2 servers in 2 different locations:

Server 1 running an application with mysql databases (Running Ubuntu 14.04 LTS)
Server 2 has a PHP webserver frontend to serve it's customers with a website and customer panel (also Ubuntu, same version).

The databases on server 1 total around 2.5 GB in size.
The servers are in different data centers but within 5ms latency of one another both with gigabit uplinks and heavy dataplans.

Currently the webserver(2) relies completely on the availability of the application server (1). Though this is essential it does mean that the webserver  is effectively useless in case of maintenance or unscheduled downtime, the client does not wish to synchronize the databases to both locations but would allow the synchronization of a single table of account login data (just the one table, no other tables from the same database) to at least keep the bare functionality of the website intact.

Now the 2 options I currently am considering are:
A. Setting up MySQL Replication Master->Slave setup for this table
B. Using a "smart/efficient" table sync script (https://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html)

My consideration is also that the binary log of the application mysql server is around 8.5Gb a day and I was unable to find out the exact performance impact of this is on replication. I can say that the table of which I speak is 88MB and has around ~250.000 rows. The daily changes to the account table I wish to synchronize are very minor, I expect less than 1000 rows a day).

I am currently leaning towards option B because

It would avoid future issues on mysql version conflicts between servers (which I do not both manage for the client)
The amount of data changes are rather light on the specific table I wish to synchronize.
The methods MySQL offered to synchronize just specific tables seemed to fall short as the replication technology puts more emphasis for use as a full replication solution (in this my fear that big binary logs would have a negative impact based on mysql forum/article comments)
"to the second" data accuracy is not an concern for the client, a sync every hour would already suffice (his words).

My question then of course is if anyone can provide information on if option A or B (or another) would be preferred in this scenario and more importantly why?

Please note: I aim to keep the core setup which the client runs intact, the application or web server cannot switch to another database engine.

First of all about Variant A:

Size of bin-log will not affect to performance if You will use filter to replicate only selected tables, and even if You will replicate all - it also normally not a problem. For compare we replicate 20Gb (and more) between datacenter with real-time loading (telecom)
Keep version of Source and Target Server same - it good practice even if You choose Variant B - it avoid many other troubles

About Variant B - I personally look for tools similar pt-table-sync as for excellent but occasional tools in Your toolbox, and not for permanent solution. I can not explain drawbacks, it just personal vision

Variant C:
Tools like - SymmetricDS

Variant D:
I personally actively work now with CDC variants, when You parse bin-logs and ship them to subscriber over message queue.

I have tested under loading at least 2 working solution (really it more for now):

StreamSets
Maxwell + Talend
Very interesting for some reasons - MariaDB MaxScale, but have not a time for make it work

I personally like this because:

have a lot of integration projects
not always source and target system same. Most resent in my case - MySQL to Redshift, DB2
It also allow realise "event-based" integration - fire Sub-Job when specific data changed
Have more than 1 subscriber with the same producer

Edited for add answer for question in comments:

The problem as medal have 2 (and more) sides:

Size of binary-logs - this is objective, but with good network it not a issue.
for make shipping faster You can enable compression - see more
Time what Slave need to apply all changes from Master. Even if You enable multithread slave it still slower than on Master, and by default with single thread it could be a problem with very high amount of changes on Master.

But as I wrote You can enable filter on Slave side for reduce number of monitored objects - replicate-do-table and Slave will ignore all other changes and apply only for this table (this is case of more wide filter - replicate-do-db), You can repeat this settings for several tables, or use wildcards

Filter logic diagram

Any solution have and benefits and drawbacks, I will prefer Replication because it will apply updates "on the fly" but not only when I run the external program.


Adding a varchar to a datetime column and dealing with nulls I have two columns in my table a varchar(8) titled Trans_Time which holds data in an XX:XX:XX time format and a DateTime column named Trand_Date which holds just a the date component in XX-XX-XXXX format.

I am trying to combine the two columns to get a full date and time (i.e. if Trans_Time is 08:10:14 and Trand_Date is 04-04-2007 the output should be 04-04-2007 08:10:14).

My problem is that some times the Trans_Time is null and this produces an output of null.

Is there some way I can treat null as 00:00:00?

You can use ISNULL or COALESCE in T-SQL to replace a null value:

SELECT
  Trand_Date + ' ' + COALESCE(Trans_Time, '00:00:00') AS Trand_DateTime1,
  Trand_Date + ' ' + ISNULL(Trans_Time, '00:00:00') AS Trand_DateTime2
FROM MyTable

Both columns above should return the same value. Note that if you want to perform date-based comparisons, e.g. DATEDIFF, DATEADD or even something as simple as WHERE Trand_DateTime < GETDATE(), you can create a view of the table that presents the values as an actual SQL DateTime datatype like so:

CREATE VIEW [dbo].[ViewTransDateTimes]
AS
SELECT
  CAST(Trand_Date + 'T' + COALESCE(Trans_Time, '00:00:00') as DateTime) AS Trand_DateTime
  --, [OTHER COLUMNS]
FROM MyTable
GO

Then you can do things like:

SELECT * FROM ViewTransDateTimes
WHERE DATEDIFF(day, Trand_DateTime, GETDATE()) < 1000


Philosophy on stored procedures that perform updates: is a 'granular' update approach sufficient? I am embarking on on a large project and have an inquiry about stored procedures in general.
Let's say I have a User table.
UserKey IDENTITY NOT NULL PRIMARY KEY
FirstName nvarchar(50)NOT NULL
LastName nvarchar(50) NOT NULL
Email nvarchar(50) NOT NULL
Birthdate date NULL
EffectiveFrom datetimeoffset NOT NULL
EffectiveThru datetimeoffset NULL

In reality, there are some more fields here, but for the sake of brevity I have skimmed it down to the basics.
Now, I am faced with creating stored procedures to allow basic CRUD functionality for this table.
The Insert sproc is simple enough.
usp_INSERT_User:
CREATE PROCEDURE [dbo].[usp_INSERT_User]
(
    --@UserKey BIGINT,
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(50),
    @BirthDate DATE = NULL,
    @EffectiveFromDtTm DATETIMEOFFSET(7),
    @EffectiveThruDtTm DATETIMEOFFSET(7) = NULL,
)

My main inquiry is how I should write an update sproc.
Since there can be pretty granular updates, should I create an update stored procedure for each of those granular updates?
For example, let's say I only want to update the user's email after processing a verified email update for the user, would I write a sproc with one parameter?
usp_UPDATE_UserEmail @NewEmail
If the user updated their profile, I could write a sproc like:
usp_UPDATE_User @UserKey, @FirstName, @LastName, @Email, @Birthdate
So, to cover my most common update operations, I might end up with a few sprocs to cover use cases of updates to the User table. Obviously, the larger the table and/or the higher number of business requirements would have a direct correlation to the number of update sprocs written for a particular table. This gets me thinking about maintainability of a ton of sprocs.
Yesterday, I went down the rabbit hole of

"oh, why don't I just write ONE end-all-be-all update sproc that can take whatever parameter I want to update via an XML param"

Luckily, without using dynamic sql, I was able to create that monstrosity. And it works.
I then decided to test speeds between my fancy xml sproc and just a regular update sproc (with multiple params) with very similar updates.
So, I created a mock user table with 12 million records and tested both approaches. Unsurprisingly, the XML approach on average, took 7x longer than the vanilla update sproc.
There is also additional overhead on the other side of the wire serializing parameters into XML, which could be of concern with a large update batch.
Now, I say all of that to wrap up and ask which type of approach in your experience is most often (if at all) seen? Is is most common practice to have granular sprocs like usp_UPDATE_Field1 / usp_UPDATE_Field2 / usp_UPDATE_Field3&4 OR usp_UPDATE_whateveryouwant
I like the simplicity of having a golden sproc that can take however many number of parameters you want to pass it and update them accordingly, but there are also performance drawbacks.
To give more context: running SQL Server 2019. I am also pretty new at this stuff.
Thanks for any help!

Stored procs are the programming API of the database, the methods you invoke to mutate the system state. As such, I prefer to have SPs names reflect the business outcome desired rather than the implementation - change_of_address rather than update_customer. Each SP has the parameters to perform that business change and no others. Each does one thing and does it well.
If the business supports two processes that both update a common column then so be it. That column is an input to each of those SPs. Say there's a web based "change profile" and a mobile-only "register new phone". They both write to customer.phone_number. I would have separate SPs for each business purpose.
There is no measurable overhead to having many small, tightly focused stored procedures. Changes to data types are automatic on the next recompile. Changes to column names are simple search-and-replace. SSMS's built-in dependency analysis can help, too. If you use local variables that mirror columns use the column's name e.g. customer_name is read into @old_customer_name.


Want to select distinct from 3 fields I have a mySQL table with a town_id and 3 categories - cat1, cat2, cat3.

I need to be able to come up with a distinct list of all the categories in the table selected by town_id, not duplicated and in alphabetical order.

Thanks!

If I understood the question right, you need something like

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

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

Taking the OP's comment context:

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


Fetching rows from million rows table(Optimization) I have millions of rows in a table of Greenplum database out of which I have to fetch around 45k rows and store them in a python List.

It's taking more than 2hrs to fetch the data. How can I optimize the time taken to fetch data?

resultList = []
for(item in list):
  result = SELECT column_1, ... column_n from TABLE WHERE column = item
  resultList.append(result)

To do anything takes a finite amount of time.
To do the same thing 45K times, takes [at least] 45K times as long.

This is a classic 1+n query.
Retrieve a list of ids, then process each in turn with another query.

It runs like a slug.
It's completely unscaleable.
It's complete untunable.  Indeed, your database will be thoroughly enjoying itself, being asked to retrieve a single record based on single value that it can find in a unique index!

Rewrite your code to retrieve the whole Set of data, then loop through the rows within it to process it or, in this case, simply select the data you want, for all the rows you want, in a single query.  I assume you used another query to retrieve the values in your "list": use the "WHERE" clause from that query to retrieve [all] the data you need:

(PseudoCode)

resultlist =
   SELECT column_1, ... column_n
   from TABLE
   WHERE condition-you-originally-used-to-get-the-list-of-ids
   ORDER BY column_1

No; it's not very Object Oriented.

It's Set Based and, for Relational Databases, there's nothing better.


Why would Mysql's Round(N.DD,1) sometimes show no digits after the decimal place when using the Perl DBI? We have a set of calculations that do something like

SELECT ROUND(SUM(ROUND(3*IP))/3,1) as IP_sum from ....

Say for example that IP_sum is exactly 180.

When we call from the mysql client we get 180.0 every time, but if we use the perl dbi, the result depends on IP's column type.

When IP is a decimal(7,1) we get one digit and IP_sum => '180.0'
When IP is a float(7,1) we don't get any digits => IP_sum => '180'

Is anyone aware of a new change or update to the Perl DBI that might be causing this?  We just updated some modules and programs this week.

Engine: Mysql 5.6,  mysql client, 5.5.

This was unrelated to MySQL and was due to a change in the Perl DBD::mysql packages from version 4.013 to 4.042. Essentially, DBD::mysql now attempts to coerce the MySQL datatype into a Perl datatype. So, previously, a float of 4.0 would be returned as a string '4.0', but now the same float will be returned as a numeric value and be displayed as '4' when printed (without additional formatting on the part of Perl.


What perfmon counters should I use to get the fastest restore possible on an idle system? I have a development VM running Windows 2012-R2 and Sql Server 2014. I have one disk from a SAN holding the OS and SQL. My backup file is stored locally on the disk.

I want to restore database as quickly as possible as part of a "reset my dev environment" process. The system is mostly idle when I do this. The system runs SQL Server and Tomcat 7. I usually have an RDP session which has one or more of the following open Perfmon, SSMS, Visual Studio 2013, Visual Studio 2010. Process Monitor. I'm willing to tune this restore to the point where this RDP session becomes completely unresponsive during the restore process. No one but me is using this server.

Using Nic Cain's post I was able to get my restore process from 6xx seconds to ~100-120 seconds with the following parameters:

RESTORE DATABASE [XXXX]
    FROM DISK = N'$(BackupFile)'
        WITH
            REPLACE,
            MOVE N'RMS_SIMULATION' TO N'$(MdbPath)\XXXX.mdf',
            MOVE N'RMS_SIMULATION_log' TO N'$(LDBPATH)\XXXX.LDF',
            STATS = 5,
            MAXTRANSFERSIZE = 4194304,
            BUFFERCOUNT = 72

By adding MAXTRANSFERSIZE and BUFFERCOUNT I was able to peg the I/O Data bytes/sec and I/O Data operations/sec counters to the top of the perfmon chart. Before that, bytes/sec would spike to the top, but spend more time at the bottom of the scale, and operations/sec was spikey in the middle of the scale.

Now I know I can tweak with the numbers and run the restores 100 times at different numbers and measure the restore time. While, there is a certain scientific vigor in that approach, I'd like something more data-centric.

I know I don't want SQL Server page faulting during the restore, because hitting virtual memory is a big slow down. However, what other perfmon counters should I be looking at?

If you are running Developer or Enterprise edition, consider restoring from a database snapshot rather than a full backup (https://msdn.microsoft.com/en-s/library/ms189281.aspx).  This can also be less resource-intensive than a full restore.

The time needed to revert from a snapshot is largely proportional to the amount of changes made since the snapshot was taken so it can be significantly faster than a normal restore in many cases.  Like a regular full restore, the log is initialized so keeping the log size small will reduce the restore time.


SQL view, how to append results from tableB with the results from tableA I am using SQL server 2012. I would like to generate a sql view where I got all records from tableA. And below these results I would like to have the results from tableB. There are no relations between these 2 tables. The columns are also differant. Here is an example:

tableA:                       tableB:

+-------------------------+   +--------------------------------+
| Name                        | Year
---------------------------   +---------------------------------
| A    |                      | 2000    |
| B    |                      | 2001    |
| C    |                      | 2002    |

I would like to have results like this:

 +-------------------------+
    | Name | Year
    ------------------------
    | A    |
    | C    |
    | D    |
    |      | 2000
    |      | 2001
    |      | 2002

How can I realize this? I see only joins, union solutions, but they dont work for me because I dont have some relation between these 2 tables.

with cte1(Name, Year) as ( select Name, null as year from tablea),
cte2(Name, Year) as (select NULL as Name, year from tableb)
Select Name, Year from CTE1
UNION ALL
select Name, Year from CTE2

This should work, didn't read the question properly last answer :)


How to restore everything (including "postgres" role) from pg_dumpall backup I have a postgres 9.1 server running and I do backups with

pg_dumpall -c -f /nfs/backup/fulldump.sql

I'm testing restoring the backup to another server with

psql -f /nfs/backup/fulldump.sql

while running as user postgres and I get two errors

psql:/nfs/backup/fulldump.sql:27: ERROR:  current user cannot be dropped
psql:/nfs/backup/fulldump.sql:36: ERROR:  role "postgres" already exists

I understand that these are caused by commands

DROP ROLE postgres;
CREATE ROLE postgres;

in the backup dump.

Is it possible to somehow restore the dump from pg_dumpall -c without having a single error?

I ended up using grep for this:

egrep -v '^(CREATE|DROP) ROLE postgres;' dump.sql \
| psql -X -v ON_ERROR_STOP=1 -1 ...


Optimizer not choosing index union plan Why is the following query slow?

select count(*)

from [dbo].[mt_dispatch_link]
   , [dbo].[_mt_dispatch] [_mt_dispatch]

where   (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id
     and mt_dispatch_link.dispatch_id_1 = _mt_dispatch.dispatch_id)

   or   (mt_dispatch_link.contract_id_2 = _mt_dispatch.contract_id
     and mt_dispatch_link.dispatch_id_2 = _mt_dispatch.dispatch_id)

It takes over 10 minutes, and then I tend to stop it at that point. My question is more about how to make sense of the query plan.

Looking at the query plan I can see that the bottom clustered index scan is returning about 250000 records, but the cost is 0% and it is being put into a temporary table.

The top index scan is about 25000 records.

But the cost of 95% comes from the nested join. What conclusions should I draw from this?

The above query plan shows two index scans, does that mean it is doing 25000 + 250000 index scans, or does it mean that it is doing 25000 * 250000 index scans?

If I change the query to this (adding FORCESEEK):

select count(*)

from [dbo].[mt_dispatch_link]
   , [dbo].[_mt_dispatch] [_mt_dispatch]

    WITH (FORCESEEK)

where   (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id
     and mt_dispatch_link.dispatch_id_1 = _mt_dispatch.dispatch_id)

   or   (mt_dispatch_link.contract_id_2 = _mt_dispatch.contract_id
     and mt_dispatch_link.dispatch_id_2 = _mt_dispatch.dispatch_id)

I end up with a much better plan and the query runs instantly:

I ran update statistics on both tables. Didn't fix it unfortunately. The table design is not very good, so I think SQL Server doesn't really understand and is thus coming up with a poor query plan. Some more info about the table design at How to Optimise Query.

Why is the query optimiser not coming up with the optimum plan?

The optimizer does not always consider index-union plans (like the one shown in your second graphic) to resolve disjunctions (OR predicates) unless a FORCESEEK or INDEX hint is specified.  This is a heuristic* based on some practical considerations:

Index union is not often enough a good plan selection for general queries.
The number of ways indexes can be combined grows exponentially.

Using a hint changes the way the optimizer searches the space of possible plans.  It disables some of the general heuristics and pursues a more goal-orientated strategy.

The optimizer's usual primary goal is to find a good plan quickly.  It does not exhaustively search for the 'best' plan (even relatively simple queries could take years to compile if it did).

Joins with multiple conditions separated with OR have long been problematic.  Over the years, the optimizer has added new tricks like converting them to equivalent UNION forms, but the transformations available are limited, so it is quite easy to come unstuck.

As far as the query plan is concerned:

The first row from DispatchLink causes a full scan of the Dispatch table
The result of the scan is stored in an internal tempdb worktable (the Table Spool)
The join checks every row from the worktable against the full OR predicate
The next row is fetched from DispatchLink and the process repeats from step 3

If there are 25,000 rows in the Dispatch Link table, the spool will be fully scanned 25,000 times.  This is a disaster of course (and without index intersection, the best the optimizer can do is run the whole thing on multiple threads).

Percentage costs in query plans are only the optimizer's estimates.  They never reflect actual execution costs, and are subject to the optimizer's model and will usually bear little resemblance to the 'true' cost of executing the plan on your specific hardware.

Costing numbers are there to be informative, but they should not be taken literally.  The particular model the optimizer uses happens to produce pretty good plans for most queries on most systems across the world - that does not mean the model approximates anyone's reality, just that it happens to work reasonably well in practice.

Changing the design so that (Dispatch, Contract) pairs are stored in rows rather than repeated across columns will make the whole index-intersection problem go away.  Relational designs with useful constraints and indexes almost always get the best out of the optimizer.

* This can be overridden with undocumented trace flag 8726


Is it possible to create a database called C that is a view into all tables in database A + B? Using SQL 2005 or 2008, is it possible to create a database called C that is a view into all tables in database A + B ?   Databases A, B, and C  are on the same local server.

I am wanting to be able to create join queries between tables that are in 2 different databases.    Is there another way to do this?

You can create DatabaseC if you want to, but it adds no value. Just use 3 part object names:

SELECT
   A.col1, B.col2
FROM
   DatabaseA.dbo.TableA A
   JOIN
   DatabaseB.dbo.TableB B ON A.xx = B.xx


SQL Server slow after host reboot I work with SQL Server 2008R2 and the server is rebooted in a monthly basis due to Windows patching. Usually the reboot takes place in the last Sunday of each month during the morning period. However every time that happens, some critical jobs that are executed in a daily basis late at night become incredible slow taking more than the double of the time to complete. After a few days, they get back to their usual duration.
I understand that the caches are cleared and some queries will recompile but would that make queries to take the double of the time (from usual 4 to 8 or more hours) ?
And why they get back to the normal behavior without no intervention at all after a few days?
I wonder what could be done to minimize (if not completely avoid) the impact of this monthly reboots.
Thoughts?

Reason for slowness is that - your buffer pool (including other caches e.g. plan cache, etc) are nuked when sql server is restarted.

Couple of things that you can do -

After reboot, if you know what tables are getting hit by your critical jobs, just cache them (doing select * ) into the buffer pool.
Check to see if your server is configured properly  - e.g. power setting (high perf mode) , max memory setting, etc. You can use sp_Blitz and follow general best practices.

I am assuming that you are regularly updating stats and / or reorg/rebuilding indexes as and when required.


Doubts about Converting MyISAM to InnoDB (master/slave and more) I want to convert my MySQL DB (46 Tables, 2.1 GB) from MyISAM to InnoDB. I have a couple of questions about this process.

Since I have a Master/Slave structure, do I need to manage this and change the Slave also? Or any structural changes will directly affect the Slave?
Do I have to stop the slave before the conversion?
I'm using MySQL 5.5.32 (on ubuntu 12.04). Should I update MySQL (to 5.6.*) before the conversion?
I use only stored procedure to run queries. Is there something I need to change for the new engine?

If you have any other suggestions, feel free to share them with me.

ANSWERS TO YOUR QUESTIONS

  1) Since I have a Master/Slave structure, do I need to manage this and change the Slave also? Or any structural changes will directly affect the Slave?

I would recommend you change the Master to all InnoDB and leave the Slave all MyISAM

You will definitely need to make configuration changes since InnoDB has more a lot more moving parts in memory and disk.

  2) Related to n.1, do I have to stop the slave before the conversion?

You can disable binary logging in your session and convert all tables on the Master without replicating any of it to the Slave. Therefore, you do not need to run STOP SLAVE;

  3) I'm using MySQL 5.5.32 (on ubuntu 12.04). Should I update MySQL (to 5.6.*) before the conversion?

You need to upgrade either way. Why? I wrote a post describing how Oracle had a security path installed in MySQL 5.5.40 and 5.6.21 (See my post Find MySQL Patch Level)

If you want to remain with MySQL 5.5, upgrade to MySQL 5.5.40 or greater
If you want to upgrade to MySQL 5.6, upgrade to MySQL 5.6.21 or greater

  4) I use only stored procedure to run queries. Is there something I need to change for the new engine?

No changes for stored procedures are necessary

I have discussed this before. Here a two of my posts where I describe having a Master and Slave with different engines:

Jan 15, 2013 : Using MyISAM for reading and InnoDB for writing data

Accepted Answer from Shlomi Noach
My Answer

Aug 14, 2012 Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

CONVERSION SCRIPT

Create a script that has these instructions. It will convert everything to InnoDB without replicating:

CONVERT_SCRIPT=/tmp/MassConvertRowFormat.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Query to Create SQL Script
# that Converts All Your MyISAM tables to InnoDB
#
SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name', ENGINE=InnoDB;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN ('information_schema','mysql','performance_schema')"
SQLSTMT="${SQLSTMT} ORDER BY data_length"
#
# 1. Disable the Binary Logging of Your Session
# 2. Append storage engine conversion statements
# 3. Execute the Script
#
echo "SET SQL_LOG_BIN = 0;" > ${CONVERT_SCRIPT}
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" >> ${CONVERT_SCRIPT}
mysql ${MYSQL_CONN} < ${CONVERT_SCRIPT}

NOTE: Please do your homework and make changes to the InnoDB settings and log files before doing this conversion. Without do so, some have experienced slow performance because of the default settings of InnoDB

MY RECOMMENDED OLDER POSTS

Here are my other posts on how and why to tune InnoDB after (or before) converting from MyISAM

Aug 17, 2012 : When to switch from MyISAM to InnoDB?
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options
Apr 11, 2014 : MySQL - Best long read query solution within a replication based system
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?


MySQL does not follow Ubuntu System Time Zone An Ubuntu 16.04 server has it's time zone set to America/New_York using the command

sudo timedatectl set-timezone America/New_York

This is confirmed by running the commands

timedatectl status | grep "Time zone"

 Time zone: America/New_York (EST, -0500)

date +%Z

EST

However MySQL thinks that the system's time zone is still UTC. MySQL 5.7 server (running within a docker container) has been restarted befpre querying for the MySQL timezone

Output of MySQL Query show global variables like '%time_zone'

system_time_zone UTC
time_zone SYSTEM

Did something go wrong?

You say you run mysql inside docker. Docker needs timezone as an environment variable as follows:

Add the following to mysql startup command in docker:

-e TZ='America/New_York'

https://github.com/docker-library/mysql/issues/320


How to query which users are using which certificates in SQL Server? I would like to know which users a certificate is used by.  I tried to do this query but got no results:
SELECT
    master.sys.sql_logins.name,
    sys.certificates.name
FROM
    master.sys.sql_logins
INNER JOIN
    sys.certificates
    ON master.sys.sql_logins.sid = sys.certificates.sid

Does anyone know how to do this?
Any help would be greatly appreciated.

That information is not exactly available and may be a security risk if it was, because essentially you'd need to know the public keys used with that certificate. This StackOverflow answer may be relevant and essentially agrees with my previous statement.
Taking a step back, these are the things you can derive though:

If your certificate was generated for a specific database, then all principals within that database are applicable to that certificate. You should be able to see which principals are mapped to the given database certificate by querying sys.database_principals WHERE type = 'C' as discussed here.

You should be able to get the principal of who owns a given certificate by using the principal_id to join to the sys.server_principals view to get the principal name.


Teradata learning resources I'm moving from SQL Server to Teradata, therefore could someone recommend any good (preferably online) resources about Teradata? I'm not looking for information about basics, but rather about new features and concepts that are in Teradata.

I would spend sometime over at the Teradata Developer Exchange, Teradata Questions (currently disabled), and the Teradata Forum. There is a wealth of information to be gleaned from these sites.

There is an Express version of Teradata that can be downloaded from here. This will allow you the opportunity to experiment with Teradata on your own computer. The VMware edition will probably provide you the most flexibility from a DBA perspective. The Windows version is great for developers who want to improve their understanding of SQL with Teradata.

Of course there are also questions about Teradata on Stack Overflow using the teradata tag.


How do I pass multiple statements into sqlcmd? Wanting to automate some tasks instead of firing up SSMS for everything, I'm trying to write a batch file to run these statements, which work perfectly when I paste them into the sqlcmd prompt.  They simply check the mirroring status, suspend mirroring and then check the status again.  (To minimize dependencies and ease migration from one server to another, I want to keep all commands in a single file.)

select cast(DB_NAME(database_id) as char(30)),
        cast(mirroring_state_desc as char(15))
from sys.database_mirroring
where mirroring_state_desc is not null;
go;

ALTER DATABASE db1 SET PARTNER SUSPEND;
ALTER DATABASE db2 SET PARTNER SUSPEND;
go;

select cast(DB_NAME(database_id) as char(30)),
        cast(mirroring_state_desc as char(15))
from sys.database_mirroring
where mirroring_state_desc is not null;
go;

So, I wrote this batch file:

SETLOCAL ENABLEDELAYEDEXPANSION
SET @SQLSTRING=select cast(DB_NAME(database_id) as char(30)), ^
        cast(mirroring_state_desc as char(15)) ^
from sys.database_mirroring ^
where mirroring_state_desc is not null; ^
go ^
^
ALTER DATABASE MSCRM_CONFIG SET PARTNER SUSPEND; ^
ALTER DATABASE XeroxGHSCRMTest_MSCRM SET PARTNER SUSPEND; ^
go ^
^
select cast(DB_NAME(database_id) as char(30)), ^
        cast(mirroring_state_desc as char(15)) ^
from sys.database_mirroring ^
where mirroring_state_desc is not null; ^
go
::
echo.
sqlcmd -S AGABQ0VMCRMDB01\PROD_CRM -E -Q "%@SQLSTRING%"

However, it fails with syntax errors at the "go" commands.  I've tried "go" and "go;" with no luck.

Any tips or guidance to make this work would be appreciated.

There are a couple of problems with this approach:

The immediate problem is that you cannot continue an empty line. So the two lines (following each go ^ line) that are only the single character ^ are causing the line to end and not continue. It has nothing to do with the go. So you can just add a space before the carrot and it will build the full string.
However, even if you fix problem #1, you are still left with invalid SQL since GO needs to be on a line by itself or, at most, optionally followed by some whitespace and an INT value for how many times to repeat the prior batch (e.g. GO 12). But, using line continuation via ^ there won't be any embedded returns so everything will be submitted as a single line, and that is not valid syntax with the GO commands in there.

This leaves you with two options:

If you are submitting SQL that does not require any batch separation, then just get rid of the GO commands (and wouldn't hurt to get rid of the @ sign, and the ENABLEDELAYEDEXPANSION and final GO aren't needed either):

SET SQLSTRING=select cast(DB_NAME(database_id) as char(30)), ^
        cast(mirroring_state_desc as char(15)) ^
from sys.database_mirroring ^
where mirroring_state_desc is not null; ^
 ^
ALTER DATABASE MSCRM_CONFIG SET PARTNER SUSPEND; ^
ALTER DATABASE XeroxGHSCRMTest_MSCRM SET PARTNER SUSPEND; ^
 ^
select cast(DB_NAME(database_id) as char(30)), ^
        cast(mirroring_state_desc as char(15)) ^
from sys.database_mirroring ^
where mirroring_state_desc is not null;

ECHO.
sqlcmd -S AGABQ0VMCRMDB01\PROD_CRM -E -Q "%SQLSTRING%"

If you do need batch separation (e.g. you have a CREATE PROCEDURE statement or something like that), then you can create a SQL file in your CMD script, add each line individually, and then use that as the SQL script that SQLCMD will run. This allows you to embed returns as well as use GO commands:

@ECHO OFF

ECHO. > MultiLine.sql
ECHO select cast(DB_NAME(database_id) as char(30)), >> MultiLine.sql
ECHO        cast(mirroring_state_desc as char(15)) >> MultiLine.sql
ECHO from sys.database_mirroring >> MultiLine.sql
ECHO where mirroring_state_desc is not null; >> MultiLine.sql
ECHO GO >> MultiLine.sql
ECHO. >> MultiLine.sql
ECHO ALTER DATABASE MSCRM_CONFIG SET PARTNER SUSPEND; >> MultiLine.sql
ECHO ALTER DATABASE XeroxGHSCRMTest_MSCRM SET PARTNER SUSPEND; >> MultiLine.sql
ECHO GO >> MultiLine.sql
ECHO. >> MultiLine.sql
ECHO select cast(DB_NAME(database_id) as char(30)), >> MultiLine.sql
ECHO        cast(mirroring_state_desc as char(15)) >> MultiLine.sql
ECHO from sys.database_mirroring >> MultiLine.sql
ECHO where mirroring_state_desc is not null; >> MultiLine.sql

ECHO.
sqlcmd -S AGABQ0VMCRMDB01\PROD_CRM -E -i MultiLine.sql


Is there any benefit in limiting the number precision on a surrogate PK in Oracle? I'm reviewing some DDL that creates surrogate primary keys of type NUMBER(38).  When I first looked at this I thought it was a problem because 10^38 is a HUGE number, much much bigger than the number of stars in the universe or all the grains of sand on earth.  But I've come to learn that this doesn't matter in terms of how the numbers are stored.  And there doesn't seem to be any other options than say using a float type which I'm not really ready to seriously contemplate.  Here are some references about those facts:

Is there any point in specifying the precision and scale of a number for a foreign key?
Oracle integer to number conversion
Oracle Datatypes Reference

Given that, it would seem that it's pointless to bother with declaring a limit on the size of the PK i.e. just use NUMBER (which apparently can hold 40 digits.)  The only answer that I've seen is that you should do it for consistency.  But for a surrogate PK, the only thing it needs to be consistent with is itself.  What I mean is, if we set a limit, it's going to be something larger than what we would possibly reach so it seems completely pointless.

One piece of this I'm not sure about is whether the declaration could matter for indexing.  I think it probably doesn't for the most common approaches but are there any indexing strategies that might take this limit into consideration or use more storage because the column is not limited?

Just use

my_key number not null

If you are generating the primary key using an Oracle sequence (PS: you should), you are guaranteed to get integers so you don't have to have Oracle checking precision, etc. if you specified number(7,0) to specify an integer with up to seven digits.

And then if you ever exceed 7 digits, not a fun day to fix that.  As the links you've referenced above mention that Oracle only uses enough space to store the number, no need to worry about how much storage is being used; and therefore you don't have to concern yourself with indexing of the columns holding the primary and foreign keys.

The only time I would specify a fixed length integer would be if the primary key was an intelligent key such as a USA social security number, but intelligent keys are a bad idea too.


Adding multiple foreign keys to single table I'm trying to add multiple foreign keys to the same table; however, the error code 1215: Cannot add foreign key constraint keeps popping up.  I've read other people's posts on this same topic and tried to add innodb, but the error is still popping up. Any help is appreciated!

Here's my code:

Create Database if not exists Research;
Use Research;

Create table if not exists CompanyInfo
(
CID int primary key, Company varchar(45),
SIC int, InnovativeIndex int
)
engine = innodb;

Create table if not exists TimePeriod
(
Year int primary key, GDP int, GDPgrowth int,
inflation decimal(5,2), unemployment decimal(5,2)
)
engine = innodb;

Create Table If not exists CompanyRank
(
CID int, Year int, IndexYN int, SPRank int, FortuneRank int,
primary key (CID, Year), index (CID), Index(year),
foreign key (CID) references CompanyInfo.CID,
foreign key (Year) references TimePeriod.Year
)
engine = innodb;

You have a syntax problem, try this :

Create Table If not exists CompanyRank
(
CID int, Year int, IndexYN int, SPRank int, FortuneRank int,
primary key (CID, Year), index (CID), Index(year),
foreign key (CID) references CompanyInfo (CID),
foreign key (Year) references TimePeriod (Year)
) engine = innodb;

the reference is "CompanyInfo (CID)", not "CompanyInfo.CID"


SQLCMD is not working in pre-deployment script in sql project I have sql project in visual studio and I created pre-deployment script

/*
 Pre-Deployment Script Template
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be executed before the build script.
 Use SQLCMD syntax to include a file in the pre-deployment script.
 Example:      :r .\myfile.sql
 Use SQLCMD syntax to reference a variable in the pre-deployment script.
 Example:      :setvar TableName MyTable
               SELECT * FROM [$(TableName)]
--------------------------------------------------------------------------------------
*/

:setvar TableName MyTable
SELECT * FROM [$(TableName)]

but Iam getting an error at :setvar line

Incorrect syntax near ':'.

what am I missing ?

Community wiki answer:

You need to switch to SQLCMD mode in Visual Studio via the menu SQL > Execution Settings.

You can also have all SQL files open in SQLCMD mode by default if you dig into Options > SQL Server Tools > Query Execution.


How do I prevent updates on an entire table (but allow inserts) in Mysql 5.5? I want to make the rows in a Mysql table immutable, once they are inserted I don't want it to be possible to change them.  How can I do this?

This question handles making individual rows immutable, but I'm hoping there is a better way to make all rows in a table immutable (ideally returning a suitable error message if any change is attempted).

You can revoke 'UPDATE' privilege for a particular table. Or you can write BEFORE UPDATE trigger that raises an error (using signals or issuing a statement that always fails).


Versioning entities in db with draft option, select query I have postgresql db where i keep data for jee application. User can edit those data without creating new version of entity(draft). During edit, entity is copied as draft(version set to -1) after editing user can save all changes as new version. My problem is to create proper sql query to get data by version instead of extract all of them and do it in application.

Table schema:

id(uniq), field_id,version,otherFields....

id is uniq value and automatically incremented. When user start editing new field is created with field_id=parent.id. So i can extract all versions of field using group by field_id

Case 1

I want to get current version which means max value of version field or version set to -1.

Case 2

I want to get specific version. It is not that easy because data in db are not changed if field was not edited(to save space). For example field a was created with version 1 and then changes was done on other fields. Current global version is 7 but field a still have one entry in db with version=1(this field was not edited). So to extract specific version different than current i need to get MAX(version) where version<=:selected_version

Example data:

id(uniq),field_id,version
1,1,1
3,1,7
55,1,-1
53,53,14

Entity with field_id=1 has 2 version and draft(user is editing it).

Entity with field_id=53 has one version and no draft.

I hope that my explanation of problem is understandable. Could you help me with those sql query? It could be two queries, one for actual version with draft and other for old versions.

Case 2 probably solution

I think that specific version i can extract using such query

select field_id, max(version) ver from field_mappings where version<4 and version!='-1' group by field_id

I don't know why i can't extract id. When i change query to

select *, max(version) from field_mappings where version<4 and version!='-1' group by field_id

Then i got error

ERROR: column "field_mappings.id" must appear in the GROUP BY clause or be used in an aggregate function
SQL: 42803

And still don't know how to add case for draft(max(version) or version=-1).

You can use the following, which relies on DISTINCT ON, and turns calculates version -1 as 9999999:

select DISTINCT ON (field_id) *
from field_mappings
where COALESCE(NULLIF(version, -1), 9999999) < 4
ORDER BY field_id, COALESCE(NULLIF(version, -1), 9999999) DESC

The query retrieves all the rows with unique fields (by field_id), prioritising the version you specify, or the draft one (which has its priority bumped to the top). If you want the draft or last version, then use < 10000000 instead of < 4.


Get all rows from table where JSON column contains a certain value I'm struggling to get data from a JSON column in my PostgreSQL database.
In our users table, we have a known_ips column, which is a JSON column, holding a flat array of IP addresses known for the given user, like this:

# select email, known_ips from users limit 3;
       email       |          known_ips
-------------------+-------------------------------
 user1@example.com | ["192.168.1.1","192.168.1.2"]
 user2@example.com | ["192.168.1.3"]
 user3@example.com | ["192.168.1.2"]
(3 rows)

What I'm trying to do is to select all the users that have an IP address from an array of IP addresses (in this example, either 192.168.1.1 or 192.168.1.2) in their known_ips column. So in this case, user1 and user3 have IP address .1 or .2, so I want to have them returned, but not user2, since I'm not looking for the .3 address.

I've tried the following queries, all to no avail:

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip in("192.168.1.1","192.168.1.2");
ERROR:  column "192.168.1.1" does not exist
LINE 1: ... json_array_elements(known_ips) as ip where ip in("192.168.1...
                                                             ^

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip in('192.168.1.1','192.168.1.2') limit 5;
ERROR:  invalid input syntax for type json
LINE 1: ... json_array_elements(known_ips) as ip where ip in('192.168.1...
                                                             ^
DETAIL:  Token "." is invalid.
CONTEXT:  JSON data, line 1: 192.168....

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip in(192.168.1.1,192.168.1.2) limit 5;
ERROR:  syntax error at or near ".168"
LINE 1: ...array_elements(known_ips) as ip where ip in(192.168.1.1,192...
                                                             ^

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip in("192.168.1.1","192.168.1.2");
ERROR:  column "192.168.1.1" does not exist
LINE 1: ... json_array_elements(known_ips) as ip where ip in("192.168.1...
                                                             ^

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip in(array["192.168.1.1","192.168.1.2"]);
ERROR:  column "192.168.1.1" does not exist
LINE 1: ...array_elements(known_ips) as ip where ip in(array["192.168.1...
                                                             ^

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip = any(array["192.168.1.1","192.168.1.2"]);
ERROR:  column "192.168.1.1" does not exist
LINE 1: ...ay_elements(known_ips) as ip where ip = any(array["192.168.1...
                                                             ^

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip in(unnest(array["192.168.1.1","192.168.1.2"]));
ERROR:  column "192.168.1.1" does not exist
LINE 1: ...lements(known_ips) as ip where ip in(unnest(array["192.168.1...
                                                             ^

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip = any(array["192.168.1.1","192.168.1.2"]);
ERROR:  column "192.168.1.1" does not exist
LINE 1: ...ay_elements(known_ips) as ip where ip = any(array["192.168.1...
                                                             ^

I'm feeling like I'm missing something obvious, but I just don't see it. Why does Postgres think that I'm defining columns instead of strings? How do I properly get the data?

Double quotation marks are name delimiters. They are reserved for delimiting names (column names, table names etc.) that contain non-standard characters or those that you want to explicitly make case-sensitive (because that is their effect in PostgreSQL, which is according to the standard, too).

So, that is why all attempts with "192.168.1.1" fail: PostgreSQL indeed interprets those as names (specifically column names in each of those contexts).

The one case without quotes fails simply because 192.168.1.1 is an invalid token sequence. Numbers and some other constants can be represented in PostgreSQL without quotation marks but the tokens you specify there cannot be interpreted either as a number or anything else.

Finally, the one where you are using single quotation marks around the IPs fails because PostgreSQL is trying to interpret those as JSON literals. Why? Because the ip column is of type json – that is the type of column values returned by json_array_elements.

So, in order for that second attempt of yours to succeed, you should first of all represent the IPs as valid JSON string items. That means you need to enclose them in double quotation marks and then in single quotation marks, like this:

where ip in ('"192.168.1.1"','"192.168.1.2"')

However, that will give you this error:

  operator does not exist: json = json

Your options are:

convert ip to text:

where ip::text in ('"192.168.1.1"','"192.168.1.2"')

convert ip to jsonb

where ip::jsonb in ('"192.168.1.1"','"192.168.1.2"')

Either should get you going.

Note, though, that filtering your data like that can give you duplicates in the output. The issue is, the json_array_elements function turns the specified json value into a row set, repeating the source row's columns for each transposed item. So, for your example the FROM clause effectively produces the following row set:

       email       |          known_ips            |      ip
-------------------+-------------------------------+---------------
 user1@example.com | ["192.168.1.1","192.168.1.2"] | "192.168.1.1"
 user1@example.com | ["192.168.1.1","192.168.1.2"] | "192.168.1.2"
 user2@example.com | ["192.168.1.3"]               | "192.168.1.3"
 user3@example.com | ["192.168.1.2"]               | "192.168.1.2"

Since for user1 each ip will match the IN predicate, you will get the corresponding email returned twice.

To resolve that, instead of this:

...
from users, json_array_elements(known_ips) as ip
where ip::jsonb in ('"192.168.1.1"','"192.168.1.2"')

you can do something like this:

...
from users
where exists
(
  select *
  from json_array_elements(known_ips) as ip
  where ip::jsonb in ('"192.168.1.1"','"192.168.1.2"')
)


Connect excel to ms-access front end database I have an access database split. It is part of an app working along with an excel add-in.

Question:

Within excel, should I address all query updates directly to the back-end version (in the shared network)?

Or should I connect to the front-end (local)?

As I understand splitting access prevents from data corruption in shared database (when users open same file). If I use a SQL command within excel and send it directly to the back-end, then I am using excel as the front-end (and that clears the issue, am I right?)

Many thanks in advance.

I would connect directly to the Access database back-end part. Why involve the front-end?

...As I understand splitting access prevents from data corruption in shared database (when users open same file). ...

This is not quite correct. Splitting an Access database ...

  ...Splitting a shared database can help improve its performance and reduce the chance of database file corruption.

Source: Split an Access database (Microsoft Office Website)

...If I use a SQL command within excel and send it directly to the back-end, then I am using excel as the front-end (and that clears the issue, am I right?)

Same as above. You are minimizing the risk when splitting an Access database into a front-end (form, queries, etc.) and back-end (data), but you are not eliminating the risk of data corruption.

Consider using a database system like Microsoft SQL Server, Oracle RDBMS, PostgreSQL or MySQL to further reduce the risks of data corruption.


Do I need to change the delimiter when creating a procedure using PHP MyAdmin? I know I need to change the delimiter when manually creating a MySql Procedure but I can't find evidence on how to do it when using the PHP MyAdmin new procedure form or is it even necessary to change it when using this form? Can anyone help?

Thanks in advance.

You don’t need to specify the DELIMITER via the Routines UI tool/form.


MySQL hidden files on ubuntu taking up space I have a weird issue where my system (ubuntu 19) is showing that MySql 8 is using up 600gb of space, when it should be closer to 250gb.

I ran ubuntu's baobab utility in sudo mode, and it as well, is showing that MySQL is using up 250GB .  Any idea what could be taking up the other 250GB?

I also ran this query via SQL, to show table sizes - same story ~250GB, so not sure what else I could try.

SELECT
     table_schema as `Database`,
     table_name AS `Table`,

     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;

.. I do have a few indexes set up, but most of them on columns containing 0/1s

Thank you in advance for suggestions.

looks like this command worked for temporary solution:
PURGE BINARY LOGS BEFORE '2019-10-19 22:46:26'; #your date

(mysql manual)
Still working on the long term solution. Per mysql manual - these were the logs of select statements (which are typically huge in my case). Not 100% yet on whether I need these :) In either case, thank you everyone for helping troubleshoot. This was blowing my mind for a bit, but all it took was browsing into the folder itself, and seeing hundreds of huge files!


How do I define the frequency of my mongo backup I want to know if there is a way to define in Mongo the frequency of backups have to be executed. There is any config file for that? An example will be excellent!!

UPDATE

I want to create a automated mongo configuration. With this configuration I want to define the process of backup. I want the backups process be executed based on my time frequency, for example every two minutes.

So I don't know if there is a possible configuration for that. I prefer a configuration file because I want to create an automated provisioning and with commands it becomes more hard to automate.

MongoDB doesn't have an automatic backup feature built-in -- you have to choose an appropriate backup strategy using command-line or third party tools.

The backup procedure will vary depending on your deployment type (standalone, replica set, sharded cluster), disk/dbpath configuration, and backup requirements.

Unless you are using a third party tool, it's best to follow a supported procedure from the MongoDB manual.


Need some insights on 'index_page' variables in Innodb_Metrics Table I was experimenting with page-split behaviour for sequencial vs random ordered inserts in Mysql,Innodb.

For sequencial Inserts I got:

index_page_splits:           3199
index_page_merge_attempts:   0
index_page_merge_successful: 0
index_page_reorg_attempts:   0
index_page_reorg_successful: 0

For random Inserts I got:

index_page_splits:           4423
index_page_merge_attempts:   1
index_page_merge_successful: 1
index_page_reorg_attempts:   1
index_page_reorg_successful: 1

I am having two questions:

1.Why there are increments in index_page_merge_attempts,index_page_merge_successful,
index_page_reorg_attempts,index_page_reorg_successful variables?

2.What is the difference between attempts and successfull variables?

How big are the rows?  I'm surprised that the "splits" numbers are similar.

"Merge" probably refers to what may happen after DELETEing a row or UPDATEing a row such that the row is smaller.  The block contains less.  If that shrunken block is next to another block that is also rather shrunken, then it behooves InnoDB to "merge" the two blocks together.  This won't happen if all you are doing is INSERTs.

"Attempts" vs "Successful" -- Suppose some row is locked.  Or something else is going on that would be disrupted by the "merge".  It would be better to abort the merge, than to mess up the 'real' operation.

What is the typical size of the TEXT column?  It could be stored in the row, or it could be stored elsewhere.  The "merges" could be happening in the off-record storage.


Does a materialize view refresh in its creation? I create a materialized view with the following cast function. It does not give me an error during creation although it exists an error. But refresh gives the error.

CREATE MATERIALIZED VIEW XXXX_ROW_MV
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
    CAST ('null' AS NUMBER)   currency_rate,
     CAST ('null' AS NUMBER)   conversion_factor;

Does this mean that materialized views compile until it is being refreshed?

refresh can be done by using following code

begin
  DBMS_MVIEW.refresh('XXXX_ROW_MV');

end;

The BUILD DEFERRED clause in the CREATE MATERIALIZED VIEW DDL statement causes the actual population to be deferred until the first refresh.

While Oracle parses the actual SQL used to populate the view, it does not execute it & will therefore not pick up "runtime" problems.

This is easily demonstrated.

Parsing error, due to NONSENSE not being a datatype:

SQL> CREATE MATERIALIZED VIEW PARSINGERROR
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
    CAST ('null' AS NONSENSE) as parsing_error
     from dual;

    CAST ('null' AS NONSENSE) as parsing_error
                    *
ERROR at line 6:
ORA-00902: invalid datatype

SQL>

Runtime error, that won't get picked up as it doesn't analyse the actual values involved, and is unaware that the included string isn't a number:

SQL> CREATE MATERIALIZED VIEW RUNTIMEERROR
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
    CAST ('notanumber' AS number) as runtime_error
     from dual;

Materialized view created.

SQL>

SQL> begin
  2  DBMS_MVIEW.refresh('RUNTIMEERROR');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748
ORA-06512: at line 2

SQL>


How to make UUID() function, statement-based replication safe? I'm trying the copy and generating UUID()s on the fly for exiting data when executing A insert from select.

How can I make the UUID() function statement-based replication safe?
Can I somehow use the old ID's from the primary table or at least save them for the entire operation? so the replica will have the same IDs?

INSERT INTO table_name (number,REPLACE(UUID(),………(SELECT ….));

which means primary and any its slave have inconsistent data for table_name, because of the UUID func.

Not sure I understand the question, but if you select the uuid from the parent, Whats the problem?

Insert into t (c1, c2,..., cn)
Select 36, 'abc', c3,... FROM parent


How to specify a task in a SQL Server maintenance plan conditionally? I have a weekly and nightly SQL Server Agent jobs executing a weekly and nightly maintenance plans, resp.

These weekly (on Sunday) and nightly (on other 6 nights of the week) maintenance plans differ only in the  1st task from total consecutive six steps. That is, in nightly/daily maintenance plan the 1st step is "Reorganize Indexes on all User databases" and in weekly/Sunday maintenance plan the first step is "Rebuild Indexes on All User Databases".

Is it possible and how to run just one (instead of two) maintenance plan, all 7 nights of week, with condition that on 6 days the 1st step is one (Reorganize Indexes) and on the rest 1 day of week the 1st step is another (Rebuild Indexes) ?

SQL Server 2012 R2 on Windows Server 2008 R2

I am not sure how dirty you want to get on this one but the only method is going to be opening up SQL Server Data Tools and building (or modifying) an SSIS package. One would be to import the maintenance plan into SQL Server 2012 Data Tools and modify the SSIS package. The other would be to create it from scratch. You have the same options as the maintenance plans when you create an SSIS package.

The one thing you want to add is an Expression Task, which is an awesome addition to SQL Server 2012. You could use the DATEPART SSIS expression to determine whether it was a weekday or weekend based on the system variable for package start time. Now I am not the best with expressions but this would be the one I would use:

DATEPART("dw",@[System::StartTime])

This will return a number between 1 and 7, with 1 = Sunday and 7 = Saturday. So from that task you would have two precedence constraints going to each path of your maintenance plan. If the value is 1 (one) then it goes to the rebuild index task, if it is anything else it goes to the reorganize task.

Although if I was going into this much detail I would stop using the index task with maintenance plans. I would suggest going to using T-SQL code that actually helps you maintenance the index fragmentation much more efficiently. I will say though I don't know if SQL Server has improved in 2012 with the index task. The most well known solution for index fragmentation management can be found here, Ola Hallengren.


How to find index details on Temporary tables in SYBASE I am a layman in SYBASE world.

In order to improve performance, I am creating indexes after data load of temporary tables in many stored procedures.

Say, one proc A  has an insert into temporary table X. so I have created index IDX after insert statement.

Proc B is invoking Proc A in a loop. In this case, index IDX on temporary table X will try to get created at each call. That’s where I get duplicate index has already create on table X with index name IDX.

To avoid that, I developed code piece(below) to check if the above IDX on temporary table A created or not. If not exists, I create index IDX on temporary table A. This will avoid duplicate index error when invoked proc A in a loop. It's not helping me.

I need to understand how to find if an indexes created on temp table pro-grammatically to restrict duplicate index error.

OPEN CUR_VAR_1
FETCH CUR_VAR_1 INTO @VARIABLE
WHILE (@@sqlstatus=0)
BEGIN
INSERT INTO #TABLE_X
SELECT * FROM TABLE Y

IF NOT EXISTS(SELECT 1 FROM sysindexes where  id=OBJECT_ID('#TABLE_X') and
name ='IDX')
BEGIN
CREATE INDEX IDX on #TABLE_X(COL_1)
END
End
CLOSE CUR_VAR_1

Error message:

  Msg 1913, Level 16, State 1: Server 'ABCDE', Procedure 'PROC_A', Line
  1155: There is already an index on table '#TABLE_X' named 'IDX'

A quick help is really appreciated as I have to complete this no later than 04/16/2018.

Assuming this is Sybase ASE ...

I would probably pull the create index out to a different place in your code, eg:

create/select-into <table>, create index, looping/insert, update statistics <table>, run queries

or

create/select-into <table>, looping/insert, create index, (optionally update statistics <table> - depends on your query requirements), run queries

If you insist on throwing the create index into the middle of your looping/insert construct (I wouldn't recommend this), consider ...

to query the system tables you need to know which tempdb your session is assigned to (assuming your ASE is configured with multiple tempdb's), and then query the system tables in that particular tempdb (eg, build a dynamic query)

or

you can use some of the built-in system functions to run some tests

Your current tempdb's database id can be found in @@tempdbid (or you can call tempdb_id()).  You can then call db_name(@@tempdbid) (or db_name(tempdb_id())) to get your tempdb name.  From here you can build your dynamic query against sysindexes. [Plenty of posts on dynamic ASE queries so I'm not going to cover that here.]

Instead of building a dynamic query I'd probably opt for a (relatively) simple function-based test, eg:

NOTE: Assuming no other indexes on the table ... the index id should be 1 or 2 ... depending on the table's locking scheme and the type of index (clustered/non-clustered).

if  index_name(@@tempdbid,object_id(#TABLE_X),1) is NULL
and index_name(@@tempdbid,object_id(#TABLE_X),2) is NULL
begin
    create index ...
end

Regardless of whether you build/run a dynamic query, or use the system functions to test for the existence of the index, I would highly recommend you re-think your code design.  Do you really, Really, REALLY want to add the extra overhead for a dynamic-query/function-call to every pass through your looping/insert construct?


Reporting services scheduled reports are clogging up sql job history log This is a bit of a cross-disciplinary question - Hopefully I’ve posted in the right place.

SQL server 2008 R2

Question:
How can I limit the number of rows in [sysjobhistory] for specific jobs, especially jobs inserted into SQL Agent from High Frequency Reporting Services Subscriptions?

History:
I have a SQL server that where the SQL Agent has to handle a mix of ETL jobs and jobs coming from reporting services schedules. Recently I am finding that I only have 2 or 3 days of history on the ETL jobs where I used to have 5-7 days or more.
 The SQL agent properties for Job History retention are:

Maximum Job History Size (in rows): 5000
Maximum Job History rows per job: 1000

If you were thinking “reporting services jobs are going to be the problem…” you would be right. We recently introduced some reporting services reports that are responsible for populating data for overhead displays, running at 3 min intervals all day. Of course these jobs are hitting their max rows per job (1000) in no time flat and leaving only 1000 rows for all the other jobs total.

I don’t want to change the SQL Agent Job History row limits – but I would like to have longer histories on the ETL jobs.

The Reporting services DB carries its own logs internally (ExecutionLog), so I don’t really need 600+ log entries per day per job in sysjobshistory for these High Frequency subscriptions.

The Jobs inserted into SQL Agent by Reporting Services scheduler specifically warn against modifying them outside of Report Manager..but as far as I can see the Report scheduler has no options for this.

Write your own job that clears out the entries (and its own!) every couple of minutes.

You do have the ability to clear out history details by simply issuing:

DELETE msdb.dbo.sysjobhistory WHERE run_status = 1 AND job_id = 'guid';


Count down from 0 to 5 seconds and repeat What query can I use to trigger a count down from 0 to 5 seconds whenever executed? When I execute a query the output has to be 0 second to 5 second and once 5 seconds are done again it should go back to 0. When I execute again it should start from 0 to 5 seconds. Is it possible to achieve something like this using sql query?

WaitFor should help you accomplish this.

In your case we can simply pass a DELAY time as below

WAITFOR DELAY '00:00:05';

This will simply wait for 5 seconds, then return to the caller.


Update fails on linked server, Select works I am trying to understand why my update statement on my local server is failing on my remote server. I am receiving the following error:

OLE DB provider "SQLNCLI11" for linked server "WEBDB" returned
message "Multiple-step OLE DB operation generated errors. Check each
OLE DB status value, if available. No work was done.". Msg 7341, Level
16, State 4, Line 1 Cannot get the current row value of column
"[SQLNCLI11].AdditionalBarcode" from OLE DB provider "SQLNCLI11" for
linked server "WEBDB". [COLUMN_NAME= AdditionalBarcode
STATUS=Unknown DBBINDSTATUS]

This is my update statement
Update WEBDB SET Additionalbarcode = UDF_ADDITIONALBARCODE FROM
openquery(WEBDB, ' SELECT Id ,[AdditionalBarcode] FROM
...[ProductGroup] where IsActive = 1') WEBDB inner
join LocalDB..Items CI on WEBDB.Id = CI.itemcode where
nullif(UDF_ADDITIONALBARCODE,'') <> isnull(Additionalbarcode,'')

This is my select statement
Select itemcode, UDF_AdditionalBarcode, Id, Additionalbarcode FROM
openquery([remoteserver], ' SELECT Id ,[AdditionalBarcode] FROM
...[ProductGroup] where IsActive = 1') WEBDB inner
join LocalDB..Items CI on WEBDB.Id = CI.itemcode where
nullif(UDF_ADDITIONALBARCODE,'') <> isnull(Additionalbarcode,'')

Why would I receive an error during UPDATE but not select? Any idea on what would be the cause of the error in the update statement?

I don't know exactly why that is failing, but there are probably better alternatives.
First is the general best-practice to avoid the propriatary and confusing UPDATE FROM:
Start with a working SELECT and then do
with q as
(
 Select itemcode, UDF_AdditionalBarcode, Id, Additionalbarcode FROM
 openquery([remoteserver], ' SELECT Id ,[AdditionalBarcode] FROM
 ...[ProductGroup] where IsActive = 1') WEBDB inner
 join LocalDB..Items CI on WEBDB.Id = CI.itemcode where
 nullif(UDF_ADDITIONALBARCODE,'') <> isnull(Additionalbarcode,'')
)
update q set Additionalbarcode = UDF_ADDITIONALBARCODE

But I would go farther and simply spool the OPENQUERY results locally first:
Select *
into #webdb
FROM openquery([remoteserver], ' SELECT Id ,[AdditionalBarcode] FROM
...[ProductGroup] where IsActive = 1')

When working across linked servers, being explicit about how you're moving data back and forth is usually best.


Optimizing query performance with aggregation tables and partitioning Our setup is a bit complex, so I will try to simplify what our tables look like but still try to describe the real problem:

We have a large table (items) with about 500 million rows (grows with ~1.5million rows per day). Where each item has a timestamp (among other things).
We have 5 metadata tables (based on the item type) with FKs to items. These have about 100 million rows each.

When doing queries on the items table, we need to filter and sort on values in the metadata tables. This is based on filter parameters in our UI, so we might filter on things from all 5 metadata tables, or from none of them. The query will look something like this:

SELECT
    i.id
FROM items i
LEFT JOIN itemmetadata1 im1 ON im1.itemid = i.id AND i.type = 1
LEFT JOIN itemmetadata2 im1 ON im2.itemid = i.id AND i.type = 2
LEFT JOIN itemmetadata3 im1 ON im3.itemid = i.id AND i.type = 3
LEFT JOIN itemmetadata4 im1 ON im4.itemid = i.id AND i.type = 4
LEFT JOIN itemmetadata5 im1 ON im5.itemid = i.id AND i.type = 5
WHERE
    i.timestamp > '2019-02-01'
    AND i.timestamp < '2019-02-07'
    -- These aren't always here, query is dynamically generated (based on user input)
    AND im1.somevalue = TRUE
    AND im3.anothervalue > 5
    ... etc
ORDER BY
    -- This can also change dynamically
    im5.value DESC

This makes this query quite slow for some cases. For example when looking at a long period of time, with few matching rows in the metadata tables.

So I have two questions:

1. Aggregation

Would it make sense to create a new denormalized table called aggregateditems that contains all of the item and metadata columns we need to filter and sort on? That way our query would be simplified like this:

SELECT
    i.id
FROM aggregateditems ai
WHERE
    ai.timestamp > '2019-02-01'
    AND ai.timestamp < '2019-02-07'
    -- These aren't always here, query is dynamically generated (based on user input)
    AND ai.metadata1somevalue = TRUE
    AND ai.metadata3anothervalue > 5
    ... etc
ORDER BY
    -- This can also change dynamically
    ai.metadata5value DESC

I guess this would speed things up considerably with the right indexes.

So my thought is to put triggers on items and the metadata tables, that will update aggregateditems when the data changes, is that a good idea? Or might this kill performance?

2. Partitioning

Creating an aggregated table will speed up queries I think, but we'd still have very large tables, with large indexes, so I guess we'd need to do some partitioning as well.

Let's say we partition items and aggregateditems on timestamp, how do we partion the metadata tables? (They don't have any timestamps).

I would recommend trying a single-table design.  There would be groups of columns which are only relevant to a single type, meaning they would be NULL for rows of any of the other types.  For the physical layout of the table, you would probably want the common columns first, then the frequently filtered-upon type-specific columns, then the infrequently/never filtered-upon type-specific columns last.

This will get rid of the problem of needing to do left joins.  It will also make it easy to partition on timestamps, as you no longer have the side tables which lack timestamps.  Hopefully the large text fields will get TOASTed aggressively, keeping the size of the main table down (but this is not something easy to control).

If the partitions with the most recent timestamps are the hottest ones, this will help keep their data and their indexes small enough to fit RAM cache, and hot enough to be naturally kept there.