Why isn't the ASYMKEY_ID function returning the Asymmetric Key ID?

Why is this not working?

The AsymKey_ID function is returning NULL because it cannot find that particular Asymmetric Key. I also got a NULL until I prefixed the literal string with an N. So the following should work:

SELECT [Email],
       ASYMKEY_ID(N'smGK_АSymmetricKey_01'),
       EncryptByAsymKey(AsymKey_ID(N'smGK_АSymmetricKey_01'), [Email])
FROM   myTable;

The root cause of this issue is that you have a character in the key name that does not cleanly translate into ASCII (for the Code Page used by the default Collation of the Database that this is running in). In order to see the issue more clearly, run the following:

SELECT 'smGK_АSymmetricKey_01';
-- smGK_?SymmetricKey_01

Now that we know which character is causing the issue, we can copy/paste it by itself into the UNICODE() built-in function to see its value, and converting that decimal value into binary/hex will give us the Code Point (remember to include the N prefix on the literal so that it doesn't get translated into "?"):

SELECT UNICODE(N'А') AS [Decimal], CONVERT(BINARY(2), UNICODE(N'А')) AS [CodePoint];
-- 1040 0x0410

We can then look up that Code Point at http://unicode-table.com/en/0410/ to see that it is "Cyrillic Capital Letter A". This means that a second solution to this issue is to drop and recreate the Asymmetric Key with a Latin Capital "A" (i.e. CHAR(65) ) as that would not require the N prefix on the string literal.

ALSO, you do not need the OPEN MASTER KEY... / CLOSE MASTER KEY statements, at least not to encrypt with an Asymmetric Key; the single statement shown above will work without the Open and Close. Not sure about when using DECRYPTBYASYMKEY. The Open / Close is needed, however, with a Symmetric Key.

  why the EncryptByAsymKey and DeCryptByAsymKey functions works with the same key ID? I think that when asymmetric key is used, I will grant to specific users to control the public key and ...

These two functions use the same AsymKeyID because that is how they are supposed to work. "Controlling" a public key does not make sense because it is "public". The whole idea is that knowledge of the "public" key does not hurt you because it cannot be used to decrypt the data. This is why people sometimes put their PGP public key in their email signature (or at least used to). This allows for anyone to encrypt data so that only those who have the "private" key will be able to read it. This is why you don't need an open Key or even specify a password when encrypting via ENCRYPTBYASYMKEY. Also, the public_key column in sys.asymmetric_keys is available to anyone who can SELECT from that system catalog view.

  If this is not the case and only one group of users will be able to encrypt - decrypt the data, then there is no logical difference between the two type of keys (only in terms of performance).

Again, anyone (who has rights to see the key **) can encrypt, but only those with access to the password of either the Database Master Key OR the Asymmetric Key itself (depending on which one the Asymmetric Key was protected with) will be able to decrypt. So you do have separation.

There is also a rather large difference between Symmetric and Asymmetric encyption, besides Symmetric being much faster than Asymmetric:

When using a Symmetric Key, it needs to be "open" in order to encrypt or decrypt, not just decrypt. Hence, no separation.
Symmetric encryption is vulnerable to "Whole-value substitution" attacks. The following example is taken from the MSDN page for ENCRYPTBYKEY:

  Employee_ID        Standard_Title             Base_Pay
  345                Copy Room Assistant        Fskj%7^edhn00
  697                Chief Financial Officer    M0x8900f56543
  694                Data Entry Supervisor      Cvc97824%^34f

  Without breaking the encryption, a malicious user can infer significant information from the context in which the ciphertext is stored. Because a Chief Financial Officer is paid more than a Copy Room Assistant, it follows that the value encrypted as M0x8900f56543 must be greater than the value encrypted as Fskj%7^edhn00. If so, any user with ALTER permission on the table can give the Copy Room Assistant a raise by replacing the data in his Base_Pay field with a copy of the data stored in the Base_Pay field of the Chief Financial Officer. This whole-value substitution attack bypasses encryption altogether.

  Whole-value substitution attacks can be thwarted by adding contextual information to the plaintext before encrypting it. This contextual information is used to verify that the plaintext data has not been moved.

  If an authenticator parameter is specified when data is encrypted, the same authenticator is required to decrypt the data by using DecryptByKey. ...

** The O.P. pointed out in a comment on this answer that Users need meta-data visibility rights to an Asymmetric Key in order to see it and use it. This can be done by:

being in a role that implicitly has such rights
being granted a Database-level permission that implicitly has such rights
being granted any of the Asymmetric Key permissions on a particular Asymmetric Key:

GRANT VIEW DEFINITION ON ASYMMETRIC KEY::{asym_key_name} TO [{user_name}];

Please see the MSDN page for GRANT Asymmetric Key Permissions for full details.

Not having such rights would be another possible reason for the ASYMKEY_ID built-in function returning NULL. And because Asymmetric Keys can be "hidden" from Users, the ability to encrypt using one or more keys can be prohibited.


SSRS table populating data into a field that should be blank

I found that using a Matrix instead of a table resolves the issue.


Oracle 12c, can not chose standard edition when install!

There was no Standard Edition released from version 12.1.0.1

Download 12.1.0.2 for Standard Edition.


MySQL: How to drop all tables starting with a prefix?

First do a

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '\_elgg%'
AND TABLE_SCHEMA = 'your_database_name';

and check if this returns the correct statements for every table. If you want to execute those statements, do

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '\_elgg%'
AND TABLE_SCHEMA = 'your_database_name'
INTO OUTFILE '/tmp/whatever_filename';

SOURCE /tmp/whatever_filename;

Another way: On the command line, to do it in one rush, you can do

mysql -B databasename -uroot -prootpassword --disable-column-names  -e `mysql -B databasename -uroot -prootpassword --disable-column-names  -e "SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '\_elgg%' AND TABLE_SCHEMA = 'your_database_name';"`


Oracle Data Guard:Can archived redo log files be sent from primary database to physical standby database manually?

Yes, you can manually script it.

This is covered in the documentation here.

In essence, you copy the archive log files to the correct filesystem location on the logical standby host, then register them using the ALTER DATABASE REGISTER LOGFILE command.


Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?

We had a similar issue with a query that was called from EntityFramework.
Was fast in SSMS, but slow from application.
It turned out there was an error in mapping of parameters, their type, which caused the query from application to do a scan, due to the query becomming non-SARGable.
After fixing this issue, the query was fast from application.


Service Broker to Send Messages Specific Database bases based on critiera

Service Broker conversations are between services, not between queues. You send from a service, to a service. A conversation is tied to exactly a pair of services and cannot change. The same service can participate in any number conversations, with any number of peer services. If you want to send messages to multiple peers, you need to start multiple conversations.


Executing SPs in parallel (maybe with a cursor)

You should consider Table-Valued Parameters and a new stored procedure that can take 1000s of invoices and deal with them as a set rather than the singleton insert you have now. Knowing how to write the procedure to deal with all invoices as a set would require first-hand knowledge of what the procedure does now with a single invoice.

I wrote up a quick article on TVPs here:

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

And Erland's article is useful as well:

http://sommarskog.se/arrays-in-sql-2008.html


edit:
Will delete this shortly or update based on answer to question. Realized OP mentioned he DID rebuild indexes already.

Shrinking a SQL Server database causes the data to get massively fragmented due to how the algorithm works.  You'll want to usually presize so you don't have to shrink but don't have to worry about growths too much either.  Always easier to grow a live DB with IFI than to shrink and reindex.

Paul Randal has a great post about this in depth here.  You will want to reindex and check your fragmentation prior and after using this script ripped off here:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc


How to compare MySQL privileges granted to privileges used?

This is a demanding task that may not give the exact answer. The only exact way is that you know requirement of applications that use that user privileges to access specific DB.

You must know: does that particular user administrate MySQL server? If not, remove privileges to 'mysql', 'performance_schema' and 'information_schema'.

Second step:
If you are uncertain which DBs are used, you may check logs.

There is useful log config that can be put in my.cnf: How to Log All mysql queries into log file?
Bear in mind that it will put additional load on your server, but it may give you info about usage.
Anyway, you will need to check logs for a week or more to be able to determine which queries are executed by this user and to determine which privileges are required. Bear in mind that some queries will be executed only sporadically, like some monthly tasks (usually procedures), that will not show up so easily.

The best way is to find out which applications use that user and to decide from that which DBs can be excluded from privileges given to that particular user.


How can we pass default value as null to Associative Array in Procedure?

Casting it as NULL should work fine:

DECLARE
  TYPE t_test IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;

  PROCEDURE test_me ( in_test IN t_test DEFAULT CAST( NULL AS t_test ) )
  AS
  BEGIN
    dbms_output.put_line( in_test.COUNT );
  END test_me;

BEGIN
  test_me();
END;


The full contents of the bind-variable are not displayed in SQL Plus

The length of a CLOB output is limited.
You can increase the limit with set long n, example:

set long 50000


How do I abstract a WHERE clause condition?

OK 5 years later, here's a possible solution.
If this is your database and you are allowed to change its structure, I would

Create 2 new fields; OldC and NewC.
Update all OldC values with the value of ColC.
When ready to calculate, update all NewC values with the new value.
When ready to migrate, update all ColC with NewC.

If during this time, you need to know whether ColC is the old or new value, compare it to the 2 new fields.


Isssue with Database Links converting OEM Job to DBMS_Scheduler Job

The only way to get around this was to use credentials in the database links definition. Not nice if you ask me but it is what I found. This was in 10g and 11, did not test with 12c.


MySQL replication can work over a WAN and is likely your simplest solution. My main concerns (other than running MySQL on Windows for something important enough to need failover) would be making sure the sites have enough bandwidth, and deciding whether to implement compression and/or encryption. I don't have a specific guide to recommend, but they are easy enough to find.

Windows multi-site clustering would be more difficult. You'll need block-level replication of storage and probably high throughput, low latency networking between sites if you want to fail over in a timely manner. Also, the MySQL installer does support Windows clustering now, but I don't know anyone actually running MySQL on a Windows cluster, and I don't know if it was tested on multi-site clusters.

Personally, if I were tasked with improving reliability of MySQL servers running Windows, I'd want to re-evaluate why Windows was chosen as an OS. Windows is perfectly capable of running highly available systems, but historically MySQL has had a number of deficiencies when running on Windows (bugs, resource usage limits, missing features and optimizations), and the MySQL development and support communities are far more active on Linux than Windows. This has improved somewhat since MySQL 5.5 when it was no longer compiled against the Windows POSIX API, but I'm not convinced that MySQL on Windows is at parity with MySQL on Linux. I know this isn't what you asked, but it's hard to honestly answer the question without bringing it up.


Here is the information on the CREATE INDEX statement for DB2. You may also want to check these links on designing indexes and space requirements for indexes as they also deal with your question on what factors into index page space.

Based on the last link it would seem to me that the page size for the index would be your column lengths + the index overhead. This may take you beyond the page size for your table. (Maybe that is what is happening here.) You could also try to place your table in a bigger tablespace. It may place the index in a bigger page size as well.

  EDIT: I have tested this locally as well to see. Using Control Center
  I was able to get a more detailed picture of the error message. Per
  DB2: "Explanation:

  The index could not be created or altered because the sum of the key
  column internal lengths would exceed the index key length limit. Also,
  an index cannot be created using a LONG VARCHAR, LONG VARGRAPHIC, or
  LOB column. If the index is defined on a typed table, there is
  additional index overhead that reduces the maximum length by 4 bytes.
  The index may be altered by an ALTER TABLE or ALTER NICKNAME statement
  that alters the data type of one or more columns.

  An index extension could not be created because the sum of the columns
  returned by the GENERATE KEY function would exceed the index key
  length limit.

  The index key length limit is based on the page size of the tablespace
  used by the index:

Max Key Length  Page size
--------------  ---------
1K              4K
2K              8K
4K              16K
8K              32K

  The statement cannot be processed. The specified index or index
  extension was not created or the table or nickname could not be
  altered.

  User response:

  To modify the index definition or alter column, eliminate one or more
  key columns and reduce the key length to the permitted maximum. For an
  index extension definition, specify a different GENERATE KEY function,
  or redefine the function to reduce the length of the returned row."

  As mentioned in my comment below your key size would be at least 32 +
  (4000 * 2 (for UTF-16)) + 2 (for varchar length identifier) + index
  overhead, so you are already at 8034+ bytes. This means just over 7K.
  I'm guessing you are not defining a tablespace, so it will default to
  USERSPACE1, which is an 8K tablespace), so you have a 2K max key length
  as shown by the table above, plus your error message). In order to make
  this key, you have to do one of two things

  Alter your table definition so it isn't so big (probably reduce your
  varchar size) so you can fit within a 1K max key definition. I'm
  guessing you don't want to do this so then you end doing the next one:
  Since your key space requires 8K key length, you need a 32K page size.
  You can leave the table in a default 4K tablespace, but the index MUST be in a 32K
  tablespace. If you are using
  automatic storage you could easily create a tablespace with the
  following:

create bufferpool BP32K immediate size 250 automatic pagesize 32K;
create large tablespace TS32K pagesize 32K managed by automatic
storage bufferpool BP32K;
create table line (
  field_key   integer not null,
  value       varchar(4000),
  ... ) index in TS32K;
create index key_value_idx on line (field_key, value);

  This will place the index in the 32K tablespace and you
  should be fine.


How? SQL Cluster with multiple active nodes (not failover)

You don't get "load balancing" with SQL Server. There are a lot of "High Availability" features including clustering.

That is, you can't have read/write on the same data with multiple SQL Server instances.

You can off load reporting onto a snapshot, or replicate data between Instances, or "shared scalabl" but none is full OLTP on all data on every node.

You scale up, not out, for SQL Sever

SO: SQL Server load balancing
SF: How to load-balance SQL Server 2008 for high-usage ASP.NET applications?


The following worked for me:

I ran through the command line wizard to set this up:
shell> mysql_secure_installation

Then entered the following:
shell> mysql
ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

Credit goes to this question/answer.


MySQL Workbench is not finding MySQL service?

I custom named the Windows service

This may seem a bit of an obvious question... but have you considered not doing that?


MySQL: How to get the maximum disposition and the maximum date from my database


Huge database logging of event type rows and ways to optimize it

We have something simular, ~5000 events per seconds. Tables are partitioned by month and we store 5 years of data. That gives us a 60 partitions, works fine. It runs on PostgreSQL version 9.1, works better than any older version when using partitions.

EXPLAIN and EXPLAIN ANALYZE are your best friends to get your queries right.


Ideal way to represent a Grade -> Section -> Student -> Marks database

Tie the marks to the student, the student to the section, and the section to the grade using foreign keys.  The join will usually be quick as long as you have your indexes set up to include those columns.

As far as making it easy to tie together you can create a view that ties them together so you only have to query one place to get what you are looking for.


Mysql 5.5: Optimizing all tables with mysqlcheck - is it useful to run the -check option along with -optimize?

Well, I can at least save you some time wondering about this.  The mysqlcheck utility only does one thing at a time.

sqlbot@dev:~$ mysqlcheck --check --optimize --all-databases --verbose
Error:  mysqlcheck doesn't support multiple contradicting commands.

So, if you want to do both, you'll need 2 runs.  While --optimize doesn't appear to first go through the identical motions to "check" the tables, it does rebuild them, which would imply that it has to be able to read them successfully.

I've never seen CHECK TABLE find a new problem with an InnoDB table, but if it does, InnoDB will shut down the MySQL Server, according to the docs... and I would think it likely that "shut down" means "force crash" to keep the corruption contained.  On the other hand, if you have tables that are corrupt, OPTIMIZE TABLE (in InnoDB) would likely "help you find" that corruption in much the same way.

Arguably, checking the tables before optimizing seems redundant, though I would welcome correction on this point; however, it also seems potentially wise in the face of such a large amount of data that you're about to rebuild, to invest the time running through the  less obtrusive "check" process before launching into the "optimize" process.

Under the hood, all mysqlcheck really does is connect to the server as a client and execute the corresponding SQL statements.


What happens redis uses all allocated memory?

Redis respects the maxmemory configuration directive. When set to 0 (the default), Redis will try to allocate as much memory as needed. Once it over does it, the OS will kill it.

Setting maxmemory to any positive value limits the amount of RAM that Redis will consume for data. When you reach the limit, Redis' behavior will depend on the maxmemory-policy configuration setting. The following is taken from the default redis.conf file and details the different evictions policies:

  volatile-lru -> Evict using approximated LRU among the keys with an expire set.
  allkeys-lru -> Evict any key using approximated LRU.
  volatile-lfu -> Evict using approximated LFU among the keys with an expire set.
  allkeys-lfu -> Evict any key using approximated LFU.
  volatile-random -> Remove a random key among the ones with an expire   set.
  allkeys-random -> Remove a random key, any key.
  volatile-ttl -> Remove the key with the nearest expire time (minor TTL)
  noeviction -> Don't evict anything, just return an error on write operations.


Can I Simply Change A Transaction Log From Unrestricted to Restricted?

I prefer restricting to unrestricted.

If some process goes awry, then I don't want the log file to grow and grow and fill the disk up. I'd rather the DB stopped working than the entire Server/SQL Server Instance.

However, in this case you have a different issue: most likely "FULL" recovery and no log backups. Change to simple, shrink the log to, say 500MB, allow growth at 250MB, set a maximum of 2GB.


Nested with - Incorrect syntax near the keyword 'WITH'

From documentation

  Specifying more than one WITH clause in a CTE is not allowed. For
  example, if a CTE_query_definition contains a subquery, that subquery
  cannot contain a nested WITH clause that defines another CTE.

Thus,

with PS as (
    WITH CS AS (
      SELECT      'ab' as a
    )
    SELECT * from cs
    ) select * from PS

gives you error. You can define multiple CTEs though :

WITH CS AS (SELECT 'ab' as a),
PS as (SELECT * from cs)
select * from PS


pg_stat_statements always uses the literal directory "pg_stat_tmp", even if the "stats_temp_directory" parameter has been changed to point elsewhere.

See this note from the source code:

  Location of external query text file.  We don't keep it in the core
   system's stats_temp_directory.  The core system can safely use that GUC
   setting, because the statistics collector temp file paths are set only once
   as part of changing the GUC, but pg_stat_statements has no way of avoiding
   race conditions.  Besides, we only expect modest, infrequent I/O for query
   strings, so placing the file on a faster filesystem is not compelling.

Are your tons of short-running queries all entirely different queries?  If they all normalize to a smaller set of queries (i.e. groups that differ only in supplied parameter values, not in SQL syntax elements or SQL identifiers) then they won't generate IO because pg_stat_statements only needs to update the in-memory counters, not the on-disk normalized query text.


restoring Postgres dump with tablespaces

Got it,

had to recreate the tablespace manually on target before restoring the dump.

Thanks anyway


"Are there any advantages to remove all this tables and use the
  following Users table instead"

It depends.  Are you talking about a lot of rows?  Or just a few?  How about the number of different data fields in a "People" document?  Parsing something like that (for a lot of fields) is not going to be easy or performance-friendly.  Especially if the fields are not consistent across the documents.

  "Where people are document with a special structure, for
  example, JSON or XML?  This database is not truely relational"

Given these statements (and others) it almost sounds to me like this particular instance would be better served with a (NoSQL) MongoDB solution.  Essentially, you could have a "People" database and save collections of "Parents", "Friends" and "Colleagues."  MongoDB stores documents in collections as BSON, or a binary-encoded serialization of JSON.

Here's a link to the MongoDB site which explains a little about BSON and MongoDB.  Hope this helps.


Why don't you use an OR condition?

select *
from sip_trunks
where resporg_account_id_id = 62
  and (   sip_trunks.default = true
       or identifier = (select action
                        from sip_trunk_numbers
                        where number = 'xxxxxxxxxx'
                        and resporg_id = 80
                        limit 1))

limit 1

If you need to know which condition matched, you can use a UNION:

select *
from (
  select st.*, 1 as source
  from sip_trunks st
  where resporg_account_id_id = 62
    and identifier = (select action
                      from sip_trunk_numbers
                      where number = 'xxxxxxxxxx'
                        and resporg_id = 80
                      limit 1)
  limit 1

  union all

  select st.*, 2 as source
  from sip_trunks st
  where resporg_account_id_id = 62
    and sip_trunks.default = true
  limit 1
)
order by source
limit 1

If source equals one in the result the first query matched, otherwise the second.

Alternatively you could use an outer join:

select st.*,
       stn.action is not null as identifier_matched
from sip_trunks st
  left join (
    select action
    from sip_trunk_numbers
    where number = 'xxxxxxxxxx'
      and resporg_id = 80
    limit 1
  ) stn on stn.action = st.identifier
where resporg_account_id_id = 62
  and (stn.action is not null or sip_trunks.default = true)
limit 1

Note that limit without an order by usually makes no sense.


Oracle XE/SQLPlus query display more items

In SQL*Plus, you can change the pagesize

SQL> set pagesize 100

will repeat the headings every 100 rows rather than every 15.  You can set the pagesize value to (just about) whatever you'd like.  If you want to ensure that the headers are only written once, you can set it to a number much greater than the number of rows that the query will return.


Somehow this was a VIEW that turned into a TABLE?

I checked; the max of 16 columns per index has existed for a 'long time' and applies to 'all' engines.  Reference. I can't explain your specific question.

Also, I am thinking there are worse problems than the failing UNIQUE.

It is unreasonable to normalize something like "location", while also including all the fields in this table:

location_id,city_id,region_id,dma_id,country_id,continent_id

That is, there should be a different table with all of those columns, but only location_id in this table.

I recommend you manually remove the UNIQUE line, load the data, then clean up messes such as location_id.


Tracking down unused Views and Stored Procedures

For the unused stored procedures you can make use of dmv's especially sys.dm_exec_procedure_stat.

To find unused stored procs, please see the answer as explained by Aaron Bertrand.

Also, there is an good read to identify as explained here in the article
 How to find unused Stored Procedures in SQL Server 2005/2008

To tack down unused views:

a) Generate a List of non-recently-used code (by querying the plan cache).

b) Script the Views/Sprocs/etc that aren’t in cache (for whatever reason) using ALTER scripts. (i.e. the SSMS ‘Modify’ option when right-clicking on an object).

c) Execute the ALTER script generated by SSMS in production. Since the object wasn’t in cache, there’s no harm and no foul in worrying about recompilation overhead. And since the script is an ALTER (not a DROP/CREATE), permissions are retained intact.

d) But, if the object references invalid objects, it’ll throw an error and I can definitively know that it’s not only NOT used, but one of the culprits that’s causing me grief during replication snapshot procedures.

Also read as mentioned by Jonnathan@ here

  Unfortunately if you want to be 100% accurate, the plan cache isn't
  going to cut it, because there are all types of scenarios in SQL
  Server where a plan may not get cached at all. For example,
  OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and
  single use plan stubs, etc. Since you want a method that works across
  2000-2012 your only real option to guarantee you catch the usage is to
  use a server-side trace with the Audit Database Object Access event:


Update value in same view using CASE expression

Schematically

WITH
cte1 AS ( SELECT *, LAG(state) OVER (ORDER BY datetime) AS prev_state
          FROM sourcetable),
cte2 AS ( SELECT *, CASE WHEN state = prev_state THEN 0 ELSE 1 END AS state_changed
          FROM cte1 ),
cte3 AS ( SELECT *, SUM(state_changed) OVER (ORDER BY datetime) AS group_number
          FROM cte2 )
SELECT *, COUNT(*) OVER (PARTITION BY group_number) AS group_count
FROM cte3

For multiple entities add proper partitioning / partitioning level.


Query for a month works faster then for a week

(Some of this is redundant, but I will spell it out anyway.)

I think 5.6.23, with MRR, is smart enough to use all 3 columns of INDEX(site_id, type, created_at) in that order.  (Older versions would not work as well.)

If you are using a 'range' of created_at values, nothing after that column will be used in optimization.  (Unless it is a 'covering' index.)

Instead of using OFFSET, "remember where you left off":  pagination blog.

With 170M rows, you should consider tightening up the datatypes wherever possible.  For example, can type changed to a 1-byte ENUM or normalized to a 1-byte TINYINT UNSIGNED (1 byte) instead of VARCHAR(25)?  subtotal decimal(25,2) takes 11 bytes.  If you don't need the precision of DECIMAL, consider an 8-byte DOUBLE.  Are there more than 16M sites?  If not, consider a 3-byte MEDIUMINT UNSIGNED instead of a 4-byte INT SIGNED.  Etc.

In place of USE INDEX.. this might be safer: IGNORE INDEX(PRIMARY).


Why is there ASYNC_NETWORK_IO wait type on Shared Memory connections?

As far as SQL Server is concerned, whenever results are going out the door, it's ASYNC_NETWORK_IO whether it's shared memory or TCP/IP.


The array_to_json function with a cast to jsonb accomplishes this.

Example: select array_to_json(ARRAY['{"id": "1"}', '{"id": "2"}'])::jsonb;


MySQL create index with LOCK=NONE still locks a table

Looks like I have a clue why it doesn't work

Our table have the following constraint:

CONSTRAINT `rpush_notifications_event_id_fk`
  FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE

Next, I found here one interesting thing:
https://blogs.oracle.com/mysqlinnodb/entry/online_alter_table_in_mysql

  Online operation (LOCK=NONE) is not allowed in the following cases:

  when adding an AUTO_INCREMENT column,
  when the table contains FULLTEXT indexes or a hidden FTS_DOC_ID column, or
  when there are FOREIGN KEY constraints referring to the table, with ON…CASCADE or ON…SET NULL option.

so looks like we have a 3rd case


Defining a GIN index across two columns/arrays with similar data

Since you want to run the query on metadata->'emails', you need to build the index on that expression:

create index on contacts using gin ((metadata->'emails'));

Alternatively, you could make the index on metadata itself, and re-formulate the query condition like this:

... OR metadata @> '{"emails":[{"email": "my@email.com"}]}'

In either case, you also need an index on the column "email", to satisfy the other branch of the OR condition.  Then the two indexes will be combined with a BitmapOr operation.

It is possible you could make one index on both columns using some complicated expression index, but that doesn't really seem worthwhile.


Cleaning up bad data with REGEXP_REPLACE too aggressive with non latin chars

Given proper lc_ctype (not C) and encoding (especially not SQL_ASCII), character classes could be used in regular expressions to include accents without having to specify ranges.

Example:

=# show lc_ctype;
  lc_ctype
-------------
 fr_FR.UTF-8

=# show server_encoding ;
 server_encoding
-----------------
 UTF8

=# \set raw_text '- Snälla du! Kan du sätta? : om vägledni i förskolan'

=# select regexp_replace(:'raw_text', '\w', ' ', 'g')
                    regexp_replace
------------------------------------------------------
   Snälla du  Kan du sätta    om vägledni i förskolan

If you wanted to keep punctuation signs:

=# select regexp_replace(:'raw_text', '[^\w[:punct:]]', ' ', 'g');
                    regexp_replace
------------------------------------------------------
 - Snälla du! Kan du sätta? : om vägledni i förskolan

Another possilibity to clean up could be to filter out non-printable characters:

=# select regexp_replace(:'raw_text', '[^[:print:]]', ' ', 'g');
                    regexp_replace
------------------------------------------------------
 - Snälla du! Kan du sätta? : om vägledni i förskolan


The problem comes from using Aginity, since this client applies that the default path is $user, public.

I've tried it in SQL Workbench/J and it works without problems.


You don't need the long CASE, use MySQL's CEILING function - https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_ceiling
What is the data type for the rating column? My guess is that it's a FLOAT, and being an approximate, your actual values fall within the ranges that aren't covered by the CASE , 2.00001 for example. Change it to DECIMAL to avoid similar issues.
When using CASE, it's a good idea to always include an ELSE clause. Try adding ELSE -1  or other value just to troubleshoot this issue, and you will see for yourself.

HTH
UPDATE - I stand partly corrected. While my answer and points are still valid, I believe that @RickJames and @RolaondoMySQLDBA were right for this case as well.
According to IEEE 754 these number do happen to have exact representation as FLOAT, and the problem was your CASE. Using CEILING is better than CASE and IF for this, but to understand the root issue see @RolandoMySQLDBA Answer below, and @RickJames comments.
UPDATE 2019-07-21 18:03 EDT (RolandoMySQLDBA)
In all fairness, CEILING() does make the most sense.
I ran this
SELECT CEILING(rating) ratings,COUNT(1)
FROM user_review GROUP BY CEILING(rating);

on the sample data and got this
mysql> SELECT CEILING(rating) ratings,COUNT(1)
    -> FROM user_review GROUP BY CEILING(rating);
+---------+----------+
| ratings | COUNT(1) |
+---------+----------+
|       5 |        3 |
|       3 |        2 |
|       4 |        2 |
|       1 |        2 |
|       2 |        2 |
+---------+----------+
5 rows in set (0.00 sec)

So, CEILING() is the most concise in this case. +1 to you !!!


This would have a considerable impact on your system, but you could enable the MySQL General Query Log by running the following:

SET GLOBAL general_log = 'ON';

The default setting for the logs will output to a file that can be identified by running this:

SHOW GLOBAL VARIABLES WHERE Variable_name = 'general_log_file';

Otherwise, you can change the setting to log in a table with the line below, and then you would be able to run queries on the table mysql.general_log

SET GLOBAL log_output = 'TABLE';


The problem with su postgres command, you changed the user to posgress but bash (to be more specific environment) owner still root or the original user, in-order to fix this issue use su - postgres instead of su postgres


NDF is secondary data file, By default it will not be created unless we create the file while creating SQL server database or manually create it after database is created using AlTER DATABASE statement. SQL server by default will create one primary data file and one log file. If it is not visible in sys.master_files means you have not created it.

Read more about database files and filegroups

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

To add secondary data file please see

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


How to involve table index in filtering of 3 conditions?

You have a problem with correlations of the expressions.  The cases where field3 = 1 are selectively depleted of cases where ((field1 < field2) OR (field2 < 0)).  PostgreSQL thinks that those are independent, but they aren't.  So it thinks you will very quickly find a qualifying row by doing a seq scan.  But instead it had to dig through 4 million before finding the first one.

A quirk of PostgreSQL is that it collects special statistics on functional indexes, but not on plain partial indexes.  So if you add a dummy function to your partial index, you may get a better plan:

create index on mytable (abs(field3)) WHERE (field1 < field2 OR field2 < 0);

And then your query would have to use abs(field3)=1 in the condition.  Of course you can only do that if field3 is never negative, otherwise pick some other dummy function to use.


How to auto start instance of sql server 2012 localdb on startup?

Create a Windows Service program that uses the instance.  Auto start your program.

Or, use SQL Server Express, which is also free, and can be setup to run at startup - this is the default behavior.


Recovering Mysql - how to get a dumpfile from wampserver and none running db

I'm a bit lost in your explanation, but there may not be much of a problem.

You have a dump from December?
But it is in the 'wrong' directory?
The dump was taken using mysqldump?

Reloading is nothing more than
mysql -uroot -p


Your problem might be related to issuing a USE inside of a cursor. Instead, I would suggest using the following approach, so you can get rid of the cursor.

EXEC sp_MSforeachdb
'USE ?
 IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
 BEGIN
      IF EXISTS(SELECT 1 FROM sys.indexes ind WHERE object_id = OBJECT_ID(''dbo.TestTable'') AND name = ''Test'')
    PRINT ''Exists in '' + DB_NAME()
ELSE
    PRINT ''Not Exists in '' + DB_NAME()
END'

Please note that technically, sp_MSforeachdb is an undocumented feature but it simply works.


Why is an empty XML node xml datatype not being recognized in this query?

In (td//text())[1] the predicate [1] will return the first node returned by the XQuery (td//text()).

You are saying that you want the first text() node. But in the first td element there are no text nodes present so you get the text node for the second td element which is the first text node returned.

You need to specify the position predicate on the td node instead.

This will give you null in col1

select
     x.c.value('td[1]/text()[1]', 'varchar(1024)') as col1
    ,x.c.value('td[2]/text()[1]', 'varchar(1024)') as col2
    ,x.c.value('td[3]/text()[1]', 'varchar(1024)') as col3
from @x.nodes('/root/tr') x(c)

And not specifying the text() node will give you an empty string in col1.

select
     x.c.value('td[1]', 'varchar(1024)') as col1
    ,x.c.value('td[2]', 'varchar(1024)') as col2
    ,x.c.value('td[3]', 'varchar(1024)') as col3
from @x.nodes('/root/tr') x(c)


Mysql Getting Access denied for user 'root'@'localhost' (using password: YES) constantly in log

A) You can check TCPdump output for MySQL port connection.

B) Also you can verify whether any script is running on MySQL server may be something like monit etc.. which continuously pings MySQL port.


Dealing with disk space full in postgresql

Because PostgreSQL must write WAL before making any changes to tables, it needs free disk space in order to delete things and release more disk space.

If you let the disk fill up, you can't recover from within PostgreSQL. Even TRUNCATE still has to write to WAL.

So you must free some space on the volume, or expand the volume. If your PostgreSQL log files are in pg_log in the data directory, you can safely remove some of those and restart Pg.

Do not delete pg_xlog or pg_clog. These are not server error logs, they're critical parts of the database, the transaction log and commit log.


Where to start learning to become a successful Oracle DBA

Start with the Oracle Concepts Guide (pdf). It will give you a good overview of everything related to Oracle databases and will give you a framework in which to place everything else you learn about Oracle.  While you will by no means be a DBA after reading the guide, this is where you should start.


import TSV file into MySQL table with SET datatype column

The answer appears to be yes.

Set members are accepted if they are separated by comma, however there should be no space between the comma and the next set element.

Since commas are used to separate set members, columns must be separated by a different character. I have used tabs, and this works well.

Performance is greatly improved (roughly by a factor of 10-20), compared to joining with another table holding the set values.


SQL Server 2008R2 DBCC CheckDB never finishing

There is a very interesting post on DBCC CHECKDB on SqlPerformance, Minimize impact of checkdb. This will give you several ideas, like testing your backup on another server (therefore offloading the charge), using several options (you can experiment with PHYSICAL_ONLY for example).

As CHECKDB is making a big use of tempdb, this you check how things are going on for this database? Is it on separate disk?


Extracting files from partitioned database

As I understand it, following the merge each partition will have its
own mdf and ldf files and these will contain all the data for one
customer.
...
My question is, given that I can get the files for a single customer
from the database server is it possible to copy them to my development
SQL Server and create a database for that single customer?

That is wrong.
There are no "partitioned databases" in SQL Server, there are partitioned tables and partitioned views. I suppose you tell us about partitioned tables.
Normally, every database has ONLY ONE log file, and even if for some reason some database has more than 1 log file, it has nothing to do with partitioning. So one more time, you misunderstand partitioning.
Partitions are NOT independent databases within your database, you cannot get out one partition by just copying the corrsponding .ndf file. You never can attach a single ndf file, it will never succeed.
And even if you'll try to make partial backup/restore you cannot restore database from one ndf backup without restoring PRIMARY filegroup


MariaDB: Insert records (list of tuples) into table if not exists

Your SELECT with two select can be JOINed or UNIONìted
As you want to insert two rows, you need UNION like this
INSERT INTO `test` (col1, col2)
SELECT * FROM
((SELECT * FROM (SELECT '1','2') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='1' AND col2='2'
) LIMIT 1)
UNION
(SELECT * FROM (SELECT '3','4') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='3' AND col2='4'
) LIMIT 1)) t1;

I don't see why it should give you an error
INSERT INTO `test` (col1, col2)
SELECT * FROM
((SELECT * FROM (SELECT '1','2') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='1' AND col2='2'
) LIMIT 1)
UNION
(SELECT * FROM (SELECT '3','4') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='3' AND col2='4'
) LIMIT 1)
UNION
(SELECT * FROM (SELECT '9','9') AS tmp  WHERE col1='9' AND col2='9')
) t1;


TempDB is no different than any other database.  All actions must be logged, and log space will only be reused once the log records for a given transaction have either been committed or rolled back.

As @KrisGruttemeyer said in a comment, you can find out what type of action is causing the transaction log to grow by looking at:

select d.name, d.log_reuse_wait_desc
from sys.databases d
where d.name = 'tempdb';

This will likely show you either 'active transaction' or 'nothing' for tempdb.  If you see active transaction you know some transaction is open and has not been committed yet.  You can see what transactions are open by looking at various system DMVs including, sys.dm_tran_active_transactions, among others.

For a much more in-depth look at what is causing your tempdb to grow, check out this question.


Best practise for copying a database from production

SSIS could certainly take care of this for you but as with many things there are several ways to complete the task.

1) You have several options within SSIS to complete the copy process, Copy Database is one of them. You could backup the database and restore it using a SQL Script. You may need to use the file system task to move the backup file but that depends on the configuration of your infrastructure.

2) Easily taken care of in SSIS by executing a no. of scripts against a target database.

3) Again, easily completed by running a SQL script against the target server.

4) You could use package configurations to specify a different environment/connection strings very easily.

I hope that's some help.


If foreign keys/cascade deletes are bad, why use a database-server with that feature?

Lets start with your first link. It says clearly:

  Working on large databases with referential integrity just does not perform well.

And that is right. Just you likely have no clue that "large database" is terabyte size with billions of rows in a table. A simple select may cascade into hundreds of millions of related elements to be deleted, and then you have a performance problem.

This is a non-issue for regular small databases such as a wordpress log or most CMS - it turns into a problem if you do something like facebook, or handle financial simulation data. I regularly deal with multi billion row tables and deletes that work in stored procedures outside of transactions in batches of x - because the end delete may easily clean up some hundred million rows.

  Will they benefit going away from MySQL to a NoSQL kind of server?

Hardly. They are useful when a professional uses them as appropriate.

  On other hand, can applications coded in a way to make use of foreign keys feature without
  running into issues?

Yes.

  Is noSQL/redis better if we are using database only for storage and managing "relations"
  at application/script layer?

I once did a application review for a technology upgrade in a bank that uses no referential integrity (to get the performance up). Loading the data into SQL Server (which was supposed to replace their aging Adabas installation) it failed with integrity constraint violations. Happens 40% of the historical records were invalid because some * had deleted lookup table values not in use any more (such as old customer classification codes, which got replaced for all active customers, just not the old ones). No referential integrity warning ever came up. The result was some fired people and a problem stuck in workaround and a partially useless data warehouse build on top.

So much for managing relations at application / script layer. Errors WILL happen. Data is valuable, applications change.

Most people complaining about SQL level features would be more advised to read a book about them, and try to understand them, instead of complaining. Sadly a lot of the advice on the internet is written by people that refuse to read even documentation. Always be careful with that. Most advice to NoSql is based strongly on ignorance.


unenforced constraints/possible duplication in my database design?

Your model includes referential cycles.  This comes up quite a bit when you're modeling and you haven't worked through the differences between what relationships exist and what relationships are important to your system.

Without knowing more about your business rules and their justification, I would suggest that you consider dropping teachers.topic_id and possibly also teachers.subject_id.

Your lessons table relates subjects and topics.  Since you have a many-to-many relationship between teachers and lessons, you can use lessons (via teachers_and_lessons) to tell you which teachers can teach which topics and which subjects.

I doubt that neither teachers.topic_id nor teachers.subject_id are correct anyway, since this would imply that any given teacher can only teach one topic and one subject.  I don't know about your school, but it sounds much too specialized to me!

Edit: Recognizing Teaching Qualifications

Based on a comment by OP (below) the question is how can a teacher be recognized as being qualified to teach a topic without there being an actual lesson assignment made (yet)?

Issue 1: The relationship of topics to subjects:

It isn't clear from the original question whether topics belong to one subject or possibly to multiple subjects.  If topics (e.g. fractions) belong to only one subject (e.g. math) then there should be a foreign key like so: topics.subject_id.  If this were so, then lessons.subject_id would be redundant and could also be dropped.  This would simplify the model and make things much clearer while reducing the risk of inconsistent foreign keys.

Issue 2: What teachers can do vs. what they are doing:

The teachers_and_lessons table shows which lessons teachers are actually teaching.  Before this assignment is made, OP would like to be able to record which topics a teacher is qualified to teach.  This could be done with a second M:N intersection, like so:

teacher_qualifications:
(teacher_id (PK/FK), topic_id (PK/FK))

This design assumes the simplification suggested under Issue 1, above.  One potential drawback of this simplistic solution is that there is no constraint against assigning a teacher to teach a lesson for which they are unqualified.  This constraint, if it is important, could be accommodated by replacing the teacher_qualifications and the teachers_and_lessons tables with new tables as follows:

teacher_qualifications:
(qualification_id (PK), teacher_id (FK,UN1), topic_id (FK,UN1))

lesson_instructor:
(qualification_id (PK,FK), lessons_id (PK,FK))

Again, these tables assume the simplification from Issue 1.  Even without that simplification, these tables could be amended to use an analogous approach.


SSDT Drop and Recreate Tables when nothing has changed

After a fair amount of frustration, finally found the cause, and posting as an answer as it may help others....

Finding the reason for why SSDT thought the scheme was different I think is the first point of call. SQL Scheme compare is your friend here. (In VS under Tools => SQL => New Scheme Comparison...)

Firstly thanks to @jadarnel27, was very helpful so upvote dude cheers, but wasn't the answer I'm afraid.
Computed column definitions and constraint definitions were definitely candidates, as was potentially column ordering. Ironically I did have one issue with a computed column but it was not the definition, it was the fact that I didn't add NOT NULL to the end of the column definition (which is the default of course) but SSDT saw that as different everytime.

So I had to change a column
from

[ColumnName] AS (CONCAT(Col1,' ',Col2)) PERSISTED

to

[ColumnName] AS (CONCAT(Col1,' ',Col2)) PERSISTED NOT NULL

then it stopped constantly dropping and re-creating the column. This showed up in Scheme Compare, not in the definition of the computed column.

Secondly, because we were using replication, SQL was adding NOT FOR REPLICATION to most of the tables that were being used in replication (reasons for is to do with the works of replication) but in essence was changing a tables DDL
from

CREATE TABLE [dbo].[Whatever]
(
    [WhateverId]    INT IDENTITY(1,1) NOT NULL
    ...etc more columns
)

to

CREATE TABLE [dbo].[Whatever]
(
    [WhateverId]    INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
    ...etc more columns
)

again this showed up on Scheme compare......

and to fix, either add NOT FOR REPLICATION into the DDL source code in VS for all tables required or under the publish settings -> Advance -> Ignore tab and scroll down a bit, tick the box as below:

and then everything was disco


Frustrating SQL Server and VS Express 2013 Issue

Sql Server DB verion 782 is for SQL Server 2014 and 706 is for SQL Server 2012.

and SQL Server 2014 will support all the previous version, but SQL Server 2012 will not support DB version 782(SQL Server 2014 version)

Probably something wrong with installation of SQL server 2014 and you still have SQL server 2012 version, Please check the SQL server version

SELECT @@version


Yes. Someone who has administrative rights (aka admin rights) to the SQL instance can change this by using SQL Server Management Studio.


Oracle: Decreasing PCTFREE on large table

My question was answered on another message board. The answer is to use DBMS_REPAIR.SEGMENT_FIX_STATUS after the change in PCTFREE.
On my platform (sparc64) it's quite buggy, though, with lots of ORA-600 when repairing individual blocks. But repairing all blocks seems to work better for some reason.


Mysql slow query log always include "# Time:"

A lot of things have changed in the slowlog over the versions.  Discover the inconsistencies and code to them!

# Time (and maybe some other things) are specified only when they change.

In 5.7.2 Time will include microseconds.  This is one of many syntax changes that have happened over the years -- be careful.  Percona and MariaDB also have different things that can show in the SlowLog.

Query time used to be just an integer.


Transfer data from MS SQL to PostgreSQL with SSIS ODBC destination

Isn't this a case for a tools rethink ?
How about BCP from SQL Server to write out a csv text file & Bulk Load on the PostgresSQL side to do the load ?


Is parallel execution enabled or disabled by default in Oracle Standard Edition database version 12.1?

You say that VLDB and Partitioning Guide, Release 12.2

  About Initializing and Tuning Parameters for Parallel Execution

  Oracle Database computes defaults for the parallel execution parameters based on the
  value at database startup of CPU_COUNT and PARALLEL_THREADS_PER_CPU.

Parameters are described in the Database Reference.

But a lot of features are not possible in the Standard Edition. Either they cannot be enabled or are not allowed. Some of the features of Enterprise Edition need extra license if you use them.

Most of the parallel features need Enterprise Edition but no additional license
Partitioning needs Enterprise Editition and and additional license

More about which edition has which feature can be found in the Licensing Information User Manual


SELECT count vs DELETE JOIN. Why/how are they different?

The reason the count differs is due to multiple rows being present in tableA that have a foreign key reference to the same row in tableB.

When using an inner join the SELECT produces a separate row for each of the records in tableA.  The DELETE statement is only affects tableB.  As mentioned in ypercube's comment, if you were to do a distinct count, the numbers would match.


Automating SQL scripts in Toad for Oracle (13.1)

DBMS_SCHEDULER is your friend https://docs.oracle.com/database/121/ADMIN/scheduse.htm
  and  https://oracle-base.com/articles/10g/scheduler-10g – Philᵀᴹ Oct 21 at 19:11

Also, I recommend you use SQL Developer instead.  That software has a GUI interface for creating the scheduled jobs to run.


Query for returning query plans from sql plan cache that contain 1 or more warnings?

RedGate has a great article Checking the Plan Cache Warnings for a SQL Server Database on how to query the plan cache to find plans with warnings.
Ultimately the warnings will be stored in the XML query_plan column of sys.dm_exec_query_plan DMV.


Grabbing SQL Dump of Master DB Without Downtime

When seeking a dump which provides binlog position you will instruct MySQL to call Flush Tables With Read Lock. The acquisition this lock can block. This where you could impact the system. If you have long running transactions you may see problems but you can check this before starting the dump. Your command looks fine to me.


Create a job that sets Auto Update Statistics on

You need to have a check using:

SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on
FROM sys.databases

For example:

IF
(
    SELECT COUNT(*)
    FROM [sys].[databases]
    WHERE [name] = 'AdventureWorks_2005'
    AND [is_auto_create_stats_on] = 0
) = 0
BEGIN
    ALTER DATABASE [AdventureWorks_2005]
    SET AUTO_UPDATE_STATISTICS ON
END
ELSE
PRINT 'The auto update stats is ON ... no need to worry !'


reseed auto_increment to specific number?

This works, does that answer your question?

create table dbo.test (id int identity(1,1), value int);

declare @indexmaxt int = 9999,@indexmaxt2 int;
SET @indexmaxt2 = @indexmaxt+1;
DBCC CHECKIDENT ('dbo.test' , RESEED, @indexmaxt2);

insert into dbo.test(value) values(1);

select * from dbo.test;

Results:
id  10000
value
1


What to check before attempting a read-scale replica log backup?

Log backups are important.  If they are scheduled to be run every 15 minutes, for example, that means they need to run every 15 minutes in order to meet you RPO.  Although it's nice to have the secondary running log backups, it is not nice if the log backups fail, and then you have a catastrophe at the primary site and you don't have recent log backups--you will not meet your RTO.

I would recommend that you run the log backups on the primary if possible, and have them copied to another location at the primary site, and also to the secondary's site.  This way you have the log backups available at the primary site, and this covers you in case you have a problem with the primary server, but the site survives.  If the entire site goes down, you have the latest log backups possible, given the network instability, at the secondary site.

I would inform management that due to the network instability, you cannot guarantee the RPO at the secondary site, and have them approve whatever plan you implement.


If the esg user was already used for replication, you shouldn't need to grant privileges again.  Login to the master server with he esg user, and execute show grants.  This will confirm whether or not the esg user has REPLICATION SLAVE privilege.

If you don't already have it, get the root password set to something you know.  That will be helpful for troubleshooting and setup/resync.

If you are unsure of the replication state, execute SHOW SLAVE STATUS and check the fields SLAVE_SQL_Running and SLAVE_IO_Running.  If either of those are NO, then check the status for Error messages and #'s.  If Replication is down, then that may be a cause of missing data.

If your SLAVE threads are running, check Seconds_Behind_Master.  If that is not 0 (or something relatively close to 0), then your slave is behind, and that could be the missing data.

If the slave is up to date, and your missing some data, another possible cause is that another user with write privileges accidentally inserted/updated/deleted on the Slave instead of the master.  Yes, this does happen.

Another possibility is data inconsistency from using Statement Based Replication, as opposed to Row Based Replication.  Check show global variable like 'bingo_format' to determine if you're using statement or row based replication.

Beyond that, you might need to break your question down a bit more to understand what you're doing (rebuilding rep, vs restarting) and focusing in on one piece at a time.


As you have foreign keys in your tables so you can't go with partitioning directly.As Partitioning does not support foreign keys in MySQL.

You may go with partitioning but then you will have to create the work around for that.you have to ensure your integrity by using triggers for (INSERT/UPDATE/DELETE) on tables.

i.e Insert into the Child table will only be allowed if the corresponding record exist in Parent table.
You have to maintain all these integrity(INSERT/UPDATE/DELETE) by using triggers.

Then you can create the partitioning on table's on a quarter basis and then drop the  partition.

for creating the .sql files you may use --where clause with mysqldump it will create the backup in .sql file format.

You can also create the csv's for your old data for the future reference.


You can use the function pg_get_constraintdef(constraint_oid) in a query like the following:

SELECT conrelid::regclass AS table_from
     , conname
     , pg_get_constraintdef(oid)
FROM   pg_constraint
WHERE  contype IN ('f', 'p ')
AND    connamespace = 'public'::regnamespace  -- your schema here
ORDER  BY conrelid::regclass::text, contype DESC;

Result:

 table_from | conname    | pg_get_constraintdef
------------+------------+----------------------
 tbl        | tbl_pkey   | PRIMARY KEY (tbl_id)
 tbl        | tbl_col_fk | FOREIGN KEY (col) REFERENCES tbl2(col) ON UPDATE CASCADE
...

Returns all primary and foreign keys for all tables in the given schema, ordered by tablename, PKs first.

The manual about pg_constraint.

The manual about object identifier types (regclass,regnamespace, ...).


Check the Oracle 11g R2 installation type

Are you talking about the Oracle Client?

In that case you can review the installation logs in the Oracle Inventory directory and look for the INSTALL_TYPE property:

INFO: INFO: ---------------------------------------
INFO: INFO: Starting to setup InstallClient
INFO: INFO: Initializing setup InstallClient
INFO: INFO: Session Details :
INFO: ---------------------------------------------------------------------
INFO:  PROPERTY                                               VALUE
INFO: ---------------------------------------------------------------------
[...]
INFO:  INSTALL_TYPE                                           Administrator


Problem creating subscriptions on Replication Server version 15.7.1

The error message indicates you're using a WarmStandby (WS) setup, in which case there are no subscriptions required (nor supported), period.

If you're trying to use the WS pair as a source for replicating into a 3rd db, then the repdef will need to reference the WS logical pair as the primary db.

If you're trying to replicate into the WS pair from a 3rd db, then the subscription will reference the WS logical pair as the replicate db.


How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"?

Simply run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p
Enter password:
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

Trivia: If you want to know what the HY00 stands for (a follow up I asked years later)....


The SELECT permission was denied on the object 'syscolumns', database 'mssqlsystemresource', schema 'sys'

As the error indicates the login does not have SELECT permissions on the sys schema. This would require you to specifically grant SELECT to the login, or I believe you could also just GRANT VIEW DEFINITION for the database.

Update
Sorry just caught that last sentence. I have not worked with that software but seems it might be using a different login to execute the query if you can execute the same query fine in SSMS.

On a side note, since you are in SQL 2005, it would be a better practice to start using the new views for looking at catalog information.


How to find SQL Server database is in use or not?

In order to use a database any session must first obtain a lock on it, usually a shared lock. So simply look for such locks:

select *
from sys.dm_tran_locks
where resource_type = 'DATABASE'
and resource_database_id = <dbid>;


Literal does not match format string error in oracle

Oracle doesn't understand the literal date format strings, as they are not in the same format that your client is configured for. You need to use the TO_DATE function to tell Oracle the format of the date string you are presenting it with, like so:

AND (ACTIVITY.ACTIVITY_START_DATE >= TO_DATE('2014-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS'))
AND (ACTIVITY.ACTIVITY_START_DATE < TO_DATE('2014-02-06 00:00:00','YYYY-MM-DD HH24:MI:SS')));

You could also represent your dates using ANSI syntax (seeing as they have no time component):

AND (ACTIVITY.ACTIVITY_START_DATE >= DATE'2014-02-05')
AND (ACTIVITY.ACTIVITY_START_DATE < DATE'2014-02-06'));


Index conditional aggregate query on PostgreSQL

You'll have to create a special table to contain the sum.

Then create triggers on the original table that add values that are inserted, subtract values that are deleted, do both for an update and set the value to zero on truncate.

You can see a small example of that for the count aggregate in my blog post. Different from that example you will have to have a summation table with one sum per "PlayerId" and status.


Speed up LEFT JOIN with OR operator in ON clause

Change LEFT JOIN to JOIN unless you really need LEFT.
Clean up things like ( sr.supplier_id = su.supplier_id AND sr.supplier_id = su.supplier_id )
Change the OR into a UNION (with a lot of rewriting)
Provide SHOW CREATE TABLE (so we can see if you have suitable indexes)
Don't leave out the rest of the conditions -- they may be part of the problem.
Improve the prettyprinting, perhaps

like this:

SELECT  *
    FROM  supplier_responses sr
    LEFT JOIN
        (
        SELECT  *
            FROM  supplier_updates su
            LEFT JOIN
                (
                SELECT  supplier_id supplierId,
                        coalesce(internal_id, supplier_id) latest_id,
                        Max(timestamp) latest_timestamp
                    FROM  supplier_updates
                    GROUP BY  supplier_id, supplier_id
                ) suLatest  ON suLatest.supplierId = su.supplier_id
                         AND  suLatest.latest_timestamp = su.timestamp
                         AND  suLatest.latest_id = coalesce(su.internal_id, supplier_id)
        ) su  ON ( sr.supplier_id = su.supplier_id
              AND  sr.internal_id = su.internal_id
                 )
      OR  (        sr.supplier_id = su.supplier_id
              AND  sr.supplier_id = su.supplier_id
          ) --
    WHERE  some clause...
    ORDER BY  su.timestamp_at ASC;

Perhaps a composite index like INDEX(supplier_id, internal_id) would be beneficial.


How to do aggregate functions in GROUP_CONCAT

Your query (the one that doesn't work) is:

   SELECT
      tsf.Admno,tsf.AdmissionFee,tsf.SplFee,
      tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,
      tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,
      tsf.Miscell,ta.StdNm,
      SUM(
          tfc.AdmissionFee,tfc.SplFee,tfc.TutionFee,
          tfc.ComputerFee,tfc.Stationary,tfc.Transport,
          tfc.Hostelfee,tfc.Lab,tfc.Library,tfc.Miscell ) AS 'feepaid',
      (SUM(
          tsf.Admno,tsf.AdmissionFee,tsf.SplFee,
          tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,
          tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,
          tsf.Miscell
          ) -
       SUM(
          tsf.Admno,tsf.AdmissionFee,tsf.SplFee,
          tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,
          tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,
          tsf.Miscell
          )
      ) AS 'outstanding fee'
      FROM tbl_stdfees tsf,
           tbl_admission ta,
           tbl_feecollection tfc
      WHERE
           tsf.admno = ta.admno
           AND tfc.sadmno = tsf.admno
      GROUP BY ta.stdnm;

You are using SUM, an aggregate function that sums up values in the same column, but you are just adding up the individual column values in one row - so MySQL gives you an error message. If you change the query as below, it will work.

SELECT DISTINCT
      tsf.Admno,tsf.AdmissionFee,tsf.SplFee,
      tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,
      tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,
      tsf.Miscell,ta.StdNm,
      tfc.AdmissionFee + tfc.SplFee + tfc.TutionFee +
      tfc.ComputerFee + tfc.Stationary + tfc.Transport +
      tfc.Hostelfee + tfc.Lab + tfc.Library + tfc.Miscell AS 'feepaid',
      ( (tsf.Admno + tsf.AdmissionFee + tsf.SplFee +
          tsf.TutionFee + tsf.ComputerFee + tsf.Stationary +
          tsf.Transport + tsf.Hostelfee + tsf.Lab + tsf.Library +
          tsf.Miscell)
          -
          (tfc.AdmissionFee + tfc.SplFee + tfc.TutionFee +
          tfc.ComputerFee + tfc.Stationary + tfc.Transport +
          tfc.Hostelfee + tfc.Lab + tfc.Library + tfc.Miscell)
        ) AS 'outstanding fee'
      FROM tbl_stdfees tsf,
           tbl_admission ta,
           tbl_feecollection tfc
      WHERE
           tsf.admno = ta.admno
           AND tfc.sadmno = tsf.admno;

Since you are not using an aggregate function, you don't need to use GROUP BY either.

Result:

  Admno | AdmissionFee | SplFee | TutionFee | ComputerFee | Stationary | Transport | Hostelfee | Lab  | Library | Miscell | StdNm | feepaid | outstanding fee
  1     |      6000.00 |   0.00 |      0.00 |     2500.00 |    1500.00 |      0.00 |      0.00 | 0.00 |    0.00 | 3000.00 | zzz   | 1400.00 |           11601
  2     |      5000.00 |   0.00 |      0.00 |     2500.00 |    1500.00 |      0.00 |      0.00 | 0.00 |    0.00 | 5000.00 | xxyy  | 1500.00 |           12502 |
  2 rows in set (0.01 sec)

That being said:
In my experience the database is much easier to maintain, if you'd restructure the tables and normalize them, have one row for each of the fees, and then use SUM and GROUP BY. You then could add fee types without changing the table structure, and your scripts don't need to know which fees to expect.

For example, change the tbl_stdfees to:

CREATE TABLE tbl_newfees (
   id INT AUTO_INCREMENT,
   Admno VARCHAR(50) NOT NULL,
   fee_id int,
   fee FLOAT(10,2) DEFAULT 0.00,
   PRIMARY KEY (id, fee_id, fee),
   CONSTRAINT FOREIGN KEY (fee_id) REFERENCES tbl_feevars.id);

This assumes that you only have one fee per fee type per id. If several fees might occur of the same type, you could add an additional identifier like a date. Use REPLACE to either insert a new few or overwrite an older fee.

Not taking in account the date and not making provisions for multiple fees of the same type bothers me, I would take provisions for it in production.

Also, I wouldn't allow a NULL value for Admno - especially since you are grouping by it (in this scenario).

Example:

mysql> select * from tbl_newfees;
+----+-------+--------+---------+
| id | Admno | fee_id | fee     |
+----+-------+--------+---------+
|  1 | 1     |      1 | 6000.00 |
|  2 | 1     |      4 | 2500.00 |
|  3 | 1     |      5 | 1500.00 |
|  4 | 1     |     10 | 3000.00 |
|  5 | 2     |     10 | 5000.00 |
|  6 | 2     |      5 | 1500.00 |
|  7 | 2     |      4 | 1500.00 |
|  8 | 2     |      1 | 5000.00 |
+----+-------+--------+---------+

This takes advantage of your tbl_feevars table - you'd have to define the FeeName only once.

mysql> select Admno,sum(fee) from tbl_newfees group by Admno;
+-------+----------+
| Admno | sum(fee) |
+-------+----------+
| 1     | 13000.00 |
| 2     | 14000.00 |
+-------+----------+

Another table for the paid fees - the meta information should go into a separate table and get joined in, however, if the bank, remarks, PaM, etc are specific for each individual fee, which they might be, the could be in the paid fees table as well. If they are general to the fee type, they could be in the tbl_feevars table. You know your system best.

mysql> select * from tbl_paidfees;
+----+-------+--------+---------+
| id | Admno | fee_id | fee     |
+----+-------+--------+---------+
|  1 | 1     |      1 | 6000.00 |
|  3 | 1     |      5 | 1500.00 |
|  4 | 1     |     10 | 3000.00 |
|  5 | 2     |     10 | 5000.00 |
|  6 | 2     |      5 | 1500.00 |
|  8 | 2     |      1 | 5000.00 |
+----+-------+--------+---------+

(this is a bit different from your example data, I just deleted the fee_id 4 entries complared to the other table)

I then select for the sums using a left outer join, which assumes that all outstanding fees have been entered into the table before an entry in the paid fees table has been made:

SELECT n.Admno,
       SUM(n.fee) AS "Total Fees",
       SUM(p.fee) AS "Total Paid",
       SUM(n.fee)-sum(p.fee) as "Outstanding Balance"
FROM tbl_newfees n
       LEFT OUTER JOIN tbl_paidfees p
       ON n.Admno=p.Admno AND p.fee_id=n.fee_id
GROUP BY n.Admno;

Results:

+-------+------------+------------+---------------------+
| Admno | Total Fees | Total Paid | Outstanding Balance |
+-------+------------+------------+---------------------+
| 1     |   13000.00 |   10500.00 |             2500.00 |
| 2     |   14000.00 |   11500.00 |             2500.00 |
+-------+------------+------------+---------------------+

To query for more detailed information for a specific individual, for example, you could do:

SELECT n.Admno, v.FeeName, n.fee AS "Total Fee", p.fee AS "Fee Paid"
FROM tbl_feevars v,
     tbl_newfees n LEFT OUTER JOIN tbl_paidfees p
                   ON n.fee_id=p.fee_id AND n.Admno = p.Admno
WHERE v.id = n.fee_id
ORDER BY n.Admno;

+-------+---------------+-----------+----------+
| Admno | FeeName       | Total Fee | Fee Paid |
+-------+---------------+-----------+----------+
| 1     | Admission Fee |   6000.00 |  6000.00 |
| 1     | Computer Fee  |   2500.00 |     NULL |
| 1     | Stationary    |   1500.00 |  1500.00 |
| 1     | Miscell       |   3000.00 |  3000.00 |
| 2     | Admission Fee |   5000.00 |  5000.00 |
| 2     | Computer Fee  |   2500.00 |     NULL |
| 2     | Stationary    |   1500.00 |  1500.00 |
| 2     | Miscell       |   5000.00 |  5000.00 |
+-------+---------------+-----------+----------+

Similarly, you can left join the tbl_feevars table to see which fees have not been entered for a student into any of the fees tables.

To display the results in one row, you could use the pivot table approach, similar to https://stackoverflow.com/questions/17680926/adding-columns-while-inserting-a-row-in-another-table/17681027#17681027, or just query regularly and assemble the result externally in a script.


What will happen in the background, when we update a record in a table in Postgres?

In general, a new row version will be created by an update to support MVCC. This might not happen under certain conditions e.g. if the update does not change any indexed column.

The space  occupied by the old row version will be freed by the auto-vacuum process and re-used for new data once there are no more transactions that would see that version.


Differential Backup from SQL Server 2012 to Azure Blob

You are correct, this is not possible in Azure cloud. You can do this for premise backup which I highly discourage. It just makes your restore more complicated. But in Azure if file name already exists in your container only way to use that same name is to use WITH FORMAT which will create a new media set.

I tested this and I get following error message:

  Msg 3296, Level 16, State 1, Line 1 The file
  https://storageacocunt/sqlbackfiles/testFull.bak
  exists on the remote endpoint, and WITH FORMAT was not specified.
  Backup cannot proceed. Msg 3013, Level 16, State 1, Line 1 BACKUP
  DATABASE is terminating abnormally.

From Books online:

BACKUP (Transact-SQL) | Microsoft Docs

  FORMAT

  Specifies that a new media set be created. FORMAT causes the
  backup operation to write a new media header on all media volumes used
  for the backup operation. The existing contents of the volume become
  invalid, because any existing media header and backup sets are
  overwritten.

You can check this with RESTORE HEADERONLY command. If you take a fullbackup and then a differential WITH FORMAT, you will see backup type change from 1 to 5.

I suggest you create a new for each file.

This link has example about generating a new filename for each backup.


Is it possible to mimic Oracle SET AUTORECOVERY ON in SQLServer 2008 for applying transaction logs?

No. SQL Server does not have this option.

What you can do ?

Take often Full/differential backups (as suggested by @Aaron Bertrand). This highlights the importance of having a good backup (and restore) plan which is inturn dictated by your company's RTO and RPO.
Script out and often test your restores. A backup is ONLY good if you are able to restore it. You can use sp_RestoreGene - T-sql version from Paul Brewer. It automates your restores.
Implement Logshipping to a warm standby server. Logshipping is an automated process of backing up your primary database (Full backup followed by T-Logs set as per frequency (minimum is 1 mins)), copying the log files to a shared location on secondary and applying them on the secondary database. This will be a close option to SET AUTORECOVERY ON that you have in Oracle.

  My goal is to deliver logs to standby, but apply them once a day.

This is a perfect case of implementing Logshipping. You can even delay applying transaction logs and have the secondary database in standby mode which can be used for reporting queries.

Alternatively, if you are on sql server 2012 Enterprise and up, then you can look into implementing AlwaysON Availablity groups.


InnoDB engine getting disabled after decreasing innodb_page_size to 4K

max_allowed_packet is limited to 1G, not 26843545600.
join_buffer_size should not be more than 1% of RAM.
How much RAM do you have?


PRIMARY KEY (`STUDENT_ID`, `ANNUAL_FEES_ANNUAL_FEE_ID`, `PARENTS_PARENTS_ID`, `PARENTS_ACCOUNTS_ACCOUNT_ID`),

It seems like Students would have student_id as its PRIMARY KEY.

DECIMAL(4,2) -- do you want to limit things to $99.99?

This is what I mean about many-to-many relation table:

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, y_id),            -- When starting with X
    INDEX      (y_id, x_id)             -- When starting with Y
) ENGINE=InnoDB;

What is a "Performance_Student"?

Parents table:

parent_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
family_id MEDIUMINT UNSIGNED NOT NULL,
type ENUM('mother', 'father', 'guardian', 'grandparent', 'other'),
`LAST_NAME` VARCHAR(35) NULL,
`FIRST_NAME` VARCHAR(35) NULL,
`EMAIL` VARCHAR(50) NULL
PRIMARY KEY(parent_id)

The Phones table would have parent_id and be many_to_1.


Oracle SQL NOT IN and NOT EXISTS returning different values. Why?

That is normal behaviour. NOT IN and NOT EXISTS is not the same. They return same results only as long as nulls are not involved.

SQL> create table tablea (id number);

Table created.

SQL> create table tableb (id number);

Table created.

SQL> insert into tablea values(1);

1 row created.

SQL> insert into tablea values(2);

1 row created.

SQL> insert into tableb values(2);

1 row created.

SQL> insert into tableb values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select ID from tableA where ID not in (select ID from tableB);

        ID
----------
         1

SQL> select ID from TableA A where NOT EXISTS (select ID from tableB B where B.ID = A.ID);

        ID
----------
         1

SQL> insert into tableb values (null);

1 row created.

SQL> commit;

Commit complete.

SQL> select ID from tableA where ID not in (select ID from tableB);

no rows selected

SQL> select ID from TableA A where NOT EXISTS (select ID from tableB B where B.ID = A.ID);

        ID
----------
         1

Your subquery returns nulls.


https://www.lynda.com/ has free online classes. Some of which include practice databases and datasets to work with. Or you might try Gale. https://education.gale.com/l-slcl/


select items with one or more specified tags

Just use FIND_IN_SET function

SELECT * FROM items
JOIN tags
ON FIND_IN_SET(tags.id,items.tags)
WHERE tags.id IN(2,3)

Fiddle


After you are finished with a prepared statement, and executing it, be sure to deallocate_prepare_statement.  I think this will keep the counter from ever climbing to the limit.


Dynamically define a RETURN table (column type, name) for subsequent loop

What you might need

You should be able to avoid all the complication with a simple SELECT instead:

SELECT t.*, ST_Intersection(x.geom, t.geom) AS geom2
FROM   tbl t  -- replace tbl with actual table
JOIN   ST_GeomFromText($clipper_geom, 4326) AS x(geom) ON ST_Intersects(x.geom, t.geom)
WHERE  t.seq = $age_sequence;

Minor difference: you get the original geom and the intersection geom2 in the result. Add the redundant ST_AsText(ST_Intersection(x.geom, t.geom)) AS clipped_geom_wkt to the SELECT list if you really need it.

What you ask

I understand your question like this:

I have various input tables, each has a column geom geometry. I want a function to take a table name and clipper_geom geometry as input and return all rows intersecting with my clipper_geom. One column clipped_geom_wkt text shall be appended and the value of geom changed, each showing the intersection with clipper_geom.

That's not trivial. SQL is a static language, a function demands to know the return type at call time at the latest. We can achieve dynamic results with polymorphic types, but we need a well-known row type to work with. The row type of an existing table is good for it, but you want to append another column. That's not easily possible. The workaround is to return the polymorphic row type, plus an extra column (like you seem to have tried already). Decompose the row type in the function call to arrive at your desired result:

CREATE OR REPLACE FUNCTION my_func(_tbl ANYELEMENT
                                 , clipper_geom text
                                 , age_sequence text)
  RETURNS TABLE (tbl ANYELEMENT, clipped_geom_wkt text) AS
$func$
DECLARE
   _geom geometry := ST_GeomFromText(clipper_geom, 4326);  -- compute once
BEGIN
   FOR tbl IN EXECUTE format(
     'SELECT * FROM %s
      WHERE  seq = $1
      AND    ST_Intersects($2, geom)'
    , pg_typeof(_tbl)
      )
   USING age_sequence  -- data type must match!
       , _geom
   LOOP
      tbl := tbl #= hstore('geom', ST_Intersection(_geom, tbl.geom));
      clipped_geom_wkt := ST_AsText(tbl.geom);
      RETURN NEXT;
   END LOOP;
END
$func$  LANGUAGE plpgsql;  -- don't quote the language name

Call (important!):

SELECT (tbl).*, clipped_geom_wkt
FROM my_func(NULL::public.clipped_table, $my_clipper_geom, $my_age_sequence);

Works for me in Postgres 10.

Notable features:

Polymorphic types.
Note how the row variable tbl is defined implicitly in the RETURNS clause, where it copies the input type dynamically (ANYELEMENT). So you are not bound to use SETOF with polymorphic types like you speculated.
Related (you linked to that one yourself):

Refactor a PL/pgSQL function to return the output of various SELECT queries

FOR-IN-EXECUTE to loop over a dynamic query.
Assignment of a yet-unknown column with the hstore operator #=. See:

How to set value of composite variable field using dynamic SQL

Decomposition of composite type in the function call with (tbl).*

Simpler

I stuck to your design above, but it's unnecessary complication for the given example. The extra column clipped_geom_wkt is completely redundant, just derive it from geom in the function call. Then we can use the input type directly:

CREATE OR REPLACE FUNCTION my_func2(_tbl ANYELEMENT
                                 , clipper_geom text
                                 , age_sequence text)
  RETURNS SETOF ANYELEMENT AS
$func$
DECLARE
   _geom geometry := ST_GeomFromText(clipper_geom, 4326);
BEGIN
   FOR _tbl IN EXECUTE format(
     'SELECT * FROM %s
      WHERE  seq = $1
      AND    ST_Intersects($2, geom)'
    , pg_typeof(_tbl)
      )
   USING age_sequence -- data type must match!
       , _geom
   LOOP
      _tbl := _tbl #= hstore('geom', ST_Intersection(_geom, _tbl.geom));
      RETURN NEXT _tbl;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT *, ST_AsText(geom) AS clipped_geom_wkt -- if you need the redundant column
FROM   my_func2(NULL::public.clipped_table, $my_clipper_geom, $my_age_sequence);


Is it ok to add a table years in a Relational DataBase?

vehicle_year should be datatype YEAR.

Dates should be datatype DATE.  Even if you need to break apart a date into its component parts, it is almost always better to do it that way instead of having a dimension table with dates and their parts.

In general, do not "normalize" any 'continuous' value -- dates, integers, floats, etc.

As kevensky pointed out, there is a use case for going the other direction for "reports" that need to display zero for missing years (or whatever).  But this is not linked in any way to the main tables.  Instead it is used something like

SELECT y.year,
       COALESCE(SUM(m.stuff), 0),
       ...
    FROM Years AS y
    LEFT JOIN my_table AS m
    GROUP BY...

Note how the LEFT JOIN includes all the years from the Years table.  (You may want to limit the range with a WHERE clause.)

And the COALESCE is used to turn NULL for a missing year into 0.  Or N/A.  Or No data.  Or whatever.

While I am at it, I would suggest that normalizing "model" is also "over-normalization".  The model name spelled out is perfectly fine in the Vehicle table.

When should you normalize?

Not unique -- People's names.
When the value is likely to change -- Vehicle owner (but this needs a many:many table)
There is a lot of ancillary data -- Company (with address, etc)
To save space -- the name is long and the table is big; not for 2-letter country_code versus 4-byte INT.

Model year is self-identifying, never changes, not big, has no ancillary data.

Vehicle make and model are mostly similar to model year.  Ditto for engine size, color, price, etc.

Let me branch off into a hypothecal query:  "What years (model_years, that is) did Chevrolet produce their Impala model?"

That could be answered by `SELECT DISTINCT model_year FROM Vehicle WHERE make = ...;".  That gets the answer from the available Vehicles in your table.

Or you might have it from a historical web site that lists the answer.  Now you need a table with PRIMARY KEY(make, model) and various info on the history of old cars.

That leads to a messier situation -- hierarchical info.  Note:  GM > Chevrolet > Impala > LT.  "Locations" have a similar problem:  USA > Georgia > Fulton County > Atlanta > address.  Generally, normalization at each level is gross overkill and should be avoided.

  because many tables have the 'year' value

Well, the 'textbook' argument for normalization fails miserably here.  It says that you should normalize in order to have the value sitting in a single place to make it easy to change.  But if that year represents a vehicle's model_year for one table, but your child's birthday for another table and your graduation in another, you certainly don't want to be changing the value.

Think of the normalization table as an "Entity" such as a person, a place, a company, a picture, a web posting, etc.  You give the Entity a unique identifier (PRIMARY KEY) so that everyone can refer to it easily.  In the table, you have a printable name, a location, a "likes" counter, etc, etc.


Difference Between SP with BEGIN/END and without BEGIN/END

There is no difference. That is, it's optional.

What matters is the "end of the batch" which is denoted in SQL Server Management Studio by GO


Store a small log file as a blob in sqlite

For me your last line contradicts the subject (blob vs text), so probably I mistook your idea.
But what about making your log file an INSERT statement on the fly?  Then feed that into your desired sqlite database.  I mean something like this:

$ cat test.sql
INSERT INTO test
SELECT 'something
anything';

$ sqlite3 test < test.sql

$ sqlite3 test
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM test;
something
anything
sqlite>


Do cross-joins get optimised by the client library?

I'm reasonably confident that there is no optimization in the Oracle client libraries for repeating data.  If it is, it's below the SQL*Net trace layer which would surprise me.

There are definitely cases where it could make sense to do the join in the app server rather than in the database.  But it would be exceedingly rare that you'd have a table structure like this where you would also want to display the result of joining A to B to the user.  If you don't want to display the data to the user, you'd just make sure that the results were being processed in the database so that neither A nor B had to be sent over the network.

I have had one or two cases where I needed to get highly denormalized results to a client where it made sense to use the CURSOR function rather than doing a straight join solely in an effort to minimize the amount of data being sent over the network.  Of course, that requires that you're selecting a substantial amount of data from table A (DEPT in this case) so that the duplication of data actually becomes problematic.

SQL> ed
Wrote file afiedt.buf

  1  select dname,
  2         cursor( select ename, empno
  3                   from emp e
  4                  where e.deptno=d.deptno) emps
  5*   from dept d
SQL> /

DNAME          EMPS
-------------- --------------------
ACCOUNTING     CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME           EMPNO
---------- ----------
CLARK            7782
KING             7839
MILLER           7934

RESEARCH       CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME           EMPNO
---------- ----------
smith            7369
JONES            7566
SCOTT            7788
ADAMS            7876
FORD             7902

SALES          CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME           EMPNO
---------- ----------
ALLEN            7499
WARD             7521
MARTIN           7654
BLAKE            7698
TURNER           7844
SM0              7900

6 rows selected.

OPERATIONS     CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


Is it possible that records are changed while a read query is still executing?

Yes, it is possible and this may or may not be desirable. The database theory has a concept of isolation, which is about transaction visibility to other processes. MySql's documentation about set transaction has a discussion about product specific syntax.


How can I mimic functionality of SQL Server `TYPE` in Oracle?

This is a very good question and not just because it shows effort on your part to answer the question.  Thank you for asking it and please do not remove it.

The answer as you have already discovered is -- You can't do that.

You have correctly identified several similar concepts including...

Using standard types with constraints.
Object Types that behave differently than standard types.
Subtypes which are for PL/SQL not SQL.

Why might SQL Subtypes be useful?  You mentioned consistency.  Here are some ideas:

Errors are prevented when inserting a field from one table into another.
The definition of the field does not have to be rememberd or looked up.
There is less typing.
Changing the definition can be done in one place.
The datatype name provides documentation.

Here are some thoughts addressing these:

Table definitions are not changed frequently.  Yes, they do change, but the amount of change is considerably smaller than that of PL/SQL (where you can use subtypes and %types) or application side code.
Databases are usually normalized to eliminate duplication of data and limit the requirement for multiple tables to have fields with identical definition.  Of course something as generic as a Money type would get used in many places, but some types would get moved to a lookup table and only the key repeated in multiple places.
Often there are benefits to using distinct sizes for different tables.  A discount store might have a products table with a field defined as Number(4,2) since they never sell anything over ten dollars.  The definition is itself a constraint on the data a constraint that improves acuracy, documents the system, is fast, cannot be circumvented, and is all around better than any application side constraint.  If the business decides to remove this constraint by increasing the field size, it does not necessarily mean it needs to change the size of other fields such as Salary, SalesAmount, etc.
Denormalized data is often aggregated and may require different field sizes. A Salary column need not be Number(18,4) and even a sum of all salary columns company wide need not be Number(18,4) (just short of 100 trillion). Even if you need to increase the aggregated field containing Salary information due to growth of the company, you still wouldn't necessarily need to increase the Salary field at the same time, but if they are based on the same SQL subtype then you would have no choice.  Right sized fields have the benefit of using less storage and memory as well as having faster index and table lookups.
Remembering or looking up a custom type is similar in difficulty to remembering or looking up a previously used field definition.
Oracle converts the SQL Server Money type to Number(19,4) when using the 10.2 Transparent Gateway, so even standardizing on Number(18,4) would be wrong when working with the gateway.
In Oracle, frequently table columns are created that include definition that could be in the datatype name for other systems.  For example, Wholesale with a datatype of Money might be WholesaleCost in Oracle so that it is clear the field holds relates to money.  Sometimes this disabiguation would be necessary anyway such as if there were UnitCount, UnitVolume, and UnitCosts fields.  Other times the name itself gives the type away (such as Salary).   Finally, comments can be put on the table columns to provide further clarification if necessary.

I say all this not to say that the concept of an SQL subtype is bad, just that it is not as useful as it may seem.


What is a 'Target Server' in a SQL Server Agent Job?

A Target server allows the job to be run against a different server than the one it is defined on.

Multiple Target Servers allows you to define a job once (say a routine backup job) and have it execute against many servers (thus backing each of them up).

UPDATE:

MSDN Documentation here:  http://msdn.microsoft.com/en-us/library/ms180992.aspx


Mongodb background indexing - no progress

Solved this one. While indexing was running, new code was released in production, which contained a bad query. I was checking Primary performance only, and killer query was happening on Secondary node. After adding one more necessary index, everything went back to normal, performance is stellar again.

However message "msg" : "Index Bulk Build: (3/3) btree-middle" is still there.


The size of dateTime2(0), dateTime2(1), dateTime2(2), dateTime2(3) use
  the same amount of storage. (6 Bytes)

  Would I be correct in saying that I might as well go with dateTime2(3)
  and gain the benefit of the precision without any additional size
  costs.

No, you misinterpreted the documentation. Note the doc states storage size 6 bytes for precisions less than 3 (emphasis mine). So a precision equal to 3 will require 7 bytes.

If you don't care about milliseconds, datetime2(0) would be the proper data type and precision.  The best practice is to specify the proper data type and precision based on the data stored as this will inherently provide optimal storage and efficiency. That being said, I would not expect a significant performance impact based on the specified datetime2 precision as long as the storage size is the same but I have not specifically tested that myself.

Application requirements will dictate what must be stored in the database when a greater precision is available in the source. For example, for an order entry time sourced from SYSDATETIME(), users may not want 100 nanosecond precision. Again, choose the data type and precision for new development according to requirements and you will generally get optimal performance without additional thought:

date - you don't need time
smalldatetime - you don't need seconds
datetime2(0) - you don't need fractional seconds
datetime2(1-7) - you need fractional seconds of the specified precision
datetimeoffset(0-7) - you need date and time with time zone awareness
time(0-7) - you need time only (no date) with fractional seconds of the specified precision

Although datetime2 is most appropriate for new development as listed above, one may sometimes need to use datetime (fixed precision 3 with 1/300 fractional seconds accuracy) instead for compatibility with legacy datetime applications, thus avoiding implicit conversions and unexpected comparison behavior, but at the expense of fractional second accuracy and increased storage.

Consider that storing a greater precision than required may also have a development cost. If one stores a time component with fractional seconds when only whole second precision is required, queries will still need to consider the fractional seconds to return the correct results. For example, with an app where the user selects a time range via a UI that only allows for whole seconds, the app code would need to account for the fractional seconds in the end time range value and adjust the user-supplied value accordingly (e.g. WHERE OrderEntryTime BETWEEN '2017-01-11T08:00:00.00.00' AND '2017-01-11T08:59:59.99' or WHERE OrderEntryTime >= '2017-01-11T08:00:00.00' AND OrderEntryTime < '2017-01-11T09:00:00.00'). This will add code complexity.


Disk queue length very high in SQL Server 2016

Disc queue is an indication that you have saturated the bandwidth to that disc. It indicates that you might have

slow disk
slow connection to a disk (or vhd)
contention

(or any combination of these)
Look at where those discs are, how they are connected, and what is on them. Troubleshooting techniques depend on whether this is a VM (with VHD) or physical hardware.
I usually have the most success by asking my SAN admin (or VM admin) to issue me a drive (letter) "pinned to a spindle" (and not shared), and "scaling-out" (move my busy(est) DB to the new storage), but again, it depends on your equipment/situation.


Availability Groups and Partition Swapping

If database that is being partition swapped is part of Availability Group, parition switch operation will be logged into the transaction log and it will be replicated to secondary replica.