Is there a kind of deadlock that SQL Server won't kill automatically? We've been having some blocking problems on SQL Server 2014.

First, let me test my knowledge:

A deadlock, as I understand it, is when process A blocks process B which then also blocks process A, creating a cycle in the graph of blocked processes. It was my understanding that, when this occurs, SQL Server can recognize it and kill one of the processes, the deadlock victim.  I assume SQL Server can also recognize a cycle of three or more processes, too, but maybe not?

What's not a deadlock is when a process locks a resource, then some other processes need it and waits for it to become unlocked. If the first process never completes, then the other processes will just wait (until they timeout). SQL Server will not kill any processes in this case because blocking is normal.

Now, are there any kind of deadlocks that SQL Server either can't detect or will choose not to kill?

We have some users running heavy queries, a job that runs every 5 minutes to update the tables, and a second job that runs a small query to check the health of the first job. (It checks the most recent date and sends an email if the data is stale.)

My DBA tells me that some combination of the above three things is creating a deadlock, but that SQL Server won't kill it. Not a normal block, but an actual deadlock. Is that possible?

As an anecdote that may or may not shed some light on your particular issue, we've had a similar problem to this where an external application would execute a stored procedure and improperly roll back when an error was encountered.

This procedure was vendor provided and unable to be modified.  Adding to this, XACT_ABORT was set to the default value of OFF and could not be modified.

The application would reach a CommandTimeout event (due to SqlException being thrown and the application not handling this appropriately), but the session would remain open.

Since XACT_ABORT is OFF and explicit transaction statements were used in the SP without appropriate error handling, the session would maintain any locks held by the aborted procedure. The session itself would be in a "SLEEPING" status with "AWAITING COMMAND".

This was a somewhat insidious issue for us, because the failed session would not be immediately obvious. However, contention would rapidly grow in the way that a vehicle stopping in the middle of a busy intersection will cause a multi-car incident. Unrelated applications attempting to access the locked resources held by the now-orphaned session would simply WAIT, entering a SUSPENDED status while potentially holding locks of their own, creating a scenario of branching contention and lock escalation.

While there are certainly other methods, this was a very busy application database which limited the usefulness of more direct methods which would have been optimal in a more controlled or quieter environment. In order to quickly and easily identify the ROOT session responsible, we cobbled together the following query, which returns the contention "tree" and allowed us to KILL the offending session (once appropriately identified).

USE master
GO;

WITH CTE_Blks
AS (
 SELECT Blocked [SPID]
 FROM sysprocesses
 WHERE Blocked <> 0
 )
 , CTE_Tree
AS (
 SELECT DB_NAME(sp.dbid) AS [Database]
  , spid AS [Session]
  , Blocked [BlockedBy]
  , sp.STATUS AS [Status]
  , cmd [Command]
  , loginame AS [Login]
  , hostname AS [Host]
  , program_name AS [Program]
  , ec.client_Net_Address [IPv4]
  , ec.Client_TCP_Port AS [Port]
  , Last_Batch
  , ST.Query [Transaction]
  , ST2.Query AS [MostRecentStmnt]
 FROM sysprocesses SP
 LEFT JOIN sys.dm_exec_connections ec ON ec.session_id = sp.spid
 CROSS APPLY (
  SELECT TEXT AS [processing-instruction(x)]
  FROM sys.dm_exec_sql_text(SP.sql_handle)
  FOR XML PATH('')
   , TYPE
  ) AS ST(Query)
 CROSS APPLY (
  SELECT TEXT AS [processing-instruction(x)]
  FROM sys.dm_exec_sql_text(ec.most_recent_sql_handle)
  FOR XML PATH('')
   , TYPE
  ) AS ST2(Query)
 WHERE blocked <> 0
  OR spid IN (
   SELECT SPID
   FROM CTE_Blks
   )
 )
SELECT CASE
  WHEN BlockedBy = 0
   THEN 'ROOT'
  WHEN BlockedBy <> 0
   AND EXISTS (
    SELECT 1
    FROM CTE_Tree s1
    WHERE s1.BlockedBy = t1.Session
    )
   THEN 'BRANCH'
  ELSE 'LEAF'
  END [NodeType]
 , t1.*
FROM CTE_Tree t1
ORDER BY [NodeType]

To add some explanation to this:

ROOT is obviously the orphaned session and should be KILLed.
A LEAF is a blocked session which is not, itself, blocking other transactions.
A BRANCH is a blocked session which, due to its own locks, is blocking another session--either another BRANCH in the chain, or a LEAF (terminal).

The use of returning our sql_handle text as an XML processing-instruction
may seem odd, but was deliberate to circumvent truncation, preserve formatting, and bypass the need to explicitly escape characters.


How can i improve MySQL Delete performance? When I delete 5000 rows from a table .It is costs more than 2 seconds but if I change delete to select it is just costs a few milliseconds.

How can I improve the performance?

Here is the details:

There are 2 million rows. And the meeting_number have index.

    mysql> show table status like 'zm_meeting_ext'\G;
    *************************** 1. row ***************************
               Name: zm_meeting_ext
             Engine: InnoDB
            Version: 10
         Row_format: Compact
               Rows: 2018854
     Avg_row_length: 318
        Data_length: 643842048
    Max_data_length: 0
       Index_length: 1225310208
          Data_free: 59768832
     Auto_increment: NULL
        Create_time: 2015-08-29 05:48:54
        Update_time: NULL
         Check_time: NULL
          Collation: utf8_general_ci
           Checksum: NULL
     Create_options:
            Comment:
    1 row in set (0.24 sec)
mysql> show index  from zm_meeting_ext\G;
*************************** 1. row ***************************
        Table: zm_meeting_ext
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 2018856
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: zm_meeting_ext
   Non_unique: 0
     Key_name: meeting_ext_key
 Seq_in_index: 1
  Column_name: meeting_number
    Collation: A
  Cardinality: 2018856
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

delete Statement：

DELETE  FROM zm_meeting_ext
        WHERE   meeting_number IN
                 (
                    888234641
                 ,
                    713842289
                 ,
                    587345701
                 ,
                    543533869
                 ,
                    885497679
                 ,
                    805900814
                 ,
                    804899468
                 ,
                    115617318
                 ,
                    582279870
                .......
                5000 numbers
                )

So how can I improve the performance and what is the principle of the delete operation?

When You SELECT data - You are work with InnoDB memory cache, You not write to disk, You are not log operations.

It is normal when same select thousands time faster then DELETE.

When You run DELETE command, MySQL must:

do the same as SELECT - found rows
log operation (write into redo-log files)
mark rows as deleted
lock deleted rows - wait if any other operations use them
update indexes

2 sec it is not significant huge time, but if it make collisions for other operations - You can to split operation for chunks


Why would someone store text in an SQL Image field? I've been asked to get some info out of the database of a system we use. I've found what I'm looking for, but its left me... confused.

For some reason, the developers have the system storing text in an SQL Image field. I suspected this, but wasn't entirely sure, until I took the Hex output from my select statement, put it into a Hex to ASCII converter, and hey presto, it's exactly the kind of message I'm looking for.

What I'd like to know is: why? Is there some legitimate reason why someone would store text in an Image field?

Given the quite serious and private nature of these messages, the only thing I can think of is that someone was seriously mistaken in thinking that storing the text in an Image field would somehow make it more secure? Or is there really just something obvious I'm missing?

EDIT: Covered in the comments, but: At minimum, this text will occasionally likely include accented characters and such. Plus, my manager just let me know that the software was written using SQL Server 2000, or maybe even a version or two earlier. Possibly the simple answer is that there wasn't an analogue to nvarchar or similar back when it was written?

The only options to store data over 8000 bytes was text, ntext, or image back in the days of SQL Server 2000. The appropriate choice for long character data strings would be text or ntext, with text appropriate for single-byte characters tied to the column collations code page and ntext for Unicode (UCS-2).

I can only speculate as the why developers used the binary image type to store character data but it could be in order to store characters as UTF-8, which is more compact that ntext UCS-2. Although this method saves space, it does come with the cost of not being able to easily evaluate the character data in T-SQL; encoding/decoding of UTF-8 stored as binary needs would need to be done in the application code.


Using Resource Governor with SQL Agent Jobs I'm trying to setup Resource Governor for limiting IOPS for processes running using SQL agent jobs. The classifier function is set to identify a particular login (any spid that is running using that login should use the resource group it is assigned to). I then added EXECUTE AS LOGIN = 'ResourceGovernerUser' within the job, but I cannot get it to work and it falls back to the default pool because the jobs are 'EXECUTED AS 'ServiceAccount''. Although if I look at the active processes using sp_WhoisActive, the login name is displayed as 'ResourceGovernerUser' instead of the Service Account. So I modified the classifier function to use the Service Account and then it just worked. I verified this using the perfmon counters 'Disk Read IO/Sec' and 'Disk Write IO/Sec' under 'Resource Pool Stats' object.

The question is - how do I get Resource Governor to use a login other than the Service Account (when used with agent jobs)? I do have some untested ugly ideas like - running the jobs using a CmdExec or PowerShell proxy. If anyone has come across a similar situation or has any better ideas, I would really appreciate it. Thank you.

    --Example Setup:
    USE [master]
    GO

    CREATE RESOURCE POOL [SqlJobPool] WITH(
            min_iops_per_volume=1,
            max_iops_per_volume=5000);
    GO

    CREATE WORKLOAD GROUP [IOGroup]
        USING [SqlJobPool];
    GO

    CREATE FUNCTION [dbo].[fn_LimitedIO]()
    RETURNS SYSNAME WITH SCHEMABINDING
    AS
        BEGIN
            DECLARE @grp SYSNAME;

            IF SUSER_NAME() = N'ResourceGovernerUser' -- When this is set to the Service Account, it works
                BEGIN
                    SET @grp = N'IOGroup';
                END
            ELSE
                BEGIN
                    SET @grp = N'default';
                END
            RETURN @grp;
        END
    GO

    ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fn_LimitedIO);
    ALTER RESOURCE GOVERNOR RECONFIGURE;
    GO

    --Within the Agent job
    EXECUTE AS Login = 'ResourceGovernerUser'
    EXECUTE SomeSQLStuff

Resource governor classifier functions only run during the  Login process.  Impersonation via EXECUTE AS does not trigger the classifier function.  EXECUTE AS is how SQL Server Agent runs jobs in the context of another login or user.

Specifying the Run As ... user or modifying the Job Owner does not change how SQL Server Agent logs into SQL Server.  The only way I can get SQL Server agent jobs to run in a specific resource group with a classifier function that looks only at the user name is to put the SQL Server Agent Service Account into it's own resource group.

Since testing is a great way to understand what is really happening, I've created a small test-bed.  Don't run this on a production system since it will modify the resource group configuration.

This creates the classifier function, and several resource groups attached to a test resource pool:

USE master;
SET NOCOUNT ON;
GO
CREATE FUNCTION dbo.fnDummyClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @GroupName sysname = NULL;
    IF SUSER_SNAME() = 'DOMAIN\USER'
        SET @GroupName = N'AgentServiceAccountGroup';

    IF SUSER_SNAME() = 'ResourceGovernorTestLogin'
        SET @GroupName = N'ResourceGovernorTestLoginGroup';

    IF SUSER_SNAME() = 'ResourceGovernorTestUser'
        SET @GroupName = N'ResourceGovernorTestUserGroup';

    IF @GroupName IS NULL
        SET @GroupName =  N'default';

    RETURN @GroupName;
END
GO
CREATE RESOURCE POOL TestPool
WITH (MAX_CPU_PERCENT = 10);
CREATE WORKLOAD GROUP AgentServiceAccountGroup
WITH (
      group_max_requests=0
    , importance=Medium
    , request_max_cpu_time_sec=0
    , request_max_memory_grant_percent=25
    , request_memory_grant_timeout_sec=0
    , max_dop=0
    )
USING TestPool;
CREATE WORKLOAD GROUP ResourceGovernorTestLoginGroup
WITH (
      group_max_requests=0
    , importance=Medium
    , request_max_cpu_time_sec=0
    , request_max_memory_grant_percent=25
    , request_memory_grant_timeout_sec=0
    , max_dop=0
    )
USING TestPool;
CREATE WORKLOAD GROUP ResourceGovernorTestUserGroup
WITH (
      group_max_requests=0
    , importance=Medium
    , request_max_cpu_time_sec=0
    , request_max_memory_grant_percent=25
    , request_memory_grant_timeout_sec=0
    , max_dop=0
    )
USING TestPool;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[fnDummyClassifier]);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Here we'll create a login, and allow it to VIEW SERVER STATE so we can tell which resource group the session has been assigned to.

CREATE LOGIN ResourceGovernorTestLogin
WITH PASSWORD = '!NnrtiHummusPlenumPoodle2'
    , DEFAULT_LANGUAGE = us_english
    , CHECK_EXPIRATION = OFF
    , CHECK_POLICY = OFF;
GRANT VIEW SERVER STATE TO ResourceGovernorTestLogin;
GO

This creates a SQL Server Agent job where the "owner" is set to the login we just created.  Since the login is not a member of sysadmin, SQL Server agent will run this job in the context of that login.

DECLARE @JobID uniqueidentifier;
EXEC msdb.dbo.sp_add_job @job_name = 'TestResourceGovernorJob'
    , @enabled = 1
    , @description = 'Tests resource governor classification'
    , @start_step_id = 1
    , @owner_login_name = 'ResourceGovernorTestLogin'
    , @job_id = @JobID OUTPUT;
EXEC msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = 'Step1'
    , @subsystem = 'TSQL'
    , @command = 'SELECT '''';
SELECT UserName = CONVERT(nvarchar(30), SUSER_SNAME())
    , [SYSTEM_USER] = CONVERT(nvarchar(30), SYSTEM_USER)
    , [SESSION_USER] = CONVERT(nvarchar(30), SESSION_USER)
    , [ORIGINAL_LOGIN] = CONVERT(nvarchar(30), ORIGINAL_LOGIN())
    , WorkloadGroup = CONVERT(nvarchar(30), wg.name)
FROM sys.dm_exec_requests der
    INNER JOIN sys.dm_resource_governor_workload_groups wg ON der.group_id = wg.group_id
WHERE der.session_id = @@SPID;'
    , @flags = 4 --write step output into msdb.dbo.sysjobstephistory
    , @on_success_action = 1;
EXEC msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(LOCAL)';
/*
    Call SQL Server Agent via msdb.dbo.sp_notify_job
    which in turn calls msdb.dbo.xp_notify_job.
    The SQL Server Agent executable logs into the
    SQL Server instance using the service account,
    then performs an "EXECUTE AS ..." to run
    job step(s).
*/
EXEC msdb.dbo.sp_start_job @job_id = @JobID;
GO

WAITFOR DELAY N'00:00:01';
DECLARE @msg nvarchar(max);
SELECT @msg = sjh.message
FROM msdb.dbo.sysjobhistory sjh
    INNER JOIN msdb.dbo.sysjobs sj ON sjh.job_id = sj.job_id
WHERE sj.name = 'TestResourceGovernorJob'
    AND sjh.step_id = 1;
PRINT (N'');
PRINT (@msg);
PRINT (N'');
EXEC msdb.dbo.sp_delete_job @job_name = 'TestResourceGovernorJob';
GO

The results from the msdb history table for the above job:

Job 'TestResourceGovernorJob' started successfully.

Executed as user: ResourceGovernorTestLogin.
-

(1 rows(s) affected)
UserName                       SYSTEM_USER                    SESSION_USER                   ORIGINAL_LOGIN                 WorkloadGroup
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
ResourceGovernorTestLogin      ResourceGovernorTestLogin      guest                          DOMAIN\USER                    AgentServiceAccountGroup
(1 rows(s) affected).  The step succeeded.

Next, we'll create a user for the login, and run a new version of the job using the Run As option in the advanced properties of the SQL Server Job Step.

CREATE USER ResourceGovernorTestUser
FOR LOGIN ResourceGovernorTestLogin;

DECLARE @JobID uniqueidentifier;
EXEC msdb.dbo.sp_add_job @job_name = 'TestResourceGovernorJob'
    , @enabled = 1
    , @description = 'Tests resource governor classification'
    , @start_step_id = 1
    , @owner_login_name = 'ResourceGovernorTestLogin'
    , @job_id = @JobID OUTPUT;
EXEC msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = 'Step1'
    , @subsystem = 'TSQL'
    , @database_name = 'master'
    , @database_user_name = 'ResourceGovernorTestUser'
    , @command = 'SELECT '''';
SELECT UserName = CONVERT(nvarchar(30), SUSER_SNAME())
    , [SYSTEM_USER] = CONVERT(nvarchar(30), SYSTEM_USER)
    , [SESSION_USER] = CONVERT(nvarchar(30), SESSION_USER)
    , [ORIGINAL_LOGIN] = CONVERT(nvarchar(30), ORIGINAL_LOGIN())
    , WorkloadGroup = CONVERT(nvarchar(30), wg.name)
FROM sys.dm_exec_requests der
    INNER JOIN sys.dm_resource_governor_workload_groups wg ON der.group_id = wg.group_id
WHERE der.session_id = @@SPID;'
    , @flags = 4 --write step output into msdb.dbo.sysjobstephistory
    , @on_success_action = 1;
EXEC msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(LOCAL)';
/*
    Call SQL Server Agent via msdb.dbo.sp_notify_job
    which in turn calls msdb.dbo.xp_notify_job.
    The SQL Server Agent executable logs into the
    SQL Server instance using the service account,
    then performs an "EXECUTE AS ..." to run
    job step(s).
*/
EXEC msdb.dbo.sp_start_job @job_id = @JobID;
GO

WAITFOR DELAY N'00:00:01';
DECLARE @msg nvarchar(max);
SELECT @msg = sjh.message
FROM msdb.dbo.sysjobhistory sjh
    INNER JOIN msdb.dbo.sysjobs sj ON sjh.job_id = sj.job_id
WHERE sj.name = 'TestResourceGovernorJob'
    AND sjh.step_id = 1;
PRINT (N'');
PRINT (@msg);
PRINT (N'');
EXEC msdb.dbo.sp_delete_job @job_name = 'TestResourceGovernorJob';

DROP USER ResourceGovernorTestUser;
GO

The output from that run of the job:

Job 'TestResourceGovernorJob' started successfully.

Executed as user: ResourceGovernorTestLogin.
-

(1 rows(s) affected)
UserName                       SYSTEM_USER                    SESSION_USER                   ORIGINAL_LOGIN                 WorkloadGroup
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
ResourceGovernorTestLogin      ResourceGovernorTestLogin      ResourceGovernorTestUser       DOMAIN\USER                    AgentServiceAccountGroup
(1 rows(s) affected).  The step succeeded.

This cleans up the resource governor configuration, and drops the classifier function and the login.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
DROP WORKLOAD GROUP AgentServiceAccountGroup;
DROP WORKLOAD GROUP ResourceGovernorTestUserGroup;
DROP WORKLOAD GROUP ResourceGovernorTestLoginGroup;
DROP RESOURCE POOL TestPool;
ALTER RESOURCE GOVERNOR RECONFIGURE;
DROP FUNCTION dbo.fnDummyClassifier
DROP LOGIN ResourceGovernorTestLogin;
GO

In both result sets above, you can see the Resource Governor classifier function put each job into the AgentServiceAccountGroup group; this is because the SQL Server Agent initially logs into SQL Server using the Service Account used when SQL Server Agent starts up.


How to prevent SQLite from blocking while using R? I am doing an infinity loop to keep gathering data from database db, rework it and send it to another database db1

library(DBI)
library(RSQLite)

while(1==1){

  db = dbConnect(SQLite(), dbname="Data.sqlite",synchronous = NULL)
  db1 = dbConnect(SQLite(), dbname="Data2.sqlite",synchronous = NULL)
  db1
  db

Column1=dbGetQuery(db,'select * from Table1')

  #Here is some calculations#

    Result = Column1/10

  #Here calculations are finished and ready to be sent to new database#

dbWriteTable(db1, name="Result", value=Result, row.names=TRUE, append=TRUE)
   dbDisconnect(db1)
   dbDisconnect(db)
}

when I run the code, it executes about 10 loops and then rise the following Error: Error in rsqlite_send_query(conn@ptr, statement) : database is locked

Can someone please help me to solve this problem

I solved the problem by opening the database from sqlitebrowser and typing the following query there: pragma journal_mode=wal;

for more info: https://www.sqlite.org/wal.html


Subquery on timestampdiff only allows for 1 result, how do I scale this for all results? I'm currently writing up a query which is meant to output data to show curves of power profiles, and I'm facing a slight problem. So far, I've managed to do this:

select datetime, PLANT_STATUS, INCOMER_PWR, PLANT_DEMAND, PLANT_OUTPUT
from d909
where
    (PLANT_STATUS >=400 AND PLANT_STATUS < 500)
    OR
    (timestampdiff(minute,
                   (select min(datetime)
                    from d909
                    where PLANT_STATUS >= 400 AND PLANT_STATUS < 500
                   ),
                   datetime)
         <= -1
     )
     OR
     (timestampdiff(minute,
                    (select max(datetime)
                     from d909
                     where PLANT_STATUS >= 400 AND PLANT_STATUS < 500
                    ),
                    datetime)
         >= 1
     );

How do I get the timestampdiff functions to resolve for ANY data, not just the min and max? I realise subqueries normally have 1 row, but I would like to have multiple rows output from it.

Based on your comment try something like this I think

select datetime, PLANT_STATUS, INCOMER_PWR, PLANT_DEMAND, PLANT_OUTPUT
from d909 as d1
where
    (PLANT_STATUS >=400 AND PLANT_STATUS < 500)
UNION ALL
select datetime, PLANT_STATUS, INCOMER_PWR, PLANT_DEMAND, PLANT_OUTPUT
from d909 as d1
where (select COUNT(*)
        from d909 as d2
        -- find all records within a minute of the first set of records
        where abs(DATEDIFF(MI,d2.datetime,d1.datetime)) <= 1
        -- not sure if this status check required
        AND (PLANT_STATUS >=400 AND PLANT_STATUS < 500)
        ) > 1
AND (PLANT_STATUS >=400 AND PLANT_STATUS < 500)


Is there a way to store a PostgreSQL database directly on a block device (not filesystem)? I am reading about where PostgreSQL stores its data it needs to offer me its service (here: http://www.postgresql.org/docs/8.4/static/storage-file-layout.html )

As it is writen there PostgreSQL's data and storing seems to work on top of the respective filesystem in which I store the PGDATA folder. In this folder again the data is stored in a set of individual files (i.e. also files per database).

Question:
While I can get along with such an "in-filesystem-setup",

I wonder if it is possible to setup PostgreSQL to use directly a block device (like /dev/sda) on for POSIX systems for data storage?

I would think that this would offer some advantages:

the blocksize configuration and optimization to fit database to filesystem needs no longer be undertagen
overhead in the filesystem can be avoided
backup can be simpler

If not for PostgreSQL are there database applications known that do not rely on keeping its data as files filesystem, but use block devices directly?

[Is it] possible to setup PostgreSQL to use directly a block device (like /dev/sda) on for POSIX systems for data storage?

Nope.

PostgreSQL would have to implement its own file system to support this. It lets the operating system do that, on the theory that the OS will probably do it better.

The project tries to avoid duplicating functionality the operating system already provides. That's one of the reasons why it performs as well as it does; when the kernel folks make improvements, PostgreSQL gets them for free.

Of course, when the kernel folks add performance regressions and bugs we get those too, but that's true even if you use raw block devices. We're just exposed to more filesystem and memory management level differences too.

There are a lot more smart kernel hackers with a lot more time than there are PostgreSQL hackers. So letting them deal with the complexities of buffer eviction, NUMA scheduling, dirty page write-back, etc seems like a no-brainer and it's worked out well so far. Mostly.

  If not for PostgreSQL are there database applications known that do not rely on keeping its data as files filesystem, but use block devices directly?

Oracle. It is virtually its own operating system; the Emacs of the database world.

MySQL with InnoDB can create InnoDB segments on raw disk partitions.

I know it's been looked into for SQLite but I don't know if anyone cared enough to finish the work.

  backup can be simpler

I question your assertion that using a raw partition makes backups simpler. I see few advantages and plenty of disadvantages to relying on raw block devices.


Optimize query to filter active IDs for a given day from big table I have a PostgreSQL table called users with 4 columns:

user_id   integer,       -- we can have a lot of the same user_id
name      text,          -- name of the user
value     text NOT NULL, -- enumeration values: ok, not ok, on,off
timestamp timestamp default current_timestamp NOT NULL, -- date of insertion
primary_key(user_id,name)

I have to select distinct user_id where timestamp='date x' and value='on' not having value='off' after. I made this query:

select distinct user_id,name
from users
where (timestamp='date x' and value='on')
and user_id not in (select distinct user_id from users  where value='off')

But it is very slow because there are more than 300K users to handle. Is there a way to make the query faster?

user_id name    value    timestamp
1       mike    not ok   2018-03-27 10:24
1       mike    on       2018-03-27 11:22
1       mike    ok       2018-03-27 13:33
1       mike    off      2018-03-27 15:33

2       joe     not ok   2018-03-28 10:24
2       joe     on       2018-03-28 11:22
2       joe     ok       2018-03-28 13:33

The result wanted is:

  user_id    name
    2         joe

because user_id 1 has a value off in the table.

You could try not exists:

select distinct user_id
from users u1
where u1.timestamp='date x'
  and u1.value='on'
  and not exists (
      select 1
      from users u2
      where u1.user_id = u2.user_id
        and u2.value='off'
  );

If you want more qualified guesses, edit your question with CREATE TABLE statement including keys and indexes.


For "human readable" output, you can use column mode, and turn header output on. That will get you something similar to the sqlplus output in your examples:

sqlite> select * from foo;
234|kshitiz|dba.se

sqlite> .mode column
sqlite> select * from foo;
234         kshitiz     dba.se

sqlite> .headers on
sqlite> select * from foo;
bar         baz         baf
----------  ----------  ----------
234         kshitiz     dba.se


Get INSERT/UPDATE/DELETE statements executed at specified time We are investigating some issues with our production database where some suspicious records inserted/updated in our database. I just wanna know how we will be able to get these TSQL statements executed at a specified time. We are SQL Server 2014 with a FULL recovery option.

Sorry for my ignorance (I am not a DBA and we don't have any).

If there is no auditing enabled to capture CDC(Change Data Capture) and no trigger defined on Insert/Update/Delete statement then, I don't think its possible to get details.

If you enable them now then, you would be able to capture them in the future. There are tonnes of article on how to configure auditing over SQL server. Full recovery can only help you in restoring database upto a specified point in time however that again depends how frequently you are taking log backup.

Hope above helps.


GROUP BY two columns I want to count two columns, but need to get the results of first column in one row.

SELECT country, COUNT(*)
FROM table1
GROUP BY country, type

This query gives me

country    type     COUNT(*)
Canada     first    22
Canada     second   42
Canada     third    15
Australia  second   23
Australia  third    18

but I need to get

country    type_first   type_second   type_third
Canada     22           42            15
Australia  23           18            0

as I want to update another table with these values, and with this row structure, I can update the country table row by row as I get from the above query.

UPDATE country SET first=x, second=x, third=x

NOTE: type column is ENUM with predefined values.

Looks like you want something like:

SELECT country
, sum(case when type = 'first' then 1 else 0 end) as type_first
, sum(case when type = 'second' then 1 else 0 end) as type_second
, sum(case when type = 'third' then 1 else 0 end) as type_third
FROM table1
GROUP BY country


Filter on value in another row Please see image of the data and my code so far.

In this example I only want the first two rows to be retrieved. The reason for this is because where JobID is 1684 the sum of STILL_REQUIRED is > 0

Where JOBID is 1683 the sum of STILL_REQUIRED is 0, so don't show this row.

I think I need to get the sum when it is grouped by JobID, but I cant get it working. I am using Firebird.

Here is the code I have so far:

SELECT *
FROM (SELECT ID,
             JobID,
             NAME,
             ORDERNUM,
             STARTAFTER,
             FINISHBEFORE,
             START_OFFSET_MINS,
             TOTAL_MINUTES_REQ -
             (SELECT COALESCE(
                       SUM(TABLE_PROCESSTIMES.END_TIME -
                           TABLE_PROCESSTIMES.START_TIME) / 60, 0)
                FROM TABLE_PROCESSTIMES
               WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID
                  AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
             ) AS STILL_REQUIRED
FROM TABLE_PROCESSES)
WHERE STILL_REQUIRED > 0

Based on my understanding of your needs, you could use a Common Table Expression (CTE) to achieve this. Define your main query within the CTE and then do a JOIN on a second SELECT which calculates the total per JobID:

WITH results AS
(
    SELECT ID,
           JobID,
           NAME,
           ORDERNUM,
           STARTAFTER,
           FINISHBEFORE,
           START_OFFSET_MINS,
           TOTAL_MINUTES_REQ -
           (SELECT COALESCE(
                     SUM(TABLE_PROCESSTIMES.END_TIME -
                         TABLE_PROCESSTIMES.START_TIME) / 60, 0)
              FROM TABLE_PROCESSTIMES
             WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID
               AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
           ) AS STILL_REQUIRED
      FROM TABLE_PROCESSES
)
SELECT x.*
  FROM results x
  JOIN (SELECT JobID, SUM(STILL_REQUIRED) AS STILL_REQUIRED
          FROM results
      GROUP BY JobID) y
    ON x.JobID = y.JobID
 WHERE y.STILL_REQUIRED > 0


how do I use sa to access local sql server database with jdbc I have a new local sql server database, running hypothetically on port 48000.  In SQL Server Management Studio under the name of the computer\MSSQLSERVER01, Security, Logins I clicked sa, properties and set the password.  I have created a database, mystuff.  sa is not listed under security under mystuff.  I have the connection string
jdbc:sqlserver://MYDESKTOP\MSSQLSERVER01:48000;databasename=mystuff;user=sa;password=123
but I get "Login failed for user 'sa'" when I attempt to connect.  What else do I need to do to setup sa?  Something under mystuff?

You should not be connecting any application to your database installation using the installation owner account ("sa").  Instead, create an account dedicated for the use of your Application and grant that account the right privileges.
Why?
Always keep the biggest and best tools in the box for yourself, because it's your job to clean up the "mess" made by everyone (and everything) else!
If an application connects with an "overpowered" account, like "sa"' and that application gets hacked, then your database can be wrecked in all sorts of nasty, painful and expensive to recover ways!
Save yourself the grief.


What exactly does "No Join Predicate" mean in SQL Server? MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate".

But unfortunately it does not seem to be as easy as that.

For example, very simple situation:

create table #temp1(i int);
create table #temp2(i int);
Select * from #temp1, #temp2 option (recompile);

There is no data in the tables, there is no warning either, although it obviously has no join predicate.

If I take a look at documentation for SQL Server 2005 (the same link, just other server version), there is an extra sentence: "This event is produced only if both sides of the join return more than one row." This would make perfect sense in the previous situation. There is no data, so both sides returns 0 rows and no warning. Insert rows, get warning. Ok, cool.

But for the next confusing situation, I insert the same values in both tables:

Insert into #temp1 (i) values (1)
Insert into #temp1 (i) values (1)
Insert into #temp2 (i) values (1)
Insert into #temp2 (i) values (1)

And I get:

-- no warning:
Select * from #temp1 t1
    inner join #temp2 t2 on t1.i = t2.i
option (recompile)
-- has warning:
Select * from #temp1 t1
    inner join (select 1 i union all select 1) t2 on t1.i = t2.i
option (recompile)

Why is this so?

Note: some scripts I used to detect these bad queries on my server.

of course, execution plan of procedures
used default server trace to find warnings

Declare @trace nvarchar(500);
Select @trace = cast(value as nvarchar(500))
From sys.fn_trace_getinfo(Null)
Where traceid = 1 and property = 2;

Select t.StartTime, te.name, *
From sys.fn_trace_gettable(@trace, 1) t
    Inner join sys.trace_events te on t.EventClass = te.trace_event_id
    where EventClass = 80
order by t.StartTime desc

execution plan cache, to find those plans with warnings (like this one)

WITH XMLNAMESPACES (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    Cast('<?SQL ' + st.text + ' ?>' as xml) sql_text,
    pl.query_plan,
    ps.execution_count,
    ps.last_execution_time,
    ps.last_elapsed_time,
    ps.last_logical_reads,
    ps.last_logical_writes
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
WHERE pl.query_plan.value('(//Warnings/@NoJoinPredicate)[1]', 'bit') = 1
Order By last_execution_time desc
OPTION (RECOMPILE);

Your question is similar to this one. Sometimes SQL Server can remove a join predicate from the original query.

In the case where you see a join predicate warning SQL Server detects at compile time that the table of constants only has one distinct value and that value is 1 so rewrites the query as

SELECT *
FROM   (SELECT *
        FROM   #temp1 t1
        WHERE  t1.i = 1) t1
       CROSS JOIN (SELECT 1 i
                   UNION ALL
                   SELECT 1) t2

There is a predicate on the table scan of #temp as follows [tempdb].[dbo].[#temp1].[i] =(1)

The join predicate of on t1.i = t2.i can't be removed in this way at compile time when using two tables or if the table of constants contains more than one distinct value.

More info about this can be found in Paul White's Query Optimizer Deep Dive series.


created a user with clusterMonitor and dbOwner role, but db.getRoles() shows enableSharding role I created a user with full privileges in it's own database (dbOwner) and read-only access to administrative commands (clusterMonitor)

use customerdb
(mongod-3.4.7) customerdb> db.createUser( { user: "customer",
...                  pwd: "customerpw",
...                  roles: [ { role: "clusterMonitor", db: "admin" },
...                           { role: "dbOwner", db: "customerdb" }] },
...                { w: "majority" , wtimeout: 5000 } )
Successfully added user: {
  "user": "customer",
  "roles": [
    {
      "role": "clusterMonitor",
      "db": "admin"
    },
    {
      "role": "dbOwner",
      "db": "customerdb"
    }
  ]
}

Enabled auth and logged in with the new user. It's a Homebrew installed MongoDB single instance with latest release.

$ mongo -u customer -p customerpw localhost --authenticationDatabase=customerdb

Why getRoles() shows me enableSharding role? I didn't found explanation in docs

> db.getRoles(
...     {
...       rolesInfo: 1,
...       showPrivileges:false,
...       showBuiltinRoles: true
...     }
... )
[
  {
    "role": "dbAdmin",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ]
  },
  {
    "role": "dbOwner",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ]
  },
  {
    "role": "enableSharding",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ]
  },
  {
    "role": "read",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ]
  },
  {
    "role": "readWrite",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ]
  },
  {
    "role": "userAdmin",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ]
  }
]

the privleges for enableSharding role

  {
    "role": "enableSharding",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ],
    "privileges": [
      {
        "resource": {
          "db": "",
          "collection": ""
        },
        "actions": [
          "enableSharding"
        ]
      }
    ],
    "inheritedPrivileges": [
      {
        "resource": {
          "db": "",
          "collection": ""
        },
        "actions": [
          "enableSharding"
        ]
      }
    ]
  }

I tested this in a sharded cluster on mongos with version:

MongoDB Enterprise mongos> db.version()
3.2.11

and also on MacBook with single mongod and version 3.4.7

I guess I am doing something wrong how I create users and grant roles?

You get that "role" : "enableSharding" for customerdb because of "role" : "dbAdmin". So, that use can shard customerdb, but nothing else.


Do non-relevant columns affect query time of select statements? I'm just curious.

Say you have a table of 1 million records/ rows.

select order_value from store.orders

Does it make a difference whether that table has 1 field, 2 fields, or 100 fields, in actual query time? I mean all fields other than "order_value."

Right now I'm pushing data to a data warehouse. Sometimes I dump fields into the table that "may be used in the future, someday" - but they aren't being queried right now, by anything. Would these 'extraneous' fields affect select statements that do no include them, directly or indirectly (no * I mean)?

It depends, on the table structure and the available indexes.

Case A: Common (rowstore) table, no index on (order_value).

The only possible execution plan is to read the whole table (which is of course much different when it's 2 vs 200 columns, so a few vs a few thousand bytes wide).
Case B: Common table, there is an index on (order_value) or some other indexes that include that column.

There is a better plan now, scan the whole index (one of them) - which is of course much more narrow than the whole table, just a few bytes. Which makes irrelevant if the table has 2 or 200 columns. Only the index is scanned.
Case C: It's a columnstore table.

As the name implies, the structure of these tables are column-wise oriented, not row-wise. There is no need for any index, the table design itself is suited for reading entire columns.


SSIS File System Task Copy File Could not find part of the path error I have created an SSIS package that uses a File Systen Task (copy) that copies a file from 1 folder to another. The package runs fine when I run it from my local computer but when I import the package into our SQL Server and Create a Job to run the Package, the package fails and I see the following error in the log:

Started:  3:20:01 PM
Error: 2013-03-27 15:20:01.70
Code: 0xC002F304
Source: copy template File System Task
Description:
An error occurred with the following error message:
    "Could not find a part of the path
    'P:\IT\PROVIDERS\PROVIDERS AUDIT\File
    Template\QnxtProviderAuditList_yyyymmdd.xlsx'."
End Error
DTExec:
The package execution returned DTSER_FAILURE (1).
Started:  3:20:01 PM
Finished: 3:20:01 PM
Elapsed:  0.452 seconds.
The package execution failed.  The step failed.

The file path in the error is the file that needs to be copied (sourceconnection).

I can copy the file path directly from the error and go to it (via windows explorer) on the physical SQL Server machine so I do not understand why the package is saying it could not find the Path.

Does anyone have any idea why this package would run fine on my local machine but fails with the above error when run from the SQL Server? P:\ is a shared drive which the SQL Server has access to so I doubt it's an access/privileges issue.

Mapped drives, like P:\ in your case are dependent on the user logged in.  Just because you can see the drive letter when logged into the server machine does not mean SQL Server can "see" the drive letter.  If you specify the UNC (Universal Naming Convention) name in the path instead of using the drive letter, and the account SQL Server is using has access to the share and path, then it will work.

Instead of P:\mypath\myfile.txt use \\machine\share\mypath\myfile.txt


Order by and then Concat I have a table table1 like:

col1  col2

A1    A2
A1    B1
A1    B2
A2    A1
A2    B1
A2    B2
B1    A1
B1    A2
B1    B2
B2    A1
B2    A2
B2    B1

I need to concat col1 and col2 such a way that it should be sorted first.

Expected result:

col1  col2  ConcatResult

A1    A2    A1-A2
A1    B1    A1-B1
A1    B2    A1-B2
A2    A1    A1-A2
A2    B1    A2-B1
A2    B2    A2-B2
B1    A1    A1-B1
B1    A2    A2-B1
B1    B2    B1-B2
B2    A1    A1-B2
B2    A2    A2-B2
B2    B1    B1-B2

One more requirement: if possible, I would like to select the distinct from ConcatResult column.

You can do

SELECT col1, col2,
       CONCAT(LEAST(col1, col2), '-',
              GREATEST(col1, col2)) concat_result
  FROM table1
 ORDER BY concat_result

Output:

| COL1 | COL2 | CONCAT_RESULT |
|------|------|---------------|
|   A1 |   A2 |         A1-A2 |
|   A2 |   A1 |         A1-A2 |
|   B1 |   A1 |         A1-B1 |
|   A1 |   B1 |         A1-B1 |
|   A1 |   B2 |         A1-B2 |
|   B2 |   A1 |         A1-B2 |
|   A2 |   B1 |         A2-B1 |
|   B1 |   A2 |         A2-B1 |
|   A2 |   B2 |         A2-B2 |
|   B2 |   A2 |         A2-B2 |
|   B1 |   B2 |         B1-B2 |
|   B2 |   B1 |         B1-B2 |

To get distinct values from concatenated result

SELECT DISTINCT
       CONCAT(LEAST(col1, col2), '-',
              GREATEST(col1, col2)) concat_result
  FROM table1
 ORDER BY concat_result

Output:

| CONCAT_RESULT |
|---------------|
|         A1-A2 |
|         A1-B1 |
|         A1-B2 |
|         A2-B1 |
|         A2-B2 |
|         B1-B2 |

Here is SQLFiddle demo


Evict node from SQL Server 2016 Cluster on Windows Core I have 4 nodes cluster on Windows Core with SQL Server 2016 installed on them (4 instances, 1 instance on each node).

I would like to remove 2 nodes from the cluster (2 instances aren't needed).

I can't run the SQL Server SETUP.exe utility in order to Choose "Remove node from a SQL Server failover cluster".

I can open Failover Cluster Manager on another server remove it from there, OR use WAC (Windows Admin Center) to perform the action.

However, most of the links I read about evicting SQL Cluster nodes are using that GUI utility (Setup.exe)

Will evicting via the WAC/Failover Cluster manager without the 'Setup.exe' step  will hurt the cluster's validity ?
What is the correct way to perform it ?

Many thanks,
Roni.

Use the command-line parameters for setup.exe on Windows Core.  The details can be found at Microsoft Docs.  Using the command-line parameters allows use of the setup.exe without requiring the GUI pieces, which are problematic on Windows Core.

From that page:

  Use the parameters in the following table to develop command-line scripts for RemoveNode. To uninstall a failover cluster, you must run RemoveNode on each failover cluster node. For more information, see Always On Failover Cluster Instances (SQL Server).

A simple example, taken from the page linked, is:

setup.exe /q /ACTION=RemoveNode /INSTANCENAME="<Insert Instance Name>" [/INDICATEPROGRESS] /CONFIRMIPDEPENDENCYCHANGE=0


Understanding SQL Developer user accounts I have installed this Docker image today: https://hub.docker.com/_/oracle-database-enterprise-edition
I followed the instructions here: https://www.youtube.com/watch?v=ciYsDbBx80s.  Basically I ran the following commands:
docker pull store/oracle/database-enterprise:12.2.0.1
docker run -d -p 1521:1521 --name oracle store/oracle/database-enterprise:12.2.0.1
docker exec -it 0500ab7ee75c bash -c "source /home/oracle/.bashrc; sqlplus /nolog"

Then in sqlpus:
connect sys as sysdba
Enter password: anyrandompassword
alter session set "_ORACLE_SCRIPT"=true;
create user dummy identified by dummy;
SQL> GRANT ALL PRIVILEGES TO dummy;
SQL>ALTER USER sysdba IDENTIFIED BY Password1 ACCOUNT UNLOCK;

I have two questions:

Why is the SQLDeveloper password for the dummy user: dummy.  I have not specified this.

It appears that I can now login to SQL Developer with the following credentials (as described here: https://stackoverflow.com/questions/9129664/unable-to-log-in-to-database-as-sys-with-oracle-sql-developer):
Username: SYS AS SYSDBA
Password: Password1

However, when I run the following command in sqlplus
connect sys as sysdba

I am then prompted for a password, however any password appears to work.

Yes, you did:

create user dummy identified by dummy;

By default, for local logins, Oracle databases use OS authentication based on OS user group membership. Any user who is a member of the dba group or whatever group was specified as the OSDBA group can log in locally as sysdba without providing a password (or providing any password).

I recommend that you forget this part:
alter session set "_ORACLE_SCRIPT"=true;
create user dummy identified by dummy;

Tutorials with this workaround (alter session set "_ORACLE_SCRIPT"=true;) are the result of laziness or lack of knowledge about the CDB architecture introduced in Oracle 12c. That is how incorrect "solutions" spread.
The author forgot to tell you that by doing the above, you break a basic principle of the CDB architecture (because you create a new user that is treated as built-in Oracle-managed users + your data in the future should be stored in pluggable databases, not the root container). That is not how a container database is used.
Instead of doing the above, after getting the SQL prompt with this docker exec -it 0500ab7ee75c bash -c "source /home/oracle/.bashrc; sqlplus /nolog", you should follow as below.
Connect to the database instance, below connects to the root container of the database. Do not bother with a password at this point:
connect / as sysdba

List pluggable databases, you should see a pluggable database named ORCLDB1.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO
SQL>

Connect to that pluggable database:
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1
SQL>

And create the user there:
SQL> create user dummy identified by dummy;

User created.

SQL> GRANT ALL PRIVILEGES TO dummy;

Grant succeeded.

SQL>

Now you can connect with the dummy user, which is in the ORCLPDB1 pluggable database, and not in the root container. For that, use orclpdb1.localdomain as service name and not ORCLCDB.localdomain.


Updateable Subscriptions: Rows do not match between Publisher and Subscriber I have transactional replication with updatable subscribers set up in SQL Server 2008. It has 3 subscribers and 1 publisher. I had to setup replication again due to some errors related to the database and the application which uses the database. However, now I run into issues when I try updating a section in my application. It does not go through the with update and gives the following error:

  Execption Message: Updateable Subscriptions: Rows do not match between
  Publisher and Subscriber. Run the Distribution Agent to refresh rows
  at the Subscriber. Updateable Subscriptions: Rolling back transaction.
  The transaction ended in the trigger. The batch has been aborted.
  Exception Triggered by Method: OnError

The update statement obviously doesn't go through. However, when I try it the second time it works. Replication is working. Everything seems to be replication.

Can anyone explain why this error would occur and how I can resolve this issue. I would really appreciate the help!...

Several years ago Hilary Cotter responded to a similar problem.  In his post he mentioned that synchronous replication is more reliable than asynchronous replication.

Rows Do Not Match Between Publisher and Subscriber

Part of Hilary's response is included:

  Your chances of having this work successfully would be better if you did a
  sync subscription (i.e. the subscriber does not have the schema and
  data in place option).

  I would advise you to drop and recreate the subscription and push it
  again, again making sure that nobody else was working on the
  publication database while you are doing this.

The poster of the question responded to this direction with:

  I have since dropped and re-created the table at the subscriber that
  wasn't updating properly and this seems to have cured the problem. ...
  I can only guess that something had gone awry in the system tables
  regarding this object.

The poster also noted that that he was using asynchronous replication and noted that it was extra work to tear down and recreate the affected referential integrity.

Nonetheless, that apparently got his replication running again.


Unable to Restore a Database to a previous point in time from a Full Backup Set I have a full backup set (SQL server) and I am trying to restore the Database to a previous date and time. Every time I restore it (To another database) selecting a previous date that I want from the backup set, its RESTORES the latest version of the data. I can still query for data after the point in time.
All I want to for the backup to stop at the point in time indicated (see image)

Is there something that i am missing?

DB: SQL server 2000

Tool used: Enterprise manager

You didn't say what backups you have. If you only have a full backup, then there's no point-i-time restore. It can only be restored to the "snapshot in time" it reflects (towards the end of the backup period).

If you do have also transaction log backups, then I agree that you should test SSMS and see what RESTORE commands it generates (you have a script button to see the TSQL) and then take it from there.


SQL Server 2014 SP2 CU10 Patch uninstall stuck at DowntimeTimingConfigAction SQL Server 2014 SP2 CU10 Patch uninstall stuck at DowntimeTimingConfigAction.

It has been at the step
"Updating '[Instance Name]': DowntimeTimingConfigAction" for almost 2 hours.

What is DowntimeTimingConfigAction?
and at what point should I call it a lost cause?

I can't find much about it anywhere.

So I figured this out, Posting an answer to help out anyone else who is stuck.

When SQL updates a set of scripts is run on the system databases to make sure everything is set up right for the new version of SQL. When you uninstall an update it has to do the same thing but backwards.

In this case the backward scripts for 2014 SP2 CU10 back to base 2014 SP2 do not stop SQL correctly. So you just need to make sure to stop the SQL service first before running the uninstaller.

So to do this downgrade follow these steps for best results:

1. Restart the server, (make sure there are no other windows updates pending)
2. Stop the SQL Server Services
3. Uninstall the SQL Update
4. Start the SQL Server Services (start the 2ndary services first like Agent and SSRS first)
5. SQL will then run the downgrade scripts and will be happy after 5-10 minutes.
6. Restart the server again for good measure.

If your stuck at the place I was the way to get out of it is:

1. Open SQL server configuration manager
2. Open the proprieties of the SQL Server Service
3. Go to the Startup Parameters, add "-902" startup flag
4. Restart the server
5. Wait for the server and SQL to come fully back up
6. Remove the startup flag
7. Restart the SQL Server Service

Then Follow the first set of steps to fully remove the update.


Database Collation problem when comparing two databases I have changed the collation of test database using the follwoing query:

USE master;
GO
ALTER DATABASE test
COLLATE SQL_Latin1_General_CP1_CI_AI ;
GO

--Verify the collation setting.
SELECT name, collation_name
FROM sys.databases
WHERE name = N'test';
GO

Now when I want to compare a columns of test database against test2 database, I receive the following error:

  Cannot resolve the collation conflict between
  "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in
  the equal to operation.

I have checked that both databases has SQL_Latin1_General_CP1_CI_AI collation in :

Management studio > Database > properties > options > collation.

Is there another option rather than the database itself (e.g. schema) which still holds the old collation?

First: Collations for columns are stored per-each column.

Second: The ALTER DATABASE ... COLLATE ... statement changes only the default collation of the database itself. It does not change the collation of any existing columns within the database. This change affects:

database-level meta-data (mostly names of database-level objects: tables, columns, procedures, functions, triggers, indexes, users, views, constraints, etc, etc) except in Contained databases (where the DB-level meta-data is always Latin1_General_100_CI_AS_KS_WS_SC)
new string columns that do not specify a COLLATE clause. New via:

CREATE TABLE ...
ALTER TABLE ... ADD
DECLARE @TableVariable TABLE ... (these default to the database's collation, not [tempdb]'s collation)

altered string columns that do not specify a COLLATE clause (i.e. ALTER TABLE ... ALTER COLUMN ...)
new or altered columns that do specify COLLATE using DATABASE_DEFAULT
string literals and variables

For an in-depth look at changing database-level (and even instance-level) collations, please see my post:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

For more info on working with collations in general, please visit: Collations Info


Deleting a Column from a Table in Production We have a situation where we need to change the relationship between 2 tables from m:1 to m:n.

So, we need to create a Cross-reference table in between those two tables.

After migrating all existing data from the "child" table into the Cross-reference table, would it be a bad idea to delete the original foreign key column in the child table?

If we leave it there, we basically have technical debt. But I'm not a dba and don't have a good grasp of the implications of deleting a column from a table. (I know it is possible, but is it a bad idea? Will my database hate me for it?)

Thanks

Without knowing all of the structure of your tables I am limited in my advice. However, no, your database will not plot your demise if your remove a column under the following circumstances (by no means exhaustive):

You still use a database key to map your dimensions.
Your new indices on this new Dimension table are properly covering indices when they should be.
You manage this number of indices so as not to overburden Insert/Updates

Your new design has Two Dimension Tables and a Fact table

This is why it went from m:1 to m:n with a "cross reference" table. We call this another dimension.

Design actually implemented Normalization to achieve this

By removing a dependency, your team will be better equipped to retrieve Facts that can transform how your data is processed in a more meaningful way.

Note on Dimensions and Facts

Dimensions for Descriptive Context

Dimensions provide the “who, what, where, when, why, and how” context surrounding a business process event. Dimension tables contain the descriptive attributes used by BI applications for filtering and grouping the facts. With the grain of a fact table firmly in mind, all the possible dimensions can be identified.
Whenever possible, a dimension should be single valued when associated with a given fact row. Dimension tables are sometimes called the “soul” of the data warehouse because they contain the entry points and descriptive labels that enable the DW/BI system to be leveraged for business analysis. A disproportionate amount of effort is put into the data governance and development of dimension tables because they are the drivers of the user’s BI experience.

Facts for Measurements

Facts are the measurements that result from a business process event and are almost always numeric. A single fact table row has a one-to-one relationship to a measurement event as described by the fact table’s grain. Thus a fact table corresponds to a physical observable event, and not to the demands of a particular report. Within a fact table, only facts consistent with the declared grain are allowed. For example, in a retail sales transaction, the quantity of a product sold and its extended price are good facts, whereas the store manager’s salary is disallowed.
Kimball Dimensional Modeling Techniques

My suggestion is the design team should know that enforcing rules in the database is best, unless it hurts performance. I do not know the size or quantification of your DDL statements to fully answer this, though.
But rest assured this should be a positive change to your system as now SQL Server will not have to go through all that extra data to retrieve what actually mattered.


redshift drop table with + and spaces in name I have a table that a user created which appears to be from a python variable that never interpolated.

afsproddb01=# \dt+ *transtable*
                    List of relations
 schema |      name       | type  | owner  | description
--------+-----------------+-------+--------+-------------
 public |  + transtable + | table | usiapp |
(1 row)

I am unable to drop it, I can only describe it with the \dt+ and wildcard method.  Can anyone point me in the right direction?

I was able to answer my own question, a co worker was able to see a space in front of the first plus sign. I was not seeing this, so quoted drop worked.

afsproddb01=#drop table " + transtable +";
DROP TABLE


Modeling a graph of different node types and edge types using SQL Graph I am looking into using SQL Server 2017's SQL Graph feature to model a graph that has different node types and edge types, but am confused as to how I would model this. In short, a user can create a project that is a collection of connected nodes. There are different types of nodes, where each type of node has some attributes in common (say 5 or so), but has many unique attributes (say 10-25).

Moreover, the nodes are connected by edges, but there are different types of edges that can be used to make the connection, where (like nodes) each type of edge has a few attributes in common, but many more unique attributes.

For simplicity's sake, imagine that there is just one type of edge and only two types of nodes: NodeA and NodeB. My initial thought is that I'd:

Create two node tables
Create one edge table

E.g.:

NodeA (AS NODE)
---------------
ID, PK
ProjectID, FK
NodeA_Attribute1, string
NodeA_Attribute2, string
...

NodeB (AS NODE)
---------------
ID, PK
ProjectID, FK
NodeB_Attribute1, string
NodeB_Attribute2, string
...

Edge (AS EDGE)
--------------

If the user has a project that has two nodes - one NodeA and one NodeB that are connected, then the INSERT statements would be like so:

INSERT INTO NodeA(ID, ProjectID, Attribute1, Attribute2) VALUES(1, 123, 'I'm a NodeA', '...')
INSERT INTO NodeB(ID, ProjectID, Attribute1, Attribute2) VALUES(1, 123, 'I'm a NodeB', '...')
INSERT INTO Edge VALUES((SELECT $node_id FROM NodeA WHERE ID = 1), (SELECT $node_id FROM NodeB WHERE ID = 1))

But here is where I am lost... How do I reconstruct the graph for this particular project? I could query each possible node table, e.g.:

SELECT * FROM NodeA WHERE ProjectID = 123
SELECT * FROM NodeB WHERE ProjectID = 123

But that doesn't seem practical or efficient. My project in reality has ~30 different types of nodes, so running 30 SELECTs (and having to add new SELECTs if a new node type gets added) seems incorrect.

It feels like I'm missing something fundamental here. Is my proposed design incorrect? Are there different ways of querying the SQL Graph that I'm overlooking?

Thanks

How do I reconstruct the graph for this particular project?

To send data from SQL Server to the client, you use TDS, which means one result set per table "shape", or use JSON/XML encoded in a unicode string.

So yes, if you needed all the details for each node type, you would have one query per node type.  You can join the different tables together into fewer queries (as in EF Eager Loading), but that doesn't really improve the efficiency.


LogShipping frequency for backup/copy/restore I wish to create a LogShipping job that runs every 5 minutes. What is the best way to do this so that the backup/copy/restore does not collide?

Maybe Sql server knows how to handle this, but I was concerned if I put 5 mins into all of the scheduled times, that I would get some sort of conflict.

i.e. would this create a problem?

Primary server: starts backup at 9am
Secondary server: starts copy at 9am
Secondary server: starts restore at 9am

Would staggering them be a better idea?

Primary server: starts backup at 9:00am
Secondary server: starts copy at 9:01am
Secondary server: starts restore at 9:02am

The database is only approx 200mb in backup size and the two sql servers are running on the same local network.

This doesn't really solve the above problem, but I ended up using Transaction Replication and I didn't need to worry about the above complicated timing issues.


How do I generate a Loader_Generate_Script for all states and regions? I'm using PostGIS and I'm wondering how do I generate a loader script for all states that will load all of the census dataset.

All of this is stored in tiger.state

SELECT stusps,name,division,statefp
FROM tiger.state
ORDER BY name;

The script expects it's arguments in the form of the two character stusps code,

\t
\a
\o /gisdata/statescript.sh
SELECT Loader_Generate_Script(ARRAY['AL' , 'AK' , 'AS' , 'AZ' , 'AR' , 'CA' , 'CO' , 'MP' , 'CT' , 'DE' , 'DC' , 'FL' , 'GA' , 'GU' , 'HI' , 'ID' , 'IL' , 'IN' , 'IA' , 'KS' , 'KY' , 'LA' , 'ME' , 'MD' , 'MA' , 'MI' , 'MN' , 'MS' , 'MO' , 'MT' , 'NE' , 'NV' , 'NH' , 'NJ' , 'NM' , 'NY' , 'NC' , 'ND' , 'OH' , 'OK' , 'OR' , 'PA' , 'PR' , 'RI' , 'SC' , 'SD' , 'TN' , 'TX' , 'VI' , 'UT' , 'VT' , 'VA' , 'WA' , 'WV' , 'WI' , 'WY'], 'sh');

(the method of \t \a \o was borrowed from PostGIS in Action Second Edition)


Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty? Setup

DROP TABLE IF EXISTS #EmptyTable, #BigTable

CREATE TABLE #EmptyTable(A int);
CREATE TABLE #BigTable(A int);

INSERT INTO #BigTable
SELECT TOP 10000000 CRYPT_GEN_RANDOM(3)
FROM   sys.all_objects o1,
       sys.all_objects o2,
       sys.all_objects o3;

Query

WITH agg
     AS (SELECT DISTINCT a
         FROM   #BigTable)
SELECT *
FROM   #EmptyTable E
       INNER HASH JOIN agg B
                    ON B.A = E.A;

Execution Plan

Problem

This is a simplified repro for a phenomenon I hadn't noticed before today. My expectation for an inner hash join would be that if the build input is empty the probe side should not be executed as the join can return no rows. The above example contradicts that and reads the 10 million rows from the table. This adds 2.196 seconds to the execution time of the query (99.9%).

Additional Observations

With OPTION (MAXDOP 1) the execution plan reads no rows from #BigTable. The ActualExecutions is 0 for all operators on the inside of the hash join.
For the query SELECT * FROM #EmptyTable E INNER HASH JOIN #BigTable B ON B.A = E.A- I get a parallel plan, the scan operator on the inside of the hash join does have ActualExecutions of DOP but still no rows are read. This plan has no repartition streams operator (or aggregate)

Question

What's going on here? Why does the original plan exhibit the problem and the other cases don't?

Not running the probe side of the join when the build is empty is an optimization. It is not available for parallel row mode hash join when the probe side has a child branch i.e. when there is an exchange operator.

There was a similar report many years ago by Adam Machanic on the now-defunct Connect feedback site. The scenario was a start-up Filter on the probe side, which ran its child operators unexpectedly. The answer from Microsoft was that the engine requires a guarantee that certain structures are initialized, and the only sane way to enforce that was to ensure the probe side operators are opened.

My own recollection of the details is that not initializing a sub-tree led to hard-to-fix parallel timing bugs. Ensuring the child branch started up was a work around for those problems.

Batch mode hash join does not have this side effect because the way threads are managed is different.

In your particular case, the effect is more pronounced because the hash aggregate is blocking; it consumes its entire input during the iterator's Open() call. When there are only streaming operators on the probe side, the performance impact will often be more limited, depending on how much work is required to return the first row to the probe side of the hash join.


TEMPDB File Growth with Multiple Files There are a lot of blog postings about splitting tempdb into multiple files, and I understand the logic and reasoning behind this, but there is one question that never seems to get asked about a multi-tempdb architecture. TempDB file growth and disk-space consumption.

The argument is always to grow tempdb files at the same rate to prevent undoing the benefits (and I may be about to answer my own question here).

Does this mean that a query when processed cannot make use of all the tempdb files? If I had a single tempdb of 100MB and most queries needed 75% of this file to process.

Then I decided to split the tempdb into 4 logical files of 25MB each (giving me the same 100MB in total). If a query cannot work with mutliple files at the same time, then it won't take long before all the tempdb files are grown to 75-100MB each and I've effectively quadrupled the space requirements.

And the more files you add to tempdb the more overall disk-space you have to commit. IS that correct or does the query processor use any combination of files if needed?

Answer to your question will depend on the answer to the following.

Most queries need 75% meaning 75MB in your case.

Do you mean all queries combined need 75MB?
Each of those queries need 75MB individually?

If all queries combined need 75MB then you can have multiple smaller files and you will not increase overall space because those files should not grow.

If each query needs 75MB then are they running in parallel or serial? If parallel you must be using more than 100MB today.  If serial than I do not see why you need multiple tempDB files.

Are you seeing contention on PFS and SGAM pages?

I suggest you review these articles regarding your question.

https://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/
https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d


Master mysq8 slave Mariadb 10.3 Error 22 Master (mysql  Ver 8.0.16)
Slave (mysql  Ver 15.1 Distrib 10.3.15-MariaDB)

I have this error

  Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file' on query. Default database: 'test.database'. Query: 'BEGIN'

    MASTER my.cnf
    [mysqld]
    collation-server = utf8_unicode_ci
    character-set-server = utf8
    [client]
    default-character-set=utf8

    SLAVE my.cnf
    [client]
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    character-set-server = utf8mb4

The Master should not be a newer version than the Slave(s).  In this case, 8.0 has a lot of new collations that do not exist in 5.7.  And the defaults pull you toward them.

Plan A:  Change the collations in the Master.
Plan B:  Upgrade the Slave to 8.0.

Meanwhile, suggest filing a bug at bugs.mysql.com pointing out how awkward it is to upgrade a Master-Slave setup with 8.0 calling for a collation that does not exist in 5.7.


Strange MySQL replication error 1146 (Table doesn't exist) I'm getting a very weird MySQL replication error No. 1146 for a REPLACE INTO query on a slave host replicating all tables in all databases from a master, and I'm having a bit of a hard time understanding why.

Here's my scenario:

New data is generated solely on the master server, MySQL 5.5.40.
Slave A, MySQL 5.5.38, has been replicating all tables in all databases from this master just fine for a long while, producing no errors of any kind.
IO_THREAD was paused on slave A. Value of Relay_Master_Log_File was confirmed to match that of Master_Log_File and value of Exec_Master_Log_Pos was confirmed to match that of Read_Master_Log_Pos.
A FLUSH TABLES WITH READ LOCK was issued on slave A and a dump of all db's was subsequently produced from it with mysqldump -v -h localhost -u root -p --all-databases --opt --single-transaction --hex-blob --no-autocommit > dump.sql. Lock was released only after the dump completed, and then the slave IO_THREAD was restarted; replication from master resumed without any problems, and continues to run smoothly to this day on slave A.
The dump was transferred to slave B, MySQL 5.5.34, and loaded successfully into it through a simple mysql -h localhost -u root -p < dump.sql command, after confirming non of the target databases existed on this second host (and actually no db's other than the mysql db and information & performance schemas). I can also confirm integrity of the dump file after the transfer to slave B thanks to matching RMD160 checksums for it on both hosts.
Slave B was pointed to the master server for replication, with MASTER_LOG_FILE and MASTER_LOG_POS coordinates set to the values of Relay_Master_Log_File and Exec_Master_Log_Pos recorded in step 3 above from slave A, respectively.
Replication was started on slave B, and data started flowing in just fine.

However, after about a day of smooth operation, slave B produced the following error in its SQL_THREAD:

Error 'Table 'knet.course_location_tracks' doesn't exist' on query. Default database: 'knet'. Query: 'REPLACE INTO `course_location_tracks` (`userid`,`courseid`,`lesson_location`,`datestamp`) VALUES (val1,val2,val3,val4)'

(actual row values have been redacted out)

I can't make much sense of this error because I can confirm not only that the knet.course_location_tracks table exists on slave B, but also that its definition is identical to that of the corresponding table on slave A. Slave A, as I pointed out above, continues to replicate from master just fine to this day, without any problems of any kind.

If replication started fine on slave B, leading me to believe that initial replication coordinates were calculated correctly for it off of the state of slave A at the point of the dump, why am I then getting this error for a table that does exists on the host? Moreover, why am I getting the error on slave B while slave A is still replicating smoothly?

Other than having mismatching MySQL versions across my three hosts (something I only noticed recently in trying to debug the problem, and that I will see to correct ASAP), what could I be doing wrong?

And, finally, once the problem is determined and corrected, how could I get slave B to resume replication at the correct point so that it is again fully in sync with master?

Thanks in advance for any help!

PS: If it matters, replication on slave A was initially set up by transferring all db's & tables in master to it in a similar fashion, i.e. by first flushing & blocking all tables on master with the read lock, dumping the data with mysqldump (same flags), and finally loading them into the slave with the same mysql command-line client call.

Not too sure why still, but this problem disappeared after some of the troubleshooting steps I took to hunt down the culprit. Maybe it was the open files limit that caused the errors while opening the MyISAM tables, perhaps because of the high activity of other services running on the slave2 server, like Rackspace's cloud backup... But, whichever the case, replication has been running smoothly on slave2 off of my master server for a number of weeks already after loading it up once more with a fresh data snapshot taken from slave1 in the same fashion as described in my original question.

So, although unfortunately I can't provide a clear answer to the problem, I can definitely say it looks to have been solved. So I'm closing this post for the time being.


SQL Server OR operator causing massive loop join The query below is very slow (running over a minute) and I have narrowed down the issue to the OR operator (...OR (EXISTS (SELECT...).

I used live execution to verify that there is a nested loop join between the tables for the OR statement and then records are joined back to the EmailTable in the execution plan.

Basically, EmailTable is being probed twice.

If I add a hint OPTION (MERGE JOIN), the query is finishing within a second.

Please show me how to re-write this query so that the optimizer chooses a better plan by default.

EmailTable and TeamMembers has clustered index on INS_ID. Statistics on the tables are updated often.

DECLARE @a INT
    ,@b BIT
    ,@c INT
    ,@d INT
    ,@e INT;

SELECT [XYZ].[CNT] AS [C]
FROM (
    SELECT COUNT(1) AS [CNT]
    FROM [dbo].[EmailTable] AS [table1]
    WHERE ([table1].[INS_ID] = @a)
        AND ([table1].[ACTIVE] = 1)
        AND ([table1].[QUEUED_TO_SEND] = @b)
        AND ([table1].[OWNER_USER_ID] <> @c)
        AND (
            ([table1].[OWNER_USER_ID] IN (- 1))
            OR (N'Allusers' = [table1].[VISIBLE_TO])
            OR ([table1].[OWNER_USER_ID] = @d)
            OR (
                EXISTS (
                    SELECT 1 AS [C]
                    FROM [dbo].[TeamMembers] AS [table2]
                    WHERE ([table1].[INS_ID] = [table2].[INS_ID])
                        AND ([table1].[VISIBLE_TEAM_ID] = [table2].[TEAM_ID])
                        AND ([table2].[INS_ID] = [table1].[INS_ID])
                        AND ([table2].[MEMBER_USER_ID] = @d)
                        AND ([table2].[TEAM_ID] = [table1].[VISIBLE_TEAM_ID])
                    )
                )
            )
    ) AS [XYZ];

There are some redundant JOINs between the 2 tables, and using too many OR conditions with nested queries sometimes overwhelms the SQL Query Optimiser.

After cleaning up and organising the code, I've come up with the following (hopefully equivalent) alternative:

DECLARE @a INT
    ,@b BIT
    ,@c INT
    ,@d INT
    ,@e INT;

SELECT SUM(CNT) AS [C]
FROM (
    SELECT COUNT(1) AS [CNT]
    FROM [dbo].[EmailTable]
    WHERE 1 = 1
        AND [ACTIVE] = 1
        AND [INS_ID] = @a
        AND [QUEUED_TO_SEND] = @b
        AND [OWNER_USER_ID] <> @c
        AND (
            [OWNER_USER_ID] IN (- 1)
            OR N'Allusers' = [VISIBLE_TO]
            OR [OWNER_USER_ID] = @d
            )

    UNION ALL

    SELECT COUNT(1) AS [CNT]
    FROM [dbo].[EmailTable] AS [table1]
    WHERE 1 = 1
        AND [table1].[ACTIVE] = 1
        AND [table1].[INS_ID] = @a
        AND [table1].[QUEUED_TO_SEND] = @b
        AND [table1].[OWNER_USER_ID] <> @c
        AND EXISTS (
            SELECT 1 AS [C]
            FROM [dbo].[TeamMembers] AS [table2]
            WHERE [table2].[INS_ID] = [table1].[INS_ID]
                AND [table2].[TEAM_ID] = [table1].[VISIBLE_TEAM_ID]
                AND [table2].[MEMBER_USER_ID] = @d
            )
    ) XYZ
OPTION(RECOMPILE);

Let us know how it works and maybe post the Actual Execution Plan, as some commenters requested.


Converting Monthly Rolling SUM (YTD) back to Monthly numbers I'm having a problem converting rolling SUM by month (monthly year to date) back to only that month's numbers.
For example, see table below. Periods are (YYMM) and a monthly period per year starts with 07 and end with 06:
Name         Period         Amount
AAA          1611             10
BBB          1611             15
CCC          1611             20
AAA          1612             12
BBB          1612             18
CCC          1612             24
AAA          1701             13
BBB          1701             20
CCC          1701             27

The result that we are after is as follows. Period 1611 is the lowest in this example, but can be any YYMM month (07,08,09,10,11,12,01,02,03,04,05,06):
Name         Period         Amount
AAA          1611             10
BBB          1611             15
CCC          1611             20
AAA          1612             2
BBB          1612             3
CCC          1612             4
AAA          1701             1
BBB          1701             2
CCC          1701             3

Basically, it needs to take a higher period's value, minus the next period below it according to the Group By. If it can't find a lower period, then it keeps data the same.
Currently what I did was to Group By the data from the table I'm pulling it from as there would be multiple lines for each combination and we only need unique ones according to the group by.
Select

a.[Actuality], a.[Period], a.[(C) Company Code], a.[(C) Account Code], a.[(C) D1 Code],
a.[(C) D2 Code], a.[(C) D3 Code], a.[(C) D4 Code], a.[(C) Intercompany (To)],
a.[(C) Intercompany (From)], a.[(C) Type], Sum(a.[Amount]) as 'Amount'

From Table1 as a

Group By
a.[Actuality], a.[Period], a.[(C) Company Code],
a.[(C) Account Code], a.[(C) D1 Code], a.[(C) D2 Code]

I was thinking about putting a Where with a difference between periods needing to be 1 (1608-1607) or 89 (1701-1612) or 0 with AND a . [Period] needing to be smallest value for that Group by.
Could I please ask for help in formulating this in SQL?
Additional information

In the table only 1 year data is stored. Next year the data is moving to another table and its starts all over again from Period YY07 to YY06
In a table during the year around 2.5M rows are made
There are no duplicates in the table as a Group by is used to eliminate them
Variance between two lines should be also shown if a line exist 1 month and the next month doesn't: YTD in Period 1611 ABC has 20 and if Period 1612 ABC has 0 the line wouldn't appear in the YTD table, but in the month to date (MTD) would need to show Period 1612 ABC -20  (0-20)

select name,period
    ,amount-lag(amount,1,0) over (partition by name order by period)
from table1

with cte as
(
     select *
           ,row_number() over (partition by name order by period) as rn
      from table1
 )
 select t1.name,t1.period
       ,t1.amount-coalesce(t2.amount,0)
 from   cte as t1
        left join cte as t2
        on t2.rn = t1.rn-1


download mysql slow query log on google cloud sql I'm having a difficult time locating the log file to download.  When running
show global variables like '%slow%';

I can see it is located
/mysql/logs/slow.log

When I log into the instance though that path doesn't exist.  Any ideas?
This is through google cloud sql and using mysql as the db.

After a lot of digging I found an answer.  It is not perfect as the quality of data may need some massaging, but it works.
gcloud logging read projects/yourproject/logs/cloudsql.googleapis.com%2Fmysql-slow.log --format json| jq -rnc --stream 'fromstream(1|truncate_stream(inputs)) | .textPayload' > slow-log-timestamp.log


SQL Server Full txt Search - Thesaurus Entries I'm thinking of adding a full text search to our product (currently using SQL 2005 Enterprise), so just doing some preliminary research.

I read that you can add thesaurus entries, so "Internet Explorer" would also have related entries such as "IE", "IE7" and so on.

Would it also work in reverse, so if I enter "IE7" would it find "Internet Explorer"?

You should really consider upgrading to a more recent version, especially if you want to utilize some of the best parts of Full Text Search.

To your question, the short answer is yes.

From the documentation for 2005, you have two main features to customize in a thesaurus, Expansion Sets and Replacement Sets:

  An expansion set contains a group of synonyms. These synonyms are
  identified in code by "substitution" tags ( and ). Queries
  that contain matches in one substitution are expanded to include all
  other substitutions in the expansion set.

and

  A replacement set contains a text pattern to be replaced by a
  substitution set.

So if you place all three of your key words (IE, IE7, Internet Explorer) into a Substitution Set, then they will all trigger matches for each other. You can put as many substitutions in the set as you'd like - it's not a one-way definition for the substitutions.


Sequence of dates - months from January to December with SQL How can I create in SQL a function to search how many courses does a company each month from January to December in a specific year (2015)?

In the table fechahora form cursos, I have 1 in June, 1 in July, 3 in August, 1 in November and 0 in the other months. Furthermore I have the dates in this format '2015-01-01' and I need the name of the month.

I'm trying with this SQL statement.

SELECT
  MONTHNAME(FECHAHORA) AS 'MONTH',
  COUNT(Monthname(FECHAHORA)) AS 'TOTAL'
FROM COURSES
WHERE DATEOFCOURSE BETWEEN '2015-01-01' AND '2015-12-31'
GROUP BY MES
ORDER BY FECHAHORA;

From this statement, I have the results from the months that have a course but I need from January to December.

If you are using MariaDB, use something like this:

SELECT '2015-01-01' + INTERVAL seq MONTH FROM seq_0_to_11;

More discussion:  https://mariadb.com/kb/en/library/sequence-storage-engine/


is it safe to grant permission to all users in Microsoft sql server I have a global production server and database, as I all users should have access to it by remote desktop.
Is it possible to grant NTFS permission on all database files (mdf+ldf files) to all users? something like

everyone can full access to it.

note that : we didn't need to share anything by file sharing service.
our app admins can remote and then if needed they all can detach-attach all database if they want. then they want to move or change location of mdf or ldf if they want
my suggestion is we can grant full access to system server admins.
but our dba said:
we grant permission to everyone and it didn't have any risk
is this safe?

I'm not entirely sure what problem you're trying to solve, but if you grant the kind of access you're describing to "Everyone" and you get hacked, now you have opened up a lot more possibilities for someone to do damage to your systems. That is access that they could not have done if access had only been granted to a select few people.  It sounds like you want to have certain people with the ability to move database files around as needed. This will require a login and user and the ALTER DATABASE permission as well as an AD group that is either a local admin on the server or has been granted specific access to the folder(s) where the database files are stored. See the below question and answer about ALTER Database.
https://stackoverflow.com/questions/9798968/permissions-required-to-run-alter-database-set-single-user-statement-on-sql-se


Save performance with large update on Index with Included Column Points
------------------
PK  QuestionId  int (+30.000.000 distinct values)
PK  EventId     int (large batches where 80.000 rows have the same EventId)
    Value       smallint

The table is about 40 million rows and has performance issues.
There are two main queries:
On QuestionId:

about 30 million different QuestionId values (lot of variation)
a lot of queries during busy times (multiple thousands per minute)

On EventId:

there will be batch updates of +150.000 rows where EventId=X to set the Value=NULL during very busy moments.

So my first thought to get the best performance was that I make EventId,QuestionId the ClusteredIndex so the batch update can easily find all of EventId close to eachother and directly update the values.
My second thought was adding an Index with QuestionId and included column Value so it can read the value directly from the Index (EventId is irrelevant in this case).
But then I thought: would the Clustered Index even matter? As the Included column Value from the Index also needs be updated during the batch update.

While not trading off on query performance - is it possible to get the batch update fast (few seconds) or do I have to accept this process will always be slow without upgrading hardware.
Any other thoughts what would be the best way to set the ClusteredIndex / Indexes?

I know in theory I should test everything out and measure it, but the site is live and used heavily.
I'm a solo developer and I don't have the resources to hire someone. Any estimated guesses and thoughts about this would be really helpful as this gives me already the right direction!

So if your primary access path is by question, then the unique clustered index that makes the most sense will be (QuestionId, EventId).
Adding a second index to EventId may not be useful as the index might not be selective enough and the query engine will decide it's just faster to read the entire table instead of doing a lot of work to read a large portion of it.
Alternately, if you always query based wholly or in part on EventId, the clustered index of (EventId,Questionid) is more suitable, and has the added benefit of making your updates based on EventId require less I/O to complete.
I would not include Value to an additional index, as this will essentially copy the entire table (just clustered on different columns) and your updates will take even longer as Value must be kept in sync across both the clustered index and the additional index.
At a certain point there is no free lunch, and the correct solution is probably choosing the clustered index with the leading column that supports the most use cases, then adding RAM/CPU/faster storage to deal with cases where the entire table (or large chunks of it) must be read or written to.  With 40 million rows and such a narrow table I can't imagine this being something that more RAM wouldn't fix.
Depending on your edition of SQL Server, you can also look into if page compression would reduce the table size significantly, as this would reduce the number of reads/writes to disk (the additional CPU overhead is offset by fewer disk operations).  My guess in your case is it would not, but it's work looking at.

So if I understand correctly (with the current knowledge) your gut
feeling would be Clustered Index on QuestionId,EventId and then a
normal Index on EventId for the batch update?

Only if the primary use is to return specific QuestionIds without regard to EventId. You can try the additional index in EventId, but you may find it isn't being used often (or at all) for updates (or updates still take longer than you'd like), depending on how EventIds are distributed through your data with respect to QuestionId.
You also have to determine what is more important to you overall - select performance or update performance.  If the update is the pain point, (EventId,QuestionId) will undoubtedly be the better choice.  Given the number of unique values for QuestionId, adding an index on that column could be useful for SELECT performance, but that's going to depend on how QuestionId is distributed and how many you are searching for at a time.
Keeping statistics up to date will be crucial in either case.
An extremely simple example (for the sake of completeness):
Let's say we have a DBMS that maintains a clustered index and stores 4 rows per page.  We have a table with a primary key of (QuestionId, EventId) and one additional column, Value.
If we create the clustered index as (QuestionId, EventId), the data in our imaginary DBMS is (roughly speaking) stored like so:
Page | QuestionId | EventId | Value
-----------------------------------
A    | 1          | 2       | ...
A    | 1          | 3       | ...
A    | 1          | 6       | ...
A    | 1          | 7       | ...
B    | 1          | 8       | ...
B    | 1          | 10      | ...
B    | 1          | 11      | ...
B    | 2          | 2       | ...
C    | 3          | 2       | ...
C    | 4          | 1       | ...
C    | 5          | 6       | ...
C    | 5          | 7       | ...
D    | 6          | 1       | ...
D    | 7          | 2       | ...
D    | 7          | 6       | ...
D    | 7          | 8       | ...

So if I need to perform an operation based on QuestionId, the engine will not have to read more pages than necessary.
However, if I need to perform an operation based on EventId, I will have to read the entire table (clustered index scan), unless I add an additional index, which will look like this (and require four pages):
EventId | QuestionId
--------------------
1       | 4
1       | 6
2       | 1
2       | 2
2       | 3
2       | 7
3       | 1
6       | 1
6       | 5
6       | 7
7       | 1
7       | 5
8       | 1
8       | 7
10      | 1
11      | 1

This index be selective for some EventIds, but in the extreme case (EventId = 2) the whole table would still need to be read, and for some cases (EventId = 6) our optimizer might decide that searching the index and reading the table is more expensive than just reading the entire table.
If we instead cluster on EventId, QuestionId our table looks like this:
Page | EventId | QuestionId | Value
-----------------------------------
A    | 1        | 4         | ...
A    | 1        | 6         | ...
A    | 2        | 1         | ...
A    | 2        | 2         | ...
B    | 2        | 3         | ...
B    | 2        | 7         | ...
B    | 3        | 1         | ...
B    | 6        | 1         | ...
C    | 6        | 5         | ...
C    | 6        | 7         | ...
C    | 7        | 1         | ...
C    | 7        | 5         | ...
D    | 8        | 1         | ...
D    | 8        | 7         | ...
D    | 10       | 1         | ...
D    | 11       | 1         | ...

Any operation based on EventId will only read the necessary portions of the table, and like our first instance, any operation based on QuestionId will require a scan without an additional index.  If we create an index on QuestionId, the index will be:
QuestionId | EventId
--------------------
1          | 2
1          | 3
1          | 6
1          | 7
1          | 8
1          | 10
1          | 11
2          | 2
3          | 2
4          | 1
5          | 6
5          | 7
6          | 1
7          | 2
7          | 6
7          | 8

So as in the first example, this index will be more useful for some questions and less useful for others.  For QuestionId = 1 the optimizer will probably say the cost of reading half the index then looking up half the table isn't worth the cost and will just read the entire table instead of utilizing the index.
If we include Value on the index, we now have to change the table AND the index within the same transaction.  Best case, this doubles the work for any operation.  Worst case this requires the entire table or index (which is just a copy of the table) to be read and possibly locked.
Now it's possible with your actual data that adding an additional index on QuestionId or EventId will provide a lot of benefit.  But it's not going to solve every problem, and it might not be worth the overhead imposed on inserts/updates/deletes.


SQL - Query on table creation, referential integrity Please let us know what does the below definition means on table creation.

CREATE TABLE A(w INT PRIMARY KEY);

CREATE TABLE B(x INT PRIMARY KEY REFERENCES A(w) ON SET NULL)

As per my understanding, there is a foreign key reference on attribute-B(x) with attribute-A(w) and B(x) will set to null when A(w) is deleted that reference to B(x). Will this not violate the primary key constraint on X, which cannot be NULL?

Your scenario makes no sense. You want something like

CREATE TABLE B(x INT REFERENCES A(w) ON DELETE (SET NULL | CASCADE | NO ACTION)

What you have is a 1-1 relationship between your tables, so x should really be in table A - you are trying to implement a parent-child relationship with only one possible child. Furthermore, you are trying to SET a primary key field to NULL, which is not possible. It is possible to have a UNIQUE KEY with multiple fields with NULL values for some of those fields. To answer your question directly, it will violate the PRIMARY KEY constraint and will throw an error at some point, either at table declaration or during testing/production.


PostgreSQL: alternative ways to connect to database THIS IS A THEORETICAL QUESTION. I am posting it on this site because of this and because I think that database administrators could provide more accurate insight. The line that separates the two sites is still not crystal clear to me so I may have got it wrong.

Long story short: there is a certain Postgres database, and there is a need of creating a form like access to it, and I have run out of ideas and/or lack some knowledge as to how to do some things (in case they can be done at all)

There are some fields of a single table that need to be accessed to to read and edit them on demand. The rest of the data is imported into the database following a traditional ETL process, but these pieces of information need to be inserted manually and on demand, and the users will be business people who are incredibly fond of Excel.

Below are some of the options I have considered and the reasons as to why they have been rejected:

Web form: the time needed to develop it is not affordable.
Distribute an Access Application based form with an ODBC connection to DB: every single user would have to install both MS-Access and the psqlODBC driver and they are strongly against this *sigh*
Having a similar form to that based on Access, but developed in Excel: from what I have seen and tried, pulling data from PostgreSQL to Excel is viable although not so trivial (I have had some issues with the casing of most names, as Windows is case insensitive while Postgres is not), but the reverse is not. Maybe there is a VBA solution to do the push? I know some VBA and could manage to get a solution if this is viable. I know how to do this through VBA in Access but the VBA used in Excel has a different set of resources and I don't know them that much.
Having this single table managed by a FDW (Foreign Data Wrapper) and distribute an Excel file linked to it (with a form-like "interface"): from my research I have found that importing/pulling data from a csv file is a common task but I have not found anything on reversing the data flow: editing the "remote" csv file from Excel. If this can be done, truly dynamically linking a csv file to excel so that it can be updated, it could pose as a viable solution

Any suggestion or insight as to how can this be done will be greatly appreciated.

Edit:
The options number 3 and 4 described above are oficially rejected. My brain must have gone numb or something to even consider Excel pushing data a possibility. It has never been able to do that, it just pulls, visualizes and analyzes data. That's it. That's all there is to Excel.

Edit2:
We have successfully made it clear to the client that what he wants from this project under these conditions is plainly impossible. So they have reconsidered it and the constraints set have changed. I will be doing a web app form, which is the best solution in terms of software needed to run it.
I hope this can be of help to anyone who ever runs into a similar situation. Thanks for the attention to those who cared to read (and write) :)

A poor man's solution might be simply installing pgAdminIII, setting up the connection to the database and using it's built in filtering and data view/modification functionality.

I admit this does not give you a very good user experience (about 8 clicks to get to the table you want to view/edit), but it is very easy to set up.

One important thing: you may want to restrict access to the tables that are really editable by your users.  A good approach might be:

CREATE ROLE limited_editor;

GRANT USAGE ON SCHEMA your_schema TO limited_editor;
GRANT SELECT, INSERT, UPDATE ON your_table1, your_table2, ... TO limited_editor;
REVOKE ALL ON ALL TABLES IN SCHEMA your_schema FROM public;

GRANT limited_editor TO alice, bob, ...;

When doing this, revoking access from public might have some unwanted effects on other users' privileges.  Fix them accordingly (for example, by setting up a more powerful editor role, that can be granted to other users).