PL/pgSQL functions and optimizations fences?

Functions written in language SQL can under certain conditions (e.g., no side effects) be "inlined" (at the discretion of the optimizer), in which case they will take part in the query plan optimization.  Functions written in all other languages (including PL/pgSQL) will not take part in the optimization of the main query, so they are indeed optimization fences, as you call it.

On the other hand, a query run by a PL/pgSQL function will be compiled and prepared the first time it is run, so subsequent calls of that function (even as part of the same top-level query) will be faster.  SQL functions do not implicitly prepare the queries they run, so if inlining of an SQL function fails, repeated calls will probably slower.

In practice, it depends on the specific circumstances which is faster.  Generally, I stick to the basic rule of using LANGUAGE SQL when it's possible and LANGUAGE plpgsql only when I need the additional expressive power.  But it's definitely a useful microoptimization strategy to compare implementations in the two languages.


use an explicit SELECT, not nested

INSERT INTO stories
   (vid, ip, body, timestamp)
SELECT
   COALESCE(MAX(vid),0)+1,    --max always returns one row if no group by
   INET_ATON('127.0.0.1'),
   'test',
   NOW()
FROM stories
ORDER BY vid DESC LIMIT 1


I probably overlooked this so I'm sorry if I posted this question in vain but I consider this solved. I Expanded the 'Tables' folder of my database and found out that the tables were already there. I'm not sure if they're automatically generated as you build the diagram or if they're created once you generate a backup. All I did was to generate a .bak and a .sql file through the 'Tasks' menu, using the 'Back up...' and 'Generate Scripts...' sub-menus. Again I probably overlooked this before generating a backup and they were already there.


Slow import for users .DMP files in Oracle 11G

It could be that one of the imp default parameters for Oracle 10G is different in Oracle 11G.  I would try adding the

buffer=4096000

parameter to the imp command to see if that helps.  That parameter is available on both exp and imp commands.


Rebuilding indexes for a partitioned table having 300 partitions

While examining the query plan of rebuilt indexes, I can see the 'Estimated number of rows' is 180k but 'Actual number of rows' is 300 partitions * 180,000 rows = 54 Million rows, even though I am loading data for only one partition.

Your maths are a little off here. The image provided shows an estimated 1,853,250 rows (not 180k) and a total of 674,582,272 rows (not 54 million) over 364 iterations (364 * 1,853,250 = 674,582,272).

Still, the question remains: Why would SQL Server read 674 million rows to rebuild a nonclustered index when the whole table only holds 1,853,250 rows?

The execution plan shown is a colocated join. It performs the following operations:

The Constant Scan holds the partition numbers for each partition of the table.
For each partition:

Fully scan the table (Clustered Index Scan)
Sort the rows into nonclustered index order (Sort)
Filter the rows for the current partition (Filter)
Insert the rows into the nonclustered index (Index Insert)

This is obviously a terribly inefficient way to go about things. But the optimizer is not crazy: the general idea is sound, the problem is that the Filter is applied too late. Normally, the 'current partition' predicate would be pushed down into the Clustered Index Scan, where it would appear as a seek to select the current partition.

How It Should Work

The usual parallel plan generated looks like this:

Notice the lack of a Filter operator. Its predicate has been pushed into the scan:

Yes, this is a scan with a seek. The idea is to fetch rows from only the current partition on each iteration of the loop, so each iteration builds one partition of the nonclustered index.

Your case

I managed to reproduce the execution plan shown in the question by using an float or bigint data type as the partitioning column. Some aspect of the cardinality estimation for the Filter (probably a guess) prevents the optimizer pushing the 'current partition' predicate past the Sort, and into the Clustered Index Scan.

The following demo creates a 300-partition table with a disabled nonclustered index, loads one million rows into a single partition, then rebuilds the nonclustered index. It uses the bigint data type:

DROP TABLE IF EXISTS dbo.Test;

IF EXISTS (SELECT * FROM sys.partition_schemes AS PS WHERE PS.[name] = N'PS')
    DROP PARTITION SCHEME PS;

IF EXISTS (SELECT * FROM sys.partition_functions AS PF WHERE PF.[name] = N'PF')
DROP PARTITION FUNCTION PF;
GO
CREATE PARTITION FUNCTION PF (bigint)
AS RANGE RIGHT
FOR VALUES
(
    0,
    1000000,2000000,3000000,4000000,5000000,6000000,7000000,8000000,9000000,10000000,
    11000000,12000000,13000000,14000000,15000000,16000000,17000000,18000000,19000000,
    20000000,21000000,22000000,23000000,24000000,25000000,26000000,27000000,28000000,
    29000000,30000000,31000000,32000000,33000000,34000000,35000000,36000000,37000000,
    38000000,39000000,40000000,41000000,42000000,43000000,44000000,45000000,46000000,
    47000000,48000000,49000000,50000000,51000000,52000000,53000000,54000000,55000000,
    56000000,57000000,58000000,59000000,60000000,61000000,62000000,63000000,64000000,
    65000000,66000000,67000000,68000000,69000000,70000000,71000000,72000000,73000000,
    74000000,75000000,76000000,77000000,78000000,79000000,80000000,81000000,82000000,
    83000000,84000000,85000000,86000000,87000000,88000000,89000000,90000000,91000000,
    92000000,93000000,94000000,95000000,96000000,97000000,98000000,99000000,100000000,
    101000000,102000000,103000000,104000000,105000000,106000000,107000000,108000000,109000000,
    110000000,111000000,112000000,113000000,114000000,115000000,116000000,117000000,118000000,119000000,
    120000000,121000000,122000000,123000000,124000000,125000000,126000000,127000000,128000000,129000000,
    130000000,131000000,132000000,133000000,134000000,135000000,136000000,137000000,138000000,139000000,
    140000000,141000000,142000000,143000000,144000000,145000000,146000000,147000000,148000000,149000000,
    150000000,151000000,152000000,153000000,154000000,155000000,156000000,157000000,158000000,159000000,
    160000000,161000000,162000000,163000000,164000000,165000000,166000000,167000000,168000000,169000000,
    170000000,171000000,172000000,173000000,174000000,175000000,176000000,177000000,178000000,179000000,
    180000000,181000000,182000000,183000000,184000000,185000000,186000000,187000000,188000000,189000000,
    190000000,191000000,192000000,193000000,194000000,195000000,196000000,197000000,198000000,199000000,
    200000000,201000000,202000000,203000000,204000000,205000000,206000000,207000000,208000000,209000000,
    210000000,211000000,212000000,213000000,214000000,215000000,216000000,217000000,218000000,219000000,
    220000000,221000000,222000000,223000000,224000000,225000000,226000000,227000000,228000000,229000000,
    230000000,231000000,232000000,233000000,234000000,235000000,236000000,237000000,238000000,239000000,
    240000000,241000000,242000000,243000000,244000000,245000000,246000000,247000000,248000000,249000000,
    250000000,251000000,252000000,253000000,254000000,255000000,256000000,257000000,258000000,259000000,
    260000000,261000000,262000000,263000000,264000000,265000000,266000000,267000000,268000000,269000000,
    270000000,271000000,272000000,273000000,274000000,275000000,276000000,277000000,278000000,279000000,
    280000000,281000000,282000000,283000000,284000000,285000000,286000000,287000000,288000000,289000000,
    290000000,291000000,292000000,293000000,294000000,295000000,296000000,297000000,298000000,299000000,
    300000000
);
CREATE PARTITION SCHEME PS
AS PARTITION PF
ALL TO ([PRIMARY]);
GO
CREATE TABLE dbo.Test
(
    c1 bigint NOT NULL,
    c2 bigint NOT NULL,

    CONSTRAINT [PK dbo.Test (c1)]
    PRIMARY KEY CLUSTERED (c1)
    ON PS (c1),

    INDEX [IX dbo.Test (c2)]
    NONCLUSTERED (c2)
    ON PS (c1)
)
ON PS (c1);
GO
ALTER INDEX [IX dbo.Test (c2)]
ON dbo.Test
DISABLE;
GO
INSERT dbo.Test WITH (TABLOCKX)
(
    c1,
    c2
)
SELECT
    CONVERT(bigint, SV1.number * 1000 + SV2.number),
    CONVERT(bigint, (SV1.number * 1000 + SV2.number) * 2)
FROM master.dbo.spt_values AS SV1
CROSS JOIN master.dbo.spt_values AS SV2
WHERE
    SV1.[type] = N'P'
    AND SV2.[type] = N'P'
    AND SV1.number >= 0
    AND SV1.number < 1000
    AND SV2.number >= 0
    AND SV2.number < 1000;
GO
ALTER INDEX [IX dbo.Test (c2)]
ON dbo.Test
REBUILD;

The index rebuild plan takes around 90 seconds to run:

By contrast, when the partitioning column is an integer data type, the rebuild takes two seconds (with the efficient scan-with-a-seek plan previously shown).

This might be because the partition id is natively an integer (so no conversion is necessary in the filter predicate), but the details are unclear at this point.

A workaround

The above only reproduces for me if I use the default (modern) cardinality estimation model. It is not possible to specify a hint directly on an index rebuild statement, so the following uses documented trace flag 9481 to temporarily use the legacy (original) cardinality estimation model instead:

ALTER INDEX [IX dbo.Test (c2)]
ON dbo.Test
DISABLE;

DBCC TRACEON (9481);

ALTER INDEX [IX dbo.Test (c2)]
ON dbo.Test
REBUILD;

DBCC TRACEOFF (9481);

This produces the optimal execution plan and completes in around two seconds.

The real solution

There is a better way to add or load data into a single partition of a table. The idea is to SWITCH the existing partition out into a standalone table, load the data into that, then SWITCH the partition back in:

DROP TABLE IF EXISTS dbo.TestSwitchP2;
GO
-- Table with constraints limiting values to the target partition
CREATE TABLE dbo.TestSwitchP2
(
    c1 float NOT NULL PRIMARY KEY,
    c2 float NOT NULL,

    CHECK (c1 >= 0 AND c1 < 1000000)
)
ON [PRIMARY];
GO
-- Switch existing rows into the working table
ALTER TABLE dbo.Test
SWITCH PARTITION 2
TO dbo.TestSwitchP2;
GO
-- Add new rows
INSERT dbo.TestSwitchP2 WITH (TABLOCKX)
(
    c1,
    c2
)
SELECT
    CONVERT(float, SV1.number * 1000 + SV2.number),
    CONVERT(float, (SV1.number * 1000 + SV2.number) * 2)
FROM master.dbo.spt_values AS SV1
CROSS JOIN master.dbo.spt_values AS SV2
WHERE
    SV1.[type] = N'P'
    AND SV2.[type] = N'P'
    AND SV1.number >= 0
    AND SV1.number < 1000
    AND SV2.number >= 0
    AND SV2.number < 1000;
GO
-- Create a compatible nonclustered index
CREATE INDEX i ON dbo.TestSwitchP2 (c2);
GO
-- Switch the partition back in to the main table
ALTER TABLE dbo.TestSwitchP2
SWITCH TO dbo.Test
PARTITION 2;

Partition switching is a metadata-only operation, and typically completes instantly.

The implementation above loads the one million new rows in less than two seconds. Note there is no need to disable the nonclustered index on the main table.


multiple calls to procedure optimization

High CPU usually implies SELECT with an inadequate index.

You mentioned inserts -- Yes, INSERTing 100 row in a single statement runs about 10 times as fast as 100 separate INSERT statements.  This is largely because of various overhead things that involve the CPU.  So, that should cut back on the CPU.

If you are doing any "normalization", consider doing it before entering the big transaction.  Ditto for anything else that might be "idempotent".


Finding related functional dependencies from Amstrong's axioms

This is rather easy.

AB → C
B → D

therefore:

AB → C
AB → D

and:

AB → CD

Next:

AB → CD
CD → E

therefore:

AB → E

with: AB → C
we get:

AB → CE

And finally:

CE → GH

thus:

CE → G

and with the (previous): AB → CE

AB → G


Backup/restore check data is the same SQL Server

SQL Server Data Tools offers a Data/Schema comparison option. You need to install it, previous download of it, and then do the comparison.

More information on how to compare in How to: Compare and Synchronize the Data of Two Databases.

To download the free tool, please visit https://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx.


Add AWS EC2 instance to existing Availability Group

I assume it would be like this hybrid it: disaster recovery scenario but with AWS instead of Azure. There is an interesting point in the article about having your Active Directory available in the cloud.

I think you'd have to give some thought to how quorum would work. For example, what would happen if you did a manual failover to the cloud instance, then later your on-premises instances came back online?

HTH


How do I implement insert-if-not-found for transactions at serializable isolation level?

This is a bit of a FAQ. You'd find more information if you searched for ON DUPLICATE KEY UPDATE (the MySQL syntax), MERGE (the SQL-standard syntax), or UPSERT. It's surprisingly hard.

The best article I've seen on it yet is Depesz's "why is upsert so complicated". There's also the SO question Insert, on duplicate update (postgresql) which has suggestions but lacks explanation and discussion of the issues.

The short answer is that, yes:

  It seems the only option I have is to abort the entire transaction and
  try again.

When using SERIALIZABLE transactions you just have to re-issue them when they fail. Which they will. By design - and much more frequently on Pg 9.1 and above because of greatly improved conflict detection. Upsert-like operations are very high conflict, so you may land up retrying quite a bit. If you can do your upserts in READ COMMITTED transactions instead it'll help, but you should still be prepared to retry because there are some unavoidable race conditions.

Let the transaction fail with a unique violation when you insert the conflicting row. If you get a SQLSTATE 23505 unique_violation failure from the transaction and you know you were attempting an upsert, re-try it. If you get a SQLSTATE 40001 serialization_failure you should also retry.

You fundamentally cannot do that retry within a PL/PgSQL function (without dirty hacks like dblink), it must be application side. If PostgreSQL had stored procedures with autonomous transactions then it'd be possible, but it doesn't. In READ COMMITTED mode you can check for conflicting inserts made since the transaction started, but not after the statement that calls the PL/PgSQL function started, so even in READ COMMITTED your "detect conflict with select" approach simply will not work.

Read depesz's article for a much better and more detailed explanation.


Add an outer join to a complete series of days using generate_series():

SELECT day, COALESCE(duration, 0) AS duration
FROM  (  -- your original query, just added table aliases to reduce noise
         -- and fixed the upper bound of your time range
   SELECT date_trunc('day', t.start_at) AS day
        , sum(duration) AS duration  -- add table qualification to duration
   FROM   time_logs     t
   JOIN   users         u  ON t.user_id = u.id
   JOIN   account_users a  ON a.user_id = u.id
   JOIN   categories    c  ON c.id = t.activity_id
   WHERE  a.account_id IN (1,2,3)
   AND    t.start_at >='2017-03-01 00:00 +1'
   AND    t.start_at < '2017-04-01 00:00 +1' -- to get the whole month
   AND    c.report_group = 'Segment1'
   GROUP  BY 1
   ) sub
RIGHT  JOIN generate_series(timestamptz '2017-03-01 00:00 +1'
                                      , '2017-03-31 00:00 +1'  -- !
                                      , '1 day') day USING (day)
ORDER  BY 1;

And COALESCE to get 0 ("zero") instead of NULL, if you need that.

BETWEEN '2017-03-01 00:00 +1' AND '2017-03-31 00:00 +1' would exclude the last day of march (except for its very first µs). I changed it to include the whole month. AND t.start_at < '2017-04-01 00:00 +1' has the additional advantage that you don't need to adapt the day of the upper bound, just the month.

Since start_at is obviously timestamptz, that's the most efficient call with matching data type. Detailed explanation here:

Generating time series between two dates in PostgreSQL

The actual data type of start_at and your actual time zone setting might lead to disagreement when days start. Consider:

How do I use window functions ("over") together with crosstab in Postgres?
Ignoring timezones altogether in Rails and PostgreSQL


Cannot connect to server. Too many symbolic links. Postgres Error

Check what is /var/pgsql_socket/.s.PGSQL.5432. And if it is symlink, what it points to.


Mongo zoned sharding with a single value

Assuming your customerId does not have any obvious mapping to location, it would be best to add a location field and create a compound shard key. The location field should be a prefix of the shard key, otherwise you will have an administrative headache trying to maintain zones for small ranges of customerID values (which could end up as granular as a single customer).

With a compound shard key of  {location: 1, customerId:1} your zone ranges would be straightforward to define.

For example:

sh.updateZoneKeyRange(
  "mydb.mycollection",
  { "location" : "US", "customerId" : MinKey },
  { "location" : "US", "customerId" : MaxKey },
  "US"
)

The MongoDB manual includes a similar example in Segmenting Data by Location and this is the same approach used for MongoDB Atlas' Global Clusters feature. Both of these examples suggest two-character country codes (ISO 3166-1 Alpha-2) for the location field, but you could choose any values which provide suitable granualarity for your future use cases. For example, ISO 3166-2 would provide for countries and subdivisions (administrative regions like state or province).

Starting in MongoDB 4.2, you can also change a document's shard key value if you need to move existing user data between zones. You presumably would never want to change your customerId values, but could update a location field.


An admin (GUI or shell tool) for managing all kinds of databases

A bit too late, but the product Data Utensil aims to be just that. As of now, it supports SQL Server, My SQL, Oracle & Firebird (embedded).

Disclosure: I work for Maxotek, who created Data Utensil.


where can I find the DNS name associated to the listener of an Availability Group?

The message that is returned is not accurate as it is referring to the name that it will give the network name resource (SQLPROD_AG_SQLPROD), and it is labelled incorrectly as the DNS name.  You can see that the network name resource in your AG is named SQLPROD_AG_SQLPROD, and that is what it's referring to.

It really should say something like, "The availability group 'SQLPROD_AG' already has a listener with a network name resource of 'SQLPROD_AG_SQLPROD'."  When the listener is created, the associated network name resource is created with a name according to the following syntax:

<Availability Group Name>_<DNS name>

Since a resource with that name already exists, it can't create it.

Also, underscores aren't allowed in DNS names, so the name given in the error message is obviously not a DNS name.

To see the DNS name associated with the listener, you can use SQL Server Management Studio and view the properties of the listener, or use Failover Cluster Administrator and view the properties of the network name.

The following PowerShell command will return the names and DNS names of all network name resources that are not core cluster resources.

Get-ClusterResource | Where-Object {$_.isCoreResource -eq $false -and $_.ResourceType -eq "Network Name" } | Get-ClusterParameter | Where-Object { $_.Name -eq "DnsName" } | Select ClusterObject, Value

If you want to filter by a specific AG name, you would use Where-Object {$_.OwnerGroup -eq "SQLPROD_AG" -and $_.ResourceType -eq "Network Name' } for the first WHERE clause.

With T-SQL, you can get this from the AG-related views.  To list all:

select ag.name, agl.dns_name from sys.availability_group_listeners agl
join sys.availability_groups ag ON ag.group_id = agl.group_id

To list only the one for SQLPROD_AG, you would just need to add a WHERE clause:

WHERE ag.name = 'SQLPROD_AG'

If you're wanting to use a different DNS name for the listener, you have to drop the current listener and create a new one.

ALTER AVAILABILITY GROUP SQLPROD_AG REMOVE LISTENER 'SQLPROD'

See Remove an availability group listener.


Deleting aged postgresql rows with tricky condition

You need something like this (the table and column names may differ, check the actual SQL CREATE TABLE statements):

DELETE FROM links_group AS g
WHERE g.id IN
      ( SELECT r.which_group_id
        FROM links_reply AS r
          -- WHERE r.which_group IS NOT NULL
          -- not needed, the column is not nullable
        GROUP BY r.which_group_id
        HAVING MAX(r.submitted_on) < NOW() - INTERVAL '14 days'
      ) ;

Since there is a foreign key from links_reply that references links_group and does not have an ON DELETE CASCADE attribute set, the above will of course fail. To delete from both tables, you either need to run two statements or combine them in one with a modifying CTE:

WITH
  groups_to_delete AS
    ( SELECT r.which_group_id
      FROM links_reply AS r
      GROUP BY r.which_group_id
      HAVING MAX(r.submitted_on) < NOW() - INTERVAL '14 days'
    ),
  delete_replies AS
    ( DELETE FROM links_reply AS r
      WHERE r.which_group_id IN (TABLE groups_to_delete)
      RETURNING which_group_id
    )
DELETE FROM links_group AS g
WHERE g.id IN (TABLE delete_replies) ;


Assuming you're using a 64 bit system, an INT(eger), 2^64  =   18,446,744,073,709,551,616, so if you have an insert every nanosecond, your system will run out of numbers after approximately 600 years - I wouldn't worry too much! :-)


Find Max(value) for each department using GROUP BY MYSQL

You're missing the ON clause in your join.
The GROUP BY is not necessary, since you're selecting the MAX(GPA) entry for each student anyway. For this, the subquery needs to relate to the outer query.

SELECT S.StuName, M.DEPTNAME
FROM Students S NATURAL JOIN MAJORS M ON S.stuID = M.stuID
WHERE S.GPA=(SELECT MAX(S2.GPA) FROM STUDENTS S2 WHERE S2.StuName = S.StuName)
;

Here's a manual entry regarding your problem, where you can also see other ways of solving this.


In case of such issues it may be useful to check:

show shared_preload_libraries ;
select name,setting,source,sourcefile,sourceline,pending_restart from pg_settings where name = 'shared_preload_libraries';
Maybe something overrides your shared_preload_libraries somewhere or maybe server didn't actually restart, then 2. will show that as setting field will show something else than pg_stat_statements or pending_restart will show t if that change still waits for restart.

(i don't have comment rights here, so i post as answer hoping it will help)


SAN block size for SQL Server on 4KB NTFS

Quoting from Brent Ozar's article SQL Server Virtualization Q&A:

  Question: “Regarding Virtualization and SAN… is there a recommendation
  for setting block size on SQL Server 2008?”

  There’s a few related settings here including NTFS allocation unit
  size, RAID stripe size, and partition offset.  Check with your SAN
  vendor’s documentation to see what’s right for you.  In most cases for
  OLTP databases, you’re in decent shape with 64K NTFS allocation unit
  size and RAID stripe size, and a 1mb partition offset.

So yes, by default you'll be safe with a 64k block size, but see also your storage documentation, maybe they specify some other preferable unit for a database server.

Some more great information from the same blog: SAN Storage Best Practices for SQL Server.


Is there a place to store ERDs in Postgres?

I recommend storing the XML of the ERD in a Code Repository (or Document Management System).  Read your Vendor's recommendation.

If you are building your own Document Manager, use a BLOB data type (which seems to be bytea in PostreSQL).


Right-click on your Database in Object Explorer
Select Tasks -> Generate Scripts...
In that wizard you will be able to select the desired object(s) that you'd like to script out
The next portion has an Advanced button, click that
Under General, you will see the option called Types of data to script.  The options are Schema only, Schema and data, or Data only.  I believe you want the last option (Data Only)


Making a column unmodifiable/non-updatable after its row's initial creation?

GRANTS

Oracle has column level GRANT capabilities.

Therefore, the answer you seek:
Don't GRANT the update privilege on that column of that table to that user.

grant insert,select,delete on my_table to some_role;
grant update (updatable_column, upd_ts) on my_table to some_role;
grant some_role to single_user;

VPD/FGAC

Note: Virtual Private Database (VPD) and Fine Grain Access Control (FGAC) require Enterprise Edition (EE);  18c XE also supports VPD/FGAC

VPD allows you to add a WHERE clause based specific conditions.  In this example, the function always_false generates the appropriate conditions.

create function always_false return varchar2 deterministic
as
begin
  if sys_context('USERENV', 'SESSION_USER' ) = 'SINGLE_USER'
  then
    return '1=0'
  end if;

  return null; -- does not apply a rule
end;
/

begin
  dbms_rls.add_policy (
     object_name       => 'MY_TABLE'
    ,policy_name       => 'NO_CRT_TS'
    ,policy_function   => 'ALWAYS_FALSE'
    ,statement_types   => 'UPDATE'
    ,enable            => 'TRUE'
    ,static_policy     => 'TRUE'
    ,sec_relevent_cols => 'CRT_TS'
  );
end;
/

note:  I haven't tested this code

TRIGGER

I wouldn't use TRIGGER to enforce this because triggers can be by-passed.


How to preserve sqlplus session in shell script

preliminary note

You issue the statement

 cat "temp.sql" | sqlplus -s sys/oracle@xe as sysdba

You should avoid to pipe into sqlplus. Better you execute

sqlplus -s sys/oracle@xe as sysdba @temp.sql

An advantage is that no prompt will be generated in the output file.
You can better control how the output looks like. Some input will produce
results that make sense when working interactively but not make sense when executing a batch job.
I can't see any advantage in piping commands to sqlplus.

You should not use SYS to do your work with application objects and application data. There are  a lot of security reasons. Besides that  you should be aware that

  Queries by SYS will return changes made during the transaction even if SYS has set the transaction to be READ ONLY

This is not the behavior if you issue DML statements. This can make your data inconsistent.

From the manual:

  You can specify changes you wish to make to the information stored in the database using the
  SQL Database Manipulation Language (DML)
  commands UPDATE, INSERT, and DELETE—which can be used independently or within a PL/SQL block.
  These changes are not made permanent until you enter a SQL COMMIT command or a
  SQL Database Control Language (DCL) or Database Definition Language (DDL) command
  (such as CREATE TABLE), or use the autocommit feature. The SQL*Plus autocommit feature causes
  pending changes to be committed after a specified number of successful SQL DML transactions.
  (A SQL DML transaction is either an UPDATE, INSERT, or DELETE command, or a PL/SQL block.)

So your script  must contain the right statements so that all can be rolled back.

SET EXITCOMMAND was introduced in 11.2 to SQL*Plus. I don't think that it added something really new to SQL*Plus.
You can use the WHENEVER commands to control the SQL*Plus behavior.

  I want to commit all transactions only if all queries succeed

So I assume if the first error occurs you want to rollback and exit.

Therefore you should start your script with the WHENEVER SQLERROR statement

WHENEVER SQLERROR EXIT FAILURE ROLLBACK

If an SQL error occurs the transaction will be rolled back and SQL*Plus will be exited.
The error code that SQL*Plus is an OS-system depended value that signals an error

Maybe a WHENEVER OSERROR command should be executed, too

WHENEVER OSERROR EXIT FAILURE ROLLBACK

Of course AUTOCOMMIT should not be enabled, but

SET AUTOCOMMIT OFF

is the default, so you must not execute this command.

You can add these statements to the site profile or user profile scripts (glogin.sql or login.sql)
so that they will be executed automatically whenever you connect to a database, if this makes sense.

Here  draft how you can run your scrip.

sqlplus script run01.sql

SPOOL run01.log
REM file: run01.sql
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACK
@temp.sql
SPOOL OFF
EXIT COMMIT
REM COMMIT is the default of EXIT

shell script main1.sh

#!/bin/bash
sqlplus $USER/$PASSWORD @run01.sql
if [[ $? = 0 ]]; then
    echo "sql script executed successfully" >&
else
    # alert
    echo "error when executing sql script, check run01.log" >&2
    fi

You added a new requirement:

  I would like to execute all queries irrespective of any error and rollback all queries if there was at least one error.

You can again run the script an rollback end exit if the first error occurs. If that happens you can
run your script again
but now you don't exit in case of error but continue with the execution of the script. At the end
of the script you roll back all transactions using the {EXIT COMMAND]8.

sqlplus script run02.sql

SPOOL run02.log
REM file: run02.sql
WHENEVER SQLERROR CONTINUE NONE
WHENEVER OSERROR EXIT FAILURE ROLLBACK
@temp.sql
SPOOL OFF
EXIT ROLLBACK

shell script main2.sh

#!/bin/bash
sqlplus $USER/$PASSWORD @run01.sql
if [[ $? = 0 ]]; then
    echo "sql script executed successfully" >&
else
    sqlplus $USER/$PASSWORD @run02.sql
    # alert
    echo "error when executing sql script, check run02.log" >&2
    fi

This is my preferred solution. The only disadvantage I can see is
that the statements of the temp.sql script
that are executed until the first error occurs are executed two times:
in script run01.sql and in script run02.sql.
In a Linux environment you can try to avoid this by running the whole
script and analyze the logfile with the grep statement
an then exit the session with commit or rollback, depending on the
result of this analysis.

sqlplus script run03.sql

SPOOL run03.log
REM file: run03.sql
WHENEVER SQLERROR CONTINUE NONE
WHENEVER OSERROR EXIT FAILURE ROLLBACK
@temp.sql
SPOOL OFF
HOST ./analyze_logfile.sh
@exit.sql

shell script main3.sh

#!/bin/bash
sqlplus $USER/$PASSWORD @run03.sql
if [[ $? = 0 ]]; then
    echo "sql script executed successfully" >&
else
    # alert
    echo "error when executing sql script, check run03.log" >&2
    fi

shell script analyze_logfile.sh

#/bin/bash
# file: analyze_logfile.sh
if grep "^ORA-" run03.log; then
    echo "EXIT ROLLBACK"
else
    echo "EXIT COMMIT"


Oracle - Search a value in all columns of a table

As far as I can tell it's not possible to search all columns of a table for a value without referencing all of the column names that you want to search in some way in the query.

If all of your columns have compatible data types and you just want to return any rows that have at least one column that matches your value here is one way to accomplish that:

SELECT *
FROM tablename
WHERE 'value' IN (col1, col2, ... col53);

If you don't want to go through the work of writing out all of the column names or if you need to generate these types of queries for many different tables you can automate the query writing using Oracle's metadata views. For example, you can generate a list of all column names for a table by querying the ALL_TAB_COLUMNS view. Something like this should work:

SELECT LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID)
from ALL_TAB_COLUMNS
WHERE OWNER = 'TABLE_OWNER' -- replace with the owner
AND TABLE_NAME = 'tablename'; -- replace with the table name


I don't fully follow all of your example questions, but knowing what the position is you're interviewing for may help in us giving you advice on what to study and how deep to go down the rabbit hole on certain topics. For example a Database Administrator role would certainly need to study more technical management things about databases such as backups, Transaction Log and growth, SQL security, maybe HA/DR features like Availability Groups, etc. Whereas a Data Analyst role would probably not need to know any of that stuff, but have a basic understanding of the Data Query Language (DQL) in SQL Server and general database structure. And a Database Developer would have some kind of skillset in between both roles, in addition to things neither role necessarily needs to know.
I'm going to assume since your existing skillset involves R, then you're probably applying to a position somewhere around Data Analyst. In that case, a quick review of W3Schools SQL Walkthrough is a good refresher on the basics of DQL and SQL in general. It also goes through some of the functions in SQL Server so that'll be good to know for some of the potential questions.
You should also review and make sure you understand the fundamentals of keys and referential integrity.
You should also make sure you thoroughly understand the different types of JOINS in SQL Server, which the previous W3Schools link will go through, but this webpage on SQL Server Joins also does a good breakdown specifically on them, with visual representations of each one. Understanding how to use JOINS in a relational structure are very important. The only type of JOIN that doesn't cover is the CROSS JOIN but you can read up on it here.
You should also understand CROSS APPLY and OUTER APPLY as well, which you can read up on in this article. Though they're not as common and probably slightly less important than understanding the previous JOIN types, so don't stress as much over them.
It sounds like one of the example questions involves transforming rows into columns which means you should understand how to use the PIVOT and UNPIVOT operators. (Quick tip, use PIVOT to turn row values into columns, and UNPIVOT to turn columns into row values.) Here's a good walkthrough with examples on using the PIVOT operator.
Understanding Window Functions is a very useful thing but also a little bit of an advanced topic depending on the position you're applying for. Particularly DENSE_RANK(), RANK(), and ROW_NUMBER() are good ones to know. More information and examples here.
If you update your post with more details such as the specific job role you're applying for, I'll update my answer with more relevant information accordingly.


Try a Cartesian or cross join.  You can test the select clause separately to ensure it selects the desired data before adding it into the insert.

INSERT INTO EPISODE_CAST (episode_id, cast_member_id)
SELECT  episode.id AS episode_id,
        cast_member.id AS cast_member_id
FROM EPISODE CROSS JOIN CAST_MEMBER
WHERE show_number BETWEEN 1 AND 550
AND   person_name='John Smith';


How do I import data into oracle 11g r2 using a `.dmp` file and a `.par` file?

The PAR file is a parameter file, which contains parameters for data pump that you don't need to enter on the command line.  Without knowing what those are, we can't advise what you need to use in any detail.

Generally speaking, if you have a parameter file, you just put that on the command line:

impdp user/pass parfile=/path/to/parfile

However, there may be other parameters required.

Assuming you have a parameter file, you need the command line to contain at minimum:

Username
Parfile

To perform an import, you need at minimum these parameters defined between the command line and parameter file:

Username
Directory
Dumpfile
Full OR Schemas OR Tables
(and for good practice, Logfile)

For a basic import, those four parameters between the command line and the parameter file will import data.  Without knowing what you're trying to achieve, anything else is guesswork.


Can't restore mysqldump database backup in Ubuntu 12.04

Try

mysql -u ROOT -p DATABASE_NAME < DATABASE.SQL

instead of

mysqldump -u ROOT -p DATABASE_NAME < DATABASE.SQL


What's better/faster? MySql or FileSystem?

The file system is useful if you are looking for a particular file, as operating systems maintain a sort of index. However, the contents of a txt file won't be indexed, which is one of the main advantages of a database. Another is understanding the relational model, so that data doesn't need to be repeated over and over. Another is understanding types. If you have a txt file, you'll need to parse numbers, dates, etc.

So - the file system might work for you in some cases, but certainly not all.


PostgreSQL for running Tests (Durability is not important)

use UNLOGGED tables throughout

set shared_buffers big enough to contain the whole database

if you have bigger queries, increase work_mem

have enough RAM to contain shared_buffers plus work_mem times the number of client connections

Typically, you can gain most by tuning the queries that use most of the time.


user table
---------
USERID
EmailAddress

User Details
-------------
USerDetailID
USERID (FK)
FirstName
-----

USER_Contact_Mapping
--------------------
USERcontactMapID
UserID
contactID

User table hold info of both User and contact.This table can by utilise to hold other
info which won't be duplicated.

User Details table hold info of both User and contact.This table contain multiple detail of same user.
Example

USER_Contact_Mapping contain relation between USERID and ContactID.This table is must.


ER Diagram Design: Should every entity have a primary key? And can we have sub entity?

As far as primary keys, ALL TABLES HAVE THEM. It's a little over the top, but I've seen too many databases that lose their data integrity because the developers didn't want to lock down a PK ID field off the start. I would have a table for persons and a table for companies, with an attribute field to mark them as a seller.

For the company point of contact, just make it a foreign key relationship to the persons table (but only there will only ever be one POC). If there could be more than one POC, then make a join table between the two. Graphically, you look pretty good there from what I can tell. If I wanted to nit-pick, I would make Point Of Contact an attribute of the Company Table, with a "IS A" link to the Persons Table.

Your last question gets into a gray area of DB Design. You're asking about a "Business Rule." The best approach to this is to have something in the application to check that the Seller_ID does not match a Company_ID. It would probably end up being a bit of logic in the stored procedures. Trying to design that into the database any other way won't be easy, or maintainable.


Anyone want to try a script bash or python?

DISCLAIMER : Not a Python Programmer

I can give you a generic algorithm

Let's say you have 4 DB Servers

replicator@10.1.2.% is the Replication User
1 Master (10.1.2.10)
3 Slaves (IP Addresses : 10.1.2.20,10.1.2.30,10.1.2.40)

STEP 01) Run this query

SELECT host FROM information_schema.processlist WHERE user = 'replicator';

Collect host columns into a list.

STEP 02) For each host collected into the list, run

SHOW SLAVE STATUS;

and retrieve the Relay_Master_Log_File column. Collect these in an array.

I wrote about why to pick that field and not Read_Master_Log_File

Aug 02, 2012 : Deleting bin logs in replication environment
May 26, 2012 : Trying to understand SHOW SLAVE STATUS\G
Jan 24, 2012 : Mysql master-master replication auto flush old logs
Jan 03, 2012 : what are the conditions under which mysql replication might break?

STEP 03) Sort the array of Relay_Master_Log_File names. Select the first entry.

Suppose you collected

mysql-bin.00124
mysql-bin.00123
mysql-bin.00125

After sorting, you would pick mysql-bin.00123

STEP 04) Purge binary logs on the Master using the select binary log

PURGE BINARY LOGS TO 'mysql-bin.00123';

That's it.

Give it a Try !!!


Why would someone put `where 1=1` in a query?

Some dynamic query builders include this condition so that any "real" conditions can be added with an AND without doing a check like if (first condition) 'WHERE' else 'AND'.


How can I copy the MySQL EXPLAIN result into a table?

If you are running on Linux OS you could save it into a file.

Example:

User='root'
Pass='text'
Host='10.0.0.223' (If it's remote)

On command line run:

mysql -u $User -p$Pass --host=$Host -e "EXPLAIN SELECT * FROM mysql.user LIMIT 0,2" | tee -a test.txt

mysql -e will execute the query on the command line.

tee -a will read from standard input and write to file test.txt

And you'll get:

You can use perl to replace the death lines to any delimiter you want (I used comma in this example).

perl -wnlpi -e 's/\s+/,/g;' text.txt

Example:

Hope this help.


SQL Query Joins Multiple Tables - Results in Duplicate Rows

This means there is 1 to Many relationship on the third table. You either need to add more in your join to make it 1 to 1 or create a crosswalk table to do the same.


Not all MySQL processes are killed when restarting

Seems the issue for us was the use of the old-style /etc/init.d/mysql script, rather than the service mysql restart script provided by Upstart. We haven't had these issues when using the Upstart commands.


MySql - How to monitor the amount of reads/writes to the DB

In the table GLOBAL_STATUS in information_schema.

Use the field Com_select for reads and sum the fields Com_delete, Com_insert, Com_update, Com_replace for writes.

These values are cumulativ counters, you should subtract values between an interval:

if t1=t0+60seconds, Com_select at t1 - Com_select at t0 = reads in 1 minute


Moved database (percona mysql) to larger disk, now everything is missing (though the files exist)

Ok, after about a billion headaches I finally figured out what was wrong. Here it is: Some tables are MyISAM and some are InnoDB. I didnt know that when I started, which would have totally helped. So, what ended up happening is that when I moved the files to a temp directory, I guess I messed up the InnoDB tables alignment or whatever you call it, anyway the results where that all the sites that were InnoDB didnt work because I didnt do proper InnoDB moving and it couldnt find those tables.

So what I eventually realized is that I had been making backups of the database and that I could probably restore the database and get the InnoDB tables back inline where they should be. So what I ended up doing was mounting a new disk at /var/lib/mysql and then restoring the databases from a backup onto the new filesystem that was mounted for /var/lib/mysql.

Once that was done, I restarted MySQL and it is now running like nothing ever changed. Geesh.. what an adventure!


I have 2 queries with only one difference that I want to merge in just one query

It will be something close to

SELECT
    titulo_id as titulo_id,
-- calculate both SUMs, select values for each dependent by what value it matches to
    SUM(CASE WHEN ativos_extratos.data_import_id = borders.min_id THEN valor_bruto_atual END) AS valor_min,
    SUM(CASE WHEN ativos_extratos.data_import_id = borders.max_id THEN valor_bruto_atual END) AS valor_max,
    data_imports.data_import AS created_at,
    week(data_imports.data_import) AS weeknumber
FROM
    ativos_extratos
        INNER JOIN
    titulos ON titulo_id = titulos.id
        INNER JOIN
    representantes ON representante_id = representantes.id
        INNER JOIN
    data_imports ON data_import_id = data_imports.id
-- moved subquery and condition by it from WHERE
    INNER JOIN ( SELECT
                    MIN(ID) as min_id,
                    MAX(ID) as max_id
                 FROM
                    data_imports
                 WHERE
                    data_import BETWEEN '2018-11-01' AND '2018-12-10'
                 GROUP BY week(data_import)
               ) borders ON ativos_extratos.data_import_id IN (borders.min_id, borders.max_id)
WHERE
    user_id = 1
-- removed subquery and condition were there
        AND data_imports.data_import BETWEEN '2018-11-01' AND '2018-12-10'
GROUP BY titulos.nome_titulo , weeknumber

I am too lazy to debug - do it by yourself.


Amazon RDS replication with external slave

For RDS to have binlogs, there are a couple non-obvious tricks.  First, you need to set the "backup retention period" to at least 1 day.  This turns on binlogging.

Then, in order to prevent a ridiculous rollover rate in the logs:

call mysql.rds_set_configuration('binlog retention hours', 24);

Here's some documentation that Amazon provides for dumping the database and starting up the replication:  http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html


How to store data having a fixed number of columns and a variable number of rows

Based on your image, something like the following will be fine (using a RDBMS solution):

create table articles
(
  article_id     integer PRIMARY KEY,
  title          varchar(256) NOT NULL,
  url            varchar(256) NOT NULL,
  summary        varchar(256) NOT NULL,
  body           varchar(4000) NOT NULL
);

create table article_comments
(
  comment_id    integer PRIMARY KEY, -- primary key, unique, does not map to the "comment number" in your question
  article_id    integer NOT NULL, -- references articles.article_id
  screen_name   varchar(100),
  disqus_url    varchar(256),
  comment_dt    datetime, -- both the date & time from your spreadsheet
  comment_text  varchar(4000),
  comment_url   varchar(256),
  comment_post_id varchar(25)
);

Basically, there's a one-to-many relationship between articles and comments.

You don't say what RDBMS you'd want to use, so the data types will need changing accordingly (bigger VARCHARs/TEXT columns etc will be needed for long articles/comments etc). Constraint DDL is also largely RDBMS-dependent, so you'll have to look the syntax up for yourself once you've made a decision. I can recommend Postgres.


Maybe you need windowing functions like:
select
RANK () OVER (
    PARTITION BY name
    ORDER BY epoch desc, version desc, release desc
        ) namerank,
name,
epoch,
version,
release,
architecture
from packages
order by name,namerank;

But it does not list the versions in the correct order because it sees 0.1.7 as greater than 0.1.10 which it is as a character string. You might need version split into multiple columns of type number to get 0.1.10 greater than 0.1.7 or convert it to a number that keeps the order you want. I tested this on Oracle since I don't have sqllite or postgres handy. Output:
  NAMERANK NAME                                                                  EPOCH VERSION                          RELEASE                                                          ARCHITECTURE
---------- ---------------------------------------------------------------- ---------- -------------------------------- ---------------------------------------------------------------- ----------------
         1 my-date-based-rpm                                                         0 2021010128002                    0                                                                x86_64
         2 my-date-based-rpm                                                         0 2021010128001                    0                                                                x86_64
         1 my-first-rpm                                                              0 0.1.7                            0                                                                x86_64
         2 my-first-rpm                                                              0 0.1.10                           0                                                                x86_64
         3 my-first-rpm                                                              0 0.1.0                            0                                                                x86_64
         1 my-second-rpm                                                             1 0.1.0                            0                                                                x86_64
         2 my-second-rpm                                                             0 0.1.0                            0                                                                x86_64
         1 my-third-rpm                                                              0 0.1.1                            0                                                                x86_64
         2 my-third-rpm                                                              0 0.1                              0                                                                x86_64

Bobby


SUM n rows of each group with ORDER BY and LIMIT where the LIMIT is based off of another table

The solution I ended up implementing was to break up the query into simpler ones based on the different permutations of the positions and formations. I also needed to add several tables to store intermediate results. I ran all the queries (around 42) in a transaction.

This worked out pretty well and completed in reasonable time without overloading the server resources.

I guess trying to do it in one giant query was a bad decision from the beginning. Breaking it up into smaller queries allowed the process to be maintainable and flexible.


Two things.

Use the tool you already know.   This simplifies development, and reduces the desire to prematurely optimize.
Fit your environment to the requirements.  The hardware required to store 400,000 x 10 byte rows per day is vastly different from 400,000 x 10,000 byte rows.

Define your requirements by mocking up some sample data in MySQL, along with a sample of the queries you'll run.  See how much space this sample takes up, and do the math to determine the requirements for your data over a 1 month, a year, and so-forth.  This will give you a much better idea of the kind of hardware you'll need.

My answer talks about the requirements for storing billions of rows in SQL Server and shows some of the things to consider.  The other answers on that question are also quite instructive.  As I note in my answer, don't store an IP address as a varchar.  Use an integer.


Small starting clarification: the article you linked to on InnoDB text/blob storage is a little out of date with MySQL 5.5, and the barracuda row format.  This article is more up to date.

On to your question:

In the schema you have, each row will be less than ~8K, so you can guarantee across both antelope and barracuda row formats that all data will be stored in-line - not requiring external/overflow pages.

If you were to require overflow pages, they are never de-duplicated (which is what I would probably describe your 'pooling' mechanism as).  Even worse than they are never de-duplicated, they are never shared...

If you could have a record too big to fit inline (~8K limit), each text/blob that needs to be moved out will take a minimum of a 16K page to itself.


Should I rely on Database Engine Tuning Advisor for creating indexes?

Database Engine Tuning Advisor, like the DMVs such as sys.dm_db_missing_index_details and sys.dm_db_missing_index_groups give you a good place to start, as does the dmv sys.dm_db_index_usage_stats, to see which indexes are not being utilized and could be dropped.

However, these tools are never going to be as good as a skilled DBA making rational descisions about what indexes are really required. For example, DTA might recommend dropping an index that is hardly ever used and is expensive to maintain, but with business knowledge, you know that this index is key to running the monthly payroll.

In short - Use these tools to get a head start, but rely on your business knowledge, and performance troubleshooting skills as the final descision maker.


Restore one table from SQL Server data file (.mdf)

Yes, the easiest way is to attach the MDF (if you can). Not all abandoned MDF files are attachable, it depends on how they were detached and what state the server was in when that happened. The proper way to take a backup is BACKUP DATABASE.

There isn't a way that I know of to extract only a single table from a detached MDF file, unless you're really, really comfortable with a hex editor.

So I think you are on the right track - attach your MDF as a different database name, and extract the data from the one table you need. Shark provided the syntax you'll need. However, again, a disk image does not necessarily leave an MDF in a usable state, so without a proper backup, all I can do is cross my fingers for you.


Error importing back a postgres db after dumping

The problem is that you cannot COPY FROM stdin with pgAdmin (no stdin available in the query window), but you can easily restore the dump with psql

psql <database> -h <host> -p <port> -f <dump-file>

Hope that helps.


Error when building documentation of Postgres

Error: ENOENT, no such file or directory '/Users/thomas/Source/postgres/doc/src/sgml/-p'

I'd say you have some ancient version of one of the required tools, and it's seeing the -p command-line switch as a regular path argument instead.


MySQL store current binary log in different folder from past binary logs`

Why?  Replication was not designed with the thought of keeping binlogs for very long.  Very few systems ask to keep them longer than 2 weeks.  To use very old logs is costly due to the effort to rebuild the system and replay the logs.  It is usually better to periodically take some form of snapshot.

That being said, I will get back to your Question.  How about the following...  You write a shell script that runs daily and does:

If less than X GB of data in uncompressed binlogs, exit silently.
Copy the oldest binlog to the compressed filesystem.
Replace that binlog with a soft link to the copy.

Meanwhile, set up MySQL to never purge binlogs, and add something to your script to do the equivalent purging.  (I think there are some edge cases that will go haywire otherwise.)

Any reference from MySQL to the compressed binlogs should work fine; however there won't be any (unless a Slave is more than X GB behind).


COUNT number of column values with INNER JOIN

Will it work for you ?

SELECT a.id as question_id, a.costperlead, a.costperlead *(b.numYes+b.numPossible) as revenue,
b.numYes,b.numNo,b.numPossible
FROM questions a
LEFT JOIN
(
SELECT a.question_id,
count(case when a.response='Yes' then 1 end)AS numYes,
count(case when a.response='No' then 1 end) AS numNo,
count(case when a.response = 'Possibly' then 1 end) numPossible
FROM responses a
GROUP BY a.question_id
)b ON b.question_id = a.id;

(http://sqlfiddle.com/#!15/6ce465/46)

Note: questions without responses will be also included in resultset. If it's not needed , replace LEFT JOIN with INNER JOIN.


The process is running and is waiting for a response from the server - transactional replication

The Query Timeout message you are seeing is your Distribution Agent timing out, not your Log Reader Agent.  You need to increase your Distribution Agent profile Query Timeout parameter value to alleviate the error.  Please see the section To view and edit the parameters associated with a profile in Work with Replication Agent Profiles to edit the QueryTimeout parameter value for your Distribution Agent.

Note that if you run your Distribution Agent continuously that you will need to restart your Distribution Agent for the profile change to take affect.


Defining SQL Server domain/AD service accounts during installation vs. after the fact. Anything outside of "personal preference/time saving"?

Although it is going to be a bit of a pain to go back and change all of them, even if it can be done with scripts, there is one factor that I would consider beyond all of the technical stuff that can obviously be addressed: Anxiety.

Given the two scenarios:

Installed with domain service accounts
Installed without domain service accounts and changed afterwards

Assume that something goes wrong a few days after all of these servers get put into production.  Let's say a Windows security update gets installed on domain controllers, or the organizations routers get patched, or some new security software gets pushed out to every server.  Suddenly, your SQL Servers stop working because they get login failures.  You may not know about the change in the environment.  If you went with scenario #1, you're going to have zero, or much less, anxiety that the installation/setup process was the cause of the issue.

If you used scenario #2, you are going to have more anxiety that you missed something, screwed something up, etc.  I would, anyway.  Some people wouldn't, but I think I lot of us would have a bit of panic.  Additionally, would other people in the project possibly start pointing out the post-install service account change if there was an outage of this nature?  Regardless of whether it is a reasonable concern, someone may start questioning anything that is out-of-the-ordinary.

So if it's a server or two and an outage isn't going to cause you any anxiety--no big deal.  But if an outage is going to cause anxiety, I would stick with scenario #1 if possible.


Best way to create searchable and sortable product database

If an index can be defined on the search criteria then this will work OK at any scale. BTree indexes have O(log N) performance IIRC so working through 100,000 rows will be 5/3 the time needed for 1,000 rows.

However .. most systems can't fulfill that criteria. Often a user can fill as many or few search fields as she chooses. Then things get complicated. Sometimes it may be best to define separate queries, and indexes, for each combination of search values. This quickly gets large. 5! = 120; that's a lot of indexes and queries. Some DBMS have optimisations for generic searches - OPTION (RECOMPILE) in SQL Server, for example, will cause parameter embedding. Probably some combination of often-used specific queries and a generic catch-all will work.

As the list gets hugh users will expect ever fuzzier searches. At some stage a full text search on the product name and / or description may be useful. Maybe, eventually, you offload that task entirely.


Partial (filtered) replication in PostgreSQL

Multiple people suggested using the pglogical extension, which turned out to work really well for this use-case.


If you want to insert Unicode text in your update statement you need to supply a Unicode string of the form N'...', as in

update foo set test = N'褠ఓ�ᵂ뱗텬㰗' where id = 1

Otherwise the characters really are converted to question marks if SQL Server is unable to map them to corresponding non-Unicode characters, even in an nvarchar column.


Creating a job scheduled based on a specific timezone

The following creates a job to execute daily at 17.30 local time specified by country.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'update_sales',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
   start_date         =>  '07-AUG-13 17.30.00 PM India/Delhi',
   repeat_interval    =>  'FREQ=DAILY',
   end_date           =>  '07-AUG-14 17.30.00 PM India/Delhi',
   auto_drop          =>   FALSE,
   job_class          =>  'batch_update_jobs',
   comments           =>  'My new job');
END;
/


Why SELECT query is waiting on HADR_SYNC_COMMIT?

Since the HADR_SYNC_COMMIT wait indicates that the primary is waiting for a log block to be hardened on the secondary, it doesn't really make sense for a plain select query to cause this wait to accumulate - since a select query does not generate any transaction log to be sent.

How are you gathering that wait info?  The most likely reason for that wait is that there are inserts, updates, or deletes being executed, and then the select runs within the same transaction, and however the waits are being accounted are including the previously accrued HADR_SYNC_COMMIT waits alongside that select statement.

If the select statement were really running on it's own outside of a transaction, and it's trying to read data that's part of an active transaction (that's waiting on HADR_SYNC_COMMIT):

without RCSI, I'd expect to see LCK waits for the select query
with RCSI (your scenario), I'd expect the select to just work (since writers shouldn't block readers in this case)

Quick point of clarification: since you have Read Committed Snapshot enabled and turned on, any queries using the READ COMMITTED isolation level are [automatically using RCSI.  This is just how RCSI works:

  The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:
  ...
  If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data...

You mentioned that the client sending the queries is enforcing READ COMMITTED.  It could be that it's forcing the use of shared locks vs snapshots via a READCOMMITTEDLOCK table hint.  But I still thought I'd clarify for completeness.


What is the best way to purge data from a table with minimum damage to performance?

You can rebuild or defrag, depending on the nature of the data that was deleted, the number of indexes, and how badly they were impacted. If you know the fragmentation before the delete, it would be easy to assess the delta from sys.dm_db_index_physical_stats; if you don't, then you could just apply the same rules you normally would when determining whether to defrag, rebuild, or neither. (You could also take this time to look at sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats to validate that all of your indexes are necessary and are being used. Sometimes the best thing to do is drop an index.)

You could also break the deletes into chunks (see this article), and perform incremental maintenance in between, so that your maintenance at the end will be minimal. That may be more intrusive than a single operation, but it would be broken into smaller bursts over a longer time.


What data is duplicated when MySQL/MariaDB BLOB columns are copied?

Separate copy.

Copying just a disk pointer would necessitate "reference counting", locks in places where there are none now, etc.

You could implement such by keeping the BLOBs in a separate table and somehow link to them.  You could use MD5 (or other digest) for de-dupping.  However, I suspect the benefit would be outweighed by the complexity and potential for bugs.

"for a long time" -- If there are many rows being copied in the same transaction, note that there is extra effort to prepare for ROLLBACK in the case of a crash.


How can I create a MongoDB based on MySQL schema?

MongoDB uses flexible schema and you do not have to predeclare your field types in the database. MongoDB (as at 3.0) does not provide any server-side schema validation, so it's perfectly allowable to have a field with different types within the same collection or to have fields vary by document within the same collection.

Schema declaration & validation is optional, but if you want to enforce types you would have to do so via your application logic. There are analogous MongoDB BSON field types for most RDBMS column types, but also obvious differences. For example, MongoDB has a String field which stores variable length text (up to ~16MB) rather than distinct VARCHAR(..) and TEXT types.

In general you will not want to use identical schema when exporting data from a relational database to MongoDB: you would instead consider how best to model your data to support common use cases. For a more complete overview including concepts and examples please refer to the Data Modeling section in the MongoDB manual.

You have many options to migrate data, for example:

using client drivers to read/write from your source & target databases
exporting to a CSV or JSON format which can be read by the mongoimport command line tool
using an ETL tool


Get total size for all Postgres or Greenplum tables including partitions

Here is what i have, its also a little bit roundabout and not testet in PG8.2
(not supported anymore ;) ) but it shows your schema.tablename,PrettySize (first Select) and  partitioned Tables are grouped to one (ParentTablename) (second Select). its maybe not the best solution (Tuning welcome)

--get all tables sizes and group partitioned tables by PartenTablename
SELECT nspname || '.' || relname AS "relation",
       pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
LEFT OUTER JOIN pg_inherits I ON (C.relfilenode = I.inhrelid OR C.relfilenode = I.inhparent)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    AND I.inhrelid is null
UNION ALL
select nspname || '.' || relname AS "relation",
       pg_size_pretty(sum(pg_relation_size(inhrelid))) AS "total_size"
FROM pg_inherits  I
LEFT JOIN pg_class C ON (C.oid = I.inhparent)
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
GROUP BY  nspname,relname
ORDER BY 1,2


ORA-39001: ORA-39000: ORA-31640: ORA-27041: and Linux-x86_64 Error: 13: Permission denied

The main issue is Linux-x86_64 Error: 13: Permission denied

cd /u01/app/oracle/admin/XE/dpdump/
 chmod 777 -R gsw

Then login as oracle user and run the same command again


How to find out the segment behind a corrupted block when the database cannot be opened?

Well, I guess the database is expendable since there is no proper backup. So just drop and recreate the database.

OK, seriously there are not many options left. Before you do anything shutdown the database and copy all files which belong to the database somewhere else! Because everything you do can make it worse.

Once you did that you have 2 options:

Open an Oracle Service Request. With their help try to restore the block with lots of tricks and magic.
Call the Oracle Consulting Team, they have unpublished/internal tools which might be helpful.

This database is not in archivelog mode so there is no option to restore the block from a backup. If you still have an rman backup of datafile 1 you might be able to restore this file and then open the database with _allow_resetlogs_corruption (Don't use this option on your own!). Because you cannot fully recover the block, you have to tell Oracle that you don't care that the datafile do not have the same SCN. See details here: https://practicalappsdba.wordpress.com/2008/04/01/how-to-recover-and-open-the-database-if-the-archive-log-required-for-recovery-is-missing/

If this is a block which contains standard objects you might be able to extract the block from a healthy database and inject it on this block.

Good luck!


If the external company is not willing to correct their export, then you can set up your own lookup table.

CREATE TABLE dbo.Companies
(
  CompanyID int IDENTITY(1,1) PRIMARY KEY,
  CompanyName nvarchar(255)
);

Now, when you get a new import, I assume this goes into some temporary ETL table. So you do:

INSERT dbo.Companies(CompanyName)
  SELECT company_name
  FROM dbo.ETL_Table AS etl
  WHERE NOT EXISTS
  (
    SELECT 1
      FROM dbo.Companies
      WHERE CompanyName = etl.company_name
  )
  GROUP BY company_name;

Now you can join against your lookup table on the name, and that join can produce the IDs you need to use in your own referential integrity.

This does not handle the case when a company name changes, and I don't know how you can detect that.


Why isn't the objectname and indexname populated in deadlock graph?

I can't answer as to why there is information missing, but I can point out how to extract it.

First, move to that database:

SELECT DB_NAME(25);

Take that result and:

USE <db_name>;

Now find out what these hobt_ids represent:

SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id)
FROM sys.partitions
WHERE hobt_id IN (72057594099728384, 72057594099793920);

You can also find the batches involved (if they're still around) using:

SELECT * FROM sys.dm_exec_sql_text(0x03001900b9667f2bc41ade003ea000000100000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x03001900f28a732c051bde003ea000000100000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x030019002cf5fc35c2449200d2a000000100000000000000);

The offsets are there in the ExecutionStack so you can drill deeper into that output if you like (many examples of using offsets online).

EDIT Mr. Sandwiches pointed out the following Connect item (no longer available), where Microsoft has stated that this bug is fixed in SQL Server 2012. It won't be fixed in 2008/R2.

http://connect.microsoft.com/SQLServer/feedback/details/635391/objectname-indexname-not-populated-in-deadlock-graph-in-sql2008-sp1-and-sql2008-r2


ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

You have set aside, 12000M for your InnoDB DataFiles (ibdata1...ibdata12). The only possible way for you to have this error is if all 12000M of InnoDB DataFiles have no more room to accommodate new rows into it. How is that possible?

There are four types of information that reside in InnoDB DataFiles

Table Data Pages
Table Index Pages
Table MetaData
MVCC Data

MVCC is Multiversion Concurrency Control. This facilitates ACID Compliance and Transaction Isolation for every SQL transaction, whether it is a single SQL statement or a block of SQL Statements. Whenever you run SQL against InnoDB Tables, that will definitely involve transaction control thus introducing new MVCC Data. Even if you do not execute START TRANSACTION...COMMIT/ROLLBACK paradigms in your application, AUTOCOMMIT is on by default. That will cause InnoDB to write MVCC Data around any data you are reading and/or writing. If there is enough MVCC in the InnoDB DataFiles, it could potentially block InnoDB row data of a certain length from being written.

You have three options to make this go away:

OPTION 1 : Add one or more InnoDB DataFiles to innodb_data_file_path

innodb_data_file_path=ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M;ibdata6:1000M;ibdata7:1000M;ibdata8:1000M;ibdata9:1000M;ibdata10:1000M;ibdata11:1000M;ibdata12:1000M;ibdata13:1000M;ibdata14:1000M

OPTION 2 : Add autoextend to the laste InnoDB DataFile in innodb_data_file_path

innodb_data_file_path=ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M;ibdata6:1000M;ibdata7:1000M;ibdata8:1000M;ibdata9:1000M;ibdata10:1000M;ibdata11:1000M;ibdata12:1000M:autoextend

OPTION 3 : Cleanup the InnoDB Infrastructure

This would be the most enduring solution because there is an option to keep Table Data Pages and Table Index Pages from ever entering the InnoDB DataFiles. You would have to set this option in my.cnf

[mysqld]
innodb-file-per-table

This creates a separate tablespace (.ibd) file for each InnoDB table create after you restart mysql with this new option. Just putting in the option and restart mysql will not create the tablespace file. The added bonus for doing this is that you can collapse the innodb_data_file_path to the default:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

I wrote up instructions on StackOverflow on how to do this.


Databases outside of AlwaysOn availability group stuck in RESTORING status

How did you setup your Availability Group?  It sounds like what happened is you blanketed the backups and restores from the primary replica server to the secondary replica server.

In other words, you did a:

backup database <DatabaseName>
to disk .... <so on and so forth>

to all databases on the primary and a

restore database <DatabaseName>
from disk ....
with norecovery

to all the backups aimed at the secondary replica server (proper operations for future AG databases).  If you don't join one of those databases to the availability group then it'll stay in that restoring state.

The main question here is, if Database1 (for example) on your primary replica server as not meant to be in the availability group, then why would it be restored/existing on the secondary replica server to begin with?


PL/SQL - Member Function: Link directly to object without parameters

Forget my comment.  I was thinking of something else.

Your question is really about "How do I access the MEMBER attributes from within a MEMBER function?".

In this case, you use the keyword SELF as the current instance's name.

Spec

CREATE TYPE "CUSTOMER" AS OBJECT (
    customer_id           NUMBER,
    customer_firstname    VARCHAR2(30),
    customer_lastname     VARCHAR2(30),
    customer_location_id  NUMBER,
    creation_date         DATE,
    MEMBER FUNCTION "FULL_YEARS" RETURN NUMBER
);
/

Body

CREATE OR REPLACE
TYPE BODY CUSTOMER AS

  MEMBER FUNCTION "FULL_YEARS" RETURN NUMBER
  as
     v_years  NUMBER;
  BEGIN
    -- don't do the SELECT INTO FROM DUAL thing.  that is just ugly code.
    v_years := FLOOR(MONTHS_BETWEEN( sysdate, self.creation_date)/12);

    RETURN v_years;
  END "FULL_YEARS";
END;
/


I suggest creating a single common database. In this common database create your user table.

Instead of a login_has_site field, have a table of access that can be joined:

primary user id (of common table)
client name (other database)
user id in client db

After you have have that you can start to deprecate the fields in the user table across all but the common database's user table.


It's just the missing keyword AS:

CREATE TYPE pixel AS (r "char", g "char", b "char");

The data type "char" is a non-standard type, primarily meant for internal purposes. But it's just another type - except for the peculiar spelling of the name including the double-quotes to disambiguate against char.


SQL Logins in db_owner role on database cannot create stored procedures

Interesting one - difficult to pin this one down. Have you thought about looking at the public role?

sp_helprotect 'CREATE PROCEDURE',NULL,NULL,'s'

Does that bring you back anything?


Problem to apply replacement with regexp that include look ahead in Postgres

Look-ahead are zero width.  They don't match anything themselves.  So your replacement operations is just replacing the empty string with the empty string, under certain conditions.

Simply removing the look-ahead designation makes it give the answer you want for your one example:

select regexp_replace ('12s2-3 abc def', '^.*\d', '');


depending on how you want to fetch the data. If you want to get all by item, then item as a primary key is a great choice. If you want to filter by item and url then i would add primary key Item and sort key url. If you want to have url and search for the item by url, i would add url as primary key and url as sort key. If you want to be able to do both, i would add primary key for item, sort key for url and then add new GSI with primary key url and sort key item.

I learned a lot about this from this conference: https://www.youtube.com/watch?v=jzeKPKpucS0 recommended.


Unique code based on category - a sort of auto_increment

Assuming you are using an ACID compliant engine (i.e. InnoDB) the "get max, add one, then insert" method should work if wrapped in a transaction.

I would query the requirement for the user code to be a specific item though given you have a user ID which is presumably an auto-incrementing integer. If there is no need for codes to be sequential within a region (i.e. it is OK to have ITRY000003 and ITRY000042 but nothing in between because users 4 through 41 are in other places) then you could just have the user code be a derived value constructed from [district_code]+PadWithZeros([user_id]). THis menas you don;t have to worry about generating the extra code (which I assume is needed for display purposes mainly?). You may still need to store it if the users could move between districts but you'd like to keep their original code if they do. If it isn't required that the code be static if the user moves districts then you don't need to store it: it can be derived when needed.


Ensuring Only Fresh Data Gets Accessed on SQL Server 2008/2012 Replication For Reads

Transactional replication can be used for something like this, but it would be tricky. However it sounds like what you are doing is keeping multiple front facing caches up to date.

This is tricky as a polling mechanism limits scalability. What works best is a push mechanism where if a change occurs, or some data meets a certain criteria it is send to all caches, in other works a messaging framework.

If this is to be done within SQL Server, service broker might be a better fit if the number of nodes are high and the transactions low. If the transactions are high and the number of nodes relatively small, transactional replication might be a better fit.

If the database is a backend for an app which is providing global failover services an application solution will likely be a better fit. I happen to know a guy who implemented a similar caching mechanism for a large auction site and then


Speed-- Restoring from Azure Blob to local database

You can also turn on Trace Flag 3051 when doing the restore to
  generate a detailed log. This log file is placed in the log directory,
  and is named using the format:
  BackupToUrl---action-.log. The log file
  includes information about each round trip to Azure Storage including
  timing that can be helpful in diagnosing the issue.

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restoring-from-backups-stored-in-microsoft-azure?view=sql-server-ver15#optimizing-restores

And you can also use AzCopy to download the backup to a local disk first.

Or overwrite a daily backup on a local disk in addition to the Azure backups.


This is valid syntax, too:

       sum(sum(qty)) over ()

It's a bit confusing when one sees it at first but you only have to remember that the window functions - e.g. sum() over () - are applied after the group by so everything that can appear in the select list of a group by query can be placed inside a window aggregate. So (the qty cannot but) the sum(qty) can be placed inside sum() over ():

select sum(qty), itemid, proddte,
       sum(sum(qty)) over () as grandtotal
from testtable
where ....
group by itemid, proddte ;

Having said that, I'd prefer the GROUPING SETS query provided by Aaron Bertrand. The total sum needs to be shown once and not in every row.

Also note that while the sum of sums can be used to calculate the total sum, if you wanted the total count, you'd have to use the sum of counts (and not the count of counts!):

sum(count(*)) over ()  as grand_count

And if one wanted the average over all the table, it would be even more complicated:

sum(sum(qty)) over ()
/ sum(count(qty)) over ()  as grand_average

because the average of averages is not the same as the average over all. (If you try the avg(avg(qty)) over () you'll see that it may yield a different result than the above grand average.)


SQL Server - Bulk load blocking queries on heaps

Optimized bulk load means SQL Server is using a special bulk load code path (fast load) and minimal logging.
These concepts are described in:

SQL Server 2016, Minimal logging and Impact of the Batchsize in bulk load operations
Prerequisites for Minimal Logging in Bulk Import
The Data Loading Performance Guide

From that last link:

Although readers under RCSI are not affected by X locks, there are two bulk load situations that will block RCSI queries (as well as NOLOCK queries) in SQL Server 2008:

When populating a heap with TABLOCK using BULK INSERT, INSERT-SELECT, or other bulk load operations. This is because the heap load acquires a BULK OPERATION intent exclusive (IX) lock and the NOLOCK or RCSI readers acquire a BULK OPERATION shared (S) lock. To bulk load a heap and permit concurrent readers even using RCSI or NOLOCK, you must eliminate the TABLOCK hint and thus give up minimal logging.


How to take physical backup of MYSQL database

You literally do not need a third party tool.

STEP 01

Find out the path to the data by running

mysql> SHOW GLOBAL VARIABLES LIKE 'datadir';

For this example, let's suppose it is C:\Program Files\MySQL\MySQL 5.6\data

STEP 02

Pick a destination folder to copy the data directory

For this example, let's pick D:\MySQLBackup

STEP 03

Create batch file called C:\RunMySQLBackup.bat with the the following commands

@echo off
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
set MYSQL_DATA=C:\Program Files\MySQL\MySQL 5.6\data
set MYSQL_BACK=D:\MySQLBackup
mysql %MYSQL_CONN% -ANe"FLUSH TABLES; SET GLOBAL innodb_fast_shutdown=0;"
net stop mysql
xcopy /s %MYSQL_DATA%\*.* %MYSQL_BACK%\.
net start mysql

CONCLUSION

Just run C:\RunMySQLBackup.bat on demand or add it to the Windows Scheduler. This is just a rough backup script and it only keeps one copy.

This is not the first time I discussed this. I have another post MySQL backing up MyISAM tables by copying the physical files - and automating the process for copying an all-MyISAM database.

You should think about doing mysqldumps since you can get the data at a point-in-time. Should you want to go back to mysqldumps, I have another post I wrote on how to do mysqldumps in Windows with named backup filenames based on the data and time (mysqldump with automatically generated export name).


MEMORY_OPTIMIZED table as replacement for temporary tables

In your case, you'd have to use a memory-optimized table variable, and assuming that the child procedures are called by the same SPID, there shouldn't be an issue with visibility. Please see this link:

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization

I will caution you that the cardinality estimator still assumes 1 row for memory-optimized table variables, and this can be an issue for performance.

More info at this link:

https://blogs.msdn.microsoft.com/psssql/2017/05/10/memory-optimized-table-variable-and-cardinality-estimate/


Is timestamptz preferred when timezone logic is performed by the client?

Is timestamptz still preferred if the application always inserts/reads UTC and converts to local datetime itself?

Well, no. Then it's very slightly simpler / faster to use timestamp.

But is "the application" the only client accessing the database? And is it going to stay this way for the lifetime of the DB?

If doubts remain, I would still use timestamptz, it's the safe choice. You already found my related answer on SO I would suggest as reference for basic information:

Ignoring time zones altogether in Rails and PostgreSQL

Also, timestamptz literally is the "preferred" type among "Date/time types" in Postgres (tagged typeispreferred in pg_type), which can make a difference, but probably not to your particular question, even though the question title almost sounds like you might be asking for that. Related:

Generating time series between two dates in PostgreSQL


Hallengreen's Index maintenance query not using all indexes

There is a variable in the script. Something like 'do not rebuild indexes with less then 1000 pages' - this is the default.
Its name @PageCountLevel

You can change it to whatever you wish.


Right Distribution of Databases amongst Multiple Instances

I don't think you're going to come up with a perfect formula. It will be almost impossible to predict who is going to consume most of your resources and at what times. What you will likely need to do is come up with some predictable division (based on information we don't know, like client A is 24/7 and demanding but client B is 9-5 only). You might start with 50 OLTP and 50 OLAP on one server, and the other 50/50 on the other server. The nice thing about database-level separation is that if one of those clients turns out to be really busy, you can move databases around relatively easy to provide better balance - either trading one busy database for 10 quieter databases on the other instance, or perhaps introducing a 3rd instance.

In my previous job we had a system where 500 clients had databases on a single instance. In order to lighten the load and mitigate unpredictable performance spikes, we pushed the reporting stuff that didn't need to be real-time to a secondary server. We would perform a full backup and restore each night, restore with recovery, and mark the database as read only, during a window where they were blocked out of the system. Then they could run their reports all day long with absolutely no locking and they were quite happy.


The reason for the definer clause is that view can act as protective layers above the tables. For example, instead of limiting users to be able to read specific columns from a table with otherwise sensitive data, one can create a view which only presents allows columns, and let users select from that view.

Sometimes you may allow users to read a view even though you forbid them from any access to the underlying tables, in which case security=definer and definer=some_user_usually_root.

Other times views just help out in simplifying common complex joins & aggregations, and have nothing to do with security. In which case you want the users of this view to use it under same set of privileges as they would for underlying tables. In which case security=invoker.

So far I've explained why the security stuff is even there. Now for your second question: what should you do if you don't have the definer user in your new database: for this reason it is common to make the definer root@localhost, which is an account typically found everywhere.

In your case I'm guessing that's not what you have, in which case you'll need to modify the dump file before loading into MySQL. Not to worry, you can do it on the fly with sed. There's no built-in mechanism to fix this for you.


How to create a CSV file which imports data from an Access Database on a Weekly Schedule?

If I understand your question correctly ... you are trying to store the Report Date and Sales Value in a CSV file on a weekly schedule.

Why not save this information in your database? Have a scheduled job run a query to produce your sales numbers. That scheduled job would insert the Report Date and Sales number into a database table. That way you don't have the sales numbers in an external CSV file.

When you want to check the report date you just have to run a simple query to pull back that information. Let me know if I'm missing the boat.

*** EDIT ****
In your post you said "I have access to a database". I suppose I should have first asked if this was an "Access Database" or a "SQL Server Database".


Log for database tempdb is not available

SQLServerLogMgr::LogWriter: Operating system error 170(The requested resource is in use.) encountered.

Yeah, this is bubbled up from the OS error the logwriter is running into. In this case it's OS error 170 which is the resource is in use. That's pretty damning that the issue is outside of SQL Server (barring any bad 3rd party DLLs loaded into the address space).

Normally when I see this it's anti-virus, host protection, defragmentation software, backup software, etc., that ends up holding long locks on the files or in clustered environments when the drives aren't properly setup on the back end.

  Storage team has excluded these files from anti-virus scanning.

Not trying to be rude, but I'm not buying it. Tell them you want proof. Also, just because the file is "excluded" doesn't mean the anti-virus software doesn't look at it, it still does - just doesn't do all the in-depth checks in most cases.

Now What?

I'd start with checking to see if a rogue application is trying to do something. The problem is that this could happen at any time and offending (if any) application could hold the lock for a short time or for a long time - we don't know.

One of the ways to quickly do this would be to use the sysinternals handle application on the file having the issue. Since this error has an error code in SQL Server for it, you should be able to create an agent alert to execute a job which calls a small batch file or PowerShell or whatever you create to run handle against the file. That should help try to grab the information quickly (but might not be quick enough).

Another way to go about it, especially if you can reproduce it fairly frequently or easy, is to run process monitor (procmon) which is another sysinternals tool to capture what is touching that file other than SQL Server.


PLE is a measure of how long (in seconds), pages are staying in memory.  It is more a measure of the age of the current pages, than a prediction.  But if the current load stays the same you should expect the PLE to stay the same.

In reality, it changes, the value grows every second, until there is a big flush then it shrinks.

Related:

Page Life Expectancy (PLE), where to start?
SQL Prove memory pressure - high Buffer Cache Hit Ratio, but low Page Life Expectancy
How can you have a Buffer cache hit ratio of 100% with PLE of 103?


SELECT
    T1.id,
    T1.`name`,
    T1.history,
    T2.rank
FROM
    test T1
LEFT JOIN (
    SELECT
        history,
        (@v_id := @v_Id + 1) AS rank
    FROM
        (
            SELECT DISTINCT
                history
            FROM
                test
            ORDER BY
                history DESC
        ) t,
        (SELECT @v_id := 0) r
) T2 ON T1.history = T2.history
ORDER BY
    history DESC

with result as:

7   jmaany  657 1
3   pae     599 2
9   yanier  599 2
5   shl     295 3
4   spohl   284 4
2   viyrp   217 5
1   yngiid  97  6
8   hxt     80  7
6   okeer   73  8


Mysql fulltext index search returning weird result

According to the MySQL Documentation on FULLTEXT Boolean Searching

  "

  A phrase that is enclosed within double quote (“"”) characters matches
  only rows that contain the phrase literally, as it was typed. The
  full-text engine splits the phrase into words and performs a search in
  the FULLTEXT index for the words. Nonword characters need not be
  matched exactly: Phrase searching requires only that matches contain
  exactly the same words as the phrase and in the same order. For
  example, "test phrase" matches "test, phrase".

  If the phrase contains no words that are in the index, the result is
  empty. For example, if all words are either stopwords or shorter than
  the minimum length of indexed words, the result is empty.

  The following examples demonstrate some search strings that use
  boolean full-text operators:

  'apple banana'

  Find rows that contain at least one of the two words.

  '+apple +juice'

  Find rows that contain both words.

  '+apple macintosh'

  Find rows that contain the word “apple”, but rank rows higher if they
  also contain “macintosh”.

  '+apple -macintosh'

  Find rows that contain the word “apple” but not “macintosh”.

  '+apple ~macintosh'

  Find rows that contain the word “apple”, but if the row also contains
  the word “macintosh”, rate it lower than if row does not. This is
  “softer” than a search for '+apple -macintosh', for which the presence
  of “macintosh” causes the row not to be returned at all.

  '+apple +(>turnover

  Find rows that contain the words “apple” and “turnover”, or “apple”
  and “strudel” (in any order), but rank “apple turnover” higher than
  “apple strudel”.

  'apple*'

  Find rows that contain words such as “apple”, “apples”, “applesauce”,
  or “applet”.

  '"some words"'

  Find rows that contain the exact phrase “some words” (for example,
  rows that contain “some words of wisdom” but not “some noise words”).
  Note that the “"” characters that enclose the phrase are operator
  characters that delimit the phrase. They are not the quotation marks
  that enclose the search string itself.

MAIN PROBLEM

You are searching for 3 ingredients
You are querying for 4 words (Mint, Leaves, Corriender, Greenchili)

COURSE OF ACTION

You need to adjust the MATCH clause to query three string tokens. You do that by putting double quotes around "Mint Leaves" and "Corriender Leaves":

SELECT `Ingredient`.`ingredientID` , `Ingredient`.`rcteID`
FROM `ingredient` AS `Ingredient`
WHERE MATCH (`Ingredient`.`ingredient`)
      AGAINST ('+Greenchili +"Mint Leaves" +"Corriender Leaves"' IN BOOLEAN MODE)
AND `Ingredient`.`rcteID` IN ( 1 )
GROUP BY `Ingredient`.`rcteID`

GIVE IT A TRY !!!

NOTE: Corriender should be spelled Coriander


Variable numbers of parameters in a SqlClr UDF or Stored Proc

I can manually write DDL to reference the stored procedure with any number of variables, defaulting them all to null. However, is there a T-SQL way to say "this stored procedure has a variable number of arguments" in the same way as sp_executesql does?

No, there's no built-in way to do this.

  Is my only option to use an extended stored procedure?

You should avoid writing an extended stored procedure, since these have been deprecated since SQL Server 2005 and generally cause more problems than they solve. There is no really good way to simulate the optional parameter behaviour of things like sp_executesql with a SQLCLR procedure or function.

Various workarounds can be suitable depending on the situation. The most often recommended is a T-SQL procedure or function wrapper, though I have also seen people experiment with passing a SQLCLR type (containing an array of values, for example) as well. The T-SQL equivalent to that would be a table-valued parameter.


In mysql, find all instances of a named column that are not foreign keys

Here's what I eventually came up with. Of course, I'm open to input/refinements from the DBA grandfathers around here! But for now, this approach is giving me the info I need.

USE INFORMATION_SCHEMA;

Get all tables that have at least one foreign key referencing a column in "mytable"

SELECT TABLE_NAME, COUNT(*) AS reference_count
FROM REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'mydb'
    AND REFERENCED_TABLE_NAME = 'mytable' GROUP BY TABLE_NAME;

Get all tables that have at least one foreign key on a column named "mycolumn"

SELECT TABLE_NAME
FROM KEY_COLUMN_USAGE
WHERE CONSTRAINT_SCHEMA = 'mydb'
    AND COLUMN_NAME = 'mycolumn'
    AND REFERENCED_TABLE_SCHEMA IS NOT NULL;

Get all tables that have ANY COLUMN named "mycolumn". The subquery in the WHERE clause AND (SELECT Table_Type FROM TABLES WHERE table_schema = 'mydb' AND TABLE_NAME = COLUMNS.TABLE_NAME) is necessary, otherwise it would return also any VIEW having a column named "mycolumn"

SELECT TABLE_NAME
FROM COLUMNS
WHERE
    TABLE_SCHEMA = 'mydb'
    AND COLUMN_NAME = 'mycolumn'
    AND (SELECT Table_Type
        FROM TABLES WHERE table_schema = 'mydb'
        AND TABLE_NAME = COLUMNS.TABLE_NAME) = 'BASE TABLE';

Now, using the 2 previous queries, we can obtain any tables having a column named "mycolumn" but in which "mycolumn" is NOT A FOREIGN KEY. Note that this filtering will not respect a PRIMARY KEY, and will also return any table where "mycolumn" is a PRIMARY KEY.

SELECT TABLE_NAME
FROM COLUMNS
WHERE TABLE_SCHEMA = 'mydb'
    AND COLUMN_NAME = 'mycolumn'
    AND (SELECT Table_Type
        FROM TABLES
        WHERE table_schema = 'mydb'
            AND TABLE_NAME = COLUMNS.TABLE_NAME) = 'BASE TABLE'
    AND TABLE_NAME NOT IN
        (SELECT TABLE_NAME
        FROM KEY_COLUMN_USAGE
        WHERE CONSTRAINT_SCHEMA = 'mydb'
            AND COLUMN_NAME = 'mycolumn'
            AND REFERENCED_TABLE_SCHEMA IS NOT NULL
        );


wal_level set to replica at database level but i dont see that in configuration file

replica is the default value for wal_level.
“Default” means the value that is used if the configuration files fail to provide a setting for the parameter. It is a value that is hard coded into the postgres executable.
postgresql.conf contains most parameters “commented out” and with their default values. This may be confusing at first, but documents both the parameters and their default values.


How can copy one column data of one table1 into other table2 column and other clumns table2 empty?

You could use INSERT with a SELECT.

INSERT INTO result (id) SELECT id FROM stdd

With this your SQL Server adds per row in stdd one row in result with the id from stdd.

If you can use multiple columns, too.

INSERT INTO result (id, col2, col3) SELECT id, s_col2, s_col3 FROM stdd


Stop using Activity Monitor
Use sp_WhoIsActive to figure out what's actually going on

It could be backups, in which case there's not a whole heck of a lot you can do that doesn't involve hardware upgrades (maybe that 1 Gb iSCSI wasn't such a great idea...)

It could be client-side code consuming data RBAR (think foreach loops for every row coming in), or asking for a lot of rows all at once (this is where paging queries can help).

It could be something else entirely!

To get an idea what your server is waiting on the most, head over to firstresponderkit.org -- full disclosure, I contribute to this open source project -- and grab sp_BlitzFirst (or, heck, grab'em all).

You can run this command to look at your wait stats as a whole, since startup.

EXEC sp_BlitzFirst @SinceStartup = 1

Or this to get a sample of wait stats during a slowdown.

EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1

Hope this helps!


Putting the database in backup mode before making file system changes

The method you describe is ok. This method was the backup method used before Oracle introduced RMAN and is still valid. It is still used by tools that do snapshot backups (like Netapp's backup tool for Oracle) and I often use this in similar situations.
You also should make a controlfile backup if all the controlfiles are on these disks.


SQL Server Linked Server to Progress is slow with an openquery view

in your query above, every time some one views your view, an OPENQUERY is executed to your server

why don't you run a job that transfers the data from your progress server to your sql server and create a view pointing to a table in your server locally.

that will be much faster

CREATE PROCEDURE [dbo].[copydatafromProgress]
AS
BEGIN

insert into tbl_accounts
SELECT * FROM OPENQUERY(myLinkedServerName,'SELECT * FROM PUB.accounts')

insert into tbl_clients
SELECT * FROM OPENQUERY(myLinkedServerName,'SELECT * FROM PUB.clients')

End

CREATE VIEW accounts AS SELECT * FROM tbl_accounts
CREATE VIEW clients AS SELECT * FROM tbl_clients

after that create a job that runs the stored procedure once a day or depending on the schedule you need.