823 errors on tempdb Have a couple Fiber channel SAN-attached cluster servers which have been intermittently reporting errors like:

2012-05-09 16:31:54.13 spid74      Error: 823, Severity: 24, State: 2.
2012-05-09 16:31:54.13 spid74      The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000000e11b0000 in file 'T:\tempdb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2012-05-09 16:31:54.13 spid74      Error while allocating extent for a worktable.  Extent (1:461024) in TEMPDB may have been lost.

SQL Server re-start, which re-creates tempdb, has so far always fixed the issue.

Per a Microsoft tech recommendation, I've enabled trace flag 818 ( http://support.microsoft.com/kb/826433 ), but this flag has not yet yielded any fruit [addition ERRORLOG details]; I'm supposing what's happening must not be meeting the criteria for this flag's feature to log extra data.

Have a Microsoft case open, and sysadmins and storage vendor looking at things.  Also working through http://support.microsoft.com/kb/2015755 , including running sqliosim at one site for ~2.5 hrs.  Though there must be something going wrong in the I/O path, nothing has bubbled to the top to pinpoint the cause.

What have you experienced?
What are your thoughts about where else to look?

First, if you're getting corruption issues in TempDB, I'd start by making sure I've got DBCC CHECKDB reporting clean results across my user databases. I'd also restore those backups on another server and run DBCC CHECKDB there just to make sure the problems are only confined to TempDB.

Then I'd remove any filter drivers on the servers - antivirus, disk defragmenting, storage mirroring, etc - and try to run multiple load-intensive queries against TempDB to see if I can reproduce the issue. Do simple select-intos, dumping Cartesian joins of your user databases into TempDB.

Finally, if I can get an outage window, I'd try SQLIO - not SQLIOSIM - hammering the TempDB drives. SQLIOSIM is interesting if you want to reproduce a vague similarity of a benchmark, but SQLIO is better if you just want to repeatedly punch your storage in the junk. Plus, it's really good about bubbling failures up quickly.

You also asked:

  how much more likely is drive corruption than, say, I/O path driver
  issues (storport, HBA driver or firmware, filter drivers).

In my experience, the most common corruption issues are connectivity problems, followed by storage processor/controller firmware bugs, followed by filter drivers (I see less only because they were so unreliable that most folks have uninstalled them), finally followed by drive corruption. After all, in modern storage, the RAID controllers are pretty good about catching problems with a single failing drive.


How to properly restart a MySQL Master server? We are running on Azure VM wherein you can not expand the attached disk while the VM is running. So we need to stop the VM first before doing anything on the disk size/performance.
We just have 1 slave server for this replication.
I've read some procedures online in doing this, please confirm if this is appropriate.
On Slave Server
STOP SLAVE;

On Master Server
sudo service mysql stop

Once stopped, update the VM settings (expand disk). Then start the VM.
On Slave Server
START SLAVE;

You don't need to stop the slave. It will continue once the master restarts. It tries every 60 second or so to connect.
Only stopping the master service/vm is needed.


SQL actually executed as planned in ORACLE? I wonder if SQL is definitely executed as execution plan.

I mean.. is the actual SQL execution process always consistent with execution plan, i.e. does the same SQL always give the same plan?

The short answer is that no, the same SQL doesn't automatically lead to the same execution plan.

For example, one may have a small table and the optimizer may realise that there's no point in using an index since an FTS (Full Table Scan) would be cheaper than trawling through an index and then performing lookups.

The "No" answer is the reason that Oracle introduced Outlines to implement Plan Stability. Many posts on the Oracle forums concern poor DBAs complaining about the fact that their system worked fine on Tuesday but that on Wednesday it had slowed right down. Further investigation showed that this was due to a critical change in the Execution Plan for a particular query.

What Plan Stability is basically doing is while accepting that some plans may be sub-optimal, the DBAs/Devs would prefer a stable system over one which might change catastrophically in an unpredictable fashion.

Some good information about this can be found here and here. The oracle-base site is one of the best sources of Oracle information and Tanel Poder is a very big hitter in the Oracle world.


What are the files with the extensions .1, .2 or .3 in the data directory Why are there files ending in .1 .2 .3 in my PostgreSQL database folder?

-rw------- 1 postgres postgres 1073741824 Oct 11 09:32 412rw95.2
-rw------- 1 postgres postgres   67100672 Oct 11 09:32 41295.3
-rw------- 1 postgres postgres 1073741824 Oct 11 09:07 41296
-rw------- 1 postgres postgres 1073741824 Oct 11 07:27 41296.1

The PostgreSQL documentation describes this:

  When a table or index exceeds 1 GB, it is divided into gigabyte-sized segments. The first segment's file name is the same as the filenode; subsequent segments are named filenode.1, filenode.2, etc.


How SQL determine page exists in Buffer pool (internal) When we execute SQL query, then Relational Engine optimize it and pass query plan to Storage Engine which then ask for a page with actual data.

If data page isn't in memory, then SQL pull those pages from disk and place it in RAM.

How, in future, SQL determine if page, which Storage Engine asks, is already in memory? And which algorithm it uses to keep some free space for new pages?

How, in future, SQL determine if page, which Storage Engine asks, is already in memory?

To balance CPU and I/O throughput to achieve good performance and maximize hardware utilization, SQL Server includes 2 asynchronous I/O mechanisms - sequential read ahead and random prefetching

When SQL Server has to scan a large table, the storage engine will initiate the read ahead mechanism to ensure that pages are in memory and they are ready to scan before they are needed by the Query Optimizer.

If you are using Enterprise edition, then there is a mechanism called advanced scanning or merry-go-round scanning that will allow multiple tasks to share full table scans. This advance scanning avoids users to compete for buffer space and taxing other resources on the server.

  And which algorithm it uses to keep some free space for new pages?

SQL Server (and Sybase as well :-) ) uses LRU (Least Recently Used) algorithm to keep track of pages that are aged in the buffer pool. It increments a counter every time a page is referenced and decrements the counter every time the lazy writer process sweeps the page.

Any worker thread will check the memory status of Buffer Pool to make sure that there are healthy number of Free pages to honor incoming new requests.

Note that : SQL Server will always keep a minimum number of free pages on the free list, so that it can serve incoming requests without any significant delay. The amount of free space is calculated based on Buffer Pool and amount of incoming requests.

IF there are no free buffers or very little left then :

If Max Memory is reached then

the lazy writer process will sweep parts of buffer pool and based on when the page was last accessed - either leave it or flush it to disk and thereby put the page on the FREE list. A dirty page will not be flushed to disk until the corresponding log records have been written to disk.

If Max Memory is not reached then

SQL Server will commit more reserved pages into the buffer pool.

You can use DMV (in 2005 and up) - sys.dm_os_buffer_descriptors to find the pages in the buffer pool.

Also, there are Memory Manager surface area changes in SQL Server 2012.

KB 2663912 :

  Starting with SQL Server 2012, Multi-Page allocations and CLR allocations are also included in memory limits that are controlled by max server memory (MB) and min server memory (MB). This change provides a more accurate sizing ability for all memory requirements that go through the SQL Server memory manager. Carefully review your current max server memory (MB) and min server memory (MB) values after you upgrade to SQL Server 2012.

Highly recommend to read :

Inside the Storage Engine: What’s in the buffer pool? by Paul Randal
Performance issues from wasted buffer pool memory by Paul Randal
Sequential Read Ahead
Reading Pages


Counting number of occurrences, how to "group by" I have the following query, which works as intended. Database is Sybase SQLAnywhere, which follows t-sql syntax for the most part.
I'm trying to add a column to COUNT the number of times each "referred_link" occurs on the table called "cases".
Num_refs is the total number of times a specific "cases.referred_link" exists on the cases table, for each referrer.
Expected output:
casenum:    casetype:    date_opened:    client:    referrer:    num_refs:
30293       MVA           1/1/2021       Joe Smith   Maggie G      3
10293       SF            1/4/2020       Ben R       Simon A       4

Here's the query:
select casenum,
matcode as casetype,
date_opened,
sp_first_party(casenum) as client,
sp_name(referred_link,1) as referrer,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star,
'repeat client' as reason
from cases
inner join party on cases.casenum=party.case_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on party.party_id=a.names_id
where party.role='Plaintiff'
and party.our_client='Y'
and date_opened&gt;='##STARTDATE##'
and date_opened&lt;='##ENDDATE##'
and cases.case_date_6 is null
and party.party_id IN
  (select
    party_id
    from
      (select
        count(casenum) as casecount,
        party_id
        from cases
        inner join party on cases.casenum=party.case_id
        where party.role='Plaintiff'
        and party.our_client='Y'
        and matcode IN ('PMV','MVA','IMV','GPI','IGP','S%F','ISF')
        group by party_id) multicases
        where multicases.casecount>=2)
UNION ALL
select casenum,
matcode,
date_opened,
sp_first_party(casenum) as client,
sp_name(referred_link,1) as referrer,
count(sp_name(referred_link,1)) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star,
'personal referrer' as reason
from cases
inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on names.names_id=a.names_id
left join provider on names.names_id=provider.name_id
left join party on cases.casenum=party.case_id
where names.person='Y'
and date_opened&gt;='##STARTDATE##'
and date_opened&lt;='##ENDDATE##'
and party.our_client='Y'
and party.role='Plaintiff'
and party.party_id != referred_link
and ((code not like '%Attorney%' and code not like '%Established%' and code not like '%Doctor%' and code not like '%Chiropractor%') or provider.code is null)
and matcode in ('PMV','IMV','MVA','S%F', 'ISF', 'GPI', 'IGP')
order by date_opened asc

And here's how I'm trying to do it, entered after line 5:
(select
  count(sp_name(referred_link,1))
  from cases
  where sp_name(referred_link,1) = referrer
  and date_opened&gt;='##STARTDATE##'
  and date_opened&lt;='##ENDDATE##'
) as num_refs,

but I'm getting an error "Function or column reference to 'casenum' must also appear in a GROUP BY"
Can anybody help me straighten this out?

Well, I was able to get it working.
If anyone else has some similar scenario, here's what worked:
select casenum,
matcode as casetype,
date_opened,
sp_first_party(casenum) as client,
sp_name(referred_link,1) as referrer,
(select
  count(sp_name(referred_link,1))
  from cases
  where sp_name(referred_link,1) = referrer
  and date_opened&gt;='##STARTDATE##'
  and date_opened&lt;='##ENDDATE##'
  group by cases.referred_link
) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star,
'repeat client' as reason
from cases
inner join party on cases.casenum=party.case_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on party.party_id=a.names_id
where party.role='Plaintiff'
and party.our_client='Y'
and date_opened&gt;='##STARTDATE##'
and date_opened&lt;='##ENDDATE##'
and cases.case_date_6 is null
and party.party_id IN
  (select
    party_id
    from
      (select
        count(casenum) as casecount,
        party_id
        from cases
        inner join party on cases.casenum=party.case_id
        where party.role='Plaintiff'
        and party.our_client='Y'
        and matcode IN ('PMV','MVA','IMV','GPI','IGP','S%F','ISF')
        group by party_id) multicases
        where multicases.casecount>=2)
group by cases.referred_link, cases.casenum, cases.matcode, date_opened, mailing_list
UNION ALL
select casenum,
matcode,
date_opened,
sp_first_party(casenum) as client,
sp_name(referred_link,1) as referrer,
count(sp_name(referred_link,1)) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star,
'personal referrer' as reason
from cases
inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on names.names_id=a.names_id
left join provider on names.names_id=provider.name_id
left join party on cases.casenum=party.case_id
where names.person='Y'
and date_opened&gt;='##STARTDATE##'
and date_opened&lt;='##ENDDATE##'
and party.our_client='Y'
and party.role='Plaintiff'
and party.party_id != referred_link
and ((code not like '%Attorney%' and code not like '%Established%' and code not like '%Doctor%' and code not like '%Chiropractor%') or provider.code is null)
and matcode in ('PMV','IMV','MVA','S%F', 'ISF', 'GPI', 'IGP')
group by cases.referred_link, casenum, cases.matcode, date_opened, mailing_list
order by num_refs desc, date_opened asc


Disable InnoDB deadlock detection I would like to disable InnoDB dead-lock detection and automatic rollback, as per the requirement we like to rely on innodb_lock_wait_timeout (because of heavy concurrency).

  The lock wait timeout value does not apply to deadlocks when
  innodb_deadlock_detect is enabled (the default) because InnoDB detects
  deadlocks immediately and rolls back one of the deadlocked
  transactions. When innodb_deadlock_detect is disabled, InnoDB relies
  on innodb_lock_wait_timeout for transaction rollback [...]

How can we achieve the above in MySQL 5.7.13? I couldn't find any variable innodb_deadlock_detect.

Yes, you can do it. Be sure to keep innodb_lock_wait_timeout reasonably low, for example, 2 seconds. Deadlocks that last for 2 seconds are completely harmless, if they are rare (and usually they are very rare). Remind developers to avoid long transactions, and avoid foreign keys ON ... CASCADE (because they propagate locks, making deadlocks more likely).

Of course I am assuming that you can keep a very low value for those timeouts. But can you? I don't know your workload but, for example, a statement may lock some rows for 10 seconds every hour. In such cases, can you afford a low timeout? If not, then everything I wrote above does not apply to you.


Dispaly Results from left join takes lot of time - MSSQL I have the following query (just for example):

SELECT
     a.id
    , a.name
FROM db.schema.tablea a
    LEFT JOIN db.schema.tableb b
        ON a.id = b.id    -- number
        AND a.name = b.name    --string

This query takes usually 1 second to run.

But, the following query takes 20 seconds to run:

SELECT
    a.id
    , a.name
    , b.value
FROM db.schema.tablea a
    LEFT JOIN db.schema.tableb b
        ON a.id = b.id    -- number
        AND a.name = b.name    --string

In other words, display the results (b.value) makes the query 20 times slower.
In my specific case, I make a left join on two columns: string (lenght max 10) and number. The result (b.value in my example) is a float.

The joined table has 3 columns and ca 2000 rows.

Perhaps you see a join elimination? We can't say since you didn't give us any details (like execution plan) and also your query is simplified, so we can't say whether join elimination is possible in the first place. Join elimination is when SQL Server determines that it doesn't have to access a table at all, so it eliminates it from the execution plan. Here's an example using Adventureworks where the is no need to access the referencing table (in a FK relationship) since we didn't pick any columns from it (note the commented column):

SELECT DISTINCT c.CustomerID, c.AccountNumber--, h.Freight
FROM Sales.Customer AS c
LEFT OUTER JOIN Sales.SalesOrderHeader AS h ON c.CustomerID = h.CustomerID

And here is a different example where it doesn't access the referenced table, since we preserve the referencing table and don't pick any columns from the referenced table:

SELECT h.Freight, h.CustomerID, h.CustomerID
FROM Sales.Customer AS c
RIGHT OUTER JOIN Sales.SalesOrderHeader AS h ON c.CustomerID = h.CustomerID


Remove Local RAID Drive from MS SQL Windows Cluster We recently stood up a production SQL Server 2014 Enterprise on a two-node Windows Failover cluster (Active/Passive).  We had some SSD's laying around and decided to use them for the tempdb for one of the two instances hosted on this cluster.  The thought was local storage plus SSD = win.

We did run Dsksp, SQLIO, etc to test this setup before going into production and everything checked out ok.  However, once we got went live, we started to see significant I/O wait issues during (and only during) our nightly DB integrity check.

After trying a few things in an attempt to resolve the issue, we decided to move the tempdb off to our SAN storage--which cleared up the I/O wait issues.

We now want to remove the SSD from each node.  Before we do so, I want to make sure I understand the process correctly.

These were local disks so they were not assigned as a cluster resource.

From what it appears, now that the tempDB has been moved, we simply need to remove the logical drive from the OS (i.e. take offline/delete), then delete the virtual disk from the RAID controller.

Is it this simple or do is there something more that needs to be in SQL or in the cluster configuration?

I have seen articles about removing shared storage, but nothing touching on local and since this is our first cluster, I want to make sure there isn't anything hidden that will cause down time or otherwise ruin our day.

Thanks

SSDs are great in general, but they are much better suited to read heavily applications, while tempdb is a mix of reads / writes and the nature of tempdb generates a consistent churn which is not good for an SSD. But it sounds like you've already gotten your tempdb off the SSD, so that step is already done.

I've not tried swapping out a local SSD for a SAN drive, but I've been involved in a migration which did that process in reverse (swapped a SAN drive for a local SSD) We failed over to a virtualized server during the process (the main server was a physical server) but it was very straightforward. We might have been able to swap in the SSD and done the entire process in a 30 minute window and skipped the fail over altogether had we known. But the policy at my current work is that any downtime longer than 30 minutes needs to be a failover.

So TLDR, it was very easy, but I wasn't the person responsible for doing the physical drive swap, only for failing over SQL Server, then doing the work to utilize the new SSD, which was reassigned the drive letter that we used for data originally.


Log (ldf) file grows continuously We have a problem with just one of the databases on our SQL2008 Server. For some reason recently the log file is just growing and growing.

The server runs the following backups

Weekly - Full
Daily - Diff
Hourly - Transactional

I have followed the information here (Why Does the Transaction Log Keep Growing or Run Out of Space?) but without any luck and have confirmed the following:

The database is in FULL recovery mode
Backups are occuring as expected (Hourly, Daily, Weekly)
The log file is full, it's that that there is space that can be recovered
Looking at the log_reuse_wait_desc returns 'NOTHING'

The website associated with the database is the most used site we have, but the database and usage is still small (under 5Gb of data) a few hundred users a day, but the Log file gets over 10Gb after a couple of days.

Anyone any idea what could be causing this? The only way I can find to get around this is to switch to 'SIMPLE' recovery mode. Shrink the file, switch back to 'FULL' then do a new Full backup to start a new chain.

Thanks for any advice you can provide

Conceptually this is what should be happening within your database when the end of the physical file is reached:

However, it sounds like you have a log header that doesn't want to wrap back around to the start of the physical log file and continues to fire off auto-growth events instead.  This can occur for any number of reasons, but the most common is a vlf in front of the logical log has a DBCC LOGINFO status of 2 for whatever reason, even after switching recovery models as you're doing now, but the way to fix it is the same regardless of the reason.  Simply perform the following steps:

Perform a Log Backup (e.g. BACKUP LOG [DBNAME]...) against the database
Perform a DBCC SHRINKFILE operation on the transaction log file specifying 1 MB as the target size
Perform another Log Backup against the database
Perform another DBCC SHRINKFILE operation on the transaction log file specifying 1 MB as the target size
Manually increase the size of the log file, incrementally per Kimberly Tripp's article, to grow the TLog back to an appropriate size.

So what's happening here?

The first TLog backup simply forces a CHECKPOINT operation and flushes the log to disk.
The first DBCC Shrinkfile operation shrinks the physical TLOG down to where the current logical log is located
The second Log Backup issues another checkpoint and will likely backup/clear any resident log operations located in front of the logical log.  These were never flushed in the past for whatever reason, but should be cleared at this point.  This step is critical to fixing your issue and sometimes this backup can take a bit of time depending on how much data needs to be written down to disk.
The second DBCC SHRINKFILE operation moves the logical log file to the head of the physical log file, eliminates excessive VLFs that are likely there now, and reduces the TLOG file back down to a very small size, in this case 1MB.  It is imperative to manually grow the file in step 5 so you don't run into vlf issues again down the road
Manually increasing the size of the tlog will allow you to control the size and number of VLFs within your physical log file.  This is an opportune time to configure this properly, so take advantage.

One final note on the process.  If your tlog is highly active, you may need to perform this a few times or during a window of reduced activity.  High activity may result in another errant auto-growth event occurring before you execute the second log backup.  This basically turns what you thought was step 3 back into step 1.  Generally Steps 1 and 2 go by quickly, and step 3 takes the longest to complete.  Make sure to follow-through with steps 4 and 5.


Have several 128 elements lists, need to obtain distance between them Is storing them in a CUBE type field and then getting euclidean distance, (using <->) the only way?

I can not save them as a list of floats, since i get: "can't adapt type numpyndarray"

Can not cast a type bytea to type cube, either.

Cube extension is installed, i can convert the list to type bytea using psycopg2, but nothing seems to work.

What am i missing?

I'm using postgresql 10.4, python 3, postgresapp 2.1.4 and postico

I can not save them as a list of floats, since i get: "can't adapt type numpyndarray"

Quick google search shows numpyndarray as being SciPy's "N-dimensional array". This can hold what PostgreSQL calls a cube and vise-versa.

You assume that there is a translation layer that takes numpyndarray and converts the types to a cube for you. There likely isn't. You could provide such a layer, or extend your PostgreSQL connector/driver to provide that layer (see this for information on psycopg), but shy of that you'll have to go by way of text.

That means you'll be calling cube() or providing a text string and a cast to cube.

SELECT '(0,1,2,3,4,5)'::cube;
SELECT CAST ( '(0,1,2,3,4,5)' AS cube );

Your job is to convert numpyndarray to a textual representation like the above, and then to convert it back again.

Cubes can also be constructed as float[] which you may find easier if your DB layer supports that,

SELECT cube(ARRAY[0,1,2,3,4,5]::float[]); ## Array-constructor for float[]
SELECT cube('{1,2,3,4,5}'::float[]);      ## Text-constructor for float[]


What exactly is Psycopg2? This question is for a report I'm writing for school. Technically speaking, what is Psycopg2? In this tutorial they refer to it both as an "adapter" and "driver"

  Psycopg2 is a DB API 2.0 compliant PostgreSQL driver that is actively
  developed. It is designed for multi-threaded applications and manages
  its own connection pool. Other interesting features of the adapter are
  that if you are using the PostgreSQL array data type, Psycopg will
  automatically convert a result using that data type to a Python list.

Since it's imported into Python, I would've guest it was a library?

What's the distinction between library, driver and adapter?

psycopg2 is a wrapper around libpq, written in C, to expose a Python DB-API compatible API to Python programs. It implements Python objects in C that call libpq functions. It has a thin Python module wrapper around it to load it and provide some of the interface functionality that's easier to write in pure Python.

Anything that implements the DB-API to connect to an external database can be reasonably called a database driver or database adapter. The two are pretty much interchangeable. Any DB-API implementation must be a Python module. Python modules are programming language libraries, in that they're collections of re-usable code. Some are also C shared libraries, in that they're compiled C code that gets linked into the cPython executable.


Are SQL Server statistics stored in database or buffer pool? Just wondering are statistics kept in the database but not in the memory? If I backup/restore the database from a prod server to a development server, would it keep the same statistics so that execution plans won't be much different if I do it on the development server?

The buffer pool is a cache of the database. There is never an 'or', things that are in the buffer pool are also in the database, always. And anything read from the database must be, even temporarily, present in the buffer pool.

As for the question: statistics are in the database so a backup/restore will preserve the statistics.

Note though that preserving statistics is not a guarantee to plan reproducibility. Other factors influence plan generation, like number of CPUs and amount of RAM.


Should I add set nocount for functions? I've been reading about the benefits for all triggers and procedures having "set nocount on" implemented unless you make use of DONE_IN_PROC.

I have a scalar-valued function that is called from an update trigger to calculate the sum of values across multiple tables.

Searching msdn and past articles, I haven't been able to find an explicit example of a function using set nocounton.

What is the impact on a function to include or exclude 'set nocount on'?
Is there a point or code smell in functions that should indicate the addition of set nocount on?

Functions cannot return data to a client so I can't think of any use of SET NOCOUNT ON. If you do try to add it a function you'll get an error. SQL Server considers it to cause a side effect:

  function_body

  Specifies that a series of Transact-SQL statements, which together do not produce a side effect such as modifying a table, define the value of the function.

For example, the follow code throws an error:

CREATE FUNCTION X_JRO_TEST()
RETURNS INT
AS
BEGIN
SET NOCOUNT ON;
RETURN 1;
END;

  Msg 443, Level 16, State 15, Procedure X_JRO_TEST, Line 5

  Invalid use of a side-effecting operator 'SET OPTION ON' within a function.


Identifying number of rows (COUNT) that fall between two timestamps for each minute in the hour Good day all!

I'm attempting to write some SQL that can identify the number of rows that fall between two timestamps but iteratively count them up.

An example use case, a telephone call CDR for one user:

|Call Number|Start Time         |EndTime            |
|===========|===================|===================|
|1          |2019-01-01 10:01:23|2019-01-01 10:05:44|
|2          |2019-01-01 10:02:33|2019-01-01 10:05:22|
|3          |2019-01-01 10:03:50|2019-01-01 10:06:11|

I'd like a query that could produce this sort of result, per each minute for the hour (using the example data above, Call ID is an arbitrary number, so can be ignored in this context, it's the count of the calls in progress that's important):

|Time Chunk         |Calls In Progress|
|===================|=================|
|2019-01-01 10:00:00|                0|
|2019-01-01 10:01:00|                1|
|2019-01-01 10:02:00|                2|
|2019-01-01 10:03:00|                3|
|2019-01-01 10:04:00|                3|
|2019-01-01 10:05:00|                3|
|2019-01-01 10:06:00|                1|

A script would iterate through customers to produce reports per customer so the WHERE clause would consist of something like WHERE user = 123 for example.

I've tried a couple of queries, but they will only produce instantaneous results based on the start time of the call and then show me how many were in progress for that particular minute of the day, with a load of gaps in between, and not counting for any calls that are "still in progress", as follows:

SELECT
    FROM_UNIXTIME(
        CEILING(
            `timestamp`/60
        )*60
    ) AS timeslice,
    COUNT(*) AS calls_in_progress
FROM
    calls
WHERE
    account = {ACCOUNT_ID}
    AND `timestamp` >= UNIX_TIMESTAMP('2019-01-01 00:00:00')
    AND `timestamp` <= UNIX_TIMESTAMP('2019-01-01 23:59:59')
GROUP BY
    timeslice
WITH ROLLUP;

...which in turn produced the following undesirable result (the rollup is useful but not required)...

|timeslice          |calls_in_progress|
|===================|=================|
|2019-01-01 09:35:00|               18|
|2019-01-01 12:10:00|                2|
|2019-01-01 12:11:00|                2|
|2019-01-01 12:19:00|                1|
|2019-01-01 12:49:00|               51|
|NULL               |               74|

Performance impact of said query is quite important given this is going through a table with millions of rows, so the use of the indexes associated to timestamps would be critical.

Any input would be super useful.

I'd like a query that could produce this sort of result

Look at

WITH RECURSIVE
cte1 AS ( SELECT CAST(DATE_FORMAT(MIN(StartTime), '%Y-%m-%d %H:%i:00') AS DATETIME) mintime,
                 CAST(DATE_FORMAT(MAX(EndTime), '%Y-%m-%d %H:%i:00') AS DATETIME) maxtime
                 FROM srctable ),
cte2 AS ( SELECT mintime timechunk,
                 mintime + INTERVAL 1 MINUTE nextchunk
          FROM cte1
        UNION ALL
          SELECT cte2.nextchunk,
                 cte2.nextchunk + INTERVAL 1 MINUTE
          FROM cte1, cte2
          WHERE cte2.timechunk < cte1.maxtime )
SELECT cte2.timechunk time_chunk,
       COUNT(*) calls_in_progress
FROM cte2
LEFT JOIN srctable ON cte2.timechunk < srctable.endtime
                  AND cte2.nextchunk >= srctable.starttime
GROUP BY cte2.timechunk
/* WITH ROLLUP
   ORDER BY cte2.timechunk IS NULL, cte2.timechunk */

fiddle


Express edition core limits ambiguity We have an application that uses MSSync to download data from a server into a client's SQL Server Express 2005. The client's have a dual core with hyperthreading so, a total result of 4 cores.

I have done a lot of research on SQL Server Express' limitations, and I think it comes down to 1 physical socket, but using up to 4 cores in that socket. But, that raises some questions in contrast with what I perceive in practice.

Our sync process is maxing out 1 core, drowning the sync process, resulting in 10-20 times slower performance than on devices that have higher GHz and not maxing out the single core that is being used by SQL Server Express 2005.

But: if SQL Server is allowed to use 4 cores in 1 socket, why is it using only 1 for our sync process? Is this because 1 connection has a dedicated core? Or am I not understanding the limit specs correctly? Would upgrading SQL Server Express to a modern version be useful to get it to use more cores?

I have done some more research. I used SQL Server Express 2012 and even SQL Server Developer 2012 and ALL max out only 1 core. So, apparently, this has nothing to do with an Express restriction.

It is probably a technical restriction where your querying within a single connection/transaction just stays on a single core. Likely, this is a logical requirement for guaranteeing transactional consistency.

What I do see as of SQL Server 2012, is that the load will swap to another core now and then, but it will never use more than 1 core at the same time. Not even the Dev edition.

If anyone could confirm these assumptions, that would be welcome.

SQL Server 2005 Express supports a single socket with up to four cores.

Each core is mapped to a SQLOS scheduler. There is one VISIBLE ONLINE scheduler per core in the sys.dm_os_schedulers dynamic management view. The following screenshot was taken from an instance of SQL Server 2005 Express running on a machine with a single socket CPU containing four cores:

The general SQLOS model is:

Statements in a client batch are mapped to one or more tasks.
Each task is bound to a worker thread.
A worker thread is bound to a scheduler.

In a serial execution plan, there is just one task, and only one scheduler is used. SQL Server Express Edition only supports serial execution. Therefore, each statement can only use a single core.

Other editions of SQL Server are capable of generating parallel execution plans, where a single statement may generate multiple tasks, which can then run on multiple schedulers concurrently. This is generally limited to operations that read data (not write it).

Before SQL Server 2014, parallel data modifications were limited to index (re)build plans. General INSERT, UPDATE, DELETE, and MERGE statements could only use parallelism on the read side of the execution plan; the data modifications were always serial (using a single task/worker/scheduler).

SQL Server 2014 Enterprise Edition (and equivalent Developer and Evaluation Editions) added the ability to do parallel writes for SELECT INTO statements only. SQL Server 2016 Enterprise Edition further adds parallel writes for some INSERT...SELECT statements.

All this means that SQL Server Express Edition can only use one scheduler (= one core) per client statement batch. Having four cores simply means that four separate statements (from different clients/connections) can be executing concurrently.

If the affinity mask bit is not set for a particular scheduler, Windows may choose which physical execution unit to run the scheduler on, and this may vary over timeslices. If the affinity mask bit is set, the scheduler is bound to a particular execution unit, and Windows cannot move it around.

It is still possible (in the right circumstances) for SQL Server Express to use multiple logical processors to concurrently bulk load a single unindexed heap table. This requires four separate statements (on four separate connections) targeting the same table, and specific conditions must be met. The details are contained in The Data Loading Performance Guide. There is an element of chance as well, in that the separate statements must be bound to different schedulers at connection time by SQL Server. That is a complex topic of its own.

I am not familiar with "MSSync", and the question doesn't state exactly what the task is, but in many cases achieving minimally-logged inserts is more than enough to meet performance objectives. Avoiding sorts on the server side is often an important factor as well. High CPU utilization is often an indication of server-side sorting when loading data. It is often avoidable.

A properly configured minimally-logged bulk load (without sorting) should saturate the server's I/O abilities before it runs out of CPU. Perhaps a follow up question could be asked with details of the exact task being performed. There may be other improvement options as well.


How to change data structure in mysql using mysqldump without deleting files Essentially what I'm trying to do is sync a production server with a sandbox server, but only the table structures and stored procedures. The procedures aren't any problem since they can be overriden, but the problem is the tables. I want to sync and alter their structures on the production server using mysqldump (or any other way that you can propose) without altering any existing data.

If it helps, I only want to add more columns, not remove any existing ones. Also, I am using mysqlyog.

Is there any way to do this?

As long as you don't rename tables and columns and you are not using partitions you might use schemasync .

Basic usage is:

schemasync mysql://user:pass@dev-host:3306/dev_db mysql://user:pass@prod-host:3306/prod_db

This will produce two SQL scripts - one for "patching" the production database, and another one for reverting the changes.

Please note that schema altering will lock the tables being affected, so you will have some downtime anyway. There are tools to avoid this: pt-online-schema-change from Percona and oak-online-alter-table from openarkkit.

Also, as with any possibly destructive changes, test the procedure on a staging (or other non-production use, but production data and schema) database.


reseed auto_increment to specific number? Is it possible to set the identity column of a table to start from a specific value?

I'm converting an ETL stage from mysql to a sql server database where a staging database loads records with a unique ID starting from the maximum value in the main database (which is not auto-incrementing). The mysql went like this:

SET @s = Concat('alter table table_1 auto_increment=',coalesce(@indexmaxt,0)+1);

Ideally this is what I want to do:

DBCC CHECKIDENT (table_1 , RESEED, @indexmaxt+1)

Is this possible or is this impossible in SQL server

This works, does that answer your question?

create table dbo.test (id int identity(1,1), value int);

declare @indexmaxt int = 9999,@indexmaxt2 int;
SET @indexmaxt2 = @indexmaxt+1;
DBCC CHECKIDENT ('dbo.test' , RESEED, @indexmaxt2);

insert into dbo.test(value) values(1);

select * from dbo.test;

Results:
id  10000
value
1


MySQL login after secure installation I have installed mysql5.5 via secure installation procedure by using:

sudo mysql_secure_installation

Now when I am trying to login in MySQL from the terminal by using:

sudo -u root -p

The following error is displayed:

  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I have also used:

sudo -u root

Which I got the following error:

  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

What should I do for this error?

You most likely have a .my.cnf file in your home directory, which is specifying a password.

The clue here is that when you did not specify a password (sudo -u root), the error message is still saying that you did.


What steps to take to resize an existing MYSQL NDB Cluster? I have an existing NDB Cluster. It's grown larger than I expected and I need to make it bigger. When I ask the management console for the ALL REPORT MEMORY, it looks like the datamemory is 65% full. However, I get page faults, swap, and memory related issues.

How do I find what my correct settings for data-memory, index-memory and any other related parameters should be?

Is ndb_size.pl the correct script to run?

Since your data nodes are up and running you can check the
size of the virtual memory allocated to the data node. If
this is bigger than 80-90% of your available memory you will
experience issues like you describe.


Understanding Logging Priority & Options in Oracle DB From my understanding once we switch on database logging, the entire DB goes into logging mode and generates redo logs.
I want to categorically exclude/include some tables/tablespaces from this logging as they are not required for recovery incase of failures.
Is there any priority on the logging options and to exclude certain tables/tablespaces from logging so as to reduce some of the traffic going to redo logs.

The database generates redo even without archivelog mode, I guess you meant enabling archivelog mode by logging.
What you asked for is not possible.
Conventional path DML will generate redo, it can not be turned on/off per tablespace.
Direct path loads (INSERT /*+ APPEND */, INSERT /*+ APPEND_VALUES */, CTAS, Data Pump import) and maintenance operations (like table move, index build) can bypass logging, but that is not your everyday workload.
You can exclude tablespaces from the backup, but you can not exclude them from redo.


How can I write a window function using something like row_number to increment only on discrete values? Say I want to do something like:

select
     my_date,
     row_number() over(partition by my_date order by my_date asc)
from
     my_table

I want the row numbers to come out as:

2018-10-01,1
2018-10-01,1
2018-10-02,2
2018-10-03,3

Not quite sure how to do this. With the current window function, it does:

2018-10-01,1
2018-10-01,2
2018-10-02,1
2018-10-03,1

You want dense_rank() instead of row_number()

select my_date,
       dense_rank() over(order by my_date asc)
from my_table


PostgreSQL SELECTs not returning correct result following recovery? I took a copy of the current database from a live slave using pg_basebackup and recovered it to a brand new isolated computer following the instructions in the docs. Once the new database had started up, I could see everything was present and had been restored OK, but some simple SELECT queries were failing (returning 0 results), specifically:

SELECT * FROM users WHERE email_address='me@example.com';

However, some email addresses did work. The fix for it was a REINDEX on the email address index. Now, the manual says in the caveats section that hash indexes aren't transferred across in the WAL logs, so any slaves won't get the updates and also following recovery, a REINDEX should be done, but my index is a B-Tree and nothing is mentioned about that.

What is happening?
I don't see this on the live slave (connected to master in streaming replication) at the minute, will I in the future?

Systems:

Live M/S: CentOS 6.6, with PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

Isolated Machine: OS X 10.1, PostgreSQL 9.3.5 on x86_64-apple-darwin13.3.0, compiled by Apple LLVM version 5.1 (clang-503.0.40) (based on LLVM 3.4svn), 64-bit

CentOS:

production=# explain analyse verbose select user_id from hive.users where email_address='me@example.com';

QUERY PLAN
----------------------------------------------
 Index Scan using users_email_address_key on hive.users  (cost=0.14..8.16 rows=1 width=4) (actual time=0.044..0.044 rows=1 loops=1)
   Output: user_id
   Index Cond: (users.email_address = 'me@example.com'::text)
 Total runtime: 0.093 ms
(4 rows)

production=# select user_id from hive.users where email_address='me@example.com';
 user_id
---------
       1
(1 row)

Mac OS X:

production=> explain analyse verbose select user_id from hive.users where email_address='me@example.com';

QUERY PLAN
----------------------------------------------
 Index Scan using users_email_address_key on hive.users  (cost=0.14..8.16 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=1)
   Output: user_id
   Index Cond: (users.email_address = 'me@example.com'::text)
 Total runtime: 0.031 ms
(4 rows)

production=> select user_id from hive.users where email_address='me@example.com';
 user_id
---------
(0 rows)

following the re-index:

production=# reindex index hive.users_email_address_key;
production=# explain analyse verbose select user_id from hive.users where email_address='me@example.com';
QUERY PLAN
------------------
 Seq Scan on hive.users  (cost=0.00..1.41 rows=1 width=4) (actual time=0.019..0.019 rows=0 loops=1)
   Output: user_id
   Filter: (users.email_address = 'me@example.com'::text)
   Rows Removed by Filter: 33
 Total runtime: 0.045 ms
(5 rows)

production=# select user_id from hive.users where email_address='me@example.com';
 user_id
---------
       1
(1 row)

The index data order on disk for text columns depends on the locales provided by the underlying operating system.

The same locales (that is, with the same name) may differ between operating systems on the order rules, even on simple things. As an example this question:
PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly
shows how "0102"and 0102sort differently on Ubuntu and FreeBSD.

The result in the physical index structure is that one value should come before the other in one OS and vice-versa in the other.

The problem with a recovered base backup is the same as with replication: the on-disk structure of the index is used as-is, resulting in a corrupted index in the target system if the column's locale doesn't sort exactly the same between systems.

It's mentioned in the comments that the origin system is CentOS and destination Mac OS X. Given that CentOS uses GNU libc and Mac OS X certainly a BSD-like libc, I'd think that there is no chance at all for on-disk postgres portability between these systems.


Connect Jira to Postgresql with Pgpool-II I gotta problem with connecting Jira 6 to Pgpool-II which is responsible for load balancing and  replication in Postgresql bases(2 instances).

Pgpool is started on port 9999, I just want to connect to it in this (Jira)installation step but Jira tolds that connection attempt failed....

Well ... check the pgpool logs and your servlet container logs.

I'd also try psql 'host=localhost port=9999 dbname=postgres username=postgres password=whatever'

Most likely the PgPool configuration has issues. If it works from psql, you'd need to check out the server logs to see what's up.


Mongo cluster - simple backup question I've got a mongo cluster with three nodes, running 2.6, but probably going to 3.2
What is the best method to back-up a database in a replica set of three mongodb servers?
Should I use mongodump --db [dbname] --out [path]?
And do I back-up from the primary node, or the slaves?
The database is about 30GB, so will the performance decrease during the backup of the other nodes?
I looked at the documentation here and here, but could not find my answer.

You could use:

backup software (for automated snapshots of your data),
mongodump/mongorestore,
or a simple copy of the \data\db directory to your backup location.

For option 2, make sure to use mongodump with the --oplog option. If you need to restore, then use mongorestore with the --oplogReplay option.

With option 3, stop the secondary member, and copy the the data out. Or, if you do not desire to stop the secondary, use db.fsyncLock(), backup and then use db.fsyncUnlock().

Secondaries replica set members are ideal for reporting and backup use cases.

Reference: https://docs.mongodb.com/v3.2/core/backups/


MySQL database for storing city bounding boxes and names I need to design a mysql database which contains a table of cities which supports only a single query - given a point, return the city (or cities) which it falls in. I have the bounding box for each city. What should the column look like to store the city bounding box to fully take advantage of mysql's spatial-indexing, multiple POINT columns? POLYGON? And what does the query look like.

Most discussion I've seen of spatial indexing involves the inverse question - given a bounding box, return the set of entries which have a POINT column which falls within the bounding box, which they are able to do using MBRWITHIN to obtain.

(Not a full answer, but too long for a Comment.)

With 200 cities with 'retangular' bounding boxes, virtually anything will do.  Even a simple table without a SPATIAL index, but with INDEX(latitude), INDEX(longitude).

3 million cities, on the other hand, needs some serious indexing.  SPATIAL, as you are asking about, is probably the 'right' way to do it.

Here is an efficient way to do it without SPATIAL, using PARTITIONing and special code instead.  (However, the code given is aimed at a different problem, so it is not directly applicable.)


I think what you want to do is rank the data based upon the smallest difference between the dates, per year - but you only need to consider any rows where date1 is greater that date2. For this, I would personally use a CTE to filter and rank the results before returning it like so:

WITH results
AS(
  SELECT id, date1, date2, value
       , RANK() OVER (PARTITION BY id, date1
                ORDER BY DATEDIFF(DAY, date1, date2) DESC) AS rnk
    FROM tblA
   WHERE date1 >= date2
)
SELECT DISTINCT id, date1, date2, value
  FROM results
 WHERE rnk = 1;

You can find the results on dbfiddle.

For more information see the official documentation of RANK and CTE.


postgresql: deleting from a view In Postgresql 9.4, we're doing a view as a UNION of to tables A and B, and we'd like to have a single DELETED N ROWS message when we 'delete' the records in the underlying tables (via a a trigger on the view).

Here's more detailed info. Our views look like,

A
--
id, name
1, afoo
2, abar

B
--
id, name
3, bfoo
4, bbar

So our view C looks like

C
--
1, afoo
2, abar
3, bfoo
4, bbar

We did a CREATE TRIGGER delete_trigger INSTEAD OF DELETE C FOR EACH ROW DO delete_trigger(), which delete the rows by id which basically its like

-- our function trigger():
$$
delete from A where id=old.id;
GET DIAGNOSTICS deletedA = ROW_COUNT;

delete from B where id=old.id;
GET DIAGNOSTICS deletedB = ROW_COUNT;

raise notice 'deleted % records from A, % records from B', deletedA , deletedB;

$$

If we issue a command like delete * from C;, our ideal goal would be to have a single message that said

deleted 2 records from A, 2 records from B

But instead we have - because we don't know better -,

deleted 1 records from A, 0 records from B
deleted 1 records from A, 0 records from B
deleted 0 records from A, 1 records from B
deleted 0 records from A, 0 records from B

Is it possible to get a single sum of deleted records?

We're also worried about the performance of this.

Any and all help greatly appreciated!

The triggered event is invoked for each row.

The syntax is

CREATE TRIGGER c_view_delete_trg INSTEAD OF DELETE
   ON c_view
   FOR EACH ROW
   EXECUTE PROCEDURE c_view_delete();

This means that the function is invoked once for each row that is being deleted. this is why it prints each time one row.


Is there any sense in using Postgres array of JSONB values instead of only JSONB? Is there any reason to use array of JSONB values instead of just raw JSONB?

My use case is that I need to store custom forms definition in list of JSON objects. And my question is whether it would be better to store it as raw JSON value or split this into separate JSONB objects.

What are pros and cons of each solution?

Generally, if you have an array of one thing (statically typed), it's better to store them as jsonb[] (a native SQL array). There are far more functions that operate over a SQL arrays, and indexes. It also tends to less overhead in storage. JSONb only has,

jsonb_array_length(jsonb)
jsonb_array_elements(jsonb)
jsonb_array_elements_text(jsonb)

While Array has a far more, but most of those features are implemented as operators.

Ultimately, in most use cases it won't matter much. You're better off normalizing your data. You really shouldn't be storing multiple json documents in one row. That sounds like a big "con" to begin with.


Any better way to rewrite the following "create table"? I have the following table:

CREATE TABLE Employee
(
    UID VARCHAR(10) NOT NULL,
    Name VARCHAR(20) NOT NULL,
    Type ENUM('Class_A', 'Class_B', 'Class_C') NOT NULL,
    Class_A_Speed ENUM('Fast', 'Medium', 'Slow') DEFAULT NULL

    PRIMARY KEY (UID)
);

Type defines three categories of employee (think departments). For "Class A" employees, we further categorize them based on how fast they work.

My concern is that, for all "Class A" employees, they should have a non-NULL speed; the other two classes should have Class_A_Speed set to NULL.

I can add constraints but I rather avoid it. I am trying to learn and this design seems very hacky to me...

Even on MySQL, you don't need to specify DEFAULT null. That's always the default, well usually always because MySQL is a special monster.

Use PostgreSQL.
UID should be a number, not varchar.
Class_A is pretty silly, even in an enum. If your classes are all one letter, just make them a char(1) or the like and just add "type" to the column name.
Class_A_Speed sounds like a function over a real speed, rather than a category. Just add the function to the database, class_from_speed(speed)

Like this,

CREATE TABLE employee (
  employee_id   int       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name          text,
  class_type    "char",   -- or char(1)
  speed         int
);


Joining 2 queries to match Months side by side I have 2 SQL statements that I want to join up. The first gets the actual data per month for Turnover accounts. The second query gets budgets (if any) for the same Turnover accounts.

Query 1 Actuals

/* Turnover 1 */
SELECT * FROM
(
SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
FROM OACT T0
LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
LEFT JOIN OBGT T2 ON T0.[AcctCode] = T2.[AcctCode]
WHERE T0.GroupMask = 4 AND T0.Levels = 5
AND (T1.RefDate BETWEEN '20150101' AND '20151231')
GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate) AS q
PIVOT
(
    SUM(Amount)
    FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

Sample data

Query 2 Budgets

/* Turnover 1 */
SELECT * FROM
(
SELECT T0.AcctCode AS 'AcctCode', T3.AcctName AS 'Description', T3.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0) THEN T1.DebLTotal ELSE T1.CredLTotal END AS BudgetAmt, T1.Line_ID AS 'LineID'
FROM OBGT T0 INNER JOIN BGT1 T1 ON T0.[AbsId] = T1.[BudgId]
INNER JOIN OACT T3 ON T0.[AcctCode] = T3.[AcctCode]
WHERE T3.GroupMask = 4) AS q
PIVOT
(
    SUM(BudgetAmt)
    FOR LineID IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])
) AS P

Sample Data

For the 2nd query, 0 represents Jan, 1 represents Feb ... and 11 represents December.

How do I combine the 2 queries such that I can get data such as below?

Not a duplicate as this contains a query to retrieve budgets.

use a union all

the same way you have used in your other question, combine 2 queries

the idea is exactly the same.

All you have to do is

select * from
(
select [set of columns]
from query1
union all
select [same set of columns as before]
from query2

) as Q
PIVOT
(
SUM(Amount)
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

I think,
if I understood it right, I got it modified for you, see the picture below.

It is a UNION ALL plus a CASE.

creating the tables to get the exercise done was a bit troublesome.

    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    declare @JDT1 table(

    ContraAct char(6) primary key clustered,
    Account int,
    RefDate datetime,
    credit money,
    debit money

    )

    declare @OACT table(
    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int
    )

    declare @OBGT table(
    AcctCode int primary key clustered,
    AbsId int,
    CredLTotal int,
    DebLTotal int,
    Line_ID int
    )

    declare @BGT1 table(

    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int,
    CredLTotal int,
    DebLTotal int,
    Line_ID int

    )

    insert into @BGT1 values  (1,4,'London','412100',5,1,100,70,1)
    insert into @BGT1 values  (2,4,'London','412100',5,1,100,70,2)
    insert into @BGT1 values  (3,4,'London','412100',5,1,100,70,3)
    insert into @BGT1 values  (4,4,'London','412100',5,1,100,70,4)
    insert into @BGT1 values  (5,4,'London','412100',5,1,100,70,5)
    insert into @BGT1 values  (6,4,'London','412100',5,1,100,70,6)
    insert into @BGT1 values  (7,4,'London','412100',5,1,100,70,7)
    insert into @BGT1 values  (8,4,'London','412100',5,1,100,70,8)
    insert into @BGT1 values  (9,4,'London','412100',5,1,100,70,9)
    insert into @BGT1 values  (10,4,'London','412100',5,1,100,70,10)
    insert into @BGT1 values  (11,4,'London','412100',5,1,100,70,11)
    insert into @BGT1 values  (12,4,'London','412100',5,1,100,70,0)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311202',1,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311203',1,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311204',2,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311205',2,'20151231',10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311206',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311207',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311208',2,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311209',2,'20151031',10,7)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (2,4,'London','412100',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (3,4,'New York','412101',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (4,4,'Rome','412102',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (5,4,'Paris','412103',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (6,4,'Moscow','412103',5,1)

    update @BGT1
    set GroupMask  = 4

    update @OACT
    set GroupMask  = 4

    insert into @OBGT
    select
    AcctCode,
    AbsId,
    CredLTotal,
    DebLTotal,
    Line_ID
    from @BGT1

    --select * from @OBGT

    --select * from @OACT

    --select * from @BGT1

    SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
    MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
    FROM @OACT T0
    LEFT JOIN @JDT1 T1 ON T0.[AcctCode] = T1.[Account]
    --LEFT JOIN @OBGT T2 ON T0.[AcctCode] = T2.[AcctCode] -- this is not being used here
    WHERE T0.GroupMask = 4 AND T0.Levels = 5
    AND (T1.RefDate BETWEEN '20150101' AND '20151231')
    GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate

    SELECT T0.AcctCode AS 'SAP Code',
           T3.AcctName AS 'Description',
           T3.FatherNum, 1 AS 'Group',
           'Turnover' [Definition],

            T1.Line_ID+1 AS 'Month',

           CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0)
THEN T1.DebLTotal ELSE T1.CredLTotal END AS Amount

          FROM @OBGT T0
    INNER JOIN @BGT1 T1 ON T0.[AbsId] = T1.[AbsId]
    INNER JOIN @OACT T3 ON T0.[AcctCode] = T3.[AcctCode]
    WHERE T3.GroupMask = 4

    SELECT * FROM
    (

    SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
    MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
    FROM @OACT T0
    LEFT JOIN @JDT1 T1 ON T0.[AcctCode] = T1.[Account]
    --LEFT JOIN @OBGT T2 ON T0.[AcctCode] = T2.[AcctCode] -- this is not being used here
    WHERE T0.GroupMask = 4 AND T0.Levels = 5
    AND (T1.RefDate BETWEEN '20150101' AND '20151231')
    GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate

    UNION ALL

    SELECT T0.AcctCode AS 'SAP Code',
           T3.AcctName AS 'Description',
           T3.FatherNum, 1 AS 'Group',
           'Turnover' [Definition],

            T1.Line_ID+1 AS 'Month',

           CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0)
           THEN T1.DebLTotal ELSE T1.CredLTotal END AS Amount

          FROM @OBGT T0
    INNER JOIN @BGT1 T1 ON T0.[AbsId] = T1.[AbsId]
    INNER JOIN @OACT T3 ON T0.[AcctCode] = T3.[AcctCode]
    WHERE T3.GroupMask = 4

    ) AS q
    PIVOT
    (
        SUM(Amount)
        FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS query

I believe, if I understood well, you want something like the picture above.

if that is not right, then, please create a picture of what you need, even if you do it in excel, but try to use the values according to the tables in the script below. Or post your table definitions with some scripts inserting data into them, then we would be definitely on the same page.

Also, I have not included all of your columns, if they are really important you can add them to the script below.

 SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    declare @JDT1 table(

    ContraAct char(6) primary key clustered,
    Account int,
    RefDate datetime,
    credit money,
    debit money

    )

    declare @OACT table(
    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int
    )

    declare @OBGT table(
    AcctCode int primary key clustered,
    AbsId int,
    CredLTotal int,
    DebLTotal int,
    Line_ID int
    )

    declare @BGT1 table(

    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int,
    CredLTotal int,
    DebLTotal int,
    Line_ID int

    )

    insert into @BGT1 values  (1,4,'London','412100',5,1,100,70,1)
    insert into @BGT1 values  (2,4,'London','412100',5,1,100,70,2)
    insert into @BGT1 values  (3,4,'London','412100',5,1,100,70,3)
    insert into @BGT1 values  (4,4,'London','412100',5,1,100,70,4)
    insert into @BGT1 values  (5,4,'London','412100',5,1,100,70,5)
    insert into @BGT1 values  (6,4,'London','412100',5,1,100,70,6)
    insert into @BGT1 values  (7,4,'London','412100',5,1,100,70,7)
    insert into @BGT1 values  (8,4,'London','412100',5,1,100,70,8)
    insert into @BGT1 values  (9,4,'London','412100',5,1,100,70,9)
    insert into @BGT1 values  (10,4,'London','412100',5,1,100,70,10)
    insert into @BGT1 values  (11,4,'London','412100',5,1,100,70,11)
    insert into @BGT1 values  (12,4,'London','412100',5,1,100,70,0)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311202',1,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311203',1,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311204',2,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311215',4,'20151231',5,2)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311216',4,'20151231',5,3)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311219',4,'20151030',5,3)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311217',5,'20151231',8,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311218',6,'20151231',4,6)

        insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311205',2,'20151231',10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311206',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311207',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311208',2,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311209',2,'20151031',10,7)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (2,4,'London','412100',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (3,4,'New York','412101',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (4,4,'Rome','412102',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (5,4,'Paris','412103',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (6,4,'Moscow','412103',5,1)

    update @BGT1
    set GroupMask  = 4

    update @OACT
    set GroupMask  = 4

    insert into @OBGT
    select
    AcctCode,
    AbsId,
    CredLTotal,
    DebLTotal,
    Line_ID
    from @BGT1

    --select * from @OBGT

    --select * from @OACT

    --select * from @BGT1

    declare @months table(
     i smallint primary key clustered,
     name varchar(25))

     insert into @months
     select 1,'jan'
        union all select
            2, 'feb'
        union all select
            3, 'mar'
        union all select
            4, 'apr'
        union all select
            5, 'may'
        union all select
            6, 'jun'
        union all select
            7, 'jul'
        union all select
            8, 'aug'
        union all select
            9, 'sep'
        union all select
            10, 'out'
        union all select
            11, 'nov'
        union all select
            12, 'dec'

SELECT
 K.[SAP Code]
,K.Description,
[jan_Budget],
[jan_Amount],[feb_Budget],
[feb_Amount],[mar_Budget],
[mar_Amount],[apr_Budget],
[apr_Amount],[may_Budget],
[may_Amount],[jun_Budget],
[jun_Amount],[jul_Budget],
[jul_Amount],[aug_Budget],
[aug_Amount],[sep_Budget],
[sep_Amount],[out_Budget],
[out_Amount],[nov_Budget],
[nov_Amount],[dec_Budget],
[dec_Amount]

FROM

(
    SELECT A.*
      FROM (

                SELECT K.[SAP Code]
                      ,K.[Description]
                      ,AMOUNT = COALESCE(K.Amount,0)
                      ,PIVOT_KEY = M.NAME + '_Amount'
                FROM @months M
                     LEFT OUTER JOIN

                        (   SELECT T0.AcctCode AS 'SAP Code',
                                   T0.AcctName AS 'Description',
                                   T0.FatherNum, 1 AS 'Group',
                                   'Turnover' AS [Definition],
                            MONTH(T1.RefDate) AS 'Month',
                            SUM(T1.Debit - T1.Credit) AS 'Amount'
                            FROM @OACT T0
                            LEFT JOIN @JDT1 T1 ON T0.[AcctCode] = T1.[Account]
                            WHERE T0.GroupMask = 4 AND T0.Levels = 5
                            AND (T1.RefDate BETWEEN '20150101' AND '20151231')
                            GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate
                        ) K
                        ON K.Month = M.i

           ) RADHE
    PIVOT (SUM([AMOUNT]) FOR PIVOT_KEY IN ([jan_Amount],[feb_Amount],[mar_Amount],
                                         [apr_Amount],[may_Amount],[jun_Amount],
                                         [jul_Amount],[aug_Amount],[sep_Amount],
                                         [out_Amount],[nov_Amount],[dec_Amount])) A
    where [SAP Code] is not null

) K

INNER JOIN

(

SELECT *
  FROM (

              SELECT K.[SAP Code]
                  ,K.[Description]
                  ,AMOUNT = COALESCE(K.Amount,0)
                  ,PIVOT_KEY = M.NAME + '_Budget'
            FROM @months M
                 LEFT OUTER JOIN

                    (

                        SELECT T0.AcctCode AS 'SAP Code',
                               T3.AcctName AS 'Description',
                               T3.FatherNum, 1 AS 'Group',
                                T1.Line_ID+1 AS 'Month',

                               CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0)
                    THEN T1.DebLTotal ELSE T1.CredLTotal END AS Amount

                              FROM @OBGT T0
                        INNER JOIN @BGT1 T1 ON T0.[AbsId] = T1.[AbsId]
                        INNER JOIN @OACT T3 ON T0.[AcctCode] = T3.[AcctCode]
                        WHERE T3.GroupMask = 4

                    ) K
                    ON K.Month = M.i

       ) RADHE
PIVOT (SUM([AMOUNT]) FOR PIVOT_KEY IN ([jan_Budget],[feb_Budget],[mar_Budget],
                                     [apr_Budget],[may_Budget],[jun_Budget],
                                     [jul_Budget],[aug_Budget],[sep_Budget],
                                     [out_Budget],[nov_Budget],[dec_Budget])) B

) R
ON 1=1
AND  K.[SAP Code] = R.[SAP Code]


Using CROSS JOIN LATERAL

The output is similar to your output, but please check again. Here's, I using lateral (as loop) to find the max value of someNume for the 3 surrounding days (it doesn't mention about performance).

SELECT tbl.date, tbl.somenum, tbl.name, tbl.elem, t2.max_somenum
FROM tbl
CROSS JOIN LATERAL (
  SELECT MAX(t2.somenum) AS max_somenum
  FROM tbl t2
  WHERE t2.date >= (tbl.date - interval '3 days') AND t2.date <= (tbl.date + interval '3 days')
) t2
ORDER BY date;


How to purge an already dropped table? I dropped a table but didn't purge it. How can I purge it, so I can release the tablespace that the table uses?

PURGE TABLE test;

or

PURGE RECYCLEBIN;

From docs:

  The following statement removes the table test from the recycle bin.
  If more than one version of test resides in the recycle bin, Oracle
  Database removes the version that has been there the longest:

PURGE TABLE test;

  To determine system-generated name of the table you want removed from
  your recycle bin, issue a SELECT statement on your recycle bin. Using
  that object name, you can remove the table by issuing a statement
  similar to the following statement. (The system-generated name will
  differ from the one shown in the example.)

PURGE TABLE RB$$33750$TABLE$0;

  Remove the Contents of Your Recycle Bin:

  Example  To remove the entire contents of your recycle bin, issue
  the following statement:

PURGE RECYCLEBIN;

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9018.htm


How should I model my database - cannot decide on multiple options I apologize if this might be a rather beginner-level-question.
For my database design, I have worked out multiple options but cannot decide on which one I should implement. I am using 10.4.17-MariaDB.
The basic idea:
I have a Table called Article in which I want to store simple fields such as name (e.g. varchar), description (e.g. varchar), ..., etc. also more complex information like Producer, Price and so on.
I have worked out the following:
Implementation I (using just a FK)

Table: Article
| id (auto_inkr.) | fk_producer | ... |
| --------------- | ----------- | --- |
| 1               | 1           | ... |
| 2               | 1           | ... |
| 3               | 2           | ... |
| ...             | ...         | ... |

As defined, Producer(id: 1), is the Producer of Articles (1, 2, ...?).
Implementation II (using a lookup table)

Table: Article
| id (auto_inkr.)   | fk_article_producer | ... |
| ----------------- | ------------------- | --- |
| 1                 | 1                   | ... |
| 2                 | 1                   | ... |
| 3                 | 2                   | ... |
| ...               | ...                 | ... |

Table: Article Producer
| id (auto_inkr.)   | article_fk  | producer_fk |
| ----------------- | ----------- | ----------- |
| 1                 | 1           | 1           |
| 2                 | 2           | 1           |
| 3                 | 3           | 2           |
| ...               | ...         | ...         |

In this case here, I have implemented a mapping table. This would also allow me to have a many-to-many configuration. I don't have to implement it that way - it is up to my backend-code how I store the data.
Questions
Please keep in mind, that my Article table as of now will have like 7+ dependent tables (as shown in the example here: e.g. Producer). I want to design it, so I can write an efficient filter-function (query) for my text- respectively tag-based-search later on.

Which design should I choose and why?
What are the benefits or downsides of these approaches (best practice)?

Design 2 two is needed when here are multiple producers per article, you need a bridge table between article and producer, so a producer can have multiple articles and an article can have multiple producers
your first is only valid for 1 to 1 reöationships.
so what you you t have 1:1 or m:n
Thet will determine, what you need


HOW to impliment SQL 2012 send_dbmail with OAuth2 Situation: we currently send emails from our sql 2012/asp app using the msdb.dbo.sp_send_dbmail process and implement multiple different domain accounts. It works (worked) well for us to send authorised emails from our application on behalf of our customers. Now that OAuth2 is being properly maintained, we now need to be able to connect to email accounts that are implementing OAuth2 for Authentication (or any of the other Authentication protocols eg encrypted password). Is this possible? Is the answer a different email agent? Who as solved this and how?

Is the answer a different email agent?

Yep. If you wanted to do it inside of SQL Server you could write some CLR code, but I wouldn't advise it.


FreeTextTable - Query as a set operation vs. having to cursor Does anyone know if it is possible to join to a free text table to query for many search terms in one operation vs. having to cursor over?

For example if I have a function that gets the top 1 closest match using FreeTextTable:

create function FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL (
     @classCodeDescription  varchar(4000)
    ,@state                 varchar(10)
    ,@classCode             int
    ,@effectiveOn           datetime2)
returns table as
return

select KEY_TBL.RANK, cd.CLASS_CODE_DESCRIPTION_ID
from CLASS_CODE_DESCRIPTION cd
  join CLASS_CODE cc on cc.CLASS_CODE_ID = cd.CLASS_CODE_ID
  inner join freetexttable (
      CLASS_CODE_DESCRIPTION, DESCRIPTION, @classCodeDescription, 1) as KEY_TBL
      on cd.CLASS_CODE_DESCRIPTION_ID = KEY_TBL.[KEY]
where cc.CLASS_CODE = @classCode
  and cc.STATE = @state
  and cc.EFFECTIVE_DATE <= @effectiveOn
  and cc.EXPIRATION_DATE > @effectiveOn
  and cd.EFFECTIVE_DATE <= @effectiveOn
  and cd.EXPIRATION_DATE > @effectiveOn

I then have a table that I would like to join against, to avoid having to do a painfully slow cursor through (there can be thousands upon thousands of rows)

declare @tbl table (
 classCode      int not null
,description    varchar(255) not null
,state          varchar(10) not null default 'FL'
,effectiveOn    datetime2 not null default '1999-01-01 00:00:00')

insert into @tbl (classCode, description)
values (9410, 'Municipal Employees'), (6, 'Farm: Fish Hatchery & Drivers')

I then do the query:

select *
from @tbl t
  cross apply dbo.FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL
  (t.description, t.state, t.classCode, t.effectiveOn)

But receive the error:

  Msg 4129, Level 16, State 1, Line 13 The inline function
  "dbo.FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL" cannot take
  correlated parameters or subqueries because it uses a full-text
  operator.

I have tried it several different ways, but ultimately I am unable to trick out the engine because they all really boil down to the same thing under the covers.

Is there a way to run FreeTextTable in a set operation vs. having to cursor through it?

Hmm... try this "trick" for your function FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL to correlate with a SQL table variable rather than the freetext query:

create function FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL (
     @classCodeDescription  varchar(4000)
    ,@state                 varchar(10)
    ,@classCode             int
    ,@effectiveOn           datetime2)
RETURNS @ClosestClassCodeDesc TABLE
(
     TBLRANK INT,
     CLASS_CODE_DESCRIPTION_ID  varchar(4000)
)
as
begin
    insert into @ClosestClassCodeDesc (TBLRANK, CLASSname)
    select KEY_TBL.RANK, cd.CLASS_CODE_DESCRIPTION_ID from CLASS_CODE_DESCRIPTION cd
  join CLASS_CODE cc on cc.CLASS_CODE_ID = cd.CLASS_CODE_ID
  inner join freetexttable (CLASS_CODE_DESCRIPTION, DESCRIPTION, @classCodeDescription, 1) as KEY_TBL on cd.CLASS_CODE_DESCRIPTION_ID = KEY_TBL.[KEY]
where cc.CLASS_CODE = @classCode
  and cc.STATE = @state
  and cc.EFFECTIVE_DATE <= @effectiveOn
  and cc.EXPIRATION_DATE > @effectiveOn
  and cd.EFFECTIVE_DATE <= @effectiveOn
  and cd.EXPIRATION_DATE > @effectiveOn
return
end


How can I get the minimum sum value of fields without repeating where clause? How can I get the minimum value after adding value of 2 fields without repeating the where clause?

Example: Get records with minimum value of num1 + num2 and deleted_at is null

id          num1        num2       deleted_at
----------  ---------   ---------  ---------
1           1           1          2015-01-01
2           2           2          null
3           2           2          null
4           4           4          null

Expected result is

id          num1        num2       deleted_at
----------  ---------   ---------  ---------
2           2           2          null
3           2           2          null

I tried subquery, but I have to repeat the where clause

SELECT *
FROM   foos t1
WHERE  t1.num1 + t1.num2 = (SELECT Min(t2.num1 + t2.num2)
                            FROM   foos t2
                            WHERE  t2.deleted_at IS NULL) //repeat
       AND t1.deleted_at IS NULL //repeat

In Sql Server , you can do it with RANK

CREATE TABLE foos
    ([id] int, [num1] int, [num2] int, [deleted_at] datetime)
;

INSERT INTO foos
    ([id], [num1], [num2], [deleted_at])
VALUES
    (1, 1, 1, '2015-01-01'),
    (2, 2, 2, NULL),
    (3, 2, 2, NULL),
    (4, 4, 4, NULL)
;

--the select

SELECT [id], [num1], [num2]
FROM
(
 SELECT [id], [num1], [num2]
  , RANK() OVER(ORDER BY [num1]+ [num2] ASC) as rk
 FROM foos
 WHERE [deleted_at] IS NULL
)as r
WHERE r.rk = 1

output:

id  num1    num2
2      2    2
3      2    2

dbfiddle here


Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes I try installing SQL2008R2 - SQL2012 - sql2014

2014-07-17 16:31:16.00 spid14s     Error: 17190, Severity: 16, State: 1.
2014-07-17 16:31:16.00 spid14s     Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.
2014-07-17 16:31:16.00 spid14s     Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
2014-07-17 16:31:16.00 spid7s      Starting up database 'msdb'.
2014-07-17 16:31:16.01 spid12s     Starting up database 'mssqlsystemresource'.
2014-07-17 16:31:16.01 spid14s     Error: 17182, Severity: 16, State: 1.
2014-07-17 16:31:16.01 spid14s     TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
2014-07-17 16:31:16.01 spid14s     Error: 17182, Severity: 16, State: 1.
2014-07-17 16:31:16.01 spid14s     TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
2014-07-17 16:31:16.01 spid14s     Error: 17826, Severity: 18, State: 3.
2014-07-17 16:31:16.01 spid14s     Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2014-07-17 16:31:16.01 spid14s     Error: 17120, Severity: 16, State: 1.
2014-07-17 16:31:16.01 spid14s     SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Following can be reason for this error

The account which user selected on Server Configuration page window ( during installation)  is somehow not able to bring SQL Server database engine services online. Either is lacks privilege or it is corrupted. During installation of database engine services SQL Server tries to bring online database services as a internal process but due to startup account either corrupt or not having appropriate privileges it fails to do so and ultimately installation fails.

Other reason is when installation fails first time due to some reason and user uninstall failed installation from add remove program, the un-installation leaves account in broken state so any further attempt to install flashes this error message.

Solution:

Uninstall SQL Server completely from Add remove program and start new installation by right click on Setup and selecting run as admin. Now when you reach Server Configuration page please select NT Authority\System as SQL Server Database Engine service startup account.

http://social.technet.microsoft.com/wiki/contents/articles/24364.sql-server-troubleshooting-could-not-find-database-engine-startup-handle-error-during-installation.aspx

PS: If this does not helps please revert. Please follow steps mentioned in link carefully


Postgres: understanding amount of WAL files/size generated I'm constantly caught by surprised how certain operations generate me huge amount of WAL files.

I want these WAL files for point in time recovery (I also perform a nightly full dump in addition) so the basic functionality provided is wanted and I don't want to change that (i.e. I'm not searching for a way to turning WAL archives off, etc.)

Using Postgres 9.5 with these settings:

wal_level = archive
checkpoint_timeout = 20min
max_wal_size = 1GB
min_wal_size = 80MB
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'

I had run this statement today:

WITH table2_only_names AS (
    SELECT id , name FROM table2
)
UPDATE table1
SET table2_name = table2_only_names.name
FROM table2_only_names
WHERE table1.table2_id = table2_only_names.id;

Table1

CREATE TABLE public.table1 (
  id BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('table1_id_seq'::regclass),
  table2_id BIGINT,
  table3_id BIGINT,
  positive_count INTEGER NOT NULL DEFAULT 0,
  neutral_count INTEGER NOT NULL DEFAULT 0,
  negative_count INTEGER NOT NULL DEFAULT 0,
  is_blocked BOOLEAN NOT NULL DEFAULT false,
  blocker_id BIGINT,
  group BIGINT,
  created TIMESTAMP WITH TIME ZONE,
  modified TIMESTAMP WITH TIME ZONE,
  table4_id BIGINT,
  name CHARACTER VARYING(255)
);
CREATE UNIQUE INDEX idx1 ON table1 USING BTREE (table3_id, table2_id);
CREATE INDEX idx2 ON table1 USING BTREE (table3_id);
CREATE INDEX idx3 ON table1 USING BTREE (table2_id);
CREATE INDEX idx4 ON table1 USING BTREE (group);
CREATE INDEX idx5 ON table1 USING BTREE (blocker_id);
CREATE INDEX idx6 ON table1 USING BTREE (table4_id);

15 Mio rows, table size ~3,4GB, index size 7GB

Table2

CREATE TABLE public.table2 (
  id BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('table2_id_seq'::regclass),
  name CHARACTER VARYING(255),
);

10 Mio rows, table size ~2GB, index size 3,4GB

The runtime was about 55 minutes (not complaining here).

The amount of generated WAL files was unexpectedly huge. The aforementioned WAL archives are on a dedicated partition with has 100GB and at the point the query started, around 30GB were free.

It was not enough as after 15-20 minutes disk space was <10GB and I started to delete "older" WAL archives. I had to do this constantly up to the point were I was pretty sure I already had to delete WAL archive files generated by this very statement.

The tables in question were not used by any other process during that time but "normal" operations of other tables continued.

I've marked the time where I started the query and where it ended. You can clearly see where I deleted the WAL archive files :-)

To me, why so many are generated is a mystery and it currently is a problem because it's hardly foreseeable how much is needed and when operation strike which need one.

What am I missing to better understand how much space is needed?
Are these things avoidable?
Am I doing something wrong?

When you update a row in PostgreSQL, it generally makes a copy of the entire row (not just the column that was updated) and marks the old row as deleted.  The new copy is going to need to get WAL logged in its entirety.  The old row is probably also going to be WAL logged in its entirety, on average, if you have full_page_writes turned on and you are checkpointing too closely together.

Almost all of the updated rows are probably going to need to update all of the indexes for it, as well.  That is because the new version of the row won't fit on the same page as the old version, so the indexes have to know where to find the new version.

So you are logging the entire table twice (once for the old rows, once for the new ones) and all if its indexes as well.  And WAL records have quite a bit of overhead.  And if you have full_page_writes turned on and checkpoint frequently, that will make it even worse.

So what are your options to reduce the volume?

1) If many of your updates are degenerate (updated to the value they already have) you can suppress those updates with an additional where clause:

WITH table2_only_names AS (
    SELECT id , name FROM table2
)
UPDATE table1
SET table2_name = table2_only_names.name
FROM table2_only_names
WHERE table1.table2_id = table2_only_names.id
AND table2_name is distinct from table2_only_names.name;

2) Most WAL files are extremely compressible.  You can include a compresssion command in your archive_command, something like

 archive_command = 'set -C -o pipefail; xz -2 -c %p > /backup/wal/%f.xz'

Of course you will have to make your recovery_command do the reverse.

3) Since you are using 9.5, you can try turning wal_compression on.

4) You could try turning off full_page_writes, although this does but your data at risk of corruption in the case of a crash, on most storage hardware.  Or, if you have frequent checkpoints during this operation you could make checkpoints occur much less frequently, which will lessen the impact of having full_page_writes turned on.


disallow SET command in a postgresql server Im planning to publish my postgresql server to a few untrusted clients.

I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the pg_catalog schema is public but I don't want to allow updating pg_settings at all.

Is it possible?

I agree with Tometzky, just want to add some thoughts.

Firstly, if I had to publish my DB server, I would separate it from my own servers (or server processes) - either physically, if possible, or putting the public DB server into a VM, which is constrained in its resource consumption to a degree when it can't really affect other processes on the same physical machine.

Secondly, limiting changes to resource-related configuration parameters can be only of limited use.  One can set work_mem, for example, to a fairly high value, and make huge sorts, but this will not affect the overall server performance more than, say, a four-way Cartesian product of one million rows each - which you can't prevent by any means...

And, unfortunately, I don't know any means of modifying a session's settings from an other session (be the latter connected with the postgres user).  You can't even really see (to my knowledge) the changed settings of the other session.  One possibility to control the usage of the public servers is checking resource consumption at the OS level, and kill the processes that show signs of abuse.  This is, of course, not really friendly, but may be useful as a last resource.


Availability groups using multiple subnets changes I have a question about using multiple subnets in a AlwaysOn Availability group. We have a AlwaysOn setup (SQL 2012) already in production that has a listener that is configured for 2 subnets. 1 of these is local and the other is used for DR. Obviously, we want to avoid anything being routed through the DR subnet on everyday use. We had an issue where when we first did this that we experienced timeouts. The ways to overcome this is well-documented and we have implemented fixes and looking at some of the alternate ways.

Primarily, we are using “MultiSubnetFailover=True” in our connection strings, but we are looking at modifying the RegisterAllProviderIP cluster resource setting to 0 due to some limitations on the MultiSubnetFailover parameter to handle this and then just working around the slower cutover time as a result. The only other subnet we are using is DR so I think this is manageable.

My question though is if AlwaysOn changed the way connections are made in any of the newer versions of SQL (or .NET Framework) that prevents the need to do some of this. We are currently planning to upgrade the coming year to SQL 2016 and at the same time move to AlwaysOn AG. Is “MultiSubnetFailover=True” or changing the RegisterAllProviderIP still required in SQL 2016 or is any of this handled automatically\different?

Most of the information regarding this is specific to SQL 2012 and I cannot seem to find information that definitively says if this applies for all versions. I did find some articles that mentioned this “MultiSubnetFailover=True” is handled automatically, but not sure if that was just application specific or product specific. Thanks.

... but we are looking at modifying the RegisterAllProviderIP cluster resource setting to 0 due to some limitations on the MultiSubnetFailover parameter ...

Could you let me know what those limitations are? AFAIK there aren't any... MultiSubnetFailover is the defacto way to deal with your issue. Period. Hence why I would be interested to know what the limitations of the parameter are.

  My question though is if AlwaysOn changed the way connections are made in any of the newer versions of SQL (or .NET Framework) that prevents the need to do some of this.

No, there are no changes in SQL Server. There is a change in .NET 4.6.1 that will use multisubnetfailover logic if the initial connection doesn't happen in the first 500 miliseconds if multisubnetfailover was not added to the connection string.

  Is “MultiSubnetFailover=True” or changing the RegisterAllProviderIP still required in SQL 2016 or is any of this handled automatically\different?

You should ALWAYS use multisubnetfailover=true unless the client driver does not support it... in which case, upgrade the client driver and/or application... or deal with the application timing out.


Moving the MySQL data directory Ubuntu 16.04 OS: Ubuntu 16.04

I am trying to move the MySQL data directory to another location. Here's what I did:

mysql -u root -p

select @@datadir;
Output
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+

sudo systemctl stop mysql

mkdir /data

sudo rsync -av /var/lib/mysql /data

I then verified that a /data/mysql/mysql directory was created and all the data was moved there.

Then I modified the /etc/mysql/mysql.conf.d/mysqld.cnf file:

datadir=/data/mysql

I then modified /etc/apparmor.d/tunables/alias. At the bottom of the file, added the following alias rule:
    alias /var/lib/mysql/ -> /data/mysql/,

then:

sudo systemctl restart apparmor

No errors so far.

I then did:

sudo mkdir /var/lib/mysql/mysql -p

followed by:

sudo systemctl start mysql

This gave me the following error message:

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

systemctl status mysql.service

gives me:

mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: activating (start-post) (Result: exit-code) since Thu 2016-09-01 08:09:04 UTC; 10s ago
  Process: 20380 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 20377 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 20380 (code=exited, status=1/FAILURE);         : 20381 (mysql-systemd-s)
    Tasks: 2
   Memory: 336.0K
      CPU: 221ms
   CGroup: /system.slice/mysql.service
           └─control
             ├─20381 /bin/bash /usr/share/mysql/mysql-systemd-start post
             └─20430 sleep 1

Sep 01 08:09:04 db.msrv.com systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Sep 01 08:09:04 db.msrv.com systemd[1]: Stopped MySQL Community Server.
Sep 01 08:09:04 db.msrv.com systemd[1]: Starting MySQL Community Server...
Sep 01 08:09:06 db.msrv.com systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

Any ideas?

Verify the existing MySQL installtion:

mysql -u root -p

From the MySQL prompt, do:

select @@datadir;

You should see the current data directory:

/var/lib/mysql/

Exit the MySQL mode:

exit

Stop MySQL:

sudo systemctl stop mysql

Verify that MySQL is stopped:

sudo systemctl status mysql

Copy the data to the new location:

sudo rsync -av /var/lib/mysql /data/newlocation

(or whatever volume you've created to store the data)
Create a backup of the existing data volume

sudo mv /var/lib/mysql /var/lib/mysql.bak

Tell MySQL about the new location. Edit the file:

/etc/mysql/mysql.conf.d/mysqld.cnf

and change the datadir directive, to reflect the new location:

datadir=/data/newlocation

Tell AppArmor about it: edit the file /etc/apparmor.d/tunables/alias  and add:

alias /var/lib/mysql/ -> /data/newlocation/,

to the bottom of that file (the comma at the end of the line is NOT a typo).

Then do:

sudo systemctl restart apparmor

Create the minimal directory structure, to pass the mysql-systemd-start checks:

sudo mkdir /var/lib/mysql/mysql -p

Restart MySQL

sudo systemctl start mysql

Check status:

sudo systemctl status mysql

Verify that the new data directory is active:

mysql -u root -p

At the MySQL prompt, enter:

SELECT @@datadir;

Remove backup, once everything is verified to work:

sudo rm -Rf /var/lib/mysql.bak

Restart MySQL

sudo systemctl start mysql

Check status:

sudo systemctl status mysql


ntwdblib.dll : How to configure it to connect over tcpip ? (SQL Server, legacy) We have an old app that uses ntwdblib.dll to connect to sql server.

It works, but it appears to connect over named pipes.

Due to moving the sql server to a data center, we need to change this so it connects over tcpip.

Currently, we connect by connecting to:  < server name>\< instance name>

We tried putting just the server name, and < server name>,1433  and that did not work.

How do we specify/configure for this lib to use tcpip?

Try <server name>, <port that instance is listening on> as explained here.

In most cases, a named instance is not going to be listening on port 1433. In order to connect over TCP/IP, you need to explicitly specify the port number. You can find this in SQL Server Configuration Manager - under SQL Server Network Configuration, Protocols for <Instance Name>, right-click TCP/IP and hit Properties. The port number is near the bottom under IPAll:

In my case, I'm using dynamic ports, which means a different port could be assigned next time the service starts. You can force a specific, static port in the box below - hit Apply, then OK, then restart the service. You can try to use 1433 here, which might work if this is the only instance on the machine, but I haven't tried to do this for app compatibility reasons - I've always tried to make changes to the app configuration rather than SQL Server.

Then change your app to use <server name>, <port number>.


MySQL NDBcluster 7.3 limitation on Table Joining in a single SQL statement I am a novice user of Mysql database and recently my company is evaluating MySQL Cluster for its shared nothing architecture and synchronised replication.

However my colleagues informed me that the MySQL clusters will have limitation on the number of table joins you can do on one SQL statement that is only joining not more than 5 tables. He went to a MySQL training 1 or 2 years back, and I could not find any article or documentation on this matter.

  My web application usually query from 5-6 tables in a single SQL
  statement. A table usually has > 200 columns and not more than 3
  columns as combination Primary Key. The table tuples has more than 10
  million in extreme case. The MySQL cluster will be spanning across 2
  data centers with more than 10 data nodes. Table storage engine will be NDB for the replication capabilities.

Please let me know if more information is needed.

Any expert here is with MySQL cluster knowledge about this and able to share ? I appreciate any links or article to back the claims, thank you.

P.S.  please note the version of the mysql cluster in the title

This says 61.  However, it could be an oversight that NDB is different.