Unable to trace the transaction log space

why the operations of bulk insertion & deletion didn't reduce the log space, but its size behaves trembling(up and down in stead of continuous increase)?

It's likely that you've missed one of these two steps in your test setup:

the test database is not using the FULL recovery model

ALTER DATABASE [TestDB] SET RECOVERY FULL WITH NO_WAIT;

you have not performed a FULL backup of the test database

BACKUP DATABASE  [TestDB] TO DISK = N'NUL';

Until you've taken a full backup, even if the database is set to use the full recovery model, it's still essentially using the Simple model.  Here's a post where Paul Randal discusses the situation:

Misconceptions around the log and log backups: how to convince yourself

  If you switch recovery models to FULL or BULK_LOGGED, until you take the first full backup, you are still essentially in the SIMPLE recovery model, and so the log will truncate on checkpoint.

Since your question says "checkpoint is done between the query)", this second point is likely your issue.

To answer your other question:

  when the db throw exception transaction log is full - is it due to the out of memory of harddisk storage? or is it due to log size is over the configured size?

See this related question on this site for a lot of details on that topic: Why Does the Transaction Log Keep Growing or Run Out of Space?

It could be that the disk is out of space, or the log can't grow because it's reached it's max size and is unable to grow anymore.  Some solutions to this issue would be to take log backups more frequently, change to a different recovery model, increase the size of the disk where the log file resides, etc.'

Regarding a couple of comments on this answer:

  sorry, i missed one important question, you are correct that actually my database was not in full recovery mode, but in the SSMS UI, it did show the db is in full recovery mode. in this case, where can i check the actually recovery mode?

and

  Is there a way to see if the recovery mode is fully active or not yet because of missing backup (besides query last backup date). And does it count to have a full backup before switching to full recovery mode?

The only way I know of to tell if a backup hasn't been performed since the DB was switch to full is to attempt a log backup.  It will fail with the error "BACKUP LOG cannot be performed because there is no current database backup."

This really shouldn't be a problem in practice, since you're backing up real production databases, right? =P  But in a test environment, I would just run this to see where things stand:

BACKUP LOG NCEB_DMS TO DISK = N'NUL';


It would be something like the following. Please verify the syntax and functionality as I have not tested it. There might be a better way to do this.

SELECT * FROM
(
    SELECT
        @rownum1 := @rownum1 + 1 AS rownum,
        acties.*
    FROM
        acties
        INNER JOIN (SELECT @rownum1 := 0) AS rownum
    WHERE
        type = 1
    ORDER BY
        aanmaak_datum DESC
) AS question
UNION
SELECT * FROM
(
    SELECT question2.rownum, answers.*
    FROM acties AS answers
    INNER JOIN
    (
        SELECT
            @rownum2 := @rownum2 + 1 AS rownum,
            acties.*
        FROM
            acties
            INNER JOIN (SELECT @rownum2 := 0) AS rownum2
        WHERE
            type = 1
        ORDER BY
            aanmaak_datum DESC
    ) AS question2 ON answers.koppel_id = question2.koppel_id
    WHERE answers.type = 2
) as answer2
ORDER BY rownum, type


From researching further, and a bit from the comment on my question, I found that I needed to generate the DDL statements from the previous database. Once doing so, and setting the correct directory, everything worked perfectly.


In Postgres, how to get a list of current defined savepoint?

Interesting question! Short answer: no.

Long answer: there does not appear to be any existing way to get a list of savepoints defined. Even worse, it doesn't seem possible to create a PostgreSQL extension which would let you do this: looking at src/backend/access/transam/xact.c, you can see that functions like RollbackToSavepoint (which is where that "no such savepoint" error message you mentioned comes from) rely on the variable CurrentTransactionState, which is declared static to xact.c, i.e. would not be visible globally to extension code.

Now, if you were daring and quite desperate to generate a list of defined savepoints from the server-side (as opposed to just having your client remember...), you could add in a helper function to xact.c that would display this information for you. In fact, here is just such a patch. That's a very rough patch for illustration purposes only, and just elogs the savepoint names, it should really be returning those names as setof text.

As to why this feature is missing, I surmise that there is simply no plausible use case for a client needing to fetch a list of defined savepoints from the server. What would the client do with this list -- just choose one at random and ROLLBACK to it? ROLLBACK to the last one blindly? AFAICT savepoints are only useful if a client remembers what savepoints it has defined and where they were in order to be able to make use of them.


In SQL Server and other systems that support ROW_NUMBER()...

WITH GoalsWithGoalNum (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY [In]
            ORDER BY [Goal ID]
        ) AS GoalNum
    FROM Goals
)
SELECT *
FROM GoalsWithGoalNum
WHERE GoalNum <= 2;


Is there a way to create a re-usable base table definition in InfoSphere Data Architect?

Sadly, it appears that at least in the version we are currently using (7.5.3.0), that no such functionality exists. Marking this the answer for now, unless someone else discovers otherwise (including within a new version).


SET CONNECTION in PostgreSQL gives "unrecognized configuration parameter"

Converting question comments into an answer

Abelisto:

It seems that you missed a section of the documentation: Chapter 34. ECPG - Embedded SQL in C. The PostgreSQL documentation is not ideal (but IMO it is much better then many others). Hint: to avoid inconveniences look at the page name in the address, for example plpgsql-declarations.html is about DECLARE variables in the plpgsql blocks and sql-declare.html is about DECLARE cursors in the pure SQL.

dezso:

If using psql, you still can make use of \c - especially if it does not require typing a password.


Behavior of STUFF function for empty strings

After closely reading the documentation while posting this question, I realized it told me the answer. I'm still posting it, since I hope it might be helpful to others in a similar predicament.

Per the documentation:

  If start is negative or zero, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.

This means that, effectively, you can't use STUFF with an empty string (since any argument to start will result in a NULL string).

One workaround is to use a non-empty string, and delete the content. For the above example, you could use STUFF('a', 1, 1, 'z') to delete the initial character.


This is one possible approach, although it does not produce exactly what you're asking for, it may be a proxy to get there:

-- Let's assume you have your data in "table_1" and "table_2"
-- They should have an index on column "time"
WITH table_1 (time, value_1) AS
(
VALUES
    ('2017-01-16 16:13:59'::timestamp, 18.9),
    ('2017-01-16 16:14:20', 17.9),
    ('2017-01-16 16:15:08', 19.9)
)
, table_2 (time, value_2) AS
(
VALUES
    ('2017-01-16 16:13:57'::timestamp, 348),
    ('2017-01-16 16:14:05', 350),
    ('2017-01-16 16:14:17', 353)
)

(I've changed "value" to "value_1" and "value_2" for ease)

At this point, we do two things: a UNION of table_1 and table_2 to get all the existing "time" values, then, for each time, make a subquery looking for the value_1 and value_2 closest in time...

-- We first make a UNION to have all present times, then, for each time a SUBQUERY, finding
-- the closest value ORDERing BY time
SELECT
    time,
    (SELECT value_1
       FROM table_1
      WHERE table_1.time <= times.time
   ORDER BY time DESC
      LIMIT 1
    ) AS value_1,
    (SELECT value_2
       FROM table_2
      WHERE table_2.time <= times.time
   ORDER BY time DESC
      LIMIT 1
    ) AS value_2
FROM
    (
    SELECT "time" FROM table_1
    UNION DISTINCT
    SELECT "time" FROM table_2
    ) AS times
ORDER BY
    time ;

The result that you would get is:

|---------------------+---------+---------|
|        time         | value_1 | value_2 |
|---------------------+---------+---------|
| 2017-01-16 16:13:57 |         |   348   |
|---------------------+---------+---------|
| 2017-01-16 16:13:59 |  18.9   |   348   |
|---------------------+---------+---------|
| 2017-01-16 16:14:05 |  18.9   |   350   |
|---------------------+---------+---------|
| 2017-01-16 16:14:17 |  18.9   |   353   |
|---------------------+---------+---------|
| 2017-01-16 16:14:20 |  17.9   |   353   |
|---------------------+---------+---------|
| 2017-01-16 16:15:08 |  19.9   |   353   |
|---------------------+---------+---------|

This is probably not very efficient, but it does (more or less) what you're looking for. It requires that you have an index (DESC) on the "time" column; or the efficiency will be absolutely horrible.

If you want, you can change the "times" query by a generate_series call. It will also work.

NOTE: value_1 on first column doesn't have a value... because we have not yet any reading. For this to work, you would need a whole set of readings at "start time".


What are some good methods of structuring a MySQL audit trail to be both immutable, and perform well for usage?

Are there other ways this has been dealt with?

If you use MySQL Replication, you could have a Master/Slave that has the following:

AUDIT_LOG as InnoDB on the Master
AUDIT_LOG as ARCHIVE on the Slave

This would provide fast writing of the AUDIT_LOG on the Master and makes the data available for reads almost instantly. The Slave would have the backup as an ARCHIVE with a small disk footprint.

DO NOT SEND ANY ALTER TABLE SQL AGAINST AUDIT_LOG ONCE REPLICATION IS SET UP.  Otherwise, you lose the small disk footprint feature of the ARCHIVE Storage Engine.

  Any drawbacks or trade-offs that are apparent?

You must configure InnoDB

with enough RAM to accommodate data and indexes that are frequently queried : What are the main differences between InnoDB and MyISAM?
with tablespaces for table separate from ibdata1 : What is the best way to reduce the size of ibdata in mysql?
to access multiple CPUs (if using MySQL 5.5) : Possible to make MySQL use more than one core?


Not able to connect to local MySql database "only" when connected to VPN on VM

I'm fairly certain the MySQL not only has users listed by name and password, but also by "where they are coming from".  You may want to check and make sure the user is listed as being allowed to connect from "localhost".


Which part of a DB bulk insert takes the most time?

The database page cache is written to disk during the commit. But even that is fast because the OS writes to its own cache. What takes the most time is, at the end of the commit, waiting for the data to be actually written to disk (FlushFileBuffers()).

If you want to minimize the overall time needed for a transaction, you have to limit the amount of data.

Alternatively, consider using asynchronous writes by enabling WAL mode.


On Load sub which checks records causing problems

Don't worry, I've got it! I prefixed it with:

Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo 0
Me.txtCount.Value = rst.RecordCount
intCount = Me.txtCount.Value

Then if the value was zero, it would skip the check code.


Cube with 2 measure groups (with different relationships to dimensions) returns too many dimension members in reports

As you correctly noted this is what happens when you try to display measures across a dimension to which they don't have a relation.

You basically have 2 options

Use IgnoreUnrelatedDimensions
Use an MDX solution

I would suggest you try the IgnoreUnRelatedDimensions first, as the measures would be aggregated better, and NonEmptyCrossJoins would be able to benefit from the Bitmap Indexes used when Attribute Relationships are defined.

The MDX solution is more a last resort because of possible performance issues, and you would also need to define a calculated measure for each measure you have. The MDX solution would most likely be either complex or mess up your totals/show incorrect data.

IgnoreUnrelatedDimensions

When looking at the Adventureworks 2012 cube you have this situation where Employees are not linked to the 'Internet sales' but they are linked to the 'Reseller sales'.

When browsing the cube the result is the same as yours when you add measures from both measure groups across the employee dimension.

You can solve this by setting the IgnoreUnrelatedDimensions property to False on the Measure Group.

After reprocessing the cube and refreshing the browser the result now looks like this:

There are a few caveats with this though, such as an issue with setting DefaultMember

If the DefaultMember is not the All level on one of the attributes of the unrelated dimensions you don't get aggregated data since the default member still acts as a filter even if it's not included in your pivot table:

For example after setting the DefaultMemberproperty on the gender attribute of the employee dimension like this.

This is the result when no slicers are included in the report. Of course, you would want to see the total here:

I seem to recall some other edge cases with calculated measures but I don't remember the specific situation.

MDX Solution

If you run into any of the edge cases you would have to resort to an MDX solution such posted by @GregGalloway, but that could end up being tedious and very specific to a single report or use case:

CREATE MEMBER CURRENTCUBE .[Measures].[Max Current CP With Units] AS
  IIf (
    NOT IsEmpty ( [Measures].[Units] ),
    [Measures].[Max Current CP],
    NULL
  ) ;

The drawback of that solution would be that you would be messing up the way totals look and possibly present incorrect data but that might be acceptable in your specific case of price information with MIN/MAX aggregation but would totally break for sums, and will probably show false data to your users.

For example when running this query

WITH MEMBER [Measures].[Filtered internet order count] as
'
IIf (
    NOT IsEmpty ([Measures].[Reseller Order Count] ),
    [Measures].[Internet Order Count],
    NULL)
'
SELECT {[Measures].[Filtered internet order count],[Measures].[Reseller Order Count]} ON 0,
[Employee].[Employees].allmembers ON 1
FROM [Adventure Works]

Produces this result

Which looks equally strange, since the total is the same as each individual member. But that may be acceptable in your specific case of min/max pricing, I think it's mostly showing false data as there are no internet sales for those employees.

I guess if you control the reports it would be possible. For the added pivot table in your edit I guess it would produce OK results.


Always On: replication over cross server SQL instances

Depending on your requirement, both solution are OK.
In first schema, imagine to have a fault on a server. Or you simply need to do updates. Can a single server handle your work load? If yes, you can save money.

You will need to handle correctly multiple IP for server, or different port for the istances, but it is not a problem if your department is skilled.

I mixed mutiple istances in multiple servers, also mixed failover cluster istances with Always ON. My only suggestion is to implement on SQL 2016 or 2017 if you can. It introduces DAG and is a bit more mature.


Different Disk Drive Same Backend Storage

This will depend on your exact SAN. But in a former job we had a SAN with auto-tiering. In this case, in theory you can divide up your database into files and the most active files would automatically be moved to flash storage by the SAN


Enforcing a "disjoint or identical" constraint

After I posted my question, I thought of a possible solution.

For concreteness, let's say that this is the table definition for F:

CREATE TABLE F (
    X NUMBER(10) NOT NULL,
    Y NUMBER(10) NOT NULL,
    CONSTRAINT xy_unique UNIQUE (X, Y)
);

We could replace this F with tables G, H, and I, defined as follows

CREATE TABLE I (
    ID NUMBER(10) PRIMARY KEY
);

CREATE TABLE G (
    X NUMBER(10) NOT NULL,
    YSET_ID NUMBER(10) NOT NULL,
    CONSTRAINT fk_g_yset_id FOREIGN KEY (YSET_ID) REFERENCES I(ID),
    CONSTRAINT x_unique UNIQUE (X)
);

CREATE TABLE H (
    YSET_ID NUMBER(10) NOT NULL,
    Y NUMBER(10) NOT NULL,
    CONSTRAINT fk_h_yset_id FOREIGN KEY (yset_id) REFERENCES I(ID),
    CONSTRAINT unique_y UNIQUE (Y)
);

The idea is that H is now a table of "y-sets".  Each y-set consists of all the [y]'s corresponding to a given value of H.YSET_ID.  The fundamental difference between F and H is that for H we can define a uniqueness constraint on H.Y, which ensures that these y-sets are disjoint.

Table I's only function is to relate G and H.  (It would be convenient if G.YSET_ID could refer directly to H.YSET_ID, rather than indirectly via I.ID, but my understanding is that a foreign key must always refer to a primary key...)

(I don't know much about Oracle's version of SQL, so this solution is bound to be clumsy, or even syntactically incorrect.)

For example, if F is

 X  Y
10  0
10  1
20  2
20  3
20  4
30  0
30  1

...then the corresponding tables G, H, and I could be

G:
X  YSET_ID
10 100
20 101
30 100

H:
YSET_ID Y
100     0
100     1
101     2
101     3
101     4

I:
ID
100
101

As pointed out by @ypercubeᵀᴹ, the original F can be recovered from the new G, H, and I tables with

SELECT G.X, H.Y FROM G JOIN H ON G.YSET_ID = H.YSET_ID;

EDIT: Yet another afterthought: it turns out the there is a surprisingly symmetrical variation of the solution above:

CREATE TABLE I (
    ID NUMBER(10) PRIMARY KEY
);

CREATE TABLE G (
    X NUMBER(10) PRIMARY KEY,
    YSET_ID NUMBER(10) NOT NULL,
    CONSTRAINT fk_g_yset_id FOREIGN KEY (YSET_ID) REFERENCES I(ID)
);

CREATE TABLE H (
    Y NUMBER(10) PRIMARY KEY,
    YSET_ID NUMBER(10) NOT NULL,
    CONSTRAINT fk_h_yset_id FOREIGN KEY (yset_id) REFERENCES I(ID)
);

Even though it is not clear from my problem description, a table satisfying the desired constraint expresses a bijective function whose domain and codomain are both sets of disjoint sets.  My second solution above makes this symmetry manifest.


Syncing two databases in a redundant network architecture

That's tough. Even 5 minutes of downtime for a reboot would put you in a situation with out of sync data. I really think you should look into other options.

I'd definitely recommend mirroring here instead of what the app is doing. With Synchronous Database Mirroring with a witness, the two databases will be in sync and you'll even get automatic failovers. It's not hard to setup either.

You could use Transaction Replication as it can handle a server being down and pickup where it left off. But I don't consider it to be an HA or DR technology. Mirroring, Log Shipping and Availability Groups are the HADR technologies. Specifically Sync mirroring and Sync AG are HA, async mirroring, LS and async AG would be DR.


Getting Master Current Binlog and Binlog Position in Replication Backup

Surprise, you can get the Master's binlog file/position from the dump !!! Use --dump-slave.

MySQL 5.6 Documentation says :

  This option is similar to --master-data except that it is used to dump a replication slave server to produce a dump file that can be used to set up another server as a slave that has the same master as the dumped server. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped slave's master. These are the master server coordinates from which the slave should start replicating.

Just get the CHANGE MASTER TO command from Lines 23-25 of the dump as always.


There are a couple of things here.  First performance monitoring is a subject that is very broad.  When I started down the rabbit hole of performance monitoring I found Brent Ozar and Paul Randal's blogs very helpful.

Specifically Brent's post on perfmon counters.  Its a little old but still a very good resource.  To go along with that is Paul's post on I/O latencies and Brent's How to Prove Your SQL Server Needs More Memory

You will notice that most of the information I have posted here is from only two sources but they are the first place I tend to look.  The second place I look to is Google.  On top of those there are third party monitoring solutions such as Red Gate SQL Monitor, Confio Ignite (now Solar Winds) and many others.

The way I learned how to do performance monitoring was by being in a situation where I thought something was going on and so I started to try and prove it.  Google was my best friend to find blogs and other information as well as reading this site and others as people asked questions.  Once I got enough information from perfmon and other sources to point out that there was something wrong but we needed better visibility into the server I was able to get a third party monitoring tool which really helped.  Turns out we did not really need more of something just tune several queries.


To answer your question. No! There is no fail on your logic and it should work without problems. However, if LVM snapshots can be used, it's better way to do backups.


How to count unique records within a time range in MySQL?

This query will give you the answer you are looking for:

select s.id, count(*) as visits from
(select distinct id, concat(hour(door_entry), floor(minute(door_entry)/30))
from visit_log) s group by s.id


Ola Hallengren index maintenance - long periods of time between commands?

Digging a bit on this, using a common query to retrieve index fragmentation took just about an hour even to return 160 rows on a 1.5TB database:

SELECT  s.name AS schemaname ,
        t.name AS tablename ,
        t.object_id ,
        i.name AS indexname ,
        i.index_id ,
        x.page_count ,
        x.avg_fragmentation_in_percent ,
        x.avg_page_space_used_in_percent ,
        i.type_desc
FROM    sys.dm_db_index_physical_stats(DB_ID(
                                ), NULL, NULL, NULL, 'LIMITED') x
        INNER JOIN sys.tables t ON x.object_id = t.object_id
        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        INNER JOIN sys.indexes i ON x.object_id = i.object_id
                                AND x.index_id = i.index_id
WHERE   x.index_id > 0
        AND alloc_unit_type_desc = 'IN_ROW_DATA'
        AND x.page_count > 1000;

Which leads me to believe that when this part of the index maintenance routine runs, it's taking a very long time at the beginning of a new database to collect initial information and build commands.

SET @CurrentCommand12 = @CurrentCommand12 +
'SELECT @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent),
@ParamPageCount = SUM(page_count)
FROM sys.dm_db_index_physical_stats(
@ParamDatabaseID, @ParamObjectID, @ParamIndexID,     @ParamPartitionNumber, ''LIMITED'')
WHERE alloc_unit_type_desc = ''IN_ROW_DATA'' AND     index_level = 0'

EXECUTE sp_executesql @statement = @CurrentCommand12, @params = N'@ParamDatabaseID int,
@ParamObjectID int, @ParamIndexID int, @ParamPartitionNumber int,
@ParamFragmentationLevel float OUTPUT, @ParamPageCount bigint OUTPUT',
@ParamDatabaseID= @CurrentDatabaseID, @ParamObjectID = @CurrentObjectID, @ParamIndexID =
@CurrentIndexID, @ParamPartitionNumber = @CurrentPartitionNumber,
@ParamFragmentationLevel = @CurrentFragmentationLevel OUTPUT, @ParamPageCount =
@CurrentPageCount OUTPUT


Is it possible to use variables in MySQL schema definition?

You cannot use variables for object names. What you can do is compose the statement string dynamically and PREPARE then EXECUTE it.

SET @DBNAME = 'users';
SET @STMT = CONCAT(
'CREATE TABLE `some_table` (',
'  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,',
'  `user_id` bigint(20) unsigned DEFAULT NULL,',
'  PRIMARY KEY (`id`),',
'  KEY `user_id` (`user_id`) USING BTREE,',
'  CONSTRAINT `key_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES ', @DBNAME, '.`users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION'
);
PREPARE s1 FROM @STMT;
EXECUTE s1;


'Out of memory' Error for MariaDB on RDS

Usually the quick fix is to decrease the biggest memory usage -- which should be innodb_buffer_pool_size.  What is that currently set to?  Do you have other applications running on the same VM?  How much room are they taking?

On a 4GB 'machine', I don't recomment innodb_buffer_pool_size be higher than 1500M, but less if the server is not dedicated to MySQL.


SQL Server service is running under an account that is a member of the built-in Administrators group

The built-in Administrators group is a machine-local group.  You need to connect to the machine where SQL Server is running via the Computer Management console, and remove the SQL Server service account from the "Administrators" group, as in:

Be aware, the SQL Server may, for some reason, require access to the local machine via membership in the local Administrators group.  Only you can determine if this is required for your specific instance, since it is not required for an out-of-the-box install.


expdp full export not exporting all schemas

This is expected behaviour.

FULL

  A full export does not, by default, export system schemas that contain
  Oracle-managed data and metadata. Examples of system schemas that are
  not exported by default include SYS, ORDSYS, and MDSYS.

12.1.0.1 EXPDP Does Not Export APEX Schema (Doc ID 1912162.1)

  This is the expected behavior in 12.1.0.X version,

  This was added, in part, to avoid problem with common objects in a
  multi-tenant DB, but certainly has been very helpful in eliminating
  errors  report - particularly in full import.  The bit is called
  'ORACLE_MAINTAINED' in [user,all,dba]_objects  dictionary views. Seems
  pretty reasonable that Oracle maintained objects are  not be moved
  around by datapump.


Maybe you could try with:

WHERE c.tid = :id AND c.pid IN(0,1)

or

WHERE c.tid = :id AND c.pid <=1

but I am not sure what you want to get. Maybe an UNION is more appropriate.

-- only 20 with c.pid = 0
(SELECT *, c.id AS cid, u.id AS userid
FROM comments c INNER JOIN users u ON u.id = c.uid
WHERE c.tid = :id AND c.pid = 0
LIMIT 20)
UNION
(SELECT *, c.id AS cid, u.id AS userid
FROM comments c INNER JOIN users u ON u.id = c.uid
WHERE c.tid = :id AND c.pid = 1)
ORDER BY c.posted ASC


Not able to regulate/auto grow tempdb size

As Tibor mentioned in the comments, add your SQL Server Service account to the Perform volume maintenance tasks local security policy so it can perform Instant File Initialization.  I personally don't feel that alone will solve your problem as I suspect you may be running into the byproduct of thin provisioned disk.  A nice definition being as follows:

  A thin-provisioned virtual disk consumes only the space that it needs initially, and grows with time according to demand.

First, are these servers VMs?  If so, is your infrastructure team presenting VM disk that has been thin provisioned?

Alternatively is the SAN presenting thin provisioned disk to the VM or physical hosts (in the event they are not VMs)?

What can happen with thin provisioned disk is that when additional space is requested to be allocated, and the underlying disk pool/group is over-allocated, you'll run into errors similar to what you're seeing as there's no more actual storage available.  From the OS perspective, the disk has plenty of free space to expand into, but you get these random errors that make no sense.  Databases tend to suffer most from thin-provisioning as they often request resources in large quantities and need said resources quickly, and thin-provisioning mechanisms are built for slow and steady rather than varied and rapid.

It is pretty easy to identify if this is your issue or not as you only need to reach out to both your VM admins and your storage admins.  Ask if any of the underlying disk the TempDB (or any other database) files are on are thin provisioned, and if so how much actual space is available to use.  I've run into scenarios where the OS showed I had a 1 TB drive, but in actuality, I was limited to significantly less space as the underlying disk pool didn't have enough actual storage to honor the extra space the OS was told it has.

I hate when resources are thin provisioned (especially when this happens with memory) because you're not working with all the resources you've been promised.  It's basically like telling your kids they can spend $100 but you only have $80 available and you just hope they are fiscally responsible.  I've yet to work in an environment where thin provisioning doesn't eventually cause issues.  Database Servers should be built for usage spikes rather than averages, and infrastructure admins hate seeing idle resources so many times they offer thin-provisioning as a compromised approach.


Try following change. It might work.

select d.count1 , d.IVR_APPLN_NAME
from (
        SELECT COUNT(distinct B.CALL_ID) as count1,
               C.IVR_APPLN_NAME ,
               C.IVR_APPLN_NAME || '' as  test_name
        FROM PTDT.TDT_CALL_DATA AS A,
             PTDT.TDT_IVR_CALL_APPLICATIONS AS B,
             PTDT.TDT_IVR_APPLN_REF AS C
        WHERE A.CALL_ID = B.CALL_ID AND B.IVR_APPLN_ID = C.IVR_APPLN_ID
        GROUP BY C.IVR_APPLN_NAME
     ) as D
where D.test_NAME in ('DSC','EMS','FAO','RET','YAS')

This SQL is different from the SQL in the question.  By using a derived column (test_name) instead of original column (IVR_APPLN_NAME) in where clause, we might be able to outsmart the optimizer. I tested this strategy on our system using different tables and it worked.


Migrating to Oracle Flashback for Historization on Oracle 11g

It sounds like Oracle Total Recall may fit your needs:

Regulatory oversight such as Sarbanes-Oxley, HIPAA, and Basel-II, as
well as internal audits, require companies to keep historical data
available for long periods of time. Oracle Total Recall, part of
Oracle's comprehensive portfolio of database security solutions, works
with Oracle Database 11g, Enterprise Edition to help companies store
their data in secure, tamper-proof databases while keeping it
accessible to existing applications

The documentation states that it can support refactoring:

In Oracle Database 11g Release 1, the Add Column DDL operation is
supported with Flashback Data Archive. With Oracle Database 11g
Release 2, the following DDL operations are supported, with full
support for Flashback queries across all associated changes:
• Add, Drop, Rename, Modify Column
• Drop, Truncate Partition
• Rename, Truncate, Drop Table
For more complex DDL –
upgrades, split table, etc. – the Disassociate and Associate PL/SQL
procedures can be used to temporarily disable Flashback Data Archive
on specified tables.


The problem with your query is that the subquery is not correlated. You dodn't need and you shouldn't use lessons inside the subquery.

UPDATE lessons
SET minicourse_id = subquery.minicourse_id
FROM (
  SELECT topics.minicourse_id
  FROM topics
  WHERE topics.id = lessons.topic_id   -- this refers to the "lesson"
                                       -- of the main query
) AS subquery ;

In fact, it can be written without a subquery at all:

UPDATE lessons
SET minicourse_id = topics.minicourse_id
FROM topics
WHERE topics.id = lessons.topic_id ;

or with a different subquery:

UPDATE lessons
SET minicourse_id
    = ( SELECT minicourse_id
        FROM topics
        WHERE id = lessons.topic_id
      ) ;

Regarding your design, I assume you have added a lessons.minicourse_id column and this foreign key:

ALTER TABLE lessons
    ADD minicourse_id INT ;

ALTER TABLE lessons
    ADD FOREIGN KEY (minicourse_id)
        REFERENCES minicourses (id) ;

While this achieves what you want, there is a small issue: you may end up with rows in child (lessons) that refer to a grandparent (minicourse) A and also to a parent (topics) that refers to a different grandparent B.

Of course if all your applications and users have code that is correct, this won't happen. But I suggest you enforce this in the database level and not (only) in the application level. This is quite easy to do, with the following change in the foreign keys.

It basically sets the FK to minicourses to be "through" lessons, without a direct FK. You'll still be able to use direct joins between lessons and minicourses:

ALTER TABLE lessons
    ADD minicourse_id INT ;

-- the UPDATE is the same!
UPDATE lessons
SET minicourse_id = topics.minicourse_id
FROM topics
WHERE topics.id = lessons.topic_id ;

ALTER TABLE lessons
    ALTER minicourse_id SET NOT NULL ;

-- we need this for the FK below
ALTER TABLE topics
    ADD CONSTRAINT minicourse_topic_UQ
        UNIQUE (minicourse_id, id) ;

-- the FK is "lessons -> topics"
ALTER TABLE lessons
    ADD CONSTRAINT lessons_to_topics_FK2
        FOREIGN KEY (minicourse_id, topic_id)
        REFERENCES topics (minicourse_id, id) ;

-- drop the previous FK to topics
ALTER TABLE lessons
    DROP CONSTRAINT lessons_to_topics_FK ;


How to change ANSI_NULLS option for a table?

As far as my quick and dirty tests show (using ALTER TABLE and ALTER INDEX), no

The setting is captured at CREATE time.


I can't say for certain because I've never dealt with that large a stored procedure but you could try this:

SELECT query_plan
FROM sys.dm_exec_procedure_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE object_id = object_id('procedure_name')

Then save the output to a text file.


You must declare it as (n)varchar(x) if you need a variable length string:

ALTER PROCEDURE [dbo].[filter_opombe]
(
@opombe_par nvarchar(256)
)
AS
BEGIN
(
SELECT        Id_opombe
FROM            opombe
WHERE        (CHARINDEX(@opomba_par, opomba,0) > 0)
)
END

With a (n)char data type, shorter string will be stuffed with white space at the end: 'x' with data type nchar(5), becomes 'x    '.


Designing SQL database with two types of users

If that is a good model or not depends mostly on how you use the tables:

If you expect that many queries or foreign key constraints will refer to “brand and/or customer”, your proposed model may be the way to go.
If the tables are mostly used separately, using only two tables might be better.

If your concern is mostly that the tables should share certain columns, you could also use PostgreSQL table inheritance:

CREATE TABLE users (
   id bigint PRIMARY KEY,
   username text NOT NULL,
   email text,
   password text NOT NULL
);

CREATE TABLE brands (
   storename text NOT NULL,
   ...
) INHERITS (users);

CREATE TABLE customers (
   stripeuserid text NOT NULL,
   ...
) INHERITS (users);

Then brands and customers are separate tables, but they are guaranteed to have the same columns as users (and others in addition).

Then you can query users to see rows from both customers and brands, but you cannot use users as the destination of foreign keys to either customers or brands.


Return a percentage of the full record set

You really should be using the TOP clause.  This is perfectly legal (assuming you're using SQL Server):

declare @top_val int = 30

select top (@top_val) percent
    col1,
    col2,
    col3
from yourTable
order by <something>;

The above query will select the top 30% of the rows.  As far as I can tell from your question, this satisfies your requirements and is the simplest method.


ALTER TABLE ... DISABLE TRIGGER ... takes a SHARE ROW EXCLUSIVE lock. So no relevant concurrent operation can happen on the table:

  DISABLE/ENABLE ... TRIGGER

  This command acquires a SHARE ROW EXCLUSIVE lock.

where the docs on lock levels say that:

  SHARE ROW EXCLUSIVE

  ... This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time.

Despite the name, a SHARE ROW EXCLUSIVE lock is a table-level lock. The name is historical.

Now, PostgreSQL has a mechanism you can (ab)use to skip triggers firing on certain operations. It's intended for replication tools like Slony-I and Londiste, and is called session_replication_role. Be aware that using it for other purposes may have "surprising" consequences if you then start using a logical replication tool.


My understanding is that, with the Readable Secondary option set to Yes on both replicas, any connection string with applicationIntent=ReadOnly will route to Node1

No it should first go to node 2 because the Node 2 is read only replica and Node 1 is primary replica which will server to read write operations. As per BOL

  Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role)

What is output of

SELECT ag.name as "Availability Group", ar.replica_server_name as "When
  Primary Replica Is",
rl.routing_priority as "Routing Priority",
ar2.replica_server_name as "RO Routed To",
ar.secondary_role_allow_connections_desc,
ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
inner join sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

So from output you can see when Node 1 is primary replica read only connections will go to Node 2 as first preference and vice versa


Group Database Entries by time difference

With the comment from FrustratedWithFormsDesigner, I came to the following solution:

SELECT subq2.*, sum(new_group) OVER (ORDER BY t ASC) AS group_id
FROM (
  SELECT subq.*, CASE WHEN delta > 1500 THEN 1 ELSE 0 END AS new_group
  FROM (
    SELECT t, lag(t) over (ORDER BY t ASC),
      t - lag(t) over (ORDER BY t ASC) AS delta
    FROM time_points
  ) AS subq
) AS subq2

I am using the sum of all new_group values to create different groups.
Thank you very much!


How to get past account lock information in oracle database?

You can't. The database stores the actual status of an account and the time since it is expired or locked, but no history. The only history it stores is the password history, for enforcing password policies.


What are the user privileges required to for an ssrs report owner to 'send email'

Is the new user allowed to send mail on the SMTP server?

You can do this:

Review this and make sure your setup is valid with the correct user (MSDN Link).
Also check the settings in RSreportserver.config (MSDNlink).


For now, I see two possible problems in your model:

Member of a team can exist without a reference to Student.

1.1. If you delete a Member with ON DELETE CASCADE foreign key option in Student it will remove your student.

1.2. If you delete a Student, there might be a Member, that not referenced to any Student.
If you would like to place Member properties to Student table, then you will have to set them to NULL each time, then you need to unreference Student and Member. Also it will lead to table Student will loose it's independence. If you would not do that, this proplem will never happen.

I might suggest something like this instead:

Where: AI = AUTO_INCREMENT, NN = NOT NULL, UN = UNSIGNED.

Student can not exist in two teams, because there might be only one record in table Member for each Student record.
Student can be a member if record in the Member table exists for the Student table record. However, he might have no record in the Member, but still have a record in the Student table. Means, that he is not member of any team.

How to achieve that?

Make a primary key of the Member table to be a foreign key, that referes to Student table's primary key. PRIMARY KEYs are UNIQUE by default and can not have duplicate values. So there wouldn't be two records in the Member table for one record of table Student.
Make a foreign key for the table Member, that referes to the Team table's primary key. Only member can have a team.
As there is only one record for each Student can be placed in Member table, each student will have to be only in one team.
If you delete Member record, Student associated record will no longer be a member of any team, but still be a student.

Test script (assuming MySQL RDBMS):

DROP SCHEMA IF EXISTS `test`;
CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `test`;

CREATE TABLE IF NOT EXISTS `Team` (
  `IDTeam` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `etc` VARCHAR(45) NULL,
  PRIMARY KEY (`IDTeam`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Student` (
  `IDStudent` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`IDStudent`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Member` (
  `IDMember` INT UNSIGNED NOT NULL,
  `TeamID` INT UNSIGNED NOT NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`IDMember`),

  INDEX `fk_Member_Team` (`TeamID` ASC),

  CONSTRAINT `fk_Member_Student`
    FOREIGN KEY (`IDMember`)
    REFERENCES `Student` (`IDStudent`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `fk_Member_Team`
    FOREIGN KEY (`TeamID`)
    REFERENCES `Team` (`IDTeam`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


Why does PostgreSQL FDW talk to multiple backend servers for this point query?

PostgreSQL lacks the insight into the "mod" function that it would need to declare that user_id=97 also implies that mod(user_id,4)=1.  If you provide that insight manually, it would likely honor it:

WHERE user_id=$1 and mod(user_id,4)=mod($1,4)

This has nothing to do with FDW.  If all partitions/tables were local, the answer would remain the same.

You could use hash partitioning, then it would generate the insight automatically.


How can I represent a one-to-many relationship with three tables?

When representing one-to-many (1:n) relationships no "intermediate" table is neccessary. You can simply model that with two tables, one for the 1 cardinality and the other for the n cardinality. The outcome is that one row in the referenced table can be referenced by many rows in the referencing table.

You need an "intermediate" table for representations of many-to-many (n:m) relationships only, because you cannot directly model the situation as described above in both directions.


Alternative to triggers on system views for user auditing

You can always tap up the default trace to catch any changes to objects, including stored procs.

Give the following script a whirl, it's easy enough to find out who changed what and when.  It's helped me catch out more than one sly developer ;)

SELECT  t.DatabaseName, t.ObjectID,t.NTUserName,t.HostName,t.StartTime, te.name
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL13.SQL2016\MSSQL\Log\log.trc', DEFAULT) t
JOIN sys.trace_events te  on t.EventClass = te.trace_event_id
ORDER BY t.StartTime DESC;

You'll need to change the path to wherever your SQL trace file is, to find out where it is run:

select path
from sys.traces
where id = 1

One tip, remove the _number from the file name to read all available trace files e.g. if the file name is log_16.trc, change it to log.trc.


Use PostgreSQL builtin operator <@ after including extension intarray

If you installed the intarray extension to the public schema (or some schema other than pg_catalog), you can schema-qualify the operator to choose the built-in one.

Instead of

a <@ b

you can write

a OPERATOR(pg_catalog.<@) b

to schema-qualify it.

e.g.

test=# SELECT 1 OPERATOR(pg_catalog.+) 2;
 ?column?
----------
        3
(1 row)


How to create a job that uses Powershell script?

You need to pass the powershell file in as an argument

powershell.exe -file C:\Users\domainAccount\Desktop\deleteold.ps1 -FolderPath \\192.168.x.x\Backups -FileAge 2 -ListOnly -LogFile \\192.168.x.x\Backups\log.log

Or if the step type doesn’t have to be CmdExe you can change the type to PowerShell


How to compare strings as int in EAV model?

easiest is to ignore anything that does not look like an integer.

select e.* , av.value
FROM attribute_value AS av
JOIN attribute AS a ON av.attribute_id = a.id
JOIN entity AS e ON av.entity_id = e.id
WHERE
   a.label = 'marks12'
 AND
   CASE WHEN av.value ~ '[+-]?[0-9]{1,20}' THEN av.value::bigint END < 100;

this is much harder when value might be a date,


It looks like the database server updates include client updates.

See this CPU:

http://www.oracle.com/technetwork/security-advisory/cpuapr2017-3236618.html#AppendixDB

Includes a fix to SQL*Plus which is part of the client.

Bobby


What are some practical use cases for memory tables?

If you are learning MySQL, skipping MEMORY storage engine is not a bad idea. InnoDB is the default storage engine and it is suitable for most cases. Only in edge cases it makes sense to use another one, and you probably shouldn't consider this option before knowing InnoDB quite well.

However, to answer your question, first let's check pro's and con's of MEMORY.

Cons:

You could lose your data (except for table definition).
No transactions, no consistency of any kind.
Only supports HASH indexes, so there is no optimisation for queries like WHERE col > n, ORDER BY or GROUP BY.
Massive usage consumes memory. No trivial way to monitor how much memory you are using for these tables.

Pros:

Not dramatically faster than InnoDB (in terms of latency), but still generally faster when used properly.
Better throughput because you don't talk to storage devices.

So the question becomes, which use cases don't suffer because of the cons and take advantage of the pro's? Well, a first obvious generic answer is, avoid all use cases where data loss is a problem. More specifically, you could consider MEMORY for:

Caches: all queries are point searches, data can always be reconstructed.
Speed up some massive insertion. You put data into a MEMORY table temporarily, do your transformations, and copy the rows to a "regular" table. But if your transformations require BTREE indexes, this is not a good option.
TEMPORARY tables, when you don't need BTREE indexes.


Infrastructure for Highly Concurrent, High Write DB

If you are using MySQL as the main database, you may want to consider using a Star Topology via MySQL Replication.

Now, before you say UGHHH, ROFL and OMG to MySQL Replication, hear me out.

A star topology allows you to write to one DB server (called a Distribution Mster [DM]) and send the SQL commands to several DB servers. How do you setup such a DB infrastructure?

Here is the Description

You have 5 DB servers (server A,B,C,D,E)

Server A

In MySQL Replication setup, it will be the Master
Plays special role as the DM
Master of servers B,C,D,E
All tables use the storage engine BLACKHOLE (/dev/null)
Only stores binary logs
Bare Metal Machine
Benefits

Very fast writes since all tables on the DM use BLACKHOLE
Network Latency is less of a issue since reads are 15-30% of DB Activity
All slaves are updated strictly from the DM

Servers B,C,D,E

Slave of A
Server a base for heavy SELECTs
Server Can Be Virtual or Bare Metal
For all servers whose user tables use the storage engine InnoDB

It can server as a warm standby DB Server
Nonintrusive backups can be run against it

For all servers whose user tables use the storage engine MyISAM

Set up with read-only oprion
Tables can have their row formats redone to accelerrate reads

I have written posts on this before

https://serverfault.com/questions/264374/mysql-in-star-topology/264444#264444
https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5781504#5781504

To keep MySQL Replication in tip top shape

Use MySQL 5.5
Use Semisynchronous Replication
Tune InnoDB to engage more CPUs


Is it a good idea from a performance perspective to store audio files in a mysql database?

MySQL/InnoDB is a relational database management system which works best when a significant part of its data is on memory for fast retrieval. Traditionally, it has been said that you should keep only small data (integers, floats, smallish pieces of text) on MySQL and use the filesystem, or a dedicated blob/file storage for extreme cases. The reasons are several:

Metadata and small data can be effectively indexed on a MySQL a table and accessed and searched quickly, but huge amount of text or binary data cannot (even fulltext indexes are limited in capabilities, and other string search engines are recommended)
For a long time, small updates on large blobs meant that the whole field was updated and duplicated, even if a single bit was updated
Large blobs tend to be stored off-row, losing some of the advantages of in-row storage
In general, metadata tends to require very fast access (e.g. search a particular row among millions) while retrieving a large file is usually more lenient on latency
MySQL/InnoDB caching system is not optimized for large IO/operations, making it potentially less efficient than the OS caching system. You want probably to save your buffer pool (MySQL application cache) for in-memory metadata retrieval, and don't cache or cache only once on the OS large files in a more simplistic way (different access patterns)
Normally, MySQL query results are handled in-memory on the application, which is fast and nice for KB or results, but it would be highly inefficient for MB of files, which if they were stored separately of the filesystem or a dedicated blob storage, it would not affect the application's memory
MySQL/InnoDB in general is optimized for small transactions, and you may find latency problems on IO operations and replication for very large transactions. That is even more true for distributed and clustered topologies. The need for ACID properties on files is usually non-existent of very low (files are normally added to the storage and have very low consistency needs)
Backing up MySQL gets more and more difficult the more data you have due to consistency requirement. Backing up a directory of files is usually trivial.

For all those reasons and some more I may be forgetting, it is a common advice to store on the database only a reference of the files (e.g. name, path, hash) and store the actual files separately. You can break the rules, no answer is absolute (e.g. you will not hit a scalability problem with 10MB of small files), but that is the reason why filesystem/hadoop/ceph/S3/etc. are chosen and why files are cached/dealt with on a separate pipeline, to allow for future scalability.

Regarding some advantages:

probably it will be a simpler setup (less technologies, less code, and less logic to handle distributed files- as that is all handled transparently by the db)
It will be simpler to maintain consistency between data and metadata, as a single agent will be used to control both; not only for the application, also for things like backups
Even if MySQL may not be the best technology, it may be better to keep it there than on the filesystem if you are handling millions or billions of small files, e.g. on a single directory


This would produce the expected result:
create table #demo (id int, race int, lap int)
insert into #demo values (1,21,11),(2,21,null),(3,21,null),(4,21,null),(5,29,65),(6,29,null),(7,29,null),(8,29,null);

with CTE as
(select race, ROW_NUMBER() over (partition by race  order by race) "extra_lap" from #demo where lap is null),
CTE2 as
(select race, lap "lap" from #demo where lap is not null)
select race, lap from CTE2
union
select CTE.race, CTE2.lap + CTE.extra_lap "lap" from CTE join CTE2 on CTE.race=CTE2.race

drop table #demo;


Is there a cool way of performing CU updates for SQL Server on hundreds of machines?

The cool way is to use an unattended installation script that simply is called from each server, where the media for the installation is on a central directory on your network, that is accessible from each server. You will have to run the script in an elevated mode, which this is fairly easy in PowerShell.

You will need to use the CLI to extract the hotfix, I found that here. Then the CLI options for the SQL Server patch can be found here.

The following commands are what I used to apply the CU6 patch to the local SQL Server 2012 SP2 instance on my laptop. I changed to the directory I downloaded the hotfix to:

.\SQLServer2012-KB3052468-x64.exe /X:E:\Software\SQL_Server\2012\SP2_CU6\Extracted
cd Extracted
.\setup.exe /action=patch /instancename=SQL12 /quiet /IAcceptSQLServerLicenseTerms

You pretty much just work out writing this as a script for each server, then pull in that bit of the summary log (or just the whole log) to a central spot so you can go back and determine the status. Option 2 would be to just include a few lines of code to connect to each instance and verify the build number.


SQL Server Linked Servers and Remote Statistics

What I do is this 'trick':

Give this 'linkuser' read, whatever else needed and ddl_admin privileges, then run the following script to ensure that none of the ddl_admin privileges can actually be used.

But by being technically member of ddl_admin the user can now see statistics in the remote db! (note: from SQL2012SP1 there is no need for this hack, the stats are visible as they should be)

DENY CREATE TABLE TO [LinkUser]
DENY CREATE VIEW TO [LinkUser]
DENY CREATE PROCEDURE TO [LinkUser]
DENY CREATE FUNCTION TO [LinkUser]
DENY CREATE RULE TO [LinkUser]
DENY CREATE DEFAULT TO [LinkUser]
DENY CREATE TYPE TO [LinkUser]
DENY CREATE ASSEMBLY TO [LinkUser]
DENY CREATE XML SCHEMA COLLECTION TO [LinkUser]
DENY CREATE SCHEMA TO [LinkUser]
DENY CREATE SYNONYM TO [LinkUser]
DENY CREATE AGGREGATE TO [LinkUser]
DENY CREATE MESSAGE TYPE TO [LinkUser]
DENY CREATE SERVICE TO [LinkUser]
DENY CREATE CONTRACT TO [LinkUser]
DENY CREATE REMOTE SERVICE BINDING TO [LinkUser]
DENY CREATE ROUTE TO [LinkUser]
DENY CREATE QUEUE TO [LinkUser]
DENY CREATE SYMMETRIC KEY TO [LinkUser]
DENY CREATE ASYMMETRIC KEY TO [LinkUser]
DENY CREATE FULLTEXT CATALOG TO [LinkUser]
DENY CREATE CERTIFICATE TO [LinkUser]
DENY CREATE DATABASE DDL EVENT NOTIFICATION TO [LinkUser]
DENY CHECKPOINT TO [LinkUser]
DENY ALTER ANY SCHEMA TO [LinkUser]
DENY ALTER ANY ASSEMBLY TO [LinkUser]
DENY ALTER ANY DATASPACE TO [LinkUser]
DENY ALTER ANY MESSAGE TYPE TO [LinkUser]
DENY ALTER ANY CONTRACT TO [LinkUser]
DENY ALTER ANY SERVICE TO [LinkUser]
DENY ALTER ANY REMOTE SERVICE BINDING TO [LinkUser]
DENY ALTER ANY ROUTE TO [LinkUser]
DENY ALTER ANY FULLTEXT CATALOG TO [LinkUser]
DENY ALTER ANY SYMMETRIC KEY TO [LinkUser]
DENY ALTER ANY ASYMMETRIC KEY TO [LinkUser]
DENY ALTER ANY CERTIFICATE TO [LinkUser]
DENY REFERENCES TO [LinkUser]
DENY ALTER ANY DATABASE DDL TRIGGER TO [LinkUser]
DENY ALTER ANY DATABASE EVENT NOTIFICATION TO [LinkUser]


Sql Server Performance Degradation in moving from 2005 to 2008

Did you rebuild indexes and statistics after setting up the database on SQL Server 2008?
It's mentioned on MSDN (see "Next steps")

...
Next Steps
After you upgrade to SQL Server ...
Update statistics — To help optimize query performance, we recommend that you update statistics on all databases following upgrade. Use the sp_updatestats stored procedure to update statistics in user-defined tables in SQL Server databases.
...


Database Transfer from PostgreSQL to MySQL

Pg_dump will create a plaintext SQL script with the statements needed to re-create the  schema and the data. You can then import this script into mysql. You may need to tweak the script in certain places due to postgres/mysql differences.


Separating the affiliates into their own tables would usually be a bad idea. It will not save you anything and will make reporting across populations should you need to later.

I would go further than ypercube suggests: why not record each sale individually?

Clicks:    Affiliate            ClickTime
           (FK to aff. table)   (timestamp)

Salses:    Affiliate            SaleTime      SaleAmount
           (FK to aff. table)   (timestamp)   (fixed precision decimal)

Yes this will take more space, but far from a lot - if this much space is a problem then you are working with a extrememely limited hosting solution. With daily recording as suggested by ypercube each row is going to be 28 bytes (assuming 4 for date, everything else in your table being 4-byte ints, and the affiliate ID being an int also) plus index load. Assuming you need enough indexes to more-or-less double the storage requirements (for tables that do not have many columns this is not unusual, though almost certainly overkill for these tables), 30,000 rows per month is still only ~1.6Mb/month.

The above structure gives you much more detail should you need it for reports later. If you specifically need to report on the basis of days, you can either split the date and the time into separate fields or denorlamise a tad and keep the full timestamp plus a date-only field. You can then generate a daily report for each affiliate just by grouping by the date, affiliate, and data fields and using the count and sum aggregates appropriately.

With proper FKs and other indexes the extra detail should not impose performance issues. Databases are designed for handling large amounts of data (and this is far from large) in tables - millions of rows should not be a problem in that respect, never mind tens of thousands.


Restore a SQL 7 database into SQL 2014 without in-between versions?

If I was in your place, I would go the below route which is not as tedious as you would think of.

Backup your sql 7.0 database and restore it on sql server 2005.
Backup your sql server 2005 database and restore on sql server 2014.

Its a myth that you cannot upgrade a database to a version more that is more then two versions newer.

I have written an elaborated answer about the pre and post migration steps that will be a handy reference to you.

For automation, you can use dbatools (powershell based) - start-sqlmigration that supports migration from sql server 2000 to 2016 (newest version out).

Note: You have to test out a lot of stuff as this is a major leap for your application !


Can you have a WHERE clause with a greater than operator on a composite key?

You can use

SELECT *
FROM `bbcmapimp_pc_raw`
WHERE (`pc_area`,`suffix`) > ('FO', 'BAR')
ORDER BY `pc_area`,`suffix`

Though actually this seems to have a better explain plan

SELECT *
FROM `bbcmapimp_pc_raw`
WHERE `pc_area` >= 'FO' AND (`pc_area` > 'FO' OR `suffix` > 'BAR')
ORDER BY `pc_area`,`suffix`


Query against sys.schemas and sys.synonyms runs very slow for one user

You may want to re-write your query as follows (I'm using dbo rather than XXXX so that I do find some synonyms on my testing database). This is similar to the re-write you found to be more efficient, but avoids the need to declare a variable and use two queries.

SELECT name, base_object_name
FROM sys.synonyms
WHERE schema_id = SCHEMA_ID(N'dbo')
ORDER BY name

This yields a plan like the following:

One very interesting thing about the Filter operator in this plan is that it has a predicate that performs an internal has_access() check. This filter removes any objects that the current account does not have sufficient permissions to see. However, this check is short-circuited (i.e., completes much more quickly) if you are a member of the db_owner role, which may explain the performance differences you are seeing.

Here is the query plan for your original query. Notice that all synonyms on the database (1,126 in my case, but likely many more in your case) pass through the very expensive has_access() filter, even though only 2 synonyms match the schema. By using the simplified query above, we can ensure that has_access() is only invoked for the synonyms that match your query rather than for all synonyms in the database.

Using sys.dm_exec_query_profiles to explore further

As Martin suggests, we can confirm that the has_access() check is a significant bottleneck by using sys.dm_exec_query_profiles on SQL Server 2014+. If I run the following query using a db_owner account on a database with ~700K objects, the query takes ~500ms:

SELECT COUNT(*)
FROM sys.objects

When run with an account that is not a db_owner, this same query takes about eight minutes! Running with actual plan on and using a p_queryProgress procedure that I wrote to help parse sys.dm_exec_query_profiles output more easily, we can see that almost all of the processing time is spent on the Filter operator that is performing the has_access() check:


In standard SQL you could do that very easily with EXCEPT:

SELECT
  Column1
FROM
  Table1

EXCEPT

SELECT
  Column2
FROM
  Table2;

However, EXCEPT is unsupported in MS Access. Instead, you could use an anti-join. There are various ways of implementing an anti-join. One is using an EXISTS predicate:

SELECT
  Column1
FROM
  Table1 AS t1
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      Table2 AS t2
    WHERE
      t1.Column1 = t2.Column2
  );


Table with Clustered Columnstore Index bigger than a Clustered Rowstore Index

Unfortunately, you destroyed all the evidence that could have lead to an answer when you got rid of the CCI.

I'd suspect a lack of maintenance (rebuilds/reorgs) lead to uncompressed rowgroups, or lots of deleted rows leftover in them.

One way to test this would be to create a second copy of the table with a CCI on it and see if it's still larger. A good way to get answers about what's going on in column store indexes generally is NikoNeugebauer's CISL GitHub repo.


Does Data Migration Advisor migrate system databases?

No, because the Data Migration Assistant helps you to move from SQL Server to Azure SQL.

Azure SQL is a different product and thus the system databases are not the same. You will only be able to access the master database on your Azure SQL deployment. That being said, there really isn't anything you will be missing by not moving those over, since most functions of msdb aren't available on Azure SQL in the same way anyways.

From the documentation:

  For Azure SQL Database single databases and elastic pools, only master
  Database and tempdb Database apply.


Oracle tnsping.exe - where to get/download it

Just install the Oracle client, the tnsping.exe tool comes with it ! it should be in the bin folder.


How does pgAdmin III build CREATE statements?

pgadmin has it's own reverse-engineering functionality - it just examines the system tables like pg_class and pg_attribute to find the details.

If what you're looking for is actually an easy way to show the CREATE statements for your objects that you can use from something else, you should look at pg_dump instead of pgadmin, it is much simpler. In particular, if you just want to look at it for one or more object, you can use pg_dump -s -t tablename and it will give you the required CREATE statements.


SQL Server 2008 transaction log growing since SP3 Installed

I'd suggest the 2 are unrelated.

Has anything changed:

in the maintenance plan setting
recovery model of the database (is it now FULL where it was SIMPLE)
did you take log backups before (FULL) in the maintenance plan? Do you still do them?
did you have auto shrink in the maintenance plan? This is bad anyway
have you any open transaction that is preventing a log backup (or shrink)

Also, restore a database from pre-SP3 on another server and compare the sys.databases entries


Postgresql get rows based on minimum value

Here is the query which orders by first for warehouse_id and days then gets first record only according to DISTINCT ON clause

select distinct on (warehouse_id)
    warehouse_id,
    days
  from this_table
  where destination_id = 1
  order by warehouse_id, days

warehouse_id | days
-----------: | ---:
           1 |    2
           2 |    3
           3 |    5

db<>fiddle here


What is best way to storing geo coordinates related to planned route?

This might be an NP-Complete problem, http://en.wikipedia.org/wiki/NP-complete  Compare it to the travelling salesman problem, http://en.wikipedia.org/wiki/Travelling_salesman_problem   I'm not sure if it's NP-Complete since my college texts are in storage, and it's been a while since my complexity classes.

Not to say that we can't just do some simplistic "drift math" using SQL Server spatial data types starting with the table approach suggested by @kenwilsondba.

A more thorough, (if not NP-Complete,) approach would look for where the actual route went back on track amoung other things.

However, we could do the following utilizing SQL Server spatial data types if all we need is simplistic calculation of drift where we could just throw away extra destinations if the actual route goes over, or repeat the last actual end point if the actual route goes under, and disregard any segments where the route went back on track, and assume that the stop ids are actually sequential.

Note that this approach to calculation also penalizes actual routes that only stray in the beginning and never stray for the remainder of the route, and rewards those who stay on track until the final points.

Another caveat is that fields in the table design below are redundant.

The distance calculations are in meters by default.

CREATE TABLE a_planned_point (
    route_id            INT,
    stop_id             INT,
    lat                 DECIMAL(10,7),
    long                DECIMAL(10,7),
    pointspatialdata    GEOGRAPHY,
    city                VARCHAR(20),
    state               CHAR(2) )

CREATE TABLE b_actual_point (
    route_id            INT,
    stop_id             INT,
    lat                 DECIMAL(10,7),
    long                DECIMAL(10,7),
    pointspatialdata    GEOGRAPHY,
    city                VARCHAR(20),
    state               CHAR(2) )

CREATE TABLE c_planned_segment (
    route_id            INT,
    start_id            INT,
    stop_id             INT,
    lat_planned_stop    DECIMAL(10,7),
    long_planned_stop   DECIMAL(10,7),
    city_planned        VARCHAR(20),
    state_planned       CHAR(2),
    segmentspatialdata  GEOGRAPHY)

CREATE TABLE d_actual_segment (
    route_id            INT,
    start_id            INT,
    stop_id             INT,
    lat_actual_stop     DECIMAL(10,7),
    long_actual_stop    DECIMAL(10,7),
    city_actual         VARCHAR(20),
    state_actual        CHAR(2),
    segmentspatialdata  GEOGRAPHY)

CREATE TABLE e_drift_segment (
    route_id            INT,
    planned_stop_id     INT,
    actual_stop_id      INT,
    lat_planned_stop    DECIMAL(10,7),
    long_planned_stop   DECIMAL(10,7),
    city_planned        VARCHAR(20),
    state_planned       CHAR(2),
    lat_actual_stop     DECIMAL(10,7),
    long_actual_stop    DECIMAL(10,7),
    city_actual         VARCHAR(20),
    state_actual        CHAR(2),
    distance_drift      FLOAT,
    segmentspatialdata  GEOGRAPHY)

INSERT INTO a_planned_point (route_id, stop_id, lat, long, pointspatialdata, city, state) VALUES
    (1, 0, 33.93, -118.40, CAST('POINT(-118.40 33.93)' AS GEOGRAPHY), 'Los Angeles', 'CA'),
    (1, 1, 33.43, -112.02, CAST('POINT(-112.02 33.43)' AS GEOGRAPHY), 'Phoenix', 'AZ'),
    (1, 2, 39.75, -104.87, CAST('POINT(-104.87 39.75)' AS GEOGRAPHY), 'Denver', 'CO'),
    (1, 3, 25.82, -80.28,  CAST('POINT(-80.28 25.82)' AS GEOGRAPHY),  'Miami Intl', 'FL'),
    (1, 4, 40.77, -73.98,  CAST('POINT(-73.98 40.77)' AS GEOGRAPHY),  'New York', 'NY'),
    (1, 5, 42.37, -71.03,  CAST('POINT(-71.03 42.37)' AS GEOGRAPHY),  'Boston', 'MA')

INSERT INTO b_actual_point (route_id, stop_id, lat, long, pointspatialdata, city, state) VALUES
    (1, 0, 33.93, -118.40, CAST('POINT(-118.40 33.93)' AS GEOGRAPHY), 'Los Angeles', 'CA'),
    (1, 1, 39.75, -104.87, CAST('POINT(-104.87 39.75)' AS GEOGRAPHY), 'Denver', 'CO'),
    (1, 2, 33.43, -112.02, CAST('POINT(-112.02 33.43)' AS GEOGRAPHY), 'Phoenix', 'AZ'),
    (1, 3, 25.82, -80.28,  CAST('POINT(-80.28 25.82)' AS GEOGRAPHY),  'Miami Intl', 'FL'),
    (1, 4, 40.77, -73.98,  CAST('POINT(-73.98 40.77)' AS GEOGRAPHY),  'New York', 'NY')

INSERT INTO c_planned_segment (
    route_id,
    start_id,
    stop_id,
    lat_planned_stop,
    long_planned_stop,
    city_planned,
    state_planned,
    segmentspatialdata
    )
    SELECT
        x.route_id,
        x.stop_id,
        y.stop_id,
        y.lat,
        y.long,
        y.city,
        y.state,
        CAST('LINESTRING(' + CAST(x.long AS VARCHAR) +' '+ CAST(x.lat AS VARCHAR) +', '+
            CAST(y.long AS VARCHAR) +' '+ CAST(y.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
    FROM
        a_planned_point x
    LEFT OUTER JOIN
        a_planned_point y
    ON
        y.stop_id = x.stop_id + 1
    WHERE
        y.stop_id IS NOT NULL
        and
        x.route_id = 1
    ORDER BY x.stop_id

INSERT INTO d_actual_segment (
    route_id,
    start_id,
    stop_id,
    lat_actual_stop,
    long_actual_stop,
    city_actual,
    state_actual,
    segmentspatialdata
    )
    SELECT
        x.route_id,
        x.stop_id,
        y.stop_id,
        y.lat,
        y.long,
        y.city,
        y.state,
        CAST('LINESTRING(' + CAST(x.long AS VARCHAR) +' '+ CAST(x.lat AS VARCHAR) +', '+
            CAST(y.long AS VARCHAR) +' '+ CAST(y.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
    FROM
        b_actual_point x
    LEFT OUTER JOIN
        b_actual_point y
    ON
        y.stop_id = x.stop_id + 1
    WHERE
        y.stop_id IS NOT NULL
        and
        x.route_id = 1
    ORDER BY x.stop_id

INSERT INTO e_drift_segment (
    route_id,
    planned_stop_id,
    actual_stop_id,
    lat_planned_stop,
    long_planned_stop,
    city_planned,
    state_planned,
    lat_actual_stop,
    long_actual_stop,
    city_actual,
    state_actual,
    distance_drift,
    segmentspatialdata
    )
    SELECT
        x.route_id,
        x.stop_id,
        y.stop_id,
        x.lat,
        x.long,
        x.city,
        x.state,
        y.lat,
        y.long,
        y.city,
        y.state,
        x.pointspatialdata.STDistance(y.pointspatialdata),
        CAST('LINESTRING(' + CAST(x.long AS VARCHAR) +' '+ CAST(x.lat AS VARCHAR) +', '+
            CAST(y.long + 0.0000001 AS VARCHAR) +' '+ CAST(y.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
    FROM
        a_planned_point x
    LEFT OUTER JOIN
        b_actual_point y
    ON
        y.stop_id = x.stop_id
    WHERE
        x.stop_id IS NOT NULL
        and
        y.stop_id IS NOT NULL
        and
        x.route_id = 1
    ORDER BY x.stop_id

To find the total amount of drift in meters:

select sum(distance_drift) from e_drift_segment where route_id = 1

To show both planed, actual and drift route segments at one time in the SQL Server "Spatial Results" tab:

select segmentspatialdata from c_planned_segment where route_id = 1
union all
select segmentspatialdata from d_actual_segment where route_id = 1
union all
select segmentspatialdata from e_drift_segment where route_id = 1


How to install cstore_fdw inside RDS postgres 9.6

No. It's not supported on RDS at this point. See https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.

If you really need cstore_fdw, you'll have to manage your own PG instance.


You In  para meter is not country_idcountry it is countryID

see

PROCEDURE `setState`(IN `statename` VARCHAR(100), IN `countryID` INT(11))

So you mus use

SET @countryID := `countryID`;

To grab the country_idcountry.

Be aware when country_idcountry is a foreign key you must have the county id already in your database.


Nest SUM() Function Inside Case Statement?

You need to remove the outer case statement. It should be:

Select sum( case when ..... then .... end) from foo

You have

Select case when sum(case when.... then .... end) end from foo

The when clause expects something that will resolve to either true or false. That's what your error message is trying to tell you.

I'm still not 100% sure what your end goal is but you either need to remove the wrapping case statement like I said above or pull the case condition out of the sum.

Select
   Case
       When <condition > then sum(<column>)
        When <condition > then sum(<column >)
         Else sum(<column >)
         End
From foo


DB2: The transaction log for the database is full, But there is no 'First active log file'

You really should be reading manuals, not "pruning logs". Your database is set up for circular logging, which means there is nothing to "prune". If transactions occasionally require extra log space, increase the value of LOGSECOND. Too many primary logs will slow down the database startup, as they are preallocated. Secondary logs are allocated as needed, they will remain in the active log directory until the database is restarted.

"Pruning logs" will not affect the amount of active log space (with one exception that I will mention later): it is dictated purely by the sum of LOGPRIMARY and LOGSECOND, multiplied by LOGFILSIZ. If you were using the archive log mode, you would be able to remove older archived log files to free up some disk space, but the amount of active log space would not change. Even then, you would delete logs created prior to the previous backup, not prior to the first active log file.

Only if your archived logs and active logs share the same file system (which is not a good idea in itself), the uncontrolled accumulation of archived logs may prevent the active log space to grow, when necessary, to its configured maximum size.


The answer is to put any testing of aggregations in to a HAVING clause:

SELECT
    S_Product.Name AS "Name"
    SUM(S_Item.Quantity) AS "QtySold"
FROM s_product
INNER JOIN S_Item ON S_Product.ID = S_Item.Product_ID
GROUP BY S_Product.Name
HAVING SUM(S_Item.Quantity) > 500 ;


MySQL Clustered Index or Non Clustered index? (Database Design)

About PRIMARY KEYs:

Each table must have a PRIMARY KEY.
The PK (in MySQL's InnoDB engine) is always "clustered"
Indexes other than the PK are non-clustered.
The PK is "unique".  It uniquely identifies each row.

In your question:

One INSERT per minute is very slow.  (So, no performance concerns here, regardless of the indexes on the table, regardless of anything else.)
"Exporting" rows -- Do you mean "read" (SELECT) or "archive" (SELECT, then DELETE)??  (More details, please.)
"first 8 characters of the MYSQL DATE = 00/00/00" -- Are you not storing the DATE part?  If not, then make it a TIME column.  If you want to "extract" just the time (from a DATETIME, see the TIME() function.
"indexes ... slow down inserting and updating of data because the index needs to be rebuilt every time new data is entered."  NO, NO, NO.  Indexes are not rebuilt except on explicit requests -- which are rarely warranted.
Having an index does slow down writes slightly, but the index is likely to speed up reads significantly.  So, don't fear having indexes.
After 10 years, you will have about 5 million rows.  As tables go, this is only medium-sized.  Not a problem.  However, for accessing individual rows or groups of rows, indexes (whether PK or secondary) are strongly advised.
"I'd want to put an index on the date column to make it future proof in terms of speed." -- That does not "future-proof" it.  It will help some SELECTs, but not others.  Show us the selects so we can recommend a set of indexes that would be optimal for those selects.
Later, if you add/drop/change an index (primary or secondary), there will be some amount of effort, possibly involving copying the table over.  It is premature to worry about that.


Materialized View: What is the Update Trigger property?

That's the way materialized views work. They essentially create a copy of the results of the query in the view. The base table(s) used in that query  are automatically updated when the base table changes. That is implemented via a trigger on the base table that records all changes into a materialized view log associated to that base table.
A subsequent process then reads that log to apply the changes to the materialized view table. That application can be automatic or manual.
This: https://oracle-base.com/articles/misc/materialized-views has a nice illustration of how this works:


PaaS Server - Random row order while using aggregate function with group by

Without ORDER BY, you tell SQL Server that you don't care about the ordering.

SQL Server can do GROUP BY and aggregation internally by either sorting data or through hashing. SQL Server decides this based on what it expects to be the more efficient way. For your case when the order isn't consistent, SQL server found it more efficient to use hashing, apparently. You can see this in the execution plan.

Bottom line is, as always, if you want some specific order for the rows, then you need to use ORDER BY.


How do I design a database/query that queries users's answers to user tags

(
"usertaganswer_answeredoptions"."userTagAnswerOptionId" = '5'
or "usertaganswer_answeredoptions"."userTagAnswerOptionId" = '6'
or "usertaganswer_answeredoptions"."userTagAnswerOptionId" = 4
)

but I don't want an 'or' of all the criteria I want an 'and'. But if I do the following, it will return zero results even thought it should return 1

Zero is the correct result.
usertaganswer_answeredoptions.userTagAnswerOptionId can have the single value at the time. It cannot be at the same time equal to 4, 5 and 6. It can be (4 OR 5 OR 6) not (4 AND 5 AND 6).


Optimising latitude and longitude selection query

To find which vehicle(s) are in a specific 'rectangle' ("bounding box") during a particular day,

SELECT ...
    FROM gdata
    WHERE gDateTime >= '2017-07-03'
      AND gDateTime  < '2017-07-03' + INTERVAL 1 DAY
      AND latitude > 34.567
      AND latitude < 35.678
      AND longitude > 123.456
      AND longitude < 124.567;

The optimizer cannot do a perfect job of optimizing the query, but you can give it 3 choices and hope that it picks the best:

INDEX(gDateTime),
INDEX(latitude),
INDEX(longitude)

Since each column is used in a "range", there is no advantage in putting two columns in a single index.  (Only the first column will be used.)

Or, with SPATIAL index...

If it is a polygon, not a simple rectangle, then something like

SELECT ...
    FROM gdata
    WHERE gDateTime >= '2017-07-03'
      AND gDateTime  < '2017-07-03' + INTERVAL 1 DAY
      AND MBRWithin(pt, ...);

with

pt being an extra column containing a POINT
SPATIAL(pt)
the '...' being a pre-calculated polygon from somewhere else

Do not call your FUNCTION in the WHERE clause; that will be terribly inefficient.

Since you only want a list of vehicleID, not all the locations, this is probably better:

SELECT DISTINCT vehicleID
    FROM gdata
    WHERE ...

DISTINCT is sort of a degenerate case of GROUP BY.  I prefer to use DISTINCT in this situation since it makes it clearer what is going on.

Partitioning?

PARTITION BY RANGE(TO_DAYS(gDateTime)) with 20-50 partitions may help performance, especially if the table will be billions of rows long.  The partitioning would narrow the focus to one (maybe more) partition, then do the spatial (or lat/lng) lookup within just that partition.

Also, partitioning is an excellent way to purge 'old' data.

Use cases 1 and 2 of my partition blog discuss those two ideas.


PostgreSQL vs. Oracle High Availability solutions?

Oracle's Data Guard replication is similar to PostgreSQL's "Hot/Warm Standby Using PITR", which is built-in to the database as of PostgreSQL 9.0.  Version 9.1 adds synchronous replication too.  One advantage that PostgreSQL has over Oracle here is that Sync Rep can be controlled on a per-transaction basis.  You can have a fully synchronous "Important!" Transaction followed by an asynchronous "OK to lose" one in Postgres.

Oracle's RAC is similar to what PostgreSQL is labelling "Shared Disk Failover" in that grid.  The main difference is that RAC is fully integrated into Oracle's product, while "Shared Disk Failover" just describes a method of doing something.  You have to assemble the necessary clusterware software around that for PostgreSQL, and there are a variety of advanced things RAC does you'll be hard pressed to duplicate in PostgreSQL.  I regularly hear that most of those things are so complicated to setup that few Oracle installations get them right either--just because RAC is built-in doesn't mean it sets itself up automatically.

The main thing you can do in Oracle that is very hard to duplicate as well in PostgreSQL is multi-master replication.  It's possible to do multi-master in PostgreSQL, but only with add-on software like Bucardo.  And all such programs still have more restrictions on what you can do with them than the Multi-Master Oracle installations provide.


Set values conditionally in Postgres UPSERT

...
SET   col1 = COALESCE(excluded.col1, table.col1)
...

Of course, both tables must be visible.

If you are updating table1 and that's the only change you make it's much more efficient to use a WHERE clause:

...
SET   col1 = excluded.col1
WHERE excluded.col1 IS NOT NULL
AND   excluded.col1 IS DISTINCT FROM table1.col1 -- to avoid empty updates
...

About the added second condition:

How do I (or can I) SELECT DISTINCT on multiple columns?


At which point does SQL Server validate data for insertion

In all honesty, it depends on exactly how the query is processed, and it is certainly possible that changing the query (even though it yields the same end results) can make expressions (like attempted conversions required to fit a big string into a small column) happen before filters have eliminated those rows (and a different plan shape can occur simply because of the syntax, because stats have changed, because indexes have been added, etc. etc.).

Erland Sommarskog has always called those "illogical errors" - this UserVoice issue was migrated from a Connect item that was originally posted at least a decade ago.

In your specific case, there are multiple failsafes you can use here, assuming small_col is a varchar(10) and large_col is something that can't have more than 10 characters when col1 = 1:

INSERT dbo.target(small_col)
  SELECT CASE col1
    WHEN 1 THEN CONVERT(varchar(10), large_col) END
  FROM dbo.source
  WHERE col1 = 1;

In fact an additional failsafe, depending on the data types involved, would be to use TRY_CONVERT() instead of CONVERT(), in SQL Server 2012 and up. I don't think that applies in this specific case, since virtually any string type can be converted to varchar(10), but might be useful in other cases.

There was a similar but not identical question in the past, where I added other examples and links to external references:

CTE Error (nvarchar to numeric)

I also wrote about this in #1 here:

T-SQL Tuesday #56 : SQL Server Assumptions

Anyway, to answer your explicit question, the error is only thrown when the query is processed in such a way that an expression is attempted on the violating rows before they are filtered out. This is why you tend to hear a lot of us old school folks lecturing about always using the right data type, not overloading columns, etc.


'ERROR: could not access file "$libdir/postgis-2.5": No such file or directory'

This was "solved" by installing the 2.5 branch of PostGIS. But everything I said still stands.


Generate CREATE DATABASE FOR ATTACH for all existing user databases

This one does the job for me.  You can tweak the first CTE to include or exclude specific database and it will then generate the appropriate commands.

;WITH CTE_Databases AS
    (
    SELECT D.name AS DatabaseName
        , D.database_id
    FROM sys.databases AS D
    WHERE D.name <> 'tempdb'
        AND NOT(D.name IN ('master', 'msdb', 'model'))
    )
, CTE_FirstAndLast AS
    (
    SELECT 1 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'IF NOT EXISTS (SELECT TOP 1 1 FROM sys.databases WHERE name = ' + QUOTENAME(D.DatabaseName, '''') + ')' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 2 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'BEGIN' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 3 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , ' CREATE DATABASE ' + QUOTENAME(D.DatabaseName) + ' ON ' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 20 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , '     FOR ATTACH;' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 90 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'END' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 91 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'GO' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 92 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , '' AS CommandText
    FROM CTE_Databases AS D
    )
, CTE_Files AS
    (
    SELECT 10 AS CommandStep
        , ROW_NUMBER() OVER (PARTITION BY F.database_id ORDER BY F.file_id) AS SubStep
        , F.database_id AS DatabaseID
        , D.DatabaseName
        , CASE WHEN ROW_NUMBER() OVER (PARTITION BY F.database_id ORDER BY F.file_id) > 1 THEN '        , ' ELSE '      ' END + '(FILENAME = N' + QUOTENAME(F.physical_name, '''') + ')' AS CommandText
    FROM sys.master_files AS F
        INNER JOIN CTE_Databases AS D ON d.database_id = F.database_id
    )
, CTE_AllTogether AS
    (
    SELECT CommandStep, SubStep, DatabaseID, DatabaseName, CommandText
    FROM CTE_FirstAndLast
    UNION ALL
    SELECT CommandStep, SubStep, DatabaseID, DatabaseName, CommandText
    FROM CTE_Files
    )
SELECT * FROM CTE_AllTogether
ORDER BY DatabaseID, DatabaseName, CommandStep, SubStep;


How to Query Properties of an Entity in Apache Cassandra

In Cassandra, as far as I know, for SELECT filtered by non-key attributes, you only have three options:

(1) Application side filtering.  That is, if you get your results from a CQL SELECT, use your application to filter the results.  For all but the smallest data sets, this is ill-advised.

(2) Bite the bullet, and create those secondary indices.

(3) Probably the most common option, duplicate your data by having rows composed of keys.  That is, for whatever filter condition you want to apply, create a new entry in your database where you store the keys of all the relevant row entries which match the filter.

Note that while the third option is most common, you will almost eventually develop some data inconsistency due to the inherent de-normalization. Apache Cassandra is not a cure all, it simply handles some applications very well.

Good luck!

P.S.:  Here's a couple decent blog entries which can explain a bit of the theory at the logical data model level.  Part 1 & Part 2


greatest-n-per-group problems are usually solved using window functions.

The following is standard SQL and will work on SQL Server as well:

select name, major, minor, attr
from (
  select name, major, minor, attr,
         row_number() over (partition by name order by major desc, minor desc) as rn
  from t_version
) t
where rn = 1
order by name;

For Postgres it's typically faster to use distinct on () instead:

select distinct on (name) name, major, minor, attr
from t_version
order by name, major desc, minor desc;


What is the difference between COUNT(*) and COUNT(*) OVER()

COUNT(*) Returns all rows ignoring nulls right?

I'm not sure what you mean by "ignoring nulls" here. It returns the number of rows irrespective of any NULLs

SELECT COUNT(*)
FROM (VALUES (CAST(NULL AS INT)),
             (CAST(NULL AS INT))) V(C)

Returns 2.

Altering the above query to COUNT(C) would return 0 as when using COUNT with an expression other than * only NOT NULL values of that expression are counted.

Suppose the table in your question has the following source data

+---------+---------------+
|  Name   | MaritalStatus |
+---------+---------------+
| Albert  | Single        |
| Bob     | Single        |
| Charles | Single        |
| David   | Single        |
| Edward  | Married       |
| Fred    | Married       |
| George  | NULL          |
+---------+---------------+

The query

SELECT MaritalStatus,
       COUNT(*) AS CountResult
FROM   T
GROUP  BY MaritalStatus

Returns

+---------------+-------------+
| MaritalStatus | CountResult |
+---------------+-------------+
| Single        |           4 |
| Married       |           2 |
| NULL          |           1 |
+---------------+-------------+

Hopefully it is obvious how that result relates to the original data.

  What does COUNT(*) OVER() do?

Adding that into the SELECT list for the previous query produces

+---------------+-------------+-----------------+
| MaritalStatus | CountResult | CountOverResult |
+---------------+-------------+-----------------+
| Single        |           4 |               3 |
| Married       |           2 |               3 |
| NULL          |           1 |               3 |
+---------------+-------------+-----------------+

Notice that the result set has 3 rows and CountOverResult is 3. This is not a coincidence.

The reason for this is because it logically operates on the result set after the GROUP BY.

COUNT(*) OVER () is a windowed aggregate. The absence of any PARTITION BY or ORDER BY clause means that the window it operates on is the whole result set.

In the case of the query in your question the value of CountOverResult is the same as the number of distinct MaritalStatus values that exist in the base table because there is one row for each of these in the grouped result.


Pros and cons in combining current data and historical data in the same table

I have old posts where I have discussed something like this before

Nov 30, 2012 :  How much does table size affect performance?
Oct 07, 2011 : How Does Table Partitioning Help?

The pros can never outweigh the cons. The reason ? Should you attempt to scale hardware and cache settings, you make room for both current and historical data to be cached. This introduces the possibility of the following:

Caching old data
Not using the old data that was recently cached
Having to prune that old data from caches to make room for current and historical data
Start these three problems over again

Even if you tune queries, caches could still suffer. How?
MyISAM
Recall that there are session-level caches for reading ahead for data. This is set by read_buffer_size and read_rnd_buffer_size. This affects only MyISAM tables since MyISAM only caches indexes system-wide. Each thread caches its own data.
InnoDB
Retrieving pages of data you may not need is also possible if your queries do full table scans or index range scans. Since each page for InnoDB is 16K, a page could end up with both current and historical data if you are not paying attention and just loading both willy-nilly.
SUGGESTIONS
Keep current data in one table and historical in another. It will be faster to retrieve current data. You can also scale caches for current data only.
If you really want old and new data in one table, do the following:

Create an empty database
Load all historical data first, sorted by datetime
Load all current data next, sorted by datetime

As an added bonus, you should consider partitioning tables by month. This will spread out table data and corresponding indexes. This makes it easier to defragment or delete by partition.


restricting user just to get aggregated information

Plan A:  Use GRANT to not let that user see salary.

Plan B:  Provide Summary Tables (with counts, totals, etc) of stuff that you are willing to let them see, then restrict (via GRANT) their privileges on a table basis.  (Or put the Summary Tables in a different database.)


How can I get satisfactory random numbers from the RAND function (or elsewhere)?

The only reason to ever provide a "seed" for RAND() is if you want the exact same sequence of (pseudo) random values for testing purposes.

Just call it without a seed, and that should be good enough for most purposes.

If you do need a predictable sequence, and you're simply unhappy with the starting (random) value, RAND() takes seeds from -2,147,483,648 all the way up to 2,147,483,647 (full range of int), so try some seeds that differ by more than 1 or 2.

Finally, if you were on SQL 2008 or better, you'd also have the option of considering CRYPT_GEN_RANDOM instead (warning, it returns a varbinary, not a float, so you'd likely need to change your code a bit): see this article for some differences and discussion.


No, please do not detach your database and move the log file. Follow the advice from Kin, spaghettidba, and others, but take a full backup first (you'll need to adjust db to your database name, and db_log and db.ldf to the log file logical and physical names, according to sp_helpfile):

BACKUP DATABASE db TO DISK = 'backup location' WITH INIT;

-- set it to single user
ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- set the recovery mode to simple
ALTER DATABASE db SET RECOVERY SIMPLE;

-- run checkpoint twice
CHECKPOINT;
CHECKPOINT;

-- shrink the log file to something more reasonable (please not 1 MB):
DBCC SHRINKFILE(db_log, 1024); -- 1 GB

-- take the database offline
ALTER DATABASE db SET OFFLINE;

-- tell the system catalogs that you're moving the file
ALTER DATABASE db MODIFY FILE
  (NAME = db_log, FILENAME = 'x:\other_drive\db.ldf');

Stop here. This should tell you:

  The file "db_log" has been modified in the system catalog. The new path will be used the next time the database is started.

So, move the LDF file (which should now be about 1 GB) to x:\other_drive\ and then bring the database back online and multi-user:

ALTER DATABASE db SET ONLINE;
ALTER DATABASE db SET MULTI_USER;

(To be honest, I'd feel safer restoring the backup WITH MOVE; any time you start moving files around, you're opening yourself up to loss/corruption. But as long as you took your full backup right before you started this process, that should protect you.)

Now, leave the thing in simple recovery if you don't care about transaction logs and don't care to learn why they're important. But I really, really, really strongly recommend you read this post in full:

Why Does the Transaction Log Keep Growing or Run Out of Space?

Try to understand your employer's tolerance for data loss, because I can assure you that most businesses cannot afford to be laissez-faire about this, and 24 hours of loss is absolutely not acceptable. If this is true in your workplace, then set the database back to full recovery, and immediately take a full+log backup to start the log backup chain:

ALTER DATABASE db SET RECOVERY FULL;

BACKUP DATABASE db TO DISK = N'backup_location\db_full.bak';
GO
BACKUP LOG db TO DISK = N'backup_location\db_init.trn';

Then create some kind of job that will take regular log backups throughout the day. Every hour might not be enough; again, make sure the owners of the data understand the trade-offs involved with not backing up the log often enough. I doubt that even an hour of data loss will be acceptable to them. And no, the .trn files will rarely be as large as the LDF itself; this will only happen in extreme scenarios where the log file blew up because of activity that happened since the last log backup.

Also, don't let SQL Server anywhere near compressed drives. Disk space is cheap; have them invest in enough space to adequately store your data and copies related to fault tolerance.


Postgresql table function: get where condition

The temporary table will only make things slower, you don't need it. And if you change the function to be a SQL function, the Postgres optimizer can push the condition from the WHERE clause automatically into the query inside the function:

create or replace function public.fn_world_cities()
  returns table (city_id int, city_name varchar, country_id int)
as $$
  select city_id, city_name, country_id
  from public.table_world_cities
  where row_status = 'A';
$$
language sql
STABLE; --<< this is important!

If you now run:

select *
from public.fn_world_cities()
where country_id = 10;

And country_id has an index that is worth using, Postgres will use it Note that you need a non-trivial number of rows in that table, and the condition needs to be selective enough to warrant an index scan (as with any other index usage)


Updating and/or inserting several rows in the same SQL statement

Having been pointed in the right direction by @Phil, it looks like the following merge will work:

MERGE userabilitytest
USING (VALUES
        (1, 'EDITACCOUNT'),
        (1, 'EDITORDER'),
        (1, 'BOOHOO'),
        (1, 'FooBar')
        )
      AS foo(account_id, ability)
ON
  userabilitytest.account_id = foo.account_id and
  userabilitytest.ability = foo.ability and
  userabilitytest.active = 'Y'
WHEN MATCHED THEN
  UPDATE SET ability = foo.ability
WHEN NOT MATCHED THEN
  INSERT (account_id, ability)
  VALUES (account_id, ability);

Being able to use a "values" list and ascribing it to "foo" makes all the difference - a table with the new values in is not actually necessary.

Also, some sort of statement where UPDATE is is needed, but the one that is there at the moment does nothing as ability is matched in the ON part. Commenting the UPDATE statement out causes an error.


Drawing an entity-relationship diagram for a store business context

First of all: (A Cashier can also be a Customer)
In this case, you can use a 1-to-1 relationship named "CAN ALSO BE" between Cashier Entity and Customer Entity. And also transmit Customer P.K as F.K to Cashier. This F.K (Foreign key of Customer in Cashier) can be null too and should be UNIQUE.

Edit: (based on @RDFozz notice)
We can use the similar User_ID in both Cashier and Customer to show that a Cashier can also be a Customer.

Secondly: (Only a Cashier can add a Customer to the Store's database)
Meaning that each Customer has a creator and should be a Cashier. So you have a one-to-many relationship named "CREATOR" between Cashier and Customer. So, you should transmit Cashier P.K as F.K to Customer. This F.K (Foreign key of Cashier in Customer that shows creator id) should not be null.

Thirdly: (Only a Cashier can add a Customer to the Store's database)
We can not put ALL Business Logic of our project into ERD. For this reason we creates Business Layer or Business Logic Layer (BLL) or sometimes we write some Stored Procedures or Functions with Enhanced SQLs (those have IF, Loops and etc.)

If we put P.K of Cashier as F.K into Customer and prevent it to be null, that's enough. Your Insert into Customer should has a Cashier ID to be done.