Does SQL Server's serializable isolation level lock entire table

Escalation, though

Lock escalation under serializable isolation level may occur the same as it does with other isolation levels.

Correct indexes can help to avoid lock escalation up to a point
Locking many indexes will increase the likelihood of lock escalation; the count is cumulative across objects for a single statement

Some quick examples using a single table with a single index. Id is the primary key and clustered index on the table.

One Row

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

UPDATE c
SET c.Score = 2147483647
FROM dbo.Comments AS c
WHERE c.Id = 138; --One value

ROLLBACK

For a single Id value, locking is minimal.

+--------------+---------------+---------------+-------------+
| request_mode | locked_object | resource_type | total_locks |
+--------------+---------------+---------------+-------------+
| RangeX-X     | Comments      | KEY           |           1 |
| IX           | Comments      | OBJECT        |           1 |
| IX           | Comments      | PAGE          |           1 |
+--------------+---------------+---------------+-------------+

Multiple Rows

But locks will go up if we start working in ranges:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

UPDATE c
SET c.Score = 2147483647
FROM dbo.Comments AS c
WHERE c.Id BETWEEN 1 AND 5000; -- Small range

ROLLBACK

Now we have more exclusive locks on more keys:

+--------------+---------------+---------------+-------------+
| request_mode | locked_object | resource_type | total_locks |
+--------------+---------------+---------------+-------------+
| RangeX-X     | Comments      | KEY           |        2429 |
| IX           | Comments      | OBJECT        |           1 |
| IX           | Comments      | PAGE          |          97 |
+--------------+---------------+---------------+-------------+

Way More Rows

This will carry on until we hit a tipping point:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

UPDATE c
SET c.Score = 2147483647
FROM dbo.Comments AS c
WHERE c.Id BETWEEN 1 AND 11655; --Larger range

ROLLBACK

Lock escalation is attempted and is successful:

+--------------+---------------+---------------+-------------+
| request_mode | locked_object | resource_type | total_locks |
+--------------+---------------+---------------+-------------+
| X            | Comments      | OBJECT        |           1 |
+--------------+---------------+---------------+-------------+

Pay Attention

It's important to separate two concepts here: the isolation level will be serializable no matter what kind of locks are taken. The query chooses the isolation level, and the storage engine chooses the locks. Serializable won't always result in range locks -- the storage engine can pick whichever kind of locks still honor the isolation level.


Streaming Replication Failover - how to point second slave at new master?

In PostgreSQL 9.2, you should be able to just repoint server C to B by changing it's recovery.conf file, if you are also using log archiving (and have a restore_command defined in your recovery.conf). It should work automatically - but it does require the log archive to work (machine B will send critical information about timeline switching to the log archive, which machine C will then replay).

PostgreSQL 9.3 will be able to deal with this repointing of the slave over pure streaming replication.


How to avoid using the IN clause in sql query used in a stored procedure

Where exists is gnerally faster than IN

SELECT userID,
             Memberid
      FROM   Member
             INNER JOIN [USER]
               ON UserMemberID = MemberiD
      WHERE  EXISTS  (SELECT *
                        FROM   @dtUserIds a where [user].userId = a.Uniqueid)


`greatest` function makes the query run much slower

Look at the EXPLAIN PLAN and you will see the difference.
Oracle uses a cost based optimizer that takes into account the actual values that are used in the query to come up with the optimal execution plan.
I have not tested it in Oracle, but it is most likely that sysdate-1 is evaluated at compile/optimize time, and the optimizer is aware that your predicate only covers one day, and therefor it will make sense to use an index for example.
When you 'hide' the value in a function, the optimizer has no way to tell what value you use, as the function is only evaluated at execute time. If the general density of the column suggests that the average value will return many rows, and will benefit from a full table scan for example, you will get a full scan even for 1 day's worth of data, which may be less optimal for the value you are using.
As a rule of thumb - don't 'hide' predicate values from the optimizer by placing them in functions.


Oracle: summing column value with subquery?

You're unfortunately hitting bug 12999577 (Oracle support account required) or something close to it. And the fix for that bug introduced another bug 17564992.

The workaround indicated in the first bug report is to switch the conditions around, i.e. express your where clause as 0 < ( ... ).

The workaround indicated in the second is to set either of these:

alter session set "_fix_control" = '12999577:0';
-- or
alter session set "_optimizer_squ_bottomup"= FALSE;

I've tried your example on an 12.1.0.2 database that reproduces the behavior you are seeing (including returning the expected result for the >=0 and >-1 cases). The workarounds suggested for that second bug appear to work and give the expected result. But do verify the bugs yourself, and seriously consider applying the bugfix rather than these parameters. I'd suggest opening an SR with Oracle if you want confirmation for your particular version.

Interesting difference in the explain plans. With the _fix_control (correct result):

--------------------------------------------------------------------------------
| Id  | Operation       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    13 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL  | FOO      |     1 |    13 |     3   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE    |          |     1 |       |        |          |
|   3 |    TABLE ACCESS FULL| EMPTYTAB |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("F"."X"+ (SELECT COUNT(*) FROM "EMPTYTAB" "EMPTYTAB")>0)

Without the _fix_control (no rows returned):

-------------------------------------------------------------------------------
| Id  | Operation      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    82 |  1066 |     4   (0)| 00:00:01 |
|*  1 |  FILTER        |          |       |       |        |          |
|   2 |   TABLE ACCESS FULL| FOO      |    82 |  1066 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPTYTAB |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM "EMPTYTAB" "EMPTYTAB"))

Plan is completely different, the filtering clause is interesting.


SQL Management Studio - Add-on / Extension to view Column Data Type

This is standard functionality of SSMS (and has been for a while, I tested in both SSMS 2014 and SSMS 17.2).

For example when you hover over a column the tooltip shows you the data type:

The intellisense autocomplete also shows the data type when you select a column with your up/down arrows:


Multi master single slave replication mysql

I am assuming you already have Master1 and Master2 acting as Slaves of each other?

The slave will be replicating from only one of the Masters.  It will get data from the other Master via that one.  But you need (and probably already have):

log_slave_updates = ON  -- on all 3 servers
server_id -- distinct values for all 3 machines


How to take partitions or filegroups out of service

I can't give you an answer to question 1).
But to answer question 2):
What you are requesting is known as the "sliding window scenario".
If you want to eliminate a partition containing data that is no longer needed you have to perform following steps:

Create a table on the same filegroup as the partition you want to delete (typically, this table is referred to as a staging table).
This table has to have exact the same structure as the partitioned table, including indexes.
Switch the partition to the staging table
You have to remove the reference to the filegroup from the partition function vie MERGE RANGE ...
Now you can truncate the staging table and drop it. What is left is an empty filegroup you can remove, too.

Some more details on this at http://technet.microsoft.com/en-us/library/aa964122.aspx and the white paper on partitioning, available under http://msdn.microsoft.com/en-us/library/dd578580%28v=sql.100%29.aspx.


MongoDB remove() function deletes documents but retains indexes

The db.collection.remove() command is used to remove data from a collection. As such, documents will be removed from the collection and the indexes on the collection will be updated to no longer reference the removed documents.

Remove can be used in such a way to only remove a subset of data. From the docs:

db.products.remove( { qty: { $gt: 20 } } )

For example, you may want to remove a subset of data but retain the existing indexes to continue access the remaining data according to the existing access patterns.

This command contrasts from the db.collection.drop() command, which removes the entire collection along with its indexes.


Backup chain for DBs restored from a different server

Before running Ola's script, you can check the msdb.dbo.backupset.type = 'D' using top(1) .. ORDER BY backup_finish_date desc.

If it returns value then you have a full backup done and you can proceed with differential.
If it returns null or no value then you dont have a full backup and you can take a full backup and then proceed with your regular differential backups.

I would agree with JohnM that do you really need differential backups on a test server ? Its easy for you to get a full backup and refresh the environment.


PostgreSQL GIN pg_trgm default operator class

you can update the pg_opclass table and set the default.

update pg_opclass set opcdefault = true where opcname='gin_trgm_ops'

pg_trgm doesn't do this by default as you might have/want a different GIN default.

select * from pg_opclass where opcname = 'gin_trgm_ops';

than you can create an index:

create index ON table USING gin (field); -- it will use the new default


One option is to use SET ROLE command after you open a connection from your application to the databaserole:

SET ROLE databaserole;

In such way any object (including large objects) created within this session will be owned by databaserole instead of token-XXX.

Another option is to use LOCAL and make it work only within the transaction used to create the large object:

BEGIN;
SET LOCAL ROLE databaserole;
-- create and insert the large object
COMMIT;

Alternatively, you could just set the role directly as the user setting:

ALTER ROLE "{{name}}" SET role TO 'databaserole';

I kind of dislike that option as it can become a bit obscure to others how it is working, and if you manage other roles to your user it won't inherit them (although that doesn't seem like a problem to you, as with dynamically created user you should have a single role that it directs inherit from).


How do I get Pentaho Reporting/Kettle to produce PDF reports by business entity?

Parametrize your report and make sure that your query is parametrized too.
You can test your parametrized report inside the report designer.

Then use Kettle/PDI to feed the parameters to your report step, and once you produce the file, rename it or move it to the correct spot.

Running several smaller reports also allows you to run multiple reports in parallel. The reporting engine is inherently single-threaded, so one report process occupies one core. If you have more than one core, I recommend to run ([number of cores] - 1) instances of the step for optimum performance.


MySQL replication on slave server Broken/Stopped without error

Slave_SQL_Running_State: Reading event...

Suggests that it's working on a large event. If you have MIXED or Row based binary logging and have changed a large amount of data on the master you might end up with a large event or series of large events. Use the mysqlbinlog tool to see what's going on with your relaylog. The master might show a concentration of binary logs over a small period of time (on the filesystem)


Indexes with the same object_id but different names

The Object_ID belongs to the object the index belongs to, such as an indexed view or a table.

When you look at the object_id in the sys.indexes documentation

  object_id  - ID of the object to which this index belongs.

If you want to uniquely identify the index you need the Object_ID + index_id columns or the index_id for a specific object_id.

To verify this, you can add the OBJECT_NAME() function to your query

SELECT
  count(object_id) AS count,
  object_id,
  OBJECT_NAME(object_id),
  min(name) as name1,
  max(name) AS name2
FROM
  sys.indexes
GROUP BY object_id HAVING COUNT(*) > 1 ORDER BY count;


How to model entities in an entity-relationship diagram about a car service scenario?

The General Advice:

When you are starting off learning how to model databases, one of the most important rules of thumb is: Every tangible thing that matters to your system is probably an entity type.

This is a really good place to start with any logical database design.  If you spend some time up front thinking about what kind of things matter to your system, then you're going to come up with a solid foundation on which to build your system.  The things your organization cares about will change much less frequently than the business processes and rules your organization uses to deal with those things. That is why a solid data model is so important.

Another important rule of thumb is: Normalize your data model by default and only denormalize when you have a (really) good reason to.  This is especially true for a transactional system.  Reporting systems and data warehouses are a different story.

The Specific Answers:

Cardinality: If you think about it, it is easily the case that a car could have never been serviced (by your shop).  Therefore a minimum cardinality of zero is very plausible.  On the other hand, by the time the vehicle matters to your system it may well be because it has had its first service - so a minimum cardinality of one is also plausible.  You need to think about what the business rule is for your organization and model accordingly.  I would think, for example, that a car dealership would have lots of cars in its system that haven't been serviced by the dealership yet, whereas a muffler shop wouldn't care about cars it hasn't serviced.

Service Items: You asked:

  Also, a service involves parts, labor, and consumable.

  How would you model this? As a separate entity? Or in the service
  entity or part of the relation (intersection entity) between car and
  service ?

Let's consider an intersection entity between car and service...  You could potentially use such an intersection to store details about the service, like how much labour, which parts, and consumables were used.

However, using an intersection implies a many-to-many between cars and services, but you've already stated that each service is for (exactly?) one car.  Using an intersection entity to track service item details would mean your model isn't properly normalized.

Consider this model as an alternative:

In this model each service is for one vehicle, but each service can have many instances of labour, parts and consumables.  This model follows the first rule of thumb I mentioned and makes an entity type out of each tangible thing the system cares about.  This might be a good first stab at a logical model.

One of the issues with the above model is that it doesn't handle one aspect of how your system is likely to want to use the data, at least not very well.  One of the most important reasons for tracking all of this data in your system at all is so that you can print off an itemized service invoice.  That means that a service line item is itself a thing which is important to your system.  If you take that into consideration, you might end up with something more like this:

Notice in this second alternative SERVICE_LINE_ITEM is recognized as an entity type.  It is an intersection between SERVICE and the generic line item type: SKU.  A SKU is a supertype entity that could be a part, a consumable or some kind of labour.  You don't need to have a logical supertype for service line item types, but a lot of systems would be modeled this way because it makes the transactional detail much simpler.

This second model introduces abstract entities over and above the concrete entities of the first model.  Introduction of abstractions like this is one of the things that tends to happen as you move from an initial logical model, based mostly on tangible things to a physical model.

As you gain experience with data modeling, you'll get good instincts for moving past the conceptual/logical model stage directly to a well structured physical model.


what is so special about MongoDB indexing?

there is absolutely nothing special about it. absolutely zero; nothing.
if you want REALLY advanced indexing -> use PostgreSQL.


Rotate table for year-over-year comparison

Yes, more concise ways of pivoting exist for your scenario.
Option 1
For instance, you could use the PIVOT operator:
SELECT
  *
FROM
  (
    SELECT
      road_id,
      insp_year,
      condition
    FROM
      road_insp
  ) derived
  PIVOT
  (
    MAX(condition)
    FOR insp_year IN
    (
      2009 AS condition_2009,
      2010 AS condition_2010,
      2011 AS condition_2011,
      2012 AS condition_2012,
      2013 AS condition_2013
    )
  )
;

PIVOT implies grouping, which is why you can see the MAX() aggregate function applied to condition rather than simply using condition.
The implicit grouping is also the reason you often need to use a derived table before applying the PIVOT operator: all columns not mentioned in the PIVOT clause actually participate in grouping, so you need to exclude those that are not supposed to be grouping criteria. For your specific example you do need a derived table, because you need to exclude insp_id.
Option 2
As an alternative to the PIVOT operator, you could try the old-school conditional aggregation method:
SELECT
  road_id,
  MAX(DECODE(insp_year, 2009, condition, NULL)) AS condition_2009,
  MAX(DECODE(insp_year, 2010, condition, NULL)) AS condition_2010,
  MAX(DECODE(insp_year, 2011, condition, NULL)) AS condition_2011,
  MAX(DECODE(insp_year, 2012, condition, NULL)) AS condition_2012,
  MAX(DECODE(insp_year, 2013, condition, NULL)) AS condition_2013
FROM
  road_insp
GROUP BY
  road_id
;

In this case there is no need to derive a table, as columns you want to group by can be explicitly specified in the GROUP BY clause.
—————————
Both methods produce the same output, identical to the one that your joining method generates, as can be seen in this demo at dbfiddle.uk.


Maybe, you should use staff, attendance_staff, and school.

staff table
==========
id,
fullname,
birthday
...

school Table
==============
id,
name,
founded,
number_of_staff
...

attendance_staff table
=======================
id,
staff_id,
school_id,
attended_date,
...


Installing OE (Order Entry) schema in Oracle Express

SQL should be present in $ORACLE_HOME/demo/schema/order_entry.

This link gives all the info:

Oracle 11g Release 1 Database Sample Schemas

It can be downloaded from:

Sample Models and Scripts


Is there a "Full Containment" option for databases in SQL Server 2014?

SQL Server doesn't support fully contained databases (yet).

Azure SQL databases are fully contained.


They are part of a mixed extent, the diagram just didn't draw a grey box behind them, probably more to keep it from being too busy as much as anything.
Data File : Page
Again, I think this is just meant to simplify and not make the diagram too busy, as well as not give any sort of suggestion that pages will necessarily be linked in a certain order. Surely you can picture the arrows that would represent a doubly-linked list?

For most of this, you're much better off asking the author of the book and creator of the diagram, than a community of peers.


Can PostgreSQL index calculations be multithreaded?

Is there any way to calculate indexes multithreaded for single inserts?

Unfortunately, no.

PostgreSQL backends are single-threaded, and a single connection can use only one backend.

There's work to enhance this, but it's a seriously difficult and big job because of PostgreSQL's process-based architecture.

The only real option is to split the insert its self into multiple concurrent connections, each doing part of the work.


Database engine that share a row among multiple tables

Isn't this what CASCADE option on foreign key is supposed to do? You can find that in many DB engines.
It is not ok to perform the join on Fullname column as long as you have a CID column, which is supposed to be the identity. It is just a bad practice, and can have undesired effects - like the one you encountered.

In conclusion, either you change the joining column, or try to active the cascading on update capabilities of your DBMS.


Difference ReadOnly connections through sqlcmd vs SSMS

Without knowing the exact parameters you've used, it's hard to be sure.  But based on the example code you provided for sqlcmd:

sqlcmd -S servername -E -d db_name -K ReadOnly

I would bet you used the instance name of the current secondary replica - which would explain why you saw all the traffic there in your trace.

When you connected via SSMS, I imagine you used the listener name - which would go through the listener to connect to the primary, and then connect to the appropriate secondary replica (based on read only routing rules, load balancing, if there are any secondary replicas actually available, etc).

PS: to clarify one thing regarding this statement:

  However, when I run a trace all the queries point to the listener instead of the secondary HA server

The listener is neither the primary nor secondary, so the "instead of" relationship you mention here is a little confusing.  The listener is a virtual network name that facilitates connecting to the different replicas in the AG.


SSRS View Report: Change View Report Button Text

You could modify the button text through javascript if you edit C:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportManager\js\ReportingServices.js

See this blog post

Just define a function using the DOM to replace the button text and add it to the addLoadEvent


Is it possible to tell the source of a backup?

When you set up a backup using the Maintenance plan, it provides a rather complex name for the database backup (example: MovieManagement_backup_2012_02_14_064551_7520824). You can view this by using RESTORE HEADERONLY. Most people don't supply any name at all when running a backup manually. This can be the difference. Other than that, no, I'm not so sure there's anything you can do there.


Can Postgres index regular expressions stored in a column?

Short version: no. There's no practical way (in PostgreSQL, at least) to index a pattern column so it can be matched against plaintext inputs in a way that will speed up "does this plaintext match any of these patterns" queries.

PostgreSQL would need a special custom index type that "understood" pattern matches. I'm not sure how practical it'd be to implement one, nor how much could be gained from such an index. Since there isn't one, the question is somewhat academic.


Since the depth is variable and an item can have multiple parents, I recommend a general "relationship", not a parent-child hierarchy.

See this for the optimal way to write a many-to-many table.

Then you need to write multiple queries, not a single, fixed-depth, query.  There is no "good" way to traverse a tree/DAG/web (until 8.0, with CTEs).  And the speed of multiple queries, with suitable indexes and suitable caching, should be quite adequate.


From /tmp/trace.sql:

  The first set opens the database with the NORESETLOGS option and should be used only if the current versions of all online logs are available. The second set opens the database with the RESETLOGS option and should be used if online logs are unavailable.

If you're only recreating the controlfile you should be able to perform a complete recovery. i.e. without resetlogs. If you are missing some online redo logs or if they are not complete, then it will be a partial recovery, and RESETLOGS will be required.


Querying SQL Server extended properties for tables/views and its columns

If you wish to return Column level Extended Properties for all columns in the database, use this query:

SELECT
    ObjectType = o.type_desc,
    SchemaName = SCHEMA_NAME(o.schema_id),
    ObjectName = o.name,
    ColumnName = clmns.name,
    ExtendedPropertyName = p.name,
    ExtendedPropertyValue = CAST(p.value AS sql_variant)

FROM sys.objects AS o
INNER JOIN sys.all_columns AS clmns
    ON clmns.object_id = o.object_id
INNER JOIN sys.extended_properties AS p
    ON p.major_id = o.object_id
    AND p.minor_id = clmns.column_id
    AND p.class = 1

WHERE o.type IN ('U','V') -- User Tables and Views

Query was taken (and modified) from Working with SQL Server Extended Properties.


Why does PostgreSQL not use an index when OR is used in the conditions?

I cannot really answer your question, because I really don't know why, but I've found a way to make PostgreSQL do more or less what I guess you want. I've tested your situation with a simplified simulation scenario, and using PostgreSQL 9.6.1 (latest as of today). I get the same results.

Good news is: If you can change the way you make your query, you have a couple of options which use the trigram index.

The first one consists on moving the condition on the subcontacts. In this case, the trigram index is used for one of the situations (but not the other):

SELECT
    c.id, c.number
FROM
    cases c
    JOIN case_contacts caco ON caco.case_id = c.id
    JOIN contacts con_main ON con_main.id = caco.contact_id
    LEFT JOIN
    (
        SELECT
            *
        FROM
            contacts
        WHERE
            v_fullname ilike '%test%'
    ) AS con_sub ON con_sub.id = caco.subcontact_id
WHERE
    con_main.v_fullname ILIKE '%test%'
    or con_sub.id is not null /* if the left join gave an answer, it's got '%test%' */ ;

A very few trials with simulated data (where aprox. 0.1%, 2.5%, 5% or 25% of the v_fullname contain '%test%') show that the difference in execution times is minuscule. [My disc is SSD, a real HD might behave very differently.] This should actually be checked with a real system with real data... but it seems that using the trigram index or not, doesn't make a big difference.

PostgreSQL is not exceptionally good at estimating how many rows will appear searching "like '%test%'", but it seems not to matter on which plan decides to use.

There is another option, which (with my little experimentation) works a little bit faster in most cases, and a lot faster when the percentage of '%test%' is low. This option means using a CTE to "prefilter" the contacts (and it uses the trigram index once, because it doesn't need to use it twice):

WITH filtered_contacts AS
(
SELECT
    *
FROM
    contacts
WHERE
    v_fullname ilike '%test%'
)
SELECT
    c.id, c.number
FROM
    cases c
    JOIN case_contacts caco ON caco.case_id = c.id
    JOIN filtered_contacts con_main ON con_main.id = caco.contact_id
    LEFT JOIN filtered_contacts con_sub ON con_sub.id = caco.subcontact_id
WHERE
    con_main.v_fullname ILIKE '%test%'
    or con_sub.id is not null /* we need this test again, or we'll miss rows */ ;


How does Amazon create the RDS for Postgres? Some kind of Docker?

Amazon doesn't talk about this much and the servers are intentionally locked down, so it's hard to be completely sure.

They're EC2 instances that run a custom AMI and have automation tools - in-house, or something like Puppet/Chef/etc. These automation tools communicate with the AWS control panel over web service APIs, SSH push access, etc, and are responsible for managing the PostgreSQL configuration, starting/stopping/reloading the server, etc.

Each EC2 instance runs a single PostgreSQL database server, with its own users, roles, etc.

It's basically just a sealed AWS EC2 instance that you don't have much access to, you just get a locked down non-superuser PostgreSQL connection. Nothing magic.

This isn't the only way to do it. Heroku used to use OpenVZ on top of EC2 to partition EC2 instances into smaller containers, for example. I think these days they always have one EC2 instance per database though.

It sounds like what you want is multi-tenant hosting. You have many options for this:

One server per user with a single PostgreSQL instance on each server (EC2 or Heroku style)
one PostgreSQL instance per user on a single host server;
one database per user on a single PostgreSQL instance;
one schema per user in a single PostgreSQL database;
a single set of tables with your application limiting access to data within the tables based on enforced WHERE clauses or row-level security policies.

Which to choose depends on trade-offs involving isolation of users, performance, and cost.

There aren't currently any convenient canned recipes to do this that I know of, but searching for "multi-tenant postgresql" will help you find more information.


I don't think I can help you with the formatting, but the query you need is this one:

PROPOSED QUERY

SELECT
    IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Country,Hour WITH ROLLUP;

YOUR SAMPLE DATA

USE test
DROP TABLE IF EXISTS hits;
CREATE TABLE hits
(
    id int not null auto_increment,
    time datetime,
    country varchar(20),
    PRIMARY KEY (id)
);
INSERT INTO hits (time,Country) VALUES
('2014-06-19 01:00:00','France'),
('2014-06-19 01:00:00','Brazil'),
('2014-06-19 01:00:00','USA'),
('2014-06-19 02:00:00','USA');

YOUR SAMPLE DATA LOADED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS hits;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE hits
    -> (
    ->     id int not null auto_increment,
    ->     time datetime,
    ->     country varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO hits (time,Country) VALUES
    -> ('2014-06-19 01:00:00','France'),
    -> ('2014-06-19 01:00:00','Brazil'),
    -> ('2014-06-19 01:00:00','USA'),
    -> ('2014-06-19 02:00:00','USA');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM hits;
+----+---------------------+---------+
| id | time                | country |
+----+---------------------+---------+
|  1 | 2014-06-19 01:00:00 | France  |
|  2 | 2014-06-19 01:00:00 | Brazil  |
|  3 | 2014-06-19 01:00:00 | USA     |
|  4 | 2014-06-19 02:00:00 | USA     |
+----+---------------------+---------+
4 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->     IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    ->     COUNT(1) TotalHits
    -> FROM
    -> (
    ->     SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    ->     FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
    -> ) AA
    -> GROUP BY Country,Hour WITH ROLLUP;
+-------------------------+-----------+
| Statistic               | TotalHits |
+-------------------------+-----------+
| 2014-06-19 01:00:00     |         1 |
| Total for Brazil        |         1 |
| 2014-06-19 01:00:00     |         1 |
| Total for France        |         1 |
| 2014-06-19 01:00:00     |         1 |
| 2014-06-19 02:00:00     |         1 |
| Total for USA           |         2 |
| Total for All Countries |         4 |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql>

I'll leave the formatting to you :-)

CAVEAT: Please make sure you have a time index on the table

ALTER TABLE hits ADD INDEX time_index (time);

An additional query could be

SELECT
    IFNULL(Country,CONCAT('Total for ',IFNULL(Hour,DATE(NOW())))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Hour,Country WITH ROLLUP;

whose output would be

+-------------------------------+-----------+
| Statistic                     | TotalHits |
+-------------------------------+-----------+
| Brazil                        |         1 |
| France                        |         1 |
| USA                           |         1 |
| Total for 2014-06-19 01:00:00 |         3 |
| USA                           |         1 |
| Total for 2014-06-19 02:00:00 |         1 |
| Total for 2014-06-19          |         4 |
+-------------------------------+-----------+
7 rows in set (0.00 sec)

UPDATE 2014-06-21 19:07 EDT

Perhaps the the two queries combined with UNION will work for you

SELECT
    IFNULL(Country,CONCAT('Total for ',IFNULL(Hour,DATE(NOW())))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Hour,Country
UNION
SELECT
    IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Country,Hour WITH ROLLUP;

I removed the WITH ROLLUP from the first one so that the total comes out once


What to call a column that references another row in the same table?

There are several things in play here. A table that has a self-referential key is said to have a reflexive foreign key, though that doesn't look like it applies here because you'll have missing values.

What you're looking for is a hierarchical query, which can be achieved in Oracle by using a CONNECT BY clause.

They can be a bit difficult to get your head around at first, but there are plenty of good examples on the internet. A good one to start with is here. For once, the official documentation is quite clear too!

Something like:

SELECT id, bar, reference_id, LEVEL
FROM yourtable
CONNECT BY PRIOR reference_id = id;

... will get you started on your data.


Need a stored procedure to check and update row when it does not already exists.

without any more information it is difficult to give you the "right :)" answear. but what i can tell you, check:

http://www.techrepublic.com/article/defining-sql-server-constraints-with-tsql/6181836

and ...

When you specify a PRIMARY KEY constraint for a table, the SQL Server Database Engine enforces data uniqueness by creating a unique index for the primary key columns. If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.

See more on http://msdn2.microsoft.com/en-us/library/ms175132.aspx here


How to trouble shoot intermittently slow SQL Server

I've run into this several times: new server, hand-me-down, etc. etc.

I always start with the First Responder Kit from the BrentOzar team.  It's always allowed me to quickly diagnose issues and take preventative measures to mitigate future issues.

https://www.brentozar.com/first-aid/


Pros/Cons of using multiple databases vs using a single database

None of performance, stability, optimization are true. Does anyone have a solid argument or reference article why these would be true?

Resources are not allocated to a database: the SQL Server Instance balances resources so it makes no difference

You lose:

data integrity
restore integrity (data in DB7 will be later then DB1)

You gain complexity:

security (users, roles etc) have to be in all databases
you'll have some data that doesn't fit into 1 database nicely

Options:

splitting a database onto separate disks can be done with filegroups
use schemas to logically separate data (based on other answer)


Can a column be referenced from multiple table as a foreign key

Yes, you can do it. Your services table will store all the common attributes between your sub-classes.

If you intend to avoid 1:1 relationships, you'll have to create, for example, an ServiceId column and do the relationship with it, using Id as PK of RoomService. By the way, there is no problems with 1:1 relationships, specially in your case, where you are creating specialized sub-classes.

In addition, the type column will be used by your application to determine which subclass the object is. Also no problem on that.


How can I send HTTP requests from SQL Server 2012 to a non-.Net application?

You send a request, when you need something back. If you want to let someone to know something, but do not need anything back you notify with a notification.

There is such a feature called SQL Server Service Broker. It allows communitaion over boundaries of different systems and guarantees consistency and timing of the messages.

Further reading:
 - SQL Server Service Broker  on MSDN
 - SQL Server Service Broker Explained


How to store high-dimensional (N > 100) vectors and index for fast lookup by cosine similarity?

I'm not aware of any existing modules that will do this for you.  It will be hard to index this in a traditional sense of jumping to a specific and small part of the index which covers all possibly-qualifying rows.

Will the vectors be centered?  Normalized?  Could you get away with representing them as integers with an implicit divisor, rather than true floats?

Your best hope may be to implement an index somewhat like the Bloom extension, in which it always scans the full "index" but does it in a way which is much faster than it would it be to scan the table, both because the index is smaller, and because the data is stored in a way that doesn't have to go through the type abstraction machinery but rather uses the CPUs more directly.


PostgreSQL: Can the archive command for master and standby point to the same directory?

In theory this should work.  After all, the files should be identical.  In practice I think it is scary and I wouldn't do it.  I would worry about file locking contention, and also the fact that if I am archiving twice to two locations I get a full backup of the WAL's esp, if I send to different file servers.  If one goes belly up at a bad time, I can still get logs off the other for PITR.

Keep in mind that if something goes wrong with this setup, you can lose PITR ability to roll forward beyond a certain point.  Additionally if you set things up how they are now, you may be able to use file permissions and attributes to prevent overwriting existing parts of existing files which can be very helpful in backup assurance.


How can I use function's parameter in it?

Certain parts of queries cannot be substituted like that.  Such parts include table names and keywords.  "WEEK" fall into this category.  Instead...

-- Construct the query:
SET @sql = CONCAT("SELECT ... INTERVAL 1 ", range, "))");
-- perform it:
PREPARE _query FROM @sql;
EXECUTE _query;
-- clean up:
DEALLOCATE PREPARE _query;

(This will probably be more efficient than some IF..THEN..ELSE or CASE..WHEN construct.)


using string concatenation to reference a table

You can't use variables for columns. So you would need to do something like this:

CREATE RULE update_main_table AS ON INSERT TO entity_history
DO
UPDATE entity
   SET eValue1 = case
                    when new.type = 'Value1' then NEW.val
                    else eValue1
                 end,
       eValue2 = case
                    when new.type = 'Value2' then NEW.val
                    else eValue2
                 end;

Now I guess you didn't show us the whole picture and you have more than just two columns in the entity table (which will make the above update statement very inefficient and cumbersome).

The whole thing looks like a variation of the (anti) pattern named "entity-attribute-value". As you are using Postgres, I would highly recommend using the hstore data type to store key value pairs.

At least in the entity table you could do that:

CREATE TABLE entity (
    eID     SERIAL PRIMARY KEY,
    eName   VARCHAR(255) NOT NULL,
    value   hstore
);

Then you could just do:

CREATE RULE update_main_table AS ON INSERT TO entity_history
DO
UPDATE entity
   SET value = value || hstore(NEW.type, NEW.val::text)
WHERE eid = new.eID_FK;

Additionally this will automatically adjust to new "value types" without the need to rewrite the statement.

Maybe you can even restructure the entity_history table to also use a hstore column.


Log/Show number of (select) queries per table

Slight optimization:

SELECT ...
    round(VARIABLE_VALUE / usfs.VARIABLE VALUE)
    ...
FROM information_schema.GLOBAL_STATUS
JOIN ( select VARIABLE_VALUE from information_schema.GLOBAL_STATUS
               where VARIABLE_NAME = 'Uptime_since_flush_status') AS usfs

That way, it will fetch Uptime_since_flush_status only once.

Turning on the "general log" is probably the only way to get more details.  And that would be quite messy, since you would have to deduce the "current database" for each query.


MySQL SELECT n-th row between dates (time interval)

You should create a summary table that will store this summary data.

5 minute summaries will require additional 288 rows per day (24 * 60 / 5); 30 minute summaries - 48 rows per day (24 * 60 / 30); hourly summaries - 24 rows per day. In total, you're looking at storing additional 360 rows per day (288 + 48 + 60), which is a tiny overhead (360 / 86400 ~= 0.4167%) for a massive performance boost.

Try something like this:

CREATE TABLE data_summary (
    granularity_mins INT NOT NULL,
    unixDate BIGINT NOT NULL,
    sensorId INT NOT NULL,
    sensor_min_value DECIMAL NOT NULL,
    sensor_max_value DECIMAL NOT NULL,
    sensor_ave_value DECIMAL NOT NULL,
    sensor_readings_count INT NOT NULL,
    PRIMARY KEY(granularity_mins, sensorId, unixDate)
)

You can then get your 5 minute summary with a SELECT like this:

SELECT *
FROM data_summary
WHERE granularity_mins = 5
    AND unixDate > 1458829800 - 86400
    AND unixDate < 1458829800
    AND sensorId = 0


PgAdmin III -- how to merge output pane and query pane

Go to File-> Reset layout
This will merge the outputpane to the bottom of the screen.


Why is it that when I drop a field in table it says `#1050 - Table already exist`

Check out this post by Aleksandr Kuzminsky who not only provides a solution, but also (and maybe more importantly) an explanation of what's going on. Kuzminsky is a former Percona employee (Percona is a big hitter in the MySQL ecosphere) and a contributor to open source MySQL projects on Launchpad.

Basically, what happens is this

When you run an alter table, MySQL creates a temporary table with the new structure. The name of the new table is (something like) #sql-ib712.

It appears that MySQL doesn't choose very random names for these temporary tables. The problem arises when there's a crash in the middle of the ALTER TABLE process and the dictionary ends up with an orphaned table. Which wouldn't be a problem, except that MySQL (due to the non-random nature of temp table naming) has a good chance of giving the same name to a subsequent temporary table.

Obviously, Kuzminsky goes into a bit more detail, and provides a script to help fix your database.


For administrative purposes, you need to have a SUPER user WITH GRANT OPTION, but the name ('root') does not matter.  But...

Are there any scripts that assume it is root?
Such a user should be granted access only from localhost, thereby adding the OS security on top of that login.
Do not give SUPER or WITH GRANT OPTION to any other user (without understanding the security remifications)
Each application should have its own login into MySQL.  That is, use root (or your replacement name) only for admin purposes, not for applications.
Each application login should limit what it can touch.  Commonly that is one database, hence GRANT ... ON dbname.* ..., not ON *.*.  (There are exceptions and variations on this rule.)


Percona has already done some benchmarks with TokuDB vs InnoDB

See my post from 10 days ago : TokuDB not much faster than MySQL

Better answers are there as well.


I had to reread the vendor requirements a couple of times, but I take the instructions to mean the local administrator account only requires elevated permissions during installation.  Run one of the following blocks prior to the app install to set this up.

-- Add the Local Administrators Group
USE [master]
GO
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [BUILTIN\Administrators]
GO

-- Or Add only the Local Administrator User - Dynamic SQL to capture servername
DECLARE @hostname NVARCHAR(128), @createStmt NVARCHAR(MAX)
SELECT @hostname = CAST(SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS NVARCHAR(128))

SET @createStmt = N'USE [master];' + CHAR(13) + CHAR(10) +
    N'CREATE LOGIN [' + @hostname + N'\Administrator] FROM WINDOWS WITH DEFAULT_DATABASE=[master];' + CHAR(13) + CHAR(10) +
    N'ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @hostname + N'\Administrator];'

PRINT @createStmt
EXECUTE (@createStmt)
GO

When the installation is complete, be sure to back out these permissions as they are not the most secure, by running one of the following:

-- Drop Local Administrators group
USE [master]
GO
DROP LOGIN [BUILTIN\Administrators]
GO

-- Or Drop the explicitly defined Local Administrator login
DECLARE @hostname NVARCHAR(128), @createStmt NVARCHAR(MAX)
SELECT @hostname = CAST(SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS NVARCHAR(128))

SET @createStmt = N'USE [master];' + CHAR(13) + CHAR(10) +
    N'DROP LOGIN [' + @hostname + N'\Administrator];'

PRINT @createStmt
EXECUTE (@createStmt)
GO


No 'Copying to tmp table' state in profiling a mysql query

It just has a slightly different name. It's "Creating tmp table", which also makes more sense. MySQL knows, that a temporary table will be needed, so it generates it on the fly. The state name "copying to tmp table" is just poorly chosen.


My answer doesn't answer your question directly, but addresses your method of operation.

You shouldn't be logging in as postgres, just like you shouldn't be logging in as root on your servers. On your servers you should be logging in as real users and be using sudo for the odd occasion that you need superuser privileges. And preferably sudo su - <other_user> rather than just doing everything directly using sudo <command>.

In PostgreSQL we can implement more fine-grained policies.

Create a role structure something like:

create role admin noinherit;
grant <owner> to admin;
grant admin to <real_person>;

Then login as <real_person>.

To your scripts to deploy into <owner>, add the first line:

set role <owner>;

Then you should never have the problem again that you describe in your question.

And you never need to login as a superuser -- unless you need to create new schemas or users.


What would cause an UPDATE statment to hang when executing from an .mdb file to SQL Server 2008 R2 over ODBC?

If I understand your question correctly, you are doing a join in one MDB, based on a table in a different MDB and another table in SQL Server.

In this scenario, I would expect Access might need to read all of the data in both tables to be able to complete the join, and may not be able to do any optimizations or filtering until it has both datasets in memory. How big are the two tables? If either of them are large (10,000s of rows), it may take a while to run - although I would not expect it to hang permanently.

One option that may or may not be practical in your situation is to import the data to local tables before doing the join.  Another generic approach I would recommend is to offload as much of the heavy-lifting to SQL as possible - it is much better suited for working with large datasets.

If you can give some more details about the nature of the hang (temporary? permanent?) and the data (how many rows & columns) that would help us troubleshoot.

A troubleshooting step you can consider is to eliminate all the data columns from your query - just have the Keys. If that works, then slowly add the data columns back in until you can establish which one is causing the hang.


How to predict the growth of a PostgreSQL database

Well, for this part of your question:

  I'm looking for a way to predict growth of the database given its fields, indexes,  constraints (as foreign key), number of lines, and so.

  I searched on Google, but I found only means to determine the actual space occupied by the database, and can not predict their growth.

It's not clear why monitoring pg_database_size(your_database_name) doesn't give you the information you are after. Plot the size of all your databases daily, fit a curve to the plot, and you should be able to come up with an estimate of when you need to buy more disk space. What else do you need to know, and why?

For this part of your question:

  For example, how can I determine how many bytes would be occupied on disk for the following tables, in the following cases

  1) 100,000 rows in the account table, 10 rows in the role table and 100,000 lines in account_role table.

  2) 500,000 lines per account table, 20 lines on paper table and 600,000 rows in the table account_role?

You don't really need to concern yourself with how many rows are in each table or the precise structure of the table. Just query SELECT pg_total_relation_size('account'::regclass); and you have the answer, in bytes.


Disable / bypass authentication in PostgreSQL

The default Linux distribution of PostgreSQL (https://www.postgresql.org/download/) creates the system user "postgres", simply by installing the package from there.
It also has the default behaviour, that this user may access the database without password.

This is set up via the config file pg_hba.conf (https://www.postgresql.org/docs/9.2/auth-pg-hba-conf.html):

# Rule Name: local access as postgres user
local   all     postgres                                ident

So, on Linux you simply can change to this user with

  su - postgres (for root users)

or

  sudo -u postgres -i (for sudo users)

Windows has a similar command "runas" since XP/2003.

When connecting to Postgresql server via a command like

psql

the server asks the operating system for the executing user and that's it.
You won't even need to set a password.
Because pg_hba.conf defines local superuser access for this user.

createdb test
pgbench -i test
pgbench test


1. You know the column names ...
For Postgres 9.6 or later, use num_nulls()
WITH t(a, b, c, d, e) AS (
   VALUES
     (NULL ,'hi',2,NULL,'null')
   , ('' ,'hi',2,3,'test')
   , (NULL ,'hi',2,3,'null')
   )
SELECT num_nulls(a,b,c,d,e)
FROM   t;

Returns your desired result exactly, works for any mix of data types.
The manual:

num_nulls(VARIADIC "any") ...   returns the number of null arguments

For Postgres 9.5 or older, convert to text[], array_remove(arr, null) and use the remaining array length for an exact count:
SELECT 5 - cardinality(array_remove(ARRAY[a::text,b::text,c::text,d::text,e::text], null))
FROM   t;

Any type can be cast to text. The cast is redundant for text columns, of course.
array_remove() requires Postgres 9.3 or later.
cardinality() requires Postgres 9.4 or later. Substitute with array_length(arr, 1) in older versions.
2. You don't know column names, but Postgres does
When building on actual tables (or other registered objects like a view or a materialized view), we can retrieve column names from the system catalog pg_attribute to fully automate with dynamic SQL. Like:
CREATE OR REPLACE FUNCTION f_num_nulls(_tbl regclass)
  RETURNS SETOF int AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT num_nulls(%s) FROM %s'
    , (SELECT string_agg(quote_ident(attname), ', ')  -- column list
       FROM   pg_attribute
       WHERE  attrelid = _tbl
       AND    NOT attisdropped    -- no dropped (dead) columns
       AND    attnum > 0)         -- no system columns
    , _tbl
   );
END
$func$  LANGUAGE plpgsql;

Call:
SELECT * FROM f_num_nulls('myschema.tbl');

Returns the count for for each row in current physical order. Nothing else, to be absolutely generic.
Related:

Table name as a PostgreSQL function parameter
psql: SELECT * ... except one column

We could also pass each row to return a single count for it using a polymorphic function. Related:

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

3. You don't know anything: anonymous records
In the unlikely event that column names are unknown even to Postgres (like from a VALUES expression in your example), convert to a document type (json, jsonb, xml, hstore) to get a handle, like demonstrated by ypercube (comment deleted by now) and Evan.
But anonymous records do not have primary keys or any other unique attribute per definition. Count within each LATERAL subquery to defend against false aggregates. Demo with jsonb:
SELECT *
FROM  (
   VALUES
      (NULL ,'hi',2,NULL,'null')
    , (NULL ,'hi',2,NULL,'null')       -- duplicate row !!!
    , ('' ,'hi',2,3,'test')
    , (NULL ,'hi',2,3,'null')
   ) t                                 -- column names unknown
, LATERAL (
   SELECT count(*) FILTER (WHERE j.value = jsonb 'null') AS num_nulls
   FROM   jsonb_each(to_jsonb(t)) j
   ) c;

Or with json: probably a bit faster because the conversion is cheaper.
Demonstrating 3 different ways:
SELECT *
FROM  (
   VALUES
      (NULL ,'hi',2,NULL,'null')
    , (NULL ,'hi',2,NULL,'null')
    , ('' ,'hi',2,3,'test')
    , (NULL ,'hi',2,3,'null')
   ) t   -- column names unknown
, to_json(t) j
, LATERAL (
   SELECT count(*) FILTER (WHERE j1.value::text = 'null') AS num_nulls1
   FROM   json_each(to_json(t)) j1
   ) c1
, LATERAL (
   SELECT count(*) FILTER (WHERE j->>k IS NULL) AS num_nulls2
   FROM   json_object_keys(j) k
   ) c2
, LATERAL (
   SELECT count(*) - count(j->>k ) AS num_nulls3
   FROM   json_object_keys(j) k
   ) c3;

db<>fiddle here


Postgresql doesn't accept replication connection

The database name has to be replication as all does not cover replication connections.

host    replication    postgres             127.0.0.1/0               trust

The PostgreSQL documentation further says:

  The value replication specifies that the record matches if a
  replication connection is requested (note that replication connections
  do not specify any particular database). Otherwise, this is the name
  of a specific PostgreSQL database. Multiple database names can be
  supplied by separating them with commas. A separate file containing
  database names can be specified by preceding the file name with @.


How to use "WHERE" and also "WHERE NOT EXISTS" in the same query and table?

WHERE is a separator, not an operation , the bit after WHERE describes the rows you want to see in the result.

To apply two conditons use AND or OR to combine them.

select * from t1
where not exists (select * from t2 inner join t3 on(t3._id = t2.department_id)
  AND  t2.category_id = t1._id AND t3._id = 27 AND t3.company_id = 1)
ORDER BY "ctsName"


How to check if the current time is between two times?

Solution 1:

Define the range column as 'hours since midnight'. Continuing with the 22:00 and 06:00 range example, the range value would be [22, 30] (22 hours since midnight, 1 day + 6 hours since midnight), i.e.

current_date::timestamptz + interval '22 hours' AND
current_date::timestamptz + interval '30 hours'

One could define a custom rangetype for this purpose.

Solution 2:

Alternatively, you can have two columns:

maintenance_window_starts_at time without time zone
maintenance_window_duration interval

and write the check as:

SELECT
  maintenance_window_starts_at IS NULL OR
  now() BETWEEN
    current_date + maintenance_window_starts_at AND
    current_date + maintenance_window_starts_at + maintenance_window_duration
FROM maintenance_task;

Both solutions were suggested by the members of Freenode #postgresql community.


Detached DB showing up in back-up history

When you use Delete Database from modern versions of Management Studio, it asks whether you want to delete the backup history for this database in MSDB. The reason that a deleted database is showing in your query is simply because backup history exists for it - in MSDB.

I adjusted your query by joining backupset to sysdatabases, so it excludes databases that do not exist at the moment you run the query.

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date),
GETDATE()) AS  [Backup Age (Hours)]
FROM    msdb.dbo.backupset
inner join master.dbo.sysdatabases
on msdb.dbo.backupset.database_name=master.dbo.sysdatabases.name
WHERE     msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING
(MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24,     GETDATE()))
 UNION
 SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
 master.dbo.sysdatabases.NAME AS database_name,
 NULL AS [Last Data Backup Date],
 9999 AS [Backup Age (Hours)]
 FROM
 master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
 ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
 WHERE msdb.dbo.backupset.database_name IS NULL
 AND  master.dbo.sysdatabases.name <> 'tempdb'
 ORDER BY
 msdb.dbo.backupset.database_name


Create a table with the same constraints,triggers,indexes, and statistics for house-keeping table

Right mouse click on your database -> Tasks -> Generate scripts -> Tables:

On the next page select if you want to generate a single file or single file per object and go to Advanced:

Here select constraints, triggers, indexes and what else you want to script


I have just addressed this a month ago: InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6

This usually happens when one upgrades mysql from 5.5 to 5.6. The cuurrent tools for MySQL upgrading does not account for the only 5 system tables that are InnoDB.

Such errors are really innocuous and would not impede InnoDB, provided you do not activate features that need them.

This answer does not address your real issues. It just explain away the error message you posted.

You can create the tables in phpmyadmin. Just run this:

CREATE TABLE `mysql`.`slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

UPDATE 2014-01-17 11:00 EST

You last commented

  It's weird, the database says those tables exist when trying to create them. But the errors say they don't... MySql is behaving ok at the moment. I'm going to keep this question open for a bit I think. Look for more clues next time it dies on me

The reason MySQL says the tables exist in simple. First, here is an XRay of InnoDB

If you look at ibdata1 in the picture, you see the data dictionary. The data dictionary is insisting that the tables exist. Your solution to clean this up is

Run set global innodb_fast_shutdown = 0;
mysqldump everything to a SQL file
shutdown mysql
delete ibdata1, ib_logfile0, ib_logfile1
startup mysql (ibdata1, ib_logfile0, ib_logfile1 get recreated)
See my posts

Apr 19, 2012 : MySql - Clean ibdata1
Dec 09, 2011 : What is the best way to reduce the size of ibdata in mysql?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine? (StackOverflow)

With a clean data dictionary, you can now create the missing InnoDB system tables.

Give it a Try !!!


Master port is disabled from Public IP, MySQL log is not getting refreshed

The basic problem stems from the Slave's IO Thread. Sometimes, it has the nasty habit of playing dumb and not properly checking or pinging the master.

MySQL 5.5's semisynchronous replication allows you to have the master time itself out and degrade back to asynchronous replication. Slaves running semisynchronous replication should have a more sensitive IO Thread now.

Concerning your particular situation, you are using MySQL 5.0.24 ??? That's a very old version. There are two bug reports (Bug1 and Bug2) discussing this.

It just dawned on me that you asked this question earlier and DTest answered it. Giving credit where credit is due, the bug reports came from his answer.

It would be nice to to be informed when the Public IP is being disabled from Slave access. That way, you could run STOP SLAVE; on the slave in advance.

You also asked is there a way to come to know the master status from the slave, when this type of behavior occurs.

Here is a typical SHOW SLAVE STATUS\G

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.80.136
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019 <<------
          Read_Master_Log_Pos: 277892198 <<------
               Relay_Log_File: relay-bin.000058
                Relay_Log_Pos: 37535484
        Relay_Master_Log_File: mysql-bin.000019 <<------
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 277892198 <<------
              Relay_Log_Space: 277892637 <<------
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

Please note the following:

Master_Log_File : Log file from Master whose latest SQL was last copied to relay logs
Read_Master_Log_Pos : Log position within Master_Log_File whose latest SQL was last copied to relay logs
Relay_Master_Log_File : Log file from Master whose SQL was last executed from the relay logs
Exec_Master_Log_Pos : Log position within Relay_Master_Log_File whose SQL was last executed from the relay logs

If none of these value are moving, that could mean the all SQL is processed or that the slave is currently processing an SQL statement in the SQL thread that came from position Exec_Master_Log_Pos of the Master Log Relay_Master_Log_File.

Now, look at Relay_Log_Space. This number represents the sum total of all filesizes for all relay logs. If Slave_IO_Running is Yes and Relay_Log_Space is not changing, then go check the master by running SHOW MASTER STATUS;. It should like something like this:

mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000019 | 682563021 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

If the Master's (File,Position) of SHOW MASTER STATUS; is beyond the Slave's (Master_Log_File,Read_Master_Log_Pos) of SHOW SLAVE STATUS\G, then the IO Thread on the Slave is dead for intents and purposes, even if Slave_IO_Running is Yes.


SQL Server 2014 SP3 Unexpected Service Stop

You might see a stack dump in error log. You might have hit a bug and would need to go through the logs to find more on it. You might need to apply latest patch CU4. The error message is documente in below link :

SQL Server reports errors for exceptions, assertions, and hang conditions

Too many mights above as these errors are quite tricky and never have just one cause/solution.


Loop through all users in MySQL and add REQUIRE SSL flag

You could GRANT each existing user the special no-privilege USAGE privilege, which doesn't change their existing privileges but can be used to REQUIRE SSL for an existing user.

USAGE can be specified to create a user that has no privileges, or to specify the REQUIRE or WITH clauses for an account without changing its existing privileges.
— http://dev.mysql.com/doc/refman/5.6/en/grant.html

mysql> SHOW GRANTS;
+---------------------------------------------------------------------------------------------+
| Grants for sqlbot@%                                                                         |
+---------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sqlbot'@'%' IDENTIFIED BY PASSWORD '*XXX' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'sqlbot'@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS;
+---------------------------------------------------------------------------------------------------------+
| Grants for sqlbot@%                                                                                |
+---------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sqlbot'@'%' IDENTIFIED BY PASSWORD '*XXX' REQUIRE SSL WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The above approach adds the SSL restriction without otherwise changing my privileges.
Alternately, you should be able to manipulate the mysql.user table directly with queries.  The only caveats here: don't add this to your own SUPER account until you know you've got SSL working correctly, and remember to FLUSH PRIVILEGES; after manually modifying the grant tables.  Without that last step, the changes won't be noticed by the server until the next restart, since the grant tables are cached and only changes made by GRANT or REVOKE are noticed by the server otherwise.
This query should force all users to use SSL.  You may want to modify it to suit your requirements, based on what a correctly-configured user looks like in your user table, or try it on only one user and not the entire user base, to confirm that the behavior you get is the behavior you expect.
mysql> UPDATE mysql.user SET ssl_type = 'any' WHERE ssl_type = '';
mysql> FLUSH PRIVILEGES;

There is no reason I can think of that this should be problematic, but please use this suggestion with caution in production.


mysql restart won't kill child processes on CentOS

The script /etc/init.d/mysqld is dependent on the presence of the file mysql.sock. If that file is missing, run service mysqld restart or service mysqld stop simply does not work.
Over the years, I have seen many incidents where mysql.sock just disappears (going back to MySQL 4.x). See ServerFault Post : What should mysqld.sock contain, why don't I have it?. When this happens, you will have to resort to mysqladmin shutdown.
If you tried this:
mysqladmin -h127.0.0.1 -uroot -p shutdown

this may fail if it authenticates as root@localhost
Instead, shutdown mysql using the TCP/IP protocol
mysqladmin -h127.0.0.1 --protocol=tcp -uroot -p shutdown

This bypasses checking for the socket file mysql.sock.
Give it a Try !!!
CAVEAT
I have discussed using mysqladmin shutdown before

Dec 14, 2012 : Percona-server time out on /etc/init.d/mysql start (ServerFault)
May 08, 2012 : How to properly stop MySQL server on Mac OS X?
Feb 06, 2012 : MySQL Database and Table Loss


Why is SQL Server's online indexing causing blocking?

Despite what the official documentation says, dropping a clustered index using ALTER TABLE DROP CONSTRAINT can be performed online (tested on SQL Server 2005-2014):

USE Sandpit;

ALTER DATABASE Sandpit
SET RECOVERY SIMPLE;

CREATE TABLE dbo.Test
(
    col1 integer NOT NULL,
    CONSTRAINT PK PRIMARY KEY CLUSTERED (col1)
);

INSERT dbo.Test
VALUES (1);

CHECKPOINT;

BEGIN TRANSACTION;

ALTER TABLE dbo.Test
DROP CONSTRAINT PK WITH (ONLINE = ON);

SELECT
    [Current LSN],
    [Operation],
    [Transaction Name],
    [Xact ID],
    [Description]
FROM sys.fn_dblog(NULL,NULL) AS FD
WHERE
    FD.[Transaction Name] IS NOT NULL;

ROLLBACK TRANSACTION;

DROP TABLE Test;

Nevertheless, performing an operation online does not mean that other concurrent queries will not be impacted at all. The online operation does not take long term locks, but it may still consume considerable system resources (e.g. memory, processor, I/O). If these resources become a bottleneck, concurrent queries may experience resource-related waits.

In your specific scenario, the PAGELATCH_UP waits on PFS pages indicate latch contention on allocation resources. Any possible corrective action depends on the resource description associated with the latch waits.


How can I output a legible timestamp from "1583981745.335346" (stored in a 'double' field type) via a select statement?

FROM_UNIXTIME can help.

select from_unixtime(1583981745.335346)

2020-03-12 02:55:45.335346

ref: fiddle

This is a datetime type so it can be handled as such in PHP.


Sql Server Ranking Value Not Changing Please Help

No need for the complex - and expensive - FIND_IN_SET() and GROUP_CONCAT() solution. All you need is the DENSE_RANK() function:

SELECT
    id, name, score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM scores ;

If you want to add a WHERE clause, say WHERE name = 'C' and still get the rank(s) - among all rows of the table - you can use either a CTE or a derived table:

-- derived table
SELECT d.*
FROM
  (
    SELECT
        id, name, score,
        DENSE_RANK() OVER (ORDER BY score DESC) AS rank
    FROM scores
  ) AS d
WHERE d.name = 'C' ; -- arbitrary `WHERE`

-- CTE
WITH d AS
  (
    SELECT
        id, name, score,
        DENSE_RANK() OVER (ORDER BY score DESC) AS rank
    FROM scores
  )
SELECT d.*
FROM d
WHERE d.name = 'C' ; -- arbitrary `WHERE`


You should specify

System username

System password

e.g. username and password one of the windows account


backup of databases - consistently back up databases with control of log truncates

VEBF appears to be a desktop/workstation backup software, and doesn't (directly) support any capability for SQL server, except for backing up SQL BAK files you have already created via a SQL native backup.

Are you talking about Veeam Explorer for Microsoft SQL Server?

If your databases are in Simple mode, you don't need to do anything to "control your log truncates". When transactions complete, SQL will automatically re-use that portion of the transaction log. If you are somewhat impatient, you can issue a "checkpoint", but that really shouldn't be necessary.


The journal will be 3 x 1GB files by default, so really you should just make sure that you have at least 3GB space for the journal when you are starting up.  You can use the smallfiles option to reduce that to 3 x 128MB (384MB) instead, however I would not recommend that for any system with significant volume.

Similarly you can turn off pre-allocation using the noprealloc option but again I would not recommend doing so due to the potential performance issues you can hit as a result.

Both of these options must be configured when the process starts, there is no way to (re)configure them after the fact.


One of the assumptions that I would ask you to reconsider is whether you really want to have different tables for each event type, or if rather these event types are rows in a table instead of distinct tables.

If you had an EVENT_TYPE table containing your list of event types (about 100 of these) you could take the columns from your current various event tables and make them rows in an EVENT_PARAMETER table.

This type of design would turn your schema changes into data changes when you add new event types.  That would save you code maintenance issues and also avoid making your queries more complex and potentially slower.

The instances of events would similarly be kept in two tables, for example a LOG table and a LOG_DETAIL table.

LOG would be an intersection between user and event type.

LOG_DETAIL would be an intersection between log and event parameter.

Your data model might look something like this:

Note that one objection some people may have to this approach is that you end up saving the log parameter values in a string format, rather than in a native format.  This is clearly a trade-off.  You have to ask yourself is it a good trade-off for your situation.


How to speed up query with exists clause?

See if this gives what you need, but faster:
SET @@group_concat_max_len = 8000;

SELECT GROUP_CONCAT(customer_email SEPARATOR '  ')
    FROM sales_order AS so
    WHERE EXISTS ( SELECT 1 FROM sales_order_address
                       WHERE address_type = 'billing'
                         AND parent_id = so.entity_id )
    GROUP BY first_name, last_name, street, city, region
    HAVING COUNT(*) > 1;

(The output won't be formatted the same.)
Another possibility:
SELECT GROUP_CONCAT(customer_email SEPARATOR '  ')
    FROM ( SELECT so.first_name, so.last_name, so.street, so.city, so.region,
                  so.customer_email
            FROM sales_order AS so
            JOIN sales_order_address AS soa
            WHERE soa.address_type = 'billing'
              AND soa.parent_id = so.entity_id ) AS x
    GROUP BY first_name, last_name, street, city, region
    HAVING COUNT(*) > 1;

Indexes that may help:
sales_order_address:  INDEX(address_type, parent_id)
sales_order:          INDEX(entity_id)   -- unless it is the PRIMARY KEY


Why do subsequent unmet conditions in my CASE statement affect my return data type?

The column defined by your CASE expression has a data type, as all columns do.
SQL Server determines the resulting type in its usual way, making use of data type precedence. The type datetime has a higher precedence than integer.
You might be expecting the CASE expression to return different types on different paths, but that is not possible (sql_variant aside).


HierarchyID: Get all descendants for a list of parents

One way to accomplish this is:

USE tempdb;
CREATE TABLE dbo.PersonHierarchy
(
    PersonHierarchyID HierarchyID
);

INSERT INTO dbo.PersonHierarchy VALUES ('/1/');
INSERT INTO dbo.PersonHierarchy VALUES ('/1/1/');
INSERT INTO dbo.PersonHierarchy VALUES ('/1/2/');
INSERT INTO dbo.PersonHierarchy VALUES ('/2/');
INSERT INTO dbo.PersonHierarchy VALUES ('/2/1/');

SELECT *
FROM dbo.PersonHierarchy;

DECLARE @T TABLE
(
    PersonHierarchyID HierarchyID
);

INSERT INTO @T
VALUES ('/1/'), ('/2/');

WITH parent AS (
    SELECT p.PersonHierarchyID
    FROM dbo.PersonHierarchy p
        INNER JOIN @T t ON p.PersonHierarchyID = t.PersonHierarchyID
)
SELECT ph.PersonHierarchyID.ToString(), parent.PersonHierarchyID.ToString() /* select only the necessary columns, do NOT use SELECT *  */
FROM dbo.PersonHierarchy ph
    INNER JOIN parent ON ph.PersonHierarchyID.IsDescendantOf(parent.PersonHierarchyID) = 1
ORDER BY ph.PersonHierarchyID; /* always include an ORDER BY */


Initial recovery Postgres with backup in sync commit mode

If you don't archive wal's then how do you suppose the slave is going to get them?

You have to archive, and then recovery.conf should read from the archive.


Performance Inserting and Updating Millions of rows into a table

If you are bringing data from a CSV with a large import process, I'd recommend you utilize a staging table.  This will allow you to dump all of your imported data into the staging table that won't be queried by end-users/applications.  You can use this staging table as a source for your INSERT and UPDATE operations.

Once the data is in the staging table you can kick off a stored procedure (or multiple stored procedures, depending on how vast your business logic is) to do the necessary INSERT and UPDATE DML statements into the actual queried table.

As for performance, you need to decide what is more important for your implementation.  If you're looking to get the data in the table as quickly as possible, a very feasible route may be to disable the indexes.  But realize that OLAP performance will be impacted by that.  If you want to ensure the minimal amount of end-user impact then you would have to approach this a different way.  When you say "the DB should be kept availabel as normal" then I would assume the latter.

Performance is double-edged in this situation.  Performance for the data import will most likely take away from performance of end-user interaction with the data, and vice versa.


Running query against SQL Server with FreeTDS's bsqldb returns blank output

I suspect the one of the values is NULL, so the whole expression concatenation is NULL.

You'd need this:

CAST(ISNULL(PageGuid, '') as varchar(max)) + ',' +
CAST(ISNULL(TenantGuid, '') as varchar(max)) + '/' +
CAST(ISNULL(OriginalFileGuid, '') as varchar(max))


Updating whole table once, criteria : column_name and each value

This is a very simple query. You can use a CASE expression, one for each column that needs updating:

UPDATE table_name
SET other1 = CASE WHEN other1 <=  0 THEN other1    -- no change
                  WHEN other1 <  50 THEN other1 * 10
                  WHEN other1 < 120 THEN other1 *  5
                  WHEN other1 < 230 THEN other1 *  2
                  -- possibly more WHEN..THEN clauses
                  ELSE other1
             END,
    other2 = CASE WHEN other2 <=  0 THEN other2       -- no change
                  WHEN other2 <  50 THEN other2 * 10
                  WHEN other2 < 120 THEN other2 *  5
                  WHEN other2 < 230 THEN other2 *  2
                  ELSE other2
             END,
    --- repeat similarly for all columns that need updating
  ;


When can SARGable predicates be pushed into a CTE or derived table?

There are a few issues in play here.
Pushing predicates past TOP
The optimizer cannot currently push a predicate past a TOP, even in the limited cases where it would be safe to do so*. This limitation accounts for the behaviour of all the queries in the question where the predicate is in a higher scope than the TOP.
The work around is to perform the rewrite manually. The fundamental issue is similar to the case of pushing predicates past a window function, except there is no corresponding specialized rule like SelOnSeqPrj.
My personal opinion is that an exploration rule like SelOnTop remains unimplemented because people have deliberately written queries with TOP in an effort to provide a kind of 'optimization fence'.
* Generally this means the predicate should appear in the ORDER BY clause associated with the TOP, and the direction of any inequality should agree with the direction of the sorting. The transformation would also need to account for the sorting behaviour of NULLs in SQL Server. Overall, the limitations probably mean this transformation would not be generally useful enough in practice to justify the additional exploration efforts.
Costing issues
The remaining execution plans in the question can be explained as cost-based choices due to the distribution of values in the Score column (many more rows <= 500 than >= 500), and the effect of the row goal introduced by the TOP.
For example, the query:
--Written normally
SELECT TOP (101)
    c.UserId,
    c.[Text],
    c.Score
FROM dbo.Comments AS c
WHERE
    c.Score <= 500
ORDER BY
    c.Score ASC;

...produces a plan with an apparently unpushed predicate in a Filter:

Note that the Sort is estimated to produce 101 rows. This is the effect of the row goal added by the Top. This affects the estimated cost of the Sort and the Filter enough to make it seem like this is the cheaper option. The estimated cost of this plan is 2401.39 units.
If we disable row goals with a query hint:
--Written normally
SELECT TOP (101)
    c.UserId,
    c.[Text],
    c.Score
FROM dbo.Comments AS c
WHERE
    c.Score <= 500
ORDER BY
    c.Score ASC
OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));

...the execution plan produced is:

The predicate has been pushed into the scan as a residual non-sargable predicate, and the cost of the whole plan is 2402.32 units.
Notice that the <= 500 predicate is not expected to filter out any rows. If you had chosen a smaller number, like <= 50, the optimizer would have preferred the pushed-predicate plan regardless of the row goal effect.
For the query with Score DESC and a Score >= 500 predicate:
--Written normally
SELECT TOP (101)
    c.UserId,
    c.[Text],
    c.Score
FROM dbo.Comments AS c
WHERE
    c.Score >= 500
ORDER BY
    c.Score DESC;

Now the predicate is expected to be very selective, so the optimizer chooses to push the predicate and use the nonclustered index with lookups:

Again, the optimizer considered multiple alternatives and chose this as the apparently cheapest option, as usual.


SQL Server 2000 optimzation job hasn't run for a while

A lot can have happened in a year to cause performance issues. The fact that your "clean up job" did not run might or might not be the primary reason.

Before you start optimizing performance, the first thing you need to do is finding out what your biggest problem is and then address that. (Independent of the SQL Server version, or really independent of the system you are working with.)

SQL Server 2000 provides a limited set of tools you can use for this investigation. Start by tracing batch and procedure executions to see which of them take the longest and do the most reads. It is not unlikely at all that over the year the data changed in a way that a new index is now required.

If in the end you come to the conclusion that the indexes in place are the best for your workload, you can look at this "clean up job" again.

First you need to figure out (on a per table basis) if a reorganization or a rebuild is required. Reorganization usually is faster but has a smaller improvement impact. after a year of "neglect" you most likely need to execute a rebuild on all tables. See http://technet.microsoft.com/en-us/library/cc966523.aspx for more details, particularly the   DBCC DBREINDEX vs. DBCC INDEXDEFRAG section.

As Shawn mentioned, each table rebuild/reorganize action requires a table lock in SQL Server 2000. To mitigate that there are two options:

Upgrade to a more modern version of SQL Server that supports online index maintenance
Clean up one table at a time and then take a significant break to allow other processes to be worked on. Depending on how many tables you have do one a night, or a few with an hour break in between each. In the following nights address other tables. You could also, each night address the 3 - 5 most fragmented tables. DBCC SHOWCONTIG can show you how fragmented a table is. (See above link for details.)


Look for software that can convert sql (mysql) to erd

You can use MySQL Workbench (http://www.mysql.com/products/workbench/) or Navicat for MySQL (http://navicat.com/en/products/navicat_mysql/mysql_overview.html)


Error during sp_refreshsqlmodule after altering table type

To anyone, that may come across this problem:
The issue was  nested dependencies.
You need to run sp_refreshsqlmodule starting from the bottom of the dependency hierarchy. If you try to refresh the Stored Procedure, that uses this table type and passes it further, you will get this operand type clash error


Understanding mysqltuner Recomendations w/ Unused Catalogs

If you know that the MyISAM tables are not being actively queried, not only can you ignore recommendations for key_buffer_size, you could actually lower it.

What can you do with the extra RAM you just reclaimed? One or more of the following options:

OPTION 1) Add that number to innodb_buffer_pool_size
OPTION 2) Slightly increase max_connections
OPTION 3) Slightly increase by 1M (given to each DB Connection) one or more of the following

join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd buffer_size

After restarting mysqld, rerun mysqltuner and evaluate the resulting recommendations. Taking key_buffer_size out of the equation is enough in your instance.


Can I connect to SQL Server 2008 R2 database from SSIS 2012?

You would need to save the package to a SQL 2012 instance and run it from there. It will connect to R2 just fine.

This is because a SQL Server 2012 SSIS package cannot be saved to a lower version of SQL Server or run from a lower version of SQL Server. That said, an SSIS package (in any version of SQL Server) can connect to any data source that is ODBC or OLE DB compliant. So an SSIS Pacakge created in SQL Server 2012 can connect to an Oracle Database as a connection, it can connect to a text file as a connection or it can connect to SQL Server 2008 R2 as a connection.

Also the benefit of keeping your development in SSIS 2012 is that you don't have to worry about upgrading that package when you finally move all of your infrastructure to SQL Server 2012. You can also take advantage of the new features in SSIS 2012, as you rightly pointed out.


Mysql join query taking too much time even though indexes are implemented

Try this rewrite:

SELECT  m.`keyId` AS `caseId`,
        m.`id`
    FROM  `locationMapping` AS m
    WHERE  m.`keyTable` = 'xercase'
      AND  m.govtId = 90
      AND  EXISTS (
        SELECT  *
            FROM  locationSubtype
            WHERE  m.`locationSubtypeId` = `id`
              AND  m.`locationTypeId` = `locationTypeId`
              AND  m.`govtId` = `govtId`
                  )
    GROUP BY  m.`keyId`

In many cases EXISTS() is faster than INNER JOIN.

This 'composite' index is important:

locationMapping: INDEX(govtId, keyTable)  -- in either order

The Optimizer is smart enough to notice the transitive nature of govtId=90, so I removed one mention of it.

More

AND   `locationMapping`.`govtId` = `locationSubtype`.`govtId`)
AND ((`locationSubtype`.`govtId` = '90')
...        locationMapping.govtId = 90

The optimizer, if given the first of those, plus either of the other two, will deduce the final one.  You have provided all 3, which is OK.  But it is 'overkill'.

The first line is important because it tells the optimizer how the two tables are related.


UNPIVOT is not the best way to unpivot a table, CROSS APPLY with VALUES is a far better method.

Sorry, but I don't think you can get away from Dynamic SQL for this one.

This code will dynamically unpivot an entire table.

DECLARE @tablename VARCHAR(255) = 'Employee'
DECLARE @schemaname  VARCHAR(255) = 'HumanResources'
DECLARE @qrystr NVARCHAR(4000) = ''

;with cte
    AS
    (
    SELECT
        c.column_id
        ,c.name
    FROM
        sys.objects o
            INNER JOIN sys.columns c
                ON
                o.object_id = c.object_id
    WHERE
        o.name = @tablename
        AND
        SCHEMA_NAME(o.schema_id) = @schemaname
    )
SELECT
@qrystr =
'SELECT A.[' + (SELECT name FROM cte WHERE column_id = 1) + ']
    ,C.Name
    ,C.Val
FROM
    [' + @schemaname + '].[' + @tablename + '] A

    CROSS APPLY (VALUES ' +
    STUFF((SELECT ',' + '(CAST(['+name + '] AS VARCHAR(255)),''' + name + ''')' FROM cte WHERE column_id > 1 ORDER BY column_id FOR XML PATH ('')), 1, 1, '')
    + ' ) C(Val,Name)'

EXEC (@qrystr)


SQL Server Cluster + Availability Groups?

You can have AGs within an FCI but I don't see the point (I mean when you do this you usually do away with the FCIs unless there was some specific requirement).

In an AG the listener acts similar to a load balancer - kind of. If applications support using application intent read only in the connection string it can offload reads to the "passive" server. It's not load balancing though as it only goes to one server. In 2016 those reads are round robin and so it works better.

If your apps don't support application intent and splitting reads and writes (very few do) though then you're largely out of luck. And you're not going to be able to load balance writes.

In your comment to me you mentioned you'd want to keep the FCI in order to balance load. That isn't really going to work that way with an AG and it would be wasteful of resources - which are one of the best parts of having an AG.

Let's say you have two nodes in an AG and ten databases. You can (if queries don't cross the databases, or only groups of them), create ten groups with a different name each. Then to do balancing you can failover just groups within the node to other nodes until you're happy with it. (Note though that you'll need to do scripting to do this; AGs don't have a notion of preferred nodes to go to automatically unlike FCIs).

As the nodes are "as" available as an FCI there's not many reasons to leave the FCI with its shared storage in place. Those resources are kind of wasted.


SQL Server Merge Replication Dummy Update issue

We have found a lot of records in MSmerge_metadataaction_request pointing to the same article (table), in action = 5.

They look like garbage. When profiling the database those records are returned to the working process even though the article was not included in the subscription that was running (another subscription).

Deleting the records from MSmerge_metadataaction worked out. However, I am not sure if that will generate issues in the long run, at least now the subscriptions are synchronizing.


In my specific case, this was a result of cell security. The role did not have access to all the fields returned in the drillthrough action. I missed this because there are two very similarly-named fields and my eye did not notice. It's still odd that a security denial creates a query timeout, but at least this note is here for the next person.


How to return a dataset only if an unrelated username and password are correct?

Is it possible to combine these so that the first query will only return a result if the username and password had a match?

Yes, it's pretty simple; an EXISTS subquery (a semi-join):

-- query 1
select *
from Reports
where exists
      ( select * from Users where username = @username and password = @password ) ;

  Would it be better as a cross join?

Not really. I suppose you mean something like that - with either from Reports cross join Users or from Reports, Users:

-- query 2
select r.*
from Reports as r cross join Users as u
where u.username = @username
  and u.password = @password ;

While this would give you the same results, it would only if there is a UNIQUE constraint on Users (username). If there is no such constraint, you may have (however unlikely it may sound) 2 rows with same username and password and in that case get all results duplicated. The EXISTS subquery wouldn't have such a problem.
But my main preference for the exists approach is that it is much cleaner about its intentions (a semijoin, not a cross join). The FROM clause has the table that you intend to have in the result set. The cross join has both tables in the FROM clause and if the developer is not careful enough (i.e. to use SELECT r.* and not SELECT *, the user details will leak in the result. Only if you do want to include the user details in the result set, then yes, the cross join should be preferred.

I'd use aliases in all required places anyway and write the query like this. Note that queries 1 and 3 are identical, except for the use of aliases and formatting:

-- query 3
select r.*
from Reports as r
where exists
      ( select *
        from Users as u
        where u.username = @username
          and u.password = @password
      ) ;


This is a simple "filtered" outer join:

select a.col1, b.col2
from table_a a
  left join table_b b on a.col1 = b.col1 and b.col2 = 1


Are snapshots generated for each sub query inside a Postgres READ_COMMITTED transaction?

The snapshot 'created' is per statement regardless of whether that statement contains subqueries or not. From the documentation:

... This level is different from Read Committed in that a query in a repeatable read transaction sees a snapshot as of the start of the first non-transaction-control statement in the transaction, not as of the start of the current statement within the transaction.


i use http://en.wikipedia.org/wiki/Corosync_%28project%29 and peacemaker.
maybe http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster can help you.


bcp is a command-line command utility - you can't call it as a T-SQL command.  However, if you enable xp_cmdshell, you can run it via the command-line using this code inside a SQL Server Agent Job (or query window in SQL Server Management Studio):
DECLARE @cmd varchar(1000);
SET @cmd = 'sqlcmd -S londata01\raisers -d DataInsight -E -s, -W -w 65535 -Q "SET NOCOUNT on; select * from dbo.All_Email_Addresses" -s "," -o "C:\Users\scarsen\Documents\all_emails.csv"';
EXEC sys.xp_cmdshell @cmd;

In the example above, we're exporting data into the C:\Users\scarsen\Documents folder.  In order for this to work from inside SQL Server, the SQL Server Service Account must have access to this folder.
You can enable xp_cmdshell at the server using these T-SQL commands, which you'd run via a query window in SQL Server Management Studio (SSMS):
EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

You'd only need to enable it once per SQL Server.  In case you want to reverse the above change, simply run this:
EXEC sys.sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

There really is no need to reverse the show advanced options configuration setting.  It doesn't have any bearing at all on anything other than letting you configure things like xp_cmdshell.


You have 3 tables of which only first table is normalized, 2nd & 3rd are not.

Normalization works like this:

students_info
st_id | f_name | l_name | date_of_birth | address  | phone
  1   | quinoo | mickel |   1/1/1990    | nowhere1 | 1-800-1234
  2   | nunoo  | gyan   |   2/1/1990    | nowhere2 | 1-555-1234
  3   | kwanis | nnipa  |   3/1/1990    | nowhere3 | 1-555-4321

classes_info
cl_id | class | academic_year
 13   |  A    | 2014
 32   |  B    | 2014
 73   |  C    | 2014

students_classes
cl_id | st_id
13    |  2
13    |  1
32    |  1
32    |  2
73    |  3

Why you got it wrong? You set 3 students and 3 classes, and put each student to one class. If you have 3 students and 3 classes, and each student is going to two classes, in your example you would have duplication of rows in students_class table.

In basic tables, lets call them OBJECT tables (students_info, classes_info) you CAN NOT have duplication, as every object (student, class) is unique.
In relations tables you have relations and there you can have whatever relations you like, as there you store only id's of basic objects you relate to each other and not all other data pertaining to some object (which is stored in object table). So in students_classes table you have 5 rows, as students 1 and 2 go to two classes each, and 3rd student is going only to one class.
Having 300 students and 20 classes, tables for students and classes whould have 300 and 20 rows respectively, and relations table students_classes would have as many rows as needed, e.g. if every student must take 5 classes, and can take max 10 classes, students_classes table could have from 1500 to 3000 rows (for one academic year).