Buffer Cache Size when Page Life Expectancy Drops

I'm troubleshooting an issue with one of our SharePoint databases, essentially huge drops in PLE which I think are caused by increased I/O activity (I see increased reads, writes and lazy writes at the same time)

I can see from the question that you have SQL Server 2012 SP1. There was a Bug in SQL Server 2012 which forced PLE to plummet but that was fixed in SQL Server 2012 SP1 CU4. Now since ** SQL Server 2012 SP3** has been released I suggest you apply SP3 and see if the issue subsides.

  I wanted the check the size of the buffer cache at the exact time of PLE drop,

You can't use perfmon counter Buffer Manager: Database Pages to check buffer size. As per BOL what it signifies is:

  Indicates the number of pages in the buffer pool with database content.

So you can see it does not gives information about complete Buffer Pool. When you define SQL Server max server memory the value set in max server memory becomes the buffer pool size. As such from SQL Server 2012 onward the buffer pool has less significance. Previously it was both consumer and the provider of memory. From SQL Server 2012 onward its just the consumer. To see the contents of the buffer pool(to some extent) you have DMV sys.dm_os_buffer_descriptors.

Below query returns cached page count of each database.

SELECT COUNT(*)AS cached_pages_count
    ,CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE db_name(database_id)
        END AS database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,database_id
ORDER BY cached_pages_count DESC;

If you want to get pages cached for each objects in particular database

SELECT COUNT(*)AS cached_pages_count
    ,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
    INNER JOIN
    (
        SELECT object_name(object_id) AS name
            ,index_id ,allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.hobt_id
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name
            ,index_id, allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.partition_id
                    AND au.type = 2
    ) AS obj
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC;


You have to first decide: Which table do I want to return all records for?

The answer for you sounds like "Client". So start with your client table:

SELECT ClientID, ClientName
FROM Client

Then ask: Which tables do I want to add?

The answer for you sounds like "Completed" and "Planned". So join them to Client:

SELECT c.ClientID, c.ClientName
FROM Client AS c
JOIN Completed AS comp ON c.ClientID = comp.ClientID
JOIN Planned AS p ON c.ClientID = p.ClientID

Now ask: Do I want to return all fields from my Client table, or only those that have matching records in both tables?

The answer for you appears to be: "All records from client and any matching from other tables". So for this, outer join to the tables from your base and return the columns you want:

SELECT c.ClientID, c.ClientName, comp.FirstDeliveryStatus, comp.FirstDeliveryDate, p.NextDeliveryStatus, p.NextDeliveryDate
FROM Client AS c
LEFT OUTER JOIN Completed AS comp ON c.ClientID = comp.ClientID
LEFT OUTER JOIN Planned AS p ON c.ClientID = p.ClientID

Now if you only wish to have records which have a record in Completed or Planned tables, add in a filter:

SELECT c.ClientID, c.ClientName, comp.FirstDeliveryStatus, comp.FirstDeliveryDate, p.NextDeliveryStatus, p.NextDeliveryDate
FROM Client AS c
LEFT OUTER JOIN Completed AS comp ON c.ClientID = comp.ClientID
LEFT OUTER JOIN Planned AS p ON c.ClientID = p.ClientID
WHERE p.ClientID IS NOT NULL OR comp.ClientID IS NOT NULL


What is the Postgres's equivalent/closest lock of Microsoft SQL Server's IX (Intent Exclusive) lock?

You need to take a step back and check what Intent Locks are made for.
Transaction locking for MS-SQL server is broadly explained here:
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide
And for PostgreSQL it's broadly explained here
https://www.postgresql.org/docs/current/mvcc.html
MS-SQL server doc says:

Intent locks are named intent locks because they are acquired before a
lock at the lower level, and therefore signal intent to place locks at
a lower level.
Intent locks serve two purposes:
To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
To improve the efficiency of the SQL Server Database Engine in detecting lock conflicts at the higher level of granularity.

There is no equivalent to intent locks in PostgreSQL. The IS, IX, SIX, IU, SIU, UIX lock modes have no equivalent because they refer to a concept that PostgreSQL doesn't have and seemingly doesn't need.


1GB is "tiny" today.

Swapping is terrible for MySQL performance, so it is important not to tune things too big.

max_allowed_packet -- dangerously high, change to 30M; if you really need more than that, then either get more RAM, or we can dig deeper to find some other solution.

Change max_connections to, say, 40.

Change open_files_limit to, say, 1000.

Can you ferret out the specific error when "establishing a database connection"?  That might help, too.


Help in understanding Hinted Handoffs and Data replication in Cassandra

Your understanding is correct that beyond the default 3 hours, hints are no longer stored. This is to avoid overloading the other alive nodes for prolonged period of time.

If any node happens to be down for a longer period than the hint_window, its absolutely necessary to rebuild the node or even remove all the files from its data directory and let it bootstrap as a brand new node. You may have to use replace_address if you are bringing up the same node.


Insert escaped double quotes into MySQL JSON field

Let's create the table:

mysql> create table jsontest (id serial, value json);
Query OK, 0 rows affected (0.02 sec)

mysql> show create table jsontest\G
*************************** 1. row ***************************
Table: jsontest
Create Table: CREATE TABLE `jsontest` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `value` json DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

And insert some JSON data/objects:

mysql> INSERT INTO jsontest(value) VALUES (JSON_OBJECT('test', 'string with "escaped quotes" does not work'));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO jsontest(value) VALUES ('{"test": "string with \\"escaped quotes\\" does not work"}');
Query OK, 1 row affected (0.01 sec)

mysql> select id, value->>"$.test" from jsontest;
+----+--------------------------------------------+
| id | value->>"$.test"                           |
+----+--------------------------------------------+
|  2 | string with "escaped quotes" does not work |
|  9 | string with "escaped quotes" does not work |
+----+--------------------------------------------+
2 rows in set (0.00 sec)

As you can see here you have to use the double backslash escape sequence because you don't want the SQL parser to perform the standard escape sequence processing but instead you want to pass the literal string containing the escape sequence down to the storage engine for the JSON data type processing.

I'll talk to the MySQL docs team about adding some examples and explanation for that here:

12.6 The JSON Data Type - Creating JSON Values


Failed to populate indices of table table_name

This very likely is caused by Postgres version support. If you are trying to connect to a Postgres 10 server SQL Power Architect does not support this version. My solution, which is far from perfect, is to run a small older Postgres in my development desktop for ERD purposes. From what I can gather, version 10 support is either unlikely and/or problematic at best.


How to change the default timezone in Amazon Redshift?

You can change the timezone with a SET command.
For example:

SET TIMEZONE = 'America/Los_Angeles';

For more info please find the relevant AWS documentation here.

The challenge is that setting the environment seems to hold for maybe the leader node but doesn't get transferred along to the compute node that may end up writing your table.
See the comments here for more info on the issue:

Until the folks at Amazon fix that issue, you can alter the user timezone with

ALTER USER dbuser SET timezone to 'America/Los_Angeles';

or convert the timezone each time you make the timestamp like so:

load_dttm TIMESTAMP DEFAULT convert_timezone('America/Los_Angeles', sysdate) NOT NULL


How do I translate this query from MS Access into SQL Server

Not quite sure I follow as I rarely use Access, but I believe this is what you're looking for:

SELECT -SUM(
   CASE WHEN 12=[GroupNum] THEN 1 ELSE 0 END
   * [BoardStatus]
   ) AS G12
[...]

CASE...WHEN is the inline "if" of T-SQL.


Your question, at first, surprised me. Usually, I've seen memory issues with max server memory being configured incorrectly. But then I started pouring over the cumulative updates and realized that their are minefields of memory leaks sewn throughout all these cumulatives for SQL 2014! It's like Microsoft took a hint from their SQL 6.5-7.0 playbook and went retro on us.

Rather than spend hours of trouble-shooting, I would consider:

Option A:
Taking a snapshot of the VM and installing the latest cumulative 7 update for SQL 2014 sp2. And rolling back if necessary. (it looks like a memory leak related to SSAS is included in this update).

Option B:
Create a Microsoft support ticket to help yourself and the rest of us out by possibly adding a memory leak fix to cumulative 8 when it comes out. And updating your question based on Microsoft's findings.

Personally, I don't think I would consider any reason satisfactory for SQL Server going much above it's max memory limit--for any situation.


Easiest solution
(If you have deep enough pockets for Oracle and can handle the audit/licensing requirements)
Create the big ugly wide table, but then use Oracle's in-memory column store to speed your analytic queries.  The high level view is each column is split, dedicated to memory (at least partially), and data compression and some other storage tricks are used to speed lookups.
This works great for the type of ad-hoc workflow you describe and doesn't require much thought about table design other than choosing the correct data types (all else being equal, smaller = faster).
As always, test thoroughly as vendor's claims are often inflated.  But the engineering is sound and others have found column stores to be very beneficial.
Also Easy Solution
(With caveats)
SQL Server has a similar solution, the Clustered Columnstore Index, but there are impacts to update performance that may make it unsuitable to your workflow.  If you have a wide window during which you can perform your updates, it may work well.  Worth a try.
Harder solution
(If you don't have deep pockets or clustered columnstore doesn't work)
Recreate, in part, the same idea as a columnstore but in a typical database table.  Some refer to this as sixth normal form (a timestamp is not required for the definition), others vertical partitioning.  I just tend to think of it as don't read more data than you have to.
You will need an RDMS that has both clustered indexes (index-organized tables) and join elimination.  Right now that's SQL Server, Oracle, Sybase, and DB2.
The idea is you have your Customer table with a very basic amount of data stored with it.  For every attribute, you will create a table Customer_<attribute> that contains the key from Customer and the column(s)* associated with that attribute.  If that attribute is NULL you will not insert a row into that table.**
This seems like a lot of work, but if you have a list of columns and their data types, you can write a script to generate the table creation as well as the required insert/update procedures.
You can also write a script to generate a view with all of your columns (for simplicity's sake you can just LEFT JOIN all the attributes back to the Customer table).  The view will look like a big ugly table, but with join eliminate it will behave much differently.
So if you search for customers on three attributes, the query engine will look at the request, realize it only needs to hit three tables in addition to Customer and off you go.
Some tables will benefit from secondary indexes and you can add them as necessary and if they improve query performance (they don't always).
This solution also has the added benefit of making updates/inserts fairly quick and without requiring the all the data to undergo a lock.
The biggest downside to this solution is you'll get some "expert" who will walk in, proclaim "joins are bad!" and you'll have to patiently explain to them why they are not.
*If one of the attributes is comprised of a foreign key reference to a composite key, you would not split them apart.  Additionally, if those columns will be used together to search for a customer you would include them.
**This pattern requires NULL/NOT NULL be enforced through your update/delete processes.
The "Please Don't Do This"
(Really, please do not do these)
Entity-attribute-value (EAV).  Looks simple.  In practice it's a nightmare, join logic is convoluted, data/relational integrity is impossible to maintain and table locks are disastrous.
Big, ugly, wide table with an index on every column.  Wastes a lot of space, performance isn't great.
Big Data/Whatever is hot this week.  No/limited indexes.  No clear access path to the data.  Data/relational integrity may vary from weak to non-existent.


Nevermind, found a way, by adding one more CTE
WITH cte AS ( ... )
, sum1 AS (
  SELECT i, MAX(x) tot -- or AVERAGE/MIN
  FROM cte
  GROUP BY 1
)
SELECT ...
   , (SELECT SUM(tot) FROM sum1) AS sum1
FROM cte


Issue of structure of query does not match function result type

To return a TABLE add the table definition:

CREATE OR REPLACE FUNCTION historicalavg01()
RETURNS TABLE
(
    Hist double precision,
    Tm   timestamp
) AS
$$
DECLARE starttime timestamp;
DECLARE endtime timestamp;
DECLARE total_hour_avg DOUBLE PRECISION;
l_row RECORD;
BEGIN
CREATE TEMP TABLE historical_temp(Histroical DOUBLE PRECISION,time timestamp);
total_hour_avg := 0;
FOR b IN 0 .. 23 LOOP
    FOR a IN 0 .. 27 LOOP

        starttime := (CURRENT_DATE - (a  * INTERVAL '1 day')) + (b * INTERVAL '1 hour');

        endtime := (CURRENT_DATE - (a  * INTERVAL '1 day')) + ((b + 1) * INTERVAL '1 hour');

        --total_hour_avg := total_hour_avg + (SELECT Avg("activepower") AS "Avg" FROM sca_meter WHERE "time" between starttime AND endtime);
        total_hour_avg := b * 0.5;

        IF(a = 27) THEN
            INSERT INTO historical_temp SELECT AVG(total_hour_avg) AS "Histroical Avg", CURRENT_DATE - ((b + 1) * interval '23 hour') AS "time";
        END IF;

    END LOOP;
END LOOP;

RETURN QUERY SELECT * FROM historical_temp;

END$$ LANGUAGE plpgsql;
SELECT * FROM historicalavg01();

hist | tm
:--- | :------------------
0    | 2020-02-05 01:00:00
0.5  | 2020-02-04 02:00:00
1    | 2020-02-03 03:00:00
1.5  | 2020-02-02 04:00:00
2    | 2020-02-01 05:00:00
2.5  | 2020-01-31 06:00:00
3    | 2020-01-30 07:00:00
3.5  | 2020-01-29 08:00:00
4    | 2020-01-28 09:00:00
4.5  | 2020-01-27 10:00:00
5    | 2020-01-26 11:00:00
5.5  | 2020-01-25 12:00:00
6    | 2020-01-24 13:00:00
6.5  | 2020-01-23 14:00:00
7    | 2020-01-22 15:00:00
7.5  | 2020-01-21 16:00:00
8    | 2020-01-20 17:00:00
8.5  | 2020-01-19 18:00:00
9    | 2020-01-18 19:00:00
9.5  | 2020-01-17 20:00:00
10   | 2020-01-16 21:00:00
10.5 | 2020-01-15 22:00:00
11   | 2020-01-14 23:00:00
11.5 | 2020-01-14 00:00:00

db<>fiddle here

Have a look at this tutorial about it.


Unexplainable DBCC CHECKDB Command Output

The error message you're seeing:

  Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C.

Occurs when sqlcmd is asked to quit while it is waiting for a response from SQL Server.  Most likely someone (or something) killed the spawned cmd.exe process that was running SQLCMD.exe, which resulting in that error message being directed to the SQL Server errorlog.

Instead of using SQLCMD to execute DBCC CHECKDB, you should simply setup a standard T-SQL job step to run the DBCC CHECKDB command directly.

Alternately, run Ola Hallengren's excellent free and open-source Maintenance Solution, or one of the other myriad solutions available.


What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer)

It is easy enough to benchmark this, but the INSERT performance of UUIDs will be worse, because they are bigger and slower to generate.
But it doesn't sound like you are building a high performance application anyway (then you probably wouldn't be using JSON), so it probably won't make much difference.
Finally, you want to use UUIDs for security reasons (which I won't discuss here). Security always puts a penalty on performance and usability, so consider it as the price you are paying for security.


Merging multiple client-specific databases into a single one

Yes, that's normally how it is done.

Note that this is a non-trivial application change, pretty much every query interacting with the database (inserts, updates, selects) is going to have to include that ClientID.

Depending on what else you're storing, some tables might not need a ClientID. To make up some examples:

If your products table is available to all clients, that won't need a ClientID
A child table (like OrderDetail) might not need a ClientID if its parent record (the Order table) already has a ClientID.

Please see Brent's excellent post about some additional tradeoffs/consideration of this design.


Data Validation before entry into Couchbase server?

You can validate the input by adding another entity "validate_doc_update" to the _design/ document.The validate_doc_update function gets executed for each document you want to create or update. If the validation function raises an exception, the update is denied; when it doesn’t, the updates are accepted.
It is optional i.e. if you do not write one, no checking will be done.

More details can be found on http://guide.couchdb.org/draft/validation.html


This is an extremely common error, with literally millions of search results online. The typical troubleshooting steps include:

Make sure the SQL Server service is actually running (SQL Server Configuration Manager > SQL Server Services > check the State of the specific instance and make sure it says "Running", otherwise right-click > Start).
Make sure the SQL Server Browser service is running, you can check this in Configuration Manager as above, or using sc query sqlbrowser from the command line (if it is not running, right-click > Start or run net start sqlbrowser from the command line).
If you are trying to connect to a different machine (or using a name that forces you to route outside of your machine for some reason), make sure the server is configured to allow remote connections. You can fix this by using remote desktop to connect to that server and running this locally:

EXEC sys.sp_configure N'remote access', 1;
GO
RECONFIGURE WITH OVERRIDE;

Of course this might be hard to do if it's the local instance you can't connect to, so you'll have to get connected locally using other steps in this list first before you'll be able to talk to the server to change this setting.
Make sure the port SQL Server is running on is open, and that TCP/IP is enabled (SQL Server Express Edition, for example, expects Named Pipes for remote connections by default). You can see this in Configuration Manager > SQL Server Network Configuration > Protocols for <instance name> > TCP/IP, right-click, properties, IP Addresses tab, scroll all the way down to see (a) that TCP/IP is enabled (and if not, right-click > Enable, may as well try enabling Named Pipes too), and check what port is listed under IPAll > TCP Dynamic Ports or TCP Port. Note that if it's using dynamic ports, that port could change, and if SQL Browser is not configured to start automatically, you'll be right back here potentially on your next reboot.
Make sure the server name is correct, and be sure to try IP address, simple server name, and fully-qualified domain name. If the server is local, try localhost and 127.0.0.1 in addition to the server name. Make sure you can traceroute/tracert to the server and telnet to the running port. If you can connect to one of these hosts but not others, either change your connection string, change your hosts file and/or DNS, or consult your network administrator.
Make sure the instance name is correct. Often people type their machine name and don't realize that they are trying to connect to a named instance, which requires syntax like server_name_or_ip\SQLEXPRESS - a common one as SQLEXPRESS is typically the instance name in use when running SQL Server Express Edition.
If you can't connect using any of those methods, it is likely a firewall issue. You'll need to determine which ports to open and for whom (of course your SQL Server should be secured from external traffic whenever possible), but you can quickly test if this is the problem by momentarily disabling the firewall altogether.

A lot of this is documented much more elaborately here (found in Paul White's wiki post here):

How to Troubleshoot Connecting to the SQL Server Database Engine


RMAN Not restoring data from restore set

It sounds to me that you want to restore a newer backup of the previous incarnation, but the start of the current incarnation is before the time this backup was taken.

Check the output of list incarnation;. If the reset time of the current incarnation is before the time this backup was taken, you will need to reset incarnation to X;, where X is the incarnation key.


Number of databases on a server. Should I try to consolidate these objects together?

Best practice is what works best for your organization.

If you're going to discuss this with your boss, who has a certain way of doing things, then you need to plan out the discussion in terms of a business case.

So, first:
Rank the following twice each, in terms of What's important to you, and what's important to your boss.  If these rankings are the same, you need to re-evaluate; that's pretty unlikely

Maintainability

via the GUI?
via handwritten SQL?
via automated SQL?

File count on disk
Cross-database queries
Cross-database permissions

Remember, EXECUTE on DatabaseA allows the object executed to access other objects in schemas with the same schema owner; however, going cross-database changes the rules

Quickness of the initial, first draft of a solution
Quickness of adding dozens of followon, similar solutions
Quickness of adding dozens of followon, identical solutions
Quickness of making changes to dozens of already existing similar solutions
Quickness of making changes to dozens of already existing identical solutions
Ease of investigating why solution A6 is not quite acting like solution A23, when they "should be" the same
Ease of restoring the entire set of databases somewhere else
Separation of data so customer A's data is not mixed with customer B (often a contractual requirement)
Ease of moving the entire set of Agent jobs somewhere else
Overall performance of ALL the solutions at once - profiler, extended events, indexing, monitoring
Foreign keys

which CANNOT go cross-database

Essentially, most of this boils down to: If you want one-off results as quickly as possible for each one-off unique thing, a separate database makes sense, but they should be destroyed when they are no longer required.  However, long term, you're going to have hundreds of databases; is that desired?  Or required, perhaps, due to contracts?

I would say that the arguments to consolidation are firmly around foreign keys (data integrity), identicalness of solutions (data integrity), and doing stuff by hand.  The latter should be replaced with automated techniques which can just as easily handle 500 databases as 2, so I would ignore that.

Again, know what's important to your boss; if consolidating is a solution that is superior in terms of what's important to them, you've got a good potential for a successful argument.  If not, you don't.


Oracle Database 11G Installation "Error al llamar al destino 'install' del archivo make"

Solved with:

Try to link with g++ instead of gcc.

cd /bin
mv gcc gcc.save
ln -s g++ gcc

and run the installer again. as suggested on: https://stackoverflow.com/questions/28774848/oracle-database-installation-issue-in-ins-ctx-mk


2 fast SQL queries get slow when bunched as subquery on a very large table

I'm not an expert on mysql/mariadb, but I suspect that it is running the sub-query one for every row in CLD_DB.lnmntr, or at least every row it needs to until it finds 500 that match the condition (which might just be 500 or might be far more depending on your data). Some SQL engines are bright enough to see that the inner query is invariant within the context of the current statement and only run it once, but IIRC mariadb's query planner doesn't do that sort of thing.

Assuming you have an index on CLD_DB.lnmntr your first query will result in a seek to the first point where lnmntr_ndx >= 18411001 then an ordered partial scan until 500 rows are found (or it hits the end). Even if the inner query were pretty much instantaneous so there would be very little difference between running it once and running it some hundreds or thousands of times, if it needs to be run for each row then you are forcing it to scan down the index from the beginning until 500 rows after the point where lnmntr_ndx is greater than the value.

So it could be either or both of these factors. To confirm which use EXPLAIN to look at the query plan, as suggested by Nick, and edit your question to add the resulting information. It would also be useful to provide the relevant table definitions (columns and their types, and the indexes & keys).


Is making joins between different table spaces slower?

This is only for Postgres:

If the tablespaces reside all on the same harddisk, then there is no performance difference at all.

If the tablespaces are created on different harddisks then yes, this can make a difference. In that case the necessary I/O is spread over multiple disks, thus improving the overall throughput (assuming all harddisks have the same performance).


Can a user with readonly access change SQL server settings

Both the settings that you have mentioned in the question requires membership in public role, which is true in almost all the cases. You may refer below screenshot from the login window:

Kindly refer microsoft site for both of these as below:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql?view=sql-server-2017

You may also refer article from Pinal Dave on this setting. There is another wonderful article from Mr. Ahmad Yaseen,

I hope above helps.


Does HP Vertica run on Windows and is it compatible with our existing Oracle SQL?

Vertica only runs on Linux, you can have a Windows client to acesso Vertica. But don't get scared Linux is not such a scary dragon kkkkk. Vertica installs as 1,2,3 check this link for a full installation article.
  Vertica uses SQL ANSI so no need for you to learn other languages, it has it's differences but is easy to work with.
 No need for you to break you brain in doing performance tuning and all those Oracle crazy queries, or create a billion indexes in SQL SERVER, Vertica does this for you.
With the Database designer you can throw your query into Vertica and Vertica will create the optimum design for you.

I think that Vertica arch is so simple that people are confused about it !! kkkk
HOST: is a computer where the Vertica is installed.
- is a linux box that has the Vertica software installed with the required configuration
to see the configuration needs go to this link for a full how to tutorial

INSTANCE: it is also a computer where the Vertica is in running state.
- this is the linux box with Vertica installed and up and running(as a single node or as part of a cluster) take a look at this link for more details about Vertica arch topology

Node: it is the combination of HOST & INSTANCE.
 Once more the link i gave you would explain this point. A node is a part of a cluster that has the Vertica software up and running on it. The instance is made of the processes that Vertica software creates when the Vertica engine is started.
 So in short Node is made of the Host(is the linux box with the Vertica software) and Instance(are the processes that the Vertica software creates) - this is not specifically for Vertica, it applyes to all databases.

CLUSTER: I am not clear about this. May be this is about the instances of Vertica running on different computers.
- a cluster is a collection of Vertica instances. Why do cluster is good ? MPP(massive parallel processing), HA(high availability).

DATABASE: It is the actual installed database software where the data & catalog resides to which other nodes are connected (not sure abt this also).
The database is combination of Software(all those binary+ more files filed with code + datafiles(these files hold the data that is processes by your database)) and the instance (once again the instance is the process created by your database software).
- I hope i could help !
Feel free to visit this link  for more Vertica scripts, articles and tutorials.
Also this link


Computed Column Update when Table Value changes

Given the additional information that Aaron Bertrand didn't have access to when he posted his answer I would suggest a different tack.

Instead of putting logic/business significance in table names I would have general table names and put the logic/business significance in attributes/data in the tables.  This should make it easier to expand functionality, and maintain your data.  Furthermore you can extract useful information much easier.

The following is a rough schema that captures the direction I recommend and will probably need to be adapted to your exact needs:

CREATE TABLE dbo.WeatherStation
(
    WeatherStationId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(50) NOT NULL -- This is where you put the name of the station instead of in the table.
)

CREATE TABLE dbo.SensorReading
(
    SensorReadingId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    WeatherStationId INT NOT NULL FOREIGN KEY REFERENCES dbo.WeatherStation(WeatherStationId), -- Match a reading to the station
    ReportedTime DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(), -- When the time was reported to the database
    <Other columns like temp, pressure, etc.>
)

CREATE TABLE dbo.SensorOffset
(
    SensorOffsetId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    WeatherStationId INT NOT NULL FOREIGN KEY REFERENCES dbo.WeatherStation(WeatherStationId), -- Match a reading to the station like you do now
    Offset DECIMAL(20, 10) NOT NULL -- Adjust precision/datatype as needed
    Comment NVARCHAR(500) NULL,
    Created DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME() -- This would need to be unique per weather station
)

Now you can add a new station without duplicating table schema, you can easily compare data from related stations, etc.

Even if you didn't want/can't change your schema, I would recommend putting the calculation in a view.  That is more obvious in my opinion than a trigger, and it would be easier to trouble shoot for me.  Something like the following should work with my schema above:

;WITH CurrentOffset_CTE AS
(
    SELECT
        WeatherStationId
        , MAX(Created) AS Created
    FROM dbo.SensorOffset
    GROUP BY
        WeatherStationId
)
SELECT
    WS.Name
    , SR.ReportedTime
    , CASE WHEN SR.<reading> IS NOT NULL THEN SR.<reading> + SO.Offset ELSE NULL END AS <reading>
    , <repeat same pattern as above for the various readings>
FROM dbo.WeatherStation WS
    INNER JOIN dbo.SensorReading SR ON SR.WeatherStationId = WS.WeatherStationId
    INNER JOIN CurrentOffset_CTE CO ON CO.WeatherStationId = WS.WeatherStationId
    INNER JOIN dbo.SensorOffset SO ON SO.WeatherStationId = CO.WeatherStationId AND SO.Created = CO.Created

This will be easy to troubleshoot, hard to miss, and obvious to future maintainers.  You could modify this code to work for your current schema too, but would have to duplicate it for each station.  In that case I would still recommend this approach for the above stated reasons.


Defining two distinct relationships between a user table and a company table

To guarantee that a company, which can have many users, can ever have at most one admin, and further guarantee that the user defined as the admin is also a user defined for the same company, create a separate admin table. However, the foreign key definition has to be created in a specific way.

create table Companies(
  ID      int not null auto_increment,
  Name    varchar not null,
  Address varchar,
  primary key( ID )
);

create table Users(
  ID      int not null auto_increment,
  Name    varchar not null,
  primary key( ID )
);

create table CompanyUsers(
  UserID    int not null references Users( ID ),
  CompanyID int not null references Companies( ID ),
  JoinDate  date not null,
  LeaveDate date,
  primary key( UserID ), -- makes this 1-n
  unique( UserID, CompanyID )
);

create table Admins(
    UserID   int not null,
    CompanyID int not null,
    ..., -- other admin-related data
    primary key CompanyID,
    foreign key( UserID, CompanyID ) references CompanyUsers( UserID, CompanyID )
);

At first, the Admins table appears to define a 1-n relationship the same way as the CompanyUsers table. But if you follow the FK relationship through, you will see that it can only support 1-1 relationships.

Technically, you don't need the CompanyUsers table as you can define the company and dates in the Users table. This maintains the 1-n relationship but then it is no longer in 3nf. If you are going to have attributes (JoinDate, LeaveDate) that are dependent on more than the PK (that is, they describe the relationship rather than the user), they should go in a new table.


How to set weights on a Postgres TSVECTOR column?

Okay so this was dumb of me. The weights work correctly. The issue I was having was because of the forced UTF8 encoding (I simply set "utf8" on the db, didn't rebuild. I set it back to latin1 and it works now).


How to monitor how much data is transfered in a linked server?

This sounds like a job for PERFMON, which is included with the latest windows server offerings.

http://technet.microsoft.com/en-us/library/cc749249.aspx

Basically you're going to setup a counter to watch the MSSQL data transfer between the servers.

You may be able to use a counter inside of MSSQL to track this as well, but I'm not familiar with any functionality that provides that.


Show sql statements from mysql binlog (GTID)

It seems that the option  --verbose must be added:

mysqlbinlog  --base64-output=AUTO --verbose mysql-bin.000005

In the result you see:

### UPDATE `customer`
### WHERE
###   @1=388442
###   @2=382023
###   @3='2015:05:30'
###   @4='2015:06:02'
###   @5=3
###   @6=1
###   @7=0

@x are the table columns in their order


SQL ISO Standard for group concatenation

The wiscorp.com page SQL Standards has several older revisions and drafts of SQL:20nn (zip):

The 7IWD2-02-Foundation-2011-12.pdf, with a date of 2011-12-21 has at page 289:

  If at least one of S1 and S2 is the null value, then the result of the <concatenation> is the null value.


Is it possible to get a "pages touched" statistic from PostgreSQL?

You can analyze your query with EXPLAIN (ANALYZE, BUFFERS) (BUFFERS being the essential option to see details about memory utilization). A "buffer" in RAM is the equivalent of a "block" on disk, 8 kb on most installations.

Example:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM tbl WHERE tbl_id = 123;

With a big table tbl that's not cached, yet (not recently queried), and a btree index on tbl_id, you'll see something like:

Index Scan using tbl_pkey on tbl  (cost=0.43..5.45 rows=1 width=86) (actual time=61.360..61.363 rows=1 loops=1)
  Index Cond: (tbl_id = 12345)
  Buffers: shared read=4

Meaning that 4 data pages have been read from disk. That includes both index and table pages. For an index scan reading a single row from a big table, that's typically 3 blocks from the index and one from the table.
(For a small table you'd see shared read=2 - only 1 index page for a shallow btree index.)

If you repeat the same, you typically see the same, executed much faster and one difference:

  Buffers: shared hit=4

Meaning that 4 data pages have been read from cache, cached due to the first invocation.

The manual explains:

  BUFFERS

  Include information on buffer usage. Specifically, include the number
  of shared blocks hit, read, dirtied, and written, the number of local
  blocks hit, read, dirtied, and written, and the number of temp blocks
  read and written. A hit means that a read was avoided because the
  block was found already in cache when needed. Shared blocks contain
  data from regular tables and indexes; local blocks contain data from
  temporary tables and indexes; while temp blocks contain short-term
  working data used in sorts, hashes, Materialize plan nodes, and
  similar cases. The number of blocks dirtied indicates the number of
  previously unmodified blocks that were changed by this query; while
  the number of blocks written indicates the number of
  previously-dirtied blocks evicted from cache by this backend during
  query processing. The number of blocks shown for an upper-level node
  includes those used by all its child nodes. In text format, only
  non-zero values are printed. This parameter may only be used when
  ANALYZE is also enabled. It defaults to FALSE.

Related:

"Recheck Cond:" line in query plans with a bitmap index scan


Count instances of value from one column in another column located in another table

You can do this:

SELECT Table1.Task, COUNT(Table2.Task)
FROM Table1
LEFT JOIN Table2
    ON Table1.Task = Table2.Task
GROUP BY Table1.Task ;

Table1 is your base set, so join to it. You want to retain all values, so you use a LEFT JOIN and you join on the matching task values. The group and count operations happen on different columns - the GROUP BY is based on your tasks from Table1, and you COUNT the of tasks from Table2.

Here's a demo (using CTEs as implemented by SQL Server, PostgreSQL, Oracle, etc.):

WITH Table1 AS
(
    SELECT 100 AS Task
    UNION ALL
    SELECT 101
    UNION ALL
    SELECT 103
)
,Table2 AS
(
    SELECT 113 AS Task
    UNION ALL
    SELECT 113
    UNION ALL
    SELECT 103
)

SELECT Table1.Task, COUNT(Table2.Task)
FROM Table1
LEFT JOIN Table2
    ON Table1.Task = Table2.Task
GROUP BY Table1.Task ;


Small MySQL table swap/replace taking incredibly long time

Rename table requires a LOCK on the table to complete.

In your 0sec case there were no connections using the table and it works immediately.

In you 8 min case, The RENAME table waits in a stage something like "waiting for table metadata lock" until the existing SELECTS are done. Also the SELECTs that are issued after the RENAME waits on the lock. Hence the hang you notice.

You could run show full processlist when this occurs to confirm.

To avoid this, you will have to time the rename in the same app that is reading or kill the existing connections that are holding the locks.

Perhaps the SELECT queries need to be tuned as well. 40K row table shouldn't take that long to return. Since you mentioned all the queries are by ID, is there an INDEX on ID?


HIVE SQL [Error 10025]: Expression not in GROUP BY key

As long as one ip address has more than 20 rows, all these rows will be returned. If less then 20, do not return.

WITH cte AS ( SELECT *, COUNT(ip_address) OVER (PARTITION BY ip_address) cnt
              FROM my_table
              WHERE DAY = current_date() )
SELECT *
FROM cte
WHERE cnt > 20;


How do I identify all super users in SQL Server?

You can use this code:

SELECT   name,type_desc,is_disabled,
         (IS_SRVROLEMEMBER ('sysadmin',name)) AS ISSRVROLE
FROM     master.sys.server_principals
-- WHERE    IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY name ;

sys.server_principals - Contains a row for every server-level principal

IS_SRVROLEMEMBER - Indicates whether a SQL Server login is a member of the specified server role.

You would also need to check for the sysadmin-equivalent CONTROL SERVER permission for the server principal in sys.server_permissions.

IS_SRVROLEMEMBER can return:

0 -  login is not a member of role.
1 - login is a member of role.
NULL - role or login is not valid, or you do not have permission to view the role membership.


T-SQL How to Create a new column as INT from Datetime column as YYMM or YYYYMM


Mapping 5 columns to 1 (ID, dimension) table without 5 joins?

Normalise the table responses so there is 1 question per row. Then you are also not limited to a number of questions per survey.


Insert randomly takes 10+ seconds to complete

Could be other sessions are causing blocking. See the Q& A http://dba.stackexchange.com/questions/34313/identify-blocking-and-send-alert.

If the actual and estimated plans are the same, there's no point providing them, because they can't explain anything (aside from general inefficiencies that should apply to all executions, not just the slow instances). You'll could monitor sys.dm_exec_requests when it is running slowly to see who/what it's waiting on.

You can also monitor wait statistics (it could be severely bottlenecked I/O, for example). Reproducing in Plan Explorer might help here, as you can automatically get the wait statistics for your session (unless you fire up your own extended events session, you can only get system-wide waits on your own).

To capture what is going on during the potential blocking periods, you could also use Service Broker and Event Notifications.  Tony Rogerson back in 2007 posted a sample that reports on 10 second (or longer) blocks that you could use.

Also check auto-growth events and settings for the database and log. Occasional long delays can be caused by having to wait for a data or log file to grow.


Mac OS X 10.10 mySQL 5.6.26 accessible from CLI but not port 3306

I had this same problem too, and changing the port in /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist fixed the issue, with no side effects so far.

Changing the port in my.cnf didn't work. It makes the Preference Pane say that MySQL is not running, and makes it impossible to start from the Preference Pane.


Why does MySQL Workbench let me add unique indexes to primary keys?

Answer compiled from comments by:
Akina:
No reason for to restrict index creation when the statement is syntactically correct. The most common issue - CREATE TABLE t (id SERIAL PRIMARY KEY); which creates both UNIQUE index (due to SERIAL definition) and PRIMARY KEY (due to explicit specifying). So excess UNIQUE key must be removed by an additional statement.

To make this more confusing, this (upvoted) answer says:

This question deals with SQL Server and cannot be applied to MySQL. Check such moments carefully.

So could there be edge cases, or should I just cement in my memory to never think about UQ on a column that's already PK, and move on?

If you have PK then its expression is always unique, and you do not need in "think about UQ on a column that's already PK". Additionally you may be sure that none column mentioned in PK expression definition is NULL (by default all such columns are defined as NOT NULL implicitly if there is no explicit option in column definition - check SHOW CREATE TABLE for this, fiddle).

...and Vérace:

or should I just cement in my memory to never think about UQ on a column that's already PK, and move on?

Yes, move on! Typically a UNIQUE index is for a natural primary key - and your PRIMARY KEY is for a surrogate. Purists aren't too keen on this approach, but it's more or less an industry standard.
There are cases where having a surrogate slows down the system.  A prime example is with many-to-many mapping tables where a composite PK is demonstrably better.
Just to add a word - the UNIQUE index for a natural primary key should include a NOT NULL for the relevant fields - one can have a UNIQUE index with NULL fields - because each NULL is neither equal to nor not equal to any other NULL or any other value. Take a look at this fiddle.
Would it be good Workbench were to suggest or enforce "best practice"?  If you think so, file it as an enhancement at bugs.mysql.com
Another "suggestion":  INDEX(a) is redundant and wasteful if you also have INDEX(a,b).
A contrived use case:  SELECT id FROM tbl; would be more efficiently executed by scanning the UNIQUE index's BTree instead of scanning the PRIMARY KEY's BTree.  The former contains only id, the latter contains all the columns of the table.  (If you tack on a WHERE clause the references any other columns, then the PK's BTree will be the optimal.)


SQL Server 2008R2 - Why is my index not used

I like providing direct answers to questions; however, this topic can go deeper and longer, therefore I am adding a few articles at the bottom that expand on these details for all to learn from.

In summary ...

A few things that can affect using an index (mind you there are a lot of reasons too as you'll see in the articles posted below). The main priority of the engine is to predict how to get the data off the disk as fast as possible in the most effective/efficient way (use of statistics). What becomes the primary choice of the engine is to perform a table scan or index seek/scan. (There can be more depth to this conversation, but I will keep it to the context of your question).

Use less fields in the SELECT that are more cohesive with the INDEX KEY and INDEX INCLUDES.
More rows in the table will associate with the use of indexes
The more the rows are selective (unique) the more likely the use of indexes, thus many NULL values will cause the engine to avoid the index

In detail ...

What are some causes a table scan or an index scan/seek is used. These are also general helpers to determine index creation and use for most general situations. Follow these few steps and you will achieve big benefits right from the get-go.

One reason, as it is mentioned in the comments above, is the use of SELECT * FROM WHERE . SELECT * is a sure-fire way for the engine to decide to avoid using indexes. It's faster to get all fields from the table (Clustered Index/Heap) by scanning/retrieving from the table itself, bypassing any indexes. First human choice is to minimize your select fields to those in the INDEX KEY and in the INDEX INCLUDES.

The second reason is to how many records are in the table. The fewer the records, the easier it is for the engine to simply scan the table. The statistics can have a part in this. Because the engine will use the statistics to predict where the data is on the pages/disk, it can be said that there is not enough rows/distribution to use the index.

And thirdly, having values in your table that are less selective (less unique), like Male and Female, the less likely the index will be used. A table that will use indexes more in queries will be for fields that are highly selective (more unique), like zip codes in an address table (so long as your list of address are NOT all of your neighbors with the same zip code).

There are many techniques and strategies. But one piece of advice, become knowledgeable in reading and experience with indexes/statistics and how/where to put them on DISKS/LUNS and you'll go far as a DBA and offering huge performance gains.

Understanding SQL Server Statistics
Stairway to SQL Server Indexes


As an answer,

  What exactly does the function do? Can you define it as stable or even better as immutable? – a_horse_with_no_name 12 mins ago

From the docs

  IMMUTABLE / STABLE / VOLATILE These attributes inform the query optimizer
  about the behavior of the function. At most one choice can be
  specified. If none of these appear, VOLATILE is the default
  assumption.

  IMMUTABLE indicates that the function cannot modify the database and
  always returns the same result when given the same argument values;
  that is, it does not do database lookups or otherwise use information
  not directly present in its argument list. If this option is given,
  any call of the function with all-constant arguments can be
  immediately replaced with the function value.
  STABLE indicates that the function cannot modify the database, and
  that within a single table scan it will consistently return the same
  result for the same argument values, but that its result could change
  across SQL statements. This is the appropriate selection for functions
  whose results depend on database lookups, parameter variables (such as
  the current time zone), etc. (It is inappropriate for AFTER triggers
  that wish to query rows modified by the current command.) Also note
  that the current_timestamp family of functions qualify as stable,
  since their values do not change within a transaction.
  VOLATILE indicates that the function value can change even within a
  single table scan, so no optimizations can be made. Relatively few
  database functions are volatile in this sense; some examples are
  random(), currval(), timeofday(). But note that any function that has
  side-effects must be classified volatile, even if its result is quite
  predictable, to prevent calls from being optimized away; an example is
  setval().

  For additional details see Section 36.6

The real question is essentially, can you optimize away f(x)

Over everything (mathematical purity)
Over the same scan
Never (uses IO or system calls)

A somewhat important note is that even in production applications you can sometimes lie and say IMMUTABLE and it's perfectly fine. Just be aware of the consequences.


Easy way to update Excel data to SQL Server

You coud either use BULK INSERT directly in SQL Server Management Studio like this:

BULK INSERT [DataBase].[dbo].YourTable
FROM 'filepath'
WITH
(
    Firstrow = 1,
    Fieldterminator=';',
    Rowterminator='\n',
    Codepage = 'ACP'
)

For Excel-Files (.xls) you can use this:

SELECT * INTO YourTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=YourFilePath', [ExcelSheet$])

You may have to replace the 8.0 with your current office version (8.0 is for Office 2000, 14.0 is for Office 2010, for example).

There's also SqlBulkCopy in C#.

If you don't want to import all columns, I suggest you write a small program, load the file into a DataTable, drop all the columns you don't need and then use SqlBulkCopy


Mysql master/master replication, what is the problems

The autoincrement% settings you mentioned take care of AUTO_INCREMENT values, but do not handle other UNIQUE KEYs.  Keep in mind that if a dup key error occurs, replication is stopped until a human intervenes.

Depending on how the code (router, proxy, etc) picks which Master to write to, you may have the "critical read" problem.  This is where, say, someone posts something, then goes to another web page and expects to see the results of it.  But the read goes to another machine and the results have not [yet] arrived because of replication delay.

Automatic failover is a big challenge.  It is probably best left to be manual.  If the network dies, you could have the "split brain" problem.  In this case it is unsafe to failover because machine can't see the other.  If they both blindly thing the other is dead, and take control, you could get data conflicts that are 'impossible' to fix.

Some of the issues are avoided by designating one of the Master as active and having the other be readonly.

After all, every write to a Master is also performed on every Slave (including the other Master in a dual-Master setup).  That is, there is essentially no "write scaling" by having dual-Master.  There is some "read scaling".

What is your goal of picking M-M?  Scaling?  HA?  Disaster protection?  Facilitate certain schema changes?  Other?  Are the two Masters located next to each other?  On the same power source?

One of my points is that M-M provides limited benefits.  And you may have to do other things to really achieve them.  (Separate data centers, careful routing of queries to servers, etc.)

A number of the issues are lessened if you go to one of the "cluster solutions" (Galera or Group Replication).  They require 3 servers, preferably in 3 geographic locations.  But they handle split brain, automatic failover, and some other issues.


Can I Improve Performance on Bloated System Tables?

If you have not already done so, you could gain performance by moving the primary data file to a separate set of spindles from the rest of the data (see Files and Filegroups Architecture and SQL Server: filegroup for system tables only?).


What are the best practices for backing up a secured SQL Server 2012?

There's different things you can do with certificates: you can use them to encrypt endpoint communications (like mirroring or AlwaysOn Availability Groups), or you can use them to encrypt databases with Transparent Data Encryption (TDE).

If you're using them to encrypt endpoint communications, just back up the certificates once and keep them in a safe place. I wouldn't expect shops to routinely rotate those certificates.

If you're using them with Transparent Data Encryption, back up both the certificate and the key as described in Books Online:

http://technet.microsoft.com/en-us/library/bb934049.aspx

With TDE, though, you should routinely rotate the keys. Otherwise, when anybody steals the certificate and key, then they can decrypt all of the backups going forward. When you rotate the key, you'll want to immediately back that up again. You can't run backups while the database is re-encrypting with the new key, but back it up again as soon as the re-encryption process is done.


The answer to this question depends on the server-side language being used.
I ran two simple benchmarks and compared the results using MySQL and PHP.
The database used in the tests contained 500,000 records
Test #1 (Calculation in MySQL):
$start_time = microtime( true );

$sql =  'SELECT
            SUM( ROUND( retail - ( wholesale / ( 1 + ( 8.25 / 100 ) ) ) ) ) AS percent_sum
        FROM
            invoice';

$result  = $mysqli->query( $sql );
$invoice = $result->fetch_assoc();

$end_time = microtime( true );

echo $end_time - $start_time;       // Result: 0.56663717265

Test #2 (Calculation in PHP):
$start_time = microtime( true );

$sql =  'SELECT
            retail,
            wholesale
        FROM
            invoice';

$result = $mysqli->query( $sql );

$percent_sum = 0;

while ( $invoice = $result->fetch_assoc() )
{
    $percent_sum += round( $invoice['retail'] - ( $invoice['wholesale'] / ( 1 + ( 8.25 / 100 ) ) ) );
}

$end_time = microtime( true );

echo $end_time - $start_time;       // Result: 0.76414209361

These results remained typical for all tests performed, so my conclusion is that calculations in MySQL seem to perform faster than PHP for the types of calculations performed in the question given.
Regardless, I would prioritize maintainability over micro-optimizations, so if you are performing calculations on the data extracted from the database in PHP for the majority of the functions, consistency is key.


Why does Oracle hang on "ALTER TABLE ADD ... NOT NULL"?

My case is similar, my talble has 3 million rows. It hangs about 2 hours and I abort it.

I found  the reason is some other sessions lock this table. After I killed the sessions, It completes adding column in miliseconds.

I don't know why Oracle don't throw "resource busy" when adding column.


Duplicate key value error message on a database for a writing groups scenario

The problem is in the books insert

INSERT INTO books VALUES('GroupName1','title 1 ','P1','1994',50);

You have a foreign key constraint, books_pub_fk1, referencing publishers, but you don't have a publisher P1, it is P01.

This is for referential integrity.

You will also have the same problem once this is fixed with WritingGroups_Books_fk as you don't have a group record in WritingGroup named GroupName1


To do that you need a column to say when is true or false.
You can change the schema view or the query that run against the view.

Just use a case clause

select *, case flag_column when 1 then 'TRUE' else 'FALSE' end as boolean_column from view

I suppose that is what you will need


I would suggest you to make use of External Tables. You can create an external table on your CSV file using ORACLE_LOADER driver and then update your existing table with data in your external table using DML (MERGE for example).

Consult Oracle Utilities Guide for detailed info.

What follows is my sample of how you can update tables from flat files.

First you create the directory where your flat file will be located:

SQL> create directory ext_tab_dir as '/home/oracle/ora_load';
Directory created.

SQL> grant read, write on directory ext_tab_dir to spongebob;
Grant succeeded.

Then you create your external table:

SQL> CREATE TABLE emp_load (empid          number(3),
                            first_name     CHAR(15),
                            last_name      CHAR(20),
                            year_of_birth  CHAR(4))
       ORGANIZATION EXTERNAL (
         TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
         ACCESS PARAMETERS (RECORDS FIXED 21 FIELDS (
           empid          char(1),
           first_name     CHAR(7),
           last_name      CHAR(8),
           year_of_birth  CHAR(4)))
         LOCATION ('info.dat')
       );

We'll assume that you already have the table that you want to update with data from the flat file:

SQL> CREATE TABLE emp (empid         number(3),
                      first_name     CHAR(15),
                      last_name      CHAR(20),
                      year_of_birth  CHAR(4));
SQL> insert into emp values(1, 'SpongeBob', 'SquarePants', '1997');
SQL> insert into emp values(2, 'Patrick',   'Star',        '1997');
SQL> insert into emp values(3, 'Squidward', 'Tentacles',   '1997');

(Here, I just loaded it with sample data.)

Now that's what your info.dat contains, for example:

[oracle@oca ~]$ cat ora_load/info.dat
1Alvin  Tolliver1976
2KennethBaer    1963
3Mary   Dube    1973
4NandiniShastri 1985

At this point you will just update your existing table with above data from the external table:

SQL> merge into emp e1
       using emp_load e2
       on (e1.empid = e2.empid)
       when matched then update set e1.first_name    = e2.first_name,
                                    e1.last_name     = e2.last_name
       when not matched then insert
         values (e2.empid, e2.first_name, e2.last_name, e2.year_of_birth);

Here's what you'll get as a result:

SQL> select * from emp;
     EMPID FIRST_NAME      LAST_NAME            YEAR
---------- --------------- -------------------- ----
         1 Alvin           Tolliver             1997
         2 Kenneth         Baer                 1997
         3 Mary            Dube                 1997
         4 Nandini         Shastri              1985


Allocate space for object 'dbo.SORT temporary run storage

This is pretty self explanatory - your tempdb is full, and can't be grown to perform the operation it needs tempdb for.

As indicated, either the disk where it is located is full, or you haven't got suitable autogrowth settings (see this earlier post for information about that), if it's not autogrowth, you'll have to either delete other files from the disk that your tempdb is on, or locate your tempdb on a bigger disk.

This sqlservercentral post is similar to yours and might take be worth a look. This video by Brent Ozar might be worth a look too, covering tempdb performance and config.


possible to specify field enclosure in psql command line?

In v12, psql introduced the --csv option.  However, it still seems like the wrong tool for the job.  You should use COPY or \copy instead (probably still invoked through psql)


Quickly clone a database in SQL Server Management Studio

I just took a look at my ssms. Copy is in SSMS 2012 and 2014 under tasks. I haven't tried it but it is there. What ssms are you using?

SSMS 64 bit: http://download.microsoft.com/download/5/2/9/529FEF7B-2EFB-439E-A2D1-A1533227CD69/SQLManagementStudio_x64_ENU.exe


Understand responsibilities of database and backend

An RDBMS is part of the backend, only in  rare cases such as access, you can have both.
Even so the GUI should always (try to) prevent errors in the database, and should control the input of its validity.
Errors happen, should not directly shown to the user. But as you have to debug it, you should show helpful ids or text, that could help fixing things.
For a qualified system you have to go a step further and tell the user how he can fix the problem, for example be editing his input in a correct way.
Check constraints help to debug your code, especially in the beginning, when the GUI is only starting, so no problem will be missed.
Foriegn keys, triggers and events help the database maintaining or do things that the user has no influence over, since the RDBMS is  a background process (nearly always running on a different machine to the end-user's), that shouldn't come to light.


The steps you have should almost work. Assuming that there are database changes in the upgrade (I'm assuming there are because you want to keep SQL #2 offline so it doesn't upgrade) then SQL #2 won't be able to automatically or gracefully become the primary, as there is no quorum it can use. You'll need to force a failover with data loss.

For your questions:

I can't see why it wouldn't work. SQL #2 doesn't know about the changes on SQL #1, the sharepoint farm doesn't know about the changes, everything should resume as normal
If you power on SQL #1, it will realise that it's not the primary, but it will also be impossible to make it synchronise again. You'll have to drop the database from #1, break the AlwaysOn Availability Group and reseed it again. Because #1 isn't the primary I can't see how it will write data over #2.


A is not good

a table for the keywords

ID  PK
Keyword

then have a join table

jobID       PK FK
keywordID   PK FK

with FK to the two tables


This topic is documented in the Oracle Security Guide at the section Finding Information About Audited Activities.

In your case you should find useful the catalog view DBA_OBJ_AUDIT_OPTS which displays the objects on which auditing options have been enabled.

Here is described the view structure.


Combine intersecting ranges to produce new ranges based on the points of intersection

My guess

select ID, valid_from
  , lead(valid_from,1,'31.12.2999') over (partition by id order by valid_from) as valid_to
  , case Item_a when '-' then lag(Item_a,1,'-') over (partition by id order by valid_from) else Item_a end as Item_a
from t


I read somewhere that it's not relevant to have more than 10 indexes

No hard-and-fast rules like that are generally applicable. If you have more than ten columns (or combinations of columns as an index can cover several) that are regularly searched by then you probably need more than 10 indexes.

The key problems with too many indexes which you need to consider are:

Space needed. If you have limited disk space then this can be an issue, but space is cheap these days. Remember to consider backup sizes as well as the size of your production databases.
Potential RAM use. Though this effect is usually small unless you have too little RAM to start with.
Insert and update speed, though if your table sees vastly more reads than writes (which is the most common use profile) then this is not generally significant either.

  how can I know if it's really usefull to define all these indexes?

Nothing beats application specific benchmarking for that sort of thing. You know how your application and its users hit the database (or if you don't, you need to study this (or apply some careful thought if the app isn't live yet so doesn't have real users)) so build some benchmarks based on that activity and see what difference it makes. Remember to test queries/updates that don't happen very often as well as those that do though - sometimes infrequent reports are the most important (i.e. in the app that I manage there are reports the users access once or twice per month, but while they aren't used often they are vital to their business and need to respond in reasonable time) so you don't want them timing out or simply taking hours to produce.


How to SUM values with same value by using WHERE IN()

The IN predicate will either evaluate to TRUE or FALSE (or NULL) exactly once for each row, so you won't get any multiplier effect from that. You can use a Common Table Expression (CTE, only supported in 8+) or a temporary table
CREATE TABLE vo_plurep
( ID_PLUREP int not null
, PRE_PRP int not null
);

insert into vo_plurep (id_plurep, pre_prp) values (1,1),(2,2);

CREATE TEMPORARY TABLE T (id_plurep int not null);

insert into T (id_plurep) values (1),(1);

SELECT SUM(PRE_PRP) AS IMPORTO
FROM vo_plurep
JOIN T
    ON T.id_plurep = vo_plurep.id_plurep

IMPORTO
2

Fiddle


One possibility (one that you feel intuitively, apparently) is to centralize your query into a function and disable querying otherwise.

Let's say the tables are owned by a role called owner, and there is a reader to run SELECT queries against the tables.  Then there is an application role which can connect to the DB and is able to call functions.  All this can be achieved by:

-- log in as a superuser, for example
CREATE ROLE owner;
CREATE ROLE reader;
CREATE USER application WITH PASSWORD 'a';

SET ROLE TO owner;

CREATE SCHEMA data;

CREATE TABLE data.important (i_id serial PRIMARY KEY);  -- will be owned by owner

GRANT SELECT ON data.important TO reader;

At this point, whoever can assume the reader role (by being a member and/or using SET ROLE) will be able to run any SELECT against the table.  Note that application doesn't have any rights here.

Then create another schema for your functions:

CREATE SCHEMA api; -- well, you can call it something else, too

RESET ROLE; -- to switch back to your superuser self

CREATE FUNCTION api.select_important(id integer)
RETURNS data.important
LANGUAGE sql
SECURITY DEFINER -- the important bit
AS $$
SELECT * FROM data.important WHERE i_id = id;
$$;

GRANT USAGE ON SCHEMA api TO application;
GRANT EXECUTE ON FUNCTION api.select_important(integer) TO application;

The SECURITY DEFINER clause will make the function inherit the rights of the superuser to read from the table (there is no need to grant it).  Now if you log in as application, you can call the function:

\c - application
Password for user application:

SELECT * FROM api.select_important(1);
 i_id
──────

(1 row)

However, you cannot read from the table directly:

my=> SELECT * FROM data.important WHERE i_id = 1;
ERROR:  permission denied for schema data
LINE 1: SELECT * FROM data.important WHERE i_id = 1;

Note: to spare GRANTs all the time you create a table or function, you can set default privileges.  See the bottom part of an earlier answer of mine about how to do that.


What's the difference between Object.bsonsize and avgObjSize returned by db.collection.stats?

My field avgObjSize was around 6442 bytes. I chose a random document in my collection, I typed :

  Object.bsonsize(db.collectionName.find({"_id" :         ObjectId("5508497c51a990da07b07106")}))

  And I obtained 810 bytes.

  Why do I have this huge difference ?

The db.collection.find() method returns a cursor, so what you have calculated is the size of the cursor object rather than the size of a document.

You should use db.collection.findOne() to return a single document if you want to measure the BSON size:

Object.bsonsize(db.collectionName.findOne(
    {"_id" : ObjectId("5508497c51a990da07b07106") }
))

  I also noticed that more I store documents, more avgObjSize decreases... Is that normal ? (I checked, my documents are not empty.)

The average is based on the size of the documents in the collection, so it sounds like your trend over time is to insert smaller documents. The normal behaviour depends entirely on your use case ;-)


Firebird execute block where External datasource is localhost

If I understand correctly from your comments, the execute block is executed on the external server, and it needs to query data from the local server.

The problem with your code - as far as I can tell - is that you seem to think the IP addresses are resolved from the perspective of the process initiating the execution (so 127.0.0.1 would be your local database). It is not, address resolution is from the perspective of the process executing the execute block (in this case: the Firebird process on the external server, so 127.0.0.1 is that external server).

You will need to replace 127.0.0.1 with the IP address of the local server for this to work. This IP address should be public, or at least accessible from the external server.

Be aware though, that if you expose both the local and external server to the public internet, that is a security risk. Consider using a VPN to connect between the local and external server(s).


Why does simple ALTER TABLE command take so long on table with full-text index?

The schema change it taking so long because you are assigning a default value to the column during the change and enforcing that with a non-nullable column, and it has to populate the column for 60+ million rows, which is an incredibly expensive operation.  I'm not sure what your application requirements are but an approach that would make the schema change faster is to add it in as a nullable column with no default value and then perform an update in batches to assign 0 as the value for the column.  After your update are done then you can apply another schema change to change the column to non-nullable and assign the default value.


Delete all duplicated entries where multiple columns duplicates match

The easiest way to remove duplicates is to join the table with itself:
DELETE z.*
  FROM kraken AS w
  JOIN kraken AS z ON z.exchange_pair = w.exchange_pair
                  AND z.timestamp = w.timestamp
                  AND z.id > w.id -- or what primary key you have
;

All duplicates will be deleted except one with the lowest id.


"Upsert" trigger writing mass duplicates from SSIS

I suspect the issue appears because you are referencing the view twice in the UPDATE statement, once in UPDATE [dbo].[vwMP_VOL] clause and then in FROM clause and the second time you are giving it an alias. But you are not using the alias in the SET clause, only the first reference.

The result is that the the two references are unrelated and the rows are updated with unrelated data (and possibly many times as well!)

See the  the official MSDN documentation about UPDATE and the caution:

  Best Practices

  ...

  Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

and a more detailed analysis of this "feature" and other issues it can cause, in Hugo Kornelis's blog post: Let's deprecate UPDATE FROM! and especially this paragraph:

  Correctness? Bah, who cares?

  Well, most do. That’s why we test.

  If I mess up the join criteria in a SELECT query so that too many rows from the second table match, I’ll see it as soon as I test, because I get more rows back then expected. If I mess up the subquery criteria in an ANSI standard UPDATE query in a similar way, I see it even sooner, because SQL Server will return an error if the subquery returns more than a single value. But with the proprietary UPDATE FROM syntax, I can mess up the join and never notice – SQL Server will happily update the same row over and over again if it matches more than one row in the joined table, with only the result of the last of those updates sticking. And there is no way of knowing which row that will be, since that depends in the query execution plan that happens to be chosen. A worst case scenario would be one where the execution plan just happens to result in the expected outcome during all tests on the single-processor development server – and then, after deployment to the four-way dual-core production server, our precious data suddenly hits the fan…

I think the best way to solve this is to use the alias in the UPDATE clause and change accordingly the SET clause:

UPDATE old
   SET
       [id_batch] = inserted.id_batch
      ,[id_invoice] = inserted.id_invoice
      ,[text_vchr] = inserted.text_vchr
      ,[amt_frt_tcur] = inserted.amt_frt_tcur
      ,[amt_line_hcur] = inserted.amt_line_hcur
      ,[line_total_hcur] = inserted.line_total_hcur
      ,[id_tax_1] = inserted.id_tax_1
      ,[id_tax_2] = inserted.id_tax_2
      ,[id_tax_3] = inserted.id_tax_3
      ,[id_tax_4] = inserted.id_tax_4
      ,[line_total_rem_hcu] = inserted.line_total_rem_hcu
      ,[id_po] = inserted.id_po
      ,[cnt_rlse] = inserted.cnt_rlse
      ,[cnt_line_nbr] = inserted.cnt_line_nbr
FROM inserted
          --   INNER JOIN (SELECT * FROM [dbo].[vwMP_VOL]) AS old
-- or the simpler
INNER JOIN [dbo].[vwMP_VOL] AS old
ON old.[Org] = inserted.Org
      AND old.[id_vend] = inserted.id_vend
      AND old.[id_org_orig] = inserted.id_org_orig
      AND old.[id_vchr_nbr] = inserted.id_vchr_nbr
      AND old.[id_vchr_line] = inserted.id_vchr_line;

This would work, too, but I don't like it as much as the previous because we can't alias the table:

UPDATE [dbo].[vwMP_VOL]
   SET
       [id_batch] = inserted.id_batch
      ---
      ,[cnt_line_nbr] = inserted.cnt_line_nbr
FROM inserted
WHERE [dbo].[vwMP_VOL].[Org] = inserted.Org
      AND [dbo].[vwMP_VOL].[id_vend] = inserted.id_vend
      AND [dbo].[vwMP_VOL].[id_org_orig] = inserted.id_org_orig
      AND [dbo].[vwMP_VOL].[id_vchr_nbr] = inserted.id_vchr_nbr
      AND [dbo].[vwMP_VOL].[id_vchr_line] = inserted.id_vchr_line;


Trying to read a log file encrypted by TDE

Do you have database audit in place? temporary tables? If you have one of these solutions in place, it can be done relatively easy.

If no, you can use undocumented functions fn_dump_dblog() to analyze log backup contents.

hope this helps!


My only suggestion is to create stored procedure that performs DML on tag_bundle_relation and restrict all users from manipulating the table directly (it's in a sense 'explicit lock' as you say).  I don't see any other acceptable ways to enforce the required restriction. In my understanding your simple solution just adds complexity to the system - you will need more triggers to synchronize tags field with tag_bundle_relation and tag tables. Also, you may get false fail if you are adding records to tag_bundle_relation one by one - for instance, inserting tag_bundle_id=3 with tag_ids 100,101,102 will fail after attempting to insert 101 (assuming 100 already inserted).


Weird SQL Server instance crash on casting to numeric

I was able to repro on 2008 R1 SP3 10.00.5512 but installing the latest CU (14) fixed it.

Reviewing the bugs fixed in the intervening versions it looks as though you need to upgrade to a build that includes the following fix.

Access violation when you run a query that contains many constant values in an IN clause in SQL Server 2008 or in SQL Server 2012

As you are on 2008 R2 you will need at least CU 9 for SP1 or CU 5 for SP2.

The description of symptoms is somewhat brief but mentions mismatched datatypes

  When you run a query that contains many constant values in an IN
  clause in Microsoft SQL Server 2008, Microsoft SQL Server 2012 or in
  Microsoft SQL Server 2008 R2, an access violation might occur.

  Note For the issue to occur, the constants in the IN clause cannot
  match exactly with the column data type.

It doesn't define "many". From the testing I did I suspect this may mean "20 or more" as this seems to be the cut off point between two different methods of estimating cardinality.

The crash was happening inside a couple of methods called by CScaOp_In::FCalcSelectivity() with names such as LoadHistogramFromXVariantArray() and CInMemHistogram::FJoin() -> WalkHistograms().

For 19 or fewer distinct in list items these methods weren't getting called at all. A similar SQL Sever 2000 bug also mentions this cut off point as significant.

Populating a test table with 100,000 rows of random test data with values between 0 and 1047 and a histogram starting as follows

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
|            0 |          0 |     104 |                   0 | 1              |
|            8 |        672 |     118 |                   7 | 96             |
|           13 |        350 |     118 |                   4 | 87.5           |
|           18 |        395 |     107 |                   4 | 98.75          |
|           23 |        384 |      86 |                   4 | 96             |
|           28 |        371 |      85 |                   4 | 92.75          |
+--------------+------------+---------+---------------------+----------------+

The query

SELECT * FROM dbo.[TestTable]
where mpnr in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)
option (maxdop 1)

Shows estimated rows of 1856.

This is exactly what would be expected by getting the estimated rows for the 19 equality predicates individually and adding them together.

+-------+----------------+-------+
| 1-7   | AVG_RANGE_ROWS | 96    |
| 8     | EQ_ROWS        | 118   |
| 9-12  | AVG_RANGE_ROWS | 87.5  |
| 13    | EQ_ROWS        | 118   |
| 14-17 | AVG_RANGE_ROWS | 98.75 |
| 18    | EQ_ROWS        | 107   |
| 19    | AVG_RANGE_ROWS | 96    |
+-------+----------------+-------+

7*96 + 118 + 4*87.5 + 118 + 4*98.75 + 107 + 1*96 = 1856

The formula no longer works after 20 is added to the in list (Estimated rows 1902.75 rather than the 1952 that adding another 96 to the total would generate).

BETWEEN seems to use yet another method of calculating cardinality estimates.

where mpnr BETWEEN 1 AND 20 estimates only 1829.6 rows. I've no idea how that is derived from the histogram shown.


SQL Server Management Studio for a remote database

The way that I know of to do this is to establish a VPN connection to the other site, thus eclipsing the need to open a port and maintaining the security of the setup, while eliminating the need to RDP into the other server. Granted, you run the risk of dropping a connection while you're working, thus possibly ending queries prematurely, but otherwise ... it's what we do and it works well for us.

If you're going to use them on a regular basis remotely you may look into a semi-permanent VPN connection (I'm sure there's a better name .. VPN bridge?) that will allow you to just connect by IP address. A networking guy can weigh in better here.


Improve performance by combining two quieries

Can it be?

select distinct s2.user_name
from submission s1
  join submission s2
    on s1.md5 = s2.copy_of
    or s1.copy_of = s2.copy_of
    or s1.copy_of = s2.md5
where s1.user_name = ?1
 and s2.copy_of is not null;

s1.md5 = s2.copy_of will match submission which is copy from this user.
s1.copy_of = s2.copy_of or s1.copy_of = s2.md5 will match submission which current user copy from.


Are PostgreSQL 10.x identity columns inherently indexed? unique?

Are PostgreSQL 10.x identity columns inherently indexed?

No

  unique?

No

  Or do I need to add a (unique) index to the column as desired?

You don't have to, if you desire it you can. Or just declare the IDENTITY COLUMN to be a PRIMARY KEY (as they're often used). All PRIMARY KEYs are UNIQUE and indexed.

See also

What are Identity Columns?


How to break down MySQL big table into multiple to solve operational issue

There are other things you could and should do, also.

Normalization.  For example, how many different values are there of GROUP_ID or SYSTEM_ID?  Rather than using a string, use a TINYINT UNSIGNED -- 1 byte and a range of 0..255, or maybe a SMALLINT UNSIGNED -- 2 bytes and 0.65K.  Etc.
Are any of the columns "rare"?  I mean are they NULL in many of the rows?  If so, design the split so that a new row is not a full 4 billion rows, but, instead, has only the needed rows.  Then use LEFT JOIN when you need to get NULLs back.
How were the UUIDs generated?  If they are standard, it is simple to compress down to BINARY(16) (16 bytes) from 36 bytes.
Don't blindly use (255), find a sensible, but conservative limit.  (This has a small impact on performance.)
What are the main queries now?  And after moving to 3 tables?  We should make sure the indexes are 'right' for all of them.  If the split is done 'wrong' certain queries will become much slower and cannot be fixed by indexes.
DETAILS takes about 2.5TB?  Probably that will shrink to less than 1TB if you compress it.  But do it in the client, not the server.  And then put the result in a MEDIUMBLOB.  And uncompress in the client after fetching.
Consider PARTITION.  It is unlikely to help anything.  However, if you purge "old" data, the process is much faster than DELETE.  It will not help with ALTER TABLE.
What do you use ALTER for?  There are some cheap tricks to perform for some tasks.
The PK being varchar(255) sounds awful; let's discuss alternatives.  Does any combination of other columns provide a unique key?
Do SELECT AVG(LENGTH(...)), AVG(LENGTH(...)), ... FROM t to see what is likely to be the benefit from each column.

"Alter is used mainly for adding column and adding index" --

MySQL 8.0 has faster ALTER operations.  Some are "INSTANT".  You need to look carefully at the details to see which are fast versus which need a copy.  And some flavors block other actions more than others.
If the only goal is to add a new column, consider adding a new table instead.  This is similar to your original idea but without any of the downtime.  It can also take advantage of having rows only where necessary (see my LEFT JOIN comment above).
But...  If you need to search the new set (with multiple tables), some queries can be efficient, some will be terribly inefficient.  Please provide some concrete examples of what you have done in the past or might do in the future; I will explain in more detail.  Perhaps start a new Question and show the old CREATE TABLE and the new one, together with the SELECT that involves the new column; then ask about performance.


Why is an aggregate query significantly faster with a GROUP BY clause than without one?

It looks like it is probably following an index on CreatedDate in order from lowest to highest and doing lookups to evaluate the SomeIndexedValue = 1 predicate.

When it finds the first matching row it is done, but it may well be doing many more lookups than it expects before it finds such a row (it assumes the rows matching the predicate are randomly distributed according to date.)

See my answer here for a similar issue

The ideal index for this query would be one on SomeIndexedValue, CreatedDate. Assuming that you can't add that or at least make your existing index on SomeIndexedValue cover CreatedDate as an included column then you could try rewriting the query as follows

SELECT MIN(DATEADD(DAY, 0, CreatedDate)) AS CreatedDate
FROM MyTable
WHERE SomeIndexedValue = 1

to prevent it from using that particular plan.


SP to return last ID and insert into a column using a trigger

Based on the limited information in your question, I made a few changes to your stored procedure.  If I've misunderstood something, let me know.  This code was not fully tested on my side.

ALTER PROCEDURE [dbo].[SP1_Test] @Opp_Id VARCHAR(24)
    ,@Opp_Number INT
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @NewQno VARCHAR(50)

    --The Qno column will contain the current value, whatever that may be
    INSERT INTO O_QUDF (Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
    SELECT @Opp_Id,@Opp_Number,1,0,Qno
    FROM T1 WHERE ID = 1;

    --While you can probably get away with doing simple arithmetic on NVARCHAR columns,
    --I think it's better to implicitly convert it to a numeric data type to do the arithmetic.
    --I chose Integer.
    --Then, you need to convert it back to nvarchar for the upcoming UPDATE
    SELECT @NewQno = Convert(NVARCHAR(50), Convert(INT, Qno) + 1)
    FROM T1 WHERE ID = 1;

    UPDATE T1 SET Qno = @NewQno WHERE ID = 1
END
GO


What is the meaning of "-x-icu" in PostgreSQL's "collates"?

The documentation, 23.2. Collation Support / 23.2.2.2.2. ICU Collations , states:

  Collations provided by ICU are created in the SQL environment with names in BCP 47 language tag format, with a “private use” extension -x-icu appended, to distinguish them from libc locales.

ICU is the "International Components for Unicode", which, according to their homepage, is:

  ICU is a mature, widely used set of C/C++ and Java libraries providing Unicode and Globalization support for software applications. ICU is widely portable and gives applications the same results on all platforms and between C/C++ and Java software.

  ICU is released under a nonrestrictive open source license that is suitable for use with both commercial software and with other open source or free software.

Unicode is a specification, not software, hence for many years each vendor has implemented it on their own and thus there is not nearly as much consistency anyone working with collations would appreciate. The ICU project is, quite fortunately, being implemented by more and more vendors, which is improving Unicode support and consistency. If available, I would expect the ICU collations to be a better choice than non-ICU collations.


MySQL InnoDB Reset Existing Auto-Increment Records

TRUNCATE TABLE tbl;

If that does not work,

ALTER TABLE tbl AUTO_INCREMENT=1;

But you should not depend on the actual values of AUTO_INCREMENT.  There are many things that can make it non-reproducible.


What does below parallel processing execution plan mean?

The two worker processes are additional to the session backend process, which is the “parallel leader”, so there are three processes working on the scan.


Modify a value inside of a nested dynamic column

After MUCH trial and error... This is the structure required to modify an item nested in a dynamic column.

UPDATE item SET attr=COLUMN_ADD(attr, "level1", COLUMN_ADD(COLUMN_GET(attr, "level1" as blob), "level2", "NEWVALUE")) WHERE id=1;

Which turns;

  { "level1" : { "level2" : "level2_value" } }

INTO

  { "level1" : { "level2" : "NEWVALUE" } }


The syntax for taking MySQL dump:

-- Dump all databases and all tables (option "-A" is similar to "--all-databases")
mysqldump -u [username] -p[user_password] -A > [dump_filename]
-- Example, please note (NO SPACE between -p and your password)
mysqldump -u root -pmypass -A > full_dumb.sql

-- Dump single database
mysqldump -u [username] -p[user_password] [database_name] > [dump_filename]
-- Example
mysqldump -u root -pmypass dbshopping > dbshopping_dumb.sql

UPDATE:

-- MySQL Enterprise Backup Syntax
mysqlbackup --user[username] --password=[user_password] --backup-dir=[backup_directory] --databases=[database_list] backup

-- Example Command
mysqlbackup --user=root --password=mypass --backup-dir=G:\mysql-enterprise-backup-directory --databases=dbshopping backup

I'm not an expert in 'MySQL Enterprise Backup', yet It worked fine for me...

  You can also verify backup history and/or progress from 'mysql'
  database

SELECT * FROM mysql.backup_progress;
SELECT * FROM mysql.backup_history;

Hope this may help you...


Go to Command Prompt and Try Start Service With This

NET START MSSQLServer /f /T3608

Then in Command Prompt Login With SQLcMD

SQLCMD -E  -S ServerName

Then run Following Command to move the model log to the F drive.  Make sure the path is correct.

ALTER DATABASE Model MODIFY FILE( NAME = modellog , FILENAME = 'F:Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf' )
Go

Then stop and re-start your instance.

Repeat with other files if needed.  Except master.  That is a whole different problem.


We just had a similar problem and noted that we could use UNC-pathes to specify the source of a restore.

The restore is done by some account with rather restricted permissions and therefore it does not see the drive mappings you see.

To make it work in domain context, allowing read access to the backup file to the computer.

In other cases, when the machines aren't in the same domain giving read permission to the file to everyone might be the last resort.

Edit:

Good to know that there is a build in option to add backup devices.
For DBAs it might be the preferred way, developers in restricted environments have sometimes to try other options depending on the rights they where given.

For the German version of SSMS. You find the tool mentioned by mKorbel at

SQL Server Management Studio -> Server Objects -> Sicherungsmedien -> Neues Sicherungsmedium...


Transferring grants from one mysql DB to another

An easier way is to use pt-show-grants.

On source server:

$ pt-show-grants -uroot -p > grants.sql

Destination server:

$ mysql -uroot -p < grants.sql
$ mysql -uroot -p -e "FLUSH PRIVILEGES;"


connect to psql and run SQL command from command line

You don't need the \c in the command specified with -c, you can specify the database as a parameter to psql:

psql -U <username> -d some_db -c "DELETE from some_table; DELETE from another_table"


SQL Server Master Data Services database history by snapshots

Snapshots do have some performance overhead. With a dozen snapshots you could have up to 12 additional pages written to disk for each page written to the main DB, depending on your write patterns.

Consider what you want to do with these snapshots.  If they're simply for putative future auditing then do a backup and get them off your production server.  You can restore them when they're needed.  Ad hoc analysis?  best not done on the production server (and engaging the live DB) either.  Disaster recovery?  DB snapshots do not give you that.  To revert to in the event of a mistake?  You'll be rolling back a lot of other work, too, which would have to be re-done.  Then-versus-now analysis.  Hmmm, maybe.

In short, a "proper" backup, restored as an independent database and only db_reader granted will be better in almost every way.


The SQL server Log folder is expanding because of the SQL Dump files (what to do with it ?)

First of all, everything that Shanky said in his answer is 100% correct.  I would like to add:

The age of the files - If your log folder has several dumps from two years ago, then no dumps for several months, then a few recent dumps, you can safely delete the two year old dumps.
Duplicate dumps - When dumps occur there are three files created: .txt, .log & .mdmp.  Open the .txt file for several of the dumps.  If they are different, keep the dumps.  If they are the same, delete the old ones.
Your available disk space - If the disk where your dumps are accumulating is also the same disk where your database files or transaction log files also reside, and you are going to run out of disk space if you don't do something, and you have no where to move the dump files to, then by all means, delete them.
Types of dumps - If the .log file indicates "Non-yielding Scheduler",  "Non-yielding IOCP Listener" or "Non-yielding Resource Monitor", these are performance related specific to CPU.  You can troubleshoot those following the steps in this blog:  https://blogs.msdn.microsoft.com/karthick_pk/2012/08/21/non-yielding-iocp-listener-non-yielding-scheduler-and-non-yielding-resource-monitor-known-issues-and-fixes/

If the .log file indicates "Deadlocked Schedulers", this is also performance related specific to CPU.  You can troubleshoot those following the steps in this blog:  https://blogs.msdn.microsoft.com/karthick_pk/2010/06/22/how-to-analyze-deadlocked-schedulers-dumps/

Corruption Dumps - Just as the first answer states, run DBCC CHECKDB.  If the output from DBCC CHECKDB indicates corruption in your indexes, rebuild them.  If it indicates that "repair allow dataloss is the minimum" necessary to repair the database, then restore from last known good backup.
AV Dumps - You can attempt to tackle this yourself:  https://blogs.msdn.microsoft.com/sqlcat/2009/09/11/looking-deeper-into-sql-server-using-minidumps/
If you run into difficulty, open a case with Microsoft, but just as Shanky said, ensure you are on a supported version and you have the latest updates.


RDS MYSQL instance rebooted and won't load INNODB table

You need to open the case with AWS Support team here. As only they have direct access to the server consoles and only they can help you to perform required operations as suggested in the output of attempt to recreate table.

Alternatively you can create the table with new name, as a possible workaround for the problem.


Made changes to my.cnf and mysqld wont restart - no backup

atadir=/var/lib/mysql

should be

datadir=/var/lib/mysql


You may be referring to a Federated Database System.


SQL Server 2008 R2, error creating file for FileStream

Turned out to be an HBSS issue.  Typically, I would have had this turned off during install but the environment I'm working with is particularly secured where you can't have HBSS set to "learning mode."

it turns out that filestream creates a registry key for each directory created and these exceptions have to be in place before creating the filestream enabled "file".

HBSS stands for Host Based Security System. Ours is a McAfee product. It's an incredibly powerful tool that is unrelated to SQL server, but on systems that require it, you will encounter strange problems like this with little to no explanation.


Is VACUUM FULL necessary on a DROP TABLE / TRUNCATE approach for a datawarehouse

Point about TRUNCATE/COPY

Unrelated note, depending on the WAL level, wrapping the TRUNCATE and COPY in the same transaction things may go faster, because WAL will be skipped. Further the CTAS will always skip most WAL.

  In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7). Operations in which this optimization can be applied include
  source

  CREATE TABLE AS
  CREATE INDEX
  CLUSTER
  COPY into tables that were created or truncated in the same transaction

    BEGIN;
      TRUNCATE source_table1
      COPY source_table1 FROM [...]
    COMMIT;

Issue of VACUUM FULL

There is no need to do a VACUUM FULL on a new table. The table is already new in those transactions so there is no need to rewrite it, as FULL does. There is also no need to VACUUM it because there are no movable rows that VACUUM will act on. When you run VACUUM FULL VERBOSE, you can see nothing was removable, and no dead rows were removed. VACUUM FULL can be useful if

you're staging INSERTS though a temp table, and adding them in batches into a dirty table. Then it can often pay off at the end of the process.
you're running UPDATE on a large batch because UPDATE generates new rows forcing VACUUM to later mark the old rows as dead.

A simple ANALYZE will work fine. This will update the statistics on the table.


Is there a standard approach to rolling out database schema changes?

There's no standard process because every system is different.  About the last thing that I would do if just wrap everything in a single transaction.  What happens if I need to move 500 Gigs of data around?  That's one massive transaction.

Recently I've been using database snapshots as my rollback.

Basically take a snapshot, make the changes.  Delete the snapshot after signoff.  If upgrade failed roll back the snapshot and then try again.

It's a lot quicker to roll back a snapshot than to restore the database (assuming that the database is large).


Execute SSIS procedure from stored procedure using non-privileged SQL login

As far as I know session started with SQL Server authentication will not let you create security context outside of SQL Server (because the only external context available is SQL Server service account and EXECUTE AS will not let you impersonate SQL Server service account for external access).
SSISDB procedures has to have context outside of SQL Server, so session has to be started using Windows Auth.
You have few options, but all needs more changes that you mentioned in the email:
1. I think easiest: create SQL Agent job wrapper.
2. Create SQLCLR stored procedure creating new callback session (it will use service account = sysadmin, I would say too privileged).
3. Enable Service broker service and use activation to start SSIS.
4. Publish SSIS package as a view and let your app consume the view.
5. I didn't try this, but worth trying to use a loopback linked server.


Is SERVICE_NAME forced to be unique per logical database within a network? Would it have sense the opposite?

The same SERVICE_NAME can run in multiple instances with a RAC, and in a physical standby database, those services also exist.  In fact, you'd need them to be running in order for failover to work.

Service name from a uniqueness perspective only matters in terms of being unique within a particular listener's purview; it's how the listener routes connections.  What would happen if multiple unique databases served by the listener each registered the same service?  How would it choose between/among them?


The first query will be faster this way:

select * from `entries`
    where EXISTS (
        select * from `users` where `entries`.`profile_id` = `users`.`id`
                 )
order by `id` desc

It needs an index (PRIMARY KEY?) on users.id.  It is faster because the subquery quits when finds one row, rather than counting all of them.

The query part cannot use that technique.  But I think it is much simpler than what you have:

SELECT  e.*
    FROM
      ( SELECT  user_id
            FROM  `tag_user`
            WHERE  tag_id IN (119, 36)
            GROUP BY  user_id
            HAVING  count(*) = 2
      ) x
    JOIN  entries AS e  ON e.profile_id = x.user_id

Notice that users does not seem to be of any use.

entries will need INDEX(profile_id).

I assume tag_user is a many-to-many mapping table?  Have you followed all the advice in this?  The advice there may provide further performance boost.

If you need to discuss this further, please provide SHOW CREATE TABLE.


You should check that there isn't any empty column: <> ''

SELECT Name,
       Phone,
       Email
FROM   YourTable
WHERE  Name <> ''
AND    Phone <> ''
AND    EMail <> '';