MS SQL Server Copy Database Wizard vs Backup / Restore Script I am working on a live SQL Server database. It was just recently migrated from another server. In the old server the backup was done using a job with Copy Database Wizard. When the database was migrated to the new server, the job wasn't restored along with the database, so the new database doesn't automatically run a backup job.

I'm not familiar with the copy database wizard, so my question would be is there any disadvantage of running a job with sql script using BACKUP and RESTORE as opposed to the Copy Database Wizard ? Is there any risk if I run the backup using script as opposed to the CDW ?

I suggest using regular SQL jobs, using the Ola Hallengren solutions for your backups.
 Ola hallengren has a full (free) maintenance solution that you can download and deploy on your server. ola.hallengren.com
It's used by a lot of DBAs, and has been around for years.

All you have to do is deploy the scripts and add some schedules, to your full/diff and log backups job.
You can also fine tune everything to fit your needs. There's code examples and options on the Ola site.
For example this link gives you all the options for the backup SP.

A big benefit you get from this, is that you can simply script out everything, and deploy it on any server you want, without much hassle.

Another nice to have, is that it also includedes jobs like IndexMaintenance and IntegrityChecks, which are needed on any SQL Instance. So it's a good all in one solution.


Cannot find the user, because it does not exist or you do not have permission I am using mssql and I would like to achieve this:
Have 1 database, 2 non-SA users. 1st user should be owner of database, 2nd one should have read permissions on some tables, read+write on others.

Tables for 2nd user are created by our application which uses 1st user.

Steps to what I do:
1) I execute script which creates DB + users

CREATE LOGIN USER1
WITH PASSWORD = 'password';
GO
CREATE USER USER1 FOR LOGIN USER1;
GO

CREATE DATABASE my_database;
ALTER AUTHORIZATION ON DATABASE ::my_database TO USER1;

CREATE LOGIN USER2
WITH PASSWORD = 'password';
GO
CREATE USER USER2 FOR LOGIN USER2;
GO

2) My application executes scripts which creates tables + grant privileges on tables to USER2, this fails, because:

Cannot find the user 'USER2', because it does not exist or you do not
have permission.

My question is how to grant privileges to USER1 to add read/write privileges for another users within my_database? I though, that database owner is enough.

Example how I grant privileges to USER2:

GRANT SELECT ON OBJECT ::schema_version TO USER2;

You've created the login for user1 and user2 to the master database not the database you want.

rearrange to:

CREATE LOGIN USER1
WITH PASSWORD = 'password';
GO

CREATE DATABASE my_database;
ALTER AUTHORIZATION ON DATABASE ::my_database TO USER1;

CREATE LOGIN USER2
WITH PASSWORD = 'password';
GO
USE my_database;
GO
CREATE USER USER2 FOR LOGIN USER2;
GO

Then you can add permissions to that user as it will exist in that database.

NOTE: you may want to go into your master database (or whichever database you were running those commands in before) and remove USER1 and USER2


CHECK constraint not implemented in MySQL, what about other DBMS? MariaDB, which is based on MySQL, does not.
Oracle, including Oracle XE 10g and 11g do.

I'm curious as to other DBMS?

Every R (relational) DBMS I've used has.  MySQL is peculiar in so many ways I struggle to put it head to head with any of the "normal" RDBMS for anything other than a basic data dump for a simple application.

There are plenty of DBs that are not relational that don't have the concept.  As far as I know though, there are none other of either sort that support the syntax without the functionality.


DBCC CHECKTABLE taking 15+ minutes to run on an empty table I have a database where DBCC CHECKTABLE on a number of small or empty tables taking over 15 minutes to run.  When it finishes there are no failures or errors.  The performance on everything else on the server is at a very acceptable form.  There was nothing else running at the same time.

I have also tried DBCC CLEANTABLE and updated stats with fullscan.

I'm using SQL Server 2016 Enterprise Edition (13.0.5201.2)

Example table:

CREATE TABLE [Schema1].[Table1](
    [col1] [int] NOT NULL,
    [col2] [nvarchar](100) NOT NULL,
    [col3] [xml] NOT NULL,
 CONSTRAINT [PK_1] PRIMARY KEY CLUSTERED
(
    [col1] ASC,
    [col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I ended up opening an incident with Microsoft and it was determined that the sys.sysrscols (that can only be accessed via DAC) had over 200 million rows.  After they did a review it was found as part of DBCC SQL Server always does checks on system tables even for DBCC CHECKTABLE. From the DBCC CHECKTABLE manual:

Integrity checks are always performed on all system table indexes.

My understanding is sys.sysrscols does some column tracking and the reason for the large size is a number of tables (200+) we have that have 2,000+ partitions and 163 columns.  Almost all of them were temporary working tables that should have been dropped by the application but were not.  I cleared them out and the process returned to a normal response time for an empty table of less than 1 minute.
They also noted it is listed in the documentation that partitions can impact DBCC checks. From Partitioned Tables and Indexes:

DBCC Commands
With a larger number of partitions, DBCC commands could take longer to execute as the number of partitions increases.


vba Excel data Validation error 'Application-defined or object-defined error' I've read loads of similar questions and answers, but keep getting error.

What I do is I export query via

DoCmd.TransferSpreadsheet acExport, 8, "blabla", outputFile, True

Then I'm attempting to add validation to a range in my new excel file.

Here I get an error on setting AlertStyle:=xlValidAlertStop, Operator:=xlBetween,

(no errors without them, but no validation is set as well, obviously)

[Application-defined or object-defined error ]

Running same code from Excel - cause no issues!

Sub ApplyExcelFormating(ByVal myFile As String, ByVal mySpreadsheet As String)

    Dim myValidationList(1) As String
    myValidationList(0) = "YES"
    myValidationList(1) = "OK"

    Dim XL As Object, WB As Object, WKS As Object
    Set XL = CreateObject("Excel.Application")
    XL.Visible = True
    XL.DisplayAlerts = True
    Set WB = XL.Workbooks.Open(myFile)
    WB.Activate
    Set WKS = WB.worksheets(mySpreadsheet)

    Dim lastrow As Integer
    lastrow = WKS.UsedRange.Rows.Count

    WKS.Range("K2:K" & lastrow).Activate
    WKS.Range("K2:K" & lastrow).Select

Only these two cause error:

    'AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
    With WKS.Range("K2:K" & lastrow).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(myValidationList, ",")
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With

    WB.Save
    XL.Workbooks.Close
    XL.Visible = True
End Sub

I found a way around the issues i had, related to Excel Data Validation setup from Access via VBA.
Here:
https://msdn.microsoft.com/en-us/library/office/ff840715.aspx

For some weird reason, VBA would not set data validation based on property names.
E.g. if I use Type:=xlValidateList - nothing changes in the spreadsheet.
While the below does the job:

... .Validation.Add Type:=3, Operator:=2, AlertStyle:=2, Formula1:="YES,OK"


How to search Query Store for a specific word or command in a query? I have an Enterprise Edition of SQL 2016 SP1, that has a database with Query Store active.

I want to search for a specific word or command in the saved queries. I have googled around and not seen anything. In this case I want to find 'SHRINK', but the same approach should be usable for any text in stored query.

Anticipating the "why?" comment. I have a vendor app that does a bunch of work and then shrinks the database and log files. I am trying to find the query that does it.

How to search Query Store for a specific word or command in a query?

As sp_BlitzErik mentioned in a comment, and as I gathered from my own research, you can find some things in sys.query_store_query_text but not necessarily always a SHRINK.

This query returns 22,497 results:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text LIKE '%Select%'

This query does not return any, except the second time I run it :)

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text LIKE '%SHRINK%'

The foundation of the query above comes from a SQL with Bert post.

A coworker has shown me a query that finds the shrink and the time and login of who did it.  So I have what I need for the my specific issue, but that is something of a side step from my question.

My workaround uses the default query trace. It shows multiple recent DBCC Shrinkdatabase ('MyDB', TRUNCATEONLY) that are run against the master database, which does not have query store.

A DBCC SHRINKFILE run in the context of a database with query store enabled does get recorded in the query store.


'Prioritize' one column over another I'm working with subscription table currently and I'm creating a cron job that will take records based on some condition from the subscription table.

I want to select all subscription records that have either been renewed_at or created_at (these are column names) on certain date, seems easy so I do it like this, if the date is 2013-10-16 :

select * from subscription where
(subscription.renewed_at between '2013-10-16 00:00:00.000000'
and '2013-10-16 23:59:59.999999') OR
(subscription.created_at between 2013-10-16 00:00:00.000000'
and '2013-10-16 23:59:59.999999')

So here is what I want to be sure, and I'm not sure how to do it.

I want to prioritize renewed_at column over created_at column, in a sense that if somebody renewed their subscription at a certain date that I should select the record based on that date rather than on created_at date. In case however the renewed_at is not there I should select the record based on the created_at date.

And I should not select the record based on created_at if the renewed_at column is in there, and they're always different in sense than they're not ever the same date even if they're both present. created_at is always present.

Is is possible to do it? Or am I thinking this the wrong way?

I think this might work, assuming that by not present you mean is NULL:

select * from subscription where
coalesce ( subscription.renewed_at, subscription.created_at )
between '2013-10-16 00:00:00.000000' and '2013-10-16 23:59:59.999999')

The coalesce() function returns its first non-NULL argument, so if renewed_at is present it will be returned, otherwise created_at will be the result.

Alternatively, you could use this, which will work in case not present means something else in your table, e.g. the value 9999-01-01-00-00-00:

select * from subscription where
(subscription.renewed_at
   between '2013-10-16 00:00:00.000000' and '2013-10-16 23:59:59.999999')
or (
subscription.renewed_at = '9999-01-01-00-00-00'
and
subscription.created_at
   between '2013-10-16 00:00:00.000000' and '2013-10-16 23:59:59.999999'
)


How to add entries in a table column as the attributes for another table in MySQL? I am designing an Attendance Management System, so for the table which contains the attendance status(Present or not) of all the students for all the days and all courses, following are the details:

Date and Lecture ID are primary keys (Assuming that only one lecture of particular course per day)
Every student is represented as an attribute.

Is this a good design?
How can I add students from the students table as attributes to the attendance table?

You should have a table something like this:

CREATE TABLE Attendance (
    class_id ... NOT NULL,
    student_id ... NOT NULL,
    date DATE NOT NULL,
    PRIMARY KEY(class_id, student_id, date)
) ENGINE=InnoDB;

The existence of a row says that a particular student was in a particular class for the particular day.

This is perhaps the cleanest way to store the data.  Remember:  A database is a repository for data, not a reflection of reports you might like to present.  Reports are derived from raw data.

Note that the table is not limited by the number of classes, students, or dates.

This, for example, reports how many students were in each class for each day:

SELECT class_id, date, COUNT(*)  FROM Attendance  GROUP BY class_id, date;

If you want to turn that into a 2D matrix, see the [pivot] tag.  (It's a bit tricky.


SQL Server 2008 Restore I'm trying to restore from a database backup to SQL Server 2008.  The backup came from a different server.  I'm executing the following query:

RESTORE DATABASE Database_Name
FROM DISK = 'C:\Path\To\Backup.file'
WITH REPLACE
GO

If the database does not exist, I'm getting the following error:

  Msg 3118, Level 16, State 1, Line 1
  The database "Database_Name" does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

The file is a full backup.

If the database does exist, I get the following error:

  Msg 3154, Level 16, State 4, Line 1
  The backup set holds a backup of a database other than the existing 'Database_Name'  database.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

From what I understand, WITH REPLACE is supposed to be a workaround for this.

I'm stumped.

Try this:

CREATE DATABASE foo;
GO
CREATE DATABASE bar;
GO
BACKUP DATABASE foo TO DISK = 'c:\temp\x.bak';
BACKUP DATABASE bar TO DISK = 'c:\temp\x.bak';
GO
RESTORE HEADERONLY FROM DISK = 'c:\temp\x.bak';

Relevant columns for this scenario:

Position  DatabaseName
--------  ------------
1         foo
2         bar

To restore foo, whether it exists or not:

RESTORE DATABASE foo FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 1, REPLACE;

To restore bar, whether it exists or not:

RESTORE DATABASE bar FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 2, REPLACE;

If you don't specify WITH FILE, you will get a slew of errors, depending on whether the database in Position = 1 already exists.

The lessons here:

do not use common and generic names for backup files - name them for the database they represent and keep them separate.
use WITH INIT if you expect a backup file to only ever contain one copy of a backup.
understand what is in your backup by first running RESTORE
HEADERONLY and/or RESTORE FILELISTONLY.

If you show us those two columns from the output of RESTORE HEADERONLY and what database you are actually trying to restore, we can provide you with a specific command that should work.


Autocomplete too slow: possible optimizations? The autocomplete search functionality of my website searches a varchar field which contains the model number of sale items.

This field can contains strings varying from 1 to 75 characters and the table contains 400 000 rows. I have come up with a query that searchs only from the start of the string and that takes about 150-250ms to execute which is acceptable but now my manager wants the query to search for any substrings which makes the query 3-10 times slower (around 1000-2000ms).

I have built a JS fiddle to give you an example of what the data looks like and what the two query are like.

http://sqlfiddle.com/#!6/9efa3/2/0

There already are a few indexes on the table. What would be the best practice to speed-up this auto-complete search field ?
(database version is SQLSERVER 2008R2)

Here is a brief example of the data I'm dealing with :

CREATE TABLE [Products](
    [productid] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [model] [nvarchar](75) NOT NULL
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
    [productid] ASC
));

insert into products values ('UMPX1AA0011 danish e-315 woot');
insert into products values ('P27y719VC');
insert into products values ('VG2y439m-LED');
insert into products values ('UMUyX165AAB01');
insert into products values ('U28y79VF');
insert into products values ('U52417HJ');
insert into products values ('VA25746M-LED WITH FLYING CORNERS');
insert into products values ('S19F350HNN 1pc california storage');
insert into products values ('VA211917A');
insert into products values ('PM2500X2');
insert into products values ('E22470SWHE');
insert into products values ('V22465WLYDP');
insert into products values ('I129LMH1HKC');
insert into products values ('OM5EN X 35 the new version');
insert into products values ('DLS3060WDB');
insert into products values ('PVW');
insert into products values ('LI23721S');
insert into products values ('V173516LBM');
insert into products values ('VX2376-SMHD-A');
insert into products values ('GUM5FX1AA1001');
insert into products values ('GPM300X11');
insert into products values ('GUM-WH6AA002');
insert into products values ('2435V5LSB');
insert into products values ('P2418HZ');
insert into products values ('Stylish sectional one of a kind y-5151');

and these are the two queries I'm comparing

--runs acceptably fast, about 100-250ms
select * from products where model like 'y-5151'+'%';
--takes too long, around 1000-2500ms
select * from products where model like '%' + 'y-5151' +'%'

The solution I went with is to build a "half-triangram" table which contains a pre-processsed version of all the model # substrings as suggested by Aaron Bertrand in the two following blog posts of his :

https://sqlperformance.com/2017/02/sql-indexes/seek-leading-wildcard-sql-server

https://sqlperformance.com/2017/02/sql-performance/follow-up-1-leading-wildcard-seeks

The solution for me was to create a new table where I look for models starting with the search string. Each model is listed as such, let's say the product_ID was 51 and the model was 7500 Twin Bed

ID|Model
----------------
51|7500 Twin Bed
51|500 Twin Bed
51|00 Twin Bed
51|0 Twin Bed
51| Twin Bed
51|Twin Bed
51|win Bed
51|in Bed
51|n Bed
51| Bed
51|Bed
51|ed
51|d

This way you never need to do a full wildcard search, a simple select distinct id_product from products_dictionary where model like 'Twin%' will return the required results. The query now takes less than 100ms.

Here is the code I used to create the table and populate it. Again, this is all described properly on Aaron's blog post:

CREATE TABLE [dbo].[products_dictionary](
    [Id_product] [int],
    [model] [nvarchar](75) NOT NULL
)
insert into [products_dictionary]
select p.id_product,f.fragment from products p
cross apply dbo.CreateStringFragments(p.model) AS f;

create clustered index index_idprod_substrmodel on [Products_dictionary]([model],[Id_product])


Performance Metrics: Columnstore Indexes vs SSAS Cubes What has faster performance speed:  Columnstore Indexes or SSAS Cubes? Are Columnstore indexes proven to be faster than SSAS cubes (SQL Analysis Services)? Have their been any performance studies conducted by Microsoft?

There is no single right answer to your question.

Its worth noting they are not mutially exclusive technologies. You can have an OLAP cube reading from table using column store indexes.

The performance of each in various scenarios will differ depending upon the size, number, complexity of tables used and granularity/sparcity of data within them.

It will also depend upon the variability of queries used. Are they frequently aggregating? Or constantly seeking row level data?

The only way to know for sure in your case is to test your queries and scenarios with representative data.


Oracle 11g not allowing to connect to. Error ORA-01122: database file 1 failed verification check I am running Oracle 11g on Windows 7 Enterprise SP2 16 GB RAM, 1 TB HD. I can connect to Oracle using SQL Plus (see below),

C:>sqlplus sys/xxxxxxxxx@XE as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 9 18:52:26 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

... but when JDeveloper or another commercial application try to connect to I get the following error:

ORA-01033: Oracle initialization or shutdown in progress ...

Any clue or help would be really appreciated.

Thanks,

XMLDeveloper

Did you do anything like renaming datafiles or moving them around in the filesytem?
The ORA-01122 indicates that the information in the controlfile does not match your environment.

So if you do a shutdown/restart of the database and still get the error and moved files around you should move/rename the files to their original places/names and try again.

If all this does not work, have a look at MOS 1041424.6 for details.


Oracle: Read from CLOB column line by line and insert to the table I have CLOB column, and wanna read its data line by line to insert it in another table.

Now let's create the same situation that I have:

Data in a Table MyTable_1

OWNER | TABLE_NAME | SQL
----------------------------
HR    | AAA        | <CLOB>

In <CLOB> there is the following data:

CREATE TABLE AAA
( A NUMBER
);

I want my table to look like this(let's call it MyTable_2) :

OWNER | TABLE_NAME | SQL
----------------------------
HR    | AAA        |CREATE TABLE AAA
HR    | AAA        |( A NUMBER
HR    | AAA        |);

This is not a full answer because it only works if your clob is less than 4000 chars. The clob version (with plain bar instead of to_char(bar) in the query) fails with ORA-00932: inconsistent datatypes: expected - got CLOB - perhaps someone else can explain why exactly?

testbed:

drop table foo;
create table foo(id integer, bar clob);
insert into foo(id, bar) values (1, 'Hello'||chr(10)||'there');
insert into foo(id, bar) values (2, 'Hello'||chr(10)||'there'||chr(10)||'again');

query:

with w(id, line#, line, rest) as (
  select id, 1, regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'),
         substr(to_char(bar), regexp_instr(bar, '$', 1, 1, 1, 'm')+1)
  from foo
  union all
  select id, line#+1, regexp_substr(rest, '^.*?$', 1, 1, 'm'),
         substr(rest, regexp_instr(rest, '$', 1, 1, 1, 'm')+1)
  from w
  where rest is not null ) cycle id, line# set is_cycle to '1' default '0'
select id, line#, line from w order by id, line#;
/*
ID                     LINE#                  LINE
---------------------- ---------------------- ----------------------------------------
1                      1                      Hello
1                      2                      there
2                      1                      Hello
2                      2                      there
2                      3                      again
*/


PostgreSQL + why archive_command line is marked after barman restore I use the barman tool as backup & restore for PostgreSQL

after I restored successfully the data folder under /var/lib/pgsql by the barman

we have seen that the line with archive_command  was remarked by the #BARMAN# and the line - archive_command = false added also

as the following

vi /var/lib/pgsql/data/postgresql.conf

#BARMAN# archive_command = 'rsync -a %p barman@107.14.23.6:/var/lib/barman/main-db-server/incoming/%f'
archive_command = false

this mean from this point we can't backup the data folder from the backup server
because line is marked

why barman remark the line after restore process?

can we avoid this? ( because this stops the backup process each night )

IP - 107.14.23.6 is the backup server

As far as barman knows, you did the restore in order to make a clone of your instance and the original instance is still running someplace.  You definitely don't want two instances sending WAL to the same archive.  If you are dead certain the original instance has been irreversibly destroyed, then you should turn archiving back on.  Otherwise, you should start archiving to a different location.


sql server replication - how to get combination of values from a binary(8) field? I am using the table sysschemaarticles to collect information in my transactional replication.

It is used for a variety of things including adding new articles and finding out who are the subscriber Servers.

this script needs to be run in the publication database.

SELECT
 PublisherDB=db_name()
,PublisherName=sp.name
,The_Type=so.type_desc
,The_Schema=schema_name(so.schema_id)
,The_Object=sa.name
,SubscriberServerName=UPPER(srv.srvname)
,so.is_schema_published
,so.is_published
,sa.schema_option
from dbo.syspublications sp
INNER JOIN dbo.sysschemaarticles sa on sp.pubid = sa.pubid
INNER JOIN sys.objects so on sa.objid = so.object_id
INNER JOIN dbo.syssubscriptions s on sa.artid = s.artid
INNER JOIN master.dbo.sysservers srv on s.srvid = srv.srvid

However the schema_option has different options that can be associated with it, and I would like to figure out ALL of them.

You can see the possible options on the picture below.

How can I get ALL the values stored in a binary(8) field?

I believe there could be a combination of options in this case.

Use the built-in bitwise operators to examine the column.  For example

select
    schema_option & 0x01 AS  GeneratesObjectCreation,
    schema_option & 0x02 AS  GenerateCustomSP,
... etc.


Does ' /*+parallel(,4)*/ ' have better impact on query performance in comparison with simple parallel hint '/*+parallel(4)*/'? I have three tables with below structure:
create table customer_info
(customer_num        number,
 customer_firstName  varchar2(100),
 customer_lastName   varchar2(100),
 branch_code         varchar2(100));

create table branch_info
(branch_code   varchar2(100),
 branch_name   varchar2(100));

create table customer_transaction
(transaction_date  date,
 customer_num      number,
 branch_code       varchar2(100),
 transaction_count number
  )

Among these tables the one which has the biggest amount of data is customer_transaction with nearly 10 million records right now. The table has partition on column transaction_date .I want to know:
1)whether there is a difference between these two queries in terms of performance.
2)In what way these two queries are different from each other.
The point is that in the first query I'm using this /*+parallel(4)*/ and in the second query I'm referencing the table which has the biggest amount of data /*+parallel(t2,4)*/.I must say that both queries produce different execution plans.
First Query:
Select /*+parallel(4)*/
       t2.transaction_date,
       t1.customer_num,
       t1.customer_firstname,
       t1.customer_lastname,
       t3.branch_name,
       t2.transaction_count
  from customer_info t1
  left join customer_transaction t2
    on t1.customer_num = t2.customer_num
  left join branch_info t3
    on t2.branch_code = t3.branch_code;

Second Query:
Select /*+parallel(t2,4)*/ -----> t2 is "customer_transaction"
       t2.transaction_date,
       t1.customer_num,
       t1.customer_firstname,
       t1.customer_lastname,
       t3.branch_name,
       t2.transaction_count
  from customer_info t1
  left join customer_transaction t2
    on t1.customer_num = t2.customer_num
  left join branch_info t3
    on t2.branch_code = t3.branch_code;

Thanks in advance.

Sure there is, but this may or may not be noticeable. We can not know this just by looking at your table definitions.
See below.

The first query does all operations in parallel with degree 4, including table accesses and joins:
Plan hash value: 2107928671

-----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |        |      |            |
|   1 |  PX COORDINATOR                   |                      |        |      |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10004             |  Q1,04 | P->S | QC (RAND)  |
|   3 |    HASH JOIN OUTER BUFFERED       |                      |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|   5 |      PX SEND HASH (NULL RANDOM)   | :TQ10002             |  Q1,02 | P->P | HASH       |
|   6 |       HASH JOIN OUTER BUFFERED    |                      |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH (NULL RANDOM)| :TQ10000             |  Q1,00 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR        |                      |  Q1,00 | PCWC |            |
|  10 |           TABLE ACCESS FULL       | CUSTOMER_INFO        |  Q1,00 | PCWP |            |
|  11 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH              | :TQ10001             |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR        |                      |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL       | CUSTOMER_TRANSACTION |  Q1,01 | PCWP |            |
|  15 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|  16 |      PX SEND HASH                 | :TQ10003             |  Q1,03 | P->P | HASH       |
|  17 |       PX BLOCK ITERATOR           |                      |  Q1,03 | PCWC |            |
|  18 |        TABLE ACCESS FULL          | BRANCH_INFO          |  Q1,03 | PCWP |            |
-----------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of hint

The second query accesses only CUSTOMER_TRANSACTION in parallel. Other tables are not accessed in parallel. Joins to CUSTOMER_TRANSACTION are however processed in parallel.
Plan hash value: 1958681835

-----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |        |      |            |
|   1 |  PX COORDINATOR                   |                      |        |      |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10004             |  Q1,04 | P->S | QC (RAND)  |
|   3 |    HASH JOIN OUTER BUFFERED       |                      |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|   5 |      PX SEND HASH (NULL RANDOM)   | :TQ10002             |  Q1,02 | P->P | HASH       |
|   6 |       HASH JOIN OUTER BUFFERED    |                      |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH (NULL RANDOM)| :TQ10000             |  Q1,00 | S->P | HASH       |
|   9 |          PX SELECTOR              |                      |  Q1,00 | SCWC |            |
|  10 |           TABLE ACCESS FULL       | CUSTOMER_INFO        |  Q1,00 | SCWP |            |
|  11 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH              | :TQ10001             |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR        |                      |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL       | CUSTOMER_TRANSACTION |  Q1,01 | PCWP |            |
|  15 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|  16 |      PX SEND HASH                 | :TQ10003             |  Q1,03 | S->P | HASH       |
|  17 |       PX SELECTOR                 |                      |  Q1,03 | SCWC |            |
|  18 |        TABLE ACCESS FULL          | BRANCH_INFO          |  Q1,03 | SCWP |            |
-----------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of table property


Setup replication between two Galera nodes I've been using MariaDB 10.2 Galera with 3 nodes successfully on local servers. Now, I have a second 3-nodes Galera cluster on another location, joined by an Site-to-Site IPSec Tunnel.

I can't really merge it to one big cluster, as the performance drawback would be high (due to physical positions of those two datacenters).

I'd like to setup asynchronous replication between those two clusters but I can't make it two work. Using "old-school" master-master replication (which is master/slave in both ways, as I get it) between two nodes doesn't work as conflicts starts to rise.

Is there a way to setup asynchronous replication between those two Galera clusters ?

Thanks for your help.

If you have any AUTO_INCREMENTs, I think it is impossible.

You are effectively asking for 6 Masters talking to each other.  For AUTO_INCREMENT not to mess up, you need auto_increment_increment = 6.  But Galera will insist on using 3, since there are only 3 "nodes" in [each] "cluster".


How to work around Sequelize not supporting composite foreign keys I am developing an advanced query with Sequelize (hitting a PostgreSQL database). It's not difficult in pure SQL, but I can't get it work in Sequelize, in part because they don't support composite foreign keys.

The best way to describe my problem is with code, so I made a repository with a minimal reproduction of my problem. You should be able to clone it and follow the instructions in the README to reproduce the problem locally. Basically, if you can get the query to run without errors and correctly return an empty array of rows, you've solved my problem :)

For convenience I will copy here the query and output themselves, but you can check the repo to look at the migrations, models and Sequelize configuration.

The Query

const performQuery = (db) => {
  const { usersRoles, invitations } = db; // eslint-disable-line max-len

  const sql = {
    include: {
      model: invitations,
      on: {
        user: Sequelize.where(
          Sequelize.col('usersRoles.userUid'),
          '=',
          Sequelize.col('invitations.guestUid')),
        propertyManager: Sequelize.where(
          Sequelize.col('usersRoles.propertyManagerUid'),
          '=',
          Sequelize.col('invitations.propertyManagerUid')),
        tenant: Sequelize.where(
          Sequelize.col('usersRoles.tenantUid'),
          '=',
          Sequelize.col('invitations.tenantUid')),
      },
    },
  };
  return usersRoles.findAll(sql);
};

Output (truncated)

TypeError: Cannot read property 'toUpperCase' of undefined
    at str.trim.replace (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/utils.js:108:60)
    at RegExp.[Symbol.replace] (native)
    at RegExp.[Symbol.replace] (native)
    at String.replace (native)
    at camelize (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/utils.js:108:21)
    at Object.camelizeIf (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/utils.js:32:14)
    at new HasMany (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/associations/has-many.js:67:31)
    at Function.hasMany (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/associations/mixin.js:26:25)
    at Function.usersRoles.associate (/home/shawn/git/waltzapp/sequelize-troubleshooting/sequelize/models/usersRoles.js:71:23)
    at Object.keys.forEach (/home/shawn/git/waltzapp/sequelize-troubleshooting/index.js:62:29)
    at Array.forEach (native)
    at Promise.map.then (/home/shawn/git/waltzapp/sequelize-troubleshooting/index.js:60:27)

I have never seen Sequelize, but your error reads:

 TypeError: Cannot read property 'toUpperCase' of undefined

Where it says of undefined I would suggest your code is trying to convert a string to uppercase, but in fact, your string is null and the conversion (toUpperCase) fails on null.

That said, I don't see toUpperCase in the query you supplied, but you have the line model:invitations - does this mean that your query is acting on some data structure (table?) called 'invitations'?

If so, I would check there for the toUpperCase function. For each instance of the function, see whether it is being called on something that might have a null value. If so, then somehow ensure you don't get a null value - for example, add filter criteria to exclude null values, or use a casting function like isNull (if it exists for you) or coalesce to replace nulls with an empty string, like this:

 coalesce(columnName, '')

Failing the above, post a little more info on what your data query really looks like, and why you think the composite foreign keys may relate to the error.


How to reduce the number of things SQL Server puts in the windows event viewer? As part of our automated unit tests, a lot of databases get created, set up, then dropped. The problem is that every time this happens, we get a bunch of records from SQL Server in the Windows Event Viewer. The messages are mostly things like: "Starting Up", "Setting database option", and "index restored".

Is there any way to modify the SQL Server config so that it doesn't log these trivial "Information"-level events to the event log. They are filling up the log so that it becomes very difficult to find useful information.

Note: This will not limit what gets logged. It justs stops logging in Windows Event log.

There are some stuff that you can prevent like - successful completion of log backups using Trace Flag 3226

Have a look at -n startup parameter.

From Database Engine Service Startup Options :

  Does not use the Windows application log to record SQL Server events. If you start an instance of SQL Server with -n, we recommend that you also use the -e startup option. Otherwise, SQL Server events are not logged.


"Approximately equal" between Sync and Async operations for PageCleaners The documentation of num_iocleaners says:

  The parameter can be reduced if both of the following conditions are true:

  pool_data_writes is approximately equal to pool_async_data_writes
  pool_index_writes is approximately equal to pool_async_index_writes.

  The parameter should be increased if either of the following conditions are true:

  pool_data_writes is much greater than pool_async_data_writes
  pool_index_writes is much greater than pool_async_index_writes.

What is approximately? What is "much greater"?  What ratio?  What percentage of difference?

Does anyone have an example of values that are "approximately equal" and where the Page Cleaners value should be increased? The same info for the "much geater" values would also be appreciated.

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0000332.html?cp=SSEPGG_10.5.0%2F2-4-4-8-108&lang=en

The idea is that you want as many of your writes as possible to be asynchronous. On the other hand, pool_data_writes will always be greater than pool_async_data_writes (ditto for index writes).

The tolerance depends entirely on your system. If each minute you have 3 pool_data_writes and only 1 pool_async_data_writes, you obviously have twice as many synchronous writes as you have asynchronous, but it's hardly a reason for concern.

If, on the other hand, each minute you have 300K pool_data_writes and 290K pool_async_data_writes, the difference appears to be minor, but you still have 10000/60 ~ 166 waits per second, which is pretty bad.

Please keep in mind that num_iocleaners is not the only configuration parameter that affects the write efficiency: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0011381.html?lang=en

PS. In your comment you compare asynchronous reads with writes, which doesn't make much sense.


Always On - keep secondary database readable when connection to primary node failed By default, secondary replica brings databases into offline state when connection to the primary replica fails. We are using SQL 2016 SP1.

The goal is to reduce interruption for users on secondary async replica when primary replica goes offline for maintenance or due to network issues.

Is there a way to change this behavior and keep secondary databases always online ?

Is there a way to change this behavior and keep secondary databases always online?

Yes.  Use SQL 2014 or later :)

  Beginning in SQL Server 2014 (12.x), readable secondary replicas can
  remain online even when the primary replica is offline due to user
  action or a failure. However, read-only routing does not work in this
  situation because the availability group listener is offline as well.
  Clients must connect directly to the read-only secondary replicas for
  read-only workloads.

Active Secondaries: Readable Secondary Replicas (Always On Availability Groups)


What is the best data type to store prices upto 8 decimals + indicators in SQLite? I am developing a trading system which stores data in SQLite, consisting of OHLC and volume. I would also like to store some indicators like RSI etc I read the other answer which indicated integers as the data type. In my case I am looking at 8 digit precision which would slow down the calculations if I were to multiply every value by 1e8 Any recommendations are appreciated!

The FAQ pretty much explains the reason why you don't want to use their REAL data type for storing numbers with 8 decimal places.

  (16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?

https://www.sqlite.org/faq.html#q16

Workaround 1

The trick of using an INTEGER to achieve your required level of precision has been used since computers existed.

With today's powerful CPUs, I wouldn't worry about the overhead of any additional calculations.  You'll need to run benchmarks to (dis)prove my statement.

Workaround 2

IMHO - You shouldn't use a tool that doesn't support your requirements.


mysql ERROR 1205: 1205: Lock wait timeout exceeded; try restarting transaction This is a replica of my StackOverflow question

I am trying to update a row on a specific table of my database.

UPDATE `Search`.`credentials` SET `status`='0' WHERE `id`='30';

and i am getting the error

ERROR 1205: 1205: Lock wait timeout exceeded; try restarting transaction
SQL Statement: UPDATE `Search`.`credentials` SET `status`='0' WHERE `id`='30'

I have set the timeout to 200

show variables like 'innodb_lock_wait_timeout';

  +----------------------------------+-------+
  |  Variable_name                              | Value|
  +----------------------------------+-------+
  | innodb_lock_wait_timeout | 50       |
  +----------------------------------+-------+

The database is been used, but not all the time, and not the table I want to update

mysql> show open tables where in_use>0;

  +-----------+---------------------------+--------+--------------------+ |Database| Table

  | In_use | Name_locked |
  +-----------+---------------------------+--------+--------------------+ | db1          | PayInformation
           |2
          |
  0
  | | db1          | KeyMapping
  |      4        |
  0
  | | db1          | NoticeInformation
      |      2
          |
  0
  | | Search  |
  companies
  |      1         |
  0
  | | db1          |
  ContractInformation |      5
          |
  0
  |
  +----------+---------------------------+--------+---------------------+

some times there are more lines concerning the Search database, but none that is using the same table that i need to update.

and from the process list i get

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where db="afmSearch";

  +----+------+-------------+--------+---------+------+-------+------+ |  ID  |  USER  |  HOST
     |  DB          |
   COMMAND  |  TIME  |  STATE  |
   INFO  |
  +----+------+-------------+--------+---------+------+-------+------+ |  xx  |  me      |  my.ip:44604
   |  Search  |  Sleep
   |        1  |
         |  NULL  |
  +----+------+-------------+--------+---------+------+-------+------+

Any ideas?

Some other connection was running something really long - probably longer than 50 seconds.

Possibly another connection is in autocommit=0 mode, but just sitting there, forgetting to do a COMMIT.

If you can, get the PROCESSLIST before it times out.  Also do SHOW ENGINE=InnoDB STATUS;

Provide SHOW CREATE TABLE credentials.  Look for other writes to that table, and check for index usage.


SQL Server - Temporary vs. Physical Tables A movement is afoot in my place of employ to move away from using #temp tables and instead to use permanent physical tables with SPIDs.  Whenever one would have previously INSERTed INTO a #temp table, now an INSERT INTO dbo.MyPermanentTable (SPID, ...) VALUES (@@SPID, ...) is required - together with a bunch of DELETE FROM dbo.MyPermanentTable WHERE SPID = @@SPID statements at the beginning of e.g. a stored procedure.  Additionally, it goes without saying that anywhere that these 'permanent tables for storing temporary data' are used, one has to be careful to include a WHERE SPID = @@SPID.

The logic behind the move towards this practice is that it'll improve overall performance of the server on which the queries are running (by reducing I/O and contention in tempdb).  I'm not keen on this approach for a number of reasons - it's ugly, potentially dangerous and seems like it may well harm the performance of those queries that use the new scheme.

Does anyone have any experience with this or similar approaches to eliminating #temp tables?

It can be demonstrated quite easily that it will not reduce IO nor contention, but instead increase both.

IO: Every row inserted, read or deleted from a #temp table will be now inserted, read or deleted from the @@SPID table. But every row will be wider with an additional @@SPID column, hence the number of pages needed will slightly increase and the IO will be ever so slightly bigger. But more importantly, the drop of a #temp table and the initialization of a new session's #temp table by a session will now have to be simulated with a DELETE FROM @@spidTable WHERE spid = @@SPID, and thus truncate/create operation (ie. page extent management operations) will be transformed in row operations, incomparable slower.
Contention: Every scan that was using page locks on the #temp table will now have the potential to lock a page with unrelated spid rows, thus creating previously non-existing contention. Every update that does more that hits the lock escalation threshold has the opportunity to escalate the lock to table lock and thus block every other spid.

So while is true that you won't hit the mythical IAM/SGAM/GAM contention in tempdb, the sole reason why this would happen is because your operations will become far slower due to ordinary extra IO and extra contention.


MySQL Replication Troubleshooting Here's what I did. On the master, change /etc/my.cnf:

[mysqld]
server-id=1
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Save and restart mysql, then log in.

>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123';
Mysql> flush table with read lock;//  Start copy data master (myisam) chuyển sang slave .
Mysql> show master status;

On the slave, change /etc/my.cnf.

[mysqld]
server-id=2
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Save and restart mysql. Create a database on the slave (Snapshop master). Move data from master to slave.

Mysql> stop slave;
Mysql>CHANGE MASTER TO
MASTER_HOST='server2',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='xxxx',
MASTER_LOG_POS=xxx;

On the master:

Mysql> unlock tables;

On the slave:

Mysql> start slave;
Mysql> show slave status /G

My problem is: I forget answer command belows.

Mysql> show master status;

when I start the slave. The error is

236 | Got fatal error 1236 from mast er when reading data from binary log: 'Could not find first log file name in binary log index file' |

What does the error mean, and how can I solve it?

Something tells me that when you run SHOW SLAVE STATUS\G you get

Slave_IO_Running=No
Slave_SQL_Running=Yes

It is a connectivity issue back to the master. This is a not connectivty due to the network but with the binary logging mechanism of the master. This is definitely the case because, the error message (1236) is complaining about the first entry of the master's list of binary logs.

When you had setup the line

[mysqld]
log-bin=mysql-bin

This would naturally create a file called mysql-bin.index, a files that would contain the list of all binary logs that starts with mysql-bin.

Just run this on the master:

SHOW BINARY LOGS;

and you will see the list of binary logs on the master. It shold look something like this:

+------------------+------------+
| mysql-bin.000001 | 1073741824 |
| mysql-bin.000002 | 1073741824 |
| mysql-bin.000003 | 1073741824 |
    .
    .
    .

There are a few scenarios that could have occurred:

Not every entry in the master's mysql-bin.index existing in the master's datadir
The master had a problem writing a binary log that the slave needs
One of the master's binary logs is either zero-length or corrupt

You may need to sync the data like this:

service mysql stop on the slave
Run RESET MASTER on the master to zap the master's binary logs
Perform rsync of data from master to slave multiple times until rsync < 1 min
service mysql stop on the master
Perform final rsync of data from master to slave
Find the master's log file and its filesize
service mysql start on the master
service mysql start --skip-slave-start on the slave
On the slave, perform change master to using master_log_file='mysql-bin.000001',master_log_pos=filesize of master's log fiie
Perform START SLAVE; on the slave

Give it a Try !!!

I answered another question back on Feb 23, 2011 about creating a slave from a running master : MySQL Replication - Introduce new Slave to replication


How to have row values as column headers with the associated values in postgresql? This is hard to explain but I'll do my best. So to make it simple, I have 3 tables with the following format:

Creator table

id | name
 1 | jack
 2 | doge
 3 | cardano

roads

id | creator_id | length
 1 |     1      |   3
 2 |     1      |   2
 3 |     3      |   9
 4 |     1      |   3

Blocks

id | creator_id | length
 1 |    1       |   4
 2 |    1       |   4
 3 |    3       |   5
 4 |    1       |   3

I can't seem to pick out the rows into columns, otherwise I would be posting here but the query I use to bring the data together is:

select name, layer, sum(length)
from ( SELect a.name, 'road routes'::text as layer, length
       from creator a
       LEFT JOIN road_routes b on a.id = b.creator
       where a.id in (73, 35, 33, 31)
       group by a.name, layer, length
     union
       SELECT a.name, 'block routes'::text as layer, length
       from creator a
       LEFT JOIN block_route b on a.id = b.route_creator
       where a.id in (73, 35, 33, 31)
       group by a.name, layer, length) x
group by layer, name

The expected result and what I need is a table with the following output:

type   | jack | doge | cardano | total
roads  | 8    | 0    | 9       | 17
blocks | 11   | 0    | 5       | 16

How to sort the data like this?

I've used this good answer of Erwin Brandstetter

But keep in mind that your solution requires a dynamic query because I'm sure that creator table has different records from these you has posted here.

create table creator (id int primary key, name text);
insert into creator values (1, 'jack'),(2, 'doge'),(3, 'cardano');
create table roads(id int primary key, creator_id int references creator(id), length int);
insert into roads values (1, 1, 3), (2, 1, 2), (3, 3, 9), (4, 1, 3);
create table blocks(id int primary key, creator_id int references creator(id), length int);
insert into blocks values (1, 1, 4), (2, 1, 4), (3, 3, 5), (4, 1, 3);

I've used a view to get results for roads and blocks.

create view my_view
as
select 'roads' as subject, c.name,
       coalesce((select sum(length) from roads where creator_id = c.id), 0)::int as length
from   creator c
union all
select 'blocks' as subject, c.name,
       coalesce((select sum(length) from blocks where creator_id = c.id), 0)::int as length
from   creator c;

✓

select * from
crosstab
(
  $$select subject, name, length
    from my_view
    union all
    select subject, 'total'::text as name, coalesce(sum(length),0)::bigint as length
    from my_view
    group by subject
    order by 1,2$$
)
as t ("subject" text, "cardano" bigint, "doge" bigint, "jack" bigint, "total" bigint)

subject | cardano | doge | jack | total
:------ | ------: | ---: | ---: | ----:
blocks  |       5 |    0 |   11 |    16
roads   |       9 |    0 |    8 |    17

dbfiddle here


Using all the CPU cores by NDBD and MySQL We are running MariaDB which is a fork of MySQL with NDB cluster engine.
The cluster includes one mysql node and two ndb data nodes. While benchmarking the cluster, we are trying to make a bottleneck of CPU and the mysql nodes has 8 cores in total. But mysqld process uses only one to two cores and does not utilizing more than 2 cores.
We tried increasing threads and warehouses but that did not help in utilizing all the CPU cores. Is there a way to make this possible. What system variables should I focus on to increase???

Any thoughts or suggestions are appreciated.

Solution is rather simple: Run mysqld multiple times on the same DB Server !!!.

Assign each mysqld to run on a different port.

The OS will spread the multple mysqld processes across the cores.

UPDATE

You may have to jerry rig some port forwarding using mysql-proxy

Assuming 10.240.35.180 is the SQL node

startup mysql on port 3307 in the SQL node
startup mysql on port 3308 in the SQL node
startup mysql on port 3309 in the SQL node
startup mysql on port 3310 in the SQL node

In the mgnt node, set up 4 instances of mysql-proxy

mysql-proxy 10.1.1.6 to 10.240.35.180 port 3307
mysql-proxy 10.1.1.7 to 10.240.35.180 port 3308
mysql-proxy 10.1.1.8 to 10.240.35.180 port 3309
mysql-proxy 10.1.1.9 to 10.240.35.180 port 3310

Now place IPs 10.1.1.6 - 10.1.1.9 in config.ini and startup mysql cluster. This sounds like a very major stretch, but it's your only shot at getting mysqld running across multiple cores.

BTW : back on August 10, 2011, someone asked a similar question in ServerFault and gave a detailed explanantion from the MySQL Cluster Team.

Give it a Try !!!


Is it better to create another table to possible null values in relational databases? I'm designing a relational database to use in MySQL. I have the next situation: In one table there are fields that in the most of situations are NULL. The fields would be like:

Table name: tabla

Fields:
  idtabla not null,
  text (varchar(n)) not null,
  image (mediumblob) {this can be null}

The doubt: Is better to create another table and when be necessary to use the image, query the new table?
Why is your solution, better for design? MySQL's time response, ease to make queries, etc?

You are touching on an almost philosophical argument here: should NULLable values be allowed at all given they violate the "closed world" assumption of the model relational databases are derived from (see relevant sections of http://en.wikipedia.org/wiki/Null_(SQL) and a number of other questions here such as Why shouldn't we allow NULLs? for more notes on that). To avoid unknown values splitting properties that might be unknown (or simply not applicable in all circumstances) into their own relation (table) so that you don't have values that are unknown but instead if a value is unknown it simply doesn't exist. While this satisfies the theory, there are practical implications that make it less desirable:

A JOIN operation is not free in most RDBMSs, searching for the properties in the extra table(s) increases the work the engine needs to do to satisfy your queries.
If the existence of properties is not codependent (i.e. each proerty can be unknown irrespective of the others) then to carry this implementation to its conclusion you sometimes end up needing a table for each property.
The extra joins add to the complexity of your queries and can reduce maintainability.
Updates also become less intuitive in places: blanking a property now becomes a DELETE operation and updating one may be either an INSERT or an UPDATE.

Of course there are circumstances where that first point is reversed and breaking properties off improves efficiency:

Most RBDMSs use page based storage and breaking optional information off from core data means you can fit more core data rows in a given page. Depending on your data size, RAM, and storage infrastructure this could be significant in reducing the amount of IO required for some large queries (but often isn't: try not to "over optimise" around this point, at least without running good benchmarks to make sure things are improving not worsening).
This is of course assuming that your queries are designed so they pick up only what they need so the engine won't need to care about the drawing in the extra information anyway.
Similarly there is less writing to do if you are only updating core information in an INSERT or UPDATE, and if you have constraints or triggers around those extra properties this processing is potentially avoided on each row change.

Your example looks like it is storing images (or at least large bits of data in blob type columns. There are two extra considerations here:

Most database engines store large data "off page" anyway, so if you avoid SELECT * you get the rows-per-page bonuses "for free" anyway.
Does moving them elsewhere allow you to share the large data between multiple entities rather than storing the same blob more than once, or is the information (if present) unique to a given row?

tl;dr: So no hard and fast answer I'm afraid. My advice would be to do whatever best fits your conceptual model of your data and therefore requires less thought on your part to maintain. This usually, but not always, means using NULLable columns rather than separate tables. Unless your data is truly massive the performance differences are going to be negligible.


SQL Server 2008 Version of rows between... preceding and current row Being a beginner, I'm having a hard time coding this particular scenario in SQL Server 2008

As you can see, The SUM column for July 2017 for example is equal to the following: August to Dec 2016 + Jan to July 2017 = 4625

Similarly, the SUM column for August 2017 is equal to the following: Sep to Dec 2016 + Jan to August 2017 = 4625

How can I automate this from month to month?

I understand that this can be done using window functions in SQL Server 2012 onwards but I have to modify this query to work in SQL Server 2008

select year(date), month(date),
   sum(sum(numbers)) over (order by year(date), month(date) rows between 11 preceding and current row) as prev_12_sum
 from t
 group by year(date), month(date)
 order by min(date);

I appreciate any help I can get.

Here's one method (that works in versions 2012+ unfortunately):

with
  cte as
  ( select
        year(date) as year,
        month(date) as month,
        sum(sum(numbers)) over (order by year(date), month(date))
            as running_total,
        row_number() over (order by year(date), month(date))
            as rn,
    from t
    group by year(date), month(date)
  )
select
    t.year, t.month,
    t.running_total - coalesce(p.runinng_total, 0)
        as total_12_months
from
    cte as t
    left join cte as p
    on t.rn - 12 = p.rn ;

This will work only if there are no months with gaps (without any entry).

Another try, for older versions:

with
  cte as
  ( select
        year(date) as year,
        month(date) as month,
        sum(numbers) as sum_numbers
    from t
    group by year(date), month(date)
  )
select
    t.year, t.month,
    sum(p.sum_numbers) as total_12_months
from
    cte as t
    left join cte as p
    on p.year = t.year - 1 and p.month > t.month
    or p.year = t.year     and p.month <= t.month
group by
    t.year, t.month ;


'Tempdb' is full warning message in SQL Server 2005 I am using SQL Server 2005. it's a running plant, Everyday large number of data posted into SQL Server 2005

SELECT
  TOP 5000 [T_idx], [T_TAG], [T_Date]
FROM
  [house].[dbo].[total]
ORDER BY
  T_Date DESC

From SQL Server 2005 I executed above script, and observed below error message:

  5000 row effected

  Msg 9002, Level 17, State 6, Line 1
  The log fie for database 'tempdb' is full. Backup the transaction log for the database to free up some log space.

and the 'tempdb' file size 860 Mb and my hard disk free size 9.4 GB. Is this space is
enough or do I need to free-up some space?

Without a suitable index, SQL Server has to perform a full sort of the data to satisfy your query. You can verify this is occurring by looking for a Sort or Top N Sort operator in the execution plan:

A suitable covering index that would avoid the need to sort in your query is:

CREATE NONCLUSTERED INDEX nc1
ON dbo.total (T_Date)
INCLUDE (T_idx, T_TAG);

When an explicit sort is needed, if SQL Server does not (or cannot) allocate sufficient memory to perform the sort entirely in memory, it will use tempdb. The allocation of sort run space in tempdb is a logged operation, which directly contributes to tempdb log space usage.

In addition, the sort starts a system transaction in tempdb to ensure sort allocations are correctly undone in case the transaction aborts.  This open transaction can prevent tempdb log space being reused until the sort completes. Executing DBCC OPENTRAN (tempdb) may show an open transaction with sort_init in the description.

You should also review the SQL Server product documentation and this article to determine how to configure your tempdb database optimally for your workload.


Insert blank row in the postgres query result I have below table after run a postgres query select * from tbl

1 type_1 name-1
2 type_1 name_2
3 type_2 name_3
4 type_2 name_4
5 type_3 name_5

how can I add a blank line in between each type so I can read the output easily?

Like

1 type_1 name-1
2 type_1 name_2

3 type_2 name_3
4 type_2 name_4

5 type_3 name_5

OR

1 type_1 name-1
2 type_1 name_2
- ----   ----
3 type_2 name_3
4 type_2 name_4
- ----   ----
5 type_3 name_5

Spacer between all rows
UNION ALL with the same query and blank columns. The only purpose of the subquery is to hide the additional column rn, which I use for sorting.
SELECT tbl_id, type, name
FROM  (
   SELECT tbl_id AS rn, tbl_id, type, name
   FROM   tbl

   UNION  ALL
   SELECT tbl_id AS rn, NULL::int, NULL::text, NULL::text
   FROM   tbl
   OFFSET 1
   )
ORDER BY rn, tbl_id NULLS FIRST

OFFSET 1 in combination with the later NULLS FIRST  in ORDER BY avoids a dangling spacer.
Spacer between groups of rows
Answer to updated question:
SELECT tbl_id, type, name
FROM  (
   SELECT type AS rn, tbl_id, type, name
   FROM   tbl

   UNION  ALL
   SELECT DISTINCT type AS rn, NULL::int, NULL::text, NULL::text
   FROM   tbl
   OFFSET 1
   )
ORDER BY rn, type NULLS FIRST


How to Join Two Result sets to query on output came from Two statements I Have two queries
Query 1:

SELECT
    e.eid, e.item as 'ITEM', SUM(s.qty) as 'TOTAL SOLD'
from
    tbl_sales s,
    tbl_matentry e
WHERE
    e.eid = s.item
GROUP By e.eid ;

Result 1:

+---------+-------------+---------------+
|     eid |    ITEM     |  TOTAL SOLD   |
+---------+-------------+---------------+
|       1 | rupa        |             5 |
|       2 | pan america |             3 |
|       3 | John Player |            10 |
|       4 | classmate   |            11 |
|       5 | lepakshi    |            55 |
|       6 | lee         |            14 |
|       7 | puma        |             9 |
+---------+-------------+---------------+

Query 2:

SELECT
    e.eid, e.item as 'ITEM', SUM(s.qty) as 'TOTAL STOCK'
from
    tbl_purchases p,
    tbl_matentry e
WHERE
     e.eid = p.item
GROUP By e.eid ;

Result 2:

+---------+-------------+--------------------+
|     eid |    ITEM     |        TOTAL STOCK |
+---------+-------------+--------------------+
|       1 | rupa        |                 41 |
|       2 | pan america |                 45 |
|       3 | John Player |                 32 |
|       4 | classmate   |                 75 |
|       5 | lepakshi    |                 56 |
|       6 | lee         |                 65 |
|       7 | puma        |                 50 |
+---------+-------------+--------------------+

When I combine both sql statements into single statement I am getting incorrect output

So Far i have tried this

SELECT
e.eid,
e.item as 'ITEM',
SUM(p.qty) as 'TOTAL STOCK',
SUM(s.qty) as ' TOATL SOLD',
(SUM(p.qty)-SUM(s.qty)) as 'BALANCE STOCK'
FROM
tbl_matentry e
    LEFT OUTER JOIN
tbl_purchases p ON p.item = e.eid
    LEFT OUTER JOIN
tbl_sales s ON s.item=e.eid
GROUP BY e.eid ORDER BY e.eid

+-----+-------------+-------+------+---------+
| eid |    ITEM     | STOCK | SOLD | BALANCE |
+-----+-------------+-------+------+---------+
|   1 | rupa        |   123 |   15 |     108 |
|   2 | pan america |    90 |    6 |      84 |
|   3 | John Player |    64 |   20 |      44 |
|   4 | classmate   |   150 |   22 |     128 |
|   5 | lepakshi    |   112 |  110 |       2 |
|   6 | lee         |   130 |   28 |     102 |
|   7 | puma        |   100 |   18 |      82 |
+-----+-------------+-------+------+---------+

How to do i get correct result? Where is mistake/error in my query.Please review my Query and help me to get correct data.

My Expected Output is

+---------+-------------+------------------+-----------------+------------------+
|     eid |    ITEM     |      TOTAL STOCK |   TOTAL SOLD    |  BALANCE STOCK   |
+---------+-------------+------------------+-----------------+------------------+
|       1 | rupa        |               41 |               5 |               36 |
|       2 | pan america |               45 |               3 |               42 |
|       3 | John Player |               32 |              10 |               22 |
|       4 | classmate   |               75 |              11 |               64 |
|       5 | lepakshi    |               56 |              55 |                1 |
|       6 | lee         |               65 |              14 |               51 |
|       7 | puma        |               50 |               9 |               41 |
+---------+-------------+------------------+-----------------+------------------+

The problem that you are having is most likely coming from the tbl_sales because your item can appear in the table more than once.  When you then JOIN your tables together, you are returning the qty from tbl_purchases for each row in tbl_sales.

You can see this behavior by performing a SELECT on your tables:

SELECT e.*, p.qty purQty, s.qty salesQty
FROM tbl_matentry e
LEFT OUTER JOIN tbl_purchases p
 ON p.item = e.eid
LEFT OUTER JOIN tbl_sales s
  ON s.item=e.eid
ORDER BY e.eid;

See Demo. You will see in my sample, that the rupa qty appears twice because there are two entries in tbl_sales.

One way to get the result would be to calculate the TotalSold and TotalStock in subqueries:

select e.eid,
  e.item,
  coalesce(p.TotalStock, 0) TotalStock,
  coalesce(s.TotalSold, 0) TotalSold,
  coalesce(p.TotalStock, 0) - coalesce(s.TotalSold, 0) BalanceStock
from tbl_matentry e
left join
(
  select item, sum(qty) TotalSold
  from tbl_sales
  group by item
) s
  on e.eid = s.item
left join
(
  select item, sum(qty) TotalStock
  from tbl_purchases
  group by item
) p
  on e.eid = p.item;

See SQL Fiddle with Demo.

This could also be written using a single subquery to tbl_sales:

select e.eid,
  e.item,
  sum(p.qty) TotalStock,
  coalesce(s.TotalSold, 0) TotalSold,
  coalesce(sum(p.qty)) - coalesce(s.TotalSold, 0) BalanceStock
from tbl_matentry e
left join tbl_purchases p
  on e.eid = p.item
left join
(
  select item, sum(qty) TotalSold
  from tbl_sales
  group by item
) s
  on e.eid = s.item
group by e.eid, e.item, TotalSold;

See SQL Fiddle with Demo


PostgreSQL sort by max date and get the difference I've got a table with user id, balance (amount only increases) and date created. So, each month I can get last amount by user and date created.
I'm doing it like that:

SELECT balance
FROM balances
WHERE id IN (
    SELECT max(id)
    FROM balances
    WHERE "userId" = 123
    AND datecreated IN (
        SELECT max(datecreated)
        FROM balances
        WHERE "userId" = 123
        AND EXTRACT(YEAR FROM date(datecreated)) = 2018
        AND EXTRACT(MONTH FROM date(datecreated))::integer = 8
    )
)

First question: can it be done somehow better?
Second question: I can find the income by finding difference between current month last balance and previous month last balance.

SELECT (
SELECT balance
FROM balances
WHERE id IN (
    SELECT max(id)
    FROM balances
    WHERE "userId" = 123
    AND datecreated IN (
        SELECT max(datecreated)
        FROM balances
        WHERE "userId" = 123
        AND EXTRACT(YEAR FROM date(datecreated)) = 2018
        AND EXTRACT(MONTH FROM date(datecreated))::integer = 8
    )
) - (
SELECT balance
FROM balances
WHERE id IN (
    SELECT max(id)
    FROM balances
    WHERE "userId" = 123
    AND datecreated IN (
        SELECT max(datecreated)
        FROM balances
        WHERE "userId" = 123
        AND EXTRACT(YEAR FROM date(datecreated)) = 2018
        AND EXTRACT(MONTH FROM date(datecreated))::integer = 7
    )
)
) AS "month_income";

It works when I've got data for previous balance. But How can I select 0 if there is nothing in previous month? Tried to do CASE like:

SELECT
    CASE WHEN balance IS NULL THEN 0
    ELSE balance
    END AS balance
FROM balances
WHERE id IN ( ... )

It does not return anything (even NULL value) when, for example, I'm setting WHERE id IN NULL. Also tried COALESCE (balance, 0). But the problem is that nothing returned.

If there is exactly one maximum balance per month, the first query can be simplified to

select balance
from balances
where user_id = 123
  and date_trunc('month', datecreated) = date '2018-08-01'
order by datecreated desc
limit 1;

The difference between a specific month and the previous one, can be calculated with a window function:

select b.datecreated,
       b.balance,
       coalesce(prev.balance, 0) as prev_balance
from balances b
  left join lateral (
    select balance
    from balances b2
    where b2.user_id = b.user_id
      and b2.datecreated < date_trunc('month', b.datecreated)
    order by datecreated desc
    limit 1
  ) as prev on true
where user_id = 123
  and date_trunc('month', datecreated) = date '2018-08-01'
order by datecreated desc
limit 1;

Online example: https://rextester.com/WGTPBO61473


How do I read Query Cost, and is it always a percentage? I'm currently studying for SQL 70-433 (the Microsoft Certification exam), and I'm getting very confused about the "query cost" performance metric.

According to any documentation I could find via Google, the query cost is a percentage figure, and represents the percentage of the whole batch taken up by any one part of it.
This already seemed a little odd to me, as I am interested in the absolute merit of a particular query, rather than its merit relative to other queries which happen to appear alongside it.

But then I thought, well, maybe what you are expected to do is place two alternative queries side by side, run them as "a batch", and then whichever one has a cost less than 50% is the winner.

But the discussion of query cost in chapter 6, lesson one of Microsoft's SQL 70-433 Training Kit doesn't seem to bear any relation to this.

Here is an example: They show a query containing two correlated sub-queries, and then improve on it by replacing the sub-queries with an OUTER APPLY. The result: "This query has a cost of roughly 76, while the first query's cost was double that, about 151."
They then improve the query even further, and reduce the cost from 76 to 3.6. They do not imply these figures are percentages, whereas they do imply that they are absolute figures which relate to the query as a standalone object, without reference to any other queries. And anyway, how could the first query have a cost of 151%?

Later on in the chapter, they show a screenshot of an execution plan which has three parts. The first says "Cost: 0%", the second says "Cost: 1%" and the last says "Cost: 99%" but the text (of the book itself) below the screenshot "The cost of this query is 0.56".
I'm guessing that they mean some other kind of cost, but I can't find reference to it elsewhere.

Can someone help? I'm thoroughly confused.

The query cost is reported in execution plans as "estimated subtree cost". This is an absolute figure such as 1.5. Conor Cunningham mentioned in a SQLBits presentation that it originally referred to the number of seconds taken to execute on a particular Microsoft employee's machine ("Nick's Machine")  in the SQL Server 7 days

but now should be interpreted as a unitless measure of overall cost.

The execution plan is a tree. Each iterator in the tree is given an estimated CPU cost and an estimated IO cost and these are added together to get the overall cost (the relative weightings can be adjusted with a couple of undocumented DBCC commands). The estimated subtree cost includes the cost for the iterator itself and all its descendants. To see an example of the costing formulas used you can look at this article.

To determine the estimated cost for an entire query in SSMS select the root iterator (e.g. the SELECT iterator) to the left of the graphical plan and look at this metric in the SSMS properties window.

When running multiple queries (whether in the same batch or not) the percentage is calculated by adding up all of these values and calculating the percentage as you would expect.

You should be aware that even in actual execution plans this cost figure is based on estimates and using this to compare the relative merits of two different queries can be woefully wrong in cases where the estimates are inaccurate.


User has Areas, which have Buildings, 3 PK's or tables in between? I'm building a database which has the following

User
Area
Building

A User has many areas, and a user can add buildings to his/her areas.
I can up with the following idea but I feel like there's some duplication going on
user (id, name)
user_area (user_id, area_id)
area (id, size)
area_building (area_id, building_id, user_id)

Since this allows an area_building that is put on an area that doesn't belong to the user.
Would it be better to have this layout?
user (id, name)
user_area (id, user_id, area_id) <-----
area (id, size)
user_area_building (user_area_id, building_id) <-----

A user can have an area and build on it, another user can also have that area and build on it, but they won't ever see each other's building.

From the question and comments:

A user can have an area and build on it, another user can also have that area and build on it, but they won't ever see each other's building.

Based on this, both solution 1 and 2 are ok.

The user_area table won't be a many-to-many table then, but become it's own entity.

Yes, it would a many-to-many table and an entity of its own. That's only a matter of definition really. If you have additional attributes (eg inventory per user and area), then the columns can be added in the user_area table. Then the table is more obviously an entity of its own but it is still a many-to-many table.

Let's say that later a user can also have an inventory for a certain area of his, and there can be payments added to a certain area, would solution one still be okay? Each of these new tables will have a reference to both user_id and area_id.

The payments table would need to be separate and have a FK referencing user_area.
If it were payments associated to user and building, it would be a separate table with a FK referencing building.

A user has many areas, and a user can add buildings to his/her areas.

Based on this, the area_building from the 1st solution looks correct but with a FK on (area_id, building_id) to user_area
area_building (area_id, building_id, user_id)

So my suggestion would be to use solution 1 with the following FKs:
user (id, name)
area (id, size)
user_area (user_id, area_id)
building (area_id, building_id, user_id)

I would also just rename it to building and have PK the (building_id). I would also get rid of the id names, in favour of more descriptive id names but that's a personal preference.
In detail:
user
    user_id    PK
    name
area
    area_id    PK
    size
user_area
    user_id    PK  FK1 -> user (user_id)
    area_id    PK  FK2 -> area (area_id)
building
    user_id         FK3 -> user_area (user_id, area_id)
    area_id         FK3
    building_id  PK


how to do select more then 3 table add in select IN() function SELECT M.post_id, M.uid_fk, M.message, M.image1,M.roles, M.created,U.uname, U.f_name, U.l_name
 FROM posts M, person U
 WHERE M.uid_fk=U.p_id
    AND M.uid_fk in (
      (select send_id from friend_request where p_id = '7' AND status=1)
        or
     (select p_id from friend_request where send_id = '7' AND status=1)
        AND
     (select send_id from follower_request where p_id = '7' AND status=1 AND M.uid_fk = '7')
        AND
     M.uid_fk = 7
  )
 order by M.post_id DESC

and made by i can't got current data.

how to i get like this query

SELECT M.post_id, M.uid_fk, M.message, M.image1,M.roles, M.created,U.uname, U.f_name, U.l_name
 FROM posts M, person U
 WHERE M.uid_fk=U.p_id
  AND M.uid_fk in (1,2,3,4,7,8,9)
 order by M.post_id DESC

Hope It will Help.

SELECT M.post_id, M.uid_fk, M.message, M.image1,M.roles, M.created,U.uname, U.f_name, U.l_name
 FROM posts M, person U
 WHERE M.uid_fk=U.p_id
    AND M.uid_fk in ( SELECT id FROM (
      (select send_id as id from friend_request where p_id = '7' AND status=1)
        UNION ALL
     (select p_id as id from friend_request where send_id = '7' AND status=1)
        UNION ALL
     (select send_id as id from follower_request where p_id = '7' AND status=1)
 ) as ids

  ) AND M.uid_fk = 7
 order by M.post_id DESC


Conditional INSERT with a nested CTE? I am trying to figure out if there is a way I can make nested CTEs work for this particular case.

Consider the following (highly contrived) scenario which is based on the actual application: there's a single column table of employee id's. Then there's an employee property table with all the details. (The main reason behind the single col table is often as a matter of course new employee id's need to be created in lots and assigned before any details of the actual staff is known.)

Now to the task at hand, we are inserting details (i.e. the name) for a new employee, but first we need to check if an employee with that name already exists. If it does, we'll simply return the id, and if not, we'll create a new employee record and then insert the details, finally returning the newly created id.

To recreate this test scenario:

CREATE TABLE public.employee (
    id text DEFAULT gen_random_uuid(),
    PRIMARY KEY (id)
);

CREATE TABLE public.employee_details (
    employee_id text,
    name text,
    PRIMARY KEY (employee_id),
    FOREIGN KEY (employee_id) REFERENCES public.employee(id)
);

The query I am trying to hammer into shape looks like shown below.

with
e as
    (select name, employee_id from employee_details where name = 'jack bauer'),

i as (insert into employee_details (name, employee_id)
    select 'jack bauer',
        (with a as (insert into employee values(default) RETURNING id) select a.id from a)
    where not exists (select 1 from e) returning name, employee_id)

select employee_id, name from e
union all
select employee_id, name from i;

If I replace the nested CTE with an already created id (executing the nested CTE separately), it works (but can result in the creation of a superfluous id). It is also possible to simply move the nested CTE to the top level (so the whole thing will look like with e as (..), i as (..), a as (..) select .. where not exists..., but this would also mean that a superfluous employee id is created where no details need to be inserted. I want to figure out a way to do it "inline" - so the new id will only be created if the not exists clause returns true.

I keep getting the error:

  WITH clause containing a data-modifying statement must be at the top level.

I guess the trouble is that the nested CTE returns a "column" whereas the whole query will work if it gets a "value" instead (which it does, when one simply copies in a text value instead of the CTE). I did come across a somewhat related discussion at this question mentioning an apparent bug which has been fixed since 9.3. I don't know if that is related to my troubles here. To quote from the linked discussion:

  The parse analysis code seems to think that WITH can only be attached to the top level or a leaf-level SELECT within a set operation tree; but the grammar follows the SQL standard which says no such thing

I'm using Postgres 10.3.

For the purpose of this question, I'll assume employee_details.name to be defined UNIQUE. Else, the whole operation wouldn't make sense.

You cannot nest a data-modifying CTE like you tried (as you already found out the hard way) - and you don't need to. This query would achieve your objective:

WITH e AS (
   SELECT name, employee_id
   FROM   employee_details
   WHERE  name = 'jack bauer'
   )
 , i1 AS (
   INSERT INTO employee             -- no target columns!
   SELECT                           -- empty SELECT list!
   WHERE NOT EXISTS (SELECT FROM e)
   RETURNING id
   )
 , i2 AS (
   INSERT INTO employee_details (name, employee_id)
   SELECT 'jack bauer', id
   FROM   i1
   RETURNING name, employee_id
   )
SELECT employee_id, name FROM e
UNION ALL
SELECT employee_id, name FROM i2;

The core feature is the INSERT with no target columns and an empty SELECT. Postgres fills all columns not listed in the SELECT with default values. This way we can replace the unconditional VALUES (default) with a conditional INSERT. The CTE i1 only inserts a row if the given name was not found.

The manual:

  If no list of [target] column names is given at all, the default is all the
  columns of the table in their declared order; [...]

  Each column not present in the explicit or implicit column list will
  be filled with a default value, either its declared default value or
  null if there is none.

This is a Postgres specific extension of the standard:

  Also, the case in which a column name list is omitted, but not all the
  columns are filled from the VALUES clause or query, is disallowed by the standard.

The final CTE i2 only inserts a row if i1 returned a row. Voilá.

This is subject to race conditions under concurrent write load to the same tables. If you need to rule that out, you need to do more. Related:

How to use RETURNING with ON CONFLICT in PostgreSQL?

Without the complications from the conditional INSERT in the 2nd table, this would boil down to a common case of SELECT or INSERT:

Is SELECT or INSERT in a function prone to race conditions?

Aside

"id" text DEFAULT gen_random_uuid()

I'd strongly advise to use the data type uuid to store UUIDs.

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
What is the optimal data type for an MD5 field?


SQL Server 2008 Full-Text Search installation I have full version of SQL Server 2008 R2 on my machine. Only problem is when my computer was created (by the IT Tech's at my work) they did not add the Full-Text Search feature to the installation. I would like to know if there is anywhere that you can download this feature from online. And if there is how do i install it?

Thanks.

Full-Text Search is a feature on the installation media. You will need the installation media or binaries and go through setup and add the feature to an existing installation of SQL Server. You cannot download Full-Text search as a standalone installation.


How to get job start time inside of a TSQL agent job step? Is there a good way to query the job start time inside of a given sql agent job step (TSQL)?  This will help with step 3 in the following scenario.
Job Foo (Run Daily @ 11:30pm)
Step1 - StartTime = Saturday 11:30pm TSQL - (15 minute duration)
Step2 - StartTime = Saturday 11:45pm TSQL - (30 minute duration)
Step3 - StartTime = Sunday   12:15am TSQL - Exit early / don't run unless Foo job was started on a Saturday.

There is a lot of information about the jobs and SQL Server Agent in msdb.
You can check it out below, I added a few columns that help you in your thoughts. Maybe you want to check If this job success or not (in the message column).
SELECT  JobName = sj.name,
        StepName = jh.step_name,
        RunDateTime = msdb.dbo.agent_datetime(run_date, run_time),
        EndDateTime = DATEADD(SECOND, run_duration / 10000 * 3600 + run_duration % 10000 / 100 * 60 + run_duration % 100, msdb.dbo.agent_datetime(jh.run_date, jh.run_time)),
        jh.message
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory jh ON sj.job_id = jh.job_id
where step_id=0

For the comment: There is no clearly answered yet about that. So, you should try it.
Microsoft says;

In most cases the data is updated only after the job step completes
and the table typically contains no records for job steps that are
currently in progress, but in some cases underlying processes do
provide information about in progress job steps.


Sybase 12 to behave like Oracle and SQL Server 2014 - non blocking transaction read We utilize an ODBC connection so our code can access various databases - namely Oracle, Sybase 12 and SQL Server 2014.

NOTE : We have a table called which we use for locks. This table only ever has one record in it and it is either empty or populated with whom has the lock.

I have a scenario where whilst the application has an ODBC connection to Oracle or SQL Server 2014 the application runs fine, the test scenario being - two processes accessing the db - one doing a long running task the other doing short tasks. If I connect to Sybase and do the same activities one of the processes blocks (the short task one). I am guessing here that in Sybase the lock table is written to, locked grabbed, and released within a transaction and the other process is awaiting the commit\rollback on the transaction to use the table next.

Why does it work in SQL Server 2014 and Oracle but block in Sybase 12? What option needs to be ON\OFF in Sybase 12 to get the behavior of Oracle and SQL Server 2014?

I finally found the options of the database in Sybase Central and had a play with various options and I got it to work with turning ON "allow_snapshot_isolation" it was OFF by default.


Removing duplicate rows with two or more left join tables With a query like this

SELECT a.id, a.name,
       COALESCE( json_agg(b.*), '[]'::json ),
       COALESCE( json_agg(c.*), '[]'::json ),
  FROM a
  LEFT JOIN b ON a.id = b.a_id
  LEFT JOIN c ON a.id = c.a_id
 GROUP BY a.id, a.name;

When executed, both c and b will be multiplied by each other and produce duplicated entries in the JSON array object.

I tried changing the query to use 2 subqueries instead, but I get all sorts of errors and warning, such as "Subquery must return only one column", etc.

I also tried using LEFT OUTER JOIN, but I guess I don't yet master how joining tables work as it only applies to b and c is still multiplied and contain duplicates.

Edit: using DISTINCT errors with "could not identify an equality operator for type json" on the COALESCE functions.

How can I fix this query and only aggregate unique rows?

Edit 2

I need to specify that both table b and c are actually VIEWs, and they both have at least one json_agg column as well, so I cannot just use json_agg(DISTINCT b.*). This would've been way too easy.

Edit 3

Here is a small snippet to reproduce the issue :

--DROP TABLE IF EXISTS tbl_a CASCADE;
--DROP TABLE IF EXISTS tbl_b CASCADE;
--DROP TABLE IF EXISTS tbl_c CASCADE;

CREATE TABLE tbl_a (
  id bigserial NOT NULL,
  name character varying(16),
  CONSTRAINT "PK_tbl_a" PRIMARY KEY (id)
) WITH ( OIDS=FALSE );

CREATE TABLE tbl_b (
  a_id bigint NOT NULL,
  foo json NOT NULL DEFAULT '{}'::json,
  CONSTRAINT "FK_tbl_b_a" FOREIGN KEY (a_id)
      REFERENCES tbl_a (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
) WITH ( OIDS=FALSE );

CREATE TABLE tbl_c (
  a_id bigint NOT NULL,
  bar json NOT NULL DEFAULT '{}'::json,
  CONSTRAINT "FK_tbl_c_a" FOREIGN KEY (a_id)
      REFERENCES tbl_a (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
) WITH ( OIDS=FALSE );

INSERT INTO tbl_a (id,name) VALUES (1, 'Test');
INSERT INTO tbl_b (a_id, foo) VALUES (1, '{"foo":"Hello"}'::json), (1, '{"foo":"World"}'::json);
INSERT INTO tbl_c (a_id, bar) VALUES (1, '{"bar":"abc"}'::json), (1, '{"bar":"def"}'::json);

SELECT tbl_a.id, tbl_a.name,
       COALESCE(json_agg(tbl_b.*), '{}'::json),
       COALESCE(json_agg(tbl_c.*), '{}'::json)
  FROM tbl_a
  LEFT JOIN tbl_b ON tbl_a.id = tbl_b.a_id
  LEFT JOIN tbl_c ON tbl_a.id = tbl_c.a_id
 GROUP BY tbl_a.id, tbl_a.name;

Returns

id  name    coalesce                              coalesce
--  ------  ------------------------------------  ----------------------
 1  "Test"  "[{"a_id":1,"foo":{"foo":"World"}},   "[{"a_id":1,"bar":{"bar":"abc"}},
             {"a_id":1,"foo":{"foo":"Hello"}},    {"a_id":1,"bar":{"bar":"abc"}},
             {"a_id":1,"foo":{"foo":"World"}},    {"a_id":1,"bar":{"bar":"def"}},
             {"a_id":1,"foo":{"foo":"Hello"}}]"   {"a_id":1,"bar":{"bar":"def"}}]"

I have found a solution. I am not sure if it is optimal, but it works.

SELECT tbl_a.id, tbl_a.name,
       COALESCE( ( SELECT json_agg(tbl_b.*)
                     FROM tbl_b
                    WHERE tbl_b.a_id = tbl_a.id ), '{}'::json),
       COALESCE( ( SELECT json_agg(tbl_c.*)
                     FROM tbl_c
                    WHERE tbl_c.a_id = tbl_a.id ), '{}'::json)
  FROM tbl_a;

Which correctly returns

id  name    coalesce                             coalesce
--  ------  -----------------------------------  ----------------------
 1  "Test"  "[{"a_id":1,"foo":{"foo":"World"}},  "[{"a_id":1,"bar":{"bar":"abc"}},
              {"a_id":1,"foo":{"foo":"Hello"}},    {"a_id":1,"bar":{"bar":"def"}}]"


SQLcmd error 'Failed to initialize sqlcmd library with error number -2147467259' I'm trying to update some maintenance plans to make use of Ola Hallengren's IndexOptimize,
but also mail the result of the operation using msdb.dbo.sp_send_dbmail.
Old T-SQL:
declare @indexQuery nvarchar(1000);

set @indexQuery = 'USE [MyDB];
    SELECT i.index_id, i.name, s.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (
      DB_ID(),
      OBJECT_ID(N''MyTable''),
      DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
    WHERE s.object_id = i.object_id
      AND s.index_id = i.index_id;';

exec msdb.dbo.sp_send_dbmail
    @profile_name = 'Local Mail Profile'
   ,@recipients = 'someone@somewhere.co.uk'
   ,@subject = 'Re-index Started'
   ,@query = @indexQuery
   ,@attach_query_result_as_file = 1
   ,@query_attachment_filename = 'Reindex BEFORE.txt'

...and this works perfectly fine.
But if I modify @indexQuery thusly:
set @indexQuery = 'IndexOptimize ''MyDB''';

...to make use of the IndexOptimize procedure, the result is:

Failed to initialize sqlcmd library with error number -2147467259.

I can't seem to find anything related to the error number anywhere to work out what the problem is.
Can anyone please shed any light on it for me?

After hunting around a lot more, I found a few articles discussing security and granting permissions that sent me on a wild goose chase and ultimately made no difference to the outcome.

The script continued to work outside of sp_send_dbmail, but would not work with it.

I don't recall how, but I have subsequently discovered that stored procedures the indexing script allegedly uses had gone AWOL (ones created by the file CommandExecute.sql), even though the indexing script worked fine outside of sp_send_dbmail.

Once I created those stored procedures, everything seems to work as it should!


Difference between logical and physical reads/writes In short, I would like to understand the difference between physical and logical reads/writes in a database. When (which threshold) should I be worried about them? Is this post an accurate description? (Even in that case, I am still a bit unclear as to what can be considered high reads or writes).

I am doing level 2 support at this moment and the drive where our production database resides is experiencing severe performance problems:

I ran 2 of Glenn Berry's diagnostic information queries to view the top 10 stored procedures in terms of total logical writes and average I/O and got the following back:

My task is to identify the stored procedures that may be causing the low %idle time so our product team can review the code.

In short, I would like to understand the difference between physical and logical reads/writes in a database

A very terse explanation of the difference between logical and physical reads can be seen in the Microsoft documentation for the diagnostic command SET STATISTICS IO here:

logical reads - Number of pages read from the data cache.
physical reads - Number of pages read from disk.

Note that when it says "data cache" there it means RAM.

I've never heard of anyone talk about logical writes so I can't speak to that one.  If that just means writes, then it's talking about inserts, updates, and deletes.

Regarding this:

  My task is to identify the stored procedures that may be causing the low %idle time so our product team can review the code.

Looking at the graph in your question, I do see the period of high disk utilization ("low idle time").  If you can identify what procedures are running during that time (for instance, by logging sp_whoisactive to a table), then you can try to tune those specific queries (or post questions on this site with sufficient detail to get some help).


Interpreting sys.dm_os_sys_memory I'm trying to figure out a way(using only TSQL) to determine if and how much the PAGEFILE is used (I will capitalize this term if I refer to the windows page file from now on - like pagefileusage% counter).

My first thought is using sys.dm_os_sys_memory. I am a little bit confused here.
The description of the total_page_file_kb is that this is a commit limit, so basically, it's the size of the physical memory (RAM) + size of the PAGEFILE.  So far it is ok.
But what exactly is the available_page_file_kb?
Is it referring to the commit limit or to the PAGEFILE?
I tried to use these calculations:

Assuming that available_page_file_kb referring to the commit limit:

PAGEFILE usage% = (available_page_file_kb-available_physical_memory_kb) / (total_page_file_kb-total_physical_memory_kb)

Assuming that available_page_file_kb is referring to the PAGEFILE

PAGEFILE usage% = (available_page_file_kb) / (total_page_file_kb-total_physical_memory_kb)

Unfortunately none of these calculations show me the right results.
I keep checking the pagefileusage% counter on some of my test servers, and sometimes the first formula is wrong by only 1-3% and the second one by 15-25% or vice versa, the second one by 1-3% and the first one by 15-25%,

What did I miss here? Is it impossible to determine (or even just get a close estimate) the pagefileusage% with sys.dm_os_sys_memory dmv?

Could it be that you need to take into account the System Cache and Kernel Memory pages?

It would help a lot if you added some actual numbers from your server and what you expect, to your question.

For example, here is my test server's memory state:

The code is:

SELECT 'Page File' AS MemoryType
    ,CONVERT(DECIMAL(10, 2), 1.0 * total_page_file_kb / 1024) AS Total_MB
    ,CONVERT(DECIMAL(10, 2), 1.0 * available_page_file_kb / 1024) AS Free_MB
    ,CONVERT(VARCHAR(5), CONVERT(DECIMAL(10, 1),
        100.0 * available_page_file_kb / total_page_file_kb)) + '%' AS Free
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 'Physical (RAM)'
    ,CONVERT(DECIMAL(10, 2), 1.0 * total_physical_memory_kb / 1024)
    ,CONVERT(DECIMAL(10, 2), 1.0 * available_physical_memory_kb / 1024)
    ,CONVERT(VARCHAR(5), CONVERT(DECIMAL(10, 1),
        100.0 * available_physical_memory_kb / total_physical_memory_kb)) + '%'
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 'Non-Physical'
    ,CONVERT(DECIMAL(10, 2),
         1.0 * (total_page_file_kb - total_physical_memory_kb) / 1024)
    ,CONVERT(DECIMAL(10, 2),
         1.0 * (available_page_file_kb - available_physical_memory_kb) / 1024)
    ,CONVERT(VARCHAR(5), CONVERT(DECIMAL(10, 1),
       100.0 * (available_page_file_kb - available_physical_memory_kb)
             / (total_page_file_kb - total_physical_memory_kb))) + '%'
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 'System Cache',CONVERT(DECIMAL(10, 2), 1.0 * system_cache_kb / 1024),NULL,NULL
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 'Kernel: Paged'
    ,CONVERT(DECIMAL(10, 2), 1.0 * kernel_paged_pool_kb / 1024),NULL,NULL
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 'Kernel: Non-Paged'
    ,CONVERT(DECIMAL(10, 2), 1.0 * kernel_nonpaged_pool_kb / 1024),NULL,NULL
FROM sys.dm_os_sys_memory
ORDER BY Total_MB DESC