How can I create a Foreign Key to a Compound Primary Key with a single column? I am working with a 'lookup' style table that is already in place, which has a compound primary key (not my design) in Microsoft SQL Server 2012. I want to add a foreign key to it in another table, but with only a single column, and I am seeking a workaround, no matter how unreasonable (CLR based custom library or dll hijacking, even), just to see how much would need to be done.

Here's an example:

CREATE TABLE dbo.Lookup (
    FieldName VARCHAR(100) NOT NULL,
    Value VARCHAR(100) NOT NULL,
    PRIMARY KEY (FieldName, Value)
)

INSERT INTO Lookup (FieldName, Value)
SELECT 'This', 'A' UNION ALL
SELECT 'This', 'B' UNION ALL
SELECT 'That', 'A'

CREATE TABLE dbo.OtherTable (
    ID INT PRIMARY KEY IDENTITY(1,1),
    Blah VARCHAR(100)
)

ALTER TABLE dbo.OtherTable WITH CHECK ADD CONSTRAINT [FK__OtherTable__Blah]
FOREIGN KEY(Blah) REFERENCES Lookup (Value)

--Cannot create this:
--"There are no primary or candidate keys in the referenced table 'Lookup'
--that match the referencing column list in the foreign key 'FK__OtherTable__Blah'."

This is somewhat expected. I'm acutely aware that the SQL Standard for working with this is that I would need to include another column in dbo.OtherTable that stores FieldName for the foreign key to be created.

However, what if I knew ahead of time that dbo.OtherTable is only ever using FieldName of 'This'? Why can't I then add a foreign key that uses 'This' for FieldName for that check?

I know that I could just add another column, set all their values to 'This', and move on. I'm not interesting in going this route. What I'd like to know is how possible it is to wrangle this into submission. Would it even be possible to write my own foreign-key-like integrity check, should it not be possible to do any other way? Or hijack the built-in Foreign Key implementation?

Basically, all I'm looking for is something like:

ALTER TABLE dbo.OtherTable WITH CHECK ADD CONSTRAINT [FK__OtherTable__Blah]
FOREIGN KEY('This', Blah) REFERENCES Lookup (FieldName, Value)

Is this feature available in other SQL Implementations (MySQL, Postgres, Oracle, etc.)?

Yes, you can do this with a COMPUTED column, It's a workaround really as the column has to be PERSISTED to be used for the foreign key constraint so it consumes storage space:

CREATE TABLE dbo.OtherTable (
    ID INT PRIMARY KEY IDENTITY(1,1),
    Blah VARCHAR(100),
    FieldName AS CAST('This' AS VARCHAR(100)) PERSISTED NOT NULL
) ;

ALTER TABLE dbo.OtherTable WITH CHECK
  ADD CONSTRAINT [FK__OtherTable__Blah]
    FOREIGN KEY (FieldName, Blah)
    REFERENCES Lookup (FieldName, Value) ;

Tested at SQL-Fiddle.

The table can then be used as if the FieldName does not exist. You could even define a view that does not include this column and make your applications use that view: SQL-Fiddle-2

The syntax you hoped for, although looking pretty, has not been implemented in any DBMS I know of:

ALTER TABLE dbo.OtherTable WITH CHECK
  ADD CONSTRAINT [FK__OtherTable__Blah]
    FOREIGN KEY('This', Blah)
    REFERENCES Lookup (FieldName, Value) ;

Note however that the workaround with the computed columns would be 100% equivalent to this syntax, if only SQL-Server removed in the future the restriction of using only persisted columns. You could add a Connect item with your request.

For the question whether there is any other DBMS that allows this, no, there isn't. Check also a related question:

Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?

If you really don't want to  use this method (because of the additional storage requirements, alternative paths would be to enforce the constraint via procedures or triggers (Note that you would have to write procedures or triggers for both tables involved.)


how to make a table unlocked within transaction block I am using SQL Server 2008.  I have several stored procedures that process different reports.  Each stored procedure makes use of Begin Transaction' and 'Commit Transaction, using the default isolation level.

Everything works fine since the target tables use by these stored procedures are different for each stored procedure, except that all stored procedures call another common stored procedure to write log messages into the SPLog table.   I need the SPLog table to be available to all stored procedures, and not to be locked by any stored procedure. The table is designed so that each message has a column indicating which stored procedure it belongs to.

The problem is the SPLog table is locked once a stored procedure is ran, causing other stored procedures to wait until the first stored procedure commits.  How can I allow INSERTs into this SPLog table by concurrent stored procedures from within a TRANSACTION block?

Below is the pseudo code of these stored procedures:

Stored Procedure SP

Begin Transaction

 business logic....perform operation A on dedicated tables
    call common SP to insert a message row to SPLOG table (for common use)
 business logic....perform operation B on dedicated tables
    call common SP to insert a message row to SPLOG table (for common use)
 ....
Commit Trasaction

All stored procedures have similar pseudo code. Appending rows to SPLOG table should be allowed at any time.

How do I achieve that?

You'd have to do it outside of the transaction

For example: Spool the data in a table variable which don't participate in transactions and write once at the end (after commit). Or use Service Broker to decouple log writes.

Any logging otherwise is subject to the transaction semantics eg locks, can be rolled back etc


Restore multiple databases quickly - practicing disaster recovery I am currently working administering a SQL Server 2014 Standard Edition instance for Sharepoint 2013. We started 5 month ago with ~25 DB's and now we have 60 in total. So the databases in use are growing. In case of a recovery I would have to restore all my collected backups quickly.

Until now I would copy the name and put it into a single script. But in a real disaster situation I would love to be prepared better. How do you manage this?
Do you have a tool or anything which registers the newly created DB's?

There are couple of things that you need to make clear

  But in a real disaster situation I would love to be prepared better. How do you manage this?

Dont wait for the disaster to happen. Taking backups is not just enough. You should be able to confirm that the backups you take are able to restore as well. A backup is good ONLY if you are able to restore it (and DBCC CHECKDB returns no corruption errors).

I tend to take my backups with CHECKSUM. To quote Paul Randal

  using the WITH CHECKSUM option and having the backup complete successfully tells you that those pages with page checksums had not been corrupted by the I/O subsystem at the time the backup was taken.

  Do you have a tool or anything which registers the newly created DB's?

Technically, you dont need a tool. All the new databases are in master.sys.databases and msdb - backup tables record the backup history. The script from Paul Brewer - restore Gene takes care of generating the restore commands for you. (I have used it and its an excellent tool/script !)

If you really need to log what databases are created, then you can use server level trigger CREATE_DATABASE and log it in a DBA database.


Database schema for entities with two possible owner/parent types? I'm using PostgreSQL with Sequelize as my ORM.

I have one type, User. The second type is Group, which can have any number of users associated with it through a GroupMemberships table. Users can also own any number of Groups.

My third type, Playlist, can belong to either a User OR a group. What would be the best way to design a schema for this type so that it can have either one type of owner or the either?

My first pass I created both associations, but only populated one at a time. This could possibly work, but seems hacky and makes queries difficult.

Additional information

Here are my responses to the clarification requests posted by MDCCL via comments:

  (1) If a Playlist is owned by a given Group, one can say that this Playlist is related to one-to-many Users, as long as they are Members of such Group, right?

I believe this is technically true, but this one-to-many association does not exist explicitly.

  (2) So, is it possible for a specific Playlist to be owned by one-to-many Groups at the same time?

No, it should not be possible for a Playlist to be owned by one-to-many Groups.

  (3) Is it possible for a particular Playlist to be owned by one-to-many Groups and, at the same time, by one-to-many Users who are not Members of such Group?

No, because as in (2) a one-to-many from Playlist to Group should not exist. Additionally, if a Playlist is owned by a Group it is not owned by a User, and vice versa. Only one owner at a time.

  (4) What are the properties used to uniquely identify a Group, a User and a Playlist?

They each have a surrogate primary key (id), as well as a natural key (though not primary). These are slug for Group and Playlist, and username for User.

  (5) Could a particular Playlist suffer an Owner change?

Although I don't plan on this being a feature (at least initially), I suppose this could hypothetically occur.

  (6) What’s the meaning of the Group.Slug and Playlist.Slug attributes? Are their values stable enough to be defined as primary keys or do they change very often? The values of these two properties, along with User.Username must be unique, correct?

These slugs are unique, lowercase, hyphenated versions of their respective entity's title. For example, a group with the title 'Test Group' would have the slug 'test-group'. Duplicates are appended with incremental integers. This would change any time their title changes. I believe that means they wouldn't make great primary keys? Yes, slugs and usernames are unique in their respective tables.

If I understand your specifications properly, your scenario involves —among other significant aspects— a supertype-subtype structure.
I will exemplify below how to (1) model it at the conceptual level of abstraction and subsequently (2) represent it in a logical-level DDL design.
Business rules
The following conceptual formulations are among the most important rules in your business context:

A Playlist is owned by either exactly one Group or exactly one User at a specific point in time
A Playlist may be owned by one-to-many Groups or Users at distinct points in time
A User owns zero-one-or-many Playlists
A Groups owns zero-one-or-many Playlists
A Group is made up of one-to-many Members (who must be Users)
A User may be a Member of zero-one-or-many Groups.
A Group is made up of one-to-many Members (who must be Users)

As the associations or relationships (a) between User and Playlist and (b) between Group and Playlist are quite alike, this fact reveals that User and Group are mutually exclusive entity subtypes of Party1, which is in turn their entity supertype —supertype-subtype clusters are classic data structures that come about in conceptual schemas of very diverse sorts—. In this manner, two new rules can be asserted:

A Party is categorized by exactly one PartyType
A Party is either a Group or a User

And four of the previous rules must be reformulated as only three:

A Playlist is owned by exactly one Party at a specific point in time
A Playlist may be owned by one-to-many Parties at distinct points in time
A Party owns zero-one-or-many Playlists

Expository IDEF1X diagram
The IDEF1X2 diagram shown in Figure 1 consolidates all of the aforementioned business rules along with other ones that appear pertinent:

As demonstrated, Group and User are portrayed as subtypes that are connected by the respective lines and the exclusive symbol with Party, the supertype.
The Party.PartyTypeCode property stands for the subtype discriminator, i.e., it indicates which kind of subtype instance must supplement a given supertype occurrence.
Also, Party is connected with Playlist via the OwnerId property which is depicted as a FOREIGN KEY that points to Party.PartyId. In this way, Party interrelates (a) Playlist with (b) Group and (c) User.
Accordingly, since a particular Party instance is either a Group or a User, a specific Playlist can be linked with at most one subtype occurrence.
Illustrative logical-level layout
The IDEF1X diagram expounded before has served me as a platform to create the following logical SQL-DDL arrangement (and I have supplied notes as comments highlighting several points of particular relevance —e.g., the constraint declarations—):
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient INDEX strategies based on the exact
-- data manipulation tendencies of your business domain.

-- As one would expect, you are free to utilize
-- your preferred (or required) naming conventions.

CREATE TABLE PartyType ( -- Represents an independent entity type.
    PartyTypeCode CHAR(1)  NOT NULL,
    Name          CHAR(30) NOT NULL,
    --
    CONSTRAINT PartyType_PK PRIMARY KEY (PartyTypeCode),
    CONSTRAINT PartyType_AK UNIQUE      (Name)
);

CREATE TABLE Party ( -- Stands for the supertype.
    PartyId         INT       NOT NULL,
    PartyTypeCode   CHAR(1)   NOT NULL, -- Symbolizes the discriminator.
    CreatedDateTime TIMESTAMP NOT NULL,
    --
    CONSTRAINT Party_PK            PRIMARY KEY (PartyId),
    CONSTRAINT PartyToPartyType_FK FOREIGN KEY (PartyTypeCode)
        REFERENCES PartyType (PartyTypeCode)
);

CREATE TABLE UserProfile ( -- Denotes one of the subtypes.
    UserId     INT      NOT NULL, -- To be constrained as both (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    UserName   CHAR(30) NOT NULL,
    FirstName  CHAR(30) NOT NULL,
    LastName   CHAR(30) NOT NULL,
    GenderCode CHAR(3)  NOT NULL,
    BirthDate  DATE     NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Multi-column ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT UserProfile_AK2       UNIQUE (UserName), -- Single-column ALTERNATE KEY.
    CONSTRAINT UserProfileToParty_FK FOREIGN KEY (UserId)
        REFERENCES Party (PartyId)
);

CREATE TABLE MyGroup ( -- Represents the other subtype.
    GroupId INT      NOT NULL, -- To be constrained as both (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Title   CHAR(30) NOT NULL,
    --
    CONSTRAINT Group_PK        PRIMARY KEY (GroupId),
    CONSTRAINT Group_AK        UNIQUE      (Title), -- ALTERNATE KEY.
    CONSTRAINT GroupToParty_FK FOREIGN KEY (GroupId)
        REFERENCES Party (PartyId)
);

CREATE TABLE Playlist ( -- Stands for an independent entity type.
    PlaylistId      INT       NOT NULL,
    OwnerId         INT       NOT NULL,
    Title           CHAR(30)  NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    --
    CONSTRAINT Playlist_PK     PRIMARY KEY (PlaylistId),
    CONSTRAINT Playlist_AK     UNIQUE      (Title),  -- ALTERNATE KEY.
    CONSTRAINT PartyToParty_FK FOREIGN KEY (OwnerId) -- Establishes the relationship with (a) the supertype and (b) through the subtype with (c) the subtypes.
        REFERENCES Party (PartyId)
);

CREATE TABLE GroupMember ( -- Denotes an associative entity type.
    MemberId       INT       NOT NULL,
    GroupId        INT       NOT NULL,
    IsOwner        BOOLEAN   NOT NULL,
    JoinedDateTime TIMESTAMP NOT NULL,
    --
    CONSTRAINT GroupMember_PK              PRIMARY KEY (MemberId, GroupId), -- Composite PRIMARY KEY.
    CONSTRAINT GroupMemberToUserProfile_FK FOREIGN KEY (MemberId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT GroupMemberToMyGroup_FK     FOREIGN KEY (GroupId)
        REFERENCES MyGroup (GroupId)
);

Of course, you can make one or more adjustments so that all the characteristics of your business context are represented with the needed precision in the actual database.
Note: I have tested the above logical layout on this db<>fiddle and also on this SQL Fiddle, both “running” on PostgreSQL 9.6, so that you can see them “in action”.
The Slugs
As you can see, I did not include Group.Slug nor Playlist.Slug as columns in the DDL declarations. This is so because, in agreement with your following explanation

These slugs are unique, lowercase, hyphenated versions of their respective entity's title. For example, a group with the title 'Test Group' would have the slug 'test-group'. Duplicates are appended with incremental integers. This would change any time their title changes. I believe that means they wouldn't make great primary keys? Yes, slugs and usernames are unique in their respective tables.

one can conclude that their values are derivable (i.e., they must be computed or calculated in terms of the corresponding Group.Title and Playlist.Title values, sometimes in conjunction with —I assume, some kind of system-generated— INTEGERs), so I would not declare said columns in any of the base tables as they would introduce update irregularities.
In contrast, I would produce the Slugs

maybe, in a view, which (a) includes the derivation of such values in virtual columns and (b) can be used directly in further SELECT operations —appending the INTEGER part could be obtained, e.g., by combining the value of (1) the Playlist.OwnerId with (2) the intermediate hyphens and (3) the value of the Playlist.Title;

or, by virtue of application program code, mimicking the approach described before (perhaps procedurally), once the pertinent data sets are retrieved and formatted for end-user interpretation.

In this manner, any of those two methods would avoid the “update synchronization” mechanism that ought to be put in place iff the Slugs are retained in columns of base tables.
Integrity and consistency considerations
It is critical to mention that (i) each Party row must be complemented at all times by (ii) the respective counterpart in exactly one of the tables standing for the subtypes, which (iii) must “comply with” the value contained in the Party.PartyTypeCode column —denoting the discriminator—.
It would be quite advantageous to enforce that kind of situation in a declarative manner, but none of the major SQL database management systems (including Postgres) has supplied the necessary instruments to proceed like that; therefore, writing procedural code within ACID TRANSACTIONS is so far the best option to guarantee that the circumstances previously described are always met in your database. Other possibility would be resorting to TRIGGERS, but they are prone to make things untidy, so to speak.
Comparable cases
If you want to establish some analogies, you might be interested in taking a look at my answers to the (newer) questions entitled

“Designing a database for a funds transfers business context” and
“Modeling a scenario in which each Music Artist is either a Group or a Solo Performer”,

since comparable scenarios are discussed.

Endnotes
1 Party is a term used in legal contexts when referring to either an individual or a group of individuals that compose a single entity, so this denomination is suitable to represent the concepts of User and Group with respect to the business environment in question.
2 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the early theoretical works authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


Pausing the secondary Replicas for a day in always on availability group We are planning to make the DR as primary and do maintenance on the secondary site(Formerly Primary Site)  and hence pausing both the secondaries in always on availability group for a day. I am aware that if both the secondaries are paused then there wont be any failover, but will there be any other problems of pausing the secondaries for a day ...Am i missing something ? Any help will be greatly appreciated !

Can i safely go to the Failover cluster manager and paused both the secondary nodes ?

...but will there be any other problems of pausing the secondaries for a day ...

"It depends" but in general, yes. the non-complete list of items:

No transaction log truncation on the primary
Primary send queue will be large
Log caches will become large and may end up consuming more memory than you'd like...
Which could cause memory pressure, buffer pool, etc. issues
When the connection reconnects it'll need to send all the outstanding data, which it may not ever catch up

Now, this seems pretty grim, however, if the amount of traffic is normally pretty low (which you can guesstimate by looking at your transaction log backup size history) then you might be able to get away with it.

If, however, the amount is very large then you might just want to remove it from the Availability Group, do your maintenance, replay the logs when you bring it back up, and join it back in.

  Can i safely go to the Failover cluster manager and paused both the secondary nodes ?

You could but that won't affect SQL Server. You'll need to work with SQL Server through T-SQL or SMO. Pausing the node will only cause you more problems on the primary, but pausing the node on the secondary will tell the WSFC to not move any resources over (which won't happen anyway but this won't hurt anything).

Edit: Moving comments and addressing

  On average the total T log Size are 1.5 gb per day

If it'll be down for a week, that's 7 days * 1.5 GB/Day = 7 * 1.5 = 10.5 GB of outstanding data to send and the log file will roughly need to be or grow by this amount - on average. This may or may not be a "big deal" in your environment.

  Cant I just switch servers and pause both the secondaries or from the always on dashboard ssms just disable automatic failovers between the primary and the secondary?

So you could do that, again my concerns with it are listed above. "Pausing" the data movement does just that, pauses the data movement. The nodes will still, mostly likely, lose connection and the WSFC will boot some of the nodes out of the cluster - in fact, depending on the overall design and configuration of the WSFC, the entire cluster may go down (worst case).

Since this is the first time, you may want to find someone to work with to go over the design and give you some recommendations. This should be used to create your DR or runbook/playbook/whatever documentation as David suggested.


SQL Server 2005 still running after in place upgrade to sql 2012 We upgraded a default instance from SQL Server 2005 to 2012 today. But Components of SQL Server 2005 are still on the server. These includes the MSQL1. folder (it has all the data and log files), Mgmt Studio and configuration manager. The instance now shows it is 2012 version. I am hesitant to uninstall SQL Server 2005 as it may cause problems. Any thoughts? Has anyone come to experience this? Thanks in advance

Any uninstall of SQL Server will not delete the data or log directories used by the particular version.

The reason your data and log files stayed in the same spot is because you did an inplace upgrade. It will not move the databases to the "new" folders SQL Server 2012 would create (eg. "..\MSSQL11.MSSQLSERVER\DATA").

The only issue you could hit when removing SQL Server is with the shared tools (e.g. SSMS, etc.), but since 2012 changed to using full VS it should not cause any issues.


Why do innodb tables get corrupted so easily? I'm upgrading a legacy system running MySQL 5.1 all the way to MariaDB 10.  We're on a brand new Windows server, running XAMPP. I've done plenty of DB migrations in my time but this one has just been a week of MySQL / InnoDB hell.

Tables are a mix of MyISAM and InnoDB.  Every time MySQL crashes for any reason -- ANY REASON -- one or more of the InnoDB tables gets corrupted so badly I have to manually drop them and re-import them.  By "manually drop them" I mean:

1) mysqld crashes on startup unless I do innodb_force_recovery=6, and when I do that it puts the DB in read-only mode, so DROP TABLE just gives me a table is read-only error.

2) So instead I have to stop mysqld, delete the .ibd file for the table in question, restart mysqld with innodb_force_recovery=0 (which will now work), and then finally DROP TABLE works.

3) Then I have to re-import 3-4gb tables from SQL dumps, which takes forever.

Interestingly it's never the MyISAM tables, just the InnoDB tables.

If I check the mysql_error.log, what I see is almost always something like this:

2015-12-19  1:39:41 6580 [Warning] InnoDB: Allocated tablespace 234, old maximum was 0
InnoDB: Error: trying to access page number 2601 in space 234,
InnoDB: space name example/foobar,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
2015-12-19 01:39:41 19b4  InnoDB: Assertion failure in thread 6580 in file fil0fil.cc line 5821
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
151219  1:39:41 [ERROR] mysqld got exception 0x80000003 ;

After re-creating and importing the tables everything works great, as long as mysqld doesn't crash.

Sadly I can reproduce the steps to cause this pretty darn easily.  All I have to do is run a really slow query -- for example, there is a particular report our system generates and the query behind it easily runs for 30 seconds (that's another story).  If I happen to stop the mysqld service before it's finished, the very next time I start mysqld it crashes immediately and I'm back to dealing with corrupted InnoDB tables and repeating all the above steps.

I've tried innodb_fast_shutdown = 0, but it didn't help.

You might say, "just don't stop mysqld while that query is running"... and you'd be right, except live users in our system can be running that report at any time, which means any time I need to restart mysqld for any reason I'm taking a chance that it won't restart and I'll have to re-create tables all over again.

What am I doing wrong here?

Update 1
The long running query is just this (changed actual name of table, otherwise this is verbatim):

SELECT * FROM `foobar` ORDER BY `daterun` DESC

The table has approx 130,000 rows, and is about 4GB in size.

Innodb is rather advanced and complex engine than myisam. It is a default storage engine for reasons...

you said the system is upgraded from 5.1 to mariadb 10 (eqv to 5.6). So the upgrade path was mysql 5.1 -> maria 5.5 -> maria 10?

Can you share the query?
Does the issue repeats if you: Kill query and then restart?
Is there anything apart from query running while you're shutting down?
I'd try to find out if there is related bug report.

Solutions to consider even before we identify exact error here:
- separate instance for reporting.
- optimize the query.


check when last backup was taken Brent Ozar had an interesing newsletter on 2015-06-22; How do you manage dbas measuring backups, where he thinks that a good DBA should check when

the last backup was done
it was last restored
the last CheckDB was done

It turned out to be a good exercise for me. Not only did I find two databases that were not backed up, I also discovered that we do have databases where we have never checked that we can restore the backup.

So;
I have provided one solution to the task in the answer section. We have the convention that database names are unique and my script lists production server and test server(s), so I can compare them.
Do you have a solution that is better?

Best regards,

Henrik

I would actually use dbatools (https://dbatools.io) these days

Get-DBALastBackup -SQLinstance Instance

Which looks like this

Old Answer Below

I am Powershell dude (sounds like I am attending a Powershell Anonymous meeting!!)

so this is the script I would use although I would use the objects returned to write to a database for a report or write to Excel or create an HTML email for my manager or whatever was required.
Remember the $Servers should hold SERVERNAME\InstanceName,PORTNumber if any of those are not standard

# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$Servers =
## A list 'Servername1','Servername2' a text file Get-Content 'PATHTOSERVERFILE' or query a database Invoke-SQLCmd -Server SERVERNAME -Database ALLMyInstances -Query "Select Name FROM Instances"
foreach($Server in $Servers)
{
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server
$lastDBCC_CHECKDB = @{Name="Last DBCC Check";Expression={$_.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | where {$_.Field.ToString() -eq "dbi_dbccLastKnownGood"} | Select Value -ExpandProperty Value}}
foreach($db in $srv.databases)
{
$db|Select Parent,Name,LastBackupDate,LastDifferentialBackupDate,LastLogBackupDate,$lastDBCC_CHECKDB
 }
}


weekly moving average from current_date to the last 52 weeks I'm a new head to the mysql programming. I have to write a procedure for calculating a weekly moving average grouped by week from 'monday-friday'.

Criteria is : current_date is the end date. start date should be either previous 52 weeks if available or if the number of weeks is less then the moving average should be calculated till the available date.

SELECT g(f(date)) AS 'week of',
       AVG(...) AS 'moving average'
    FROM tbl
    WHERE date > CURDATE() - INTERVAL 52 WEEK
    GROUP BY f(date)

Where f() is a function that turns a date (or datetime or timestamp) into a number.  Something like FLOOR((TO_DAYS(date) - 3) / 7), where the 3 is chosen based on what day a week "starts" on.  (I doubt if '3' is correct.)  g() is the inverse:  FROM_DAYS((x * 7) + 3).  Test:

mysql> SELECT                            NOW(),
                          FLOOR((TO_DAYS(now()) - 3) / 7) AS 'f(now)',
              FROM_DAYS( (FLOOR((TO_DAYS(now()) - 3) / 7) * 7) + 3) AS 'g(f(now))';
+---------------------+--------+------------+
| NOW()               | f(now) | g(f(now))  |
+---------------------+--------+------------+
| 2016-05-24 17:13:54 | 105210 | 2016-05-24 |
+---------------------+--------+------------+


PostgreSQL Common Table Expression: Ambiguous Column I have a Common Table Expression which joins two tables with some column names in common:

CREATE TABLE first(
    id serial PRIMARY KEY,
    data VARCHAR,
    date DATE
);
CREATE TABLE second(
    id serial PRIMARY KEY,
    key INT REFERENCES first(id),
    data VARCHAR,
    date DATE
);

WITH cte AS
    (SELECT * FROM first JOIN second ON first.id=second.key)
SELECT data FROM cte;

This, of course, results in an error because field data is ambiguous.

I know that it would be possible to extract the ambiguous columns with the CTE and give them unique names.

The question is, is it possible resolve this ambiguity from the main SELECT statement? Alternatively, is there some notation I can use within the CTE to make this impossible without actually creating new names?

Edit

A number of comments suggest using the wild card is not appropriate. I only use it here to simplify the query — never in a serious query. Here is another version:

WITH cte AS
    (SELECT f.id as fid, s.id as sid, f.data, s.data
        FROM first f JOIN second s ON f.id=s.key
    )
SELECT data FROM cte;

I know that I can resolve the issue by aliasing in the CTE:

WITH cte AS
    (SELECT f.id as fid, s.id as sid, f.data as fdata, s.data as sdata
        FROM first f JOIN second s ON f.id=s.key
    )
SELECT fdata FROM cte;

While there is no real solution that matches your requirements, there are a few things to consider about the requirements.

First, let's see the hint that an error raised about a misnamed column produces:

WITH ts AS (
    SELECT * FROM t1 JOIN t2 USING (t_id)
) SELECT col2 FROM ts;

ERROR:  column "col2" does not exist
LINE 1: ...AS (SELECT * FROM t1 JOIN t2 USING (t_id)) SELECT col2 FROM ...
                                                             ^
HINT:  Perhaps you meant to reference the column "ts.col1" or the column "ts.col1".

Even the database suggests to choose from two indentically named columns - which, as you already guessed, impossible.  (Interestingly, you cannot do such a thing in a definition of a view, not to mention tables.)

So, about the requirements:

It is usually wise not to use * in the first place.  Apart from being faster to type, it does not bring too much.  When writing the main SELECT, we usually have an idea which columns we need - just add those inside the CTE.  This might be in some cases even beneficial for the performance of the query (rendering the size of the data set smaller).

When you want to make it simpler to type, you could try something like

SELECT f.*, s.data AS s_data
FROM first AS f JOIN second AS s ON f.id = s.key

In my practice, it is even better to name columns with a prefix derived from the table name:

CREATE TABLE first(
    f_id serial PRIMARY KEY,
    f_data VARCHAR,
    f_date DATE
);

CREATE TABLE second(
    s_id serial PRIMARY KEY,
    s_first_id INT REFERENCES first(id), -- an exception, for making more clear
                                         -- what the column refers to
    s_data VARCHAR,
    s_date DATE
);

This way, even in big databases, it is very rare to have columns with the same name.  And this completely removes the problem you are fighting with.


MySQL sum of column group by question I have the results of a select that I am joining on that I need to get a slightly complex sum of
a column from.

I need the sum of result grouped on id PLUS the sum of result of any row where parent_id = id.

The data from the join:

cat_id   parent_id   name    result
------   ---------   -----   ------
1        2           name1   7
1        2           name1   1
2        4           name2   2

For example from the above I need a result like:

cat_id   name    sum(result)
------   -----   -----------
1        name1   8
2        name2   10

Any ideas much appreciated

SELECT id, name, SUM(result)
FROM (SELECT cat_id as id, name, result  FROM test
UNION ALL
SELECT t1.parent_id as id, t2.name, t1.result
FROM test as t1
LEFT JOIN test as t2 ON t2.cat_id = t1.parent_id
) as a
GROUP BY id,name ORDER BY id


Implement SQL Server 2012 AlwaysOn I have two SQL Server instances running on Windows Server, SQL Server cluster with 2 nodes with shared storage (Active, Active type).

The SQL Server version running is SQL Server 2012 Enterprise edition.

Can we implement AlwaysOn on this? Is it feasible then what are the complexities and steps?

Yes and no. You cannot configure it in such a way that each server hosts a copy of the database. They both share the same storage, so that is logical. You have a SQL Server cluster OR you configure AlwaysOn between the 2. However, you can host an AlwaysOn copy on a SQL Server failover cluster instance (FCI). In that case, you need one or more other SQL Servers to host the synchronous or a-synchronous copies. The picture below might illustrate this a bit (no sure where I got it from, it is from my own library):

Mind the difference between the two: a SQL Server failover cluster hosts an instance of SQL Server using 2 servers in an active/passive configuration. An AlwaysOn node hosts a database or group of databases on a single instance.

Here are some possible usefull links:

https://blog.papercut.com/the-ultimate-guide-to-high-availability-methods-for-microsoft-sql-server/

https://www.brentozar.com/archive/2012/02/introduction-sql-server-clusters/

https://techcommunity.microsoft.com/t5/Premier-Field-Engineering/An-overview-of-High-Availability-and-Disaster-Recovery-solutions/ba-p/370479

Is this the configuration you currently have?

If so, then you can create a setup like the one below. You can see that there is an extra SQL Server node with 1 instance that hosts an a-synchronous copy for each database of the availability group (where a-synchronous would be common for a DR setup). You can use synchronous copies technically, but you would not use that for a DR scenario. Synchronous copies rely heavily on latency and is therefore suitable for an High Availability scenario. For your understanding; look at the AlwaysOn layer as a seperate thing. There is no difference in configuring the AlwaysOn group on single server instances or a Failover Cluster Instance. AlwaysOn is configured at the database level.

Be aware that you need to buy an extra license in this configuration. However, recently there was a change in licensing for SA customers, which would make it free:

https://cloudblogs.microsoft.com/sqlserver/2019/10/30/new-high-availability-and-disaster-recovery-benefits-for-sql-server/

Also; don't forget to look how you would setup your maintenance scripts and that instance level configuration is not copied to the other server. If you require things to be in sync, then you need to script that or update it manually every time you change something.

I found this easy setup guide for you to configure the AlwaysOn group:

https://www.sqlservercentral.com/blogs/alwayson-availability-groups-step-by-step-setup

Also be aware that mixing 2 seperate technologies (Failover Cluster Instances and AlwaysOn) make things more complex. It is more complex to troubleshoot then when you have only 1 technology. You could setup a similar HA/ DR setup with AlwaysOn only like below. Just be aware that you need double the storage space (which you don't need for a cluster setup) and you would have some more latency (because you need to wait for the commit of the synchronous copy):

There are a lot of things you need to make decisions on, because all the technologies have their strong and weak points. The only option to learn them all is to read about it, but I think you get a fairly good jumpstart with all this information.

Oh and you might want to delete your Answer earlier. I saw you also set it in the comments, so it is double.


Retrieve user id for users that have 5 specific rows I have 2 tables: Titles and Data
They have a One-to-One relationship
The entry_id in Titles t = entry_id in Data d

I need to retrieve the t.author_id for those users who have 5 specific rows in the Data table.

e.g

SELECT t.author_id
FROM titles t, data d
WHERE d.skills = "Skill A"
AND d.skills = "SKILL B"
AND d.skills = "Skill C"
AND d.skills = "Skill D"
AND d.skills = "Skill E"
GROUP BY author_id

I think i have too many joins happening as the query won't finish executing.

Any ideas?

Thanks.

If your both tables have referential relationship with entry_id then you can fetch your result data through with 'IN' clause.
No need to write down as many AND operator in the query.

select t.author_id,t.entry_id,d.entry_id

from yourdatabsename.titles as t left outer join yourdatabasename.data  as d

on t.entry_id=d.entry_id

where d.skills IN ('Skill A','Skill B','Skill C','Skill D','Skill E')

Group By t.author_id

Order By t.author_id
;


In Oracle shared server mode, what is a virtual circuit? I read that shared server mode allows dispatcher processes to use two new SGA sturctures : queues (request and response) and UGA (which is just PGA "flushed" in SGA)

So with a that, a single dispatcher can handle several client connections.

My issue is I saw an expression I don't understand : "virtual circuit". What does it do and where exactly is it positionned ?

Its a shared memory(in SGA) used by dispatcher for client connection requests and responses.

  In a shared server configuration, client user processes connect to a dispatcher. The dispatcher can support multiple client connections concurrently. Each client connection is bound to a virtual circuit, which is a piece of shared memory used by the dispatcher for client database connection requests and replies. The dispatcher places a virtual circuit on a common queue when a request arrives.

Reference

In other words, its a memory where the actual request is stored and shared server can know what it has to serve by reading this memory. For example if a client sends a query lets say select * from emp, the dispatcher has to store it somewhere so that an idle shared server process can pick it up and process further. The somewhere is the virtual circuit.


(adding a second answer -- to address the VARIABLES and STATUS)

Observations:

Version: 5.6.24
52 GB of RAM
Uptime = 21d 02:26:20
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

table_open_cache = 200K, yet 156 tables opened per second, and all are misses/overflows from the cache.  You must redesign you schema to have a civilized number of tables.
Are there really 403 ROLLBACKs per second?  If so, think about how to avoid some of them.  That's probably more than 10% of the transactions.
innodb_lock_wait_timeout = 300 -- Assuming you really need transactions to hang around waiting for 5 minutes, this could be consuming RAM.
tmp_table_size = 8M, together with 51 tmp tables created per second, may be leading to quite a few GB of transient MEMORY tables.  Look through the slowlog to find which queries need tmp tables and see if they can be rewritten.
Decrease long_query_time to 2 (now 10), turn on the slowlog, wait a day, run pt-query-digest.  Then study the worst few queries -- improving them will speed up the entire system and probably decrease RAM usage.

Details and other observations

( Innodb_buffer_pool_reads ) = 329,533,959 / 1823180 = 180 /sec -- InnoDB buffer_pool I/O read rate
-- check innodb_buffer_pool_size

( Innodb_buffer_pool_pages_flushed ) = 589,768,119 / 1823180 = 323 /sec -- Writes (flushes)
-- check innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 16,106,127,360 / 53248M = 28.8% -- % of RAM used for InnoDB buffer_pool

( Opened_tables ) = 285,544,112 / 1823180 = 156 /sec -- Frequency of opening Tables
-- increase table_open_cache

( table_open_cache ) = 200,000 -- Number of table descriptors to cache
-- Several hundred is usually good.

( Table_open_cache_overflows ) = 285,343,673 / 1823180 = 156 /sec
-- May need to increase table_open_cache

( Table_open_cache_misses ) = 285,544,112 / 1823180 = 156 /sec
-- May need to increase table_open_cache

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((329533959 + 589768119) ) / 1823180 = 504 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?

( innodb_log_buffer_size ) = 128M -- Suggest 2MB-64MB, and at least as big as biggest blob set in transactions.
-- Adjust innodb_log_buffer_size.

( Innodb_log_writes ) = 167,769,519 / 1823180 = 92 /sec

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 332,945,398,784 / (1823180 / 3600) / 2 / 100M = 3.13 -- Ratio

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,823,180 / 60 * 100M / 332945398784 = 9.57 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size.

( Com_rollback ) = 735,486,482 / 1823180 = 403 /sec -- ROLLBACKs in InnoDB.
-- An excessive frequency of rollbacks may indicate inefficient app logic.

( innodb_lock_wait_timeout ) = 300 -- Two battling InnoDB transactions, but not a deadlock -- one will wait this long (seconds) in hopes of getting the desired locks.
-- Fix the cause of timeouts rather than increasing this value.

( Innodb_dblwr_writes ) = 14,574,293 / 1823180 = 8 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)

( local_infile ) = ON
-- local_infile = ON is a potential security issue

( Questions ) = 7,836,091,851 / 1823180 = 4298 /sec -- Queries (outside SP) -- "qps"
-- >2000 may be stressing server

( Queries ) = 8,262,090,685 / 1823180 = 4531 /sec -- Queries (including inside SP)
-- >3000 may be stressing server

( Created_tmp_tables ) = 92,537,439 / 1823180 = 51 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 6,158,440 / 1823180 = 3.4 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables being able to use MEMORY instead of MyISAM. It may be possible to make a minor schema or query change to avoid MyISAM.
Better indexes and reformulation of queries can

( Handler_read_rnd_next ) = 266,467,437,770 / 1823180 = 146155 /sec -- High if lots of table scans
-- possibly inadequate keys

( Com_rollback / Com_commit ) = 735,486,482 / 741864016 = 99.1% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic

( Com_show_variables ) = 6,666,028 / 1823180 = 3.7 /sec -- SHOW VARIABLES ...
-- Why are you requesting the VARIABLES so often?

( Select_scan ) = 315,076,005 / 1823180 = 172 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 315,076,005 / 3888296954 = 8.1% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (613230844 + 1238003 + 0 + 30479625 + 71282659 + 0) / 1823180 = 392 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 23 - 22 ) / ( 23 + 22 ) = 2.2% -- Are you closing your prepared statements?
-- Add Closes.

( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( Com_change_db ) = 31,947,189 / 1823180 = 18 /sec -- Probably comes from USE statements.
-- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.

( max_connect_errors ) = 10,000 -- A small protection against hackers.
-- Perhaps no more than 200.

( Connections ) = 21,121,749 / 1823180 = 12 /sec -- Connections
-- Increase wait_timeout; use pooling?

( Threads_running - 1 ) = 22 - 1 = 21 -- Active threads (concurrency when data collected)
-- Optimize queries and/or schema

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

39 issues flagged, out of 125 computed Variables/Status/Expressions. Settings not mentioned are mostly OK.


Upgrade SQL Server 2000 engine I have SQL Server in which I need to upgrade and restore a .bak file

Server 1:

  Microsoft SQL Server  2000 - 8.00.194 (Intel X86)

And by requirements I need it to match Server 2:

  Microsoft SQL Server  2000 - 8.00.2040 (Intel X86)

I have the SQL2000-KB899761-v8.00.2040-x86x64-ENU.exe, but it says I need a previous update.

This version is too old and I can't figure where to start.

Actually what I really need is restore a .bak file from Server 2 and put it to Server 1.

I can't because it says:

  ... is not a valid Microsoft Tape Format

I can restore it within a 2008 Server. The issue is when I try to restore to the older Server 1.

How to get the 8.00.194 (!!!!!!) server to 8.00.2040:

You can download Service Pack 4 (8.00.2039) from here, which you will need to install before you can install the 8.00.2040 hotfix.

How to restore a 2000 .bak to a 2008 server:

You may need to clarify your actual requirements and clean up the question, but if you are trying to ultimately restore a SQL Server 2000 .bak file from server 2 to a SQL Server 2008 instance somewhere else, there is no reason to restore to server 1 first - just restore the .bak directly on the SQL Server 2008 instance (regardless of the source, 8.00.194 or 8.00.2040).

How to resolve the invalid tape format message:

You may have an invalid RESTORE statement, or you may have a corrupt backup. If the former, add the RESTORE statement to the question. If the latter, take a new backup (there are some tedious workarounds if you are restoring to 2005 or higher, but they include potentially allowing data loss). I don't believe this error message will be resolved simply by upgrading server 1. If @@VERSION were the issue (and it can be in some cases), you'd get a different error message than one about tape format.


How to restore a database to the latest file in a backup set USE ICT;
GO
BACKUP DATABASE ICT
TO DISK = 'E:\Backup\ICT.bak' -- Verwijder dit bestand a.u.b.
GO

RESTORE DATABASE ICT_TEST FROM disk = 'E:\Backup\ICT.bak'
WITH REPLACE,
   MOVE 'ICT' TO 'E:\DATA\ICT_TEST.mdf',
   MOVE 'ICT_log' TO 'E:\DATA\ICT_TEST_log.ldf'
GO

This script makes a backup from my PROD DB en restores it to my TEST DB. When i run this script twice, i get the first backup in the file. How can i make sure that the latest backup will be restored over the TEST DB? I understand that you can tell SQL:

RESTORE DATABASE ICT_TEST FROM disk = 'E:\Backup\ICT.bak'
WITH REPLACE, FILE = 2

But i don't want to do this every time.

Converting my comment to answer (read the comments in the script) :

if you are using the same backup file name (ICT.bak) every time you backup, its better to use INIT. This way the previous file gets overwritten.

USE master -- here no need to use the name of the database that you are backing up. Other RDBMS like SybaseASE will thorw an error saying that you should be in master db to run a backup command
GO
BACKUP DATABASE ICT
TO DISK = 'E:\Backup\ICT.bak' -- Verwijder dit bestand a.u.b.
with init, stats =10
--, compression -- if you are using sql server 2008R2 standard and up (enterprise has compression feature). Make sure that backup compression is enabled using sp_configure option.
GO

RESTORE DATABASE ICT_TEST FROM disk = 'E:\Backup\ICT.bak'
WITH REPLACE, RECOVERY,
   MOVE 'ICT' TO 'E:\DATA\ICT_TEST.mdf',
   MOVE 'ICT_log' TO 'E:\DATA\ICT_TEST_log.ldf'
GO


Is there any option to kill the particular process automatically if it is consuming a particular value of cpu? some sort of sql process makes my database server to consume 100% of the cpu, Is there any option to kill the particular process automatically if it is consuming a particular value of cpu ?

No, there isn't. But you could consider setting statement_timeout, which will kill any query running longer than a certain time.


Connection refused when adding ApplicationIntent Environment : AlwaysOn HA group, 2 nodes, 1 database, SQL2012, Server2012R2.

Here is my .NET C# code :

string connectionString = "Server=tcp:ecouteuralwayso.xppocsql.local,1435;Database=AdventureWorks2012;Integrated Security=SSPI;";
string queryString = "select top 30 * from Person.Person;";
using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(queryString, connection);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

[...]

It works perfectly. "ecouteuralwayso.xppocsql.local" accepts connections on TCP1435, I can read my database, no matter which node is the primary.

I want to use Read-Only redirection (http://msdn.microsoft.com/en-us/library/hh710054.aspx), so I configured read-only urls as described in the KB. Here are the results:

select read_only_routing_url from sys.availability_replicas

Which gives:

TCP://MSSQLCLUST-3.xppocsql.local:1433 [the "primary"]

TCP://POCSQL3.xppocsql.local:1433 [the "secondary"]

Then, I add

ApplicationIntent=ReadOnly;MultiSubnetFailover=True

To my connection string ... But it doesn't work :

  [Win32Exception (0x80004005): The remote computer refused the network connection]

  [SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The remote computer refused the network connection.)]
     System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +6564850

Without the MultiSubnetFailover, the error is :

[Win32Exception (0x80004005): No connection could be made because the target machine actively refused it]

Nothing logged in SQLServer...Any ideas?

Ok, got it. I had to force the TCP1433 port in SQL Configuration manager... That was only that.
I found it when trying to connect with a wrong password : SQLServer denied connection because of this wrong pass.
So, the mistake was between the listener and the server supposed to accept the connection... which was the second one, because of the read-only intent.

And this second one, even if it was listening on TCP1433, wasn't responding without explicitely configuring it in Configuration Manager.

Sorry!


Return counts for multiple ranges in a single SELECT statement I have a Postgres database table foo that, among other things, has a column for score that ranges from 0 - 10. I want a query to return the total number of scores, the number of scores between 0 and 3, the number of scores between 4 and 6, and the number of scores between 7 and 10. Something like the following:

SELECT
  COUNT(*) as total,
  COUNT(
    SELECT * from foo where score between 0 and 3;
  ) as low,
  COUNT(
    SELECT * from foo where score between 4 and 6;
  ) as mid,
  COUNT(
    SELECT * from foo where score between 7 and 10;
  ) as high
FROM foo;

I tried this, but got an error with the SELECT in the COUNT statements. Any ideas how I can do this? I'm sure there's a super simple way in Postgres. I just can't figure out the correct terms to Google for.

Just use conditional SUM() statements per column for each number range. The total can be summed by just using SUM(1), assuming all of the data in the table is within one of the ranges - if not, just restrict it as with the others.

select sum(case when score between 0 and 3 then 1 else 0 end) as minrange,
       sum(case when score between 4 and 6 then 1 else 0 end) as midrange,
       sum(case when score between 7 and 10 then 1 else 0 end) as maxrange,
       sum(1) as total
from foo;

SQL Fiddle link.


MySQL server parameters default empty string to null How can I set MySQL server parameters to default empty strings to null on insert?

is this possible?

There is no way to do that by changing a server parameter. NULL is different from an empty string so there is no reason to have that.

You could change your application to send NULL to your SQL statement or add a condition to your query.

INSERT INTO table (column1) values (IF(LENGTH('$variable')=0,NULL,'$variable'));


Is there harm in applying passwords to built-in Oracle Roles like DBA, Connect, etc First, thanks for any help you may be able to provide.  Oracle 12.1.0.2 on Windows x64

Is there any harm in applying passwords (for example: alter role connect identified by "my_password") to all built-in roles? I ask as an audit is complaining that these built-in roles (such as connect, dba, java_admin, etc.) by default have no password requirement.

I've checked documentation and cannot find any information as to what subtle or not so subtle repercussions may occur if I do password protect these roles. Again thanks for any help you can provide.

These are roles required to function as those roles.  However, users cannot just use those roles.  They are granted to users by a DBA as required.  What they should be auditing is who has the role and should they have them.  To do that they will need to understand what the purpose of the role is.

Many of the roles will be granted to few, if any, non-standard users. But some such as connect will be granted to almost all users.

It may help them if you document those roles granted to not non-standard users.  Include the reasons they would/should be granted to a user.  It is not uncommon to create local roles to simplify granting access.  Theses should also be documented and identified as local.  Having such a document should make dealing with the auditors easier as it will show that roles are being managed.


How to find if the application uses the database snapshots in Management Studio I'm taking over dba responsibilities for the migration of an environment that the application owners are unable to give enough information on.
I need to comment on possibility of optimizing licensing cost. Having compared Enterprise and Standard edition features, the only concerns i have are for Database snapshots and TDE, both of which are only supported by Enterprise in SQL 2012.
There are snapshots in SSMS for some of the databases, but how do I know if the application is using them or not? My thoughts are that, if they are not being used then we could look at using Standard edition.
For TDE I can check that with sys.dm_database_encryption_keys.
Any thoughts to guide me will be appreciated please.
Thank you

Sql server 2012 will go out of support next year July 2022.
For more recent sql server edition key points are HA & DR features like AG.
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15
Other good point are the index online rebuild feature, but also memory limit, read-ahead, partitions and data compression.


Consistency of data between two databases on different server I have to guarantee consistency of data stored in two databases installed on different servers.
The first one is a MySql database and the second is a Sql Server database.
Data's properties are not the same. There's only one field that allows me to join them.
Basically, I get the object A in the SqlServer database and retrieve the object B in the MySql database using a where clause A.Id = B.A_Id.
Primary keys are auto generated and INT(11) type. The Sql Server database is not yet implemented.
The connection at the MySql database is managed by a WPF application and the connection at the Sql Server database is managed by a WebAPI. I'm using the ORM NHibernate to perform databases handling.
I have to ensure that no errors occurs during an insert.
I have to insert the object A in the Sql Server database to be able to set the A_Id property of my object B before inserting it.
If the insertion in the MySql database fails, I have to rollback the insertion in the Sql Server database.
I would like to find a solution to avoid an insert/delete in the Sql Server database.
What is the best approach to achieve this ?
I try to implement something like this but I'm facing an issue with the primary key of object A:
Connection open for SqlServer database
Begin transaction
Insert A  -> Id is not set : Is it possible to ask NHibernate to book an id here ???
Connection open for MySql database
Begin transaction
Insert B
Commit transaction for B
Commit transaction for A
Close transaction for B
Close transaction for A
Close connection for B
Close connection for A\
Thanks in advance.

If SQL server is the master,
LEt him insert first and get an id, this id you use to insert into mysql. As the sql server control ist. Mysql has an index on the id but is not a primary key., so you can insert what ever you need.
If both can generate the id, you must switch to uuids, these would always guarantee, that you are unique, and so it doesn't matter who generated the id first. as teh application generates the uuid, no autoincrrement is needed any more.
It will always be a good idea, to switch to other unique identifiers when mnore than one server is invooved.
The price is that the queries get slower, even with an index on the ids


Mysql Connections are string when checking from SHOW STATUS LIKE '%onn%'; I have a Mysql db server which seems to be getting slow by time. When i am checking detail from SHOW STATUS LIKE '%onn%'; I get following details

Variable_name                                  Value
---------------------------------------------  --------
Aborted_connects                               2
Connection_errors_accept                       0
Connection_errors_internal                     0
Connection_errors_max_connections              0
Connection_errors_peer_address                 0
Connection_errors_select                       0
Connection_errors_tcpwrap                      0
Connections                                    2327
Max_used_connections                           7
Performance_schema_session_connect_attrs_lost  0
Ssl_client_connects                            0
Ssl_connect_renegotiates                       0
Ssl_finished_connects                          0
Threads_connected                              5

Is there anyway i can get the connections detail of the open connections?
Like source ip, username e.t.c.
Mysql version is 5.6

Their docs indicate that connections variable is,

  "The number of connection attempts (successful or not) to the MySQL
  server."

I just stopped and restarted the service on my dev server and connections dropped from 28 to 6.

Threads_connected is

  "The number of currently open connections."

and those are shown using show processlist;


Why are certain objects loaded into Buffer? I have been experimenting on my test environment with clearing buffer cache via

DBCC DROPCLEANBUFFERS

Before any work is done or operational queries run the buffer is instantly starting to repopulate with data from core tables (data warehouse). It is loading one dimension fully (3 years data) whist others are ignored or only partially loaded so for example

Tab1 1490476 of 1490525 pages loaded (100%) (60m+ row dim)
Tab2 86446 of 12737347 pages loaded (0.68%) (250m row+ fact)
Tab3 14 of 18 pages loaded (77.78%) (800 row dim)

Tab1 would rarely have all rows read and updates/inserts would only occur on approximately the last 20% where as Tab3 would be 100% read pretty much all the time but rarely updated/inserted.

Interestingly when I test in a dev environment no data is reloaded in buffer until queries start to be run at which point I see a far different profile

Tab1 89154 of 512569 pages loaded (17.39%)
Tab2 105652 of 289851 pages loaded (36.45%)
Tab3 18 of 18 pages loaded (100%)

These were just random reports and a couple of builds run against dev to get these figures but still fairly true to typical usage. The test environment did not change other than a higher dirty row count.

I am curious as to whether the buffer is being reloaded from something like the plan cache or is there something else i should be looking at?

The reason for this question is that I have a performance problem in production where there are significant IO waits when loading Tab2 and I am perplexed as to why Tab1 is being fully loaded despite limited use and in my simple mind a higher buffer % for Tab2 would make more sense.

2017-02-24
There were a couple of queries being passed down that were highlighted by BlitzCache on read sort that were reading an unnecessary amount of data from Tab1 but it also reads from Tab4 equally unnecessarily (Both joined from another table on Surrogate key in the read).

Tab4 pages are however 0.34% written to buffer which I would assume would have been higher if this was the cause of the high buffer content of Tab1.

There were some other issues highlighed by BlitzCache which I will investigate more fully but still at a bit of a loss here.

First, check the plan cache with queries that hit sys.dm_exec_query_stats. My favorite is the open source sp_BlitzCache (disclaimer: I'm one of the coauthors). Queries could be running outside of your control, like monitoring systems. I've seen third party tools that do things like check table fragmentation.

Next, not everything stays in the plan cache. Unfortunately - especially when you're dealing with monitoring software - some queries are smart enough to do a WITH (RECOMPILE) in order to avoid going into the plan cache. (sp_BlitzCache even does this.) To catch diabolical queries like this, consider doing a Profiler trace or Extended Events capture - although I wouldn't recommend leaving those in place long term. Only do it to satisfy your curiosity.


Automatic deduplication (normalization) of strings in MySQL The question is whether MySQL can use separate physical storage for strings transparently, but with the same operations that are applicable to varchar, including %.

I have a large number of duplicated strings in my MySQL database: for example, many customers have the same first name. To save space, especially in indexes, and thus speedup the queries, I would prefer to physically store only an ID and store the actual string elsewhere: instead of varchar(30)s

| Mary | Smith  | New York |
| Mary | Cooper | Chicago  |
| John | Cooper | New York |

I want to only store ints

| 1 | 2 | 3 |
| 1 | 4 | 5 |
| 6 | 4 | 3 |

where the IDs refer to the strings:

| 1 | Mary     |
| 2 | Smith    |
| 3 | New York |

I can do it manually and use joins to map between strings and IDs in each query, but my question is whether there is any property of a field to tell MySQL to handle this transparently for me, so that in the queries I operate with the table as if it contained strings but the underlying physical storage for strings would be separate?

In my case the DB is rarely (if any) updated but frequently read, the queries being rather complex and slow, so I need to optimize for select, not for update.

MySQL has no such feature.

It is left to the user to "normalize" the data either for avoiding having to update multiple spots and/or for saving space.

In your example, it is generally not practical to do such with first/last name.  But it may be advisable for "locations".

It is not practical to dedup names (for example) because the payoff is poor. With "Mary" sitting somewhere else, the code has to implicitly (as you hypothecate) or explicitly (via a JOIN) reach for the string. In large datasets, this is likely to cause an extra disk hit -- costly. Also, "Mary" is 4 characters; the first_name_id might be a MEDIUMINT UNSIGNED, which is 3 bytes -- not much savings. For bigger strings that repeat a lot (company names), the tradeoffs might be better.

The main purpose of "normalization" is to put things in a single location.  A company name should be spelled out only once in a system that talks about companies.  In its place an id would be used -- possibly an integer, possibly a ticker symbol (as in a Stock database).  When the company changes its name, only one spot needs to be changed.  (If the ticker changes, as happened with AOL, then the code is messy.)


PgBouncer "no more connections allowed (max_client_conn)" I am using PostgreSQL with PgBouncer, and receive the following error in my application:

  08P01: no more connections allowed (max_client_conn)

pgbouncer.ini has the following configuration:

max_client_conn = 100
default_pool_size = 20
pool_mode = session

postgresql.conf has:

max_connections = 100

The PostgreSQL server has 5 databases that are accessed by a single user.

My understanding is that max_connections should be equal to (default_pool_size * # databases), and that max_client_conn should be at least this much. I have also seen the comment in the pgbouncer.ini that the default_pool_size should be equal to the max_client_conn if using session pooling.

So to resolve this error I am encountering, should I simply set default_pool_size = 100? Or increase just the max_client_conn? Or something else?

The point of using PGBouncer is to let it pool connections to the PG server.  If your PG max_connections = 100 and PGBouncer max_client_conn = 100 then you aren't pooling anything.  You should set your PGBouncer max_client_conn to a much higher number to allow it to marshal actual PG connections to service the clients.  I've seen max_client_conn set to default_pool_size * # databases * user count * some arbitrary number (10) to allow enough PGBouncer connections to service all the databases connected to by all the users with an arbitrary (10) connection multiplier.  For example, I have a PG server with one database, max_connections = 100, a PGBouncer default_pool_size of 20 and a PGBouncer max_client_conn = 1000.  That effectively allows PGBouncer to pool 20 connections to my single database while allowing my client 1000 connections to process it's data.  (I'm capturing a lot of data real time from a web api).


Personal Finance App Monthly Reports Database Design ( SQLite ) I am building a personal finance mobile app that supposed to generate monthly reports, now I have a problem, confused about how to design the database to be monthly, I need the wallet to get archived after a month from the day it's created and transfer the remains/loss to the new wallet ( next month ) if exist.

Now my idea is to generate one wallet with xx money and let the user change the start date and make the mobile app automatically generate a new wallet every month on that day with the necessary transactions like there a loss/remain from the old wallet, and archive or prevent the user to make any transaction to the old wallets.
does my logic make sense or ...? ( Note I'm still learning and this my first app using SQL/Database so please be patient with me )

From what I'm understanding you need to design a database which will be record transactions of a user and draw some conclusion like a profit/loss at the end of a month. To achieve this you don't necessarily need to create a wallet every month this is just basic accounting. You will need Transaction table and Transaction_History_Log and Charts_Of_Accounts for more detail check this. Your Transaction table will have a one-to-many relationship to your Transaction_History_Log and Transaction_History_Log will have many-to-one with your Charts_Of_Accounts. So just basically you will need to record journal entries this will help you get a more understanding about what journal entries are in accounting. You can set a createDate attribute in your Transaction_History_Log to just get the a snapshot of month or a whole year or whatever the user likes.


mongorestore from multiple dumps I have a mongo-database where existing entries never change but new entries are added every day. I have ~1000 dumps, each is the new data that was added on the corresponding day.

I.e. I have the following files:

/dump1/mydatabase/mycollection1.bson
/dump1/mydatabase/mycollection1.metadata.json
/dump1/mydatabase/mycollection2.bson
/dump1/mydatabase/mycollection2.metadata.json
...
/dump2/mydatabase/mycollection1.bson
/dump2/mydatabase/mycollection1.metadata.json
/dump2/mydatabase/mycollection2.bson
/dump2/mydatabase/mycollection2.metadata.json
...
...
...
/dump1000/mydatabase/mycollection1.bson
/dump1000/mydatabase/mycollection1.metadata.json
/dump1000/mydatabase/mycollection2.bson
/dump1000/mydatabase/mycollection2.metadata.json

I would like to restore "mydatabase" containing all the collections. The content in each of the collections should be appended to the existing collections instead of overwriting them.

When I run

mongorestore /dump1
mongorestore /dump2
...

will this append my data to the existing collections or overwrite them?

Find below the wordings from MongoDB official documentation

Insert Only

mongorestore can create a new database or add data to an existing database. However, mongorestore performs inserts only and does not perform updates. That is, if restoring documents to an existing database and collection and existing documents have the same value _id field as the to-be-restored documents, mongorestore will not overwrite those documents.

  To answer your question, no the documents won't be overwritten.


SQL Server - Is it possible to forbid creation of default constraint name? I was having a looking on this article about default constraints and I have a question. Is there a configuration in SQL Server 2008 that forbid naming creation of new default constraints declared without a proper custom name?

In other words, can SQL Server block this:

CREATE TABLE Test1 (
   Val1 int DEFAULT -1,
   Val2 int DEFAULT -2)
  /*the fields above will generate a strange suffix in default constraint name such as DF__Test1__Val1__32E0915F*/

but allow this:

CREATE TABLE Test1 (
   Val1 int Test1_Val1_DF DEFAULT -1,
   Val2 int Test1_Val2_DF DEFAULT -2)
  /*the fields will use proper custom name in default constraint name*/

I think you could use a DDL trigger if you could build a reliable LIKE expression that determines that a column has been specified with a default but does not contain the word constraint before the word default. But that won't be very fun. If you don't have PBM you're probably better off just manually reviewing the DDL in the default trace or having a DDL trigger that e-mails you on create/alter table, and slapping wrists when it happens.

(I mean, how often are people creating tables in a way that violates your corporate naming standard, and why are they still allowed to create tables if they can't follow the rules?)


SSDT 2010 and SQL70590 errors I have Visual Studio 2010 SP1 with SSDT (with Dec 2012 update). I have created a new SQL Server project and imported objects from a database on a SQL Server 2008 R2 instance. The database has a compatibility mode of 80, can't change this at the time.

Anyway. When I do a build, which has to be clean in order for me to do a schema compare, it is throwing SQL70590 errors on the variables although SQL Server itself has no problem with them. Looking at them it seems it is because of the case sensitivity of the variable (e.g. declared as @VARIABLE but referenced as @Variable).

The database collation is set to Latin1_General_BIN, is this why the error is being thrown? I can't see why it sees it as an error yet the procedure code runs without error on the database.

Try adding this to a PropertyGroup in the .sqlproj file directly:

<ModelCollation>1033,CI</ModelCollation>

I had a similar issue when was migrating a .dbproj to a .sqlproj. I had this suggested to me by our team's dba, I don't know exactly what this does..


Fastest validation query in PostgreSQL What is the fastest query in PostgreSQL, which I can use as a validationQuery binding a JNDI resource?
I thought that SELECT 1 is the simplest, but in this document it is said that in PostgreSQL we should use select version(). This is not obvious to me.
I've tried to compare EXPLAIN ANALYZE SELECT 1 and EXPLAIN ANALYZE SELECT version() and still can't see why second one is (or should be) faster.

This is obviously nonsense. SELECT 1 is faster (if only by a tiny bit). version() also returns quite a few bytes of text, in my case:

PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

So you get some additional I/O, too.
pgAdmin, for instance, uses SELECT 1 as validation query.


Postgres stops using a (btree_gin) index when select clause includes a column not part of the index I am seeing some strange behavior with the btree_gin extension

I have the following table

create table my_table(id INTEGER, a TEXT, b text);

CREATE EXTENSION btree_gin;

CREATE INDEX idx ON my_table USING gin (id, a);

If I run the query :

select t.id, t.a
from my_table t
where t.id = :someNumber and t.a like 'someString'

the index gets used.

If I add another column in the select clause that is not part of the index, the index is no longer used, ex:

select t.id, t.a, t.b
from my_table t
where t.id = :someNumber and t.a like 'someString'

I'm using PostgreSQL 9.6.3

One thing that is very strange, is that the index does get used if I join the table to anotherone, and add a col from the other table in the select, ex:

select t.id, o.a_col_from_other_table
from my_table t
join another_table o on t.id = o.the_fk
where t.id = :someNumber and t.a like 'someString'

However, if the join clause uses a column not part of the index, it will will resort to seq scan.

Update 1

For some reason, the query optimizer does not even use the btree_gin anymore. The only way I can get it to use the (btree_gin) index is with

SET enable_seqscan = OFF;. It turns out that even by using this index, it is quite slow, so the optimizer is probably correct in not using it, but the question is then why is it slow ?   I've used indexes for like query and they work fine, in this case there is not even a wild card.

the real query is :

 EXPLAIN
    SELECT
      b.transcript_translation_accession
    FROM r_prot_search b
    WHERE
        b.transcript_translation_accession like 'IP_304743.1'
        and b.assembly_id = 12
        and b.openprot_release_id = 28
    limit 30;

The index:

CREATE INDEX idx_r_prot_search2_transcript_translation_accession3
ON r_prot_search USING gin (openprot_release_id, transcript_translation_accession);

and the plan :

"QUERY PLAN"
"Limit  (cost=27633.94..593461.27 rows=30 width=13)"
"  ->  Bitmap Heap Scan on r_prot_search b  (cost=27633.94..2366386.90 rows=124 width=13)"
"        Recheck Cond: (openprot_release_id = 28)"
"        Filter: (((transcript_translation_accession)::text ~~ 'IP_304743.1'::text) AND (assembly_id = 12))"
"        ->  Bitmap Index Scan on idx_r_prot_search2_transcript_translation_accession3  (cost=0.00..27633.91 rows=3079721 width=0)"
"              Index Cond: (openprot_release_id = 28)"

I should probably add that the query returns 5 rows, and the table has 10 millions rows.

update 2

Here's an EXPLAIN ANALYZE, with SET enable_seqscan = ON:

"QUERY PLAN"
"Limit  (cost=1000.00..537006.24 rows=30 width=13) (actual time=15164.523..15610.792 rows=6 loops=1)"
"  ->  Gather  (cost=1000.00..2216492.44 rows=124 width=13) (actual time=15164.521..15610.788 rows=6 loops=1)"
"        Workers Planned: 4"
"        Workers Launched: 4"
"        ->  Parallel Seq Scan on r_prot_search b  (cost=0.00..2215480.04 rows=31 width=13) (actual time=15514.285..15603.515 rows=1 loops=5)"
"              Filter: (((transcript_translation_accession)::text ~~ 'IP_304743.1'::text) AND (assembly_id = 12) AND (openprot_release_id = 28))"
"              Rows Removed by Filter: 1948958"
"Planning time: 0.370 ms"
"Execution time: 15628.514 ms"

Now with SET enable_seqscan = OFF;

"QUERY PLAN"
"Limit  (cost=27633.94..593461.27 rows=30 width=13) (actual time=3406.041..8350.549 rows=6 loops=1)"
"  ->  Bitmap Heap Scan on r_prot_search b  (cost=27633.94..2366386.90 rows=124 width=13) (actual time=3406.038..8350.544 rows=6 loops=1)"
"        Recheck Cond: (openprot_release_id = 28)"
"        Filter: (((transcript_translation_accession)::text ~~ 'IP_304743.1'::text) AND (assembly_id = 12))"
"        Rows Removed by Filter: 3228843"
"        Heap Blocks: exact=320353"
"        ->  Bitmap Index Scan on idx_r_prot_search2_transcript_translation_accession3  (cost=0.00..27633.91 rows=3079721 width=0) (actual time=640.817..640.817 rows=3228849 loops=1)"
"              Index Cond: (openprot_release_id = 28)"
"Planning time: 0.394 ms"
"Execution time: 8350.618 ms"

As jjanes points out, I may not be right here if you've only got a GIN index. I'm waiting for the plan before I revise

I don't believe the index "is no longer used" is correct. In the first query, you're selecting ONLY the columns indexed. That means you can potentially use an "index-only scan". In the second query, you're selecting columns not in the index. That means you CAN NOT use an "index-only scan". That means you have to visit the table on disk.

On the basis of that, in the second case, whether or not the index is used is dependent on the selectivity estimates. We would need the EXPLAIN ANALYZE of the two queries to further assist in that. We have two options:

I imagine the index is used, however the rows need to be retrieved from disk and thus it is slower than the first query. This is what is likely happening.
PostgreSQL assess a higher cost to visiting the table AND the index, so it just visits the table and does a seq scan. Usually this only happens on small tables.


Mongodb - Returning the points nearest to a location, with distance What is the MongoDB's equivalent to this query:

SELECT 111151.29341326 * SQRT( pow(-6.186753-`Latitude`, 2)
                               + pow(106.772835-`Longitude`, 2)
                                 * cos(-6.186753*0.017453292519943)
                                 * cos(`Latitude`*0.017453292519943)
                             )
       as distance
from tablename ;

This uses the haversine formula to compute the great-circle distance to a fixed point. We want to get nearest 20 points from a location and then display the distance.

MongoDB has built in support for geoindexing.  You don't need to do the calculation yourself.

Basically, you would create a field with the lat/long stored as an array or as sub documents, something like one of these:

{ loc : [ 50 , 30 ] } //SUGGESTED OPTION
{ loc : { x : 50 , y : 30 } }
{ loc : { lon : 40.739037, lat: 73.992964 } }

Then index the new loc field appropriately:

db.places.ensureIndex( { loc : "2d" } )

Finally you can then use one of the operators to query a point for the nearest 20 results:

db.places.find( { loc : { $near : [50,50] } } ).limit(20)

You could, of course, just use MongoDB to store the data, then pull the information out of the DB with a find() and do the calculation client-side but I imagine that is not what you want to do.

If the distance part of the equation is what you want:

http://www.mongodb.org/display/DOCS/Geospatial+Indexing#GeospatialIndexing-geoNearCommand

The $geoNear operator will return the distance also.  An example:

> db.runCommand( { geoNear : "places" , near : [50,50], num : 10 } );
{
        "ns" : "test.places",
        "near" : "1100110000001111110000001111110000001111110000001111",
        "results" : [
                {
                        "dis" : 69.29646421910687,
                        "obj" : {
                                "_id" : ObjectId("4b8bd6b93b83c574d8760280"),
                                "y" : [
                                        1,
                                        1
                                ],
                                "category" : "Coffee"
                        }
                },
                {
                        "dis" : 69.29646421910687,
                        "obj" : {
                                "_id" : ObjectId("4b8bd6b03b83c574d876027f"),
                                "y" : [
                                        1,
                                        1
                                ]
                        }
                }
        ],
        "stats" : {
                "time" : 0,
                "btreelocs" : 1,
                "btreelocs" : 1,
                "nscanned" : 2,
                "nscanned" : 2,
                "objectsLoaded" : 2,
                "objectsLoaded" : 2,
                "avgDistance" : 69.29646421910687
        },
        "ok" : 1
}

The "dis" : 69.29646421910687 elements are what you are looking for, there is also a spherical distance option.

For all this, how to use the distances, and more, take a look here for more information on geo indexes and how to use them:

http://www.mongodb.org/display/DOCS/Geospatial+Indexing/


SQL query to get the syntax of an existing table Is there any SQL query to get the syntax of an existing table in my MySql database?

Intent:
Iam in the early stages of working with databases and I am using the MySql workbench for creating tables and updating them whenever needed. At some point there are cases where I would like to have the syntax that the table is now holding so as to document it or paste it in forums like StackOverflow(expecting that I can show all the constraints in fact the foreign key constraints on the table in an easy way).

Please let me know if there is any workaround for this.

You can use SHOW CREATE TABLE <table_name>; to get CREATE TABLE statement.
  Eg. SHOW CREATE TABLE acl_user_role; This will show you the table name and CREATE TABLE statement, something like:

CREATE TABLE `acl_user_role` (
  `UserId` INT(10) UNSIGNED NOT NULL,
  `RoleId` SMALLINT(5) UNSIGNED NOT NULL,
  PRIMARY KEY (`UserId`,`RoleId`),
  KEY `FK_UserRole_Role` (`RoleId`),
  CONSTRAINT `FK_UserRole_Role` FOREIGN KEY (`RoleId`) REFERENCES `acl_role` (`RoleId`),
  CONSTRAINT `FK_UserRole_User` FOREIGN KEY (`UserId`) REFERENCES `user` (`UserId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

  If you want to know about Keys for a particular table, use something like SHOW KEYS FROM acl_user_role;

And if you want to get all keys and constraints referencing to other table (like foreign key references), use:

SELECT * FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE TABLE_NAME = '<your_table_name>' AND TABLE_SCHEMA = '<your_database_name>';

-- Example
SELECT * FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE TABLE_NAME = 'acl_user_role' AND TABLE_SCHEMA = 'db_shop';

Hope this may help you...


Select range of non-identical date from different table On MySQL5 with innoDB, I have sensor data in multiple tables, all with an ID and a DATETIME field, like so (two tables shown, but there are more). The IDs and DATETIMEs are not identical between tables, as each sensor uploads at a different rate.

temp_data:

INT      id
DATETIME ts
FLOAT    temp
FLOAT    humidity
FLOAT    lux

power_data:

INT      id
DATETIME ts
FLOAT    kW
FLOT     volts

My question is:
I need to pull all rows within a certain date range from BOTH table- in this case, the last 12 hours, so, the most recent N rows, but in the future, any date range in the past.  These will be sorted by the DATETIME value.

Using the (very good!) answer below, I get:

sourceTable    id  ts Ascending        temp    humd    lux
power   19879   2015-05-16 18:13:08 0.5 121.9   NULL
temp    19880   2015-05-16 18:13:10 76  44  1099
power   19880   2015-05-16 18:13:12 0.51    122 NULL
temp    19881   2015-05-16 18:13:13 76  44  1102

...and so on.  I need to preserve the column headers to parse this in JSON using PHP.  Is there a way to do that?  For instance, the volts/kW column headers are gone in this example.  I could write some PHP which looks for a NULL in the third position or check the sourceTable field, so I'm considering this answer correct.  However, I am interested to know if there's a way to keep the headers.

There is no connection between these two tables, so a join won't really get what you need. If everything goes into a single result set, you need to UNION them together. Note that when doing a union, you get one set of columns, and you can't mix datatypes within a column (no concern here since all your data are FLOATs). Here's an example starting point:

SELECT
  'temp' AS sourceTable,
  id,
  ts,
  temp AS measure1,
  humidity AS measure2,
  NULL AS measure3
FROM
  temp_data
WHERE
  ts >= @StartDate AND
  ts <= @EndDate
UNION ALL
SELECT
  'power' AS SourceTable,
  id,
  ts,
  lux AS Measure1,
  kw AS Measure2,
  volts AS Measure3
FROM
  power_data
WHERE
  ts >= @StartDate AND
  ts <= @EndDate


How can we use transaction in mongodb standalone connection? I want to use transaction in mongodb but its told to replicaset can we perform transaction query with standalone mongodb if yes please share how to we can because when I try its give error

This MongoDB deployment does not support retryable writes. Please add retryWrites=false to your connection string

how to we perform translation without retryable writes.

Thanks

This is not possible and clearly documented in the manual.

Ref: Retryable Writes

Retryable writes allow MongoDB drivers to automatically retry certain write operations a single time if they encounter network errors, or if they cannot find a healthy primary in the replica sets or sharded cluster.

Retryable writes have the following requirements:

Supported Deployment Topologies

Retryable writes require a replica set or sharded cluster, and do not support standalone instances.

Supported Storage Engine

Retryable writes require a storage engine supporting document-level locking, such as the WiredTiger or in-memory storage engines.


Should tempdb be larger on a secondary replica? I am working on SQL Server 2012 with Availability Groups.

On the secondary replica, the size of the tempdb datafile is roughly double that of the primary.  I am struggling to find information on whether this is normal or not?

The secondary replica is set up as a read-only replica.  All the server agent jobs check if they are executing on the primary node when they run, apart from backup jobs which only run on the secondary.  As far as I am aware we don't have any client applications that access the secondary replica directly.

Size examples (from sp_helpfile):

Primary replica - tempdev: 12259456 KB
Secondary replica - tempdev: 24828992 KB

On secondary node, when used for read-only query, SQL enable ReadCommited Snapshot behind the scene.
If you have long running transaction that does read, that could explain why you used a lot more tempdb on that secondary node.


MySQL user password vs authentication_string I run following commands:

CREATE USER 'dbuser'@'%' IDENTIFIED BY PASSWORD('mypass');
CREATE USER 'dbuserx'@'%' IDENTIFIED BY PASSWORD('mypass');
SET PASSWORD FOR 'dbuser'@'%' = PASSWORD('mypass');

And that results in:

MariaDB [(none)]> select host, user, password,authentication_string from mysql.user;
+------------------+---------+-------------------------------------------+-------------------------------------------+
| host             | user    | password                                  | authentication_string                     |
+------------------+---------+-------------------------------------------+-------------------------------------------+
| %                | dbuser  |                                           | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
| %                | dbuserx | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |                                           |
+------------------+---------+-------------------------------------------+-------------------------------------------+

Is this proper? I read there is PAM in MariaDB by default, but why user creation does not store password in authentication_string right from start?

Could someone explain the difference and possible problems with this?

Mariadb 10.3


Find SQL text from Plan Diagram in SentryOne Plan Explorer I had SSMS create an execution plan and opened it through the context menu in SentryOne Plan Explorer. I go to Top Operations, select one with huge cost and switch to Plan Diagram. The node corresponding to the option is scrolled into view.

I'm now wondering how I can find out to what part of my SQL statement this node relates to. To generate comma separated lists I use some calls to STUFF ... FOR XML PATH. I guess it's one of those that's slow. Hence it reports some [XML Reader].

How can I find out which one?

Unfortunately, there's not really a way to do this "automatically" that I'm aware of.

An execution plan operator doesn't necessarily map to something in the query text.  Some simple cases of this are functions and views.  Imagine a simple query like this:

SELECT * FROM dbo.vwOrderDetails;

The execution plan will probably have scans or seeks against dbo.Orders and dbo.OrderLineItems (whatever is in the view definition).  But there is nothing in the plan XML that indicates those scans and seeks "map back" to dbo.vwOrderDetails.

Another example would be execution plan operators that don't map easily to any specific SQL text.  For instance, a Sort operator might map to an ORDER BY clause.  But it might be a sort introduced to satisfy a MERGE JOIN.

In your specific case, your best bet is probably to look at the STUFF ... FOR XML PATH portions of your query and try to reason about which one is the node in question (maybe based on what operators are flowing into it, or based on the output list).

Another option would be to break the query up into smaller chunks that can be run separately to narrow down what is what.  You could comment out JOINs and / or items in the SELECT list until the XML Reader TVF node disappears, and then you'll know which part of the query text it's coming from.


Mysql horizontalizing grouping I have a table that essentially has rows with:

sessionId
date/time
event

event can be (to make it simple, "start" and "end")

The same sessionId however can be reused, so, simply I have

sessionId     datetime                event
AAAAA         2020-01-01 11:30:00     start
AAAAA         2020-01-01 12:20:00     end
AAAAA         2020-01-01 14:22:00     start
AAAAA         2020-01-01 14:30:00     end
BBBBB         2020-01-02 14:22:00     start
BBBBB         2020-01-02 14:42:00     end
CCCCC ...etc ...

I need to convert it to:

SessionId     StartTime              duration
AAAAA         2020-01-01 11:30:00     00:50:00
AAAAA         2020-01-01 14:22:00     00:08:00
BBBBB         2020-01-02 14:22:00     00:20:00

Is there any way I can do this with an sql query or do I have to do it in code?

Thanks in advance!

SELECT t1.sessionId, t1.dt StartTime, TIMEDIFF(t2.dt, t1.dt) duration
FROM src t1
JOIN src t2 ON t1.event = 'start'
           AND t2.event = 'end'
           AND t1.sessionId = t2.sessionId
           AND t1.dt < t2.dt
           AND NOT EXISTS ( SELECT NULL
                            FROM src t3
                            WHERE t3.sessionId = t1.sessionId
                              AND t1.dt < t3.dt
                              AND t3.dt < t2.dt );

fiddle


What is the difference between `ALTER TABLE DISABLE TRIGGER` and `DISABLE TRIGGER`? My company logs DDL on one database using a Database Trigger, looking for DDL including:

ALTER_TABLE
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER

When I disable or enable triggers using ALTER TABLE x DISABLE TRIGGER tiud_x, this change is logged with the Database Trigger.
When I disable or enable triggers using DISABLE TRIGGER tiud_x ON x, this change is not logged.
To me, DISABLE TRIGGER and ENABLE TRIGGER look like "one weird trick" to avoid firing a database trigger, since they seem to be functionally equivalent. What's the difference between ALTER TABLE DISABLE TRIGGER and DISABLE TRIGGER?

Sadly:

Disabling a trigger is not altering the trigger, just like turning off a light is not altering the lightbulb. However it is funny that it is not captured by ALTER_TRIGGER but something still manages to update modify_date when it happens. ¯\-(ツ)-/¯

Your DDL trigger catches the ALTER TABLE variation because that is explicitly altering the table - it says so in the command. While you could argue that disabling the trigger is altering the table regardless of the syntax you use (and I would agree with you, especially since the net result is the same), the difference is that this is currently a gap in what DDL triggers capture, and there is no DISABLE_TRIGGER event (you can vote for them to fix it in this feedback item on UserVoice, but that has been sitting there completely ignored for over a decade). Here is a related, understandably unanswered question on Stack Overflow.
Similarly, a long time ago I remember the ALTER_ events didn't capture RENAME, and this was added as an event later.

You'll have to catch DISABLE TRIGGER in other ways, like audit or Extended Events (I talked about this here):

When was a table trigger disabled and re-enabled?

In which case, you may as well capture the ALTER TABLE variation the same way so they're both logged in the same place.
A really dumb idea would be to poll sys.triggers every n seconds/minutes/hours and log whenever the state of is_disabled changes. This won't let you see who did it but at least you'll know that it happened and, along with other auditing, you may be able to narrow it down.


Compression function in Postgres Using Java & JDBC I am storing Strings into a JSONB column in Postgres.  When inserting this works great.

When retrieving values, I am running low on memory in Java because I am retrieving larger quantities and each json/string is about 1MB.  What I would like to do is write a query like this:

SELECT compress( myJsonColumn ) FROM myTable WHERE ...

I have looked around a bit for a compress method in Postgres, but haven't found one.  Any pointers would be appreciated.  Thanks.

Clarification: Just to be clear, I don't want to compress the data in the database.  I think that Postgres will handle this gracefully, my concern is mostly java-heap-space.

I have three components in my architecture.

Clients who need the uncompressed json (and who will handle the uncompression themselves)
A Service that serves multiple clients.  I am trying to protect the service's memory.  The service never needs to see the uncompressed json.
The DB.  I want to store the json in a jsonb field, not as binary so that I can write new queries against the data later if required.

I've recently had the occasion to use something like this:

CREATE FUNCTION gzip(text) RETURNS bytea
    LANGUAGE plperlu IMMUTABLE STRICT COST 100000 PARALLEL SAFE
    AS $_X$
    use Compress::Zlib;
    my $x = Compress::Zlib::memGzip($_[0]);
    return encode_bytea($x);
$_X$;

But note that bytea is itself transmitted in an escaped form which will enlarge the size.  With any luck, JDBC implementation might unescape them on the fly as they are read into memory, and not store the entire result set in the raw form.  Sorry, I don't use JDBC enough to know.

Can't you just use a cursor to process one row at a time and then discard it, rather than reading all rows into memory?


Why index REBUILD does not reduce index fragmentatation? I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It seems that this happens especially with small indices.

If an index is very small (I believe less than 8 pages) it will use mixed extents.  Therefore, it'll appear as if there is still fragmentation remaining, as the housing extent will contain pages from multiple indexes.

Because of this, and also the fact that in such a small index that fragmentation is typically negligable, you really should only be rebuilding indexes with a certain page threshold.  It is best practices to rebuild fragmented indexes that are a minimum of 1000 pages.