How to improve query to include 2 critical pieces of employee data I’m going to preface by saying that I’m fairly green with SQL.

My code below works, however I am missing two vital pieces of my employee population: (1) those employees who have been invoiced but had no corresponding payroll deduction, and (2) those employees who experienced payroll deductions but were not invoiced. The code, as it is now, only performs the arithmetic on employees who have corresponding data in both the Employee_Deductions and Employee_Invoices tables. I've been reading up on CASE, but am not certain if that's the path I should go down. I greatly appreciate any constructive critiques and suggestions on how to arrive at my desired result. Thank you.

SELECT
    PayrollpersonID,
    PayrollDeductionYear,
    January,
    February,
    March,
    April,
    May,
    June,
    July,
    August,
    September,
    October,
    November,
    December,
    Total
FROM
    (SELECT
        PayrollpersonID,
        PayrollDeductionAmount-i.invoice as Variance,
        PayrollDeductionMonth,
        PayrollDeductionYear,
        Total = sum(PayrollDeductionAmount -i.invoice) over (partition by PayrollpersonID)
    FROM
        Employee_Deductions as d,
        (SELECT
        InvoicepersonID,
        sum(isnull(v.InvoiceAmount,0.00)) as invoice,
        InvoiceProcessedMonth,
        InvoiceProcessedYear
        FROM
            Employee_Invoices as v
        WHERE InvoiceProcessedYear = '2015'
        GROUP BY InvoicepersonID, InvoiceProcessedMonth, InvoiceProcessedYear) as i
    WHERE
        d.PayrollpersonID = i.InvoicepersonID and
        d.PayrollDeductionMonth = i.InvoiceProcessedMonth and
        d.PayrollDeductionYear = i.InvoiceProcessedYear and
        d.PayrollDeductionYear = '2015' and
        d.PayrollDeductionAmount > 0)
SOURCE PIVOT
(
SUM(Variance) FOR PayrollDeductionMonth in (January, February, March, April, May, June,
                                            July, August, September, October, November, December)
)
AS pivottable
ORDER BY PayrollpersonID;

Table Schema:

The [Employee_Deductions] table has the following columns:

PayrollDeductionYear nvarchar,
PayrollDeductionMonth nvarchar,
PayrollpersonID nvarchar,
LastName nvarchar,
FirstName nvarchar,
PayrollDeductionAmount money

The [Employee_Invoices] table has the following columns:

InvoiceProcessedYear nvarchar,
InvoiceProcessedMonth nvarchar,
InvoicepersonID nvarchar,
EmployeeLastName nvarchar,
EmployeeFirstName nvarchar,
EmployeeEmail nvarchar,
OrganizationID nvarchar,
OrgName nvarchar,
OrgAddress1 nvarchar,
OrgAddress2 nvarchar,
OrgCity nvarchar,
OrgState nvarchar,
OrgZip nvarchar,
OrgEIN nvarchar,
InvoiceAmount money

Result Table:

Without the schema this is a little difficult to test but I think this will work. Even if I do make some syntax errors you will be able to see what I am getting at.

SELECT
    PayrollpersonID,
    PayrollDeductionYear,
    SUM(CASE WHEN PayrollDeductionMonth = 'January' THEN Variance ELSE 0 END) AS January,
    MAX(CASE WHEN (PayrollDeductionMonth = 'January' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'January No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'January' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'January No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'February' THEN Variance ELSE 0 END) AS February,
    MAX(CASE WHEN (PayrollDeductionMonth = 'February' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'February No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'February' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'February No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'March' THEN Variance ELSE 0 END) AS March,
    MAX(CASE WHEN (PayrollDeductionMonth = 'March' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'March No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'March' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'March No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'April' THEN Variance ELSE 0 END) AS April,
    MAX(CASE WHEN (PayrollDeductionMonth = 'April' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'April No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'April' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'April No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'May' THEN Variance ELSE 0 END) AS May,
    MAX(CASE WHEN (PayrollDeductionMonth = 'May' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'May No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'May' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'May No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'June' THEN Variance ELSE 0 END) AS June,
    MAX(CASE WHEN (PayrollDeductionMonth = 'June' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'June No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'June' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'June No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'July' THEN Variance ELSE 0 END) AS July,
    MAX(CASE WHEN (PayrollDeductionMonth = 'July' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'July No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'July' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'July No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'August' THEN Variance ELSE 0 END) AS August,
    MAX(CASE WHEN (PayrollDeductionMonth = 'August' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'August No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'August' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'August No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'September' THEN Variance ELSE 0 END) AS September,
    MAX(CASE WHEN (PayrollDeductionMonth = 'September' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'September No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'September' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'September No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'October' THEN Variance ELSE 0 END) AS October,
    MAX(CASE WHEN (PayrollDeductionMonth = 'October' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'October No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'October' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'October No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'November' THEN Variance ELSE 0 END) AS November,
    MAX(CASE WHEN (PayrollDeductionMonth = 'November' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'November No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'November' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'November No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'December' THEN Variance ELSE 0 END) AS December,
    MAX(CASE WHEN (PayrollDeductionMonth = 'December' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'December No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'December' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'December No Invoice',
    Total
FROM
    (SELECT
        PayrollpersonID,
        PayrollDeductionAmount,
        i.invoice,
        PayrollDeductionAmount-i.invoice as Variance,
        PayrollDeductionMonth,
        PayrollDeductionYear,
        Total = sum(PayrollDeductionAmount -i.invoice) over (partition by PayrollpersonID)
    FROM
        Employee_Deductions as d,
        (SELECT
        InvoicepersonID,
        sum(isnull(v.InvoiceAmount,0.00)) as invoice,
        InvoiceProcessedMonth,
        InvoiceProcessedYear
        FROM
            Employee_Invoices as v
        WHERE DeductionYear = '2015'
        GROUP BY InvoicepersonID, InvoiceProcessedMonth, InvoiceProcessedYear) as i
    WHERE
        d.PayrollpersonID = i.InvoicepersonID and
        d.PayrollDeductionMonth = i.InvoiceProcessedMonth and
        d.PayrollDeductionYear = i.InvoiceProcessedYear and
        d.PayrollDeductionYear = '2015' and
        d.PayrollDeductionAmount > 0) AS a
GROUP BY PayrollpersonID,
    PayrollDeductionYear,
    Total
ORDER BY PayrollpersonID;

You could have done this by stacking pivot statements but this can be messy and inefficient. This is a different way of pivoting data called the cross tab method. This method is usually more efficient than the pivot statement and easier to read.


WSFC File Share Witness I've got a multi-site 2008 R2 WSFC with a file share witness. I noticed that the possible owners for the FSW is every node in the cluster. I thought the concept of a FSW was that no node of the cluster owns it. Its a witness that sits outside of the cluster. So what is the purpose of having "owners" of a FSW resource and what would be the effect if I limited the possible owners to just those nodes at the primary site?

This secondary site of this cluster only exists to support a SQL Always On Availability group that's using asnyc commit for DR. Failover to the DR site will be manual. All nodes at the DR site are set to NodeWeight = 0 so they don't participate in quorum anyway.

The concepts of quorum, and owners are separate topics. Just because a member of the WSFC doesn't get a vote in quorum does not mean it can't own a resource. Additionally, SQL Server doesn't really play a role at all--the same concepts apply regardless of what type of clustered resource you're dealing with.

Quorum:
The quorum is the number of votes necessary to transact business on your WSFC. Depending on your WSFC configuration, voters can be nodes (servers), a drive, or a file share. You need more than 50% of your votes in order for the WSFC to be online. If you lose 50% or more of your voters, then the WSFC and all clustered services (including your FCI) will go offline and not come back until you have (or force) quorum.

In your configuration, you have a File Share Witness (hopefully in an impartial location reachable by both primary & DR sites), and you've also changed the NodeWeight to 0 for your DR servers. Rather than thinking of NodeWeight as "The DR servers don't get to vote," you should think of it as "The DR servers get to vote, but their vote doesn't count." They are still there, they're still part of the WSFC, it's just that the WSFC doesn't listen.

Even though the FSW isn't hosted on the cluster (or, perhaps, more accurately, because the FSW isn't hosted on the cluster!), it still has to be "present" to vote. Which brings us to the concept of ownership...

Cluster Owner/Host Server:
Your WSFC has a network name and an IP address. That name & IP has to be tied to a machine that is part of your cluster. More specifically, it can be tied to any one machine in your cluster. This is part of your WSFC.

In your scenario, your DR servers have no vote for quorum, but they still must be possible owners of the WSFC Host Server. If you manually fail over to your DR site (which will involve forcing quorum because you have zero voters in DR), then one of your DR servers must host the cluster name & IP. If it doesn't, then your cluster can't come online.

Your FSW is "owned" by the same server that owns/hosts the Cluster itself. Therefore it must have all servers, including DR, as possible owners. When you do force quorum and come online in your DR site, the WSFC is going to want to continue talking to the FSW.

Your original question:

  ...what would be the effect if I limited the possible owners to just
  those nodes at the primary site?

I suspect that you would have problems when you tried to force quorum and bring your WSFC online in the DR site--though, that's just a guess.

From a SQL Server perspective, you just care that you have quorum and the WSFC is reliably up. If you're having issues in that area, I'd look at the specific issues to your reliability. In reality, you probably don't really care which server is hosts your WSFC (and FSW).


MySQL cluster - how many hosts are needed for full HA? I need some clarification on MySQL-Cluster (NDB) setup

I want to achieve full HA but I'm low on server budget and can't afford to rent a dozen of servers for simple solution that I'm working on.

What's the minimum amount of hosts to achieve full HA in case of one node failure?

I mean situation that cluster should continue to work with no errors when any single physical servers fails. Failed server can be any of MySQL Cluster components (management, sql or data)

I don't need extreme performance, just fully automated server failure handling on small number of servers

From the MySQL Cluster documentation I know that I need at least two full copies of data (replicas). Does one replica need to be on more than one host?

Can HA be achieved on 4 servers? 2 management nodes (kind of small VPS) + 2 physical servers each running SQL and data node?

I'm aware of MySQL master-slave, but it doesn't provide automated failover and requires application rewrite

You really need to define what level of "HA" you are looking for quantitatively -- one man's "can-sleep-at-night' is another man's "this-thing-is-a-house-of-cards".

The minimum number of systems is two (2) -- An active and a standby replica (with something like heartbeat or custom-grown scripts to handle the failover).

With a MySQL cluster this means at least two SQL nodes and two Data nodes (to continue serving requests in the event of a failure of any one node). (If you also need redunancy on the management server's functions you would need two of those as well).

The key part here is testing the failover in a development environment -- which means you need at least two more machines (or a virtual machine). You also want to test upgrade and maintenance processes to ensure they won't trigger unintended consequences (Ideally you should do nothing to production that hasn't been tested and proven in Development).

If you fail to properly test you may trigger failover, which means you incur the procedural (and possibly business) cost of a failover -- typically having to rebuild the former active server to be a new standby server.

This protects against hardware failures (power supply, NIC, disk, switch (if they're on separate switches).

Note that this doesn't just apply to your DB servers -- You need two of *EVERYTHING: Web servers, DBI servers, Firewalls, DNS servers...
Redundancy of one component is meaningless if you still have a bunch of single-points-of-failure in your stack.

The next level of protection is network failures ("What if my ISP goes down?") - this requires replicating your whole redundant environment above to a remote datacenter.

What's important here is that you diversify network connections, power, etc -- You don't want your standby datacenter across the street where it's fed by the same power and fiber as your main facility.
A company I consulted for had a requirement that any remote facility used for DR be "at least 15 degrees of longitude away" (i.e. "In the next time zone").  A common practice in the US is East-Coast/West-Coast, or NY/Chicago LA/Texas.

The next level above that is truly distributed resources (think Google) which requires a database system that supports replication and sharding (think MongoDB).
If implemented properly there's almost no chance of a true "outage", though service may occasionally be degraded and recovery can take a while.


What is Atomic Relation in First Normal Form I have read the definition of 1NF which is, "If each attribute of relation is atomic". Please tell me what is Atomic.

1NF requires that every attribute position in every tuple in every relation contains a single value of the appropriate type. The types can be arbitrarily complex. In fact, the types can be relations. (CJ Date's book Database in depth: relational theory for practitioners treats this issue in a way that's pretty easy to understand.)

"Atomic" has never really meant "indivisible", which is why that term is finally falling out of favor. Loosely speaking, "atomic" means if a value has component parts, the dbms either ignores the existence of those parts, or it provides functions to manipulate them. For example, a timestamp value has these parts.

Year
Month
Day
Hours
Minutes
Seconds
Milliseconds

That kind of value is obviously divisible, and all database management systems provide functions to manipulate those parts. They also provide a way to select a timestamp as a single value. (Which, of course, it is.)


Why am I getting a snapshot isolation issue on INSERT? Given two tables

Parent

KeyID   GroupID   Name  Active

Child

KeyID   ParentID  Name

Child.ParentID is FKed to Parent.KeyID

We insert both Parent and Child in a single transaction.

If a different Parent row gets updated (eg. Active 1 -> 0) while the transaction is active, the Child INSERT fails with:

  Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Child' directly or indirectly in database 'Test' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

From what I can tell from Why am I getting "Snapshot isolation transaction aborted due to update conflict"? this is probably due to a full scan to verify the foreign key.

Indeed, removing the foreign key does allow the Child INSERT to complete as expected.

With that said, no amount of non-clustered indexes on the foreign key on Child table seem to be helping to resolve this issue, so I'm a bit at a loss of what to do.

We have RCSI turned on for this database and the transaction is running in Snapshot isolation mode.

Additional details

I have discovered this issue manifests when the insert to Child is larger than a given number of rows. At this point the query optimizer switches from a Nested Loops (Left Semi Join) to a Merge Join (Left Semi Join).

Apologies for not including the fact that multiple Child records are inserted for a single Parent record.

Working insert (20 child records):

Failing insert (50 child records):

Insert sproc is roughly this:

CREATE PROCEDURE dbo.[usp_InsertRecords] (
    @journal dbo.ParentType READONLY,
    @journalItems dbo.ChildType READONLY,
    @tenantId INT
) AS
BEGIN
    INSERT INTO dbo.Parent(GroupID, Name, Active, TenantId)
        SELECT GroupID, Name, Active, @tenantId FROM @journal

    DECLARE @JournalId INT = convert(int,scope_identity());

    INSERT INTO dbo.Child(ParentID, Name, TenantId)
        SELECT @JournalId, Name, @tenantId
        FROM @journalItems j2

END
GO

And concurrent update would be something like:

UPDATE dbo.Parent Set Active = 0 WHERE KeyID = 1234 -- row not being inserted

Add an OPTION (LOOP JOIN) hint to the INSERT statement.

Or use a plan guide (or query store) to force the nested loops semi join plan shape.

You might find that OPTION (FAST 1) works as well.

The point is to avoid a merge semi join, where many (potentially all) of the referenced tables' rows are touched by the current transaction. If any parent row with a change (including creation) is encountered, an update conflict error is raised.


Why does Oracle store time in DATE column? I have a table with a date column. I used JDBC to save a java.util.Date object into the table.

When I run following query I get date aswell as a time value:

select To_CHAR(departure_date,'DD-MM-YYYY HH:MI') from service

This tells me that date is column is also storing time.

This is unexpected. Shouldn't date type discard time value like an integer type discards fractional digits?

Oracle's DATE datatype behaves as both an ANSI DATE and TIME datatype. This was a design decision that only Oracle knows the reasoning for.

I'll add that Oracle isn't the only RDBMS that doesn't follow ANSI SQL standards in this way.

Obviously, you can remove the time portion with TO_CHAR(departure_date,'DD-MM-YYYY').


Non matching attribute 'INSTANT COLUMN(s)' between partition and table I have a program that regularly exchanges a partition with a table.

MyTable is a partitioned table, and MyTableTemplate is an empty copy of this table, with the same structure but no partitioning.

The process is the following:

CREATE TABLE tmp LIKE MyTableTemplate;

INSERT INTO tmp VALUES (...);
// more inserts...

ALTER TABLE MyTable EXCHANGE PARTITION p1 WITH TABLE tmp;
DROP TABLE tmp;

It worked fine so far, until I needed to add a column to the table:

ALTER TABLE MyTable         ADD COLUMN someColumn TINYINT(1) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE MyTableTemplate ADD COLUMN someColumn TINYINT(1) UNSIGNED NULL DEFAULT NULL;

Now when I run EXCHANGE PARTITION WITH TABLE, I get the following message:

  General error: 1731 Non matching attribute 'INSTANT COLUMN(s)' between partition and table

Oddly, if I do not add the column at the last position:

ALTER TABLE ... ADD COLUMN ... AFTER someField;

...then the partition exchange works fine!

What's this "INSTANT COLUMN(s) attribute" and what did I do wrong?

Is this a bug? I'm using MySQL 8.0.12.

Specify ALGORITHM=INPLACE (or ALGORITHM=COPY if in-place is not supported for your table's engine) in the ALTER TABLE statement explicitly.


SQL Server 2012 Data Change Tracking We currently have a front end application that is business critical and uses Microsoft SQL Server as the database provider. I have been asked to create another system that mimics some of the functions of this application and will insert/modify data in exactly the same way.

The only reason we are looking at doing this custom is because the developer does not provide any sort of API or assistance in manual data entry of their tables. Please note, we own the databases and all the data on the system but we still need the application to perform the day to day tasks.

I am looking for ways to track all changes made in a database (insert/update/delete) on all tables. I have a fresh copy of the system and a blank database to test, to give an example, I would generate a Purchase Order and then find out which tables were modified and what data was added so I could replicate in my code.

I know SQL has Data Change Tracking; however, I cannot see a way to easily implement this on all columns and tables within a database.

I am interested in any recommendations of third party applications or a walkthrough on how to utilise Microsoft's built in functions, appreciate the help if any.

I would recommend to script a loop or cursor to enable this on all of your databases and tables. It will capture all of the columns automatically.

Example of loop CDC Note: As always with code on the internet, evaluate cautiously and promote through personal and dev environments prior to production.

  By default, all of the columns in the source table are identified as captured columns. If only a subset of columns need to be tracked, such as for privacy or performance reasons, use the @captured_column_list parameter to specify the subset of columns.

CDC Configuration MSDN

I think it would be better to run SQL Profiler though to catch the code that is running and re-create it that way however. CDC will show you the end results, not how it gets from A to C.


How to change the login for a database's dbo user? I just finished a successful install of SQL Server 2014 Express, onto a domain-joined VM instance of Win8.1.

During setup which I ran under DOMAIN\Admin I accepted the default configuration of adding the current user as a SQL admin. Upon reconsideration after completion, however, I'd like to use the DOMAIN\DbAdmins group instead of a single user on the domain.

All well and good. Create the group, add it to SQL Server's Security node in SSMS, put it in the SysAdmin role, grant a 'Connect SQL' securable and we're off and running—right?

Not so fast.

The installer mapped DOMAIN\Admin to the dbo user on these two databases:

ReportServer
ReportServerTempDB

And of course, dbo can't be modified or removed.

I'd like to do this without uninstalling and reinstalling Reporting Services. Will it be possible to do so?

The group or login that is member of the sysadmin role is indepentent of who owns the database. The login that is mapped to the dbo of the database is basically the owner. You can change this via SSMS or with the query below:

ALTER AUTHORIZATION ON DATABASE::[ReportServer] TO [sa];
ALTER AUTHORIZATION ON DATABASE::[ReportServerTempDB] TO [sa];

You can find more information on the specifics about the database owner (dbo) from this question: What is the purpose of the database 'owner'?


How To Tell if Trigger is failing I have a trigger, that I want to try to update a field based off the value of another table.  I have manually verified that the data exists in the other table so I know the join will work.  However, data is being inserted into the table, but no update made.  Which makes me think I did something off with the trigger
Create Trigger [dbo].[trig_OnInsert] ON [dbo].[SHIPMENT]
For Insert
    AS
        BEGIN
            UPDATE si
            SET si.au = au.au + ' - ' + si.au
            FROM SHIPMENT si
            JOIN alphauniversal au
            ON si.cm_controllerID = au.controllerid
            WHERE si.cm_controllerID IN (Select si.cm_controllerID FROM Inserted)
        End

Also, if the trigger presents an error, or doesn't execute successfully, will the record still be inserted in the table?

I fear the where clause with the in is causing problems.  Try joining to the inserted table instead.  Here are two examples - one with the in clause and one with a join to the inserted table.  The results are different using my sample data.
drop table if exists shipment;
drop table if exists alphauniversal;
go
create table SHIPMENT (cm_controllerID int,au varchar(100))
create table alphauniversal (controllerid int, au varchar(100))
insert into alphauniversal values(1,'abc')
insert into alphauniversal values(2,'xyz')
go
Create Trigger [dbo].[trig_OnInsert] ON [dbo].[SHIPMENT]
For Insert
    AS
        BEGIN
            UPDATE si
            SET si.au = au.au + ' - ' + si.au
            FROM SHIPMENT si
            JOIN alphauniversal au
            ON si.cm_controllerID = au.controllerid
            WHERE si.cm_controllerID IN (Select si.cm_controllerID FROM Inserted)
            --JOIN inserted i on i.cm_controllerID=si.cm_controllerID
        End
go
insert into SHIPMENT values(1,'test1')
insert into SHIPMENT values(2,'test2')
select * from shipment

cm_controllerID     au
1                  abc - abc - test1
2                  xyz - test2

drop table if exists shipment;
drop table if exists alphauniversal;
go
create table SHIPMENT (cm_controllerID int,au varchar(100))
create table alphauniversal (controllerid int, au varchar(100))
insert into alphauniversal values(1,'abc')
insert into alphauniversal values(2,'xyz')
go
Create Trigger [dbo].[trig_OnInsert] ON [dbo].[SHIPMENT]
For Insert
    AS
        BEGIN
            UPDATE si
            SET si.au = au.au + ' - ' + si.au
            FROM SHIPMENT si
            JOIN alphauniversal au
            ON si.cm_controllerID = au.controllerid
            --WHERE si.cm_controllerID IN (Select si.cm_controllerID FROM Inserted)
            JOIN inserted i on i.cm_controllerID=si.cm_controllerID
        End
go
insert into SHIPMENT values(1,'test1')
insert into SHIPMENT values(2,'test2')
select * from shipment

cm_controllerID au
1               abc - test1
2               xyz - test2

The trigger code runs as part of the transaction that actually inserted the rows in the base table.  If the trigger encounters a fatal error, the transaction should roll back everything that was done during that transaction.  It's an all-or-nothing process.
You cannot swallow an exception inside a trigger even with try/catch logic.  Any exception inside a trigger WILL abort the transaction.


Storing revisions in SQL Server (simply much like TFS) So, what I've got so far:

Table: ChangeSets

Columns: Id | Comment | Committer

Table: Customers

Columns: Id | Name

Table: CustomerChangeSets

Columns: Id | CustomerId (FK) | ChangeSetId (FK)

So now I can have multiple customer changes in one changeset (again very much like TFS). Every changeset in a customer object now results in a new customer object being created (and a new Id assigned due to an identity column).

My problem now is, that I don't know how to represent the mapping from and to my customers. I've got several tables that reference to Customers.Id, but that'd mean I would have a reference to an old version of a customer (the initial one, without any changes).

What's the best way?

Don't create a new customer on each checkin? But how would I get my
required logic then?
Update all the foreign keys to my new Customer.Id? That'd take
quite a long time.

Obviously when you check in a changeset, you know who the customer is (or was) according to what you describe about your process.  Even TFS requires something to be the same, so that it can maintain history.

In any case, in multiple versions of the same object, what is constant?

Is the "Name" constant? (Probably not.) If the Customers table were that simple I do not see what you are tracking other than a name change.  But likely the table is much more complex.

But as long as you know this is the same Customer, you should carry a constant value in the new Customer rows. Something like:

 Table: Customers
 Columns: Id | CustomerNumber| Name

That way "Id" is just a row surrogate key, but CustomerNumber would be assigned once and would be a datum included in the versions of the data you are tracking.

I suppose that the CustomerNumber could be the first Id assigned to this Customer, but I would suggest an independent numbering scheme, perhaps check-digited for a degree of protection from entry errors, should that ever be needed publicly.


MongoDB database schema designing for big data I have never designed a database schema in NoSQL, nor in RDBMS before but for my recent big data project I need to store and locate large amount of data in MongoDB(3.6). I have done it myself, but the querying was hard and speed was slow and I need some advice. Please help me out.

The first collection is 'news'.

It has 'oid', 'aid', 'date', 'section', 'rank', 'title'.

'oid', 'aid' is their unique ID and I need to be able to find 'date', 'section', 'rank', 'title' by querying them.

My current schema design is:

{
  "1234567890123": {    // oid + aid
    "date": "20180126",
    "section": "100",
    "rank": "1",
    "title": "Linus: Intel patch is a piece of shit ",
    "oid": "123",
    "aid": "4567890123"
  },
  "1234567890124": {   // oid + aid
    "date": "20180126",
    "section": "101",
    "rank": "2",
    "title": "Intel sues Torvalds for claiming fact",
    "oid": "123",
    "aid": "4567890124"
  }
}

About 180 of these individual news info will be stored in a day.

The second collection is 'comments'. Obviously, it has comments for items in 'news'.

Each comment object consists of comments specific to items('oid', 'aid') in the 'news' section.

The included data will be 'oid', 'aid', 'date', 'commentCount', 'deletedCommentsCount' and it has an array of comments 'uid', 'id', 'time', 'upvotes', 'downvotes', 'contents'

My current schema design is:

{
  "date": "20180126",   //Same with one in 'news'
  "oid": "123",
  "aid": "4567890123",
  "commentCount": "7",
  "deletedCommentsCount": "0",
  "reply": {
    "12345678901230": {         // oid + aid + commentid
      "id": "john_skeet",
      "time": "2018-01-26 07:32:45",
      "upvotes": "2",
      "downvotes": "0",
      "contents": "So true!"
    }
     "12345678901231": {         // oid + aid + commentid
      "id": "sundar_pichai",
      "time": "2018-01-26 07:35:46",
      "upvotes": "5",
      "downvotes": "0",
      "contents": "@john_skeet Get back to work!"
    }
  }
}

In a day for the 180 news * 1160 comments(approx) so about 200,000 comments will be stored. (65MB / day)

I need to:

query all comments in specific 'oid'
query all comments in specific 'oid' and 'aid'
query all comments for specific word in comment 'contents'
query all comments for specific id in comment 'id'

.... so on

Can you tell me the proper way to design a database containing these data? And if you can, the query statements too. ANY comments or answers will be appreciated. Thanks.

Arthur!

I agree with you the most convenient data model here is to work on two collections, but I can see you have 1.5 collections.

Let me explain. Your comments collection stores almost all your data, except section, rank and title fields from news collection. You are embedding all the comments in an array in the comments collection, and this is inefficient due to the large amount of comments you have.

The schema I recommend you for the comments collection is simply to store one document per comment, referencing it to its news, so:

{
  "news_date": "20180126",
  "news_oid": "123",
  "news_aid": "4567890123",
  "commentCount": "7",
  "deletedCommentsCount": "0",
  "comment_id": "0",
  "id": "john_skeet",
  "time": "2018-01-26 07:32:45",
  "upvotes": "2",
  "downvotes": "0",
  "contents": "So true!"
}

In this way your required queries would be:

db.comments.find({ 'news_oid' : '123' })
db.comments.find({ 'news_oid' : '123', 'news_aid' : '4567890123' })
db.comments.find({ 'comment_id' : '0' })

The indexes you'd create would be:

db.comments.createIndex({ 'news_oid' : 1 }, { background : true })
db.comments.createIndex({ 'news_oid' : 1, 'news_aid' : 1 }, { background : true })
db.comments.createIndex({ 'comment_id' : 1 }, { background : true })

For searching I recommend you to use the full text search MongoDB 's capabilities.

Please, tell me anything you need!


Dedicated SQL instance vs multiple database same server instance Looking for best practice advice for a sprawling network. I inherited a bunch of SQL servers. The practice has been to just stand up a new VM instance any time a database was needed or add a new SQL instance for every database. The administration challenge is huge so was hoping to hear some feedback on approach.
I guess the simple question is, would it be better to run multiple instances per machine or 1 instance with multiple databases assuming licensing is not an issue, security and compatibility can be easily grouped and managed (very small user base, very small lightweight databases)?

assuming licensing is not an issue, security and compatibility can be easily grouped and managed (very small user base, very small lightweight databases)

You already have addressed the concerns.

IMHO, depending on the VM capacity (memory, CPU and disk space), you should consolidate databases to one instance.

This gives you advantage of managing one instance wherein you can have different rules for different databases - e.g. maintenance (index rebuilds, reorgs, stats update, backup schedules) etc depending on their criticality.

When your database size and usage increases, thats the time to move out the heavy used ones out of the instance on a different VM.

Make sure you have proper Disaster recovery inplace e.g. use logshipping/AlwaysON, etc so if the VM crashes or fails, you can failover the databases to secondary server.

Also, refer to my answer for

  Which is better: one database per application, or just one database?.


Either of two parent tables I have a table of users:

CREATE TABLE users
(
  userId INT PRIMARY KEY,
  userName VARCHAR(100),
  /* plus other user columns */
);

And a table of groups:

CREATE TABLE groups
(
  groupId INT PRIMARY KEY,
  groupName VARCHAR(100),
  /* plus other group columns */
);

And I have a table of resources; and I want each resource to be owned (i.e. to have a parent), either owned by a user or owned by a group.

Is the following the right way to model that:

CREATE TABLE resources
(
  resourceId INT PRIMARY KEY,
  userId INT NULL,
  groupId INT NULL,
  /* plus other resource columns */

  FOREIGN KEY(userId) REFERENCES users(userId),
  FOREIGN KEY(groupId) REFERENCES groups(groupId),

  CHECK ((userId IS NULL) ^ (groupId IS NULL))
);

In other words I model it as two nullable columns, each with foreign key constraint, and a check constraint to ensure that exactly one of them is null.

Is this a good way to model that? It looks plausible to me but I don't remember reading it in a book, so I thought I should ask.

Is there a different way to model it? I guess I don't want to have two resources tables (e.g. userResources and groupResources), because they're the same kind of resource and because resources is itself a parent table of further tables (so I don't want to multiply the number of resources tables).

because resources is itself a parent table of further tables

As an alternative, you could model the ownership of resources the same way you would model the membership of users and groups:

I think this model might allow for more flexibility depending on other possible business requirements for ownership of resources.

create table users(
    userId   int not null primary key
  , userName varchar(100)
  /* plus other user columns */
);
create table groups(
    groupId   int not null primary key
  , groupName varchar(100)
  /* plus other group columns */
);

create table groupUsers(
    groupUsersId int identity(1,1) not null primary key
  , groupId int not null
  , userId  int not null
  , constraint fk_groupUsers_Users
      foreign key(userId) references users(userId)
  , constraint fk_groupUsers_Groups
      foreign key(groupId) references groups(groupId)
);

and resources like:

create table resources(
    resourceId int not null primary key
  /* plus other resource columns */
);

create table userResources (
    userResourcesId int identity(1,1) not null primary key
  , userId     int not null
  , resourceId int not null
  , constraint fk_userResources_Users
      foreign key(userId) references users(userId)
  , constraint fk_userResources_Resources
      foreign key(resourceId) references resources(resourceId)
  /* if resource can only link to 1 user */
  , constraint uq_userResources_resourceId
      unique (resourceId)
);

create table groupResources (
    groupResourcesId int identity(1,1) not null primary key
  , groupId    int not null
  , resourceId int not null
  , constraint fk_groupResources_Groups
      foreign key(groupId) references groups(groupId)
  , constraint fk_groupResources_Resources
      foreign key(resourceId) references resources(resourceId)
  /* if resource can only belong to 1 group */
  , constraint uq_groupResources_resourceId
      unique (resourceId)
);

If you want to use check constraints to enforce that a resourceId is only in one of userResources and groupResources you could use something like this:

go
create function udf_resourceId_in_groupResources (@resourceid int)
returns bit as
    begin;
    if exists (select 1 from groupResources g where g.resourceId = @resourceId)
        return 1;
    return 0;
    end;
go
alter table userResources
  add constraint chk_ResourceId_notin_groupResources
    check (dbo.udf_resourceId_in_groupResources(resourceId) = 0)
go
create function udf_resourceId_in_userResources (@resourceid int)
returns bit as
    begin;
    if exists (select 1 from userResources u where u.resourceId = @resourceId)
        return 1;
    return 0;
    end;
go
alter table groupResources
  add constraint chk_ResourceId_noin_userResources
    check (dbo.udf_resourceId_in_userResources(resourceId) = 0);
go


Are SQL Server's temporal tables here to stay? We're considering different approaches to save records' history in an application.
Some of the managers in charge are concerned that as this is a relatively new feature of SQL server, and Microsoft may cease to support it.
Are these concerns justified?
Is there a roadmap to this feature that says what Microsoft intends to do with this feature in the future?

I don't think the concern is legitimate. This feature is introduced in SQL Server 2016 and it's very clearly mentioned that it applies to SQL Server 2016 and later versions. Later version meaning for now it is at least SQL Server 2019 which has been released.
Temporal tables

Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, Azure
SQL Managed Instance  - Azure Synapse Analytics
SQL Server 2016 introduced support for temporal tables (also known as
system-versioned temporal tables) as a database feature that brings
built-in support for providing information about data stored in the
table at any point in time rather than only the data that is correct
at the current moment in time. Temporal is a database feature that was
introduced in ANSI SQL 2011.

If at all, there is chance of removal of any feature or anything they do mention at Microsoft documentation, for instance "Database Mirroring". You can read the details about the same as below:

Note
This feature is in maintenance mode and may be removed in a future
version of Microsoft SQL Server. Avoid using this feature in new
development work, and plan to modify applications that currently use
this feature. Use Always On availability groups instead.

I hope this addresses your concern.
Note: Given temporal tables are part of the ISO/ANSI SQL standard, and it's highly unlikely the feature will be removed from SQL Server.


Benefits of table level locking What are the benefits of table level locking which is used by the MyISAM storage engine? Row level locking has lots of benefits like concurrent updates and reads that do not lock the table.

Edit
Its widely considered that table level locking prevents from deadlocks. But how prevention of deadlocks at the cost of concurrency is worthwhile?

Since mysql schedules queries for execution in this manner:

Write requests (like UPDATEs and DELETEs) take priority over read requests (SELECTs)
Server performs writes FIFO manner (in the order received)

What are the benefits ?

Deadlocks can never occur with MyISAM. The MySQL server can thus manage all contention, explicit (LOCK TABLEs) or implcit (Any DML).

As long as a MyISAM table has no deleted or updated records, concurrent inserts can freely occur with impunity. That would, indeed, include INSERTs on a table that has an explicit read lock.

For any table with gaps, running OPTIMIZE TABLE would remove those gaps and allow concurrent inserts once again.

For more information, please read "MySQL 5.0 Certification Study Guide" pages 408-412 Section 29.2.


Oracle instant client memory footprint In the link mentioned here --> http://www.oracle.com/technetwork/database/features/oci/ic-faq-094177.html it says there is increased virtual memory footprint when running my applications in conjunction with Instant Client.! Why is that so? If that is the case I would rather install the native Oracle client which takes time to install but still end of the day performance matters right? Why should I prefer instant client when there are memory leaks with it?

You link to a FAQ that says

  The Instant Client libraries occupy a virtual address space that is
  equal to the size of the files. However, only frequently used error
  messages from the libraries occupy physical memory. Under most cases,
  the physical memory load is a few kilobytes despite the larger
  reserved virtual address space

This isn't saying that there is a memory leak in the Instant Client.  It is saying that using the Instant Client involves adding a few kb of physical memory and a bit more virtual memory to your application's memory footprint.  That's normally not something that would impact performance.  If you are running in an environment where a few kb of space is an issue (for example, you're building an application that is supposed to run 10's of thousands of copies of itself on a single server), then you might be better off with a full client install.  Of course, the full client will undoubtedly load more than a few kb of shared libraries into memory, they just won't count as part of your application's footprint.


How do I log into the Dedicated Admin Console (DAC) on Linux? sqlcmd is documented to have,

  Login-Related Options

  -A
  Logs in to SQL Server with a Dedicated Administrator Connection (DAC). This kind of connection is used to troubleshoot a server. This will only work with server computers that support DAC. If DAC is not available, sqlcmd generates an error message and then exits. For more information about DAC, see Diagnostic Connection for Database Administrators. The -A option is not supported with the -G option. When connecting to SQL Database using -A, you must be a SQL server administrator. The DAC is not availble for an Azure Active Directory adminstrator.

Clients

There are two Linux clients from Microsoft that can connect to SQL Server's Dedicated Administrator Connection (DAC),

mssql-cli. Versions newer than 0.90 have the -A option to connect to the Dedicated Administrator Connection (DAC). (QUOTED_IDENTIFIERS is on by default).

mssql-cli -S localhost -U sa -A

sqlcmd. This is the older client that ships with the mssql-tools package. It should never be used. If you insist on using it, you'll have to get accustomed to GO. Don't foget to always use -I with sqlcmd to turn on QUOTED_IDENTIFIERS. No idea why Microsoft chose the admin: prefix for sqlcmd. On most other clients, they use -A flag.

sqlcmd -I -S admin:localhost -U sa

Server Settings

If connecting from outside localhost you may have to enable remote DAC first. To do that simply run

sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO


MySQL query show times in milliseconds, not seconds? In psql, with \timing on, query times are displayed in milliseconds + hundredths of milliseconds. In MySQL, they are only displayed in seconds + hundredths of seconds. Is there a way to get MySQL to display them in milliseconds too (I'm trying to compare queries, but the small times just come out as zero in MySQL, which isn't very helpful).

For a test/check session you can enable the Slow Query Log and setting the sysvar long_query_time to 0

  The minimum and default values of long_query_time are 0 and 10,
  respectively.  The value can be specified to a resolution of
  microseconds.  For logging to a file, times are written including the
  microseconds part.  For logging to tables, only integer times are
  written; the microseconds part is ignored


Does the term "page" have the same meaning in DBMS and OS jargons? I am learning the data format of DBMS and found we have a term: page.

Pages are the fetched data in main memory from external storage (eg. a disk, SSD) and several pages would be "cached" firstly to the Low Level Cache, when these pages are acquired by a query.

That reminds me of the same term page used in operating systems and computer architecture. Some formal descriptions about this "paging" from Wiki are:

  Paging is a memory management scheme by which a computer stores and retrieves data from secondary storage for use in main memory. In this scheme, the operating system retrieves data from secondary storage in same-size blocks called pages. Paging is an important part of virtual memory implementations in modern operating systems, using secondary storage to let programs exceed the size of available physical memory.

So it comes to the first question:

Does the term page carry the same meaning in DBMS and in OS?

If yes, can you explain question 2 a little bit?
If not, can you explain why? Do they have different way to be implemented?

A step more:

In the lecture we learn the N-ary Storage Model (NSM or slotted
pages) and PAX (Partition Attributes Across) refer to this paper from Prof. Ailamaki.
Assume page has the same meaning in DBMS and OS, why do we specify the NSM and PAX only in DBMS? Can this page format be portable to OS?
(About the paper will be helpful, that you just go quicklythrough the data layout and the figures.)

The question 2 is the main reason, that I raise question here and start to ask myself, why NSM, PAX pages are special for DBMS. This question is more general and not focused on any particular DBMS in mind.

Does the term page have the same meaning in DBMS and in OS?

Generically, yes. The term PAGE is a generic for the smallest allocate-able unit of space. In SQL Server, for example, pages are a fixed size and have historically been 4k and 8k, depending on version (not counting Large Pages which are virtual memory and not database structures) and in Windows pages can be a multitude of sizes depending on options and underlying hardware.

  Assume page has the same meaning in DBMS and OS, why do we specify the NSM and PAX only in DBMS? Can this page format be portable to OS?

I didn't read the entire paper because it also overloads the same term. Note that the hardware and underlying OS may change what a PAGE size is but doesn't change the generic meaning. Its use is quite overloaded and TBH you can make whatever you want in your own OS.

This is a little too generic and high level to really "answer" per se, and the "answers" are going to be hardware and software dependent and specific. You might want to narrow your focus a bit and ask a much more pointed question as this is extremely wide IMHO.

Edit:

You also mention SSD's, which also have a unit of PAGE which depends on (sarcasm) shockingly enough the storage controller and firmware. Some SSD's use pages of 1MB or more, some smaller.

Normally when we're talking about "Low Level Cache" it's the data or code caches which are generally referred to as lines or segments and not pages.

  Maybe an additional question. Where and how can I find the documentation or implementation of page size or page format from a SQL server? Any particular DBMS or SQL server will make me happy.

SQL Server documents the architecture here. This is fixed and cannot be changed.

Windows page size (not to be confused with a paging file) can be found by calling GetSystemInfo (Windows API) which will populate a _SYSTEM_INFO structure which has a member called dwPageSize that will tell you the current page size for the system - this should all still be 4k at the time of this writing.


Why does my execution plan (sometimes) include a left join? I found a similar question and am aware that in order for a query of the form

SELECT COUNT(1)
FROM foo f
LEFT OUTER JOIN bar b ON f.Value = b.Value AND f.Value = b.Value2

to perform without touching bar, there needs to be a unique index on the two columns in question.

And indeed, this works so far, the tables being defined as:

CREATE TABLE [dbo].[Foo](
    [Value] [varchar](255) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Bar](
    [Value] [varchar](1024) NULL,
    [Value2] [varchar](1024) NULL
)

CREATE UNIQUE CLUSTERED INDEX [IX] ON [dbo].[Bar]
(
    [Value] ASC,
    [Value2] ASC
)

The query plan doesn't touch bar, great.

In my quest of why this doesn't work in my real-life query of some app I write, I reduced the query there to the following test that is simple and also has the query planner fails to leave its hands off the bar table:

WITH numbers AS (
    SELECT 1 AS i
    UNION ALL SELECT i + 1
    FROM numbers
    WHERE i < 10
)
SELECT COUNT(1)
FROM numbers n
LEFT OUTER JOIN bar b ON n.i = b.Value AND n.i = b.Value2
;

This does indeed touch bar. Hmmm.

Any ideas? Why does the query planner think this is any different than the other query?

(My actual problem doesn't use this recursive table expression, it's actually just like the demo case, a simple join on two columns - and I can't fathom why it wouldn't leave the joined table alone in a count scenario.)

I suspect the difference is the implicit conversion, which can get in the way.
As an example, Bar isn't touched if it has a compatible data type:

CREATE TABLE [dbo].[Bar]
(
    [Value] int NULL,
    [Value2] int NULL
);


UTC date in DMV dm_broker_queue_monitors It seems that the DMV dm_broker_queue_monitors returns UTC time rather than local time. Why is it so?

I am wondering how to get local time from this view? I can get current time offset, but that  is not valid for older dates (before daylight saving), so it is not really a correct way to do that.

To get local time, your best bet is to use the CLR, if that is available to you.  Matt Whitfield and Slawomir Brys have sample code you could use to convert which can handle Daylight Savings Time.

Regarding why the DMV is in UTC rather than local time, Service Broker can work across instances, so having service broker-related functionality all in UTC makes sense--that way, there isn't a problem with date synchronization between your east coast server and Tokyo branch.  That'd be my best guess as to why that particular DMV is in UTC whereas a number of others (e.g., dm_exec_sessions) are in local time.


Selecting next month first Friday when current month last day not equal to Friday Hi I have my data as below.

Year    Period  Start_Date  End_Date
2019    6        1-Jun-19    30-Jun-19
2019    7        1-Jul-19    31-Jul-19

Period will be the month number

start date and end date will be the month start and end dates.

I want to fetch the data as below based on above.

Period  Sequence_no Period_end_dt
6        1           07-Jun-19
6        2           14-Jun-19
6        3           21-Jun-19
6        4           28-Jun-19
6        5           05-Jul-19 ( when ever month last day is not equal to Friday i need to display next month first Friday in current month).
7        1           05-Jul-19
7        2           12-Jul-19
7        3           19-Jul-19
7        4           26-Jul-19
7        5           26-Aug-19( when ever month last day is not equal to Friday i need to display next month first Friday in current month).

Oracle fiddle is not available now... :(

In MySQL it can be:

WITH
cte1 AS (      SELECT 0 num
         UNION SELECT 1
         UNION SELECT 2
         UNION SELECT 3
         UNION SELECT 4
         UNION SELECT 5
         UNION SELECT 6),
cte2 AS (SELECT (@firstday:=CAST(CONCAT(@year:=2019, '-', @period:=5, '-1') AS DATE)) + INTERVAL cte1.num * 7 DAY somedate
         FROM cte1)
SELECT somedate - INTERVAL 3 + WEEKDAY(somedate) DAY friday
FROM cte2
HAVING friday BETWEEN @firstday
                  AND @firstday + INTERVAL 1 MONTH + INTERVAL 5 DAY;

@year and @period emulates the values from source table.

@firstday is used to decrease the amount of calculations.

PS. You may face a problem Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'between' in MySQL... if so, replace BETWEEN @firstday with BETWEEN @firstday + INTERVAL 1 DAY - INTERVAL 1 DAY in outer query. fiddle. Or calculate first day of month in outer query instead of @firstday variable usage.


pgAdmin on a fresh OS X Server installation I know for sure postgresql is running (checked with ps ax), but when I install pgAdmin and try to make a server connection to localhost I get this error:

Server doesn't listen
The server doesn't accept connections: the connection library reports
could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (fe80::1) and accepting TCP/IP connections on port 5432?

Any help?

Postgresql, just as most databases, can be accessed locally (on the local machine) and on TCP/IP, from remote machines. There is a common phenomenon that a database is configured by default for local connections.

The local connections can happen

on unix sockets, these are very like TCP ports, but they have a filesystem path instead of a port number. In case of PostgreSQL, the commons settings are 5432 for the TCP port and /var/run/postgresql/.s.PGSQL.5432.
On TCP ports, which is by default 127.0.0.1:5432, and is often by default turned off.  To turn it on, specify port = '5432' in postgresql.conf.

Most clients - php postgresql module, or the command line psql command - connecting the local server on unix sockets by default.

pgAdmin is a special thing, it wants to connect only to TCP ports, even if it runs on a localhost. It doesn't mean anything on your system were bad, this tool was simply so written, that it can use only TCP even for localhost administering.

You had to enable the TCP port listening in postgresql config file pg_hba.conf. The following line will be probably okay:

host all all 127.0.0.0/8 trust

The official documentation for this pg_hba.conf can you find here, and a short tutorial to enable the localhost networking can you find here.

If it don't work or you want better answer, it could help a lot if you extended your question with your actual pg_hba.conf.

Good luck!


Full text search in PostgreSQL with search term including unrelated hyphen and ending I've a problem in my DB (PGSQL 9.6) that I've not been able to resolve and I don't really even understand what options I might have (if any). Simplified I have a DB of multi-language news articles where I've created full text search index (tsvector) on headlines and text. I need to search through the DB to find articles with a certain game name.

Example of an index that would match what I do:

SELECT to_tsvector('simple', 'Yakumo Shionome featured in new Code Vein-trailer');

This results in the following full text search index:

  'code':6 'features':3 'in':4 'new':5 'shionome':2 'trailer':9 'vein':8
  'vein-trailer':7 'yakumo':1

The challenge come when I want to search for "Code Vein" as game title. In this case I would normally use "phraseto_tsquery". A raw test query could be similar to this:

SELECT to_tsvector('simple','Yakumo Shionome featured in new Code Vein-trailer')
  @@ phraseto_tsquery('simple','Code Vein');

This would result in FALSE in this case. I would have to search for "Code Vein-Trailer" to get a TRUE match. So the "hyphen" followed by "some word" in essence is causing me trouble and I don't really get what options I might have as far as creating a way around this (there can be many different ending words, many I also might not know ahead of time).

Normally the fact that my Boolean searches are word boundary based is good as it avoid potential false positives, but this seems to be a challenging drawback. I use "simple" due to multiple languages and that game names and elements I sometime search for included stop words - which I felt ended up being problematic or more prune to issues if not taking this approach. I also do require exact matches and not ranked results.

Any thoughts and help welcome.

The problem is that the token 'vein-trailer' gets emitted and is located before the bare token 'vein' is, so is between 'code' and 'vein'.

One way to fix this is to make a new text search configuration which doesn't emit hyphenated words:

create text search configuration simple2 (copy=simple);
alter text search configuration simple2 drop mapping for asciihword;

Then the following returns true:

select
  to_tsvector('simple2','Yakumo Shionome featured in new Code Vein-trailer')
  @@
  phraseto_tsquery('simple2','Code Vein');

Whether this will fix all your problems, or create new problems, is hard to tell based on your description and also hard to intuit, but it does fix the literal problem you presented.  You probably need to do something with non-ascii hwords as well.


Migration to databases Domain Users do not have access I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be failing. I have a domain account who is the job owner. However, when I try to execute the job under my username I get the following error:

  Executed as user: NT AUTHORITY\SYSTEM. Login failed for
  user.....[SQLSTATE 28000) (Error 18456).

Is this related to Domain Users not having appropriate read and write access to the database?

How would I give all domain users permissions to execute stored procedures.

Users are tied to logins with SIDS. If you moved your DBs, you may need to recreate that mapping.

You can either create another login for DOMAIN\DOMAIN USERS and go through and manually remap, or
run sp_revlogins (link) and sp_change_users_login 'Report' (link)

If sp_change_users_login 'Report' finds orphans, run sp_change_users_login 'Auto_Fix', 'user'.


SQL 2016 Changing Page Verify Impact Recently inherited a new SQL server and after some Blitz (Thanks Brent and team) checks have noticed that some of the DB's are set to Torn_Page or None for the page verify option.  I want to change this to Check_Sum.

Company policy is raise a change for everything which I have done, I was going to change this early morning as I am sure the impact is very low.  I understand it will flush the proc cache but will it have any other impact on a live system, should it be an out of hours change?

Outside of what you've already noted this should be a low impact change (but hey, anything could happen).

In fact, even if you change the value the checksum won't be calculated and written until the pages and written to disk - which could mean it could take days or weeks to get all of the pages actually changed in a natural lifecycle way (as in you didn't just go around rebuilding indexes to have this immediately take effect).

However, company policy is company policy. Even if there is -5% impact, if anything were to happen then the typical finger pointers will immediately throw out any relevant evidence and logic and start the blame game.

TL;DR: Little to no impact, I'd still put in the change control and "after hours" change due to company policy and CYA.


Mirroring Databases in MySQL Is there a way to mirror databases in MySQL?  In SQL Server, you have the ability to link 2 databases in a master to master relationship.  One DB will accept transactions and the transactions will be committed on both DBs at the same time.  Is there a a similar concept in MySQL?

I think the other answer missed the point of the question; which is that in SQL Server mirroring, a transaction that commits to both servers A and B are committed at the same time (in atomicity).  If the transaction fails on one server, it fails on both.  It doesn't seem like there is a similar feature on MySQL


Maintenance plan using external tool to execute query and stored procedures giving off no results My situation is as follows:
I am using a third party tool (VisualCron) to run stored procedures and sql queries on several database servers.
The stored procedures are from http://ola.hallengren.com/ and the sql queries are as follows:

Sql query to check index fragmentation.

IF EXISTS ( SELECT *
    FROM [tempdb].[dbo].[sysobjects]
    WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))
    DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details]

CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](
    [DatabaseName]                  [nvarchar] (1000) NULL,
    [ObjectName]                    [nvarchar] (1000) NULL,
    [Pagecount]                         INT,
    [indexName]                     [nvarchar] (1000) NULL,
    [avg_fragmentation_percent]     float NULL,
) ON [PRIMARY]
DECLARE @dbname varchar(1000)
DECLARE @sqlQuery nvarchar(4000)
DECLARE dbcursor CURSOR for
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')and  state not in('6')
OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sqlQuery = '
    USE [' + @dbname + '];

    IF EXISTS
    (
        SELECT compatibility_level
        FROM sys.databases
        WHERE
            name  = N'''+ @dbname +'''
            AND compatibility_level >= 90
    )
    BEGIN
        INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details]
        (
            DatabaseName
            , ObjectName
            , IndexName
            , avg_fragmentation_percent
            , PageCount
        )
            SELECT db_name() as DatabaseName,
dbtables.[name],
dbindexes.[name],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
    END;'
    EXEC sp_executesql @sqlQuery
FETCH NEXT FROM dbcursor
INTO @dbname
END
CLOSE dbcursor
Deallocate dbcursor
-- Show the index fragmentation result
SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details]
ORDER BY databasename, avg_fragmentation_percent desc

Sql query to check database size (for baseline)

with Temp
as (select database_id, type, size * 8.0 / 1024 / 1024 size from sys.master_files)
select (select sum(CAST(size AS DECIMAL (38, 2))) from Temp where  Temp.database_id = db.database_id) DataFileSizeGB, Name
from sys.databases db
order by datafilesizeGB desc

After these 2 queries save their results in an .xlsx file, I run the following stored procedure using the basic script as found on http://ola.hallengren.com/ .

execute dbo.indexoptimize
@Databases = 'USER_DATABASES, -%WSSContent%, -%SP2013%, -%WSS_CONTENT%',
@fragmentationmedium = 'INDEX_REORGANIZE',
@fragmentationLevel2 = '30',
@fragmentationHigh = 'INDEX_REORGANIZE',
@FragmentationLevel1 = '5',
@fragmentationLow = 'NULL'

execute dbo.indexoptimize
@Databases = 'USER_DATABASES, -%WSSContent%, -%SP2013%, -%WSS_CONTENT%',
@updatestatistics = 'all',
@FragmentationLevel1 = '5',
@FragmentationLevel2 = '30'

When I run the stored procedure in SSMS, followed by the query to check fragmentation I can see changes in the indexes
When I run the stored procedure using my third party tool, I don't see any changes in the indexes
The stored procedure takes the same amount of time using the third party tool, as it does running directly in SSMS
When I check sql profiler, I can see the stored procedure being executed when being run from VisualCron

How do I go about further troubleshooting the stored procedure being run from VisualCron?

By default IndexOptimize is skipping indexes with less than 1000 pages. You can read more about this in the Frequently Asked Questions. Could this explain it?


Unicode storage of \u202b RLE and \u202c PDE in a Unicode-aware database? I'm building a new product for toponyms and in it the Arabic shows kinda like this:

^IArabic^I<202b>ﺰﻤﺑﺎﺑﻮﻳ<202c>^I<202b>ﺞﻫﻭﺮﻳﺓ ﺰﻤﺑﺎﺑﻮﻳ<202c>$

Actually not quite. This is a real problem for my ASCII-spewing terminal, so I'll make an exception and screenshot text.

My question is about those U202B "Right-To-Left Embedding" (RLE), and U202C "Pop Directional Formatting" (PDF). Do those get stored as data? My first assumption was that the characters were rendered and not in the file, but alas they are there..

360    5E03 97E6 5171 548C 56FD 000A 0009 0041 0072 0061 0062 0069 0063 0009 202B 0632    布韦共和国..Arabic..ز
.............................................................................^HERE
389    0645 0628 0627 0628 0648 064A 202C 0009 202B 064F 062C 0647 0648 0631 064A 0629    مبابوي...ُجهورية
.....................................^HERE.....^HERE
422    0020 0632 0645 0628 0627 0628 0648 064A 202C 000A 0009 004E 006F 0074 0065 0073    .زمبابوي...Notes
...............................................^HERE

When storing Arabic in a database, do you typically store \u202b, and \u202c? They seem like they're rendering characters and not technically data? I'm simply wanting to process this text to throw in a database, and wondering if these characters should be present in the database, or stripped before insert.

Background

The screenshot was taken with VIM in a terminal (Kitty) which does not support the Arabic text because all characters get displayed on a grid.
The text comes from text-extraction (using pdftotext)
The pdf was produced by the "United Nations Group of Experts on Geographical Names". You can find the pdf (E/CONF.105/13) freely available here.

Arabic (as well as Hebrew and Syriac) are right-to-left languages. Hence they display in the opposite direction that the bytes are physical stored in. Having the proper display is controlled through non-printable characters that are interpreted only by the font / rendering system. These two characters in particular are used to control this (see original Unicode spec for starters: https://www.unicode.org/charts/PDF/U2000.pdf ), especially in the context of embedding right-to-left text in the same paragraph as left-to-right text (and the other way around).

So, you must keep them stored or else attempted to display this data later will render it backwards from how the language is supposed to appear and will hence be considered data loss. These are among many formatting control characters that are non-printable / zero-width.

The "official" description of how to work with these characters, from the Unicode Consortium, is (taken from "Chapter 23: Special Areas and Format Characters" top of page 868):

  As with other format control characters, bidirectional ordering controls affect the layout of the text in which they are contained but should be ignored for other text processes, such as sorting or searching. However, text processes that modify text content must maintain these characters correctly, because matching pairs of bidirectional ordering controls must be coordinated, so as not to disrupt the layout and interpretation of bidirectional text. Each instance of a lre, rle, lro, or rlo is normally paired with a corresponding pdf. Likewise, each instance of an lri, rli, or fsi is normally paired with a corresponding pdi.

Regarding the importance of keeping (not discarding) these hidden formatting code points, the "Unicode® Standard Annex #9: UNICODE BIDIRECTIONAL ALGORITHM", in section "2.7 Markup and Formatting Characters" states (emphasis mine):

  The explicit formatting characters introduce state into the plain text, which must be maintained when editing or displaying the text. Processes that are modifying the text without being aware of this state may inadvertently affect the rendering of large portions of the text, for example by removing a PDF.

and:

  Whenever plain text is produced from a document containing markup (ed: HTML and/or CSS), the equivalent formatting characters should be introduced, so that the correct ordering is not lost.

Further explanation is provided in the (excellent) "Understanding Bidirectional (BIDI) Text in Unicode" document by Cal Henderson (taken from the O.P.'s answer) states:

  ... we could disallow these explicit characters (U+202A - U+202E) which is pretty easy. This does mean that anybody who wants to use them to include Neutrals at the edges of their Arabic usernames will be out of luck - and that sucks more when it's a comment they're posting, where the period jumps to the 'beginning' of the text.

  If we want to allow use of these characters, the solution is fairly simple (if hard to implement): we need to make sure that every opening marker has a paired closing marker (PDF) so that the state stack coming out of the string is at the same state as when we went in. We also need to be careful that we don't allow any PDFs to be used without accompanying push markers, else we can't use any ourselves outside of the block.

So, even if the text of a particular cell is supposed to be entirely a right-to-left language, removing these markers could alter the placement of neutral characters (such as punctuation). For example (using SQL Server):

SELECT NCHAR(0x0671) + NCHAR(0x0679) +  N'!';
-- ٱٹ!

SELECT NCHAR(0x202B) + NCHAR(0x0671) + NCHAR(0x0679) + N'!' + NCHAR(0x202C);
-- ٱٹ!

Planning to add them back in later, or having a client app add them back in, won't work because there is no inherent means of knowing that they were even being used, and if so, where they were placed.

The safest approach is to keep these characters

For example, you are attempting to include some of this text at the top of the question:

^IArabic^I<202b>ﺰﻤﺑﺎﺑﻮﻳ<202c>^I<202b>ﺞﻫﻭﺮﻳﺓ ﺰﻤﺑﺎﺑﻮﻳ<202c>$

but clearly that's not displaying in the correct order. The bytes, however, are in the correct order:

Looking at just the first <202b>...<202c> section (again, using SQL Server, hence it's Little Endian):

SELECT CONVERT(VARBINARY(MAX), N'<202b>ﺰﻤﺑﺎﺑﻮﻳ<202c>');

the bytes are:

3C00 3200 3000 3200 6200 3E00 B0FE E4FE 91FE 8EFE 91FE EEFE F3FE 3C00 3200 3000 3200 6300 3E00
<    2    0    2    b    >    .    .    .    .    .    .    .    <    2    0    2    c    >

As you can see, there are no additional formatting characters. Because the Arabic characters are strong right-to-left, the characters that follow – <202 – which are neutral (<) and weak (202), continue to display heading to the left (even turning the < into >). And to be clear, the 202 itself displays left-to-right, which would be clearer if the number wasn't a palindrome. If the number was 203, then it would still show as 203 and not 302. But the c is strong left-to-right so it (and the characters that follow) display as expected.

How to fix? Just add the implicit left-to-right markers just after the Arabic to indicate that the right-to-left directionality should end at that point. If we add Code Point U+200E after the last Arabic character (and just before the <) in each of those two segments, we get the following:

^IArabic^I<202b>ﺰﻤﺑﺎﺑﻮﻳ<202c>^I<202b>ﺞﻫﻭﺮﻳﺓ ﺰﻤﺑﺎﺑﻮﻳ<202c>$

Now, if StackOverflow were to strip out the formatting, then it would revert to the incorrect display, and there is no indication as to the intention of what is desired here that can be discovered programmatically.

If you want to strip out the formatting and add it back in later, are you 100% certain as to the intention of why those characters are there? They aren't always used, so how do you know why they are used when they are present? Don't think there will be any non-Arabic characters? Ok, so how do you classify <202>? I left out the "b" and "c" because there could be punctuation and numbers without any Latin characters and still be "fully Arabic".

This is why I said that keeping them in was the "safest" route to go. Not the only route. But if you don't control the input values, then I don't see how you can guarantee never accidentally altering the meaning of the data.


Oracle XE Backup Restore Problem I have a Oracle XE 11g on Windows 7. I try to backup my database and restore it.

First, i backup my database with running backup.bat script which is in C:\oraclexe\app\oracle\product\11.2.0\server\bin which ended with no error.

Then, for testing i drop a table with this command drop table karzarar; and then committed.

I restore my backup by clicking Restore Database from Startup menu. It restored with no error.

But when i connected to the database and try to select the table that i dropped it gives me error.

SQL> select * from karzarar;
select * from karzarar
              *
ERROR at line 1:
ORA-00942: table or view does not exist

I fallowed the instructions from there to backup the database.

Did i miss anything?

After datafiles are restored, oracle performs complete recovery by applying all changes from archived and online redolog files. Those changes also covers "drop statement" you did run after the backup. Hence, the recovery after the restore applies "drop table" statement during "restore database"

In order to recover drop table stament, you need to choose/perform incomplete (time/SCN based) recovery. That will bring database to a time before the drop statement is run.


Slow execution with MariaDB Not sure if this can be done without UNION. I think it is the UNION which makes it slow. The two parts are quite fast on their own, but combined with the UNION this code takes > 5 seconds to complete. Can it be optimized?

SELECT rl.Updated, c.CultureName,
"CollectionName"
as CollectionName, k.KeyName , rl.Value , s.StatusName ,
rl.Comment , rl.Comment as ActionInfo, a.ActionName, u.UserName
FROM ResourceLog rl
INNER JOIN `Status` s ON rl.StatusId = s.StatusId
INNER JOIN Culture c ON rl.CultureId = c.CultureId
INNER JOIN `Key` k ON rl.KeyId = k.KeyId
LEFT JOIN `Action` a ON rl.ActionId = a.ActionId
LEFT JOIN `User` u ON rl.UserId = u.UserId
UNION SELECT imp.Created as Updated, "Imported" as CultureName,
"None" as CollectionName, "Imported" as KeyName,
"Imported" as Value, "Imported" as StatusName,
"Imported" as Comment, "ActionInfo" as ActionInfo,
"Imported" as ActionName, UserId
FROM Import imp
ORDER BY Updated DESC, KeyName, CultureName limit 50

I suspect the combined query is not doing what you think it is doing.

Add parentheses to make it clear whether the second ORDER BY belongs to the second SELECT or to the UNION.  Ditto for the LIMIT.

( SELECT ...
)
UNION
( SELECT ...
     -- Do the ORDER BY and LIMIT belong here?
)
-- Or here (for the UNION)?

For further discussion, Please provide

EXPLAIN ...
SHOW CREATE TABLE ...
how big are the tables


How to see the original text of the the function that has been wrapped by "DBMS_DDL.WRAP" As I'm new to Oracle PL-SQL,I'm trying to be familiar with DBMS_DDL package and I used it to hide my source code,below you can see the example I'm working on:
Declare
  v_string varchar2(32676);
begin

  v_string := 'create or replace function get_os_user
                 return varchar2 ' || ' is  ' || ' begin ' ||
              ' return (sys_context(''userenv'',''os_user''));' || ' end; ';

  execute immediate sys.dbms_ddl.wrap(v_string);
end;

The function get_os_user is created in the function section and In the view mode , I see the non readable hex format of the original function.Now my question is how I can see the original format of the function? The way the book from which I read about the package just said this:
The original text can be viewed in DBA/ALL/USER_SOURCE

Where is DBA/ALL/USER_SOURCE? Are there other ways to see the original format of wrapped functions and procedures?

You can't get the original source back.  DBA_SOURCE, ALL_SOURCE, and USER_SOURCE are data dictionary views.  So you can do something like
select line, text
  from user_source
 where name = 'GET_OS_USER'
   and type = 'FUNCTION';

That will show you the wrapped text of the function.  But the point of wrapping your source code is to prevent users that have access to the database from recovering your source code.  It would defeat the purpose if you could just view the unwrapped source in the data dictionary.
If you are going to wrap the source of your procedures, you would need to have the unwrapped source outside of the database.  Presumably in a source control system.
A couple of notes-

Wrapping is a pretty uncommon thing to do.  It really only makes sense if you're delivering an application that clients will install and administer themselves but you don't trust your clients not to steal your intellectual property.  These days, most companies in this sort of position would just offer a software-as-a-service product rather than having customers administer their own installation.
There are presentations and some programs that you can find on the internet that walk through how to unwrap source.  So it's not strictly true that you can't get the original source back.  Newer versions of Oracle implement wrapping in a more secure way than older versions but no version is unbreakable.
If you do wrap your code, the actual SQL statements that it executes will be visible in the clear in various data dictionary tables like v$sql, in AWR reports, etc. If your clients are determined to reverse engineer what your application is doing, they likely can.  Wrapping just increases the degree of difficulty.


Local SSMS client is not connecting one particular server My SSMS is not connecting to one particular server say X except X it is connecting to all the other servers. Could not find the root cause for this problem does anyone have idea here?

Guy sitting next to me can connect to X server. Only for me its not connecting.

Note: Before one hour I can connect to same X server.

Details of my ssms client

  Microsoft SQL Server 2012 - 11.0.5058.0 (X64)  May 14 2014 18:34:29
  Copyright (c) Microsoft Corporation Developer Edition (64-bit) on
  Windows NT 6.2  (Build 9200: )

Error am getting

Any help is highly appreciated.

I went to the command prompt and typed ping X. It's coming back with Request Timed Out 4 times. Server is not down; my neighbour can connect to same X server which is not connecting to me.

Due to your comments on the main answer, I would write my suggestions below. You described that your neighbor can still connect to the machine but you not. This doesn't seem like a normal behavior of an SQL Server. This just brings me to another conclusion. It seems to be more likely a network error. In fact, you also described that you could connect to the server too, just before an hour.

I would suggest the following solution approaches:

Check if your neighbor still can connect to the server.
Check if you can connect to all other systems your normally used to connect with.
Logon on the PC of your neighbor, try to connect to Server X. If this will work, it's nothing with your permissions or with your user.
Due to the fact, that it seems to be network related, I would
suggest to check the network in your company. Check at your
neighbor's PC which IP is assigned to Server X. You can do this by
using the cmd and typing nslookup serverx. Compare it with the
IP which was provided after you tried to ping serverx. Do they
match? If not, you probably have a DNS error. Which means that
Server X has another IP, but your PC hasn't recognized it in his
ARP. You may want to flush your your dns-cache. You can do this using ipconfig /flushdns on the command line.
If this 4 doesn't apply or help, you can try an tracert to server x from your neighbor using tracert serverx. Check which hops occur. Try the same on your machine. Do they match or is there any difference? If you found a timeout on a special ip, you can try to ping the corresponding ip from your pc and from the pc of your neighbor. If you can't reach the ip, but your neighbor can - it looks like a port error on a specific switch between you and your neighbor. By the way, the whole story at all seems to be a defect port in my opinion. Just due to the fact that this just occurs from one minute to the next is kind of specific for an port error. Even the fact that just you cannot connect to the server. You should try this!
If all of the above doesn't help, you still check the firewalls between you and server x. Some companies use smart switches or IDS application (Intrusion detection system) which will detect misbehavior and close specific ports. If your company use such a system and it's not proper configured it may cause such a port close. I've had a similar solution in my old company in the first days of our IDS. You can also try to connect to server x from your neighbors PC using RDP and try to ping your PC from the side of the Server.

If nothing else helps, you need to contact your system administrator (if your not the one :-D) and try to figure out which cable or port is broken.

I really don't think that this is an SQL Server specific error. I would guess that this is some hardware or maybe software error in your network/pc.


Extend a single week's win/loss ratio calculation to a whole season I'm quite inexperienced at SQL, and I'm trying to improve a query in a small project.

The database schema looks somewhat like this. (I've reduced to relevant information, if there seems to be something missing please point it out):

It's a database of a fantasy sports league, and we want to calculate an 'all-play record'.

My understanding is that the 'all-play record' is generated by taking every team in a given league's scores for a given week and assuming they all play each other.

I've written a query that does this for a given week:

WITH weekly_points_table (team_id, max_points)
AS
(
  SELECT teams.id, SUM(player_scores.points) as max_points
  FROM player_scores
  INNER JOIN weeks ON weeks.id = player_scores.week_id
  INNER JOIN teams ON teams.id = player_scores.team_id
  WHERE (team_id IN (?) AND weeks.number = ? and weeks.year = ?)
  GROUP BY teams.id
)
SELECT *
FROM weekly_points_table
WHERE max_points < (SELECT max_points
  FROM weekly_points_table
  WHERE team_id = ?
)

The list of team_ids, the given week number/year, and the final team_id are passed in.

Wins are calculated with the above query, losses are calculated by simply flipping the sign, and ties are just (count of all teams - 1) - (wins + losses) for a given week.

But now I'm at a loss at how to extend this to a given season.

A season is a collection of weeks where the year is identical, and week numbers go from 1 to 16.

Right now we're literally just taking the single-week query and running it in a for-loop. (Before that, we were using nested for loops to calculate the wins/losses/ties, and you can imagine how slow that can be.)

Pretty much, how do I take the calculated wins/losses for a given week and add them up for all 16 weeks?

Assuming a standard many-to-many implementation ...

SELECT week_id
     , count(*) FILTER (WHERE week_points > team_points) AS losses
     , count(*) FILTER (WHERE week_points < team_points) AS wins
     , count(*) FILTER (WHERE week_points = team_points) AS draws
--     , count(*) AS total -- redundant check
FROM (
   SELECT week_id, team_id, sum(points) AS week_points
        , first_value(sum(points)) OVER (PARTITION BY week_id
                                         ORDER BY team_id <> 7) AS team_points
   FROM  (SELECT id AS week_id FROM weeks WHERE year = ?) w  -- your year
   CROSS  JOIN unnest(?::int[]) t(team_id)  -- your teams, see below!
   JOIN   player_scores p USING (week_id, team_id)
   WHERE  EXISTS (  -- only weeks where the team actually played
      SELECT 1 FROM player_scores
      WHERE week_id = p.week_id
      AND   team_id = ?  -- your team_id
      )
   GROUP  BY 1, 2
   ) sub
WHERE  team_id <> ?  -- your team_id again
GROUP  BY 1;

Returns something like:

week_id | losses | wins | draws
--------+--------+------+------
1       | 3      | 0    | 0
2       | 5      | 7    | 0
3       | 2      | 2    | 1

The key feature is the window function first_value() to get the weekly score for the team in the same query. Aggregate functions can be nested in window functions. Consider the sequence of events:

Best way to get result count before LIMIT was applied in PHP/PostgreSQL

The aggregate FILTER clause requires Postgres 9.4+. There are other ways. Details in this closely related answer on SO:

How can I simplify this game statistics query?

Assuming you provide your list of teams as array literal in the form '{1,2,3}'. There are other ways ...


unable to change secure_file_priv in my.cnf I have problems loading in a csv file, secure_file_priv is set to NULL, and that prevents me from reading in the csv file. I am trying to change secure_file_priv in my my.cnf file, but my changes don't go through.
mysql says:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

so I made a my.cnf file in /etc, but the changes don't go through. (I restarted mysql server in mac preferences window)

this is what my file looks like:

[mysqld]
wait_timeout=28800
interactive_timeout = 28800
secure_file_priv=""
max_allowed_packet=64M

i edited my my.cnf file, restarted mysql server but when i use show variables like secure_file_priv it still says NULL, how can i alter the secure_file_priv option?

I do not know in Linux but in Windows 10/MySQL80 (my.ini instead of my.cnf) the option is spelled secure-file-priv  and not secure_file_priv. Do not forget to restart the service once the change is made.

I sadly lost one evening before finding my error. This is especially confusing as the SQL query to check the variable value is SELECT @@secure_file_priv.


Data Loss when forced failed over to secondary replica on Sql Server 2014 Setup:

2 Availability Groups
2 Replicas on Synchronous Commits.
1 Replica on Asynchronous Commit.
Automatic Failover configured for the two replicas on Synchronous Commits.
Manual Failover configured for the single replica on Asynchronous Commit.

  Problem:

ESX Server for the primary replica went down.
Automatic failover didn't happen.
Manual forced failover was performed using the command:

ALTER AVAILABILITY GROUP AccountsAG FORCE_FAILOVER_ALLOW_DATA_LOSS;
This resulted in a lot of data loss.

  Question:

What am I missing here ?
Why did the data loss happen? My understanding is that since two replicas was on synchronous commit, there would be no data loss when either one of them goes down.
Is it the data in the Log transactions that was lost. Would periodic log shrink prevented this data loss ?

What am I missing here

You should first read How It Works: Always On–When Is My Secondary Failover Ready?. This would tell you about scenarios when your secondary is not failover ready and what could lead to "no automatic" failover

  Why did the data loss happen? My understanding is that since two replicas was on synchronous commit, there would be no data loss when either one of them goes down

The data loss happened because you forced failover over to replica which was not synchronized. Look at the word "FORCE_FAILOVER_ALLOW_DATA_LOSS" it says itself that if you use me you "might" face data loss. What this command does is this beings online database discarding any transaction log blocks which originated on primary but somehow did not made on secondary and since secondary has no information about it it cannot replay it and hence you might face data loss when database comes online.

  Is it the data in the Log transactions that was lost. Would periodic log shrink prevented this data loss ?

Let us call it transaction log block not data. This log block was on primary but due to unexpected shutdown it was not delivered to secondary and secondary was not synchronized. If you are able to bring primary online after some time without doing any forced failover you might have been able to save data loss but since you forced failover it is not possible. May be later if you bring primary replica online and the information is there in transaction log you can get it in primary replica. Shrinking has NO business here at all.


Methodology & Architecture to Create ETL Process What book or webpage can I read the architecture and methodology for creating ETL process?

In other words, I'm looking for "how to do it" to create a ETL process when you have many source system to be involved and you also have a data warehouse.

I would recommend reading The Data Warehouse Toolkit by Ralph Kimball. Goes through the theory,  with some good examples, and will leave you with a solid understanding of the theory. Then you can look at the tools themselves.

If you're looking for something to build your ETL process, Pentaho have something called the "Star Modeller" which lets you model your dwh, and it then builds the ETL/cubes etc.  But it's pretty new and far from complete - worth keeping an eye on though.


Combining data from multiple rows I am relatively new when working with queries so let me apologize in advance.  This is SQL Server 2012 that I will be working with.  This data has been brought into a staging table through a nightly import from Excel.  I need to move this data from this table into our main hardware table and make sure that I insert any new data while checking to make sure it updates any of the older data.  I will give as much information as I can, please bear with me.

The field names are:

|Ticket_Num | Name | Worker | date1 | Serial | Part | Description1 | Type1 | Eqmt_Type |

I need to bring them into a table that has this:

   |Event_NUM|Name|Install_Date|Pull_Date|Install_Tech|Pull_Tech| PMP1_Desc|PMP1_Part|PMP1_Serial|PMP2_Desc|PMP2_Part|PMP2_Serial|PMP3_Desc|PMP3_Part|PMP3_Serial|Motor1_Desc|Motor1_Serial|Motor1_Part|Motor2_Desc| Motor2_Serial| Cab1_Disc|Cab1_Part|Cab1_Serial|Cab2_Desc|Cab2_Part|Cab2_Serial|Dis1_Desc|Dis1_Part|Dis1_Serial|Dis2_Desc|Dis2_Part|Disc2_Serial|

Here is SQL Fiddle with an example of the data being pulled in from Excel:
SQL Fiddle for data to be inserted

Here is SQL Fiddle with an example of what I would like: SQL Fiddle example of what I would like

Is it possible to redesign your schema?  It feels like you are making life harder for yourself by basically trying to pivot the data you're importing from the excel spreadsheets.

CREATE TABLE dbo.Hardware -- hw?
(
    [Event_Num] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(100) NOT NULL,
    [Install_Date] DATE NULL,  -- Install after pulling?
    [Pull_Date] DATE NOT NULL,
    [Install_Tech] INT NULL FOREIGN KEY REFERENCES dbo.techs(UserId), -- separate out your techs to another table
    [Pull_Tech] DATE NOT NULL FOREIGN KEY REFERENCES dbo.techs(UserId), -- separate out your techs to another table
    <Add other common fields here>
)

CREATE TABLE dbo.ComponentType -- Or some better name for PMP, Motor, dis, etc. table
(
    [ComponentTypeId] INT NOT NULL PRIMARY KEY,
    [Description] NVARCHAR(50) NOT NULL -- This is where you would put PMP, Motor, etc.
)

CREATE TABLE dbo.WorkPerformed
(
    [WorkPerformedId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [ComponentTypeId] INT NOT NULL FOREIGN KEY REFERENCES dbo.ComponentType(ComponentTypeId), -- FK to type lets us reuse this structure for all components and prevents the need to pivot the excel data
    [Event_Num] INT NOT NULL FOREIGN KEY REFERENCES dbo.Hardware(Event_Num), -- Allows you to associate the work performed with the hw
    [Description] NVARCHAR(400) NULL,
    [SerialNumber] NVARCHAR(50) NOT NULL,
    [Part] NVARCHAR(50) NOT NULL,
    <Other valuable info not currently tracking, ie pulled/installed, tech doing work, etc>
)

Obviously that is just a rough sample of the way the schema could be setup.  I'm sure you can see that mapping the data will be much easier now, and more flexable for future updates.  In order to maintain backwards compatibility, if needed, you could just create a view with the current table's name and select the data from the new tables.

If going down this path is not possible/acceptable I would look at pulling your data out of the temp table and inserting it into your current table with a PIVOT.  See this TechNet article for basic information about pivoting (the syntax from 2008R2 will work in 2012).


Can I create a second filestream container on an existing SQL Server 2008 database without going offline? Our production DB is using the FILESTREAM option for our sharepoint environment. The volume where the files are residing is getting full and I am planning to add another volume to ease space issues. Is the solution as simple as creating another filestream container and adding this container on the existing filestream filegroup? Would SQLServer take care of balancing among the two volumes?

Unfortunately no, not in SQL Server 2008.

According to FILESTREAM Overview - multiple containers can be added to a single FILESTREAM Filegroup but only as of SQL 2012.


open/execute huge sql file I have SQL file using PostgreSQL with size about 7GB. How can I open/execute the SQL file? I have researched and tried using Navicat Premium, Razor SQL, HeidiSQL, Workbench. In all I get out of memory. I know for sure it is a PostgreSQL database dump.

PostgreSQL database dumps are normally produced by the command line tool pg_dump, and expected to be used via either pg_restore or simply psql command line tools.

However, you can both backup and restore from PgAdminIII. The backup option is available via right-clicking either the server or any database or schema. The Restore option is available via right-clicking a database or schema.

Here's a step-by-step example of how dump or restore a database using PgAdmin.


Retrieving a fixed range of rows from Postgresql I have a query:

select qid,ansid,ans from table1 where askerid='something'

This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the next 50 rows should be retrieved. I have thought of a query like this :

select qid,ansid,ans
from table1
where askerid='something'
limit 100
minus
select qid,ansid,ans
from table1
where askerid='something'
limit 50

I want to know if this is a good query performance-wise and alternate queries that perform better than the above one.

The proper way to do this is using LIMIT and OFFSET.

Note that for this to work properly, your query will need an ORDER BY clause so that the rows are fetched in the same order each time.

Your first query would be:

SELECT qid,ansid,ans
FROM table1
WHERE askerid='something'
LIMIT 50 OFFSET 0

The second query would have an OFFSET of 50, as you want to skip the first 50 rows:

SELECT qid,ansid,ans
FROM table1
WHERE askerid='something'
LIMIT 50 OFFSET 50

The ORDER BY clause wants adding between the WHERE and LIMIT clauses.

The Postgres documentation on this is here, and mentions that this may not be performant for large OFFSET values. It will be ok for small-ish datasets if the ORDER BY column is indexed.


Recovering from the loss of datafile in NOARCHIVELOG mode? How do you recover from the loss of datafile if the database is running in NOARCHIVELOG mode?

You cannot recover a data file or a database in NOARCHIVELOG mode, since there's no redo entries that you could apply to your previous backups to roll the data files forward up to the point of failure.

If the database is in ARCHIVELOG mode, the ARCn process constantly copies online redo log files to a persistent area when they are full, so that you can later apply the redo entries to the previous backups. Copied online redo log files are called archive log files.

In NOARCHIVELOG mode, LGWR writes redo entries to redo log files while DBWR writes data blocks to data files, however redo log files full of redo entries are not copied, but are overwritten in a round-robin fashion, and thus no redo data is available to apply to the previous backups.

Therefore, if your database is in NOARCHIVELOG mode, you cannot recover your database up to the point of failure, you can only restore it to a previous state which is the time of one of your backups.

For more info, read the section "Choosing Between NOARCHIVELOG and ARCHIVELOG Mode" in Database Administrator's Guide.