The comment from pg_column_byte_packer appears to be not wrong as text columns do have a pg_type.typalign of 4, but they've also got a pg_type.typlen of -1.

Had me puzzled, too, when I researched it a couple of years back. typlen = -1 just indicates varlena storage, which nominally has typalign = 'i' (integer alignment, needs to start at 4-bytes offset). But that's not the whole story. Eventually, I found the explanation in a note in the source code:

Note also that we allow the nominal alignment to be violated when storing "packed" varlenas; the TOAST mechanism takes care of hiding that from most code.

So a varlena datum < 127 bytes (after possible compression) only adds 1 byte of overhead (signifying its length) and requires no alignment padding "on disk". (Hardly any "disks" any more these days.)
See:

Calculating and saving space in PostgreSQL (where I coined the term "Column Tetris")

To answer the question in the title:

Does the optimized column order for a PostgreSQL table always have variable length types at the end?

True-ish. Datums exceeding 127 bytes cannot be stored in optimized form and fall back to requiring "integer alignment". If we don't know that a varlena-type column will stay below that threshold (most of the time), we can't say for sure.
Also, there are additional considerations for optimized storage. With many columns in a row, it's cheaper to compute tuple storage offsets with NOT NULL, fixed-size length columns first. Placing frequently accessed columns first also yields a tiny advantage. All of this is further complicated by the TOAST mechanism and index(-only) access.
But all of these effects are typically tiny. And losing 3 bytes to alignment padding pales in comparison when a column occupies 200 bytes. So mostly not worth the bother. The rule of thumb covers most of it:
Sort columns by their required alignment typalign:
d --> i --> s --> c.
But typlen = -1 ("varlena") last (typically), even though formally typalign = 'i'.
The manual:

c = char alignment, i.e., no alignment needed.
s = short alignment (2 bytes on most machines).
i = int alignment (4 bytes on most machines).
d = double alignment (8 bytes on many machines, but by no means all).

Your example

So in the case of a table that has an integer column, a text column, and a smallint column, pg_column_byte_packer will put the text columns right in between the two.

pg_column_byte_packer is living up to its name. int --> text --> smallint is as tight as it gets.
For the typical case of short strings, the only relevant decision is to put int first. smallint can at most force 1 additional byte of alignment padding at odd byte offsets. Since tuple space is always allocated in multiples of 8 bytes, this can never result in a bigger tuple.
Strings exceeding the threshold of 127 bytes on disk (including 1 leading length-byte), flip to 4 leading length bytes and require the nominal integer alignment. That's where putting text before smallint can safe 8 bytes effectively. With random string length that happens in 25 % of cases, so 2 bytes on average for tuples of at least 144 bytes.
There's that. But putting the smallint first typically has tiny advantages, and most text columns stay well below the length threshold.
The thing to remember is not to intersperse multiple smallint and text columns. Multiple offsets can add up in this case.


I have come across a similar problem once when importing a 5GB SQL file. Most editors max out at 256mb or 512mb, the only editor I know of (on windows) that can open files in the GBs without issues is emEditor (there's a trial version). My old rig with 8GB of RAM (twice as much as yours but most of it was used up to 80% the entire time) and ~20-25 GB of free space on C drive could open it in ~2minutes (keep in mind my hardware was extremely old DDR3 with gen2 or gen3 i7 processor and slow drives).
The biggest selling point (even if you have to wait for it to load) is being able to view the schemas and the code in case the export didn't include DDL for the tables (which was the case in my scenario).
Depending on the exporting tool used, your insert statements might have ` for strings instead of ', thankfully using the emEditor you can replace all of them with '. This will allow you to import your code into SQL Server without issues.
The way I solved it was by modifying the file so it can be natively imported into SQL Server and use SQL CMD to run the script file.


Does anyone have example for setting up a job to reduce SQL Server max memory and recycle SQL server service?

Max/Min Server Memory setting in SQL 2008 is the buffer pool.  They do not include the memory that is allocated for worker threads, multipage allocator, DLLs and components that SQL loads in its address space, backup and restore operations, SSIS, SSAS, SSRS, and so on.  Set Max Server Memory low enough to leave sufficient memory available for those things not included in the buffer pool.  Windows will allocate the memory for you.

If it is your desire to have the Max Server Memory setting to be higher during the day, and adjust it lower for the ETL/SSAS jobs, then raise it back up again once they are completed, then you can script that with the following (#### is the MB being set, i.e. 1024):

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', ####;
GO
RECONFIGURE;
GO

Be warned that setting it to a lower number does not always result in the immediate return of memory to Windows.  If the memory is in use, SQL will release when it is no longer in use.


No, phpMyAdmin doesn't require using HTTPS. Some server configurations seem to be experiencing this problem, which is reported at the phpMyAdmin issue tracker at https://github.com/phpmyadmin/phpmyadmin/issues/14184.

For me, trying to log in a second time works, for others there are add-ons such as Katalon Recorder (Selenium IDE for Chrome)  or uBlock Origin preventing the file /js/vendor/js.cookie.js?v=4.8.2 (or whatever your version is) from loading.

Finally, there can be some cookies "stuck" between secure and insecure mode; you may be able to clear those directly through your browser (phpMyAdmin cookies start with "pma").


You could have one table that tracks what a patient takes, and another that tracks which drugs interact with which:

patient_drugs
-------------
   patient_id (FK to main patient table)
   drug_id (FK to main drug table)

drug_interactions
-----------------
   drug_id (FK to main drug table)
   reacting_drug_id (FK to main drug table)
   interaction_description (text)

Then to find a patient's drug interaction you could maybe do this:

select *
from drug_interactions
where drug_interactions.drug_id in (select drug_id
                                    from patient_drugs
                                    where patient_id = $PATIENT_ID)


Non numeric attributes in fact table (to track data source)?

I will typically have my business key in the fact table so I can easily track back to the source system for any questions. I usually will put a unique constraint on it to ensure the granularity of the fact table is the same as the business key.


Assuming the ordering of your input data is such that the beginning of the batch (Column1=1) always has the least RowNumber within that batch, you can do something like this to assign pseudo batch numbers using the largest RowNumber among the preceding header records:

select t.*,
    max(case column1 when 1 then RowNumber end)
    over(order by rownumber range between unbounded preceding and current row) as batchid
from tbl t

(fiddle)

From there you can use aggregate or window functions to calculate aggregates if you wrap the above in a subselect and use the pseudo batch numbers to partition data or group data:

select
max(case column1 when 1 then column3 end) as batchid,
sum(case column1 when 2 then some_numeric_column end) as sum_children,
max(case column1 when 8 then column3 end) as footer_value
from (
  select t.*,
    max(case column1 when 1 then rownumber end)
    over(order by rownumber range between unbounded preceding and current row) as batchnum
from tbl
) t1
group by batchnum


Streaming Replication Log is puzzling me

You appear to have two separate problems an issue with OpenSSL renegotiation.

First - nope, I was wrong, see Daniel's answer.

Second, you're being bitten by an OpenSSL issue with renegotiations. Searching for the error message would've told you this. As noted in that bug report:

  Workaround is to set 'ssl_renegotiation_limit=0' in postgresql.conf

which asks PostgreSQL to tell OpenSSL to stop attempting renegotiations, since that version of OpenSSL has broken renegotiation support.


Kudos for trying to get it right, and understand the principles.  I've been doing this for years and still find it difficult to label existing structures with precisely accurate relational theory terminology at times.

Normal forms, it's important to note, only describe individual relations (that is, "tables," in SQL), and not the relationships between them... so the frequent appearance of justification_id does not in any sense move your tables further from 2NF.  Indeed, these are all tables of attributes about justifications, so it's only sensible that the justification would appear.

The justification_id seems to be a candidate key, or at least a prime attribute,  in each table... isn't it?  Candidate keys, pretty much by definition, can't have a denormalizing effect.  It's the other attributes that must be analyzed.

I'm confused about category and category_id.  Would "category" be (e.g.) "ticket" and "category_id" be (e.g.) "27"...?  If so, your naming is a little counter-inituitive, and you're going off in a direction that normalization doesn't really address, but that may cause you difficulty later -- you're mixing data domains in a single attribute (column).

By that, I mean this: the value of "category_id" might be an id from the "ticket" table or an id from the "order" table, etc., depending on which type of category.  If I am right about what these columns are being used for, then you don't want to do this, because (among other reasons) it is impossible for the database to correctly impose a foreign key constraint on this column.  As a rule of thumb, a value in a column should mean the exact same thing regardless of what's contained in any other column.  It might be more correct if there were junction tables, "ticket_has_justification" (with a ticket_id and justification_id), "order_has_justification" (order_id, justification_id), etc.  You can still outer join all justifications to the various types of things they justify, but this is a cleaner approach, at least in my opinion.

The "questions" column also might be more appropriate in a "question" table, with justification_response having a question_id column referencing it, but here again, this isn't really addressed by normalization -- whether it's stored in this table as the words of the question, or as a surrogate for the words (question_id), the "question" column is still a prime attribute, with (justification_id, question) being the candidate key and response being dependent on both, so this one is at least 3NF, despite having what looks to me like room for improvement.

Fundamentally, though, I don't see what isn't 2NF about any of these... depending on exactly what "category" and "category_id" mean.


Up to date statistics does not necessarily imply perfect estimation. If you try to represent a range of 170 million values with 200 samples, even a small skew can throw you off by a factor of 10.

Here is a hint: time series are almost always accessed by time. Very likely DateStamp should be clustered key. For more detailes, post the exact DDL and actual execution plan (XML, not picture).


How do I use MySQL Workbench to edit an existing, in-production database?

This is an arguable opinion, from limited personal experience.

I believe it was the Workbench who screwed up the process of round trip engineering several times. I don't remember all the detail, but it certainly synchronized more objects than had actually changed.  It silently deleted some data a few times.  And it sometimes generated SQL that the server rejected.  It was a development DB, no big deal, especially compared to the benefits.  But in production I'd rather refrain from being at such a tool's mercy.

Some other people's experience was similar, e.g., MySQL Workbench model/source synchronize issue

So for a production DB there may be risks, including that of losing your data. I would stick with phpmyadmin and do synchronization back into the model, always checking what the Workbench is going to do. Keeping the model just for reference.


Compare each column in a row and return an error if any of them is different

I see this question is tagged both Excel and SQL Server.

In Excel you could use the approach from here

The formula in cell F2 above is

=IF(SUM(IF(FREQUENCY(IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""), IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""))>0,1))>1,"MisMatch","Match")

Pay attention to the note in the linked article

  The formulas in this example must be entered as array formulas. Select
  each cell that contains a formula, press F2, and then press
  CTRL+SHIFT+ENTER.

Curly braces will then appear around the formula in the formula bar

Another (SQL Server) way (credit to Geoff Patterson for improvements) would be

SELECT T.Identifier,
       CASE
         WHEN min_val <> max_val
           THEN 'MisMatch'
         ELSE 'NoIssue'
       END
FROM   @T T
       CROSS APPLY (SELECT MIN(Val),
                      MAX(Val)
                    FROM   (VALUES (Value1),
                                   (Value2),
                                   (Value3),
                                   (Value4)) V(Val)) V(min_val, max_val)

It should work well for the "hundreds of columns" mentioned.

Though it introduces some extra complexity into the plan compared to just a simple compute scalar.


Advice on deleting all rows and reclaiming space of very large table

Because you have limited experience with database administration, be extra careful. You have a test copy of your database server set up, right?

You mentioned the database uses full recovery model. This is VERY important, because if the transaction logs have not been getting backed up often enough, there is no way to shrink them. You'd have to put the database in simple recovery mode (which for all intents and purposes gets rid of the transaction log) and wait while that processes, then you could move forward. Given the amount of data, I'd imagine it would take a while. For information on the recovery models, go here:  https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server This article gives a basic overview.

To answer your question, truncating or dropping the archive table will not free up space - that's what shrinking the database does. All the truncate/drop does is mark the appropriate pages in the database as unused. There's little sense in dropping the table and re-creating it. A truncate basically gets the same end result and is less prone to error.

Keep in mind, if this application sends more data to this archive table, the database will expand after you've run the shrink procedure! (Are you really using SQL Server 2005? If so, seriously consider upgrading to a supported version, and getting newer hardware if it's a physical server.) So, just like the application moves data to the archive table, you should consider implementing a way to remove unneeded data from the archive table to avoid this situation in the future.  But first things first.

If you haven't already, read this: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql It's a Microsoft doc that describes shrinking a database in some good detail. This is a decent overview, and contains some best practices as well as caveats.

If you don't want to shrink the database, your other option is to create a new database, transfer to it what you want to keep from the old database, then drop the old database. Of course, this will take up quite a bit of disk space and time. It will also put the server under considerable load.

You are correct in that a database shrink can increase index fragmentation. Having said that, such fragmentation may not have a noticeable effect on performance. That's why you have a test server. :-)

You may have to adjust your maintenance window for this operation. It will take as long as it takes. While the truncate is in progress, the database will be online. You can keep the database online during a shrink as well. Performance may suffer noticeably depending on usage, how queries are tuned, hardware, and operating system configuration. Again, test before doing anything in production! A shrink can be stopped and started at a later time, however, if space is at a premium starting and stopping defeats the purpose.

One more thing: depending on how fast data is added to this database, truncating and shrinking may only delay the inevitable. Consider adding storage capacity.

If you have further details or clarification, I'll update my answer accordingly. In the meantime, best of luck, and let us know how things go!


MySQL - How to see the queries executing by slave io thread

Queries on slave are being applied by system user, so doing show full processlist and queries running with system user are the ones executed by slave sql thread.

Moreover you can view relay logs with the help of mysqlbinlog utlity and replacing binlog file with relay log file.

You can also introduce triggers on processlist table in information_schema database in this way you would be able to capture queries.

To get current query as string use following statement.

SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()

To get queries using trigger a sample trigger is as follows.

First create table logging_table with column as varchar(1024).

DELIMITER |

CREATE TRIGGER log_queries_sample BEFORE INSERT ON `table_name`
FOR EACH ROW
BEGIN
    DECLARE original_query_string VARCHAR(1024);
    SET original_query_string = (SELECT info
                                 FROM INFORMATION_SCHEMA.PROCESSLIST
                                 WHERE id >= CONNECTION_ID());
    INSERT INTO `logging_table`(`query`) VALUES (original_query_string);
END;
|
DELIMITER ;

Create triggers of your choice accordingly.

Hope it helps.


How can I allow an Oracle database to be accessed remotely?

Oracle is an enterprise database server so you shouldnt need any strange setup to get it working, just the correct configuration on the DB server or client will be enough.

When you get connection errors, i suggest first ensuring that the client machine can ping the database server where  is the name or ip address - try both as it's possible the name may not be configured in the hosts file of the client machine. (Note that ping may be disabled due to security but if you get a reply back, you know the connection is good.A non reply may not mean the network is missing but you can try to ssh or telnet to the dbhost from the client machine as an alternative)

 unix> ping <dbhost>

Once you have confirmed the client machine can see the db server, you can check the configuration on the DB server.

On the DB server, if you connect with the following, you are connecting directly to the database without using TNS.

 unix> sqlplus user/pass

If you connect instead with

 unix> sqlplus user/pass@ORACLE_SID

where ORACLE_SID is the identifier of the database, you will be using TNS, the same as your client machine.  If this doesnt work locally, then it's also not going to work on the remote machine.

Check the listener is running

 unix> lsnrctl status

and if it's not, start it as the oracle user

 unix> lsnrctl start

and try again to connect using user/pass@ORACLE_SID

If this connects OK, then check your JDBC configuration to ensure you have specified the connection string correctly.

see http://javaeesupportpatterns.blogspot.de/2011/08/network-adapter-could-not-establish.html for assistance with the correct jdbc connection string.


You should not worry about embedding the USING BTREE onto the indexes. Why ?
The default index type for MyISAM and InnoDB is BTREE. You cannot impose any other type.
The default index type for MEMORY Storage Engine is HASH. You can impose a BTREE index type.
I wrote a post 4 years ago on HASH not being allowed for MyISAM and InnoDB: Why does MySQL not have hash indices on MyISAM or InnoDB?
In light of this, there is no need to explicitly tack on USING BTREE.
You can look up the CREATE INDEX Documentation to verify default index types per Storage Engine.
UPDATE 2015-09-24 16:23 EDT
Hey Jared, I got a surprise for you. I went Googling around and there is a way to spit out USING BTREE. The option is --compatible=mysql40. I saw it in StackOverflow : MySQL error USING BTREE
Interestingly, there is sort of a bug report on this. You know who submitted it ? A guy named Jared.


Error in 'Master Database Restore' in SQL Server 2012 (Because of difference in SQL Server Build)

Upgrade your destination server to build 11.00.2218 before restoring the backup of master, that's your only chance.

If you can't do that (say, the destination master is corrupted, which is why you are trying to do this restore), then rebuild the master via this method so you can get the instance up, then apply the upgrade, then restore master.


Help using SQL Server Database Experimentation Assistant for performance testing

I end up with dozens of 500 MB files at the end of the capture.

Yes I agree the documentation is wrong and you cannot configure file size. You can only configure duration. 500 MB is by design and recommended here and here. 200 MB or 512 MB is the ideal trace rollover size to use to capture production or performance traces. Second link give you few reasons why this is the recommended size. One of them is:

If you use our "published" versions of production and performance
trace definition files and the performance trace definition filters
out the traces for one database of interest, analysis shows that the
Performance Trace size is about 15 times larger than the Production
Trace size.

  Then I am expected to sit there and replay each trace file, one by
  one. Is there any easier way of doing this, or of forcing the trace
  file to grow larger so I end up with less rollover files?

You do not need to process each file individually. In Full path to source File instead of individual file, give the folder name where your raw trace files are located. Tool will create a new sub-folder named PreProcessOutput and put all Replay Markup Language (RML) files there.

On a side note hopefully you configured your Distributed Replay successfully.


The pg_wal directory on a standby server does not get auto-purged

The replication slot should not exist on the standby, only on the master.  Unless you are using cascading replication, which you don't seem to be.

If you have a replication slot on the standby but no one is connecting to it to read from and advance it, that explains the retention.

See https://www.postgresql.org/docs/9.6/continuous-archiving.html#BACKUP-BASE-BACKUP:

  It is often a good idea to also omit from the backup the files within
  the cluster's pg_replslot/ directory, so that replication slots that
  exist on the master do not become part of the backup. Otherwise, the
  subsequent use of the backup to create a standby may result in
  indefinite retention of WAL files on the standby


Create User, Tablespace Replication in Oracle GoldenGate

DDL replication in Oracle Goldengate is disabled for the extract process by default and enabled for the replicat process.

To enable the DDL support, you need to specify the DDL parameter for the extract process.

  DDL

  Use the DDL parameter to:

  enable DDL support
    filter DDL operations
    configure a processing action based on a DDL record

  Syntax

DDL [
{INCLUDE | EXCLUDE}
  [, MAPPED | UNMAPPED | OTHER | ALL]
  [, OPTYPE type]
  [, OBJTYPE 'type']
  [, SOURCECATALOG catalog | ALLCATALOGS]
  [, ALLOWEMPTYOBJECT]
  [, ALLOWEMPTYOWNER]
  [, OBJNAME name]
  [, INSTR 'string']
  [, INSTRWORDS 'word_list']
  [, INSTRCOMMENTS 'comment_string']
  [, INSTRCOMMENTSWORDS 'word_list']
  [, STAYMETADATA]
  [, EVENTACTIONS (action)
]
[...]

In order to replicate CREATE USER, ALTER TABLESPACE or CREATE ROLE kind of statement the DDL scope should be OTHER or ALL to include all kind of DDLs.

Some other examples of OTHER scope:

CREATE USER joe IDENTIFIED by joe;
CREATE ROLE ggs_gguser_role IDENTIFIED GLOBALLY;
ALTER TABLESPACE gg_user TABLESPACE GROUP gg_grp_user;

References:
Oracle GoldenGate - DDL Replication
DDL: Oracle Goldengate 12c
Understanding DDL Scopes


OK, the hit rate is low.  But let's put things in perspective...  25K writes in 25 days is so insignificant (< 1/minute) as to be the least of your worries.  5K/minute would be a concern.

If you are having performance problems, locate the slowest query and study it -- its indexes, its formulation, etc.

The "key buffer" applies to MyISAM tables, of which you have only 1.  The InnoDB statistics are not listed.

For further discussion, see http://mysql.rjweb.org/doc.php/mysql_analysis


Move column to another table by reducing?

You'll need to do it in several steps:

Add the column to the store (I'm not sure what the type is, maybe it's something else):

ALTER TABLE store ADD COLUMN location VARCHAR(50)

Populate it, based on the most prevalent location of the employees. I'm not entirely familiar with MySQL syntax but the query should look something like this:

UPDATE store SET location = locations_by_storeid.location
FROM store INNER JOIN
(SELECT storeid, location,
  ROW_NUMBER() OVER(PARTITION_BY storeid ORDER BY COUNT(*) DESC) row_number
  FROM employee
  GROUP BY storeid, location) locations_by_storeid
WHERE store.id = locations_by_storeid.storeid
  AND locations_by_storeid.row_number = 1

The subquery (locations_by_storeid) lists all locations for employees on a certain storeid, and we take only the most frequent one (the one with row number 1) as per your requirement 2.
Now we can remove the location from the employee:

ALTER TABLE employee DROP COLUMN location


Database design for eCommerce website with multiple products having huge specifications index

You could have a basic product table, then also a table to store different types of specs, then another table to store the specs themselves:

products
--------
  id
  name
  (a few other necessary fields)

product_spec_types
------------------
  id
  name

product_spec_values
-------------------
  id
  product_id (FK to product.id)
  spec_id (FK to product_spec_types.id)
  value

So you could have data that looks like this:

product
ID  |   NAME
----|-----------
1   |   TV
2   | kids boots

product_spec_types
ID  |  name
------------------
1   | refresh rate
2   | size (diagonal)
3   | shoe size
4   | colour

product_spec_values
ID  | Product_ID   | Spec_ID  | Value
-------------------------------------
1   | 1            | 1        | 60 HZ
2   | 1            | 2        | 32 inches
3   | 2            | 3        | 4
4   | 2            | 4        | blue w/grey

This is a very simple implementation of the Entity-Attribute-Value pattern. You could go further if you want, and add data type restrictions (such as numeric, alpha-numeric, enumerations, etc...) to product_spec_types

The main benefit of this approach is that it can be extrmemely flexible.

The downside of this approach is that it is not always fast for searching, since all attributes for all products are in one table. Sometimes it is better to create separate tables that your user's searches will run off of, and those tables will have real columns for all the attributes you want them to be able to search.


SQL 2005 Replication - How do I get the DA Profile MaxBCP option to work with a Transactional Publication?

I'd like to thank Hilary Cotter for providing the answer to my dilemma.  His contribution to the SQL Server community is greatly appreciated!

To address the issue we needed to recreate the publication and specify the sp_addpublication argument @sync_method = 'native'.

According to SQL Server 2005, 2008 and 2008 R2 Books Online, the default @sync_method is character for snapshot publications (non-concurrent character mode BCP output) and concurrent_c (concurrent character mode BCP output) for all other publication types. However, in my experience, the actual defaults are native (non-concurrent native mode BCP output) for snapshot publications and concurrent (concurrent native mode BCP output) for all other publication types.  This is actually a good thing as the native SQL BCP snapshot format is faster/more efficient to deliver to the subscriber. The character formats are only necessary for publications with non-SQL Server publishers.

Also from SQL Server Books Online: When applying a snapshot that was generated at the Publisher using the concurrent snapshot option, one thread is used, regardless of the number you specify for MaxBcpThreads.

The default in SQL Server 2000 was native and according to SQL Server 2012 Books Online, the default has been changed back to native for all SQL Server publications.

Now that I've recreated the publication using the argument @sync_method = 'native', I can now push multiple BCP files simultaneously to the subscriber, thus taking full advantage of the large pipe between our site and our customer's site.

I just finished blogging on this topic (with more detail) here: http://mattslocumsql.blogspot.com/2012/06/distribution-agent-for-transactional.html

Hilary Cotter's swift response was invaluable to solving my issue.  Thank you again, sir!


You might be running into a bug in how SQL Server vNext CTP1.2 handles filepaths. To CREATE CERTIFICATE from a backup, you currently need to pre-pend a 'c:' and use backslashes ('\') instead of forward slashes. This will be fixed soon :)

Here's a demo for the scenario you're trying to do (backup an encrypted database and restore it on a SQL vNext on Linux instance). Let me know if it clarifies things for you.

https://github.com/tmullaney/tde-migration-demo


Select from a table if conditions met in joined table

Comparing each row in keys with all rows in members_keys is not how the join is actually implemented.

This is the query plan without any indexes:

create table members(id int, name text);
create table keys(id int, color text);
create table members_keys(member_id int, key_id int);
explain select * from keys join members_keys on keys.id = members_keys.key_id where members_keys.member_id = 1;
                                QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=38.39..66.55 rows=70 width=44)
   Hash Cond: (keys.id = members_keys.key_id)
   ->  Seq Scan on keys  (cost=0.00..22.70 rows=1270 width=36)
   ->  Hash  (cost=38.25..38.25 rows=11 width=8)
         ->  Seq Scan on members_keys  (cost=0.00..38.25 rows=11 width=8)
               Filter: (member_id = 1)

The database first goes through all rows of the members_keys table, and only those with the matching member_id are inserted into a hash table. It then goes through all rows in the keys table (once), and searches for matching entries in the hash table. (Hash lookups are very fast; their speed is ideally independent of the size of the hash table.)

If we add primary keys with their implicit indexes, then the join works pretty much the same, except that the member_key = 1 check can be sped up with index lookups:

alter table members add primary key(id);
alter table keys add primary key(id);
alter table members_keys add primary key(member_id, key_id);
explain select * from keys join members_keys on keys.id = members_keys.key_id where members_keys.member_id = 1;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Hash Join  (cost=15.05..41.09 rows=11 width=44)
   Hash Cond: (keys.id = members_keys.key_id)
   ->  Seq Scan on keys  (cost=0.00..22.70 rows=1270 width=4)
   ->  Hash  (cost=14.91..14.91 rows=11 width=4)
         ->  Bitmap Heap Scan on members_keys  (cost=4.24..14.91 rows=11 width=4)
               Recheck Cond: (member_id = 1)
               ->  Bitmap Index Scan on members_keys_pkey  (cost=0.00..4.24 rows=11 width=0)
                     Index Cond: (member_id = 1)

The database did rewrite your query into something like the following (except that select * returns columns from both tables):

select * from keys where id in (select key_id from members_keys where member_id = 1);

And depending on database version, options, and statistics, the database might do something entirely different. But it will not do an actual cross join if it can avoid it.


SQL AlwaysOn Availability 2 synchronous nodes on either side of an asynchronous link

No that is not possible.  The commit mode (sync/async) is between primary and secondary logical roles, and not between secondary and secondary (which is why are you aiming for).

I'm not sure what you'd even gain from that.  All your logical design would be adding (even if it was possible) would be a second hop of log blocks for SQL-B2.  You would want your data to go from SQL-A1 (primary) -async-> SQL-B1 (secondary) -sync-> SQL-B2 (secondary).  Like I said before, that isn't possible.  But what is possible would be SQL-A1 (primary) -async-> SQL-B2 (secondary).

The only thing your projected design would optimize would be network traffic between the data centers.  To reiterate, this discussion is just theory only.  Functionality dictates moving forward.


how to find out how long the last DBCC checkdb took to complete?

If it is scheduled, then getting it from the SQL Agent job's runtime duration is an option.

And if you're using Ola's solution (which I'd recommend) then his dbo.CommandLog table has StartTime and EndTime columns for all the actions it performs, including DBCC work.


Choosing the Most Recently Entered Non-Blank Value When Many Values Exist

What you're looking for is generally known as a "greatest of group" solution.  There are several patterns for this.  A search of dba.SE or SO will show you some.

The posted question and the SQL Fiddle have the table names swapped.  This code is from the Fiddle:

with NEWEST as
(
    select
        LOCATION_ID,
        MAX(LOCATION_TS) as MAX_TS
    from Location_detail
    where Location_name is not null
    and LOCATION_NAME <> ' '         -- or whatever your exclusion conditions are
    group by LOCATION_ID
)
select
    l.LOCATION_ID,
    ld.LOCATION_NAME,
    l.LOCATION_TYPE
from LOCATIONS l
LEFT OUTER JOIN NEWEST n
ON l.LOCATION_ID = n.LOCATION_ID
LEFT OUTER JOIN LOCATION_DETAIL ld
on ld.LOCATION_ID = n.LOCATION_ID
and ld.LOCATION_TS = n.MAX_TS;


How do I effectively handle very skewed data? Statistics up to date but don't seem to help

Community Wiki answer generated from comments left on the question

If you are running the query with @operator as a variable, SQL Server cannot 'sniff' the value in the variable, so it will use the statistics' average density value to calculate an estimate.  No matter what value you assign to the variable, this average value estimate will always be the same.

One way to resolve this is by using an OPTION (RECOMPILE) query hint. This will compile a fresh plan on each execution, with the plan optimized for the specific value in the variable at that time. This comes at the cost (usually small) of a statement recompilation each time.

You could also modularize the code.  You could use an IF statement and check the value of operatorid and if it's "user1" call one stored proc, let's say sp_user1. If it is not "user1" call a different procedure.  The first sp would be optimized for "user1" and the other for the rest of the value.  You could also use option (recompile) in the second sp for non-"user1" values if required.

It might also be a decent use-case for dynamic SQL. That would turn your @operator variable into a literal value, and would customize the plan for each user. Since there's only 7 users in that table I don't think that would really pose an issue for you.

For more information, see:

Local Variables vs. Parameterized Stored Procedures in SQL Server (Video) by Kendra Little


You need a string constant there, e.g. 'unpaid'.

Because all branches of a CASE expression must return the same data type, you will also need to cast the stat column to text as well (assuming it's some kind of number as you compare it to one)

case
  when rc.stat = 0 Then 'unpaid'
  when rc.stat = 1 Then 'paid'
  when rc.stat = 2 Then 'dispute'
  else rc.stat::text
end

As you only use = as the comparison operator, you can simplify that expression to:

case rc.stat
  when 0 Then 'unpaid'
  when 1 Then 'paid'
  when 2 Then 'dispute'
  else rc.stat::text
end


The IN operator expects a list of items, not a single string.
You need to structure your query as follows:

Select * from table where mid(postcode,1,2) IN ('CV','DE','LE','MK','NN');


Does anyone know where the SQL Server Connection Properties are stored?

The list of the servers are kept in the msdb database.

SELECT * FROM msdb.dbo.sysmanagement_shared_registered_servers

If you're wanting the connections and tabs to be the same, then you would need to go to Tools --> Import and Export Settings... and export the selected environment settings.
The two that would work for what you're asking for is under Options --> Environment. The two options would be "General" and "Tabs and Windows". It will export to a file. Send that to whomever will need it, and they can import it.


SQL Query question (items not having some value in a column)

I believe the following query will give you the right computer name

 select ComputerName from dbo.computers
 where os= 'windows 7'
 except
 select ComputerName from dbo.computers
 where os= 'windows 7'
 and appname not in (select appname from dbo.computers where os='windows 10')

The result is:


copy schema across different physical databases in db2

JDBC is not really going to answer your problem directly. It is a generic programming API for working with any database. So it can't possibly know about utilities that are specific to an implementation, be it DB2, Oracle, MySQL, etc.

I would look at using one of the utilities you mentioned above. That is their purpose. They assist with moving databases and/or data across environments. (There is also the restore redirect option as well, which is good if you are doing a backup/restore option).

If you insist on doing this through application code, then you will have to pass DDL through the JDBC API's (which dependent on your driver and your permissions, may or may not work). Or you would have to try to execute the DB2 utilities from "command line" through you program (look at the Runtime class to do this).

Again, I would keep this out of the application (perhaps you can share more of why you want to do this? That would help with answering your question). If you insist on doing it inside your application, you may also want to check out Stack Overflow, as you may get answers from a "programmatic" perspective.


This all depends on how exactly you are retrieving data from this clustered index.  The clustered index will be sorted by the leading key column, followed by sorting on each subsequent key column as defined in the index definition.  The best way to see this is through an example.

Test Object Setup
use TestDB;
go

if exists (select 1 from sys.tables where name = 'TestTable2')
begin
    drop table TestTable2;
end
create table TestTable2
(
    groupid int not null,
    ticketid int not null
);
go

insert into TestTable2(groupid, ticketid)
values
    (20, 56),
    (20, 87),
    (20, 96),
    (24, 13),
    (24, 87),
    (25, 5 );
go

Test 1 (groupid, ticketid) Key Column Order
Now let's create the clustered index defined on the key columns (groupid, ticketid):
alter table TestTable2
add constraint PK_TestTable2
primary key clustered (groupid, ticketid);
go

Now we want to look at how this data is stored in the page:
dbcc ind('TestDB', 'TestTable2', 1);
go
-- FileID:1 PageID:310

if exists (select 1 from tempdb.sys.tables where name like '#DbccPage%')
begin
    drop table #DbccPage;
end
create table #DbccPage
(
    ParentObject varchar(128) null,
    Object varchar(128) null,
    Field varchar(128) null,
    Value varchar(128) null
);
go

insert into #DbccPage
exec ('dbcc page(TestDB, 1, 310, 3) with tableresults;');
go

select
    Object,
    Field,
    Value
from #DbccPage
where ParentObject not in
(
    'page header:',
    'buffer:'
)
and Object not like 'memory dump%';

We see that it is in fact sorted by groupid first, then ticketid.  This will result in the follow query being an index seek:
-- this is an index seek
select *
from TestTable2
where groupid = 25;

And this query will be a scan because it can't seek on ticketid:
-- this is an index scan
select *
from TestTable2
where ticketid = 13;

Test 2 (ticketid, groupid) Key Column Order
But now let's change it up and swap the order of key columns:
alter table TestTable2
drop constraint PK_TestTable2;
go

alter table TestTable2
add constraint PK_TestTable2
primary key clustered (ticketid, groupid);
go

Now let's take a look at how the data is on the index page:
dbcc ind('TestDB', 'TestTable2', 1);
go
-- FileID:1 PageID:308

if exists (select 1 from tempdb.sys.tables where name like '#DbccPage%')
begin
    drop table #DbccPage;
end
create table #DbccPage
(
    ParentObject varchar(128) null,
    Object varchar(128) null,
    Field varchar(128) null,
    Value varchar(128) null
);
go

insert into #DbccPage
exec ('dbcc page(TestDB, 1, 308, 3) with tableresults;');
go

select
    Object,
    Field,
    Value
from #DbccPage
where ParentObject not in
(
    'page header:',
    'buffer:'
)
and Object not like 'memory dump%';

Now we see that the data is sorted by ticketid first, then groupid.  This will change the behavior of our test queries as follows:
-- this is an index scan
select *
from TestTable2
where groupid = 25;

-- this is an index seek
select *
from TestTable2
where ticketid = 13;

Summary
So as you can see above, it boils down to your workload and the kind of queries that are hitting this clustered index.  It's the different between being able to seek based off of the WHERE clause (or a JOIN) or scan depending on the sequential ordering of the key columns and how they are defined.


Copy large object field into remote PostgreSQL DB

I assume that the oid in question is the Oid of a large object, and you are wondering why the large object isn't copied when the oid field is copied.

Large objects cannot be copied via postgres_fdw. You'd have to use the large object API (or pg_dump) to move them from one database to the other. If the Oids are already taken on the second database, you'll have to use different ones and update the table accordingly.

Large objects are cumbersome to handle.


Update columns values using select from external database and table in MariaDB trigger

Your UPDATE statement is wrong. I've edited your TRIGGER and the modifications I did and errors I saw:

Errors:

You tried to drop a different TRIGGER that the one you're going to create. DROP TRIGGER IF EXISTS veevan.account_ai$$.
Why do you need the variable @TargetLastMod?. You didn't use it in the whole trigger.

Modifications:

I edited the DROP TRIGGER with veevan.address_vod__c_ai instead veevan.account_ai.
I used a JOIN with the same fields of your SELECT, with (address_vod__c.account_vod__c = NEW.account_vod__c AND  targets.veeva_account_id=NEW.account_vod__c) in the UPDATE.

TRIGGER:

USE `veevan`;
DELIMITER $$
DROP TRIGGER IF EXISTS veevan.address_vod__c_ai$$
USE `veeva_new`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `veevan`.`address_vod__c_ai` AFTER INSERT ON `address_vod__c` FOR EACH ROW
BEGIN
    DECLARE vCount INT(6) DEFAULT 0;
    SET vCount=(SELECT COUNT(*) FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c);
    IF (vCount>0) THEN
        SET @TargetLastMod=(SELECT targets.lastSyncAt FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c LIMIT 0,1);

        UPDATE pdone.targets
        JOIN veevan.address_vod__c AS avc ON (address_vod__c.account_vod__c = NEW.account_vod__c AND targets.veeva_account_id=NEW.account_vod__c)
        SET
        `address1` = avc.address1,
        `address2` = avc.address2,
        `city` = avc.city,
        `state` = avc.state,
        `zip` = avc.zip,
        `phone` = avc.phone,
        `fax` = avc.fax,
        `lastSyncAt` = NOW(),
        `updatedAt` = NOW()
        WHERE address_vod__c.account_vod__c = NEW.account_vod__c
            AND external_id_vod__c IS NOT NULL
            AND address_vod__c.primary_vod__c = 1
            AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veevan.recordtype WHERE name = 'Professional_vod');

    END IF;
END$$
DELIMITER ;

Try it!


Is it possible to generate table with generated data without manual INSERT statements?

You can use 3 CTE expressions to generate the data, then cartesian product them together.

Test table:

create table calendar
(
week integer,
time varchar(20),
venue varchar(20)
);

Weeks:

SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM weeks WHERE n < 10;

Venues:

SELECT 1 AS v
UNION ALL
SELECT v+1 FROM venues WHERE v < 5;

Times:

SELECT '11 am' AS t
UNION
SELECT '12 pm' AS t;

Put them all together to make a test query:

WITH weeks
AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM weeks WHERE n < 10
),
venues AS (
SELECT 1 AS v
UNION ALL
SELECT v+1 FROM venues WHERE v < 5
),
times AS (
  SELECT '11 am' AS t
  UNION
  SELECT '12 pm' AS t
)
SELECT n as week, t as time, 'Room '+CAST(v AS VARCHAR) as venue
FROM weeks, venues,times
;

Convert to an INSERT statement:

WITH weeks
AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM weeks WHERE n < 10
),
venues AS (
SELECT 1 AS v
UNION ALL
SELECT v+1 FROM venues WHERE v < 5
),
times AS (
  SELECT '11 am' AS t
  UNION
  SELECT '12 pm' AS t
)
INSERT INTO calendar (week, time, venue)
SELECT n as week, t as time, 'Room '+CAST(v AS VARCHAR) as venue
FROM weeks, venues,times
;


Is schema associated with login name?

No. A login is a server-level principal used to connect to the SQL Server instance. This does not infer any permissions or access to any databases unless a database user has been created and associated with the login, or high-privilege role membership (such as sysadmin) has been granted to the login.

  Is login name the same as 'user name' under SQL Server
  Authentication?

Not exactly. When you create a login (server-level principal) the field login name is used and can be either a SQL Authentication username or a Windows username (in the format DOMAIN\username). When you create a user (database-level principal) the user name field is used and represents the database username. This database-level principal can be linked to a server-level login or it can be a contained user that does not link to principals outside of the database. If it is contained, "user name" represents the username used to connect to the database as that principal. If it is linked to a server-level login then you still log in to SQL Server with the login name but that login is represented within the database as the username for the linked database principal.

  Let's say I login as username 'ITSupport', so any table I created will be sth like ITSupport.TableName, so I don't need to prefix ITSupport to access TableName. So what's my default schema name ITSupport or dbo

Default schema is set at the database user level and if your database user has permissions to create objects (such as membership in the db_ddladmin database role) and does not specify the schema then any objects created by that user where the schema is not specified will be created in the default schema for that user.

Note that a schema does not exist simply because a user was created. If you have not created a schema and set the default schema for your user, you will by default be using dbo and any non-prefixed object names will assume the schema is dbo.

The below script will create some example principals to illustrate the above points:

USE [master]
GO
-- Create a test database
CREATE DATABASE SecTest
GO

-- Create a login. This is a server-level principal that is used to connect to the SQL Server instance.
CREATE LOGIN [SecTestLogin] WITH PASSWORD=N'test1234!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE SecTest
GO

-- Create a schema for this user.
CREATE SCHEMA [SecTestSchema]
GO

-- Create a database user and associate it with the server login. Note the difference in names and the default schema specification
CREATE USER [SecTestUser] FOR LOGIN [SecTestLogin] WITH DEFAULT_SCHEMA=[SecTestSchema]
GO

-- Permissions setting to allow user to create objects
ALTER AUTHORIZATION ON SCHEMA::[SecTestSchema] TO [SecTestUser]
GO

ALTER ROLE db_ddladmin ADD MEMBER SecTestUser
ALTER ROLE db_datareader ADD MEMBER SecTestUser
ALTER ROLE db_datawriter ADD MEMBER SecTestUser

The below script will highlight some of the points above:

-- LOGIN AS THE LOGIN CREATED EARLIER

-- Create some tables. Note the prefixed vs non-prefixed tables
CREATE TABLE TableCreate1 (ID INT)
CREATE TABLE dbo.TableCreate2 (ID INT)
CREATE TABLE SecTestSchema.TableCreate3 (ID INT)

-- Show the tables and the schema they're in. Note the schema for non-prefixed table TableCreate1
SELECT s.[name] AS [Schema], t.[name] AS [Table]
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

-- Shows the database user and server login for the current user
SELECT USER_NAME() AS [Current Database Principal], SUSER_NAME() AS [Current Server Principal]


Select n random rows for each language ID in one table

Something like this, may be:

select language,
(select entry from entries e1 where e1.language = e.language order by rand() limit 1) entry
from (select distinct language from entries) e

dbfiddle link


I want SQL query for select multiple columns into one

One way to get the desired result is with UNION ALL of a SELECT query for each company column. Assign the serial number value in order by source column and company name using ROW_NUMBER in the outer SELECT.
INSERT INTO #Company(Company1, Company2, Company3)
VALUES
    ('ABC', NULL, NULL)
    ,('Test1', 'Test3', 'Test5')
    ,('Test2', 'Test4', 'Test6')
    ,('testing', 'testing2', NULL);

SELECT
      ROW_NUMBER() OVER(ORDER BY SourceColumn, CompanyName) AS SrNo
    , CompanyName
FROM (
    SELECT 1, Company1
    FROM #Company
    WHERE Company1 IS NOT NULL
    UNION ALL
    SELECT 2, Company2
    FROM #Company
    WHERE Company2 IS NOT NULL
    UNION ALL
    SELECT 3, Company3
    FROM #Company
    WHERE Company3 IS NOT NULL
    ) AS Companies(SourceColumn, CompanyName)
ORDER BY SrNo;
GO


MS-SQL: unique constraint case-insensitive

Yes a computed column would achieve this for you. Or you could create an trigger that will fire on insert or updates. Here is link to a question like yours on stackoverflow.


Is following way is right Database model for following problem?

Here are two different possible solutions.

Solution 1:

Solution 2:

For both models, Users is a parent table, with different tables subclassing it. This is achieved by the primary key of the child tables also being a foreign key to the Users' primary key.

Solution 1 involves having a list of possible user roles in the Roles table (Administrator, School, Teacher, Student) with the User having a reference to a role.

Solution 2 involves deriving the idea of the User's role from which child table the User belongs to. This would mean that a row belonging to Users must also belong to one of the child tables.

I personally like the first solution better, since it separates the idea of a Users' role in the portal from the User's position within the school environment.

As an additional note, I would look into "salted password encryption" for storing passwords, especially if this database is for a real application.


How to manually set Code Page and Collation for my SSAS Cube?

You have to change the collation settings either in your queries or in SSAS. There are several different options so the important question is do you use SSAS for other databases than the Croatian_CI_AS one.

If so you can pepper all your queries with collate Latin1_General_CI_AS to make sure that you get the data in the Latin1 collation into the cubes. If you are only using SSAS for the Croatian data, then change the SSAS collation to the same.


I would lean toward a single Category_Properties table with product_id, category_id, and optionally (if you need to retain the position of the property values in the original design) a sequence number of some sort.  You are going to have to do some programming to convert this, but it can be done with some 'brute force' SQL if you are trying to avoid scripting something.

CREATE TABLE Category_Properties
( product_id
, category_id
, sequence_value  /* this is optional */
, property_id
);

INSERT INTO Category_Properties
SELECT * FROM (
  SELECT product_id, 43 AS cat, 1 AS seq, <first_property_col> AS pval FROM category_properties_43
   UNION
  SELECT product_id, 43, 2, <second_property_col> FROM category_properties_43
   UNION
  SELECT product_id, 43, 3, <third_property_col> FROM category_properties_43
 )
 WHERE pval IS NOT NULL
;

-- From your example this would be <first_property_col> = 17, then 21, then 56.

You can do this provided you know the max number of property columns for each category, the outer query will eliminate the missing values for products which don't use all the property columns.  You can see why you might want to script this, rather than changing it for each new category.


Is it more efficient to union from sub-query or combine two outer queries?

You need to use the explain facilities in DB2 to answer this question, it's highly specific to the actual queries you are executing, and you can't get a general answer.

IBM i 7.2 (and previous versions) have Visual Explain.

If you are restricted from doing this, then you need to speak to the system or database administrator for your environment.


Physical memory usage approaching 100%(96%)

is there some kind of memory-leak problems here?

No, the problem is more someone who does not know how SQL Server works.

SQL Server uses as much memory as feasible as cache for the database. Why not use it? So, what happens basically is that SQ LServer has a lot of data in memory in case it needs it, to avoid accessing the disc.

If you need more free memory, set a memory limit in SQL Server properties.

If oot - be happy, your memroy is used.


Is it appropriate to use a timestamp as a DISTKEY in Redshift?

It's pretty corner-case, but there are cases where data location matters slightly.

Let's say you ask the database to do this query:

SELECT COUNT(DISTINCT ip)
FROM test_table
GROUP BY country

If the table is distributed by country, no network activity is needed (I tested this to confirm). For any other distribution style, the hash table will logically need to be re-distributed over the network (I also tested this to confirm).

That said, you probably want to just choose an EVEN distribution style to maximize the scanning speed. For that matter, maybe you want to use Spectrum for this use case.


This is a rather common problem, when the design has a "diamond" shape. See similar questions:
Many to Many and Weak Entities

With MySQL, I'd use something like this:

(0) Note that I prefer user_id as name for the primary key of users and not id for all the tables. I find the SQL code totally confusing otherwise (plus you can use the JOIN ... USING (tablename_id) syntax).

(1) The extra UNIQUE constraints in tables hosts and contacts are needed for the foreign keys from the domains table.
(2) The two FOREIGN KEY constraints from the domains table are changed to use composite keys (include the user_id).

Table users

CREATE TABLE users
( user_id INT NOT NULL AUTO_INCREMENT
, username VARCHAR(45) NOT NULL
-- other columns
, PRIMARY KEY (user_id)
) ;

Table contacts

CREATE TABLE contacts
( contact_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
, info TEXT
, PRIMARY KEY (contact_id)
, UNIQUE INDEX (user_id, contact_id)       -- added, see comment 1 above
, INDEX (user_id)
, FOREIGN KEY (user_id)
    REFERENCES users (user_id)
) ;

Table hosts

CREATE TABLE hosts
( host_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
, name VARCHAR(45) NOT NULL
--
, PRIMARY KEY (host_id)
, UNIQUE INDEX (user_id, host_id)          -- added, see comment 1 above
, INDEX (user_id)
, FOREIGN KEY (user_id)
    REFERENCES users (user_id)
) ;

Table domains

CREATE TABLE domains
( domain_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
, contact_id INT NOT NULL
, host_id INT NULL                       -- nullable based on comments
, name VARCHAR(45) NOT NULL
-- other columns
, PRIMARY KEY (domain_id)
, FOREIGN KEY (user_id, contact_id)       -- composite FK, see comment 2
    REFERENCES contacts (user_id, contact_id)
, FOREIGN KEY (user_id, host_id)          -- composite FK, see comment 2
    REFERENCES hosts (user_id, host_id)
) ;


Copy files using script to a network share

This is (part of) a script that I use to "archive" files that were processed by SQL Server. I actually mapped the network drive instead of moving to a UNC because it was just more stable.

A few things to note:

The service account under which the SQL Agent runs must have
permissions on both the source and the target (I'm assuming the
target would be the one you'd really need to check, unless the Agent
account can't delete from the local store).
This script MOVEs the files. If you want to copy them, then I'd recommend looking into replacing the command in the last step with XCOPY

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB',
       @type=N'LOCAL',@name=N'[Uncategorized (Local)]'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'File Mover',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=2,
            @notify_level_netsend=0,
            @notify_level_page=0,
            @delete_level=0,
            @description=N'No description available.',
            @category_name=N'[Uncategorized (Local)]',
            @owner_login_name=N'[username]',
            @notify_email_operator_name=N'alertteam', @job_id = @jobId OUTPUT

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Archive Files',
            @step_id=3,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'CmdExec',
            @command=N'move \source\path\*.csv \dest\path
    ',
            @flags=0


Restore differential backup error - no files are ready to rollforward

You have to restore the full with NORECOVERY and restore the diff with RECOVERY.

A differential is no good without the full backup that goes with it.  The differential backup contains the changed data 'since' the full.  To restore the diff, you have to restore the full.

Restoring the full with NORECOVERY allows you to apply future diff or log restores.  the very last restore of either a diff or log will need the with RECOVERY option to bring the database online.

Once you use the RECOVERY option on any restore scenario, you're done and no more restores can occur. The unfortunate thing is, RECOVERY is the default on a restore and if I had a dollar for every time someone in the world ran a long restore and forgot to use NORECOVERY only to find out that fact when they tried to apply diffs or logs, I would be on a beach somewhere and not on this site ;)


How to rename columns names in dynamically pivot table from other table?

You should replace CategoryID by CategoryName as column alias:

select group_concat(
           distinct
               concat('count(case when categoryid = ''',
                      p.categoryid,
                      ''' then value end) AS ',
                      pt.categoryname
                     )
                   ) as cases
from   points p
join   pointcategory pt
on     pt.categoryid = p.categoryid;

count(case when categoryid = '1' then value end) AS cat1,
count(case when categoryid = '2' then value end) AS cat2,
count(case when categoryid = '3' then value end) AS cat3

db<>fiddle here


2) Time/Date datatypes are often problematic choices for storing durations. Instead use an integer or decimal for whatever resolution is appropriate (seconds or milliseconds are popular, but minutes may work for you in this case).

1) In this instance, the simplest option may be: (DT_I4)TOKEN(Column,":",1) * 60 + (DT_I4)TOKEN(Column,":",2).

Other methods:

If you will always have two characters each for hours and minutes, you can use something like (DT_I4)SUBSTRING( [Column], 1, 2 ) * 60 + (DT_I4)SUBSTRING( [Column], 4, 2 ).
If hours can be 1 or more characters, but minutes is always two: (DT_I4)SUBSTRING(Column,1,FINDSTRING(Column,":",1) - 1) * 60 + (DT_I4)SUBSTRING(Column,FINDSTRING(Column,":",1) + 1,2)


Within a database, what you're describing is a table. Tables do not have order, but the data can be indexed to allow easier maintenance and retrieval.

You could always store data as a CSV list, XML, or a variety of other methods, but you should also consider what you're going to do with your data, and you're likely to find that a table ticks more boxes than most. If you're concerned about space, then compression strategies will help a lot, without losing the searchability.


Postgresql Streaming Replication on Windows failover

Like the documentation says, there are three ways to promote a standby server:

run pg_ctl promote on the command line.
create a trigger file (if you have configured the standby server accordingly).
from v12 on, you can also execute

SELECT pg_promote();


Is there a difference between an xtrabackup taken with and without --safe-slave-backup?

As the manual says:

  this option stops the slave SQL thread and wait to start backing up until Slave_open_temp_tables in SHOW STATUS is zero. [...] the SQL thread will be started and stopped until there are no open temporary tables

The reason for this is because Percona Xtrabackup basically mimics a controlled crash/shutdown of the server, and temporary tables can make the slave inconsistent, as you can see on the MySQL manual. This is not a problem of consistency per se (buckups will be consistent with the given timestamp/binlog), but it may make a slave lose some transactions when resynchronized with a master (typical usage- cloning a slave for creating another).

This does not happen if you use ROW-based replication, so I would recommend you using that. But some people cannot or do not want to use it, so this is the way to be 100%  sure that new slaves work well. On a typical replication scenario, using --safe-slave-backup may not be very problematic, assuming there are not many temporary tables being created, but that is the workaround (and typically these options are added because someone had a problem in the past).

What I would recommend you is to use always --slave-info unless you are using GTID replication.

As the own manual says, using pt-table-checksum to test the backups is a good piece of advise.


column precision exceeded. ORA-01438 is so unmeaningful

It would indeed be nice if Oracle provided more information for this error.  Until then here is an idea.  You could call a package to do the insert and the package could verify each of the values separately as follows:

drop table t1;
create table t1 (c1 number(3), c2 number(2), c3 number(1));

CREATE OR REPLACE PACKAGE p1 AS

   Procedure InsertData(iValue1 In Number, iValue2 In Number, iValue3 In Number);

END;
/

CREATE OR REPLACE PACKAGE BODY p1 AS

Procedure InsertData(iValue1 In Number, iValue2 In Number, iValue3 In Number) Is
   vValue1 T1.C1%Type;
   vValue2 T1.C2%Type;
   vValue3 T1.C3%Type;
   ePrecisionExceeded Exception;
   PRAGMA EXCEPTION_INIT(ePrecisionExceeded, -1438);
Begin
   INSERT INTO T1 VALUES (iValue1, iValue2, iValue3);
Exception
   When ePrecisionExceeded Then
      vValue1 := iValue1;
      vValue2 := iValue2;
      vValue3 := iValue3;
End;

END;
/

execute P1.InsertData(100,102,103);

The check is only done when the exception is raised, so the only overhead is the variable declaration.  You will know which value caused the exception based on the line number.  If this were the only reason to move logic into a package it might not be worth it, but since there are many other benefits, it is worth considering.


Lots of short term blockings on SQL Server 2008-R2 database

This:

wait_type  wait_time_ms_per_sec
PAGELATCH_EX    733024.4
PAGELATCH_SH    328846.4
CPU_USED    7131.4
WRITELOG    6484.4

Shows that you have a latch contention problem, and only a latch contention problem.

Since

  @ below was run 15 mins after we did node failover

The CPU_USED row should be accurate.  And so your workload is only managing to use 7sec of CPU time per second.  This is like only running on 7 cores.  In the mean time you have 1000sec of page latch time, meaning that you have, on average 1000 sessions waiting to latch a page, and only 7 performing useful work.

You are also running very old software on a very large server.  The throughput on this server is pretty high, suggesting that the operation it supports is important.  Also consider your username. So you should consider, strongly, engaging Microsoft Support or a partner with expertise in this sort of thing.

That said, the next step is to identify the source of your latch waits.  Generally these are either on a hot page of a table in your database, or a system page in TempDb.  The sys.dm_exec_requests.wait_resource should tell you which.

If tempdb, then Recommendations to reduce allocation contention in SQL Server tempdb.

If a table in your database, then post the DDL (including indexes) for the table and a the details of the queries that are blocking.

There is a whitepaper on diagnosing and resolving latch contention on SQL 2008 here: Diagnosing and Resolving Latch Contention on SQL Server


I'm guessing you're getting the unique constraint violation on the deleted_orders table because you are running this multiple times for the same ONO.  To prevent subsequent calls using the same ONO from getting a unique constraint violation, you can either modify the select query to not find them like this:

SELECT * FROM ORDERS
  WHERE ONO = PARAM_ONO
  AND ONO NOT IN (SELECT ONO FROM DELETED_ORDERS);

If instead you want feedback when this occurs rather than just ignoring the duplicate, you can nest the INSERT into a BEGIN...EXCEPTION...END block that catches the constraint violation and handles it by doing a DBMS_OUTPUT.


How to group list of start times by a category?

Using the newly added LAG() and LEAD() functions makes it look easy:

WITH a AS
    ( SELECT PersonID, FloorNum, EnterTime,
             PrevFloorNum = LAG(FloorNum) OVER
                                (PARTITION BY PersonID ORDER BY EnterTime)
      FROM RoomAssignments
    )
SELECT PersonID, FloorNum,
       FromTime = EnterTime,
       ToTime = LEAD(EnterTime) OVER
                    (PARTITION BY PersonID ORDER BY EnterTime)
FROM a
WHERE PrevFloorNum <> FloorNum OR PrevFloorNum IS NULL
ORDER BY PersonID, FromTime ;

Test in SQLfiddle


MariaDB User Define Variable throws warning if it is empty

After bit of R&D found below answer AND FIND_IN_SET works fine.

SET @some_var='';
SET @some_var:=(SELECT sids
  FROM fsettings
 WHERE cid=1
   AND tid=9
   AND tp=1
   AND IsDelete=0);

SELECT name
  FROM `tlivewsts`
 WHERE FIND_IN_SET(id ,@some_var )
 ORDER BY FIND_IN_SET(id ,@some_var );

I hope this solution is useful to some needy like me :)


How to get to know that db files are inaccessible?

If your file system is dropping out, that is a major problem. What if your database is partway through committing a transaction? This could cause corruption and potential data loss. This would be my first reaction.

If this is an issue in your test system only, and you have verified that the underlying file system fault is localized (IE definitely not affecting production systems) my feeling is that a SQL Agent job with step 1 configured to check for the physical files on disk could do the job.

Step 1 could consist of something like the following powershell:

if (Test-Path $path) {
   Return
} else {
   Throw "File doesn't exist"
}

The above code will throw an error if the file in the $path variable doesn't exist.

Step 1 on failure would move to step 2, but on success would quit the job reporting success. Step 2 would then be whatever action you wish to take.


Nice idea. I suggest two minor simplifications:

('{Foo,Bar,Poo}'::text[])[ceil(random()*3)]

Simpler syntax using an array literal ('{Foo,Bar,Poo}'::text[])
Shortens the string for longer lists.
Additional benefit: explicit type declaration works for any type, not just for text. Your original idea happens to output text, because that's the default type for string literals.
Use ceil() instead of floor() + 1. Same result.

OK, theoretically, the lower border could be 0 precisely, as hinted in your comment, since random() produces (quoting the manual here):

  random value in the range 0.0 <= x < 1.0

However, I have never seen that happen. Run a couple of million tests:

SELECT count(*)
FROM   generate_series(1,1000000)
WHERE  ceil(random())::int = 0;

-> SQLfiddle

To be perfectly safe, though, you can use Postgres custom array subscripts and still avoid the extra addition:

('[0:2]={Foo,Bar,Poo}'::text[])[floor(random()*3)]

Details under this related question on SO.

Or better yet, use trunc(), that's a bit faster.

('[0:2]={Foo,Bar,Poo}'::text[])[trunc(random()*3)]


You can use AES 256 in SQL Server, but that does not guarantee that you will be able to decrypt your data. If the "key" you have is a string used to create the key, then you might be able to use the built-in DECRYPTBYKEY function. I emphasized "might" since there is an optional second value that can be used to encrypt data (in SQL Server it is called the "authenticator"), and if it was used when encrypting the data, then it must also be used when decrypting.

The following example uses a plain text phrase as KEY_SOURCE and does not use an authenticator:

USE [tempdb];

-- DROP SYMMETRIC KEY [test_key];
CREATE SYMMETRIC KEY [test_key]
WITH ALGORITHM = AES_256,
     KEY_SOURCE = '{your "key"}'
ENCRYPTION BY PASSWORD = 'weak';

OPEN SYMMETRIC KEY [test_key]
   DECRYPTION BY PASSWORD = 'weak';

DECLARE @Encrypted VARBINARY(8000);
SET @Encrypted = ENCRYPTBYKEY(KEY_GUID(N'test_key'), 'test me!');

SELECT @Encrypted AS [EncryptedValue],
       DECRYPTBYKEY(@Encrypted) [DecryptedBytes],
       CONVERT(VARCHAR(500), DECRYPTBYKEY(@Encrypted)) AS [DecryptedText];

CLOSE SYMMETRIC KEY [test_key];

Returns:

EncryptedValue                   DecryptedBytes        DecryptedText
0x00A6E1C5C3035A45BC33EEFA...    0x74657374206D6521    test me!

IF, on the other hand, you have the actual binary key itself, then that will not work with the built-in T-SQL functions. In this case you might be able to use SQLCLR in order to use the AesManaged Class in .NET which accepts a 128 bit (16 byte), 192 bit (24 byte), or 256 bit (32 byte) key. Of course, this requires a second value, the "initialization vector (IV)", and I don't know if you have that, and I haven't been able to get it to work when not supplying a value for it. And while it might not matter, there is a "Padding" property that has a few different possible values and it is always possible that Guidewire used a different setting than the default .NET setting, so it could require a bit of trial and error.


DBCC CHECKDB returning space issue error

The message is telling you why it failed, here is the root cause of the failure:

  Write to sparse file 't:\test_audit23.ndf_MSSQL_DBCC6' failed due to lack of disk space.

Looks like the T volume runs out of space.

  I am not sure why it would need to write to this disk.

A hidden snapshot is taken and crash recovery run on the databases to bring it to a consistent state so that checkdb can run against the hidden snapshot copy of the database. This is how it works when checkdb is run online. There is the tablock option which is called 'offline' as it takes locks where the online way doesn't.

You are mostly correct. It doesn't force all writes to a single data file, however it is recalculating the proportional fill values which leads to hotspotting in a few number of files. This is known (though it seems no one cares) and that's why we had trace flags to grow the entire filegroup all together (TF 1118). This was changed and is now a database option in 2016.

Manually growing the data files each week seems excessive. If you know it's just going to grow by another TB each week, why grow it so low? If Performance Volume Maintenance Tasks is given to the service account or service sid you should be able to grow the data files fairly large with little impact.

  My question is can I try turning auto-grow off on any data files on the T drive, and try again?will it cause any issue or is there any other way.

Autogrow settings aren't your issue, sparse writes and running out of space on the volume, is.

Update:

  so what will be best way to deal these type of issues.shall I add more space?

Maybe. Depends.

If the datafiles are already on monolithic volumes, then I'd work on migrating them off the same volume. You can use mount points, storage spaces, etc., but having available space is what you need. Again, this has nothing to do with your autogrow settings and everything to do with the amount of free space available and the amount of space required to make the snapshots consistent.

What else could you do?

Well, you don't have to run CheckDB() all at once. You can break it up into smaller bite sized chunks of checks, things like using checkalloc, checkcatalog, checktable, etc. See the link here, I don't need to remake the wheel as Paul already has great articles.

  and in this case more ndf files are there then why it is writing in 23rd ndf files ,it should write to 28.ndf in X drive

The root cause of the issue is that you're unfamiliar with how CheckDB works, and that's okish. Since you know there is now a level of understanding needed, check the link I gave and read Paul's posts.

The snapshot files will be made for each data file, it's a snapshot and not actually writing to the database itself. When you read Paul's series I believe you will understand what and why - along with this answer.


The use of NOT logic in relation to indexes

NOT IN (SELECT ...) and NOT EXISTS (SELECT .. WHERE correlation..) are "Anti Semi Joins". That is, recognised set based operations
WHERE NOT (MyColumn = 1) is a filter that requires all rows to be looked at

For more info, see:

Craig Freedman's "Introduction to Joins"
Wikipedia "Relational algebra, Antijoins"

Edit: for completeness

LEFT JOINs often perform worse. See http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server

This same site notes that in MySQL, NOT EXISTS isn't optimised like other RDBMS and LEFT JOIN is better

In SQL Server, I know from experience that LEFT JOIN doesn't run as well as NOT EXISTS. You also often need DISTINCT to get the same results which another processing step.


It's very likely that when using * to select all columns in your subquery, you have 2 columns with the same name ITEM_CODE, one from stock_item_detail table and one from stock_daily_data table.

I suggest you explicitly list all the columns you want for this subquery and you should be fine.

I think something like the code below should do the trick:

INSERT stock_daily_data (item_code,date,quantity_used,stock_left)
select distinct
    item_code,
    '2017-11-07',
    0,
    stock_left
from (
    select
        stock_item_detail.item_code   -- I assume that these are the columns you want
        , stock_daily_data.stock_left
        , row_number() over (partition by stock_daily_data.item_code order by stock_daily_data.date desc) as rn
    from stock_item_detail
        join stock_daily_data
            on stock_item_detail.item_code = stock_daily_data.item_code
      ) tt
where rn = 1;


Is there an easy way to copy one database to another

for large database you need to use command line to (clean) dump local database and after that in remote server to import (with ssh connection and also command line mysql-import or directly after connect to mysql), you need to sepcify your environment (windows, mac..)

some sources:

-http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

-http://www.lullabot.com/blog/importexport-large-mysql-databases


Is there a way to administratively substitute the queries submitted by the application for other ones?

No, the closest you will get with out of the box functionality is what a plan guide can do.

  I am imagining some sort of regex match performed against the batch if the application name matches.

Nothing native inside of SQL Server can do this, currently.

  Can I edit the batches received by SQL Server on the fly?

Unfortunately (or fortunately) there is nothing built in to do this. I don't want to give you any ideas but you'd need to have a middle layer between the application and the database server. This intercepting application would need to understand TDS (which is the protocol used with SQL Server) and allow you make changes to (or, again, not) the TDS packets to change the queries on the fly.

If you made this, you should sell it :)


MySQL 5.5 : (InnoDB) - Manually edited an .ibd file to test recovery, now entire MySQL installation is broken!

Following these steps probably offers a decent shot at making the MySQL instance happy again...

recreate the database's directory that you deleted
create an InnoDB table with the same exact column names and definitions and the same table name, but in a different schema (or on a different server)
copy only the .frm file for that table into the database's directory, not the .ibd file
be sure the file and directory permissions are correct
start MySQL normally (without innodb-force-recovery)
DROP TABLE database_name.table_name;

The server should then respond by removing the .frm file, removing the table from InnoDB's internal data dictionary, and return a simple warning that the .ibd file was missing.

You'll have to repeat the process for all of the tables, but if you have the schema on another machine (or identical tables in another schema on this one, already) you should be able to copy all the .frm files over in one shot, start up, then drop them all.

But don't try opening them, don't "USE database_name", and don't connect with a graphical client that tries to enumerate the tables.  Start the command line client with the --no-auto-rehash option so it doesn't try to enumerate them in the background, either.

This should let you drop the tables, at which point, you may be stable again.


How to get MSSQL Type definition from system tables?

You need to query sys.table_types:

SELECT
    SchemaName = SCHEMA_NAME(TYPE.schema_id),
    TypeName = TYPE.name,
    ColumnID = COL.column_id,
    [Column] = COL.name,
    DataType = ST.name,
    IsNullable = COL.Is_Nullable,
    Length = COL.max_length,
    Precision = COL.[precision],
    Scale = COL.scale,
    Collation = ST.collation
FROM
    sys.table_types TYPE
    INNER JOIN sys.columns COL ON TYPE.type_table_object_id = COL.object_id
    INNER JOIN sys.systypes AS ST ON ST.xtype = COL.system_type_id
WHERE
    TYPE.is_user_defined = 1
ORDER BY
    SchemaName,
    TypeName,
    ColumnID

Make sure you are connected to the proper database.

If you want a CREATE TYPE statement like the one from SSMS, you will have to build it from the results of the previous query. That or trace the SSMS's query to see how it builds it when you ask it from the GUI, but there's a chance it's very convoluted (although bulletproof).


In the $scriptingoptions collection, you are explicitly setting on the Queue reader agent export.  Change this line:

$scriptOptions.IncludeCreateQueuereaderAgent = $true

to

$scriptOptions.IncludeCreateQueuereaderAgent = $false

If you want help with the module you are using, you need to show the code you are using to generate the scripts.


Why is SQL doing an index scan and seek with the same XML query structure?

XML index in SQL Server is implemented as an internal table that is a persisted version of the node table that is much the same as the XML shredding functions produce.
One of the columns in the internal table is called hid and that column contains the value used in the seek for a path expression. The value is an ordpath value. When you create a path xml index you get a non clustered index with hid as the leading column.
The ordpath for a specific element name is generated when SQL Server inserts XML data to the table. To encode the path, the different ordpath values are concatenated.
Simplified the path X/Y/Z could be represented as 1.2.3 and Y/Z/Z as 2.3.3.
SQL Server has to have a way to generate the same ordpath value for the same element name. That could be done with some kind of dictionary or table that keeps track of all generated ordpath values for one XML index. I'm a bit fuzzy here because I have not found anything anywhere that says this is how it is done and I have not found where it is stored.
Inserting one row with this XML
<X>
  <Y>
    <Z />
  </Y>
</X>

will give you this content in the internal table with one extra column added by me to show the path expression encoded in hid
id      hid     pk1
0x              1
0x58    À€      1     Z
0x5AC0  Á€À€    1     Y/Z
0x5AD6  Â€Á€À€  1     X/Y/Z

You can look at the internal table using a DAC login.
What happens when you query for a path expression (and here I get a bit speculative again) is that the same dictionary/lookup table that is used to make sure the elements with the same name get the same ordpath value is used to find the hid to use for the index seek in the internal table. The path expression in a XQuery is always static so the hid value used for the query can be stored in the query plan and resused.
A query for a node that exists get a query with a seek on the hid where Î is the hid value to search for.
<SeekKeys>
  <Prefix ScanType="EQ">
    <RangeColumns>
      <ColumnReference Database="[yy]" Schema="[sys]" Table="[xml_index_nodes_658101385_256000]"
                       Alias="[NodeThatExist:1]" Column="hid" />
    </RangeColumns>
    <RangeExpressions>
      <ScalarOperator ScalarString="'Î'">
        <Const ConstValue="'Î'" />
      </ScalarOperator>
    </RangeExpressions>
  </Prefix>
</SeekKeys>

A query for a node that does not exist gives you a very different way of locating the rows.
select count(*)
from dbo.T
where T.X.exist('NodeDoesNotExist') = 1

The compute scalar uses some functions to calculate values that are checked against in the filter operator.

Some of the tests I did to get here can be found here. Note that you don't actually need a generated hid for a element in the index to get the seek plan, you just have to try to insert it.
As a side note, the rewriting of the query to use one way or the other to find the rows of a XML query is done before the query optimizer starts to do its job so if you get a seek or not also depends on cardinality on the hid column in the internal table.

Paul White mentioned something in chat about where the element names inserted are stored:

QNames are stored in sys.sysqnames. The XML algebrizer requires an
existing known QName to generate an optimized path transform. i.e. to
use a path index. Entries are added to sys.sysqnames in a system
transaction that does not feel user transactions


Can I do a standard restore/recover using image file backups?

I believe the answer is Yes.  Here is the supporting evidence:

oracle-base.com - Incrementally Updated Backups: Rolling Forward Image Copies Using RMAN


Do not use LOCK TABLES with InnoDB unless you have a very special need.

Instead, learn about "transactions" and use BEGIN...COMMIT to effect most "locking" needed.  See also SELECT ... FOR UPDATE....


dbatools - Copy all jobs to the other node - selected jobs only

The beauty about dbatools is that the source code is available on github

you can use -ExcludeJob parameter

  .PARAMETER ExcludeJob
              The job(s) to exclude. This list is auto-populated from the server.


How to change owner of a table within a PostgreSQL Database on Azure?

That doesn't seem to be specific to Azure. It's a general rule in Postgres: Only the owner or a superuser can use ALTER TABLE.

To verify your role is a superuser (or not), run in the current session:

SELECT rolsuper FROM pg_roles WHERE rolname = current_user;

Related:

Give particular privileges for a table to existing role


Allow access for localhost and named server

You could use this :

GRANT ALL PRIVILEGES ON *.* TO 'username'@'ip_testserver1', 'username'@'localhost' IDENTIFIED BY 'password';

Of course, feel free to replace the ALL PRIVILEGES by the rights you want to allow to your user, and the *.* with the database name & tables name.


Use the -v (or --verbose) option for mysqdump which can show the errors that occur


Postgres 9.3: How to update row that has max value of a timestamp in addition to other selection criteria?

UPDATE appointments SET recurring = 0
WHERE id = 433
AND time = (select max(time) from appointments where id = 433);

... is the answer. You just needed brackets.


Sort by Start Date Desc if End Date is null, if not Sort by End Date

You can use a CASE expression in the ORDER BY:

ORDER BY CASE WHEN EndDate IS NULL 0 ELSE 1 END ASC
    , EndDate DESC
    , StartDate DESC

Sort criteria in order (link):

Values with a NULL EndDate will get a 0 and others will get a 1. This is used as the first sort criteria.
Then the second criteria is EndDate.
Finally ties will be sorted by StartDate.

You can play with ASC and DESC and column order if the global order is not what you expect.

Sample data and query:

DECLARE @data TABLE(id int identity(0, 1), StartDate date, EndDate date)
INSERT INTO @data(StartDate, EndDate) VALUES
    ('20160101', '20160105')
    , ('20160101', null)
    , ('20160101', '20160107')
    , ('20160102', '20160103')
    , ('20160102', '20160105')
    , ('20160102', null)
    --, (null, '20160105')
    --, (null, null);

SELECT *
FROM @data
ORDER BY CASE WHEN EndDate IS NULL THEN 0 ELSE 1 END ASC
    , EndDate DESC
    , StartDate DESC

Output:

id  StartDate   EndDate
5   2016-01-02  NULL
1   2016-01-01  NULL
2   2016-01-01  2016-01-07
4   2016-01-02  2016-01-05
0   2016-01-01  2016-01-05
3   2016-01-02  2016-01-03

This ORDER BY clause (link) will put double NULL (Start + End) at the end:

ORDER BY CASE
          WHEN EndDate IS NULL AND StartDate IS NULL THEN 2
          WHEN EndDate IS NULL THEN 0
          ELSE 1 END ASC
    , EndDate DESC
    , StartDate DESC;

id | StartDate            | EndDate
5  | 2016-01-02 00:00:00  | NULL
1  | 2016-01-01 00:00:00  | NULL
2  | 2016-01-01 00:00:00  | 2016-01-07 00:00:00
4  | 2016-01-02 00:00:00  | 2016-01-05 00:00:00
0  | 2016-01-01 00:00:00  | 2016-01-05 00:00:00
6  | NULL                 | 2016-01-05 00:00:00
3  | 2016-01-02 00:00:00  | 2016-01-03 00:00:00
7  | NULL                 | NULL


Oracle 12cR2 - How To Create DDL Script Of A View With All Dependencies and Permissions

That would require selecting from dba_dependencies. Since your dependencies may have their own dependencies that query would probably need to be recursive.
Also, if any of the dependencies are in a different schema then you'll also need the grants to go with them which you can get by selecting from dba_tab_privs and dba_col_privs.


Should data warehouse provide data for data composition for real time business transactions

You are correct.
The reason for this is the current situation, in which the transactional database is usually separate from the analytical database, and therefore isn't up to date.
While up to date technologies like Data Vault could mean that you would use the same database for transaction and analytics, a situation in which you need to aggregate data from more than one data source could still means that you need ETL process, and therefore your analytical data isn't "up to date ".
So: analytical database gives you changes accross time, but if you want to know what goes on right now, use transactional database.


With a multi-field index, you need to make sure each field in the index is used from left to right in the WHERE clause, otherwise the index will not be applicable.
If we assume that your index speedtest_id_2 is the only index you have (except for the primary key), then the following query will use the index

SELECT count(*) as rang_pos
FROM results
WHERE results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR)
AND results.speedtest_id = 1
AND results.checked = 1
AND results.typing_mode = 1
AND results.keystrokes <= 500

Whereas the following query will not use that index.

SELECT count(*) as rang_pos
FROM results
WHERE results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR)
AND results.checked = 1
AND results.typing_mode = 1
AND results.keystrokes <= 500

as the speedtest_id field which is first in the index is not part of the WHERE clause.

With your combined index, I would add keystrokes to the index before the created field, as that will help reduce the data being read through when calculating the output. Also use the EXPLAIN command to view the query plan that MySQL is going to use when running the query.

EXPLAIN SELECT count(*) as rang_pos
FROM results
WHERE results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR)
AND results.speedtest_id = 1
AND results.checked = 1
AND results.typing_mode = 1
AND results.keystrokes <= 500

You may find that rearranging the fields in the multi-field index will change the query plan, and thereby the performance of the query.

Your speedtest_id single field index is not relevant, as the combined index has that field as the first field, so will be used even if you only filter on that one field.

Whether you leave the other individual field indexes in or remove them is entirely down to how your application works, and what other queries are run against it - you may find that they are all used at some point or another when your application runs.

You can leave your application running for a while, and then examine the following tables (if you have the sys schema installed) to see how the indexes are being used.

select * from sys.schema_index_statistics where object_schema = '{database}';
select * from sys.schema_unused_indexes where object_schema = '{database}';

This will give you an idea of which indexes are not used by the application.


You should use LIKE comparison operator in this way:

SELECT *
FROM   vnd_imovelfoto
WHERE  codigofoto LIKE '2950%'


Optimizing a query which runs through all hours in a day

Just an idea: create a table with all hours in the day (0-23).

create table hours(
  hr integer
);

Then find all hours that have snapshots for the given camera_id and date (of course, you will need to substitute your own camera_id and date into the query):

select h.hr, 1 as camera_id
from hours h
where exists (
    select 1
    from snapshots s
    where s.camera_id = 1
    and s.created_at between to_timestamp ('2015-05-01 ' || to_char(h.hr, '00') || ':00:00', 'YYYY-MM-DD HH24:MI:SS')
                             and to_timestamp ('2015-05-01 ' || to_char(h.hr, '00') || ':59:59', 'YYYY-MM-DD HH24:MI:SS')
)

See this on SQLFiddle. To return a count(*) from the table the database has to go through all the rows that satisfy the condition to count them; exists() should limit this to finding the first row that satisfies the condition.


How much memory will allocated for a mysql view

When views use the TEMPTABLE algorithm, the memory allocated will be relative to the size of the temp table, which depends on the number of rows and lengths of columns.

I don't know any more specific answer than that.


Find an object cause to invalid package in Oracle

After the tables were dropped, views related to dependencies no longer contain information about the dropped table, but you can query DBA/ALL/USER_ERRORS.

SQL> create table t1 (c1 number);

Table created.

SQL> create or replace procedure p1 as
  2    l_c number;
  3  begin
  4    select count(*) into l_c from t1;
  5  end;
  6  /

Procedure created.

SQL> select status from user_objects where object_name = 'P1';

STATUS
-------
VALID

SQL> drop table t1 purge;

Table dropped.

SQL> select status from user_objects where object_name = 'P1';

STATUS
-------
INVALID

At this point, the procedure becomes INVALID automatically, but you can not query errors related to it yet:

SQL> select * from user_errors where name = 'P1';

no rows selected

So compile it, and query again:

SQL> alter procedure p1 compile;

Warning: Procedure altered with compilation errors.

SQL> select * from user_errors where name = 'P1';

NAME TYPE        SEQUENCE       LINE   POSITION TEXT                                            ATTRIBUTE MESSAGE_NUMBER
---- --------- ---------- ---------- ---------- ----------------------------------------------- --------- --------------
P1   PROCEDURE          1          4         33 PL/SQL: ORA-00942: table or view does not exist ERROR                  0
P1   PROCEDURE          2          4          3 PL/SQL: SQL Statement ignored                   ERROR                  0

Now you could view the source of P1 at line 4, position 33, and check what if refers to.


Keep the foreign key constraint using soft delete

You could require the UPDATE to reference a subquery that will return no results if references exist. Then, if 0 rows are updated, you can raise a message to the UI to report an error.


Looking at a subset of the deadlock information:

This shows that session 342 is currently executing line 47 of the stored procedure dbo.SP2, which is a DELETE statement on dbo.Table2.

Somewhere prior to that in the procedure, this session has already acquired an eXclusive (key) lock on dbo.Table1.  Based on the non-zero "log used" value, it seems like this procedure does some writes to dbo.Table1, thus acquiring the X lock and creating the potential for deadlocks.

The takeaway here is that the deadlock XML is only showing you the currently executing statement (Erik Darling has some great demos of this and other confusing things about deadlock graphs on his blog).  It appears that there are multiple statements being executed in a transaction in this stored procedure.

Another possibility is that there is a trigger on one of more of these tables that's causing more locks to be taken than what is obvious from the T-SQL statements themselves.

  Also, if I had the FK, why should need check the PK in table1 if I am just deleting from table2?

You're right, even if the foreign key existed on Table2, locks on Table1 would not be required to delete rows from Table2.  Locks would be required if the FK was defined "backwards" and cascading deletes were enabled.


Azure SQL database DTU compute utilization maxing out

the top offender represents only 0.3% CPU

A DTU is a package of CPU and IO time, so you most likely have queries performing a lot of read IO (index or table scanning most likely) or write IO.
The sudden change could be because of:

A new release of the application that has new SQL activity that hasn't been tested on data scaled/shaped like your production data. Look for long-running statements that consume a lot of IO. The solution here will be either in improving those queries and/or the indexing choices to support them.
Significant change in your data size (suddenly or over time) meaning that your common working set no longer conveniently fits in RAM meaning that the buffer pool is thrashing causing extra IO work.
Significant change in your data shape (suddenly or over time) meaning that the query planner is having a harder time choosing an optimal path. Refreshing index statistics can sometimes help with this if it has not been done for a large amount of time.
If your application allows users to define reports or filters they may have created something that has the same effect as point 1 (you'll probably detect this in the same way).
A sudden influx of active users, or existing users suddenly becoming more active. Check your application logs for that.

Or some mix of the above.
You do not state what service level your database is as currently (service tier, service level, is it in a pool, if so the tier/level of the pool and per-DB setting) - it would be a good idea to edit that into your question.
For a short term fix, bump up the DTU allocation for that database. If the load is mainly IO then consider moving from a Standard to a Premium tier (while they don't publicly document exactly how a DTU is composed/judged, a premium DTU is said to have an order of magnitude more IO then a standard one so going from standard-100 to premium-125 can sometimes be far more effective than moving from standard-100 to standard-400 but cost less).
Update after Extra Information in Question

the top offender ... data IO of 0.23%

You screen-grab is of the display sorted by CPU - so the top offender for CPU use is apparently not using much IO. But if you are looking for processes that are causing a lot of IO then you need to sort by IO as that may not also be consuming CPU (your CPU allocation could be sat waiting for the IO to complete).
Though from that display is would be worth checking that the behaviour of those four is as good as it could be, particularly the fourth which is taking an average of 22.5 minutes per execution. That could be due to locking issues hence not showing much CPU or IO activity though in any case it is worth investigation.

over the past week.

Your problem, according to the top graph, only started a couple of days ago so looking at the full week is not likely to be all that meaningful. The performance insights tab allows you to zoom in more finely than that.


TempDB Version Store used by DB STARTUP background process

I checked if in other enviroments the same query on dm_tran_active_snapshot_database_transactions returns the same results and no! It isn't normal having DB STARTUP snapshot active from days.
I checked the errorlog of 13 days ago and I found errors about an unexpected shutdown and failover.
So I tried to remove the database with DBID 7 from the availability group and I re-run the two queries: Now tempdb and version store are empty and the sessions with transaction_id 0 are gone.
I re-added the database to his availability group and I resolved the issue.


MySQL illegal mix of collations, ASCII to UTF-8

It turns out that I am doing everything correctly, and I'm not insane! This problem is a bug in MySQL 8.0.22 (also this) that is apparently fixed in 8.0.23. Unfortunately I'm using Percona server which has not yet released its 8.0.23 derivative.