Missing right parenthesis after (or before) default 0, Oracle

The problem is with the ordering of the clauses. If you move the CHECK constraint after the DEFAULT 0 clause, then the table creates:

CREATE TABLE ENCUENTROS (
  elocal constraint clave_extrana_equipos_1 references equipos(code) not null enable,
  evisitante constraint clave_extrana_equipos_2 references equipos(code) not null enable,
  fecha date,
  plocal number default 0 not null enable constraint plocal_mayor_cero check (plocal > 0),
  pvisitante number default 0 not null enable constraint pvisitante_mayor_cero check (pvisitante > 0)
);

or

CREATE TABLE ENCUENTROS (
  elocal constraint clave_extrana_equipos_1 references equipos(code) not null enable,
  evisitante constraint clave_extrana_equipos_2 references equipos(code) not null enable,
  fecha date,
  plocal number default 0 constraint plocal_mayor_cero check (plocal > 0) not null enable,
  pvisitante number default 0 constraint pvisitante_mayor_cero check (pvisitante > 0) not null enable
);

Note that I had to give different names to the two foreign key constraints.

BTW, there is a bit of a oddity in your definition: you require a value for local and pvisitante and if none is passed, then you use 0. But the check contraint requires the value to be >0. Meaning that any insert that does not provide a value will fail ... The error will be something like constraint pvisitante_mayor_cero failed validation or similar.

Maybe a simpler approach would be to remove the default clause. This will then make an insert that does not include pvisitante fail with a different error: cannot insert null into ENCUENTROS (PVISITANTE). That may be clearer to application developers.


There is likely an invalid entry in pg_hba.conf file. Reload it and then check the PostgreSQL server log for any pg_hba.conf error:

pg_ctl reload -D $PGDATA


Partial answer:  Given that you need to look up ($z1, $z2):

WHERE zip1 = LEAST($z1, $z2)
  AND zip2 = GREATEST($z1, $z2)

For prep, you have only the PRIMARY KEY(zip1, zip2) and you have filled the table only with smaller zip to larger zip.  And adjusted the 1to2, etc, columns accordingly.


Wasted space in MDF file: Should I just use SHRINKFILE or some other alternative?

It's impossible for us to know if it will affect the application, and it will be extremely difficult for you to determine this either. Even with a 100% like-for-like system and distributed replay clients, you would not notice the millisecond impact the disk IO has, and you would not be able to tell that the application itself is sensitive to that.

The only certainty is if the vendor themselves provides you a solution to prove it one way or the other (i.e. the application itself can do its own load testing) and IMHO very few vendors have such a thing.

Now onto the rest.

Shrink file on a data file is doing a lot IO and this could affect an extremely latency sensitive application - but I've never seen it. If your backups aren't killing you then it's fairly unlikely that this will kill you either. You'd have to already be living on a knife edge of tolerances.

As a DBA you should always cover yourself by letting people know what's going on (either formally through a change ticket or informally through other means). Let them know there's no known or expected impact but that you're available to stop it on a dime in the off chance anything does occur. Shrink file does stop immediately if you cancel it.

But otherwise for me it's a business as usual change with no impact. I'm not in finance or health care so your standards may be higher.

Here's what you do need to know though:

You'll be generating a lot of log records. This means you better be in simple mode, or full mode with very frequent (every few minute) log backups, to ensure you don't start filling your disk.
If you're in full mode you'll have a massive set of DIFF backups (if you do them) up until the next full backup.
On the weekend, or whenever your index maintenance window is, you're going to hit the same case of massive log backups and DIFF backups. This might be a problem if your maintenance does rebuilds and not just reorganisation; rebuilds could put you outside of your maintenance window, will take a massive amount of log or tempdb space while running, and blow up to hundreds of GB. If they're just doing reorganisation though (i.e. no outage) then you'll just see the large log and DIFF backups.

Now all of those latter things definitely can cause outages. But if you have plenty of backup space and disk space for your data and log files, and every-few-minute log backups, and are only doing reorgs, and have sensible small non-percentage based log growth sizes set, then you're unlikely to run into any trouble.


Tracking MongoDB execution times using PHP

As per MongoDB BOL Analyze Query Performance usees The cursor.explain("executionStats") and the  db.collection.explain("executionStats") methods provide statistics about the performance of a query. This data output can be useful in measuring if and how a query uses an index.

db.collection.explain() provides information on the execution of other operations, such as db.collection.update().


Create Language plperl - Error: could not load library plperl.dll

Because I missed 3 files dll (MSVCR90.DLL, PERL514.DLL, IESHIMS.DLL) in Windows I could not install the language pl/Perl.

Here my steps to solve:

Using  "Dependency Walker" tool to check dll files missing (if any). After installing, you open plperl.dll file and find errors.
Install dll files missing.
Install ActivePerl (to install Perl on window, It will automatically set Perl bin folder to windows environment variable)
Restart postgresql service
Test:

    createlang -U postgres plperl postgre  (it's ok)
    CREATE or replace LANGUAGE plperl      (it's ok)

Download (I just can post 2 links)

Dependency Walker tool: http://www.dependencywalker.com/

Reference: http://www.mkyong.com/database/install-perl-in-postgresql-the-specified-module-could-not-be-found/


Is it possible to have unique together constraint on a SET of columns in PostgreSQL?

Yes, you can create a unique index for that:

create unique index on foo ( least(bar1,bar2), greatest(bar1,bar2) );


How to efficiently design Firebase database for Home service Booking application?

your database should look something like this object:

{
  users : { uid, ... },
  bookings: { id, uid, ... },
}

And you access each user's bookings by querying with a condition that the column uid matches the user's id, by using firebase.auth().currentUser if you're using firebase. btw you should Cloud Firestore for better scalability and faster queries.


ORA-04031: unable to allocate X bytes of shared memory

Try doubling shared pool size to 1G, or 1000M.

Java pool should be larger if you're using EJB.

Large pool should be much larger if you're using RMAN.

Remove sessions.

Reduce processes by a LOT, like start with 200.  Are you ever going to use close to 10,000???  XE is not meant for that many processes.

Make job_queue about 20.


How to populate a table's foreign keys from other tables?

Do you already have all the Airports?  Some of them?  None of them?  The details depend on which case you are doing.

Two steps (per normalization table):  INSERT...SELECT DISTINCT ... LEFT JOIN .. WHERE .. IS NULL to add to normalization table.  Then UPDATE ... SELECT ... JOIN... to get the ids back.

The code here is aimed at the "some of them" case, bu probably works for all 3 cases.

If you actually have FOREIGN KEY constraints enabled, be careful of the order of performing operations -- else you could get errors.


How can I get a copy of the SQL generated by Management Studio?

Modern versions of SSMS (2008+ at least) allow you to generate a change script once you have made changes to a table using the graphical designer. You go to the menu item Table Designer > Generate Change Script.

That said, I agree with Thomas. In the long term, you should be proficient at T-SQL so that you can write ALTER TABLE scripts without the help of the wizard / graphical designers. They often do things you don't want to do.


How to solve "Servlet error" when using database control in Oracle 12c?

Database control has been desupported in 12c. You need to use EM Express or EM Could Control


Why is TOP operation in SQL execution plan

Check to ensure the database compatibility level is the same on the 2 servers.  I ran a quick test on a SQL Server 2012 instance and see the TOP operator is introduced if the compatibility level is 100 or lower.  Unless you have a specific reason to do otherwise, it is best to use the 110 (SQL Server 2012) compatibility level on a SQL Server 2012 instance.


On a FOR/AFTER type trigger, it gets fired immediately after each insert, update or delete statement, prior to commit.

An INSTEAD OF trigger works differently and actually provides alternate logic for your insert, update or delete.  It fires in place of your insert, update or delete statement.  In this case it doesn't sound like the right approach.

Use the inserted or deleted logical tables to capture the results of the insert or delete. Since you are doing bulk inserts, make sure to write the trigger to handle multiple rows at a time.

I'm not a big fan of the approach but I've seen a lot of developers using cursors in triggers. That tends to have negative performance impacts so I usually stick to normal insert statements.

One additional point, though, which I should clarify: if you are actually using the T-SQL BULK INSERT statement, it's possible that triggers will be skipped. There is an override, FIRE_TRIGGERS that you will want to make sure to use. From that documentation link:

  FIRE_TRIGGERS
  Specifies that any insert triggers defined on the destination table execute during the bulk-import operation. If triggers are defined for INSERT operations on the target table, they are fired for every completed batch.

  If FIRE_TRIGGERS is not specified, no insert triggers execute.


Retrieve additional columns in recursive CTE

Your fiddle does not open, so I'm not sure if I understand correctly what you have and what you want, but I'd venture to guess you need to join back to comment on id, something like this:

with recursive all_comments as (
   select id, parent_id, id as root_id
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id
   from comment c
     join all_comments p on c.parent_id = p.id
)
select a.root_id, a.comment_count, com.content from
(select root_id, count(*) as comment_count
from all_comments
group by root_id) as a
inner join
comment as com
 on a.root_id = com.id and com.parent_id is null;


Export SQL Server varbinary field to string using Visual Basic script in SSIS?

On line 9, instead of GetType(Byte) you need GetType(Byte()).

Encoding.GetString only takes a byte array; never a single byte, so regardless of the type provided by the framework, what's there is logically not going to work.

If there is another field in the table that comes back as a Byte, you'll need to add another branch to the If statement to handle the encoding (or convert the single byte to a byte array of length 1 and use common encoding code).


As it looked ok in design mode and the issue was happening at run time, it would be helpful to investigate what elements displayed in the Tablix can grow or expand at run time.

I resolved this issue by following the steps below:

1- There was an image in one of the tablixe cells, I checked to see what is the selected option for displaying the image, if its size is bigger than the cell's size. The image had set to accept the growth, so it was increasing the width of the cell and consequently the Tablix size.

2- The padding for all cells of the row needs to be checked, especially it can be simply overlooked when an image has been assigned to a cell in the Tablix. If there is a padding, it would affect the cell size and eventually the row size in the Tablix.

I hope this description helps anyone else experience this issue.


As a_horse_with_no_name's suggestion, the first step you need a CSV file that stores ids. Then, import it to database and execute your query as below

CREATE TEMP TABLE tmp_list_ids (id int);
COPY tmp_list_ids FROM 'your absolute path here' CSV;
SELECT * FROM your_table WHERE id IN (SELECT t.id FROM tmp_list_ids);

Please note that the COPY command is only allowed to superuser (bold is mine)

  Files named in a COPY command are read or written directly by the
  server, not by the client application. Therefore, they must reside on
  or be accessible to the database server machine, not the client. They
  must be accessible to and readable or writable by the PostgreSQL user
  (the user ID the server runs as), not the client ...
  COPY naming a file or command is only allowed to database superusers
  or users who are granted one of the default roles
  pg_read_server_files, pg_write_server_files, or
  pg_execute_server_program, since it allows reading or writing any file
  or running a program that the server has privileges to access.


The alert log should tell you if a data block is corrupt, no need to validate index structure. That will lock your table until completed. If no backups you might try to exporting the data using expdp. Then drop the table and re-create it and import using impdp. You will need to create the indexes, constraints and grants as well. Could also use CTAS.


Is it necessary for me to relocate one or two listeners running on node 2 to node 1?

Its not necessary to relocate these SCAN listener manually, these will be failed over to another node automatically.

  Will my connections still be load balanced even if I have no scan listeners running in node 1 and all the 5 are running in node 2? If yes, how?

SCAN resolves in one of the IP addresses(In your case, its five) in round-robin fashion, so we don't need to worry about the load-balancing on SCAN.

Oracle strongly recommends that you do not configure SCAN VIP addresses in the hosts file. If you use the hosts file to resolve SCAN name, you can have only one SCAN IP address.

Once the SCAN is resolved it tries to find least loaded oracle instance to hand over client connection request to local listener, in your case you have two local listeners running on two different nodes.

I would strongly recommend the following white paper published by Oracle to grasp more information about SCAN in Oracle RAC.

  When does one actually need to relocate the scan to another node?

If the node on which a SCAN VIP is running fails, the SCAN VIP and its associated listener will fail over to another node in the cluster. In your case you have two nodes so one pair of resource(SCAN VIP and listener) is running on one node and rest of these are running on another node. Once your first node hosting the SCAN VIP and listener goes down Oracle Clusterware relocates this resource to second node. If a node becomes available in the cluster again, the formerly mentioned dispersion will take effect and relocate one set accordingly.
More...


Best way to put date range in a table in mySQL

Note: not a MySQL guy, I use MS SQL. I would try to focus less on if a room is available at the start, and try to focus on marking the dates the room is reserved. If you use a table that stores reservations;

CREATE TABLE [Reservations] (
ReservationID INT NOT NULL,
RoomID INT NOT NULL,
StartDate SmallDate,      --first day of reservation
Duration INT)             --number of days being reserved

Then when you want to see what rooms are available for a day, you can search for what rooms are being used that day (using the startdate and duration columns) and get the inverse. This also frees you up from trying to allocate space for each possible reservation slot, but just add a record into the reservations table when they are reserved.


Oracle 11G Can't create database from template

The last two entries have unbalanced parenthesis. You're not closing the top-level parens.

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Test)
    )
  )              -- add this
med =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = med)
    )
  )              -- add this

(Don't add the comments though.)

Might be a good idea to be consistent with casing everywhere (even though I'm not sure it actually matters). If you call your database TEST, put TEST everywhere, not Test in some places and TEST in others.


Based on your comment about the "unwieldy" SP I'd almost guarantee that is at least part of your problem.  Try running the following query replacing DBName and SPName as appropriate.

USE DBName
GO

SELECT SUBSTRING(text, statement_start_offset/2,
        CASE WHEN statement_end_offset = -1 THEN 999999 ELSE
            (statement_end_offset-statement_start_offset)/2-1 END)
    ,*
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) query
WHERE query.dbid = db_id('DBName')
  AND query.objectid = object_id('SPName')

This will break down your SP into pieces and you can sort by one of the elapsed_time, logical_reads columns, or total_worker (cpu) columns to decide what specific commands you want to work on first.  The actual command will be the first column.

By breaking it down like this you can start picking off the low hanging fruit and speed the over all SP a bit at a time.  This doesn't of course take the place of reviewing the whole thing at once.  For example it will help to reduce the speed of a query that's being run a dozen times in the SP, it will help even more if you can figure out a way to run it only once (personal recent experience).

Now to be fair this doesn't answer your original question and it is more meant as a response to the comment and a helpful way to deal with a "problem" sp.


Cleaning up transaction replication del/ins/upd stored procedures?

When you remove a subscription nothing is done to the subscriber.  You'll need to remove these procedures manually.  They are safe to remove is replication has been removed.


Can you use an inserted variable in a default constraint

No, default constraints cannot depend on other columns.  Further, neither default constraints nor computed columns can reference other tables (unless you define a scalar udf - not recommended!).  As mentioned, you can achieve this constraint using triggers.

I'd recommend you create and use a view over these two tables.  Select from the tables joined together and compute the data you want.


User-defined session-scope variables, beginning with @ are not declared and are loosely and implicitly typed.  Change DECLARE to SET and drop the INT after the variable name if these are the variables you intend to use.

Program variables are declared and typed, and do not start with @.

http://dev.mysql.com/doc/refman/5.6/en/user-variables.html
http://dev.mysql.com/doc/refman/5.6/en/stored-program-variables.html


What impact does UseMicrosoftUpdate have on the UpdateSource option when installing SQL Server 2014?

UseMicrosoftUpdate - Setting it to TRUE - it will change the computer settings to include the latest updates when you scan for Windows Update.
I would suggest to set it FALSE and you decide what updates you want for SQL Server in-terms of SPs/CUs.
From BOL :

UpdateEnabled - Specify whether SQL Server setup should discover and include product updates. The valid values are True and False or 1 and 0. By default, SQL Server setup will include updates that are found.
UpdateSource - Specify the location where SQL Server setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default, SQL Server setup will search Microsoft Update or a Windows Update Service through the Windows Server Update Services

So the UseMicrosoftUpdate is more of a global thing governed as part of windows update vs UpdateEnabled is governed only during sql server installation/setup and UpdateSource is the location that will have sql server update files. Thats my understanding.
Honestly, I will not even enable any of those, since any SP/CU has to be regression tested and lately there are many problems with SP/CUs that can screw up many things. They are addressed with subsequent hotfixes.
With introduction of SQL Server 2016, Microsoft has made changes to their Incremental Servicing Model (ISM)

As of January CU releases, we now recommend ongoing, proactive installation of CU’s as they become available. You should plan to install a CU with the same level of confidence you plan to install SPs (Service Packs) as they are released. This is because CU’s are certified and tested to the level of SP’s.


Unable to query for thread information in mysql using root

This is something you quickly learn with GCP (CloudSQL), AWS (RDS), and all Cloud Providers of MySQL: Even root does not have all privileges.

You could run SHOW GRANTS when logged in as root. Regardless of the privileges it says it has, the Cloud Provider will restrict it.

You asked for a difference between root@localhost and root@%

root@localhost will allow you to connect to mysqld from within the OS of the MySQL Instance. This requires access mysqld via the socket file. Using any
root@% will allow you to connect to mysqld from any server via TCP
Please read my post about how mysqld authenticates users (MySQL error: Access denied for user 'a'@'localhost' (using password: YES))

Sadly, regardless of which root user you use and what SHOW GRANTS says you have, the Cloud Provider will preempt any operation that uses SUPER and PROCESS. I have written 7 posts in the past 4 years describing how Amazon does this with RDS. So, it is not surprising Google does the same thing with CloudSQL.

After all, they don't want to expose the MySQL Container with fixed log file sizes, log file locations, datadir location, and the OS itself.


PostgreSQL function's input/output as JavaScript nested objects

JavaScript nested objects can be easily (and I'd say "naturally") represented using JSON. This is "by definition" of what JSON (JavaScript Object Notation) is.

So, in your use-case use JSON

On the JavaScript side:

To parse the returned values from PostgreSQL (as strings), use JSON.parse().
To convert JSON to a string that can be included in a SQL statement, use JSON.stringify().
You can also use some libraries, such as JSON-js, although I've never used it myself.
See also Safely turning a JSON string into an object.

In PostgreSQL, on top of using json data type, you might also use jsonb.

It's got a number of advantages (smaller size, indexability, more functions), and a few differences with json (order of fields is not preserved, you can't have {"a":1,"a":2}, and a few other smallish differences that normally don't matter).
See Explanation of JSONB introduced by PostgreSQL. Look also at the second answer.

JSON is not only available in PostgreSQL. Most current databases (MySQL, MS SQL Server, Oracle, SQLite with the JSON1 extension, among others) also support it.


In order to validate your partitioning scheme, you can use the $PARTITION system function, detailed on MSDN at http://msdn.microsoft.com/en-us/library/ms188071.aspx

  Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function

So for your example, you could run:

SELECT $PARTITION.FNArchived (1);

or

SELECT $PARTITION.FNArchived (0);

To determine which partition each value will be stored in.


Convert table from MyISAM to InnoDB and keep MyISAM rows order

@a_vlad has your answer... create a new table with the same structure without keys and autoincrement (it is faster to manage indexes in batch than one row at a time).  Then insert into the new table select * from the old.  After population, any indexes (including the pk) can be applied and add autoincrementation advanced to the next value you want to generate.


Window Azure SQL Server DB Backup to a local-Premise

I would ditch Azure Sync by all means .. buggy and still in preview since ages.

We have exactly same requirement and what I did was create SSIS package and load the data from Azure to OnPremise on a nightly basis. It works just fine once you set it up. Make sure to load the data as per FK relationship.

On other side, you can explore Windows Azure SQL Database Import/Export Service.


Aggressive Under-Indexing and no data for missing index

Free Unit

Since you're using the First Responder Kit, I'll stick to using it in my examples to teach you how to diagnose this further.

No Missing Indexes?

Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?

First, Wait

Use sp_BlitzFirst @SinceStartup = 1;, and check out your wait stats.

If LCK_ waits aren't near the top of your list (top 10 or so), and if the Avg ms Per Wait column isn't showing values over 1-2 seconds for the LCK_ waits, the situation might not be so dire. I'd probably switch my focus elsewhere.

Modifiers

Rather than using sp_BlitzCache to look at Reads or Duration, use it to look at these:

EXEC sp_BlitzCache @SortOrder = 'writes';
EXEC sp_BlitzCache @SortOrder = 'avg writes';

You may find queries that modify a lot of data at once. If you do, changing them to batch modifications can reduce the locking overhead.

The problem with this approach is that it relies on execution plans being in the cache when you're looking for them. They may not be, for various reasons.

Since you're running sp_BlitzCache, pay attention to the line in the Warnings rollup (second output);

Common Problems

Long locks can come from places that end users may not notice, like index maintenance.

If this is the source, you may want to consider scaling back the fragmentation level you perform maintenance at (50-80%), the frequency of the maintenance (weekly or monthly), or just not doing it at all and updating statistics only (much less invasive and less prone to blocking).

Look for code patterns that use BEGIN TRAN, then do a ton of work before committing. This requires you do a code review. There's no shortcut here, and it's typically the kind of thing you want a monitoring tool to do.

Look in sp_BlitzIndex for foreign keys with cascading actions. These can cause a dramatic amount of locking.

Hope this helps!


How do you tune MySQL for a heavy InnoDB workload?

Interestingly, in MySQL 5.5, you can now have multiple innodb buffer pools.
The parameters you care about are

innodb_buffer_pool_instances (default is 1)
innodb_buffer_pool_size (default is 1G)

In about a month, I am slated to implement 112 innodb buffer pools for a client. I'll let you know how it went.
UPDATE 2011-02-27 21:57 EDT
I found out the max value for innodb_buffer_pool_instances is 64
I decided to configure 144 GB , so I set innodb_buffer_pool_instances to 18 and innodb_buffer_pool_size to 8. I am currently loading the server with 450GB
UPDATE 2011-04-28 13:44 EDT
I tried out multiple InnoDB Buffer Pools. There was too much threading locking and contention. I changed over to a single 162GB Buffer Pool + setting read_io_threads and write_io_threads to 64 (maximum value). This worked way better.
UPDATE 2012-07-03 17:27 EDT
I learned something amazing about MySQL. If you allocate a single monolithic InnoDB Buffer Pool that is bigger that Total Installed Divided By Number of Physical CPUs, your will incite the OS to regular intervals memory swapping due to a full InnoDB Buffer Pool. MySQL 5.5's option known as innodb_buffer_pool_instances can be used to split up the buffer pool. Yesterday, I properly implemented this for the client I mentioned in my answer last year. I still have 162GB for the client's Buffer Pool. I have set the server's innodb_buffer_pool_instances option to 2 because each DB Server is dual hexacore. I was thinking of setting it to 12 but then a colleague showed me a blog from Jeremy Cole on MySQL and Swappiness. After reading it, I put it into practice immediately for my client. I ran this command
numactl --hardware

I saw a mapping of 192GB of server RAM as 96GB to each physical core. Therefore, I set the innodb_buffer_pool_instances to 2. Things are looking good right now. I will update my answer to see how this affects memory swapping for the next 2 montns.


how to professionally normalize my database - screenshots included

You have

a customers table.
a jobs table.

You normalize the 3 Profession columns by moving that information into a linking table which contains

Customer ID
Job ID
something to represent "1st","2nd","3rd" Profession
Constraints to ensure the data is valid

To get the data back in the original form, use a VIEW.

Example:

create table customer_jobs (
    customer_id int not null
    ,job_id int not null
    ,order_by number(1) not null -- defines "1stProfession","2ndProfession","3rdProfession"

    -- ensures that each customer has only one "1st Profession", not two "1st Professions
    ,constraint customer_jobs_pk primary key (customer_id, order_by)
    -- ensures each customer claims a job once and only once
    ,constraint customer_jobs_uq1 unique (customer_id, job_id)
    -- ensures that the customer is a known customer
    ,constraint customer_jobs_fk1 foreign key (customer_id) references customers (id)
    -- ensures that the job is a known job
    ,constraint customer_jobs_fk2 foreign key (job_id) references jobs (id)
    -- Limits to only 1st, 2nd, and 3rd Professions
    ,constraint customer_jobs_ck1 check (order_by between 1 and 3)
);

Repeat this method with the *BestMovie columns


Create database role to allow read access to all tables; write access to some

Granting permissions on the schema (e.g. dbo) will cascade to all the objects in that schema. For individual exceptions you can just list those explicitly:

GRANT SELECT ON SCHEMA::dbo TO [role];
GO

GRANT INSERT, UPDATE --, DELETE
  ON dbo.table_they_can_write_to TO [role];

DENY SELECT ON dbo.table_they_cannot_read TO [role];


Drop multiple columns at the same time from a SQL Server table

You're currently looking for a column_name that has different values, so you'll never pass the test. You have to search in different rows to make it work.

    IF EXISTS(SELECT 1
              FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_NAME='my_Table'
              and column_name= 'ID')
AND EXISTS (SELECT 1
              FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_NAME='my_Table'
              and column_name= 'Relationship')
      BEGIN
        ALTER TABLE my_Table
        DROP COLUMN
         ID
        ,Name
        ,Relationship
      END


Restore backup fails - not enough disk space

Note that the backup size does not include empty pages, but when you actually perform the restore, the data and log files will be over 200 GB, because it has to restore exactly what the source system had (including a 200+ GB log file, regardless of how full it was).

If you don't want to risk data loss, you need to correct that at the source (e.g. shrink the log file to something reasonable), take another full backup, and restore that. I'm not sure I understand why it's difficult to get a backup - this is a pretty standard operation, and should be a service provided by anyone you're paying to host SQL Server.

You should also fix the source database to either (a) be in the right recovery model or (b) take log backups more frequently. Your log file is ludicrous because you are in full recovery and never take log backups. If you need point in time recovery, start backing up your log. If you don't, switch to simple. The log file should manage itself if you have it configured correctly. If you don't, then when it gets like this, shrinking should be a one-time operation, and then you should fix the configuration so you're not doing this again next week...


Proper way to create logins on Always on

Logins and users work the same way they do with DBM or log shipping. If using SQL Server authentication, you have to sync the SIDs manually (or manually create the login with the right SID) assuming the login is already created on the other instance.

Either way, this is not automatically done. You need to create the login at the instance level to match the DB user. If using Windows authentication, it should have the same SID, but you still need to create the login at the instance level.

Remember, the instance-level login sits outside the AG, so the AG won't do anything there. Not sure why you thought the AG would work this way; it never has, just like DBM and log shipping.

Also, there is no feature called Always On. Please do not refer to AGs that way. http://sqlha.com/2013/04/29/alwayson-is-the-new-activepassive-and-activeactive/


For Postgres, efficiently get points within X distance of point Y?

I only need the distance to be accurate on a local scale and so curvature of the earth shouldn't be an issue for my application, but is there a performance benefit of either POINT or GIS in this context?

The native point is for geometry. If you want distance on Earth then those "points" are being projected onto a representation of Earth (sphereoid) and you need GIS. If you're using PostgreSQL that means you need PostGIS. For a PostGIS query that returns all points in a table within a 1 km (1000 m) distance of a specific point, you can use ST_DWithin

SELECT *
FROM tbl
WHERE ST_DWithin( tbl.geog, ST_MakePoint(x,y)::geography, 1000 );

That will also use a GIST index (if any) on tbl.geog.


JOIN Call table to grouping by Origination and Destination

I think you need to change one join column on the inner join from tbl_calls to  tbl_origination_clients. You have currently c.des_client_id but it should be c.orig_client_id.

Query with adapted join column

select
  co.country Origination,
  cd.country Destination,
  sum(c.call_duration) TotalDuration
from tbl_calls c
inner join tbl_destination_clients d on d.des_client_id = c.des_client_id
inner join tbl_origination_clients o on o.orig_client_id = c.orig_client_id
inner join tbl_countries cd on cd.country_id = d.country_id
inner join tbl_countries co on co.country_id = o.country_id
group by
  co.country,
  cd.country;

Result

| Origination | Destination | TotalDuration |
| ----------- | ----------- | ------------- |
| Austria     | Denmark     | 9             |
| France      | Brazil      | 11            |
| France      | Lebanon     | 10            |

DB-Fiddle


Is there something I can do to make this UPDATE run faster?

In addition to being slow, the statement's results will not be what you expect them to (which causes it to be slow):

From the PostgreSQL documentation:

  from_list A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement. Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).

AFAICT you are updating each row of enumeratedsensorreading with values obtained by performing an unqualified join (no WHERE-clause) of enumeratedsensorreading against (a qualified join of enumeratedsensorreading against sitequantitysensor (FROM-clause)). This will result in at least 600k updates per row (depending on whethe the relevant values in sitequantitysensor are distinct).

Simply removing the second join should do the trick:

WITH sensor_ids AS
  (SELECT DISTINCT sensorid, siteid, quantityid FROM sitequantitysensor)
UPDATE enumeratedsensorreading e
  SET sensorid = s.sensorid
  FROM sitequantitysensor s
  WHERE s.siteid = e.siteid AND s.quantityid = e.quantityid;

The join you intended is already defined by FROM- and WHERE-clause


Simple recursive CTE bombs out when changing operator in the WHERE clause

If you model root nodes as having parent=child, you must exclude them from the recursive clause or you'll get an infinite loop.  Your s.TopDate < d.TopDate is currently the only thing in your query preventing an infinite loop.

EG, if you change < to <= the query will enter an infinite loop.  Note this is why (maxrecursion 0) is perhaps not a good idea.

drop table if exists #Recursion
go
create table #Recursion(parent int, child int, topdate datetime)
go
insert into #Recursion(parent,child,topdate) values (1,1,getdate());

;WITH
Parent AS
  ( SELECT parent
         , child
         , 1 as [order]
         , TopDate
     FROM #Recursion
    WHERE parent = child
 UNION ALL
   SELECT s.parent
        , d.child
        , d.[order] + 1
        , s.TopDate
     FROM Parent as d
     JOIN #Recursion s
       ON s.child = d.parent
    WHERE s.TopDate < d.TopDate
)

  SELECT parent
       , child
       , [order]
    FROM Parent
ORDER BY parent, [order] desc
OPTION (maxrecursion 0);

Also

ON s.child = d.parent

should probably be

ON d.child = s.parent

As you appear to be starting at the root nodes, so you need to recurse down by UNION ALL-ing rows whose parent is the previous iteration's child.


Disk space recovery on Mongodb replicaset secondaries

Compact will not recover space, because it really just defragments the data inside the existing data files, making the data more dense, more compact.  In fact, a compact occasionally needs extra space to do its work and so can end up using up more space rather than reclaiming it.

Because a repair rewrites the data files from scratch, it reclaims the "wasted" space and recovers disk space.

Besides bringing the secondaries down and running a repair there as you mention, another option is to delete the data files from a secondary and re-sync them from the primary from scratch.  This has a similar effect to a repair, because in both cases the data files are re-written, the difference being that you don't need ~2x the disk space because you wipe out the originals and pull the data from the primary.

Once you have done the first secondary, generally the quickest way to do the second would be to snapshot (with journaling enabled) or fsync and lock the secondary and just to a file based copy to seed the other secondary.

Your final option assuming you have run the repair already on the primary, which is really just a variation, would be to snapshot the primary (or fsync and lock) and use its repaired files as a seed to populate a secondary.

These methods are covered in more detail as part of the Backup docs:

http://www.mongodb.org/display/DOCS/Backups#Backups-Methods


SQL Server 2008 Full-Text Search installation

Full-Text Search is a feature on the installation media. You will need the installation media or binaries and go through setup and add the feature to an existing installation of SQL Server. You cannot download Full-Text search as a standalone installation.


What does Reinitialize All Subscriptions actually do to the databases

Initializing or re-initializing a subscription consists of applying (or re-applying) a snapshot of the publication to that subscription.

This consists of dropping subscribing tables at the subscriber, and recreating them containing the same data as the snapshot. The schema of the relevant tables at the subscriber will therefore match the schema at the publication following a re-initialization.

The Snapshot Agent, which lives on the Distributor, generates the snapshot, although when re-initializing a subscription you can opt to use the existing snapshot. The Distribution Agent then reads the snapshot from the snapshot folder and applies it to its subscription. The Distribution Agent lives on the Distributor for push subscriptions, and on the Subscriber for pull subscriptions.


Azure SQL Database and Database Maintenance

Once you have created an Azure SQL Database you can use all tools listed on the following image to provide maintenance:

Regarding to Ola Hallengren's maintenance scripts, you can install them using psdbatool on a Managed Instance

Learn more about psdbatool here.

You can also install them manually.

And run them.

EXECUTE dbo.IndexOptimize
@Databases = 'mydbnamehere',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

You may also need Azure Automation to schedule execution.


Deduplication/normalization of string column on insert

Here is a rough sketch. I could only test with SQLite 3.8 and it appears as if UPSERT is introduced in 2018-06-04 - Release 3.24.0. I.e. the following is untested, but hopefully you can make something out of it anyhow

create table Texts
( tid integer not null primary key AUTOINCREMENT
, textval varchar(20) not null unique);

create index x1 on texts (textval);

create table T
( x int not null
, tid int not null references texts (tid)
, primary key (x, tid) );

create view v as
   select t.x, texts.textval
   from t
   join texts
       on t.tid = texts.tid
;

CREATE TRIGGER trig1
INSTEAD OF INSERT ON V
BEGIN
        -- insert unless textval is already in place
        INSERT INTO texts (textval)
        VALUES (NEW.textval) ON CONFLICT (textval) DO NOTHING;

        -- lookup tid for textval
        insert into t (x, tid)
        select NEW.x, texts.tid
        from texts where texts.textval = NEW.textval;

END;

rowid appears to be recommended over autoincrement, but that is sort of beside the point so I used it anyhow.

For 3.8 I used this ugly workaround:

CREATE TRIGGER trig1
INSTEAD OF INSERT ON V
BEGIN
        -- insert unless textval is already in place
        INSERT INTO texts (textval)
        SELECT NEW.textval FROM (VALUES(1))
        WHERE NOT EXISTS (
            SELECT 1 FROM texts WHERE textval = NEW.textval
        );

        --lookup tid for textval
        insert into t (x, tid)
        select NEW.x, texts.tid
        from texts where texts.textval = NEW.textval;
END;

You can try it at DB<>Fiddle. Test:

insert into V (x,textval) values (1,'a');
insert into V (x,textval) values (5,'bb');
insert into V (x,textval) values (15,'a');

select * from v;
x   textval
1   a
5   bb
15  a

select * from texts;
tid textval
1   a
2   bb

select * from t;
x   tid
1   1
5   2
15  1


How to best encapsulate monetary values in a database

From a database (and client) point of view, you need to do a few things.

The first thing you need to consider is that you have to use integers for everything and have a number of implied decimals for each currency (eg: $2.00 is actually 200 with 2 implied decimals. The $2.00 part is a client formatting issue and a database maths issue.). Floating point never comes into it - you don't want to go near the mathematical implications involved in using floating point.

My advice: Have a currency table with the currency_id (PK), currency_name and decimals fields. Any other table then just needs a currency_id and amount column to relate to the original table. Formatting is then just a join between the two. Maths between any tables with the same currency is then just straight integers.

Maybe add a few more columns to the currency table for leading and trailing symbols (eg: '$' for prefix, '.' for decimal spacer) for formatting.


Approximation of table size for MySQL cluster

You will need some more space for row overhead and PK storage. More information you can find in MySQL Documentation.

For the most detailed information you can use ndb_size.pl utility.


Not quite.

If the PK is A,B,C, you already have that unique index.

A,B,C,D is a covering index for queries that need exactly those four columns, but would be better as A,B,C INCLUDE D

A,D,B,C is definitely not redundant; it's the only one that can work with both A and D on queries that use those two but don't use B or C in the index.  Depending on the cardinality of A and D, it might be critical, or worthless... but it's not redudant.

Remember, SQL Server starts at the left and goes to the right; when it hits a column that's not used, it stops.


How to use a conditional ORDER BY in MySQL?

A case expression returns an atomic value so you can't return:

favoritedDate, dateUpload

I think this will do what you want:

ORDER BY
    CASE favorited WHEN 1 THEN favoritedDate
                          ELSE dateUpload
    END DESC, dateUpload


Percona Xtrabackup on Suse EL 10 -- Failing

I guess you are under a wrong impression that it is a OS related issue.
Check your server bit if it is 32 bit and if you try to install 64bit package percona's xtrabackup . It would end up throwing such error messages.


You could do this:

SELECT COUNT(*) FROM table1 t1 CROSS JOIN table1 t2;

or this format which will allow you to copy and paste any query without rewriting it and use it as a subquery:

SELECT COUNT(*) FROM (
  SELECT t1.firstname, t2.lastname
  FROM table1 t1 CROSS JOIN table1 t2
) t3;


What exactly can SQL Server 2014 execute in batch mode?

What exactly can run in batch mode as of SQL Server 2014?

SQL Server 2014 adds the following to the original list of batch mode operators:

Hash Outer join (including full join)
Hash Semi Join
Hash Anti Semi Join
Union All (Concatenation only)
Scalar hash aggregate (no group by)
Batch Hash Table Build removed

  It seems that data can transition into batch mode even if it does not originate from a columnstore index.

SQL Server 2012 was very limited in its use of batch operators. Batch mode plans had a fixed shape, relied on heuristics, and could not restart batch mode once a transition to row-mode processing had been made.

SQL Server 2014 adds the execution mode (batch or row) to the query optimizer's general property framework, meaning it can consider transitioning into and out of batch mode at any point in the plan. Transitions are implemented by invisible execution mode adapters in the plan. These adapters have a cost associated with them to limit the number of transitions introduced during optimization. This new flexible model is known as Mixed Mode Execution.

The execution mode adapters can be seen in the optimizer's output (though sadly not in user-visible execution plans) with undocumented TF 8607. For example, the following was captured for a query counting rows in a row store:

  Is using a columnstore index a formal requirement that is necessary to make SQL Server consider batch mode?

It is today, yes. One possible reason for this restriction is that it naturally constrains batch mode processing to Enterprise Edition.

  Could we maybe add a zero row dummy table with a columnstore index to induce batch mode?

Yes, this works. I have also seen people cross-joining with a single-row clustered columnstore index for just this reason. The suggestion you made in the comments to left join to a dummy columnstore table on false is terrific.

-- Demo the technique (no performance advantage in this case)
--
-- Row mode everywhere
SELECT COUNT_BIG(*) FROM dbo.FactOnlineSales AS FOS;
GO
-- Dummy columnstore table
CREATE TABLE dbo.Dummy (c1 int NULL);
CREATE CLUSTERED COLUMNSTORE INDEX c ON dbo.Dummy;
GO
-- Batch mode for the partial aggregate
SELECT COUNT_BIG(*)
FROM dbo.FactOnlineSales AS FOS
LEFT OUTER JOIN dbo.Dummy AS D ON 0 = 1;

Plan with dummy left outer join:

  Documentation is thin

True.

The best official sources of information are Columnstore Indexes Described and SQL Server Columnstore Performance Tuning.

SQL Server MVP Niko Neugebauer has a terrific series on columnstore in general here.

There are some good technical details about the 2014 changes in the Microsoft Research paper, Enhancements to SQL Server Column Stores (pdf) though this is not official product documentation.


Maxscale read write split and route filter

The problem was not maxscale's.

I found out that even if the count of routed requests to the slave was incrementing, no connection was ever made to the slave.

Here is a functional status with established connections to the slave, whereas I had always 0 connection before:

maxadmin -pmariadb show servers

Server 0x46d6010 (master-db)
    Server:                         X.X.X.X
    Status:                         Master, Running
    Protocol:                       MySQLBackend
    Port:                           3306
    Server Version:                 10.0.21-MariaDB-wsrep-log
    Node Id:                        1
    Master Id:                      -1
    Slave Ids:                      2
    Repl Depth:                     0
    Number of connections:          54
    Current no. of conns:           0
    Current no. of operations:  0
Server 0x46306a0 (slave-db)
    Server:                         127.0.0.1
    Status:                         Slave, Running
    Protocol:                       MySQLBackend
    Port:                           3306
    Server Version:                 10.0.22-MariaDB
    Node Id:                        2
    Master Id:                      1
    Slave Ids:
    Repl Depth:                     1
    Number of connections:          54
    Current no. of conns:           0
    Current no. of operations:  0

The reason was that the slave went into unsynced mod after testing database creations and deletions because I'm only binlogging one database.

I had to redo the slave with a dump from the following command for innodb databases:

mysqldump -u root -p*** --master-data --single-transaction mydb | gzip > dump.sql.gz

After that, all selects were correctly routed to the slave without the need of a filter.


sqlservr.exe uses lots of CPU, while SQL queries does not seem to use much CPU at all

You have multiple instances of SQL Server running, and I suspect you are checking the wrong one for CPU activity.

In this system, I have two instances of SQL Server. In the plain Task Manager view I just see multiple SQL Server processes:

But if I expand each item, I can see the actual instance name involved with each:

In older versions of Task Manager you should be able to add process id (or inspect the user associated with the process):

If you can't identify the instance by name there, then you could look up the process id in Configuration Manager:

This makes it easy - in my case I know that the S1TARGET instance is the one that's using the CPU, so that's where I'm going to run queries like the one in the question.


How to free space after deleting the millions of rows from a table

alter table TABLENAME move;

followed by rebuilding the indexes on that table should do what you want.

After 'moving' the table around, the indexes for the rows will become unusable (as you can verify with something like):

select index_name, status from all_indexes
 where table_name = 'TABLENAME' and
       owner      = 'SOMEUSER';

So, you would rebuild such an unusable index with a

alter index TABLENAME_IX_01 rebuild;


How to configure postgresql-9.2 to know blk_write_time in pg_stat_database

configuration above is correct. Before viewing the blk_write_time, do a vacuum. If you still see a 0 try increasing the inserts from 2000 to say 1000000.

Step 5:
vacuum;
Step 6:
select datname, blk_write_time from pg_stat_database;


"Restore With Replace" or drop/restore with Instant Initialization on?

A RESTORE DATABASE ... WITH REPLACE will completely overwrite the database and database log files without the need to drop them first.

On a replace the database settings are also repopulated in master.  For example, if a database was in SIMPLE recovery model, but the RESTORE WITH REPLACE was in FULL recovery model, the restored database would still be in FULL recovery mode.

Additional note: RECOVERY can be considered risky, since it skips over certain safety checks.  See REPLACE Option Impact at http://technet.microsoft.com/en-us/library/ms186858.aspx

But if you know your restore is replacing the correct files then it works like a charm.


I think the shortest and most elegant way to solve this issue is to use date_trunc('quarter',d) (which will retrieve the start of the quarter) + 3 months - 1 day, and use the expression to create a FUNCTION:

CREATE FUNCTION end_of_quarter (d date)
RETURNS date AS
$$
SELECT
    CAST(date_trunc('quarter', d)  + interval '3 months' - interval '1 day' AS date)
$$
LANGUAGE SQL IMMUTABLE ;

... and then use it:

SELECT
    d, end_of_quarter(d)
FROM
    (SELECT
        CAST(d0 AS date) AS d
    FROM
        generate_series(date '2017-01-01', date '2017-12-31', interval '6 days') AS s(d0)
    ) AS q ;

d           | end_of_quarter
:---------- | :-------------
2017-01-01  | 2017-03-31
...
2017-03-26  | 2017-03-31
2017-04-01  | 2017-06-30
...
2017-06-18  | 2017-06-30
2017-07-06  | 2017-09-30
2017-07-12  | 2017-09-30
...
2017-09-22  | 2017-09-30
2017-09-28  | 2017-09-30
2017-10-04  | 2017-12-31
2017-10-10  | 2017-12-31
...
2017-12-21  | 2017-12-31
2017-12-27  | 2017-12-31

You can check it at dbfiddle here

You could also shorten a bit your original approach by using the simple version of CASE expression WHEN value THEN ...:

SELECT
    d,
    extract('year' from d) || '-' ||
    /* case expression when value instead of case when expression */
    case extract('quarter' from d)
        when 1 then '03-31'
        when 2 then '06-30'
        when 3 then '09-30'
        else        '12-31'
    end AS end_of_quarter
FROM
    generate_series(date '2017-01-01', date '2017-12-31', interval '6 days') AS s(d) ;

You can check it at dbfiddle here

If you need to do this very often, define a function:

CREATE FUNCTION end_of_quarter (d date)
RETURNS date AS
$$
SELECT
    cast (extract('year' from d) || '-' ||
        case extract('quarter' from d)
            when 1 then '03-31'
            when 2 then '06-30'
            when 3 then '09-30'
            else        '12-31'
        end
    AS date)
$$
LANGUAGE SQL IMMUTABLE ;

dbfiddle here


Set up a profiler trace and monitor for Security Audit/Audit Broker Conversation. Repro the issue, an event of this class should be produced. Look at the event details. It should shed light. I think two of the event columns (can't remeber which, and MSDN seems to omit the info) contain the problem certificate issuer name/serial number, which should further help narrow down which certificate is being used.

[Edit]
Run this query query on all three databases to find out which certificates are used by which service and remote service binding:

select s.name, c.*
from sys.services s
join sys.certificates c
on s.principal_id = c.principal_id
where c.is_active_for_begin_dialog = 1
and pvt_key_encryption_type != 'NA'
order by c.expiry_date desc

select b.remote_service_name, b.remote_service_binding_id, c.*
from sys.remote_service_bindings b
join sys.certificates c on b.remote_principal_id = c.principal_id
where c.is_active_for_begin_dialog = 1
order by c.expiry_date desc

[Edit after your data]

1) In PRd2DB services tab: all services in the DB are owned by dbo, and dbo owns 4 certificates that are valid for SSB (certificate ids 280, 288, 327 and 357). The xls did not correctly capture the expiry_date (is all 00:00.0) so I cannot know for sure which one is used, but if the results come from the query I posted above then the first one will be used, certificate id 280 with the serial number 54 89 2c 1a.... So this is the certificate used to identify any service from this db.

2) In Prd2 in the RSB tab, the query shows a mixture of certificates with private key and ones without a private key. This should never happen. An user associated with an RSB represents the identity of a remote service and should own only certificates with no private key. Also, there is only one RSB user (the one with principal_id 58) for all remote services. There should be a different identity user for each remote service. Right now any dialog started in PrdDB2, to any service, will use the certificate with serial number 34 99 ....

3) DMZDB1 services: same as prd2, all services owned by dbo, dbo has 3certificates (ids 335, 341, 371) and the one used will be the one with serial number 74 0a ...

4) DMZDB RSBs: there are two RSB users, one principal id 9 and one 30. The principal id 9 has a mixture of certificates, including some with private key, while principal id 30 has only one certificate but that certificate has a private key.

Overall, the configuration is deeply flawed and and no wonder it doesn't work. You do not understand the role and the proper configuration of a remote service binding.

Please follow the following rules. Consider a remote service 'Foo':

create a different user (principal) for each remote service, call it remote_user_Foo
create a RSB for each remote service, using the principal for this service (ie. remote_user_Foo)
The remote_user_Foo should own exactly one certificate, and it should not have a private key
Grant necessary permissions (SEND) to remote_user_Foo

Next time you run the query for showing the RSB it should show a number of rows equal to the number of remote service bindings, the principal_id values should be all distinct and the pvt_key_encryption_type should all be NA.


Why conditioning before and after join, gets different results?

To answer your first question:

In the first query, some records are filtered out AFTER getting the result of the left join. These rows are the ones that meet this condition:  card.package_id =14 AND user_card.user_id <> 5 [AND user_card.user_id IS NOT NULL]

On the other hand, in the second query, ALL result rows with "card.package_id =14" are returned regardless of the value of user_card.user_id

Example:

card.id     card.package_id
11          14
12          14
13          55

user_card.card_id   user_card.user_id
11                  5
12                  6

All possible values:

card.id package_id user_card.user_id
11      14         5
11      14         6
12      14         5
12      14         6
13      55         5
13      55         6

The first query will filter out the the rows with user_id=6, although the value of package_id is 14, (and will filter out all reos with package ID <> 14), so the returned values will be:

card.id package_id user_card.user_id
11      14         5
12      14         5

The second query would keep all rows with package id = 14, and give null to user id:

card.id package_id user_card.user_id
11      14         5
11      14         NULL
12      14         5
12      14         NULL

You can judge which output is needed, and based on that you chose which query to use.


Using TEXT or even MEDIUMTEXT for all text columns in a MySQL table?

Drawbacks of what you propose:

Temp tables (for complex SELECTs) cannot use MEMORY, which is faster than the fallback of MyISAM.
Storing numeric 'values' VARCHAR makes it difficult or slow to test for.
Key-value schema leads to ugly JOINs.  And they are inefficient.
In InnoDB, depending on the ROW_FORMAT, TEXT fields may be stored in other blocks.  This may slow you down (INSERTs and SELECTs) due to extra disk hits.

Yes/no -- the ALTER TABLE to change the width of a VARCHAR may be costly.  In some very recent version, increasing the size of a VARCHAR became virtually free.  (There may be more caveats.)


Problem Insert Data to connected tables postgresql

So I figured it out eventually
        WITH insert_projekte AS
            (
            INSERT INTO projects (name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at)
            SELECT name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at
            FROM temp_units
            RETURNING ID, name
            )

INSERT INTO units (kgnr, ez, projekt_id, creator_id, editor_id, inserted_at, updated_at)
    SELECT t.kg_ez, t.ez, insert_projekte.id, t.creator_id, t.editor_id, t.inserted_at, t.updated_at
    FROM temp_units t
    JOIN insert_projekte on
    insert_projekte.name = t.name
    RETURNING ID

I always left the subquery in the second INSERT so it never worked before.
Thank you @CL. for the helpful suggestions.


CASE WHEN DATE not providing correct results

Without seeing the table definition, this is kind of an educated guess, but it looks like your Date_Field is a string field (VARCHAR, NVARCHAR etc) instead of a DATE or DATETIME field.

As per this fiddle, when defined as a VARCHAR field the results are all "NO" because the comparison is a string to string and doesn't make any sense (one string cannot be less than another string). When defined as a DATE field, the comparison value is implicitly converted to a DATE value and then can be successfully compared.

The correct solution would probably be to change the data type of the Date_Field column to DATE or DATETIME, however, if this isn't possible you can resolve this in the query alone using TRY_PARSE:

SELECT DateField,
CASE
    WHEN TRY_PARSE(DateField AS DATE USING 'en-US') < '2020-12-31' THEN 'NO' ELSE 'YES'
    end as ResultField
FROM Dates;

You can see the results in the fiddle are as expected:

DateField   ResultField
-----------------------
06/30/2022  YES
06/30/2018  NO
12/31/2022  YES
06/30/2020  NO
09/30/2020  NO
03/31/2020  NO
03/31/2024  YES
06/30/2021  YES


Use self join with row numbering

SELECT  Productset1.Product,
        Productset1.serial,
        Productset2.Product,
        Productset2.serial,
        CAST(DATEDIFF(minute, Productset1.time_checked, Productset2.time_checked) AS VARCHAR(20)) + ' (minutes)'
FROM    ( SELECT    row_number() OVER ( ORDER BY time_checked ) pid,
                    Product,
                    serial,
                    time_checked,
                    CONVERT(VARCHAR(20), time_checked, 103) datet
          FROM      TblProduct
        ) Productset1
        INNER JOIN ( SELECT row_number() OVER ( ORDER BY time_checked ) pid,
                            Product,
                            serial,
                            time_checked,
                            CONVERT(VARCHAR(20), time_checked, 103) datet
                     FROM   TblProduct
                   ) Productset2 ON Productset1.pid = Productset2.pid - 1
                          AND Productset1.Product <> Productset2.product


Abstract classes in SQL Server. Are they even possible?

There is nothing like this in SQL Server.

What tool are you building and maintaining your schema in though, will you be using database projects? If you went the "manual" method, while there is no SQL specific templating for this in SSDT, you might be able to use T4 templates to do something similar.

If that interested you then another alternative for templating is to use BimlScript. It is used to programmatically generate SSIS packages but they've very recently started doing something similar to generate SQL DDL so you could check into that.


How to find the length of multiple column index in MySQL

You'r lucky you have only one secondary index, the PRIMARY KEY is a clustered index so it isn't included in the index statistics.

You can find the answer with the field INDEX_LENGTH of the information_schema.tables view (result in bytes):

SELECT  INDEX_LENGTH
FROM    information_schema.tables
WHERE   table_schema = "myDatabase"
AND     table_name = "test_";

I recommend you to do a ANALYZE TABLE before to get more accurate estimates:

ANALYZE TABLE myDatabase.test_;

If you have more than one secondary index in your table you should have been to drop all but one and show the INDEX_LENGHT:

CREATE TABLE myDatabase.test_ (
  testId bigint(20) NOT NULL,
  name varchar(75) DEFAULT NULL,
  key_ varchar(200) DEFAULT NULL,
  user varchar(255) DEFAULT NULL,
  PRIMARY KEY (testId),
  UNIQUE KEY NKU (name,key_,user)
  UNIQUE NKU2 (user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE myDatabase.test_ DROP INDEX NKU2;

ANALYZE TABLE myDatabase.test_;

SELECT  INDEX_LENGTH
FROM    information_schema.tables
WHERE   table_schema = "myDatabase"
AND     table_name = "test_";

Max.


Restore the data from Snapshot Backup when the data spans over multiple directories?

To begin with, storing ibdata on a separate volume from the datadir is a bad idea because of

MetaData Issues : I wrote about this as well

Why does InnoDB store all databases in one file? (See WARNING)
How to Recover an InnoDB table whose files were moved around
Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql

Performance Issues : I wrote about that back in August 2011

MySQL InnoDB data file directly on a disk slice (fixed-size)?

Getting back to your problem, the reason LVM for ibdata1 is not good has to do with the metadata in ibdata1 at any given point-in-time. If you were to LVM Snapshot ibdata1, then create new tables, and drop some tables, somne of the corresponding .frm files within datadir would not match. The time needed to coalesce that large an ibdata would be astronomical (unless 21 hours is acceptable to you). I tried using rsync as opposed to LVM and I fared no better.

The safest and most sensible way to clear the space with innodb_file_per_table disabled is to do the following on the Slave

Step 01) mysqldump all databases
Step 02) shutdown mysql
Step 03) delete ibdata1
Step 04) startup mysql
Step 05) reload the mysqldump

Without this method, ibdata1 can never be shrunk otherwise. Even in my post in StackOverflow on InnoDB Cleanup (using innodb_file_per_table), you have to do those same 5 steps.


MySQL: Error reading communication packets

I am glad you said all your data is InnoDB so I can answer as follows: If max_allowed_packet is maxed out at 1G and you are still having issues, there is really only two places to look:

innodb_log_buffer_size : The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.
innodb_log_file_size : The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.

I addressed something like about 2 years ago

Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?
Apr 20, 2011 : MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?

SUGGESTIONS

You need to increase the InnoDB transaction logs. Here are the steps to safely increase innodb_log_buffer_size and innodb_log_file_size:

Step 01 : Add these to /etc/my.cnf

[mysqld]
innodb_log_buffer_size = 32M
innodb_log_file_size = 2047M

Step 02 : Run this in mysql

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Step 03 : Shutdown mysql

service mysql stop

Step 04 : Move the old logs aside

mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak

Step 05 : Start mysql

service mysql start

That's it.

The InnoDB Infrastructure should now have enough logging space for different size BLOBs.

Give it a Try !!!


SSMS Suggested Indexes and Compability Mode

I also have databases stuck at CL80, also on 2008r2: I've just tested and they allow indexes with INCLUDEed columns to be defined. I've not explicitly tested that they are properly used though so before relying upon this check appropriate queries to see what plans get created.

If the included columns are not used properly to avoid lookups, you could translate them to used columns, i.e. CREATE INDEX ix_test_u1_i2 ON tbl_test (i1) INCLDUE (i2) would become CREATE INDEX ix_test_u1_i2 ON tbl_test (i1, i2). Do benchmark the result though, or account for it in your index maintenance plans, because if the later columns are fairly random you could increase the level of index fragmentation (through page splits on insert/update) noticeably. - edit: as per Paul's comment, indexes created with INCLUDEed columns are used correctly by the query planner so this comment is moot. Of course if your application has instances that are still actually running on SQL2000 rather than just having CL set to 80, that changes this back...

As an aside: the missing index information displayed by SSMS should only ever be taken as a suggestion that you consider something - the suggestions can potentially be detrimental to the application overall especially if you follow all of them (the extra space used could be rather large).


pgpool replication not allowing md5 authentication

It looks like your default authentication mode for TCP/IP connections (host) is md5. So at a guess, you have PgPool connecting over TCP/IP.

Change the auth mode for loopback connections on tcp/ip, or use unix sockets with pgpool (usually by omitting the host setting).

If you're still stuck after reading:

19.1. The pg_hba.conf File and
Chapter 19. Client Authentication

in the PostgreSQL 9.4 documentation, and you can't find answers elsewhere (there are lots of related questions on Stack Overflow), let me know and I'll give you a step by step if I have a chance.


Query to identify people exceeding a particular amount within an interval of 6 months

The part "within any time period of 6 months" makes things quite tricky. It is very easy to check this for a specific period:

select cid,
       sum(amount)
from transfer
where trans_date >= date '2014-11-01'
  and trans_date < date '2015-06-01'
group by cid
having sum(amount) >= 1000;

But this is not what you want.

You want a "rolling running total" that covers the last 6 month relative to the the trans_date value of each row. This can easily be achieved using a window function (aka "analytical function")

select trans_date,
       cid,
       amount,
       sum(amount) over (partition by cid order by trans_date range between interval '6' month preceding and current row) as running_total
from transfer
order by trans_date;

The "trick" is the "range between ..." part: it will only evaluate the running total for the "last 6 month" relative to the month of the "current row"

The above returns the following result for your sample data:

TRANS_DATE          | CID | AMOUNT | RUNNING_TOTAL
--------------------+-----+--------+--------------
2014-11-24 00:00:00 | 321 |    200 |           200
2015-01-08 00:00:00 | 321 |    900 |          1100
2015-06-22 00:00:00 | 123 |    550 |           550
2015-08-15 00:00:00 | 123 |    500 |          1050
2015-08-24 00:00:00 | 999 |    200 |           200
2015-10-24 00:00:00 | 999 |    200 |           400

Now to get the customers you are interested in, you can wrap this query into a derived table and then get only those CIDs that have at least one running total that exceeds 1000:

select distinct cid
from (
  select trans_date,
         cid,
         amount,
         sum(amount) over (partition by cid order by trans_date range between interval '6' month preceding and current row) as running_total
  from transfer
) t
where running_total > 1000;

The distinct is necessary because a customer can have more than one "6 month period" where the total amount exceeds 1000.

This is probably not the most efficient query though - but currently I can't think of anything better.

Here is a SQLFiddle example: http://sqlfiddle.com/#!4/1e26f/2


Size of Partitions in Partitioned Tables

Well, yes it's true.  One important reason for partitioning is to reduce the I/O load for queries.  That is, only one partition needs to be read for a given query.

If you make a bunch of assumptions, such as:

An even distribution of rows over queries over time (relative to the partitioning key)
Record sizes independent of the partitioning key
Equal access for the partitions

Then you will discover that for "n" partitions the most efficient solution is when they are equal sized.

That said, there are definitely special cases.  If the partitioning key is referenced through ranges (between), then hash partitioning is not optimal.  If some data is accessed much more frequently (such as the most recent data), then equal-sized partitions may not be suitable.  If you are using partitions for fast deletes (by dropping the partition), then hash-based partitions are not suitable.


For this you use pass through. The client connects to the TT instance and TT passes the query through to the Oracle rdbms, when needed. Why not just add a few more nodes to the grid and also cache the bigger tables?


what is the best query to find missing index in sql server 2008r2 and how to resolve missing index

You can use sp_BlitzIndex to find the high-value missing indexes, @mode=3 will specifically only show the high-value missing indexes. Target the ones with an "Estimated benefit per day" that are greater than 5,000,000. See if your workload improves after making the changes.

Disclosure: I work at Brent Ozar Unlimited.


SQL Server 2014 "syspolicy_purge_history" job keeps failing

Can you try this in the Windows Server PowerShell path?

PS C:\Windows\system32> Get-ExecutionPolicy -list

     Scope            ExecutionPolicy
     --------         ------------------
     MachinePolicy    Undefined
     UserPolicy       Undefined
     Process          Undefined
     CurrentUser      Undefined
     LocalMachine     Restricted

..and then the same in the SQL Server path:

PS SQLSERVER:\> Get-ExecutionPolicy -list

     Scope            ExecutionPolicy
     --------         ------------------
     MachinePolicy    Undefined
     UserPolicy       Undefined
     Process          RemoteSigned
     CurrentUser      Undefined
     LocalMachine     Restricted

If the execution policy is set to “Restricted”, try to set it to “RemoteSigned” or “Unrestricted”.

Set-ExecutionPolicy -Scope LocalMachine -ExecutionPolicy Unrestricted

or

Set-ExecutionPolicy -Scope LocalMachine -ExecutionPolicy RemoteSigned

Please note that you need to set this execution policy in both the Windows PowerShell and the SQL PowerShell consoles.


Permission errors on insert after schema drop/create (Pg 9.0.23)

Figured out a workaround that involved just deleting tables (and sequences) rather than dropping the schema. Leaving here in case it helps anyone else.

Changed the first few lines:

BEGIN;
drop schema if exists genome3d_data_daily CASCADE;
create schema genome3d_data_daily AUTHORIZATION genome3ddaily;

...to...

BEGIN;
create schema genome3d_data_daily AUTHORIZATION genome3ddaily;
END;

BEGIN;
drop table if exists genome3d_data_daily.annotation_type;
drop table if exists genome3d_data_daily.annotation_segment;
drop table if exists genome3d_data_daily.annotation;
drop sequence if exists genome3d_data_daily.annotation_id_seq;

This no longer disrupts existing database connections.


Why are zero length spaces being returned from a Union All against an empty table in SQL Server 2005 but not SQL Server 2012

Check the ANSI_PADDING setting:

  Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

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

I just tried on both SQL Server 2005 and 2012 and I don't get those results:

Unit5 Unit6 Len_Unit6   Spanner
----- ----- ----------- --------
13          0           --||--

(1 row(s) affected)

Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)
    Dec 10 2010 10:56:29
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

(1 row(s) affected)

Unit5 Unit6 Len_Unit6   Spanner
----- ----- ----------- --------
13          0           --||--

(1 row(s) affected)

Microsoft SQL Server 2012 (SP1) - 11.0.3349.0 (X64)
    Mar  8 2013 17:33:56
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

(1 row(s) affected)


25M x 25M inner join (postgresql) performance

Although it is hard for me to understand the motivation for this kind of query, you cannot expect any kind of efficiency since the query results will not fit in main memory. If you want the results stored somehow, the only way to do it is through the COPY command (http://www.postgresql.org/docs/9.2/static/sql-copy.html). COPY has the advantage that once a row is retrieved from the query it goes directly to secondary storage. This way you actually have a chance for the query to finish after a few hours. Then, if needed you can use COPY again to load those results to another DB table.


Step 1

First setup your drive (assumed it's mounted and setup correctly in the fstab) and create new MySQL directory:

sudo mkdir /path/to/new/mysql

Make sure this folder has the right permissions:

chown -R mysql:mysql /data/mysql

Now here you can shutdown the service while copying data. Copy the data from the current data directory to new data directory, example:.

sudo cp -R /var/lib/mysql/* /path/to/new/mysql/

or you can copy individually each databases if you prefer or if you feel safe, you can move the folder using the mv command.

Step 2

You can change the location of your MySQL data in your my.cnf. If you don't know where this file is, you can run the command:

locate my.cnf

This will give you the path of where the my.cnf is located. Then edit the file:

sudo vi /etc/mysql/my.cnf (of course you have to replace with your path)

Now look for datadir = /var/lib/mysql and update to datadir = /path/to/new/mysql.

Then restart mysql

sudo /etc/init.d/mysqld restart

After few days/weeks, if you feel comfortable you can remove the old folder (/var/lib/mysql) or backup everything inside.


This is one way to accomplish this, but I agree with JNK in the comments that it will scale very poorly and perform the same:

    SELECT CASE WHEN ISNULL([PackWeight/Vol], '') = '' OR PATINDEX('%[a-Z]%', [PackWeight/Vol]) = 0
THEN ''
ELSE CAST(SUBSTRING([PackWeight/Vol], 1, PATINDEX('%[a-Z]%', [PackWeight/Vol]) - 1)  * [Units/Pk] AS VARCHAR(256)) +
               SUBSTRING([PackWeight/Vol], PATINDEX('%[a-Z]%', [PackWeight/Vol]),  LEN([PackWeight/Vol]) + 1 - PATINDEX('%[a-Z]%', [PackWeight/Vol]))
    END
        FROM table


I think the question is poorly phrased, as the wording implies that you've already decided NULLs are bad. Perhaps you meant "Should we allow NULLs?"

Anyway, here is my take on it: I think NULLs are a good thing. When you start preventing NULLs just because "NULLs are bad" or "NULLs are hard", you start making up data. For example, what if you don't know my birth date? What are you going to put in the column until you know? If you're anything like a lot of anti-NULL folks, you're going to enter 1900-01-01. Now I'm going to be placed in the geriatric ward and probably get a call from my local news station congratulating me on my long life, asking me my secrets to living such a long life, etc.

If a row can be entered where it is possible that you don't know the value of a column, I think NULL makes a lot more sense than picking some arbitrary token value to represent the fact that it is unknown - a value which others will have to already know, reverse engineer, or ask around to figure out what it means.

There is a balance, though - not every column in your data model should be nullable. There are often optional fields on a form, or pieces of information that otherwise don't get collected at the time the row is created. But that doesn't mean you can defer populating all of the data. :-)

Also the ability to use NULL can be limited by crucial requirements in real life. In the medical field, for example, it can be a life-or-death matter to know why a value is unknown. Is the heart rate NULL because there wasn't a pulse, or because we haven't measured it yet? In such a case, can we put NULL in the heart rate column, and have notes or a different column with a NULL-because reason?

Don't be afraid of NULLs, but be willing to learn or dictate when and where they should be used, and when and where they shouldn't.


Should I delete the mysql database user id created for my site by the developer?

You can change the password in wordpress account as shown in
Resetting_Your_Password

and change the cPanel user password also for secuirty concern.


Multiple records and determine if a combination if values exist and write this to a new column

For an Update to the existing table, one solution would be:

UPDATE t
SET t.New_Column = CASE WHEN types.mintype = 'F' and types.maxtype = 'F' THEN 'FTW'
                        WHEN types.mintype = 'N' and types.maxtype = 'N' THEN 'Non-FTW'
                        WHEN types.mintype = 'F' AND types.maxtype = 'N' THEN 'Mixed' END
FROM tbl t
INNER JOIN
(SELECT ORDR,
       MIN(TYPE) mintype,
       MAX(TYPE) maxtype
FROM tbl
GROUP BY ORDR) types
    ON t.ORDR = types.ORDR

Though, I would also, as Chris Aldrich said in the comments, suggest using a view. That view could be:

CREATE VIEW v_tbl
AS
SELECT t.ORDR,
       t.TYPE,
       CASE WHEN types.mintype = 'F' and types.maxtype = 'F' THEN 'FTW'
            WHEN types.mintype = 'N' and types.maxtype = 'N' THEN 'Non-FTW'
            WHEN types.mintype = 'F' AND types.maxtype = 'N' THEN 'Mixed' END New_Column
FROM tbl t
INNER JOIN
(SELECT ORDR,
       MIN(TYPE) mintype,
       MAX(TYPE) maxtype
FROM tbl
GROUP BY ORDR) types
    ON t.ORDR = types.ORDR


how to replace with default value when querying a JSONB column with a key that is not present in a particular row giving empty result

Use a left join:
select up.id, r.element ->>'subject' as result
from "UserProfile" up
  left join jsonb_array_elements(up.result) as r(element) on true


Apparently there are updated copies of software called "dBase" but they seem to need special care, and money, to run on a modern OS.  Visual dBase 5.5 itself provides object-oriented and event-driven programming, but I think probably it's mainly based on dealing with one record (row) at a time in the data file (database or "table").  It provides the data storage, and also the program that a user needs to work with the data.

A modern equivalent system might consist of separate programs, a "database server" whose function is only to store data reliably and output it as required, and a "client" or "front end" which presents data in  user-friendly style, typically using the "SQL" language to send and retrieve data on the server (by sets, not individual rows, usually).  Another category of tool, and sometimes an expensive one, is "reporting".

Amongst questions to ask: how many people need to use the data simultaneously?  If it's more than one, then probably a client-and-server solution is best.  If one, you might be able to do the whole thing in Microsoft Excel.

Your employer may be able to buy a database application which serves the needs of your industry, and save the effort of re-learning the one that you've got or writing your own - your time presumably costs money as well.  But it won't be cheap.  A buy-in will probably be based on one of the industry-standard database servers that are known here, and you can probably convert your data into forms that the system will accept.  And the tool may provide definable fields for data that only your employer has thought of storing.

So, you could start by identifying any suitable database products for your industry, find out what database server they may use, and whether the server comes as a cheap or free download for tryout use - and then first of all, get that server, and try to import an initial copy of your data, your way, into the server.  That should move you towards converting the data for use in the new database product, or else writing your own program again, if you think you can do it.  If this exercise doesn't work, then, look at a different software and server package - or look at hiring a database consultant to help you.

But if you urgently need to replace the existing system, then using an updated version of dBase may be easier, after all.  It won't be an identical system to what you've got, but I suspect it doesn't represent twenty years of progress...  so maybe it won't be so different after all.


Proper solution

Your comment (which I added to your question) discloses the root of the problem:

  Unfortunately, the column traitements.zones_sites.origine_id cannot be
  of type integer because it holds identifier from several origins, some
  of them not being integers.

A roughly normalized DB design would avoid to mix data of different kind in the same column. Then your true integer values could be in a column of type integer and the rest in (a) text column(s). And there wouldn't be a problem to begin with.

While stuck with your current design

Since, as you asserted, the column origine_id can be cast to integer for the selection of rows in your query, I suggest this much faster alternative: create a partial expression index:

CREATE INDEX zones_sites_idx_origine_part_int ON traitements.zones_sites ((origine_id::int))
WHERE catégorie = 'emprise_site'
AND   précision_contour = 'contour_inconnu'
AND   origine = 'xxxxxxxxx';

If the cast in your INSERT into the temp table is guaranteed to work, then so is this index.

Slightly modified query:

SELECT z.pkid, z.pkid_site, z.origine, p.idpar
FROM   traitements.zones_sites z
JOIN   parcelles               p ON p.idpk = z.origine_id::int
WHERE  z.catégorie = 'emprise_site'
AND    z.précision_contour = 'contour_inconnu'
AND    z.origine = 'xxxxxxxxx';

Since the partial index also supports your query perfectly, this should make your current solutions look like slow motion in comparison. And it's simpler and more reliable, too.

The only other index you need is one on parcelles(idpk).
Since the only other column you need from parcelles is idpar, if that table is big and write-load is low so you can get index-only scans out of it, an (additional) multicolumn index on parcelles(idpk, idpar) would make it even faster.

And faster, yet, in the upcoming Postgres 11 with true covering indexes with an INCLUDE column:

Related answer with code example:

Does a query with a primary key and foreign keys run faster than a query with just primary keys?


You can start from this list from Jonathan Kehayias.

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/03/22/sql-server-installation-checklist.aspx

Can you elaborate on "migrating a database with high transactions"? This is very open ended question and not sure how to address this. There are lot of good practices to follow here.

Making sure data, logs, tempdb are on separate disk and following best practices for Files i.e Instant file initialization, pre-allocating sizes so that autogrow won't happen, partition alignment, having good RAID http://blogs.msdn.com/b/jimmymay/archive/tags/disk+partition+alignment/
Good index creation & maintenance procedures
Good application + sql code
Getting a head start on http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx
Having decent memory size so that disk access is limited
Looking for disk latency using virtual file stats DMV
Watching for WAIT STATISTICS

There are so many other things here but it takes a while to list them all and it depends.


SQL Server mirrored database is stuck in recovery mode

Restart the db mirroring end point:

--To stop
ALTER ENDPOINT<Endpoint Name> STATE=STOPPED

--To start
ALTER ENDPOINT<Endpoint Name> STATE=STARTED

TECH note: if there are multiple databases, not just the one in error, then stopping and starting the enpoint affects all the databases on that endpoint. This can cause problems on a production system like SharePoint. To fix, go the other mirrored database server and enter the same commands:
 ALTER ENDPOINT  STATE=STOPPED
 ALTER ENDPOINT  STATE=STARTED

How to find the name of the end point?
select * from sys.endpoints  <- works for SQL 2012
The name will be of " type=4 " for mirroring.


Getting duplicate results in many to many query

Objective: Count book's readers.
Display book title, count, authors.

JOIN the (Subquery: book.array_agg(authors))
TO (Subquery: count book readers)
TO Book for id, title.

I think the logical error, as you mentioned Cartesian product, was aggregating two different aggregations at the same time...

Something like this should work:

PostgreSQL:

SELECT id, title, read_ct.Readers, Authors.Names
FROM book b
JOIN ( SELECT book_id, array_agg( name) as Names
       FROM book_author ba
       JOIN author a
       ON a.id = ba.author_id
       GROUP by book_id
      ) Authors
ON Authors.book_id = b.id
JOIN (
    SELECT book_id, count(r.user1_id) Readers
    FROM reader r
    GROUP BY book_id ) read_ct
on read_ct.book_id = b.id

SQL Server:

SELECT id, title, read_ct.Readers, STUFF(
                (SELECT ', ', a.name
                FROM book_author ba
                    JOIN author a
                    ON a.id = ba.author_id
                WHERE ba.book_id = b.id
                FOR XML PATH(''), TYPE)
                .value('.', 'varchar(max)'), 1, 2, '')
    FROM book b
    JOIN (
        SELECT book_id, count(r.user1_id) Readers
        FROM reader r
        GROUP BY book_id ) read_ct
    on read_ct.book_id = b.id


I've not seen this exactly before, it does seem strange that you can run the query manually but not as an agent job. This is not unheard of though SQL Server Central has a similar post a couple of years ago, but has some reasonably complex queries involved.

What is the query you're trying to run doing?

As Tom V said, it would be helpful to know what version of SQL Server you're both running the query from, and where the linked server is connecting to

When you run the query manually:

Is it everything that the Agent job is doing (There isn't more than one step in the Agent job)?
Are you using the same account as the Agent job is run as?

As a start, it's worth checking your MSDTC configuration on both servers. I don't know if it'll be that in this case but it's best to try and eliminate simple things first. Have a look at these sites to get some details on that:

https://stackoverflow.com/questions/10130767
MSDN


Is it possible to attach a description field to the records in INFORMATION_SCHEMA.TABLES?

Use extended properties.  It's not in the INFORMATION_SCHEMA views but it will stay attached to that object.

I wrote about it in detail here: https://sqlstudies.com/2015/05/18/extending-your-metadata-with-extended-properties/

But here is what it would look like attached to a User:

Here is how you would query all of the extended properties for users in a given database:

-- View the extended properties
SELECT ep.*, dp.name FROM sys.extended_properties ep
JOIN sys.database_principals dp
    ON ep.major_id = dp.principal_id
WHERE class_desc = 'DATABASE_PRINCIPAL'
GO

The same thing will work for pretty much any object in SQL Server.  Obviously you'd want to modify the query somewhat but that should be pretty easy.


Presumably a pigeon cannot arrive at a station twice in one second?  Then add this:

PRIMARY KEY(pigeon_id, pigeon_station, date_time)

This will improve on ypercube's suggestion, perhaps by a factor of 2.

At that point, probably both of your indexes become useless.  But before that, let's see the other important queries.


Group by specific value and return results in generated column

SELECT
   object_id,
   (SELECT b1.value FROM below b1 WHERE b1.object_id=b0.object_id and b1.type='type1') as Type1,
   (SELECT b1.value FROM below b1 WHERE b1.object_id=b0.object_id and b1.type='type2') as Type2,
   (SELECT b1.value FROM below b1 WHERE b1.object_id=b0.object_id and b1.type='type3') as Type3
FROM below b0

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=98618f2a316933c6c73260b13e33f15c
or

SELECT
  b0.object_id,
  b1.value as Type1,
  b2.value as Type2,
  b3.value as Type3
FROM below b0
LEFT JOIN below b1 ON b1.object_id=b0.object_id and b1.type='type1'
LEFT JOIN below b2 ON b2.object_id=b0.object_id and b2.type='type2'
LEFT JOIN below b3 ON b3.object_id=b0.object_id and b3.type='type3'

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0437e739f9a9d4cd9923feae6ecd5593


Grant Permissions to all databases using server role

Besides reading my answer look at this Q&A by Aaron Bertrand

How to create a read-only server role on SQL Server 2012?

This solution will work for SQL Server 2014 and above.

CREATE LOGIN [AccountNeedPermission] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
GO
GRANT CONNECT ANY DATABASE to [AccountNeedPermission];
GO
GRANT SELECT ALL USER SECURABLES to [AccountNeedPermission];
GO
GRANT VIEW ANY DEFINITION to [AccountNeedPermission];
GO

Reason your solution will not work Server roles cannot be granted permission on database-level securables.


How to deploy the SQL scripts to the local database (one-click deployment)?

I think you can get your answer from this link:

https://stackoverflow.com/questions/2583517/run-all-sql-files-in-a-directory

You can also check below site for more details on SQLCMD utility:

https://ssqlforest.wordpress.com/2016/10/31/how-to-use-sql-server-database-from-sqlcmd-prompt/

You can use database name within the script or -d option if all scripts have to go in the same database. Kindly ensure that queries are separated in batch i.e. go keyword after each SQL statement.


updating answer to Use LEFT JOINS..

SELECT j.JobId
     , j.OrderId
     , j.OrderType
     , j.ShopName
     , j.ShipDate
     , j.CompletionDate
     , j.InstallDate
     , j.DayPriority
     , j.ShipDateConfirmed
     , j.IsAddon
     , j.CashOnDelivery
     , ds.ShopLabel
     , ds.DesignSetId
     , dsp.DesignSetProcessId
     , dsp.DueDate AS DesignSetProcessDueDate
     , dsp.DatetimeStarted
     , dsp.DatetimeComplete
     , dsp.BatchNumber
     , p.Name AS ProcessName
     , p.ProcessId
     , p.ZoneId
     , (
               SELECT SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount
               FROM Products pr
               WHERE ds.DesignSetId = pr.DesignSetId
                     AND (
                            dsp.BatchNumber = pr.BatchNumber
                           -- OR dsp.DesignSetId IS NULL    Note : Since dsp.DesignSetId = ds.DesignSetId this condition can be omitted. uncomment it if necessary.
                         )
            ) AS CabinetCount
FROM Jobs j
JOIN DesignSets ds
   ON j.JobId = ds.JobId
LEFT OUTER JOIN Schedule.DesignSetProcessesWithDueDates dsp
   ON ds.DesignSetId = dsp.DesignSetId
LEFT OUTER JOIN Schedule.Processes p
   ON dsp.ProcessId = p.ProcessId
WHERE j.Status >= 60
      AND j.Status <= 70
ORDER BY j.CompletionDate
       , j.DayPriority DESC
       , j.ShopName
       , j.OrderId
       , ds.ShopLabel
       , p.SortOrder
       , dsp.BatchNumber;


SQL Server 2008: Row count for each hour in a Day and Row count for each day in a Month

You want to look into using a PIVOT table.  You can use the same solution for your hour as well as the day of week or even day in month just change a few things in the query.

Here is an example for you that gives you the Sunday, Monday, etc. table that you first asked for.  It should get you started.

;WITH CTE_Parcel AS
    (
    SELECT created_at
        , 1 AS ItemCount
        , DATENAME(WEEKDAY, created_at) AS DayOfOrder
        , DATEPART(HOUR, created_at) AS HourOfOrder
        , SundayOfWeek = DATEADD(DAY , 7-DATEPART(WEEKDAY,created_at),created_at)
    FROM PARCEL
    WHERE created_at BETWEEN '2017-03-26 00:00:00'/*Sunday, report start date*/ AND '2017-04-10 23:59:59'/* Saturday, report end date */
    )
SELECT SundayOfWeek
    , PT.Sunday
    , PT.Monday
    , PT.Tuesday
    , PT.Wednesday
    , PT.Thursday
    , PT.Friday
    , PT.Saturday
    , WeekTotal = PT.Sunday + PT.Monday + PT.Tuesday + PT.Wednesday + PT.Thursday + PT.Friday + PT.Saturday
FROM (SELECT ItemCount, DayOfOrder, HourOfOrder, SundayOfWeek FROM CTE_Parcel) AS P
    PIVOT (COUNT(ItemCount) FOR DayOfOrder IN ([Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday])) AS PT
ORDER BY PT.SundayOfWeek


How do I list or search all the column names in my database?

You can use following query to list all columns or search columns across tables in a database.

USE AdventureWorks
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;

You can make use of  information_schema views to list all objects in SQL Server 2005 or 2008 databases.

SELECT * FROM information_schema.tables

SELECT * FROM information_schema.columns

http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/


In Memory Database Remove File and FileGroup

Currently, the only way to remove Hekaton is to drop the whole database.
Probably not your first choice, but it doesn't look like Microsoft is willing to change course on that.


migration sql 2003 to 2008, Sql side and php side

There is no MSSQL 2003, so you must mean MSSQL 2005, right? Are you trying to upgrade? Do you have a development environment you can test in? That's your best course of action. Other than that, I'm not sure what you're asking exactly.

But I've upgraded from 2005 to 2008 using a PHP environment before and nothing broke. There might be some deprecated features in 2008 but as far as I know they haven't been removed from the engine, just deprecated. Most everything should work. But again, try it out in a development environment.


Is database performance optimization independent of the size of a database or a table?

There is no definite answer. It really depends on the data and the change you have performed.

For instance, if you created an index that helped in the 100,000 records table, in many cases it will help with the 10,000,000 records table.

However, if, let's say, you had a cursor and you've rewritten it to a set based solution, it will be good enough for the 100,000 records table, but not good in the 10,000,000 records table because there is no supporting index for the query.

The best thing to do is to monitor the system as it grows and see if queries keep running fast or degrade, and fix accordingly.


Why are my Postgresql sessions writing directly on disk?

Write-heavy operations like COPY or INSERT INTO ... SELECT FROM will use a non-default buffer access strategy which intentionally causes the write-heavy process to write its own buffers.

This is so that, if you write a billion rows into a table, that table growth doesn't shove the entire rest of the database out of the cache.

Even if your write heavy operations do not use a buffer access strategy:

There is no reason for checkpointing to be doing most of the writing unless our checkpoint_segments are set too small.
If your bgwriter is not aggressive enough, it might not get enough data written and so the user backends will do it themselves.  You could try to make bgwriter more aggressive.  But ever since version 8.3.20 or so, the bgwriter is not very useful anyway, and having the backends do writes themselves is rarely a problem anymore.