Problems migrate database from 4 to 5.5 mysql

This should be as simple as piping the output of mysqldump through a utility that can manipulate the data stream with a regular expression pattern match, like sed or perl.

mysqldump [options] | perl -pe 's/^\)\sTYPE=InnoDB/) ENGINE=InnoDB/' > dumpfile.sql

For any line that begins with ) TYPE=InnoDB this will replace the starting portion of the line with the new string ) ENGINE=InnoDB and leave the rest of the line intact.  It won't do anything to lines that don't match (they'll pass right through).

For quick and simple validation that this does what you want, use the --no-data option in mysqldump to dump only the table structures, not the data, and examine the resulting file.  If it looks right, you can repeat the process without --no-data.

A similar expression in sed should be this:

mysqldump [options] | sed 's/^) TYPE=InnoDB/) ENGINE=InnoDB/' > dumpfile.sql

...however, I'm not as conversant in sed as I am in perl, so I'd recommend the first approach, although I tested both versions and they appear to work identically.

Both perl and sed should be already present on any *nix system, and both are freely downloadable for Windows.  On Windows, you'll probably need " rather than ' surrounding the expressions.


Refactoring subquery to JOIN and CROSS APPLY, get only one row for each record in parent table

I'd probably structure this query as below

WITH ProductDetails
     AS (SELECT ProductSuperID,
                HasImage = MAX(CASE WHEN HasImage = 1 THEN 1 ELSE 0 END),
                StockBalance = Sum(StockBalance),
                HasPrice = CASE WHEN COUNT(*) = COUNT(Price) AND COUNT(*) = COUNT(DiscountPrice) THEN 1 ELSE 0 END
         FROM   Product
         GROUP  BY ProductSuperID)
SELECT p.ProductName,
       HasImage = ISNULL(pd.HasImage,0),
       pd.StockBalance,
       HasPrice = ISNULL(pd.HasPrice,0)
FROM   ProductSuper p
       LEFT JOIN ProductDetails pd
         ON p.ProductSuperID= pd.ProductSuperID;

As CROSS APPLY it could be written like this

SELECT ps.ProductName,
       pd.HasImage,
       pd.StockBalance,
       pd.HasPrice
FROM   ProductSuper ps
       CROSS APPLY (SELECT HasImage = MAX(CASE WHEN HasImage = 1 THEN 1 ELSE 0 END),
                           StockBalance = Sum(StockBalance),
                           HasPrice = CASE WHEN COUNT(*) = COUNT(Price) AND COUNT(*) = COUNT(DiscountPrice) THEN 1 ELSE 0 END
                    FROM   Product p
                    WHERE  p.ProductSuperID= ps.ProductSuperID) pd


Import Postgres 8.3 database in Postgres 10.5

According to the manual, you have to use pg_dump:

24.4.1. Migrating data via pg_dump
To dump data from one major version of PostgreSQL and reload it in another, you must use pg_dump; file system level backup methods will not work. (There are checks in place that prevent you from using a data directory with an incompatible version of PostgreSQL, so no great harm can be done by trying to start the wrong server version on a data directory.)

(emphasis mine, this is no different for version 10 than for version 9).
You might be able to get this trick to work; it's a simple one-liner but I've never used it myself.


Table not dropped correctly in SQL Server Express 2008

Whether using dbo or not, always specify the schema prefix explicitly. Otherwise, if you have users with different default schemas, you could end up with objects in a schema you didn't expect. One user creates a table in one schema, and the other can't see it. Here is a quick example:

CREATE SCHEMA floob AUTHORIZATION dbo;
GO

CREATE USER blat1
  WITHOUT LOGIN WITH DEFAULT_SCHEMA = floob;

CREATE USER blat2
  WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;

GRANT CREATE TABLE TO blat1, blat2;
GRANT ALTER ON SCHEMA::floob TO blat1, blat2;
GRANT ALTER ON SCHEMA::dbo TO blat1, blat2;

EXECUTE AS USER = 'blat1';
GO
CREATE TABLE Employees(ID INT);
GO
REVERT;

SELECT s.name, t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name = N'Employees';

EXECUTE AS USER = 'blat2';
GO
CREATE TABLE Employees(ID INT);
GO
REVERT;

SELECT s.name, t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name = N'Employees';

Results:

-----  ---------
floob  Employees

-----  ---------
dbo    Employees
floob  Employees


You've given 'user'  the readAnyDatabase permission.
This means it has read access for every database in the cluster.
Change the permission to readOnly so user will have read permission on admin DB only.


It looks like you don't have a covering index for your CTE query on the my_table_6 table, so the PostgreSQL engine is doing its best by scanning the closest index it can find.
Create an index on the my_table_6 table that covers id and saletime, and then recheck the EXPLAIN ANALYZE is showing an index seek operation instead of index scan.
Also why not try re-writing your query in a more relational way like this, to see if you get a better query plan:
WITH CTE AS
(
     SELECT Id,
            SaleTime
     FROM   tbl_sales_log
     WHERE  saletime > ’2019 -06 -01 00:00:00’
            AND saletime < ’2019 -06 -30 23:59:59’
            AND centerid IN (708, 805, 432, 403)
            AND itemid IN (3, 6)
     ORDER BY
            saletime DESC limit 1000 offset 0
)

SELECT Id,
       col1,
       col2,
       col3,
       col4,
       col5,
       col6,
       col7,
       col8,
       saletime
FROM my_table_6 R
INNER JOIN CTE C
    ON R.id = C.id
WHERE  R.saletime > ’2019 -06 -01 00:00:00’
       AND R.saletime < ’2019 -06 -30 23:59:59’
ORDER BY
       R.saletime DESC limit 1000;


Why is determining existence (or not) of rows matching certain criteria using a 'LIMIT 1' query on indexed columns examining more than 1 row?

LIMIT provides a window into the result set.  LIMIT 1 is equivalent to LIMIT 0, 1; return 1 row starting at offset 0.  Explain plan reports the estimated number of rows the LIMIT is running against.  However, only one match should be examined.

Testing on your SQL Fiddle shows the query runs against the INDEX (KEY) which should be the fastest access method.  I have found queries of these form are sometimes faster than queries returning a constant.

SELECT owner_id
FROM someTable s
WHERE reviewed = 0
AND owner_id = 0
LIMIT 1;

I would check the I/O on the device(s) storing your data.  I/O contention can cause issues on larger databases.

I would also consider a query in the form:

SELECT MIN(event_id)
FROM someTable s
WHERE reviewed = 0
AND owner_id = 0;

With an (owner_id, review, event_id) indexed.  This should run against the index and examine only one row of the index.


Error:Must declare the scalar variable "@SumSalary"

It showing error because you might running your SP and execute statement in same SSMS window. It will work if you run the below query in separate SSMS window and it will not show the error.
declare @SumSalary int
exec pro3 @deptid = 3, @SumSalary = @SumSalary out
OR
You just change the local Variable name as below and you can run the query in same window -
create procedure pro3(@deptid int, @SumSalary int out) as
begin
select @SumSalary = sum(e.salary)
from emp e
inner join dept d
on e.deptid = d.deptid
where d.deptid=@deptid;
end
declare @SumSalaryOutput int
exec pro3 @deptid = 3, @SumSalary = @SumSalaryOutput out


How DISCARD the tablespace on mariadb while table is not found?

After carefully checking again, I've found transaction_registry.ibd on the file system. So I manually deleted the file after stopping mariadb. Here you can see what I did:

sudo systemctl stop mariadb
sudo rm /var/lib/mysql/mysql/transaction_registry.ibd
sudo systemctl startb mariadb
sudo mysql_upgrade -u root --force -pxxxxx
...


Can't seem to understand why STUFF isn't working

The problem is that you are missing a way to tie the outer table to the data inside of the STUFF. You can do this by adding a WHERE clause to the subquery:

SELECT x.SurveyQuestionID,
  PossibleAnswers
    = STUFF((
              SELECT ', ' + Answer
              FROM SurveyQuestionPossibleAnswer sqpa
              INNER JOIN SurveyQuestion sq
                ON sq.SurveyQuestionID = sqpa.SurveyQuestionID
              WHERE x.SurveyQuestionID = sq.SurveyQuestionID
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM SurveyQuestionPossibleAnswer AS x
GROUP BY x.SurveyQuestionID;

See SQL Fiddle with Demo. Or to shorten the code, you could even rewrite the query to:

SELECT x.SurveyQuestionID,
  PossibleAnswers
    = STUFF((
              SELECT ', ' + Answer
              FROM SurveyQuestionPossibleAnswer sqpa
              WHERE x.SurveyQuestionID = sqpa.SurveyQuestionID
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM SurveyQuestion AS x
GROUP BY x.SurveyQuestionID;

See SQL Fiddle with Demo. Both will give you the same result.


There are a couple of ways you could do this

a. Geocode your postcodes and calculate the distance between the 2 points (or use an existing API to do this).  There are several places you can geocode small quantities of addresses for free, or you can purchase databases. Note that this method is not entirely trivial, but is probably the best for the long term.

You can get Geocoding done via Google at https://developers.google.com/maps/documentation/geocoding/

Then, to calculate the distance, use https://developers.google.com/maps/documentation/distancematrix/

b. Manually set which zip codes are bordering other zip codes in the database. You say you are supplying predefined zip codes so this may be a simpler solution (though for a large number of zip codes it would be costly)
The idea is that you have a new table zip_neighbours as follows

 Zip code,     neighbour
  100,       101, 102, 103
  101,       100, 102, 104
  102,       100, 101, 104

This wont give an accurate '75 miles' boundary, but can be used as a pretty good indication. When a user wants to include nearby zip codes you lookup the neighbours in this table and include them in the search.


Answers to your questions:

There are no problems, but the performance will be somewhat worse than using a sequence generated bigint primary key, because adding one to an integer number has to perform better than anything more complicated. Also, a uuid takes twice the storage space (16 bytes).
You should benchmark if that makes a noticeable difference for you.

Apart from the performance impact, it makes no difference.

It does not matter, they just return different constants.

The name parameter can be any string. That function is IMMUTABLE, so you have to make sure that you use different names if you want different UUIDs.

Use UUIDs if you need to generate artificial primary keys in a decentralized fashion, for example in the application code or in different database if you use sharding. If the primary keys are generated in a single database, use a sequence.


Increase memory limit when server is stopped

Open SQL Server Configuration Manager
In the SQL Server Services pane, locate the instance that has the problem
Right-click that instance and select Properties
On the Startup Parameters tab, type -f, click Add, then click Apply
Right-click that instance and select Start (or Restart)

This will start the instance in minimal configuration mode (single user, no CHECKPOINT, and no startup procedures are run), which should allow you enough resources to connect and fix what you broke.


I wouldn't think this is why your site is very slow.  You can change the alert levels by editing the following file:

vi /etc/munin/plugin-conf.d/mysql_innodb

In this file you should see:

[mysql_innodb]
env.warning 2147483648
env.critical 1073741824

If the tablespace falls below either of these levels a warning or critical is raised.  Change these limits to whatever values (in bytes) you need. If you wish to completely disable this monitoring set the values to 0:

[mysql_innodb]
env.warning 0
env.critical 0

Restart munin:

/etc/init.d/munin-node restart
su munin -c /usr/bin/munin-cron


Selecting the last record that meets a condition

Prior answer was wrong as pointed out in the comments -- this works:

SELECT id, val,
       CASE when val < 5 then (
            select max(val) from t as tsub where tsub.id < t.id)
       else val end as newval
FROM T

have to use a sub query to find the prior max.


Script out the constraints from the staging database and apply to the problem database. Lots of options, just make sure you have backups of staging and prod before you start:

Manually, one by one. Right click the objects in SSMS and opting to 'Script'.
Manually, all together (with a bit of editing). Right click database in SSMS and Tasks / Generate Scripts / Select 'Tables'. Under Scripting Options / Advanced include PK/FK. The  script created will include table definitions, you'll have to edit to leave just PK/FK.
Use a schema comparison tool to sync staging to production.
Use a script to generate the ALTER statements for you.

    SELECT
        *
        , 'ALTER TABLE ['+ FKTableSchema +'].[' + FKTableName + '] DROP CONSTRAINT '
            + ForeignKeyName AS FKDrop
        , 'ALTER TABLE ['+ FKTableSchema +'].[' +FKTableName + '] ADD CONSTRAINT '
            + ForeignKeyName + ' FOREIGN KEY(' +
          FKColumnList +') REFERENCES ['+ PKTableSchema+'].['
            + PKTableName + '] ('+PKColumnList +')' AS FKCreate
        , 'ALTER TABLE ['+ PKTableSchema +'].[' +PKTableName + '] DROP CONSTRAINT '
            + PrimaryKeyName AS PKDrop
        , 'ALTER TABLE ['+PKTableSchema +'].[' +PKTableName + '] ADD CONSTRAINT '
            + PrimaryKeyName + ' PRIMARY KEY('+PKColumnList+')' AS PKCreate
    FROM
        (
        SELECT DISTINCT
            FKTableSchema =
                (
                SELECT DISTINCT
                    table_schema
                FROM
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                WHERE
                    constraint_name=rc.constraint_Name
                ),
            FKTableName =
                (
                SELECT DISTINCT
                    table_Name
                FROM
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                WHERE
                    constraint_name=rc.constraint_Name
                    ),
            rc.constraint_name AS ForeignKeyName,
            FKColumnList =
                (
                SELECT
                    left(t.column_name,len(t.column_name)-1) AS 'ColumnList'
                FROM
                    (
                    SELECT Column_Name + ',' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    WHERE constraint_name=rc.constraint_name
                    FOR XML PATH('')
                    ) AS t(column_Name)
                ),
            cu.table_schema AS PKTableSchema,
            cu.table_name AS PKTableName,
            cu.constraint_Name AS PrimaryKeyName,
            PKColumnList =
                (
                SELECT
                    left(t.column_name,len(t.column_name)-1) AS 'ColumnList'
                FROM
                    (
                    SELECT
                        Column_Name + ','
                    FROM
                        INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    WHERE
                        constraint_name=cu.constraint_name
                    FOR XML PATH('')
                    ) AS t(column_Name)
                )
        FROM
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
        INNER JOIN
            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
        ON  rc.Unique_Constraint_name= cu.Constraint_name
    ) AS tab


By default, SQL Server does not track and record queries that causing growth of TempDB. Moreover, even running SQL Profiler session would not help in this case.

However, extended events is an option. Brent Ozar has a blog post with an example:

Tracking tempdb growth using Extended Events

XE sessions are more lightweight than SQL Profiler traces. Moreover, your servers, perhaps, already have two or three running now, by default.

XE sessions can be setup to target a ringbuffer, so they can be completely in memory and catch queries that exceed some threshold, for instance 20 GB or more. In this case, risk that they will cause any issues are minimal.


Back up Full Recovery Model Without Log Backups

When you take full database backup it will not take all the log file, and it seems you have some confusion about copyonly option.

If the issue you are facing is free desk space then you can refer to Is it possible to restore sql-server bak and shrink the log at the same time?
if your transaction log is growing too fast then you can start troubleshooting  and check this URLtoo

if the database is used for development environment then you can change it to simple mode this will truncate transaction log for you, but if you are on production site then do not do any changes to transaction log

see what will happen if you truncate logs


how to set "result to text" with a tsql statement in sp?

No, how the results are displayed (if at all) is the job of the client application.


Your question is very broad as there are multiple storage engines in MongoDB 3.0+ (and a storage engine API). Physical layout for data and indexes will depend on the storage engine implementation, but it sounds like your general question is how documents with varying schemas are stored and indexed.

Conceptually each document is stored in a contiguous binary record allocation which may include padding depending on the storage engine. The server-side document representation is in BSON (http://bsonspec.org) which is a binary JSON-like format with a richer type system than JSON. BSON records can vary in size; indexes (typically B-tree) point to the current location of an indexed record.

MongoDB (as at 3.2) does not maintain any schema information outside of the actual BSON data. Each BSON document includes all of the field names as well as the values. This dynamic schema (or "schema on read") approach allows documents to be interpreted as they are read from storage without consulting or updating a central schema catalog.

For more information, see:

MMAP Storage Engine Internals and Understanding MongoDB Storage (MMAP is the default storage engine through MongoDB 3.0)
A Technical Introduction to WiredTiger (optional storage engine in MongoDB 3.0, default in 3.2)


What's the best way to get data for sampling points within a timeframe

I would take answer by Micheael Green a step further and suggest generating a Numbers table. It will help many other algorythms as well. Another handy table is Calendar table with every date for +/- 20 years. You can get numbers from ID column of such table as well.

Here is a query I came up with. You can easily wrap it into SP to TVF. It works for me. I tested on my dev db so had to prefix table names with tmp.

Play with >, >=, <, <= sings to count or not those processes that start/finish on boundary.

---- Create temp tables to test
create table tmp_numbers  (
id int null
)
insert into tmp_numbers values (0)
go
insert into tmp_numbers
select MAX(ID)+1 from tmp_numbers
go 1000
create clustered index Idx_ID on tmp_numbers (id)
go

create table tmp_items (
    "Type" integer,
    TimeStampStart datetime null,
    TimeStampend datetime null
)
go

insert into tmp_items
select 1,'2012-03-03 02:13:01.000','2012-03-03 15:09:05.000'
UNION ALL
select 2,'2012-03-03 07:33:59.990','2012-03-03 14:59:10.000'
UNION ALL
select 3,'2012-03-03 22:13:01.000','2012-03-04 15:09:05.000'
UNION ALL
select 4,'2012-03-04 10:33:59.990','2012-03-04 14:59:10.000'
UNION ALL
select 5,'2012-03-04 23:20:00.000','2012-03-05 02:50:00.000'
UNION ALL
select 6,'2012-03-05 12:00:00.000','2012-03-05 23:01:00.000'

------ The query itself

declare @start datetime, @end datetime
set @start = '20120303'
set @end  = '20120305'

select
ID,
DATEADD(hour, id, @start),
DATEADD(hour, id+1, @start),
(select COUNT (*)
    from tmp_items
    where TimeStampStart <= DATEADD(hour, tmp_numbers.id +1, @start)
    and TimeStampEnd > DATEADD(hour, tmp_numbers.id, @start)

    -- To limit number of Log ("items") rows scanned:
    and TimeStampEnd >= @start

)
from tmp_numbers
where id < DATEDIFF (hh,@start, @end)+24;

Another way to organise this query to overcome single CPU limitatation

declare @start datetime, @end datetime
    set @start = '20120303'
    set @end  = '20120305'

create table #Temp (
StartTime datetime null,
EndTime datetime null,
RowNum int null)

insert into #Temp
  select
    DATEADD(hour, id, @start),
    DATEADD(hour, id+1, @start),
  from tmp_numbers
    where id < DATEDIFF (hh,@start, @end)+24;

 update #Temp
 set RowNum =  (select COUNT (*)
        from tmp_items
        where TimeStampStart <= #Temp.EndTime
        and TimeStampEnd > #Temp.StartTime

        -- To limit number of Log ("items") rows scanned:
        and TimeStampEnd >= @start
    )


CREATE TABLE query with SELECT having NOT NULL fields

I don't see how MySQL could know anything about the content of your columns, unless you were basing the CREATE on another table ("LIKE ...").

It couldn't know anything about primary keys, default values, foreign keys, etc, right?

Looks like you're stuck doing an ALTER TABLE afterwards.


You could open it in Excel then write a concatenate function that builds an insert statement, then copy it down the rows.

But if you really just want to get the data into a table, you could load it using the 'SQL Server Import and Export Wizard', or build an SSIS job to do it.


Increased size of database dump even though LESS consumed storage

While I don't have a detailed explanation myself, there's the following hint in the docs:

For the custom archive format, this specifies compression of individual table-data segments[...]

I've changed the layout of my tables entirely and most likely have changed the "individual table-data segments" as well. So I did some further testing and got the following numbers. It's always OLD vs. NEW schema for the schema documented earlier:
--compress=0: 25  vs. 20  GiB
--compress=?: 5,6 vs. 6,5 GiB
--compress=9: 5,5 vs. 6,4 GiB

? means default settings, meaning --compress has been REMOVED from the shell command.
As can be seen, pretty much as soon as compression comes into play, the new table layout is less efficient, even though in OLD and NEW schema exactly the same bytea were used, have been placed at the end of their individual tables and alike. But things become more interesting with another version of my schema, which as well indicates a strong relationship of dump size, compression and table layout/size.
I've changed the new table datagram to be a partitioned one with partitions containing rows per year and per half-year. Each partition contains far less rows than before this way and while I only tested with --compress=9 this time, the numbers are quite interesting:
unpartitioned:     6,4 GiB
half-yearly parts: 4,8 GiB
yearly parts:      4,8 GiB

The interesting part this time is that the table layout for all partitions is the same like before, only the number of rows per table is different. Though, the number of rows overall is the same like before, the same data, IDs etc. Though, this time the dump really is smaller than with the OLD schema containing far more data because of duplicate IDs and stuff.
I wouldn't have expected table layout to be that important.


Automating DB Restore from network share

It appears that these backups are being made using Ola Hallengren's Maintenance Solution so I'm going to write this based upon that assumption.

The dbatools PowerShell module has a function which does exactly what you need - Restore-DbaDatabase. For a basic restore:

Restore-DbaDatabase -server DESTINATIONSERVER -path \\BackupServer\Path\To\Backups -MaintenanceSolutionBackup

There are additional parameters you can specify to put the files in a location other than the instance's default paths, change the name of the database or the files, or even a point in time earlier than the last backup (but this point in time must be reachable from the files given).

The account under which your destination SQL Server instance needs read access to the UNC path where your backups are stored. If that's not possible, you'll need to copy that directory structure to somewhere that is accessible or map a drive using alternate credentials.

To do that, you can use New-PSDrive to map a drive using alternate credentials (you'll want to save those credentials securely if you're doing this from a script that runs via Task Scheduler).

New-PSDrive -Name R -Root \\BackupServer\Path\To\Backups;
Copy-Item -path r:\*.* -destination \\Path\That\Destination\Instance\Can\Read
Restore-DbaDatabase -server DESTINATIONSERVER -path \\Path\That\Destination\Instance\Can\Read;
Remove-PSDrive -Name R;


Should fields that aren't needed often be kept in separate tables?

I sympathize with this situation because of the SELECT * FROM statements from ORMs. General MySQL (and probably other RDBMs) index theory is to only select the columns that you need, preferably entirely from an index.

That being said, the answer depends on how much traffic you are expecting to get to see a noticeable degradation of performance. It also depends on how much usage you'll get out of the category descriptions.

I wouldn't think storing the descriptions in the same table would reduce performance enough to warrant 2x calls to the database when you DO need to grab the descriptions.

To sum it up, I think you've got a bigger performance problem in using an ORM than in storing the descriptions in the same tag table.


SSRS 2014 Exec Log gives the Service Account as UserName instead of Actual User name

You may be seeing a Subscription execution. You can spot-check this by...

In the UI by going to the report listed on the ReportPath and going to Manage > Subscriptions
In the DB by checking the dbo.Subscriptions table (see some helpful queries at this SO post)


Query Conflicts in Streaming Replication in Postgres 9.4

They are stored in the replica's pg_xlog directory.


How will the order of operations in this query affect my results?

Your assumptions are almost correct.
The order of events:

Build table from FROM list

Apply join conditions and filters in WHERE clause.

GROUP BY / aggregate functions
Window functions
ORDER BY
DISTINCT ON (has to match leading columns of ORDER BY)
LIMIT (not in your case).

Window functions operate logically independent of the ORDER BY clause. But there are synergies for performance if both use the same sort order.

There is not enough information to tell whether this query does what it is supposed to do.

Related answers to shed some more light on the topic:

How do I get the aggregate of a window function in Postgres?
PostgreSQL: running count of rows for a query 'by minute'


PostgreSQL - SSL connection without providing the root-ca certificate?

In the absence of PGSSLROOTCERT or sslrootcert, the library libpq (which psql uses) looks in a compiled-in default location for the file.  And apparently, it finds it at that location.

For Linux, that location is ~/.postgresql/root.crt.  Although it is possible that "Debian 10.3-1.pgdg90+1" patched it to look someplace else instead.


How do loop through a dynamic SQL statement using a cursor?

The problem is that your dynamic query never produces a null. When the condition in the dynamic query's WHERE clause is met, the output parameter is assigned a Y. But when the condition is not met, nothing happens, and the @res parameter keeps the value assigned at the sp_executesql line – consequently, the @result variable keeps its previous value too.

In order to produce a null, you need to change the structure of the dynamic query. Instead of this:

SELECT @var = … WHERE …

make it this:

SET @var = (SELECT … WHERE …)

That way, when the condition is not met, the SELECT will return nothing, which will be interpreted as a scalar null and assigned to the @var accordingly. You can then turn your null into N or anything else as necessary. You can, of course, do that immediately in the dynamic query if that is the only way you want to treat those nulls:

SET @res = ISNULL((SELECT 'Y' WHERE …), 'N');


Conditional WHERE Clause in a Table Valued Function

Like so :

CREATE FUNCTION [dbo].[DateRange]
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25) = NULL,
        @Device varchar(25) = NULL
    )
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT *
        FROM MyTable
        WHERE
          Date < @EndDate
          AND Date > @StartDate
          AND ( @Location IS NULL OR (@Location IS NOT NULL AND Location = @Location))
          AND ( @Device IS NULL OR (@Device IS NOT NULL AND Device = @Device ))

    )

IF the device and location columns can actually be NULL, you might want to consider this option though:

CREATE FUNCTION [dbo].[DateRange]
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25) = NULL,
        @Device varchar(25) = NULL
    )
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT *
        FROM MyTable
        WHERE
          Date < @EndDate
          AND Date > @StartDate
          AND ( @Location IS NULL OR Location = @Location)
          AND ( @Device IS NULL OR Device = @Device )

    )

This would work if you're using a UD table type for the location, as you asked about in your comment. Just make sure the input parameter is Read Only.
However, you cannot pass NULL into a table type parameter, you have to pass in an empty parameter object of that type :

CREATE TYPE LocationsType AS TABLE
( LocationName VARCHAR(25));

CREATE TABLE MyTable (Date datetime, Location varchar(25), Device varchar(25));

CREATE FUNCTION [dbo].[DateRange]
    (
        @StartDate date,
        @EndDate date,
        @Location LocationsType READONLY,
        @Device varchar(25) = NULL
    )
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT *
        FROM MyTable
        WHERE
          Date < @EndDate
          AND Date > @StartDate
          AND ( ((select count(*) FROM @Location) = 0) OR Location IN (select LocationName from @Location))
          AND ( @Device IS NULL OR Device = @Device )

    )

Call it:

declare @l LocationsType

select *
from [dbo].[DateRange] (getDate(), GetDate(), @l,NULL)


sqlplus (Oracle XE) doesn't start on CentOS 7

This version of Oracle XE is based on 11g2 edition, which is not compatible with CentOS 7.

In fact, it isn't even 100% compatible with CentOS 6, however it will run there without problems, after you install a number of RPM packages not installed/required by Oracle installer (especially libaio both 32/64-bit).

These are formal OS requirements for XE 11g2:

Oracle Linux, Red Hat Enterprise Linux, or CentOS - versions 4.7+ or 5.2+
SUSE Linux Enterprise Server - versions 10 SP2 or 11


Create view with column name aliases or hard change column names on a big database with a very low amount of changes?

Here would be two recommendation based on working in environments with lots of databases being used by lots of different apps.

Option 1:
Create a view with a similar name but reflecting the changes you want made. In your case, it's just one field renamed. You may have to create triggers on the view so the apps that perform DML can use it just like they use the original table.

Publicize the view name and encourage the app developers to change their code to use the view instead of the table. You want the only change needed to be the name of the table and any changes propagated through the view (the new name of the field). They can make the changes more or less "at their leisure" as the original table still works as before. You will, of course, give them a deadline to get this finished.

When all app changes have been made (and verified), you can really consider yourself finished. I encourage a system of exposing only views to apps instead of entity tables. This allows for much more freedom to make necessary changes to the tables and change views so everything looks the same to apps.

But if you want to, rename the fields in the tables and, after extensive testing of course, notify app developers to change the "table" names back to original.

Test and finished. Delete the views or keep them around if you prefer.

Option 2:
An alternative would be to rename the tables and create the views with the original table names. Then all apps would need to be changed at once to use the new field name(s). Thoroughly test after bringing the system back online.

Next, change the tables as needed and test. Then reverse the process -- rename (or drop) the views and rename the altered tables back to the original names. Test and (hopefully) finished.


MySQL SUM with multiple GROUP BY on same table

I'm more of a SQL Server guy, but here is a stab to get you motivated. You could use the CASE expression to help SUM() orders by certain order types.

For example:

SELECT
    idclient AS [Client ID]
   ,COUNT(value) AS [Total Orders]
   ,SUM(value) AS [Total Order Value]
   ,SUM( CASE WHEN type1 = 'some type' THEN value ELSE 0 END) AS [Order Combo 1]
   ,SUM( CASE WHEN type1 = 'some type' AND type2 = 'another type' THEN value ELSE 0 END) [Order Combo 2]
   ,SUM( CASE WHEN type1 = 'some type' AND type2 = 'another type' AND type3 = 'another type' THEN value ELSE 0 END) [Order Combo 3]
FROM 'test'.'orders'
GROUP BY idclient

This will group the orders on [idclient] and will SUM([value]) the instances where certain order records meet the following conditions or combinations you specified.

So, if a customer has an order where [type1] was Apples and [type2] was Oranges, you can SUM([value]) that was 1 under a new column called something like [Apple/Orange Orders].

The result can also include additional combinations for the same record in multiple fields/columns. If that is a problem, just move the CASE to the GROUP BY to break each combination into a separate record in the the result set.

Hope this helps!


You can schedule below SQL to run daily with a SQL Agent job or if you have SSRS you can easily schedule and format the report. It will give you most of the information from the standard reports.

select ls.primary_server,ls.primary_database,lsd.restore_delay,
DATEDIFF(mi,lms.last_restored_date,getdate()) as time_since_last_restore,
lms.last_copied_date,lms.last_restored_date,lms.last_copied_file,
lms.last_restored_file,
lsd.disconnect_users,ls.backup_source_directory,
ls.backup_destination_directory,ls.monitor_server

from msdb.dbo.log_shipping_secondary ls
join msdb.dbo.log_shipping_secondary_databases lsd
  on lsd.secondary_id=ls.secondary_id
join msdb.dbo.log_shipping_monitor_secondary lms
  on lms.secondary_id=lsd.secondary_id

For HTML email status report use this answer:  Here's how can you create the HTML body part of your mail


Archiving tables with Access frontend and linked SQL Server

Try creating a separate historical file with nothing but the archived data table. In the example below you can see there is a linked table. I imagine your linked SQL table appears similar but pictured as a blue globe and is connected. Right click the linked table and select Convert to Local Table. This will sever the link and create a local table. Name it [tblYourTable_2016] for example.

OR

You could create a Make Table Query and recreate it that way as well. Either way I would keep the history files in a separate and secure file.

[]


Index for VARCHAR column has low efficiency

Would you try this and let me know if this improves your performance:

SELECT links.location_id FROM links
WHERE NOT Exists(
select location_id from locations);


Write update in more efficient way with joins

You can use an INNER JOIN on CurrencyID to join table1 back on itself but filter the table being updated to records where accountID = @recipientID and the joined table is filtered where accountID = @payerID

In this fashion, only record meeting the WHERE clause for both tables that also matches CurrencyID values will be included in the UPDATE.

UPDATE t1
SET t1.balance = t1.balance + @amount
FROM table1 t1
INNER JOIN table1 t2 ON t1.CurrencyID = t2.CurrencyID
WHERE t1.accountID = @recipientID
    AND t2.accountID = @payerID


Why can't I get a lock on an InnoDB table?

According to the MySQL Documentation on MetaData Locking

  If the server acquires metadata locks for a statement that is
  syntactically valid but fails during execution, it does not release
  the locks early. Lock release is still deferred to the end of the
  transaction because the failed statement is written to the binary log
  and the locks protect log consistency.

Somone submitted this state as a bug, but the bug report claims it is not a bug.

Both of these links describe DDL (Data Definition Language) against a table as a contributing factor. What is interesting to keep in mind is that doing something like TRUNCATE TABLE is, in reality, DDL. We have to aware of any SQL we run. We may think it is SQL that is part of a transaction when, in fact, it is DDL. TRUNCATE TABLE is an example of DDL we think is DML. This can cause current transactions to commit and may have unpredictable results when it comes to writing binary log events as the first link states:

  Before MySQL 5.5.3, when a transaction acquired a metadata lock for a
  table used within a statement, it released the lock at the end of the
  statement. This approach had the disadvantage that if a DDL statement
  occurred for a table that was being used by another session in an
  active transaction, statements could be written to the binary log in
  the wrong order.


Difference between database backup and data-tier application export

A backup is a full copy of all the database pages, or a dump of the database if you like, starting at page 0 and written sequentially to the backup device.

The Data-Tier application is a set of scripts that will create the database objects along with insert statements that will insert the data into the table objects afterwards.

From a backup perspective the Data-Tier application is a snapshot of the database in a certain state, you can install the application in that state but you can not restore additional data but have to copy the data from another source. The backup is a consistent state of the database and you can make log or differential backups that you can use to restore the database to a point in time.


Why am I getting ERROR: column "GLOBE-BE-154" does not exist

As documented in the manual string constants need to be enclosed in single quotes in SQL
INSERT INTO stunden_Plan (stunde, montag, dienstag, mittwoch, donnerstag, freitag)
VALUES (2, 'GLOBE-BE-154', 'CH-SCH-156', 'D-KR-477', 'M-HA-477', 'E-ZE-477' );

Double quotes are for identifiers


I got this error when am trying view dba_tables Ora 03113 end of file on communication channel

Error  ORA 03113 end of file on communication  means: The connection between Client and Server process was broken.

Possible causes and general troubleshooting:

Network problems
Oracle internal errors / aborts on the server
Client incorrectly handling multiple connections
check SQL*Net setup
look in the alert.log file for any errors


BCP via Linux Centos 7, cannot connect to Azure SQL

For Azure SQL Database, include the database name on the command line:

bcp schema.table_name in "path to csv file" -S example.database.windows.net -U user_name -P "password" -d schema -c -t ',' -d YourDatabase

EDIT:

If your password contains characters which must be escaped (e.g. ';' but not '%'), you need to enclose the password string in braces:

bcp schema.table_name in "path to csv file" -S example.database.windows.net -U user_name -P "{pass;word}" -d schema -c -t ',' -d YourDatabase


Changing Database Transaction isolation level to SNAPSHOT for SQL Server replication "distribution" database

With 1 billion changes, I would be worried about the size of TempDB when the row version is added to everything. Hilary Cotter responded to this same question here - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2edbdc37-c2ed-4c84-ae72-16f89404172b/sql-server-replication-and-enabling-readcommittedsnapshot-on-the-distributor?forum=sqlreplication.

He is definitely the man you want to ask replication related questions to.


You clearly stated you goal in the question
The goal is to significantly improve the performance of searches on the MyISAM table
OK let's tune the MyISAM side of things.
I have four(4) suggestions for you
SUGGESTION #1 : Reduce your RAM
First of all, look back 5 years ago and note what I said in my answer to the post What are the main differences between InnoDB and MyISAM?: The MyISAM storage engine caches only the index pages from the .MYI of a MyISAM table.
You set key_buffer_size to 2147483648, that 2G. Yet, your MyISAM indexes only total 718M. You need to reduce key_buffer_size to 768M just to free up 1.2G of RAM.
SUGGESTION #2 : Load All MyISAM indexes into RAM
Many are not aware of this, but you can load one or more indexes from a MyISAM table into RAM. It's called a dedicated keycache. Over 3.5 years ago, I wrote about this in the answer to the post Can I partially invalidate the MySQL table cache? (See SUGGESTION #1). It is also in the MySQL Documentation : See Cache Index Syntax
SUGGESTION #3 : Expand all VARCHARs into CHARs
If the MyISAM table has many VARCHAR fields, you could make VARCHAR fields behave like a CHAR with one DDL command
ALTER TABLE myisamtable ROW_FORMAT=FIXED;

This will expand the table is such a way that a the data type of a VARCHAR(32) is actually 32 bytes wide all the time, just like a CHAR(32). This can potentially double or triple the size of the MyISAM table but you can gain a 20-25% increase in read speed.
I wrote about this many times

Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes
Aug 12, 2011: Which DBMS is good for super-fast reads and a simple data structure?
Sep 20, 2011 : Best of MyISAM and InnoDB
May 02, 2012 : Which mysql storage engine to choose?
May 03, 2012 : Which is faster, InnoDB or MyISAM?

If you do this, you will need to go back and measure how big the MyISAM index has grown. Then, redo SUGGESTION #2 and create the MyISAM dedicated keycache to that new size.
SUGGESTION #4
Although MyISAM does not have a global cache of MyISAM data, each DB Connection has a local cache size by read_buffer_size (for sequential reads) and read_rnd_buffer_size (for indexed reads).
You have read_buffer_size set at 256K (262144). You did not set read_rnd_buffer_size at all, so it is set to the default size of 256K (262144). You may want to experiment with caching data but be very careful because those two MyISAM data caches are for each DB Connection.
GIVE IT A TRY !!!


How to delete constraint inside a trigger function

I think you want to set your foreign key DEFERRABLE INITIALLY DEFERRED so that you don't have to drop and recreate it every time there is an INSERT or other data change.  Maybe even this is not needed, but it depends on what happens in the rest of the transaction.

Then the rest is just

IF tg_op = 'INSERT
THEN
    INSERT INTO points (gid, fookey)
    VALUES (0, NEW.fookey);

    UPDATE points
    SET fookey = NEW.fookey
    WHERE ST_Within(points.geom, NEW.geom);
ELSIF ...

Notes:

you cannot COMMIT from inside a function
the DB is either PostgreSQL or Postgres, not Postgre.
logically, this is more an AFTER trigger than a BEFORE one.


How do I get the Query Timing with sqlcmd?

I think that what you're looking for are the SET STATISTICS commands:

SET STATISTICS TIME
SET STATISTICS IO

They work for me using sqlcmd on windows:


Is it possible to exclude rows from a select based on conditions of another row

Given your requirements, this should work for you, I think.  If you have another table with just the vouchers in it, that could help it along, as well as an index on the VoucherNo column.

DECLARE @Sample TABLE
    (
    Ref VARCHAR(10) NOT NULL
    , Amount DECIMAL(10,2) NOT NULL
    , VoucherNo INT NOT NULL
    )

INSERT INTO @Sample
(Ref, Amount, VoucherNo)
VALUES ('M019922', -154.80, 100081968)
    , ('M019922A', 154.80, 100081968)
    , ('M019922B', -154.80, 100081991)

;WITH CTE_Voucher AS
    (
    SELECT S.VoucherNo
        , SUM(COALESCE(S.Amount, CONVERT(DECIMAL(10,2), 0.0))) AS TotalAmount
    FROM @Sample AS S
    GROUP BY S.VoucherNo
    HAVING SUM(COALESCE(S.Amount, CONVERT(DECIMAL(10,2), 0.0))) <> CONVERT(DECIMAL(10,2), 0.0)
    )
SELECT C.VoucherNo
    , S.Ref
    , S.Amount
FROM CTE_Voucher AS C
    INNER JOIN @Sample AS S ON S.VoucherNo = C.VoucherNo


Full Text Search Term Casing Affects Results

This is documented although I couldn't find a reference in Books Online:

  The rules for characters followed by nonalphanumeric characters are
  somewhat convoluted (at least in English). The English word breaker
  accepts the token C# and returns C#. The lowercase token c#, however,
  is indexed as c with the # character stripped off. The uppercase token
  C++ and lowercase token c++, on the other hand, are both indexed as
  c++.

"Pro Full-Text Search in SQL Server 2008" - Michael Coles, Hilary Cotter, p106

You can check the behaviour of your search phrase using the sys.dm_fts_parser DMV, eg

SELECT * FROM sys.dm_fts_parser ( 'microsoft AND c#', 1033, 0, 0 )
SELECT * FROM sys.dm_fts_parser ( 'microsoft AND C#', 1033, 0, 0 )

As a workaround, you could force all strings through as upper-case, use some kind of mapping table for common search terms, or design a strategy to ensure non-alphanumeric characters are never stored or queried as part of the full-text index.

Note the behaviour is more consistent in SQL 2012 (where both lower and upper case will return only 'Microsoft C#').


Join JSONB column to normal PK column in Postgres

You can use the following query:

SELECT
    *
FROM
    res
    JOIN res_rem_sent ON res_rem_sent.body->>'resId' = text(res.id) ;

JSON represents all of its contents as text, and that's the reason you have to be very careful on how the UUIDs are converted to text by PostgreSQL.

Things to take into consideration:

The ->> operator gets a JSON object field as text.
On the JSON(B) side, you need to represent the UUIDs using the standard form (as explained in UUID Type), so that conversions between UUID type to the JSON(B) text representation produces the same results.
If you want the query to be performant, you need to have at least a (functional) index on the body->>'resId' part

CREATE INDEX idx_uuid_res_rem_sent ON res_rem_sent((body->>'resId')) ;
 -- NOTE: the double parenthesis aren't optional
You get the maximum performance if you also have a text index on Res.id:

CREATE INDEX idx_uuid_res ON Res(text(id)) ;
Performance could possibly be improved if, instead of using the text representations, comparisons and indexes are done using UUID representations:

(res_rem_sent.body->>'resId')::uuid = res.id
In this case, if the conversion fails, you will not be able to insert the data into the table.

DBfiddle here to see a practical simulation of this scenario


Select count date from multiple where conditions for each day

SELECT count(id)                                       as number_of_coupons,
       DATE_FORMAT(assigned_date, '%d-%m-%Y')          as date,
       SUM(CASE WHEN campaign_id=1 THEN 1 ELSE 0 END)  as campaign_1,
       SUM(CASE WHEN campaign_id=2 THEN 1 ELSE 0 END)  as campaign_2
FROM coupon
GROUP BY DATE_FORMAT(assigned_date, '%d-%m-%Y')
order by STR_TO_DATE(date, '%d-%m-%Y') DESC


Why does dbms_scheduler max_run_duration not raise event JOB_OVER_MAX_DUR?

When adding multiple attributes, try:

DBMS_SCHEDULER.set_attribute(
    name      => 'first_job',
    attribute => 'raise_events',
    value     => DBMS_SCHEDULER.job_succeeded + DBMS_SCHEDULER.some_attribute);

The valid values for event types (11gr2) is here.  And according to docs, job_over_max_dur is enabled already for 11gr2.

Now, for 10g or 11gr1, I don't see job_over_max_dur in the list of event types raised by the scheduler.  But you mentioned in earlier post that you will very soon migrate to 11gr2, so I assume you have a test box setup already that you can test this with 11gr2.  If you really want to go down the rabbit hole with AQ, you can do more custom events, but it would probably be much easier to wait a bit until you're on 11gr2.

Edit

To add more info, first some basic scheduler stuff you can view to see whats running (or scheduled, or stalled, or whatever state):

select * from dba_scheduler_jobs
where owner = 'SOME_OWNER'
and job_name = 'SOME_JOB';

You can see if max_run_duration column is set and also the "raise_events" column will show a list of event types that are to be raised by each job.  You should see "JOB_OVER_MAX_DUR" type here.

If you want to test if your db environment is handling the JOB_OVER_MAX_DUR properly, then you can setup a quick test:

BEGIN
    -- setup simple scheduler job
   --DBMS_SCHEDULER.DROP_JOB('EVENT_RAISING_JOB',false);
   DBMS_SCHEDULER.create_job (
      job_name        => 'EVENT_RAISING_JOB',
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'BEGIN
                            dbms_lock.sleep(70);
                          END;',
      start_date      => SYSTIMESTAMP,
      end_date        => SYSTIMESTAMP + 2, -- 2 day
      repeat_interval => 'freq=daily; byhour=1',
      enabled         => TRUE);

    DBMS_SCHEDULER.set_attribute(name=>'event_raising_job', attribute=>'max_run_duration', value=>interval '60' second);

   DBMS_SCHEDULER.set_attribute(name => 'event_raising_job', attribute => 'raise_events',value => DBMS_SCHEDULER.job_all_events );

END;

-- setup email notification
exec dbms_scheduler.add_job_email_notification( job_name=>'EVENT_RAISING_JOB',recipients=>'someuser@somedomain.com', events=>'job_all_events');

-- launch job
exec dbms_scheduler.run_job('EVENT_RAISING_JOB',false);

You should get 3 emails that this job: STARTED, SUCCEEDED, and JOB_OVER_MAX_DUR


Keep track of time intensive stored procedure with prints

In SQL Server (and I think this should work in the ancient version you say you're using), the normal method for this is to use RAISERROR. PRINT won't display any output until the process finishes which is not very useful. I use this technique a lot in batch scripts for say updates and deletes where I want to keep the batch size small.

In the example below, I set a @Batchsize variable and my loop just works on that many rows at a time, then I output that with a RAISERROR. It's important to use a severity of 0, which means nothing will treat it as an error, just as an informational message.

DECLARE @MsgStr varchar(200) = 'Inserted ' + CAST(@BatchSize AS varchar(10)) + ' rows...'

RAISERROR(@MsgStr, 0, 0) WITH NOWAIT


Can I execute a prepared statement inside a MySQL SELECT?

From the question, it looks like what you have written quasi-resembles what Oracle calls an anonymous code block. MySQL does not facilitate such a mechanism.

With regard to what you want to accomplish, you do not need a stored procedure.

Try assembling the SQL statement like this:

@stmt = 'SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs FROM buyerList AS b ';
@stmt = CONCAT(@stmt,'LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass ');
@stmt = CONCAT(@stmt,'LEFT JOIN pricelists p ON ');
SELECT GROUP_CONCAT(CONCAT('(p.sid = a.sid AND p.preisliste = ',sid,' AND p.ean = a.ean AND p.iln = ',pricelist',)') SEPARATOR ' OR ')
INTO @LeftJoinClause FROM preislisten;
@stmt = CONCAT(@stmt,@LeftJoinClause,' WHERE b.bid = ?');
SELECT @stmt\G

This will print out the desired query

If it is the desired query, execute it

PREPARE sql FROM @stmt;
EXECUTE sql using @param_iln;
DEALLOCATE PREPARE sql;

Give it a Try !!!

If you actually want the query to be small without hardcoding every value just write the code with a more straightforward LEFT JOIN setup

SELECT p.sid, ifnull(p.pricelist,"BASE"), count(*) AS recs FROM buyerList AS b
LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
LEFT JOIN pricelists p ON
    (p.sid = a.sid AND p.preisliste = a.sid AND p.ean = a.ean)
WHERE b.bid = ?
GROUP BY p.sid,p.pricelist;

I also just noticed you have a.sid and a.ean, what table has an alias of a ?


How to take backup of multiple DB in a specific point of time

You can try using the --snapshot method (https://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-pg-dump-snapshots/), but it might be a bit more complicated than you'd like.

My personal recommendation is to create a cluster-wide backup with pg_basebackup (or a third-party app that basically does the same thing, like barman, pg_backrest, etc.)


Is database partitioning only created to get a better performance? and why should one partition a table, and a database?

I am not sure if I would be able to put this in a comment, hence making it an answer:

Partitioning may not serve the mere goal of performance, because querying the partitioned tables and data comes more into picture when we talk performance. Therefore one needs to have a better understanding of writing efficient queries on partitioned tables.

Partitioning is good for very large databases, for maintaining that big amount of data within the database efficiently so that you can handle its growth properly. What I get from partitioning is saving some space on the data drives, which might be filling with unwanted data preserved, depending upon RTO or RPO. Good for saving some space for sure.

I don't think database partitioning and tables are the same. I have never implemented database partitioning, but yes you can use it for large tables. Also check on data and/or page compression when using partitioning.

With big databases, like 30 TB, you may consider other performance checks and good practices apart from partitioning as well, like infra build, etc.

This might help: https://www.brentozar.com/guide-to-evaluating-table-partitioning/


Remove characters from recordset permanently

Is there a reason this simple update doesn't work for you?

update part set partname = replace(partname,'functional-','')

select * from part


How to select specific items from table A which appear with all items of some column in table B?

I dont think there is any logical constructs like in/not in.

But you can check array equality like below,

select id from
(select array_agg(type)as type, id from t1  group by id) table_1
join
(select array_agg(type)as type from t2)table_2
on table_1.type=table_2.type;

May be this is useful to you :)


How do I track why my SQL Azure occupied space pulsates?

I'd have you look at tables and indexes with the following code:

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as [SizeinMB]
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name
ORDER BY [SizeinMB] DESC

You will want to check the size of the objects at regular intervals to see if you can determine which ones are in flux.


PGAdmin 4 - server status / view log file

I have followed the guide here to use a foreign data wrapper so I can access the log file through SQL.

First, create the extension:

create extension file_fdw;

Next, set up postgres.conf to support csv logging. Essentially, these settings:

  log_destination='csvlog'

  logging_collector='on'

  log_filename='postgresql.log'

And also the following, from the documentation:

  Set log_filename and log_rotation_age to provide a consistent,
  predictable naming scheme for your log files. This lets you predict
  what the file name will be and know when an individual log file is
  complete and therefore ready to be imported.

  Set log_rotation_size to 0 to disable size-based log rotation, as it
  makes the log file name difficult to predict.

  Set log_truncate_on_rotation to on so that old log data isn't mixed
  with the new in the same file.

Create, the server and the table, and the log can be queried!

create server logserver FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE postgres_log
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text)
SERVER logserver OPTIONS (filename 'pg_log/postgresql.csv', format 'csv');

select message from postgres_log;


Multiple plans (with/without missing index) in query store for same query

I'll Start with the second question

The reason for the 'missing index' advice for Plan 2 is the subtree cost calculated
The parameters passed results in a subtree cost (right click/hoover on the nested loop join) that is 10 times higher than that for Plan 1, and it is because the estimated number of rows is 149, instead of 16.
What is suggested as missing is a covering index, so that it doesn't have to make 149 keylookups to get the missing values from the index.
For Plan 1 it was considered ok to make 16 keylookups

What you are experiencing is what Erland Sommaskog is calling a “morally equivalent plan”

Kendra Litttle explains it in this article https://sqlworkbooks.com/2018/03/what-is-a-morally-equivalent-execution-plan-and-why-is-it-good/
Using the feature of Query store to force a plan, but i believe it is the same when using KEEPFIXED PLAN

As to why several plans for the same query exists.
Many reasons including restart, cache getting cleared due to memory pressure, changing the structure or schema of a table referenced by the query.

This will cause the plan to be recompiled

Normally also updating the statistics, but since you have KEEPFIXED PLAN that is not he case.

Having KEEPFIXED PLAN can cause performance degregation since as your data changes, the statistics change and the optimal plan may be different, but KEEPFIXED PLAN is forcing the same plan all the time.

i am guessing, but is the setting

OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)

used on all queries ?
then the DBA will have to monitor if optimal plans are used (if data distribution have changed much since the plan was created)

An extra comment. Having both KEEP PLAN and KEEPFIXED PLAN in the hints is not needed. KEEPFIXED PLAN is overruling KEEP PLAN.
KEEP PLAN, disables the “6 rows” rule, as far as statistics invalidation is concerned. KEEPFIXED PLAN disables recompilations due to statistics changes completely.


Image this :

select one_id,count(id) as num_Twos
from Two
group by one_id

with the output :

one_id      num_Twos
1           2
2           1

And then join to the main table (this way, the main records, from table One, are not duplicated,and no need of distinct ):

select
    id, num_Twos, isnull(num_Twos,0) as numTwos
from One
    left join
    (
        select one_id,count(id) as num_Twos
        from Two
        group by one_id
    )as two
        ON id =two.one_id

output: (used isnull - to put 0 instead of NULL)

id          num_Twos    numTwos
1           2           2
2           1           1
3           NULL        0
4           NULL        0

the same thing , with table 3
From comments: this way, you don't need the top level group by , because the all the information are already aggregated in the () as Two and () as Three

or with distinct:

select one.id
        , count(distinct two.id) as num_twos
        , count(distinct three.id) as num_threes
from one
left join two on one.id = two.one_id
left join three on one.id = three.one_id
group by one.id

output :

id          num_twos    num_threes
1           2           1
2           1           4
3           0           0
4           0           0

the source:

with One
as (select 1 id union all select 2 union all select 3 union all select 4)
,Two
as (select 1 as id, 1 as one_id Union all select 2,1 union all select 3,2)
,Three
as (select 1 as id,1 as one_id union all select 2,2 union all select 3,2 union all select 4,2 union all select 5,2)

also , you can rewrite it as :

select id
        , isnull(num_twos,0) as num_twos
        , isnull(num_threes,0) as num_threes
from one
left join (select one_id ,count(id) as num_twos from two group by one_id) as two on one.id = two.one_id
left join (select one_id ,count(id) as num_threes from three group by one_id) as three on one.id = three.one_id


Need to join two similar queries that represent different years

As you note, you need to do a full outer join between the two queries.

Let's assume your first query (for the user-specified year) is:

SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as CurSales
  FROM SalesTable
 WHERE <User-specified criteria>
   AND Year = <user-specified year>

Then, the combined query should look like this:

SELECT COALESCE(cy.Salesman, py.Salesman) as Salesman
      ,COALESCE(cy.Customer, py.Customer) as Customer
      ,COALESCE(cy.Product_Code, py.Product_Code) as Product_Code
      ,COALESCE(cy.CurSales,0) as Sales
      ,COALESCE(py.PYSales,0) as PY_Sales
  FROM (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as CurSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year>
       ) as cy
       FULL OUTER JOIN
       (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as PYSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year> - 1
       ) as py
       ON (    cy.Salesman = py.Salesman
           AND cy.Customer = py.Customer
           AND cy.Product_Code = py.Product_Code
          )

NOTE: COALESCE() may be ISNULL() in Access, or fall under some other name - it returns the first value of the values passed to it that isn't NULL (or NULL, if all values are NULL).

Also: as I'm not particularly familiar with Access itself, this is in SQL Server syntax. I think modern versions of Access should be OK with that, but I'm not sure.

How you would convince a GUI query builder to do this for you, I have no idea - most of them have difficulty with more complex queries. Hopefully, Access will let you create the query in a text editor and paste it in, if nothing else.

Well, since apparently MS Access doesn't have FULL OUTER JOIN, we need to get trick it into giving us we what want.

A FULL OUTER JOIN of A and B on A.col = B.col gives you three things:

The combination of all rows from A and B where there's a matching row in both tables;
All rows from A where there's no matching row in B (with the columns from both A and B, where all B columns are NULL); and
All rows from B where there's no matching row in A (with the columns from both A and B, where all A columns are NULL).

Now, A LEFT OUTER JOIN B ON A.col = B.col gives us the first two of the three items above.

So, we need to a way to get just item 3 above, and then combine that with the results of A LEFT OUTER JOIN B.

See if this (overly complicated) query seems to work:

SELECT COALESCE(cy.Salesman, py.Salesman) as Salesman
      ,COALESCE(cy.Customer, py.Customer) as Customer
      ,COALESCE(cy.Product_Code, py.Product_Code) as Product_Code
      ,COALESCE(cy.CurSales,0) as Sales
      ,COALESCE(py.PYSales,0) as PY_Sales
  FROM (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as CurSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year>
       ) as cy
       LEFT OUTER JOIN
       (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as PYSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year> - 1
       ) as py
       ON (    cy.Salesman = py.Salesman
           AND cy.Customer = py.Customer
           AND cy.Product_Code = py.Product_Code
          )

UNION ALL

SELECT COALESCE(cy.Salesman, py.Salesman) as Salesman
      ,COALESCE(cy.Customer, py.Customer) as Customer
      ,COALESCE(cy.Product_Code, py.Product_Code) as Product_Code
      ,COALESCE(cy.CurSales,0) as Sales
      ,COALESCE(py.PYSales,0) as PY_Sales
  FROM (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as CurSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year>
       ) as cy
       RIGHT OUTER JOIN
       (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as PYSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year> - 1
       ) as py
       ON (    cy.Salesman = py.Salesman
           AND cy.Customer = py.Customer
           AND cy.Product_Code = py.Product_Code
          )
 WHERE (    cy.Salesman IS NULL
        AND cy.Customer IS NULL
        AND cy.Product_Code IS NULL
        AND (   py.Salesman IS NOT NULL
             OR py.Customer IS NOT NULL
             OR py.Product_Code IS NOT NULL
            )
       )

UNION ALL combines the results of two queries (with compatible SELECT lists) into one result set.

The first query, A LEFT OUTER JOIN B, gives us items 1 and 2 from our bullet list above.

If we simply created a query, A RIGHT OUTER JOIN B, we'd get items 1 and 3 from the bullet list.

By adding the WHERE clause, where we only accept rows from the RIGHT JOIN where our join columns from A are NULL, and at least one of our join columns from B is not NULL. The rows from item 1 will all have matching values in A and in B - so, either they will have a non-NULL value in at least one of the join columns, or all the join columns from both A and B will be NULL. So, this should just give us the rows from item 3 in our bullet list.

NOTE: this is all untested - please confirm that it works before using in production code!


Order by for 2 row result causes 800% performance hit

The merge join works like a zipper - if you don't care about order, SQL Server knows that it can sort the input in any way it wants, and not have to worry about re-ordering anything. When you add the order by, in this case a merge join is no longer the best choice, because materializing and sorting the first CTE twice in the order defined by the ROW_NUMBER() expression must obviously be more expensive than it seems.

One workaround, thinking off the cuff at beer o'clock on a Friday:

;WITH ...
(
)
SELECT * INTO #x FROM gaps;

SELECT * FROM #x ORDER BY rn;

However I bet if you searched around for a fellow named Itzik Ben-Gan you would find more efficient approaches to problems involving gaps and islands (or the advice to not try to sort the way you're sorting).


What is stored in a transaction log file?

The transaction log stores every transaction and the changes made by those transactions to the database. This answer gets very complicated when going into detail but generally it stores the following.

Modification Operations
Log sequence numbers (LSN)
Row data. Before/After/Partial/Meta-data, depending on the operation.
Reserved space for rollbacks

It does not store queries or t-sql statements. Please refer to this overview by Microsoft.

https://msdn.microsoft.com/en-gb/library/ms190925.aspx

The specific format is not fully documented and is subject to change betwen versions.


Splitting up the tables into different activities sounds like a much better plan than trying to shoe-horn 7 different activities into 1 table.
Different objects belong in different tables - rule of thumb - better to have many smaller tables that are "thin" - i.e. fewer fields than 1 megatable which is "fat" (i.e. many fields, many of which will have to be NULL). These sorts of tables are unwieldy and difficult to programme against.
Check this out to see what happens if this advice is ignored to the point of lunacy :-)

Finally, if this is your first database  project, try a prototype with a couple of activities and see how that goes. Better to make your mistakes on a small project than a big one (and you will make mistakes).

Be sure to have a working, relatively complete, prototype before moving on to the full project.

Come back here if you get stuck on more specific issues - your question is a bit "broad" for this forum. After successfully completing a prototype you should then be ready to move onto a bigger undertaking.


Considering column selectivity only when deciding which columns to index ignores quite a bit of what indexes can do, and what they're generally good for.

For instance, you may have an identity or guid column that's incredibly selective -- unique, even -- but never gets used. In that case, who cares? Why index columns that queries don't touch?

Much less selective indexes, even BIT columns, can make useful, or useful parts of indexes. In some scenarios, very un-selective columns on large tables can benefit quite a bit from indexing when they need to be sorted on, or grouped by.

Joins

Take this query:

SELECT COUNT(*) AS records
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON u.Id = p.OwnerUserId;

Without a helpful index on OwnerUserId, this is our plan with a Hash Join -- which spills -- but that's secondary to the point.

With a helpful index -- CREATE INDEX ix_yourmom ON dbo.Posts (OwnerUserId); -- our plan changes.

Aggregates

Likewise, grouping operations can benefit from indexing.

SELECT   p.OwnerUserId, COUNT(*) AS records
FROM     dbo.Posts AS p
GROUP BY p.OwnerUserId;

Without an index:

With an index:

Sorts

Sorting data can be another sticking point in queries that indexes can help.

Without an index:

With our index:

Blocking

Indexes can also help avoid blocking pile-ups.

If we try to run this update:

UPDATE p
SET p.Score += 100
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656;

And concurrently run this select:

SELECT *
FROM   dbo.Posts AS p
WHERE  p.OwnerUserId = 8;

They'll end up blocking:

With our index in place, the select finishes instantly without being blocked. SQL Server has a way to access the data it needs efficiently.

In case you're wondering (using the equation Kumar provided) the OwnerUserId column's selectivity is 0.0701539878296839478

Wrap it up

Don't just blindly index columns based on how selective they are. Design indexes that help your workload run efficiently. Using more selective columns as leading key columns is generally a good idea when you're searching for equality predicates, but can be less helpful when searching on ranges.


What is limiting the batch requests per sec

This is just a pure guess (I cannot find anything to support this), but I believe that the reason it is much slower when you perform it on your local box is that it is going through the iteration on your local box.

If you did this as a cursor, I believe this would process everything from SQL and be much faster.

AKA GO 10000 is being iterated 10000 times from your local so it has to worry about the network for each statement as well.


If you write your queries properly and avoid SELECT * then any relational database will allow the addition of further columns or tables without requiring adjustment to the application.  You do, of course, have to declare each column to the DBMS before referencing it in SQL.

I find the claim that NoSQL is "schema-less" to be slightly misleading.  Applications using NoSQL persistance do, indeed, have a schema.  The difference is that the schema is held in application code and it is the responsibility of every programmer who touches the code throughout the application's life to enforce that schema.  With relational databases the data structure is declared to the service and it then takes the responsibility for enforcing those rules for ever after.  The real flexibility of NoSQL is that two rows within the same "bucket" (the definition of which various depending on your DBMS) can have different structure.


MariaDB 10.0.25 installs without configuration

It appears You had some MySQL/MariaDB/Percona instance previously installed, and some of data and config files were left in Your OS.

The way that worked for me on that case (Ubuntu Xenial 16.04 LTS with MariaDB 10.0 installed), with forgotten Maria's root password, was:

purging everything: sudo apt-get purge mariadb*
reboot; it didn't work without it, probably some related service running in background, locking some files from being purged: sudo reboot -h now
installing database once again: sudo apt-get install mariadb-server

Of course this was possible only on private dev machine, without any important data.


Rolling update to all rows in innodb table (no rollback if aborted)

I ended up doing this, to do it in batches of 1krow (which takes about .2s per batch if there's nothing to update, 2s otherwise; basically it has the same total speed per row if it's in batches of 10k, 5k or 1k):

SET SESSION sql_mode = 'strict_all_tables';
SET SESSION sql_warnings = 1;
\W
DELIMITER //
DROP PROCEDURE IF EXISTS update_dmf_lifespan//
CREATE PROCEDURE update_dmf_lifespan(INOUT i INT)
BEGIN
 SET @i = i;
 SET @max = (select id from death_master_files order by id desc limit 1);
 setloop: LOOP
  UPDATE death_master_files SET lifespan = DATEDIFF(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (IFNULL(IFNULL(death_day,birth_day),1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (IFNULL(birth_day,1)-1) DAY) WHERE DATEDIFF(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (IFNULL(IFNULL(death_day,birth_day),1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (IFNULL(birth_day,1)-1) DAY) >= 0 and id BETWEEN @i AND @i+1000;
  SELECT @i, ROW_COUNT(), LAST_INSERT_ID();
  SELECT *, DATEDIFF(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (IFNULL(IFNULL(death_day,birth_day),1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (IFNULL(birth_day,1)-1) DAY) as diff from death_master_files WHERE DATEDIFF(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (IFNULL(IFNULL(death_day,birth_day),1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (IFNULL(birth_day,1)-1) DAY) < 0 and id BETWEEN @i AND @i+1000;
  SET @i = @i + 1000;
  IF @i + 1000 > @max THEN
   LEAVE setloop;
  END IF;
 END LOOP setloop;
END
//
SET @i = 1//
CALL update_dmf_lifespan(@i)//
DROP PROCEDURE IF EXISTS update_dmf_lifespan//
DELIMITER ;

… so this'll probably take 2 days to run, plus more for the OPTIMIZE TABLE afterwards. :-/

Also, I haven't figured out how to get both SHOW WARNINGS and ROW_COUNT(), since they don't seem to be joinable in a single statement and both output results based on the previous statement (including each other).

I've set it to strict mode with a resumable point, which lets me manually inspect and resume after warnings.

That's surely not the best option, though I'm not sure what would be better. For cases where the deathdate is before the birthdate (the data is not exactly 100% reliable), and thus it raises on lifespan being unsigned, it doesn't update and instead outputs with select. This won't catch other sources of warnings, though.


mysql add a column to a number of tables

From this question, I understand that, you need to store same data multiple times on a database. Before starting this understand that, it is not necessary to store same data multiple times on database. IF I understood correct, a simple work around will be, have a master table group with data guppy, barb, goldfishand use its primary id on a child tables.
Example:

master table:

create table group (id int, group_name char(10), id primary key) engine = innodb;

child table:

create table child1 (id int, group_id int, ..... foreign key group_id references group(id), id primary key) engine = innodb;

But, it depends on your requirement, may be after analyzing complete requirement, this model may/may not applicable. I hope this answer will help you in some way. Thank you.


Not sure if this is the kind of crazy thing you were looking for....

Disclaimer: I have absolutely no idea why you would want to use this.

SELECT *
FROM Users AS u
INNER JOIN (SELECT 'Townsville' town) towns
  ON towns.town = u.Town;


Configuration options for relational algebra evaluator "ra.jar"

That RA tool does not have an option to output the SQL queries.

However, the mapping from relational algebra to SQL is pretty much trivial (except for division), can be easily found with a web search, and is something you should have learned in your course.

If you want to find out how RA handles this, look into the file RAXNode.java.
The various classes derived from RAXNode each implement one relational algebra operation; the method toPrintString shows the RA operation, while genViewDef generates the corresponding SQL command.
For example:

public static class PROJECT extends RAXNode {
    public String toPrintString() {
        return "\\project_{" + _columns + "}";
    }
    public String genViewDef(DB db) {
        return "SELECT DISTINCT " + _columns + " FROM " + getChild(0).getViewName();
    }
}
...
public static class INTERSECT extends RAXNode {
    public String toPrintString() {
        return "\\intersect";
    }
    public String genViewDef(DB db) {
        if (db.getDriverName().equals("com.mysql.jdbc.Driver")) {
            // MySQL doesn't support INTERSECT, so we need a workaround.
            ...
        } else {
            return "SELECT * FROM " + getChild(0).getViewName() +
                " INTERSECT SELECT * FROM " + getChild(1).getViewName();
        }
    }
}


Why MySQL Workbench creates more than two foreign keys for many to many relations?

If id is the primary key for pstx_project then you definitely don't need pstx_project_company_id in your many to many breakdown table. As you say, these tables just need their related tables primary keys (in which it's composite will become it's own primary key), and a separate foreign key against each referenced table (which might be composite also, depending on the amount of columns from the referenced table's primary key), with the addition of other optional columns you might want to add.

If company_id was part of the primary key of pstx_project in some point, then it would make sense to include it in your many to many table. Maybe you edited the primary key at some point?

Remove pstx_project_company_id from your many to many table as it will bring you problems with consistency, as it would be redundant. Also make sure that user_id with pstx_project_id inside user_has_pstx_project is a primary key so it enforces non-duplicates.

As a side note, I also note a loop in your design: there are 2 ways to get to a company from a user. While loops are possible in some scenarios, please review if it's correct for your case as you might have another redundant link between user and company.


Incorrect Statistics EQ_ROWS Causing Parallel Table Scans and Deadlocks

As pointed out by sp_BlitzErik (thank you!), the problem here was the sampling rate was too low for the billion row transaction table.  The more rows sampled by the statistics update, the more accurate the estimate, but the longer it takes for SQL Server to generate the statistics.  There is a tradeoff between database maintenance time and statistics accuracy.

A simple `update statistics MyTable' was sampling 0.13% of the rows.  This generated a very poor estimate for the "EQ_ROWS" column that was usually hundreds of times worse than the actual row counts.  For example, some EQ_ROWS estimates were over 60,000 when the actual counts were about 150.  On the other hand, the default stats update completed in 25 seconds-- pretty fast for a billion rows.

A full scan generated perfect EQ_ROWS statistics.  However, the full scan required about 5 hours to complete.  That would not be acceptable for the customer's database maintenance window.

Below is the chart of samples vs. accuracy.  We're going to recommend the customer adjusts the sample size manually for the largest tables in the system.  I expect we'll go with a sample size of about 10%.  The Avg Stat Diff column is calculated as ABS(EQ_ROWS - ActualRowCount).

Sample Size     Avg Stat Diff Time to Create Stats
 =============== ============= ====================
 Default (0.13%)        42,755             00:00:25
 1%                      4,880             00:02:41
 5%                        940             00:13:47
 10%                       443             00:27:45
 20%                       209             00:56:28
 50%                        67             02:24:17
 100% (fullscan)             0             ~5 hours


(Technically, this is 'not an answer', but claim it needs to be said.)

There will be an arbitrary number of columns, correct?  Then (1) the output is impractical, and (2) it would be virtually impossible to generate the output in SQL.

So, (1) rethink the requirement, then (2) consider whether to do it in PHP instead.


-- SET A MARK (RST=1) EVERY TIME PREVIOUS END_DATE IS NOT EQUAL START_DATE
WITH CT AS
(
    SELECT
        TASK_ID, START_DATE, END_DATE,
        CASE WHEN (COALESCE(LAG(END_DATE) OVER (ORDER BY START_DATE), END_DATE) = START_DATE)
             THEN 0 ELSE 1 END RST
    FROM
        TASK
)
-- SUM(RST) TO SET A GROUP (1, 2, 3, ...)
, CT2 AS
(
    SELECT
        TASK_ID, START_DATE, END_DATE,
        SUM(RST) OVER (ORDER BY START_DATE) AS GRP
    FROM
        CT
)
SELECT
    MIN(START_DATE) AS START_DATE,
    MAX(END_DATE) AS END_DATE
FROM
    CT2
GROUP BY
    GRP;

START_DATE          | END_DATE
:------------------ | :------------------
01/10/2015 00:00:00 | 04/10/2015 00:00:00
13/10/2015 00:00:00 | 15/10/2015 00:00:00
28/10/2015 00:00:00 | 29/10/2015 00:00:00
30/10/2015 00:00:00 | 31/10/2015 00:00:00

db<>fiddle here


Exporting table data into csv format from server to local

There are two problems:

COPY writes to a file on the database server.

If you want to write to a file on the client machine, use psql's \copy which runs COPY ... TO STDOUT under the hood.
You cannot specify a host and user name in the file name in the hope that the file will be transferred to another machine. It has to be a path on the file system (for COPY, it has to be an absolute path on the server's file system, hence the error message).

Start psql on the machine where you want the file to end up and use \copy:

\copy finance.coa to '/home/kedarisettyakhiles/akki.csv' (FORMAT 'csv', DELIMITER ',', HEADER)


SQL Server query problem when selecting data from child table based on column in parent table

Thank you @Greg for answering the question. The problem required that the SQL statement fully qualify the columns in the where clause by referencing the tables for the columns. In this case. I needed m_econ_fred_source=attrib_id.

The complete solution is:

SELECT [update], [value]
FROM m_econ_fred_source JOIN s_econ_fred
ON m_econ_fred_source.param_id = s_econ_fred.param_id
WHERE m_econ_fred_source.param_id = 2 AND
      m_econ_fred_source.attrib_id = 7 AND
      [update] BETWEEN '2014-01-01' AND '2016-03-01'
ORDER BY [update] ASC


What's the point of WHERE 1=2 for SELECT INTO table query

The reason you put the WHERE 1=2 clause in that SELECT INTO query is to create a field-copy of the existing table with no data.

If you did this:

select *
into Table2
from Table1

Table2 would be an exact duplicate of Table1, including the data rows.  But if you don't want the data contained in Table1, and you just want the table structure, you put a WHERE clause to filter out all of the data.

BOL SELECT INTO Reference quote:

  SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it.

If your WHERE clause has no resulting rows, none will be inserted into the new table:  Therefore, you end up with the duplicate schema of the original table with no data (which would be the desired result in this case).

The same effect can be achieved with TOP (0), for example:

select top (0) *
into Table2
from Table1;

Note:  The SELECT INTO will not duplicate the source table's indexes, constraints, triggers, or partition schema.


Oracle DBMS Scheduler - what if database fails?

If your scheduler database fails, this should raise an event in your monitoring application. This is about the similar situation as compared to cron, when the server fails. In that case cron also has no way to tell it failed.

If you want to use dbms_scheduler as an enterprise scheduling tool, don't forget the reporting. Reporting should help explaining what ran and why not.


One filegroup, multiple datafiles, how to get list of tables in each file

An object in a filegroup will use all datafiles in the filegroup. Any table in FG1 resides equally on Datafile1, Datafile2 and Datafile3. If you need to control placement you need to create distinct filegroups. To remove a file from a file group use DBCC SHRINKFILE EMPTYFILE.


To achieve this task kindly follow below steps:-

create server level trigger on create_database.
Create sql job and add code to dynamic get the name of database and initiate backup.
Add Code in the trigger to invoke job to create database backup.

e.g

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
   EXEC msdb.dbo.sp_start_job N'backup_job' ;
GO


MySQL Performance: conditionnal left outer join vs where only

If both tables are big enough so this is really a concern for you then fastest way is using two queries, one for each side of OR. That way you can be sure each query only accesses necessary rows, or even uses only index.

If you are worried about "sending" two queries, then you can "glue" them together using UNION - if you get at least one row, then one of those conditions was true. If result is empty, both were false.

Just a note: you can analyze your queries and see plan and estimated rows to check by using EXPLAIN.


Truncation error creating clustered index on schema-bound view

The underlying table has an INT primary key column, so I was going to
  make the view's clustered index based on that.

Here's what Microsoft says about Create Indexed Views:

  Creating a unique clustered index on a view improves query performance
  because the view is stored in the database in the same way a table
  with a clustered index is stored.

Since a clustered index is composed by data rows themselves your assumption that "the only column in the index is an int" is incorrect for, even though only one column was listed on the DDL, the clustered index view shall contain all columns under the hood. If it was a Nonclustered index, then only the listed columns would compose the index.

With the DDL you provided I was able to run some lab tests and the following chart from SSMS shall ilustrate my point:

As you can see, the view has pretty much the same size of the table. And before the index was created the view wouldn't even show on the chart. If it was formed only by the int column as you thought, it would have a much smaller size.

That info along with the contribution of SMor about the limitation of HASHBYTES Arguments for SQL Server 2008:

  For SQL Server 2014 (12.x) and earlier, allowed input values are
  limited to 8000 bytes.

In my lab I generated some data that was greater than 8000 bytes, but since I'm running a SQL Server 2019 instance, the DDL executed without any error. You can verify if you have any values that would exceed that limit with the following query:

SELECT COUNT(1)
FROM [dbo].[Events]
WHERE DATALENGTH([EventTitle]) > 8000
    OR DATALENGTH([EventContent]) > 8000;


Automatic Failover in Availability Group

If you want to enable automatic failover, you will need to ensure that you have an "automatic failover set", to include two replicas (in your case, that'd be your two nodes) both set with synchronous commit, as well as automatic failover.

When you setup the Availability Group, this can be accomplished through the GUI, but if you had this scripted out, it would appear in the CREATE AVAILABILITY GROUP command.  If you take a look at the syntax and possible parameters of CREATE AVAILABILITY GROUP, you would simply see the opportunity to set AVAILABILITY_MODE = SYNCHRONOUS_COMMIT as well as set FAILOVER_MODE = AUTOMATIC.  This is essentially the steps to setup automatic failover.

Likewise, if this is already setup you can modify these replica settings with ALTER AVAILABILITY GROUP ... MODIFY REPLICA ON ... and specify the same above parameters.

Before continuing on, though, with the above modifications it is definitely going to be worth verifying what you already have in place.  A quick query that will tell you what your commit mode and failover mode is for your current configuration is below:

select
    ar.replica_server_name,
    availability_group_name = ag.name,
    ar.availability_mode_desc,
    ar.failover_mode_desc
from sys.availability_replicas ar
inner join sys.availability_groups ag
on ar.group_id = ag.group_id
order by availability_group_name, replica_server_name;

Feel free to edit your question and put the output of the above query for review.

EDIT:  In order to address your comment, it is worth pointing out that to have automatic failover you must be utilizing synchronous commit.


How to change default security on SQL Server database so that everyone can use it?

You could create a domain group that you could use to create as login and user on your SQL Server and YourDatabase.  After creating the domain group, you could do something like:

USE [master]
GO
CREATE LOGIN [DOMAIN\AllUsersForYourDatabase] FROM WINDOWS
GO
USE [YourDatabase]
GO
CREATE USER [DOMAIN\AllUsersForYourDatabase]
FOR LOGIN [DOMAIN\AllUsersForYourDatabase]
GO

You can then make the logins on your domain members of that group. All the logins will inherit the permissions granted to the group that gives them access. (You could include Domain Users in the group if you truly wanted everyone in the domain to have access.)

Of course, you need to grant the needed rights to the YourDatabase user [DOMAIN\AllUsersForYourDatabase] so that everybody in the group gets the same rights.

If you need some users to have additional rights, those can be individually granted.  Or you can create another Group that would include only the users with the extra rights.  The users will get the aggregate of all rights from all groups for that login.


How to find the rows from a table which has exactly two 'n' s in the employee name?

something like this should be enough:

select * from employee_table where Length(lastname) - length(replace(lastname, 'n','')) = 2

be aware that may cause a full table scan :-)


How can I make this backup scheme more friendly for reporting?

Setup SQL Server replication between the production servers and the reporting server so that the data on the reporting server is updated in real time automatically.


Join 3 tables with where and sorting clauses

Still room for improvement. This is your last version, untangled, formatted and shortened with table aliases:

SELECT vm.id, v.name, vm.name, count(c.id) AS count
FROM   vendors       v
JOIN   vendor_models vm ON vm.vendor_id = v.id
JOIN   cameras       c  ON c.model_id = vm.id
GROUP  BY vm.id, v.name, vm.name
ORDER  BY v.name;

Remaining problems:

You have the name name twice in the result, which is unhelpful to say the least.
(A shining example why non-descriptive names like "name" are bad names.)
Sort order not fully deterministic.
Not as fast as it could be.

I suggest:

SELECT vm.id
     , v.name AS vendor, vm.name AS model  -- column aliases to disambiguate
     , c.camera_count
FROM   vendors       v
JOIN   vendor_models vm ON vm.vendor_id = v.id
JOIN  (
   SELECT model_id, count(*) AS camera_count  -- count(*) slightly faster
   FROM   cameras
   GROUP  BY 1           -- aggregate first, join later
   ) c ON c.model_id = vm.id
ORDER  BY v.name, vm.name, vm.id; -- add "id" if the names are not unique

If you count cameras per model first, you have far fewer join operations and don't have to aggregate outer columns at all. Should be faster.

count(*) is a bit faster than count(expression) (separate implementation) while doing the same in the absence of NULL values.

Since columns from the outer tables (vendors, vendor_models) are not aggregated at all now, you can put any of these columns in the SELECT list at will - including vm.*.

Related:

Understanding multiple table join with aggregation
Slow queries related to subqueries using aggregation


What does it mean that a mongodb query has a waitingForLatch value of ReplicationCoordinatorImpl::_mutex?

The replication coordinator mutex is used for nearly anything even tangentially related to replication.  This is usually held for sub-microsecond periods when everything is running right.
One possibility is the queries are taking the mutex in order to check the current status of the replica set member to make sure it is primary/secondary.  If there is something like an election or sync source change going on it may hold the mutex.  Even then I wouldn't expect that to take more than a few seconds, and any such activity should cause a REPL entry in the log.
If this occurs frequently, you might try increasing the default logging verbosity to see if the debug log messages provide any clues.


Seconds_Behind_Master high even though Read_Master_Log_Pos and Exec_Master_Log_Pos are very close

Regarding the seconds_behind_master oscillation.
I found that the description in seconds_behind_master in the mysql documentation is not accurate. I checked the source code.

long time_diff= ((long)(time(0) - mi->rli->last_master_timestamp)
                            - mi->clock_diff_with_master);

Seconds behind master is calculated based on the difference between current timestamp on the slave and the timestamp on the statement which is being executed by the SQL thread and any initial difference in the clocks between the slave and the master.
And if the SQL thread is completely caught up with the IO thread (i.e. SQL thread has executed all the statements in the relay log file), it is shown as 0 in mysql5.6 (This was changed to show NULL in this case in 5.7)

In my case, the IO thread was the one that was lagging, I checked the status of the binlog file on the master and relay log file on the slave. The IO thread was waiting for the binlog_dump thread to send data to the slave. For some reason, the IO thread was not fetching/able to fetch all the data from the master(Could be a network related issue. Looking into it). The rate of data growth on the slave's relay log file was slower than the rate of the data growth on the master's binlog even when there was a lot of data that can be fetched. And before the IO thread is able to fetch more data and write to the relay log file, the SQL thread was finishing executing those statements. And seconds_behind_master becomes 0.

Whenever SQL thread is executing these events in the relay log file, it shows a huge lag and once it has completed executing these events, the value falls to 0.


Fastest way to do a mysqldump restore on Amazon RDS?

You must now think in terms of the RDS Instance

Provisoning more IOPs
Is it safe to use innodb_flush_log_at_trx_commit = 2

Set innodb_flush_log_at_trx_commit = 2
Set sync_binlog=0

Since max_allowed_packet = 1M by default in Amazon RDS, this could cause mysqldump's multirow inserts (extended inserts) could fail. You may want to mysqldump the database with --skip-extended-insert. This will guarantee the data being inserted one-by-one, but the dump will be much larger and take longer to import.

Please read What I learned while migrating a customer MySQL installation to Amazon RDS from Percona's Blog for even deeper insights.


What is the algorithm used for Index Recommendation in SQL Databases?

The database tuning advisor exact algorithm isn't documented, but you can have somewhat of a look how it works by using a server side trace to look at the commands it generates.

I'm not sure what algorithm it uses to determine which ones to create but it relies heavily on  creating hypothetical indexes and then using the undocumented DBCC AUTOPILOT to create execution plans that use these hypothetical indexes.

I suppose it then somehow compares the resulting execution plans to determine which ones have the lower cost.

Daniel Farina has an excellent post explaining how it works and how you can do it by hand if you really want to: SQL Server Performance Tuning with Hypothetical Indexes.

From that page:

  You are tuning a query and need to check the performance impact of a
  new index. But the table has millions of rows and it will take a lot
  of time to create an index. Keep reading and you will see how to
  create and test hypothetical indexes with the undocumented DBCC
  AUTOPILOT command.

and

  Since this is an undocumented feature there is not information about
  it but, as far as I can tell, giving the fact that AUTOPILOT is the
  key component of Database Engine Tuning Advisor (DTA); this should
  work on all version in which DTA is available.


How to rollback last executed sql script fired as sysdba

The default tablespace of the SYS (sysdba) user is typically SYSTEM. From that you can figure out the .dbf file being used, although I'm not sure how this will help.
Maybe. Does your database run using flashback? Also, manually backing out changes (or write a script to undo what you did) might be possible.
Maybe.  What changed?
Yes.
Be more careful.  Ask this question on dba.stackexchange.com.

EDIT:

I would add that running scripts as the SYSDBA user should be VERY carefully evaluated as to necessity, and probably never used for application purposes.  As you can see, the SYSDBA user has essentially unlimited power over your database.


Freeze I/O on purpose like a vss snapshot

There are two undocumented DBCC commands you can use for this, on a test system of course.

DBCC FREEZE_IO('database_name');
DBCC THAW_IO('database_name')

Check out Erik Darling's blog post here, which is where I first heard about these commands: Wait Stats When VSS Snaps Are Slow

He's also using them to diagnose what behavior is like during these snapshot-related freezes.


ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

There are several steps to configuring a server to accept ReadOnly traffic.  The following link walks you through it, http://msdn.microsoft.com/en-us/library/hh710054.aspx ,but basically you need to configure each server in the AG and then set up the routing for each.

Here's the T-SQL involved:

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'COMPUTER01' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'COMPUTER02' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
GO

Sounds like you may be missing the configuration and/or routing information for the primary.