Is it possible to get the last restore date of a MySQL instance?

I've just tested your solution about the "CREATE_TIME" field in information_schema and I think it's the good one, look :

[root@localhost] [MFO] mysql> select * from information_schema.tables where table_name = "myisammfo"\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: MFO
     TABLE_NAME: myisammfo
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: 2
 AVG_ROW_LENGTH: 7
    DATA_LENGTH: 14
MAX_DATA_LENGTH: 1970324836974591
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2013-03-05 15:15:22
    UPDATE_TIME: 2013-03-05 15:17:58
     CHECK_TIME: 2013-03-05 15:18:28
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.01 sec)

I did a mysqldump of MFO DB:

predator:~# mysqldump -uroot -p --databases MFO >/tmp/MFO.SQL

Then loaded it:

predator:~# mysql -uroot -p </tmp/MFO.SQL

Now the CREATE_TIME is today:

predator:~# mysql -uroot -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 86261
Server version: 5.0.51a-24+lenny5 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

[root@localhost] [(none)] mysql> select * from information_schema.tables where table_name = "myisammfo"\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: MFO
     TABLE_NAME: myisammfo
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: 2
 AVG_ROW_LENGTH: 7
    DATA_LENGTH: 14
MAX_DATA_LENGTH: 1970324836974591
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2013-10-02 16:45:56
    UPDATE_TIME: 2013-10-02 16:45:56
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

Best regards.

Max.


Postgres ts_headline inconsistent behaviour

I noticed my mistake.
The to_tsquery function inside also needs to know which dictionary to use (aka use the simple one). By default it uses english stemming which produces this result:

SELECT to_tsquery('artificial');
 to_tsquery
------------
 'artifici'

which of course can not be found in the text converted by simple dictionary.
So the correct query would have been:

# SELECT ts_headline('simple', 'This is artificial text', to_tsquery('simple', 'artificial'));
          ts_headline
--------------------------------
 This is <b>artificial</b> text
(1 row)


T-SQL or PowerShell to move bak files older than 2 days to another location

This Powershell example will find all .bak files older than two days in the c:\backup folder and move them to \shared\archive shared drive.

Get-ChildItem c:\backup\*.bak |where {$_.LastWritetime -lt (get-date).adddays(-2)} |move-item -Destination \\shared\archive

Note you cannot run PowerShell commands directly in SSMS. Try it in a SQL Server Agent job step, or just in a PowerShell command window. Should work fine in either.


SQL Managment doesn't use all memory, queries fail due insufficiënt memory

I believe memory usage in Express edition is limited to 1Gb per instance: http://sqlknowledge.com/2014/04/sql-2014-express-edition-limitations/

So setting Max Memory any higher than that will be ignored by an Express instance. With that in mind I'm a bit surprised that you are seeing memory usage hitting 3.5Gb

You mention the ORDER BY in your question - does the query run ok without it?


Query to find optimal filegroups for objects

Bad news. You said ETL process - that means heavy data loads, and inserts/updates/deletes have a single disk bottleneck: the transaction log file. You can add multiple transaction log files, but they're used sequentially, not load-balanced (except in some really edge-case scenarios.)

Instead, what you need is multiple databases. In data warehouses, you typically use a different source database than a destination database, like a staging database. That way, you can put its databases on different drives (and even different classes of drives). For example, some staging databases live on cheap local SSDs.

If you do find yourself storage-bottlenecked on data file reads or writes, you're better off solving that problem by adding memory (to cache the data and avoid reads altogether) or by striping the data across more drives, not less. By splitting the data files into smaller pieces, and giving them each fewer drives, you're usually making the problem worse.


weird sql server behavior when inserting and selecting

Databases do not return rows in a given order unless you supply an ORDER BY clause in your query, thus making the INSERT "order" meaningless. The order of a SELECT * FROM MYTABLE; query is undefined.

Apologies for the simple answer!


Converting ER diagram to tables and relationships in sql

The Student - Applicant relationship is 1:1 and your implementation is correct. An alternative is to remove the app_id (or the stud_id) from the Applicant table and to use the (one and only) column both as a Primary Key and for the Foreign Key constraint (see the code below).

I assumed from your code that a Student can be selected as an Applicant. If it's the other way around (some of the Applicants are selected as Students), the foreign key constraint has to be reversed.

You also have several columns (that are used in the foreign key constraints) defined as NULL. Not sure how the diagram is to be interpreted. I guess that these should be NOT NULL. Otherwise, the course.branch_id for example being null means that you may have a course that is not related to any branch at all.

I would also name the (FK) constraints, and since this is homework, I'd name them according to the provided diagram.

It's also not bad to add the NOT NULL to primary key columns as well. It changes nothing in the created tables - until you decide to change the primary key and you forget to add the not null constraint:

create table department
 (dept_id number not null primary key,
  dept_name varchar2(15) not null
 );

create table branch
 (branch_id varchar2(5) not null primary key,
  electives varchar2(10),
  dept_id number not null,
  constraint Department_Has_Branches
    foreign key (dept_id)
    references department(dept_id)
 );

create table course
 (course_id number not null primary key,
  course_name varchar2(10) not null,
  branch_id varchar2(5) not null,
  constraint Branch_Offers_Courses
    foreign key (branch_id)
    references branch(branch_id)
 );

create table student
 (stud_id number not null primary key,
  stud_name varchar2(30) not null,
  branch_id varchar2(5) not null,
  constraint Student_BelongsTo_Branch
    foreign key (branch_id)
    references branch(branch_id)
 );

create table applicant
 (app_id number not null primary key,      -- stud_id removed
  constraint Student_SelectedAs_Applicant
    foreign key (app_id)                   -- app_id used in the FK to Student table
    references student(stud_id)
);

create table applicant_AppliesFor_branch
 (app_id number not null,
  branch_id varchar2(5) not null,
  primary key (app_id, branch_id),
  constraint Student_AppliesFor_Branch
    foreign key (app_id)
    references applicant(app_id),
  constraint Branch_AppliedBy_Student
    foreign key (branch_id)
    references branch(branch_id)
);

For Oracle, see:

Display all names of constraints for a table in Oracle SQL


MySQL INFORMATION_SCHEMA.TABLES not adding to file size

It turns out INFORMATION_SCHEMA.TABLES is rarely updated and can be off by a large factor. Extensive details from percona: https://www.percona.com/blog/2016/01/26/finding_mysql_table_size_on_disk/
INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES is always up to date, indeed:
mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name = 'data/modnar';
+-------+-------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| SPACE | NAME        | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE  | ALLOCATED_SIZE |
+-------+-------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
|    54 | data/modnar |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 | 7470055424 |     7470059520 |
+-------+-------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+

Running OPTIMIZE on the modnar table did update INFORMATION_SCHEMA.TABLES for which the numbers now add up to 6.25 GiB.
A query to find which table use large amount of unallocated space from up to date data:
select name, unallocated_KB, round(unallocated_KB/(fs_block_size/1024)) unallocated_blocks, unallocated_KB/(file_size/1024) unallocated_percent from (
  select name, fs_block_size, file_size, (ALLOCATED_SIZE - FILE_SIZE)/1024 unallocated_KB
  from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
  where name not like 'mysql/%' and name not like 'sys/%'
  order by unallocated_KB
  ) as tables;


ORA-16000 when trying to perform select on read only Oracle database

I am not aware of any specific privileges related to XMLType. There will be execute privs related to PL/SQL that works with it but your message is clear and sadly definitive. If you had a privs issue you wouldn't be getting this message...

XMLType can be funny to work with. Have you thought about retrieving it as a CLOB and then doing the XML parsing and extraction at the client end?


You can use CTE with RANK() function. Read more here:

Example:

WITH ordermarks AS (
    SELECT [STUD_ID],[SUBJECT],[MARKS], RANK() OVER (PARTITION BY [SUBJECT] ORDER BY [MARKS] DESC) AS rn
    FROM marks
)
SELECT [STUD_NAME],[SUBJECT],[MARKS]
FROM ordermarks om
JOIN Students s ON s.[STUD_ID]=om.[STUD_ID] AND rn=1


Managing code and deployments to Amazon Redshift

In general there is very little tool support at present for Redshift. We do all of our automation and deployment via psql shell scripts and we manage the code base using git version control.

As far as schema changes we use a "migrations" approach (inspired by Rails) where we create ALTER TABLE …ADD COLUMN and ALTER TABLE …DROP COLUMN scripts to roll the database forward/back from one deployment to the next.

We also use 3 separate database on the same cluster for Dev/Test/Prod. We then specify the target database in the psql script using the -d option.

Hope that helps.


Group and order output into a single row

If you're running on MySQL:

SELECT
    c.ID,
    ss.LASTNAME,
    ss.FIRSTNAME,
    ss.STREET,
    ss.CITY,
    ss.STATE,
    ss.ZIP,
    GROUP_CONCAT(c.COMMISSION ORDER BY c.DATE SEPARATOR '\n') AS COMMISSION,
    GROUP_CONCAT(c.DATE ORDER BY c.DATE SEPARATOR '\n') AS DATE
FROM db1.Sales_Staff AS ss
JOIN db1.Commission AS c ON (c.ID=ss.ID)
ORDER BY ss.LASTNAME ASC,ss.FIRSTNAME ASC;


How to solve 'tuple concurrently updated' error?

I manage to solve my problem by changing my query to this one:

INSERT INTO my_stat(id, client_addr, pid, usename)
    SELECT nextval('mystat_sequence'), client_addr, pid, usename
    FROM (
        SELECT
           host(client_addr) as client_addr,
           pid ,
           usename
        FROM pg_stat_activity
        WHERE usename = current_user
    ) s
    WHERE
        NOT EXISTS (
           SELECT NULL
           FROM my_stat u
           WHERE current_date = u.creation
               AND s.pid = u.pid
               AND s.client_addr = u.client_addr
               AND s.usename = u.usename
        );

I think something happened under the hood right from the Postgresql internals but I can't figure out what ...


Maintenance Plan Job running at additional unscheduled times?

Well, mystery solved.

I had previously posted this at another site (SqlServerCentral.com), and then posted here after I had not gotten a response for several hours.  While I was trying Aaron's suggestion here, a Josh posted the following there:

  Did you check the history of the job to see how it was invoked?
  Usually you'll see something like "The job succeeded. The Job was
  invoked by Schedule XXXX (Schedule_X ). The last step to run was step
  1 (XXXXXXXXXXXXXX)."

  Does it look the same for each run?

Though I had previously scoured the Job's history, I had not specifically looked for that message. And lo and behold, the unscheduled ones have the following:

... The Job was invoked by Alert 1.  ...

So someone must have tagged this job to run after an Alert (likely the wrong job).

Naturally now I feel silly for not checking this before.  But I post this here now, so that others in the future may benefit from my oversight.


Why can't start pgbouncer process when setting logfile to /var/log/ path?

Either you don't have permission to write to /var/log/pgbouncer as the user you are running as, or the directory doesn't exist and pgbouncer doesn't or can't create it.

A third possibility is that the permissions on that directory are TOO relaxed, but I don't think pgbouncer checks that.


Are there any Database functions which allow access to other network services in PostgreSQL

I have never seen that personally, so I looked up all the functions in Postgres found in the docs here.

The only thing it showed remotely close was 'network address functions' in this section.  Outside of that the docs don't show anything for network access functions.  Note this doesn't mean you can't write your own functions that access the system or OS which then can access the network.


Log Shipping back up location to a network share instead of clustered file share

Log shipping backups aren't anything special under the hood, they run an executable (sqllogship.exe) that fires off a standard BACKUP LOG. They can be configured to point anywhere the service account has write access to (and since this is a FCI, anywhere that is reachable from both nodes).

What's the point of writing these to a network share? Reduce the shared storage requirements (presuming there's no direct attached storage you can easily use)?


Database Encryption scan for database  was aborted. Reissue ALTER DB to resume the scan

Changing the account you use to run SQL Server means SQL Server is unable to unlock the service master key.

The service master key is protected (encrypted) using the Windows Data Protection API, which includes using the machine key and the account key for the GMSA.

When you change the service account, you prevent SQL Server from accessing the correct account key, and thereby prevent SQL Server from being able to decrypt the service master key, and in turn the TDE encryption key.

Changing the service back to the original account allows SQL Server to access the correct keys that allow it to decrypt the service master key, and thereby correctly decrypt all the other keys used in TDE.

I have a couple of related posts at SQLServerScience.com:

Filesystem Security and TDE keys
Cannot use Certificate, Private Key not Present


Moving MySQL to another server without updating connection strings

If you use "localhost" in connection string then the following may work:

edit hosts file and change the line

127.0.0.1   localhost

to

X.X.X.X localhost //( your new ip)

and restart the local application machine.


AWS KMS Encryption enable for SQL Server Columns?

As per sql server blog here On SQL Server side, it is supported to use a custom key store provider for Always Encrypted, but the implementation/support of the custom key store provider comes from the service provider itself, which in this case is the AWS KMS.

For further your ref here


.bak file not visible in any directory in SSMS

We had the same issue today. It turned out to be a permissions issue, as illustrated in some of the other answers. The difference is that the account we needed to add was NT SERVICE\MSSQLSERVER.

I identified the account by comparing the permissions of the default SQL Backup folder permissions to the folder containing the backup file.

Adding the service account is not as simple as it sounds. Here is what worked for me. (Server 2012 R2)

Log into the server. (The change must be made on the actual server, not through a network share.)
Change the Locations to the local server name.
Type in NT SERVICE\MSSQLSERVER in the name box. (Do not click Check Names)
Click OK. You will then see a list of the matching service accounts. Select MSSQLSERVER, and click OK to accept the selection.

You should see the service account listed in the security settings, and can adjust the permissions accordingly.

After adding permissions (I added Full Control, to match the default SQL Backup folder.) and restarting the SQL Server Management Console, I was able to select and restore my backup file.


Progress OpenEdge - how to remove lock from table?

You can use locking hints to select from tables that have locks on them.

select * from tblUser with (NOLOCK) ;

This is ok practice with adhoc sql.

Note Don't use in productioncode without reading and understanding documentation fully.

If you want to kill blocking processes:  http://www.kodyaz.com/articles/identify-kill-blocking-sql-server-processes.aspx


Reviewing all code on a SQL Server Instance

Startup stored procedures run from master each time the instance starts.

Replication with this database as the subscriber.  Not code per se, but it will change data.


As @jkavalik mentioned in the comment, the mysqldiff command stops after the first error it finds.
The --force option continues to look for more differences!


Syntax error when a job is executed in anonymous block in Oracle

Not enough apostrophes.

SQL> !cat 1.sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'JOB_Cambia_Planes_Prueba',
   job_type             => 'PLSQL_BLOCK',
   job_action           =>
'BEGIN
        EXECUTE IMMEDIATE ''ALTER SYSTEM SET resource_manager_plan=PLAN1 SCOPE=BOTH SID=''INST1'''';
exception when others then dbms_output.put_line(sqlerrm);
END;
',
   start_date           =>  TO_DATE('16-12-2019 17:33','DD-MM-YYYY HH24:MI'),
   repeat_interval      => 'FREQ=DAILY',
   enabled              =>  TRUE,
   comments             => 'Cambiar Planes');
exception when others then dbms_output.put_line(sqlerrm);
END;
/

SQL> @1

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.run_job('JOB_Cambia_Planes_Prueba');
BEGIN dbms_scheduler.run_job('JOB_Cambia_Planes_Prueba'); END;

*
ERROR at line 1:
ORA-06550: line 2, column 82:
PLS-00103: Encountered the symbol "INST1" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || multiset bulk
member submultiset
The symbol "* was inserted before "INST1" to continue.
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 1

SQL> exec dbms_scheduler.drop_job('JOB_Cambia_Planes_Prueba');

PL/SQL procedure successfully completed.

SQL> !vi 2.sql

SQL> !cat 2.sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'JOB_Cambia_Planes_Prueba',
   job_type             => 'PLSQL_BLOCK',
   job_action           =>
'BEGIN
        EXECUTE IMMEDIATE ''ALTER SYSTEM SET resource_manager_plan=PLAN1 SCOPE=BOTH SID=''''INST1'''''';
exception when others then dbms_output.put_line(sqlerrm);
END;
',
   start_date           =>  TO_DATE('16-12-2019 17:33','DD-MM-YYYY HH24:MI'),
   repeat_interval      => 'FREQ=DAILY',
   enabled              =>  TRUE,
   comments             => 'Cambiar Planes');
exception when others then dbms_output.put_line(sqlerrm);
END;
/

SQL> @2

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.run_job('JOB_Cambia_Planes_Prueba');

PL/SQL procedure successfully completed.

To elaborate, this value of job_action

'BEGIN
        EXECUTE IMMEDIATE ''ALTER SYSTEM SET resource_manager_plan=PLAN1 SCOPE=BOTH SID=''INST1'''';
exception when others then dbms_output.put_line(sqlerrm);
END;
',

is a string literal. Obviously, string literals are delimited with single quotes ('). That string, however, is a script containing another string literal, the ALTER SYSTEM command, which must be delimited too. As you know, you escape a quotation mark inside a string by doubling it. So, the ALTER SYSTEM command inside the string literal must be delimited with '' rather than '. This is what you have done, so far so good.

Now, the ALTER SYSTEM command, in its turn, also contains a string literal, namely INST1. This is now the second level of string nesting. You need to double the delimiters again, so INST1 should be delimited with ''''. In your snippet, however, it is delimited with '', which is wrong.

Therefore, the solution is to rewrite the … SID=''INST1'''' bit as … SID=''''INST1'''''', as already illustrated above. The closing six apostrophes are the four closing apostrophes for the INST1 value plus the two closing apostrophes for the ALTER SYSTEM command.


Can I query stored procs in all databases with one query without dynamic sql

You can do it without the cursor (or without a cursor-like operation). For example you can do it slightly differently like this:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + CHAR(13) + CHAR(10)
  + N'select ''' + db.name + ''', p.name
        from ' + QUOTENAME(db.name) + N'.sys.procedures AS p
        WHERE p.name = N''usp_MyProc''
        COLLATE SQL_Latin1_General_CP1_CI_AI;'
    FROM sys.databases AS db -- WHERE ...;

EXEC sp_executesql @sql;

You can also avoid multiple result sets going back by doing this:

CREATE TABLE #x(d SYSNAME, n SYSNAME);

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + CHAR(13) + CHAR(10)
  + N'insert #x select ''' + db.name + ''', p.name
        FROM ' + QUOTENAME(db.name) + N'.sys.procedures AS p
        WHERE p.name = N''usp_MyProc''
        COLLATE SQL_Latin1_General_CP1_CI_AI;'
    FROM sys.databases AS db -- WHERE ...;

EXEC sp_executesql @sql;

SELECT d, n FROM #x ORDER BY d, n;

If you're really only after a single, specific stored procedure though, see Martin's answer.


An Internal Routine Has Requested A Dump Of Selected Redo - Message Appears In Alert Log (Doc ID 2295361.1)

  ...

  SOLUTION

  You can ignore the message related to redo log if there is no error
  along with this information. Please note that for now, there is no way
  to disable the message by setting any event,  because these
  informative messages are useful in some cases if something goes wrong.

  ...


MariaDB - breaking up a multi join into CTEs

Your 'join' version is plenty good.  For performance, you need these indexes (or PKs):

dl:  (mongodb_id, account_id,  -- in either order
      id)                      -- third
dll:  (display_list_id, list_id)  -- in this order
li:  (list_id)
i:  (id, mongodb_id)   -- unless (id) is the PRIMARY KEY

Index Cookbook


I am trying to write a search query on MYSQL to find the users

People who didn't attend any concert should not be reference in your Attended table by definition.

So you could maybe try to do your requests with a left or right join and put a criteria on records with null id, maybe something like this:

SELECT People.FirstName from People
LEFT JOIN Attended on Attended.Customer = People.ID
WHERE Attended.Customer is null;

This is how I thought my query :

By using a left join, I'll get all records from left table (here People), with matching record from right table (here Attended).

The missing records (i.e. records on which there is no match with given on clause) are replaced by null value - thus, if I can't find an Attended record for my people ID, I'll get Null for Attended Customer column.

My WHERE clause filter all Attended record which are not null, leaving me with only people who don't have matching Attended record - these are people who don't go to concert.


Do you need something like this?

WITH cte AS ( SELECT dates.date date, series.generate_series transect
              FROM ( SELECT DISTINCT date
                     FROM datatable ) dates,
                   ( SELECT *
                     FROM generate_series(1, 5) ) series
            )
SELECT cte.date, COALESCE(datatable.value, 0) AS value, cte.transect
FROM cte
LEFT JOIN datatable ON cte.date = datatable.date
                   AND cte.transect = datatable.transect;


Factors to consider while Rebuild/Reorganize of index in SQL Server 2012

I'm going to go in a different direction than other answers and ask: After your defragmentation routine has run for however many minutes or hours, and used all sorts of resources (CPU, memory, disk, probably tempdb), how do you quantify and compare the expenditure of time and server resources to defragment indexes to any improvements you've gained from doing so?

I realize this doesn't answer your question, but I want you to think carefully about what you're doing rather than get yourself all worked up and your server all beat up about a non-issue.

There's a weird obsession (and believe me, I used to have it, too) with SQL Server users around index fragmentation. It's usually based on advice from Microsoft from 15 years ago and lots of hysterical responses on Q&A forums (this one included).

People have had the numbers you reference (5% to reorg, 30% to rebuild) pounded into their head with religious fervor forever. So they keep doing it. I've seen it cause all sorts of problems, too. Blocking and deadlocks, corruption, interruptions to production workload (long running maintenance), and worst of all: performing index maintenance at the exclusion of more important maintenance, like DBCC CHECKDB.

The one good thing that index rebuilds do is update statistics, which (simplifying greatly) will usually give the optimizer much better information about the data it's working with, and (again, simplifying greatly) can chase a bad execution plan out of the cache.

People will also do counterproductive things in maintenance plans like rebuild all their indexes, reorganize all their indexes, and then update statistics. I realize this likely isn't you, just adding it for completeness.

There are also more considerations than just fragmentation percent to rebuild or reorg:

Do queries even use this index?
Do I really want to reorg and LOB compact a 50+ GB index (single threaded, ew)?
Am I on Standard Edition where rebuilds are OFFLINE and can cause blocking?

For a bit of background, check out the blog posts here. Full disclosure; it's the company I work for, though people other than me have written a lot about it. There's even a post in there about when index fragmentation does matter.

Other database platforms have had better advice about this topic for a long time.

EDIT: The best way to have index fragmentation never ever in a million billion years matter: cache all your data in RAM.


This is a job better done on application layer. Databases are not suited for this. Just do

SELECT category_id, set_order FROM your_table ORDER BY category_id DESC;

Then write a function that rearranges the array returned by above query.

For fun I did it in MySQL, just to let you see, how complicated this can be. So you see it's doable, note though, that this can be horrible performance-wise.

Sample data:

CREATE TABLE t
    (`category_id` int, `set_order` int)
;

INSERT INTO t
    (`category_id`, `set_order`)
VALUES
    (80, NULL),
    (81, NULL),
    (82, NULL),
    (83, '2'),
    (84, NULL),
    (85, '4'),
    (86, NULL),
    (87, NULL),
    (88, NULL),
    (89, NULL),
    (90, NULL)
;

Query:

SELECT category_id FROM (
    SELECT
    category_id, set_order
    , IF(set_order IS NULL, @newOrder := IF(@newOrder + 1 IN (SELECT set_order FROM t WHERE set_order IS NOT NULL), @newOrder + 2, @newOrder + 1), NULL) new_order
    FROM t,
    (SELECT @newOrder := 0) var_init_subquery_alias
    ORDER BY category_id DESC
) sq ORDER BY COALESCE(new_order, set_order);

Result:

+-------------+
| category_id |
+-------------+
|          90 |
|          83 |
|          89 |
|          85 |
|          88 |
|          87 |
|          86 |
|          84 |
|          82 |
|          81 |
|          80 |
+-------------+


How to aggregate timeseries several months long - when I only want a portion of each month

NOTE: I'm assuming you're running SQL Server 2012 (or higher), otherwise the datefromparts() code will need to be modified.

I'd opt for getting rid of the loops in favor of a single query that includes a date generator, which is relatively easy to do with a common table expression (CTE), eg:

declare @days   tinyint,         -- first X days of a month to query
        @months smallint,        -- go back Y months for our query
        @today  date

select  @days   = 5              -- only interested in days 1-5 of each month
        @months = 4              -- only interested in this month plus the previous 4 months
        @today  = sysdatetime();

with
dategen as
(select -- piece our years/months/days together into dates
        datefromparts ( year(dateadd(m,-m.number,@today)),  -- get year  for @today minus m.number of months
                       month(dateadd(m,-m.number,@today)),  -- get month for @today minus m.number of months
                       d.number) as search_date             -- day of month

 from   master..spt_values m,
        master..spt_values d

 -- no join clause => cartesian product (or you could add a cross join, your call)
 where  m.type = 'P'
 and    m.number between 0 and @months
 and    d.type = 'P'
 and    d.number between 1 and @days)

select  search_date
from    dategen
where   search_date <= @today
order by 1

 | search_date         |  @today = 18 November 2017
 | ------------------- |
 | 01/07/2017 00:00:00 |  first 5 days of July 2017
 | 02/07/2017 00:00:00 |
 | 03/07/2017 00:00:00 |
 | 04/07/2017 00:00:00 |
 | 05/07/2017 00:00:00 |

 | 01/08/2017 00:00:00 |  first 5 days of August 2017
 | 02/08/2017 00:00:00 |
 | 03/08/2017 00:00:00 |
 | 04/08/2017 00:00:00 |
 | 05/08/2017 00:00:00 |

 | 01/09/2017 00:00:00 |  first 5 days of September 2017
 | 02/09/2017 00:00:00 |
 | 03/09/2017 00:00:00 |
 | 04/09/2017 00:00:00 |
 | 05/09/2017 00:00:00 |

 | 01/10/2017 00:00:00 |  first 5 days of October 2017
 | 02/10/2017 00:00:00 |
 | 03/10/2017 00:00:00 |
 | 04/10/2017 00:00:00 |
 | 05/10/2017 00:00:00 |

 | 01/11/2017 00:00:00 |  first 5 days of November 2017
 | 02/11/2017 00:00:00 |
 | 03/11/2017 00:00:00 |
 | 04/11/2017 00:00:00 |
 | 05/11/2017 00:00:00 |

Here's a dbfiddle for the (above) CTE example.

Plugging the CTE into your insert/select would generate something like:

-- declare/select @variables;

with
dategen as
(select datefromparts( year(dateadd(m,-m.number,@today)),
                      month(dateadd(m,-m.number,@today)),
                      d.number) as search_date

 from   master..spt_values m,
        master..spt_values d

 where  m.type = 'P'
 and    m.number between 0 and @months
 and    d.type = 'P'
 and    d.number between 1 and @days)

INSERT INTO #DailyVolumes
SELECT CONCAT('PREFIX-',DWRccn.contract_name)                              AS contract_name
      ,count(distinct(CONCAT(rtrim(p.xxxx_id), '-', rtrim(r.lis_req_id)))) AS Volume
      ,a.date_registered

FROM   accession a

join   dategen dg
on     a.date_registered = dg.search_date

Left
join   value1 P
on     a.value1_id = p.value1_id

left
join   [DataWarehouseReporting].[dbo].[DIM_contract_code_name] DWRccn
on     a.contract_code = DWRccn.contract_code

where
AND    a.lis_code = 'S'
AND    visit_type IN ('I', 'E')
AND    dg.search_date <= @today

GROUP BY CONCAT('PREFIX-',DWRccn.contract_name)
        ,a.date_registered

NOTES:

rename local variables as you see fit
set the @months/@days values to your desired limits
I haven't tested the insert/select (don't have the DDL for your system) so there may be some minor syntax issues ... ??
if performance is still not quite up to what you're expecting then it'll be necessary to review the query plan (and potentially the available indexes)

Assuming the (above) CTE/insert/select query generates the desired results, the next step would be to eliminate #DailyVolumes and add the final select/sum(Volume) code to the above query ... easiest to understand would probably be something like:

with

dategen as
(select ...),

dailyvolumes as
(select ... from accession/dategen/value1/DWRcnn ...)

select sum(Volume) ...
from   dailyvolumes ...


Specify Server for DBMS_Scheduler Job in Policy Managed RAC

dbms_scheduler.set_attribute is what you're looking for - it forces a job to run on a given node (specified by instance_id):

begin
dbms_scheduler.create_job (
job_name=>'PHIL',
job_type=>'plsql_block',
job_action=>'NULL;',
repeat_interval=>'freq=minutely;interval=2');

end;
/

begin
dbms_scheduler.set_attribute('PHIL','instance_id',1);
end;
/

dba_scheduler_jobs.instance_id will be NULL unless you have explicitly set it with set_attribute.

I realise that this isn't what you asked, but it's easier than messing with pools!


Package performs differently on two different instances on the same server

It turns out this issue was due to a restore of a database from an incorrect media set.  Since the databases were not exact copies, the package was performing differently on the second instance.  After performing a backup with INIT and then restoring that database to the second instance, the package now performs the same on both instances.


How do I totally remove SQL Server 2017 on Ubuntu?

These are actually very poor instructions on how to remove the Microsoft SQL Server.

SQL Server installs these associated packages.

mssql-server
mssql-server-fts
mssql-tools
msodbcsql
unixodbc-dev

Assuming you wish to totally remove SQL Server, you should

# Stop the service
systemctl stop mssql-server.service

sudo add-apt-repository --remove "$(curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list)"

# Remove those packages from the system.
sudo apt-get --purge remove mssql-server mssql-server-fts mssql-tools msodbcsql unixodbc-dev

# Delete the SQL Server repositories
sudo rm /etc/apt/sources.list.d/mssql-* /etc/apt/sources.list.d/msprod*

# Remove the Microsoft keys.
apt-key del "BC52 8686 B50D 79E3 39D3  721C EB3E 94AD BE12 29CF"

# Delete the Microsoft created directories
sudo rm -rf /var/opt/mssql/ /opt/{microsoft,mssql-tools,mssql}/ /usr/share/doc/{msodbcsql,mssql-server,mssql-tools}


Stop SQL Server 2012 From Highlighting Implicitly Passed Temp Tables "Invalid Object"

Is there any way to "force" knowledge of a Temp Table's schema into a Stored Proc?

You could pass the data using a Table-Valued Parameter instead.  Or you could try something like this:

create proc usp_myProc
as
begin
    create table #temp
    (
        id int
        ,data int
    )

    exec usp_mySubProc;
end
go

create proc usp_mySubProc
as
begin

    --table creation will be skipped at runtime
    if 1=0 create table #temp(id int, data int)

    update
        #temp
    set
        data = 1

end
go


How important is the build number on a SQL Cluster

You probably don't need anyone to tell you that you need to fix the build level on that third node! I suspect it's all working ok because of the introduction of the resource database in SQL2005:
http://msdn.microsoft.com/en-us/library/ms190940.aspx

I vaguely remember an old Windows 2000 / SQL Server 2000 cluster I used to work with, where SQL thought it was a lower patched level on one node compared to the other.


SQL Server Temporal Tables History Table size

Or does it do some kind of compression under the hood to realize that
  most of the data did not change and does not need to be stored each
  time?

By default, the  temporal table's history table is PAGE compressed.

Source

  To ask it I am going to create a hypothetical situation. Say I have a
  table with 300 columns in it. Each column has a char(10) in it. (3,000
  bytes).

  I insert a row into the main table.

  I then go an modify one of the columns with an update statement. That
  causes the modified row to be inserted into the history table.

  I then change that same column 4 more times (5 modifications total.)

  What I am wondering is if that means that the size of my history table
  is now 3,000 x 5 = 15,000 bytes?

Testing

Here is some pseudo code to create the table & add  300 char(10) columns

CREATE TABLE dbo.BigTable (id int identity(1,1)  primary key not null   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON)
;

  To ask it I am going to create a hypothetical situation. Say I have a
  table with 300 columns in it. Each column has a char(10) in it. (3,000
  bytes).

SELECT
TOP(300)
 'ALTER TABLE dbo.BigTable ADD COLUMN'+ CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(50))+ ' char(10)'
FROM master..spt_values;

  I insert a row into the main table.

INSERT INTO dbo.BigTable
(
Columns 1 Until 300
)
VALUES
('bla', * 300);

  I then go an modify one of the columns with an update statement. That
  causes the modified row to be inserted into the history table. I then
  change that same column 4 more times (5 modifications total.)

UPDATE dbo.BigTable
SET [COLUMN7] = 'blabla';
GO 5

  What I am wondering is if that means that the size of my history table
  is now 3,000 x 5 = 15,000 bytes?

Well, when using the query from this source

This is the result:

SchemaName  TableName   RowCounts   Used_MB Unused_MB   Total_MB
dbo MSSQL_TemporalHistoryFor_2002106173 5   0.02    0.05    0.07

We get about 0.02 MB used for this history table, which is more than the 0.015mb specified.

However when we check SSMS, the details are lower.

Only 0.008 MB?

A simplified version of the query being run is this one:

DECLARE
@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)
SELECT @_msparam_0=N'1',@_msparam_1=N'E',@_msparam_2=N'MSSQL_TemporalHistoryFor_2002106173',@_msparam_3=N'dbo'
        declare @PageSize float
        select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=@_msparam_0 and v.type=@_msparam_1

SELECT         ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
        FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
        where i.object_id = tbl.object_id),0.0)
FROM
sys.tables AS tbl
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
OPTION (FORCE ORDER)

Even more simplified, we see that the history table's clustered index data is only one data page;

SELECT  i.name, i.index_id,a.data_pages,i.type_desc
  FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
WHERE i.name = 'ix_MSSQL_TemporalHistoryFor_2002106173'

Result

name    index_id    data_pages  type_desc
ix_MSSQL_TemporalHistoryFor_2002106173  1   1   CLUSTERED

Why the difference?

This is due to the ssms query only considering  data_pages. This differs from  used_pages

Consider this query:

SELECT  i.name, i.index_id,a.data_pages,a.used_pages,a.total_pages, i.type_desc
  FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
WHERE i.name = 'ix_MSSQL_TemporalHistoryFor_2002106173'

name    index_id    data_pages  used_pages  total_pages type_desc
ix_MSSQL_TemporalHistoryFor_2002106173  1   1   2   9   CLUSTERED

Not all used_pages will be data_pages, while all data_pages will be used_pages.

There are overhead pages like PFS, GAM, SGAM, HEADER PAGE etc.

Summary

In our example, due to  the PAGE compression, the data stored in the history table is in the 8KB range.
This results in one data page used.

The SSMS Query only considers data_pages, while the other query takes into account all used_pages.

For each update, an additional row is stored in the history table, without (page) compression, the data pages would amount to 2 (and be above or equal to 15k bytes).


No, your colleague is wrong.

All SQL proroducts - DBMS that behave according to the SQL standards - provide no guarantee that the result of a query output will be ordered in any way, unless there is an ORDER BY clause in the query.

As the IBM DB2 docs mention:

  Ordering is performed in accordance with the comparison rules described in Language elements. The null value is higher than all other values. If your ordering specification does not determine a complete ordering, rows with duplicate values of the last identified sort-key have an arbitrary order. If you do not specify ORDER BY, the rows of the result table have an arbitrary order.


How to query last restore date in SQL Server?

This will list all "most recent" restores for each database on your server:

WITH LastRestores AS
(
SELECT
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1


How does Postgres know which logs have been archived?

On master there is archive_status directory in pg_xlog which contains files that mark status of archiving of xlogs.


How to not "duplicate" a table if it has a unique constraint over many columns?

With PostgreSQL 9.5 you can enjoy a BRIN index (which will make the index very small, yet functional), and handle the exclusions via a trigger, like this:

CREATE INDEX ON medi_cal_base_eligibility USING BRIN (client_index_number);

CREATE OR REPLACE FUNCTION tf_medi_cal_base_eligibility_insert() RETURNS trigger AS
$BODY$
BEGIN
  IF (TG_OP = 'INSERT' OR (NEW.client_index_number, NEW.eligibility_date, NEW.medi_cal_date, NEW.cardinal) IS DISTINCT FROM (OLD.client_index_number, OLD.eligibility_date, OLD.medi_cal_date, OLD.cardinal)) AND
     EXISTS (SELECT 1 FROM medi_cal_base_eligibility
             WHERE (client_index_number, eligibility_date, medi_cal_date, cardinal) = (NEW.client_index_number, NEW.eligibility_date, NEW.medi_cal_date, NEW.cardinal)) THEN
    RAISE 'Duplicate key: %, %, %, %', NEW.client_index_number, NEW.eligibility_date, NEW.medi_cal_date, NEW.cardinal;
    RETURN NULL;
  END IF;
  RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

CREATE TRIGGER t_medi_cal_base_eligibility_insert BEFORE INSERT OR UPDATE ON medi_cal_base_eligibility
    FOR EACH ROW EXECUTE PROCEDURE tf_medi_cal_base_eligibility_insert();

As stated by dezso, a BRIN index would only be useful in a situation in which client_index_number has a correlation to the position of the record in the file.

If the solution above using BRIN won't cut the mustard, a good choice is using a hash of the data to search for. The size of the hash will determine how many records it will have to scan to look for uniqueness; also, the bigger the hash, the bigger the index. A hash of 32 bits would most likely render unique results (or at most a handful) and would be just as large as your primary key. In the following example I'm getting the 32 bits hash by using the last 8 hexadecimal digits of the md5 function using your four unique fields concatenated together.

CREATE OR REPLACE FUNCTION f_medi_cal_base_eligibility_to_int (p_client_index_number text, p_medi_cal_date date, p_eligibility_date date, p_cardinal smallint) RETURNS int AS $BODY$
  SELECT ('x'||right(md5($1 || to_char($2, 'YYYYMMDD') || to_char($3, 'YYYYMMDD') || $4::text), 8))::bit(32)::int
$BODY$ LANGUAGE SQL IMMUTABLE SECURITY DEFINER;

CREATE INDEX ON medi_cal_base_eligibility (f_medi_cal_base_eligibility_to_int(client_index_number, medi_cal_date, eligibility_date, cardinal));

CREATE OR REPLACE FUNCTION tf_medi_cal_base_eligibility_insert() RETURNS trigger AS
$BODY$
BEGIN
  IF (TG_OP = 'INSERT' OR (NEW.client_index_number, NEW.eligibility_date, NEW.medi_cal_date, NEW.cardinal) IS DISTINCT FROM (OLD.client_index_number, OLD.eligibility_date, OLD.medi_cal_date, OLD.cardinal)) AND
     EXISTS (SELECT 1 FROM medi_cal_base_eligibility
             WHERE (f_medi_cal_base_eligibility_to_int(client_index_number, medi_cal_date, eligibility_date, cardinal), client_index_number, medi_cal_date, eligibility_date, cardinal) =
                   (f_medi_cal_base_eligibility_to_int(NEW.client_index_number, NEW.medi_cal_date, NEW.eligibility_date, NEW.cardinal), NEW.client_index_number, NEW.medi_cal_date, NEW.eligibility_date, NEW.cardinal)) THEN
    RAISE 'Duplicate key: %, %, %, %', NEW.client_index_number, NEW.medi_cal_date, NEW.eligibility_date, NEW.cardinal;
    RETURN NULL;
  END IF;
  RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

CREATE TRIGGER t_medi_cal_base_eligibility_insert BEFORE INSERT OR UPDATE ON medi_cal_base_eligibility
    FOR EACH ROW EXECUTE PROCEDURE tf_medi_cal_base_eligibility_insert();


To see all queries sent to a server check the general/slow query log (it must be activated for that server, of course).
Alternatively, for these graphs check the source code of MySQL Workbench. Use the labels below the graphs to find a location in code. For example the "InnoDB Buffer Usage" graph is created in plugins/wb.admin/Frontend/wb_admin_monitor.py. Study the code to learn which variables are queried to get series of values.


SQL Developer code 'Advanced Format' versus 'Custom Format'

The custom format allows you to augment what's available with GUI controls in the advanced formatter preferences page.

It uses arbori, a parse tree query language.

The developer, Vadim, discusses this in his blog here.

So you don't pick one or the other - both are in play when using the formatter.


Automatically detect table name in MSSQL Server database using stored function

I think you would need to use a trigger on the table(s) in question in order to get the table name.

How I have implemented table change tracking is by just that: 1) Create a table to hold the changes and 2) Create a trigger on each table that needed to be tracked.

The triggers ALL look like this (where you replace 'XX_YOURTABLE_XX' with the table in question and [tr_XXXX] with a unique trigger name ):

CREATE TRIGGER [dbo].[tr_XXXX] on [dbo].[XX_YOURTABLE_XX] for INSERT, UPDATE, DELETE
AS

DECLARE
    @bit INT ,
    @field INT ,
    @maxfield INT ,
    @char INT ,
    @fieldname VARCHAR(128) ,
    @TableName VARCHAR(128) ,
    @PKCols VARCHAR(1000) ,
    @sql VARCHAR(2000),
    @UserName VARCHAR(128) ,
    @Type char(1) ,
    @PKSELECT VARCHAR(1000)

    SELECT @TableName = 'XX_YOURTABLE_XX'

    -- Get User
    IF object_id('tempdb..#TmpUser') IS NOT NULL
        SELECT  @UserName = TheUser FROM #TmpUser
    ELSE
        SELECT  @UserName = system_user

    -- Action
    IF EXISTS (SELECT * FROM INSERTED)
        IF EXISTS (SELECT * FROM DELETED)
            SELECT @Type = 'U'  --UPDATE
        ELSE
            SELECT @Type = 'I'  --INSERT
    ELSE
        SELECT @Type = 'D'      --DELETE

    -- get lISt of columns
    SELECT * INTo #ins FROM INSERTED
    SELECT * INTo #del FROM DELETED

    -- Get primary key columns for full outer join
    SELECT  @PKCols = coalesce(@PKCols + ' AND', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
    FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    WHERE   pk.TABLE_NAME = @TableName
            AND CONSTRAINT_TYPE = 'PRIMARY KEY'
            AND c.TABLE_NAME = pk.TABLE_NAME
            AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    -- Get primary key SELECT for INSERT
    SELECT @PKSELECT = coalesce(@PKSELECT+'+','') + '''<' + COLUMN_NAME + '=''+convert(VARCHAR(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
    FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    WHERE   pk.TABLE_NAME = @TableName
            AND CONSTRAINT_TYPE = 'PRIMARY KEY'
            AND c.TABLE_NAME = pk.TABLE_NAME
            AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    IF @PKCols IS NULL
    BEGIN
        RAISERROR('no PK on table %s', 16, -1, @TableName)
        RETURN
    END

    SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
    WHILE @field < @maxfield
    BEGIN
        SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
        SELECT @bit = (@field - 1 )% 8 + 1
        SELECT @bit = power(2,@bit - 1)
        SELECT @char = ((@field - 1) / 8) + 1
        IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
        BEGIN
            SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @field
            SELECT @sql =       'INSERT tbl_TRACKING (Type, TableName, PK, FieldName, OldValue, NewValue, UserName)'
            SELECT @sql = @sql +    ' SELECT ''' + @Type + ''''
            SELECT @sql = @sql +    ',''' + @TableName + ''''
            SELECT @sql = @sql +    ',' + REPLACE(REPLACE(REPLACE(@PKSELECT,'<',''),'>',''),'PriKeyVal=','')
            SELECT @sql = @sql +    ',''' + @fieldname + ''''
            SELECT @sql = @sql +    ',convert(VARCHAR(1000),d.' + @fieldname + ')'
            SELECT @sql = @sql +    ',convert(VARCHAR(1000),i.' + @fieldname + ')'
            SELECT @sql = @sql +    ',''' + @UserName + ''''
            SELECT @sql = @sql +    ' FROM #ins i full outer join #del d'
            SELECT @sql = @sql +    @PKCols
            SELECT @sql = @sql +    ' WHERE i.' + @fieldname + ' <> d.' + @fieldname
            SELECT @sql = @sql +    ' or (i.' + @fieldname + ' IS NULL AND  d.' + @fieldname + ' IS not NULL)'
            SELECT @sql = @sql +    ' or (i.' + @fieldname + ' IS not NULL AND  d.' + @fieldname + ' IS NULL)'
            exec (@sql)
        END
    END

GO

The tracking table looks like this:

CREATE TABLE [dbo].[tbl_TRACKING](
    [Type] [char](1) NULL,
    [TableName] [varchar](128) NULL,
    [PK] [varchar](1000) NULL,
    [FieldName] [varchar](128) NULL,
    [OldValue] [varchar](1000) NULL,
    [NewValue] [varchar](1000) NULL,
    [ActionDate] [datetimeoffset](3) NULL,
    [UserName] [varchar](128) NULL,
    [AppName] [varchar](128) NULL,
    [ComputerName] [varchar](128) NULL
)

Hope that helps


Incremental column depening on column value

Just found the solution after banging my head into the wall:

SELECT
  t.*,
  @row := IF(@lastState = t.State, @row + 1, 0) as counter,
  @lastFunkt := t.State
FROM
  table t,
  (SELECT @row := 0,
          @lastState := '') SQLVars


SQL Server express - Management studio installation hangs at native images installation

Disabling AVG anti-virus software before starting the install setup allowed me to avoid this problem.


MySQL Query missing from the slow query log

Thanks to Vinicius@Percona.  He explained the reason the query does not appear in the slow query log is due to the fact that existing connections are not impacted by the long_query_time being changed.  Only new connections.


I am not sure if this would help solve the issue, but you can give it a shot.

Run:

RESTORE DATABASE YourDatabase WITH RECOVERY

See if the above brings the database out of recovering mode. If it doesn't then the problem may be something else.

Can you try the below command and see if the database is corrupt?

DBCC CHECKDB ('YourDBname') WITH NO_INFOMSGS, ALL_ERRORMSGS

If it detects corruption, then you might want to repair the database using DBCC CHECKDB


Allow Field to be NULL and every user that has no Field is researcher.
If field isn't enough there is also TeamId which can be -1 for magaers.
Or a combination of both
But to be honest a Type field make a lot if sense as Tinyint, and you  are felible for future as there must be student helpers and so on


what will happen to the binary log if it reaches its maximum value ? Does it reset to mysql-bin.00001 again?

If the binlog file number reaches its maximum value, it appears the server will throw an error.

However, the maximum value is not 999999 as it would appear ... it's 2,147,483,647.

From the source file sql/log.h (MySQL 5.6 source tree):

#define MAX_LOG_UNIQUE_FN_EXT 0x7FFFFFFF

Other relevant files are sql/binlog.cc and sql/log.cc.  The code that generates log file names zero-pads values to 6 digits after incrementing the current largest number by 1, but sprintf(ext_buf, "%06lu", next) will not truncate larger numbers, it just won't pad them... so mysql-bin.999999 would be followed by mysql-bin.1000000.

And, according to the Google Machine, somebody apparently tried it just to make sure.


PostgreSQL migrate data from one table to another (which inherits the first one)

You can use this method (assuming both tables have same columns in same order):

with T as (delete from only company where id=? returning *)
    insert into school select * from T


Primary index is being used, not the one which is on the field present in the where condition

Possibly these composite indexes will speed it up:

cat: (acc_id, islive_in, cat_id)
cat_brand: (acc_id, cat_id, brand_id)

SHOW CREATE TABLE may give more clues as to why the Optimizer picked what it did.


Find the days difference between two dates per month

Pretending this is your real table:

CREATE TABLE #Employees
(
  EmployeeID INT PRIMARY KEY,
  StartDate  SMALLDATETIME,
  EndDate    SMALLDATETIME
);

INSERT #Employees(EmployeeID, StartDate, EndDate)
  VALUES(1,'20150621','20150821');

Then this is one approach to get the results you're after (you'll need to replace #Employees with dbo.YourRealTableName of course, and this assumes you won't have anyone who has worked more months than the number of rows in your version of sys.all_columns - if you have a Numbers table that is a better approach). I added year to the output because, hopefully, you have employees that last longer than 12 months and probably some of those will last past January 1st - so being able to tell which June is which might be important.

;WITH x AS
(
  SELECT rn1 = ROW_NUMBER() OVER (ORDER BY [object_id])-1
  FROM sys.all_columns
),
y AS
(
  SELECT
    e.EmployeeID,
    x.rn1,
    rn2 = ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY x.rn1 DESC),
    e.StartDate,
    EndDate = COALESCE(e.EndDate, DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)),
    sm = DATEADD(DAY, 1-DAY(StartDate), StartDate)
  FROM x INNER JOIN #Employees AS e
  ON x.rn1 <= DATEDIFF(MONTH, e.StartDate, COALESCE(e.EndDate, GETDATE()))
),
z AS
(
  SELECT
    EmployeeID,
    s = CASE rn2 WHEN 1 THEN StartDate
      ELSE DATEADD(MONTH, rn2-1, sm) END,
    e = CASE rn1 WHEN 0 THEN DATEADD(DAY, 1, EndDate)
      ELSE DATEADD(MONTH, rn2, sm) END
  FROM y
)
SELECT
  EmployeeID,
  [Month] = DATENAME(MONTH, s),
  [Year] = YEAR(s),
  WorkingDays = DATEDIFF(DAY, s, e)
FROM z
ORDER BY EmployeeID, s;
GO

This works for all employees, but you can easily add a filter inside y to limit it to a single employee or a subset. You might also want to filter for only those employees with an EndDate; it wasn't clear to me what you wanted to do with employees who still work there, so I assumed you would want to count working days up until today.

Don't forget to clean up:

DROP TABLE #Employees

Edit for updates (please stop changing the requirements):

CREATE TABLE #Employees
(
  EmployeeID INT PRIMARY KEY,
  StartDate  SMALLDATETIME,
  EndDate    SMALLDATETIME,
  Salary     INT
);

INSERT #Employees(EmployeeID, StartDate, EndDate, Salary)
  VALUES(1,'20150621','20150821',620);

Query:

;WITH x AS
(
  SELECT rn1 = ROW_NUMBER() OVER (ORDER BY [object_id])-1
  FROM sys.all_columns
),
y AS
(
  SELECT
    e.EmployeeID, e.Salary, x.rn1,
    rn2 = ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY x.rn1 DESC),
    e.StartDate,
    EndDate = COALESCE(e.EndDate, DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)),
    sm = DATEADD(DAY, 1-DAY(StartDate), StartDate)
  FROM x INNER JOIN #Employees AS e
  ON x.rn1 <= DATEDIFF(MONTH, e.StartDate, COALESCE(e.EndDate, GETDATE()))
),
z AS
(
  SELECT
    EmployeeID, Salary,
    StartDate, EndDate,
    s = CASE rn2 WHEN 1 THEN StartDate
      ELSE DATEADD(MONTH, rn2-1, sm) END,
    e = CASE rn1 WHEN 0 THEN DATEADD(DAY, 1, EndDate)
      ELSE DATEADD(MONTH, rn2, sm) END
  FROM y
)
SELECT
  EmployeeID,
  [Month] = DATENAME(MONTH, s),
  [Year] = YEAR(s),
  WorkingDays = DATEDIFF(DAY, s, e),
  Portion = Salary * 1.0 * DATEDIFF(DAY, s, e) / (1+DATEDIFF(DAY, StartDate, EndDate))
FROM z
ORDER BY EmployeeID, s;

Results I get (there are decimals because your salary won't always be nicely divisible by the number of working days):

EmployeeID  Month   Year  WorkingDays  Portion
----------  -----   ----  -----------  ----------------
1           June    2015  10           100.000000000000
1           July    2015  31           310.000000000000
1           August  2015  21           210.000000000000

Again, clean up:

DROP TABLE #Employees


Oracle With Statement and Delete Together

The Oracle syntax is DELETE FROM (query). The query can be a SELECT with subquery factoring:

delete from (
   with to_delete as (
      select * from a
   )
   select * from to_delete
);

The above query works in 11gR2 but fails with ORA-01732 (non-updateable view) on an old 9iR2.


how to order a field based on two parts of the row value

A way to order such string is

Step1: Create Split Function

CREATE FUNCTION [dbo].[fnSplit]
(
@List VARCHAR(8000),
@Delimiter CHAR(1) = ','
)
RETURNS @Temp1 TABLE
(
ItemId INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY,
Item VARCHAR(8000) NULL
)
AS BEGIN

DECLARE @item VARCHAR(4000),
@iPos INT

SET @Delimiter = ISNULL(@Delimiter, ',')
SET @List = RTRIM(LTRIM(@List))

-- check for final delimiter
IF RIGHT(@List, 1) <> @Delimiter
-- append final delimiter
SELECT @List = @List + @Delimiter

-- get position of first element
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)

WHILE @iPos > 0
BEGIN
-- get item
SELECT @item = LTRIM(RTRIM(SUBSTRING(@List, 1, @iPos - 1)))
IF @@ERROR <> 0
BREAK
-- remove item form list
SELECT @List = SUBSTRING(@List, @iPos + 1, LEN(@List) - @iPos + 1)
IF @@ERROR <> 0
BREAK
-- insert item
INSERT @Temp1
VALUES ( @item )
IF @@ERROR <> 0
BREAK
-- get position pf next item
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)
IF @@ERROR <> 0
BREAK
END
RETURN
END

GO

Step2: split the string into tokens

create table #string
(
field varchar(500)
)

insert into #string
select '22/4, 30/3, 30/5, 30/8,40/9,40/3'

----------------Order by string Tokens returned by function
select *
from #string cross apply dbo.[fnSplit](field,',') atn
order by ltrim(rtrim(atn.Item ))

drop table #string


Routinely deleting aged postgresql rows via cron

A method that I find useful is to implement the cleanup code in my main programming framework (so Django, I guess, in your case) and expose that function via a URL. Then in my crontab I use wget or curl to invoke the cleanup URL.  That way the cron file is only responsible for the scheduling part, while the code that does the cleanup is kept together with the code that creates the data that needs to be cleaned up.


User SYS logged on from. Where I can find source of access?

Where I can find the logs of this audit user?

By default, audit logs for SYSDBA or SYSOPER privileges are stored on AUDIT_FILE_DEST directory.

SQL> show parameter AUDIT_FILE_DEST

NAME                     TYPE         VALUE
------------------------ ----------- ------------------------------
audit_file_dest          string       /u01/app/oracle/admin/orcl/adump

For non-sys users, audit logs are stored in data dictionary table called `AUD$' and have several views to get these information.

SELECT view_name
FROM   dba_views
WHERE  view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;

  "User SYS logged on from kali". What would this kali be?

Kali is the host name/domain name from which the user way logged on to the database server.


Finding indexes that aren't used often or at all

You should enable the user_statistics plugin. After having this active for a while, check out the INDEX_STATISTICS table in the information_schema. The table shows which indexes have been used since last time MariaDB was started, so if they're not listed here then they're good candidates for being purged.

Also use pt-duplicate-key-checker to find duplicated indexes.

All this is nicely written up by Federico Razzoli in Dropping useless MySQL indexes.


Postgres chooses seq scan over index scan

Yes, you are right, the problem is the high cost for the Index Scan using ix_foo_history_foo_id.  Observe that it estimates way too many result rows.
Perhaps it helps to
ANALYZE foo_history;

If that alone does not put the estimates right, try
ALTER TABLE foo_history ALTER foo_id SET STATISTICS 1000;
ANALYZE foo_history;

If that does not tilt the scales, perhaps you can INCLUDE some more columns in ix_foo_history_foo_id and replace the SELECT * with only those columns that you really need, so that you get an index-only scan.


Cause of High Disk Queue Length in SQL Server

OK, so, from what I can tell, it was related to a bunch of things:

resizing files (data and log both)
large number of inserts happening while 1. was going on
one particularly heavy query running while 1. was going on
a hard drive with some pretty high i/o times in general
lack of memory on the server, so hitting the page file on top of everything else

Here were my resolutions:

change the autogrowth settings so I'd have consistent growth when it happens, manually grew both the temp and primary database files to have more space, added additional vlogs to the temp database, and set up a notification so I can manually grow when the database gets below a certain space level.
[nothing to be done about it]
made the heavy query run less often; it was loading up data the user didn't always need, so changed it to run on-demand
[working on getting a new server, this app is growing fast]
[working on getting a new server, this app is growing fast]

So, anyway, it was a combination of a whole bunch of different things, mostly related to SQL, but not exclusively (so Will was correct there).

I'd love to split the answer between everyone, as they had portions of it right, but what can you do...


Alternative to ORDER BY GROUP BY to get first matches per ForeignKey

In MariaDB, you can use a window (ranking) function like ROW_NUMBER() or RANK():

with di as
  ( select *,
           rank() over (partition by ProductModelID
                        order by Price)
             as rnk
    from DomainProductPrice as d
    where DomainID = 1
  )
select *
from di
where rnk = 1 ;

Tested at dbfiddle.uk


How to relate 2 tables to a third when the first 2 tables have a relationship?

Here is a suggestion so you can enforce the constraints you want declaratively. (I've simplified the table names a bit, removed the bridge_ prefix.)

We remove footnote_num from:

Table:      a_ref         -- was named:  bridge_a_reference
     a_id,
     ref_id
Primary key:
     (a_id, ref_id)
Foreign keys:
     a_id  -> a
     ref_id -> reference

We add this table - which will basically store only those rows from a_ref with footnote, those you want to add children into the b_ref:

Table:      a_ref_with_footnote
    a_id,
    ref_id,
    footnote_num
Primary key:
    (a_id, ref_id)
Unique key:
    (a_id, footnote_num)
Foreign keys:
    (a_id, ref_id)  -> a_ref

And finally the 3rd table stays as in your design except the foreign keys which now reference the intermediate table (a_ref_with_footnote):

 Table:     b_ref         -- was named: bridge_b_reference:
     a_id,
     b_id,
     ref_id,
 Primary key:
     (b_id, ref_id)
 Foreign keys:
     (a_id, b_id) -> b
     (a_id, ref_id) -> a_ref_with_footnote


Even you are at the "wrong place"...
Command 'wt salvage' of wiretiger tools probably will solve this problem.


You can use the LAG() function:

SELECT
    "objectID",
    "date",
    "value",
    LAG("value", 1) OVER (PARTITION BY "objectID" ORDER BY "date")
        AS previous_value
FROM
    tableX ;

Tested at SQLFiddle


MySQL server wont' start. Reason: Internal error

This has happened to me before and while there's nothing in the logs to really point you in the right direction the first place to start is in the MySQL's configuration file (On a Mac, I believe it is called my.cnf and it is found in /etc).

You may have made some adjustments or tweaks that it doesn't agree with. Going a bit overboard with certain values (max_open_files for example you're requesting 33802 out of the maximum of 8192, this could be the culprit) can lead to this scenario where MySQL just doesn't want to kick off.

Take a look inside your conf file and try editing the values to be your defaults. You may be able to find example files here: /usr/local/mysql/support-files/

Once you fix it and wish to tweak, just tweak one at a time and start MySQL to confirm it can run with that desired change. It's a little more time consuming but it helps avoid scenarios like this in the future.


This is what you want,

SELECT t.*, coalesce( m1.desc, m2.desc ) AS desc
FROM transactions AS t
LEFT OUTER JOIN metadata AS m1
  ON (m1.attribute = 'Name' AND t.name = m1.value )
LEFT OUTER JOIN metadata AS m2
  ON (m2.attribute = 'EmpID' AND t.empid = m2.value );

What we do is join twice once for each join condition.


How to get MAX and MIN in GROUP BY query?

Would you believe...

SELECT col1, MIN(col2), MAX(col2), MIN(col3), MAX(col3)
  FROM table1
 GROUP BY col1;

...?

  each row includes the first value of col2 and col3 for each unique value of col.

That assertion is not exactly true.  That may be what you're seeing, but do not assume this to be meaningful and do not write code based on this observation.  Your original query would not be valid at all were not for a MySQL extension that departs from standard SQL handling of GROUP BY.

  MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause.

...which is what you did in your initial query, col2 and col3 are not nonaggregated columns (columns without an aggregate function applied, and not in the group-by).  When you do this...

  The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate

http://dev.mysql.com/doc/refman/5.5/en/group-by-extensions.html


First, are you really sure that you want to be starting out with a desupported version of the database that is at least 4 major releases out of date?  It would seem much more useful to download a more recent version of Oracle from the Oracle Technology Network (OTN) and to start with that.  You can get the express edition of Oracle 11.2 completely free and the enterprise edition is free if you just want to learn the product.

Second, when you installed Oracle, it should have prompted you to create a database as part of the installation process.  If you chose not to create a database during the installation process, you should run the Database Creation Assistant (DBCA) to create a database now.  Note that what lots of products refer to as a "database" is more like a schema in Oracle.  In general, you would only have one Oracle database on your machine though that database may have multiple schemas.

What operating system are you using?


Does SQL Server Management Studio 2017 Contain a New Explain Feature

No, not a feature - it's turning one of these on:

SET SHOWPLAN_TEXT ON;
SET STATISTICS PROFILE ON;

The SHOWPLAN_TEXT option and STATISTICS PROFILE option are well-documented and have been around for over a decade, but I don't know when SSMS started turning it on for the combo of live query plans and actual plans. I would consider it a bug.

Here's the SSMS bug I filed for it.


Option 1

There are several reasons for this, which I'll explain below.  First, here's how to do it.

Use your choice of standard RDBMS platform.
Set up your schema with several user-configurable fields, and make your application facilitate the configuration on a per-tenant basis.
From the per-tenant metadata, you can create a per-tenant view of their data, which has the filters built in, and the columns named from your metadata.  Any reports provided can also inherit the metadata.  If they want to do M.I. off the data then provide them an extract of the transactional data, or maybe some additional MIS application on a different server if they will pay for that.
Don't try to provide more customisation than this (i.e. no radical changes to the schema) unless the client is prepared to pay for their own private instance and maintaing a custom build.

The reasons behind this are:

These database systems will handle the sort of volumes you describe on fairly ordinary hardware.  You don't really have the sort of transaction volume that merits a NoSQL database.  Unless you have some other architectural reason to want one, there's not much point in going bleeding-edge.
They're mature, well-understood technologies.
System management, backup/restore, replication, reporting and disaster recovery are all well sorted on RDBMS platforms.
You can get client libraries including JDBC for all major RDBMS platforms.
Views can be used for the per-user customisation and generatedfrom your application metadata.
It's substantially more efficient than XML fields or EAV structures.


Trying to allocate 3G for buffer pool in InnoDB fails

Since

show variables like 'version_compile%'; gives you

version_compile_machine : x86
version_compile_os : Win32

Your OS is Windows 7 x64 OS

it looks like you simply need to upgrade the version of MySQL to the 64-bit Windows version.


How to set up triggers in MySQL for copying a field

Create the following four(4) Triggers on the Matrix table

DELIMITER $$
CREATE TRIGGER matrix_ai AFTER INSERT ON Matrix FOR EACH ROW
BEGIN
    INSERT IGNORE INTO OtherInfo (Name) VALUES (NEW.Name);
END; $$
CREATE TRIGGER matrix_bu BEFORE UPDATE ON Matrix FOR EACH ROW
BEGIN
    DECLARE found_key,dummy INT;
    SELECT COUNT(1) INTO found_key FROM Matrix WHERE Name = NEW.Name;
    IF found_key = 1 THEN
        -- Abandon the Trigger of Name already exists
        SELECT data_length INTO dummy FROM information_schema.tables;
    END IF;
END; $$
CREATE TRIGGER matrix_au AFTER UPDATE ON Matrix FOR EACH ROW
BEGIN
    UPDATE OtherInfo SET Name=NEW.Name WHERE Name=OLD.Name;
END; $$
CREATE TRIGGER matrix_ad AFTER DELETE ON Matrix FOR EACH ROW
BEGIN
    DELETE FROM OtherInfo WHERE Name=OLD.Name;
END; $$
DELIMITER ;

What does each trigger do?

matrix_ai INSERTs the new Name into OtherInfo after it is inserted into Matrix
matrix_bu performs a validation to make sure you do not change the old Name into a New Name that already exists in the Matrix table
matrix_au UPDATEs the old Name in OtherInfo to the new Name you updated in Matrix
matrix_ad DELETEs the Name from OtherInfo that you just deleted from Matrix


Postgres Full Text Search With Short Word Matching

'route' and 'routing' stem to 'rout'.  Neither 'rou' nor 'routi' do.  So, they don't match.

You can use the prefix match to make 'rou' match, but plainto_tsquery doesn't understand that so you have to make the tsquery yourself:

SELECT ts_headline('english', inner_text, query, 'StartSel = , StopSel = , ShortWord = 1') as inner_text_highlights
FROM (SELECT inner_text, ts_rank_cd(tokens, query) AS rank, query
    FROM doctokens, (select 'rou:*'::tsquery as query ) AS query
    WHERE tokens @@ query
    ORDER BY rank DESC
    LIMIT 5) AS query_results;
              inner_text_highlights
--------------------------------------------------
 Route builders give you a fluent syntax
 Does Routing rhyme with looting?

Prefix matching won't let you match 'routi:*', because the things it would match against get the 'i' stripped off when stemming.


You have to create a login on the prod server as when you restore the backup to PROD server, you are having an orphaned user.

So steps are :

Create Login using SID option (if you are just having 1 login to map). Else use sp_help_revlogin
You can use SID from sys.database_principals where type_desc = SQL_USER (since you mentioned you are using SQL Login).
Then run ALTER USER as below :

USE [DatabaseName]
go
ALTER USER [UserName]
WITH LOGIN = [UserName]

Note: sp_change_users_login is deprecated.


MariaDB Uuid as Primary Key Performance Problem / Tweaks

UUIDs are not nice when it comes to performance.  In particular, there is no relationship between the 'next' row you fetch via a UUID and all the other rows that are currently cached.
Docker possibly has a "small" value for innodb_buffer_pool_size, thereby leading to I/O when random accesses into the table happen.
UUID v1 can have its bits shuffled to make them "chronological", would remove most of the "randomness" of accesses.  But you are using v4.
Plan A:  Abandon UUIDs.
Plan B:  Use UUID v1 and shuffle the bits
Plan C:  Give docker enough room to have a big buffer_pool so that most of the indexes can be kept in RAM.
More:  http://mysql.rjweb.org/doc.php/uuid


Adding the resolution from the comments: the stored procedure was created in a case-sensitive user database, and it was trying to copy things into a case-sensitive TempDB.

After removing sp_Blitz from the case-sensitive user database, and installing it in master (which would then reflect the system database collations), it worked fine.


SQL Server CPU usage is overloaded sometimes

Is anyone able to shed some light on what might be going on, or guide me through some things to check.

Let's start with the obvious stuff. Your server, for a production SQL box, isn't that highly powered. For example, this is what I do development work on, and I'm just a consultant goofing around most of the time.

That being said, more hardware may not solve all your problems. I'm not familiar with the platform you mentioned, but typically when you start with a base product, and you start referring to it as "highly customized", that means the code and indexes that used to work with the base product probably don't work so well anymore.

What can you start with?

The company I work for writes free scripts that can help you get to the root of these things. Whether it's something you can fix with hardware, or with vendor support is another matter.

A lot of vendors don't like you making changes to their products. But hey, at least you can start a conversation about making changes.

Run sp_Blitz

EXEC sp_Blitz @CheckUserDatabaseObjects = 1, @CheckServerInfo = 1;

This will give you a general idea about what's going on health-wise with your server. Pay attention to a couple settings here: MAXDOP and Cost Threshold for Parallelism, and see my answer here for why they can make a difference in your situation.

Run sp_BlitzFirst

EXEC sp_BlitzFirst @SinceStartup = 1;

This will tell you what your server has been up to since it started up. Look at the wait stats pane to see where your bottlenecks are.

Run sp_BlitzCache

EXEC sp_BlitzCache @SortOrder = 'cpu';

Since you're concerned about your plan cache, look at it by CPU first. You may find other sort orders helpful down the line, but start here.

We'll warn you about all sorts of stuff in your query plans, and give you as much historical information as possible.

Run sp_BlitzIndex

EXEC sp_BlitzIndex @DatabaseName = N'YourDatabaseName', @Mode = 4

The main thing you'll want to look at here is high value missing indexes, which should be right up at the top.

When you catch cpu spiking, run sp_BlitzWho

EXEC sp_BlitzWho

This'll let you know what queries are running when CPU is high. They may be different from what's in the plan cache.

Hope this helps!


UPDATE statement with and without SELECT keyword

Personally, I would re-write this in the following way, to clearly separate the source and the target, instead of using this subquery assignment technique. I am making assumptions on where some of these columns are coming from, of course, because you didn't use aliases, so the references were ambiguous.

DECLARE @sd DATETIME;
SET @sd = DATEADD(DAY, DATEDIFF(DAY, 0, @StartDate_1), 0);

;WITH hrs(DrvrID, ph) AS
(
  SELECT EmployeeNo, SUM(PyblHrs)
    FROM #tmpDriverTime
    GROUP BY EmployeeNo
)
UPDATE e
  -- "WorkDay2" represents a sum of payable hours? Quaint.
  SET WorkDay2 = COALESCE(hrs.ph, 0)
  FROM hrs
  INNER JOIN dbo.spr_DriverTimeEntry AS e -- schema prefix always, please
  ON hrs.DrvrID = e.DrvrID
  -- please don't convert both sides to a string
  -- use a range, then you can still use an index:
  WHERE e.dtwrkd >= @sd
    AND e.dtwrkd <  DATEADD(DAY, 1, @sd);


I would go with option 3, and have a many to many mapping table to associate Books and Authors.  What if you have a book with more than one author? I've included an example for you below.

Your second approach could work for you.  But the answer as to what's best is how will you be querying for the information?  If you are going to query for data inside the Wide Column Store, that's going to be slow through SQL.

CREATE TABLE dbo.Author
    (
    AuthorID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , AuthorName NVARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.Book
    (
    BookID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , BookName NVARCHAR(100) NOT NULL
    , ISBN NVARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.AuthorBook
    (
    AuthorBookID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , AuthorID INT NOT NULL REFERENCES dbo.Author (AuthorID)
    , BookID INT NOT NULL REFERENCES dbo.Book (BookID)
    )


Are there any inherent shortcomings in this schema?

Unfortunately, it's very difficult to just cast one's eye over a schema and say, "Yep that's great" or "Yes, that's going to work".

Schema and system design is an iterative process - you start with a few ideas, implement them and test to see how it's working. Then you rejig the bits that aren't working and see - now the rejigging will (in all likelihood) break some bits that were working previously and that you hadn't changed - so you tweak again, and hopefully you asymptotically approach (nirvana...) a decent working app.

I can only advise you to

visit databaseanswers.org - check out databaseanswers - there's lots of links related to tickets and events, and
check out your search engine of choice for "open source reservation systems" (or similar) and download some systems and check out their schemas.

Sorry not to be able to be of more help, but it's analagous to asking "I'm thinking about writing a book, here are the chapter titles, will it be good?".

Start out your system and if you have more specific questions, come back here and ask them - p.s. welcome to the forum.


List all tables that a specific login has permissions

the script I use will show you all objects (SP, Tables, Functions) for a specified Database name and all the users that have rights on them, but you can narrow the search and extract exactly what you need.

DECLARE @EXEC_SCRIPT    NVARCHAR(4000)
DECLARE @DBNAME         NVARCHAR(256) = 'DB name'

SET @EXEC_SCRIPT = 'USE ['+ @DBNAME + ']
select DB_NAME ()                   as [db_name],
a.class_desc                        as [class_type] ,
user_name(a.grantee_principal_id)           as [user_name] ,
isnull(OBJECT_NAME(a.major_id),'''')            as [object_name],
isnull      (b.name,'''')               as [column_name] ,
a.permission_name                   as [permission_name] ,
a.state_desc                        as [permission_type] ,
c.type_desc                     as [object_type],
CASE d.type
                WHEN ''A'' THEN N''ApplicationRole''
                WHEN ''R'' THEN N''Role''
                ELSE N''User'' END  as [security_type],
CASE WHEN a.major_id  < 0 THEN N''Y''
     ELSE N''N'' END                as [system_object],
d.is_fixed_role                     as [is_fixed_role]

from sys.database_permissions a left join sys.columns b
on a.minor_id = b.column_id and a.major_id = b.object_id
left join sys.objects c on  c.object_id = a.major_id
INNER JOIN sys.database_principals AS d ON d.principal_id = a.grantee_principal_id
where a.class_desc <> ''DATABASE''  and user_name(a.grantee_principal_id) <> ''public''
order by user_name'

EXEC master.dbo.sp_executesql @EXEC_SCRIPT


UPDATE vs INSERT INTO... ON DUPLICATE KEY UPDATE

The fastest operation will be the one that has the most chances of succeeding immediately.  You should find out what happens more often: inserting new rows or updating old ones.   Code accordingly.


How to change aggregate function without duplicating SQL (by using SQL)

Contradiction Detection could kick in to make sure only one of the statements is run, and in my simple test it did as long as there was a statement-level recompile hint, but why risk it?  For example:

USE tempdb
GO

-- CREATE SCHEMA agg
--DROP TABLE agg.DataMin
--DROP TABLE agg.DataMedian
--DROP TABLE agg.DataWeightedAverage
--GO

CREATE TABLE agg.DataMin ( x INT PRIMARY KEY )
CREATE TABLE agg.DataMedian ( x INT PRIMARY KEY )
CREATE TABLE agg.DataWeightedAverage ( x INT PRIMARY KEY )
GO

INSERT INTO agg.DataMin ( x )
SELECT object_id FROM sys.all_objects

INSERT INTO agg.DataMedian ( x )
SELECT object_id FROM sys.all_objects WHERE type = 'P'

INSERT INTO agg.DataWeightedAverage ( x )
SELECT object_id FROM sys.all_objects WHERE type = 'X'
GO

-- Are there some situations when it wouldn't...
DECLARE @AggFunction INT = 1

;WITH AggData AS
(
   SELECT * FROM agg.DataMin WHERE @AggFunction = 1
   UNION ALL
   SELECT * FROM agg.DataMedian WHERE @AggFunction = 2
   UNION ALL
   SELECT * FROM agg.DataWeightedAverage WHERE @AggFunction = 3
)
SELECT *
FROM AggData
OPTION ( RECOMPILE )

My results:

In this simple example, only one table is scanned on the left with the recompile, and 3 tables are scanned on the right, without the recompile.  The recompile hint allows the optimizer to "see" the parameter value and act accordingly.  In a stored procedure where parameter sniffing would be used, a recompile would also be needed to get the same behaviour, either at statement or stored-proc level.

However I cannot say if there are no situations where contradiction detection would not occur; and you can't prove a negative.  To put it another way, I cannot prove contradiction detection would always occur even with a recompile.  There may be some unknown situations where even with a recompile it does not occur; excessive complexity springs to mind.

Also, there is no real advantage to using the CTE in your example, so why not keep it simple?  You could just write some simple procedural SQL with IF...THEN...ELSE which would guarantee only one of your statements would fire, eg

DECLARE @AggFunction INT = 99

IF @AggFunction = 1
   SELECT * FROM agg.DataMin
ELSE IF @AggFunction = 2
   SELECT * FROM agg.DataMedian
ELSE IF @AggFunction = 3
   SELECT * FROM agg.DataWeightedAverage
ELSE
    RAISERROR( 'Unknown value for parameter @AggFunction (%i).', 16, 1, @AggFunction )

Add some parameter checking while you're at it.  Hopefully this meets your requirements of guaranteeing only one statement is compiled when needed, is safe and hopefully simple to implement.

HTH


UPDATE with LIMIT / OFFSET in PL/pgSQL function

It will be much simpler (and faster) to use cursors for that:

begin;
declare foo cursor for
    SELECT
          id
    FROM
          tablea
    WHERE
          mig = true;
fetch forward 2000 from foo;
fetch forward 2000 from foo;
-- etc
rollback;

About declare
About fetch


Guidance on simplifying the following diagram

No, you shouldn't merge the 3 tables.

For a given flight, aircraft may change
For a given aircraft, flight may change

However, I'd suggest that your model isn't complex enough

FlightSeats depends on Aircraft model (seat layout etc)
Aircraft departs Airport too...
.. but Flights leave and departs Airports. Or is it FlightAirport?
Flight is arguably 2 entities: the base flight (number, schedule etc) and the actual FlightInstance (date etc)

Have you written down all your facts first? Say in tabular format like this MSDN example (actually for ORM which I use because ERDs don't capture all the information you need)

Edit, more...

As per @ypercube and @Simon Righart comments below
From @Simon Righart: A given aircraft might have three different classes of seats (economy, business + first) with different numbers of each each flight number might be flown by multiple aircraft


Can I share a MySQL database among multiple users exclusively?

Set up a DATABASE of the shared data.  GRANT users only SELECT permissions.  (And maybe a few other non-destructive privs.)

Set up another DATABASE for each user.  GRANT ALL PRIVILEGES TO user1_db.* ON 'user1'@...;

Then set up a way to initialize their db with a copy of the readonly copy.  Perhaps it could be a Stored Proc.  Or maybe a shell script.

Another approach is to set up a Docker instances, one per user.  Again, there needs to be a script to initialize their data.

OR...

If each dev does not need to mess with the existing data, consider having a single DATABASE with permissions GRANTed at the TABLE level.


This is most likely occurring as the old user ( the one with sysdmin rights) had access to the SQL Server Symmetric Keys which are used to encrypt/decrypt keys which the new user does not.

The Grant Symmetric key article provides details and the links at the end provide some good reading.


Inserting a new lookup value for the SSIS Lookup Transformation

Option 1

It's been awhile since I have done it but basically this is what I did.

After the lookup, do a conditional split.
Split the NULL values off on their own path.
Use an OLE DB Command to insert your rows.  Be careful here because you don't want to insert the same row over and over again.
Re-lookup your value (or get it back from the OLE DB Command if you can).
Use a Merge to merge the two data flows back together again.

Option 2

Do it with 2 data flows. In the first one, find all of your data that doesn't match and do the insert. In your second one, do the lookup.

The latter option may be simpler for your purposes.


How to allow NULL in foreign keys of a compound primary key

I believe that the primary key constraint enforces the not null constraint. You can look at the following Fiddle
I'm not sure what it is that you want to achieve. Should the following be valid:
INSERT INTO price (product_id, variant_id, ...
VALUES ('123-123', null, ...)
     , ('123-123', null, ...)

? If not (i.e. only one allowed null varant per product), you can use a generated column and add the constraint there:
CREATE TABLE IF NOT EXISTS price3 (
  product_id             UUID NOT NULL, -- this has to be always to a valid product
  variant_id             UUID,          -- this could be NULL
  variant_ext_id         UUID NOT NULL GENERATED ALWAYS AS (COALESCE(variant_id, product_id)) STORED,
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey3 PRIMARY KEY (product_id, variant_ext_id),
  CONSTRAINT inventory_price_amount_gt_03 CHECK (amount > '0'::money)
);

You would, of course, need to apply that rule in any dependent tables. From a normalisation point of view, you may want to treat products and variant of products differently.
Other things that come to mind is to use a default value such as:
CREATE TABLE IF NOT EXISTS price (
  product_id             UUID NOT NULL, -- this has to be always to a valid product
  variant_id             UUID DEFAULT '00000000-0000-0000-0000-000000000000' NOT NULL,
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey PRIMARY KEY (product_id, variant_id),
  CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money)
);

Edit: Another option is to change the primary key to a unique constraint, see Fiddle:
CREATE TABLE IF NOT EXISTS product (
  product_id UUID NOT NULL PRIMARY KEY
);

insert into product values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53');

CREATE TABLE IF NOT EXISTS price (
  product_id             UUID NOT NULL, -- this has to be always to a valid product
  variant_id             UUID,          -- this could be NULL
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey UNIQUE (product_id, variant_id),
  CONSTRAINT inventory_price_inventory_product_fkey FOREIGN KEY (product_id)
    REFERENCES product (product_id),
  CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money)
);

insert into price (product_id, amount)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53','12');

insert into price (product_id, variant_id ,amount)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53', '33a9fb48-5c0c-4bec-a1d9-382a73856e54', '19');

CREATE TABLE example (
  product_id             UUID NOT NULL,
  variant_id             UUID,
  foreign key (product_id) references product (product_id),
  foreign key (product_id, variant_id) references price (product_id, variant_id)
);

-- valid, f.k. evaluates to Null
insert into example (product_id)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53');

-- valid, f.k. evaluates to True
insert into example (product_id, variant_id)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53', '33a9fb48-5c0c-4bec-a1d9-382a73856e54');

-- invalid, f.k. evaluates to False
insert into example (product_id, variant_id)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53', '33a9fb48-5c0c-4bec-a1d9-382a73856e55');


Running two MySQL servers that use the same data directory

Since the documentation tells you exactly what precautions to take, I was expecting this to actually work...or else, what's the point of that guide in the official documentation? If this couldn't be done, why would it tell you how to do it in the first place?
  Most importantly, do you know how to make this work, using MySQL?

You said something that might have a bearing on the first two question

  Both MySQL instances access the same data directory, which is stored on a persistent volume (this volume is provided by Gluster, but that's not really relevant, I'm only saying this for the sake of completeness).

Interestingly, someone has claimed to have pulled this off.

On Sep 24, 2010 (nearly either years ago), Richard Holloway answered a question he himself posted in Server Fault : Can I run mysqld on top of glusterfs?

Quoting Richard Holloway's answer

Disable the query cache (Set query_cache_type=0, query_cache_size=0)
Switch on external locking

Richard cites MySQL 5.1 Doc on external locking
Michael - sqlbot just recommended 5.6 Doc in his comment.

disable delays to writing.

This is a very daring answer ( Godspeed, Spiderman !!! )

Just to throw in my 2 cents

Do not use GlusterFS in async mode !!!
Configure skip-innodb on both mysqld instances

  If not, do you know of any other DBMSs that allow you to obtain this result (that is, running two servers that use the same data directory)?

The answer is Oracle RAC. It is designed to operate multiple Oracle instances sharing only one set of datafiles. Each instance has its own log buffer. Oh yeah, you will need boat loads of money (Oracle RAC is not open source).


What is an easy query to fix up historical columns valid_from and valid_to?

There a lot of differences between the two DBMS.

Oracle supports window fundtions, the MERGE statement and several other features that MySQL doesn't.
They have different datetime functions (but you are lucky here, - interval '1' day  will work in both).
Different UPDATE syntax - apart from very simple, one table update statements.

MySQL supports a JOIN in the UPDATE statement, Oracle doesn't.
Oracle supports updatable "views" (derived tables), MySQL doesn't.
MySQL doesn't allow the updated table to be defined (again) in a subquery in the update WHERE.

Therefore, it will be very difficult - if possible at all - to write an update statement that will work in both dbms for youe complex case.

For Oracle, you can use either a MERGE statement, or the LEAD() function (altough I didn't manage to make it work in your case) or a self-join, or an inline subquery in an updatable "view":

SQLfiddle-1

update
( select id, valid_to,
         ( select min(ee.valid_from)
           from employees ee
           where ee.object_id = e.object_id
             and ee.valid_from > e.valid_from
         ) as next_valid
  from employees e
)
set valid_to = next_valid - interval '1' day ;

or with the more simple:

update employees e
set valid_to =
   ( select min(valid_from)
     from employees u
     where u.object_id = e.object_id
       and u.valid_from > e.valid_from
   ) - interval '1' day ;

None of the above will work in mysql.

For MySQL it will have to be something more convoluted:

update
    employees e
  join
    ( select id,
         ( select min(ee.valid_from)
           from employees ee
           where ee.object_id = ei.object_id
             and ee.valid_from > ei.valid_from
         ) as next_valid
      from employees ei
    ) upd
    on e.id = upd.id
set e.valid_to = upd.next_valid - interval 1 day ;

Another possibility for MySQL is using a variable (will update with an example later).

I think your only option, if you truly want something that works in both environments, is to break the problem into two actions:

1, insert the values into a temporary table and
2, do the actual update using that temp table.


Use IN clause as following

UPDATE Packet SET Packet.MSGState = 'P'
Where (MSGState <> 'R' and  MSGState <> 'D') and
Packet.MSGTO IN
(Select  MSGTO.MSGTO
From MSGTO
Where  MSGTO.Selected = 'Y' and MSGTO.Selected IS NOT NULL )


Can Postgres raise warnings if joins are inconsistant with foreign keys?

No.  It is up to the developer/DBA to write sensible joins, in this regard PostgreSQL is just a slave that does what its master commands.

In any case, defining the boundary between joins that make sense and those that don't is not trivial.  If that column is there because of a denormalization, for example, then it perfectly makes sense in spite of a nonexistent foreign key.  You can always decrease the possibility for accidental misjoins by not naming the primary key column just id but jobstep_id, for example.

Also, when should it emit the warning?  When you hit it with a 1000 line SQL script, containing a hundred statements, out of which 20 contain 'inconsistent' joins?

All in all, this is rather a code review problem than anything else.


Convert SQL Server .ldf + .mdf into MySQL scripts

Nope.

Your best bet is to not do that.

Your next best bet is to restore the backup to a SQL Server installation and use SSIS to migrate your objects.

If you like pain and punishment ( you are going to MySQL from MSSQL, so I'm assuming "yes" ), you can use the generate scripts wizard on the restored DB and try to adjust for all of the syntax / feature differences.

An .mdf ( or .ndf ) is a data file, complete with compression options and the like.  There's pretty much not even a non-trivial way to turn the storage file into scripts, much less for an entirely different engine.


Having issues with SQL with a join issues

It's one of the many limitations of the old syntax for outer joins. On a join condition of the type:

WHERE  a . a_expression  =  b . b_expression (+)

while a_expression can be an arbitrary expression involving more than one columns of table a, b_expression can only be a column of table b. For more details, see the related documentations Joins, where it states:

  The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.

To solve this, you can rewrite with the ANSI join syntax:

FROM  OOP_ACTION_ITEMS A
  LEFT JOIN  EMPLOYEE B
    ON  a.owner
      = b.LAST_NAME||','||b.FIRST_NAME||' '||SUBSTR(b.MIDDLE_NAME,1,1)
  LEFT JOIN  EMPLOYEE C
    ON  a.partner
      = c.LAST_NAME||','||c.FIRST_NAME||' '||SUBSTR(c.MIDDLE_NAME,1,1)
  LEFT JOIN  EMPLOYEE D
    ON  a.CP_Director
      = d.first_name||' '||SUBSTR (d.MIDDLE_NAME, 1, 1)||' '||d.last_name
  LEFT JOIN  EMPLOYEE E
    ON  a.Chief_of_Staff
      = e.first_name||' '||SUBSTR (e.MIDDLE_NAME, 1, 1)||' '||e.last_name ;


How to avoid implicit conversion when using XQuery value() method in TSQL?

You can ignore the implicit conversion warnings, they are irrelevant here.  But parsing XML is not free, and you're doing it the best way possible in TSQL.
If you really need to improve the XML parsing, you'd have to try SQLCLR and the low-level XmlReader, or a pre-compiled XmlSerializer.


Statistics that are neither auto_created nor user_created

Those are statistics created with the creation of an index.

I guess they somehow infer that the statistics haven't been created as statistics but as a side effect of an index.

I've noticed this in my databases before and they just seem to always be index-related. I can't find any documentation proving this though.


Java and SQL error: Unknown column 'newID' in 'where clause'

You surely want to change this:

String sql = "Update colleagues " + "set firstname = first " + "where userid = newID";

You're writing first and newID as if they were the names of columns... when you want to actually add the actual values of your variables (with adequate escaping)

Check SQL Injection Prevention Cheat Sheet: MySQL Escaping

You're probably be better of with a prepared statement (you'll save yourself of the trouble of escaping, plus you'll have some extra documentation about the types of the parameters, plus you'll avoid risks of SQL injection):

if (first != null) {
    String sql = "UPDATE colleagues SET firstname = ? WHERE userid = ?";
    // Assuming there is a global variable for the connection, named con
    PreparedStatement upd = con.prepareStatement(sql);
    upd.setString(1, first); // replace first ? with value for first name
    upd.setInt(2, newID);    // replace second ? with value for userid
    upd.executeUpdate();
}