Displaying an Estimated Execution Plan generates CXPACKET, PAGELATCH_SH, and LATCH_EX [ACCESS_METHODS_DATASET_PARENT] waits

It appears your request for an actual execution plan triggered stats updates.  Since you mention this happens in the mornings, I imagine there's an overnight process that does a lot of modifications to the tables involved?

Thus SQL Server uses the stats to create the plan, has hit the modification threshold, and executes automatic stats updates as part of the operation.

In the XML for the estimated plan you shared, I see these close-together update dates for stats from this morning:

LastUpdate="2019-05-06T09:12:49.92"
LastUpdate="2019-05-06T09:12:58.3"
LastUpdate="2019-05-06T09:13:20.33"
LastUpdate="2019-05-06T09:13:09.67"
LastUpdate="2019-05-06T09:12:59.05"
LastUpdate="2019-05-06T09:12:39.56"

If these are very large, busy tables (seems likely based on the sampling percentages), then it's not too surprising that the stats updates are taking a lot of horsepower.


This article by Conor Cunningham (Software Architect, SQL Server Engine at Microsoft) should answer your questions:

No Seatbelt – Expecting Order without ORDER BY

Some extracts below:

The hard part here is that there is no reasonable way for any external user to know when a plan will change .  The space of all plans is huge and hurts your head to ponder.  SQL Server's optimizer will change plans, even for simple queries, if enough of the parameters change.  You may get lucky and not have a plan change, or you can just not think about this problem and add an ORDER BY.
[...]
There are lots of situations where plans can change in the Optimizer - for more complex queries, there can be thousands of plan choices or more, and each of them has a case when it would likely be picked.  For each of those plans, the sort for that plan can be different if you don't specify it.
So, my advice for the day:
If you need order in your query results, put in an ORDER BY.  It's that simple.  Anything else is like riding in a car without a seatbelt.


Refreshing a table using CREATE TABLE, DROP and Rename

I have to update every minute. One person suggested me to rename the original table as well, I will try, it will probably be quicker, then I drop it after the commit.


Is Oracle DB licensing needed for a test environment?

In general, yes.  Once you have an application in production, every environment (dev, test, staging, production, etc.) needed to support that application needs to be licensed.  As with all licensing advice, however, you need to get it in writing from Oracle Sales.  Advice from some guy on the internet holds up about as well as you might expect if there is ever a question about whether you're properly licensed and/or if you're overpaying for licenses.  And remember that agreements your company has with Oracle, laws in your country, etc. can change this general statement.

If you are a reasonably large customer or if you have some other leverage in the negotiation process, however, you can generally get the licenses for the test and development environments at a good discount from Oracle Sales.


Can memcached improve performance for InnoDB in mem with a large innodb_buffer_pool_size?

The InnoDB Buffer Pool is the home of three classes of informational structures

Table Data Pages that have been accessed
Table Index Pages that have been accessed
Insert Buffer Changes to Update Secondary Indexes in ibdata1 (System Tablespace)
See Pictorial Representation of the InnoDB Buffer Pool and ibdata1

If you make the InnoDB Buffer Pool large enough, then all data you have accessed will essentially stay in RAM. Any data you have never read but finally decided to read will then have data and indexes cached in the InnoDB Buffer Pool. Please read my post : What are the main differences between InnoDB and MyISAM? on how to determine the size of a good InnoDB Buffer Pool.

Given a large enough InnoDB Buffer Pool, I would rule out using memcached.


There are two tools that jump to mind, with the caveat being I haven't tried either, just have read about them. Percona's* XtraDB Cluster (based on Galera) and Percona Replication Manager (PRM).

PRM is based on a single-master with multiple slave nodes. It uses pacemaker to handle the failover. I can't answer how it handles your last two bullet points, but as to a flapping state, read about GitHub's experience. That experience and subsequent posting caused quite a commotion, questioning the validity of automatic failover. Here is one such post that links to some of the others.

The basic gist is, be careful with automatic failover. You might save yourself a lot of time restoring service by monitoring the state of the master and getting notification to do a manual failover.

I don't know much about XtraDB Cluster, but from the documentation, below is a list of features. However, you require MyISAM support, which XtraDB Cluster/Galera does not handle.

  Synchronous replication. Transaction either commited on all nodes or none.
  Multi-master replication. You can write to any node.
  Parallel applying events on slave. Real “parallel replication”.
  Automatic node provisioning.
  Data consistency. No more unsynchronized slaves.

disclaimer: I linked a lot of Percona stuff, but I am not affiliated with them in any capacity.


Limit Access To mysql information_schema

When you grant permission, you specify the database and table names that you're allowing access to. Don't give unprivileged users modification privileges to *.*, list the databases explicitly in the grants.


When named instances are having issues being connected to, there is generally an issue with one of the following:

SQL Browser isn't running
SQL Browser ports are blocked (1434 UDP)
Incorrect DNS entries
SQL TCP Port is blocked (custom port number for named instance)
SQL TCP Port is blocked (Dynamic ports)
TCP is not an enabled protocol

To workaround it, either start the SQL Browser service or unblock the port(s).

Additionally, with the SQL Server Management Studio connection dialog it is possible to press the "options" button in the lower right hand corner and override any of the preset options. In this instance, using SERVER=MyServer,Port to specify the server name and port number overrides the SQL Browser port lookup and allows for a direct connection without name to port resolution.


AWS RDS PostgreSQL dump/restore - syntax error

A pg_dump output with the -Fc (custom) is not a SQL script, it's a compressed archive that only pg_restore is able to handle.

You may either pass the dump file to pg_restore and pipe its output to psql (often done that way and close to what you tried):

pg_restore /home/jboss/proc_dump_jan15.dump | psql  --host=blahrds [other options]...

...or use the -d,-h,... and other options of pg_restore to have it connect to the database and skip the psql invocation.

See pg_restore manpage for 9.3 for details.

  Nb. the EC2 database is PG 9.0 and the RDS database is PG 9.3.5 but I
  assume this is ok as the dump/restore will deal with the migration?

The recommended method is to use pg_dump 9.3 against the 9.0 database to create the dump. The reasoning is that newer versions always know how to extract older databases such that they will import flawlessly into their version. The opposite is not true, since obviously at the time when pg_dump 9.0 is released, the authors don't know what compatibility problems future versions might bring.


Removing duplicate entries for each hour

Generic case

Say you want to retain the values belonging the max(id) for every hour and for every distinct row3 value (but see the UUID section below).  The IDs of these you get with a query like

SELECT max(id)
FROM table1
GROUP BY row3, EXTRACT(hour FROM "time")
;

(This will omit the date from your data, which is what you may want—or not.  In the latter case you may want to use date_trunc('hour', "time") instead.)

Then you can use the above query to populate a table just as you did in your question, or delete everything else from table1 like

DELETE FROM table1
WHERE id NOT IN (
    [the query above comes here]
)
;

UUID

There is no max() for UUID.  Therefore another solution is needed in your case, like this one:

SELECT DISTINCT first_value(id) OVER w
FROM table1
WINDOW w AS (PARTITION BY row3, EXTRACT(hour FROM "time")
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
;

This one applies a window function, and the grouping is achieved by the PARTITION BY clause and DISTINCT.

(Acknowledgment: thanks for the idea for AndriyM!)


PostgreSQL: Remove password requirement for user postgres

Whether a password is required or not has nothing to do with pg_shadow and whether a password is actually defined for the user. Yes, I know, that's weird.

pg_hba.conf controls the authentication method. If you want to request a password, use md5 authentication. If you want to allow login with no password to anyone, use trust. If you want to require the same username in the operating system as in PostgreSQL, use peer (UNIX, only for local connections) or sspi (Windows).

If there's a password set, but pg_hba.conf doesn't tell PostgreSQL to ask for it, the password is ignored.

If pg_hba.conf tells PostgreSQL to ask for a password but there's none set, then all login attempts will fail no matter what password is supplied.


No records in Mysql.func table even though there are stored functions in server

All stored procedures and stored functions, whose code is written in the MySQL Stored Procedure Language, reside in mysql.proc.

You can see how many of each with this

SELECT COUNT(1) rowcount,type
FROM mysql.proc GROUP BY type;

The only time I have ever seen mysql.func is in context with AGGREGATE functions:

According to MySQL Documentation on CREATE FUNCTION Syntax for User-Defined Functions

  An AGGREGATE function works exactly like a native MySQL aggregate (summary) function such as SUM or COUNT(). For AGGREGATE to work, your mysql.func table must contain a type column. If your mysql.func table does not have this column, you should run the mysql_upgrade program to create it (see Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”).

My guess is that mysql.func stores AGGREGATE functions and not regular functions. Evidently, you would create some UDF in C/C++ and register it in mysql.func using the CREATE AGGREGATE FUNCTION syntax.


Order by starting from "the middle" in a windowing function

I wouldn't worry about hardcoding the number of months in a year
create table #t (mm int)

insert #t (mm) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

select mm, (mm + 8) % 12 from #t order by 2 asc


Dba.getCluster: This function is not available through a session to a standalone instance (RuntimeError)

It seems the node from the one you are trying to use the command is not part of the Cluster. What you can do is to try to get the status from another node.

If this is not possible, then try to execute:

  mysql-js> var cluster = dba.rebootClusterFromCompleteOutage();

Anyway, check the follow article: https://mysqlserverteam.com/innodb-cluster-in-opc-part2/


Up to you. Just be consistent though.

Personally I prefer singular based on what each *row" stores: Order, Product, User, Item, etc.

This matches my modelling (via Object Role Modelling) where I use singular entities/types.

Edit:

One reason is that plural fails when you have link tables:
Orders, Products would give OrderProducts or OrdersProducts. Neither sounds correct

Or history tables (of course you can use schemas for this):
Orders -> OrdersHistory or (no!) OrdersHistories? Wouldn't Order-> OrderHistory be better?


This query works but only loads 7 lines when one table in database holds over 100,000 records

The thing with sys.fn_dblog is that it only shows you the active portion of the log. If you wanted every transaction that ever happened through means such as this, I might suggest something more useful of your time.

  This query worked without error but only recovered 37 lines. If I restore the backup there are more than 100,000 records in one table. I would expect to see hundreds or thousands of lines when I run these queries.

A single log backup doesn't hold everything that ever happened, you'll need every log backup ever taken. If your database was ever in the simple recovery model, or if it was in the full recovery model and anything was accomplished in it before a full backup was taken then you're not going to get everything back. Period.

Just because there are 100k records in the table doesn't mean there were 100k transactions, either. It could have been one big transaction, it could have been 2 transactions that inserted 200k and then a 3rd transaction that removed 100k, etc. ad nauseam.

  The only logical answer would be that the transactions in the file are limited.

Yes in terms of what you can read at any given point as the log file can be thought of as a giant ring buffer that writes over the older inactive portions. It's circular and will continue re-using inactive portions unless it fills up and must grow because all of the current space is active.

  Is there any command I can run to check the number of transactions in the log file?

You already have them, you just need to do this for every log backup ever taken across all of the time the database has been in existence.

Also, this doesn't take into account actually reading the log structure which isn't publically documented. This doesn't even include the newer log structures for things like Hekaton which have their own structure inside of the log structures or any of the trace flags you'd need to set certain behaviors.

All-in-all you're better off doing something else with your time. If this is needed as part of database recovery then I'd employ some professionals. If this is purely for academic then I would suggest creating a very small database, putting it in the full recovery model, taking a full backup, and not to take a log backup until you're done looking at that part of the log.


SQL server disk write queue dramatically grows sometimes

Sounds like you have:

A very crappy IO subsystem.
A checkpoint occurring.
Crappy IO subsystem + checkpoint = high disk queue.

  I found that writing to log (not to data file) is default behavior of
  Full recovery mode. And log can only be copied to data file by Backup
  Transaction Log operation.

No, not really. Your information or understanding is at fault.

Regardless of recovery model, SQL Server uses write-ahead-logging (WAL). An update/delete/insert results in a change to the in-memory data page + a record of the change written to the transaction log. The data page modification is not immediately flushed to the data file. To all intents and purposes you can consider the log write to disk to be immediate (beneath the covers the log is flushed every 60K or on a COMMIT).

What you are seeing when the IO spikes is those data page modifications being flushed to disk as a result of a checkpoint operation.

  A checkpoint writes the current in-memory modified pages (known as
  dirty pages) and transaction log information from memory to disk and,
  also, records information about the transaction log. [Source]

Relevant past questions that may help you understand the mechanics:

SQL Server Cache Flush and Disk I/O
When do you remove entries from a write-ahead log (WAL)?


The following example is based on a jerry-rigged error trapping method in triggers from Chapter 11, Pages 254-256 of the book MySQL Stored Procedure Programming under the subheading 'Validating Data with Triggers':

DELIMITER $$

CREATE TRIGGER reservation_bi BEFORE INSERT ON reservation FOR EACH ROW
BEGIN

DECLARE found_count,dummy INT;

SELECT COUNT(1) INTO found_count FROM flights WHERE numflight=new.numflight;
IF found_count = 0 THEN
    SELECT 'Cannot Insert This Reservation Because Flight Number is Invalid'
    INTO dummy FROM reservation WHERE numflight=new.numflight;
END IF;

END; $$

DELIMITER ;

If there is other validation to be done, follow this same error trapping method!!!

UPDATE

The reason the book suggests preempting the Trigger in this manner stems from the fact that the MySQL Stored Procedure Language did not have SIGNAL implemented into the language (of course, SIGNAL is ANSI standard).

The authors of the book created work arounds by calling on SQL statements that are syntactically correct but fail at runtime. Pages 144-145 (Chapter 6: Error Handling) of the book gives these examples on preempting a Stored Procedure directly (Example 6-18) or by SIGNAL emulation (Examples 6-19 and 6-20).

# Example 6-18. Using a nonexistent column name to force an error to the calling program

    CREATE PROCEDURE sp_update_employee_dob2
        (p_employee_id INT, p_dob DATE)
    BEGIN
        IF datediff(curdate(),p_dob)<(16*365) THEN
            UPDATE `Error: employee is too young; Employee must be 16 years or older`
                SET x=1;
        ELSE
            UPDATE employees
               SET date_of_birth=dob
            WHERE employee_id=p_dob;
       END IF;
    END;

# Example 6-19. Standard procedure to emulate SIGNAL

    CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255))
    BEGIN
        SET @sql=CONCAT('UPDATE `',in_errortext,'` SET x=1');
        PREPARE my_signal_stmt FROM @sql;
        EXECUTE my_signal_stmt;
        DEALLOCATE PREPARE my_signal_stmt;
    END;

# Example 6-20. Using our SIGNAL emulation procedure to raise an error

    CREATE PROCEDURE sp_update_employee_dob2
        (p_employee_id INT, p_dob DATE)
    BEGIN
        IF datediff(curdate(),p_dob)<(16*365) THEN
            CALL my_signal('Error: employee is too young; Employee must be 16 years or older')
        ELSE
            UPDATE employees
               SET date_of_birth=dob
            WHERE employee_id=p_dob;
       END IF;
    END;

Unfortunately, the SIGNAL emulation from Examples 6-19 and 6-20 cannot be done in Triggers. The book's authors used the technique in Example 6-18 to preempt triggers on pages 254-256. This is why the book's suggestion has to be the way to go.


Usage of RESTRICT or No ACTION in FOREIGN KEY Constraints

In MySQL, RESTRICT and NO ACTION are synonyms:

In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT. [src]

Now, you are asking how this affects a DELETE FROM column1 WHERE first_id='XX' if the table is defined like so:
CREATE TABLE `column2` (
 `second_id` int(11) NOT NULL AUTO_INCREMENT,
 `first_id` int(11) NOT NULL,
 PRIMARY KEY (`second_id`),
 KEY `fk_first_id` (`first_id`),
 CONSTRAINT `fk_first_id` FOREIGN KEY (`first_id`) REFERENCES `column1` (`first_id`)
) ENGINE=InnoDB;

Notice there is no ON DELETE CASCADE or ON UPDATE CASCADE clause. The manual specifically states that by leaving this off, you are implying a RESTRICT:

Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

So, now when the table is defined like this, a DELETE FROM column1 WHERE first_id='3' will fail if there are any rows in column2 that have a first_id value of 3.
If you had a child table that you needed to do some processing on prior to deleting the values, you might use this to ensure that the parent cannot be deleted until you have cleaned up the child table appropriately. It still offers protection against orphan rows in the child table (orphans are rows that exist in the child table, with no matching reference in the parent table).


How Does SQL Server Choose Indexes to Use

In my limited experimentation:

When the query has to perform a scan, SQL Server does the work to find the narrowest index every time.
When the query is expected to perform a seek, it doesn't care about index width, it just uses the last covering index created.

This kind of makes sense. If you're going to add rules to search for the perfect index in either of these cases, you should do it when it's going to save you the most. On a seek the effort spent in finding a better index when it won't really matter in terms of savings for all rows as opposed to per row is far less likely to be worth it. "Last in" is maybe intentional, with the assumption that the last covering index you created is probably the best one you've created, or it may just be arbitrary and coincidental (like the order of columns in a missing index recommendation).

Proof (well, sort of proof)

My test setup was pretty simple:

CREATE TABLE dbo.t1
(
  id int IDENTITY(1,1) PRIMARY KEY,
  sn1 sysname, tn1 sysname, cn1 sysname, typ1 sysname,
  sn2 sysname, tn2 sysname, cn2 sysname, typ2 sysname,
  sn3 sysname, tn3 sysname, cn3 sysname, typ3 sysname,
  sn4 sysname, tn4 sysname, cn4 sysname, typ4 sysname
);
GO
SET NOCOUNT ON;
GO
INSERT dbo.t1
(
  sn1,tn1,cn1,typ1,sn2,tn2,cn2,typ2,
  sn3,tn3,cn3,typ3,sn4,tn4,cn4,typ4
)
SELECT s.name, t.name, c.name, typ.name,
       s.name, t.name, c.name, typ.name,
       s.name, t.name, c.name, typ.name,
       s.name, t.name, c.name, typ.name
FROM sys.schemas AS s
CROSS JOIN sys.objects AS t
INNER JOIN sys.all_columns AS c
ON t.[object_id] = c.[object_id]
INNER JOIN sys.types AS typ
ON c.user_type_id = typ.user_type_id;
GO

CREATE VIEW dbo.v1
AS
  SELECT id,sn1,tn1
  FROM dbo.t1;
GO

This put 13,260 rows into my table (your results will vary). Then I repeatedly created the same three indexes, in different order:

-- widest first
CREATE INDEX ix_wide ON dbo.t1(sn1)
  INCLUDE(tn1,cn1,typ1,sn2,tn2,cn2,typ2,sn3,tn3,cn3,typ3,sn4,tn4,cn4,typ4);
GO
CREATE INDEX ix_mid ON dbo.t1(sn1) INCLUDE(tn1,cn1,sn2,tn2,cn2,typ2,sn3,tn3);
GO
CREATE INDEX ix_small ON dbo.t1(sn1) INCLUDE(tn1,cn1);
GO

-- widest last = ix_small then ix_mid then ix_wide
-- middle1 = ix_mid then ix_wide then ix_small
-- middle2 = ix_small then ix_wide then ix_mid

Then in each of those four cases I ran these two queries and investigated the plans:

DBCC FREEPROCCACHE;
GO
SELECT id,sn1,tn1 FROM dbo.v1; -- scan
GO
SELECT id,sn1,tn1 FROM dbo.v1 WHERE sn1 LIKE N'q%'; -- seek

Results:

         widest first  widest last  middle1   middle2
-------  ------------  -----------  --------  --------
   scan    ix_small      ix_small   ix_small  ix_small
   seek    ix_small      ix_wide    ix_small  ix_mid

The scan always chose the narrowest index. The seek always chose the index that was created last (since all cover). I didn't extend the tests to also include a non-covering index in the mix, because I don't suspect it will change this outcome.

Morals (well, sort of morals)

There are two morals here:

Create your widest indexes first if you want your narrowest indexes to be used in seeks when they can.
It's probably a narrow set of use cases where this optimization is worth it. Such as seeks that return a lot of rows and the wider covering index has very large columns that aren't in the narrower index. In these cases it might be worth explicitly specifying the index with a hint (and all the caveats that involves) rather than relying on the behavior I've observed here.

Probably some internals and heuristics I'm missing here that Paul will clarify. Also I tried to enable trace flag 302 to output index selection information, but this doesn't seem to work on modern versions of SQL Server.


Auto Increment a column that is not unique...?

That seems possible in MyISAM but afaik not using InnoDB:

CREATE TABLE `invoice_sequences` (
  `company_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`company_id`,`invoice_id`)
) ENGINE=MyISAM;

http://sqlfiddle.com/#!9/d135c/1 (if the fiddle returns an error, try again; it seems a bit slow sometimes).


SQL Database Mirroring Number Restriction

Per the official Microsoft documentation, there isn't a technical limitation on the number of databases you can mirror. The more you have, the more you will have to manage, obviously.

As noted in that link, mirroring is deprecated and will be removed from SQL Server at some point in the future. Microsoft themselves recommend against implementing a new solution using mirroring as a result. You should consider alternatives such as log shipping, which may give you the protection you need (depending upon your SLAs, RTO, RPO, etc.).


Changing Schema ownership and side effects

In SQL Server, changing the schema owner will drop all permissions. This is a "feature" buried deep in the documentation:

  If the target entity is not a database and the entity is being
  transferred to a new owner, all permissions on the target will be
  dropped.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql

For a schema, this apparently applies to the permissions on all objects within it.


overhead for INSERTS using change tracking and/or change data capture (CDC)

Yes it makes sense, CDC is maintained via a Log Reader, which other than the overhead of the reads on the log drive, and the writes to the capture tables, should not affect the insert rate. They're thinking about (old school :)) tracking via triggers, which can easily impact the insert rate.

But to be safe, make sure your tests run long enough for the overhead of the reader to be actively capturing the inserts.


Run PostgreSQL without admin / superuser

This answer is about PostgreSQL.

You cannot drop a superuser unless you are a superuser yourself.
Nobody can drop the bootstrap superuser (normally postgres) because he owns the system objects.

Resetting the password won't prevent a user from logging in.

Keep the superuser around and don't allow it to connect.

For that, you could add the following lines at the beginning of pg_hba.conf:

host  all   postgres  0.0.0.0/0  reject
host  all   postgres       ../0  reject
# if you are truly paranoid and want to forbid local connections
local all   postgres             reject

Don't forget to reload PostgreSQL after that.


Is partial synchronisation possible between two databases on different servers?

The only built-in data synchronization technology that meets your requirements, I think, is Transactional Replication.
See the FAQ on replication:

How do I add or change indexes on publication and subscription databases?
Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

This means you can have different objects (indexes, views, etc) on each replica.


Azure SQL - dataspace used vs max dataspace

First of all, no need to worry as this is normal behaviour.

The link in your answer From msdocs on Data space allocated gives us the info needed:

  The amount of space allocated grows automatically, but never decreases
  after deletes. This behavior ensures that future inserts are faster
  since space does not need to be reformatted.

This space allocated grows automatically and will stay the same way when it grows out.

If the database keeps on growing, eventually the allocated space will be the same size as the maximum storage size. This is comparable to the autogrowth behaviour of regular sql server databases.

A useful part of the azure portal is that there are these little (i) marks on many settings.

These should point you in the right direction, the learn more points us to above linked page.

In short

  when used space is nearing the allocated space, some more space is
  allocated

yes (as long as you are not at the database limit)

  when used space is at max size we have no more space available thus we
  run out of space

and yes

Another way to get the size is checking the space available in the properties of the azure sql db with ssms.

This could mean that even sql server itself does not know that a mechanism like allocated space is used.

Proof

growing out the previously mentioned database a bit:

increases the used space & allocated space:

Afterwards I deleted the data & shrinked the database (Which you shouldn't ;)), also not with dbcc shrinkdatabase, double foul there.

dbcc shrinkdatabase([databasename])

Results in the same allocated space, but less used space:


Declaratively select an entity requiring relations from all (dynamic number of) related entities

I suggest loosening your resolve for a fully declarative solution. There may not be a declarative way to solve your problem, and there are many cases where dynamic SQL is a much better solution in terms of performance - even if it lacks readability.

Dynamic SQL gives the optimizer the opportunity to use different plans optimized for different parameters instead of having to come up with a single plan that solves all permutations well. The latter is quite difficult to achieve.

Your problem is called relational-division. See questions in that tag and:

Divided We Stand: The SQL of Relational Division by Joe Celko; and
How to filter SQL results in a has-many-through relation on Stack Overflow.

Most of the Stack Overflow solutions would work as they are, or with minor modifications for SQL Server. Note how the more complicated queries, with multiple joins or EXISTS subqueries (that would need dynamically produced code for the arbitrary cases) are more efficient than the GROUP BY query.


Operand type clash: my first User Defined Type with list of Integers, how to test via SSMS

As you have declared dbo.StringList to be a User-Defined Table Type, you have to utilise it as you would a table.

So instead of assigning '1,2,3,4,5' as a string, you must INSERT this value into the table.

There's a pretty decent example in BOL, but I'll create one based on your example above.

CREATE TYPE [dbo].[StringList] AS TABLE(
  [Item] [NVARCHAR](MAX) NULL
);

GO
CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.Item FROM @list l;
END

GO

DECLARE @StringList StringList

INSERT Into @StringList values('1,2,3,4,5')

EXEC sp_UseStringList @StringList


Not able to Create Database or Restore Database on the Azure SQL Managed Instance

Please make sure you created the storage account as shown below:

You need to use Classic and Standard.


Oracle 11g DBMS_CRYPTO invalid identifier

DBMS_CRYPTO is a package which is owned by SYS schema. You can ask the DBA to give the execute privilege on that package to the schema which you are using.

Here DBA has to login as SYS. Even SYSTEM or any other schema for that mater, which has SYSDBA role won't do.


Does an Oracle user have all privileges in its schema?

SQL Language Reference

  Prerequisites

  For you to select data from a table, materialized view, analytic view, or hierarchy, the object must be in your own schema or you must have the READ or SELECT privilege on the table, materialized view, analytic view, or hierarchy.

but

  To issue an Oracle Flashback Query using the flashback_query_clause, you must have the READ or SELECT privilege on the objects in the select list. In addition, either you must have FLASHBACK object privilege on the objects in the select list, or you must have FLASHBACK ANY TABLE system privilege.

So for a simple select it is sufficient to own the objects. But if you want to make a Flashback query on one of your tables, you need additional flashback-related privileges.

Also you have UPDATE, INSERT and DELETE privileges on the tables of your schema.

Details you need to execute a statement you can find in the prerequisite sevtion at the beginning of the chapter that describes a statement in this manual.


Optimize a postgresql database view query

It seems you need in something similar to:

WITH cte AS ( SELECT *,
                     RANK() OVER (PARTITION BY clients.id
                                  ORDER BY stages.position DESC) rnk
              FROM clients
              INNER JOIN deals ON deals.client_id = clients.id
              INNER JOIN deal_stages ON deal_stages.deal_id = deals.id
              INNER JOIN stages ON stages.id = deal_stages.stage_id
              WHERE deal_stages.current = true AND deals.is_active = true )
SELECT *
FROM cte
WHERE rnk=1

PS. Posted as an answer because it's too long for a comment.

PPS. I'm not sure because WHERE exists in CTE and not exists in main query.


MS SQL 2008 Express - Database Tuning Advisor

As long as you don't enable any features that aren't supported on Express Edition, you should be fine. For example I think you can use DTA to recommend table partitioning which obviously won't work on Express edition. The other thing to think about is that the server running Enterprise Edition might have a lot more memory or CPUs, so the optimizer on that server might make much different plan choices than it will on your web server.


Is it possible to sync a Pervasive SQL database to a PostgreSQL database, and vice-versa?

Possible, yes.

We can't really give you guidance without more info, but you can generally build in logic regarding synchronization and use external apps to synchronize as you need to as a last resort.  PostreSQL also has,a bunch of other tools you might be able to use on that side including untrusted stored procedure languages.

It might not be easy, and it may take a lot of work, but it is possible.


ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

If you have access to restart the mysql daemon you can follow the mysql directions for recovering your root account.


Having numbered columns is an anti pattern. Your Game table shows it.

The relationship between Game and Player is many-to-many, even though the cardinality is fixed from a game's perspective. Resolve the problem by adding a new table ("Participant"?), with foreign keys to both Game and Player.

To see how it helps, try writing a query to count the number of games an individual was in using each design.

The relationships to Player Round Result should be to this new table, not to Game and Player separately. This ensures a Player must necessarily have been in a Game to have a result recorded. The foreign key columns in Player Round Result will have the same names and types, but the foreign key constraint declaration will reference a different target.

Further, I'd suggest the table currently called Player would be better as Person, since the values it holds are for the human per se. The new table I suggest above would be well-named as Player as it is all about a person participating in a game.


Latest value of each GROUP with INNER JOIN

Most database systems won't even allow the syntax you wrote because it's a little non-sensical from the database engine's perspective. But if I understand you correctly, essentially you want the row with the MAX(date_res) for each sensor.
The way to accomplish this might vary depending on which database system you're using, so you should always tag your question with the database system and version, but here is an answer that should be applicable to most relational database systems:
WITH CTE_Results_Sorted AS
(
    SELECT fk_sens, temp_res, humi_res, date_res, ROW_NUMBER() OVER (PARTITION BY fk_sens ORDER BY date_res DESC) AS SortId -- Generates a unique ID (assuming each date is unique per sensor) for each sensor result, sorted by date descending
    FROM tb_results
)

SELECT id_sens, pos_sens, temp_res, humi_res, date_res
FROM tb_sensors
INNER JOIN CTE_Results_Sorted results ON tb_sensors.pk_sens = results.fk_sens
WHERE SortId = 1 -- Filter out everything but the latest date results row per sensor
ORDER BY id_sens


Trigger to update a date field on one table when another table is updated

You should be using the inserted and deleted pseudo tables to find out the  rows for which the column was affected by the trigger - and then update only the related rows in the second table:

CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders
    AFTER UPDATE
AS
    IF UPDATE(TradingDate)
    BEGIN
        UPDATE c
            SET ZLastSale = i.TradingDate
        FROM dbo.Customers AS c
          JOIN inserted AS i
            ON i.CustomerID = c.CustomerID     -- use the appropriate column for joining
          JOIN deleted AS d
            ON  i.TransheadersID = d.TransheadersID
            AND ( i.TradingDate <> d.TradingDate
               OR d.TradingDate IS NULL
                ) ;
    END ;


How does a security group sid differ from a per service Sid?

A SID is a mechanism that assigns privileges to the service itself, rather than to the account under which the service runs.

For Clustered installations:

It enables you to use a domain account with minimal privileges on the box, which also improves your security because people end up knowing the domain service
account credentials.

For non-clustered installations:

Per-Service SID, it allows you to use Network Service as the service account, improving security by
getting automatic password management, but without the traditional downside of having Network Service accumulate excessive privileges from multiple services.

Refer to :

SQL & SIDs why we need it and what the hell is it?
SIDs and IDs


MySQL time_zone = SYSTEM not getting correct date on CURRENT_TIME

Solved, not through MySQL, but in my WHM > Server Configuration > Server Time, then clicked on Sync Time with Time Server.


SQL Error 1452 : Cannot add or update a child row: a foreign key constraint fails

You can update your data.sql with a header and a footer like the following :
-- Add these lines as a header to your MYSQL data script
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Add these lines as a footer  to your MYSQL data script
 UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

This will enable you to restore data without any foreign-cheking.

updated :
you can re-build you data.sql file by including the header and footer :

cat header.sql data.sql footer.sql > data_NEW.sql


I think that in this case you better do this in a form.
The problem with your qeury is that it lacks where clauses. For id 1 for example, the selects to union should be:
SELECT Valx FROM Table1 WHERE ID=1
On a form you can do this for example by setting the row source in the on current event depending on the value of ID.
And yes, the structure of your database could need an update. Wich can be done with one simple query.


Select statement to table with percentage column names

Moved from comment to answer, as it seemed to resolve the OP's problem:

It sounds like the actual column names are causing your problem. If so, then you need to quote them somehow. Depending on the settings for your database:

SELECT "val%" as value, "val2%" as value2 from table

may work. In some SQL variants, you can use square brackets [] to quote column names that conflict with language keywords and operators:

SELECT [val%] as value, [val2%] as value2 from table


Stored Procedure imported from file, but tables have no data

The fact of importing or creating a Stored Procedure, does not include its execution.

After creating the SP (via MySQL Shell or via bootstrap file) you must call it, in order to process the routines which are stored in it.

Stored Procedure

DELIMITER //
CREATE PROCEDURE `books_database`()
BEGIN
   SELECT...
   INSERT...
END //

Call your Stored Procedure...

CALL books_database();

Extra Comment: Creation of Stored Procedures via MySQL Shell

Before creating your SP, first, select the database (use your_database)  where this SP will be used, or else, as you try to create the SP, the MySQL Shell will return: ERROR 1046 (3D000): No database selected.
Don't forget to rollback to the default delimiter (DELIMITER ;) in order to perform the normal commands on MySQL Shell, after creating your SP.


Database Shrinking as a logged operation

I would like to know if shrinking a data file is a logged operation or not.

It is fully logged operation and is severely bad process so ALWAYS try to avoid it. What it does is, when you hit the shrink button, quoting from Paul Randal blog

  A data file shrink operation works on a single file at a time, and
  uses the GAM bitmaps (see Inside The Storage Engine: GAM, SGAM, PFS
  and other allocation maps) to find the highest page allocated in the
  file. It then moves it as far towards the front of the file as it can,
  and so on, and so on. In the case above, it completely reversed the
  order of the clustered index, taking it from perfectly defragmented to
  perfectly fragmented.

So basically it does page movement and frees empty space from the pages. This movement of page is logged in transaction log file. Due to this movement the logical ordering of pages changes and hence logical fragmentation comes in. The point to note here is this is not an atomic operation. If you stop the shrinking operation in between the changes done so far is not lost it is maintained and when you start next time it will continue from where it left.

See this Blog from Paul Randal for data file size management if you are worried about growing data file size.


I assume new.follower_id is supposed to be:

select follower_id from folow where circle_id=new.circle_id;

I don't think this will work. If you want to use this approach you need to declare a variable for follower_id and select into that. However, a better approach (IMO) is to do:

INSERT INTO activity( uid, performed_activity_id, activity_type_id )
    VALUES (new.uid, new.event_id,  '1');

insert into notification_table (sender_id,object_id,receiver_id)
    select new.uid ,new.event_id, follower_id
    from folow where circle_id=new.circle_id;


sp_send_dbmail fails when mailing queries, but no error returned, query just hangs

Is it possible that you're running on a deadlock situation? Create a test table that you know for sure no one is accessing it, populate it with some sample data, and run a query in proc against that table just to test it out.


How can I resolve a database trigger's name with built-in functions?

Database- and server-level triggers are not scoped as "objects" per se (this is why you cannot create them under a schema, and why they don't show up in sys.objects).

You can see that these objects have certain restrictions on them, for example in the OBJECTPROPERTY() docs:

  This function cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.

And similarly in the OBJECTPROPERTYEX() docs:

  OBJECTPROPERTYEX cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.

The OBJECT_ID() docs are a little more explicit:

  Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog'.

The OBJECT_NAME() docs are less explicit, but they mention the same restriction implicitly (emphasis mine):

  Returns the database object name for schema-scoped objects.

For the first query, not sure why you need to get the name via the function, since the name column in sys.triggers already gives you that answer. For the second query, you could just join to sys.triggers:

SELECT tr.*, ts.*
FROM sys.dm_exec_trigger_stats AS ts
LEFT OUTER JOIN sys.triggers AS tr
ON ts.[object_id] = tr.[object_id];

You could create your own function, of course, but I don't know of any built-in functions that do this correlation for you (and I recommend staying away from built-in metadata functions generally anyway).

DDL triggers are kind of a special animal. So if you're worried about also having to join to sys.procedures, sys.views, etc., don't.


Turn OFF log_queries_not_using_indexes; it is virtually useless and can cause what you experienced.

If it is already off, then please show us one entire slowlog entry, together with the CREATE TABLE for the table(s) that are involved.  Also, what version are you using?

On most systems it is best to set long_query_time to no more than 2.


How to add Spatial Reference ID 28992 to MSSQL server?

I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server?

As you've mentioned, there are ways to modify system catalogs manually but in general this is not supported or recommended. As such, I consider this a non-option.

The documentation specifies:

  SQL Server supports SRIDs based on the EPSG standards. A SQL Server-supported SRID for geography instances must be used when performing calculations or using methods with geography spatial data. The SRID must match one of the SRIDs displayed in the sys.spatial_reference_systems catalog view.

Emphasis mine.

So it seems to be clear that SQL Server does not support this particular SRID (as of SQL Server 2019 - db<>fiddle).

That being said, you should

Create a feedback item for the MSSQL team about adding this functionality.
Look into PostGIS which is widely considered the best option for handling GIS data in a relational database (and free, to boot).


SQL Server Full-Text Search: combining proximity term and thesaurus

I do not believe that is a supported feature of SQL Server full text searching.  That would require a wildcard resolution to words and then a thesaurus lookup of each matching word to gather the thesaurus terms.

This basically maps to a pretty complex query: some one of a group of prefixed words is very near to some one of another group of prefixed words which all then go through a thesaurus lookup to provide even more words.  Based on previous experience, that is just not supported.

(I see online that you have asked this elsewhere in the last few months, but without any answers, so I hope that this helps.)

I believe that you can create something useful for your query, but it probably requires externalizing the thesaurus entries by doing something like the following:

DECLARE @FirstGroup TABLE (AGroup NVARCHAR(50));
INSERT INTO @FirstGroup VALUES ('hdd');
INSERT INTO @FirstGroup VALUES ('hard disk');
INSERT INTO @FirstGroup VALUES ('hard disc');
INSERT INTO @FirstGroup VALUES ('harddisk');

DECLARE @SecondGroup TABLE (BGroup NVARCHAR(50));
INSERT INTO @SecondGroup VALUES ('sata');
INSERT INTO @SecondGroup VALUES ('msata');
INSERT INTO @SecondGroup VALUES ('esatap');

DECLARE @SearchString NVARCHAR(2000);
SET @SearchString = ''

SELECT @SearchString = @SearchString + '("'+AGroup+'"~"'+BGroup+'") OR '
FROM @FirstGroup CROSS JOIN @SecondGroup

SELECT @SearchString = LEFT(@SearchString, LEN(@SearchString) - 3);

SELECT * FROM dbo.TBL_NAME
 WHERE contains(field_name,@SearchString);

So if you load the tables with the candidate thesaurus entries and make the OR connections in the code, it should work just fine.

You did not mention which version of SQL Server you are running, but if you are using SQL Server 2012, you might want to use the newer and more powerful syntax.  However, this article also has hints about the legacy NEAR context.
http://technet.microsoft.com/en-us/library/ms142568(v=sql.110).aspx


You can use BETWEEN or greater than/less than operators:

select *
from yourtable
where col1 >= 33254 and col1 <= 33848;

or

select *
from yourtable
where col1 between 33254 and 33848;

see SQL Fiddle with Demo


How to pass credentials to a linked server in another domain?

You probably need to set up Kerberos to handle delegation. This requires setting up an SPN for your SQL Server as well. The following question and sole answer give more details:

SQL Bulk Insert Impersonation issue

The main documentation links in that Question and its Answer are:

How to Configure the Server to be Trusted for Delegation
Register a Service Principal Name for Kerberos Connections
SQL Bulk Insert Impersonation issue

Also, I'm not sure if this info will be useful or not, but it might help to have the Login and Password for the account on both domains to be exactly the same. I know that when you do not have Active Directory set up but want to share folders between two systems without having to log in, it works if you use the exact same Login with the exact same Password.


Do I need to RUNSTATS after a REORG in DB2?

REORG is used to help DB2 point to accurate data (ie, indexes should become aware of fresh data and no longer include deleted data), as well as "collapse" empty page space created by deletion of data and/or indexes. It can also help move data that is related closer to each other for more efficient access (especially true in the case of a cluster index).

RUNSTATS is used to help gather updated statistics on the volume and/or distribution of data within tables and indexes. This information is stored in the system tables and is used by many things including the optimizer to determine the optimal path to query the data. This is especially true as data grows. The data may distribute in a way that causes the optimizer to now include an index that it didn't before in its access path (or perhaps vice versa and choose not to use an index). RUNSTATS can also affect how a REORG runs - at least in older versions of DB2.

Based on what I have read from DB2 DBA's you generally want to run

RUNSTATS
REORG
RUNSTATS

The first RUNSTATS helps the REORG to work appropriately and efficiently. The RUNSTATS after is more to make sure that stats are now accurate given all the data movement around on pages. Depending on where you read you may see DBA's mention the last RUNSTATS is no longer needed with newer versions of DB2. Since we haven't seen definitive answers on that and since most DB2 DBA's I've heard of follow the above order, our company has chosen to use that order (for the record we are on DB2 LUW 9.7 FP4 and we plan on migrating to V 10.1 sometime next year).

Also, just for the sake of completeness, generally when you update the statistics and you affect the optimizer you want all applications that call into DB2 to make sure they are taking advantage of those new optimizations. So a REBIND of packages will make sure that static packages know of the updated statistics and optimized query paths, and a FLUSH PACKAGE CACHE DYNAMIC cleans out dynamic queries from things like Hibernate, so that way the queries will be rebound with the updated query paths. (NOTE: on z/OS I see you have BIND and REBIND options. Perhaps the FLUSH PACKAGE CACHE DYNAMIC is under those or has a different name on z.)

So ultimately I would do the following:

RUNSTATS
REORG
RUNSTATS
REBIND packages
FLUSH PACKAGE CACHE DYNAMIC (on LUW) or whatever the equivalent is on z/OS.


First off, the sort operation on a simple query like this will probably always be the most costly.  That's because it's an operation that has to run after it has all of the data returned.  That being said, there are no clustered indexes on the tables.  That means they are heaps and they are storing the data totally unsorted on the disk.  Without adding a clustered index to those tables where your sort is applied, it's unlikely that any changes to your query will improve performance.

The only other thing that might help(might being a very big might), is move the 'WHERE type = 4' part into the corresponding join statement that it belongs to.  So instead of WHERE type = 4 it would be added to the JOIN ON for ArchiveConfig (I think that's where type comes from at least) as AND type = 4.  The other ON clause for that join seems redundant.  Keep in mind, I doubt very much that this change will actually improve performance but it will make your code a bit cleaner.

As the others have said, I don't know if it's a good idea to use the (NOLOCK) hint.  I doubt it will improve performance in this case and might result in bad data being returned.


Why is my replication lagging all of a sudden?

Ok, so after much googling I discovered the issue was quite simply we had run out of IOPS to catch up with the master database instance.

The replica DB had 120GB of storage, and the baseline IOPS for RDS is 3x storage space, so in this case it was 360 IOPS.

Cloudwatch can tell you the burst credits you have remaining, and it turned out they were all used up right at the time the replica lag started. This was great as it confirmed my suspicion that we'd run out of IOPS (in addition to @Rick_James suggesting it..)

I've now upgraded the replica to 250GB which gives 750 IOPS as a baseline - based off IOPS when everything was working fine this should be more than enough to keep up with the master server. Also, it's not too expensive ;)

Replica lag after EBS resize to 250GB:

IOPS chart after resize:


Possible to use a field within a query in PostgreSQL?

You can use:

WHERE at >= NOW() - minute * INTERVAL '1 minute' AND at <= NOW()

The Postgres documentation page has similar examples: Date/Time Functions and Operators

The above is not going to be very efficient but you could add an index on at + minute * INTERVAL '1 minute' and then modify the query condition:

WHERE at <= NOW() AND (at + minute * INTERVAL '1 minute') >= NOW()

But it would probably be better if you used a gist index on the range (from when the event starts until it finishes), with something like this:

CREATE INDEX event_start_end_idx ON events
  USING gist (tsrange(at, at + minute * INTERVAL '1 minute'));

and then use the "contains" (@>) range operator:

WHERE tsrange(at, at + minute * INTERVAL '1 minute'))  @>  NOW()


Oracle Min - ORA-00937 not a single-group group function

You want to aggregate an aggregate, so the easiest way to see this is to group twice, I believe. You may also want to rename the subquery table aliases I've used here to make them more meaningful.

SELECT sub2_.bus, MIN(sub2_.fare), MIN(sub2_.dest)
  FROM (SELECT sub1_.bus, MIN(sub1_.fare) AS fare, sub1_.dest
          FROM (SELECT tickets.bus_id    AS bus,
                       tickets.fare      AS fare,
                       route.destination AS dest
                  FROM tickets
                  LEFT JOIN route
                    ON route.route_id = tickets.route_id) sub1_
         GROUP BY sub1_.bus, sub1_.dest
        HAVING COUNT(sub1_.bus) > 1) sub2_
 GROUP BY sub2_.bus
HAVING COUNT(*) > 1


What can I do to speed up this query which aggregates over an interval?

I made a subquery that built daily order totals by shop, then ran aggregations over that table. Execution time was ~6 seconds. Final query looked like this:

SELECT s.shop_name, rrr.first_thirty_day_total FROM (
    SELECT SUM(rr.daily_total) AS first_thirty_day_total, rr.shop_id FROM (
        SELECT d.date, r.daily_total, r.shop_id FROM (
            SELECT SUM(of.total) AS daily_total, of.shop_id, of.payment_date
            FROM order_facts of
            GROUP BY shop_id, payment_date
        ) r
        INNER JOIN dates d
        ON r.payment_date = d.id
    ) rr
    INNER JOIN shops s
    ON s.id = rr.shop_id
    WHERE rr.date <= s.created + INTERVAL '30 days'
    GROUP BY rr.shop_id
) rrr
INNER JOIN shops s
ON rrr.shop_id = s.id


Is there a combination of columns in sys.dm_exec_sessions that is unique per the server?

Sessions belong to connections (1:M) and connections have unique GUID identifier, see sys.dm_exec_connections:

  connection_id uniqueidentifier Identifies each connection uniquely. Is
  not nullable.

Whenever you capture sys.dm_exec_sessions, join with sys.dm_exec_connections and capture the connection_id too.


Drop/Create MongoDB TTL index vs. collMod in production

The first you need to do is calculate how many documents the index will delete if you change the 60 days into 30. That is a simple count with {TTL_FIELD: $gt:30days}.

If the number of documents is big (how "big" depends from the server specs and the workload) I recommend to run an incremental Colmod like (pseudocode):

While achieve 30 days {

NUM = 60 days;

NUM = NUM - threshold;

db.runCommand( {"collMod" : "COLLLECTION" , "index" : { "keyPattern" : {TTL_FIELD : 1} , "expireAfterSeconds" : NUM } } )

wait 2 minutes;}

If the number of deleted document is small just do the collMod at once or on 2-3 steps manually.

Your approach to drop and recreate the index takes more time and if you have lot of documents for deletion you will get performance degradation when the index completes.

Hope that helps


In Postgres, how do I adjust the "pgsql_tmp" setting?

From the manual

  temp_tablespaces (string)

  This variable specifies tablespaces in which to create temporary objects (temp tables and indexes on temp tables) when a CREATE command does not explicitly specify a tablespace. Temporary files for purposes such as sorting large data sets are also created in these tablespaces.

In order to be able to use that, you need to create a tablespace on that volume, create a directory there (owned by the postgres user!), then run this:

create tablespace temp_space LOCATION 'mnt/volume-nyc1-01/pg_temp';

You need to grant the necessary privileges on that tablespace:

grant create on tablespace temp_space to public;

After that you can e.g. use

alter system set temp_tablespaces=temp_space;

(alternatively you could just change that in your session)

Note that for the alter system to have any effect, you need to reload the configuration:

select pg_reload_conf();

Alternatively just change the $PGDATA/base/pgsql_tmp to be a symbolic link to the directory on the other partition.


pg_ctl start gives FATAL: role does not exist

This happens when you run pg_ctl start with the -w (wait) option, because it will try a test connection with a user that does not exist (in your case).  But that's not really a problem (except for the confusing error message), because that proves that the server is up.


org.postgresql.util.PSQLException: ERROR: canceling statement due to conflict with recovery

What you see is a common experience for anyone running standbys and bigger queries on the master.  There are a couple of possible causes:

  There are also additional types of conflict that can occur with Hot
  Standby. These conflicts are hard conflicts in the sense that queries
  might need to be canceled and, in some cases, sessions disconnected to
  resolve them. The user is provided with several ways to handle these
  conflicts. Conflict cases include:

  Access Exclusive locks taken on the primary server, including both
  explicit LOCK commands and various DDL actions, conflict with table
  accesses in standby queries.

  Dropping a tablespace on the primary conflicts with standby queries using that tablespace for temporary work files.
  Dropping a database on the primary conflicts with sessions connected to that database on the standby.
  Application of a vacuum cleanup record from WAL conflicts with standby transactions whose snapshots can still "see" any of the rows
  to be removed.
  Application of a vacuum cleanup record from WAL conflicts with queries accessing the target page on the standby, whether or not the
  data to be removed is visible.

When a query will be killed for one of the above reasons is hard to predict, but usually there are some that appear more often.  It is also configurable, for options read the documentation page further.

The most important option is hot_standby_feedback, which helps against the most common cause (VACUUM removing recently-dead rows).  It has its own costs (might cause table bloat), but the outcome is usually still better than running the same queries on the master.


SQL Server Agent crashes when scheduled procedures make select in DBLink on an offline Oracle database

You can configure a timeout for a query to a remote server.  The documentation is here
( https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver15 )
You can change this on a database with this script which sets a 100 second timeout
USE AdventureWorks2012 ;
GO
EXEC sp_configure 'remote query timeout', 100 ;
GO
RECONFIGURE ;
GO

There does not seem to be a way to set a query timeout within T-SQL for one statement.  This is discussed in more detail here.
I see another user has suggested configuring the sqlnet.ora file on the client on the SQL server however this should be tested as I believe SQL Server will still wait on a response from the client even if the client has terminated the connection.
While you are testing you do not need to restart the server.  Just restart the Agent service and that will terminate all running jobs.
You might also consider a test query before doing your real work.  Try this, if it returns a value the Oracle database is up.
select 1 from dual;

Due to all places where things can go wrong in a linked server query this answer from Hannah Vernon might assist.
She suggests surrounding the initial test with some error handling that will allow any problems to be found at run time when the dynamic statement runs
BEGIN TRY
    DECLARE @cmd nvarchar(max);
    SET @cmd = 'SELECT * FROM OPENQUERY([server1], ''SELECT 1 AS c;'');';
    EXEC sp_executesql @cmd;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH;


To alias the expression computed inside a Correlated Subquery, you will need to specify it outside the complete subquery. In this case, it will be outside the If(..) block:

SELECT o.order_id,
       c.customer_name,
       Group_concat(p.product_name, ' x', p.product_quantity ORDER BY
       receipt_order ASC
       SEPARATOR
       '
') AS items,
       order_status,
       o.delivery_collection,
       o.order_total,
       o.payment_method,
       o.discount_value,
       o.order_time,
       o.order_note,
       IF(o.delivery_collection = 'delivery',
          (SELECT Group_concat(address_1,
                               address_2,
                               postcode SEPARATOR '
') -- no need of aliasing here
           FROM   address a
           WHERE c.customer_id = a.customer_id
                 AND c.customer_type = a.customer_type),
          NULL) AS delAdd  -- Aliasing needed here
FROM   orders o,
       order_products p,
       customer c
WHERE  c.customer_id = o.customer_id
       AND c.customer_type = o.customer_type
       AND o.order_id = p.order_id
       AND order_time > Now() - INTERVAL 72 hour
GROUP  BY p.order_id
ORDER  BY order_status DESC,
          o.order_id DESC


Composite field in SQL Server, does it exist?

You could leverage computed columns for this.  So your create table would look like:

CREATE TABLE foo
(ID int not null,
 OrderNumber int not null,
 OrderDate datetime not null,
 UniqueOrderNumber AS
   (CONVERT(varchar(20),OrderNumber) + ":" + CONVERT(varchar(20),OrderDate,112))
)

SQL Fiddle Example


I talked to the application team and they say that it has never occurred earlier...so it must be something with database server

Sounds about right =P

  Our SQL server version earlier was SQL 2008 R2 and we migrated to SQL Server 2016

It's likely that, since your SQL Server upgrade, the performance of some of your queries has changed.  This can lead to queries being blocked, or just running for longer (which in turn can cause this connection pooling problem, as you discovered in your investigation).

Have the developers, or users, notify you when they start getting these connection pool errors.  This will give you a time range to look into.

If you have a monitoring tool, use it to look at the queries that were running during that time.  This will give you the queries that need to be tuned.

If you don't have a monitoring tool, try logging sp_WhoIsActive to a table.  There are good step-by-step instructions by Tara Kizer here: Logging Activity Using sp_WhoIsActive – Take 2


Will switching from Concrete Table Inheritance to Single Table Inheritance improve the performance of my queries?

If you are primarily concerned with the complexity of getting the 20 most recent records, you could either write a view that does a UNION ALL of the tables, or create an empty parent table with all the common columns and have all the other table inherit using the PostgreSQL-specific inheritance feature, to hide the visual complexity of the query.  Although it probably won't improve the performance of the query.

Changing the structure as you suggest would very likely make the specific query you asked about faster, but other queries that query only records of one type would probably get worse.  You have to decide which queries are more common and more important.


differences between hot standby vs warm standby postgresql？

Those words are often misunderstood and notions are often mixed up together because they arrived in the same version of PostgreSQL.

Warm-standby is simply a replica that's not open for read-only SQL statements.
In opposition, Hot-standby is a replica that is open for read-only SQL statements.
Before that, we had a "cold standby". It was simply a restored backup, ready to be started in case of the primary node went down.
Log-shipping is the old school technic used to send the WALs (write Ahead logs) to a replica so it can replay transactions that took place on the primary node. The file is sent once the primary node isn't writing in it anymore, so lag between primary and secondary depends on how fast the primary will need another file(you can specify it needs a new file after x minutes though).
Streaming replication is the upgraded technic that allows to send modified data blocks without waiting for the WAL file to be closed by the primary. It's send as soon as that block is writtentin the WAL file. So we can reduce the lag between primary and secondary nodes.
Continuous archiving is what allows Postgres to archive WAL files when they're not needed anymore.
Binary replication is a synonym for physical replication (which warm and hot standby are).It simply means both nodes are binary identical.

I hope this will help!


How to rename MySQL users without logging into MySQL?

You will have to due it the sneaky way

STEP 01 : Restart mysqld without grants and with no one being able to login behind you

$ service mysql restart --skip-grant-tables --skip-networking

STEP 02 : Login to mysql passwordless

$ mysql

STEP 03 : Locate all users in mysql you want to change

EXAMPLE: Suppose the user in question is olduser and you want to changed to newuser

Run this

mysql> SELECT user,host FROM mysql.user WHERE user='olduser';

to user all users run this

mysql> SELECT user,host FROM mysql.user;

You can change the username with the following

mysql> UPDATE mysql.user SET user='newuser' WHERE user='olduser';

STEP 04 : Leave mysql when you have changed all users you needed to change

mysql> exit

STEP 05 : Restart mysqld normally

$ service mysql restart


Your function doesn't return a "table" in the usual sense. As noted in the docs, RETURNS TABLE (...) is just a syntactic shortcut for RETURNS SETOF record, plus a bunch of OUT params.

The output of the function is just a collection of values in memory, with no connection to the source of the data, so while callers can transform those values as they please, there is no way to propagate anything back to the original table via the function output.

If you want a different presentation of the underlying data which the user is still able to apply updates to, the only way is with an updatable view. Including generated columns should be no problem, as long as your trigger is written to handle these appropriately. The main obstacle in your case is that, unlike a function, you cannot (directly) pass parameters to a view.

The only workaround that I know of is to define your view in terms of a custom configuration variable, and require that users set this variable before querying the view, e.g.:

CREATE VIEW test2 AS
  SELECT *
  FROM test2(current_setting('my_vars.compare_date')::date);

SET my_vars.compare_date TO '2000-01-01';
SELECT * FROM test2;

Your UPDATE trigger may also query this variable if needed.


Dependency management by decoupling table name from table key

For tables and columns you could define views. The underlying objects can have whatever abstruse naming you choose. The views would make that human-friendly in the context it is used. When the design changes, create a new view with different names and you're off and running!

For programmables, such as functions and stored procedures, a wrapper presenting intelligible name could simply forward parameters to the underlying objects.

However, I do not think this is a great pattern. A relational table is more than a bucket of strongly typed variables. Columns also encode meaning. Once a column has been christened, say, iso_country_code, no other values should be permitted in that column just because they're two characters long. To repurpose columns makes a mockery of DRI. This is not how an RDBMS was designed to work.

Now, there are many fine dynamic schema persistence layers in existence. These were designed specifically to store items with variable semantics side-by-side. If this is your use-case, use one of these.


I have found a solution:

select array_to_json(array_agg(js.row)) as json from
( SELECT json_build_object('name', filename)::jsonb || jsonb_object_agg(tags.key, COALESCE(tags.str, CAST(tags.num as varchar(100)))) as row FROM
(SELECT *, rank() OVER (partition by filename order by created desc) AS rank
 FROM meta_info m where m.filename like 'DATASETS/14.0/%' ) tt
inner join tags on tags.file_id=tt.id WHERE tt.rank = 1 group by tt.filename) as js


Delete records from single Azure DB table that has no linking - reference to database and/or server name .. not supported

Change your query from

delete from
[databasename].[dbo].[tblImages]
where companyid = 123

to

delete from
[dbo].[tblImages]
where companyid = 123


Here

FROM teams p
, (SELECT @curRank := 0) r

you are resetting the value for @curRank to 0 every time you execute the query. Make this dynamic as well, adjust it according to your LIMIT clause.

On the second run change it to

FROM teams p
, (SELECT @curRank := 25) r


MySQL Available Cars With multiple owners

It wasn't completely obvious what you wanted to use all tables for, especially link_book_user, but I focused on your main question where you wanted to get all cars that were not currently being booked, taking into consideration that startTime and endTime can be null.

In the query suggestion below I filter bookings on the date already when they're being linked in the join and then the query becomes a bit easier to construct. If a booking has null values as dates the car will be seen as free.

SELECT link_model_owner.id,
        owners.fullName
FROM link_model_owner
LEFT JOIN bookings ON (link_model_owner.id = bookings.linkId
                        AND bookings.startDate <= now()
                        AND bookings.endDate >= now())
INNER JOIN owners ON owners.id = link_model_owner.ownerId
WHERE bookings.id IS null;

Note: It's important to have indexes on startDate and endDate since your database will grow quite some (as you wrote).


What is the alternative of the combination of two pattern matching conditions?

If first and second can be any value and could be embedded in other words without any kind of delimiter (eg, foofirstbar), then yes, using trigrams is probably as good as you're going to get. If there are a limited number of values for first and second, you could create an expression index of the name column passed through a regexp_replace to add spaces around your target words, and then use the built-in full-text-search functionality:

testdb=# create table data(t text);
CREATE TABLE
testdb=# insert into data select 'foofirstbarsecondbaz';
INSERT 0 1
testdb=# insert into data select 'foobarsecondbaz';
INSERT 0 1
testdb=# insert into data select 'foosecondbarfirstbaz';
INSERT 0 1
testdb=# select t from data where 'first & second'::tsquery @@ regexp_replace(t, '(first|second)', ' \1 ', 'g')::tsvector;
          t
----------------------
 foofirstbarsecondbaz
 foosecondbarfirstbaz
(2 rows)


Make query depend on input in PostgreSQL function

If I understand correctly, you can sometimes provide _company_id directly, while you only have _company_name in other situations. You do not intend to provide both at once.

Since one parameter is of type text while the other is integer the most efficient solution for this case is probably function overloading. I.e., create two functions of the same name, one for text and another one for integer.

While being at it, I suggest simple SQL functions for the simple task. The principal is the same as for plpgsql.

json_agg() returns data type json, not text. Your plpgsql variant silently coerces the type in the assignment, but that's bad style. I made out the functions to return json directly. You can cast to text explicitly if you need to.

1) companies_all_teams(text)

CREATE OR REPLACE FUNCTION companies_all_teams(_company_name text)
  RETURNS json AS
$func$
   SELECT json_agg(DISTINCT(t.name))
   FROM   companies c
   JOIN   jobs      j ON j.company_id = c.id
   JOIN   teams     t ON t.id = j.team_id
   WHERE  lower(c.name) = lower(_company_name)
   AND    NOT j.is_disabled
$func$ LANGUAGE sql;

2) companies_all_teams(int)

Since we have two functions, we can optimize each for their input. Assuming referential integrity in your relational design, we don't need to join to companies at all, we already know the company_id:

CREATE OR REPLACE FUNCTION companies_all_teams(_company_id int)
  RETURNS json AS
$func$
   SELECT json_agg(DISTINCT(t.name))
   FROM   jobs      j
   JOIN   teams     t ON t.id = j.team_id
   WHERE  j.company_id = _company_id
   AND    NOT j.is_disabled
$func$ LANGUAGE sql;

To avoid ambiguity with overloaded function calls you need to provide explicitly typed values. In your simple case, however, it's still enough to use numeric literals (no single quotes) for integer or string literals (single quotes) for text.

Call:

SELECT companies_all_teams(123);           -- numeric literal without quotes
SELECT companies_all_teams('My Company');  -- string literal with quotes

Related:

No function matches the given name and argument types
Function quote_nullable(timestamp without time zone) is not unique
Is there a way to disable function overloading in Postgres


Check if database has changed to determine need to perform backup

You can't rely on things like journaling or physical file changes to see if data has changed. I hesitate to mention LSNs because I certainly don't want to encourage any attempt down that road. Trying to do this just to determine whether it might not be worth it to actually go ahead and take a log backup is a disaster waiting to happen.

Take full backups on your current schedule. Take log backups such that your recovery objectives are met. For example, if your business stakeholders state that it is not acceptable to have more than 5 minutes of data loss, back up the log every 5 minutes (and assume that disaster can strike at 4:59.997 since the last log backup).

Sorry, but your approach does not make sense. You are trying to optimize something that is already pretty optimal - SQL Server is pretty good about resource usage and creating small diff/log backup files when there have been few or no changes. What you are attempting to do is like trying to get better gas mileage by wiping a bug off your bumper. Just take the backups. I like the analogy Thomas made: "the juice isn't worth the squeeze."

If nothing else, having a log backup file represented for every 5 minute interval will be consistent and easy to automate solutions around. If you skip some of these backups because (you think) "nothing changed", then it will be harder to automate solutions around them, and troublesome to determine if a backup is missing for that reason or because it is actually missing.


Write Access Client App for SQL Server 2005 Backend

Although Access has data bound controls, by and large you don't really want to use them for any sort of non-trivial application - partiuclarly if you want to do significant data validation before you save records.

You can use ADO through VBA on Access, much as the same as through VB.  The code to populate a form from a record set is pretty straightforward, and Access comes with a usable set of controls that can be used on a form.

If you're using ADO, the life cycle is that you read the record by issuing a query to the database.  Then you can populate the form from the recordset - this is straightforward to do with VBA.  The 'save' button onclick method calls a validate and save procedure.  You can add whatever other UI code you need to populate dropdowns, enable/disable controls etc.

You may also want to separate data access into a separate class, depending on the complexity of the application.  This lets you have a M:M relationship between formns and data items with the data access modularised into one place (i.e. one form can gather data from two or more queries by using the appropriate modules).

Beyond this we're getting into the realms of application architecture, which is probably a bigger scope than you had in mind for this question.  There are plenty of books on the subject, including ones specifically about application development with MS-Access.


You need to add some aggregation:

SELECT
     [Filename]
    ,MAX(ReceivedTime) as 'LatestReceivedTime'
    ,SUM([-1]) AS 'NotProcessing'
    ,SUM([0]) AS 'InProgress'
    ,SUM([2]) AS 'Warning'
    ,SUM([3]) AS 'Failed'
    ,SUM([1]) AS 'Success'
FROM
(
    SELECT x.Filename, x.ReceivedTime, COALESCE(x.Outcome, -1) AS 'Outcome'
    FROM @TmpTable x

) x
PIVOT
(
COUNT(Outcome)
FOR Outcome IN ([-1], [0], [1], [2], [3], [4])
) AS PivotTable

GROUP BY [Filename];


Replication normally works and I have not encountered any issues where I have to create an additional index on the distribution database. (In my company we use replication a lot for doing data movement from one region to another)

If you are not having problems with Replication, then avoid creating those indexes. Index creation is not FREE, since SQL Server has to maintain any DDL changes that means you will have to do a proper maintenance for those indexes.

There is tuning that you can do. For e.g. if immediate sync is set to TRUE for your Publication then that will cause the transactions to be held in MSRepl_commands rather than deleting them when distributed. So setting immediate sync to FALSE will help the distribution clean up job do clean up much faster.

  Is there any documentation that supports creating indexes on the distributor databases of transactional replication?

Microsoft does not support making any changes to the system tables (replication, logshipping) except you are told by CSS. This does not mean that you cannot create those indexes, it just means that if you open a case with Microsoft they wont support unless you remove the indexes that you have created by yourself.

Check this connect as won't fix - Add index on [distribution].[dbo].[MSrepl_commands]

So my recommendation would be, baseline your server instance. Get data for a longer duration and if you are facing problems then address them by creating appropriate indexes. Test, test and test all your changes first and always be on the supported side when you work on a vendor software. You can file a connect item and if Microsoft addresses it, then it will be included as hotfix or in future versions.


How to get a 2 row output in a single line in mysql?

select @@hostname,
(select count(*) from test.PATCHES) "RECOMMENDED_PATCHES",
(select count(*) from test.PATCH_VERSIONS) "PATCH_VERSIONS"


Check (health) status of MySQL schema table

To monitor your database's health in overall you could simply use:

mysqlcheck --all-databases --auto-repair

or on weekly basis (if you prefer, due to optimize option resource usage)

mysqlcheck --all-databases --auto-repair --optimize

To verify tables:

mysql
\u mydb
CHECK TABLE mytable FAST QUICK;

The first command will check the integrity for all tables of all databases, if there's any corruption your table will be repaired automatically.
And the second will additionally optimize the table on disk.

Check the explanation about it here:
What is the difference between optimize table and analyze table in mysql


What is the difference between hadoop, mongoDB, Big data?

3 different things.
Hadoop is a framework. Something you use to develop an entire application.
MongoDb is a db. A nosql one. It is where you store data.
BigData is a concept. It is related to huge quantity of data. Where huge is not a fixed parameter. 4 mb was huge in 1960. Now huge means several terabyte.


Grant users privilege to use QUERYTRACEON 9481

You can create a plan guide as sysadmin for the query, which would then allow the user to run the pre-existing plan.  Otherwise, rewriting the query is about all you can do.

Using QUERYTRACEON in plan guides


Sql Statement to display multiple true statement

You'll need separate CASE expressions since evaluation stops after the first condition is satisfied.

You could use CONCAT to concatenate the values of all expression results into a single column. If the result of a CASE is NULL (WHEN predicate is false or unknown), CONCAT will return an empty string for the value.

SELECT
    CONCAT(
        CASE WHEN (LEN(DSC1) - CHARINDEX(char(1), DSC1)) <> LEN(DSC1) THEN '[DSC1 - NUL (null)], ' END,
        CASE WHEN (LEN(DSC1) - CHARINDEX(char(2), DSC1)) <> LEN(DSC1) THEN '[DSC1 - SOH (start of heading)], ' END,
        CASE WHEN (LEN(DSC1) - CHARINDEX(char(3), DSC1)) <> LEN(DSC1) THEN '[DSC1 - STX (start of text)], ' END
        ) AS [Special Character]


Is using strings as keys for reference to other tables bad in terms of memory usage?

Yes MySql and all other rdms will store the complete email as varchar and reserve space for the number of bytes it needs or the maximal size depending on the rdms.
Integer with up to 8 bytes for a big integer will only use these bytes, and are so faster when referencing.
In terms of speed you use  INTEGER, and consider other like varchar(36) for uuids when the need arises, for example different servers have to save data in the same table.
with emails a unique and so be indexed for referencing, you should take the extra mile and use Integer if you expect you will have big tables.


Database Design - One table for different types of transactions

There are essentially three options (with the possibility of complexity via combination of the three options):

Keep each table separate
Bring all of the tables together as one major table
Use an associative relationship:  a base table with the common columns, and individual tables for the non-common columns

Here are some of the factors to consider:

Do these really represent the same entity?  If so, what is that entity?  Based on your examples (Quote, Order, Invoice, and Credit Memo), these are separate entities to me.  They are 'related' but are not the same or subtypes of a master entity (such as having an Employee table and ExemptEmployee and NonExemptEmployee subtables which have fundamentally different attributes).  This tilts in favor of keeping tables separate.
Do you need to query against Quote UNION Order UNION Invoice?  Note that this is different from joining Quotes to Orders (to get, say, a percentage of successful sales).  If you do need to do those types of queries often, that argues in favor of a single table.  I could see doing this if you're looking at "the life of an order."  On the other hand, that's still a simple query even when split out into several tables, so it's a minor consideration.
Are there columns which are not common to all of the types?  You mentioned that there are several special fields per type.  This means you probably do not want to go with option #2 in any case, because option #3 re-introduces relatively simple integrity constraints.  Suppose that, in your big table, you have three columns which are required if you are doing an Order, but should be NULL otherwise.  Doing this is trivial with associative tables, but requires check constraints with the One Big Table approach.  And if column X for an Order needs to be numeric but for an Invoice needs to be text, that just makes validation way more difficult.
Do you ever change types?  Not just copying Quote data into an Order, but actually changing the Quote itself to an Order.  If so, that's an argument in favor of one big table.  But I don't think that's a realistic business process.
How many orders do you normally get?  The more orders you get, the more you want to split out those tables.  A scan against X pages is still better than a scan against 4X pages, and separate tables increases the likelihood that you'll be able to do seeks instead of scans because of finer-grained control of indexes.

Based on these considerations, I would personally keep the tables separate.  Even though they may have similar attributes, they are fundamentally different entities.

What I would try to do, however, is look at those sets of common attributes and see if they really belong on the Quote/Order/Invoice/Credit Memo, or if normalizing those out into their own tables would be a better idea.  For example, if you have customer first name, customer last name, customer street 1, etc., splitting that out into a customer key (or customer and address keys, depending upon circumstances) could cut down on the total number of columns in each of the four major entities, reducing repetition and gaining the other benefits of normalization.

It's possible that you've already normalized everything out to the fullest extent and that each of those entities does, in fact, legitimately have 50 attributes which are in common.  In that case, I'd still keep separate tables because you're talking about fundamentally different things which happen to relate to one another.


Sql Server 2012 data warehousing and different versions

Business Intelligence Edition

Business Intelligence edition has some useful features, like Master Data Services and non-additive aggregations (i.e. anything but sum/count).  EE has partitioning and the rest of the large database features.  The EE features are mostly relevant to users with large data volumes.  If you have less than (say) 100GB of data then you can probably get by with BI edition.  B.I. edition also has a limit on the number of CPU cores and memory that can be used by the database server, although this does not appear to apply to Analysis Services or Reporting Services.

A more detailed breakdown of the S.E., B.I. and E.E. features can be found here.

Some things that will be fine with B.I. edition

Most OLAP applications - B.I. edition seems to give you the clever aggregates (last non empty etc.) and other features that SE doesn't on the OLAP server.  By the look of the link, all SSAS features present in EE are present in B.I. edition, which makes it a bit more  of a contender for data marts.
MDM applications - B.I. edition comes with Master Data Services.
Moderate data volumes.  You can probably get away with (say) 100GB or so on BIE by applying brute force at the hardware level (fast disks).
B.I. edition supports distributed partitioned views, which gives you a basic read-only sharding capability.  However, the additional hardware and licensing may be no cheaper than biting the bullet and getting EE.
SSRS seems to be the same across B.I. and Enterprise editions.
Memory and CPU core limits do not apply to SSAS and SSRS servers.

Some things you will need Enterprise Edition for

If you have compliance requirements for physically secure data then the encryption and audit facilities of EE may be desirable.  Note that this is new in 2012.
Table partitioning is an EE only feature.  If you want to use table partitions to manage large data volumes you will need EE.
Star join transformations are only supported in EE.  If you have an application with lots of highly selective (<1% coverage) queries on a very large fact table you might get a win from star transformations.  This feature isn't really well documented in SQL Server circles, though, so it's hard to tell how well it works in practice.
Columnstore index - if you want to use this for fast ROLAP applications (using report builder or a 3rd party ROLAP tool such as Business Objects) then you may get significant mileage from this feature on EE.
Table compression may be useful for archiving old data.
B.I. edition only supports servers of a certain size - 64GB RAM, 4 sockets or 16 cores for the database server.  If you want to scale above a two socket machine then you will probably need EE.
B.I. edition is only licensed on a 'Server + CAL' basis.
Parallel DBCC and index builds are only supported in EE.  If you want to drop and recreate indexes for ETL loads this may reduce your run-times, particularly on incremental loads onto large incumbent data sets.
EE has a query re-write facility (called 'automatic use of indexed view by query optimiser').  If you want to use these to boost ROLAP performance you may want EE.  However, although this is quite a mature feature on Oracle I can't really vouch for how well it works on SQL Server in practice - although SQL Server does have a CUBE operator on GROUP BY, which is mostly indended for this application.
EE has fast Oracle and Teradata adaptors for SSIS, and adaptors for various other 'enterprisey' sources such as SAP BW.
Some of the MDM-ish featues of SSIS - e.g. fuzzy lookups - are only available in EE.
Change Data Capture is an Enterprise Edition only feature.


MySQL Read Speed and Partitioning on Separate Drives

What concerns me fact that fieldC is not the lead column in the PRIMARY KEY.
What would be preferable is to reverse the order to the primary key columns
CREATE TABLE `my_table`  (
 `id` int(10) unsigned NOT NULL,
 `fieldA` char(40) NOT NULL,
 `fieldB` char(40) NOT NULL,
 `fieldC` char(32) DEFAULT NULL,
 -- some other fields
 PRIMARY KEY (`fieldC`,`id`)
 -- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
  partition pNULLorEMPTY VALUES IN(NULL,''),
  partition pValueA VALUES IN('valueA'),
  partition pValueB VALUES IN('valueB'),
  partition pValueC VALUES IN('valueC')
  -- other partitions
);

This may help navigate faster through the partitions. The MySQL Query Optimizer may/may not be smart enough to need the reversal.
If you have other columns in the WHERE clauses such as

WHERE fieldC='...' AND fieldA='...'
WHERE fieldC='...' AND fieldB='...'

you will need matching indexes so quickly search within one partition. Otherwise, you may find yourself doing a full table scan with the partition.
With that in mind, please create the table like this:
CREATE TABLE `my_table`  (
 `id` int(10) unsigned NOT NULL,
 `fieldA` char(40) NOT NULL,
 `fieldB` char(40) NOT NULL,
 `fieldC` char(32) DEFAULT NULL,
 -- some other fields
 PRIMARY KEY (`fieldC`,`id`),
 KEY C_A_Index (`fieldC`,`fieldA`),
 KEY C_B_Index (`fieldC`,`fieldB`)
 -- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
  partition pNULLorEMPTY VALUES IN(NULL,''),
  partition pValueA VALUES IN('valueA'),
  partition pValueB VALUES IN('valueB'),
  partition pValueC VALUES IN('valueC')
  -- other partitions
);

This will then accommodate such WHERE clauses.
Give it a Try !!!
CAVEAT
I noticed your character fields are CHAR and not VARCHAR. That's great.
If any of the character fields are VARCHAR, please read on.
If there are any VARCHAR fields, you need not convert them manually. Simply change the row storage format as follows:
ALTER TABLE mytable ROW_FORMAT=Fixed;

This will make the table's read speed increase 20%-25%. I wrote about this before:

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?
Sep 20, 2011 : Best of MyISAM and InnoDB Jan 03, 2012 : Optimized my.cnf for high-end and busy server


Cannot find one or more components. Please reinstall the application

Uninstall the Visual Studio 2010 Shell, this was done by downloading the installer.

Once downloaded, run the installer and remove the VS2010 Shell component. Once done, run the SQL 2014 Management Studio installer or do a repair on your existing installation, this will itself reinstall the VS2010 Shell components.

Then it opened for me.

In Order To Repair Sql Server Management Studio Do Below Steps -

Open Control Panel Then Click on Program and Features
Then Find Microsoft Sql Server 2012 , Right click on it and choose uninstall
Then uninstall wizard will open and Follow the wizard steps to uninstall it.


In PSQL, is it possible to insert rows in two different tables with not-nullable foreign key columns referencing each other?

Assuming the primary key columns are defined as serial (or bigserial) you can first get the new IDs using nextval() and then use them in the subsequent inserts.

This can either be done separate statements:

select nextval(pg_get_serial_sequence('make','make_id'));
select nextval(pg_get_serial_sequence('model','model_id'));

insert into make
  (make_id, name, first_model_id)
values (
  currval(nextval(pg_get_serial_sequence('make','make_id'))),
  'Make One',
  currval(pg_get_serial_sequence('model','model_id'))
);

insert into model
  (model_id, name, make_id)
values (
  currval(pg_get_serial_sequence('model','model_id')),
  'Make One',
  currval(nextval(pg_get_serial_sequence('make','make_id')))
);

or a single statement using a data modifying CTE

with new_ids as (
   select nextval(pg_get_serial_sequence('make','make_id')) as new_make_id,
          nextval(pg_get_serial_sequence('model','model_id')) as new_model_id
), new_make as (
  insert into make (make_id, name, first_model_id)
  select new_make_id, 'Make One', new_model_id
  from new_ids
)
insert into model (model_id, name, make_id)
  select new_model_id, 'Model 1', new_make_id
from new_ids;


It's not completely clear what you're trying to do, but this might help:

declare @Data table (
    nID varchar(16)
  , name varchar(16)
  , saletotals float
  , saledate date
)

declare @GiveMeSaleTotals table (
  nID varchar(16)
, datetocheck date
)

Insert into @Data (nID, name, saletotals ,saledate) Values
 ('111eee', 'Blue', '13.22', '01/01/2016')
,('111eee', 'Blue', '44.11', '01/03/2016')
,('111eee', 'Blue', '22.11', '01/08/2016')
,('222ffff', 'Red', '11.11', '01/11/2016')
,('222ffff', 'Red', '22.10', '01/22/2016')
,('4444ffff', 'Red', '22.10', '12/05/2016')
,('4444ffff', 'Red', '21.10', '12/05/2016')

insert into @GiveMeSaleTotals (nID, datetocheck) values ('4444ffff', convert(date,GetDate()))

select d.*
  from @Data d
    inner join @GiveMeSaleTotals g on d.nid=g.nid and d.saledate=g.datetocheck

results in:

    nID              name             saletotals             saledate
---------------- ---------------- ---------------------- ----------
4444ffff         Red              22.1                   2016-12-05
4444ffff         Red              21.1                   2016-12-05

Rextester link: http://rextester.com/SPJQX46455


What happens to replication if rows have been deleted on a target already?

What happens from a SAP/Sybase Replication Server (SRS) perspective?

Depends on SRS version and how the DBA's configured the SRS instance.

Prior to SRS 15.2 the 'empty' DELETE would cause no problems, ie, the DELETE would be issued against the target, no rows found/affected, and SRS would continue to process the next transaction.

The key here is that a DELETE statement that does not find any rows to delete merely completes, successfully (ie, without error), with @@rowcount=0.

NOTE: The same 'logic' applies to UPDATEs, INSERT/SELECTs and SELECT/INTOs that complete successfully but affect no rows.

NOTE: The same 'logic' applies to DML statements that complete successfully but affect a different number of rows.

With SRS 15.2+ a replication server error class was introduced with the primary emphasis being to notice when transactions affect a different number of rows in the target ... and when said issue occurs to raise an error (eg, 5185) and suspend the DSI connection.

Each transaction coming over from the source is tagged with the number of affected rows.  If the transaction, when applied to the target, affects a different number of rows then SRS will (by default) suspend the DSI and dump an error message to the errorlog.

The DBA can modify the default rowcount mismatch behavior by assigning a different action to the corresponding error number in the replication server error class (see the assign action command for more details).

Obviously (?) the DBA should research any rowcount mismatch errors to determine if they are ok/valid, or if they indicate an issue with the the target being out of sync with the source.

Other scenarios the DBA would want to investigate could include:

replicated stored procs that don't affect the same set of rows on the target
custom function strings that modify a transaction in such a was as to (explicitly) modify the number of affected rows in the target
lack of a primary key for a table

Keep in mind that while SRS is routinely used to keep a DR/backup database in sync with the primary database, SRS can also be used to:

distribute subsets of data (distribution model)
consolidate data (roll-up model)
maintain historical/audit data (eg, convert DELETEs/UPDATEs to save 'old' image of data in a history table)
propagate sub-/super-sets of data per application requirements

Net result is that the occurrence of rowcount mismatches may or may not be ok ... it all depends on the individual SRS environments.


Here is the query you require:

SELECT TABLE_NAME
FROM DBA_TABLES
WHERE IOT_TYPE IS NOT NULL;

The documentation states:

  If this is an index-organized table, then IOT_TYPE is IOT,
  IOT_OVERFLOW, or IOT_MAPPING. If this is not an index-organized table,
  then IOT_TYPE is NULL.


Install pg_trgm in PostgreSQL 8.4 on Windows XP

Answered in comments by a-horse-with-no-name:

Those are only "notice" message, not errors (I don't understand what they say, but NOTICE is never a problem or even an error). All statements ran without error, so everything is fine and the module was created. But you should really upgrade to a modern version (12 or 13) and also ditch Windows XP.