How to INNER JOIN and OUTER JOIN within the same table?

The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with product. This creates a kind of cartesian (cross) product and will give wrong results in the SUM() calculations. To avoid that, you need to do the summations for the two tables in two different subqueries to avoid errors.

Something like this will work:

SELECT
    m.month,
    p.name,
    COALESCE(r.receipts_quantity, 0) AS receipts_quantity,
    COALESCE(s.sales_quantity, 0)    AS sales_quantity
FROM
    product AS p
  CROSS JOIN
    ( VALUES (1),(2),(3),(4),(5),(6) ) AS m(month)     -- months wanted in the report
  LEFT OUTER JOIN
    ( SELECT month, product_id, SUM(quantity) AS receipts_quantity
      FROM receipts
      GROUP BY month, product_id
    ) AS r
    ON  p.id_product = r.product_id
    AND m.month = r.month
  LEFT OUTER JOIN
    ( SELECT month, product_id, SUM(quantity) AS sales_quantity
      FROM sales
      GROUP BY month, product_id
    ) AS s
    ON  p.id_product = s.product_id
    AND m.month = s.month ;

If you want to avoid having results for products that did not have any activity in a month, you can add the condition:

WHERE r.month IS NOT NULL
   OR s.month IS NOT NULL


PostgreSQL streaming versus file-based replication (In terms of server behavior & configuration)

"Streaming replication" refers to continuous sending of WAL records over a TCP/IP connection between the master and the replica, using the walsender protocol over replication connections. The master reads its own WAL from pg_xlog and sends it to the replica on demand. It's configured with a primary_conninfo directive in recovery.conf and pg_hba.conf entries on the master to permit replication connections. You also need wal_keep_segments and some other options covered in the docs.

"Log shipping" refers to periodic sending of WAL records as whole WAL archives via a file transfer protocol to an archive location from which the replica can then fetch them. It's configured with a restore_command directive in recovery.conf and an archive_command in the master. PostgreSQL doesn't care where the files are or how they're transferred, only that the archive_command puts them there and the restore_command fetches the required archive; this permits the building of systems like PgBarman and WAL-E.

Streaming replication doesn't have as much lag, as records are sent as they are generated. However, it requires both master and replica to be online and able to communicate directly. It also requires the replica to keep up well enough that the master still has on-disk copies of the WAL the replica needs, and generally requires you to spend extra pg_xlog space on retaining extra WAL for the replica.

Log shipping replication has more lag because the replica only sees WAL once a whole archive is sent. However, it can work even when the master and replica can't communicate directly over TCP/IP by using a shared storage location. It continues to work even if the replica is down for a while, because the master will have discarded the WAL from pg_xlog only after archiving it, so the WAL is still in the archive and usable by the replica even though the master can't send it by streaming anymore. Note that archive_command never gives up, so pg_xlog can fill up if archiving is failing; for that reason it's better to archive to a reliable location and then have the replica server fetch from that location.

In general you actually combine the two, i.e. use both. In that case, streaming replication gets used when everything's going fine. If the replica gets too far behind and the master has discarded xlogs it requires, a connectivity problem arises, etc, then the replica will switch to reading archived WAL until it's caught up. It'll periodically re-try switching back to streaming until it succeeds.

If you're only going to use one, use log shipping, because streaming replication without log shipping fallback is (until PostgreSQL 9.4) potentially prone to replication lag causing failures that force a replica to be re-built .

PostgreSQL 9.4 changes this a bit, because streaming replication can now use "replication slots". That lets the master keep track of how much WAL a replica needs, and avoid throwing it away until the replica has replayed it. So there's no more need for wal_keep_segments if you use a replication slot (not the default).

See my article streaming replication slots in PostgreSQL 9.4.

9.4 also introduces the foundations for streaming logical replication, which is yet another mechanism, designed for use by logical replication systems like Londiste, Slony-I, and the new bi-directional async multi-master replication feature.


DBA Tasks ( SQL Server 2008 ) - Recommendation - Maintenance plan

It's a good pick to use it? If I was hired on a new company, it's a good pick to use maintenance plan ( for me, looks like too "simple", don't want to look a bad DBA).

Maintenance plans are not bad, but when your environment grows, the limited flexiblity and functionality that maintenance plans provide wont be sufficient.

For e.g Maintenance plans suffer from below drawbacks :

There is no CHECKSUM option when taking backup of databases or there is no option of verifying backups using CHECKSUM.
There is not much logic you can implement when dealing with index fragmentation and once you configure it, they run regardless if you really need a reorg or rebuild on your indexes... and many more :-)

I highly recommend to use Ola's backup solution (and Index maintenance  solution as well).


Why is it a bad practice to allow everyone to use the sa login?

You have several different questions in here, so I'll knock 'em out individually:

"I've read that it's a best practice to not let users use the sa login directly, instead using Windows Authentication"

You're mixing two things here: the concept of SA, and the concept of SQL authentication and Windows authentication.

SQL authentication is a list of usernames and passwords that are stored in each and every SQL Server.  The fact that it's stored in SQL is the first problem.  If you need to change a login's password, you have to change it on every server (or maintain different passwords on different servers).  With Windows authentication, you can centrally disable logins, change passwords, set policies, etc.

If you choose to use SQL authentication, then SA is just one SQL authentication login.  It's the default administrator username, just like Administrator is in Windows authentication.  It has local superpowers on that one instance, but not global superpowers across all instances.

"...and allowing those accounts (or account groups) sysadmin privileges."

Whatever authentication method you choose, ideally you want to follow the principle of least privilege: granting people the bare minimum rights they need to get their job done, and no more.

Don't think of them as just logins - they're people who can get you fired.  If they drop the database or disable your backup jobs accidentally, they're not going to get fired, because by default, SQL doesn't track who did what.  You're the one who will get fired because it's going to happen, and you're not going to be able to say which person did it.

"How does the best practice increase the security of my SQL Server instances?"

You want to do two things:

Stop people from breaking the server
When they do break the server, be able to identify exactly who did it

The first one is accomplished with the principle of least privilege: giving folks only the permissions they need, and nothing more.

The second one is accomplished by giving each person their own login, not allowing shared logins (like letting everyone use the same username/password), and ideally, auditing the logins.  You probably won't do that last part right away, because it's kind of painful, but let's put the pieces in place first so you can add auditing later after somebody drops a database and your boss wants to know why.

I know what you're thinking: "But we're coding apps, and the app needs a login."  Yes, give the application its own login, and the developers need to know that password, but that login should be so stripped of permissions that nobody in their right mind would want to use it.  For example, it might need to be in the db_datareader and db_datawriter roles alone, nothing else.  That way it can insert, update, delete, and select data, but not necessarily change schemas, add indexes, change stored procedures, etc.

"Does this only apply to production instances, or to our internal development instances, too?"

I think it applies just as much to development instances because I'm usually worried about people breaking things.  People just love to break servers in development.  And of course, when it's time to bundle up the list of changes to migrate to production, I need to know whether a particular index really is vital to the app or whether some bonehead just ran the Database Tuning Advisor and told it to apply all changes.  Proper permissions helps lessen that pain.


Rather than stick with that table, would creating a question table work for your application? That is, instead of having a separate table for each question, have a question table as

CREATE TABLE question (question_id numeric, question text)

and instead of the AFE table linking to a bunch of other tables, create a separate answers table as

CREATE TABLE answers(question_id numeric, afe_id numeric, answer text)

That acts as a bridge between AFE and question. This lets you add new questions easier too.


How do I turn off MariaDB GTID to revert back to binlog replication?

On each slave, run this. It'll do it without downtime and very little replication delay.

stop slave; change master to master_use_gtid=no; start slave;

You can watch show slave status \G, specifically "Using_Gtid, "Master_Log_File" and "Read_Master_Log_Pos". Assure the first is "No" and the other two are climbing after this command.


Implementing a query, two types of strategies

(1) is simple and straightforward.  It may be the most efficient.

(2) is a common speed-up.  But it shines when the non-indexed part (just i.label in your case) is bulky and/or the key into the table (i.id) is not the PRIMARY KEY.  So, if your example is watered down from the real code, (2) may outperform (1).

(3) is unlikely to be efficient, since it needs to reach into items 50~100K times.

Side issues:

Don't use CHAR unless the column really is fixed length; instead, use VARCHAR.

I assume you are using InnoDB.  (MyISAM, because of different handling of PRIMARY KEY, will be less efficient.)

If groups is a many:many mapping table, see my tips.  It will probably help (3) some.


How to analyze compression options for Azure SQL Database?

Kalen Delaney has created a version of sp_estimate_data_compression_savings specifically for this use case.

Its available for download from the corresponding blog post here.


How to update SQL Server 2012 from Evalutation to Developer edition?

You will need to re-download from your MSDN subscription.  The license keys are built into the ISO files.  When you do the installation you can selecte the edition and it will auto-populate the license key.


MySQL user per database performance implications?

Using different users will not reduce or increase the performance. It is clearly a security issue.

Usually you don't want users from company A to be able to access company B data.

I would recommend to create separate users for each application and grant them only the necessary permissions.

If you run the application using a user with admin rights you risk a lot when the application gets compromised.


ORA-31626: job does not exist during schema export

Try exp in stead of expdp
exp system/system@xxx.xxx.xxx.xxx:1521/orcl owner=schema-name file=/test_dir/Schema.dmp log=SchemaLog.log


i like to tell you in mysql , its also schema and database are different things.

schema is present under database.

means in two different database, same named schema may present.

i.e; in any one database this may possible -

create new schema sch1;
CREATE TABLE sch1.pet (name VARCHAR(20), owner VARCHAR(20),

create new schema sch2;
CREATE TABLE sch2.pet (name VARCHAR(20), owner VARCHAR(20),

in any 2nd database which having different connection ip have

above same schema  and  may possibly present..


Creating an incremental database copy in PostgreSQL

There isn't really anything like this.

You can use pg_basebackup or pg_dump to copy the original DB, but there's no copy-on-write facility. The built-in replication requires a shared-nothing copy and the replicas are read-only.

You can use 3rd party replication options like Londiste or Slony-I to replicate the state of the master to the dev machines, but these systems are a bit fragile, don't deal with DDL replication, and can have a significant impact the master server's performance.

Future PostgreSQL versions will hopefully have log-streaming logical replication which would be closer to what you want. It exists as a patch to the source code now, but at time of writing (Pg 9.3 release) it's in alpha stage development where it's validated for a couple of uses but not for general use.

I'd probably go a layer or two down. I'd use file-system copy-on-write snapshots, or even copy-on-write virtual machine images, to achieve what you want.


Query to get duplicate records then update it by putting a sequence number

create table data
(
  first_name varchar2(20 char),
  last_name varchar2(20 char),
  sequence_number number(10, 0)
);

insert into data values ('John', 'Smith', null);
insert into data values ('John', 'Smith', null);
insert into data values ('Jane', 'Doe', null);
insert into data values ('Jane', 'Doe', null);
insert into data values ('Jane', 'Doe', null);
insert into data values ('Rock', 'Lee', null);
commit;

select * from data;

FIRST_NAME           LAST_NAME            SEQUENCE_NUMBER
-------------------- -------------------- ---------------
John                 Smith
John                 Smith
Jane                 Doe
Jane                 Doe
Jane                 Doe
Rock                 Lee

--

select
  first_name, last_name, rowid,
  row_number() over (partition by first_name, last_name order by rowid) as sequence_number
from data
  where (first_name, last_name) in
  (select first_name, last_name from
    data group by first_name, last_name having count(*) > 1
  )
;

FIRST_NAME           LAST_NAME            ROWID              SEQUENCE_NUMBER
-------------------- -------------------- ------------------ ---------------
Jane                 Doe                  AAAJ7hAAEAAAAFjAAC               1
Jane                 Doe                  AAAJ7hAAEAAAAFjAAD               2
Jane                 Doe                  AAAJ7hAAEAAAAFjAAE               3
John                 Smith                AAAJ7hAAEAAAAFjAAA               1
John                 Smith                AAAJ7hAAEAAAAFjAAB               2

--

merge into data using
(select
  first_name, last_name, rowid,
  row_number() over (partition by first_name, last_name order by rowid) as sequence_number
from data
  where (first_name, last_name) in
  (select first_name, last_name from
    data group by first_name, last_name having count(*) > 1
  )
) data_with_sequences
on (data.rowid = data_with_sequences.rowid)
when matched then update set data.sequence_number = data_with_sequences.sequence_number;

5 rows merged.

--

commit;
select * from data;

FIRST_NAME           LAST_NAME            SEQUENCE_NUMBER
-------------------- -------------------- ---------------
John                 Smith                              1
John                 Smith                              2
Jane                 Doe                                1
Jane                 Doe                                2
Jane                 Doe                                3
Rock                 Lee


What connection string to use to connect to network server?

I found a solution. Maybe someone could expand on this, because I don't fully understand it.

When I opened Management Studio as MyMachineName\SQLEXPRESS which I normally do, attached the database there, I couldn't access it through MyMachineName\SQLEXPRESS in the connection string.

However, when I reopened Managment Studio as MyMachineName, attached the database there, and removed the \SQLEXPRESS from my connection string, I can now access it.

Problem solved.


Restoring a database that belonged to an AG

Looks like Brent answered it in a thread I hadn't seen:
SQL Availability Group restore database to standalone server

Short answer: Don't restore the master database of an AG.

I'm not happy with this....But it'll do.

Thanks, everyone!


Relationship design for areas, managers and general manager

It's ok to have an "ALL" value as an Area but it might be preferable and more flexible to add a third table, as a linking table, called EmployeeArea instead.
The EmployeeArea table is where you can store the area_id and employee_id (as manager_id), to represent the technically many-to-many relationship between the tables. Then the boss of all areas would have a record in there for each Area.
This is more normalized of a design, and is more flexible if for some reason there needs to be multiple bosses of all areas, or special cases where multiple employees are responsible for the same area.


How to get data from different table having same column?

UNION (Transact-SQL).

  Combines the results of two or more queries into a single result set
  that includes all the rows that belong to all queries in the union.

select T.f1, T.f2, T.f3
from [2016_01_01] as T
union all
select T.f1, T.f2, T.f3
from [2016_02_01] as T
union all
select T.f1, T.f2, T.f3
from [2016_12_01] as T;

One way to build the statement dynamically.

/*
-- Create tables
create table [2016-01-01](f1 int, f2 int, f3 int);
create table [2016-02-01](f1 int, f2 int, f3 int);
create table [2016-12-01](f1 int, f2 int, f3 int);
*/

declare @SQL nvarchar(max);

set @SQL = stuff((
  select
N'
union all
select T.f1, T.f2, T.f3
from ' + quotename(name) + N' as T '
  from sys.tables
  -- Get all tables where the name is a date
  where isdate(name) = 1
  for xml path(''), type
  ).value('text()[1]', 'nvarchar(max)'), 1, 13, '');

-- Print statement
print @SQL;

-- Execute statment
exec (@SQL);


SQLVDI error when backing up to Azure Storage BLOB

Crikey, this is one of those "no idea how I fixed it" things.

Before I went to lunch, I could reliably generate the error in question by running the provided T-SQL, yet when I came back from lunch the BACKUP command completed fine!

The main thing I remember changing was on the Azure side, where I created a SAS (Shared Access Signature).

This is supposedly not required for SQL 2014 as it uses an actual Azure Credential to connect to the storage instead. I actually created the SAS for an instance of SQL Server 2016 that I want backing up to the same container, and that may have opened the access pathway to the container for SQL Server 2014 too!

Many thanks to Sean Gallardy for recommending ProcMon, which showed a whole ton more error log information than the "ErrorCode=(5)Access is denied" message did.


Postgresql tables seem duplicated (with suffixes such us pkey, hashes, etc)

No, those are not "duplicated", those are additional relations/objects created for the tables.

reversion_revision_pkey is most probably the unique index supporting the primary key for the table reversion_revision.

And the others are most probably indexes as well.

You can add this expression to your query to see the actual type of the relation:

case relkind
   when 'r' then 'table'
   when 'i' then 'index'
   when 'S' then 'sequence'
   when 'v' then 'view'
   when 'm' then 'materialized view'
   when 't' then 'TOAST table'
   else relkind
end as rel_kind

  I imagine it should be enough with deleting this "duplicated" tables

No, do not delete (drop) those. They are essential for your database to work.

For a description on all columns in pg_class see here


MongoDB Replica Set member cannot start due "84 key/value already in index"

Do initial sync for that problematic secondary node. Just delete files from dbpath and start mongod. Node will do initial sync automatically. Other and faster way (if you can) is copy snapshot of dbpath of existing node (can be primary or secondary) and start mongod.

The problem here is that there is already entry at unique index and system tries to apply opLog line and crashes to that error.


local administrator does not have permissions to create database in db2

I had some chats with IBM and the issue is kind of simple. The db2 service is running as a local account (db2admin) just as a default installation would do. My account is a domain account and is in the local administrator group. The local account is not able to verify my domain account and therefore I am denied admin permission.
Solutions:

use a local account for connecting to the database
run db2 service under the local system account
run db2 service under domain account

I changed the setup to local system account. Waiting on IBM for letting me know what the security impact (risk) is.


Calculate total size of transaction log backups in the last 24 hours

You need to avoid casting your backup size to varchar or you will not be able to sum the values.  If you need to then you can do that conversion afterwards.

Other than that it is just a matter of wrapping the whole lot up as a subquery, grouping the results by database name, and doing the sum (You may be able to do it other ways but that seemed the least amount of re-working for me).

    select subquery.DATABASE_NAME,
        SUM(bkSize_KB) as SumBkSize_KB,
        SUM(bkSize_MB) as SumBkSize_MB,
        SUM(bkSize_GB) as SumBkSize_GB
from
(
        SELECT
           A.[Server],
            A.DATABASE_NAME,
           A.last_db_backup_date,
           B.backup_start_date,
           B.expiration_date,
           CAST(b.backup_size / 1000 AS INT) AS bkSize_KB,
           CAST(b.backup_size / 1000000 AS INT) AS bkSize_MB,
           CAST(b.backup_size / 1000000000 AS INT) AS bkSize_GB,
           B.logical_device_name,
           B.physical_device_name,
           B.backupset_name,
           B.description
        FROM
           (
           SELECT
               CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
               msdb.dbo.backupset.database_name,
               msdb.dbo.backupset.backup_finish_date AS last_db_backup_date
           FROM    msdb.dbo.backupmediafamily
               INNER JOIN msdb.dbo.backupset
               ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
           WHERE   msdb..backupset.type = 'l'
           --GROUP BY
           --    msdb.dbo.backupset.database_name
           ) AS A

           LEFT JOIN

           (
           SELECT
           CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
           msdb.dbo.backupset.database_name,
           msdb.dbo.backupset.backup_start_date,
           msdb.dbo.backupset.backup_finish_date,
           msdb.dbo.backupset.expiration_date,
           msdb.dbo.backupset.backup_size,
           msdb.dbo.backupmediafamily.logical_device_name,
           msdb.dbo.backupmediafamily.physical_device_name,
           msdb.dbo.backupset.name AS backupset_name,
           msdb.dbo.backupset.description
        FROM   msdb.dbo.backupmediafamily
           INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
        WHERE  msdb..backupset.type = 'l'
           ) AS B
           ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
        WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()

) subquery

group by subquery.DATABASE_NAME


MySQL View from different database with the same table schema

Simply reference whatever databases you want.  Note the "db.tbl" syntax:

CREATE VIEW ...
    AS
    SELECT ...
        FROM sales_2014.blah AS x
        JOIN sales_2015.blah AS y ON ...

Similarly, you could use UNION ALL.

CREATE OR REPLACE VIEW
    AS all_sales
        SELECT * FROM 2014.sales
    UNION ALL
        SELECT * FROM 2015.sales;

For 'sanity', you might want to create a new database to store the VIEWs in.  You might call it sales_all.


PowerShell to get all servers with SQL Server or Oracle installed

This is an easy way to find the SQL Servers with the dbatools module.

Install-Module dbatools
Find-DbaInstance -DiscoveryType All

Reference:
https://docs.dbatools.io/#Find-DbaInstance


Is there a postgresl server configuration that will let me expose an untrusted language like pl/R while still maintaining security?

I don't think that there is any general method to do this.  (Otherwise someone probably would have implemented a trusted version of the language using that method).

You could create a hot-standby database on a separate machine (or at least running as a separate and little-privileged OS-user) and let your users have at that with superuser permissions.  Note that they would be able to do nasty things to each other if they are clever and malicious enough, but should have limited capability to mess with the primary server.

Since a hot-standby is read only, you would have to set up the (super)user roles you want in the primary server, but disable them in the pg_hba.conf file.  Then use a different pg_hba.conf in the standby which allows those roles to connect.

I think it is kind of playing with fire to do even this much, though.


Cant restore database from A to B using any method in SSMS GUI

I would try:

USE master;
GO
ALTER DATABASE B SET OFFLINE WITH NO_WAIT

Note this will make the database inaccessible but guess that doesn't matter if you are going to restore over the top of it anyway.

If that fails then:

USE master;
GO
ALTER DATABASE B SET OFFLINE WITH ROLLBACK IMMEDIATE

But if a big transaction is in progress then it could take a long time to rollback.


Poor performance on a 'leaderboard' query

You need a combined index on rooms(embed_code_id,type).

Only one index can be used at a time. type is a poor index.


11g has the pivot construct, which appears to meet your needs:

with pivot_data as (
select buildnumber, testname, status
  from testresults)
select * from pivot_data
 pivot (min(status)
        for buildnumber
        in ('1' as build_1, '2' as build_2, '3' as build_3)
        )
order by 1;

Note the min(status), the pivot requires an aggregate function, so we're working around that requirement.

TESTNAME    BUILD_1 BUILD_2 BUILD_3
testname1   pass    fail    fail
testname2   fail    fail    pass
testname3   warning fail    fail


Are these two queries logically equivalent?

Whether the two queries you posted are logically equivalent is irrelevant; you shouldn't use either of them. I will try to steer you away from a couple of things:

Whenever possible, try to avoid applying functions to columns. It's always as good, and mostly better, to keep those calculations against constants and not columns - this can destroy SARGability and render indexes on those columns useless. In this case, I much prefer query 2, especially if LogDateTime is indexed (or might ever be).
I don't like the shorthand date math and I recommend against it. Sure, it's faster to type, but try that with a DATE data type and you will get an ugly error. Much better to spell it out, e.g.:

WHERE LogInsertTime < DATEADD(DAY, -7, @DateTime);


Is there any way to tell which derived column failed?

You have to manually identify the failures.

Redirect the rows (as you currently are) into a table that matches your columns, with the single addition of an IDENTITY column. This table should be all VARCHAR data types, so that you retain all original values.

Now you can identify your failures using TRY_CAST when querying the table.

For example:

SELECT
Identity,
TRY_CAST(Winter As INT) As WinterConverted,
Winter
--------------------------------------
Identity  |  WinterConverted   | Winter
   1      |    NULL            | NaN

TRY_CAST failed to cast the value in Winter column to an int leaving you with a null. You can do this for all columns.

If it's worth your time, you could make a more complex query to return each column name that contains a NULL. If not, you should be able to relatively easily visually inspect.

Of course, if most of your errors are 'NaN' or 'N/A', there's a good chance you're wasting time identifying errors you already knew about. Cleanup the issues before they become one:

Winter == "NaN" || Winter == "N/A" ? -1 : (DT_I4)Winter

As a side note, you can add a TRY_CAST to 100 columns very easily by using vertical editing: hold Alt+Shift in SSDT or SSMS (some other apps too) and then ↑ or ↓, then edit hundreds of lines at the same time.


Shrink Log File without breaking Log Chain

I vaguely recall you have to do something like this, since the SQL Server log file is written cyclically.

Make a log backup. This marks the active log space for reuse.
Shrink the log file. This will shrink it down to the end of the last virtual log file in use (which will probably leave it larger than what you wanted to shrink it to).
Since the end of the log file will now be in use, given that's where it stopped shrinking due to a VLF being in use, SQL Server will wrap back to the beginning of the log file for further log writes.
CHECKPOINT to make sure dirty pages for active transactions are flushed, then make a backup again. (The CHECKPOINT might not be necessary - you can try without first.)
Shrink the log file again. You should be able to bring it down to where you want.

If that still doesn't work, check DBCC OPENTRAN, and the log_reuse_wait column in master.sys.databases.


Command line arguments are subject to interpretation by the system's command shell, changing the behavior of the command or changing the value of the arguments before they are passed into the called program.

When an argument (such as the value for --password) contains an character that the shell may interpret, they need to be either quoted (usually enclosed in single quotes ' in unix or double quote " in Windows) or escaped individually (usually with a backslash \ before each metacharacter) to avoid interpretation by the shell.

While the specific characters are system-specific, some characters to watch out for include:

$ & ! \ [ ] < > `

If the password, for a really bad example, were set to pa$$word ...

mysql --password=pa$$word     # does not work
mysql --password='pa$$word'   # works
mysql --password=pa\$\$word   # works, but slightly unclear what's going on at first glance

Further reading:

http://www.gnu.org/software/bash/manual/bashref.html#Quoting
http://www.sal.ksu.edu/faculty/tim/unix_sg/shell/metachar.html
http://www.grymoire.com/Unix/Quote.html

Update: to escape either ' single or " double quotes in the password, you can either escape them with a leading backslash, or enclose the entire argument in the opposite style of quotes if there are no other characters that the chosen style of quoting isn't compatible with.

mysql --password="like'this" # password has a single quote in the middle
mysql --password='like"this' # password with a double quote in the middle

If you have a single quote and other special characters as well, you're stuck with backslash escaping because, in unix, the double quote is "weaker" than a single quote and many metacharacters are still expanded when enclosed in double quotes but not single quotes.

This is not MySQL-specific but applies to anything with command line arguments.

You can typically use the echo command to see how the shell is interpreting your arguments.

$ echo foo$bar
foo                # literal 'foo' plus the (empty) shell variable $bar

$ echo foo\$bar
foo$bar            # backslash prevents expansion of $bar as a variable

$ echo "foo$$bar"  # weaker double quote doesn't prevent expansion so
foo9691bar         # the $$ expands to the unix process id (pid) of the current shell

$ echo 'foo$$bar'
foo$$bar           # "stronger" single quote prevents shell expansion

$ echo "foo'bar"
foo'bar            # double quote allows single quote within the literal

Follow-up: The bash shell (and presumably some others) allows escaping single quotes within single-quoted strings, though the convention is bizarre (probably based on some long-forgotten decision lost now to the mists of time):

Replace each ' inside the string with '\'' before wrapping the entire string in single quotes... so the literal string foo'bar is expressed as 'foo'\''bar'.

Like I said, bizarre.  This is needed because a backslash escapes a single quote outside of a single quoted string,  a backslash escapes nothing inside a single-quoted string in bash, and single quoted strings can be closed and reopened by multiple single quotes as long as there are no unescaped intervening characters that have special meaning.  So '\'' closes the quoting of the string, then supplies the escaped literal, then reopens the quoting of the string.


In SQL Server, the default behavior with an Availability Group listener is to register ALL IPs in all subnets with DNS. While DNS has all the IPs attached to the listener's A Record, only the IP in the correct subnet for the current primary will be online. Other IPs will be in DNS for the network name, but will not actually be online/resolvable for any actual host.
With a default configuration, multi-subnet AGs require that the clients connecting to them include MultiSubnetFailover=true as a connection string attribute. This attribute tells the driver to expect DNS to provide multiple IP addresses for the Listener name, and to try all of them to find the correct IP to connect to for that network name. Clients that do not specify this attribute will get multiple IPs and not know how to handle them properly–most drivers will pick up one of the returned IPs at random (or maybe just seemingly random), and try to connect to that. This can result in random (or seemingly random) connection failures when it picks the wrong IP.
When failover happens, the DNS records do not change. Instead, the IP that is online & responding to requests will change. The former primary IP will go offline & stop responding, and a different IP will come online in a new subnet, and start handling traffic. This is why the client-side MultiSubnetFailover=true connection string attribute is necessary. The client's driver needs to know how to handle this.
You can turn off this behavior, so that DNS only registers a single IP address at a time, or even create two listeners so that one uses the default behavior and the other registers a single IP at a time. When using the single-IP listener, you do need to wait for the TTL to expire, and for the DNS updates to propagate before clients notice the failover & are able to reconnect. In this situation, you would want to set the TTL sufficiently low that the downtime is acceptable. Adjusting the TTL is ultimately the lever you can pull to prevent the DNS propagation from taking days.


How is a join performed by a database engine?

There are a number of ways depending on what the DBMS thinks you want versus what is helped in the database.

Read a row from the first table, then read any matching rows from the second table.
This is the preferred method when you are requesting a very few rows and there are indexes to support the read of the second table.
Matching index scan, select the required set from an index of the first table, then match this set to an index of the second table (usually after a sort), then fetch the required rows. Usually this method is used where a substantial number of rows are requested in a particular sequence.
Brute force, get all the rows from the first table and sort them into the right sequence, then get all the rows from the second table and sort them into the right sequence, then merge the results. Usually this method is used when there are no usable indexes to support the join. Its a performance pig and only used where nothing else will do.

There are many variations on these three methods which vary from RDBMS to RDBMS and the more expensive commercial databases have dozens of subtle optimizations which they will use depending on the circumstances.


I think the comments on the question answer it. I'm creating this so i can mark the question as answered.


Why VolumeQueueLength goes high for SQL Server in AWS

A disk queue length of 32 for a period of time on modern storage systems is most likely insignificant.

To correctly interpret disk queue length, you need to know how many physical disks are involved.  If the volume is spanned across 32 physical disks, for example, then you have an average disk queue across the disks of 1.  Since it's unlikely you can determine how many physical disks are really being used for the volume, it's difficult, if not impossible, to know what disk queue length number is going to be alarming until you can correlate it with SQL Server wait statistics showing that it is waiting on I/O.  Also, a high disk queue length can simply mean that there's a high load, and as long as performance is acceptable, then you've got nothing to fix, but just note what the disk queue length is as a benchmark.

Generally you need to first look at SQL Server waits to determine if there is a bottleneck, and what the bottleneck is.  Check out articles such as Resolving SQL Server Disk IO bottlenecks to get started on this subject.  If you see I/O waits increasing, then you'll probably also see the disk queue length increasing.

So that answers question number 2.  To answer question number 1, it's simply a matter of how many disk requests are waiting to be serviced.  If you have faster disks, or more disks, you'll be able to throw a higher load at it before it a the disk queue starts to increase.  There are so many factors--for example, if you have only one slow CPU, the application won't be able to process much data, and you'll have almost no disk queue, but performance will be horrible.


How to design database tables using normalization in better manner in SQL?

The table structure that your colleagues have suggested might be appropriate for a data warehouse, but it would typically not reduce I/O in an OLTP database.

Consider this--if you run a query for all of the customers in a city that returns 1,000 rows, with the denormalized table, the database engine will have to read the CityName, StateName, and CountryName 1,000 times.  Fewer rows will fit on a database page, so it will end up reading more pages to fulfill the query.

With normalized tables, it will only have to read the IDs for those columns (which is very small compared to text fields), and read the actual CityName, StateName, and CountryName only once.  More rows will fit on a database page, so fewer pages will need to be read to fulfill the query.

Additionally, think what the denormalized table does to the buffer.  If there are 1,000 customer records in the buffer, then the city, state, and country names are also in the buffer 1,000 times.  With normalized tables, those names are only in the buffer one time, using much less space.  So you'll have more data in buffer with normalized tables.


Issue with Ñ character in name (migrated data from MySQL to PostgreSQL)

You can start troubleshooting this by looking at the bytes behind the characters, to check if there are invalid UTF-8 contents independently of the display layer.

An UTF-8 string can be converted into its series of bytes in SQL with

SELECT convert_to(value, 'UTF-8');

You may also split a suspicious string character by character to look at each codepoint individually, with the character, the Unicode codepoint, and the UTF-8 representation

SELECT c, to_hex(ascii(c)), convert_to(c, 'UTF-8') FROM
   regexp_split_to_table('El Niño', '') AS c;

 c | to_hex | convert_to
---+--------+------------
 E | 45     | \x45
 l | 6c     | \x6c
   | 20     | \x20
 N | 4e     | \x4e
 i | 69     | \x69
 ñ | f1     | \xc3b1
 o | 6f     | \x6f

I would try this on the  fields where some characters display as ? and compare with the fields where the characters display correctly. The goal is to identify what's exactly behind the ?.


How to find historical sql_id executed by users

AWR captures only the top N sql for each category (Elapsed time, CPU time, I/O time, Gets, Reads, etc.).

If you set this limit to MAXIMUM with DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS, even that does not guarantee capturing all SQLs as they may age out from the shared pool between taking AWR snapshots.

If you want all SQLs to be logged, you can use SQL tracing (Monitoring and Tracing SQL) or Auditing (Monitoring Database Activity with Auditing) with the settings that fulfill your requirements.


You have SELECT SSF.ST_ItemSize after GROUP BY SSF.SM_StockCode, so you are using a column without aggregation in the SELECT list, a column that is not in the GROUP BY list.

This is not valid SQL by the 1992 standard.

Since ST_ItemSize is neither the primary key nor it has a unique constraint, the ST_ItemSize is not functionally dependent on it. This makes the SQL code invalid by the ISO/ANSI standards even in the 2003+ versions and will give indeterminate results (in the ST_ItemSize column).

MySQL unfortunately allows such queries (does no dependency check) and is the duty of the developer to ensure that a query will give determinate results.

So I would advise to remove this query from your code, discuss with the developers that wrote it or the ones that use it and find out what results it is supposed to return. Then you can rewrite it.

If you still want to have it running until you do that, the following will produce similar results - and will gain efficiency by an index on (ST_StockCode, ST_ItemSize, SM_StockCode):

SELECT ssf.SM_StockCode, MIN(ssf.ST_ItemSize) AS ST_ItemSize
FROM  Stock AS ssf
  JOIN
  ( SELECT ST_StockCode, MIN(ST_ItemSize) AS ST_ItemSize
    FROM  Stock
    GROUP BY ST_StockCode
  ) AS g
  ON  g.ST_StockCode = ssf.ST_StockCode
  AND g.ST_ItemSize = ssf.ST_ItemSize
GROUP BY ssf.SM_StockCode ;


Just in case somebody else needs, I am posting the solution I used. Instead of using fromuser and touser options, I used remap_schema option:

> impdp user1/password DIRECTORY=backup_dir DUMPFILE=Dump.dmp FULL=Y
> remap_schema=user2:user1

Also, if the tablespaces are different, they must be remapped, too:

> impdp user1/password DIRECTORY=backup_dir DUMPFILE=Dump.dmp FULL=Y
> remap_schema=user2:user1 remap_tablespace=table2:table1


assistance with mongodb setup on windows server

You shouldn't scan the folder in which mongodb stores it's data files, as this will degrade performance. The location is set by using the --dbpath C:\Where\You\Want\The\Files flag at runtime.

With regards to best practices for clustering, I'd recommend taking a look at the Infrastructure Requirements for a Sharded Cluster page.

At a minimum, you will need three config servers, two or more shards and one or more MongoSes


Restoring database to UNC path on local drive

Sounds like "access is denied" -  make sure that the SQL Server service account can access the share with both read and write (whether you can access the share is irrelevant). If you did not create a share named D that points to the root of D:, then your syntax is wrong - you need to use D$. However I recommend creating a share that is NOT in the root of a drive, and making sure that the SQL Server service account (again, not you unless you manually set the SQL Server service account to run as your Windows account) has read and write access. Then it would be:

MOVE N'test'     TO N'\\PC91\ShareName\Data\test.mdf',
MOVE N'test_log' TO N'\\PC91\ShareName\Log\test.ldf'

However I have to wonder why you would want to use a UNC path for this in the first place. It means your database files are more at risk because the share can be removed or become temporarily unavailable while SQL Server is running.


Need for recursive lookup, stuck in query design

Data model

First of all: fold duplicate entries. I don't see why you would allow reverse duplicates to begin with, so your setup becomes:

CREATE TEMP TABLE tbl(
     pk serial PRIMARY KEY
   , userid int NOT NULL
   , partneruserid int NOT NULL
   , ct int DEFAULT 1 NOT NULL  -- this part is unclear
   , CONSTRAINT user_partner_uni UNIQUE (userid, partneruserid)
   , CONSTRAINT userid_smaller   CHECK  (userid < partneruserid)
   );

INSERT INTO tbl
VALUES
  (1,1,2,1)
, (2,1,3,2)  -- folded
, (3,1,6,1)
, (4,2,5,1)
, (5,2,6,2)  -- folded
, (7,1,4,1)
, (8,3,5,1)
, (10,3,6,1);

Recursive query

Building on the sanitized model (the constraint userid_smaller is not required for this query, though). This recursive query does basically everything you need:

WITH RECURSIVE  ids (a, z) AS (SELECT 1, 3)  -- enter values here
, cte AS (
   SELECT t.pk, CASE i.a WHEN t.userid THEN t.partneruserid ELSE t.userid END = i.z AS found
        , ARRAY[CASE i.a WHEN t.userid THEN t.partneruserid ELSE t.userid END, i.a] AS path
   FROM   tbl t, ids i
   WHERE  i.a IN (userid, partneruserid)   -- constraint user_id_smaller simplifies this task

   UNION ALL
   SELECT t.pk
        , CASE c.path[1] WHEN t.userid THEN t.partneruserid ELSE t.userid END = i.z AS found
        , CASE c.path[1] WHEN t.userid THEN t.partneruserid ELSE t.userid END || c.path
   FROM   cte c, tbl t, ids i
   WHERE  c.path[1] IN (t.userid, t.partneruserid)
   AND    CASE c.path[1] WHEN t.userid THEN t.partneruserid ELSE t.userid END <> ALL (c.path)
   AND    NOT c.found
   AND    array_length(path,1) < 4
   )
SELECT path, array_length(path,1) - 1 AS steps
FROM   cte c
WHERE  found;

SQL Fiddle

Except for the count. It's unclear how you count duplicate connections exactly. Strictly speaking, you should multiply ct with every step ...

Explanation

The adapted table forces userid to be the lower ID, so the initial predicate can be simplified: userid matches lower ID, partneruserid matches higher ID. If your input is parameterized, you can sort with LEAST (x,y) and GREATEST(x,y) Update: That shortcut only works for short-circuiting in a single step. We need to consider both IDs as starting point for longer paths. Query fixed.

I pick the lower input value (a) as start (could be the other way round) and walk the graph of partnerships until I reach the higher input value (z). Each step can connect to userid or partneruserid, which necessitates the CASE expressions: the match could be on either column, we need to continue with the other column respectively.

We have to remember the path so far (path) and rule out repetitions so not to walk in circles (... <> ALL (c.path)).

I save the last node of the path as first element (path[1]), just to avoid another column for that information. Either way may be more convenient / faster ...

Stop traversing the graph when z is reached (found) or when a given maximum length for the path has been exhausted (array_length(path,1) < 4).

Only rows where z is reached (WHERE  found) are in the final result set. I only collect the bare minimum of information along the way. You can add more columns to the query or / and  JOIN to the original table in the outer query to return more fields.


Is it possible to automate the creation of multiple FEDERATED tables in MySQL?

There's not an internal way to do this, but it's easy enough to automate with a script, like the following:

#!/usr/bin/env perl
use strict;
use warnings;
use DBI;

my $sleep_between = 1; # artificial delay; set to zero to remove

my $origin = 'origin_server_hostname';
my $oport = 3306; # origin server port
my $ou = 'origin_server_username';
my $op = 'origin_server_password';

my $fu = $ou; # or change, if federation needs a different user
my $fp = $op; # or change, if federation needs a different user

my $target = 'target_server_hostname';
my $tport = 3306; # target server port
my $tu = 'target_server_username';
my $tp = 'target_server_password';

warn "connecting to origin server";
my $odbh = DBI->connect("DBI:mysql:information_schema;host=$origin;port=$oport","$ou","$op",{RaiseError => 1, mysql_auto_reconnect => 0});
warn "connecting to target server";
my $tdbh = DBI->connect("DBI:mysql:information_schema;host=$target;port=$tport","$tu","$tp",{RaiseError => 1, mysql_auto_reconnect => 0});

warn "fetching origin tables...";
# change the query to select only the tables you want to federate; existing tables won't be overwritten
my $otables = $odbh->selectall_arrayref(qq{
        SELECT table_name, table_schema
          FROM information_schema.tables
         WHERE table_type = 'BASE TABLE'
           AND table_schema NOT IN ('mysql','information_schema','performance_schema')
},{Slice => {}}
);

my $prev_db = '';
foreach (@$otables)
{
        my ($os,$ot) = ($_->{table_schema}, $_->{table_name});
        my ($ts,$tt) = ($os,$ot);

        my $sct = $odbh->selectrow_arrayref(qq{SHOW CREATE TABLE `$os`.`$ot`})->[1];
        $sct =~ s/^CREATE\sTABLE\s`$ot`/CREATE TABLE IF NOT EXISTS `$ts`.`$tt`/ || die "regex replacement failed: $sct";
        $sct =~ s/\)\sENGINE=\S+\s/) ENGINE=FEDERATED / || die "regex replacement failed: $sct";
        my $fcs = "CONNECTION='mysql://$fu:$fp\@$origin:$oport/$os/$ot'";
        $sct .= ' ' . $fcs;
        warn "$sct\n\n";
        if($prev_db ne $ts)
        {
                my $dbdef = $odbh->selectrow_arrayref(qq{SHOW CREATE DATABASE IF NOT EXISTS `$os`})->[1];
                print STDERR "$dbdef ... ";
                print STDERR ($tdbh->do($dbdef) ? 'ok' : 'error') . "\n\n";
                $prev_db = $ts;
                sleep $sleep_between;
        }
        print STDERR "creating table ${ts}.${tt}... ";
        print STDERR ($tdbh->do($sct) ? 'ok' : 'error' ) . "\n\n";
        sleep $sleep_between;
}

$odbh->disconnect;
$tdbh->disconnect;

warn "done";
# # #

Save the script to a file, e.g. federation-automate.pl.

Modify the variables at the top to specify the origin (server with the base tables) and target (server where you want to create federated tables) hostnames, ports, usernames, and passwords.  Modify the information_schema query to select fewer tables, if you don't want to federate the whole server.

All modern systems have Perl, but you need DBD::mysql for Perl, if you don't have it.  On Ubuntu, that's...

$ sudo apt-get install libdbd-mysql-perl

Then run the script.

$ perl federation-automate.pl

It loads the table definitions from the origin, and rewrites the SHOW CREATE TABLE statements to build federated tables.  It will also attempt to create the database containing each table, if the database doesn't already exist on the target server.

Note that federated tables don't actually support foreign key constraints, ROW_FORMAT=COMPRESSED, and some other common table options... however, table definitions containing these elements are still valid, and will be accepted, by the federated engine, so there's no need for this code to scrub them out.

Minor modifications to the code would allow you to change the database and/or table names on the target server if you didn't want them to be the same as the origin, for some reason.


How to most effectively get the first preceding/subsequent entry outside a given time interval?

I assume you query for all data (from all stations) in a specific time range.

You can probably write this with window functions (and it might be more efficient) but here is one method, with OUTER APPLY.

(The ds subquery is only used to get a list of distinct StationID. It should be replaced with the Stations table you likely have):

SELECT
    ds.[StationID],                   -- station
    st.ExtendedStartDate,             -- extended start and
    en.ExtendedEndDate                -- and end dates

    tt.[Timestamp],                   -- timestamps and data
    tt.[DatapointId],
    tt.[Value]
FROM
    ( SELECT DISTINCT [StationID]
      FROM ArchiveTable
    ) AS ds
  OUTER APPLY
    ( SELECT TOP (1) s.[Timestamp] AS ExtendedStartDate
      FROM ArchiveTable AS s
      WHERE s.[StationId] = ds.[StationId]
        AND s.[Timestamp] < @StartDate
      ORDER BY s.[Timestamp] DESC
    ) AS st
  OUTER APPLY
    ( SELECT TOP (1) e.[Timestamp] AS ExtendedEndDate
      FROM ArchiveTable AS e
      WHERE e.[StationId] = ds.[StationId]
        AND e.[Timestamp] > @EndDate
      ORDER BY e.[Timestamp] ASC
    ) AS en
  OUTER APPLY
    ( SELECT t.*
      FROM ArchiveTable AS t
      WHERE t.[StationId] = ds.[StationId]
        AND t.[Timestamp] >= COALESCE(st.ExtendedStartDate, @StartDate)
        AND t.[Timestamp] <= COALESCE(en.ExtendedEndDate, @EndDate)
    ) AS tt ;


Large update taking too long; looks like it is updating more records than I have

Well, after a few iterations your @Updates table variable probably has become huge (since you're inserting data into it in every iteration ), and this is probably leaking memory. Plus, on every iteration, you're doing an EXISTS check for every single row in the tables which are a product of the join statement, against the @Updates table.

Might I suggest this approach here for the while part of your code, it works because the [rn] column is sequential, so you actually have a index you can just use for the chunk based update

--Keep this original code as is --
USE IMB_TraceData
GO

IF OBJECT_ID('tempdb.dbo.PRIMARY KEY (lname,') IS NOT NULL
    DROP TABLE #MaxDate;
GO

-- ******************************************
-- Create Work Table
-- ******************************************
SELECT * INTO #MaxDate
FROM
    (
    SELECT  RowNum = row_number() OVER (ORDER BY (SELECT 0)),*
    FROM    (
            SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY TR_SerNum
            ORDER BY TR_Scan_Date DESC) AS rn
            FROM  Trace_Return_Table
            ) a
    WHERE   a.rn = 1
    ) AS n
GO
CREATE CLUSTERED INDEX IDX_RowNum ON #MaxDate(RowNum)
CREATE INDEX IDX_SerNum ON #MaxDate(TR_SerNum)
GO
-- original code ends --

-- modified code follows --
DECLARE @tRow BIGINT = 0
DECLARE @tMax BIGINT = (SELECT MAX(RowNum) FROM #MaxDate)

WHILE @tRow< @tMax
        BEGIN
          set @tRow = @tRow + 10000
            BEGIN TRANSACTION
            UPDATE t
            SET TR_Scan_Date = n.TR_Scan_Date,
                TR_Facility_Id = n.TR_Facility_Id,
                TR_Operation_Code = n.TR_Operation_Code
            FROM IMB_TBO_Data.dbo.The_Big_One t
            INNER JOIN #MaxDate n ON t.HT_SerNum = n.TR_SerNum
            WHERE n.RowNum between (@tRow - 10000) AND @tRow

            COMMIT TRANSACTION
        END

My bet is, this will run a lot faster.


Do I need to have enterprise server in order to have multiple data sources in SSRS

There is no specific requirement for Enterprise Edition, though Express will not work either, which leaves Standard as your realistic option.

See Features Supported by the Editions of SQL Server 2008 R2.

Express Edition does have limitations:

Allowed data source SQL Server edition is SQL Server Express only.

The is no Remote and non-relational data source support.

Web and Workgroup have similar limitations.

You can see from that list there is no specific limitation against the number of Data Sources.


Heroku has the features you require:

https://postgres.heroku.com/


Can we download SSRS 2017 to use with SQL Server 2016?

You can download and install SSRS 2017, and use an existing SQL Server to store the Report Server database.  The databases can be on any supported version of SQL Server, as documented here.

The server running SSRS 2017 must, of course, be licensed per the licensing guide here.

Alternatively you can get this functionality (and more), with Power BI Report Server, which can be acquired via Power BI Premium, or run using SQL Server Enterprise Edition Core Licenses covered by active Software Assurance.


mysql select count on same table slow subquery

What is the datatype of clldate and calldate?  If they are both DATE, get rid of the function calls (DATE()) around them; it prevents use of indexes.

If wither or both is DATETIME (or TIMESTAMP), then we need to rephrase the query to make it more efficient.

Add compound index:
INDEX(caller_id, calldate)

If calldate is DATE, then also add
INDEX(calldate, caller_id)

How big is cdr?  How big will it eventually become?

Does the performance improve much is you get rid of SQL_CALC_FOUND_ROWS?  Consider whether you really need it.

Will you be purging 'old' data?

After you have answered my questions, I will consider whether PARTITIONing is worth doing.


12.0 Access Database Engine OLD DB Provider Not showing up in SQLEXPRESS 2008

For a 64bit build of SQL to use the Access drive you'll need to install the 64bit version of the Access drive.  If you have the 32bit version of Office installed you'll need to either remove that and install the 64bit version of office or uninstall the 64bit build of SQL and install the 32bit build.


Because of filtering on two tables, it is not possible for a single index to handle all the WHERE, to handle the ORDER BY, much less optimize theLIMIT 10`.

The LEFT JOIN seems to be a JOIN.

This link on comments may help:  INDEX(dateAdded, linkId, id) (in that order).

If you want to discuss further, please provide EXPLAIN SELECT ... and EXPLAIN FORMAT=JSON SELECT ...


You simply need to think about the date range slightly differently.  An event falls in your given range if the start date is prior to the end of your reporting period, and the end date is after the beginning of your reporting period:

Select * from tbl_Events
where StartDate <= @end
    AND EndDate >= @start;

For example:

CREATE TABLE MyTestDates
(
    MyTestDateID INT NOT NULL PRIMARY KEY CLUSTERED CONSTRAINT PK_MyTestDates IDENTITY(1,1)
    , StartDate DATETIME
    , EndDate DATETIME
    , EventName NVARCHAR(255)
);

INSERT INTO MyTestDates (StartDate, EndDate, EventName)
VALUES ('2012-01-27', '2012-01-30', 'Test Event 1');

INSERT INTO MyTestDates (StartDate, EndDate, EventName)
VALUES ('2012-01-27', '2012-02-28', 'Test Event 2');

INSERT INTO MyTestDates (StartDate, EndDate, EventName)
VALUES ('2011-01-01', '2012-03-22', 'Test Event 3');

DECLARE @FromDate DATETIME;
DECLARE @ToDate DATETIME;

SET @FromDate = '2012-01-01';
SET @ToDate = '2012-02-01'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, *
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

SET @FromDate = '2012-01-28';
SET @ToDate = '2012-01-29'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, *
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

SET @FromDate = '2010-01-01';
SET @ToDate = '2012-05-15'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, *
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

DROP TABLE MyTestDates;

The three SELECT statements return all events in this example.


Dimensions in Microsoft SQL Server Analysis Services support either ROLAP or MOLAP.

Open the dimension in BIDS 2008, select the root dimension node

and change this in the properties window.

For fact data you open the cube and can similarly set the storage mode by selecting a measure group and altering the storage mode to one of HOLAP/ROLAP/MOLAP in its properties window.

But this only sets the default for new partitions. You still need to go onto the partitions tab of the cube designer and set any existing partitions to the desired configuration.


Why do connections to my database fail, but only sometimes?

Is the error you see on that last line always preceded by the same set of jobs, or does the error happen in different places?

I've thought about this for the last half hour and everything I think of I can refute.  Do you have any known networking issues on this server?  I've seen something kind of like this with VMWare's networking stack being flaky and dropping connections.  But then that doesn't make sense because the SQL box is showing an error.

Even though I ask about networking, I can come up with refutations to that, too.  This is a tough one.

If the jobs listed above are the culprit, then I would need to see what they are doing be sure what was going on.


PostgreSQL/RedShift - students that attended zero classes on a given day

SELECT <fields> FROM students LEFT OUTER JOIN class ON classes.studentid =  students.id WHERE classes.id IS NULL - the outer join will output one row per student for each matching class and one row per student with no matching classes, checking for classes.id being NULL will bring out just the output where there was no match.

Lookup how OUTER JOINs work and how they differ from INNER JOINs (note that by default if you don't specify otherwise joins are INNER) for more detail.

As a side note your model does appear to be in normal form: I would expect to have a table for students, a table for classes, and a table for the relationships between the two - your classes tbale appears to mix the latter two which may lead to consistency problems later.


Spool query to file without output on screen

You can use SQL> SET TERMOUT OFF command to get the result.

Here are some useful links collected from comments:

Oracle SqlPlus - saving output in a file but don't show on screen
sqlplus, how to stop output to console
How to make sqlplus output appear in one line?
Remove blank lines from csv using shell script for oracle


DBCC CheckDb on Secondary Asynchronous Replica

Can I run Ola Hallengren CheckDb job on a Secondary Asynchronous Replica (DR) that is readable during business hours?

Sure, assuming the routine doesn't try to update any command or history tables in that database (since it's read-only).

  ... and secondly if is safe as I have a primary server that is Live and I am doing a checkdb on a secondary during business hours/ off hours.

Much like sp_BlitzErik has stated, we can't tell you if it's "safe". This is going to depend on a host of factors such as disk space, redo queue size, send queue size on the primary, memory, current load, etc. Your best bet is to try it and see if you have no other metrics to go by - if people complain, you have your answer. If the website goes down, you have your answer. If no one says a word and everything goes well - you have your answer.


Query Cache utilisation ~50% Qcache_lowmem_prunes still high

To use least amount of QC space, use

query_cache_min_res_unit=512

for less waste of QC RAM  and qcache_lowmem_prunes expected to decrease as well over time.


Query Postgres JSON array for values in text

On this case you can cast the jsonb as text and use regular LIKE operator.

create table test(data jsonb);
insert into test values ('["Mr Smith","Ms Wellington","Mr Anderson"]'::jsonb);
insert into test values ('["Md Smith","Ms Wellington","Md Anderson"]'::jsonb);
insert into test values ('["Mg Smith","Ms Wellington","Mr Anderson"]'::jsonb);

select *
from   test
where data::text like'%Mr%';

| data                                         |
| :------------------------------------------- |
| ["Mr Smith", "Ms Wellington", "Mr Anderson"] |
| ["Mg Smith", "Ms Wellington", "Mr Anderson"] |

dbfiddle here


Using @@ROWCOUNT in multi-operation batch to check either a failure or a success of the whole transaction

To my surprise it looks like this works.  I wasn't aware that during the next query @@rowcount persisted from the previous query.  I don't know of any particular cases where it wouldn't but I would still probably change it to this just in case.

SET XACT_ABORT, NOCOUNT ON;
DECLARE @ROWCOUNT AS INT;
BEGIN TRANSACTION;

-- Insert this if it doesn't exist already.
INSERT INTO Table1
(
   Table1Value
)
SELECT
   @table1Value
WHERE NOT EXISTS
(
    SELECT 1
FROM
    Table1
WHERE
        Table1Value = @table1Value AND @table1Value IS NOT NULL
);

-- Update this table if insertion to the previous one succeeded. That is,
-- the @@ROWCOUNT in the WHERE conditional is more than zero. It's
-- OK if the the value changed was the same as before.
SET @ROWCOUNT = @@ROWCOUNT;

IF @ROWCOUNT > 0
    UPDATE Table2
    SET
        Timestamp = GETUTCDATE(),
        VersionCounter = VersionCounter + 1
    WHERE
        Table1Value = @table1Value AND @table1Value IS NOT NULL
        AND VersionCounter = @versionCounter AND @versionCounter IS NOT NULL;

-- The idea here is that the transaction will be committed
-- if both of the previous operations succeeded. Otherwise it
-- will be rolled back. Either way, an explicit return value is
-- used to indicate either a success or a failure.

SET @ROWCOUNT = @@ROWCOUNT;
IF @ROWCOUNT = 0
    ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION

-- Interpreted as TRUE if this is more than zero. FALSE otherwise.
SELECT @ROWCOUNT;


Of the multiple cnf files in /etc/mysql/conf.d/ dir, which would be the last one to be read (and will eventually take precedence)?

If I understand it correctly, there can be only one config file. But mine ends with

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

and so it is directly responsible for "merging" those additional ones into itself.

The !includedir directive is covered in the manual https://dev.mysql.com/doc/refman/5.5/en/option-files.html which states:

  There is no guarantee about the order in which the option files in the directory will be read.

So it probably depends on the OS and filesystem but you should not depend on it anyway, even when it seems "stable" for now.

I sugget using the !include directive instead and maybe a different folder for those config files where the order matters.


Elasticsearch and product characteristics

When every document is about shoes, then the term "shoe" will likely do little for the relevancy score, which may be enough of a filter in many cases. Still, as you point out, if there are no sport shoes and a document contains multiple occurrences of "shoes", that document may be considered relevant enough to be included in the result and without competition from actual sport shoes it will be at the top of the set.

However, if every search is for a shoe then any generic term for shoes will have little significance and you may want to ignore that term completely from either the index (can be done with a stop list) or from the searches (can be done with a token filter).

In both cases the occurrence of "shoes" will have no effect on search results, thus you end up selling a pair of the all-round sneakers that "feels like a sport shoe" which happened to be the only ones in the result set.

Same concept used in language based optimization when we remove the significance of articles and prepositions such as "the" and "on".

This can be applied to just about any text search, including ElasticSearch and Oracle Text.


Why is my update affecting twice the number of records than expected?

An update trigger on table1 causes the UI to return double the amount of records. The 500 records are being updated. However, each update causes a new record to be inserted into an audit table. The UI returns the count of the total rows affected for the query statement, not just the update itself!


I think you 'might' need to grant the proper authority on that folder to the Windows group SQLRUserGroup.

I have a test SQL Server instance where R-Services is installed.  I took your script and changed the directory to be C:/Scripts (just as a test).

EXECUTE sp_execute_external_script
       @language = N'R'
      ,@script = N'OutputDataSet <- data.frame(file.access("C:/scripts/", 2));'
WITH RESULT SETS (([DefaultLibraryName] VARCHAR(MAX) NOT NULL));
GO

I got the same -1 return code as you.

I then granted FULL access against C:/Scripts to the Windows group SQLRUserGroup and ran the same script code.  It executed successfully with a zero (0) being returned.


Transaction Isolation level different threads massive number of Writes

An ORM requires information about the first write (SCOPE_IDENTITY or such) to complete the second write. This means 2 (with an OUTPUT clause) or 3 database (with SELECT SCOPE_IDENTITY) calls in general in a client side transaction.

No amount of tinkering with isolation levels will eliminate these 2 or 3 calls.

If you want more performance, then the best concept stored procedure to do an atomic write (in a transaction) to both tables in one database call. This means 50% or 66% reduction in database calls

Going further, you have to decide what "superfast" really means

For example, should you be using SSDs for your transaction log files: the drive hosting the log files determines overall write speed because of write ahead logging.

Then there the design: are you using IDENTITY columns or GUIDs or Hibernate style nvarchar keys? Has the ORM designed the database for you?


Declare variable for SELECT statement in Postgres function

If the SELECT with the CASE expression is something you want to return in the output table, just add RETURN QUERY before it:

RETURN QUERY SELECT CASE ...

Note: 8.4 is very old now.  Even 9.0 is out of support by now - consider upgrading to a recent major version soon.  The old ones usually don't get any security (and other) fixes anymore.


How do I eliminate a second seq scan over a table when deriving a new table?


WITH abc AS (
SELECT
   i.InvoiceId
   ,h.BusinessName
   ,h.BranchId
   ,ItemCode
   ,Quantity
FROM InvoiceItem i
INNER JOIN InvoiceHeader h ON i.InvoiceId = h.InvoiceId
)
SELECT PT.InvoiceId, PT.BusinessName, PT.BranchId,
    ISNULL(PT.SNACK1,0) as Snack1,
    ISNULL(PT.SNACK2,0) as Snack2,
    ISNULL(PT.SNACK3,0) as Snack3,
    ISNULL(PT.SNACK4,0) as Snack4
FROM abc
PIVOT( SUM(Quantity)
       FOR ItemCode in ("SNACK1","SNACK2","SNACK3","SNACK4")
       ) as PT

output:

SQLfiddle

| InvoiceId | BusinessName | BranchId | Snack1 | Snack2 | Snack3 | Snack4 |
|-----------|--------------|----------|--------|--------|--------|--------|
|         1 |      BSN INC |        1 |    150 |    120 |      0 |      0 |
|         2 |      BSN INC |        2 |    100 |    150 |      0 |      0 |
|         3 |   SAMPLE INC |        1 |    150 |    150 |    110 |      0 |
|         4 |  ANOTHER INC |        1 |     90 |     80 |    120 |      0 |
|         5 |  ANOTHER INC |        3 |     50 |     70 |     90 |     70 |


Dimensional Model for inheritance in dimensions

While in a traditional OLTP system you may need to be concerned about normalizing and inheritance, in a typical data warehouse system you will want to denormalize to accelerate query responses.

In this case, I would design an Employee_Dimension table which had all relevant columns about an employee which you might want to query against, along with an additional column which I might call employee_role, which I would fill in with entries like Driver, Manager, etc. and then have another column which had either the truck_number filled for employees who were a driver, or leave the column as NULL when they aren't drivers. Of course, you can add even further columns to include information for other employee roles.

Long story short, if you're building a typical star schema, you can achieve faster query results by using heavy denormalization, at the cost of storage space of course.


Can I set a filegroup of database offline,change the location of files and bring it online again?

There is no T-SQL command to take a filegroup offline, though an individual file can be set offline.

From the documentation, ALTER DATABASE File and Filegroup Options (Transact-SQL):

  Use this option only when the file is corrupted and can be restored. A file set to OFFLINE can only be set online by restoring the file from backup. For more information about restoring a single file, see RESTORE (Transact-SQL).

To relocate files, follow the instructions in the documentation, or see the related Q & A:

How do I move SQL Server database files?


How not to have SQL Server to restart on the same node but failover on 1st failure in a cluster

By default SQL Server tries to restart on the same node in case of a failure, next it does failover.

Yes correct. Just to add, in Windows Server 2003 WSFC would attempt 3 times to bring resource online(By default) but from Windows Server 2008 it would just attempt 1 times( By default). Of course you can change the value. What i have said above is documented in This Support Article

  is it possible to have SQL Server NOT try a restart on the same node and just failover in case of a failure? Max restarts in the specified period - Will making it zero do the needful?

Yes I believe so, but this should not be done. I must also say I have never changed this option to zero, never seen it people using zero in their cluster configuration, and dont know how cluster would behave( when multiple failover happens) in case it is set to zero. You must test this before proceeding with the change in prod. In almost all cases at least one restart is necessary to check, because a failover can cause longer downtime( depending on storage and complexity) and downtime for all resources which are part of resource group even if it is not their fault.


denydatawriter does not work in SQL Server

Because you're also a member of sysadmin, all security checks are skipped.


Using EncryptByPassPhrase in where condition

You need to use the condition that works. The reason is easy to demonstrate. Run this:

DECLARE @x VARBINARY(8000) = ENCRYPTBYPASSPHRASE('12','YYY');
SELECT @x, CONVERT(VARCHAR(32), DECRYPTBYPASSPHRASE('12', @x));
GO 5

Do you see the same result every time? No. This is because the encryption adds some magic to prevent predictable results (that is grossly over-simplified, but for the purpose of this question, this is kind of how 3DES works to produce this symptom).

So, the way to do this is to decrypt first, then compare.

Of course, this kills sargability on the password column (if it is indexed), forcing a full scan (if this is really the only predicate). If you need efficient seeks on this column (seems unlikely that you'd ever just be checking for the password to match), you might need to consider a "locked-down" computed/persisted/indexed column.

As an aside, funny that a Google search for encryptbypassphrase where clause has hit #1 as a Stack Overflow answer by me.


Setting stack-size ulimit for mongodb to reduce connection overhead

The default stack size for MongoDB is already 1024, not 8192 (it is set in the code, not as a system setting) and has been since version 1.8.3 (see SERVER-2707), so you are already seeing the benefits of a lower stack size.


There are no technical issues with this.

It isn't going to make any difference to SQL Server.

From a usability point of view identifier names that begin with a number must always be quoted so

exec some_schema.16_TestStoredProc

would not be valid and you must always use

exec some_schema.[16_TestStoredProc]

Also when viewing the stored procedures alphabetically sorted - e.g. in Object explorer this prefix may well not be helpful in trying to find a stored procedure and as they are ordered lexicographically it may be somewhat inconvenient that 18_TestStoredProc precedes 2_TestStoredProc.

  I am not going to explain why we are doing this

Well perhaps you have good reasons. As you aren't going to share them it isn't possible for us to comment on them.


Best option to index an application log table for quick insertion and retrieval in date order?

I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that.

At insert rates of under 10,000/sec hot page latch contention is not a big issue, and the efficiency and locality of end-of-index inserts is preferable.

Change the primary key to be composite on (started, id) so it's the only index and the data pages are in order by date

Yes.  Prefer the solution where you don't need two indexes.  And look here for a solution to using a datetime2 as the only unique index on the table by filling the sub-second precision with values generated by a sequence.


How to "normalize" external data before insertion into "Data Warehouse" like database?

The whole process you described is implemented in the database engine itself for the columnstore compression.
DB engine will scan data for repeating text, find duplicates and create dictionary tables. In the row data it will keep only minimal references to dictionary.
Be sure to keep data batches big enough to trigger this compression process during insert - follow this guidance: Columnstore indexes - Data loading guidance


Just a heads up that you can't use aggregate functions (like MAX()) in a WHERE clause, rather that's the purpose of the HAVING clause. But instead of using an aggregate function, a window function like DENSE_RANK() simplifies the solution you're looking for, like so:
CREATE VIEW FavoriteDroneManufacturers AS

SELECT s.[name]
FROM
(
    SELECT dman.[name], DENSE_RANK() OVER (ORDER BY COUNT(1) DESC) AS DroneCountRankId
    FROM Drones d
    INNER JOIN DroneModels dm
        ON d.model_id = dm.id
    INNER JOIN DroneManufacturers dman
        ON dm.id = dman.id
    GROUP BY dman.id, dman.[name]
) AS s
WHERE s.DroneCountRankId = 1

(Note the above uses a subquery since you were weary of using CTEs, but the logic inside the subquery could've just as equally been created in a CTE instead, and a View can equivalently be created on both either way.)
DENSE_RANK() generates an ID for each row ordered by the logic you specify in the ORDER BY clause. When there are two rows that tie for ordering (based on the logic specified) then the same ID will be generated for both.
In the above query, I'm ordering (by descending) on the grouped COUNT() of drones for each manufacturer, and when two manufacturers share the same COUNT() of drones, then they get the same DroneCountRankId. In the outer query, I filter on DroneCountRankId = 1 to give us only the manufacturer(s) with the max number of drones (if it's two manufacturers like in your example, then they are both returned this way). (If you changed the WHERE clause to DroneCountRankId = 2 then you'd get all manufacturer(s) with the top 2 number of drones, etc.)
I highly recommend reading up on window functions and the DENSE_RANK() function in particular, via the links I provided above.


Postgres fast update on non-indexed column

I'm afraid there isn't any significant improvement to your scenario.

A few things to consider:

If your data statistics are good enough (i.e.: autovacuum has had the oportunity to properly ANALYZE your data), it is likely that PostgreSQL will use an Index Scan when you DELETE on the low side of the 0.5 .. 50% range, and a Sequential Scan on the high side. It's a matter of the estimated cost and the threshold where it will change from one method to the other will depend on some cost parameters you could actually tune.
It isn't likely that there are any faster ways... in any case the database need to locate which rows to delete (using an index or not), and delete them. For PostgreSQL, deleting a row is akin to changing the value of some hidden column (xmax), and later on, reclaiming the unused space through (auto)VACUUM. Other databases (specially, if they're non-transactional) perform this operation in significant different ways. Normally you trade higher speed by much lower safety/security.
The way PostgreSQL Multi Version Concurrency Control MVCC works, an UPDATE is basically equivalent to a DELETE followed by an INSERT. If you don't touch any indexed column, this UPDATE can take place as a Heap-Only Tuple (HOT) update. This won't have an effect when you delete a huge percentage of your table, because it isn't very likely that there is enough free room in your pages. In practice: don't expect an UPDATE to be faster than a DELETE. Your soft-deletes aren't likely to be faster than hard-deletes (as you already have been able to test).
If your data is not critical to you (i.e.: you use the database to process information that you actually have also stored somewhere else, and whose loss would then not be critical, because you could retrieve it again from sources), you can Use Unlogged Tables. You could also set some aggresive value to some of the database parameters: synchronous_commit (=off) and fsync (=off). I would discourage you from doing so unless your needs for speed are critical and the data can actually be easily recreated.


Database schema for entities with two possible owner/parent types?

If I understand your specifications properly, your scenario involves —among other significant aspects— a supertype-subtype structure.
I will exemplify below how to (1) model it at the conceptual level of abstraction and subsequently (2) represent it in a logical-level DDL design.
Business rules
The following conceptual formulations are among the most important rules in your business context:

A Playlist is owned by either exactly one Group or exactly one User at a specific point in time
A Playlist may be owned by one-to-many Groups or Users at distinct points in time
A User owns zero-one-or-many Playlists
A Groups owns zero-one-or-many Playlists
A Group is made up of one-to-many Members (who must be Users)
A User may be a Member of zero-one-or-many Groups.
A Group is made up of one-to-many Members (who must be Users)

As the associations or relationships (a) between User and Playlist and (b) between Group and Playlist are quite alike, this fact reveals that User and Group are mutually exclusive entity subtypes of Party1, which is in turn their entity supertype —supertype-subtype clusters are classic data structures that come about in conceptual schemas of very diverse sorts—. In this manner, two new rules can be asserted:

A Party is categorized by exactly one PartyType
A Party is either a Group or a User

And four of the previous rules must be reformulated as only three:

A Playlist is owned by exactly one Party at a specific point in time
A Playlist may be owned by one-to-many Parties at distinct points in time
A Party owns zero-one-or-many Playlists

Expository IDEF1X diagram
The IDEF1X2 diagram shown in Figure 1 consolidates all of the aforementioned business rules along with other ones that appear pertinent:

As demonstrated, Group and User are portrayed as subtypes that are connected by the respective lines and the exclusive symbol with Party, the supertype.
The Party.PartyTypeCode property stands for the subtype discriminator, i.e., it indicates which kind of subtype instance must supplement a given supertype occurrence.
Also, Party is connected with Playlist via the OwnerId property which is depicted as a FOREIGN KEY that points to Party.PartyId. In this way, Party interrelates (a) Playlist with (b) Group and (c) User.
Accordingly, since a particular Party instance is either a Group or a User, a specific Playlist can be linked with at most one subtype occurrence.
Illustrative logical-level layout
The IDEF1X diagram expounded before has served me as a platform to create the following logical SQL-DDL arrangement (and I have supplied notes as comments highlighting several points of particular relevance —e.g., the constraint declarations—):
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient INDEX strategies based on the exact
-- data manipulation tendencies of your business domain.

-- As one would expect, you are free to utilize
-- your preferred (or required) naming conventions.

CREATE TABLE PartyType ( -- Represents an independent entity type.
    PartyTypeCode CHAR(1)  NOT NULL,
    Name          CHAR(30) NOT NULL,
    --
    CONSTRAINT PartyType_PK PRIMARY KEY (PartyTypeCode),
    CONSTRAINT PartyType_AK UNIQUE      (Name)
);

CREATE TABLE Party ( -- Stands for the supertype.
    PartyId         INT       NOT NULL,
    PartyTypeCode   CHAR(1)   NOT NULL, -- Symbolizes the discriminator.
    CreatedDateTime TIMESTAMP NOT NULL,
    --
    CONSTRAINT Party_PK            PRIMARY KEY (PartyId),
    CONSTRAINT PartyToPartyType_FK FOREIGN KEY (PartyTypeCode)
        REFERENCES PartyType (PartyTypeCode)
);

CREATE TABLE UserProfile ( -- Denotes one of the subtypes.
    UserId     INT      NOT NULL, -- To be constrained as both (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    UserName   CHAR(30) NOT NULL,
    FirstName  CHAR(30) NOT NULL,
    LastName   CHAR(30) NOT NULL,
    GenderCode CHAR(3)  NOT NULL,
    BirthDate  DATE     NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Multi-column ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT UserProfile_AK2       UNIQUE (UserName), -- Single-column ALTERNATE KEY.
    CONSTRAINT UserProfileToParty_FK FOREIGN KEY (UserId)
        REFERENCES Party (PartyId)
);

CREATE TABLE MyGroup ( -- Represents the other subtype.
    GroupId INT      NOT NULL, -- To be constrained as both (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Title   CHAR(30) NOT NULL,
    --
    CONSTRAINT Group_PK        PRIMARY KEY (GroupId),
    CONSTRAINT Group_AK        UNIQUE      (Title), -- ALTERNATE KEY.
    CONSTRAINT GroupToParty_FK FOREIGN KEY (GroupId)
        REFERENCES Party (PartyId)
);

CREATE TABLE Playlist ( -- Stands for an independent entity type.
    PlaylistId      INT       NOT NULL,
    OwnerId         INT       NOT NULL,
    Title           CHAR(30)  NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    --
    CONSTRAINT Playlist_PK     PRIMARY KEY (PlaylistId),
    CONSTRAINT Playlist_AK     UNIQUE      (Title),  -- ALTERNATE KEY.
    CONSTRAINT PartyToParty_FK FOREIGN KEY (OwnerId) -- Establishes the relationship with (a) the supertype and (b) through the subtype with (c) the subtypes.
        REFERENCES Party (PartyId)
);

CREATE TABLE GroupMember ( -- Denotes an associative entity type.
    MemberId       INT       NOT NULL,
    GroupId        INT       NOT NULL,
    IsOwner        BOOLEAN   NOT NULL,
    JoinedDateTime TIMESTAMP NOT NULL,
    --
    CONSTRAINT GroupMember_PK              PRIMARY KEY (MemberId, GroupId), -- Composite PRIMARY KEY.
    CONSTRAINT GroupMemberToUserProfile_FK FOREIGN KEY (MemberId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT GroupMemberToMyGroup_FK     FOREIGN KEY (GroupId)
        REFERENCES MyGroup (GroupId)
);

Of course, you can make one or more adjustments so that all the characteristics of your business context are represented with the needed precision in the actual database.
Note: I have tested the above logical layout on this db<>fiddle and also on this SQL Fiddle, both “running” on PostgreSQL 9.6, so that you can see them “in action”.
The Slugs
As you can see, I did not include Group.Slug nor Playlist.Slug as columns in the DDL declarations. This is so because, in agreement with your following explanation

These slugs are unique, lowercase, hyphenated versions of their respective entity's title. For example, a group with the title 'Test Group' would have the slug 'test-group'. Duplicates are appended with incremental integers. This would change any time their title changes. I believe that means they wouldn't make great primary keys? Yes, slugs and usernames are unique in their respective tables.

one can conclude that their values are derivable (i.e., they must be computed or calculated in terms of the corresponding Group.Title and Playlist.Title values, sometimes in conjunction with —I assume, some kind of system-generated— INTEGERs), so I would not declare said columns in any of the base tables as they would introduce update irregularities.
In contrast, I would produce the Slugs

maybe, in a view, which (a) includes the derivation of such values in virtual columns and (b) can be used directly in further SELECT operations —appending the INTEGER part could be obtained, e.g., by combining the value of (1) the Playlist.OwnerId with (2) the intermediate hyphens and (3) the value of the Playlist.Title;

or, by virtue of application program code, mimicking the approach described before (perhaps procedurally), once the pertinent data sets are retrieved and formatted for end-user interpretation.

In this manner, any of those two methods would avoid the “update synchronization” mechanism that ought to be put in place iff the Slugs are retained in columns of base tables.
Integrity and consistency considerations
It is critical to mention that (i) each Party row must be complemented at all times by (ii) the respective counterpart in exactly one of the tables standing for the subtypes, which (iii) must “comply with” the value contained in the Party.PartyTypeCode column —denoting the discriminator—.
It would be quite advantageous to enforce that kind of situation in a declarative manner, but none of the major SQL database management systems (including Postgres) has supplied the necessary instruments to proceed like that; therefore, writing procedural code within ACID TRANSACTIONS is so far the best option to guarantee that the circumstances previously described are always met in your database. Other possibility would be resorting to TRIGGERS, but they are prone to make things untidy, so to speak.
Comparable cases
If you want to establish some analogies, you might be interested in taking a look at my answers to the (newer) questions entitled

“Designing a database for a funds transfers business context” and
“Modeling a scenario in which each Music Artist is either a Group or a Solo Performer”,

since comparable scenarios are discussed.

Endnotes
1 Party is a term used in legal contexts when referring to either an individual or a group of individuals that compose a single entity, so this denomination is suitable to represent the concepts of User and Group with respect to the business environment in question.
2 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the early theoretical works authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


How to protect SQL Server 2012 Backups on Standard Editions

For SQL Server 2012 standard edition you do not have in-built option to protect the backups. You would either have to reply on third party tools like one provided by RedGate or you can use WinZip or 7Z  to Zip the backups with password.

PS: SQL Server 2014 standard edition do have option to encrypt the backups


Not sure what your problem is... the three views solution sounds best.
If consistency is your problem with that then automate the creation of the views.
Use a function to return the common "logic"

Create function fn_GetStaticViewElement()
   return CONVERT(VARCHAR(MAX),',SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3')

Create proc proc_GenerateViews

'ALTER VIEW vwALL
execSQL ('ALTER VIEW vwALL AS Select ... ' + fnGetStaticViewElement() + '...From...Where...')
'ALTER VIEW Board
execSQL ('ALTER VIEW vwBoard AS Select ... ' + fnGetStaticViewElement() + '...From... Where...')
'ALTER VIEW School
execSQL ('ALTER VIEW vwSchool AS Select ... ' + fnGetStaticViewElement() + '...From... Where...')

to have the data combined:
Create 1 huge view, union all the sources, combining all the possible columns.
De-normalised.
Expose sourcename into a column.
use that as part of your filter.
Should act like a partitioned view, I think.


How to store JSON data from APIs and be able to search efficiently

Keywords for indexing -- Either put it in a TEXT and apply a FULLTEXT index, or put the keywords one-per-row in another table (which is indexed).

To critique a schema, we need to see the main SELECTs that you will have.


Secondary filegroup for Full Text Index only - rebuild when lost

(answering my own question)

OK, so we ran a simulation - what will happen if we lose the secondary filegroup file?

Not good.

The database gets stuck in recovery mode and you can't do anything about it - you can't set the "emergency" mode, you can't change it to "single_user" mode - nothing. Basically, every command you run throws an error Unable to open the physical file "xxx.mdf". Operating system error 2: "2 (The system cannot find the file specified.)

The only solution is to restore the database. But "rebuilding" a secondary filegroup won't work, even if there's only a fulltext index in it.


How to create a table with multiple data type like char short int string in mysql?

Entity-Attribute-Value is the nightmare you are embarking on.  See the additional tag I added.  See a similar tag in stackoverflow.com; there is a lot more discussion there.

It is traditional to have another column in the Attribute table:  entity_id, attribute, value.  Your question applies to how to store value; that is only part of the nightmare.

In the cases of integers, simply make it BIGINT; that will hold the various sizes (with a little wasted space), and simplify your code some.


There is few ways to do it as per below-

1) partitioning but you don't want it. Also need to remember that partitioning does not support with foreign keys.

2) You can normalize your table if there is duplicate values in table.

3) If this table contains transactional data or logs then you can move old data in historical tables year wise etc if possible in your environment.


Query For Where Table 1 (A,B,C) for each distinct A, has rows matching all rows in Table 2 (B,C)

select *
from t1
where t1.a in ( select t1.a
                  from t1
                  join t2
                    on t2.b = t1.b
                   and t2.c = t2.c
                 group by t1.a
                having count(*) = (select count(*) from t2)
              )


Efficiently comparing prices in different currencies

Here's a different approach for which recomputing the calculated_price is just an optimization, as opposed to being strictly necessary.

Suppose that in the currencies tables, you add another column, last_rate, which contains the exchange rate at the time the calculated_price was last updated, no matter when this happened.

To quickly retrieve a set of products with a price point between, say, 50 USD and 100 USD that include the desired results, you could do something like that:

  SELECT * FROM products
   WHERE calculated_price > 50.0/(:last_rate*
    (SELECT coalesce(max(value/last_rate),1) FROM currencies
      WHERE value>last_rate))
   AND calculated_price < 100.0/ (:last_rate*
    (SELECT coalesce(min(value/last_rate),1) FROM currencies
      WHERE value<last_rate))

where :last_rate contains the EUR/USD exchange rate at the time of the last update. The idea is to increase the interval to take into account the maximum variation of every currency. The increase factors for both ends of the interval are constant between rates updates, so they could be pre-computed.

Since the rates change only slightly over short periods of time, the above query is likely to give a close approximation of the final result. To get the final result, let's filter out the products for which the prices have slipped out of the bounds due to the changes in rates since the last update of calculated_price:

  WITH p AS (
   SELECT * FROM products
   WHERE calculated_price > 50.0/(:last_rate*
    (SELECT coalesce(max(value/last_rate),1) FROM currencies
      WHERE value>last_rate))
   AND calculated_price < 100.0/ (:last_rate*
    (SELECT coalesce(min(value/last_rate),1) FROM currencies
      WHERE value<last_rate))
  )
  SELECT price,c.value FROM p join currencies c on (p.currency=c.id)
     WHERE price/c.value>50/:current_rate
       AND price/c.value<100/:current_rate;

where :current_rate is the more up-to-date rate with EUR for the money choosen by the user.

The efficiency comes from the fact that the range of rates is supposed to be small, the values being close together.


Here is something interesting about MySQL Replication:

When it comes to Master/Slave Replication

Master DB Server must have server-id defined in my.cnf
Master DB must have binary logging enabled (define log-bin)
Slave DB Server is not required to defined server-id in my.cnf

If, all of a sudden, you find yourself having to reverse the DB Server's roles (making the old Slave the new Master, and making the old Master the Slave), the requirement to define server-id and log-bin must also hold true.

BTW a DB Server without server-id being set has a default value of 1. If server-id was define as 1 on the Master and the Slave did not have server-id defined, the Slave would also have had server-id being equal to 1. Activating the Slave as the new Master with any trace remnants of the old replication setup wmay have attempted to complete the processing of any remaining SQL entries in the relay logs. Those entries carried the server-id of 1. Bringing those entries over to a new machine without server-id define would have caused mysqld to complain about processing relay logs entries with the same server-id.

Perhaps, someone forgot to define the server-id on the Slave before making it the new Master and trying to engage Replication afterwards. Adding server-id to my.cnf and a quick restart of mysqld would have eliminated this weird situation without giving it a second thought.


Default Schema allows dbo table selection without using schema prefix

You don't need the prefix when some object is in your default schema because SQL Server checks for a non-schema-prefixed object in your default schema first. It will then check the dbo schema, so you can actually get away with this laziness in two different ways.

From the documentation:

  When database objects are referenced by using a one-part name, SQL Server first looks in the user's default schema. If the object is not found there, SQL Server looks next in the dbo schema. If the object is not in the dbo schema, an error is returned.

They say "one-part name" but this also applies when you reference an object with a pattern like:

[server].[database]..[name]

or

[database]..[name]

This is nothing but a confirmation that you should just always specify schema name. Always.

Bad habits to kick : avoiding the schema prefix