Can arbiterOnly replica in MongoDB become SECONDARY and what it means?

Some time ago my one of my data replicas which was secondary at a time crushed due to hard drive failure. After I fixed that problem and restarted secondary it went into “Recovering” state. But my arbiter is now “Secondary”

A MongoDB arbiter cannot automatically become a secondary or a primary node, as it does not have a copy of the data set.

If you try to manually reconfigure the arbiter as a regular node via rs.reconfig() you should get an exception similar to:

{
    "errmsg" : "exception: arbiterOnly may not change for members",
    "code" : 13510,
    "ok" : 0
}

  Furthermore, the data directory for this Arbiter shows bunch of files of total size >10GB. They indeed look to me like data files. Are they? What is going to happen to these files when Recovery completes?

Assuming this node is definitely an arbiter,
I would expect those files are unused (check the timestamps?) and are either:

a local database created if this node was incorrectly initialized with an oplog
unused copy of data directory if this node was copied from another secondary or used as a standalone before

You can always log into the arbiter mongod directly to see what data it appears to have.


Can a production Oracle DB server also have a dataguard active standby for a different production database?

Yes, no issue at all. This is done over and over. For instance two servers taking each others database(s) in standby mode.


Microsoft Access - performance difference between index and constraint?

Constraints and indexes are two entirely different things.  A constraint defines what can go in the column and an index creates an internal structure to help performance.

-- (1)
create table TestTable (
  ID autoincrement primary key
, F int not null
);

This is part of the table/column definition.  Probably the easiest and most common way to restrict a column from null values.

or:

-- (2)
create table TestTable (
  ID autoincrement primary key
, F int
, constraint cnnF not null (F)
);

This is a constraint.  This will also work but it's really meant for things like making sure a char(1) column only has a Y or an N in it.

or:

-- (3)
create table TestTable (
  ID autoincrement primary key
, F int
);
create index idxF on TestTable (F) with disallow null;

This is an index.  As a side effect it is disallowing null but really it's purpose is is to speed up your queries.

I hate to say it but lazy isn't going to work.  I would put null/not null definitions into the column definition.  Then use constraints on columns where you want to restrict what values go into the column, not if it's null or not.  Then last but not least add indexes where needed for query performance.


The equivalent of Common Table Expressions in MySQL

Onetime action:  Build a table with lots of dates-- a la DatesTable.  (This can be done with a cross join to help generate lots of numbers, then use + INTERVAL n DAY for turning into dates.)

Then LEFT JOIN (with a WHERE clause) from the DatesTable to the other table(s).

Note that you will need to rewrite most date operations because of different syntax.

With MariaDB, you can use a pseudo table like seq_1_to_1000 to get a bunch of numbers.  Also, MariaDB has CTEs.  (MySQL will be getting CTEs sometime in version 8.0.)


In regards to GS1 barcode - what size DB field are used?

As the maximum number of characters in a single symbol is 48 and the Technical Spec for aggregators (http://www.gs1.org/sites/default/files/docs/gs1-source/TSD_v1_1_Technical_Implementation_Guide_for_Aggregators.pdf) indicates the file can come in as UTF-8. UTF-16, or UTF-32, the safest bet would be

nvarchar(48)


Unique index corrupted SQL. Select query returns single row but create unique index fails

if you consider error, Msg 1505, Level 16, State 1, Line 2 The CREATE
  UNIQUE INDEX statement terminated because a duplicate key was found
  for the object name 'dbo.MSmerge_contents' and the index name
  'uc1SycContents'. The duplicate key value is (7696031,
  08703987-557d-e111-9888-e61f13c44f03)...... I am running below Query
     select * from msmerge_contents where rowguid='08703987-557d-e111-9888-e61f13c44f03'
  and it is returning only 1 row

When you have index corruption problems (ie. keys present in NC index but not in base table or vice-versa) you must be very careful about the SQL you use to validate data. At this moment your data is inconsistent but the query optimizer does not know that and completely trusts your schema, including these incorrect indexes. As such it may optimize your query to use one of the NC indexes that is missing a key and the result will also miss a a key falsely returning no duplicates. To solve this catch-22 situation you need to force the optimizer hand by explicitly requesting an index or another and make sure the projected list of columns can be satisfied by the index you enforced (ie. no *). Assuming uc1SycContents is not the clustered index, try out the following:

select rowguid
from msmerge_contents with INDEX (1)
where rowguid='08703987-557d-e111-9888-e61f13c44f03';

select rowguid
from msmerge_contents with INDEX ([uc1SycContents])
where rowguid='08703987-557d-e111-9888-e61f13c44f03';

This will forcefully check if the rowguid has a duplicate for that guid in the base table clustered index (index id 1) vs. the index uc1SycContents. I expect that the first query returns 2 (or more) rows while the second returns 1.


Well this answer depends on how your database is designed and licensed.

If you have an Enterprise license, you should take a look at partitioning. This way you may partition your data which will help you in querying those table. If you make a good partitioning, you'll be able to just read a small portion of the table from the disc, instead of querying everything.

I don't know how your application writes to your database. But maybe it's possible to flag the first record of an hour so that you don't need to filter it later on that way?

Another idea is a SQL Agent, which will perform those aggregations in the background for you. Maybe this could be hourly and write all aggregated values to another cumulative table, which could be queried by you even faster.


The MSDN Page Configure Windows Service Accounts and Permissions has some detailed recommendations for the SQL Server 2014 service accounts.

The subheading for configuring the services is at Service Permissions

This is quite a detailed outline of recommendations for the SQL Server Service Accounts.  These are good recommendations, however you can make your own decisions on configuration, based on your needs for security and for being able to manage the configuration.

Truth in reading: Although we use many of these settings, we also have some variations that suit us.


InnoDB log sequence number is in the future

The only correct solution is to reinstall and restore from a backup.  You say you don't have a backup, but the point here is that the idea behind forcing InnoDB recovery is that you can potentially get the server stable enough that you can make a backup.  Then use it to restore on a fresh setup.

Obviously, make a copy of all the files before doing anything.

Unless there's a memo I didn't get, you don't want to try increasing the log sequence number to pacify InnoDB, because even if you manage to do it, you've still got a server with data corruption in unknown places -- fix the LSN and all you've really accomplished is made InnoDB unaware of the corruption.  Forcing the LSN is pretty much a fool's errand.

Have you tried anything from...

http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

The different levels, 1 through 6, cumulatively disable parts of InnoDB with the idea that if you disable enough functionality you might be able to get a successful mysqldump or at least SELECT ... INTO OUTFILE from the tables.

The idea is to use the lowest number that allows you to successfully dump your data, but if this error is occurring at startup like I think it may be, you might just want to go ahead and start with 6.  If the server starts up, then try selecting from tables.  It may crash again and if it does, then write that table off for the moment and move to the next table.

Once you've retrieved the data from the tables that aren't corrupt, you may be able to go back to the ones that don't work, and try to retrieve the data in small chucks with SELECT ... LIMIT ... OFFSET.

Take a look at my answer to this similar question for some additional ideas.

You may also want to look at Percona's InnoDB data recovery tool as a potential resource, though I've never (fingers crossed) been caught without a backup or a running replication slave, so I've never needed it.


MySQL: Incorrect binary log format - OFF.000

I have seen your problem before

Problem #1

You have /etc/mysql/conf.d/mycustom.cnf and the log-bin line is not being read

Problem #2

Chances are /etc/mysql/conf.d/my.cnf exists and has the following line

[mysqld]
log-bin=OFF

That's why the binlogs start with a filename of OFF

Some feel log-bin=OFF disables binlogging. The MySQL Documentation on log-bin says the permitted value is a filename. Thus, it is not boolean. Simply removing the line or commenting it out disables binlogging.

Suggestion #1

You may want to start mysqld manually using

service mysql stop
mysqld --defaults-file="/etc/mysql/conf.d/mycustom.cnf"

Suggestion #2

Just change log-bin in /etc/mysql/conf.d/my.cnf

Then service mysql restart

Epilogue

Don't restart mysql just yet. This problem is one the Master.

STEP 01 : Stop all writes to the Master

STEP 02 : Go to the Slave and run STOP SLAVE;

STEP 03 : Restart mysql on the Master

STEP 04 : On the Slave, run this

CHANGE MASTER TO master_log_file='acct-db1-bin.000001',master_log_pos = 4;
START SLAVE;

STEP 05 : Start up all writes to the Master

This should complete your maintenance


postgresql has more DB connection in staging servers than production server

This is a little hard to guess at because it might be something unique to your staging setup, but here are some things to investigate:

Do you have either multiple staging environments that share the same database?
Do your developers attach to the staging database at all in the course of their work? Every open client is going to use database connections.

Many clients will identify themselves, and you query for current clients with this SQL taken from this posting:

SELECT *
FROM pg_stat_activity
WHERE datname = 'mydatabasename'

If you compare the results you might find the culprit.


Wild Guess only: Use quality_ + time_ instead time_ + quality_ and include (value_) ?


SELECT  date(lights_1.readingtime),
        max(lights_1.energy)-min(lights_1.energy)as total_1,
        max(lights_2.energy)-min(lights_2.energy)as total_2
FROM lights_1
join lights_2 on lights_1.readingtime = lights_2.readingtime
WHERE lights_1.readingtime between 20140407000000 and 20140409235959
GROUP BY date(lights_1.readingtime) LIMIT 0,30


MongoDB Sharding : What should be the Sharding key if we want to find records with more than one field

From what you share the url seems the best candidate for shard key. Has good cardinality, its not monotonically increased  (since is url), does not modified and don't contain null values.

Additionally your reads contain the url will directed to only one shard, which is the best scenario.

We can discuss that further on chat after Xmas, because sharding isn't always easy task.

Antonis


Replication Monitor reports little to no latency, but my tracer token is taking hours to go from Distributor to Subscriber

For my money, tracer tokens are a really good way to monitor latency. I don't know what the latency metric in replication monitor is measuring, but I've personally found it to be unreliable. For my environment, I post a tracer token through a SQL Agent job and then monitor the token's progress by looking at the token tables in the distribution database.


Disable CertificateRequest in ServerHello in MySQL/MariaDB

You could revoke SSL from the user in one of two ways:

METHOD #1 : Recreate User without REQUIRE SSL

DROP USER IF EXISTS 'username'@'%';
CREATE USER 'username'@'%' IDENTIFIED BY 'passowrd';
GRANT ALL ON db_name.* TO 'username'@'%';

METHOD #2 : Hack mysql.user

UPDATE mysql.user SET ssl_type='' WHERE user='username' AND host='%';
FLUSH PRIVILEGES;

CAVEAT

Check release notes on MariaDB concerning METHOD #2.

I think there is a bug doing FLUSH PRIVILEGES; in older versions.

GIVE IT A TRY !!!


According to the Oracle documentation here (see heading Setting the PASSWORD_LIFE_TIME Profile Parameter to a Low Value) the account status is changed from OPEN to EXPIRED(GRACE) if the user is currently logged in when you change PASSWORD_LIFE_TIME to a low value. I guess you were logged in as system when you changed the PASSWORD_LIFE_TIME. Try logging in as CUSUSER and then changing it.

The following method will set a user's account status to EXPIRED(GRACE):

create profile EXPTESTPROF limit
failed_login_attempts             1
password_life_time                1/24/60/60
password_reuse_time               unlimited
password_reuse_max                1
password_verify_function          null
password_lock_time                1
password_grace_time               1;

create user EXPTEST identified by EXPTEST;

alter user EXPTEST profile EXPTESTPROF;

grant "CONNECT" to EXPTEST;

Then login as EXPTEST. You'll get the message that the password will expire within 1 day. If you check the ACCOUNT_STATUS now it should say EXPIRED(GRACE):

select ACCOUNT_STATUS
  from DBA_USERS
 where USERNAME = 'EXPTEST'


Query JSON in Azure SQL DB to get one array element per row

Well, I figured out an answer that gives me the result I want, but it looks bad and I assume it is inefficient. The Estimated Subtree Cost of the plan that is generated is 976507. Here is the query.

SELECT DISTINCT
    ArrayValues.ArrayKey
  , ArrayValues.ArrayElement
FROM
(
    SELECT
           [ON].OrderNumber
         , [S].Site
         , [LN].LineNumber
         , [IN].ItemNumber
         , [Q].Quantity
    FROM OPENJSON(@JsonParameter)
        WITH (
             [OrderNumber]      NVARCHAR(MAX)   '$.OrderNumber'     AS JSON
            ,[Site]             NVARCHAR(MAX)   '$.Site'            AS JSON
            ,[LineNumber]       NVARCHAR(MAX)   '$.LineNumber'      AS JSON
            ,[ItemNumber]       NVARCHAR(MAX)   '$.ItemNumber'      AS JSON
            ,[Quantity]         NVARCHAR(MAX)   '$.Quantity'        AS JSON
        ) AS jsonValues
    CROSS APPLY OPENJSON(jsonValues.OrderNumber)
        WITH (OrderNumber NVARCHAR(31) '$') AS [ON]
    CROSS APPLY OPENJSON(jsonValues.Site)
        WITH (Site NVARCHAR(31) '$') AS [S]
    CROSS APPLY OPENJSON(jsonValues.LineNumber)
        WITH (LineNumber NVARCHAR(31) '$') AS [LN]
    CROSS APPLY OPENJSON(jsonValues.ItemNumber)
        WITH (ItemNumber NVARCHAR(31) '$') AS [IN]
    CROSS APPLY OPENJSON(jsonValues.Quantity)
        WITH (Quantity NVARCHAR(31) '$') AS [Q]
) ExpandedJson
UNPIVOT
(
    ArrayElement FOR ArrayKey IN (OrderNumber, Site, LineNumber, ItemNumber, Quantity)
) AS ArrayValues

And here is the result.

ArrayKey       |ArrayElement
---------------|-------------
Quantity       |23
ItemNumber     |BX12345
Site           |New York
LineNumber     |1000
LineNumber     |2000
ItemNumber     |BX12349
Quantity       |15
OrderNumber    |OD12034


The CHECK clause is parsed but ignored by all storage engines.

http://dev.mysql.com/doc/refman/5.7/en/create-table.html
Use trigger instead.


Split 100 million row table into multiple tables

Yes, it is possible to split this 100 million row table into any number of tables, even without it having any indexes. Now, you might could do it purely in T-SQL but that would require loading each of the split tables via individual insert statements in a transaction, and that can be a bit heavy on the tran log, especially if splitting to tables of 2 million rows each.
But, thanks to the totally awesome awesomeness of the fully streamed API for Table-Valued Parameters (TVPs), a small (and relatively simple) C# / VB.Net (or any .Net language) app can handle this, and do so without using more than 1 row's worth of memory (on the app side; buffer pool on the DB is what it is), and do so using a minimally-logged operation! The trick is to:

call a stored procedure that accepts a TVP for the rows to move, and
pass the rows into the TVP by setting the SqlParameter value to a method that returns IEnumerable<SqlDataRecord>. This will allow for incrementally moving through a SqlDataReader that is reading the source records.
Using this method, the size of the source table shouldn't matter: splitting a 1 billion row table should only require more time (and the obvious disk space required to duplicate the table).

Database Objects

First: You need a User-Defined Table Type

CREATE TYPE dbo.SplitTable AS TABLE
(
   CreateDate DATETIME NOT NULL,
   StringField01 VARCHAR(500) NULL,
   StringField02 VARCHAR(500) NULL,
   ...
   StringField10 VARCHAR(500) NULL,
   StringField11 VARCHAR(500) NULL
);
GO

GRANT EXECUTE ON TYPE::[dbo].[SplitTable] TO [user_or_role];
GO

Second: Use the UDTT as an input param to an import proc. Hence "Tabled-Valued Parameter" (TVP)

-- DROP PROCEDURE dbo.ImportChunk
CREATE PROCEDURE dbo.ImportChunk (
   @TableNumber   INT,
   @SplitTable    dbo.SplitTable READONLY
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX),
        @CurrentTableName sysname;

BEGIN TRY

   -- Empty TVPs are not allowed, and can't send DbNull.Value when using
   -- IEnumerable<SqlDataRecord>, so send in a single-row with a field to test that would
   -- otherwise never have that value. Only need to test 1 row as real sets of rows
   -- won't have this value so no need to test the whole set.
   IF ((SELECT TOP (1) [CreateDate] FROM @SplitTable) = CONVERT(DATETIME, 0))
   BEGIN
      RETURN;
   END;

   BEGIN TRAN;

   SET @CurrentTableName = N'dbo.SplitTableName_'
                           + RIGHT(N'000' + CONVERT(NVARCHAR(20), @TableNumber), 4);
   PRINT 'CurrentTableName: ' + @CurrentTableName;

   SET @SQL = N'CREATE TABLE '
              + @CurrentTableName
              + N' (
CreateDate DATETIME NOT NULL,
StringField01 VARCHAR(500) NULL,
StringField02 VARCHAR(500) NULL,
...
StringField10 VARCHAR(500) NULL,
StringField11 VARCHAR(500) NULL
);';

   EXEC(@SQL);

   -- Use a SYNONYM as we are dealing with dynamic table names which requires Dynamic SQL
   -- but the TVP is a table variable and cannot be referenced in Dynamic SQL. However,
   -- a SYNONYM can be created in Dynamic SQL and is a consistent object name that can
   -- be used in the static / main portion of this code where the TVP can be used.
   -- http://msdn.microsoft.com/en-us/library/ms177544.aspx (CREATE SYNONYM)
   SET @SQL = N'CREATE SYNONYM CurrentImportTable FOR '
              + @CurrentTableName;

   EXEC(@SQL);

   -- The WITH (TABLOCK) hint is required for this query to be considered minimally logged.
   -- http://msdn.microsoft.com/en-us/library/ms174335.aspx (INSERT)
   INSERT INTO CurrentImportTable WITH (TABLOCK)
      (CreateDate, StringField01, StringField02, --...,
      StringField10, StringField11)
      SELECT tmp.CreateDate,
             tmp.StringField01,
             tmp.StringField02,
             ...
             tmp.StringField10,
             tmp.StringField11
      FROM   @SplitTable tmp;

   DROP SYNONYM CurrentImportTable; -- Clean up.

   COMMIT TRAN;

END TRY
BEGIN CATCH

   IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK;
   END;

   DECLARE @Message NVARCHAR(4000);
   SET @Message = ERROR_MESSAGE();
   RAISERROR(@Message, 16, 1);

END CATCH;
GO

GRANT EXECUTE ON dbo.ImportData TO [user_or_role];

C# Code

Third: Open a SqlDataReader to a simple select on the table in question (essentially a cursor). The trick is to implement a method that will

read a set number of rows rather than until the end of the result set
pass each row, as it is read, to the TVP

Then just call the stored procedure shown above passing in the loop counter (so we can name the new table appropriately) and the method that returns only that set number of rows via IEnumerable. Keep calling the procedure until there are no more records in the result set / SqlDataReader.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;

namespace TableSplitter
{
    class Program
    {
        // these first two variables are static as their values need to be passed back to
        // the main loop but iterators (i.e. returning IEnumerable) cannot have "ref" parameters
        private static SqlDataReader _RowsToSend;
        private static int _RowsCopied;

        private static int _RowsPerSplit;

        private static IEnumerable<SqlDataRecord> SendRows()
        {
            SqlMetaData[] _TvpSchema = new SqlMetaData[]
            {
                new SqlMetaData("CreateDate", SqlDbType.DateTime),
                new SqlMetaData("StringField01", SqlDbType.VarChar, 500),
                new SqlMetaData("StringField02", SqlDbType.VarChar, 500),
                //...
                new SqlMetaData("StringField10", SqlDbType.VarChar, 500),
                new SqlMetaData("StringField11", SqlDbType.VarChar, 500)
            };

            SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);
            object[] _AllFields = new object[12];

            // read a row, send a row
            for (_RowsCopied = 0; _RowsCopied < _RowsPerSplit; _RowsCopied++)
            {
                if (!_RowsToSend.Read())
                {
                    break;
                }
                _RowsToSend.GetValues(_AllFields);
                _DataRecord.SetValues(_AllFields);

                yield return _DataRecord;
            }

            if (_RowsCopied == 0)
            {
                _DataRecord.SetDateTime(0, new DateTime(1900, 1, 1));
                yield return _DataRecord;
            }
        }

        public static void SplitTable(string ConnectionString)
        {
            _RowsCopied = _RowsPerSplit; // seed value to enter the loop

            int _TableNumber = 1;

            SqlConnection _SourceConnection = new SqlConnection(ConnectionString);
            SqlCommand _SourceCommand = new SqlCommand(
                "ALTER DATABASE [Test] SET RECOVERY BULK_LOGGED;",
                _SourceConnection);
            _SourceCommand.CommandType = CommandType.Text;

            SqlConnection _DestinationConnection = new SqlConnection(ConnectionString);
            SqlCommand _DestinationCommand = new SqlCommand(
                "dbo.ImportChunk",
                _DestinationConnection);
            _DestinationCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter _TVParam = new SqlParameter();
            _TVParam.ParameterName = "@SplitTable";
            _TVParam.SqlDbType = SqlDbType.Structured;
            _DestinationCommand.Parameters.Add(_TVParam);

            SqlParameter _TableNumParam = new SqlParameter();
            _TableNumParam.ParameterName = "@TableNumber";
            _TableNumParam.SqlDbType = SqlDbType.Int;
            _DestinationCommand.Parameters.Add(_TableNumParam);

            try
            {
                _SourceConnection.Open();
                _SourceCommand.ExecuteNonQuery();

                _SourceCommand.CommandText =
                    @"SELECT CreateDate, StringField01, StringField02, --...,
                    StringField10, StringField11 FROM dbo.TableToSplit;";
                _RowsToSend = _SourceCommand.ExecuteReader();

                _DestinationConnection.Open();

                while (_RowsCopied == _RowsPerSplit)
                {
                    _TableNumParam.Value = _TableNumber;
                    _TVParam.Value = SendRows(); // method return value is streamed data

                    _DestinationCommand.ExecuteNonQuery();

                    _TableNumber++;
                }
            }
            catch (Exception _Exception)
            {
                System.Console.WriteLine("\n\n" + _Exception.Message + "\n\n");
            }
            finally
            {
                _RowsToSend.Close();
                _DestinationConnection.Close();

                _SourceCommand.CommandText =
                    "ALTER DATABASE [Test] SET RECOVERY SIMPLE;"; // or FULL
                _SourceCommand.ExecuteNonQuery();

                _SourceConnection.Close();
            }

            return;
        }

        static void Main(string[] args)
        {
            System.Diagnostics.Stopwatch _ElapsedTime = new System.Diagnostics.Stopwatch();
            _ElapsedTime.Start();

            _RowsPerSplit = 1000000;

            SplitTable("Trusted_Connection = yes; Database = Test;");

            _ElapsedTime.Stop();

            System.Console.WriteLine("\n\n\tElapsed Millseconds: {0}\n\n",
                                      _ElapsedTime.ElapsedMilliseconds);
        }
    }
}

I ran this on my laptop with a sample table consisting of:

5 fields: 1 DATETIME and the other 4 are VARCHAR
3 million rows
205 GB

I ran it several times and it took about 19 - 24 seconds to split into 3 tables of 1 million rows each.


SQL Log Shipping - What is the date time format used by transaction log file name?

What date time format is the transaction log expressed

Yes, it is UTC time with the format dbname_YYYYMMDDHHMMSS (time is backup start time in 24 hours format). I think, following screenshot (copied from..) can help for easy validation.
Left side: Highlighted is Log Shipping backup name with UTC datetime
Right side: Date modified column indicating the time in +4 time-zone


Using a Different Windows Account for Invoke-Sqlcmd

You could grant the account that is going to run the job the necessary permissions onto the SQL Server to run the query.  That would be the easiest.  If you are going to use Task Scheduler, it even gives a method for specifying the account to use.

The next best would be to use SQL Authentication.

Finally, you can use PowerShell to invoke a new thread to run under a different windows account, that thread then to use Invoke-sql.  However, the password then needs to be stored somewhere somehow (usually encrypted in a file).  This is not the best solution and not one that I would recommend using.


Nullable columns or jsonb for storing user profile?

You can use "best of both worlds". Implement every regular column you know of as dedicated column (with exact proper data type, possibly with NOT NULL / CHECK / PK / FK etc. constraints to enforce detailed rules, default values and comment for documentation) and add a "catch-all" jsonb column for very rare cases / later additions / highly volatile attributes that do not need any of the features mentioned above.

To be clear: "highly volatile" does not mean the value is updated a lot, which would clearly speak against any document type like jsonb, making updates more complex and expensive. It means the attribute per se (on the meta level) changes a lot (different names, rare and and pops in and out of existence over time or similar).

Most people underestimate how cheap NULL storage really is:

Do nullable columns occupy additional space in PostgreSQL?
Measure the size of a PostgreSQL table row

Related:

Does it make sense to store a couple of Boolean values as array?
Is there a name for this database structure?
jsonb and primary/foreign keys: which performs better in PostgreSQL?
Does JSONB make PostgreSQL arrays useless?


How do I generate a time series in PostgreSQL?

To optimize:

SELECT x::time
FROM   generate_series(timestamp '2000-01-01 00:00'
                     , timestamp '2000-01-02 00:00'
                     , interval  '5 min') t(x);

The date is irrelevant, so use arbitrary timestamp constants. The cast to time is very cheap.
This includes lower and upper bound, so we get '00:00' twice. Use '2000-01-01 23:59' as upper bound to get it once only.

Related:

Generating time series between two dates in PostgreSQL


How to get full query text from performance_schema.threads?

Digging more into documentation:
The Performance Schema is intended to provide access to
useful information about server execution while having minimal
impact on server performance

It looks safe to use performance_schema.events_statement_current for monitoring currently running queries and events_statement_current.SQL_TEXT gives full query text as long as it fits performance_schema_max_sql_text_length bytes.


Query performance with primary keys of different data types

i would not see this as a major issue. of course things are a little larger but if we talk about a single integer column things will most likely be lost in noise anyway. we got 24 bytes of tuple header ... then comes the data. if things are 4 bytes larger? what difference does it make in a 10 column table.

so, no worries ... make sure that you do what is good for your app.


Framework to effectively identify blocking queries

I'd suggest looking for long-running sessions, using an XEvents session.

The problem you're describing sounds like you have client code that is performing row-by-agonizing-row (RBAR) processing instead of using efficient set-based approaches.

Poorly designed client applications may do something like this:

Connect to SQL Server to get a list of items that need processing.  The query result is held open until all rows have been processed.
perform some long-running process on each row.
Close the query.

What should happen is:

Connect to SQL Server
Run a query, caching the results of the query locally, and closing the connection.
Run the long-running process against each row without keeping the original query open, thereby preventing blocking.

A workaround for SQL Server can be implemented using snapshot isolation row versioning.  See this Technet document for details.  Essentially, row-versioning allows writers to not block readers, vastly reducing blocking.


Update MySQL slave when changing the hostname/IP of master

Once you change the MASTER_HOST, everything else needs to change with it.

The "dark back-alley" approach you just suggested is exactly what to do.

Warning: Some systems use "mysqld" versus "mysql".
The --skip-slave-restart "option" may not available without editing your
/etc/init.d/mysqld script to include this as a new option.
If used, it will still start the slave upon startup.</p>

On the Slave, run these four lines:

cd /var/lib/mysql
service mysql stop
cp master.info master.info.bak
wc -l < master.info

The last line echoes the number of lines in master.info

Next, vi master.info and change the IP address.

Next, run wc -l < master.info and make sure the linecount is still the same

Next, start mysql without starting replication

service mysql start --skip-slave-start

Login to MySQL, and run

SHOW SLAVE STATUS\G

If the IP address you added appears, then run

START SLAVE; SELECT SLEEP(5);
SHOW SLAVE STATUS\G

If you see Slave_IO_Running : Yes and Slave_SQL_Running : Yes, Congratulations !!!

Give it a Try !!!


Using WITH TIES to retrieve multiple rows with same value

This query returns the maximum price for each type:

SELECT itemtype,
       max(price)
FROM clothes_catalog
GROUP BY itemtype;

To get all items with that price, join these results back to the original table:

SELECT itemtype,
       catalogID,
       price
FROM clothes_catalog
JOIN (SELECT itemtype,
             max(price) AS price
      FROM clothes_catalog
      GROUP BY itemtype
     )
     USING (itemtype, price)
ORDER BY catalogID;


Get base table from materialized view in postgresql

I think something like this should do it:

SELECT DISTINCT
  pg_class.oid::regclass
FROM pg_rewrite
JOIN pg_depend ON
  pg_depend.classid = 'pg_rewrite'::regclass AND
  pg_depend.objid = pg_rewrite.oid AND
  pg_depend.refclassid = 'pg_class'::regclass AND
  pg_depend.refobjid <> pg_rewrite.ev_class
JOIN pg_class ON
  pg_class.oid = pg_depend.refobjid AND
  pg_class.relkind IN ('r','f','p','v','m')
WHERE
  pg_rewrite.ev_class = 'rental_by_category'::regclass

This should show any tables or views which are directly referenced by the view. If your view references other views, and you want to drill down to the base tables, you would need to apply this logic recursively (probably with a RECURSIVE CTE).

pg_class.relkind IN ('r','f','p','v','m') covers all of the table and view types as of Postgres 11, but the list may change in future releases. The possible values are covered in the docs.

Many different kinds of dependencies are stored in pg_depend, and not all of them are relevant to your question. For example, the view's pg_rewrite entry has a dependency on its own pg_class entry; the pg_depend.refobjid <> pg_rewrite.ev_class constraint is there to filter this out. I may well have overlooked something else which ought to be excluded, so proceed with caution.


Oracle: finding rows without children in a table with a foreign key?

A semantically appropriate way of expressing this would be:

select ...
from   a
where  not exists (
         select null
         from   b
         where  b.x = a.x)

NOT EXISTS uses an anti-semi-join which allows the optimiser to be very responsive to the table and join cardinalities.


Why does NOLOCK make a scan with variable assignment slower?

NOTE: this might not be the type of answer you're looking for. But perhaps it will be helpful to other potential answerers as far as providing clues as where to start looking
When I run these queries under ETW tracing (using PerfView), I get the following results:
Plain  - 608 ms
NOLOCK - 659 ms

So the difference is 51ms.  This is pretty dead on with your difference (~50ms).  My numbers are slightly higher overall because of the profiler sampling overhead.
Finding the difference
Here's a side-by-side comparison showing that the 51ms difference is in the FetchNextRow method in sqlmin.dll:

Plain select is on the left at 332 ms, while the nolock version is on the right at 383 (51ms longer).  You can also see that the two code paths differ in this way:

Plain SELECT

sqlmin!RowsetNewSS::FetchNextRow calls

sqlmin!IndexDataSetSession::GetNextRowValuesInternal

Using NOLOCK

 sqlmin!RowsetNewSS::FetchNextRow calls

sqlmin!DatasetSession::GetNextRowValuesNoLock which calls either

sqlmin!IndexDataSetSession::GetNextRowValuesInternal or
kernel32!TlsGetValue

This shows that there is some branching in the FetchNextRow method based on the isolation level / nolock hint.
Why does the NOLOCK branch take longer?
The nolock branch  actually spends less time calling into GetNextRowValuesInternal (25ms less).  But the code directly in GetNextRowValuesNoLock (not including methods it calls AKA the "Exc" column) runs for 63ms - which is the majority of the difference (63 - 25 = 38ms net increase in CPU time).
So what's the other 13ms (51ms total - 38ms accounted for so far) of overhead in FetchNextRow?
Interface dispatch
I thought this was more of a curiosity than anything, but the nolock version appears to incur some interface dispatch overhead by calling into the Windows API method kernel32!TlsGetValue via kernel32!TlsGetValueStub - a total of 17ms.  The plain select appears to not go through the interface, so it never hits the stub, and only spends 6ms on TlsGetValue (a difference of 11ms).  You can see this above in the first screenshot.
I should probably run this trace again with more iterations of the query, I think there are some small things, like hardware interrupts, that were not picked up by the 1ms sample rate of PerfView

Outside of that method, I noticed another small difference that causes the nolock version to run slower:
Releasing Locks
The nolock branch appears to more aggressively run the  sqlmin!RowsetNewSS::ReleaseRows method, which you can see in this screenshot:

The plain select is on top, at 12ms, while the nolock version is on the bottom at 26ms (14ms longer).  You can also see in the "When" column that the code was executed more frequently during the sample.  This may be an implementation detail of nolock, but it seems to introduce quite a bit of overhead for small samples.

There are lots of other small differences, but those are the big chunks.


Is there a fast way to change column type without dropping the clustered index?

If you have Enterprise Edition, are on 2008 or greater (always useful to specify version), and most or all of the values in this column are less than the upper bound of INT, you can get all the space back and probably quite a bit more by applying data compression.

My guess is you can improve I/O by at least as much, probably more depending on the nature of the rest of the data (and your workload), but without having to make the change to the column directly (and hence all of the constraints that apply to it, foreign key columns in other tables, etc).

Something that's certainly worth testing, especially since it's so much less complicated than changing the data type.

Of course, you should establish first whether I/O is even your bottleneck, and whether stripping 4 bytes from this particular column is likely to help you much at all. On its own, I'd have to guess: no.


Does a composite primary key break the first normal form?

No, this is quite normal (pun intended). The criteria is

First Normal Form:
Tables are said to be in first normal form when:

The table has a primary key.
No single attribute (column) has multiple values.
The non-key attributes (columns) depend on the primary key.

None of these requirements for 1NF is violated by the design you propose.
For instance a Junction Table created to normalize a many-to-many relationship will always have a composite Primary Key, with each component being also a Foreign Key into one of the joined tables.


Error 1304 when creating a procedure after a drop if exists

Is every client's updateProc100 identical?  Or is that proc generated by code had has code specific to the client?

If it is identical to all clients, suggest creating, but not dropping, a single copy of it in some 'common' DATABASE.  Then only do

 CALL common.updateProc100();

If there is something tailored to each client, could it be an argument?  And then do prepare+execute to build and run the specific stuff?

Back to what you currently have...

Procs are specific to a DATABASE.
Is each client specific to that database?  That is, are you sure there was a USE (or equivalent) before doing the drop+create+call+drop?
What happens at "intervals after" the login?
Can a client be logged in from two places?
Does the Procedure have an interlock so that it cannot be executed twice?
What is the current mechanism for saying "client 'X' has already called 'proc100'"?


Basic Google-Fu produced the following information:

  The innodb_io_capacity variable defines the number of I/O operations per second (IOPS) available to InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer.

I wouldn't set this to the maximum IOPS value of your SSD disk as you need IOPS for InnoDB foreground tasks. This is also commented on in the link that can be found just underneath the documentation for the parameter.

  For information about configuring the innodb_io_capacity variable, see Section 14.8.8, “Configuring InnoDB I/O Capacity”.

This lead to:

  The InnoDB master thread and other threads perform various tasks in the background, most of which are I/O related, such as flushing dirty pages from the buffer pool and writing changes from the change buffer to the appropriate secondary indexes. InnoDB attempts to perform these tasks in a way that does not adversely affect the normal working of the server. It tries to estimate the available I/O bandwidth and tune its activities to take advantage of available capacity.

  The innodb_io_capacity variable defines the overall I/O capacity available to InnoDB. It should be set to approximately the number of I/O operations that the system can perform per second (IOPS). When innodb_io_capacity is set, InnoDB estimates the I/O bandwidth available for background tasks based on the set value.

  You can set innodb_io_capacity to a value of 100 or greater. The default value is 200. Typically, values around 100 are appropriate for consumer-level storage devices, such as hard drives up to 7200 RPMs. Faster hard drives, RAID configurations, and solid state drives (SSDs) benefit from higher values.

  Ideally, keep the setting as low as practical, but not so low that background activities fall behind. If the value is too high, data is removed from the buffer pool and change buffer too quickly for caching to provide a significant benefit. For busy systems capable of higher I/O rates, you can set a higher value to help the server handle the background maintenance work associated with a high rate of row changes. Generally, you can increase the value as a function of the number of drives used for InnoDB I/O. For example, you can increase the value on systems that use multiple disks or SSDs.

Carrying on to the innodb_read_io_threads parameter...

  InnoDB uses background threads to service various types of I/O requests. You can configure the number of background threads that service read and write I/O on data pages using the innodb_read_io_threads and innodb_write_io_threads configuration parameters. These parameters signify the number of background threads used for read and write requests, respectively. They are effective on all supported platforms. You can set values for these parameters in the MySQL option file (my.cnf or my.ini); you cannot change values dynamically. The default value for these parameters is 4 and permissible values range from 1-64.

  The purpose of these configuration options to make InnoDB more scalable on high end systems. Each background thread can handle up to 256 pending I/O requests. A major source of background I/O is read-ahead requests. InnoDB tries to balance the load of incoming requests in such way that most background threads share work equally. InnoDB also attempts to allocate read requests from the same extent to the same thread, to increase the chances of coalescing the requests. If you have a high end I/O subsystem and you see more than 64 × innodb_read_io_threads pending read requests in SHOW ENGINE INNODB STATUS output, you might improve performance by increasing the value of innodb_read_io_threads.

I interpret this as the parameters not being really related to each other. Each parameter can be configured individually based on the performance of the system.


AlwaysOn Availability Groups: connecting correctly?

There's some weirdness when you use AG Listeners along with multiple standalone SQL instances.  Note that the more common configuration is to install a single standalone instance per node, possibly participating in multiple AGs.

When you enable TCP/IP connections for a SQL instance, the default configuration is for the SQL instance to listen on a port (either fixed or dynamic) on "All IP Addresses":

For standalone instances "Listen All" means that it will listen for incoming connections on all current and future IP addresses.  If an IP address is later added to the system, the instance is already listening on its port on the new IP.  And an AG Listener is just a Network Name and IP that the cluster adds to whichever node hosts the primary replica.

For Failover Cluster Instances, "Listen All" means that the instance will listen on all IP addresses that are dependencies of the FCI's Virtual Network Name, so they don't have this problem.

So, when you have multiple standalone instances where one or more participates in an AG with an AG Listener, you need to perform some extra configuration to prevent every instance from listening on every AG Listener's IP.

Configure each instance to listen on a non-default port (can be dynamic), only on the host's main IP(s), reserving port 1433 for the AG Listener(s).  That way you can connect to each AG Listener without a port number or instance name, and to the instances directly only with a port number or instance name.


Looks like this is similar to: https://jira.mongodb.org/browse/SERVER-17243
This is not fixed as of November 16, 2015.
From the link:

Description
After dropping the collection for a sharded collection, via mongos, the chunks are still visible in the config server. Same if the database is dropped.
This collection had balancing failures, which seems to have left a lock in place, preventing full cleanup.


Yes this is normal. SQL Server will only automatically reduce the size of data files if you have AUTO SHRINK switched on and there is space available in the files. This setting is not recommended as it will introduce considerable fragmentation and consume considerable IO resources.

You could do a one time shrink with DBCC SHRINKDATABASE. See here for more info. This will introduce fragmentation but it can be fixed by rebuilding your indexes.

IMHO the main considerations would be:

Can you live with the current size of the database? If yes then shrinking is probably not worth the effort.
Do you have a long enough maintenance window to shrink and fix the fragmentation? You could test this process by restoring a backup to a similar specced machine and running through the process.

Currently shrinking a databse will fail if you have any columnstore indexes. These need to be dropped if you want to shrink the DB as much as possible.


Select children of product, with no other active parents

This query should do it:

SELECT child.child_product_id
FROM   products_m_products child
JOIN   product             c_prod ON c_prod.id = child.child_product_id
WHERE  child.parent_product_id = 1337     -- user input here
AND    c_prod.status = 'ACTIVE'
AND    NOT EXISTS (
   SELECT
   FROM   products_m_products parent
   JOIN   product             p_prod ON p_prod.id = parent.parent_product_id
   WHERE  parent.child_product_id = child.child_product_id
   AND    p_prod.status = 'ACTIVE'
   AND    parent.parent_product_id <> 1337  -- same user input
   );

The anti-semi-join with NOT EXISTS only keeps children with no other active parent than the given A. This query does not care whether the given parent A is active itself.

Should be as fast as possible.

There are several basic techniques to check there is no related row in another table:

Select rows which are not present in other table

Related answer using a LEFT JOIN:

DELETE rows which are not referenced in other table


Upgrading SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3

Microsoft recommends you update to both the latest SP and the latest CU which means SP3 and CU2. As a side note if you used WSUS with the right classifications ticked this is how it would be updated "automatically". I wouldn't recommend this but it does help when explaining your update policy to users.

For future versions you can also check http://sqlserverupdates.com/ which is managed by the Brent Ozar company. It's incorrect on some of the older versions of SQL Server but perfectly fine for SQL 2012+ (I've notified them and they thanked me but didn't fix the problem).

It's always highly recommended to test updates on lower environments first. I started keeping a list for fun and found literally 20 different ways for the update to fail. Problems occur and not infrequently, though you can almost always recover the situation without resorting to the backups.

Which reminds me, always, and especially before any update, make sure you have valid working backups of everything, especially including your system databases. If you use encryption keys and certificates (or SSRS) you should export and save those as well.

As to the process:

If you have a simple standalone install of SQL Server then it's just a matter of running it and clicking through; or running it from the command line with options to update all instances and shared components.
But if you use other features of SQL Server such as Replication, Clustering, Availability Groups, then you need to look up more specific instructions because the servers should be done in a specific order.
Also if you use Master Data Services you MUST check it's working afterwards as if the update touches its components it will stay non-functional until you go into the GUI and click to update, and this can't be tested or automated; thanks Microsoft.

I suggest starting to keep your own list of your update process and document the failures and resolutions you find as you go along, because you or your successor may need to refer to it later.


This is explained in Extending Database Impersonation by Using EXECUTE AS. The EXECUTE AS context is trusted only in the current database and allowing it to spill over to other databases is a escalation of privilege attack vector.

There are two solutions, both described in the article linked above:

the easy one is to mark the database TRUSTWORTHY: ALTER DATABASE [source_db] SET TRUSTWORTHY ON;. Although easy, is also dangerous in as it makes the dbo of source_db a de-facto sysadmin.
the safe one is use code signing, see Call a procedure in another database for an example. This is more complex, but is 100% buletproff security.


How To Add Days And Time For Instructor Schedule

Try making the instructor_course table like this

CREATE TABLE instructor_course (
    InstructorCourseID int IDENTITY (1,1) CONSTRAINT pk_instructor_course PRIMARY KEY CLUSTERED NOT NULL,
    DayName char(10) NOT NULL,
    CourseID int CONSTRAINT fk_CourseID_instructor_course REFERENCES Course(CourseID) NOT NULL,
    InstructorID ...
    StartTime time NOT NULL,
    EndTime time NOT NULL,
    StartDate date NOT NULL,
    EndDate date NULL);

Then you could also add some constraint to the table if you want to enforce a rule that two courses can't be timetabled at the same time. If a course has multiple sessions during the week each will be a row in this table.


Extracting date from file group name and converting to date fails when filtering

The problem is that for some values the resulting trim isn't a date so it can't be converted to one.

This is your current REPLACE function:

DECLARE @FileGroupName VARCHAR(100) = 'Event_Header_Daily_2018_08_09_FG'
SELECT REPLACE(REPLACE(REPLACE(@FileGroupName, 'Event_Header_Daily_', ''), '_FG',''), '_', '-')
-- Result: 2018-08-09

This is the value in which it won't work (you filtered one but there might be others similar to this one):

DECLARE @FileGroupName VARCHAR(100) = 'Event_Header_Daily_LeftNull_FG'
SELECT REPLACE(REPLACE(REPLACE(@FileGroupName, 'Event_Header_Daily_', ''), '_FG',''), '_', '-')
-- Result: LeftNull

SELECT CAST('LeftNull' AS DATE)
-- Conversion failed when converting date and/or time from character string.

TRY_PARSE() function won't throw an error whenever the cast can't be done, it will return NULL.

SELECT TRY_CAST('LeftNull' AS DATE)
-- Result: NULL

You should switch your explicit CAST with a TRY_PARSE:

SELECT *
FROM
(

    SELECT
        groupname,
        TRY_PARSE(REPLACE(REPLACE(REPLACE(groupname, 'Event_Header_Daily_', ''), '_FG',''), '_', '-') AS DATE) AS dt
    FROM sys.sysfilegroups
    WHERE groupname LIKE 'Event_Header_Daily_%'
        AND groupname NOT IN ('Event_Header_Daily_LeftNull_FG')
) a
WHERE dt < GETDATE()

If you want to know which values are giving casting problems, you can check where the TRY_PARSE IS NULL.

SELECT
    groupname,
    REPLACE(REPLACE(REPLACE(groupname, 'Event_Header_Daily_', ''), '_FG',''), '_', '-') AS dt
FROM sys.sysfilegroups
WHERE groupname LIKE 'Event_Header_Daily_%'
    AND groupname NOT IN ('Event_Header_Daily_LeftNull_FG')
    AND TRY_PARSE(REPLACE(REPLACE(REPLACE(groupname, 'Event_Header_Daily_', ''), '_FG',''), '_', '-') AS DATE) IS NULL


No mysql database in mysql server - how do I give users permissions?

The problem here is that I am invoking mysql as user danielsank, but that user does not have privileges to make new tables, nor to see the mysql database!

The issue becomes clear by invoking mysql as root

$ mysql -u root

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

Now we can see the mysql database, and we can check it for user information

mysql> USE DATABASE mysql;
mysql> SELECT User, Host, Password FROM user;
+------------------+-------------------------+-----------------------------+
| User             | Host                    | Password                    |
+------------------+-------------------------+-----------------------------+
| root             | localhost               |                             |
| root             | <my machine name>       |                             |
| root             | 127.0.0.1               |                             |
| root             | ::1                     |                             |
| debian-sys-maint | localhost               | *<a really long string>     |
| danielsank       | localhost               |                             |
+------------------+-------------------------+-----------------------------+

We can create the new database

mysql> CREATE DATABASE purchases;

and grant permissions to danielsank

mysql> GRANT ALL PRIVILEGES ON purchases . * TO 'danielsank'@'localhost';


Error 1067: The process terminated unexpectedly [InnoDB]

Sounds like you have your data file corrupted. You may try to start up your server using innodb Force Recover and then dump your table data using Select Into Command. If you had your binary log file enabled before the crash, you may restore data through Point In Time Recovery


What rights do you need for Ola's integrity check?

This sounds like more of an issue with DBCC CHECKDB being run than anything specific to Ola's.

Since you're probably creating snapshots, the service account will need to have access to the database directory folders in order to create snapshots there. You could work around this temporarily by peforming a physical only CHECKDB as well.


MySQL replication - independent data update on slave

On this scenario replication should not face any issues.

MASTER (A): INSERT

SLAVE (B): UPDATE,DELETE

The only issue i can think is to change a unique constraint on B and try to insert the same record on A.


Clarification on MySQL Workbench Options File Configuring

The options file in MySQL is a human-readable text file, typically called my.cnf on Unix and my.ini on Windows.  This single file contains configuration entries for all things MySQL, including the server, and the client library, and the various other official client utilities.

The screen you're referring to is a glorified configuration file editor for that file.

Within the file there are "groups" with headers in brackets, that determine which portions of the file are read by the different components of MySQL -- the command line client hast its [mysql] section, the various client utilities (like mysqlcheck) will also read what's in [client], and the server itself is configured under [mysqld].  Some of the other utilities may also have their own optional sections, for utility-specific features and options that would not be valid for all client utilities, like [mysqldump].

What they're doing here is letting you edit different sections of a single file, without actually telling you what they're letting you do.

Take a look at your local options file, and this part of Workbench will perhaps be a little more intuitive.


The shortest expression you can get is:

least(bitand(a, power(2, n-1)), 1)

where you want to find the nth bit of a.

The shortest query with this expression is:

select least(bitand(a, power(2, n-1)), 1) from dual;

From what I've read the above expression also works in PL/SQL, eg:

l_bit_n := least(bitand(a, power(2, n-1)), 1);


What's the quickest way to periodically export PostgreSQL data to a local file on Windows?

Install the psql client (I believe it's in the pgAdmin III install). Add it to your PATH.
Put the following in %appdata%\postgresql\pgpass.conf (you may need to create that file):
hostname:port:database:username:password
In cmd:
psql -c "\copy (select * from foo) to 'fooLocal.txt';" --host $hostname --dbname $dbname --user $username
then fooLocal.txt will be a tab-delimited representation of the output of your query.
\copy (as opposed to COPY) can copy data from a remote server and doesn't need superuser privileges.
If you want to run that psql command on a schedule, you can use Task Scheduler (GUI) or schtasks (CLI), which is somewhat like cron for Windows.


Determining the tables and impact of a large number of SPs

A scientific approach is based on routinely collecting process and procedure performance information, either using a commercial package or a more home-grown solution. You could also start completely from scratch collecting information from Profiler or Extended Events. The important thing is to capture data regularly, and to make it easily consumable (e.g. using SSRS).

Usable historical information makes it easy to track gradual performance changes over time, anticipate increased resource requirements before they occur, diagnose sudden changes, and identify and test areas for improvement.

On that latter point, I believe your approach should be something like this:

Establish good baseline information over a reasonable period of time
Identify areas that might benefit from compression
Test both ROW andPAGE compression in the improvement target area
Test aggregate changes on a representative workload
Refine or regress changes that were not beneficial
Implement the change and monitor the effects in production

This is a lot easier than attempting to track down performance regressions in complex code after the fact, without a baseline to compare with.


Problem creating database using default mdf and ldf path

Try using dynamic sql:

SET @createDatabase = 'CREATE DATABASE [test] ON PRIMARY CONTAINMENT = NONE
( NAME = N''test'', FILENAME = ''' + @filenamedata + ''', SIZE = 51200KB , FILEGROWTH = 10240KB
)
LOG ON
( NAME = N''test_log'', FILENAME = ''' + @filenamelog + ''' , SIZE = 5120KB , FILEGROWTH = 5120KB
)'

EXEC sp_executesql @createDatabase

Another member had a similar problem a while back.
https://stackoverflow.com/questions/5202257/create-a-database-with-dynamic-database-name-in-sql-server-2005


Postgresql update a partitioned table, where update affects multiple partitions

You can update tuples from multiple partitions in one statement.

If you are using declarative partitioning, then the ability to update tuples in a way that causes them to move partitions was added in v11. If you are using partitioning by inheritance, then what it does in this case is up to your triggers.


Why does my execution plan (sometimes) include a left join?

I suspect the difference is the implicit conversion, which can get in the way.
As an example, Bar isn't touched if it has a compatible data type:

CREATE TABLE [dbo].[Bar]
(
    [Value] int NULL,
    [Value2] int NULL
);


best practice with RAID0 configuration - to keep all of them in separate drives i.e. tempdb, mdf, ldf, backups

From performance and safety point of view, 1st solution is better (if you have 4 physical disks available).
For sure you need to separate as soon as possible backups from mdf files in differents disks: in case of a disk crash you lose both the DB and the backup!


It sounds like what you're looking for is the list of communications for which there is no accepted message. You're only interested in checking for communications where there is at least one rejection, so the logic might be something like:

select distinct communications
where  the status is rejection
and there does not exist an acceptance for that communication.

Alternatively, you could check the status on the record with the highest received timestamp for each communication to ensure that it is an acceptance.


That does not look like a corruption problem. It might be a collation issue, not with the database, but with the IIS 7 or Moodle.

You did not specify if MySQL was running in Linux or Windows, so here it goes:

I ran queries to unveil collations and character sets

For MySQL 5.5.12 running on a Windows 7 machine, I get this:

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+---------------------------------+
| Variable_name            | Value                           |
+--------------------------+---------------------------------+
| character_set_client     | latin1                          |
| character_set_connection | latin1                          |
| character_set_database   | latin1                          |
| character_set_filesystem | binary                          |
| character_set_results    | latin1                          |
| character_set_server     | latin1                          |
| character_set_system     | utf8                            |
| character_sets_dir       | C:\MySQL_5.5.12\share\charsets\ |
+--------------------------+---------------------------------+
8 rows in set (0.00 sec)

mysql>

For MySQL 5.5.9 running in Linux, I get this:

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (1.75 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (1.69 sec)

mysql>

You have to make sure your connector software's character set/collation settings match the database it is connecting to. If the REPLACE ran on the DB Server, the REPLACE is a case-sensitive match. I could easily see a collation shift in the eyes of IIS or Moodle messing up a server-side string replace.

You may want to substitute this with doing a client side replace using PHP's str_replace


Insert top 253 values where value in select distinct tradernames

In general if you have a cursor that's your first place to look for performance issues.  It probably works great if you have 10 traders, not so well if you have 10,000.

Try this.

INSERT INTO #result (port, portdate, P, V_95, V_99, VLimit, dCnt,Trad)
SELECT pl.Port,pl.Portdate,pl.dtd_P
    PL,var.V_95,var.V_99, sum(gu.Guid * -1) VLimit,
    Row_number() OVER(partition by pl.TradName ORDER BY Portdate DESC)
    dCnt,pl.TradName
FROM Trad_PL pl
INNER JOIN #Traders
    ON #Traders.Traders = pl.TradName
INNER JOIN rep_tab var
    ON pl.Portdate = var.V_Date
    AND pl.Port = var.Port
    AND pl.TradName = var.Trad
INNER JOIN Trad_Guid gui
    ON pl.Port = gui.Port
    AND pl.TradName = gui.Trad
WHERE pl.Portdate <= @portdate
    AND pl.Port = CASE WHEN @port = 'Her' THEN 'Herport' ELSE @port END
    AND pl.ONA = 'Act'
    AND var.PType = '0Trad'
    AND dCnt <= 253
group by
    pl.TradName,pl.Port,pl.Portdate,pl.dtd_P,var.V_95,var.V_99
ORDER BY Portdate DESC

I removed the where clause that hit @tradlist and just joined to your temp table (which was already created from a distinct list of traders so no worries there).  I also eliminated the TOP 253.  You are already creating a row number with your dCnt field so I just added an entry to the WHERE clause where dCnt <= 253.

If you are still having performance problems I'd make sure you have indexes related to each of your joins and take a look at the query plan.  Performance tuning in general is a big subject but you could do worse than buying a copy of Grant Fritchey's Execution Plan book.  There is even a free ebook copy.

https://www.red-gate.com/simple-talk/books/sql-server-execution-plans-third-edition-by-grant-fritchey/

Last but not least, completely away from your actual question, formatting is your friend when posting pieces of code.  You are far more likely to get positive responses (and answers) if your code is easier to read.  If you look, the piece of code I am posting is formatted to make it easier to read.  You certainly don't have to use this particular layout, formatting can be a very personal thing, but something other than all of the code down the left hand side of the screen is usually helpful.


Need to make sense of FileTable permissions

I've had to resort to a bit of guesswork, but as far as I can tell, I haven't done anything overly permissive.

I started with a non-privileged test account that's a member of the AD group I created, and I was able to navigate as far as the FileStream directory for the database (\\servername\FileStream\DatabaseFileStreamDirectoryName), but nothing was visible in there.

So, I tried the most obvious approach first:

GRANT VIEW DEFINITION ON PDFReports TO [MyDomain\App Operators]

...Then hit F5 in the VM I was browsing from, and the directory for PDF reports showed right up. I was able to browse in there, and copy some files to the directory.

So I think I'm good, but I'd appreciate anybody piping up if I've done anything bone-headed.


Two backup programs, diff and log out of sync

Differential backups are attached to the Full. If your other backup tool takes a full, it's normal that you cannot restore your diff without that full.
You should not use 2 different backup tool in parallel. If you really need to, then you can check if it's possible to specify the "copy_only" option for the backup in CommVault (so your native backup will remains the "official" backups).
If it's not possible, you could then do only full (with copy only) on your side. Of course, if the database are big, this mean bigger backup and longer time to restore.
Maybe you should address the problem at the source. It seems to me that what you really need is permission in CommVault to be able to run restore from that tool.


Login failed for User 18456 State 5. User id works on other machines

According to Microsoft documentation it could be:

  The error reason An attempt to login using SQL authentication failed.
  Server is configured for Windows authentication only. can be returned
  in the following situations.

  When the server is configured for mixed mode authentication, and an ODBC connection uses the TCP protocol, and the connection does not
  explicitly specify that the connection should use a trusted
  connection.
  When the server is configured for mixed mode authentication, and an ODBC connection uses named pipes, and the credentials the client
  used to open the named pipe are used to automatically impersonate the
  user, and the connection does not explicitly specify that the
  connection should use a trusted connection.

  To resolve this issue, include TRUSTED_CONNECTION = TRUE in the
  connection string.


I have decided to store the images on the file system and store the image's path in a separate table which has a foreign key to the row which the images belong to.

Why not just store the paths on the images table? Why have a 1:1 relationship?

  Since I will be storing millions of images, I'm a bit concerned about keeping track of these images. I will have to add a few computers/hdds to store the images. From what I have read I should be using a distributed file system. Am I correct?

You likely do not need a distributed file system. I wouldn't bother with that. Just start an ZFS array up.

  Are there any best conventions I should follow before I start implementing this? I'm thinking about storing the images and giving them a UUID name, and to store the UUID as the path of the image. Then my application ill contact the rest of the path.

I wouldn't do that. You're only talking millions. Just use an int. If you want to store something useful on the row, I would store the sha256 of the image.


Mysql - lock wait timeout while updating 70 million records

Redesign.  Relational databases let you update every row in a table, but it is poor design to need to do such.  Please elaborate on what you are really doing; we can probably discuss another way to do the task.

Compute the flag as needed instead of needing the massive update.
Avoid the flag completely.
Have a parallel table with nothing but the id and flag.
Walk through the table 1K rows at a time -- takes longer to finish, but is less invasive.  (No, do not use OFFSET.)

A disk of only 20GB is very tiny by today's standards.  How much of that is your 70M table?  It sounds like you have no room to maneuver.  So, we may have to take that into account when redesigning.

Please provide SHOW CREATE TABLE; perhaps we can shrink some of the datatypes to conserve space.


Restore A Live Database to Test Database containing defferent Names using SQL Server Management Studio

Just so you know, this is so much more easily done with T-SQL. Using the GUI for backups and restores is a pain in the bottom.
However, what you need to do to in order to restore a backup from one database on top of a different database is ensure that you're moving the data files from the old databases definitions to the new database. On the screen above, you'll need to change the database name, obviously, for the restore.
Then, you have to click on the Files tab. It's right there in the upper left. That will list out all the files in the current database. It will show their current location. It also has a column called "Restore As" that is defaulted to the current location. You want to change that to be the location of your TestDB, wherever that is.
On the screen above, you also have to click on the Options tab (again, upper left). There, you want to enable "Overwrite existing database (WITH REPLACE)".
A giant word of caution here. Make darned sure you've got the database named changed in the first screen. If you mess that up, you'll be forcing a restore on top of your production database (which will probably fail because connections will block you, but if it doesn't, oh boy).
Or, you could:
RESTORE DATABASE NewDatbaseName
FROM DISK = 'x:\bu\olddatabase.bak'
WITH REPLACE,
MOVE 'OldDataFile' TO 'd:\data\newdbname.mdf',
MOVE 'OldLogFile' TO 'd:\data\newdbname_log.ldf';

So much easier and more clear. However, to each their own.


Can we enable tls 1.0 on MSSQL Server 2019 on database level? if yes then how?

TLDR
You don't.
Long answer
You should be using TLS 1.2. If you follow the documentation, you'll be up and running using TLS, but you'll be using TLS 1.2.
SQL Server 2019 does not support SSL/TLS1.0


moving table to another tablespace in primary server oracle 11g

If you have configured Data Guard properly, this will not cause any problem. And by 'properly', I mean FORCE LOGGING is enabled. Data Pump can use direct path load (in which case there is minimal redo  generated), but there are several conditions that will prevent it:

Situations in Which Direct Path Load Is Not Used

Anyway, to check FORCE LOGGING:

select force_logging from v$database;

If the result is NO, you can enable it with:

alter database force logging;

Note that, this should be done on the primary and all standby databases as well (just in case a role transition happens and a standby becomes primary).


Most performant design for each member to manage his/her own news list?

You need four tables:

User            Category           Article                  Pivot
+----+-----     +----+-------+     +----+--------+-----     +---------+--------+
| id | ...      | id | title |     | id | cat_id | ...      | user_id | cat_id |
+----+-----     +----+-------+     +----+--------+-----     +---------+--------+

Table Pivot is standing for M:N relation and contains the subscription pairs user:category. Each user can have 0..n categories subscribed and each category can be subscribed by 0..m users. To fetch articles accordingly to the user's subscriptions you need to use JOINs like that:

SELECT u.name  AS UserName
     , c.title AS Category
     , a.id    AS ArticleID
     , a.title AS ArticleTitle
     , a.date  AS ArticleDate
     , a.url   AS ArticleLink
     -- everything you need
  FROM user      AS u
  JOIN pivot     AS p  ON p.user_id = u.id     -- pivot to user first
  JOIN articles  AS a  ON a.cat_id = p.cat_id  -- articles and
  JOIN category  AS c  ON c.id = p.cat_id      -- categories to pivot then
 WHERE u.id = 12345
 ORDER BY a.date DESC
 LIMIT 10
;

For the best performance you need to index tables by the columns used by the query:
user should have the primary Key (id)
article should have the multicolumn key (id, cat_id, date)
pivot should have the multicolumn primary key (user_id, cat_id) with FKeys to the corresponding tables.


Do I need to set tcpip_socket = true to allow remote connections to a postgresql database?

All you should need is:

listen_addresses = '*'

and then to modify pg_hba.conf to permit connections from the desired source.

This is stated in the comments on the (very old) article you were reading.

Try using the main PostgreSQL documentation, or up to date tutorials, in future.

See:

Connection and authentication settings
Client authentication


Ignore empty rows while loading data into a table

load data local infile 'C:\\temp\\abc.csv'
into table table_name
fields terminated by ',' ignore 1 lines
(@col1,@col2,@col3,@col4)
set
table.column_name = @col1,
table.column_name = @col2,
table.column_name = @col3,
table.column_name = @col4;

(@col1,.....) extend the number of columns to your actual number of columns in csv file.
and relate these column numbers to table columns.
It will be done.


In order to timezone support to be around, the hosting company should have executed this:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql ...

The mysql_tzinfo_to_sql utility should load the time zones. Please make sure the host company has done this. If they can't or won't,

you could into Amazon EC2, you could have full access to MySQL and can run it yourself
Find a willing hosting company

BTW Here is the MySQL Documentation for CONVERT_TZ : http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz


What is a good way to copy data from one Cassandra ColumnFamily to another on the same Keyspace (like SQL's INSERT INTO)?

cqlsh -k mykeyspace -e 'COPY fromTable(columnNames) TO STDOUT' | head -n -1 |
cqlsh -k mykeyspace -e 'COPY toTable(columnNames) FROM STDIN'


In SQL Server what is the database cost for setting a default value on table that already has over 1mil records?

In order to apply this change the process will need to acquire a SCH-M lock and once it's got that it should be pretty instant (a metadata only change.)

The only risk is that if you have a long running transaction that is using the table whilst the ALTER TABLE statement waits to acquire the lock any other subsequent requests coming in that require a lock on the table will be queued up behind it to prevent your alter request from being starved.

So you could end up blocking production queries for a while until the lock is granted and the change made.


How to model that several variants of a merchandise item may affect its price?

If the variants are identified by a variant code at the base of their sku, is the variant code always in the same place?  If so, you can always group together with the base sku.

However, you might want to look at product grouping.  As an example, if your catalog of products is exercise products.  And you have fitbits.  The Fitbit flex can be sold with different colored bands.  Each one would have a sku of 1234 with a letter variant.  So bl for black, bu for blue, pk for pink.  so 1234bl is black fitbit flex.

Now you can set up categories.  So the next level would be the type of fit bit.  You also carry the fitibit Charge.  That sku is 2345.  So one item can have a sku of 1234bl, a subcategory of Fitbit, a category of pedometer, under the store area of technology.  And you can group the data at those various levels.  A vp of Electornics might only care about the Category and the technology level.  Yet a Category manager cares about the sub category and category.  They may also care at the sku level.

Does this answer what you are looking for?


Should I have a parent table with only one field?

Do not store dates in a separate table unless there is something about a particular date that has additional information that you need to store.  In general the date is a simple attribute of some other entity, it isn't an entity in itself unless you have a very peculiar application.

One exception to this might be a star schema data warehouse where there is a time dimension table that might use date as the primary key and have attributes that describe the way that date might roll up - assuming that some roll ups might not be trivial to calculate (like fiscal reporting period or some such).

You can index a date column just as easily as an integer column, so there is nothing to be gained by abstracting out a date so that you can give it an integer alias value.

You can also put a date column in a WHERE clause so searching for records by date is not at all onerous to do.


capability and performance of tags implemented as array vs text vs full text

You can get search functionality with arrays by using the ANY/SOME or generate_subscripts functions. However, the PostgreSQL documentation notes the following:

  Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

So it looks like you're on the right track, because you're correct about not losing performance (you would more likely gain it) and the other methods allow greater flexibility. Tags themselves are about exact matches, but most sites have a way to search tags too (for instance, this very site).

If you implement tag storage as full text, that doesn't preclude matching, including regex, with simpler expressions such as LIKE/ILIKE. A common scenario is to store documents (the chunks of text being searched) as text or varying character and store the tsvector type necessary for full-text search in a separate column. See the PostgreSQL documentation for more on creating a full text index.

The pros and cons should then be evident: using an array limits scalability and search options because it lacks text indexing, stemming and inflection support, but is easy to use when an exact match is desired. Using a text datatype is more flexible when searching for strings. Using a text datatype with a generated tsvector column provides the most flexibility and good performance in all scenarios, but requires more storage.


I suggest to improve the index to:

(timestamp, id) WHERE (imported = false)

so it fits the query better, for both the WHERE and ORDER BY clauses.

The plan will only need an index seek and a partial index scan (until it finds the required 1000 rows).

The current index does not hold the id values, so the whole index has to be read and then the id values retrieved from the table and sorted to get the first 1000.

If the rows matching the WHERE conditions are not many (less than 1000 or less than 10k), then the sort operation is not very expensive but the values still have to be read as they are not in the index. If the matching rows are a lot more (say 100K or more), then both fetching and sorting them will be much more expensive.
It might be cheaper in these cases to do a full table scan. The optimizer decided in your case to do something similar: a parallel index scan of the PK index. This avoids the need of sorting but it still has to read the rows from the table until it finds 1000 rows that match the WHERE condition.

Both cases however would have no problem with the suggested index above, as the table would not have to be read at all and no sorting needed as the index order matches the ORDER BY.


What is considered for output from an sql server job step?

For T-SQL Job Steps, the "output" refers to "messages" -- notices sent via PRINT and RAISERROR. Result sets are also included as "output", but only if there are no PRINT / RAISERROR messages, else it is only the PRINT / RAISERROR messages that are included.

Try this test:

Job Step 1

Definition:

PRINT ' ** Line 1 ** ';

SELECT ' ** Line 2 ** ' AS [Line Two];

RAISERROR(' ** Line 3 ** ', 10, 1);

Output in job history:

  Message
  Executed as user: NT SERVICE\SQLSERVERAGENT. ** Line 1 ** [SQLSTATE 01000] (Message 0)  ** Line 3 ** [SQLSTATE 01000] (Message 50000).  The step succeeded.

Job Step 2

Definition:

SELECT ' ** Line B1 ** ' AS [Line B-One];

--PRINT ' ** Line B2 ** '; -- uncomment and output will show this and not "Line B1"

Output in job history:

  Message
  Executed as user: NT SERVICE\SQLSERVERAGENT. Line B-One
  ---------------
  ** Line B1 **
  (1 rows(s) affected).  The step succeeded.

Job Step 3

Definition:

PRINT ' ** Start ** ';

RAISERROR(' ** Test Exception ** ', 16, 1);

PRINT ' ** End ** ';

Output in job history:

  Message
  Executed as user: NT SERVICE\SQLSERVERAGENT. ** Start ** [SQLSTATE 01000] (Message 0)  ** Test Exception ** [SQLSTATE 42000] (Error 50000)  ** End ** [SQLSTATE 01000] (Message 0).  The step failed.

Regarding the following statement in the Question:

  One might easily think that the history would show exactly the same as is shown in the Messages pane of the Query window [in SSMS] [for example: "(X row(s) affected)"]

One certainly might think that, but what is shown in the Messages tab of SSMS is not necessarily direct output from SQL Server. The "X rows(s) affected" is being generated by SSMS based on info it received from SQL Server that was not direct output but additional info that comes back in the Tabular Data Stream (TDS). The same applies to batch iteration using "GO x" where the x is an integer telling SSMS how many times to submit that particular batch (the one ending with the "GO" batch separator). The output in the "Messages" tab would show Beginning execution loop and at the end Batch execution completed 4 times., but those are messages from SSMS and not from SQL Server.


Cumulative Subtraction from external input

I supposed that the third column is the result of your subtraction and doesn't exist in your main table.

Query:

SELECT `Serial`, `Value1`
    , (@value := @value-Value1) `external value`
FROM `data` d
    , (SELECT @value := 20) v
ORDER BY `Serial`

The query uses a variable which is set to 20 at the beginning, cross join it to the data table and subtract each Value1 one by one. Value1 are order by Serial, from A to E. The order seems to be A to E but based on real data, you will have to order it according to your needs.

Output:

Serial | Value1 | external value
-------|--------|----------------
     A |      1 |             19
     B |      2 |             17
     C |      3 |             14
     D |      4 |             10
     E |      5 |              5

Sample (sqlfiddle):

CREATE TABLE data(Serial char, Value1 int);
INSERT INTO data(Serial, Value1) VALUES
  ('A', 1),
  ('B', 2),
  ('C', 3),
  ('D', 4),
  ('E', 5);


MySQL table locks solution -> InnoDb / Partitions

key_buffer_size ?  RAM size?

On huge tables, indexes, especially if they are not "appended to", cause disk hits -- whether MyISAM or InnoDB.

var0, being an AUTO_INCREMENT, will be "appended to" the end.  Essentially no disk hits for it.  I can't tell about the other 4 indexes.  Let's say they are all very random.  This implies that each row inserted will need 4 disk hits.  On normal drives that means about 25 rows inserted per second.

Solutions...

RAID striping (0,5,6,10).  That will give you a factor of improvement.

SSDs.  $$$

PARTITIONing.  This may help.  It may also help with some SELECTs.  To discuss further, please reveal some of the semantics of the indexed fields.  (dates?  md5s?  names?)  Also provide some of the SELECTs, so we can optimize them at the same time.  The "partition key" must be part of any UNIQUE key.  This is not a problem with the AUTO_INCREMENT, but it may be with the other UNIQUE key.

Nothing (yet) says whether the Engine will make any difference.


What's the difference between POINT(X,Y) and GeomFromText("POINT(X Y)")?

There are two different binary formats related to the MySQL spatial extensions, the "well-known binary" (WKB) format from the standards, and the MySQL internal GEOMETRY data type.

Prior to MySQL 5.1.35, functions like POINT() didn't return the MySQL internal data type; they returned WKB... so prior to then, you had to do this:

INSERT INTO t1 (pt_col) VALUES (GeomFromWKB(Point(1,2)));

But now, as in your example, this works:

INSERT INTO t1 (pt_col) VALUES(Point(1,2));

To the developers' credit, when they changed Point() and similar functions to (more sanely) return GEOMETRY objects, they allowed GeomFromWKB() and similar functions to actually accept either WKB or MySQL Geometry data as input even though the functions are intended to accept WKB as input.

The fact that the 1st method works (in spite of being technically wrong) on newer servers and the 2nd method doesn't work at all prior to MySQL 5.1.35 might explain why examples were written using the approach you've seen -- to avoid the issue entirely.  Otherwise... I've got nothing, here.

Concatenating and then parsing text seems intuitively slower and more error-prone than functions that accept proper variables as input, so I can't think of any reason to craft concatenated strings and use the text-based functions.

http://dev.mysql.com/doc/refman/5.1/en/creating-spatial-values.html#gis-wkb-functions

http://dev.mysql.com/doc/relnotes/mysql/5.1/en/news-5-1-35.html


Create a User and Password for ADO.net and SQL SERVER Authentication

Please go through below links :

https://stackoverflow.com/questions/16386610/how-do-i-create-a-new-user-in-sql-server-2012-that-i-can-use-in-a-connection-str

https://msdn.microsoft.com/en-in/library/aa337562.aspx

https://stackoverflow.com/questions/1601186/sql-server-script-to-create-a-new-user


MySQL Update Ignore not working - errors and does not update all rows

I managed to track this one down. There was a trigger on the table which was updating / inserting into another table.

The update/insert on this trigger did NOT have an ignore and it was actually that which was throwing the key error, not the original update.

Adding the ignore into that trigger code resolved the problem.


Copy a view from one database to another database on the same server

Basic method:

Right click on the view in SQL Server Management Studio
Save as script to new query window
Change context to target database
Execute script

This is basically correct, but this will not preserve permissions on the views. A better way is to:

Right-click the database
Choose Tasks -> Generate Scripts -> Choose Objects -> Select specific database objects
Choose your views -> next
Click the [Advanced] button and then [Script Object-Level Permissions] should be set to True (default is False)


The variant with direct separate SELECTs:

WITH
cte21 AS (SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
          UNION
          SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
          UNION
          SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week,
          BuchDatum) = 21)
         ),
cte22 AS (SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
          UNION
          SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
          UNION
          SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week,
          BuchDatum) = 22)
         )
INSERT INTO [Test].[dbo].[Übersicht_Woche_RAP] (W21, W22, id)
SELECT cte21.Rüsten, cte22.Rüsten, cte21.filtering
FROM cte21
JOIN cte22 ON cte21.filtering = cte22.filtering

But you can create more compact version by create cte AS (SELECT 21 weeknumber UNION SELECT 22) and cross apply it to formalized version of your query (add GROUP BY and wrap values with MAX(), or use PIVOT).


SUMMARIZECOLUMNS is the main function to use here.

So this:

select SUM(Kiekis )
from Info
left outer join Prekes on Info.PrekeID = Prekes.PrekeID
left outer join PrekiuGrupes on Prekes.PrekiuGrupeID = PrekiuGrupes.PrekiuGrupeID
group by PrekiuGrupesID

would be simply

   EVALUATE (
         SUMMARIZECOLUMNS(PrekiuGrupes[PrekiuGrupesID], "TotalKiekis", Sum(Info[Kiekis]) )
   )

DAX traverses the active relationships in the model, so there's no need to use explicit JOINs.  And instead of having a structured query language DAX uses function argument types to detect the different "clauses".  The first string argument to SUMMARIZECOLUMNS indicates the transition from "group by" columns, to pairs of (String,Expression) to specify the result columns.


Use array expression from subquery in ANY() condition

Explanation

By definition in the SQL standard (which Postgres implements) you can reference output columns in ORDER BY or GROUP BY, but not in the WHERE or HAVING clause. The manual:

  An output column's name can be used to refer to the column's value in
  ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses;
  there you must write out the expression instead.

Related:

PostgreSQL reusing computation result in select query
PostgreSQL Where count condition
GROUP BY + CASE statement

Obviously, your subquery is a correlated subquery expression in the SELECT list (which is hidden in the question due to over-simplification).

To avoid repeating lengthy / expensive expressions in the WHERE clause you can use a subquery in the FROM list. A correlated subquery in the SELECT list cannot be referenced by alias in the WHERE clause, that's just an output column like any other.

Chances are, your query can be much more efficient ...

Better query

Applying the above, this query would work:

SELECT s.*, con.cats
FROM   some_table s  -- guessing the missing query
JOIN  (
   SELECT schedule_id
        , array_agg(category_id ORDER BY category_id) AS cats
   FROM   category_schedule_con
   GROUP  BY 1
   ) con ON con.schedule_id = s.id
WHERE  3 = ANY(cats);

Now you can reference the column alias cats in the WHERE clause. But this query is terribly inefficient for big tables for multiple reasons. Most importantly, the predicate is not sargable.

Using GROUP BY and the aggregate function array_agg() instead of the ARRAY constructor because we are producing multiple arrays in a single query.

You can apply an ORDER BY clause to almost any aggregate function, but a sorted subquery typically performs better:

SELECT s.*, con.cats
FROM   some_table s
JOIN  (
   SELECT id, array_agg(category_id) AS cats
   FROM   (
      SELECT schedule_id AS id, category_id  -- alias id for convenience
      FROM   category_schedule_con
      ORDER  BY 1, 2  -- to get ordered list per schedule_id
      ) con
   GROUP  BY 1
   ) con USING (id)
WHERE  3 = ANY(cats);

More importantly, pull the predicate (the WHERE condition) down into the subquery to make it possible to use an index and exclude irrelevant rows early. Much faster with big tables:

SELECT s.*, con.cats
FROM   some_table s
JOIN  (
   SELECT id, array_agg(category_id) AS cats
   FROM  (
      SELECT schedule_id AS id, category_id
      FROM   category_schedule_con c
      WHERE  EXISTS (
         SELECT 1 FROM category_schedule_con
         WHERE  schedule_id = c.schedule_id
         AND    category_id = 3
         )
      ORDER  BY 1, 2
      ) con
   GROUP BY 1
   ) con USING (id);

Depending on data distribution, a LATERAL join (requires Postgres 9.3+) may be more efficient:

SELECT s.*, con.cats
FROM   some_table s
     , LATERAL (
   SELECT ARRAY (
      SELECT category_id
      FROM   category_schedule_con
      WHERE  schedule_id = s.id
      ORDER  BY 1
      ) AS cats
   ) con
WHERE  EXISTS (
   SELECT 1 FROM category_schedule_con
   WHERE  schedule_id = s.id
   AND    category_id = 3
   );

About LATERAL:

What is the difference between LATERAL and a subquery in PostgreSQL?

But it should be the fastest to invert the logic: start by finding schedule_id that have category_id = 3, self-join to category_schedule_con and aggregate before joining to the other table:

SELECT s.*, con.cats
FROM  (
   SELECT id, array_agg(c.category_id) AS cats
   FROM  (
      SELECT schedule_id AS id
      FROM   category_schedule_con
      WHERE  category_id = 3
      ) x
   JOIN   category_schedule_con c USING (id)
   GROUP  BY id
   ) con
JOIN  some_table s USING (id);

Index

Be sure to have a multicolumn index like:

CREATE INDEX category_schedule_con_foo_idx ON category_schedule_con
(schedule_id, category_id);

For the last query, we'd need the inverted sequence of columns:

CREATE INDEX category_schedule_con_bar_idx ON category_schedule_con
(category_id, schedule_id);

and another one with two columns switched back again. Two indexes on just (category_id) and (schedule_id) would work fast, too;

A PK or UNIQUE constraint on both columns serves as well.


Can indexes really decrease performances in a low volume writes DB?

You have a lot of questions and unknowns (no info on the size of db, hardware - RAM, CPU, Disk (san or local ssd), is the server VM or physical, do you have baseline as to what looks good or what is acceptable, sp_configure settings, do you run maintenance - update stats, index reorg/rebuild etc) in your question.

  should I go on and blindly add indexes

No. Read this first - Don't just blindly create those "missing" indexes!

Once you read that, do a health check sp_Blitz: Overall Health Check
Review and Address the high priority ones.
Tune your indexes - sp_BlitzIndex: Tune Your Indexes

Above will get you in good shape. If you see any issues, post the question back and we will help you :-)


In advance i've never written anything in mysql, but the theory should still be there in case of small syntax errors.

In general i'd advice using INNER JOINS, instead of joining your tables in a WHERE clause, due to easier readability and better coding practice, as it makes it easy to change the sql inner join to an outer join as i've shown below:

SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer)+1
FROM voorstelling v
INNER JOIN reserveringen r ON r.Voorstellingsnummer = v.Voorstellingsnummer
WHERE v.Vestigingsnaam = 'Amsterdam'
AND v.Filmnaam = 'Avengers'
GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50

I am however very confused as to how you want the counts added? The above query should return what you're asking for. As it sounds like you just want the initial counts to be added with 1. To have this match more function like instead of a hardcoded value, i would have to understand why 4 and 5 should have a count of 1 in the other table?


If you put dbo, it will look at the dbowner of the database, in your case (I guess) 'MyCompany\A.Smith'

Be sure the user you want to use in the execute as clause is part of sys.database_principals or sys.server_principals.

If not, you can fix this issue changing the owner of the database you use.

First, check the current owner :

select suser_sname(owner_sid)
from sys.databases
where name = 'MyDatabase'

If it's 'MyCompany\A.Smith' your procedure will not run.

So, change the owner, for instance :

sp_changedbowner 'sa'

And it will work.


Fixing MySQL errors recorded in error logs

One of the silent killers of MySQL Connections is the MySQL Packet.

According to the MySQL Documentation

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.
An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

RECOMMENDATION

Try raising the max_allowed_packet to a much larger number, since the default is 1M. I would suggest about 10 times the largest TEXT or BLOB field you have in your current dataset.

You can add it to /etc/my.cnf or my.ini

[mysqld]
max_allowed_packet=128M


Fetch records only for today's date by using PHP & Mysql

You have to use DATE() function:

SELECT *
  FROM table
 WHERE DATE(col1) = DATE(NOW()) -- date parts are equal (HH:MM:SS are dropped)
 ORDER BY col1 ASC
;

Keep in mind that such condition can't be covered by index and can be slow/very slow on a big tables. If the query performance is inappropriate you have to store the date and time parts separately. Columns of types DATE and TIME can be indexed by single multicolumn index.