Is there a non-blocking operation to back up MyISAM tables using Percona XtraBackup?

No - the MyISAM architecture prohibits hot backups - it uses a locking mechanism. You might be interested to my response in a related thread.

Take a mysqldump of your database, change the bit where it says ENGINE=MyISAM to ENGINE=InnoDB (InnoDB uses a MVCC architecture) and then restore - XtraBackup will then perform hot backups. It's relatively easy to use sed to do the edit that I have suggested

mysqldump [options] | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > my_schema.sql

Then restore your dumpfile in the normal way.

MyISAM is going to be deprecated in the near future anyway! [EDIT - added 19/04/2018] - the system tables in MySQL 8 are now InnoDB!

In reply to the OP's question about the Percona's XtraBackup page being a "complete fabrication" -  the quote is "can back up the following storage engines by briefly pausing writes at the end of the backup".

I think that the keyword on that page is  "briefly"! :-) Take a look here - Bill Karwin is a big hitter on this forum.

Also, take a look at what Shlomi Noach says about MyISAM hot backups (or lack of!) - he's also a big hitter in the MySQL world. The file system snapshot to which he refers is mylvmbackup - which appears to have a reasonably good rep. here.


First of all, the syntax for a named parameter marker is CONCAT(:queryDate, '-12.00.00.000000'), that is, it begins with a colon, not a question mark.

Processing of parameter markers is performed on the client side, not on the server, and it's irrelevant if your server happens to be DB2 for z/OS or not. The IBM Data Server Client (the common driver for IBM database servers) supports named parameter markers.

Whether Data Studio SQL editor can properly parse named parameter markers and pass them to the driver is a different question, to which I'm afraid I don't know the answer.


How to read configuration option into variable?

Besides current_setting(), SHOW is workable in this context, it's just a matter of returning a scalar rather than a set. Your function slightly fixed to work would be:

create or replace function get_param()
returns text as $$
declare v text;
begin
  SHOW custom.param INTO v;
  return v;
end;
$$ language plpgsql;

However this form does not seem to offer any advantage over calling directly current_setting(), so it's probably not worth creating such functions.


Mysql master-master replication auto flush old logs

To rotate out logs more than 7 days old, add this to my.cnf
[mysqld]
expire-logs-days=7

then restart mysql.
To perform this manually, run this command:
mysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY) + INTERVAL 0 SECOND;

This will erase binary logs back to Midnight 7 days ago.
WARNING !!!
Do not erase binary logs from the OS level because it disturbs mysqld's internal functionality for rotating binary logs.
UPDATE 2012-01-24 13:20 EDT
Make sure you do not purge binary logs that replication is using.
Here is an example how you can safely erase binary logs manually...
For a Master-Master setup M1 and M2
Go to M2 and run this:
mysql> SHOW SLAVE STATUS\G

You will see two lines that show binary log names

Master_Log_File
Relay_Master_Log_File

Master_Log_File represents the current binary log on M1 that was last read on M2.
Relay_Master_Log_File represents the current binary log on M1 that was last executed on M2.
Let's suppose that M2 has this:
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.176.205
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000131
          Read_Master_Log_Pos: 570079419
               Relay_Log_File: relay-bin.003496
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000131
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 570079419
              Relay_Log_Space: 545
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

mysql>

ON M2, Relay_Master_Log_File says mysql-bin.000131. So on M1, you can run this:
mysql> PURGE BINARY LOGS TO 'mysql-bin.000131';

Since Master_Log_File and Relay_Master_Log_File are the same, why not use Master_Log_File?
REASON #1 : SQL Error in the Slave
When an SQL Error happens in M2 on the SQL Thread, Replication stops processing SQL from the Relay Logs. The IO Thread continues downloading new SQL entries from M1 and appending to M2's Relay Logs.
REASON #2 : Long Running SQL Query in the Slave
If a query comes along, such as UPDATE that updates every row in a table and it takes 5 minutes. During those 5 minutes, the IO Thread continues downloading new SQL entries from M1 and appending to M2's Relay Logs.
Given both reasons, there are some occassion where Relay_Master_Log_File and Master_Log_File are different. In that instance, always use Relay_Master_Log_File. In light of this, there is another reason:
REASON #3 : Log Corruption in Slave or Master
There are rare times when bad network transmission will produce corrupt relay logs. When you do SHOW SLAVE STATUS\G, an error message will explain the the binary log may be corrupt. You should start firsdt with the relay logs.
To clear them out and start with fresh set, do SHOW SLAVE STATUS\G, get the Relay_Master_Log_File (RMLF), the Exec_Master_Log_Pos (EMLP), and run this on M2
STOP SLAVE;
CHANGE MASTER TO master_log_file='RMLF',master_log_pos=EMLP;
START SLAVE;

The CHANGE MASTER TO command erases all relay logs and starts downloading from scratch.
Once you START SLAVE;, if you get the corrupt error log message again, then network transmission was not the issue. The binary log on M1 may just be corrupt after all.
You must go to M1, make copy of the the suspected log, run mysqlbinlog against that copied binary log and redirect to a text file. Read the text file. If the text files contains gibberish or indiscernable characters, then you must perform a full sync of the slave.


Simple Implementation of SQL Server 2008 Encryption

Q1a: Is the master key password created per DB instance?

A1a: Assuming the question really means "Is the master key created per DB?"" then answer is Yes. Each DB has an different master key. there is also a thing called the service master key, which is per SQL Server instance.

  Q1b: When I backup that DB (.bak) will I be able to restore the DB to
  another Server2?

A1b: Yes. The master key in the restored database can be open using the original password. Then the Server2 service master key encryption can be added to the restored DB master key.

  Q2: Do I need to backup the certificate, master key or symmetric key
  if I want to restore to another server?

A2: No. All these objects are part of the database and they are restored along with the database. Specific needs to backup individual keys may arise from operational requirements (eg. key escrow).

  Q3: When should the symmetric key be opened?

A3: Keys that require opening have to be opened in the session. Once opened, they stay open until explicitly closed or until the session disconnects. An 'open' key is 'open' only in the session that opened it.

  Q4: Should I worry about who can open and close the symmetric key? ...

A4: Now this is the real question. You have two alternatives really, which correspond to two distinct scenarios:

Scenario A: when the service needs to access encrypted data without asking the user for passwords to the data. This is the vast majority of the cases. In this case the service needs to open the keys somehow. the solution is that the SQL Server uses the service master key to encrypt the database master key and the database master key is used to encrypt the certificate's private key and the private key is used to encrypt a symmetric key and the symmetric key encrypts the data. The SQL Server itself can reach any data following this chain, because it has access to the service master key. In this case the data is cryptographically protected only against accidental media loss: a lost laptop with the database on it, or an improperly disposed HDD with database of backup files on it etc. For all other threats, the data is not cryptographically protected, is only protected by the access control: since the SQL Server itself can decrypt the data w/o needing a password from the user, any user with sufficient privileges can access the data w/o knowing the password. In other words, a compromised ASP application may allow access to the encrypted data. As a note, scenarios in which the root of encryption is some secret stored on the ASP web application itself are just a (badly designed) variation on this and do not change anything.

Scenario B: when the service requires the user to provide a password. The password must come from the end user. In a web application, the user must type the password in a form in the browser, it gets sent over SSL channel to the ASP application, which passes it to the SQL Session (again on an SSL channel) and SQL can now decrypt the data using this password. This scenario is typical for multi-tenant applications in which tenants provide the data access password. In this scenarios the data is cryptographically secured against unauthorized access, because the SQL Server itself, nor the intermediate ASP web application, simply do not know the password. Even for a syadmin user with all privileges it would be impossible to read the data. Data can be moved at will and remains just as unscrutable, as it can be, again, only be read by the end-user that has knowledge of the password at the root of the encryption chain. Note that any 'shortcut' deviation in this scenario in which the password is 'saved' somewhere intermediately and not provided by the end-user this scenario degrades immediately to the first Scenario A.

A mandatory read for you: Encryption Hierarchy.


How can I stop a single Progress OpenEdge server process and not the whole database?

After googling around some more with different queries, I found my answer from the Progress Knowledge Base after all.

First of all, it turns out that indeed, the remote server processes by default stay active until database shutdown happens. So if all the slots are occupied by 4GL servers, they will not open up a slot for a SQL server by themselves.

However, the PROMON utility has commands to terminate specific server processes gracefully to free up server slots. The sequence of commands is as follows from the PROMON main menu:

R&D for Advanced options
4 for Administrative functions
7 for Server options
7 for Terminate a server

From the server list find the server ID that needs to be terminated and type it in. After issuing the command, using the R option to refresh the screen the terminated server ID should show up as inactive.

After terminating a server, a new SQL server should be spawned if an ODBC connection to the system is initiated.

On Windows you might need to use a local user account, that is in the Administrators group. Domain users belonging to Administrators group are not allowed to terminate servers.

Sources:

Background information how remote servers are handled
Instructions on how to terminate a single server process
Information on how domain administrator accounts are handled on Windows


Does pg_dump flag -j give performance improvement up to -j 16 or -j 32 or even -j 64?

You will probably not see a performance increase.

"pg_dump" only dumps multiple tables in parallel.  It does not parallelize the dumping of data for any single table.  So the entire process will always take at least as long as it takes to dump the single slowest table, no matter how much parallelism you throw at it.  Most applications are dominated by a handful of large tables, so won't see much increase to assign more jobs than the number of large tables + 1.

And beyond that, you would need enough CPU and IO.  '-Fd' uses compression by default, and that compression is often the bottleneck (on the CPU) rather than IO.  But if you have enough large tables and enough cores, then IO would probably become the bottleneck.


Search multiple values in multiple columns

Personally I would create your table

id
result_id
value

But with a single value in the value column.  Now you can index on the value column and that should dramatically speed things up.  Your query would then look like this:

SELECT result_id
FROM TableName
WHERE VALUE IN ('02','12','20')
GROUP BY result_id
HAVING COUNT(1) >= 3

One of the benefits of this is that you can now easily check for 1 match, 2 matches etc.  You can also increase the number of items they could check to 50 and your table structure wouldn't have to change.

That being said right now that won't work for you.  Right now I would use something like this:

SELECT *
FROM TableName
WHERE '02' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '12' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '20' IN (Number_1, Number_2, Number_3, Number_4, Number_5)


Copy large database from one server to another

The slide to increase the size should be there. If you don't see maybe your user isn't allowed to take such decision:

By the way, you said that your database is actually slightly more than 1TB.
In this case you have to move to Premium P11 or  P15.
Moving it there will presents some constraints.


Migration From Oracle to PostgreSQL Encoding Errors

Sounds like the data you're exporting isn't utf-8 encoded, or is being chopped up / mangled in transit.

This: Ã9ÃýÃ0Ã looks like what happens when you decode utf-8 as iso-8859-15, cp1252, or related 1-byte encodings. But it's not valid utf-8 when demangled. Perhaps you've cut it part way through a string, rather than copying from the beginning of the value?

0xcb 0xcf is indeed nonsense as utf-8. It's a continuation byte without any appropriate context.

Trying other encodings, it's:

ËÏ in latin-1. Unlikely. Same in iso-8859 3,4,9,10,14,15, and in the default Windows encoding for Western Europe / US, cp1252.
ËĎ in iso-8859-2
ЫЯ in iso-8859-5
ثد in iso-8859-6
ΛΟ in iso-8859-7
หฯ in iso-8859-11
ĖĻ in iso-8859-13

Any of those look like likely candidates, given what you know about the original data?

You need to either export in a known encoding, or determine the encoding of the input reliably.

It's also possible that the data is in fact starting with a utf-8 continuation byte and was supposed to be utf-8, but something has mangled it by cutting utf-8 strings up byte-wise.

If you can post a complete line of exported CSV it might help.


Running select affects performance of stored procedure called from code

You would have to do some digging but one way I can imagine is the SELECT is pulling data pages into cache from disk and subsequent SP runs are just getting their data straight from the buffer pool without having to hit disk.


Storing numbers that will be also used as digits

Your best solution for this is undoubtedly the use of GENERATED columns (also known as VIRTUAL or COMPUTED columns).

You mention MariaDB - the documentation is available here. The core bit (the syntax) is here:

<type>  [GENERATED ALWAYS]  AS   ( <expression> )
[VIRTUAL | PERSISTENT | STORED]  [UNIQUE] [UNIQUE KEY] [COMMENT <text>]

So, for MariaDB, I constructed the following table here (fiddle):

CREATE TABLE the_code_1
(
  code CHAR(8),
  the_code_location CHAR(1) GENERATED ALWAYS AS (SUBSTR(code, 3, 1))
  VIRTUAL COMMENT '3rd character'
);

Then I INSERTED some data:

INSERT INTO the_code_1 (code) VALUES ('12345678');
INSERT INTO the_code_1 (code) VALUES ('87654321');

Note that only 1 field is inserted! Then, I SELECT from that table:

SELECT * FROM the_code_1;

Result:

code    the_code_location
_________________________
12345678                3
87654321                6

Which is the 3rd character in both of those fields!

The table definition above has VIRTUAL, meaning that it's calculated on the fly. The fiddle also shows the use of the PERSISTENT keyword (in place of VIRTUAL), meaning that the data is physically stored on disk.

This is a fiddle using MySQL 5.7 - the only difference is that in place of the the PERSISTENT keyword, STORED is required. The MySQL syntax is here. This is also interesting:

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]  -- Note! no PERSISTENT keyword!
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

All in all, depending on your disk versus performance requirements, one of these two options is the way to go - you can do a few tests. Your requirements are minimal so either of the two options should work satisfactorily.


2 (+1) possible reasons:

0 - AUDIT ALL does not audit TRUNCATE operations. Use AUDIT TABLE BY ACCESS;

1 - You perform the above as SYS user. Actions performed by SYS are
    audited into files, under the location of audit_file_dest

2 - AUDIT (Traditional Auditing)

  Track the issuance of SQL statements in subsequent user sessions. You
  can track the issuance of a specific SQL statement or of all SQL
  statements authorized by a particular system privilege. Auditing
  operations on SQL statements apply only to subsequent sessions, not to
  current sessions.

Try it in a new session.


What does "Data_free" mean?  Not much.  Seriously, there are several areas in a table where there is "free space"; Data_free refers to only one of them:

An InnoDB block is exactly 16KB; there is almost always some wasted space in a block.
A block in a BTree is normally between 50% and 100% full.
Groups of blocks ("extents") are allocated in chunks of 8MB(?).  Your 7MiB is part of one such extent.  You will very often see 4, 5, 6, or 7, in spite of the main allocation unit being 16KB.
If you are talking about the main tablespace (ibdata1), Data_free refers to how much space is "free" in it; this is usually a large number.

If you were to OPTIMIZE TABLE, the table might, or might not, shrink from the current 25.6 GiB; it could even increase!  The 7.0 MiB would probably stay 7, or might change to 4 or 5 or 6.

"Does it mean some sort of reserved space?" -- Partially a fluke of the way it allocates space.  But mostly it is a useless number.  If it is bigger than 7 MiB, then there is likely to be some space that could be given back to the OS with OPTIMIZE -- _assuming it is file_per_table.  But don't use OPTIMIZE unless you have both file_per_table and Data_free is a significant fraction of the table size.  (This may occur after a big DELETE.)

"Or is it actually free space due to a size limit?" -- Tables under some threshold are allocated 16KB at a time; over that threshhold, they are allocated 8MB at a time.


How to execute a query from psql without waiting for the result?

You can try sending psql to the background:

psql -f your_sql_file.sql &

Or, connecting to the local DB, you can use dblink to dispatch a query to the remote DB:

SELECT dblink_connect('your_connection_name', 'your_connection_string');
SELECT dblink_send_query('your_connection_name', 'your_query');

Note that dblink_send_query can only send one query at a time.  So, if you want to run multiple SQL statements, this is not your solution.

Or, you can start a pg_agent job on the remote server, which requires no manual intervention, therefore the state of your home box has no effect on running your job.  The same can be achieved by setting up a cron (or even better, at - thanks, Erwin) job executing your script.

Additionally, if you have a long running job which you start manually, you can start a screen session on the server and run the file from there.  In this case, you can log off and go home, and the script will keep running.


Is Policy-Based Management dead? Is it scalable?

I liked it and have not seen it being removed anywhere so far.  I used PowerShell DSC since the beta and it's great but it is no replacement for PBM.  DSC is great for enforcing system states and some SQL states.  I can use it to roll out automated testing environments and ensure all the domain membership, firewalls, users, restored databases, masked data, shares, etc. are all done.

Then PBM would be used to help out in areas that I want a report to meet compliance requirements, actually enforcing state on the fly.  DCS has a check in time where it scans, and it requires you to write custom scripts to test and apply changes if the test fails, it also requires a central server.

I like both but for very different purposes and PBM can be very helpful if deployed as part of a greater effort.


Primary key guarantees: duplicates and nullity

Oracle allows primary keys to have duplicate and null values.

Not really. What you have managed to create - after a series of complicated statements - is an enabled but not validated constraint. Which means that Oracle will check inserts and updates (for uniqueness) but there may be left existing duplicates. So it is a PK only in name. It's a not-validated constraint so not really a PK.

In other words, can I absolutely rely on PK uniqueness and non-nullity within the documented feature set (ignoring cases like manually edited data files, bugs, or modified server source code)?
More context: I am working on an application that displays records and allows a user to delete them on any arbitrary table. When dealing with an arbitrary table, I need to use metadata and other means to determine which guarantees I have. Much database development is about guarantees -- guarantees that a large transaction will commit or not (but not partially commit). Guarantees that a successfully modified row stays modified. And guarantees that a primary key uniquely refers to exactly one row.

Yes, you can rely but only if your application reads the metadata and all the details - which may differ from DBMS to DBMS.

SQL Server allows for disabled constraints? The application has to consider this when reading and interpreting the metadata tables.

Oracle allows for disabled or non-validated constraints? The application has to consider those options too, accordingly.

Postgres allows for some different weird scenarios? It has to consider them, too.

Another possible option - which may or may not be an option for you - is if the application is the only application that creates, deletes and modifies database objects or if all applications that do so are under your or a single control. Then it can rely on the soundness of the metadata ("consider only unique constraints") without checking for these details / rare cases - because it can rely that no such rare case is ever created in the first place.
With your conclusions:

Is it Oracle's fault? No.

I agree. I don't know why this was allowed but it probably solves some problem. And it probably was meant to be used only temporarily - e.g. during an import from another source to a database.

Is the table properly designed? No.
Are the statements to create such a table complex? Immaterial.

I agree, too, on both.

Is the Id column a "real" primary key? Maybe not, but this is more a philosophical matter. ID is listed (and is shown as "ENABLED") by the query in the top-rated SO answer to the linked question above. Perhaps that question needs to add a check for validation, but I have never once seen anyone check for this in code, nor do any answers in that thread or related threads I have found.

We come to the same conclusion: needs to add a check for validation.

As for what other possibilities exist in SQL Server and Postgres:

SQL Server:

PRIMARY KEY and UNIQUE constraints cannot be disabled.
Unique indexes can be disabled. They can also be re-enabled without check which would result in a situation very similar to the "enabled non-validated" in Oracle.
FOREIGN KEY and CHECK constraints can be disabled. They can be re-enabled without check, too.
See ALTER TABLE and Disable indexes and constraints for details. My answer in this question: What is a WITH CHECK CHECK CONSTRAINT? explains options and the syntax, which differs from Oracle's.

PostgreSQL:

PRIMARY KEY, UNIQUE and EXCLUDE constraints cannot be disabled.
FOREIGN KEY and CHECK constraints cannot be disabled. They can be created with the NOT VALID option though (which means that they are enabled without checking existing rows, as in SQL Server). See ALTER TABLE for details.


Can Execute trigger in SQL Server 2008 R2

I'd put this in a comment but I want to give pictures.

My guess is that you are hitting the little green arrow, or DEBUG button

Instead of the red exclamation point, or RUN button.

It's a fairly common mistake.  FYI you can also hit Ctrl-E to "execute" the script or Alt-F5 to "debug" it.  The TSQL debugger requires elevated rights (sysadmin if I remember correctly) and as you saw doesn't work in versions prior to 2008.


The only way to get to what you're looking for is through an audit process of some kind, using a pre-defined checklist like the DISA STIG or CIS or some other recognized benchmark. These will all include a variety (even a majority) of "metrics" about documentation and policy that can't be measured through automation, so don't think there's an easy way to do this to get meaningful results.
There are whole areas of research and organization dedicated to how to approach this on a complete infrastructure or system level, like the Risk Management Framework. There are also legal requirements to evaluate against if your system is covered by the Fair Credit Reporting Act, Graham-Beach-Bliley Act, Children's Online Privacy Protection Act, The Health Information Technology for Economic and Clinical Health Act, HIPPA, the Federal Information Security Act of 2002, or GDPR. These carry their own auditing requirements.
It's also important to recognize that a system or application's security is about all layers of that system: database; operating systems; application servers; web servers; application code; network infrastructure; storage; authentication and authorization; and even user and admin training. All must be evaluated from top to bottom to get anything close to a complete picture. Even then with these checklists all you're really demonstrating is what you've prepared for / hardened against; there's no absolute measure of a system's security, per se.


The root cause is those tables in msdb  (e.g. backupmediafamily, backupset, backupfile)
are MS Shipped Object. by default, Ola skip those MS Shipped object for rebuild/reorganize.

MS Shipped Object means the objects that are created by internal SQL Server components.

Solution: Add 1 more parameter @MSShippedObjects = 'Y' to enable the Ola command to get those MS Shipped Object to defragment.


The simplest solution normalizes a period:

SELECT NORMALIZE Employee, Status, PERIOD(date,date+1) AS pd
FROM mytable
ORDER BY Employee, pd

NORMALIZE is a very unknown syntax, it combines overlapping periods, you just need to create a one day period out of the date column. As this results in a period you get a slightly different output, the end date is +1 compared to the expected result.

To fix this you can split the period back into seperate columns:

SELECT Employee, Status, Begin(pd),
   Last(pd) -- last included date, i.e. expected EndDT
FROM
 (
   SELECT NORMALIZE Employee, Status, PERIOD(date,date+1) AS pd
   FROM myTable
 ) AS dt
ORDER BY 1,pd

Getting this Some time in the history is more complicated, would need additional calculations, you should check if you really need it.

Another more classical solution calculates groups of rows with  consecutive values:

SELECT Employee, Status, Min(date), Max(date)
FROM
 (
   SELECT Employee, Status, date,
   -- this is the tricky part, the differnce between a monotonous sequence (row_number)
   -- and another monotonous sequence with gaps (date)
   -- is constant when there's no gap
      date - Row_Number()
             Over (PARTITION BY Employee, Status
                   ORDER BY date) AS grp
   FROM vt
 ) AS dt
GROUP BY Employee, Status, grp
ORDER BY 3

Both solution will result in an extra row when you add

20171120   001          At work
...
001           At work       20171115                      20171116
001           At work       20171120                      20171120

If you want to combine with the previous row

001           At work       20171115                      20171120

it's also a bit more complicated...


How to export base and discover its structure

You can generate an SQL file (of metadata) with built-in commandline tools (available on all database installations).

Just log in to the database server, create a full=y, content=metadata_only export:

expdp \'/ as sysdba\' full=y content=metadata_only

Then use impdp to generate an SQL file:

impdp \'/ as sysdba\' sqlfile=metadata.sql

Without specifying a location, Data Pump places its files by default in the directory DATA_PUMP_DIR, but expdp will display the full path of the file it created.


I would use the PowerShell Command from the dbatools module Get-DbaLastGoodCheckDb which will enable you to check all of your servers in one go.

Here is a sample output from the command

You can get detailed information like this

and check a whole bunch of servers like this

It's really quick too. In my lab of 10 servers with 125 databases it completes in 5.3 seconds

You can read more about dbatools at https://dbatools.io


Import CSV to MySQL with long text fields containing line breaks

According to the MySQL docs, you can set both the field and line delimiters in the LOAD DATA statement:

[{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]

That should enable you to import the TEXT data.  Just be careful in choosing your delimiters!


MongoDB automatic failover between 2 data centers

First, you are correct. If the replica set doesn't have a quorum, it will relegate to secondary state.

The solution is pretty easy: put two data bearing nodes in each of the two datacenters and an arbiter on a cheapo VM in a third datacenter. Hell, a free AWS instance should do the trick. It is very unlikely that you'll loose two of three datacenters at the same time.

From my experience, everything else will fire back on you.


Filter results based on data from another table

Start with that:

select * from products where price > 100 and price < 150;

Then...

select * from products p
left join discounted_products dp on dp.product_id = p.id
left join discounts d on dp.discount_id = d.discount_id
 where (price+IFNULL(d.amount,0)) > 100 and (price+IFNULL(d.amount,0)) < 150;

I do not have any idea about the column names you are using, so...

Is that what you want?


Grouping on an expression (and possible HAVE)

You don't have to.  You can just rewrite it to CTE/inline view , and refer by alias :

SELECT t.* FROM
(
SELECT
    a,
    b,
    CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c as d
from #TEMP
)t
GROUP BY a,b,d
HAVING d > 0
ORDER BY 3 ;

or

WITH cte1 AS
(
SELECT
    a,
    b,
    CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c as d
from #TEMP
)
select t.*
from cte1 t
group by t.a,t.b,t.d
having t.d > 0
order by 3;

Also, there is no need for  GROUP BY( and HAVING can be replaced by WHERE)  in this particular case - SELECT DISTINCT will do the job.


The query window is in the context of the master database.

You need to execute USE AnandSchool; at the top of the query window.  Subsequent attempts to SELECT * FROM ... will return an error message.

Prior to creating a table using T-SQL, you need to ensure the query window is using the correct database.


Starting in version 9.4, it now automatically zeroes the tail end of the WAL file.  (Actually it is just mostly zero, there are some block headers that don't get zeroed, but still the result is very compressible).

In version 9.2, there is a program named pg_clearxlogtail you can use.  You can add it into your archive_command before the compression step.

If you are using 9.3, you are out of luck.

Note that checkpoints do not inherently cause log file switches. It is probably archive_timeout which is causing the switches.


what you are looking for is to dynamically create tables in the destination.
or in other words Copying tables from Source to destination programmatically

here is a very useful article that transfers tables from access to sql dynamically
but you can take the same concept and apply it on your source as well.

https://stackoverflow.com/questions/6298806/how-do-i-programmatically-get-the-list-of-ms-access-tables-within-an-ssis-packag


SSAS - Inferred Dimension Attributes - are they necessary?

I'm not sure I understand your question fully, so please clarify if necessary.

If you want to query the cube to get persons by state, you should add a Referenced relationship between Person and State, using Case as the Intermediate dimension.

Select the empty box at the intersection of Person and State and click on the button
Select Referenced from the relationship type dropdown
Select Case as the Intermediate dimension
Selecting the matching attributes in the two attribute dropdowns
Click OK.

Deploy and process the cube, and it should work the way you want.  Then you can get rid of the (State - Person) dimension.


By recompiling/committing the Alert, the double emails have stopped. It must have been a bad commit initially.


Determine last usage date of a linked server

You'll have to setup SQL Profiler to monitor for queries which use the linked server.  There's no DMV that you can easily monitor for usage.


mysql - unique index with nulls - actual solution anyone?

[This is not tested as it requires MySQL 5.7.6 or above]

While I agree with the comments above, I still have an idea that can be tried, which I think is not the best in terms of performance, but it solves the issue you're describing.

The idea is to add a key that deals with NULL as a concrete value, like '0', or any other value. Then, uniquely index the combination of the fields that you want to be unique.

MySQL 5.7.6 supports generated columns.

ALTER TABLE b2c_constant_bindings
ADD unique_md5 char(32) AS
    (MD5(CONCAT_WS('X', ifnull(constant_id, 0), ifnull(company_id, 0), ifnull(object_id, 0), ifnull(property_id,0))))
    UNIQUE;

Instead of using CONCAT, I am using CONCAT_WS to avoid having same results in cases similar to CONCAT(1, 23) and CONCAT(12, 3) (ss per @Rick James' notice).


How to safely change MySQL innodb variable 'innodb_log_file_size'?

Yes it is safe to delete the log file once mysqld has been shutdown

In light of this, just perform the following steps:

mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop
mv /var/lib/mysql/ib_logfile[01] /tmp
service mysql start

Starting up mysqld will recreate ib_logfile0 and ib_logfile1

Give it a Try !!!

UPDATE 2011-10-20 16:40 EDT

It cleanly page out all data in the InnoDB Buffer Pool prior to redoing the Log Files, you should set this option about 1 hour before shutdown:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

By default, innodb_max_dirty_pages_pct is 75 (MySQL 5.5+) or 90 (prior to MySQL 5.5). Setting this to zero keeps the number of dirty pages under 1% of the InnoDB Buffer Pool. Performing service mysql stop does this anyway. In addition, a shutdown will finish up any remaining items in the redo log. To keep to this option just add it to /etc/my.cnf:

[mysqld]
innodb_max_dirty_pages_pct = 0

UPDATE 2013-04-19 16:16 EDT

I updated my answer a little more with innodb_fast_shutdown because I used to restart mysql and stop mysql to do this. Now, this one-step is vital because every transaction uncommitted may have other moving parts within and outside of the InnoDB Transaction Logs (See InnoDB Infrastructure).

Please note that setting innodb_fast_shutdown to 2 would clean the logs out as well but more moving parts still exist and gets picked on Crash Recovery during mysqld's startup. Setting of 0 is best.


A bit confused on how mongo and mongod load configuration files

When you are using automation agent, configuration is done at OPS manager (or cloud front) and there is not much what you can do at actual machine. Yes, in this case, automation agents config file includes information of port and other parameters.

When you start locally installed mongod (f.ex. systemctl start mongod), it is set at (systemctl) mongod.service file that startup command is mongod -f /etc/mongod.conf, so config file is /etc/mongod.conf where all startup parameters has been defined.


Getting intervals between time from all succeeding rows? (Time is in text data type)

You can create a date-time Access type from text using the format() function, so you CAN use datediff.  You should consider creating an query that adds a new column that is date formatted.   The SQL I include here uses that repeatedly which makes it hard to read.

Assume your table is called Table1 with just those three columns, and grouped on Name with no duplicate times (e.g. interval between current row and next row is never zero).

NAME DATE       TIME
a   10/19/2015  5:00 PM
a   10/20/2015  5:00 PM
a   10/20/2015  5:30 PM
b   10/20/2015  4:00 PM
b   10/21/2015  3:00 PM
b   10/21/2015  5:00 PM

The following query unfortunately does not show that first line (for each group) that has no interval, only subsequent rows with the current time, and the interval between the current time and the previous time.    The results look like this:

NAME    DATE        TIME    INTERVAL
a     10/19/2015    5:00 PM 86400
a     10/20/2015    5:00 PM 1800
b     10/20/2015    4:00 PM 82800
b     10/21/2015    3:00 PM 7200

Create a new query, do not add any tables, switch to SQL view,  and Copy and paste the SQL below.  change "Table1" in the code below to your actual table name which you don't provide.

 SELECT table1.NAME,future.DATE,future.TIME,
        Min(DateDiff('s',
              Format([Table1].[TIME] & " " & [Table1][DATE]),
              Format([future].[TIME] & " " & [future].[DATE])))
        AS [INTERVAL]
 FROM Table1 INNER JOIN Table1 AS future ON Table1.NAME = future.NAME
 WHERE (((
   Format([future].[TIME] & " " & [future].[DATE]))
     >Format([Table1].[TIME] & " " & [Table1].[DATE]
   )))
 GROUP BY table1.NAME, future.DATE,future.TIME;

The key is to use a self join (add your data table twice to your query).  When you do that Access creates a table alias with _1 added to the end.  Instead I select the query properties and add an alias for the table in design view.    Join on the NAME field only, and limit on the date-time to select the minimum date-time that is just greater than the current row.

Let me know if that works for you


I guess I got the answer. Its not a part of DB2. But its a feature of SecureSphere. As SecureSphere captured the user name as Hashed User and its a security feature of SecureSphere that when SecureSphere encounters user names it cannot decrypt it will put it as "Hashed User".


how to install SQL 2000 in windows server 2016 OS

I would highly recommend that you take the time and effort required to connect your app to a newer version of SQL, preferably one that is officially supported. i.e 2012+. (SQL 2000, 2005 and 2008 and 2008R2 are all officially obsolete.)

If you have issues with deprecated features or connectivity, SQL 2008R2   supports database compatibility back to 2000. It's still obsolete, but is still a much more sophisticated product than 2000.

Edit: Technically Support for 2008 & 2008R2 is still available from Microsoft through their MS Lifecycle Support Site.

In most cases apps are oblivious to which version of SQL they are on as long as they can connect and the features and functions still work.

Even if you can't do a backup and restore, you should be able to create a new db and use SSIS to import the data. If this is a current application I highly recommend you go back to the vendors for assistance and ask/insist they support current db's. Requiring a 17 yr old db to be able to install or run their software is terrible.

If this is a legacy app and no longer supported I suspect you have a much bigger issue looming than just moving a db.

If this is a legacy app and you just want to keep it running but need to replace hardware, I would consider virtualising the server.


Disk errors in one of the SQL cluster nodes and SQL patching was failing with a lot of errors related to disk and permission issues

There is possibility of disk replacement, for detailed steps..

Seems your team decision is correct as you have been tried to apply patches that been ran through failures.

Edit (Including disk replacement steps summary from same source - referral link):

Take backups of all the databases including System DBs. [ONLY for disaster recovery]
Present all the drives from the new storage to the Windows Cluster. These disks must be visible under “Available Storage” Failover Cluster Manager
Now add these disks to the SQL Cluster Role/Group.
From the SQL Server dependencies, remove the drive letters pointing to the old storage
Take SQL Server Resource to an OFFLINE State
Make sure that the old disks should be online. Now start copying all the folders as is from old storage to the new storage. DO NOT change any files or folder structures.
Once all the files are copied, you need to swap the drive letters. Such that the new drives will get the old drive letters and vice-versa [This can be directly from Failover Cluster Manager] - This might not applicable in your case
Add the disks to the SQL Server dependencies
Make sure the SQL server service account has appropriate permissions on new disk/folder especially for the folders that are pointed in SQL Server - startup parameters
Bring the SQL Resource ONLINE.
Test connectivity from applications.
Once testing is completed thoroughly, disks from old storage can be removed from the Failover Cluster Manager


How to startup a oracle database with sql plus or with any developer tool without connecting to the Database

normally you need a statically defined listener for this. So, in the listener.ora add the entries defining your instance. Next to that you need a remote passwordfile setup
remote_login_passwordfile = exclusive

to allow remote sys connects (don't forget the "as sysdba" part)


What databases can I use to represent ordered sets?

I think you are missing one of the key principles of relational database design. I thought it was Dr E F Codd but I can't find a reference.

Data is stored without sorting, and is sorted when it is retrieved. This was intended to avoid the overhead of re-ordering the data with every update or insert.

Sorting is done with the order by clause when you select data.

The fact that some vendors store the data ordered by a clustered index is a bit misleading. If your data must be sorted you should always use order by rather than rely upon the the underlying storage.

As you must have some criteria for sorting, why not incorporate that in your query so that data is ordered as it is queried every time you select it.


Here's a short concise answer.

Vacuum full takes out an exclusive lock and rebuilds the table so that it has no empty blocks (we'll pretend fill factor is 100% for now).

Vacuum freeze marks a table's contents with a very special transaction timestamp that tells postgres that it does not need to be vacuumed, ever. Next update this frozen id will disappear.

For instance, the template0 database is frozen because it never changes (by default you cannot connect to it.)

Every so often the autovacuum daemon will check a database and its tables to see what needs to be vacuumed. If a table is vacuum freeze'd and then never updated, the autovacuum daemon will simply pass it by. Also the "wrap around" protection in postgresql will never kick in on that table either.

tl;dr freezing marks a table as not needing any autovac maintenance. The next update will unfreeze it.


How to display Postgres version in the CLI pompt?

Since 9.3, you can do this using \gset in psqlrc:

select split_part(version(),' ',2) pmt
\gset
\set PROMPT1 '%:pmt:'

Or, as 'the value of the selected prompt variable is printed literally, except where a percent sign (%) is encountered':

select split_part(version(),' ',2) "PROMPT1"
\gset

If your version is <9.3, it's still possible, but much uglier, e.g. something like:

\set PROMPT1 `psql postgres postgres -Atc "select split_part(version(),' ',2)"`


The leading wildcard in the like, not the function, is preventing the use of the table1.col1 index (assuming it is indexed).

For an indexed approach, the values can be store reversed...

update table1 set col1=reverse(col1);
alter table1 add key (col1);  -- if not already indexed

update table2 set col2=reverse(col2);

update table1 a
join table2 b    on a.col1 like concat(b.col2,'.%')
set a.foreign_id=b.foreign_id;

Of course, this comes with the downside that the column will need to be reversed for display...

select reverse(col1) from table1 where...

Here is a similar denormalized approach with the reversed value redundantly stored in the same table...

alter table table1 add column col1_rev -- col1 datatype specification here;
update table1 set col1_rev=reverse(col1);
alter table table1 add key (col1_rev);

alter table table2 add column col2_rev -- col2 datatype specification here;
update table2 set col2_rev=reverse(col2);
alter table table2 add key (col2_rev);

update table1 a
  join table2 b on a.col1_rev like concat(b.col2_rev,'.%')
    set a.foreign_id=b.foreign_id;

A normalized approach would be another table of unique table1.col1 values as stored and reversed...

create table table1_col1_rev as
select distinct col1
     , reverse(col1) as col1_rev
  from table1;

alter table table1_col1_rev
  add key (col1)
        , (col1_rev);

update table1 a
     , table1_col1_rev b
     , table2 c
   set a.foreign_id=c.foreign_id
 where b.col1_rev like reverse(concat('%.',c.col2))
   and a.col1=b.col1;


MySQL Still purging binary logs and ignoring expire_logs_days

(Not an answer, but too big for a comment.)

Flipping over to a new binlog happens two ways:

passing max_binlog_size, or
FLUSH LOGS

But neither should cause the deletion of the previous binlog without PURGE or expires.

Sounds like FLUSH is happening, probably from the 'nightly backup'.

Please provide the details of your "nightly backup"; the answer is likely to be buried in there somewhere.


How to put one table on one disk and anther table on another (a ramdisk)?

I have not done it but, if not mistaken, it can be easily accomplished with tablespaces.

Let us know how it went.


Run DELETE on each INSERT to enforce row limit

If you have an index on user_id, you can drop it and replace it with an index on (user_id,timestamp). This will also save a sort when displaying the latest log entries (WHERE user_id=... ORDER BY timestamp DESC LIMIT n).
Then:
SELECT timestamp FROM logs WHERE userid=1 ORDER BY timestamp DESC LIMIT 1 OFFSET 100

If there are more than 100 rows, this will return the timestamp of the 100th row. Otherwise it will return nothing. To delete the old logs for one user:
DELETE FROM logs WHERE userid=1 AND timestamp <=
(SELECT timestamp FROM logs WHERE userid=1 ORDER BY timestamp DESC LIMIT 1 OFFSET 100);

This is a very fast query. If the select doesn't find any rows to delete, it will be well under 1ms.
To delete all the old logs:
DELETE FROM logs
USING (SELECT userid, f.timestamp FROM users CROSS JOIN LATERAL (SELECT timestamp FROM logs WHERE logs.userid=users.userid ORDER BY timestamp DESC LIMIT 1 OFFSET 100) f) oldlogs
WHERE logs.userid=oldlogs.userid AND logs.timestamp<=oldlogs.timestamp;

This will probably seq-scan logs, so it could be slow. Here's a better one which will exploit the index on (userid,timestamp) and be fast if there is nothing to do:
DELETE FROM logs USING
( SELECT userid,timestamp FROM users
  CROSS JOIN LATERAL (SELECT timestamp FROM logs WHERE logs.userid=users.userid ORDER BY timestamp DESC OFFSET 100) oldlogs ) o
WHERE logs.userid=o.userid AND logs.timestamp=o.timestamp;

To answer your comment "what if many logs all have the same timestamp?"... Well this should never happen since if you want your logs to be useful, they should be ordered by something unique, otherwise you don't know in what order they were logged. But... you can simply use the primary key:
-- one user
DELETE FROM logs USING
( SELECT id FROM logs WHERE logs.userid=123 ORDER BY timestamp DESC, id DESC OFFSET 100 ) o
WHERE logs.id=o.id;

-- all users
DELETE FROM logs USING
( SELECT oldlogs.id FROM users
  CROSS JOIN LATERAL (SELECT id FROM logs WHERE logs.userid=users.userid ORDER BY timestamp DESC, id DESC OFFSET 100) oldlogs ) o
WHERE logs.id=o.id;

So if they have the same timestamp, the ORDER BY will keep the highest ids which should have been inserted last.


Postgres has an option "valid until" that specifies user's password expiry Date&time. but, I believe, what you are asking can be done as an application feature not the Database feature.


SELECT x.id1, x.id2
FROM   tbl x
WHERE  EXISTS (
    SELECT *
    FROM   tbl y
    WHERE  (y.id1, y.id2) = (x.id2, x.id1)
    );

Assuming that the combination of (id1, id2) is unique, so no DISTINCT. This would still allow (1,2) and (2,1) to coexist (and both will be returned), but no two rows with (1,2).

Also assuming id1 != id2 for every row (deriving from your test data and question). A unique entry (1,1) without doppelganger would be returned by this query. If my first assumption is true, you can exclude id1 = id2 right away. Else, you'd need something like row_number() to identify duplicates ...

A multicolumn index would greatly speed up such a query:

CREATE UNIQUE INDEX tbl_id1_id2_idx ON tbl (id1, id2);

Use it without UNIQUE keyword, should (id1, id2) not be unique after all.

Or, you can use those other techniques discussed under your prequel question, accordingly modified.


You might want to verify this in SQLPlus.  If it still doesn't disconnect you then verify your assumptions by running the following after the trigger finishes:

SELECT 'Check This' FROM dual WHERE sys_context('USERENV','SESSION_USER') = 'xx';

SELECT 'Check This' FROM dual WHERE sys_context('USERENV','IP_ADDRESS') <> '10.0.30.219';

SELECT 'Check This' FROM user_role_privs WHERE granted_role='DBA';

SELECT 'Check This' FROM user_objects WHERE Object_Name='YOU_MAY_NOT_LOGIN' AND Object_Type='TRIGGER';

SELECT 'Check This' FROM User_sys_Privs WHERE Privilege='ADMINISTER DATABASE TRIGGER';

If any of these return Check This then one of your assumptions is incorrect.

For me all that was necessary to be disconnected was the RAISE_APPLICATION_ERROR, but another site uses an EXECUTE IMMEDIATE 'DISCONNECT'; as well.  Perhaps this allows it to work even when the DBA role has been granted.  I have not tested it to know.

You should also be aware that this should not be used for security in place of secure passwords and other measures as the IP_ADDRESS the client sends can be altered.


How to organize a single query to several sql servers?

The servers are situated in different networks, connected through
  internet.

Preferably, don't do that.

VPN and (as AceCTO suggested) linked servers if you must but if this is a query you want to run regularly, move the data. BCP it, replicate it, log ship it... just move it.


Elegantly handling 'enum-like' magic numbers in T-SQL code

I would actually recommend against tying this lookup in any way to SSMS / SQLCMD -specific features. Doing so would require that the code could only ever be executed using just those two programs. You wouldn't even be able to put any such logic into a stored procedure, even if it was executed from SSMS or SQLCMD. You also can't use SQLCMD commands in SSRS, etc.

Some options (that are completely independent of the client tool used to execute them):

If SQL Server 2019 is an option, then it's possible that the new Scalar UDF Inlining feature might resolve the issue with your Enum('Sales Doc Type', 'Order') approach.

It's possible that an inline TVF might do the trick. You just might need to restructure those predicates in the WHERE clause to be INNER JOINs:

FROM  SOPxxxxx sod
INNER JOIN dbo.Enum('Sales Doc Type', 'Order') sdt
        ON sod.SOPTYPE = sdt.Value

Of course, the IN list is a little trickier as I think that might require a LEFT JOIN on each option and then a WHERE predicate to ensure that both of those did not result in a NULL:

FROM  dbo.POP10110 pod
LEFT JOIN dbo.Enum('PO Line State', 'Released') pls_r
        ON pod.POLNESTA = pls_r.Value
LEFT JOIN dbo.Enum('PO Line State', 'Change Order') pls_co
        ON pod.POLNESTA = pls_co.Value
WHERE  ( pls_r.Value IS NOT NULL
     OR  pls_co.Value IS NOT NULL)

OR, using this same iTVF, you might be able to do non-correlated subqueries that might be more readable / manageable:

FROM  SOPxxxxx sod
WHERE sod.SOPTYPE = (SELECT sdt.Value FROM dbo.Enum('Sales Doc Type', 'Order') sdt)

And the IN list is at least looks better:

FROM  dbo.POP10110 pod
WHERE pod.POLNESTA IN (
                       SELECT pls_r.Value
                       FROM   dbo.Enum('PO Line State', 'Released') pls_r
                       UNION ALL
                       SELECT pls_co.Value
                       FROM   dbo.Enum('PO Line State', 'Change Order') pls_co
                      )

Or perhaps (not sure what the query optimizer will prefer):

FROM  dbo.POP10110 pod
WHERE pod.POLNESTA IN (
           (SELECT pls_r.Value FROM dbo.Enum('PO Line State', 'Released') pls_r),
           (SELECT pls_co.Value FROM dbo.Enum('PO Line State', 'Change Order') pls_co)
                      )

If SQLCLR is an option, then SQLCLR scalar UDFs that do not do any data access and are marked as [SqlFunction(IsDeterministic = true)] are constant folded into the execution plan (the "Query Processing Architecture Guide" documentation currently states that SQLCLR functions cannot be folded, but that is provably wrong, so I suspect that the documentation was merely not updated when SQL Server 2012 added that ability; I have submitted a documentation update to fix that: Fix "Constant Folding" info in "Query Processing Architecture Guide").

The trick here is to read the data from the table without doing so in the SQLCLR scalar UDF so as to not prevent it from being foldable. In order to accomplish this, do the following:

Create a static readonly dictionary at the class level.
Create a static constructor for the class that uses a regular, external SqlConnection to connect to the local instance. This will select from the table and populate the static dictionary. This constructor will ensure that the dictionary is always populated, even if the assembly gets unloaded due to memory pressure, DBCC FREESYSTEMCACHE, etc. Unfortunately, the internal "context" connection cannot be used here since there is no Sql Context when the static constructor is executed (but would be great if there was!).
Mark the assembly as WITH PERMISSION_SET = EXTERNAL_ACCESS. No need to specify UNSAFE because the dictionary is "readonly". And even though it is "readonly", it can still have items added and/or removed from it. This should not pose any problems as it's a single use collection that is always the same for all users.
Decorate the static method that is exposed to T-SQL to do the lookup with:

[SqlFunction(IsDeterministic = true)]

No need to specify DataAccess or SystemDataAccess properties as those are set to None by default.

This method will allow you to implement the Enum() approach that you had started doing. And, since you are working with a 3rd party app, you can keep things "clean" by installing the assembly into a separate database (and same would go for report procs, etc, I suppose). I have tested and verified that it still does the constant folding.

OR (this is really option 3b): Given that this is a 3rd party app, IF it is safe to assume that the lookup / enum values won't be changing much, if ever, then you don't really need to read them from a table. You can simple have the entire dictionary hard-coded in the .NET code. In this approach, you could then keep the assembly marked as PERMISSION_SET = SAFE as there would be no need for that external SqlConnection back to the current instance.


There is a view object available in several ERD tools that I've seen (TOAD, Agilian, Visio).  I'm not entirely sure that it is formally a part of any notation, but could be construed as a stereotype, as it is effectively just another entity.

Demonstrating the relationship to the source tables is a bit more difficult, IMO. I would just use a generic connector, especially as the relationship is likely much more complex than can be described visually (I just created a view that is populated in part by functions that run complex queries on multiple tables).  I would absolutely not use the relationship connectors.


Community wiki answer:

I think a composite clustered index key with CustomerID as the first column will be best since that's in the WHERE clause of nearly all queries.

There may be more splits compared to an incremental key (or more likely suboptimal page density for a time if you manage and maintain fill factor to avoid 'bad' splits). However, the overall performance improvement for customer queries is substantial, because the key lookup is avoided.

OrderID or OrderDate may be best for the second column depending on your most critical queries.

For example, if customers see a chronological list of recent orders after logging in to a web site, OrderDate should be next, to optimize ORDER BY OrderDate DESC.

If you choose OrderID as the clustered index, with a non-clustered index on CustomerID, you'll still get splits and fragmentation, just in the non-clustered index.


Can I decrypt TDE transaction log files for use with 3rd party software?

Is there a way to decrypt TDE log files on disk for use with 3rd party software?
The short answer is -- this cannot be done.

Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.
[...] When the data is written from buffer pool to disk (MDF, LDF & NDF) the data is first encrypted and then then encrypted data will be sent to disk.
When the data is read from  the disk, first the data will be decrypted and then it will be sent to buffer pool.

Taken from How to enable/remove Transparent Data Encryption (TDE).
See also SQL Server 2008 Transparent Data Encryption.


Will this work reliably?

The answer to this depends on the strength of the guarantee you need. There have been bugs in the past around CASE evaluation-order, for example constant-folding an error-generating expression. There are no current bugs in SQL Server 2014 that I am aware of that would affect your query, but that does not mean they might not occur in future.

This is a risk with any code of course, but my assessment is that the risk is higher when combining this technique with MERGE. Merge has a complex implementation, and there have been many related bugs in the past, as you know.

  Is there a reason this should not be done?

Personally, I would look to avoid a divide-by-zero as part of the solution. A better option might be to attempt to write a value that violates an explicit CHECK (or some other) constraint on the target table. This would result in an explicit Assert operator in the execution plan, positioned after the Merge operator. It might not be desirable to add such a constraint (or even possible if the column is allowed to hold all values in its type domain) but it is one thing to consider.

One other general suggestion: avoid updating the target column when the values match. This is logically a no-op, but that doesn't always translate physically.

DECLARE @target AS TABLE (pk integer PRIMARY KEY, col2 integer NULL CHECK (col2 <> -1));
DECLARE @source AS TABLE (pk integer PRIMARY KEY, col2 integer NULL);

INSERT @target VALUES (1, 100), (2, NULL), (3, 300);
INSERT @source VALUES (1, 100), (2, 200), (3, 301);

MERGE @target AS T
USING @source AS S
    ON S.pk = T.pk
WHEN MATCHED
    AND
    (
        -- No update if values match
        T.col2 IS NULL
        OR T.col2 <> S.col2
    )
    THEN UPDATE SET col2 =
        CASE
            WHEN T.col2 IS NULL THEN S.col2
            ELSE -1 -- violates CHECK constraint
        END;


Postgres soft delete partition, what to do with Foreign keys?

A table of 1.3M rows is pretty small.  It would be unusual for a table of that size to benefit from partitioning.

Have you looked at the queries which are slow with EXPLAIN (ANALYZE, BUFFERS) to see what the problems are?

When partitioning works well, it is because it works hand-in-glove with your use cases.  So there can't really be generic partitioning advice.  You would have to tell us much more about the types of queries you run and what your performance expectations of them are, and what the current execution plans look like.


In SQL Server 2008, converting binary to a character representation became a lot faster and easier:

CREATE TABLE dbo.X
(
    pk integer PRIMARY KEY,
    c1 integer NOT NULL,
    rv rowversion NOT NULL,
    rvc AS CONVERT(char(18), CONVERT(binary(8), rv), 1)
);

Notice the style 1 option on the CONVERT to char. Also, the rowversion type is equivalent to binary(8) (when not nullable, varbinary(8) otherwise).

We can now create an index on the computed column:

-- Create index on the computed column
-- Note PERSISTED is *not* required
CREATE UNIQUE INDEX i
ON dbo.X (rvc);

Add some sample data:

-- Some rows
INSERT dbo.X
    (pk, c1)
VALUES
    (1, 100),
    (2, 200),
    (3, 300);

And display the results:

-- Show the data
SELECT
    X.pk,
    X.c1,
    X.rv,
    X.rvc
FROM dbo.X AS X;

Output example:

The task of joining to the table with rowversions in character format is now trivial.

SQL Server 2005 version

This requires a helper function:

CREATE FUNCTION dbo.Bin8ToHexStr
    (@Hex binary(8))
RETURNS char(18)
WITH SCHEMABINDING
AS
BEGIN
    RETURN
        '0x' +
        CONVERT(xml, N'').value('xs:hexBinary(sql:variable("@Hex"))', 'char(16)');
END;

The table definition becomes:

CREATE TABLE dbo.X
(
    pk integer PRIMARY KEY,
    c1 integer NOT NULL,
    rv rowversion NOT NULL,
    rvc AS dbo.Bin8ToHexStr(CONVERT(binary(8), rv))
);

Everything else proceeds as before, including the index.


LIKE '%http%www%domain.com%'

will find the lines.  But SQL cannot extract the desired part.  Instead, do the extraction in your application code.

OR...

Redesign your schema to already have the URL in a separate column.


What is the difference between Shrink Database and File?

Simply...

DBCC ShrinkDatabase(): shrink all files
DBCC ShrinkFile(): just one file

For example, you may have a log backup issue and it's grown out of control so you run DBCC ShrinkFile().

You almost never use ShrinkDatabase.

Before you consider using either command, please read Paul Randal's blog on shrinking.

I'd shrink neither one of the files (mdf, ldf) unless there was a clear reason. The files are the size they are because they need to be. Any blogs suggesting to do so as part of regular maintenance probably don't understand how SQL Server works.


"TASK MANAGER" process is taking over a single-user mode database. What is it?

Had the same issue today, If you have not put your AUTO_UPDATE_STATISTICS ASYNC OFF, you will not be able to enter your database, you can solve this by putting your database offline. Important to know is that you need to set your deadlock priority to high, otherwise you will be deadlocked out of the command. Use following commands to get out of SINGLE user mode

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [YourDBName] SET OFFLINE WITH ROLLBACK IMMEDIATE

Followed by

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [YourDBName] SET ONLINE WITH ROLLBACK IMMEDIATE

Followed by

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [YourDBName] SET MULTI_USER WITH ROLLBACK IMMEDIATE


How to avoid empty rows in SSIS Excel Destination?

I tested this in SSIS from a 2008 R2 install and Excel 2010.

Using the Excel 97-2003 output format in the connection manager, I was able to reproduce the case where there was the header name in the first row, then a blank, then the data. But changing the "First row has column names" had a different effect for me: I still got the header row, but no blank.

Using the Excel 2007 format, I wasn't able to suppress the header row either, though in both cases, it didn't output a blank row.

You didn't say if you want the header row or not, or which output format you're using; based on my testing, you're using 97-2003, so all you might have to do is switch to the 2007 format.

Attaching a Data Viewer just before the Excel Destination shows only data rows, so the extra blank is being added by the Destination component. So if the Destination isn't configurable to get rid of the blank, you're out of luck.

Suffice to say, though, since the behaviour of this component seems very buggy and the whole setup process to even get this to work at all was such a pain, I would strongly consider switching to a more stable component like the Flat File Destination to output CSV, which you can open directly in Excel, too.


GROUP BY clause / Aggregate function error

It looks like you might have to change your first few lines to reflect this:

CREATE VIEW product_variation_stock_view AS
SELECT
    product_variations.product_id AS product_id,
    product_variations.id AS product_variation_id,
    COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) AS stock,
    CASE WHEN COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) > 0
        THEN true
        ELSE false
    END in_stock
GROUP BY product_variations.id, product_variations.product_id
FROM product_variations


Store lat lon in addition to the geography data type?

The coordinates can be computed with ST_X/ST_Y/ST_Centroid, when needed.
If you just want to view the data, use ST_AsText or ST_AsEWKT.

You need separate columns only if there is some client software that needs these values, but is not able to use those SQL functions.


All users have access to sys tables. Giving read access on the database is orthogonal to accessing the sys tables. System catalogs (ie. sys views and tables) have built in security and you cannot grant, deny or revoke read permissions on them. The visibility of entities in system catalogs derives from the permissions granted on the entity itself, not on the system catalog. To give an example, an user can see a row in sys.tables because it has VIEW DEFINITION permission on that table.

This is laso explained in the MSDN:

  The visibility of the metadata in catalog views is limited to
  securables that a user either owns or on which the user has been
  granted some permission. For more information, see Metadata Visibility Configuration.


Can I simply copy mdf and log files into the data folder to add a new database?

Does MSSQL server automatically attach all mdf's in the data directory?

No it does not attach a new mdf and ldf automatically.

Instead of fiddling around with stopping sql service and copying around mdf and ldf files, I suggest you take a look at backup and restore commands.

backup database foo
to disk = 'd:\backup\foo_full.bak'
with init, stats =10, compression

-- now restore the database with move and recovery

restore database foo
from disk = 'd:\backup\foo_full.bak' -- backup location
with recovery, stats =10,
move 'logicalName' to 'M:\data\foo.mdf', -- mdf location
move 'logicalName_log' to 'L:\log\foo_log.ldf' -- ldf location


How to get output from trigger on linked server

Have you thought about modifying your trigger to put that integer into a table?  Include some identifying information, say the primary key from the table you are inserting into.  Then just query the table after the insert.


Recommended ways to upgrade AlwaysOn AG from 2014 to 2016

The short answer is yes, much the same as when you mirror from a lower version to a higher version.  Make sure you do not have the 2016 as readable so it's not trying to upgrade the database version.  That said I haven't tried this, just going on how log shipping/mirroring work.

If it works it is only supported doing this as a means to upgrade, leaving 2014 replicating to a 2016 replica for extended periods of time is not going to be supported by MSFT if you hit an issue.


Query that always worked now hangs indefinitely

Here's what I'd generally do. I apologize if the indexes are duplicative to what you already have. I started looking at the output you posted, but there was  lot in there that I didn't have time to weed through.

Architecture:

Replace WHERE CLAUSE function dbo.dataSKU with a computed column in xtra_itemmaster and xtra_SKUmaster. I don't mean use the function in a computed column, I mean pull out the code you showed me and put that in one.

You can do that with a case expression like this (not copy and paste-able)

ALTER TABLE dbo.Table ADD [Computed SKU Column Name Here] AS CASE WHEN SKUlite LIKE '%COMP' THEN left(SKUlite, len(SKUlite)-4) ELSE SKUlite END /*PERSISTED if possible*/

That will allow you to index the computed expression, which should make the joins more efficient.

Query:

After adding indexes, play with removing the distinct. You're already grouping by a bunch of columns, so I'm not sure what else you're getting out of this.

Pull out the order by Pprin.product_id unless you really need it.

Replace dbo.deduceMFRfromSubCatName with inline table valued function. I can't do this rewrite, even if you show me the code.

Indexes I'd like:

xtra_icecat_printers1_rel_carts2
 - (prodid1, prodID2)

xtra_icecat_allproducts
 - (product_id, SKUlite)

xtra_itemmaster
 - ([Computed SKU column], CatID, subCatID)

xtra_SKUmaster
 - ([Computed SKU column], dateCreated, lastUpdated, lastIceCatFetch)

xtra_icecat_allproducts
 - (product_id, category_id) INCLUDE (model_name)

xtra_icecat_productdata
 - (product_id)

Xtra_IceCat_Cats
 - (catID)

Xtra_IceCat_PrinterNameOverride
 - (printerID) INCLUDE (forceName, dateForced)

category
 - (CatID)

subcategory
(SubcatID) INCLUDE (category)

And, as discussed in the comments, replace the ISNULL in the WHERE clause as well.

Hope this helps!


Column cannot be converted from type 'int' to type 'bigint(20) unsigned'?

I'm not sure why you are getting that error. I have 2 solutions. The solutions will give you an error if your data truly can't be converted to unsigned bigint.

ALTER TABLE `order_item_units` ADD COLUMN `parent_id2` BIGINT(255) UNSIGNED AFTER `parent_id`;
UPDATE `order_item_units` SET `parent_id2` = `parent_id`;
SELECT * FROM `order_item_units` WHERE `parent_id2` != `parent_id` OR `parent_id` IS NULL;

Verify data from the 3rd query.
Once you're comfortable that the data is in place, drop the old column. Then rename the new column to the old column name. It is imperative that you verify your data before dropping.

ALTER TABLE `order_item_units` DROP COLUMN `parent_id`;
ALTER TABLE `order_item_units` CHANGE `parent_id2` `parent_id` BIGINT(255) UNSIGNED;

Don't forget to add any constraints to the new column.

You could also do the same process with a temporary table OR creating a new table with the new column definition. Then selecting your old table into it. Verify your data. Rename your original table by appending "_old" to it. Then rename your corrected table to the original table name


Forced Parameterization generating duplicate plans

You will not be able to turn on Trace Flag 4199 in Azure SQL Server. You will get an error message. In fact, you cannot turn on any trace flags (globally or session scope) in the Azure SQL database. At the time of writing this, there were 24 trace flags enabled with Global Scope for Azure SQL database. You can check the list by running DBCC TRACESTATUS.If you want to mimic the same behavior of TF4199 run the below T-SQL statement.
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON ;
Reference : ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
I suggest you read this article:
Understanding Optimizer Timeout and how Complex queries can be Affected in SQL Server by Joseph Pilov
Plus look at the other hints you are getting from running sp_blitzcache and try to resolve those.

Many Rows Table Spool

Table Spool by Hugo Kornelis

Nested Loops Joins and Performance Spools by Paul White

non-SARGables

Non-SARGable Predicates by Brent Ozar

The Two Ways to Fix Non-Sargable Queries by Brent Ozar


pg_restore using PostGIS/PostgreSQL Dump Changes Data in Geography Column

Your first output is showing as EWKT (Extended Well Known Text) while the second output is WKB (Well Known Binary). These are both standard representations of the internal geometry but are designed for different end users (human v machine).
To be honest I have never see PostGIS return WKT in a standard select statement, usually you would wrap the geometry in ST_AsTEXT or ST_AsEWKT to see that.
Any way I think your data is just fine.


Using a DELETE JOIN
--
-- Collect all keys you intend to keep
--
DROP TABLE IF EXISTS CallDetailRecord_Keys;
CREATE TABLE CallDetailRecord_Keys
SELECT CallDetailRecordID FROM CallDetailRecord WHERE 1=2;
ALTER TABLE CallDetailRecord_Keys ADD PRIMARY KEY (CallDetailRecordID);
--
-- Collect all keys with 0 for StartTime
--
INSERT INTO CallDetailRecord_Keys
SELECT MinCallDetailRecordID FROM CallDetailRecord
WHERE StartTime = 0;
--
-- Collect the Minimum CallDetailRecordID for all nonzero StartTimes
--
INSERT INTO CallDetailRecord_Keys
SELECT MinCallDetailRecordID FROM
(SELECT StartTime,MIN(CallDetailRecordID) MinCallDetailRecordID
FROM CallDetailRecord WHERE StartTime > 0 GROUP BY StartTime) A;
--
-- Perform DELETE JOIN
--
DELETE A.* FROM CallDetailRecord A
LEFT JOIN CallDetailRecord_Keys B USING (CallDetailRecordID)
WHERE B.CallDetailRecordID IS NULL;

Test this against a copy of the table like this:
CREATE TABLE CallDetailRecord_Test LIKE CallDetailRecord;
INSERT INTO CallDetailRecord_Test SELECT * FROM CallDetailRecord;
--
-- Collect all keys you intend to keep
--
DROP TABLE IF EXISTS CallDetailRecord_Keys;
CREATE TABLE CallDetailRecord_Keys
SELECT CallDetailRecordID FROM CallDetailRecord_Test WHERE 1=2;
ALTER TABLE CallDetailRecord_Keys ADD PRIMARY KEY (CallDetailRecordID);
--
-- Collect all keys with 0 for StartTime
--
INSERT INTO CallDetailRecord_Keys
SELECT MinCallDetailRecordID FROM CallDetailRecord_Test
WHERE StartTime = 0;
--
-- Collect the Minimum CallDetailRecordID for all nonzero StartTimes
--
INSERT INTO CallDetailRecord_Keys
SELECT MinCallDetailRecordID FROM
(SELECT StartTime,MIN(CallDetailRecordID) MinCallDetailRecordID
FROM CallDetailRecord_Test WHERE StartTime > 0 GROUP BY StartTime) A;
--
-- Perform DELETE JOIN
--
DELETE A.* FROM CallDetailRecord_Test A
LEFT JOIN CallDetailRecord_Keys B USING (CallDetailRecordID)
WHERE B.CallDetailRecordID IS NULL;

If the CallDetailRecord_Test has the rows left over that you wanted, then this works for.
Give it a Try !!!
CAVEAT
Some would recommend doing a DELETE JOIN of a table against itself. I am not comfortable with that because some keys could disappear during query optimization and the rows would not be deleted as expected. I wrote about this 2.5 years ago in this post :  Problem with MySQL subquery


Question on Oracle rowid column in every table

I found the answer of my own question. The so called object number from DBMS_ROWID.Rowid_object corresponds to dba_objects.data_object_id, not object_id.

sys@ORCL>select owner, object_name, object_id, data_object_id from dba_objects where data_object_id = 73321;

OWNER    OBJECT_NAME OBJECT_ID  DATA_OBJECT_ID
-------- ----------- ---------- --------------
HR       EMPLOYEES   73953      73321

sys@ORCL>


What is the most efficient way of inserting 199 millon rows of data?

BCP is likely to be the best tool for the task. SQLFool has a good BCP basics article on its usage.

@Marian posted a comprehensive list of BCP optimisation references in response to a question I asked, which you may find useful.


Relation to original tables or to existing linking table

Andriy already said most of it, so I'll just be short.
After your description, settings come to existance upon the condition that a guest and an event are already linked, therefore option 1 would be choice, for that model will convey that concept to anyone viewing it.
There is some talk about surrogate vs composite keys. Anyway, Smaller PK => smaller indexes, but as in your case you'll be going with int surrogate keys for guests and events tables, then the composite key will also be small, meaning that performance loss potential against surrogate key is small, especially if you'll be searching by guest/event.
The composite key, on the other hand, will give more meaning to the key.


SQL Server 2008 R2 Query For Failed Email Notifications

Let's say you wanted in the last 5 minutes:

USE msdb;
GO

DECLARE @age INT = 5; -- age in minutes
DECLARE @cutoff DATETIME = DATEADD(MINUTE, -@age, CURRENT_TIMESTAMP);

SELECT recipients, subject, sent_status, send_request_date
  FROM dbo.sysmail_faileditems
  WHERE send_request_date >= @cutoff
  ORDER BY send_request_date;

/*
 In fact, there are some items that appear in the log
 but not in failed items for some reason, and failed
 items doesn't describe the problem anyway. So as @Oleg
 added I would also run this query:
*/

SELECT mi.recipients, mi.subject, l.description, l.log_date
  FROM dbo.sysmail_log AS l
  LEFT OUTER JOIN dbo.sysmail_mailitems AS mi
  ON l.mailitem_id = mi.mailitem_id
  WHERE l.log_date >= @cutoff
  AND l.event_type > 1
  ORDER BY l.log_date;

Sorry, needed to poison an SMTP server to verify that my original answer was insufficient. :-)


There is something very wrong with the last line, which is a no-op (it has no effect being there).

AND TBLA.mobile = (SELECT mobile)

Because (SELECT mobile) creates a small subquery returning a single scalar column, which is, tada - TBLA.mobile.  So that condition resolves to nothing, really.

That aside, you're getting something like this

Mobile          Factor_id
0312-1232132    1234
0312-1232132    1235
0312-1232132    1244
0312-1232132    1314

If the customer bought product_id 16 on 4 different invoices.  DISTINCT on the entire row won't work.  If you only need the mobile number to contact, then

  SELECT TBLA.mobile, MAX(TBLB.factor_id) LastFactorId
    FROM dbo.payware_factor_product TBLB
    JOIN dbo.payware_factor TBLA ON TBLB.factor_id = TBLA.factor_id
   WHERE TBLB.product_id = 16
GROUP BY TBLA.mobile

You can substitute MAX for MIN to get the first instance, or a complicated FOR XML subquery if you wanted all the factor_id's in a comma separated list.  Or you could remove the column entirely.

Now, if your query is not as simple as shown, and you wanted everything from the B table, then you'll need to use ROW_NUMBER() to choose an individual row from B for a distinct row in A, like the following:

      SELECT *
        FROM
  (
      SELECT TBLA.mobile, TBLB.*, RN=row_number() over (partition by TBLA.mobile
                                                        order by TBLB.factor_id desc)
        FROM dbo.payware_factor_product TBLB
        JOIN dbo.payware_factor TBLA ON TBLB.factor_id = TBLA.factor_id
       WHERE TBLB.product_id = 16
    GROUP BY TBLA.mobile
  )        X
       WHERE RN=1;