GROUP BY and using MAX or SUM is the most used standard pivot way.

SELECT
   results.sims_id
 , results.subject
 , MAX(CASE WHEN results.progress_check = "C1" THEN results.result END) "C1"
 , MAX(CASE WHEN results.progress_check = "C2" THEN results.result END) "C2"
 , MAX(CASE WHEN results.progress_check = "C3" THEN results.result END) "C3"
FROM
 results
GROUP BY
   results.sims_id
 , results.subject
ORDER BY
    results.sims_id ASC
  , results.subject ASC

Result

sims_id  subject  C1      C2      C3
-------  -------  ------  ------  --------
   1111  Art      C       B       (NULL)
   1111  English  6       5       (NULL)
   1111  History  B       C       (NULL)
   1111  maths    8       8       (NULL)
   1111  science  A       B       (NULL)
   2222  Art      (NULL)  A       (NULL)
   2222  English  6               (NULL)
   2222  ICT      A       B       (NULL)
   2222  maths    7       6       (NULL)
   2222  science  A       A*      (NULL)

see demo http://sqlfiddle.com/#!9/0be1f2/1


Create triggers on slave only in mysql replication with a slave with different schema

@Mati when your Master/Slave is row based replication the replication thread  replicate a copy of the row into the slave instead of ruining the "insert into xx" statement into the slave.

therefore it'll never run your trigger.

however in your case i suggest you the following solution:
1- create synchronized field on the master with default value 0, and never call populate this field during an insert statement from the master
2- create a trigger on the master for each update to set synchronized to 2 when an update occur
3- remove the trigger from the slave

because the "synchronized" field already exists your replication will fail when you create the field on the master at step #1 above. you can tell your slave to skip temporary the error code related to field already exists below:

slave-skip-errors = xxx (xxx is the error code )


Actually, you question has less to do with "DB2" than it does with one of IBM's main products - IBM i on Power (also known as System i, iSeries, and AS/400 in the past). For whatever reason, IBM chooses to refer to a lot of things as "Objects" within their system. This gets very confusing to hear, because in and of itself, the operating system i5/OS is not particularly object oriented, nor is the programming language (RPG) that comes with the system. That being said, RPG is starting to gain some object-oriented functionality within the last few releases of the language.

The key point of the link you shared is that the Object Server is the piece of the iSeries that allows end users access to reports, etc. (Strangely, a lot of these reports, end-programs, etc. are referred to as objects as mentioned above). Hence the term Object Server.

Now, all this being said, the iSeries does come with a version of DB2. It is called DB2 for i and it is actually integrated into the operating system and the file system. It differs from DB2 LUW (Linux, Unix, Windows) in that it relies on an EBCDIC representation of text rather than ASCII. It also functions a bit different under the covers. Because it is integrated into the OS, the OS takes care of monitoring DB2, so you can actually get away with having no DBA on staff. We did it for years. I've heard similar of other iSeries shops. (And since IBM created the RPG language for the iSeries it integrates incredibly well with DB2 as well.)

Now if it also helps, a physical iSeries server can be divided into several LPARs (logical partitions). Perhaps a "virtual server" if you will. So an LPAR is an "instance" of the iSeries.

Since DB2 for i is integrated into the OS, there is one instance of DB2 per instance of the OS. In other words, each LPAR is an instance of DB2 (and also like THE one and only database at the same time). And unlike DB2 LUW, you cannot have more than one instance on the same server. And lastly, a "library" for DB2 for i, is the equivalent of a schema, and a "file" for DB2 for i, is the equivalent of a table.

Hope this helps clarify your thoughts.


PLE is a bit of a flaky metric. Sure, it's better when it's high and steady, but if you have more data than RAM, that ain't always gonna happen.

Things that can make PLE drop:

Queries that get large memory grants
Queries that displace large amounts of pages in memory with new
ones
Queries that modify many pages and force them to get flushed to
disk
DBCC CHECKDB
Index Rebuilds

PLE is more of a concern if it's constantly low. As with most things, you need to look at your overall wait stats to see if it's an issue.

Since you're looking at My Favorite Monitoring Tool© -- what is it telling you about overall wait stats? If you don't know where to find those, contact support.

The RAM-ifications (wait for it) are that you'll likely need to read those pages back into RAM (there it is) at some point.

Hope this helps!


Create index clustered X NonClustered with include

In this case, the non-clustered index is essentially a duplicate of the primary key*. It will take up extra space and make inserts/updates/deletes slower, but it will not add any benefit.

The reason is that any query that can be answered by seeking on column a can perform that seek on the primary key and has no need for the non-clustered index.

However, if your actual table has many additional columns beyond a, b, and c (e.g., let's say there are 100 other columns as well), it's possible that the non-clustered index could improve queries that use only a, b, and c. In that case, it would provide a smaller data structure that can be used to access only these three columns. It would depend on your workload whether this is worth the cost of maintaining the index.

* The primary key is the clustered index here and, as Max Vernon points out, the clustered index is the table. So another way of putting it is that your non-clustered index is duplicating the whole table.


Mongo always allocates next file when perform a first write to last file.
So when collection is created it has 64MB allocated (using small files option)  in first file and mongo allocates 128MB in next chunk.

When collection grows and last file has 2GB, then when document is added to that file, next 2GB file is allocated.

file allocation sizes
e.g. 64MB (small files), 128MB, 256MB, 512MB ... 2GB - this is max size and every new one will be 2GB.)


if col1 has the same data, copy col2 data

This solution gives you the desired output.

DECLARE @TempTable TABLE
    (
      Prime CHAR(2) NOT NULL
    , Percentage INT NOT NULL
    );

INSERT  INTO @TempTable
        ( Prime, Percentage )
VALUES  ( 'a1', 3 )
    ,   ( 'a1', 0 )
    ,   ( 'b2', 25 )
    ,   ( 'b3', 42 )
    ,   ( 'c4', 23 )
    ,   ( 'c4', 0 );

WITH CTE_NonZero
    AS ( SELECT   T.Prime
                , MAX(T.Percentage) AS PercentageToUse
        FROM     @TempTable T
        WHERE T.Percentage <> 0
        GROUP BY T.Prime
        )
    UPDATE  @TempTable
    SET     Percentage = P.PercentageToUse
    FROM    @TempTable T
            INNER JOIN CTE_NonZero P ON P.Prime = T.Prime
    WHERE   T.Percentage = 0;

SELECT * FROM    @TempTable;

Alternatively, you could make the CTE updatable and thus avoid the join:

WITH MaxPercentages
    AS ( SELECT   T.Percentage
                , MAX(T.Percentage) OVER (PARTITION BY T.Prime) AS PercentageToUse
        FROM     @TempTable T
        )
    UPDATE  MaxPercentages
    SET     Percentage = PercentageToUse
    WHERE   Percentage = 0 AND PercentageToUse <> 0;


First approximation:  An InnoDB table or index will be 2-3 times as large as you would expect from adding up the column sizes.

I get 53 for VARCHAR(1023) with an average of 51 characters.  VARCHAR for non-small max needs 2 bytes for length.  This also assumes there are no non-English letters in the url.  For example, each Chinese character takes 3-4 bytes.  The index size needs to be computed in bytes.

For your 3 examples, there won't be a lot of difference, since most of the 2x-3x comes from BTree overhead, padding, row overhead, etc.

Another thing that can make a significant difference in index size is whether the rows are are inserted in sorted order -- or not.  That leads to well packed BTree blocks -- or not.

Also, your numbers are incomplete.  Three different cases:  MyISAM index, InnoDB PRIMARY KEY, InnoDB secondary key.

For InnoDB secondary key, the PRIMARY KEY is tacked on.  So, you need to add whatever sized those column(s) are.  Typical case: 4 bytes for INT.

Even if you get past all that, there will be anomalies.  A 1-row table will have 16KB for each secondary index.  At a certain table size, the "allocation unit" switches from 16KB to 4MB.  After that, adding a row will rarely change the index size, but sometimes will show a big jump.

Etc, etc.

An aside...  Indexes using SHA2 (or other digests/hashes) are notoriously bad for performance on huge tables.  They lead to random accesses, which blows out cache, which leads to reads and writes being I/O bound, which is deadly for huge tables.


How many fields is too much for a primary key?

First off, don't worry about performance until you're sure you have a performance problem, especially for a tiny number of rows.

Second, whether you use a unique index that is primary key or a unique index that isn't the primary key to enforce the uniqueness of the combination of your four columns the performance will be the same with respect to that index.

Whether to make that unique index the primary key should depend on what else is going on in your schema.  If you have another table (or tables) that reference this table, then your compound primary key is going to get propagated to other tables as a foreign key.  If that is going to happen, they you have more to think about.

If you don't need to worry about propagating a compound foreign key, then you should probably just make your compound unique index the primary key of your table.  That would be the least effort which achieves your goal of enforcing your business rule.


Using source columns in OUTPUT INTO clause of an INSERT statement (SQL Server)

Actually, you can achieve the same thing by changing your INSERT to a MERGE. While the MERGE statement is actually a pretty neat way to do "upserts" in SQL Server, there's nothing to stop you from using it just for the purpose of inserting:

-- The existing table
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX));

-- My data I want to insert
DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX));
INSERT INTO @MyInsertData ( ID,Name)
VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last');

DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT);

MERGE INTO @MyTable AS dest
USING @MyInsertData AS ins ON 1=0   -- always false

WHEN NOT MATCHED BY TARGET          -- happens for every row, because 1 is never 0
    THEN INSERT ([Name])
         VALUES (ins.[NAME])

OUTPUT inserted.ID, ins.ID
INTO @MyCrossRef (NewId, OldId);

-- Check the result
SELECT * FROM @MyCrossRef

One of the nice things about MERGE is that it allows you to access the source columns as well as the built-in inserted and deleted tables in the OUTPUT clause.

My code may contain errors, as I haven't actually tested it. My blog post from a few years ago goes into a little more detail, including on query performance.


In MySQL, how can I have non-nullable timestamp column without a default value?

I think that the only way is setting the server variable explicit_defaults_for_timestamp to ON (default is OFF).
This will disable the non-standard behaviour of timestamp columns regarding NULL values:

If explicit_defaults_for_timestamp is enabled, the server disables the nonstandard behaviors and handles TIMESTAMP columns as follows:

It is not possible to assign a TIMESTAMP column a value of NULL to set it to the current timestamp. To assign the current timestamp, set the column to CURRENT_TIMESTAMP or a synonym such as NOW().

...

TIMESTAMP columns declared with the NOT NULL attribute do not permit NULL values. For inserts that specify NULL for such a column, the result is either an error for a single-row insert or if strict SQL mode is enabled, or '0000-00-00 00:00:00' is inserted for multiple-row inserts with strict SQL mode disabled. In no case does assigning the column a value of NULL set it to the current timestamp.


Set private key password for mysql using ssl

I had to make sure that mysql owned the .pem files by cd to directory that contains the .pem files and:

chown -R mysql .
chgrp -R mysql .

After doing that the problem was solved.


You have many state changes you want to happen in order for your interaction to be considered a success.

This pretty much defines the "atomic" goal of a transactions.

begin; --start the transaction
insert into person values (...);
insert into address values (....);
commit; --finish the transaction

This ensures that if there was an error any where along the way all transaction activity is rolled back.  It's an "all or nothing" operation so you don't end up in an in between state you define as undesirable.


Linked server not working after AWS RDS failover

Update: July 2020
As of July 2020, we now replicate the service master key after a host replacement. Previously, during a host replacement linked servers with passwords would get the error “An error occurred during decryption”. This is now resolved.
Found the answer here
https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/
If a host replacement happens, RDS SQL Server does not restore the service master key. This means that linked servers with passwords will get the error “An error occurred during decryption” when you try to use the linked server. Unfortunately, there is no current notification of a host replacement. The closest you can come is to monitor reboots and failovers via SNS events. You can read more about subscribing to those events on Using Amazon RDS Event Notification. When a reboot or failover happens, you need to manually re-add the linked server login passwords. This is a temporary workaround until a future update to RDS SQL Server addresses this by restoring the service master key after a host replacement.


SERVERPROPERTY('HadrManagerStatus') equating to 1 even if Always On is not configured

As mentioned in a comment by Kin, if you want to check on whether the "Enable Always On" checkbox is checked in SQL Server Configuration Manager, you should be using this property instead:

SELECT SERVERPROPERTY('IsHadrEnabled');

From the docs on IsHadrEnabled:

  Always On availability groups is enabled on this server instance.

Regarding the other setting (HadrManagerStatus), here's what docs has to say:

  Indicates whether the Always On availability groups manager has started.

I don't really know what the "Availability Groups manager" is, but it sounds like probably an internal component to SQL Server.

Tony helpfully pointed out that this value was non-zero on non-AG instances prior to 2017.  I just verified this on a SQL Server 2016 instance where AGs are not enabled and set up, and I get "2" ("Not started and failed").

I'm speculating here, but the fact that the "AG manager" component is started by default on SQL Server 2017 probably has something to do with the clusterless, "read-scale" AG feature introduced in that version.  So on 2017 this component is able to start up and run, whereas previous versions noticed the lack of WSFC and failed to start.

Anyway, that's just a guess.  Maybe someone from Microsoft will come by and confirm one way or the other.  The real answer is what Kin pointed out - if you want to check for AG's being enabled, use IsHadrEnabled.


Is there a maximum IOPS for MySQL asynchronous replication on the slave server?

The immediate fix for the Slave

SET GLOBAL innodb_flush_log_at_trx_commit = 2;

This should speed up the slave a little.

Go to my.cnf on the Slave and add

[mysqld]
innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DIRECT

and restart mysqld.

Please read my old post Dynamic change to innodb_flush_log_at_trx_commit for more clarification.


If you've dropped all the tables, you could just drop the database and re-create it. It will be created with the default sizes and growth rates as defined by the model database. These may not be ideal (I don't find the defaults particularly useful in most scenarios), but you can change them.

Unless you have a lot of things set up on this database (like Service Broker, a ton of users, etc.), I think that is less cumbersome than getting into the habit of growing/shrinking growing/shrinking growing/shrinking. You should put the database on a drive that has enough space to accommodate the data you're going to generate, without having to shrink all the time. Shrinking and growing are both expensive events, especially on slow I/O.

Also, to prevent the log from growing to 9 GB (that seems pretty ridiculous for the data you describe), you should either be full recovery and you need to perform more frequent backups and log backups, or you should switch to simple recovery. More details here - it's long but please read it all.


Pausing the import of an SQL file in Postgresql?

I'm assuming based on the post that the PostgreSQL database server and the process restoring the dump are on the same machine. If so:

  Is it possible to pause the import of the database?

Not really. You could SIGSTOP the pg_restore or psql process and/or the corresponding postgres backend, but I wouldn't consider that my first-choice option.

  If not, will putting the laptop into hibernate while I relocate it cause the import to freeze or fail?

Since it's a loopback TCP connection or a unix socket connection when you're restoring to a local PostgreSQL instance, it doesn't care if the network changes or goes away. So suspend and resume is just fine.

BTW, 9GB is not large. 1TB is large.


Enforce NOT NULL for set of columns with a CHECK constraint only for new rows

If you have a serial column or an integer one that's automatically populated with a nextval (so that you are never supposed to insert new rows with an explicit value for that column), you could additionally check whether the value of that column is greater than a specific value:

(
  (("qb_id" IS NOT NULL) :: INTEGER +
  ("xero_id" IS NOT NULL) :: INTEGER +
  ("freshbooks_id" IS NOT NULL) :: INTEGER +
  ("unleashed_id" IS NOT NULL) :: INTEGER +
  ("csv_data" IS NOT NULL) :: INTEGER +
  ("myob_id" IS NOT NULL) :: INTEGER) > 0
  OR
  YourSerialColumn <= value
)

where the value should be determined as currval of the column/corresponding sequence at the time of altering/recreating the constraint.

This way the IS NOT NULL checks will apply only to the rows whose YourSerialColumn value is greater than value.

Note

This can be viewed as a variation on David Spillet's suggestion. The principal difference lies in the fact that this solution does not require structural changes (partitioning). Both options, however, rely on existence of a suitable column in your table. If there is no such a column and you can add it specifically to solve this problem, going with the partitioning idea might be the better option of these two.


Restoring an old backup to latest MySQL release

IMHO, when it comes to MySQL 5.5, you will definitely see much better performance from InnoDB than in past releases because dirty pages in the InnoDB Buffer Pool do not linger as long. Those dirty pages are flushed more robustly.

You also have the luxury of creating multiple InnoDB Buffer Pools (See innodb_buffer_pool_instances and innodb_buffer_pool_size). To reduce thread locking amongst the buffer pools, make sure set innodb_thread_concurrency to 0 to let InnoDB storage engine decide best how to handle thread allocation.

CAVEAT ON MIGRATION TO MySQL 5.5

Make sure you mysqldump all databases EXCEPT the mysql schema. There is a cleaner, crisper way to migrate User Grants out of the MySQL Schema. In fact, there are two options for accomplishing this:

OPTION 1 : Use mk-show-grants

This dumps out all the MySQL Grants as SQL statements, which is completely portable to any MySQL 5.x instance.

OPTION 2 : Run these commands (My personal emulation of what mk-show-grants does)

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql


The conventional approach is to set up multiple replicated slaves.

You then backup from these slaves periodically (mysqldump is probably fine for this part still).

The master need not be blocked while the slaves are being backed-up; it will simply continue to replicate once those backups are done. Stagger the backups so you always have an up-to-date slave.

Replication is not CPU-expensive (unless you have a crazy level of database activity), and the slaves' backup process won't impact the master at all.

And everything should have redundant storage.

Anecdote: At a previous job we had a 600GB database that was backed up in the same way that you're doing it, but over a 10Mbit pipe to the target, and it would block the entire database for 11 hours every night! Eesh. Never did get replication in place there (lol) but upgrading the networking to 100Mbit meant it could be done in 2 hours or so instead, which was "fine". Of course, with the database locked during the backup (which it should be!), and thus the actual service unavailable for that duration, a performance hit from the backup process was not even observable from the outside. In your case you have multiple distinct databases so the situation isn't quite the same — you don't have to lock everything for the whole time.


First of all, MySQL has been supporting CTEs since relatively recently, starting from version 8.0 (as has been mentioned by others). The syntax error you are getting seems to indicate that you are using an older version. Therefore, your database server requires an upgrade1.

Apart from that, there are issues with your CREATE VIEW statement that need to be addressed in order to make it work in MySQL.

Square bracket delimiters.

Use of square brackets to delimit names, like in the statement's first line:

CREATE VIEW [dbo].[vw_PurchParent] as

is specific to Transact-SQL. For MySQL, you need to replace them with either double quotes (") or backticks (`). Or, in this case, you can just omit them altogether, because the name contains only letters and an underscore, which are allowed to be used in names without delimiting.
Schema name.

In SQL Server, databases contain schemas, and schemas contain objects (tables, views, functions, stored procedures etc.). Therefore, the full name of an object consists of three parts:

database.schema.object

When it only has two parts, like in this case ([dbo].[vw_PurchParent]), then it is interpreted as

schema.object

However, MySQL does not support schemas in databases. When an object reference has two parts, it is interpreted as

database.object

Therefore, it is likely that you will want to drop the dbo. part of the view name when adjusting the script for MySQL.
Missing keyword RECURSIVE.

The CTE in this view's definition is a recursive CTE2. MySQL supports recursive CTEs but, unlike SQL Server, it requires that the RECURSIVE keyword be specified when one or more CTEs in the WITH clause are recursive.

Therefore, the WITH line of the definition will need to be rewritten as

WITH RECURSIVE cte AS

The GO keyword.

The GO keyword at the end of your script is specific to the SQL Server platform, although it is not part of the Transact-SQL dialect per se. Rather it is an instruction interpreted by some of SQL Server client tools (SSMS, sqlcmd, the now deprecated osql). It indicates the end of a batch of statements. In this case, it should simply be replaced with a semicolon (;).

Note that it is also a good practice in general to always end a statement with a semicolon, which is the standard statement delimiter in SQL.

1 Alternatively, you could also consider switching to MariaDB. MariaDB is a fork of MySQL, and as such it is compatible with MySQL in many respects, including the SQL dialect aspect. At the same time, though, MariaDB is known to often introduce advanced language features – which includes CTE, window functions and others – before MySQL does. For instance, as already mentioned, CTEs have been introduced to MySQL in version 8.0, which was released one year ago (in April 2018). In contrast, MariaDB has been supporting CTEs for about 2-3 years, as of writing this. (CTEs were first added to version 10.2 in 2016; the version became a stable release in 2017.)

2 It represents a UNION ALL query with a self-reference in the second leg, which is the specific structure that a recursive CTE is required to have.


How do I return a json object of my database table

So with a bit of hunting and adapting, i resolved the issue with a different method.  I'll post it here incase someone else has an issue similar to this in the future.

The completed route in the server to get the json working correctly is

@testRestServer.route("/dbMap", methods=['GET', 'POST'])
def getData():

cursor.execute('''SELECT * FROM locations ''')
row_headers = [x[0] for x in cursor.description]  # this will extract row headers
rv = cursor.fetchall()
json_data = []
for result in rv:
    json_data.append(dict(zip(row_headers, result)))
return json.dumps(json_data)


Strange indeed.

That local.oplog.rs is journaling what is enable (default) at 64 bit systems (32bit default value is false). So, running time for that thread should be same than your mongod uptime.

But journaling should not rise CPU usage. When you have high CPU usage, what mongotop and mongostat shows? What is happening in the system?

If you look result of top-command, "who" is eating most CPU power (one or more mongod processes?) and what is highest %-value us(er), sy(stem), ni(ce), id(le), wa(it)?


Setting a column's default value by adding a constraint

This is a rather contrived example, but you'll get the idea.

mysql> create table b(a int primary key, c varchar(3), unique key x_b_uk (c));
Query OK, 0 rows affected (0.46 sec)

mysql> alter table b alter c set default 'xyz';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into b (a) values(1);   <<== didn't set field c!
Query OK, 1 row affected (0.39 sec)

mysql> select * from b;
+---+------+
| a | c    |
+---+------+
| 1 | xyz  |                    <<===== value of c = 'xyz'!
+---+------+
1 row in set (0.16 sec)

mysql>

Check here.


How do I add values to partition function and scheme?

You can modify the partition scheme and function to effectively add a new partition.

ALTER PARTITION SCHEME [PartitionByPeriodScheme] NEXT USED [FactsAfter2016_2];

Follow this up with:

ALTER PARTITION FUNCTION [PartitionByPeriodFunction]() SPLIT RANGE 20171231;

This will add a range between 20161227 and 20171231, which will be placed on  [FactsAfter2016_2].

As always, try this on a non-production system first.


Select rows from table where each row refers to a different schema

You could use inheritance for this. You would have a "master" schema with template tables and only the basic set of columns that all inheriting tables share:

CREATE TABLE master.portal(portal_name text);

All other tables in various schemata inherit from it, while possibly adding more local columns:

CREATE TABLE a.portal(portal_id serial PRIMARY KEY, col2 text)
INHERITS (master.portal);

CREATE TABLE b.portal(portal_id serial PRIMARY KEY, col2 text, col3 text)
INHERITS (master.portal);

A SELECT query targeted on the master table would then search all child tables as well:

SELECT portal_name, tableoid::regclass::text AS source
FROM   master.portal
WHERE  portal_name ILIKE '%test%';

More details in this closely related answer on SO:

Retrieve all records from multiple schemas in a PostgreSQL database


SQL query for combinations without repetition

For relatively small values of n (20 in this example), you can use a method that exploits the fact that the natural integers are combinations of bits.

T-SQL Solution

Sample data:

DECLARE @Sample AS TABLE
(
    item_id     tinyint IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    item        nvarchar(500) NOT NULL,
    bit_value   AS
                CONVERT
                (
                    integer,
                    POWER(2, item_id - 1)
                )
                PERSISTED UNIQUE CLUSTERED
);

INSERT @Sample
    (item)
VALUES
    (N'Ann'),
    (N'Bob'),
    (N'Charles'),
    (N'Darren'),
    (N'Eric'),
    (N'Fred'),
    (N'George'),
    (N'Harry'),
    (N'Ian'),
    (N'John'),
    (N'Keith'),
    (N'Larry'),
    (N'Mark'),
    (N'Nathan'),
    (N'Owen'),
    (N'Paul'),
    (N'Quentin'),
    (N'Ryan'),
    (N'Steve'),
    (N'Terry');

Solution:

-- Maximum integer we need
-- for all combinations of 'n' bits
DECLARE
    @max integer =
    POWER(2,
        (
            SELECT COUNT(*)
            FROM @Sample AS s
        )
    ) - 1;

SELECT
    combination =
        STUFF
        (
            (
                -- Choose items where the bit is set
                -- and concatenate all matches
                SELECT ',' + s.item
                FROM @Sample AS s
                WHERE
                    n.n & s.bit_value = s.bit_value
                ORDER BY
                    s.bit_value
                FOR XML
                    PATH (''),
                    TYPE
            ).value('(./text())[1]', 'varchar(8000)'), 1, 1, ''
        )
-- A standard numbers table
-- (single column, integers from 1 to 1048576, indexed)
FROM dbo.Numbers AS N
WHERE
    N.n BETWEEN 1 AND @max;

Output sample:

╔════════════════════════╗
║      combination       ║
╠════════════════════════╣
║ Ann                    ║
║ Bob                    ║
║ Ann,Bob                ║
║ Charles                ║
║ Ann,Charles            ║
║ Bob,Charles            ║
║ Ann,Bob,Charles        ║
║ Darren                 ║
║ Ann,Darren             ║
║ Bob,Darren             ║
║ Ann,Bob,Darren         ║
║ Charles,Darren         ║
║ Ann,Charles,Darren     ║
║ Bob,Charles,Darren     ║
║ Ann,Bob,Charles,Darren ║
║ ...                    ║
╚════════════════════════╝

Execution plan:

It takes 41 seconds to write the 1,048,576 combinations to a variable on my laptop.  With forced parallelism, I was able to drop the execution time at DOP 8 to 13 seconds.

If you need a Numbers table, this is a quick way to produce one:

SELECT TOP (1048576)
    n = ISNULL(CONVERT(integer, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), 0)
INTO dbo.Numbers
FROM sys.columns AS c
CROSS JOIN sys.columns AS c2
CROSS JOIN sys.columns AS c3;

CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.Numbers (n)
WITH (MAXDOP = 1, SORT_IN_TEMPDB = ON);

SQLCLR solution

A much more efficient implementation is possible in SQLCLR (SQL Server 2005 onward):

CREATE ASSEMBLY [Demo]
    AUTHORIZATION [dbo]
    FROM [encoded data, 12290 characters];
GO
CREATE FUNCTION dbo.Combinations
(
    @ElementsCSV nvarchar (4000)
)
RETURNS TABLE
(
    Combination nvarchar (4000) NULL
)
AS EXTERNAL NAME Demo.UserDefinedFunctions.Permute;

Example usage:

SELECT
    f.Combination
FROM dbo.Combinations('A,B,C,D') AS f;

Output:

╔═════════════╗
║ Combination ║
╠═════════════╣
║ A           ║
║ B           ║
║ A,B         ║
║ C           ║
║ A,C         ║
║ B,C         ║
║ A,B,C       ║
║ D           ║
║ A,D         ║
║ B,D         ║
║ A,B,D       ║
║ C,D         ║
║ A,C,D       ║
║ B,C,D       ║
║ A,B,C,D     ║
╚═════════════╝

Using the 20-element set from earlier:

DECLARE @Sample AS TABLE
(
    item_id     tinyint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    item        nvarchar(50) NOT NULL
);

INSERT @Sample
    (item)
VALUES
    (N'Ann'),
    (N'Bob'),
    (N'Charles'),
    (N'Darren'),
    (N'Eric'),
    (N'Fred'),
    (N'George'),
    (N'Harry'),
    (N'Ian'),
    (N'John'),
    (N'Keith'),
    (N'Larry'),
    (N'Mark'),
    (N'Nathan'),
    (N'Owen'),
    (N'Paul'),
    (N'Quentin'),
    (N'Ryan'),
    (N'Steve'),
    (N'Terry');

SQLCLR solution:

-- Create CSV input
DECLARE
    @Elements nvarchar(4000) =
        STUFF
        (
            (
                SELECT ',' + s.item
                FROM @Sample AS s
                ORDER BY
                    s.item_id
                FOR XML
                    PATH (''),
                    TYPE
            ).value('(./text())[1]', 'varchar(8000)'), 1, 1, ''
        );

DECLARE
    @bitbucket nvarchar(4000);

SELECT
    @bitbucket = combination
FROM dbo.Combinations(@Elements);

Execution time is 2.5 seconds for the 1,048,576 combinations on my laptop at DOP 1.

Creating the CSV input:

Finding the combinations:

C# Source Code:

using System;
using System.Collections;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
        DataAccess=DataAccessKind.None,
        SystemDataAccess=SystemDataAccessKind.None,
        FillRowMethodName="FillRow",
        TableDefinition="Permutation nvarchar(4000)"
        )
    ]
    public static IEnumerable Permute(string ElementsCSV)
    {
        // Split CSV
        string[] elements = ElementsCSV.Split(new char[] { ',' });

        // Highest integer needed
        int count = (int)Math.Pow(2, elements.Length) - 1;

        // Pre-computed array of 2^n values
        int[] powers = new int[elements.Length];

        for (int i = 0; i < powers.Length; i++)
        {
            powers[i] = (int)Math.Pow(2, i);
        }

        // Test integers
        for (int i = 1; i <= count; i++)
        {
            // Reset output
            string s = string.Empty;

            // Test each bit that could be set
            for (int bit = 0; bit < powers.Length && i >= powers[bit]; bit++)
            {
                if ((i & powers[bit]) == powers[bit])
                {
                    // Add the element corresponding to the set bit
                    s += elements[bit] + ',';
                }
            }

            // Return a row via enumeration
            yield return s.Substring(0, s.Length - 1);
        }
    }

    // Called by SQL Server to fetch a row
    public static void FillRow(object o, out string Permutation)
    {
        Permutation = (string)o;
    }
}


The error sounded very cryptic, but it resulted to be a simple solution:

http://databasebestpractices.com/attributes-match-present-attributes-directory-compressed-included-attributes-0-excluded-attributes-compressed-encrypted/

It meant that the target folder was compressed, I didn't notice before, so I uncompressed "c:\Program Files\Microsoft SQL Server" and the Edition Upgrade ended in 5 minutes succesfully.


Full scans/sec metric seems very high, dropped after killing 2 queries

These metrics often have to be looked at in parallel with other metrics.  Simply doing a table scan is not a bad thing in itself.  Often the query engine will pick a table scan even if an index exists if it's faster, for example on a tiny table.  You need to look at other metrics, such as Disk Read/Sec & Disk Write/Sec, along with PLE and others.

Note that your memory metrics did not change at all, and you're still at 100% cache hit ratio, assuming you waited before taking the screenshot.  What it did affect was the CPU usage, which I didn't notice you mentioning as a concern, but if you are concerned about it, there it is.  Note that you reduced 3 ms of latch wait times, which means most likely you had some sort of writes or a high isolation level.

If your table scans are causing performance issues it'll probably show up in the other metrics or in your blocking reports based on your isolation level.  If you look at your PLE it didn't seem like the drops in scans did anything to it.  Maybe you need to give it more time but look to see if that drastically rises.  That'll be 1 indication that there was pressure because of the scans.  Collect the other metrics and review them before and after.

Also I'm sure you already know but be very careful about using the kill command.  In this case it was a read only query, in a write it will not perform a rollback thus voiding your ACID compliance.  Consider a DBCC INPUTBUFFER('spid') first if you're not already using that.


The first query you have used returns Cartesian product of the rows from these three tables.
For details: Join

The following is the simple example to join three tables.

SQL> select * from student;

STUDENT_ID FIRSTNAME        LASTNAME
---------- -------------------- --------------------
     1 Will         Smith

SQL> select * from module;

 MODULE_ID NAME
---------- --------------------
     1 Computing

SQL> select * from marks;

STUDENT_ID  MODULE_ID       MARK
---------- ---------- ----------
     1      1         40

SQL>

SQL> select s.firstname, s.lastname, mo.name Module_Name, m.mark
     from student s
         join marks m on (s.student_id=m.student_id)
         join module mo on (m.module_id=mo.module_id);

FIRSTNAME     LASTNAME     MODULE_NAME     MARK
------------- ------------ --------------- --------
Will          Smith         Computing      40

SQL>


Scheduled backup task doesn't always backup all databases despite always saying job successful

I would add TRY/CATCH blocks to handle errors and log them. The DB could be in single user, being restored or whatever.

Without this, errors can abort in a way that no errors are logged (statement, batch, scope, connection etc)

With TRY/CATCH then everything except for compile or connection aborting errors are logged? but I doubt this is the case.

I'd also use sys.databases which replaces sysdatabases and read more flags:

-- declares etc

BEGIN TRY

    DECLARE db_cursor CURSOR FOR
    SELECT name, state, user_access
    FROM sys.databases
    WHERE name NOT IN ('tempdb')

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @name, @state, @user_access

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @fileName = @path + @name + '.bak'
        IF @state = 0 AND user_access = 0
        BEGIN
            BEGIN TRY
                BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
            END TRY
            BEGIN CATCH
                -- log but do not rethrow so loop continues
            END CATCH
            WAITFOR DELAY '00:00:20'
        END
        ELSE
           --log user and/or state issues

        FETCH NEXT FROM db_cursor INTO @name
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

END TRY
BEGIN CATCH
  -- some useful stuff here
END CATCH


Mongoose is not showing the ISODate in the result

You need to add like this:
let date = new Date().toISOString();
let isoDate = new Date(date);

Hope it works.


Using the SQL will get you the invoice and json aggregated items in an array:

SELECT invoice, JSON_ARRAYAGG(item)
FROM tablename
GROUP BY invoice


How do I make Postgres use an index when using IN or ANY?

I don't think that it's failing to use the index, but that it has a lot to do with a combination of factors:

number of rows in the table
number of unique values in col1
query planner tuning (seq_page_cost,random_page_cost,etc.)

It could very well be that the lowest-cost plan involves a Sequential Scan, at least for this situation.

On my machine, I get the following:

edb=# create table mytable (col1 text);
CREATE TABLE
edb=# create index mt_idx on mytable(col1);
CREATE INDEX
edb=# insert into mytable values ('a'),('b'),('c');
INSERT 0 3
edb=# explain analyze select col1 from mytable where col1 in ('a','b','c');
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on mytable  (cost=8.62..18.90 rows=20 width=32) (actual time=0.087..0.131 rows=3 loops=1)
   Recheck Cond: (col1 = ANY ('{a,b,c}'::text[]))
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on mt_idx  (cost=0.00..8.62 rows=20 width=0) (actual time=0.058..0.071 rows=3 loops=1)
         Index Cond: (col1 = ANY ('{a,b,c}'::text[]))
 Planning time: 0.124 ms
 Execution time: 0.290 ms
(7 rows)

edb=# insert into mytable values (generate_series(1,1000000)::text);
INSERT 0 1000000
edb=# analyze;
ANALYZE;
edb=# explain analyze select col1 from mytable where col1 in ('a','b','c');
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using mt_idx on mytable  (cost=0.42..13.33 rows=3 width=6) (actual time=0.039..0.105 rows=3 loops=1)
   Index Cond: (col1 = ANY ('{a,b,c}'::text[]))
   Heap Fetches: 0
 Planning time: 0.223 ms
 Execution time: 0.211 ms
(5 rows)

If you think that the query planner is erroneously choosing a Sequential Scan, please share more details about your data and test case (though from my experience, whenever I question the query planner's choices, I'm usually wrong).

You may also wish to SET enable_seqscan = off and try the EXPLAIN again, and see the cost of using the Index, and compare with the cost of the Sequential Scan.


Questions about the output of "statistics IO"

Is the TOTAL number of physical reads a query did = [physical readsc] +
  [read-ahead reads]?

No. These are calculated separately.

physical reads:
Number of pages read from disk-Running thread go to runnable state and wait for the IO to finish.

read-ahead reads:

The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk.There are two kinds of read-ahead: one for data pages and one for index pages.

  Why sometimes we have [physical reads]=0 while the [read-ahead
  reads]>0? what does that mean?

As mentioned before read ahead pages are not included in physical read. This means all pages required are already in cache. Either the pages already existed or placed in cache by read ahead mechanism.

  How many pages SQL Server reads in one physical read? ... if it is one
  to one, then

It is not one to one. It will vary.  See reference section below.

  Why when "select * from aTable", the number of [physical reads] and/or
  [read-ahead reads] does not equal the table's number of pages?
  although the cache was cleared before executing the query.

As explained a physical read and read ahead can read more than one page at a time.

The read-ahead mechanism allows the Database Engine to read up to 64 contiguous pages (512KB) from one file. The read is performed as a single scatter-gather read to the appropriate number of (probably non-contiguous) buffers in the buffer cache. If any of the pages in the range are already present in the buffer cache, the corresponding page from the read will be discarded when the read completes. The range of pages may also be "trimmed" from either end if the corresponding pages are already present in the cache.

  why sometimes [logical reads] is greater than [physical reads] +
  [read-ahead reads]

Because your pages are already in buffer so reads in this case means logical read and you do not need (or you need less than) physical read + read ahead read.

  and even greater than the total number of pages of the table?

It is possible to read same page more than once while reading from cache. Think about seek operation on an index. If you have to do 10 seeks, you will always do 10 logical read on the root page and possibly 10 logical reads on the each intermediate level.

Reference:

SET STATISTICS IO (Transact-SQL)
Reading Pages
When does “physical reads” include read-ahead reads in SQL
Server? by Kendra Little
How It Works: Bob Dorr’s SQL Server I/O Presentation


Post Migration error in sql server error log

Those messages indicate checkpoint activity is occurring.  Checkpoints in SQL Server 2012 occur frequently, depending on several configuration variables, and are typically not something to be overly concerned about.

Check the recovery interval for your server:

SELECT c.name
    , c.value_in_use
FROM sys.configurations c
WHERE c.name = 'recovery interval (min)';

From the Microsoft Docs:

  The default recovery-interval value is 0, which allows the Database Engine to automatically configure the recovery interval. Typically, the default recovery interval results in automatic checkpoints occurring approximately once a minute for active databases and a recovery time of less than one minute. Higher values indicate the approximate maximum recovery time, in minutes. For example, setting the recovery interval to 3 indicates a maximum recovery time of approximately three minutes.

What is your recovery time set to?  Are you experiencing poor performance?


Database which was set up for logshipping is dropped

In order to fix alert issues use database: msdb and

First of all find out details on Primary Server by executing following..

PRIMARY SEVERE

SELECT * FROM dbo.log_shipping_monitor_primary
where primary_database like 'CHANGE_DB_NAME%'

SELECT * FROM dbo.log_shipping_primary_databases
where primary_database like 'DB_NAME_HERE%'

SELECT * FROM dbo.log_shipping_primary_secondaries
where secondary_database like 'DB_NAME_HERE%'

Once verifies DB names execute

delete FROM dbo.log_shipping_monitor_primary
where primary_database like 'DB_NAME_HERE%'

delete FROM dbo.log_shipping_primary_databases
where primary_database like 'DB_NAME_HERE%'

delete FROM dbo.log_shipping_primary_secondaries
where secondary_database like 'DB_NAME_HERE%'

Follow same steps on SECONDARY SERVER

SECONDARY

SELECT * FROM dbo.log_shipping_monitor_secondary
where secondary_database like 'DB_NAME_HERE%'

SELECT * FROM dbo.log_shipping_secondary_databases
where secondary_database like 'DB_NAME_HERE%'

SELECT * FROM [dbo].[log_shipping_monitor_primary]
where primary_database like 'DB_NAME_HERE%'

and once confirmed delete details from table by executing following....

delete FROM dbo.log_shipping_monitor_secondary
where secondary_database like 'DB_NAME_HERE%'

delete FROM dbo.log_shipping_secondary_databases
where secondary_database like 'DB_NAME_HERE%'

delete FROM [dbo].[log_shipping_monitor_primary]
where primary_database like 'DB_NAME_HERE%'

Hope this will help you to clear your unnecessary notifications from msdb database. It happens when you do not clear log-shipping clearly or in your case database dropped.


MySQL: filter date column using LIKE or MONTH?

The whole point of using DATE as a type is so the database can efficiently query the data. It's the same reason you store a number as an INT and not a VARCHAR - so the engine can make intelligent decisions. If you use the LIKE operator on a date, you lose the benefits of having chosen the correct data type.

Using MONTH(birthday) allows MySQL to grab the month portion of the birthday column that it knows adheres to the DATE data format. When you use LIKE, it's doing character-by-character pattern matching which is significantly costlier and slower to do.

If LIKE was sufficient, then MONTH() wouldn't exist. Any built in function is always going to be the choice over LIKE for a DATE query.


ER diagramming grew out of ER modeling.  ER models were intended to be application neutral, so that ER modeling could be applied to a given situation whether the ultimate implementation was to use relational databases or some other technology, such as the (then current) hierarchical databases or the CODASYL style network databases.  Since that time, hierarchical and network databases have been all but eclipsed by relational databases, at least as far as new work is concerned.  At the same time, various post relational data schemes have come up for fitting data management into an object oriented framework or an unstructured enviroment.

ER diagrams were, initially, depictions of an ER model.  Eventually, this mode of diagramming became so widespread that it began to be used for diagramming schemas designed under a relational model as well.  ERD actually works quite well in this manner.  The only confusion that results is when a person builds a relational model, and expects the same benefits out of it that you get from an ER model, or vice versa.

A relational model and an ER model differ in a few important details.  The use of foreign keys and junction tables are peculiar to relational models, and do not appear in ER models.  Nevertheless, they do appear in most of the ER diagrams you will see today.  That is the best clue that what you are really looking at is an ERD picture of a relational schema.

For years, I looked at ER models as though they were "relational lite".  It turns out that this is a misconception.  The two models are really looking at reality from different levels of abstraction.  Each model has its strengths and weaknesses.

As far as good references go, I would start with Wikipedia.  There are good articles in there about the ER model, the relational model, SQL, and some of the major DBMS products like Oracle, SQL Server, and many many others.

Once you understand the models and their uses, you'll be in good shape to tackle the diagramming conventions.


Getting the "Database does not exist" error message

Using your script, I hit the same error with a database called ಠ_ಠ.

I've made some changes to handle Unicode strings properly, and quote any odd characters in database names.

Ultimately, the problem was with your use of sp_sqlexec. When I changed it to sp_executesql, the script worked fine.

DECLARE @CurrentDB NVARCHAR(128);
DECLARE curDatabase CURSOR FAST_FORWARD FOR
    SELECT name
    FROM   master.sys.databases
    --SELECT * FROM master.sys.databases
    WHERE  name NOT IN ( 'master', 'tempdb', 'msdb', 'model', 'AdventureWorks', 'AdventureWorksDW' );
OPEN curDatabase;
FETCH NEXT FROM curDatabase
INTO @CurrentDB;
WHILE ( @@FETCH_STATUS = 0 )
    BEGIN
        --print @CurrentDB
        --
        DECLARE @sql1 NVARCHAR(MAX) = N'';
        SELECT @sql1 = @sql1 + N'USE ' + QUOTENAME(@CurrentDB) + N';

'       ;

        --EXEC sp_sqlexec @sql1
        --print @sql1

        DECLARE @sql2 NVARCHAR(MAX) = N'';
        SET @sql2
            = @sql2
              +
        -- Ensure a USE <databasename> statement has been executed first.
        N'
SET NOCOUNT ON

SELECT DB_NAME() AS DataBaseName;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''#work_to_do'') AND type in (N''U''))
DROP TABLE #work_to_do

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE 1 = 0;

select rtrim(name) as objectname,frag as frag_percent,type,create_date,objectid
from #work_to_do a , sys.objects b
where a.objectid=b.object_id
order by frag desc
'       ;

        --PRINT @sql2

        DECLARE @sql NVARCHAR(MAX) = N'';
        SET @sql = @sql + @sql1 + @sql2;

        PRINT @sql;
        EXEC sys.sp_executesql @sql;

        SET @sql = N'';
        SET @sql1 = N'';
        SET @sql2 = N'';

        FETCH NEXT FROM curDatabase
        INTO @CurrentDB;
    END; -- curDatabase WHILE loop
CLOSE curDatabase;
DEALLOCATE curDatabase;

Also, I'd strongly recommend against writing maintenance scripts like this from scratch. Ola Hallengren writes amazing free ones.


SQL Server Linux(Ubuntu) Connection Options

Shared Memory and Named Pipes protocols are only available with SQL Server on Windows.

Note the documentation links above call out "SQL Server (Windows only)" in the APPLIES TO header.


How do I reference a row which has two foreign keys as its primary key?

What I would do here is to have a table match_winner! It will be a JOINing table (also more formally called an ASSOCIATIVE ENTITY as well as many other names from the page).

I'm not quite sure why you have two tables called matches and games - are they not the same thing? If not, let me know and I'll modify the schema.

Something like this is what you require IMHO. It is a joining table between teams and matches

CREATE TABLE match_winner
(
  match_id INTEGER NOT NULL,
  team_id  INTEGER NOT NULL,
  winning_score INTEGER NOT NULL CHECK (winning_score > 0),

  CONSTRAINT 'match_winner_pk' PRIMARY KEY (match_id, team_id),
  CONSTRAINT 'mw_match_fk'     FOREIGN KEY (match_id) REFERENCES matches (match_id),
  CONSTRAINT 'mw_team_fk'      FOREIGN KEY (team_id)  REFERENCES teams   (team_id)
);

I made a small modification to your schema - I put a id INTEGER SERIAL into the teams table definition - check out the fiddle here. SERIALs are great! I also added a constraint CHECK (player1 != player2).

I then ran this SQL

SELECT t.player1, t.player2, m.id, mw.match_id, mw.team_id, mw.winning_score
FROM teams t
JOIN matches m ON t.player1 = m.team_1 AND t.player2 = m.team_2
JOIN match_winner mw ON m.id = mw.match_id;

You can check the result on the fiddle. Having the joining table avoids the use of NULL and provides added flexibility. Just a couple of recommendations - use upper case for SQL keywords and lower case for your own variables - or the opposite - just choose a standard and stick to it. You should also name your constraints as I did for the match_winner table - it's much easier to figure out error messages that way.

I hope this answers your question - if not, let me know! p.s. welcome to the forum! ;-)

Ooops - I nearly didn't notice the question. I reversed my modification of your teams schema - see the fiddle here, but the SQL is exactly the same. To use a two field PRIMARY KEY you just join on both fields instead of just a simple id one - et voilà! Of course, you can have the id and with a UNIQUE constraint, it's that bit easier sometimes - it's just that I wanted to give you the identites of the players. I've added a further fiddle here showing how you can alias a table twice in the same query to do useful things - here to join the names of player1 and player2 to your results.


Is using multiple foreign keys separated by commas wrong, and if so, why?

Yes it's a terrible idea.

Instead of going:

SELECT Deal.Name, DealCategory.Name
FROM Deal
  INNER JOIN
     DealCategories ON Deal.DealID = DealCategories.DealID
  INNER JOIN
     DealCategory ON DealCategories.DealCategoryID = DealCategory.DealCategoryID
WHERE Deal.DealID = 1234

You now have to go:

SELECT Deal.ID, Deal.Name, DealCategories
FROM Deal
WHERE Deal.DealID = 1234

Then you need to do stuff in your application code to split that comma list into individual numbers, then query the database seperately:

SELECT DealCategory.Name
FROM DealCategory
WHERE DealCategory.DealCategoryID IN (<<that list from before>>)

This design antipattern stems from either a complete misunderstanding of relational modelling (You don't have to be scared of tables. Tables are your friends. Use them), or a bizarrely misguided belief it's faster to take a comma-separated list and split it in application code than it is to add a link table (it never is). The third option is that they're not confident/competent enough with SQL to be able to set up foreign keys, but if that's the case they shouldn't have anything to do with the design of a relational model.

SQL Antipatterns (Karwin, 2010) devotes an entire chapter to this antipattern (which he calls 'Jaywalking'), pages 15-23. Also, the author has posted on a similar question over at SO. Key points he notes (as applied to this example) are:

Querying for all deals in a specific category is rather complicated (the easiest way to solve that problem is a regular expression, but a regular expression is a problem in and of itself).
You can't enforce referential integrity without foreign key relationships. If you delete DealCategory nr. #26, you then, in your application code, have to go through each deal looking for references to category #26 and delete them. This is something that should be handled at the data layer, and having to handle it in your application is a very bad thing.
Aggregate queries (COUNT, SUM etc), again, vary from 'complicated' to 'almost impossible'. Ask your developers how they'd get you a list of all categories with a count of the number of deals in that category. With a proper design, that's four lines of SQL.
Updates become much more difficult (i.e. you have a deal that's in five categories, but you want to remove two and add three other ones). That's three lines of SQL with a proper design.
Eventually you'll run into VARCHAR list length limitations. Although if you have a comma-seperated list that's over 4000 characters, chances are parsing that monster is going to be slow as hell anyway.
Pulling a list out of the database, splitting it up, and then going back to the database for another query is intrinsically slower than one query.

TLDR: It's a fundamentally flawed design, it won't scale well, it introduces additional complexity to even the simplest queries, and right out-of-the-box it slows your application down.


Inside stored procedure, set column names of returned query according to variable

You'll need to use dynamic SQL for this, if you truly want SQL Server to pass back results with variable column names.

By way of an example, consider the following:

Use tempdb to avoid any nasty surprises to existing objects:

USE tempdb;

Create a table to hold the sample data.  Since we are using dynamic SQL, we cannot use a table variable for this.

IF OBJECT_ID('dbo.Animals') IS NOT NULL
DROP TABLE dbo.Animals;

CREATE TABLE dbo.Animals
(
    AnimalType varchar(20) NOT NULL
    , AnimalName varchar(20) NOT NULL
);

INSERT INTO dbo.Animals (AnimalType, AnimalName)
VALUES ('Cat', 'Garfield')
    , ('Dog', 'Lucy');

GO

Create a stored procedure using dynamic T-SQL:

IF OBJECT_ID('dbo.DynamicResults') IS NOT NULL
DROP PROCEDURE dbo.DynamicResults;
GO
CREATE PROCEDURE dbo.DynamicResults
(
    @DogsOrCats varchar(3)
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @msg nvarchar(1000);
    IF (@DogsOrCats = 'Dog' OR @DogsOrCats = 'Cat')
    BEGIN
        DECLARE @cmd nvarchar(max);
        DECLARE @vars nvarchar(max);
        SET @vars = '@Type varchar(20)';
        SET @cmd = 'SELECT a.AnimalType
            , [NumberOf' + @DogsOrCats + 's] = COUNT(1)
        FROM dbo.Animals a
        WHERE a.AnimalType = @Type
        GROUP BY a.AnimalType;';
        EXEC sys.sp_executesql @cmd, @vars, @Type = @DogsOrCats;
    END
    ELSE
    BEGIN
        SET @msg = '@DogsOrCats must be either ''Dog'' or ''Cat''';
        RAISERROR (@msg, 14, 1) WITH NOWAIT;
    END
END
GO

Running the stored proc returns varying result sets, based on the value passed in as @DogsOrCats

EXEC dbo.DynamicResults 'Cat';

Returns:

+------------+--------------+
| AnimalType | NumberOfCats |
+------------+--------------+
| Cat        |            1 |
+------------+--------------+

EXEC dbo.DynamicResults 'Dog';

Returns:

+------------+--------------+
| AnimalType | NumberOfDogs |
+------------+--------------+
| Dog        |            1 |
+------------+--------------+


Manually entering data using Management Studio

You should be able to add new rows by right-clicking the table that you're interested in, and then selecting Edit Top 200 Rows.

If required, the value for this can be increased from 200 via the tools menu: Tools -> Options -> SQL Server Object Explorer -> Value for Edit Top <n> Rows command.

Presumably, the reason that they limit the number of rows returned is because this is not a robust way to manipulate data. The risk that you'll press the wrong key and accidentally overwrite important data is quite high.

If this isn't a live, production-critical database, and you're interested in regularly working with the data in a tabular/spreadsheet view, you may want to consider linking Microsoft Access to your database.


Mysql Procedure error while executing it

You are fetching into i, but not using it.
There is no end loop.
The insert is copying the whole table over repeatedly?

Consider using a single multi-table UPDATE, not a Stored Procedure with a loop.


Triggers in MySQL are synchronous. That means that the query submitter has to wait for the trigger to finish before it can resume its work.


The least disruptive method would be to use the general query log. The query log output can be a database table or a log (text) file; however it does not support filtering by DML operation or database so all SELECT statements on all databases will be logged as well. Obviously you can filter the logging output later.

Also note:

  The session sql_log_off variable can be set to ON or OFF to disable or enable general query logging for the current connection.

This means that with some injected logic, you could conceivably disable logging for SELECTs.

Another solution is to use triggers on UPDATE, INSERT, DELETE. This will directly affect the database in question but not other databases running in the same instance.

See also SO questions here and here.


Whenever there is a 1062 error, the usual table with the problem is the actual table being updated in the query. The query should appear in the output of SHOW SLAVE STATUS\G

For example, in your error it says Duplicate entry '174465' for key 'PRIMARY'. This indicates that you should look up the value 174465 in the table you are either doing an INSERT or UPDATE. If the row does exist, can you have to decide if the query halted execution will change the row's contents. If the query will simply reproduce the exact same contents, and you believe that will be the case, you can perform one of two options:

OPTION 1

Skip the error, wait 5 seconds, and view the Slave Status. Here the 5 steps for Skipping an Error

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

When you view the Slave Status, here is what to expect

If Seconds_Behind_Master is NULL

Replication is Broken : Look for Tell-Tale Signs
If Error Number is 1062 again, Repeat the 5 steps for Skipping an Error

If Seconds_Behind_Master is a Number

Replication is running
When Seconds_Behind_Master > 0, Replication is Catching Up.
When Seconds_Behind_Master = 0, Replication is Fully Caught Up.

OPTION 2

Remove the row to allow replication to continue

Delete the row from the table on the Slave and do the following 4 Steps for Skipping an Error:

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

At the risk of sounding redundant...

When you view the Slave Status, here is what to expect

If Seconds_Behind_Master is NULL

Replication is Broken : Look for Tell-Tale Signs
If Error Number is 1062 again, delete the row Repeat the 4 steps for Skipping an Error

If Seconds_Behind_Master is a Number

Replication is running
When Seconds_Behind_Master > 0, Replication is Catching Up.
When Seconds_Behind_Master = 0, Replication is Fully Caught Up.

What if there are just too many duplicate key issues? Here are some of my earlier posts concerning how to use MAATKIT's mk-table-checksum, mk-table-sync, pt-table-checksum, pt-table-sync:

In a MySQL Master/Slave replication, what would happen if I write to the Slave?
Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working
MySQL replication problem: slave has become out of sync


Is my implementation of type/subtype design pattern (for mutually exclusive subclasses) correct?

Yes, the design looks great. Minor notes:

You could use TINYINT, instead of the INT for the ClassTypeID. Or even CHAR(1) and have 'A' and 'B' instead of 1 and 2. 1 byte instead of 4 means you are saving 3 bytes in every row, in all 3 tables and in every index that includes ClassTypeID - which would be every index on these tables, if ClassTypeID is part of the clustered key.
The attributes of both the base and the subtype tables could be NOT NULL. I don't see why you would want them nullable, with this design.
It might be better (but needs thorough testing) if you had both the UNIQUE constraint of the base table and the 2 foreign keys that reference it defined with the reverse order (ClassTypeID, BaseClass_ID). This is more an indexing / physical design suggestion, it doesn't alter the logical design. I would experiment with having the clustered key in the base table using this order, too.


Seems simple.  Build the VIEW without the last 5 lines.  Then use those 5 lines when you use the VIEW as if it were a TABLE.

If those JOINs are not "many:one", you will get an inflated COUNT(*).


Ola Hallengren MaintenanceSolution - Differential - the value for @ModificationLevel is not supported

This indicates that you are hitting the validation (taken from Ola Hallengren's site here)

  IF @ModificationLevel IS NOT NULL AND NOT EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_file_space_usage') AND name = 'modified_extent_page_count')
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 1
  END

  IF @ModificationLevel IS NOT NULL AND @ChangeBackupType = 'N'
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 2
  END

  IF @ModificationLevel IS NOT NULL AND @BackupType <> 'DIFF'
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 3
  END

Please check the conditions and also keep in mind the @ModificationLevel is the difference needed for a Full backup to occur. If there is not enough difference it won't do the Full backup.

IF @ChangeBackupType = 'Y'
    BEGIN
      IF @CurrentBackupType = 'LOG' AND @CurrentRecoveryModel IN('FULL','BULK_LOGGED') AND @CurrentLogLSN IS NULL AND @CurrentDatabaseName <> 'master'
      BEGIN
        SET @CurrentBackupType = 'DIFF'
      END
      IF @CurrentBackupType = 'DIFF' AND (@CurrentDatabaseName = 'master' OR @CurrentDifferentialBaseLSN IS NULL OR (@CurrentModifiedExtentPageCount * 1. / @CurrentAllocatedExtentPageCount * 100 >= @ModificationLevel))
      BEGIN
        SET @CurrentBackupType = 'FULL'
      END
    END


MySQL performance impact of increasing innodb_buffer_pool_size

If you want to size the innodb_buffer_pool_size bigger please run this:

SELECT CEILING(SUM(data_length+index_length)/POWER(1024,2)) RIBPS
FROM information_schema.tables WHERE engine='InnoDB';

This will give you the maximum amount of InnoDB Buffer Pool needed for your current data set.

Given the amount of data and index pages for your dataset, having 8MB Buffer Pool will just cause data and index pages accessed to rotate out and load new pages as needed. Setting it with the value based on that RIBPS query (RIBPS stands for Recommended InnoDB Buffer Pool Size), all of InnoDB will be accessed from RAM. Having the Buffer Pool too big would just wastes RAM.

Let's say that you ran this query and got the following:

mysql> SELECT CEILING(SUM(data_length+index_length)/POWER(1024,2)) RIBPS
    -> FROM information_schema.tables WHERE engine='InnoDB';
+-------+
| RIBPS |
+-------+
|  1493 |
+-------+

Take that number and place it in /etc/my.cnf

[mysqld]
innodb_buffer_pool_size=1493M

and restart mysql.

I have discussed this before

Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Jan 11, 2012 : MySQL said "InnoDB: Initializing buffer pool, size = 128.0M", does it mean it is using 128MB RAM?
Jun 13, 2012 : What to set innodb_buffer_pool and why..?
Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?

Give it a Try !!!


Is it OK to use PRIMARY KEYs from one table as NAMEs of other tables?

If the data in each subset it truly independent and will never be reported upon together then modelling them as one table per entity type is perfectly valid. The fact that the table name will match a key in another table isn't something the database will care about - that table just becomes a list that tells the business logic what entity tables to expect to exist.

Even the "if you will never report on them together" can be relaxed to "if you won't need to report on them live together in an efficient manner" because there is the etl-to-datawarehouse option for offline analysis and less than efficient (but workable) options for ad-hoc reporting.

Of course the database layer is not going to be able to enforce the relationship between table name and data in another table: FKs can only be defined on data not schema elements.

I suspect that you don't want the data to be completely independent though. If I've understood you then what you describe is a classic example where simple table inheritance is applicable and what you want is something like:

                   Organisms            HumanModel1Details
OrganismTypes      ===============      ==================
=============      OrganismID (PK) <-<- OrganismID (FK)
Organism (PK) <-<- Organism   (FK)  |   SpecificDetail1
Model    (PK) <-<- Model      (FK)  |        ...
                   CommonProperty1  |   SpecificDetailN
                        ...         |
                   CommonPropertyN  |
                                    |
                                    |   RatModel4Details
                                    |   ================
                                    `<- OrganismID (FK)
                                        SpecificDetail1
                                             ...
                                        SpecificDetailN

Your business logic sill needs to know that Organism.OrganismType='Human' & Organism.Model='Model1' means that the details are in the table HumanModel1Details and the database can't help you enforce that. The common properties at very least include something like "display name" which will be used in your UI. Having OrganismTypes as a separate table is optional but helpful; the database can then use the FK in Organism enforce that each has a valid combination from those defined in OrganismTypes, and you can add properties to each type too (perhaps properties, or just a free text field, describing what differentiates it from other models.

You may wish to consider splitting by just Organism, not organism and model, or having two layers of inheritance (but that is probably "over modelling" and getting more complex than you need).

This is only speculation thought. As ypercube requests in his comment: please give more detail such as sample data so that we can better understand what you are asking. Use the "edit" link to add the detail to your question rather than responding via comments.

Search and read around the concept of "table inheritance", you'll find examples in many places as it is a pretty common pattern. Many ORM based frameworks such as Hibernate (see http://hibernate.org/orm/what-is-an-orm/) use it for certain model types.


Need best way to to avoid Replication Re-Configuration

What @Kin said is correct but may be time-consuming (if you have really big tables involved in replication) when you need to run Snapshot.

Here are the steps that you can omit the snapshot

Script out QA replication and change @sync_type parameter value (highlighed
below)

exec sp_addsubscription @sync_type = 'Replication Support Only', ... (other paramaters should remain the same)
Remove replication (you can write a script to do this, note, first
remove subscriptions then publisher, but do NOT remove distributor)
Restore PROD db on QA publisher
Restore PROD db on QA subscribers.
Run the replication setup script on QA publisher
Test your replication by inserting/updating a few test records and see whether the replication works as expected.

This link talks about another ways that can avoid snapshot and may be of help (but I never tried myself)

https://blogs.msdn.microsoft.com/repltalk/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication/

1st step of scripting out QA replication is one time work and you can actually automate all these steps (after step 1 modification is done) via t-sql scripts, and using sqlcmd.exe or powershell to drive the t-sql scripts. I used this way frequently before.


Since a query that would utilize the "date" index will implicitly contain a criteria for that "date" field (value(s) of certain type(s)), Mongo will decide what index/part of the index to use, making the fact of having multiple types of values in the same field moot (as far as indexing is concerned).

Here's an example:

I've added the following data to my Mongo 2.4.4 test db:

{ "_id" : ObjectId("52c679ac7a23e6225b62c51f"), "date" : ISODate("2014-01-06T03:48:30.737Z") }
{ "_id" : ObjectId("52c679b63cdecc6508c54fe5"), "date" : "2014-01-06T03:48:30.737Z" }
{ "_id" : ObjectId("52ca15677a23e6d3354fb7b4"), "date" : "2014-01-03 19:49:48" }
{ "_id" : ObjectId("42c679ac7a23e6225b62c51f"), "date" : ISODate("2014-02-06T03:48:30.737Z") }

Before index creation, any query on the date field (be it with a ISODate or a string criteria) will search through all the 4 documents, this can be confirmed by using explain():

> db.t1.find({date:ISODate("2014-02-06T03:48:30.737Z")}).explain();
{
        "cursor" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 1,
        "nscannedObjects" : 4,
        "nscanned" : 4,
        "nscannedObjectsAllPlans" : 4,
        "nscannedAllPlans" : 4,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {

        },
        "server" : "P09104:27017"
}

> db.t1.find({date:"2014-01-03 19:49:48"}).explain();
{
        "cursor" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 1,
        "nscannedObjects" : 4,
        "nscanned" : 4,
        "nscannedObjectsAllPlans" : 4,
        "nscannedAllPlans" : 4,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {

        },
        "server" : "P09104:27017"
}

Once I create the index via ensureIndex("date"), doing either a query of type date:"some string", date:ISODAte(...) or date in some range of ISODate values will only look through the pertinent values (2 for each of these queries):

> db.t1.find({date: {$gte: ISODate("2014-01-05T03:48:30.737Z"), $lt: ISODate("2014-02-07T03:48:30.737Z")}}).explain();
{
        "cursor" : "BtreeCursor date_1",
        "isMultiKey" : false,
        "n" : 2,
        "nscannedObjects" : 2,
        "nscanned" : 2,
        "nscannedObjectsAllPlans" : 2,
        "nscannedAllPlans" : 2,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
                "date" : [
                        [
                                ISODate("2014-01-05T03:48:30.737Z"),
                                ISODate("2014-02-07T03:48:30.737Z")
                        ]
                ]
        },
        "server" : "P09104:27017"
}

This is made more clear if we add even a 3rd type of value for date (numerical) and create something like this:

{ "_id" : ObjectId("52c679ac7a23e6225b62c51f"), "date" : ISODate("2014-01-06T03:48:30.737Z") }
{ "_id" : ObjectId("52c679b63cdecc6508c54fe5"), "date" : "2014-01-06T03:48:30.737Z" }
{ "_id" : ObjectId("52ca15677a23e6d3354fb7b4"), "date" : "2014-01-03 19:49:48" }
{ "_id" : ObjectId("42c679ac7a23e6225b62c51f"), "date" : ISODate("2014-02-06T03:48:30.737Z") }
{ "_id" : ObjectId("52ca15677a23e6d2354fb7b4"), "date" : 100 }
{ "_id" : ObjectId("52ca15677a23e6d2254fb7b4"), "date" : 400 }

Doing an $or seach for (let's say) ISODate and numerical values (exact match) and then doing an explain on that search will show that only 2 documents are scanned (out of the 6 indexed), showing that having mixed types of values for a field does not lower the efficiency of the index:

> db.t1.find( { $or: [ {date: ISODate("2014-01-06T03:48:30.737Z")}, {date: 100} ] } ).explain();
{
        "clauses" : [
                {
                        "cursor" : "BtreeCursor date_1",
                        "isMultiKey" : false,
                        "n" : 1,
                        "nscannedObjects" : 1,
                        "nscanned" : 1,
                        "nscannedObjectsAllPlans" : 1,
                        "nscannedAllPlans" : 1,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 0,
                        "indexBounds" : {
                                "date" : [
                                        [
                                                ISODate("2014-01-06T03:48:30.737Z"),
                                                ISODate("2014-01-06T03:48:30.737Z")
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BtreeCursor date_1",
                        "isMultiKey" : false,
                        "n" : 1,
                        "nscannedObjects" : 1,
                        "nscanned" : 1,
                        "nscannedObjectsAllPlans" : 1,
                        "nscannedAllPlans" : 1,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 0,
                        "indexBounds" : {
                                "date" : [
                                        [
                                                100,
                                                100
                                        ]
                                ]
                        }
                }
        ],
        "n" : 2,
        "nscannedObjects" : 2,
        "nscanned" : 2,
        "nscannedObjectsAllPlans" : 2,
        "nscannedAllPlans" : 2,
        "millis" : 0,
        "server" : "P09104:27017"
}

Doing a similar $or search with ranges (for both ISODate and numerical) will show that 4 results are obtained after only scanning 4 documents (the string values to not influence the utilization of the index):

> db.t1.find( { $or: [ {date: {$gte: ISODate("2014-01-05T03:48:30.737Z"), $lt: ISODate("2014-02-07T03:48:30.737Z")}}, {date: {$gte: 50, $lt: 500}} ] } ).explain();
{
        "clauses" : [
                {
                        "cursor" : "BtreeCursor date_1",
                        "isMultiKey" : false,
                        "n" : 2,
                        "nscannedObjects" : 2,
                        "nscanned" : 2,
                        "nscannedObjectsAllPlans" : 2,
                        "nscannedAllPlans" : 2,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 0,
                        "indexBounds" : {
                                "date" : [
                                        [
                                                ISODate("2014-01-05T03:48:30.737Z"),
                                                ISODate("2014-02-07T03:48:30.737Z")
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BtreeCursor date_1",
                        "isMultiKey" : false,
                        "n" : 2,
                        "nscannedObjects" : 2,
                        "nscanned" : 2,
                        "nscannedObjectsAllPlans" : 2,
                        "nscannedAllPlans" : 2,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 0,
                        "indexBounds" : {
                                "date" : [
                                        [
                                                50,
                                                500
                                        ]
                                ]
                        }
                }
        ],
        "n" : 4,
        "nscannedObjects" : 4,
        "nscanned" : 4,
        "nscannedObjectsAllPlans" : 4,
        "nscannedAllPlans" : 4,
        "millis" : 0,
        "server" : "P09104:27017"
}


Try the free underground PHP and Oracle manual
http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html


Query condtion for values not being the default value?

Yes, there is, in MySQL. You can use the DEFAULT() function:
SELECT column
FROM table
WHERE NOT (column <=> DEFAULT(column)) ;

Instead of <>, the NOT (.. <=> ..) construct that uses the null-safe equal operator <=> is needed for nullable columns, to have values returned when the default is NULL and null values returned when the default is not null.
Tested in SQLFiddle


How to check if column exists in case statement?

You can use INFORMATION_SCHEMA COLUMNS Table
But you can't use it in a normal sql statement, because mysql will parse all parts.
I still have problems to understand, why you have missing columns in the first place.
CREATE PROCEDURE `new_procedure` (IN _account_id1,IN _account_id2)
BEGIN
        IF (EXISTS (SELECT 1 FROM information_schema.COLUMNS
                    WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'conversation_facebook_page_sent '
                    AND COLUMN_NAME = 'message_id')) THEN
            SELECT
                ps.account_id,
                ba.account_name,
                NULL AS total_users,
                COUNT(DISTINCT(ps.uid)) AS unique_users,
                COUNT(DISTINCT r.msg_timestamp) AS messages_received,
                NULL AS total_clicks,
                NULL AS unique_clicks,
           COUNT(DISTINCT s.message_id) AS messages_sent,
                COUNT(DISTINCT CASE WHEN ps.added BETWEEN '2020-01-01' AND '2020-06-30' THEN ps.uid END) AS new_users
            FROM conversation_facebook_page_subscribers ps
                LEFT JOIN conversation_facebook_page_received r ON r.account_id = ps.account_id AND r.uid=ps.uid AND r.received_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN conversation_facebook_page_sent s ON s.account_id = ps.account_id AND s.uid=ps.uid AND s.send_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN business_assets ba ON ba.business_id = '3' AND ba.account_type = 'facebook_page' AND (ba.account_id = ps.account_id OR SUBSTRING_INDEX(ba.account_id, '_', -1) = ps.account_id)
            WHERE
                (ps.account_id='104522080903905' OR ps.account_id='180004075382887' OR ps.account_id='85825440048')
                AND
                (r.account_id IS NOT NULL OR s.account_id IS NOT NULL)
            GROUP BY ps.account_id, ba.account_name;
        ELSE
            SELECT
                ps.account_id,
                ba.account_name,
                NULL AS total_users,
                COUNT(DISTINCT(ps.uid)) AS unique_users,
                COUNT(DISTINCT r.msg_timestamp) AS messages_received,
                NULL AS total_clicks,
                NULL AS unique_clicks,
            COUNT(DISTINCT s.send_time) AS messages_sent,
                COUNT(DISTINCT CASE WHEN ps.added BETWEEN '2020-01-01' AND '2020-06-30' THEN ps.uid END) AS new_users
            FROM conversation_facebook_page_subscribers ps
                LEFT JOIN conversation_facebook_page_received r ON r.account_id = ps.account_id AND r.uid=ps.uid AND r.received_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN conversation_facebook_page_sent s ON s.account_id = ps.account_id AND s.uid=ps.uid AND s.send_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN business_assets ba ON ba.business_id = '3' AND ba.account_type = 'facebook_page' AND (ba.account_id = ps.account_id OR SUBSTRING_INDEX(ba.account_id, '_', -1) = ps.account_id)
            WHERE
                (ps.account_id='104522080903905' OR ps.account_id='180004075382887' OR ps.account_id='85825440048')
                AND
                (r.account_id IS NOT NULL OR s.account_id IS NOT NULL)
            GROUP BY ps.account_id, ba.account_name;
        END IF;

END


MariaDB (MySQL) Replication Slave Lags Behind a Certain Amount

Please show us all of SHOW MASTER STATUS; and SHOW SLAVE STATUS;.

Sometimes this helps:

STOP SLAVE;
START SLAVE;


Storage size for varchar length in Redshift

According to this page Redshift varchars use "4 bytes + total bytes for characters...". The varchar stored size is likely a much smaller issue with Redshift due to columns being compressed.


MongoDB 3.2 replica set with arbiter version 2.6

In general MongoDB only supports mixing adjacent major versions (eg. 2.6 and 3.0, 3.0 and 3.2, 3.2 and 3.4) in a replica set for the purposes of a rolling upgrade to a new version of MongoDB. Mixed version requirements are currently only mentioned in the upgrade notes but I expect the underlying assumption is that new deployments would always use start with the same version.

If you create a new replica set deployment using MongoDB 3.2, the replica set will be using a new election protocol ("version 1") that older versions of MongoDB do not support. For more information, see: Replication Election Enhancements.

The proper solution is to add a MongoDB 3.2 arbiter to your new 3.2 replica set. For any new deployment I would definitely recommend using identical versions of MongoDB to avoid unexpected issues.


What does Threads_connected=-1 signify in MySQL?

Perhaps you are seeing diff since last snapshot


Oracle alter table modify column from NVARCHAR2 to VARCHAR2

Please try this, its working for me.  Hope it helps you.

CREATE TABLE TEST_1
(
FIRSTNAME VARCHAR2(30)
);

INSERT INTO TEST_1(FIRSTNAME)
VALUES('NARENDRA');

COMMIT;

ALTER TABLE TEST_1
MODIFY FIRSTNAME  NVARCHAR2(30);

--Gives Error while modifying
/*ALTER TABLE TEST_1  MODIFY FIRSTNAME  VARCHAR2(30);8*/

ALTER TABLE TEST_1
ADD (FIRSTNAME2 VARCHAR2(30)
);

UPDATE TEST_1
SET FIRSTNAME2= substr(FIRSTNAME,1,30)

COMMIT;

ALTER TABLE TEST_1
DROP COLUMN FIRSTNAME;

ALTER TABLE TEST_1
RENAME COLUMN FIRSTNAME2 TO FIRSTNAME;


How to optimize and estimate DB recovery times?

What are the steps to take to optimize DB recovery time for a given SQL server database?

The best way is to upgrade to SQL Server 2019 and use Accelerated Database Recovery which provides:

Fast and consistent database recovery

Instantaneous transaction rollback

Aggressive log truncation

On older versions you can configure the recovery interval to force more aggressive checkpointing of dirty pages, but in-flight transactions will still need to be rolled back during a failover.  So you would also want to avoid long-running transactions during windows where you need fast failover.
Recovery time during large transactions might be optimized by temporarily switching the database to the Bulk Logged recovery model, but you'd need to ensure that the operations qualify for minimal logging.


One of the issues you face is that you can't mix data types within a single column. So while you can have numbers-as-strings in your varchar column, you can't easily do math-like functions on these strings-that-are-numbers.

However, with some casting and subqueries, you might be able to get the results you're looking for. The following works on SQL Server, though I'm not sure if it would behave the same on Access.

Here's a SQL Fiddle that demonstrates a solution, though it's probably far from ideal:
SQL Fiddle

If you run the inner query separately (

select
    value,
    case isnumeric(value)
        when 1 then round(cast(value as float), 0)
        else null
    end as valueint,
    case isnumeric(value)
        when 0 then value
        else null
    end as valuestring
from myvalues

) you'll see that it's selecting the raw value, a rounded number if applicable (otherwise null) and a string value if applicable (otherwise null), while the outer query is putting it all back together for you.

Hope this helps.


Email Column Constraint to Contain '@' character

Try this:
CREATE TABLE myTable ( email VARCHAR2(254) );
ALTER TABLE myTable ADD CONSTRAINT chk_email_format CHECK ( REGEXP_LIKE(EMAIL, '.{1,64}@.{1,255}'));

INSERT INTO myTable (email) VALUES ('abc abc.com')
Error report -
SQL Error: ORA-02290: check constraint (FLASHCARD.CHK_EMAIL_FORMAT) violated

254 total characters is from Errata ID: 1690 with 64 before the @ and up to 255 after it.
This simple regexp, email address must have a @, will alert you to attempts to insert wildly wrong data (perhaps helping catch a bug or two).  However, building a complete RFC 5322 validator is a difficult task.  For details take a look here:
https://stackoverflow.com/questions/201323/using-a-regular-expression-to-validate-an-email-address


SQL Server, how to execute a job against all databases on server?

Create a SQL Agent Job with a Job Step of type "Powershell" on the SQL Server instance where your databases reside.

# Define the query to be run against each database
Set-Variable -Name MyQuery -Value "SELECT @@SERVERNAME"

# Access SQL Server information by using a drive and file system analogy
Set-Location "SQLSERVER:\SQL\localhost\DEFAULT\Databases"

# Get the list of databases
Get-ChildItem | Set-Variable -Name DBList

# Iterate through the list, execute your query
ForEach ( $DB in $DBList) {
    Invoke-Sqlcmd -query $MyQuery -database $DB.Name -ServerInstance 'localhost' -ErrorAction Stop
}

Schedule it like any other job.


MySQL single large table vs many small tables

one big table with all these rows was a better way to go?

Yes, absolutely

With proper table structures and indexing, MySQL will comfortably handle millions of rows in any table.  The "Table-per-?" model is never a Good Idea and invariably comes back to haunt you (usually when you need to summarise across all those tables).


How to find the list of SQL Server jobs using PowerShell?

The easiest way is to install the DBATools library and use the command Get-DbaAgentJob.

  To return all SQL Agent Job on the local default SQL Server instance.

Get-DbaAgentJob -SqlInstance localhost

There is no explicit command to filter all scheduled jobs on the server, so we can use common features at the end of the script.

Get-DBAAgentJob -SqlInstance localhost -NoDisabledJobs | Where-Object {$_.HasSchedule -Match "True"}


What are some technical reasons for not including N-ary multiplication in the SQL standard?

Yes, the PostgreSQL core team has spent days deciding that this should not be included in core.

Seriously:

a mul operator is not in the SQL standard.
With PostgreSQL's extensibility, it is trivial to define such an aggregate function yourself:

CREATE AGGREGATE mul(integer) (
   SFUNC = int4mul,
   STYPE = integer,
   INITCOND = '1'
);


How can I query names using regular expressions?

Your question doesn't require regular expressions:

WHERE name LIKE 'A%' OR name LIKE 'B%'

For Oracle, there's REGEXP_LIKE; Postresql, there's SIMILAR TO; mysql has REGEXP.  One thing to remember though is not all regular expression engines are the same, so just because they support regular expressions doesn't mean they necessarily support word boundry assertions, non-greedy quantifiers, or negative lookbehind assertions, etc.


As the way your schema current sits, you'll need two parts; the main select statement and (since you are using MySQL, which doesn't support common table expressions), a view in the where clause.

The view, priveView:

SELECT product_id
, MAX (valid_since) [validSince]
FROM prices
GROUP BY product_id

The select statement:

SELECT SUM (P.Prices)
FROM Prices AS P
INNER JOIN priceView as PV ON P.product_id = PV.Product_Id AND PV.valid_since = P.valid_since

If possible I would change the schema of your prices tables so that it also includes a column of when the price starts in additional when it ends/changes.  Whatever the current price happens to be has it's "price_end" value set to null.  You'd then be able to avoid the need for an intermediate view that gets the sub-section of rows that apply.


SSIS: Working with 3 constrains where 2 should be OR and one should be AND

Here is my original solution for this problem, which apparently is not as bad as I thought. The only problem with it is that the designer canvas can get quite crowded.

I managed to achieve the desired solution with a little kludge, which gives me exactly what I want. However I can't help but feel a little dirty doing it, so I wonder if there is a better way. Here is a diagram of what I have done:

NOTE: It is important to emphasise that I need Main Script B to start as soon as Main Script A completes.

Script Task B1 is the one requiring the complex constraint. As you can see in the centre of the diagram, I grouped failure of Main Script A and completion of Script Task A2 in an empty container.

This allows for script tasks A1 and A2 to be bypassed if Main Script A fails, and Script Task B1 to be executed after previous script tasks have been completed. But Script Task B1 must wait for Main Script B to finish as well.

Here are the results using this cheap kludge:

When Main Script A succeeds:

When Main Script A fails:

I wonder if there are alternatives to this solution, though.


Mysql (win) Is it possible to get databases from multiple drives in the same time?

A given instance of MySQL can only use one datadir. Under the datadir, each MySQL schema lives in a subdirectory.
For example, the default datadir is C:\Program Files\MySQL\MySQL Server 8.0\data.
Schema1 would be under C:\Program Files\MySQL\MySQL Server 8.0\data\Schema1
Schema2 would be under C:\Program Files\MySQL\MySQL Server 8.0\data\Schema2
But if you could trick Windows into thinking that Schema2 subfolder actually uses storage from your physical drive D:, then you could achieve what you want.
One method is to use mklink as shown here: https://dev.mysql.com/doc/refman/8.0/en/windows-symbolic-links.html I would prefer this solution because it allows you to target a specific path on your D: drive.
A second option is to use virtual drives. Windows allows you to map drives to specific paths of another drive. Not many Windows users employ this option, they prefer to use drives as drive letters instead.
https://winaero.com/create-virtual-drive-from-a-folder-in-windows-10/
So you would use
SUBST D: C:\Program Files\MySQL\MySQL Server 8.0\data\Schema2

Read the article I linked to for more details.
Whichever solution you use, now files under Schema2 are automatically stored on your physical drive D:, and do not take up space on the physical drive C:.
What if you have already created the Schema2? So there is already a subdirectory for that schema's tables on C:. Well, you'll have to do some file juggling to make that work. I don't have a Windows machine so I can't test it or give advice on what the steps are.
Remember not to do any manual moving of MySQL tables around while the MySQL Server service is running. Stop the service first, or you will corrupt your data. Practice this sort of configuration on another server, not your important production data, and also make a backup of the whole database instance before you try anything.


How to print more than 8,000 characters at a time to the SSMS Message window, without compromising text formatting?

I have not personally used this technique, but you could try LongPrint.  From that post:

  This very simple procedure is designed to overcome the limitation in
  the SQL print command that causes it to truncate strings longer than
  8000 characters.

  It will print the text passed to it in substrings smaller than 8000
  characters. If there are carriage returns (CRs) in the text, it will
  break up the substrings at the carriage returns and the printed
  version will exactly reflect the string passed.

  If there are insufficient CRs in the text, it will print it out in
  blocks of 8000 characters with an extra carriage return at that point.

  If it is passed a null value, it will do virtually nothing.

  I developed a need to display very lengthy strings while trying to
  debug a script that generated a very long dynamic SQL section. When I
  did not instantly find a script to do this on SQLServerCentral.com I
  decided it would be faster to write one myself than search the broader
  internet.

Copying the code from that post:

CREATE PROCEDURE [dbo].[LongPrint]
      @String NVARCHAR(MAX)

AS

/*
Example:

exec LongPrint @string =
'This String
Exists to test
the system.'

*/

/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).

It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.

If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.

If it is passed a null value, it will do virtually nothing.

NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
 */

DECLARE
               @CurrentEnd BIGINT, /* track the length of the next substring */
               @offset tinyint /*tracks the amount of offset needed */

set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@String) > 1
BEGIN

    IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN

           SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
    END
    ELSE
    BEGIN
           SET @CurrentEnd = 4000
            set @offset = 1
    END

    PRINT SUBSTRING(@String, 1, @CurrentEnd)

    set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)

END /*End While loop*/


Filter array attribute in jsonb column type using jsonb_array_elements_text

You can use the contains all ?& operator with arrays:

select *
from r_fav
where favorites -> 'favoriteColors' ?| array['Blue', 'Red']

That will return rows at least one of the colors exist in the array. If want those where all colors are contained, use the operator ?& instead