T-SQL - How to split (char separated) string into rows and columns

It is not neccesary to modify Jeff Moden's function. I have devised a little bit sophisticated way of achieving the result just using the original function.

First Here it is the original Moden's function:
(Source: http://www.sqlservercentral.com/articles/Tally+Table/72993/)

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

And the query for obtaining the required result (I have created a CTE just for clarity)

DECLARE @string VARCHAR(8000)
SET @string = '!1;100;10;200;0;500;2;1000;30!2;100;3;500;1;2000;5'

;WITH CTE AS
(
SELECT
SUBSTRING(A.Item,1,CHARINDEX(';',A.Item)-1) As Id,
B.Item
FROM dbo.DelimitedSplit8K(@string, '!') AS A
CROSS APPLY dbo.DelimitedSplit8K(A.Item, ';') AS B
WHERE A.Item <> '' --to avoid blank first row because the first !
)

SELECT Id, [Value], [Count]
FROM
(
SELECT Id, Item As [Value],
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id) As RowNumber,
LEAD(Item,1,0) OVER(PARTITION BY Id ORDER BY Id) As [Count]
FROM CTE
) As T
WHERE RowNumber % 2 = 0

EDITED:
I realized that my solution assummed that id value was ordered or that the order in the original string was not neccesary to be preserved in the result. Below the general solution (note that I changed the first number in the string for testing the situation)

DECLARE @string VARCHAR(8000)
SET @string = '!3;100;10;200;0;500;2;1000;30!2;100;3;500;1;2000;5'
;WITH CTE AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT 0)) As RN,
CAST(SUBSTRING(A.Item,1,CHARINDEX(';',A.Item)-1) AS Varchar(400)) As Id,
B.Item
FROM dbo.DelimitedSplit8K(@string, '!') AS A
CROSS APPLY dbo.DelimitedSplit8K(A.Item, ';') AS B
WHERE A.Item <> '' --to avoid blank first row because the first !
)

SELECT Id, [Value], [Count]
FROM
(
SELECT Id, Item As [Value],
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id) As RowNumber,
LEAD(Item,1,0) OVER(PARTITION BY Id ORDER BY Id) As [Count]
,FIRST_VALUE(RN) OVER(PARTITION BY Id ORDER BY Id) AS FV
FROM CTE
) As T
WHERE RowNumber % 2 = 0
ORDER BY FV

http://sqlfiddle.com/#!6/31ccd/3


Creating an Index based on Execution Plan

You may find this answer to be helpful. The index contains all needed columns for the query so it can be used by the query. However, the order of the columns does not allow for as much filtering as possible using the index keys. About 14.3 million rows are located in the index using an index seek but almost 12 million of those rows are discarded after the predicate filter is applied. It's also worth mentioning that the table only has 10.7 million rows. Using nested loops and an index to read 14 million rows from a table with 10.7 million rows may not be the best choice. With that said, you can make the index seek more efficient by changing the index key order to the following:

PolicyNumber
CovPerilId
TransactionId
All other columns

Changing the key order of the index may impact other queries in positive or negative ways. I can't say if it's the right decision to make overall for the server's workload.
I noticed a few other things while looking at your query plan:

About 164 seconds (38% of total execution time) was spent during query execution waiting to send results to the client. Returning almost a million rows to the SSMS results grid can take a while. Be sure to run your query in a way most similar to what the application does to get the most accurate tuning results. You may find it useful to discard to query result set in SSMS while doing performance testing.
About 42 seconds (10% of total execution time) was spent waiting on CPU. The query will complete faster if it can be changed to use less CPU, if other queries on the server can be used to use less CPU, or if the total core count of the server is increased.
There are other index seeks in the plan where more rows are read from the index than are present in the corresponding table. '[AfterShock].[dbo].[LineOfCoverage].[LineOfCoverage11]' in node 53 is one example. A different index or join type may improve performance of the query.
There are some poor cardinality estimates that lead to tempdb spills. Explaining why is outside the scope of this Q&A. The query is fairly complex so perhaps splitting it into multiple queries that use temp tables will result in better performance.


Connection timeout errors from Postgres Update Monitor (postgres 9.6)

While this answer does not resolve the current question, I think it could be useful for someone searching about Postgres timeout issues on DBA.SE or across the Internet.

According to Postgres documentation, you can use:

  statement_timeout (integer)

  Abort any statement that takes more than the specified number of
  milliseconds, starting from the time the command arrives at the server
  from the client. If log_min_error_statement is set to ERROR or lower, the
  statement that timed out will also be logged.
  A value of zero (the default) turns this off.

  Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.

That means that you can set it in postgres.conf or you can use it in pgAdmin.

I've found an example in Postgres community.

set statement_timeout to 1000;
select pg_sleep(2);

  ERROR:  canceling statement due to statement timeout

You can also have different timeout settings per user:

ALTER USER postgres SET statement_timeout=0

And even using a client session via libpq:
From Postgres-XL

  When starting a client session via libpq, parameter settings can be
  specified using the PGOPTIONS environment variable.
  Settings established in this way constitute defaults for the life of the
  session, but do not affect other sessions. For historical reasons, the
  format of PGOPTIONS is similar to that used when launching the postgres
  command; specifically, the -c flag must be specified. For example,

env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql


insert multi on a table handled by trigger

You have a contradiction -- On the one hand, you want to "ignore" dups.  On the other hand, you want to "SIGNAL" about dups.  Which is it?

If you want to signal after trying to insert multiple rows, you won't know which row(s) were dups.  If that is OK, then consider something like (I'm not sure of syntax):

DECLARE _dups INT DEFAULT FALSE;
FOR EACH ROW
  BEGIN
  IF NOT EXISTS (SELECT 1 FROM users WHERE md5_email = MD5(TRIM(NEW.email))) THEN
    SET NEW.md5_email = MD5(TRIM(NEW.email));
  ELSE
    SET _dups = TRUE;
  END IF;
  IF _dups THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Some Emails Already Exists';
  END IF;
END $$

Alternatively, you could probably use rows_affected() to see how many were successfully inserted.


When trying to connect to an external postgres database using pgAdmin, I get an error

The cause of the is the bold part, the host "":

  "FATAL: no pg_hba.conf entry for host "", user "postgres", database "postgres", SSL off"

The database, from which you get the error, see a local connection, and not a networked one.

How can it happen, it depends on your system configuration, what you didn't share here.


Problems Exporting Long Columns to Flat File Using SSIS

Try clicking the Unicode Checkbox

Try the following 65001 code page and Unicode checkbox, it got rid of the error message for me. Hopefully we're on similar versions of SSDT. My Version is 15.

But first, double-click your flat file destination, and click the Update button...


Install standalone instance on cluster server with SQL Server

It is a hell of a bad idea, but it might work. I have done something similar in the past because it was the only possible solution and I can confirm that it works.

The only thing to be careful about is the versions: you will probably get warnings about installing sql server 2008 on Windows 2016. Also, keep in mind that it's not supported (which doesn't mean it won't work).

However, this is something I would never do in production: do yourself a favour and find a different machine for that SQL Server 2008 standalone instance: at least, you won't get shared features incompatibilities and the like.


Redshift: do with clauses /CMTs ALWAYS create temporary tables?

If you want to know what is going to do the planner. I would suggest to do
EXPLAIN

I would say your planner will execute both CMT


Is the [NT Service\SQLSERVERAGENT] a supported type for SQL Server Agent service?

NT Service\SQLSERVERAGENT is the name of an account, not an account type.

The document refers to account types.  As @Jon-Seigel said, it qualifies as a 'Local non-administrative user'.


how to select a value in one table with max() to update a row in a second table, with a trigger in PostgreSQL

The immediate error is that you have num where it would have to be NEW.num. But there are more problems ...

I add : In fact, whatever I do (insert or update) the max num by gidTbl1 (tbl2) always has to have the pm corresponding in tbl1.

Trying to get this right with triggers is a pain, even without possible concurrent writes.
With possible concurrent writes, it's only even possible with expensive locks (or with SERIALIZABLE snapshot isolation). You can't have multiple concurrent transactions messing with the max num at the same time.
You could force all write access on tbl2 to first take out a row lock on the respective parent row in tbl1 (and keep track of the current max tbl2.num in an additional column tbl1.max_num while being at it) ...
See:

UPDATE n:m relation in view as array (operations)
Delete parent if it's not referenced by any other child

But I wouldn't go there. The safe way to achieve this is to drop the column tbl2.pb altogether. Instead use a VIEW (or MATERIALIZED VIEW) like this to dynamically get the current value, where you need it:
CREATE VIEW tbl2_plus_pb AS
SELECT t2.gid, t2.num, t1.pm AS pb, t2.gid_tbl1
FROM  (
   SELECT *, CASE WHEN max(num) OVER (PARTITION BY gid_tbl1) = num
                  THEN gid_tbl1 END AS link
   FROM   tbl2
   ) t2
LEFT   JOIN tbl1 t1 ON t1.gid = t2.link
ORDER  BY t2.gid;

db<>fiddle here


Vastly different query run-times on different servers

Many reasons are affected to your result i try to explain some important of :

  Note

  At First it's important to know how config your VMs many options are important like : reserved CPU ? Or reserve Memory Or Share it.
  How many Vms in your host and when you run your queries how many workloads your host has ? and many other reason like hypervisor config hypervisor queue and etc ...
  Of course it's too complex for explaining by few rows you can read this:
  https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/techpaper/sql_server_virtual_bp.pdf

  Cardinality Estimation (CE)

  After SQL Server 2012 CE is Change and use new algorithm it's one of most factor you have to pay attention when you want to check SQL Server 2012 Vs SQL Server 2016

  https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-ver15

But I explain about some important factor in SQL Server

CPU

CPU is very important, and config Maxdop and Cost threshold for parallelism are affected to your result.

and many things related to cpu ...

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option?view=sql-server-ver15

Memory

The Other important factor you have to check is memory , are your queries cash , limitation of memory and etc

and many things related to RAM ...

Disk I/O

I/O is very important in different way you can look at :

One side is what your disk IOPS is ? Or how many VMDK you are? how you config Filegroups in Vmdks and ...

the other side is How many Page IO you need to do your work in each server (related to check execution paln and Statistics , i will explain it)

TempDB

how do you config your Tempdb by how many files (it's can related to CPU) or where your TempDB is locate

https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/

Log File

how do you config your Log File How many VLF Generate What size of Log growing and ...

MDF/NDF File Config

how do you config your Data File growing ... , FileGroups and etc

Do you config Instant File Initialization
https://www.sqlshack.com/an-overview-of-instant-file-initialization-in-sql-server/

and many other reason of course ...

After ALL ...

Statistics

It's very important to check you Execution Plan (DO NOT COMPARE COST , CHECK WITCH OPERAND USE) in each server and how many page IO you need to complete your action.

check your statistics are update.

when you want to compare some scenario like yours, has baseline is very helpfull to get correct result ,test your senarios a lot and find a baseline to compare

Finally when you use Non-Cluster instead of Cluster Index
if i want to explain shortly : Cluster Index is structure of your data (actually is your data in B+Tree ...) when you remove it your table now is Heap and don't have Tree structure and don't need to sort , or other IO need , for this reason and many other reasons ... your bulk insert faster than before
https://www.sqlshack.com/clustered-index-vs-heap/


Vertica supports ANSI SQL-92 isolation levels with standard ACID properties. If a COPY is terminated, interrupted, or an error occurs, it will be rolled back.

By default, HP Vertica uses the READ COMMITTED isolation level.
The COPY command automatically commits itself and any current transaction (except when loading temporary tables). HP recommends that you COMMIT or ROLLBACK the current transaction before you use COPY. Please note that DDL statements are autocommitted.

Documentation


Can DBA create a public synonym for a table that has the same name as an existing table

The user table will take precedence over the public synonym.
The data from the user table would be shown.

Oracle's object name resolution is explained in the article Managing Object Name Resolution (Oracle Database Administrators Guide)

The following SQL code will explain the use case:

SQL> conn user1
Enter password:
Connected.

SQL> create table test as select * from user_objects where 1=2; --Creating first table with 0 rows
Table created.

SQL> create table conflicting_name as select * from user_objects where rownum<2; --Creating second table with 1 row
Table created.

SQL> select count(0) from test; --first table with 0 rows
  COUNT(0)
----------
         0

SQL> select count(0) from conflicting_name; --second table with 1 row
  COUNT(0)
----------
         1

At this stage you have the test table containing zero (0) rows and the conflicting_name table containing one (1) row.

Let's continue by creating a public synonym and querying conflicting_name:

SQL>  create public synonym conflicting_name for test; --public synonym with second table's name for first table
Synonym created.

SQL> select count(0) from conflicting_name; --second table still points to original table and public synonym not used
  COUNT(0)
----------
         1

The result shows you that the query is run against the table conflicting_name which contains one row and not against the public synonym conflicting_name which references the test table containing zero rows.

What happens with a different user? Let's try:

SQL> conn user2
Enter password:
Connected.

SQL> -- Connected to different user

SQL> select count(0) from conflicting_name; --now public synonym pointing to first table kicks in
  COUNT(0)
----------
         0

SQL> select count(0) from user1.conflicting_name; --can explicitely access original table bypassing public synonym
  COUNT(0)
----------
         1

Because of the public synonym, user2 will be querying the test table. Only if user2 directly queries the user1.conflicting_name table will the single row be returned.


Edited after a couple of comments by the question asker!:
Better idea for your design:
(If you're not familar with these diagrams, that's seven tables, with foreign key links as pictured [the three _Link tables all have two foreign keys referencing the data tables]. It probably looks like I'm overcomplicating things, but trust me, "tokenise once and store in the database" is so much more efficient than "tokenise data each time you retrieve it".)
Sample data, using a simple tweet (ignoring Users for now, it's the same concept though): "test tweet! #howdoesthiswork #newbie"
      Tweet
TweetID   WordCount
   1         2

Tweet_Word_Link
TweetID   WordID
   1         1
   1         2

      Word
WordID    Word
   1      test
   2      tweet

Tweet_Hashtag_Link
TweetID  HashtagID
   1         1
   1         2

      Hashtag
HashtagID  Hashtag
    1      #howdoesthiswork
    2      #newbie

This allows you to easily get all the users linked in a tweet:
SELECT U.UserName
FROM  User U
        INNER JOIN
      Tweet_User_Link TUL ON U.UserID = TUL.UserID
        INNER JOIN
      Tweet T ON TUL.TweetID = T.TweetID
WHERE T.TweetID = <your ID here>

(change table names as appropriate for Hashtags and Words)
Or, slightly more complicated, get the top 10 words for all tweets mentioning a specific hashtag:
SELECT TOP 10 W.Word, COUNT(*) AS WordCount
FROM  Word W
        INNER JOIN
      Tweet_Word_Link TWL ON TWL.WordID = W.WordID
        INNER JOIN
      Tweet T ON TWL.TweetID = T.TweetID
        INNER JOIN
      Tweet_Hashtag_Link THL ON THL.TweetID = T.TweetID
        INNER JOIN
      Hashtag H ON H.HashtagID = THL.HashtagID
WHERE H.Hashtag = <your hashtag here>
GROUP By W.Word

(I've used ANSI SQL syntax, I'm not 100% sure if MySQL supports all of ANSI SQL - but it should be close enough.)


Sounds like you may have deleted the certificate from the certificate store prior to unbinding it from SQL Server.  I haven't tested it yet, but I'm guessing that if you did that, no certificate would be shown in the SSCM dialog because it would be trying to find the certificate that matches the thumbprint stored in the registry, but wouldn't be able to find it.

You may be able to fix this by clicking on the "Clear" button in SSCM where the certificate is configured.  IF this will clear the thumbprint that is stored in the registry, then SQL Server will start, but it's possible that the "Clear" button won't work if it can't find the certificate.

If that doesn't work, check the Certificate value in the registry at:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.12.MSSQLSERVER\MSSQLSERVER\SuperSocketNetLib

The registry path will be different if you're using a named instance.

If it's not blank, then SQL Server will try to find the certificate that matches the thumbprint that is stored there.  If it doesn't find the certificate, then it fails to start.  If the certificate has been deleted and the Certificate value in the registry still contains a thumbprint, then just save what's there and then delete the thumbprint so that it's blank.  Once that is done, SQL Server should start.

Lastly, if you already have the new certificate, you should be able to install it in the certificate store, bind the service to it in SSCM, and SQL Server should start.


Error using triggers with SQL Server Profiler Tables

These are the messages that get logged:

  16943  Could not complete cursor operation because the table schema changed after the cursor was declared.
  19052  The active trace must be stopped before modification.

So it seems like this is an inherent issue with creating a trigger on the trace table (and I did not crack open a debugger to see exactly what Profiler is trying to do in this case). This has nothing to do with the fact that you're pulling spid values in the trigger, or using DBCC INPUTBUFFER, or using dynamic SQL. A simple trigger that just does PRINT N'hi'; also causes the same symptom: those two exceptions, the error dialog in Profiler, and the trace stops logging to that table altogether.

Perhaps you can just set up a trace that collects both the sort warning events and SQL:StmtCompleted and try to manually correlate them using sequence / transaction info or simple intuition. Or you could try my idea from above:

  You can always automate pulling in trace data to a table from a file once a server-side trace is up and running - I suspect such a table would have the ability to utilize such a trigger when Profiler is not involved.

It's not exactly as simple as it sounds, and it would have to poll quite frequently in order for the trigger to rely on the timeliness of subsequent inputbuffer data. I don't want to get too deep into the implementation details of that, it's just an off-the-cuff idea.

The other idea is to upgrade. :-)


In RDS digest function is undefined after creating pgcrypto extension

My coworker discovered the issue, in case anyone is having a similar problem, the issue was that pgcrypto had been installed into a schema that was not public.  So the query for reported installed extensions said it was installed, installing if not exists did not change its schema or install it to all schemas.  The final solution was to remove it from all schemas and install it directly in the public schema.


Hierarchical data: get all nodes from a hierarchy, including parents that don't match the where

For future posts, it's much better to provide CREATE TABLE and INSERT statements like:

create table tasks
(TaskId int not null primary key
,AssignedTo char(5) not null
,ParentTaskId int references tasks (taskid));

insert into tasks (TaskId, AssignedTo, ParentTaskId)
values (1, 'Jim', NULL);
insert into tasks (TaskId, AssignedTo, ParentTaskId)
values (2, 'Jim', 1);
insert into tasks (TaskId, AssignedTo, ParentTaskId)
values (3, 'Joe', 2);
insert into tasks (TaskId, AssignedTo, ParentTaskId)
values (4, 'John', NULL);

That said, you can use a recursive common table expression (CTE) as in:

with tree (ancestortaskid) as (
    select taskid from tasks where assignedto = 'Joe'
    union all
    select ParentTaskId from tree t join tasks s
        on t.ancestortaskid = s.taskid
)
select t.ancestortaskid
from tree;

I only had sql-server2017 to test with, but afaik, recursive cte should be availible in 2008 as well


Shreded system_health ExEvent not returning all node information

According to http://www.red-gate.com/community/books/accidental-dba [Troubleshooting SQL Server: A Guide for the Accidental DBA] this looks to be by design.

Changes to Extended Events in SQL Server Denali
At the time of the
final edits of this chapter, SQL Server Denali CTP3 was released, with
changes associated to how the Extended Events targets store XML data
inside of the value element of the Event XML  output. Listing 7.5
shows the use of the .value() method from XML in SQL Server, but in
Denali CTP3, a .query() method has to be used to retrieve the
deadlock graph from the Event XML output.

Additional information : Extended Events Changes in SQL Server 2012 – Event XML for XML data elements


Verify that the SQL Server Agent account has the necessary permissions to query the performance counters DMV (sounds like your agent account has recently been changed). According to the documentation:

  Requires VIEW SERVER STATE permission on the server.

So:

GRANT VIEW SERVER STATE TO [SQL Agent account];

Also make sure that as that login (or any login with VIEW SERVER STATE, such as sa), you can see results come back from the DMV. I have heard of many cases where the performance counters simply come up empty in SQL Server, and this dates back to SQL Server 2000 with sysperfinfo. Several sources suggest the following as a solution from the command line (followed by a reboot):

lodctr "<path to binn>\binn\sqlctr.ini"

Of course Microsoft closes most of these reports as no repro (which doesn't surprise me, as I haven't seen this symptom directly, and would have no idea how to reproduce the issue).

Missing sys.dm_os_performance_counters
sys.dm_os_performance_counters only showing XTP-related data (fix was applying a CU)

Also some advice in these threads, which you are welcome to try (these are quite long-standing and I'm not going to try and summarize and repeat the info here):

What should we do when the sql server performance counters are missing
How to revive SQL Server 2008 R2 performance counters?
sys.dm_os_performance_counters empty
Empty Sysperfinfo table and blank sql performance monitor counters


How monitor TEMP space allocation for a particular object type like Clob\Blob

If you are connecting to an Oracle instance 11.2+, you can fetch data from the column temp_space_allocated of the view v$active_session_history, which shows the amount of TEMP memory (in bytes) consumed by your session at the time the sample was taken.

Otherwise, you may look at the V$TEMPSEG_USAGE view, column blocks, and convert it to megabytes depending on your tbs block size.


How to transfer DDL changes between different stages?

There are some.  The most reliable I've seen so far (at least when the development process is also have to be considered) is having diffs.

Basically, all DB objects (tables, types, functions, etc.) are defined one by one in files, which are then under version control.  When you want to change something, you write a diff (ALTER TABLE, for example) and change the affected definition files, too.  The changed files and the diff itself have to be checked in, of course. Then you can roll out these diffs on the different stages, each time registering the diff with its unique name (which might be a ticket number with something else).

All this does not really need special software, your favourite DB client, the usual version control system and some small mechanism of registering the diffs is enough.  For the latter, you can use depesz's versioning schema.


database design for inventory from transactions

Ultimately, the answer will depend on what you can spend the time to do.

In the ideal world, I would recommend a DB design like the following:

stock_holder - identifies an entity that can provide or receive stock. This can actually be your existing table(s) for HO, branches, etc.; a new "bridge" table that allows you to treat each of the current entities as basically equals; or, a new table that actually holds all the data you currently have for these various entities. I assume that there may be different columns needed for different entities.
stock_exchange - at the core, holds: entity sending stock; entity receiving stock; and amount of stock. This would probably be a mutation of your current transaction table. An entry in this table would mean the sender's stock inventory is decremented by the amount, and the receiver's is incremented.

Of course, you have an entire existing working program that is probably not designed like this. And completely changing the DB design of a working system to handle what (at least seems) like a minor change is probably not practical.

So, another alternative is to set up a new table structure that still works with the existing application.

Take the data from the current tables, and figure out how to combine it into a stock_exchange table. Then, based on the stock_exchange table, create views that would replicate the existing tables. This would require that your version of the SQL language to allow INSTEAD OF triggers on views: you would create such triggers on each of your views, to make sure that inserts, updates, and deletes actually happen to stock_exchange. Then, the application keeps using the views for now; as opportunities to update the application arise, you can phase out the views.

This would allow for other oddities - emergency shipments direct from HO to a distributor, or something like that.


What is the StatementParameterizationType plan attribute?

The StatementParameterizationType attribute indicates the type of parameterization applied to the statement.

The values are documented in sys.query_store_query:

0 = None
1 = User
2 = Simple
3 = Forced

It appears on the root node of post-execution ("actual") plans only.

The Query Store must also be enabled to see this attribute (e.g. in SSMS).

AdventureWorks demo:

USE AdventureWorks2017;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
ALTER DATABASE CURRENT SET PARAMETERIZATION SIMPLE;
GO
-- None (0)
SELECT COUNT_BIG(*)
FROM Production.Product AS P
WHERE P.Color = N'Red';
GO
-- User (1)
EXECUTE sys.sp_executesql
    @stmt = N'
        SELECT COUNT_BIG(*)
        FROM Production.Product AS P
        WHERE P.Color = @Color;',
    @params = N'@Color nvarchar(15)',
    @Color = N'Red';
GO
-- Simple (2)
SELECT A.AddressID
FROM Person.[Address] AS A
WHERE A.AddressLine1 = N'1 Smiling Tree Court';
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED;
GO
-- Forced (3)
SELECT COUNT_BIG(*)
FROM Production.Product AS P
WHERE P.Color = N'Red';


Index for date column for range query in Oracle?

Ranges - especially date ranges - can be hard to tune.  There is a word of difference between this ...

where xdate between date '2013-07-01' and date '2013-07-31'

... and this ...

where xdate between date '2003-07-01' and date '2013-07-31'

You would want radically different access paths for those two queries.  An index will help with the first one, but will probably be disastrous for the second (depending on the volumes of data involved).

The other snag with indexing dates concerns the time element.  If xdate contains the time element then potentially you have 86400 different values for each calendar day.  In most cases when we're doing a date range search we want all hits for the day regardless of time.

We can't give an absolute recommendation, because you haven't provided enough details but my general advice would be:

Build a function based index, removing the time element:  create index tbl_xdate_idx on tbl(trunc(xdate)).
Investigate whether index compression will give you a space saving.  Compressed indexes usually perform better too. But it's probably only worthwhile if the dates don't include times, another benefit of the FBI approach.  Oh, and you need Enterprise Edition.
Remember that searches which span long ranges may need to be handled differently.  How you do that depends on the peculiarities of your application.


Track row origin in MySQL multi source replication

Are the rows going into the same table(s)?  If so, your suggestion of an extra column from the Master is probably the only way.

Or are the rows going into different databases (or tables)?  If so, then nothing is needed -- the different dbs/tables provide the clue.

Another approach may be "replicate rewrite" -- this turns same table into different table.  If you then need to "combine" the data (on the unified Slave), use UNION ALL and/or a VIEW.  See here.


How can I help SQL Server recognize my indexed view column is NOT NULL-able?

user_id, currency_id, and transaction_amount are all defined as NOT
  NULL columns in dbo.transactions

It looks to me that SQL Server has a blanket assumption that an aggregate can produce a null even if the field(s) it operates on are not null. This is obviously true in certain cases:

create table foo(bar integer not null);
select sum(bar) from foo
-- returns 1 row with `null` field

And is also true in the generalized versions of group by like cube

This simpler test case illustrates the point that any aggregate is interpreted as being nullable:

CREATE VIEW dbo.balances
with schemabinding
AS
SELECT
      user_id
    , sum(1)   AS balance_amount
FROM dbo.transactions
GROUP BY
      user_id
;
GO

IMO this is a limitation (albeit a minor one) of SQL Server - some other RDBMSs allow the creation of certain constraints on views that are not enforced and exist only to give clues to the optimizer, though I think 'uniqueness' is more likely to help in generating a good query plan than 'nullability'

If the nullability of the column is important, perhaps for use with an ORM, consider wrapping the indexed view in another view that simply guarantees the non-nullability using ISNULL:

CREATE VIEW dbo.balancesORM
WITH SCHEMABINDING
AS
SELECT
    B.[user_id],
    B.currency_id,
    balance_amount = ISNULL(B.balance_amount, 0),
    transaction_count = ISNULL(B.transaction_count, 0)
FROM dbo.balances AS B;


Oracle: are objects created by sys in a schema the same as objects created by that user?

Apart form all the DOs and DONTs about using SYS in this case is  that "if you connect as sysdba, your schema name will appear to be SYS".

Phil's answer demonstrates it. A nice quote by Thomas Kyte on a similar question -

  In general, unless the documentation tells you, you will NEVER LOG IN
  as sys or system, they are our internal data dictionary accounts and
  not for your use. You will be best served by forgetting they exist.

  sysdba and sysoper are ROLES - they are not users, not schemas. The
  SYSDBA role is like "root" on unix or "Administrator" on Windows. It
  sees all, can do all. Internally, if you connect as sysdba, your
  schema name will appear to be SYS.

  In real life, you hardly EVER need sysdba - typically only during an
  upgrade or patch.

We keep saying the following so many times -

  Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
  SYS/SYSDBA is special
  SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
  SYS/SYSDBA does not act like any other user
  When you use SYS/SYSDBA Oracle deactivates some code path and activates others
  Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

  NEVER EVER use SYS/SYSDBA for anything that can be done by another
  user.  Use SYS/SYSDBA ONLY for something that can't be done by someone
  else.

You might also like to read this.


Community Wiki answer generated from comments on the question by James Wahlin and Markus W Mahlberg

James: The arbiter is a voting member in an election but doesn't appoint members as primary or control the process. I would suggest reading: Replica Set Elections in the MongoDB manual.

When there is an election, all client connections are dropped by MongoDB. At that point in time the client will connect to one of its seeds and perform an isMaster call. This call reports back to the client which member is primary and which members are secondaries. In that way the client will seamlessly write to the correct member, based on read preference.

Markus: It will route requests from the application to the secondary. The drivers are replica set aware. Since all connections are either actively dropped by the old primary or stale, the driver will try to find the current primary in the list of replica set members it knows. You can verify this behaviour by setting up a replica set locally, running at 3 different ports on your local machine and kill one of them at random. After the short while an election needs to be finished, your client application writes to the newly elected primary.


This is part of the table definition, you'll need to do something similar to this:

ALTER TABLE Table1 MODIFY COLUMN Column1 INT AFTER Column2;

This will move Column1 after Column2.

Alter Table Syntax


Which data type should I use to store a 11 digit number

I would suggest storing the number as a BIGINT, and simply displaying the number the way you want it.

DECLARE @Num BIGINT;

SET @Num = 2421402015;

SELECT CONVERT(VARCHAR(11), REPLICATE('0', 11 - LEN(@Num)))
    + CONVERT(VARCHAR(11), @Num);

As pointed out by Aaron Bertrand in his comment, it is better performance-wise to not calculate the length of the required string of zeros, and simply truncate the string:

SELECT RIGHT(REPLICATE('0', 11) + CONVERT(VARCHAR(11), @Num), 11);

For your sample number, the concatenation would result in '000000000002421402015', which would then be right-truncated into 11 digits resulting in:

02421402015

In fact, as srutzky pointed out, this can be further optimized to:

SELECT RIGHT('00000000000' + CONVERT(VARCHAR(11), @Num), 11);

If users will pass in values with leading zeros that need to get matched in a WHERE clause,  I would convert the search term to an integer first, then use that in the predicate, so an index can be used.

I've written a blog post1 that details how you can store the number as an integer, while simultaneously providing an elegant method for the number to look like it's being stored in the database with leading zeros.  Check http://www.sqlserver.science/basics/how-do-i-store-a-number-with-leading-zeros/ for the details!  There are several other methods of formatting the leading zeros column, however the performance of each method varies quite drastically; the RIGHT('00000000000' + CONVERT(VARCHAR(11), @Num), 11) method looks like it's the fastest by far.

1 - My blog post was featured in the SQL PASS Community Connector News
2 - Follow up post on performance of FORMATMESSAGE() function in SQL Server 2012+


InnoDB - Use combined index with primary key on GROUP BY

Because you only select columns from table_1 and because table_2 is only joined (on the right side) of the LEFT JOIN and none of its columns is used elsewhere (SELECT, WHERE, GROUP BY or ORDER BY clauses), you can completely remove the LEFT JOIN table_2 t2 on t1.id = t2.refid part.

This might have given less rows in the result but because you group by the Primary key of table_1 after the join, there is no such case.

SELECT t1.a
FROM table_1 t1
WHERE t1.b = 99
GROUP BY t1.id
ORDER BY t1.c ;

Now, because you group by the Primary key of table_1, which is redundant when you have only one table, you can also remove that part: GROUP BY t1.id

Finally, the query is equivalent to:

SELECT t1.a
FROM table_1 t1
WHERE t1.b = 99
ORDER BY t1.c ;

which should use the index on (b, c), which yes, includes the primary key. You may consider it to be (b, c, id) if you want.

But whether that index will be used is not 100% sure. Depending on the selectivity of column b (what percent of the whole table has b=99?) this index may or may not be used. If there a lot of rows with b=99 (a large percentage), the optimizer may choose to scan the whole table instead and do a filesort, than use the index, select those (99,c) combinations that exist and then hit the table to find the a values.

If you have an index on (b, c, a) or on (b, c, id, a), the query will be able to find all the info that is needed in this index and in the correct order, so it will use it.


Restoring postgres backup with WAL-E fills up disk

It was operator error. My process to run daily base backups was not being run, so I had weeks of WAL segments, and that's what was causing it to fill up the disk and take forever. Once I got them all imported, I ran a base backup and then did the import again, and it was done in less than a minute. Do frequent base backups!


creating and populating table using pl/sql

You can't. You have to use execute immediate. Check here

DECLARE
    v_customer_ID  VARCHAR(10) not null := 3025;
    v_customer_Name VARCHAR(15) :='Michel Jackson' ;
    v_room_code  VARCHAR(5) := 6536;
BEGIN

  execute immediate 'create table customer....';

END;
/


Select which has max date or latest date

Your current query is not giving the desired result because you are using a GROUP BY clause on the PERSON_ID column which has a unique value for both entries.  As a result you will return both rows.

There are a few ways that you can solve this.  You can use a subquery to apply the aggregate function to return the max(LAST_UPDATE_DATE_TIME) for each SCHOOL_CODE:

select s1.LAST_UPDATE_DATE_TIME,
  s1.SCHOOL_CODE,
  s1.PERSON_ID
from SCHOOL_STAFF s1
inner join
(
  select max(LAST_UPDATE_DATE_TIME) LAST_UPDATE_DATE_TIME,
    SCHOOL_CODE
  from SCHOOL_STAFF
  group by SCHOOL_CODE
) s2
  on s1.SCHOOL_CODE = s2.SCHOOL_CODE
  and s1.LAST_UPDATE_DATE_TIME = s2.LAST_UPDATE_DATE_TIME;

See SQL Fiddle with Demo

Or you can use use a windowing function to return the rows of data for each school with the most recent LAST_UPDATE_DATE_TIME:

select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME
from
(
  select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME,
    row_number() over(partition by SCHOOL_CODE
                        order by LAST_UPDATE_DATE_TIME desc) seq
  from SCHOOL_STAFF
  where STAFF_TYPE_NAME='Principal'
) d
where seq = 1;

See SQL Fiddle with Demo

This query implements row_number() which assigns a unique number to each row in the partition of SCHOOL_CODE and placed in a descending order based on the LAST_UPDATE_DATE_TIME.

As a side note, the JOIN with aggregate function is not exactly the same as the row_number() version.  If you have two rows with the same event time the JOIN will return both rows, while the row_number() will only return one.  If you want to return both with a windowing function, then consider using the rank() windowing function instead as it will return ties:

select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME
from
(
  select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME,
    rank() over(partition by SCHOOL_CODE
                        order by LAST_UPDATE_DATE_TIME desc) seq
  from SCHOOL_STAFF
  where STAFF_TYPE_NAME='Principal'
) d
where seq = 1;

See Demo


Is there anything wrong with creating a very large log file?

There is nothing wrong with setting the initial log size to 70GB, if you need that large of a transaction log.  There is a problem, however, with it set to grow in 10MB increments.  Such a small increment of growth can result in numerous VLFs (Virtual Log Files) which can result in long recovery of the database during startup and lengthen restore times.

You are correct that the log file consumes the disk space regardless of how much data is actually in the log.  Backing up the transaction log does not decrease the size of the file.  Either the file was initially created that large, or it grew to that size over time, or because of a large insert operation or something.  If it grew to that size over time, you should run DBCC LOGINFO and see how many VLFs you have.  If you have thousands, then you will want to fix that by following the steps lined out here: http://support.microsoft.com/kb/2455009


How to change VARCHAR length in PRIMARY KEY in SQL Server 2014

As you are increasing the length you can just do an alter column, for example:

CREATE TABLE dbo.T1
    (
      C1 VARCHAR(20) NOT NULL
                     CONSTRAINT PK_T1 PRIMARY KEY ,
      C2 INT
    );
GO

INSERT  INTO dbo.T1
VALUES  ( 'test', 1 ),
        ( 'test2', 2 );
GO

ALTER TABLE dbo.T1 ALTER COLUMN C1 VARCHAR(25) NOT NULL;

Further reading on the ALTER TABLE syntax at https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql


MySQL 5.5 accepts password but then closes connection

A) Try with

mysql -h[your IP] -u[username] -P[]port -p
mysql -h127.0.0.1 -u[username] -p
mysql -hlocalhost -u[username] -p

Which one works and which does not?

B) Set Log_warnings=2 in my.ini and restart MySQL server and see error log entry when connection gets closed abruptly.


Dynamic Partition on tabular model 1200 using visual basic 2015 in visual studio

I would study this white paper and the AsPartitionProcessing sample for ideas. Ignore that it targets Azure Analysis Services as it should work against your SSAS instance if you use that servername instead of asazure:// as the servername. It uses the Tabular Object Model (TOM).


How to allow a user to create databases only with a certain prefix?

PostgreSQL does not allow you to do this inside PostgreSQL.  You cannot create a database inside a transaction so procedural language functions cannot create databases or drop databases.  I hope some day this is corrected, but it is the one piece of DDL that does not work inside a transaction.

What this means is you have to do it from outside the database with a separate application that runs whatever checks you want to run.  Some key things to keep in mind are:

You probably want to have a dedicated user (role with login, noinherit) for the database creation process.
You do not want to give the permission to create dbs to the general users.
The script should create the database and then ALTER DATABASE ... OWNER TO ... in order to ensure that the user has full permissions to it.


Postgres DB is extremely fast with a query with a certain parameter, but extremely slow for another

The fast query retrieves and sorts 26229 rows - for that small number of rows, the sorting can be done in memory, so obviously this is going to be quick. First because retrieving the data only takes ~500ms and then the sorting is done in 50ms.

The slow query retrieves 560135 rows (20 times as many as the first query) but the time it wook - 149939ms - seems quite slow. Maybe your table (or index) is bloated - the number of blocks needed to read that number of rows is way too high I think.

You can run vacuum full analyze public.stats; and see if performance gets better after that.

Or maybe you simply have a very slow harddisk.

The sorting was done on disk, but that only added another 3 seconds to the total runtime.

The question why the same query is sometimes fast and sometimes quick (especially when run the second time) is more often that not answered with: caching effects. When you re-run the second query you will probably see a lot of the "shared read=318147" information turn to "shared hit=..." which means those blocks were already in the cache.


How to import excel to MS Access table design?

If you want to create a table using DDL (data definition language) you can use a create table statement

You don't need the start or end position. But you do need a data type and field length.

With a bit of clever work in excel, you can probably generate a create statement.

Unless you are repeating this for lots of tables it might be easier just to create the table directly in Access.

Another option is to mock up a sample table in excel with your field names and a sample row of data and import it into access. That way access will select suitable data types.


"Measure Type Dimensions" in "Accumulating Snapshot" fact table

I believe the guidance is referring to a wide fact table where the majority of measure values are null:

CREATE TABLE dbo.SparseFact
(
    Dim1Key     INT NOT NULL
    , Dim2Key   INT NOT NULL
    , Dim3Key   INT NOT NULL
    , Dim4Key   INT NOT NULL
    , Dim5Key   INT NOT NULL
    , Value1    INT NULL
    , Value2    INT NULL
    , Value3    INT NULL
    , Value4    INT NULL
    , Value5    INT NULL
    , Value6    INT NULL
    , Value7    INT NULL
    , Value8    INT NULL
    ..
    , Value101  INT NULL
    , Value102  INT NULL
    , Value103  INT NULL
);

The suggestion is that some people will see all the nulls and decide to do this instead:

CREATE TABLE dbo.DontDoThisFact
(
    Dim1Key             INT NOT NULL
    , Dim2Key           INT NOT NULL
    , Dim3Key           INT NOT NULL
    , Dim4Key           INT NOT NULL
    , Dim5Key           INT NOT NULL
    , MeasureTypeKey    INT NOT NULL
    , Value             INT NOT NULL
);

Not good.

In your scenario I think I'd be looking at something like this, which is very different to the scenario described in the articles you referenced.

CREATE TABLE dbo.InventoryFact
(
    ContainerKey        INT NOT NULL
    , TransportTypeKey  TINYINT NOT NULL
    , EntryDateTime     DATETIME NULL
    , ExitDateTime      DATETIME NULL
);

CREATE TABLE dbo.TransportType
(
    TransportTypeKey    TINYINT IDENTITY(1,1) NOT NULL
    , EntryTransport    CHAR(10) NOT NULL
    , ExitTransport     CHAR(10) NOT NULL
);

INSERT
    dbo.TransportType
SELECT
    EntryTransport
    , ExitTransport
FROM
    (
    SELECT EntryTransport = 'Train'
    UNION
    SELECT EntryTransport = 'Truck'
    UNION
    SELECT EntryTransport = 'Vessel'
    UNION
    SELECT EntryTransport = 'N/A'
    UNION
    SELECT EntryTransport = 'Unknown'
    ) en
CROSS JOIN
    (
    SELECT ExitTransport = 'Train'
    UNION
    SELECT ExitTransport = 'Truck'
    UNION
    SELECT ExitTransport = 'Vessel'
    UNION
    SELECT ExitTransport = 'N/A'
    UNION
    SELECT ExitTransport = 'Unknown'
    ) ex;

To the additional questions...

I would add ExpectedEntryDate, ExpectedExitDate to the Container/InventoryFact. Less certain, without visibility of all the data elements, I would probably put EntryVoyageId and ExitVoyageId in a separate junk dimension together as one row along with any other degenerate data items (identifiers for the truck, train etc).

I would add 3 new dimensions for VesselVoyage, TruckVoyage and TrainVoyage and 6 Voyage keys (inbound/outbound) to this one fact (it's 6 new keys, not 6 additional rows). You then have the option of placing Dock and Tollbooth in the appropriate Voyage dimension. If you keep the generic data in these dimensions (VesselFlag, TruckCapacity) and the specific in a junk dimension (VesselName, VesselMMSI) they won't explode in size.


SQL Server Management Studio Express 2012 "ONLY"

It's pretty likely you were using RC0, which expired a few days ago. I would imagine that if you start and stop the 2012 engine service in the services control panel, then it will refuse to start again.

When you have reinstalled Express it will be fine.


Why does requesting the "estimated execution plan" for sp_delete_job throw an error?

Because Microsoft hasn't learned to create unique object names. If you look at the definition for sp_delete_job, you will find this around line 115, a slightly different version of #temp_jobs_to_delete:

CREATE TABLE #temp_jobs_to_delete (job_id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED,
                                   job_is_cached INT NOT NULL)

Now, consider that SQL Server may take a slightly different path when generating estimated execution plans for a set of nested procedure calls than if it simply ran the code. It likely does some kind of object-level collection that recognizes the same #table is used and assumes that it must adhere to the same schema (even if logically the two #temp tables would never cross paths).

Now, it is possible that this code will actually break when you try to delete a job and the properties of that job lead to sp_delete_all_msx_jobs to be called, which I suspect in your case did not happen on actual execution (nor in mine). I did not have the energy to set up MSX to evaluate. But remember some of the other things that happen during parsing that couldn't possibly be a problem on actual execution:

IF (1 = 1)
  CREATE TABLE #x(i INT);
ELSE
  CREATE TABLE #x(d DATETIME);

Illogical parsing error:

  Msg 2714, Level 16, State 1
  There is already an object named '#x' in the database.


The documentation explains how to go about configuring listener tracing, but in summary:

Add:

TRACE_FILE_<LISTENERNAME>=listener.log
TRACE_DIRECTORY_<LISTERNAME>=/path_to_your_log_directory
TRACE_LEVEL_<LISTENERNAME>=user

You can up the TRACE_LEVEL to admin or support if you need more information, but be aware that they generate increasing amounts of output.

Your issue could be due to either a DB not being able to service a request due to the maximum number of processes being reached (check the alert log), a lack of resources on the box (are kernel parameters set correctly?) or an outright DB crash.


How to restore single table in Sql Server 2016

If you used the "Generate Scripts" option and included both the schema and the data, you'll have a script containing something like "CREATE TABLE xyz INSERT INTO xyz VALUES ..." with a series of data values to insert. Possibly with a "DROP TABLE" statement at the top, if you included that option when you scripted it.

In that case, execute the generated script (the .sql file) to re-create the table and insert the values to it. Hopefully if there were any indexes etc. you selected the option to include those too. If it's not as simple as dropping and re-creating that table (like if there are foreign key constraints, identity columns etc) you may need to 'restore' the scripted table to a copy of itself, like my_table_new (if the original is my_table) and then re-insert the row from the 'copy' to the original - using ALTER TABLE my_table SET identity_insert on if necessary.

In the future please consider having a proper backup and restore schedule in place, to take care of this kind of thing. Please tell us you aren't running this update in production...!


Why does this simple select by primary key show such intensive CPU usage?

A possible explanation:

The table is nearly as big as innodb_buffer_pool_size.
The Could did some sort of maintenance just before that spike, hence the buffer_pool (cache) was lost.
UUIDs are terribly inefficient because of how random they are.
Each SELECT (during the spike) found the desired row to not be cached, so it had to read from disk.  (Was there also a spike in I/O?)

If the table is growing is size, the problem will become noticeably worse when the table becomes bigger than the buffer_pool.


ORA-39014 import in Oracle 19c from DMP file of Oracle12c

It looks like your Oracle instance is failing you. But let's roll it up from the beginning.
IMPDP in Parallel
To execute the IMPDP command with the PARALLEL parameter you have to meet the following requirements:

This parameter is valid only in the Enterprise Edition (check) of Oracle Database 11g or later.

To import a table or table partition in parallel (using PQ slaves), you must have the DATAPUMP_IMP_FULL_DATABASE (check) role.

Transportable tablespace metadata cannot be imported in parallel.

Metadata cannot be imported in parallel when the NETWORK_LINK parameter is also used

The following ojbects cannot be imported in parallel:

TRIGGER
VIEW
OBJECT_GRANT
SEQUENCE
CONSTRAINT
REF_CONSTRAINT.

Reference: 3. Data Pump Import | PARALLEL (Oracle Help Center)
ORA-xxxxx Errors

Disclaimer
Due to copyright restrictions I am only able to provide you with some general links to the Oracle Doc IDs which may or may not provide you with a solution to your issue(s). Registration is required to view this information in full.

ORA-00800 Errors

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], for Oracle Linux (Doc ID 2718971.1) (Oracle on *nix systems)
ORA-00800: Soft External Error, Arguments: [Set Priority Failed], [VKTM] [Check traces and OS configuration] (Doc ID 2551359.1) (Oracle on IBM AIX systems)

ORA-00313 Errors

ORA-00313 at Startup After a New Redo Log Member is Added (Doc ID 1005110.6)

ORA-07445

ORA-7445 Troubleshooting Tool (Oracle Online Troubleshooting Tool)

This option requires you to upload an IPS package to Oracle in order for them to analyse the data. If you create an SR with Oracle and supply the error message ORA-7445 in the case, then you will be automatically redirected to this tool. There is however a downloadable version under that link.
If All Else Fails
There do seem to be issues when trying to import 12c objects into 19c so you might want to contact a certified Oracle consultant or if you have a Support Identifier with Oracle open up a new Service Request.


How can I check index fragmentation in the quickest way possible?

It looks like the other suggestions so far all lead back to sys.dm_db_index_physical_stats

This is where you will have to look to get the index stats, but you could look to use the LIMITED option and restrict it to specific indexes at a time.

With the size of your table, I would want to know how quickly fragmentation occurs to help me confirm that the indexing (particularly the clustered index) is appropriate.

How big is your maintenance window?  Is it all consumed by the scanning part?

One other aspect to investigate (you didn't mention which version you were on) is RESUMABLE ONLINE REBUILDS.  If you have the space then this might be an option to run as much as you can over multiple maintenance windows.


How to update scheduler job credentials in oracle enterprise manager?

In Oracle Enterprise Manager Cloud Control 12c you can edit the named credentials by navigating to Setup > Security > Named Credentials

In Oracle Enterprise Manager 11g Database Control you should look at Preferences > Preferred Credentials, here is the documentation.


How these conflicting scenarios will work in cassandra when one of the mode crashes?

In both scenarios, everything depends on the how long the node N1 was offline, and where the hints enabled or not...

In scenario 1, if downtime was less than 3 hours (default window to store hints), then the changes done when N1 was offline will be replayed, and value will be updated to scott_update2 because this value has greater timestamp. If downtime was larger than this window - then you need to run repair.

For scenario 2, there are 2 things - it could be eventually repaired when read repair happens, or you can explicitly run repair.

But talking about replication - write usually happens to all replicas at the same time, and consistency level specifies how many nodes need to answer to consider request as successful, so N2 could be updated as well in most cases.


Is it a good idea to create an index just for new data to increase index creation performance?

if you create a partial index then your queries will have also include the condition used to create the index. if they do not the index will be of no use.

when creating the index there will probably need to be a scan of the entire table your only win is a smaller index size.

What index will help queries most depends on what information you want from the table. are you sure that there are enough payments with the exact same creation that including userid as the second term in the index is justified? bear in mind that timestamp is 8 bytes while numeric(20) is 16 bytes, so an index on timestamp only will also be more compact.

An index on payment (userid, creation) will be more useful in the case where you want you find the most recent record, or the payment history, for any particular userid.


What's the term that describes filtering query that makes use of indexes

Sargable (or sometimes sargeable). It's not really a word, it's made up of Search ARGument, and when a WHERE clause is sargable, that mean's it's possible for it to use an index. It doesn't mean it will use the index, and it doesn't mean it will seek, either. A lot of factors go into the optimizer's choice, and the rules can clearly differ between different platforms, and even different versions and editions of the same platform.

References:

Wikipedia
https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable
A question on SARGability


Getting the part for string using sql server query

Assuming you want data between first instance of _ and second instance of _ OR length of string in-case second _ is not present.

Try this Query:

-- Create Table Variable
DECLARE @tblString TABLE(StringVal VARCHAR(512));
-- Insert Values in Table Variable
INSERT INTO @tblString(StringVal)
select '10470_AMK-4799__OFS_BROWSERTC' UNION ALL
select '6038_DMUSER_DM.READ.REPLACEMENT' UNION ALL
select '6043_DMUSER2__OFS_DM.OFS.SRC.VAL' UNION ALL
select '10194_AMK-TC037__OFS_BROWSERTC' UNION ALL
select '64948_AMK-TABLETUSER__OFS_TWS' UNION ALL
select '10010_AMK-0357';
-- Select and process for expected Result
SELECT SUBSTRING(
                  SUBSTRING(StringVal,CHARINDEX('_',StringVal,0)+1,LEN(StringVal))
                ,0
                ,CASE when CHARINDEX('_',SUBSTRING(StringVal,CHARINDEX('_',StringVal,0)+1,LEN(StringVal))) <> 0
                    then CHARINDEX('_',SUBSTRING(StringVal,CHARINDEX('_',StringVal,0)+1,LEN(StringVal)))
                    else LEN(StringVal) as Result
                end
                )
FROM @tblString

The results are as below:

Result
-------
AMK-4799
DMUSER
DMUSER2
AMK-TC037
AMK-TABLETUSER
AMK-0357

This will achieve current scope.


Postgres query slow, shows long running active queries, statement_timeout does not work

SET statement_timeout = 1;
SELECT * from "table_name";

  does not work. That is... the SELECT statement above takes 400
  milliseconds and doesn't terminate. Seems that I don't have this
  procedure right.

I don't know what else might be going on in your system, but this definitely does work for me as long as "table_name" is large enough to take more than 1 millisecond.  Are you issuing this from 'psql'?  Or if from something else, then what is that?  If you can't trust that your commands are actually getting sent to the database, or that any errors are reliably getting sent back, that is going to make it hard to accomplish anything else.


I don't see a problem either.

If your application logic allows, you might consider committing every N rows.

For improved performance, you might consider whether you can do blocked inserts of multiple rows at a time.

(I use DB2 for i. If you are on LUW or z/OS, your mileage may vary.)


Demographics Dimension Table - Finding the Key!

You mention that you already have a piece of sql that does what you want.

So i would suggest you modify that to become an update statement and implement it as a sql task. Another option is to save the update within a stored proc and call/execute it from ssis.

With a bit of thought you could probably write an update statement that only sets the values for new records or for records that have changed. Or perhaps do it as part of your ETL insert/merge.

However going back to my other comments: i don't think it makes sense to have a single key or dimension for such diverse attributes. I suggest you have a look at some sample schemas to see if it could be done differently.


Check for duplicate data from called functions

Right now your code is doing a cartesian join between derived tables o and p. If derived table o contains M rows and derived table p contains N rows then your result set will M x N rows with none of the column values being NULL unless your TVFs return NULL rows. You need to add a join clause between o and p to get the result set that you want.


What are safe characters to use in SQL Server Full Text Search?

It is difficult to answer the question specifically without a good description of what you are trying to achieve and why, but it appears that Full-Text Search may not be a good fit for you based on your need to search for characters that are either reserved for a special purpose within the full-text engine, treated as delimiters by word-breakers, or present as a stopword in the default system stoplist.

The Full-Text Search feature is primarily aimed at performing linguistic searches on words and phrases using the rules of a particular language like English, German, or French, for example. Searching for programming keywords like $partition in T-SQL will probably not work as you would hope because $partition is not a word or phrase in any of the available 50 (spoken) languages. If there were a T-SQL language option, things would be different.

It might be possible use a custom dictionary of 'new words' to meet your requirements, but this is not something I have directly experimented with myself. I think that's about as helpful an answer as I can offer without knowing more about your needs.


postgres dyanmic and unlimited where conditions

Have the function take a structure that defines the constraint to apply and parse that structure into a SQL where expression, then execute it.

The structure could be represented as JSON, XML, or any other type that can be handled by postgresql built-ins or your code.

For safety aghainst injection attacks be sure to not copy any raw data from the struct to the query, all values and identifiers should be quoted (quote_identifier(), quote_nullable(), etc...) and all operators should come from string literals in the function code.

the function itself would be structured like something like.

CREATE OR REPLACE FUNCTION filter( conditions text )
   RETURNS SETOF sometable
   LANGUAGE plpgsql
   AS $$
        DECLARE
          whereclause text := '';
        BEGIN
          whereclause = whereclause || conditions
          RETURN QUERY EXECUTE
             'SELECT * FROM sometable WHERE ' || whereclause;
        END;
      $$;

but probably with some more involved processing of conditions before the RETURN QUERY EXECUTE


The connection is no longer usable because the server response for a previously executed statement was incorrectly formatted

Based on the details you've provided, it's likely this error is being caused by a network issue between the AX server and the SQL Server.  If you have a network team, it might be good to bring them in to investigate if there are packets being lost between the two servers.
Another possibility is that there is an issue with the driver AX is using to connect to SQL Server (SQL Server Native Client 11.0).  Some possible options there are to

reinstall the driver,
update to the most recent supported version of the driver,
try a different driver, like MSOLEDBSQL (if that's configurable in AX, I don't know anything about that)


Using a NULL value to signify that the value does not mattter. Not that it isn't known

My approach for this is to think of what the fields are, which is storing information on computers. There is no case ever where having a fan would result in NULL, just a yes or no. In your case of using it as a major category for computers, I would avoid that, and instead use your queries and stored procedures to consider the filters. In the stored procedure you can have the WHERE clause look for the different combinations;
Only Computers with fans -- SELECT [recommendations] FROM table WHERE [has_fan]=1
Only computers without fans --SELECT [recommendations] FROM table WHERE [has_fan]=0
All computers\don't care -- don't use a WHERE clause;SELECT [recommendations] FROM table


computed columns, index, clustered index and covering index?

Computed Columns can be stored in the data page in one of two ways. Either by creating them as PERSISTED or by including them in the clustered index definition.

If they are included in the clustered index definition then even if the columns are not marked as PERSISTED then the values will still be stored in each row. These index key values will additionally be stored in the upper level pages.

If the computed column is imprecise (e.g. float) or not verifiable as deterministic (e.g. CLR functions) then it is a requirement for the column to be marked as PERSISTED in order to be made part of an index key.

So to give an example

CREATE TABLE T
(
A INT,
C1 AS REPLICATE(CHAR(A),100) PERSISTED,
C2 AS REPLICATE(CHAR(A),200),
C3 AS CAST(A AS FLOAT) PERSISTED,
C4 AS CAST(A + 1 AS FLOAT)
)

CREATE UNIQUE CLUSTERED INDEX IX ON T(C2,C3)

C1 will be stored in just the data page rows as it is marked as
PERSISTED but not indexed.
C2 will be stored in both the rows on the data page and the index higher levels as it is an index key column.
C3 will be stored as for C2. As it is imprecise it is a requirement to mark it as PERSISTED however.
C4 won't be stored anywhere as it is neither marked as PERSISTED nor indexed.

Similarly all computed columns referenced in non clustered index definitions as key columns need to be stored at all levels of the index as they are part of the index key. There is the same requirement regarding precise/deterministic results.

Fails

CREATE NONCLUSTERED INDEX IX2 ON T(A,C4)

With the error.

  Cannot create index or statistics 'IX2' on table 'T' because the
  computed column 'C4' is imprecise and not persisted. Consider removing
  column from index or statistics key or marking computed column
  persisted.

To include it as part of the non clustered index key it must also be stored in the clustered index data pages. However

Succeeds.

CREATE NONCLUSTERED INDEX IX2 ON T(A) INCLUDE (C4)

Computed columns that are only INCLUDEd columns are persisted to the NCI leaf page and do not have the requirement that they also be persisted in the data page.


PostgreSQL for windows create tablespace on network location (nfs shared) failed because directory not exist

It seems that on Windows, PostgreSQL can only use local storage as its data location, right?

On any operating system, PostgreSQL should only use "local" file systems.
On Linux, those file systems can be mounted from other machines in a way that PostgreSQL is not aware of so things "just work".
On Windows, a network drive is another thing entirely.
Under which account are you running PostgreSQL?  (Probably as a Windows Service under "Local System").
Does that account have the ability to "see" anything not on the local machine ("No").
Even if you were to change this, putting your data "further" from the process that needs to manipulate it is just asking for [major] Trouble.


What makes SQL Server prefer page locking over row locking?

There are many reasons why page locks might be used over row locks, but the most basic answer is memory management. 400-500 row locks might be far less attractive than locking a smaller number of pages to accomplish the same task.

It's also possible that the way the query is written, or the way the index is designed is responsible for the type of locks taken.

See my posts here for greater detail:

Bad Estimates And Locking
Index Key Column Order And Locking


Foreign key are constraints for data consistency, and they should be added always as a security measure. In order for the foreign keys to be fast, MySQL also creates its corresponding indexes unless the columns are already indexed. That is independently of the performance question: create the foreign keys to avoid storing incorrect data.

In most cases, you may want to index the pivot table because the most typical queries that you will be doing are along these lines:

SELECT *
FROM Reservation RV
JOIN pivot p ON p.reservation_id = RV.id
JOIN Resource RS ON p.resource_id = RS.id
WHERE filter(column_on_Reservation_or_Resource)

In that case, you will traverse the pivot table in Reservation->Resource direction or viceversa. For that, the best index would be pivot(reservation_id, resource_id) (and/or viceversa if you are using it in the other direction), as that way you can get advantage of the covering index optimization. One of the directions can be the primary key, if the pairs happen to be unique (relationships cannot be multiple among the same pair of records from reservation and resource).


SQL Server add a linked server to postgres database

PostgreSQL:

You  must provide a database name in the Data Source DSN.
Run Management Studio as Administrator
You must omit the DBName from the query:

SELECT * FROM OPENQUERY([LinkedServer], 'select * from schema."tablename"')


Always-On secondary Replica's Read Committed Snapshot Isolation

Yes, when failover occurs the secondary changes back to read committed if that's how the DB is configured on the primary.
The developers are the only ones that can tell you if your application supports RCSI.  It probably does, but if they designed it such that it depends on blocking to ensure things are done in the correct order, then it doesn't.  Many third-party applications do not document whether it is supported when it really is, so step one is to contact the vendor support and ask and go from there.


Script to see running jobs in SQL Server with Job Start Time

I posted a query a while back for getting a list of currently running jobs here.

SELECT
    ja.job_id,
    j.name AS job_name,
    ja.start_execution_date,
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
    ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
    ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;

This has job name, job id, start time and what step it's running on.  If you follow the link above you can get some more detail on how I came up with it and what else is available.


How to remove orphaned Log Reader Agents from Replication Monitor?

Its never recommended to delete rows from system defined table. Please follow below steps to remove any orphan logreader agent.

Create a  dummy transactional replication ‘Dummy1’.
Add database(select publisher_db from [distribution].[dbo].[MSlogreader_agents]) as publisher and another database(it would be good if you remember the old subscriber) as subscriber.
Add any dummy article to it.
Drop the subscriber of ‘Dummy1’.

This will renmove the orphan log reader agent of transactional replication. Similarly you can work for snapshot agent or merge agent.


Normalization is based on functional dependencies. Functional dependencies have to do with semantics; they have to do with what the data means. When you simplify a real-world problem to the level of "parent_id, child_id, date", and you don't include any sample data, you really limit how much help a conscientious database designer can give you.

The fact that you have a key {child_id, parent_id, date} in one table, and that you have (it seems) a unique pair {child_id, parent_id} in the child table doesn't necessarily mean that part of the combination is redundant. It might mean that in the table that has {child_id, parent_id, date} as the primary key, the pair of attributes {child_id, parent_id} ought to reference the child table in the first place.

If that's the case, you might use FOREIGN KEY (child_id, parent_id) REFERENCES child (child_id, parent_id). To do that, you need a UNIQUE constraint on the pair of columns (child_id, parent_id) in the table "child", which shouldn't be a problem if child_id is its primary key.

But there's no way to tell without knowing what the data means, and you're the only one in this thread who knows that. (But we'd be happy to let you explain it to us.)

As far as the original table is concerned, you seem to be saying that child_id -> parent_id. If that's the case, why is parent_id in the original table in the first place? Why isn't the key just (child_id, date), with a foreign key reference to the "child" table? It looks to me like the kind of redundancy you're talking about might be resolved by dropping the column "parent_id".

SQL DDL and sample data in the form of INSERT statements helps us help you. DDL and INSERT statements are more precise than descriptions.


How do you show SQL executing on an Oracle database?

Most of the info is available in v$session ... and the SQL Text can be grabbed from v$sql or v$sqltext_with_newlines...

Here is a query that I often use that formats In-flight SQL, longest running at top.

-- In Flight SQL
SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,
       MACHINE,
       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT
  FROM V$SESSION SES,
       V$SQLtext_with_newlines SQL
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
   and Ses.AUDSID <> userenv('SESSIONID')
 order by runt desc, 1,sql.piece;


How to optimize constant querying for a sum of many rows?

The concept you want is called a "materialized view". Normally, a view is fundamentally just a query prepared in advance, that you can reference like a table for convenience. A materialized view is at least partially stored in the database.

In SQL Server, this is done by creating an indexed view. This acts as an index tied to the table(s) in the view, and is updated when the underlying rows are updated, just like any other index. Note that the SQL Server Developer's Edition is free, and has very few limits on it.

Near as I can tell, neither MYSQL nor PostGreSQL provide this feature in this form.

With such a materialized view, you still have a potential for a performance issue. Since the index is updated every time the underlying rows are updated, if you're trying to tally up votes in real time, you may find that the view is constantly being modified. Between frequent SELECTs against the view, and constant INSERTs and/or UPDATES on the underlying tables, you may face blocking issues, a major problem if it prevents you from showing results, and an even worse problem if it prevents votes from being entered in the first place!

You may want to think about another option. Consider if you need a real-time tally, or if one that's been updated recently (say, within the last 5 minutes) would be sufficient. From what I've seen in election results in the US, that's the path they seem to take.


replace values of table from values of another table where both tables don't have same no of rows

The error you're receiving is because you haven't included the table in your FROM clause in your update statement.

UPDATE T1
SET T1.value = ISNULL(T2.value,T1.value)
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.id = T2.id

The above code resolves the problem - you could also remove the ISNULL and change the join to an INNER join as below:

UPDATE T1
SET T1.value = T2.value
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.id = T2.id

dbfiddle here

For further details see the docs on update


SQL Server log file used pecentage does not drop after multiple backups

In addition to the additional space marked "in-use" by SQLPERF you might have space in your transaction log that is not being freed for other reasons.  You should probably take a look at the log_reuse_wait_desc column of sys.databases. If it says NOTHING or LOG_BACKUP then what you are seeing is probably the space taken up by the current VLF.  If however if you are seeing other values, such as DATABASE_MIRRORING or REPLICATION then a portion of your log is waiting to be replicated to another machine.  VLFs are not marked "free" unless they are completely ready to be re-used.  This can mean an open transaction or waiting on a log backup (if in bulk-logged or full recovery model), but it can also be other things too.


Use separete tables. If one user can make comments on many articles you will have huge repeat of same information.


For Unique Impression IP as there is no need for a autoincrement primary key, use VARBINARY(16) for IP address, and as the primary key. Use INET6_ATON() when inserting it to keep it in a compact form.

Use:

INSERT IGNORE INTO `Unique Impression IP` VALUES (INET6_ATON($var))

And look at rows affected to see if that exists or not for your UPDATE Banner statement branch. As it's a unique key (primary keys are unique) it will only be inserted once. This removes a race condition in your current code.


Configuring Oracle ASM Filter Driver During Installation

  This procedure must be performed after the Oracle Grid Infrastructure
  installation zip files have been unzipped in the Oracle Grid
  Infrastructure home, but before you enable the Configure ASM Filter
  Driver check box during the installation process when the setup script
  is run.

su root
# set ORACLE_HOME=/u01/app/oracle/12.2.0/grid
# set ORACLE_BASE=/tmp

# asmcmd afd_label DATA1 /dev/disk1a --init

# asmcmd afd_lslbl /dev/disk1a

  Run the setup installation script (gridSetup.sh) to install Oracle
  Grid Infrastructure and enable Oracle ASM Filter Driver configuration.

  On the Create ASM Disk Group wizard page during the installation
  process, check the Configure ASM Filter Driver box to enable
  configuration of Oracle ASM Filter Driver and then add your
  provisioned disks to the initial disk group (typically named DATA).

I used the above before an it worked.

Example: https://balazspapp.wordpress.com/2017/03/20/installing-oracle-12c-release-2-12-2-rac-on-virtualbox-silent-installation-part-3/

Based on the documentation, afd_configure and afd_state are used when you migrate an existing installation to AFD.


What extra work is required to restore a SQL Server instance and its databases included in a Windows Server snapshot backup?

A backup of the entire server will be inclusive of the SQL Server instance and the database files as a snapshot in time at that moment. This is assuming that the database files live on the server itself that you're taking a backup of, as it is possible to setup the database's such that their files live on remote shares.
You can verify the location of your databases' files in SQL Server Management Studio, by right clicking on a specific database, clicking Properties, and then navigating to the Files page, then looking at the Path and File Name properties.
For example:

In the above, my Test database's files are stored in the default SQL instance folder. Please ensure the drives that your database files live on are part of the server / snapshot being taken of it, as even mapped drives can have drive letters but point to remote shares.
There shouldn't be any additional work on the destination server, but you may want to make sure the appropriate SQL services (in the Windows Services) are started and running just the same as on the source server.
For example:

The above is my test instance which probably has a lot more components of SQL Server installed than yours will, so don't take this as a definitive list, rather it's an example. Compare against your source server to the destination server.
The only other thing I can think of is to ensure all the same Windows permissions are setup to the correct accounts on your destination server as its respective source server. For example, if you're using the SQL Agent, or a feature like Replication, those might leverage Windows accounts that currently have special permissions setup on your source server which need to be replicated on your destination server. (This is a little outside the scope of DBA.StackExchange though, and might be more of a ServerFault question, if you need help with that.)


Installing extension as non superuser on Amazon AWS RDS

Amazon RDS has a custom list of allowed extensions. You can see it with:
SHOW rds.extensions;

See online documentation.
And you should do it as role rds_superuser or as a member of that role (actual superuser is not available). Their website informs:

The rds_superuser role can do the following:

Add extensions that are available for use with Amazon RDS. [...]


Securefile LOBs are free to use in any edition.

Only the extra features of Securefile LOBs require buying the Advanced Compression or Advanced Security option, and these options are available only in Enterprise Edition.

These features are:

Advanced Compression: Securefile compression and deduplication.

Advanced Security: Securefile encryption.

Licensing Information


Keep Mongo DB set up up and running with two data center always

This is a configuration will work with using Virtual Host Names for config and arbiters. When you bring up the hidden ones you only need to change DNS entries to point to DC2 servers.


How to immediately terminate a SPID with a very long rollback time estimated?

Let that thing rollback. There's nothing you can do to stop it or speed it up. If you shut down the server or "trip" on the power cord, the rollback will just start back up again when you turn the server back on.

In any case, if you can restore as a different name, you don't need to rename the data file. First, see what files you have:

RESTORE FILELISTONLY FROM DISK = 'C:\path\file.bak';

It will indicate two or more files. You need to map those into the below. To find out what the current data path is, in case the backup is from a different instance, you can grab it from:

EXEC master.sys.sp_helpfile;

Then the restore command is like this:

RESTORE DATABASE [new_name_here]
  FROM DISK = 'C:\path\file.bak'
  WITH MOVE Logical_Data_File_Name TO 'C:\some other path\some other name.mdf',
       MOVE Logical_Log__File_Name TO 'C:\some other path\some other name.ldf';


As you  mentioned you are tying to run mysql_upgrade and it's looking for 'mysql.exe' and  'mysqlcheck.exe'.

Try this To make it easier to invoke MySQL programs, you can add the path name of the MySQL bin directory to your Windows system PATH environment variable:

Here is the link to set Mysql environment variable in Windows system path.

Hope this will help you.


Is it possible to redirect queries to another server?

First Things that come to my mind:

1 Linked Servers
You can use a linked Server and forward all requests to it, but then the calling Code or Programm must use prepared procedures or views that use the linked Server.

2 SQL alias, but I guess this is not what you are looking for.

But I wonder why would you do that? I can't See advantages.

Think about license Costs. They may double if you Do this kind of scenario in a production Environment.

Monitoring and tracing : administrative effort increases and again Costs.

And Security. It's Trick to configure linked Server connection to reuse the calling Logins to make sure you dont Open All Gates to anyone who can access the "Proxy".


What I would do is
SET STATISTICS IO ON

and then I would test the SELECT statement that produces the seek and the UPDATE statement.
Finally, I would compare the number of pages read by the SELECT with the number of pages written by the UPDATE.
But in general this ratio between seek and updates seems to suggest that your nonclustered index is not valuable. But it really depends on your query needs.


Insert into multiple tables with one query

I think you can easily pile the CTEs on top of each other, like so:

WITH ins AS (
  INSERT INTO core.adr
    (street, "number", postal_code, city)
  VALUES
    ('test 1', '25a', '00912', 'villageman')
  RETURNING id),
ins2 AS (
  INSERT INTO someothertable
    (id, something)
  SELECT id, 'something' FROM ins
  RETURNING id -- this is necessary for CTE, but not used
)
INSERT INTO core.adr_information
  (idref, info)
SELECT id, 'test data'
FROM ins;


Ensure percentages are between 0 and 1, inclusive (using a single function)

Ok, this can't be a serious question, reminds me of https://codegolf.stackexchange.com/

I can do better, no function at all.

select
  percent_decimal,
  case
    when percent_decimal <= 0 then 0
    when percent_decimal >= 1 then 1
    else percent_decimal
  end as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1

But we need a function. Finally Oracle 12.2 allows long (128 bytes) identifiers, so we can give more meaningful names:

with function
  "Convert -0.01 to 0, Leave .1 as is, Leave 1 as is, Convert 1.1 to 1" (p number)
return number
as
begin
  if (p = -0.01) then return 0; end if;
  if (p = 1.1) then return 1; end if;
  return p;
end;
select
  percent_decimal,
  "Convert -0.01 to 0, Leave .1 as is, Leave 1 as is, Convert 1.1 to 1"(percent_decimal)
    as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1

I also happen to have a super secret function that coincidentally does exactly what you need:

create or replace function "." wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
97 aa
30RI5qP/Li6VhppxgO2AXQw62mYwg1xfrZ6pfI6iO8cYr/L9s3tguDonZ52omRm1G6sfUCen
xkcEU/4jCxrp7Ii4VNrCrVY6cDr0UNz1Id5vZ5p7e0S0F+AEd55sz9JyGokPaBbDJHFVdW+A
IAHg6Xpxmglu3BtbDtBmCA==

/

select
  percent_decimal,
  "."(percent_decimal) as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1

Must use a built-in function? No problem.

select
  percent_decimal,
  round(percent_decimal/1.05, 1) as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1


When to ANALYZE/VACUUM/REINDEX when adding primary keys and indexes

To speed up index creation, increase maintenance_work_mem.

Additionally you can raise max_wal_size to reduce the number of checkpoints.
If you don't need crash recovery at all (i.e., you are happy to recreate the database in case of a crash), you can use unlogged tables which would boost performance.


From the manual here, you can simply use the DATE() function.

 DATE(expr)

Extracts the date part of the date or datetime expression expr.

mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'

It is designed for precisely your use case. Although the quote is from the 5.7 manual, it works right back to at least 5.0. You don't have to worry about corrupting your data.

If you are using 5.7, you can use a generated column:

  The syntax is:

  TYPE   [ GENERATED ALWAYS ]   AS   (  )   [
  VIRTUAL|STORED ] [ UNIQUE [KEY] ]   [ [PRIMARY] KEY ]   [ NOT NULL ]
  [ COMMENT  ]

MyDate DATE AS (DATE(My_Date_Time_Field))  -- or similar, not tested

This avoids throwing away data which might be useful later and nicely tidies your SQL syntax.

However, if you're using an earlier version of MySQL, you can always generate this DATE() part on the fly, which given the relatively simple syntax, shouldn't be too problematic.


Excluding the standard MySQL databases from replication

There's no need to avoid replicating performance_schema or information_schema -- they are not real databases with real tables, they are generated by server internals to interact via SQL, but you'll notice from their table definitions that they aren't normal.  They can't be altered, modified, dropped, created, updated, inserted into, or deleted from... and they don't cause replication events. The performance_schema does not have to be configured the same on master and slave.

The mysql database, you need to allow to replicate, because the users on the master need to be valid users on the slave -- otherwise, you will encounter potential problems with definitions and execution of views, triggers, stored procedures, stored functions, and events, all of which have "definer" attributes that need the defining users to exist on the slave in many cases.

However, you are free to create additional users on the slave as long as you don't, later, generate events on the master that would conflict with those users.

A slave needs to be run in global read_only mode, which prevents users with write permissions granted to them from actually modifying data on the slave, unless they have the SUPER privilege, which bypasses this restriction (which is also why I, as a DBA, have two accounts... I connect to slaves with my account that lacks SUPER unless I have a specific reason to do otherwise).

There is no "tuning" that I can think of that's stored in the system tables in the mysql schema.

All of these things in mind, I'd suggest that the best/easiest/safest approach is to let replication do it's thing and cross any problematic bridges when you come to them... which is typically far less likely when you replicate everything than if you try to be selective.


SSRS Query Timeout settings: rsreportserver.config verses RDL Query.Timeout

The answer is in your first link:

  DatabaseQueryTimeout  -  Specifies the number of seconds after which a connection to the report server database times out.

This setting only applies to the connections from the RS services to the RS databases, not for individual reports to their data sources. This makes sense given:

  This value is passed to the System.Data.SQLClient.SQLCommand.CommandTimeout property

What if your data source is not SQL, what if it is Oracle or MongoDB or some other data source? This setting wouldn't exist for that connection object.

The answer to your question is, with no setting defined your timeout would be 30s.


SQL Server 2000 - How to find unused indexes

The only thing I can recommend is to put your 2000 database on a server with SQL Server 2005 or greater (a development or test server) installed, find the unused indexes with the help of the new DMVs and once you have a list of changes apply them back to the production SQL Server 2000 instance.

Please be aware that SQL Server 2000 is no longer supported and you should move your production processes, if possible, to a later version of SQL Server.

An example query in SQL Server 2012 using the sys.dm_db_index_usage_stats DMV:

SELECT u.*
FROM [sys].[indexes] i
JOIN [sys].[objects] o
ON (i.OBJECT_ID = o.OBJECT_ID)
LEFT JOIN [sys].[dm_db_index_usage_stats] u
ON (i.OBJECT_ID = u.OBJECT_ID)
AND i.[index_id] = u.[index_id]
AND u.[database_id] = DB_ID() --returning the database ID of the current database
WHERE o.[type] <> 'S' --shouldn't be a system base table
AND i.[type_desc] <> 'HEAP'
AND i.[name] NOT LIKE 'PK_%'
AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0
AND u.[last_system_scan] IS NOT NULL
ORDER BY 1 ASC


SQL Server does not automatically change the recovery model.  If you set it to simple, then see later it is set to full, either:

Someone else changed it back to full
The database has been restored from a backup where the database is set to full recovery
There may be a scheduled agent job that is changing the recovery model.

From a comment on this answer:

This was in AWS and #3 was the case. In this case backups were enabled so they have a process that automatically changes the recovery model back to Full. Changing the backup retention to zero days allows it to stay in simple recovery.


What exactly is the use of the column USER in a pg_hba.conf file?

"someone needs to be logged in as the user 'user123'" - no, that means that someone tries to connect with the Postgres database user user123.

This can be used to allow certain database users to connect only from certain client computers, e.g. the user for a web application is only allowed to connect from the IP of the webserver.

Or this can be used to prevent superuser logins from remote connections by only allowing  local connections for the database user postgres.


How to optimize concurrent restoration of multiple VLDB user DBs?

I think you are going to have to build your own tests for this one - there will be relatively few people who do this on a regular basis - (but happy to be proved wrong).
Is there a reason why you can't use VSS / SAN Snapshots to meet this requirement ?
Some standard best practices are going to help, i.e. use multiple backups files, use backup compression.
regarding restoring 2 VLDBs simultaneously I would try and ensure that each database has its own separate IO systems - so probably a Diskgroup & dedicated controller on the SAN rather than just a separate set of LUNs, but talk to your storage admins as SANs vary widely with regards to best configuration.


After datafiles are restored, oracle performs complete recovery by applying all changes from archived and online redolog files. Those changes also covers "drop statement" you did run after the backup. Hence, the recovery after the restore applies "drop table" statement during "restore database"

In order to recover drop table stament, you need to choose/perform incomplete (time/SCN based) recovery. That will bring database to a time before the drop statement is run.


Balancer is down after stopping config servers

First: balancer is running ONLY at config server replica sets PRIMARY, never on SECONDARY.

Second: (as) your error message says that, that node where you gave rs.statatus() command is not started in replica set mode, more like maintenance mode. i.e. started without --replSet or replication.replSetName config parameter and maybe also without "sharding.clusterRole: configsvr" parameter.

You can check what where starting parameters, by just reading mongod.log file. There is (at startup) list of parameters what that mongod used when it was started.


I fail to see why you are loadbalancing between pgbouncers on the same server, I assume you have a single DB server instance, if they run on the same node you're not really balancing anything.

Than being said, this sounds like timeout issues, since pgsql connections tend to live longer than the standard haproxy timeouts I suggest your raise them: (for example in defaults section add : )

timeout queue           1m
timeout connect         1s
timeout client          3600s
timeout server          3600s
timeout check           2s

This will keep any connection that the client hasn't closed open for an hour. queries that take longer to run will be cut.

see https://github.com/gplv2/haproxy-postgresql/blob/master/haproxy-example.cfg for more inspiration from a working haproxy config.


Too many records on database and how to handle it? MySQL is it good to clone?

Partitioning and proper indexing plus do not forget proper multi server setup. Start from MySQL documentation on indexing and partitioning. When you hit such db size you should be able to afford somebody who knows what to do.

For big datasets you might want to look in to other solutions like Hadoop. Do not forget caching Redis/memcached.

Based of my experience with big data sets. Your table has too many columns, does not have proper indexing. So in case you do proper indexing and partitioning on production(properly setup) speed on indexed, partitioned columns would start degrade after 40,000,000-50,000,000
 per partition.