arbiter bring down the three node mongodb replica set

When I take the arbiter offline (killing the instance), both two data
  nodes become secondary (they were primary and secondary).

In this case, when the arbiter(_id = 3) goes down, The member with _id equals to 4 has Priority and votes as 0, so it cannot become primary and cannot vote in the election and the Majority(2 votes) cannot be established. So both the nodes becomes secondary.

  When I take the primary offline (killing the instance), the secondary
  and arbiter remain secondary and arbiter. There is no voting to select
  the new primary.

In this case, when the Primary(_id = 5) goes down, The member with _id equals to 4 has Priority, so it cannot become primary. Because of this the secondary
and arbiter remains as secondary and arbiter.

How to design this replica set to work better?

Make the Priority and votes of the node(_id = 4) to 1, it will fix both the scenarios.


Postgres - returning custom values ie. "true" rather than "t"

You can use a CASE WHEN clause to custom the output :

SELECT CASE WHEN status THEN 'true'
            WHEN NOT status THEN 'false'
            ELSE 'uknknown'
       END AS status
FROM mytable;

If you want to change the default output of PostgreSQL in order to output 'true' instead of 't', I'm pretty sure it is not possible.


Not sure I understand, but I guess something like:

select u.name, count(*)
from users u
join order o
    on u.id = o.user
group by u.name
order by max(o.cost) desc

would do. Please include your query and if relevant, table definitions for future questions.


Configure database mirroring for multiple databases via TSQL or Powershell when mirror is already restoring

Run the Alter Database command to add database to mirroring session

--Run on Mirror
ALTER DATABASE Test_DB
    SET PARTNER = 'TCP://Mirror_Me:5022'

Run on both mirror and principal. Remember to change the port numbers appropriately

--Run on Principal
ALTER DATABASE Test_DB
    SET PARTNER = 'TCP://Mirror_Me:5023'

Read this for more info.

Adding Additional script for a complete configuration minus the backup and restore process:

--This script allows db mirroring to be setup iwth a witness.
--Edited to skip the backup and restore process to the mirror
--Make right configuraton changes accordingly

--On A server
    CREATE ENDPOINT [Endpoint_Mirroring] --change endpoint name as required
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022)  --change port number as required. default is 5022
    FOR DATA_MIRRORING
    (ROLE = ALL);

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\login_name] --change to required domain account

--On B server
    CREATE ENDPOINT [Endpoint_Mirroring] --change endpoint name as required (good to keep the same name)
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022) --change port number as required.
    FOR DATA_MIRRORING
    (ROLE = ALL);

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\login_name] --change to required domain account

--Change for current db
    ALTER DATABASE TEST_DB SET PARTNER = 'TCP://servername.domainname.net:5022'  --change the database/server name
    GO

--On B server
    ALTER DATABASE TEST_DB SET PARTNER = 'TCP://servername.domainname.net:5022' --change the database/server name
    GO

--To setup Witness on Server C
    CREATE ENDPOINT [Endpoint_Mirroring] --change endpoint name as required (good to keep the same name)
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022) --change port number as required.
    FOR DATA_MIRRORING
    (ROLE = ALL);

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\login_name]

--On Principal Server
    ALTER DATABASE Test_DB SET WITNESS = 'TCP://servername.domainname.net:5022' --change the database/server name
    GO

NOTE: This does not contain setting up certificates for encrypted connections on the endpoints/mirroring. It does not hand transfer of logins from principal to mirror. This is outside the scope of this question.

DISCLAIMER: This script is AS-IS and should be thoroughly tested before implementing into a production environment.


SQL Server 2005 Percent Log Used Alert fires too fast?

In the properties of the Alert you can set a "Delay between responses". You can set the delay to slightly longer than it takes to do the compression.


I would check if the users get access via an Active Directory Security Group.


sp_send_dbmail issue when using variable for subject line

An option would be to enhance the FOR XML PATH('') to pull the actual node values out.

See the following examples (with the results of both at the bottom).

(Original represents what you are basically doing now)

(Updated uses the enhanced FOR XML PATH('') to pull the actual node values out).

set nocount on
Declare @T Table (LoanId int, FirstName varchar(100), LastName varchar(100))
insert into @T(LoanId,FirstName,LastName) values
    (12345,'John','Smith'),(54321,'James','Smith')

declare @subject varchar(100)

--Original statement
SET @subject = cast((
            SELECT TOP 1 LoanId, FirstName, LastName
            FROM @t
            FOR XML PATH('')) AS NVARCHAR(MAX) ) ;

print @subject

--Updated statement
SET @subject = (
            SELECT TOP 1 'LoanId = ' + convert(varchar(10),Loanid) + ',' + 'FirstName = ' + FirstName + ',' + 'Lastname = ' + lastname
            FROM @t
            FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)');

print @subject

<LoanId>12345</LoanId><FirstName>John</FirstName><LastName>Smith</LastName>
LoanId = 12345,FirstName = John,Lastname = Smith


TOP PERCENT

  Indicates that the query returns only the first expression percent of
  rows from the result set. Fractional values are rounded up to the next
  integer value.

Source

Meaning that TOP percent is rounding up, we could use the CEILING() function on a numeric value to get the same result.

You could change the query like this:

SELECT *
FROM sys.databases
ORDER BY database_id
OFFSET 0 ROWS
FETCH NEXT (SELECT CAST(CEILING((COUNT(*) * 10.0/100.0)) as int) FROM sys.databases) ROWS ONLY;

To get the same amount.

Issues with applying a filter.

If you add a where clause the results could differ, the reason being that in the changed query, you would need to add the where clause also on the query in the OFFSET FETCH part.

These two queries could return different results:

SELECT *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id
OFFSET 0 ROWS
FETCH NEXT (SELECT CAST(CEILING((COUNT(*) * 10.0/100.0)) as int) FROM sys.databases) ROWS ONLY;

SELECT top 10 percent *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id;

DB<>Fiddle

These two queries should return the same results:

SELECT *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id
OFFSET 0 ROWS
FETCH NEXT (SELECT CAST(CEILING((COUNT(*) * 10.0/100.0)) as int) FROM sys.databases where name not like '%A%') ROWS ONLY;

SELECT top 10 percent *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id;

DB<>Fiddle


Task generated T-SQL shows unknown property IsMemoryOptimized

Converting comment to answer so as to close this question:

Most probable reason that error has been witnessed in SQL server 2014, could be you are not updated to the latest cumulative updates.

  unknown property IsMemoryOptimized

has been reported as bug, may not be somewhat similar to you're situation, but CU has been suggested.

However, for now, please try to make the changes using the SQL server 2008/R2 and see if that works.


Why TRY CATCH does not suppress exception in trigger

The transaction is doomed with pretty much any exception and must be rolled back.
From "Using TRY...CATCH in Transact-SQL" on MSDN

Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back.
A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block.

This example shows why. XACT_STATE() doesn't register an implied transaction (eg no explicit BEGIN TRAN)
CREATE TABLE TrgTest (gbn int NOT NULL);
GO
CREATE TRIGGER TRG_TrgTEst_I ON TrgTest AFTER INSERT
AS
BEGIN TRY
     SELECT '1', @@TRANCOUNT, XACT_STATE();
     RAISERROR('Test error', 16, 2);
END TRY
BEGIN CATCH
     SELECT '2', @@TRANCOUNT, XACT_STATE();
END CATCH
GO

BEGIN TRANSACTION
INSERT TrgTest VALUES (1)
SELECT 'will not run'
GO
SELECT 'next batch'
GO

BEGIN TRY
    BEGIN TRANSACTION
    SELECT 'a', @@TRANCOUNT, XACT_STATE();
    INSERT TrgTest VALUES (1)
    SELECT 'b', @@TRANCOUNT, XACT_STATE();
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
    SELECT 'c', @@TRANCOUNT, XACT_STATE();
END CATCH
GO

DROP TABLE TrgTest;


Changing master database compatibility level

I'm not certain what you mean by one application "running its queries from the master database."  As you likely know, this would fall into the category of "worst practices."

The upgrade worked as expected in regard to the master database compatibility mode.  See Change the Database Compatibility Level and use the Query Store.  Unfortunately, this documentation doesn't give any guidance on whether it should be changed after the upgrade.

So if everything is working fine in production with the higher compatibility mode, then it should be safe to change the compatibility mode in development.  The only reason I say should be safe is that I've been in IT way to long to ever say anything will be safe.  If you aren't sure of why this change should be safe, then I would recommend that you do a bit of reading on what the compatibility mode actually does, and then it should be clear that it is OK to change it--particularly since you've already tested the configuration in production.

Additionally, I have not been able to find anything to suggest that it's not OK to change it, and I have found a couple of references that suggest it should be done:

https://www.sqlrx.com/master-database-compatibility-level/
https://ask.sqlservercentral.com/questions/119827/rolling-upgrade-to-2014-master-compatibility-issue.html

Lastly--this question is a day old and nobody has commented or answered with any warnings, and I'm quite certain someone would have already commented if there was any known significant risk.

In your case, since it is a development system and there is a production server partner with the upgraded compatibility mode, I would certainly get it upgraded.  If the compatibility mode has a negative affect on any queries, you will want to find that in development, not in production.  For most systems this wouldn't matter as applications aren't using code in the master database, but it sounds like you have some stored procedures there or something that may be impacted by the master database compatibility mode.


Make a backup file or save a DB in PostgreSQL

First think about what you want to achieve with a backup. To my humble opinion, a dump is not a backup good enough for DR. It can help restoring to a point in time but with a dump you can not recover the transactions that were created since the 'backup' was taken.
So if you just want an SQL dump that allows you to restore to a time in the past and ignores all transactions made between the dump time and the restore time, you can do that with a user that has read only access to those tables.
If you want a real backup, one that also enables you to recover the transactions made since the backup, use pg_backup to make a physical backup of the database and regularly make wall backups (there is a parameter for that "archive_command") and of course, your cluster needs archive_mode = ON, and wal_level to archive or replica (depends on version, replica for >= 9.6) This backup needs to be done by the database owner, often the postgres OS user.


Duplicate Relationships in Rails/MySQL Healthcare Application

This question if part database schema and part rails. From a Rails perspective, you can create as many aliased relationships as you like so normalization isn't as much of a concern there.

Without seeing the schema and just going based off of what you have written above though, I'd say that Dosage doesn't belong in a table with it's own model.

Dosage is effectively

Amount: 10
Unit Of Measure: mg

Writing things like 10mg into a table and giving them an id doesn't make a ton of sense. Organizing the types of units of measure that you have available or even standardizing by converting everything to milligrams in the database so that you can just do conversions when displaying would probably make the most sense.

Then you eliminate the dosages table completely and simplify to:

therapies
 - id
 - drug_id
 - milligrams

And Prescription becomes:

prescription
 - id
 - therapy_id
 - condition_id

Now, usage is the outlier here because it's where your only real "duplicate" relationship is but everything you need to link drugs to conditions can be inferred from your prescription data, effectively like so:

SELECT DISTINCT prescriptions.condition_id, therapies.drug_id
FROM prescriptions
  INNER JOIN therapies ON prescriptions.therapy_id = therapies.id

You could use a query like the above to create a view called usages that could be used by a read only Usage model in Rails. If performance became a concern you could use that to create a cached usages table and setup a database trigger to update the cache table when a new prescription is added though.


SQL Server: Multiple Availability Groups, one cluster, how do I keep the primary cluster node as primary?

Converted from comments:

  Are you referring to the core cluster resources? If yes, the answer is no, failover of the cluster role for the availability group would not impact the core cluster resources. The core cluster resources will of course fail over to another node if the current owner is rebooted or loses connectivity, but that behavior is not impacted by what anything that happens with the AG resources. – Tony Hinkle


Create an incremental number with year and month without updating the entire table using a trigger

Schematically (no syntax):

CREATE TRIGGER UpdateThingsNumberTrigger
ON Things
AFTER INSERT
AS
WITH cte AS ( SELECT LEFT(Number, 8) yyyymm, CAST(RIGHT(MAX(Number),4) AS INT) nnnn
              FROM Things
              GROUP BY LEFT(Number, 8)
            )
UPDATE Things
LEFT JOIN cte ON cte.yyyymm = YEAR(CreationDate)
                            + '.'
                            + MONTH(CreationDate)
                            + '.'
SET Number = YEAR(CreationDate)
           + '.'
           + MONTH(CreationDate)
           + '.'
           + RIGHT('0000' + CAST((  COALESCE(nnnn, 0)
                                  + ROW_NUMBER() OVER (GROUP BY YEAR(CreationDate)
                                                              + '.'
                                                              + MONTH(CreationDate)
                                                              + '.'
                                                       ORDER BY CreationDate) AS CHAR), 4)
WHERE Number IS NULL

Logic:

We update the whole table, but only those records which have no value in Number field (WHERE Number IS NULL).
In cte we obtain maximal current number for each year-month in the table divided to prefix (yyyymm) and number within group (nnnn).
For each group of records to be updated (1) we calculate new number part - we enumerate those records and add this number to nnnn part, then format in with leading zeros. If it is new year-month, no nnnn for it, and we set it to 0, so we start number part from 1.

PS. It is possible when the last record in the group by year-month was deleted - in such case deleted Number value will be re-assigned. I do not see the way to determine does this value existed previously and it was deleted, or it was not existed.


What are the hazards of upgrading a SQL Server service pack?

Well one thing you may want to consider doing is validating your application(s) still work on a test instance that contains the same database and upgrading it to the newer service pack. While rare and usually changes are backward compatible, there could be behavior changes to things like the optimizer that would be impossible to guess exactly how they might affect your application without testing. The risk is certainly less with a service pack than a version upgrade, for example, but installing a service pack on a cluster does not exactly have a trivial "undo" button - so in this scenario the backups won't really help you directly (they are still an absolutely sound idea, of course).


Is there a way to show the creation statement for an index in PostgreSQL

There is actually, just query the pg_indexes system catalog view as follows:

SELECT indexdef FROM pg_indexes WHERE indexname = '...'

and you should get back the SQL statement used to define it.


In the MySQL shell, enter

pager less

Then enter your query. Use Enter to scroll down and 'q' to stop browsing the results.

My system doesn't have less, so I used more instead:

pager more

More can scroll down and quit like less, but it can't scroll up.


Automatic Failover on SQL Server Always On Availability Groups is not triggered when primary replica becomes offline

Add a quorum configuration in the failover cluster manager. In order for the cluster to continue to be online a majority of nodes need to be online, you have only two nodes. If one goes down, only one is online and that’s not majority. If you add a witness like shared drive which needs to be stored off the nodes, then if one node goes down, majority two are still online so cluster will remain online.


If the data is purely read-only once it arrives in the target database (and you have lots of disk space available), here's an alternative you might consider.

-- Two tables with the SAME structure
create table table1_a ( ...
create table table1_b ( ...
-- No Application permissions on these tables

create view table1_read  as select * from table1_a ;
create view table1_write as select * from table1_b ;

grant select                         on table1_read to application_role ;
grant delete, insert, select, update on table1_write to etl_role ;

The application that consumes the loaded Data always reads from table1_read.

Your application doing the ETL always writes to table1_write.

Apart from the background work in the database churning all that data across, there is no impact on the Application ...  until, at the end of a successful load, you invoke a stored Procedure that works out which View is looking at which Table and then switches the definitions of the two Views.

create or replace view table1_read  as select * from table1_b ;
create or replace view table1_write as select * from table1_a ;

This allows you to use really fast, database-level utilities to trash and reload one of the tables while the Application continues to read the other and then with minimal, if any, impact on the Application, makes the new data available and frees up the other table ready for loading next time.

OK, you are doubling your data storage requirement, having two copies of the data lying around but if, for any reason you suspect the newly loaded Data is wrong, you can [very] quickly revert to "yesterday's data" by switching the Views over again.  No re-running the ETL load process to get back to a stable, working system.

Sure; it's a sledgehammer approach, but is has the advantage of simplicity and has proven remarkably resilient.  That Procedure is the only "fiddly" bit but it can be coded in such a way that it only has to be written and tested once and reused for any number of tables treated in this way.


MySql - changing innodb_file_per_table for a live db

There is really only one way to pull this off. You will have to export the data using mysqldumps, drop all databases, shutdown mysqld, delete ib_logfile0, delete ib_logfile1, delete ibdata1, add innodb_file_per_table under the [mysqld] heading, start mysql.

I posted this answer in StackOverflow back in October 2010

Here are the steps listed vertically:

Step 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

Step 02) Drop all databases (except mysql schema)

Step 03) Shutdown mysql

CAVEAT: To totally clean out uncommitted transactions from the InnoDB Files, run this

mysql -uroot -p... -Ae"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop

Step 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

Step 05) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

Step 06) Restart mysql

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

Step 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many times in my career as a MySQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!

There is an alternative that will extract the InnoDB table without shrinking ibdata1.

Step 01) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Step 02) service mysql restart

Step 03) To extract a single InnoDB table called mydb.mytable, do this:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

This will create one file pleus keep the original structure file

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

You can do this for every InnoDB table. Unfortunately, ibdata1 will stay 150GB.


What is the least amount of privileges (Administrator, db_owner, etc ) for a user to perform Backup and Restore in SQL Server?

For backups the db_backupoperator fixed database role is the least amount of privileges needed.

https://msdn.microsoft.com/en-gb/library/ms187510.aspx

For Restores, If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE.

https://msdn.microsoft.com/en-gb/library/ms186858.aspx


Error code is 0x80131500 when applying SP4 to SQL Server 2012

Mystery solved. I have found out that the network segment between the nodes of the AG had File and Printer Sharing disabled. Only Sql traffic was allowed.
Re-enabling that on both nodes of the AG for the time it takes to apply the patches on both nodes allowed the needed communication between them.

Just another reminder that you should always check the simple things first when you have a problem.

Have a great day
Richard


Restore SQL Server database backup to other directory than root

restore database YourDatabase
from disk = 'C:\db.bak'
with
    move 'YourDataFile' to 'D:\YourNewDir\YourNewDataFileName.mdf',
    move 'YourLogFile' to 'D:\YourNewDir\YourNewLogFileName.ldf'

The above command will restore your database but move your database files to a different location as per your specifications.  To get a list of database files contained in the backup (I just used the logical names of YourDataFile and YourLogFile), you can run the following command:

restore filelistonly
from disk = 'C:\db.bak'

If you want do to this through the GUI, then in the Restore Database dialog you can click on the item Files on the left, and you will see your database files listed.  There are elipses buttons next to those file names, and you can click that to change the path.  Likewise, you can just double click on the text box that has the file names themselves and change that to reflect the new path.  Below is a screenshot of what I explained:


Important things :

Depending on your version of sql server, use TF 1117 and 1118 to equally grow all the files and alleviate tempdb contention. Those are safe trace flags to have.
Make sure your tempdb files are all equally sized and set to grow by a fix MB as opposed to Percent growth. I would suggest you preallocate so auto-growth is minimal to none.

Followups:

Run sp_whoisactive @get_plans =1, @get_transaction_info =1 and log it to a table every 1 min or so and see the tempdb usage.
Tell the vendor to have application only get the data that is needed. Have seen vendors abusing tempdb, but once you highlight the issue they will hopefully address it.
Read fully - Comprehensive tempdb blog post series - by Paul from SQLSkills


How can I set myself as owner of a database ? just using sql

Check this links and references:
http://technet.microsoft.com/en-us/library/ms178630.aspx

Example:

EXEC sp_changedbowner 'Albert'

and ALTER AUTHORIZATION

ALTER AUTHORIZATION ON OBJECT::Parts.Sprockets TO MichikoOsada;
GO

http://technet.microsoft.com/en-us/library/ms187359(v=sql.105).aspx


SELECT ta.Branch, tm.Description, COALESCE(ta.Values, 0) Values
FROM tbMonths tm
LEFT JOIN tbAccountValues ta ON ta.Month = tm.Description

The reason this works is that a LEFT JOIN requires two columns that form a relationship (be that PK/FK or otherwise). These columns have to contain identical values to JOIN the rows together.

In your example the tbAccountValues.Month and tbMonths.Description columns seem to contain the same values, so a JOIN can be made on those columns.

  It almost works though the problem is the branch column becomes null for those with 0 values

WITH tbBranches AS (SELECT DISTINCT Branch FROM tbAccountValues),
tbBranchesMonths AS (SELECT tB.Branch, tM.Description FROM tbBranches tB, tbMonths tM)
SELECT tBM.Branch, tBM.Description, COALESCE(tA.[Values], 0) [Values]
FROM tbBranchesMonths tBM
LEFT JOIN tbAccountValues tA ON tA.Month = tBM.Description
                            AND tA.Branch = tBM.Branch

fiddle


Need listing of all relationships of tables SQL Server 2017

This will get all relations discoverable through FK constraints for a database:
USE <your database>
GO

SELECT
  ChildSchema.name AS ChildSchema
 ,Child.name AS ChildTable
 ,fk.name
 ,ParentSchema.name AS ParentSchema
 ,Parent.name AS ParentTable
FROM
 sys.foreign_keys FK
INNER JOIN
  sys.tables Child
    ON Child.object_id = FK.parent_object_id --Parent here means the "parent" of the FK, not the relation
INNER JOIN
  sys.schemas ChildSchema
    ON ChildSchema.schema_id = Child.schema_id
INNER JOIN
  sys.tables Parent
    ON Parent.object_id = FK.referenced_object_id
INNER JOIN
  sys.schemas ParentSchema
    ON ParentSchema.schema_id = Parent.schema_id
ORDER BY
  ChildSchema.name
 ,Child.name
GO


MySQL slave_open_temp_tables always shows 1

I found the way to do this.

STOP SLAVE;
SHOW SLAVE STATUS;

write down relay_master_log_file and exec_master_log_pos

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE = 'relay_master_log_file', MASTER_LOG_POS = exec_master_log_pos;
START SLAVE;

And now it reports Slave_open_temp_tables AS 0.


Searching for keywords in fulltext indexes using the "contains" function

If you believe that "AND" should not be considered a stop word by SQL DB Engine while performing full-text index search, you need to configure it accordingly.
From CONTAINS description.

The search for characters in the word or phrase is not case-sensitive. Noise words (or stopwords) (such as a, and, or the) in full-text indexed columns are not stored in the full-text index.
If a noise word is used in a single word search, SQL Server returns an error message indicating that the query contains only noise words.
SQL Server includes a standard list of noise words in the directory \Mssql\Binn\FTERef of each instance of SQL Server.

You need to edit \Mssql\Binn\FTERef location manually to remove AND from the list of stopwords.
Here is MSDN resource:
CONTAINS
Configure and Manage Stopwords and Stoplists for Full-Text Search


Remove text from columns that may or may not be present multiple times

Something like this?

UPDATE dbo.OrgUnits
SET OrgUnitName = RTRIM(REPLACE(OrgUnitName,'('+OrgUnitKey+')',''))+' (' + OrgUnitKey + ')';

View the result

SELECT  OrgUnitName from dbo.OrgUnits;

Result

OrgUnitName
Company1 (5)
Division1 (1)
XAVAT (4)
WATe (7)
Communications (05-905-6320-300)
Corporate Development (05-955-6320-300)
Corporate Board of Directors (05-111-3515-301)


Query Relating Rows W/O A Corresponding Value

SELECT [ID Code], statusCode
  FROM dbo.Table AS t
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.Table
    WHERE [ID Code] = t.[ID Code]
    AND statusCode = 'A'
  )
  GROUP BY [ID Code], statusCode;


What is the persistent_version_store table?

Since this question hasn't had any comments or answers since August 2018 I'm going to assume nothing came of the large number of rows, but documenting the structure of the table might be useful to someone.

This table can be found in SQL Server 2019 Preview (CTP 2.2 as of this writing).

CREATE TABLE sys.persistent_version_store (
    xdes_ts_push bigint,
    xdes_ts_tran bigint,
    subid_push int,
    subid_tran int,
    rowset_id bigint,
    sec_version_rid binary(8),
    min_len smallint,
    seq_num bigint
    prev_row_in_chain binary(8),
    row_version varbinary(8000)
);

Perhaps given the high churn of a Dynamics environment, the version store did have a lot of rows, but they were short-lived?


Index for range of timestamp with varchar fields

Index  will be used if you remove cast to date (joined_date instead of joined_date::date) from WHERE clause (upper bound in BETWEEN should be changed accordingly).

  select *
  from test
  where joined_date >= '2017-09-25' AND joined_date <'2017-10-26'  ;

Alternatively , you can create index as
CREATE INDEX index_test2 ON test (cast (joined_date as date), company_name, department);

Proof

CREATE TABLE test (
    id           serial       PRIMARY KEY,
    company_name varchar(255) not null,
    department   varchar(255) not null,
    joined_date  timestamp    not null
);

INSERT INTO test(company_name, department,joined_date)
SELECT
  md5(x::text),
  CASE WHEN x%2 = 0 THEN 'foo' WHEN x%3 = 0 THEN 'bar' ELSE 'baz' END,
  now()
FROM generate_series(1,1e6)
  AS gs(x);

CREATE INDEX index_test
  ON test (joined_date, company_name, department);

VACUUM ANALYZE test;

Query 1

EXPLAIN ANALYZE SELECT *
FROM test
WHERE joined_date BETWEEN '2017-09-25' AND '2017-10-25';
                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_test on test  (cost=0.42..8.45 rows=1 width=49) (actual time=0.069..0.069 rows=0 loops=1)
   Index Cond: ((joined_date >= '2017-09-25 00:00:00'::timestamp without time zone) AND (joined_date <= '2017-10-25 00:00:00'::timestamp without time zone))
 Planning time: 0.142 ms
 Execution time: 0.097 ms
(4 rows)

Query 2

EXPLAIN ANALYZE SELECT *
FROM test
WHERE joined_date BETWEEN '2017-09-25' AND '2017-10-25'
  AND company_name = 'xxx';
                                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_test on test  (cost=0.42..8.45 rows=1 width=49) (actual time=0.016..0.016 rows=0 loops=1)
   Index Cond: ((joined_date >= '2017-09-25 00:00:00'::timestamp without time zone) AND (joined_date <= '2017-10-25 00:00:00'::timestamp without time zone) AND ((company_name)::text = 'xxx'::text))
 Planning time: 0.111 ms
 Execution time: 0.047 ms
(4 rows)

Query 3

SELECT *
FROM test
WHERE joined_date BETWEEN '2017-09-25' AND '2017-10-25'
  AND company_name = 'xxx'
  AND department = 'yyy';

                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_test on test  (cost=0.42..6.20 rows=1 width=49) (actual time=0.017..0.017 rows=0 loops=1)
   Index Cond: ((joined_date >= '2017-09-25 00:00:00'::timestamp without time zone) AND (joined_date <= '2017-10-25 00:00:00'::timestamp without time zone) AND ((company_name)::text = 'xxx'::text) AND ((department)::text = 'yyy'::text))
 Planning time: 0.120 ms
 Execution time: 0.046 ms
(4 rows)


Assuming that you did your homework correctly and these tables listed are indeed the way space is used (ie. there is no other table consuming the space) then what you have now is state and you ask about history. The state of the database now is a 16GB file with ~6Gb accounted for and 10Gb unexplained. How did it end up in this state? we cannot answer. There are a number of avenues that could lead up to this:

the database could had been created with a 16Gb data file size.
there could had been one or more big tables once that now are dropped
some operation needed temporary space and could had grown the database to 16Gb size and leave it so

You could consult the history captured in the default administrative trace and in the ERRORLOG and see if some of the info captured can shed light on the mystery.

If I'd venture a guess I would say you have a maintenance task of some sort, perhaps a table rebuild, that runs periodically. Rebuild operations require a great amount of data space as all the data exist for a while duplicated (old index being rebuild and new index being created) and, when the default OFFLINE option is used, they require a similarly great amount of log space as the entire operation is a single transaction. A log of 6Gb, data of approximately 6Gb size and a DB with +10Gb space would point toward that ~6Gb data being rebuild, maybe once manually, or maybe periodically by an index rebuild maintenance task.

Now about your real question: is there a better way to do this? Not an easy one. Such pattern of high churn with large range deletes is indeed very unhealthy. If you can prove that it causes real problems (sorry, but a 16Gb data file is not really a problem, and fragmentation is more often than not a red herring) then there are solutions, the most effective being using a partitioning sliding window for efficient deallocation via switch-out+truncate (The link contains the exact how-to). This is a heavy impact change that would ripple everywhere, including the application code, so do consider all the aspects. See How To Decide if You Should Use Table Partitioning.


First Response Kit is missing sp_AskBrent

sp_AskBrent was renamed to sp_BlitzFirst.

https://www.brentozar.com/archive/2016/06/first-responder-kit-updated-sp_askbrent-now-sp_blitzfirst/


Setup an aerospike high-availbilty cluster?

Aerospike's HA setup is one of the easiest. No need to setup sharding or failover policies. Everything is automatically taken care of. You just need to configure the nodes to see each other and boot them. I suggest to go with 'mesh' mode configuration. Ref: https://www.aerospike.com/docs/operations/configure/network/heartbeat/index.html


How to select a subset of data from a large ER model?

Unfortunately there isn't a cookie-cutter way to do this.  You have to start with some base records, write queries that join against them and then repeat.  As you go deeper the queries get more complex as they have to go right back to the root.

If you have the foreign keys then you can automate the generation of the queries, as the joins will just be through the foreign key columns.  It's not as hard as it sounds, but here are a few pointers if you take this approach:

The queries are only going to retrieve the contents of the table at their leaf, so they are fairly straightforward to generate.
Get the data out in a form that will load through bcp.  As a bonus, you can generate bcp control files from the system metadata.
You can probably quite safely just copy all of the reference data, which probably accounts for the majority of your tables anyway.
You may have to start from multiple roots, and you may have to manually add join metadata if you have missing foreign keys.  If your relationships to your leaf tables are not purely hierarchical then you may have to manually edit the metadata for the generator.  In practice this means you probably want to start from the system data dictionary and then make up a configuration file for your generator that works around this.
You may also need to add custom queries in for certain data items if the selection criteria involve more than one path to the record.
Generate or write a script that uses bcp to load the tables to the destination in the right order.

Automation will probably get you 95% of the way with some manual intervention and the scripts aren't all that hard to write.  It's not a 5 minute job, but it's certainly possible.

You could programatically generate SSIS packages to do the load, but I think that generating bcp control files is probably easier.  Another option would be to just structure the scripts so they copy the data from the queries into another shadow database.  This can then be backed-up/restored to wherever you want.

It's not a trivial undertaking but it's certainly not beyond the wit of man.  As a bonus, if you can configure your script so the starting keys can be parameterised, then you can make a generalised utility to copy subsets of your application database.  This will be quite useful for rolling out test environments.


How to split field and run sub query using it?

select t1.id, t1.list
from t1
left join t2 on locate(t2.vals, t1.list)
group by t1.id, t1.list
having 0 = count(t2.vals);

fiddle

In more complex case (when a token in list may contain a token as a substring) you may use

select t1.id, t1.list
from t1
left join t2 on locate(concat(':',t2.vals,':'), concat(':',t1.list,':'))
group by t1.id, t1.list
having 0 = count(t2.vals);


Problem using 'Regular expression' in order to split characters of a column when there is no delimiter between them

1- Because with multiple rows, multiple paths exist to reach a level in the hierarchy
select lvl, count(*) from (
SELECT col_id, REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) AS VAL, level lvl
  FROM tbl_test
CONNECT BY REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) is not null
) group by rollup(lvl)
order by 1;

       LVL   COUNT(*)
---------- ----------
         1          3
         2          9
         3         27
         4         81
         5        243
         6        486
                  849

2- I am not saying it is the best, it is just one possible way to do it (add delimiters and use other tricks):
drop table tbl_test;
create table tbl_test
(
  col_id   number,
  col_name varchar2(500)
);

insert into tbl_test values (1, 'aetnap');
insert into tbl_test values (2, 'elppa');
insert into tbl_test values (3, 'ananab');
commit;

select
  col_id, trim(column_value) val
from tbl_test,
  xmltable(trim(trailing ',' from regexp_replace(col_name, '(.)', '"\1",')));

COL_ID VAL
------ ---
     1 a
     1 e
     1 t
     1 n
     1 a
     1 p
     2 e
     2 l
     2 p
     2 p
     2 a
     3 a
     3 n
     3 a
     3 n
     3 a
     3 b

Here you can find multiple methods for tokenizing strings:
https://lalitkumarb.wordpress.com/2015/03/04/split-comma-delimited-strings-in-a-table-in-oracle/


Attempting to identify minimal functional dependencies in a Compact Disc details scenario

A concern that exists is that a song may be released as a single, then as part of an album, then re-released in a "greatest hits" album. This would lead me to have a song table, then create a join table that would connect the album table to the song table. This join table would allow for a single song to be connected to a number of albums. The PK of that table could be the Album_ID and Song_ID, making it a composite.
I would also look at creating an Artist table, which would separate data like the artist's name, country of origin, and group/solo. This could also head off the "Prince Effect," where a singer/group changes their name.
Tables (PKs are bold)

Albums (Album_ID, Artist_ID, Album_Title, Release_Date)

Artist (Artist_ID, Country_Of_Origin, Type, Artist_Name)

Song (Song_ID, Song_Name, Song_Duration)

Song_Album (Album_ID, Song_ID, Track_Number)


How to create development/test environment of TB reporting database in SQL Server

The best option is to have a separate Dev/Test server where you can have a full instance of  your Data Warehouse.  If you don't have a seperate server, you might use an Azure Virtual Machine (or similar) that can be turned off when not in use.
And you should also have a local dev environment, probably with a subset of the data.  This allows teams to work in parallel without stepping on each other.
If you have only one server, you can co-locate the Dev/Test database and the production database, but you have to be careful to not over-tax the resources on the server.
It's also completely appropriate to perform some Dev/Test activities directly against the production data warehouse.  For instance when you're building or modifying reports, but not when you're modifying tables, views, or stored procedures, or working on your ETL jobs.  A good rule of thumb is that if the work requires more than SELECT privileges, it shouldn't be done directly on production.


Modelling a database for a travelling business that involves Vehicules, Routes, Subroutes and Prices

I did similar work to this many moons ago - I worked for an airline, but it's more or less the same thing!

A critical thing to get right from the start is terminology - yes, I know that you just want to start coding! :-) However, life will be much easier if everyone is singing off the same hymnsheet!

In the airline for which I worked, the word "flight" had three different meanings depending on whether you were speaking to an engineer, flight crew or sales people!

I would use the word (and table name) "leg" for a journey from one point to another where passengers can get on or off.

A "trip" can be many legs, normally, but not necessarily, a journey from point A to point B and back to point A "series" can be a number of trips over a season (don't know if this is your scenario, but it might help to have an over-arching framework and then factor in exceptions? You might have prices for legs and another price for a trip (normally lower than the sum of the legs)?

Firstly, on a technical note, I would advise you to use PostgreSQL (way before MySQL) for 5 main reasons (see below). If you want an embedded database, use Firebird which again has far more features (and has had them far longer) than MySQL.

1) it is rock-solid,

2) also has CTEs (i.e. WITH clause - also in MySQL 8 - but that is alpha)

3) Analytic functions (again, in MySQL 8?)

4) JSON functions

5) a rich language (PL/pgsql) that is comparable to Oracle's.

Note that (for PostgreSQL at any rate), the DDL (Data
Definition Language) - the code that creates tables and
indexes &c... is a powerful programming tool in its own
right - it can save you lots of coding and error-checking
in your application code. In fairness, all of the
mainstream databases have similar functionality. PostgreSQL
however is excellent (the best IMHO) for standards compliance!

My philosophy is always to put as much logic in the database
as possible - development environments come and go, databases
and data (like diamonds :-) ) are forever!

p.s. you may wonder why my names for fields, constraints, indexes
&c. seem long and maybe to some, overly verbose. This is
because I have learnt through painful experience that calling
the auto-incrementing PRIMARY KEY field for every table
'id' is a recipe for disaster - as is letting the RDBMS
assign its own machine-generated names to database objects.

I would start by creating the following tables:

CREATE TABLE location
(
  location_id SERIAL CONSTRAINT location_pk PRIMARY KEY,

  -- I find that keeping CONSTRAINT declarations in the CREATE TABLE
  -- statement makes them easier to follow and is more elegant.
  -- Furthermore, I find that keeping the declarations of CONSTRAINTs
  -- as near to the relevant FIELD as possible also helpful.

  -- I *_always_* give meaningful names to my CONSTRAINTs - makes
  -- debugging *_much_* easier - here location_pk makes more sense than
  -- some machine-generated gibberish. Oracle generates particularly
  -- opaque names for keys and other constraints. Use the capabilities
  -- of the database to the maximum!

  -- PRIMARY KEY name is **_always_** (specified by the programmer as) table_name_pk, guaranteed to be UNIQUE in
  -- the entire system. I always have singular table names - they are the equivalent
  -- of class definitions in OO programming. **_Plus_**, you may well want tables
  -- with 1 or even 0 records (think system = Nuclear_Power_Plant - table =
  -- Catastrophic_meltdown! :-)

  location_name VARCHAR(30) NOT NULL CONSTRAINT location_name_uq UNIQUE

  -- You might want to add GPS coordinates?

);

Then, insert some (sample/real) data - note that
PostgreSQL takes care of the SERIAL column incrementing!

INSERT INTO location (location_name) VALUES ('One');
INSERT INTO location (location_name) VALUES ('Two');
INSERT INTO location (location_name) VALUES ('Three');
INSERT INTO location (location_name) VALUES ('Four');

Now, create another table leg (more complex this time). The
leg table is just a list of departure points, arrival points,
departure points and amounts. You will have to determine if your
system has to allow for simple "there and back" runs (over several
locations) or more complex routes (i.e. trips).

CREATE TABLE leg
(
  leg_id SERIAL CONSTRAINT leg_pk PRIMARY KEY,

  leg_location_dep INTEGER,  -- is an FK field - no need to specify NON NULL!
  leg_location_arr INTEGER,
  leg_amount INTEGER NOT NULL CHECK (leg_amount > 0),

  -- ADD FKs to location table.

  CONSTRAINT leg_location_dep_FK FOREIGN KEY (leg_location_dep) REFERENCES location (location_id),
  CONSTRAINT leg_location_arr_FK FOREIGN KEY (leg_location_arr) REFERENCES location (location_id),

  -- Your app will *_never_* have to check that a fare is not quoted from one place back
  -- to the same place. If you are going to do return fares, then I would combine trips
  -- in some way.

  CONSTRAINT leg_dep_ne_arr_ck CHECK (leg_location_dep <> leg_location_arr),

  -- This last constraint ensures that you can never have two
  -- different fares for the same leg - this *_may_* or *_may_*
  -- suit your requirements - what you can see though, is that
  -- with intelligent use of the `CREATE TABLE` statement, you
  -- can already put a *_lot_* of logic into your database without
  -- writing a single bit of client code! You may also want to
  -- different fares for different times of day, week, year.

  CONSTRAINT leg_dep_arr_uq UNIQUE (leg_location_dep, leg_location_arr)

  );

CREATE INDEX leg_dep_idx ON fare (leg_location_dep);
CREATE INDEX leg_arr_idx ON fare (leg_location_arr);

The construction of INDEXes is a science in its own right
and any indexing strategy will depend on the queries that
you write against your tables - again, meaningful names
are very helpful for debugging.

I didn't put in data for the fare table, and the trip, vehicle
and owner tables are only outlines - but you should be able to
make a start with what I've shown here - you can drop back if you
require further assistance/information.

CREATE TABLE trip  -- not all work done!
(
  trip_id SERIAL,
  trip_location_dep INTEGER, -- references location table
  trip_location_arr INTEGER, -- references location table
  trip_date DATE NOT NULL,
  trip_time TIME NOT NULL,
  trip_fare INTEGER -- same checks as for fare.
);

What you want are JOINING tables - also called Associative
entities or simply many-to-many tables!

You could have one, for example, between trip and leg?

Call it trip_leg and have trip_id and leg_id as the
two fields - you might also want an order field i.e.
what might be A->B->C->D->E on one route, might be
A->D->C->E on another - one-way streets are another
issue - the vehicle can only be going only one way,
despite being on different trips/routes.

1 trip can have many legs and 1 leg can be on many trips, for
example, if you have a number of locations around a city, you
may have partially overlapping routes depending on your setup.

Some of your vehicles might be more
suitable for longer routes - a large bus for example, while some
(tuk-tuks or smaller taxis) might be more locally based?

Many choices in designing your system will depend on your
particular needs! Check out this page and that site
in general - you can see what others have done when faced with
a similar situation and you can customise them according to your
needs!

CREATE TABLE vehicle
(
  vehicle_id SERIAL,
  vehicle_owner INTEGER, -- references owner table (not shown)
  vehicle_driver INTEGER, -- references driver table (not shown) - may want different drivers even for same vehicle?
  vehicle_capacity
);

CREATE TABLE vehicle_owner
(
  owner_id SERIAL,
  owner_name VARCHAR(30),
  .. other fields
  ..
);

HTH, and best of luck.


Find IDs in table where at least two entries exist with that ID and an additional field is A for one and B for the other entry

find all game_ids where user A and user B played together

SELECT t1.game_id
FROM table t1, table t2
WHERE t1.game_id = t2.game_id
  AND t1.user_id = 'A'
  AND t2.user_id = 'B'

If you need the same for 3, 4 or more users, you can add more table copies, or use

SELECT game_id
FROM table
WHERE user_id IN ('A', 'B', 'more users')
GROUP BY game_id
HAVING COUNT(user_id) = :users_count:

If you want to use users list as a parameter, you can use

SELECT game_id
FROM table
WHERE FIND_IN_SET(user_id, :comma_separated_users_list:)
GROUP BY game_id
HAVING COUNT(user_id) = :users_count:


Why am I getting a deadlock for a single UPDATE query?

Your statement modifies several rows. Each of these rows is locked when it is updated.

It is well possible that a statement in a concurrent transaction has already locked one of these rows, blocking your UPDATE. If the concurrent transaction then tries to lock one of the rows that your UPDATE has already locked, you get a deadlock.


Enhanced ER Model Supertype / Subtype modeling issue with inheritance and recursion

I want to call attention to one of your business rules: Representative is Customer and is Company.

This sounds like a superclass/subclass situation to me, and I think you have the EER diagram well in hand, as far as ER modeling goes.  Where things start to get interesting (and a little messy) is when you try to switch over from an ER model (which is abstract and implementation neutral) to a relational model (which is not).

At this point, you'll have to choose between a few well known techniques that make up for the fact that the relational model doesn't incorporate inheritance.  You may wish to include this tag in your original question:  subtypes

Here are three of the techniques in a nutshell:  single table inheritance, class table inheritance, and shared primary key.

Single table inheritance lumps all the subclasses together in one table.  Data that does not pertain to a given instance is left NULL.

Class table inheritance has one table for the superclass and one table for each subclass.  Generalized data goes in the superclass table, and specialized data goes in the appropriate subclass table.

Shared primary key enforces the one to one nature of the relationships between each subclass and the superclass.  It also allows a foreign key elsewhere in the database to reference the superclass without knowing or caring which subclass it belongs to.

For a good treatment of this subject, look up Martin Fowler's treatment on the web.

Edit based on feedback

The question of which ER model is "correct" is usually determined by the database design team by analyzing the subject matter, and coming up with a model that depicts the way it works in "the real world".  This presumes that the database is yet to be designed, but the business itself has already been designed, and the business design is not to be changed.  Your comment makes me think that you are designing (or redesigning) the business itself, not just the database.  If so, then the question is outside the expertise of database design.

To deal more specifically with the questions you asked:

Is supertype / subtype needed here (since subtype Entities have no unique attributes and since it gives me a headaches)

If there are no attributes that are specialized to the subtypes , then no inheritance (from a data perspective) is needed.  So it won't make any difference to the relational design.  Don't give yourself headaches.  Pick a model that works for your business, and let it go.

How to implement inheritance with one entity in recursive relationship - if for instance Customer / Representative is in recursive relationship (relational table example needed)

If Customer and Representative are the same entity, give them a common name.  One entity with two names is confusing.  If they are different entities, rephrase your question.  From my answer to question 1, why is implementing inheritance called for?

Are "recursive relationship" and "reflexive relationship" the same thing?  If not, please clarify your question.

Can you give me relational solution (EER to relational) with some examples (like table with data) so I can wrap my mind around it.

That's what I thought I was answering the first time.  Fowler's treatment of inheritance/relational issues is available on the web.  Here and here.
His examples are about players and cricketers, not customers and representatives, but the issues concerning implementing inheritance (really pseudo inheritance) in a relational system are not case dependent.


Columnstore index for Key/Value Pair table on SQL Server

What better way than to test?

TL:DR up front. The way you structure your data makes it awkward when there are multiple conditions, as you have to check different rows for the same response. Columnstore works better simply for using batch mode in the self join of my test query.

I would expect you to gain far more from structuring your data in a different way than by looking at storage options.

Here's a setup with 5m rows, and an example query at the end looking for average age of people in the city center for a range of responses (assuming one particular survey). Dbfiddle link as well

DROP TABLE IF EXISTS dbo.AnswersRow

CREATE TABLE AnswersRow (
ResponseID INT NOT NULL,
QuestionID INT NOT NULL,
AnswerValueText VARCHAR(20) NULL,
AnswerValueDecimal DECIMAL(15,2) NULL,
CONSTRAINT PK_R_Q PRIMARY KEY CLUSTERED (ResponseID,QuestionID)
)

INSERT dbo.AnswersRow
SELECT TOP 5000000
FLOOR((-1+ROW_NUMBER() OVER(ORDER BY(SELECT 'Joe')))/5+1) AS ResponseID,
(-1+ROW_NUMBER() OVER(ORDER BY(SELECT 'Joe')))%5+1 AS QuestionID,
NULL AS AnswerValueText,
NULL AS AnswerValueDecimal
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b

--5 questions
--1) Married 0 or 1
--2) BuildingType
--3) CityArea
--4) Age
--5) Income

--randomize answers
UPDATE ar
SET AnswerValueText = CASE
WHEN QuestionID = 2 THEN (CASE x2.seed
                            WHEN 0 THEN 'Concrete'
                            WHEN 1 THEN 'Skeleton'
                            WHEN 2 THEN 'Eternite Sheets'
                            WHEN 3 THEN 'Iron Sheets'
                            WHEN 4 THEN 'Other'
                            END)
WHEN QuestionID = 3 THEN (CASE x3.seed
                            WHEN 0 THEN 'North'
                            WHEN 1 THEN 'South'
                            WHEN 2 THEN 'East'
                            WHEN 3 THEN 'West'
                            WHEN 4 THEN 'Center'
                            END)
ELSE AnswerValueText END,
ar.AnswerValueDecimal = CASE
WHEN QuestionID = 1 THEN (FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*2))
WHEN QuestionID = 4 THEN (FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*60+17))
WHEN QuestionID = 5 THEN (FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*200000))
ELSE AnswerValueDecimal END
FROM dbo.AnswersRow ar
CROSS APPLY (SELECT FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*5) AS seed) x2
CROSS APPLY (SELECT FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*5) AS seed) x3

SELECT *
INTO dbo.AnswersCol
FROM dbo.AnswersRow

CREATE CLUSTERED COLUMNSTORE INDEX CX_Answers ON dbo.AnswersCol
CREATE NONCLUSTERED INDEX IX_Answer_text ON dbo.AnswersRow(AnswerValueText)
CREATE NONCLUSTERED INDEX IX_Answer_decimal ON dbo.AnswersRow(AnswerValueDecimal)
CREATE NONCLUSTERED INDEX IX_Answer_question ON dbo.AnswersRow(QuestionID) INCLUDE(AnswerValueText,AnswerValueDecimal)

--average age of particular survey, city center
SELECT AVG(AnswerValueDecimal), COUNT(*)
FROM dbo.AnswersRow ar1
WHERE 1=1
AND ar1.ResponseID >= 100000 AND ar1.ResponseID < 200000
AND ar1.QuestionID = 4
AND EXISTS (SELECT 1
            FROM dbo.AnswersRow ar2
            WHERE ar1.ResponseID = ar2.ResponseID
            AND ar2.QuestionID = 3
            AND ar2.AnswerValueText = 'Center'
            )

SELECT AVG(AnswerValueDecimal), COUNT(*)
FROM dbo.AnswersCol ar1
WHERE 1=1
AND ar1.ResponseID >= 100000 AND ar1.ResponseID < 200000
AND ar1.QuestionID = 4
AND EXISTS (SELECT 1
            FROM dbo.AnswersCol ar2
            WHERE ar1.ResponseID = ar2.ResponseID
            AND ar2.QuestionID = 3
            AND ar2.AnswerValueText = 'Center'
            )


select columns with different conditions

When doing a GROUP BY, it is improper to list anything other than the group-by columns and aggregates in the SELECT part.  In your case, the values of b.* could be random.

Are you trying to compute both counts at the same time?  If BookingDate is inc, then it is easy.  If it is in the other table, then it gets messier.  _WhenJOINing`, please make it clear which columns come from which tables._

SELECT COUNT(c.CustomerID) AS count_all,
       SUM(( SELECT BookingDate FROM Booking
                     WHERE BookingCustomerID = c.CustomerID
                       AND BookingDate LIKE '2019-05-04%' )) AS count_day
    FROM Customer AS c
    GROUP BY c.CustomerId

To get the rest them,

SELECT x.count_all, x.count_day
       c.CustomerTelephone,
       b.*
    FROM ( ... the query above ... ) AS x
    JOIN Customer AS c USING(CustomerID)
    JOIN Booking AS b ON b.BookingCustomerID = x.CustomerID
    FROM Customer AS c
    GROUP BY c.CustomerId


How to close mongodb idle connection(active: false)?

This can be avoided by setting up the maxIdleTimeMS parameter of Connection Pool Options

  This maxIdleTimeMS parameter sets the maximum number of milliseconds
  that a connection can remain idle in the pool before being removed and
  closed.

Set this parameter as per your requirement. So the idle connections will be closed once the time limit exceeds.


Is there any SQL Database that will use UDP replication?

Years ago, I was DBA for a system that was replicating from USA to China (many years ago).  An earthquake happened in the Pacific, disrupting most traffic to/from east Asia.  Connections were flaky for most of a day, by which time traffic had been rerouted (I guess) through the Suez Canal instead of the Pacific Ocean.  Slave connections came and went, but MySQL replication survived.

"UDP is faster" -- I suggest that this is an 'uninformed' reason.  MySQL uses TCP/IP because it needs to know that the packets arrive at the other end.  With UDP, replication can break due to network issues, and MySQL would not know it.  That leads to a much worse mess than being "slower".

CH:CN might be about 250ms -- that is network travel time.  One packet will take that long, whether you are using TCP/IP or UDP.  TCP/IP will seem slower because it will send only a certain number of packets before waiting for ACK/NAK.  That can (and should) probably be tuned due to the long distance.

Once you have done that low level tuning (at the OS, not MySQL), TCP/IP will be about as fast as UDP.  But reliability is the real reason to tell your client to jump in a lake.  (Sorry, I don't know the OS tunables.)

As for Replication being "down for a long time"...  (I assume you mean hours or days.)  Yes, you should plan for such.  Set expire_logs_days to a high enough number.  I like 14; that's days.)  Plus have monitoring to see if things have gone bad.  UDP can't help with the network being down.


Why is a hash match operator in this very basic query

The first query is using a parallel plan, meaning the "work" was split into multiple tasks carried out by multiple threads. The cumulative CPU time was therefore higher than for the serial plan used for your second query.

As to why the distinct causes the hash match operator to appear in the plan; an aggregate or sort operation is required to determine the DISTINCT result. @SQL_Kiwi might pop up with a more indepth explanation shortly but the hash match operator is apparently favoured for larger result sets.

  The optimizer tends to prefer the Hash Match Aggregate on larger
  rowsets, with fewer groups, where there is no reason to produce a
  sorted output, and where the incoming rows are not sorted on the
  DISTINCT expression(s).  Larger inputs favour hash matching because
  the algorithm generally scales well (although it does require a memory
  grant) and can make good use of parallelism.  Fewer groups are better
  for hashing because it means fewer entries in the hash table, and the
  memory needed to store unique values is proportional to the number of
  groups (and the size of the group).  Hash matching does not require or
  preserve the order of the incoming row stream. [Source]


Why does "SELECT *" take longer than "SELECT "?

Your query:

SELECT *
FROM cupom_fiscal
WHERE send_date IS NULL
  AND travado IS FALSE
  AND status = 0
  AND ong_id = 1618
  AND data_emissao BETWEEN '2014-04-01' AND '2014-05-05'
LIMIT 1 ;

involves only one table and 5 of its columns in the WHERE clause. Four conditions are equality (=) and the 5th is a range condition (BETWEEN .. AND).

(parenthesis)

The query is not very efficient as it uses the index_merge algorithm, i.e. it uses more than one index (actually two: status and fkey_id_ong) and then "merges" the results using the index_merge/intersect algorithm (Read: Using intersect(status,fkey_id_ong);.) After that operation, it has to also check the actual rows of the table (because, I guess, these two indexes do not include all the 5 columns of the WHERE clause.) This also has a negative effect on efficiency.

The best index for this query would be a 5-column index where the 5th column in data_emissao (the column with the range condition.) The order of the first 4 columns in the index would not matter.

It may matter for other queries but not for this one. So, choose an order that would help other queries as well (if for example you have many queries that use ong_id, use that as the 1st column of the index.


SQL Server 2014-Can we stop sql auto recompile process?

You can monitor for statement level recompile events using Extended Events. I have a blog post here that lays out exactly how to do it. You can see that there is a column for the recompile reason. That will tell you explicitly why any given statement was recompiled.
You can, for a plan that stays in cache, prevent it from recompiling while it's in cache by using the query hint KEEPFIXED PLAN. If the plan ages out of cache, it will still have to compile the next time the query is run. This also doesn't prevent recompiles from structural or code changes. Drop an index, change the procedure, you still get a recompile. This is because it can no longer be assured that the plan will work because it may reference the index you dropped, or a different column than the code you submitted. So it has to recompile. However, for statistics updates, KEEPFIXED PLAN will prevent automatic recompiles.
There is also a KEEP PLAN hint that will reduce the number of recompiles due to data changes, but it won't eliminate them the way KEEPFIXED PLAN will.
One more option would be to create plan guides for specific plans where recompilation is taking significant resources. Although, for plan guides and plan forcing using query store, the compilation process still runs, using up those resources. You just end up with a specified plan.


T SQL, Get new customer added or deleted from historical table

Not sure I completely understand your intention or the difference between Table1 and Table2.
If you are trying to find just the IDs that only occur once in the last two days.
SELECT StaffID, count(StaffID)
FROM Table1
WHERE CensusDate >= cast(dateadd(day, -2, getdate()) as date)
Group By StaffID


Why would I not run "index reorganization" regularly?

Reorganizing an Index should be done when you have elevated amounts of white space within your index (i.e. avg_page_space_used_in_percent column in the sys.dm_db_index_physical_stats DMV).  Many people (and even solutions) in the SQL Server community say you need to look at Index Fragmentation levels (e.g. the avg_fragmentation_in_percent in that same DMV), but this is not as much a concern depending on the type of underlying storage (e.g. anything that's RAIDed fragments your data for redundancy purposes regardless).  Others also have come to the same conclusion.

The downsides to Reorganizing your Indexes when not needed is that this operation will purge portions of your cache so it can have sufficient room to perform the reorg within memory. Reorgs aren't as destructive to your cache as Rebuild operations, but they will reduce your Page Life Expectancy (PLE) and potentially purge useful data that you will have to re-read from disk (e.g. increased I/O operations).  Additionally, Reorg operations do NOT update Index Statistics.  Updated Statistics do more to produce optimal executions plans than defragmented indexes, so if you're going to mindlessly run a nightly operation I would say Updating Stats is the better choice over Reorging instances.

The right answer though is to have a solution check your White Space thresholds and reorg/rebuild indexes when needed.  Have another process check your statistics and update when needed.  There are a few solutions out there, such as Ola's maintenance solution, Minionware's Reindex solution, etc.  But I often find myself customizing solutions to fit my needs.


Purging Binary Logs from Master in a Replication Environment

It's ok to purge binlog on M 01 ~ M 06 as long as S 01 ~ S 06 has relayed all the binlog you want to purge, but remember to purge slowly, for example each time purge 20 binlogs (depends on your single binlog file size and the server load)


How to recover data from a corrupted /base/ directory?

It seems like you have deleted folders like

pg_xlog
pg_tblspc
pg_serial

With only base directory :   /var/lib/postgresql/9.5/main/base/18686
You can't recover data.
You can refer to the documentation.

If you have a dump file recovering from it might be the only solution.


How do I backup/restore database encryption key in order to restore on new server

If you are receiving the thumbprint error the certificate was not created properly from the Source Server's Cert/Key backups.

I found this question which was already answered with a solution for you:

Restore encrypted database to another server


Here's how you hide How to: Hide an Item (Reporting Services)

Here's the expression

=IIF( Fields!yourField1.Value = 0  and Fields!yourField2.Value = 0
  and Fields!yourField3.Value = 0  , True, False )


MySQL partitioning table by date and hour

Use 12 partitions -- 10 days, plus one at each end.  REORGANIZE PARTITION to split the empty, future, partition at the end before the new day; drop the oldest.  All the details, including code, are spelled out in http://mysql.rjweb.org/doc.php/partitionmaint .

Hourly partitions would lead to over 240 partitions; that large a number slows things down.


If you have firewall running, allow connections on port 27017, MongoDB default port.

Not sure which RHEL version you are running so below commands might change.

1. Any connections can connect to MongoDB on port 27017

iptables -A INPUT -p tcp --dport 27017 -j ACCEPT

2. Only certain IP can connect to MongoDB on port 27017

iptables -A INPUT -s <ip-address> -p tcp --destination-port 27017 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -d <ip-address> -p tcp --source-port 27017 -m state --state ESTABLISHED -j ACCEPT

For Eg:

    iptables -A INPUT -s 192.168.6.100 -p tcp --destination-port 27017 -m state --state NEW,ESTABLISHED -j ACCEPT
    iptables -A OUTPUT -d 192.168.6.101 -p tcp --source-port 27017 -m state --state ESTABLISHED -j ACCEPT

Also i personally believe commenting bind option is not a good idea.

Instead you can use below in /etc/Mongod.conf :

# Listen to local and LAN interfaces.
bind_ip = 127.0.0.1,x.x.x.x, x.x.x.x

and then

service mongod restart


Yes. Now my disclaimer is I'm not your licensing rep or Microsoft so you should check with them on questions.

But the verbiage in the various license guides is typically "A Server running SQL Server - or any of its core components (SSRS, SSAS, SSIS)".

So if you are using SSIS on a server standalone, by the licensing guidelines you are running one of the core components on another server and that should be licensed separately from any other machine running a core component.

Depending on the load of your SSIS server, I have typically found that in many installations, just running the SSIS server on an already licensed machine - be it your warehouse server or a staging server or even your main production server works.

When you get into complex SSIS workloads with lots of moving parts and pieces and high overhead, you may start considering a separate processing server that perhaps also contains the DB instance for your SSIS logging and reporting type of environment. I'd say that I have to consider that sort of separation for performance rarely lately, though I have in the past.

Microsoft publishes several detailed and overview license guides. This one here is an example for SQL Server 2012.


MongoDB: Server sockets closed after a few minutes

First a probably silly question, are you replacing all the , , etc placeholders with the actual values from your compose.io database?

If you are already doing that, maybe you can debug the connection using the mongo shell to connect to your DB instances.

If you have local install of mongodb you can use:

  mongo host:port/database-name -u user -ppassword

This is also what compose.io docs recommend for connection debugging https://docs.compose.io/common-questions/how-to-connect-to-mongodb.html


Updating a compatibility mode 80 database for SQL Server 2014

How about spinning up a SQL Server 2000 or 2005 server.  Then script out the 80 database objects and create them on the earlier version SQL Server.

Then run the SQL Server 2008 R2 upgrade advisor to collect the 2000 and 2005 errors that might exist. Once you fixed those issues on 2008 R2, then you can run the 2014 Upgrade Advisor.


Limit crosstab query based on join from outer query

If all the export can happen through a single database connection, or even better, through a single transaction, it would be simpler and more efficient to just use a server-side CURSOR instead of a pagination based on successive queries with LIMIT/OFFSET.

For instance:

BEGIN;

DECLARE cursor_name FOR
WITH "cross_responses" AS (select * from crosstab(
       $$select...
        ...
) SELECT  ...blah blah...
ORDER BY "assessments"."id" desc, assessments.end_time DESC NULLS LAST,
assessments.start_time DESC NULLS LAST

Then call the SQL statement FETCH 1000, stream the results to the web client, until FETCH returns no row. Then close the transaction, and the cursor will disappear with it.

It's also possible to not use a transaction if the cursor is declared WITH HOLD.

If multiple passes on the query must be run by independant connections (such as if there are multiple invocations by a web server), another possibility is to not use the crosstab() function, and do the pivot operation in the main query itself. This is not difficult because there are only 3 static columns.

For instance:

 select
  assessments.id as id,
  max(case when i.name='Last Name' then r.value end) as "Last Name",
  max(case when i.name='First Name' then r.value end) as "First Name",
  max(case when i.name='E-mail Address' then r.value end) as "Email"
 from assessments
     left join users u on u.id = assessments.user_id
     inner join responses r on r.assessment_id = assessments.id
     inner join items i on i.id = r.item_id
 where i.name = ANY ('{E-mail Address, First Name, Last Name}')
 group by assessments.id
 order by 1 LIMIT 1000 OFFSET 4000;


MS Access linked table displays short text instead of Decimal data type

I have discovered that even though the data types are directly comparable, the data precision is not. Access can handle a max precision of 28, hence why the conversion into short text.


What is the advantage of using numeric primary keys in a relational database?

Size is one consideration, certainly.  Consider not only the size of the index in the widgets table, but also that primary keys show up in other tables as foreign keys.  In some systems, even short strings are going to take up more space than integers (e.g., MSSQL:  VARCHAR is a byte per character plus 2 bytes, so at only two characters you're already as large as an integer).

Almost all numeric primary keys I have seen are surrogate keys, implemented via series, sequence, auto_increment, IDENTITY or whatever the database engine's native method is for generating values itself.  I suspect this is a leading reason for the ubiquity of the numeric primary key.  One advantage to surrogate keys is that they have no business meaning.  Since business meaning can change over time, using a key without business meaning helps ensure that the primary key is static.

If your widgets have some sort of industry-standard identifier (like the auto industry's VIN, publishing's ISBN, UPCs and so forth), that's probably the best choice for your primary key.  My concern in using widget_name is that attribute's immutability.  Will it ever change?  How do you know it will never change--did Sales tell you that?  :)

The whole surrogate vs. natural key issue is nearly a religious debate, and is sort of tangential to your question.  I would say if you have a natural key that is static, minimal, and unique, use it.  Otherwise, consider a surrogate key (which is likely going to be numeric).


What is MySQL's "Implicit Default Value" and how does it work with "strict mode"?

An "Implicit Default Value" seems to be in reference to the type; the "Explicit Default Value" or simply "Default Value" seems to be in reference to the column's DEFAULT,

Also from the docs,

  Inserting NULL into a column that has been declared NOT NULL. For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. INSERT INTO ... SELECT statements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT to see whether it returns a single row. (For a single-row INSERT, no warning occurs when NULL is inserted into a NOT NULL column. Instead, the statement fails with an error.)

you can see this documented here in "11.7 Data Type Default Values"

Running through a quick check,

SET sql_mode='';
CREATE TABLE foo ( a int NOT NULL );
-- all of these insert the value 0
INSERT INTO foo (a) VALUES (DEFAULT);
INSERT INTO foo (a) VALUES (a);
INSERT INTO foo () VALUES ();

Whipe it and set strict mode,

TRUNCATE TABLE foo;
SET sql_mode='strict_all_tables';

Retry,

INSERT INTO foo (a) VALUES (DEFAULT);
ERROR 1364 (HY000): Field 'a' doesn't have a default value

-- still inserts "implicit default", doesn't gaf about strict mode.
INSERT INTO foo (a) VALUES (a);
Query OK, 1 row affected (0.01 sec)

INSERT INTO foo () VALUES ();
ERROR 1364 (HY000): Field 'a' doesn't have a default value

You'll see now only one row in the table which is a result of the goofy syntax here that is still permitted with "strict mode".


What is Clustered index data page behavior on inserts if all columns make up the index?

It's not clear to me how your first paragraph relates to the second, but I can answer the following bit:

  Since the pages have 20% free space for the inserts, what happens
  during the inserts to the records that already exist on the page and
  the new record would fits in between them? Does a page split occur or
  does the rest of the records move on the page or something else?

When there is enough free space on the page for the record being inserted (whether in the middle or at the end), there will be no page split, as the page can simply be rewritten with the new data inserted.
If there is insufficient space, then there will be a page split, with approx half the data moved to a new page.
Note that if the insert is to the end of the very last page, and there's no space, then a new page will be created just for the new row and subsequent appends. That's what's known as a "good" page split, though it really isn't a split at all even though SQL server counts it as one.


How to get session parameters changed by the user in Oracle?

This one is provided by one of the oracle gurus

There are several Initialization Parameters that influence the Optimizer and that can be modified with ALTER SESSION. We can see whether certain sessions have settings different from the System-wide settings with a query like

QL> set lines 200
col name for a40
col username for a10
col value for a40
select a.sid,c.username,a.name,a.value
from v$ses_optimizer_env a
join v$sys_optimizer_env b on a.id=b.id
join v$session c on a.sid=c.sid
where a.value<>b.value
and c.username is not null
and c.username not in ('SYS','SYSTEM','DBSNMP')
order by a.sid,a.name;


How to check the pre-allocated database file of MongoDB is Used or not?

I am not very familiar with MongoDB, but from this link:

  As soon as a datafile starts to be used, the next one will be preallocated.

It seems to me that (on *nix at least), MongoDB will always have a datafile that is empty, or mostly empty. If it's writing heavy and filling up the file, the last file might have some data in it before a new empty file can be created.

  Additionally, on Unix, mongod will preallocate an additional datafile in the background and do background initialization of this file. These files are prefilled with zero bytes.

So to determine if it's used, check the file for non-zero bytes.


Unexpected (not helpful) result on first run of sp_BlitzIndex

It sounds like you ran it with @GetAllDatabases = 1, and an error occurred. For example, you may have more than 50 databases on your system, or it may have encountered a dynamic SQL problem.

When you're troubleshooting why a query doesn't produce the results you expected, head on over to the Messages tab in SSMS.


PostgreSQL (true up to at least 9.4) doesn't currently support removing a column with CREATE OR REPLACE VIEW.

  The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list.

There is no fundamental reason why support for dropping columns couldn't be added, but nobody has done the work required to implement it yet.

CREATE OR REPLACE VIEW would have to recursively scan all dependencies and make sure that none of them referenced the column to be dropped. If they used SELECT * it'd have to remove the column from the expansion of * in the dependency then scan its dependencies too. There's a fair bit of work involved in doing that, and there are some areas where it's not clear how exactly dropping the column should behave especially when it comes to interactions with dump and reload. So nobody's wanted the feature enough to implement it yet. Patches and/or sponsorship of development are welcome.

You will have to drop the view and everything that depends on it, then re-create it and its dependencies. (The same used to be true for adding a column to a view; support for adding columns was introduced in 8.4).

Note that in general there's no expectation that DDL be reversible. The concept of "devolutions" is really flawed. For example, if you drop a column, then add it again, the data is still gone.


You could do this with sp_executesql and assigning the variable to an output parameter

DECLARE @numreturned  INT;
DECLARE @sql NVARCHAR(MAX);
DECLARE @fulllocation NVARCHAR(MAX) = 'nagadishi.mirama.dbo.Humizan';

SET @sql = N'SELECT @numreturned = COUNT(*) FROM '+@fulllocation+' '
                   +N'WHERE ([Nishan] IS NULL)';

EXEC sys.sp_executesql @sql, N'@numreturned INT OUTPUT', @numreturned = @numreturned OUTPUT;
SELECT @numreturned;


Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

Depending on your permissions, the linked server could be trying to stream all the data over locally and then doing filtering. References

Performance implications of using OPENQUERY in a view
Which one is more efficient: select from linked server or insert into linked server?
http://blogs.msdn.com/b/psssql/archive/2009/09/02/distributed-queries-remote-login-permissions-and-execution-plans.aspx

You might be able to skip that pain by computing the total aggregate count first into a table on the local server and then beat against that.

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT
    p.package_uuid
,   count(d.external_identification) AS [count]
FROM
    ServerB.DATABASE.dbo.package p
    INNER JOIN
        ServerB.DATABASE.dbo.doc2 d
        ON p.package_id = d.package_id
GROUP BY
    p.package_uuid;

Try running that query locally on ServerB first to get an understanding of the theoretical throughput without factoring in your network. You can then do some quick and dirty estimates based on data sizes (500 + 8 per row in temporary table) and then it depends on your network. Hopefully this is all local network.

If the time is significantly different between the run on ServerB and pulling it back over, then you might need to use the OPENQUERY syntax to force the join on the remote server. Code approximately

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT
    OQ.package_uuid
,   OQ.[count]
FROM
    OPENQUERY(ServerB,
    N'
    SELECT
        p.package_uuid
    ,   count(d.external_identification) AS [count]
    FROM
        DATABASE.dbo.package p
        INNER JOIN
            DATABASE.dbo.doc2 d
            ON p.package_id = d.package_id
    GROUP BY
        p.package_uuid
    ) AS OQ;


SQL Server - How to know which tables are used and which are not?

SELECT t.name AS [TableName],
       fi.page_count AS [Pages],
       fi.record_count AS [Rows],
       CAST(fi.avg_record_size_in_bytes AS int) AS [AverageRecordBytes],
       CAST(fi.avg_fragmentation_in_percent AS int) AS [AverageFragmentationPercent],
       SUM(iop.leaf_insert_count) AS [Inserts],
       SUM(iop.leaf_delete_count) AS [Deletes],
       SUM(iop.leaf_update_count) AS [Updates],
       SUM(iop.row_lock_count) AS [RowLocks],
       SUM(iop.page_lock_count) AS [PageLocks]
  FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) AS iop
  JOIN sys.indexes AS i ON iop.index_id = i.index_id AND
                           iop.object_id = i.object_id
  JOIN sys.tables AS t ON i.object_id = t.object_id AND
                          i.type_desc IN ('CLUSTERED', 'HEAP')
  JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS fi ON fi.object_id=CAST(t.object_id AS int) AND
                                                                                fi.index_id=CAST(i.index_id AS int)
  GROUP BY t.name, fi.page_count, fi.record_count, fi.avg_record_size_in_bytes, fi.avg_fragmentation_in_percent
  ORDER BY [RowLocks] desc

Remarks:
sys.dm_db_index_operational_stats

  (How the Counters in the Metadata Cache Are Reset

  The data returned by sys.dm_db_index_operational_stats exists only as
  long as the metadata cache object that represents the heap or index is
  available. This data is neither persistent nor transactionally
  consistent. This means you cannot use these counters to determine
  whether an index has been used or not, or when the index was last
  used. For information about this, see sys.dm_db_index_usage_stats.

  The values for each column are set to zero whenever the metadata for
  the heap or index is brought into the metadata cache and statistics
  are accumulated until the cache object is removed from the metadata
  cache. Therefore, an active heap or index will likely always have its
  metadata in the cache, and the cumulative counts may reflect activity
  since the instance of SQL Server was last started. The metadata for a
  less active heap or index will move in and out of the cache as it is
  used. As a result, it may or may not have values available. Dropping
  an index will cause the corresponding statistics to be removed from
  memory and no longer be reported by the function. Other DDL operations
  against the index may cause the value of the statistics to be reset to
  zero.)


compare to total, compare to percent of a main category, compare over periods e.g. month over month to get growth %

If anyone stumbles upon my question looking for an answer, I solved the % of total by persisting a table with totals per period (month, week, etc), then joined that table with the data table from above and used a sum(net_revenue)/max(total_revenue) and a visual filter for the period. It's not the most elegant solution, but it allows for some visual interaction within the nested categories.


I need to verify that the number that I will insert into table tbl_database is not in tables tbl_blacklist and tbl_cmdatabase

Perhaps you can do this with something like

SELECT count(*)
FROM (
  SELECT number FROM tbl_blacklist
  UNION
  SELECT number FROM tbl_cmdatabase ) AS Q
where Q.number = '$number'

When the count returned is 0, it means that $number is not in the tables. It's possible this is not the best query for this job, but I think it will work OK.

You can play around with it here:

http://sqlfiddle.com/#!2/7d697/23


How can I create a table to lookup values for combinations of varying sized sets of numbers?

I'll try with a table structure this way:

create table packaging_group
(
    pack_group_id int primary key,
    product_id int foreign key references products(product_id),
    packaging_id int foreign key references packages(package_id)
);

No matter how many groups and how many products fit a package. You can always add/remove products to an specific package.


How can I hide a column using a Select statement?

You just need to add a case statement to your select list:

declare @bid varchar(4) = '1007'

select sr.release_date,
    sr.customer_id,
    sr.customer_name,
    sr.item_id,
    sr.item_desc,
    sr.order_no,
    sr.qty_on_hand,
    sr.release_qty,
    sr.qty_after_released,
    sr.extended_sell,
    sr.enough,
    case when sr.route_code in ('pm 01','pm 03','pm 05') then sr.route_description else '' end as route_description
from sri_schedule_releases_view as sr
where
    ((case
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'John')
    end = @bid) or
    (case
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Jane')
    end = @bid) or
    (case
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Doe')
    end = @bid) or
    (sr.contact_id = @bid))
order by sr.item_id asc


Should a ledger DB store a separate line for each side of a transaction?

If you decide to keep a single entry for both sides of a transaction, then by definition you are engaging in single-entry bookkeeping. This may be the most appropriate solution for some simple applications, but be clear that you are losing all the functional and robustness advantages of double-entry bookkeeping, in exchange for a simpler design.

Note that when viewed stand-alone Subledgers (though not their corresponding Journals) are often implemented as single-entry, since a control-account in the General Ledger captures the subledger total and the balancing side of the transactions are in the General Journal (GJ) and General Ledger (GL).

You also appear to be confusing the distinct concepts of Ledger and Journal in traditional double-entry bookkeeping. The various Journals (of which there will be numerous specialized varieties for specific common transactions of the business in addition to the General Journal) is a chronological history of all transactions entered into the system. The General Ledger is an ordering by account of all transactions entered into the system, and the various subledgers are an ordering by subledger-code of all transactions entered into the corresponding Journal.

Two examples of common Ledger and Journal combinations:

the General Journal and General Ledger are an aggregated summary of
all transactions used for generating Trial Balances and the Financial
Statements.
The Account Receivable subledger receives entries from the Invoicing Journal (debits to AR by Customer Number) and the Cash Receipts Journal (credits to AR by Customer Number).

Note that while both sides of every transaction will balance in toto, in general it is not required for the two sides of a transaction to be the same number of lines with the same values. For instance a simple Retail Sales transaction (in most jurisdictions) will look something like this:

Dr Accounts Receivable Customer #xxxx     $X,XXX.XX
Cr Revenue                                           $Z,ZZZ.ZZ
Cr Sales Tax Payable                                 $  YYY.YY
-----------------------------------------------------------------
Totals                                    $X,XXX.XX  $X,XXX.XX

A manufacturing Sales transaction (at the simplest level) will start like this:

Dr Accounts Receivable Customer #xxxx     $X,XXX.XX
Cr Revenue                                           $Z,ZZZ.ZZ
Cr Sales Tax Payable                                 $  YYY.YY
Dr Cost of Sales                          $U,UUU.UU
Cr Finished Goods Inventory                          $U,UUU.UU
-----------------------------------------------------------------
Totals                                    $W,WWW.WW  $W,WWW.WW

A Bond Purchase transaction will have to handle the Discount(Premium) on Face Value as either a Credit or a Debit depending on whether interest rates have risen or fallen since the bond was issued, so you won't even know in advance which side of the ledger your single-entry is on.

Once you decide to engage in single-entry bookkeeping you lose all ability to handle these transactions, which as seen here are by no means either rare or complicated.


postgres9.5: can't create extension on standby

As the error message says: the standby is read-only you can't run any statements on the standby that change things.

If you need the extension on the standby you need to create it on the master


Find application password expiration date

Use the LOGINPROPERTY function by specifying the DaysUntilExpiration property:

select loginproperty('YourLoginName', 'DaysUntilExpiration');

Reference: BOL reference on LOGINPROPERTY


Generally when there are geographically distributed clusters, having a disk witness does not make sense. This is due to the need to have block level synchronous disk replication between all sites while preserving things like write ordering and scsi reservations.

In almost all instances of having a geo-cluster it's best to go with a fileshare witness. The two different types of witnesses do the same thing (there are subtle differences like how the arbitration happens, etc.)

Where should you place the witness? The best place would be a neutral 3rd site, however most won't have this available. My recommendation is to place this at the site you want to keep primary in the event of a communications loss between the nodes at each site. In your case I'd likely put it on the primary site. It should be somewhere that is as highly available as possible.