The JSON notation definition follows the following schema:

The definition of the string is the following:

You can see that the quotes are mandatory both at the beginning and at the end.

The definition of the value is the following:

Note that here you can either supply a string or a number, the number being:

Conclusions:

Keys must have quotes both at the beginning and at the end.
Quotes can be avoided on the value end if you are supplying numbers.

I can't answer why JSON adopted this particular schema and an response here might be opinion based.

SQL Server will always get a performance increases when treating integers over string data types (like VARCHAR or NVARCHAR) since they are faster to operate with and compare, but make sure the data type is actually a numeric type and not a number stored as a string.


What are the canonical NoSQL resources for people who never used this technology?

This is a tough question in the sense that there are several NoSQL databases out there, and they're all slightly (sometimes radically) different from each other.

An important concept to understand for NoSQL technology is that of Brewer's CAP Theorem, so I've provided a link to a good article on it.  For a brief, graphical overview on the CAP Theorem, you should also look at Nathan Hurst's blog, a Visual Guide to NoSQL Systems.

Personally, I have experience with MongoDB (school) and Cassandra (work).  Now that I've used Cassandra for a few years, there are a few good resources that I can recommend:

Cassandra High Availability by Robbie Strickland
Webinar - Getting Started with Apache Cassandra by Rebecca Mills
About Apache Cassandra - DataStax's documentation

As for MongoDB, I have read several books on the subject and here are two (that I think) are really good:

MongoDB In Action by Kyle Banker
MongoDB: The Definitive Guide by Kristina Chadrow


How Do I Filter a Group of Rows based on one Column's Value in TSQL?

Do you really need the GROUP BY, are those rows not distinct?

You can add a conditional CASE to find rows with 'Overtime Pay'

WITH RESULTS AS
(
SELECT
      [FullName]
  ,[PayHours]
  ,[SSN]
  ,[EffectiveDate]
  ,[PayDescription]
  ,[OrderNumber]
  ,[WeekStart]
  ,[WeekEnd]
  ,SUM(PayHours)
   OVER(Partition BY FullName,WeekStart) AS TotalHours
  ,MAX(CASE WHEN PayDescription ='Overtime Pay' THEN 1 ELSE 0 END)
   OVER(Partition BY FullName,WeekStart) AS OvertimePayFlag

  FROM [viewReportPotentialOverTime]

  WHERE EffectiveDate BETWEEN '01/01/2015 00:00:00' AND '10/23/2015 23:59:59'

   -- really needed?
  Group By FullName,SSN,WeekStart, WeekEnd, EffectiveDate, OrderNumber, PayDescription, PayHours
  )
SELECT * FROM RESULTS
WHERE TotalHours > 40  -- no HAVING needed
  AND OvertimePayFlag = 1
Order By FullName, WeekStart, EffectiveDate


How to reference a UNIQUE KEY CONSTRAINT in parent table as a foreign key in child table?

Thanks to ypercube,

I had to drop the parent table "department", recreate it and then use ALTER TABLE employee ADD CONSTRAINT fk_manager_unique FOREIGN KEY (manager_id) REFERENCES department (manager_id); everything worked just fine and the foreign key reference to the unique manager_id key were finally created. Note that I had to change the name of the constraint "manager_unique" in the second table because of duplication issues.

Thanks again!


MySQL GROUP BY query drastically slow down when after data reaches a certain amount

I solved the problem. I needed a single-column index on t1.item. It had a multi-column index that was doing nothing for the query.


Rebuilding, truncating etc SQL Server 2008 maintenance

Your questions cover a lot of scope for SQL Server database maintenance.  We need to address two areas here:  Backups and Index Maintenance.

Backups

The first thing you must understand before you can properly manage your backups is your recovery model.  This will determine how your transaction log is managed by the engine.  The simple description (the "real answer" is far more involved) is that your log file contains the records of each transaction that has occurred in your database.  In relation to that, your recovery model tells the engine whether or not you want point in time recovery (Full/Bulk Logged if you do, Simple if you don't).  Based on this information, the engine will remove records from the log file at checkpoints if it considers them safe to remove:

Full/Bulk Logged:  Since point in time recovery is desired, log records will not be removed until they have been safely backed up.  The engine needs to ensure that these log records can be made available for recovery.  Note, this is different than a full database backup.  If you need to restore your database, you will require both a full database backup and attendant log backups.  More detail on this restore process can be found here.
Simple Recovery: Log records are removed if transactions are completed.  Since point in time recovery is not desired, the engine will not retain the log records.

In regards to your other concerns, the backup operations are done online, so they won't directly impact your database.  Full backups capture a point in time, so any previous files will represent a previous point in time and, if you don't need those previous points in time, you won't need to retain those older backup files.

As for a backup schedule(frequency and retention of files), this is very much of an "it depends" answer.  It is discussed in more detail here.

Index Maintenance

This is a MUCH deeper topic, but essentially what you're looking to do is manage your index fragmentation so that it does not impact performance.  There's a lot written out there on what it is and how to manage it.  ToadWorld is a good place to start on how to manage your indexes and what index maintenance means.  This can impact use of your database (though the database will remain online for the operation), but there's lots of different factors around this impact and how to manage it.

Maintenance Plan

If you have DBA resources in your company, these tasks are within their bailiwick and they will likely have tools and a roadmap for managing these already.  If you don't have these resources, there are several tools that can be leveraged.  For maintenance as a whole, Ola Hallengren provides some free tools for this that are highly regarded and recommended in the SQL community.


Maybe there is a way to tell it to synchronize faster?

Since the amount of data copied to the new secondary is very large (7TB) so the issue happens. MongoDB suggests the following procedure for a large amount of data set.

Sync by Copying Data Files from Another Member

This approach “seeds” a new or stale member using the data files from an existing member of the replica set. The data files must be sufficiently recent to allow the new member to catch up with the oplog. Otherwise the member would need to perform an initial sync.

Copy the Data Files

You can capture the data files as either a snapshot or a direct copy. However, in most cases you cannot copy data files from a running mongod instance to another because the data files will change during the file copy operation.

Sync the Member

After you have copied the data files from the “seed” source, start the mongod instance and allow it to apply all operations from the oplog until it reflects the current state of the replica set.

STARTUP2

Each member of a replica set enters the STARTUP2 state as soon as mongod finishes loading that member’s configuration, at which time it becomes an active member of the replica set and is eligible to vote. The member then decides whether or not to undertake an initial sync. If a member begins an initial sync, the member remains in STARTUP2 until all data is copied and all indexes are built. Afterwards, the member transitions to RECOVERING.


Input requested on localization setup for DB

I have ended up going with a dual table design. The first table would contain all data that would not have a translation for it. A second table would have the word "Translation" appended to it and it would contain columns to cover all the data needing translations and have a single translation per row.


Two major improvements:

SELECT *
FROM   certificates c
WHERE  c.expires_on <= current_date + 30  -- sargable!
AND    NOT EXISTS (
   SELECT 1
   FROM   certificates
   WHERE  common_name = c.common_name
   AND    expires_on  > c.expires_on
   AND    state = 'issued'
   );

Make the first predicate sargable, so that an index can be used.
You second condition is that no qualifying row exists. So don't count, that would be expensive because Postgres has to scan all rows unconditionally. Use a NOT EXISTS anti-semi-join. Postgres can use an index again to quickly verify non-existence and can stop looking further as soon as the first qualifying row has been found. Typically much faster (depending on data distribution). There are several related techniques possible for this:

Select rows which are not present in other table

Ideally, you have at least two indexes:

on (expires_on):

CREATE INDEX ON certificates (expires_on);

A partial multicolumn index.

CREATE INDEX foo ON certificates (common_name, expires_on DESC)
WHERE  state = 'issued';

If state = 'issued' is true for most rows, then adding the condition to the index won't help.

Answer to additional question in comment

If the number of days for tolerance is variable and stored as integer with each row, use a functional index:

CREATE INDEX ON certificates ((expires_on + expiring_days));

And adapt the query accordingly:

SELECT *
FROM   certificates c
WHERE  expires_on + expiring_days <= current_date  -- can use index
AND    NOT EXISTS (
   SELECT 1
   FROM   certificates
   WHERE  common_name = c.common_name
   AND    expires_on  > c.expires_on
   AND    state = 'issued'
   );

Aside: A column like state typically only allows a couple of distinct values. If so, it's a candidate for normalization: add a table certificate_state listing allowed values and use state_id int REFERENCES state in the main table. Just like outlined here:

Low cardinality fields: integer or string


The same MySQL process will use the same CPU cores to process and move data between the same disk and the same memory, whether your reporting table is in the same MySQL database (schema) or in a different schema. Even if you have a separate MySQL instance on that machine, it will still compete with its sibling for hardware resources. For proper isolation you need a separate server.

A typical scenario includes a master-slave replicated pair, with reporting workload hitting the slave.


select row where a couple of column values is different

IPs with different created_by:

SELECT ip
     , Count(DISTINCT created_by) As number_of_created_by
FROM   your_table
GROUP
    BY ip
HAVING Count(DISTINCT created_by) > 1

Alternative method:

SELECT ip
     , Min(created_by) As min_created_by
     , Max(created_by) As max_created_by
FROM   your_table
GROUP
    BY ip
HAVING Min(created_by) < Max(created_by)

Something like this should suffice for getting the details of these "rows":

SELECT *
FROM   your_table As outer_part_of_query
WHERE  EXISTS (
         SELECT ip
              , Count(DISTINCT created_by) As number_of_created_by
         FROM   your_table
         WHERE  ip = outer_part_of_query.ip
         GROUP
             BY ip
         HAVING Count(DISTINCT created_by) > 1
       )


Can SQL Server 2014 schema be defined using ODBC?

No, ODBC cannot set the schema.

ODBC is (in Wikipedia-speak) "a standard programming language middleware API for accessing database management systems (DBMS).  The designers of ODBC aimed to make it independent of database systems and operating systems."

If you want an array of schemas, they need to be created in the database before they are used.

If you manage your import closely, then is should be possible to change the schema of objects after the import.  E.g. from the SQL Server documentation:
https://msdn.microsoft.com/en-us/library/ms173423.aspx

USE AdventureWorks2012;
GO
ALTER SCHEMA HumanResources TRANSFER Person.Address;
GO

This transfers the Address table from the Person schema to the HumanResources schema.

Additionally the CREATE SCHEMA command can be used to create objects within the schema. (Again, this is at the database level.)
https://msdn.microsoft.com/en-us/library/ms189462(v=sql.105).aspx

CREATE SCHEMA Sprockets AUTHORIZATION Annik
  CREATE TABLE NineProngs (source int, cost int, partnumber int)
  GRANT SELECT ON SCHEMA::Sprockets TO Mandar
  DENY SELECT ON SCHEMA::Sprockets TO Prasanna;


Go with your alternate option - create a new file group, move the data there, and then drop the old file group. That's Paul Randall's advice, even:

So what if you do need to run a shrink? For instance, if you’ve deleted a large proportion of a very large database and the database isn’t likely to grow, or you need to empty a file before removing it?
The method I like to recommend is as follows:
Create a new filegroup. Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time. Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)
Basically you need to provision some more space before you can shrink the old files, but it’s a much cleaner mechanism.
If you absolutely have no choice and have to run a data file shrink operation, be aware that you’re going to cause index fragmentation and you should take steps to remove it afterwards if it’s going to cause performance problems. The only way to remove index fragmentation without causing data file growth again is to use DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE. These commands only require a single 8KB page of extra space, instead of needing to build a whole new index in the case of an index rebuild operation.
Bottom line – try to avoid running data file shrink at all costs!


Meta-relationships (Relationships between relationships) versus tuples

I like option 1 better. My first thought is always maintaining data integrity so my question is, "Could it be possible to relate a student to a tutor for a course the tutor is not approved for?" With table student_course_tutor, the field student_id would, as you say the student needn't be attending the course, be a FK to the Students table and the fields (course_id, tutor_id) would be a FK to the course_tutor table. If the requirement that the student must be enrolled in the course as well as the tutor must be approved for the course, that would require only that the fields (student_id, course_id) be a FK to the student_course table.

So you cover the existing specifications and you have an easy solution for a not unreasonable future requirement.


SQL query for getting Count of distinct items along with Total

You need to be looking at GROUP BY and aggregate functions. See https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html for mysql's documentation.
In this case it looks like:

You want to be grouping by Item Name, you can just use the field directly in SELECT then as it is part of the grouping.
For Qty use the counting aggregate (COUNT(*)).
For Total Amount use SUM(Price).
Price could be a little more complicated. If the price for a given item is always the same (so something called Coffee is always 10) then either of the MIN() or MAX() aggregates will do, or you could include Price in the grouping (GROUP BY Item, Price) and not need any function. If the price of a named item can vary then you could use AVG(Price) to output the average, or you could chose to list each item+price combinations (so "2x coffee at 10, total 20, and 1x coffee at 12, total 12").


oracle crash with ORA-00942: table or view does not exist

The error you're getting indicates that a query is trying to reference an object that doesn't exist or that the user doesn't have access to.  If you started getting it a week ago and weren't getting it before that, something changed a week ago so that an object was dropped, a privilege was revoked, or a SQL statement was added that references an invalid object.  It is unlikely that upgrading the database would resolve the error.

Can you determine the SQL statement that your application is issuing when it receives this error?  If not, can you add additional logging so that you can determine what statement is generating the error?  If your application doesn't log the SQL statements it generates, you could create a SERVERERROR trigger (based on this trigger code).  Obviously, you would probably want to log more than just the SQL statement.

create table stmt_err_log (
  sql_stmt varchar2(1000)
);

 CREATE OR REPLACE TRIGGER after_error
  AFTER SERVERERROR ON DATABASE
  DECLARE
  pragma autonomous_transaction;
  id NUMBER;
  sql_text ORA_NAME_LIST_T;
  v_stmt CLOB;
  n NUMBER;
 BEGIN
  n := ora_sql_txt(sql_text);
  --
  IF n >= 1
  THEN
    FOR i IN 1..n LOOP
      v_stmt := v_stmt || sql_text(i);
    END LOOP;
  END IF;
  --
  FOR n IN 1..ora_server_error_depth LOOP
   IF ora_server_error(n) = '942'
   THEN
     insert into scott.stmt_err_log( sql_stmt )
       values( v_stmt );
     commit;
   END IF;
 END LOOP;
  --
 END after_error;


When looking at the plan-forcing limitations one of the bullet points is:

  Distributed query or full-text operations

However, this limitation does not mean that a delete should fail the plan forcing.

Consider these 4 queries, two SELECT statements and two DELETE statements on a table with a fulltext index on the val column:

SELECT *
FROM  dbo.fulltextindexed;

SELECT val
FROM  dbo.fulltextindexed
WHERE CONTAINS(val, N'WOW');

DELETE top(1)
FROM dbo.fulltextindexed;

DELETE top(1)
FROM dbo.fulltextindexed
WHERE id <500 and id > -1;

The only query that is failing is the 2nd one, which is using the CONTAINS statement:

With the failure reason being DQ_NO_FORCING_SUPPORTED.

The plans for the two delete statements, which also show the same clustered index merge operators get forced:

This source gives more information on DQ_NO_FORCING_SUPPORTED:

  Cannot execute query because plan conflicts with use of distributed
  query or full-text operations

To answer the question

  Does this join to the FT index mean that plan-forcing is not
  supported?

My answer would be that this is not the reason as you should see the DQ_NO_FORCING_SUPPORTED instead of the NO_PLAN error.
The reason could be due to another limitation rendering the plan invalid, index changes, added foreign keys, ....

Extra tests

When adding a table and creating a foreign key with on delete cascade that references the table used in the queries above.We would expect the delete to fail since we cannot use the forced plan any longer.

CREATE TABLE dbo.fulltexreference(id int identity(1,1) primary key not null, t1id int, val varchar(max));
ALTER TABLE dbo.fulltexreference add constraint FK_test foreign key(t1id) REFERENCES dbo.fulltextindexed(id) ON DELETE CASCADE;

As expected, we get the NO_PLAN on the two delete statements

Re-forcing the plans gives us salvation:

And removes the NO_PLAN issue

The same is true for a table with a fulltext index that has a foreign key (with on delete cascade) that references the main table where the delete query is running on.

Regarding the high / low estimates

If the delete is not executed that much, adding OPTION(RECOMPILE) could help with the high row estimates as this can give you a better estimation due to the optimizer 'seeing' the variable at runtime.

Posting a different question with the query plan might bring up a different workaround / solution.


mysql query with 1 derived table and 1 inner join takes forever to load

(Need formatting not available in a Comment.)

This is ambiguous:

FROM deal_buildings AS db2
WHERE db2.deal_id = n.id
  AND n.main_member_id = 79
  AND db.status in (11,12,13,20,21)
ORDER BY status DESC              -- Is this in `db` or `db2`?  QUALIFY!

IF(blah, true, false) can be shortened to just (blah)


Filtering options in Data Pump are still something that should be improved. Anyway, you could try the below parameters:

directory=data_pump_dir
dumpfile=dump.dmp
logfile=export.log
schemas=schema1,schema2,schema3
exclude=TABLE_DATA:" IN (select table_name from dba_tables where owner in ('SCHEMA1', 'SCHEMA2') union select partition_name from dba_tab_partitions where table_owner in ('SCHEMA1', 'SCHEMA2'))"

This excludes TABLE_DATA for all tables and partitions whose name match the result of the query provided in the exclude part.

And that is why this may not be appropriate for you. If you have a table called T1 both in SCHEMA1 and SCHEMA3, the data of SCHEMA3.T1 will be excluded, because the above query returns T1 from SCHEMA1, and Data Pump will ignore TABLE_DATA of all table called T1. If you have a partitioned table in SCHEMA2, with a partition called P1, the data of the table SCHEMA3.P1 will be excluded. The above may work if your table and partition names are distinct accross all referenced schemas.

Or you could dynamically generate the expdp command or use DBMS_DATAPUMP to filter out all the table data from SCHEMA3.

But I would rather just run 2 seperate exports for SCHEMA1,SCHEMA2 with CONTENT=METADATA_ONLY and a default export (CONTENT=ALL) for SCHEMA3.


According to Oracle Support Community this is likely due to a JDBC / Oracle JDBC driver issue; are you using an unusual Locale on your PC?  Is the ojdbc*.jar file on your PC that SQL*Developer is using the correct version for your database?


How to re-assign two variables in a conditional UPDATE

It is possible to do two assignments in one IF statement.

There are several ways; I present one, using CASE statement:

SET @var1 = 20;
SET @var2 = 26;
UPDATE table1 SET
    col1=IF(
      @var1>50,
      @var1 := @var1-col4,
      CASE
        WHEN @var1 := @var1+col5 IS NULL THEN NULL
        WHEN @var2 := @var2 + 100 IS NULL THEN NULL
        ELSE @var1
      END
    ),
col2=IF(@var2>50, @var2 := @var2-col6, @var2);

The idea is to create a CASE statement where first WHEN clauses fail. They fail because we want them to, but along they way they get evaluated.

But in order for them to get evaluated, they must first computer the assignment expression, such as @var1 := @var1+col5 and @var2 := @var2 + 100.

CASE expressions work from first WHEN clause to the next one, and must work in this order.

Please consider reading more about it in my presentation of last year's Percona Live London (2011) conference. For referece, relevant slides are 13 - 15: Programmatic queries: things you can code with sql


How to loop through AS400 table with cursor

You are not checking for an end of cursor condition.

DECLARE END_CL INT DEFAULT 0;
DECLARE CL CURSOR FOR
SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS
FROM DB_TRANSDATA
ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET END_CL = 1;
FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , V_POINTSEQ ;
WHILE END_CL = 0 DO
    . . .
    FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , V_POINTSEQ ;
END WHILE;

For more information:

IBM i 7.1 Information Center > Database > Programming > SQL programming > Routines > Stored procedures

IBM Redbooks > Power Systems > Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database™ for iSeries


Slow SQL Server authentication, but fast SAN, fast querying?

Here is your answer:

MSDB on C:
86% of 32GB
io_stall_write_ms : 192217

If MSDB is on c: master is on c: as well and I'm almost willing to bet that tempdb is still in the default location as the average I/O stall on c is 19 seconds!

First, check if tempdb is on c:\ or has any file on the c:\ partition and if so move it away

Check if the master database has some free space - Usually you dont need to tune it as very little is written to the master database, unless if someone has created objects in it

Then make space in the MSDB database is way to big which can cause a lot of troubles, please remove as much backup and job history from it database as you can.

You can do all these checks very simply by downloading and running sp_blitz


Is there some sort of shortcut/feature in PostgreSQL to avoid horrible storage waste with duplicate values?

There is no magical automatism for that. You will have to create the lookup table yourself.

This is the way relational databases are designed: you spread your data over several tables. For example, if you normalize your schema, you will end up with more tables than entities. In a way, this lookup table can be seen as a kind of normalization, since the user agent doesn't feel atomic to you.

Don't worry about having more than one table: inner joins are quite simple and readable in SQL, and databases are optimized to process them efficiently.


Why do we need to backup Oracle archive logs along with datafiles

No, you still need archived redo logs. An RMAN backup is not the same as a cold backup; when you restore it you need to apply all the redo generated from the time you started the backup until the time the backup completed in order to get the SCN consistent across all the datafiles and the controlfile.

Let us consider the old fashioned hot backup. In this mode, entire changed database blocks are written to the redo log stream, and the file is copied in the OS. This causes fractured blocks, i.e. the block has changed during the read by the backup. So to recover the database, we restore the DBFs, then the recovery process overlays the changed blocks from the archived redo logs onto the fractured blocks in the file, and we get back to a consistent database.

In an RMAN backup the problem of fractured blocks is eliminated because the blocks are read by Oracle via the SGA not by a Unix tool like cp. However the DBF changes between the time the first block is read and the last block, so it is the same problem, just at a larger scale. That is to say, the SCN changes between the first and last blocks in a backup. The archived redo logs cover this as well.

RMAN gives you the manageability of the catalog, and a means to do hot backups without needing to generate redo at a much higher rate (which puts its own stress on the disks, the backup system etc). However BACKUP DATABASE will not give you a consistent database in and of itself; the only way to do that in Oracle is the cold backup.


How to order and partition by 2+ columns and then select a random sample based on N number of ranks in the sample?

Use ROW_NUMBER() function better.

SELECT
    name,
    category,
    ROW_NUMBER() OVER (PARTITION BY name, category ORDER BY name, category) unique_id
FROM
    test;

name | category | unique_id
:--- | :------- | --------:
A    | Alpha    |         1
A    | Alpha    |         2
A    | Alpha    |         3
A    | Beta     |         1
A    | Beta     |         2
B    | Alpha    |         1
B    | Alpha    |         2
B    | Theta    |         1

db<>fiddle here

To get a unique_id for each pair of name-category, remove the PARTITION part and use only ORDER BY:

SELECT
    name,
    category,
    DENSE_RANK() OVER (ORDER BY name, category) unique_id
FROM
    test;

name | category | unique_id
:--- | :------- | --------:
A    | Alpha    |         1
A    | Alpha    |         1
A    | Alpha    |         1
A    | Beta     |         2
A    | Beta     |         2
B    | Alpha    |         3
B    | Alpha    |         3
B    | Theta    |         4

db<>fiddle here

To select two rows of each unique_id: (As far as I've not used any order it is supposed to be random)

WITH ct AS
(
  SELECT
      name,
      category,
      DENSE_RANK() OVER (ORDER BY name, category) unique_id
  FROM
      test
)
SELECT
    a.unique_id, c.name, c.category
FROM
    (SELECT DISTINCT unique_id
     FROM ct) a
JOIN LATERAL (SELECT b.name, b.category
              FROM   ct b
              WHERE  b.unique_id = a.unique_id
              LIMIT 2) c ON TRUE
ORDER BY
    a.unique_id;

unique_id | name | category
--------: | :--- | :-------
        1 | A    | Alpha
        1 | A    | Alpha
        2 | A    | Beta
        2 | A    | Beta
        3 | B    | Alpha
        3 | B    | Alpha
        4 | B    | Theta

db<>fiddle here


How long is "too long" for MySQL Connections to sleep?

mysqld will timeout database connections based on two server options:

interactive_timeout
wait_timeout

Both are 28,800 seconds (8 hours) by default.

You can set these options in /etc/my.cnf

If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 minutes) or even 60 (1 minute). Or, if your app works just fine, you can leave the default. This is up to you.

You must set these as follows in my.cnf (takes effect after mysqld is restarted):

[mysqld]
interactive_timeout=180
wait_timeout=180

If you do not want to restart mysql, then run these two commands:

SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;

This will not close the connections already open. This will cause new connections to close in 180 seconds.


postgres performance tuning - what to try next?

I think some of these suggestions may help, esp. using COPY instead of INSERT.

https://stackoverflow.com/questions/12206600/how-to-speed-up-insertion-performance-in-postgresql


select network_segment_ID, display_name, starting_ip, ending_ip, bld.description, dept.description
from network_segments
join buildings bld on (building = building_ID)
join departments dept on (department = department_ID);


Understanding MS SQL Server encryption and backups

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION`
 BY CERTIFICATE MyCertificate`

  notice - there is no password here.

Yes, there is. You opened the certificate MyCertificate somehow, didn't you? There could be several cases, but any of them ends up with a password:

you opened the certificate using a password (obviously, there is a password in this case)
the certificate is encrypted with the database master key and you opened the database master key using the DBMK password -> that's the password
the certificate is encrypted with the database master key and the DBMK is encrypted with the service master key. The service master key was in turn opened using the service account DPAPI storage key, which is encrypted with the account password -> and that is the password

In the last key there seem to be no password, but there is one: the service account password. The backup, when restored on a different host, it will lack the service master key encryption of the database master key and this will make the data inaccessible.

See Encryption Hiearchy


Full outer join does not work for two small tables, error 1064

The simple reason is that MySQL has not implemented FULL outer joins, only LEFT and RIGHT ones.

You can simulate the FULL join with a UNION of a LEFT and a RIGHT outer join:

SELECT TableA.*, TableB.*
FROM
  TableA LEFT OUTER JOIN TableB
    ON TableA.name = TableB.name

UNION

SELECT TableA.*, TableB.*
FROM
  TableA RIGHT OUTER JOIN TableB
    ON TableA.name = TableB.name ;

or (for improved performance) using UNION ALL:

SELECT TableA.*, TableB.*
FROM
  TableA LEFT OUTER JOIN TableB
    ON TableA.name = TableB.name

UNION ALL

SELECT TableA.*, TableB.*
FROM
  TableA RIGHT OUTER JOIN TableB
    ON TableA.name = TableB.name
WHERE
    TableA.name IS NULL ;

Both queries (UNION and UNION ALL) will return same results. Unless the result of the join is not unique (if it produces 2 or more identical rows). Since this question has SELECT *, so SELECT a.*, b.*, for this to happen, the tables should have duplicate rows, which is rather unlikely (a table with no primary or unique constraint.)


How to access to DB Instance from another DB Instance with Windows Authentication

You will have to setup a linked server forwarding the user credentials between the instances. There are step by step instructions here:

It boils down to running this on  DB_INSTANCE1\DB1

EXEC sp_addlinkedserver
 @server=N' DB_INSTANCE2',
 @srvproduct=N'',
 @provider=N'SQLNCLI',
 @datasrc=N' DB_INSTANCE2\DB2';

sp_addlinkedsrvlogin @rmtsrvname = 'DB_INSTANCE2', @useself = 'TRUE'

Which creates a linked server named db_instance2 and tells the server to use the logged on user credentials to connect to the linked server. If you have configured Kerberos correctly for DB_INSTANCE1 users with permissions on both the servers will be able to select data from the other server: select * from db_instance2.master.sys.databases

As stated before, for this to work Kerberos has to work and therefore you will need to setup SPN's and do some active directory magic. The Kerberos configuration manager will help you and your AD administrator to set up everything needed

It also means that users that connect with database logins will not be able to connect to the server - For that you will have to create user mappings either in the GUI or using TSQL

sp_addlinkedsrvlogin
 @rmtsrvname = 'DB_INSTANCE2',
 @locallogin = 'Local_SQLLogin',
 @rmtuser = 'RemoteSQLLogin',
 @rmtpassword = 'rmtpassword'


Depending on the full error message you receive (I have a hunch you didn't post it in full) you probably are trying to run the script by using a login role which is not superuser:

C:\Users\Administrator>c:\PostgreSQL\9.0\bin\psql.exe -U milen -f C:\PostgreSQL\9.0\share\contrib\pgcrypto.sql milen
SET
psql:C:/PostgreSQL/9.0/share/contrib/pgcrypto.sql:9: ERROR:  permission denied for language c
psql:C:/PostgreSQL/9.0/share/contrib/pgcrypto.sql:14: ERROR:  permission denied for language c
psql:C:/PostgreSQL/9.0/share/contrib/pgcrypto.sql:19: ERROR:  permission denied for language c
...


sql error 1241 operand should contain 1 column

You cannot have multiple columns being returned in a subquery like that, so you have several ways that you would have rewrite this query to work.

Either you can unpivot the data in the team table so you are only returning one column:

select *
from players
where sport='football'
  and position='DEF'
  and pname!='Binoy Dalal'
  and pname not in (select player1
                    from team where sap='60003100009'
                    union all
                    select player2
                    from team where sap='60003100009'
                    union all
                    select player3
                    from team where sap='60003100009'
                    union all
                    select player4
                    from team where sap='60003100009'
                    union all
                    select player5
                    from team where sap='60003100009'
                    union all
                    select player6
                    from team where sap='60003100009'
                    union all
                    select player7
                    from team where sap='60003100009'
                    union all
                    select player8
                    from team where sap='60003100009')
order by price desc;

Or you can use a NOT EXISTS query:

select *
from players p
where sport='football'
  and position='DEF'
  and pname!='Binoy Dalal'
  and not exists (select *
                  from team t
                  where sap='60003100009'
                    AND
                    (
                      p.pname = t.player1 OR
                      p.pname = t.player2 OR
                      p.pname = t.player3 OR
                      p.pname = t.player4 OR
                      p.pname = t.player5 OR
                      p.pname = t.player6 OR
                      p.pname = t.player7 OR
                      p.pname = t.player8
                    ))
order by price desc;

Or you would have to use multiple WHERE filters on the player name:

select *
from players
where sport='football'
  and position='DEF'
  and pname!='Binoy Dalal'
  and pname not in (select player1
                    from team where sap='60003100009')
  and pname not in (select player2
                    from team where sap='60003100009')
  and pname not in (select player3
                    from team where sap='60003100009')
  and pname not in (select player4
                    from team where sap='60003100009')
  and pname not in (select player5
                    from team where sap='60003100009')
  and pname not in (select player6
                    from team where sap='60003100009')
  and pname not in (select player7
                    from team where sap='60003100009')
  and pname not in (select player8
                    from team where sap='60003100009')
order by price desc;

However, ideally you should consider normalizing the team table so you have one column with the player name and another column that assigns them a player number. Similar to this:

create table team
(
  player varchar(50),
  playerNumber int
);

Then when you are searching the team data you only have to join on one column instead of 8 different columns.


pg_stat_activity entries with no corresponding process on the client

You are probably correct that this simply means that the client process exited.  If it exits without cleaning up the connection while its back-end server process is working on a query, then the server process will not notice until it tries to send a message to the client and finds that no one is there.

If it takes a long time to return a tuple (because it has to sort or aggregate before it sends the first one, for example) then the server process can be a dead man walking for a long time.

There was discussion about fixing this, but finding a way to do it in a performant and cross-platform way is not easy.


Take advantage of the index for each table doing something like this:
(SELECT  MIN(`time`) `time` FROM  `eventlog` WHERE userid =  '9819') UNION ALL
(SELECT  MIN(`time`) FROM  `screenshots` WHERE userid =  '9819')     UNION ALL
(SELECT  MIN(`time`) FROM  `sitelog` WHERE userid =  '9819')         UNION ALL
(SELECT  MIN(`time`) FROM  `keylog` WHERE userid =  '9819')          UNION ALL
(SELECT  MIN(`time`) FROM  `webcamcaps` WHERE userid =  '9819')
ORDER BY  `time` ASC
LIMIT 1;

or
(SELECT  `time` FROM  `eventlog` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `screenshots` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `sitelog` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `keylog` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `webcamcaps` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
ORDER BY  `time  ASC
LIMIT 1;

The main idea is to get each subquery to return 1 row.
Give it a Try !!!


Selecting unique rows based on the maximum date

CREATE OR REPLACE VIEW schema.view AS
SELECT DISTINCT ON (thing_id, forecast_datetime)
       forecast_datetime,
       interval,
       time_zone,
       file_date,
       thing_id,
       thing_name,
       other_cols...
FROM   schema.orig_table
ORDER  BY  thing_id, forecast_datetime ASC, file_date DESC

This appears to have worked perfectly.


What indexed view capabilities does Azure SQL Database have?

I was just searching for an answer to this and I found an MSDN blog entry from 2016 that suggests that Azure SQL does support indexed view matching and may (in at least some circumstances) use indexed views without NOEXPAND. From the linked article:

  YES,The code proves that the  Azure SQL supports automatic use of indexed views without specifying the NOEXPAND hint.

The blog entry also provides a code example that the author uses to support their assertion. I won't reproduce it here but check out the article at: https://blogs.msdn.microsoft.com/azuresqldbsupport/2016/09/04/does-azure-sql-db-support-automatic-indexed-views/


What are current values in PG admin configuration editor for postgresql.conf

Current value is what the currently active database is using.

many of those settings require the database to be restarted (or reboot) before they take effect.


Trigger to create a variable Table to be sent to service broker

You could send the xml data in predefined chunks by using something like the trigger in the following setup.

Create the testbed in tempdb:

USE tempdb;

IF EXISTS (SELECT 1 FROM sys.triggers t WHERE t.name = 'TriggerTest_Chunked')
DROP TRIGGER dbo.TriggerTest_Chunked;

IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTest')
DROP TABLE dbo.TriggerTest;

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

IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTestTarget')
DROP TABLE dbo.TriggerTestTarget;

CREATE TABLE dbo.TriggerTestTarget
(
    OperationType INT NOT NULL
    , DEETS XML NOT NULL
);

The trigger:

CREATE TRIGGER dbo.TriggerTest_Chunked
ON dbo.TriggerTest
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @RowStart INT;
    DECLARE @RowEnd INT;
    DECLARE @RowCount INT;
    DECLARE @BatchSize INT;
    SET @BatchSize = 1000;

    SET @RowStart = 0;

    SELECT @RowCount = COUNT(1)
    FROM inserted;

    IF @RowCount > @BatchSize
        SET @RowEnd = @BatchSize
    ELSE
        SET @RowEnd = @RowCount;

    WHILE @RowEnd <= @RowCount AND (@RowStart < @RowEnd)
    BEGIN
        INSERT INTO dbo.TriggerTestTarget (OperationType, DEETS)
        SELECT 1, (
            SELECT ID
            FROM (
                    SELECT *
                        , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    FROM inserted
                ) i_rn
            WHERE i_rn.rn > @RowStart
                AND i_rn.rn <= @RowEnd
            FOR XML PATH('')
        );

        SET @RowStart = @RowStart + @BatchSize;
        IF @RowEnd + @BatchSize > @RowCount
            SET @BatchSize = @RowCount - @RowEnd;
        SET @RowEnd = @RowEnd + @BatchSize;
    END

    SET @RowStart = 0;

    SELECT @RowCount = COUNT(1)
    FROM deleted;

    IF @RowCount > @BatchSize
        SET @RowEnd = @BatchSize
    ELSE
        SET @RowEnd = @RowCount;

    WHILE @RowEnd <= @RowCount AND (@RowStart < @RowEnd)
    BEGIN
        INSERT INTO dbo.TriggerTestTarget (OperationType, DEETS)
        SELECT 2, (
            SELECT ID
            FROM (
                    SELECT *
                        , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    FROM deleted
                ) i_rn
            WHERE i_rn.rn > @RowStart
                AND i_rn.rn <= @RowEnd
            FOR XML PATH('')
        );

        SET @RowStart = @RowStart + @BatchSize;
        IF @RowEnd + @BatchSize > @RowCount
            SET @BatchSize = @RowCount - @RowEnd;
        SET @RowEnd = @RowEnd + @BatchSize;
    END
END
GO

Insert some test data to see what the trigger does:

INSERT INTO dbo.TriggerTest (ID)
SELECT ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id)
FROM sys.objects o1
    , sys.objects o2;

Show results:

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

DELETE TOP(100)
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;


Unknown system variable 'show_compatibility_56'

This has been fixed so a later version (>1.0.2) of backupninja will likely resolve your issue.


Trigger to delete inserted record if it doesn't exist in another table

Well, it's a crazy and novel idea, but you might consider that this is what foreign key constraints are actually for... you can't insert a row in table "x" if a column there has a foreign key constraint against a column in table "y" but the value you're inserting in "x" doesn't exist in "y"...

mysql> create table t1 (
         id int not null auto_increment primary key
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.40 sec)

mysql> create table t2 (
         id int not null auto_increment primary key,
         t1_id INT NOT NULL,
         FOREIGN KEY(t1_id) REFERENCES t1(id)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.49 sec)

mysql> insert into t2 (t1_id) VALUES (1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))

mysql>


The redo thread that services the AlwaysOn log stream will block trying to acquire the required SCH-M lock (is a SCH-M for any of the cases you cite, it makes no difference what DDL it is).

New queries on the secondaries that reference the locked object(s) will coalesce behind the waiting SCH-M lock (they cannot sneak ahead, to prevent starvation of the waiter). Eventually the last query holding the SCH-S will drain and unblock the redo thread. The redo thread will get the lock and resume, applying the log records.

When the log record of the DDL commit will be replayed the the lock will be released and the secondary queries will resume. All this is described also in AlwaysON – HADRON Learning Series: lock_redo_blocked/redo worker Blocked on Secondary Replica.

Sync or Async AlwaysOn make no difference, since the sync part applies to received log durability, not to replay (redo) of the received log.

Normally there is no need to stop queries on the secondary. The mechanism described will handle the situation. In the rare event that you have secondary queries that may last a really long time (+30 min) with a potential to block log redo, you should monitor for the blockage and maybe take measures.

You need to ensure that log redo is current (ie. not much log received and pending redo), but that is the normal state. Then just apply the DDL. If you notice redo blockage on the secondaries you can kill the individual SPID blocking the redo.
Note: The reason why I say 'redo is current' is simply to have control when things occur. If the redo is, say, 30 minutes behind, then you apply the DDL now and it will be replayed in 30 minutes, so killing secondary queries now is rather useless. Is not that you have to be exactly up to date, a small delay (redo queue size) should do no harm.

In async mode log redo can get current. It all depends on how fast is the primary writing. Think, what if the primary has no writes for 1h, will the secondary refuse to apply the last log received?


Can I grant a user ALTER TRACE permission to a single database?

i doubt that

the ALTER TRACE permission is a server level permission, and access is at the server level; if a user can start a trace, he or she can retrieve event data no matter what database the event was generated in.

for more info

http://technet.microsoft.com/en-us/library/cc293611.aspx

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


full outer join where join key in result always has value

Use this function:

COALESCE( a.id, b.id )


How to pass a row as a parameter to a Postgres function?

It's much simpler that that. While one would expect it to be ...

SELECT build_details(my_table.*, 1000, TRUE) FROM my_table LIMIT 1;

... the actual syntax is:

SELECT build_details(my_table, 1000, TRUE) FROM my_table LIMIT 1;


sql server maintence plans. What account to use?

Since the jobs will run as your SQL Server Agent account, that's the way to go: http://msdn.microsoft.com/en-us/library/ms187658.aspx

It is a best practice to run your SQL Server engine and your Agent under different accounts however, the logic being if one account is locked or otherwise compromised, you aren't hurting the other. To change your Agent account: http://msdn.microsoft.com/en-us/library/ms191543.aspx


Join two SELECT results with the same post_id value

Are you looking for IN?

SELECT wp_postmeta.post_id
FROM wp_postmeta
WHERE wp_postmeta.meta_key in ('_downloadable', '_virtual')
AND wp_postmeta.meta_value='no';

Updated answer as per updated question:

I'd try to understand your question.

You have following table:

mysql> select * from s;
+------+------+------+
| id   | skey | sval |
+------+------+------+
|    1 | v    | n    |
|    1 | d    | n    |
|    2 | b    | n    |
|    3 | v    | n    |
|    3 | d    | y    |
|    4 | v    | n    |
|    4 | d    | n    |
|    2 | c    | n    |
+------+------+------+

You want final answer as 1 and 4 because both "v" and "d" are "n" at-the-same-time (for id).

If this is right requirement then below query should work.

select id from s where skey in ('v','d') and sval='n' group by id having count(*)=2;


Storing multiple types of users and their respective credentials in separate or same table?

This question appears to me to be related to another one, concerning generalization/specialization (aka inheritance) vs. association. The "baking" approach is one of the strategies to map inheritance to relational databases (table per concrete class in Hibernate). The separate table approach can also be regarded as such a strategy (table per subclass), or as a mapping of associated, not inherited, objects.

By the way, talking of inheritance, your students and sponsors have quite a bit in common, so they might be generalized to person, which could then also carry the credentials.

That said, let me comment on your criteria. The criteria against don't appear particularly strong to me:

Complexity: About the same in both solutions. Having two instead of three tables still means to have one piece of functionality around credentials which needs to be applied to both students and sponsors.
Commonality: No matter whether the rest of student and sponsor data and functions have much in common, authenticating users certainly is a significant bit of commonality.
Transferring the credentials part to another platform won't get easier if it is included in the student or sponor table.
This will only be significant if there are lots of such queries, or if they are very intricate. Even then you can simulate the separate table by an SQL union, and, the other way round, the "baked" tables by joins.

Unfortunately, the critera for creating the new table aren't compelling either:

Cf. criterium 4 above.
Separating person from credentials appears to me even to introduce a join (if needed) between the two, not to save any joins.
The amount of data, for those few attributes of all involved objecs, should not be a problem.

To sum up, neither of both solutions will break your neck. I have still a favor for the separate table (and, in particular, for separate program constructs to handle credentials), because access to a system is a separate concern. That's why holders of access to a system are sometimes not called users but principals. E.g. there might be credentials for web or rest services of a system that are not bound to any person, or credentials for automatic jobs operating on your data. As you said, granting or refusing access has not much to do with the attributes of a person, like name or age.


Why is MySQL MariaDB GREATEST(timestamp,NOW()) padding appending results with zeros?

The MariaDB doku claims for NOW():

  Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS'
  or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is
  used in a string or numeric context.

So this might be related to the fact that '2016-04-14 15:06:30' may look like a timestamp, but is actually a Varchar.

Try using timestamp literals:

SELECT GREATEST( TIMESTAMP '2016-04-14 15:06:30', NOW() )

Or functional conversion:

SELECT GREATEST( STR_TO_DATE('2016-04-14 15:06:31','%Y-%m-%d %H:%i:%s'), NOW() )


Merging Continous Date Ranges with upto 63 days in between

WITH
CTE1 AS (SELECT LAG(TERM_DT) OVER (PARTITION BY ID1,ID2 ORDER BY EFF_DT) PREV_DT,
                EFF_DT,
                TERM_DT,
                LEAD(EFF_DT) OVER (PARTITION BY ID1,ID2 ORDER BY EFF_DT) NEXT_DT,
                ID1,
                ID2
         FROM ELIGIBILITY),
CTE2 AS (SELECT EFF_DT,
                TERM_DT,
                ID1,
                ID2,
                COALESCE(DATEDIFF( DAY, PREV_DT, EFF_DT), 1000) D1,
                COALESCE(DATEDIFF(DAY, TERM_DT, NEXT_DT), 1000) D2
         FROM CTE1
         WHERE COALESCE(DATEDIFF( DAY, PREV_DT, EFF_DT), 1000) > 63
            OR COALESCE(DATEDIFF(DAY, TERM_DT, NEXT_DT), 1000) > 63)
SELECT DISTINCT
       CASE WHEN D1 > 63
            THEN EFF_DT
            ELSE LAG(EFF_DT) OVER (PARTITION BY ID1,ID2 ORDER BY EFF_DT)
            END EFF_DT,
       CASE WHEN D2 > 63
            THEN TERM_DT
            ELSE LEAD(TERM_DT) OVER (PARTITION BY ID1,ID2 ORDER BY EFF_DT)
            END TERM_DT,
       ID1,
       ID2
FROM CTE2
ORDER BY ID1, ID2, EFF_DT

fiddle


What's the best way to structure logging application data in MySQL

Yes, there is. I would change the partition names to something more meaningful.
Let say the table look like this in the mydb database:
CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    date DATETIME,
    PRIMARY KEY (id,date),
    KEY (date)
)
PARTITION BY RANGE( YEAR(date) ) (
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p9999 VALUES LESS THAN MAXVALUE
);

To add a partition with the year 2019
ALTER TABLE mytable DROP PARTITION p9999;
ALTER TABLE mytable ADD PARTITION
    (PARTITION p2019 VALUES LESS THAN (2020)),
    (PARTITION p9999 VALUES LESS THAN MAXVALUE)
;

To drop the oldest partition
ALTER TABLE mytable DROP PARTITION p2016;

Want a Stored Procedure to do the following ?

Drop partitions before this year
Append new partition 4 years into the future

Here is the code
DELIMITER $$

DROP PROCEDURE IF EXISTS `mydb`.`sp_mytable_Rotate` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `mydb`.`sp_mytable_Rotate` ()
This_Stored_Procedure:BEGIN

  SET @year0 = YEAR(NOW());
  SET @year4 = @year0 + 4;
  SET @year5 = @year0 + 5;
  SET @OldPartitionToKeep = CONCAT('p',@year0);
  SET @NewPartitionToMake = CONCAT('p',@year4);

  SELECT COUNT(1) INTO @zapcount FROM information_schema.partitions
  WHERE table_schema = DATABASE() AND table_name = 'mytable'
  AND partition_name < @OldPartitionToKeep;

  IF @zapcount = 0 THEN LEAVE This_Stored_Procedure; END IF;

  SELECT GROUP_CONCAT(partition_name) INTO @partitions_to_drop
  FROM information_schema.partitions  WHERE table_schema = DATABASE()
  AND table_name = 'mytable'  AND partition_name < @OldPartitionToKeep;
  SET @partitions_to_drop= CONCAT(@partitions_to_drop,',p9999');

  SET @sql = CONCAT('ALTER TABLE mytable DROP PARTITION ',@partitions_to_drop);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

  SET @sql = 'ALTER TABLE mytable ADD PARTITION ';
  SET @sql = CONCAT(@sql,'(PARTITION ',@NewPartitionToMake,' VALUES LESS THAN (',@year5,'))');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

  SET @sql = 'ALTER TABLE mytable ADD PARTITION (PARTITION p9999 VALUES LESS THAN MAXVALUE)');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

END $$

DELIMITER ;

From here, just call it
USE mydb
CALL sp_mytable_Rotate();

On January 1, you could backup the old partition's data using mysqldump before rotating like this:
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
THISYEAR=`date +%Y`
(( LASTYEAR = THISYEAR - 1 ))
BACKUP_FILE=Data_From_${LASTYEAR}.sql
BACKUP_GZIP=${BACKUP_FILE}.gz
mysqldump ${MYSQL_CONN} mydb mytable --where="date<'%{THISYEAR}-01-01'"| gzip > ${BACKUP_GZIP}
mysql ${MYSQL_CONN} -Dmydb -ANe"CALL sp_mytable_Rotate()"

Based on Vérace's suggestion, if you want to backup the data into a table, an ARCHIVE table can compress the data and have no additional overhead for indexes.
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
THISYEAR=`date +%Y`
(( LASTYEAR = THISYEAR - 1 ))
BACKUP_TABLE=mydata_${LASTYEAR}
MKDT="MAKEDATE(${THISYEAR},1)"
SQL="CREATE TABLE ${BACKUP_TABLE} ENGINE=ARCHIVE SELECT * FROM mytable WHERE 0=1;"
SQL="${SQL} INSERT INTO ${BACKUP_TABLE} SELECT * FROM mytable WHERE date < ${MKDT}"
SQL="${SQL} CALL sp_mytable_Rotate();"
mysql ${MYSQL_CONN} -Dmydb -ANe"${SQL}"

You can then move .frm and .ARZ files of the ARCHIVE table to a designated backup volume.
GIVE IT A TRY !!!


Execution plan percentages don't add up to 100%

The sum of operator costs is more than 100% in execution plan is a known bug and is closed as by design!

Posted by Microsoft on 11/17/2010 at 5:28 PM
Thanks for taking the time to file this observation and simple repro. The strange cost percentage values that you observed are an aritifact of the specific structure of the query plan that are a bit confusing but ultimately do make sense. They will not adversely affect the running of the query in any way.
The concatenation operator has two children - the table scans. However the server expects that it will not be necessary to execute the second table scan. This is because it expects to find a row from the first table scan which would satisfy the "exists" part of the query. Therefore it does not include the cost of the second table scan when computing the total cost of the subtree rooted at the concatenation. However it still estimates and reports the cost of the second table scan, which the management tool the computes as a percentage of the total query cost as if it will be executed.
In this case of course, it would be necessary to run the second table scan since the first is empty. However the engine operates on the conservative assumption that there will always be at least one row (which may only have been added after the query was compiled).
Campbell
SQL Development

Aaron Bertrand filed a similar bug - SSMS : Execution plan sometimes exceeds 100%

Posted by Microsoft on 5/28/2008 at 11:58 AM
Thanks again for bringing this to our attention. This anomaly arise in the presence of "row goals" and the concatenation operator. Row goals is the term for when a subtree (perhaps the entire query) is not required to return all possible rows. This most commonly happens when the query has a "top" clause although there are other causes. The anomaly arises because we do not expect to execute the second child of the Concatenation (because we expect the first child to provide all the required rows). Those additional children are costed to assume they return a single row. The only alternative would be to give them 0 cost which would fix the anomaly but probably create other confusions and would lead the optimizer to not care about the plans - we don't want to do that just in case they are executed.
Campbell Fraser, SQL Development.

If you want to understand how plan costing works, Paul White explains it at his best here.
From the query processor team - What’s this cost?
General guidelines for better performance -

Have proper indexes & up-to-date stats.
Fetch data that is required ONLY along with relevant columns.
Have proper schema.table with proper indexes & datatypes
Use statistics time, IO before and after you make changes to measure the gain.
Get help / guidance from experts at this site :-)

That's why I use SQL Sentry's FREE Plan Explorer


Since you said that most queries would generally return few rows, letting the query use a RID lookup (key lookup in this case as the table has a clustered index) is perfectly fine for retrieving a potentially large field. For a highly-available system, I couldn't recommend putting a LOB type in an index anyway as this prevents online rebuilds (for versions of SQL Server before 2012). Also, you need to be very careful that the query plan always sticks to a seek-type plan and not tip into a table scan which could be very expensive. This is a case where I might use a table hint (or a plan guide if the query can't be modified) even if it isn't absolutely necessary.

Another option is to recreate the clustered index on the combination of parentId and parentType if this combination of values is static and generally increasing over time. It would be better if parentType was an integral type, though, and you may want to look into changing that anyway to save storage space if the base table is, or will become, large. Considering this change also involves looking at how it might affect indexing for the other classes of queries that run against this table.

If either of those two methods aren't fast enough for the workload, look into implementing a data caching solution using something like AppFabric, which scales much more readily than running a SQL query every time you need data. This can be a huge payoff; the cost is added complexity.


You need t o compare product.info with descriptions.info. If the needed condition is for one to be a substring of the other, it can be done with LIKE.

Since there may be more than one descriptions matching (having as substring) a product code, we should use an EXISTS subquery:

SELECT p.id, p.code
FROM products AS p
WHERE p.description IS NULL
  AND EXISTS
      ( SELECT *
        FROM descriptions AS d
        WHERE d.info LIKE '%' + p.code + '%'
      ) ;


I've encountered this issue more times than I can count. For some reason, maybe it's an execution plan difference, if you run it manually and also narrow it down it will complete faster than the job. Figure out the max date in there and then do the cleanup manually:

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 78, @max_distretention = 80
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 76, @max_distretention = 78
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 74, @max_distretention = 76
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 72, @max_distretention = 74

I know this sounds ridiculous, but it is a solution that many of us have had to resort to (I had to google for a solution a few years ago and saw many people needing to do this. One such article). This is one of the reasons why I was so excited to move away from replication when Availability Groups came out.

Make sure you add some monitoring for this condition too that way it doesn't get too far behind when you have to resort to the manual running of sp_MSdistribution_cleanup.


How to query with filter classifications

In Postgres, I would aggregate the labels for each clothe into a JSONB array which can then be queried for "contains all of this" quite easily.
Something along the lines:
select *
from clothes c
  join (
     select cl.clothes_id,
            jsonb_agg(to_jsonb(l) - 'id') as class
     from clothes_labels cl
       join labels l on cl.label_id = l.id
     group by cl.clothes_id
  ) cl on cl.clothes_id = c.id
where class @> '[{"name": "Blue", "classification": "color"},{"name": "Aloha", "classification": "style"}]'
   or class @> '[{"name": "Knit", "classification": "style"}]'
;

Online example

In general I would replace EAV models with a JSON approach these days as they tend to be easier to query and work with (again: assuming Postgres, in MySQL this can't be indexed efficiently)


Was solved using window functions, like:

SELECT e.*
FROM (SELECT e.pageview_identifier,
             e.created_at,
             e.pageview_current_url,
             e.pageview_mobile,
             e.event_type,
             COUNT(*) FILTER (WHERE e.event_type = 'mouse:move') OVER (PARTITION BY e.company_identifier, e.user_identifier, e.pageview_identifier) as cnt_mouse_move,
             ...
      FROM events e
      WHERE e.company_identifier = 'xyz' AND
          e.user_identifier = '01CDQZVSJFBDA8W444JS2CS3BA'
     ) e
WHERE e.event_type = 'page:view';


restore db from .bak file that contains multi backup

try the following:

USE [MASTER]
GO
ALTER DATABASE [spetztest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [spetztest] FROM  DISK = N'c:\sdk.bak' WITH FILE = 74,  REPLACE,  STATS = 1
GO

Make sure that your session that you are using to do the restore is not using the database you want to restore.


Parent -> Child -> Grandchild with a foreign key back to Parent, Bad Practice?

You'll have your reasons for going this route, where it seems you're duplicating information (since id_Products is implied from id_Variants).

However, unless what you want to achieve is having Options with an optional product OR variant (i.e. a CHECK constraint ensuring one and only one of these IDs is not null), you must also ensure that you don't get into an invalid id_Variants + id_Products combination in Options in order to ensure you don't break referential integrity. For this, you need a UNIQUE key on Variants(Id, id_Products) and a FOREIGN KEY on Options(id_Variants, id_Products) REFERENCES Variants(Id, id_Products).


List the underlying objects used in a SQL Server Function

sp_depends will give you list of dependent objects in all current versions of SQL server but is listed as deprecated.

Here is a MSDN article with a way to find objects referencing a function and with small modifications you can get the list of object referenced by a function:

SELECT OBJECT_NAME(object_id) AS referencing_object_name
 ,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name
 ,object_name(referenced_major_id) as referenced_object
 ,*
FROM sys.sql_dependencies
--WHERE referenced_major_id = OBJECT_ID('schema.objectname')
where object_id = object_id('schema.objectname')
ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);

And as a bonus: http://beyondrelational.com/modules/2/blogs/28/posts/10399/tsql-lab-9-how-to-find-the-dependency-chain-of-a-database-object.aspx


It may be a good practice because when you have other users using the database you want to be able to limit their access with schemas. For example in a database you have the following tables.

HR.Payhist
HR.Payscale
HR.Jobdesc
IT.username
IT.useraccesslevel
ENG.jobsite
ENG.trainings

As the HR director I am able to access anything in the HR schema, as the IT director I can see employees usernames and access levels. The Engineering department can see what job sites are active, etc. If dbo was the set schema for all the tables I would have a harder time segmenting out my data and providing access roles.

The idea, I believe, in SQL Server is to offer a product that can be access and queried by different departments. In reality only DBAs/DBDevs really access the database and it typically only stores application data.

It also helps with readability and manageability. At first blush I can easily identify what table holds what data and how the data is separated.

Personally I prefer defining schemas as a general practice. Remember schema is greek for plan, having a laid out schema structure helps you to plan and identify data.


SSIS Derived column giving error on CAST function

You are not using the expression you are showing in your question as evidenced by the error message.

  Attempt to parse the expression "(DT_DATE)DATEADD(DAY,-1GETDATE())"
  failed and returned an error

Your expression with problems is the one for Batch_as_of_Date

(DT_DATE)DATEADD(DAY, -1, GETDATE())

If you don't put double quotes around the first argument SSIS tries to parse it as a column. Since you don't have a DAY column in your input columns so you get this error message:

  Attempt to find the input column named "DAY" failed ....

So your expression would need to be

DATEADD("Day", -1, GETDATE())

This is clearly documented in DATEADD (SSIS Expression)


How can I move a database from one server to another?

My favorite way is to pipe a sqldump command to a sql command. You can do all databases or a specific one.  So, for instance,

mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserverpassword

You can do all databases with

mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver

The only problem is when the database is too big and the pipe collapses.   In that case, you can do table by table or any of the other methods mentioned below.


Privilege to select only user's own tuple only in mysql

You may probably make use of LEFT and INSTR built in functions to strip out the host name from the user@host.

CREATE VIEW mine
AS
SELECT * FROM person
 WHERE `name`=LEFT(CURRENT_USER(),INSTR(current_user(), '@')-1);


Transactional Replication from SQL Server to Azure SQL

This is supported (if I understand your question correctly).

See Transactional Replication to Azure SQL DB now in public preview

Below is the article  written by Jean-Yves (JY) Devant, a Senior Program Manager for SQL Server.

Copying Entire Article ,so to avoid link rot

This new capacity of Transactional Replication is available starting with the following versions\updates of SQL Server:

Community Technology Preview (CTP) 3.0 of SQL Server 2016
SQL Server 2014 Service Pack 1 Cumulative Update 3
SQL Server 2014 RTM Cumulative Update 10
SQL Server 2012 Service Pack 2 Cumulative Update 8

This enables two main scenarios:

Migrate your data to Azure SQL DB with no downtime.
Bridge SQL Server on-premises/on VMs to Azure SQL DB.

The way we enable Azure SQL DB as a subscriber is by extending existing logic of Transactional Replication. From the perspective of your experience with it, the only difference you’ll notice is when you create a subscriber: you give the URL to your Azure SQL DB instead of giving the name of a server or an instance. Because we designed the feature so you have a seamless experience, and you already know about Transactional Replication, the learning curve to use this capacity is 0. It will be very easy and quick for you to start replicating your data to Azure SQL DB.

How does it work? There is "no replication" service per say in Azure SQL DB. The actual replication of data is performed through the Distribution agents.

The chart below shows the architecture from a high level.


The answer from Phil is the solution:

/tmp should be chmod'ed to 777 – Phil 2 days ago

sudo chmod 777 /tmp

(I can not set a comment as answer, so maybe some mod can change my answer to his?)


Even after permissions are updated, I still get the error permission denied for relation users

In addition to privileges on the TABLE, a role needs privileges for the SCHEMA and the DATABASE. The most likely candidate is the schema. If so, this additional GRANT would be the fix:

GRANT ALL ON SCHEMA my_schema TO my_user;

Or at least:

GRANT USAGE ON SCHEMA my_schema TO my_user;


Sql-Server Unique Constraint but with NON-unique data

It seems the underlying constraint index is disabled.  You can verify this with:

SELECT is_disabled
FROM sys.indexes
WHERE
    object_id = OBJECT_ID(N'home_tickets')
    AND name = N'UQ__home_tic__969168722645B050';

After the bad values are remediated, rebuild the index to enable the constraint:

ALTER INDEX UQ__home_tic__969168722645B050 ON home_tickets REBUILD;


Reset Running Total based on another column

I've looked at similar problems and have never been able to find a window function solution that does a single pass over the data. I don't think it's possible. Window functions need to be able to be applied to all of the values in a column. That makes reset calculations like this very difficult, because one reset changes the value for all of the following values.

One way to think about the problem is that you can get the end result you want if you calculate a basic running total as long as you can subtract the running total from the correct previous row. For example, in your sample data the value for id 4 is the running total of row 4 - the running total of row 3. The value for id 6 is the running total of row 6 - the running total of row 3 because a reset hasn't happened yet. The value for id 7 is the running total of row 7 - the running total of row 6 and so on.

I would approach this with T-SQL in a loop. I got a little carried away and think I have a full solution. For 3 million rows and 500 groups the code finished in 24 seconds on my desktop. I'm testing with SQL Server 2016 Developer edition with 6 vCPU. I'm taking advantage of parallel inserts and parallel execution in general so you may need to change the code if you're on an older version or have DOP limitations.

Below the code that I used to generate the data. The ranges on VAL and RESET_VAL should be similar to your sample data.

drop table if exists reset_runn_total;

create table reset_runn_total
(
id int identity(1,1),
val int,
reset_val int,
grp int
);

DECLARE
@group_num INT,
@row_num INT;
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    SET @group_num = 1;
    WHILE @group_num <= 50000
    BEGIN
        SET @row_num = 1;
        WHILE @row_num <= 60
        BEGIN
            INSERT INTO reset_runn_total WITH (TABLOCK)
            SELECT 1 + ABS(CHECKSUM(NewId())) % 10, 8 + ABS(CHECKSUM(NewId())) % 8, @group_num;

            SET @row_num = @row_num + 1;
        END;
        SET @group_num = @group_num + 1;
    END;
    COMMIT TRANSACTION;
END;

The algorithm is as follows:

1) Start by inserting all rows with a standard running total into a temp table.

2) In a loop:

2a) For each group, calculate the first row with a running total above the reset_value remaining in the table and store the id, the running total that was too large, and the previous running total that was too large in a temp table.

2b) Delete rows from the first temp table into a results temp table that have an ID less than or equal to the ID in the second temp table. Use the other columns to adjust the running total as needed.

3) After the delete no longer processes rows run an additional DELETE OUTPUT into the results table. This is for rows at the end of the group that never exceed the reset value.

I'll go through one implementation of the above algorithm in T-SQL step by step.

Start by creating a few temp tables. #initial_results holds the original data with the standard running total, #group_bookkeeping is updated each loop to figure out which rows can be moved, and #final_results contains the results with the running total adjusted for resets.

CREATE TABLE #initial_results (
id int,
val int,
reset_val int,
grp int,
initial_running_total int
);

CREATE TABLE #group_bookkeeping (
grp int,
max_id_to_move int,
running_total_to_subtract_this_loop int,
running_total_to_subtract_next_loop int,
grp_done bit,
PRIMARY KEY (grp)
);

CREATE TABLE #final_results (
id int,
val int,
reset_val int,
grp int,
running_total int
);

INSERT INTO #initial_results WITH (TABLOCK)
SELECT ID, VAL, RESET_VAL, GRP, SUM(VAL) OVER (PARTITION BY GRP ORDER BY ID) RUNNING_TOTAL
FROM reset_runn_total;

CREATE CLUSTERED INDEX i1 ON #initial_results (grp, id);

INSERT INTO #group_bookkeeping WITH (TABLOCK)
SELECT DISTINCT GRP, 0, 0, 0, 0
FROM reset_runn_total;

I create the clustered index on the temp table after so the insert and the index build can be done in parallel. Made a big difference on my machine but may not on yours. Creating an index on the source table didn't seem to help but that could help on your machine.

The code below runs in the loop and updates the bookkeeping table. For each group, we need to get the find the maximum ID which should be moved into the results table. We need the running total from that row so we can subtract it from the initial running total. The grp_done column is set to 1 when there isn't any more work to do for a grp.

WITH UPD_CTE AS (
        SELECT
        #grp_bookkeeping.GRP
        , MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN ID ELSE NULL END) max_id_to_update
        , MIN(#group_bookkeeping.running_total_to_subtract_next_loop) running_total_to_subtract_this_loop
        , MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN initial_running_total ELSE NULL END) additional_value_next_loop
        , CASE WHEN MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN ID ELSE NULL END) IS NULL THEN 1 ELSE 0 END grp_done
        FROM #group_bookkeeping
        INNER JOIN #initial_results IR ON #group_bookkeeping.grp = ir.grp
        WHERE #group_bookkeeping.grp_done = 0
        GROUP BY #group_bookkeeping.GRP
    )
    UPDATE #group_bookkeeping
    SET #group_bookkeeping.max_id_to_move = uv.max_id_to_update
    , #group_bookkeeping.running_total_to_subtract_this_loop = uv.running_total_to_subtract_this_loop
    , #group_bookkeeping.running_total_to_subtract_next_loop = uv.additional_value_next_loop
    , #group_bookkeeping.grp_done = uv.grp_done
    FROM UPD_CTE uv
    WHERE uv.GRP = #group_bookkeeping.grp
OPTION (LOOP JOIN);

Really not a fan of the LOOP JOIN hint in general, but this is a simple query and it was the quickest way to get what I wanted. To really optimize for response time I wanted parallel nested loop joins instead of DOP 1 merge joins.

The code below runs in the loop and moves data from the initial table into the final results table. Notice the adjustment to the initial running total.

DELETE ir
OUTPUT DELETED.id,
    DELETED.VAL,
    DELETED.RESET_VAL,
    DELETED.GRP ,
    DELETED.initial_running_total - tb.running_total_to_subtract_this_loop
INTO #final_results
FROM #initial_results ir
INNER JOIN #group_bookkeeping tb ON ir.GRP = tb.GRP AND ir.ID <= tb.max_id_to_move
WHERE tb.grp_done = 0;

For your convenience below is the full code:

DECLARE @RC INT;
BEGIN
SET NOCOUNT ON;

CREATE TABLE #initial_results (
id int,
val int,
reset_val int,
grp int,
initial_running_total int
);

CREATE TABLE #group_bookkeeping (
grp int,
max_id_to_move int,
running_total_to_subtract_this_loop int,
running_total_to_subtract_next_loop int,
grp_done bit,
PRIMARY KEY (grp)
);

CREATE TABLE #final_results (
id int,
val int,
reset_val int,
grp int,
running_total int
);

INSERT INTO #initial_results WITH (TABLOCK)
SELECT ID, VAL, RESET_VAL, GRP, SUM(VAL) OVER (PARTITION BY GRP ORDER BY ID) RUNNING_TOTAL
FROM reset_runn_total;

CREATE CLUSTERED INDEX i1 ON #initial_results (grp, id);

INSERT INTO #group_bookkeeping WITH (TABLOCK)
SELECT DISTINCT GRP, 0, 0, 0, 0
FROM reset_runn_total;

SET @RC = 1;
WHILE @RC > 0
BEGIN
    WITH UPD_CTE AS (
        SELECT
        #group_bookkeeping.GRP
        , MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN ID ELSE NULL END) max_id_to_move
        , MIN(#group_bookkeeping.running_total_to_subtract_next_loop) running_total_to_subtract_this_loop
        , MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN initial_running_total ELSE NULL END) additional_value_next_loop
        , CASE WHEN MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN ID ELSE NULL END) IS NULL THEN 1 ELSE 0 END grp_done
        FROM #group_bookkeeping
        CROSS APPLY (SELECT ID, RESET_VAL, initial_running_total FROM #initial_results ir WHERE #group_bookkeeping.grp = ir.grp ) ir
        WHERE #group_bookkeeping.grp_done = 0
        GROUP BY #group_bookkeeping.GRP
    )
    UPDATE #group_bookkeeping
    SET #group_bookkeeping.max_id_to_move = uv.max_id_to_move
    , #group_bookkeeping.running_total_to_subtract_this_loop = uv.running_total_to_subtract_this_loop
    , #group_bookkeeping.running_total_to_subtract_next_loop = uv.additional_value_next_loop
    , #group_bookkeeping.grp_done = uv.grp_done
    FROM UPD_CTE uv
    WHERE uv.GRP = #group_bookkeeping.grp
    OPTION (LOOP JOIN);

    DELETE ir
    OUTPUT DELETED.id,
        DELETED.VAL,
        DELETED.RESET_VAL,
        DELETED.GRP ,
        DELETED.initial_running_total - tb.running_total_to_subtract_this_loop
    INTO #final_results
    FROM #initial_results ir
    INNER JOIN #group_bookkeeping tb ON ir.GRP = tb.GRP AND ir.ID <= tb.max_id_to_move
    WHERE tb.grp_done = 0;

    SET @RC = @@ROWCOUNT;
END;

DELETE ir
OUTPUT DELETED.id,
    DELETED.VAL,
    DELETED.RESET_VAL,
    DELETED.GRP ,
    DELETED.initial_running_total - tb.running_total_to_subtract_this_loop
    INTO #final_results
FROM #initial_results ir
INNER JOIN #group_bookkeeping tb ON ir.GRP = tb.GRP;

CREATE CLUSTERED INDEX f1 ON #final_results (grp, id);

/* -- do something with the data
SELECT *
FROM #final_results
ORDER BY grp, id;
*/

DROP TABLE #final_results;
DROP TABLE #initial_results;
DROP TABLE #group_bookkeeping;

END;


Is it possible to COPY tables into PostgreSQL without setting structure first?

If you can live with all columns being text and if the CSV file has at least a header line with column names, then one ugly solution could be:

Create a table with a single column. Then import the first row of the CSV file into that table. You can then get a list of the names and generate a CREATE TABLE statement from that.

Something like:

create table header_names (header_line text);

Then import the first row into that table.

Something like (untested!):

\copy header_names from (program 'head -n 1 data.txt')

\copy and COPY do not have a way of limiting the number of rows that are imported, so you need to use head to just get the first line.

Then use:

select 'create table import ('||string_agg(col_name||' text', ', ')||');'
from header_names
   cross join lateral unnest(string_to_array(header_line,',')) as x(col_name);

The second parameter to the string_to_array() function is the delimiter of your CSV file.

Assuming you have a header like:

id,first_name,last_name

The above will generate:

create table import (id text, first_name text, last_name text);


How to force drop database in SQL Server 2008

That is expected and documented behavior:

  Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer. To remove a database from the current server without deleting the files from the file system, use sp_detach_db.

So why are you taking your database offline first? Just set it into SINGLE_USER mode and then drop it as documented on SQL Server Books Online.-

USE master;
ALTER DATABASE [databasename] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [databasename] ;

Note, database backups will not be deleted as part of the process documented above.


You could run this against each file, replacing each n with the size and growth amounts:

ALTER DATABASE tempdb
MODIFY FILE
(
  NAME       = tempdev,
  SIZE       = nMB,
  FILEGROWTH = nMB
);

ALTER DATABASE tempdb
MODIFY FILE
(
  NAME       = tempdev2,
  SIZE       = nMB,
  FILEGROWTH = nMB
);

...

Now to ensure that they all grow at the same time (instead of only one of them growing, e.g. in response to a large spill or other operation), then you can enable trace flag 1117, but keep in mind that this flag applies to all databases, so if tempdb isn't your only database with more than one data file, you'll want to test this behavior. More info:

http://blogs.technet.com/technet_blog_images/b/sql_server_sizing_ha_and_performance_hints/archive/2012/02/09/sql-server-2008-trace-flag-t-1117.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Tempdb-configuration-survey-results.aspx


Adding a dropped check-constraint what queries the table

By adding the NOT VALID option to the ALTER ... ADD CONSTRAINT statement it's possible to ignore the the validation of the CONSTRAINT on the existed data.

ALTER TABLE layers
    ADD CONSTRAINT layers_name_uniqueness
    CHECK (layers_name_uniqueness_check(bucket, name, parent))
    NOT VALID
;


mysql hardware refresh + version upgrade

I think your plan is largely sound, and is how I handled a similar upgarde (albeit 5.0 > 5.5). What I did was to take a Dump from the Master in the quiet hours like you said, and then set up a new Slave on one of the new 2008r2 boxes.

This allows you time to 'play' with the new 5.6 database so you can tweak the config, and make sure the data replicates correctly.
Bearing in mind that as akuzminsky mentioned 5.0 > 5.6 is a big change, and a lot of things will have changed (binlog_format and GTID to name two of the big ones), and various settings will have been deprecated, or removed entireley.

In this way you get to keep the original Master and two slaves in production use, whilst you get up to speed with the 5.6 database. You can also repeat the process with a fresh install and different settings  if things don't quite work as planned.

Once you are happy with the new slave, then you can set up two additional 5.6 slaves connected to the new 5.6 database giving you a new 5.6 system (master and two slaves), allowing you to do more checks.

Finally, once you are completely happy with the new 5.6 system, you can then simply move your application(s) to look at the new master and slaves at a convenient time.

Also, a bit of advice from someone that's been there, keep an eye on your error logs on the new servers. The later versions are more sensitive to errors, and I suddenly found my error logs filling up very quickly, requiring a bit of time to re-write some of the queries in our applications to make them 'replication safe'.


Is it good to save Keywords stored by users in a new DB schema?

I would have 2 columns: one for the description and one for the keyword.
I would probably use MyIsam (depending on which Mysql version you're using) and use Mysql FullText search features. Innodb supports it now. I would test with both and see which ones is most performant.


c# Backup sql server database, error Operating system error 5(Access is denied.)

You can find the account that your SQL service runs as through the services dialog on the server. That's the account that needs write permission to the directory that you've specified.


Best practice around non-production databases refreshes and TDE

In a perfect world, if you’re storing personally identifiable data in production, you never restore the database to a development environment.

You can’t rely on obfuscation alone. For example, sooner or later, someone’s going to make a backup copy of a table in production. They’re going to select the dbo.Customers table into a new table for safekeeping before they make a change to it, and they’re going to forget to drop it. Your obfuscation code won’t be expecting new random tables with personally identifiable data, and so after the obfuscation process, the data will still be there.

Instead, when you need development data, generate it from scratch.

(Remember: you asked for the best practice, not a compromise. If your response is, “but we want production data in development,” then you’re already missing the best practice.)


Clustered Index

This index is the table.  The index information is stored in the b-tree pages while all of the other columns (those not in the index) are stored at the leaf level of the index.  All other indexes will have a link back to this one and will always include the indexed columns of the clustered index at their leaf level.  This is because if SQL uses a non-clustered index and needs additional information it will use that value to look back into the clustered index for the additional index.  There can be only one clustered index (obviously) because as I said above it actually is the table.

Non-Clustered Index

The indexed values are also located in the b-tree pages but in this case the only data located at the leaf level is the link back to the clustered index and any columns specified by the INCLUDE clause.  In the case of a UNIQUE index there may also be an additional, virtual, column stored at the leaf level called the uniqueifer.

Covering Index

As @KrisGruttemeyer said this is a functional term not structural (like the other two).  This is just a non-clustered index that contains all of the required columns (either in the index or at the leaf level) to satisfy the query. Don't forget that all indexes include the clustered index columns so if you have a query that requires Cols1-4 and Id (the clustered index) and you only have an index that is indexed on Col1 and includes Col2, Col3 and Col4 it will still be covering because the Id column is going to be included since it's the clustered index. Edit to answer the Why part of the question: Because you don't have to make that extra lookup to the clustered index a covering index can be much faster.  The smallest index that fits the query and covers it will probably be the fastest.  Of course you also have to take into account the overhead of having indexes vs the benefit you get for how many queries but that goes way beyond what can be put here.

Just for completeness Heaps

A heap is simply a table that does not have a clustered index.  The page layout is somewhat different from a clustered index but that is beyond the scope of this question.

And because I always include this when talking about Clustered Indexes
The Primary Key

The primary key is a special case of a unique index.  There can be only one and it may be clustered or not.  It has nothing to do with the clustered index but a lot of people get confused and think the primary key is always clustered.


Reading your question literally, "if we delete 1 of 2 records pointing to Table2 from Table1 would also delete referenced record from Table2", you just need an ordinary ON DELETE CASCADE, as you've shown: Table2 references Table1, and deleting a record in Table1 causes all related records in Table2 to be deleted, even if those records are being pointed to by other records in Table1.

However, if your goal is instead to delete from Table2 when there are no more associated records in Table1, whereas there are normally two such records, you'll need something more elaborate.

I think better in concrete terms, so based on your pre-edit question, I'll call Table1 Guardians (because "parents" can be confusing for SQL-heads when they're actually the child half of a relationship) and Table2 Students (similarly, avoiding the word "children").  The goal is to ensure that any students that do not have any guardians get deleted.

If polygamy and divorce are unheard on, one option would be to have two NULLable fields in Students, FatherID and MotherID (or Parent1 and Parent2, to allow for step-parents, gay marriage, etc.).  Set these to reference Guardians with ON DELETE SET NULL, and add an ON UPDATE trigger to Students to delete a record if both fields become NULL.  If Bob's father is deleted but he still has a mother, he's OK, but if his mother is deleted the poor orphan gets wiped from the database.  This assumes that a CASCADE UPDATE event triggers triggers, I don't know that for a fact.

A more robust solution would be to create a third table relating Guardians to Students.  It would include fields GuardianID, StudentID, EffectiveDate (these three making up a candidate key), StopDate, and maybe fields to indicate the rights of the guardian; perhaps only one has legal custody, and the other should not be allowed to remove the student from school grounds.  This would allow for death or divorce: just set the old record's StopDate.  Insert a new record if the remaining guardian ever remarries.  You could create an ON UPDATE trigger and an ON DELETE trigger on this third table, checking to see if there are now any students which lack active guardians.

However, before putting too much engineering into this, consider: do you really need to cascade?  Keep the referential integrity, of course, but just enforce deletion logic in your business logic layer.  For that matter, does it even make sense to delete students?  If you're asked how many students took a specific class in 2011, but you've deleted some because they've subsequently dis-enrolled, you'll have an undercount.


Depends on how sophisticated you want your reports to be.

The simple solution is to create SQL stored procedures that generate emails with reports as embedded/attached HTML or as attached csv files.

The stored procdure can be called from SQL Agent using the flexible scheduling and error reporting that is already built in.


Is it possible to deploy Microsoft PDW on Azure?

Hopefully you have seen the Azure SQL Data Warehouse announcements by now. It is Microsoft's data warehouse as a service offering. The syntax is very similar to PDW and it is also massively parallel like PDW.


Both DBAs are right, in a way. There are no limits on the number of schemas, except those imposed by the filesystem, as in MySQL/MariaDB, a schema is basically a directory, producing absolutely no difference for both MyISAM and InnoDB if a table is part of one schema or another in terms of performance or features (you can join 2 tables in different schemas as fast or as slow as if they were in the same schema). I've managed instances with hundreds of thousands of schemas, and the multiple directories were not the performance bottleneck. It is a pure identifier thing. While you can run into some issues, those are indirect to having more or less schemas: for example, in 5.6, a slave can apply changes in parallel only for rows from different schemas.

Why do I say that both are right, in a way? Because having too many objects in the database is a very common source of issues. When you have lots of databases, you usually have 10x or more the number of tables, and that may mean thousands of file descriptors open (that requires memory), thousands of data dictionary cache entries, millions of open table cache entries, etc., which can lead truly to a CPU and above all, memory, bottleneck.


Does PgBouncer create a separate pool for every database config entry?

Yes, pgbouncer creates a separate pool for each database. You really don't want a pool size as big as 250 in a scenario like this - because that is 250 connections from pgbouncer to the postgres server for each database.

The max of 480 is the total number of connections from clients into pgbouncer, regardless of which database they go into.


'The computer must be trusted for delegation and the current user account must...' error when trying to connect to sql server 2008

According to KB3000850, this affects domain-joined Windows 8.1 computers after installing security update 2992611 or update 3000850.

I added the following value under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Cryptography\Protect\Providers\df9d8cd0-1501-11d1-8c7a-00c04fc297eb

ProtectionPolicy = 1 (DWORD)

Now I can connect.

I found some other postings about this:

https://blog.sqlauthority.com/2017/04/01/sql-server-system-security-cryptography-cryptographicexception-error-generating-xml-document/

https://social.technet.microsoft.com/Forums/windows/en-US/47faab6b-d717-4068-bee4-c694811e0066/credential-manager-problems-error-0x80090345?forum=w8itpronetworking


SHOW INNODB STATUS will give you the last detected deadlock.


you can create user on database level by using the following command:

grant all privileges on MyDB.* to MyUser@'host' identified by 'password';

Note: you can change all privileges to any privileges you want, but you need to make sure its enough for the user to log in and get needed data.


SELECT
   `vote_question_totals`.`question_id`,
    COALESCE(question_total_score,0) AS `score`,
   `question`.*
FROM
   `vote_question_totals` RIGHT JOIN `question`
        ON vote_question_totals.question_id = question.question_id
ORDER BY
   `score` DESC


First, restart the mongos process on mongosHost3, then recheck the locks as you did in the question.

If the same lock (make sure it is the same and not a new lock by the same process - check the ts field etc.) is still present after that restart it is safe to remove it manually, it is basically stale and should be cleaned up eventually in any case. You can remove it just like any other document (assuming you have permissions to edit the config DB of course). MongoDB 3.2 introduced the deleteOne command which I would recommend using here.

It is possible that this is not an issue with a stale lock and is instead an issue with balancing (i.e. it is seeing errors or is stuck/slow for some reason), take a look in the mongos logs of whatever process holds the lock once balancing kicks off again and see what it reports as progress for balancing.

You can also look in the the changelog for more information related to balancing and migrations. If balancing is seeing issues I would recommend getting the logs, changelog information and posting a separate question to troubleshoot that issue.


DO NOT DO #1 AS STATED! -- There are several vital files you have left out.  Capturing the entire mysql tree, plus my.cnf, would suffice.

And #2 is no better!

Without ibdata1, you have lost everything.


Is it possible to create a horizontally partitioned table that is located on two separate sites in Oracle?

Starting with 12c, there is a new feature called Oracle Sharding.

Sharded Database Management

For example you can define the following shardspaces in Global Data Services with GDSCTL:

ADD SHARDSPACE –SHARDSPACE shspace1;
ADD SHARDSPACE –SHARDSPACE shspace2;

ADD SHARD –CONNECT shard1 –SHARDSPACE shspace1;
ADD SHARD –CONNECT shard2 –SHARDSPACE shspace1;
ADD SHARD –CONNECT shard3 –SHARDSPACE shspace1;

ADD SHARD –CONNECT shard4 –SHARDSPACE shspace2;
ADD SHARD –CONNECT shard5 –SHARDSPACE shspace2;
ADD SHARD –CONNECT shard6 –SHARDSPACE shspace2;
ADD SHARD –CONNECT shard7 –SHARDSPACE shspace2;

Where each shard is an independent, seperate database. Then create the tablespace sets as:

CREATE TABLESPACE SET tbs1 IN SHARDSPACE shspace1;
CREATE TABLESPACE SET tbs2 IN SHARDSPACE shspace2;

And create a partitioned table as:

CREATE SHARDED TABLE customers
( cust_id NUMBER NOT NULL
, name VARCHAR2(50)
, address VARCHAR2(250)
, location_id VARCHAR2(20)
, class VARCHAR2(3)
, signup_date DATE
, CONSTRAINT cust_pk PRIMARY KEY(class, cust_id)
)
PARTITIONSET BY LIST (class)
  PARTITION BY CONSISTENT HASH (cust_id)
  PARTITIONS AUTO
(PARTITIONSET gold VALUES (‘gld’) TABLESPACE SET tbs1,
 PARTITIONSET silver VALUES (‘slv’) TABLESPACE SET tbs2)
;


How can I change the data source of a linked report?

If all the databases are on the same instance you could have a central database (named reports, admin or whatever you like). Your report could use that database for your reports datasource.

You could then have a stored procedure that made use of dynamic SQL and a parameter to determine which client database the query should read from.

So your report connects to the reports database and runs the stored procedure with the parameter of 'ClientA', this is passed into the dynamic SQL and executed.

If the databases are in separate instances you would need to use linked servers for this method to work which could cause some security issue so I would look into that before enabling it.


How to select non unique values from 1 column if a value doesn't exist in column 2

You query will need DISTINCT, so it doesn't return the values multiple times:

SELECT DISTINCT a
FROM table
WHERE a NOT IN (SELECT a FROM table WHERE b = 3) ;

Also beware that NOT IN may return unexpected results if your columsn are nullable. It's safer to use NOT EXISTS or LEFT JOIN / IS NULL:

SELECT DISTINCT a
FROM table AS x
WHERE NOT EXISTS (SELECT 1 FROM table AS y WHERE y.b = 3 AND y.a = x.a) ;