I wish I could upvote you 100 points just for thinking about this! I have seen this subject overlooked so many times it's untrue - so well done. From what I understand you actually want to scramble the data within the fields themselves, and although I understand what you are trying to achieve it might not be quite necessary to do so - although it should be considered on a case-by-case basis.

Most data protection laws revolve around the ability to correctly associate a piece of data with an individual - for example a date of birth or a phone number. You can meet the requirements of the law by ensuring that when you move your data out of production into UAT it is jumbled up so it is not easily re-mapped to the original person - especially when you jumble forename and surnames.

However, this does not address the issue for instance of let's say contact details. You can meet the requirements of the law by jumbling the data but the phone numbers are still real, the emails still real etc... they are just not assigned to the correct person. For this I recommend if at all possible clearing that data before passing it into UAT, Red Gate do a piece of software called Data Generator that can create random test data for you so that you can repopulate the fields with data that can be tested against.

As for data scrambling: there exists many applications that do this for you and honestly you are correct in not wanting to reinvent the wheel. The one that we use at our company is a product called Data Masker by a company called Net2000. The license is pretty cheap, it works extremely fast and you don't have to worry about having to disable all your constraints before scrambling the database.

You can of course roll your own solution should you not find anything that meets your requirements - if you do decide to do this I would strongly recommend using CLR procedures to do it as it is much more flexible than pure TSQL (not to say that you can't use TSQL see here).

Once you have chosen an application to perform this for you the next thing you need to decide is what is it you actually want/need to scramble? Honestly your best resource for this is your company legal team and or the company auditors. I know that sometimes we may not like working with them but they will be much nicer to you for approaching them and asking them the question rather than trying to do it on your own and get it wrong, there is absolutely nothing wrong with asking for help - especially when it is as important as this.

I hope this helps you and I wish you good luck in your quest... ;-)


As there are two commands you have to specify the one you want, eg

SELECT
    JSON_QUERY( configInfo, 'strict $.interfaces[0].commands' ),
    JSON_QUERY( configInfo, 'strict $.interfaces[1].commands' )
FROM dbo.jsonWorking


Looking at the BULK INSERT documentation, it shows that the syntax should be:

BULK INSERT Table_name
FROM 'FilePath'
WITH
( FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')
;

ie, with = signs between the option name and the value.


First of all, categorizing the backup into the four mentioned categories is not accurate. You can categorize into (hot, warm, cold), and into (physical, logical). In other words, the backup can be cold and physical, or, hot and physical, etc. It cannot be (physical and logical).

Notice please that physical backup is better called "Raw backup", which is a copy of the data files, and other related file, at filesystem level.

mysqldump is logical, i.e. it produces the statements that re-create the DB and/or the tables and/or the data, etc. Therefore it is not "physical"

"Hot Backup" means that the backup can happen without affecting the users, and without taking the application or DB server down, AND, the resulting backup is consistent. There are different strategies and tools to make hot backups. Sometimes, it is conditional to the storage engines you use.

To answer your question: mysqldump is not hot backup tool by default. This is especially true if you are using MyISAM among other engines, and/or tables are being locked during the backup.

But if your tables are pure InnoDB, and you use the --single-transaction option with mysqldump, then, yes, it would be considered hot.

HTH


There shouldn't be any impact adding datafiles to an existing tablespace.

Easiest is to let oracle manage the files, make sure you have DB_CREATE_FILE_DEST set:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '%ORACLE_BASE%\oradata\';

Now the only thing to do if your existing datafiles are full or hitting the max autoextend limit is:

alter tablespace USERS add datafile;

where USERS is the name of the tablespace.


Query to normalize table/combine row text

You can create a function that stings the values together using a cursor within the function, but that's about your only option.  You'll need to do row by row processing to make this happen.


mysqlbinlog: unknown variable ssl_key or ssl_cert or ssl_ca

BOTTOM OF THE BUG REPORT YOU REFERENCED

[21 Jan 16:44] David Moss
Thanks for your feedback. This was fixed in version 5.7.3, and the following text was added to the 5.7.3 release notes: Previous versions of mysqlbinlog did not correctly accept the ssl-ca option in an option file. This fix ensures that this option can be correctly used. In earlier versions a work around is to use the loose-ssl-ca option.
[19 Feb 16:08] David Moss
Posted by developer:
This was covered in the release notes. Closing.
[27 Feb 9:02] Christoph Mitasch
will this also be fixed for the 5.6.X release?

MySQL 5.6 Documentation
The --ssl-crl and --ssl-crlpath variables were first introduced for mysqlbinlog in MySQL 5.6.3. Once MySQL 5.6 went GA, those options were not meant to be used at all. How did I know ? Look at the list of options for mysqlbinlog (Table 4.16). They appear in the list between --socket and --start-datetime=datetime BUT THEY HAVE NO HYPERLINKS !!!
mysqlbinlog
Here is something else to consider: mysqlbinlog is not a client program. It is a utility program. In mysql, client programs require logging into mysqld with a username and password.
If you look at the list of MySQL Client Programs, please note that mysqlbinlog is not there because it is a utility. You can see mysqlbinlog among the list of MySQL Administrative and Utility Programs.
If you are using an option file, mysqlbinlog can never respond to the options under the [client] group header. Why ? According to the Options File Documentation:

The [client] option group is read by all client programs (but not by mysqld). This enables you to specify options that apply to all clients. For example, [client] is the perfect group to use to specify the password that you use to connect to the server. (But make sure that the option file is readable and writable only by yourself, so that other people cannot find out your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs that you use. Programs that do not understand the option quit after displaying an error message if you try to run them.

SUGGESTION
I don't know if you will encounter the bug with my suggestion, but here it goes ...
If you are using an option file, make the header use [mysqlbinlog] instead of [client]. As a utility, I know [mysqlhotcopy] can be used as a group header. Please try out [mysqlbinlog] as a group header and see if it helps.
ADDITIONAL OBSERVATION
Since you are connecting to DB Server running MySQL 5.5, its mysqld will never understand --ssl-crl and --ssl-crlpath variables because they belong to MySQL 5.6.3. I guess this means you were connecting from a MySQL 5.6 machine using the mysqlbinlog for MySQL 5.6. You will not need to specify any SSL connection options if you are remote connecting for binlgogs


Check on the TCP/IP listening address in SQL Server Manager, both servers have to be listening on the same network (probably both server will be listening on both networks)
When connecting each server to another, you will have to specify the IP address and that should do it.


Does MVC architecture design pattern effect MySQL database performance?

We use cake for some internal applications.  And yes it does at times, pardon my french, suck.  This isn't anything to do with mysql per se.  Once you start defining more than basic models and go down the "has many"/"has many belongs to many" relationships, the underlying queries it generates can be less than sub par.

Do you have table with multi column primary keys? Forget about it.

Frankly there are just times you need to break the molding your framework is supposed to shield you from and write your sql more directly :-\

That being said, as with anything else interacting with your DB you'll want to ensure you have proper indexes and appropriate buffer pool/key buffer.


SqlPackage resolving table that doesn't exist

I found the answer in this post https://www.sqlservercentral.com/forums/topic/create-or-alter-procedure-not-detecting-invalid-object-names-1 Basically, stored procedures use 'late binding' which allows for non-existent entities to be allowed during compile time, and resolved at run time.  This link also provides a helpful SQL Script to detect those issues.
I have additionally created a post-deployment script that can be used in SSDT to verify after a deployment that a given database has no unresolved references https://gist.github.com/DarylSmith/ff69ee75dc1bb61af44dc9b9d9e00d7a


An INSERT statement triggers the SQL Server Audit to write it down in the logs although INSERT is not listed as an Audit Action

The optimal approach would be to disable the INSERT audit for that table, but since you seem to have a peculiar situation, here's one way of dealing with it:
Once you made sure the INSERT audit is not enabled by connecting to the audited database and running this query:
-- List enabled database specifications
SELECT  a.audit_id,
        a.name as audit_name,
        s.name as database_specification_name,
        d.audit_action_name,
        s.is_state_enabled,
        d.is_group,
        s.create_date,
        s.modify_date,
        d.audited_result
FROM sys.server_audits AS a
JOIN sys.database_audit_specifications AS s
ON a.audit_guid = s.audit_guid
JOIN sys.database_audit_specification_details AS d
ON s.database_specification_id = d.database_specification_id
WHERE s.is_state_enabled = 1

You can filter that audit to avoid the inserts from being registered. The Create a Server Audit and Server Audit Specification doc says:

Optionally, on the Filter page, enter a predicate, or WHERE clause,
to the server audit to specify additional options not available from
the General page. Enclose the predicate in parentheses; for example:
(object_name = 'EmployeesTable').

The Filter page can be found by right click the audit > properties. If you prefer, the T-SQL to alter the audit not to register the inserts would be something like this:
USE [master]
GO

ALTER SERVER AUDIT [Your_Audit_Name]
WHERE (NOT [statement] LIKE 'INSERT%')
GO

Some relevant content:
Filter SQL Server Audit on action_id / class_type predicate
sys.fn_get_audit_file (Transact-SQL)
How to analyze and read SQL Server Audit information


mysqldump drop create use case sensitivity

Set lower_case_table_names=1 on the Linux server before importing, this will convert all databases to lower case and new queries will use lower case, too. You can see it here.

Your problem arises from the fact that the default value on Mac is 2 (names are not case sensitive when queried, but they are when stored) but 0 on Unix/Linux (names are always case sensitive). This is because entities like tables and databases names depend on ON files and directories, which have different properties on different systems.

To avoid further problems I would recommend you following a standard (usually, only using lower case identifiers for tables, databases and aliases -column names are always case insensitive) and stick to it.


SQL Server: multiple failover cluster instances with disjoint storage

I've found the answer to this problem. It is possible, but you either need Windows 2008R2 SP1 or higher, or a hotfix. The full details are at:

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups

To quote a line from this article: This arrangement of storage, where a cluster disk is shared between a subset of nodes within a WSFC, is referred to as asymmetric storage


This is what you're looking for, if I've read your question correctly:

SQL> select segment_type, sum(bytes)/1024/1024 as size_in_mb, sum(blocks) as size_in_blocks
  2  from dba_extents
  3  group by segment_type;

SEGMENT_TYPE       SIZE_IN_MB SIZE_IN_BLOCKS
------------------ ---------- --------------
LOBINDEX              43.4375           5560
INDEX PARTITION         61.75           7904
TABLE SUBPARTITION          2            256
TABLE PARTITION       70.6875           9048
NESTED TABLE           1.8125            232
ROLLBACK                .4375             56
LOB PARTITION         21.1875           2712
LOBSEGMENT              331.5          42432
INDEX                338.8125          43368
TABLE                   713.5          91328
CLUSTER                 60.25           7712
TYPE2 UNDO            14.3125           1832

12 rows selected.

SQL>


Is searching faster if indexed values are the same?

Indexes perform better when they have unique values like customer ID, acc number etc. The more unique your indexed field is the less number of records gets picked improving the performance. You can build index on the date field, but would that be unique enough to filter the record you need?


Select all values in same group using value in group

I believe this is what you are after if I have understood your requirements:

DECLARE @Number NVARCHAR(30);
SET @Number = '3H603020';

SELECT
    ms.SubstitutionGroupID,
    subs.Number,
    subs.DescriptionEN
FROM
    MaterialSubstitution AS ms
    INNER JOIN Material  AS m ON m.MaterialID = ms.MaterialID
    INNER JOIN Material  AS subs ON subs.MaterialID = ms.MaterialID
WHERE
    m.Number = @Number
ORDER BY
    ms.SubstitutionGroupID, subs.Number

You need to join back to Material again to find the "substitutions" (hence the alias subs) for a given Material.Number


select Data between two date range mysql return wrong values


Select all rows where a column value occurs more than once

Your query seems correct (although it may not be the most efficient).

There are many ways to write this type of query. For example, you could use EXISTS to avoid counting in the correlated subquery:

select * from table_name t1
where exists
      (select 1 from table_name t2
       where t1.account_id = t2.account_id and t1.id <> t2.id)
 ;

Another method is to use a subquery or CTE and window aggregate:

select id, account_id, plan_id, active
from
  ( select *,
           count(1) over (partition by account_id) as occurs
    from table_name
  ) AS t
where occurs > 1 ;

Or use a subquery to find the accounts that have more than one occurrences and then join to the table:

select t.*
from
  ( select account_id
    from table_name
    group by account_id
    -- having count(1) > 1
    having min(id) <> max(id)      -- variation with same result
  ) as c
  join table_name as t
  on t.account_id = c.account_id ;


I can't see why it would be useful to perform the UNION you describe (and, assuming flight.id and airport.id are the respect primary keys of those tables, there's no reason not to do a UNION ALL, avoiding checking for duplicates). To be specific, it seems like you'd be dumping the data from the two separate tables into one (in effect) separating them back out into flight and airport in the self-join.

To shift the sub-query into a JOIN, we'd also need a way to select the highest ranked airport returned. We'd wind up with something like this:

  SELECT id
        ,airport_id
  FROM   (
          SELECT flight.id
                ,airport.id as airport_id
                ,@RowNum := CASE WHEN @PrevFlightID = flight.id AND @PrevAirportId = airport.id
                              THEN @RowNum + 1
                              ELSE 1
                            END as rank
                ,@PrevFlightID := flight.id
                ,PrevAirportID := airport.id
          FROM   flight
                   INNER JOIN airport ON SQRT(
                                                POW(69.1 * (airport.latitude - flight.lat), 2)
                                              + POW(69.1 * (flight.lon - airport.longitude) * COS(airport.latitude / 57.3), 2)
                                             ) < 10
                   CROSS JOIN (SELECT @RowNum := 0
                                     ,@PrevFlightID := 0
                                     ,@PrevAirportID := 0
                              ) v
          WHERE  flight.STATUS = 1
          ORDER BY
                 ABS(
                      (airport.magnetic -180)
                     -ABS(atan2(TAN(airport.longitude - flight.lon), TAN(airport.latitude -flight.lat)) * (180 / (22 / 7)))
                    )
         ) ranked_data
  WHERE rank = 1
  ;

which, of course, still has a subquery, to filter out the rows we don't want. If I were you, I'd run some tests with both options, and see if one performs notably better than the other.  If not, I suspect I'd use what you already have; in my opinion, it's a little easier to follow.

(Note: code untested)

FYI: given that you have to plug the values from the tables into a somewhat complex equation in order to match them up, it's unlikely that adding indexes or anything like that would help in the matching process. If either table is relatively wide (has many more columns than the ones needed in the query), you might get better performance by creating covering indexes (indexes that have the columns this query needs; the engine can use that instead of the table, and probably will if the index has notably more rows per page than the table itself).

If this information is required frequently, but the flight and airport tables are not updated constantly during the day, then you might save the results of the query to a table. This would work best if the flight table is updated nightly (I assume the airport table is basically static), for example. Note that if the flight table is updated off the normal schedule, the saved results would need to be recomputed.


SQL Server 2014 Management Studio Backup error

Change your Backup command to something like this

BACKUP DATABASE MyDB
TO DISK = N'D:\MyDB.bak'
WITH INIT, NOUNLOAD,
     NAME = N'MyDB backup', STATS = 10, FORMAT

Notice the FORMAT at the end.

This should do it. You're changing NOFORMAT to FORMAT and remove NOSKIP


How to delete all rows in a Greenplum row oriented table

Since Greenplum uses the Postgres database engine, reasoning applicable to a Postgres database seems appropriate. I don't see any reason not to use TRUNCATE when deleting all rows in a table with no children or foreign keys (which is your case), unless you have the ON DELETE triggers that you want to fire.


Maintain Up-To-Date Copy of Very Large Table if Clustering/Partitioning/Moving Operation Needs to be Killed/Rollback

I've done this a few times. My servers are only 2008R2, however. I've moved tables like this and 'partitioned' data both because data files were reaching the max size (16tb) and identity columns were reaching the integer maximum.

Index rebuilds on big tables can take some time to roll back. Reorganization does not, however it is much slower (single threaded). You can kill it immediately and it will just stop where it is, so you could maybe do it over the course of several nights if need be. I had to do this with some quite unwieldy full text catalogs.

Rebuilding clustered indexes to new file groups is not an online operation if you have a (MAX) data type in your table -- see comments below. That was something that surprised me when I was working on my process.
Also, be careful with sorting in tempdb. I once attempted a full statistics scan on a 3tb table and my 500gb tempdb LUN ran out of space overnight.

Let me know if you have any questions.

Thanks


Answer received from Redgate support:

  Following are answers to your questions:

  When you load a database in SQL Search, it indexes and stores the
  info on cache for giving search results. To not index specific
  servers, just do not select 'All database' nor pick the database you
  do not want to index in the database filter.
  Whenever you have mode modifications or want the index to be
  refreshed, manually refresh the index from the menu in SQL Search.

  Hope that helps.

  Thanks,   Anuradha Deshpande


How to update two records with two different values with two 'where' conditions at once?

UPDATE account
    SET balance = balance +  CASE WHEN accountID = 1 THEN amount
                                               WHEN accountID = 2 THEN amount * -1
                                               ELSE 0
                                               END
WHERE accountID IN (1,2)

The * is the multiply operator, that means amount * -1 = - amount - dat-nguyen


Your design is sound.

I have a concern about using the image filename as the key.  Although academically acceptable there could be performance implications.  A long string value like that is not going to make for an efficient index i.e. one with lots of IDs per page.

Secondly, there's nothing certain but change.  At some point you will have to re-locate these files onto a different directory/ server/ domain.  At that point you will be updating the primary key of a table which, while do-able, is rarely pretty.

I understand the example you've shown only has four tables.  I would anticipate you will have more before your system goes live and then these concerns will become more significant.  I'd use a surrogate key instead.


Backup and restore database - occured system error 5

Sounds like a permissions error is being masked. You have to remember that the user that needs access to the folder where the backup resides (and where you restore the backup, if using WITH MOVE explicitly) is the SQL Server service account, not regular users.

If the folder listed in the error message does not represent the same instance where you're trying to restore, it's unlikely the service has access to the folder. Try moving the .bak file to the current instance's data or log folder, where SQL Server will certainly have adequate permissions.


What will be the approximate downtime for indexing a column in psql

You can create indexes concurrently to prevent locks.
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

Docs


High rise in SQL Server CPU after adding a DB

Read this article from Paul Randal on wait stats. You find what sql server is waiting on using the sys.dm_os_wait_stats DMV(Data Management View). It sounds like it MIGHT be a query plan change between versions. But there are a lot of things it could be. Could be storage too. Wait stats should help. Check to see if the MAXDOP setting was different between the versions if possible, you stated the original server died, but not sure if you have the server config saved in a repository for DR purposes. If it's a highly transactional system, parallelism can slow you down. Make sure you know what you are doing before making any changes to MAXDOP.
Also check this article from this thread on SO , it will help you find the worst performing queries. And for more information, check Brent Ozar's Blitz scipt
Hope this helps, Good luck.
Chris


Drop primary key without dropping an index

I don't think there is a supported way to do this. Consider the comments so far.

If you are concerned about effects on concurrent transactions, there is a code example in the manual:

  To recreate a primary key constraint, without blocking updates while
  the index is rebuilt:

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
    ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

For your case, you could create the same index the PK uses a second time and  the index used by your new PK. Then drop the PK like in the example, add a different (unlike the example) PK based on the new index and a new UNIQUE constraint based on the duplicated former PK index. All in a single statement. That would only lock the table for a millisecond.
Three separate commands (not in one transaction):

CREATE UNIQUE INDEX CONCURRENTLY dupe_of_old_pk_idx ON tbl (old_pk_id);
CREATE UNIQUE INDEX CONCURRENTLY new_pk_idx ON tbl (new_pk_id1, new_pk_id2);

ALTER TABLE tbl
   DROP CONSTRAINT tbl_pkey
 , ADD  CONSTRAINT tbl_uni  UNIQUE USING INDEX dupe_of_old_pk_idx
 , ADD  CONSTRAINT tbl_pkey PRIMARY KEY USING INDEX new_pk_temp_idx;


SELECT t1.*
FROM the_table t1
WHERE t1.trading_id = (SELECT max(t2.trading_id)
                    FROM the_table t2
                    WHERE t2.subsection = t1.subsection)


Postgres LEFT JOIN with recurring dates and users

You have to do with users the same thing you're doing with days:

WITH
  days AS (
    SELECT generate_series(current_date-7, current_date, '1d')::date AS day
    ),
  eves AS (
    SELECT user_id, created_at::date AS full_day, COUNT(*) as evs
    FROM logged_events
    WHERE logged_events.created_at >= current_date-6
    GROUP BY user_id, full_day
    ),
  users AS (
    SELECT DISTINCT user_id FROM eves
    )
SELECT eves.full_day, days.day, users.user_id, eves.evs
FROM days
CROSS JOIN users
LEFT JOIN eves ON (eves.full_day = days.day AND eves.user_id = users.user_id)
GROUP BY users.user_id, days.day;


Full Text Search

You can use FTS5

CREATE VIRTUAL TABLE customer USING tblCustomers(col1, col2, col3);

Then you just

SELECT * FROM tblCustomers WHERE email MATCH 'keyword AND keyword2';


This took some doing, but I think I got it.

Here is your solution

SET @given_locale_id = 3;
SELECT terms_value,MAX(rosetta_value) rosetta_value
FROM
(
    SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    FROM terms T LEFT JOIN rosetta R
    ON T.terms_id=R.terms_id
    UNION
    SELECT T.value,'',@given_locale_id FROM terms T
) A
WHERE
    locale_id = @given_locale_id
GROUP By terms_value;

I loaded your sample data with this:

DROP DATABASE IF EXISTS timhaserback;
CREATE DATABASE timhaserback;
use timhaserback
CREATE TABLE locale
(
    locale_id INT NOT NULL AUTO_INCREMENT,
    value VARCHAR(128),
    PRIMARY KEY (locale_id)
);
INSERT INTO locale (`value`) VALUES
('English'),('Deutch'),('French');
CREATE TABLE terms
(
    terms_id INT NOT NULL AUTO_INCREMENT,
    value VARCHAR(128),
    PRIMARY KEY (terms_id)
);
INSERT INTO terms (`value`) VALUES
('lname'),('lpass'),('welcome_blurb');
CREATE TABLE rosetta
(
    rosetta_id INT NOT NULL AUTO_INCREMENT,
    locale_id INT NOT NULL,
    terms_id INT NOT NULL,
    value VARCHAR(128),
    PRIMARY KEY (rosetta_id)
);
INSERT INTO rosetta (locale_id,terms_id,value) VALUES
(1,1,'Username:'),
(2,1,'Benutzername:'),
(1,2,'Password:'),
(2,2,'Passwort:'),
(1,3,'Welcome to appland! Please log in below.'),
(2,3,'Welcome to appland Bitte melden Sie sich unten.');
SELECT * FROM locale;
SELECT * FROM terms;
SELECT * FROM rosetta;

Here is the sample data actually loaded:

mysql> DROP DATABASE IF EXISTS timhaserback;
Query OK, 3 rows affected (0.14 sec)

mysql> CREATE DATABASE timhaserback;
Query OK, 1 row affected (0.00 sec)

mysql> use timhaserback
Database changed
mysql> CREATE TABLE locale
    -> (
    ->     locale_id INT NOT NULL AUTO_INCREMENT,
    ->     value VARCHAR(128),
    ->     PRIMARY KEY (locale_id)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO locale (`value`) VALUES
    -> ('English'),('Deutch'),('French');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE terms
    -> (
    ->     terms_id INT NOT NULL AUTO_INCREMENT,
    ->     value VARCHAR(128),
    ->     PRIMARY KEY (terms_id)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO terms (`value`) VALUES
    -> ('lname'),('lpass'),('welcome_blurb');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE rosetta
    -> (
    ->     rosetta_id INT NOT NULL AUTO_INCREMENT,
    ->     locale_id INT NOT NULL,
    ->     terms_id INT NOT NULL,
    ->     value VARCHAR(128),
    ->     PRIMARY KEY (rosetta_id)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO rosetta (locale_id,terms_id,value) VALUES
    -> (1,1,'Username:'),
    -> (2,1,'Benutzername:'),
    -> (1,2,'Password:'),
    -> (2,2,'Passwort:'),
    -> (1,3,'Welcome to appland! Please log in below.'),
    -> (2,3,'Welcome to appland Bitte melden Sie sich unten.');
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM locale;
+-----------+---------+
| locale_id | value   |
+-----------+---------+
|         1 | English |
|         2 | Deutch  |
|         3 | French  |
+-----------+---------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM terms;
+----------+---------------+
| terms_id | value         |
+----------+---------------+
|        1 | lname         |
|        2 | lpass         |
|        3 | welcome_blurb |
+----------+---------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM rosetta;
+------------+-----------+----------+-------------------------------------------------+
| rosetta_id | locale_id | terms_id | value                                           |
+------------+-----------+----------+-------------------------------------------------+
|          1 |         1 |        1 | Username:                                       |
|          2 |         2 |        1 | Benutzername:                                   |
|          3 |         1 |        2 | Password:                                       |
|          4 |         2 |        2 | Passwort:                                       |
|          5 |         1 |        3 | Welcome to appland! Please log in below.        |
|          6 |         2 |        3 | Welcome to appland Bitte melden Sie sich unten. |
+------------+-----------+----------+-------------------------------------------------+
6 rows in set (0.00 sec)

mysql>

Let's try it against locale_id 1

mysql> SET @given_locale_id = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT terms_value,MAX(rosetta_value) rosetta_value
    -> FROM
    -> (
    ->     SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    ->     FROM terms T LEFT JOIN rosetta R
    ->     ON T.terms_id=R.terms_id
    ->     UNION
    ->     SELECT T.value,'',@given_locale_id FROM terms T
    -> ) A
    -> WHERE
    ->     locale_id = @given_locale_id
    -> GROUP By terms_value;
+---------------+------------------------------------------+
| terms_value   | rosetta_value                            |
+---------------+------------------------------------------+
| lname         | Username:                                |
| lpass         | Password:                                |
| welcome_blurb | Welcome to appland! Please log in below. |
+---------------+------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Let's try it against locale_id 2

mysql> SET @given_locale_id = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT terms_value,MAX(rosetta_value) rosetta_value
    -> FROM
    -> (
    ->     SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    ->     FROM terms T LEFT JOIN rosetta R
    ->     ON T.terms_id=R.terms_id
    ->     UNION
    ->     SELECT T.value,'',@given_locale_id FROM terms T
    -> ) A
    -> WHERE
    ->     locale_id = @given_locale_id
    -> GROUP By terms_value;
+---------------+-------------------------------------------------+
| terms_value   | rosetta_value                                   |
+---------------+-------------------------------------------------+
| lname         | Benutzername:                                   |
| lpass         | Passwort:                                       |
| welcome_blurb | Welcome to appland Bitte melden Sie sich unten. |
+---------------+-------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Let's try it against locale_id 3

mysql> SET @given_locale_id = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT terms_value,MAX(rosetta_value) rosetta_value
    -> FROM
    -> (
    ->     SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    ->     FROM terms T LEFT JOIN rosetta R
    ->     ON T.terms_id=R.terms_id
    ->     UNION
    ->     SELECT T.value,'',@given_locale_id FROM terms T
    -> ) A
    -> WHERE
    ->     locale_id = @given_locale_id
    -> GROUP By terms_value;
+---------------+---------------+
| terms_value   | rosetta_value |
+---------------+---------------+
| lname         |               |
| lpass         |               |
| welcome_blurb |               |
+---------------+---------------+
3 rows in set (0.00 sec)

mysql>

I tried it in your SQL Fiddle and all three locale_ids work as advertised.

Give it a Try !!!


Changing SQL Server installation - Will it bring the server down?

Adding SSIS to your server should not cause the running SQL Server to be affected, stopped, restarted, etc. I have never seen this happen with adding SSIS to a server.

However, if you want to make absolutely sure, you could test by installing SQL Server on a test server.  Then come back and install SSIS and see what happens.


Simon,

it would be worth looking at the following

are there any invalid objects in the database; if so recompile
have you got this situation if so packaged procedures are the way to go.
have you got user managed locking going on, if so maybe reconsider?


You are using FederatedX engine and since you are not specifying column definitions explicitly, it is trying to perform assisted discovery to retrieve the structure of the table. It cannot do it with the underlying "table" being a view, so you are getting the error.

Instead, try to specify the columns:

-- remote
CREATE VIEW `DB_1`.`testView` AS SELECT "test_line"

-- local
CREATE TABLE `DB_1`.`testView` (test_line varchar(16))
ENGINE=FEDERATED CONNECTION='remote_server';


How should I store multiple relationships (many posts to many categories)?

The way that you have described it is the way to do it.  The Primary key for this table is Post_Id, Cat_ID.


Oracle Error: the sga requires more space than was allocated for it

Before you start make a copy of the files you change (the spfile)
Check where your spfile is located, on Linux this is $ORACLE_HOME/dbs/spfile${ORACLE_SID].ora. If this does not exist check the content of $ORACLE_HOME/dbs/init${ORACLE_SID].ora. There the spfile parameter may be set which defines the path of the spfile. For Windows look in the %ORACLE_HOME%\database directory instead of.
Open sqlplus and create a pfile from the spfile create pfile='<auxiliarypfile>' from spfile='<spfilepath>'. Do not overwite an existing pfile but write the file in an arbitrary directory.
Edit the pfile <auxiliarypfile> created in step 3 with a text editor like notepad.
Open sqlplus and overwrite the spfile by a new created spfile create spfile='<spfilepath>' from pfile='<auxiliarypfile>'


Is it a good idea to index timestamp in postgresql?

For the query you give, the optimal general index would be on (my_timestamp, my_row_id, my_item_id), with the order of the last two columns possibly switched.  This will allow an index-only scan to be used which possibly doesn't visit the table at all, since all columns you reference would be available in the index.  How much of a performance improvement this would give you depends on how much of your table meets the timestamp WHERE condition, and how well-vacuumed you keep the table.  You might do even better with a partial index if the timestamp hardcoded into your query never changes, or changes in a very rote manner.

  Like store an extra column text 2018-08-17 from timestamp with timezone 2018-08-17 07:06:43.508778

There is little reason to think that this would be helpful in general, and certainly wouldn't help with the query you show (you would have to rewrite the query before it could use such an index, and then what happens if the timestamp is exactly '2018-09-13 00:00:00' ?)


How to get list of all tables in SQL Server without specifying a db?

There is very little metadata you can get across databases.  OBJECT_ID works, so you can see if an object with a specified name exists in a specified database, but that's about it.  EG:

select object_id('adventureworksdw2014.dbo.DimDate')

You can alternatively use a stored procedure with dynamic SQL, something like:

create or alter procedure table_exists @database sysname, @tablename sysname, @exists bit out
as
begin
   declare @sql nvarchar(max) = concat('select @exists=1 from ',@database,'.sys.tables where name = ''',@tablename,'''')
   exec sp_executesql @sql, N'@exists bit out', @exists  = @exists out
end
go

declare @exists bit
exec table_exists 'AdventureWorksDW2014','DimDate', @exists out

select @exists


SQL Server tempdb on SSD or virtual RAM disc to increase temp-table performance

Before you commit to a particular course of action, you first need to understand what is causing the poor performance.

Look at wait stats while the calculation process is underway.  Check this article by Paul Randal for an excellent starting point on how to gather and analyze them.

Use the system dmv, sys.dm_io_virtual_file_stats to understand the I/O requirements of your process.  I use this code, which shows the activity over a 10 minute period:

IF (COALESCE(OBJECT_ID('tempdb..#vfs_stats'), 0) = 0)
CREATE TABLE #vfs_stats
(
    run_num INT NOT NULL
    , database_id INT NOT NULL
    , file_id INT NOT NULL
    , sample_ms BIGINT NOT NULL
    , num_of_reads BIGINT NOT NULL
    , num_of_bytes_read BIGINT NOT NULL
    , io_stall_read_ms BIGINT NOT NULL
    , num_of_writes BIGINT NOT NULL
    , num_of_bytes_written BIGINT NOT NULL
    , io_stall_write_ms BIGINT NOT NULL
    , io_stall BIGINT NOT NULL
    , size_on_disk_bytes BIGINT NOT NULL
);

TRUNCATE TABLE #vfs_stats;

INSERT INTO #vfs_stats (run_num
    , database_id
    , file_id
    , sample_ms
    , num_of_reads
    , num_of_bytes_read
    , io_stall_read_ms
    , num_of_writes
    , num_of_bytes_written
    , io_stall_write_ms
    , io_stall
    , size_on_disk_bytes
)
SELECT 1
    , vfs.database_id
    , vfs.file_id
    , vfs.sample_ms
    , vfs.num_of_reads
    , vfs.num_of_bytes_read
    , vfs.io_stall_read_ms
    , vfs.num_of_writes
    , vfs.num_of_bytes_written
    , vfs.io_stall_write_ms
    , vfs.io_stall
    , vfs.size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs;

WAITFOR DELAY '00:10:00';

INSERT INTO #vfs_stats (run_num
    , database_id
    , file_id
    , sample_ms
    , num_of_reads
    , num_of_bytes_read
    , io_stall_read_ms
    , num_of_writes
    , num_of_bytes_written
    , io_stall_write_ms
    , io_stall
    , size_on_disk_bytes
)
SELECT 2
    , vfs.database_id
    , vfs.file_id
    , vfs.sample_ms
    , vfs.num_of_reads
    , vfs.num_of_bytes_read
    , vfs.io_stall_read_ms
    , vfs.num_of_writes
    , vfs.num_of_bytes_written
    , vfs.io_stall_write_ms
    , vfs.io_stall
    , vfs.size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs;

SELECT
    DatbaseName = d.name
    , FileName = mf.name
    , SampleTime_ms =   s2.sample_ms            - s1.sample_ms
    , Reads =           s2.num_of_reads         - s1.num_of_reads
    , BytesRead =       s2.num_of_bytes_read    - s1.num_of_bytes_read
    , IOStallRead =     s2.io_stall_read_ms     - s1.io_stall_read_ms
    , Writes =          s2.num_of_writes        - s1.num_of_writes
    , BytesWritten =    s2.num_of_bytes_written - s1.num_of_bytes_written
    , IOStallWRite =    s2.io_stall_write_ms    - s1.io_stall_write_ms
    , IOStall =         s2.io_stall             - s1.io_stall
    , GrowthOnDisk =    s2.size_on_disk_bytes   - s1.size_on_disk_bytes
FROM #vfs_stats s1
    INNER JOIN #vfs_stats s2 ON s1.run_num = (s2.run_num - 1)
        AND s1.database_id = s2.database_id
        AND s1.file_id = s2.file_id
    INNER JOIN sys.databases d ON s1.database_id = d.database_id
    INNER JOIN sys.master_files mf ON s1.database_id = mf.database_id
        AND s1.file_id = mf.file_id
ORDER BY d.name
    , mf.name;

You'll want to get an excellent understanding of the performance profile of the process that you can use as a baseline.  Once you do make changes, you can compare the performance profile after the changes to the baseline performance to understand what impact your change had.

Without understanding the performance profile you will be shooting in the dark by throwing hardware at the problem.  However, this can be a good strategy if you know you have underperforming hardware, such as a the data files on old 7200 rpm spinning rust, or old Xeon processors.  As a total off-the-cuff recommendation, you could try putting all the databases (tempdb, and the data database) onto a nice fast SSD, preferably a PCIe SSD, and making sure you have enough processor speed and memory bandwidth.


How to migrate data from SQL database to MySQL including flat files?

According to this you should be able to store files in a blob or text field.  I don't actually work with mysql so I googled "mysql datatypes".


Is there any benefit to having both an ordered and unordered index on the same column in postgres?

I am combining several good comments into a comprehensive answer.

Order on a mutli-column index could have a bigger impact on efficiency than a single column. In my case it is only one column.

Postgres can scan indexes forwards and backwards just as efficiently. The index which does not have an explicit order is ordered ASC by default.

  Specifies ascending sort order (which is the default).

This means that the two indexes are redundant.


Microsoft Master Data Services (MDS) Migration | 2017 to 2019

We ended up getting this to work by backing up the 2016 MDS database and restoring over the MDS database on the 2019 server.  You have to open Master Data Services Configuration Manager after the restore, select the database and choose Upgrade Database (you may get an error about 'clr strict security' which we had to disable).


error in SSIS - [SQL query execution task] Error: The binding name of the results must be zero for the complete result set and the XML results

Click on ResultSet in the Execute SQL Task, click Add. In the 'ResultSetName' column, enter the exact name of the columnname that you are retrieving or simply give it 0. In the 'variablename', select the variable you created to map the data returned.


Do you want to perform the update once or on a regular basis? If the update on million rows is done once, then the best solution is to create a temp column "processed" on #testing2 table of bit (int,tinyint) which will serve as your null filter. The index on bit or int columns works a lot more optimal than on varchar. Also, having 2 type of values on index definition (0 for null 1 for not null) will be very fast.

Keep your indexes and add the second index and you will have the following Plan if you change the filtering options in the query on "processed" column as it is marked in the picture below with the plan:

create nonclustered index ix_test1 on testing2(processed, id)


Aggregate query on multiple values of same field

Since you are using SQL Server you can use the PIVOT function to convert rows into columns:

select cardScheme, Declined, Approved
from yt
pivot
(
  count(RespCode)
  for RespCode in (Declined, Approved)
) piv;

See SQL Fiddle with Demo


Single query for searching for records without knowing what the search column is for

If you want to know which column matched the parameter your best bet is probably to use a union query such as the following.

set @param = '%new%';

select address, city, zip, 'address' as matched_on
from mls_data
where address like @param
union all
select address, city, zip, 'city' as matched_on
from mls_data
where city like @param
union all
select address, city, zip, 'zip' as matched_on
from mls_data
where zip like @param;

This will return multiple rows if more than one column matches (e.g. both the city and the address contain "new") indicating that multiple columns matched.

Example results: http://sqlfiddle.com/#!9/b814a/4

Update

After clarification of the question in the comments, I think this query may be what you're looking for:

declare @param varchar(max) = '%new%'

select mls, cast(address as varchar(max)) as [value]
from mls_data
where address like @param
union all
select mls, cast(city as varchar(max))
from mls_data
where city like @param
union all
select mls, cast(zip as varchar(max))
from mls_data
where zip like @param;

This returns two columns, the row ID and the matching value, regardless of which column the value comes from.

Example: http://sqlfiddle.com/#!3/ea1c9/5


Fastest way to add a field to a large table

As you are on Enterprise Edition and version >= 2012 then the information in Online non-NULL with values column add in SQL Server 2012 should apply.

Alter table yourtable
    add newcolumn bit not null default 0;

Should be metadata only and, unlike your proposed solution, will not have to write anything to all rows.


What is the difference between node types and atomic types in XML

Sounds like you are not asking about XML itself, but about the XQuery/XPath/XSLT data model known as XDM.

In these languages there are two kinds of value: nodes, which basically means anything within an XML document, and atomic values like integers and strings which have no relationship to any XML document. So for example the query count(//employee) takes a set of nodes as input, and produces an atomic value (an integer) as output. An important operation is atomization, which extracts an atomic value from a node, for example the string that is the value of an attribute node.


Unexpected Indexing performance (Everything's equal)

The table definition that you have is leading to some really odd optimizer behavior. I suspect that you're running into the issue documented in this SE post. To avoid that issue I'm going to create the table with just the [a] and the [IndexedPersisted] columns.

Query hints can be useful to figure out why the optimizer didn't pick the plan that you expected. Here you expected the index to be used but SQL Server did not use it. Let's view both query plans side by side:

SELECT *
FROM [dbo].[CompColIndexing]
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

SELECT *
FROM [dbo].[CompColIndexing] WITH (INDEX([IX_DJB_CompPersisted]))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

The query optimizer thinks that the sort after the table scan is cheaper than 800000 RID lookups. Maybe it's wrong, so let's run the queries and compare performance metrics for them.

cpu_time    total_elapsed_time  logical_reads   reads
1204        1395                3087            1485
1516        1570                851798          0

I got those numbers by looking at sys.dm_exec_sessions after running the queries in separate sessions with "discard results after execution" checked so I wouldn't have to wait on the rows to be returned to the client.

Those numbers seem reasonable to me. Just because an index can be used does not mean that it should be used, especially if SQL Server will need to read the entire table by using the index. That's the worst use case for an index that I can think of. Indexes can be very useful when selecting a small percentage of rows from the table or when they are covering indexes.

The index is a covering one if I only select the [IndexedPersisted] column. In that case, SQL server thinks that using the index is cheaper than doing a table scan. Code to compare the two methods:

-- force table scan
SELECT [IndexedPersisted]
FROM [dbo].[CompColIndexing] WITH (INDEX(0))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

SELECT [IndexedPersisted]
FROM [dbo].[CompColIndexing] WITH (INDEX([IX_DJB_CompPersisted]))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

Here are the performance numbers:

cpu_time    total_elapsed_time  logical_reads   reads
1171        1212                2727            1088
343         406                 1798            0

Now that the index is a covering index it is a better access path for the table than a table scan.


Can I give the same name to a table and a stored procedure?

No. This is clearly stated in the MSDN documentation:

  Procedure names must comply with the rules for identifiers and must be
  unique within the schema.

Since you have a table with the same name, in that schema, you cannot create the stored procedure with that name.


Migrate foreign key type from char to binary - ways to deal with the fallout?

Given the next example:

CREATE TABLE A
(
    [ID] VARCHAR(10) NOT NULL,
    CONSTRAINT [PK_A] PRIMARY KEY ([ID])
);

CREATE TABLE B
(
    [ID] INT PRIMARY KEY,
    [A_ID] VARCHAR(10) NOT NULL,
    CONSTRAINT [FK_B] FOREIGN KEY ([A_ID]) REFERENCES A([ID])
);

CREATE TABLE C
(
    [ID] INT PRIMARY KEY,
    [A_ID] VARCHAR(10) NOT NULL,
    CONSTRAINT [FK_C] FOREIGN KEY ([A_ID]) REFERENCES A([ID])
);

INSERT INTO A VALUES ('001'), ('010'), ('100');
INSERT INTO B VALUES (1, '001'), (2, '001'), (3, '010');
INSERT INTO C VALUES (4, '010'), (5, '100'), (6, '100');

0- Backup, backup & backup your database.

1- Due you are trying to convert varchar to varbinary and there isn't an explicit conversion you should add new columns to your tables. NOTE: You cannot make it non nullables.

ALTER TABLE A ADD [ID_VB] VARBINARY(10);
ALTER TABLE B ADD [A_ID_VB] VARBINARY(10);
ALTER TABLE C ADD [A_ID_VB] VARBINARY(10);

2- Copy current values to the new columns:

BEGIN TRANSACTION
  UPDATE A SET [ID_VB] = CAST([ID] AS VARBINARY(10));
  UPDATE B SET [A_ID_VB] = CAST([A_ID] AS VARBINARY(10));
  UPDATE C SET [A_ID_VB] = CAST([A_ID] AS VARBINARY(10));
COMMIT TRANSACTION

3- Drop current constraints:

ALTER TABLE B DROP CONSTRAINT [FK_B];
ALTER TABLE C DROP CONSTRAINT [FK_C];
ALTER TABLE A DROP CONSTRAINT [PK_A];

4- Once you have checked new values are correct, drop actual columns:

ALTER TABLE A DROP COLUMN [ID];
ALTER TABLE B DROP COLUMN [A_ID];
ALTER TABLE C DROP COLUMN [A_ID];

5- Rename new columns with old names:

EXEC sp_rename 'A.ID_VB', 'ID', 'COLUMN';
EXEC sp_rename 'B.A_ID_VB', 'A_ID', 'COLUMN';
EXEC sp_rename 'C.A_ID_VB', 'A_ID', 'COLUMN';

6- Make new columns not nullables:

ALTER TABLE A ALTER COLUMN [ID] VARBINARY(10) NOT NULL;
ALTER TABLE B ALTER COLUMN [A_ID] VARBINARY(10) NOT NULL;
ALTER TABLE C ALTER COLUMN [A_ID] VARBINARY(10) NOT NULL;

7- Add constraints again:

ALTER TABLE A ADD CONSTRAINT [PK_A] PRIMARY KEY ([ID]);
ALTER TABLE B ADD CONSTRAINT [FK_B] FOREIGN KEY ([A_ID]) REFERENCES A([ID]);
ALTER TABLE C ADD CONSTRAINT [FK_C] FOREIGN KEY ([A_ID]) REFERENCES A([ID]);

8- Check final result:

SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM C;

| ID       |
| :------- |
| 0x303031 |
| 0x303130 |
| 0x313030 |

ID | A_ID
-: | :-------
 1 | 0x303031
 2 | 0x303031
 3 | 0x303130

ID | A_ID
-: | :-------
 4 | 0x303130
 5 | 0x313030
 6 | 0x313030

db<>fiddle here


Oracle using index on partitioned table when full scan would have been better

You'd need to do a 10053 trace to see exactly how Oracle is computing its estimated cardinalities.  I would assume, though, that Oracle's cardinality estimate for this particular range is too low leading it to use an index where a partition scan would actually be more efficient.  Assuming that is the case, I would guess that the problem comes from storing date data in a numeric data type.

If Oracle knows that a particular partition has a min(dt_key) of, say, 20100101 and a max(dt_key) of 20140101, it is going to believe that there is a range of 40,000 values.  If there are no histograms and you ask for the range between 20120101 and 20120731, the optimizer will expect that you're going to retrieve 730 of 40,000 possible values or 1.825%.  If you used a date to store your date data, however, Oracle would know that there are only 4*365=1460 values and that you're actually retrieving 212 of them or 14.5%.  Without knowing the actual range, these are just example calculations, of course, but they show the principle that using the wrong data type can lead to the optimizer getting confused.

Assuming this speculation is correct, you can probably resolve the problem by changing dt_key to a date.  You can probably also resolve it by gathering a histogram on dt_key.


The way to tune InnoDB is centered around

InnoDB Buffer Pool : It caches data pages and index pages. The amount of data and index you can cache is not a function of disk space constraints but a function of available memory and diskspace currently used by InnoDB.
InnoDB MetaData : By default, the file ibdata1 normally houses anything and everything InnoDB. That would include data pages, index pages, table metadata, MVCC data.

Here is a formula I have used for the past 5 years to compute the InnoDB Buffer Pool based on diskspace used by InnoDB data and index pages:

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(Power1024<0,0,
IF(Power1024>3,0,Power1024)))+0.49999),SUBSTR(' KMG',IF(Power1024<0,0,
IF(Power1024>3,0,Power1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 2 Power1024) B;

Use (SELECT 0 Power1024) for Bytes
Use (SELECT 1 Power1024) for KB
Use (SELECT 2 Power1024) for MB
Use (SELECT 3 Power1024) for GB
Use (SELECT 4 Power1024) for TB (Email Me if you have TerraBytes of RAM)

Of course, I said a function of available memory and diskspace currently used by InnoDB. From here, just use common sense. The recommended number from the above query SHOULD NOT EXCEED 75% OF INSTALLED RAM !!! That's the simplest rule-of-thumb for sizing the InnoDB Buffer Pool.

You should also set innodb_flush_method to O_DIRECT since it will provided stable synchronous writes of InnoDB. I have also written a post on how to optimize Disk Storage for InnoDB.

With regard to the message Table does not support optimize, doing recreate + analyze instead, the reason why you get that error message is the fact that the storage engine is InnoDB. Mechanically, OPTIMIZE TABLE just copies the table to a temp table and performs ANALYZE TABLE.

In reality, ANALYZE TABLE against InnoDB is completely useless. Even if you ran ANALYZE TABLE on an InnoDB table, the InnoDB storage engine performs dives into the index for cardinality approximations over and over again, thus trashing the statistics you just compiled. In fact, Percona performed some tests on ANALYZE TABLE and came to that same conclusion as well.

Here are other posts I have made over the year about InnoDB Tuning

https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261
https://stackoverflow.com/questions/5248420/at-what-point-does-mysql-innodb-fine-tuning-become-a-requirement/5250306#5250306
https://serverfault.com/questions/155455/how-do-myisam-and-innodb-utilize-hd-space/246111#246111
https://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-cons/231400#231400
https://serverfault.com/questions/253059/mysql-innodb-optimisation/253233#253233
How do you tune MySQL for a heavy InnoDB workload?


Returning rows in PostgreSQL with a table called "user"

user is a pseudo-function keyword

user is an alias for current_user, a built-in pseudo-function. It doesn't have the () of most zero-argument functions because that's how the SQL standards committee declared that it would be for current_user. I don't know why user follows that too, since I don't think the spec requires it.

Functions can be used in FROM

Anyway, it's legal in PostgreSQL to specify a function in from, e.g.

regress=> SELECT * from clock_timestamp();
        clock_timestamp
-------------------------------
 2014-09-03 17:00:59.191019+08
(1 row)

because a function that returns a single value can be used in a set-returning context too.

So when you run:

select * from user;

you're really doing:

select current_user;

"quoting" escapes keywords

The reason it "works" if you write "user" instead is that quoted identifiers are always user-identifiers, not keywords. user is a keyword; "user" is a user-defined identifier that's a legal table name.

Confusing, no?

Usually "tablename" and tablename are the same (quoted-lower-case and unquoted-lower-case), unless tablename is a keyword.

This might make more sense if we pick a keyword that isn't a pseudo-function, so it's not legal syntax. Say:

regress=> CREATE TABLE "where" (id integer);
CREATE TABLE

regress=> CREATE TABLE where (id integer);
ERROR:  syntax error at or near "where"
LINE 1: CREATE TABLE where (id integer);
                     ^

Quoting makes identifiers case-sensitive

Normally, PostgreSQL case-folds all identifiers to lower case. This is required by the SQL spec, though it actually requires upper case. So when you:

CREATE TABLE MyTable (id integer);

PostgreSQL actually creates a table named mytable. However, this case-folding is disabled if you "double quote" identifiers - again, per the SQL standard.

So the reason that:

select * from "User";

fails is that PostgreSQL, per the SQL standard, treats quoted identifiers as case-sensitive. So "User", "USER" and "user" are all different things.

How to avoid these problems?

Don't name your tables after keywords like type names, function names, etc. It's often legal, but it's rarely a good idea.

If you're going to use keyword names, you should always "double quote" them wherever they appear - and in fact make a habit of simply doing that for all your identifiers, all the time, and always using consistent case.

There's a keyword list here.


Using EXCEPTION to ignore duplicates during bulk inserts

There are 3 possible kinds of duplicates:

Duplicates within the rows of the bulk insert. That's your immediate cause for the exception.
Duplicates between inserted rows and existing rows.
Duplicates between inserted rows and concurrently inserted / updated rows from other transactions.

1. and 2. can be fixed easily. But you really need to define exactly how to solve conflicts. Which row is to be picked?

INSERT INTO readings (real_time_device_id, recorded_at, duration)
SELECT DISTINCT ON (real_time_device_id, recorded_at)  -- solves 1.
       i.real_time_device_id, i.recorded_at, i.dur
FROM (
    VALUES
  ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:33 -0700', 10.0),
  ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:43 -0700', 10.0)
  ) i (real_time_device_id, recorded_at, dur)
LEFT   JOIN readings r1 USING (real_time_device_id, recorded_at)
WHERE  r1.real_time_device_id IS NULL                    -- solves 2.

I am picking an arbitrary row from each set of dupes with DISTINCT ON. You may want to define deterministically instead:

Select first row in each GROUP BY group?

3. is more tricky - but hopefully not applicable to your case ...

To insert ~250K rows, it would be much more efficient to COPY the lot to a temp table (or as much as you can easily store in RAM and process without spilling to disk) and proceed from there.

If that's a large part of the table, it might pay to drop indexes you don't need for the duration of the update and create them afterwards ...

Getting error while importing large data through csv
Call UPSERT function multiple times to different inputs
How to update selected rows with values from a CSV file in Postgres?


Is there an advantage to moving to 'real' date partitions on MySQL 5.5?

Using to_days to Partition
The to_days function yields a 6 digit number in the 700,000's (in the MEDUIMINT range).
Using DATE or DATETIME to Partition
DATE takes up 3 bytes. DATETIME takes up 8 bytes.
Conjecture
UNIX_TIMESTAMP of today's date exceeds 1.37 billion. It takes 4 bytes to store that. Range partitioning allows for range values that high. Here is an example of that from the MySQL Documentation:

It is also possible to partition a table by RANGE, based on the value
of a TIMESTAMP column, using the UNIX_TIMESTAMP() function, as shown
in this example:
CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

Partitions seem to be numerically represented as a 4-byte unsigned integer. As mentioned, DATE takes up 3 bytes, but, from what I have observed, partitions are bounded by 4-byte numbers. Given this, expressing a DATE as a RANGE parameter really does not buy you anything except cleaner syntax for defining the range.
Expressing to_days of a DATE yourself or having mysql do it for you boils down to personal choice.


What is the advantage of pluggable database over normal databases?

Not a performance gain, but multi-tenant architecture reduces hardware costs.

http://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CEGJGEJH


How to better perform a query split into subqueries in PHP

Quickly get into the habit of writing your queries with some kind of formatting, proper joins, and with properly qualified column names. It makes it much easier to understand. E.g.:

SELECT t.id
     , t.uid
  FROM tracks t
  JOIN users u
    ON u.idu = t.uid
 WHERE t.time > CURDATE() - INTERVAL 1 YEAR
   AND u.public > 0
 ORDER
    BY RAND()
 LIMIT 10


Difference between Handler_write and Innodb_data_writes

A "write" is distinct from a "disk write".

Writes include writes to tmp tables.

This pattern avoids having to do subtracts (for Handler%, not for Innodb%):

FLUSH STATUS;
perform some query;
SHOW SESSION STATUS LIKE 'Handler%';

Innodb_% values are only GLOBAL; Handler% are SESSION, then added to the GLOBAL tallies.

Handler% is done at an engine-agnostic level; Innodb% is done in the InnoDB engine.  (This should answer you engine questions, but I have not double checked.)

Fraction of read requests that had to hit disk =
  Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
Possibly the numerator needs to be Innodb_pages_read or Innodb_data_reads.

Sorry that I don't have answers to all your questions.


How to uninstall SQL Server Management Studio 2012?

You will select "Microsoft SQL Server 2012 (64-bit)"

Upon loading of the setup wizard, you will select the instance for SQL Server 2012. The next screen you check the shared component you want to remove.


One way to define your tables:

Users: user_id (primary key), name, etc

Group_A: user_id, group_a_id, group_a_name

Group_B: group_a_id, group_b_id, group_b_name

Group_C: group_b_id, group_c_id, group_c_name

Items: group_c_id, item_id, item_name, details


Replacing a member of a replica set with the same host name

I think you can get what you want if you first rs.add() the new secondary, and when it's done you rs.remove() the arbiter.

There will be a brief period of thime with 4 nodes, howerever.


Active-Passive master-master is a good setup but I have seen SUPER (humans)users writing on slave without setting sql_log_bin.
(Though super_read_only in 5.7 will change things around this.)

Anyways, following is possible and works.

ProdActiveMaster<---->ProdPassiveMaster
|
|
|
\/
DRmaster----->DRSlave

So you know now that another master-master setup is not possible as DRMaster cannot replicate from two masters.
I doubt multi-source replication is a solution here as it will create more trouble than help.

You need to make sure to keep your DRSlave, "master-ready"... setting auto_increment% and binlog + logslaveupdates settings.


sp_executesql creates a new execution context which ceases to exist when the executed statements return.  The USE only has effect within the sp_executesql.  Try this

SET @SQL02 = 'USE ['+ convert(nvarchar(50),@dbName) +']; SELECT DB_NAME();'

and you'll get what you're after.


How to prevent the creation of Mysql columns with default values that are not defined

I don't think there is such an option.  You could request such at bugs.mysql.com.

I disagree with making NULL (or, for that matter) NOT NULL mandatory.  NULL and NOT NULL have important uses in schema design and execution.  PRIMARY KEYs must be NOT NULL, and that is implicitly provided.  Due to business logic, some columns should not be left out; some should be allowed to be optional.

NULL has a lot of different semantics, and arbitrarily forcing all columns to be NULL begs the issue of which semantics you want.  Examples:  Optional (product specifications); not yet provided (an end_date); unknown (due to partial data received).


The output of your CTE is inconsistent: john, paul, and ringo have different numbers of band members. You should go through the pairs in both directions (and then use UNION to prevent duplicates and infinite loops):

with bands (member, leader) as (
    select item1, item2 from pairs
    union
    select item2, item1 from pairs
    union
    select ... -- recursion
) ...

Now you have all possible combinations of leaders and band members, but you want only one (random but unique) leader per band. So for all rows with the same band member, choose the one with the smallest leader:

with bands as (...)
select 'group:'||min(leader), member
from bands
group by member
order by min(leader);


Automatically give grants to particular users PostgreSQL

There is alter default privileges, but this does not accept a wild card for the users it will apply to (i.e. the creating roles).  You have to list every possible creating user, which is quite a drag.


What things SQL Server takes backup of when we run a full backup

A Full Backup contains a copy of all the database pages, and all the log records from the LSN at which the first database page was copied through the LSN at which the last database page was copied.

On restore the database pages are copied into the new database files, then the log records are replayed to to bring the database up to the point-in-time at which the backup completed.

If you restore WITH RECOVERY, then any transactions that were open at the time the backup completed are rolled-back and the database is brought online.

If you restore WITH NORECOVERY, then you can restore additional Log Backups to restore the database to a later point-in-time.


The typical design here is to have a Transaction table and an Account table

The Account table has the current balance, as of the last transaction that has "posted".  A modern "online" system will post transactions immediately, but older systems would capture transactions online, but run "batch" to post the transactions later.

PK on the transaction table would normally be (AccountId,TransactionId), which balances the cost of insert and the cost of pulling the transactions for a single account.

I don't think there's a rule controlling whether you also store the running balance on the transaction table.  Normalization does not require you to recalculate the account balance or the running balances.  Balance is a proper attribute of Account, regardless of whether it can be derived from rows in another table.  And you don't necessarily have to perform these calculations as you insert transactions.


Storing revisions in SQL Server (simply much like TFS)

Obviously when you check in a changeset, you know who the customer is (or was) according to what you describe about your process.  Even TFS requires something to be the same, so that it can maintain history.

In any case, in multiple versions of the same object, what is constant?

Is the "Name" constant? (Probably not.) If the Customers table were that simple I do not see what you are tracking other than a name change.  But likely the table is much more complex.

But as long as you know this is the same Customer, you should carry a constant value in the new Customer rows. Something like:

 Table: Customers
 Columns: Id | CustomerNumber| Name

That way "Id" is just a row surrogate key, but CustomerNumber would be assigned once and would be a datum included in the versions of the data you are tracking.

I suppose that the CustomerNumber could be the first Id assigned to this Customer, but I would suggest an independent numbering scheme, perhaps check-digited for a degree of protection from entry errors, should that ever be needed publicly.


UPDATE table SET value which SELECT from other table

So really these tables aren't related in any way at all. Assuming you want each row in table 2 assigned to a row in table 1, here's one arbitrary way to do it:

;WITH t1 AS
(
  SELECT ID, col_1, rn = ROW_NUMBER() OVER (ORDER BY col_1)
    FROM dbo.table_1
    WHERE ID = 11 -- guessing here based on sample data
),
t2 AS
(
  SELECT col_2, rn = ROW_NUMBER() OVER (ORDER BY col_2)
    FROM dbo.table_2
)
UPDATE t1
    SET col_1 = t2.col_2
  FROM t1
  INNER JOIN t2
    ON t1.rn = t2.rn;

If you have other columns you want to order by, you can add them. If you only want three rows and there are more than three rows in table 2, add:

WHERE t2.rn <= 3;


If you have to connect via TCP/IP using IPv4, i.e., the most common situation, you should check what is your IP address. Ince you have it, add this line to pg_hba.conf file on the server. This line should be added after all lines that start with local and before other lines that start with host:

host databasename username ipaddress/32 md5

if youo don't know your ip address, or if it is variable (for a dialup line), then use 0.0.0.0/0 instead of ipaddress/32. This way you will allow connection from any IP address.

But, if your provider is responsible for postgresql, then he should make thee changes. Are you sure you need to configure that file on the server?

If you are only responsible for your client machine, and you want to connect via pgAdminIII, then just use the hostname portnumber username and password provided by your provider. If you cannot connect using these settings, please come back here and write what is the exact error you get from pgAdminIII.


How to determine Windows Server start time using T-SQL

You can use the ms_ticks column from sys.dm_os_sys_info. This is the number of milliseconds since the computer was started.

SELECT DATEADD(SECOND, (ms_ticks/1000)*(-1), GETDATE())
    FROM sys.dm_os_sys_info

Will get you this information.


How to restrict data pump logon to localhost

ADMINISTER DATABASE TRIGGER Privilege Causes Logon Trigger to Skip Errors (Doc ID 265012.1)

ADMINISTER DATABASE TRIGGER Privilege Behavior with Database Logon Trigger
--------------------------------------------------------------------------
Logon triggers can be used to mediate database access: when the restrictive
conditions are not met, an application error with a message is raised that
causes the logon to be denied.

...

However, we need to keep at least one user who can still connect when there is
a problem : a fallback mechanism must exist where an administrative user is
exempt from such errors of a prohibited connection.

Any user granted the ADMINISTER DATABASE TRIGGER system privilege can still
connect : instead of getting the error causing the session to be terminated,
the error is recorded in the alert.log and a trace file in user_dump_dest.

And:

select * from dba_sys_privs where privilege = 'ADMINISTER DATABASE TRIGGER';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
WMSYS                          ADMINISTER DATABASE TRIGGER              NO
DBA                            ADMINISTER DATABASE TRIGGER              YES
SYS                            ADMINISTER DATABASE TRIGGER              NO
IMP_FULL_DATABASE              ADMINISTER DATABASE TRIGGER              NO

So yes, you can try local OS authentication.


How much RAM I should assign to SQL Server 2008 running on W2K3

12GB, leave 4GB for the OS.

This 12GB is for the various caches. The binaries and various out-of-process calls will use the 4GB, along with the OS.


Why is 'LOAD DATA INFILE' faster than normal INSERT statements?

LOAD DATA INFILE and extended INSERTs each have their distinct advantages.

LOAD DATA INFILE is designed for mass loading of table data in a single operation along with bells and whistles to perform tings like:

Skipping Initial Lines
Skipping Specific Columns
Transforming Specific Columns
Loading Specific Columns
Handling Duplicate Key Issues

Less overhead is needed for parsing

On the flip side, if you are only importing 100 rows instead of 1,000,000 rows, extended INSERT is sensible.

Notice that mysqldump was designed around extended INSERTs for the sake of carrying table design along with data as it performs the injection of hundreds or thousands of rows per INSERT. LOAD DATA INFILE always creates a physical dichomoty between schema and data.

From an application point-of-view, LOAD DATA INFILE is also more insensitive to schema change than extended INSERTs.

One can go back and forth on the good, the bad, and ugly of using LOAD DATA INFILE. No matter which technique you use, you must always set the bulk_insert_buffer_size. Why?

According to the MySQL Documentation on bulk_insert_buffer_size:

  MyISAM uses a special tree-like cache to make bulk inserts faster for
  INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA
  INFILE when adding data to nonempty tables. This variable limits the
  size of the cache tree in bytes per thread. Setting it to 0 disables
  this optimization. The default value is 8MB.

For years, I have seen client after client not set this and leave it at 8MB. Then, when they decide to use LOAD DATA INFILE or import mysqldumps, they can sense something wrong. I usually recommend setting this to a moderate 256M. In some cases, 512M.

Once you have a big enough bulk INSERT buffer, using either technique is rendered academic and boils down to personal choice. For applications where you bulk INSERT just 100 rows on demand, stick with extended INSERTs.

In all fairness, saying LOAD DATA INFILE is faster that normal INSERT statements is kind of a loaded statement mainly because configuration is not taken into account. Even if you setup a benchmark between LOAD DATA INFILE and extended INSERTs with a proper bulk_insert_buffer_size, the nanoseconds saved on parsing each row can only yield nominal results at best in favor of LOAD DATA INFILE.

Go ahead and add this to my.cnf

[mysqld]
bulk_inset_buffer_size=256M

You could also set it just for your session before launching extended INSERTs

SET bulk_insert_buffer_size= 1024 * 1024 * 256;

UPDATE 2012-07-19 14:58 EDT

To keep things in perspective, the bulk insert buffer is only useful for loading MyISAM tables, not InnoDB. I wrote a more recent post on bulk loading InnoDB : Mysql load from infile stuck waiting on hard drive


the 'SECONDARY' filegroup is full

Read Database Files and Filegroups.
To move all your data off of a File to other Files in the same Filegroup, use DBCC SHRINKFILE (N'SomeFile', EMPTYFILE);
To move your data off of a Filegroup to some other filegroup rebuild it targeting the new Filegroup, using ALTER TABLE REBUILD for a heap, or by droping the clustered index and recreating it on the new filegroup.


How to determine used/free space within SQL database files?

This one works for me and seems to be consistent on SQL 2000 to SQL Server 2012 CTP3:

SELECT RTRIM(name) AS [Segment Name], groupid AS [Group Id], filename AS [File Name],
   CAST(size/128.0 AS DECIMAL(10,2)) AS [Allocated Size in MB],
   CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used in MB],
   CAST([maxsize]/128.0 AS DECIMAL(10,2)) AS [Max in MB],
   CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space in MB],
   CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]
FROM sysfiles
ORDER BY groupid DESC

An alternative (not compatible with SQL Server 200) that provides more information, suggested by Tri Effendi SS:

USE [database name]
GO
SELECT
    [TYPE] = A.TYPE_DESC
    ,[FILE_Name] = A.name
    ,[FILEGROUP_NAME] = fg.name
    ,[File_Location] = A.PHYSICAL_NAME
    ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
    ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
    ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
    ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
    ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
            ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;


PostgreSQL Locking Behavior w/ Partial Index

The lock taken is on the table level.  It doesn't lock a specific WHERE clause.


Reference: SQL Server Audit (Database Engine)

  If we enable audit on the main database, will it automatically get
  replicated to our replica?

No it will not automatically get replicated.

The Database Audit Specification object also belongs to a SQL Server audit.

The Server Audit Specification object belongs to an audit. You can create one server audit specification per audit, because both are created at the SQL Server instance scope.


Constructing an SQL query with time intervals

You could try using a CTE recursive function that generates a time series.

There is an answer on SO that shows how to do it:

WITH RECURSIVE dates(StartDate, EndDate) AS
(
    VALUES('2016-10-01 00:00:00', '2016-10-01 00:00:02')
    UNION ALL
    SELECT EndDate as StartDate, date(EndDate, '+2 seconds') as EndDate
    FROM   dates
    WHERE  EndDate < '2016-10-02 00:00:00'
)
SELECT     you_table.*
FROM       your_table
CROSS JOIN dates
WHERE      value > 0.9
AND        time >= StartDate
AND        time <  EndDate;


How can Stored Procedures and Triggers be Automatically Loaded When MySQL Starts

Use a cron task.
Here are some examples for mysql

http://dbperf.wordpress.com/2010/04/05/scheduling-a-mysql-stored-procedure-using-a-linux-cron-job-and-mysql-events/

Add at boot
http://jonathonhill.net/2009-08-18/easy-startup-scripts-using-crontab/


Count the number of occurrences of certain values of TagName column

Sounds like a simple group by:
select tagname, count(*)
from alarmmaster
where tagname like '%Sensor%'
   or groupname like '%Sensor%'
group by tagname;


Disk Sectors

A disk has a fixed sector size, normally 512 bytes or 4096 bytes on some modern disks; these disks will also have a mode where they emulate 512 byte sectors.  The disk will have tracks with varying numbers of sectors; tracks closer to the outside of the disk have more sectors as they have more room for a given bit density.  This allows more efficient usage of the disk space; typically a track will have something like 1,000 512 byte sectors on a modern disk.

Some formatting structures can also include error correcting information in the secotrs, which manifests itself in the disks being low-level formatted with 520 or 528 byte sectors.  In this case the sector still has 512 bytes of user data.  Neither Windows nor Linux support this directly, although i5OS (IBM iSeries) and various SAN controllers do.

Normally the sector/head/track is translated into a logical block address; due to historical issues with backward compatibility the geometry (heads x sectors x tracks) seen by the operating system (particularly on IDE and SATA disks) normally has little to do with its physical structure.

RAID stripe Size

A RAID controller can have a stripe size for an array using striping (e.g. RAID-5 or RAID-10).  If the array has (for exmaple) a 128k stripe, each disk has 128k of contiguous data, and then the next set of data is on the next disk.  Normally you can expect to get approximately one stripe per revolution of the disk, so the stripe size may affect performance on certain workloads.

Partition Alignment

A disk partition may or may not align exactly with a RAID stripe, and can cause performance degradation due to split reads if it is not aligned.  Some systems (e.g. Windows 2008 server) will automatically configure partitions to align with disk volume stripe sizes.  Some (e.g. Windows 2003 server) will not, and you have to use a partition utility that does support stripe alignment to ensure they do.

File System Block Size

The file system will allocate blocks of storage in chunks of a certain size.  Generally this is configurable - for example NTFS will support allocation units from (IIRC) 4K to 64K.  Misalignment of partitions and file system blocks to RAID stripes can cause a single filesystem block read to generate multiple disk accesses where only one would be necessary if the file system blocks aligned correctly with the RAID stripes.

Database Block Size

The database will allocate space in a table or index in some given block size.  In the case of SQL Server this is 8K, and 8K is the default on many systems.  On some systems such as Oracle, this is configurable, and on PostgreSQL it is a build-time option.  On most systems space allocation to tables is normally done in larger chunks, with blocks allocated within those chunks.

Misalignment of filesystem and data allocation blocks can generate multiple I/Os for a single block write, which can drive a performance penalty.

I/O Chunking

Normally a DBMS will actually do its I/O in chunks of more than one block.  For example, on SQL Server, all I/O is done in chunks of 8 blocks, 64k in total).  On Oracle this is configurable.  Casual inspection of the PostgreSQL docs doesn't reveal a specific description of whether PostgreSQL does this, so I'm not sure how it works on this platform.

When the I/O chunk larger than the file system block size or is misaligned with RAID stripe boundaries a disk write from the DB can cause multiple disk writes, which generates a performance penalty.

Disk space usage

No disk space is wasted - the database I/O will use one or more physical I/O operations on the disk to complete - but incorrectly tuned I/O can generate inefficiencies which will slow down the database.  The main things that have to be in alignment are:

RAID stripes and partitions - the partition should begin on a RAID stripe boundary.
Filesystem I/O allocation and raid stripe/partition boundaries - a RAID stripe boundary must align with a filesystem allocation unit, and should be a multiple of the filesystem allocation unit size.
Disk write size and filesystem allocation unit size.  There should be a 1:1 relationship between database I/O operations and filesystem I/O operations.

Misalignment does not create a greater data integrity problem than would otherwise be present.  The database and file system have mechanisms in place to ensure file system opearations are atomic.  Generally a disk crash will result in data loss but not data integrity issues.


Introducing an 'age' category in a patients database

Some of this depends upon your particular database implementation.  The part that is general, however, is the idea of an age category.  Here's one implementation, using ID as a surrogate key and written to work with SQL Server (meaning you would probably need to alter the syntax a little bit to get it to work with another product):

CREATE TABLE dbo.AgeCategory
(
    ID tinyint identity(1,1) NOT NULL,
    MinimumAge tinyint NOT NULL,
    Name varchar(20) NOT NULL
);
ALTER TABLE dbo.AgeCategory ADD CONSTRAINT [PK_AgeCategory] PRIMARY KEY(ID);
CREATE UNIQUE INDEX [IX_MinimumAge] ON dbo.AgeCategory(MinimumAge) INCLUDE(Name);

INSERT INTO dbo.AgeCategory(MinimumAge, Name) values
(0, 'Newborn'),
(1, 'Infant'),
(2, 'Toddler'),
(5, 'Child'),
(12, 'Adolescent'),
(18, 'Adult'),
(30, 'Some marker'),
(40, 'Something else'),
(50, '50+'),
(65, 'Retiree'),
(90, 'Amazing');

I've populated this with sample ages and categories.  Here's an example of how you could use this table.  I'm going to populate a Person table with dates of birth, and then get a query to find their age category.

Here's the sample data:

CREATE TABLE dbo.Person
(
    ID int identity(1,1) NOT NULL,
    Name varchar(50) NOT NULL,
    DateOfBirth datetime NOT NULL
);

insert into dbo.Person(Name, DateOfBirth) values
('Bob', '1983-01-08'),
('Mary', '1976-05-05'),
('Jane', '1960-04-01'),
('Tony', '2012-08-16'),
('Marcy', '1955-06-11'),
('Carl', '1930-12-24'),
('Joseph', '1918-11-11');

Here's the query:

with people as
(
    select
        Name,
        DateOfBirth,
        datediff(day, dateofbirth, current_timestamp) / 365.25 as YearsOld
    from
        dbo.Person p
)
select
    p.Name,
    p.DateOfBirth,
    p.YearsOld,
    a.MinimumAge,
    a.Name as AgeCategory
from
    people p
    cross apply
    (
        select top 1
            ac.MinimumAge,
            ac.Name
        from
            dbo.AgeCategory ac
        where
            ac.MinimumAge < p.YearsOld
        order by
            ac.MinimumAge desc
    ) a;

Note that this query works for SQL Server, but I can't guarantee the syntax will be the same for any other product (or that this is a relatively efficient way to solve this problem for any product other than SQL Server).  What we're doing is calculating the age in terms of years based on the date of birth.  Then, we can apply that to the age category table using the CROSS APPLY function.  This will return the one best age category for a particular row.

Note that you could modify the AgeCategory table to have two age values:  MinimumAge and MaximumAge.  That would get rid of the cross apply statement and replace it with an INNER JOIN, but the downside there is that you might end up with overlapping rows:  if you have a minimum age of 10 and maximum age of 20, and then another row with a minimum age of 15 and a maximum age of 25, you'll have two results for each person between the ages of 15 and 20.  The single-entry table gets rid of that problem without having to introduce a trigger or data check other than a unique index on the age column, but might be more costly in terms of performance with large enough data sets.


Is there any difference at all between NUMERIC and DECIMAL?

They are actually equivalent, but they are independent types, and not technically synonyms, like ROWVERSION and TIMESTAMP - though they may have been referred to as synonyms in the documentation at one time. That is a slightly different meaning of synonym (e.g. they are indistinguishable except in name, not one is an alias for the other). Ironic, right?

What I interpret from the wording in MSDN is actually:

  These types are identical, they just have different names.

Other than the type_id values, everything here is identical:

SELECT * FROM sys.types WHERE name IN (N'numeric', N'decimal');

I have absolutely no knowledge of any behavioral differences between the two, and going back to SQL Server 6.5, have always treated them as 100% interchangeable.

  for DECIMAL(18,2) and NUMERIC(18,2)? Assigning one to the other is technically a "conversion"?

Only if you do so explicitly. You can prove this easily by creating a table and then inspecting the query plan for queries that perform explicit or - you might expect - implicit conversions. Here's a simple table:

CREATE TABLE [dbo].[NumDec]
(
    [num] [numeric](18, 0) NULL,
    [dec] [decimal](18, 0) NULL
);

Now run these queries and capture the plan:

DECLARE @num NUMERIC(18,0);
DECLARE @dec DECIMAL(18,0);

SELECT
  CONVERT(DECIMAL(18,0), [num]), -- conversion
  CONVERT(NUMERIC(18,0), [dec])  -- conversion
FROM dbo.NumDec
UNION ALL SELECT [num],[dec]
  FROM dbo.NumDec WHERE [num] = @dec  -- no conversion
UNION ALL SELECT [num],[dec]
  FROM dbo.NumDec WHERE [dec] = @num; -- no conversion

As shown in SQL Sentry Plan Explorer*, the plan is not really interesting:

But the Expressions tab sure is:

As I commented above, we have explicit conversions where we asked for them, but no explicit conversions where we might have expected them. Seems the optimizer is treating them as interchangeable, too.

Go ahead and try this test, too (data and indexes).

CREATE TABLE [dbo].[NumDec2]
(
    [num] [numeric](18, 2) NULL,
    [dec] [decimal](18, 2) NULL
);

INSERT dbo.NumDec2([num],[dec])
SELECT [object_id] + 0.12, [object_id] + 0.12
  FROM sys.all_columns;

CREATE INDEX [ix_num] ON dbo.NumDec2([num]);
CREATE INDEX [ix_dec] ON dbo.NumDec2([dec]);

Now run this query:

DECLARE @num NUMERIC(18,2) = -1291334356.88,
        @dec NUMERIC(18,2) = -1291334356.88;

SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = @num
UNION ALL
SELECT [num] FROM dbo.NumDec2 WHERE [num] = @dec;

Plan has no conversions (in fact the Expressions tab is empty):

Even these don't lead to any unexpected conversions. Of course you see it on the RHS in the predicate, but in no case did any conversion have to occur against the column data to facilitate the seek (much less force a scan).

SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(DECIMAL(18,2), @num)
UNION ALL
SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(DECIMAL(18,2), @dec)
UNION ALL
SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(NUMERIC(18,2), @num)
UNION ALL
SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(NUMERIC(18,2), @dec)
UNION ALL
SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(DECIMAL(18,2), @num)
UNION ALL
SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(DECIMAL(18,2), @dec)
UNION ALL
SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(NUMERIC(18,2), @num)
UNION ALL
SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(NUMERIC(18,2), @dec);

Personally, I prefer to use the term DECIMAL just because it's much more accurate and descriptive. BIT is "numeric" too.

* Disclaimer: I work for SQL Sentry.


I would try locating all duplicates and their respective highest secondary values like this:

SELECT
  StringColumn,
  MAX(SecondaryColumn) AS HighestValue
FROM
  atable
GROUP BY
  StringColumn
HAVING
  COUNT(*) > 1

and then using the results to produce the final set like this:

SELECT
  t.StringColumn,
  t.SecondaryColumn
FROM
  atable AS t
  INNER JOIN
  (
    SELECT
      StringColumn,
      MAX(SecondaryColumn) AS HighestValue
    FROM
      atable
    GROUP BY
      StringColumn
    HAVING
      COUNT(*) > 1
  ) AS s ON t.StringColumn = s.StringColumn
        AND t.SecondaryColumn <> s.HighestValue
;

I believe an index on (StringColumn, SecondaryColumn) should help the performance.


Decided to just remove and reinstall it!


Yes, you can use the @SkipChecks parameters for this. Create a table with columns for:

DatabaseName NVARCHAR(128)
CheckID INT
ServerName NVARCHAR(128)

Then populate it with the list of databases & checks you want skipped. For example, if you want check 52 skipped for all databases, add a row with CheckID 52, and databasename null. If you want check 52 only skipped for the WebSite database, add a row with DatabaseName = WebSite, CheckID = 52.

Then when you run sp_Blitz, populate these parameters:

@SkipChecksDatabase - the database where your SkipChecks table lives, like DBAtools
@SkipChecksSchema - the schema, like dbo
@SkipChecksTable - the name of the SkipChecks table (could be BlitzSkipChecks for example)


PostgreSQL vacuum starting too late (every 100k instead of 10k dead rows)

Autovacuum will start right away, but it takes some time to process the table. During that time, you don't see a decrease in the number of dead tuples.

To make autovacuum be more aggressive, don't make it start sooner, but make it faster. A threshold of 10000 with a scale factor of 0 seems insane to me, it will cause autovacuum to permanently process this table (at a snail's pace), and you don't end up with significantly less bloat.

Every table needs a certain amount of bloat, and the 20% you have by default are typically fine.

So I recommend these settings:

ALTER TABLE subscriptions
   RESET (autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor),
   SET (autovacuum_analyze_threshold = 50000, autovacuum_vacuum_cost_delay = 0);

That will cause autovacuum to process the table as fast as possible, and you won't end up with much more than 20% bloat. Autoanalyze will run very frequently.

If you really don't want 20% of bloat in spite of what I said, consider setting autovacuum_vacuum_scale_factor to 0.1. I wouldn't go lower than that.


Denormalization Strategies: When is it appropriate to create redundancy for faster querying?

It seems that denormalization is done on a case-by-case basis. If you do decide to denormalize your tables for efficiency, make sure you are thoroughly testing your queries. As @Pieter Geerkens mention

  Every redundancy made for for the purpose of one specific query will slow down all other queries, inserts, and updates against the same data.

That is denormalizing while may speed up some gains, there are very huge costs in not only speeds of other queries, but also in scalability. It's important to take these all into consideration along with other ways to improve performance.

In short, I'll end it with a quote from Chris Date who wrote the book Database in Depth: Relational Theory for Practitioners:

  I believe firmly that anything less than a fully normalized design is strongly contraindicated ... [Y]ou should "denormalize" only as a last resort. That is, you should back off from a fully normalized design only if all other strategies for improving performance have somehow failed to meet requirements.

Extended Reading:

Scaling Secret #2: Denormalizing Your Way To Speed And Profit
Database War Stories #3: Flickr


Comparing data types of a primary and foreign key

I am sure this can be optimized but here is to experiment,

with parent as (select --+ no_merge(dc) no_merge(dccr) no_merge(dtcr)
distinct dc.r_owner ref_owner,  dc.r_constraint_name,dtcr.column_name
,case when dtcr.NULLABLE = 'N' then 'NOT NULL ' end ||
  case when dtcr.DATA_TYPE in ('CHAR','VARCHAR2') then dtcr.DATA_TYPE || ' (' || dtcr.DATA_LENGTH || ')'
     when dtcr.DATA_TYPE = 'DATE' then 'DATE'
     when dtcr.DATA_TYPE = 'TIMESTAMP' then 'TIMESTAMP (' || dtcr.DATA_SCALE || ')'
     when dtcr.DATA_TYPE = 'NUMBER' and dtcr.DATA_PRECISION is null then 'NUMBER'
     when dtcr.DATA_TYPE = 'NUMBER' and dtcr.DATA_PRECISION is not null then 'NUMBER (' || dtcr.DATA_PRECISION ||',' || NVL(dtcr.DATA_SCALE,'0') || ')'
end c_dt
from dba_constraints dc
join dba_cons_columns dccr on (dccr.owner = dc.r_owner and dccr.constraint_name = dc.r_constraint_name)
join dba_tab_columns dtcr  on (dccr.owner = dtcr.owner and dccr.column_name = dtcr.column_name and dccr.table_name = dtcr.table_name)
where dc.constraint_type = 'R' and dc.r_owner = 'SCHEMA_OWNER')
,child as (select --+ no_merge(dc) no_merge(dccr) no_merge(dtcr)
distinct dc.owner ref_owner,  dc.constraint_name, dc.r_constraint_name, dtcr.column_name, dtcr.table_name
,case when dtcr.NULLABLE = 'N' then 'NOT NULL ' end ||
  case when dtcr.DATA_TYPE in ('CHAR','VARCHAR2') then dtcr.DATA_TYPE || ' (' || dtcr.DATA_LENGTH || ')'
     when dtcr.DATA_TYPE = 'DATE' then 'DATE'
     when dtcr.DATA_TYPE = 'TIMESTAMP' then 'TIMESTAMP (' || dtcr.DATA_SCALE || ')'
     when dtcr.DATA_TYPE = 'NUMBER' and dtcr.DATA_PRECISION is null then 'NUMBER'
     when dtcr.DATA_TYPE = 'NUMBER' and dtcr.DATA_PRECISION is not null then 'NUMBER (' || dtcr.DATA_PRECISION ||',' || NVL(dtcr.DATA_SCALE,'0') || ')'
end c_dt
from dba_constraints dc
join dba_cons_columns dccr on (dccr.owner = dc.owner and dccr.constraint_name = dc.constraint_name)
join dba_tab_columns dtcr  on (dccr.owner = dtcr.owner and dccr.column_name = dtcr.column_name and dccr.table_name = dtcr.table_name)
where dc.constraint_type = 'R' )
select parent.ref_owner, parent.r_constraint_name ref_constraint, parent.column_name ref_col, parent.c_dt parent_col_datatype
,child.ref_owner child_owner, child.table_name child_table, child.r_constraint_name child_constraint, child.column_name child_col, child.c_dt child_col_datatype
from parent join child on (parent.r_constraint_name = child.r_constraint_name) order by 1,2,3;


Relational databases in general, but specifically MySQL are thought to have a static structure. Let me elaborate a bit on what static entails (as it doesn't necessarily mean immutable):

  Each version of the application should work with a predefined set of
  tables and internal structure

Only when requirements change (be it functional or things like performance improvements) you will perform an application "migration" to adapt the logic and data to the new ones, and add, delete or ALTER existing tables. If your table structure changes frequently (compared to the number of read and write operations), that is normally a sign of a bad design.

In particular, new users or state of an application (sessions), should not create new sets of tables, but add rows to existing ones.

As exceptions, Schema-based Multi-tenancy is a pattern that would fit the "creation of a new database" for each "application user group", but normally the scope of that is new organizations sharing the same software, or separate communities that require separate configuration; and normally with things like security or cost in mind. For sessions, table-based multi-tenancy is probably the most common pattern.

Dynamicaly creating and deleting tables usually has a high cost compared to inserting and deleting rows, specially on MySQL (creation of new tablespaces, invalidating high regions of memory, extra metadata locking, etc.)


SQL Server Log Shipping from 2012 to 2014/2016

You can happily do ONE-WAY logshipping from lower version to higher version.

The only caveat is that once you failover to higher version, you cannot failback to lower version... same applies to Database Mirroring.

This technique is normally used when you want to do migration from lower version to higher version with minimal downtime.