Hopefully this should provide an answser:
This implements "common" relational division:
SELECT DISTINCT grpid
FROM groups AS g
GROUP BY grpid, changeDate
HAVING NOT EXISTS
    ( SELECT 1
      FROM groups AS gi
      WHERE gi.grpid = g.grpid
        AND gi.changeDate = g.changeDate
        AND NOT EXISTS
            ( SELECT 1
               FROM groups AS gk
               WHERE gk.grpid = gi.grpid
                 AND gk.changeDate <> gi.changeDate
                 AND gk.userid = gi.userid
                 AND gk.pct = gi.pct
                 AND gk.hobby = gi.hobby
            )
    ) ;

Result:
grid
   1
   4
   7

This implements exact relational division, which results in even more complicated code. Your case, where the groupings/versions are determined by two columns (grpid, changeDate) makes it look like even more complicated.
Tested in dbffdle.uk
Query 1:
SELECT DISTINCT g1.grpid
FROM
  ( SELECT grpid, changeDate
    FROM groups AS g
    GROUP BY grpid, changeDate
  ) AS g1
  JOIN
  ( SELECT grpid, changeDate
    FROM groups AS g
    GROUP BY grpid, changeDate
  ) AS g2
  ON  g1.grpid = g2.grpid
  AND g1.changeDate < g2.changeDate
WHERE NOT EXISTS
    ( SELECT 1
      FROM groups AS gi
      WHERE gi.grpid = g1.grpid
        AND gi.changeDate = g1.changeDate
        AND NOT EXISTS
            ( SELECT 1
               FROM groups AS gk
               WHERE gk.grpid = g2.grpid
                 AND gk.changeDate = g2.changeDate
                 AND gk.userid = gi.userid
                 AND gk.pct = gi.pct
                 AND gk.hobby = gi.hobby
            )
    )
   AND NOT EXISTS
    ( SELECT 1
      FROM groups AS gi
      WHERE gi.grpid = g2.grpid
        AND gi.changeDate = g2.changeDate
        AND NOT EXISTS
            ( SELECT 1
               FROM groups AS gk
               WHERE gk.grpid = g1.grpid
                 AND gk.changeDate = g1.changeDate
                 AND gk.userid = gi.userid
                 AND gk.pct = gi.pct
                 AND gk.hobby = gi.hobby
            )
    )
 ;

Query 2 that uses MySQL's GROUP_CONCAT function:
SELECT DISTINCT grpid
FROM
  ( SELECT grpid, changeDate,
           GROUP_CONCAT( CONCAT_WS('-', userid, hobby, pct)
                         ORDER BY userid, hobby, pct
                         SEPARATOR '  '
                       ) AS groupdata
    FROM groups AS gr
    GROUP BY grpid, changeDate
  ) AS g
GROUP BY grpid, groupdata
HAVING COUNT(*) > 1 ;


MongoDB Replica Set Can't be connected To I just set up a replica set for a standalone Mongo instance using the following changes to my config and rs.initiate().

# in master/slave replicated mongo databases, specify here whether
# this is a slave or master
#slave = true
#source = master.example.com
# Slave only: specify a single database to replicate
#only = master.example.com
# or
master = true
#source = slave.example.com

# in replica set configuration, specify the name of the replica set
replSet = rs0

When I check the status of the replica set from within Mongo it seems to be setup properly:

rs0:PRIMARY> rs.status()
{
    "set" : "rs0",
    "date" : ISODate("2014-11-14T08:18:06Z"),
    "myState" : 1,
    "members" : [
        {
            "_id" : 0,
            "name" : "Server:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 1980,
            "optime" : Timestamp(1415952019, 4),
            "optimeDate" : ISODate("2014-11-14T08:00:19Z"),
            "self" : true
        }
    ],
    "ok" : 1
}

However when I try to run a test I get the following error:

PHP Fatal error:  Uncaught exception 'MongoConnectionException' with message 'MongoClient::__construct(): php_network_getaddresses: getaddrinfo failed: Name or service not known' in /var/www/html/Site/m.php:10
Stack trace:
#0 /var/www/html/Site/m.php(10): MongoClient->__construct('mongodb://MONGO...', Array)
#1 {main}

Next exception 'MongoConnectionException' with message 'No candidate servers found' in /var/www/html/Site/m.php:10
Stack trace:
#0 /var/www/html/Site/m.php(0): MongoClient->__construct()
#1 {main}
  thrown in /var/www/html/Site/m.php on line 10

The connection code is a simple test:

$uri = "mongodb://MONGOHOST:27017/provider";

/*
 * We recommend explictly configuring a connection timeout (see tips & tricks
 * below). Specify the replica set name to avoid connection errors.
 */
$options = array("connectTimeoutMS" => 30000, "replicaSet" => "rs0");
$client = new MongoClient($uri, $options);
echo print_r($client)."\n";

However, I can connect if I omit the $options array with the following:

The connection code is a simple test:

$uri = "mongodb://MONGOHOST:27017/provider";

/*
 * We recommend explictly configuring a connection timeout (see tips & tricks
 * below). Specify the replica set name to avoid connection errors.
 */
$client = new MongoClient($uri);
echo print_r($client)."\n";

Can anyone point out why I can't connect directly to the replica set?

It turns out that the host wasn't properly added to /etc/hosts. Adding it seemed to solve the problem.


MS SQL Server create user who can access only those databases which they have created How to create and give permission to a user in such a way that they will have full access only to the database(s) they have created not other database(s). I am using SQL Server 2016 Express.

Welcome to the world of SQL Server Security!

Probably the best place to start is by reading the Microsoft Securing SQL Server Guide. This covers all the basics from securing the physical SQL server to database and user permissions.

An alternative view is the SQL Server Central Stairway to SQL Server Security


What to look for in bad pg_dump log We want to programmatically detect errors in cron scheduled pg_dumps.

Apart from cheking whether or not the log file ends in "pg_dump: saving database definition":

What other tell-tale string can I grep for in order to programatically check a dump is ok.

The main indicator is the exit status of the pg_dump command. If it's non-zero, then something went wrong otherwise it worked all along. This is the implicit contract between any command and the shell, and breaching it would be a bug.
And it's also testable when the sub-command could't be launched at all.

Here's a basic skeleton in Unix shell that tests the exit code and acts accordingly:

d=`date +%Y%m%d`
dump=/path/to/backups/daily-backup-"$d"
error=/path/to/backups/errors-backup-"$d".txt

pg_dump -U username dbname >$dump 2>$error
code=$?
if [ $code -ne 0 ]; then
  echo 1>&2 "The backup failed (exit code $code), check for errors in $error"
fi

It is necessary to keep the error messages in durable storage so that they can be analyzed later.
When no error at all occurs, the file for errors should be there but empty, unless the -v option is used but it's intentionally not in the above script.

About the message: saving database definition

It is displayed along with lots of other informational messages only when using the verbose (-v) option. The problem is that in case of error, these messages will be mixed with error messages, and it requires some level of expertise to distinguish what's normal from what's not.


How Many Rows om Int and Big Int How many rows does datatype int and big int retrieve in their table when you work with fact table in SQL server?

Not sure I understand specifically what your question is or what exactly you're trying to do, but take a look at this BOL reference on int, bigint, smallint, and tinyint.

int

Size: 4 bytes
Min: -2,147,483,648
Max: 2,147,483,647

bigint

Size: 8 bytes
Min: -9,223,372,036,854,775,808
Max: 9,223,372,036,854,775,807

If you're asking about what are the upper limits of a sequential number (for instance, an IDENTITY column) then the max rows will be the Max values shown above, provided an initial value of 1.


Point in time restore fails - backup file from before the restore point not available (why does this matter) I have tried to do a point-in-time restore of a database through the management studio gui, the point-in-time being yesterday morning (2016-04-12).

This fails with the error

  "...cannot open backup device [name of backup from 2016-02-19]..."

so it cannot access the backup from two months ago - I know there's a very good reason for this, as that backup is no longer present (we don't keep backups that long).

My question is, why is SQL Server trying to access a backup file from two months ago for a restore to a point in time only yesterday? Is there something I'm doing wrong here?

I've checked all my settings in the restore wizard gui (to make sure I hadn't got the date wrong). All the required (Or the only ones I think should be required) backup files are correct and accessible. I am going to just restore the 2016-04-11 nightly backup manually anyway so the urgency for this is off, but it's still interesting/annoying.

I can't see anything in BOL that explains why SQL server is trying to access an old backup for this.

Setup info:

SQL Server 2012 (SP3)
FULL Recovery model
Nightly full backups
T-log backups every 10 minutes

Restore DB screenshot:

for a restore to a point in time, you just need the latest full-backup and all the transaction logs thereafter.

this script will show you when was the last backup taken and where it is located.

declare @backup_type CHAR(1) = 'D' --'D' full, 'L' log

                ;with Radhe as (
                    SELECT  @@Servername as [Server_Name],
                    B.name as Database_Name,
                    ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
                    ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
                    ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
                    ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
                    ,media_set_id = MAX(A.media_set_id)
                    ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,A.type
                    FROM sys.databases B

                    LEFT OUTER JOIN msdb.dbo.backupset A
                                 ON A.database_name = B.name
                                AND (@backup_type IS NULL OR A.type = @backup_type  )

                    GROUP BY B.Name, A.type

                )

                 SELECT r.[Server_Name]
                       ,r.Database_Name
                       ,[Backup Type] = r.type
                       ,r.DaysSinceLastBackup
                       ,r.LastBackupDate
                       ,r.BackupSize_GB
                       ,r.BackupSize_MB
                       ,F.physical_device_name
                       ,r.[AVG Backup Duration]
                       ,r.[Longest Backup Duration]

                   FROM Radhe r

                    LEFT OUTER JOIN msdb.dbo.backupmediafamily F
                                 ON R.media_set_id = F.media_set_id

                    ORDER BY r.Server_Name, r.Database_Name

for the transaction logs, use the same script above with @backup_type CHAR(1) = 'L'

see what you have inside your backup file doing a restore filelistonly

RESTORE FILELISTONLY FROM DISK=N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_021056_1290970.bak'
--- CHECK MDF ,NDF AND log files with size

  check the disk space available

---------------------------------------
--BEFORE RESTORING - ALWAYS CHECK THE AVAILABLE DISK SPACE
--> 4 - Free space by drive.
EXEC master..xp_fixeddrives
---------------------------------------

  replication

---------------------------------------
--> 5 - see if the database is involved in replication
---------------------------------------
SELECT
    name as [Database name],
    CASE is_published
        WHEN 0 THEN 'No'
        ELSE 'Yes'
        END AS [Is Published],
    CASE is_merge_published
        WHEN 0 THEN 'No'
        ELSE 'Yes'
        END AS [Is Merge Published],
    CASE is_distributor
        WHEN 0 THEN 'No'
        ELSE 'Yes'
        END AS [Is Distributor],
    CASE is_subscribed
        WHEN 0 THEN 'No'
        ELSE 'Yes'
        END AS [Is Subscribed]
FROM sys.databases
WHERE database_id > 4

this is an example of a point in time restore.
the database is called IntEng2 and it already existed, so it needs to be overwritten.

it will be restored to this point in time: 2014-08-22T11:30:00

--================================================
-- RESTORE THE DATABASE TO A POINT IN TIME - the database IntEng2 is NOT replicated
--================================================

-- put database in single user mode - only me - the man doing the job ;)
USE [master]
ALTER DATABASE [IntEng2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- backup the tail of the log
BACKUP LOG [IntEng2] TO  DISK = N'H:\SQLBackups\IntEng2_LogBackup_2014-08-22_14-48-05.bak' WITH NOFORMAT, NOINIT,  NAME = N'IntEng2_LogBackup_2014-08-22_14-48-05', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5

-- restore the full backup - with norecovery
-- with replace on the log file because for some reason this database has 2 log files and we want to keep it like that
RESTORE DATABASE [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_021056_1290970.bak'
WITH  FILE = 1,  MOVE N'IntEng2_Log2' TO N'G:\SQLLog\IntEng2_Log2.ldf',
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

-- resotore the log files - IN THE SPECIFIC ORDER ---
-- with NORECOVERY

RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_030002_2577183.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_031501_5336409.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_033001_6524314.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_034501_7888108.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_040002_3541104.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_041506_9327770.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_043001_9532881.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_044502_0339177.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_050002_0942167.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_051506_3762732.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_053005_8234237.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_054505_2202118.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_060002_3445813.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_061502_1611989.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_063002_2204240.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_064501_8698190.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_070002_1582584.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_071501_8781090.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_073002_2857856.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_074502_4935206.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_080001_8744757.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_081501_8779343.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_083001_7488322.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_084501_5171402.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_090002_4606122.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_091501_9724206.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_093002_3221461.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_094502_0465413.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_100001_7103846.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_101501_8737945.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_103002_2375779.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_104501_8634658.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_110002_4519941.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_111501_5088114.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

-- restore the log closest to the time when the disaster happened
-- this will be the restore with STOP AT
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_113001_5819154.trn'
WITH  FILE = 1,  NOUNLOAD,  STATS = 5,
STOPAT = N'2014-08-22T11:30:00'

--================================================
-- BRING THE DATABASE BACK TO LIFE
--================================================
-- have a look at the database,
-- if everything is ok
-- bring the database back to multiuser
ALTER DATABASE [IntEng2] SET MULTI_USER

GO

there were other things I did not include here:

1) before a restore - make sure all the processes that use that database are stopped.

2) before a restore - script all the permissions, so that you can re-apply them later on.


Can I create a view to edit ID mapping table without seeing the IDs? I have 2 tables, A and B with an int primary key and a string name.

I have a 3rd table, C, with 2 columns that map the IDs of A and B together. I want to create a view to edit the mappings in table C, but I want the view to show the Name value rather than the IDs. The view would be edited via SQL Server Management Studio.

For example, the view should allow the user to type in "Item4" in the item column, and "Accessory1" in the accessory column, and table C gets updated with a new row mapping "4" to "1".

I am wondering if I can make this an editable view so I don't have to go to the trouble of writing a whole application to manage this.

If I go to the view in Microsoft SQL Server Management Studio, and right click, I can edit the top 200 rows. I know the constraint on this feature is that I can only edit if the change is to a single table. I would only be editing a single table, so there might be a way to get this to work. Is there a way? (My users are people (traders) who are familiar with SQL Server and prefer to have direct access to their data).

Base tables

CREATE TABLE dbo.Item
(
    ItemID integer PRIMARY KEY,
    ItemName varchar(50) NOT NULL UNIQUE
);

CREATE TABLE dbo.Accessory
(
    AccessoryID integer PRIMARY KEY,
    AccessoryName varchar(50) NOT NULL UNIQUE
);

CREATE TABLE dbo.ItemAccessory
(
    ItemID integer REFERENCES dbo.Item (ItemID),
    AccessoryID integer REFERENCES dbo.Accessory (AccessoryID),

    PRIMARY KEY (ItemID, AccessoryID)
);

Sample data

INSERT dbo.Item
    (ItemID, ItemName)
VALUES
    (1, 'Item 1'),
    (2, 'Item 2'),
    (3, 'Item 3'),
    (4, 'Item 4');

INSERT dbo.Accessory
    (AccessoryID, AccessoryName)
VALUES
    (1, 'Accessory 1'),
    (2, 'Accessory 2'),
    (3, 'Accessory 3'),
    (4, 'Accessory 4');

INSERT dbo.ItemAccessory
    (ItemID, AccessoryID)
VALUES
    (1, 1),
    (1, 2),
    (2, 3),
    (3, 4);

View

CREATE VIEW dbo.ItemAccessoryName
WITH SCHEMABINDING
AS
SELECT
    I.ItemName,
    A.AccessoryName
FROM dbo.ItemAccessory AS IA
JOIN dbo.Accessory AS A
    ON A.AccessoryID = IA.AccessoryID
JOIN dbo.Item AS I
    ON I.ItemID = IA.ItemID;

Problem

The view is not directly updatable:

INSERT dbo.ItemAccessoryName
    (ItemName, AccessoryName)
VALUES
    ('Item 4', 'Accessory 4');

  Msg 4405, Level 16, State 1, Line 61
  View or function 'dbo.ItemAccessoryName' is not updatable because the modification affects multiple base tables.

Solution

We can make any view updatable using an instead of trigger:

CREATE TRIGGER dbo_ItemAccessoryName_IOI
ON dbo.ItemAccessoryName
INSTEAD OF INSERT
AS
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    INSERT dbo.ItemAccessory
    (
        ItemID,
        AccessoryID
    )
    SELECT
        I.ItemID,
        A.AccessoryID
    FROM Inserted AS INS
    JOIN dbo.Item AS I
        ON I.ItemName = INS.ItemName
    JOIN dbo.Accessory AS A
        ON A.AccessoryName = INS.AccessoryName;

The trigger essentially converts the supplied names to IDs before the insert is attempted. As long as you have good referential integrity, the need for additional checks in the trigger code should be minimal. The details depend on exactly what you want to happen if someone pastes multiple entries, only some of which are valid, for example.

Inserts, updates, and deletes

You would need additional trigger logic if you want to allow (general) updates and deletes through the view. For example:

DROP TRIGGER dbo.dbo_ItemAccessoryName_IOI;
GO
CREATE TRIGGER dbo_ItemAccessoryName_IOIUD
ON dbo.ItemAccessoryName
INSTEAD OF INSERT, UPDATE, DELETE
AS
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    -- Handle deletions (including the delete part of an update)
    DELETE IA
    FROM dbo.ItemAccessory AS IA
    JOIN dbo.Item AS I
        ON I.ItemID = IA.ItemID
    JOIN dbo.Accessory AS A
        ON A.AccessoryID = IA.AccessoryID
    JOIN Deleted AS DEL
        ON DEL.ItemName = I.ItemName
        AND DEL.AccessoryName = A.AccessoryName;

    -- Handle inserts (including the insert part of an update)
    INSERT dbo.ItemAccessory
    (
        ItemID,
        AccessoryID
    )
    SELECT
        I.ItemID,
        A.AccessoryID
    FROM Inserted AS INS
    JOIN dbo.Item AS I
        ON I.ItemName = INS.ItemName
    JOIN dbo.Accessory AS A
        ON A.AccessoryName = INS.AccessoryName;


INSERT rows in two tables preserving connection to a third table As part of a recent database development project, I'm stuck with a query for which I seem to find no elegant solution (i.e. without looping).

Relevant bits of the schema:

CREATE TABLE sites (
  site_id SERIAL PRIMARY KEY,
  ...
);

CREATE TABLE properties (
  property_id SERIAL PRIMARY KEY,
  property_def_id INT NOT NULL,
  FOREIGN KEY (property_def_id) REFERENCES property_def(property_def_id)
);

CREATE TABLE site_properties (
  property_id INT NOT NULL,
  site_id INT NOT NULL,
  PRIMARY KEY (property_id, site_id),
  FOREIGN KEY (property_id) REFERENCES properties(property_id),
  FOREIGN KEY (site_id) REFERENCES sites(site_id)
);

The task:
Insert one new, distinct property in table properties for each row in table sites and also insert a row in site_properties to store  the relation between both.

Desired behaviour:

for each site_id (as my_site_id) in sites:
* INSERT INTO properties (property_def_id) VALUES (1) RETURNING property_id INTO my_prop_id
* INSERT INTO site_properties (property_id, site_id) VALUES (my_prop_id, my_site_id)

At the end, I would have in the site_properties table each "new" property having being assigned only to one site (i.e. no cross-joins)

I feel it should be possible to do this in one query, but I couldn't figure out how for the love of me. I have tried CTEs but I ended up being stuck with a list of property_id's, a list of sites and no way to join between the two.

What am I missing?

It took me an awfully long while to figure it out, but here it goes:

Setup:

CREATE TABLE sites
(
  site_id INT PRIMARY KEY
);

CREATE TABLE properties
(
  property_id SERIAL PRIMARY KEY,
  property_def_id INT NOT NULL
);

CREATE TABLE site_properties
(
  property_id INT NOT NULL,
  site_id INT NOT NULL,
  PRIMARY KEY (property_id, site_id),
  FOREIGN KEY (property_id) REFERENCES properties(property_id),
  FOREIGN KEY (site_id) REFERENCES sites(site_id)
);

INSERT INTO sites VALUES (101),(102),(103);

INSERT INTO properties(property_def_id) VALUES (3), (4);
INSERT INTO site_properties(property_id, site_id) VALUES (1, 101), (2, 101);

Actual query:

WITH props AS (
  INSERT INTO properties (property_def_id)
    SELECT 1 FROM sites
  RETURNING property_id
), new_props AS (
  SELECT row_number() OVER () AS row, property_id
    FROM props
), all_sites AS (
  SELECT row_number() OVER () AS row, site_id
    FROM sites
)
INSERT INTO site_properties (site_id, property_id)
SELECT s.site_id, np.property_id
FROM all_sites s
  JOIN new_props np USING (row)
;

Checking everything works as expected:

SELECT *
FROM site_properties
  JOIN properties USING (property_id);

property_id | site_id | property_def_id
---------------------------------------
     1          101            3
     2          101            4
     3          101            1
     4          102            1
     5          103            1


How do I go from a PostgreSQL native point type to a PostGIS point? If I have a point

SELECT point(long,lat);

How do I go from that to a PostGIS point?

There is no implicit conversion or casting from a PostgreSQL native point type to a PostGIS geometry, and a PostGIS POINT has a Spatial Reference System attached to it. ST_MakePoint will create the point geometry, but it'll be apart from any system that maps it to Earth,

SELECT ST_MakePoint(p[0],p[1])
FROM ( VALUES (point(-71.1043443253471,42.3150676015829)) ) AS t(p);

We're accessing the point type with the array syntax,

  It is possible to access the two component numbers of a point as though the point were an array with indexes 0 and 1. For example, if t.p is a point column then SELECT p[0] FROM t retrieves the X coordinate and UPDATE t SET p[1] = ... changes the Y coordinate. In the same way, a value of type box or lseg can be treated as an array of two point values.


Disk page reads for Index Nested Loop Join I apologise if this is the wrong place to post this question.

In my Advanced Databases course I'm taking, we were taught that the number of disk page reads done when doing an index nested loop join when you have enough buffer space for 3 pages is:

Disk page reads for R INLJ S = |Pages(R)| + |R|.Depth(Index on S) + |R Join S|

I understand that we need to read in all the pages of R as the outer loop and then we have to traverse the depth of the B+ tree to get the leaf pages (and do this |R| times) but why do we need to add the number of times R joins with S?

I never took a class like that but I believe the idea is that it takes additional reads to fetch the data pages when you find matching rows through the index. Let's run a quick test in SQL Server to see if we get numbers close to your formula.

First I'll create tables and insert sample data into them. I want to test three different queries: one with 0 rows returned, one with 5000 rows returned, and one with 10000 rows returned.

-- create tables
CREATE TABLE X_OUTER_TABLE (NUM INT);
CREATE TABLE X_INNER_TABLE_0_MATCHES (NUM INT, FILLER VARCHAR(100));
CREATE INDEX IX_X_INNER_TABLE_0_MATCHES ON X_INNER_TABLE_0_MATCHES (NUM);

CREATE TABLE X_INNER_TABLE_5000_MATCHES (NUM INT, FILLER VARCHAR(100));
CREATE INDEX IX_X_INNER_TABLE_5000_MATCHES ON X_INNER_TABLE_5000_MATCHES (NUM);

CREATE TABLE X_INNER_TABLE_10000_MATCHES (NUM INT, FILLER VARCHAR(100));
CREATE INDEX IX_X_INNER_TABLE_10000_MATCHES ON X_INNER_TABLE_10000_MATCHES (NUM);

-- populate data
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT INTO X_OUTER_TABLE WITH (TABLOCK)
select N from cteTally where N BETWEEN 1 AND 10000;

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT INTO X_INNER_TABLE_0_MATCHES WITH (TABLOCK)
select N, REPLICATE('Z', 100) from cteTally where N BETWEEN 10001 AND 60000;

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT INTO X_INNER_TABLE_5000_MATCHES WITH (TABLOCK)
select N, REPLICATE('Z', 100) from cteTally where N BETWEEN 5001 AND 65000;

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT INTO X_INNER_TABLE_10000_MATCHES WITH (TABLOCK)
select N, REPLICATE('Z', 100) from cteTally where N BETWEEN 1 AND 50000;

I need to get some information about the index depth and the number of pages in the tables as well as do some additional setup to get cleaner numbers:

-- index depth of 2
SELECT INDEXPROPERTY ( object_ID('X_INNER_TABLE_10000_MATCHES'), 'IX_X_INNER_TABLE_10000_MATCHES' , 'IndexDepth');

 -- 19 used pages, 26 reserved
SELECT OBJECT_NAME(s.object_id) table_name,
        SUM(s.used_page_count) used_pages,
        SUM(s.reserved_page_count) reserved_pages
FROM    sys.dm_db_partition_stats s
WHERE OBJECT_NAME(s.object_id) IN ('X_OUTER_TABLE')
GROUP BY s.object_id;

-- get logical reads after query execution
SET STATISTICS IO ON;

-- Trace flag 8744: Disable pre-fetching for ranges
DBCC TRACEON(8744);

Based on the formula I expect the following results:

Join X_OUTER_TABLE to X_INNER_TABLE_0_MATCHES: 19 + 2 * 10000 + 0 = 20019

Join X_OUTER_TABLE to X_INNER_TABLE_5000_MATCHES: 19 + 2 * 10000 + 5000 = 25019

Join X_OUTER_TABLE to X_INNER_TABLE_10000_MATCHES: 19 + 2 * 10000 + 10000 = 30019

Run the SELECT queries:

SELECT INNER_TABLE.FILLER
FROM X_OUTER_TABLE OUTER_TABLE
INNER LOOP JOIN X_INNER_TABLE_0_MATCHES INNER_TABLE ON OUTER_TABLE.NUM = INNER_TABLE.NUM;

/*
(0 row(s) affected)
Table 'X_INNER_TABLE_0_MATCHES'. Scan count 10000, logical reads 20000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'X_OUTER_TABLE'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/

SELECT INNER_TABLE.FILLER
FROM X_OUTER_TABLE OUTER_TABLE
INNER LOOP JOIN X_INNER_TABLE_5000_MATCHES INNER_TABLE ON OUTER_TABLE.NUM = INNER_TABLE.NUM;

/*
(5000 row(s) affected)
Table 'X_INNER_TABLE_5000_MATCHES'. Scan count 10000, logical reads 25022, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'X_OUTER_TABLE'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/

SELECT INNER_TABLE.FILLER
FROM X_OUTER_TABLE OUTER_TABLE
INNER LOOP JOIN X_INNER_TABLE_10000_MATCHES INNER_TABLE ON OUTER_TABLE.NUM = INNER_TABLE.NUM;

/*
(10000 row(s) affected)
Table 'X_INNER_TABLE_10000_MATCHES'. Scan count 10000, logical reads 30044, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'X_OUTER_TABLE'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/

Actual results:

Join X_OUTER_TABLE to X_INNER_TABLE_0_MATCHES: 20017 (predicted 20019)

Join X_OUTER_TABLE to X_INNER_TABLE_5000_MATCHES: 25039 (predicted 25019)

Join X_OUTER_TABLE to X_INNER_TABLE_10000_MATCHES: 30061 (predicted  30019)

I think that's pretty close!

Clean up script for completeness:

DBCC TRACEOFF(8744);

DROP TABLE X_OUTER_TABLE;
DROP TABLE X_INNER_TABLE_0_MATCHES;
DROP TABLE X_INNER_TABLE_5000_MATCHES;
DROP TABLE X_INNER_TABLE_10000_MATCHES;


Intermittent Login Issues with Windows Authentication I have a strange one. I've been experiencing occasional login errors via windows authentication for the past couple weeks and the network team and I have yet to find a solution.

I am running SQL 2016 on a physical server running Windows Server 2012 R2.

While this has been happening, I've been telling users to wait five minutes and try to login again. If the user waits five minutes, they will (most times) be able to login. I've had instances where I couldn't log in for 15 minutes, then my next login attempt works.

From the SQL error log, I see entries like this:

  Login failed. The login is from an untrusted domain and cannot be used
  with Windows authentication. [CLIENT: XX.XX.XXX.XXX]   Error: 18452,
  Severity: 14, State: 1.

  SSPI handshake failed with error code 0x80090311, state 14 while
  establishing a connection with integrated security; the connection has
  been closed. Reason: AcceptSecurityContext failed. The Windows error
  code indicates the cause of failure. No authority could be contacted
  for authentication.   [CLIENT: XX.XX.XXX.XXX] Error: 17806, Severity:
  20, State: 14.

From the server, I see this:

  An account failed to log on.

  Subject:  Security ID:        NULL SID    Account Name:       -   Account Domain:     -
    Logon ID:       0x0

  Logon Type:           3

  Account For Which Logon Failed:   Security ID:        NULL SID    Account
  Name:     A User  Account Domain:     A Domain

  Failure Information:  Failure Reason:     An Error occured during Logon.
    Status:         0xC000005E  Sub Status:     0x0

  Process Information:  Caller Process ID:  0x0     Caller Process Name:    -

  Network Information:  Workstation Name:   A Computer  Source Network
  Address:  -   Source Port:        -

  Detailed Authentication Information:  Logon Process:      NtLmSsp
    Authentication Package: NTLM    Transited Services: -   Package Name
  (NTLM only):  -   Key Length:     0

Any ideas what could be causing (sometimes) login failures like this?

SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. No authority could be contacted for authentication.

If you cut up the HRESULT listed, as the error indicates, it maps to:
SEC_E_NO_AUTHENTICATING_AUTHORITY

FAILURE (1), FACILITY_SSPI (0x9), Code 0x311

  Failure Information: Failure Reason: An Error occured during Logon. Status: 0xC000005E Sub Status: 0x0

If you cut up the HRESULT listed in this one, it maps to: STATUS_NO_LOGON_SERVERS

Both of these error are fairly indicative that the domain controllers and infrastructure around them should be investigated. Additionally if it's a single server having the issue, look at recent patches or group policy items that have been pushed, recently, especially due to OU changes or membership changes. If others on the same network are having issues, network hardware or improper domain controller resources might be an issue.


Query - Match Name to Multiple Unique Types I'm trying to create an Access 2013 database query to display all insurance policy holders that have more than one type of insurance. I am not interested in policy holders that have only one type of insurance. If a policy holder has more than one type of insurance I need a list showing the policy holder “NAME” and each unique insurance “TYPE”.

The database table is named “INSURANCE” and contains a large number of record fields but I need to isolate only two record fields. Here is a sample of 15 records:

So, in the above example, I am not interested in “Jones” or “Miller” since they have only a single type of insurance. Since “Smith’, “Brown”, "Davis" and "Wilson" have multiple types of insurance I need a list showing their names and each unique type of insurance. For the above example, the query would produce the following result:

Thank you for your help.

-- this should work for a starter
select distinct MyTable.Name, MyTable.Type
from MyTable
where Name in (
        select t1.Name
        from (
            select t0.Name, t0.Type
            from (
                select distinct MyTable.Name, MyTable.Type
                from MyTable
                ) as t0
            group by t0.Name, t0.Type
            ) as t1
        group by t1.Name
        having count(*) > 1
        );


Chunks not splitting on MongoDB sharded clusters I have created 2 shard servers named rs1 and rs2.
Now in the database collection there are multiple records those should split on these 2 servers in chunks. The sharding key is TxnMonth. It is not unique.

Now I have given the chunk size as 1 MB. When I inserted records corresponding to "TxnMonth" : 1, "TxnMonth" : 2 and "TxnMonth" : 3, the data was splitted onto 2 shards where on rs1 there were 2 chunks and on rs2 there was 1 chunk.

On checking, data for TxnMonth : 1 and 2 was on rs1 and data for TxnMonth : 3 was on rs2.

On running, sh.status(), it gave this output-

{  "_id" : "shardPOC",  "primary" : "rs1",  "partitioned" : true }
                shardPOC.machines
                        shard key: { "TxnMonth" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                rs1     2
                                rs2     1
                        { "TxnMonth" : { "$minKey" : 1 } } -->> { "TxnMonth" : 1 } on : rs1 Timestamp(2, 1)
                        { "TxnMonth" : 1 } -->> { "TxnMonth" : 3 } on : rs1 Timestamp(1, 2)
                        { "TxnMonth" : 3 } -->> { "TxnMonth" : { "$maxKey" : 1 } } on : rs2 Timestamp(2, 0)
        {  "_id" : "NewTestDB",  "primary" : "rs1",  "partitioned" : false }

After this, I started inserting records for TxnMonth : 4, 5, 6, 7 and 8, to check when the chunk on rs2 will split but even if the chunk size exceeded 1 MB size, it didn't split. Below is the output of db.collection.getShardDistribution() -

Shard rs1 at rs1/JCB-DB1:27012
   data : 57KiB docs : 80 chunks : 2
   estimated data per chunk : 28KiB
   estimated docs per chunk : 40
Shard rs2 at rs2/JCB-DB1:27013
   data : 1.1MiB docs : 1560 chunks : 1
   estimated data per chunk : 1.1MiB
   estimated docs per chunk : 1560
Totals
   data : 1.15MiB docs : 1640 chunks : 3
   Shard rs1 contains 4.87% data, 4.87% docs in cluster, avg obj size on shard : 740B
   Shard rs2 contains 95.12% data, 95.12% docs in cluster, avg obj size on shard : 740B

What I expected was that the chunk will split after it exceeds 1 MB but it did not.
Can someone explain the reason?
Thanks in advance.

Your chosen shard key for a collection defines the granularity of possible chunk splits. Chunks represent a range of values for the shard key. With a single field in your shard key (TxnMonth), that means the smallest possible range is a single value such as TxnMonth: 3.

The chunk size setting determines when MongoDB will try to split chunk ranges in order to help distribute data in a sharded cluster, but does not put an upper bound on the size of the data in a chunk range.

When the maximum chunk size is reached for a given value, that chunk will be marked as an indivisible  jumbo chunk and the balancer will no longer attempt to split or migrate that chunk. New data will continue to be added to that chunk leading to an imbalance in data distribution as you observed in your testing.

  there are 8 distinct values of TxnMonth from 1 to 8

This limits your sharded collection to a maximum of 8 chunks (or the number of unique TxnMonth values in this collection).

For effective sharding you need to choose a more appropriate shard key with higher cardinality.


Mysql error 1064, granting privileges I'm trying to add privileges for a newly created user in my database (CLI). But i am getting an error, probably because i have a dash in my db name but how do you work around it?

Error:

    You have an error in your SQL syntax; check the manual that corresponds to
    your MySQL server version for the right syntax to use
    near '-acc.* TO 'bivv-acc'@'localhost'' at line 1

The actual command:

GRANT ALL PRIVILEGES ON bivv-acc.* TO 'bivv-acc'@'localhost';

Note: I am in my database, not in the overview of all the databases. Any clues?

Cheers,

Try to escape dash using the backticks like

GRANT ALL PRIVILEGES ON `bivv-acc`.* TO 'bivv-acc'@'localhost';

Check the manual which says:

  If an identifier contains special characters or is a reserved word,
  you must quote it whenever you refer to it.

  ...

  The identifier quote character is the backtick ("`")


How to retrieve disk spaces by removing tables from tablespaces in Oracle 10g? I'm using a Oracle 10g database.
I have a big tablespace MYTBS made of a dozen data files (MYTBS_01, MYTBS_02, ...) of different sizes which have been added through the years.

Now i have removed very big tables from this tablespace and i would like to retrieve disk space on the server.

How can i be able to free up space/blocks from the tablespace ?

You need to shrink the data files, which can be tricky.

However, possibly the easier method to describe and implement is to create a new tablespace of approximately the right size, use ALTER TABLE to move the table segments, and ALTER INDEX to rebuild the indexes in the new tablespace. When the old tablespace is empty of objects you can drop it.

Oracle Enterprise Manager can do this automatically for you. This is described in detail in this post: http://www.oracle-base.com/articles/misc/reclaiming-unused-space.php#em_reorg


Why is casting a DATE field to VARCHAR datatype non-deterministic and is there a way to make it deterministic? I'm trying to create an indexed view where the index is on a field in the view which is a hash of a bunch of the columns of the underlying table in the view.

Here's an example:

CREATE VIEW CoolHashedView WITH SCHEMABINDING AS

    SELECT
        KeyId,
        CONVERT
        (
            VARCHAR(34),
            HASHBYTES('MD5', TextColumn1 + '||' + TextColumn2 + '||' + CAST(DateColumn1 AS VARCHAR(50)),
            2
        ) AS HashedData
    FROM dbo.BoringTable;

    CREATE UNIQUE CLUSTERED INDEX IX_CoolHashedView_KeyId_HashedData ON CoolHashedView (KeyId, HashedData);

When I try to create the above index, it fails and the following error is thrown:

  Msg 2729, Level 16, State 1, Line 26 Column 'HashedData' in view
  'CoolHashedView' cannot be used in an index or
  statistics or as a partition key because it is non-deterministic.

When I remove the date field from the HASHBYTEs function, the clustered index then successfully creates.

My guess is it has something to do with the different ways to format a date or different timezones?...am I getting warmer?

CAST is not deterministic because date format may change based on the server settings (i.e. under compatibility before 110 default date format is 0 => "Dec 12 2019  2:11PM" and output dependeds on language).
To make it deterministic use CONVERT and

  the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113. (from documentation below)

More documentation at: Deterministic and Nondeterministic Functions


REDIS Bulk RPUSH set values to a list Is there an optimized way to RPUSH all the values of a set to a list without SPOP-ing them one by one (or SMEMBERS)? (I need to perform bulk RPUSH of huge set each T time to a list) ?

Thank you,

Actually there is but not where you'd intuitively think of looking for it. Have a look at SORT - this should do what you need:

SORT myset BY nosort STORE mylist


How to convert a variable to be used in where clause I have stored Job_id value of msdb..sysjob in a variable @job_id_char, which i want to use against msdb..sysjobactivity to get the 'start_execution_date' value.
I have used following query but getting error as conversion failed

select start_execution_date from msdb.dbo.sysjobactivity where job_id = @job_id_char

I know that i have to convert the variable, but i don't know how

Since the data type of the job_id in the msdb.dbo.sysjob table is uniqueidentifier.

So you need to create the @job_id_char as a uniqueidentifier variable, like
DECLARE @job_id_char uniqueidentifier
then only it can be used in the WHERE clause of msdb.dbo.sysjobactivity table.

Or you can JOIN the tables by the the job_id like

SELECT sja.start_execution_date, .....
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id
WHERE ..... -- condition related sysjobs tables


Drag and Drop table object explorer into query window issue Sql Server Management Studio 18.1 I recently installed SSMS 18.1 and I am having an issue when trying to drag and drop table names from the object explorer to the query window. For example, I am writing a query and dont want type out the table name I would drag and drop the table into the query editor and the name will be there.

This is the error I get: "An exception has been encountered. This may be caused by an extension".

Any help would be appreciated.

Follow this feedback item for updates on the issue:
SSMS Crash when dragging table from Object explorer into new query window

Microsoft SQL Server (Product Manager, Microsoft Azure) responded  ·  Jun 14, 2019
Issue has been identified and a fix is being made. It will be rolled out with the next update of SSMS 18.

In the meantime, you could revert to SSMS 17.9.


How to change temporary directory for pg_dump? I'm running out of space from my hard drive which has a 18GB PostgreSQL database.
When I try pg_dump to a different drive, PostgreSQL creates temporary files on the disk it's dumping from, so I run out of space and the dump fails.

I tried this from Stackoverflow and a small file is created in the new directory, but nothing else, and pg_dump still writes to the original disk.

How do I change temp directory for pg_dump?

Note: My work_mem setting is pretty high already, I can't change that.
My db version is 9.0.13.

The temporary files are a side effect of the --format=tar option.

A backup in default plain format goes through without temporary tables in the local drive. Omit the option to produce a backup in plain format without temporary local files.


How to create a SELECT statement involving a subtype Okay, so all I'm trying to do here is "list" the managers who supervised the employee that charged a customer $100 for a rental on November 1st, 1998.

My issue is that I don't know how to select the managers' name because they're listed under MANAGER, which is a subtype of the EMPLOYEE supertype. I've posted my EER diagram below:

How you do the select depends on your table design.  Your EER depicts the entities, relationships and attributes nicely, but doesn't say anything about table design.  (That's as it should be, IMO).  A relational schematic would give those details.

If you are interested in how to design tables for a subtype (or subclass) situation here are two alternative techniques for you to explore:

http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html
http://martinfowler.com/eaaCatalog/classTableInheritance.html

And there is a technique often used when tables have a one to one relationship to each other.  You can see it here:

https://stackoverflow.com/questions/tagged/shared-primary-key


Fetch correct stats from database I am trying to get count of all quiz users who have made purchases. Main table for storing users is user and quiz_client is linked with user table. sales_order table holds data of users who have placed orders/made purchases.

I'm running the below query but it's taking forever, is there something wrong with the query?

SELECT
    COUNT(user.id)
FROM
    cx_user
        JOIN
    quiz_client ON quiz_client.user_id = user.id
        JOIN
    sales_order ON user.email = sales_order.customer_email

Change to

COUNT(DISTINCT user.id)

Else you are counting "purchases", not "users".


MySQL search function join on multiple conditions I have a database with two tables studies and elements. I've made a website where users can perform search queries to check if a particular study has used particular elements. If it does, I want the code to return those studies. For example, if the users typed in "Barney Likert" in the search query, it should return 1 row because "Barney" can be found in studies and "Likert" can be found in elements. However, if I type it in it returns zero rows. If I type in "Test", it does return rows because "test" can be found in both tables. What am I doing wrong? The key part of the code is this one:

$sql = "SELECT * FROM studies join elements
                   on studies.S_Element_ID = elements.E_Element_ID
              WHERE ((studies.Author LIKE '%$search%'
                   OR studies.Study_Name LIKE '%$search%'
                   OR studies.Abstract LIKE '%$search%')
                AND (elements.Element_Name LIKE '%$search%'
                  OR elements.Element_Author LIKE '%$search%'
                  OR elements.Element_Source LIKE '%$search%'
                  OR elements.Element_Manipulation LIKE '%$search%'
                  OR elements.Element_Scale LIKE '%$search%'))";

As you can see, I have the values Element_ID in both, these need to match. Moreover, the keyword search needs to be found in both tables. Why does the query not return the right result?

At the moment you're searching for e.g. "Barney Likert" (the whole string) in both the studies and the elements columns you've specified.

You need to either have two search strings, one for searching studies, and one for searching elements. Or if your users can't be bothered to use two separate fields, then you could parse the string before it's sent to the database, split it up if it consists of two words, and execute a particular query depending on what the search string is. E.g. you could have one query for input of one word, and another query for input of two words.

You may also want to consider changing the JOIN to LEFT OUTER JOIN, otherwise you will only get results from studies that have at least one element. If you know that all studies have at least one element, then you can keep it as is, as this type of join is faster.


MySQL slave_open_temp_tables always shows 1 I have a MySQL database set up for testing xtrabackup. The database is MySQL 5.5.52.

It is configured as a slave, however, there is very little in the way of replication (1 query every 10 seconds).

During the last test of xtrabackup (last night) it failed to complete due to:

180606 01:05:18 Slave open temp tables: 1
180606 01:05:18 Slave_open_temp_tables did not become zero after 300 seconds

Looking at the the database now, it is still showing Slave_open_temp_tables = 1.

I have tried restarting replication, and flushing the tables and logs. But it insists on staying at 1.

In the end I was forced to restart the database entirely, and that fixed the problem.

Is there a better way of 'flushing' this status without needing to resort to a full restart.

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.


Why database diagrams are not working when domain account is attached to database owner? I was trying to use Database diagrams is SQL Server and got following error:

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

I tried to use this command to solve my problem

ALTER AUTHORIZATION ON DATABASE::mydb TO "domain\username"
GO

but it did not help. When I changed my domain account to sa and ran

ALTER AUTHORIZATION ON DATABASE::mydb TO sa
GO

I got rid of this error. The question is, why domain account is not acceptable for database owner (it seems that only sa annd NT AUTHORITY\SYSTEM are acceptable)?

You don't need to be sa or another sysadmin account to create database diagrams, but I think you may need to do this in order to initially install database diagram support.

If you do this as sa, you should then be able to let domain\username create diagrams. Most of the resources online will tell you that if domain\username is the database owner it should work, but there is a slight difference when you just set a login as the owner of a database and they aren't already a user inside the database: they get mapped to the special dbo account. I seemed to be able to do this in SQL Server 2012, but I used SQL accounts not domain accounts. So I don't know if that's what is causing your issue, but I think I have provided a valid workaround.


Increase in IO Wait after adding indexes with postgres I've recently been put in charge of a system that has been fairly untended for a period of time.

The majority of activity is via queued worker jobs based off of data feeds from other systems/data sources.

Via the slow query log I identified a number of slow queries using the PostgreSQL logs and added indexes to address these. This started around 7am or so.

Would the addition of these indexes have contributed to the large increase in IO/Wait?
What steps can I take to reduce this?

Yes, adding indexes could cause IO wait to increase.

Perhaps without the index, you are doing a lot of full scans of the table, thousands or millions of blocks, to get just one piece of data.  But the IO wait is very low, because the kernel read ahead keeps the pump primed so your process doesn't wait on IO (instead it uses a lot of User CPU to filter through the blocks to find the one row it wants).

With the index, it jumps exactly to the row it needs, but it needs to wait for it get read from disk, because read-ahead doesn't help you out.

  What steps can I take to reduce this?

Why does it need to be reduced?  Is there an actual problem?


InnoDB, Hash Table, Reference Table and Fragmentation I have a big list of domains and URLs that I store in a database, there are around 150M domains and 300M URLs, I'm using InnoDB to store each in the format:

CREATE TABLE IF NOT EXISTS `domains_list` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `domain` varchar(350) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Which is fine. New records are assigned what is effectively an auto-increment, so there's no fragmentation and inserts are in ascending order.

When new data comes in however (typically between 250K and 2M rows), I use two separate 'hash' tables to see whether the domain or URL already exists in the database. Strictly speaking it's not a 'hash' table, just a bunch of MD5s which I use ensuring values are unique, with the added benefit of the table being fixed length. The table is also partitioned.

CREATE TABLE IF NOT EXISTS `domains_hashes` (
  `id` int(10) unsigned NOT NULL,
  `segment` tinyint(3) unsigned NOT NULL,
  `hash` binary(15) NOT NULL,
  PRIMARY KEY (`segment`,`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (segment)
(PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
 ...
 PARTITION p254 VALUES LESS THAN (255) ENGINE = InnoDB,
 PARTITION p255 VALUES LESS THAN (256) ENGINE = InnoDB) */;

segment is basically the first character of the hash, which is used for the partition. With the remaining 15 bytes going into hash.

For seeing whether a bunch of domains already exist in the database, this works relatively well, however, the table gets fragmented due to the random nature of insertions.

The hash table is basically only used for insertions and quickly looking up whether a domain exists in the DB or not. During insertions, a script walks from 0-255 and performs the necessary check.

My question is, do you know of a better procedure in order to handle inserts/selects better? I believe when I started out with this database I simply had a key on domains_list.domain, which was slow.

I find that when the partitions are re-organised the lookups are very quick, but after a number of batch insertions, the same lookups slow down somewhat. Server has 32GB of RAM and I use 16GB for the buffer pool, while the table itself takes up 5.4GB on disk.

Several ideas come to mind:

The hash table could be a memory table, once your inserts are done, you could then copy just the domains that don't already exist into the permanent domain_list table. Having your new data in memory might also make the domain name comparison between the hash table and the domain_list tables go faster.

Another option would be to use the innodb_file_per_table option and keep the database (idb) files on a faster disk.

A third option would be to load your entire dataset into a memory table and then convert that table to InnoDB using the ALTER TABLE table_name ENGINE=InnoDB. This will be much faster than inserting into InnoDB directly. This is possible since you control how and when data is inserted.


What does "as anonymous on" means in mysql's general log? In mysql's general log I see things like:

141017  8:57:31     4 Connect   root@localhost as anonymous on

What is this "as anonymous on" thing even means? I log in as root@localhost. Am I still anonymous, or even "anonymous on"?

UPD

$ systemctl stop mysqld
$ rm -f /var/lib/mysql/yuri.log
// enable general log in /etc/mysql/my.cnf
$ systemctl start mysqld
$ mysql -u root -p -BNe 'SELECT USER() HowYouAttemptedToLogin, CURRENT_USER() HowMySQLAllowedYouToLogin;'
Enter password:
root@localhost  root@localhost
$ mysql -u root -p -BNe 'SELECT host, user FROM mysql.user;'
Enter password:
127.0.0.1   root
::1 root
localhost   hostname
localhost   root
$ cat /var/lib/mysql/yuri.log
/usr/bin/mysqld, Version: 10.0.14-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /run/mysqld/mysqld.sock
Time                 Id Command    Argument
141204 19:03:19     3 Connect   UNKNOWN_USER@localhost as anonymous on
            3 Connect   Access denied for user 'UNKNOWN_USER'@'localhost' (using password: NO)
141204 19:04:55     4 Connect   root@localhost as anonymous on
            4 Query select @@version_comment limit 1
            4 Query SELECT USER() HowYouAttemptedToLogin, CURRENT_USER() HowMySQLAllowedYouToLogin
            4 Quit
141204 19:05:47     5 Connect   root@localhost as anonymous on
            5 Query select @@version_comment limit 1
            5 Query SELECT host, user FROM mysql.user
            5 Quit

After some major league Googling all over the planet, I think I found the answer.

http://dbaspot.com/mysql/70551-how-disable-any-logging-mysql.html
https://lists.drupal.org/pipermail/support/2014-June/025447.html

From these posts I saw something interesting

13679547 Connect @localhost as anonymous on test
140612 12:33:07     3 Connect   gk_admin at localhost as anonymous on dTEST_db

My conjecture is the following: What follows as anonymous on is the currently connected database
mysql -u root -Dmysql -p -BNe 'SELECT USER(),CURRENT_USER()'

The general log entry must say as anonymous on mysql
mysql -u root -Dtest -p -BNe 'SELECT USER(),CURRENT_USER()'

The general log entry must say as anonymous on test
mysql -u root -p -BNe 'SELECT USER(),CURRENT_USER()'

The general log entry must say as anonymous on
Give it a Try !!!
UPDATE 2014-12-04 14:26 EST
Are you ready for this? I have an old post where someone has the source code with as anonymous on
log_warnings doesn't seem to work for aborted connections in MySQL 5.1
In the code from my 2-year-old post, as anonymous on is hardcoded !!!
Look at the code again
[29 Sep 2008 8:03] Konstantin Osipov
OK, it was fixed differently:
      /*
        Log the command before authentication checks, so that the user can
        check the log for the tried login tried and also to detect
        break-in attempts.
      */
      general_log_print(thd, command,
                        (thd->main_security_ctx.priv_user ==
                         thd->main_security_ctx.user ?
                         (char*) "%s@%s on %s" :
                         (char*) "%s@%s as anonymous on %s"),
                        thd->main_security_ctx.user,
                        thd->main_security_ctx.host_or_ip,
                        db ? db : (char*) "");

So, this is logged in the general log at least.

I was reviewing a patch that added more logging,
but I can't remember the worklog task number.

Please note the part of the code that references the database db ? db : (char*) "");. Either the db variable (the current database) points to a database name or an empty string.
The weird part about answering this question is that this is the second time I had to refer back to a post I myself wrote years ago. The first time was this: Does MySQL still handle indexes in this way?
UPDATE 2014-12-17 16:45 EST
Based on your last comment

But what exactly is your answer? That it doesn't matter what "as anonymous" mean? Otherwise, we've got yet to find out why thd->main_security_ctx.user is falsy.

In your question, you asked What is this "as anonymous on" thing even means? The answer I said was Either the db variable (the current database) points to a database name or an empty string. As for thd->main_security_ctx.user, thd->main_security_ctx.host_or_ip, that would be root@localhost. What comes after as anonymous on is the current DB. This is nothing left to figure out.
You then asked Am I still anonymous, or even "anonymous on"?. The answer would have to yes because you have been authenticated as a user of a DB Connection and your connection becomes an active thread like any other thread.
Any connection

DB connection from an authenticated user
system user (internal DB Connection for Replication)
Event Scheduler (DB Connection for Processing events)

are all equal threads. The general log does not differentiate one DB Connection from another. The code checks all connections the same way regardless of connections being internal or external.
If what my answer has stated still does not answer your question, there is really one thing you can do: Go to the MySQL Forums and post this question to the Developers. Since Konstantin Osipov was the developer who was responding to the Bug List and refers to this source code (bugs.mysql.com/bug.php?id=24761) that uses the expression as anonymous on in the general_log_print function, he would have to know the correct context and reason for using the expression as anonymous on.
UPDATE 2018-08-12 19:46 EDT
In ight of your conjecture, I went Googling around and found sample output from a general log:
131021 17:43:50    43 Connect root@localhost as anonymous on pnet_blog
       43 Init DB pnet_blog
       43 Query SELECT count(id) as total_posts FROM posts WHERE date_published is not null AND date_published <= '20131021144350'
       43 Query SELECT * FROM posts WHERE date_published is not null AND date_published <= '20131021144350' ORDER BY date_published DESC LIMIT 0,10
       44 Connect root@localhost as anonymous on pnet_blog
       44 Query SELECT id, title, impressions FROM tips WHERE date_published IS NOT NULL AND date_published <= '20131021144350' ORDER BY date_published DESC LIMIT 0, 10
       44 Quit
       43 Quit
131021 17:44:28    45 Connect root@localhost as anonymous on pnet_blog
       45 Init DB pnet_blog
       45 Query SELECT * FROM posts WHERE url='how-and-when-to-enable-mysql-logs'
       45 Query UPDATE posts SET impressions=impressions+1 WHERE id='41'
       45 Query SELECT url, post_title FROM posts WHERE date_published IS NOT NULL AND date_published < '20131020150000' ORDER BY date_published DESC LIMIT 0,1
       45 Query SELECT url, post_title FROM posts WHERE date_published IS NOT NULL AND date_published > '20131020150000' ORDER BY date_published ASC LIMIT 0,1
       45 Query SELECT * FROM posts WHERE date_published is not null AND date_published <= '20131021144428' AND date_published >= '20130421144428' ORDER BY impressions DESC LIMIT 0,10
       46 Connect root@localhost as anonymous on pnet_blog
       46 Query SELECT id, title, impressions FROM tips WHERE date_published IS NOT NULL AND date_published <= '20131021144428' ORDER BY date_published DESC LIMIT 0, 10
       46 Quit
       45 Quit

Please look at both timestamp entries from the sample log:
131021 17:43:50    43 Connect root@localhost as anonymous on pnet_blog
131021 17:44:28    45 Connect root@localhost as anonymous on pnet_blog

as well as the original log entry from your question:
141017  8:57:31     4 Connect   root@localhost as anonymous on

At these points in time, the user is not authenticated even though  root@localhost appears. The comment for the code does support your conjecture. You now have the answer directly from the source code's author.
Thank you for accepting my posted answer (which lead to you finding the real answer)


Can IS DISTINCT FROM be combined with ANY or ALL somehow? Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result?

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo <> any(array[null, 'A']);

 count
-------
     1
(1 row)

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo is distinct from any(array[null, 'A']);

ERROR:  syntax error at or near "any"
LINE 3: where foo is distinct from any(array[null, 'A']);
                                   ^

Looking at it as a grammar problem, ANY is defined as
(in Row and Array Comparisons):

  expression operator ANY (array expression)

But is distinct from is not an operator, it's a "construct" as we're told in Comparison Operators:

  When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM
  constructs

Since PostgreSQL has user-defined operators, we may define an operator/function combo for this purpose:

create function is_distinct_from(text, text) returns bool as
'select $1 is distinct from $2;' language sql;

create operator <!> (
 procedure=is_distinct_from(text,text),
 leftarg=text, rightarg=text
);

Then it can precede ANY:

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo <!> any(array[null, 'A']);

 count
-------
     3
(1 row)


Query Conflicts in Streaming Replication in Postgres 9.4 When using Streaming Replication in Postgres 9.4, when a query makes the standby Postgres "start to count" the max_standby_streaming_delay, changes being made in the primary:

Are dammed at the primary (not sent to standby) using the wal_keep_segments setting to limit that; OR
Are sent to standby and stored locally (where?) at the standby for later processing?

Scenario 1 would lead to a bigger data loss in the event of a crash in the primary. On the other hand, the scenario 2 may lead to a filesystem being completely filled if the allowed dalay is too big.

They are stored in the replica's pg_xlog directory.


Running a query on the results of another query? I have 2 queries, they both run fine and pull the results I'm looking for. However, I need to be able to run one query from the results of another. This is t-sql/mssql/sybase

For example, the following query works to count the number of times/rows a each value shows up in the 'referred_link' column of the cases table, and show me only those that appear 3 or more times:

SELECT cases.referred_link, count(*)
FROM CASES
GROUP BY referred_link
HAVING COUNT(*) >= 3

The next query pulls a bunch of data from various tables and filters down on the required criteria. The catch is, I only want this 2nd query to run on the results from the first query... items which show up 3+ times on the referred_link table.

select * from cases inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='home_addr') a
on names.names_id=a.names_id
left join party on cases.casenum=party.case_id
where names.person='Y' and date_opened>=today()-6 and party.our_client='Y' and party.role='Plaintiff'

I tried something like this (doesn't work, of course):

select * from cases inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a
on names.names_id=a.names_id
left join party on cases.casenum=party.case_id
where names.person='Y' and date_opened>=today()-6 and party.our_client='Y' and party.role='Plaintiff'
and names_id IN (SELECT cases.referred_link, count(*)
                FROM CASES
                GROUP BY referred_link
                HAVING COUNT(*) >= 3)

Can anybody point me in the correct direction for how this could be accomplished?

When you put a subquery between IN(…), you have to ensure it returns exactly one column. Going by your other query, it seems cases.referred_link is actually already a names_id, so you are already pretty much there. All you need to do is remove that extra count(*) from the list of selected columns.

… and names.names_id IN (SELECT cases.referred_link
                FROM CASES
                GROUP BY referred_link
                HAVING COUNT(*) >= 3)


Log all accessed objects during a certain time period We are running quite a few tests for our software that require a certain set of databases. These databases are usually huge, while most of the data in there will not be used by any testcase.

In order to reduce stress on our database server due to many CI tests, we would like to remove the unused data and it would be great if that would be possible in an automated way.

Here I'm thinking of something like "Run the tests and log which objects (e.g. Table and PK) are accessed (SELECT, UPDATE, DELETE)". Then afterwards we could remove all which haven't been accessed. Is there a way to collect this information in SQL Server 2016?

Thanks, Alex

Updating answer

There are two parts to your question:

  In order to reduce stress on our database server due to many CI tests, we would like to remove the unused data and it would be great
  if that would be possible in an automated way.

This is to do with data inside tables, meaning we have to get to the row level in order to capture what has been updated/deleted(DML). This can be achieved by enabling tracking. Copied from Microsoft site:

  SQL Server 2017 provides two features that track changes to data in a
  database: change data capture and change tracking. These features
  enable applications to determine the DML changes (insert, update, and
  delete operations) that were made to user tables in a database. Change
  data capture and change tracking can be enabled on the same database;
  no special considerations are required. For the editions of SQL Server
  that support change data capture and change tracking, see Features
  Supported by the Editions of SQL Server 2016. Change tracking is
  supported by SQL Database. Change data capture is only supported in
  SQL Server and Azure SQL Database Managed Instance.

You may refer this link from Microsoft to understand details. Alternatively you can enable auditing on tables for any DML operations and you may also club these two as mentioned here.

For more details on Change tracking feature in SQL server database, you may refer another link from Mr. Arshad Ali.

Above will help you for tracking DML operation only however for tracking select, its really difficult and can be traced only by enabling auditing as mentioned here.

  Here I'm thinking of something like "Run the tests and log which
  objects (e.g. Table and PK) are accessed (SELECT, UPDATE, DELETE)".
  Then afterwards we could remove all which haven't been accessed. Is
  there a way to collect this information in SQL Server 2016?

As data level is tracked, we can go a level higher i.e. object level. In order to handle the usage/non-usage of any object, you may refer below.

There is a wonderful article from Mr. Ahmad Yaseen on finding unused objects in any given databases. You may Below is the query which can be used:

-- Create CTE for the unused tables, which are the tables from the sys.all_objects and
-- not in the sys.dm_db_index_usage_stats table

; with UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate )
AS (
  SELECT DBTable.name AS TableName
     ,PS.row_count AS TotalRowCount
     ,DBTable.create_date AS CreatedDate
     ,DBTable.modify_date AS LastModifiedDate
  FROM sys.all_objects  DBTable
     JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name
  WHERE DBTable.type ='U'
     AND NOT EXISTS (SELECT OBJECT_ID
                     FROM sys.dm_db_index_usage_stats
                     WHERE OBJECT_ID = DBTable.object_id )
)
-- Select data from the CTE
SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate
FROM UnUsedTables
ORDER BY TotalRowCount ASC

Kindly note that, this will work from the last restart time of SQL service/server as mentioned:

  We will use the sys.dm_db_index_usage_stats system DMV that stores the
  number of scans, seeks and updates performed on the database tables
  since the last time the SQL Service was restarted.

You may read more details here.

There are other articles also, which could be referred for this purpose. For instance, you may use sp_blitzindex for finding similar info from Mr. Brent Ozar.

For checking access date/time for views/function/SPs, you may refer below question:

Query last time a view, funcion or sp was last used/read

There is another article from Mr. Bart Gawrych on the same topic:

  select [schema_name],
           table_name,
           max(last_access) as last_access
    from(
        select schema_name(schema_id) as schema_name,
               name as table_name,
               (select max(last_access)
                from (values(last_user_seek),
                            (last_user_scan),
                            (last_user_lookup),
                            (last_user_update)) as tmp(last_access))
                    as last_access
    from sys.dm_db_index_usage_stats sta
    join sys.objects obj
         on obj.object_id = sta.object_id
         and obj.type = 'U'
         and sta.database_id = DB_ID()
    ) usage
    where last_access < dateadd(month, -3, current_timestamp)
    group by schema_name,
             table_name
    order by last_access desc;

This again depends on system DMV, so be wary of last reboot of service/server.

Note: Your question is on how to find list of objects which are getting used however there are tonnes of article on finding unused objects, you may tweak them a bit to get list of used objects or else if you do manage to find unused objects, that should also serve your purpose.

Please let us know if this helped you in achieving your objective.


How to Get the Sum (total) of a column in a query result set Is there a way that I can get the sum of a column in my query result set by just clicking on the column (something like in Excel) so that I don't have to copy and paste the column into Excel to the see the total sum of all the values in the column?

I'm running SQL Server 2008.

Use the OVER clause to modify a SUM scope in your query. No GROUP BY needed

SELECT
    MyColumn, OtherColumn,
    SUM(MyColumn) OVER () AS SumTotal
FROM
    ...

Otherwise, SSMS isn't a calculating engine: it display query results so add it to your query...


SQL Server 2017 Always-ON I have configured Distributed AG from DC1 to DC2 (SQL 2017 Ent edition with CU22 version)
DC1- DB1, DB2 and DC2-DB3, DB4

I have restarted DC1-DB1 SQL services, after restart DC1-DB2 should be primary. But still DC1-DB2 showing as  secondary.  Is this is the expected behaviour?

I have restarted DC1-DB2 SQL services, nothing happen - still DC1-DB1 is primary. Is this is the expected behaviour?

DC1 AG configurations:

The exact similar question was asked on new Microsoft Q and A Site. I created a 2 node WSFC with AG configured for SQL Server databases. To simply put WSFC did not initiated a failover because the parameter Failure-Condition Level is default 3. And as per Microsoft official document Configure a flexible automatic failover policy for an Always On availability group for a SQL Server restart to trigger a failover you need Failure-Condition Level=1
This can be changed via Powershell, TSQL or from cluster GUI. One below shows value in cluster GUI


How to find percentage data change after last full backup? I am trying to find the data change rate for all databases on a server. In the past, I used Paul Randal's script How much of the database has changed since the last full backup which worked great. Except for databases that have a '-' (hyphen) in the name.

I understand that using '-' in a database name is not really a good practice, but an application creates the databases that way.

Also looked into Paul Randal's another script  to trend growth of the databases from backups, but that's not something that will work for me.

Was able to calculate the average growth for data files over a period of time, but I am interested in the actual amount/percentage of data that got changed after the last full backup OR in the last one day/week etc..

Any ideas how to modify Paul Randal's stored procedure to work with hyphens in the database name? Or is something like this even possible?

Please suggest if there are any other options available.

Well, you should be able to fix his procedure with a very simple change. Change this line:

SELECT @dbccPageString = 'DBCC PAGE (' + @dbName + ', '

To this:

SELECT @dbccPageString = 'DBCC PAGE (' + QUOTENAME(@dbName) + ', '

But really, fix your database names. This is not the last thing that will break as a result.


Blitz procedure failing When I run the latest version of sp_blitz I get the following:
Warning: Null value is eliminated by an aggregate or other SET operation.

  Msg 1934, Level 16, State 1, Procedure sp_Blitz, Line 3467

  INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

It seems no one else is having this problem except me. What have I done wrong?

In SSMS settings, make sure the SET QUOTED IDENTIFIER box is checked. That should fix it.


Update an entry in table when time expires I need to modify an entry in table, based on time.

i.e. table contains customer-info & subscription-info. I need to modify/delete subscription when subscription expires for each user.

There can be upto 2-3 million entries in the table.

each user will have different expiration date so what is the best means to achieve this?

Is there some sort of trigger mechanism which gets triggered when time expires?

or is it safe to create an event that poles per second or create separate event for each subscriber?

or is it safe to have a third party timer tool that maintains expiration times and trigger an action when timer expires?

or is there a better approach to address this requirement?

btw: database is sql server.

Thanks & Regards,
Prasun

A trigger would not be the most appropriate solution for you particularly for a large table which could have lots of hits.

Right a simple stored procedure and just have a SQL Server Agent Job that runs it periodically (as often as is required for your business purposes).

Something simple like:

UPDATE
    custSubsTable
SET
    subscritionActive = 0
WHERE
    subscritionActive = 1
        AND
    expiryDate < GETDATE()

Would work.
I don't think the first half of the WHERE clause is strictly necessary, but it doesn't hurt. You could just stick the code straight into the Job, but it'll be more easily maintained as a stored procedure and can be manually run that way

This could easily be modified to delete instead of update, or even possible delete/update a different row in the same table (by joining the table with itself), although if that is the case then best practise would dictate denormalising your db and splitting the tables up.

Even with a large table, that should be a fairly quick statement to run if it's done frequently enough - obviously the more changes it has to make at once the longer it could be. Make sure you run it manually yourself the first time and add/update any indexes if necessary.

You can do this across multiple tables - I won't get into it here and it's beyond the scope of your question (and I know nothing about your DB), but my first impression, purely from what you say in your question, is that holding customer and subscription data in a single table could lead to lots of denormalisation, might be something to bear in mind.


Error on open the master key I create a copy of a database with DB Master Key in the same database server, this server is part of an activity group I run in both servers this script:

OPEN MASTER KEY decryption by password = 'This is the Key';
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'This is the Key';
Alter master key add encryption by service master key;

This fix temporary the decription of the rows, but when the server run on fail over and swap the primary node i must rerun the script to fix again the problem, this issue only affect the restored database, the original one, works normarlly.

Update

I already try restore the key exporting it from the other server but do not work.

The issue here is that SQL Server can only open the database master key if it has been encrypted with the service master key. In your scenario, the primary server can open the DB automatically because the DMK is encrypted by the SMK. When you failover, the new servers SMK was not used to encrypt the DMK so can't open it automatically.

When you run your script, it is decrypting the DMK using the password, re-generating it, then re-encrypting it using the service master key of the new server. The solution to this is quite straightforward:

sp_control_dbmasterkey_password

This system stored procedure is used to create a CREDENTIAL object in SQL Server that contains the DMK password. On start-up (or failover) SQL Server will attempt to use the SMK to decrypt the DMK. If that fails, it locates all credentials with the same family GUID as the DB being decrypted and tries each credential in turn until one is successful or no credentials are left.

Execute this stored procedure (providing the values as appropriate) on your secondary node and the next failover should see the DMK decrypted automatically.


Scaling down DB where filesystem is almost full Our DBA is on holiday, and since I know 'a bit' of MySQL, they've asked me to fix this. The data slice is at 99%, and I need to know what to get rid of.

  [root@servername]# find . -type f -size +1000000k -exec ls -lh {} \; |
  awk '{ print $9 ": " $5 }' ./mysql/stuffInDatabase.ibd: 6.8G

Thing is, I've never done this before, and so don't know where to start. Can someone point me in the right direction? Which database do I need to use, the production one or the information_schema? Which query do I use? Do I need to dump and then reimport?

Thanks in advance.

If you are not experienced at both mysql and linux sysadmin I would suggest backing off from this task: if this is a production system there could be much trouble if something goes wrong and data is lost! If you do proceed, take care to make sure you take, and test, fresh backups before doing anything.

I'm assuming the tables are of InnoDB type as that is the default these days. IIRC InnoDB files can not be shrink by the standard tools: when data is deleted the space is just left unallocated internally (not released by to the filesystem) to be used again as more data comes in. As your file is not ibdata I assume you have the "one file per innodb table` option turned on, in which case to free unallocated space within the file, dump that table to file, drop the table, recreate the table and then import the dump file back into it. If there is little free space in the file to start with (i.e. there has been very little delete activity recently) this won't help much though: the resulting file will be more-or-less the same size after you have finished. Also, as the database will be in an inconsistent state during the process, you should block access to it (aside from the admin accounts you need to perform the dump/drop/rebuild/restore of course) for the duration.

If your server uses LVM for space/partition management then you may have free space in the appropriate volume group and can just expend the volume to allow extra space. If you have room on other file-systems you could move the file to one of those and link to it from its currently location (IIRC mysql is happy to follow softlinks to find data-files) like so:

stop mysql
move the files to a volume with more space (though not /tmp or anything with tmp in the name - these may be wiped on start-up after reboot or power-fail)
create a sym-link with the same name in the old location pointing to the new on
restart mysql

(obviously all your databases on this machine will be inaccessible during this process)

To see what free space you have where on current filesystems run df -h. If you are using LVM run the commands lvs, vgs and pvs to see what space you have where outside that currently allocated to filesystems (if you are not using LVM these three commands will either return nothing or not be found). I may be straying a tad far off-topic here IMO (out of DBA-land and into ServerFault territory) so I'll not go into more detail.

I'll say again though: if you are not a confident DBA+sysadmin and this is a production system I would stay away. Failing that, at least try practice on another system first (do you have a development environment setup similarly to the production one?) so you can verify what you are about to do before doing it on real data.

If you do do anything make sure you fully document your actions (all commands entered and why, all output received) so your usual DBA/sysadmin can review things upon return and make any further adjustments that may be needed to keep things running well long-term.


modelling warehouse picking What's the best way to approach modelling a warehouse picking system?

To be precise, customers place orders which are processed by warehouse staff.

A simple products and orders database isn't an issue. My trouble comes breaking down orders into smaller warehouse trips. Tips are created based on the quickest route around the warehouse based on product locations. Two assistants can pick different parts of the same order (different trips), but they can also be picking multiple orders.

I can't wrap my head around how I'd make a simple model of this, I know I need to break an order down into a smaller order(s) based on whatever criteria (warehouse location in this case).

I think it'll be along the lines of (some obvious fields missing for clarity):

TRIPS {
TripID
AssitantID
SmallOrderID
}

ORDERS {
OrderID
ProductID
Quantity
}

SMALL_ORDERS {
SmallOrderID
OrderID
}

I know this isn't right as the SMALL_ORDERS isn't broken down, I'm just duplicating ORDERS. Would I make SmallOrderID on the TRIPS table, contain multiple values or is this a bad idea?

This is solely for a self-learning programming project, I'm not looking for anything enterprise-like. It's a simple version of a major enterprise system we use where I work.

I'd be more than happy if anyone can tell me the correct type of schemes I should be googling for, I assume they must have a certain name.

thanks.

My first IT job was in this area - basically I was involved in a "fiddle" on the part of my employer to land a big contract which involved convincing the client that we had a functioning and "intelligent" order picking system. It involved alcholic beverages and there are many complex rules about these (tax reasons).

Anyway, I just wish that Open Source had been as prevalent then as it is now - because there appear to be a plethora of high-quality projects out there in this area. A quick Google landed me here and here - search for the word "pick" on these pages.

If I were you, what I would do is examine these (and others...) packages and see which ones correspond most closely to your needs - download and install them and then check out the underlying schemas and see which bits you can borrow for your project. Or you may just decide to implement one of the systems that's already out there?

What I do know is that the logistics behind these problems are very complex (operations research, tricky mathematics &c.), so you might be able to take some of the pain out of your project by "standing on the shoulders of giants"!

[EDIT]

You might like to check out this and its parent site- it specifically mentions bin-packing and employee rostering, so it might be of interest. I remember this from studying operations research. If you work in Java, it could be of interest - although the principles obviously apply to all languages.


Why so many logical reads? I have a very simple query that is showing in Activity monitor and other statistics as having the most logical reads on my entire DB server.

SELECT MAX(RESULT_DATE) FROM TABLEX mm WITH (NOLOCK)
JOIN TABLEX_RESULTS mr WITH (NOLOCK) on mr.ID = mm.ID
WHERE DAYS IS NOT NULL AND mm.ORDER_ID = 12345

TABLEX has about 28 million rows

TABLEX_RESULTS has about 6 million rows

What can I do here to reduce the number of logical reads on this query?  I'm kind of confused on how such a simplistic query can have such a massive number of logical reads.

Thanks

Index definition (from comment)

tableX
IndexName                    PK     Type    Key1            Key2
IDX_MP_MEDS                  0      B AA    ORDER_ID        MEDPASS_DATE
IDX_MP_MEDS_ID_AND_ORDER_ID  0      B A     MEDPASS_DATE
IX_MP_MEDS_ROOT_ORDER_ID     0      B DA    ROOT_ORDER_ID   MEDPASS_DATE
PK_MP_MED PK                 1      C A     MEDPASS_MEDS_ID

tablex_resulsts

Those are page reads, mind you.  That is relevant.

Possibilities:

Improper or insufficient indexing.  Are any of the filtered fields indexed?  How wide are the indexes (see below)?
Poor page density.  What's your fill factor on any indexes you may have?  If it's too low, you are pulling a lot of pages for this.
Very wide indexes.  If you have indexes but they have a lot of fields, or very wide fields, then you get less rows per page.

I'm guessing you have a table or clustered index scan happening for at least one of these criteria, and the table is wide-ish which causes a lot of data to be read, regardless of how much data you actually need.


How does SQL Server determine precision / scale? I'm running SQL Server 2012

SELECT
   0.15 * 30 / 360,
   0.15 / 360 * 30

Results:

 0.012500,
 0.012480

This one is even mor confusing to me:

DECLARE @N INT = 360
DECLARE @I DECIMAL(38,26) = 0.15 * 30 / 360
DECLARE @C DECIMAL(38,26) = 1000000

SELECT @C *  @I *  POWER(1 + @I, @N)  / ( POWER(1 + @I, @N) - 1 )
SELECT @C * (@I *  POWER(1 + @I, @N)  / ( POWER(1 + @I, @N) - 1 ) )

The first select gives me the correct result: 12644.44022
The second one truncates the result: 12644.00000

Determining precision and scale resulting from expressions is a rat's nest and I don't think anyone understands the exact rules in every scenario, especially when mixing decimal (or float!) and int. See this answer by gbn.

You can of course tailor the expressions to give you what you want by making much more verbose explicit conversions. This is probably overkill but:

SELECT
   CONVERT(DECIMAL(15,6), CONVERT(DECIMAL(15,6), 0.15)
   * CONVERT(DECIMAL(15,6), 30)
   / CONVERT(DECIMAL(15,6), 360)),
   CONVERT(DECIMAL(15,6), CONVERT(DECIMAL(15,6), 0.15)
   / CONVERT(DECIMAL(15,6), 360)
   * CONVERT(DECIMAL(15,6), 30));

Neither result is rounded wrongly due to broken floating point math or wildly wrong precision/scale.

0.012500    0.012500


MYSQL: Display NULL value when foreing key is NULL I have the following database structure in MYSQL:

CREATE TABLE xs_bugtracking (
                    `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    `product` VARCHAR(64) NOT NULL,
                    `title` VARCHAR(64) NOT NULL,
                    `status` VARCHAR(8) NOT NULL,
                    `descr` VARCHAR(1024) NOT NULL,
                    `assignee` INT(11),
                    `reported_by` INT(11) NOT NULL,
                    `importance` VARCHAR(8),
                    `create_date` INT(11) NOT NULL,
                    `modify_date` INT(11) NOT NULL
                    );

The fields assignee and reported_by are foreing key of the same table of wordpress db: wp_users

Now i want get all element with the following SELECT query:

SELECT xs_bugtracking.id, product, xs_bugtracking.title AS title, status, xs_bugtracking.descr AS descr,
            assignee_tbl.user_nicename AS assignee,reported_tbl.user_nicename AS reported_by,importance,
            FROM_UNIXTIME(create_date) AS 'create_date', FROM_UNIXTIME(create_date) AS 'modify_date'
            FROM xs_bugtracking, xs_products, wp_users AS assignee_tbl,
            wp_users AS reported_tbl WHERE
            xs_products.name=product AND xs_products.lang='en' AND
            (xs_bugtracking.assignee=assignee_tbl.ID OR ISNULL(xs_bugtracking.assignee)) AND
            xs_bugtracking.reported_by=reported_tbl.ID

Now, problems come when you have a row with assignee equals to NULL, infact the resulting table multiplies the rows when the value is NULL, because lost the JOIN from wp_users aka assignee_tbl.

There is a way to display assignee_tbl.user_nicename when assignee is not NULL and just NULL when assignee is NULL?

If you want check the full source code

Thanks in advance!

I suggest you use the ANSI join that was introduced in SQL92. If you use a LEFT JOIN I think you achieve what you want:

SELECT xs_bugtracking.id, product
     , xs_bugtracking.title AS title
     , status
     , xs_bugtracking.descr AS descr
     , assignee_tbl.user_nicename AS assignee
     , reported_tbl.user_nicename AS reported_by
     , importance
     , FROM_UNIXTIME(create_date) AS 'create_date'
     , FROM_UNIXTIME(create_date) AS 'modify_date'
FROM xs_bugtracking
JOIN xs_products
    ON xs_bugtracking.product = xs_products.name
LEFT JOIN wp_users AS assignee_tbl
    ON xs_bugtracking.assignee = assignee_tbl.ID
JOIN wp_users AS reported_tbl
    ON xs_bugtracking.reported_by=reported_tbl.ID
WHERE xs_products.lang='en'


Database column vs row size I have Column A and Column B in a single table.

I need to add 1,000,000 records to this table.

I have 2 options:

Keep 2 columns and add 1,000,000 records
Add more columns and flatten out the data. This will decrease the row count by half.

Am I correct in assuming the table size will be the same for both options?

The fact is that this question cannot be answered as such, so I will reframe it and answer as reframed.

The basic problem is that the database size will depend entirely on implementation details of the db.  It could be the same size, could be more, could be less.  We can't say.  Additionally it may depend on how much repetition is avoided using one approach or the other.

At any rate it is exactly the wrong question to ask here.  The right question is how your data relates to eachother.  You basically have three options:

Second table with 1000000 rows.  Use this if there are normalization issues or if you need to break this off for table space reasons.  It may increase the size of the db because of join conditions.
New column.  Use this if the data does not belong in the same column of the other table due to semantic reasons, but is functionally dependent on the data in the existing rows.
New rows.  Use this if the data belongs in the existing rows based on the internal semantics of the data.

Note that table size is not your biggest concern.  You can probably use indexes to address them but 1-2 million rows is still way too small to worry about size-wise in this way.