Scalar Operator in Seek Predicate and a Row Estimate of 1 I'm trying to understand some behavior I'm seeing in a query plan that's rather large and unruly.  In particular, I'm looking at a Clustered Index Seek Operation containing a Scalar Operation within its Predicate.  I suspect the Scalar operation is simply the aliasing of one of the tables (as described in Scalar Operator in Seek Predicate) as both columns are of the same type and this operation feeds into a Parallel Nested Loops (Left Outer Join) operator.

My question though is more about the Row Estimate coming in at 1 instead of a number closer to the Actual Number of Rows (~6.7 million).  Is the Scalar Operation killing the optimizer's ability to estimate the rows properly?  I assume so and also assume this is hurting my query execution plan from being optimal, but I don't really know for certain.  Can someone confirm or refute my suspicions along with why?

Here's the operation in question:

Version: SQL Server 2012 Enterprise

On the inner side of a nested loop join, the estimated number of rows represents an estimate per iteration of the loop. The actual number of rows is the total number of rows returned for all iterations of the join. This is unintuitive and a common source of confusion.

We can see this action with a simple demo:

CREATE TABLE dbo.X_OUTER_TABLE (ID INT NOT NULL);

INSERT INTO dbo.X_OUTER_TABLE WITH (TABLOCK)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

CREATE TABLE dbo.X_INNER_TABLE (ID INT NOT NULL, PRIMARY KEY (ID));

INSERT INTO dbo.X_INNER_TABLE WITH (TABLOCK)
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

Detailed information for the clustered index seek against X_INNER_TABLE:

The estimated number of rows is 1 because the seek is performed against the full primary key of the table. The actual number of rows is 100 because the outer table has 100 rows, the loop iterates 100 times, and 1 row is found in each loop.

A similar scalar operator shows up in the simple demo as you observed in your query plan. There isn't a lot to go on in your original question, but it's likely that the scalar is indeed a red herring. Instead, I would focus on the difference between the estimated number of executions (580308) and the actual number of executions (6740779). SQL Server estimated that the outer table would have 10% of the rows that it actually had, so the nested loop is costed inappropriately low. It's possible that if the optimizer had a more accurate cardinality estimate for the outer table it would have switched from a nested loop join against this table to a different plan.


How to add 6 hours on MSSQL with format I am currently using

  SELECT FORMAT (getdate(), 'MM/dd/yyyy, hh:mm:ss tt') as date

The output is in the format I need, but is off by 6 hours.

  05/30/2019, 06:26:01 PM

I just need to add 6 hours to it. been using dateadd, but I keep loosing the format I need it to be on.

  SELECT DATEADD(Hours,6,GETDATE()) outputs in wrong format

Declare @dt datetime ;
select @dt=  DATEADD(Hour,6,GETDATE())
SELECT FORMAT (@dt, 'MM/dd/yyyy, hh:mm:ss tt') as date


Why are my Log Ship Jobs Blocking Each Other I have 5 databases at a DR site all being log shipped to once a minute using the built in SQL Server log ship jobs. The server is running SQL Server 2017.

For the last week the log shipping keeps going down due to blocking, when I look at the blocked queries there are several queries all deleting from msdb.dbo.log_shipping_monitor_error_detail and they're all blocking each other...

If I add get_locks=1 to sp_whoisactive they all look very similar...

Session 104...

<Database name="msdb">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="log_shipping_monitor_error_detail" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="21" />
        <Lock resource_type="PAGE" page_type="*" index_name="nc3lsmonitor_error_detail" request_mode="U" request_status="GRANT" request_count="4" />
        <Lock resource_type="PAGE" page_type="*" index_name="nc3lsmonitor_error_detail" request_mode="U" request_status="WAIT" request_count="2" />
      </Locks>
    </Object>
  </Objects>
</Database>

Session 109...

<Database name="msdb">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="log_shipping_monitor_error_detail" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="21" />
        <Lock resource_type="PAGE" page_type="*" index_name="nc3lsmonitor_error_detail" request_mode="U" request_status="GRANT" request_count="3" />
        <Lock resource_type="PAGE" page_type="*" index_name="nc3lsmonitor_error_detail" request_mode="U" request_status="WAIT" request_count="2" />
      </Locks>
    </Object>
  </Objects>
</Database>

The queries never unblock and never get registered as deadlocks, at the minute when I see it happen I have to kill the connections then it goes back to working again for anything from a few minutes to a few days when it will eventually get stuck again. The table in question that its trying to delete from only has about a thousand rows in it.

At this point I'm running out of ideas.

Edit : Complete version details from @@Version are ....

  Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2
  (X64)   Jun 15 2019 00:45:05   Copyright (C) 2017 Microsoft
  Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard
  10.0  (Build 14393: ) (Hypervisor)

What I think you can try here is to change the run times of each of your log shipping jobs.
If you have 5 LS backup jobs running at the same time, change to time to 1 minute/2 minutes apart, the same to the LS restores and copy jobs.


Joining INTO many-to-many table (From Primary Key Table) This is how far i have gotten, though i don't think it can be done with 1 SQL-statement, i just want to confirm whether or not it is possible to do this with ONLY 1 statement:

SELECT * FROM users
INNER JOIN users_mentors ON users_mentors.id=users.mentoruser_id
INNER JOIN mentor_types ON (mentor_types.id=users_mentors.mentor_type OR users_mentors.mentor_type IS NULL)
INNER JOIN mentor_geographies ON mentor_geographies.mentor_id=users_mentors.id
INNER JOIN communes ON communes.id=mentor_geographies.commune_id
LIMIT 0,10

users table with foreignkey to users_mentors:

+------+---------+---------------+
| id   | user_id | mentoruser_id |
+------+---------+---------------+
| 1886 |    NULL |             4 |
| 1885 |    NULL |          NULL |
| 1884 |    NULL |          NULL |
| 1883 |    NULL |          NULL |
| 1882 |    NULL |          NULL |
+------+---------+---------------+

users_mentors table (in a many-to-many relationship with communes):

+----+-------------+
| id | mentor_type |
+----+-------------+
|  4 |        NULL |
|  1 |           1 |
|  2 |           2 |
|  3 |           3 |
+----+-------------+

communes table (in a many-to-many relationship with users_mentors):

+----+--------------+-------+----------+
| id | name         | short | contract |
+----+--------------+-------+----------+
|  1 | KÃ¸benhavn   | NULL  |        0 |
|  2 | Aarhus       | NULL  |        0 |
|  3 | Aalborg      | NULL  |        0 |
|  4 | Odense       | NULL  |        0 |
|  5 | Esbjerg      | NULL  |        0 |
+----+--------------+-------+----------+

mentor_geographies table (the m2m table that has FK to communes & users_mentors):

+----+-----------+------------+
| id | mentor_id | commune_id |
+----+-----------+------------+
|  1 |         4 |          1 |
|  2 |         4 |          2 |
+----+-----------+------------+

So my question is:

Is it possible to get all rows from users_mentors and a list of all their commune.type's, IF THEY EXIST (if mentor_geographies is empty, i want empty list of commune.type). In all cases i want the user.

http://sqlfiddle.com/#!9/33b19d/3 ...

as I said in the comments - use left join instead of inner join ...

your diagram misses the mentor_types ... so I commented it out.

of course you will get 2 lines for user 1886

For your question regarding all lines of user_mentors - no its impossible if you start building the select with the users table. And as there is no mentoruser_id that points to other lines than the id==4 line in users_mentors.


Why straight SQL query run faster than stored procedure? I am facing-out a very surprising problem in SQL Server.

The problem is when the query run in SSMS window it will return result in ~1 sec and when the query run through a stored procedure it will execute in ~57 sec.

For example :

DECLARE @ip_RequestId As Int
SET @ip_RequestId = 3287

SELECT
   Tbl1.SurveyResponseId,
   Tbl1.ReportLabelID,
   Tbl1.EntityId AS FeedbackByEntityId,
   (SELECT Candidate + ' ' + LastName
    FROM Tbl2
    WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName,
   (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3 WHERE MultiRaterId = @ip_RequestId), '')) As ReportTitle
FROM Tbl1

When I run the above query in SSMS as a query, it execute in ~1 seconds.

But after creating the stored procedure:

CREATE PROCEDURE [dbo].[spGetResponse]
   @ip_RequestId As Int
AS
BEGIN
    SELECT
       Tbl1.SurveyResponseId,
       Tbl1.ReportLabelID,
       Tbl1.EntityId AS FeedbackByEntityId,
       (SELECT Candidate + ' ' + LastName
        FROM Tbl2
        WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName,
       (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3
                       WHERE MultiRaterId = @ip_RequestId), '')) As ReportTitle
    FROM Tbl1
END

When I execute the above stored procedure with same parameter value (i.e @ip_RequestId = 3287) in SSMS window (exec spGetResponse 3287), it executes in ~57 seconds.

After some googling, I found it's because of "parameter sniffing". But I am not really understand the "parameter sniffing".

**Updated: One more thing **

When the Sp run with search query, i will also fast for me. Means if I convert the SP following format, this will also return result in ~1 sec:

CREATE PROCEDURE [dbo].[spGetResponse]
    @ip_RequestId As Int
AS
BEGIN
     DECLARE @strQuery As VARCHAR(MAX)
     SET @strQuery = '(SELECT Tbl1.SurveyResponseId, Tbl1.ReportLabelID, Tbl1.EntityId AS FeedbackByEntityId, (SELECT Candidate + '' '' + LastName FROM Tbl2 WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName, (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3 WHERE MultiRaterId = ' + @ip_RequestId + '), '''')) As ReportTitle FROM Tbl1)'
     EXECUTE (@strQuery)
END

When I execute the above stored procedure with same parameter value (i.e @ip_RequestId = 3287) in SSMS window (exec spGetResponse 3287), it executes in ~1 seconds.

Is there any solution exist to overcome the "parameter sniffing" problem?

Assign the value of parameter to the local variable in the procedure then used that variable.


Is there a way to achieve Workload Management in MySQL? Is there a way to set up Workload Management in MySQL (in particular v5.5 on AWS RDS)?

By Workload Management I mean the functionality implemented in e.g. Teradata, RedShift that allows prioritization of queries. In particular, I want to give high priority to tactical, short running queries from applications and give low priority to longer running background queries.

Other than outdated techniques like obtaining exclusive locks on tables and tactics like SELECT HIGH_PRIORITY and INSERT DELAYED -- which are either deprecated or otherwise not useful (especially with InnoDB) -- MySQL (including RDS) does not have any mechanisms for prioritization like this.

Depending on the nature of your "longer running background queries" -- if they are reads, not writes, such as for reporting purposes -- the usual approach is to deploy a read replica.  In RDS for MySQL 5.5, the replica server has to be another RDS instance.  This is different than "Multi-A/Z" RDS, where you don't have access to the standby server, and switchover to the standby is automatic.

In addition to offloading heavy read loads, having a read replica is still a viable disaster recovery tool.  If you were to have a catastrophic outage of your production RDS instance, an RDS read replica can be manually converted to a standalone (master) instance, and you can migrate your application to it -- it will have the most up to date version of your production data possible -- nothing that it received up until the original master failed should be lost.

Standard replication in MySQL is asynchronous -- the replica is not precisely in lock-step-sync with the master, but on a system that is sufficient for the workload offered, the delay is usually a matter of milliseconds.  The master sends the changes to the replica almost instantaneously, and the replica -- which started out with an identical data set to the master -- immediately stores a copy of the raw change instructions to disk in the "relay log," and then applies those changes to its own working data set as fast as its disk, memory, and CPU will permit... in a 2-step process.  An overloaded replica server's working data set may lag behind the master, but if the master has a catastrophic failure preceded by a period of normal operating conditions, then the replica will have already received the change events and once it catches up on playing back and applying the stream, its working data set will be identical or as near as possible to identical to what existed on the master at the moment the master failed.

New, in RDS for MySQL 5.6, you can actually deploy a read replica on a MySQL instance that is external to RDS, either in an EC2 instance or at your own data center, and have a live copy of your data that's independent of RDS.  Officially, a non-RDS replica is documented as being intended for short term migration purposes, but it does not appear to be actually limited to this application.  The availability of this feature was the main reason I dropped my resistance to allowing some of my corporate services to use RDS.

Operating in a production environment without at least one live replica as a standby in case of failure of the master is, to me, unthinkable, and the ability to run the reporting queries with zero impact on the master is, at minimum, a bonus.  Every production server I have has at least one and sometimes more than one replica.

I don't know if this addresses your needs directly, but I would recommend having one at any rate.

ETA: A follow-up thought that occurred to me as something worth mentioning is that while a read replica generally has identical table definitions to those on the master, it is possible to declare the indexes on those tables as differently as your needs require.  On one of my systems, I have a multi-million-row table where the master rarely does anything to more than one row at a time, but the replica does extensive sorting and ordering and group-by, so the replica has substantially more indexes on the same table than the master has.  This gives me the luxury and flexibility of what are arguably almost excessive indexes, but without the master having to spend the necessary time maintaining all of those different indexes as it's processing live transactions.  If these same indexes had to be defined on the master server, they could add up to unjustifiable overhead... not to mention that I can add additional indexes to an table on the replica at any time, without impacting the live system.  In most cases, I do the majority of backups from replicas as well.


Trying to return empty rows even if data does not exsist in SQL I am trying to return the last changed time for a list of users, but i would like to return a row even if the a last changed time does not exist

select  user_id,
        last_changed_time = MAX(ISNULL(change_date_time,0))
from audit_log_tx
where user_id in (users)
group by user_id
order by user_id asc

SELECT u.user_id,
  column_name = COALESCE(MAX(change_date_time), '19000101')
  FROM dbo.users AS u
  LEFT OUTER JOIN dbo.audit_log_tx AS o
  ON u.user_id = o.user_id
  GROUP BY u.user_id;

Though I really don't like the use of token dates for missing data (especially 0).


Unable to create a Filtered Index on a Computed Column In a previous question of mine, Is it a good idea to disable lock escalation while adding new calculated columns to a table?, I am creating a computed column:

ALTER TABLE dbo.tblBGiftVoucherItem
ADD isUsGift AS CAST
(
    ISNULL(
        CASE WHEN sintMarketID = 2
            AND strType = 'CARD'
            AND strTier1 LIKE 'GG%'
        THEN 1
        ELSE 0
        END
    , 0)
    AS BIT
) PERSISTED;

The calculated column is PERSISTED, and according to computed_column_definition (Transact-SQL):

  PERSISTED

  Specifies that the Database Engine will physically store the computed
  values in the table, and update the values when any other columns on
  which the computed column depends are updated. Marking a computed
  column as PERSISTED allows an index to be created on a computed column
  that is deterministic, but not precise. For more information, see
  Indexes on Computed Columns. Any computed columns used as partitioning
  columns of a partitioned table must be explicitly marked PERSISTED.
  computed_column_expression must be deterministic when PERSISTED is
  specified.

But when I try to create an index on my column I get the following error:

CREATE INDEX FIX_tblBGiftVoucherItem_incl
ON dbo.tblBGiftVoucherItem (strItemNo)
INCLUDE (strTier3)
WHERE isUsGift = 1;

  Filtered index 'FIX_tblBGiftVoucherItem_incl' cannot be created on
  table 'dbo.tblBGiftVoucherItem' because the column 'isUsGift' in the
  filter expression is a computed column.   Rewrite the filter
  expression so that it does not include this column.

How can I create a filtered index on a computed column?

or

Is there an alternative solution?

Unfortunately as of SQL Server 2014, there is no ability to create a Filtered Index where the Filter is on a Computed Column (regardless of whether or not it is persisted).

There has been a Connect Item open since 2009, so please go ahead and vote for it. Maybe Microsoft will fix this one day.

Aaron Bertrand has an article that covers a number of other issues with Filtered Indexes.


Duplicate error when update column in the same table I am getting error code Error Code: 1062 Duplicate Entry for KEY Primary.

The Procedure is suppose to increment workshift by 1 day each week. Ex, 6001 is Monday shift, 6002 Tuesday and so on each time it's called.

I know i'm getting the error due to as soon as 6001 == 6002 in first if , then there is 2 6002 with same PK. Is there anyway to fix this?

Here is current table.

roster
 employee  branch  workShift
   0001      01       6001
   0001      01       6002
   0001      01       6003
   0001      01       6004

Table after call to update roster:

roster
 employee  branch  workShift
   0001      01       6002
   0001      01       6003
   0001      01       6004
   0001      01       6005

CREATE PROCEDURE `UpdateRoster`(in employeeid char(8), in BranchID int(11))
BEGIN
 DECLARE hours_max INT(35);
 DECLARE fin INT DEFAULT 0;
 DECLARE empid CHAR(8);
 DECLARE brnid INT(11);
 DECLARE workshfid INT(11);
 DECLARE emp INT;

 DECLARE employee_roster CURSOR FOR
         SELECT EmployeeID, BranchID, WorkingShiftID
         FROM DutyRoster;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = 1;

 OPEN employee_roster;
 REPEAT
    FETCH employee_roster INTO empid, brnid, workshfid;
        IF workshfid = '6001' THEN
            SET workshfid = '6002';

        ELSEIF workshfid = '6002' THEN
            SET workshfid = '6003';

        ELSEIF workshfid = '6003' THEN
            SET workshfid = '6005';

        ELSEIF workshfid = '6005' THEN
            SET workshfid = '6007';

        ELSEIF workshfid = '6007' THEN
            SET workshfid = '6008';

        ELSE
            SET workshfid = '6001';
        END IF;

    UPDATE DutyRoster
    SET WorkingShiftID = workshfid;

  UNTIL fin
  END REPEAT;
  CLOSE employee_roster;
END

It appears that WorkingShiftID is the primary key of the DutyRoster table, and that you're trying to set this to the same value in all rows:

UPDATE DutyRoster SET WorkingShiftID = workshfid;

Presumably, the problem is you forgot to include a WHERE clause in that statement, so the update could be restricted to a single row.


Explain shows index doesnt working but i have an index The query in MySQL is:

EXPLAIN EXTENDED
SELECT id,
       username,
       name,
       last_name,
       IF(friend IS NOT NULL,1,0) as friend,
       activated,
       if(private=1 AND IF(friend IS NOT NULL,1,0)=1,0,private) as private
FROM users
LEFT JOIN ( SELECT CASE WHEN sender='asdf10e84b5fd1b1'
                        THEN receiver
                        WHEN receiver='asdf10e84b5fd1b1'
                        THEN sender
                        END AS friend
            FROM `friendship`
            WHERE (   sender='asdf10e84b5fd1b1'
                   OR receiver='asdf10e84b5fd1b1')
              and accepted=1 ) as f ON friend=id
WHERE activated=1
ORDER BY friend DESC LIMIT 100

And the EXPLAIN EXTENDED says this:

So in the users table i have this indexes

And the great problem and why i'm using indexes in this problem is because the query is large and the time is 4 sec so when 10,000 users are checking the same the query is slow x10000.

Help me pls
I forgot. The index for users as showed in the first image arent working for this query. You know what im doing bad?

UPDATE:

THE CREATE TABLE FOR users is:

CREATE TABLE `users` (
  `id` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `username` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `photo` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `birth_date` date NOT NULL,
  `country_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `verification_code` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `activated` tinyint(1) NOT NULL DEFAULT 0,
  `access_token` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notifications` int(11) NOT NULL,
  `private` tinyint(1) NOT NULL DEFAULT 0,
  `blue_keys` int(11) NOT NULL DEFAULT 0,
  `orange_keys` int(11) NOT NULL DEFAULT 0,
  `playID` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `languaje` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'es',
  `first_login` tinyint(1) NOT NULL,
  `tutos_viewed` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `usuarioIndice` (`username`),
  KEY `tutosViewed_Users` (`tutos_viewed`),
  KEY `userIndex` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

And the SHOW CREATE TABLE for friendship is

CREATE TABLE `friendship` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `receiver` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `accepted` tinyint(1) NOT NULL DEFAULT 0,
  `blocked` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `friendshipIndex` (`sender`),
  KEY `friendshipBlockedIndice` (`blocked`)
) ENGINE=InnoDB AUTO_INCREMENT=2132 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

OR does not optimize well.  The usual workaround is to reformulate the query to be the UNION of multiple SELECTs to look for the alternatives.  However, your situation might work better with a second LEFT JOIN and COALESCE().

SELECT ..., COALESCE(r.sender, s.receiver), ...
    FROM ...
    LEFT JOIN ... AS r WHERE receiver = '...'
    LEFT JOIN ... AS s WHERE sender = '...'

Side notes:

What the heck is the id that needs to be varchar(250)?
There are two identical indexes on username; drop one.
Switch from MyISAM to InnoDB.  Virtually all arguments for MyISAM are "old wives' tales".


Reducing size of datatype involved in index fails where as increasing size work This question is related to my previous question Database events that will cause deletion of index

For example if i create a table like

CREATE TABLE dbo.Customers (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, CustomerName NVARCHAR(200));
CREATE INDEX IX_CustomerName ON dbo.Customers(CustomerName);

And then try to change column datatype like shown below to 230 from 200.

alter table Customers
alter column CustomerName nvarchar(230) null

The above alter command runs successfully and index IX_CustomerName is not dropped.

But if i try to reduce the size of column to 150 from 200 then it shows error.

alter table Customers
alter column CustomerName nvarchar(150) null

I get error like

Msg 5074, Level 16, State 1, Line 1
The index 'IX_CustomerName' is dependent on column 'CustomerName'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN CustomerName failed because one or more objects access this column.

So reducing the size(data may loss) of a column does not work if index is present on a column where as if the index not present then above query works successfully.
So why does it does not allow reducing the column when index present where as increasing size does not show any problem and works?

Variable length character types only store actual content put into it and odn't keep the extra space allocated (thus the name "variable length").  As a result, when you INCREASE the size of a variable length column, the data storage doesn't actually change (in the table or the index).  The SQL engine is smart enough to know that and doesn't do anything to either, other than notate that the max size has changed.

On the other hand - if you were to try your script using fixed-length data types (i.e. CHAR of CHAR), any change up or down will change how the content is stored (since the full length is pre-allocated).  This would force the table to rewrite (and the index would need to reflect the change as well).

If you run that very script you have above but with NCHAR, the reported error would pop up during the INCREASE as well.


How do I restore a plain text postgres .backup file I exported a postgres db from pgAdmin in a plain format because I wanted to search the whole db text. I then tried to restore the database back but it gives an error saying that the archive file is not valid archive file. How do I restore the db back if it can be restored?

run:

psql -U your_user_name your_db_name < your_dump_file


Changing startup accounts on alwayson setup I have a 2 node Always On Availability Group cluster where we are planning to take backups to a file share which will then be moved to Azure cloud. We have a service account from the Windows team for the backup job, but the startup account of the database engine and the SQL Server Agent are local accounts. Using proxies did not help me with the situation so I am now thinking of providing the service account credentials in the SQL Server Configuration Manager.

What I want to know is how do I proceed?

This is what I have planned. Please let me know if I have missed something in the flow

Change service account on the Node2 (secondary) - both SQL Server (InstanceName) (database engine) and SQL Server Agent (InstanceName) (job engine)
Pause synchronization
Restart the SQL Server (InstanceName) service and the SQL Server Agent (InstanceName)
Failover the AOAG to the secondary, then change the account details of the primary (now the seconday)
repeat step 2. and 3.
Fail back to the primary again

Please let me know if I am missing any step here

Thanks!

If you have local accounts and an AG then you must be using mirroring certificates - is this correct? If so then your switchover plan is likely going to be fine because nothing is going to change from the AG's point of view.

I have done something similar just recently to replace local accounts and expiring AG mirroring certificates with service accounts and using that Windows authentication. After the local accounts were in place I ran Alter Endpoint Hadr_Endpoint For Database_Mirroring (Authentication = Negotiate) on both sides, followed by Alter Endpoint Hadr_Endpoint State = Stopped on each, and then started them again. I monitored the endpoint status from their DMVs to make sure they started and checking the ERRORLOG for issues, and confirmed that the AG dashboard was healthy. The final test to confirm it is a failover.

When you say using agent proxies didn't help though - are you sure you used one properly? What happened? I really would have thought it would work.


New Database vs New Table for User Registrations Here's an example, there is this application that completes a task (mostly data-enrty stuff). Users can sign-up on a website to begin using the application straight away.

At the moment, I was looking at setting up a new database for each user that signs up and loading the tables that are required in etc at this first signup.

However, in my previous questions about permissions, someone bought up the issues of scalability when using new databases for each user, it certainly isn't an impossible task but updating (for example, 500 users) all of those databases is going to be a huge task.

What is the best way to create these databases or tables for these new signups?

Each user can probably have 7 or 8 tables minimum from the outset.
There could be about 12 tables pretty soon too

The only other way I can see of how to do it is to have those 12 tables (or whatever) and just have many rows with respective user id's associated with them. However, should I do this, would there be an issue, if 30 or 40 people were logged on and updating/modifying information, with speed etc? vs. having new databases for each?

Having a separate database for every user is an extremely bad idea. It will be a nightmare to keep up.

Your database sounds teeny tiny, I would not worry about it causing problems to have one database. Databases can easily be designed to perform well with one database and can handle terrabytes of information speedily if properly designed.


Can we update multiple record with join of two query Can we run Update query to update multiple records with join ?
Below query is working fine when we running with one employee number -

update java set JAVA_MECHANISM='RECONCILIATION'
where usr_key =(select usr_key from usr
where usr_emp_no='******' and usr_status='Active')
and JAVA_MECHANISM= 'ACCESS';

Now requirement is want to run this query with all users who exist with where filter of 2 tables where usr.usr_status='Active' and java.JAVA_MECHANISM= 'ACCESS';  i.e.

update oiu set OIU_PROV_MECHANISM='RECONCILIATION',
POL_KEY=null, OIU_POLICY_BASED=null,
OIU_POLICY_REVOKE=null
where usr_key IN(select usr_key from usr where usr_status='Active'
and usr_udf_isexecutiveuser='1')
and OIU_PROV_MECHANISM = 'AP HARVESTED';

it’s taking too much time more than 10 mins but not completed as IN takes much time so any idea how we can get with oracle query ???

Try merge instead, e.g.

merge into oiu o
  using (select u.usr_key
         from user u
         where u.usr_status = 'Active'
           and u.usr_udf_isexecutiveuser = '1'
        ) x
  on (o.usr_key = x.usr_key)
  when matched then update set
    o.oiu_prov_mechanism = 'RECONCILIATION',
    o.pol.key = null,
    o.oiu_policy_based = null,
    o.oiu_policy_revoke = null
  where o.oiu_prov_mechanism = 'AP HARVESTED';


Mysql Connection Pooling Similar To PostgreSQL's PgBouncer I'm looking for a pooling solution for mysql that is independent of the language. I am using PGBouncer as an example because it

Resides on the server the database is on
Just change the connection string to use the pooling connector
No modification to the code required

Is there something similiar in MySQL where I can using pooling by installing a 3rd party connector and just connect through that port?

This started off as a comment but it's getting a bit long.

Perhaps you could provide a better description of what it is you actually want rather than simply say it's like something else.

If you want multiple clients to use the same connection, then it must be implemented in the DBMS, otherwise you have no session management. Further you need to advise how you intend dealing with issues of state spanning more than a single request/response (i.e. transactions).

It would also be helpful to know why you want it. MySQL has a very different setup cost and per-session overhead cost compared to PostgreSQL - why do you think it needs it, and why you think it would actually yeild any benefit.


The SELECT permission was denied on the object 'configurations', database 'mssqlsystemresource', schema 'sys' (Error 229) Window account is getting the below error when trying to connect into the SQL instance via management studio 2014.

The SELECT permission was denied on the object 'configurations', database 'mssqlsystemresource', schema 'sys' (Error 229)

The SQL instance is 2014, the user doesn't get the error if I make the login sysadmin.

The login has connect permission to the instance and even if i give
it dbowner to the master database it cannot connect.
Default database is master also.
It is not an orphaned user
I have tried the GRANT VIEW DEFINITION - no luck
I have checked and the login doesnt have any DENY permissions

When i go into the properties of the login > Securables > effective tab > it gives the error:

The SELECT permission was denied on the object 'fn_my_permissions', database 'mssqlsystemresource', schema 'sys'.

So that is obviously related also

Any ideas?

The answer was in fact the logins being in a AD group account also which I didn't see that they were members of and someone had selected all permissions on the master database so obviously selecting denydbreader/denydbwriter.

My fault for missing this group out from my checks!


MySQL 5.6 service not going to stop Something happennig in my environment

MySQL 5.6.34
RHEL 6
22G buffer pool size

When i stop MySQL it shows:

service mysql stop
.....................................

Until and unless I kill using kill -9 <pid of mysqld_safe>

Post killing when I start service mysql, it will start within 10 second....

But when I stop it, It will wait until an unless I kill the pid of mysql

What should I do...?

The issue might be happening due to innodb_max_dirty_pages_pct and innodb_log_buffer_size as before shutdown Innodb needs to flush all the dirty buffers. It depends on the buffer pool size that how much dirty buffers are there.

You can check the link https://www.percona.com/blog/2010/09/02/how-long-innodb-shutdown-may-take for more detail.

In order to reduce the time, you can tune some mysql settings as mentioned in the blog https://www.percona.com/blog/2009/04/15/how-to-decrease-innodb-shutdown-times/


multiple table inner join didn't get expected values Student_register table:

regNo
pro(HNDIT,HNDA,HNDFT)
name
start_day
etc ... more columns

Semester:

regNo
curSemmester(3,4,5)
name

The query I used to get values:

SELECT student_register.pro='HNDIT', student_register.regNo,sem.curSem =4,sem.name
FROM student_register
INNER JOIN sem
ON sem.regNo = student_register.regNo
ORDER BY sem.regNo;

I need to get regNo from student_register that according to pro = hndit or any other (hndft/HNDA) then I want to compare those regNo to current semester which that user define curSem=3 (4/5/6) regNo Numbers

Get student numbers who do HNDIT course and then get regNO from semester table that user define semester(3 or 4 or 5 ) student who do Course hndit:

student_register.pro='HNDIT' -> sem.curSem =4 -> regNo and name

Suppose that you want sem3 then query will be

Case 1#

Select stureg.regno,stureg.pro,stureg.name,stureg.start_day,
sem.regno,sem.curSemmester,sem.name
from student_register as stureg left outer join semester as sem on stureg.regno=sem.regno
where stureg.pro REGEXP '^H' and sem.CurSemmester='3'

group by regno

order by regno
;

Case 2#

Select stureg.regno,stureg.pro,stureg.name,stureg.start_day,
sem.regno,sem.curSemmester,sem.name
from student_register as stureg left outer join semester as sem on stureg.regno=sem.regno
where stureg.pro like '%H%' and sem.CurSemmester='3'

group by regno

order by regno
;

Give it Try.


Best Practices for Creating and Deleting Records in MYSQL I'm fairly new to working with databases, but I've currently have a webCrawler that I've written in C# using MYSQL database.   The crawler is frequently writing and deleting records from the database as sites are scraped.

Each record has primary key, which is MD5 checksum for the URL,  to insure that no table has two duplicate entries.

Currently is it good practice to do a check on table before inserting the database to see if there is a duplicate.   I.e. two operations on the database.

Or is it sufficient to add the record and let the database gracefully fail to add it.

The same question is relevant for other operations like deleting etc.

At the moment I'm trying to operate on 1000's of records a minute from a single client (multiple connections from that client).  Does the answer change knowing that level of database activity.

Also it fairly frequently that there will be duplicate. and the add code will be skipped.  Say every ten adds, there is one new record.

There are at least two drawback when using a query to check if URL exist:

Sending an extra query to database, will include additional network delay to your overall response time and also more network traffic.
no matter what value you insert, database will still check for duplicate key. therefore, the search on primary key is performed twice when two query is used.

Moreover, @mrdenny is right, there is 1% chance of collisions (with as few as 93,000 values). in order to  avoid that, keep a copy of the URL in your table and write query as below:

SELECT * FROM myTable WHERE md5Val = MD5('https://www.mysql.com') and url = 'https://www.mysql.com';

This will avoid collisions but your md5 value cannot be the primary key anymore. Therefore, I suggest to create table as below:

CREATE TABLE Urls(
id int unsigned NOT NULL auto_increment,
url varchar(255) NOT NULL,
url_crc int unsigned NOT NULL DEFAULT 0,
PRIMARY KEY(id), INDEX index_url_crc(url_crc)
);

and you may also use a trigger to maintain the crc value

DELIMITER //
CREATE TRIGGER Urls_crc_ins BEFORE INSERT ON Urls FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//
CREATE TRIGGER Urls_crc_upd BEFORE UPDATE ON Urls FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//
DELIMITER ;

and now query your table like this

SELECT * FROM Urls WHERE url_crc = CRC32('https://www.mysql.com') and url = 'https://www.mysql.com';

hope this is helpful.


What connection string to use to connect to network server? I have a database on my computer running on SQL Server Express 2014, and I'd like other computers connected to my network to be able to connect to it using a C# program I wrote.

I've done the following:

1. turned off windows firewall. I know I don't need to do this completely, but I'm just debugging.

2. enabled TCP/IP in SQL Server Configuration Manager on the machine running the instance.

I can't change this for all machines. On the computer I'm trying to connect from, but not the computer hosting the database, I can't enable this. I'm not sure if that's a problem. However, on my machine I've enabled pretty much everything and restarted everything.

3. Remote connections are allowed

Using a .UDL file to test the connection, I found that on my computer, if I select MyComputerName as the server, I don't see my database listed. But if I enter MyComputerName\SQLEXPRESS, I do see the database and can connect to it with integrated windows security.

But if I log on another computer and use a .UDL to test the connection. I can connect if I enter MyComputerName as the server, but if I try to enter MyComputerName\SQLEXPRESS as the server, it tries really hard but then tells me

  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Again, on my computer using the following connection strings work

Server=MyMachine\SQLEXPRESS;Database=MyDatabase;Integrated Security=True;
Server=MyMachine\SQLEXPRESS;Database=MyDatabase;Trusted_Connection=True;

But on another computer on the network, I get the error

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

But I know that the the server is allowed remote connections and that the instance name is running, so I'm stuck. Any ideas of where to go from here?

I found a solution. Maybe someone could expand on this, because I don't fully understand it.

When I opened Management Studio as MyMachineName\SQLEXPRESS which I normally do, attached the database there, I couldn't access it through MyMachineName\SQLEXPRESS in the connection string.

However, when I reopened Managment Studio as MyMachineName, attached the database there, and removed the \SQLEXPRESS from my connection string, I can now access it.

Problem solved.


Why do we use Group by 1 and Group by 1,2,3 in SQL query? In SQL queries, we do use Group by clause to apply aggregate functions.

But what is the purpose behind using numeric value instead of column
name with Group by clause? For example: Group by 1.

This is actually a really bad thing to do IMHO, and it's not supported in most other database platforms.

The reasons people do it:

they're lazy - I don't know why people think their productivity is improved by writing terse code rather than typing for an extra 40 milliseconds to get much more literal code.

The reasons it's bad:

it's not self-documenting - someone is going to have to go parse the SELECT list to figure out the grouping. It would actually be a little more clear in SQL Server, which doesn't support cowboy who-knows-what-will-happen grouping like MySQL does.
it's brittle - someone comes in and changes the SELECT list because the business users wanted a different report output, and now your output is a mess. If you had used column names in the GROUP BY, order in the SELECT list would be irrelevant.

SQL Server supports ORDER BY [ordinal]; here are some parallel arguments against its use:

https://sqlblog.org/2009/10/06/bad-habits-to-kick-order-by-ordinal


Need to group by column while ignoring some text format I have the following data.

Sport
--------------
Baseball
Football
Baseball [a]
Football [a] [b]
Basketball

How can I group this to ignore the data in the brackets?

The expression

regexp_replace(the_column, ' +.*$', '')

will remove everything starting with the first space character up until the end of the string. So you just need to group by that expression:

with test_data (sport) as
(
  values
    ('Baseball'),
    ('Football'),
    ('Baseball [a]'),
    ('Football [a] [b]'),
    ('Basketball')
)
select regexp_replace(sport, ' .*$', ''), count(*)
from test_data
group by regexp_replace(sport, ' .*$', '')

This will return:

regexp_replace | count
---------------+------
Baseball       |     2
Basketball     |     1
Football       |     2

SQLFiddle: http://sqlfiddle.com/#!15/d41d8/3379

Edit

To deal with legitimate multi-word sports there are two options:

remove everything after the first [: regexp_replace(sport, ' \[.*$', '')
remove all characters that occur between [..] and then trim the result: trim(regexp_replace(sport, '\[.*\]', '', 'g'))


How do I do a JOIN in ANSI SQL 89 syntax? In Oracle, I use

SELECT * FROM table1...JOIN...

where the dots represent either the type of join or the condition on which to be joined.

Is this ANSI 89 syntax? If not, then how would I perform a join if I were using ANSI 89?

You don't want to use SQL-89 syntax. SQL-89 is just an implicit CROSS JOIN (Cartesian product).

SELECT *            -- NEVER
FROM foo, bar;      -- NEVER EVER DO THIS

Is the same as the more modern

SELECT *
FROM foo
CROSS JOIN bar;

Adding the WHERE clause makes it the equivalent of an INNER JOIN ... ON

SELECT *                         -- DO NOT
FROM foo,bar                     -- DO THIS
WHERE foo.foo_id = bar.foo_id;   -- EVER

You want to use SQL-92 Syntax.

SELECT *
FROM foo
INNER JOIN bar
  ON foo.foo_id = bar.foo_id;

Or even better (if the join column is the same and it's an equijoin =)

SELECT *
FROM foo
INNER JOIN bar
  USING (foo_id);

Note some people don't use INNER. That's fine. It's still SQL-92.

See also

What does [FROM x, y] mean in Postgres?


Need some support on MySQL Query I have a query

SELECT leads.id, sfav.id is_favorite
FROM leads
  INNER JOIN teams tst
    ON  tst.team_set_id = leads.team_set_id
  INNER JOIN members members
    ON  tst.team_id = members.team_id
    AND members.user_id = 'a14447cf-a997-bdad-a0f8-51111616c23c'
    AND members.deleted=0
  LEFT JOIN  favourite sfav
    ON sfav.record_id=leads.id
    AND sfav.module ='Leads'
    AND sfav.created_by='a14447cf-a997-bdad-a0f8-51111616c23c'
    AND sfav.deleted=0
  LEFT JOIN cldetails bidders
    ON  bidders.lead_id = leads.id
    and bidders.deleted = 0
  LEFT JOIN accounts
    ON accounts.id = bidders.account_id
    AND accounts.deleted = 0
WHERE leads.deleted = 0
  AND
  ( ( accounts.billing_address_state IN  ( "AL","AK","AZ","AR","CA","CO","CT","DE","DC","FL","GA","HI","ID","IL","IN","IA","KS","KY","LA","ME","MD","MA","MI","MN","MS","MO","MT","NE","NV","NH","NJ","NM","NY","NC","ND","OH","OK","OR","PA","RI","SC","SD","TN","TX","UT","VT","VA","WA","WV","WI","WY") )
    OR leads.assigned_user_id = "a14447cf-a997-bdad-a0f8-51111616c23c"
  )
  AND leads.parent_lead_id IS NULL
GROUP BY COALESCE(leads.parent_lead_id, leads.id)

Which is taking around 4 seconds the Explain plan is as

id   select_type     table               type        possible_keys                                       key                 key_len     ref                                             rows    Extra
1    SIMPLE          leads               ref         idx_leads_tmst_id,idx_del_user                      idx_del_user       2    const                                  16097    Using where
1    SIMPLE          sfav                ref         idx_abdul_test                                      idx_abdul_test     375 db.leads.id,const,const,const   1
1    SIMPLE          bidders             ref         idx_abdul_test                                      idx_abdul_test     111  db.leads.id,const              5
1    SIMPLE          tst                 ref         idx_ud_set_id,idx_ud_team_id,idx_ud_team_set_id     idx_ud_set_id      109  db.leads.team_set_id           3    Using where; Using index
1    SIMPLE          members             index       idx_teammemb_team_user,idx_abdul_test               idx_abdul_test     220  NULL                                   8    Using where; Using index; Using join buffer
1    SIMPLE          accounts            eq_ref      PRIMARY,idx_accnt_id_del,idx_accnt_assigned_del     PRIMARY            108  db.bidders.account_id          1

Indexes are as

teams
  PRIMARY KEY (`id`),
  KEY `idx_ud_set_id` (`team_set_id`,`team_id`),
  KEY `idx_ud_team_id` (`team_id`),

members
  PRIMARY KEY (`id`),
  KEY `idx_abdul_test` (`team_id`,`user_id`,`deleted`)

favourite
  PRIMARY KEY (`id`),
  KEY `idx_abdul_test` (`record_id`,`module`,`created_by`,`deleted`)

cldetails
  PRIMARY KEY (`id`),
  KEY `idx_abdul_test` (`lead_id`,`deleted`)

leads
  PRIMARY KEY (`id`),
  KEY `idx_del_user` (`deleted`,`assigned_user_id`),

What can i do minimize execution time of query.

UPDATE : If i run this query with LIMIT clause it will run in 0.00 sec,But this is a part of some other query so i can't use LIMIT clause over there.

This question offers a usable answer on stackexchange from KernelM: Indexed Views in MySQL

Specifically this paragraph:

I don't think MySQL supports materialized views which is what you would need, but it wouldn't help you in this situation anyway. Whether the index is on the view or on the underlying table, it would need to be written and updated at some point during an update of the underlying table, so it would still cause the write speed issues.

Your best bet would probably be to create summary tables that get updated periodically.


Window function in Update statement Is there a work around to use window functions in update statement

You can populate sample/original table using the code

Create table #table(
key1 Char(5),
date1 Date,
date2 Date
);

Insert into #table
Values ('abc','2019-07-22',NULL);
Insert into #table Values ('abc','2019-07-23',NULL);
Insert into #table Values ('def','2019-07-22',NULL);
Insert into #table Values ('ghi','2019-07-22',NULL);
Insert into #table Values ('ghi','2019-07-23',NULL);

I want to partition and Order by key1 - check for NULL in date2 - leave NULL intact if max(date1) over (partition by key1 order by key1) = date1 and change to max(date1) over (partition by key1 order by key1) if the above condition is not true. This is a data vault concept by the way.

Original:

Required:

I tried the below code:

UPDATE #table
SET date2 = CASE WHEN MAX(date1) OVER (PARTITION BY key1 ORDER BY key1) = date1
  THEN NULL
 ELSE MAX(date1) OVER (PARTITION BY key1 ORDER BY key1) END
WHERE date2 IS NULL ;

I am getting an error message:

  Msg 4108, Level 15, State 1
  Windowed functions can only appear in the SELECT or ORDER BY clauses.

The error message defines the problem: you can't use a window function directly in an assignment (SET). You can use a CTE to pull out the aggregate and then apply the update to the CTE (which pushes it back to the table).

;WITH t AS
(
  SELECT key1, date1, date2,
    date1max = MAX(date1) OVER (PARTITION BY key1 ORDER BY key1)
  FROM #table
)
UPDATE t SET date2 = CASE date1max
  WHEN date1 THEN NULL ELSE date1max END
WHERE date2 IS NULL;

If dates are not unique for any given key, you might need to do something to deal with ties. Please add sufficient sample data that would represent all edge cases and how you want to handle those.


SQL Server 2019 executes unreachable code [Update: This question describes a bug which has been fixed in Cumulative Update 5 for SQL Server 2019.]

Consider the following repro example (fiddle):
CREATE FUNCTION dbo.Repro (@myYear int)
RETURNS datetime
AS
BEGIN
    IF @myYear <> 1990
    BEGIN
        RETURN NULL
    END

    DECLARE @firstOfYear datetime;
    SET @firstOfYear = DATEFROMPARTS(@myYear, 1, 1);

    IF DATEDIFF(day, @firstOfYear, @firstOfYear) <> 0
    BEGIN
        RETURN NULL
    END

    RETURN @firstOfYear
END
SELECT dbo.Repro(0);

Obviously, that function should return the first of January 1990 if the input is 1990, and NULL otherwise. Yes, I know that DATEDIFF(day, @firstOfYear, @firstOfYear) <> 0 is a nonsensical operation. This is a mcve to demonstrate a potential bug, not production code.
Now let's execute SELECT dbo.Repro(0) on SQL Server 2017 and SQL Server 2019.
Expected result: NULL.
Actual result on SQL Server 2017: NULL
Actual result on SQL Server 2019:

Msg 289 Level 16 State 1 Line 1
Cannot construct data type date, some of the arguments have values which are not valid.

Apparently, SQL Server 2019 executes some of the code below the initial guard clause (IF @myYear <> 1990) even if it shouldn't.
My questions:

Is this expected behavior, or did I find a bug in SQL Server 2019?
If this is expected behavior, how do I correctly write a guard clause validating input paramters?

This is a bug with the inlining of Scalar UDFs (or perhaps a bug with the query optimiser that is being exposed more by scalar UDF inlining). You can use WITH INLINE = OFF to turn off inlining for that function.

Using a variable instead of a constant shows a bit more detail

declare @myYear int = 0

SELECT dbo.Repro(@myYear);

Node 5 defines Expr1000 = CASE WHEN [@myYear]<>(1990) THEN (1) ELSE (0) END
Node 2 defines [Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,datefromparts([@myYear],(1),(1)),0))

These expressions are simplified when using the literal 0 to 1 and CONVERT_IMPLICIT(datetime,datefromparts((0),(1),(1)),0) respectively.

The datefromparts(0 will throw the error when evaluated.

Node 6 defines Expr1002 = CASE WHEN [Expr1000] = (1) THEN (1) ELSE (0) END

And Expr1002 is used as a passthru predicate on a nested loops join (node 3). On the inside of that nested loops the constant scan (node 7) returns no columns.

So this looks like the same basic issue as the answer here where an expression on the inside of a nested loops protected by a passthru predicate is moved out into an unprotected region.


How secure is an Azure database within Microsoft? I am considering moving most of my Databases to Azure SQL server Database, but I am concerned that Microsoft will be able to snoop and look at the data inside the database, which is absolutely not acceptable.

Can Microsoft see any data in an Azure AQL DB?
What is the extent of Microsoft ability to influence a database in Azure?

Thanks for the help.

The only way to be secure, is to control your data. If someone else is running it on its own infrastructure, they are able to read, change or use them. Period.
For example: if your database is confidential one, you should not use someone else infrastructure.
Saying that ( which is completely obvious), you may assume, that as long as you not breaking the law, and USA is not in political conflict with your country, Microsoft is not interested in peeking at your data, exactly as any other company

So if you have something to hide, use your own server. If you have nothing special ( and putting it on the web is in compliance with local law) go on.


can log shipping to log shipping? I have one production SQL Server 2008 Database with log shipping set up for that server in US.

I have a account in the log shipping database so I could sometimes run query on log shipping database.

Now I need to ship data back to Korea daily but I don't want to impact production performance. Can you suggest any technique to achieve this

You can ship the logs to as many sources as you want. Just add a new secondary server on the primary one. The impact will be on the Korean server as it will have to copy the logs from the backup directory and replay them.


Bidirectional data transfer I have two nodes and one database. I want to transfer data between them. DML operations on server node-1 should transfer to node-2 and DML operations on node-2 should transfer to node-1 as well.

Is this possible and what is the best (most fast, safe, and stable) way?

Distributing reads is easy; distributing writes is not. In SQL Server, most availability technologies require a specific node or database to be the "master" where all writes go.  Some possible options:

Merge Replication
Peer-to-Peer Replication
Transactional Replication with Updatable Subscribers (deprecated)

What is "best"? That's not really the kind of question we can answer here unless you get much, much more specific about what you mean by "best."


Disk size and log file growth during upgrades I have four SQL Server databases. Per the maximum load requirements, each database file can be between 1 and 2 TB in size. The HDD is a multi-disk DAS with about 9TB of space. During a database upgrade, the transaction log file for a single database can grow up to 4TB in size. (yeah, I added a new column to a multi-billion row table.) The databases are on Simple Recovery model.

I can find no recommendation (or rule of thumb) from Microsoft about how much disk space I need for a given database size. I get that it can vary widely. If my understanding is correct, it's the largest transaction which I need to consider since space internal to the log file is re-used for different transactions. I think the log growth is set to 10%, but even if it were set to 1 or 2 Gig increments, I think I'd still be cutting it close.

Is there some strategy I need to employ to avoid such huge log files or do I simply need more disk space to avoid running out of space during an upgrade? If I'm doing something wrong, I want to learn how to do it the proper way.

I'm using SQL Server 2012.
Thanks!

(yeah, I added a new column to a multi-billion row table.)

Adding a column to a very large table can have implications but there is a clever way of adding column as well.
From : Adding NOT NULL Columns as an Online Operation

Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table.
Adding a NOT NULL column with a default value that is not a runtime constant is always performed offline and an exclusive (SCH-M) lock is acquired for the duration of the operation.

Enterprise edition (works on 2008 R2 standard edition as per comment) has ALTER TABLE source_table SWITCH TO new_destination_table

The databases are on Simple Recovery model.

In simple recovery model, only a CHECKPOINT will truncate the log.
If you are recording autogrowth metrics during your typical upgrade, then an average of those collected metrics would give you a good starting figure. This script will help you get started (you would need default trace enabled and running on your server).

I think the log growth is set to 10%, but even if it were set to 1 or 2 Gig increments, I think I'd still be cutting it close.

I would suggest you to change the autogrowth setting from Percentage to Fixed MB. As @AaronBertrand says in his comment :

The problem is that it takes longer and longer over time, since 10% of a growing file is also constantly growing itself - it's like compound interest, but you're paying, not receiving.

For completeness, make sure you have Instant file initialization enabled, so the data file autogrowth can take advantage of it.


Export database in one file with every dataset existing I've got a unusual question.
The human resources needs to export an old DB to a new one.
And the guys managing the new DB wants one csv file with every dataset existing.
I did a Relational database and now i find it difficult to write the select statement.

I did this:

SELECT *
FROM teilnehmer, personen, seminare, termine, kategorien, haeuser
WHERE teilnehmer.sid = seminare.sid
AND teilnehmer.pid = personen.pid
AND termine.sid = seminare.sid
AND seminare.katid = kategorien.katid
AND personen.hausid = haeuser.hausid
ORDER BY personen.pid

but i only get ~1500 entrys.
I know thats to little, because when i look for a Name and all the workshops he/she did, i get much more than what is in this export.

I don't need the answer i would appreciate if someone could give me a little hint in how to get this done.
Till yet i had only like one join for the data i'd needed to display but this confuses me.

My guess is you need to specify the type of join.  You really want a "full outer join" which will not exclude records.  A select where you join by keys will default to an inner join.

See https://stackoverflow.com/a/6188334/259538


How to protect Sqlite3 database with password that using in desktop application I am creating database with Qtsql.QSqlDatabase using with in python. I want to protect my Database with password. I am using this database for my desktop applications. If it is possible, How to do this. or is there any or other databases way to create database with password protected.
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")#database
db.setDatabaseName("project.db")

Sqlite doesn't come with encryption built in, however you can enable encryption in sqlite by either purchasing the official paid extension here for $2000. Or you can take a chance on one of the free alternative like this one. I have no idea how well the free versions will work, but I imagine the paid one is quite robust and up to date.
You will also need to build a custom qt sql driver by following the instructions here. And finally you must run this sql immediately after opening the database
PRAGMA key='your-secret-key';


Index optimization for table that is truncated every day I have a bunch of tables in SQL Server 2012.

These tables are fed by other tables. Every day there are routines that truncate (fully delete) them and run queries whose result are inserted into them. They are like static views, storing those processed data so that they are queried quicker.

And there are source tables, which receive millions of new records every day, these aren't deleted.

All these tables have a time when they get a lot of inserts, and in the rest of the day they are only read.

Even with these 'static view' tables, I think I can enhance overall performance. Is there any best practice to handle these behaviors?

For the first situation, I've run a few benchmarks and saw that the best performance comes from using nonclustered indexes on the most filtered field. I drop these indexes before truncating and recreate them after inserting. Dropping and recreating is faster than rebuilding, and inserts are faster too.

Should I keep PKs (without semantic meaning) and clustered index too? I suppose that with no index those inserts will be even faster, but will I have any issue if these tables have no PK? They are never used explicitly.

I ask this because when I create the table and set its PK with

idBenchmark int NOT NULL IDENTITY (1,1) PRIMARY KEY

clustered index is created automatically, with a random name, and therefore I can't drop and recreate it.

Go ahead and drop the clustering key while you're importing data.  When you've finished your INSERTs, create the clustering key first, then the PK if it's non-clustered, then any remaining indices.  I'm running such scripts at this very moment, and it takes about half as long as inserting into a table which is fully indexed.

There's no problem in going without a clustering key while loading data.  I would recommend that you import your data in the same order that it will be clustered, if possible; this will reduce the need to shuffle the data around when it's clustered.  If you're using an arbitrary IDENTITY column, I suggest you reconsider; there may be a better candidate for clustering (or you may not even need a clustering key).

There's no problem in going without a PK while loading data.  It's most important in maintaining referential integrity and in giving your indices a narrow target to hit; neither applies when you're bulk-loading data, assuming that you trust your data to not contain duplicates.

Duplicates are not always the devil either, and it may be faster to remove them after bulk load than to build processes at the front end that de-dupe in other ways.

My preference is to pull in data in all its ugly rawness first, often into a heap table, then do SQL cleanup before copying it to a new table.  Maybe that's just because I'm a SQL guy and everything looks like a nail to me, but SQL is optimized for set-based operations.  On the other hand, if the volume of data is huge you may need to do conversions and cleanup on an RBAR basis as you import it via SSIS or whatnot.

I would also recommend explicitly defining your PK (if any) with an ALTER TABLE statement after your CREATE TABLE.  This reminds you to make an explicit choice (including whether to cluster on the PK), puts it next to your other index declarations, and lets you give it a non-random name.


MySQL: reducing ibdata file size for MyISAM tables My Question is actually very similar to this one and also includes a good answer for a case with InnoDB Engine tables:

What is the best way to reduce the size of ibdata in mysql?,

I have noticed that drop schema do not shrink ibdata files , so i have looked for a methods to configure the DB so that the size will be reduced after deleting a schema.

i have found many links talking about InnoDB and the way to save table per file so that the .frm file it self will contain the table data and it will be reduced.

But what happens with MyISAM tables (with more than 5G table size).

MyISAM tables do not use ibdata files, so they won't affect the size of ibdata files.

From the MySQL Docs, here is how MyISAM stores data on disk:

  Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.


Determining Homogeneity by Common Foreign Keys I manage a MySQL database of ten or so tables.

In higher code, I have a list of primary keys belonging to entries of some table A. These A entries have foreign keys to entries in another table B. The relationship is 1-to-many, so several As may point to the same B.

Problem: I need to determine if the list of primary keys I hold in higher code (Scala) is homogeneous. That is, if their foreign keys to B all correspond to the same entry in B.

Question: Would it be possible to determine this by analysing the results of a single SQL call?

For the table A (with B_id) and table B (with id)
Count of Primary Keys By Foreign Key
SELECT FK,COUNT(1) PKCount FROM
(SELECT IFNULL(B.id,0) FK
FROM A LEFT JOIN B ON A.B_id = B.id) K
GROUP BY FK;

If FK is 0, then PKCOUNT is a count of Primary Keys that do not have a Foreign Key
To be 100% homogenous, FK should never show up as zero(0) (In a Perfect World).
Count of Foreign Keys Used By Primary Keys
SELECT B.id,IFNULL(FK_Count,0) FKCount
FROM B LEFT JOIN
(SELECT B_id id,COUNT(1) FK_Count FROM A GROUP BY B_id) K
USING (id) GROUP BY B.id;

If FKCount is 0, then B.id is a Foreign Key Not Used By Any Primary Key
To be 100% homogenous, FKCount should never show up as zero(0) (In a Perfect World).


Informix DBDELIMITER tab I'm using the Informix DBEXPORT to dump my database however it uses | as its default delimiter.

I know I can specify the delimiter I'd like to use using the DBDELIMITER environment variable but how do I set it to use tab

I tried using \t but that didn't seem to work

(It's running on a CentOS 5 Server btw)

UPDATE

checking through the documentation it says that you can specify tab using CTRL-I but when I set it to CTRL-I it comes out in the unl file as ^@

In theory, you would need to specify an actual tab, possibly using Bash's ANSI C quoting:

$ DBDELIMITER=$'\t' dbexport stores
Invalid delimiter; Don't use '\\', SPACE or HEX or Multibyte chars.
$

Unfortunately, that looks like you're not allowed to use tab either.  (Test: Informix 12.10.FC5 on Mac OS X 10.11.6.)

Incidentally, I tried some alternatives: $'\a' (alert, aka Control-G) and $'\b' (backspace, aka Control-H) are both accepted, but none of $'\f' (formfeed, aka Control-L), $'\v' (vertical tab, aka Control-K), $'\r' (carriage return, aka Control-M) of $'\n' (newline, aka Control-J) are allowed.  Any other control character from $'\0' through $'\037' (octal numbers, all except 9-13 decimal, 011-015 octal) seems to be OK (and NUL or 0 being OK did surprise me!).  Do you have any tabs in your main data?  If not, you could export with one of the acceptable alternative characters and then map that to tab.  If you have tabs, life is harder because you'd need to protect any tabs that are in a data field with a backslash.

DB-Access allows DBDELIMITER=$'\t' without problem and produces an unload file with tab delimiters, so the problem is not in the shell code I'm using.

If you're certain you must have tabs, then
investigate Art Kagel's myexport which is part of his utils_ak package from the IIUG Software Repository, which also uses my SQLCMD, available from the same source.  Using DBDELIMITER=$'\t' sqlunload -d stores -t customers (or sqlcmd -U -D $'\t' -d stores -t customers, or various other twists of the command line) produces output in a tab-delimited format.  (On its own, SQLCMD does not handle a full export; it will cheerfully unload single tables, though.  It does not yet handle NUL as a field separator or line separator — a design decision or artefact that I need to review.)


Extending fiat transaction log to support cryptocurrency Let's assuming that I have an existing database that retaings fiat currency transaction as described below with decimal (18, 4).

Now, I would like to record bitcoin, ethereum and erc20 token transactions.

Transaction
------------------
TransactionID
CurrencyID
UserID
DebitAmount  decimal(18, 4)
CreditAmount decimal(18, 4)

The decimal precision of cryptocurrency is suggested as follows:

 decimal (16, 8)  -- bitcoin
 decimal (30, 18) -- ethereum / erc20 token

I am thinking of just:

Treating bitcoin, ethereum, erc20 token as new currency in the Currency table.
Changing the DebitAmount and CreditAmount directly to decimal(30, 18).
Applying decimal(30, 18) data type changes to all the tables that retain currency amount.

My questions are:

Any possible impact to original currency amount conversion that I need to look after for when I increase the precision (eg. currency rate change DebitAmount * 1.023)?
Am I doing this in the "correct" way?
Is there any other better approach/alternative to this?

Any possible impact to original currency amount conversion that i need to look after for when i increase the precision?

Yes.

Carefully review your calculations against the table here: Precision, scale, and length, because you can have loss of precision when multiplying decimals:

  The result precision and scale have an absolute maximum of 38. When a
  result precision is greater than 38, it's reduced to 38, and the
  corresponding scale is reduced to try to prevent truncating the
  integral part of a result.


What is running right now? I know I can use "show processlist" to see what is running when I'm in the office. How can I see what is running when I'm not there.

Something is running on my server at 3am every morning that is generating a IO Rate Alert and a CPU Usage Alert, but I don't know what it is. Is there a logging system that I should be using or a script I can write that will report what is currently running against the database at that time.

The alerts come in within the same rough time-frame every night, about 0300 - 0330.

I'm not even sure if this is a DBA question or more a Sys Admin question?

I appreciate any help.

You have three(3) options to go about looking at what is running in MySQL
OPTION #1 : General Log
You could simply activate the general log and look for the timestamp between 3:00-3:30 AM. You could either have the general log as a text file or a MyISAM table. Here are my posts on using the general log:

How to enable MySQL general log? (Jan 08, 2012)
How to keep MySQL general_log after I restart my computer? (Dec 10, 2011)
How do I output MySQL logs to syslog? (Jun 30, 2011)

OPTION #2 : Slow Log
Same setup procedure as the General Log. Here are my past posts on this:

MySQL slow log configuration (Mar 30, 2012)
Create a custom log table in MySQL (Nov 18, 2011)

OPTION #3 : Query Digest
The problem with Options 1 and 2 is that they record only completed queries. If you are trying to catch long running queries in the act of running long between 3:00-3:30AM, even before they get recorded in the slow log or general log, you have to go with setting up a crontab.
I usually use MAATKIT's mk-query-digest (most prefer the most current pt-query-digest). Here are my posts on how to script a crontabed Query Digest:

Investigate peak in MySQL throughput (Apr 24, 2012)
MySQL general query log performance effects (Nov 24, 2011)


When pg_restore honours --dbname option I am trying to restore a binary pg_dump backup under a different database name that was in the original dump. Despite giving --dbname command line argument, pg_restore tries to restore everything under the original database name - the behavior I especially want to avoid.

createdb mydb

pg_restore --dbname=mydb --exit-on-error -Fc database.bak

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8; 2615 16396 SCHEMA topology u9o2f4j0
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "topology" already exists
    Command was: CREATE SCHEMA topology;

How can I make pg_restore to restore the database under a different name?

pg_dump --version
pg_dump (PostgreSQL) 9.5.5

pg_restore --version
pg_restore (PostgreSQL) 9.6.1

There are few command line options you need to consider.

  pg_restore -x --no-owner --dbname=mynewdb --exit-on-error -Fc database.bak

-Fc: restore from a binary back up as its more flexible than plain text SQL dump
--dbname: restore under this database name (must be created beforehand using createdb)
-x: do not try to restore access priviledges (e.g. the superuser name may change between databases)
--no-owner: the database owner user is different on the system where restore is performed
--exit-on-error: If bad stuff happens, abort and don't flood your terminal with useless errors


Safe delete Transaction Log in SQL Mirroring scenario We are using SQL Server Mirroring with a witness (High Safety mode) and we see the transaction log on the Principal Server has grown around 20 GB in few weeks. I looked around on SO and google for specific articles related to "Safe delete Transaction Log" in SQL Server but found none for my case.

My Questions:

Would it be safe taking full backup of Principal Server and then delete the transaction log completely?
Does it have to do something with Full Recovery/ Simple Recovery Mode?
What would happen when the transaction log on the Principal Server is deleted and the after sometime Mirror Server will take the place of the Principal?

Note:

We are using SQL Server 2014 Enterprise Edition for Mirroring and Express version of it as a Witness.

Thanks in Advance!

Transaction log is the most important element in SQL Server. It is like a sequential journal that logs all the changes made to the database and contains enough information to undo / redo the changes in the event of crash recovery to keep the database in a consistent state.

Highly recommend to read : Understanding Logging and Recovery in SQL Server

All your question are just because you dont have proper understanding of how transaction log works.

To keep transaction log in shape, you should take regular log backups (more frequently) on the principal server. Check - How do we handle transaction log maintenance for a mirrored database?

To answer your questions :

  Would it be safe taking full backup of Principal Server and then delete the transaction log completely?

No this is not possible and not advisable to delete T-Log.

  Does it have to do something with Full Recovery/ Simple Recovery Mode?

Mirroring uses T-log to replay everything from principal server to mirror server. So Full recovery is only possible. check : recovery models.

  What would happen when the transaction log on the Principal Server is deleted and the after sometime Mirror Server will take the place of the Principal?

Not feasible. Please read the links that I have suggested.


Replication and linked server security I have three servers: P1, A1, and R1.  P1 and A1 are production oltp servers.  On each there are two databases that are being replicated down to R1 for olap (mostly SSRS reports).  I am working on cleaning up our linked server infrastructure.  P1 and A1 both have linked servers to R1, and SSMS won't let me remove them because they are being used for replication.

Currently both of these linked servers are using the sa account on R1 for authentication.  I'd like to replace this with a least privileged account.  What privileges would P1 and A1 need in order for replication to work?

In case it matters, all of the publications are transactional replication.

Also, sql server versions:

P1: 10.0.5500
A1: 9.0.5069
R1: 10.0.4000

I just reviewed the replication on one of my clients systems and I see similar linked servers both on the publisher and the distributor.  In my case the linked servers are all setup using the "Be made using the login's current security context".

If you've got the linked servers setup using a specific security context that's probably because the machines aren't on the same domain.  If they are, and the rights are setup correctly you can try changing things.  The linked servers should only be used when making changes to the replication configuration so if there's a problem you may not see it until later.


How to copy wt files from another mongo instance? I received a copy of mongo dbpath directory copy from a peer. I copied entire content to my local dbpath with an intention to work on the collections. However, my local is not recognizing the databases or the collections. Am I missing anything?

The WiredTiger.wt file is a crucial metadata file that keeps track of the state of the whole database and all the *.wt files. Hence, the content of this file is deployment specific, and not transferable to other deployments. It is not possible to recreate the content of this file.

If you have a known good backup of the whole dbpath, you may be able to restore to a valid state of the database, as long as you restore the dbpath as a whole.

Please see MongoDB Backup Methods for instructions and information on supported backup and restore methods.

For further your ref here, here and here


Granting [NT AUTHORITY\SYSTEM] sysadmin on SQL server - Compliance/Best Practice New to security in general.  We have a third party service which runs on SQL server host and under Local System.  Since it is running under Local System, they have always given sysadmin privilege to [NT AUTHORITY\SYSTEM] on older server.

When a new server was getting build we request if you could use a service account to run the service so that permission can be granted to that specific account instead of a build in account and for whatever reason the application team does not seem to have figured out a way to make it work.  Now they are asking if we could go back to giving [NT AUTHORITY\SYSTEM] permission again.

So my options/questions

Grant sysadmin to [NT AUTHORITY\SYSTEM].  Probably the worst option, but I am more interested in knowing what the industry standard is about this.  Does any compliance like SOX or PCI ban this?
Grant more granular permission to [NT AUTHORITY\SYSTEM].  This is what I will try to do first.

So I would like know about what others do in terms of [NT AUTHORITY\SYSTEM] in SQL Server and what SOX, PCI etc compliance best practices are.

Thanks

I assume this is some sort of IT management or monitoring agent, otherwise it shouldn't be running on your SQL Server.

If you don't run any other code as SYSTEM on the server, there's not really a lot of extra risk over and above running 3rd party code with Windows administrative privileges.  If the service runs as any local administrator, it can get access to SQL Server.

If possible you should do both of:

1) Grant the service the least privileges is needs to run

2) Provision the login and grant permissions to the per-service SID instead of the service account.

So if the service is has a Name of 'someservice' (not DisplayName), create a login like:

create login [nt service\someservice] from windows

And grant the permissions to that.  Then other services running under the same service account won't be able to connect to SQL Server.


How to install pgstattuple extension in postgres 10.6? Can someone let me know how to install pgstattuple extension in postgres 10.6 running on Ubuntu 16?

I have installed postgres-contrib and can see the following .sql files under extension folder

ubuntu@vrni-proxy:~$ ls /usr/share/postgresql/10/extension/pgstattuple*
/usr/share/postgresql/10/extension/pgstattuple--1.0--1.1.sql  /usr/share/postgresql/10/extension/pgstattuple--1.3--1.4.sql  /usr/share/postgresql/10/extension/pgstattuple.control
/usr/share/postgresql/10/extension/pgstattuple--1.1--1.2.sql  /usr/share/postgresql/10/extension/pgstattuple--1.4--1.5.sql  /usr/share/postgresql/10/extension/pgstattuple--unpackaged--1.0.sql
/usr/share/postgresql/10/extension/pgstattuple--1.2--1.3.sql  /usr/share/postgresql/10/extension/pgstattuple--1.4.sql

To install a extension in PostgreSQL exist CREATE EXTENSION command.

Try:

CREATE EXTENSION pgstattuple;

In your database


Encrypt backup in 2008 R2 Is it possible to encrypt 2008 R2 backups like TDE for instance?
What I've read is encrypting the whole database and that way the backups will be encrypted. But can you have TDE encrypted backups alone?

Backup Encryption was added in SQL Server 2014.
And SQL 2008 R2 is out of support.  So upgrade, and yes, otherwise you can use TDE, or write ordinary backups to an encrypted location (eg with BitLocker).


Insert into table which contains nested-table inside (Object in Oracle) I have script of creating here:

create type Ingredients_type as object (
Ingredient_ID varchar2(20),
Ingredient_Name varchar2(50),
ICalori int,
IPretein int);

create type Food_type as object (
FoodType_ID varchar2(20),
FoodType_Name varchar2(50));

create type FoodIngredients_type as object (
Ingredients Ingredients_type,
Amount int);

create type FoodIngredients_table as table of FoodIngredients_type;

create table Foods(
Food_ID varchar2(20),
Food_Name varchar2(50),
FType Food_type,
FIngredients FoodIngredients_table,
Cost int,
FCalori int,
FProtein int)
nested table FIngredients store as FIngredients_col;

But I can't insert values into Foods. I have counted enough arguments and don't know where is missing or redundancy...

insert into Foods values ('F1', 'Food #1', Food_type('FT1', 'Noodle'),
FoodIngredients_table(FoodIngredients_type(Ingredients_type('I1','Ingredient #1', 0, 0), 10,
                                           Ingredients_type('I2','Ingredient #2', 0, 0), 20)),
                                           1000, 0, 0);

A simple mistake, I forget to call FoodIngredients_type. Sorry for disturbing all again.

insert into Foods values ('F1', 'Food #1', Food_type('FT1', 'Noodle'),
FoodIngredients_table(FoodIngredients_type(Ingredients_type('I1','Ingredient #1', 0, 0), 10),
                      FoodIngredients_type(Ingredients_type('I2','Ingredient #2', 0, 0), 20)),
                                           1000, 0, 0);


MySQLdump Exclude master-data Is it possible to exclude the master data from a mysql dump? From the manual it seems like the only options are commented out or present. I don't need the data at all because it is going to a static system. Commented out will work but just wondering if 0 or some other value would make it not present?

  Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave.

  If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.

-https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_master-data

My plan was to use:

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=0 ...

but from the above entry that would put the CHANGE MASTER TO as an uncommented command.

Use grep -v with --master-data=2
mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 | grep -v "^-- CHANGE MASTER TO" > dump.sql

GIVE IT A TRY !!!


Replicate data from multiple tables with same structure to a single destination table with additional columns? I have a table with say the following structure. This table is in multiple databases.
Name varchar(50),
Age int

I want to replicate the data from this table from multiple databases to a single table. The target table has an extra column TabCode. I want to populate this TabCode with a value depending on from where the data is coming.
I do not have the option of adding any new column to the source tables.
Name varchar(50),
Age int,
TabCode int

Can this be done in Microsoft Sql Server?

Yes this is achievable in Microsoft SQL Server, and there's a couple different ways you can go about it.

SSIS Package: You can create an SSIS package that pulls the data from the different source databases and consolidates it all into one table on the target database with the additional column "TabCode" filled in accordingly.

Replication + View: You can setup replication publications between your source servers to subscriptions on your target server. Then you can use a view that unions all the data together into one view with the additional column "TabCode" filled out.

Some of the things to consider about SSIS packages is that they require some upfront implementation (you create them as a Visual Studio SSDT project type), and they require a job to trigger when they run (typically a SQL Agent job). So they aren't necessarily very real time in data synchronization (though they can be scheduled to ran as frequently as every second - but their actual runtime will depend on how you implement the SSIS package).
Some things to consider about Replication + Views is you can accomplish near real time data synchronization (with Transactional Replication) and they require less upfront implementation, but can be a little tricky getting setup first time around. (Overall they're not too bad to setup and work well when they work.)


Can I get results from a Postgres query that I got disconnected from? I started an hours-long Postgres query in my psql shell. Then, I got disconnected from the network and had to make a new psql shell. If I look in pg_stat_activity, I see that my query is still running. Can I connect to that query and get its output?

Unfortunately you can't. However, if the query is for changing data and there isn't any transaction started, then it will make the change. Next time start a screen or save results to a file.


How to export oracle 11g database partially I have a huge oracle test database whose size is more than 100 GB. I want to export only 1% of all the data and import it into my local database to have a local replica of that application. Is it possible in oracle 11g?

You can add a where clause to each table to filter rows exported:

http://www.dba-oracle.com/t_export_where_clause_selective.htm

However your referential integrity will not be maintained by this method, because the export is done per-object and filters are applied in the same way. You will need to either be very precise in how you define each where clause, which will make the export slow, or clean up the data afterwards by deleting any orphaned rows.

I would recommend creating a new schema in the same database and copying a chunk of the data into that, while maintaining the integrity during the copy. Then export the new schema.


Can't Restore Database Because of Rogue Log File Someone, somehow, made a database disappear from SQL Server 2008 R2. I'm assuming it was dropped, because it doesn't show in SSMS in the Object Explorer's list of databases. Let's call the database "Rogue". Now, there is a rogue log file, "Rogue_log.ldf" on the server's file system. The file cannot be deleted via Explorer.

Here is the restore script. Note that I tried to run it without "MOVE" first, and then got the error further below in this post. Yes, it's "Logs" and not "Log" in the path (don't ask).

RESTORE DATABASE Rogue
FROM DISK = 'E:\Rogue.bak'
WITH MOVE 'Rogue' TO 'E:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Rogue.mdf',
MOVE 'Rogue_log' TO 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\Logs\Rogue_log.ldf',
RECOVERY, REPLACE
GO

Trying to run the script is giving the following error:

  Msg 3634, Level 16, State 1, Line 1 The operating system returned the
  error '5(Access is denied.)' while attempting
  'RestoreContainer::ValidateTargetForCreation' on
  'D:\MSSQL10_50.MSSQLSERVER\MSSQL\Logs\Rogue_log.ldf'. Msg 3156, Level
  16, State 8, Line 1 File 'RGA_log' cannot be restored to
  'D:\MSSQL10_50.MSSQLSERVER\MSSQL\Logs\Rogue_log.ldf'. Use WITH MOVE to
  identify a valid location for the file. Msg 3119, Level 16, State 1,
  Line 1 Problems were identified while planning for the RESTORE
  statement. Previous messages provide details. Msg 3013, Level 16,
  State 1, Line 1 RESTORE DATABASE is terminating abnormally.

I can restore a backup from Rogue.bak if I change the log file name to Rogue1_log.ldf (Star Wars puns NOT intended.) Can anyone help me figure out what's going on with this rogue Rogue_log.ldf on the server file system?

I'd bet the database didn't actually get dropped, and is still in use by SQL.

Not sure why it isn't showing up in SSMS, but run the following query to see what database is still using it:

SELECT database_id, DB_NAME(database_id) AS DBName, name AS LogicalName, type_desc,
physical_name, convert(numeric(10,1),(size/128.0/1024.0)) SizeGB
FROM master.sys.master_files
ORDER BY database_id, type_desc desc, name

Is your Rogue database in the list?

If you just do SELECT * FROM master.sys.databases, does it show Rogue? What does it show as the user_access_desc and state_desc?

What does the SQL errorlog say during the time in question? It should mention a database drop or change in status.


Multiple table rows in one row I have a table from where want to get such classes where 3rd position candidate got more marks than difference of 1st and 2nd.

class  position  name  total_marks
10     1         a     100
10     2         b     200
10     3         c     150
11     1         d     300
11     2         e     400
11     3         f     200

I want to write a query to find similar situation in all classes.

Try this query with 2 self joins:

select
a.class
,a.total_marks as 1st_Position_Marks
,b.total_marks as 2nd_Position_Marks
,c.total_marks as 3rd_Position_Marks
,abs (a.total_marks - b.total_marks) as Difference

from MyTable a
join MyTable b on a.class = b.class and a.position + 1 = b.position
join MyTable c on a.class = c.class and a.position + 2 = c.position

where
a.position= 1 and b.position=2 and c.position =3 and
c.total_marks > abs(a.total_marks - b.total_marks)

UPDATE. Some explaination.

Basically you are doing calculations on columns, so you need to put data in two columns alongside each other. To do than you need a join, not union. You need to join two rows, that would contain data for your calcultaion. As we need difference between position 1 and 2 we need to put this data on the same row. A.position +1 = B.position does exactly that. as you do not need other permutations of position, I added the filter.


Preventing concurrent sends of emails from my application I have a system that sends emails for me and my clients every day in the morning.  This process is triggered automatically through access to my website, via an iframe.

Often, I receive the same email twice.

The simple process I created on a MySQL database to control this is as follows:

run this query:

SELECT nome, email
FROM clientes
WHERE DATE_FORMAT(lastsend, '%d/%m/%y') <> DATE_FORMAT(now(), '%d/%m/%y')

send emails
UPDATE clientes SET lastsend = Now();

I need a way to lock this down to prevent the same emails from being sent multiple times on the same day.

This should be your main two queries

STEP01 : Run this query

SELECT nome,email FROM clientes
WHERE lastsend < (DATE(NOW()) + INTERVAL 0 SECOND);

STEP02  : Run your process to send out emails

STEP03 : Run this query

UPDATE clientes SET lastsend = NOW()
WHERE lastsend < (DATE(NOW()) + INTERVAL 0 SECOND);

The expression (DATE(NOW()) + INTERVAL 0 SECOND); is always midnight today.

Look it over

STEP01 will check for lastsend to make sure it is before midnight today
STEP03 changes the lastsend to the current date and time for the emails that have not been sent out today
You can run STEP01 - STEP03 multiple times in the same and only new emails will go out

You should also make sure that lastsend is indexed. If it is not, then run this

ALTER TABLE clientes ADD INDEX (lastsend);

This will help speed up the query in STEP01

As for locking the table during this process, I would need to see the table structure. I have an idea concerning using the DB Connection ID. It goes something like this:

Add a column called sender to clientes table and index it

ALTER TABLE clientes ADD COLUMN sender TINYINT DEFAULT 0;

Once you create that column and index it as I specified, you can perform the following:

STEP01 : Mark everything before midnight in your DB Connection as midnight

UPDATE clientes SET
    sender   = CONNECTION_ID(),
    lastsend = (DATE(NOW()) + INTERVAL 0 SECOND)
WHERE
    lastsend < (DATE(NOW()) + INTERVAL 0 SECOND)
;

STEP02 : Run your process to send out emails where sender=CONNECTION_ID() and lastsend = (DATE(NOW()) + INTERVAL 0 SECOND)

STEP03 : Mark your stuff as sent

UPDATE clientes SET
    lastsend = NOW()
WHERE
    sender   = CONNECTION_ID() AND
    lastsend = (DATE(NOW()) + INTERVAL 0 SECOND)
;

This will restrict which DB Connection send which batch of email

Give it a Try !!!


DBCC show_statistics: density doesn't match what I calculate I've read in several articles that the density of a column is (1.0 / count(distinct columnname)).  I have a column with roughly 2.6 million distinct values over 2.7 million rows, but the column density in DBCC show_statistics even AFTER updating statistics with fullscan is .48.

I am confused as to where this number could be coming from.  Can anyone explain it to me?

The documentation for DBCC SHOW_STATISTICS says that Density (in the header) is:

  Calculated as 1 / distinct values for all values in the first key column of the statistics object, excluding the histogram boundary values.

It also says:

  This Density value is not used by the query optimizer and is displayed for backward compatibility with versions before SQL Server 2008.

You are using SQL Server 2005, so this value may be relevant to you in some circumstances, but I don't know exactly when 2005 would use that Density value.


Filling Empty Values when Calculating Standard Deviation I am currently trying to calculate the standard deviation of the amount of products we have sold at our company over the past 12 months.

I have a table that contains every date over the past year and is updated moving forward. I use a left outer join on our sales table to come up with 4 columns:

Year | Month | Part ID | Value

The Year and Month columns come from the date table, and the Part ID and Value represent a summation from the sales table, where the summation is grouped based on Sales Year, Sales Month, and Part ID.

Now, I can calculate standard deviation fine using this.

A simple

SELECT
    PART_ID,
    STDEV(VALUE) STANDARD_DEV_VALUE
FROM QUERYX
GROUP BY PART_ID

This works fine when I have a sale for a given item each month, as it returns 12 months of data. But, if a part does not have  a sale, I do not have data for that month.

For example, here is a possible output:

YEAR | MONTH | PART | VALUE
2016 | 1     |   X  | 100.0

when what I need is:

YEAR | MONTH | PART | VALUE
2016 | 1     |   X  | 100.0
2016 | 2     |   X  | 0
2016 | 3     |   X  | 0

 and so on upto a full 12 months...

If I don't have this, I am not accurately calculating the standard deviation.

So, can someone help explain to me how I could fill in the Part ID when there are no sales? I have no link between the Part and the Dates, so I can only link on the sales dates. Thus, my issue.

This needs to be done in a single query, as there is other data being pulled here. Cursors are not an option. A temporary table is possible but it would have to execute very quickly.

I was able to resolve the issue through the use of a CROSS JOIN.

  The SQL CROSS JOIN produces a result set which is the number of rows
  in the first table multiplied by the number of rows in the second
  table if no WHERE clause is used along with CROSS JOIN.This kind of
  result is called as Cartesian Product.

I used this in a sub-query to generate a list of dates with every Part ID. This was joined to my other table, allowing me to substitute a value of 0 where null. The standard dev. then calculates correctly.


Using “nolock” hint for the SQL View (MSSQL) Could you help evaluate the "nolock" hint necessity for the View.

From the view definition, we notice that there’s no WITH(NOLOCK) SQL hint added to any table reference.  Thus, all the reports regarding the view have a potential to block other transactions (insert/update/delete) while it executes.

In case you don't know, NOLOCK is equivalent to the transaction isolation level named "READ UNCOMMITTED."  I'd recommend reading this blog post from Paul White to understand some of the data integrity implications of using the NOLOCK hint: The Read Uncommitted Isolation Level

To your question though, you mention this justification for potentially adding the hint:

  Thus, all the reports regarding the dbo.rptdepitemdetailsResearch have a potential to block other transactions (insert/update/delete) while it executes.

I would say not to worry about this unless your users / applications are actually experiencing blocking due to this reporting view.

If there is blocking, you could use the READ UNCOMMITTED isolation level, but note (from Paul's blog post) that it's possible to see totally invalid data in the report.

Another option would be to enable SNAPSHOT ISOLATION in the database:

ALTER DATABASE YourDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON;

And then have any code that queries your view use the SNAPSHOT isolation level:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

This will prevent your reporting query from blocking insert / update queries, and will also make the reporting query accurate to the point in time the query started.

There is overhead to using this option, including increases to the database size and increased usage of TempDB.  So make sure you test accordingly.


Array integer[]: how to get all distinct values in a table and count them? I'm not so good with SQL (PostgreSQL). Here is what I want to do:

I have a table, fields:

id SERIAL
inet INET
ports integer[]

 id |    inet    | ports
----+------------+------------
  2 | 1.2.2.1    | {80}
  1 | 1.2.3.4    | {80,12}
  ...

How can I

get all used "ports" values in this table: 80, 12
count how many inet addresses are on specific port:

Like this:

  port  | count
--------+------------
 12     | 1
 80     | 2
  ...

If anyone is looking for a Django version of it:

class Unnest(Func):
    function = 'UNNEST'

Model.objects \
.annotate(port=Unnest('ports', distinct=True)) \
.values('port') \
.annotate(count=Count('port')) \
.order_by('-count', '-port')

You can use UNNEST.

select unnest(ports) as port, count(*) from foo group by port;

Using more than one UNNEST in the same query (or the same select list, anyway) is confusing and is probably best avoided.