How to use keys in a table so two columns are unique I want to create a basic database that handles photos. Also, i want to show  a photo of the day to the user.
I´ve created a table called ´photo´ and another table ´photooftheday´, like so

CREATE TABLE `photo` (
`photo_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL,
`caption` VARCHAR(255) NOT NULL,
`filename` CHAR(29) NOT NULL,
PRIMARY KEY (`photo_id`)
)

CREATE TABLE `photooftheday` (
`photo_id` SMALLINT(5) UNSIGNED NOT NULL,
`date` DATE NOT NULL
)

I didn't add an auto increment column for the table photooftheday, coz I believe it’s not necessary (maybe I’m wrong).

My question is about the table ´photooftheday´. I don’t know how to handle keys so each day contains a photo that is not already selected in another day. In this case, i’m confused about primary keys, unique keys, keys, foreign keys, column indexes...

Also, if it helps, my query to select the photo of the day is going to use the date column, like:

$todays_date = date('Y-m-d');

$q = "SELECT p.photo_id, p.title, p.caption, p.filename
FROM photooftheday pd
JOIN photo p ON pd.photo_id = p.photo_id
WHERE DATE(pd.date) = '$todays_date'";

How do i handle the columns in the table photooftheday, so to optimize it?

Thanks

If I understand correctly, a given photo will have a maximum of one record in photooftheday, so you could use photo_id as the primary key of that table.  That would ensure uniqueness.

EDIT: Added based on first comment

Basically you will need one more index:

ALTER TABLE `photooftheday`
ADD UNIQUE INDEX `idx_date` (`date`);

That will ensure no two values in the column date will be the same.  As far as optimizing for the query, with the addition of the unique index you will have it covered:  You want indexes on the columns used in joins  (photo_id from each table which, as PKs, are already indexed) and in the WHERE clause (which has only date, which we just indexed).


Given two known statements, how can I replicate deadlock? I have two statements (an update against every row in one table (call it table A) and a delete on another table that looks up rows in table A) that I know are causing occasional deadlocks. It seems that there is an X lock and a U lock on the same primary key index of table A.

I have been trying, and failing, to replicate the deadlock in SQL Server Management Studio. Should I be able to?

Separately, the delete statement is very inefficient and I think I can fix the issue by creating a covering index that means that the primary key index mentioned above is no longer included in the actual execution plan of the delete statement. Given that ultimately the same rows are required by both statements will this guarantee no deadlocks or simply reduce the chance of it happening by giving SQL Server a different path to the data?

I finally managed to get a deadlock graph as shown below:

If I am reading this correctly, the process node on the right has an exclusive lock on PK_LoanFacility and is requesting another one (maybe this is because there are two updates in the statement associated with this process node. The first sets a field to NULL for all rows and the second then updates a subset with values pulled from another database). The process node on the left has an update lock on PK_LoanFacility and is requesting a shared lock. The statement on the left is deleting a single row from a child table and is finding the parent id via a WHERE clause. i.e. DELETE FROM table where ForeignID = (SELECT ID FROM ParentTable WHERE x = y). I am unsure as to why this would require an update lock on PK_LoanFacility (the PK of the parent table). I am guessing that whenever you delete a row from a table, all the foreign keys are inspected and updates to the primary key indexes of all parent tables are made..?

As previously mentioned, I am fairly sure that I can eliminate the need for the requested shared lock by adding some proper indexes, but I am still interested to understand what is happening.

Nothing can prevent deadlocks in a RDBMS. However, you can greatly reduce the chance of the occurring by following a few simple rules:

Make sure your queries are tuned well, using appropriate indexes.
If you have to take concurrent locks on multiple tables, all your statements and procedures should access those tables in the same order.
Keep your transactions as short as possible.

That will make it a lot less likely for you to encounter deadlocks. However, you cannot completely avoid them, so your code should be resilient and just automatically wait a random time interval (a few milliseconds) and then try again. Only if after three or so retries you still cant get the statement executed log the error.

Now to your question, of how to reproduce the deadlock: There are several types of deadlocks. The simplest one is the deadlock on two resources:

connection A takes lock on resource 1
connection B takes lock on resource 2
connection A tries to take conflicting lock on resource 2 and has to wait
connection B tries to take conflicting lock on resource 1 and has to wait

Now both connections wait for each other causing a deadlock. This type of deadlock can be fairly easily reproduced by stepping through the code one statement at a time on two connections. Other deadlocks are not always as easy to reproduce because e.g. they might only occur on memory pressure situations or similar.

The easiest way to get more information about deadlocks is, to set up a deadlock-graph trace. The deadlock graph contains a lot of information that can be helpful understanding the captured deadlock. As your system (hopefully) has deadlock only relatively infrequent, you might have to run the trace for a while. But if you are capturing only that one event, it won't use a lot of resources. Once you captured the deadlock graph look at it as XML. The graphical representation given by the profiler does reveal only a limited amount of information, but it is a good starting point.


Listing expired SQL logins For audit purposes, we need to list all SQL logins with their creation and expiration dates. Is there a way to script this for all of my SQL Servers, rather than performing the task manually through the UI?

To get the information for a single instance you would use DaysUntilExpiration value for the LOGINPROPERTY function. This will give you the days until the password expires. You can then cast that as an integer and use the DATEADD function to get the actual date.

SELECT name AS LoginName,
   DATEADD(DAY, CAST(LOGINPROPERTY(name, 'DaysUntilExpiration') AS int), GETDATE()) AS ExpirationDate,
   create_date
   FROM sys.server_principals
   WHERE type = 'S'

Now you can use a few different methods to get this from multiple servers:

Multiple instance connection via SSMS and Registered Servers
PowerShell using the Invoke-SqlCmd cmdlet within the SQLPS snapin. AS long as you are on a machine with SQL Server 2008 (or higher) management tools, this is available.

Examle:

$q = @"
SELECT name AS LoginName,
DATEADD(DAY, CAST(LOGINPROPERTY(name, 'DaysUntilExpiration') AS int), GETDATE()) AS ExpirationDate,
create_date
FROM sys.server_principals
WHERE type = 'S'
"@

Add-PsSnapin *SQL*

$serverList = 'Server1','Server2','Server3\InstanceName'

foreach ($s in $serverList)
{
 Invoke-SqlCmd -ServerInstance $s -Database master -query $q
}


SQL Server - Must be first statement in query batch - what and why? I cannot find a list of type of objects that while being created the command must be the first statement in query batch. So far I ve found that function/stored_procedure/view must be the first.

Does anyone know why this is required? Im sure there is a good reason, but it escapes me. The only reason for stored procedures that comes to mind is that weird interpretation of code behind END of the procedure. The SQL server still executes the code when the SP is called and had there been any more CREATE STATEMENTS the result would be mess.

Background: I m trying to create create/drop/disable/enable etc. scripts and need to know what to put into dynamic exec() for before execution. Yes I might wrap all statements in exec(), but then I would not learn anything.

From http://msdn.microsoft.com/en-us/library/ms175502(v=sql.105).aspx

  Rules for Using Batches

  The following rules apply to using batches:

  CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE
  SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined
  with other statements in a batch. The CREATE statement must start the
  batch. All other statements that follow in that batch will be
  interpreted as part of the definition of the first CREATE statement.
  A table cannot be changed and then the new columns referenced in the
  same batch.
  If an EXECUTE statement is the first statement in a batch,
  the EXECUTE keyword is not required. The EXECUTE keyword is required
  if the EXECUTE statement is not the first statement in the batch.


What things can influence log_shipping_monitor_secondary - last_restored_latency values? Looking at msdb.dbo.log_shipping_monitor_secondary last_restored_latency values for each log shipped db.
last_restored_latency - The amount of time, in minutes, that elapsed between when the log backup was created on the primary and when it was restored on the secondary.

Usually this value is 0 or fairly close to 0.  Every once in awhile see a higher number in this column (850).  What are the things to look for that can cause high values for last_restored_latency?

Most significant variable is the size of the transaction log backup that you are trying to ship, larger files :-
take longer to copy,
longer to restore &
more time to create the standby file.
If tlog backup size doesn´t explain more than 90% of the variation then you can start to troubleshoot whether server CPU or network congestion is involved.


PostgreSQL 9.3 - standard_confirming_strings option in postgresql.conf not affecting remote machine I am updating a program at work that communicates with a postgreSQL 9.3 database. I need to turn off an option in the postgresql.conf file called standard_conforming_strings, so that I can store backslashes properly in the database.

During installation, I turn off the standard_conforming_strings option in postgresql.conf automatically on the server machine. However, client installations of this program simply consist of shortcuts that point to the executable on the server machine. The client machines don't have postgres installed at all.

If data containing a backslash is stored on the server machine, the data saves correctly, meaning the standard_conforming_strings option was successfully turned off. However, when information is saved from a client machine, backslashes are stored incorrectly (as '\134' instead of '\').

Only the server machine has postgres installed, so it is the only machine with a postgresql.conf file where the standard_conforming_strings option can be turned on or off. I know this option is being disabled correctly on the server machine, but for some reason, when data is saved on the client machine, backslashes are not saved correctly, even though the data is being saved to the same database.

If both these computers are saving to the same database, they should refer to the same postgresql.conf file, shouldn't they? Data should be stored in the same manner on both machines. What is going wrong here?

Any help or suggestions would be greatly appreciated!

Edit: As requested, I've enabled logging of statements on postgres and this is what showed up in the log file when I tried to save data containing backslashes:

2014-06-02 13:44:01 PDT LOG:  statement: SELECT * FROM tblapp;
2014-06-02 13:44:38 PDT WARNING:  nonstandard use of escape in a string literal at character 135
2014-06-02 13:44:38 PDT HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
2014-06-02 13:44:38 PDT LOG:  statement: BEGIN;DELETE FROM tblapp WHERE app_key='ClientApp_ArchivePath';INSERT INTO tblapp (app_key,app_value) VALUES ('ClientApp_ArchivePath','C:\134Users\134Stephane\134Documents\134Archive');
2014-06-02 13:44:38 PDT LOG:  statement: COMMIT

The data shows up like this in pgAdmin:

ClientApp_ArchivePath | C:\Users\Stephane\Documents\Archive

Note: I did this on a machine that is saving backslashes correctly. I do not currently have access to a machine that is saving them incorrectly, but hopefully I will soon and I will go through the same process and post the results here.

I am updating a program at work that communicates with a postgreSQL 9.3 database. I need to turn off an option in the postgresql.conf file called standard_conforming_strings, so that I can store backslashes properly in the database.

These two things are completely unrelated. You may want to change the setting, but you don't need to in order to "store backslashes properly". I very strongly recommend that you do not change standard_conforming_strings - it's a backward compatibility setting that will get removed from PostgreSQL at some point in the future. Not only that, it's better to follow the standard where possible.

With standard_conforming_strings on, you can store backslashes just like anything else:

CREATE TABLE x(blah text);

SET standard_conforming_strings = on;

INSERT INTO x(blah) VALUES ('back\slash');

SELECT blah FROM x;

will produce back\slash.

If you turn standard_conforming_strings off you have to write 'back\slash' to get the same effect. The same is true for E'' strings: E'back\slash'.

  During installation, I turn off the standard_conforming_strings option in postgresql.conf automatically on the server machine. However, client installations of this program simply consist of shortcuts that point to the executable on the server machine. The client machines don't have postgres installed at all.

standard_conforming_strings is a server side setting. It can also be assigned by clients, but usually isn't, and the default is set server-side.

You don't need to mess with clients.

  If data containing a backslash is stored on the server machine, the data saves correctly, meaning the standard_conforming_strings option was successfully turned off.

Again, your first statement doesn't lead to the second at all. Your assumption is invalid.

  However, when information is saved from a client machine, backslashes are stored incorrectly (as '\134' instead of '\').

According to what client? Show the actual SQL, provide info on what the client app is, show the DDL for the table, etc.

If you're in doubt about the value of standard_conforming_strings for a session, use SHOW standard_conforming_strings to view the setting.


Why is there automatic failover for HA and manual failover for DR? I am reading this summary from the book 'Pro SQL Server 2019 Administration' from Carter:

And it specifies:
hot → automatic failover → used for high availability (HA)
warm → manual failover → used for disaster recovery (DR)
Since 'high availability' is usually planned and 'disaster recovery' is not, shouldn't we aim for manual failover in 'high availability' scenarios?
What's the point of having automatic failover for high availability?
It makes sense to me that if DR occurs, failover should be automatic, and if we have something planned (patching, etc.) and we have HA, we can do the failover manually...

It is tricky with generalizations and simplifications. However, here's an attempt:
My feeling is that HA generally is closer geographically. In such cases it is more realistic to use synchronous methods, like sync Availability Groups. In such cases, it is reasonable to failover automatically.
But when we talk DR, it is reasonable to imagine longer distances, since having some distance is part of the resilience architecture itself. And with longer distance, it is likely that you have async methods to get changes across. Like async AGs. Log shipping is by its very nature async. I.e., failover with an async solution and you lose data! Not something you want to happen "behind the scenes".


Export and Import large database from one rds of amazon to newly created amazon rds We are facing some issue. We would like to export and import mysql database "xyz" from old rds say xyz-rds to newly created rds say abc-rds (mysql).

We are approaching common method taking full export of xyz database using mysql command and which is too much of time.

Actually database size is of 45GB . from one hour we exported only 2 GB  of data so , if we calculate it will take around 20 hours to export whole database. Which will be tedious, So please suggest any solution to import and export database as much quickly as possible. Like say maximum of 2 hours.

Note: We are running old rds with 40GB RAM and new one with 20 GB RAM.

You could just use the RDS feature "Restore to point in time", which will create a full point-in-time snapshot of your current RDS instance:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html


Constraint referencing multiple tables I believe my problem is something similar to this but I am not really sure. I am not even sure my title is on topic. Using mysql.
I have the following ACL design for a website:

### tools
id
name

### tasks
id
tool_id references tools.id
name

### roles
id
tool_id references tools.id
name

### access
id
role_id references roles.id
task_id references tasks.id
has_access

Website has several tools, each tool can do several tasks. Each tool has several roles (ex: admin, mod, user) and each role is allowed to perform specific set of tasks based on access table.

Problem: how do I achieve that access.task_id belongs to the correct tool and not some random tool? For example, if we have role "editor" in tool "articles" which has tasks "delete", "edit" and "publish", how do we prevent someone inserting into access table a task id belonging to "execute_shell_script" which is from a totally different tool? So roles.tool_id=tasks.tool_id for each row.

I suspect that my design is flawed in some way so feel free to point me to a better solution.

And a more general question, when db constraints get really complicated is it generally better to do them only programatically?

What you have described is modeled as follows:

What you are attempting to do is a design flaw (logic bomb); it's called a Fan-Trap in relational database design.

To get around the logic flaw that you are bumping into, you must collect (resolve) the relation from all three tables, Tasks, Roles and Tools into a single associative entity as follows:

Each row of Access collects the relationships between Tools, Tasks and roles.

As far as complexities of constraints is concerned, I think you are confusing the implementation method with design. Good design will clarify the intent, help the developers and maintain performance. The constraints are declarative (and in the SQL standard) to keep things simple while offering verification, validation and enforcement of the design. When you get to the point where you are considering implementing your constraints in code; take a moment to reflect (can an alternate design address this issue in a more elegant way? That's the fun part of design anyway).

Sometimes it cannot (Business Rules come to mind), and the use of triggers/functions is supportable; but your job just got a LOT more complex and bug prone.


How to set full text catalog location SQL Server 2008/2012 I want to upgrade my SQL Server 2005 database to SQL Server 2012.  In SQL Server 2005 you can set the location of the full text catalog when you create it.  But in SQL Server 2012 (and in SQL Server 2008 from what I can deduce from online chatter) you cannot specify the location.

In SQL Server 2005 it was important to be able to set this location because, for performance reasons, we put the catalogs on separate disk.  I understand that the full text engine has changed starting in SQL Server 2008 but I can't find information on where the catalogs get physically created and if putting them on a separate disk is still a best practice.

How can I set the catalog location in SQL Server 2008/2012?  Or is the best practice of storing them on separate disks no longer necessary to follow?

Keith,

Full-text catalog had a complete architecture change in 2008 & above and that's why this option is removed and NOT necessary. Definitely NOT the answer you were looking for :-)

Ref: http://msdn.microsoft.com/en-us/library/ms189520(v=SQL.100).aspx

    Important
    Beginning with SQL Server 2008, a full-text catalog is a virtual object and does not belong to any filegroup. A full-text catalog is a logical concept that refers to a group of full-text indexes.

    ON FILEGROUP filegroup
    Beginning with SQL Server 2008, this clause has no effect.
    IN PATH 'rootpath'
     Note
    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
    Beginning with SQL Server 2008, this clause has no effect.


Why the backup file size is much larger than the restored database I got a 18.3 GB .bak file, when restored, the database size is about 78 MB, including logs (54MB).

restore filelistonly from  DISK = N'E:\xx.bak'

BackupSizeInBytes
23068672
0

I understand that the opposite (backup smaller than restored database) might occur if there is unused space that was allocated in the source database, but this is not the case here.

Why the backup file size is much larger than the restored database?

The size on disk is 19,200,123 KB which is about 18 GB.

23068672 bytes = 22 MB, not 20 GB.

If it were really 20 GB the only thing I could think that would make it that big would be reusing the same file for multiple backups and appending each backup to it.


Constraint to prevent overlapping Time periods I have a pretty simple table that stores a StartTime and an EndTime, both with the data type of Time(0).  I need to enforce a constraint that prevents any overlapping. 'Touching' time periods are OK, like 2pm-3pm and 3pm-4pm.  I have created a function to use for the constraint but I am having a hard time dealing with time periods that span the midnight hour.  Any help would be appreciated!

SQL Fiddle

This is driving which hours that a product is available every day.  So unfortunately I can not attach an exact date to these time periods.

If the product is always available, the StartTime and EndTime would be the same (i.e. 00:00:00 and 00:00:00). I know this is probably bad design.

The active periods are just for a 24 hour window.

The requirement for EndTime to be greater than StartTime was removed (phew!).  I have accepted the answer below, thank you!

With the 24 hour restriction you can change your function to the following:

CREATE FUNCTION [dbo].[fnIsItemGroupAvailabilityValid]
(
    @availabilityStart time(0),
    @availabilityEnd time(0)
)
RETURNS int
AS
BEGIN
    DECLARE @OverLappingRows int

    IF(@availabilityEnd > @availabilityStart)
    BEGIN
        SELECT @OverLappingRows = count(*)
        FROM [dbo].[ItemGroupAvailability] iga
        WHERE @availabilityStart < iga.AvailabilityEnd
              AND @availabilityEnd > iga.AvailabilityStart
    END
    ELSE
        SET @OverLappingRows = 1

    RETURN @OverLappingRows
END

This will insure that the times are sequential, if not it returns an @OverLappingRows = 1 as a sentinel value to tell you something went wrong. You can set it to something else like -1 if you want to specifically catch that failure case.

I removed the equals from the comparisons to drop the inclusion aspect when comparing, it should now allow time spans to butt up against each other but not overlap. Dropped the OR as it's redundant now that we're verifying the the two time stamps are sequential before running the query.

Finally, all day should be 0:00 to 23:59, not 0:00 to 0:00 otherwise you need to do a bunch of convoluted logic.


Why filtered index on IS NULL value is not used? Assume we have a table definition like this:

CREATE TABLE MyTab (
    ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
    ,GroupByColumn NVARCHAR(10) NOT NULL
    ,WhereColumn DATETIME NULL
    )

And a filtered non-clustered index like this:

CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn ON MyTab
    (GroupByColumn)
WHERE (WhereColumn IS NULL)

Why this index is not "covering" for this query:

SELECT
    GroupByColumn
    ,COUNT(*)
FROM MyTab
WHERE WhereColumn IS NULL
GROUP BY GroupByColumn

I'm getting this execution plan:

The KeyLookup is for the WhereColumn IS NULL predicate.

Here is the plan: https://www.brentozar.com/pastetheplan/?id=SJcbLHxO7

Why this index is not "covering" for this query:

No good reason.  That is a covering index for that query.

Please vote for the feeback item here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896348-filtered-index-not-used-when-is-null-and-key-looku

And as a workaround include the WhereColumn in the filtered index:

CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn
ON MyTab (GroupByColumn) include (WhereColumn)
WHERE (WhereColumn IS NULL)


What's wrong with my stored proc? I have written a basic stored procedure, which looks up the contact from one table, links with a membership, and inserts the membership plus an original field (from one table) into the new one.

Unfortunately the entity_id insert is always the same. 13226 for all of the records. I've gone through the logic and query step by step. It would seem somewhere the entity_id value isn't being updated, so the same value gets inserted.

DROP PROCEDURE IF EXISTS data_membership_insert;
TRUNCATE test_table;

  DELIMITER ;;

  CREATE PROCEDURE data_membership_insert()
  BEGIN

    -- Declare variables
    -- Must be first, else will fail
    DECLARE DONE INTEGER DEFAULT 0;
    DECLARE CONTACT_ID INT DEFAULT 0;
    DECLARE MEMBERSHIP_ID INT DEFAULT 0;
    DECLARE PRIMARY_KEY INT DEFAULT 0;
    DECLARE NOMINATOR_CONTACT_ID INT DEFAULT 0;
    DECLARE COUNT INT DEFAULT 0;

    DECLARE cursor_i CURSOR FOR (
      SELECT id FROM civicrm_value_membership_3
      WHERE nominated_by_4 IS NOT NULL
      AND entity_id IS NOT NULL
      GROUP BY entity_id
      ORDER BY id
    );

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;

    SET CONTACT_ID = null;
    SET NOMINATOR_CONTACT_ID = null;
    SET MEMBERSHIP_ID = null;
    SET PRIMARY_KEY = null;
    SET COUNT = null;

    OPEN cursor_i;
      read_loop: LOOP

        FETCH cursor_i INTO PRIMARY_KEY;

      SET CONTACT_ID = null;
      SET NOMINATOR_CONTACT_ID = null;
      SET MEMBERSHIP_ID = null;
      SET COUNT = null;

      IF DONE = 1 THEN
        LEAVE read_loop;
      END IF;

      SELECT entity_id
      FROM civicrm_value_membership_3
      WHERE id = PRIMARY_KEY
      INTO CONTACT_ID;

      SELECT id
      FROM civicrm_membership
      WHERE contact_id = CONTACT_ID
      LIMIT 1
      INTO MEMBERSHIP_ID;

      SELECT nominated_by_4
      FROM civicrm_value_membership_3
      WHERE id = PRIMARY_KEY
      LIMIT 1
      INTO NOMINATOR_CONTACT_ID;

      INSERT INTO test_table ( entity_id, nominated_by_48 )
      VALUES( MEMBERSHIP_ID, NOMINATOR_CONTACT_ID );

      END LOOP read_loop;
    CLOSE cursor_i;
    END;
    ;;

DELIMITER ;

CALL data_membership_insert();
DROP PROCEDURE IF EXISTS data_membership_insert;

Your insert statement:

INSERT INTO test_table ( entity_id, nominated_by_48 )
      VALUES( MEMBERSHIP_ID, NOMINATOR_CONTACT_ID );

It's inserting MEMBERSHIP_ID into entity_id and looking at how MEMBERSHIP_ID is fetched, problem seems to be there. If you have multiple records but limit 1 is used, it will most likely (results can be indeterminate though) pick the same value.

Possible solution is to change the query for cursor_i and select entity_id along with the ID and just use that value for insert when you loop through (no need to fetch entity_id again within the loop.


Can't start Oracle after Virtual Machine restore As part of a project for a customer of mine, they've given me the complete backup of a physical server (taken by Veeam) which I have then used to make a Bare Metal recovery onto a test VM host I have here (VMWare Workstation Pro)
That's all gone fine and windows starts up with all data intact. I'm having issues getting Oracle going though (yes, this is Oracle running on Windows)
From what I can tell, all datafiles and log files are in place. However when I try to start Oracle, this happens
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size                  2191576 bytes
Variable Size            1.0972E+10 bytes
Database Buffers         5033164800 bytes
Redo Buffers               26554368 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'F:\DB_DATA\LSTEG\SYSTEM01.DBF'
ORA-01207: file is more recent than control file - old control file

EDIT
After speaking with the customer again, they've told me that the DBF / LOG files are screwed by the backup. Their expectation is that the current database be "trashed" and the schemas to be recreated and then the actual database backup dmp files to be reimported
But how do you remove all existing schemas if you can't even start the db?

And that is why Veeam should not be used to backup a database while it is online.
Just delete the database, create a new emtpy one then proceed with the rest of the steps:
"schemas to be recreated and then the actual database backup dmp files to be reimported".


Can I change the default settings for SQL Server Replication Is there a way to change the default setting in SQL Server replication? Example when I create an new article I want to have the create Non-Cluster set to true all the time.

there is no default server configuration setting to achive this task. Replication is article based and that can done after creating replication.
you can add number of articles once by add dialog wizard and then enable property by "set properties of all table articles".

Make sure reinitialization of subscription is required after changing the property.


Administrator can't view tables ("server refused to connect") I'm quite experienced with MSSQL and MySQL/MariaDB, but I'm a newbie re PostgreSQL.
I got everything to work, except for the odd detail that the admin user which I set up during installation can't "view/edit" tables. The error displayed is:

domain.com refused to connect

Here's what works
I installed PostgreSQL 12.3 and pgAdmin4 both on a Ubuntu 20.04 LTS server (Azure VM, CLI only, no GUI). Everything works, I can log in from a browser (domain.com/pgadmin4), and create databases and tables.
This doesn't seem to work
However, when I right-click on a table (View/edit data -> first 100 rows), then I get the error message:

domain.com refused to connect.

Equally strange observation
Also, on another machine, I use Pentaho Data Integration for ETL purposes. From there, I can connect, create tables (via the automatic SQL code it generates), and I can both write and read tables, not a problem at all.
Problem
My only issue is that the admin user in pgAdmin4 cannot read tables (despite the "Administrator" role). I also turned off ufw (the Ubuntu Firewall), and any IP restrictions in the Azure account (which is effectively a second firewall). I also checked the logfile postgresql-12-main.log, but I didn't see any related entries.
I'd really appreciate if someone could give me an idea how to troubleshoot that.

I figured it out, I needed to add two lines in the ssl.conf file:
Header set Access-Control-Allow-Origin "*"
Header set Access-Control-Allow-Methods "GET, POST, PUT, DELETE, OPTIONS"
That solved it (don't forget to restart apache).


Bug in PL/pgSQL function creation I don't know if this question better suits here or in SO ...

This is a script that I'd like to launch (the code of the function was copied from a question on SO):

\c mydb

create or replace function truncate_tables(username in varchar) returns void as $$
declare
    stmt RECORD;
    statements cursor for select tablename from pg_tables where tableowner = username;
begin
    for stmt in statements loop
        execute 'truncate table ' || quote_ident(stmt.tablename) || ' CASCADE ;';
    end loop;
end;
$$ language 'plpgsql';

I get the following error:

ERROR: syntax at or near "$1"    LINE1:   $1
QUERY $1
CONTEXT: SQL statement in PL/PgSQL function "truncate_tables" near line 5

I am new to Postgres and PL/pgSQL and don't know what this error message means.

This particular example can be simpler.
You can TRUNCATE multiple tables at once. Aggregate all tablenames and execute a single statement:

CREATE OR REPLACE FUNCTION truncate_tables(_username text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE (
      SELECT 'TRUNCATE TABLE '
             || string_agg(quote_ident(t.tablename), ', ')
             || ' CASCADE;'
      FROM   pg_tables t
      WHERE  t.tableowner = _username
      AND    t.schemaname = 'public'
   );
END;
$func$ LANGUAGE plpgsql;

Call:

SELECT truncate_tables('postgres');

string_agg() requires PostgreSQL 9.0 or later.
In 8.4 you can substitute:

array-to_string(array_agg(quote_ident(t.tablename)), ', ')

For v8.3 you would write your own aggregate function - rather simple, yet not simpler than the looping solution any more.

Performance degrades when deleting or truncating many tables at once in PostgreSQL 9.1. A fix for this in the upcoming version 9.2. I quote the release notes:

  Improve performance of checkpointer's fsync-request queue when many
  tables are being dropped or truncated (Tom Lane)

Related thread at pgsql-hackers.

@Craig's related answer at SO helped me discover this.


What is the effect of number of CPU cores on update operations in Postgres? I have batch update operations being sent to the PostgreSQL server to be executed. Each operation (batch) will be updating rows in the same table but it is assured that they will be updating different rows.
Given this, is it safe to assume that the a 2 fold increase in throughput can be countered by doubling the number of CPU cores?

The additional CPUs can only be used if you use more sessions/connections.
If you increase the number of sessions, then a higher number of CPUs should show an increase in throughput. However, the throughput is not only limited by CPU, but also by the capacity of your I/O system. If the current workload is I/O bound (i.e. limited by the performance of the disks) then adding more sessions (and more CPUs) is not going to increase the throughput (it's probably making it worse).
If your disks are not saturated with the current workload, then adding CPUs and using more sessions is likely to improve the throughput. But this is typically not linear, i.e. doubling the number of CPUs and sessions might only give you an x1.5 increase in throughput. Typically this curve flattens. If you increase concurrency from 5 to 10 sessions, this will probably double your throughput (again: assuming you are not limited by I/O). But going from 100 to 200, is probably not going to double the throughput.


SQL Server instance using only a few hundred MB. Well below the minimum server memory setting I've got two SQL Server 2012 instances running on a server with 32GB of RAM and running little else.

One instance is behaving normally. Its working set memory is running close to its commit which is also close to the maximum server memory setting in SQL Server.

The other instance (highlighted in the image) is not behaving as I would expect. No matter how I configure its minimum and maximum memory settings, I cannot get its working set memory over a few hundred MB, even though there is plenty of available RAM on the server.

This is having a very detrimental effect on its performance especially under heavy load. The database is well used and when this instance was working normally* it would quickly fill up to the maximum server memory setting of 20GB.

The server has been restarted. The instances have been restarted. I've tried changing to various min/max settings and stopping the instance which is working fine - just to check it's not interfering somehow. The problem remains the same, while one instance seems to respond normally to the minimum and maximum memory settings, the other does not.

* I have no idea what's changed otherwise I'd change it back.

Both OS and SQL Server are 64 bit.
The contents of sys.configurations is identical on both instances.
The databases on the 'low' server total in the 10s of GBs.
I'm confident enough load is being put on the server that it should like to use more memory. In the past it used to fill its ram allocation quite quickly.
I've done some large queries on tables which should have pulled data into memory.
This is exclusively a database server, nothing else is running on it and it's running noticeably slower since this problem began.
sys.dm_os_sys_memory and perfmon agree with resource monitor.
Target Server Memory (KB) = 10240008; Total Server Memory (KB) = 5237744

The memory counters in Task Manager or Resource Monitor are not a good way to determine SQL Server's memory usage. If SQL Server is using locked pages, these do not show in the working set or private bytes.
There are many ways to determine if SQL Server is using locked pages. You can look for a message like the following in the error log:
Using locked pages in the memory manager
Or, check the sys.dm_os_process_memory DMV locked_page_allocations_kb column. For example, on a local test instance using locked pages I see this:

Whereas Resource Monitor shows:

Note this is different from the sys.dm_os_sys_memory DMV you mention in the question comments.
There is all sorts of other useful memory usage information in the DMVs, including high-or-low virtual or physical memory indications. These are all described in Books Online.
It seems probable that someone has granted the SeLockMemoryPrivilege to the SQL Server start-up account recently, or the account has been changed to LocalSystem, which has this privilege by default.
Generally speaking, locking pages in memory is a good thing.


How can you recursively query across 2 columns? Sorry my DBA skills are a bit limited, but here's an example schema (simplified from my real table)
create table test
(
    id          serial primary key,
    person      TEXT,
    start_point TEXT,
    end_point   TEXT
);

and here's some sample data
insert into test(person, start_point, end_point)
values ('Bob', 'a', 'b'),
       ('Bob', 'b', 'c'),
       ('Bob', 'c', 'd'),
       ('Alice', 'a', 'b'),
       ('Alice', 'b', 'c'),
       ('Fred', 'a', 'b'),
       ('Fred', 'c', 'd')
;

Notice how a particular person can go from a->b, then b->c etc.
The Question
How can I SELECT that traverses the start/end and gives me something like this as the output?
| person | place | group_id |
|--------|-------|----------|
| Bob    | a     | 1        |
| Bob    | b     | 1        |
| Bob    | c     | 1        |
| Bob    | d     | 1        |
| Alice  | a     | 2        |
| Alice  | b     | 2        |
| Alice  | c     | 2        |
| Fred   | a     | 3        |
| Fred   | b     | 3        |
| Fred   | c     | 4        |
| Fred   | d     | 4        |

Notice how Bob went thru a-b-c-d, Alice went thru a-b-c, but Fred didn't make it all the way thru so he gets split out into an a-b then a c-d, because he was missing the b-c. group_id can just be from a sequence.
What I have tried so far
What I'm currently doing, is selecting the entire dataset into a CURSOR, then doing a LOOP over it, checking if the start_point matches the end_point of the previous row, and the person matches, whilst this condition is true group them up and update a table.
When we don't find a match, assume it's for a new person and start counting again.
This is incredibly inefficient when the table grows, and I'm in a multithreaded application so I'd much rather be able to do an atomic UPDATE...SELECT rather than opening a cursor and looping through it.
Thanks

It isn't exactly clear what the group_id represents but for the traversing of the points this can be handled with a recursive query like so:
WITH RECURSIVE movement(id, person, place, end_point, level) as
(
 select id,person, start_point, end_point, 1
 from test
 union
 select p.id, c.person, c.end_point, c.end_point
  , case when p.id=c.id then p.level else p.level + 1 end
 from test c
 inner join movement p on (c.start_point=p.end_point or c.start_point=p.place)
 and c.person=p.person
), movementSum as
(
  select id,person,place,level
  from movement m
  where m.id = (select min(id) from movement t where t.person=m.person and m.place = t.place)
)

select *
from movementSum ms
order by id, place, level

Once you share more about group_id perhaps the exact sql can be produced.
Use this sql fiddle to decide what group_id should represent: http://sqlfiddle.com/#!17/cc042/2


Backup a MySQL database -options I'm relatively new to database driven sites. In order to backup a database I use phpMyAdmin's Export function from the server and download the output to my computer. That works, but it requires me to do this on a regular basis. As I build more database driven sites, the process is going to become cumbersome and tedious. I wonder if there is a better method, possibly something automated and scheduled?

For a relatively small database, the mysqldump utility can be an effective way to backup the database.

We use a shell script, execution scheduled thru cron

We run separate mysqldump for each database. We omit the "create database" and "drop table" statements. We also include options to get a "consistent" backup of the InnoDB tables. There's some housekeeping work the script does (such as getting the list of databases to backup, etc.) We run the output from mysqldump straight into a compression routine.

For each database, this is what gets run:

  $MYSQLDUMP -h localhost -u root -pTOPSECRET \
  --insert-ignore --single-transaction --quick --tz-utc \
  --routines --no-create-db --skip-add-drop-table $DB \
  | gzip -c >$BACKUPDIR/backup_$BDT.$DBF.sql.gz

Obviously, some variables are set when this gets executed:

$MYSQLDUMP is the full qualified location of the mysqldump executable
$DB is the name of the database
$BACKUPDIR is the directory, and $BDT and $DBF are parts of the file name

The generated .sql file is suitable for running into an empty database; we test restore by moving the file to a different machine, and do restore a clone of the database (into a new database name.)


Increase the time to live of a cached plan I'm looking at the Plan Cache in SQL Server. I've got a simple stored procedure:

CREATE PROC [dbo].[CountDescriptions](@Description varchar(1))
AS
    SELECT ID
    FROM MyTable
    WHERE Description = @Description
GO

If I execute it and immediately check the cache it's generally there, but it seems to expire very quickly and get removed.

This is making it a bit of a pain to play with.  Is there a way to artificially change a plan's details to stop the garbage collector (not sure if that's the right word for it) from deleting it so quickly?

Maybe increasing it's cost to compile or something?

SQL Server doesn't evict plans from the cache unless there's some sort of memory pressure, generally speaking.  Have a look here for more information:

Plan Cache Internals

This is a little out of date, but the same principles apply.


SQL Server: are backups between same major/minor version but different build compatible among them? For example a backup executed on SQL Server 10.50.6000.34 can always be restored on SQL Server 10.50.4000.0 or 10.50.2500.0. All of them indicates an SQL Server 2008 R2 (10.50.xxxx.xx) but different build (service pack 2,3 etc).

From some tests that I did seems that there are no problems but I want to know if this is always possible.

It should always be possible to restore within the same version, irrespective of service packs and other updates. Having said that, it is also recommend that you keep up with service releases so in an ideal world this won't be an issue.

One thing to note that throws some people off is that 2008 and 2008R2 are considered different versions (not one being a service release of the other) so you can't restore a backup from the latter with the former.


Counting number of ids present in one table which is not presented on another for a particular user I have two tables I want to count the number of ids presented on first which is not in second with respect to user id how can I do the same?

For example.

Case 1

Table 1.

|id|data           |User_id
|4 |Some data      |12
|5 |Another data   |8
|6 |Some other data|6

Table2

|id|table1Id|userId|
|1 |4       |12    |
|2 |5       |14    |
|3 |12      |12    |

Case 2

Table 1.

|id|data           |userid
|4 |Some data      |12
|5 |Another data   |24
|6 |Some other data|32
|7 |Some more data |12

Table2

|id|table1Id|userId|
|1 |4       |12    |
|2 |5       |14    |
|3 |12      |12    |

What I am trying to do is this.

I want to know how many occurrences of id in table1 which is not in table2  for the user 12.

How can I do that?

Case 1 it should return 0

Case2 it should return 1

You can get it by using NOT EXISTS

First example:

create table table1 (id int, data varchar(100), user_id int);
insert into table1 values (4, 'some data', 12);
insert into table1 values (5, 'another data', 8);
insert into table1 values (6, 'some another data', 6);
create table table2 (id int, tableid int, user_id int);
insert into table2 values (1, 4, 12);
insert into table2 values (2, 5, 14);
insert into table2 values (3, 12, 12);

SELECT count(*) coincidences
FROM   table1
WHERE  user_id = 12
AND    NOT EXISTS (SELECT 1
                   FROM   table2
                   WHERE  tableid = table1.id
                   AND    user_id = table1.user_id);

| coincidences |
| -----------: |
|            0 |

dbfiddle here

Second example:

create table table1 (id int, data varchar(100), user_id int);
insert into table1 values (4, 'some data', 12);
insert into table1 values (5, 'another data', 24);
insert into table1 values (6, 'some another data', 32);
insert into table1 values (7, 'some more data', 12);
create table table2 (id int, tableid int, user_id int);
insert into table2 values (1, 4, 12);
insert into table2 values (2, 5, 14);
insert into table2 values (3, 12, 12);

SELECT count(*) coincidences
FROM   table1
WHERE  user_id = 12
AND    NOT EXISTS (SELECT 1
                   FROM   table2
                   WHERE  tableid = table1.id
                   AND    user_id = table1.user_id)

| coincidences |
| -----------: |
|            1 |

dbfiddle here


SQL Server - error running 'sp_addsubscription' when not sysadmin Windows 2008 R2
MSSQLSERVER 2012 (SP1) Standard

When running sp_addsubscription under account within db_owner role of both user and distribution databases, I get the following error:

Msg 14260, Level 16, State 1, Procedure sp_MSadd_subscriber_info, Line 44
You do not have sufficient permission to run this command. Contact your system administrator.
Msg 14042, Level 16, State 1, Procedure sp_MSrepl_addsubscriber, Line 342
Could not create Subscriber.

...despite books online (https://msdn.microsoft.com/en-us/library/ms181702.aspx) stating that “…db_owner fixed database role can execute sp_addsubscription”.

sp_addsubscription appears to kick off another sys procedure called sp_MSadd_subscriber_info which requires sysadmin:

-- Security Check
    IF IS_SRVROLEMEMBER ('sysadmin') != 1
    BEGIN
        -- "You do not have sufficient permission to run this command."
        RAISERROR(14260, 16, -1)
        RETURN 1
    END

Therefore, sp_addsubscription cannot be run without sysadmin seemingly.

I've also recreated this on a Windows 2012 R2 machine running MSSQLSERVER 2012 (standard).

However, on a Windows 2012 R2 machine running MSSQLSERVER 2014, the issue does  NOT present itself.

Cannot find any information online on this. Is this a bug with earlier versions of windows / SQL does anyone know? Alternatively, am I missing something?

This is resolved by ensuring distributor_admin (or login it is replaced by) is in sysadmin server role


Unnest multiple arrays into rows I have been shown a great way to bulkinsert with this example:

WITH p AS (
    INSERT INTO parent_table (column_1)
    SELECT $1
    RETURNING id)
INSERT INTO child_table (parent_table_id, column_a)
SELECT p.id, a
FROM   p, unnest($2::text[]) AS a

However, I need to insert multiple rows from multiple arrays, so I tried this syntax:

WITH p AS (
    INSERT INTO parent_table (column_1)
    SELECT $1
    RETURNING id)
INSERT INTO child_table (parent_table_id, column_a, column_b)
SELECT p.id, a, b
FROM   p, unnest($2::text[]) AS a, unnest($3::bigint[]) AS b

I have a primary key on parent_table_id and column_a, and when I try to execute this query, Postgres complains of a duplicate key violation.

How should the arrays be unwrapped so that they form individual rows?

In other words, if $2 and $3 both have two entries, how can the first entry of $2 only be inserted with the first entry of $3 and the same for the respective second entries?

If this is not possible, can I construct a multidimensional array?  If so, how should it be passed with multiple array types, and what is the multidimensional array syntax?

This would do what you desire:

WITH p AS (
    INSERT INTO parent_table (column_1)
    SELECT $1
    RETURNING id)
INSERT INTO child_table (parent_table_id, column_a, column_b)
SELECT p.id, t.a, t.b
FROM   p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t

The subtle difference here is that unnest() calls in the same SELECT list are unnested in parallel if the number of elements is identical. Careful though: In Postgres 9.6 or older, if the number is not the same, it results in a Cartesian product instead. The behavior was sanitized in Postgres 10. See:

What is the expected behaviour for multiple set-returning functions in select clause?
Is there something like a zip() function in PostgreSQL that combines two arrays?

You could use a cleaner form with generate_subscripts() or other techniques, but those would be much more verbose. Details in this related question:

How to preserve the original order of elements in an unnested array?

Postgres 9.4

The new WITH ORDINALITY in Postgres 9.4 allows a much cleaner (and only moderately more verbose) form for this:

WITH p AS (...)
INSERT INTO child_table (...)
SELECT p.id, ta.a, tb.b
FROM   p
     , unnest($2::text[]) WITH ORDINALITY AS ta(a, rn)
JOIN   unnest($3::bigint[]) WITH ORDINALITY AS tb(b, rn) USING (rn);

And this special case can be even simpler with the new form of unnest() that accepts multiple arrays:

WITH p AS (...)
INSERT INTO child_table (...)
SELECT p.id, t.a, t.b
FROM   p, unnest($2::text[], $3::bigint[]) AS t(a, b);

Example in this related answer.


Reliability of mongodump I understand that the recommended way to backup MongoDB data is to use file system level tools. However, mongodump utility is promoted as a valid alternative for smaller instances, when file system snapshots are not available for some reason. Many resources recommend running it with --oplog option to enable point-in-time restores.

I tried to understand how --oplog could enable point-in-time snapshots without proper multi-version concurrency control from the database engine. I think it can only work in the following way:

We assume that _id values in all collections are monotonously increasing
Mongodump blocks all writes and records last operation id in the oplog
Mongodump starts dumping documents from all collections by navigating _id index in decreasing order
After dump has started, mongodump unlocks writes to the collections
We assume that all inserts from now on will have higher _id values and will appear only in oplog dump, not collections dump.

This system quickly breaks if _id values are not monotonously increasing or if write locks are not implemented and there is a race condition. Also, there is no support for secondary unique indexes.

From what I see, the following statements are true, meaning that mongodump cannot possibly enable point-in-time restores, so it's specification is misleading as it comes to its snapshot-like capabilities:

Even build-in _id values generators do not guarantee total order
mongodump does not issue any write locks
mongodump does not traverse _id index in descending order, so inserted records will appear both in collection dump and oplog dump.

Is my understanding correct? Is there any value in --oplog option of mongodump utility?

Following are the tickets which seem to enforce my suggestions:

https://jira.mongodb.org/browse/SERVER-24231
https://jira.mongodb.org/browse/TOOLS-176

Is my understanding correct? Is there any value in --oplog option of
  mongodump utility?

As MongoDB Blog documentation here Ensures that mongodump creates a dump of the database that includes a partial oplog containing operations from the duration of the mongodump operation. This oplog produces an effective point-in-time snapshot of the state of a mongod instance. To restore to a specific point-in-time backup, use the output created with this option in conjunction with mongorestore --oplogReplay.
Without --oplog, if there are write operations during the dump operation, the dump will not reflect a single moment in time. Changes made to the database during the update process can affect the output of the backup.

--oplog has no effect when running mongodump against a mongos instance to dump the entire contents of a sharded cluster. However, you can use --oplog to dump individual shards.

--oplog only works against nodes that maintain an oplog. This includes all members of a replica set, as well as master nodes in master/slave replication deployments.

--oplog does not dump the oplog collection.

  I understand that the recommended way to backup MongoDB data is to use
  file system level tools. However, mongodump utility is promoted as a
  valid alternative for smaller instances, when file system snapshots
  are not available for some reason. Many resources recommend running it
  with --oplog option to enable point-in-time restores.

You could use oplog option if you are taking backups of all the DBs.

mongodump  --oplog --out /dump_location

Now while restoring do something like this :

mongorestore --oplogReplay

This way mongodump won't require a write lock. Any writes that are made during the backup process will be written to oplog.bson. While restoring, mongorestore will use this oplog to include the write operations.

For your further ref here, here and here


You can use a variable to simulate a LAG() function, as far as I don't know how many records by month I've used a group by just to be sure.

select year, month, entry,
       if(@last_entry = 0, 0, ((entry - @last_entry) / entry) * 100) "growth rate",
       @last_entry := entry
from
      (select @last_entry := 0) x,
      (select year, month, sum(entry) entry
       from   mytable
       group by year, month) y;

This is the result:

| year | month | entry | growth rate      |
|------|-------|-------|------------------|
| 2017 | 3     | 1     | 0                |
| 2017 | 4     | 1     | 0                |
| 2017 | 5     | 2     | 50               |
| 2017 | 6     | 9     | 77,7777777777778 |

rextester here


MySQL - INSERT DELAYED <- deprecated What now? I've just been looking at the MySQL doc's https://dev.mysql.com/doc/refman/5.7/en/insert-delayed.html

I was intending to use an INSERT DELAYED, but I see that this has been deprecated.

I still need this functionality for compatibility with some PHP code that is handling some very large data transfers. I'm quite happy to update my PHP but I have no idea how to achieve the same functionality now.

How would we achieve the same outcome as traditional INSERT DELAYED now that it's deprecated?

According to the MySQL 5.5. Documentation

INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. For engines that do not support DELAYED, an error occurs.

You could downgrade to MySQL 5.5 (Of course, I am joking). I have three suggestions. Please look into them.
SUGGESTION #1
You could setup MySQL Replication and perform the following

Bulk insert into a table on the Master whose storage engine is BLACKHOLE.
The same table on the Slave would be InnoDB. Perform your SELECTs on the Slave
See my 3.5-year-old post How to do something like UPDATE DELAYED in MySQL

SUGGESTION #2
Tuning the InnoDB Environment to handle faster writes. Please see my 4.5-year-old posts How to use insert delay with the InnoDB engine and use less connection for insert statements? and Mysql load from infile stuck waiting on hard drive
SUGGESTION #3
Customize the bulk insert to use extended inserts that load a fixed number of  rows at a time like a mysqldump does.


Use Windows 2016 Storage Replica for SQL Server failover cluster instance We have two standalone servers with local storage. Based on our needs we have determined that we need to use AlwaysOn failover cluster instances.

Can we use the "Storage Replica overview" feature of Windows Server 2016 to create the failover cluster?

We also open to buy new SAN storage. Can this be possible?

Frequently Asked Questions about Storage Replica specifically mentions using it for a SQL Server FCI.  Business continuity and database recovery - SQL Server also mentions it specifically.

I've also found other non-Microsoft articles (this one, in particular) mentioning it as well.

This, along with the fact that SQL Server doesn't know or care about the underlying storage as long as it presented as cluster storage, indicates that the answer is yes, it will work.


Is there any way to get recently inserted value in a table without using aggregate functions? I tried to get the users list who are all accessed our application in past 6 months . In our table , we are storing login_time , user_id ..etc . It's a huge table approximately it will contain over 3 million rows . We splitted up every month tables as like below format TABLE_MONTH_YEAR . I am running this query in current month table only .

I tried this query .

select max(login_time) , user_id from ZmediaUserDetails group by user_id

user_id is an indexed column .

login_time is an  timestamp .

I am running this query in production setup with master/slave setup . I ran this query in a slave machine . I waited for nearly a hour . Still it was not finished. I checked the slave status using show slave status it shows Seconds_Behind_Master: 4886 . So I killed that query .

I can't create an index in either master or slave . Because , It will lock the whole table .

Please give your suggestions .

Your existing index doesn't help you out at all, since you are actually asking for the last timestamp of every possible user. This means the only way for MySQL to answer this is to do a full table scan.

As @ypercube commented, an index on (user_id, login_time) is good for this kind of query - MySQL could utilize such an index without actually going to the table.

Adding an index to a table will indeed lock the table; but there are solutions to that as well. Take a look at oak-online-alter-table or pt-online-schema-change (disclaimer: I'm author of the first), both of which are scripts which allow online table alteration via simulation, triggers and incremental copy.

You might also want to solve this in a completely different way: create a new table like

CREATE TABLE last_user_login_time (
  user_id INT UNSIGNED PRIMARY KEY,
  last_login_time DATETIME
);

And

INSERT INTO last_user_login_time
  VALUES (the_user_id, NOW())
ON DUPLICATE KEY UPDATE last_login_time=NOW()

to this table upon any new login. Thus, keeping the last login time per user at any given time. This means more writes (an additional write per login), but I suspect this table should be very small and the impact proportionally small. Reading from it, of course, is trivial.


How to create Sql Server login for a domain account? All,

I have Sql Server 2008 installed on a server (let's say Server1) in a Domain (let's say AD). I also have a domain account called AD\Sql1. This is not an Admin account on the Domain (AD), but I want it to be an admin on the Server1. And then create a login on Sql Server for that Domain account (not local account).

How do I do that?

Questions:

The user AD\Sql1, I want him as Admin on Server1. For that, do I just create a local account with the same user name (let's say, Server1\Sql1) and make the local account an Admin on the machine. Will that local account be automatically mapped to the domain account with the same name?
May be I should add here that CREATE LOGIN [AD\Sql1] FROM WINDOWS did not work for me. I tried and got an error saying user does not exist in Windows (something like that)
Now, if the above technique works. Lets assume, AD\Sql1, becomes an Admin on Sever1. So, in theory I do not need to create a Sql Login for that Windows Domain account on the Sql Server, right? All the local admins that are part of BUILTIT\Admins automatically have sysadmin access to Sql Server, right?

Where did I go wrong? Please share any URLs that could explain this concept better.

Edit: I should also add that Question 1 and 2 are important independently. I know how to add a domain/windows account as sysadmin. But I am interested in how mapping works between Domain and local accounts. My goal here is not how to add a local account as sysadmin, I can do that pretty quickly. My intention is not to be rude, but be as clear as possible. If my question is still not clear, please let me know, so I could add more details.

Thanks,
_UB

Edit: Grammar

Don't create a local account with the same name as the domain account. If you want to add a domain login as a sql admins do as follows:

create a login for the domain account: create login [AD\Sql1] from windows;
add the login to sysadmin group: exec sp_addsrvrolemember 'AD\Sql1', 'sysadmin';

Done. You would achieve the same result if you'd simply add the AD\Sql account to the local administrators via net localgroup Administrators /add AD\Sql1 (from a CMD shell) but that is not the correct solution as it grants AD\Sql1 all NT administrator privileges in addition to granting him SQL admin, which is not stated as a requirement therefore is unnecessary elevation. BTW the rule that members of the local Administrators group are SQL admins is not implicit, is an explicit privilege granted by default during SQL Setup and it can be revoked so you have to check for it.


Table size in Azure SQL Managed Instance vs On Premise SQL Server In doing a test, I have observed that the size of the same table with the same exact DDL (created from scratch) with the same exact data (10M rows) varies drastically in our Azure SQL Managed Instance vs a local installation of SQL Server: 336 MB in the managed instance vs 198 MB in the local.

The steps are the same in either scenario:
- Run the table creation script (exact same) without any primary key/index in both environments
- Load a file with 10M rows (3 columns) to the table with bcp in both environments

Does anyone have any insight as to why the managed instance takes significantly more space? (I am not worried about the actual MBs, but the ratio since this is a table that will contain billions of rows).

The Accelerated Database Recovery (ADR) feature introduced as part of SQL Server 2019 is enabled automatically on SQL Server Managed Instances.

Part of the way this feature is implemented is to include a 14 byte row versioning tag with each row.

14 bytes per row * 10,000,000 rows = 140,000,000 bytes = 133.5 MB of extra data to support ADR.

This explains the entirety of the difference in the approximate numbers provided (336 MB - 198 MB = 138 MB).

You should see the size of the SQL Server 2019 database match the Managed Instance size if you enable ADR prior to loading the 10,000,000 rows:

ALTER DATABASE [YourDatabaseName]
SET ACCELERATED_DATABASE_RECOVERY = ON;


How can I find the stored binary value of a DOUBLE value in MySQL? Given the following table, backed by MySQL 5.7/InnoDB:

CREATE TABLE mytable(mydouble DOUBLE) SELECT 0.005 `mydouble`;

how can I find what exactly InnoDB is storing on the disk for the value 0.005?

I know that this varies depending on the architecture, so I'm looking for a methodology rather than a certain value. I'm a programmer/dba, so I'm handy with any tool, if required.

A (very) poor man's strategy is to simply:

create a table with a known float value
perform a hex dump of the table ibd file
test against a hypothesis of how the value is stored

I've tested against the IEEE754 format value, but couldn't identify the value.

I've created the table adding a string value in the same record as the float, and I've actually found that the float storage format is actually IEEE754, but it's little endian.


Application testing: using multiple users in Oracle instead of mem H2. How to make it as fast as possible? I decided to stop using H2 for obvious reasons (I have Oracle on production, compatibility mode is a fake). So, I wrote simple test framework which for each test in my application does the following:

Generate random username (in example below it's test_user).
Create new user and tablespace:

create tablespace test_user_ts
datafile 'test_user_tabspace.dat'
size 10M reuse
autoextend on next 500K;

create temporary tablespace test_user_ts_tmp
tempfile 'test_user.tabspace_temp.dat'
size 10M reuse
autoextend on next 500K;

create user test_user
identified by test_password
default tablespace test_user_ts
temporary tablespace test_user_ts_tmp;

grant create session to test_user;

grant all privileges to test_user;

Populate database with test data.
Run test.
Clean up:

drop user test_user cascade;
drop tablespace test_user_ts_tmp;
drop tablespace test_user_ts;

The problem is that stages 1-3 are slow. How can I make them as fast as possible? Is there maybe any way of copying existing db schema to another one?

Db version: Oracle 11g

I have full control over Oracle instance. It runs on a vagrant image on my dev machine.

Speed up 2:

If you create the tablespace with let's say 100M and extend it with 50M or 100M, your database is much quicker than with 500k extension. This way the database keeps extending the file over and over again.
You can also use ASM which is much faster in creating files than traditional file-systems.

  EDIT1: ASM has a different structure than normal filesystems. For example crating a 32G file in ext4 takes 3-8 minutes. In ASM it just takes a fraction of a second. The reason is, in ASM you don't have to update lots of filesystem records to allocate the space. I would say in general ASM is slightly faster than other filesystems because it is optimized for Oracle databases.

  Speed up 3:

How do you populate the database with test data?

impdp: If you have EE you can load with multiple threads
insert-statement file: there is not much you can do
sqlLoader: sqlLoader is pretty fast and has some options to tune the process
INSERT INTO AS SELECT... in EE you can use parallel query, in SE you have to split it up and start multiple external threads.

Optimize 5:

Like ibre5041 mentioned you don't have to remove the tablespaces all the time. If you still want to create a new tablesapce for each test then remove the tablespace with drop tablespace test_user including contents and datafiles;. This command will remove the datafiles as well.
You can use the default temporary tablespace. You don't need a temp tbs for each user.

  EDIT1:

  Each database has a default temporary tablespace which is being used by the database users if you do not specify one. So if you configure the database as follows:

  alter database default temporary tablespace temp;

  (this is the default configuration by the way)

  You can create a user with this command: create user <username> identified by <pw> default tablespace test_user_ts quota unlimited on test_user_ts;

  From performance aspects you don't have to create multiple temp tablespaces.

In Memory (EDIT1)

  Starting with Oracle 12c you can use the In-Memory option (keep in mind it's a extra cost option). It is a completely different memory structure than the normal data buffer cache. We did some benchmarks with it and it's very very fast.

  In Oracle 11g there is no In-Memory option but this does not mean you can't optimize the database performance. I would recommend the following action plan to figure out the optimal memory settings:

  use AWR report or statspack report (depends on whether you have diagnostic pack licensed)

  check the buffer hit ratio (should be above 90%)
  or check if the  /  = x. "x" should be below 10%
  check the buffer cache advisory listings (this is not really reliable but it gives you an idea)
  check the size of the buffer cache compared to shared pool, the shared pool should not be grater than the buffer cache

  Adjust the memory parameter

  use huge pages (use_large_pages=TRUE) (the os needs to be configured for it, does not apply on win)
  if the buffer cache hit ratio is smaller than 90% increase sga size (sga_target and sga_max_size)
  if the shared pool is overextended then set a minimum size for the buffer cache to at least 60% of the sga size (db_cache_size = sga_target/100*60)
  maybe(!!!) you can use KEEP and RECYCLE pool to optimize the data buffer cache usage.


Creating and editing my.cnf by MySQL Workbench? I just installed MySQL Server and MySQL Workbench on a clean Snow Leopard MacBook. I hooked up Workbench with the server and was able to start the MySQL Server from within the Workbench. I enter the Administration menue of MySQL Workbench it tells me there is no /etc/my.cnf. And if I click on Option File on the left side in the Workbench it tells me, that it will generate a my.cnf file after hitting Apply.

Now I see many configuration possibilitys. Is there a documentation for that? The best I could find was this MySQL-Dev-Page, but it does not help much.

Even better would be an explanatory default file, which I could adapt, since I am no expert with MySQL and I only want to use python sqlalchemy and django with the MySQL Server as localhost.

Every hint is appreciated.

You many need to take a look at the comprehensive list of all Server Variables. Some of them you can change for the duration of your session, some you may change with the SET GLOBAL command, and other options may require a restart of MySQL.

Here is the list of all options. This will also show which options can be used in option files.


Execute Stored Procedure with SET LOCK_TIMEOUT Is there anyway to execute stored procedure with SET LOCK_TIMEOUT explicitly?

E.g. I have sproc called dbo.LoadData. It just takes data from table dbo.Abc and insert them in table dbo.Xyz.

Now I want to execute the sproc dbo.LoadData, but if the table dbo.Abc is locked for e.g. more than 10 seconds, stop the execution of the stored procedure.

I know the option SET LOCK_TIMEOUT can be implemented in the sproc dbo.LoadData itself, but I am wondering if there is any way how to call it explicitly (= not implementing the option in the stored procedure itself).

LOCK_TIMEOUT is not only a session-level setting, it also needs to be set in a separate batch. But, it cannot be set via a variable. So, this can be accomplished by using Dynamic SQL in the "master" Stored Procedure. This will allow for setting LOCK_TIMEOUT and then executing whatever other Stored Procedure that should run within this particular setting. It has to be a single execution of Dynamic SQL since the setting will revert back to the value of the top-most / outer-most process. For example:

Run this in one query tab in SSMS:

GO
CREATE PROCEDURE ##ShouldTimeout
AS
INSERT INTO ##LockedTable ([ID]) VALUES (1);
GO

GO
CREATE PROCEDURE ##TimeoutTest
(
    @SecondsUntilTimeout INT = 2
)
AS
SET NOCOUNT ON;

SET @SecondsUntilTimeout = ISNULL(@SecondsUntilTimeout, 2); -- enforce default

DECLARE @SQL NVARCHAR(MAX) = N'SET LOCK_TIMEOUT ';
SET @SQL += CONVERT(NVARCHAR(MAX), @SecondsUntilTimeout * 1000) + N';
';

SET @SQL += N'EXEC ##ShouldTimeout;'; -- use CASE / IF to decide what to exec

RAISERROR(@SQL, 10, 1) WITH NOWAIT; -- print Dynamic SQL in "Messages" tab

EXEC (@SQL);
GO

Run the following in another query tab in SSMS:

BEGIN TRAN;
CREATE TABLE ##LockedTable (ID INT);

-- ROLLBACK

Now go back to the first query tab (where you created the two temporary Stored Procedures) and run the following two tests:

EXEC ##TimeoutTest;
-- this will timeout after 2 seconds (the default)

EXEC ##TimeoutTest 5;
-- this will timeout after 5 seconds

Now go back to the second query tab and execute the ROLLBACK ;-).


MySQL, IF statement, variables, and NULL After struggling for too long with something that should have been simple, I had a question to ask, but while writing it, I kept looking for solutions and I finally found the solution. I am posting it here anyway in case someone else has the same problem I had.

Description: get max value of a field from database. If that value is null, make it 1, otherwise increase it by one. Insert new record with that value.

Code I was trying:

select @nextNumber := max(number)+1 from test;
if @nextNumber == NULL then @nextNumber := 1;
insert into test (number) values (@nextNumber);

Turns out, the if statement can't stand on its own, at least not here.

Will put working code as an answer.

INSERT INTO test (number)
SELECT COALESCE(MAX(number), 1)
FROM test;

UPDATE

If record(s) exists we need in the next value. So:

INSERT INTO test (number)
SELECT 1 + COALESCE(MAX(number), 0)
FROM test;


Performance issues with inherited tables and indices I have a PostgreSQL database with a master table and 2 child tables.
My master table:

CREATE TABLE test (
    id serial PRIMARY KEY,
    date timestamp without time zone
);
CREATE INDEX ON test(date);

My child tables:

CREATE TABLE test_20150812 (
    CHECK ( date >= DATE '2015-08-12' AND date < DATE '2015-08-13' )
) INHERITS (test);

CREATE TABLE test_20150811 (
    CHECK ( date >= DATE '2015-08-11' AND date < DATE '2015-08-12' )
) INHERITS (test);

CREATE INDEX ON test_20150812(date);
CREATE INDEX ON test_20150811(date);

When I execute query like:

select * from test_20150812 where date > '2015-08-12' order by date desc;

It returns very quickly (20-30 miliseconds). EXPLAIN output:

 Limit  (cost=0.00..2.69 rows=50 width=212)
   ->  Index Scan Backward using test_20150812_date_idx on test_20150812  (cost=0.00..149538.92 rows=2782286 width=212)
         Index Cond: (date > '2015-08-12 00:00:00'::timestamp without time zone)

However if I execute query like:

select * from test where date > '2015-08-12' order by date desc;

It takes a long time (10-15 seconds). EXPLAIN output:

 Limit  (cost=196687.06..196687.19 rows=50 width=212)
   ->  Sort  (cost=196687.06..203617.51 rows=2772180 width=212)
         Sort Key: public.test.date
         ->  Result  (cost=0.00..104597.24 rows=2772180 width=212)
               ->  Append  (cost=0.00..104597.24 rows=2772180 width=212)
                     ->  Seq Scan on test  (cost=0.00..0.00 rows=1 width=1857)
                           Filter: (date > '2015-08-12 00:00:00'::timestamp without time zone)
                     ->  Seq Scan on test_20150812 test  (cost=0.00..104597.24 rows=2772179 width=212)
                           Filter: (date > '2015-08-12 00:00:00'::timestamp without time zone)

constraint_exclusion is set to ON in my postgresql.conf. Therefore it should only be executed on test_20150812.

I see that, if a query is executed on master table, indices are never used. How can I improve it? I want to make all my queries on my master table. When querying for a specific date I expect no performance difference between querying on the master table or the child table.

"date"

Don't call your timestamp column "date", that's very misleading. Better yet, don't use the basic type name "date" as identifier at all, that's error-prone, leads to confusing error messages and it's a reserved word in standard SQL. Should be something like:

CREATE TABLE test (
  id serial PRIMARY KEY
, ts timestamp NOT NULL  -- also adding NOT NULL constraint
);
CREATE INDEX ON test(ts);

Caveats

Be aware of this caveat with constraint exclusion:

  Constraint exclusion only works when the query's WHERE clause contains
  constants (or externally supplied parameters). For example, a
  comparison against a non-immutable function such as CURRENT_TIMESTAMP
  cannot be optimized, since the planner cannot know which partition the
  function value might fall into at run time.

Bold epmhasis mine. You dodged this one, but with your confusing setup you might trip over it soon enough.

Also, since you have daily partitions:

  All constraints on all partitions of the master table are examined
  during constraint exclusion, so large numbers of partitions are likely
  to increase query planning time considerably. Partitioning using these
  techniques will work well with up to perhaps a hundred partitions;
  don't try to use many thousands of partitions.

Bold emphasis mine. If you are spanning more than a couple of months, try weekly or monthly partitions instead.

Mismatch in predicates

Your check condition:

CHECK ( date >= DATE '2015-08-12' AND date < DATE '2015-08-13' )

But your query has the condition:

where date > '2015-08-12' order by date desc;  -- should be: >=

This leaves a slight mismatch (probably incorrect!) and forces Postgres to recheck the condition. Not good, but also can't answer your question.

Use >=, and either make the column NOT NULL or append NULLS LAST to the statement:

WHERE ts >= '2015-08-12' ORDER BY ts DESC;

... and make the index match.

Unclean CHECK constraint

The CHECK constraints are saved with date constants instead of timestamp constants. Should be something like:

CHECK (ts >= timestamp '2015-08-11' AND ts < timestamp '2015-08-12');

Constraint exclusion

You write:

  constraint_exclusion is set to ON in my postgresql.conf. Therefore it should only be executed on test_20150812.

As you can see in the query plan, only test and test_20150812 are scanned, but not test_20150811. Ergo: constraint exclusion is working fine, despite all your deviations. That's just another wrong track.

Won many battles, but not the war

After cleaning all of this up, I see a bitmap index scan for the child table instead of your seq scan. Still slower than a query on the child table only. That's obviously due to the fact that the parent table itself can have matching rows, too, which must be sorted with the rest, so the result cannot just be read from the index.


Import and Export database in Mongo just like MySQL I am newbie to the Mongo.Before this I have worked only with MySQL.In MySQL we can import and export database easily.I want to know is there any option available in Mongo to import and export database easily.Any help and suggestions will be highly appreciable.

If you look inside mongodb/bin folder (ls -l)
You can find a couple of binaries.

The mongoDB has import and export tools.

If you're running mongod locally on the default port, you can just do:

$ ./mongodump

Example: Dumping Everything

$ ./mongodump --host prod.example.com

Example: Dumping a Single Collection

$ ./mongodump --db blog --collection posts

Others like

mongoexport, mongoimport, mongorestore, bsondump etc.

for more http://www.mongodb.org/display/DOCS/Import+Export+Tools


Normilsation - 2NF and 3NF I've been through several questions and youtube tutorials, i understand that 2NF is removing the partial dependencies and 3NF is the transitive ones, but i can't get my head around how the following example should look like in 2NF.

studentID | studentName | courseCode | courseTitle | modCode | modTitle | credits | resultCode

My attempt is the following for 2NF:

Student

studentID | studentName | courseCode | modCode | resultCode

Course

courseCode | courseTitle

Module

modCode | modTitle | credits

Is this correct? if not where am i going wrong and why.

The following is the 3NF:

Student

studentID | studentName | courseCode

Course

courseCode | courseTitle

Module

modCode | modTitle | credits | courseCode

Results

studentID | modCode | resultCode

Same goes for this, is this correct - if not where and why?

Any help would be great as i've been going over this topic for days now and still not quite sure! Thank you.

From my answer on stack overflow: https://stackoverflow.com/a/29772759/2534472
Ok, let's have a look at your 2NF attempt:

Student
studentID | studentName | courseCode | modCode | resultCode
Course
courseCode | courseTitle
Module
modCode | modTitle | credits

Let's talk about Student first.
Your key cannot be studentID, because the resultCode depends on studentID and courseCode (you have one result for every course). But your studentName depends only on studentID, so a part of the key (studentID,courseCode). So 2NF is violated. You need to do something like this:
Student
studentID | studentName | courseCode | modCode
Course
courseCode | courseTitle
Module
modCode | modTitle | credits
Result
studentID | courseCode | resultCode
But please keep in mind, that this respects the 2NF, but still seems not correct, because modules and courses are now completely unrelated. So try this:
Student
studentID | studentName | modCode
Module
modCode | modTitle | credits
Course
courseCode | courseTitle | modCode
Result
studentID | courseCode | resultCode
A course belongs to a module (a module has many courses) - from my point of view. This is automatically in 2NF (because the key is always just one attribute (except for the result, but there is just one non-key-attribute always depending on both key attributes), so nothing can depend on a "part" of the key). And it is in 3NF, too, because every "physical" entity has it's logical representation in the data model (more a rule of thumb than a formalism).

Now let's have a look at your 3NF attempt. I think you've got the module<->course dependency the wrong way round, but let's just concentrate on the normalization.

Student
studentID | studentName | courseCode
Course
courseCode | courseTitle
Module
modCode | modTitle | credits | courseCode
Results
studentID | modCode | resultCode

This is correct 3NF, because there is simply no other key candidate than the key itself. So there can't be any transitive dependency.
To clarify this: a key candidate is one of the many minimal sets of possible keys. In your relation you have found at least one key candidate with one element (except for the results relation). So any other key candidate cannot have more than one element. That means, that you can simply look at every single attribute and decide "can this be a key or not?" And in your example you find, that no other attribute can be a key - so it's automatically in 2NF and 3NF.


How can I update multiple tables at once in Postgres? I have several tables with the exact same structure and I need to update a value in all tables.

In order to do so I tried to build the following script:

DO
$do$
DECLARE
  i pg_tables%rowtype;
BEGIN
FOR i IN SELECT * FROM pg_catalog.pg_tables where schemaname like 'public' and tablename like '%_knex_migrations'
LOOP
    UPDATE i.tablename SET name = replace(name, '.js', '.ts');
END LOOP;
END
$do$;

I could see that i.tablename has the correct value (I inserted in a tmp table to check), but the update fails.

name: error
length: 223
severity: ERROR
code: 42P01
internalPosition: 8
internalQuery: UPDATE i."tablename" SET name = replace(name, '.js', '.ts')
where: PL/pgSQL function inline_code_block line 7 at SQL statement
file: parse_relation.c
line: 965
routine: parserOpenTable

Just plugin i.tablename on the UPDATE statement doesn't work.

Is there a way to make it work? Or an easier way to update all tables at once?

You're actually close.. first create some test data..

CREATE TABLE foo_knex_migrations ( name )
  AS VALUES ('test.js'),('test2.js'),('bicycles');
CREATE TABLE bar_knex_migrations AS TABLE foo_knex_migrations;
CREATE TABLE baz_knex_migrations AS TABLE foo_knex_migrations;

Next, we're going to use EXECUTE...FORMAT(), with %I.

DO
$do$
  DECLARE
    i pg_tables%rowtype;
  BEGIN
  FOR i IN SELECT * FROM pg_catalog.pg_tables where schemaname like 'public' and tablename like '%_knex_migrations'
  LOOP
    EXECUTE FORMAT(
      $$
        UPDATE %I
        SET name = replace(name, '.js', '.ts');
      $$,
      i.tablename
    );
  END LOOP;
  END
$do$;

TABLE baz_knex_migrations ;
   name
----------
 test.ts
 test2.ts
 bicycles
(3 rows)

test=# TABLE foo_knex_migrations ;
   name
----------
 test.ts
 test2.ts
 bicycles
(3 rows)

As a side note,

In general, you should be using information_schema which is standardized for simple things like this, and where speed doesn't matter.
You should probably be checking to see if the UPDATE needs to run by adding a WHERE clause. Otherwise, you're rewriting the table for nothing.
In SQL, we don't use naming conventions like this. There is no need for that. If knex_migrations has multiple tables, consider CREATE SCHEMA knex_migrations to store them, rather than searing through the catalog based on a naming convention for all tables.


How do I count rows with two properties in one index scan in SQL? I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it.

I need to output the following: for each day I need to count separately

items that were created in that day and have zero ItemUnitsCount
items that were created in that day and have nonzero ItemUnitsCount

and output the count and the date and on any day there can be any number of item of both kinds, maybe none. Each kind of items should be reported separately, so for any day I can have no rows, one row or two rows.

So I crafted the following query:

(SELECT ItemCreationDate, COUNT(ItemId) AS ComputedCount, 1 AS CountType
   FROM Items WHERE ItemUnitsCount<>0 GROUP BY ItemCreationDate )
UNION ALL
(SELECT ItemCreationDate, COUNT(ItemId) AS ComputedCount, 2 AS CountType
   FROM Items WHERE ItemUnitsCount=0 GROUP BY ItemCreationDate )

and this works okay, but when I looks at the actual execution plan I see two clustered index scans one of which has predicate ItemUnitsCount=0 and another has predicate ItemUnitsCount<>0.

ItemUnitsCount can be changed several times over lifetime of the table row, so I'd rather not build an index for it.

Is there a way to build a query with the same effect that results in one index scan instead of two?

SELECT
    ItemCreationDate
  , SUM(CASE WHEN ItemUnitsCount <> 0 THEN 1 ELSE 0 END) AS CountType1
  , SUM(CASE WHEN ItemUnitsCount = 0 THEN 1 ELSE 0 END) AS CountType2
FROM
    dbo.Items
GROUP BY
    ItemCreationDate


Fix for orphan user 'guest'? What can be done, if anything, when the special guest user is orphaned (not linked to any login)?

For one of my databases (SQL Server 2005), executing the following lists the guest user as an orphaned user.

exec sp_change_users_login 'report'

Results:

UserName    UserSID
guest       0x3C2E66759FFBC14F84127D6795C27FD3

If I try to fix the guest user using that procedure, I get the following:

exec sp_change_users_login 'update_one', 'guest', 'guest'

  Terminating this procedure. 'guest' is a forbidden value for the
  login name parameter in this procedure.

If I try to delete the user, I get:

  User 'guest' cannot be dropped, it can only be disabled.

select * from sys.database_principals where name = 'guest'

Results in:

name                 guest
principal_id         2
type                 S
type_desc            SQL_USER
default_schema_name  guest
create_date          11/13/98 2:58 AM
modify_date          10/16/01 4:31 PM
owning_principal_id  NULL
sid                  0x3C2E66759FFBC14F84127D6795C27FD3
is_fixed_role        0

The database seems to be confused as to whether this is a special user or not. Is there anything that can be done?

The user "guest" is never assigned to a server login, even on a fresh install it is classified as a SQL user without a login. As you can only set the SID of a login (upon creation), and not a user I don't believe that this is possible; sp_change_users_login does not work precisely for the reason that the guest account should never be mapped to a server login. As a result, the user "guest" is always an orphaned user. Probably not the answer you wanted though :)


get record between two dates I want to fetch record where start_date is 01/01/2017 and to_date is 02/25/2018 problem is it cannot show id 2 record

date store in varchar MM/DD/YY

This is my query:

SELECT *
FROM `new_item`
WHERE  `from_date` >= '01/01/2017'
  AND `to_date` <= '02/25/2018'

id 2 record not shown

  id   from_date     to_date
   1   01/01/2017    02/05/2018
   2   01/20/2017    06/20/2017
   3   02/05/2018    02/25/2018
   4   02/02/2018    02/20/2018

The problem:

  date store in varchar MM/DD/YY

That is your problem. You are storing dates in character columns. Even worse, in a non-aligned format.

Solution:

Use a type that suits your data. You are storing dates, use the DATE type.

Then you won't have to decide or worry about the internal format that dates are stored. You'll only have to provide date literals in a format that MySQL accepts (eg. ISO: YYYY-MM-DD). Your query would then work just fine:

SELECT *
FROM new_item
WHERE from_date >= '2017-01-01'
  AND   to_date <= '2018-02-25' ;


I Need Help Setting Up Roles and Passwords in PostgreSQL I'm using the postgres.app on my Mac Mini Server running version 9.4.0 using El Capitan. I have not done any customization in pg-hba.conf. I do not have a .pgpass file. I believe that .pgpass is used to store passwords where you would not have to enter them when accessing a database instance. I really need more detailed understanding on the Unix terminology being used in pg-hba.conf.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

For my Ruby on Rails projects having roles with passwords with different types of access will be sufficient. From what I can tell everything I want would be considered local.

I submitted the two questions below in Stack Overflow last year and tried to implement them but to no avail. I ended up either locking myself out of PostgesSQL or with no passwords working. At that time I was using the built-in PostgreSQL instance on my Mac Mini Server.

https://stackoverflow.com/questions/22882280/postgresql-9-2-4-desire-to-change-the-localhost-server-password-in-mavericks

https://stackoverflow.com/questions/25167284/rails-4-desire-to-set-postgresql-password-access-database-in-rails

I want to add an encrypted password to my current role. I also want to add two more roles with encrypted passwords to use in my Ruby on Rails applications hosted on my Mac Mini server, one with read only access and the other with read/write access.

I assume I would execute the following commands in the PostgreSQL terminal or in pgAdmin.

CREATE USER readonly WITH ENCRYPTED PASSWORD 'readonlypasswordencryptedtext';
CREATE USER readwrite WITH ENCRYPTED PASSWORD 'readwritepasswordencryptedtext';
GRANT SELECT PRIVILEGES ON ALL TABLES IN public TO readonly, readwrite;
GRANT INSERT, UPDATE, DELETE PRIVILEGES ON TABLE table1, table2, table3, etc. TO readwrite;
ALTER ROLE myuserid WITH ENCRYPTED PASSWORD 'myuseridpasswordencryptedtext';

I would like help on how to change pg-hba.conf and in what order so I don't lock out myuserid AND if there are additional things I need to consider.

I have multiple databases with tables. I don't think my GRANT statements take that into consideration. Would I need to create a schema to include all my databases for the readonly user and the databases to update for the readwrite user?

Basic Role and User Management

By default, PostgreSQL stores encrypted passwords:

create user u_one with password 'password';

select *
from pg_authid
where rolname = 'u_one';

-[ RECORD 1 ]--+------------------------------------
rolname        | u_one
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcatupdate   | f
rolcanlogin    | t
rolreplication | f
rolconnlimit   | -1
rolpassword    | md54e83b97ec0bf59917d9d2689a961e390
rolvaliduntil  |

for some testing:

create table tt (
    a serial primary key,
    v varchar
);

insert into tt (v)
values ('one'), ('two');

the goal is to create one user who has read-only access and one user who has all privileges. It is better to create roles that have the privileges defined and then attach users to each of those roles:

create role readonly;
create role readwrite;

grant select on all tables in schema public to readonly;
grant insert, update, delete on table tt to readwrite;

In my example, I would like u_one to have read-only access. This user does not yet:

select *
from tt;
ERROR:  permission denied for relation tt

I'm going to attach u_one to read-only:

grant readonly to u_one;

And now u_one can select from table tt.

Now I'll create a u_two who is readwrite:

create user u_two with password 'password';
grant readwrite to u_two;

you probably meant to allow u_two select privileges as well:

grant readonly to u_two;

When you attempt to insert into tt using u_two:

insert into tt (v) values ('three');
ERROR:  permission denied for sequence tt_a_seq

You need to add insert, update, and/or delete privileges to the users who are going to modify this sequence:

grant usage on tt_a_seq to readwrite;

Notice how I only added this to the role, and not the user: u_two is  now able to insert a new record.

Using this role / user strategy will give you considerable flexibility by reducing duplicate permissions and allowing you to quickly create and set up new users.

The HBA config file:

By default, all users will be able to login from localhost, and as long as that is true, you won't have to make changes to pg_hba.conf. You will need to make changes if you want a user to be able to access the database from another ip-address (remote computer).

If you are going to grant remote access:

DATABASE: host --
USER: the username
ADDRESS: the ip address the user is connecting from
(I doubt this will work with roles since roles do not have passwords,
 though I've never tried either)
(if you want the user to be able to connect from any IP, then use
0.0.0.0/0)
METHOD: md5

PostgreSQL docs on creating roles and user syntax.
http://www.postgresql.org/docs/current/static/sql-grant.html

Granting privileges to sequences:
https://stackoverflow.com/questions/9325017/error-permission-denied-for-sequence-cities-id-seq-using-postgres

PostgreSQL docs on hba:
http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html


Table design: how to store denormalized Excel data I have following data stored in Excel which I would like to transfer to SQL DB. There will be a new value every year for every indicator.

Indicator | 2001 | 2002 | 2003
-------------------------------
AACA      |    10|    20|    30
bbb       |   100|   200|   100
ccc       |    10|   300|   300

Does below project of SQL table follow SQL design rules?

+----+-----------+------+-------+
| Id | Indicator | Year | Value |
+----+-----------+------+-------+
|  1 | AACA      | 2001 |    10 |
|  2 | bbb       | 2001 |   100 |
|  3 | ccc       | 2001 |    10 |
|  4 | AACA      | 2002 |    20 |
|  5 | bbb       | 2002 |   200 |
|  6 | ccc       | 2002 |   300 |
|  7 | AACA      | 2003 |    30 |
|  8 | bbb       | 2003 |   100 |
|  9 | ccc       | 2003 |   300 |

You will want to add an alternate key/unique constraint upon the combination of Indicator and Year. By virtue of the value being stored at the intersection of Indicator and Year in the spreadsheet, you are only allowed a single value. The new table design would allow you to have as many entries for a given indicator and year unless you explicitly disallow that.

DDL is approximately

CREATE TABLE dbo.MyTable
(
    Id int IDENTITY(1,1)
,   Indicator varchar(10) NOT NULL
,   Year smallint NOT NULL
,   Value int NOT NULL
,   CONSTRAINT PK_MyTable PRIMARY KEY
(
    Id
)
,   CONSTRAINT AK_MyTable_Indicator_Year UNIQUE
(
    Indicator
,   Year
)
);


Which is faster among MySQL Community Server, Percona and MariaDB I have a small setup of MySQL Community Server version 5.6 running on KVM based VPS. 4 CPU cores are available and total memory is limited to 1GB. I use MyISAM tables mainly for Wordpress and few other personal projects.

Which server can best suit my requirements among MySQL Community Server(all 5.x versions), Percona and MariaDB? I am basically looking for better performance (mainly read) and resource utilization.

I recently ran into this benchmark results:

https://blog.mariadb.org/benchmarking-mariadb-5-3-4/

The conclusion here was that MySQL-5.5 is the mos performing among others.

The tested editions here are :

MariaDB-5.3.4 – the Monty Program release candidate, both with XtraDB
and the InnoDB plugin
Percona-Server 5.1.61 because it is based on the same XtraDB
version as MariaDB-5.3
Percona-Server 5.5.20 – the current Percona flagship
MySQL-5.5.20 – the current Oracle flagship

Hope i helped


SSIS in Clustered Enterprise We have a 2-node clustered SQL Server 2014, Enterprise, environment for our data warehouse. Storage, network, and servers are all high performing (1tb mem, 32 cores CPU per node, all SSD)

We are trying to come up with where to put SSIS itself. Based on Microsoft documentation, it isn't recommended that it be clustered and, therefore, shouldn't be on the 2 nodes (though I did install it with the instances, we don't have to use if from there can could disable the services.) On the flip side, we don't really want to spend money on licensing of 8 vCPU's to put it on an available server in the same ecosystem (used for a proprietary ETL from vendor.)

What are the best practices and recommendations on how/where to install SSIS for a clustered SQL Server database environment?

Thank you,
Wes

EDIT: Microsoft discusses this here: https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-ssis-in-a-cluster but does not go over the recommended approach as an alternative.

The SSIS Catalog and SQL Server Agent are both part of the Failover Cluster Instance, and failed-over when a Failover Cluster Instance fails-over.  So my recommendation is to store all your packages in the SSIS catalog and schedule them all with SQL Agent.  And simply ignore the SSIS Service completely.

See
"The topics in this section discuss the Integration Services service, a Windows service for managing Integration Services packages. This service is not required to create, save, and run Integration Services packages. SQL Server 2012 supports the Integration Services service for backward compatibility with earlier releases of Integration Services. "
https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service