SSIS Script Component - How to modify the Output0Buffer

Everything in a Transformation is done within Input0_ProcessInputRow
The solution would essentially be

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
         listOfData.Add(new ClockData
         {
             TimeClockID=Row.TimeClockID,
             PersonID=Row.EmployeeCode,
             LocationCode=Row.ClockInServiceContextID,
             ClockInDateTime=Row.ClockInDateTime,
             ClockOutDateTime=Row.ClockOutDateTime
         });
         Output0Buffer.AddRow();
         Output0Buffer.EmployeeCode = item.PersonID;
         MessageBox.Show(item.PersonID);
}

You may be able to perform the output steps in PostExecute, CreateNewOutputRows is not run in Transformation, only in Destination scripts.


Why database snapshot creation is faster than normal database backups?

Mostly because a snapshot isn't really a backup.

As explained in How database snapshots work

  The snapshot uses one or more
  sparse files. Initially, a sparse file is an essentially empty file
  that contains no user data and has not yet been allocated disk space
  for user data. As more and more pages are updated in the source
  database, the size of the file grows. When a snapshot is taken, the
  sparse file takes up little disk space.

A snapshot doesn't actually contain any data until you start making changes to the original database.  Basically any time you change a page in the database that original page gets written to the snapshot.  If you then make another change to the same page nothing needs to happen since the original already exists in the snapshot. Because of this the size of the snapshot will be from 0 to the size of the database at the time of the snapshot.

So when you restore back to the snapshot it just writes back those changed pages.

A backup (a full one at least) is a copy of the database written to a file.

When you restore from backup it writes ALL of the pages, not just changes (again assuming a full backup).

That's why creating/recovering from a snapshot is so much faster.

Here is some further reading:

Database Snapshots


Returning correct timezone offset from join

If you are not able to modify or generate a new Timezones table without daylights, you could use more than one solution.

Using CROSS APPLY

select m.meetingid,
       m.start,
       m.timezone,
       m.localstarttime,
       t.offsetmin
from   meetingtable as m
cross apply (select top 1 offsetmin
             from timezone
             where timezone = m.timezone) t;

Using sub-query

select m.meetingid,
       m.start,
       m.timezone,
       m.localstarttime,
       (select top 1 offsetmin
        from timezone
        where timezone = m.timezone) offsetmin
from   meetingtable as m;

Using GROUP BY

select    m.meetingid,
          m.start,
          m.timezone,
          m.localstarttime,
          t.offsetmin
from      meetingtable as m
left join (select timezone,  max(offsetmin) offsetmin
           from   timezone
           group by timezone) t
on         t.timezone = m.timezone;


Calculate row value based on previous and actual row values

Assuming that stmnt_date has a UNIQUE constraint, this would be fairly easy with window/analytic functions:

SELECT
    s.stmnt_date, s.debit, s.credit,
    SUM(s.debit - s.credit) OVER (ORDER BY s.stmnt_date
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW)
        AS balance
FROM
    statements AS s
ORDER BY
    stmnt_date ;

Unfortunately, MySQL does not (yet) have implemented analytic functions. You can solve the problem either with strict SQL, by self-joining the table (which should be rather inefficient although working 100%) or by using a specific  MySQL feature, variables (which would be quite efficient but you'd have to test it when upgrading mysql, to be sure that the results are still correct and not mangled by some optimization improvement):

SELECT
    s.stmnt_date, s.debit, s.credit,
    @b := @b + s.debit - s.credit AS balance
FROM
    (SELECT @b := 0.0) AS dummy
  CROSS JOIN
    statements AS s
ORDER BY
    stmnt_date ;

With your data, it will result in:

+------------+-------+--------+---------+
| stmnt_date | debit | credit | balance |
+------------+-------+--------+---------+
| 2014-05-15 |  3000 |      0 |    3000 |
| 2014-06-17 | 20000 |      0 |   23000 |
| 2014-07-16 |     0 |   3000 |   20000 |
| 2014-08-14 |     0 |   3000 |   17000 |
| 2015-02-01 |  3000 |      0 |   20000 |
+------------+-------+--------+---------+
5 rows in set (0.00 sec)


I'm an idiot!   All I needed to do was add ORDER BY FIELD(id,5,2,6,8,12,1...)
to my code.  I like it when the answer is an easy one.  I just wish it wouldn't take me so long to figure out!


Postgres crosstab query on multiple columns

The values clause here:

values(
          ('(HOUSEFIRE,HISTORICAL)'::product_status),
          ('(HOUSECONTENT,HISTORICAL)'::product_status),
         ('(LIABILITY,ARCHIVED)'::product_status)
    )

produces a single line with 3 columns, whereas you want 3 lines with a single column, as in:

values   ('(HOUSEFIRE,HISTORICAL)'::product_status),
         ('(HOUSECONTENT,HISTORICAL)'::product_status),
         ('(LIABILITY,ARCHIVED)'::product_status)


How to get row_count diagnostic after EXPLAIN ANALYZE in Postgres?

Having come to this conclusion that there isn't an easy built-in way, here is my quick solution for this:

Create this function to capture the explain analyze plan result:

CREATE OR REPLACE FUNCTION get_explain_analyze(p_sql text)
RETURNS TABLE("QUERY PLAN" text)
AS
$BODY$
BEGIN
  RETURN QUERY EXECUTE 'EXPLAIN ANALYZE '||p_sql;
END;
$BODY$
LANGUAGE plpgsql;

Run this to get the row count (only tested for select/create temp table statements - regex to the 2nd rows= number):

SELECT substring("QUERY PLAN" from 'rows=.+rows=(\d+)') AS row_count
FROM get_explain_analyze('SELECT 1')  -- query here
LIMIT 1;


Unable to use datetime2(0) in Partition Function

Removing the NULL as the first partition value should get you past the error.
(UPDATE - Read the comments further down from Dan's post - he actually DOES recommend the action of the OP - you just need to use convert(datetime2(0),null)

Also - check out Dan Guzman's Table Partitioning Best Practices.

Well - after re-reading Dan's post, he actually DOES recommend creating the first partition with a NULL boundary for RANGE RIGHT,

  I suggest specifying a NULL value for the first boundary of a RANGE
  RIGHT partition function.  This NULL boundary serves as the upper
  boundary of the permanent first partition as well as the lower
  boundary for the second partition containing data outside the expected
  range.  No rows are less than NULL so the first partition will always
  be empty.  It is therefore safe to map the first partition to the
  previously mentioned dummy filegroup even if you need to house data
  outside the expected range.  That being said, there is no harm in
  mapping the first partition to another filegroup other than lack of
  clarity.


SQL Server Management Studio 2016 (Release 16) keeps on crashing

You are not alone. See more details here.

Posted by Microsoft:

  Turns out there's a thread leak in a utility class. The number of
  threads leaked will be proportional to the number of registered
  servers you have, among other things. A fix is coming in the next
  release


Concurrent backups (full and transaction) and testing recovery model

Working out which Log Backup to restore is all based on LSN's. You have to apply all log backups since the full (or differential, but you didn't mention them) backup, including log backups taken while the full backup was in progress.

There is no danger in trying to restore a log backup out of sequence, it just won't work. Kin answered above how to find the actual log backups you need to restore.

It's important that you have a continuous chain of log backup files. If you are missing one, or if it's corrupt then you can't restore anything after that point.


SQL Continuous Table Updating: Best Practices?

This is something that I recently did with a table of mine. I used CROSS APPLY and used this page as my guide. Perhaps this can help you?


There is no need to grant the CONNECT role.  In 10.2, Oracle finally reduced the set of privileges assigned to that role to just CREATE SESSION but in previous versions, that role has many more privileges than the name would imply.

It would be more secure to create one users-- one that owns the tables, procedures, etc. but that does not have CREATE SESSION privileges and one that has appropriate privileges on the various objects that the application can use to connect to the database.  That allows you to do things like prevent the application user from dropping tables or from deleting data from logging tables.

Additionally, the owner of the tables is going to need to be granted quota on whatever tablespace or tablespaces that user's tables are going to be created in.  You could grant the owner the UNLIMITED TABLESPACE privilege but it is more secure to grant them a smaller quota on whatever tablespaces they actually need to use.


PostgreSQL Backup and restore, sizes are different

I Will answer my own question,

From: https://stackoverflow.com/questions/13505785/postgresql-database-size-is-less-after-backup-load-on-heroku

The reason of this is its MVCC system. Every time you UPDATE any record in a database it creates another "version" of this record instead of rewriting the previous. This "outdated" records will be deleted by VACUUM process, when there will be no need in them.

I've search in PostgreSQL docs and I found:
Unlike traditional database systems which use locks for concurrency control, PostgreSQL maintains data consistency by using a multiversion model (Multiversion Concurrency Control, MVCC). This means that while querying a database each transaction sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This protects the transaction from viewing inconsistent data that could be caused by (other) concurrent transaction updates on the same data rows, providing transaction isolation for each database session.

The main advantage to using the MVCC model of concurrency control rather than locking is that in MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading.

From: https://stackoverflow.com/questions/924993/postgresql-database-size-increasing
If the dead tuples have stacked up beyond what can be accounted for in max_fsm_pages, a regular VACUUM will not be able to free everything. The end result is that the database will grow larger and larger over time as dead space continues to accumulate. Running a VACUUM FULL should fix this problem. Unfortunately it can take a very long time on a large database.

If you're running into this problem frequently, you either need to vacuum more often (autovacuum can help here) or increase the max_fsm_pages setting. When running VACUUM VERBOSE it will tell you how many pages were freed and give you a warning if max_fsm_pages was exceeded, this can help you determine what this value should be. See the manual for more information.

From: postgres backup / restore: restored database much smaller?

Maybe it's just index bloat. VACUUM FULL does not help with index bloat, on the contrary, as said in the doc for 8.4:

  The FULL option is not recommended for routine use, but might be
  useful in special cases. An example is when you have deleted or
  updated most of the rows in a table and would like the table to
  physically shrink to occupy less disk space and allow faster table
  scans. VACUUM FULL will usually shrink the table more than a plain
  VACUUM would. The FULL option does not shrink indexes; a periodic
  REINDEX is still recommended. In fact, it is often faster to drop all
  indexes, VACUUM FULL, and recreate the indexes.

(in more recent versions, this advice has disappeared because VACUUM FULL has been reimplemented differently).

See Routine reindeing and the REINDEX command.

The easiest way to reindex is to connect to the database with the db user that owns it and issue:

REINDEX database test_db;

Ideally, it should be done immediately after VACUUM FULL and the database should shrink to its lowest possible size at this point in time.


Avoiding a full re-initialization when making changes to transactional replication

I've done that but I've still sometimes had it prompt to reinitialize the whole thing.

The snapshot will only contain the new table that has been added.

  Also, we frequently remove a table from replication but need to add it back in later, are there scripts out there to preserve the replication settings so this process is easier?

Script out replication - right click - script to file. Change the parameters @immediate_sync and @allow_anonymous should be set to 0.

Run the script post completion of your work.

Also, check my answer for Transactional replication altering tables and adding stored procedures that will help when you are adding columns to existing replication.


what better way to multiple ON conditions

There's nothing wrong with the query regarding the joins and how to find the values (except some details). It should be efficient if there are appropriate indexes. There is nothing horrible or "unperformatic" about it.

Things that need addressing:

Syntax issues:

use the correct columns for joining (c.accountManagerID, c.attendantID, u1.userID, etc).
there is noLEFT INNER JOIN. There are LEFT OUTER JOIN and INNER JOIN (which can be written respectively as LEFT JOIN and JOIN).
add the table's name or alias in each column reference (c.customerID, not customerID). This won't always cause an error but when you'll have  columns from multiple tables with same name, it helps avoiding them.
don't use single quotes for aliases, only for string literals. For aliases, remove them or use back quotes (`alias`) if necessary. MySQL allows it but it's confusing and even the official documentation suggests to avoid using single quotes for aliases.

Logic issues:

use LEFT JOIN if the two columns (accountManagerID and attendantID) are nullable (i.e. if there are customers with no manager or no attendant assigned). Otherwise, with INNER joins, you will not see such customers in the results.

SELECT c.customerID        AS ID
     , c.customerName      AS Customer
     , u1.accountManagerID AS Manager
     , u2.attendantID      AS Attendant
FROM Customers c
    LEFT JOIN Users u1 ON u1.userID = c.accountManagerID
    LEFT JOIN Users u2 ON u2.userID = c.attendantID ;


How to track changes between nightly MySQL database dumps

What you're referring to is called a delta, incremental, or differential backup, which is certainly natively supported by MySQL. You might want to consider backing up the deltas to an intermediary database that you can run your reports off of.  That data can then be added to the up-to-date DB.

I would suggest using an administration tool like Navicat for MySQL for setting up the backups.  It makes it easy to schedule and configure the backup rules.  You can compare and sync data between databases/schemas with detailed analytical wizard process.

As a bonus, it can produce really eye-catching reports.

Hope that helps!


using PERFORM to insert a string of SELECT statement into a temp table

The issue with your SQL is with the type (or return type).

In

SELECT * FROM validation();

You are using validation() as if the returned value from the function were a table. But you defined the function to return a single value

CREATE OR REPLACE FUNCTION validation()
  RETURNS text AS $$

So, the said function can't be used in the FROM clause.

If you are trying to use the side-effects of the function (PERFORM 'INSERT INTO ...), then you can use it in the SELECT list directly, e.g.

SELECT validation();

If you really intend to use the returned relation, then you need to make validation a Set Returning Function (SRF).

--EDIT--

In addition to the return type issue, another error in the IF statement is causing the actual error message (rather than the error in return type, which needs to be fixed as well).

If I understand what you are looking for (e.g. to test whether col3 = false for some rows in temp_table), one way that I think can achieve it, is to use a query, e.g.:

IF (SELECT 1 FROM temp_table WHERE  col3 = false) = 1 THEN
  RAISE NOTICE 'there is a false value';
END IF;

The subquery (SELECT 1 FROM temp_table WHERE  col3 = false) returns 1 if FALSE exists, and NULL otherwise.

I don't think it's valid to use temp_table.col3 directly.


Adding a column to the view which shows the date in format YYYYMM

(Assuming DB2 for Linux/Unix/Windows here, since you don't specify, but this will probably also work on the other platforms.)

You don't have to do funky date math, there is a built-in function VARCHAR_FORMAT that will do it for you:

SELECT VARCHAR_FORMAT(CURRENT DATE, 'YYYYMM')
FROM SYSIBM.SYSDUMMY1


When you create the clustered index on SampleView, SQL Server has to materialize the data in the index.  Normally, a view does not have a separate copy of the data.  It is normally just a stored query.  An indexed view changes that.

Since the indexed view has a separate copy of the data, it can be sorted in whatever way you'd like.  Normally, of course, you wouldn't have an indexed view that simply replicates all the data in a table.  You'd normally only create an indexed view if you're going to materialize a subset of data based on various conditions or if you're going to materialize some aggregate data.


Brian,

Unfortunately the Windows event log and SQL log will not provide that information.

Fortunately there are several ways to audit a such activity. SQLShack has a good explanation on the various techniques and 3rd party tools

https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/

I had a similar situation at my work where I need to track some unintentional updates to a table that seemed to happen a couple of times a week.

I decided to use out of the box SQL Server auditing features. I was able to track down who, where and when certain DML changes were occuring..

You need to first setup a server audit then create a database audit..

You can tailor your database audit to "update" statements on your specific table.. Limiting the audit to your table and "update" statements will limit the amount of auditing taking place.

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-2017

You set up the server audit to log the activity to a file locally or somewhere on your network. Make sure the SQL Service account has write permissions to whatever location you choose.

You will have to use the function sys.fn_get_audit_file to read the audit file.

https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql?view=sql-server-2017

After you have solved the issue you can simply disable or delete the server and database audit.


Performance Metrics be on Own Server or Own Server Instance

The issue is mostly because of resource competition. Any tool or method you use to monitor your server will introduce observer overhead. This goes for traces, Extended Events, home grown scripts, or third party monitoring tools.

The goal of putting them on a separate server is to reduce further overhead.

Monitoring queries write a lot to disk at regular intervals, and often get hit with rollup/aggregation queries to report on metrics, smooth data out, etc.

Those queries are usually big and ugly because those tables don't have a lot of indexes. Lots of indexes to help reporting queries would just slow those big/regular writes down.

Production resources should be solely for production data. It's not just a rule for monitoring tools, but for dev, QA, UAT, or demo databases sitting in prod. They're a potential performance liability that's easy to avoid.


SQL Server 2014 Latest CU / Service Pack

For the latest CU and builds (this article will also include service packs when released):
SQL Server 2014 build versions

As of 2015-01-13, SQL Server 2014 is currently on CU5, which was released Dec. 17, 2014.
Service pack 1 has not yet been released.

Another similar article, specific to Service Packs:
How to obtain the latest service pack for SQL Server 2014


Multi-expression indexes in PostgreSQL not using being used as expected

It looks to me like there is a conflict between the single-expression and the multi-expression indexes.

All applicable indexes are candidates, if that's what you mean by "conflict". But that's the extent of it. Postgres estimates execution time based on table and index statistics in combination with cost settings, and the best estimate wins.

If Postgres expects one of your two conditions to be selective enough, it will likely take a matching single-column index and filter possible (few!) false positives in a FILTER step with the second condition.

However, that is certainly not the case for the data distribution you mention in the comment:

  20,000 rows. b=1 for 10,000, a=1 for 2. One row for a=1,b=1

@ypercube already demonstrated in his sqlfiddle that your test should use the multicolumn index. Even more so with the data distribution you describe.

If you provide the output of EXPLAIN (ANALYZE, BUFFERS) instead of just EXPLAIN, we might be able to see more. I would guess you didn't run ANALYZE after creating the expression index, which is required to update statistics.  autovacuum will kick in after some time, but not fast enough if you run your test immediately after creating the index (and never for temporary tables!).

ANALYZE Table1;

Come to think of it, we (that is: you & me) solved this problem before:

PostgreSQL partial index unused when created on a table with existing data

Also related:

Index that is not used, yet influences query

That aside, Postgres 9.3 is getting old. There have been 3 major release since. If you run into problems like this I suggest to test a current version and see if the problem is still there.


Sessionizing Web Log Data using MySQL (perhaps by simulating window functions)

You've provided your own answer - MySQL doesn't have window-functions (AKA analytic functions). My advice to you would be to switch to PostgreSQL for that reason alone.

Also, PostgreSQL supports SET operators, CHECK constraints, GIS, calculated fields (although MariaDB does support these),  and CTEs (Common Table Expressions, AKA the WITH clause) - although see Justin Swanhart's answer here.


If shared memory, named pipes, TCP/IP network protocols are all enabled which has priority?

If no protocol is specified , the connection will try based on the order. This is specified in the Enabling a Protocol section in Choosing a network protocol


Why am I connecting securely to MySQL when I don't have client certificates?

Yes the connection is encrypted. I think you are getting a few things mixed up here. Let me try to simplify it for you:

SSL is a protocol for exchanging data across a secure network, and in order to establish a connection, the server must have a certificate to use. In order to obtain a certificate you must be issued one. This is generally done by a Certificate Authority (CA) or by using a self-signed one, but we will keep it simple here and stick with CA's.

If the certificate you used when setting up SSL is valid, and is trusted by the client attempting to establish a connection, then the connection attempt should succeed. The client does not need to present a certificate here, but it does need to trust the issuer of the certificate. This is generally done by having a copy of the CA certificate on your machine and verifying that the certificate presented by the server is signed/issued by the CA.This corresponds roughly to the --ssl-mode=VERIFY_CA and --ssl-mode=VERIFY_IDENTITY (which checks that the host name in the certificate matches the server you are trying to connect to).

MySQL allows the client to select the certificate to use when verifying the identity of the server using the --ssl-ca, --ssl-cert and --ssl-key options and in order for the transport connection to be successful, the certificate presented by the client must be issued by the same CA that issued the server certificate. This is optional, and if you don't present one then the client will look for a corresponding certificate in its store.

Additionally, once you have established an SSL connection you can authenticate an account using certificates (generally using some kind of PKI technology) which would essentially replace using passwords to log in - but the key thing to understand here is that by the time the process gets to the point where you are attempting to log in, the SSL connection has already been established. When you use this method, you generally find that the client certificates have been issued by the same CA as the one that issued the server certificate.

I use the following analogy to explain it to people I teach:

  When my daughter was born the only people she knew she could trust was her mother and me. Then as she got older and more people came into her life we told her who she could and couldn't trust. In this scenario, my wife and I act as the Certificate Authority verifying the trustworthiness of others. If we have not told our daughter she can trust someone then she will not speak to them, much like a how a client will act if it does not trust a server.

The topic of certificates is quite extensive but the key for you is to understand the difference between the client trusting the server enough to establish a secure connection and you then authenticating across that connection using a client certificate.


This query simulates a RANK() function.

SELECT T1.ID, (SELECT COUNT(*)
               FROM YOUR_TABLE T2
               WHERE T1.ID = T2.ID AND T1.OP_ID >= T2.OP_ID) AS Rank
FROM YOUR_TABLE T1
ORDER BY ID, OP_ID

It works only if there is an unique ID field that allow to count records where:

T1.OP_ID >= T2.OP_ID


Use "LEN" function in "WHERE" clause in "CREATE UNIQUE INDEX"

One method to workaround the filtered index restriction is with an indexed view:

CREATE TABLE dbo.Table01 (
  Column01 NVARCHAR(100)
);
GO

CREATE VIEW dbo.vw_Table01_Column01_LenOver5Unique
WITH SCHEMABINDING AS
SELECT Column01
FROM dbo.Table01
WHERE LEN(Column01) >= 5;
GO

CREATE UNIQUE CLUSTERED INDEX cdx
    ON dbo.vw_Table01_Column01_LenOver5Unique(Column01);
GO

INSERT INTO dbo.Table01 VALUES('1'); --success
INSERT INTO dbo.Table01 VALUES('1'); --success
INSERT INTO dbo.Table01 VALUES('55555'); --success
INSERT INTO dbo.Table01 VALUES('55555'); --duplicate key error
GO

EDIT:

  How should I define the view if I have two columns in the index?
      CREATE UNIQUE INDEX UIX_01 ON Table01(column01, column02) WHERE LEN(column01)>=5

The indexed view approach can be extended for a composite key by adding other key columns to the view definition and index. The same filter is applied in the view definition but uniqueness of qualifying rows enforced by the composite key rather than the single column value:

CREATE TABLE dbo.Table01 (
   Column01 NVARCHAR(100)
  ,Column02 NVARCHAR(100)
);
GO

CREATE VIEW dbo.vw_Table01_Column01_LenOver5Unique
WITH SCHEMABINDING AS
SELECT Column01, Column02
FROM dbo.Table01
WHERE LEN(Column01) >= 5;
GO

CREATE UNIQUE CLUSTERED INDEX cdx
    ON dbo.vw_Table01_Column01_LenOver5Unique(Column01, Column02)
GO

INSERT INTO dbo.Table01 VALUES('1','A'); --success
INSERT INTO dbo.Table01 VALUES('1','A'); --success
INSERT INTO dbo.Table01 VALUES('55555','A'); --success
INSERT INTO dbo.Table01 VALUES('55555','B'); --success
INSERT INTO dbo.Table01 VALUES('55555','B'); --duplicate key error
GO


What could cause waiting for table metadata lock?

Thanks to jynus for advising on working with general log, the problem has been solved!

As in most of the cases, the error was quite simple, here is the description:

On certain occasions, a function was invoked, that performed the following:

Lock several tables in the DB
Do its queries
Send a message to chat with the results
Unlock tables

The message to the chat was sent via a socket with connection timeout of 10 seconds. Sometimes, after step 1, at step 3, the chat was busy logging on the new user, and was trying to access the tables, that are locked. So the loop was closed - the chat was waiting for the unlock and the function was waiting for processing its connection request.

10 seconds was enough for the other requests to reach the max_connections limit and lock the database.

The only thing that puzzles is me, is why the state of all queries was "Waiting for table metadata lock".

After switching the places of steps 3 and 4, the problem was no more.

Thanks for help!


The job failed. The owner (pc\user) of job FULL DB BACKUP does not have server access

Try to create the PC\user as a login and give him the backup privilege in your database with this code.

USE [master]
GO
CREATE LOGIN [SERVER\Test1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [mydb]
GO
CREATE USER [SERVER\Test1] FOR LOGIN [SERVER\Test1]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [SERVER\Test1]
GO


Postgresql query slowed with table growth

You are assuming that the problem is the switch to the bitmap scan.  That is very unlikely.

The likely problem is that your data had to be read from disk, either because it has grown too large to fit in cache (in which case more RAM would help), or the cache was cold when you happened to run the test.

The switch to bitmap scan is likely a response to the same thing that caused the problem, the growth in data.

You could cluster the table on the index t_feature_ind, which would make it more efficient to pull out all of the data with any given feature.


Why SSRS use Share point server for deployment of reports?

Technet has a decent article.
https://technet.microsoft.com/en-us/library/bb326358(v=sql.105).aspx


What is the meaning of this anomaly warning message?

Based on the sample table in your link, I'd suggest it's taking issue with fields such as:

phone1
phone2

or

notes1
notes2
notes3

This may indicate a design flaw (but not always), and if so, one that can probably be corrected by more descriptive field names or perhaps a child table.

For example, someone might have more than one phone number... what if someone has three? Seven? Maybe data like this should be in a subtable, and perhaps even with some sort of indicator of what type of phone number it is, whether it's a preferred phone number, etc. Alternatively, maybe just renaming the fields to phone_home and phone_cell will be enough to eliminate confusion and allow you to collect all of the data you need.


extra steps after changing storage engine and adding index

You came to right place. Let's look at an example.

Suppose you have the following MyISAM table:

CREATE TABLE mydb.mytable
(
    id int not null auto_increment,
    firstname varchar(20),
    lastname varchar(20),
    straddr varchar(40),
    city varchar(40),
    state char(2),
    primary key (id)
) ENGINE=MyISAM;

You said you want to do two things:

Change the table to InnoDB
Add an index

For the above table, here is how to convert the table to InnoDB and add an index on names

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ALTER TABLE mydb.mytable ADD INDEX name (lastname,firstname);

Here is a way to make the table InnoDB, index it, and keep the original as MyISAM

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
ALTER TABLE mydb.mytablenew ENGINE=InnoDB;
ALTER TABLE mydb.mytablenew ADD INDEX name (lastname,firstname);
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytableold;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;

CAVEAT

This answer is really an oversimplification of the conversion. There are still other aspects of setting up InnoDB (Here is a Pictorial Representation of InnoDB's Infrastructure).  Here are a few of my past posts on such configuration due diligence:

Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?
Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?
Mar 04, 2011 : Clarification on MySQL innodb_flush_method variable
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Sep 12, 2011 : Possible to make MySQL use more than one core?
Feb 22, 2012 : Ibdata usage and Recommendations?
Apr 01, 2012 : Is innodb_file_per_table advisable?


Nested Cursors in Mysql problem with FETCH

You are trying to close the cursor after it is already closed. If no more rows=1 is true, it closes the cursor. Then exists the loop. Then tries to close the cursor again.


Efficient way to paginate in sql server with 50M plus data (sql server 17)

There is simply no cheap way to get the 12753rd page out of a 50M row result set.

  [offset .. fetch] works fine for small offset. But, as the offset value increase. It gradually starts to take a lot of time.

Which is normally fine, as you rarely page deep into a large result set.

  [The "Key Seek Method"] failed when you want to move from page 1 to page 3.

Yep.  But it's so much more efficient that you simply avoid skipping pages.


How do you handle maintenance window for DDL commands on Primary replicating to readable secondary AGs

disable read on secondary:
USE [master]
GO
LTER AVAILABILITY GROUP [AG-Something]
MODIFY REPLICA ON N'SQLSERVER-SECONDARY' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS =  NO))
GO

do your ddll workload on primary

re enable read on secondary
USE [master]
GO
ALTER AVAILABILITY GROUP [AG-Something]
MODIFY REPLICA ON N'SQLSERVER-SECONDARY' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS =  READ_ONLY))
GO

you can chek the AG status between the step 2 and 3 with this query:
SELECT
ar.replica_server_name,
adc.database_name,
ag.name AS ag_name,
case HDRS.is_primary_replica
when 1 then 'Primary Replica'
else 'Secondary Replica'
end as Replica,
HDRS.synchronization_state_desc,
HDRS.synchronization_health_desc,
HDRS.recovery_lsn,
HDRS.truncation_lsn,
HDRS.last_sent_lsn,
HDRS.last_sent_time,
HDRS.last_received_lsn,
HDRS.last_received_time,
HDRS.last_hardened_lsn,
HDRS.last_hardened_time,
HDRS.last_redone_lsn,
HDRS.last_redone_time,
HDRS.log_send_queue_size,
HDRS.log_send_rate,
HDRS.redo_queue_size,
HDRS.redo_rate,
HDRS.filestream_send_rate,
HDRS.end_of_log_lsn,
HDRS.last_commit_lsn,
HDRS.last_commit_time
FROM sys.dm_hadr_database_replica_states AS HDRS
INNER JOIN sys.availability_databases_cluster AS adc
ON HDRS.group_id = adc.group_id AND
HDRS.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
ON ag.group_id = HDRS.group_id
INNER JOIN sys.availability_replicas AS ar
ON HDRS.group_id = ar.group_id AND
HDRS.replica_id = ar.replica_id


Why is this query failing with invoker rights?

Because this is a new 12c security feature.

User x creates a harmless package and everyone starts using it, even users with higher privileges, like SYS.

Now user x knows that SYS uses this package regularly, so user x could replace the contents of this package with some malicious code any time and do anything in the database, knowing the code will be ran by SYS sooner or later.

12c prevents this with this new feature:

INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges

But if SYS trusts x, the following can be done:

grant inherit privileges on user sys to x;

This will allow x to inherit the privileges of SYS when running code like the above.


A better option if possible would be to change out to using database mail instead of SQL mail.  This uses SMTP and takes the MAPI client out of the config.

It would require that anything that's calling xp_sendmail be changed to use sp_send_dbmail.  If you are only using this for SQL Agent emails then this is a very easy change.


generate_series : how to remove or no generate the last one

CREATE OR REPLACE FUNCTION slots(start_date TIMESTAMP, end_date TIMESTAMP, delay INTERVAL) RETURNS SETOF TIMESTAMP AS $$
BEGIN
RETURN QUERY SELECT * FROM generate_series(start_date, end_date - delay, delay);
END
$$
LANGUAGE plpgsql;

Last timeslot is not generated even it is partial (not empty at all) - i.e.

SELECT slots('2019-12-19 10:00', '2019-12-19 12:10', '30 mins')

will not generate '2019-12-19 12:00' timeslot because it has 10 mins length.

If such partial timeslot must be generated, then substract '1 second'::interval instead of delay from end_date.


How to update a yes/no field to yes when all related fields in another table are marked yes

In general a design like that is a bad idea, a better idea would be to start using a view, and in that view you add a column where you calculate if all subtasks are done or not. That way every time you query the view you calculate the correct value without having to rely on things like triggers to update your actions_complete every single time something happens.


Deleting millions of records on SQL Server 14.0

For large deletes in batches, consider specifying a clustered index key range instead of using TOP so that a clustered index seek can be used in the plan. Below is an example.

DECLARE
      @BATCHSIZE INT = 4000
    , @ITERATION INT = 0
    , @TOTALROWS INT = 0
    , @MSG VARCHAR(500)
    , @STARTTIME DATETIME
    , @ENDTIME DATETIME
    , @StartValue int = 0
    , @EndValue int = 0
    , @MaxValue int = (SELECT MAX(PK) FROM [mydb].[dbo].tableA);

SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;

WHILE @StartValue <= @MaxValue
BEGIN

    SET @EndValue = @StartValue + @BATCHSIZE;

    SET @STARTTIME = GETDATE();

    DELETE FROM [mydb].[dbo].tableA
    WHERE [mydb].[dbo].tableA.Code NOT IN (
            SELECT Code
            FROM [mydb].[dbo].TableB
        )
        AND [mydb].[dbo].tableA.PK >= @StartValue
        AND [mydb].[dbo].tableA.PK < @EndValue;

    SET @TOTALROWS=@TOTALROWS+@@ROWCOUNT;
    SET @ITERATION=@ITERATION+1;

    SET @ENDTIME = GETDATE();

    SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + '  >> ' + CAST(DATEDIFF(millisecond, @STARTTIME,@ENDTIME) AS VARCHAR);

    RAISERROR (@MSG, 0, 1) WITH NOWAIT;

    SET @StartValue = @EndValue;

END;
GO


MySQL limits on huge number return zero rows

Table have 1,000,000 records but It look like Table doesn't have 800000+ records where

`articles`.`hash` NOT
IN (
'1z8y'
)

But table have 2000+ records where

`articles`.`hash` NOT
IN (
'1z8y'
)

LIMIT 800000,10 will return 10 records after 800000th records but it looks like you didn't have 800000 records which satisfy your where clause condition.

You can find no. of records which satisfy your where clause using this query

SELECT Count(*) FROM `articles`
WHERE `articles`.`hash` NOT
IN (
'1z8y'
)


Updating multiple columns using case conditional clause

Check:

UPDATE @tt
SET @tt.TypeId1 = t.TypeId1, @tt.TypeId2 = t.TypeId2
FROM @tt
JOIN ( SELECT Id,
              MAX(CASE WHEN TypeId=1 THEN number END) TypeId1,
              MAX(CASE WHEN TypeId=2 THEN number END) TypeId2
       FROM @dd
       GROUP BY Id ) t
ON @tt.Id = t.Id


Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?

Sure, in SQL Server at least you can set up a linked server to other SQL Servers, Oracle, MySQL etc. and pull data from them. There are other approaches as well (such as OPENQUERY). An example would be:

SELECT * INTO dbo.localtable FROM OtherServer.database.dbo.TableName;

In Oracle I think the equivalent is database link but I confess that I have never used it.


What are the risks for logging across databases via a trigger?

There aren't many issues beyond what you have now that I can think of off-hand (I'm sure someone will correct me though!). Since you are proposing to have your audit DB on the same server, and, I presume, instance, then by using three part naming you are all set. You've already noted the unlikely possibility of someone removing the audit DB and likewise there's a slim chance someone might re-name it, move it, leave it in an uncertain state by some failed maintenance and so on, but again these aren't that likely. If all your DBs are in the same file group or all on oen disk/array of disks then there might be a performance implication, depending on the level of traffic you expect so you might want to consider where your audit DB physically resides quite carefully: Same SQL instance doesn't have to mean the same physical disk.

With regards to trigger design, you would most likely be using AFTER INSERT and you could handle exceptions inside the trigger so that they are not raised to the caller and therefore the transaction completes on the parent table. Of course, you wouldn't be getting audit data then and you must decide if that is a problem for you and if so you'll need a suitable strategy for raising an alert of some kind. But you'll need to consider if it is right and acceptable to swallow exceptions in that way: Most developers I know (and I am one!) would look like you'd asked them to do something un-natural with a chicken if you asked them to bury exceptions in that way.

I'm not sure I understand the rationale for a job which copies and deletes records: That's a programming smell to me. Why over-complicate things? You're right to question it.


From the manual:

  \t

  Toggles the display of output column name headings and row count footer. This command is equivalent to \pset tuples_only and is provided for convenience.


Trigger for updating a count of rows from a table into another table

Yes, you could do this via a Trigger. But you would need 3 triggers: 1 on insert, 1 on delete, 1 on update (which would do nothing, unless you modify the Item column). This would be harder to mantain, and would slow down operations in orders table.

Another option is using a view:

CREATE OR REPLACE VIEW `ordered_items`
    AS
        SELECT `Item`, COUNT(*) AS `Qty`
            FROM `orders`
            GROUP BY `Item`;

The drawback is that reading ordered_items is slower, because the GROUP BY on orders is executed each time. If this is a problem, you could periodically copy data from ordered_items view into a physical table. This could be done nightly via an Event, if you have MANY data.


First, I'll explain why DDL cannot execute during backup. The reason is that MySQL stores the definitions of relations (tables, indexes, etc) to MyISAM tables located at mysql subdirectory in version 5.6 or earlier (, therefore DDL should be prevented to execute during backup).

Next, I'll answer your question. I assume that you use only innodb tables. (If you use any MyISAM tables, --single-transaction option does not act to your wish.) In this case, the answer of your question is "Yes, you can create temporary tables during backup",  because the definition of temporary tables does not need to store  the mysql subdirectory,  and  creating temporary tables is a normal operation under transaction processing.

In other words, there is no problem because definitions and contents of temporary tables are not backed up.


Which collation should I choose for a muiti-language website?

Generally speaking, one of the Unicode variants is probably the best for broad language support - UTF-8 is going to use less memory per codepoint, and thus will have a slight advantage in any time/space tradeoffs you find yourself in need of making; however, I think there are some of the more esoteric languages/scripts that UTF-8 cannot represent (but I'm not 100% certain of that, I haven't done an exhaustive study on the matter).

This Wikipedia article might be enlightening on the dis/advantages of each.


SQL Server - Sudden log autogrowth after database backup

I wanted to clear this in a comment but there is no room to do this, so I post it as the answer.

While the the key point of what happens is right, i.e. the  full backup cause the log to grow, this explanation below given by Ste Bov is completely wrong:

  When you start a full backup that is the new point in time that the
  tLog backups will work from, but they cant work until that backup is
  complete, so during this time the transaction log grows, once the full
  backup is complete you now have a new point in time and the tLog
  backups will now be able to actually release the data from the
  transaction log and it will return to its normal usage levels

Fisrt of all full backup will NOT make "the new point in time that the
 tLog backups will work from", yes it creates a new recovery point, but the next log backup will start exaclty from the point where the previous log backup was stopped (this point in the log file, last_LSN, is written to the backup and can be seen using RESTORE HEADERONLY, or by querying msdb..backupset)

Second, starting with SQL Server 2005, concurrent log and full backups are possible, you can prove it yourself. What happens instead is that the log backup cannot clear the log when it finishes if there is a full/differential backup running concurrently. That is because diff/full backup need to write out the "active portion"* of the log in order to get a database in consistent stay when restored.

*to be precise, it's not an "active portion", but all the log records from
MIN(LSN of last checkpoint, LSN of oldest active transaction) to the LSN at which the data read portion of a backup ends

So the log should be large enough to be able to accomodate all the activity that is logged for the whole duration of the full backup.

More on this here:

Concurrent log and full backups by Randal

More on how much transaction log a full backup includes by Randal


Doing a comparison on two csv files with no Primary Key

If you have your first CSV loaded into a table, you can just as easily load the other one into a staging table (presumably with the same structure as the 'real' one).  Then you can get the new rows by

SELECT * FROM staging_table
EXCEPT
SELECT * FROM real_table
;

Rows missing from the new CSV can be get reversing the two sides around EXCEPT.  However, given the lack of a key on the staging table (and hopefully not on the real one - it's not clear from your question), deleting rows based on this comparison can be painful, especially with so many rows.

You can drop the staging table once you have finished.

(As far as I see, this approach will work in any RDBMS.)


Auto Update Stats not updating Statistics

The new calculation is only used if trace flag 2371 is enabled, except on SQL Server 2016 when the context database compatibility level is set to 130, where it is the default behaviour. See Microsoft KB 2754171:

Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server

Statistics are not updated when data is modified. A statistics update is triggered when cost-based optimization finds that interesting statistics for the query are stale.

The optimizer does not enter cost-based optimization for very simple ("trivial") queries, where a single obvious plan is always optimal. No statistics updates occur in that scenario.

See the Microsoft White Paper Plan Caching and Recompilation in SQL Server 2012 by Greg Low.


How to create an Access query to find # symbol into a field?

For Access you can use:

WHERE obs LIKE '*[#]*'

There are three wildcard characters in Access that can be used with LIKE operator:

`*` for a string of arbitrary length (equivalent ANSI: `%`)
`?` for a single character (equivalent ANSI: `_`)
`#` for a single numeric digit
`[` for escaping

If you want to search for one of those wildcards, you have to enclose them in brackets: [ ]

You can find more info in the msdn article: Separate But Equal Wildcard Characters in SQL Server and Access


One of the Database Ownerhas been Removed and corresponding property changes in sql server

You have to change the ownership of each database that that user owns before you can delete the user.

Change ownership using SSMS or you can use T-SQL

use database_name
EXEC sp_changedbowner 'newowner'

Following query allows you to query all databases owned by a particular login.

use master
select database_id, name, suser_sname(owner_sid) from sys.databases where suser_sname(owner_sid) = 'logintoremove'

You could then use this information to bulk update ownership.

use master
DECLARE @name VARCHAR (50) -- database name
DECLARE @oldOwner VARCHAR (50)
DECLARE @newOwner VARCHAR (50)

SET @oldOwner = 'oldownerlogin'
SET @newOwner = 'newownerlogin'

BEGIN
    DECLARE db_cursor CURSOR FOR
    select name from sys.databases where suser_sname(owner_sid) = @oldOwner
END

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN
        EXEC(
            'USE '+@name +';

            EXEC sp_changedbowner ''' +@newOwner+ '''
            ' )
        END

    FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor


Import SQL dumps (tables) in folder from shell

Assuming a bash shell, (and for some reason you cannot concat the files together) that is a simple for loop:

for file in *.sql; do mysql **put here your connection parameters** database_name < $file; done


Msg 5145: Autogrow of database log file took 1010871 milliseconds

The error is caused by how long it's taking SQL Server to grow your transaction log.

Assuming the error message you've quoted is for the last grow, SQL Server is claiming that it's taking about 17 minutes for SQL Server to add just under 5GB to your transaction log file.

This can be caused by a number of issues:

Slow disk: If your disks are particularly old and slow it will take SQL Server longer to extend the files
Disk Contention: If SQL Server is having to compete for access to the disks, then it will be slow to extend them. Common causes of this could be having only 1 disk for OS, Data and Logs, or another application on the machine accessing the same disk as SQL Server

There are a number of things you can try to alleviate the problem:

Move your files onto seperate physical disks if possible. Typically
you'd like to have 3 disks for OS, Data and Logs.
Use faster storage. If you can upgrade your disks then this will alleviate the wait for autogrowth to complete
Modify your autogrowth setting to be a fixed size. That way you'll get consistent growth rather than exponential as you are with your current percentage setting. Pick a value that is an acceptable compromise between lots of file growth events and waiting for the growth to happen.
Recreate your database, and preallocate the log file required to accommodate the queries you plan to run. This will remove the autogrowth events (unless you exceed that size).

Hope that helps.


Is there any limit on how deep the Server Owner can access my data?

From what I gather in your post, you are on the development side and they are on the administration side.  I can see why they would like to handle the administration portion of the database(s).  That would most definitely include backup/restore, security, etc.  If they are responsible for the maintenance and administration of the data layer, then that is more than appropriate.

As for their suggestions about data normalization, there is a possibility there suggestions are prudent.  At the same time, that would be a database design/development decision which sounds more like your responsibility.  Maybe they were just friendly suggestions, that's up to you to decide.  But if they were forceful "recommendations" then that would be another story, as you application is likely to adhere to the schema.

As for the restore on a development server, it is very shop-specific.  The overruling argument will be who had jurisdiction on that particular server (or servers).  Again, this is very shop-specific.

What I'm trying to get at is there are too many variables, and as participants of DBA.SE there is really no way we can give you sound advice.  I recommend you take this argument to management in your company that can make a decision one way or another.


Converting my comments to answer ....

  Total Physical RAM = 3583, Virtual = 3583, (1) 21GB, (1) 13GB, (8) 4GB, a handful of smaller ones plus 17GB proprietary file storage. The power plan is currently Balanced.

Based on your above comment, with a mere 3.5GB RAM, you are running dbs totaling the size of 65+ GB. There will be resource starvation on the database server.

Increase the RAM close enough to the size of db and change the power plan to high performance.

Then monitor your db server with sp_whoisactive or SQL-Server-First-Responder-Kit and start tuning when you have data.


It looks like you are trying to generate a list of all courses, plus a note as to which of those courses a specific user is enrolled in.

If that's the case, try changing the query as follows:

    select DISTINCT
    `courses`.*, IF(`course_enrollments`.`group_id` IS NOT NULL, TRUE, FALSE) as is_enrolled
    from `courses`
    inner join `course_time_periods` on `course_time_periods`.`course_id` = `courses`.`id`
    inner join `org_time_periods` on `time_period_id` = `org_time_periods`.`id`
    left join `course_groups` on `course_time_periods`.`id` = `course_groups`.`course_period_id`
    left join `course_enrollments` on (    `course_groups`.`id` = `course_enrollments`.`group_id`
                                       AND `course_enrollments.user_id` = 12
                                      )
    where `org_time_periods`.`started_at` <= "2017-08-12 12:39:05"
        and `org_time_periods`.`finished_at` >= "2017-08-12 12:39:05"
        and `courses`.`is_active` = true
        and `courses`.`organization_id` = 2
        and `course_enrollments`.`deleted_at` is null

As far as I can tell, your left joins only exist to check to see if the user_id in question showed up in a course_enrollments row. So, I modified the join to course_enrollments so that only rows with the indicated user_id would be returned by the join.

Then, instead of checking for user_id = 12, we simple test for whether a row was returned from course_enrollments. If a row was returned, then the user you're interested in is enrolled; otherwise, they are not enrolled.

Note: the code is untested.


Deadlock in INSERT ... ON DUPLICATE KEY UPDATE

Following the MySQL recommendations, some options I'd consider:

Check the SHOW ENGINE INNODB STATUS to see what the deadlock is on.
Convert the INSERT ON DUPLICATE KEY UPDATE into a SELECT then INSERT or UPDATE.
Reduce the size of the batches so they are quicker.
Reduce the concurrency.

What is the relation between the ids in the concurrent batches?  Do the ranges of ids overlap?


Failover strategy for SQL Server 2016 Standard Edition

Separate from your Replication solution, could use a Failover Cluster Instance or Basic Availability Group for HA of your publisher.

Or, if you have a stomach for obscure replication solutions, you could implement Bidirectional Transactional Replication, which is similar to Peer-to-Peer replciation, but older and available on Standard Edition.


Server Integrity Check CheckCommands question

If you run the base CHECKDB command, you don't need to run the others. The other commands make up what is run by the full command.

See:

CHECKDB From Every Angle: Complete description of all CHECKDB stages
CHECKDB From Every Angle: Consistency Checking Options for a VLDB

Breaking the full check into its constituent parts is sometimes done on very large databases where a full check in total is too time consuming or gobbles up too many resources.

The only CHECKDB option that results in a different full check (PHYSICAL_ONLY) runs fewer overall checks. It skips logical checks against the data and only checks the physical file structure.


Simple MySQL query randomly takes forever

To speed up the execution of your query, you need to create a compound index:

ALTER TABLE email_messages
ADD INDEX idx1(is_sent, is_cancelled, time_created);

More information: Multiple-Column Indexes


Why is autovacuum running during a VACUUM FREEZE on the whole database?

Autovacuum is triggered by the table statistics on the table, and as long as your manual VACUUM (FREEZE) is not done, these are not updated. That is why anti-wraparound autovacuum processes will still start.

But that's not a big problem: Only one VACUUM can run on a table at any given time. Now anti-wraparound autovacuum workers won't give up when they block another process, in that case your manual VACUUM. But if you kill the anti-wraparound autovacuum workers, your manual VACUUM gets the lock, and now it is the restarted anti-wraparound autovacuum worker that is blocked. Look at pg_locks to verify that they are waiting for a lock (granted = FALSE).

Now your vacuumdb processes one table after the other, so you must be ready to kill autovacuum workers as soon as it starts processing the next table and is blocked by a new autovacuum worker there.

It might be easier to manually launch VACUUM on those big read-only tables rather than using vacuumdb, because then you can control when which table gets vacuumed.

Make sure you set maintenance_work_mem high to speed up VACUUM. You should also set autovacuum_vacuum_cost_delay to 2ms or lower at least on these big tables, so that future autovacuum runs get done faster.

To reduce the pain for the future, lower autovacuum_freeze_max_age on the affected tables substantially. Then the next anti-wraparound vacuum will kick in sooner and will be done faster.

Best of all, upgrade to v13 as soon as you can, because from that version on insert-only tables will receive regular vacuum runs as well, which should do away with the problem.


How to print text in select query instead of NULL value in DATE field in postgresql?

You need to cast it to string first. Check the below query;

select coalesce(to_char(enroll, 'YYYY-MM-DD'),'Error') as formatted from test;


Please check your current sql_modes by command:

show variables like 'sql_mode' ;

Or

You can include SET statement, before you run any statements put this in the first line:

SET sql_mode = '';

Problem might be because of sql_modes.

So change it by remove the sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE" to make it work.

This is the default sql_mode in mysql new versions.


If alter system disable restricted session doesn't work it means that there are patch violations

Check the status with below command:

select * from PDB_PLUG_IN_VIOLATIONS;

If post steps did not complete properly then it will keep PBD's in restricted mode. Run the below steps:

sqlplus / as sysdba

shutdown immediate
startup upgrade
alter pluggable database all open upgrade;
quit
cd $ORACLE_HOME/OPatch
./datapatch -verbose

check the log, should be no errors

sql> shutdown immediate
sql>startup
sql> alter pluggable database all open;


How to set "PRAGMA foreign_keys = ON" each time I'm connecting to sqlite db via Datagrip

Please, go the SQLite driver options, Advanced tab and set true to foreign_keys parameter.


Increase speed altering column on large table to NON NULL

Instead of changing the column definition you could add a CHECK CONSTRAINT that doesn't allow NULLs for that column. The table will still need to be scanned but it won't need to modify every single data page, so it should be a much faster operation. Regrettably, a Sch-M lock will still be held during the operation. One trick is to try to get as much of the table into the buffer pool as possible before attempting to add the constraint. That may reduce the amount of time the Sch-M lock is held.

You can then remove the constraint and change the column definition during your next maintenance window.


Joining on 3 different keys as they're set

Try this:

SELECT settings.* FROM vessels INNER JOIN settings ON setting.organization_id=vessel.id
UNION
SELECT settings.* FROM vessels INNER JOIN settings ON setting.fleet_id=vessel.id
UNION
SELECT settings.* FROM vessels INNER JOIN settings ON setting.vessel_id=vessel.id


Is `count(*)` ever guaranteed to return the same result in a transaction at any transaction level?

My ten cents, and based on the PostgreSQL documentation:

Assumption: your own transaction does not change any relevant value from table foo.

Having the same count(*) in both your queries means:

You cannot have dirty reads, because another transaction might have written more rows into your foo table between the first and the second select; and you should not see them.
You are not really reading any column, so nonrepeatable reads are not an issue.
You cannot have phantom reads, that is, if another transaction changes any row whose x column is NULL to a non-null value, your transaction does not have to notice those changes affecting the WHERE condition.
I don't really know how to judge serialization anomalies. My educated guess is that this isn't required. But this is really very open for debate.

Under these conditions, the table from Transaction Isolation Levels makes it clear that Repeatable read complies with the criteria of:

Not having dirty reads
Not having non-repeatable reads
Not having phantom reads (in PostgreSQL, but not demanded by the standard)

... as such, it would give you the same count(*) in both select statements.


Simple PL/SQL code to throw NO_DATA_FOUND exception

Many ways, but as you suggested, you can use DUAL:

CREATE PROCEDURE NODATAFOUND
AS
   nowt VARCHAR(10);
BEGIN
   SELECT * INTO nowt FROM DUAL WHERE 1=0;
END;
/

It'll produce a ORA-01403: no data found when executed.


Loading the databases from previous installation mysql

Based on your question, I will assume you know the old data root in Program Files MySQL. If you simply copy the entire thing into the data root of your new MySQL 5.6, and then restart, you should be good to go. The .myi .myd and .frm files are the key parts as I understand it.

If you are unsure where the new data root is, check your new installation's my.ini file.


The fact that you have no lines in pg_stat_replication on the master indicates that your streaming replication is not working - and it has instead fallen back on the file based replication, which has more delay.

Check your logs on both master and slave, they should tell you why it's not starting up in streaming mode.

Also, your recovery.conf file is incorrect - you should not be using pg_standby together with standby_mode. That's a leftover from pre-9.0 days.


How to delete SQL Server Database Mail history WITHOUT stored procedures

One idea could be:

script out CREATE TABLE for sysmail_attachments, sysmail_send_retries, sysmail_mailitems, and sysmail_log
start a transaction
drop the tables in that order
create the tables again using the script from 1.
commit the transaction
create a job that purges those tables regularly (like, once an hour, from the sounds of it), also in that order


ORA-00838 error after increasing MEMORY_TARGET

Set MEMORY_TARGET to at least the recommended value.

SQL> CREATE PFILE FROM SPFILE;
MANUALLY EDIT PFILE & CORRECT MEMORY_TARGET VALUE
SQL> CREATE SPFILE FROM PFILE;
SQL> STARTUP

What OS are you using?


Identify whether the given servers time is in daylight saving or not

This is solved in SQL Server 2016 with AT TIME ZONE. And I wrote an article about it here: http://sqlperformance.com/2016/07/sql-plan/at-time-zone

In earlier versions you'll probably need some CLR to read that information from the registry.


What complications arise from edits to a table receiving a new index?

Standard index build locks the table for the whole index build process, and DML statements on the table will be blocked. You may call that a complication.

Online index build is a feature of Enterprise Edition.
This method locks the table at the beginning, and at the end of the index build process. In between, DML operations on the table can run as usual.

Creating an Index Online


Is there really any benefit in wrapping random ad-hoc queries in a transaction?

The BEGIN TRANSACTION/COMMIT TRANSACTION statements are mostly superfluous in this specific case.

As for running all queries with READ UNCOMMITTED isolation level, I'd strongly advise against it, unless you have a specific need, and aren't worried about the implications of dirty reads:

You may end up querying data from a half-finished transaction (e.g. only one half of a double-entry accounting ledger entry, only some of an order's line items, etc.)
You may end up querying data from a transaction that is eventually rolled back.

If you're concerned about write queries blocking read queries, I would recommend either using SNAPSHOT isolation, or setting the READ_COMMITTED_SNAPSHOT database option, which effectively uses snapshot isolation for all queries. The net result is that instead of being blocked, read queries will see data as it existed prior to any currently running write transactions.

Be advised that this option increases tempdb usage, as that's where the snapshot data is stored (and it has to be stored for all write transactions in databases where it's enabled). If this overhead is unacceptable, then I would only use READ UNCOMMITTED after evaluating queries that need it on a case-by-case basis.

I know you aren't the one making the decision to use it, but you can use these points as ammo if you need to push back on it. :)


When an error log is considered to be "big"? Some of our logs take long to load, close to a million of rows.

This is going to be different for certain scenarios, but with your description ("Some of our logs take long to load"), that's my definition of big. When file operations (copying, moving, etc.) start to take a little longer than instant, or parsing an individual error log becomes an actual time consumer, then that's when I'd start to work on cycling my error log regularly to keep that size down. I know I'm not giving you a "X MB is too large of an error log file" statement, but hopefully this gives you insight. Outside of extremely abnormal and corner cases, the error log size shouldn't be a pain point in troubleshooting.

  I'm trying to find some tsql to get error log size, but can't find it, it is possible to do it?

As far as T-SQL is concerned, outside of a call to xp_cmdshell, I can't think of a way to get this. And because I wouldn't open up that security vulnerability for this type of operation, my recommendation would be to use PowerShell, consuming SMO:

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("YOUR_SQL_SERVER_NAME")

$SqlServer.EnumErrorLogs() |
    Select-Object Name, @{Name = "SizeMB"; Expression = {[System.Math]::Round($_.Size/1024/1024,2)}}

  I see the recommendation to recycle the logs and to configure more than just the default number of 6. Some authors recommend 50, others 10. I guess it depends on the environment, but is there a recommended number?

There's nothing worse than not having historical data due to rollover, no matter what the source of troubleshooting is (SQL Server error logs, XEvents logs, etc. etc. etc.).  You need to really see what your cycling schedule is, and then determine how many days you want to retain. That may be different from server to server. It's hard to give a default retention value here, as there are very different factors such as available hard disk space, average error log generation size (normal conditions) during a 24-hour period, how far you care to go back with logs, and a few others. If you have the disk space with a comfortable buffer (always needs to be a buffer when planning out storage consumption), I personally like to err on the side of too many archived error logs. A conservative bet would be a retention duration of 4 weeks, but again there's too many factors to just making a hard and fast rule based off of. That's really a determination that you need to make, though.  As a DBA and a troubleshooter, how far back do you want your error logs to go?

Side Note: I see in your original question that you referenced sp_cycle_agent_errorlog.  It seemed to me like your question may have been more geared towards the instance's error log cycling, in which case it would be the sp_cycle_errorlog stored procedure.


It's really interesting what I found. I start SSMS as an administrator, and chose the option to debug the stored procedure. I found it was doing this,

declare @test int
select @test=1 from [dbo].[DispatchLink] , [dbo].[Dispatch] [Dispatch]
where (DispatchLink.ContractLink1 = Dispatch.ContractId and DispatchLink.DispatchLink2 = Dispatch.DispatchId)
or (DispatchLink.ContractLink2 = Dispatch.ContractId and DispatchLink.DispatchLink2 = Dispatch.DispatchId)

And it seems that the reason it was doing that was to test whether the join clause was feasible, i.e. whether it had syntax errors etc.

The resulting query plan is the problem, and can be see here,

Analysing A Query Plan

Adding WITH (FORCESEEK) makes the query instant, but of course I cannot do that because that is just what sp_addmergefilter is doing internally. The query optimiser is making an incorrect choice for some reason.


How can I force the MySQL client to output to stdout or a log file when importing a SQL file?

You can use the  general log
so enable general log
but be careful this will log everything what happens and gets fast big


I began receiving these errors consistently after running a script that collects and stores data in psql:

Your max_connections is too low, or that script isn't dying or releasing the connection.

If you're getting Connection refused after you restart, then perhaps you have multiple copies of postgresql installed on the same machine and you mean to connect to a different port (like 5433). If you're using Ubuntu or Debian run pg_lsclusters and paste the output.


SQL Transaction - use values before commit?

You can do a quick test to confirm this, I just ran a test out of curiousity, this is on sql server 2012. The current session will have visibility to a record that was previously inserted but not committed, however other sessions will not have visibility to that depending on their transaction isolation level.

Example

create table hs_test(
id int
)

begin transaction

insert into hs_test values (1)

select * from hs_test

--commit

I ran this in one ssms window, with the default isolation level of ReadCommitted. Without committing, the select will still see the value of 1 inserted.

Now if you open up another ssms window, and try to query that table, you won't see any records, because it hasn't been committed yet.


How to reduce the log file size without shrinking it in SQL server

Once the log claims disk space the only way to get it back is to shrink.  Checkpoints and truncation will free up space within the log file (now available for future use by the log) but the actual footprint of the file on disk stays the same.

Read these articles for good information not only about how this works but the pro's and con's of messing with it.  Most DBA's would agree that shrinking is not a good idea in most normal cases (as you eluded to in your question).

http://msdn.microsoft.com/en-us/library/ms365418.aspx

pros and cons

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/a9e92f1b-7a27-4999-b241-eadadbb0a057


Give permission to select from system tables

System views require a slightly elevated state to view, since they are instance wide.  You'll want to GRANT VIEW SERVER STATE for this:

GRANT VIEW SERVER STATE TO marie;


Select rows from table having more than two specific values

I have recreated your table in tempdb using the Adventure Works Database. This code basically selects all the records that have column contains ('A', 'B', 'C', 'D', 'E', 'F')
and then does a distinct count on the [contains] column. When the result is six the record must have a row of all of ('A', 'B', 'C', 'D', 'E', 'F').

Create Table #Table (Userpin int, product int, [time] time, [contains] varchar(1))
Insert into #Table (Userpin, product, [time], [contains])
Select 4556,     23,            '7:30',         'A' UNION ALL
Select 4557,     23,            '7:30',         'B' UNION ALL
Select 4558,     23,            '7:30',         'C' UNION ALL
Select 4559,     23,            '7:30',         'D' UNION ALL
Select 4560,     23,            '7:30',         'E' UNION ALL
Select 4561,     24,            '7:30',         'A' UNION ALL
Select 4562,     24,            '7:30',         'B' UNION ALL
Select 4563,     24,            '7:30',         'C' UNION ALL
Select 4564,     24,            '7:30',         'D' UNION ALL
Select 4565,     24,            '7:30',         'E' UNION ALL
Select 4566,     25,            '7:30',         'A' UNION ALL
Select 4567,     25,            '7:30',         'A' UNION ALL
Select 4568,     23,            '7:30',         'F' UNION ALL
Select 4568,     23,            '7:30',         'G'

Select product, count(distinct [contains]) as count_distinct_contains
from #Table
where [contains] In ('A', 'B', 'C', 'D', 'E', 'F')
GROUP BY product
HAVING count(distinct [contains]) = 6

This is the Microsoft SQL Server syntax.


How do I load a data-only dump created with pg_dump to another database?

If you are happy with recreating the tables upon restore (and I see no reason why one should not be happy :), then you could use the --clean option:

  -c
  --clean

  Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is
  also specified, restore might generate some harmless error messages,
  if any objects were not present in the destination database.)

  This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call
  pg_restore.

Use it with pg_dump when creating plaintext dumps, or with pg_restore when using any other format.  In both cases, you will need a full dump, as a data-only one would lack the information for creating the tables.

Note: don't use INSERT statements in your dumps (--inserts does this in pg_dump).  It is almost invariably slower than relying on the default COPY functionality.


How to optimize the my.cnf of a MySQL server that mixes InnoDB and MyISAM tablespaces?

One of the most important variables for InnoDB is innodb_buffer_pool_size. This is the amount of memory allocated to load tablespace information for InnoDB only.

Since you are mixing MyISAM and InnoDB, you will need to find a good balance between key_buffer_size (for MyISAM indexes) and innodb_buffer_pool_size (for InnoDB reads).

In general, you want to fit your entire data into memory if possible, as disk reads are (obviously) very expensive.

You didn't mention which MySQL version you are running, but I would recommend reading through the documentation of the appropriate version for tuning InnoDB for more detailed information.


Tracking (un)successful autovacuums in postgres

First figure out what PostgreSQL version you have. If it is truly 9.5, upgrade before you do anything else. I mean it. The rest of my answer deals with recent PostgreSQL versions.
A row lock won't block autovacuum progress (unless it is an anti-wraparound vacuum), but a table lock can. These are logged if you set log_autovacuum_min_duration to something else than -1:
LOG:  skipping vacuum of "vacme" --- lock not available

or
ERROR:  canceling autovacuum task
DETAIL:  while scanning block 5756 of relation "laurenz.vacme"

Keep all autovacuum settings at default values, except autovacuum_vacuum_cost_delay, which you set to 2ms.
Now why is your autovacuum not running? Some ideas:

You didn't commit the transaction that deleted the rows.

The autovacuum workers are doing something else. Check pg_stat_activity.

Check for oddities in the log file.


get average of number of likes in mysql from two tables

You can use the COALESCE function to return zero if it has no results like so:

SELECT posts.post_id, posts.text , COALESCE(AVG(ranks.score),0) as `ranking`
  FROM posts
  JOIN ranks
    ON ranks.post_id = posts.post_id
 GROUP BY posts.post_id;


IF UPDATE in trigger returns true when no rows were affected

Because IF UPDATE(col1) only checks if col1 was referenced in the UPDATE statement. It does not check how many rows were affected, nor does it check if the value has actually changed.

To not get the trigger to fire when zero rows are affected:

CREATE TRIGGER dbo.r_test
  ON dbo.t1 after UPDATE
AS
BEGIN
  IF @@ROWCOUNT > 0
  BEGIN
    ...
  END
END

Note that MERGE may complicate this - @@ROWCOUNT can be non-zero even if no rows are updated, only inserted/deleted (more details here).

Instead you can:

CREATE TRIGGER dbo.r_test
  ON dbo.t1 after UPDATE
AS
BEGIN
  IF EXISTS (SELECT 1 FROM inserted)
     AND EXISTS (SELECT 1 FROM deleted)
  BEGIN
    ...
  END
END

To determine if the value has changed:

IF EXISTS
(
  SELECT 1
    FROM inserted AS i
    INNER JOIN dbo.t1 ON i.id = t1.id
    WHERE i.col1 <> t1.col1
);

This is grossly simplified; NULLs will complicate this, as will columns with encrypted values.

Also, please always use the schema prefix.


you can use shrinkdatabase command to reclaim the freespace,
But be aware ! shrinking in many cases causes fragmentation , you can rebuild  or reogranize all indexes depend on fragmentation percentage.

DBCC SHRINKDATABASE(N'YourDatabaseName' ) or
DBCC SHRINKFILE(N'YourDatabaseName', Size )

After that, you should change your default filegroup from primary filegroup to another one.


I believe this is handled internally in SQL Server's query cache using memory available on the system.  I know you can empty all of the cache by running the following:

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

I just don't think you can alter how many queries it's saving.

Good luck.


This kind of problem can be solved with the use of the LAG() function:

WITH cte AS
  ( SELECT dept,
           term,
           LAG(dept) OVER (ORDER BY term) AS prev_dept
     FROM tableX
  )
SELECT dept, term
FROM cte
WHERE prev_dept <> dept
   OR prev_dept IS NULL
ORDER BY term ;

Test at: SQL-Fiddle


If fork is false then mongo dies when shell (where you started mongo) die. Set fork to true


OK - so in your first year, all going well, you might be looking at 50GB for 1k organisations? That's certainly not "pie in the sky". What I'm going to propose is based on my own experiences and other people's opinions may vary depending on theirs. I worked for a company that had fewer organisations but more data, however I think that what worked there may very well apply to your use case.

We used Oracle at the time, and the company, being scrupulous about licences, didn't want to run loads of Oracle servers with attendant costs. So, every client had their own schema (or in MySQL terms, database) on the same server. That is what I am proposing for your situation.

The advantage this has is that if you have to take one client down, you won't affect the others because their data will be independent of each other. Neither will you have to worry about one client seeing another client's data. Furthermore, given that this is a new project, I imagine that you'll want a good deal of flexibility until the app is "bedded down".

This arrangement also gives you the possibility of running different clients on different versions - say your early adapter types on a test release and your laggards on another. You might want to to test on Freemium type clients, but keep your paying customers happy with the stable version?

You can also consolidate some of your data in a central reference schema (i.e. lookup tables - names of countries/US states/post codes...) and grant select on those to all users of the other schemas. You could do something like what's shown here.

Yet another advantage is that when you grow, all you'd have to do is fire up another machine and split your customer base between the two machines (and so on up to ...). Although, I hope you'll have split long before you arrive where this chap did in terms of schema numbers! :-)

The only major downside of this approach as far as I can see is that it will complicate scripting - but then that should be no problem to a man of your calibre (10K on Stackoverflow :-) ). Just to make sure I wasn't committing some appalling IT faux pas, I used your friend and mine, Mr. Google and was pleasantly surprised to come up with these links (1 - note the point about standards, 2 & 3 - see the "Shared schema" part of the accepted answer). There are dissenting voices, but on the whole, they appear to show that I'm not completely delerious! Furthermore, as I said, this is based on my own personal experiences. You may well, of course, choose to consolidate when your app is up, and running stably.

Two final points.

I believe that I've given my take on this part of your question

  For example, to make sure that only members of the relevant
  organisation can view Estimates I need to perform a query each time to
  make sure that the authorised user belongs to the organisation that
  owns the estimate. I can do this via a join but I wonder if this is
  unnecessary overhead.

but I haven't answered the bit as to which tables should have which foreign keys. This is virtually (again, IMHO) impossible to answer for the following reason. I've started many projects with all sorts of ideas but then when you start throwing data at it, all sorts of new issues arise - it's the classic "no plan survives contact with the enemy". Experiment, play around, test. If you come up with particular issues, then post back here, but it would be rash to give advice at this point.

Finally, and this is again (totally) IMHO. If I were starting a project such as this from scratch, I would, without hesitation, choose PostgreSQL over MySQL. How MySQL came to dominate the Open Source database world is a different question. MySQL doesn't have check constraints. It doesn't fully support set operators. It doesn't have CTEs (Common Table Expressions). It doesn't support windowing functions. Just my 0.2c...


found it ...

ANSWER: Oracle Data Masking and SubSetting

https://www.oracle.com/technetwork/database/options/data-masking-subsetting/overview/index.html


What is the best approach to find all fields that has (Foreign Key and Primary Key) constraints in a Mysql database?

You can use INFORMATION_SCHEMA KEY_COLUMN_USAGE table.

SELECT
table_name AS TableName,
column_name AS ColumnName,
Constraint_name as ConstraintName
FROM
information_schema.key_column_usage
WHERE table_schema = 'database_name'

Updated code based on OP's requirement:

SELECT DISTINCT(TC.table_name) AS TableName, KC.column_name as ColumnName,
TC. CONSTRAINT_TYPE AS ConstraintType
FROM
information_schema.table_constraints TC,
information_schema.key_column_usage KC
WHERE
TC.CONSTRAINT_TYPE IN ('FOREIGN KEY', 'PRIMARY KEY')
and KC.table_name = TC.table_name and KC.TABLE_SCHEMA = TC.TABLE_SCHEMA
and KC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
and KC.table_schema = DATABASE();


Taking your code,

DELIMITER //

CREATE TRIGGER UpdateStock
AFTER INSERT ON ORDERS.Quantity
FOR EACH ROW

BEGIN
   UPDATE PRODUCT.Stocklevel
   SET New.PRODUCT.StockLevel = New.PRODUCT.StockLevel - New.ORDERS.Quantity
   WHERE ORDERS.Quantity = NEW.Quantity;

END;

// DELIMITER;

, I can hardly believe that you've read and understood any documentation -- not on triggers and not on updates at least. And it surely does not compile, let alone run even with errors...

(And by the way, why not posting the relevant code here but instead on an other platform? Makes things unnecessarily harder, I think.)

The ON clause of a trigger takes a table, not a column. So change ON ORDERS.Quantity to ON ORDERS.

After UPDATE follows a table, not a column. So change UPDATE PRODUCT.Stocklevel to UPDATE PRODUCT.

Via the the NEW pseudo table the values of the inserted row can be accessed in a FOR EACH ROW trigger. But not any other table or their columns or the table on which the trigger is executed, for that matter. So New.PRODUCT.StockLevel and New.ORDERS.Quantity are nonsense. Change SET New.PRODUCT.StockLevel = New.PRODUCT.StockLevel - New.ORDERS.Quantity to SET StockLevel = StockLevel - New.Quantity.

And finally, there is no column Quantity in your table ORDERS. So WHERE ORDERS.Quantity = NEW.Quantity; is just wrong either. And even if there were such a column, it would mean the stock level was set at all products having the same quantity (whatever that should mean in products) as the row inserted into order. I guess you want to update the stock level of the product in the order, so use the product id: WHERE ProductID = NEW.ProductID;

That should do the trick.


Find a small recordset and see how many loops your code does. How many does it do for 10, 100 and 1,000 records? You will see that the number of loops increases exponentially as the number of rows increases. My rough guess is that you have asymptotically big O(2^n+2^n+2^n). Asymptotics is a way of estimating work where the amount of rows processed is not known. Can't you just write some minus selects that are union all joined together? My example is looking at indexes and I use different filters so that the data sets will not be the same. Hence you will see the difference.

SELECT 'SDO_NETWORK_METADATA_TABLE_a' table_name, index_name, column_name, column_position
  FROM dba_ind_columns
 WHERE index_owner = 'MDSYS'
   AND table_name = 'SDO_NETWORK_METADATA_TABLE'
   AND index_name
   NOT IN ( SELECT index_name
              FROM dba_ind_columns
             WHERE index_owner  = 'MDSYS'
               AND table_name   = 'SDO_NETWORK_METADATA_TABLE'
               AND column_name  = 'LRS_TABLE_NAME' )
MINUS
SELECT 'SDO_NETWORK_METADATA_TABLE_a' table_name, index_name, column_name, column_position
  FROM dba_ind_columns
 WHERE index_owner = 'MDSYS'
   AND table_name = 'SDO_NETWORK_METADATA_TABLE'
   AND index_name
   NOT IN ( SELECT index_name
              FROM dba_ind_columns
             WHERE index_owner  = 'MDSYS'
               AND table_name   = 'SDO_NETWORK_METADATA_TABLE'
               AND column_name  = 'PATH_LINK_TABLE_NAME' )
UNION ALL
SELECT 'SDO_NETWORK_METADATA_TABLE_b' table_name, index_name, column_name, column_position
  FROM dba_ind_columns
 WHERE index_owner = 'MDSYS'
   AND table_name = 'SDO_NETWORK_METADATA_TABLE'
   AND index_name
   NOT IN ( SELECT index_name
              FROM dba_ind_columns
             WHERE index_owner  = 'MDSYS'
               AND table_name   = 'SDO_NETWORK_METADATA_TABLE'
               AND column_name  = 'PATH_LINK_TABLE_NAME' )
MINUS
SELECT 'SDO_NETWORK_METADATA_TABLE_b' table_name, index_name, column_name, column_position
  FROM dba_ind_columns
 WHERE index_owner = 'MDSYS'
   AND table_name = 'SDO_NETWORK_METADATA_TABLE'
   AND index_name
   NOT IN ( SELECT index_name
              FROM dba_ind_columns
             WHERE index_owner  = 'MDSYS'
               AND table_name   = 'SDO_NETWORK_METADATA_TABLE'
               AND column_name  = 'LRS_TABLE_NAME' )
 ORDER BY 1,2,4;


How to remove data redundancy in product table if multiple suppliers are there for same product

Business rules
Based on (a) some deliberations we had via comments and (b) the content of the question, we have defined the following characteristics of your business environment:

A Product is primarily identified by its Id
A Product is alternately identified by its Name
A Product is alternately identified by its SKU
A Product is offered at a Price
Supplier and Vendor are different names you use to refer to the same particular entity type
An Organization can become a Supplier
An Organization is primarily identified by its Id
An Organization is alternately identified by its Name
A Person cannot become a Supplier
A Product can be offered by zero-one-or-many Organizations playing the Role of Suppliers
An organization, playing the Role of Supplier, supplies one-to-many Products

So, among other points, there is a many-to-many (M:N) association or relationship involving the entity types called Supplier and Product. This is a very basic —but, at the same time, quite powerful when modeled accurately— construct usually employed to shape some components of a database conceptual model.
IDEF1X model
Consequently, I created an expository IDEF1X1 diagram that consolidates the previously formulated business rules in a single graphical device, which is shown in Figure 1:

As you can see, ProductSupplier is the associative entity that portrays the M:N association that connects the Product and Organization.
Each ProductSupplier is uniquely identified by the combination of values of two distinct properties or attributes, i.e.,

SupplierId (a role name I have assigned to Organization.OrganizationId in order to express the meaning that such attribute holds when it migrates2 to the ProductSupplier entity type) and

ProductId,

so they are depicted as a composite PRIMARY KEY. These properties are FOREIGN KEY references to Organization and Product, respectively.
With this layout, I have arranged the properties that pertain to a each entity type in their appropriate place:

The properties that arise regarding the association between Product and Organization (let us say, the property that denotes the point in time when a given Organization started suppliying a certain Product) are included exclusively in the associative entity type.

The properties that concern only to the Organization entity type are placed in the corresponding box, an so are the properties that belong to the Product entity type.

Expository logical SQL-DDL structure
I wrote the DDL statements that follow based on the conceptual definitions discussed above, so that you can get a form a better idea about how to design this part of your database:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on the exact business context characteristics.

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

CREATE TABLE Organization (
    OrganizationId  INT      NOT NULL,
    Name            CHAR(30) NOT NULL,
    FoundingDate    DATE     NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Organization_PK PRIMARY KEY (OrganizationId),
    CONSTRAINT Organization_AK UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE Product (
    ProductId       INT      NOT NULL,
    Name            CHAR(30) NOT NULL,
    SKU             CHAR(30) NOT NULL,
    Price           INT      NOT NULL, -- Retains an amount in Cents, but there are other options.
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Product_PK      PRIMARY KEY (ProductId),
    CONSTRAINT Product_AK1     UNIQUE      (Name), -- ALTERNATE KEY.
    CONSTRAINT Product_AK2     UNIQUE      (SKU),  -- ALTERNATE KEY.
    CONSTRAINT PriceIsValid_CK CHECK       (Price > 0)
);

CREATE TABLE ProductSupplier ( -- Represents the conceptual M:N association.
    SupplierId      INT      NOT NULL,
    ProductId       INT      NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT ProductSupplier_PK               PRIMARY KEY (SupplierId, ProductId), -- Composite PRIMARY KEY.
    CONSTRAINT ProductSupplierToOrganization_FK FOREIGN KEY (SupplierId)
        REFERENCES Organization (OrganizationId),
    CONSTRAINT ProductSupplierToProduct_FK      FOREIGN KEY (ProductId)
        REFERENCES Product (ProductId)
);

In this logical structure, as demonstrated:

each table denotes an individual entity type;
each column stands for an property of the respective entity type;
a specific data type is fixed for each column in order to ensure that all the values it contains belong to a particular set (that you will have to adapt to adapt to your organization needs), be it INT, DATETIME, CHAR, etc.; and
multiple constraints are set up (declaratively) in order to guarantee that the assertions in form of rows retained in all the tables comply with the business rules determined in the conceptual model.

All these factors help to prevent ambiguities that would have come about if multiple entity types are represented by way of a single table.
Derivable information
Successively, you can derive data from the three different base tables —e.g., by dint of JOINs— in order to take advantage of the logical structure and produce new tables that include information like shown in the pictures you included in the question.
Redundancy
In a database that is designed following the principles of the relational model, duplication of values contained in columns is not only acceptable but expected, while duplicate rows are strictly forbidden.
As for the logical structure expounded above, all the tables are constrained declaratively so as to prevent the INSERTion of duplicate rows, therefore the avoidance of this kind of harmful redundancy is well addressed.
Normalization
Relational normalization —a subject worth mentioning since you added the normalization tag— is a logical-level procedure whose purposes are

to decompose via first normal form the table columns whose types accept non-atomic values so that data manipulation and constriction are much easier to cope with by the data sublanguage of use (e.g., SQL), and

to get rid of undesirable dependencies among the columns of a table by virtue of the successive normal forms to avoid update anomalies that introduce other kinds of dangerous redundancies.

Of course, one has to take into account the meaning carried by the table(s) and column(s) at issue.
I suggest that you take the time to test the tables I present in the previous logical structure so that you can determine if it meets each normal form.

Endotes
1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) theoretical work authored by the originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
2 IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.


Exporting table records to an INSERT script

As well as using SSMS, you can use the commercial SSMS Tools Pack

I prefer it: more options, batching, cleverer all round.

  Insert statements for the whole database are generated by the order of PK-FK relationships. Top tables with no FK's are scripted first. Binary data is by default fully scripted. If you wish you can also set the scripting data limit between 0 and 10 Mb. Larger values than the limit are then scripted as NULL. Insert statements for the data in result grids are scripted into a new temporary table for each grid. For example from 5 result grids insert statements for 5 temporary tables get created.


The Customer dimension is joined to the measure group at a non-key granularity. If the Customer Name attribute isn't related to the attribute you join on then data repeats in reports. You need to do one of two things:

Recommended. Change the fact table to have the surrogate key of the dimension. Then change the Dimension Usage tab to join to the key of the dimension.
Change the dimension to change which column is used as the key of the dimension. Make sure the fact table has this column. Fix the Dimension Usage tab to join to the dimension key.


Executing the same SQL statement for different tables

You dont need a loop for what you want. Dynamic SQL will help you want you want to achieve.

SELECT * INTO #tablenames
FROM (VALUES ('table1'), ('table2'), ('table3')) AS tables(tablename)

--select * from #tablenames

declare @sqltext nvarchar(max) = N''
select @sqltext += 'UPDATE  desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM '+quotename(tablename)+ ' table1 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = '''';'+char(10)
from #tablenames
order by tablename

print @sqltext

-- once you review the above print statement and are HAPPY, uncomment the below part.
-- Below part will actually RUN the UPDATE statement ...
-- exec sp_executesql @sqltext

Note: I agree with @AaronBertrand that you should create a physical table - that will be a driver table for the update statement and just update the table when you want to modify or insert more values if you want other tables to be updated.