Tiny table causes extreme performance degradation, fixed by forced VACUUM. Why?

The table may be small, but as long as Postgres expects roughly 0 rows, chances are it is going to chose a different query plan than for roughly 40 rows - for which the same query plan is not as efficient.

Since joins multiply result rows rather than just adding to them, the 40 rows in the tiny table can have a massive effect when joined to big tables with several million rows like in your example. The difference can easily explain a factor 30 in execution time.
Or as the manual puts it:

  It is important to have reasonably accurate statistics, otherwise poor
  choices of plans might degrade database performance.

Default autovacuum settings are ok for most installations. Consider:

Are regular VACUUM ANALYZE still recommended under 9.1?

But for a database holding multiple tables with several millions rows, I would consider tuning per-table settings for selected tables and a manual ANALYZE on the whole DB from time to time.

The remaining questions

Q1. Why did autovacuum not launch ANALYZE automatically?
Q2. Why did VACUUM FULL fix the problem?

Q2 is simple: While other important statistics are only updated by ANALYZE, the basic count estimate in pg_class.reltuples is updated more often. The manual:

  Number of rows in the table. This is only an estimate used by the
  planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such
  as CREATE INDEX.

Q1 is more sophisticated.

The manual again:

  The daemon schedules ANALYZE strictly as a function of the number of
  rows inserted or updated; it has no knowledge of whether that will
  lead to meaningful statistical changes.

Relevant settings (among others):

  autovacuum_analyze_threshold (integer)

  Specifies the minimum number of inserted, updated or deleted tuples
  needed to trigger an ANALYZE in any one table. The default is 50
  tuples. This parameter can only be set in the postgresql.conf file or
  on the server command line; but the setting can be overridden for
  individual tables by changing table storage parameters.

  autovacuum_analyze_scale_factor (floating point)

  Specifies a fraction of the table size to add to
  autovacuum_analyze_threshold when deciding whether to trigger an
  ANALYZE. The default is 0.1 (10% of table size). This parameter can
  only be set in the postgresql.conf file or on the server command line;
  but the setting can be overridden for individual tables by changing
  table storage parameters.

Bold emphasis mine.

Demo

Be sure the test DB is mostly idle to avoid testing artifacts and you are running with default settings:

SELECT * FROM pg_settings WHERE name ~ '^autovacuum|track_counts';

Most importantly:

autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50
autovacuum_naptime = 60
track_counts = on

Basically, autovacuum checks once every minute whether any table has last_estimate / 100 + 50 rows changed and launches ANALYZE for those.

To understand what happened in your case:

CREATE TABLE t50 (id int primary key, foo text);
INSERT INTO t50 SELECT g, 'txt' || g FROM generate_series(1,50) g;
SELECT reltuples FROM pg_class WHERE oid = 't50'::regclass;

pg_class.reltuples is the estimated row count of the table. More here:

Fast way to discover the row count of a table in PostgreSQL

You'll get 0. Wait for 2 minutes to make sure we cross the 1 minute delay. Check again. Still 0. Now insert one more row and check again:

INSERT INTO t50 VALUES (51, 'txt51 triggers analyze');
SELECT reltuples FROM pg_class WHERE oid = 't50'::regclass;

Still 0. Wait another 2 minutes, check again. Tada! We see the updated count of 51. Autovacuum did not kick in until 51 rows were inserted (or updated or deleted).

To see more details (including the timestamp of the last_autoanalyze):

SELECT * FROM pg_stat_all_tables WHERE relid = 't50'::regclass;

Related:

When is Postgres autovacuum executed

Solution

Run ANALYZE on public.clients manually once (or on the whole DB, it's cheap) and use more aggressive per-table autovacuum settings for this important table. Like:

ALTER TABLE public.clients SET (autovacuum_analyze_scale_factor = 0.01
                              , autovacuum_analyze_threshold = 10);

You might also want to audit the settings for some of your big tables for other reasons. Compare:

Debug query on big table that is sometimes slow

Also important

You are joining 17 tables, which is well beyond the default setting for join_collapse_limit which is 8. You may want to use explicit join syntax (maybe you already do) and rewrite your query to place the most selective tables (or the ones with the most selective predicates) first in the SELECT list. Related:

Complex view becomes slow when adding ORDER BY in outer query with small LIMIT
Can we execute an optimal plan instead of a generic one in the first execution of a PL/pgSQL function?

P.S.:
I think I found a minor documentation bug while running the test above. The manual on autovacuum_analyze_threshold reads:

  Specifies the minimum number of inserted, updated or deleted tuples
  needed to trigger an ANALYZE in any one table

This would indicate that 50 inserts trigger ANALYZE, not 51 like I observed. Similar in pg_settings.short_desc:

  Minimum number of tuple inserts, updates, or deletes prior to analyze.

In fact, the explanation for autovacuum in the manual here matches my observation:

  Otherwise, if the number of tuples obsoleted since the last VACUUM exceeds the "vacuum threshold", the table is vacuumed.

The first two sentences seem slightly incorrect.
I filed a bug report.


conditional/processing on the dummy data column to create new dummy column

with the help of CTE

;WITH CTE AS (Select [A],[C]
      ,CASE WHEN [A]>19999 and [A]<30000 THEN '12' ELSE '21' END AS 'dummy1'
    ,Substring(CAST(C AS varchar(9)),1,4) as YEARDummy
    ,Substring(CAST(C AS varchar(9)),5,2) as MONTHDummy
    ,Substring(CAST(C AS varchar(9)),7,2) as DAYDummy
    --,CASE WHEN MONTHDummy>0 and DAYDummy<31 THEN '30dayas' ELSE 'xyz' END AS Dummy4
From TableYearlyData)

-- here you can apply your CASE with YEARDummy,MONTHDummy,DAYDummy column
SELECT *,CASE WHEN MONTHDummy>0 and DAYDummy<31 THEN '30dayas' ELSE 'xyz' END AS Dummy4
FROM CTE


You must ensure that both combinations exists.

-- I'd like all samples that have labels of "narf=zord" AND "foo=bar"
-- AND l.key = 'foo' AND l.value = 'bar'
WITH pairs AS
(
    SELECT s.id AS sample_id, s.prefix AS sample, l.id AS label_id, l.key, l.value
    FROM   samples s
    JOIN   sample_labels sl ON (s.id = sl.sample_id)
    JOIN labels l ON (sl.label_id = l.id)
)
SELECT p.sample_id, p.sample, p.label_id, p.key, value
FROM   pairs p
WHERE  EXISTS (SELECT 1
               FROM   pairs
               WHERE sample_id = p.sample_id and key = 'narf' AND value = 'zord')
AND    EXISTS (SELECT 1
               FROM   pairs
               WHERE sample_id = p.sample_id and key = 'foo' AND value = 'bar');

sample_id | sample              | label_id | key  | value
--------: | :------------------ | -------: | :--- | :----
        1 | narf=zord & foo=bar |        1 | narf | zord
        1 | narf=zord & foo=bar |        2 | foo  | bar

dbfiddle here


OK i have found the issue. On the select statement at the beginning of the loop i have a select statement that is there to see if we run the insert statements. This statement will some times set a null value to the variable. this seems to be the reason that we are leaving the loop early. I have added this statement into the initial result set pull and it is working well now.


MySQL Character set and Collation Issue.?

Based on your default database encoding, character_set_database=latin1, you should force your client to use latin1.  Your client is currently in utf8 and the database appears to be in latin1.  This assumes that your table is also using latin1.

To make sure your data is stored correctly in MySQL, you can force the mysql client to use latin1.

1 Force the Client to use latin1

mysql -u root -p --default-character-set=latin1 nameofthedatabase

Then run your SELECT statement to verify the character formatting

select budgetID,StartDate,modifiedBy from Table_name order by budgetID desc limit 10

2 Backup the database using latin1

You can also dump the database using the same option

mysqldump -u root -p --default-character-set=latin1 nameofthedatabase > nameofthedatabase.sql

Then you could view the dump file nameofthedatabase.sql using a standard text editor like textpad or gedit to see if the characters are encoded correctly.

Collation is a set of rules that determine how rows are sorted and how they are compared.  Collation does not affect the encoding which is the problem you are currently having.

If both options still give you weird symbols, the import file may not have been correctly formatted in the first place.


Move result of a query from one server to another table on another server

The simplest method  which  we use is something like below

Create a table on destination server using the scripts generated from the table on the source server by right click and going through option of Tasks/Generate scripts.
Then run the below T-SQL to upload the data from source table to destination.

INSERT INTO dbo.YourTableNameHere
SELECT *
FROM [SourceServer].[SourceDatabase].dbo.YourTableNameHere

For large data movement, refer to below discussion and answer for same:

Copy a large data from sql query result


You have it almost right, by grouping by start it would only group the same date and time, but when you group by time_label, it takes the individual days with out the time

SELECT
    DATE_FORMAT(start, '%Y-%m-%d') AS time_label,
    AVG(TIME_TO_SEC(TIMEDIFF(end, start))) AS value
FROM
    timetable
GROUP BY time_label

Result

| time_label | value |
| ---------- | ----- |
| 2019-10-14 | 13710 |
| 2019-10-16 | 7966  |

View on DB Fiddle


AT TIME ZONE direction changes based on column having a timezone

Yes! it does!

The syntax to convert from timestamp with timezone (AKA timestamptz) to timestamp without timezone (AKA timestamp) is identical to the syntax to do the opposite transformation.

So, depending on which way you are converting the value you will see the printed value change in the corresponding direction.

You have a column called admission_at of type timestamp
when you say admission_at AT TIME ZONE 'GMT+2' postgres interprets that to mean treat the values in  admission_at as-if they are in the zone 'GMT+2' and produce a result of type timestamptz

If you actually want to treat the admission_at values as GMT timestamps and display the apropriate Johannesburg wall time, convert them first to
timestamptz using the GMT rules and then back to timestamp using the Johannesburg` rules.

SELECT
   admission_at AT TIME ZONE 'gmt' AT TIME ZONE 'gmt+2' AS gmt_plus_2,
   admission_at AT TIME ZONE 'gmt' AT TIME ZONE 'Africa/Johannesburg' AS africa_jnb,
   admission_at AT TIME ZONE 'gmt' AS gmt
   admission_at AS naieve
 FROM admissions WHERE admission_at::date = '2018-06-11'
 ORDER BY admission_at LIMIT 3;


Update one column based on count of records of grouped value in another column

CREATE TABLE TABLE_ITEMS (
    serial_num int null,
    item_group int null
 )
 GO

INSERT INTO TABLE_ITEMS
VALUES
(0,1), (1,1), (2,1),(3,1) ,(4,1) ,(0,2) ,(1,2), (2,2), (3,2), (4,2), (5,2), (0,3) ,(1,3) ,(2,3)
GO

SELECT
    Item_group,
    ROW_NUMBER() OVER (PARTITION BY item_group order by item_group) AS AddNum
INTO #NewSerials
from TABLE_ITEMS
WHERE serial_num <> 0

SELECT * FROM TABLE_ITEMS

;WITH CTE AS
(SELECT MAX(addnum) as addnum, item_group from #NewSerials group by item_group)
UPDATE ti
    SET ti.serial_num = CTE.AddNum
FROM TABLE_ITEMS ti
JOIN CTE ON CTE.item_group = ti.item_group
WHERE ti.serial_num = 0

SELECT * FROM TABLE_ITEMS

5   1
1   1
2   1
3   1
4   1
6   2
1   2
2   2
3   2
4   2
5   2
3   3
1   3
2   3


Why query_cache_type is disabled by default start from MySQL 5.6?

You need the history of InnoDB to understand why. Here it goes:
WAR STORY
InnoDB and the query cache are in a constant state of war. InnoDB tends to be very heavy-handed when inspecting changes in the InnoDB Buffer Pool and then crosschecking the Query Cache for the same changes.
PEACE TREATY
Before MySQL 5.0, the query cache was disabled for InnoDB. Now, InnoDB interacts with it. To simplify matters, you can just disable the Query Cache by setting the query_cache_size to 0.
According to the MySQL Documentation on query_cache_time

If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.

TERMS OF SURRENDER
Setting query_cache_size to 0 is not a one-size-fits-all solution.
The reason for the war, in the first place, is overhead. InnoDB will always inspect changes. A bigger query cache will make InnoDB work that much harder. Disabling the query cache let's the InnoDB and Query Cache be happy. However, you (the Developer/DBA) might be a casualty of that war by means bad query performance, even with such a peace treaty in place.
Depending on the following

Workload
Frequency of Changes
Frequency of reading the same data

you should set query_cache_size to whatever number you feel increases performance (This being tantamount to starting an underground movement).
EPILOGUE
In case you are wondering where I came up with this war story, please see my old post

Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it?

Read it carefully because I learned this from Pages 209-215 of High Performance MySQL (2nd Edition)
I have recommended disabling the query cache to others before

Sep 25, 2013 : invalidating query cache entries(key)
Sep 26, 2013 : query cache hit value is not changing in my database
Dec 23, 2013 : MySQL with high CPU and memory usage

NOTE : I realize the question was about the query_cache_type. It does have an effect on the query cache. Disabling the cache squashes InnoDB's dominance over it. Setting the query_cache_type manually simply forces the Developer/DBA to think carefully about the type of queries the query cache will encounter.


no pg_hba.conf entry for replication connection from host

When doing this:

$ psql -h $MASTER_PORT_5432_TCP_ADDR -U replication
psql: FATAL:  no pg_hba.conf entry for host "$SLAVE_PORT_5432_TCP_ADDR", user "replication", database "replication", SSL off

Since there's no explicit database indicated with -d (for example -d postgres ), it takes replication as the database, the same as the user name. However that's probably not what you want. replication is not supposed to be a real database, as mentioned in the doc for pg_hba.conf:

http://www.postgresql.org/docs/static/auth-pg-hba-conf.html

  The value replication specifies that the record matches if a
  replication connection is requested (note that replication connections
  do not specify any particular database). Otherwise, this is the name
  of a specific PostgreSQL database.

So if you expected that this line:

host    replication     ${REP_USER}     0.0.0.0/0               md5

would allow psql to connect with the command above, it's normal that it doesn't work, even assuming that ${REP_USER} is actually replication.

As for the other line:

host    ${DB_NAME}      ${DB_USER}      0.0.0.0/0               md5

not knowing what are the real values for ${DB_NAME} and ${DB_USER} it's not really possible to comment on it.

If the real problem is the replication not happening, I don't think psql is very useful to test this since psql must connect to a specific database before doing anything useful, whereas the replication process must not and does not.


How do I create a partition using a bit field in SQL Server 2014

To get partition elimination, your queries need to be explicit about the bit data type, for example:

...WHERE Archivable = CONVERT(bit, 0)
-- NOT
...WHERE Archivable = 0

Without this, the risk of truncation in the implicit conversion means partition elimination is not applied.

The implicit conversion in the CREATE PARTITION FUNCTION statement is not a contributing factor, because SQL Server will do the conversion to bit for you, once, when the function is created. Still, it would be nicer/better practice to write:

CREATE PARTITION FUNCTION [Archived](bit)
AS RANGE LEFT
FOR VALUES
(
    CONVERT(bit, 0),
    CONVERT(bit, 1)
);

It might be easier all round to avoid these potential issues by making the Archived column an integer instead, with a CHECK constraint to enforce the allowed values.


Attempt to add DQS Cleansing Connection Manager to SSIS results in error

Open the SQL Server Data Quality Services client and attempt to
connect to the server with it.  You can sometimes get a better
explanation of a problem from the DQS Client than the SSIS
component.
If unsuccessful, run 'DqsInstaller.exe -upgrade'


Dataguard the datafiles are created in another diskgroup

OK, resuelto, el problema era el parametro 'db_create_file_dest' que al tenerlo definido en SYS por defecto creaba los datafile en dicha ruta. Espero a alguien le hay sido de utilidad esto.


What could cause an auto increment primary key to skip numbers?

There are many things that can cause gaps in an IDENTITY column (rollbacks, deletes), but in this case due to the jump I suspect it is this bug - caused by the changes to IDENTITY with the introduction of SEQUENCE:

Connect # 739013 : Failover or Restart Results in Reseed of Identity

So I bet that if you look in SQL Server's error logs, the rows associated with these jumps were inserted shortly after a SQL Server service restart, a database was detached/re-attached, etc.

Really, though, why do you care about gaps? You shouldn't. If you need a contiguous sequence of numbers, stop using IDENTITY.


Query performance diffrence between equals and between

Since the column OrderTime contains the whole time stamp option A would only return orders places exactly on midnight. There is no attempt to extract the date from the stamp.

D doesn’t change the column and can use an index since it’s regular comparisons. C would most likely also work but it includes a CONVERT for each row tested so no index used and every row has to be checked.


CTE vs Function/trigger performance in Postgresql

Running a big statement with its CTEs will be more efficient than running several small ones.

The only advantage of having a PL/pgSQL function for the individual inserts is that plans of statements are cached for the duration of the database session. But you can put your big statement into such a function if you want to make use of that.


When I have 6 cores and they do a table scan and merge afterwards, does every core scan the whole table?

To be upfront with you, I'm not exactly sure what you mean by "a merge was done". Are you talking about a merge join? Perhaps you mean a parallelism operator? At the very least I can answer the question about parallel table scans.

Statistics from IO showed me that every core did a table scan, or better that I got 6 table scans.

I assume what you mean by this is that you ran SET STATISTICS IO ON before running your query and part of the output included something like this:

Table 'your_table'. Scan count 6, ...

The label "scan count" is a bit misleading. You should not conclude that if STATISTICS IO reported 6 scans that all of the rows in the table were scanned 6 times. Considering the following simple example query on a heap table called heap_table:
SELECT TOP 1 *
FROM heap_table
OPTION (MAXDOP 1);

For that query STATISTICS IO should report a scan count of 1, right? But SQL Server clearly didn't need to read all of the rows in the table. Looking at the definition for the scan count label is also helpful:

Number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output.
...
Scan count is N when N is the number of different seek/scan started towards the left or right side at the leaf level after locating a key value using the index key.

So if your query did a parallel scan I would expect to see a scan count of at least 6, but that doesn't necessarily imply that all of the rows from the table were read six times. How can you tell how the rows were distributed amongst your CPU cores?
The easiest way is to just look at an actual execution plan. If you look at the details for a parallel scan SQL Server will show you how many rows were processed by each CPU thread. Below is a picture of what you might see borrowed from Paul White's article Parallel Execution Plans – Branches and Threads:

As you said you encountered this query years ago so that method won't help you. Instead we need to look at the techniques available to SQL Server for parallel plan processing. Craig Freedman has a series of blog posts on the subject. From the Parallel Scan article:

How does parallel scan work?
The threads that compose a parallel scan work together to scan all of the rows in a table.  There is no a-priori assignment or rows or pages to a particular thread.  Instead, the storage engine dynamically hands out pages to threads.  A parallel page supplier coordinates access to the pages of the table.  The parallel page supplier ensures that each page is assigned to exactly one thread and, thus, is processed exactly once.

Well, there you have it. Like I said earlier you can easily test this by running a query with a parallel scan and checking out the details for the parallel scan operator in the actual execution plan.
For another way to look at it, try thinking of a scenario in which it's beneficial for SQL Server to do a full table scan per core.
Suppose that you just so happened to have a UNION ALL query that referenced your table six times. In principle, SQL Server could do each table scan with one core independently and combine the results at the end. However, SQL Server will not do this because it will not do pipeline parallelism. Even if it could, I personally can't think of an advantage of doing that here, other than avoiding some of the overhead associated with parallelism.
You could read about the broadcast type of parallel execution and wonder if in that scenario SQL Server could do six complete scans of a table, one with each core. For the broadcast type of exchange SQL Server sends all rows to all consumer threads. However, this can be accomplished with a serial scan on the table followed by a Distribute Streams type of parallel exchange. Indeed, that is what you see in the hash join example. I can't really think of a benefit to doing that scan in parallel, especially when the broadcast type is only used for relatively small tables.
One case in which I suppose this could happen is if you had a parallel nested loop join with an outer table that contained 6 rows and with a table scan on the inner side of the join. In that case, I believe that the table scans will be completed by independent serial threads, so effectively you would get each core doing its own table scan. Of course, such a query probably performs very poorly and is not something to aim for, especially when the outer table has more than six rows.


oracle sql: How to not select select duplicate records from table

You can use NOT IN:

    SELECT DISTINCT t1.ORDER_NUMBER, t1.FLAG
    FROM TABLE t1
    WHERE t1.FLAG='true' AND t1.ORDER_NUMBER NOT IN
    (SELECT t2.ORDER_NUMBER FROM TABLE t2
    WHERE t2.FLAG='false' OR t2.FLAG IS NULL)

If you don't care about nulls (that is you only want to exclude order numbers that explicitly have a false flag) just remove the "OR t2.FLAG IS NULL" condition. Also this query will return orders where possibly more than one records with flag 'true' exist (as long as there are no records with flag 'false' or null)


Check Index Fragmentation on SQL Server Audit Queues

I dug into this and it looks like the SQL Server team went out of their way to prevent internal table stats from being exposed through DMFs and DMVs.

I can't say I blame them, as the implementation of things like queues is always subject to change, but it doesn't help you out with your problem. I can see why in this case it's really difficult to balance between exposing metadata and exposing implementation details.

Anyway, let's play around with a few things and see what happens.

If I grab an object_id from sys.internal_tables and plug it into sys.dm_db_index_physical_stats (the obvious thing to do), something interesting happens -- on 2005, you get an empty result set and a level 16 error (msg 2561, state 10); on 2008 R2, you get just an empty result set with no error. In contrast, if you put in some random number on 2005 you get the same error, while on 2008 R2 you now get a different level 16 error (msg 2573, state 40).

This tells me there is some internal checking going on specifically to handle the case of calling this function (and sys.dm_db_index_operational_stats -- probably all of them) with a valid, yet non-user-table object_id. (Note: using the OBJECT_ID function on an internal table name prefixed with sys. does return a result.)

Since DBCC DBREINDEX "solves" the fragmentation problem, I figured I'd try out something else from the same era -- DBCC SHOWCONTIG, the precursor to sys.dm_db_index_physical_stats.

If I feed it a table name from sys.internal_tables (again, prefixed with sys.), it returns nothing -- no error, just a dissatisfying DBCC execution completed. If DBCC printed error messages, contact your system administrator. -- while giving it a random object_id results in a level 16 error (msg 2501, state 45). This is a pretty clear indication there's an explicit check for this case in the code.

I tried all this using a normal connection and the DAC with the same result. For the sake of curiosity, I even tried started the server in single-user mode and used the DAC, but that didn't change anything.

Hacking into the resource database to dump the definitions of the DMFs yielded nothing except internal-only OPENROWSET syntax, which isn't particularly helpful (kind of interesting, though).

While I was hacking into the resource database, I also dumped out the internal assemblies and used Reflector to look at what's inside, but that didn't yield anything (but was likewise interesting).

In any event, I think the only reasonable recourse is doing as the article suggests -- running the deprecated DBCC DBREINDEX from a job as frequently as you need. There just isn't any visibility into these kinds of stats, at least not that I can find, and to me at least, it appears that these stats have been intentionally hidden.

Sadly, even if you were okay writing your own algorithm, the undocumented DBCC IND fails the same way as DBCC SHOWCONTIG, which is really too bad, because this probably would have given you a decent shot. As an academic exercise you could do it with output from DBCC PAGE, but obviously that's nowhere close to a practical (or efficient) solution.


Postgres text column too large for an index - searching for IP address, so I don't think text search will work

You need a function which extracts the IP addresses into an array.  The regexp you gave doesn't work for me, so I came up with my own crude one, you will probably want to tweak it to suit yourself:

create or replace function extract_ip(text) returns text[] immutable language SQL as $$
  select array_agg(x[1]) from regexp_matches($1,'\d+.\d+.\d+.\d+','g')f(x)
 $$;
create index on foobar using gin (extract_ip(value));
select * from foobar where extract_ip(value) && ARRAY['192.168.2.1'];


In Subselect, using GetDate instead of a @variable leads to worse execution plan

When you use the variable, SQL Server makes a guess at the selectivity of the  tstamp_nosync predicate. For the > operator, the guess is 30%. The table contains 3052 rows, so estimation guesses that 30% of 3052 = 915.6 rows will pass that test.

When you use the dateadd(year, -100, getdate()) expression, SQL Server can produce a much more accurate estimate of the number of rows that will qualify for that predicate.

Your build of SQL Server 2012 does not have the Actual Rows Read property, so the number of rows that actually match is not available in your second plan. That feature was added in Service Pack 3 of SQL Server 2012 (you are still on Service Pack 1, for some reason).

From what I can tell, the better estimate for the dateadd expression is that all rows will qualify. This is apparent from the output of the Eager Index Spool in the first plan. The 3052 rows are tested 3052 times (once per iteration of the nested loop join) with the tstamp_nosync predicate, and all pass. The result is 3052 * 3052 = 9,314,704 rows in total. This is shown as the actual number of rows from the spool:

The general expectation that using variables will result in a worse execution plan is sound, because specific estimations are usually more accurate than complete guesses; however, the other predicate sub.cus_id > main.cus_id in the query will always result in a 30% guess.

For the variable query, both predicates are estimated with a 30% guess. The combined estimate is 3052 * 30% * 30% = 274.68 rows (seen at the input to the Stream Aggregate):

For the dateadd query, there is one 30% guess (seek predicate) and a 100% selectivity estimate (residual predicate), giving a combined estimate of 915.6 rows as noted previously. This estimate is shown at the output of the Clustered Index Seek:

The difference in estimates explains the optimizer's different plan selections.

With fewer rows estimated for the variable case, the estimated cost is lower. The better estimate is costed higher, for similar reasons. Nevertheless, both plans are based on inaccurate estimations because of the guess involved in the cus_id predicate.

In terms of observed performance, which plan performs better rather depends on the details of your local environment. The variable plan scans the source table once and builds an indexed temporary table in tempdb. The non-variable plan simply accesses the base table each time with a seek (guessed 30% selectivity) and residual predicate (computed 100% selectivity). Which performs better in practice depends on multiple factors.

Given correct information for both predicates, the optimizer would be more likely to generate the plan that is better in practice.

It often pays to be very conscious of data types. There is an implicit conversion to datetime2(4) in your execution plan because that is the type of the column, and the variable is something else (datetime perhaps). The result of the dateadd expression is also datetime, and also requires implicit conversion. This conversion does not appear to be important in this specific case, but it often is. For example, see:

Performance Surprises and Assumptions : DATEADD() by Aaron Bertrand.


Create a secondary table with contiguous values for the known states (and maybe leave a little room for others you may want to segregate later):

CREATE TABLE dbo.States
(
  StateID TINYINT PRIMARY KEY,
  Abbreviation CHAR(2)
  StateName VARCHAR(255) NOT NULL UNIQUE
);

INSERT dbo.States VALUES
(1,'AL','Alabama'),(2,'AK','Alaska'), ..., (51,'WY','Wyoming');

-- leave 52 - 68 available for other partitions

INSERT dbo.States VALUES
(69,'EH','Canada'),(70,'PR','Puerto Rico');

Now, in the partitioned table, you can partition against the StateID column, and just set up the partition scheme such that 69+ is its own partition.

You can't do this with state or city names because partitioning on those values can only be alphabetical. There's not really a clean way to say "put PA in its own partition, and RI in its own partition, but lump PR into another partition along with Guam."


How do get correct instance name on Multi instance cluster

On each instance, run:
SELECT
  ServerAndInstanceName = @@SERVERNAME,
  UnderlyingServerName  = SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS');

If they both show the same UnderlyingServerName then they're both on the same node.


Can not reset MariaDB password "can't connect to local MySQL server through socket"

This is because mysqld_safe silently fails if it doesn't have permissions and it can't write to the log as in the event of being run as a regular user.

Instead run,

sudo mysqld_safe --skip-grant-tables &


I looked at your plans and found the cause of this difference:

you get a slow plan in the database with compatibility level 100 (see the plan: CardinalityEstimationModelVersion="70" i.e. the old cardinality estimator is used).

Your good plan is generated using new cardinality estimator of SQL Server 2014: CardinalityEstimationModelVersion="120"

You should update your question with the view text to find out the exact predicate where estimation goes wrong, but in your slow plan cardinality there is underestimation of the join with OMInternalOrganization so NL was chosen while in the fast plan you see Hash Join for this table, starting with this join slow plan shows many other Nested Loops while Hash Join is better and it's used in fast plan.

I don't know for what reason that database was left in compatibility level 100, maybe other queries perform better with old cardinality estimator, so instead of global changing compatibility level to 120 for that database I suggest you to use local query option: OPTION (QUERYTRACEON 2312) to forse your query to use new cardinality estimator even in that "old-style" database.

More on cardinality estimator here: Cardinality Estimation (SQL Server)

And here: Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator


Grant permissions to allow user to check if session is on read replica or not

It appears that the issue was not permissions at all.

Thanks to this thread https://lists.ibiblio.org/pipermail/freetds/2010q2/025616.html I was pointed in the correct direction - our connection library was not able to handle the return type for the procedure gracefully, as it couldn't map the datatype.

SELECT CAST(DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS VARCHAR(40));

Returns the expected output.

For reference, to confirm the permissions that worked for us;

SQL User

Server Roles - public
Database Roles - public, db_datareader, db_datawriter


What happens when you update a VARCHAR cell with a longer string?

MySQL's InnoDB stores rows in blocks of 16KB each.  Within a block there could be a few big rows or hundreds on narrow rows.  Almost always, there is some spare room in a block.  When a column in a row is changed, the row is rewritten in the same block (if possible) and things are shuffled around within the block to make room.  (The exact details are buried in the code.)

If so much text (or blob or whatever) is added, that there is not room in the block, then there is a "block split".  This is where the rows in the original block are split between two blocks.

Blocks are stored in a B+Tree arrangement of blocks.  (See Wikipedia.)  Therefore, the blocks are not necessarily stored consecutively, but they can be referenced consecutively.  Thus, a block split in a billion-row table is about the same small amount of work as in a hundred-row table.

Another aspect is the "MVCC" that goes on to allow for multiple transactions to touch the same rows at the same time.  This leads to not just replacing a row with the modified value, but actually hanging onto the previous copy of the row until the transaction that needs it is finished.  But again, the spare room in a block, plus block splits, plus the BTree organization handles this.

So, your next question is about deleting?  Well, if the text is shrunken a lot, or a bunch of rows are DELETEd, then it seems like a block might be shrunken a lot, possibly even emptied?  Yes.  In this case, InnoDB checks to see if two adjacent blocks are small enough to combine, and does so, thereby freeing up one of the blocks for future use.  (This freed up block is kept in the tablespace and not given back to the OS, hence ibdata1 never shrinks; etc.)

All of that stuff happens under the covers, and we don't need to worry about it.

Really large records:  For example a row with a MEDIUMTEXT column with 1MB of data won't fit in 16KB.  What to do?  Well, the bulky columns are actually stored in other blocks, separate from the main part of the record.  This leads to some confusing limitations on the maximum size of a row and some unexpected performance issues.  Again, this is handled transparently, and the unit of allocation is 16KB.

While I am at it, note that the structure of an INDEX is essentially identical to that of a table -- a BTree of 16KB blocks that play block split/merge games under the covers as the index entries are added/deleted for row inserts/deletes.


Interval values for aggregate function AVG greater than 24 hours

As @horse_with_no_name already comment, you can use the justify_* funcions, take a look:

WITH config AS (
SELECT '3 days 0 hours 50 minutes 26.092199 seconds'::interval as reference
), tests AS (
    SELECT
        extract(epoch from reference) as epoch
    FROM config
)
SELECT
    config.reference,
    tests.epoch,
    (tests.epoch::text || ' seconds ')::interval,
    justify_days(config.reference) as justify_days,
    justify_hours(config.reference) as justify_hours,
    justify_interval(config.reference) as justify_interval
FROM config, tests;

Take a look at the output:

postgres=# \x
Expanded display is on.
postgres=# WITH config AS (
postgres(# SELECT '3 days 0 hours 50 minutes 26.092199 seconds'::interval as reference
postgres(# ), tests AS (
postgres(#     SELECT
postgres(#         extract(epoch from reference) as epoch
postgres(#     FROM config
postgres(# )
postgres-# SELECT
postgres-#     config.reference,
postgres-#     tests.epoch,
postgres-#     (tests.epoch::text || ' seconds ')::interval,
postgres-#     justify_days(config.reference) as justify_days,
postgres-#     justify_hours(config.reference) as justify_hours,
postgres-#     justify_interval(config.reference) as justify_interval
postgres-# FROM config, tests;
-[ RECORD 1 ]----+-----------------------
reference        | 3 days 00:50:26.092199
epoch            | 262226.092199
interval         | 72:50:26.092199
justify_days     | 3 days 00:50:26.092199
justify_hours    | 3 days 00:50:26.092199
justify_interval | 3 days 00:50:26.092199

Please, take a look at the documentation[1] for more details.

I hope it helps.

References:

https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TABLE


Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system?

Bulk Logged will not help you in this situation.  Your transaction log is growing because you are performing large transactions.  In simple mode, the log is truncated after a checkpoint.

Depending on how you are loading your data, you may need to break it up into smaller chunks.  Make sure you are not loading all the data in one large transaction.


Download the Google Drive desktop application.
When backing up SQL Server, set the destination to the "My Drive" folder created by Google Drive (something like C:\Users\<user>\Google Drive by default)
Once a backup is placed in that folder, it will automatically start to sync to Google Drive.

Also worth nothing, as per Shawn Melton's comment:

You can adjust the folder location for the drive application, so it does not have to reside in a user folder. Any Administrator can actually remove a user profile and you just lost all your backups (locally at least).


How to properly downgrade mysql 8 to 5.7 in centos 8

Downgrade from MySQL 8.0 to MySQL 5.7, or from a MySQL 8.0 release to a previous MySQL 8.0 release, is not supported. The only supported alternative is to restore a backup taken before upgrading. It is therefore imperative that you back up your data before starting the upgrade process.
https://dev.mysql.com/doc/refman/8.0/en/downgrading.html


Database design - People and Organisations

Some insights from having actually built a couple of apps with contacts and companies.

Firstly, you're missing several use-cases in your outline. Among the colorful ones I've run into over the years which you don't necessarily cover:

Some organizations have sub-organizations, be them divisions, subsidiaries, you name it.
Some people belong to several organizations. As in VP whatever of XYZ, chairman of ABC, and self-employed CEO/consultant -- each on a part time basis, hopefully.
Some people can be contacts for an organization without actually belonging to it. E.g. when a high profile consultant is temporarily hired as a project lead, he could possibly become the primary contact for company XYZ without actually belonging to it.
People can have sub-people too. And the latter can be in the same companies, or not. For instance, our VP of XYZ might prefer that you go through his main primary secretary over there, unless you contact him regarding ABC in which case that would be his assistant; or personally for consulting work -- a very busy person indeed, but there are people like that.
Some organizations have no contacts associated with them. This can happen, for instance, in an app that outputs lists prospect companies (for sales people) in which contacts have yet to be identified.
Conversely, contacts can have no organizations. For instance consumers.

Secondly, since your outline mentions customers, be wary that customers can be a company or a (physical) person. You seem to have that part covered based on your diagram, so let's move on.

If your next step is to add anything related to accounting, such as an "orders" table, be wary that anything accounting related is tied to a company, a contact, products, prices, etc. at a given point in time. These details can evolve in all sorts of colorful ways, and a frequent design mistake is to create the perfect normalized design and assume that you'll just update the tables as needed. Big no-no. If the taxman asks you to print out your invoices, and your IT says company XYZ at price P when accounting booked company ABC at price Q, you're very, very screwed. And don't get me started on archived/closed yearly accounts and reports that change due to similar design mistakes.

Thirdly, be very, very, very wary of UI/UX issues that you might introduce by normalizing too much. If it does not work like the apps that typical users have in their hands (read: Outlook), you'll be a poorly trained secretary away from changing the company of every employee at XYZ to ABC when Joe gets a new job. I've actually seen this happen and it was not a pretty sight. (She kept her job, in case you're wondering.)

Lastly, don't get me started on merging the inevitable duplicate companies, contacts, and so many other colorful things that couldn't possible turn up. Keep those in mind and make sure that your schema is very, very forgiving because it will happen.

Now... in practice...

Personally, I've come to actually turn a blind eye on normalization in this case. Contacts/Companies is one of those cases where DB design and beautifully normalized data as taught in school is a recipe for trouble in the form of resource hogging, over-complicated queries and ludicrously complex UI. It is, imho anyway, not the right thing to do.

Start by creating a contacts table, complete with fields such as first and last name, display_name if needed, company_name, address, phone, cell_phone, email, email2, secretary_name, secretary_phone, etc.  If it's available when you create a contact in Outlook, it probably belongs in there.

You'll note that I didn't mention a companies table. That's because you usually don't want any strong ties between your contacts table and your companies table. If you need one, add one, and add a copmany_id in addition to the company_name. But then, make it a foreign key on delete set null. And be sure to keep it very, very loosely tied to company_name at the database level. Maintain it at the front-end level -- not anywhere else. This will keep a secretary from inadvertently changing the company of multiple contacts.

Keep things sane. Anything that might reasonably show up in a list (i.e. select * from contacts where ... limit 10), be queried against, or be useful frequently ought to be in the table. No joins, nada. Query, iterate through the result, done.

If you've really want additional garbage in there, you've two options.

One is to create an extra_contact_details table. It can be an EAV table, or the full load of company_name, address, phone, etc. fields, or a hogwash of normalized bits and pieces. Whichever option you take, don't over do it. Either will create additional (potentially complex) queries, and heaps of thorny programming issues down the road when you create the UI. What's absolutely key here is, if you go this route, a secretary who has always worked with Outlook needs to make sense of it.

The other, which I'll actually recommend upfront, is to add a text field called "extra_contact_details" in the contacts table and begone with it. No normalization at all. One set of primary details. Frequently used secondary details. Anything extra as plain text. Secretary gets it. End users get it. Done.

Lastly, if you need to store a version of any data at a given point in time, be sure to duplicate the value of any key details -- read: whatever you need to print it exactly as it was when it got inserted.


What's the best way to use a gist index on tsrange to check if a time occurs after the range?

Postgres doesn't support any operators for this. As pointed out by @evan-carroll, it probably could and should.

So, the best solution is

where tsrange(localtimestamp, localtimestamp, '[]') >> tsrange(start_at, events.end_at, '[)')


How to link data from two spreadsheets imported to sql

The only connection I found is that the Reference column in Sheet 2
  has numbers that match the ParentID on sheet 1 but with a suffix
  appended.

You need to match float value 10125 (column ParentID) on Sheet1 with NVarchar(255) value 10125_Motsomane (column Reference) from Sheet2.

If data types would match you would simply do a direct INNER JOIN:

SELECT
    S1.ParentID,
    S2.Reference,
    AnyOtherColumnYouNeed
FROM
    Sheet1 AS S1
    INNER JOIN Sheet2 AS S2 ON S1.ParentID = S2.Reference

But to extract the number before the underscore you can use CHARINDEX to find the position of the underscore and then LEFT to get the value up to that position:

DECLARE @ExampleValue NVARCHAR(255) = N'10125_Motsomane'

SELECT
    Original = @ExampleValue,
    Trimmed = LEFT(
        @ExampleValue,
        CHARINDEX(N'_', @ExampleValue) - 1)

Result:

Original            Trimmed
10125_Motsomane     10125

Applying this on the join (will do an implicit conversion from NVARCHAR to FLOAT):

SELECT
    S1.ParentID,
    S2.Reference,
    AnyOtherColumnYouNeed
FROM
    Sheet1 AS S1
    INNER JOIN Sheet2 AS S2 ON S1.ParentID = LEFT(S2.Reference, CHARINDEX(N'_', S2.Reference) - 1)

Keep in mind that the LEFT function will fail if there is no underscore on a value for Reference, since it will return -1 as position.


How to use same stored procedure for different tables and avoid using dynamic sql

The other thing you could do is create a view that unions the like tables together, and include a defined column with the table name, then just do a select against that view with the tablename in the where clause.

CREATE VIEW tablesviews AS
SELECT columns, 'TABLE1' as tablename
FROM Table1
UNION ALL
SELECT columns, 'TABLE2' as tablename
FROM Table2
...

SELECT {some columns}
FROM  tableviews
WHERE Tablename=@table and [other where clauses]


Since you don't have a WHERE clause in either query, you're returning all rows in both cases, so I'd think the use or non-use of the index would have very little impact on performance in these examples.


Why can hr create an Oracle DB session though not using PWFILE?

Only user having sysdba and sysoper privilege are connected to database using password file authentication. Other users such as HR are authenticated using data dictionary.

Password file is required for SYSDBA and SYSOPER privileged users to make remote connection. As I said earlier other users are authenticated using data dictionary, password file is not required for them to make remote connection.

The password is stored in encrypted form user undocumented table called USER$.

SQL> select password from user$ where name='HR';

PASSWORD
------------------------------
4FF80DF8D8DBE02E


If you are using the web interface to run and view the reports there is an option to set the zoom level by appending &rc:Zoom=Page Width or whatever percentage you want.

Also it can be configured in the SSRS configuration file see MSDN for details.


Query to categorize rows based on a "time" column without using a CASE expression

You stored Time as an int but then displayed it as a string (with leading zeros). Those don't get stored, so in order to perform calculations that need to handle the leading zeros, you need to convert to a string first (your current query doesn't do this, so either your query doesn't work, or that table structure is not accurate). Since this is a linear calculation (groups of 3), you can simplify away the CASE expression by simply dividing the first two digits in the time by 3 (and thanks to SQL Server's integer division, the remainder gets discarded, and we add 1 to go from 0-3 to 1-4). Of course, there is an exception, because you want 12 PM to be in group 4, not group 5. With a CASE expression this could just be left to the ELSE clause, but if you eliminate CASE, you will have to deal with that exception explicitly - that's all the COALESCE/NULLIF stuff at the end.

;WITH x AS
(
  SELECT ID, Product, [Time] = RIGHT('000'+CONVERT(varchar(4),[Time]),4)
  FROM dbo.ProductTT
), y AS
(
  SELECT ID, Product, [Time], h = CONVERT(char(2),[Time])
  FROM x
)
SELECT ID, Product, [Time],
  [GroupID] = 'group' + CONVERT(char(1),h/3+1-COALESCE(NULLIF(h%11,1)-h%11,1))
FROM y;

Results:

ID  Product  Time   GroupID
--  -------  ----   -------
1   XX       0030   group1
2   UY       0354   group2
3   YY       0517   group2
4   ZZ       0712   group3
5   WW       0415   group2
6   GG       1112   group4
7   MM       1030   group4
8   HH       0913   group4

I strongly recommend you use the actual time data type, as that is what it was designed for. Then you can use DATEPART(HOUR( in your calculations instead of messy string manipulation, the query above is less complex and, as a bonus, you get built-in validation, to avoid invalid times like 1369 and 9997. Or if the leading zeros are important but you don't care about validation, use char(4) instead of int.

I also think you need to handle the case where an event happens in the afternoon.

And FWIW I am not sure why you don't want to use a CASE expression here. It's a few more characters, sure, but it's a lot more clear what the query is actually doing. Code that is self-documenting is much more valuable than code that is slightly shorter. This is simpler IMHO, and would be even simpler if you used the right data types:

;WITH x AS
(
  SELECT ID, Product, [Time] = RIGHT('000'+CONVERT(varchar(4),[Time]),4)
  FROM dbo.ProductTT
)
SELECT ID, Product, [Time],
  GroupID = 'group' + CASE CONVERT(char(2),[Time])/3
    WHEN 0 THEN '1'
    WHEN 1 THEN '2'
    WHEN 2 THEN '3'
    ELSE '4' END
FROM x;


In the end I had to reset the slave completely and set up replication again. Not sure why but this seems to be a common problem/solution as I can see from other articles online.


database duplication VS database replication

Database duplication generally refers to restoring a physical backup of a database to a different server (preferably using RMAN).  That is normally done periodically to refresh lower environments from production.

Database replication generally refers to the process of copying a subset of data from one database to another on an ongoing basis.  Replication generally implies that the data is being copied from one production database to another production database (or a test database to a test database, etc.)


Pull entire row based on MAX(Column) MySQL

If you don't care about ties and want one row only and always one row, then the following could be used. With an index on (Score), should be as efficient as possible:

SELECT ID, Name, Score
FROM TestTable
ORDER BY Score DESC
LIMIT 1 ;

If you care about ties but still want only one row, you'll have to decide how ties should be resolved and modify the ORDER BY accordingly. If, for example, the row with the first name in lexicographic order is wanted in case of ties, then use the following - and an index on (Score, Name):

ORDER BY Score DESC, Name ASC

If you want all tied results, then the query by @Dan in the other answer should be the best. The index on (Score) would be sufficient for that.


Yes and No. Most of the RDBMS  like MySQL Postgres SQL-Server ... relay on the basic SQL Syntax. All the different systems have their own dialect with their one extensions on SQL but it is rather easy to transit between the individual ones.

MongoDB is somewhat different as it is a so-called NoSQL ( Not Only SQL). Mongo ( like CouchDB or Cassandra) aren't based on the premises that your data is structured and store them in key-value pairs ( I have to admit I'm not an expert here, so correct me if I'm wrong) instead of tables.


Cannot open table mysql/innodb_index_stats

PROBLEM
In MySQL 5.6, ibdata1 includes 5 InnoDB in the mysql schema.
mysql> select table_name from information_schema.tables
    -> where table_schema='mysql' and engine='InnoDB';
+----------------------+
| table_name           |
+----------------------+
| innodb_index_stats   |
| innodb_table_stats   |
| slave_master_info    |
| slave_relay_log_info |
| slave_worker_info    |
+----------------------+
5 rows in set (0.00 sec)

mysql>

In MySQL versions before 5.6, if you shutdown mysql, delete ibdata1, and start mysql back up, ibdata1 gets recreated. If you do this with MySQL 5.6, these 5 tables are not recreated. Even if your deleted ibdata1, the following 10 files are still in /var/lib/mysql/mysql:

innodb_index_stats.frm
innodb_index_stats.ibd
innodb_table_stats.frm
innodb_table_stats.ibd
slave_master_info.frm
slave_master_info.ibd
slave_relay_log_info.frm
slave_relay_log_info.ibd
slave_worker_info.frm
slave_worker_info.ibd

I learned this early on. With a newly created or damaged ibdata1, there are no corresponding data dictionary entries for those 5 tables.
In your particular case, I suspect you copied ibdata1 from a Linux server to the Windows server. MySQL/Windows would be expecting the data dictionary to have the DOS Filename Format. Moving that ibdata1 to Windows, MySQL/Windows cannot relate to the Linux filename and path for pointing to the .frm and .ibd files.
SOLUTION
STEP 01 : Install MySQL on another DB Server
STEP 02 : mysqldump only those 5 tables
TABLELIST="innodb_index_stats"
TABLELIST="${TABLELIST} innodb_table_stats"
TABLELIST="${TABLELIST} slave_master_info"
TABLELIST="${TABLELIST} slave_relay_log_info"
TABLELIST="${TABLELIST} slave_worker_info"
mysqldump -uroot -p mysql ${TABLELIST} > mysql_innodb_tables.sql

STEP 03 : Copy mysql_innodb_tables.sql to the DB Server with bad tables.
STEP 04 : Execute mysql_innodb_tables.sql
STEP 05 : Run FLUSH TABLES; (Optional)
Give it a Try !!!


What tasks can only sysadmin's do in SQL Server 2000?

You don't have to perform any task (destructive or non-destructive) at all.

You can use the built-in function IS_SRVROLEMEMBER to find if a certain LOGIN is a memeber of the sysadmin server role:

SELECT IS_SRVROLEMEMBER('sysadmin','<LoginName>');

Note that for roles other than sysadmin the result will be positive (=1)  for implicit membership as well.

You can use the built-in stored procedure sp_helpsrvrolemember to get a list of all the explicit members of a certain role:

EXEC sys.sp_helpsrvrolemember @srvrolename = 'sysadmin';

Or a complete list of all explicit members of all server roles:

EXEC sys.sp_helpsrvrolemember;

For SQL Server 2000 there is also the option to sort or filter the result set using:

CREATE TABLE #ServerRoles
(
  ServerRole VARCHAR(20),
  MemberName sysname,
  sid VARBINARY(85)
);

INSERT INTO #ServerRoles
(ServerRole, MemberName, sid)
EXEC sys.sp_helpsrvrolemember;

SELECT SR.MemberName, SR.ServerRole
FROM #ServerRoles SR
WHERE 1 = 1                            -- Change this to any filter you want
ORDER BY SR.MemberName, SR.ServerRole; -- Change this to any sorting you want

DROP TABLE #ServerRoles;
GO

For SQL Server 2005+ there is already a system view to be queried directly:
sys.server_role_members which is a many-to-many relationship table between roles and logins.


SQL server database backup - Destination Disk - Adding multiple files - does it duplicate or split backup into the files?

Don't rely on the behaviour of SSMS, which has historically been buggy or poorly documented and also sometimes changes from version to version.

The easiest way to be sure is to click the "script" button on top of the dialog and compare the output to the documentation.

For example when you click the script button after adding several files in the SSMS version that comes with SQL Server 2014 like this:

You can see the exact script that will be used is this:

BACKUP DATABASE [mydatabase] TO
DISK = N'G:\location\file1.bak',
DISK = N'G:\location\file2.bak' WITH NOFORMAT, NOINIT,
NAME = N'databasename-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

If you then refer to the documentation about backups you will see that this script creates a striped backup.

A mirrored backup would need the MIRROR TO clause like this

BACKUP DATABASE [mydatabase] TO
DISK = N'G:\location\file1.bak'
MIRROR TO DISK = N'G:\location\file2.bak' ;
GO

I don't know which version of SSMS your book is talking about as it's unavailable to me, but the bottom line is don't trust SSMS, use T-SQL and refer to the documentation.


Are cursors/While loops the only way to make administrative changes to multiple databases?

As far as loops go for this type of thing, don't worry about it.  Loops and cursors have a bad reputation because there are normally better set-based approaches that are often faster.  For admin stuff, sometimes loops are the only way, and no set-based ways of doing this spring to mind, although you can parallelise tasks with tools like start in DOS, SSIS, Powershell etc.

Having said that, I prefer to use SQLCMD mode for this type of thing.  It's a special mode you can switch on in SQL Server Management Studio (SSMS) via the main menu > Query > SQLCMD Mode ... like this:

Once you have switched this mode on, you have access to all kinds of commands like :connect to connect to another server, :r to read a file, :out to redirect output, SQLCMD variables, and any DOS commands by prefixing them with two exclamation marks, eg !!dir.

For your example, you could do something like this:

:connect .\sql2014
SET NOCOUNT ON
GO

-- Redirect output back to normal
:out d:\temp\temp.sql
GO

SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL;'
FROM sys.databases
WHERE recovery_model_desc != 'FULL'
  AND database_id > 4
  AND name Not In ( 'distribution', 'SSISDB' )
GO

PRINT 'GO'
GO
-- Redirect output back to normal
:out STDOUT

-- Optionally read/run the temp file you have scripted
--:r d:\temp\temp.sql
GO

This allows you to "script a script", then you can review it, change if required and there is an audit trail of what has been run.  Give SQLCMD mode a try!


Updating data when data is from an external source

If the imported data has artificial ids (such as from AUTO_INCREMENT), and your tables are referencing that data via those ids, and each "update" changes those ids, then you need to rethink.

If the imported data has "natural" keys that do not change with each "update", then you are safe.

If the "update" is really a complete set of data, then to reload, do this for each table

DROP TABLE IF EXISTS old;   -- from last month
LOAD DATA .. INTO new ... -- or whatever load mechanism works
RENAME TABLE real TO old, new TO real;

Keep the old tables around until next month -- you may have to revert, or you may want to compare old to real to see, or react to, what changed.  See LEFT JOIN to discover what is added/removed.  Use a plain JOIN to see what values have changed.


How to set constraint sql 2 colume, Data is not available elsewhere and must be used only

The requirement.is unclear. More examples/better specification is needed..
However, i asume that after an ID is " added " to PI, this becomes " paired" and can't be paired with different ID/PI.

create a pairing table (ID,PI) with PK on Id and Unique on PI. Then add FK from your table to this new table either on (PI,ID) or an identity column

There is no simple constraint that would do what you need. What can be used is a CHECK constraint with an function that would do the check by counting ID/PI pairs with same halves. However this is not very optimal when it comes to performance


What type of SQL Server Replication fits our scenario? Merge or Transactional?

A few thoughts. It sounds to me like you may be able to get away with a couple options if replication is what you end up with. First a word of caution - Replication isn't something that should just be configured in production and enabled/used unless you have some experience with it. This becomes truer with more important workloads and busier systems. Replication can cause headaches if not properly done. I'd invest in mentoring/training/consulting to help you along the path from folks who have been burned by mistakes already.

That said. It looks to me like your requirements don't seem to have any 2-way changes that need to be synced up or merged. It looks like you have a bunch of things that need to go one directional. Orders never come in locally, only on the website. Order status never gets generated on the website, only consumed, etc.

If that is the case - this sounds like Transaction Replication to me. Merge is more for when you need to have multiple versions that can make changes and receive changes at the same time. The tired old Microsoft example still works - a bunch of sales folk all with a local app that does lead management and a big central server. They can get their leads when in the home office and get data, then they work disconnected and sync up their changes. With Merge you have to deal with conflicts (if the same data is modified in two places, who wins?) Transactional is more what is used when you have changes going in one direction (but you can have it go bidirectional here too even).

And you can set replication up to have various tables included or not included in the publication as articles.

A couple links to give you a bit more info:

Transactional Replication Overview

Merge Replication Overview

Now if you have data that can be updated on each and needs to be "merged" then merge may be a better answer. When I work with replication - I prefer to stay as close to Transactional as I can, though. It is easier to administer and there is no conflict resolution worries if you don't need it.

Depending on your volume - if it is low enough - and your latency - if some lag time is acceptable - you might also consider something more programatic or ETL like. Perhaps staging data and sending it over in batches. Sending messages across as data changes in one side, etc.


Can I use only OrientDB without any RDBMS?

Any DB model can store any type of data. The trade-off is what you're prepared to do in the application and what you want the DBMS to handle for you, plus performance.

So in some ways I think it would be safer to have a single system that's good enough than several disparate systems that are individually excellent at their own part but that I have to stitch together.

Think also from a business perspective. Where are the risks and opportunities here? Are you able to charge more for this new service because of the clever way you've stitched together MySQL and Orient? Conversely, if the stitching frays is there a risk of service failure and lost revenue? If you can quantify the benefits and costs, even only in tee-shirt sizes, of both approaches I think it will help you reach a conclusion.


How can I replace the following query with ISNULL function in SQL?

This should do what you need

SELECT ISNULL(ta.[tag-value], 'N/A') AS [tag-value]

FROM   @AgentTableFilt ta

Or you could use the following

SELECT COALESCE(ta.[tag-value], 'N/A') AS [tag-value]

FROM   @AgentTableFilt ta


How to retrieve top 3 highest and 3 lowest values in SQL?

You can also use something like this (see the fiddle here) - it's perhaps more flexible than the other answer - in any case, you should look at learning window functions in SQL - they can be very powerful.
First, table and data:
CREATE TABLE sale
(
  SALE_ID          VARCHAR (10),
  PRODUCT_ID       INTEGER,
  YEAR             INTEGER,
  QUANTITY         INTEGER,
  PRICE                INTEGER
);

INSERT INTO sale (sale_id, price) VALUES
('Item_1', 1), ('Item_2', 2), ('Item_3', 3), ('Item_4', 4), ('Item_5', 5), ('Item_6', 6),
('Item_7', 7), ('Item_8', 8), ('Item_9', 9), ('Item_10', 10), ('Item_11', 11),
('Item_12', 12), ('Item_13', 13), ('Item_14', 14);

Run this SQL:
SELECT
  t.sale_id,
  t.price,
  CASE
    WHEN t.rn_up <= 3 THEN 'Low priced'
    WHEN t.rn_down <= 3 THEN 'High priced'
  END as price_range
FROM
(
  SELECT
    s.sale_id,
    s.price,
    ROW_NUMBER() OVER (ORDER BY s.price ASC) AS rn_up,
    ROW_NUMBER() OVER (ORDER BY s.price DESC) AS rn_down
  FROM sale s
) AS t
WHERE t.rn_up <= 3 OR t.rn_down <= 3
ORDER BY t.price ASC;

Result:
sale_id price     price_range
Item_1      1      Low priced
Item_2      2      Low priced
Item_3      3      Low priced
Item_12    12     High priced
Item_13    13     High priced
Item_14    14     High priced
6 rows

If you want to deal with ties - I've added a record:
INSERT INTO sale (sale_id, price) VALUES  ('Item_43', 13);

And now, the results are (more or less) arbitrary - I run the same query and I get the same results - I reckon it may be the INSERTion order of records that have a price of 13 (see the fiddle).
What you can do then is use DENSE_RANK() as follows:
SELECT
  t.sale_id,
  t.price,
  CASE
    WHEN t.rn_up <= 3 THEN 'Low priced'
    WHEN t.rn_down <= 3 THEN 'High priced'
  END as price_range
FROM
(
  SELECT
    s.sale_id,
    s.price,
    DENSE_RANK() OVER (ORDER BY s.price ASC) AS rn_up,
    DENSE_RANK() OVER (ORDER BY s.price DESC) AS rn_down
  FROM sale s
) AS t
WHERE t.rn_up <= 3 OR t.rn_down <= 3
ORDER BY t.price ASC, sale_id;

Result:
sale_id price   price_range
 Item_1     1   Low priced
 Item_2     2   Low priced
 Item_3     3   Low priced
Item_12    12   High priced
Item_13    13   High priced    --  <<=== Note: now we have 2 items with price 13!
Item_43    13   High priced
Item_14    14   High priced

As mentioned in comments, what exact SQL statement you'll use will depend on your  requirements  - I would just urge you to spend some time studying window functions - they'll repay that time ten times over!


If I understand correctly you want to totally remove replication from the server.

There are a number of tools you can use, as outlined here for limited dismantling of replication.

Removing Replication

If you just want to strip all replication from the server, you can apparently use sp_removedreplication on each database that needs to have replication dropped.

sp_removedbreplication (Transact-SQL) on the appropriate database

The code is simple.  Here is the MSDN example code:

-- Remove replication objects from the subscription database on MYSUB.
DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'AdventureWorks2008R2Replica'

-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO


Understanding Simple MongoDB Replication Works

You can play with read preference modes.
Basically this will instruct your application to read from secondary members of the replica set.
The default behaviour is to read from primary.
Be aware that if you use asynchronous replication, data in the secondary may not reflect the most recent write operations.

More details here:
https://docs.mongodb.com/manual/core/read-preference/


How to cache inserted data with ProxySQL to avoid delays

Adding delay of 10 milliseconds before fetching a freshly inserted record solves the issue, and it has been implemented as a temporary fix, but I hope there is a prettier solution to it.


Partial rollback doesn't decrement trancount

@@TRANCOUNT reports a count of BEGIN TRANSACTION statements, not active transactions. From a different perspective, it is reporting the depth of a nested transaction.

  @@TRANCOUNT (Transact-SQL)  Returns the number of BEGIN TRANSACTION
  statements that have occurred on the current connection. [source]

Misunderstandings of nested transactions and savepoint usage arise because nested transactions are not what we would expect them to be. To all intents and purposes there is no such thing as a nested transaction. The actions of a set of nested transactions are not committed until you issue the outer most COMMIT and a ROLLBACK will undo the actions of all.

Nested transactions exist to support transactions in procedures which could be called from external processes that may have started a parent transaction, from parent procedures that may have done the same, or where no prior transaction exists. They do not provide nested commit and rollback behaviour.


Location of #th iteration of a character or a word in a sql text field

There's no specific support for this in Transact-SQL.

You could write your own function using CHARINDEX.

Alternatively, you could use a library that someone else has already written, for example the SQLsharp SQLCLR extension library. Among the over 240 functions and procedures is SQL#.String_NthIndexOf:

-- Result: 13
SELECT SQL#.String_NthIndexOf
(
    N'abc abc abc abc abc',  -- Input string
    N'abc',                  -- Search for this
    1,                       -- Starting at position
    4,                       -- Nth occurrence
    1,                       -- Case sensitive
    0                        -- Don't count overlaps
);

I have no association with this (mostly free) product, I am just a fan.


issues with creating varchar returning functions

From the documentation:

      --Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
    [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

So, the return statement must be the last one. Use a variable to store your return value and add in the end return @retval.


Efficient way to get last 10 rows of each group in SQLite, can CTE help?

Since the number of distinct job_id is small, you can try this way of writing this type of queries. I've often used in MySQL that doesn't have window functions - and you can't even have a subquery with a IN (SELECT ... LIMIT x). Try this with an index on (job_id, run_number, status):

SELECT
    jr.*
FROM
    ( SELECT DISTINCT job_id
      FROM job_runs
    ) AS jrd
  JOIN
    job_runs AS jr
    ON  jr.job_id = jrd.job_id
    AND jr.status <> 'running'
    AND jr.run_number >= COALESCE(
        (
            SELECT jri.run_number
            FROM job_runs AS jri
            WHERE jri.job_id = jrd.job_id
              AND jri.status <> 'running'
            ORDER BY jri.run_number DESC
            LIMIT 1 OFFSET 9
        ), -2147483647)
    ;

and this (with same index):

SELECT
    jr.*
FROM
    ( SELECT DISTINCT job_id
      FROM job_runs
    ) AS jrd
  JOIN
    job_runs AS jr
    ON  jr.run_id IN
        (
            SELECT jri.run_id
            FROM job_runs AS jri
            WHERE jri.job_id = jrd.job_id
              AND jri.status <> 'running'
            ORDER BY jri.run_number DESC
            LIMIT 10
        )
    ;


Procedure MySQL cursor taking a long time to execute

IN ( SELECT ... ) is often poorly optimized.  See if you can turn it into either a JOIN or EXISTS ( SELECT 1 ... )
Don't use a CURSOR if it is possible to write a JOIN or something to do the entire task rather than acting one row at a time.
PARTITIONing is unlikely to be beneficial; let's see the details.  (Composite indexes often do the job adequately.)
Remove the ORDER BY in subqueries; it has no effect.  If you need ordering, please elaborate; we should be able to find another way.
Are there parameters being fed in?  Which?


SQL Server: backup error 112: not enough space on the disk - true or not?

Ok, my bad. :-/  SQL Server is correct on both accounts.

The weekly full backup starts at 6 PM on Sundays, and the daily incremental backup starts at 8 PM. The weekly backup is still running when the daily incremental starts. The daily incremental records a .bak file that is the same size as the full, because ?? all indexes, statistics and so on have changed.

At this point, or rather, some two hours later, when it has finished reorganizing indexes and updating statistics, there isn't enough disk left for the full backup.

The incremental backup that I checked with RESTORE VERIFYONLY is ok.
The full backup was never created.

So now I have to choose between some options:

start the Full backup a few hours earlier,
skip the incremental on Sundays,
(suggestions welcome ... )


How do I create a file from a result set?

There's two routes to go through.

Firstly, Server-side. This is where a process on the machine hosting the DB will write the data to its file system using UTL_FILE. A DIRECTORY entry needs to be created in the database by a DBA pointing to a directory on the file system which can be written to by the OS process running the database (generally 'oracle')

Secondly, client-side. This is where a client program (eg SQL*Plus, Java, Python, Excel...) connects to the database, pulls the required data across to the client and writes it to a file on the client's file system.

You can have the client and server on the same machine, or a shared directory/file system that is accessible to both a client and server (one side needs to be able to read/write, the other can just be read).

You need to decide which best fits your situation, including

where you want the file to end up
whether the task will be invoked manually or through a scheduler/automation
how to manage and deploy changes to the script/code that generates the file


Is individual column and row-level encryption possible in SQL Server?

Always Encrypted feature of SQL Server 2016 onward supports individual column level encryption but not row level.

Instead of row level encryption, Row Level Security feature of SQL Server supports restricting access of particular set of records in the table.

Thanks!


Quorum configuration warning for cross subnet AAG failover

I had removed the node weight of the 3rd node. Am I missing something here?

You're receiving the informational message because you've removed the possible vote (node weight of 0) of the replica. Since it won't ever have a vote, it also won't ever be able to stand on its own should any issues arise with the cluster, since this effectively removed the dynamic quorum aspect of clustering when this node owns anything.

This truly may be what you want, for example as a DR node whose availability you don't want impacting the production nodes. In that case, as long as you understand the situations you're protecting against and meeting those then I don't see a problem setting it like this (which is effectively that you know better than the cluster).


Why do you have to execute a full backup after a new database for trans backup jobs to run

do a manual full backup and then it will run. Why is this?

This is because any database you create with full recovery model is always in pseudo simple recovery mode until you take a full database backup and this is where a log backup chain can be establish successfully.

You can easily check if a database is in pseudo simple recovery mode using DBCC DBINFO () WITH TABLERESULTS** --> check for field dbi_dbbackupLSN with value of 0:0:0 (0x00000000:00000000:0000). This means that the log chain has not been established.

Paul Randal has a TSQL script and Edwin Sarmiento  has a PowerShell script that will tell you if a database is in pseudo simple recovery or not.

You can use the scripts from above links to first check if the database is in pseudo simple recovery and if the database is then execute a full backup else run diffs or log backups.

** DBCC DBINFO is undocumented.


passing variable values to procedure in postgresql via psql

That won't work, because the $$ that surround the body of your DO statement delimit a string literal, just like single quotes. And psql won't interpolate variables in a string literal.
You should write a PL/pgSQL function and pass the variable as argument in the function call.  If you need the function only temporarily, you can use the undocumented feature of a temporary function: create the function in schema pg_temp, and it will vanish automatically when your database session ends.


How to find what tablespace a table/index is in on PostgreSQL?

For example, you have a table example_table (optionally in more than one schema):

SELECT tablespace
FROM pg_tables
WHERE tablename = 'example_table' [AND schemaname = 'your_schema'];

The same thing for the index example_index:

SELECT tablespace
FROM pg_indexes
WHERE indexname = 'example_index' [AND schemaname = 'your_schema'];


DB2 TRUNCATE command - DROP STORAGE option

You can use LIST TABLESPACES SHOW DETAIL to check the High Water Mark. If it still appears high, you can always issue an ALTER TABLESPACE <tablespace> REDUCE MAX (assuming you are on automatic storage). The commands for old-style DMS are a litte different.

I have another answer where I list that information


Assuming dates in other months are truncated to 3-char months:

 ALTER TABLE dbo.tablename ADD newcol DATETIME;

 UPDATE dbo.tablename
   SET newcol = CONVERT(DATETIME,
    REPLACE(LEFT(varchar_col, 11), '/', ' ')
        + ' ' + RIGHT(varchar_col, 8), 113);

Once you've verified, you can drop the varchar_col, rename newcol, and then re-add any affected indexes.


Andreas got this answer first in terms of what to do. +1 for Andreas !!!

I would like clarify why that answer is the only way and how to do it.

By default ibdata1 normally houses four types of information

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
Table Metadata

Running OPTIMIZE TABLE against an InnoDB table stored ibdata1 will make things worse because here is what it does:

Makes the table's data and indexes contiguous inside ibdata1
It makes ibdata1 grow because the contiguous data is appended to ibdata1

You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table. That way, only MVCC and Table MetaData would reside in ibdata1.

If you already use it, then you must have a high-write environment that stores lots of MVCC to support transaction isolation. Once the transactions holding the MVCC is done, the space is simply abandoned for re-use.

To shrink ibdata1 once and for all you must do the following:

STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

STEP 02) Drop all databases (except mysql schema)

STEP 03) service mysql stop

STEP 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

STEP 05) rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

At this point, there should only be the mysql schema in /var/lib/mysql

STEP 06) service mysql start

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

STEP 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable; and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I would like to suggest a formula for you to query on sizing your InnoDB Buffer Pool from my post on What are the main differences between InnoDB and MyISAM?


The transaction log for the database fills up overnight everyday

I think your problem could actually be that you only want to backup and shrink your transaction log once per day.
If you don't need Transaction Log Backups to restore to any point in time, then switch your recovery model to "Simple" and stop doing log backups altogether.
If you do need Transaction Log Backups, then you usually have a need to take more than 1 in a day. SQL Server will track every change in the Transaction Log until the Transaction Log is backed up, so maybe try scheduling these to run more often.
Before you backup and shrink the file, or whatever you're doing each day, you can run the following command to tell you what it's waiting for:
SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases;
Given that backing up the transaction log fixes your problem, that's where I'd put my money. But other things that can cause the Transaction Log to continue to grow are Mirroring/AlwaysOn replicas falling behind, long running transactions (which will affect Simple recovery model as well). The above query will help you find the problem.
Finally, having a large transaction log is not actually a problem. You shouldn't shrink the file every night, it's just going to grow again tomorrow. If log_reuse_wait = 0 then just let it be. If log_reuse_wait is not 0 then check before you clear the log what it's waiting on. Microsoft Docs has the full list of codes.


To normalize or not to normalize for few distinct values

If you are implementing party-role-relationship model or part of it, having party_type as a separate entity is very important. You may have many more tables with foreign key to party_type (for instance, to limit applicability of certain roles to particular party type[s], or to enforce relationships between different types of parties, etc).  Using check constraints in any form (including enums) in such cases would hide the logic and make maintenance harder .


Intermittent only_full_group_by errors in AWS Aurora MySQL cluster db

If only_full_group_by is enabled
All Columns of a SELECT must be in the GROUP BY or use a aggregation function
so simply a is in the GROUP BY and b uses an aggregation function
SELECT
 a, MIN(b)
FROM
 mytable
GROUP BY a

So you have now to check all queries with GROUP BY and "correct" them.
see also the manual
Try all queries, if they give the correct answer, that will cost some time.


MySQL Security - Is there an easy way to encrypt a confidential data and also that it cannot view or access by DBA?

Traditionally this is done using a multi-tier architecture where the Application tier performs encryption before storing data in the Database tier.  The database administrators have access to dump all the data, but they don't have the keys.  The application administrators have the keys, but can't dump all the data out of the database.  Logging and auditing helps ensure that the application administrators don't try to extract mass data using their limited access.


Avoiding MySQL Fabric to become a single point of failure

The MySQL Fabric node itself is not currently redundant but as stated in the MySQL Fabric FAQ:

  There is currently only a single instance of the MySQL Fabric node. If that process should fail then it can be restarted on that or another machine and the state and routing information read from the existing state store (a MySQL database) or a replicated copy of the state store.

  While the MySQL Fabric node is unavailable, Fabric-aware connectors continue to route queries and transactions to the correct MySQL Servers based on their cached copies of the routing data. However, should a Primary fail, automated failover will not happen until the MySQL Fabric node is returned to service and so it's important to recover the process as quickly as possible.

In terms of best practices, this MySQL Fabric blog post provides an end-to-end worked example of using MySQL Fabric and then the MySQL Fabric white paper adds more of an overview and context information.

Finally, you can watch the MySQL Fabric webinar replay and read Q&A transcript - there are some good, technical questions + responses there.

If you're looking for more discussion on the topic then there's also a dedicated forum for MySQL Fabric, Sharding, HA & Utilities.


How to filter values from different table inner join and group by

Probably someone can more optimally write this but it is fast:

  --1.
    SELECT alll.Date, alll.InventoryId, alll.ItemNameCode, SUM(alll.CurrentQuantity) AS CurrentQuantity, SUM(alll.InwardQuantity) AS InwardQuantity, SUM(alll.OutwardQuantity) AS OutwardQuantity FROM (
    SELECT BASE_GoodsInward.GoodsInwardDate AS "Date", BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode, SUM(BASE_Inventory.CurrentQuantity) AS CurrentQuantity, 0 AS InwardQuantity, 0 AS OutwardQuantity  FROM BASE_Inventory
    INNER JOIN BASE_GoodsInwardLine ON BASE_Inventory.InventoryId = BASE_GoodsInwardLine.InventoryId
    INNER JOIN BASE_GoodsInward ON BASE_GoodsInwardLine.GoodsInwardId = BASE_GoodsInward.GoodsInwardId
    GROUP BY BASE_GoodsInward.GoodsInwardDate, BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode
    UNION ALL
    SELECT BASE_GoodsOutward.GoodsOutwardDate AS "Date", BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode, SUM(BASE_Inventory.CurrentQuantity) AS CurrentQuantity, 0 AS InwardQuantity, 0 AS OutwardQuantity  FROM BASE_Inventory
    INNER JOIN BASE_GoodsOutwardLine ON BASE_Inventory.InventoryId = BASE_GoodsOutwardLine.InventoryId
    INNER JOIN BASE_GoodsOutward ON BASE_GoodsOutwardLine.GoodsOutwardId = BASE_GoodsOutward.GoodsOutwardId
    GROUP BY BASE_GoodsOutward.GoodsOutwardDate, BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode
    UNION ALL
    SELECT BASE_GoodsInward.GoodsInwardDate AS "Date", BASE_GoodsInwardLine.InventoryId, BASE_Inventory.ItemNameCode, 0 AS CurrentQuantity, SUM(BASE_GoodsInwardLine.InwardQuantity) AS InwardQuantity, 0 AS OutwardQuantity
    FROM BASE_GoodsInwardLine
    INNER JOIN BASE_GoodsInward ON BASE_GoodsInwardLine.GoodsInwardId = BASE_GoodsInward.GoodsInwardId
    INNER JOIN BASE_Inventory ON BASE_GoodsInwardLine.InventoryId = BASE_Inventory.InventoryId
    GROUP BY BASE_GoodsInward.GoodsInwardDate, BASE_GoodsInwardLine.InventoryId, BASE_Inventory.ItemNameCode
    UNION ALL
    SELECT BASE_GoodsOutward.GoodsOutwardDate AS "Date", BASE_GoodsOutwardLine.InventoryId, BASE_Inventory.ItemNameCode, 0 AS CurrentQuantity, 0 AS InwardQuantity, SUM(BASE_GoodsOutwardLine.OutwardQuantity) AS OutwardQuantity
    FROM BASE_GoodsOutwardLine
    INNER JOIN BASE_GoodsOutward ON BASE_GoodsOutwardLine.GoodsOutwardId = BASE_GoodsOutward.GoodsOutwardId
    INNER JOIN BASE_Inventory ON BASE_GoodsOutwardLine.InventoryId = BASE_Inventory.InventoryId
    GROUP BY BASE_GoodsOutward.GoodsOutwardDate, BASE_GoodsOutwardLine.InventoryId, BASE_Inventory.ItemNameCode
    ) alll
    GROUP BY alll.Date, alll.InventoryId, alll.ItemNameCode
    ORDER BY alll.Date, alll.InventoryId, alll.ItemNameCode;

    --2.
    SELECT alll.InventoryId, alll.ItemNameCode, SUM(alll.CurrentQuantity) AS CurrentQuantity, SUM(alll.InwardQuantity) AS InwardQuantity, SUM(alll.OutwardQuantity) AS OutwardQuantity FROM (
    SELECT BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode, SUM(BASE_Inventory.CurrentQuantity) AS CurrentQuantity, 0 AS InwardQuantity, 0 AS OutwardQuantity  FROM BASE_Inventory
    INNER JOIN BASE_GoodsInwardLine ON BASE_Inventory.InventoryId = BASE_GoodsInwardLine.InventoryId
    INNER JOIN BASE_GoodsInward ON BASE_GoodsInwardLine.GoodsInwardId = BASE_GoodsInward.GoodsInwardId
    GROUP BY BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode
    UNION ALL
    SELECT BASE_GoodsInwardLine.InventoryId, BASE_Inventory.ItemNameCode, 0 AS CurrentQuantity, SUM(BASE_GoodsInwardLine.InwardQuantity) AS InwardQuantity, 0 AS OutwardQuantity
    FROM BASE_GoodsInwardLine
    INNER JOIN BASE_GoodsInward ON BASE_GoodsInwardLine.GoodsInwardId = BASE_GoodsInward.GoodsInwardId
    INNER JOIN BASE_Inventory ON BASE_GoodsInwardLine.InventoryId = BASE_Inventory.InventoryId
    GROUP BY BASE_GoodsInwardLine.InventoryId, BASE_Inventory.ItemNameCode
    UNION ALL
    SELECT BASE_GoodsOutwardLine.InventoryId, BASE_Inventory.ItemNameCode, 0 AS CurrentQuantity, 0 AS InwardQuantity, SUM(BASE_GoodsOutwardLine.OutwardQuantity) AS OutwardQuantity
    FROM BASE_GoodsOutwardLine
    INNER JOIN BASE_GoodsOutward ON BASE_GoodsOutwardLine.GoodsOutwardId = BASE_GoodsOutward.GoodsOutwardId
    INNER JOIN BASE_Inventory ON BASE_GoodsOutwardLine.InventoryId = BASE_Inventory.InventoryId
    GROUP BY BASE_GoodsOutwardLine.InventoryId, BASE_Inventory.ItemNameCode
    ) alll
    GROUP BY alll.InventoryId, alll.ItemNameCode


Postgresql 9 speeding up indexed inserts (JPA)

In addition to Craig's advice I would like to advise you to examine the storage parameters of the affected tables.

I am currently in a similar situation to yours. The largest table in my system contains ~200 million records and the performance was really bad.

Tune the storage parameters of your tables and indexes

Besides adding several indexes to the database, I changed the storage parameters of some tables and specified a custom value for the fillfactor of the table itself and the indexes.

Setting a custom value for the fillfactor allows you to instruct PostgreSQL how much space in each page should be reserved for further updates. The same applies to indexes.

See the documentation on CREATE TABLE and the description of the available storage parameters for details.

Monitor your infrastructure

Monitor and analyze your infrastructure. The PostgreSQL wiki lists a lot of usefull tools.

Find long running statements

Enable statement logging by altering the following values in your postgresql.conf file:

log_min_duration_statement=x to log all statments which run longer the x milliseconds
log_min_messages=level to a level what helps you the understand the statements generated by JPA

See the description of the runtime logging configuration for details

Install pgFounine to analyse your PostgreSQL log file easily.

Be picky

Besides altering the storage parameters I also gained a lot of performance by optimizing all frequently executed statements. In parts I won only 100 or 50 milliseconds for each execution but in total I gained more then 5 seconds for complex operations.


Index usage stats DMV indicates no index activity

One possibility would be that you don't have sufficient permissions to view the metadata.

Another is that sys.dm_db_index_usage_stats only reports on indexes that have been used at least once since the server was last restarted. Also

  whenever a database is detached or is shut down (for example, because
  AUTO_CLOSE is set to ON), all rows associated with the database are
  removed.

AUTO_CLOSE is the default for SQL Server Express.

You need to filter sys.dm_db_index_usage_stats by database_id to determine if it has metadata loaded for a particular database also the join should include index_id

SELECT *
FROM   sys.indexes i
       LEFT JOIN sys.dm_db_index_usage_stats s
         ON i.object_id = s.object_id
            AND i.index_id = s.index_id
            AND s.database_id = DB_ID()


Why does my query hang when I put one more id in where statement?

You are probably exhausting some resource (adequate resource settings?) and Postgres starts swapping out to disk. And / or, more likely, the query planner switches to a different query plan, based on your cost settings (which may be configured inadequately) and table statistics (which may be outdated).

I can't be more specific, information is missing.

All these possible problems in your setup aside, assuming customer.id is a unique column, this equivalent query with a JOIN replacing in the IN (subquery) should be considerably faster:

SELECT count(*)
FROM   (SELECT id FROM customer LIMIT 128) c  -- arbitrary rows! see below
JOIN   joins j ON j.customer_id = c.id        -- resolve "joins" properly
WHERE  -- ... (more expressions)

Or put the subquery in a CTE. The important point is the join instead of IN. Like:

WITH cids AS (SELECT id FROM customer LIMIT 128)  -- arbitrary rows!
SELECT count(*)
FROM   cids c
JOIN   ...

Adapt the join clause, depending on what's behind (joins) in your original query.

Plus, be aware that LIMIT without ORDER BY selects arbitrary rows. So due to internal effects, the subquery with LIMIT 128 can return completely different IDs from the one with LIMIT 129 (even for the same LIMIT), which can result in a completely different count. Is that what you want?

Related:

SQL sorting by 0
Complex view becomes slow when adding ORDER BY in outer query with small LIMIT


SERVICE_NAMES specifies one or more names by which clients can connect to the instance. The instance registers its service names with the listener. When a client requests a service, the listener determines which instances offer the requested service and routes the client to the appropriate instance.

You can specify multiple service names in order to distinguish among different users of the same database. For example:

SERVICE_NAMES = sales.acme.com, widgetsales.acme.com

You can also use service names to identify a single service that is available from two different databases through the use of replication.

If you do not qualify the names in this parameter with a domain, Oracle qualifies them with the value of the DB_DOMAIN parameter. If DB_DOMAIN is not specified, then no domain will be applied to the non-qualified SERVICE_NAMES values.

DATABASE NAME is the name of the physical database structure. It is stored at the controlfile and datafile header. It is used to identify all physical structures that belong to the same database. It can be defined at install time. It is originally defined by the static instance parameter database_name, and it cannot be changed, and the only way to change it is by means of rebuilding the controlfile and resetting the log sequence.

To conclude, you can have a common name for both of them. However, you might need different service names to distinguish among different users of the same database.


I am trying to get the string between hyphen (-) which is before (+) upto next colon (:)

SELECT txt,
       SUBSTRING(txt, CHARINDEX('-', txt), CHARINDEX(':', txt, CHARINDEX('-', txt)) - CHARINDEX('-', txt))
FROM test

or

SELECT txt,
       SUBSTRING(txt, CHARINDEX('- +', txt), CHARINDEX(':', txt, CHARINDEX('- +', txt)) - CHARINDEX('- +', txt))
FROM test

fiddle


Should tempdb be larger on a secondary replica?

On secondary node, when used for read-only query, SQL enable ReadCommited Snapshot behind the scene.
If you have long running transaction that does read, that could explain why you used a lot more tempdb on that secondary node.


Okay I figured it out. No Need for Delimiters because it is technically one line or one statement and only needs one semicolon. Also make sure to notice that one is for before an Insert and one before an Update. I believe that SET was also necessary.

CREATE TRIGGER CREATE_BY_MENU_TR
BEFORE INSERT ON MENU
FOR EACH ROW
SET NEW.Create_By = CURRENT_USER();

CREATE TRIGGER CREATE_BY_MENU_TR
BEFORE UPDATE ON MENU
FOR EACH ROW
SET NEW.Last_Update_By = CURRENT_USER();

The Create_By and Last_Update_By are just columns of data type varchar.


MariaDB - how to drop partition on master but not on slave

You could run SET SESSION sql_log_bin=0 in the session before running the ALTER TABLE...


Use the information stored in INFORMATION_SCHEMA:

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables
ORDER BY table_schema, table_name;

table_schema is the database name.

To show also the databases without any tables:

SELECT s.schema_name, t.table_name
FROM INFORMATION_SCHEMA.schemata AS s
  LEFT JOIN INFORMATION_SCHEMA.tables AS t
    ON t.table_schema = s.schema_name
-- optional, to hide system databases and tables
-- WHERE s.schema_name NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY schema_name, table_name ;

Documentation links: INFORMATION_SCHEMA.tables,  INFORMATION_SCHEMA.schemata


SQL Server script to delete accounts no longer in Active Directory

What I ended doing is listing the accounts with:

    exec sp_validatelogins

And running

    exec sp_dropuser loginname
    exec sp_droplogin loginname

on the results.