Bad query plan only on first execution, not in SSMS

When application is run for the first time of day,then it is usually slow,so problem may be in Middleware logic also.

Like you said you are using Dataset,if your were using Generic collection like LIST and populate them with SqlDataReader,then you can see the performance improvement your self.

Second thing is How you are using those 2 ResultSet ?Are the subject to further filter between datatable. ?

As far as your query is visible, I see that you are querying Order Table twice using same predicate and Order by clause

your query can be rewritten like this,

Create TABLE #tempOrder(all required columns)
insert into #tempOrder
SELECT TOP (20) bunch of configurable columns, xml blob of customer preferences, ...
FROM ORDERS
INNER JOIN CUSTOMERS ON CUSTOMERS.ID = ORDERS.CUSTOMERID
INNER JOIN CUST_PREFS ON CUSTOMERS.ID = CUST_PREFS.CUSTOMERID
WHERE ORDERS.DATE BETWEEN @from and @to
ORDER BY ORDERS.DATE

SELECT bunch of configurable columns, xml blob of customer preferences, ...
FROM
#tempOrder ORDERS
INNER JOIN
    (SELECT sums, avgs, etc FROM ORDERDETAILS
     GROUP BY ORDERDETAILS.ORDERID) orderavg on orderavg.ORDERID = ORDERS.ORDERID
... whatever other joins needed based on configuration ...

SELECT ORDERID, orderdetails
FROM
    (SELECT ORDERID
     FROM #tempOrder ORDERS
INNER JOIN ORDERDETAILS ON ORDERDETAILS.ORDERID = ORDERS.ORDERID

Create Non Clustered index on ORDERS(DATE)

There is no point in creating index on #tempOrder since it will contain only 20 records always(Top 20 indicate)

  bunch of configurable columns

It means that query must be dynamic so it all depend upon your real query.

Use SET NOCOUNT ON


Searching from array (complex criteria) without unnesting?

Even if the syntax were valid, your first query wouldn't do what you describe anyway, as you need to ensure it is same date from the array which is > one thing and < another thing, not two different dates.  You need to use ranges, both to be syntactically valid, and to be semantically valid.

select * from somedates where
    daterange((startdate+interval'0.6 years')::date,(startdate+interval'1.4 years')::date) @> ANY (otherdates) and
    daterange((startdate+interval'3.5 years')::date,(startdate+interval'5.5 years')::date) @> ANY (otherdates);

You can make it a bit less ugly like this:

create or replace function daterange2(date, interval, interval) returns daterange
  as $$ select daterange(($1+$2)::date, ($1+$3)::date) $$
  language sql immutable parallel safe;

select * from somedates where
    daterange2(startdate,'0.6 years','1.4 years') @> ANY (otherdates) and
    daterange2(startdate,'3.5 years','5.5 years') @> ANY (otherdates);


What happens when you have two events scheduled to run after identical n seconds?

You could stagger survey_task yourself by calling the following functions

IS_FREE_LOCK()
GET_LOCK()
RELEASE_LOCK()

This supports the paradigm known as cooperative or advisory locking.
Use in the survey_task as follows:
DELIMITER $$
CREATE PROCEDURE survey_task()
BEGIN

WHILE IS_FREE_LOCK('survey_task_lock') = 0 DO
    SELECT SLEEP(0.1) INTO @x;
END WHILE;
SELECT GET_LOCK('survey_task_lock',10) INTO @x;

select tel_number, text_message, id INTO @t_number, @t_message, @t_id from incoming_messages where t_status = 0 limit 1;

insert into surveyed_lot(telephone_number, survey_answer,date_of_insert )
        values(@t_number, @t_message,now());

UPDATE incoming_messages SET t_status = 1 WHERE id = @t_id;

SELECT RELEASE_LOCK('survey_task_lock') INTO @x;

END $$
DELIMITER ;

The idea is to have the other invocation of survey_task poll every 0.1 seconds until the lock is free. Then acquire the lock and release it when done.
GIVE IT A TRY !!!


What does 'sp_configure allow updates' do?

This is an old option from the SQL Server 2000 days that would allow you to directly make updates to the system catalog tables (sysobjects, syscomments, etc.). Since 2005, this is no longer possible (at least not without jumping through a ton of additional hoops beyond just an sp_configure option - it's not something you ever want to do on a production system), and all of the system catalog is now exposed through read-only views like sys.objects.

I'm not sure how that would be related to the maintenance plan failing, though. That error you saw is pretty typical if you delete or otherwise remove a database that's selected as part of the maintenance plan. Usually, editing the offending step in the maintenance plan and resaving is enough to fix it.


Join query returning 0 rows - impossible where

this query is supposed to return a list of the top 10 brands (other than the selected brand) that are purchased with the selected brand.

Try this:

SELECT `2_products`.`brand`, COUNT(*) as numOrders
FROM `2_products`
INNER JOIN `1_txns` ON `1_txns`.sku=`2_products`.sku
INNER JOIN (SELECT `1_txns`.`txn_id`
    FROM `1_txns`
    INNER JOIN `2_products` USING (sku)
    WHERE brand = :brand) as tmp_txns ON tmp_txns.txn_id=`1_txns`.txn_id
WHERE brand <> :brand
GROUP BY brand
ORDER BY numOrders desc, brand ASC
LIMIT 10;

Example sqlfiddle


The idea behind the large object API is to mimic a file-like API. The OID is like the path of the file, and the file descriptor obtained by lo_open or lo_creat  is the equivalent of the POSIX open() and creat() system calls for files.

JDBC provides LargeObject.truncate() and the both libpq (in C) and the server have built-in lo_truncate() functions.

So yes it  makes sense to replace LO contents (keeping the same OID) by a truncate followed by lo_write if libpq, or lowrite if server-side code, or LargeObject.write() if using the dedicated JDBC class, as you could do with a file on a filesystem.

It would work as well to create a new LO with a new OID and then unlink the old one, it's just less elegant and it consumes a new OID for no good reason.


MySQL binlog does not show SQL statements

Remove this clause:

--database=securities


Can a witness server be a witness for more than one pair of mirrored servers?

There's no reason why it can't be done. In fact, Microsoft documentation states that one server can be a witness to multiple sessions.


Backing Up PDBs with RMAN

  To back up one or more PDBs while connected to the root:

  Start RMAN and connect to the root as a common user with the SYSBACKUP
  or SYSDBA privilege as described in Connecting as Target to the Root.
  Issue a BACKUP PLUGGABLE DATABASE command at the RMAN prompt.

  The following example backs up the PDBs sales and hr:

  BACKUP PLUGGABLE DATABASE sales, hr;

  To back up one PDB while connected to the PDB:

  Start RMAN and connect to the PDB as a local user with the SYSBACKUP
  or SYSDBA privilege as described in Connecting as Target to a PDB.
  Issue a BACKUP DATABASE command at the RMAN prompt.

  BACKUP DATABASE;


How to use MAX() to select a single record but prevent GROUP BY from selecting all distinct values and multiple records

Just incase you need another solution. Below uses row_number() over (partition by .. order by)

CREATE TABLE dbo.docStore
    ([Doc No] int,
     [Version No] int,
     [Line No] int ,
     [Data-a] varchar(10),
     [Data-b] varchar(10))
;

INSERT INTO dbo.docStore
    ([Doc No],[Version No],[Line No],[Data-a],[Data-b])
VALUES
    (1,1,1000,'abc','abc'    ),
    (1,2,1000,'xyz','abc'    ),
    (1,1,2000,null,'lmnop'   ),
    (1,2,2000,'ggg','lmnop'  )
;

with cte as
(
select [Doc No], [Version No], [Line No], [Data-a], [Data-b],
        row_number() over (partition by [Doc No], [Line No]
                           order by [Version No] desc)
        as rn
from dbo.docStore
)
select [Doc No], [Version No], [Line No], [Data-a], [Data-b]
from cte
where rn = 1
;

-- clean up
drop table dbo.docStore
;


I deleted a folder from /var/lib/mysql and now all of my databases seem to be unaccessible

mysqlcheck won't help, it's for damaged files.
You should have deleted non-table files in the directory, not the directory itself. Especially if you use MySQL 8, as I suspect. Because your tables metadata are stored in an internal dictionary.
Anyway, don't you have a backup?
If not, recreate the directory and try running individual DROP TABLEs. It may work or not. Let me know how it goes.


Looking for a book on optimizing tables and queries for handling time sequence data

Adding "out of print" to my search terms led to https://www.postgresql.org/message-id/02D8929E-1F0D-11D9-9830-000A95A0A9C8%40mit.edu which references https://www2.cs.arizona.edu/people/rts/tdbbook.pdf which is what I was looking for.


Three possibilities come to mind immediately:

Maybe someone dropped an index? You should create a query that also breaks it down by index. A row missing from those results would be much more obvious than numbers changing.
A filtered index, and a data change that substantially reduced the number of rows contained in the index.
Fragmentation - was a defrag or rebuild run recently against this table or any of its indexes? This could drastically reduce the number of pages used by an index, even if the number of rows and the index itself have not changed.

Also, why didn't it affect the size of the database? Because the database is just a container, and it doesn't auto-shrink just because you delete data or shift data around (unless you have auto-shrink set, which you absolutely should not do!). Think about your house: if you clean up the clutter in your basement, your house doesn't shrink! You don't want your database to do this either. All this does is cause the data file to grow again as soon as you add more data. What could be more wasteful than that? You freed up some space, temporarily, only to make user transactions wait while you use it up again. Yay?

In any case, your overall data size seems to have remained the same, so I postulate that two things happened: you added a million rows that contributed significantly to data size, and some index maintenance happened with reduced the index size substantially. In the end, we can't tell you what caused this, because we don't have access to your system. We can only throw wild guesses into the air, and you have to figure out which ones stick.


How a DBA can monitor multiple huge SQL Server databases more than 2 tera bytes

Lots of questions so many answers. It depends on so many factors. Budget being one.

First of all I would like to point out that visiting some basic SQL Server courses will vastly increase your understanding of SQL Server and its requirements. IMHO switching from Windows Server Administrator to SQL Server DBA is not just something that can be accomplished by querying Google or asking questions in a forum. You can acquire some knowledge, but without the full picture, you are liable to break something in a productive environment.

Do a search for SQL Server Monitoring Tools in Google. You should get a list of well known products back.
That depends on the monitoring software you purchase. Some can handle these requirements some not. There are also freeware tools available that could help with certain aspects.
Some monitoring products can handle up to 200-250 servers per instance depending on the load and the frequency of polling.
Brent Ozar has an article on this aspect: How many servers can one person manage?, and then again: it depends.
So many aspects here:

Don't hand out the sa password! Never!
Windows Server admins are not DBAs and should not have access to the SQL Server instance. (My opinion)
People outside of the Server or DBA team should not have access to backup drives or to SQL Server database file locations.
Only a qualified DBA should be allowed to be have sysadmin server role
Limit each SQL Login/NT Login to the least possible number of databases.
Limit the SQL Login to different roles depending on usage

DB_NAME_User : db_datareader, db_datawriter
DB_NAME_Dbo : db_owner

Try Transparent Database Encryption
Try Database Backup Encryption

Security depends on the requirements of your business. Ask the head of IT for SLAs or OLAs and any company-wide security policies, etc.

One question you haven't yet asked is: Availability. Will you be required to have Always on Availability Groups?

You asked a lot of questions, I hope you have a general starting point.


\password username

... will change the password for a given username, whilst suppressing the inputted password. I don't know of a way to do it for a given variable.


add RECOMPILE for specific query server wide

You can use a plan guide. What type of plan guide to use depends on whether the query is in a proc (OBJECT) or not (SQL). Example:
sp_create_plan_guide
@name = N'myGuide',
@stmt = N'SELECT col1, col2 FROM MyTable',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (RECOMPILE)'

Documentation for plan guides here: https://docs.microsoft.com/en-us/sql/relational-databases/performance/plan-guides


MYSQL query to return most recent entry as long as it is equal to XXX

with AD as (
select T.*, rank() over (partition by Agent, customer_id order by date desc) R from tableName T
 /* order by agent, customer_id, date desc */
 )
select * from AD
  where R = 1 and answer = 'Maybe';

See db fiddle
For people trying to answers questions like these, tableconvert comes in handy.  Use Import / Markdown.


Recalculate row size in SQL Server 2008?

This is a bug (albeit a fairly minor one) that still exists in SQL Server 2012 RC0. Report it at connect.microsoft.com

(Thanks Mr Denny).


Create a trigger to update table data on another Server's database

To accomplish what you are wanting to do, it is possible to use the FEDERATED storage engine on both servers, in conjunction with triggers, to allow each server to update the other server's database.

This is not exactly a simple out-of-the-box solution, because it requires additional precautions and requires you to decide whether consistency or isolation tolerance is more important and allow the queries to fail when the other server isn't available (more consistency) or use a CONTINUE HANDLER to suppress errors (isolation tolerance).

But here is an extremely simplified example.

Each server would have the identical configuration.

The local user table:

CREATE TABLE user (
  username varchar(64) NOT NULL,
  password varbinary(48) NOT NULL, /* encrypted of course */
  PRIMARY KEY(username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A local table that is federated to the user table on the other server.

CREATE TABLE remote_user (
  username varchar(64) NOT NULL,
  password varbinary(48) NOT NULL, /* encrypted of course */
  PRIMARY KEY(username)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://username:pass@the_other_host:port/schema/user';

Selecting from remote_user on one server will retrieve the records from the other server, and insert/update/delete on that table will change data on the other server.

So, we create triggers do accomplish the purpose of updating the distance server.  They are written as BEFORE triggers, with the idea being that we don't want to do something to ourselves that we can't do to the other server -- for example, if a username already exists on the other server, but not here, we want the insert on the other server to throw an error that prevents us from creating the user here... as opposed to creating a user here with what would be a conflicting username.  This is, of course, one of the tradeoff decisions you'll need to make.

DELIMITER $$

CREATE TRIGGER user_bi BEFORE INSERT ON user FOR EACH ROW
BEGIN
  INSERT INTO remote_user (username,password) VALUES (NEW.username,NEW.password);
END $$

CREATE TRIGGER user_bu BEFORE UPDATE ON user FOR EACH ROW
BEGIN
  UPDATE remote_user
     SET username = NEW.username,
         password = NEW.password
   WHERE username = OLD.username;
END $$

CREATE TRIGGER user_bd BEFORE DELETE ON user FOR EACH ROW
BEGIN
  DELETE FROM remote_user
   WHERE username = OLD.username;
END $$

DELIMITER ;

This is not a perfect solution and is not a high-availability solution, because it relies on solid connectivity between the two systems and even if you are using InnoDB and transactions, the actions you take against the target table are not part of your local transaction and cannot be rolled back.

I use the FEDERATED engine quite a bit; it comes in handy for a number of creative purposes in my environment, including one situation where I used a federated query launched by a trigger to impose foreign key constraints against a foreign data source; however, I restrict its use to back-end processes where unexpected issues such as timeouts, coding errors, or server-to-server network/outage/isolation events cannot result in the end user on one of our web sites experiencing any kind of problem.  Your ability to tolerate such a situation would be a major determining factor into whether this is an appropriate solution.

An alternative would be to configure your two servers in master/master replication.  For this, you would need to use different database names on each server, so that for most events that replicate, the two servers could not possibly conflict with each other.  In the worst-case scenario, if you lose connectivity or encounter a replication error, the two sites would still be running independently and you could resynchronize and recover.  Configuration would look something like this:

database_a database for site A
database_b database for site B
database_c database for only the shared table(s)

Then, in database_a and database_b:

CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW user AS SELECT * FROM c.user;

MySQL will treat database_a.user and database_b.user as aliases for the "real" user table, database_c.user, so you would not have to change your application other than to use its designated database (i.e, you wouldn't have to configure it to understand that the user table was actually in a different schema, because the view will function pretty much transparently with this configuration).  If the schemas have foreign keys against the user table, you would declare those against the true base table database_c.user.

Configure the two servers to replicate everything, but set auto_increment_increment and auto_increment_offset appropriately so you do not have conflicting auto-increment values on the shared table(s), if your tables use auto-increment.  (Note, the documentation says that these variables are for NDB tables only, but that's not accurate).

An extra advantage of this setup is that your two servers would then have a complete duplicate of the other site's data that you could potentially use to your advantage for recovery from hardware failure in one of the servers.


Do synonyms affect remote query performance

A synonym is just a pointer and gets simplified away prior to optimization. If you have this:

CREATE SYNONYM dbo.foo FOR OtherServer.db.dbo.blat;

Then these two will optimize the same, producing identical execution plans (both having a Remote Query operator that points at blat):

SELECT bar FROM dbo.foo;

SELECT bar FROM OtherServer.db.dbo.blat;

You can test this quite easily.


How to create `instead of` trigger on a table in postgres?

You can achieve what you want with a regular row level trigger. If that trigger throws an exception, the insert won't happen. If you return new from the trigger, the insert will continue.

Quote from the manual

  If a nonnull value is returned then the operation proceeds with that row value. Returning a row value different from the original value of NEW alters the row that will be inserted or updated. Thus, if the trigger function wants the triggering action to succeed normally without altering the row value, NEW (or a value equal thereto) has to be returned

(emphasis mine)

essentially something like this:

if count > 0 then
   raise exception 'the room is already occupied at the time';
else
   return new;
end if;

Then define the trigger as:

create trigger TRIG1 before insert
  on room_schedule
  for each row
  execute procedure inserttrigfunc();

  I'm not sure whether triggers are atomic and maybe someone could already make an incorrect decision based on the bad data

Yes they are and they are part of the transaction that fired the trigger.

But you don't need a trigger at all.

This can easily be achieved with an exclusion constraint using the two dates as a daterange

create table room_schedule(
  start_date date,
  start_time time,
  room_no int,
  event_id int,
  duration interval,
  primary key(start_date, start_time, room_no, event_id),
  EXCLUDE USING gist (room_no with =, daterange(start_date, end_date, '[]') WITH &&)
);

Note that you need the extension btree gist for the above because by default a GiST index does not support equality which is needed as the constraint should only apply for the same room_no


Mysql checksum for master-slave replication returning different values, despite being identical

There is better way to check whether the master and it's slaves are in sync.

Run pt-table-checksum on the master.

Then if it shows some tables are different use this script to see where exactly the difference is. http://bazaar.launchpad.net/~akuzminsky/+junk/stats-scripts/view/head:/compare_tables.sh

./compare_tables.sh master_ip slave_ip database table


Is CREATE EXTERNAL DATA SOURCE Azure PaaS elastic databases broken?

You need to create the db scoped credential and the external data source all on the same (destination) database.

The IDENTITY='' should be an account that has access to the source database where you want to query from, in my example Test2.

So for example to access database Test2's tables from database test, execute all this on the database: Test.

USE TEST
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'STRONGP@SSWORD124321';
GO
CREATE DATABASE SCOPED CREDENTIAL CRED_Azure_Login
WITH IDENTITY = 'SomeUser', SECRET = 'STRONGP@SSWORD124321' -- this user needs to  have sufficient rights on the test2 db.
GO

CREATE EXTERNAL DATA SOURCE Source_AzureDatabase
WITH
(
    TYPE=RDBMS,
    LOCATION='<DB location>.database.windows.net',
    DATABASE_NAME='Test2',
    CREDENTIAL= CRED_Azure_Login
);

CREATE EXTERNAL TABLE [dbo].[test]
([Test ID] int)
WITH
(
DATA_SOURCE= Source_AzureDatabase
);

Source


How to keep updates (diffs) of some entity in the database

Check out Drupal and RDF.  Check out what StackExhange (etc) uses.  And study how GIT works.

I recommend keeping the current value in the primary table, and put the history in other table(s).  That is, plan for efficient access to the current value, at the expense of history.

If you are talking about "articles" of, say, 1K characters, then compress them.  Compress old copies; optionally compress the 'current' copy.  But do the compression in the client, so as to offload the database server.  This will save space.  If you are talking about ancillary data (dates, names, etc), don't bother compressing.

Do not save the entire set each time there is a change.  That is, if you change the date, don't store another, identical, copy of the article body.  Too bulky.

You mentioned diffs -- Do you intend to do diffs on paragraphs?  If so, do you have a good algorithms for such?  (My previous comments do not assume "diff".)

Oh, well, I guess I am not adding much to your stated requirements.


try use "with" clase
with employees as (
--- your employee list in select statement
--- for example:
select 1 emp_id
union
select 2
)

select *
from requests rq
join employees em on (em.emp_id = rq.employee)
where start_date > {start_date} and end_date < {end_date}


Selecting 'edge' records in a sequence of data

You can use outer apply the get the lead and lag values and in the column list you can use datediff to see if the gap is something other than one day.

select T1.ID,
       case
         when datediff(day, Lag.Start, T1.Start) = 1 then T1.Start
         else dateadd(hour, 9, T1.Start)
       end as Start,
       case
         when datediff(day, T1.Start, Lead.Start) = 1 then T1.[End]
         else dateadd(hour, 17, T1.Start)
       end as [End]
from YourTable as T1
  outer apply
    (
      select top(1) T2.Start
      from YourTable as T2
      where T2.Start < T1.Start
      order by T2.Start desc
    ) as Lag(Start)
  outer apply
    (
      select top(1) T2.Start
      from YourTable as T2
      where T2.Start > T1.Start
      order by T2.Start
    ) as Lead(Start)

The query above works from SQL Server 2005 even with compatibility SQL Server 2000 (80).

If you find your way to SQL Server 2012 you can use lead and lag instead of simulating with an outer apply.

select T1.ID,
       case
         when datediff(day, lag(T1.Start) over(order by T1.Start), T1.Start) = 1
         then T1.Start
         else dateadd(hour, 9, T1.Start)
       end as Start,
       case
         when datediff(day, T1.Start, lead(T1.Start) over(order by T1.Start)) = 1
         then T1.[End]
         else dateadd(hour, 17, T1.Start)
       end as [End]
from YourTable as T1

SQL Fiddle


Not sure how such a query should be optimized for Oracle, when no indexes are available but here's another rewriting. It will not calculate the number of mismatching tuples but will stop searching when it finds one:

SELECT 1
FROM dual
WHERE EXISTS
     (
        Select
            X, Y, Z
        From
            A
     MINUS
        Select
            X, Y, Z
        From
            B
     ) ;

SQL-Fiddle shows a similar performance for all the provided answers, as the two tables have to be fully scanned in the worst case.

Rolando's and Narendra's queries show different execution time (sometimes better, sometimes worse), I think because of the HASH JOIN used.

There may be different results with bigger tables and with fewer or more matches between the two tables. (the test were done with only a few K rows).


How write mysql query corresponding this tables

You have to use dynamic SQL (PREPARE, EXECUTE) for that

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT(
  'SUM(CASE WHEN DATE_FORMAT(o.date, ''%Y%m'') = ',
  DATE_FORMAT(date, '%Y%m'),
  ' THEN op.count ELSE 0 END) `', DATE_FORMAT(date, '%m/%Y'), '_Count`,',
  'SUM(CASE WHEN DATE_FORMAT(o.date, ''%Y%m'') = ',
  DATE_FORMAT(date, '%Y%m'),
  ' THEN op.count * op.price ELSE 0 END) `', DATE_FORMAT(date, '%m/%Y'), '_Sum`'))
  INTO @sql
  FROM `order`;

SET @sql = CONCAT(
  'SELECT c.name,p.title,', @sql,
  '  FROM `order` o JOIN order_product op
       ON o.id = op.order_id JOIN client c
       ON o.client_id = c.id JOIN product p
       ON op.product_id = p.id
    GROUP BY o.client_id, c.name, op.product_id, p.title');

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

Here is SQLFiddle demo

To simplify things on the calling end you can wrap it up into a stored procedure like this

DELIMITER $$
CREATE PROCEDURE sp_monthly_report()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(DISTINCT CONCAT(
    'SUM(CASE WHEN DATE_FORMAT(o.date, ''%Y%m'') = ',
    DATE_FORMAT(date, '%Y%m'),
    ' THEN op.count ELSE 0 END) `', DATE_FORMAT(date, '%m/%Y'), '_Count`,',
    'SUM(CASE WHEN DATE_FORMAT(o.date, ''%Y%m'') = ',
    DATE_FORMAT(date, '%Y%m'),
    ' THEN op.count * op.price ELSE 0 END) `', DATE_FORMAT(date, '%m/%Y'), '_Sum`'))
    INTO @sql
    FROM `order`;

  SET @sql = CONCAT(
    'SELECT c.name,p.title,', @sql,
    '  FROM `order` o JOIN order_product op
         ON o.id = op.order_id JOIN client c
         ON o.client_id = c.id JOIN product p
         ON op.product_id = p.id
      GROUP BY o.client_id, c.name, op.product_id, p.title');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Sample usage:

CALL sp_monthly_report();

Here is SQLFiddle demo


What's the values for the Freq_Interval column in MSDB.dbo.SysSchedules when Freq_Type is weekly and more than one day is selected in the schedule?

The documentation states

freq_interval is one or more of the following:
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday

These values are all powers of 2.
When you select multiple days simply add the distinct codes.
2 + 4 + 8 + 16 + 32 = 62

or alternatively use the bitwise or operator to combine them.
2 | 4 | 8 | 16 | 32

(my recommendation would be to use | in code and + if adding them up in your head! - | is the "correct" thing to use and won't behave incorrectly if you have duplicate values in the list)
If you are trying to do the reverse operation you need bitwise and
DECLARE @Sunday TINYINT = 1,
        @Monday TINYINT = 2,
        @Tuesday TINYINT = 4,
        @Wednesday TINYINT = 8,
        @Thursday TINYINT = 16,
        @Friday TINYINT = 32,
        @Saturday TINYINT = 64;

DECLARE @TestValue TINYINT = 62;

SELECT CASE WHEN @TestValue & @Sunday = @Sunday THEN 'Y' ELSE 'N' END AS Sunday,
       CASE WHEN @TestValue & @Monday = @Monday THEN 'Y' ELSE 'N' END AS Monday,
       CASE WHEN @TestValue & @Tuesday = @Tuesday THEN 'Y' ELSE 'N' END AS Tuesday,
       CASE WHEN @TestValue & @Wednesday = @Wednesday THEN 'Y' ELSE 'N' END AS Wednesday,
       CASE WHEN @TestValue & @Thursday = @Thursday THEN 'Y' ELSE 'N' END AS Thursday,
       CASE WHEN @TestValue & @Friday = @Friday THEN 'Y' ELSE 'N' END AS Friday,
       CASE WHEN @TestValue & @Saturday = @Saturday THEN 'Y' ELSE 'N' END AS Saturday


How to store values of all columns in an index in PostgreSQL?

Of course, you can create an index which would contain all columns from the rows.

From PostgreSQL 9.2 there is a possibility that this will speed up certain SELECT queries as from that version there are index-only scans.  The difference between the 'raw' table and this index will only show if the query picks only a part of all rows, based on the first column(s) of the index.  A WHERE clause containing conditions only about some later columns will most probably do a sequential scan, that is, it will go to the table itself as the rows to be retrieved will be spread across the whole index.

Also, maintaining such an index is quite expensive when it comes to DML statements - I would say that it doubles the number of writes for every such statement.


Impact of "_oracle_script"=true in Oracle database 18c

Yes, common users, those starting with C##, will be created in the CDB.  They will available in all PDBs (assuming they have the correct permissions).

If you need to create a user in the PDB, make sure you connect to the PDB ( SID=XEPDB ) not the CDB (SID=XE )


I don't think that someone can give you magic numbers like the acceptable number of columns, or information too strictly related to your workload, like if it is a good idea to split the table. There are too many variables: number/types of existing columns and indexes, number of queries, how many columns you read per query, and so on. Proper tests will give you a good answer. All we can say is that, yes, common sense says that such a table should be split if possible.

But then, every query will need to read from multiple partitions? Every new row will have matches in all partitions? This could slow down your application. Ideally, most of the queries should be able to read from only one partition. So the first suggestion would be to check with developers if some queries can be rewritten so that they will read less columns - possibly by rewriting some SELECT *.

You also ask about the best possible match condition. Here the answer is easy: join by primary key. All matching rows should have the same id. It should be an AUTO_INCREMENT columns on only one table. You first insert new rows into the table, then to the others, in this way:

INSERT INTO t1 (...) VALUES (...);
INSERT INTO t2 (id, ...) VALUES (LAST_INSERT_ID(), ...);
INSERT INTO t3 (id, ...) VALUES (LAST_INSERT_ID(), ...);

Also don't add foreign keys, because they will slow down your writes.


Well it's really a matter of you deciding on the architecture you want to go with.

SimpleDB is a non-relational "no-sql" data storage system built around the ideas of collections of items accessible via key/value pairing.

Where as RDS is really just a custom version of MySQL which a fully relational SQL database.

both systems will work for tracking profiles but the implementation patterns are very different. Neither is right or wrong, so it's your call on what you want to do.


Insert performance progressively deteriorating for indexless table

Based on your last comment, it's still unclear what the actual problem is, but I'm going to answer to spell out my recommendations a bit, as I think they could help others in the future.

It's possible that if you try to scale up the existing "working" solution, you'll run into the same problem again.

To improve performance of a series of single-row inserts over a period of time, I recommend switching to a batch-based INSERT strategy by buffering rows on the client. The buffer would be flushed periodically, based on either row count, elapsed time since the last flush, or both.

The recommended way to do inserts in batch for SQL Server is to use the System.Data.SqlClient.SqlBulkCopy class which uses BULK INSERT under the hood.

If you need to stay database-agnostic (or minimize changes to the existing code), a dynamic SQL strategy can be adapted to construct a larger batch of singleton INSERT statements, instead of executing one statement per batch.

This type of strategy is more efficient as it minimizes network roundtrip overhead, allowing the application to scale higher. Note that for either strategy, you'll need to tune the number of rows in a batch to maximize throughput.

It's possible either data or log file growth is still an issue, but I recommend using the above strategy regardless, as the file growth issues should be solved independently of how the application operates.

Ensure there is a suitable amount of free space in both the log file and data file(s) before starting the process.
Set the auto-growth settings to more aggressive values than the defaults (usually a reasonably large, fixed size is appropriate).
Enable instant data file initialization to minimize the impact of data file auto-growth. On my blog, I have a post/video of what this setting does, and how to enable it. Note: this is an instance-wide setting, not a database setting.


how effective creating 2 database with same content?

This is called Write masters with Read slaves. Check out MySQL replication. You'll find an interesting document here (check out the slides) - but you probably won't be needing the level of availability of Booking.com, but hey, who knows? :-).

There are many different replication/HA topologies, but this will give you a start. Also, see here (Andrew Morgan is the author of MySQL Cluster).


Oracle counterpart to `pg_cancel_backend`

Versions below 18c:

DBMS_RESOURCE_MANAGER

SWITCH_CONSUMER_GROUP_FOR_SESS Procedure

exec DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (sid, serial, 'CANCEL_SQL');

  If the group name is 'CANCEL_SQL', the current call will be canceled
  when other switch criteria are met.

From 18c:

ALTER SYSTEM

  cancel_sql_clause

  Use this clause to terminate an SQL operation that is consuming
  excessive resources, including parallel servers. You must provide the
  session id and the session serial number to identify the SQL statement
  that you want to cancel. If you do not provide these required
  arguments, the SQL statement will not run.

  session_id is required and stands for the session identifier.

  serial_number is required and stands for the serial number of the
  session.

  @instance_id is optional. If this argument is omitted, the instance id
  of the current session is used.

  sql_id is optional. If this argument is omitted, the SQL statement
  currently running in the specified session is terminated.

Example:

ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID';


SQL Server 2005, Foreign key check against part of a table

No this isn't possible.

I'd probably create a separate table categoriesWithArticles that holds the relevant categoryids (instead of having the flag) and have the FK reference that table instead.

Another more convoluted way to enforce it and keep the column would be

CREATE TABLE categories
(
categoryId int primary key,
hasarticles bit not null,
unique(categoryId, hasarticles)
)

CREATE TABLE articles
(
categoryId int,
hasarticles as cast (1 as bit) persisted,
FOREIGN KEY (categoryId, hasarticles) REFERENCES categories(categoryId, hasarticles)
)

But this requires the creation of a technically redundant unique constraint as well as the additional persisted column in the articles table.


How to know if mysql server is using sha_256 or caching_sha_256 authentication plugin?

When you have a connection, (ref SHOW CREATE USER),
show create user {user}

will how you the plugin used for any specific user.
SHOW GLOBAL VARIABLES LIKE 'default_authentication_plugin'

will show which authentication is used for created users if none is specified. ref


Postgres how to drop table owned by another user without mirroring that user's Permissions

make the tables be owned by role A and grant role A to role B (or make them owned by role C and grant C to both A and B)


postgres: Database replica with some schemas writable

I'd use Londiste for this. Set up the data collector as a publisher and the analytics DB as a subscriber.

The main issue is that Londiste imposes a significant load due to write-amplification - each write must be written once to the table and once to a trigger-maintained replication queue. So using Londiste for replication may add too much load to an already struggling data collector server.

After comments: No PRIMARY KEY means you're pretty stuffed.

I haven't checked if any of the trigger based replication systems support append-only replication of tables without a PRIMARY KEY but I'd be surprised.

Streaming replication doesn't support replication of only some tables, nor is a replica writeable.

The only solution I know of that'll meet your use case is a unidirectional variant of BDR, which we're calling "UDR", but it's still a few months from being ready.


Recursive self relational via join column

Here is an example query,

WITH RECURSIVE t(item_id, json) AS (
        SELECT item_id, to_jsonb(items)
        FROM items
        WHERE NOT EXISTS (
                SELECT 1
                FROM joins
                WHERE items.item_id = joins.item_id
        )
        UNION ALL
        SELECT parent.item_id, to_jsonb(parent) || jsonb_build_object( 'children', t.json )
        FROM t
        JOIN joins AS j
                ON t.item_id = j.child_id
        JOIN items AS parent
                ON j.item_id = parent.item_id
)
SELECT item_id, jsonb_pretty(json)
FROM t
WHERE item_id = 1;
 item_id |             jsonb_pretty
---------+---------------------------------------
       1 | {                                    +
         |     "title": "PARENT",               +
         |     "item_id": 1,                    +
         |     "children": {                    +
         |         "title": "LEVEL 2",          +
         |         "item_id": 2,                +
         |         "children": {                +
         |             "title": "LEVEL 3.2",    +
         |             "item_id": 6             +
         |         }                            +
         |     }                                +
         | }
       1 | {                                    +
         |     "title": "PARENT",               +
         |     "item_id": 1,                    +
         |     "children": {                    +
         |         "title": "LEVEL 2",          +
         |         "item_id": 2,                +
         |         "children": {                +
         |             "title": "LEVEL 3.1",    +
         |             "item_id": 3,            +
         |             "children": {            +
         |                 "title": "LEVEL 4.1",+
         |                 "item_id": 4         +
         |             }                        +
         |         }                            +
         |     }                                +
         | }
       1 | {                                    +
         |     "title": "PARENT",               +
         |     "item_id": 1,                    +
         |     "children": {                    +
         |         "title": "LEVEL 2",          +
         |         "item_id": 2,                +
         |         "children": {                +
         |             "title": "LEVEL 3.1",    +
         |             "item_id": 3,            +
         |             "children": {            +
         |                 "title": "LEVEL 4.2",+
         |                 "item_id": 5         +
         |             }                        +
         |         }                            +
         |     }                                +
         | }
(3 rows)

Note, we're not actually merging the paths to form a completed tree. You either have to build the tree from the root node down, or the leaf nodes to the top. In this case, you'll have to merge the discrete paths. Look for a deep json merge in Javascript and tie it together with plv8

Test data

I modified your example schema a bit,

DROP TABLE joins;
CREATE TABLE items (
  item_id     serial PRIMARY KEY,
  title text
);
CREATE TABLE joins (
  id          serial PRIMARY KEY,
  item_id     int,
  child_id    int
);
INSERT INTO items (item_id,title) VALUES
  (1,'PARENT'),
  (2,'LEVEL 2'),
  (3,'LEVEL 3.1'),
  (4,'LEVEL 4.1'),
  (5,'LEVEL 4.2'),
  (6,'LEVEL 3.2');
INSERT INTO joins (item_id, child_id) VALUES
  (1,2),
  (2,3),
  (3,4),
  (3,5),
  (2,6);


If I install SQL Server FCIs in Production environment(2 node) can i
  set one secondary to synchronous mode automatic failover and
  DR(Stand-Alone) server to Async mode?

If you use a SQL Server FCI with Availability Groups, you cannot have automatic failover of the AG but you still get automatic failover of the FCI. The FCI will be one instance and the DR secondary will be another (as FCI are logically a single instance).

  Total 3 SQL Servers (Primary Sync,Secondary Sync and Secondary
  Async(Stand-Alone)) is it possible ?

This is possible, but you're going to use the FCI from your first question then there will only ever be 2 instances, so you'd have Primary Sync and secondary async.

  If SQL Server Cluster(2 Instances) is configured in windows 2 node
  cluster,is synchronous mode possible ?

I'm not sure what you mean by SQL Server cluster and windows cluster. If you are talking FCIs, you wouldn't be able to AG between the FCIs because they'd share the same set of nodes.

  Should all the SQL Servers should be stand-alone to configure
  Always-ON synchronous mode and automatic failover?

They don't have to be. It really depends on what you're trying to solve for. Remember that each secondary will have a copy of the database and will need to have the log blocks be transported to it. This may or may not be doable with the infrastructure available.

  Two SQL Servers in Production site,can i set secondary as Synchronous
  mode with automatic failover ?

Assuming neither are in a SQL Server FCI, yes and you can do it on the fly (though it may take time to get in sync).


Does Oracle PL/SQL have a standard ASSERT routine?

There isn't a built-in assertion procedure in SQL or PL/SQL, so you will have to write your own.

There are two ways of going about this. You can either manually raise an exception, as discussed in this Oracle article, or you can write a wrapper for the raise_application_error procedure, which is documented in the Oracle exception handling section of the documentation.

I'll add that exceptions were designed for this kind of scenario, so you'd be better off taking your programmer hat off for a second & using your DBA hat :)


Enable Service Broker when deploying solution

In the latest SSDT version, the EnableBroker option is under Database Settings-->Miscellaneous-->Service broker options.  I would expect SqlPackage to honor this specification.


Get rows only joined to certain types of row in another table

Untested (seemed to be some technical problem with fiddle), but you might be looking for the Exist predicate

SELECT p.*
FROM person as p
WHERE EXISTS (
    SELECT 1
    FROM link as l
    JOIN thing as t
        ON t.objectID = l.thingObjectId
    WHERE t.objectType IN ('fork','knife','eraser')
      AND p.objectID = l.personObjectId
) ;

EDIT:
With info from the comment, it is clear that what you want persons that have all 3 object types (I assumed any). In SQL there is no ALL quantifier (contrary to EXISTS). It is, however, possible to do the transformation:

ALL x:p(x) <=> NOT EXISTS x:NOT x

which as noted by you in the comment would result in:

SELECT p.*
FROM person as p
WHERE NOT EXISTS (
    SELECT 1
    FROM link as l
    JOIN thing as t
        ON t.objectID = l.thingObjectId
    WHERE t.objectType NOT IN ('fork','knife','eraser')
      AND p.objectID = l.personObjectId
) ;

Another option is to count which persons that have exactly 3 objects among the 3:

 SELECT p.rowkey
 FROM person as p
 JOIN link as l
     ON p.objectId = l.personObjectId
 JOIN thing as t
     ON t.objectID = l.thingObjectId
 WHERE t.objectType IN ('fork','knife','eraser')
 GROUP BY p.rowkey
 HAVING COUNT( DISTINCT t.objectType ) = 3;

These are the two most commonly used techniques for relational division that I know of.


Select row from one query where opposites are not present in second query

SELECT  m.id
    FROM  message AS m
    WHERE  NOT EXISTS
      ( SELECT  *
            FROM  inbox AS i
            WHERE  i.message_id = m.id
              AND  i.to_user_id = m.from_user_id
      )

I think it could also be cone with a LEFT JOIN (with similar efficiency), but NOT EXISTS more closely matches your description of the task.


Ensure order when select two rows using two identifiers on the same column

SELECT *
FROM teams
WHERE name IN ($1, $2)
ORDER BY CASE WHEN name = $1 THEN 0 ELSE 1 END;


SQL query with multiple many-to-one joins

Use single quotes instead of double:

SELECT count(*)
FROM   surveys s
JOIN   properties p ON s.property_id = p.id
JOIN   addresses a ON p.address_id = a.id
WHERE  a.city = 'Garrison'


Is it ok if I use only the latest binary log file when restoring MySQL db?

Binlog itself isn't intended for DB restoration. It contains the changes in the DB since some point in time. Bin files are the chunks of the whole log where the max_binlog_size defines how big the single chunk can be.

To perform the restoration in the proper way you need two things:

the dump created by mysqldump utility at the some point of time
all the binlog chunks since DB was dumped

Binlog events can be applied to the some initial state only. They are not idempotent - it can't be applied again and again. Let me illustrate the principle: you want to bake a cake.

The initial state is all the ingredients apart. Then you mix the water, flour, add the yeаst, then you kneed the dough and set it into the owen. All your operations are written to the binlog. If something went wrong you can't start from the arbitrary point. Say, if the cake was burned you can't start from the kneeding of burned cake. You have to revert to the initial state and perform all the steps from the very beginning.

Dump + full binlog allows to restore the final state of DB. Last state + binlog (full or partial) doesn't.


Query is extremely slow after ALTER and deploying of updates

10 times as slow?  And if you ran it again, it would be back to normal?

That is normal.  Before the big ALTER, all the data for that query was cached in RAM.  Then the ALTER bumped most of it out.  After that, the query had to go to disk, hence 10x slower.  Running again should see it in cache and be fast again.

If this does not fit with what you are seeing, please provide the following so we can dig deeper:

SHOW CREATE TABLE for each table
The size of each table
The query
Value of innodb_buffer_pool_size
The ALTER
Are the JOINs 1:1?  If so, there is a probably a significant rewrite to speed it up, even the 1-2 seconds.

Notice that the query must read all of the rows from all 3 tables, and put all the data into a temp table for sorting.

You don't seem to need LEFT, can you remove it?

Possible indexes (pending seeing the above info):

block -- already has PRIMARY KEY(id)?
cache_vatno:  INDEX(vatno)
data_voucher:  INDEX(time, id)

If the joins are 1:1?  Assuming they are, give this rewrite a try:

SELECT  SQL_CALC_FOUND_ROWS dv.id,dv.client_id,
        ( SELECT b.name FROM block b WHERE b.id=dv.block_id ) AS block_name,
        dv.is_ocr_pending,dv.time,dv.label,dv.is_pdf_broken,dv.is_pdf_scan,
        dv.is_pic,dv.file_sha1,dv.file_ext,dv.file_ext_thumb,
        dv.file_size,dv.file_size_original,dv.num_pages,dv.dpi,
        dv.ocr_confidence,dv.ocr_recall_id,dv.ocr_vatno,
        ( SELECT cv.name FROM `cache_vatno` cv
              WHERE cv.vatno=dv.ocr_vatno ) AS ocr_vatno_name,
        dv.ocr_vatno_country,dv.ocr_is_vatno_verified,dv.ocr_invoice_id_,
        dv.ocr_invoice_time,dv.ocr_invoice_time_due,dv.ocr_fi_type,
        dv.ocr_fi_payment_id_,dv.ocr_is_fi_payment_verified, dv.ocr_fi_creditorno,
        dv.ocr_bank_code,dv.ocr_bank_code_id,dv.ocr_is_bank_code_verified,
        dv.ocr_bank_account,dv.ocr_is_bank_account_verified,dv.ocr_bank_iban,
        dv.ocr_is_bank_iban_verified,dv.ocr_bank_swift,dv.ocr_bank_swift_id,
        dv.ocr_is_bank_swift_verified,dv.ocr_products_pattern,
        dv.ocr_total,dv.ocr_is_total_verified,dv.ocr_vat,dv.ocr_currency
    FROM  `data_voucher` dv
    ORDER BY  dv.time DESC,dv.id DESC
    LIMIT  0,25

Rationale:  I turned the JOINs into subqueries, thereby making it obvious that things are 1:1, and letting the Optimizer focus on just the one table (dv).  This should allow it to use INDEX(time, id) to handle both the ORDER BY and the LIMIT, thereby stopping after 25 rows.

Unfortunately, that still leaves the processing of SQL_CALC_FOUND_ROWS.  It must proceed to count the rest of the rows in that index.  So, removing the CALC may speed up the query signficantly.  Does the UI really need it?

...


Does the Oracle database username exist for each Linux account?

Does the Oracle database username exist for every Linux account?

No - Operating System accounts and Oracle database accounts are unrelated.


No, MySQL builds a temporary result set from your query before performing the insert.

  When selecting from and inserting into a table at the same time, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows into the target table.

Source: MySQL Documentation

Edit based on Comments

The insert is an atomic operation and so the data used to build the temporary result set will match the committed data in the source table at the precise moment that the query was executed (i.e. it is a snapshot of that point in time).

After the temporary result set has been built any modifications to the source table will not be reflected in the results being inserted into the target table - unless you run the query again.


PostgreSQL Common Table Expressions vs a temporary table?

There are a few subtle differences, but nothing drastic:

You can add indexes on a temp table;
Temp tables exist for the life of the session (or, if ON COMMIT DROP, transaction), wheras WITH is always scoped strictly to the query;
If a query invokes a function/procedure, it can see the temp table, but it can not see any WITH table-expressions;
A temp table generates VACUUM work on the system catalogs that WITH doesn't, it needs an extra round trip to create/fill it, and it requires extra work in the server's cache management, so it's slightly less efficient.

Overall, you should prefer WITH to temp tables unless you know you will benefit from creating an index.

However, the other option, a subquery in the FROM clause, has a very different set of advantages. It can be inlined, in particular, and qualifiers can be pulled up / pushed down. I wrote about this in a recent blog article.


MySQL: Recover Master from Slave after failover

Before you set it to read_only=0 and point connections to the Slave (new master) run a

show master status;

and take a note with those numbers.

When you recovered your master server you can run

CHANGE MASTER TO MASTER_HOST=myslavehost.example.com, MASTER_LOG_FILE=[take it from your notes],MASTER_LOG_POS=[take it from your notes], MASTER_USER='replication_user', MASTER_PASSWORD='replication_password';
START SLAVE;

Then the old master should start to replicate from the new master and catch up.

A couple of things to keep in mind:

Make sure your expire_logs_days settings is set to more days than your recovery takes otherwise you would lose the binlogs and you need to set up the old master from scratch
Make sure you know the replication user and password. If you don't you can recover it from:

the repository table (in case of master_info_repository = TABLE)

select User_name, User_password from mysql.slave_master_info;
the master.info file (5th and 6th line)

If your master has crashed chances are your slave is a bit behind so you will have some autoincrement conflict after you set the master back to replicate from the slave. pt-table-sync can you remedy that situation.


Why is this select statement not returning what I've provided?

The WHERE clause returns the desired single row AssignSchedule but you have no correlation between that row and the outer Subject and Schedule tables so you get a Cartesian product of all Subject and Schedule combinations.  You could add the outer table correlations to your existing query to get the desired result:

SELECT  Subject.CourseCode
      , Schedule.Room
FROM    Subject
      , Schedule
      , AssignSchedule
WHERE   AssignSchedule.ScheduleID = ( SELECT    Schedule.ScheduleID
                                      FROM      Schedule
                                      WHERE     Room = 'Room1'
                                    )
        AND AssignSchedule.SubjectID = ( SELECT Subject.SubjectID
                                         FROM   Subject
                                         WHERE  CourseCode = 'SubjectA'
                                       )
        AND AssignSchedule.ScheduleID = Schedule.ScheduleID
        AND AssignSchedule.SubjectID = Subject.SubjectID;

A better approach would be use simple equality predicates instead in the ugly subqueries.  Below are examples using the old ANSI SQL-89 syntax you've been using as well as the ANSI SQL-92 equivalent.  I suggest the latter as it helps avoid missing join predicates.

SELECT  Subject.CourseCode
      , Schedule.Room
FROM    Subject
      , Schedule
      , AssignSchedule
WHERE
            AssignSchedule.ScheduleID = Schedule.ScheduleID
        AND AssignSchedule.SubjectID = Subject.SubjectID
        AND Schedule.Room = 'Room1'
        AND Subject.CourseCode = 'SubjectA';

SELECT  Subject.CourseCode
      , Schedule.Room
FROM    Subject
JOIN AssignSchedule ON AssignSchedule.SubjectID = Subject.SubjectID
JOIN Schedule ON AssignSchedule.ScheduleID = Schedule.ScheduleID
WHERE
        Schedule.Room = 'Room1'
    AND Subject.CourseCode = 'SubjectA';


SQL Server: Invalid Log Scan Number (...) passed to log scan in database (...) is not valid

With the help of an outside source, the issue was nailed down to a corrupted transaction log and was fixed by reinitializing replication from scratch.

It was strongly recommended that we evaluate our I/O subsystems that house the log file to make sure there isn't a problem there which caused it.


What are valid connection strings for SSMS login box?

There are various possibilities and I will try to answer them according to the numbered questions.

Valid connection strings for the SSMS login box are:

1st instance

Using the host name

SERVERNAME
SERVERNAME,1433

Using the CNAME/Alias

SERVERNAME-I01
SERVERNAME-I01,1433

Using the IP address

123.1.2.3
123.1.2.3,1433

For the Dedicated Admin Connection (DAC); depends on Browser Service status

a) Browser Service ON

ADMIN:SERVERNAME
ADMIN:SERVERNAME-I01
ADMIN:123.1.2.3

b) Browser Service OFF

SERVERNAME,1434
SERVERNAME-I01,1434
123.1.2.3,1434

2nd instance

Because the 2nd instance has a dynamic port for the SQL Server instance and a dynamic port for DAC, the ports have to be retrieved from the ERROR log file in advance before connecting to the SQL Server. See 7 for more information. In this example I will assume port 63390 is used for the SQL Server instance and port 63389 is used for the Dedicated Admin Connection. Microsoft recommends to fix the IP port, thus allowing you to properly configure a firewall 6.

Using the host name

SERVERNAME\Instance2 (Browser Service ON)
SERVERNAME,63390 (Browser Service OFF)

Using the CNAME\Alias

SERVERNAME-I02 (Browser Service ON)
SERVERNAME-I02,63390 (Browser Service OFF)

Using the IP address

123.1.2.3,63390 (Browser Service is ON or OFF)
Using the IP address without port, would connect you to the default instance

For the Dedicated Admin Connection (DAC); really depends on Browser Service status

a) Browser Service ON

ADMIN:SERVERNAME\Instance2
ADMIN:SERVERNAME-I02\Instance2
ADMIN:123.1.2.3\Instance2
Always requires instance name, because the CNAME or IP address references the same IP address as the default standard instance

b) Browser Service OFF

SERVERNAME,63389
SERVERNAME-I01,63389
123.1.2.3,63389

3rd instance

Using the host name

SERVERNAME\Instance3
Because this instance is using the same SQL Server port as the default instance, but a different IP address, the SERVERNAME,1433 notation would not work. You would be connected to the default instance.

Using the CNAME/Alias

SERVERNAME-I03 (Browser Service ON)
SERVERNAME-I03,1433 (Browser Service OFF)

Using the IP address

123.1.2.4
123.1.2.4,1435

For the Dedicated Admin Connection (DAC); depends on Browser Service status

a) Browser Service ON

ADMIN:SERVERNAME\Instance3
ADMIN:SERVERNAME-I03 (works because the CNAME references a dedicated IP address)
ADMIN:123.1.2.4

b) Browser Service OFF

SERVERNAME,63389 (works because the port references a dedicated port for DAC)
SERVERNAME-I01,63389 (works because the CNAME and port reference a dedicated instance/DAC)
123.1.2.4,63389

Browser Service

If the browser service is DISABLED or has not been started, then connections cannot be routed to the relevant port. In this case the DAC port has to be retrieved from the ERRORLOG file of the instance. You should find a line with something like Dedicated admin connection support was established for listening remotely on port 63389. for the DAC port. This port number is the port the DAC will listen on. You are required to supply the relevant port for the Dedicated Admin connection and the corresponding port for the SQL Server connection if your Browser Service is turned off. You do not have to add the instance name to the connection string when using the port, because the port is reserved for the instance.

In short the Browser Service is responsible for converting instance names to ports. If the Browser Service is not running, then you have to supply the port instead of the instance name.

Reference Material

Logging In to SQL Server (Microsoft | Docs | SQL Server)


What is the best way to manage interdependent indexes

It can looks like

CREATE TABLE indices (firstIndex INT NOT NULL,
                      subIndex INT NOT NULL,
                      PRIMARY KEY (firstIndex, subIndex)
                     );

CREATE TABLE refs (baseIndex INT NOT NULL,
                   relatedIndex INT NOT NULL,
                   PRIMARY KEY (baseIndex, relatedIndex),
                   FOREIGN KEY fk_bi (baseIndex) REFERENCES indices (firstIndex),
                   FOREIGN KEY fk_ri (relatedIndex) REFERENCES indices (firstIndex)
                  );

Update

  why there's a baseIndex + relatedIndex

It is a "link" table (instead of non-relational CSV-field relatedIndexes). The (2,6,8) value for firstIndex=1 in a source scheme is transferred to 3 separate records in refs with (1,2),(1,6),(1,8) value pairs.

  what is Foreign Key

It is constraint references which do not allow to insert into refs an index value which not exists in indices table. And prevents deletion a record from indices while at least one reference exists in refs.


sp_removedbreplication Database name works for me but sometimes when replication is really screwed up I have to remove all replication by using sp_removesrvreplication


Backing up the database is a best practice right before upgrading to any version. You can use mysqldump, Xtrabackup, mydumper .

Replacing MySQL community packages with Percona Server packages should do the job just make sure to execute mysql_upgrade after starting up PS 5.6 the first time.

  Percona Server is a free, fully compatible, enhanced, open source drop-in replacement for MySQL®

Always check the error log after the upgrade.


since which Oracle version this behavior is present?

I have no idea but I expect it's there for a looong time.

is it reliable?

100% reliable

when is the two column index prefered?

When you want to work with null records, too.
select distinct SUBOBJECT_NAME from SCOTT.T; can use the following index
create index SCOTT.IDX_T_SUBOBJ on SCOTT.T(1, subobject_name);

And why isnt it mentioned more often (for example this otherwise good answer does not mention it)?

The whole logic is quite clear once you know that indexes do not contain all-null records and that constraints are not used by the optimizer (as it seems to be the case).
If your index does not contain nulls and you ask for distinct on nullable column, you see, that index cannot be used (nulls are missing). If you select where col is not null, index can be used (all non-null values are there).


You can specify the number of records to return by using TOP

SELECT TOP 10 * FROM table


What you see makes perfect sense given the information available.

SQL Server 2012 Standard Edition is limited to the lesser of 4 sockets or 16 physical cores.

Since you have 40 cores (likely 2 socket 10 core + HT) 16 physical cores is going to be 32 logical cores (because of HT). If you have 40 total, I would EXPECT 8 to be VISIBLE OFFLINE due to licensing.

Features Supported by the Editions of SQL Server 2012 - Cross-Box Scale Limits

Edit: Additional Information

Depending on the OS involved the schedules may be bound to CPUs that are also making an unbalanced NUMA memory configuration for your system. While it is highly unlikely that this will cause you any issues (since it wasn't noticed that all the CPUs were being used in the first place) it could have a much higher impact on a highly used system where the foreign memory access can really add up.


Estimating distribution of row access in mysql

If you want that kind of granular statistics per id, it sounds like you may want to try something a little convoluted to collect such info. Let's explore this scenario:

If you have a table with the following layout in the wp database:

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  `post_password` varchar(20) NOT NULL DEFAULT '',
  `post_name` varchar(200) NOT NULL DEFAULT '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and you want to track access to this table, you must force the recording of the table's id using a series of operations combining the use of Triggers, BLACKHOLE tables, MySQL Replication, and "Socially Responsible" Coding.

TRIGGERS

Triggers follow the course of any INSERT, UPDATE, and DELETE. In this scenario, you will need three(3) types of Triggers: 1) After INSERT, 2) After UPDATE, 3) After DELETE.

Each time wp_posts has an  INSERT, UPDATE or DELETE, record the ID in another table. What kind of table ???

BLACKHOLE TABLES

Let's create a table for recording IDs

CREATE TABLE wp_posts_idtracker
(
  `ID` bigint(20) unsigned NOT NULL,
  `DTSTAMP` datetime NOT NULL
) ENGINE=BLACKHOLE;

Now here are triggers to record the ID accesses:

CREATE TRIGGER wp_posts_insertafter AFTER INSERT ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (NEW.ID,NOW());

CREATE TRIGGER wp_posts_updateafter AFTER UPDATE ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (OLD.ID,NOW());

CREATE TRIGGER wp_posts_deleteafter AFTER DELETE ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (OLD.ID,NOW());

WAIT A MINUTE !!! The table wp_posts_idtracker is a BLACKHOLE table. It stores nothing. So where do the statistics get written ??? Make sure the MySQL Instance with wp_posts has binary logging enabled. Big deal, the stats get written to the binary logs. How can you read those stats ???

MySQL REPLICATION

Using commodity hardware, employee the use of a MySQL Replication Slave. Setup the slave to only accept one table: wp_posts_idtracker. Place this line in /etc/my.cnf in the slave:

[mysqld]
replicate-do-table=wp.wp_posts_idtracker

Since this BLACKHOLE table would be replicated over to the slave, how will it store data ??? Convert it to MyISAM on the slave. Also, index it by ID and DTSTAMP and by DTSTAMP:

use wp
ALTER TABLE wp_posts_idtracker ENGINE=MyISAM;
ALTER TABLE wp_posts_idtracker ADD INDEX (ID,DTSTAMP);
ALTER TABLE wp_posts_idtracker ADD INDEX (DTSTAMP);

Now the Master will simply record every access to a row in wp_posts into the binary logs. MySQL Replicaton takes responsibility to record that over to the Slave. As an alternative to using a separate server for recording this information, you may want to create a second instance of MySQL on port 3307 and have it act as slave of MySQL running on port 3306. You must make sure that the datadir of MySQL on port 3307 is on a separate data volume from that of MySQL on port 3306. Another variation would be to alter the storage engine for wp_posts_idtracker on the slave with the MEMORY storage engine to reduce disk I/O (Caution: If you go with the MEMORY storage engine fro the table wp_posts_idtracker, remember to make the indexes BTREE instead of the default HASH index because running range queries against a HASH-indexed table has horrible performance, even for a MEMORY table). Still another variation would be to place the binary logs in a RAM Disk for even faster replication or placing the relay logs in a RAM Disk as well, along with further reducing disk I/O.

Thus far, IDs involved in INSERTs, UPDATEs and  DELETEs are stored safely in a Replication Slave. Are we forgetting any other types of access to wp_posts ??? Oh yes, SELECT statements. How do we record SELECTs ??? There are no triggers for SELECTs in any known RDBMS. How do we handle SELECTs ???

"SOCIALLY RESPONSIBLE" CODING

Since there are no special mechanisms for SELECTs, the developer must take personal responsibilitity to get the IDs that are encountered in SELECT queries and simply INSERT them into the table wp.wp_posts_idtracker. Suppose the ID you are requesting comes from a bulk gathering of IDs. Send them in bulk into wp.wp_posts_idtracker:

SELECT * FROM wp_posts WHERE ...
INSERT INTO wp.wp_posts_idtracker SELECT IDs,NOW() FROM wp_posts WHERE ...

Don't worry about the INSERT query causing Disk I/O accessing the IDs. Those IDs should be cached following the SELECT. If wp_posts is MyISAM, keys would be cached in the key cache (sized by key_buffer_size). If wp_posts is InnoDB, keys would be cached in the innodb buffer pool (sized by innodb_buffer_pool_size).

SUMMARY

Once you employ this rather unique infrastructure, you can simply connect to the slave and read your statistics.

This was just an example of how to create the type of stats recording you want for a table's ID. You may have other ideas in mind. Do not be afraid to try them. Always make sure you know the consequences of each milestone you are trying achieve when recording stats.

Give it a Try !!!

I TOLD YOU THIS WOULD BE CONVOLUTED !!!


How to insert multiple values in a specific field in a table using mysqli?

No.  Don't put multiple values in a column; make a new table:

Mapping table
-id -- same as the `id` in transaction_tbl
-ref_number
PRIMARY KEY(id, ref_number)

For your example, there will be 3 rows in this table for the 1 row in transaction_tbl.

INSERT INTO transaction_tbl (name, descripton) VALUES (..., ...)
$id = LAST_INSERT_ID()
INSERT INTO mapping (id, ref_number) VALUE ($id, '10359435846')
INSERT INTO mapping (id, ref_number) VALUE ($id, '05438547656')
INSERT INTO mapping (id, ref_number) VALUE ($id, '035848576')

One way of getting the output:

SELECT t.id, t.name, t.description,
       GROUP_CONCAT(m.ref_number) AS ref_numbers
    FROM transaction_tbl t
    JOIN mapping m  ON m.id = t.id
    GROUP BY t.id;


You may want to have a look on that document http://docs.mongodb.org/manual/reference/database-references/ . In general join is not a good practice for mongodb since it does not automatically supported. You might need to nest some author attributes - if not all information - that commonly used by your find queries on the blog collection for faster lookup.


Basic ERD question: how many entities to use

Generally in these cases its best to have a single entity with flags for which type the person is. In many cases as your system is used, you will likely find someone can be any combination of these. And if they update their name/address/username/password, you want it updated for ALL of their roles simultaneously. This lends to using a single entity with flags for their types:

Person
-------
PersonID
FirstName
Surname
...
AddressLine1
AddressLine2
...
Username
Password
...
Seller Boolean
Buyer Boolean
Reviewer Boolean

EDIT:

Alternatively, as suggested by John M and depending on if you are going to create more Person Roles, you could have 2 entities with a lookup between them identifying the type of roles a person is able to perform:

Person
-------
PersonID
FirstName
Surname
...
AddressLine1
AddressLine2
...
Username
Password

A Role table

Role
------------
RoleID
RoleDescription

(this table would contain Seller/Reviewer/Buyer to start with)

Then this link table:

PersonRole
------------
PersonID
RoleID

This would enable you to add Roles easily in the future without modifying your Person table. So if you created a Role such as "Editor", you would only need to create the row in the Role table and Inserts for the people you want to add as Editors into the PersonRole table.


Calculate 12 months rolling / moving average, median, min, max, percentiles, etc. as single query in Postgres

Generic code for calculating 1,2,3,4,..6,...12 years / quarters / months / weeks / days /hours moving average, median, percentiles, etc. summary stats where table contains a list of individual time records (like sales transactions,etc)

WITH grid AS (
      SELECT end_time, start_time
      FROM (

            SELECT end_time
          , lag(end_time, 12, 'infinity') OVER (ORDER BY end_time) AS start_time
            FROM (

                SELECT
                generate_series(date_trunc('month', min(time2))
              , date_trunc('month', max(time2)) + interval '1 month', interval '1 month') AS end_time
                FROM   my_table

                ) sub

           ) sub2

      WHERE end_time > start_time

)

SELECT
    to_char(date_trunc('month',a.end_time - interval '1 month'), 'YYYY-MM') as d
  , count(e.time2)
  , percentile_cont(0.25) within group (order by e.price) as Q1
  , percentile_cont(0.5) within group (order by e.price) as median
  , percentile_cont(0.75) within group (order by e.price) as Q3
  , avg(e.price) as Aver
  , min(e.price) as Mi
  , max(e.price) as Mx

FROM grid a

LEFT JOIN my_table e ON e.time2 >= a.start_time

                   AND e.time2 <  a.end_time

GROUP  BY end_time
ORDER  BY d DESC

The answer looks 'simple' but it is very disappointing nobody was prepared to lend a hand with some code snippets to put me on the right track...

A note about time:

. The first section of the scrip generates a list of start-end time ranges to run queries for.

. The second part calculates the stats and outputs the results for each interval of time (eg. for "12 months median" the time shown in the first column will be "12 months to the end of the given month")

. The convention in PosgreSQL is that "end of the month" is actually "0 hour" of the next month (ie. end of Oct 2019 is "2019.11.01 at 00:00:00"). Same applies for any time range (eg. end of 2019 is actually "2020.01.01 at 00:00:00").

. So, for display purposes you may wish use the following:

 to_char(date_trunc('month',a.end_time - interval '1 month'), 'YYYY-MM') as d

Else, if you don't include "- interval '1 month' ", the 12 months moving stats ending October 2019 will be shown as "for" 1st November 2019 at 00:00:00 (trunkaded to 2019-11).


How to monitor the performance of a mysql database during development?

My favorite monitoring tool is the slowlog, with long_query_time=1.  And use pt-query-digest to summarize the results.  Then work on improving the first few queries in the list.  They will, by default, be ordered with the "worst" queries first -- either slow but infrequent or fast but frequent (or both).

Digging into why a particular query is slow (or frequent) may lead to a missing index, the need for a 'composite' index, reformulation of a query, redesign of the code, tuning, more RAM, or any of a dozen other things.

More here


When you switch Table A to Table B, does the index data get switched as well?

does the index data get switched as well?

Yes. It would be bizarre if it didn't because then queries would return the wrong results or we would have to manually rebuild the indexes after switching.

  I don't know how to verify this

One way would be to just try it

CREATE TABLE [dbo].[GroupEvent]
  (
     GroupName VARCHAR(100) INDEX IX_NC_GroupEvent_staging_GroupName,
     Created   DATETIME INDEX IX_NC_GroupEvent_staging_Created
  );

CREATE TABLE [dbo].[GroupEvent_staging]
  (
     GroupName VARCHAR(100) INDEX IX_NC_GroupEvent_staging_GroupName,
     Created   DATETIME INDEX IX_NC_GroupEvent_staging_Created
  );

INSERT INTO [dbo].[GroupEvent_staging]
VALUES      ('Group1',GETDATE()),
            ('Group2',GETDATE());

ALTER INDEX IX_NC_GroupEvent_staging_GroupName ON [dbo].[GroupEvent_staging] REBUILD;

ALTER INDEX IX_NC_GroupEvent_staging_Created ON [dbo].[GroupEvent_staging] REBUILD;

SELECT index_id,
       allocated_page_file_id,
       allocated_page_page_id
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('[dbo].[GroupEvent_staging]'), NULL, NULL, 'DETAILED')
WHERE  is_allocated = 1;

-- Empty production table
TRUNCATE TABLE [dbo].[GroupEvent];

-- Switch data from staging-table into production table
ALTER TABLE [dbo].[GroupEvent_staging] SWITCH TO [dbo].[GroupEvent];

SELECT index_id,
       allocated_page_file_id,
       allocated_page_page_id
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('[dbo].[GroupEvent]'), NULL, NULL, 'DETAILED')
WHERE  is_allocated = 1;

SELECT GroupName
FROM   [dbo].[GroupEvent];

DROP TABLE [dbo].[GroupEvent], [dbo].[GroupEvent_staging];

The only object accessed in the process of returning the two rows was the index showing that the data must have been switched.

The above also compares the result from sys.dm_db_database_page_allocations for GroupEvent_Staging before the switch with a similar query for GroupEvent after the switch to see that the pages remain the same for both the heap itself (index_id=0) and the two nonclustered indexes (ids 2 & 3). This shows the switch was metadata only with ownership of the allocated pages transferred.


You seem to have an issue with key distribution with id and the Query Optimizer.
Since the id is not being factored into the partitioning scheme, I only see MySQL having to look up the id up to 6 times. Depending on when and how frequent each id value is written, the query could be using the wrong index.
There are two things you could do to see if id key distribution is at issue
SUGGESTION #1 : Run EXPLAIN on each query
explain select *
from table
where id='25885'
and clock>1476044179
order by clock desc
limit 3;
explain select *
from table
where id='24946'
and clock>1476044179
order by clock desc
limit 3;

The output will tell you what index was chosen to scan for rows. If the clock index is being used, then the clock is being scanned in the index, but the id is being scanned from the table.
SUGGESTION #2 : Change the ORDER BY
If the order by clock desc is making the Query Optimizer choose the clock index, try this:
order by id,clock desc

That way, the scan will be from table_1 index, starting from the last time anid was inserted.
GIVE IT A TRY !!!


Group by + Order by Behavior Explanation?

The order of execution for SQL query is

FROM & JOIN -determine table & filter rows
WHERE -more filters on the rows
GROUP BY -Combine rows in group
HAVING - filters on group
ORDER BY - arrange the remaining rows

  Does group by get a random record from the table ?

Answer is No.

  Is Order by working first and then Group by gets the first record?

Answer is No.

How the query execution happen will tell us the reason,
In the query that you have added, FROM provider,piece,provides is executed first, then WHERE provider._id = provides.ProviderId
AND piece._id = provides.PieceId
AND provider.Name LIKE 'A%'
after that GROUP BY followed by ORDER BY clause.

EDIT:

Check Sqlite select for more details.


Performance gain with MultiSubnetFailover for single subnet

When MultiSubnetFailover=True is set, SQL Server Native Client will aggressively retry the TCP connection.

So in

a multi-subnet scenario, it will attempt connections in parallel, which you already know.
a single subnet scenario, it will aggressively retry the TCP connection faster than the OS default TCP retransmit interval, so your reconnect time after an AG failover will be improved.


SP2-0667: Message file sp1.msb not found sqlplus oracle

I found the solution ! As the oracle documentation said about ORACLE_HOME, My env variables was setted in the /etc/profilefile, but not visible from env | grep ORACLE_HOME or dbhomeso I executed the following command :

export ORACLE_HOME=/app/oracle/product/12.1.0/dbhome_1

and now it works


I would use Excel 2013 - it has fairly easy Map integration which can take Lat+Lon, eg:

http://www.sqljason.com/2013/03/geospatial-analytics-microsoft-bi-john.html

I would pull a SQL view through PowerPivot for easy derivation of .Lat and .Long, and for refreshing the data.

Alternatively SQL Server Reporting Services 2008 R2 or 2012 has similar Map integration, but the end user experience is static.

http://msdn.microsoft.com/en-AU/library/ee240845.aspx


Recommended Data profiling tool for BI developer

Your questions are quite broad, so this is difficult to answer definitively.  However, I'll give an overview of the field.

Data profiling is the process of analysing data to understand the semantics of the data and identify data quality issues that may need to be resolved.  Things that data profiling will address include:

Meanings of various reference data tables or coding schemes in the data.  This will almost always be necessary to understand what business logic will be necessary to implement calculations in the ETL processing.
Content of various data items in the source.  This may not be documented sufficiently well that the location or origin is immediately obvious.
Cardinality and relationships of the data.  In some cases foreign keys may not be present in the system, or not a feature of the data source (for example ISAM/VSAM files from older mainframe systems).
Data issues where the data is incorrect and needs to be fixed at source.
Reconciliation processes where the results of prototyping, ETL processing or other work need to be reconciled to the data source or some other control.

Often the data can be profiled by just poking around it with a database query tool such as SSMS and writing SQL queries directly against the source, or a copy of the data loaded into a staging or scratch area.  Desktop tools such as spreadsheets (pivot tables can be useful) or database systems such as MS-Access may also be helpful.

Purpose built data profiling tools such as Pandora X88 are also available, but these tend to be relatively expensive.  Often they are quite a hard sell, even on larger enterprise projects where they represent a tiny fraction of the overall budget.

Often your best approach is to simply copy all your source data into a staging database and poke around it.  This work can also form the basis of your staqging processes later on.


Databses design, parallel many-to-many relationships. Two tables, or extra fields?

If both fields are relevant in one record (i.e. "used by" and "paid for" can both have a person_id from the persons table), a single table is a better solution. If at least one of the two fields is always NULL, splitting the table would actually make the SQL less complex.
(Also, if an expense is either "paid for" OR "used by" but not both, you could instead use an expense_type field instead of 2 separate fields).

As a sidenote, I would recommend against using space in field names.


Using the same table twice in a select returns duplicate rows

You could use a subquery

SELECT DB_NAME() as DBName,
       SCHEMA_NAME(t.schema_id) as SchemaName,
       t.name as TaskTable,
       (SELECT t2.Name from sys.tables t2 where t2.name in ('TaskTimeRecord_NEW','TaskTimeRecord') and t2.schema_id = t.schema_id) as TimeRecordTable
FROM sys.tables t
WHERE  t.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW');

However, if there is more than one tablename returned by the subquery, the query will fail with the following error:

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression.

If more than one tablename could be returned

If the subquery is an issue due to multiple table names, you could create a comma separated string with FOR XML PATH('').

SELECT DB_NAME() as DBName,
       SCHEMA_NAME(t.schema_id) as SchemaName,
       t.name as TaskTable,
       stuff(((SELECT ', '+ t2.Name from sys.tables t2 where t2.name in ('TaskTimeRecord_NEW','TaskTimeRecord') and t2.schema_id = t.schema_id for xml path(''))), 1, 2, '') as TimeRecordTable
FROM sys.tables t
WHERE  t.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW');

If both the subquery and the main query could return multiple tablenames

And you still need one row per schema, you could create two comma separated strings

SELECT DISTINCT DB_NAME() as DBName,
       SCHEMA_NAME(t.schema_id) as SchemaName,
       stuff(((SELECT ', '+ t2.Name from sys.tables t2 where t2.name in ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW') and t2.schema_id = t.schema_id for xml path(''))), 1, 2, '') as TaskTable,
       stuff(((SELECT ', '+ t3.Name from sys.tables t3 where t3.name in ('TaskTimeRecord_NEW','TaskTimeRecord') and t3.schema_id = t.schema_id for xml path(''))), 1, 2, '') as TimeRecordTable
FROM sys.tables t
WHERE  t.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW','TaskTimeRecord_NEW','TaskTimeRecord');


Oracle - abort within a "before insert" trigger without throwing an exception

The only way for a trigger on a table to prevent an INSERT operation from completing is to throw an error.

It is, as you have stated, a huge hack, but you could

Rename the table
Create a view that has the same name as the original table
Create an instead of trigger on that view that only does an INSERT on the table if you want the row to be persisted.  Otherwise, the trigger would do nothing.

That should work so long as the application isn't doing a MERGE into the table.  Of course, it's a hack upon a hack so it's definitely not going to win any awards for clean code.


Including the substring "_id" in column names

This is a matter of style, and likely to get religious.  Personally, I do find it valuable to explicitly label ID fields TeamID and PlayerID; as you say, it makes it perfectly clear that the field stores an ID and not some other type of data, such as a label.  Being explicit is usually more valuable than being brief, in the long term.

I prefer using PlayerID, TeamID, etc. over just ID, but offhand I can't give you a compelling reason.  It means that your joins are consistently X to X (P.TeamID = T.TeamID), whereas P.ID and T.ID are very different fields which should not be joined.  It's easier to confuse P.ID and T.ID when glancing through a query than P.PlayerID and T.TeamID.  On the other hand, there's value in consistently using the same field name for your PK, too.


It is technically possible, yes. But you may be able to leverage performance gains by having multiple files on multiple disks as opposed to just one file. Why not try using one file and see if it works, and what the performance is like?

Here is a link for info on BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx


Why Does UNPIVOT Work on Compatibility Level 80 DB?

The answer is in the error message:

  You may need to set the compatibility level of the current database to
  a higher value to enable this feature.

Where 'current database' means the context database - i.e. the database the query is executed in, which is not necessarily the same as the database referenced in the query. For example, the following produces the error message:

USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks
SET COMPATIBILITY_LEVEL = 80;
GO
SELECT
    U.SystemInformationID,
    U.[Database Version],
    U.ColumnName,
    U.Value
FROM AdventureWorks.dbo.AWBuildVersion AS ABV
UNPIVOT
(
    Value
    FOR ColumnName IN
        (
        VersionDate,
        ModifiedDate
        )
) AS U;

The following executes successfully (assuming tempdb has a compatibility level > 80):

USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks
SET COMPATIBILITY_LEVEL = 80;
GO
USE tempdb; -- Context database
GO
SELECT
    U.SystemInformationID,
    U.[Database Version],
    U.ColumnName,
    U.Value
FROM AdventureWorks.dbo.AWBuildVersion AS ABV -- Still references data in AW DB
UNPIVOT
(
    Value
    FOR ColumnName IN
        (
        VersionDate,
        ModifiedDate
        )
) AS U;

You need to check what the context database is for the connection that produces the error.


Count distinct instances of a SKU and count all items in all instances containing that SKU

You can use a window function with the COUNT() function in a CTE or subquery to get the total Line_Item_ID count per Sales_Order_Id or even just group it separately at that point, and re-join it to your existing query with a SUM() on top of it to get the total Line_Item_ID for each SKU like so:
WITH CTE_LineItemsPerSalesOrder AS
(
    SELECT sales_order_id, COUNT(1) AS LineItemsPerSalesOrderCount
    FROM Table
    GROUP BY sales_order_id
)

SELECT T.SKU, COUNT(DISTINCT(T.sales_order_id) as Order_Count,
    SUM(C.LineItemsPerSalesOrderCount) AS Line_Item_Count
FROM Table T
INNER JOIN CTE_LineItemsPerSalesOrder AS C
    ON T.sales_order_id = C.sales_order_id
GROUP BY T.SKU
ORDER BY T.SKU

Feel free to rename and reformat the casing of the syntax I used, as needed.


MySQL replication - invalidating query cache entries (table)

Was query_cache_type OFF (0) in my.cnf, not merely turned off later?  (This seems to be critical for having it really off.)
Another possible common thread in the bugs -- MyISAM.  If you are using MyISAM, you really should convert to InnoDB.  There are many reasons for converting.  Here are some tips on doing the conversion:  http://mysql.rjweb.org/doc.php/myisam2innodb
Make sure that query_cache_wlock_invalidate is OFF.


How to connect to Oracle database on VM1 from host with 11gClient

It depends on the VM software you are using. In case of VirtualBox you have to check what type of connection you set (NAT, bridge, etc); if using NAT you need to enable a port-forward to the listener port: e.g. TCP 21521 on host, TCP 1521 on VM).

Also check if iptables firewall is enabled on your VM.


How can I make an index covering a ts_vector expression and also an integer?

With the help of the btree_gin extension, you can include an int column into your index, but doing so will not offer you an advantage.  The way gin indexes work, it can't be used for ordering.
You should write your query first, and worry about optimizing once you have something to optimize.


SQL Server tuning: Disk queue to OS drive: How to optimize?

Use Microsoft Performance Analyzer or Media Experiance Analyzer with Windows Performance Recorder. Any of these is capable of answering your question in great detail, quickly and foul-proof, using system ETW capabilities.

This article is a good walk through: Analyzing Storage Performance using the Windows Performance Analysis ToolKit.

At the simplest, it will be something like this:

c:\>Xperf -on FileIO+Latency+DISK_IO+DISK_IO_INIT+SPLIT_IO
 # repro your scenario
c:\>Xperf -d repro.etl
c:\>Xperf repro.etl

Use the Hard Faults view to track paging spikes, use Disk Utilization to correlate paging and disk IO, use Disk Utilization per Process to identify disk users.

You can take it one step further with Windows Performance Analyzer, WPA can open the same .etl file Xperf Viewer can, but it has much nicer graphs and intelligence built-in.


Can I determine if a stored procedure uses dynamic sql without parsing the definition?

No. There is no flag or metadata about "UsesDynamicSQL"

You have to search the definition...

There are 2 ways to execute dynamic SQL

sp_executesql
EXEC (.. or EXECUTE ('

You can search for the first in sys.sql_modules, the 2nd using LIKE

WHERE
   REPLACE(definition, ' ', '') LIKE '%EXEC(%'
   OR
   REPLACE(definition, ' ', '') LIKE '%EXECUTE(%'
   OR
   definition LIKE '%sp[_]executesql%'


Full Transaction Log on closed source system

You can try to use some third party tools for transaction log reading, ApexSQL, Idera, RedGate. You can also try to determinate by using fn_dblog function, with all that executions you should be able to easily see the problematic statement:

SELECT [Current LSN], Operation, Context, [Transaction ID], [Begin time] FROM sys.fn_dblog (NULL, NULL)


Without more specific requirements, this is probably where I'd start:

CREATE TABLE dbo.MonthDimension
(
  StartDate date NOT NULL,
  EndDate   AS (CONVERT(date,     DATEADD(DAY,-1,DATEADD(MONTH,1,StartDate))))
               PERSISTED,
  NumDays   AS (CONVERT(tinyint,  DATEDIFF(DAY,StartDate,DATEADD(MONTH,1,StartDate)))),
  Y         AS (CONVERT(smallint, DATEPART(YEAR,StartDate))),
  M         AS (CONVERT(tinyint,  DATEPART(MONTH,StartDate))),
  Q         AS (CONVERT(tinyint,  DATEPART(QUARTER,StartDate))),
  MonthName AS (CONVERT(varchar(20), DATENAME(MONTH,StartDate))),
  CONSTRAINT PK_MonthDimension PRIMARY KEY (StartDate, EndDate)
);

Then to populate it, just substitute your desired start and end dates:

DECLARE @start date = '20000101',
        @end   date = '20991231';

;WITH x(n) AS
(
  SELECT 0 UNION ALL
  SELECT n+1 FROM x
  WHERE n < DATEDIFF(MONTH, @start, @end)
)
INSERT dbo.MonthDimension(StartDate)
  SELECT DATEADD(MONTH,n,@start) FROM x
  OPTION (MAXRECURSION 32767);

Partial table data:

StartDate   EndDate     NumDays  Y      M   Q   MonthName
----------  ----------  -------  ----   --  --  ---------
2000-01-01  2000-01-31  31       2000   1   1   January
2000-02-01  2000-02-29  29       2000   2   1   February
2000-03-01  2000-03-31  31       2000   3   1   March
2000-04-01  2000-04-30  30       2000   4   2   April
2000-05-01  2000-05-31  31       2000   5   2   May
2000-06-01  2000-06-30  30       2000   6   2   June
2000-07-01  2000-07-31  31       2000   7   3   July
2000-08-01  2000-08-31  31       2000   8   3   August
2000-09-01  2000-09-30  30       2000   9   3   September
2000-10-01  2000-10-31  31       2000   10  4   October
2000-11-01  2000-11-30  30       2000   11  4   November
2000-12-01  2000-12-31  31       2000   12  4   December
2001-01-01  2001-01-31  31       2001   1   1   January
2001-02-01  2001-02-28  28       2001   2   1   February
2001-03-01  2001-03-31  31       2001   3   1   March

... 1,170 rows in the middle ...

2098-10-01  2098-10-31  31       2098   10  4   October
2098-11-01  2098-11-30  30       2098   11  4   November
2098-12-01  2098-12-31  31       2098   12  4   December
2099-01-01  2099-01-31  31       2099   1   1   January
2099-02-01  2099-02-28  28       2099   2   1   February
2099-03-01  2099-03-31  31       2099   3   1   March
2099-04-01  2099-04-30  30       2099   4   2   April
2099-05-01  2099-05-31  31       2099   5   2   May
2099-06-01  2099-06-30  30       2099   6   2   June
2099-07-01  2099-07-31  31       2099   7   3   July
2099-08-01  2099-08-31  31       2099   8   3   August
2099-09-01  2099-09-30  30       2099   9   3   September
2099-10-01  2099-10-31  31       2099   10  4   October
2099-11-01  2099-11-30  30       2099   11  4   November
2099-12-01  2099-12-31  31       2099   12  4   December


Identity Column Behaviour After Replication

After switching to a different master you must reseed the identity value, otherwise you will get duplicates in the id column. Use the DBCC CHECKIDENT statement.

DBCC CHECKIDENT ( 'dbo.ITest', reseed, 1000 ) ;

Choosing the number could be automated by using a SELECT MAX( TID ) query and placing the returned number into a dynamic SQL string. This must be placed in a serializable transaction to make sure no other sessions insert new rows while this session is reseeding the value (thanks David). Also, the master must not be changed while reseeding is taking place.

One way to deal with this might be to create non-overlapping identity domains. On ServerA, create the identity column as identity ( 1, 2 ) and on ServerB as identity (2, 2 ). That way ServerA's inserts will be 1, 3, 5… and  ServerB's inserts will be 2, 4, 6… Of course, if you ever want to create a third server then you are hosed. :-)

Better still, don't use identity in a multi-master situation. It provides no prevention of duplicates. Use a uniqueidentifier or an application-generated identifier instead.


It has nothing to do with SCH-S. Select queries which acquire S and IS locks are compatible with SCH-S, which is called Schema Stability and all it does, it prevents table during query execution from being modified which means SCH-S and SCH-M are not compatible. SCH-S is compatible with all other locks.

Your problem however is that you have auto update stats enabled, which are triggered whenever there is certain amount of data changed within a table (20% pre SQL2016 or with 2371 traceflag counts % depending on number of rows) and it does when you execute the query.

So what might have happened is that your table was pretty busy and query optimizer decided it should update statistics, so you had to wait.

You should disable auto update statistics and update it manually


This is because the owner of the database ( the login ) was deleted. You should remap the owner of the database, to a login that exists.

ALTER AUTHORIZATION
ON DATABASE::[DatabaseName]
TO [A Suitable Login];

Dont use sp_changedbowner. it's a deprecated feature.


Kerne module for Oracle 11gR2 RAC on Linux

Without knowing what kernel you're running, I can't say for sure, but my guess is that you're running a recent UEK kernel, and it has the kernel module built in.


Window function IGNORE NULLS workaround for PostgreSQL

LAG() doesn't repeat the last non null value.

Quoted from docs

  returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value)

But you can set a partition depending on one column value and then use firts_value() function.

create table tbl (id int, a int, b int, c int);

✓

insert into tbl values
(1, 12, 4, 3),
(2, 10, 10, 5),
(3, 6, 12, 23),
(4, 6, null, 10),
(5, 7, null, 4),
(6, 1, 8, 10),
(7, 4, null, 3);

7 rows affected

select id, a, first_value(b) over (partition by grp) as b, c
from (
      select id, a, b, c,
             sum(case when b is not null then 1 end) over (order by id) as grp
      from   tbl
     ) t

id |  a |  b |  c
-: | -: | -: | -:
 1 | 12 |  4 |  3
 2 | 10 | 10 |  5
 3 |  6 | 12 | 23
 4 |  6 | 12 | 10
 5 |  7 | 12 |  4
 6 |  1 |  8 | 10
 7 |  4 |  8 |  3

db<>fiddle here


I think you are looking for DBs that are designed for analytics.
May be you should check these solutions:.
some data storage solutions specific to realtime analytics

Druid
Spark
Cassandra
Hbase
hypertable
accumulo

reference:

http://kkovacs.eu/cassandra-vs-mongodb-vs-couchdb-vs-redis
http://relistan.com/cassandra-vs-mongo/


MySQL Error: “Table storage engine for `proc` doesn't have this option (ERROR 1031)”

The error you got is often seen when you try to use a table option that is incompatible with the storage engine used by the table you're working on.

For example,

CREATE TABLE mytable (...) ROW_FORMAT=FIXED;

This table option was deprecated in MySQL 5.7. It will raise an error if innodb_strict_mode is enabled.

You should always run mysql_upgrade after installing an upgrade of MySQL (it says so in https://dev.mysql.com/doc/refman/5.7/en/upgrading.html). Sometimes a new version introduces a change to system tables, and you should apply those changes after you update the software.

I'm guessing you have not run mysql_upgrade in a while, so the dump produced from your previous installation may have still used system tables in the format of some earlier version of MySQL.

So it wouldn't hurt to run mysql_upgrade before dumping the system tables before you upgrade.